<?php
class Mouexpired extends MY_Controller
{
   var $db_regional;
   public function index()
   {
      echo "Mou Expired API";
   }

   public function __construct()
   {
      parent::__construct();
      $this->db_regional = $this->load->database("regional", true);
   }

   public function search()
   {
      $prm = $this->sys_input;
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

            $company = $prm['company'];
            $status = $prm['status'];
      // echo $norm;
      if($status === 'C'){
            $sql_status = " AND M_MouStatus = 'R' AND M_MouEndDate <= now() + INTERVAL + 30 DAY AND M_MouEndDate  > now()";
      }elseif($status === 'Y'){
            $sql_status = " AND M_MouStatus = 'R' AND M_MouEndDate < now()";
      }elseif($status === 'N'){
            $sql_status = " AND M_MouStatus = 'R' AND M_MouEndDate > now()";
      }elseif($status === 'A'){
            $sql_status = " AND M_MouStatus = 'R'";
      }
      $sql_where = "WHERE M_MouIsActive = 'Y'";
	   if(intval($company) > 0){
		   $sql_where = "$sql_where AND M_MouM_CompanyID = {$company}";
         }
         
      $number_limit = 10;
      $number_offset = ($prm['current_page'] - 1) * $number_limit ;

      $sql = "	SELECT count(*) as total
         FROM m_mou

         $sql_where $sql_status
";
      $query = $this->db_regional->query($sql, $sql_param);
      $tot_count = 0;
      $tot_page = 0;
      if ($query) {
         $tot_count = $query->result_array()[0]["total"];
         $tot_page = ceil($tot_count/$number_limit);
      } else {
         $this->sys_error_db("m_mou count", $this->db_regional);
         exit;
      }

      $doctor_field = "
      M_MouID as id,
      M_MouM_CompanyID as companyid,
      M_MouM_CompanyID,
              M_MouName as name,
              M_MouStartDate,
              M_MouEndDate,
              DATE_FORMAT(M_MouStartDate,'%d%m%Y') as startdate,
              DATE_FORMAT(M_MouEndDate,'%d%m%Y') as enddate,
              CONCAT(DATE_FORMAT(M_MouStartDate,'%d-%m-%Y'), ' s/d ', DATE_FORMAT(M_MouEndDate,'%d-%m-%Y')) as periode,
              M_MouIsBill as isbill,
              M_MouRefNumber as refnumber,	 
              M_MouNumber as number,
              CONCAT(M_MouNumber, ' [',M_MouBase, ']') as numberx,
              CONCAT(M_MouName, ' [',M_OmzetTypeName, ']') as namex,
              M_MouIsDefault as isdefault,
              M_MouIsUsingFavourite as isfavorit,
              M_MouJpaIsNetto as isjpanetto,
              M_MouIsMcu as ismcu,
              M_MouIsApproved as isapproved,
              M_MouIsAgingOnHold as isaging,
              M_MouEmailIsDefault as isemail,
              M_MouMinDP as mindp,
              IFNULL(M_MouIsAgingOnHoldNote,'') as agingnote,
              M_MouNote as xnote,
              IFNULL(M_MouEmail,'') as mouemail,
              IF(M_MouIsBill = 'Y','Pakai Billing','Tidak Pakai Billing') as bill,
              M_MouBase as baseid,
              M_MouBase as basename,
              M_MouM_OmzetTypeID,
              M_OmzetTypeID,
              M_OmzetTypeName, 
              M_MouM_MouTypeID,
              M_MouTypeID,
              M_MouTypeName,
              M_AgingTypeID,
              M_AgingTypeName,
              M_MouIsVerified as isverified,	 
              M_MouAllowVerify as isallowverified,
              IF(v.M_UserFullName IS NULL,'',DATE_FORMAT(M_MouVerifyDate,'%d-%m-%Y %h:%i:%s')) as verifydate,	 
              M_MouVerifyUserID,
              IFNULL(v.M_UserFullName,'') as verifyuser,
              IF(v.M_UserFullName IS NULL, '', CONCAT(DATE_FORMAT(M_MouVerifyDate,'%d-%m-%Y %h:%i:%s'),'\r',v.M_UserFullName)) as verify,
              M_MouIsReleased	as isreleased,	 
              IF(r.M_UserFullName IS NULL,'',DATE_FORMAT(M_MouReleaseDate,'%d-%m-%Y %h:%i:%s')) as releasedate,	 
              M_MouReleaseUserID,
              IFNULL(r.M_UserFullName,'') as releaseuser,
              IF(r.M_UserFullName IS NULL, '', CONCAT(DATE_FORMAT(M_MouReleaseDate,'%d-%m-%Y %h:%i:%s'),'\r',r.M_UserFullName)) as released,
              M_MouIsConfirm as isconfirm,
              'xxx' as action,
              CASE  
              WHEN M_MouStatus = 'N' THEN 'Baru'
              WHEN M_MouStatus = 'V' THEN 'Verified'
              WHEN M_MouStatus = 'UV' THEN 'Unverified'
              WHEN M_MouStatus = 'R' THEN 'Released'
              WHEN M_MouStatus = 'C' THEN 'Konfirmasi'
             ELSE 'Unreleased'
             END as aksi,
             'xxx' as statuss,
             M_MouJpa1Name,	
             M_MouJpa1Percent,	
             M_MouJpa2Name,	
             M_MouJpa2Percent,	
             M_MouJpa3Name,	
             M_MouJpa3Percent,	
             M_MouJpa4Name,
             M_MouJpa4Percent
";
      $sql = 	"SELECT $doctor_field, M_CompanyName,
      IF(M_MouEndDate <= now() + INTERVAL + 30 DAY AND M_MouEndDate  > now(),'C','N') as isexpired
         from m_mou 
         JOIN m_company On M_MouM_CompanyID = M_CompanyID
         LEFT JOIN m_omzettype ON M_MouM_OmzetTypeID = M_OmzetTypeID
         LEFT JOIN m_moutype ON M_MouM_MouTypeID = M_MouTypeID
         LEFT JOIN m_agingtype ON M_MouM_AgingTypeID = M_AgingTypeID
         LEFT join m_user v ON M_MouVerifyUserID = v.M_UserID
         LEFT join m_user r ON M_MouReleaseUserID = r.M_UserID
         $sql_where $sql_status
         GROUP BY M_MouID
         ORDER BY M_CompanyName ASC, M_MouEndDate ASC
         limit $number_limit offset $number_offset";

      $query = $this->db_regional->query($sql, $sql_param);
      //echo $this->db_regional->last_query();
      $rows = $query->result_array();
      if($rows){
         foreach($rows as $k => $v){
            //$rows[$k]['verification_px'] = $this->add_verification_test($v['M_PatientID']);

         }
      }


      //$this->_add_address($rows);
      $result = array("total" => $tot_page, "records" => $rows, "sql"=> $this->db_regional->last_query());
      $this->sys_ok($result);
      exit;
   }

   function getsexreg(){
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $rows = [];
      $query ="	SELECT *
         FROM m_sex
         WHERE
         M_SexIsActive = 'Y'
";
      //echo $query;
      $rows['sexes'] = $this->db_regional->query($query)->result_array();
      $query ="	SELECT *
         FROM m_religion
         WHERE
         M_ReligionIsActive = 'Y'
";
      //echo $query;
      $rows['religions'] = $this->db_regional->query($query)->result_array();
      $query ="	
		SELECT 0 as M_CompanyID, 'Semua' as M_CompanyName
		UNION
		SELECT M_CompanyID, M_CompanyName
         FROM m_company
         WHERE
         M_CompanyIsActive = 'Y'

";
      //echo $query;
      $rows['companys'] = $this->db_regional->query($query)->result_array();
	  
	  $query ="	SELECT 'A' as statusid, 'Semua' as statusname
         UNION SELECT 'Y' as statusid, 'Expired' as statusname
         UNION SELECT 'N' as statusid, 'Coming Soon' as statusname
";
      //echo $query;
      $rows['statuss'] = $this->db_regional->query($query)->result_array();


      $result = array(
         "total" => count($rows) ,
         "records" => $rows,
      );
      $this->sys_ok($result);
      exit;
   }
   function searchstaff(){
      if (! $this->isLogin) {
      $this->sys_error("Invalid Token");
      exit;
      }
      $prm = $this->sys_input;
  
      $max_rst = 12;
      $tot_count =0;
  
      
      $name = $prm['name'];
      $hirarkiid = intval($prm['id']) - 1;
  
        // QUERY TOTAL
      $sql = "SELECT count(*) as total
              FROM nat_staff
              left join m_position ON Nat_StaffM_PositionID = M_PositionID
         WHERE
         Nat_StaffIsActive = 'Y' and M_PositionIsMarketing = 'Y'
         AND (Nat_StaffName like '%{$name}%' OR Nat_StaffNIK like '%{$name}%')";
      $query = $this->db_regional->query($sql);
      //echo $query;
      if ($query) { 
          $tot_count = $query->result_array()[0]["total"]; 
      } 
      else {
          $this->sys_error_db("nat_staff count",$this->db_regional);
          exit;
      }
  
      $sql = "
      SELECT * FROM(SELECT *, CONCAT(Nat_StaffName, ' [',Nat_StaffNIK,']') as Nat_StaffNames
      FROM nat_staff
      left join m_position ON Nat_StaffM_PositionID = M_PositionID
      WHERE
      Nat_StaffIsActive = 'Y' and M_PositionIsMarketing = 'Y') a
          WHERE 
          (Nat_StaffName like '%{$name}%' OR Nat_StaffNIK like '%{$name}%')
          AND Nat_StaffIsActive = 'Y'
          ORDER BY Nat_StaffName ASC
        ";
        $query = $this->db_regional->query($sql);
          
        if ($query) { 
           $rows = $query->result_array();
           //echo $this->db_regional->last_query();
           $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
           $this->sys_ok($result);
        } 
        else {
           $this->sys_error_db("nat_staff rows",$this->db_regional);
           exit;
        }
  }

}