<?php
class Mr05 extends CI_Controller
{
   function __construct() {
      parent::__construct();
      $this->db = $this->load->database('onedev', true);
   }
   function piutang($s_date,$e_date) {
      $sql = "delete from ssr_mr05_piutang where SsrMr05PiutangDate >= ? and SsrMr05PiutangDate <= ? ";
      $qry = $this->db->query($sql, array($s_date,$e_date));
      if (! $qry ) {
         echo 'Err : ' . print_r($this->db->error(),true). '\n';
         exit;
      }
      $sql = "select * from m_branch where M_BranchIsActive = 'Y' and M_BranchIsDefault = 'Y'";
      $qry = $this->db->query($sql);
      if (! $qry ) {
         echo 'Err : ' . print_r($this->db->error(),true) . '\n';
         exit;
      }
      $rows = $qry->result_array();
      $branchCode = '';
      if (count($rows) > 0 ) {
         $branchCode = $rows[0]['M_BranchCode'];
      }
      if ($branchCode == '') {
         echo 'Err : Invalid Branch\n';
         exit;
      }
      $sql = "insert into ssr_mr05_piutang(SsrMr05PiutangDate, SsrMr05PiutangM_BranchCode, SsrMr05PiutangM_CompanyID, 
         SsrMr05PiutangM_MouID,SsrMr05PiutangSales)
         select date(T_OrderHeaderDate) xdate, ?, T_OrderHeaderM_CompanyID, T_OrderHeaderM_MouID,
            sum(T_OrderHeaderTotal)
         from t_orderheader
         where date(T_OrderHeaderDate) >= ? and date(T_OrderHeaderDate) <= ?
            and T_OrderHeaderIsActive = 'Y'
         group by xdate,T_OrderHeaderM_CompanyID, T_OrderHeaderM_MouID";

      $qry = $this->db->query($sql, array($branchCode, $s_date,$e_date));
      if (! $qry ) {
         echo 'Err : ' . print_r($this->db->error(),true). '\n';
         exit;
      }
      $tot = $this->db->affected_rows();
      $sql = "insert into ssr_mr05_piutang(SsrMr05PiutangDate, SsrMr05PiutangM_BranchCode,  SsrMr05PiutangM_CompanyID, 
         SsrMr05PiutangM_MouID,SsrMr05PiutangPayment)
         select xdate , ?, companyID, mouID, xtotal 
         from (   
            select date(F_PaymentDate) xdate, T_OrderHeaderM_CompanyID companyID, T_OrderHeaderM_MouID mouID,
               sum(F_PaymentTotal) xtotal
            from f_payment
            join t_orderheader on F_PaymentT_OrderHeaderID = T_OrderHeaderID 
               and T_OrderHeaderDate >= '2020-02-01 00:00:01'
            where F_PaymentDate >= ? and F_PaymentDate <= ?
               and T_OrderHeaderIsActive = 'Y' and F_PaymentIsActive = 'Y'
            group by xdate,T_OrderHeaderM_CompanyID, T_OrderHeaderM_MouID
         ) payment
         on duplicate key 
         update SsrMr05PiutangPayment = xtotal";

      $qry = $this->db->query($sql, array($branchCode, $s_date,$e_date));
      if (! $qry ) {
         echo 'Err : ' . print_r($this->db->error(),true). '\n';
         exit;
      }
      $tot = $this->db->affected_rows();
      echo "Total Records : $tot\n";
   }
   function do($prm_date) {
      $sql = "delete from ssr_mr05 where SsrMr05Date = ?";
      $qry = $this->db->query($sql, array($prm_date));
      if (! $qry ) {
         echo 'Err : ' . print_r($this->db->error(),true). '\n';
         exit;
      }
      $sql = "select * from m_branch where M_BranchIsActive = 'Y' and M_BranchIsDefault = 'Y'";
      $qry = $this->db->query($sql);
      if (! $qry ) {
         echo 'Err : ' . print_r($this->db->error(),true) . '\n';
         exit;
      }
      $rows = $qry->result_array();
      $branchCode = '';
      if (count($rows) > 0 ) {
         $branchCode = $rows[0]['M_BranchCode'];
      }
      if ($branchCode == '') {
         echo 'Err : Invalid Branch\n';
         exit;
      }
      $sql = " insert into ssr_mr05 (SsrMr05Date, SsrMr05M_BranchCode, SsrMr05M_StaffNIK, 
         SsrMr05T_OrderHeaderDate, SsrMr05T_OrderHeaderID, SsrMr05M_CompanyID, SsrMr05M_MouID, SsrMr05M_MouName,
         SsrMr05M_OmzetTypeName, SsrMr05M_OmzetTypeID, SsrMr05Total, SsrMr05Payment, SsrMr05TotalPasien )
         select 
            distinct ?,?, M_StaffNIK, 
            T_OrderHeaderDate, T_OrderHeaderID, M_MouM_CompanyID, T_OrderHeaderM_MouID,
            M_MouName, M_OmzetTypeName, M_OmzetTypeID,  sum(T_OrderHeaderTotal) as total,
             sum(fn_rpt_mr05_payment(T_OrderHeaderID)), Count(T_OrderHeaderID)  
            from 
            t_orderheader
            join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
            join m_mou on T_OrderHeaderM_MouID = M_MouID  and M_MouIsActive = 'Y'
            join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID and  M_OmzetTypeIsActive = 'Y'
            join m_staff on M_CompanyM_StaffID = M_StaffID and M_StaffIsActive = 'Y'
            where T_OrderHeaderIsActive = 'Y'
            and
            date(T_OrderHeaderDate) = ? 
            and M_OmzetTypeID in ('1','2','3','4','6')
            group by M_CompanyID,M_MouID";
      $qry = $this->db->query($sql,array($prm_date,$branchCode,$prm_date));
      if (! $qry) print_r($this->db->error());
      $tot = $this->db->affected_rows();
      
      $sql = " insert into ssr_mr05 (SsrMr05Date, SsrMr05M_BranchCode, SsrMr05M_StaffNIK, 
         SsrMr05T_OrderHeaderDate, SsrMr05T_OrderHeaderID, SsrMr05M_CompanyID, SsrMr05M_MouID, SsrMr05M_MouName,
         SsrMr05M_OmzetTypeName, SsrMr05M_OmzetTypeID, SsrMr05Total, SsrMr05Payment, SsrMr05TotalPasien )
         select 
            ?, ?, M_StaffNIK, 
            T_OrderHeaderDate, T_OrderHeaderID, M_MouM_CompanyID, T_OrderHeaderM_MouID,
            M_MouName, M_OmzetTypeName, M_OmzetTypeID,  0  as total,
             sum(ifnull(F_PaymentTotal,0)), 0
            from 
            f_payment  
            join t_orderheader on F_PaymentT_OrderHeaderID = T_OrderHeaderID  and  T_OrderHeaderIsActive = 'Y'
               and date(T_OrderHeaderDate) < ? 
               and date(F_PaymentDate) = ?
               and T_OrderHeaderDate >= '2020-02-01 00:00:01'
            join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
            join m_mou on T_OrderHeaderM_MouID = M_MouID  and M_MouIsActive = 'Y'
            join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID and  M_OmzetTypeIsActive = 'Y'
            join m_staff on M_CompanyM_StaffID = M_StaffID and M_StaffIsActive = 'Y'
            where M_OmzetTypeID in ('1','2','3','4','6')
            group by M_MouID";
      $qry = $this->db->query($sql,array($prm_date, $branchCode,$prm_date,$prm_date));
      if (! $qry) print_r($this->db->error());
      $tot += $this->db->affected_rows();
       
      echo  "$prm_date : $tot records\n";
   }
   function upload_piutang($s_date, $e_date) {
      $sql = "select * from ssr_mr05_piutang
         where SsrMr05PiutangDate >= ? and SsrMr05PiutangDate <= ? 
         and SsrMr05PiutangIsSent = 'N' 
         and SsrMr05PiutangRetry < 10";
      $qry = $this->db->query($sql,array($s_date, $e_date));
      if (! $qry) {
         echo "Err : " . print_r($this->db->error(),true);
         exit;
      }
      $rows = $qry->result_array();
      $data = json_encode($rows);
      $md5 = md5($data);
      $param = array("data" => $data, "md5" => $md5);
      $jparam = json_encode($param);
      $url = "http://192.168.250.110/one-api/tools/marketing/r_mr05/piutang";
      $url = "http://devbandungraya.aplikasi.web.id/one-api/tools/marketing/r_mr05/piutang";
      
      echo "Uploading : $s_date to $e_date , total " . count($rows) . " records\n";
      $j_result = $this->post($url,$jparam); 
      $result = json_decode($j_result,true);
      $sql = "update ssr_mr05_piutang set SsrMr05PiutangRetry = SsrMr05PiutangRetry + 1 where SsrMr05PiutangDate >= ?
         and SsrMr05PiutangDate <= ? ";
      $this->db->query($sql,array($s_date, $e_date));
      if ($result["status"] == "OK") {
         $sql = "update ssr_mr05_piutang set SsrMr05PiutangIsSent = 'Y' where SsrMr05PiutangID = ?";
         foreach($result["SsrMr05PiutangID"] as $id ) {
            $this->db->query($sql, array($id));
         }
         echo $result["status"] . ", total " . count($result["SsrMr05PiutangID"]) . "\n";
         exit;
      }
      echo "ERR : " . $result["message"] . "\n";
   }

   function upload($prm_date) {
      $sql = "select * from ssr_mr05 
         where date(SsrMr05T_OrderHeaderDate) = ? 
         and SsrMr05IsSent = 'N' 
         and SsrMr05Retry < 10";
      $qry = $this->db->query($sql,array($prm_date));
      if (! $qry) {
         echo "Err : " . print_r($this->db->error(),true);
         exit;
      }
      $rows = $qry->result_array();
      $data = json_encode($rows);
      $md5 = md5($data);
      $param = array("data" => $data, "md5" => $md5);
      $jparam = json_encode($param);
      $url = "http://192.168.250.110/one-api/tools/marketing/r_mr05";
      $url = "http://devbandungraya.aplikasi.web.id/one-api/tools/marketing/r_mr05";
      
      echo "Uploading : $prm_date, total " . count($rows) . " records\n";
      $j_result = $this->post($url,$jparam); 
      $result = json_decode($j_result,true);
      $sql = "update ssr_mr05 set SsrMr05Retry = SsrMr05Retry + 1 where date(SsrMr05T_OrderHeaderDate) = ?";
      $this->db->query($sql,array($prm_date));
      if ($result["status"] == "OK") {
         $sql = "update ssr_mr05 set SsrMr05IsSent = 'Y' where SsrMr05ID = ?";
         foreach($result["SsrMr05ID"] as $id ) {
            $this->db->query($sql, array($id));
         }
         echo $result["status"] . ", total " . count($result["SsrMr05ID"]) . "\n";
         exit;
      }
      echo "ERR : " . $result["message"] . "\n";
   }
   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_HTTPHEADER, array(                                                                          
         'Content-Type: application/json',                                                                                
         'Content-Length: ' . strlen($data))                                                                       
      );                                                                                                                   
      $result = curl_exec($ch);
      return $result;
   }
}