<?php

/**
 *
 */
class SearchClass extends ViewData
{
  public function CasesearchN($request)
  {
      $request = $request['REQUEST'];
      $count = "";
      $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
            , (
                  SELECT  cast(ClassesCode as char() )
                  FROM t_case_classes_map
                  WHERE CaseId = c.CaseId AND Deleted = 0
                  ORDER BY ClassesCode
              ) as ClassList
            ,  (SELECT cast(ClassesId as char() )
              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(CONCAT(u1.Name, ' ',  u1.MiddleName), ' ', u1.Surname) as CreateBy
            , CONCAT(CONCAT(u2.Name, ' ',  u2.MiddleName), ' ', u2.Surname) as LastUpdateBy
            , c.CreateDateTime as CreateDateTime
            , c.LastUpdateDateTime as LastUpdateDateTime
            , pcmas.ProfitCentreName as ProfitCentreName
            , ttmas.TaxTreatmentName as TaxTreatmentName
            , onm1.OfficialNo as AppNo
            , onm2.OfficialNo as RegisNo
            , FORMAT_DATE(e37.OccurredDate, '%Y-%m-%d') as AppFilingDate
            , FORMAT_DATE(e29.OccurredDate, '%Y-%m-%d') as RegisDate
            , FORMAT_DATE(e51.CompleteDate, '%Y-%m-%d') as NextRenewalDate
            , FORMAT_DATE(e69.CompleteDate, '%Y-%m-%d') as RenewalDeadlineDate
            ,
                (
                  SELECT '\n- ' . emasl.EventsName . FORMAT_DATE(m.CompleteDate, ' - %d/%m/%Y'), FORMAT_DATE(m.OccurredDate, ' - %d/%m/%Y '))
                  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
                ) 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
      ";
      if(@$request['instructorRefText']!=''){
        $name_table = $name_table . "
          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
        ";
      }else {
        $name_table = $name_table . "
          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
        ";
      }
      if(@$request['instructorRef']!= 6){
        $name_table = $name_table . "
          AND nm1.NamesName { nm1.NamesCode }
        ";
      }else {
        $name_table = $name_table . "
          AND nm1.PostalAddress
        ";
      }
      function isRefuse($ts)
      {
        $ts = parseInt($ts);
        return ( ($ts == 3) || ($ts == 5) ? 'not' : '');

      }
      $name_table = $name_table .isRefuse($request['instructorRef']) . " LIKE '%".@$request['instructorRefText']."%' ";
      $search = "";

      $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['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'] = @$view3;


      return $data;
  }

  public function Casesearch($request)
  {
    //$name_table = "t_case_management_trn tcmt";
    $request = $request['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
        LEFT JOIN t_basis_mas tbm ON tbm.BasisId = tcmt.BasisId
        LEFT JOIN t_profit_centre_mas tpcm ON tpcm.ProfitCentreId = tcmt.ProfitCentreId
        LEFT JOIN t_billing_currency_mas tbcm ON tbcm.BillingCurrencyId = tcmt.BillingCurrencyId
        LEFT JOIN t_tax_treatment_mas tttm ON tttm.TaxTreatmentId = tcmt.TaxTreatmentId
        LEFT JOIN t_type_of_mark_mas ttomm ON ttomm.TypeOfMarkId = tcmt.TypeOfMarkId
        LEFT JOIN t_case_images_map tci ON tci.CaseId = tcmt.CaseId AND tci.Deleted = 0

        LEFT JOIN t_system_users ts ON ts.Id = tcmt.CreateBy
        LEFT JOIN t_system_users ts2 ON ts2.Id = tcmt.LastUpdateBy
        ";
    //INNER JOIN t_case_classes_map tccm ON tccm.CaseId = tcmt.CaseId

    $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,
      tci.FilePath, tci.FileName, tci.FileNameOriginal,
      CONCAT(ts.Name, ' ', ts.Surname) AS CreateBy,
      CONCAT(ts2.Name, ' ', ts2.Surname) AS LastUpdateBy
    ";
    /*
    tccm.CaseClassId,
    tccm.ClassesId,
    tccm.ClassesCode,
    tccm.ClassesTextTH,
    tccm.ClassesTextEN,
    */

    $search = "tcmt.Deleted=0";

    /// Reference ///
    if(@$request['caseReferenceText']!=''){
      if(@$request['caseReference']==0){
        $search = $search." AND `tcmt`.`CaseReference` LIKE '".@$request['caseReferenceText']."%'";
      }
      if(@$request['caseReference']==1){
        $search = $search." AND RIGHT(`tcmt`.`CaseReference`, 4) LIKE '%".@$request['caseReferenceText']."'";
      }
      if(@$request['caseReference']==2){
        $search = $search." AND `tcmt`.`CaseReference` LIKE '%".@$request['caseReferenceText']."%'";
      }
      if(@$request['caseReference']==3){
        $search = $search." AND `tcmt`.`CaseReference` NOT LIKE '%".@$request['caseReferenceText']."%'";
      }
      if(@$request['caseReference']==4){
        $search = $search." AND `tcmt`.`CaseReference` = '".@$request['caseReferenceText']."'";
      }
      if(@$request['caseReference']==5){
        $search = $search." AND `tcmt`.`CaseReference` <> '".@$request['caseReferenceText']."'";
      }
    }

    if(@$request['familyText']!=''){
      if(@$request['family']==0){
        $search = $search." AND `tcmt`.`Family` LIKE '".@$request['familyText']."%'";
      }
      if(@$request['family']==1){
        $search = $search." AND RIGHT(`tcmt`.`Family`, 4) LIKE '%".@$request['familyText']."'";
      }
      if(@$request['family']==2){
        $search = $search." AND `tcmt`.`Family` LIKE '%".@$request['familyText']."%'";
      }
      if(@$request['family']==3){
        $search = $search." AND `tcmt`.`Family` NOT LIKE '%".@$request['familyText']."%'";
      }
      if(@$request['family']==4){
        $search = $search." AND `tcmt`.`Family` = '".@$request['familyText']."'";
      }
      if(@$request['family']==5){
        $search = $search." AND `tcmt`.`Family` <> '".@$request['familyText']."'";
      }
    }

    if(@$request['instructorRefText']!=''){
      if(@$request['instructorRef']==0){
        //$search = $search." AND `tcmt`.`CaseReference` = '".@$request['instructorRefText']."'";
      }
      if(@$request['instructorRef']==1){
        //$search = $search." AND RIGHT(`tcmt`.`CaseReference`, 4) LIKE '%".@$request['instructorRefText']."'";
      }
      if(@$request['instructorRef']==2){
        //$search = $search." AND `tcmt`.`CaseReference` LIKE '%".@$request['instructorRefText']."%'";
      }
      if(@$request['instructorRef']==3){
        //$search = $search." AND `tcmt`.`CaseReference` NOT LIKE '%".@$request['instructorRefText']."%'";
      }
      if(@$request['instructorRef']==4){
        //$search = $search." AND `tcmt`.`CaseReference` NOT LIKE '%".@$request['instructorRefText']."%'";
      }
      if(@$request['instructorRef']==5){
        //$search = $search." AND `tcmt`.`CaseReference` NOT LIKE '%".@$request['instructorRefText']."%'";
      }
    }

    /// Case Details ////
    if(@$request['dataCaseoffice']!=''){
      $search = $search." AND `tcmt`.`CaseOfficeId` = '".@$request['dataCaseoffice']."'";
    }
    if(@$request['dataCasetype']!=''){
      $search = $search." AND `tcmt`.`CaseTypeId` = '".@$request['dataCasetype']."'";
    }
    if(@$request['dataCountry']!=''){
      $search = $search." AND `tcmt`.`CountryId` = '".@$request['dataCountry']."'";
    }
    if(@$request['dataBasis']!=''){
      $search = $search." AND `tcmt`.`dataBasis` = '".@$request['dataBasis']."'";
    }

    if(@$request['caseClassesCode']!=''){
      if(@$request['caseCaseStatus']==0){
        $search = $search." AND `tccm`.`ClassesId` LIKE '".@$request['caseClassesCode']."%'";
      }
      if(@$request['caseCaseStatus']==1){
        $search = $search." OR `tccm`.`ClassesId` = '".@$request['caseClassesCode']."'";
      }
      if(@$request['caseCaseStatus']==2){
        $search = $search." AND `tccm`.`ClassesId` LIKE '%".@$request['caseClassesCode']."%'";
      }
      if(@$request['caseCaseStatus']==3){
        $search = $search." AND `tccm`.`ClassesId` <> '".@$request['caseClassesCode']."'";
      }
    }

    if(@$request['caseClassesCode2']!=''){
      if(@$request['caseCaseStatus2']==0){
        $search = $search." AND `tccm`.`ClassesId` LIKE '".@$request['caseClassesCode']."%'";
      }
      if(@$request['caseCaseStatus2']==1){
        $search = $search." OR `tccm`.`ClassesId` = '".@$request['caseClassesCode']."'";
      }
      if(@$request['caseCaseStatus2']==2){
        $search = $search." AND `tccm`.`ClassesId` LIKE '%".@$request['caseClassesCode']."%'";
      }
      if(@$request['caseCaseStatus2']==3){
        $search = $search." AND `tccm`.`ClassesId` <> '".@$request['caseClassesCode']."'";
      }
    }

    if(@$request['appNoText']!=''){
      $table = $table . "INNER JOIN t_case_official_number_map tcon ON tcon.CaseId = tcmt.CaseId AND tcon.OfficialNumberTypeId = 1
      INNER JOIN t_case_official_number_map tcon2 ON tcon2.CaseId = tcmt.CaseId AND tcon2.OfficialNumberTypeId = 2";

      $count = $count . ",tcon.OfficialNo AS AppNo,  tcon2.OfficialNo AS RegisNo,  tcon.DateInForce AS AppFilingDate,  tcon2.DateInForce AS RegisDate";
      if(@$request['appNostatus']==0){
        $search = $search." AND `tcon`.`OfficialNo` LIKE '".@$request['appNoText']."%'";
      }
      if(@$request['appNostatus']==1){
        $search = $search." AND RIGHT(`tcon`.`OfficialNo`, 4) LIKE '%".@$request['appNoText']."'";
      }
      if(@$request['appNostatus']==2){
        $search = $search." AND `tcon`.`OfficialNo` LIKE '%".@$request['appNoText']."%'";
      }
      if(@$request['appNostatus']==3){
        $search = $search." AND `tcon`.`OfficialNo` NOT LIKE '%".@$request['appNoText']."%'";
      }
      if(@$request['appNostatus']==4){
        $search = $search." AND `tcon`.`OfficialNo` = '".@$request['appNoText']."'";
      }
      if(@$request['appNostatus']==5){
        $search = $search." AND `tcon`.`OfficialNo` <> '".@$request['appNoText']."'";
      }
    }


    /// Text ///
    if(@$request['MarkText']!=''){

      if(@$request['Markstatus']==0){
        $search = $search." AND `tcmt`.`Title` LIKE '".@$request['MarkText']."%'";
      }
      if(@$request['Markstatus']==1){
        $search = $search." AND RIGHT(`tcmt`.`Title`, 4) LIKE '%".@$request['MarkText']."'";
      }
      if(@$request['Markstatus']==2){
        $search = $search." AND `tcmt`.`Title` LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['Markstatus']==3){
        $search = $search." AND `tcmt`.`Title` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['Markstatus']==4){
        $search = $search." AND `tcmt`.`Title` = '".@$request['MarkText']."'";
      }
      if(@$request['Markstatus']==5){
        $search = $search." AND `tcmt`.`Title` <> '".@$request['MarkText']."'";
      }
    }


    if(@$request['dataTypeofmark']!=''){
      $search = $search." AND `tcmt`.`TypeOfMarkId` = '".@$request['dataTypeofmark']."'";
    }

    /// Names ///
    if(@$request['instructorText']!=''){

      if(@$request['instructorstatus']==0){
        //$search = $search." AND `CaseReference` = '".@$request['MarkText']."'";
      }
      if(@$request['instructorstatus']==1){
        //$search = $search." AND RIGHT(`CaseReference`, 4) LIKE '%".@$request['MarkText']."'";
      }
      if(@$request['instructorstatus']==2){
        //$search = $search." AND `CaseReference` LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['instructorstatus']==3){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['instructorstatus']==4){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['instructorstatus']==5){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['instructorstatus']==6){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
    }

    if(@$request['debtorText']!=''){

      if(@$request['debtorstatus']==0){
        //$search = $search." AND `CaseReference` = '".@$request['MarkText']."'";
      }
      if(@$request['debtorstatus']==1){
        //$search = $search." AND RIGHT(`CaseReference`, 4) LIKE '%".@$request['MarkText']."'";
      }
      if(@$request['debtorstatus']==2){
        //$search = $search." AND `CaseReference` LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['debtorstatus']==3){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['debtorstatus']==4){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['debtorstatus']==5){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }

    }

    if(@$request['ownerText']!=''){

      if(@$request['ownerstatus']==0){
        //$search = $search." AND `CaseReference` = '".@$request['MarkText']."'";
      }
      if(@$request['ownerstatus']==1){
        //$search = $search." AND RIGHT(`CaseReference`, 4) LIKE '%".@$request['MarkText']."'";
      }
      if(@$request['ownerstatus']==2){
        $search = $search." tnm.NamesName LIKE '%".@$request['ownerText']."%'";
      }
      if(@$request['ownerstatus']==3){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['ownerstatus']==4){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['ownerstatus']==5){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['ownerstatus']==6){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }

    }

    if(@$request['agentText']!=''){

      if(@$request['agentstatus']==0){
        //$search = $search." AND `CaseReference` = '".@$request['MarkText']."'";
      }
      if(@$request['agentstatus']==1){
        //$search = $search." AND RIGHT(`CaseReference`, 4) LIKE '%".@$request['MarkText']."'";
      }
      if(@$request['agentstatus']==2){
        //$search = $search." AND `CaseReference` LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['agentstatus']==3){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['agentstatus']==4){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['agentstatus']==5){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }

    }

    if(@$request['refferalText']!=''){

      if(@$request['refferalstatus']==0){
        //$search = $search." AND `CaseReference` = '".@$request['MarkText']."'";
      }
      if(@$request['refferalstatus']==1){
        //$search = $search." AND RIGHT(`CaseReference`, 4) LIKE '%".@$request['MarkText']."'";
      }
      if(@$request['refferalstatus']==2){
        //$search = $search." AND `CaseReference` LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['refferalstatus']==3){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['refferalstatus']==4){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['refferalstatus']==5){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }

    }

    if(@$request['partnerText']!=''){

      if(@$request['partnerstatus']==0){
        //$search = $search." AND `CaseReference` = '".@$request['MarkText']."'";
      }
      if(@$request['partnerstatus']==1){
        //$search = $search." AND RIGHT(`CaseReference`, 4) LIKE '%".@$request['MarkText']."'";
      }
      if(@$request['partnerstatus']==2){
        //$search = $search." AND `CaseReference` LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['partnerstatus']==3){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['partnerstatus']==4){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['partnerstatus']==5){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }

    }

    if(@$request['attorneyText']!=''){

      if(@$request['attorneystatus']==0){
        //$search = $search." AND `CaseReference` = '".@$request['MarkText']."'";
      }
      if(@$request['attorneystatus']==1){
        //$search = $search." AND RIGHT(`CaseReference`, 4) LIKE '%".@$request['MarkText']."'";
      }
      if(@$request['attorneystatus']==2){
        //$search = $search." AND `CaseReference` LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['attorneystatus']==3){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['attorneystatus']==4){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['attorneystatus']==5){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }

    }

    if(@$request['executiveText']!=''){

      if(@$request['executivestatus']==0){
        //$search = $search." AND `CaseReference` = '".@$request['MarkText']."'";
      }
      if(@$request['executivestatus']==1){
        //$search = $search." AND RIGHT(`CaseReference`, 4) LIKE '%".@$request['MarkText']."'";
      }
      if(@$request['executivestatus']==2){
        //$search = $search." AND `CaseReference` LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['executivestatus']==3){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['executivestatus']==4){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }
      if(@$request['executivestatus']==5){
        //$search = $search." AND `CaseReference` NOT LIKE '%".@$request['MarkText']."%'";
      }

    }


    /// Datas ///


    /// Status ///
    if(@$request['dataCasestatus']!=""){
      for ($i=0; $i < count(@$request['dataCasestatus']); $i++) {
        $search = $search." AND `tcsm`.`CaseStatusId` = '".@$request['dataCasestatus'][$i]."'";
      }
    }

    $order = '';

    if($request['OrderDirection']!=''){
      $order = " Order By tcmt.".$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;
      $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]['CaseReference'] =$row['CaseReference'];

        $data['Data'][$i]['Family'] =$row['Family'];
        $data['Data'][$i]['CustomerId'] =$row['CustomerId'];
        $data['Data'][$i]['Title'] =$row['Title'];
        $data['Data'][$i]['CaseTypeId'] =$row['CaseTypeId'];
        $data['Data'][$i]['CountryId'] =$row['CountryId'];
        $data['Data'][$i]['CaseOfficeId'] =$row['CaseOfficeId'];
        $data['Data'][$i]['CaseOfficeId'] =$row['CaseOfficeId'];
        $data['Data'][$i]['BasisId'] =$row['BasisId'];
        $data['Data'][$i]['BasisId'] =$row['BasisId'];
        $data['Data'][$i]['CaseStatusId'] =$row['CaseStatusId'];
        $data['Data'][$i]['ProfitCentreId'] =$row['ProfitCentreId'];
        $data['Data'][$i]['BillingCurrencyId'] =$row['BillingCurrencyId'];
        $data['Data'][$i]['TaxTreatmentId'] =$row['TaxTreatmentId'];
        $data['Data'][$i]['TypeOfMarkId'] =$row['TypeOfMarkId'];
        $data['Data'][$i]['PurchaseOrderNo'] =$row['PurchaseOrderNo'];
        $data['Data'][$i]['NoOfClaims'] =$row['NoOfClaims'];
        $data['Data'][$i]['KeepOnTopNote'] =$row['KeepOnTopNote'];
        $data['Data'][$i]['NoInSeries'] =$row['NoInSeries'];
        $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]['CaseStatusName'] =$row['CaseStatusName'];


        $data['Data'][$i]['CaseOfficeId'] =$row['CaseOfficeId'];
        $data['Data'][$i]['CaseOfficeCode'] =$row['CaseOfficeCode'];
        $data['Data'][$i]['CaseOffice'] =$row['CaseOfficeName'];
        $data['Data'][$i]['CaseTypeCode'] =$row['CaseTypeCode'];
        $data['Data'][$i]['CaseTypeName'] =$row['CaseTypeName'];
        $data['Data'][$i]['CountryName'] =$row['CountryName'];
        $data['Data'][$i]['CountryShortName'] =$row['CountryShortName'];

        $data['Data'][$i]['BasisName'] =$row['BasisName'];
        $data['Data'][$i]['ProfitCentreName'] =$row['ProfitCentreName'];
        $data['Data'][$i]['ProfitCentreCode'] =$row['ProfitCentreCode'];
        $data['Data'][$i]['BillingCurrencyCode'] =$row['BillingCurrencyCode'];
        $data['Data'][$i]['BillingCurrencyName'] =$row['BillingCurrencyName'];
        $data['Data'][$i]['TaxTreatmentName'] =$row['TaxTreatmentName'];
        $data['Data'][$i]['TypeOfMarkName'] =$row['TypeOfMarkName'];

        $data['Data'][$i]['FilePath'] = $row['FilePath'];
        $data['Data'][$i]['FileName'] = $row['FileName'];
        $data['Data'][$i]['Image'] ='<img src="../Uploads'.$row['FilePath'].'" width="100px" height="auto" alt="IMG">';
        $data['Data'][$i]['FileNameOriginal'] =$row['FileNameOriginal'];

        $name_table_c = "t_case_classes_map";
        $list_c = array('table'=>$name_table_c ,'where'=>"CaseId = '".$row['CaseId']."' AND StatusActive = '1'");
        $view_c = $this->view($list_c);
        $result_c = $this->q_re($view_c);
        $respons_count_num_c = mysqli_num_rows($result_c);
        $i2=0;
        if(@$respons_count_num_c > 0){
          while ($row_c = $this->q_ro($result_c)) {
            $data['Data'][$i]["ClassList"][$i2]['CaseClassId'] = $row_c['CaseClassId'];
            $data['Data'][$i]["ClassList"][$i2]['ClassesId'] = $row_c['ClassesId'];
            $data['Data'][$i]["ClassList"][$i2]['ClassesCode'] = $row_c['ClassesCode'];
            $data['Data'][$i]["ClassList"][$i2]['ClassesTextTH'] = $row_c['ClassesTextTH'];
            $data['Data'][$i]["ClassList"][$i2]['ClassesTextEN'] = $row_c['ClassesTextEN'];
            /*
            $data['Data'][$i]["ClassList"] = $row_c;
            */
            $i2++;
          }
        }else{
          $data['Data'][$i]["ClassList"] = [];
          $data['Data'][$i]["ClassList"] =[];
          $data['Data'][$i]["ClassList"] =[];
          $data['Data'][$i]["ClassList"] =[];
          $data['Data'][$i]["ClassList"] =[];
        }



        $data['Data'][$i]['CreateBy'] =$row['CreateBy'];
        $data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];


        $name_table3 = "t_case_names_map c INNER JOIN t_names_mas n ON c.NamesId = n.NamesId";
        $sql3 = array('table'=>$name_table3 ,'where'=>"c.Deleted='0' AND c.CaseId = '".$row['CaseId']."' AND c.NamesTypeId = '3'");
        $view3 = $this->view($sql3);
        $row3 = $this->q($view3);

        $data['Data'][$i]['Owner'] =$row3['NamesName'];
        $data['Data'][$i]['OwnerCode'] =$row3['NamesCode'];
        $data['Data'][$i]['OwnerAddress'] =$row3['PostalAddress'];
        $data['Data'][$i]['OwnerTaxID'] =$row3['TaxID'];
        $data['Data'][$i]['OwnerEmail'] =$row3['ElectronicMail'];
        $data['Data'][$i]['OwnerContact'] =$row3['Telephone'];


        $data['Data'][$i]['CaseNameId'] =$row['CaseNameId'];
        $name_table1 = "t_case_names_map c INNER JOIN t_names_mas n ON c.NamesId = n.NamesId";
        $sql1 = array('table'=>$name_table1 ,'where'=>"c.Deleted='0' AND c.CaseId = '".$row['CaseId']."' AND c.NamesTypeId = '1'");
        $view1 = $this->view($sql1);
        $row1 = $this->q($view1);
        $data['Data'][$i]['InstructorRef'] =$row1['Reference'];
        $data['Data'][$i]['Instructor'] =$row1['NamesName'];
        $data['Data'][$i]['InstructorCode'] =$row1['NamesCode'];
        $data['Data'][$i]['InstructorAddress'] =$row1['PostalAddress'];
        $data['Data'][$i]['InstructorTaxId'] =$row1['TaxID'];

        $name_table21 = "t_case_names_map c INNER JOIN t_names_mas n ON c.NamesId = n.NamesId";
        $sql21 = array('table'=>$name_table21 ,'where'=>"c.Deleted='0' AND c.CaseId = '".$row['CaseId']."' AND c.NamesTypeId = '2'");
        $view21 = $this->view($sql21);
        $row21 = $this->q($view21);
        $data['Data'][$i]['Debtor'] =$row21['NamesName'];
        $data['Data'][$i]['DebtorCode'] =$row21['NamesCode'];
        $data['Data'][$i]['DebtorAddress'] =$row21['PostalAddress'];
        $data['Data'][$i]['DebtorTaxID'] =$row21['TaxID'];

        $name_table4 = "t_case_names_map c INNER JOIN t_names_mas n ON c.NamesId = n.NamesId";
        $sql4 = array('table'=>$name_table4 ,'where'=>"c.Deleted='0' AND c.CaseId = '".$row['CaseId']."' AND c.NamesTypeId = '4'");
        $view4 = $this->view($sql4);
        $row4 = $this->q($view4);
        $data['Data'][$i]['Agent'] =$row4['NamesName'];
        $data['Data'][$i]['AgentCode'] =$row4['NamesCode'];
        $data['Data'][$i]['AgentAddress'] =$row4['PostalAddress'];
        $data['Data'][$i]['AgentTaxID'] =$row4['TaxID'];
        $data['Data'][$i]['AgentEmail'] =$row4['ElectronicMail'];
        $data['Data'][$i]['AgentContact'] =$row4['Telephone'];

        
        $name_table8 = "t_case_names_map c INNER JOIN t_names_mas n ON c.NamesId = n.NamesId";
        $sql8 = array('table'=>$name_table8 ,'where'=>"c.Deleted='0' AND c.CaseId = '".$row['CaseId']."' AND c.NamesTypeId = '8'");
        $view8 = $this->view($sql8);
        $row8 = $this->q($view8); 
        $data['Data'][$i]['Refferal'] =$row8['NamesName'];
        $data['Data'][$i]['RefferalCode'] =$row8['NamesCode'];
        $data['Data'][$i]['RefferalAddress'] =$row8['PostalAddress'];
        $data['Data'][$i]['RefferalTaxID'] =$row8['TaxID'];
        $data['Data'][$i]['Refrow'] =$row8;
        $data['Data'][$i]['Refsql'] =$view8;


        $name_table5 = "t_case_names_map c INNER JOIN t_names_mas n ON c.NamesId = n.NamesId";
        $sql5 = array('table'=>$name_table5 ,'where'=>"c.Deleted='0' AND c.CaseId = '".$row['CaseId']."' AND c.NamesTypeId = '5'");
        $view5 = $this->view($sql5);
        $row5 = $this->q($view5); 
        $data['Data'][$i]['Partner'] =$row5['NamesName'];
        $data['Data'][$i]['PartnerCode'] =$row5['NamesCode'];
        $data['Data'][$i]['PartnerAddress'] =$row5['PostalAddress'];
        $data['Data'][$i]['PartnerTaxID'] =$row5['TaxID'];

        $name_table6 = "t_case_names_map c INNER JOIN t_names_mas n ON c.NamesId = n.NamesId";
        $sql6 = array('table'=>$name_table6 ,'where'=>"c.Deleted='0' AND c.CaseId = '".$row['CaseId']."' AND c.NamesTypeId = '6'");
        $view6 = $this->view($sql6);
        $row6 = $this->q($view6); 
        $data['Data'][$i]['Attorney'] =$row6['NamesName'];
        $data['Data'][$i]['AttorneyCode'] =$row6['NamesCode'];
        $data['Data'][$i]['AttorneyAddress'] =$row6['PostalAddress'];
        $data['Data'][$i]['AttorneyTaxID'] =$row6['TaxID'];

        $name_table7 = "t_case_names_map c INNER JOIN t_names_mas n ON c.NamesId = n.NamesId";
        $sql7 = array('table'=>$name_table7 ,'where'=>"c.Deleted='0' AND c.CaseId = '".$row['CaseId']."' AND c.NamesTypeId = '7'");
        $view7 = $this->view($sql7);
        $row7 = $this->q($view7); 
        $data['Data'][$i]['Executive'] =$row7['NamesName'];
        $data['Data'][$i]['ExecutiveCode'] =$row7['NamesCode'];
        $data['Data'][$i]['ExecutiveAddress'] =$row7['PostalAddress'];
        $data['Data'][$i]['ExecutiveTaxID'] =$row7['TaxID'];

        $sql2 = array('table'=>"t_case_official_number_map",$count =>'OfficialNo, DATE_FORMAT(DateInForce, "%Y-%m-%d") ','where'=>"CaseId = '".$row['CaseId']."' AND OfficialNumberTypeId = '1'" );
        $view2 = $this->view($sql2);
        $row2 = $this->q($view2);

        $data['Data'][$i]['AppNo'] =$row2['OfficialNo'];
        $data['Data'][$i]['AppFilingDate'] =($row2['DateInForce'] == "0000-00-00 00:00:00")?"":$row2['DateInForce'];

        $sql4 = array('table'=>"t_case_official_number_map",$count =>'OfficialNo, DATE_FORMAT(DateInForce, "%Y-%m-%d") ','where'=>"CaseId = '".$row['CaseId']."' AND OfficialNumberTypeId = '2'" );
        $view4 = $this->view($sql4);
        $row4 = $this->q($view4);
        $data['Data'][$i]['RegisNo'] =$row4['OfficialNo'];
        $data['Data'][$i]['RegisDate'] =($row4['DateInForce'] == "0000-00-00 00:00:00")?"":$row4['DateInForce'];

        $data['Data'][$i]['RenewalDeadlineDate'] =$row['RenewalDeadlineDate'];
        $data['Data'][$i]['NextRenewalDate'] =$row['NextRenewalDate'];
        //$data['Data'][$i]['ClassList'] =$row['ClassList'];
        $data['Data'][$i]['EventList'] =$row['EventList'];
        




        $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;
    $data['view_c'] = @$view_c;


    return $data;

  }

  public function ReportFiling($CaseId)
  {
    $data=array();

    $search = "Deleted=0 AND CaseId = '".$CaseId."'";
    $sql = array('table'=>'t_case_official_number_map','where'=>$search, 'order'=>' ORDER BY CaseOfficialNumberId ASC');

    $view = $this->view($sql);
    $result = $this->q_re($view);
    $i=0;
    while ($row = $this->q_ro($result)) {
        $data['CaseOfficial'][$i]['CaseOfficialNumberId'] =$row['CaseOfficialNumberId'];
        $data['CaseOfficial'][$i]['OfficialNumberTypeId'] =$row['OfficialNumberTypeId'];
        $data['CaseOfficial'][$i]['CaseOfficialNumberType'] =$row['CaseOfficialNumberType'];
        $data['CaseOfficial'][$i]['OfficialNo'] =$row['OfficialNo'];
        $data['CaseOfficial'][$i]['SeqNo'] =$row['SeqNo'];
        $data['CaseOfficial'][$i]['DateInForce'] =$row['DateInForce'];
        $i++;
    }

    $sql2 = array('table'=>'t_case_classes_map','where'=>$search, 'order'=>' ORDER BY CaseClassId ASC');
    $view2 = $this->view($sql2);
    $result2 = $this->q_re($view2);
    $i2=0;
    while ($row2 = $this->q_ro($result2)) {
        $data['classes'][$i2]['CaseClassId'] =$row2['CaseClassId'];
        $data['classes'][$i2]['ClassesId'] =$row2['ClassesId'];
        $data['classes'][$i2]['ClassesCode'] =$row2['ClassesCode'];
        $data['classes'][$i2]['ClassesTextTH'] =$row2['ClassesTextTH'];
        $data['classes'][$i2]['ClassesTextEN'] =$row2['ClassesTextEN'];
        $i2++;
    }

    $name_table = "t_case_names_map c
    INNER JOIN t_names_type_mas n ON c.NamesTypeId = n.NamesTypeId";
    $sql3 = array('table'=>$name_table ,'where'=>"c.Deleted=0 AND c.CaseId = '".$CaseId."'", 'order'=>' ORDER BY c.CaseNameId ASC');
    $view3 = $this->view($sql3);
    $result3 = $this->q_re($view3);
    $i3=0;
    while ($row3 = $this->q_ro($result3)) {
        $data['names'][$i3]['NamesTypeId'] =$row3['NamesTypeId'];
        $data['names'][$i3]['NamesId'] =$row3['NamesId'];
        $data['names'][$i3]['SeqNo'] =$row3['SeqNo'];
        $data['names'][$i3]['NamesTypeName'] =$row3['NamesTypeName'];

        $sql4 = array('table'=>"t_names_mas",'where'=>"NamesId = '".$row3['NamesId']."'");
        $view4 = $this->view($sql4);
        $row4 = $this->q($view4);
        $data['names'][$i3]['NamesName'] = (isset($row4['NamesName']))? $row4['NamesName'] : "";
        $data['names'][$i3]['NamesCode'] = (isset($row4['NamesCode']))? $row4['NamesCode'] : "";
        $data['names'][$i3]['Telephone'] = (isset($row4['Telephone']))? $row4['Telephone'] : "";
        $data['names'][$i3]['PostalAddress'] = (isset($row4['PostalAddress']))? $row4['PostalAddress'] : "";
        $data['names'][$i3]['Fax'] = (isset($row4['Fax']))? $row4['Fax'] : "";
        $data['names'][$i3]['ElectronicMail'] = (isset($row4['ElectronicMail']))? $row4['ElectronicMail'] : "";
        $data['names'][$i3]['TaxID'] = (isset($row4['TaxID']))? $row4['TaxID'] : "";
        $data['names'][$i3]['Remark'] = (isset($row4['Remark']))? $row4['Remark'] : "";
        $data['names'][$i3]['Initial'] = (isset($row4['Initial']))? $row4['Initial'] : "";
        $i3++;
    }

    $data['sql'] = @$view3;

    return $data;
  }



}


 ?>