<?php
class CT_stock_transfer extends CI_Controller{
  function __construct()
    {
      parent::__construct();
      if (!$this->dx_auth->is_logged_in()) {
             header('HTTP/1.1 401 Unauthorize');die('expire'); 
        }
        
        $uid = $this->session->userdata('DX_user_id');
        
        if(!permissionAuth(2) && $uid != RID){ 
            header('HTTP/1.1 401 Unauthorize');die('noaccess');
        } 
		$this->load->model("inventory/mdl_outgoing", 'mdl_outgoing');
  }

  function index(){
 	
	$this->load->view('inventory/view_stocktransfer');
  }


 function loadDataProdRequest(){
 
	$search = isset($_POST['_search'])?$_POST['_search']:false;
	$searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
	$searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
	$filters =  isset($_POST['filters'])?$_POST['filters']:'';
	
	$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
	$limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
	$sidx = isset($_POST['sidx'])?$_POST['sidx']:'tbl_data_out_hdr.id_data_out_hdr'; // get index row - i.e. user click to sort
	$sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
	
	$where = " 1=1 and tbl_data_out_hdr.doc_out_to_id=1 ";
	
	if ($search == 'true'){
		if (!empty($filters)){
			$array = json_decode($filters);
			foreach($array->rules as $arr=>$k){
				
				if ( $k->field == "doc_out_no" ) {
					$searchString = $k->data;
					$where .= " and tbl_data_out_hdr.doc_out_no like '%".$searchString."%'"; 
				} else if ( $k->field == "doc_out_date" ) {
					$searchString = $k->data;
					$where .= " and tbl_data_out_hdr.doc_out_date =  '$searchString' "; 
				}  else if ( $k->field == "approve_date" ) {
					$searchString = $k->data;
					$where .= " and convert(date, approve_date) =  '$searchString' "; 
				}
			}
		}
	}
	
	$start = $limit*$page - $limit;
	if ($start<0) $start=0;
	
	$records = $this->mdl_outgoing->data_out_hdr_All($limit,$start,$sidx,$sord, $where);
	$count = $this->mdl_outgoing->data_out_hdr_CountAll($where);
	if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
	if ($page > $total_pages) $page=$total_pages;
	
	$responce->page = $page;
	$responce->total = $total_pages;
	$responce->records =  $count;
	$i=0;
	foreach($records as $r){
					
		$responce->rows[$i]['id_data_out_hdr']=$r->id_data_out_hdr; 
		$responce->rows[$i]['doc_out_no']=$r->doc_out_no; 
		$responce->rows[$i]['doc_out_date']=$r->doc_out_date; 
		$responce->rows[$i]['doc_out_to_id']=$r->doc_out_to_id;
		$responce->rows[$i]['id_data_out_hdr_h_status']= $r->id_data_out_hdr_h_status;
		$responce->rows[$i]['id_terkait']=$r->id_terkait;
		$responce->rows[$i]['status']=$r->status;
		$responce->rows[$i]['approve_date']=$r->approve_date;
		
		/*$responce->rows[$i]['create_by']=$r->create_by;
		$responce->rows[$i]['create_date']=$r->create_date;
		$responce->rows[$i]['update_by']=$r->update_by;
		$responce->rows[$i]['update_date']=$r->update_date; */
		
		$i++;
	}
	echo json_encode($responce);
	
		
  }
  function view_stocktransfer_load_data(){
  
  
	$this->load->view('inventory/view_stocktransfer_load_data');
  
  }
  
  function loaddatavgi(){
  
	$search = isset($_POST['_search'])?$_POST['_search']:false;
	$searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
	$searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
	$filters =  isset($_POST['filters'])?$_POST['filters']:'';
	
	$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
	$limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
	$sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_vgi_hdr'; // get index row - i.e. user click to sort
	$sord = isset($_POST['sord'])?$_POST['sord']:'asc'; // get the direction	
	
		
	$where = " status='new' ";
	
	if ($search == 'true'){
		if (!empty($filters)){
			$array = json_decode($filters);
			foreach($array->rules as $arr=>$k){
				
				if ( $k->field == "REG_DATE" ) {
					$searchString = $k->data;
					$where .= " and REG_DATE = '$searchString'"; 
				} else if ( $k->field == "ISSUE_NO" ) {
					$searchString = $k->data;
					$where .= " and ISSUE_NO like  '%".$searchString."%'"; 
				} 
			}
		}
	}
	
	$start = $limit*$page - $limit;
	if ($start<0) $start=0;
	
	$records = $this->mdl_outgoing->data_vgi_all($limit,$start,$sidx,$sord, $where);
	$count = $this->mdl_outgoing->data_vgi_CountAll($where);
	if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
	if ($page > $total_pages) $page=$total_pages;
	
	$responce->page = $page;
	$responce->total = $total_pages;
	$responce->records =  $count;
	$i=0;
	foreach($records as $r){
					
		$responce->rows[$i]['id_vgi_hdr']=$r->id_vgi_hdr; 
		$responce->rows[$i]['from_file_name']=$r->from_file_name; 
		$responce->rows[$i]['exec_date']=$r->exec_date;
		$responce->rows[$i]['itemcount']=$r->itemcount;
		$responce->rows[$i]['status']=$r->status;
		$responce->rows[$i]['ISSUE_NO']=$r->ISSUE_NO;
		$responce->rows[$i]['REG_DATE']=$r->REG_DATE;
		$i++;
	}
	echo json_encode($responce);
	
  
  
  }
  
  function getdatavgi(){
		$qry = $this->db->query("
			BEGIN TRY
			
			exec(' EXEC [sp_vgi] ')
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			
			");
		$rs = $qry->result_array();
        $r = $qry->result();		
		//var_dump($r);die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description'].' Data Loaded');
		}  
		echo json_encode($responce);
  
  }
  
  function view_stocktransfer_vgi_dtl(){
	$id_vgi_hdr = (isset($_POST['id_vgi_hdr']) && !empty($_POST['id_vgi_hdr']))?$_POST['id_vgi_hdr']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
	$data["id_vgi_hdr"]	=$id_vgi_hdr;
	$this->load->view('inventory/view_stocktransfer_vgi_dtl',$data);  
  
  }
  function loaddata_vgi_dtl(){
  
	$search = isset($_POST['_search'])?$_POST['_search']:false;
	$searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
	$searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
	$filters =  isset($_POST['filters'])?$_POST['filters']:'';
	
	$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
	$limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
	$sidx = isset($_POST['sidx'])?$_POST['sidx']:'tbl_vgi_dtl.id_vgi_dtl'; // get index row - i.e. user click to sort
	$sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
	
	$id_vgi_hdr = (isset($_POST['id_vgi_hdr_dtl']) && !empty($_POST['id_vgi_hdr_dtl']))?$_POST['id_vgi_hdr_dtl']:die('{"sts":"ERROR","desc":" Param ID Tidak Ditemukan"}');
		
	$where = " tbl_vgi_dtl.id_vgi_hdr=$id_vgi_hdr ";
	
	$nullin = (isset($_POST['nullin']) && !empty($_POST['nullin']))?$_POST['nullin']:'';
	if ($nullin == 'ya'){ $where=" tbl_vgi_dtl.id_vgi_hdr=$id_vgi_hdr and tbl_data_part_hs.part_no is null "; };
	
	if ($search == 'true'){
		if (!empty($filters)){
			$array = json_decode($filters);
			foreach($array->rules as $arr=>$k){
				
				if ( $k->field == "vgi_date" ) {
					$searchString = $k->data;
					$where .= " and tbl_vgi_dtl.vgi_date = '$searchString'"; 
				} else if ( $k->field == "note" ) {
					$searchString = $k->data;
					$where .= " and tbl_vgi_dtl.r1 like  '%".$searchString."%'"; 
				} else if ( $k->field == "assycode" ) {
					$searchString = $k->data;
					$where .= " and tbl_vgi_dtl.r2 like  '%".$searchString."%'"; 
				} else if ( $k->field == "part_number" ) {
					$searchString = $k->data;
					$where .= " and tbl_vgi_dtl.part_number like  '%".$searchString."%'"; 
				} else if ( $k->field == "qty" ) {
					$searchString = $k->data;
					$where .= " and tbl_vgi_dtl.qty=$searchString"; 
				} else if ( $k->field == "part_name" ) {
					$searchString = $k->data;
					$where .= " and tbl_data_part_hs.part_name like  '%".$searchString."%'"; 
				} else if ( $k->field == "part_name_customs" ) {
					$searchString = $k->data;
					$where .= " and tbl_data_part_hs.part_name_customs like  '%".$searchString."%'"; 
				} 
			}
		}
	}
	
	
		
	$start = $limit*$page - $limit;
	if ($start<0) $start=0;
	
	$records = $this->mdl_outgoing->data_vgi_dtl_all($limit,$start,$sidx,$sord, $where);
	$count = $this->mdl_outgoing->data_vgi_dtl_CountAll($where);
	
	
	
	if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
	if ($page > $total_pages) $page=$total_pages;
	
	$responce->page = $page;
	$responce->total = $total_pages;
	$responce->records =  $count;
	$i=0;
	foreach($records as $r){
					
		$responce->rows[$i]['id_vgi_dtl']=$r->id_vgi_dtl; 
		$responce->rows[$i]['id_vgi_hdr']=$r->id_vgi_hdr;
		$responce->rows[$i]['vgi_date']=$r->vgi_date;
		$responce->rows[$i]['note']=$r->note;
		$responce->rows[$i]['assycode']=$r->assycode;
		$responce->rows[$i]['part_number']=$r->part_number;
		$responce->rows[$i]['qty']=$r->qty;
		$responce->rows[$i]['part_name']=$r->part_name;
		$responce->rows[$i]['part_name_customs']=$r->part_name_customs;
		$i++;
	}
	echo json_encode($responce);
	
  }
  function vgi_dtl_isnull(){
  
	$id_vgi_hdr = (isset($_POST['id_vgi_hdr_dtlx']) && !empty($_POST['id_vgi_hdr_dtlx']))?$_POST['id_vgi_hdr_dtlx']:die('{"sts":"ERROR","desc":" Param ID Tidak Ditemukan"}');
	$where = " tbl_vgi_dtl.id_vgi_hdr=$id_vgi_hdr and tbl_data_part_hs.part_no is null ";
	$count = $this->mdl_outgoing->data_vgi_dtl_null($where);
	//echo $count; die();
	if ($count > 0){
		$responce = array("sts"=>"00",  "desc" => " Ada $count data Part Number di part hs belum diisi (Belum Match).<br />Isikan di master data. Click Disini untuk cek.");
	} else { 
		$responce = array("sts"=>"01", "desc" => ' '); };
	echo json_encode($responce);	
  }
  
  function st_process_outgoing(){
	$id_vgi_hdr = (isset($_POST['id_hdr']) && !empty($_POST['id_hdr']))?$_POST['id_hdr']:die('{"sts":"ERROR","desc":" Param ID Tidak Ditemukan"}');
	
	$user = $this->session->userdata('DX_username');	
	$qry = $this->db->query("
			BEGIN TRY
			
			EXEC ('[sp_stock_transfer] @id_vgi_hdr=$id_vgi_hdr ,@user=''$user'' ')
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			");
	$rs = $qry->result_array();
	$r = $qry->result();		
	//var_dump($r);die();
	foreach ($rs as $row){
		$responce = array("sts" => $row['status'], "desc" => $row['description']);
	}  
	echo json_encode($responce);
  
  
  }
  
  function st_process_approved(){
	$id_data_out_hdr = (isset($_POST['id_data_out_hdr']) && !empty($_POST['id_data_out_hdr']))?$_POST['id_data_out_hdr']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
	$id_terkait = (isset($_POST['id_terkait']) && !empty($_POST['id_terkait']))?$_POST['id_terkait']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
	$doc_out_to_id = (isset($_POST['doc_out_to_id']) && !empty($_POST['doc_out_to_id']))?$_POST['doc_out_to_id']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		
	
	$user = $this->session->userdata('DX_username');	
	$qry = $this->db->query("
			
			EXEC [spi_outgoing_approved]
			@id_data_out_hdr=$id_data_out_hdr , 
			@id_terkait=$id_terkait , 
			@doc_out_to_id =$doc_out_to_id, 
			@user='$user'
			");
	$rs = $qry->result_array();
	//$r = $qry->result();		
	//var_dump($r);die();
	foreach ($rs as $row){
		$responce = array("sts" => $row['status'], "desc" => $row['description']);
	}  
	echo json_encode($responce);
  
  
  
  }
  
  function view_stocktransfer_dtl(){
  
	$id_data_out_hdr = (isset($_POST['id_data_out_hdr']) && !empty($_POST['id_data_out_hdr']))?$_POST['id_data_out_hdr']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
	$data["id_data_out_hdr"]	=$id_data_out_hdr;
	$this->load->view('inventory/view_stocktransfer_dtl',$data);  
  
  }
  
  function data_stocktransfer_dtl(){
  
	$search = isset($_POST['_search'])?$_POST['_search']:false;
	$searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
	$searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
	$filters =  isset($_POST['filters'])?$_POST['filters']:'';
	
	$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
	$limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
	$sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_data_out_dtl'; // get index row - i.e. user click to sort
	$sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
	$id_data_out_hdr = (isset($_POST['id_data_out_hdr_dtl']) && !empty($_POST['id_data_out_hdr_dtl']))?$_POST['id_data_out_hdr_dtl']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
	
	
	$where = " tbl_data_out_dtl.[id_data_out_hdr]=$id_data_out_hdr ";
	
		
	if ($search == 'true'){
		if (!empty($filters)){
			$array = json_decode($filters);
			foreach($array->rules as $arr=>$k){
				
				if ( $k->field == "out_date" ) {
					$searchString = $k->data;
					$where .= " and out_date = '$searchString'"; 
				} else if ( $k->field == "part_name_customs" ) {
					$searchString = $k->data;
					$where .= " and part_name_customs like  '%".$searchString."%'"; 
				} else if ( $k->field == "assy_code" ) {
					$searchString = $k->data;
					$where .= " and assy_code like  '%".$searchString."%'"; 
				} else if ( $k->field == "part_number" ) {
					$searchString = $k->data;
					$where .= " and part_number like  '%".$searchString."%'"; 
				} else if ( $k->field == "qty" ) {
					$searchString = $k->data;
					$where .= " and qty=$searchString"; 
				}
			}
		}
	}
	
	
		
	$start = $limit*$page - $limit;
	if ($start<0) $start=0;
	
	$records = $this->mdl_outgoing->data_stocktransfer_dtl_all($limit,$start,$sidx,$sord, $where);
	$count = $this->mdl_outgoing->data_stocktransfer_dtl_CountAll($where);
	
	
	
	if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
	if ($page > $total_pages) $page=$total_pages;
	
	$responce->page = $page;
	$responce->total = $total_pages;
	$responce->records =  $count;
	$i=0;
	foreach($records as $r){
					
		$responce->rows[$i]['id_data_out_dtl']=$r->id_data_out_dtl; 
		$responce->rows[$i]['id_data_out_hdr']=$r->id_data_out_hdr;
		$responce->rows[$i]['id_part_number']=$r->id_part_number;
		$responce->rows[$i]['out_date']=$r->out_date;
		$responce->rows[$i]['part_name_customs']=$r->part_name_customs;
		$responce->rows[$i]['part_number']=$r->part_number;
		$responce->rows[$i]['qty']=$r->qty;
		$responce->rows[$i]['qty_supply']=$r->qty_supply;
		$responce->rows[$i]['unit_out']=$r->unit_out;
		$responce->rows[$i]['unit_konversi']=$r->unit_konversi;
		$responce->rows[$i]['pengali']=$r->pengali;
		$responce->rows[$i]['box']=$r->box;
		$responce->rows[$i]['pack']=$r->pack;
		$responce->rows[$i]['assy_code']=$r->assy_code;
		
		$i++;
	}
	echo json_encode($responce);
	
  
  
  }
  
  function st_alldata_process_outgoing(){
	$user = $this->session->userdata('DX_username');	
	$qry = $this->db->query("
			BEGIN TRY
			
			EXEC ('[sp_stock_transfer_all] @username=''$user'' ')
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			
			
	");
	$rs = $qry->result_array();
	$r = $qry->result();		
	//var_dump($r);die();
	foreach ($rs as $row){
		$responce = array("sts" => $row['status'], "desc" => $row['description']);
	}  
	echo json_encode($responce);
  
  
  }
  function st_process_approved_selected(){
	$user = $this->session->userdata('DX_username');	
	$postData = (isset($_POST['postData']) && !empty($_POST['postData']))?$_POST['postData']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
	
	$dataposted = json_decode($postData);
	$responce = '';
	$suksescnt = 0;
	$id_data_out_hdr = '';$id_terkait = '';$doc_out_to_id = 1;
	foreach ($dataposted as $r) {
		$id_data_out_hdr = $r->id_data_out_hdr;
		$id_terkait = $r->id_terkait;
		$doc_out_to_id = $r->doc_out_to_id;
		
		if ($r->status != 'APPROVED'){
			
			//echo $id_data_out_hdr . ' .... ' . $id_terkait . '........'.$doc_out_to_id . '<br>';
			
			$qry = $this->db->query("
			
			EXEC [spi_outgoing_approved]
			@id_data_out_hdr=$id_data_out_hdr , 
			@id_terkait=$id_terkait , 
			@doc_out_to_id =$doc_out_to_id, 
			@user='$user'
			");
			$rs = $qry->result_array();
			foreach ($rs as $row){
				
				if ($row['status'] == '00'){
					$suksescnt = $suksescnt + 1;
				
				};
			
			}  
		}	
		/* 
		 $r->doc_out_no;
			[doc_out_date] => 10/08/2013
            [doc_out_to_id] => 1
            [id_data_out_hdr_h_status] => 54356
            [id_terkait] => 5196
            [approve_date] => 11/20/2013
            [status] => APPROVED */
	
	}
	$responce = array("sts" => '00', "desc" => 'Data Processed :' . $suksescnt );
	echo json_encode($responce);
	
  
  
  }
  function updatepartnumber(){
	$user = $this->session->userdata('DX_username');	
	$id_vgi_dtl = (isset($_POST['id_vgi_dtl']) && !empty($_POST['id_vgi_dtl']))?$_POST['id_vgi_dtl']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
	$part_number = (isset($_POST['part_number']) && !empty($_POST['part_number']))?$_POST['part_number']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
	$part_number1 = trim($part_number);
	$qry = $this->db->query("
			BEGIN TRY
				
				
				update [dbo].[tbl_vgi_dtl] set part_number='$part_number1' where id_vgi_dtl = $id_vgi_dtl
				select '00' as status, 'Data Part Updated' as description
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			
			
	");
	$rs = $qry->result_array();
	$r = $qry->result();		
	//var_dump($r);die();
	foreach ($rs as $row){
		$responce = array("sts" => $row['status'], "desc" => $row['description']);
	}  
	echo json_encode($responce);
	
  
  }
  
	function st_deletedata(){
		$id_vgi_hdr = (isset($_POST['id_vgi_hdr']) && !empty($_POST['id_vgi_hdr']))?$_POST['id_vgi_hdr']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');	
		$qry = $this->db->query("
			BEGIN TRY
				
				delete from [dbo].[tbl_vgi_dtl] where id_vgi_hdr = $id_vgi_hdr
				delete from [dbo].[tbl_vgi_hdr] where id_vgi_hdr = $id_vgi_hdr
				select '00' as status, 'Data Canceled!!!!... Silahkan kalo mau diupload ulang' as description
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			
			
		");
		$rs = $qry->result_array();
		$r = $qry->result();		
		//var_dump($r);die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);

	} 
	
	function st_process_delete_selected(){
		$id_data_out_hdr = (isset($_POST['id_data_out_hdr']) && !empty($_POST['id_data_out_hdr']))?$_POST['id_data_out_hdr']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');	
		$id_terkait = (isset($_POST['id_terkait']) && !empty($_POST['id_terkait']))?$_POST['id_terkait']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');	
		$doc_out_to_id = (isset($_POST['doc_out_to_id']) && !empty($_POST['doc_out_to_id']))?$_POST['doc_out_to_id']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');	
		$qry = $this->db->query("
			BEGIN TRY
				declare @id_data_out_hdr bigint
				if (select count(*) from  tbl_data_out_hdr where id_data_out_hdr=$id_data_out_hdr and doc_out_to_id=$doc_out_to_id and id_terkait=$id_terkait)=0
				begin
					select '09' as status, 'Data tidak ditemukan' as description
					return
				end
				set @id_data_out_hdr = (select id_data_out_hdr from  tbl_data_out_hdr where id_data_out_hdr=$id_data_out_hdr and doc_out_to_id=$doc_out_to_id and id_terkait=$id_terkait)
				
				delete from [dbo].[tbl_data_out_hdr_h_status] where id_data_out_hdr = @id_data_out_hdr
				delete from [dbo].[tbl_data_out_dtl] where id_data_out_hdr = @id_data_out_hdr
				delete from [dbo].[tbl_data_out_hdr] where id_data_out_hdr = @id_data_out_hdr
				select '00' as status, 'Data Canceled!!!!... Silahkan kalo mau diupload ulang' as description
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			
			
		");
		$rs = $qry->result_array();
		$r = $qry->result();		
		//var_dump($r);die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
		
	
	}
	
	function view_stocktransfer_search(){
	
		$this->load->view('inventory/view_stocktransfer_search');
	
	}
	
	function downloadcsvst_inv(){
		ini_set('memory_limit', '1024M');
		$tglawal = (isset($_GET['tglawal']) && !empty($_GET['tglawal']))?$_GET['tglawal']:die('{"sts":"ERROR","desc":" idhdrbcst Param Tidak Ditemukan"}');
		$tglakhir = (isset($_GET['tglakhir']) && !empty($_GET['tglakhir']))?$_GET['tglakhir']:die('{"sts":"ERROR","desc":" idhdrbcst Param Tidak Ditemukan"}');
		/*
		$qry = $this->db->query("
			SELECT        tbl_data_out_hdr.doc_out_no, CONVERT(varchar, tbl_data_out_hdr.doc_out_date, 103) AS doc_out_date, tbl_data_out_dtl.part_name_customs, tbl_data_out_dtl.part_number, tbl_data_out_dtl.qty, 
                         tbl_data_out_dtl.qty_supply, tbl_data_out_dtl.type_items, tbl_data_out_dtl.unit_out, tbl_data_out_dtl.assy_code, tbl_kode_barang.kode_barang
			FROM            tbl_data_out_hdr INNER JOIN
                         tbl_data_out_dtl ON tbl_data_out_hdr.id_data_out_hdr = tbl_data_out_dtl.id_data_out_hdr INNER JOIN
                         tbl_kode_barang ON tbl_data_out_dtl.part_name_customs = tbl_kode_barang.nama_barang
			where doc_out_to_id = 1 and type_items = 'RM' and doc_out_date between '$tglawal' and '$tglakhir'
		 ");
		 */
		 $qry = $this->db->query("
			SELECT        tbl_data_out_hdr.doc_out_no, CONVERT(varchar(10), tbl_data_out_hdr.doc_out_date, 103) AS doc_out_date, tbl_data_out_dtl.part_name_customs, tbl_data_out_dtl.part_number, tbl_data_out_dtl.qty, 
                         tbl_data_out_dtl.qty_supply, tbl_data_out_dtl.type_items, tbl_data_out_dtl.unit_out, tbl_data_out_dtl.assy_code, tbl_kode_barang.kode_barang, tbl_kode_barang.satuan
			FROM            tbl_data_out_hdr INNER JOIN
                         tbl_data_out_dtl ON tbl_data_out_hdr.id_data_out_hdr = tbl_data_out_dtl.id_data_out_hdr INNER JOIN
                         tbl_kode_barang ON tbl_data_out_dtl.part_name_customs = tbl_kode_barang.nama_barang
			where [type_items] = 'RM' and tbl_data_out_hdr.approve_date is not null and (doc_out_to_id <> 6)
				and  [tbl_data_out_dtl].out_date  between '$tglawal' and '$tglakhir'
		 ");
		 
		/* 
		include_once ( APPPATH."libraries/PHPExcel.php");	
		$phpExcel = new PHPExcel();
		
		$phpExcel->setActiveSheetIndex(0);
		$phpExcel->getActiveSheet()->setTitle('INVOICE OUT');
		$phpExcel->getActiveSheet()->setCellValue('A1', 'PERIODE: ' . $tglawal . ' S/D ' . $tglakhir); 
		$phpExcel->getActiveSheet()->setCellValue('A2', 'DOK OUT NO'); 
		$phpExcel->getActiveSheet()->setCellValue('B2', 'DOK OUT DATE');
		$phpExcel->getActiveSheet()->setCellValue('C2', 'KODE');
		$phpExcel->getActiveSheet()->setCellValue('D2', 'PARTNAME CUSTOMS');
		$phpExcel->getActiveSheet()->setCellValue('E2', 'PART NUMBER');
		$phpExcel->getActiveSheet()->setCellValue('F2', 'QTY');
		$phpExcel->getActiveSheet()->setCellValue('G2', 'UNIT');
		$phpExcel->getActiveSheet()->setCellValue('H2', 'KET');
		
		$counter=3;
		foreach ($qry->result() as $row){
			$phpExcel->getActiveSheet()->setCellValueExplicit("A".$counter, $row->doc_out_no, PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValue("B".$counter, $row->doc_out_date); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("C".$counter, $row->kode_barang, PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValue("D".$counter, $row->part_name_customs); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("E".$counter, $row->part_number, PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValue("F".$counter, $row->qty); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("G".$counter, $row->satuan, PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("H".$counter, $row->assy_code, PHPExcel_Cell_DataType::TYPE_STRING); 
			
			$counter = $counter+1;
		}
		
		$filename='invoice'. date('Y-m-d') .'.xls';
		header('Content-Type: application/vnd.ms-excel'); 
		header('Content-Disposition: attachment;filename="'.$filename.'"'); 
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');
		$objWriter->save('php://output');
		*/ 
		
		$rs = $qry->result_array();
		 
		$data1 = array("DOK OUT NO, DOK OUT DATE, KODE ,PARTNAME CUSTOMS, PART NUMBER, QTY, UNIT ,KET ");
		foreach ($rs as $row){
			$data1[] = $row['doc_out_no'] . "," . $row['doc_out_date'] . "," .  $row['kode_barang'] . "," . $row['part_name_customs'] . "," . $row['part_number'] . ","  .  $row['qty'] . "," . $row['satuan'] . "," . $row['assy_code'] ;
		}
		//$result = array_merge($data, $data1);
		$result = $data1;		
		header('Content-Type: application/excel');
		header('Content-Disposition: attachment; filename="sample.csv"');
		
		$fp = fopen('php://output', 'w');
		foreach ( $result as $line ) {
			$val = explode(",", $line);
			fputcsv($fp, $val);
		}
		fclose($fp);
		
		
	
	}
	
	function downloadcsvst_inv_inout(){
		ini_set('memory_limit', '1024M');
		$tglawal = (isset($_GET['tglawal']) && !empty($_GET['tglawal']))?$_GET['tglawal']:die('{"sts":"ERROR","desc":" idhdrbcst Param Tidak Ditemukan"}');
		$tglakhir = (isset($_GET['tglakhir']) && !empty($_GET['tglakhir']))?$_GET['tglakhir']:die('{"sts":"ERROR","desc":" idhdrbcst Param Tidak Ditemukan"}');
	
		 $qry = $this->db->query("
			;with
cte_out (part_number, qty_out, part_name_customs) as (
SELECT         replace(tbl_data_out_dtl.part_number,CHAR(10), ''), sum(tbl_data_out_dtl.qty), tbl_data_out_dtl.part_name_customs
			FROM            tbl_data_out_hdr INNER JOIN
                         tbl_data_out_dtl ON tbl_data_out_hdr.id_data_out_hdr = tbl_data_out_dtl.id_data_out_hdr INNER JOIN
                         tbl_kode_barang ON tbl_data_out_dtl.part_name_customs = tbl_kode_barang.nama_barang
			where [type_items] = 'RM' and tbl_data_out_hdr.approve_date is not null and (doc_out_to_id <> 6)
				and  [tbl_data_out_dtl].out_date  between '$tglawal' and '$tglakhir'
			group by tbl_data_out_dtl.part_number, part_name_customs
),			
cte_in (part_no,part_name_customs, qty_in ) as (
select replace(part_number, CHAR(10),''), part_name_customs, 
			sum(qty_in * isnull(pengali, 1))  --sum(qty_in) 
			from [tbl_data_in_dtl] 
			inner join tbl_data_in_hdr on [tbl_data_in_dtl].id_data_in_hdr=tbl_data_in_hdr.id_data_in_hdr
			inner join tbl_kode_barang on [tbl_data_in_dtl].part_name_customs = tbl_kode_barang.nama_barang
			where type_brg = 1 and tbl_data_in_hdr.doc_in_date between '$tglawal' and '$tglakhir'
			
			group by part_name_customs,part_number
)

select cte_in.part_no, cte_in.qty_in, cte_out.part_number as part_no_out, cte_out.qty_out, cte_in.part_name_customs, cte_out.part_name_customs as part_name_customs_out  from cte_in
full join cte_out on cte_in.part_no = cte_out.part_number
		 ");
		 
		include_once ( APPPATH."libraries/PHPExcel.php");	
		$phpExcel = new PHPExcel();
		
		$phpExcel->setActiveSheetIndex(0);
		$phpExcel->getActiveSheet()->setTitle('IN OUT');
		$phpExcel->getActiveSheet()->setCellValue('A1', 'PERIODE: ' . $tglawal . ' S/D ' . $tglakhir); 
		$phpExcel->getActiveSheet()->setCellValue('A2', 'PART NO IN'); 
		$phpExcel->getActiveSheet()->setCellValue('B2', 'PART NAME CUSTOMS IN');
		$phpExcel->getActiveSheet()->setCellValue('C2', 'QTY IN');
		$phpExcel->getActiveSheet()->setCellValue('D2', 'QTY OUT');
		$phpExcel->getActiveSheet()->setCellValue('E2', 'PART NAME CUSTOMS OUT');
		$phpExcel->getActiveSheet()->setCellValue('F2', 'PART NO OUT');
		
		$counter=3;
		foreach ($qry->result() as $row){
			$phpExcel->getActiveSheet()->setCellValueExplicit("A".$counter, $row->part_no, PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValue("B".$counter, $row->part_name_customs); 
			$phpExcel->getActiveSheet()->setCellValue("C".$counter, $row->qty_in); 
			$phpExcel->getActiveSheet()->setCellValue("D".$counter, $row->qty_out); 
			$phpExcel->getActiveSheet()->setCellValue("E".$counter, $row->qty_out); 
			$phpExcel->getActiveSheet()->setCellValue("F".$counter, $row->part_name_customs_out); 
			
			$counter = $counter+1;
		}
		
		$filename='invoice_inout'. date('Y-m-d') .'.xls';
		header('Content-Type: application/vnd.ms-excel'); 
		header('Content-Disposition: attachment;filename="'.$filename.'"'); 
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');
		$objWriter->save('php://output');
		 
		 /*
		 $rs = $qry->result_array();
		 
		$data1 = array("PART NO IN, PART NAME CUSTOMS IN, QTY IN ,QTY OUT, PART NAME CUSTOMS OUT, PART NO OUT");
		foreach ($rs as $row){
			$data1[] = $row['part_no'] . "," . $row['part_name_customs'] . "," .  $row['qty_in'] . "," . $row['qty_out'] . "," . $row['part_name_customs_out'] . ","  .  $row['part_no_out'] ;
		}
		//$result = array_merge($data, $data1);
		$result = $data1;		
		header('Content-Type: application/excel');
		header('Content-Disposition: attachment; filename="datainout.csv"');
		
		$fp = fopen('php://output', 'w');
		foreach ( $result as $line ) {
			$val = explode(",", $line);
			fputcsv($fp, $val);
		}
		fclose($fp);
		*/
	
	}
	
	function downloadcsvst_mutasi_perpart(){
		ini_set('memory_limit', '1024M');
		
		
		$tglawal = (isset($_GET['tglawal']) && !empty($_GET['tglawal']))?$_GET['tglawal']:die('{"sts":"ERROR","desc":" idhdrbcst Param Tidak Ditemukan"}');
		$tglakhir = (isset($_GET['tglakhir']) && !empty($_GET['tglakhir']))?$_GET['tglakhir']:die('{"sts":"ERROR","desc":" idhdrbcst Param Tidak Ditemukan"}');
	
		 $qry = $this->db->query("
			select * from [f_mutasi_bb_per_partx]('$tglawal','$tglakhir')
		 ");
		
		
		include_once ( APPPATH."libraries/PHPExcel.php");	
		$phpExcel = new PHPExcel();
		
		$phpExcel->setActiveSheetIndex(0);
		$phpExcel->getActiveSheet()->setTitle('Stock');
		$phpExcel->getActiveSheet()->setCellValue('A1', 'PERIODE: ' . $tglawal . ' S/D ' . $tglakhir); 
		$phpExcel->getActiveSheet()->setCellValue('A2', 'PART NO'); 
		$phpExcel->getActiveSheet()->setCellValue('B2', 'PART NAME CUSTOMS');
		$phpExcel->getActiveSheet()->setCellValue('C2', 'SALDO AWAL');
		$phpExcel->getActiveSheet()->setCellValue('D2', 'PEMASUKAN');
		$phpExcel->getActiveSheet()->setCellValue('E2', 'PENGELUARAN');
		$phpExcel->getActiveSheet()->setCellValue('F2', 'SALDO AKHIR');
		
		$counter=3;
		foreach ($qry->result() as $row){
			$phpExcel->getActiveSheet()->setCellValueExplicit("A".$counter, $row->kodebarang, PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValue("B".$counter, $row->namabarang); 
			$phpExcel->getActiveSheet()->setCellValue("C".$counter, $row->qty_saldoawal); 
			$phpExcel->getActiveSheet()->setCellValue("D".$counter, $row->qty_in); 
			$phpExcel->getActiveSheet()->setCellValue("E".$counter, $row->qty_out); 
			$phpExcel->getActiveSheet()->setCellValue("F".$counter, $row->saldo_akhir); 
			
			$counter = $counter+1;
		}
		
		$filename='mutasi_perpart'. date('Y-m-d') .'.xls';
		header('Content-Type: application/vnd.ms-excel'); 
		header('Content-Disposition: attachment;filename="'.$filename.'"'); 
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');
		$objWriter->save('php://output');
		
		die();
		/* 
		$data1 = array("PART NO| PART NAME CUSTOMS| SALDO AWAL| PEMASUKAN| PENGELUARAN| SALDO AKHIR");
		$kode_brg = "";
		foreach ($rs as $row){
			if(is_numeric($row['kodebarang'])){
				$kode_brg = '"' . $row['kodebarang'] . '"';
			} else {
				$kode_brg = $row['kodebarang'];
			}
			$data1[] = $kode_brg . "|" . $row['namabarang'] . "|" .  $row['qty_saldoawal'] . "|" . $row['qty_in'] . "|" . $row['qty_out'] . "|"  .  $row['saldo_akhir'] ;
		}
		//$result = array_merge($data, $data1);
		$result = $data1;		
		header('Content-Type: application/excel');
		header('Content-Disposition: attachment; filename="datainout.csv"');
		
		$fp = fopen('php://output', 'w');
		foreach ( $result as $line ) {
			$val = explode("|", $line);
			fputcsv($fp, $val);
		}
		fclose($fp);
		*/
	}
	
	function load_laporan_per_part(){
  
		$search = isset($_POST['_search'])?$_POST['_search']:false;
		$searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
		$searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
		$filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
		$limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
		$sidx = isset($_POST['sidx'])?$_POST['sidx']:'kodebarang'; // get index row - i.e. user click to sort
		$sord = isset($_POST['sord'])?$_POST['sord']:'asc'; // get the direction	
		
			
		$tglawal = (isset($_POST['tglawal']) && !empty($_POST['tglawal']))?$_POST['tglawal']:Date('Y-m-d');
		$tglakhir = (isset($_POST['tglakhir']) && !empty($_POST['tglakhir']))?$_POST['tglakhir']:Date('Y-m-d');
		
		$where = " 1=1 ";
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "kodebarang" ) {
						$searchString = $k->data;
						$where .= " and kodebarang like '%".$searchString."%'"; 
					} else if ( $k->field == "namabarang" ) {
						$searchString = $k->data;
						$where .= " and namabarang like  '%".$searchString."%'"; 
					} 
				}
			}
		}
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_outgoing->data_report_per_part_all($limit,$page,$sidx,$sord, $where, $tglawal, $tglakhir);
		$count = $this->mdl_outgoing->data_report_per_part_CountAll($where, $tglawal, $tglakhir );
		if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
		if ($page > $total_pages) $page=$total_pages;
		
		$responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =  $count;
		$i=0;
		foreach($records as $r){
						
			$responce->rows[$i]['kodebarang']=$r['kodebarang']; 
			$responce->rows[$i]['namabarang']=$r['namabarang']; 
			$responce->rows[$i]['qty_saldoawal']=$r['qty_saldoawal'];
			$responce->rows[$i]['qty_in']=$r['qty_in'];
			$responce->rows[$i]['qty_out']=$r['qty_out'];
			$responce->rows[$i]['saldo_akhir']=$r['saldo_akhir'];
			$responce->rows[$i]['unit']=$r['unit'];
			$responce->rows[$i]['stock_opname']=$r['stock_opname'];
			$responce->rows[$i]['selisih']=$r['selisih'];
			$i++;
		}
		echo json_encode($responce);
	
  
  
  }
  
  function download_stock_card(){
	 ini_set('memory_limit', '1024M');
	 $tglawal = (isset($_GET['tglawal']) && !empty($_GET['tglawal']))?$_GET['tglawal']:die('{"sts":"ERROR","desc":" idhdrbcst Param Tidak Ditemukan"}');
	 $tglakhir = (isset($_GET['tglakhir']) && !empty($_GET['tglakhir']))?$_GET['tglakhir']:die('{"sts":"ERROR","desc":" idhdrbcst Param Tidak Ditemukan"}');
	 $part_no = (isset($_GET['part_no']) && !empty($_GET['part_no']))?$_GET['part_no']:die('{"sts":"ERROR","desc":" idhdrbcst Param Tidak Ditemukan"}');
	 
	 $qry = $this->db->query("
		declare @tgstart date, @tgend date, @part_no varchar(50), @saldo_awal float 
		declare @datesaldoawal date
		declare @stsstockopname int, @dtsto date
		declare @jml_in float, @jml_out float

		set @tgstart='$tglawal'
		set @tgend = '$tglakhir'
		set @part_no = '$part_no'

		set @saldo_awal = (
		 select sum(qty) from [dbo].[tbl_stock_opname] a

				  where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
				  where b.[date_stock_opname] <= @tgend 
				  )  and a.typebahan = 'RM' and part_no = @part_no
				  group by part_no
		)

		set   @dtsto =  (select max(dt) from (
				select [date_stock_opname] as dt from [dbo].[tbl_stock_opname] a

						  where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
						  where b.[date_stock_opname] <= @tgstart and b.typebahan  = 'RM'
						  )  and a.typebahan = 'RM'
						  group by part_name_customs, [date_stock_opname]
				) as x)
		set @datesaldoawal=dateadd(day, 1, @dtsto) 


		set @jml_in = (select
					sum(isnull(tbl_data_in_dtl.qty_in, 0) * isnull(tbl_data_in_dtl.pengali, 1))  --sum(qty_in) 
				
				   FROM            tbl_data_in_dtl INNER JOIN
								 tbl_data_in_hdr ON tbl_data_in_dtl.id_data_in_hdr = tbl_data_in_hdr.id_data_in_hdr LEFT OUTER JOIN
								 tbl_data_part_hs_article ON tbl_data_in_dtl.part_number = tbl_data_part_hs_article.article_code		
				   where tbl_data_in_dtl.type_brg = 1 and tbl_data_in_hdr.doc_in_date between @datesaldoawal and  dateadd(day, -1, @tgstart)
				   and tbl_data_in_dtl.part_number = @part_no
					
		)

		set @jml_out = (select sum(qty) from
					(	select sum([qty] * isnull(pengali, 1)) as qty
					  
						
						FROM    tbl_data_out_dtl INNER JOIN
								 tbl_data_out_hdr ON tbl_data_out_dtl.id_data_out_hdr = tbl_data_out_hdr.id_data_out_hdr 
						where [type_items] = 'RM' and tbl_data_out_hdr.approve_date is not null and (doc_out_to_id <> 6)
						and  [tbl_data_out_dtl].out_date  between  @datesaldoawal and dateadd(day, -1, @tgstart) 
						and [tbl_data_out_dtl].part_number = @part_no
						group by part_number
					) x 
		)

		set @saldo_awal=(select @saldo_awal + (@jml_in- @jml_out))
		
		;with
		cte ( 

		 [doc_in_no]
			  ,[date_in_out]
			  ,[typedok]
			  ,[CAR]
			  ,[BC23No]
			  ,[BC23Tg]
			  ,[statusdok]
			  ,[part_number]
			  ,[qty_in]
			  ,qty_out
			  ,part_no
			  ,[out_date]
			  ,[id_terkait]
			  ,[doc_out_to_id]
			  ,[doc_out_no]
			  ,[doc_out_date]		
			  ,tipe 
		 )  as (
		Select
		'SALDO_AWAL'
			  ,@datesaldoawal
			  ,'SALDO_AWAL'
			  ,NULL
			  ,NULL
			  ,NULL
			  ,NULL
			  ,@part_no
			  ,@saldo_awal
			  ,NULL
			  ,@part_no
			  ,NULL
			  ,NULL
			  ,NULL
			  ,NULL
			  ,@datesaldoawal		
			  ,'SALDO_AWAL' 
		union all 
		SELECT

			  [doc_in_no]
			  ,doc_in_date
			  ,[typedok]
			  ,[CAR]
			  ,[BC23No]
			  ,[BC23Tg]
			  ,[statusdok]
			  ,[part_number]
			  ,[qty]

			  ,NULL as qty_out
			  ,NULL as part_no
			  ,NULL as [out_date]
			  ,NULL as [id_terkait]
			  ,NULL as [doc_out_to_id]
			  ,NULL as [doc_out_no]
			  ,NULL as [doc_out_date]
			  ,'IN'

		  FROM [dbo].[v_data_part_in]
		  where part_number = @part_no and [doc_in_date] between @tgstart and @tgend
		  union all
		  SELECT 
				 [doc_out_no]
			  ,[doc_out_date]
			  ,NULL
			  ,NULL
			  ,NULL
			  ,NULL
			  ,NULL
			  ,[part_number]
			  ,NULL

				,[qty]
			  ,[part_number]
			  ,[out_date]
			  ,[id_terkait]
			  ,[doc_out_to_id]
			  ,[doc_out_no]
			  ,[doc_out_date]
			  ,'OUT'
		  FROM [dbo].[v_data_part_out]
		  where part_number = @part_no and [doc_out_date] between @tgstart and @tgend
		)
		select doc_in_no, 
		convert(char(10), [date_in_out], 126) as date_in_out, 
		tipe, 
		[part_number],
		isnull(qty_in, 0) as qty_in, 
		isnull(qty_out, 0) as qty_out
		--,SUM((isnull([qty_in],0) - isnull([qty_out], 0))) OVER(ORDER BY [date_in_out] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS saldo
		 ,[typedok]
			  ,[CAR]
		 ,[BC23No]
			  ,convert(char(10),[BC23Tg], 126)  as BC23Tg
		  from cte
		order by [date_in_out]
	 ");
	 
	 
		include_once ( APPPATH."libraries/PHPExcel.php");	
		$phpExcel = new PHPExcel();

		$phpExcel->setActiveSheetIndex(0);
		$phpExcel->getActiveSheet()->setTitle('Stock Trx');
		$phpExcel->getActiveSheet()->setCellValue('A1', 'PERIODE: ' . $tglawal . ' S/D ' . $tglakhir); 
		$phpExcel->getActiveSheet()->setCellValue('A2', 'DOK IN/OUT'); 
		$phpExcel->getActiveSheet()->setCellValue('B2', 'DATE IN/OUT');
		$phpExcel->getActiveSheet()->setCellValue('C2', 'TIPE');
		$phpExcel->getActiveSheet()->setCellValue('D2', 'PART NO');
		$phpExcel->getActiveSheet()->setCellValue('E2', 'QTY IN');
		$phpExcel->getActiveSheet()->setCellValue('F2', 'QTY OUT');
		$phpExcel->getActiveSheet()->setCellValue('G2', 'SALDO');
		$phpExcel->getActiveSheet()->setCellValue('H2', 'DOK TIPE');
		$phpExcel->getActiveSheet()->setCellValue('I2', 'CAR');
		$phpExcel->getActiveSheet()->setCellValue('J2', 'BC NO');
		$phpExcel->getActiveSheet()->setCellValue('K2', 'BC TG');
		
		$counter=3;
		$qty_in = 0;
		$qty_out = 0;
        $saldo = 0;
		foreach ($qry->result() as $row){
			
			$qty_in = $row->qty_in;
			$qty_out = $row->qty_out;
			$saldo = $saldo + ($row->qty_in - $row->qty_out);
			
			$phpExcel->getActiveSheet()->setCellValueExplicit("A".$counter, $row->doc_in_no, PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValue("B".$counter, $row->date_in_out); 
			$phpExcel->getActiveSheet()->setCellValue("C".$counter, $row->tipe); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("D".$counter, $row->part_number, PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValue("E".$counter, $row->qty_in); 
			$phpExcel->getActiveSheet()->setCellValue("F".$counter, $row->qty_out); 
			$phpExcel->getActiveSheet()->setCellValue("G".$counter, $saldo); 
			//$phpExcel->getActiveSheet()->setCellValue("G".$counter, $row->saldo); 
			$phpExcel->getActiveSheet()->setCellValue("H".$counter, $row->typedok); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("I".$counter, $row->CAR, PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("J".$counter, $row->BC23No, PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValue("K".$counter, $row->BC23Tg); 
			
			$counter = $counter+1;
		}

		$filename='part_trx'. date('Y-m-d') .'.xls';
		header('Content-Type: application/vnd.ms-excel'); 
		header('Content-Disposition: attachment;filename="'.$filename.'"'); 
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');
		$objWriter->save('php://output');

		die();
  
	  
  }
  
}

