<?php
//diberi tambahan pembeda IsFromPanel 
//utk contoh kasus yg ndak bisa di delete
//sementara profile di ambilkan dari panel juga dengan IsFromPanel = N
class Px extends MY_Controller
{
   var $db_smartone;
   public function index()
   {
      echo "Px API";
   }
   public function __construct()
   {
      parent::__construct();
      $this->db_smartone = $this->load->database("onedev", true);
   }
   function _add_ref_test(&$rows) {
     $ids = "-1";
     foreach($rows as $idx => $r) {
        $ids .= "," . $r["T_TestID"];
        if (! $rows[$idx]["ref_test"]  ) $rows[$idx]["ref_test"] == array();  
     }
     $sql="select T_TestID,T_RefTestName, T_TestName
     from
     t_reftest
     join t_test on T_RefTestID = T_TestT_RefTestID
     and T_RefTestIsActive = 'Y'
     where T_TestID in ( $ids )";


   }
   public function profile() 
   {
      $prm = $this->sys_input;
      $search = $prm["search"];
      $mou_id = $prm["mou_id"];
      $max_rst = 8;

      $sql_param = array("%$search%");
      $sql = "select count(distinct T_ProfileID) total
            from t_profile
            join t_profiledetail on t_profileid = t_profiledetailt_profileid
               and t_profiledetailisactive = 'Y'
            where t_profilename like ?";
      $query = $this->db_smartone->query($sql, $sql_param);

      $tot_count =0;
      if ($query) {
         $tot_count = $query->row()->total;
      } else {
         $this->sys_error_db("Test Profile count", $this->db_smartone);
         exit;
      }

      $sql = "select T_ProfileID, T_ProfileName, CONCAT('[', GROUP_CONCAT( JSON_OBJECT('T_TestID', T_TestID, 'T_TestName', T_TestName, 'T_TestRequirement', T_TestRequirement) SEPARATOR ','), ']') detail
               from t_profile
               join t_profiledetail on t_profileid = t_profiledetailt_profileid
                  and t_profiledetailisactive = 'Y'
               join t_test on t_profiledetailt_testid = t_testid
               where t_profilename like ?
               group by t_profileid
               limit 0, $max_rst";
      $query = $this->db_smartone->query($sql, $sql_param);
      $rows = $query->result_array();
      
      foreach($rows as $k => $r) 
      {
         $err = 0;
         $detail = json_decode($r['detail']);
         foreach ($detail as $l => $w)
         {
            $sql_param = array($w->T_TestID, date('Y-m-d'), 'N', $mou_id);
            $sql = "select fn_price(?, ?, ?, ?) as price";
            $query = $this->db_smartone->query($sql, $sql_param);

            if ($query)
            {
               $price = json_decode($query->row()->price);

               $detail[$l]->T_PriceAmount = $price->test_price;
               $detail[$l]->T_PriceDisc = $price->test_disc;
               $detail[$l]->T_PriceDiscRp = $price->test_discrp;
               $detail[$l]->T_PriceID = $price->price_id;
               $detail[$l]->T_PriceIsCito = "N";
               $detail[$l]->T_PriceM_CompanyID = $price->company_id;
               $detail[$l]->T_PriceM_MouID = $price->mou_id;
               $detail[$l]->T_PriceOther = $price->test_other;
               $detail[$l]->T_PriceSubTotal = $price->test_subtotal;
               $detail[$l]->T_PriceT_TestID = $price->test_id;
               $detail[$l]->T_PriceTotal = $price->test_total;
               
               if ($price->test_price == 0)
                  $err++;
            }
         }
         
         $rows[$k]['detail'] = $detail;
         $rows[$k]['err'] = $err;
      }
      
      $result = array("total" => $tot_count, "records" => $rows  );
      $this->sys_ok($result);
      exit;
   }

   public function panel() {
      $prm = $this->sys_input;
      $search = $prm["search"];
      $mouCompanyID = $prm["mouCompanyID"];

      $sql_param = array($mouCompanyID, "%$search%");
      $sql = "select count(distinct T_TestPanelID) total
         from 
         t_testpanel
         join t_testpaneldetail on T_TestPanelID = T_TestPanelDetailT_TestPanelID
            and T_TestPanelIsActive = 'Y' and T_TestPanelDetailIsActive = 'Y'
         join t_test on T_TestPanelDetailT_TestID = T_TestID
            and T_TestIsActive = 'Y'
         join t_testprice on T_TestID = T_TestPriceT_TestID
            and T_TestIsPrice = 'Y'
            and T_TestPriceIsActive = 'Y'
            and T_TestPriceM_MouCompanyID = ?
         where 
         T_TestPanelName like ? ";
      $query = $this->db_smartone->query($sql, $sql_param);
      $tot_count =0;
      if ($query) {
         $tot_count = $query->result_array()[0]["total"];
      } else {
         $this->sys_error_db("m_testpanel count", $this->db_smartone);
         exit;
      }  
      $sql = "select distinct T_TestPanelID
         from 
         t_testpanel
         join t_testpaneldetail on T_TestPanelID = T_TestPanelDetailT_TestPanelID
            and T_TestPanelIsActive = 'Y' and T_TestPanelDetailIsActive = 'Y'
         join t_test on T_TestPanelDetailT_TestID = T_TestID
            and T_TestIsActive = 'Y'
         join t_testprice on T_TestID = T_TestPriceT_TestID
            and T_TestIsPrice = 'Y'
            and T_TestPriceIsActive = 'Y'
            and T_TestPriceM_MouCompanyID = ?
         where 
         T_TestPanelName like ? 
         limit 0,20";
      $query = $this->db_smartone->query($sql,$sql_param);
      $xrows = $query->result_array();
      $a_tpid = "-1";
      foreach($xrows as $r) {
         $a_tpid .= "," . $r["T_TestPanelID"];
      }
      $sql = "select distinct T_TestPanelID,T_TestPanelName,
         T_TestID,T_TestName, 'Y' IsFromPanel,T_TestRequirement,
         t_testprice.*
         from 
         t_testpanel
         join t_testpaneldetail on T_TestPanelID = T_TestPanelDetailT_TestPanelID
            and T_TestPanelIsActive = 'Y' and T_TestPanelDetailIsActive = 'Y'
         join t_test on T_TestPanelDetailT_TestID = T_TestID
            and T_TestIsActive = 'Y'
         join t_testprice on T_TestID = T_TestPriceT_TestID
            and T_TestIsPrice = 'Y'
            and T_TestPriceM_MouCompanyID = ?
            and T_TestPriceIsActive = 'Y'
         where 
         T_TestPanelID in ( $a_tpid ) 
         order by T_TestPanelID";
      $query = $this->db_smartone->query($sql,array($mouCompanyID));
      $xrows = $query->result_array();
      $rows = array();
      $prev_tpanel_id = 0;
      foreach($xrows as $r) {
         $tpanel_id = $r["T_TestPanelID"];
         if ($tpanel_id != $prev_tpanel_id) {
            $rows[] = array(
               "T_TestPanelID" => $r["T_TestPanelID"],
               "T_TestPanelName" => $r["T_TestPanelName"],
               "test" => array()
            );
         }
         $idx = count($rows) - 1;
         $rows[$idx]["test"][] = $r;
         $prev_tpanel_id = $tpanel_id;
      }
      $result = array("total" => $tot_count, "records" => $rows  );
      $this->sys_ok($result);
      exit;
   }

   public function search_v2()
   {

      $prm = $this->sys_input;
      $search = $prm["search"];
      $mouCompanyID = $prm["mouCompanyID"];

      $sql_param = array($mouCompanyID, "%$search%");

      $query = $this->db_smartone->query("CALL sp_fo_px_count_v2(?, ?)", $sql_param);
      $this->clean_mysqli_connection($this->db_smartone->conn_id);
      
      $tot_count = 0;
      if ($query) {
         $tot_count = $query->result_array()[0]["data"];
      } else {
         $this->sys_error_db("PX count", $this->db_smartone);
         exit;
      }  

        if (isset($prm['order_id']))
            $query = $this->db_smartone->query("CALL sp_fo_px_search_byorder_v2(?, ?)", [$prm['order_id'], $mouCompanyID]);
        else if (isset($prm['clinic_id']))
            $query = $this->db_smartone->query("CALL sp_fo_px_search_byclinic(?, ?)", [$prm['clinic_id'], $mouCompanyID]);
        else if ($search == "")
            $query = $this->db_smartone->query("CALL sp_fo_px_search_favorite_v2(?, ?)", $sql_param);
        else
            $query = $this->db_smartone->query("CALL sp_fo_px_search_v2(?, ?)", $sql_param);
      $this->clean_mysqli_connection($this->db_smartone->conn_id);
      
      // echo $this->db_smartone->last_query();
      // $query = $this->db_smartone->query($sql);
      if ($query)
      {
         $rows = $query->result_array();
         $id_to_remove = [];
         
         // var_dump($rows);
         foreach ($rows as $k => $v)
         {
            $rows[$k]['requirement'] = [];
            if($v['px_type'] == "PX"){
				$x = $this->db_smartone->query("SELECT fn_fo_requirement_get('{$v['T_TestID']}') x")
                                                ->row();
				if ($x->x != null)
				   $rows[$k]['requirement'] = json_decode($x->x);
			}
            

            // Janji Hasil per PX
            //$x = $this->db_smartone->query("SELECT fn_fo_find_promise_by_one_px('{$v['T_TestID']}') x")->row();
			if($v['is_packet'] == 'N'){
				$tests = $v['T_PriceT_TestID'];
				$panels = '';
			}
			else{
				$tests = '';
				$panels = $v['T_PriceT_TestID'];
			}
			
			$sql_param = array($tests, $panels);
			$sql = "select fn_fo_find_promise_by_px(?, ?) as x";
			$x = $this->db_smartone->query($sql, $sql_param)->row();
				
            if ($x->x != null)
                $rows[$k]['promise'] = $x->x;
               
            $rows[$k]['nat_test'] = json_decode($v['nat_test']);
            $rows[$k]['child_test'] = json_decode($v['child_test']);

            // IF PROFILE
            if ($v['px_type'] == "PR" || $v['px_type'] == "PXR") {

               if ($v['T_TestID'] == null)
               { 
                  $id_to_remove[] = $k;
                  continue;
               }
               else
               {
					foreach ($rows[$k]['child_test'] as $l => $w) {
						$rows[$k]['child_test'][$l]->requirement = [];
						$rows[$k]['child_test'][$l]->nat_test = json_decode($w->nat_test);
						$rows[$k]['child_test'][$l]->promise = null;
						//echo "SELECT fn_fo_requirement_get('{$w->T_TestID}')";
						$x = $this->db_smartone->query("SELECT fn_fo_requirement_get('{$w->T_TestID}') x")->row();
						if ($x->x != null)
							$rows[$k]['child_test'][$l]->requirement = json_decode($x->x);

						// Janji Hasil per PX
						$x = $this->db_smartone->query("SELECT fn_fo_find_promise_by_one_px('{$w->T_TestID}') x")
                                                        ->row();
						if ($x->x != null)
							$rows[$k]['child_test'][$l]->promise = $x->x;
					}
               }
            }
         }
         
         // REMOVE INDEXES
         
         foreach ($id_to_remove as $l => $w)
            { $x = $w - $l; array_splice($rows, $x, 1); }

         $result = array("total" => $tot_count, "records" => (array) $rows, "query" => $sqlx, "query2" => $sqly  );
         $this->sys_ok($result);
         exit;
      }
      
   }   
   
   function get_price()
   {
      $prm = $this->sys_input;
      $r = [];      

      $sql_param = array($prm['test_id'], date('Y-m-d'), $prm['cito'], $prm['mou_id']);
      $sql = "select fn_price(?, ?, ?, ?) as price";
      $query = $this->db_smartone->query($sql, $sql_param);
      
      if ($query) {
         $r = $query->result_array()[0];
         $r = json_decode($r['price']);
         $this->sys_ok($r);

         exit;
      } else {
         $this->sys_error_db("get price", $this->db_smartone);

         exit;
      }  
   }
   
   public function search()
   {
      $prm = $this->sys_input;
      $search = $prm["search"];
      $mouCompanyID = $prm["mouCompanyID"];

      $sql_param = array($mouCompanyID, "%$search%");

      $query = $this->db_smartone->query("CALL sp_fo_px_count(?, ?)", $sql_param);
      $this->clean_mysqli_connection($this->db_smartone->conn_id);
      
      $tot_count = 0;
      if ($query) {
         $tot_count = $query->result_array()[0]["data"];
      } else {
         $this->sys_error_db("PX count", $this->db_smartone);
         exit;
      }  

        if (isset($prm['order_id']))
            $query = $this->db_smartone->query("CALL sp_fo_px_search_byorder(?, ?)", [$prm['order_id'], $mouCompanyID]);
        else if (isset($prm['clinic_id']))
            $query = $this->db_smartone->query("CALL sp_fo_px_search_byclinic(?, ?)", [$prm['clinic_id'], $mouCompanyID]);
        else if ($search == "")
            $query = $this->db_smartone->query("CALL sp_fo_px_search_favorite(?, ?)", $sql_param);
        else
            $query = $this->db_smartone->query("CALL sp_fo_px_search(?, ?)", $sql_param);
      $this->clean_mysqli_connection($this->db_smartone->conn_id);
      
      // echo $this->db_smartone->last_query();
      // $query = $this->db_smartone->query($sql);
      if ($query)
      {
         $rows = $query->result_array();
         $id_to_remove = [];
         
         // var_dump($rows);
         foreach ($rows as $k => $v)
         {
            $rows[$k]['requirement'] = [];
            
            $x = $this->db_smartone->query("SELECT fn_fo_requirement_get('{$v['T_TestID']}') x")
                                                ->row();
            if ($x->x != null)
               $rows[$k]['requirement'] = json_decode($x->x);
               
            $rows[$k]['nat_test'] = json_decode($v['nat_test']);
            $rows[$k]['child_test'] = json_decode($v['child_test']);

            // IF PROFILE
            if ($v['px_type'] == "PR" || $v['px_type'] == "PXR") {

               if ($v['T_TestID'] == null)
               { 
                  $id_to_remove[] = $k;
                  continue;
               }
                  
               else
               {
                  foreach ($rows[$k]['child_test'] as $l => $w) {
                     $rows[$k]['child_test'][$l]->requirement = [];
                     $rows[$k]['child_test'][$l]->nat_test = json_decode($w->nat_test);
                     $x = $this->db_smartone->query("SELECT fn_fo_requirement_get('{$w->T_TestID}') x")
                                                         ->row();
                     if ($x->x != null)
                        $rows[$k]['child_test'][$l]->requirement = json_decode($x->x);
                  }
               }
            }
         }
         
         // REMOVE INDEXES
         
         foreach ($id_to_remove as $l => $w)
            { $x = $w - $l; array_splice($rows, $x, 1); }

         $result = array("total" => $tot_count, "records" => (array) $rows, "query" => $sqlx, "query2" => $sqly  );
         $this->sys_ok($result);
         exit;
      }
      
   }

   function packet_reqs()
   {
      $prm = $this->sys_input;
       $child_test = $prm['pxs'];
	   $reqs = array();
	   foreach($child_test as $k => $v){
		   $x = $this->db_smartone->query("SELECT fn_fo_requirement_get('{$v['T_TestID']}') x")->row();
			if ($x->x != null){
				$now_reqs = json_decode($x->x);
				foreach($now_reqs as $kr => $vr){
					$idx = $this->check_reqs_exist($reqs,$vr->req_id);
					if($idx == -1){
						$xtests = array();
						array_push($xtests,$v['T_TestID']);
						$vr->tests = $xtests;
						array_push($reqs,$vr);
					}
					else{
						$xtests = $reqs[$idx]->tests;
						array_push($xtests,$v['T_TestID']);
						$reqs[$idx]->tests = $xtests;
					}
				}
			}
				
	   }
      
      $this->sys_ok($reqs);
	   exit;
   }
   
   function check_reqs_exist($reqs,$req_id){
	   $rtn = -1;
	   foreach($reqs as $k => $v){
		   if(intval($v->req_id) == intval($req_id)){
			   $rtn = $k;
			   break;
		   }
	   }
	   return $rtn;
   }

   function get_appx_schedule()
   {
      $prm = $this->sys_input;
      $r = [];      

      $sql_param = array($prm['test_ids'], $prm['panel_ids']);
      $sql = "select fn_fo_find_promise_by_px(?, ?) as x";
      $query = $this->db_smartone->query($sql, $sql_param);
      
      if ($query) {
         $r = $query->result_array()[0];
         $r = $r['x'];
         $this->sys_ok($r);

         exit;
      } else {
         $this->sys_error_db("get schedule", $this->db_smartone);

         exit;
      }  
   }

    public function search_cito()
    {
        $prm = $this->sys_input;

        $sql = "SELECT Nat_CitoID, Nat_CitoName, Nat_CitoIsDefault 
            FROM nat_cito WHERE Nat_CitoIsActive = 'Y'";
        $query = $this->db_smartone->query($sql);
     
        if ($query) 
        {
            $rows = $query->result_array();
            $this->sys_ok(["records"=>$rows]);
            return;
        }

        $this->sys_error_db("CITO", $this->db_smartone);
   }

    public function add_promise()
    {
        $prm = $this->sys_input;

        $this->sys_ok("OK");
    }
}