<?php

class Re_px extends MY_Controller
{
   var $db_smartone;

   public function index()
   {
      echo "RE Px API";
   }

   public function __construct()
   {
      parent::__construct();

      $this->db_smartone = $this->load->database("onedev", true);
   }

   public function search()
   {
      $prm = $this->sys_input;
      $max_rst = 99;

      $id = $prm["order_id"];
      $lang = $this->lang_default_code;
      $lang_id = isset($prm['lang_id']) ? $prm['lang_id'] : '0';

      if ($lang_id != 0)
      {
         $l = $this->db_smartone->where("Nat_LangID", $lang_id)->get("nat_lang")->row();
         $lang = $l->Nat_LangCode;
      }

      $tot_count = 0;
      $lq = "";
      $group_id = 0;
      if (isset($prm['group_id']))
         $group_id = $prm['group_id'];

      if ($group_id != 0)
      {
	  $sql = "select distinct T_WorklistDetailNat_TestID Nat_TestID
		from t_worklistdetailv2
		where T_WorklistDetailT_WorklistID = {$group_id}";
          $qry = $this->db_smartone->query($sql);
          $rows = $qry->result_array();
          $nat_test_ids = "0";
          foreach($rows as $r) {
             $nat_test_ids .= ", " . $r["Nat_TestID"];
          } 
          $q_group = "  ( $nat_test_ids ) ";

         $sql = "
            SELECT a1.*, cOUNT(a2.T_OrderDetailID) level 
            FROM (
            SELECT IFNULL(y.T_OrderDetailT_TestID, x.T_OrderDetailT_TestID) t_testid, 
            IFNULL(y.T_OrderDetailT_TestName, y.T_OrderDetailT_TestName) t_testname,
            y.T_OrderDetailResult result, y.T_OrderDetailResult result_old, y.T_OrderDetailNote note, y.T_OrderDetailNote note_old, 
            y.T_OrderDetailID id,
            y.T_OrderDetailT_TestIsResult is_result, a.T_TestIsQuantitative is_quantitative,
            y.T_OrderDetailResultFlag result_flag,
            y.T_OrderDetailNat_NormalValueID normal_id,
            y.T_OrderDetailNormalValueNote normal_note, y.T_OrderDetailNat_UnitName unit_name,
            y.T_OrderdetailNat_MethodeID methode_id, y.T_OrderdetailNat_MethodeName methode_name,
            a.T_TestNat_TestID nattest_id, a.T_TestID tx_id, count(T_ResultInstrumentID) as result_instrument_n,
            y.T_OrderDetailVerification, T_OrderSampleReceive sample_receive,
            T_OrderSampleProcessing sample_processing, T_OrderSampleWorklistReceive sample_worklist_receive,
            IF(T_OrderDetailAddOnPreAnalytic = 'Y', T_OrderDetailAddOnPreAnalytic, PreAnalyticIsOk) pre_analytic,
            T_OrderDetailAddOnPreAnalytic,
            T_OrderHeaderID order_id, y.T_OrderDetailT_TestSasCode test_sas_code,
            tx.T_TestSasCode,
            T_OrderDetailAddOnResample `resample`, T_OrderDetailAddOnResampleStatus resample_status,
            T_OrderDetailAddOnRef ref, y.T_OrderDetailVerification verification, y.T_OrderDetailValidation validation,
            T_OrderPromiseDateTime, fn_process_group_result(x.T_OrderDetailT_TestID) ResultGroupName
            FROM t_orderdetail x
            left join t_orderpromise  on x.T_OrderDetailT_OrderPromiseID = T_OrderPromiseID 
            JOIN t_test tx ON x.T_OrderDetailT_TestID = T_TestID
            JOIN t_test tcheck on  tcheck.T_TestNat_TestID in {$q_group} 
            and ( tx.T_TestID  = tcheck.T_TestID  or tx.T_TestSasCode like concat(tcheck.T_TestSasCode,'%'))
            JOIN documentation_group_detail ON DocumentationGroupDetailNat_SubGroupID = tx.T_TestNat_SubGroupID
            JOIN documentation_group ON DocumentationGroupDetailDocumentationGroupID = DocumentationGroupID
            AND DocumentationGroupName = 'lab'
            LEFT JOIN t_orderdetail y ON x.T_OrderDetailT_TestSasCode LIKE CONCAT(y.T_OrderDetailT_TestSasCode, '%')
            AND x.T_OrderDetailT_OrderHeaderID = y.T_OrderDetailT_OrderHeaderID
            AND y.T_OrderDetailIsActive = 'Y'
            LEFT JOIN t_orderdetailaddon ON x.T_OrderDetailID = T_OrderDetailAddOnT_OrderDetailID
            JOIN t_orderheader ON y.T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
            LEFT JOIN t_test a ON y.T_OrderDetailT_TestID = a.T_TestID
            AND a.T_TestIsNonLab = ''
            LEFT JOIN (SELECT T_OrderSampleT_SampleTypeID, T_OrderSampleReceive, T_OrderSampleProcessing, T_OrderSampleWorklistReceive
            FROM t_ordersample WHERE T_OrderSampleT_OrderHeaderID = ?
            AND T_OrderSampleIsactive = 'Y'
            GROUP BY T_OrderSampleT_SampleTypeID) os ON a.T_TestT_SampleTypeID = os.T_OrderSampleT_SampleTypeID

            LEFT JOIN t_resultinstrument ON T_ResultInstrumentT_OrderDetailID = x.T_OrderDetailID
            LEFT JOIN pre_analytic ON PreAnalyticNat_TestID = a.T_TestNat_TestID AND PreAnalyticDate = date(now()) and PreAnalyticIsActive = 'Y'
            WHERE x.T_OrderDetailT_OrderHeaderID = ?
            AND x.T_OrderDetailIsActive = 'Y'
            GROUP BY y.T_OrderDetailT_TestID
            ORDER BY y.T_OrderDetailT_TestSasCode ASC
            ) a1

            LEFT JOIN t_orderdetail a2 ON a2.T_OrderDetailT_OrderHeaderID = a1.order_id
            AND a2.T_OrderDetailIsActive = 'Y'
            AND a1.test_sas_code LIKE CONCAT(a2.T_OrderDetailT_testSasCode, '%')
            GROUP BY a1.t_testid
            order by a1.T_TestSasCode
";
         $query = $this->db_smartone->query($sql, [$id, $id]);
          $lq = $this->db_smartone->last_query();
      }
      else
      {
         $sql = "
            SELECT a1.*, cOUNT(a2.T_OrderDetailID) level
            FROM (
            SELECT IFNULL(y.T_OrderDetailT_TestID, x.T_OrderDetailT_TestID) t_testid, 
            IFNULL(y.T_OrderDetailT_TestName, y.T_OrderDetailT_TestName) t_testname,
            y.T_OrderDetailResult result, y.T_OrderDetailResult result_old, y.T_OrderDetailNote note, y.T_OrderDetailNote note_old, 
            y.T_OrderDetailID id,
            y.T_OrderDetailT_TestIsResult is_result, a.T_TestIsQuantitative is_quantitative,
            y.T_OrderDetailResultFlag result_flag,
            y.T_OrderDetailNat_NormalValueID normal_id,
            y.T_OrderDetailNormalValueNote normal_note, y.T_OrderDetailNat_UnitName unit_name,
            y.T_OrderdetailNat_MethodeID methode_id, y.T_OrderdetailNat_MethodeName methode_name,
            a.T_TestNat_TestID nattest_id, a.T_TestID tx_id, count(T_ResultInstrumentID) as result_instrument_n,
            y.T_OrderDetailVerification, T_OrderSampleReceive sample_receive,
            T_OrderSampleProcessing sample_processing, T_OrderSampleWorklistReceive sample_worklist_receive,
            IF(T_OrderDetailAddOnPreAnalytic = 'Y', T_OrderDetailAddOnPreAnalytic, PreAnalyticIsOk) pre_analytic,
            T_OrderDetailAddOnPreAnalytic,
            T_OrderHeaderID order_id, y.T_OrderDetailT_TestSasCode test_sas_code,
            tx.T_TestSasCode,
            T_OrderDetailAddOnResample `resample`, T_OrderDetailAddOnResampleStatus resample_status,
            T_OrderDetailAddOnRef ref, y.T_OrderDetailVerification verification, y.T_OrderDetailValidation validation,
            T_OrderPromiseDateTime , fn_process_group_result(x.T_OrderDetailT_TestID) ResultGroupName
            FROM t_orderdetail x
            left join t_orderpromise  on x.T_OrderDetailT_OrderPromiseID = T_OrderPromiseID 
            JOIN t_test tx ON x.T_OrderDetailT_TestID = T_TestID
            JOIN documentation_group_detail ON DocumentationGroupDetailNat_SubGroupID = tx.T_TestNat_SubGroupID
            JOIN documentation_group ON DocumentationGroupDetailDocumentationGroupID = DocumentationGroupID
            AND DocumentationGroupName = 'lab'
            LEFT JOIN t_orderdetail y ON x.T_OrderDetailT_TestSasCode LIKE CONCAT(y.T_OrderDetailT_TestSasCode, '%')
            AND x.T_OrderDetailT_OrderHeaderID = y.T_OrderDetailT_OrderHeaderID
            AND y.T_OrderDetailIsActive = 'Y'

            LEFT JOIN t_orderdetailaddon ON x.T_OrderDetailID = T_OrderDetailAddOnT_OrderDetailID

            JOIN t_orderheader ON y.T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
            LEFT JOIN t_test a ON y.T_OrderDetailT_TestID = a.T_TestID
            AND a.T_TestIsNonLab = ''
            LEFT JOIN (SELECT T_OrderSampleT_SampleTypeID, T_OrderSampleReceive, T_OrderSampleProcessing, T_OrderSampleWorklistReceive
            FROM t_ordersample WHERE T_OrderSampleT_OrderHeaderID = ?
            AND T_OrderSampleIsactive = 'Y'
            GROUP BY T_OrderSampleT_SampleTypeID) os ON a.T_TestT_SampleTypeID = os.T_OrderSampleT_SampleTypeID

            LEFT JOIN t_resultinstrument ON T_ResultInstrumentT_OrderDetailID = x.T_OrderDetailID
            LEFT JOIN pre_analytic ON PreAnalyticNat_TestID = a.T_TestNat_TestID AND PreAnalyticDate = date(now()) and PreAnalyticIsActive = 'Y'
            WHERE x.T_OrderDetailT_OrderHeaderID = ?
            AND x.T_OrderDetailIsActive = 'Y'
            GROUP BY y.T_OrderDetailT_TestID
            ORDER BY y.T_OrderDetailT_TestSasCode ASC
            ) a1

            LEFT JOIN t_orderdetail a2 ON a2.T_OrderDetailT_OrderHeaderID = a1.order_id
            AND a2.T_OrderDetailIsActive = 'Y'
            AND a1.test_sas_code LIKE CONCAT(a2.T_OrderDetailT_testSasCode, '%')
            GROUP BY a1.t_testid
            order by a1.T_TestSasCode
";
         $query = $this->db_smartone->query($sql, [$id, $id]);
         $lq = $this->db_smartone->last_query();
      }
      //         file_put_contents("/xtmp/re-query.sql",$lq); 

      if ($query) {
         $rst = [];
         $rows = $query->result_array();

         foreach($rows as $k => $v) 
         {
			 
            if ($v['is_result'] == "Y" && $v['is_quantitative'] == "N")
            {
               $v['template'] = [];
               $x = $this->db_smartone->query("CALL sp_master_resulttemplate_get('{$v['t_testid']}')");
               $this->clean_mysqli_connection($this->db_smartone->conn_id);
               if ($x)
                  $v['template'] = $x->result_array();
            }

            // IF Rujukan, by pas Pre Analytik
            if ($v['ref'] == 'Y')
               $v['pre_analytic'] = 'Y';

            $rst[] = $v;
         }

         $result = array("total" => $tot_count, "records" => $rst, "total_display" => sizeof($rows), "q" => $lq);
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("RE Px rows", $this->db_smartone);
         exit;
      }
   }

   public function save()
   {
      $prm = $this->sys_input;
      $data = json_decode($prm["data"]);
      $lang = $this->lang_default_code;
      $lang_id = isset($prm['lang_id']) ? $prm['lang_id'] : '0';

      if ($lang_id != 0)
      {
         $l = $this->db_smartone->where("Nat_LangID", $lang_id)->get("nat_lang")->row();
         $lang = $l->Nat_LangCode;
      }

      $sql_norm = "select T_OrderDetailMinValue, T_OrderDetailMaxValue,
            T_OrderDetailMinValueInclusive, T_OrderDetailMaxValueInclusive,
            Nat_TestFlagLow, Nat_TestFlagHigh
         from t_orderdetail 
         join t_test on T_OrderDetailT_TestID = T_TestID 
         join nat_test on T_TestNat_TestID = Nat_TestID and T_TestIsActive = 'Y'
         where T_OrderDetailID = ? ";

      $ids = [];
      foreach ($data as $k => $v)
      {
         if ( $v->resultInstrumentID > 0 ) {
            $sql_i = "select T_ResultInstrumentNat_NormalValueID normalValueID
               from t_resultinstrument 
               where T_ResultInstrumentID = ?";
            $qry_i = $this->db_smartone->query($sql_i, array($v->resultInstrumentID));
            if ($qry_i) {
               $rows_i = $qry_i->result_array();

               if (count($rows_i) > 0 ) {
               $sql_iu = "update t_orderdetail, nat_normalvalue , nat_methode
                  set T_OrderDetailMinValue = Nat_NormalValueMinValue,
                  T_OrderDetailMinValueInclusive = Nat_NormalValueMinValueInclusive,
                  T_OrderDetailMaxValue = Nat_NormalValueMaxValue ,
                  T_OrderDetailMaxValueInclusive = Nat_NormalValueMaxValueInclusive,
                  T_OrderDetailNormalValueNote = Nat_NormalValueNote,
                  T_OrderDetailNormalValueDescription = Nat_NormalValueDescription,
                  T_OrderDetailNat_NormalValueID = Nat_NormalValueID,
                  T_OrderDetailNat_MethodeID  = Nat_NormalValueNat_MethodeID,
                  T_OrderDetailNat_MethodeName = Nat_MethodeName
                  where T_OrderDetailID = ? and Nat_NormalValueID = ?
                     and Nat_NormalValueNat_MethodeID = Nat_MethodeID
                  ";
                  $this->db_smartone->query($sql_iu, array($v->id, $rows_i[0]["normalValueID"]));
                  //file_put_contents("/xtmp/update-nilai-normal", $this->db_smartone->last_query() . "\n", FILE_APPEND);
               }
            }
         }
         $y = $this->db_smartone->query("SELECT fn_process_sample_handling_perfect('{$v->order_id}', '{$v->test_id}') c")
            ->row();
         $req_status = $y->c;
         $qry_norm = $this->db_smartone->query($sql_norm,$v->id);
         $result_flag = "";
         if ($qry_norm) {
            $rows_norm = $qry_norm->result_array();
            if (count($rows_norm) > 0 ) {
               $r = $rows_norm[0];
               if (is_numeric($v->result)) {

                  if ( $r["T_OrderDetailMinValueInclusive"] == "Y" && $v->result <  $r["T_OrderDetailMinValue"] ) {
                        $result_flag = $r["Nat_TestFlagLow"];
                  }
                  if ( $r["T_OrderDetailMinValueInclusive"] == "N" && $v->result <=  $r["T_OrderDetailMinValue"] ) {
                        $result_flag = $r["Nat_TestFlagLow"];
                  }
                  if ( $r["T_OrderDetailMaxValueInclusive"] == "Y" && $v->result >  $r["T_OrderDetailMaxValue"] ) {
                        $result_flag = $r["Nat_TestFlagHigh"];
                  }
                  if ( $r["T_OrderDetailMaxValueInclusive"] == "N" && $v->result >=  $r["T_OrderDetailMaxValue"] ) {
                        $result_flag = $r["Nat_TestFlagHigh"];
                  }
               }
            }
         }
         if ($lang == $this->lang_default_code)
         {
            $this->db_smartone->set('T_OrderDetailResult', $v->result)
               ->set('T_OrderDetailResultFlag', $result_flag)
               ->set('T_OrderDetailNote', $v->note)
               ->set('T_OrderDetailReqStatus', $req_status)
               ->where('T_OrderDetailID', $v->id)
               ->update('t_orderdetail');
         }
         else
         {
            $this->db_smartone->set('T_OrderDetailLangResult', $v->result)
               ->set('T_OrderDetailLangNote', $v->note)
               ->where('T_OrderDetailLangID', $v->id)
               ->update('t_orderdetaillang');
         }

         $ids[] = $v->id;                           
      }

      // LOG Process
      $order = $this->db_smartone->select('T_OrderDetailT_OrderHeaderID id, T_OrderHeaderLabNumber lab_number', false)
         ->join('t_orderheader', 'T_OrderHeaderID = T_OrderDetailT_OrderHeaderID')
         ->where('T_OrderDetailID', $data[0]->id)
         ->get('t_orderdetail')
         ->row();

      $dblog = $this->load->database("onelog", true);
      $dblog->set('Log_ProcessCode', 'PROCESS.Result.Entry')
         ->set('Log_ProcessOrderID', $order->id)
         ->set('Log_ProcessOrderNumber', $order->lab_number)
         ->set('Log_ProcessJson', json_encode($data))
         ->set('Log_ProcessUserID', $this->sys_user['M_UserID'])
         ->insert('log_process');
      // END OF Log Process
      //auto calculation
      $this->load->library("Resultcalc");
      $rows = $this->resultcalc->auto($order->id);

      try {
         foreach($ids as $id ) {
            $this->db_smartone->query("call sp_set_normal_value_flag($id)");
            $this->clean_mysqli_connection($this->db_smartone->conn_id);
         }
      } catch(Exception $e) {
      }

      $this->sys_ok($ids);
   }

   public function save_template()
   {
      $prm = $this->sys_input;

      $n = $this->db_smartone->select("COUNT(T_ResultTemplateID) n")
         ->where("T_ResultTemplateT_TestID", $prm['test_id'])
         ->where("T_ResultTemplateIsActive", "Y")
         ->where("T_ResultTemplateValue", $prm['value'])
         ->get('t_resulttemplate')
         ->row();
      if ($n->n > 0)
      {
         $this->sys_error_db("Insert Result Template", $this->db_smartone);
         return;
      }

      $r = $this->db_smartone->set("T_ResultTemplateT_TestID", $prm['test_id'])
         ->set("T_ResultTemplateValue", $prm['value'])
         ->insert('t_resulttemplate');
      if ($r)
      {
         $template = [];
         $x = $this->db_smartone->query("CALL sp_master_resulttemplate_get('{$prm['test_id']}')");
         $this->clean_mysqli_connection($this->db_smartone->conn_id);
         if ($x)
            $template = $x->result_array();

         $this->sys_ok($template);
      }
   }

   public function export()
   {
      $prm = $this->sys_input;

      $sql = "CALL sp_process_result_lang(?, ?, ?, ?)";
      $query = $this->db_smartone->query($sql, [$prm['order_id'], $prm['lang_id'], $prm['is_si'], $this->sys_user['M_UserID']]);
      if ($query)
      {
         $r = $query->row();
         if ($r->status == "OK")
         {
            $this->sys_ok($r->data);
            return;
         }

         $this->sys_error_db("Message : " . $r->message, $this->db_smartone);
      }
      else
      {
         $this->sys_error_db("Lang Result", $this->db_smartone);
         return;
      }
   }

   public function search_group()
   {
      $prm = $this->sys_input;
      $max_rst = 100;

      // QUERY TOTAL
      $sql = "select count(*) total
         from t_worklist
         where T_WorklistIsActive = 'Y'
         order by T_WorklistName ASC";
      $query = $this->db_smartone->query($sql);

      if ($query) { 
         $tot_count = $query->result_array()[0]["total"]; 
      } 
      else {
         $this->sys_error_db("worklist count", $this->db_smartone);
         exit;
      }

      $sql = "select T_WorklistID group_id, T_WorklistName group_name
         from t_worklist
         where T_WorklistIsActive = 'Y'
         order by T_WorklistName ASC
         limit 0, {$max_rst}";
      $query = $this->db_smartone->query($sql);

      if ($query) { 
         $rows = $query->result_array();
         foreach ($rows as $k => $v)
            $rows[$k]['data'] = json_decode($v['data']);
         $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows), "q" => $this->db_smartone->last_query());
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("worklist rows", $this->db_smartone);
         exit;
      }
   }

   public function search_rerun()
   {
      $prm = $this->sys_input;
      $id = $prm["detail_id"];

      $sql = "CALL sp_process_result_rerun(?)";
      $query = $this->db_smartone->query($sql, [$id]);

      if ($query) 
      {
         $rows = $query->result_array();
         $this->sys_ok(["records"=>$rows]);
      } 
      else {
         $this->sys_error_db("Rerun rows", $this->db_smartone);
         exit;
      }
   }
   
   function getdoctorsfna()
   {
      $prm = $this->sys_input;
     // $id = $prm["id"];

      $sql = "	SELECT M_DoctorID as id, CONCAT(M_DoctorPrefix,M_DoctorPrefix2,M_DoctorName,M_DoctorSufix,M_DoctorSufix2,M_DoctorSufix3) as name
				FROM m_doctorso
				JOIN m_doctor ON M_DoctorSOM_DoctorID = M_DoctorID AND
				M_DoctorIsActive = 'Y'
				WHERE
				M_DoctorSONat_SubGroupID = 9  AND 
				M_DoctorSOIsActive = 'Y'
				ORDER BY M_DoctorName ASC";
		//echo $sql;
      $query = $this->db_smartone->query($sql);

      if ($query) 
      {
         $rows = $query->result_array();
         $this->sys_ok(["records"=>$rows]);
      } 
      else {
         $this->sys_error_db("Rerun rows", $this->db_smartone);
         exit;
      }
   }
   
   function getfnaresult()
   {
      $prm = $this->sys_input;
      $id = $prm["id"];

      $sql = "	SELECT Other_FNADetailsID as xid,
				Other_FNAT_OrderDetailID as orderdetail_id,
				Other_FNADetailsCode as code,
				Other_FNADetailsLabel as label,
				Other_FNADetailsResult as result,
				IFNULL(Other_FNAM_DoctorID,0) as doctor_id,
				IF(ISNULL(Other_FNAM_DoctorID) OR Other_FNAM_DoctorID = 0 ,'0', CONCAT(M_DoctorPrefix,M_DoctorPrefix2,M_DoctorName,M_DoctorSufix,M_DoctorSufix2,M_DoctorSufix3)) as doctor_name
				FROM other_fna
				JOIN other_fnadetails ON Other_FNADetailsOther_FNAID = Other_FNAID AND Other_FNADetailsIsActive = 'Y'
				LEFT JOIN m_doctor ON Other_FNAM_DoctorID = M_DoctorID AND
				M_DoctorIsActive = 'Y'
				WHERE
					Other_FNAT_OrderDetailID = {$id} AND Other_FNAIsActive = 'Y'
				GROUP BY Other_FNADetailsID
				ORDER BY Other_FNADetailsCode ASC";
      $query = $this->db_smartone->query($sql);

      if ($query) 
      {
         $rows = $query->result_array();
		 $sql = "	
				SELECT '0' as id, 'Belum memilih dokter' as name
				UNION
				SELECT M_DoctorID as id, CONCAT(M_DoctorPrefix,M_DoctorPrefix2,M_DoctorName,M_DoctorSufix,M_DoctorSufix2,M_DoctorSufix3) as name
				FROM m_doctorso
				JOIN m_doctor ON M_DoctorSOM_DoctorID = M_DoctorID AND
				M_DoctorIsActive = 'Y'
				WHERE
				M_DoctorSONat_SubGroupID = 9  AND 
				M_DoctorSOIsActive = 'Y'
				ORDER BY name ASC";
				//echo $sql;
		$doctors = $this->db_smartone->query($sql)->result_array();
		$rst = array('doctors'=>$doctors,'results'=>$rows);
        $this->sys_ok(["records"=>$rst]);
      } 
      else {
         $this->sys_error_db("Rerun rows", $this->db_smartone);
         exit;
      }
   }
   
   function getpapsmearresult()
   {
      $prm = $this->sys_input;
      $id = $prm["id"];

      $sql = "	SELECT Other_PapSmearDetailsID as xid,
				Other_PapSmearID as header_id,
				Other_PapSmearT_OrderDetailID as orderdetail_id,
				Other_PapSmearDetailsCode as code,
				Other_PapSmearDetailsLabel as label,
				Other_PapSmearDetailsResult as result,
				IFNULL(Other_PapSmearM_DoctorID,0) as doctor_id,
				IF(ISNULL(Other_PapSmearM_DoctorID) OR Other_PapSmearM_DoctorID = 0 ,'0', CONCAT(M_DoctorPrefix,M_DoctorPrefix2,M_DoctorName,M_DoctorSufix,M_DoctorSufix2,M_DoctorSufix3)) as doctor_name
				FROM other_papsmear
				JOIN other_papsmeardetails ON Other_PapSmearDetailsOther_PapSmearID = Other_PapSmearID AND Other_PapSmearDetailsIsActive = 'Y'
				LEFT JOIN m_doctor ON Other_PapSmearM_DoctorID = M_DoctorID AND
				M_DoctorIsActive = 'Y'
				WHERE
					Other_PapSmearT_OrderDetailID = {$id} AND Other_PapSmearIsActive = 'Y'
				GROUP BY Other_PapSmearDetailsID
				ORDER BY Other_PapSmearDetailsCode ASC";
				//echo $sql;
      $rows = $this->db_smartone->query($sql)->result_array();
		if(!$rows){
			$rows = array(
						array(
							'xid' => '0',
							'header_id' => '0',
							'orderdetail_id' => $id,
							'code' => 'A',
							'label' => 'Makroskopik',
							'result' => '',
							'doctor_id' => '0',
							'doctor_name' => ''
						),
						array(
							'xid' => '0',
							'header_id' => '0',
							'orderdetail_id' => $id,
							'code' => 'B',
							'label' => 'Mikroskopik',
							'result' => '',
							'doctor_id' => '0',
							'doctor_name' => ''
						),
						array(
							'xid' => '0',
							'header_id' => '0',
							'orderdetail_id' => $id,
							'code' => 'C',
							'label' => 'Kesimpulan',
							'result' => '',
							'doctor_id' => '0',
							'doctor_name' => ''
						)
					);
		}
     
		 $header_id = $rows[0]['header_id'];
		 $sql = "SELECT Other_PapSmearMaturasiID as xid,
					Other_PapSmearMaturasiValue as value
					FROM other_papsmearmaturasi 
				WHERE
					Other_PapSmearMaturasiOther_PapSmearID = {$header_id} AND 
					Other_PapSmearMaturasiIsActive = 'Y'
				";
		//echo $sql;
		 $dt_maturasi = $this->db_smartone->query($sql)->row_array();
		 if(!$dt_maturasi){
			 $maturasi = array('xid'=>'0','value'=> array('','',''));
		 }
		 else{
			 $value = explode('/',$dt_maturasi['value']);
			 $maturasi = array('xid'=>$dt_maturasi['xid'],'value'=> $value);
		 }
		 
		 $sql = "SELECT IFNULL(Other_PapSmearBahanID,0) as xid,
					M_PapSmearBahanID as bahan_id,
					M_PapSmearBahanName as name,
					IF(ISNULL(Other_PapSmearBahanID),'N','Y') as selected
					FROM m_papsmearbahan 
					LEFT JOIN other_papsmearbahan ON Other_PapSmearBahanM_PapSmearBahanID = M_PapSmearBahanID AND
						Other_PapSmearBahanOther_PapSmearID = {$header_id} AND
						Other_PapSmearBahanIsActive = 'Y'
				WHERE
					M_PapSmearBahanIsActive = 'Y'
				ORDER BY M_PapSmearBahanID ASC
				";
				//echo $sql;
		 $bahans = $this->db_smartone->query($sql)->result_array();
		 
		 $sql = "SELECT IFNULL(Other_PapsmearCategoryID,0) as xid,
					M_PapSmearCategoryID as category_id,
					M_PapSmearCategoryName as name,
					IF(ISNULL(Other_PapsmearCategoryID),'N','Y') as selected
					FROM m_papsmearcategory 
					LEFT JOIN other_papsmearcategory ON Other_PapsmearCategoryM_PapSmearCategoryID = M_PapSmearCategoryID AND 
						Other_PapsmearCategoryOther_PapSmearID = {$header_id} AND
						Other_PapsmearCategoryIsActive = 'Y'
				WHERE
					M_PapSmearCategoryIsActive = 'Y'
					ORDER BY M_PapSmearCategoryID ASC
				";
				//echo $sql;
		 $categories = $this->db_smartone->query($sql)->result_array();
		 
		 $sql = "SELECT IFNULL(Other_PapSmearCheckID,0) as xid,
					M_PapSmearCheckID as check_id,
					M_PapSmearCheckLabel as name,
					IF(ISNULL(Other_PapSmearCheckID),'N','Y') as selected,
					Other_PapSmearCheckNote as note
					FROM m_papsmearcheck 
					LEFT JOIN other_papsmearcheck ON Other_PapSmearCheckM_PapSmearCheckID = M_PapSmearCheckID AND
						Other_PapSmearCheckOther_PapSmearID = {$header_id} AND
						Other_PapSmearCheckIsActive = 'Y'
				WHERE
					M_PapSmearCheckIsActive = 'Y'
				ORDER BY M_PapSmearCheckCode ASC
				";
		 $checks = $this->db_smartone->query($sql)->result_array();
		 
		 $sql = "	
				SELECT '0' as id, 'Belum memilih dokter' as name
				UNION
				SELECT M_DoctorID as id, CONCAT(M_DoctorPrefix,M_DoctorPrefix2,M_DoctorName,M_DoctorSufix,M_DoctorSufix2,M_DoctorSufix3) as name
				FROM m_doctorso
				JOIN m_doctor ON M_DoctorSOM_DoctorID = M_DoctorID AND
				M_DoctorIsActive = 'Y'
				WHERE
				M_DoctorSONat_SubGroupID = 9  AND 
				M_DoctorSOIsActive = 'Y'
				ORDER BY name ASC";
				//echo $sql;
		$doctors = $this->db_smartone->query($sql)->result_array();
		$rst = array('doctors'=>$doctors,'checks'=>$checks,'bahans'=>$bahans,'categories'=>$categories,'maturasi'=>$maturasi,'results'=>$rows);
        $this->sys_ok(["records"=>$rst]);
     
   }
   
   function saveresult_papsmear()
   {
		$prm = $this->sys_input;
		$detail_id = $prm['detail_id'];
		$doctor_id = $prm['doctor'];
		$checks = $prm['checks'];
		$maturasi = $prm['maturasi'];
		$bahans = $prm['bahans'];
		$categories = $prm['categories'];
		$results = $prm['results'];
		
		$userid = $this->sys_user['M_UserID'];
		
		$header_id = 0;
		$sql = "SELECT * FROM other_papsmear WHERE Other_PapSmearT_OrderDetailID = {$detail_id} AND Other_PapSmearIsActive = 'Y'";
		$dt_header = $this->db_smartone->query($sql)->row_array();
		if($dt_header){
			$header_id = $dt_header['Other_PapSmearID'];
		}
		if($header_id == 0){
			$sql = "INSERT INTO other_papsmear(
						Other_PapSmearT_OrderDetailID,
						Other_PapSmearM_DoctorID,
						Other_PapSmearUserID,
						Other_PapSmearCreated
					)
					VALUES(
						{$detail_id},
						{$doctor_id},
						{$userid},
						NOW()
					)";
			$this->db_smartone->query($sql);
			$header_id = $this->db_smartone->insert_id();
		}
		
		if($checks){
			foreach($checks as $k =>$v){
				if(intval($v['xid']) == 0 && $v['selected'] === 'Y'){
					$sql = "INSERT INTO other_papsmearcheck (
								Other_PapSmearCheckOther_PapSmearID,
								Other_PapSmearCheckM_PapSmearCheckID,
								Other_PapSmearCheckNote,
								Other_PapSmearCheckUserID,
								Other_PapSmearCheckCreated
							)
							VALUES(
								{$header_id},
								{$v['check_id']},
								'{$v['note']}',
								{$userid},
								NOW()
							)";
					$this->db_smartone->query($sql);
				}
				if(intval($v['xid']) != 0 && $v['selected'] === 'N'){
					$sql = "UPDATE other_papsmearcheck SET 
								Other_PapSmearCheckIsActive = 'N'
							WHERE
								Other_PapSmearCheckID = {$v['xid']}";
					$this->db_smartone->query($sql);
				}
				if(intval($v['xid']) != 0 && $v['selected'] === 'Y'){
					$sql = "UPDATE other_papsmearcheck SET 
								Other_PapSmearCheckNote = '{$v['note']}'
							WHERE
								Other_PapSmearCheckID = {$v['xid']}";
					$this->db_smartone->query($sql);
				}
				//echo $sql;
			}
		}
		
		if($maturasi){
			if(intval($maturasi['xid']) == 0){
				$value = join("/",$maturasi['value']);
				$sql = "INSERT INTO other_papsmearmaturasi (
							Other_PapSmearMaturasiOther_PapSmearID,
							Other_PapSmearMaturasiValue,
							Other_PapSmearMaturasiUserID,
							Other_PapSmearMaturasiCreated
						)
						VALUE(
							{$header_id},
							'{$value}',
							{$userid},
							NOW()
						)";
				$this->db_smartone->query($sql);	
				//echo $sql;				
			}
			if(intval($maturasi['xid']) != 0){
				$value = join("/",$maturasi['value']);
				$sql = "UPDATE other_papsmearmaturasi SET 
							Other_PapSmearMaturasiValue = '{$value}',
							Other_PapSmearMaturasiUserID = {$userid}
						WHERE
							Other_PapSmearMaturasiID = {$maturasi['xid']}";
						//	echo $sql;
				$this->db_smartone->query($sql);		
			}
		}
		
		if($bahans){
			foreach($bahans as $k =>$v){
				if(intval($v['xid']) == 0 && $v['selected'] === 'Y'){
					$sql = "INSERT INTO other_papsmearbahan (
								Other_PapSmearBahanOther_PapSmearID,
								Other_PapSmearBahanM_PapSmearBahanID,
								Other_PapSmearBahanUserID,
								Other_PapSmearBahanCreated
							)
							VALUES(
								{$header_id},
								{$v['bahan_id']},
								{$userid},
								NOW()
							)";
					$this->db_smartone->query($sql);
				}
				if(intval($v['xid']) != 0 && $v['selected'] === 'N'){
					$sql = "UPDATE other_papsmearbahan SET 
								Other_PapSmearBahanIsActive = 'N'
							WHERE
								Other_PapSmearBahanID = {$v['xid']}";
					$this->db_smartone->query($sql);
				}
				//echo $sql;
			}
		}
		
		if($categories){
			foreach($categories as $k =>$v){
				if(intval($v['xid']) == 0 && $v['selected'] === 'Y'){
					$sql = "INSERT INTO other_papsmearcategory (
								Other_PapsmearCategoryOther_PapSmearID,
								Other_PapsmearCategoryM_PapSmearCategoryID,
								Other_PapsmearCategoryUserID,
								Other_PapsmearCategoryCreated
							)
							VALUES(
								{$header_id},
								{$v['category_id']},
								{$userid},
								NOW()
							)";
					$this->db_smartone->query($sql);
				}
				if(intval($v['xid']) != 0 && $v['selected'] === 'N'){
					$sql = "UPDATE other_papsmearcategory SET 
								Other_PapsmearCategoryIsActive = 'N'
							WHERE
								Other_PapsmearCategoryID = {$v['xid']}";
					$this->db_smartone->query($sql);
				}
				//echo $sql;
			}
		}
		
		if($results){
			foreach($results as $k =>$v){
				if(intval($v['xid']) == 0){
					$sql = "INSERT INTO other_papsmeardetails (
								Other_PapSmearDetailsOther_PapSmearID,
								Other_PapSmearDetailsCode,
								Other_PapSmearDetailsLabel,
								Other_PapSmearDetailsResult,
								Other_PapSmearDetailsUserID,
								Other_PapSmearDetailsCreated
							)
							VALUES(
								{$header_id},
								'{$v['code']}',
								'{$v['label']}',
								'{$v['result']}',
								{$userid},
								NOW()
							)";
					$this->db_smartone->query($sql);
				}
				if(intval($v['xid']) != 0){
					$sql = "UPDATE other_papsmeardetails SET 
								Other_PapSmearDetailsCode = '{$v['code']}',
								Other_PapSmearDetailsLabel = '{$v['label']}',
								Other_PapSmearDetailsResult = '{$v['result']}'
							WHERE
								Other_PapSmearDetailsID = {$v['xid']}";
					$this->db_smartone->query($sql);
					
				}
				//echo $sql;
			}
		}
		
		$result = array("total" => 1, "records" => array(), "sql"=> '');
		$this->sys_ok($result);
		exit;
   }
   
   function saveresult_fna()
   {
	  $inp = $this->sys_input;
      $prm = $inp['results'];
	  $doctor_id = $inp['doctor'];
	  $userid = $this->sys_user['M_UserID'];
	  $header_id = 0;
	  if($prm[0]['xid'] == '0'){
			$sql = "INSERT INTO other_fna(
						Other_FNAM_DoctorID,
						Other_FNAT_OrderDetailID,
						Other_FNAUserID,
						Other_FNACreated
					)
					VALUES(
						{$doctor_id},
						{$prm[0]['orderdetail_id']},
						{$userid},
						NOW()
						
					)";
			//echo $sql;
			$this->db_smartone->query($sql); 
			$header_id = $this->db_smartone->insert_id();
	  }
	  else{
		$sql = "SELECT * FROM other_fna WHERE Other_FNAT_OrderDetailID = {$prm[0]['orderdetail_id']} AND Other_FNAIsActive = 'Y'";
		$data_header = $this->db_smartone->query($sql)->row_array();
		$header_id = $data_header['Other_FNAID'];
		$sql = "UPDATE other_fna SET 
				Other_FNAM_DoctorID = {$doctor_id},
				Other_FNAUserID = {$userid}
				WHERE
					Other_FNAID = {$header_id}";
		$this->db_smartone->query($sql); 
	  }
	  foreach($prm as $k => $v){
		 if($v['xid'] == '0'){
			 $results = str_replace("'", "\\'", $v['result']);
			$sql = "INSERT INTO other_fnadetails(
						Other_FNADetailsOther_FNAID	,
						Other_FNADetailsCode,
						Other_FNADetailsLabel,
						Other_FNADetailsResult,
						Other_FNADetailsCreated,
						Other_FNADetailsUserID
					) 
					VALUES(
						{$header_id},
						'{$v['code']}',
						'{$v['label']}',
						'{$results}',
						NOW(),
						{$userid}
					)";
			$query = $this->db_smartone->query($sql);   
		 }
		 else{
			 $results = str_replace("'", "\\'", $v['result']);
			$sql = "UPDATE other_fnadetails SET
						Other_FNADetailsResult = '{$results}',
						Other_FNADetailsUserID = {$userid}
					WHERE
						Other_FNADetailsID = {$v['xid']}";
			$query = $this->db_smartone->query($sql);    
		 }
	  }

        $result = array("total" => 1, "records" => array(), "sql"=> '');
		$this->sys_ok($result);
		exit;
   }

}