<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Laporan_model extends CI_Model
{
	var $columnLaporan = array('customers.customer_code',
								'customers.customer_name',
								'products.product_code',
								'products.product_name',
								'barcode.create_date', 
								'barcode.total_print');

	private function _get_laporan_query()
  	{
  		
  		$this->db->select("customers.customer_code AS 'customerCode', 
  							customers.customer_name AS 'customerName',
  							products.product_code AS 'productCode', 
  							products.product_name AS 'productName',
  			 				barcode.create_date AS 'createDate', 
  			 				barcode.total_print AS 'totalPrint'");
  		$this->db->join('customers', 'barcode.customer_code=customers.customer_code', 'left');
  		$this->db->join('products', 'barcode.product_code=products.product_code', 'left');
	    $this->db->from('barcode');

	    $i = 0;

		foreach ($this->columnLaporan as $item) // loop column
		{

			if(!empty($_POST['search']['value'])) // if datatable send POST for search
			{

				if($i===0) // first loop
				{
					$this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
					$this->db->like($item, $_POST['search']['value']);
				}
				else
				{
					$this->db->or_like($item, $_POST['search']['value']);
				}

				if(count($this->columnLaporanDetail) - 1 == $i) //last loop
					$this->db->group_end(); //close bracket
			}
			$columnLaporanDetail[$i] = $item; // set column array variable to order processing
			$i++;
		}

		if(!empty($this->input->post('customerCode'))){
			$parts = explode('-', $this->input->post('customerCode'));
			$customerCode = $parts[0];

			if(!empty($this->input->post('productCode'))){
				$parts = explode('-', $this->input->post('productCode'));
				$productCode = $parts[0];
				if(!empty($this->input->post('dateFrom')) && !empty($this->input->post('dateTo'))){
					$dateFrom = $this->cDate($this->input->post('dateFrom'));
					$dateTo = $this->cDate($this->input->post('dateTo'));
					$where2 = "customers.customer_code = '".$customerCode."' and products.product_code = '".$productCode."' and date(barcode.create_date) >= '".$dateFrom."' and date(barcode.create_date) <= '".$dateTo."'";
					$this->db->where($where2);
				}else{
					$where2 = "customers.customer_code = '".$customerCode."' and products.product_code = '".$productCode."' ";
					$this->db->where($where2);
				}
			}else{
				$where2 = "customers.customer_code = '".$customerCode."' ";
				$this->db->where($where2);
			}


		}
		else if(!empty($this->input->post('productCode'))){
			$parts = explode('-', $this->input->post('productCode'));
			$productCode = $parts[0];
			if(!empty($this->input->post('dateFrom')) && !empty($this->input->post('dateTo'))){
				$dateFrom = $this->cDate($this->input->post('dateFrom'));
				$dateTo = $this->cDate($this->input->post('dateTo'));
				$where2 = "products.product_code = '".$productCode."' and (date(barcode.create_date) >= '".$dateFrom."' and date(barcode.create_date) <= '".$dateTo."')";
				$this->db->where($where2);
			}else{
				$where2 = "products.product_code = '".$productCode."' and date(barcode.create_date)";
				$this->db->where($where2);
			}
		}else if(!empty($this->input->post('dateFrom')) && !empty($this->input->post('dateTo'))){
			$parts = explode('-', $this->input->post('customerCode'));
			$customerCode = $parts[0];
			$parts = explode('-', $this->input->post('productCode'));
			$productCode = $parts[0];
			$dateFrom = $this->cDate($this->input->post('dateFrom'));
			$dateTo = $this->cDate($this->input->post('dateTo'));
			$where2 = "date(barcode.create_date) >= '".$dateFrom."' and date(barcode.create_date) <= '".$dateTo."'";
			$this->db->where($where2);
		}

		if(isset($_POST['order'])) // here order processing
		{
			$this->db->order_by($columnLaporanDetail[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
		}
		else if(isset($this->order))
		{
			$order = $this->order;
			$this->db->order_by(key($order), $order[key($order)]);
		}
  	}

  	public function get_laporan()
	{
		$this->_get_laporan_query();
		if(!empty($_POST['length'])){
			if($_POST['length'] != -1)
			$this->db->limit($_POST['length'], $_POST['start']);
		}else if(!empty($this->input->post('length')) && !empty($this->input->post('start'))){
			$this->db->limit($this->input->post('length'), $this->input->post('start'));
		}
		$query = $this->db->get();
		return $query->result();
	}

  	public function count_laporan_filtered()
	{
		$this->_get_laporan_query();
		$query = $this->db->get();
		return $query->num_rows();
	}

	public function count_laporan_all()
	{
		$this->db->from('barcode');
		return $this->db->count_all_results();
	}

	private function cDate($date)
    {
        $thn=substr($date,6,4);
		$bln=substr($date,0,2);
		$tgl=substr($date,3,2);
		$tanggal=$thn."-".$bln."-".$tgl;
		return $tanggal;
    }


    public function get_summary()
    {

    	$query='';
    	$sql='';
    	if(!empty($this->input->post('customerCode')))
    	{
    		$parts = explode('-', $this->input->post('customerCode'));
			$customerCode = $parts[0];

			if(!empty($this->input->post('productCode')))
			{
				$parts = explode('-', $this->input->post('productCode'));
				$productCode = $parts[0];
				if(!empty($this->input->post('dateFrom')) && !empty($this->input->post('dateTo')))
				{
					$dateFrom = $this->cDate($this->input->post('dateFrom'));
					$dateTo = $this->cDate($this->input->post('dateTo'));
					$sql="SELECT c.customer_code, 
	    	 			  c.customer_name, b.product_code, b.product_name,  
	    	 			  Sum(a.total_print) as Total FROM barcode a
					INNER JOIN products b ON a.product_code = b.product_code
					INNER JOIN customers c ON a.customer_code = c.customer_code
					WHERE a.customer_code='".$customerCode."' AND a.product_code='". $productCode."'
					AND Date(a.create_date) >= '".$dateFrom."' and date(a.create_date) <= '".$dateTo."'
					GROUP BY c.customer_code, c.customer_name,b.product_code, b.product_name";
					
				}
				else 
				{

					$sql="SELECT c.customer_code, 
	    	 			  c.customer_name, b.product_code, b.product_name,
	    	 			  Sum(a.total_print) as Total FROM barcode a
					INNER JOIN products b ON a.product_code = b.product_code
					INNER JOIN customers c ON a.customer_code = c.customer_code
					WHERE a.customer_code='".$customerCode."' AND a.product_code='". $productCode."' 
					GROUP BY c.customer_code, c.customer_name ,b.product_code, b.product_name";
					
				}
			}
			else 
			{

					$sql="SELECT c.customer_code, 
	    	 			  c.customer_name ,b.product_code, b.product_name,
	    	 			  Sum(a.total_print) as Total FROM barcode a
					INNER JOIN products b ON a.product_code = b.product_code
					INNER JOIN customers c ON a.customer_code = c.customer_code
					WHERE a.customer_code='".$customerCode."'
					GROUP BY c.customer_code, c.customer_name ,b.product_code, b.product_name";
					
			}
    	}
    	else if(!empty($this->input->post('productCode'))){
			$parts = explode('-', $this->input->post('productCode'));
			$productCode = $parts[0];
			if(!empty($this->input->post('dateFrom')) && !empty($this->input->post('dateTo'))){
				$dateFrom = $this->cDate($this->input->post('dateFrom'));
				$dateTo = $this->cDate($this->input->post('dateTo'));
				$sql="SELECT c.customer_code, 
	    	 			  c.customer_name,b.product_code, b.product_name, 
	    	 			  Sum(a.total_print) as Total FROM barcode a
					INNER JOIN products b ON a.product_code = b.product_code
					INNER JOIN customers c ON a.customer_code = c.customer_code
					WHERE a.customer_code='".$customerCode."' AND a.product_code='". $productCode."'
					AND Date(a.create_date) >= '".$dateFrom."' and date(a.create_date) <= '".$dateTo."'
					GROUP BY c.customer_code, c.customer_name, b.product_code, b.product_name ";
			}else{
				$sql="SELECT c.customer_code, 
	    	 			  c.customer_name, b.product_code, b.product_name,
	    	 			  Sum(a.total_print) as Total FROM barcode a
					INNER JOIN products b ON a.product_code = b.product_code
					INNER JOIN customers c ON a.customer_code = c.customer_code
					WHERE a.product_code='". $productCode."' 
					GROUP BY c.customer_code, c.customer_name, b.product_code, b.product_name";
			}
		}else if(!empty($this->input->post('dateFrom')) && !empty($this->input->post('dateTo'))){
			$parts = explode('-', $this->input->post('customerCode'));
			$customerCode = $parts[0];
			$parts = explode('-', $this->input->post('productCode'));
			$productCode = $parts[0];
			$dateFrom = $this->cDate($this->input->post('dateFrom'));
			$dateTo = $this->cDate($this->input->post('dateTo'));

			$sql="SELECT c.customer_code, 
	 			  c.customer_name, b.product_code, b.product_name ,
	 			  Sum(a.total_print) as Total FROM barcode a
			INNER JOIN products b ON a.product_code = b.product_code
			INNER JOIN customers c ON a.customer_code = c.customer_code
			WHERE  Date(a.create_date) >= '".$dateFrom."' and date(a.create_date) <= '".$dateTo."'
			GROUP BY c.customer_code, c.customer_name ,b.product_code, b.product_name";
			
		}

    	$query = $this->db->query($sql);
    	 // return $query->result();  
    	if(!empty($query))
        {
            return $query->result();  
        }
        else
        {
            return null;
        }
              
    }
}