| Server IP : 172.67.216.113 / Your IP : 104.23.243.32 [ 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_old/ |
Upload File : |
<?php
/**
*
*/
class ReportClass extends Viewdata
{
public function date_chang_1($var)//in:07/06/2023 //out: 2023-06-07 05:24:40
{
$txt_date = explode("/",$var);
$h_s = "00:00:00";
$date_sum = $txt_date[2].'-'.$txt_date[1].'-'.$txt_date[0].' '.$h_s;
return $date_sum;
}
public function CaseSummary(){
$name_table = "ct_country";
$secrch = "Del = 0";
$sql = array('table'=>$name_table,'where'=>$secrch);
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
while ($row = $this->q_ro($result)) {
$data[$i]['CountryId'] =$row['CountryId'];
$data[$i]['CountryName'] =$row['CountryName'];
$data[$i]['CountryCode'] =$row['CountryCode'];
$data[$i]['Status'] =$row['Status'];
$data[$i]['Del'] =$row['Del'];
$data[$i]['CreateBy'] =$row['CreateBy'];
$data[$i]['CreateDateTime'] =$row['CreateDateTime'];
$data[$i]['UpdateBy'] =$row['UpdateBy'];
$data[$i]['UpdateDateTime'] =$row['UpdateDateTime'];
$i++;
}
return $data;
}
public function CaseSummaryExcelandPdf($request)
{
$name_table = "ct_case c INNER JOIN ct_case_timesheet ts ON c.CaseId=ts.CaseId AND ts.Del=0";
$count = " c.CaseId,c.CaseNumber,'' as DueDate";
$count .= " ,(SELECT CaseTypeName FROM ct_master_case_type WHERE CaseTypeId = c.CaseTypeId) as CaseType";
$count .= " ,(SELECT NamesName FROM t_names_mas WHERE NamesId = c.CustomerId) as CustomerName";
$count .= " ,'' as LastEvent,ts.TaskDate,ts.TaskDescription,(SELECT CONCAT(Name, '', Surname) FROM t_system_users WHERE Id = ts.CreateBy) as TaskBy ,ts.Duration";
$count .= "";
$secrch = "1 = 1";
if(@$request['IsRetainer']>0){
$type =$request['IsRetainer'];
$fQuotationType = ($request['IsRetainer']==1)?"One Time only" : "Retainer only";
$secrch = $secrch." AND (SELECT Count(*) FROM ct_quotation WHERE QuotationId = c.QuotationId AND QuotationType = '".$type."') > 0";
}
if(@$request['CaseTypeId']!=''){
$typeId = $request['CaseTypeId'];
$secrch_country = "CountryId = '".$request['CountryId']."'";
$sql_country = array('table'=>'ct_country','where'=>$secrch_country);
$view_country = $this->view($sql_country);
$row_country = $this->q($view_country);
$fCountry = $row_country['CountryCode'];
$secrch_casetype = "CaseTypeId = '".$request['CaseTypeId']."'";
$sql_casetype = array('table'=>'ct_master_case_type','where'=>$secrch_casetype);
$view_casetype = $this->view($sql_casetype);
$row_casetype = $this->q($view_casetype);
$fCaseType = $row_casetype['CaseTypeName'];
$secrch = $secrch." AND c.CaseTypeId = '".$typeId."'";
}else{
$countryId = $request['CountryId'];
$secrch_country = "CountryId = '".$request['CountryId']."'";
$sql_country = array('table'=>'ct_country','where'=>$secrch_country);
$view_country = $this->view($sql_country);
$row_country = $this->q($view_country);
$fCountry = @$row_country['CountryCode'];
$secrch = $secrch." AND c.CaseTypeId IN (SELECT CaseTypeId From ct_master_case_type WHERE CountryId = '".$countryId."')";
}
if(@$request['CaseNumber']!=''){
$fCaseNo =$request['CaseNumber'];
$caseNumber =$request['CaseNumber'];
$secrch = $secrch." AND c.CaseNumber LIKE '".@$caseNumber."'";
}
if(@$request['Status']>0){
if(@$request['Status']==1){
$fStatus = "Opening";
$secrch = $secrch." AND (SELECT Count(*) FROM ct_case_event WHERE EventId = 1 AND CaseId=c.CaseId) > 0";
$secrch = $secrch." AND (SELECT Count(*) FROM ct_case_event WHERE EventId = 2 AND CaseId=c.CaseId) = 0";
}elseif(@$request['Status']==2){
$fStatus = "Completed";
$secrch = $secrch." AND (SELECT Count(*) FROM ct_case_event WHERE EventId = 2 AND CaseId=c.CaseId) > 0";
}
}
if(@$request['CustomerName']!=''){
$cusName = $request['CustomerName'];
$secrch = $secrch." AND (SELECT NamesName FROM t_names_mas WHERE NamesId = c.CustomerId) Like '%".@$cusName."%'";
}
if(@$request['CaseId'] > 0){
$caseId = $request['CaseId'];
$secrch = $secrch." AND c.CaseId = '".@$caseId."'";
}
if($request['CustomerId'] > 0){
$secrch_names = "NamesId = '".$request['CustomerId']."'";
$sql_names = array('table'=>'t_names_mas','where'=>$secrch_names);
$view_names = $this->view($sql_names);
$row_names = $this->q($view_names);
$fCustomer = $row_names['NamesName'];
$cusId = $request['CustomerId'];
$secrch = $secrch." AND c.CustomerId = '".@$cusId."'";
}
$order = '';
//$order = " ORDER BY c.CaseId ASC";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$secrch, 'order'=>$order);
$view = $this->view($sql);
$result = $this->q_re($view);
$num_result = $this->q_nr($result);
$i=0;$data=array();
if($num_result>0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CasssseId'] =$row;
$data['Data'][$i]['CaseId'] =$row['CaseId'];
$data['Data'][$i]['CaseNumber'] =$row['CaseNumber'];
$data['Data'][$i]['CaseType'] =$row['CaseType'];
$data['Data'][$i]['CustomerName'] =$row['CustomerName'];
$data['Data'][$i]['TaskDate'] =$row['TaskDate'];
$data['Data'][$i]['TaskDescription'] = $row['TaskDescription'];
$data['Data'][$i]['TaskBy'] =$row['TaskBy'];
$data['Data'][$i]['Duration'] =$row['Duration'];
$data['Data'][$i]['row'] =$row;
$sql_dueDate = array('table'=>'ct_case_event ','where'=>"CaseId == '".$row['CaseId']."' AND DueDate !=''" );
$view_dueDate = $this->view($sql_dueDate);
$result_dueDate = $this->q_re($view_dueDate);
$num_dueDate = $this->q_nr($result_dueDate);
$d=0;
if($num_dueDate>0){
while ($row_dueDate = $this->q_ro($result_dueDate)) {
$data['Data'][$i]['DueDate'][$d] =$row_dueDate;
$d++;
}
}
//$data['Data'][$i]['DueDate_sql'] = $view_dueDate;
$sql_lastEvent = array('table'=>'ct_case_event cv INNER JOIN ct_master_event ev ON cv.EventId = ev.EventId','where'=>"cv.CaseId = '".$row['CaseId']."'" );
$view_lastEvent = $this->view($sql_lastEvent);
$result_lastEvent = $this->q_re($view_lastEvent);
$num_lastEvent = $this->q_nr($result_lastEvent);
$r=0;
if($num_lastEvent>0){
while ($row_lastEvent = $this->q_ro($result_lastEvent)) {
$data['Data'][$i]['LastEvent'][$r] =$row_lastEvent;
$r++;
}
}
$i++;
}
}else {
$data['Data']="";
}
$data['fQuotationType'] =$fQuotationType;
$data['fCountry'] =$fCountry;
$data['fCaseType'] =$fCaseType;
$data['fCaseNo'] =$fCaseNo;
$data['fStatus'] =$fStatus;
$data['fCustomer'] =$fCustomer;
$data['sql'] = $view;
return $data;
}
public function CaseSummaryExcelandPdf2($request)
{
$name_table = "ct_case c INNER JOIN ct_case_timesheet ts ON c.CaseId=ts.CaseId AND ts.Del=0";
$count = "
c.CaseId,c.CaseNumber,c.CaseTypeId,c.CustomerId,'' as DueDate ,
'' as LastEvent,ts.TaskDate,ts.TaskDescription,ts.Duration,ts.CreateBy
";
$secrch = "1 = 1";
if(@$request['IsRetainer']>0){
$type =$request['IsRetainer'];
$fQuotationType = ($request['IsRetainer']==1)?"One Time only" : "Retainer only";
$secrch = $secrch." AND (SELECT Count(*) FROM ct_quotation WHERE QuotationId = c.QuotationId AND QuotationType = '".$type."') > 0";
}
if(@$request['CaseTypeId']!=''){
$typeId = $request['CaseTypeId'];
$secrch_country = "CountryId = '".$request['CountryId']."'";
$sql_country = array('table'=>'ct_country','where'=>$secrch_country);
$view_country = $this->view($sql_country);
$row_country = $this->q($view_country);
$fCountry = $row_country['CountryCode'];
$secrch_casetype = "CaseTypeId = '".$request['CaseTypeId']."'";
$sql_casetype = array('table'=>'ct_master_case_type','where'=>$secrch_casetype);
$view_casetype = $this->view($sql_casetype);
$row_casetype = $this->q($view_casetype);
$fCaseType = $row_casetype['CaseTypeName'];
$secrch = $secrch." AND c.CaseTypeId = '".$typeId."'";
}else{
if(@$request['CountryId']!=""){
$countryId = $request['CountryId'];
$secrch_country = "CountryId = '".$request['CountryId']."'";
$sql_country = array('table'=>'ct_country','where'=>$secrch_country);
$view_country = $this->view($sql_country);
$row_country = $this->q($view_country);
$sql_cm = array('table'=>'ct_master_case_type', 'count'=>'CaseTypeId','where'=>'CountryId = "'.$countryId.'"');
$view_cm = $this->view($sql_cm);
$row_cm = $this->q($view_cm);
$fCountry = @$row_country['CountryCode'];
$secrch = $secrch." AND c.CaseTypeId IN ('".$row_cm['CaseTypeId']."')";
}
}
if(@$request['CaseNumber']!=''){
$fCaseNo =$request['CaseNumber'];
$caseNumber =$request['CaseNumber'];
$secrch = $secrch." AND c.CaseNumber LIKE '%".@$caseNumber."%'";
}
if(@$request['Status']>0){
if(@$request['Status']==1){//opening
$fStatus = "Opening";
$secrch = $secrch." AND (SELECT Count(*) FROM ct_case_event WHERE EventId = '1' AND CaseId=c.CaseId) > 0";
$secrch = $secrch." AND (SELECT Count(*) FROM ct_case_event WHERE EventId = '2' AND CaseId=c.CaseId) = 0";
}elseif(@$request['Status']==2){
$fStatus = "Completed";
$secrch = $secrch." AND (SELECT Count(*) FROM ct_case_event WHERE EventId = '2' AND CaseId=c.CaseId) > 0";
}
}
if(@$request['CustomerName']!=''){
$cusName = $request['CustomerName'];
$secrch = $secrch." AND (SELECT NamesName FROM t_names_mas WHERE NamesId = c.CustomerId) Like '%".@$cusName."%'";
}
if(@$request['CaseId'] > 0){
$caseId = $request['CaseId'];
$secrch = $secrch." AND c.CaseId = '".@$caseId."'";
}
if($request['CustomerId'] > 0){
$secrch_names = "NamesId = '".$request['CustomerId']."'";
$sql_names = array('table'=>'t_names_mas','where'=>$secrch_names);
$view_names = $this->view($sql_names);
$row_names = $this->q($view_names);
$fCustomer = $row_names['NamesName'];
$cusId = $request['CustomerId'];
$secrch = $secrch." AND c.CustomerId = '".@$cusId."'";
}
//$order = '';
$order = " ORDER BY c.CaseId ASC";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$secrch, 'order'=>$order);
$view = $this->view($sql);
$result = $this->q_re($view);
$num_result = $this->q_nr($result);
$i=0;$data=array();
if($num_result>0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['CasssseId'] =$row;
$data['Data'][$i]['CaseId'] =$row['CaseId'];
$data['Data'][$i]['CaseNumber'] =$row['CaseNumber'];
$data['Data'][$i]['TaskDate'] =$row['TaskDate'];
$data['Data'][$i]['TaskDescription'] =$row['TaskDescription'];
$data['Data'][$i]['Duration'] =$row['Duration'];
$sql_casetype = array('table'=>'ct_master_case_type ','count'=>'CaseTypeName AS CaseType' ,'where'=>"CaseTypeId == '".$row['CaseTypeId']."'" );
$view_casetype = $this->view($sql_casetype);
$row_casetype = $this->q($view_casetype);
$data['Data'][$i]['CaseType'] =$row_casetype['CaseType'];
$sql_cust = array('table'=>'t_names_mas ','count'=>'NamesName AS CustomerName' ,'where'=>"NamesId == '".$row['CustomerId']."'" );
$view_cust = $this->view($sql_cust);
$row_cust = $this->q($view_cust);
$data['Data'][$i]['CustomerName'] =$row_cust['CustomerName'];
$sql_task = array('table'=>'t_system_users','count'=>'CONCAT(Name,"", Surname) AS TaskBy' ,'where'=>"Id = '".$row['CreateBy']."'" );
$view_task = $this->view($sql_task);
$row_task = $this->q($view_task);
$data['Data'][$i]['TaskBy'] =$row_task['TaskBy'];
/*
$sql_dueDate = array('table'=>'ct_case_event ','where'=>"CaseId == '".$row['CaseId']."' AND DueDate !=''" );
$view_dueDate = $this->view($sql_dueDate);
$result_dueDate = $this->q_re($view_dueDate);
$num_dueDate = $this->q_nr($result_dueDate);
$d=0;
if($num_dueDate>0){
while ($row_dueDate = $this->q_ro($result_dueDate)) {
$data['Data'][$i]['DueDate'][$d] =$row_dueDate;
$d++;
}
}
*/
//$data['Data'][$i]['DueDate_sql'] = $view_dueDate;
$sql_lastEvent = array('table'=>'ct_case_event cv INNER JOIN ct_master_event ev ON cv.EventId = ev.EventId','where'=>"cv.CaseId = '".$row['CaseId']."'" );
$view_lastEvent = $this->view($sql_lastEvent);
$result_lastEvent = $this->q_re($view_lastEvent);
$num_lastEvent = $this->q_nr($result_lastEvent);
$r=0;
if($num_lastEvent>0){
while ($row_lastEvent = $this->q_ro($result_lastEvent)) {
$data['Data'][$i]['LastEvent'][$r] =$row_lastEvent;
$r++;
}
}
$i++;
}
}else {
$data['Data']="";
}
$data['fQuotationType'] =$fQuotationType;
$data['fCountry'] =$fCountry;
$data['fCaseType'] =$fCaseType;
$data['fCaseNo'] =$fCaseNo;
$data['fStatus'] =$fStatus;
$data['fCustomer'] =$fCustomer;
$data['sql'] = $view;
return $data;
}
public function MyTimeSheetSummaryExcelandPdf($request)
{
$TaskDateStart = $request['TaskDateStart'];
$TaskDateEnd = $request['TaskDateEnd'];
$year = $request['Year'];
$month = $request['Month'];
$uid = $request['UserId'];
$name_table = " (";
$name_table .= " SELECT (SELECT concat(Name, ' ', Surname) FROM t_system_users WHERE Id = t.CreateBy) as TaskName,t.TimeSheetId,t.TaskDate,t.CategoryId,t.TaskDescription,t.Duration,t.Del,t.CaseId,t.CaseTimeSheetId,c.CaseNumber,concat(n.NamesCode,' ',n.NamesName) as CustomerCode";
$name_table .= " FROM ct_timesheet t";
$name_table .= " LEFT JOIN ct_case c ON t.CaseId=c.CaseId";
$name_table .= " LEFT JOIN t_names_mas n ON t.CustomerId=n.NamesId";
$name_table .= " ";
$secrch = "t.Del = 0";
/*
if($year!=''){
$secrch .= " AND YEAR(t.TaskDate)='".$year."'";
}
if($month!=''){
$secrch .= " AND MONTH(t.TaskDate)='".$month."'";
}
*/
if($uid!=''){
$secrch .= " AND t.CreateBy = '".$uid."'";
}
if(!empty($TaskDateStart) || !empty($TaskDateEnd) ){
$secrch .= " AND t.TaskDate BETWEEN '$TaskDateStart' AND '$TaskDateEnd'";
}
$secrch .= " ) A";
$order = " ORDER BY A.TaskName,A.TaskDate ";
$sql = array('table'=>$name_table,'where'=>$secrch, 'order'=>$order);
$view = $this->view($sql);
$result = $this->q_re($view);
$result_num = $this->q_nr($result);
$i=0;$data=array();
if($result_num>0){
while ($row = $this->q_ro($result)) {
$data['Data'][$i]['TaskName'] =$row['TaskName'];
$data['Data'][$i]['TimeSheetId'] =$row['TimeSheetId'];
$data['Data'][$i]['TaskDate'] =$row['TaskDate'];
$data['Data'][$i]['TaskDescription'] =$row['TaskDescription'];
$data['Data'][$i]['Duration'] =$row['Duration'];
$data['Data'][$i]['Del'] =$row['Del'];
$data['Data'][$i]['CaseId'] =$row['CaseId'];
$data['Data'][$i]['CaseTimeSheetId'] =$row['CaseTimeSheetId'];
$data['Data'][$i]['CaseNumber'] =$row['CaseNumber'];
$data['Data'][$i]['CustomerCode'] =$row['CustomerCode'];
$data['Data'][$i]['CategoryId'] =$row['CategoryId'];
$data['Data'][$i]['Category'] ="";
$sql_cate = array('table'=>'ct_category_timesheet','where'=>'CategoryId = "'.$row['CategoryId'].'"');
$view_cate = $this->view($sql_cate);
$result_cate = $this->q_re($view_cate);
$result_cate_num = $this->q_nr($result_cate);
if($result_cate_num>0){
$row_cate = $this->q($view_cate);
$data['Data'][$i]['Category'] =$row_cate['CategoryName'];
}
$i++;
}
}else{
$data['Data'] ='';
}
$data['sql'] =$view;
return $data;
}
public function TimeSheetSummaryExcelandPdf($request)
{
$uid = $request['UserId'];
$dtstar= date_create(str_replace("/","-",$request['TaskDateStart']));
$dtend= date_create(str_replace("/","-",$request['TaskDateEnd']));
$dtstart = date_format($dtstar, "Y-m-d H:i:s");
$dtend = date_format($dtend, "Y-m-d H:i:s");
$name_table = " (";
$name_table .= " SELECT (SELECT concat(Name, ' ', Surname) FROM t_system_users WHERE Id = t.CreateBy) as TaskName,t.TimeSheetId,t.TaskDate,t.TaskDescription,t.Duration,t.Del,t.CaseId,t.CaseTimeSheetId,c.CaseNumber,concat(n.NamesCode, '-',n.NamesName) as CustomerCode";
$name_table .= " FROM ct_timesheet t";
$name_table .= " LEFT JOIN ct_case c ON t.CaseId=c.CaseId";
$name_table .= " LEFT JOIN t_names_mas n ON t.CustomerId=n.NamesId";
$secrch = " t.Del = 0";
if($request['TaskDateStart']!='' && $request['TaskDateEnd']!=''){
$secrch .= " AND t.TaskDate BETWEEN '".$dtstart."' AND '".$dtend."'";
}
if($uid!=''){
$secrch .= " AND t.CreateBy = '".$uid."'";
}
if($request['CustomerId']!=''){
$cid = $request['CustomerId'];
$secrch_names = "NamesId = '".$request['CustomerId']."'";
$sql_names = array('table'=>'t_names_mas','where'=>$secrch_names);
$view_names = $this->view($sql_names);
$row_names = $this->q($view_names);
$fCustomer = $row_names['NamesName'];
$secrch .= " AND t.CustomerId = '".$cid."'";
}
if($request['CaseNumber']!=''){
$caseNumber = $request['CaseNumber'];
$fCaseNo = $request['CaseNumber'];
$secrch .= " AND c.CaseNumber LIKE '".$caseNumber."'";
}
if($request['CaseId']!=''){
$caseId = $request['CaseId'];
$secrch .= " AND c.CaseId LIKE '".$caseId."'";
}
$secrch .= " ) A";
$order = " ORDER BY A.TaskName,A.TaskDate ";
$sql = array('table'=>$name_table,'where'=>$secrch, 'order'=>$order);
$view = $this->view($sql);
$result = $this->q_re($view);
$result_num = $this->q_nr($result);
$i=0;$data=array();
if($result_num>0){
while ($row = $this->q_ro($result)) {
//$data[$i]['CountryId'] =$row['CountryId'];
$data['Data'][$i]['TaskName'] =$row['TaskName'];
$data['Data'][$i]['TimeSheetId'] =$row['TimeSheetId'];
$data['Data'][$i]['TaskDate'] =$row['TaskDate'];
$data['Data'][$i]['TaskDescription'] =$row['TaskDescription'];
$data['Data'][$i]['Duration'] =$row['Duration'];
$data['Data'][$i]['Del'] =$row['Del'];
$data['Data'][$i]['CaseId'] =$row['CaseId'];
$data['Data'][$i]['CaseTimeSheetId'] =$row['CaseTimeSheetId'];
$data['Data'][$i]['CaseNumber'] =$row['CaseNumber'];
$data['Data'][$i]['CustomerCode'] =$row['CustomerCode'];
$i++;
}
}else {
$data['Data'] = "";
}
$data['sql'] =$view;
$data['fCustomer'] = $fCustomer;
$data['fCaseNo'] = $fCaseNo;
return $data;
}
public function ExportToExcel($request)
{
$name_table = "t_names_mas";
$secrch = "1 = 1";
if(@$request['CustomerId']!=''){
$secrch .= " AND NamesId = '".$request['NamesId']."'";
}
$sql = array('table'=>$name_table,'where'=>$secrch);
$view = $this->view($sql);
$result = $this->q_re($view);
$i=0;$data=array();
while ($row = $this->q_ro($result)) {
$data[$i]['NamesId'] =$row['NamesId'];
$data[$i]['NamesName'] =$row['NamesName'];
$data[$i]['NamesCode'] =$row['NamesCode'];
$data[$i]['Telephone'] =$row['Telephone'];
$data[$i]['PostalAddress'] =$row['PostalAddress'];
$data[$i]['Fax'] =$row['Fax'];
$data[$i]['ElectronicMail'] =$row['ElectronicMail'];
$data[$i]['TaxID'] =$row['TaxID'];
$data[$i]['Remark'] =$row['Remark'];
$data[$i]['Initial'] =$row['Initial'];
$i++;
}
return $data;
// code...
}
public function AgedDebtExcelandPdf($request)
{
$StartDate = $request['StartDate'];
$EndDate = $request['EndDate'];
$CustomerId = $request['CustomerId'];
$count = "
inv.*,c.NamesName as CustomerName,c.NamesCode as CustomerCode
,DATEDIFF(CURDATE(),DATE_FORMAT(inv.CreateDateTime,'%Y-%m-%d')) as DiffDays
,(SELECT SUM(A.Amount) FROM (SELECT Case When q2.Currency = 'usd'
THEN (Amount * (SELECT ThbPerOneUsb FROM ct_setting WHERE SettingId = 1))
ELSE Amount
END as Amount FROM ct_invoice c2
INNER JOIN ct_quotation q2 ON c2.QuotationId = q2.QuotationId
INNER JOIN ct_invoice_item inv2 ON inv2.InvoiceId = c2.InvoiceId
WHERE inv2.InvoiceId = c2.InvoiceId) A) as OutStanding
";
$name_table = "
ct_invoice inv
INNER JOIN t_names_mas c ON inv.CustomerId=c.NamesId
INNER JOIN ct_quotation q ON inv.QuotationId = q.QuotationId
";
$secrch = "inv.Del = 0 AND (SELECT COUNT(*) FROM ct_tax_invoice WHERE InvoiceId = inv.InvoiceId AND Del = 0) = 0";
if(@$StartDate!=''){
$secrch = $secrch." AND inv.CreateDateTime >= '".$this->date_chang_1(@$StartDate)."'";
}
if(@$EndDate!=''){
$secrch = $secrch." AND inv.CreateDateTime <= '".$this->date_chang_1(@$EndDate)."'";
}
if(@$CustomerId!=''){
$secrch = $secrch." AND c.NamesId = '".@$CustomerId."'";
}
//c.NamesName,c.NamesCode,CreateDateTime
$order = " ORDER BY c.CreateDateTime desc ";
$sql = array('table'=>$name_table, 'count' =>$count, 'where'=>$secrch, 'order'=>$order);
$view = $this->view($sql);
$result = $this->q_re($view);
$result_num = $this->q_nr($result);
$i=0;$data=array();
if($result_num>0){
while ($row = $this->q_ro($result)) {
//$data[$i]['CountryId'] =$row['CountryId'];
$data['Data'][$i]['InvoiceId'] =$row['InvoiceId'];
$data['Data'][$i]['InvoiceNo'] =$row['InvoiceNo'];
$data['Data'][$i]['QuotationId'] =$row['QuotationId'];
$data['Data'][$i]['CustomerId'] =$row['CustomerId'];
$data['Data'][$i]['Vat'] =$row['Vat'];
$data['Data'][$i]['BillDetail'] =$row['BillDetail'];
$data['Data'][$i]['Status'] =$row['Status'];
$data['Data'][$i]['Del'] =$row['Del'];
$data['Data'][$i]['CreateBy'] =$row['CreateBy'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['UpdateBy'] =$row['UpdateBy'];
$data['Data'][$i]['UpdateDateTime'] =$row['UpdateDateTime'];
$data['Data'][$i]['IsWitholdingTax'] =$row['IsWitholdingTax'];
$data['Data'][$i]['IsExcludeVat'] =$row['IsExcludeVat'];
$data['Data'][$i]['IsCo'] =$row['IsCo'];
$data['Data'][$i]['IsConsultService'] =$row['IsConsultService'];
$data['Data'][$i]['ResponseId'] =$row['ResponseId'];
$data['Data'][$i]['CustomerName'] =$row['CustomerName'];
$data['Data'][$i]['CustomerCode'] =$row['CustomerCode'];
$data['Data'][$i]['DiffDays'] =$row['DiffDays'];
$data['Data'][$i]['OutStanding'] =$row['OutStanding'];
$i++;
}
}else {
$data['Data'] = '';
}
$data['request'] =$request;
$data['sql'] =$view;
return $data;
}
public function ReceivedPaymentExcelandPdf($request)
{
$StartDate = $request['StartDate'];
$EndDate = $request['EndDate'];
$CustomerId = $request['CustomerId'];
$count = "
*,(Amount * Vat/100) as Tax,(Amount + ((Amount * Vat/100))) as Gross
FROM (
SELECT ti.CreateDateTime,ti.TaxInvoiceNo,inv.InvoiceNo,c.NamesName as CustomerName,c.NamesCode as CustomerCode,inv.Vat
,(SELECT SUM(A.Amount) FROM
(SELECT Case When q2.Currency = 'usd'
THEN (Amount * (SELECT ThbPerOneUsb FROM ct_setting WHERE SettingId = 1))
ELSE Amount END as Amount
FROM ct_invoice_item inv2
INNER JOIN ct_quotation q2 ON inv2.InvoiceId = q2.QuotationId
INNER JOIN ct_invoice c2 ON c2.InvoiceId = inv2.InvoiceId
WHERE c2.InvoiceId = inv2.InvoiceId)
A)
as Amount
";
$name_table = "
ct_tax_invoice ti
INNER JOIN ct_invoice inv ON ti.InvoiceId = inv.InvoiceId
INNER JOIN t_names_mas c ON inv.CustomerId=c.NamesId
INNER JOIN ct_quotation q ON inv.QuotationId = q.QuotationId";
$secrch = " ti.Del = 0 AND ti.Status = 2";
if(@$StartDate!='' && @$EndDate!=''){
$secrch = $secrch. " AND ti.CreateDateTime BETWEEN '".$this->date_chang_1(@$StartDate)."' AND '".$this->date_chang_1(@$EndDate)."'";
}
if(@$CustomerId!=''){
$secrch = $secrch." AND c.NamesId = '".$CustomerId."'";
}
$secrch = $secrch ." ) A ";
$order = " ORDER BY CreateDateTime DESC ";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$secrch, 'order'=>$order);
$view = $this->view($sql);
$result = $this->q_re($view);
$num_result = $this->q_nr($result);
$i=0;$data=array();
if($num_result>0){
while ($row = $this->q_ro($result)) {
//$data[$i]['CountryId'] =$row['CountryId'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['TaxInvoiceNo'] =$row['TaxInvoiceNo'];
$data['Data'][$i]['InvoiceNo'] =$row['InvoiceNo'];
$data['Data'][$i]['CustomerName'] =$row['CustomerName'];
$data['Data'][$i]['CustomerCode'] =$row['CustomerCode'];
$data['Data'][$i]['Vat'] =$row['Vat'];
$data['Data'][$i]['Amount'] =$row['Amount'];
$data['Data'][$i]['Tax'] =$row['Tax'];
$data['Data'][$i]['Gross'] =$row['Gross'];
$i++;
}
}else {
$data['Data'] = '';
}
$data['sql'] =$view;
return $data;
}
public function QuotationRetainerSummaryExcelandPdf($request)
{
$StartDate = $request['StartDate'];
$EndDate = $request['EndDate'];
$CustomerId = $request['CustomerId'];
$count = "
q.QuotationNo,c.CaseNumber,c.CaseDescription,n.NamesCode as CustomerCode,n.NamesName as CustomerName,YEAR(c.CreateDateTime) as Year,MONTH(c.CreateDateTime) as Month,c.CreateDateTime AS CreateDateTime,c.LocalQuantity as LocalQuantity,c.InterQuantity as InterQuantity
";
$name_table = "
ct_case c
INNER JOIN ct_quotation q ON c.QuotationId = q.QuotationId AND q.QuotationType = 2
INNER JOIN t_names_mas n ON c.CustomerId = n.NamesId
";
$secrch = "1=1";
if(@$StartDate!='' && @$EndDate!=''){
$secrch = $secrch. " AND c.CreateDateTime BETWEEN '".$this->date_chang_1(@$StartDate)."' AND '".$this->date_chang_1(@$EndDate)."'";
}
if(@$CustomerId!=''){
$secrch = $secrch." AND n.NamesId = '".$CustomerId."'";
}
$order = " ORDER BY c.CreateDateTime DESC ";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$secrch, 'order'=>$order);
$view = $this->view($sql);
$result = $this->q_re($view);
$result_num = $this->q_nr($result);
$i=0;$data=array();
if(@$result_num>0){
while ($row = $this->q_ro($result)) {
//$data[$i]['CountryId'] =$row['CountryId'];
$data['Data'][$i]['QuotationNo'] =$row['QuotationNo'];
$data['Data'][$i]['CaseNumber'] =$row['CaseNumber'];
$data['Data'][$i]['CaseDescription'] =$row['CaseDescription'];
$data['Data'][$i]['CustomerCode'] =$row['CustomerCode'];
$data['Data'][$i]['CustomerName'] =$row['CustomerName'];
$data['Data'][$i]['Year'] =$row['Year'];
$data['Data'][$i]['Month'] =$row['Month'];
$data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
$data['Data'][$i]['LocalQuantity'] =$row['LocalQuantity'];
$data['Data'][$i]['InterQuantity'] =$row['InterQuantity'];
$i++;
}
}else{
$data['Data'] = 'No data found';
}
//$data['result_num'] =$result_num;
$data['sql'] =$view;
//$data['request'] =$request;
return $data;
}
public function QuotationRetainerClientExcelandPdf($request)
{
$StartDate = $request['StartDate'];
$EndDate = $request['EndDate'];
$CustomerId = $request['CustomerId'];
$count = "
q.QuotationNo,c.CaseNumber,c.CaseDescription,n.NamesCode as CustomerCode,n.NamesName as CustomerName,
YEAR(c.CreateDateTime) as Year,
MONTH(c.CreateDateTime) as Month,
c.LocalQuantity as LocalQuantity,
c.InterQuantity as InterQuantity
";
$name_table = "
ct_case c
INNER JOIN ct_quotation q ON c.QuotationId = q.QuotationId AND q.QuotationType = 2
INNER JOIN t_names_mas n ON c.CustomerId = n.NamesId
";
$secrch = "1=1";
if(@$StartDate!='' && @$EndDate!=''){
$secrch = $secrch. " AND c.CreateDateTime BETWEEN '".$this->date_chang_1(@$StartDate)."' AND '".$this->date_chang_1(@$EndDate)."'";
}
if(@$CustomerId!=''){
$secrch = $secrch." AND n.NamesId = '".$CustomerId."'";
}
$order = " ORDER BY c.CreateDateTime DESC ";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$secrch, 'order'=>$order);
$view = $this->view($sql);
$result = $this->q_re($view);
$result_num = $this->q_nr($result);
$i=0;$data=array();
if(@$result_num>0){
while ($row = $this->q_ro($result)) {
//$data[$i]['CountryId'] =$row['CountryId'];
$data['Data'][$i]['QuotationNo'] =$row['QuotationNo'];
$data['Data'][$i]['CaseNumber'] =$row['CaseNumber'];
$data['Data'][$i]['CaseDescription'] =$row['CaseDescription'];
$data['Data'][$i]['CustomerCode'] =$row['CustomerCode'];
$data['Data'][$i]['CustomerName'] =$row['CustomerName'];
$data['Data'][$i]['Year'] =$row['Year'];
$data['Data'][$i]['Month'] =$row['Month'];
$data['Data'][$i]['LocalQuantity'] =$row['LocalQuantity'];
$data['Data'][$i]['InterQuantity'] =$row['InterQuantity'];
$i++;
}
}else{
$data['Data'] = 'No data found';
}
$data['sql'] =$view;
return $data;
}
public function FinanceStatusReportExcelandPdf($request)
{
$StartDate = $request['StartDate'];
$EndDate = $request['EndDate'];
$UserId = $request['UserId'];
$personName = $request['personName'];
$CaseStatus = $request['CaseStatus'];
$PaymentStatus = $request['PaymentStatus'];
$CaseNumber = $request['CaseNumber'];
$count = "";
$name_table = "
(
SELECT cs.CaseNumber,u.Name+' '+u.Surname as Person,q.QuotationNo
,(SELECT SUM(A.Amount) FROM (SELECT Case When q1.Currency = 'usd'
THEN( Amount *(SELECT ThbPerOneUsb FROM ct_setting WHERE SettingId = 1 ))
ELSE Amount
END AS Amount
FROM ct_quotation_payment_terms cp1
INNER JOIN ct_quotation q1 ON cp1.QuotationId = q1.QuotationId
WHERE cp1.QuotationId = q1.QuotationId
) A) as Amount
,(SELECT SUM(A.Amount) FROM (SELECT Case When q2.Currency = 'usd'
THEN( Amount *( SELECT ThbPerOneUsb FROM ct_setting WHERE SettingId = 1))
ELSE Amount
END AS Amount
FROM ct_quotation_payment_terms cp2
INNER JOIN ct_quotation q2 ON cp2.QuotationId = q2.QuotationId
WHERE cp2.QuotationId = q2.QuotationId AND cp2.Status= 3
) A
) as OutStanding
FROM ct_case cs
INNER JOIN t_system_users u ON u.Id = cs.CreateBy
INNER JOIN ct_quotation q ON cs.QuotationId = q.QuotationId
";
$secrch = "1=1";
if(@$StartDate!='' && @$EndDate!=''){
$secrch = $secrch. " AND cs.CreateDateTime BETWEEN '".$this->date_chang_1(@$StartDate)."' AND '".$this->date_chang_1(@$EndDate)."'";
}
if(@$CaseNumber!=''){
$secrch = $secrch." AND cs.CaseNumber LIKE '%".$CustomerId."%'";
$caseNo = $CaseNumber;
}
if(@$UserId!=''){
$secrch = $secrch." AND cs.CreateBy = '".$UserId."'";
$sql_user = array('table'=>"t_system_users",'where'=>"Id = '".$UserId."'");
$view_user = $this->view($sql_user);
$result_user = $this->q($view_user);
$personName = $result_user['Name'] + " " + $result_user['Surname'];
}
if(@$CaseStatus>0){
if(@$CaseStatus == 1){
$secrch = $secrch." AND (SELECT Count(*) FROM ct_case_event WHERE EventId = 1 AND CaseId=cs.CaseId) > 0";
$secrch = $secrch." AND (SELECT Count(*) FROM ct_case_event WHERE EventId = 2 AND CaseId=cs.CaseId) = 0";
$status = "Opening";
}elseif (@$CaseStatus == 2) {
$secrch = $secrch." AND (SELECT Count(*) FROM ct_case_event WHERE EventId = 2 AND CaseId=cs.CaseId) > 0";
$status = "Completed";
}
}
$secrch = $secrch." ) C WHERE 1=1";
if(@$PaymentStatus>0){
if(@$PaymentStatus == 1){
$secrch = $secrch." AND OutStanding = Amount";
$payStatus = "Not Received";
}elseif (@$CaseStatus == 2) {
$secrch = $secrch." AND OutStanding IS NOT NULL AND OutStanding < Amount";
$payStatus = "Received Some";
}else {
$secrch = $secrch." AND OutStanding IS NULL";
$payStatus = "Received All";
}
}
$order = " ";
$sql = array('table'=>$name_table,'count'=>$count,'where'=>$secrch, 'order'=>$order);
$view = $this->view($sql);
$result = $this->q_re($view);
$result_num = $this->q_nr($result);
$i=0;$data=array();
if(@$result_num>0){
while ($row = $this->q_ro($result)) {
//$data[$i]['CountryId'] =$row['CountryId'];
$data['Data'][$i]['CaseNumber'] =$row['CaseNumber'];
$data['Data'][$i]['Person'] =$row['Person'];
$data['Data'][$i]['QuotationNo'] =$row['QuotationNo'];
$data['Data'][$i]['Amount'] =$row['Amount'];
$data['Data'][$i]['OutStanding'] =$row['OutStanding'];
$data['Data'][$i]['payStatus'] =$payStatus;
$data['Data'][$i]['status'] =$status;
$data['Data'][$i]['personName'] =$personName;
$data['Data'][$i]['caseNo'] =$caseNo;
$i++;
}
}else{
$data['Data'] = 'No data found';
}
$data['sql'] =$view;
return $data;
}
}
?>