<?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']));
$prev_date = date('Y-m-d', strtotime('-1 day', strtotime($start_date)));
//$end_date = date('Y-m-d', strtotime($_POST['end_date']));
$arr = array();
$arr_1 = array();

require '../vendor/autoload.php'; // Import the necessary classes from the library

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
set_time_limit(0);
$styleArray = array(
  
    'alignment' => array(
        'textRotation' => 90,
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
    )
);

$general_style = array(
    'borders' => array(
	'allBorders'=> array(
       
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => array('rgb' => '000000')
	),
    )
);

$general_style_1 = array(
    'borders' => array(
	'allBorders'=> array(
       
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => array('rgb' => '000000')
	),
    ),
	 'alignment' => array(
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
    )
);

$rotate_style = array(
    'borders' => array(
	'allBorders'=> array(
       
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => array('rgb' => '000000')
	),
    ),
	 'alignment' => array(
        'textRotation' => 90,
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
    )
);
$next_gen = array('Sales', 'New Stock', 'Returned (Order No.)', 'Balance', 'Unpacked', 'In Stock');
$next_gen_color = array('FF0000', 'FFFFFF', 'FFFFFF', '92D050', '0DF50A', 'EC683A');

$filename = Time().".xlsx";
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet ->getStyle('A1:G1')->applyFromArray($general_style);	
$spreadsheet
    ->getActiveSheet()
    ->getStyle('A1')
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('FFFF00');
$spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
$sheet->getColumnDimension('A')->setWidth(5);

$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('92D050');
$spreadsheet->getActiveSheet()->getStyle('C1')->applyFromArray($styleArray);
$sheet->getColumnDimension('C')->setWidth(5);
$get_orid = mysqli_query($conn,"Select * from tbl_confirmorder WHERE DATE_FORMAT(order_date_time, '%Y-%m-%d') = '$start_date' AND refid NOT LIKE '%SYP%' and company_name not like 'Siddhayoga Pharma' and orderstatus not like '90'");
while($set_orid = mysqli_fetch_array($get_orid, MYSQLI_ASSOC))
{
	$pattern1 = "/CO/i";
	$pattern2 = "/Siddhayoga/i";
	$pattern3 = "/Cash/i";
	$pattern4 = "/HC/i";
	$pattern5 = "/Energy/i";
			
//		if((!preg_match($pattern1, $set_orid['refid']) && !preg_match($pattern2, $set_orid['company_name']) &&  !preg_match($pattern3, $set_orid['varstatus'])) || (!preg_match($pattern4, $set_orid['refid']) && !preg_match($pattern5, $set_orid['company_name']))) {
//			array_push($arr, $set_orid['orderid']);
//		}
//	if((!preg_match($pattern1, $set_orid['refid']) && !preg_match($pattern2, $set_orid['company_name']) &&  !preg_match($pattern3, $set_orid['varstatus'])) || (!preg_match($pattern4, $set_orid['refid']) ) && !preg_match($pattern5, $set_orid['company_name'])) {
			array_push($arr, $set_orid['orderid']);
//		}
	
}
ksort($arr);
foreach($arr as $arrays){
	$check_sy = mysqli_query($conn, "select * from tbl_confirmorder where refid like '%HC%' and company_name='Energy Foods' and userid='7444' and orderid='$arrays'");
	$check_syp = mysqli_num_rows($check_sy);
	if($check_syp == 0){
		array_push($arr_1, $arrays);
	}
	
}
 $column = 'D';
$row = 1;
ksort($arr_1);

foreach($arr_1 as $array){
	$get_pro = mysqli_query($conn, "SELECT tc.order_date_time as mytime, tc.refid, tc.userid as myuser, tc.company_name, ts.orderdate,sp.name,sp.product_url,ts.qty+ts.freeqty as qty,tc.dif_name FROM tbl_confirmorder tc inner join tbl_order ts on tc.orid = ts.orderid inner join shop_product sp on ts.productid=sp.product_id where tc.orid = '$array'");
	$get_c = mysqli_fetch_array($get_pro, MYSQLI_ASSOC);
	 // Start from the first column
	
	  $cell = $column . $row;
        $sheet->setCellValue($cell, $get_c['refid']);
	$sheet->getStyle($cell)->applyFromArray($rotate_style);
	 $sheet->getStyle($cell)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
     $sheet->getStyle($cell)->getFill()->getStartColor()->setRGB('FFC000');
	 $sheet->getColumnDimension($column)->setWidth(5);
        $column++; // 
}
$pop = 0;
foreach($next_gen as $next_gens){
	
	$cell = $column . $row;
	 $sheet->setCellValue($cell, $next_gens);
	$sheet->getStyle($cell)->applyFromArray($rotate_style);
	 $sheet->getStyle($cell)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
	$sheet->getStyle($cell)->getFill()->getStartColor()->setRGB($next_gen_color[$pop]);
		$sheet->getColumnDimension($column)->setWidth(5);
	$column++;
	$pop = $pop+1;
}
$sheet->setCellValue('A1', $_POST['start_date']);
$sheet->setCellValue('B1', 'Product Name');
$sheet->setCellValue('C1', 'Opening');

$check_stock = mysqli_query($conn, "SELECT m1.*
FROM tbl_india_update_stock m1 LEFT JOIN tbl_india_update_stock m2
 ON (m1.product_id = m2.product_id AND m1.id < m2.id)
WHERE m2.id IS NULL;");
$row = 2;

$s_no = 0;
    while($row1 = mysqli_fetch_array($check_stock)) {
		$opening_balance='';
		$column = 'A';
		$sale_qty = 0;
		$get_refund_count = 0;
		$balance = 0;
		$pending = 0;
		$packing = 0;
		$new = 0;
		
		$select = "select new_stock, product_id,from_name from tbl_india_update_stock where stock_date='$start_date' and product_id = '".$row1['product_id']."' group by product_id";
$get_sto = mysqli_query($conn, $select);
		$check_sto = mysqli_num_rows($get_sto);
		if($check_sto > 0){
			$set_sto = mysqli_fetch_array($get_sto);
			$new_stock = $set_sto['new_stock'];
		}else{
			$new_stock = 0;
		}
		
	$get_prod = mysqli_query($conn, "SELECT * from shop_product where product_id='$row1[product_id]'");
	    $get_product = mysqli_fetch_array($get_prod, MYSQLI_ASSOC);
		//opening balance
		$open_bal = mysqli_query($conn, "select * from stock_log_entry where product_id = '".$row1['product_id']."' and DATE(created_at)='$prev_date'");
		
		$check_rows = mysqli_num_rows($open_bal);
		if($check_rows > 0){
			$sql = mysqli_query($conn, "select * from stock_log_entry where product_id = '".$row1['product_id']."' and DATE(created_at)='$prev_date' order by id desc limit 1");
			$open_balance = mysqli_fetch_array($sql, MYSQLI_ASSOC);
			$opening_balance = $open_balance['current_stock'];
		}else{
			
			$open_bal_1 = mysqli_query($conn, "select * from stock_log_entry where product_id = '".$row1['product_id']."' order by id desc limit 1");
			
			$check_rows_1 = mysqli_num_rows($open_bal_1);
			if($check_rows_1 > 0){
				
				$open_balance_1 = mysqli_fetch_array($open_bal_1, MYSQLI_ASSOC);
			$opening_balance = $open_balance_1['current_stock'];
				
			}else{
				$opening_balance = 0;
			}
		}
		$open_balance = mysqli_fetch_array($open_bal, MYSQLI_ASSOC);
			$cell = $column . $row;
			$s_no = $s_no + 1;
			$sheet->setCellValue($cell, $s_no);
		$sheet ->getStyle($cell)->applyFromArray($general_style_1);	
			$column++;
			$cell = $column . $row;
			 $sheet->setCellValue($cell, $get_product['name'].' '. $get_product['product_url']);
		$sheet ->getStyle($cell)->applyFromArray($general_style);	
		$sheet->getColumnDimension($column)->setWidth(40);
		$column++;
		$cell = $column . $row;
		$sheet->setCellValue($cell, $opening_balance);
	$spreadsheet
    ->getActiveSheet()
    ->getStyle($cell)
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('92D050');
		$sheet->getStyle($cell)->applyFromArray($general_style_1);	
		//$row++;
		foreach($arr_1 as $arrays){
			$get_sale = mysqli_query($conn, "SELECT qty+freeqty as qtys FROM tbl_order WHERE orderid ='$arrays'  AND productid = '".$row1['product_id']."'");
			$check_rows_2 = mysqli_num_rows($get_sale);
			if($check_rows_2 > 0){
				$column++;
				$cell = $column . $row;
				$get_sales = mysqli_fetch_array($get_sale, MYSQLI_ASSOC);
				$sheet->setCellValue($cell, $get_sales['qtys']);
				$sale_qty = $sale_qty + $get_sales['qtys'];
				$sheet->getStyle($cell)->applyFromArray($general_style_1);
				$spreadsheet
    ->getActiveSheet()
    ->getStyle($cell)
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('FFC000');
			
				
			}else{
				$column++;
				$cell = $column . $row;
				$sheet->setCellValue($cell, 0);
				$sheet->getStyle($cell)->applyFromArray($general_style_1);	
				$spreadsheet
    ->getActiveSheet()
    ->getStyle($cell)
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('FFFFFF');
		
			}
		}
		$column++;
		$cell = $column . $row;
		$sheet->setCellValue($cell, $sale_qty);
				$sheet->getStyle($cell)->applyFromArray($general_style_1);	
				$spreadsheet
    ->getActiveSheet()
    ->getStyle($cell)
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('FF0000');
		$column++;
		$cell = $column . $row;
		$sheet->setCellValue($cell, $new_stock);
				$sheet->getStyle($cell)->applyFromArray($general_style_1);	
				$spreadsheet
    ->getActiveSheet()
    ->getStyle($cell)
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('FFFFFF');
		//calculate refund order count
		foreach($arr_1 as $arrays){
			$get_refu = mysqli_query($conn, "SELECT ts.qty + ts.freeqty as qtys FROM tbl_confirmorder tb inner join tbl_order ts on tb.orid = ts.orderid where tb.orderstatus = 4 AND ts.productid='".$row1['product_id']."' and ts.orderid = '$arrays' and DATE(tb.refund_time) = '$start_date'");
			$check_rows_2 = mysqli_num_rows($get_refu);
			if($check_rows_2 > 0){
				$get_refund = mysqli_fetch_array($get_refu, MYSQLI_ASSOC);
				$get_refund_count = $get_refund_count + $get_refund['qtys'];
			
			}
		}
		$column++;
		$cell = $column . $row;
		$sheet->setCellValue($cell, $get_refund_count);
				$sheet->getStyle($cell)->applyFromArray($general_style_1);	
				$spreadsheet
    ->getActiveSheet()
    ->getStyle($cell)
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('FFFFFF');
		
			$balance = $opening_balance - $sale_qty + $new_stock;
		
		$column++;
		$cell = $column . $row;
		$sheet->setCellValue($cell, $balance);
				$sheet->getStyle($cell)->applyFromArray($general_style_1);	
				$spreadsheet
    ->getActiveSheet()
    ->getStyle($cell)
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('92D050');
	$get_packing = mysqli_query($conn, "SELECT sum(tb.qty + tb.freeqty) AS fqty FROM tbl_confirmorder tc INNER JOIN tbl_order tb ON tc.orderid = tb.orderid WHERE tc.orderstatus = 5 AND tc.varstatus!='Cash' and  tc.company_name='Energy Foods' and tb.productid='".$row1['product_id']."'");	
		
		$pack_rows = mysqli_num_rows($get_packing);
		if($pack_rows > 0){
			$set_packing = mysqli_fetch_array($get_packing, MYSQLI_ASSOC);
			$packing = $set_packing['fqty'];
		}else{
			$packing = 0;
		}
		
		$get_new = mysqli_query($conn, "SELECT sum(tb.qty + tb.freeqty) AS fqty FROM tbl_confirmorder tc INNER JOIN tbl_order tb ON tc.orderid = tb.orderid WHERE tc.orderstatus = 0 AND tc.varstatus!='Cash' and  tc.company_name='Energy Foods' and tb.productid='".$row1['product_id']."'");	
		
		$new_rows = mysqli_num_rows($get_new);
		if($new_rows > 0){
			$set_new = mysqli_fetch_array($get_new, MYSQLI_ASSOC);
			$new = $set_new['fqty'];
		}else{
			$new = 0;
		}
		
		$pending = $packing + $new;
		
		if(!isset($pending)){
			$pending = 0;
		}
		$column++;
		$cell = $column . $row;
		$sheet->setCellValue($cell, $pending);
				$sheet->getStyle($cell)->applyFromArray($general_style_1);	
				$spreadsheet
    ->getActiveSheet()
    ->getStyle($cell)
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('FFFFFF');
		$instock = $balance + $pending;
		$column++;
		$cell = $column . $row;
		$sheet->setCellValue($cell, $instock);
				$sheet->getStyle($cell)->applyFromArray($general_style_1);	
				$spreadsheet
    ->getActiveSheet()
    ->getStyle($cell)
    ->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()
    ->setARGB('FFFFFF');
		
		$row++;
	}

$writer = new Xlsx($spreadsheet);
$writer->save("../daily_report/$filename");
$file_path = "./daily_report/$filename";

echo $file_path;
?>