<?php
date_default_timezone_set('Asia/Kolkata');
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
include '../../include/conf.php';
$start_date = date('Y-m-d', strtotime($_POST['start_date']));
$end_date = date('Y-m-d', strtotime($_POST['end_date']));
$last_sunday = date('d-m-Y', strtotime($start_date.'last sunday'));
$last_sunday_1 = date('Y-m-d', strtotime($start_date.'last sunday'));


require '../vendor/autoload.php'; // Import the necessary classes from the library

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
set_time_limit(0);
$filename = Time().".xlsx";
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$spreadsheet
    ->getActiveSheet()
    ->getStyle('A1')
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('FFFF00');

$spreadsheet
    ->getActiveSheet()
    ->getStyle('B1')
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('FFFF00');

$spreadsheet
    ->getActiveSheet()
    ->getStyle('C1')
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('FFFF00');

$spreadsheet
    ->getActiveSheet()
    ->getStyle('D1')
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('FFFF00');

$spreadsheet
    ->getActiveSheet()
    ->getStyle('E1')
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('FFFF00');

$spreadsheet
    ->getActiveSheet()
    ->getStyle('F1')
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('FFFF00');

$k=2;
$x=1;
$sheet->setCellValue('A1', 'S.No.');
$sheet->setCellValue('B1', 'Product Name');
$sheet->setCellValue('C1', 'Stock As On');
$sheet->setCellValue('D1', 'New Stock Added on');
$sheet->setCellValue('E1', 'Sales Between');
$sheet->setCellValue('F1', 'Balance Stock as on');
$sheet->setCellValue('C2', $last_sunday);
$sheet->setCellValue('D2', $_POST['start_date'].' - '.$_POST['end_date']);
$sheet->setCellValue('E2', $_POST['start_date'].' - '.$_POST['end_date']);
$sheet->setCellValue('F2', $_POST['end_date']);

	$get_prod = "SELECT * from shop_product_malayasia where (product_id!='173' AND product_id!='172' AND product_id!='174' AND product_id!='175' AND product_id!='176' AND product_id!='177' AND product_id!='178' ) and v_s='0'  order by name asc";
	$get_prod_det = mysqli_query($conn, $get_prod );
$x=2;
$y = 0;
while ($get_prod_data = mysqli_fetch_array($get_prod_det, MYSQLI_ASSOC)){

$get_sun = mysqli_query($conn, "SELECT * FROM `tbl_malaysia_update_stock` WHERE product_id = '".$get_prod_data['product_id']."' AND DATE(stock_date) <= '$last_sunday_1' order by id desc limit 1");

	$get_sun_det = mysqli_fetch_array($get_sun, MYSQLI_ASSOC);
	
	
	$get_new_stock = mysqli_query($conn, "SELECT sum(new_stock) as new_stock FROM `tbl_malaysia_update_stock` WHERE product_id = '".$get_prod_data['product_id']."' AND DATE(stock_date) >= '$start_date' and DATE(stock_date) <= '$end_date'");
	$get_new_stock_added = mysqli_fetch_array($get_new_stock, MYSQLI_ASSOC);
	
//	$get_sale = mysqli_query($conn, "SELECT sum(qty) as sales FROM `tbl_malaysia_sales_main_explained` where prod_id = '".$get_prod_data['product_id']."' and Date(created_at) >= '$start_date' and Date(created_at)<= '$end_date'");
	
	$get_sale = mysqli_query($conn, "select sum(qty) as sales from tbl_malaysia_sales_main sm inner join tbl_malaysia_sales_main_explained se on sm.id = se.sales_id where sm.sales_date >='$start_date' and sm.sales_date<='$end_date' and se.prod_id = '".$get_prod_data['product_id']."'");
	$get_sale_data = mysqli_fetch_array($get_sale, MYSQLI_ASSOC);
    $x=$x+1;
	$y=$y+1;
//	if(empty($get_sun_det['current_stock'])){
//		$current_stock = '0';
//		
//	}else{
//		$current_stock = $get_sun_det['current_stock'];
//	}
	if($get_sun_det['stock_date'] == $last_sunday_1){
		$current_stock = $get_sun_det['old_stock'] + $get_sun_det['new_stock'];
	}else{
		$current_stock = $get_sun_det['current_stock'] + $get_sale_data['sales'];
		
	}
	
		if(empty($get_new_stock_added['new_stock'])){
		$new_stock = '0';
		
	}else{
		$new_stock = $get_new_stock_added['new_stock'];
	}
	
			if(empty($get_sale_data['sales'])){
		$sales = '0';
		
	}else{
		$sales = $get_sale_data['sales'];
	}
	

   $sheet->setCellValue("A$x", $y);
    $sheet->setCellValue("B$x", $get_prod_data['name']);
	 $sheet->setCellValue("C$x", $current_stock);
	 $sheet->setCellValue("D$x", $new_stock);
	 $sheet->setCellValue("E$x", $sales['sales']);
	 $sheet->setCellValue("F$x", abs( $current_stock -  $get_sale_data['sales']));

}


$writer = new Xlsx($spreadsheet);
$writer->save("../stock_report/$filename");
$file_path = "./stock_report/$filename";
// $send_file=new CURLFile($file_path);
// $telegram = new Telegram('1407277292:AAEBSlqqJbMFjxfMjfclsrDMcTTlnM6wu3s');
// //$content = array('chat_id' => '17778086', 'document'=>$send_file, 'caption'=>'<u>Payment Confirmation<b></b></u>','parse_mode' => 'html');
// $content = array('chat_id' => '900663950', 'document'=>$send_file, 'caption'=>'<u><b>Pending Indent Requests / Payment Confirmation</b></u>','parse_mode' => 'html');
// $response=$telegram->sendDocument($content);
// unlink($file_path);
// sleep(2);
echo $file_path;
sleep(5);
?>