<?php
class Whatsappstatushallosis extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "Verify Doctor API";
   }

   public function __construct()
   {
      parent::__construct();
      $this->db_onedev = $this->load->database("onedev", true);
   }

   //    public function searchhallosis()
   //    {
   //       $prm = $this->sys_input;
   //       if (!$this->isLogin) {
   //          $this->sys_error("Invalid Token");
   //          exit;
   //       }

   //       $nama   = isset($prm["name"]) ? $prm["name"] : "";
   //       $date   = isset($prm['date']) ? $prm['date'] : date('Y-m-d');
   //       $status = isset($prm['status']) ? $prm['status'] : "";

   //       $number_limit = 10;
   //       $current_page = isset($prm['current_page']) ? $prm['current_page'] : 1;
   //       $number_offset = ($current_page - 1) * $number_limit;

   //       $sql_param = array();

   //       $sql = "SELECT
   // oh.T_OrderHeaderID,
   // oh.T_OrderHeaderLabNumber AS No_Lab,
   // oh.T_OrderHeaderDate AS Tanggal,
   // md.M_DeliveryID,

   // GROUP_CONCAT(DISTINCT IF(COALESCE(T_TestIsNonLab,'')='', 'LAB', T_TestIsNonLab) SEPARATOR ', ') AS Daftar_Pemeriksaan,

   // TRIM(CONCAT_WS(' ', NULLIF(M_TitleName,''), p.M_PatientName)) AS Nama_Pasien,
   // p.M_PatientDOB AS DOB_Pasien,

   // CONCAT_WS(', ', TRIM(CONCAT_WS(' ', 
   // NULLIF(d.M_DoctorPrefix,''), d.M_DoctorName)),
   // NULLIF(d.M_DoctorSufix,''), 
   // NULLIF(d.M_DoctorSufix2,''), 
   // NULLIF(d.M_DoctorSufix3,'')
   // ) AS Nama_Dokter_Lengkap,

   // oh.T_OrderHeaderTotal AS Total_Tagihan,
   // od.T_OrderDeliveryDestination AS No_WhatsApp,
   // md.M_DeliveryName AS Jenis_WA,

   // IFNULL(mou.M_MouName, 'UMUM/TANPA MOU') AS Nama_MOU,
   // CASE 
   // WHEN mou.M_MouIsBill = 'Y' THEN 'Ditagihkan' 
   // WHEN py.Last_StatusPaymentIsLunas = 'Y' THEN 'Lunas' ELSE 'Belum Lunas' END AS Status_Bayar, 

   // CASE 
   // WHEN tx.Tx_WhatsappID IS NULL THEN 'Pending'
   // WHEN tx.Tx_WhatsappStatus = 'Sending' THEN 'Sending'
   // WHEN tx.Tx_WhatsappIsReply = 'Y' THEN
   // CASE
   // WHEN tx.Tx_WhatsappReplyDOB = p.M_PatientDOB THEN 'DOB.OK'
   // ELSE 'DOB.ERR' END ELSE 'Confirm' END AS Status_WA 

   // FROM t_orderheader oh

   // INNER JOIN t_orderdetail odet ON oh.T_OrderHeaderID = odet.T_OrderDetailT_OrderHeaderID AND odet.T_OrderDetailIsActive = 'Y' 
   // INNER JOIN t_test ON odet.T_OrderDetailT_TestID = T_TestID
   // INNER JOIN m_patient p ON oh.T_OrderHeaderM_PatientID = p.M_PatientID
   // INNER JOIN m_doctor d ON oh.T_OrderHeaderSenderM_DoctorID = d.M_DoctorID
   // INNER JOIN t_orderdelivery od ON oh.T_OrderHeaderID = od.T_OrderDeliveryT_OrderHeaderID
   // INNER JOIN m_delivery md ON od.T_OrderDeliveryM_DeliveryID = md.M_DeliveryID AND md.M_DeliveryID IN (6, 7)
   // INNER JOIN halosis_api.template tpl ON md.M_DeliveryID = tpl.TemplateM_DeliveryID AND tpl.TemplateIsActive = 'Y'

   // LEFT JOIN m_mou mou ON oh.T_OrderHeaderM_MouID = mou.M_MouID AND mou.M_MouIsActive = 'Y'
   // LEFT JOIN last_statuspayment py ON oh.T_OrderHeaderID = py.Last_StatusPaymentT_OrderHeaderID AND py.Last_StatusPaymentIsActive = 'Y'
   // LEFT JOIN m_title mt ON p.M_PatientM_TitleID = mt.M_TitleID AND mt.M_TitleIsActive = 'Y'
   // LEFT JOIN tx_whatsapp tx ON oh.T_OrderHeaderID = tx.Tx_WhatsappT_OrderHeaderID AND md.M_DeliveryID = tx.Tx_WhatsappM_DeliveryID AND tx.Tx_WhatsappIsActive = 'Y'
   // LEFT JOIN halosis th ON oh.T_OrderHeaderID = th.T_OrderHeaderID AND md.M_DeliveryID = th.M_DeliveryIDWHERE (mou.M_MouIsBill = 'Y' OR py.Last_StatusPaymentIsLunas = 'Y')

   // AND oh.T_OrderHeaderIsActive = 'Y'
   // AND DATE(oh.T_OrderHeaderDate) = ?
   // AND (oh.T_OrderHeaderLabNumber LIKE ? OR p.M_PatientName LIKE ?)";

   //       if ($status !== "") {
   //          $sql .= " AND IFNULL(tx.Tx_WhatsappIsSent, 'N') = ? ";
   //       }

   //       $sql .= " GROUP BY oh.T_OrderHeaderID, md.M_DeliveryID
   // ORDER BY oh.T_OrderHeaderLabNumber ASC
   // LIMIT ? OFFSET ?";

   //       // Isi Parameter Binding
   //       $sql_param[] = $date;
   //       $sql_param[] = "%$nama%";
   //       $sql_param[] = "%$nama%";
   //       if ($status !== "") $sql_param[] = $status;
   //       $sql_param[] = (int)$number_limit;
   //       $sql_param[] = (int)$number_offset;

   //       $query = $this->db_onedev->query($sql, $sql_param);
   //       $rows = $query ? $query->result_array() : [];

   //       // Hitung Total untuk Pagination
   //       $sql_count = "SELECT COUNT(DISTINCT oh.T_OrderHeaderID) as total 
   //          FROM t_orderheader oh
   //          INNER JOIN m_patient p ON oh.T_OrderHeaderM_PatientID = p.M_PatientID
   //          INNER JOIN t_orderdelivery od ON oh.T_OrderHeaderID = od.T_OrderDeliveryT_OrderHeaderID
   //          INNER JOIN m_delivery md ON od.T_OrderDeliveryM_DeliveryID = md.M_DeliveryID AND md.M_DeliveryID IN (6, 7)
   //          LEFT JOIN tx_whatsapp tx ON oh.T_OrderHeaderID = tx.Tx_WhatsappT_OrderHeaderID AND md.M_DeliveryID = tx.Tx_WhatsappM_DeliveryID AND tx.Tx_WhatsappIsActive = 'Y'
   //          WHERE DATE(oh.T_OrderHeaderDate) = ? 
   //          AND oh.T_OrderHeaderIsActive = 'Y'
   //          AND (oh.T_OrderHeaderLabNumber LIKE ? OR p.M_PatientName LIKE ?)";

   //       if ($status !== "") {
   //          $sql_count .= " AND IFNULL(tx.Tx_WhatsappIsSent, 'N') = ? ";
   //       }

   //       $count_params = array_merge(array($date, "%$nama%", "%$nama%"), ($status !== "" ? array($status) : array()));
   //       $query_count = $this->db_onedev->query($sql_count, $count_params);
   //       $tot_count = $query_count ? $query_count->row()->total : 0;
   //       $tot_page = ceil($tot_count / $number_limit);

   //       $result = array(
   //          "total" => $tot_page,
   //          "records" => $rows,
   //          "count" => $tot_count,
   //          "sql" => $this->db_onedev->last_query()
   //       );

   //       $this->sys_ok($result);
   //       exit;
   //    }


   public function searchhallosis()
   {
      $prm = $this->sys_input;
      if (!$this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $nama   = isset($prm["name"]) ? $prm["name"] : "";
      $date   = isset($prm['date']) ? $prm['date'] : date('Y-m-d');
      $status = isset($prm['status']) ? $prm['status'] : "";

      $number_limit = 10;
      $current_page = isset($prm['current_page']) ? $prm['current_page'] : 1;
      $number_offset = ($current_page - 1) * $number_limit;

      // Logika Tambahan Baru
      // $status_logic = "CASE 
      // WHEN th.HalosisID IS NULL THEN 'Pending'
      // WHEN th.HalosisStatus = 'NEW' THEN 'Confirm'
      // WHEN th.HalosisStatus = 'SENT' THEN 'Sending'
      // WHEN th.HalosisStatus = 'RETRIED' THEN 'Retrying'
      // WHEN th.HalosisStatus = 'DOB_VALID' THEN 'DOB.OK'
      // WHEN th.HalosisStatus = 'DOB_ERR' THEN 'DOB.ERR'
      // WHEN th.HalosisStatus = 'SENT_ERR' THEN 'Error'
      // ELSE th.HalosisStatus 
      // END";

      $status_logic = "CASE 
      WHEN th.HalosisStatus IN ('SENT', 'DOB_VALID') THEN 'Terkirim'
      WHEN th.HalosisStatus IN ('NEW', 'RETRY', 'DOB_ERR') THEN 'Pending'
      WHEN th.HalosisID IS NULL THEN 'Pending'
      ELSE 'Pending'
      END";

      $sql = "SELECT
      oh.T_OrderHeaderID,
      oh.T_OrderHeaderLabNumber AS No_Lab,
      oh.T_OrderHeaderDate AS Tanggal,
      md.M_DeliveryID,
      
      GROUP_CONCAT(DISTINCT IF(COALESCE(T_TestIsNonLab,'')='', 'LAB', T_TestIsNonLab) SEPARATOR ', ') AS Daftar_Pemeriksaan,
      
      TRIM(CONCAT_WS(' ', NULLIF(M_TitleName,''), p.M_PatientName)) AS Nama_Pasien,
      p.M_PatientDOB AS DOB_Pasien,
      
      CONCAT_WS(', ', TRIM(CONCAT_WS(' ', 
      NULLIF(d.M_DoctorPrefix,''), d.M_DoctorName)),
      NULLIF(d.M_DoctorSufix,''), 
      NULLIF(d.M_DoctorSufix2,''), 
      NULLIF(d.M_DoctorSufix3,'')
      ) AS Nama_Dokter_Lengkap,
      
      oh.T_OrderHeaderTotal AS Total_Tagihan,
      od.T_OrderDeliveryDestination AS No_WhatsApp,
      md.M_DeliveryName AS Jenis_WA,
      
      IFNULL(mou.M_MouName, 'UMUM/TANPA MOU') AS Nama_MOU,
      CASE
      WHEN mou.M_MouIsBill = 'Y' THEN 'Ditagihkan' 
      WHEN py.Last_StatusPaymentIsLunas = 'Y' THEN 'Lunas' ELSE 'Belum Lunas' 
      END AS Status_Bayar, 
      
      $status_logic AS Status_WA
      FROM t_orderheader oh
      INNER JOIN m_patient p ON oh.T_OrderHeaderM_PatientID = p.M_PatientID
      INNER JOIN m_doctor d ON oh.T_OrderHeaderSenderM_DoctorID = d.M_DoctorID
      INNER JOIN t_orderdelivery od ON oh.T_OrderHeaderID = od.T_OrderDeliveryT_OrderHeaderID
      INNER JOIN m_delivery md ON od.T_OrderDeliveryM_DeliveryID = md.M_DeliveryID AND md.M_DeliveryID IN (6, 7)
      
      LEFT JOIN halosis_api.template tpl ON md.M_DeliveryID = tpl.TemplateM_DeliveryID AND tpl.TemplateIsActive = 'Y'
      LEFT JOIN t_orderdetail odet ON oh.T_OrderHeaderID = odet.T_OrderDetailT_OrderHeaderID AND odet.T_OrderDetailIsActive = 'Y' 
      LEFT JOIN t_test t ON odet.T_OrderDetailT_TestID = t.T_TestID
      LEFT JOIN m_mou mou ON oh.T_OrderHeaderM_MouID = mou.M_MouID AND mou.M_MouIsActive = 'Y'
      LEFT JOIN last_statuspayment py ON oh.T_OrderHeaderID = py.Last_StatusPaymentT_OrderHeaderID AND py.Last_StatusPaymentIsActive = 'Y'
      LEFT JOIN m_title mt ON p.M_PatientM_TitleID = mt.M_TitleID AND mt.M_TitleIsActive = 'Y'
      LEFT JOIN halosis th ON oh.T_OrderHeaderID = th.HalosisT_OrderHeaderID AND md.M_DeliveryID = th.HalosisM_DeliveryID
      
      WHERE (mou.M_MouIsBill = 'Y' OR py.Last_StatusPaymentIsLunas = 'Y')AND oh.T_OrderHeaderIsActive = 'Y'
      AND DATE(oh.T_OrderHeaderDate) = ?
      AND (oh.T_OrderHeaderLabNumber LIKE ? OR p.M_PatientName LIKE ?)";

      if ($status !== "") {
         $sql .= " AND ($status_logic) = ? ";
      }

      $sql .= " GROUP BY oh.T_OrderHeaderID, md.M_DeliveryID
      ORDER BY oh.T_OrderHeaderLabNumber ASC
      LIMIT ? OFFSET ?";

      $sql_param = array($date, "%$nama%", "%$nama%");
      if ($status !== "") $sql_param[] = $status;
      $sql_param[] = (int)$number_limit;
      $sql_param[] = (int)$number_offset;

      $query = $this->db_onedev->query($sql, $sql_param);
      $rows = $query ? $query->result_array() : [];

  
      $sql_count = "SELECT COUNT(*) as total FROM (
         SELECT oh.T_OrderHeaderID 
         FROM t_orderheader oh
         INNER JOIN m_patient p ON oh.T_OrderHeaderM_PatientID = p.M_PatientID
         INNER JOIN m_delivery md ON 1=1 AND md.M_DeliveryID IN (6, 7)
         LEFT JOIN m_mou mou ON oh.T_OrderHeaderM_MouID = mou.M_MouID
         LEFT JOIN last_statuspayment py ON oh.T_OrderHeaderID = py.Last_StatusPaymentT_OrderHeaderID
         LEFT JOIN halosis th ON oh.T_OrderHeaderID = th.HalosisT_OrderHeaderID AND md.M_DeliveryID = th.HalosisM_DeliveryID
         WHERE (mou.M_MouIsBill = 'Y' OR py.Last_StatusPaymentIsLunas = 'Y')
         AND oh.T_OrderHeaderIsActive = 'Y'
         AND DATE(oh.T_OrderHeaderDate) = ?
         AND (oh.T_OrderHeaderLabNumber LIKE ? OR p.M_PatientName LIKE ?)" .
         ($status !== "" ? " AND ($status_logic) = ? " : "") .
         " GROUP BY oh.T_OrderHeaderID, md.M_DeliveryID
         ) AS temp";

      $count_params = array($date, "%$nama%", "%$nama%");
      if ($status !== "") $count_params[] = $status;
      $tot_count = $this->db_onedev->query($sql_count, $count_params)->row()->total;

      $this->sys_ok([
         "total" => ceil($tot_count / $number_limit),
         "records" => $rows,
         "count" => $tot_count,
      ]);
   }


   public function searchhalosisdetail()
   {
      $prm = $this->sys_input;
      if (!$this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $order_id    = isset($prm["order_id"]) ? $prm["order_id"] : "";
      $delivery_id = isset($prm["delivery_id"]) ? $prm["delivery_id"] : "";

      if (empty($order_id) || empty($delivery_id)) {
         $this->sys_error("Parameter tidak lengkap");
         exit;
      }

      $sql = "SELECT 
oh.T_OrderHeaderLabNumber AS No_Reg,

TRIM(CONCAT_WS(' ', mt.M_TitleName, p.M_PatientName)) AS Nama_Pasien,
CONCAT_WS(', ', TRIM(CONCAT_WS(' ', NULLIF(d.M_DoctorPrefix,''), d.M_DoctorName)), NULLIF(d.M_DoctorSufix,'')) AS Nama_Dokter,

md.M_DeliveryName,
od.T_OrderDeliveryDestination AS No_WhatsApp,

IF(COALESCE(t.T_TestIsNonLab,'')='', 'LAB', t.T_TestIsNonLab) AS Kategori,
odet.T_OrderDetailT_TestName AS Nama_Tes,
sre.So_ResultEntryID,

CASE WHEN mou.M_MouIsBill = 'Y' THEN 'Ditagihkan' WHEN py.Last_StatusPaymentIsLunas = 'Y' THEN 'Lunas' ELSE 'Belum Lunas' END AS Status_Bayar

FROM t_orderheader oh

INNER JOIN t_orderdetail odet ON oh.T_OrderHeaderID = odet.T_OrderDetailT_OrderHeaderID
LEFT JOIN t_test t ON odet.T_OrderDetailT_TestID = t.T_TestID
INNER JOIN m_patient p ON oh.T_OrderHeaderM_PatientID = p.M_PatientID
LEFT JOIN m_title mt ON p.M_PatientM_TitleID = mt.M_TitleID
INNER JOIN m_doctor d ON oh.T_OrderHeaderSenderM_DoctorID = d.M_DoctorID
INNER JOIN t_orderdelivery od ON oh.T_OrderHeaderID = od.T_OrderDeliveryT_OrderHeaderID
INNER JOIN m_delivery md ON od.T_OrderDeliveryM_DeliveryID = md.M_DeliveryID
LEFT JOIN m_mou mou ON oh.T_OrderHeaderM_MouID = mou.M_MouID
LEFT JOIN last_statuspayment py ON oh.T_OrderHeaderID = py.Last_StatusPaymentT_OrderHeaderID
LEFT JOIN so_resultentry sre ON odet.T_OrderDetailID = sre.So_ResultEntryT_OrderDetailID AND sre.So_ResultEntryIsActive = 'Y'

WHERE oh.T_OrderHeaderID = ?
AND md.M_DeliveryID = ?
AND odet.T_OrderDetailT_TestIsPanelChildren = 'N';
      ";
      $query = $this->db_onedev->query($sql, array($order_id, $delivery_id));
      $rows = $query ? $query->result_array() : [];

      if (empty($rows)) {
         $this->sys_error("Data tidak ditemukan");
         exit;
      }

      // Cek status detail
      // Cek status header di tabel halosis
      $sql_halosis_check = "SELECT HalosisID, HalosisStatus FROM halosis WHERE HalosisT_OrderHeaderID = ? AND HalosisM_DeliveryID = ?";
      $query_halosis_check = $this->db_onedev->query($sql_halosis_check, [$order_id, $delivery_id]);

      $halosis_header_status = 'Pending';
      $existing_details = [];

      if ($query_halosis_check && $query_halosis_check->num_rows() > 0) {
         $row_h = $query_halosis_check->row();
         $halosis_header_status = ($row_h->HalosisStatus == 'NEW') ? 'Confirm' : $row_h->HalosisStatus;
         $halosis_id = $row_h->HalosisID;

         $query_details = $this->db_onedev->query("SELECT HalosisDetailRptName FROM halosis_detail WHERE HalosisDetailHalosisID = ?", [$halosis_id]);
         if ($query_details) {
            foreach ($query_details->result_array() as $row_d) {
               $existing_details[] = $row_d['HalosisDetailRptName'];
            }
         }
      }

      $header = [];
      $pemeriksaan = [];
      $grouped_data = [];

      foreach ($rows as $row) {
         if (empty($header)) {
            $header = [
               "no_reg"         => $row['No_Reg'],
               "nama_pasien"    => $row['Nama_Pasien'],
               "dokter"         => $row['Nama_Dokter'],
               "tujuan_alamat"  => $row['M_DeliveryName'] . " : " . $row['No_WhatsApp'],
               "jenis_wa"       => $row['M_DeliveryName'],
               "no_wa"          => $row['No_WhatsApp'],
               "status_bayar"   => $row['Status_Bayar'],
               "status_header"  => $halosis_header_status
            ];
         }

         $kat = strtoupper(trim($row['Kategori']));
         $nama_tes = strtoupper($row['Nama_Tes']);

         // Deteksi manual berdasarkan nama tes (sesuai logika search utama)
         if (strpos($nama_tes, 'USG') !== false) {
            $kat = "USG";
         } elseif (strpos($nama_tes, 'EKG') !== false || strpos($nama_tes, 'ECG') !== false || strpos($nama_tes, 'TREADMILL') !== false) {
            $kat = "ELEKTROMEDIS";
         }

         if (!isset($grouped_data[$kat])) {
            $grouped_data[$kat] = [];
         }
         if (!empty($row['So_ResultEntryID'])) {
            $grouped_data[$kat][] = $row['So_ResultEntryID'];
         }
      }

      foreach ($grouped_data as $kat => $ids) {
         $pid_to_use = !empty($ids) ? $ids[0] : $order_id;

         $rpt_name = "";
         $url = "#";
         if ($kat == "LAB") {
            $rpt_name = "rpt_test_email.rptdesign";
            $url = "https://devone.aplikasi.web.id/birt/frameset?__report=report/one/lab/" . $rpt_name . "&__format=pdf&username=AMALIA&PLang=1&PID=" . $pid_to_use;
         } else if ($kat == "USG") {
            $rpt_name = "rpt_hasil_so_usg_email.rptdesign";
            $url = "https://devone.aplikasi.web.id/birt/run?__report=report/one/lab/" . $rpt_name . "&__format=pdf&username=ABITA%20JUWITA%20SARI&PLang=1&PID=" . $pid_to_use;
         } else if (in_array($kat, ["ELEKTROMEDIS", "ELEKTROMEDIK", "ELECTROMEDIS", "ELECTROMEDIC"])) {
            $rpt_name = "rpt_hasil_so_elmd_email.rptdesign";
            $url = "https://devone.aplikasi.web.id/birt/run?__report=report/one/lab/" . $rpt_name . "&__format=pdf&username=ABITA%20JUWITA%20SARI&PLang=1&PID=" . $pid_to_use;
         }

         $status_detail = 'Pending';
         if (!empty($rpt_name) && in_array($rpt_name, $existing_details)) {
            $status_detail = 'Confirm';
         }
         $pemeriksaan[] = [
            "kategori"  => $kat,
            "status"    => $status_detail,
            "url_cetak" => $url,
            "rpt_name"  => $rpt_name
         ];
      }

      $this->sys_ok(["header" => $header, "pemeriksaan" => $pemeriksaan]);
      exit;
   }


   //INI UNTU INSERTNYA
   public function confirmHalosis()
   {
      if (!$this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $prm = $this->sys_input;
      $order_id = isset($prm["order_id"]) ? $prm["order_id"] : "";
      $delivery_id = isset($prm["delivery_id"]) ? $prm["delivery_id"] : "";

      if (empty($order_id) || empty($delivery_id)) {
         $this->sys_error("Parameter tidak lengkap");
         exit;
      }

      $check_sql = "SELECT HalosisID FROM halosis WHERE HalosisT_OrderHeaderID = ? AND HalosisM_DeliveryID = ?";
      $check_query = $this->db_onedev->query($check_sql, [$order_id, $delivery_id]);
      if ($check_query && $check_query->num_rows() > 0) {
         // Jika sudah ada, anggap sukses saja agar tidak error di frontend
         $this->sys_ok(["message" => "Data sudah terkonfirmasi sebelumnya."]);
         exit;
      }

      $insert_sql = "INSERT INTO halosis (HalosisT_OrderHeaderID, HalosisM_DeliveryID, HalosisStatus, HalosisCreated) VALUES (?, ?, 'NEW', NOW())";
      $this->db_onedev->query($insert_sql, [$order_id, $delivery_id]);

      if ($this->db_onedev->affected_rows() > 0) {
         $this->sys_ok(["message" => "Berhasil dikonfirmasi."]);
      } else {
         $this->sys_error("Gagal menyimpan data.");
      }
      exit;
   }

   //DAN INI JUGA
   public function confirmHalosisDetail()
   {
      if (!$this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $prm = $this->sys_input;
      $order_id = isset($prm["order_id"]) ? $prm["order_id"] : "";
      $delivery_id = isset($prm["delivery_id"]) ? $prm["delivery_id"] : "";
      $rpt_name = isset($prm["rpt_name"]) ? $prm["rpt_name"] : "";

      if (empty($order_id) || empty($delivery_id) || empty($rpt_name)) {
         $this->sys_error("Parameter tidak lengkap");
         exit;
      }

      $sql_halosis = "SELECT HalosisID FROM halosis WHERE HalosisT_OrderHeaderID = ? AND HalosisM_DeliveryID = ?";
      $query_halosis = $this->db_onedev->query($sql_halosis, [$order_id, $delivery_id]);
      if (!$query_halosis || $query_halosis->num_rows() == 0) {
         // Jika header belum ada, otomatis buatkan header baru
         $insert_header = "INSERT INTO halosis (HalosisT_OrderHeaderID, HalosisM_DeliveryID, HalosisStatus, HalosisCreated) VALUES (?, ?, 'NEW', NOW())";
         $this->db_onedev->query($insert_header, [$order_id, $delivery_id]);
         $halosis_id = $this->db_onedev->insert_id();
      } else {
         $halosis_id = $query_halosis->row()->HalosisID;
      }

      $sql_check = "SELECT HalosisDetailID FROM halosis_detail WHERE HalosisDetailHalosisID = ? AND HalosisDetailRptName = ?";
      $query_check = $this->db_onedev->query($sql_check, [$halosis_id, $rpt_name]);
      if ($query_check && $query_check->num_rows() > 0) {
         $this->sys_ok(["message" => "Data detail sudah terkonfirmasi sebelumnya."]);
         exit;
      }

      $sql_insert = "INSERT INTO halosis_detail (HalosisDetailHalosisID, HalosisDetailRptName, HalosisDetailCreated) VALUES (?, ?, NOW())";
      $this->db_onedev->query($sql_insert, [$halosis_id, $rpt_name]);

      if ($this->db_onedev->affected_rows() > 0) {
         $this->sys_ok(["message" => "Berhasil konfirmasi detail."]);
      } else {
         $this->sys_error("Gagal menyimpan data detail.");
      }
      exit;
   }


   public function search()
   {
      $prm = $this->sys_input;
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $nama = $prm["name"];
      $date = $prm['date'];
      $status = $prm["status"];

      /*     $e = explode('+', $prm['name']);
      if (isset($e[0]))
      $nama = "%{$e[0]}%";
      if (isset($e[1]))
      $nohp = "%{$e[1]}%";
      // echo $norm;

      $sql_where = "";
      $sql_param = array();
    if ($nama != "") {
         if ($sql_where != "") {
            $sql_where .=" and ";
         }
         $sql_where .= " M_PatientName like ?";
         $sql_param[] = "$nama";
         //$prm['current_page'] = 1;
      }
      if ($sql_where != "") $sql_where .= " and ";
*/
      // Order masih dalam status registrasi
      $sql_where .= " IFNULL(Tx_WhatsappIsSent,'N') =  '{$status}' AND T_OrderHeaderIsActive = 'Y' AND (date(T_OrderHeaderDate) = '{$date}' OR date(T_OrderDeliveryCreated) = '{$date}') AND date(T_OrderHeaderDate) > '2020-09-27'
      AND (JSON_UNQUOTE(JSON_EXTRACT(fn_get_patient_atribute(T_OrderHeaderM_PatientID), '$.patient_fullname')) LIKE CONCAT('%','{$nama}','%') OR T_OrderHeaderLabNumber LIKE CONCAT('%','{$nama}','%'))";
      if ($sql_where != "") {
         $sql_where = " where $sql_where";
         //$prm['current_page'] = 1;
      }
      $number_limit = 10;
      $number_offset = ($prm['current_page'] - 1) * $number_limit;

      $sql = "	SELECT count(*) as total
      FROM
      (SELECT T_OrderHeaderID FROM t_orderheader 
      JOIN t_orderheaderaddon ON T_OrderHeaderAddOnT_OrderHeaderID = T_OrderHeaderID
      join t_orderdelivery on T_OrderHeaderID = T_OrderDeliveryT_OrderHeaderID and T_OrderDeliveryIsActive = 'Y' and T_OrderDeliveryM_DeliveryTypeID = 4
      join m_delivery ON M_DeliveryID = T_OrderDeliveryM_DeliveryID
      JOIN m_doctor ON T_OrderHeaderSenderM_DoctorID = M_DoctorID
      join last_statuspayment ON Last_StatusPaymentT_OrderHeaderID = T_OrderHeaderID AND Last_StatusPaymentIsActive = 'Y'
      JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID 
      join group_resultdetail on Group_ResultDetailT_TestID = T_OrderDetailT_TestID
      and T_OrderDetailIsActive = 'Y' and Group_ResultDetailIsActive = 'Y'
      and T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
      join group_result on Group_ResultDetailGroup_ResultID = Group_ResultID
      LEFT JOIN tx_whatsapp ON Tx_WhatsappT_OrderHeaderID = T_OrderHeaderID AND Tx_WhatsappGroup_ResultID = Group_ResultID AND Tx_WhatsappM_DeliveryID = M_DeliveryID AND Tx_WhatsappIsActive = 'Y' 
      LEFT JOIN t_email_nonlab ON T_EmailNonLabT_OrderHeaderID = T_OrderDetailT_OrderHeaderID  AND
      T_EmailNonLabType LIKE CONCAT('%',REPLACE(Group_ResultName, 'Elektromedik', 'electromedis'),'%')
      JOIN t_orderpromise ON T_OrderPromiseT_OrderHeaderID = T_OrderHeaderID
      JOIN result_processtooffice ON Result_ProcessToOfficeT_OrderPromiseID = T_OrderPromiseID AND 
      T_OrderDeliveryID = Result_ProcessToOfficeT_OrderDeliveryID AND Result_ProcessToOfficeStatus = 'S'
      $sql_where
      GROUP BY Group_ResultID,M_DeliveryID,T_OrderHeaderID
      UNION
      SELECT T_OrderHeaderID FROM t_orderheader 
      JOIN t_orderheaderaddon ON T_OrderHeaderAddOnT_OrderHeaderID = T_OrderHeaderID
      join t_orderdelivery on T_OrderHeaderID = T_OrderDeliveryT_OrderHeaderID and T_OrderDeliveryIsActive = 'Y' and T_OrderDeliveryM_DeliveryTypeID = 4
      join m_delivery ON M_DeliveryID = T_OrderDeliveryM_DeliveryID
      JOIN m_doctor ON T_OrderHeaderSenderM_DoctorID = M_DoctorID
      join last_statuspayment ON Last_StatusPaymentT_OrderHeaderID = T_OrderHeaderID AND Last_StatusPaymentIsActive = 'Y'
      LEFT JOIN tx_whatsapp ON Tx_WhatsappT_OrderHeaderID = T_OrderHeaderID AND Tx_WhatsappIsActive = 'Y' 
      $sql_where AND Tx_WhatsappGroup_ResultID = -1 AND Tx_WhatsappM_DeliveryID = -1
      GROUP BY M_DeliveryID,T_OrderHeaderID) a
";
      $query = $this->db_onedev->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("t_orderheader count", $this->db_onedev);
         exit;
      }

      $sql = "SELECT T_OrderHeaderID,	
      Tx_WhatsappGroup_ResultID,
CONCAT(M_DoctorPrefix, ' ',M_DoctorName) as M_DoctorName,	
JSON_UNQUOTE(JSON_EXTRACT(fn_get_patient_atribute(T_OrderHeaderM_PatientID), '$.patient_fullname')) as M_PatientName,
T_OrderHeaderLabNumber,
CONCAT(
        'https://devone.aplikasi.web.id/birt/frameset?__report=report/one/lab/',
        CASE 
            WHEN GROUP_CONCAT(T_OrderDetailT_TestName) LIKE '%USG%' THEN 'rpt_hasil_so_usg_email.rptdesign'
            WHEN GROUP_CONCAT(T_OrderDetailT_TestName) LIKE '%ECG%' OR GROUP_CONCAT(T_OrderDetailT_TestName) LIKE '%EKG%' THEN 'rpt_hasil_so_elmd_email.rptdesign'
            ELSE 'rpt_test_email.rptdesign'
        END,
        '&__format=pdf&username=', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(fn_get_patient_atribute(T_OrderHeaderM_PatientID), '$.patient_fullname')), ' ', '%20'),
        '&PID=', T_OrderHeaderID,
        '&PLang=1'
      ) AS url_print,
IF(T_EmailNonLabUrl IS NULL AND Group_ResultFlagNonLab = 'Y',' [Belum Pilih Format Hasil]','') temail,
Group_ResultID,
GROUP_CONCAT(DISTINCT Group_ResultName SEPARATOR ' ,') as Group_ResultName,
IF(Last_StatusPaymentIsLunas = 'N','Belum Lunas','Sudah Lunas') as Last_StatusPaymentIsLunas,
IF(IFNULL(Tx_WhatsappIsSent,'N') = 'N','Pending','Terkirim') as wastatus,
IF(IF(Group_ResultFlagNonLab = 'Y','Y',T_OrderHeaderAddOnValidationDone) = 'N','Belum Selesai','Selesai') as validasi,
M_DeliveryName,
M_DeliverySource,
T_OrderDeliveryDestination,
CASE  
WHEN M_DeliverySource = 'PATIENT' THEN IF(JSON_UNQUOTE(JSON_EXTRACT(fn_get_patient_atribute(T_OrderHeaderM_PatientID), '$.M_PatientHP')) IS NULL OR JSON_UNQUOTE(JSON_EXTRACT(fn_get_patient_atribute(T_OrderHeaderM_PatientID), '$.M_PatientHP')) = '' OR JSON_UNQUOTE(JSON_EXTRACT(fn_get_patient_atribute(T_OrderHeaderM_PatientID), '$.M_PatientHP')) = 'null','---',JSON_UNQUOTE(JSON_EXTRACT(fn_get_patient_atribute(T_OrderHeaderM_PatientID), '$.M_PatientHP')))
WHEN M_DeliverySource = 'DOCTOR' THEN IF(M_DoctorHP IS NULL OR  M_DoctorHP = '' OR M_DoctorHP = 'null','---',M_DoctorHP)
END as hp,
Tx_WhatsappNote,
IF(SUM(IF(IFNULL(Tx_WhatsappDetailIsSend,'N') = 'N',0,1)) = 0,'N',IF(COUNT(Tx_WhatsappDetailID) = SUM(IF(Tx_WhatsappDetailIsSend = 'N',0,1)),'Y','P')) as K,
IF(SUM(IF(IFNULL(Tx_WhatsappDetailIsReceive,'N') = 'N',0,1)) = 0,'N',IF(COUNT(Tx_WhatsappDetailID) = SUM(IF(Tx_WhatsappDetailIsReceive = 'N',0,1)),'Y','P')) as T,
IF(SUM(IF(IFNULL(Tx_WhatsappDetailIsRead,'N') = 'N',0,1)) = 0,'N',IF(COUNT(Tx_WhatsappDetailID) = SUM(IF(Tx_WhatsappDetailIsRead = 'N',0,1)),'Y','P')) as B,
GROUP_CONCAT(DISTINCT Tx_WhatsappDetailMessage SEPARATOR ' ,') as statusprovider
         FROM t_orderheader 
         JOIN t_orderheaderaddon ON T_OrderHeaderAddOnT_OrderHeaderID = T_OrderHeaderID
         join t_orderdelivery on T_OrderHeaderID = T_OrderDeliveryT_OrderHeaderID and T_OrderDeliveryIsActive = 'Y' and T_OrderDeliveryM_DeliveryTypeID = 4
         join m_delivery ON M_DeliveryID = T_OrderDeliveryM_DeliveryID
         JOIN m_doctor ON T_OrderHeaderSenderM_DoctorID = M_DoctorID
         join last_statuspayment ON Last_StatusPaymentT_OrderHeaderID = T_OrderHeaderID AND Last_StatusPaymentIsActive = 'Y'
         JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID 
         join group_resultdetail on Group_ResultDetailT_TestID = T_OrderDetailT_TestID
         and T_OrderDetailIsActive = 'Y' and Group_ResultDetailIsActive = 'Y'
         and T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
         join group_result on Group_ResultDetailGroup_ResultID = Group_ResultID
         LEFT JOIN tx_whatsapp ON Tx_WhatsappT_OrderHeaderID = T_OrderHeaderID AND Tx_WhatsappGroup_ResultID = Group_ResultID AND Tx_WhatsappM_DeliveryID = M_DeliveryID AND Tx_WhatsappIsActive = 'Y'
         LEFT JOIN t_email_nonlab ON T_EmailNonLabT_OrderHeaderID = T_OrderDetailT_OrderHeaderID  AND
         T_EmailNonLabType LIKE CONCAT('%',REPLACE(Group_ResultName, 'Elektromedik', 'electromedis'),'%')
         LEFT JOIN tx_whatsappdetail ON Tx_WhatsappDetailTx_WhatsappID = Tx_WhatsappID
         JOIN t_orderpromise ON T_OrderPromiseT_OrderHeaderID = T_OrderHeaderID
      JOIN result_processtooffice ON Result_ProcessToOfficeT_OrderPromiseID = T_OrderPromiseID AND 
      T_OrderDeliveryID = Result_ProcessToOfficeT_OrderDeliveryID AND Result_ProcessToOfficeStatus = 'S'
         $sql_where
         GROUP BY T_OrderHeaderID, M_DeliveryID, T_OrderDeliveryDestination
         UNION
      SELECT
      T_OrderHeaderID,	
      Tx_WhatsappGroup_ResultID,
CONCAT(M_DoctorPrefix, ' ',M_DoctorName) as M_DoctorName,	
JSON_UNQUOTE(JSON_EXTRACT(fn_get_patient_atribute(T_OrderHeaderM_PatientID), '$.patient_fullname')) as M_PatientName,
T_OrderHeaderLabNumber,
'' as url_print,
'' as temail,
0 as Group_ResultID,
'' as Group_ResultName,
IF(Last_StatusPaymentIsLunas = 'N','Belum Lunas','Sudah Lunas') as Last_StatusPaymentIsLunas,
IF(IFNULL(Tx_WhatsappIsSent,'N') = 'N','Pending','Terkirim') as wastatus,
'Greeting' as validasi,
M_DeliveryName,
M_DeliverySource,
T_OrderDeliveryDestination,
CASE  
WHEN M_DeliverySource = 'PATIENT' THEN IF(JSON_UNQUOTE(JSON_EXTRACT(fn_get_patient_atribute(T_OrderHeaderM_PatientID), '$.M_PatientHP')) IS NULL OR JSON_UNQUOTE(JSON_EXTRACT(fn_get_patient_atribute(T_OrderHeaderM_PatientID), '$.M_PatientHP')) = '' OR JSON_UNQUOTE(JSON_EXTRACT(fn_get_patient_atribute(T_OrderHeaderM_PatientID), '$.M_PatientHP')) = 'null','---',JSON_UNQUOTE(JSON_EXTRACT(fn_get_patient_atribute(T_OrderHeaderM_PatientID), '$.M_PatientHP')))
WHEN M_DeliverySource = 'DOCTOR' THEN IF(M_DoctorHP IS NULL OR  M_DoctorHP = '' OR M_DoctorHP = 'null','---',M_DoctorHP)
END as hp,
GROUP_CONCAT(DISTINCT Tx_WhatsappDetailMessage separator '\n') as Tx_WhatsappNote,
IF(SUM(IF(IFNULL(Tx_WhatsappDetailIsSend,'N') = 'N',0,1)) = 0,'N',IF(COUNT(Tx_WhatsappDetailID) = SUM(IF(Tx_WhatsappDetailIsSend = 'N',0,1)),'Y','P')) as K,
IF(SUM(IF(IFNULL(Tx_WhatsappDetailIsReceive,'N') = 'N',0,1)) = 0,'N',IF(COUNT(Tx_WhatsappDetailID) = SUM(IF(Tx_WhatsappDetailIsReceive = 'N',0,1)),'Y','P')) as T,
IF(SUM(IF(IFNULL(Tx_WhatsappDetailIsRead,'N') = 'N',0,1)) = 0,'N',IF(COUNT(Tx_WhatsappDetailID) = SUM(IF(Tx_WhatsappDetailIsRead = 'N',0,1)),'Y','P')) as B,
GROUP_CONCAT(DISTINCT Tx_WhatsappDetailMessage SEPARATOR ' ,') as statusprovider
      FROM t_orderheader 
      JOIN t_orderheaderaddon ON T_OrderHeaderAddOnT_OrderHeaderID = T_OrderHeaderID
      join t_orderdelivery on T_OrderHeaderID = T_OrderDeliveryT_OrderHeaderID and T_OrderDeliveryIsActive = 'Y' and T_OrderDeliveryM_DeliveryTypeID = 4
      join m_delivery ON M_DeliveryID = T_OrderDeliveryM_DeliveryID
      JOIN m_doctor ON T_OrderHeaderSenderM_DoctorID = M_DoctorID
      join last_statuspayment ON Last_StatusPaymentT_OrderHeaderID = T_OrderHeaderID AND Last_StatusPaymentIsActive = 'Y'
      LEFT JOIN tx_whatsapp ON Tx_WhatsappT_OrderHeaderID = T_OrderHeaderID AND Tx_WhatsappIsActive = 'Y' 
      LEFT JOIN tx_whatsappdetail ON Tx_WhatsappDetailTx_WhatsappID = Tx_WhatsappID
      $sql_where AND Tx_WhatsappGroup_ResultID = -1 AND Tx_WhatsappM_DeliveryID = -1
      GROUP BY M_DeliveryID,T_OrderHeaderID
         ORDER BY T_OrderHeaderID DESC
         limit $number_limit offset $number_offset";

      $query = $this->db_onedev->query($sql, $sql_param);
      //echo $this->db_onedev->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, "total_filter" => $tot_page2, "records" => $rows, "sql" => $this->db_onedev->last_query());
      $this->sys_ok($result);
      exit;
   }
   public function searchconfirm()
   {
      $prm = $this->sys_input;
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $code = $prm["code"];
      $nama = $prm["name"];
      $date = $prm['date'];

      $e = explode('+', $prm['name']);
      if (isset($e[0]))
         $nama = "%{$e[0]}%";
      if (isset($e[1]))
         $alamat = "%{$e[1]}%";
      if (isset($e[2]))
         $nohp = "%{$e[2]}%";
      // echo $norm;

      $sql_where = "";
      $sql_param = array();
      if ($code != "") {
         if ($sql_where != "") {
            $sql_where .= " and ";
         }
         $sql_where .= " M_DoctorOldCode like ?";
         $sql_param[] = "$code";
         //$prm['current_page'] = 1;
      }
      if ($nama != "") {
         if ($sql_where != "") {
            $sql_where .= " and ";
         }
         $sql_where .= " M_DoctorName like ?";
         $sql_param[] = "$nama";
         //$prm['current_page'] = 1;
      }
      if ($alamat != "") {
         if ($sql_where != "") {
            $sql_where .= " and ";
         }
         $sql_where .= " CONCAT(M_DoctorAddressNote, ': ',M_DoctorAddressDescription) like ?";
         $sql_param[] = "$alamat";
         //$prm['current_page'] = 1;
      }
      if ($nohp != "") {
         if ($sql_where != "") {
            $sql_where .= " and ";
         }
         $sql_where .= " M_DoctorHP like ?";
         $sql_param[] = "$nohp";
         //$prm['current_page'] = 1;
      }
      if ($sql_where != "") $sql_where .= " and ";

      // Order masih dalam status registrasi
      $sql_where .= " M_DoctorIsMarketingConfirm =  'Y' AND M_DoctorIsActive = 'Y'";
      if ($sql_where != "") {
         $sql_where = " where $sql_where";
         //$prm['current_page'] = 1;
      }

      $number_limit = 10;
      $number_offset = ($prm['current_page'] - 1) * $number_limit;

      $sql = "	SELECT count(*) as total, CONCAT(M_DoctorAddressNote, ': ',M_DoctorAddressDescription) as doctor_address
      
         FROM m_doctor
         JOIN m_sex ON M_DoctorM_SexID = M_SexID
         LEFT JOIN m_religion ON M_DoctorM_ReligionID = M_ReligionID
         LEFT JOIN m_staff ON M_DoctorM_StaffID = M_StaffID
         left join m_doctoraddress ON M_DoctorOldCode = M_DoctorAddressM_DoctorOldCode AND M_DoctorAddressIsActive = 'Y'
         $sql_where
";
      $query = $this->db_onedev->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_doctor count", $this->db_onedev);
         exit;
      }

      $doctor_field = "
M_DoctorID,
M_DoctorOldCode,
M_DoctorCode	,
M_DoctorPrefix	 ,
M_DoctorPrefix2	 ,
M_DoctorName	 ,
M_DoctorSufix	 ,
M_DoctorSufix2	 ,
M_DoctorSufix3	 ,
M_DoctorM_SexID	 ,
M_DoctorM_ReligionID,
M_DoctorM_StaffID,
IFNULL(M_DoctorEmail,'') as M_DoctorEmail,
M_DoctorHP	,
M_DoctorNote,
M_DoctorPhone	,
M_DoctorIsMarketingConfirm,
ifnull(M_DoctorPjIsPJ,'N') M_DoctorIsPJ,
ifnull(M_DoctorPjIsDefaultPJ,'N') M_DoctorIsDefaultPJ	,
M_DoctorM_SpecialID	,
ifnull(M_DoctorPjIsClinic,'N') M_DoctorIsClinic	,
ifnull(M_DoctorPjIsDefault,'N') M_DoctorIsDefault	,
M_DoctorEmailIsDefault,
M_DoctorIsDefaultMcu,
M_DoctorCreated	,
M_DoctorLastUpdated,
M_DoctorIsActive,
M_DoctorReportCode	,
M_DoctorPrivateRequest,
M_DoctorM_UserID	,
M_DoctorCreated,
";
      $sql =    "SELECT $doctor_field
         CONCAT(IFNULL(M_DoctorPrefix,''),IFNULL(M_DoctorPrefix2,''),' ',M_DoctorName,' ',IFNULL(M_DoctorSufix,''),IFNULL(M_DoctorSufix2,''),IFNULL(M_DoctorSufix3,'')) as doctor_fullname,
         M_SexName,
         M_ReligionName,
         M_StaffName,
         GROUP_CONCAT(CONCAT(M_DoctorAddressNote, ': ',M_DoctorAddressDescription) SEPARATOR '\r\n') as M_DoctorAddressNote,
         GROUP_CONCAT(CONCAT(M_DoctorAddressNote, ': ',M_DoctorAddressDescription) SEPARATOR '\r\n') as doctor_address,
         'Telah dikonfirmasi marketing' as status
         FROM m_doctor
         JOIN m_sex ON M_DoctorM_SexID = M_SexID
         left join m_doctorpj on M_DoctorID = M_DoctorPjM_DoctorID 
         left JOIN m_religion ON M_DoctorM_ReligionID = M_ReligionID
         left JOIN m_staff ON M_DoctorM_StaffID = M_StaffID
         left join m_doctoraddress ON M_DoctorOldCode = M_DoctorAddressM_DoctorOldCode AND M_DoctorAddressIsActive = 'Y'
         $sql_where
         GROUP BY M_DoctorID
         ORDER BY M_DoctorName ASC
         limit $number_limit offset $number_offset";

      $query = $this->db_onedev->query($sql, $sql_param);
      // echo $this->db_onedev->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_onedev->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_onedev->query($query)->result_array();
      $query = "	SELECT *
         FROM m_religion
         WHERE
         M_ReligionIsActive = 'Y'
";
      //echo $query;
      $rows['religions'] = $this->db_onedev->query($query)->result_array();
      $query = "	
		SELECT 0 as M_StaffID, 'Semua' as M_StaffName
		UNION
		SELECT M_StaffID, M_StaffName
         FROM m_staff
         WHERE
         M_StaffIsActive = 'Y' and M_StaffM_PositionID = '2'

";
      //echo $query;
      $rows['staffs'] = $this->db_onedev->query($query)->result_array();
      $query = "	
      SELECT '' as M_BranchCode, 'Semua' as M_BranchName
      UNION
      SELECT M_BranchCode, M_BranchName
   FROM m_branch
   WHERE
   M_BranchIsActive = 'Y'

";
      //echo $query;
      $rows['branchs'] = $this->db_onedev->query($query)->result_array();

      $query = "	SELECT *
         FROM nat_jpa
         WHERE
         Nat_JpaIsActive = 'Y'
";
      //echo $query;
      $rows['jpas'] = $this->db_onedev->query($query)->result_array();


      $result = array(
         "total" => count($rows),
         "records" => $rows,
      );
      $this->sys_ok($result);
      exit;
   }
   function getnewcode()
   {
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $sql = $this->db_onedev->query("SELECT `fn_numbering`('DOCTOR') as code")->row();
      $newcode = $sql->code;

      $rows = [];
      $query = "SELECT `fn_numbering`('DOCTOR') as code
";
      //echo $query;
      $rows['code'] = $this->db_onedev->query($query)->result_array();


      $result = array(
         "total" => 1,
         "records" => $rows,
         "newcode" => $newcode
      );
      $this->sys_ok($result);
      exit;
   }
   function searchcity()
   {
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $prm = $this->sys_input;

      $max_rst = 12;
      $tot_count = 0;

      $q = [
         'search'     => '%'
      ];

      if ($prm['search'] != '') {
         $q['search'] = "%{$prm['search']}%";
      }

      // QUERY TOTAL
      $sql = "SELECT count(*) as total
         FROM m_city
         WHERE
         M_CityName like ?
         AND M_CityIsActive = 'Y'";
      $query = $this->db_onedev->query($sql, $q['search']);
      //echo $query;
      if ($query) {
         $tot_count = $query->result_array()[0]["total"];
      } else {
         $this->sys_error_db("m_city count", $this->db_onedev);
         exit;
      }

      $sql = "
                        SELECT *
                        FROM m_city
                        WHERE
                        M_CityName like ?
                        AND M_CityIsActive = 'Y'
                        ORDER BY M_CityName DESC
                  ";
      $query = $this->db_onedev->query($sql, array($q['search']));

      if ($query) {
         $rows = $query->result_array();
         //echo $this->db_onedev->last_query();
         $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
         $this->sys_ok($result);
      } else {
         $this->sys_error_db("m_city rows", $this->db_onedev);
         exit;
      }
   }

   function getdistrict()
   {
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $prm = $this->sys_input;
      $query = "	SELECT *
         FROM m_district
         WHERE
         M_DistrictIsActive = 'Y' AND M_DistrictM_CityID = ?
";
      //echo $query;
      $rows = $this->db_onedev->query($query, array($prm['id']))->result_array();

      $result = array(
         "total" => count($rows),
         "records" => $rows,
      );
      $this->sys_ok($result);
      exit;
   }

   function getkelurahan()
   {
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $prm = $this->sys_input;
      $query = "	SELECT *
         FROM m_kelurahan
         WHERE
         M_KelurahanIsActive = 'Y' AND M_KelurahanM_DistrictID = ?
";
      //echo $query;
      $rows = $this->db_onedev->query($query, array($prm['id']))->result_array();

      $result = array(
         "total" => count($rows),
         "records" => $rows,
      );
      $this->sys_ok($result);
      exit;
   }

   function getjpa()
   {
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $prm = $this->sys_input;
      $query = "	SELECT *
         FROM nat_jpa
         WHERE
         Nat_JpaIsActive = 'Y'
";
      //echo $query;
      $rows = $this->db_onedev->query($query)->result_array();

      //  $rows['jpas'] = $this->db_onedev->query($query)->result_array();

      $result = array(
         "total" => count($rows),
         "records" => $rows,
      );
      $this->sys_ok($result);
      exit;
   }


   function save()
   {
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $prm = $this->sys_input;
      $userid = $this->sys_user["M_UserID"];

      // ambil data lama
      $sql = "select * from tx_whatsapp where Tx_WhatsappID = ?";
      $qry = $this->db_onedev->query($sql, array($prm["id"]));
      $rows = $qry->result_array();
      $old_doctor = array();
      if (count($rows) > 0) $old_doctor = $rows[0];

      $query = "UPDATE tx_whatsapp SET
         Tx_WhatsappUpdated = now(),
         Tx_WhatsappUserID = '{$userid}',
         Tx_WhatsappIsSent = 'Y'
         WHERE
         Tx_WhatsappID = '{$prm['id']}'
";
      //echo $query;
      $rows = $this->db_onedev->query($query);
      $this->sys_ok($result);
      exit;
   }

   function newdoctor()
   {
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $userid = $this->sys_user["M_UserID"];
      $prm = $this->sys_input;
      $ispj = $prm['M_DoctorIsPJ'];
      $isdefaultpj = $prm['M_DoctorIsDefaultPJ'];
      $isdefault = $prm['M_DoctorIsDefault'];
      $isclinic = $prm['M_DoctorIsClinic'];
      $query = "INSERT INTO m_doctor (
         M_DoctorPrefix,
         M_DoctorPrefix2,
         M_DoctorName,
         M_DoctorSufix,
         M_DoctorSufix2,
         M_DoctorSufix3,
         M_DoctorM_SexID,
         M_DoctorM_ReligionID,
         M_DoctorM_StaffID,
         M_DoctorEmail,
         M_DoctorHP,
         M_DoctorNote,
         M_DoctorPhone,
         M_DoctorIsMarketingConfirm,
         M_DoctorIsPJ,
         M_DoctorIsDefaultPJ,
         M_DoctorIsClinic,
         M_DoctorIsDefault,
		 M_DoctorEmailIsDefault,
         M_DoctorM_UserID
      )
      VALUES(
         '{$prm['M_DoctorPrefix']}',
         '{$prm['M_DoctorPrefix2']}',
         '{$prm['M_DoctorName']}',
         '{$prm['M_DoctorSufix']}',
         '{$prm['M_DoctorSufix2']}',
         '{$prm['M_DoctorSufix3']}',
         '{$prm['M_DoctorM_SexID']}',
         '{$prm['M_DoctorM_ReligionID']}',
         '{$prm['M_DoctorM_StaffID']}',
         '{$prm['M_DoctorEmail']}',
         '{$prm['M_DoctorHP']}',
         '{$prm['M_DoctorNote']}',
         '{$prm['M_DoctorPhone']}',
         '{$prm['M_DoctorIsMarketingConfirm']}',
         '{$prm['M_DoctorIsPJ']}',
         '{$prm['M_DoctorIsDefaultPJ']}',
         '{$prm['M_DoctorIsClinic']}',
         '{$prm['M_DoctorIsDefault']}',
		 '{$prm['M_DoctorEmailIsDefault']}',
         $userid
      )
";
      //echo $query;
      $rows = $this->db_onedev->query($query);
      $last_id = $this->db_onedev->insert_id();
      if ($rows) {
         if ($isdefault == 'Y') {
            $querydefault = "UPDATE m_doctorpj SET
               M_DoctorPjIsDefault = 'N' WHERE M_DoctorPjIsDefault = 'Y'
";
            $rows = $this->db_onedev->query($querydefault);
         }
         if ($isdefaultpj == 'Y') {
            $querydefault = "UPDATE m_doctorpj SET
               M_DoctorPjIsDefaultPJ = 'N' WHERE M_DoctorPjIsDefaultPJ = 'Y'
";
            $rows = $this->db_onedev->query($querydefault);
         }
         if ($ispj == 'Y' || $isdefaultpj == 'Y' || $isdefault == 'Y' || $isclinic === 'Y') {
            $querypj = "INSERT INTO m_doctorpj (
               M_DoctorPjM_DoctorID,
               M_DoctorPjIsPJ,
               M_DoctorPjIsDefaultPJ,
               M_DoctorPjIsClinic,
               M_DoctorPjIsDefault,
               M_DoctorPjCreated
            )
            VALUES(
               '{$last_id}',
               '{$prm['M_DoctorIsPJ']}',
               '{$prm['M_DoctorIsDefaultPJ']}',
               '{$prm['M_DoctorIsClinic']}',
               '{$prm['M_DoctorIsDefault']}',
               NOW()
            )
";
            $rows = $this->db_onedev->query($querypj);
         }
      }
      $result = array(
         "total" => 1,
         "records" => array('status' => 'OK'),
         "id" => $last_id
      );
      //sipe tambah log doctor
      $prm["M_DoctorID"] = $last_id;
      $prm["M_DoctorM_UserID"] = $userid;
      $d_doctor = json_encode($prm);
      $this->db_onedev->query("call one_log.log_me('DOCTOR','DOCTOR_ADD','{$d_doctor}',$userid)");
      $this->sys_ok($result);
      exit;
   }

   function deletedoctor()
   {
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $prm = $this->sys_input;
      // sipe nambah ambil userid
      $userid = $this->sys_user["M_UserID"];

      $query = "UPDATE m_doctor SET
         M_DoctorIsActive = 'N'
         WHERE
         M_DoctorID = '{$prm['M_DoctorID']}'
";
      //echo $query;
      $rows = $this->db_onedev->query($query);

      $result = array(
         "total" => 1,
         "records" => array('status' => 'OK')
      );
      //adi tambah log doctor
      $prm["M_DoctorM_UserID"] = $userid;
      $d_doctor = json_encode($prm);
      $this->db_onedev->query("call one_log.log_me('DOCTOR','DOCTOR_DELETE','{$d_doctor}',$userid)");
      $this->sys_ok($result);
      exit;
   }

   function getaddress()
   {
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $prm = $this->sys_input;
      $query = "	SELECT m_doctoraddress.*,
         M_KelurahanName,
         M_DistrictID,
         M_DistrictName,
         M_CityID,
         M_CityName,Nat_JpaName,
         '' as action
         FROM m_doctoraddress
         JOIN m_kelurahan ON M_DoctorAddressM_KelurahanID = M_KelurahanID
         JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID
         JOIN m_city ON M_DistrictM_CityID = M_CityID
         left join nat_jpa on M_DoctorAddressNat_JpaID = Nat_JpaID
         WHERE
         M_DoctorAddressIsActive = 'Y' AND M_DoctorAddressM_DoctorID = ?
";
      //echo $query;
      $rows = $this->db_onedev->query($query, array($prm['id']))->result_array();
      if ($rows) {
         foreach ($rows as $k => $v) {
            $rows[$k]['action'] = '<v-icon color="error" @click="deleteAddress(props.item)">delete</v-icon>';
            $rows[$k]['action'] .= '<v-icon color="primary" @click="deleteAddress(props.item)">edit</v-icon>';
         }
      }
      $result = array(
         "total" => count($rows),
         "records" => $rows,
      );
      $this->sys_ok($result);
      exit;
   }

   function savenewaddress()
   {
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $userid = $this->sys_user["M_UserID"];
      $prm = $this->sys_input;
      $count_addrs = $this->db_onedev->query("SELECT COUNT(*) as countx FROM m_doctoraddress WHERE M_DoctorAddressM_DoctorID = '{$prm['M_DoctorAddressM_DoctorID']}' AND M_DoctorAddressIsActive = 'Y'")->row()->countx;

      //echo $this->db_onedev->last_query();
      if ($count_addrs == 0) {
         $prm['M_DoctorAddressNote'] = 'Utama';
      } else {
         $count_addrs_utama = $this->db_onedev->query("SELECT COUNT(*) as countx FROM m_doctoraddress WHERE M_DoctorAddressM_DoctorID = '{$prm['M_DoctorAddressM_DoctorID']}' AND M_DoctorAddressNote = 'Utama' AND M_DoctorAddressIsActive = 'Y'")->row()->countx;
         if ($count_addrs_utama > 0 && strtolower($prm['M_DoctorAddressNote']) == 'utama') {
            $rx = date('YmdHis');
            $prm['M_DoctorAddressNote'] = 'Utama_' . $rx;
         }
      }
      $query = "INSERT INTO m_doctoraddress (
         M_DoctorAddressM_DoctorID,
         M_DoctorAddressNote,
         M_DoctorAddressDescription,
         M_DoctorAddressM_KelurahanID,
         M_DoctorAddressNat_JpaID,
         M_DoctorAddressCreated
      )
      VALUES(
         '{$prm['M_DoctorAddressM_DoctorID']}',
         '{$prm['M_DoctorAddressNote']}',
         '{$prm['M_DoctorAddressDescription']}',
         '{$prm['M_DoctorAddressM_KelurahanID']}',
         '{$prm['M_DoctorAddressNat_JpaID']}',
         NOW()
      )
";
      //echo $query;
      $rows = $this->db_onedev->query($query);

      $result = array(
         "total" => 1,
         "records" => array('status' => 'OK')
      );
      //adi tambah log doctor
      $prm["M_DoctorM_UserID"] = $userid;
      $d_doctor = json_encode($prm);
      $this->db_onedev->query("call one_log.log_me('DOCTOR','DOCTOR_ADDR','{$d_doctor}',$userid)");
      $this->sys_ok($result);
      exit;
   }

   function saveeditaddress()
   {
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $userid = $this->sys_user["M_UserID"];
      $prm = $this->sys_input;

      $query = "UPDATE m_doctoraddress SET
         M_DoctorAddressM_DoctorID = '{$prm['M_DoctorAddressM_DoctorID']}',
         M_DoctorAddressNote = '{$prm['M_DoctorAddressNote']}',
         M_DoctorAddressDescription = '{$prm['M_DoctorAddressDescription']}',
         M_DoctorAddressM_KelurahanID = '{$prm['M_DoctorAddressM_KelurahanID']}',
         M_DoctorAddressNat_JpaID = '{$prm['M_DoctorAddressNat_JpaID']}'
         WHERE
         M_DoctorAddressID = '{$prm['M_DoctorAddressID']}'
";
      // echo $query;
      $rows = $this->db_onedev->query($query);

      $result = array(
         "total" => 1,
         "records" => array('status' => 'OK')
      );
      //adi tambah log doctor
      $prm["M_DoctorM_UserID"] = $userid;
      $d_doctor = json_encode($prm);
      $this->db_onedev->query("call one_log.log_me('DOCTOR','DOCTOR_ADDR_EDIT','{$d_doctor}',$userid)");
      $this->sys_ok($result);
      exit;
   }

   function deleteaddress()
   {
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $userid = $this->sys_user["M_UserID"];
      $prm = $this->sys_input;

      $query = "UPDATE m_doctoraddress SET
         M_DoctorAddressIsActive = 'N'
         WHERE
         M_DoctorAddressID = '{$prm['M_DoctorAddressID']}'
";
      //echo $query;
      $rows = $this->db_onedev->query($query);

      $result = array(
         "total" => 1,
         "records" => array('status' => 'OK')
      );
      //adi tambah log doctor
      $prm["M_DoctorM_UserID"] = $userid;
      $d_doctor = json_encode($prm);
      $this->db_onedev->query("call one_log.log_me('DOCTOR','DOCTOR_ADDR_DELETE','{$d_doctor}',$userid)");
      $this->sys_ok($result);
      exit;
   }
}