<?php
class Mouexpiredv2 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;
        }
  }
  public function extendmou()
  {
     try {
        //# cek token valid
        if (! $this->isLogin) {
           $this->sys_error("Invalid Token");
           exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $userid = $this->sys_user["M_UserID"];
        $id = $prm['id'];
        $startdate = date('Y-m-d',strtotime($prm['startdate']));
        $enddate = date('Y-m-d',strtotime($prm['enddate']));
        $sql = "update m_mou SET 
        M_MouStartDate = '{$startdate}',
           M_MouEndDate = '{$enddate}',
           M_MouUserID = '{$userid}',
           M_MouLastUpdated = now()
           WHERE
           M_MouID = ?";

        $query = $this->db_regional->query($sql,
           array(
              $prm['id']
           )
        );
        $result =  $this->upload_mou($id);
        if ($result[0] ) {
           $result = array ("total" => 1, "records" => array("xid" => 0));
           $this->sys_ok($result);
        } else {
           $this->sys_error($result);
        }

     } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
     }
  }
  function upload_mou($mouID) {
      //upload aggrement , ss_price_mou 
      $sql = "select M_MouID,M_MouStartDate,M_MouEndDate from m_mou where M_MouID = ?";
      $qry = $this->db_regional->query($sql, array($mouID));
      if (! $qry) {
         return array(false, print_r($this->db_regional->error(),true));
      } 
      $rows = $qry->result_array();
      if (count($rows) == 0) {
         return array(false, "No MOU : " . $this->db_regional->last_query());
      }
      $mou = $rows[0]; 

      $sql = "select * from m_company where M_CompanyID = ?";
      $qry = $this->db_regional->query($sql, array($mou["M_MouM_CompanyID"]));
      if (! $qry) {
         return array(false, print_r($this->db_regional->error(),true));
      } 
      $rows = $qry->result_array();
      if (count($rows) == 0 ) {
         return array(false, "No Company");
      }
      $company = $rows[0]; 



      $param = array (
         "mou" => $mou
      );
      $param_md5 = md5(json_encode($param));
      $j_param = json_encode(array("param" => $param, "md5" => $param_md5 ));
      $sql = "select * from m_branch
      JOIN s_regional ON M_BranchS_RegionalID = S_RegionalID AND S_RegionalIsDefault = 'Y'
      where M_BranchIsActive = 'Y'";
      $qry = $this->db_regional->query($sql);
      if (! $qry) {
         return array(false, print_r($this->db_regional->error(),true));
      } 
      $rows = $qry->result_array();
      if (count($rows) == 0 ) {
         return array(false, "No Ss Price Mou");
      }

      $sql = "insert into tx_mou(TxMouM_BranchIPAddress, TxMouM_BranchCode,TxMouM_MouID, TxMouJson,TxMouM_UserID,TxMouT_TestID )
         values(?,?,?,?,?,'-1')";
      //$sql_del = "delete from tx_mou where TxMouM_MouID=? and TxMouM_BranchCode=?";
      $flag_error = false;
      $err_msg = array();
      $userID = $this->sys_user["M_UserID"];
      foreach($rows as $r ) {
         $branchCode = $r["M_BranchCode"];
         $ipAddress = $r["M_BranchIPAddress"];
         $qry =  $this->db_regional->query($sql_del, array($mouID,$branchCode)); 
         /* if (! $qry) {
            return array(false, print_r($this->db_regional->error(),true));
         } 
         */
         $qry =  $this->db_regional->query($sql, array($ipAddress, $branchCode,$mouID,$j_param,$userID)); 
         if (! $qry) {
            return array(false, print_r($this->db_regional->error(),true));
         } 
         try {
            $txMouID = $this->db_regional->insert_id();
            $url = "http://$ipAddress/one-api/tools/price/extendmou/verify";
            $post_rst = $this->post($url,$j_param);
            $j_rst = json_decode($post_rst,true);
            if ($j_rst["status"] != "OK" ) {
               $err_msg[] = $post_rst;
               $flag_error = true; 
               $this->db_regional->query("update tx_mou set TxMouRetry = 1 where TxMouID = ? ", array($txMouID));
            } else {
               $this->db_regional->query("update tx_mou set TxMouStatus = 'Y' , TxMouRetry = 1 where TxMouID = ? ", array($txMouID));
            }
         } catch(Exception $e) {
            $err_msg[] = $e->getMessage();
            $flag_error = true;
         }
      }
      if ( $flag_error) {
         return array(false, join(",",$err_msg));
      }
      return array(true,"OK");
   }

   function post($url,$data) {
      $ch = curl_init($url);                                                                      
      curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");                                                                     
      curl_setopt($ch, CURLOPT_POSTFIELDS, $data);                                                                  
      curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);                                                                      
      curl_setopt($ch, CURLOPT_TIMEOUT, 12);                                                                      
      curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 2);                                                                      
      curl_setopt($ch, CURLOPT_HTTPHEADER, array(                                                                          
         'Content-Type: application/json',                                                                                
         'Content-Length: ' . strlen($data))                                                                       
      );                                                                                                                   
      $result = curl_exec($ch);
      return $result;
   }
}