<?php

class InvoiceClass extends ViewData
{

  public function LoadInvoiceList($request)
  {
    $data['Data'] = array();
    $order = '';
    $count = "inv.*,c.NamesName as CustomerName,c.NamesCode as CustomerCode,c.PostalAddress as Address";
    $name_table = "
     ct_invoice inv INNER JOIN t_names_mas c ON inv.CustomerId=c.NamesId
     INNER JOIN ct_quotation q ON q.QuotationId = inv.QuotationId";
    $search = "inv.Del = 0";
    if(@$request['InvoiceNo']!=''){
      $search = $search. "  AND inv.InvoiceNo LIKE '%".$request['InvoiceNo']."%'";
    }
    if(@$request['CustomerId'] > 0){
      $search = $search. " AND inv.CustomerId = '".$request['CustomerId']."'";
    }
    if($request['OrderDirection']!=''){
      $order = " ORDER BY inv.".$request['OrderBy'] ." ".$request['OrderDirection'];
    }

    $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;


      $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;
    if(@$respons_count_num > 0){

      while ($row = $this->q_ro($result)) {
        $sql_TaxInvoiceNo = array('table'=>"ct_tax_invoice", 'count'=> 'TaxInvoiceNo AS TaxInvoiceNo','where'=>"InvoiceId = '".$row['InvoiceId']."' AND Del = '0'");
        $view_TaxInvoiceNo = $this->view($sql_TaxInvoiceNo);
        $row_TaxInvoiceNo = $this->q($view_TaxInvoiceNo);
        $data['Data'][$i]['TaxInvoiceNo'] =$row_TaxInvoiceNo['TaxInvoiceNo'];

        $sql_IsEdit = array('table'=>"ct_tax_invoice", 'count'=> 'COUNT(*) AS IsEdit','where'=>"InvoiceId = '".$row['InvoiceId']."' AND Del = 0");
        $view_IsEdit= $this->view($sql_IsEdit);
        $row_IsEdit = $this->q($view_IsEdit);
        $data['Data'][$i]['IsEdit'] =$row_IsEdit['IsEdit'];

        $sql_IsPaid = array('table'=>"ct_tax_invoice", 'count'=> 'COUNT(*) AS IsPaid','where'=>"InvoiceId = '".$row['InvoiceId']."' AND Del = 0 AND Status = 2");
        $view_IsPaid= $this->view($sql_IsPaid);
        $row_IsPaid = $this->q($view_IsPaid);
        $data['Data'][$i]['IsPaid'] =$row_IsPaid['IsPaid'];

        $sql_Amount = array('table'=>"ct_invoice_item", 'count'=> 'SUM(Amount) AS Amount','where'=>"InvoiceId = '".$row['InvoiceId']."'");
        $view_Amount= $this->view($sql_Amount);
        $row_Amount = $this->q($view_Amount);
        $data['Data'][$i]['Amount'] = floatval($row_Amount['Amount']);

        $sql_AmountVat = array('table'=>"ct_invoice_item", 'count'=> 'SUM(Amount) * (inv.Vat / 100) AS AmountVat','where'=>"InvoiceId = '".$row['InvoiceId']."' AND (IsExcludeVat != '' OR IsExcludeVat = 0)");
        $view_AmountVat= $this->view($sql_AmountVat);
        $row_AmountVat = $this->q($view_AmountVat);
        $data['Data'][$i]['AmountVat'] = floatval($row_AmountVat['AmountVat']);

        $sql_Responsibility = array('table'=>"t_system_users", 'count'=> 'CONCAT(Name," ",Surname) AS Responsibility','where'=>"Id = '".$row['ResponseId']."'");
        $view_Responsibility= $this->view($sql_Responsibility);
        $row_Responsibility = $this->q($view_Responsibility);
          $data['Data'][$i]['Responsibility'] =$row_Responsibility['Responsibility'];


         $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]['Address'] =$row['Address'];


         $description = "";
         $sql2 = array('table'=>"ct_case",'where'=>"QuotationId = '".$row['QuotationId']."'", 'order'=> " ORDER BY CaseId ASC");
         $view2 = $this->view($sql2);
         $row2 = $this->q($view2);
         if(@$data['Data'][$i]['description']!=''){
           $description = $description."<br/>";
         }
         $description = $description."<b style='color:#000;'>[".$row2['CaseNumber']."]</b><br/>";
         $description = $description." ".$row2['CaseDescription']." ";

         if(@$row2['ResponseId']!=''){
           $sql3 = array('table'=>"t_names_mas",'where'=>"Id = '".$row2['ResponseId']."'");
           $view3 = $this->view($sql3);
           $row3 = $this->q($view3);

           $description = $description."<br/><span style='color:#2653d4;'><i>".@$row3['UserName']."</i></span>";

         }

          $data['Data'][$i]['CaseDescription'] = $description;
          $data['Data'][$i]['description'] = $description;

         $i++;
      }
    }

    $data['PageNumber'] = $request['PageNumber'];
    $data['CustomerID'] = $request['CustomerID'];
    $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['sql2'] = $view_count;
    $data['re'] = $request;

    return $data;
  }

  public function InvoiceEditResponse($request)
  {
    $name_table = "ct_invoice";
    $secrch = "InvoiceId = '".$request['InvoiceId']."'";
    $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]['InvoiceId'] =$row['InvoiceId'];
     $data[$i]['InvoiceNo'] =$row['InvoiceNo'];
     $data[$i]['QuotationId'] =$row['QuotationId'];
     $data[$i]['CustomerId'] =$row['CustomerId'];
     $data[$i]['Vat'] =$row['Vat'];
     $data[$i]['BillDetail'] =$row['BillDetail'];
     $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'];
     $data[$i]['IsWitholdingTax'] =$row['IsWitholdingTax'];
     $data[$i]['IsExcludeVat'] =$row['IsExcludeVat'];
     $data[$i]['IsCo'] =$row['IsCo'];
     $data[$i]['IsConsultService'] =$row['IsConsultService'];
     $data[$i]['ResponseId'] =$row['ResponseId'];
     $i++;
    }
    return $data;
  }

  public function LoadInvoiceItems($Id)
  {

      $name_table = "ct_invoice";
      $count = "";
      $order = "";
      $secrch = "InvoiceId = '".$Id."'";

      $secrch_isConsult = "InvoiceId = '".$Id."' AND IsConsultService = '1' ";
      $sql_isConsult = array('table'=>$name_table,'where'=>$secrch_isConsult);
      $view_isConsult = $this->view($sql_isConsult);
      $result_isConsult = $this->q_re($view_isConsult);
      $row_isConsult = $this->q($view_isConsult);


      if($row_isConsult){
        $order = " ORDER BY ActionDate ASC";
      }
      $sql = array('table'=>$name_table,'where'=>$secrch, 'order'=>$order);

      $view = $this->view($sql);
      $result = $this->q_re($view);
      $row = $this->q($view);
      $i=0;


      $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]['UserName'] =@$row['UserName'];
      $data['Data'][$i]['Name'] =@$row['Name'];
      $data['Data'][$i]['MiddleName'] =@$row['MiddleName'];
      $data['Data'][$i]['Surname'] =@$row['Surname'];
      $data['Data'][$i]['namefull'] =@$row['Name'] ." ".@$row['Surname'];
      $data['Data'][$i]['view'] =@$view;



      if(@$row['QuotationId']!=''){
        $sql3 = array('table'=>"ct_quotation",'where'=>"QuotationId = '".@$row['QuotationId']."'");
        $view3 = $this->view($sql3);
        $row3 = $this->q($view3);

        $data['Data'][$i]['QuotationNo'] =@$row3['QuotationNo'];
      }

      /*---------------------------------------------------------------------*/
      /*---------------------------------------------------------------------*/
      /*---------------------------------------------------------------------*/
      $sql2 = array('table'=>"ct_invoice_item",'where'=>"InvoiceId = '".$Id."'", 'order'=>' ORDER BY ActionDate ASC');
      $view2 = $this->view($sql2);
      $result2 = $this->q_re($view2);
      $count = $this->q_nr($result2);
      $data['Data2'] = array();
      $i2=0;
      if(@$count>0){
        while ($row2 = $this->q_ro($result2)) {
          // code...
          $data['Data2'][$i2]['InvoiceItemId'] =$row2['InvoiceItemId'];
          $data['Data2'][$i2]['ActionDate'] =$row2['ActionDate'];
          $data['Data2'][$i2]['Amount'] = floatval($row2['Amount']);
          $data['Data2'][$i2]['CaseDisburseId'] =$row2['CaseDisburseId'];
          $data['Data2'][$i2]['CaseExpenseId'] =$row2['CaseExpenseId'];
          $data['Data2'][$i2]['Category'] =$row2['Category'];
          $data['Data2'][$i2]['Description'] =$row2['Description'];

          $data['Data2'][$i2]['IsExcludeVat'] =$row2['IsExcludeVat'];
          $data['Data2'][$i2]['QuotationPaymentTermId'] =$row2['QuotationPaymentTermId'];
          $data['Data2'][$i2]['Remark'] =$row2['Remark'];
          $i2++;
        }

      }else{
        $data['Data2']= array();
      }
      $data['view'] = @$view;
      $data['view2'] = @$view2;
      $data['view3'] = @$view3;

      return $data;
  }

  public function Index($request)
  {
    $name_table = "ct_invoice";
    $secrch = "InvoiceId = '".$request['InvoiceId']."'";
    $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['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]['Tax'] =$row['Tax'];
     $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]['Lbilling'] =$row['Lbilling'];

     if(@$row['QuotationId']!=''){
       $sql3 = array('table'=>"ct_quotation",'where'=>"QuotationId = '".@$row['QuotationId']."'");
       $view3 = $this->view($sql3);
       $row3 = $this->q($view3);

       $data['Data'][$i]['QuotationNo'] =@$row3['QuotationNo'];
       $data['Data'][$i]['Currency'] =@$row3['Currency'];
     }

     $i++;
    }
    return $data;
  }

  public function ShopQuotationPaymentTerms($request)
  {
    $name_table = "ct_quotation_payment_terms w";
    $secrch = " w.QuotationId = '".$request['Id']."'";
    $sql = array('table'=>$name_table,'where'=>$secrch);
    $view = $this->view($sql);
    $result = $this->q_re($view);
    $result_num = $this->q_nr($result);
    $i=0;$data=array();
     $data['Data'] = array();
    if(@$result_num>0){
      while ($row = $this->q_ro($result)) {

       $sql2 = array('table'=>"ct_invoice_item a",'where'=>"a.QuotationPaymentTermId = '".@$row['QuotationPaymentTermId']."'
       AND (SELECT  aa.InvoiceId FROM ct_invoice aa  WHERE  aa.InvoiceId = a.InvoiceId AND Del != '1')");

       $view2 = $this->view($sql2);
       $result2 = $this->q_re($view2);
       $result_num2 = $this->q_nr($result2);
       if($result_num2==0){

         $sql3 = array('table' => 'ct_quotation_payment_terms', 'where' => "QuotationPaymentTermId = '".@$row['QuotationPaymentTermId']."'" );
         $view3 = $this->view($sql3);
         $row3 =  $this->q($view3);

         $data['Data'][$i]['QuotationPaymentTermId'] = @$row3['QuotationPaymentTermId'];
         $data['Data'][$i]['QuotationId'] =@$row3['QuotationId'];
         $data['Data'][$i]['Detail'] =@$row3['Detail'];
         $data['Data'][$i]['Status'] =@$row3['Status'];
         $data['Data'][$i]['Amount'] = floatval(@$row3['Amount']);
         $data['Data'][$i]['CreateBy'] =@$row3['CreateBy'];
         $i++;
       }

      }
    }else{
      $data['Data'] = array();
    }
    $data['sql'] = $view;
    $data['sql2'] = $view2;
    $data['request'] = $request;
    return $data;
  }

  public function ExportPdf($request)
  {
    $matterNo =""; $isConsultService=0;
    $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
     ";
     $count = "
     inv.*,
     c.NamesName as CustomerName,c.NamesCode as CustomerCode,c.TaxID,c.PostalAddress as Address,c.Telephone,c.NamesId,c.Fax,c.ElectronicMail
    ,q.Currency,q.QuotationNo,(SELECT COUNT(*) FROM ct_tax_invoice WHERE InvoiceId = inv.InvoiceId AND Del = 0) as IsEdit
    ";

    $secrch = "inv.Del = 0 AND inv.InvoiceId = '".@$request."'";
    $sql = array('table'=>$name_table,'count'=>$count,'where'=>$secrch);
    $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)) {

        $isConsultService = ($row['IsConsultService']==1)?1 : 0;
        $data['Data'][$i]['InvoiceId'] =$row['InvoiceId'];
        $data['Data'][$i]['InvoiceNo'] =$row['InvoiceNo'];
        $data['Data'][$i]['QuotationId'] =$row['QuotationId'];
        $data['Data'][$i]['QuotationNo'] =$row['QuotationNo'];
        $data['Data'][$i]['CustomerId'] =$row['CustomerId'];
        $data['Data'][$i]['Vat'] =$row['Vat'];
        $data['Data'][$i]['Tax'] =$row['Tax'];
        $data['Data'][$i]['BillDetail'] =$row['BillDetail'];
        $data['Data'][$i]['Lbilling'] =$row['Lbilling'];
        $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]['NamesId'] =$row['NamesId'];
        $data['Data'][$i]['NamesName'] =$row['CustomerName'];
        $data['Data'][$i]['NamesCode'] =$row['CustomerCode'];
        $data['Data'][$i]['Telephone'] =$row['Telephone'];
        $data['Data'][$i]['PostalAddress'] =$row['Address'];
        $data['Data'][$i]['Fax'] =$row['Fax'];
        $data['Data'][$i]['ElectronicMail'] =$row['ElectronicMail'];
        $data['Data'][$i]['TaxID'] =$row['TaxID'];
        $data['Data'][$i]['IsEdit'] =$row['IsEdit'];

        $data['Data'][$i]['Remark'] = $row['Remark'];
        $data['Data'][$i]['Initial'] = $row['Initial'];
        $data['Data'][$i]['StatusActive'] =$row['StatusActive'];
        $data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
        $data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
        $data['Data'][$i]['Deleted'] =$row['Deleted'];
        $data['Data'][$i]['QuotationNo'] =$row['QuotationNo'];
        $data['Data'][$i]['CountryId'] =$row['CountryId'];
        $data['Data'][$i]['QuotationType'] =$row['QuotationType'];
        $data['Data'][$i]['RetainerStart'] =$row['RetainerStart'];
        $data['Data'][$i]['RetainerEnd'] =$row['RetainerEnd'];
        $data['Data'][$i]['Condition'] =$row['Condition'];
        $data['Data'][$i]['CaseTypeId'] =$row['CaseTypeId'];
        $data['Data'][$i]['CreditType'] =$row['CreditType'];
        $data['Data'][$i]['CreditTerms'] =$row['CreditTerms'];
        $data['Data'][$i]['ContactPerson'] =$row['ContactPerson'];
        $data['Data'][$i]['Currency'] =$row['Currency'];
        $data['Data'][$i]['LocalQuantity'] =$row['LocalQuantity'];
        $data['Data'][$i]['InterQuantity'] =$row['InterQuantity'];

        $i++;
      }
    }else{
      $data['Data'] = array();
    }

    $sql_item = array('table'=>"ct_invoice_item",'where'=>"InvoiceId = '".$request."'");
    $view_item = $this->view($sql_item);
    $result_item = $this->q_re($view_item);
    $count_item = $this->q_nr($result_item);

    $i2=0;
    if(@$count_item>0){
        while ($row_item = $this->q_ro($result_item)) {
          $data['Data2'][$i2]['InvoiceItemId'] = $row_item['InvoiceItemId'];
          $data['Data2'][$i2]['InvoiceId'] = $row_item['InvoiceId'];
          $data['Data2'][$i2]['Description'] = $row_item['Description'];
          $data['Data2'][$i2]['Amount'] = $row_item['Amount'];
          $data['Data2'][$i2]['QuotationPaymentTermId'] = $row_item['QuotationPaymentTermId'];
          $data['Data2'][$i2]['Remark'] = $row_item['Remark'];
          $data['Data2'][$i2]['IsExcludeVat'] = $row_item['IsExcludeVat'];
          $data['Data2'][$i2]['CaseDisburseId'] = $row_item['CaseDisburseId'];
          $data['Data2'][$i2]['CaseExpenseId'] = $row_item['CaseExpenseId'];
          $data['Data2'][$i2]['ActionDate'] = $row_item['ActionDate'];

          $data['Data2'][$i2]['Category'] = $row_item['Category'];
          $i2++;
        }
    }else{
      $data['Data2'] = array();
    }

    $sql_caseList = array('table'=>"ct_case",'where'=>"QuotationId = '".$data['Data'][0]['QuotationId']."'");
    $view_caseList = $this->view($sql_caseList);
    $result_caseList = $this->q_re($view_caseList);
    $count_caseList = $this->q_nr($result_caseList);
    $i3=0;
    if(@$count_caseList>0){
        while ($row_caseList = $this->q_ro($result_caseList)) {
          $data['Data3'][$i3]['CaseId'] = $row_caseList['CaseId'];
          $data['Data3'][$i3]['CaseNumber'] = $row_caseList['CaseNumber'];
          $data['Data3'][$i3]['CaseTypeId'] = $row_caseList['CaseTypeId'];
          $data['Data3'][$i3]['CaseDescription'] = $row_caseList['CaseDescription'];
          $data['Data3'][$i3]['QuotationId'] = $row_caseList['QuotationId'];
          $data['Data3'][$i3]['CustomerId'] = $row_caseList['CustomerId'];
          $data['Data3'][$i3]['CreateBy'] = $row_caseList['CreateBy'];
          $data['Data3'][$i3]['CreateDateTime'] = $row_caseList['CreateDateTime'];
          $data['Data3'][$i3]['UpdateBy'] = $row_caseList['UpdateBy'];
          $data['Data3'][$i3]['UpdateDateTime'] = $row_caseList['UpdateDateTime'];
          $data['Data3'][$i3]['LocalQuantity'] = $row_caseList['LocalQuantity'];
          $data['Data3'][$i3]['InterQuantity'] = $row_caseList['InterQuantity'];
          $data['Data3'][$i3]['IsNoCounting'] = $row_caseList['IsNoCounting'];
          $data['Data3'][$i3]['ResponseId'] = $row_caseList['ResponseId'];

          $i3++;
        }
    }else{
      $data['Data3'] = array();
    }



    $data['sql'] = $view;
    $data['sql2'] = $view2;
    $data['view_item'] = $view_item;
    $data['result_item'] = $result_item;
    $data['count_item'] = $count_item;
    $data['view_caseList'] = $view_caseList;
    //$data['content'] = $content;
    $data['isConsultService'] = $isConsultService;
    return $data;
  }

  public function ShopExpense($request)
  {

    $name_table = "ct_case_expense w";
    $count = "w.CaseId,w.CaseExpenseId,w.IsExcludeVat";
    $secrch = "(w.Del = 0 AND ( SELECT c.QuotationId FROM ct_case c WHERE c.CaseId = w.CaseId AND c.QuotationId = '".$request['Id']."')	)";


    $sql = array('table'=>$name_table,'count'=>$count,'where'=>$secrch);
    $view = $this->view($sql);
    $result = $this->q_re($view);
    $result_num = $this->q_nr($result);
    $i=0;
    $data=array();
    $data['Data'] = array();
    if(@$result_num>0){
      while ($row = $this->q_ro($result)) {

        $sql2 = array('table'=>"ct_invoice_item a",'where'=>"a.CaseExpenseId = '".@$row['CaseExpenseId']."'
        AND (SELECT  aa.InvoiceId FROM ct_invoice aa  WHERE  aa.InvoiceId = a.InvoiceId AND Del != '1')");

        $view2 = $this->view($sql2);
        $result2 = $this->q_re($view2);
        $result_num2 = $this->q_nr($result2);
        if($result_num2==0){
          $sql3 = array('table' => 'ct_case_expense', 'where' => "Del = 0 AND CaseExpenseId = '".@$row['CaseExpenseId']."'" );
          $view3 = $this->view($sql3);
          $row3 =  $this->q($view3);
          $data['Data'][$i]['CaseExpenseId'] =@$row3['CaseExpenseId'];
          $data['Data'][$i]['CaseId'] =@$row3['CaseId'];
          $data['Data'][$i]['CreateBy'] =@$row3['CreateBy'];
          $data['Data'][$i]['Del'] =@$row3['Del'];
          $data['Data'][$i]['ExpenseDate'] =@$row3['ExpenseDate'];
          $data['Data'][$i]['ExpenseDetail'] =@$row3['ExpenseDetail'];
          $data['Data'][$i]['ExpenseStatus'] =@$row3['ExpenseStatus'];
          $data['Data'][$i]['ExpenseTotal'] = floatval(@$row3['ExpenseTotal']);
          $data['Data'][$i]['Category'] = @$row3['Category'];
          $data['Data'][$i]['IsExcludeVat'] = @$row3['IsExcludeVat'];

          $data['Data'][$i]['sql'] = @$view3;
          $i++;
        }

      }
    }else {
      $data['Data'] = array();
    }


    $data['sql'] = $view;
    $data['sql2'] = $view2;
    $data['result_num2'] = $result_num2;
    $data['request'] = $request;

    return $data;
  }

  public function ShopDisbursement($request)
  {

    $name_table = "ct_case_disbursement w";
    $count = "w.CaseId,w.CaseDisburseId";
    $secrch = "(SELECT c.QuotationId FROM ct_case c WHERE c.CaseId = w.CaseId AND c.QuotationId = '".$request['Id']."' )";



    $sql = array('table'=>$name_table,'count'=>$count,'where'=>$secrch);
    $view = $this->view($sql);
    $result = $this->q_re($view);
    $result_num = $this->q_nr($result);
    $i=0;
    $data=array();
    $data['Data'] = array();
    if(@$result_num>0){
      while ($row = $this->q_ro($result)) {

        $sql2 = array('table'=>"ct_invoice_item a",'where'=>"a.CaseDisburseId = '".@$row['CaseDisburseId']."'
        AND ( SELECT  aa.InvoiceId FROM ct_invoice aa  WHERE aa.InvoiceId = a.InvoiceId AND Del != '1'  ) ");

        $view2 = $this->view($sql2);
        $result2 = $this->q_re($view2);
        $result_num2 = $this->q_nr($result2);
        if($result_num2==0){
          $sql3 = array('table' => 'ct_case_disbursement', 'where' => "Del = 0 AND CaseDisburseId = '".@$row['CaseDisburseId']."'" );
          $view3 = $this->view($sql3);
          $row3 =  $this->q($view3);
          $data['Data'][$i]['CaseDisburseId'] =@$row3['CaseDisburseId'];
          $data['Data'][$i]['CaseId'] =@$row3['CaseId'];
          $data['Data'][$i]['Date'] =@$row3['Date'];
          $data['Data'][$i]['Category'] =@$row3['Category'];
          $data['Data'][$i]['Detail'] =@$row3['Detail'];
          $data['Data'][$i]['Amount'] = floatval(@$row3['Amount']);
          $data['Data'][$i]['Del'] =@$row3['Del'];
          $data['Data'][$i]['CreateBy'] =@$row3['CreateBy'];

          $i++;

        }
      }
    }else{
      $data['Data'] = array();
    }


    $data['sql'] = $view2;
    $data['request'] = $request;
    $data['result_num2'] = $result_num2;

    return $data;
  }

  public function ShopPayment($request)
  {

    $name_table = "ct_quotation_payment_terms w";
    $count = "w.QuotationId,w.QuotationPaymentTermId";
    $secrch = "w.QuotationId = '".$request['Id']."'";

    $sql = array('table'=>$name_table,'count'=>$count,'where'=>$secrch);
    $view = $this->view($sql);
    $result = $this->q_re($view);
    $result_num = $this->q_nr($result);
    $data=array();
    $data['Data'] = array();
    $i=0;
    if(@$result_num>0){
      while ($row = $this->q_ro($result)) {
        $sql2 = array('table'=>"ct_invoice_item a",'where'=>"a.QuotationPaymentTermId = '".@$row['QuotationPaymentTermId']."'
        AND ( SELECT  aa.InvoiceId FROM ct_invoice aa  WHERE aa.InvoiceId = a.InvoiceId AND Del != '1'  ) ");

        $view2 = $this->view($sql2);
        $result2 = $this->q_re($view2);
        $result_num2 = $this->q_nr($result2);
        if($result_num2==0){
          $sql3 = array('table' => 'ct_quotation_payment_terms', 'where' => "QuotationPaymentTermId = '".@$row['QuotationPaymentTermId']."'" );
          $view3 = $this->view($sql3);
          $row3 =  $this->q($view3);
          $data['Data'][$i]['QuotationPaymentTermId'] =@$row3['QuotationPaymentTermId'];
          $data['Data'][$i]['QuotationId'] =@$row3['QuotationId'];
          $data['Data'][$i]['Detail'] =@$row3['Detail'];
          $data['Data'][$i]['Status'] =@$row3['Status'];
          $data['Data'][$i]['Amount'] = floatval(@$row3['Amount']);
          $data['Data'][$i]['CreateBy'] =@$row3['CreateBy'];


          $i++;
        }
      }
    }else{
      $data['Data'] = array();
    }


    $data['sql'] = $view;
    $data['view2'] = $view2;
    $data['view3'] = $view3;
    $data['request'] = $request;
    $data['result_num2'] = $result_num2;

    return $data;
  }


  public function LoadLanguagebilling()
  {
    $data['Data'] = array();
    $order = '';
    $count = "";
    $name_table = "t_billing_currency_mas";
    $search = "StatusActive = 1 AND Deleted = 0";


    $sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search,'order'=>$order);
    $view = $this->view($sql);
    $result = $this->q_re($view);
    $result_num = $this->q_nr($result);

    $i=0;
    if(@$result_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'];
         $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++;
      }
    }
    $data['sql'] = $view;
    return $data;
  }

}


 ?>