<?php
class MDL_reports extends CI_Model
{
	function __construct()
    {
        parent::__construct();
		
	}
	
	function datareportpemasukanbarangperdokumen_All($limit,$start,$sidx,$sord, $where, $page){
		$query = $this->db->query("
			;with ctebc_indok_lap ( id_data_in_hdr, doc_in_no, doc_in_date, BC23No, BC23Tg, PasokNama, part_name_customs, sumqty, sumharga, unit_in, doc_in_from_name, kodebrg 
			) as (
			SELECT  dbo.tbl_data_in_hdr.id_data_in_hdr, dbo.tbl_data_in_hdr.doc_in_no, dbo.tbl_data_in_hdr.doc_in_date, dbo.tbl_bc_in_hdr.BC23No, dbo.tbl_bc_in_hdr.BC23Tg as BC23Tg, dbo.tbl_bc_in_hdr.PasokNama, dbo.tbl_data_in_dtl.part_name_customs, SUM(dbo.tbl_data_in_dtl.qty_in) AS sumqty, (case when dbo.tbl_data_in_dtl.type_brg=5 then sum(dbo.tbl_data_in_dtl.harga) else 
			 
			 case when (SUM(dbo.tbl_data_in_dtl.harga * dbo.tbl_data_in_dtl.qty_in)/1000) = 0 then
			 sum(dbo.tbl_data_in_dtl.qty_in) else (SUM(dbo.tbl_data_in_dtl.harga * dbo.tbl_data_in_dtl.qty_in)/1000) 
			 end
			 
			 end ) AS sumharga, dbo.tbl_data_in_dtl.unit_in, dbo.enum_data_in_from.doc_in_from_name, (case when dbo.tbl_data_in_dtl.type_brg=5 then (select top 1 a.part_no from tbl_data_in_dtl a where a.id_data_in_hdr=dbo.tbl_data_in_hdr.id_data_in_hdr and a.part_name_customs = dbo.tbl_data_in_dtl.part_name_customs ) else (select top 1 kode_barang from tbl_kode_barang where nama_barang = dbo.tbl_data_in_dtl.part_name_customs) end) as kodebrg
			FROM dbo.tbl_data_in_hdr
			INNER JOIN dbo.tbl_data_in_dtl ON dbo.tbl_data_in_hdr.id_data_in_hdr = dbo.tbl_data_in_dtl.id_data_in_hdr
			INNER JOIN dbo.tbl_bc_in_hdr ON dbo.tbl_data_in_hdr.id_terkait = dbo.tbl_bc_in_hdr.id_bc_in_hdr
			INNER JOIN dbo.enum_data_in_from ON dbo.tbl_data_in_hdr.doc_in_from_id = dbo.enum_data_in_from.doc_in_from_id
			 where  1 = 1  and (dbo.tbl_bc_in_hdr.[BC23No] <> '' or dbo.tbl_bc_in_hdr.[BC23No] is not null)   and  tbl_data_in_hdr.doc_in_from_id IN (2,3,4,5) 
			GROUP BY dbo.tbl_data_in_hdr.id_data_in_hdr, dbo.tbl_data_in_hdr.doc_in_no, dbo.tbl_data_in_hdr.doc_in_date, dbo.tbl_bc_in_hdr.BC23No, dbo.tbl_bc_in_hdr.BC23Tg, dbo.tbl_bc_in_hdr.PasokNama, dbo.tbl_data_in_dtl.part_name_customs, dbo.tbl_data_in_dtl.unit_in, dbo.enum_data_in_from.doc_in_from_name, dbo.tbl_data_in_dtl.type_brg
			)
			select * from (
						
					select ROW_NUMBER() OVER (ORDER BY doc_in_date) as row, id_data_in_hdr, doc_in_no, convert(varchar, doc_in_date, 103) as doc_in_date, BC23No, convert(varchar, BC23Tg,103) as BC23Tg ,PasokNama, part_name_customs, sumqty, sumharga, unit_in, doc_in_from_name, kodebrg  from ctebc_indok_lap
				where $where	
			) as x
				where x.row between  ($page - 1) * $limit + 1 AND $page * $limit
				order by $sidx $sord 
		");
		
								
        $result = $query->result_array();
		//die($this->db->last_query());
		//var_dump($result);
		//die();
		return $result;
	
	}
	
	function datareportpemasukanbarangperdokumen_CountAll($where, $where1){
	
		$this->db->select("
			row_number() over(order by (select 0))
		");
		$this->db->from("dbo.tbl_data_in_hdr");
		$this->db->join("dbo.tbl_data_in_dtl" , "dbo.tbl_data_in_hdr.id_data_in_hdr = dbo.tbl_data_in_dtl.id_data_in_hdr", "INNER");
		$this->db->join("dbo.tbl_bc_in_hdr" , "dbo.tbl_data_in_hdr.id_terkait = dbo.tbl_bc_in_hdr.id_bc_in_hdr", "INNER");
		$this->db->join("dbo.enum_data_in_from" , "dbo.tbl_data_in_hdr.doc_in_from_id = dbo.enum_data_in_from.doc_in_from_id", "INNER");
		$this->db->group_by(array("dbo.tbl_data_in_hdr.id_data_in_hdr", 
								"dbo.tbl_data_in_hdr.doc_in_no",
								"dbo.tbl_data_in_hdr.doc_in_date",
								"dbo.tbl_bc_in_hdr.BC23No",
								"dbo.tbl_bc_in_hdr.BC23Tg",
								"dbo.tbl_bc_in_hdr.PasokNama",
								"dbo.tbl_data_in_dtl.part_name_customs",
								"dbo.tbl_data_in_dtl.unit_in",
								"dbo.enum_data_in_from.doc_in_from_name")); 
		$this->db->where($where1);
		//$this->db->group_by(array("a.inv_hdr_id", "b.part_name_customs","a.r7")); 
        $query = $this->db->get();
		$rowcount = $query->num_rows();
		return $rowcount;
	
	
	}
	
	function printdatapemasukanbarangperdokumen_All($sidx,$sord, $where){
		$this->db->select("
				  row_number() over(order by (select 0)) as nomorurut,	
				  dbo.tbl_data_in_hdr.id_data_in_hdr,
				  dbo.tbl_data_in_hdr.doc_in_no,
				  convert(varchar, dbo.tbl_data_in_hdr.doc_in_date,103) as doc_in_date, 
				  dbo.tbl_bc_in_hdr.BC23No,
				  convert(varchar, dbo.tbl_bc_in_hdr.BC23Tg,103) as BC23Tg,
				  dbo.tbl_bc_in_hdr.PasokNama,
				  dbo.tbl_data_in_dtl.part_name_customs,
				  SUM(dbo.tbl_data_in_dtl.qty_in) AS sumqty,
				  SUM(dbo.tbl_data_in_dtl.harga) AS sumharga,
				  dbo.tbl_data_in_dtl.unit_in,
				  dbo.enum_data_in_from.doc_in_from_name,
				  (select top 1 [kode_brg] from [dbo].[tbl_custom_name] where [custom_name] = dbo.tbl_data_in_dtl.part_name_customs) as kodebrg
		");
		$this->db->from("dbo.tbl_data_in_hdr");
		$this->db->join("dbo.tbl_data_in_dtl" , "dbo.tbl_data_in_hdr.id_data_in_hdr = dbo.tbl_data_in_dtl.id_data_in_hdr", "INNER");
		$this->db->join("dbo.tbl_bc_in_hdr" , "dbo.tbl_data_in_hdr.id_terkait = dbo.tbl_bc_in_hdr.id_bc_in_hdr", "INNER");
		$this->db->join("dbo.enum_data_in_from" , "dbo.tbl_data_in_hdr.doc_in_from_id = dbo.enum_data_in_from.doc_in_from_id", "INNER");
		$this->db->where($where);
		$this->db->group_by(array("dbo.tbl_data_in_hdr.id_data_in_hdr", 
								"dbo.tbl_data_in_hdr.doc_in_no",
								"dbo.tbl_data_in_hdr.doc_in_date",
								"dbo.tbl_bc_in_hdr.BC23No",
								"dbo.tbl_bc_in_hdr.BC23Tg",
								"dbo.tbl_bc_in_hdr.PasokNama",
								"dbo.tbl_data_in_dtl.part_name_customs",
								"dbo.tbl_data_in_dtl.unit_in",
								"dbo.enum_data_in_from.doc_in_from_name"));
		$this->db->order_by($sidx,$sord);
        $result = $this->db->get()->result(); 
        //die($this->db->last_query());
        return $result;
	
	}
	
	function datalaporanpengeluaranbarang_all($limit,$start,$sidx,$sord, $where, $page){
	
		$query = $this->db->query("
		   
			;with ctebc_out_lap (
				BCNO, BCTGL, DOCOUTNO, DOCOUTDATE, NAMAPEMBELI,ASSY_CODE, ASSY_NO, JNSATUAN, JMSATUAN, TYPEBC, NILAI
			) as
			(
				SELECT     tbl_sa_hdr.BC25No, tbl_sa_hdr.BC25Tg, tbl_data_out_hdr.doc_out_no, tbl_data_out_hdr.doc_out_date, tbl_sa.NAMA_PEMBELI, tbl_sa.ASSY_CODE, tbl_sa.ASSY_NO, 
									  tbl_sa.SATUAN, tbl_sa.TOTAL_SET, enum_data_out_to.doc_out_to_name, tbl_sa.AMOUNT as NILAI
				FROM         tbl_sa_hdr INNER JOIN
									  tbl_sa ON tbl_sa_hdr.id_sa_hdr = tbl_sa.id_sa_hdr INNER JOIN
									  tbl_data_out_hdr ON tbl_sa_hdr.id_sa_hdr = tbl_data_out_hdr.id_terkait INNER JOIN
									  enum_data_out_to ON tbl_data_out_hdr.doc_out_to_id = enum_data_out_to.doc_out_to_id
				WHERE     (tbl_data_out_hdr.doc_out_to_id = 3)

				union all

				SELECT     tbl_bc_out_hdr.NODAFT, tbl_bc_out_hdr.TGDAFT, tbl_data_out_hdr.doc_out_no, tbl_data_out_hdr.doc_out_date, tbl_bc_out_hdr.NAMABELI, tbl_bc_out_dtl.KDBRG, 
									  tbl_bc_out_dtl.URBRG1, tbl_bc_out_dtl.JNSATUAN, tbl_bc_out_dtl.JMSATUAN, enum_data_out_to.doc_out_to_name, tbl_bc_out_dtl.DNilInv
				FROM         tbl_bc_out_hdr INNER JOIN
									  tbl_bc_out_dtl ON tbl_bc_out_hdr.id_bc_out_hdr = tbl_bc_out_dtl.id_bc_out_hdr INNER JOIN
									  tbl_data_out_hdr ON tbl_bc_out_hdr.id_bc_out_hdr = tbl_data_out_hdr.id_terkait INNER JOIN
									  enum_data_out_to ON tbl_data_out_hdr.doc_out_to_id = enum_data_out_to.doc_out_to_id
				WHERE     (tbl_data_out_hdr.doc_out_to_id = 2 or tbl_data_out_hdr.doc_out_to_id = 5)
			)

			 
			select * from (
			
				select ROW_NUMBER() OVER (ORDER BY BCNO) as row, BCNO, convert(varchar, BCTGL, 103) as BCTGL, DOCOUTNO, convert(varchar, DOCOUTDATE,103) as DOCOUTDATE, NAMAPEMBELI,ASSY_CODE, ASSY_NO, JNSATUAN, JMSATUAN, TYPEBC, NILAI from ctebc_out_lap
		
			where   $where  ) as x
			where x.row between  ($page - 1) * $limit + 1 AND $page * $limit
			order by $sidx $sord 
			
			
		");

		
      //  $result = $this->db->get()->result(); 
        //die($this->db->last_query());
        
		$result = $query->result_array();
		//var_dump($result);
		//die();
		return $result;
	
	
	}
	
	function datalaporanpengeluaranbarang_CountAll($where){
	
		/* $this->db->select("*");
        $this->db->from("tbl_data_out_dtl");
        $this->db->where($where);
        $result = $this->db->count_all_results(); 
        return $result; */
		$query = $this->db->query("
			;with ctebc_out_lap (
				BCNO, BCTGL, DOCOUTNO, DOCOUTDATE, NAMAPEMBELI,ASSY_CODE, ASSY_NO, JNSATUAN, JMSATUAN, TYPEBC, NILAI
			) as
			(
				SELECT     tbl_sa_hdr.BC25No, tbl_sa_hdr.BC25Tg, tbl_data_out_hdr.doc_out_no, tbl_data_out_hdr.doc_out_date, tbl_sa.NAMA_PEMBELI, tbl_sa.ASSY_CODE, tbl_sa.ASSY_NO, 
									  tbl_sa.SATUAN, tbl_sa.TOTAL_SET, enum_data_out_to.doc_out_to_name, isnull((select top 1 PRICE from [dbo].[tbl_assy_list] where [tbl_assy_list].ASSY_CODE = tbl_sa.ASSY_CODE) * isnull(tbl_sa.TOTAL_SET, 0), 0) as NILAI
				FROM         tbl_sa_hdr INNER JOIN
									  tbl_sa ON tbl_sa_hdr.id_sa_hdr = tbl_sa.id_sa_hdr INNER JOIN
									  tbl_data_out_hdr ON tbl_sa_hdr.id_sa_hdr = tbl_data_out_hdr.id_terkait INNER JOIN
									  enum_data_out_to ON tbl_data_out_hdr.doc_out_to_id = enum_data_out_to.doc_out_to_id
				WHERE     (tbl_data_out_hdr.doc_out_to_id = 3)

				union all

			SELECT     tbl_bc_out_hdr.NODAFT, tbl_bc_out_hdr.TGDAFT, tbl_data_out_hdr.doc_out_no, tbl_data_out_hdr.doc_out_date, tbl_bc_out_hdr.NAMABELI, tbl_bc_out_dtl.KDBRG, 
								  tbl_bc_out_dtl.URBRG1, tbl_bc_out_dtl.JNSATUAN, tbl_bc_out_dtl.JMSATUAN, enum_data_out_to.doc_out_to_name, tbl_bc_out_dtl.DNilInv
			FROM         tbl_bc_out_hdr INNER JOIN
								  tbl_bc_out_dtl ON tbl_bc_out_hdr.id_bc_out_hdr = tbl_bc_out_dtl.id_bc_out_hdr INNER JOIN
								  tbl_data_out_hdr ON tbl_bc_out_hdr.id_bc_out_hdr = tbl_data_out_hdr.id_terkait INNER JOIN
								  enum_data_out_to ON tbl_data_out_hdr.doc_out_to_id = enum_data_out_to.doc_out_to_id
			WHERE     (tbl_data_out_hdr.doc_out_to_id = 2)
			)
			select count(*) as cnt from ctebc_out_lap where   $where 	
		
		");
		$result = $query->result_array();
		foreach ( $result as $row ){
		 
			$result = $row['cnt'];
		 }
		 return $result;
			
	}
	
	function datawip_All($limit,$start,$sidx,$sord, $where, $date1, $date2, $page){
		/*$this->db->select(" 
				  row_number() over(order by (select 0)) as nomorurut, tbl_data_out_hdr.doc_out_to_id, SUM(tbl_data_out_dtl.qty) AS qty, tbl_data_part_hs.part_name_customs, tbl_data_out_dtl.unit_out as unit_out, tbl_data_part_hs.kode_barang as kodebrg, 
					isnull((case when tbl_data_out_dtl.unit_out='P' then 'PCS' when tbl_data_out_dtl.unit_out='M' then 'MTR' when tbl_data_out_dtl.unit_out='K' then 'KGM' END), 'PCS') as satuan,
				'-' as keterangan
		");
		$this->db->from("tbl_data_out_dtl");
		$this->db->join(" tbl_data_part_hs" , "tbl_data_part_hs.part_no2 = REPLACE(tbl_data_out_dtl.part_number, CHAR(32), '')", "LEFT OUTER");
		$this->db->join(" tbl_data_out_hdr" , "tbl_data_out_dtl.id_data_out_hdr = tbl_data_out_hdr.id_data_out_hdr", "INNER");
		$this->db->where($where);
		$this->db->group_by(array("tbl_data_out_dtl.unit_out", 
								"tbl_data_out_hdr.doc_out_to_id",
								"tbl_data_part_hs.part_name_customs",
								"tbl_data_part_hs.kode_barang"
								)); 
        $this->db->limit($limit,$start);
        $this->db->order_by($sidx,$sord);
        $result = $this->db->get()->result(); 
        //die($this->db->last_query());
        return $result; */
		
		$query = $this->db->query("
		select row, part_no, part_name,qty,unit from (
			select row_number() OVER (ORDER BY part_no) AS row, part_no, part_name,qty, unit from [dbo].[f_mutasi_wip]('$date1','$date2')
			) x where x.row  between  ($page - 1) * $limit + 1 AND $page * $limit
			order by x.part_no asc
		
		");
		
		 //  $result = $this->db->get()->result(); 
       // die($this->db->last_query());
        
		$result = $query->result_array();
		//var_dump($result);
		//die();	
		return $result;
		
	}
	
	function datawip_CountAll($where, $date1, $date2){
	
	//	$this->db->select("
	//		row_number() over(order by (select 0)) 
	//	");
	//	$this->db->from("tbl_data_out_dtl");
	//	$this->db->join(" tbl_data_part_hs" , "tbl_data_part_hs.part_no2 = REPLACE(tbl_data_out_dtl.part_number, CHAR(32), '')", "LEFT OUTER");
	//	$this->db->join(" tbl_data_out_hdr" , "tbl_data_out_dtl.id_data_out_hdr = tbl_data_out_hdr.id_data_out_hdr", "INNER");
	//	$this->db->where($where);
   //  $this->db->group_by(array("tbl_data_out_dtl.unit_out",        
//								"tbl_data_out_hdr.doc_out_to_id",
//								"tbl_data_part_hs.part_name_customs"
//								)); 
		//$this->db->group_by(array("a.inv_hdr_id", "b.part_name_customs","a.r7")); 
     //   $query = $this->db->get();
//		$rowcount = $query->num_rows();
//		return $rowcount;
	//	$query = $this->db->query("
	//	select row, part_no, part_name,qty,unit from (
	//		select row_number() OVER (ORDER BY part_no) AS row, part_no, part_name,qty, unit from [dbo].[f_mutasi_wip]('$date1','$date2')
	//		) x where x.row between $start and (1+$start)*$limit
	//		order by x.part_no asc
	//	
	//	");
		
		 //  $result = $this->db->get()->result(); 
       // die($this->db->last_query());
        
	//	$result = $query->result_array();
		//var_dump($result);
		//die();	
	//	return $result;
	
		 $query = $this->db->query("
			
			select COUNT(*) as cnt from (
			select row_number() OVER (ORDER BY part_no) AS row, part_no, part_name,qty, unit from [dbo].[f_mutasi_wip]('$date1','$date2')
			--where (qty_saldoawal <> 0 or qty_in <> 0 or qty_out <>0)  

			) x
		 ");

        //die($this->db->last_query());
        
		$result = $query->result_array();
		//var_dump($result);
		//die();
		$xx=0;
		foreach ($result as $r){
			$xx=$r['cnt'];
		}
		return $xx; 
		
	
	}
	
	function datamutasibj_All($limit,$start,$sidx,$sord, $where,$wheredate, $date1, $date2, $page){
	
		/* $query = $this->db->query("
		
			declare @mutasibj table (
				part_no varchar(100) null
				,part_name varchar(100) null
				,stock_awal float null
				,qty_in float null
				,qty_out float null
				,sisa float null
				,unit varchar(100) null
				,qty_in_by_date float null
				,qty_out_by_date float null
				,sisa_by_date float null
				)
			;with
			 ct_assy_list(
					part_no,part_name
				 ) as
				 (
					SELECT 
						 [ASSY_CODE]
						  ,[ASSY_NO]
					      
					  FROM [dbo].[tbl_assy_list]
					  group by 
						[ASSY_CODE]
						  ,[ASSY_NO]
				 ),
				 ctestok_trxawal(
					part_no, part_name, qty_in, unit
				 
				 ) as
				 (
					select part_no, part_name, sum(qty_in), unit_in 
					from tbl_data_in_dtl inner join tbl_data_in_hdr on tbl_data_in_hdr.id_data_in_hdr = tbl_data_in_dtl.id_data_in_hdr
					where type_brg=2 and status='APPROVED'
					group by  part_no, part_name,unit_in
				 ),
				 ctest_trxoutawal(part_no, part_name,qty_out, unit) as (
					SELECT     assy_code, part_name_customs, sum(qty), unit_out
					FROM         tbl_data_out_dtl
					where type_items='FG'
					group by
					assy_code, part_name_customs,unit_out
				 ),
				 ctestokawal(part_no, qty) as (
					select part_no, qty from dbo.saldoawal('$date1','$date2','FG')
				 ),
				  ctestok_in_date(
					part_no, part_name, qty_in
				 
				 ) as
				 (
					select part_no, part_name, sum(qty_in) as qty_in
					from tbl_data_in_dtl inner join tbl_data_in_hdr on tbl_data_in_hdr.id_data_in_hdr = tbl_data_in_dtl.id_data_in_hdr
					where type_brg=2 and status='APPROVED' and tbl_data_in_hdr.doc_in_date between   $wheredate
					group by  part_no, part_name,unit_in
				 ),
				 ctest_trxout_bydate(part_no, part_name,qty_out, unit) as (
					SELECT     assy_code, part_name_customs, sum(qty), unit_out
					FROM         tbl_data_out_dtl
					where type_items='FG' and out_date between   $wheredate 
					group by
					assy_code, part_name_customs,unit_out
				 )
				
					 
                  insert into @mutasibj (part_no,part_name,stock_awal,qty_in,qty_out,sisa,unit,qty_in_by_date,qty_out_by_date,sisa_by_date 
                  )
				SELECT 
				
				 ct_assy_list.part_no
				 , ct_assy_list.part_name
				 ,isnull(ctestokawal.qty,0) as stok_awal
				 ,isnull(ctestok_trxawal.qty_in, 0) as qty_in
				 ,isnull(ctest_trxoutawal.qty_out, 0) as qty_out
				 ,(isnull(ctestokawal.qty,0) + isnull(ctestok_trxawal.qty_in,0)) -  isnull(ctest_trxoutawal.qty_out, 0) as sisa
				 ,(isnull(ctestok_trxawal.unit, 'PCE')) as unit
				 ,isnull(ctestok_in_date.qty_in,0) as qty_in_by_date
				 ,isnull(ctest_trxout_bydate.qty_out,0) as qty_out_by_date
				  ,((isnull(ctestokawal.qty,0) + isnull(ctestok_in_date.qty_in,0)) - (isnull(ctest_trxout_bydate.qty_out,0))) as sisa_by_date 
				 from ct_assy_list 
				 left outer join ctestok_trxawal on ct_assy_list.part_no = ctestok_trxawal.part_no
				 left outer join ctest_trxoutawal on ct_assy_list.part_no=ctest_trxoutawal.part_no
				 left outer join ctestokawal on ct_assy_list.part_no=ctestokawal.part_no
				 left outer join ctestok_in_date on ct_assy_list.part_no=ctestok_in_date.part_no
				 left outer join ctest_trxout_bydate on ct_assy_list.part_no=ctest_trxout_bydate.part_no
				 
				 select * from (
					 select 
						row_number() OVER (ORDER BY part_no) AS row,
						part_no,
						part_name,
						stock_awal,
						qty_in,
						qty_out,
						sisa,
						unit,
						qty_in_by_date,
						qty_out_by_date,
						sisa_by_date
						from @mutasibj
						where  $where and (stock_awal <> 0 or qty_in <> 0 or qty_out <> 0 or qty_in_by_date <> 0 or qty_out_by_date <> 0)  
				 ) x where x.row between $start and (1+$start)*$limit
			    order by x.part_no asc
				--order by $sidx $sord
			
		");

		
      //  $result = $this->db->get()->result(); 
        //die($this->db->last_query());
        
		$result = $query->result_array();
		//var_dump($result);
		//die();
		return $result; */
		
		$query = $this->db->query("
			select * from (
			select row_number() OVER (ORDER BY part_no) AS row, part_no,part_name,stock_awal,qty_in,qty_out,sisa,unit,qty_in_by_date,qty_out_by_date,sisa_by_date,stock_opname,adj  from [dbo].[f_mutasi_fg]('$date1','$date2','FG')
			

			) x where x.row between  ($page - 1) * $limit + 1 AND $page * $limit
			order by x.part_no asc
		
		");
		//--where stock_awal <> 0 or qty_in <> 0 or qty_out <>0 
		//	--and sisa_by_date > 0
		 //  $result = $this->db->get()->result(); 
        //die($this->db->last_query());
        
		$result = $query->result_array();
		//die($this->db->last_query());
		//var_dump($result);
		//die();	
		return $result;
	}
	
	function datamutasibj_CountAll($where, $wheredate, $date1, $date2){
			/* $query = $this->db->query("
		
			declare @mutasibjx table (
				part_no varchar(100) null
				,part_name varchar(100) null
				,stock_awal float null
				,qty_in float null
				,qty_out float null
				,sisa float null
				,unit varchar(100) null
				,qty_in_by_date float null
				,qty_out_by_date float null
				
				)
			;with
			 ct_assy_list(
					part_no,part_name
				 ) as
				 (
					SELECT 
						 [ASSY_CODE]
						  ,[ASSY_NO]
					      
					  FROM [dbo].[tbl_assy_list]
					  group by 
						[ASSY_CODE]
						  ,[ASSY_NO]
				 ),
				 ctestok_trxawal(
					part_no, part_name, qty_in, unit
				 
				 ) as
				 (
					select part_no, part_name, sum(qty_in), unit_in 
					from tbl_data_in_dtl inner join tbl_data_in_hdr on tbl_data_in_hdr.id_data_in_hdr = tbl_data_in_dtl.id_data_in_hdr
					where type_brg=2 and status='APPROVED'
					group by  part_no, part_name,unit_in
				 ),
				 ctest_trxoutawal(part_no, part_name,qty_out, unit) as (
					SELECT     assy_code, part_name_customs, sum(qty), unit_out
					FROM         tbl_data_out_dtl
					where type_items='FG'
					group by
					assy_code, part_name_customs,unit_out
				 ),
				 ctestokawal(part_no, qty) as (
					select part_no, qty	from dbo.tbl_stock_awal
				 ),
				  ctestok_in_date(
					part_no, part_name, qty_in
				 
				 ) as
				 (
					select part_no, part_name, sum(qty_in) as qty_in
					from tbl_data_in_dtl inner join tbl_data_in_hdr on tbl_data_in_hdr.id_data_in_hdr = tbl_data_in_dtl.id_data_in_hdr
					where type_brg=2 and status='APPROVED' and tbl_data_in_hdr.doc_in_date between   $wheredate
					group by  part_no, part_name,unit_in
				 ),
				 ctest_trxout_bydate(part_no, part_name,qty_out, unit) as (
					SELECT     assy_code, part_name_customs, sum(qty), unit_out
					FROM         tbl_data_out_dtl
					where type_items='FG' and out_date between   $wheredate 
					group by
					assy_code, part_name_customs,unit_out
				 )
				
					 
                  insert into @mutasibjx (part_no,part_name,stock_awal,qty_in,qty_out,sisa,unit,qty_in_by_date,qty_out_by_date
                  )
				SELECT 
				
				 ct_assy_list.part_no
				 , ct_assy_list.part_name
				 ,isnull(ctestokawal.qty,0) as stok_awal
				 ,isnull(ctestok_trxawal.qty_in, 0) as qty_in
				 ,isnull(ctest_trxoutawal.qty_out, 0) as qty_out
				 ,(isnull(ctestokawal.qty,0) + isnull(ctestok_trxawal.qty_in,0)) -  isnull(ctest_trxoutawal.qty_out, 0) as sisa
				 ,(isnull(ctestok_trxawal.unit, 'PCE')) as unit
				 ,isnull(ctestok_in_date.qty_in,0) as qty_in_by_date
				 ,isnull(ctest_trxout_bydate.qty_out,0) as qty_out_by_date
				
				 from ct_assy_list 
				 left outer join ctestok_trxawal on ct_assy_list.part_no = ctestok_trxawal.part_no
				 left outer join ctest_trxoutawal on ct_assy_list.part_no=ctest_trxoutawal.part_no
				 left outer join ctestokawal on ct_assy_list.part_no=ctestokawal.part_no
				 left outer join ctestok_in_date on ct_assy_list.part_no=ctestok_in_date.part_no
				 left outer join ctest_trxout_bydate on ct_assy_list.part_no=ctest_trxout_bydate.part_no
				 
				 select 
					count(*) as cnt
					from @mutasibjx
			  
				where  $where and (stock_awal <> 0 or qty_in <> 0 or qty_out <> 0 or qty_in_by_date <> 0 or qty_out_by_date <> 0) 
				
			
		");

		
      //  $result = $this->db->get()->result(); 
        //die($this->db->last_query());
        
		$result = $query->result_array();
		//var_dump($result);
		//die();
		$xx=0;
		foreach ($result as $r){
			$xx=$r['cnt'];
		}
		return $xx; */
		
		 $query = $this->db->query("
			select COUNT(*) as cnt from (
			select row_number() OVER (ORDER BY part_no) AS row, part_no,part_name,stock_awal,qty_in,qty_out,sisa,unit,qty_in_by_date,qty_out_by_date,sisa_by_date  from [dbo].[f_mutasi_fg]('$date1','$date2','FG')
			where (stock_awal <> 0 or qty_in <> 0 or qty_out <>0)  

			) x
		 ");
		//  $result = $this->db->get()->result(); 
        //die($this->db->last_query());
        
		$result = $query->result_array();
		//var_dump($result);
		//die();
		$xx=0;
		foreach ($result as $r){
			$xx=$r['cnt'];
		}
		return $xx; 
	}
	
	function datamutasibb_All($limit,$start,$sidx,$sord, $where,$wheredate, $date1, $date2, $page){
		$query = $this->db->query("
			select row, kodebarang, namabarang,qty_saldoawal,qty_in,qty_out,saldo_akhir,unit,stock_opname, adj from (
			select row_number() OVER (ORDER BY kodebarang) AS row,
			kodebarang,
			namabarang,
			round(qty_saldoawal, 2) as qty_saldoawal,
			round(qty_in, 2) as qty_in,
			round(qty_out, 2) as qty_out,
			round(saldo_akhir, 2) as saldo_akhir,
			unit,
			round(stock_opname, 2) as stock_opname,
			adj	
			from dbo.f_mutasi_bb('$date1','$date2','RM')
			--where qty_saldoawal <> 0 or qty_in <> 0 or qty_out <>0

			) x where x.row between  ($page - 1) * $limit + 1 AND $page * $limit
			order by x.kodebarang asc
		
		");
		
		 //  $result = $this->db->get()->result(); 
        //die($this->db->last_query());
        
		$result = $query->result_array();
		//var_dump($result);
		//die();	
		return $result;
	
	}
	
	function datamutasibb_CountAll($where, $wheredate, $date1, $date2){
		 $query = $this->db->query("
			
			select COUNT(*) as cnt from (
			select row_number() OVER (ORDER BY kodebarang) AS row,kodebarang,namabarang,qty_saldoawal,qty_in,qty_out,saldo_akhir,unit from dbo.f_mutasi_bb('$date1','$date2','RM')
			--where (qty_saldoawal <> 0 or qty_in <> 0 or qty_out <>0)  

			) x
		 ");

        //die($this->db->last_query());
        
		$result = $query->result_array();
		//var_dump($result);
		//die();
		$xx=0;
		foreach ($result as $r){
			$xx=$r['cnt'];
		}
		return $xx; 
	
	
	}
	
	function datamutasimpk_All($limit,$start,$sidx,$sord, $where,$wheredate, $date1, $date2, $page){
		$query = $this->db->query("
			select row, kodebarang, namabarang,qty_saldoawal,qty_in,qty_out,saldo_akhir,unit,stock_opname, adj from (
			select row_number() OVER (ORDER BY kodebarang) AS row,
			kodebarang,
			namabarang,
			qty_saldoawal,
			qty_in,
			qty_out,
			saldo_akhir,
			unit,
			stock_opname,
			adj
			from dbo.f_mutasi_mpk('$date1','$date2')
			--where qty_saldoawal <> 0 or qty_in <> 0 or qty_out <>0

			) x where x.row between  ($page - 1) * $limit + 1 AND $page * $limit
			order by x.kodebarang asc
		
		");
		
		 //  $result = $this->db->get()->result(); 
        //die($this->db->last_query());
        
		$result = $query->result_array();
		//var_dump($result);
		//die();	
		return $result;
	
	}
	
	function datamutasimpk_CountAll($where, $wheredate, $date1, $date2){
		 $query = $this->db->query("
			
			select COUNT(*) as cnt from (
			select row_number() OVER (ORDER BY kodebarang) AS row,kodebarang,namabarang,qty_saldoawal,qty_in,qty_out,saldo_akhir,unit from dbo.f_mutasi_mpk('$date1','$date2')
			--where (qty_saldoawal <> 0 or qty_in <> 0 or qty_out <>0)  

			) x
		 ");

        //die($this->db->last_query());
        
		$result = $query->result_array();
		//var_dump($result);
		//die();
		$xx=0;
		foreach ($result as $r){
			$xx=$r['cnt'];
		}
		return $xx; 
	
	
	}
	
	function datamutasiscr_All($limit,$start,$sidx,$sord, $where,$wheredate, $date1, $date2, $page){
		$query = $this->db->query("
			select row, kodebarang, namabarang,qty_saldoawal,qty_in,qty_out,saldo_akhir,unit,stock_opname, adj from (
			select row_number() OVER (ORDER BY kodebarang) AS row,
			kodebarang,
			namabarang,
			qty_saldoawal,
			qty_in,
			qty_out,
			saldo_akhir,
			unit,
			stock_opname,
			adj			
			from [dbo].[f_mutasi_scr]('$date1','$date2')
			--where qty_saldoawal <> 0 or qty_in <> 0 or qty_out <>0

			) x where x.row between  ($page - 1) * $limit + 1 AND $page * $limit
			order by x.kodebarang asc
		
		");
		
		 //  $result = $this->db->get()->result(); 
        //die($this->db->last_query());
        
		$result = $query->result_array();
		//var_dump($result);
		//die();	
		return $result;
	
	}
	
	function datamutasiscr_CountAll($where, $wheredate, $date1, $date2){
		 $query = $this->db->query("
			
			select COUNT(*) as cnt from (
			select row_number() OVER (ORDER BY kodebarang) AS row,kodebarang,namabarang,qty_saldoawal,qty_in,qty_out,saldo_akhir,unit from [dbo].[f_mutasi_scr]('$date1','$date2')
			--where (qty_saldoawal <> 0 or qty_in <> 0 or qty_out <>0)  

			) x
		 ");

        //die($this->db->last_query());
        
		$result = $query->result_array();
		//var_dump($result);
		//die();
		$xx=0;
		foreach ($result as $r){
			$xx=$r['cnt'];
		}
		return $xx; 
	
	
	}
	
	
	
	
	
	
	
	
}
	
