<?php
class Subcategory
 extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "Samplingverify API";
   }
   
   public function __construct()
   {
      parent::__construct();
      $this->db_onedev = $this->load->database("onedev", true);
	  $this->load->helper(array('form', 'url'));
   }
   
   public function search()
   {
        $prm = $this->sys_input;
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		
		$sql = 	"
					SELECT IFNULL(T_SubCategoryID,0) as id,
					T_SubCategoryID,
					T_SubCategoryName,
					T_SubCategoryT_CategoryID,
					T_CategoryID,
					T_CategoryName,
					T_SubCategoryIsPCR,
					T_SubCategoryIsSchedule,	
					T_SubCategoryIsKuota,
					IF(T_SubCategoryIsPCR = 'Y','Ya','Tidak') as ispcr,
					IF(T_SubCategoryIsSchedule = 'Y','Ya','Tidak') as isschedule,	
					IF(T_SubCategoryIsKuota = 'Y','Ya','Tidak') as iskuota
					FROM t_subcategory
					LEFT JOIN t_category ON T_CategoryID = T_SubCategoryT_CategoryID
					WHERE
						T_SubCategoryIsActive = 'Y'
					GROUP BY T_SubCategoryID
				";
		//echo $sql;
		$query = $this->db_onedev->query($sql);
		$rows = $query->result_array();
		
		$result = array("total" => count($rows), "records" => $rows, "sql"=> $this->db_onedev->last_query());
		$this->sys_ok($result);
		exit;
   }
   
   public function search_listing()
   {
        $prm = $this->sys_input;
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		
		$sql = 	"SELECT IFNULL(T_SubCategoryID,0) as id, 
					T_SubCategoryID,
					T_SubCategoryName,
					T_SubCategoryT_CategoryID,
					T_CategoryID,
					T_CategoryName,
					T_SubCategoryIsPCR,
					T_SubCategoryIsSchedule,	
					T_SubCategoryIsKuota,
					IF(T_SubCategoryIsPCR = 'Y','Ya','Tidak') as ispcr,
					IF(T_SubCategoryIsSchedule = 'Y','Ya','Tidak') as isschedule,	
					IF(T_SubCategoryIsKuota = 'Y','Ya','Tidak') as iskuota
					FROM t_subcategory
					LEFT JOIN t_category ON T_CategoryID = T_SubCategoryT_CategoryID
					WHERE
						T_SubCategoryIsActive = 'Y'
				";
		//echo $sql;
		$query = $this->db_onedev->query($sql);
		$rows = $query->result_array();
		
		$result = array("total" => count($rows), "records" => $rows, "sql"=> $this->db_onedev->last_query());
		$this->sys_ok($result);
		exit;
   }
   function selectcategory(){
	
		  try {
			 //# cek token valid
			 if (! $this->isLogin) {
				$this->sys_error("Invalid Token");
				exit;
			 }
			 $rows = [];
			 $query ="SELECT * 
				FROM t_category 
				WHERE	
				T_CategoryIsActive = 'Y'
				ORDER BY T_CategoryName asc";
			 //echo $query;
			 $rows['categorys'] = $this->db_onedev->query($query)->result_array();
	
	
			 $result = array(
				"total" => count($rows) , 
				"records" => $rows, 
			 ); 
			 $this->sys_ok($result);
	
	
		  } catch(Exception $exc) {
			 $message = $exc->getMessage();
			 $this->sys_error($message);
		  }
	
	   }
	function save(){
		try {
		   //# cek token valid
		   if (! $this->isLogin) {
			  $this->sys_error("Invalid Token");
			  exit;
		   }
  
		   //# ambil parameter input
		   $prm = $this->sys_input;
		   $T_SubCategoryName = $prm['name'];
		   $T_SubCategoryIsPCR = $prm['ispcr'];
		   $T_SubCategoryIsSchedule = $prm['isschedule'];
		   $T_SubCategoryIsKuota = $prm['iskuota'];
		   $userid = $this->sys_user["M_UserID"];
            
			  if($prm['xid'] == 0){
				  $query = "SELECT COUNT(*) as exist FROM t_subcategory WHERE T_SubCategoryIsActive = 'Y' AND T_SubCategoryName = '{$T_SubCategoryName}' AND T_SubCategoryT_CategoryID = '{$T_SubCategoryT_CategoryID}'";
				  $exist_code =  $this->db_onedev->query($query)->row()->exist; 
				  if($exist_code == 0){
					  $sql = "insert into t_subcategory(
						  T_SubCategoryName,
						  T_SubCategoryIsPCR,
						  T_SubCategoryIsSchedule,
						  T_SubCategoryIsKuota,
						  T_SubCategoryUserID,
						  T_SubCategoryLastUpdate,
						  T_SubCategoryCreated)
					   values(?,?,?,?,?,now(),now())";
		  $query = $this->db_onedev->query($sql,
		  array(	
			  $T_SubCategoryName,	
			  $T_SubCategoryIsPCR,
			  $T_SubCategoryIsSchedule,
			  $T_SubCategoryIsKuota,
			  $userid)
		  );
		  if (!$query) {
		  $this->sys_error_db("t_subcategory insert",$this->db_onedev);
		  exit;
		  }
		  $last_id = $this->db_onedev->insert_id();
		  $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'=>'Nama sudah ada yang pakai dong'));
					  }            
					  $result = array ("total" => -1,"errors" => $errors, "records" => 0);
					  $this->sys_ok($result);
				  }          
							  
			}else{
			  $query = "SELECT COUNT(*) as exist FROM t_subcategory WHERE T_SubCategoryIsActive = 'Y' AND T_SubCategoryName = '{$T_SubCategoryName}' AND T_SubCategoryT_CategoryID = '{$T_SubCategoryT_CategoryID}' AND T_SubCategoryID <> {$prm['xid']}";
			  //echo $query;
			  $exist_code =  $this->db_onedev->query($query)->row()->exist;
			  // echo $exist_code;
			   if($exist_code == 0){
				  $sql = "UPDATE t_subcategory SET 
				  T_SubCategoryName = '{$T_SubCategoryName}',
				  T_SubCategoryIsPCR = '{$T_SubCategoryIsPCR}',
				  T_SubCategoryIsSchedule = '{$T_SubCategoryIsSchedule}',
				  T_SubCategoryIsKuota = '{$T_SubCategoryIsKuota}',
				  T_SubCategoryUserID = '{$userid}'
				  WHERE T_SubCategoryID = '{$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(); 
				  if($exist_code != 0){
					  array_push($errors,array('field'=>'code','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 delete()
	 {
	  try {
		  //# cek token valid
		  if (! $this->isLogin) {
		  $this->sys_error("Invalid Token");
		  exit;
		  }
  
		  //# ambil parameter input
		  $prm = $this->sys_input;
		  $id = $prm['xid'];
		  $userid = $this->sys_user["M_UserID"];
			  $sql = "update t_subcategory SET 
			  T_SubCategoryIsActive = 'N',
			  T_SubCategoryUserID = $userid,
			  T_SubCategoryLastUpdate = now()
					  WHERE
					  T_SubCategoryID = $id";
	  
				  $query = $this->db_onedev->query($sql);
				  if (!$query) {
					  $this->sys_error_db("t_subcategory delete");
					  exit;
				  } 
				  $sqltest = "update t_subcategory_test SET 
				  T_SubcategoryTestIsActive = 'N',
				  T_SubcategoryTestUserID = $userid,
				  T_SubcategoryTestLastUpdated = now()
						  WHERE
						  T_SubcategoryTestT_SubcategoryID = $id";
		  
					  $querytest = $this->db_onedev->query($sqltest);
					 // echo $query;
					  if (!$querytest) {
						  $this->sys_error_db("t_subcategory_test delete");
						  exit;
					  } 
				 
		  $result = array ("total" => 1, "records" => array("xid" => 0));
		  $this->sys_ok($result);
  
	  } catch(Exception $exc) {
		  $message = $exc->getMessage();
		  $this->sys_error($message);
	  }
	 }
	
	function savejpagroup(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$userid = $this->sys_user["M_UserID"];
			$query = "insert into t_subcategory(
							T_SubCategoryName,
							T_SubCategoryUserID,
							T_SubCategoryCreated
						)
						VALUES(
							'{$prm['value']}',
							{$userid},
							NOW()
						)";
	
		//echo $query;
		$action = $this->db_onedev->query($query);
		
		if($action){
			$result = array(
				"total" => 1 , 
				"records" => array(), 
			); 
			$this->sys_ok($result);
			exit;
		}
		else{
			$this->sys_error_db($this->db_onedev->last_query(), $this->db_onedev);
			exit;
		}
		
	}
	
	
	function savelistingjpagroup(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$userid = $this->sys_user["M_UserID"];
			$query = "	UPDATE t_subcategory SET 
							T_SubCategoryName = '{$prm['name']}',
							T_SubCategoryIsActive = '{$prm['status']}',
							T_SubCategoryUserID = {$userid}
						WHERE
							T_SubCategoryID = {$prm['id']}
						";
	
		//echo $query;
		$action = $this->db_onedev->query($query);
		
		if($action){
			$result = array(
				"total" => 1 , 
				"records" => array(), 
			); 
			$this->sys_ok($result);
			exit;
		}
		else{
			$this->sys_error_db($this->db_onedev->last_query(), $this->db_onedev);
			exit;
		}
		
	}
	
	
	function savealljpadetail(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$details = $prm['details'];
		$userid = $this->sys_user["M_UserID"];
		foreach($details as $k => $v){
			$query = "insert into t_subcategory(
						Nat_JPADetailNat_JPAID,
						Nat_JPADetailT_SubCategoryID,
						Nat_JPADetailDiscount,
						Nat_JPADetailUserID,
						Nat_JPADetailCreated
					)
					VALUES(
						'{$v['Nat_JpaID']}',
						'{$v['T_SubCategoryID']}',
						'{$v['Nat_JPADetailDiscount']}',
						{$userid},
						NOW()
					) ON DUPLICATE KEY UPDATE 
						Nat_JPADetailDiscount = {$v['Nat_JPADetailDiscount']},
						Nat_JPADetailUserID = {$userid}";
			
		//echo $query;
		$action = $this->db_onedev->query($query);
		}
		
		
		if($action){
			$result = array(
				"total" => 1 , 
				"records" => array(), 
			); 
			$this->sys_ok($result);
			exit;
		}
		else{
			$this->sys_error_db($this->db_onedev->last_query(), $this->db_onedev);
			exit;
		}
		
	}
	
	
	
	
   
   
}