<?php

class Almarirack extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "ALMARI RACK API";
   }
   public function __construct()
   {
      parent::__construct();
      $this->db_onedev = $this->load->database("onedev", true);
   }
	
	
	function lookuprack(){
        try {
            //# cek token valid
            if (! $this->isLogin) {
				$this->sys_error("Invalid Token");
				exit;
            }
            $prm = $this->sys_input;
			$id = $prm['id'];
            $sql = "select M_RackID as id,
					M_RackID as almariid,
					M_RackCode as code,
					M_RackRows as xrows,
					M_RackColumns as xcols,
					'xxx' as action
                    from m_rack 
                    where 
                    M_RackM_AlmariID = {$id} AND M_RackIsActive = 'Y'";
					//echo $sql;
            $rows = $this->db_onedev->query($sql)->result();

            $result = array ("total" => count($rows), "records" => $rows);
			$this->sys_ok($result);


        } catch(Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
		}
	}
	
   public function lookup()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
        }

        $prm = $this->sys_input;
        $search = $prm['search'];
		$all = $prm['all'];
		$limit = '';
		if($all == 'N'){
			$limit = ' LIMIT 10';
		}
		$sql = "select COUNT(*) as total
                from m_almari 
                where 
                M_AlmariIsActive = 'Y'";
        $sql_param = array($search);
        $total = $this->db_onedev->query($sql,$sql_param)->row()->total;
		
		
        $sql = "select M_AlmariID as id, M_AlmariCode as code, M_AlmariName as name, CONCAT('[ ',M_AlmariCode,' ]',' ', M_AlmariName) as description , 'xxx' as almarirack
                from m_almari 
                where 
                ( 	M_AlmariCode LIKE CONCAT('%','{$search}','%') OR
					M_AlmariName LIKE CONCAT('%','{$search}','%') 
				)AND
                M_AlmariIsActive = 'Y' $limit";
        $sql_param = array($search);
        $query = $this->db_onedev->query($sql);
		//echo $this->db_onedev->last_query();
        if ($query) {
            $rows = $query->result_array();
			
            
        } else {
          $this->sys_error_db("m_almari select");
          exit;
        }

        $result = array ("total" => $total, "total_filter"=>count($rows),"records" => $rows);
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   
   
   public function addnewalmari()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
		$code = $prm['code'];
        $name = $prm['name'];
		$userid = $this->sys_user["M_UserID"];
		$query = "SELECT COUNT(*) as exist FROM m_almari WHERE M_AlmariIsActive = 'Y' AND M_AlmariCode = '{$code}'";
		$exist_code =  $this->db_onedev->query($query)->row()->exist;
		
		$query = "SELECT COUNT(*) as exist FROM m_almari WHERE M_AlmariIsActive = 'Y' AND M_AlmariName = '{$name}'";
		$exist_name =  $this->db_onedev->query($query)->row()->exist;
		
		
		//echo $exist_name;
		if($exist_code == 0 && $exist_name == 0){
			$sql = "insert into m_almari(
                    M_AlmariCode,
					M_AlmariName,
					M_AlmariCreated,
					M_AlmariLastUpdated,
					M_AlmariUserID
				) 
				values( ?, ?, now(), now(),?)";
			$query = $this->db_onedev->query($sql,
				array(
					$code,
					$name,
					$userid
				)
			);
			//echo $query;
			if (!$query) {
				$this->sys_error_db("m_almari insert");
				exit;
			} 
			
			$result = array ("total" => 1, "records" => array("xid" => 0));
			$this->sys_ok($result);
		}else{
			$errors = array(); 
			if($exist_code != 0){
				array_push($errors,array('field'=>'code','msg'=>'Kode sudah ada yang pakai dong'));
			}
			if($exist_name != 0){
				array_push($errors,array('field'=>'name','msg'=>'Nama sudah ada yang pakai dong'));
			}
			
			$result = array ("total" => -1,"errors" => $errors, "records" => 0);
			$this->sys_ok($result);
		}
        
		

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   
   public function editalmari()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
		$id_almari = $prm['id'];
		$code = $prm['code'];
        $name = $prm['name'];
		$userid = $this->sys_user["M_UserID"];
		
		$query = "SELECT COUNT(*) as exist FROM m_almari WHERE M_AlmariIsActive = 'Y' AND M_AlmariCode = '{$code}' AND M_AlmariID <> {$id_almari}";
		$exist_code =  $this->db_onedev->query($query)->row()->exist;
		//echo $this->db_onedev->last_query();
		$query = "SELECT COUNT(*) as exist FROM m_almari WHERE M_AlmariIsActive = 'Y' AND M_AlmariName = '{$name}' AND M_AlmariID <> {$id_almari}";
		$exist_name =  $this->db_onedev->query($query)->row()->exist;
        //echo $this->db_onedev->last_query();
		if($exist_code == 0 && $exist_name == 0){
			//echo 'IN';
			$sql = "update m_almari SET 
					M_AlmariCode = ?,
                    M_AlmariName = ?,
					M_AlmariLastUpdated = now()
				where
					M_AlmariID = ?
				";
			$query = $this->db_onedev->query($sql,
				array(
					$code,
					$name,
					$id_almari
				)
			);
			//echo $this->db_onedev->last_query();
			if (!$query) {
				//echo $this->db_onedev->last_query();
				$this->sys_error_db("m_almari update");
				exit;
			} 
			
			$result = array ("total" => 1, "records" => array("xid" => $id_almari));
			$this->sys_ok($result);
		}else{
			$errors = array(); 
			if($exist_code != 0){
				array_push($errors,array('field'=>'code','msg'=>'Kode sudah ada yang pakai dong'));
			}
			if($exist_name != 0){
				array_push($errors,array('field'=>'name','msg'=>'Nama sudah ada yang pakai dong'));
			}
			
			$result = array ("total" => -1,"errors" => $errors, "records" => 0);
			$this->sys_ok($result);
		}

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   
   public function addnewrack()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $almariid = $prm['almariid'];
		$code = $prm['code'];
		$rows = $prm['rows'];
		$cols = $prm['cols'];
		$userid = $this->sys_user["M_UserID"];

        if($prm['xid'] == "0" || $prm['xid'] == 0){
			$query = "SELECT COUNT(*) as exist FROM m_rack WHERE M_RackIsActive = 'Y' AND M_RackCode = '{$code}' AND M_RackM_AlmariID = '{$almariid}'";
			$exist_code =  $this->db_onedev->query($query)->row()->exist;

			if($exist_code == 0 && $exist_name == 0){
				$sql = "insert into m_rack(
						M_RackM_AlmariID,
						M_RackCode,
						M_RackRows,
						M_RackColumns,
						M_RackCreated,
						M_RackLastUpdated,
						M_RackUserID
					) 
					values( ?,?,?,?,now(),now(),?)";
				$query = $this->db_onedev->query($sql,
					array(
						$almariid,
						$code,
						$rows,
						$cols,
						$userid
					)
				);
				//echo $query;
				if (!$query) {
					//echo $this->db_onedev->last_query();
					$this->sys_error_db("m_rack insert");
					exit;
				} 
				$result = array ("total" => 1, "records" => array("xid" => 0));
				$this->sys_ok($result);
			}else{
				$errors = array(); 
				if($exist_code != 0){
					array_push($errors,array('field'=>'code','msg'=>'Kode sudah ada yang pakai dong'));
				}
				
				$result = array ("total" => -1,"errors" => $errors, "records" => 0);
				$this->sys_ok($result);
			}
		}else{
			$query = "SELECT COUNT(*) as exist FROM m_rack WHERE M_RackIsActive = 'Y' AND M_RackCode = '{$code}' AND M_RackM_AlmariID <> '{$almariid}'";
			$exist_code =  $this->db_onedev->query($query)->row()->exist;
			$query = "SELECT * FROM m_rack WHERE M_RackID = '{$prm['xid']}'";
			$data_rack = $this->db_onedev->query($query)->row();
			$exist_sample = array();
			if($data_rack->M_RackRows != $rows || $data_rack->M_RackColumns != $cols){
				$query = "SELECT * FROM summary_samplestorage JOIN t_samplestorage_detail ON T_SampleStorageDetailID = Summary_SampleStorageReffID WHERE Summary_SampleStorageM_RackID = '{$prm['xid']}' AND Summary_SampleStorageStatus = 'FILLED'";
				$exist_sample = $this->db_onedev->query($query)->result_array();
			}
			if($exist_code == 0 && count($exist_sample) == 0){
				$sql = "UPDATE m_rack SET M_RackCode = '{$code}', M_RackRows = '{$rows}', M_RackColumns = '{$cols}' , M_RackUserID = '{$userid}' WHERE M_RackID = '{$prm['xid']}'";
				//echo $sql;
				$query = $this->db_onedev->query($sql);
				$result = array ("total" => 1, "records" => array("xid" => 0));
				$this->sys_ok($result);
			}else{
				$errors = array(); 
				$error_sample = array();
				if($exist_code != 0){
					array_push($errors,array('field'=>'code','msg'=>'Kode sudah ada yang pakai dong'));
				}
				if(count($exist_sample) != 0){
					array_push($errors,array('field'=>'existsample','msg'=>'Masih ada sample di rak dong'));
					foreach($exist_sample as $k => $v){
						array_push($error_sample,array('msg'=>'Pada rak baris '.$v['Summary_SampleStorageRowPosition'].' kolom '.$v['Summary_SampleStorageColPosition'].' ada sample '.$v['T_SampleStorageDetailBarcode']));
					}
				}
				
				$result = array ("total" => -1,"errors" => $errors,"samples" => $error_sample,"records" => 0);
				$this->sys_ok($result);
			}
		}
        
        

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }

   
   public function deleterack()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $userid = $this->sys_user["M_UserID"];
		
		$query = "SELECT * FROM m_rack WHERE M_RackID = '{$prm['id']}'";
		$data_rack = $this->db_onedev->query($query)->row();
		$exist_sample = array();
		if($data_rack->M_RackRows != $rows || $data_rack->M_RackColumns != $cols){
			$query = "SELECT * FROM summary_samplestorage JOIN t_samplestorage_detail ON T_SampleStorageDetailID = Summary_SampleStorageReffID WHERE Summary_SampleStorageM_RackID = '{$prm['id']}' AND Summary_SampleStorageStatus = 'FILLED'";
			$exist_sample = $this->db_onedev->query($query)->result_array();
		}
		if(count($exist_sample) == 0){
			$sql = "update m_rack SET 
				M_RackIsActive = 'N',
				M_RackLastUpdated = now(),
				M_RackUserID = ?
				WHERE
				M_RackID = ?
				
				";

            $query = $this->db_onedev->query($sql,
                array(
					$userid,
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                $this->sys_error_db("m_rack delete");
                exit;
            }
			$result = array ("total" => 1, "records" => array("xid" => 0));
			$this->sys_ok($result);
		}else{
			if(count($exist_sample) != 0){
				$errors = array(); 
				$error_sample = array();
				array_push($errors,array('field'=>'existsample','msg'=>'Masih ada sample di rak dong'));
				foreach($exist_sample as $k => $v){
					array_push($error_sample,array('msg'=>'Pada rak baris '.$v['Summary_SampleStorageRowPosition'].' kolom '.$v['Summary_SampleStorageColPosition'].' ada sample '.$v['T_SampleStorageDetailBarcode']));
				}
			}
			
			$result = array ("total" => -1,"errors" => $errors,"samples" => $error_sample,"records" => 0);
			$this->sys_ok($result);
		}
			 

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   
   public function deletealmari()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $userid = $this->sys_user["M_UserID"];
		$exist_sample = array();
		
		$query = "	SELECT * 
					FROM summary_samplestorage 
					JOIN t_samplestorage_detail ON T_SampleStorageDetailID = Summary_SampleStorageReffID 
					JOIN m_rack ON Summary_SampleStorageM_RackID = M_RackID
					WHERE Summary_SampleStorageM_AlmariID = '{$prm['id']}' AND Summary_SampleStorageStatus = 'FILLED'";
		$exist_sample = $this->db_onedev->query($query)->result_array();
		if(count($exist_sample) == 0){
			$sql = "update m_almari SET 
				M_AlmariIsActive = 'N',
				M_AlmariLastUpdated = now(),
				M_AlmariUserID = ?
				WHERE
				M_AlmariID = ?
            
				";

            $query = $this->db_onedev->query($sql,
                array(
					$userid,
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                $this->sys_error_db("m_almari delete");
                exit;
            } 

			$sql = "UPDATE m_rack SET 
					M_RackIsActive = 'N',
					M_RackLastUpdated = now(),
					M_RackUserID = ?
					WHERE
					M_RackM_AlmariID = ?
				";
		
			$query = $this->db_onedev->query($sql,
				array(
					$userid ,
					$prm['id']
				)
			);
		   // echo $query;
			if (!$query) {
				$this->db_onedev->last_query();
				$this->sys_error_db("m_rack delete");
				exit;
			} 		
			
			$result = array ("total" => 1, "records" => array("xid" => 0));
			$this->sys_ok($result);
		}
		else{
			if(count($exist_sample) != 0){
				$errors = array(); 
				$error_sample = array();
				array_push($errors,array('field'=>'existsample','msg'=>'Masih ada sample di lemari dong'));
				foreach($exist_sample as $k => $v){
					array_push($error_sample,array('msg'=>'Pada rak '.$v['M_RackCode'].' baris '.$v['Summary_SampleStorageRowPosition'].' kolom '.$v['Summary_SampleStorageColPosition'].' ada sample '.$v['T_SampleStorageDetailBarcode']));
				}
			}
			
			$result = array ("total" => -1,"errors" => $errors,"samples" => $error_sample,"records" => 0);
			$this->sys_ok($result);
		}
        

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   
}