| Server IP : 172.67.216.113 / Your IP : 172.71.28.146 [ Web Server : Apache System : Linux cpanel01wh.bkk1.cloud.z.com 2.6.32-954.3.5.lve1.4.59.el6.x86_64 #1 SMP Thu Dec 6 05:11:00 EST 2018 x86_64 User : cp648411 ( 1354) PHP Version : 7.2.34 Disable Function : NONE Domains : 0 Domains MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : OFF | Pkexec : OFF Directory : /home2/cp648411/public_html/ilawasia.onnud20.com/class/ |
Upload File : |
<?php
/**
*
*/
class IpmoduleClass extends ViewData
{
public function BasisService()
{
$count = "BasisId as value, BasisName as label";
$order = "";
$search = "StatusActive = '1' AND Deleted = '0'";
$name_table = "t_basis_mas";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['value'] = $row['value'];
$data['Data'][$i]['label'] = $row['label'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function BillingCurrencyService()
{
$count = "BillingCurrencyId as value, BillingCurrencyCode as label";
$order = "";
$search = "StatusActive = '1' AND Deleted = '0' ";
$name_table = "t_billing_currency_mas";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['value'] = $row['value'];
$data['Data'][$i]['label'] = $row['label'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseClassesMapService($request)
{
$count = "
T2.CaseClassId
,T2.ClassesCode
,T2.ClassesTextTH
,T2.ClassesTextEN
,T2.LastUpdateBy
,concat( u1.Name ,' ', concat( u1.MiddleName ,' ', u1.Surname)) as LastUpdateBy
,T2.LastUpdateDateTime as LastUpdateDateTime
";
//,DATE_FORMAT(T2.LastUpdateDateTime, '%Y-%m-%d %h:%m:%s %t') as LastUpdateDateTime
$order = "";
$caseId = $request['CaseId'];
$search = "1=1 AND T1.CaseId = '".$caseId."' AND T2.Deleted = 0";
$name_table = "
t_case_management_trn T1
JOIN t_case_classes_map T2
ON T1.CaseId = T2.CaseId
LEFT JOIN t_system_users u1 ON T2.LastUpdateBy = u1.Id
";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseClassId'] = $row['CaseClassId'];
$data['Data'][$i]['ClassesCode'] = $row['ClassesCode'];
$data['Data'][$i]['ClassesTextTH'] = $row['ClassesTextTH'];
$data['Data'][$i]['ClassesTextEN'] = $row['ClassesTextEN'];
$data['Data'][$i]['LastUpdateBy'] = ($row['LastUpdateBy']=="")?"":$row['LastUpdateBy'];
$data['Data'][$i]['LastUpdateDateTime'] = ($row['LastUpdateDateTime']=="0000-00-00 00:00:00")?"":$row['LastUpdateDateTime'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseDocumentsMapService($request)
{
$caseId = $request['CaseId'];
$count = "
T1.CaseDocumentId as 'caseDocumentId'
,T1.CaseId as 'caseId'
,T1.FilePath as 'filePath'
,T1.FileName as 'fileName'
,T1.FileNameOriginal as 'fileNameOriginal'
,T1.FileSize as 'fileSize'
,concat( u1.Name ,' ', concat( u1.MiddleName ,' ', u1.Surname)) as LastUpdateBy
,T1.LastUpdateDateTime as LastUpdateDateTime
";
$order = "";
$search = "1=1 AND T1.StatusActive = 1 AND T1.Deleted = 0 AND T1.CaseId = '".$caseId."'";
$name_table = "t_case_documents_map T1
LEFT JOIN t_system_users u1 ON T1.LastUpdateBy = u1.Id";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['caseDocumentId'] = $row['caseDocumentId'];
$data['Data'][$i]['caseId'] = $row['caseId'];
$data['Data'][$i]['filePath'] = $row['filePath'];
$data['Data'][$i]['fileName'] = $row['fileName'];
$data['Data'][$i]['fileNameOriginal'] = $row['fileNameOriginal'];
$data['Data'][$i]['fileSize'] = $row['fileSize'];
$data['Data'][$i]['LastUpdateBy'] = $row['LastUpdateBy'];
$data['Data'][$i]['LastUpdateDateTime'] = $row['LastUpdateDateTime'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseEventMapService($request)
{
$caseId = $request['CaseId'];
$count = "ac.CaseEventId
,e.EventsName
,e.EventsId
,DATE_FORMAT(ac.StartDate, '%Y-%m-%d') as StartDate
,DATE_FORMAT(ac.DueDate, '%Y-%m-%d') as DueDate
,DATE_FORMAT(ac.CompleteDate, '%Y-%m-%d') as CompleteDate
,DATE_FORMAT(ac.OccurredDate, '%Y-%m-%d') as OccurredDate
,DATE_FORMAT(ac.StartDate, '%d-%m-%Y') as StartDate_s
,DATE_FORMAT(ac.DueDate, '%d-%m-%Y') as DueDate_s
,DATE_FORMAT(ac.CompleteDate, '%d-%m-%Y') as CompleteDate_s
,DATE_FORMAT(ac.OccurredDate, '%d-%m-%Y') as OccurredDate_s
,e.HasDeadLine
,ac.Remark";
$order = "";
$search = "ac.CaseId = '".$caseId."' AND ac.Deleted = '0' AND e.HasDeadLine = '0' ";
$name_table = " t_case_events_map ac LEFT JOIN t_events_mas e ON ac.EventId = e.EventsId";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if(@$request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}else{
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
}
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$StartDate = ($row['StartDate']=="00-00-0000")?"":$row['StartDate'];
$CompleteDate = ($row['CompleteDate']=="00-00-0000")?"":$row['CompleteDate'];
$CompleteDate_s = ($row['CompleteDate_s']=="00-00-0000")?"":$row['CompleteDate_s'];
$OccurredDate = ($row['OccurredDate']=="00-00-0000")?"":$row['OccurredDate'];
$HasDeadLine = ($row['HasDeadLine']=="00-00-0000")?"":$row['HasDeadLine'];
$data['Data'][$i]['CaseEventId'] = $row['CaseEventId'];
$data['Data'][$i]['EventsId'] = $row['EventsId'];
$data['Data'][$i]['EventsName'] = $row['EventsName'];
$data['Data'][$i]['DueDate'] = $row['DueDate'];
$data['Data'][$i]['StartDate'] = $StartDate;
$data['Data'][$i]['CompleteDate'] = $CompleteDate;
$data['Data'][$i]['OccurredDate'] = $OccurredDate;
$data['Data'][$i]['HasDeadLine'] = $HasDeadLine;
$data['Data'][$i]['StartDate_s'] = $row['StartDate_s'];
$data['Data'][$i]['CompleteDate_s'] = $CompleteDate_s;
$data['Data'][$i]['OccurredDate_s'] = $row['OccurredDate_s'];
$data['Data'][$i]['HasDeadLine_s'] = $row['HasDeadLine_s'];
/*
$data['Data'][$i]['StartDate'] = $row['StartDate'];
$data['Data'][$i]['CompleteDate'] = $row['CompleteDate'];
$data['Data'][$i]['OccurredDate'] = $row['OccurredDate'];
$data['Data'][$i]['HasDeadLine'] = $row['HasDeadLine'];
*/
$data['Data'][$i]['Remark'] = $row['Remark'];
$i++;
}
$count = "ac.CaseEventId
,e.EventsName
,e.EventsId
,DATE_FORMAT(ac.StartDate, '%Y-%m-%d') as StartDate
,DATE_FORMAT(ac.DueDate, '%Y-%m-%d') as DueDate
,DATE_FORMAT(ac.CompleteDate, '%Y-%m-%d') as CompleteDate
,DATE_FORMAT(ac.OccurredDate, '%Y-%m-%d') as OccurredDate
,DATE_FORMAT(ac.StartDate, '%d-%m-%Y') as StartDate_s
,DATE_FORMAT(ac.DueDate, '%d-%m-%Y') as DueDate_s
,DATE_FORMAT(ac.CompleteDate, '%d-%m-%Y') as CompleteDate_s
,DATE_FORMAT(ac.OccurredDate, '%d-%m-%Y') as OccurredDate_s
,DATE_FORMAT(ac.StartDate, '%d-%m-%Y') as StartDate_s
,DATE_FORMAT(ac.DueDate, '%d-%m-%Y') as DueDate_s
,DATE_FORMAT(ac.CompleteDate, '%d-%m-%Y') as CompleteDate_s
,DATE_FORMAT(ac.OccurredDate, '%d-%m-%Y') as OccurredDate_s
,e.HasDeadLine
,ac.Remark";
$order = "";
$search = "ac.CaseId = '".$caseId."' AND ac.Deleted = 0 AND e.HasDeadLine = 1 AND ac.CompleteDate <> '0000-00-00 00:00:00'";
$name_table = " t_case_events_map ac LEFT JOIN t_events_mas e ON ac.EventId = e.EventsId";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num2 = mysqli_num_rows($result);
if($respons_count_num2 > 0){
while ($row = $this->q_ro($result)) {
$StartDate = ($row['StartDate']=="0000-00-00")?"":$row['StartDate'];
$CompleteDate = ($row['CompleteDate']=="0000-00-00")?"":$row['CompleteDate'];
$OccurredDate = ($row['OccurredDate']=="0000-00-00")?"":$row['OccurredDate'];
$HasDeadLine = ($row['HasDeadLine']=="0000-00-00")?"":$row['HasDeadLine'];
$DueDate = ($row['DueDate']=="0000-00-00")?"":$row['DueDate'];
$DueDate_s = ($row['DueDate_s']=="0000-00-00")?"":$row['DueDate_s'];
$data['Data'][$i]['CaseEventId'] = $row['CaseEventId'];
$data['Data'][$i]['EventsId'] = $row['EventsId'];
$data['Data'][$i]['EventsName'] = $row['EventsName'];
$data['Data'][$i]['DueDate'] = $row['DueDate'];
$data['Data'][$i]['StartDate'] = $row['StartDate'];
$data['Data'][$i]['CompleteDate'] = $row['CompleteDate'];
$data['Data'][$i]['OccurredDate'] = $row['OccurredDate'];
$data['Data'][$i]['HasDeadLine'] = $row['HasDeadLine'];
$data['Data'][$i]['Remark'] = $row['Remark'];
$data['Data'][$i]['StartDate_s'] = $row['StartDate_s'];
$data['Data'][$i]['CompleteDate_s'] = $row['CompleteDate_s'];
$data['Data'][$i]['OccurredDate_s'] = $row['OccurredDate_s'];
$data['Data'][$i]['HasDeadLine_s'] = $row['HasDeadLine_s'];
$data['Data'][$i]['DueDate_s'] = $DueDate_s;
$i++;
}
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseEventMapService2($request)
{
$caseId = $request['CaseId'];
$count = "ac.CaseEventId
,e.EventsName
,e.EventsId
,DATE_FORMAT(ac.StartDate, '%Y-%m-%d') as StartDate
,DATE_FORMAT(ac.DueDate, '%Y-%m-%d') as DueDate
,DATE_FORMAT(ac.CompleteDate, '%Y-%m-%d') as CompleteDate
,DATE_FORMAT(ac.OccurredDate, '%Y-%m-%d') as OccurredDate
,DATE_FORMAT(ac.StartDate, '%d-%m-%Y') as StartDate_s
,DATE_FORMAT(ac.DueDate, '%d-%m-%Y') as DueDate_s
,DATE_FORMAT(ac.CompleteDate, '%d-%m-%Y') as CompleteDate_s
,DATE_FORMAT(ac.OccurredDate, '%d-%m-%Y') as OccurredDate_s
,e.HasDeadLine
,ac.Remark";
$order = "";
$search = "ac.CaseId = '".$caseId."' AND ac.Deleted = '0' AND e.HasDeadLine = '1' ";
$name_table = " t_case_events_map ac LEFT JOIN t_events_mas e ON ac.EventId = e.EventsId";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}else{
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
}
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$StartDate = ($row['StartDate']=="0000-00-00")?"":$row['StartDate'];
$CompleteDate = ($row['CompleteDate']=="0000-00-00")?"":$row['CompleteDate'];
$OccurredDate = ($row['OccurredDate']=="0000-00-00")?"":$row['OccurredDate'];
$HasDeadLine = ($row['HasDeadLine']=="0000-00-00")?"":$row['HasDeadLine'];
$DueDate = ($row['DueDate']=="0000-00-00")?"":$row['DueDate'];
$DueDate_s = ($row['DueDate_s']=="0000-00-00")?"":$row['DueDate_s'];
$data['Data'][$i]['CaseEventId'] = $row['CaseEventId'];
$data['Data'][$i]['EventsId'] = $row['EventsId'];
$data['Data'][$i]['EventsName'] = $row['EventsName'];
$data['Data'][$i]['DueDate'] = $DueDate;
$data['Data'][$i]['StartDate'] = $row['StartDate'];
$data['Data'][$i]['CompleteDate'] = $row['CompleteDate'];
$data['Data'][$i]['OccurredDate'] = $row['OccurredDate'];
$data['Data'][$i]['HasDeadLine'] = $row['HasDeadLine'];
$data['Data'][$i]['StartDate_s'] = $row['StartDate_s'];
$data['Data'][$i]['CompleteDate_s'] = $row['CompleteDate_s'];
$data['Data'][$i]['OccurredDate_s'] = $row['OccurredDate_s'];
$data['Data'][$i]['HasDeadLine_s'] = $row['HasDeadLine_s'];
$data['Data'][$i]['DueDate_s'] = $DueDate_s;
$data['Data'][$i]['Remark'] = $row['Remark'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseEventMapServiceCaseEventId($Id)
{
$caseEventId = $Id;
$count = "T1.EventId
,e.EventsName
,DATE_FORMAT(T1.StartDate, '%Y-%m-%d') as StartDate
,DATE_FORMAT(T1.DueDate, '%Y-%m-%d') as DueDate
,DATE_FORMAT(T1.CompleteDate, '%Y-%m-%d') as CompleteDate
,DATE_FORMAT(T1.OccurredDate, '%Y-%m-%d') as OccurredDate
,e.HasDeadLine
,T1.Remark";
$order = "";
$search = "CaseEventId = '".$caseEventId."'";
$name_table = "t_case_events_map T1 LEFT JOIN t_events_mas e ON T1.EventId = e.EventsId";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['EventId'] = $row['EventId'];
$data['Data'][$i]['EventsName'] = $row['EventsName'];
$data['Data'][$i]['StartDate'] = $row['StartDate'];
$data['Data'][$i]['DueDate'] = $row['DueDate'];
$data['Data'][$i]['CompleteDate'] = $row['CompleteDate'];
$data['Data'][$i]['OccurredDate'] = $row['OccurredDate'];
$data['Data'][$i]['HasDeadLine'] = $row['HasDeadLine'];
$data['Data'][$i]['Remark'] = $row['Remark'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseImagesMapService($Id)
{
$caseId = $Id;
$count = "T1.CaseImageId as 'caseImageId'
,T1.CaseId as 'caseId'
,T1.FilePath as 'filePath'
,T1.FileName as 'fileName'
,T1.FileNameOriginal as 'fileNameOriginal'
,concat( u1.Name ,' ', concat( u1.MiddleName ,' ', u1.Surname)) as LastUpdateBy
,DATE_FORMAT(T1.LastUpdateDateTime, '%Y-%m-%d %h:%m:%s %t') as LastUpdateDateTime";
$order = "";
$search = "1=1 AND T1.StatusActive = 1 AND T1.Deleted = 0 AND T1.CaseId = '".$caseId."'";
$name_table = "t_case_images_map T1 LEFT JOIN t_system_users u1 ON T1.LastUpdateBy = u1.Id";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['caseImageId'] = $row['caseImageId'];
$data['Data'][$i]['caseId'] = $row['caseId'];
$data['Data'][$i]['filePath'] = $row['filePath'];
$data['Data'][$i]['fileName'] = $row['fileName'];
$data['Data'][$i]['fileNameOriginal'] = $row['fileNameOriginal'];
$data['Data'][$i]['LastUpdateBy'] = $row['LastUpdateBy'];
$data['Data'][$i]['LastUpdateDateTime'] = $row['LastUpdateDateTime'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseManageService($request)
{
$family = $request['family'];
$caseOffice = $request['caseOffice'];
$caseType = $request['caseType'];
$country = $request['country'];
$count = "COUNT(1) as ISDUPLICATE";
$order = "";
$search = "T1.StatusActive = '1' AND T1.Deleted = '0'";
$name_table = "t_case_management_trn T1";
$search = $search." AND SUBSTRING(CaseReference, 3, 5) = '".@$family."'";
if($caseOffice > 0){
$search = $search." AND T1.CaseOfficeId = '".@$caseOffice."'";
}
if($caseType > 0){
$search = $search." AND T1.CaseTypeId = '".@$caseType."'";
}
if($country > 0){
$search = $search." AND T1.CountryId = '".@$country."'";
}
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['ISDUPLICATE'] = $row['ISDUPLICATE'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseNamesMapService($request)
{
$caseId = $request['CaseId'];
$count = "T2.CaseNameId
,T2.Attention
,T2.Reference
,T2.NamesId
,T3.NamesName
,T3.NamesCode
,T3.Initial
,T4.NamesTypeName
";
$order = "ORDER BY T2.seqNo";
$search = "1=1 AND T1.CaseId = '".$caseId."'";
$name_table = "t_case_management_trn T1
JOIN t_case_names_map T2
ON T1.CaseId = T2.CaseId
LEFT JOIN t_names_mas T3
ON T2.NamesId = T3.NamesId
LEFT JOIN t_names_type_mas T4
ON T2.NamesTypeId = T4.NamesTypeId";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search,'order'=>$order);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseNameId'] = $row['CaseNameId'];
$data['Data'][$i]['Attention'] = $row['Attention'];
$data['Data'][$i]['Reference'] = $row['Reference'];
$data['Data'][$i]['NamesId'] = $row['NamesId'];
$data['Data'][$i]['NamesName'] = $row['NamesName'];
$data['Data'][$i]['NamesCode'] = $row['NamesCode'];
$data['Data'][$i]['Initial'] = $row['Initial'];
$data['Data'][$i]['NamesTypeName'] = $row['NamesTypeName'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseNamesTypeService()
{
$count = "NamesTypeId as value, NamesTypeName as label";
$order = "ORDER BY SeqNo";
$search = "StatusActive = 1 AND Deleted = 0";
$name_table = "t_names_type_mas";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['value'] = $row['value'];
$data['Data'][$i]['label'] = $row['label'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseOfficeService()
{
$count = "CaseOfficeId as value, CaseOfficeName as label";
$order = "";
$search = "StatusActive = '1' AND Deleted = '0'";
$name_table = "t_case_office_mas";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['value'] = $row['value'];
$data['Data'][$i]['label'] = $row['label'];
$i++;
}
}else{
$data['Data'] = array();
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseOfficialNumberMapService($request)
{
$caseId = $request['CaseId'];
$count = "map.CaseOfficialNumberId
,map.CaseId
,map.OfficialNumberTypeId
,mas.OfficialNumberTypeName as CaseOfficialNumberType
,map.OfficialNo
,map.SeqNo
,DATE_FORMAT(map.DateInForce, '%Y-%m-%d') as DateInForce
,DATE_FORMAT(map.DateInForce, '%d-%m-%Y') as DateInForce2
,map.StatusActive
,map.CreateBy
,map.LastUpdateBy
,map.CreateDateTime
,map.LastUpdateDateTime";
$order = "";
$search = "map.CaseId = '".$caseId."' AND map.Deleted = 0 ";
$name_table = "t_case_official_number_map map
LEFT JOIN t_official_number_type_mas mas ON mas.OfficialNumberTypeId = map.OfficialNumberTypeId";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseId'] = $row['CaseId'];
$data['Data'][$i]['CaseOfficialNumberId'] = $row['CaseOfficialNumberId'];
$data['Data'][$i]['CaseOfficialNumberType'] = $row['CaseOfficialNumberType'];
$data['Data'][$i]['CreateBy'] = $row['CreateBy'];
$data['Data'][$i]['CreateDateTime'] = $row['CreateDateTime'];
$data['Data'][$i]['DateInForce'] = $row['DateInForce'];
$data['Data'][$i]['DateInForce2'] = $row['DateInForce2'];
$data['Data'][$i]['LastUpdateBy'] = $row['LastUpdateBy'];
$data['Data'][$i]['LastUpdateDateTime'] = $row['LastUpdateDateTime'];
$data['Data'][$i]['OfficialNo'] = $row['OfficialNo'];
$data['Data'][$i]['OfficialNumberTypeId'] = $row['OfficialNumberTypeId'];
$data['Data'][$i]['SeqNo'] = $row['SeqNo'];
$data['Data'][$i]['StatusActive'] = $row['StatusActive'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseOfficialNumberMapServiceId($Id)
{
$Id = $Id;
$count = "CaseId
, mas.OfficialNumberTypeName as CaseOfficialNumberType
, map.OfficialNumberTypeId as OfficialNumberTypeId
, map.OfficialNo as OfficialNo
, DateInForce ";
$order = "";
$search = "CaseOfficialNumberId = '".$Id."'";
$name_table = "t_case_official_number_map map
LEFT JOIN t_official_number_type_mas mas ON mas.OfficialNumberTypeId = map.OfficialNumberTypeId
";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search,'order'=>$order);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseId'] = $row['CaseId'];
$data['Data'][$i]['CaseOfficialNumberType'] = $row['CaseOfficialNumberType'];
$data['Data'][$i]['DateInForce'] = $row['DateInForce'];
$data['Data'][$i]['OfficialNo'] = $row['OfficialNo'];
$data['Data'][$i]['OfficialNumberTypeId'] = $row['OfficialNumberTypeId'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseRelatedCaseMapService($request)
{
$caseId = $request['CaseId'];
$family = $request['Family'];
$caseReference = $request['CaseReference'];
$count = "
T1.CaseId
, T1.CaseReference as CaseReference
, T1.Family as Family
, T1.Title as Title
, T3.CaseTypeName
, T4.CountryName as CountryName
, T5.CaseOfficeName as CaseOfficeName
, T6.BasisName as BasisName
, T1.CaseStatusId as CaseStatusId
, T7.CaseStatusName as CaseStatusName
, T8.ProfitCentreName as ProfitCentreName
, T9.BillingCurrencyCode as BillingCurrencyCode
, T10.TaxTreatmentName as TaxTreatmentName
, T11.TypeOfMarkName as TypeOfMarkName
, T1.CaseTypeId as caseTypeId
, T1.typeOfMarkId as typeOfMarkId
, T1.basisId as basisId
, T1.PurchaseOrderNo
, T1.NoOfClaims
, T1.KeepOnTopNote
, T1.StatusActive
,concat(u1.Name, ' ', concat (u1.MiddleName, ' ', u1.Surname)) as CreateBy
,concat(u2.Name, ' ', concat(u2.MiddleName, ' ', u2.Surname)) as LastUpdateBy
,DATE_FORMAT(T1.CreateDateTime, '%Y-%m-%d %h:%m:%s') as CreateDateTime
,DATE_FORMAT(T1.LastUpdateDateTime, '%Y-%m-%d %h:%m:%s') as LastUpdateDateTime";
$order = "";
$search = "1=1
AND T1.StatusActive = 1
AND T1.Deleted = 0
AND T.CaseId = '".@$caseId."'
";
$name_table = " t_case_related_map T
LEFT JOIN t_case_management_trn T1 ON T.CaseRefId = T1.CaseId
LEFT JOIN t_case_type_mas T3 ON T1.CaseTypeId = T3.CaseTypeId
LEFT JOIN t_country_mas T4 ON T1.CountryId= T4.CountryId
LEFT JOIN t_case_office_mas T5 ON T1.CaseOfficeId = T5.CaseOfficeId
LEFT JOIN t_basis_mas T6 ON T1.BasisId = T6.BasisId
LEFT JOIN t_case_status_mas T7 ON T1.CaseStatusId = T7.CaseStatusId
LEFT JOIN t_profit_centre_mas T8 ON T1.ProfitCentreId = T8.ProfitCentreId
LEFT JOIN t_billing_currency_mas T9 ON T1.BillingCurrencyId = T9.BillingCurrencyId
LEFT JOIN t_tax_treatment_mas T10 ON T1.TaxTreatmentId = T10.TaxTreatmentId
LEFT JOIN t_type_of_mark_mas T11 ON T1.TypeOfMarkId = T11.TypeOfMarkId
LEFT JOIN t_system_users u1 ON T1.CreateBy = u1.Id
LEFT JOIN t_system_users u2 ON T1.LastUpdateBy = u2.Id
";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['BasisName'] = $row['BasisName'];
$data['Data'][$i]['BillingCurrencyCode'] = $row['BillingCurrencyCode'];
$data['Data'][$i]['CaseId'] = $row['CaseId'];
$data['Data'][$i]['CaseOfficeName'] = $row['CaseOfficeName'];
$data['Data'][$i]['CaseReference'] = $row['CaseReference'];
$data['Data'][$i]['CaseStatusId'] = $row['CaseStatusId'];
$data['Data'][$i]['CaseStatusName'] = $row['CaseStatusName'];
$data['Data'][$i]['CaseTypeName'] = $row['CaseTypeName'];
$data['Data'][$i]['CountryName'] = $row['CountryName'];
$data['Data'][$i]['CreateBy'] = $row['CreateBy'];
$data['Data'][$i]['CreateDateTime'] = $row['CreateDateTime'];
$data['Data'][$i]['Family'] = $row['Family'];
$data['Data'][$i]['KeepOnTopNote'] = $row['KeepOnTopNote'];
$data['Data'][$i]['LastUpdateBy'] = $row['LastUpdateBy'];
$data['Data'][$i]['LastUpdateDateTime'] = $row['LastUpdateDateTime'];
$data['Data'][$i]['NoOfClaims'] = $row['NoOfClaims'];
$data['Data'][$i]['ProfitCentreName'] = $row['ProfitCentreName'];
$data['Data'][$i]['PurchaseOrderNo'] = $row['PurchaseOrderNo'];
$data['Data'][$i]['StatusActive'] = $row['StatusActive'];
$data['Data'][$i]['TaxTreatmentName'] = $row['TaxTreatmentName'];
$data['Data'][$i]['Title'] = $row['Title'];
$data['Data'][$i]['TypeOfMarkName'] = $row['TypeOfMarkName'];
$data['Data'][$i]['basisId'] = $row['basisId'];
$data['Data'][$i]['caseTypeId'] = $row['caseTypeId'];
$data['Data'][$i]['typeOfMarkId'] = $row['typeOfMarkId'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseRelatedCaseMapServiceRelate($request)
{
$caseId = $request['CaseId'];
$count = "";
$order = "";
$search = "StatusActive = '1' AND Deleted = '0' AND CaseId = '".@$caseId."'";
$name_table = "t_case_related_map";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseRelatedId'] = $row['CaseRelatedId'];
$data['Data'][$i]['CaseId'] = $row['CaseId'];
$data['Data'][$i]['CaseRefId'] = $row['CaseRefId'];
$data['Data'][$i]['StatusActive'] = $row['StatusActive'];
$data['Data'][$i]['CreateBy'] = $row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] = $row['LastUpdateBy'];
$data['Data'][$i]['CreateDateTime'] = $row['CreateDateTime'];
$data['Data'][$i]['LastUpdateDateTime'] = $row['LastUpdateDateTime'];
$data['Data'][$i]['Deleted'] = $row['Deleted'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function classQuery($ts, $cl)
{
$ts = intval($ts);
sort($cl); // เรียง array
if ($ts === 0) {
// AND a LIKE '%c{item}r%'
$conditions = array_map(function($item) {
return "a LIKE '%c{$item}r%'";
}, $cl);
return ' AND ' . implode(' AND ', $conditions);
}
else if ($ts === 1) {
// OR a LIKE '%c{item}r%'
$conditions = array_map(function($item) {
return "a LIKE '%c{$item}r%'";
}, $cl);
return ' AND (' . implode(' OR ', $conditions) . ')';
}
else if ($ts === 2) {
// AND a LIKE '%c{item}r%'
$conditions = array_map(function($item) {
return "a LIKE '%c{$item}r%'";
}, $cl);
return ' AND ' . implode(' AND ', $conditions);
}
else if ($ts === 3) {
// AND a NOT LIKE '%c{item}r%'
$conditions = array_map(function($item) {
return "a NOT LIKE '%c{$item}r%'";
}, $cl);
return ' AND ' . implode(' AND ', $conditions);
}
return ''; // กรณีไม่เข้าเงื่อนไขใด ๆ
}
public function CaseSearchService($request)
{
$caseReferenceTS = $request['caseReferenceTS'];
$caseReferenceText = $request['caseReferenceText'];
$familyTS = $request['familyTS'];
$familyText = $request['familyText'];
$instructorRefTS = $request['instructorRefTS'];
$instructorRefText = $request['instructorRefText'];
$caseOfficeId = $request['caseOfficeId'];
$caseTypeId = $request['caseTypeId'];
$countryId = $request['countryId'];
$basisId = $request['basisId'];
$class1TS = $request['class1TS'];
$class1ListId = $request['class1ListId'];
$class2TS = $request['class2TS'];
$class2ListId = $request['class2ListId'];
$appNoText = $request['appNoText'];
$appNoTS = $request['appNoTS'];
$titleTS = $request['titleTS'];
$titleText = $request['titleText'];
$typeOfMarkId = $request['typeOfMarkId'];
$instructorTS = $request['instructorTS'];
$instructorText = $request['instructorText'];
$debtorTS = $request['debtorTS'];
$debtorText = $request['debtorText'];
$ownerTs = $request['ownerTs'];
$ownerText = $request['ownerText'];
$agentTS = $request['agentTS'];
$agentText = $request['agentText'];
$refferalTS = $request['refferalTS'];
$refferalText = $request['refferalText'];
$partnerTS = $request['partnerTS'];
$partnerText = $request['partnerText'];
$attorneyTS = $request['attorneyTS'];
$attorneyText = $request['attorneyText'];
$executiveTS = $request['executiveTS'];
$executiveText = $request['executiveText'];
$eventId = $request['eventId'];
$eventDateTS = $request['eventDateTS'];
$eventDateStart = $request['eventDateStart'];
$eventDateEnd = $request['eventDateEnd'];
$occurredEventBL = $request['occurredEventBL'];
$dueEventBL = $request['dueEventBL'];
$completeEventBL = $request['completeEventBL'];
$caseStatusId = $request['caseStatusId'];
$count = "";
$order = "";
/*
$search = "1=1 ". ( $class1ListId != '') ? classQuery( class1TS, class1ListId) : '' .
( $class2ListId != "") ? classQuery( class2TS, class2ListId) : '';
*/
$search = "1=1" .
(!empty($class1ListId) ? $this->classQuery($class1TS, $class1ListId) : '') .
(!empty($class2ListId) ? $this->classQuery($class2TS, $class2ListId) : '');
$name_table = "
(SELECT DISTINCT
c.CaseId
, c.CaseReference
, c.Title
, img.FilePath
, SUBSTRING(c.CaseReference, 3, 5) as Family
, n1.Reference as InstructorRef
, offmas.CaseOfficeName as CaseOffice
, tmas.CaseTypeName
, ctmas.CountryName
, bmas.BasisName
, STUFF(
(
SELECT ', ' + cast(ClassesCode AS VARCHAR )
FROM T_CASE_CLASSES_MAP
WHERE CaseId = c.CaseId AND Deleted = 0
ORDER BY ClassesCode
)
, 1, 1, '') as ClassList
, (SELECT 'c' + cast(ClassesId AS VARCHAR ) +'r'
FROM T_CASE_CLASSES_MAP
WHERE CaseId = c.CaseId AND Deleted = 0
ORDER BY ClassesId
) as a
, tmmas.TypeOfMarkName
, nm1.NamesName as Instructor
, nm1.NamesCode as InstructorCode
, nm1.PostalAddress as InstructorAddress
, nm1.TaxID as InstructorTaxId
, nm2.NamesName as Debtor
, nm2.NamesCode as DebtorCode
, nm2.PostalAddress as DebtorAddress
, nm2.TaxID as DebtorTaxID
, nm3.NamesName as Owner
, nm3.NamesCode as OwnerCode
, nm3.PostalAddress as OwnerAddress
, nm3.TaxID as OwnerTaxID
, nm3.ElectronicMail as OwnerEmail
, nm3.Telephone as OwnerContact
, nm4.NamesName as Agent
, nm4.NamesCode as AgentCode
, nm4.PostalAddress as AgentAddress
, nm4.TaxID as AgentTaxID
, nm4.ElectronicMail as AgentEmail
, nm4.Telephone as AgentContact
, nm5.NamesName as Partner
, nm5.NamesCode as PartnerCode
, nm5.PostalAddress as PartnerAddress
, nm5.TaxID as PartnerTaxID
, nm6.NamesName as Attorney
, nm6.NamesCode as AttorneyCode
, nm6.PostalAddress as AttorneyAddress
, nm6.TaxID as AttorneyTaxID
, nm7.NamesName as Executive
, nm7.NamesCode as ExecutiveCode
, nm7.PostalAddress as ExecutiveAddress
, nm7.TaxID as ExecutiveTaxID
, nm8.NamesName as Refferal
, nm8.NamesCode as RefferalCode
, nm8.PostalAddress as RefferalAddress
, nm8.TaxID as RefferalTaxID
, c.CaseStatusId
, csmas.CaseStatusName as CaseStatusName
, concat( u1.Name ,' ', concat( u1.MiddleName ,' ', u1.Surname)) as CreateBy
, concat(u2.Name, ' ', concat(u2.MiddleName, ' ', u2.Surname)) as LastUpdateBy
, DATE_FORMAT(c.CreateDateTime, '%Y-%m-%d %h:%m:%s %t') as CreateDateTime
, DATE_FORMAT(c.LastUpdateDateTime, '%Y-%m-%d %h:%m:%s %t') as LastUpdateDateTime
, pcmas.ProfitCentreName as ProfitCentreName
, ttmas.TaxTreatmentName as TaxTreatmentName
, onm1.OfficialNo as AppNo
, onm2.OfficialNo as RegisNo
, DATE_FORMAT(e37.OccurredDate, '%Y-%m-%d') as AppFilingDate
, DATE_FORMAT(e29.OccurredDate, '%Y-%m-%d') as RegisDate
, DATE_FORMAT(e51.CompleteDate, '%Y-%m-%d') as NextRenewalDate
, DATE_FORMAT(e69.CompleteDate, '%Y-%m-%d') as RenewalDeadlineDate
, STUFF(
(
SELECT '\n- ' + emasl.EventsName + ISNULL(format(m.CompleteDate, ' - dd/MM/yy'), format(m.OccurredDate, ' - dd/MM/yy '))
FROM t_case_events_map m
LEFT JOIN t_events_mas emasl ON emasl.EventsId = m.EventId
WHERE m.CaseId = c.CaseId AND m.Deleted = 0
AND (m.CompleteDate IS NOT NULL OR m.DueDate IS NULL)
AND m.EventId <> 40 AND m.EventId <> 37 AND m.EventId <> 29
ORDER BY m.CaseEventId
)
, 1, 1, '') as EventList
FROM t_case_management_trn c
---LEFT JOIN t_case_names_map nm1 ON nm1.CaseId = c.CaseId
LEFT JOIN t_case_office_mas offmas ON c.CaseOfficeId = offmas.CaseOfficeId
LEFT JOIN t_case_type_mas tmas ON c.CaseTypeId = tmas.CaseTypeId
LEFT JOIN t_country_mas ctmas ON c.CountryId = ctmas.CountryId
LEFT JOIN t_basis_mas bmas ON c.BasisId = bmas.BasisId
LEFT JOIN t_type_of_mark_mas tmmas ON c.TypeOfMarkId = tmmas.TypeOfMarkId
LEFT JOIN t_system_users u1 ON c.CreateBy = u1.Id
LEFT JOIN t_system_users u2 ON c.LastUpdateBy = u2.Id
LEFT JOIN t_case_status_mas csmas ON c.CaseStatusId = csmas.CaseStatusId
LEFT JOIN t_profit_centre_mas pcmas ON c.ProfitCentreId = pcmas.ProfitCentreId
LEFT JOIN t_tax_treatment_mas ttmas ON c.TaxTreatmentId = ttmas.TaxTreatmentId
LEFT JOIN t_case_official_number_map onm1 ON onm1.CaseId = c.CaseId AND onm1.OfficialNumberTypeId = 1 AND onm1.Deleted = 0
LEFT JOIN t_case_official_number_map onm2 ON onm2.CaseId = c.CaseId AND onm2.OfficialNumberTypeId = 2 AND onm2.Deleted = 0
` + ((instructorText || '') !== '' ? `
JOIN t_case_names_map n1 ON n1.CaseId = c.CaseId AND n1.NamesTypeId = 1
JOIN t_names_mas nm1 ON n1.NamesId = nm1.NamesId AND n1.NamesTypeId = 1
` : `
LEFT JOIN t_case_names_map n1 ON n1.CaseId = c.CaseId AND n1.NamesTypeId = 1
LEFT JOIN t_names_mas nm1 ON n1.NamesId = nm1.NamesId AND n1.NamesTypeId = 1
`) + `
` + ( parseInt(instructorTS) !== 6 ? `AND ISNULL(nm1.NamesName + '{' + nm1.NamesCode + '}', '') ` : `AND nm1.PostalAddress `) + isRefuse(instructorTS) +` LIKE '%".@$instructor."%'
` + ((debtorText || '') !== '' ? `
JOIN t_case_names_map n2 ON n2.CaseId = c.CaseId AND n2.NamesTypeId = 2
JOIN t_names_mas nm2 ON n2.NamesId = nm2.NamesId AND n2.NamesTypeId = 2
` : `
LEFT JOIN t_case_names_map n2 ON n2.CaseId = c.CaseId AND n2.NamesTypeId = 2
LEFT JOIN t_names_mas nm2 ON n2.NamesId = nm2.NamesId AND n2.NamesTypeId = 2
`) + `
AND nm2.NamesName + '{' + nm2.NamesCode + '}' `+ isRefuse(debtorTS) +` LIKE '%".@$debtor."%'
` + ((ownerText || '') !== '' ? `
JOIN t_case_names_map n3 ON n3.CaseId = c.CaseId AND n3.NamesTypeId = 3
JOIN t_names_mas nm3 ON n3.NamesId = nm3.NamesId AND n3.NamesTypeId = 3
` : `
LEFT JOIN t_case_names_map n3 ON n3.CaseId = c.CaseId AND n3.NamesTypeId = 3
LEFT JOIN t_names_mas nm3 ON n3.NamesId = nm3.NamesId AND n3.NamesTypeId = 3
`) + `
` + ( parseInt(ownerTs) !== 6 ? `AND ISNULL(nm3.NamesName + '{' + nm3.NamesCode + '}', '') ` : `AND nm3.PostalAddress ` ) + isRefuse(ownerTs) +` LIKE '%".@$owner."%'
` + ((agentText || '') !== '' ? `
JOIN t_case_names_map n4 ON n4.CaseId = c.CaseId AND n4.NamesTypeId = 4
JOIN t_names_mas nm4 ON n4.NamesId = nm4.NamesId AND n4.NamesTypeId = 4
` : `
LEFT JOIN t_case_names_map n4 ON n4.CaseId = c.CaseId AND n4.NamesTypeId = 4
LEFT JOIN t_names_mas nm4 ON n4.NamesId = nm4.NamesId AND n4.NamesTypeId = 4
`) + `
AND nm4.NamesName + '{' + nm4.NamesCode + '}' `+ isRefuse(agentTS) +` LIKE '%".@$agent."%'
` + ((partnerText || '') !== '' ? `
JOIN t_case_names_map n5 ON n5.CaseId = c.CaseId AND n5.NamesTypeId = 5
JOIN t_names_mas nm5 ON n5.NamesId = nm5.NamesId AND n5.NamesTypeId = 5
` : `
LEFT JOIN t_case_names_map n5 ON n5.CaseId = c.CaseId AND n5.NamesTypeId = 5
LEFT JOIN t_names_mas nm5 ON n5.NamesId = nm5.NamesId AND n5.NamesTypeId = 5
`) + `
AND nm5.NamesName + '{' + nm5.NamesCode + '}' `+ isRefuse(partnerTS) +` LIKE '%".@$partner."%'
` + ((attorneyText || '') !== '' ? `
JOIN t_case_names_map n6 ON n6.CaseId = c.CaseId AND n6.NamesTypeId = 6
JOIN t_names_mas nm6 ON n6.NamesId = nm6.NamesId AND n6.NamesTypeId = 6
` : `
LEFT JOIN t_case_names_map n6 ON n6.CaseId = c.CaseId AND n6.NamesTypeId = 6
LEFT JOIN t_names_mas nm6 ON n6.NamesId = nm6.NamesId AND n6.NamesTypeId = 6
`) + `
AND nm6.NamesName + '{' + nm6.NamesCode + '}' `+ isRefuse(attorneyTS) +` LIKE '%".@$attorney."%'
` + ((executiveText || '') !== '' ? `
JOIN t_case_names_map n7 ON n7.CaseId = c.CaseId AND n7.NamesTypeId = 7
JOIN t_names_mas nm7 ON n7.NamesId = nm7.NamesId AND n7.NamesTypeId = 7
` : `
LEFT JOIN t_case_names_map n7 ON n7.CaseId = c.CaseId AND n7.NamesTypeId = 7
LEFT JOIN t_names_mas nm7 ON n7.NamesId = nm7.NamesId AND n7.NamesTypeId = 7
`) + `
AND nm7.NamesName + '{' + nm7.NamesCode + '}' `+ isRefuse(executiveTS) +` LIKE '".@$executive."'
` + ((refferalText || '') !== '' ? `
JOIN t_case_names_map n8 ON n8.CaseId = c.CaseId AND n8.NamesTypeId = 8
JOIN t_names_mas nm8 ON n8.NamesId = nm8.NamesId AND n8.NamesTypeId = 8
` : `
LEFT JOIN t_case_names_map n8 ON n8.CaseId = c.CaseId AND n8.NamesTypeId = 8
LEFT JOIN t_names_mas nm8 ON n8.NamesId = nm8.NamesId AND n8.NamesTypeId = 8
`) + `
AND nm8.NamesName + '{' + nm8.NamesCode + '}' `+ isRefuse(refferalTS) +` LIKE '%".@$refferal."%'
` + ( parseInt(eventId) !== 0 ? ' JOIN t_case_events_map cem ON c.CaseId = cem.CaseId AND cem.Deleted = 0 AND cem.EventId = ' + eventId : ' LEFT JOIN t_case_events_map cem ON c.CaseId = cem.CaseId AND cem.Deleted = 0 ' ) + `
` + (( eventDateStart !== undefined) || ( eventDateEnd !== undefined ) ?
( occurredEventBL === 'true' ? eventDate(eventDateTS, 'OccurredDate') : '') + `
` + ( dueEventBL === 'true' ? eventDate(eventDateTS, 'DueDate') : '') + `
` + ( completeEventBL === 'true' ? eventDate(eventDateTS, 'CompleteDate') : '')
: '') + `
LEFT JOIN t_case_images_map img ON img.CaseId = c.CaseId AND img.Deleted = 0
LEFT JOIN t_case_events_map e51 ON e51.CaseId = c.CaseId AND e51.EventId = 51 AND e51.Deleted = 0
LEFT JOIN t_case_events_map e29 ON e29.CaseId = c.CaseId AND e29.EventId = 29 AND e29.Deleted = 0
LEFT JOIN t_case_events_map e37 ON e37.CaseId = c.CaseId AND e37.EventId = 37 AND e37.Deleted = 0
LEFT JOIN t_case_events_map e69 ON e69.CaseId = c.CaseId AND e69.EventId = 69 AND e69.Deleted = 0
WHERE c.Deleted = 0
AND c.CaseReference `+ isRefuse(caseReferenceTS) +` LIKE '%".@$caseReference."%'
AND SUBSTRING(c.CaseReference, 3, 5) `+ isRefuse(familyTS) +` LIKE '%".@$family."%'
AND n1.Reference `+ isRefuse(instructorRefTS) +` LIKE '%".@$instructorRef."%'
AND onm1.OfficialNo ` + isRefuse(appNoTS) + ` LIKE '%".@$appno."%'
` + ( parseInt(caseOfficeId) !== 0 ? ' AND c.CaseOfficeId = ' + caseOfficeId : '' ) + `
` + ( parseInt(caseTypeId) !== 0 ? ' AND c.CaseTypeId = ' + caseTypeId : '' ) + `
` + ( parseInt(countryId) !== 0 ? ' AND c.CountryId = ' + countryId : '' ) + `
` + ( parseInt(basisId) !== 0 ? ' AND c.BasisId = ' + basisId : '' ) + `
AND c.Title `+ isRefuse(titleTS) +`LIKE '%".@$title."%'
` + ( parseInt(typeOfMarkId) !== 0 ? ' AND c.BasisId = ' + typeOfMarkId : '' ) + `
` + ( caseStatusId !== undefined ? ' AND c.CaseStatusId IN (' + caseStatusId.join() + ')' : '' ) + `
) as b
";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['Instructorree'] = $row;
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
////$data['sql'] = $view;
return $data;
}
public function CaseService($request)
{
$caseReference = $request['caseReference'];
$family = $request['family'];
$title = $request['title'];
$profitCentre = $request['profitCentre'];
$taxTreatment = $request['taxTreatment'];
$caseType = $request['caseType'];
$caseStatus = $request['caseStatus'];
$startDate = $request['startDate'];
$endDate = $request['endDate'];
$nameType = $request['nameType'];
$name = $request['name'];
$officialNumbersType = $request['officialNumbersType'];
$officialNumbers = $request['officialNumbers'];
$count = "
T1.CaseId
, T1.CaseReference as CaseReference
, img.FilePath
, SUBSTRING(T1.CaseReference, 3, 5) as Family
, T1.Title as Title
, T1.CaseTypeId
, T3.CaseTypeName
, T4.CountryName as CountryName
, T5.CaseOfficeName as CaseOfficeName
, T6.BasisName as BasisName
, T7.CaseStatusName as CaseStatusName
, T8.ProfitCentreName as ProfitCentreName
, T9.BillingCurrencyCode as BillingCurrencyCode
, T10.TaxTreatmentName as TaxTreatmentName
, T11.TypeOfMarkName as TypeOfMarkName
, T1.PurchaseOrderNo
, T1.NoOfClaims
, T1.KeepOnTopNote
, T1.StatusActive
, nm1.NamesName as Instructor
, nm1.NamesCode as InstructorCode
, nm2.NamesName as Debtor
, nm2.NamesCode as DebtorCode
, nm3.NamesName as Owner
, nm3.NamesCode as OwnerCode
, nm4.NamesName as Agent
, nm4.NamesCode as AgentCode
, nm5.NamesName as Partner
, nm5.NamesCode as PartnerCode
, nm6.NamesName as Attorney
, nm6.NamesCode as AttorneyCode
, nm7.NamesName as Executive
, nm7.NamesCode as ExecutiveCode
, nm8.NamesName as Refferal
, nm8.NamesCode as RefferalCode
---, offn.OfficialNo as 'Official No.'
,concat( u1.Name ,' ', concat( u1.MiddleName ,' ', u1.Surname)) as CreateBy
,concat(u2.Name, ' ', concat(u2.MiddleName, ' ', u2.Surname)) as LastUpdateBy
,DATE_FORMAT(T1.CreateDateTime, '%Y-%m-%d %h:%m:%s %t') as CreateDateTime
,DATE_FORMAT(T1.LastUpdateDateTime, '%Y-%m-%d %h:%m:%s %t') as LastUpdateDateTime
";
$order = "";
$search = "
1=1
AND T1.StatusActive = 1
AND T1.Deleted = 0
AND img.Deleted = 0
AND n1.NamesTypeId = 1
AND n2.NamesTypeId = 2
AND n3.NamesTypeId = 3
AND n4.NamesTypeId = 4
AND n5.NamesTypeId = 5
AND n6.NamesTypeId = 6
AND n7.NamesTypeId = 7
AND n8.NamesTypeId = 8 ";
$name_table = "
t_case_management_trn T1
LEFT JOIN t_system_users u1 ON T1.CreateBy = u1.Id
LEFT JOIN t_system_users u2 ON T1.LastUpdateBy = u2.Id
LEFT JOIN t_case_type_mas T3 ON T1.CaseTypeId = T3.CaseTypeId
LEFT JOIN t_country_mas T4 ON T1.CountryId= T4.CountryId
LEFT JOIN t_case_office_mas T5 ON T1.CaseOfficeId = T5.CaseOfficeId
LEFT JOIN t_basis_mas T6 ON T1.BasisId = T6.BasisId
LEFT JOIN t_case_status_mas T7 ON T1.CaseStatusId = T7.CaseStatusId
LEFT JOIN t_profit_centre_mas T8 ON T1.ProfitCentreId = T8.ProfitCentreId
LEFT JOIN t_billing_currency_mas T9 ON T1.BillingCurrencyId = T9.BillingCurrencyId
LEFT JOIN t_tax_treatment_mas T10 ON T1.TaxTreatmentId = T10.TaxTreatmentId
LEFT JOIN t_type_of_mark_mas T11 ON T1.TypeOfMarkId = T11.TypeOfMarkId
` + ( (officialNumbers || '') !== '' ? 'LEFT JOIN t_case_official_number_map offn ON T1.CaseId = offn.CaseId': '') + `
LEFT JOIN t_case_names_map n1 ON n1.CaseId = T1.CaseId
LEFT JOIN t_names_mas nm1 ON n1.NamesId = nm1.NamesId
LEFT JOIN t_case_names_map n2 ON n2.CaseId = T1.CaseId
LEFT JOIN t_names_mas nm2 ON n2.NamesId = nm2.NamesId
LEFT JOIN t_case_names_map n3 ON n3.CaseId = T1.CaseId
LEFT JOIN t_names_mas nm3 ON n3.NamesId = nm3.NamesId
LEFT JOIN t_case_names_map n4 ON n4.CaseId = T1.CaseId
LEFT JOIN t_names_mas nm4 ON n4.NamesId = nm4.NamesId
LEFT JOIN t_case_names_map n5 ON n5.CaseId = T1.CaseId
LEFT JOIN t_names_mas nm5 ON n5.NamesId = nm5.NamesId
LEFT JOIN t_case_names_map n6 ON n6.CaseId = T1.CaseId
LEFT JOIN t_names_mas nm6 ON n6.NamesId = nm6.NamesId
LEFT JOIN t_case_names_map n7 ON n7.CaseId = T1.CaseId
LEFT JOIN t_names_mas nm7 ON n7.NamesId = nm7.NamesId
LEFT JOIN t_case_names_map n8 ON n8.CaseId = T1.CaseId
LEFT JOIN t_names_mas nm8 ON n8.NamesId = nm8.NamesId
LEFT JOIN t_case_images_map img ON img.CaseId = T1.CaseId";
if($officialNumbers == ''){
$search = $search . " AND offn.OfficialNo LIKE '%".@$officialNumbers."'%";
}
if(@$officialNumbersType > 0 && @$officialNumbers != ''){
$search = $search . " AND offn.OfficialNumberTypeId = '".@$officialNumbersType."'";
}
if(@$family){
$search = $search . " AND SUBSTRING(T1.CaseReference, 3, 5) LIKE '%" .@$family."%' ";
}
if(@$title){
$search = $search ." AND T1.Title LIKE '%".@$title."%' ";
}
if(@$caseReference){
$search = $search ." AND T1.CaseReference LIKE '%".@$caseReference."%' ";
}
if(@$profitCentre > 0){
$search = $search ." AND T1.ProfitCentreId = '" . @$profitCentre."'";
}
if(@$taxTreatment > 0){
$search = $search ." AND T1.TaxTreatmentId = '" . @$staxTreatment."'";
}
if(@$caseType > 0){
$search = $search ." AND T1.CaseTypeId = '" . $caseType."'";
}
if(@$caseStatus > 0){
$search = $search ." AND T1.CaseStatusId = '" . @$caseStatus ."'";
}
if(@$nameType > 0 && @$name != ''){
$search = $search ." AND nm" . @$nameType . ".NamesName . '{' + nm" . @$nameType . ".NamesCode + '}' LIKE '".@$name."'";
}
if(intval($nameType) == 0 && @$name != ''){
$search = $search . ' AND (
(nm1.NamesName {nm1.NamesCode} LIKE "%'.@$name.'%")
OR (nm2.NamesName {nm2.NamesCode} LIKE "%'.@$name.'%")
OR (nm3.NamesName {nm3.NamesCode} LIKE"%'.@$name.'%")
OR (nm4.NamesName {nm4.NamesCode} LIKE "%'.@$name.'%")
OR (nm5.NamesName {nm5.NamesCode} LIKE "%'.@$name.'%")
OR (nm6.NamesName {nm6.NamesCode} LIKE "%'.@$name.'%")
OR (nm7.NamesName {nm7.NamesCode} LIKE "%'.@$name.'%")
OR (nm8.NamesName {nm8.NamesCode} LIKE "%'.@$name.'%")
)';
}
if ($startDate != '' ) {
$search = $search . " AND T1.LastUpdateDateTime >= '" . $startDate . "'";
}
if ($endDate != '') {
$search = $search . " AND T1.LastUpdateDateTime <= '" . $endDate . "'";
}
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['Instructorree'] = $row;
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function CaseStatusService()
{
$count = " CaseStatusId as value, CaseStatusName as label";
$order = "";
$search = "StatusActive = '1'";
$name_table = "t_case_status_mas";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['value'] = $row['value'];
$data['Data'][$i]['label'] = $row['label'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function CaseTypeService()
{
$count = "CaseTypeId as value, CaseTypeName as label";
$order = "";
$search = "StatusActive = '1'";
$name_table = "t_case_type_mas";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['value'] = $row['value'];
$data['Data'][$i]['label'] = $row['label'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function ClassService($request)
{
$classesCode = $request['classesCode'];
$classesText = $request['classesText'];
$count = "
t_classes_mas.ClassesId
,t_classes_mas.ClassesCode
,t_classes_mas.ClassesTextTH
,t_classes_mas.ClassesTextEN
,t_classes_mas.StatusActive
,concat(u1.Name ,' ', concat( u1.MiddleName ,' ', u1.Surname)) as CreateBy
,concat(u2.Name, ' ', concat(u2.MiddleName, ' ', u2.Surname)) as LastUpdateBy
,t_classes_mas.CreateDateTime as CreateDateTime
,t_classes_mas.LastUpdateDateTime as LastUpdateDateTime
,t_classes_mas.Deleted
";
$order = "";
$search = "
t_classes_mas.ClassesCode LIKE '%".@$classesCode."%'
AND (t_classes_mas.ClassesTextTH LIKE '%".@$classesText."%'
OR t_classes_mas.ClassesTextEN LIKE '%".@$classesText."%')
AND t_classes_mas.Deleted = 0
order by t_classes_mas.ClassesCode";
$name_table = "t_classes_mas LEFT JOIN t_system_users u1 ON t_classes_mas.CreateBy = u1.Id
LEFT JOIN t_system_users u2 ON t_classes_mas.LastUpdateBy = u2.Id";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
//$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['ClassesId'] = $row['ClassesId'];
$data['Data'][$i]['ClassesCode'] = $row['ClassesCode'];
$data['Data'][$i]['ClassesTextTH'] = $row['ClassesTextTH'];
$data['Data'][$i]['ClassesTextEN'] = $row['ClassesTextEN'];
$data['Data'][$i]['StatusActive'] = $row['StatusActive'];
$data['Data'][$i]['CreateBy'] = $row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] = ($row['LastUpdateBy']=="")?"":$row['LastUpdateBy'];
$data['Data'][$i]['CreateDateTime'] = $row['CreateDateTime'];
$data['Data'][$i]['LastUpdateDateTime'] = ($row['LastUpdateDateTime']=="0000-00-00 00:00:00")?"":$row['LastUpdateDateTime'];
$data['Data'][$i]['Deleted'] = $row['Deleted'];
$i++;
}
}else{
$data['Data'] = '';
}
$sql_a = array('table'=>$name_table,'count'=>$count,'where'=>$search);
$view_a = $this->view($sql_a);
$result_a = $this->q_re($view_a);
$respons_count_num_a = mysqli_num_rows($result_a);
$i2=0;
if($respons_count_num_a > 0){
while ($row_a = $this->q_ro($result_a)) {
$data['Data_a'][$i2]['ClassesId'] = $row_a['ClassesId'];
$data['Data_a'][$i2]['ClassesCode'] = $row_a['ClassesCode'];
$data['Data_a'][$i2]['ClassesTextTH'] = $row_a['ClassesTextTH'];
$data['Data_a'][$i2]['ClassesTextEN'] = $row_a['ClassesTextEN'];
$data['Data_a'][$i2]['StatusActive'] = $row_a['StatusActive'];
$data['Data_a'][$i2]['CreateBy'] = $row_a['CreateBy'];
$data['Data_a'][$i2]['LastUpdateBy'] = $row_a['LastUpdateBy'];
$data['Data_a'][$i2]['CreateDateTime'] = $row_a['CreateDateTime'];
$data['Data_a'][$i2]['LastUpdateDateTime'] = $row_a['LastUpdateDateTime'];
$data['Data_a'][$i2]['Deleted'] = $row_a['Deleted'];
$i2++;
}
}else{
$data['Data_a'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function CountryService()
{
$count = "CountryId as value, CountryName as label";
$order = "Order by CountryName";
$search = "StatusActive = '1' AND Deleted = '0'";
$name_table = "t_country_mas";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['value'] = $row['value'];
$data['Data'][$i]['label'] = $row['label'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function DashBoardService0($request)
{
$count = "";
$order = "ORDER BY DueDate";
if(@$request['OrderDirection']!=''){
//$order = " ORDER BY ".$request['OrderBy'] ." ".strtoupper($request['OrderDirection']);
}
$search = "S_offn = 1";
$name_table = "
(
SELECT DATEDIFF(CURDATE(), ae.DueDate) AS countDate
,rank() OVER( PARTITION BY offn.CaseId ORDER BY offn.LastUpdateDateTime DESC ) AS S_offn
,ae.CompleteDate
,country.CountryName
,ae.CaseId
,DATE_FORMAT(ae.DueDate, '%Y-%m-%d') as DueDate
,c.Title as Title
,img.FilePath
,nMas.NamesName as Instructor1
,nMas.NamesCode as Instructor2
,c.CaseReference
,nMas2.NamesName as Owner1
,nMas2.NamesCode as Owner2
,offn.OfficialNo as AppNo
,ae.CreateDateTime as CreateDateTime
,eMas.EventsName
FROM t_case_events_map ae
LEFT JOIN t_events_mas eMas ON ae.EventId = eMas.EventsId
LEFT JOIN t_case_management_trn c ON ae.CaseId = c.CaseId
LEFT JOIN t_case_names_map n ON c.CaseId = n.CaseId
LEFT JOIN t_names_mas nMas ON n.NamesId = nMas.NamesId
LEFT JOIN t_case_names_map n2 ON c.CaseId = n2.CaseId
LEFT JOIN t_names_mas nMas2 ON n2.NamesId = nMas2.NamesId
LEFT JOIN t_case_images_map img ON img.CaseId = c.CaseId
LEFT JOIN t_case_official_number_map offn ON offn.CaseId = c.CaseId
LEFT JOIN t_country_mas country ON c.CountryId = country.CountryId
WHERE ae.DueDate <= NOW()+366
AND ae.DueDate > 0
AND c.CaseStatusId <> 4
AND c.CaseStatusId <> 5
AND ae.CompleteDate
AND n.NamesTypeId = 1
AND n2.NamesTypeId = 3
AND offn.OfficialNumberTypeId = 1
AND ae.Deleted = 0
AND img.Deleted = 0
) AS a
";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search,'order'=>$order);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['countDate'] = intval($row['countDate']);
$data['Data'][$i]['S_offn'] = $row['S_offn'];
$data['Data'][$i]['CompleteDate'] = $row['CompleteDate'];
$data['Data'][$i]['CountryName'] = $row['CountryName'];
$data['Data'][$i]['CaseId'] = $row['CaseId'];
$data['Data'][$i]['DueDate'] = $row['DueDate'];
$data['Data'][$i]['Title'] = $row['Title'];
$data['Data'][$i]['FilePath'] = $row['FilePath'];
$data['Data'][$i]['Instructor'] = $row['Instructor1']." {".$row['Instructor2']."}";
$data['Data'][$i]['CaseReference'] = $row['CaseReference'];
$data['Data'][$i]['Owner'] = $row['Owner1']." {".$row['Owner2']."}";
$data['Data'][$i]['AppNo'] = $row['AppNo'];
$data['Data'][$i]['CreateDateTime'] = $row['CreateDateTime'];
$sql_ClassList = array('table'=>'t_case_classes_map','count'=> 'cast(ClassesCode AS CHAR ) as ClassList','where'=>'CaseId = "'.$row['CaseId'].'" AND Deleted = 0');
$view_ClassList = $this->view($sql_ClassList);
$row_ClassList = $this->q($view_ClassList);
$data['Data'][$i]['ClassList'] = $row_ClassList['ClassList'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
//$data['view_ClassList'] = $view_ClassList ;
return $data;
}
public function DashBoardService($request)
{
$count = "DATEDIFF(CURDATE(), ae.DueDate) AS countDate,
ae.CompleteDate,
ae.CaseId,
DATE_FORMAT(ae.DueDate, '%Y-%m-%d') AS DueDate,
ae.CreateDateTime AS CreateDateTime,
c.Title AS Title,
c.CaseReference,
nMas2.NamesCode AS Owner2,
nMas2.NamesName AS Owner1,
country.CountryName,
nMas.NamesName AS Instructor1,
nMas.NamesCode AS Instructor2,
eMas.EventsName";
if(@$request['OrderDirection']!=''){
$order = " ORDER BY ".$request['OrderBy'] ." ".$request['OrderDirection'];
}
$search = "ae.DueDate <= DATE_ADD(CURDATE(), INTERVAL ".@$request['Pageday']." DAY)
AND ae.DueDate <> '0000-00-00'
AND c.CaseStatusId NOT IN (4, 5)
AND ae.CompleteDate IS NULL
AND n.NamesTypeId = 1
AND n2.NamesTypeId = 1
AND ae.Deleted = 0
AND ae.StatusActive = 1 ";
$name_table = "
t_case_events_map ae
INNER JOIN t_events_mas eMas ON ae.EventId = eMas.EventsId
INNER JOIN t_case_management_trn c ON ae.CaseId = c.CaseId
INNER JOIN t_case_names_map n ON c.CaseId = n.CaseId
INNER JOIN t_case_names_map n2 ON c.CaseId = n2.CaseId
INNER JOIN t_names_mas nMas ON n.NamesId = nMas.NamesId
INNER JOIN t_names_mas nMas2 ON n2.NamesId = nMas2.NamesId
INNER JOIN t_country_mas country ON c.CountryId = country.CountryId
";
//offn.OfficialNumberTypeId AS S_offn,
// offn.OfficialNo AS AppNo,
//AND offn.OfficialNumberTypeId = 1
//INNER JOIN t_case_official_number_map offn ON offn.CaseId = c.CaseId
// INNER JOIN t_case_images_map img ON img.CaseId = c.CaseId ,img.FilePath
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
if($request['PageSize']==''){
$respons_count_num = mysqli_num_rows($result);
}
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['countDate'] = intval($row['countDate']);
$data['Data'][$i]['CountryName'] = $row['CountryName'];
$data['Data'][$i]['EventsName'] = $row['EventsName'];
$data['Data'][$i]['Title'] = $row['Title'];
$data['Data'][$i]['Instructor'] = $row['Instructor1']." {".$row['Instructor2']."}";
$data['Data'][$i]['CaseReference'] = $row['CaseReference'];
$data['Data'][$i]['Owner'] = $row['Owner1']." {".$row['Owner2']."}";
$sql_app = array('table'=>'t_case_official_number_map','count'=> 'OfficialNo AS AppNo','where'=>'CaseId = "'.$row['CaseId'].'" AND OfficialNumberTypeId = "1"');
$view_app = $this->view($sql_app);
$row_app = $this->q($view_app);
$data['Data'][$i]['AppNo'] = $row_app['AppNo'];
$data['Data'][$i]['CompleteDate'] = $row['CompleteDate'];
$data['Data'][$i]['CaseId'] = $row['CaseId'];
$data['Data'][$i]['DueDate'] = $row['DueDate'];
$data['Data'][$i]['CreateDateTime'] = $row['CreateDateTime'];
$data['Data'][$i]['DueDate_color'] = "";
$date_day = date("Y-m-d");
if($row['DueDate'] <= $date_day ){
$data['Data'][$i]['DueDate_color'] = 'red';
}
$sql_ClassList = array('table'=>'t_case_classes_map','count'=> 'cast(ClassesCode AS CHAR ) as ClassList','where'=>'CaseId = "'.$row['CaseId'].'" AND Deleted = 0');
$view_ClassList = $this->view($sql_ClassList);
$row_ClassList = $this->q($view_ClassList);
$data['Data'][$i]['ClassList'] = $row_ClassList['ClassList'];
$sql_img = array('table'=>'t_case_images_map','count'=> 'FilePath','where'=>'CaseId = "'.$row['CaseId'].'"');
$view_img = $this->view($sql_img);
$row_img = $this->q($view_img);
$data['Data'][$i]['FilePath'] = $row_img['FilePath'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['PageDay'] = $request['Pageday'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function DashBoardServiceExport($request)
{
$count = "DATEDIFF(CURDATE(), ae.DueDate) AS countDate,
offn.OfficialNumberTypeId AS S_offn,
ae.CompleteDate,
ae.CaseId,
DATE_FORMAT(ae.DueDate, '%Y-%m-%d') AS DueDate,
ae.CreateDateTime AS CreateDateTime,
c.Title AS Title,
c.CaseReference,
nMas2.NamesCode AS Owner2,
nMas2.NamesName AS Owner1,
country.CountryName,
nMas.NamesName AS Instructor1,
nMas.NamesCode AS Instructor2,
offn.OfficialNo AS AppNo,
eMas.EventsName";
if(@$request['OrderDirection']!=''){
$order = " ORDER BY ".$request['OrderBy'] ." ".$request['OrderDirection'];
}
$search = "ae.DueDate <= DATE_ADD(CURDATE(), INTERVAL ".@$request['Pageday']." DAY)
AND ae.DueDate <> '0000-00-00'
AND c.CaseStatusId NOT IN (4, 5)
AND ae.CompleteDate IS NULL
AND n.NamesTypeId = 1
AND n2.NamesTypeId = 1
AND offn.OfficialNumberTypeId = 1
AND ae.Deleted = 0
AND ae.StatusActive = 1 ";
$name_table = "
t_case_events_map ae
INNER JOIN t_events_mas eMas ON ae.EventId = eMas.EventsId
INNER JOIN t_case_management_trn c ON ae.CaseId = c.CaseId
INNER JOIN t_case_names_map n ON c.CaseId = n.CaseId
INNER JOIN t_case_names_map n2 ON c.CaseId = n2.CaseId
INNER JOIN t_case_official_number_map offn ON offn.CaseId = c.CaseId
INNER JOIN t_names_mas nMas ON n.NamesId = nMas.NamesId
INNER JOIN t_names_mas nMas2 ON n2.NamesId = nMas2.NamesId
INNER JOIN t_country_mas country ON c.CountryId = country.CountryId
";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search,'order'=>$order);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['countDate'] = intval($row['countDate']);
$data['Data'][$i]['S_offn'] = $row['S_offn'];
$data['Data'][$i]['CompleteDate'] = $row['CompleteDate'];
$data['Data'][$i]['CountryName'] = $row['CountryName'];
$data['Data'][$i]['CaseId'] = $row['CaseId'];
$data['Data'][$i]['DueDate'] = $row['DueDate'];
$data['Data'][$i]['Title'] = $row['Title'];
$data['Data'][$i]['FilePath'] = $row['FilePath'];
$data['Data'][$i]['Instructor'] = $row['Instructor1'];
$data['Data'][$i]['Instructor2'] = $row['Instructor2'];
$data['Data'][$i]['CaseReference'] = $row['CaseReference'];
$data['Data'][$i]['Owner'] = $row['Owner1']." {".$row['Owner2']."}";
$data['Data'][$i]['AppNo'] = $row['AppNo'];
$data['Data'][$i]['CreateDateTime'] = $row['CreateDateTime'];
$sql_ClassList = array('table'=>'t_case_classes_map','count'=> 'cast(ClassesCode AS CHAR ) as ClassList','where'=>'CaseId = "'.$row['CaseId'].'" AND Deleted = 0');
$view_ClassList = $this->view($sql_ClassList);
$row_ClassList = $this->q($view_ClassList);
$data['Data'][$i]['ClassList'] = $row_ClassList['ClassList'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['numrows'] = $respons_count_num;
////$data['sql'] = $view;
//$data['view_ClassList'] = $view_ClassList ;
return $data;
}
public function OfficialNumberTypeService()
{
$count = "OfficialNumberTypeId as value, OfficialNumberTypeName as label ";
$order = "";
$search = "StatusActive = '1' AND Deleted = '0'";
$name_table = "t_official_number_type_mas";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['value'] = $row['value'];
$data['Data'][$i]['label'] = $row['label'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function casetypeddl() //Get Case Type for drop down list
{
$count = "CaseTypeId as value, CaseTypeName as label";
$order = "";
$search = "StatusActive = 1 AND Deleted = 0 ";
$name_table = "t_case_type_mas";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['value'] = $row['value'];
$data['Data'][$i]['label'] = $row['label'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function typeofmarkddl()
{
$count = "TypeOfMarkId as value, TypeOfMarkName as label";
$order = "";
$search = " StatusActive = 1 AND Deleted = 0 ";
$name_table = "t_type_of_mark_mas";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['value'] = $row['value'];
$data['Data'][$i]['label'] = $row['label'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function RelatedCaseService($request)
{
$caseId = $request['CaseId'];
$family = $request['Family'];
$caseReference = $request['CaseReference'];
$CaseType = $request['CaseType'];
$Basis = $request['Basis'];
$TypeofMark = $request['TypeofMark'];
$Title = $request['Title'];
$count = " T1.CaseId
, T1.CaseReference as CaseReference
, T1.Family as Family
, T1.Title as Title
, T3.CaseTypeName
, T4.CountryName as CountryName
, T5.CaseOfficeName as CaseOfficeName
, T6.BasisName as BasisName
, T1.CaseStatusId as CaseStatusId
, T7.CaseStatusName as CaseStatusName
, T8.ProfitCentreName as ProfitCentreName
, T9.BillingCurrencyCode as BillingCurrencyCode
, T10.TaxTreatmentName as TaxTreatmentName
, T11.TypeOfMarkName as TypeOfMarkName
, T1.CaseTypeId as caseTypeId
, T1.typeOfMarkId as typeOfMarkId
, T1.basisId as basisId
, T1.PurchaseOrderNo
, T1.NoOfClaims
, T1.KeepOnTopNote
, T1.StatusActive
,concat(u1.Name, ' ',concat(u1.MiddleName, ' ', u1.Surname)) as CreateBy
,concat(u2.Name, ' ', concat(u2.MiddleName, ' ', u2.Surname)) as LastUpdateBy
,T1.CreateDateTime as CreateDateTime
,T1.LastUpdateDateTime as LastUpdateDateTime";
$order = "";
$search = " 1=1
AND T1.StatusActive = 1
AND T1.Deleted = 0
AND T1.CaseId <> '".@$caseId."'
AND T1.CaseId NOT IN
(SELECT T1.CaseId FROM t_case_related_map T
LEFT JOIN t_case_management_trn T1 ON T.CaseId = T1.CaseId
WHERE T.StatusActive = 1
AND T.Deleted = 0
AND T.CaseRefId = '".@$caseId."')
";
/*AND T1.CaseReference LIKE '%".$caseReference."%'
AND T3.CaseTypeId LIKE '%".$CaseType."%'
AND T6.BasisId LIKE '%".$Basis."%'
AND T11.TypeOfMarkId LIKE '%".$TypeofMark."%'
AND T1.Title LIKE '%".$Title."%'*/
$name_table = "t_case_management_trn T1
LEFT JOIN t_case_type_mas T3 ON T1.CaseTypeId = T3.CaseTypeId
LEFT JOIN t_country_mas T4 ON T1.CountryId= T4.CountryId
LEFT JOIN t_case_office_mas T5 ON T1.CaseOfficeId = T5.CaseOfficeId
LEFT JOIN t_basis_mas T6 ON T1.BasisId = T6.BasisId
LEFT JOIN t_case_status_mas T7 ON T1.CaseStatusId = T7.CaseStatusId
LEFT JOIN t_profit_centre_mas T8 ON T1.ProfitCentreId = T8.ProfitCentreId
LEFT JOIN t_billing_currency_mas T9 ON T1.BillingCurrencyId = T9.BillingCurrencyId
LEFT JOIN t_tax_treatment_mas T10 ON T1.TaxTreatmentId = T10.TaxTreatmentId
LEFT JOIN t_type_of_mark_mas T11 ON T1.TypeOfMarkId = T11.TypeOfMarkId
LEFT JOIN t_system_users u1 ON T1.CreateBy = u1.Id
LEFT JOIN t_system_users u2 ON T1.LastUpdateBy = u2.Id";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['BasisName'] = $row['BasisName'];
$data['Data'][$i]['BillingCurrencyCode'] = $row['BillingCurrencyCode'];
$data['Data'][$i]['CaseId'] = $row['CaseId'];
$data['Data'][$i]['CaseOfficeName'] = $row['CaseOfficeName'];
$data['Data'][$i]['CaseReference'] = $row['CaseReference'];
$data['Data'][$i]['CaseStatusId'] = $row['CaseStatusId'];
$data['Data'][$i]['CaseStatusName'] = $row['CaseStatusName'];
$data['Data'][$i]['CaseTypeName'] = $row['CaseTypeName'];
$data['Data'][$i]['CountryName'] = $row['CountryName'];
$data['Data'][$i]['CreateBy'] = $row['CreateBy'];
$data['Data'][$i]['CreateDateTime'] = $row['CreateDateTime'];
$data['Data'][$i]['Family'] = $row['Family'];
$data['Data'][$i]['KeepOnTopNote'] = $row['KeepOnTopNote'];
$data['Data'][$i]['LastUpdateBy'] = $row['LastUpdateBy'];
$data['Data'][$i]['LastUpdateDateTime'] = $row['LastUpdateDateTime'];
$data['Data'][$i]['NoOfClaims'] = $row['NoOfClaims'];
$data['Data'][$i]['ProfitCentreName'] = $row['ProfitCentreName'];
$data['Data'][$i]['PurchaseOrderNo'] = $row['PurchaseOrderNo'];
$data['Data'][$i]['StatusActive'] = $row['StatusActive'];
$data['Data'][$i]['TaxTreatmentName'] = $row['TaxTreatmentName'];
$data['Data'][$i]['Title'] = $row['Title'];
$data['Data'][$i]['TypeOfMarkName'] = $row['TypeOfMarkName'];
$data['Data'][$i]['basisId'] = $row['basisId'];
$data['Data'][$i]['caseTypeId'] = $row['caseTypeId'];
$data['Data'][$i]['typeOfMarkId'] = $row['typeOfMarkId'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function eventsddl($Id)
{
$caseTypeId = $Id;
$count = "T.EventId as value, mas.EventsName as label ,mas.HasDeadLine ";
$order = "ORDER BY CASE
WHEN mas.EventsName LIKE 'Annuity Fees (2%th) Deadline' THEN 'Annuity Fees (2'
WHEN mas.EventsName LIKE 'Annuity Fees (1%th) Deadline' THEN 'Annuity Fees (1'
WHEN mas.EventsName LIKE 'Annuity Fees (%th) Deadline' THEN 'Annuity Fees (0'
ELSE mas.EventsName
END, mas.EventsName";
$search = "T.CaseTypeId = '".@$caseTypeId."'";
$name_table = "t_case_type_events_map T
LEFT JOIN t_events_mas mas ON mas.EventsId = T.EventId";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search,'order'=> $order);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['HasDeadLine'] = $row['HasDeadLine'];
$data['Data'][$i]['label'] = $row['label'];
$data['Data'][$i]['value'] = $row['value'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
$data['caseTypeId'] = $caseTypeId;
return $data;
}
public function caseeventmap($Id)
{
$caseId = $Id;
$count = "ac.CaseEventId
,e.EventsName
,DATE_FORMAT(ac.DueDate, '%Y-%m-%d') as DueDate
,DATE_FORMAT(ac.CompleteDate, '%Y-%m-%d') as CompleteDate
,DATE_FORMAT(ac.OccurredDate, '%Y-%m-%d') as OccurredDate
,e.HasDeadLine
,ac.Remark";
$order = "";
$search = "ac.CaseId = '".$caseId."' AND ac.Deleted = '0'";
$name_table = "t_case_events_map ac
LEFT JOIN t_events_mas e ON ac.EventId = e.EventsId";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseEventId'] = $row['CaseEventId'];
$data['Data'][$i]['EventsName'] = $row['EventsName'];
$data['Data'][$i]['DueDate'] = $row['DueDate'];
$data['Data'][$i]['CompleteDate'] = $row['CompleteDate'];
$data['Data'][$i]['OccurredDate'] = $row['OccurredDate'];
$data['Data'][$i]['HasDeadLine'] = $row['HasDeadLine'];
$data['Data'][$i]['Remark'] = $row['Remark'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function events()
{
$count = "em.EventsId
,em.EventsName
,em.StatusActive
,concat( u1.Name ,' ', concat( u1.MiddleName ,' ', u1.Surname)) as CreateBy
,concat(u2.Name, ' ', concat(u2.MiddleName, ' ', u2.Surname)) as LastUpdateBy
,DATE_FORMAT(em.CreateDateTime, '%Y-%m-%d') as CreateDateTime
,DATE_FORMAT(em.LastUpdateDateTime, '%Y-%m-%d') as LastUpdateDateTime
,em.HasDeadLine
,em.Deleted
,(
SELECT ct.Deleted
FROM t_case_events_map ct
WHERE ( EventId = em.EventsId AND ct.Deleted = 0 )
OR em.EventsId IN(37, 29, 51, 69, 30, 40)
ORDER BY ct.CaseEventId DESC LIMIT 1
) as cantDelete";
$order = "ORDER BY em.CreateDateTime DESC";
$search = "em.Deleted = 0 ";
$name_table = "t_events_mas em
LEFT JOIN t_system_users u1 ON em.CreateBy = u1.Id
LEFT JOIN t_system_users u2 ON em.LastUpdateBy = u2.Id";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CreateBy'] = $row['CreateBy'];
$data['Data'][$i]['CreateDateTime'] = $row['CreateDateTime'];
$data['Data'][$i]['Deleted'] = $row['Deleted'];
$data['Data'][$i]['EventsId'] = $row['EventsId'];
$data['Data'][$i]['EventsName'] = $row['EventsName'];
$data['Data'][$i]['HasDeadLine'] = $row['HasDeadLine'];
$data['Data'][$i]['LastUpdateBy'] = $row['LastUpdateBy'];
$data['Data'][$i]['LastUpdateDateTime'] = $row['LastUpdateDateTime'];
$data['Data'][$i]['StatusActive'] = $row['StatusActive'];
$data['Data'][$i]['cantDelete'] = $row['cantDelete'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function sss2($Id)
{
$caseId = $Id;
$count = "";
$order = "";
$search = "";
$name_table = "";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['Instructorree'] = $row;
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function ReportRenewalService($Id)
{
$caseId = $Id;
$count = "";
$order = "";
$search = " n.DIF = 1 AND n.CDT = 1";
$name_table = "
(
SELECT T2.Reference AS Instructorree
,rank() over (partition by T8.CaseId order by T8.DateInForce) as DIF
,rank() over (partition by T8.CaseId order by T18.CreateDateTime DESC) as CDT
,T18.CreateDateTime
,T1.CaseReference
,FORMAT(SYSDATE(), 'dd MMMM yyyy') AS Docgendate
,T3.NamesName AS Instructorname
,T3.PostalAddress AS Instructoraddress
,T1.Title
,T5.NamesName AS Ownername
,T5.PostalAddress AS Owneraddress
,T6.Countryname
,T7.FilePath
,(SELECT S1.ClassesCode
FROM t_case_classes_map S1
WHERE S1.CASEID = '".@$caseId."'
ORDER BY S1.ClassesCode
) AS Classes
,T8.OfficialNo AS Applicationnumber
,FORMAT(T9.OccurredDate, 'dd MMMM yyyy') AS Applicationfilingdate
,T15.OfficialNo AS Registrationnumber
,FORMAT(T16.OccurredDate, 'dd MMMM yyyy') AS Registrationdate
,FORMAT(T17.OccurredDate, 'dd MMMM yyyy') AS Issuancedate
,FORMAT(T18.DueDate, 'dd MMMM yyyy') AS Renewalduedate
,FORMAT(DATE_ADD(DATE_ADD(T18.DueDate, INTERVAL -3 MONTH), INTERVAL -1 DAY), 'dd MMMM yyyy') AS Renewalbeforeduedate
,T12.NamesName AS Partername
,T14.NamesName AS Attorneyname
FROM t_case_management_trn T1
LEFT JOIN t_case_names_map T2 ON T1.CaseId = T2.CaseId
AND T2.NamesTypeId = 1 AND T2.Deleted = 0
LEFT JOIN t_names_mas T3 ON T2.NamesId = T3.NamesId
LEFT JOIN t_case_names_map T4 ON T1.CaseId = T4.CaseId
AND T4.NamesTypeId = 3 AND T4.Deleted = 0
LEFT JOIN t_names_mas T5 ON T4.NamesId = T5.NamesId
LEFT JOIN t_country_mas T6 ON T1.CountryId = T6.CountryId
LEFT JOIN t_case_images_map T7 ON T1.CaseId = T7.CaseId
AND T7.Deleted = 0
LEFT JOIN t_case_official_number_map T8 ON T1.CaseId = T8.CaseId
AND T8.OfficialNumberTypeId = 1 AND T8.Deleted = 0
LEFT JOIN t_case_events_map T9 ON T1.CaseId = T9.CaseId
AND T9.EventId = 37 AND T9.Deleted = 0
LEFT JOIN t_events_mas T10 ON T9.EventId = T10.EventsId
AND T10.Deleted = 0
LEFT JOIN t_case_names_map T11 ON T1.CaseId = T11.CaseId
AND T11.NamesTypeId = 5 AND T11.Deleted = 0
LEFT JOIN t_names_mas T12 ON T11.NamesId = T12.NamesId
LEFT JOIN t_case_names_map T13 ON T1.CaseId = T13.CaseId
AND T13.NamesTypeId = 6 AND T13.Deleted = 0
LEFT JOIN t_names_mas T14 ON T14.NamesId = T13.NamesId
LEFT JOIN t_case_official_number_map T15 ON T1.CaseId = T15.CaseId
AND T15.OfficialNumberTypeId = 2 AND T15.Deleted = 0
LEFT JOIN t_case_events_map T16 ON T1.CaseId = T16.CaseId
AND T16.EventId = 29 AND T16.Deleted = 0
LEFT JOIN t_case_events_map T17 ON T1.CaseId = T17.CaseId
AND T17.EventId = 60 AND T17.Deleted = 0
LEFT JOIN t_case_events_map T18 ON T1.CaseId = T18.CaseId
AND T18.EventId = 51 AND T18.Deleted = 0
WHERE T1.CaseId = '".@$caseId."'
) as n
";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['Instructorree'] = $row['Instructorree'];
$data['Data'][$i]['DIF'] = $row['DIF'];
$data['Data'][$i]['CDT'] = $row['CDT'];
$data['Data'][$i]['CreateDateTime'] = $row['CreateDateTime'];
$data['Data'][$i]['CaseReference'] = $row['CaseReference'];
$data['Data'][$i]['Docgendate'] = $row['Docgendate'];
$data['Data'][$i]['Instructorname'] = $row['Instructorname'];
$data['Data'][$i]['Title'] = $row['Title'];
$data['Data'][$i]['Ownername'] = $row['Ownername'];
$data['Data'][$i]['Owneraddress'] = $row['Owneraddress'];
$data['Data'][$i]['Countryname'] = $row['Countryname'];
$data['Data'][$i]['FilePath'] = $row['FilePath'];
$data['Data'][$i]['Classes'] = $row['Classes'];
$data['Data'][$i]['Applicationnumber'] = $row['Applicationnumber'];
$data['Data'][$i]['Applicationfilingdate'] = $row['Applicationfilingdate'];
$data['Data'][$i]['Registrationnumber'] = $row['Registrationnumber'];
$data['Data'][$i]['Registrationdate'] = $row['Registrationdate'];
$data['Data'][$i]['Renewalduedate'] = $row['Renewalduedate'];
$data['Data'][$i]['Renewalbeforeduedate'] = $row['Renewalbeforeduedate'];
$data['Data'][$i]['Partername'] = $row['Partername'];
$data['Data'][$i]['Attorneyname'] = $row['Attorneyname'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function LoadIpmodules($request)
{
$count = "";
$name_table = "t_case_events_map";
$search = "`StartDate` != '' AND `DueDate` != ''";
if(@$request['CaseId']!=''){
$search = $search." AND CaseId = '".$request['CaseId'] ."'";
}
$order = "";
if(@$request['OrderDirection']!=''){
$order = " ORDER BY ".$request['OrderBy'] ." ".$request['OrderDirection'];
}
$sql = array('table'=>$name_table,'where'=>$search);
if(@$request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$sql9 = array('table'=>'t_case_events_map','where'=>'EventId = "'.$row['EventId'].'"');
$view9 = $this->view($sql9);
$row9 = $this->q($view9);
if(intval($row9['DueDate']) > 0){
$sql8 = array('table'=>'t_case_management_trn','where'=>'CaseId = "'.$row['CaseId'].'"');
$view8 = $this->view($sql8);
$row8 = $this->q($view8);
$data['Data'][$i]['DueDate'] =$row9['DueDate'];
$data['Data'][$i]['EventId'] =$row['EventId'];
$data['Data'][$i]['TextEvents'] =$row['EventsName'];
$data['Data'][$i]['BasisId'] =$row8['BasisId'];
$data['Data'][$i]['BillingCurrencyId'] =$row8['BillingCurrencyId'];
$data['Data'][$i]['CaseId'] =$row['CaseId'];
$data['Data'][$i]['CaseOfficeId'] =$row8['CaseOfficeId'];
$data['Data'][$i]['CaseReference'] =$row8['CaseReference'];
$data['Data'][$i]['CaseStatusId'] =$row8['CaseStatusId'];
$data['Data'][$i]['CaseTypeId'] =$row8['CaseTypeId'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['CustomerId'] =$row8['CustomerId'];
$data['Data'][$i]['Deleted'] =$row['Deleted'];
$data['Data'][$i]['Family'] =$row8['Family'];
$data['Data'][$i]['KeepOnTopNote'] =$row8['KeepOnTopNote'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['NoInSeries'] =$row8['NoInSeries'];
$data['Data'][$i]['NoOfClaims'] =$row8['NoOfClaims'];
$data['Data'][$i]['ProfitCentreId'] =$row8['ProfitCentreId'];
$data['Data'][$i]['PurchaseOrderNo'] =$row8['PurchaseOrderNo'];
$data['Data'][$i]['Remark'] =$row8['Remark'];
$data['Data'][$i]['StatusActive'] =$row8['StatusActive'];
$data['Data'][$i]['TaxTreatmentId'] =$row8['TaxTreatmentId'];
$data['Data'][$i]['Title'] =$row8['Title'];
$data['Data'][$i]['TypeOfMarkId'] =$row8['TypeOfMarkId'];
$sql2 = array('table'=>'t_case_images_map','where'=>'CaseId = "'.$row['CaseId'].'"');
$view2 = $this->view($sql2);
$row2 = $this->q($view2);
$data['Data'][$i]['FileName'] =$row2['FileName'];
$data['Data'][$i]['FilePath'] =$row2['FilePath'];
$data['Data'][$i]['FileNameOriginal'] =$row2['FileNameOriginal'];
$sql3 = array('table'=>'t_events_mas','where'=>'EventsId = "'.$row['EventId'].'"');
$view3 = $this->view($sql3);
$row3 = $this->q($view3);
$data['Data'][$i]['EventsName'] =$row3['EventsName'];
$sql4 = array('table'=>'t_case_names_map','where'=>'CaseId = "'.$row['CaseId'].'"');
$view4 = $this->view($sql4);
$row4 = $this->q($view4);
$sql5 = array('table'=>'t_names_mas','where'=>'NamesId = "'.$row4['NamesId'].'"');
$view5 = $this->view($sql5);
$row5 = $this->q($view5);
$data['Data'][$i]['NamesName'] =$row5['NamesName'];
$data['Data'][$i]['NamesCode'] =$row5['NamesCode'];
$sql6 = array('table'=>'t_country_mas','where'=>'CountryId = "'.$row8['CountryId'].'"');
$view6 = $this->view($sql6);
$row6 = $this->q($view6);
$data['Data'][$i]['CountryShortName'] =$row6['CountryShortName'];
$data['Data'][$i]['CountryName'] =$row6['CountryName'];
$sql7 = array('table'=>'t_type_of_mark_mas','where'=>'TypeOfMarkId = "'.$row8['TypeOfMarkId'].'"');
$view7 = $this->view($sql7);
$row7 = $this->q($view7);
$data['Data'][$i]['TypeOfMarkName'] =$row7['TypeOfMarkName'];
$sql10 = array('table'=>'t_case_official_number_map','where'=>'CaseId = "'.$row['CaseId'].'" AND OfficialNumberTypeId = 1');
$view10 = $this->view($sql10);
$row10 = $this->q($view10);
$data['Data'][$i]['OfficialNo'] =$row10['OfficialNo'];
$sql11 = array('table'=>'t_case_classes_map','where'=>'CaseId = "'.$row['CaseId'].'"');
$view11 = $this->view($sql11);
$row11 = $this->q($view11);
$data['Data'][$i]['ClassesCode'] =$row11['ClassesCode'];
$sql12 = array('table'=>'t_basis_mas','where'=>'BasisId = "'.$row8['BasisId'].'"');
$view12 = $this->view($sql12);
$row12 = $this->q($view12);
$data['Data'][$i]['BasisName'] =$row12['BasisName'];
$sql13 = array('table'=>'t_case_type_mas','where'=>'CaseTypeId = "'.$row8['CaseTypeId'].'"');
$view13 = $this->view($sql13);
$row13 = $this->q($view13);
$data['Data'][$i]['CaseTypeName'] =$row13['CaseTypeName'];
$sql14 = array('table'=>'t_case_office_mas','where'=>'CaseOfficeId = "'.$row8['CaseOfficeId'].'"');
$view14 = $this->view($sql14);
$row14 = $this->q($view14);
$data['Data'][$i]['CaseOfficeCode'] =$row14['CaseOfficeCode'];
$data['Data'][$i]['CaseOfficeName'] =$row14['CaseOfficeName'];
$i++;
}
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function LoadIpmodulesCsv($userId)
{
$count = "";
$name_table = "t_case_events_map";
$search = "`StartDate` != '' AND `DueDate` != '' AND `CreateBy` = '".$userId."'";
$order = " ORDER BY DueDate ASC";
$sql = array('table'=>$name_table,'where'=>$search, 'order'=>$order);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$sql9 = array('table'=>'t_case_events_map','where'=>'EventId = "'.$row['EventId'].'"');
$view9 = $this->view($sql9);
$row9 = $this->q($view9);
if(intval($row9['DueDate']) > 0){
$sql8 = array('table'=>'t_case_management_trn','where'=>'CaseId = "'.$row['CaseId'].'"');
$view8 = $this->view($sql8);
$row8 = $this->q($view8);
$data['Data'][$i]['DueDate'] =$row9['DueDate'];
$data['Data'][$i]['EventId'] =$row['EventId'];
$data['Data'][$i]['TextEvents'] =$row['EventsName'];
$data['Data'][$i]['BasisId'] =$row8['BasisId'];
$data['Data'][$i]['BillingCurrencyId'] =$row8['BillingCurrencyId'];
$data['Data'][$i]['CaseId'] =$row['CaseId'];
$data['Data'][$i]['CaseOfficeId'] =$row8['CaseOfficeId'];
$data['Data'][$i]['CaseReference'] =$row8['CaseReference'];
$data['Data'][$i]['CaseStatusId'] =$row8['CaseStatusId'];
$data['Data'][$i]['CaseTypeId'] =$row8['CaseTypeId'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['CustomerId'] =$row8['CustomerId'];
$data['Data'][$i]['Deleted'] =$row['Deleted'];
$data['Data'][$i]['Family'] =$row8['Family'];
$data['Data'][$i]['KeepOnTopNote'] =$row8['KeepOnTopNote'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['NoInSeries'] =$row8['NoInSeries'];
$data['Data'][$i]['NoOfClaims'] =$row8['NoOfClaims'];
$data['Data'][$i]['ProfitCentreId'] =$row8['ProfitCentreId'];
$data['Data'][$i]['PurchaseOrderNo'] =$row8['PurchaseOrderNo'];
$data['Data'][$i]['Remark'] =$row8['Remark'];
$data['Data'][$i]['StatusActive'] =$row8['StatusActive'];
$data['Data'][$i]['TaxTreatmentId'] =$row8['TaxTreatmentId'];
$data['Data'][$i]['Title'] =$row8['Title'];
$data['Data'][$i]['TypeOfMarkId'] =$row8['TypeOfMarkId'];
$sql2 = array('table'=>'t_case_images_map','where'=>'CaseId = "'.$row['CaseId'].'"');
$view2 = $this->view($sql2);
$row2 = $this->q($view2);
$data['Data'][$i]['FileName'] =$row2['FileName'];
$data['Data'][$i]['FilePath'] =$row2['FilePath'];
$data['Data'][$i]['FileNameOriginal'] =$row2['FileNameOriginal'];
$sql3 = array('table'=>'t_events_mas','where'=>'EventsId = "'.$row['EventId'].'"');
$view3 = $this->view($sql3);
$row3 = $this->q($view3);
$data['Data'][$i]['EventsName'] =$row3['EventsName'];
$sql4 = array('table'=>'t_case_names_map','where'=>'CaseId = "'.$row['CaseId'].'"');
$view4 = $this->view($sql4);
$row4 = $this->q($view4);
$sql5 = array('table'=>'t_names_mas','where'=>'NamesId = "'.$row4['NamesId'].'"');
$view5 = $this->view($sql5);
$row5 = $this->q($view5);
$data['Data'][$i]['NamesName'] =$row5['NamesName'];
$data['Data'][$i]['NamesCode'] =$row5['NamesCode'];
$sql6 = array('table'=>'t_country_mas','where'=>'CountryId = "'.$row8['CountryId'].'"');
$view6 = $this->view($sql6);
$row6 = $this->q($view6);
$data['Data'][$i]['CountryShortName'] =$row6['CountryShortName'];
$data['Data'][$i]['CountryName'] =$row6['CountryName'];
$sql7 = array('table'=>'t_type_of_mark_mas','where'=>'TypeOfMarkId = "'.$row8['TypeOfMarkId'].'"');
$view7 = $this->view($sql7);
$row7 = $this->q($view7);
$data['Data'][$i]['TypeOfMarkName'] =$row7['TypeOfMarkName'];
$sql10 = array('table'=>'t_case_official_number_map','where'=>'CaseId = "'.$row['CaseId'].'" AND OfficialNumberTypeId = 1');
$view10 = $this->view($sql10);
$row10 = $this->q($view10);
$data['Data'][$i]['OfficialNo'] =$row10['OfficialNo'];
$sql11 = array('table'=>'t_case_classes_map','where'=>'CaseId = "'.$row['CaseId'].'"');
$view11 = $this->view($sql11);
$row11 = $this->q($view11);
$data['Data'][$i]['ClassesCode'] =$row11['ClassesCode'];
$sql12 = array('table'=>'t_basis_mas','where'=>'BasisId = "'.$row8['BasisId'].'"');
$view12 = $this->view($sql12);
$row12 = $this->q($view12);
$data['Data'][$i]['BasisName'] =$row12['BasisName'];
$sql13 = array('table'=>'t_case_type_mas','where'=>'CaseTypeId = "'.$row8['CaseTypeId'].'"');
$view13 = $this->view($sql13);
$row13 = $this->q($view13);
$data['Data'][$i]['CaseTypeName'] =$row13['CaseTypeName'];
$sql14 = array('table'=>'t_case_office_mas','where'=>'CaseOfficeId = "'.$row8['CaseOfficeId'].'"');
$view14 = $this->view($sql14);
$row14 = $this->q($view14);
$data['Data'][$i]['CaseOfficeCode'] =$row14['CaseOfficeCode'];
$data['Data'][$i]['CaseOfficeName'] =$row14['CaseOfficeName'];
$i++;
}
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function IpmodulesCase($request)
{
//SELECT * FROM `t_events_mas` WHERE `HasDeadLine` = 1 ORDER BY `t_events_mas`.`CreateDateTime` ASC
$count ="";
$name_table = "t_case_management_trn";
$search = "Deleted=0";
if(@$request['CaseId']!=''){
$search = $search." AND CaseId = '".$request['CaseId'] ."'";
}
if(@$request['CaseReference']!=''){
$search = $search." AND CaseReference = '".$request['CaseReference'] ."'";
}
if(@$request['Family']!=''){
$search = $search." AND Family = '".$request['Family'] ."'";
}
if(@$request['Title']!=''){
$search = $search." AND Title LIKE '%".$request['Title'] ."%'";
}
if(@$request['ProfitCentre']!=''){
$search = $search." AND ProfitCentreId = '".$request['ProfitCentre'] ."'";
}
if(@$request['TaxTreatment']!=''){
$search = $search." AND TaxTreatmentId = '".$request['TaxTreatment'] ."'";
}
if(@$request['CaseTypeId']!=''){
$search = $search." AND CaseTypeId = '".$request['CaseTypeId'] ."'";
}
if(@$request['CaseStatus']!=''){
$search = $search." AND CaseStatusId = '".$request['CaseStatus'] ."'";
}
if(@$request['EventDateStart']!=''){
$search = $search." AND CreateDateTime >= '".$request['EventDateStart'] ."'";
}
if(@$request['EventDateEnd']!=''){
$search = $search." AND CreateDateTime <= '".$request['EventDateStart'] ."'";
}
if(@$request['Name']!=''){
//$search = $search." AND CaseId = '".$request['CaseId'] ."'";
}
if(@$request['OfficialNumbers']!=''){
//$search = $search." AND CaseId = '".$request['CaseId'] ."'";
}
$order = "";
if($request['OrderDirection']!=''){
$order = " ORDER BY ".$request['OrderBy'] ." ".$request['OrderDirection'];
}
$sql = array('table'=>$name_table,'where'=>$search,'order'=> $order);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['BasisId'] =$row['BasisId'];
$data['Data'][$i]['BillingCurrencyId'] =$row['BillingCurrencyId'];
$data['Data'][$i]['CaseId'] =$row['CaseId'];
$data['Data'][$i]['CaseOfficeId'] =$row['CaseOfficeId'];
$data['Data'][$i]['CaseReference'] =$row['CaseReference'];
$data['Data'][$i]['CaseStatusId'] =$row['CaseStatusId'];
$data['Data'][$i]['CaseTypeId'] =$row['CaseTypeId'];
$data['Data'][$i]['CountryId'] =$row['CountryId'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['CustomerId'] =$row['CustomerId'];
$data['Data'][$i]['Deleted'] =$row['Deleted'];
$data['Data'][$i]['Family'] =$row['Family'];
$data['Data'][$i]['KeepOnTopNote'] =$row['KeepOnTopNote'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['NoInSeries'] =$row['NoInSeries'];
$data['Data'][$i]['NoOfClaims'] =$row['NoOfClaims'];
$data['Data'][$i]['ProfitCentreId'] =$row['ProfitCentreId'];
$data['Data'][$i]['PurchaseOrderNo'] =$row['PurchaseOrderNo'];
$data['Data'][$i]['Remark'] =$row['Remark'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['TaxTreatmentId'] =$row['TaxTreatmentId'];
$data['Data'][$i]['Title'] =$row['Title'];
$data['Data'][$i]['TypeOfMarkId'] =$row['TypeOfMarkId'];
$sql_sm = array('table'=>'t_case_status_mas','where'=>'CaseStatusId = "'.$row['CaseStatusId'].'"');
$view_sm = $this->view($sql_sm);
$row_sm = $this->q($view_sm);
$data['Data'][$i]['CaseStatusName'] =$row_sm['CaseStatusName'];
$sql_om = array('table'=>'t_case_office_mas','where'=>'CaseOfficeId = "'.$row['CaseOfficeId'].'"');
$view_om = $this->view($sql_om);
$row_om = $this->q($view_om);
$data['Data'][$i]['CaseOfficeCode'] =$row_om['CaseOfficeCode'];
$data['Data'][$i]['CaseOfficeName'] =$row_om['CaseOfficeName'];
$sql_tm = array('table'=>'t_case_type_mas','where'=>'CaseTypeId = "'.$row['CaseTypeId'].'"');
$view_tm = $this->view($sql_tm);
$row_tm = $this->q($view_tm);
$data['Data'][$i]['CaseTypeName'] =$row_tm['CaseTypeName'];
$sql_cm = array('table'=>'t_country_mas','where'=>'CountryId = "'.$row['CountryId'].'"');
$view_cm = $this->view($sql_cm);
$row_cm = $this->q($view_cm);
$data['Data'][$i]['CountryShortName'] =$row_cm['CountryShortName'];
$data['Data'][$i]['CountryName'] =$row_cm['CountryName'];
$sql_bm = array('table'=>'t_basis_mas','where'=>'BasisId = "'.$row['BasisId'].'"');
$view_bm = $this->view($sql_bm);
$row_bm = $this->q($view_bm);
$data['Data'][$i]['BasisName'] = $row_bm['BasisName'];
$sql_pm = array('table'=>'t_profit_centre_mas','where'=>'ProfitCentreId = "'.$row['ProfitCentreId'].'"');
$view_pm = $this->view($sql_pm);
$row_pm = $this->q($view_pm);
$data['Data'][$i]['ProfitCentreCode'] =$row_pm['ProfitCentreCode'];
$data['Data'][$i]['ProfitCentreName'] =$row_pm['ProfitCentreName'];
$sql_bcm = array('table'=>'t_billing_currency_mas','where'=>'BillingCurrencyId = "'.$row['BillingCurrencyId'].'"');
$view_bcm = $this->view($sql_bcm);
$row_bcm = $this->q($view_bcm);
$data['Data'][$i]['BillingCurrencyCode'] =$row_bcm['BillingCurrencyCode'];
$data['Data'][$i]['BillingCurrencyName'] =$row_bcm['BillingCurrencyName'];
$sql_ttm = array('table'=>'t_tax_treatment_mas','where'=>'TaxTreatmentId = "'.$row['TaxTreatmentId'].'"');
$view_ttm = $this->view($sql_ttm);
$row_ttm = $this->q($view_ttm);
$data['Data'][$i]['TaxTreatmentName'] =$row_ttm['TaxTreatmentName'];
$sql_mm = array('table'=>'t_type_of_mark_mas','where'=>'TypeOfMarkId = "'.$row['TypeOfMarkId'].'"');
$view_mm = $this->view($sql_mm);
$row_mm = $this->q($view_mm);
$data['Data'][$i]['TypeOfMarkName'] =$row_mm['TypeOfMarkName'];
$sql_nm = array('table'=>'t_case_names_map','where'=>'CaseId = "'.$row['CaseId'].'"');
$view_nm = $this->view($sql_nm);
$row_nm = $this->q($view_nm);
$data['Data'][$i]['NamesTypeId'] =$row_nm['NamesTypeId'];
$data['Data'][$i]['NamesId'] =$row_nm['NamesId'];
$sql_nm1 = array('table'=>'t_case_names_map n1 LEFT JOIN t_names_mas t1 ON n1.NamesId = t1.NamesId','count'=>'t1.NamesName,t1.NamesCode','where'=>'n1.CaseId = "'.$row['CaseId'].'"');
$view_nm1 = $this->view($sql_nm1);
$row_nm1 = $this->q($view_nm1);
$data['Data'][$i]['Instructor'] =$row_nm1['NamesName'];
$data['Data'][$i]['InstructorCode'] =$row_nm1['NamesCode'];
$sql_nm2 = array('table'=>'t_case_names_map n1 LEFT JOIN t_names_mas t1 ON n1.NamesId = t1.NamesId','count'=>'t1.NamesName,t1.NamesCode','where'=>'n1.CaseId = "'.$row['CaseId'].'"');
$view_nm2 = $this->view($sql_nm2);
$row_nm2 = $this->q($view_nm2);
$data['Data'][$i]['Debtor'] =$row_nm2['NamesName'];
$data['Data'][$i]['DebtorCode'] =$row_nm2['NamesCode'];
$sql_nm3 = array('table'=>'t_case_names_map n1 LEFT JOIN t_names_mas t1 ON n1.NamesId = t1.NamesId','count'=>'t1.NamesName,t1.NamesCode','where'=>'n1.CaseId = "'.$row['CaseId'].'"');
$view_nm3 = $this->view($sql_nm3);
$row_nm3 = $this->q($view_nm3);
$data['Data'][$i]['Owner'] =$row_nm3['NamesName'];
$data['Data'][$i]['OwnerCode'] =$row_nm3['NamesCode'];
$sql_nm4 = array('table'=>'t_case_names_map n1 LEFT JOIN t_names_mas t1 ON n1.NamesId = t1.NamesId','count'=>'t1.NamesName,t1.NamesCode','where'=>'n1.CaseId = "'.$row['CaseId'].'"');
$view_nm4 = $this->view($sql_nm4);
$row_nm4 = $this->q($view_nm4);
$data['Data'][$i]['Agent'] =$row_nm4['NamesName'];
$data['Data'][$i]['AgentCode'] =$row_nm4['NamesCode'];
$sql_nm5 = array('table'=>'t_case_names_map n1 LEFT JOIN t_names_mas t1 ON n1.NamesId = t1.NamesId','count'=>'t1.NamesName,t1.NamesCode','where'=>'n1.CaseId = "'.$row['CaseId'].'"');
$view_nm5 = $this->view($sql_nm5);
$row_nm5 = $this->q($view_nm5);
$data['Data'][$i]['Partner'] =$row_nm5['NamesName'];
$data['Data'][$i]['PartnerCode'] =$row_nm5['NamesCode'];
$sql_nm6 = array('table'=>'t_case_names_map n1 LEFT JOIN t_names_mas t1 ON n1.NamesId = t1.NamesId','count'=>'t1.NamesName,t1.NamesCode','where'=>'n1.CaseId = "'.$row['CaseId'].'"');
$view_nm6 = $this->view($sql_nm6);
$row_nm6 = $this->q($view_nm6);
$data['Data'][$i]['Attorney'] =$row_nm6['NamesName'];
$data['Data'][$i]['AttorneyCode'] =$row_nm6['NamesCode'];
$sql_nm7 = array('table'=>'t_case_names_map n1 LEFT JOIN t_names_mas t1 ON n1.NamesId = t1.NamesId','count'=>'t1.NamesName,t1.NamesCode','where'=>'n1.CaseId = "'.$row['CaseId'].'"');
$view_nm7 = $this->view($sql_nm7);
$row_nm7 = $this->q($view_nm7);
$data['Data'][$i]['Executive'] =$row_nm7['NamesName'];
$data['Data'][$i]['ExecutiveCode'] =$row_nm7['NamesCode'];
$sql_nm8 = array('table'=>'t_case_names_map n1 LEFT JOIN t_names_mas t1 ON n1.NamesId = t1.NamesId','count'=>'t1.NamesName,t1.NamesCode','where'=>'n1.CaseId = "'.$row['CaseId'].'"');
$view_nm8 = $this->view($sql_nm8);
$row_nm8 = $this->q($view_nm8);
$data['Data'][$i]['Refferal'] =$row_nm8['NamesName'];
$data['Data'][$i]['RefferalCode'] =$row_nm8['NamesCode'];
$sql_n = array('table'=>'t_names_mas','where'=>'NamesId = "'.$row_nm['NamesId'].'"');
$view_n = $this->view($sql_n);
$row_n = $this->q($view_n);
$data['Data'][$i]['NamesName'] =$row_n['NamesName'];
$data['Data'][$i]['NamesCode'] =$row_n['NamesCode'];
$data['Data'][$i]['EmailAddress'] = $row_n['EmailAddress'];
$data['Data'][$i]['Initial'] = $row_n['Initial'];
$sql_im = array('table'=>'t_case_images_map','where'=>'CaseId = "'.$row['CaseId'].'"');
$view_im = $this->view($sql_im);
$row_im = $this->q($view_im);
$data['Data'][$i]['FileName'] =$row_im['FileName'];
$data['Data'][$i]['FilePath'] =$row_im['FilePath'];
$data['Data'][$i]['FileNameOriginal'] =$row_im['FileNameOriginal'];
$sql_onm = array('table'=>'t_case_official_number_map','where'=>'CaseId = "'.$row['CaseId'].'" AND OfficialNumberTypeId = 1');
$view_onm = $this->view($sql_onm);
$row_onm = $this->q($view_onm);
$data['Data'][$i]['OfficialNo'] =$row_onm['OfficialNo'];
$sql_ccm = array('table'=>'t_case_classes_map','where'=>'CaseId = "'.$row['CaseId'].'" AND Deleted = "0"');
$view_ccm = $this->view($sql_ccm);
$row_ccm = $this->q($view_ccm);
$data['Data'][$i]['ClassesCode'] =$row_ccm['ClassesCode'];
$data['Data'][$i]['ClassesTextEN'] =$row_ccm['ClassesTextEN'];
$data['Data'][$i]['ClassesTextTH'] =$row_ccm['ClassesTextTH'];
$sql_ntm = array('table'=>'t_names_type_mas','where'=>'NamesTypeId = "'.$row_nm['NamesTypeId'].'"');
$view_ntm = $this->view($sql_ntm);
$row_ntm = $this->q($view_ntm);
$data['Data'][$i]['NamesTypeId'] =$row_ntm['NamesTypeId'];
$data['Data'][$i]['NamesTypeName'] =$row_ntm['NamesTypeName'];
$data['Data'][$i]['SeqNo'] =$row_ntm['SeqNo'];
$sql_su = array('table'=>'t_system_users','where'=>'Id = "'.$row['CreateBy'].'"');
$view_su = $this->view($sql_su);
$row_su = $this->q($view_su);
$data['Data'][$i]['Name'] = $row_su['Name'];
$data['Data'][$i]['Surname'] = $row_su['Surname'];
$data['Data'][$i]['Namefull'] = $row_su['Name'] ." ".$row_su['Surname'];
$sql_cem = array('table'=>'t_case_events_map','where'=>'CaseId = "'.$row['CaseId'].'"');
$view_cem = $this->view($sql_cem);
$result_cem = $this->q_re($view_cem);
$num_cem = $this->q_nr($result_cem);
$i2=0;
if($num_cem > 0){
while ($row_cem = $this->q_ro($result_cem)) {
$data['Data'][$i]['EventsMap'][$i2]['EventId'] =$row_cem['EventId'];
$data['Data'][$i]['EventsMap'][$i2]['OccurredDate'] =$row_cem['OccurredDate'];
$data['Data'][$i]['EventsMap'][$i2]['StartDate'] =$row_cem['StartDate'];
$data['Data'][$i]['EventsMap'][$i2]['DueDate'] =$row_cem['DueDate'];
$data['Data'][$i]['EventsMap'][$i2]['CompleteDate'] =$row_cem['CompleteDate'];
$sql_em = array('table'=>'t_events_mas','where'=>'EventsId = "'.$row_cem['EventId'].'"');
$view_em = $this->view($sql_em);
$row_em = $this->q($view_em);
$data['Data'][$i]['EventsMap'][$i2]['EventsName'] =$row_em['EventsName'];
$i2++;
}
}else {
$data['Data'][$i]['EventsMap'] = "";
}
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
$data['request'] = $request;
//$data['sql'] = $view;
return $data;
}
public function IpmodulesCaseId($request)
{
$CaseId = $request['CaseId'];
$count ="T1.CaseId
, offn1.OfficialNo as 'ApplicationNo1'
, DATE_FORMAT(offn1.DateInForce, '%Y-%b-%d') as 'ApplicationNo2'
, offn2.OfficialNo as 'RegistrationNo1'
, DATE_FORMAT(offn2.DateInForce, '%Y-%b-%d') as 'RegistrationNo2'
, CONCAT(offn3.OfficialNo ,' ', offn3.DateInForce) as 'FileReference'
, T1.CaseReference as CaseReference
, T1.Family as Family
, T1.Title as Title
, T1.CaseTypeId as CaseTypeId
, T3.CaseTypeName as CaseTypeName
, T1.CountryId as CountryId
, T4.CountryName as CountryName
, T1.CaseOfficeId as CaseOfficeId
, T5.CaseOfficeName as CaseOfficeName
, T1.BasisId as BasisId
, T6.BasisName as BasisName
, T1.CaseStatusId as CaseStatusId
, T7.CaseStatusName as CaseStatusName
, T1.ProfitCentreId as ProfitCentreId
, T8.ProfitCentreName as ProfitCentreName
, T1.BillingCurrencyId as BillingCurrencyId
, T9.BillingCurrencyCode as BillingCurrencyCode
, T1.TaxTreatmentId as TaxTreatmentId
, T10.TaxTreatmentName as TaxTreatmentName
, T1.TypeOfMarkId as TypeOfMarkId
, T11.TypeOfMarkName as TypeOfMarkName
, T1.PurchaseOrderNo as PurchaseOrderNo
, T1.NoInSeries as NoInSeries
, T1.KeepOnTopNote as KeepOnTopNote
, T1.StatusActive as StatusActive
,CONCAT(u1.Name, ' ',CONCAT(u1.MiddleName, ' ', u1.Surname)) as CreateBy
,CONCAT(u2.Name, ' ',CONCAT(u2.MiddleName, ' ',u2.Surname)) as LastUpdateBy
,T1.CreateDateTime as CreateDateTime
,T1.LastUpdateDateTime as LastUpdateDateTime ";
$name_table = "t_case_management_trn T1
LEFT JOIN t_case_type_mas T3 ON T1.CaseTypeId = T3.CaseTypeId
LEFT JOIN t_country_mas T4 ON T1.CountryId= T4.CountryId
LEFT JOIN t_case_office_mas T5 ON T1.CaseOfficeId = T5.CaseOfficeId
LEFT JOIN t_basis_mas T6 ON T1.BasisId = T6.BasisId
LEFT JOIN t_case_status_mas T7 ON T1.CaseStatusId = T7.CaseStatusId
LEFT JOIN t_profit_centre_mas T8 ON T1.ProfitCentreId = T8.ProfitCentreId
LEFT JOIN t_billing_currency_mas T9 ON T1.BillingCurrencyId = T9.BillingCurrencyId
LEFT JOIN t_tax_treatment_mas T10 ON T1.TaxTreatmentId = T10.TaxTreatmentId
LEFT JOIN t_type_of_mark_mas T11 ON T1.TypeOfMarkId = T11.TypeOfMarkId
LEFT JOIN t_system_users u1 ON T1.CreateBy = u1.Id
LEFT JOIN t_system_users u2 ON T1.LastUpdateBy = u2.Id
LEFT JOIN t_case_official_number_map offn1 ON offn1.CaseId = T1.CaseId AND offn1.OfficialNumberTypeId = 1
LEFT JOIN t_case_official_number_map offn2 ON offn2.CaseId = T1.CaseId AND offn2.OfficialNumberTypeId = 2
LEFT JOIN t_case_official_number_map offn3 ON offn3.CaseId = T1.CaseId AND offn3.OfficialNumberTypeId = 3";
$search = "1=1";
/*
offn1.OfficialNumberTypeId = 1
AND offn2.OfficialNumberTypeId = 2
AND offn3.OfficialNumberTypeId = 3 */
if(@$request['CaseId']!=''){
$search = $search." AND T1.CaseId = '".@$CaseId."'";
}
$order = "";
$sql = array('table'=>$name_table,'where'=>$search,'order'=> $order);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseId'] =$row['CaseId'];
$data['Data'][$i]['ApplicationNo'] =$row['ApplicationNo1']." ".$row['ApplicationNo2'];
$data['Data'][$i]['RegistrationNo'] = $row['RegistrationNo1']." ".$row['RegistrationNo2'];
$data['Data'][$i]['FileReference'] =$row['FileReference'];
$data['Data'][$i]['CaseReference'] =$row['CaseReference'];
$data['Data'][$i]['Family'] =$row['Family'];
$data['Data'][$i]['Title'] =$row['Title'];
$data['Data'][$i]['CaseTypeId'] =$row['CaseTypeId'];
$data['Data'][$i]['CaseTypeName'] =$row['CaseTypeName'];
$data['Data'][$i]['CountryId'] =$row['CountryId'];
$data['Data'][$i]['CountryName'] =$row['CountryName'];
$data['Data'][$i]['CaseOfficeId'] =$row['CaseOfficeId'];
$data['Data'][$i]['BasisId'] =$row['BasisId'];
$data['Data'][$i]['BasisName'] =$row['BasisName'];
$data['Data'][$i]['CaseStatusId'] =$row['CaseStatusId'];
$data['Data'][$i]['CaseStatusName'] =$row['CaseStatusName'];
$data['Data'][$i]['ProfitCentreId'] =$row['ProfitCentreId'];
$data['Data'][$i]['ProfitCentreName'] =$row['ProfitCentreName'];
$data['Data'][$i]['BillingCurrencyId'] =$row['BillingCurrencyId'];
$data['Data'][$i]['BillingCurrencyCode'] =$row['BillingCurrencyCode'];
$data['Data'][$i]['TaxTreatmentId'] =$row['TaxTreatmentId'];
$data['Data'][$i]['TaxTreatmentName'] =$row['TaxTreatmentName'];
$data['Data'][$i]['TypeOfMarkId'] =$row['TypeOfMarkId'];
$data['Data'][$i]['TypeOfMarkName'] =$row['TypeOfMarkName'];
$data['Data'][$i]['PurchaseOrderNo'] =$row['PurchaseOrderNo'];
$data['Data'][$i]['NoInSeries'] =$row['NoInSeries'];
$data['Data'][$i]['KeepOnTopNote'] =$row['KeepOnTopNote'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['request'] = $request;
//$data['sql'] = $view;
return $data;
}
public function LoadFamily($request){
$name_table = "
t_case_management_trn tcmt
INNER JOIN t_case_status_mas tcsm ON tcsm.CaseStatusId = tcmt.CaseStatusId
INNER JOIN t_case_office_mas tcom ON tcom.CaseOfficeId = tcmt.CaseOfficeId
INNER JOIN t_case_type_mas tctm ON tctm.CaseTypeId = tcmt.CaseTypeId
INNER JOIN t_country_mas tcm ON tcm.CountryId = tcmt.CountryId
INNER JOIN t_basis_mas tbm ON tbm.BasisId = tcmt.BasisId
INNER JOIN t_profit_centre_mas tpcm ON tpcm.ProfitCentreId = tcmt.ProfitCentreId
INNER JOIN t_billing_currency_mas tbcm ON tbcm.BillingCurrencyId = tcmt.BillingCurrencyId
INNER JOIN t_tax_treatment_mas tttm ON tttm.TaxTreatmentId = tcmt.TaxTreatmentId
INNER JOIN t_type_of_mark_mas ttomm ON ttomm.TypeOfMarkId = tcmt.TypeOfMarkId
INNER JOIN t_case_names_map tcnm ON tcmt.CaseId = tcnm.CaseId AND tcmt.CaseTypeId = tcnm.NamesTypeId
INNER JOIN t_names_mas tnm ON tnm.NamesId = tcnm.NamesId
";
$count = "tcmt.*,
tcsm.CaseStatusName,
tcom.CaseOfficeCode, tcom.CaseOfficeName,
tctm.CaseTypeCode, tctm.CaseTypeName,
tcm.CountryName, tcm.CountryShortName,
tbm.BasisName,
tpcm.ProfitCentreCode, tpcm.ProfitCentreName,
tbcm.BillingCurrencyCode, tbcm.BillingCurrencyName,
tttm.TaxTreatmentName,
ttomm.TypeOfMarkName,
tcnm.CaseNameId, tcnm.CaseId, tcnm.NamesTypeId, tcnm.NamesId, tcnm.SeqNo, tcnm.Attention, tcnm.Reference,
tnm.NamesName, tnm.NamesCode, tnm.Telephone, tnm.PostalAddress, tnm.Fax, tnm.ElectronicMail, tnm.TaxID, tnm.Remark, tnm.Initial";
$search = "tcmt.Deleted='0' AND tcmt.Family!=''";
if(@$request['CaseTitle']!=''){
$search = $search." AND tcmt.Title LIKE '%".$request['CaseTitle'] ."%'";
}
if(@$request['CaseFamily']!=''){
$search = $search." AND tcmt.Family LIKE '%".$request['CaseFamily'] ."%'";
}
if(@$request['BasisId']!=''){
$search = $search." AND tcmt.BasisId = '".$request['BasisId'] ."'";
}
if(@$request['CaseTypeId']!=''){
$search = $search." AND tcmt.CaseTypeId = '".$request['CaseTypeId'] ."'";
}
if(@$request['TypeOfMarkId']!=''){
$search = $search." AND tcmt.TypeOfMarkId = '".$request['TypeOfMarkId'] ."'";
}
if(@$request['CaseId']!=''){
$search = $search." AND tcnm.CaseId = '".$request['CaseId'] ."'";
}
if(@$request['CaseReference']!=''){
$search = $search." AND tcmt.CaseReference = '".$request['CaseReference'] ."'";
}
if(@$request['Family']!=''){
$search = $search." AND tcmt.Family = '".$request['Family'] ."'";
}
if(@$request['Title']!=''){
$search = $search." AND tcmt.Title LIKE '%".$request['Title'] ."%'";
}
if(@$request['ProfitCentre']!=''){
$search = $search." AND tcmt.ProfitCentreId = '".$request['ProfitCentre'] ."'";
}
if(@$request['TaxTreatment']!=''){
$search = $search." AND tcmt.TaxTreatmentId = '".$request['TaxTreatment'] ."'";
}
if(@$request['CaseTypeId']!=''){
$search = $search." AND tcmt.CaseTypeId = '".$request['CaseTypeId'] ."'";
}
if(@$request['CaseStatus']!=''){
$search = $search." AND tcmt.CaseStatusId = '".$request['CaseStatus'] ."'";
}
if(@$request['EventDateStart']!=''){
$search = $search." AND tcmt.CreateDateTime >= '".$request['EventDateStart'] ."'";
}
if(@$request['EventDateEnd']!=''){
$search = $search." AND tcmt.CreateDateTime <= '".$request['EventDateStart'] ."'";
}
if(@$request['Name']!=''){
//$search = $search." AND CaseId = '".$request['CaseId'] ."'";
}
if(@$request['OfficialNumbers']!=''){
//$search = $search." AND CaseId = '".$request['CaseId'] ."'";
}
$order = "";
if($request['OrderDirection']!=''){
$order = " ORDER BY tcmt.".$request['OrderBy'] ." ".$request['OrderDirection'];
}
$sql = array('table'=>$name_table,'where'=>$search,'order'=> $order);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['BasisId'] =$row['BasisId'];
$data['Data'][$i]['BillingCurrencyId'] =$row['BillingCurrencyId'];
$data['Data'][$i]['CaseId'] =$row['CaseId'];
$data['Data'][$i]['CaseOfficeId'] =$row['CaseOfficeId'];
$data['Data'][$i]['CaseReference'] =$row['CaseReference'];
$data['Data'][$i]['CaseStatusId'] =$row['CaseStatusId'];
$data['Data'][$i]['CaseTypeId'] =$row['CaseTypeId'];
$data['Data'][$i]['CaseTypeName'] =$row['CaseTypeName'];
$data['Data'][$i]['CountryId'] =$row['CountryId'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['CustomerId'] =$row['CustomerId'];
$data['Data'][$i]['Deleted'] =$row['Deleted'];
$data['Data'][$i]['DueDate'] =$row['DueDate'];
$data['Data'][$i]['Family'] =$row['Family'];
$data['Data'][$i]['KeepOnTopNote'] =$row['KeepOnTopNote'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['NoInSeries'] =$row['NoInSeries'];
$data['Data'][$i]['NoOfClaims'] =$row['NoOfClaims'];
$data['Data'][$i]['MarkName'] =$row['MarkName'];
$data['Data'][$i]['ProfitCentreId'] =$row['ProfitCentreId'];
$data['Data'][$i]['PurchaseOrderNo'] =$row['PurchaseOrderNo'];
$data['Data'][$i]['Remark'] =$row['Remark'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['TaxTreatmentId'] =$row['TaxTreatmentId'];
$data['Data'][$i]['Title'] =$row['Title'];
$data['Data'][$i]['TypeOfMarkId'] =$row['TypeOfMarkId'];
$data['Data'][$i]['CountryShortName'] =$row['CountryShortName'];
$data['Data'][$i]['CountryName'] =$row['CountryName'];
$data['Data'][$i]['EventId'] =$row['EventId'];
$sql2 = array('table'=>'t_case_images_map','where'=>'CaseId = "'.$row['CaseId'].'"');
$view2 = $this->view($sql2);
$row2 = $this->q($view2);
$data['Data'][$i]['FileName'] =$row2['FileName'];
$data['Data'][$i]['FilePath'] =$row2['FilePath'];
$data['Data'][$i]['FileNameOriginal'] =$row2['FileNameOriginal'];
$sql3 = array('table'=>'t_events_mas','where'=>'EventsId = "'.$row['EventId'].'"');
$view3 = $this->view($sql3);
$row3 = $this->q($view3);
$data['Data'][$i]['EventsName'] =$row3['EventsName'];
$sql4 = array('table'=>'t_case_names_map','where'=>'CaseId = "'.$row['CaseId'].'"');
$view4 = $this->view($sql4);
$row4 = $this->q($view4);
$sql5 = array('table'=>'t_names_mas','where'=>'NamesId = "'.$row4['NamesId'].'"');
$view5 = $this->view($sql5);
$row5 = $this->q($view5);
$data['Data'][$i]['NamesName'] =$row5['NamesName'];
$data['Data'][$i]['NamesCode'] =$row5['NamesCode'];
$data['Data'][$i]['EmailAddress'] =$row5['EmailAddress'];
$sql6 = array('table'=>'t_country_mas','where'=>'CountryId = "'.$row['CountryId'].'"');
$view6 = $this->view($sql6);
$row6 = $this->q($view6);
$data['Data'][$i]['CountryShortName'] =$row6['CountryShortName'];
$data['Data'][$i]['CountryName'] =$row6['CountryName'];
$sql7 = array('table'=>'t_type_of_mark_mas','where'=>'TypeOfMarkId = "'.$row['TypeOfMarkId'].'"');
$view7 = $this->view($sql7);
$row7 = $this->q($view7);
$data['Data'][$i]['TypeOfMarkName'] =$row7['TypeOfMarkName'];
$sql10 = array('table'=>'t_case_official_number_map','where'=>'CaseId = "'.$row['CaseId'].'" AND OfficialNumberTypeId = 1');
$view10 = $this->view($sql10);
$row10 = $this->q($view10);
$data['Data'][$i]['OfficialNo'] =$row10['OfficialNo'];
$sql11 = array('table'=>'t_case_classes_map','where'=>'CaseId = "'.$row['CaseId'].'"');
$view11 = $this->view($sql11);
$row11 = $this->q($view11);
$data['Data'][$i]['ClassesCode'] =$row11['ClassesCode'];
$data['Data'][$i]['ClassesTextEN'] =$row11['ClassesTextEN'];
$data['Data'][$i]['ClassesTextTH'] =$row11['ClassesTextTH'];
$sql12 = array('table'=>'t_basis_mas','where'=>'BasisId = "'.$row['BasisId'].'"');
$view12 = $this->view($sql12);
$row12 = $this->q($view12);
$data['Data'][$i]['BasisName'] =$row12['BasisName'];
$sql13 = array('table'=>'t_case_type_mas','where'=>'CaseTypeId = "'.$row['CaseTypeId'].'"');
$view13 = $this->view($sql13);
$row13 = $this->q($view13);
$data['Data'][$i]['CaseTypeName'] =$row13['CaseTypeName'];
$sql14 = array('table'=>'t_case_office_mas','where'=>'CaseOfficeId = "'.$row['CaseOfficeId'].'"');
$view14 = $this->view($sql14);
$row14 = $this->q($view14);
$data['Data'][$i]['CaseOfficeCode'] =$row14['CaseOfficeCode'];
$data['Data'][$i]['CaseOfficeName'] =$row14['CaseOfficeName'];
$sql15 = array('table'=>'t_case_status_mas','where'=>'CaseStatusId = "'.$row['CaseStatusId'].'"');
$view15 = $this->view($sql15);
$row15 = $this->q($view15);
$data['Data'][$i]['CaseStatusName'] =$row15['CaseStatusName'];
$sql16 = array('table'=>'t_names_type_mas','where'=>'NamesTypeId = "'.$row['NamesTypeId'].'"');
$view16 = $this->view($sql16);
$row16 = $this->q($view16);
$data['Data'][$i]['NamesTypeName'] =$row16['NamesTypeName'];
$sql17 = array('table'=>'t_system_users','where'=>'Id = "'.$row['CreateBy'].'"');
$view17 = $this->view($sql17);
$row17 = $this->q($view17);
$data['Data'][$i]['Name'] =$row17['Name'];
$data['Data'][$i]['Surname'] =$row17['Surname'];
$data['Data'][$i]['Namefull'] =$row17['Name'] ." ".$row17['Surname'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
$data['request'] = $request;
//$data['sql'] = $view;
return $data;
}
public function LoadNamesmas($request)
{
//SELECT * FROM `t_events_mas` WHERE `HasDeadLine` = 1 ORDER BY `t_events_mas`.`CreateDateTime` ASC
$count ="";
$name_table = "
t_case_names_map tn
INNER JOIN t_names_type_mas tnt ON tn.NamesTypeId=tnt.NamesTypeId
INNER JOIN t_names_mas tnm ON tn.NamesId =tnm.NamesId
";
$search = "1=1";
if(@$request['CaseId']!=''){
$search = $search." AND CaseId = '".$request['CaseId'] ."'";
}
$order = "";
if(@$request['orderBy']=='NamesTypeName'){
if($request['OrderDirection']!=''){
$order = " ORDER BY tnt.".$request['OrderBy'] ." ".$request['OrderDirection'];
}
}
if(@$request['orderBy']=='NamesName'){
if(@$request['OrderDirection']!=''){
$order = " ORDER BY tnm.".$request['OrderBy'] ." ".$request['OrderDirection'];
}
}
if(@$request['orderBy']=='Attention'){
if(@$request['OrderDirection']!=''){
$order = " ORDER BY tn.".$request['OrderBy'] ." ".$request['OrderDirection'];
}
}
if(@$request['orderBy']=='Reference'){
if(@$request['OrderDirection']!=''){
$order = " ORDER BY tn.".$request['OrderBy'] ." ".$request['OrderDirection'];
}
}
$sql = array('table'=>$name_table,'where'=>$search,'count'=>$count,'order'=> $order);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['Attention'] =$row['Attention'];
$data['Data'][$i]['CaseId'] =$row['CaseId'];
$data['Data'][$i]['CaseNameId'] =$row['CaseNameId'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['Deleted'] =$row['Deleted'];
$data['Data'][$i]['ElectronicMail'] =$row['ElectronicMail'];
$data['Data'][$i]['Fax'] =$row['Fax'];
$data['Data'][$i]['Initial'] =$row['Initial'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['NamesName'] =$row['NamesName'];
$data['Data'][$i]['NamesCode'] =$row['NamesCode'];
$data['Data'][$i]['NamesId'] =$row['NamesId'];
$data['Data'][$i]['NamesTypeId'] =$row['NamesTypeId'];
$data['Data'][$i]['NamesTypeName'] =$row['NamesTypeName'];
$data['Data'][$i]['PostalAddress'] =$row['PostalAddress'];
$data['Data'][$i]['Reference'] =$row['Reference'];
$data['Data'][$i]['Remark'] =$row['Remark'];
$data['Data'][$i]['SeqNo'] =$row['SeqNo'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['TaxID'] =$row['TaxID'];
$data['Data'][$i]['Telephone'] =$row['Telephone'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
public function LoadCasetype($request)
{
//SELECT * FROM `t_events_mas` WHERE `HasDeadLine` = 1 ORDER BY `t_events_mas`.`CreateDateTime` ASC
$count ="";
$name_table = "t_case_type_mas";
$search = "Deleted=0";
if(@$request['Keyword']!=''){
$search = $search." AND CaseTypeId = '".$request['Keyword'] ."'";
}
$order = "";
if(@$request['orderBy']=='NamesTypeName'){
if($request['OrderDirection']!=''){
$order = " ORDER BY tnt.".$request['OrderBy'] ." ".$request['OrderDirection'];
}
}
if(@$request['orderBy']=='NamesName'){
if(@$request['OrderDirection']!=''){
$order = " ORDER BY tnm.".$request['OrderBy'] ." ".$request['OrderDirection'];
}
}
if(@$request['orderBy']=='Attention'){
if(@$request['OrderDirection']!=''){
$order = " ORDER BY tn.".$request['OrderBy'] ." ".$request['OrderDirection'];
}
}
if(@$request['orderBy']=='Reference'){
if(@$request['OrderDirection']!=''){
$order = " ORDER BY tn.".$request['OrderBy'] ." ".$request['OrderDirection'];
}
}
$sql = array('table'=>$name_table,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseTypeId'] =$row['CaseTypeId'];
$data['Data'][$i]['CaseTypeCode'] =$row['CaseTypeCode'];
$data['Data'][$i]['CaseTypeName'] =$row['CaseTypeName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['Deleted'] =$row['Deleted'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
/*
public function LoadOfficial($request)
{
//SELECT * FROM `t_events_mas` WHERE `HasDeadLine` = 1 ORDER BY `t_events_mas`.`CreateDateTime` ASC
$count ="";
$name_table = "t_case_official_number_map";
$search = "Deleted=0";
if(@$request['CaseId']!=''){
$search = $search." AND CaseId = '".$request['CaseId'] ."'";
}
$order = "";
if(@$request['orderBy']=='NamesTypeName'){
if($request['OrderDirection']!=''){
$order = " ORDER BY tnt.".$request['OrderBy'] ." ".$request['OrderDirection'];
}
}
$sql = array('table'=>$name_table,'where'=>$search,'order'=> $order,'count'=>$count);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseOfficialNumberId'] =$row['CaseOfficialNumberId'];
$data['Data'][$i]['CaseId'] =$row['CaseId'];
$data['Data'][$i]['OfficialNumberTypeId'] =$row['OfficialNumberTypeId'];
$data['Data'][$i]['CaseOfficialNumberType'] =$row['CaseOfficialNumberType'];
$data['Data'][$i]['OfficialNo'] =$row['OfficialNo'];
$data['Data'][$i]['SeqNo'] =$row['SeqNo'];
$data['Data'][$i]['DateInForce'] =$row['DateInForce'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['Deleted'] =$row['Deleted'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
//$data['sql'] = $view;
return $data;
}
*/
public function LoadEvent($request)
{
$count ="";
$name_table = "
t_case_events_map tc
INNER JOIN t_events_mas te ON tc.EventId=te.EventsId
";
$search = "1=1";
if(@$request['Keyword']==0){
$search = $search." AND YEAR(`DueDate`) = 0";
}
if(@$request['Keyword']==1){
$search = $search." AND YEAR(`DueDate`) != 0";
}
if(@$request['CaseId']!=''){
$search = $search." AND CaseId = '".$request['CaseId'] ."'";
}
$order = "";
if(@$request['orderBy']=='NamesTypeName'){
if($request['OrderDirection']!=''){
$order = " ORDER BY tc.".$request['OrderBy'] ." ".$request['OrderDirection'];
}
}
$sql = array('table'=>$name_table,'where'=>$search,'count'=>$count,'order'=> $order);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseEventId'] =$row['CaseEventId'];
$data['Data'][$i]['CaseId'] =$row['CaseId'];
$data['Data'][$i]['EventId'] =$row['EventId'];
$data['Data'][$i]['OccurredDate'] =$row['OccurredDate'];
$data['Data'][$i]['StartDate'] =$row['StartDate'];
$data['Data'][$i]['DueDate'] =$row['DueDate'];
$data['Data'][$i]['CompleteDate'] =$row['CompleteDate'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['Deleted'] =$row['Deleted'];
$data['Data'][$i]['Remark'] =$row['Remark'];
$data['Data'][$i]['EventsId'] =$row['EventsId'];
$data['Data'][$i]['EventsName'] =$row['EventsName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['HasDeadLine'] =$row['HasDeadLine'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
$data['request'] = $request;
//$data['sql'] = $view;
return $data;
}
public function LoadDocument($request)
{
$count ="*, te.Name AS Name, te.Surname AS Surname";
$name_table = "
t_case_documents_map tc
INNER JOIN t_system_users te ON tc.LastUpdateBy=te.Id
";
$search = "1=1";
if(@$request['CaseId']!=''){
$search = $search." AND CaseId = '".$request['CaseId'] ."'";
}
$order = "";
if(@$request['orderBy']=='NamesTypeName'){
if($request['OrderDirection']!=''){
$order = " ORDER BY tc.".$request['OrderBy'] ." ".$request['OrderDirection'];
}
}
$sql = array('table'=>$name_table,'where'=>$search,'count'=>$count,'order'=> $order);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseDocumentId'] =$row['CaseDocumentId'];
$data['Data'][$i]['CaseId'] =$row['CaseId'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['Deleted'] =$row['Deleted'];
$data['Data'][$i]['FileName'] =$row['FileName'];
$data['Data'][$i]['FileNameOriginal'] =$row['FileNameOriginal'];
$data['Data'][$i]['FilePath'] =$row['FilePath'];
$data['Data'][$i]['FileSize'] =$row['FileSize'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['Name'] =$row['Name'];
$data['Data'][$i]['Surname'] =$row['Surname'];
$data['Data'][$i]['Namefull'] =$row['Name']." ".$row['Surname'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
$data['request'] = $request;
//$data['sql'] = $view;
return $data;
}
public function LoadRelatemas($request)
{
$count ="
tc.*, tm.CaseReference AS CaseReference, tm.Title AS Title,te.Name AS Name, te.Surname AS Surname, tp.ProfitCentreName AS ProfitCentreName
, tt.TaxTreatmentName AS TaxTreatmentName, tct.CaseTypeName AS CaseTypeName, ts.CaseStatusName AS CaseStatusName";
$name_table = "
t_case_related_map tc
INNER JOIN t_system_users te ON tc.LastUpdateBy=te.Id
INNER JOIN t_case_management_trn tm ON tm.CaseId = tc.CaseRefId
INNER JOIN t_profit_centre_mas tp ON tp.ProfitCentreId = tm.ProfitCentreId
INNER JOIN t_tax_treatment_mas tt ON tt.TaxTreatmentId = tm.TaxTreatmentId
INNER JOIN t_case_type_mas tct ON tct.CaseTypeId = tm.CaseTypeId
INNER JOIN t_case_status_mas ts ON ts.CaseStatusId = tm.CaseStatusId
";
$search = "1=1";
/*
*/
if(@$request['CaseId']!=''){
$search = $search." AND tc.CaseId = '".$request['CaseId'] ."'";
}
$order = "";
if($request['OrderDirection']!=''){
$order = " ORDER BY tc.".$request['OrderBy'] ." ".$request['OrderDirection'];
}
$sql = array('table'=>$name_table,'where'=>$search,'count'=>$count,'order'=> $order);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if($respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseDocumentId'] =$row['CaseDocumentId'];
$data['Data'][$i]['CaseId'] =$row['CaseId'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['Deleted'] =$row['Deleted'];
$data['Data'][$i]['FileName'] =$row['FileName'];
$data['Data'][$i]['FileNameOriginal'] =$row['FileNameOriginal'];
$data['Data'][$i]['FilePath'] =$row['FilePath'];
$data['Data'][$i]['FileSize'] =$row['FileSize'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['Name'] =$row['Name'];
$data['Data'][$i]['Surname'] =$row['Surname'];
$data['Data'][$i]['Namefull'] =$row['Name']." ".$row['Surname'];
$data['Data'][$i]['CaseReference'] =$row['CaseReference'];
$data['Data'][$i]['Title'] =$row['Title'];
$data['Data'][$i]['ProfitCentreName'] =$row['ProfitCentreName'];
$data['Data'][$i]['TaxTreatmentName'] =$row['TaxTreatmentName'];
$data['Data'][$i]['CaseTypeName'] =$row['CaseTypeName'];
$data['Data'][$i]['CaseStatusName'] =$row['CaseStatusName'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = $request['PageNumber'];
$data['OrderBy'] = $request['OrderBy'];
$data['OrderDirection'] = $request['OrderDirection'];
$data['PageSize'] = $request['PageSize'];
$data['numrows'] = $respons_count_num;
$data['TotalRecords'] = $respons_count_num;
$data['request'] = $request;
//$data['sql'] = $view;
return $data;
}
public function LoadProfit()
{
$count ="";
$name_table = "t_profit_centre_mas";
$search = "";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['ProfitCentreId'] =$row['ProfitCentreId'];
$data['Data'][$i]['ProfitCentreCode'] =$row['ProfitCentreCode'];
$data['Data'][$i]['ProfitCentreName'] =$row['ProfitCentreName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadTypeofmark()
{
$name_table = "t_type_of_mark_mas";
$count ="";
$search = "";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['TypeOfMarkId'] =$row['TypeOfMarkId'];
$data['Data'][$i]['TypeOfMarkName'] =$row['TypeOfMarkName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadBasis()
{
$count ="";
$name_table = "t_basis_mas";
$search = "Deleted=0";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['BasisId'] =$row['BasisId'];
$data['Data'][$i]['BasisName'] =$row['BasisName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadTreatment()
{
$count ="";
$name_table = "t_tax_treatment_mas";
$search = "Deleted=0";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['TaxTreatmentId'] =$row['TaxTreatmentId'];
$data['Data'][$i]['TaxTreatmentName'] =$row['TaxTreatmentName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadCasestatus()
{
$name_table = "t_case_status_mas";
$count ="";
$search = "Deleted=0";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseStatusId'] =$row['CaseStatusId'];
$data['Data'][$i]['CaseStatusName'] =$row['CaseStatusName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadOfficialnumbertype()
{
$name_table = "t_official_number_type_mas";
$count ="";
$search = "Deleted=0";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['OfficialNumberTypeId'] =$row['OfficialNumberTypeId'];
$data['Data'][$i]['OfficialNumberTypeName'] =$row['OfficialNumberTypeName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['Deleted'] =$row['Deleted'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadCurrency()
{
$name_table = "t_billing_currency_mas";
$count ="";
$search = "Deleted=0";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['BillingCurrencyId'] =$row['BillingCurrencyId'];
$data['Data'][$i]['BillingCurrencyCode'] =$row['BillingCurrencyCode'];
$data['Data'][$i]['BillingCurrencyName'] =$row['BillingCurrencyName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadClasses($request)
{
$count ="tc.*, ts.Name AS Name, ts.Surname AS Surname";
$name_table = "
t_classes_mas tc
INNER JOIN t_system_users ts ON tc.LastUpdateBy = ts.Id
";
$search = "tc.Deleted=0";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['ClassesCode'] =$row['ClassesCode'];
$data['Data'][$i]['ClassesId'] =$row['ClassesId'];
$data['Data'][$i]['ClassesTextEN'] =$row['ClassesTextEN'];
$data['Data'][$i]['ClassesTextTH'] =$row['ClassesTextTH'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['Name'] =$row['Name'];
$data['Data'][$i]['Surname'] =$row['Surname'];
$data['Data'][$i]['Namefull'] =$row['Name']." ".$row['Surname'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadClassesId($request)
{
$count ="tc.*, ts.Name AS Name, ts.Surname AS Surname";
$name_table = "
t_case_classes_map tc
INNER JOIN t_system_users ts ON tc.LastUpdateBy = ts.Id
";
//INNER JOIN t_classes_mas tx ON tc.ClassesId = tx.ClassesId
$search = "tc.Deleted=0";
if(@$request['CaseId']!=''){
$search = $search." AND tc.CaseId = '".$request['CaseId'] ."'";
}
$order = "";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$search);
if($request['PageSize']!=''){
$page_number = $request['PageNumber'] - 1;
$max_rows = $request['PageSize'];
$list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
$start_count = $page_number * $max_rows;
$view_count = $this->view($list_count);
$result_count = $this->q_re($view_count);
$respons_count = $this->q_nr($result_count);
$respons_count_num = mysqli_num_rows($result_count);
$respons_pages = ceil($respons_count/$max_rows)-1;
$data_c['page_number'] = $page_number;
$data_c['respons_pages'] = $respons_pages;
$sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
}
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['ClassesCode'] =$row['ClassesCode'];
$data['Data'][$i]['ClassesId'] =$row['ClassesId'];
$data['Data'][$i]['ClassesTextEN'] =$row['ClassesTextEN'];
$data['Data'][$i]['ClassesTextTH'] =$row['ClassesTextTH'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$data['Data'][$i]['Name'] =$row['Name'];
$data['Data'][$i]['Surname'] =$row['Surname'];
$data['Data'][$i]['Namefull'] =$row['Name']." ".$row['Surname'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$request;
//$data['sql'] = @$view;
return $data;
}
public function LoadClassesSearch()
{
$count ="";
$name_table = "t_classes_mas";
$search = "Deleted=0";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['ClassesId'] =$row['ClassesId'];
$data['Data'][$i]['ClassesCode'] =$row['ClassesCode'];
$data['Data'][$i]['ClassesTextTH'] =$row['ClassesTextTH'];
$data['Data'][$i]['ClassesTextEN'] =$row['ClassesTextEN'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadCaseOfficeSearch()
{
$count ="";
$name_table = "t_case_office_mas";
$search = "Deleted=0";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseOfficeId'] =$row['CaseOfficeId'];
$data['Data'][$i]['CaseOfficeCode'] =$row['CaseOfficeCode'];
$data['Data'][$i]['CaseOfficeName'] =$row['CaseOfficeName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadCasetypeSearch()
{
$count ="";
$name_table = "t_case_type_mas";
$search = "Deleted=0";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseTypeId'] =$row['CaseTypeId'];
$data['Data'][$i]['CaseTypeCode'] =$row['CaseTypeCode'];
$data['Data'][$i]['CaseTypeName'] =$row['CaseTypeName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadCountrySearch()
{
$count ="";
$name_table = "t_country_mas";
$search = "Deleted=0";
$order = "ORDER BY CountryName ASC";
$sql = array('table'=>$name_table,'where'=>$search, 'order' => $order);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CountryId'] =$row['CountryId'];
$data['Data'][$i]['CountryName'] =$row['CountryName'];
$data['Data'][$i]['CountryShortName'] =$row['CountryShortName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadBasisSearch()
{
$count ="";
$name_table = "t_basis_mas";
$search = "Deleted=0";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['BasisId'] =$row['BasisId'];
$data['Data'][$i]['BasisName'] =$row['BasisName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadTypefomarkSearch()
{
$count ="";
$name_table = "t_type_of_mark_mas";
$search = "Deleted=0";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['TypeOfMarkId'] =$row['TypeOfMarkId'];
$data['Data'][$i]['TypeOfMarkName'] =$row['TypeOfMarkName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadNamestypeSearch()
{
$count ="";
$name_table = "t_names_type_mas";
$search = "Deleted=0";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['NamesTypeId'] =$row['NamesTypeId'];
$data['Data'][$i]['NamesTypeName'] =$row['NamesTypeName'];
$data['Data'][$i]['SeqNo'] =$row['SeqNo'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadEventSearch()
{
$count ="";
$name_table = "t_events_mas";
$search = "Deleted=0";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['EventsId'] =$row['EventsId'];
$data['Data'][$i]['EventsName'] =$row['EventsName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
public function LoadcasestatusSearch()
{
$count ="";
$name_table = "t_case_status_mas";
$search = "Deleted=0";
$order = "";
$sql = array('table'=>$name_table,'where'=>$search);
$view = $this->view($sql);
$result = $this->q_re($view);
$respons = $this->q_nr($result);
$respons_count_num = mysqli_num_rows($result);
$i=0;$data=array();
if(@$respons_count_num > 0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CaseStatusId'] =$row['CaseStatusId'];
$data['Data'][$i]['CaseStatusName'] =$row['CaseStatusName'];
$data['Data'][$i]['StatusActive'] =$row['StatusActive'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
$i++;
}
}else{
$data['Data'] = '';
}
$data['PageNumber'] = @$request['PageNumber'];
$data['OrderBy'] = @$request['OrderBy'];
$data['OrderDirection'] = @$request['OrderDirection'];
$data['PageSize'] = @$request['PageSize'];
$data['numrows'] = @$respons_count_num;
$data['TotalRecords'] = @$respons_count_num;
$data['request'] = @$respons;
//$data['sql'] = @$view;
return $data;
}
}
?>