<?php
class Base_model extends CI_Model 
{
	public $table = '';
	public $pk = '';
	public $code_field = '';
	public $limit = 0;
	public $offset = 0;
	public $where = array();
	public $like = array();
	
    function __construct() 
	{
        parent::__construct();
    }
	
	function menu(){
		$q = "SELECT *
				FROM tb_menu";
		$rs = $this->db->query($q)->result();
		
		return $rs;
	}
	function count() 
	{
		$data = array();
		$this->db->where($this->where);
		$this->db->or_like($this->like);
		
		$query = $this->db->get($this->tbl.' t1');
		
		return $query->num_rows();
	}
	
	function get($where) 
	{
		$data = array();
	
		if ( is_array($where) )
		{
			foreach ($where as $key => $value)
			{
				$this->db->where('t1.'.$key, $value);
			}
		}
		else
		{
			$this->db->where('t1.'.$this->pk, $where);
		}
		
		$query = $this->db->get($this->tbl.' t1');
        if ( $query->num_rows() == 1)
		{
            $data = $query->row_array();
			$query->free_result();
		}else
		{
			$data = $this->feed_blank();
		}
		return $data;
	}
	
	public function set_table($table)
	{
		$this->tbl = $table;
	}
	
	public function set_pk($table)
	{
		$this->pk = $table;
	}
	
	public function set_code($table)
	{
		$this->code_field = $table;
	}
	
	public function set_limit($limit=0)
	{
		if ($limit > 0)
			$this->limit = $limit;
	}
	
	public function set_offset($offset=0)
	{
		if ($offset > 0)
			$this->offset = $offset;
	}
	
	public function set_where($where=array())
	{
		if ($where)
			$this->where = $where;
	}
	
	public function set_like($like=array())
	{
		if ($like)
			$this->like = $like;
	}
	
	function save($data,$act=null)
	{
		$return = 0;
		$action = '';
		$data = $this->clean_data($data);		
		if($data[$this->pk])
		{
			$action = 'Edit Data';
			// update data
			$where[$this->pk] = $data[$this->pk];
			$this->db->where($where);		
			$update = $this->db->update($this->tbl,$data);			
			if ($update)
				$return = $data[$this->pk];
		}else
		{
			$action = 'New Data';
			// insert new row
			$this->db->insert($this->tbl, $data);		
			$return = $this->db->insert_id();
		}
		
		if ($return)
		{
			$data_log = array (
					'user_id'	=> $this->session->userdata('id_mst_user'),
					'audit_table'	=> $this->tbl,
					'row_id'	=> $return,
					'action'	=>($act?$act:$action));
			$this->db->insert('tr_auditrail', $data_log); 
		}
		
		return $return;
	}
	
	// function insert new data
	public function insert($data = null)
	{
		return $this->db->insert($this->tbl, $data);
	}
	
	public function delete($where = NULL, $table = FALSE)
	{
		$table = (FALSE !== $table) ? $table : $this->tbl;
		
		if ( is_array($where) )
		{
			$this->db->where($where);
		}
		else
		{
			$this->db->where($this->pk, $where);
		}
	
		$this->db->delete($table);
				
		return (int) $this->db->affected_rows();
		//return $this->db->last_query();
	}
	
	function del($id){
		$this->db->delete($this->tbl, array($this->pk => $id)); 		
		if ($this->db->affected_rows()>0){
			$rs="success";
			$log=$this->addLog($id,"Hapus ".$this->title." ".$id);	
		} else {
			$rs="failed";
		}		
		
		return $rs;		
	}
	
	function delDet($id,$child_no){
		$this->db->delete($this->tbl2, array($this->pk => $id,$this->pk2 => $child_no)); 		
		if ($this->db->affected_rows()>0){
			$rs=true;
			$log=$this->addLog($id.'-'.$child_no,"Hapus ".$this->title." ".$id.'-'.$child_no);	
		} else {
			$rs=false;
		}		
		
		return $rs;		
	}
	
	function clean_data($value, $table = FALSE)
	{
		/* $data = $value;
		*/
		$data = array();
		foreach($value as $key => $val)
        {	
            if(!is_array($val))
            {
                $data[$key]     = $val;
                $data[$key]     = strip_image_tags($data[$key]);
                $data[$key]     = quotes_to_entities($data[$key]);
                $data[$key]     = encode_php_tags($data[$key]);
                $data[$key]     = trim($data[$key]);
            }
        }		
       
		$cleaned_data = array();
	
		if ( ! empty($data))
		{
			$table = ($table !== FALSE) ? $table : $this->tbl;
			
			$fields = $this->db->list_fields($table);
			
			$fields = array_fill_keys($fields,'');
	
			$cleaned_data = array_intersect_key($data, $fields);
		}
		return $cleaned_data;
	}
	
	function feed_blank()
	{
		$template = array();
		$fields = $this->db->list_fields($this->tbl);
		$fields_data = $this->field_data($this->tbl);
		foreach ($fields as $field)
		{
			$field_data = array_values(array_filter($fields_data, create_function('$row', 'return $row["Field"] == "'. $field .'";')));
			$field_data = (isset($field_data[0])) ? $field_data[0] : false;
			$template[$field] = (isset($field_data['Default'])) ? $field_data['Default'] : '';
		}
		return $template;
	}
	
	function field_data($table)
	{
		return $this->db->list_fields($table);
	}
	
	function dmys2ymd($date){
		if ($date!=""){
			list($day, $month, $year) = explode('/', $date);		
			return $year."-".str_pad($month, 2, "0", STR_PAD_LEFT)."-".str_pad($day, 2, "0", STR_PAD_LEFT);
		} else {
			return "0000-00-00";
		}
	}
	
	
	function wims_currency($number,$dec_point) { 
	   if ($number < 0) { 
		 $print_number = "(" . str_replace('-', '', number_format ($number, $dec_point)) . ")"; 
		} else { 
		 $print_number = number_format ($number, $dec_point); 
	   } 
	   return $print_number; 
	} 
	
	/**
	 * Create the data output array for the DataTables rows
	 *
	 *  @param  array $columns Column information array
	 *  @param  array $data    Data from the SQL get
	 *  @return array          Formatted data in a row based format
	 */
	static function data_output ( $columns, $data, $start=0 )
	{
		$out = array();
		$no_urut=$start+1;
		for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
			$row = array();
			
			for ( $j=0, $jen=count($columns) ; $j<$jen ; $j++ ) {
				$column = $columns[$j];
				// Is there a formatter?
				if ( isset( $column['formatter'] ) ) {
					$row[ $column['dt'] ] = $column['formatter']( $data[$i][ $column['db'] ], $data[$i] );
				}
				else {
					$row[0] = $no_urut;
					$row[ $column['dt'] ] = $data[$i][ $columns[$j]['db'] ];
				}				
			}
			$out[] = $row;
			$no_urut++;	
		}
		return $out;
	}
	
	/**
	 * Paging
	 *
	 * Construct the LIMIT clause for server-side processing SQL query
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array $columns Column information array
	 *  @return string SQL limit clause
	 */
	static function limit ( $request, $columns )
	{
		$limit = '';
		if ( isset($request['start']) && $request['length'] != -1 ) {
			$limit = "LIMIT ".intval($request['start']).", ".intval($request['length']);
		}
		return $limit;
	}
	/**
	 * Ordering
	 *
	 * Construct the ORDER BY clause for server-side processing SQL query
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array $columns Column information array
	 *  @return string SQL order by clause
	 */
	static function order ( $request, $columns )
	{
		$order = '';
		if ( isset($request['order']) && count($request['order']) ) {
			$orderBy = array();
			$dtColumns = self::pluck( $columns, 'dt' );
			for ( $i=0, $ien=count($request['order']) ; $i<$ien ; $i++ ) {
				// Convert the column index into the column data property
				$columnIdx = intval($request['order'][$i]['column']);
				$requestColumn = $request['columns'][$columnIdx];
				$columnIdx = array_search( $requestColumn['data'], $dtColumns );
				$column = $columns[ $columnIdx ];
				if ( $requestColumn['orderable'] == 'true' ) {
					$dir = $request['order'][$i]['dir'] === 'asc' ?
						'ASC' :
						'DESC';
					$orderBy[] = '`'.$column['db'].'` '.$dir;
				}
			}
			$order = 'ORDER BY '.implode(', ', $orderBy);
		}
		return $order;
	}
	
	/**
	 * Searching / Filtering
	 *
	 * Construct the WHERE clause for server-side processing SQL query.
	 *
	 * NOTE this does not match the built-in DataTables filtering which does it
	 * word by word on any field. It's possible to do here performance on large
	 * databases would be very poor
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array $columns Column information array
	 *  @param  array $bindings Array of values for PDO bindings, used in the
	 *    sql_exec() function
	 *  @return string SQL where clause
	 */
	public function filter ( $request, $columns, &$bindings )
	{
		$globalSearch = array();
		$columnSearch = array();
		$dtColumns = $this->pluck( $columns, 'dt' );
		if ( isset($request['search']) && $request['search']['value'] != '' ) {
			$str = mysql_real_escape_string($request['search']['value']);
			for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
				$requestColumn = $request['columns'][$i];
				$columnIdx = array_search( $requestColumn['data'], $dtColumns );
				$column = $columns[ $columnIdx ];
				if ( $requestColumn['searchable'] == 'true' ) {
					//$binding = $this->bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
					$binding = '%'.$str.'%';
					$globalSearch[] = "`".$column['db']."` LIKE '".$binding."'";
				}
			}
		}
		// Individual column filtering
		for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
			$requestColumn = $request['columns'][$i];
			$columnIdx = array_search( $requestColumn['data'], $dtColumns );
			$column = $columns[ $columnIdx ];
			$str = $requestColumn['search']['value'];
			if ( $requestColumn['searchable'] == 'true' &&
			 $str != '' ) {
				//$binding = $this->bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
				$binding = '%'.$str.'%';
				$columnSearch[] = "`".$column['db']."` LIKE '".$binding."'";
			}
		}
		// Combine the filters into a single string
		$where = '';
		if ( count( $globalSearch ) ) {
			$where = '('.implode(' OR ', $globalSearch).')';
		}
		if ( count( $columnSearch ) ) {
			$where = $where === '' ?
				implode(' AND ', $columnSearch) :
				$where .' AND '. implode(' AND ', $columnSearch);
		}
		if ( $where !== '' ) {
			$where = 'WHERE '.$where;
		} else {
			$where = 'WHERE 1=1';
		}
		return $where;
	}
	
	/**
	 * Pull a particular property from each assoc. array in a numeric array, 
	 * returning and array of the property values from each item.
	 *
	 *  @param  array  $a    Array to get data from
	 *  @param  string $prop Property to read
	 *  @return array        Array of property values
	 */
	function pluck ( $a, $prop )
	{
		$out = array();
		for ( $i=0, $len=count($a) ; $i<$len ; $i++ ) {
			$out[] = $a[$i][$prop];
		}
		return $out;
	}
	
	/**
	 * Create a PDO binding key which can be used for escaping variables safely
	 * when executing a query with sql_exec()
	 *
	 * @param  array &$a    Array of bindings
	 * @param  *      $val  Value to bind
	 * @param  int    $type PDO field type
	 * @return string       Bound key to be used in the SQL where this parameter
	 *   would be used.
	*/
	private function bind ( &$a, $val, $type )
	{
		$key = ':binding_'.count( $a );
		$a[] = array(
			'key' => $key,
			'val' => $val,
			'type' => $type
		);
		return $key;
	}	
	
	function edit($data,$pk){
		$run = $this->db->update($this->tbl, $data, $pk); 
		$log=$this->addLog($pk['id'],"Ubah ".$this->title." Header ", $pk);
		return (int) $this->db->affected_rows();
	}

	function editarap($data,$pk){
		$run = $this->db->update($this->tbl, $data, $pk); 
		$log=$this->addLog($pk['id'],"Ubah ".$this->title." Header, ID = ".$pk['id'].", DESC = ".$data['description'].", RATE = ".$data['rate']);
		return (int) $this->db->affected_rows();
	}

	function editbrbp($data,$pk){
		$run = $this->db->update($this->tbl0, $data, $pk);
		$log=$this->addLog($pk['id'],"Ubah ".$this->title." Header, ID = ".$pk['id'].", DESC = ".$data['description'].", RATE = ".$data['rate']); 
		return (int) $this->db->affected_rows();
	}
	
	function editHdr($dt_hdr,$pk){
		try {
			$dt_hdr['mat_type']=$this->mat_type;
			$dt_hdr['wh_from']=$this->wh_from;
			$dt_hdr['wh_to']=$this->wh_to;
			
			$this->db->update($this->tbl, $dt_hdr, $pk); 
			//if ($this->db->affected_rows()>0){
				$log=$this->addlog($dt_hdr[$this->pk],"Ubah ".$this->title_ket." ".$dt_hdr[$this->pk_human]);
				
				$success=true;
				$msg='Ubah data berhasil.';  				
			/*} else {
				$success=false;
				$msg='Ubah data header gagal.'.$this->db->last_query();
			}*/
		 	return array('success'=>$success,'msg'=>$msg);  
			
		} catch (Exception $e) {			
			return array(
				'success'=>false,
				'msg'=>"Ubah data gagal.".$dt_hdr[$this->pk]."\r\n".$Exception->getMessage()
			);
			exit(0);
			//alert the user then kill the process
		  	//var_dump($e->getMessage());
			
		}		
	}
	
	function editDet($aksi,$dt_det,$pk){
		try {			
			if ($aksi=='t'){
				$this->db->insert($this->tbl2, $dt_det); 
			} else {
				$this->db->update($this->tbl2, $dt_det, $pk); 
			}
			
			//if ($this->db->affected_rows()>0){
				$log=$this->addlog(
					$dt_det[$this->pk],
					"Ubah ".$this->title_ket." ".implode(',',$pk)
				);
				
				$success=true;
				$msg='Ubah data berhasil.';  				
			/*} else {
				$success=false;
				$msg='Ubah data header gagal.'.$this->db->last_query();
			}*/
		 	return array('success'=>$success,'msg'=>$msg);  
			
		} catch (Exception $e) {			
			return array(
				'success'=>false,
				'msg'=>"Ubah data gagal.".$dt_hdr[$this->pk]."\r\n".$Exception->getMessage()
			);
			exit(0);
			//alert the user then kill the process
		  	//var_dump($e->getMessage());
			
		}		
	}
	
	function addDet2($table,$dt_det,$pk){
		try {			
			$this->db->insert($table, $dt_det); 
			$cek=$this->db->last_query();
			if ($this->db->affected_rows()>0){
				$log=$this->addlog(
					$dt_det[$this->pk],
					"Ubah ".$this->title." ".implode(',',$pk)
				);
				
				$success=true;
				$msg='Ubah data berhasil.';  				
			} else {
				$success=false;
				$msg='Ubah data gagal.'.$cek;
			}
		 	return array('success'=>$success,'msg'=>$msg);  
			
		} catch (Exception $e) {			
			return array(
				'success'=>false,
				'msg'=>"Ubah data gagal.".$pk."\r\n".$Exception->getMessage()
			);
			exit(0);
			//alert the user then kill the process
		  	//var_dump($e->getMessage());
			
		}		
	}
	
	function editDet2($aksi,$dt_det,$pk,$table){
		try {			
			if ($aksi=='t'){
				$this->db->insert($table, $dt_det); 
			} else {
				$this->db->update($table, $dt_det, $pk); 
			}
			
			//if ($this->db->affected_rows()>0){
				$log=$this->addlog(
					$dt_det[$this->pk],
					"Ubah ".$this->title_ket." ".implode(',',$pk)
				);
				
				$success=true;
				$msg='Ubah data berhasil.';  				
			/*} else {
				$success=false;
				$msg='Ubah data header gagal.'.$this->db->last_query();
			}*/
		 	return array('success'=>$success,'msg'=>$msg);  
			
		} catch (Exception $e) {			
			return array(
				'success'=>false,
				'msg'=>"Ubah data gagal.".$pk."\r\n".$Exception->getMessage()
			);
			exit(0);
			//alert the user then kill the process
		  	//var_dump($e->getMessage());
			
		}		
	}
	
	function getRow($id){
		$q="SELECT * FROM ".$this->tbl." WHERE ".$this->pk."='$id'";
		$rs=$this->db->query($q)->result();
		return $rs[0];
	}
	
	function addlog($crud_id,$ketlog){
		$tgl=date('Y-m-d H:i:s');
		$usr=$this->session->userdata('username');
		
		$q = "INSERT INTO log VALUES (0,'$tgl','$usr','$crud_id','$ketlog')";
		$rs = $this->db->query($q);
	}
	
	function addLogSubkon($crud_id,$ketlog){
		$tgl=date('Y-m-d H:i:s');
		$usr=$this->session->userdata('username');
		
		$q = "INSERT INTO log_subkon VALUES (0,'$tgl','$usr','$crud_id','$ketlog')";
		$rs = $this->db->query($q);
	}
	
	function addColor($color){
		$q="INSERT IGNORE mt_color VALUES ('$color')";
		$run = $this->db->query($q);
	}
	
	function getStockIns($data){
		$aksi=($data['aksi']=='undefined')?'t':$data['aksi'];
		$date=($data['date']=='undefined')?'':$this->dmys2ymd($data['date']);
		$prodcard_id=($data['prodcard_id']=='undefined')?'':$data['prodcard_id'];
		$lot_no=($data['lot_no']=='undefined')?'':$data['lot_no'];
		$color=($data['color']=='undefined')?'':$data['color'];
		$size=($data['size']=='undefined')?'':$data['size'];
		$qty=($data['qty']=='undefined')?'':$data['qty'];
		
		$q01="SELECT MAX(prodcard_date) AS beg_date 
			  FROM prod_cardhdr 
			  WHERE prodcard_id='$prodcard_id' 
			  LIMIT  1";
		$rssa=$this->db->query($q01)->result_array();
		$beg_date=($rssa[0]['beg_date']=="")?"0000-00-00":$rssa[0]['beg_date'];
	
	
		$q = "SELECT mat.`prodcard_id`,mat.`prodcard_no`,
		  SUM(IFNULL(tr_out.`qty_out`,0)-IFNULL(tr_in.`qty_in`,0)) AS qty_end,
		  FORMAT(SUM(IFNULL(tr_out.`qty_out`,0)-IFNULL(tr_in.`qty_in`,0)),2) AS fqty_end ";
		$q .= "FROM prod_cardhdr mat 
		   LEFT JOIN (SELECT tr_head.prodcard_id,SUM(tr_det.`qty`) AS qty_in
					  FROM `fg_incdet` tr_det INNER JOIN `fg_inchdr` tr_head
					  ON tr_det.`fgin_id` = tr_head.`fgin_id`
					  WHERE tr_head.`mat_type` =  '".$this->mat_type."'
					  AND prod_to='".$this->prod."' 
					  AND tr_det.color='$color' AND tr_det.size='$size'
					  AND tr_head.lot_no='$lot_no'
					  AND tr_head.fgin_type IN ('4','5') 
					  AND tr_head.`fgin_date` >= '$beg_date' AND tr_head.`fgin_date` <= '$date'
					  GROUP BY tr_head.`prodcard_id`) AS tr_in		  
		   ON mat.`prodcard_id` = tr_in.prodcard_id 
		   LEFT JOIN (SELECT tr_head.prodcard_id,SUM(tr_det.`qty`) AS qty_out
					  FROM `fg_outdet` tr_det
					  INNER JOIN `fg_outhdr` tr_head
					  ON tr_det.`fgout_id` = tr_head.`fgout_id`					  
					  WHERE tr_head.`mat_type` =  '".$this->mat_type."' 
					  AND prod_to='".$this->prod."'
					  AND tr_det.color='$color' AND tr_det.size='$size'	
					  AND tr_head.lot_no='$lot_no'				  
					  AND tr_head.`fgout_date` >= '$beg_date' AND tr_head.`fgout_date` <= '$date'
					  GROUP BY tr_head.`prodcard_id`) AS tr_out
		   ON mat.`prodcard_id` = tr_out.prodcard_id
		   WHERE mat.prodcard_id = '$prodcard_id'
		   GROUP BY mat.prodcard_id
		   ORDER BY prodcard_id ASC";
		$rows = $this->db->query($q)->result_array();
		
		if ($aksi=='t'){
			$qty_end=$rows[0]['qty_end'];
		} else {
			$qty_end=$rows[0]['qty_end']+$data['qty'];
		}
		
		return $qty_end;
	}
	
	
	
	function getKstok($page,$rows,$key='',$dtdari='',$dtsampai='',$color='',$size=''){
		$dtdari = $this->dmys2ymd($dtdari);
		$dtsampai = $this->dmys2ymd($dtsampai);
		
		$offset = ($page-1)*$rows;
		$result = array();
		
		
		$q01="SELECT MAX(prodcard_date) AS beg_date 
			  FROM prod_cardhdr 
			  WHERE prodcard_id='$key' 
			  LIMIT  1";
		$rssa=$this->db->query($q01)->result_array();
		$beg_date=($rssa[0]['beg_date']=="")?"0000-00-00":$rssa[0]['beg_date'];
	
	
		$q = "SELECT mat.`prodcard_id`,mat.`prodcard_no`,
		  SUM(IFNULL(tr_in.`qty_in`,0) - IFNULL(tr_out.`qty_out`,0)) AS qty_beg,
		  FORMAT(SUM(IFNULL(tr_in.`qty_in`,0) - IFNULL(tr_out.`qty_out`,0)),2) AS fqty_beg ";
	$q .= "FROM prod_cardhdr mat 
		   LEFT JOIN (SELECT tr_head.prodcard_id,SUM(tr_det.`qty`) AS qty_in
					  FROM `fg_incdet` tr_det INNER JOIN `fg_inchdr` tr_head
					  ON tr_det.`fgin_id` = tr_head.`fgin_id`
					  WHERE tr_head.`mat_type` =  '".$this->mat_type."'
					  AND color LIKE '%$color%' AND tr_det.size LIKE '%$size%'
					  AND prod_to='".$this->prod."' AND tr_head.fgin_type IN ('4','5') 
					  AND tr_head.`fgin_date` > '$beg_date' AND tr_head.`fgin_date` < '$dtdari'
					  GROUP BY tr_head.`prodcard_id`) AS tr_in		  
		   ON mat.`prodcard_id` = tr_in.prodcard_id 
		   LEFT JOIN (SELECT tr_head.prodcard_id,SUM(tr_det.`qty`) AS qty_out
					  FROM `fg_outdet` tr_det
					  INNER JOIN `fg_outhdr` tr_head
					  ON tr_det.`fgout_id` = tr_head.`fgout_id`
					  AND prod_from='".$this->prod."'
					  WHERE tr_head.`mat_type` =  '".$this->mat_type."' 
					  AND color LIKE '%$color%' AND tr_det.size LIKE '%$size%'
					  AND tr_head.`fgout_date` > '$beg_date' AND tr_head.`fgout_date` < '$dtdari'
					  GROUP BY tr_head.`prodcard_id`) AS tr_out
		   ON mat.`prodcard_id` = tr_out.prodcard_id
		   WHERE mat.prodcard_id = '$key'
		   GROUP BY mat.prodcard_id
		   ORDER BY prodcard_id ASC";
		//echo $q;   
		$rows=$this->db->query($q)->result_array();
		foreach($rows as $r) {
			$balance = $r['qty_beg'];
			$fbalance = $r['fqty_beg'];
		}
	
	$q="SELECT * FROM (
		SELECT tr_head.`prodcard_id`,fgin_no AS trans_no,fgin_date AS date,DATE_FORMAT(fgin_date,'%d/%m/%Y') AS dmy,
		matin_name AS tipe,
		NmPrshn AS buspart,
		color,
		size,
		'D' AS dbcd,
		qty AS debit,
		FORMAT(qty,2) AS fdebit,
		0 AS credit,
		0 AS fcredit,
		0 AS saldo
		FROM `fg_incdet` tr_det 
		INNER JOIN `fg_inchdr` tr_head	ON tr_det.`fgin_id` = tr_head.`fgin_id`
		LEFT JOIN ms_in_type min ON min.matin_type=tr_head.fgin_type
		LEFT JOIN ms_perusahaan pt ON pt.KdPrshn=tr_head.supplier
		WHERE tr_head.`mat_type` =  '".$this->mat_type."' AND tr_head.`prodcard_id`='$key'
		AND color LIKE '%$color%' AND tr_det.size LIKE '%$size%'
		AND prod_to='".$this->prod."' AND tr_head.fgin_type IN ('4','5')
		AND tr_head.`fgin_date` BETWEEN  '".$dtdari."' AND '".$dtsampai."'
	UNION ALL
		SELECT tr_head.`prodcard_id`,fgout_no AS trans_no,fgout_date AS date,DATE_FORMAT(fgout_date,'%d/%m/%Y') AS dmy,
		matout_name AS tipe,
		NmPrshn AS buspart,	
		color,
		size,
		'C' AS dbcd,
		0 AS debit,
		0 AS fdebit,
		qty AS credit,
		FORMAT(qty,2) AS fcredit,
		0 AS saldo
		FROM `fg_outdet` tr_det
		INNER JOIN `fg_outhdr` tr_head ON tr_det.`fgout_id` = tr_head.`fgout_id`
		LEFT JOIN ms_out_type mout ON mout.matout_type=tr_head.fgout_type
		LEFT JOIN ms_perusahaan pt ON pt.KdPrshn=tr_head.cust
		WHERE tr_head.`mat_type` =  '".$this->mat_type."' AND tr_head.`prodcard_id`='$key' 
		AND color LIKE '%$color%' AND tr_det.size LIKE '%$size%'
		AND prod_from='".$this->prod."'
		AND tr_head.`fgout_date` BETWEEN '".$dtdari."' AND '".$dtsampai."'
		) AS gabungan ORDER BY date ASC, dbcd DESC
		";	
		//echo $q;
	
		$rs=$this->db->query($q)->result_array();
		
		$temp=array();		
		$saldoarr=array(		
			'kd_barang'	=> '',
			'trans_no'	=> '',
			'date'		=> '',
			'dmy'		=> $period_from,
			'tipe'	=> 'Last Balance',
			'buspart'	=> '',
			'color'	=> '',
			'size'	=> '',
			'debit'		=> 	'',
			'fdebit'	=> 	'',
			'credit'	=> '',
			'fcredit'	=> '',
			'saldo'		=> number_format($balance,2)
		);
		array_push($temp,$saldoarr);
		foreach ($rs as $r){		
			$balance=$balance + ($r['debit'] - $r['credit']);
			$r['saldo']=number_format($balance,2);
			array_push($temp,$r);
		}
		
		return $temp;
	}
	
	function getKstokQc($page,$rows,$key='',$dtdari='',$dtsampai='',$color='',$size=''){
		$dtdari = $this->dmys2ymd($dtdari);
		$dtsampai = $this->dmys2ymd($dtsampai);
		
		$offset = ($page-1)*$rows;
		$result = array();
		
		
		$q01="SELECT MAX(prodcard_date) AS beg_date 
			  FROM prod_cardhdr 
			  WHERE prodcard_id='$key' 
			  LIMIT  1";
		$rssa=$this->db->query($q01)->result_array();
		$beg_date=($rssa[0]['beg_date']=="")?"0000-00-00":$rssa[0]['beg_date'];
	
	
		$q = "SELECT mat.`prodcard_id`,mat.`prodcard_no`,
		  SUM(IFNULL(tr_in.`qty_in`,0) - IFNULL(tr_out.`qty_out`,0)) AS qty_beg,
		  FORMAT(SUM(IFNULL(tr_in.`qty_in`,0) - IFNULL(tr_out.`qty_out`,0)),2) AS fqty_beg ";
	$q .= "FROM prod_cardhdr mat 
		   LEFT JOIN (SELECT tr_head.prodcard_id,SUM(tr_det.`qty`) AS qty_in
					  FROM `fg_outdet` tr_det INNER JOIN `fg_outhdr` tr_head
					  ON tr_det.`fgout_id` = tr_head.`fgout_id`
					  WHERE tr_head.`mat_type` =  '".$this->mat_type."'
					  AND color LIKE '%$color%' AND tr_det.size LIKE '%$size%'
					  AND prod_to='".$this->prod."'
					  AND tr_head.`fgout_date` > '$beg_date' AND tr_head.`fgout_date` < '$dtdari'
					  GROUP BY tr_head.`prodcard_id`) AS tr_in		  
		   ON mat.`prodcard_id` = tr_in.prodcard_id 
		   LEFT JOIN (SELECT tr_head.prodcard_id,SUM(tr_det.`qty`) AS qty_out
					  FROM `fg_outdet` tr_det
					  INNER JOIN `fg_outhdr` tr_head
					  ON tr_det.`fgout_id` = tr_head.`fgout_id`
					  AND prod_from='".$this->prod."'
					  WHERE tr_head.`mat_type` =  '".$this->mat_type."' 
					  AND color LIKE '%$color%' AND tr_det.size LIKE '%$size%'
					  AND tr_head.`fgout_date` > '$beg_date' AND tr_head.`fgout_date` < '$dtdari'
					  GROUP BY tr_head.`prodcard_id`) AS tr_out
		   ON mat.`prodcard_id` = tr_out.prodcard_id
		   WHERE mat.prodcard_id = '$key'
		   GROUP BY mat.prodcard_id
		   ORDER BY prodcard_id ASC";
		//echo $q;   
		$rows=$this->db->query($q)->result_array();
		foreach($rows as $r) {
			$balance = $r['qty_beg'];
			$fbalance = $r['fqty_beg'];
		}
	
	$q="SELECT * FROM (
		SELECT tr_head.`prodcard_id`,fgout_no AS trans_no,fgout_date AS date,DATE_FORMAT(fgout_date,'%d/%m/%Y') AS dmy,
		matout_name AS tipe,
		NmPrshn AS buspart,
		color,
		size,
		'D' AS dbcd,
		qty AS debit,
		FORMAT(qty,2) AS fdebit,
		0 AS credit,
		0 AS fcredit,
		0 AS saldo
		FROM `fg_outdet` tr_det 
		INNER JOIN `fg_outhdr` tr_head	ON tr_det.`fgout_id` = tr_head.`fgout_id`
		LEFT JOIN ms_out_type min ON min.matout_type=tr_head.fgout_type
		LEFT JOIN ms_perusahaan pt ON pt.KdPrshn=tr_head.cust
		WHERE tr_head.`mat_type` =  '".$this->mat_type."' AND tr_head.`prodcard_id`='$key'
		AND color LIKE '%$color%' AND tr_det.size LIKE '%$size%'
		AND prod_to='".$this->prod."'
		AND tr_head.`fgout_date` BETWEEN  '".$dtdari."' AND '".$dtsampai."'
	UNION ALL
		SELECT tr_head.`prodcard_id`,fgout_no AS trans_no,fgout_date AS date,DATE_FORMAT(fgout_date,'%d/%m/%Y') AS dmy,
		matout_name AS tipe,
		NmPrshn AS buspart,	
		color,
		size,
		'C' AS dbcd,
		0 AS debit,
		0 AS fdebit,
		qty AS credit,
		FORMAT(qty,2) AS fcredit,
		0 AS saldo
		FROM `fg_outdet` tr_det
		INNER JOIN `fg_outhdr` tr_head ON tr_det.`fgout_id` = tr_head.`fgout_id`
		LEFT JOIN ms_out_type mout ON mout.matout_type=tr_head.fgout_type
		LEFT JOIN ms_perusahaan pt ON pt.KdPrshn=tr_head.cust
		WHERE tr_head.`mat_type` =  '".$this->mat_type."' AND tr_head.`prodcard_id`='$key' 
		AND color LIKE '%$color%' AND tr_det.size LIKE '%$size%'
		AND prod_from='".$this->prod."'
		AND tr_head.`fgout_date` BETWEEN '".$dtdari."' AND '".$dtsampai."'
		) AS gabungan ORDER BY date ASC, dbcd DESC
		";	
		//echo $q;
	
		$rs=$this->db->query($q)->result_array();
		
		$temp=array();		
		$saldoarr=array(		
			'kd_barang'	=> '',
			'trans_no'	=> '',
			'date'		=> '',
			'dmy'		=> $period_from,
			'tipe'	=> 'Last Balance',
			'buspart'	=> '',
			'color'	=> '',
			'size'	=> '',
			'debit'		=> 	'',
			'fdebit'	=> 	'',
			'credit'	=> '',
			'fcredit'	=> '',
			'saldo'		=> number_format($balance,2)
		);
		array_push($temp,$saldoarr);
		foreach ($rs as $r){		
			$balance=$balance + ($r['debit'] - $r['credit']);
			$r['saldo']=number_format($balance,2);
			array_push($temp,$r);
		}
		
		return $temp;
	}
	
	function getKstokSi($page,$rows,$key='',$dtdari='',$dtsampai='',$color='',$size=''){
		$dtdari = $this->dmys2ymd($dtdari);
		$dtsampai = $this->dmys2ymd($dtsampai);
		
		$offset = ($page-1)*$rows;
		$result = array();
		
		
		$q01="SELECT MAX(prodcard_date) AS beg_date 
			  FROM prod_cardhdr 
			  WHERE prodcard_id='$key' 
			  LIMIT  1";
		$rssa=$this->db->query($q01)->result_array();
		$beg_date=($rssa[0]['beg_date']=="")?"0000-00-00":$rssa[0]['beg_date'];
	
	
		$q = "SELECT mat.`prodcard_id`,mat.`prodcard_no`,
		  SUM(IFNULL(tr_in.`qty_in`,0) - IFNULL(tr_out.`qty_out`,0)) AS qty_beg,
		  FORMAT(SUM(IFNULL(tr_in.`qty_in`,0) - IFNULL(tr_out.`qty_out`,0)),2) AS fqty_beg ";
		$q .= "FROM prod_cardhdr mat 
		   LEFT JOIN (SELECT tr_head.prodcard_id,SUM(tr_det.`qty`) AS qty_in
					  FROM `fg_outdet` tr_det INNER JOIN `fg_outhdr` tr_head
					  ON tr_det.`fgout_id` = tr_head.`fgout_id`
					  WHERE tr_head.`mat_type` =  '".$this->mat_type."'
					  AND prod_to='".$this->prod."' AND tr_head.fgout_type IN ('0','2')
					  AND color LIKE '%$color%' AND tr_det.size LIKE '%$size%' 
					  AND tr_head.`fgout_date` > '$beg_date' AND tr_head.`fgout_date` < '$dtdari'
					  GROUP BY tr_head.`prodcard_id`) AS tr_in		  
		   ON mat.`prodcard_id` = tr_in.prodcard_id 
		   LEFT JOIN (SELECT tr_head.prodcard_id,SUM(tr_det.`qty`) AS qty_out
					  FROM `fg_incdet` tr_det
					  INNER JOIN `fg_inchdr` tr_head
					  ON tr_det.`fgin_id` = tr_head.`fgin_id`
					  AND prod_to='".$this->prod."'
					  WHERE tr_head.`mat_type` =  '".$this->mat_type."' 
					  AND color LIKE '%$color%' AND tr_det.size LIKE '%$size%'
					  AND tr_head.`fgin_date` > '$beg_date' AND tr_head.`fgin_date` < '$dtdari'
					  GROUP BY tr_head.`prodcard_id`) AS tr_out
		   ON mat.`prodcard_id` = tr_out.prodcard_id
		   WHERE mat.prodcard_id = '$key'
		   GROUP BY mat.prodcard_id
		   ORDER BY prodcard_id ASC";
		//echo $q;   
		$rows=$this->db->query($q)->result_array();
		foreach($rows as $r) {
			$balance = $r['qty_beg'];
			$fbalance = $r['fqty_beg'];
		}
	
	$q="SELECT * FROM (
		SELECT tr_head.`prodcard_id`,fgout_no AS trans_no,fgout_date AS date,DATE_FORMAT(fgout_date,'%d/%m/%Y') AS dmy,
		matout_name AS tipe,
		NmPrshn AS buspart,
		color,
		size,
		'D' AS dbcd,
		qty AS debit,
		FORMAT(qty,2) AS fdebit,
		0 AS credit,
		0 AS fcredit,
		0 AS saldo
		FROM `fg_outdet` tr_det 
		INNER JOIN `fg_outhdr` tr_head	ON tr_det.`fgout_id` = tr_head.`fgout_id`
		LEFT JOIN ms_out_type min ON min.matout_type=tr_head.fgout_type
		LEFT JOIN ms_perusahaan pt ON pt.KdPrshn=tr_head.cust
		WHERE tr_head.`mat_type` =  '".$this->mat_type."' AND tr_head.`prodcard_id`='$key'
		AND color LIKE '%$color%' AND tr_det.size LIKE '%$size%'
		AND prod_to='".$this->prod."' AND tr_head.fgout_type IN ('0','2')
		AND tr_head.`fgout_date` BETWEEN  '".$dtdari."' AND '".$dtsampai."'
	UNION ALL
		SELECT tr_head.`prodcard_id`,fgin_no AS trans_no,fgin_date AS date,DATE_FORMAT(fgin_date,'%d/%m/%Y') AS dmy,
		matin_name AS tipe,
		NmPrshn AS buspart,	
		color,
		size,
		'C' AS dbcd,
		0 AS debit,
		0 AS fdebit,
		qty AS credit,
		FORMAT(qty,2) AS fcredit,
		0 AS saldo
		FROM `fg_incdet` tr_det
		INNER JOIN `fg_inchdr` tr_head ON tr_det.`fgin_id` = tr_head.`fgin_id`
		LEFT JOIN ms_in_type mout ON mout.matin_type=tr_head.fgin_type
		LEFT JOIN ms_perusahaan pt ON pt.KdPrshn=tr_head.supplier
		WHERE tr_head.`mat_type` =  '".$this->mat_type."' AND tr_head.`prodcard_id`='$key' 
		AND color LIKE '%$color%' AND tr_det.size LIKE '%$size%'
		AND prod_to='".$this->prod."'
		AND tr_head.`fgin_date` BETWEEN '".$dtdari."' AND '".$dtsampai."'
		) AS gabungan ORDER BY date ASC, dbcd DESC, dbcd DESC
		";	
		//echo $q;
	
		$rs=$this->db->query($q)->result_array();
		
		$temp=array();		
		$saldoarr=array(		
			'kd_barang'	=> '',
			'trans_no'	=> '',
			'date'		=> '',
			'dmy'		=> $period_from,
			'tipe'	=> 'Last Balance',
			'buspart'	=> '',
			'color'	=> '',
			'size'	=> '',
			'debit'		=> 	'',
			'fdebit'	=> 	'',
			'credit'	=> '',
			'fcredit'	=> '',
			'saldo'		=> number_format($balance,2)
		);
		array_push($temp,$saldoarr);
		foreach ($rs as $r){		
			$balance=$balance + ($r['debit'] - $r['credit']);
			$r['saldo']=number_format($balance,2);
			array_push($temp,$r);
		}
		
		return $temp;
	}
	
}