BE_CPONE/application/controllers/tools/marketing/Mr09.php

<?php
/*  
  alter table ssr_mr09 add SsrMr09M_PatientHp varchar(25) default '',
  add SsrMr09M_PatientAddressUtama varchar(250) default '';
*/
class Mr09 extends CI_Controller
{
    function __construct()
    {
        parent::__construct();
        $this->db = $this->load->database("onedev", true);
    }
    function getRegionalIP()
    {
        $sql = "select S_SystemIPAddressRegional from conf_systems";
        $qry = $this->db->query($sql);
        if (!$qry) {
            return "devone.aplikasi.web.id";
        }
        $rows = $qry->result_array();
        if (count($rows) > 0) {
            return $rows[0]["S_SystemIPAddressRegional"];
        }

        return "devone.aplikasi.web.id";
    }

    function do($prm_date)
    {
        $sql = "delete from ssr_mr09 where SsrMr09Date = ?";
        $qry = $this->db->query($sql, [$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_mr09(
   SsrMr09M_BranchCode, SsrMr09Date, SsrMr09M_PatientNoreg, SsrMr09M_PatientName,
   SsrMr09M_PatientDOB, SsrMr09M_PatientAge, SsrMr09M_PatientNIK, SsrMr09M_PatientJabatan,
   SsrMr09M_PatientKedudukan, SsrMr09M_PatientPJ, SsrMr09M_PatientLocation, SsrMr09M_PatientJob,
   SsrMr09Kedatangan, SsrMr09T_Test, SsrMr09JumlahTest, SsrMr09Bruto,
   SsrMr09Total, SsrMr09Discount, SsrMr09Payment, SsrMr09M_CompanyID, SsrMr09M_MouID, SsrMr09M_OmzetTypeID,
   SsrMr09M_PatientHp, SsrMr09M_PatientAddressUtama
      )
   select ? , ? , M_PatientNoreg, M_PatientName,
   M_PatientDOB, T_OrderHeaderM_PatientAge, M_PatientNIK , M_PatientJabatan ,
   M_PatientKedudukan, M_PatientPJ , M_PatientLocation , M_PatientJob,
   sum(kedatangan) as kedatangan, group_concat(test separator ',') as test, sum(jmltest) as jmltest, sum(bruto) as bruto, 
   sum(T_OrderHeaderTotal) as T_OrderHeaderTotal, sum(disc) as disc, sum(pay) as pay, M_CompanyID, M_MouID,
   M_OmzetTypeID, M_PatientHp, SsrMr09M_PatientAddressUtama
   from 
   (
      select
      concat(M_TitleName,'. ',M_PatientName) as M_PatientName, M_PatientNoreg, M_PatientID,
      M_OmzetTypeID, M_PatientDOB, T_OrderHeaderM_PatientAge, M_CompanyID, M_MouID,
      sum(T_OrderDetailPrice) as bruto, sum(T_OrderDetailDiscTotal) as disc, T_OrderHeaderTotal , 
      sum(distinct F_PaymentTotal) as pay, count(T_TestID) as jmltest, group_concat(T_TestName separator ',') as test,
      M_PatientNIK , M_PatientJabatan , M_PatientKedudukan, M_PatientPJ , M_PatientLocation , M_PatientJob,
      count(distinct T_OrderHeaderID) as kedatangan,
      M_PatientHp,fn_get_address_patient_utama(M_PatientID) SsrMr09M_PatientAddressUtama 
      from t_orderheader  
         join m_mou on T_OrderHeaderM_MouID  = M_MouID and T_OrderHeaderIsActive = 'Y'
       join m_omzettype on M_MouM_OmzetTypeID =  M_OmzetTypeID 
      left join m_company ON T_OrderHeaderM_CompanyID = M_CompanyID    AND M_CompanyIsActive = 'Y'
      left join m_patient ON T_OrderHeaderM_PatientID = M_PatientID  AND M_PatientIsActive = 'Y'
      left join m_title ON M_PatientM_TitleID = M_TitleID  AND M_TitleIsActive = 'Y'
      left join t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID   AND T_OrderDetailT_TestIsPrice = 'Y' AND T_OrderDetailIsActive = 'Y'
      left join t_test ON T_OrderDetailT_TestID = T_TestID     AND T_TestIsActive = 'Y' 
      left join f_payment on F_PaymentT_OrderHeaderID = T_OrderHeaderID and F_PaymentIsActive = 'Y'
      where T_OrderDetailIsActive = 'Y'
      and date(T_OrderHeaderDate) = ?
      group by T_OrderHeaderID
    ) as x group by M_PatientID,M_CompanyID, M_MouID
      ";
        $qry = $this->db->query($sql, [$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($prm_date)
    {
        $sql = "select * from ssr_mr09 
         where SsrMr09Date = ? 
         and SsrMr09IsSent = 'N' 
         and SsrMr09Retry < 10";
        $qry = $this->db->query($sql, [$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 = ["data" => $data, "md5" => $md5];
        $jparam = json_encode($param);
        $regionalIP = $this->getRegionalIP();
        $url = "http://$regionalIP/one-api/tools/marketing/r_mr09";

        echo "Uploading : $prm_date, total " . count($rows) . " records\n";
        $j_result = $this->post($url, $jparam);
        $result = json_decode($j_result, true);
        $sql =
            "update ssr_mr09 set SsrMr09Retry = SsrMr09Retry + 1 where date(SsrMr09T_OrderHeaderDate) = ?";
        $this->db->query($sql, [$prm_date]);
        if ($result["status"] == "OK") {
            $sql =
                "update ssr_mr09 set SsrMr09IsSent = 'Y' where SsrMr09ID = ?";
            foreach ($result["SsrMr09ID"] as $id) {
                $this->db->query($sql, [$id]);
            }
            echo $result["status"] .
                ", total " .
                count($result["SsrMr09ID"]) .
                "\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, [
            "Content-Type: application/json",
            "Content-Length: " . strlen($data),
        ]);
        $result = curl_exec($ch);
        return $result;
    }
}