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

class Hauling_model extends Base_Model
{

	function __construct()
	{

		parent::__construct();
		$this->set_table('hauling');
		$this->set_pk('vehicle_id','id_rec');
		$this->set_log(false);
	}

	function count()
	{
		$data = array();
		$this->db->join('vendor vnd','tbl.vendor_id=vnd.vendor_id','left');
        $this->db->join('trans_status sts','tbl.hauling_status=sts.trans_status','left');
        $this->db->join('vehicle vhc','tbl.vehicle_id=vhc.vehicle_id','left');

		$this->db->where($this->where);
		$this->db->or_like($this->like);

		$query = $this->db->get($this->schema.'.'.$this->table.' tbl');
	
		return $query->num_rows();
	}

	function get_list()
	{
		$this->db->select('tbl.*');
		$this->db->select('vnd.vendor_name');
		$this->db->select('sts.status');
		$this->db->select('vhc.vendor_id as vehicle_vendorid');
		$this->db->select('vhc.tare_weight as vehicle_tare_weight');
		$this->db->join('vendor vnd','tbl.vendor_id=vnd.vendor_id','left');
		$this->db->join('trans_status sts','tbl.hauling_status=sts.trans_status','left');
		$this->db->join('vehicle vhc','tbl.vehicle_id=vhc.vehicle_id','left');
		
		if($this->where)
		$this->db->where($this->where);
		if($this->like)
		$this->db->or_like($this->like);
		
		foreach ($this->order_by as $key => $value)
		{
			$this->db->order_by($key, $value);
		}

		if (!$this->limit AND !$this->offset)
			$query = $this->db->get($this->schema.'.'.$this->table.' tbl');
		else
			$query = $this->db->get($this->schema.'.'.$this->table.' tbl',$this->limit,$this->offset);
		
        if($query->num_rows()>0)
		{
			return $query;
        
		}else
		{
			$query->free_result();
            return $query;
        }
	}


	function gen_transaction_number($prefix='')
	{
		$transnumber_length	= 16;
		
		$this->db->select('RIGHT(tbl.hauling_number,7) hauling_number',false);
		$this->db->order_by('tbl.hauling_number','DESC');
		$this->db->group_by('tbl.hauling_number');
		
		$prefix_length		= strlen($prefix);
		$autonumber_length	= $transnumber_length - $prefix_length;
		
		$this->db->where("LEFT(tbl.hauling_number,9) = '$prefix' AND CHAR_LENGTH(tbl.hauling_number) = $transnumber_length");
		
		$query = $this->db->get($this->schema.'.'.$this->table.' tbl','1','0');
		// echo $this->db->last_query();
		// exit;
		
		$no_last =  $query->row_array();
		$no_next = 1;
		if ($no_last)
		{
			$no_next = $no_last['hauling_number']+1;
		}

		$no = $prefix.sprintf('%0'.$autonumber_length.'s', $no_next);
	
		$query->free_result();
		
		return $no;
	} 

	public function gen_recnum($prefix)
	{
		$this->db->select('IFNULL( MAX( id_rec ), 0 ) + 1 last_recnum',false);
		$this->db->where(array('vehicle_id'=>$prefix));
		$query = $this->db->get($this->schema.'.'.$this->table.' tbl');
		$query_row = $query->row_array();
		return $query_row['last_recnum'];
	}

	function get_list_summary()
	{
		$this->db->select('tbl.vendor_mis');
		$this->db->select('tbl.ongkos');
		$this->db->select('sum(tbl.gross_weight) as gross_weight',false);
		$this->db->select('sum(tbl.tare_weight) as tare_weight',false);
		$this->db->select('sum(tbl.net_weight) as net_weight',false);
		$this->db->group_by(array('tbl.vendor_mis','tbl.ongkos'));
		
		if($this->where)
		$this->db->where($this->where);
		if($this->like)
		$this->db->or_like($this->like);
		
		foreach ($this->order_by as $key => $value)
		{
			$this->db->order_by($key, $value);
		}

		if (!$this->limit AND !$this->offset)
			$query = $this->db->get($this->schema.'.'.$this->table.' tbl');
		else
			$query = $this->db->get($this->schema.'.'.$this->table.' tbl',$this->limit,$this->offset);
		
        if($query->num_rows()>0)
		{
			return $query;
        
		}else
		{
			$query->free_result();
            return $query;
        }
	}


	function update_header($transnum,$date1,$date2,$vendor)
	{
		$qry = '
		UPDATE hauling
		SET hauling_recapnum = '.quotedStr($transnum).'
		    , hauling_status = 3 
		WHERE
		 hauling_status = 2
		 AND hauling_date BETWEEN '.quotedStr($date1).' 
		 AND '.quotedStr($date2).'
		 AND vendor_mis ='.quotedStr($vendor) ;
		$query = $this->db->query($qry);
	}

	public function get_startdate($transnum)
	{
		$this->db->select(' MIN(hauling_out_date) start_date',false);
		$this->db->where(array('hauling_recapnum'=>$transnum));
		$query = $this->db->get($this->schema.'.'.$this->table.' tbl');
		$query_row = $query->row_array();
		return $query_row['start_date'];
	}

	public function get_enddate($transnum)
	{
		$this->db->select(' MAX(hauling_date) end_date',false);
		$this->db->where(array('hauling_recapnum'=>$transnum));
		$query = $this->db->get($this->schema.'.'.$this->table.' tbl');
		$query_row = $query->row_array();
		return $query_row['end_date'];
	}


	function getTotalHauling()
	{
		$this->db->select('ifnull(sum(net_weight),0) as total',false);
		// $this->db->join('vendor vnd','tbl.vendor_mis=vnd.vendor_mis','left');

		if($this->where)
		$this->db->where($this->where);
		if($this->like)
		$this->db->or_like($this->like);

        $query = $this->db->get($this->schema.'.'.$this->table.' tbl');
		$query_sum = $query->row_array();
        return  $query_sum['total'];
	}

	function get_list_bymonth()
	{
		$this->db->select('MONTH(tbl.hauling_date) as ob_month');
		$this->db->select('MONTHNAME(tbl.hauling_date) as ob_month_name');
		$this->db->select('ifnull(sum(net_weight),0) as total',false);
		
		if($this->where)
		$this->db->where($this->where);
		if($this->like)
		$this->db->or_like($this->like);

		if($this->group_by)
		{
			foreach ($this->group_by as $value) {
				$this->db->group_by($value);
			}
		}
		
		foreach ($this->order_by as $key => $value)
		{
			$this->db->order_by($key, $value);
		}

		if (!$this->limit AND !$this->offset)
			$query = $this->db->get($this->schema.'.'.$this->table.' tbl');
		else
			$query = $this->db->get($this->schema.'.'.$this->table.' tbl',$this->limit,$this->offset);
		
        if($query->num_rows()>0)
		{
			return $query;
        
		}else
		{
			$query->free_result();
            return $query;
        }
	}

	function get_list_byday()
	{
		$this->db->select('DAY(tbl.hauling_date) as ob_day');
		$this->db->select('ifnull(sum(net_weight),0) as total',false);
		
		if($this->where)
		$this->db->where($this->where);
		if($this->like)
		$this->db->or_like($this->like);

		if($this->group_by)
		{
			foreach ($this->group_by as $value) {
				$this->db->group_by($value);
			}
		}
		
		foreach ($this->order_by as $key => $value)
		{
			$this->db->order_by($key, $value);
		}

		if (!$this->limit AND !$this->offset)
			$query = $this->db->get($this->schema.'.'.$this->table.' tbl');
		else
			$query = $this->db->get($this->schema.'.'.$this->table.' tbl',$this->limit,$this->offset);
		
        if($query->num_rows()>0)
		{
			return $query;
        
		}else
		{
			$query->free_result();
            return $query;
        }
	}

	function get_list_byhour()
	{
		$this->db->select('HOUR(tbl.hauling_time) as ob_hour');
		$this->db->select('ifnull(sum(net_weight),0) as total',false);
		
		if($this->where)
		$this->db->where($this->where);
		if($this->like)
		$this->db->or_like($this->like);

		if($this->group_by)
		{
			foreach ($this->group_by as $value) {
				$this->db->group_by($value);
			}
		}
		
		foreach ($this->order_by as $key => $value)
		{
			$this->db->order_by($key, $value);
		}

		if (!$this->limit AND !$this->offset)
			$query = $this->db->get($this->schema.'.'.$this->table.' tbl');
		else
			$query = $this->db->get($this->schema.'.'.$this->table.' tbl',$this->limit,$this->offset);
		
        if($query->num_rows()>0)
		{
			return $query;
        
		}else
		{
			$query->free_result();
            return $query;
        }
	}


	function getStockRoomCustomAll($date1, $date2){
		$qry = 
<<<EOT
		SELECT t1.trans_date, 
		ROUND((t1.previous + t1.saldo_awal ),2) saldo_awal,  
		ROUND(t1.total_in,2) total_in,
		ROUND((t1.previous + t1.available),2) available,
		ROUND(t1.total_out,2) total_out,
		ROUND(((t1.previous + t1.available) - t1.total_out),2) end_qty,
		ROUND(IFNULL(t1.total_out / (t1.previous + t1.available),0) * 100 ,2) turn_over
		FROM 
		(select
			e.trans_date,
			@prev as previous,
			@prev := @prev + e.end_qty as current,
			e.saldo_awal,
			e.total_in,
			e.available,
			e.total_out,
			e.end_qty
		from
			(
				select @prev := 0
			) as init,

		(SELECT mov.trans_date, sum(mov.saldo_awal) saldo_awal, sum(mov.total_in) total_in, (sum(mov.saldo_awal) + sum(mov.total_in)) available, sum(mov.total_out) total_out , 
				((sum(mov.saldo_awal) + sum(mov.total_in)) - sum(mov.total_out)) end_qty
		FROM(
		-- saldo awal
			SELECT
				STR_TO_DATE('$date1','%Y-%m-%d') as trans_date, IFNULL(SUM(sa.saldo_awal),0) saldo_awal, 0 as total_in, 0 as total_out
			FROM(
				SELECT mov.trans_date, sum(mov.total_in) total_in, sum(mov.total_out) total_out,  (sum(mov.total_in) - sum(mov.total_out)) as saldo_awal
				FROM(
				SELECT coal_get_date as trans_date, IFNULL(sum(net_weight),0) total_in, 0 as total_out 
				FROM coal_getting_inbound
				GROUP BY coal_get_date
				UNION ALL
				SELECT hauling_date as trans_date, 0 as total_in, IFNULL(sum(net_weight),0) total_out 
				FROM hauling
				GROUP BY hauling_date) mov
				GROUP BY mov.trans_date) sa
			WHERE sa.trans_date < '$date1'
		-- end saldo awal
			UNION ALL
			SELECT 
				coal_get_date as trans_date, 0 as saldo_awal, IFNULL(sum(net_weight),0) total_in, 0 as total_out 
			FROM coal_getting_inbound
			WHERE coal_get_date BETWEEN '$date1' AND '$date2'
			GROUP BY coal_get_date
			UNION ALL
			SELECT 
				hauling_date as trans_date, 0 as saldo_awal, 0 as total_in, IFNULL(sum(net_weight),0) total_out 
			FROM hauling
			WHERE hauling_date BETWEEN '$date1' AND '$date2'
			GROUP BY hauling_date) mov
			GROUP BY mov.trans_date
			ORDER BY 1) 	 
		as e
		order by e.trans_date ) t1  
EOT;
			
		$query = $this->db->query($qry);
		return $query;
	}

	function getStockRoomLastMonthAll(){
		$qry = 
<<<EOT
		SELECT t1.trans_date, 
		ROUND((t1.previous + t1.saldo_awal ),2) saldo_awal,  
		ROUND(t1.total_in,2) total_in,
		ROUND((t1.previous + t1.available),2) available,
		ROUND(t1.total_out,2) total_out,
		ROUND(((t1.previous + t1.available) - t1.total_out),2) end_qty,
		ROUND(IFNULL(t1.total_out / (t1.previous + t1.available),0) * 100 ,2) turn_over
		FROM 
		(select
			e.trans_date,
			@prev as previous,
			@prev := @prev + e.end_qty as current,
			e.saldo_awal,
			e.total_in,
			e.available,
			e.total_out,
			e.end_qty
		from
			(
				select @prev := 0
			) as init,

		(SELECT mov.trans_date, sum(mov.saldo_awal) saldo_awal, sum(mov.total_in) total_in, (sum(mov.saldo_awal) + sum(mov.total_in)) available, sum(mov.total_out) total_out , 
				((sum(mov.saldo_awal) + sum(mov.total_in)) - sum(mov.total_out)) end_qty
		FROM(
		-- saldo awal
			SELECT
				DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') as trans_date, IFNULL(SUM(sa.saldo_awal),0) saldo_awal, 0 as total_in, 0 as total_out
			FROM(
				SELECT mov.trans_date, sum(mov.total_in) total_in, sum(mov.total_out) total_out,  (sum(mov.total_in) - sum(mov.total_out)) as saldo_awal
				FROM(
				SELECT coal_get_date as trans_date, IFNULL(sum(net_weight),0) total_in, 0 as total_out 
				FROM coal_getting_inbound
				GROUP BY coal_get_date
				UNION ALL
				SELECT hauling_date as trans_date, 0 as total_in, IFNULL(sum(net_weight),0) total_out 
				FROM hauling
				GROUP BY hauling_date) mov
				GROUP BY mov.trans_date) sa
			WHERE sa.trans_date < DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00')
		-- end saldo awal
			UNION ALL
			SELECT 
				coal_get_date as trans_date, 0 as saldo_awal, IFNULL(sum(net_weight),0) total_in, 0 as total_out 
			FROM coal_getting_inbound
			WHERE coal_get_date BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
			GROUP BY coal_get_date
			UNION ALL
			SELECT 
				hauling_date as trans_date, 0 as saldo_awal, 0 as total_in, IFNULL(sum(net_weight),0) total_out 
			FROM hauling
			WHERE hauling_date BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
			GROUP BY hauling_date) mov
			GROUP BY mov.trans_date
			ORDER BY 1) 	 
		as e
		order by e.trans_date ) t1  
EOT;
			
		$query = $this->db->query($qry);
		return $query;
	}


	function getStockRoomYeartoDateAll($year,$date1,$date2){
		$qry = 
<<<EOT
		SELECT t1.trans_date, 
		ROUND((t1.previous + t1.saldo_awal ),2) saldo_awal,  
		ROUND(t1.total_in,2) total_in,
		ROUND((t1.previous + t1.available),2) available,
		ROUND(t1.total_out,2) total_out,
		ROUND(((t1.previous + t1.available) - t1.total_out),2) end_qty,
		ROUND(IFNULL(t1.total_out / (t1.previous + t1.available),0) * 100 ,2) turn_over
		FROM 
		(select
			e.trans_date,
			@prev as previous,
			@prev := @prev + e.end_qty as current,
			e.saldo_awal,
			e.total_in,
			e.available,
			e.total_out,
			e.end_qty
		from
			(
				select @prev := 0
			) as init,
			
		(SELECT mov.month_date, mov.trans_date, sum(mov.saldo_awal) saldo_awal, sum(mov.total_in) total_in, (sum(mov.saldo_awal) + sum(mov.total_in)) available, sum(mov.total_out) total_out , 
				((sum(mov.saldo_awal) + sum(mov.total_in)) - sum(mov.total_out)) end_qty
		FROM(
		-- saldo awal
			SELECT
				MONTH(STR_TO_DATE('$date1','%Y-%m-%d')) as month_date, MONTHNAME(STR_TO_DATE('$date1','%Y-%m-%d')) as trans_date, IFNULL(SUM(sa.saldo_awal),0) saldo_awal, 0 as total_in, 0 as total_out
			FROM(
				SELECT mov.year_date, mov.month_date, mov.trans_date, sum(mov.total_in) total_in, sum(mov.total_out) total_out,  (sum(mov.total_in) - sum(mov.total_out)) as saldo_awal
				FROM(
				SELECT YEAR(coal_get_date) as year_date, MONTH(coal_get_date) as month_date, MONTHNAME(coal_get_date) as trans_date, IFNULL(sum(net_weight),0) total_in, 0 as total_out 
				FROM coal_getting_inbound
				GROUP BY YEAR(coal_get_date), MONTH(coal_get_date), MONTHNAME(coal_get_date)
				UNION ALL
				SELECT YEAR(hauling_date) as year_date, MONTH(hauling_date) as month_date, MONTHNAME(hauling_date) as trans_date, 0 as total_in, IFNULL(sum(net_weight),0) total_out 
				FROM hauling
				GROUP BY YEAR(hauling_date), MONTH(hauling_date), MONTHNAME(hauling_date)) mov
				GROUP BY mov.year_date, mov.month_date, mov.trans_date) sa
			WHERE sa.year_date < $year 
		-- end saldo awal
			UNION ALL
			SELECT 
				MONTH(coal_get_date) as month_date, MONTHNAME(coal_get_date) as trans_date, 0 as saldo_awal, IFNULL(sum(net_weight),0) total_in, 0 as total_out 
			FROM coal_getting_inbound
			WHERE coal_get_date BETWEEN '$date1' AND '$date2'
			GROUP BY MONTH(coal_get_date), MONTHNAME(coal_get_date)
			UNION ALL
			SELECT 
				MONTH(hauling_date) as month_date, MONTHNAME(hauling_date) as trans_date, 0 as saldo_awal, 0 as total_in, IFNULL(sum(net_weight),0) total_out 
			FROM hauling
			WHERE hauling_date BETWEEN '$date1' AND '$date2'
			GROUP BY MONTH(hauling_date), MONTHNAME(hauling_date)) mov
			GROUP BY mov.month_date ,mov.trans_date
			ORDER BY 1) 	 
		as e
		order by e.month_date ) t1  
EOT;
			
		$query = $this->db->query($qry);
		return $query;
	}

	function getStockRoomCustombySR($date1, $date2, $stockroom){
		$qry = 
<<<EOT
		SELECT t1.trans_date, 
		ROUND((t1.previous + t1.saldo_awal ),2) saldo_awal,  
		ROUND(t1.total_in,2) total_in,
		ROUND((t1.previous + t1.available),2) available,
		ROUND(t1.total_out,2) total_out,
		ROUND(((t1.previous + t1.available) - t1.total_out),2) end_qty,
		ROUND(IFNULL(t1.total_out / (t1.previous + t1.available),0) * 100 ,2) turn_over,
		t1.stockroom_id
		FROM 
		(select
			e.trans_date,
			@prev as previous,
			@prev := @prev + e.end_qty as current,
			e.saldo_awal,
			e.total_in,
			e.available,
			e.total_out,
			e.end_qty,
			e.stockroom_id
		from
			(
				select @prev := 0
			) as init,
			
		(SELECT mov.trans_date, sum(mov.saldo_awal) saldo_awal, sum(mov.total_in) total_in, (sum(mov.saldo_awal) + sum(mov.total_in)) available, sum(mov.total_out) total_out , 
				((sum(mov.saldo_awal) + sum(mov.total_in)) - sum(mov.total_out)) end_qty, mov.stockroom_id
		FROM(
		-- saldo awal
			SELECT
				STR_TO_DATE('$date1','%Y-%m-%d') as trans_date, IFNULL(SUM(sa.saldo_awal),0) saldo_awal, 0 as total_in, 0 as total_out, sa.stockroom_id
			FROM(
				SELECT mov.trans_date, sum(mov.total_in) total_in, sum(mov.total_out) total_out,  (sum(mov.total_in) - sum(mov.total_out)) as saldo_awal, mov.stockroom_id
				FROM(
				SELECT coal_get_date as trans_date, IFNULL(sum(net_weight),0) total_in, 0 as total_out, stockroom_id 
				FROM coal_getting_inbound
				WHERE stockroom_id = '$stockroom'
				GROUP BY coal_get_date, stockroom_id
				UNION ALL
				SELECT h1.hauling_date as trans_date, 0 as total_in, IFNULL(sum(h1.net_weight),0) total_out, s1.stockroom_id 
				FROM hauling h1
				LEFT JOIN stockroom s1 on s1.origin_type = h1.origin_type
				WHERE s1.stockroom_id = '$stockroom'
				GROUP BY h1.hauling_date, s1.stockroom_id) mov
				GROUP BY mov.trans_date, mov.stockroom_id) sa
			WHERE sa.trans_date < '$date1'
		-- end saldo awal
			UNION ALL
			SELECT 
				coal_get_date as trans_date, 0 as saldo_awal, IFNULL(sum(net_weight),0) total_in, 0 as total_out, stockroom_id 
			FROM coal_getting_inbound
			WHERE coal_get_date BETWEEN '$date1' AND '$date2'
				AND stockroom_id = '$stockroom'
			GROUP BY coal_get_date, stockroom_id
			UNION ALL
			SELECT 
				h1.hauling_date as trans_date, 0 as saldo_awal, 0 as total_in, IFNULL(sum(h1.net_weight),0) total_out, s1.stockroom_id 
			FROM hauling h1
			LEFT JOIN stockroom s1 on s1.origin_type = h1.origin_type
			WHERE h1.hauling_date BETWEEN '$date1' AND '$date2'
				AND s1.stockroom_id = '$stockroom'
			GROUP BY h1.hauling_date, s1.stockroom_id) mov
			GROUP BY mov.trans_date, mov.stockroom_id
			ORDER BY 1) 	 
		as e
		order by e.trans_date ) t1  
EOT;
			
		$query = $this->db->query($qry);
		return $query;
	}

	function getStockRoomLastMonthbySR($stockroom){
		$qry = 
<<<EOT
		SELECT t1.trans_date, 
		ROUND((t1.previous + t1.saldo_awal ),2) saldo_awal,  
		ROUND(t1.total_in,2) total_in,
		ROUND((t1.previous + t1.available),2) available,
		ROUND(t1.total_out,2) total_out,
		ROUND(((t1.previous + t1.available) - t1.total_out),2) end_qty,
		ROUND(IFNULL(t1.total_out / (t1.previous + t1.available),0) * 100 ,2) turn_over,
		t1.stockroom_id
		FROM 
		(select
			e.trans_date,
			@prev as previous,
			@prev := @prev + e.end_qty as current,
			e.saldo_awal,
			e.total_in,
			e.available,
			e.total_out,
			e.end_qty,
			e.stockroom_id
		from
			(
				select @prev := 0
			) as init,

		(SELECT mov.trans_date, sum(mov.saldo_awal) saldo_awal, sum(mov.total_in) total_in, (sum(mov.saldo_awal) + sum(mov.total_in)) available, sum(mov.total_out) total_out , 
				((sum(mov.saldo_awal) + sum(mov.total_in)) - sum(mov.total_out)) end_qty, mov.stockroom_id
		FROM(
		-- saldo awal
			SELECT
				DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') as trans_date, IFNULL(SUM(sa.saldo_awal),0) saldo_awal, 0 as total_in, 0 as total_out, sa.stockroom_id
			FROM(
				SELECT mov.trans_date, sum(mov.total_in) total_in, sum(mov.total_out) total_out,  (sum(mov.total_in) - sum(mov.total_out)) as saldo_awal, mov.stockroom_id
				FROM(
				SELECT coal_get_date as trans_date, IFNULL(sum(net_weight),0) total_in, 0 as total_out, stockroom_id 
				FROM coal_getting_inbound
				WHERE stockroom_id = '$stockroom'
				GROUP BY coal_get_date, stockroom_id
				UNION ALL
				SELECT h1.hauling_date as trans_date, 0 as total_in, IFNULL(sum(h1.net_weight),0) total_out, s1.stockroom_id  
				FROM hauling h1
				LEFT JOIN stockroom s1 on s1.origin_type = h1.origin_type
				WHERE s1.stockroom_id = '$stockroom'
				GROUP BY h1.hauling_date, s1.stockroom_id) mov
				GROUP BY mov.trans_date,mov.stockroom_id) sa
			WHERE sa.trans_date < DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00')
		-- end saldo awal
			UNION ALL
			SELECT 
				coal_get_date as trans_date, 0 as saldo_awal, IFNULL(sum(net_weight),0) total_in, 0 as total_out, stockroom_id 
			FROM coal_getting_inbound
			WHERE coal_get_date BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
				AND stockroom_id = '$stockroom'
			GROUP BY coal_get_date, stockroom_id
			UNION ALL
			SELECT 
				h1.hauling_date as trans_date, 0 as saldo_awal, 0 as total_in, IFNULL(sum(h1.net_weight),0) total_out, s1.stockroom_id  
			FROM hauling h1
			LEFT JOIN stockroom s1 on s1.origin_type = h1.origin_type
			WHERE h1.hauling_date BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
				AND s1.stockroom_id = '$stockroom'
			GROUP BY h1.hauling_date, s1.stockroom_id) mov
			GROUP BY mov.trans_date, mov.stockroom_id
			ORDER BY 1) 	 
		as e
		order by e.trans_date ) t1  
EOT;
			
		$query = $this->db->query($qry);
		return $query;
	}


	function getStockRoomYeartoDatebySR($year,$date1,$date2,$stockroom){
		$qry = 
<<<EOT
		SELECT t1.trans_date, 
		ROUND((t1.previous + t1.saldo_awal ),2) saldo_awal,  
		ROUND(t1.total_in,2) total_in,
		ROUND((t1.previous + t1.available),2) available,
		ROUND(t1.total_out,2) total_out,
		ROUND(((t1.previous + t1.available) - t1.total_out),2) end_qty,
		ROUND(IFNULL(t1.total_out / (t1.previous + t1.available),0) * 100 ,2) turn_over,
		t1.stockroom_id
		FROM 
		(select
			e.trans_date,
			@prev as previous,
			@prev := @prev + e.end_qty as current,
			e.saldo_awal,
			e.total_in,
			e.available,
			e.total_out,
			e.end_qty,
			e.stockroom_id
		from
			(
				select @prev := 0
			) as init,
			
		(SELECT mov.month_date, mov.trans_date, sum(mov.saldo_awal) saldo_awal, sum(mov.total_in) total_in, (sum(mov.saldo_awal) + sum(mov.total_in)) available, sum(mov.total_out) total_out , 
				((sum(mov.saldo_awal) + sum(mov.total_in)) - sum(mov.total_out)) end_qty, mov.stockroom_id
		FROM(
		-- saldo awal
			SELECT
				MONTH(STR_TO_DATE('$date1','%Y-%m-%d')) as month_date, MONTHNAME(STR_TO_DATE('$date1','%Y-%m-%d')) as trans_date, IFNULL(SUM(sa.saldo_awal),0) saldo_awal, 0 as total_in, 0 as total_out, sa.stockroom_id
			FROM(
				SELECT mov.year_date, mov.month_date, mov.trans_date, sum(mov.total_in) total_in, sum(mov.total_out) total_out,  (sum(mov.total_in) - sum(mov.total_out)) as saldo_awal, mov.stockroom_id
				FROM(
				SELECT YEAR(coal_get_date) as year_date, MONTH(coal_get_date) as month_date, MONTHNAME(coal_get_date) as trans_date, IFNULL(sum(net_weight),0) total_in, 0 as total_out, stockroom_id 
				FROM coal_getting_inbound
				WHERE stockroom_id = '$stockroom'
				GROUP BY YEAR(coal_get_date), MONTH(coal_get_date), MONTHNAME(coal_get_date), stockroom_id
				UNION ALL
				SELECT YEAR(h1.hauling_date) as year_date, MONTH(h1.hauling_date) as month_date, MONTHNAME(h1.hauling_date) as trans_date, 0 as total_in, IFNULL(sum(h1.net_weight),0) total_out, s1.stockroom_id  
				FROM hauling h1
				LEFT JOIN stockroom s1 on s1.origin_type = h1.origin_type
				WHERE s1.stockroom_id = '$stockroom'
				GROUP BY YEAR(h1.hauling_date), MONTH(h1.hauling_date), MONTHNAME(h1.hauling_date), s1.stockroom_id ) mov
				GROUP BY mov.year_date, mov.month_date, mov.trans_date, mov.stockroom_id) sa
			WHERE sa.year_date < $year 
		-- end saldo awal
			UNION ALL
			SELECT 
				MONTH(coal_get_date) as month_date, MONTHNAME(coal_get_date) as trans_date, 0 as saldo_awal, IFNULL(sum(net_weight),0) total_in, 0 as total_out, stockroom_id 
			FROM coal_getting_inbound
			WHERE coal_get_date BETWEEN '$date1' AND '$date2'
				AND stockroom_id = '$stockroom'
			GROUP BY MONTH(coal_get_date), MONTHNAME(coal_get_date), stockroom_id
			UNION ALL
			SELECT 
				MONTH(h1.hauling_date) as month_date, MONTHNAME(h1.hauling_date) as trans_date, 0 as saldo_awal, 0 as total_in, IFNULL(sum(h1.net_weight),0) total_out, s1.stockroom_id 
			FROM hauling h1
			LEFT JOIN stockroom s1 on s1.origin_type = h1.origin_type
			WHERE h1.hauling_date BETWEEN '$date1' AND '$date2'
				AND s1.stockroom_id = '$stockroom'
			GROUP BY MONTH(h1.hauling_date), MONTHNAME(h1.hauling_date), s1.stockroom_id) mov
			GROUP BY mov.month_date ,mov.trans_date, mov.stockroom_id
			ORDER BY 1) 	 
		as e
		order by e.month_date ) t1  
EOT;
			
		$query = $this->db->query($qry);
		return $query;
	}

}
