<?php 
require_once("../includes/config.php");

////// filters value/////

$daterange=$_REQUEST['daterange'];
$location=base64_decode($_REQUEST['location']);
$stock_type=base64_decode($_REQUEST['stock_type']);
$brand=base64_decode($_REQUEST['brand']);
$product_cat=base64_decode($_REQUEST['product_cat']);
$product_sub_cat=base64_decode($_REQUEST['product_sub_cat']);
$product_data=base64_decode($_REQUEST['prod_data']);
$product_code=base64_decode($_REQUEST['prod_code']);

//////////////////// get date //////////////////////

if(!empty($_REQUEST['daterange'])){
$seldate = explode(" - ",$_REQUEST['daterange']);
$fromdate = $seldate[0];
$todate = $seldate[1];
}
else{
$seldate = $today;
$fromdate = date("Y-m-01");
$todate = $today;
}
## selected Location
if(!empty($location)){
	$location_str=" and from_party='".$location."'";
}else{
	$location_str=" and from_party='".$location."'";
}
## selected Stock Type
if(!empty($stock_type)){
	$stock_type_str=" and stock_type='".$stock_type."'";
}else{
	$stock_type_str="";
}
## selected Brand
if(!empty($brand)){
	$brand_str=" and brand='".$brand."'";
}else{
	$brand_str="";
}
## selected Product Category
if(!empty($product_cat)){
	$product_cat_str=" and product_category='".$product_cat."'";
}else{
	$product_cat_str="";
}
## selected Product Sub Category
if(!empty($product_sub_cat)){
	$product_sub_cat_str=" and product_sub_category='".$product_sub_cat."'";
}else{
	$product_sub_cat_str="";
}
if(!empty($product_data)){
    $pro_data =" and partcode='".$product_data."'";
}else{
    $pro_data="";
}
if(!empty($product_code)){
    $pro_code =" and partcode='".$product_code."'";
}else{
    $pro_code="";
}
//////End filters value/////
/** Include PHPExcel */
require_once("../ExcelExportAPI/Classes/PHPExcel.php");

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Candour Software")
->setLastModifiedBy("Candour Software")
->setTitle("Office 2007 XLSX Stock Ledger Data")
->setSubject("Office 2007 XLSX Stock Ledger Data")
->setDescription("Stock Ledger Data for Office 2007 XLSX.")
->setKeywords("office 2007 openxml php")
->setCategory("Stock Ledger Data");
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'Sr. No.')
->setCellValue('B1', 'From Party')
->setCellValue('C1', 'From Party Code')
->setCellValue('D1', 'To Party')
->setCellValue('E1', 'To Party Code')
->setCellValue('F1', 'Reference No')
->setCellValue('G1', 'Reference Date')
->setCellValue('H1', 'Opening Stock')
->setCellValue('I1', 'IN')
->setCellValue('J1', 'OUT')
->setCellValue('K1', 'Closing Stock')
->setCellValue('L1', 'Brand')
->setCellValue('M1', 'Product Category')
->setCellValue('N1', 'Product Sub Category')
->setCellValue('O1', 'Part Name')
->setCellValue('P1', 'Part Code')
->setCellValue('Q1', 'Qty')
->setCellValue('R1', 'Price')
->setCellValue('S1', 'Create Date')
->setCellValue('T1', 'Create Time')
->setCellValue('U1', 'Create By')
->setCellValue('V1', 'Create By Code')
->setCellValue('W1', 'Create By(IP Address)')
->setCellValue('X1', 'Stock Type')
->setCellValue('Y1', 'Stock')
->setCellValue('Z1', 'Stock Transfer Type')
->setCellValue('AA1', 'Stock Category')
->setCellValue('AB1', 'Action Taken');
///// Add Color in header
cellColor('A1:AB1', '90EE90');

$i=2;
$count=1;
$closing = 0;
$open = 0;

$sql_loc=mysqli_query($link1,"Select * from stock_ledger where (reference_date >= '".$fromdate."' and reference_date <='".$todate."') ".$location_str." ".$stock_type_str."  ".$brand_str." ".$product_cat_str." ".$product_sub_cat_str." ".$pro_data." ".$pro_code." ");

while($row_loc = mysqli_fetch_array($sql_loc)){	

	/////// get in out qty  ///////////	 
	$in_qty = 0;
	$out_qty = 0;

	if($row_loc['stock_transfer']=='IN'){ $in_qty = getQtyFormate($row_loc['qty']);}
	if($row_loc['stock_transfer']=='OUT'){ $out_qty = getQtyFormate($row_loc['qty']);}
	
	//////////////////
	if($row_loc['stock_transfer']=='IN'){ $in = getQtyFormate($row_loc['qty']); $out=0; } else{ $in = 0;  $out = getQtyFormate($row_loc['qty']); }
	if($count==1){ $open = 0; }else{}
	$closing = $open+$in-$out;
	
	///// Get part name ////////////
	$part=explode("~",getAnyDetails($row_loc['partcode'],"partcode,part_name","partcode","partcode_master",$link1));	
	$part_name=$part[1];
	
	////////// Get request from person info /////////////
	$user_from=explode("~",getAnyDetails($row_loc['from_party'],"location_code,locationname","location_code","location_master",$link1));	
	$user_from_name=$user_from[1];

	////////// Get request to person info /////////////
	$user_to=explode("~",getAnyDetails($row_loc['from_party'],"location_code,locationname","location_code","location_master",$link1));	
	$user_to_name=$user_to[1];
		
	$objPHPExcel->setActiveSheetIndex(0)
				->setCellValue('A'.$i, $count)
				->setCellValue('B'.$i, $user_from_name)
				->setCellValue('C'.$i, $row_loc['from_party'])
				->setCellValue('D'.$i, $user_to_name)
				->setCellValue('E'.$i, $row_loc['to_party'])
				->setCellValue('F'.$i, $row_loc['reference_no'])
				->setCellValue('G'.$i, dt_format($row_loc['reference_date']))
				
				->setCellValue('H'.$i, getQtyFormate($open))
				->setCellValue('I'.$i, getQtyFormate($in_qty))
				->setCellValue('J'.$i, getQtyFormate($out_qty))
				->setCellValue('K'.$i, getQtyFormate($closing))
				
				->setCellValue('L'.$i, getAnyDetails($row_loc['brand'],"brand","brandid","brand_master",$link1))
				->setCellValue('M'.$i, getAnyDetails($row_loc['product_category'],"product_name","productid","product_master",$link1))
				->setCellValue('N'.$i, getAnyDetails($row_loc['product_sub_category'],"prod_sub_cat","psubcatid","product_sub_category",$link1))
				
				->setCellValue('O'.$i, $part_name)
				->setCellValue('P'.$i, $row_loc['partcode'])
				->setCellValue('Q'.$i, getQtyFormate($row_loc['qty']))
				->setCellValue('R'.$i, getQtyFormate($row_loc['rate'],2))
								
				->setCellValue('S'.$i, dt_format($row_loc['create_date']))
				->setCellValue('T'.$i, time_format($row_loc['create_time']))
				->setCellValue('U'.$i, getAnyDetails($row_loc['create_by'],"name","username","admin_users",$link1))
				->setCellValue('V'.$i, $row_loc['create_by'])
				->setCellValue('W'.$i, $row_loc['create_by_ip'])
				
				->setCellValue('X'.$i, $row_loc['stock_type'])
				->setCellValue('Y'.$i, $row_loc['stock_transfer'])
				->setCellValue('Z'.$i, $row_loc['type_of_transfer'])
				->setCellValue('AA'.$i, $row_loc['stock_category'])
				->setCellValue('AB'.$i, $row_loc['action_taken']);
					
				$count++;	
				$open=$closing;
				$i++;			
}
///// apply border on export sheet
$styleArray = array(
  'borders' => array(
    'allborders' => array(
      'style' => PHPExcel_Style_Border::BORDER_MEDIUM
    )
  )
);

$objPHPExcel->getActiveSheet()->getStyle('A1:AB'.($i-1))->applyFromArray($styleArray);
unset($styleArray);

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Stock Ledger Data');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client?s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="stock_ledger_list_excel.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
?>
