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

<?php
/*
  alter table ssr_mr01 add SsrMr01M_PatientHp varchar(25) default '',
  add SsrMr01M_PatientAddressUtama varchar(250) default '';
*/
class Mr01 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_mr01 where SsrMr01T_OrderHeaderDate = ?";
        $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_mr01(
         SsrMr01M_BranchCode, SsrMr01M_PatientName, SsrMr01T_OrderHeaderDate, SsrMr01T_OrderHeaderLabNumberExt,
         SsrMr01T_OrderHeaderID , SsrMr01M_DoctorOldCode , SsrMr01T_TestID , SsrMr01Total,
         SsrMr01Nat_JPADetailDiscount, SsrMr01JpaGroupName, 
         SsrMr01Jpa, SsrMr01Nat_JpaGroupMaxJpaDisc, SsrMr01Bruto,
         SsrMr01M_CompanyID,SsrMr01M_DoctorAddressOldCode, SsrMr01M_DoctorID, SsrMr01M_DoctorAddressID,
         SsrMr01M_PatientHp, SsrMr01M_PatientAddressUtama
      )
      select
         ?,
         CONCAT(M_TitleName,' ',M_PatientName) AS M_PatientName, date(T_OrderHeaderDate) as tglorder, T_OrderHeaderLabNumberExt,
         T_OrderHeaderID as headerid, M_DoctorOldCode, T_TestID ,
fn_mr_price(T_OrderDetailT_TestSasCode,T_OrderDetailTotal,T_OrderDetailT_TestID, T_OrderHeaderID) T_OrderDetailTotal,
         Nat_JPADetailDiscount,Nat_JPAGroupName,
         fn_mr_price(T_OrderDetailT_TestSasCode,T_OrderDetailTotal,T_OrderDetailT_TestID, T_OrderHeaderID)*(Nat_JPADetailDiscount/100) as jpa,Nat_JpaGroupMaxJpaDisc,
fn_mr_price(T_OrderDetailT_TestSasCode,T_OrderDetailPrice,T_OrderDetailT_TestID, T_OrderHeaderID) as bruto, M_CompanyID,
         M_DoctorAddressOldCode, M_DoctorID, M_DoctorAddressID,
         M_PatientHp, fn_get_address_patient_utama(M_PatientID)  
         from 
         t_orderheader 
         join m_doctoraddress on T_OrderHeaderSenderM_DoctorAddressID = M_DoctorAddressID and M_DoctorAddressIsActive = 'Y'
         join m_doctor on M_DoctorAddressM_DoctorID = M_DoctorID and M_DoctorIsMarketingConfirm = 'Y'
         join t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID  AND T_OrderDetailIsActive = 'Y' AND T_OrderHeaderIsActive = 'Y'
         join t_test ON T_OrderDetailT_TestID = T_TestID    and T_TestIsPrice = 'Y' AND T_TestIsActive = 'Y'
         join nat_test oN T_TestNat_TestID  = Nat_TestID  aND Nat_TestIsActive = 'Y'  
         join jpa_test on JPA_TestNat_TestID = Nat_TestID  and JPA_TestIsActive  = 'Y'
         join nat_jpa on  M_DoctorAddressNat_JpaID = Nat_JpaID and Nat_JpaIsActive = 'Y' 
         join nat_jpagroup on JPA_TestNat_JpaGroupID = Nat_JPAGroupID  and Nat_JPAGroupIsActive = 'Y'
         join nat_jpadetail on Nat_JPADetailNat_JPAID = Nat_JPAID  and Nat_JPAGroupID = Nat_JPADetailNat_JPAGroupID  and Nat_JPADetailIsActive = 'Y' 
         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'

         where 
         DATE(T_OrderHeaderDate) = ?
         and M_DoctorIsMarketingConfirm  = 'Y'
         and Nat_JpaGroupMaxJpaDisc >= Nat_JPADetailDiscount
      ";
        $qry = $this->db->query($sql, [$branchCode, $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_mr01 
         where SsrMr01T_OrderHeaderDate = ? 
         and SsrMr01IsSent = 'N' 
         and SsrMr01Retry < 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_mr01";

        echo "Uploading : $prm_date, total " . count($rows) . " records\n";
        $j_result = $this->post($url, $jparam);
        $result = json_decode($j_result, true);
        $sql =
            "update ssr_mr01 set SsrMr01Retry = SsrMr01Retry + 1 where date(SsrMr01T_OrderHeaderDate) = ?";
        $this->db->query($sql, [$prm_date]);
        if ($result["status"] == "OK") {
            $sql =
                "update ssr_mr01 set SsrMr01IsSent = 'Y' where SsrMr01ID = ?";
            foreach ($result["SsrMr01ID"] as $id) {
                $this->db->query($sql, [$id]);
            }
            echo $result["status"] .
                ", total " .
                count($result["SsrMr01ID"]) .
                "\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;
    }
}