<?php
class Courierspk extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "COurierSpk API";
   }
   
   public function __construct()
   {
      parent::__construct();
      $this->db_onedev = $this->load->database("onedev", true);
   }
   
   public function search()
   {
		$prm = $this->sys_input;
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}

      $start_date = $prm["startdate"];
	  $end_date = $prm["enddate"];
	  $status = $prm['status'];
	  $prm['current_page'] = isset($prm['current_page'])?$prm['current_page']:1;
	  
      $sql_where = "WHERE (Result_CourierSPKDate BETWEEN CONCAT(?,' ','00:00:00') AND CONCAT(?,' ','23:59:59')) AND Result_CourierSPKIsActive = 'Y'";
      $sql_param = array($start_date,$end_date);
      
		$number_limit = 10;
		$number_offset = ($prm['current_page'] - 1) * $number_limit ;
        $sql = "	SELECT count(*) as total
				FROM result_courierspk
				$sql_where
				";
		//echo $sql;
		$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;
		}  
	  
		$sql = 	"SELECT Result_CourierSPKID as id,
					Result_CourierSPKNumbering as trx_number,
					CONCAT(DATE_FORMAT(Result_CourierSPKDate,'%d-%m-%Y'),' ',DATE_FORMAT(Result_CourierSPKTime,'%H:%i') ) as trx_datetime,
					Result_CourierSPKNote as trx_note,
					M_StaffName as courier_name,
					M_CourierID as courier_id,
					DATE_FORMAT(Result_CourierSPKDate,'%d-%m-%Y') as xdate,
					DATE_FORMAT(Result_CourierSPKTime,'%H:%i') as xtime,
					'' as details
				FROM result_courierspk
				JOIN m_courier ON Result_CourierSPKM_CourierID = M_CourierID
				JOIN m_staff ON M_CourierM_StaffID = M_StaffID
				$sql_where
				ORDER BY Result_CourierSPKNumbering 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){	
				$query = "	SELECT Result_CourierSPKDetailID as trx_id,
							T_OrderPromiseID as xid,
							T_OrderDeliveryID as delid,
							T_OrderHeaderID as orderid,
							T_OrderPromiseID as promiseid,
							Result_CourierSPKDetailForceDone as force_done,
							Result_CourierSPKDetailStatus as status,
							T_OrderHeaderLabNumber as labnumber,
							CONCAT(M_TitleName,' ',M_PatientName) as patientname,
							DATE_FORMAT(T_OrderHeaderDate,'%d-%m-%Y') as date_order,
							DATE_FORMAT(T_OrderPromiseDateTime,'%d-%m-%Y %H:%i') as date_promise,
							M_KelurahanName as kelurahan,
							T_OrderDeliveryDestination as xaddress,
							'Y' as flag_checked,
							'Y' as chex
							FROM result_courierspk_detail 
							JOIN t_orderdelivery ON T_OrderDeliveryID = Result_CourierSPKDetailT_OrderDeliveryID
							JOIN t_orderheader ON T_OrderDeliveryT_OrderHeaderID = T_OrderHeaderID
							JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
							LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
							JOIN t_orderpromise ON 	T_OrderDeliveryT_OrderHeaderID = T_OrderPromiseT_OrderHeaderID AND Result_CourierSPKDetailT_OrderPromiseID = T_OrderPromiseID
							JOIN m_kelurahan ON T_OrderDeliveryM_KelurahanID = M_KelurahanID
							JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID
							WHERE Result_CourierSPKDetailResult_CourierSPKID = '{$v['id']}' AND Result_CourierSPKDetailIsActive = 'Y'";
				//echo $query;
				$ddata = $this->db_onedev->query($query)->result_array();
				if($ddata){
					foreach($ddata as $i => $j){
						$ddata[$i]['chex'] = false;
					}
				}
				$rows[$k]['details'] = $ddata;
				
			}
		}
	  
	  
		//$this->_add_address($rows);
		$result = array("total" => $tot_page, "records" => $rows, "sql"=> $this->db_onedev->last_query());
		$this->sys_ok($result);
		exit;
   }
   
	function getcourier(){
	   if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		
		$rows = [];
		$query ="	SELECT M_CourierID as id, M_StaffName as name
					FROM m_courier
					JOIN m_staff ON M_CourierM_StaffID = M_StaffID 
					WHERE	
						M_StaffIsActive = 'Y'
				";
				//echo $query;
		$rows = $this->db_onedev->query($query)->result_array();
		

		$result = array(
			"total" => count($rows) , 
			"records" => $rows, 
		); 
		$this->sys_ok($result);
		exit;
   }
   
   function searchall()
   {
      $prm = $this->sys_input;
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}

      $xdate = $prm["xdate"];
	  $kecamatan = $prm["kecamatan"];
	  $filter_kecamatan = '';
	  $details = $prm['details'];
	  $deliveries_id = array();
	  $promises_id = array();
	  $join_deliveries = '';
	  $join_promises = '';
	  if($details){
		foreach($details as $xk => $xv){
			array_push($deliveries_id,$xv['delid']);
			array_push($promises_id,$xv['promiseid']);
		}
		
		$join_deliveries = "(".join($deliveries_id,',').")";
		$join_promises = "(".join($promises_id,',').")";
	  }
	  
	  $sql_join_deliveries = '';
	  $sql_join_promise = '';
	  
	  if($deliveries_id){
			foreach($deliveries_id as $k => $v){
				if($sql_join_deliveries != '')
					$sql_join_deliveries .= ' AND ';
				
				//$sql_join_deliveries .= ' ( T_OrderDeliveryID <> '.$v.' AND T_OrderPromiseID <> '.$promises_id[$k].' ) ';
				$sql_join_deliveries .= " ( CONCAT(T_OrderDeliveryID,'-',T_OrderPromiseID) <> '{$v}-{$promises_id[$k]}' ) ";
				//echo $sql;
			}
			$sql_join_deliveries = " AND ({$sql_join_deliveries})";
			//echo $sql_join_deliveries;
	  }
	  
	  if($kecamatan['id'] != '0' || $kecamatan['id'] != 0){
		  $filter_kecamatan = " AND M_DistrictID = {$kecamatan['id']}";
	  }
	  $prm['current_page'] = isset($prm['current_page'])?$prm['current_page']:1;
	  $number_limit = 10;
		$number_offset = ($prm['current_page'] - 1) * $number_limit ;
      $sql_where = "WHERE  ISNULL(Result_CourierSPKDetailID) AND T_OrderPromiseDateTime < '{$xdate} 23:59:59' AND T_OrderDeliveryM_DeliveryTypeID = 2 AND T_OrderDeliveryIsActive = 'Y' {$filter_kecamatan}";
      

      $sql = "	SELECT count(*) as total
					FROM (
					SELECT * FROM t_orderdelivery
					JOIN t_orderheader ON T_OrderDeliveryT_OrderHeaderID = T_OrderHeaderID
					JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
					LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
					JOIN t_orderpromise ON 	T_OrderDeliveryT_OrderHeaderID = T_OrderPromiseT_OrderHeaderID  AND T_OrderPromiseIsActive = 'Y'
					JOIN m_kelurahan ON T_OrderDeliveryM_KelurahanID = M_KelurahanID
					JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID
					LEFT JOIN result_courierspk_detail ON Result_CourierSPKDetailT_OrderDeliveryID = T_OrderDeliveryID AND 
						Result_CourierSPKDetailIsActive = 'Y' AND 
						Result_CourierSPKDetailForceDone = 'N' AND 
						Result_CourierSPKDetailT_OrderPromiseID = T_OrderPromiseID 
					$sql_where $sql_join_deliveries 
					GROUP BY T_OrderDeliveryID,T_OrderPromiseID
				) x";
		//echo $sql;
		$query = $this->db_onedev->query($sql);

		$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;
		}  
	  
		$sql = 	"SELECT IFNULL(Result_CourierSPKDetailID,0) as trx_id,
					T_OrderPromiseID as xid,
					T_OrderDeliveryID as delid,
					T_OrderHeaderID as orderid,
					T_OrderPromiseID as promiseid,
					T_OrderHeaderLabNumber as labnumber,
					CONCAT(IFNULL(M_TitleName,''),' ',M_PatientName) as patientname,
					T_OrderDeliveryDestination as xaddress,
					DATE_FORMAT(T_OrderHeaderDate,'%d-%m-%Y') as date_order,
					DATE_FORMAT(T_OrderPromiseDateTime,'%d-%m-%Y %H:%i') as date_promise,
					T_OrderPromiseDateTime,
					M_KelurahanName as kelurahan,
					'N' as flag_checked,
					'N' as chex
					FROM t_orderdelivery
					JOIN t_orderheader ON T_OrderDeliveryT_OrderHeaderID = T_OrderHeaderID
					JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
					LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
					JOIN t_orderpromise ON 	T_OrderHeaderID = T_OrderPromiseT_OrderHeaderID AND T_OrderPromiseIsActive = 'Y'
					JOIN m_kelurahan ON T_OrderDeliveryM_KelurahanID = M_KelurahanID
					JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID
					LEFT JOIN result_courierspk_detail ON Result_CourierSPKDetailT_OrderDeliveryID = T_OrderDeliveryID AND 
						Result_CourierSPKDetailIsActive = 'Y' AND 
						Result_CourierSPKDetailForceDone = 'N' AND 
						Result_CourierSPKDetailT_OrderPromiseID = T_OrderPromiseID 
					$sql_where $sql_join_deliveries 
					GROUP BY T_OrderDeliveryID,T_OrderPromiseID
					ORDER BY T_OrderPromiseDateTime ASC, T_OrderHeaderID ASC
					limit $number_limit offset $number_offset
					";

		$query = $this->db_onedev->query($sql);
		//echo $this->db_onedev->last_query();
		$rows = $query->result_array();
		if($rows){
			foreach($rows as $k => $v){
				$rows[$k]['chex'] = false;
			}
		}
	  
	  
		//$this->_add_address($rows);
		$result = array("total" => $tot_page, "records" => $rows, "sql"=> $this->db_onedev->last_query());
		$this->sys_ok($result);
		exit;
   }
   
   function getkecamatan(){
	    $prm = $this->sys_input;
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		
		$rows = [];
		$query ="	SELECT M_DistrictID as id, M_DistrictName as name
					FROM t_orderdelivery
					JOIN t_orderpromise ON 	T_OrderDeliveryT_OrderHeaderID = T_OrderPromiseT_OrderHeaderID AND 
											T_OrderPromiseDateTime < '{$prm['xdate']}'
					JOIN m_kelurahan ON T_OrderDeliveryM_KelurahanID = M_KelurahanID
					JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID
					WHERE	
						T_OrderDeliveryM_DeliveryID = 2 AND T_OrderDeliveryIsActive = 'Y'
					GROUP BY M_DistrictID
				";
		//	echo $query;	
		$rows = $this->db_onedev->query($query)->result_array();
		array_push($rows,array('id'=>0,'name'=>'Semua Kecamatan'));

		$result = array(
			"total" => count($rows) , 
			"records" => $rows, 
		); 
		$this->sys_ok($result);
		exit;
   }
   
   function getrackcontent(){
	   if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$rows = [];
		$query ="	SELECT Summary_SampleStorageM_AlmariID as almarid, Summary_SampleStorageM_RackID as rackid , Summary_SampleStorageRowPosition as row, Summary_SampleStorageColPosition as col
					FROM summary_samplestorage 
					WHERE	
						Summary_SampleStorageStatus = 'FILLED' AND 
						Summary_SampleStorageM_AlmariID = {$prm['almariid']} AND 
						Summary_SampleStorageM_RackID = {$prm['rackid']}
				";
				//echo $query;
		$filledrows = $this->db_onedev->query($query)->result_array();
		$query ="	SELECT {$prm['almariid']} as almariid, 
						M_RackID as id, 
						CONCAT(M_RackCode,' ( ',M_RackRows,' x ',M_RackColumns,' )') as name, 
						M_RackCode as code, 
						M_RackRows as row, 
						M_RackColumns as col,
						'' as rackcontens
					FROM m_rack 
					WHERE	
						M_RackID = {$prm['rackid']} AND M_RackIsActive = 'Y' 
				";
				//echo $query;
		$datarows = $this->db_onedev->query($query)->row_array();

		$rows = $datarows['row'];
		$cols = $datarows['col'];
		$rackcontens = array();
		for ($x = 1; $x <= $rows; $x++) {
			$children = array();
			for ($i = 1; $i <= $cols; $i++) {
				$content = $x.' x '.$i;
				$xrow = $x;
				$xcol = $i;
				$status = $this->checkexistfilled($filledrows, $datarows['id'],$xrow,$xcol);
				$selected = 'N';
				if($xcol == $prm['col'] && $xrow == $prm['row']){
					$selected = 'T';
				}
				array_push($children,array('content'=>$content,'row'=>$xrow,'col'=>$xcol,'status'=>$status,'selected'=>$selected));
			}
			array_push($rackcontens,$children);
		} 

		$result = array(
			"total" => count($rackcontens) , 
			"records" => $rackcontens, 
		); 
		$this->sys_ok($result);
		exit;
   }
   
   
   
   function checkexistfilled($datas,$rackid,$row,$col){
	   $rtn = 'N';
	   foreach($datas as $k => $v){
		   if($v['rackid'] == $rackid && $v['row'] == $row && $v['col'] == $col){
			   $rtn = 'Y';
		   }
	   }
	   return $rtn;
   }
   
   function save(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$userid = $this->sys_user["M_UserID"];
		$last_id = $prm['trx_id'];
		if($last_id == 0 || $last_id == '0'){
			$numbering = $this->db_onedev->query("SELECT fn_numbering('DK') as numbering")->row()->numbering;
			$xdate = date('Y-m-d',strtotime($prm["trx_date"]));
			
			$query ="INSERT INTO result_courierspk (
							Result_CourierSPKNumbering,
							Result_CourierSPKDate,
							Result_CourierSPKTime,
							Result_CourierSPKM_CourierID,
							Result_CourierSPKNote,
							Result_CourierSPKUserID,
							Result_CourierSPKCreated
						)
						VALUES(
							'{$numbering}',
							'{$xdate}',
							'{$prm['trx_time']}',
							'{$prm['courier']}',
							'{$prm['trx_note']}',
							'{$userid}',
							NOW()
						)
					";
				//echo $query;
			$saveheader = $this->db_onedev->query($query);
			$last_id = $this->db_onedev->insert_id();
		}
		else{
			$numbering = $this->db_onedev->query("SELECT * FROM result_courierspk WHERE Result_CourierSPKID = {$last_id}")->row()->Result_CourierSPKNumbering;
			$xdate = date('Y-m-d',strtotime($prm["trx_date"]));
			$query ="UPDATE result_courierspk SET
							Result_CourierSPKDate = '{$xdate}',
							Result_CourierSPKTime = '{$prm['trx_time']}',
							Result_CourierSPKM_CourierID = '{$prm['courier']}',
							Result_CourierSPKNote = '{$prm['trx_note']}',
							Result_CourierSPKUserID = {$userid}
					WHERE
						Result_CourierSPKID = {$last_id}
					";
				//echo $query;
			$saveheader = $this->db_onedev->query($query);
		}
		
		if($saveheader){
			//$sql = "UPDATE result_courierspk_detail SET Result_CourierSPKDetailIsActive = 'N' WHERE Result_CourierSPKDetailResult_CourierSPKID = {$last_id}";
			//$this->db_onedev->query($sql);
			if($prm['details']){
				foreach($prm['details'] as $k => $v){
					if($v['trx_id'] == '0' || $v['trx_id'] == 0){
						$query ="INSERT INTO result_courierspk_detail (
							Result_CourierSPKDetailResult_CourierSPKID,
							Result_CourierSPKDetailT_OrderDeliveryID,
							Result_CourierSPKDetailT_OrderPromiseID,
							Result_CourierSPKDetailsUserID,
							Result_CourierSPKDetailsCreated
						)
						VALUES(
							'{$last_id}',
							'{$v['delid']}',
							'{$v['xid']}',
							'{$userid}',
							NOW()
						)
						ON DUPLICATE KEY UPDATE 
							Result_CourierSPKDetailIsActive = 'Y',
							Result_CourierSPKDetailsUserID = {$userid}";
						//echo $query;
						$savedetail = $this->db_onedev->query($query);
					}
				}
			}
			
			if($prm['deleted_details']){
				foreach($prm['deleted_details'] as $k => $v){
					$sql = "UPDATE result_courierspk_detail SET Result_CourierSPKDetailIsActive = 'N' WHERE Result_CourierSPKDetailID = {$v['trx_id']}";
					//echo $sql;
					$this->db_onedev->query($sql);
				}
			}
			
		}
		
		$sql = "SELECT * FROM result_courierspk WHERE Result_CourierSPKID = {$last_id}";
		$data_log_header = $this->db_onedev->query($sql)->result();
		$sql = "SELECT * FROM result_courierspk_detail WHERE Result_CourierSPKDetailResult_CourierSPKID = {$last_id}";
		$data_log_details = $this->db_onedev->query($sql)->result();
		
		$data_log = json_encode(array('header'=>$data_log_header,'details'=>$data_log_details));
		$sql = "INSERT INTO one_log.log_courierspk (
					Log_CourierSpkJSON,
					Log_CourierSpkUserID
				)
				VALUES(
					'{$data_log}',
					{$userid}
				)";
		//echo $sql;
		$this->db_onedev->query($sql);
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK'),
			"numbering" => $numbering,
			"id" => $last_id 
		); 
		$this->sys_ok($result);
		exit;
	}
	
	
   function checkbarcode(){
	   if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$datarows = [];
		$query ="	SELECT CONCAT(M_TitleName,' ',M_PatientName) as patientname, T_OrderSampleID
					FROM t_ordersample
					JOIN t_orderheader ON T_OrderSampleT_OrderHeaderID = T_OrderHeaderID
					JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
					LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
					WHERE	T_OrderSampleBarcode = '{$prm['barcode']}' AND T_OrderSampleIsActive = 'Y' ORDER BY T_OrderSampleID DESC LIMIT 1
				";
				//echo $query;
		$datarows = $this->db_onedev->query($query)->row();

		$result = array(
			"total" => count($datarows) , 
			"records" => $datarows, 
		); 
		$this->sys_ok($result);
		exit;
   }
   
    /*function lookupsamples()
   {
		$prm = $this->sys_input;
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}

		$search = $prm["search"];
		if($search){
			$sql_where = "WHERE ( T_OrderHeaderLabNumber LIKE CONCAT('%',?,'%') OR M_PatientName LIKE CONCAT('%',?,'%')) AND Summary_SampleStorageStatus = 'FILLED'";
			$sql_param = array($search,$search);
		}
		else{
			$sql_where = "WHERE Summary_SampleStorageStatus = 'FILLED'";
			$sql_param = array();
		}	  
      
      

      $sql = "	SELECT count(*) as total
				FROM summary_samplestorage
				JOIN t_samplestorage_detail ON T_SampleStorageDetailID = Summary_SampleStorageReffID
				JOIN t_ordersample ON T_OrderSampleID = T_SampleStorageDetailT_OrderSampleID
				JOIN t_orderheader ON T_OrderSampleT_OrderHeaderID = T_OrderHeaderID
				JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
				LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
				JOIN m_almari ON Summary_SampleStorageM_AlmariID = M_AlmariID
				JOIN m_rack ON Summary_SampleStorageM_AlmariID = M_RackID
				$sql_where
				";
		//echo $sql;
		$query = $this->db_onedev->query($sql, $sql_param);

		$tot_count = 0;
		if ($query) {
			$tot_count = $query->result_array()[0]["total"];
		} else {
			$this->sys_error_db("t_samplestorageout count", $this->db_onedev);
			exit;
		}  
	  
		$sql = 	"SELECT Summary_SampleStorageID as id,
				T_SampleStorageDetailBarcode as barcode,
				'' as almari,
				'' as rack,
				Summary_SampleStorageRowPosition as row,
				Summary_SampleStorageColPosition as col,
				m_almari.*,
				m_rack.*,
				CONCAT(M_TitleName,' ',M_PatientName) as patientname, T_SampleStorageDetailT_OrderSampleID as ordersampleid
				FROM summary_samplestorage
				JOIN t_samplestorage_detail ON T_SampleStorageDetailID = Summary_SampleStorageReffID
				JOIN t_ordersample ON T_OrderSampleID = T_SampleStorageDetailT_OrderSampleID
				JOIN t_orderheader ON T_OrderSampleT_OrderHeaderID = T_OrderHeaderID
				JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
				LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
				JOIN m_almari ON Summary_SampleStorageM_AlmariID = M_AlmariID
				JOIN m_rack ON Summary_SampleStorageM_RackID = M_RackID
				$sql_where
				ORDER BY T_OrderHeaderID ASC
				limit 0,20";

		$query = $this->db_onedev->query($sql, $sql_param);
		//echo $this->db_onedev->last_query();
		$rows = $query->result();
		if($rows){
			foreach($rows as $kx => $vx){
				$vx->almari = array('id'=>$vx->M_AlmariID,'name'=>'[ '.$vx->M_AlmariCode.' ] '.$vx->M_AlmariName,'code'=>$vx->M_AlmariCode);
				$vx->rack = array('id'=>$vx->M_RackID,'name'=>$vx->M_RackCode.' ( '.$vx->M_RackRows.' x '.$vx->M_RackColumns.' )','code'=>$vx->M_RackCode);
			}
		}
	  
	  
		//$this->_add_address($rows);
		$result = array("total" => $tot_count, "records" => $rows, "sql"=> $this->db_onedev->last_query());
		$this->sys_ok($result);
		exit;
   }*/
   
   function lookupsamples()
   {
		$prm = $this->sys_input;
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}

		$search = $prm["search"];
		if($search){
			$sql_where = "WHERE ( T_OrderHeaderLabNumber LIKE CONCAT('%',?,'%') OR M_PatientName LIKE CONCAT('%',?,'%')) AND Summary_SampleStorageStatus = 'FILLED'";
			$sql_param = array($search,$search);
		}
		else{
			$sql_where = "WHERE Summary_SampleStorageStatus = 'FILLED'";
			$sql_param = array();
		}	  
      
      

      $sql = "	SELECT count(*) as total
				FROM summary_samplestorage
				JOIN t_ordersample ON T_OrderSampleID = Summary_SampleStorageT_OrderSampleID
				JOIN t_orderheader ON T_OrderSampleT_OrderHeaderID = T_OrderHeaderID
				JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
				LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
				JOIN m_almari ON Summary_SampleStorageM_AlmariID = M_AlmariID
				JOIN m_rack ON Summary_SampleStorageM_AlmariID = M_RackID
				$sql_where
				";
		//echo $sql;
		$query = $this->db_onedev->query($sql, $sql_param);

		$tot_count = 0;
		if ($query) {
			$tot_count = $query->result_array()[0]["total"];
		} else {
			$this->sys_error_db("t_samplestorageout count", $this->db_onedev);
			exit;
		}  
	  
		$sql = 	"SELECT Summary_SampleStorageID as id,
				T_OrderSampleBarcode as barcode,
				'' as almari,
				'' as rack,
				Summary_SampleStorageRowPosition as row,
				Summary_SampleStorageColPosition as col,
				m_almari.*,
				m_rack.*,
				CONCAT(IFNULL(M_TitleName,''),' ',M_PatientName) as patientname, Summary_SampleStorageT_OrderSampleID as ordersampleid
				FROM summary_samplestorage
				JOIN t_ordersample ON T_OrderSampleID = Summary_SampleStorageT_OrderSampleID
				JOIN t_orderheader ON T_OrderSampleT_OrderHeaderID = T_OrderHeaderID
				JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
				LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
				JOIN m_almari ON Summary_SampleStorageM_AlmariID = M_AlmariID
				JOIN m_rack ON Summary_SampleStorageM_RackID = M_RackID
				$sql_where
				ORDER BY T_OrderHeaderID ASC
				limit 0,20";

		$query = $this->db_onedev->query($sql, $sql_param);
		//echo $this->db_onedev->last_query();
		$rows = $query->result();
		if($rows){
			foreach($rows as $kx => $vx){
				$vx->almari = array('id'=>$vx->M_AlmariID,'name'=>'[ '.$vx->M_AlmariCode.' ] '.$vx->M_AlmariName,'code'=>$vx->M_AlmariCode);
				$vx->rack = array('id'=>$vx->M_RackID,'name'=>$vx->M_RackCode.' ( '.$vx->M_RackRows.' x '.$vx->M_RackColumns.' )','code'=>$vx->M_RackCode);
			}
		}
	  
	  
		//$this->_add_address($rows);
		$result = array("total" => $tot_count, "records" => $rows, "sql"=> $this->db_onedev->last_query());
		$this->sys_ok($result);
		exit;
   }
}