<?php
ob_start();

$filename = 'MGM GDSK Stock Report- ' . date('m-d-Y') . '.xlsx';

require_once "vendor/autoload.php";
require_once "db.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

## Params Value ###
date_default_timezone_set('Asia/Jakarta');
$tanggal = date("Y-m-d H:i:s");
$jam = date('H:i:s');

$subject = 'Laporan Inventory MGM GDSK - ';
$body_content = 'Laporan Inventory per tanggal: ';

$masa_pengiriman = 7;

function get_percentage($total, $number)
{
    if ($total > 0) {
        return round($number * ($total / 100), 2);
    } else {
        return 0;
    }
}

$default_percentage = get_percentage(100, 5) . '%';
$five_percent = get_percentage(100, 5) . '%';
$seven_percent = get_percentage(100, 7.5) . '%';
$ten_percent = get_percentage(100, 10) . '%';

$s_seven_percentage = get_percentage(100, 7.5) . '%';
$s_five_percentage = get_percentage(100, 5) . '%';
$s_two_percentage = get_percentage(100, 2.5) . '%';
$s_ten_percentage = get_percentage(100, 10) . '%';

### DATE RANGE ###
$level1_start = date_create('2021-06-01');
$level1_end = date_create('2021-06-07');
$level2_start = date_create('2021-06-08');
$level2_end = date_create('2021-06-14');
$level3_start = date_create('2021-06-15');
$level3_end = date_create('2021-06-21');
$level4_start = date_create('2021-06-22');
$level4_end = date_create('2021-06-30');

$lv1_s = date_format($level1_start, "Y-m-d");
$lv1_e = date_format($level1_end, "Y-m-d");
$lv2_s = date_format($level2_start, "Y-m-d");
$lv2_e = date_format($level2_end, "Y-m-d");
$lv3_s = date_format($level3_start, "Y-m-d");
$lv3_e = date_format($level3_end, "Y-m-d");
$lv4_s = date_format($level4_start, "Y-m-d");
$lv4_e = date_format($level4_end, "Y-m-d");

$spreadsheet = new Spreadsheet();
$Excel_writer = new Xlsx($spreadsheet);

$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet();

$spreadsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setAutoSize(true);

$activeSheet->setCellValue('A1', 'TENANT');
$activeSheet->setCellValue('B1', 'KODE SKU');
$activeSheet->setCellValue('C1', 'NAMA PRODUK');
$activeSheet->setCellValue('D1', 'ZONA');
$activeSheet->setCellValue('E1', 'TEMPAT PENYIMPANAN');
$activeSheet->setCellValue('F1', 'STATUS');
$activeSheet->setCellValue('G1', 'FIFO/FEFO');
$activeSheet->setCellValue('H1', 'UOM');
$activeSheet->setCellValue('I1', 'BKU TERSIMPAN');
$activeSheet->setCellValue('J1', 'IKU TERSIMPAN');
$activeSheet->setCellValue('K1', 'ITEM TERSIMPAN');
$activeSheet->setCellValue('L1', 'BKU EXPIRED');
$activeSheet->setCellValue('M1', 'IKU EXPIRED');
$activeSheet->setCellValue('N1', 'ITEM EXPIRED');
$activeSheet->setCellValue('O1', 'BKU NED');
$activeSheet->setCellValue('P1', 'IKU NED');
$activeSheet->setCellValue('Q1', 'ITEM NED');
$activeSheet->setCellValue('R1', 'TANGGAL EXPIRED');

$query = $db->query("SELECT
    tenants.company_name AS 'tenant',
	products.sku_number AS 'kode_sku',
	products.name AS 'nama_product',

    CASE WHEN products.storage = 'frozen' THEN 'Beku'
    WHEN products.storage = 'dry' THEN 'Kering'
    WHEN products.storage = 'refrigerated' THEN 'Sejuk'
    WHEN products.storage = 'chilled' THEN 'Dingin' END as 'zona',
    stocks.penyimpanan 'tempat_penyimpanan',

	products.price AS 'harga_terdaftar',
	h.price AS 'harga_invoice',
	products.status AS 'status',
	products.inventory AS 'metode_penyimpanan',
	case when products.FEFO = '1' then 'FEFO' else 'FIFO' end as 'fifo_fefo',
    products.boxContain AS 'uom',
    SUM(
		CASE WHEN stocks.type = 'BKU' AND stocks.status = 'stored' THEN stocks.stock
                ELSE 0 END
	) AS 'bku_tersimpan',
	SUM(
		CASE WHEN stocks.type = 'IKU' AND stocks.status = 'stored' THEN stocks.stock
                ELSE 0 END
	) AS 'iku_tersimpan',
	SUM(
		CASE WHEN stocks.type = 'IKU' AND stocks.status = 'stored'  THEN stocks.stock
                WHEN stocks.type = 'BKU' AND stocks.status = 'stored' THEN stocks.stock * products.boxContain
                ELSE 0 END
	) AS 'item_tersimpan',

    SUM(
		CASE WHEN stocks.type = 'BKU' AND stocks.status = 'received' THEN stocks.stock
                ELSE 0 END
	) AS 'bku_diterima',
	SUM(
		CASE WHEN stocks.type = 'IKU' AND stocks.status = 'received' THEN stocks.stock
                ELSE 0 END
	) AS 'iku_diterima',
	SUM(
		CASE WHEN stocks.type = 'IKU' AND stocks.status = 'received'  THEN stocks.stock
                WHEN stocks.type = 'BKU' AND stocks.status = 'received' THEN stocks.stock * products.boxContain
                ELSE 0 END
	) AS 'item_diterima',

	SUM(
		CASE WHEN stocks.type = 'BKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) <= DATE(DATE_ADD(NOW(), INTERVAL 7 HOUR)) THEN stocks.stock
		WHEN stocks.type = 'BKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) IS NULL THEN stocks.stock
                ELSE 0 END
	) AS 'bku_expired',
	SUM(
		CASE
		WHEN stocks.type = 'IKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) <= DATE(DATE_ADD(NOW(), INTERVAL 7 HOUR)) THEN stocks.stock
		WHEN stocks.type = 'IKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) IS NULL THEN stocks.stock
                ELSE 0 END
	) AS 'iku_expired',
	SUM(
		CASE WHEN stocks.type = 'IKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) <= DATE(DATE_ADD(NOW(), INTERVAL 7 HOUR)) THEN stocks.stock
             WHEN stocks.type = 'BKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) <= DATE(DATE_ADD(NOW(), INTERVAL 7 HOUR)) THEN stocks.stock * products.boxContain
             WHEN stocks.type = 'IKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) IS NULL THEN stocks.stock
             WHEN stocks.type = 'BKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) IS NULL THEN stocks.stock * products.boxContain
                ELSE 0 END
	) AS 'item_expired',

	SUM(
		CASE WHEN stocks.type = 'BKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) <= DATE(ADDDATE(NOW(), products.minimal_fulfillment-1)) THEN stocks.stock
		WHEN stocks.type = 'BKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) IS NULL THEN stocks.stock
                ELSE 0 END
	) AS 'bku_ned',
	SUM(
		CASE
		WHEN stocks.type = 'IKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) <= DATE(ADDDATE(NOW(), products.minimal_fulfillment-1)) THEN stocks.stock
		WHEN stocks.type = 'IKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) IS NULL THEN stocks.stock
                ELSE 0 END
	) AS 'iku_ned',
	SUM(
		CASE WHEN stocks.type = 'IKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) <= DATE(ADDDATE(NOW(), products.minimal_fulfillment-1)) THEN stocks.stock
             WHEN stocks.type = 'BKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) <= DATE(ADDDATE(NOW(), products.minimal_fulfillment-1)) THEN stocks.stock * products.boxContain
             WHEN stocks.type = 'IKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) IS NULL THEN stocks.stock
             WHEN stocks.type = 'BKU' AND stocks.status = 'stored' AND DATE(stocks.date_expired) IS NULL THEN stocks.stock * products.boxContain
                ELSE 0 END
	) AS 'item_ned',
	products.minimal_fulfillment as 'min_fulfill',

	IF(products.isCreator, 'product', 'T2T') AS 'tipe',
	stocks.date_expired 'tanggal_expired'
FROM products

LEFT JOIN
(SELECT p.product_id, p.price, p.created_at FROM order_products p
INNER JOIN
(SELECT op.product_id, MAX(op.created_at) as max_date FROM order_products op GROUP BY op.product_id) op
ON p.product_id = op.product_id AND p.created_at = op.max_date) h ON h.product_id = products.id

LEFT JOIN tenants ON tenants.id = products.tenant_id
LEFT JOIN
	(
        SELECT * FROM products
        WHERE isCreator = 1
	)resellers
ON resellers.sku_ff = products.sku_ff
LEFT JOIN
	(
        SELECT product_id, 'IKU' AS TYPE, date_expired, COUNT(*) AS stock, warehouses.name as penyimpanan, ikuses.status FROM ikuses
        LEFT JOIN warehouses ON warehouses.id = ikuses.warehouse_id
        WHERE ikuses.status IN ('stored','received')
--
        GROUP BY product_id, date_expired, warehouse_id
        UNION
        SELECT product_id, 'BKU' AS TYPE, date_expired, COUNT(*) AS stock, warehouses.name as penyimpanan, bkuses.status FROM bkuses
        LEFT JOIN warehouses ON warehouses.id = bkuses.warehouse_id
        WHERE bkuses.status IN ('stored','received')
--
        GROUP BY product_id, date_expired, warehouse_id
        )stocks
	ON(
		stocks.product_id = products.id
                OR
        stocks.product_id = resellers.id
	)

WHERE tenants.id = '2bc75af7-0576-4c4d-b0be-9c1be884251d'


GROUP BY products.id, stocks.penyimpanan, stocks.date_expired");

$rowCount = 2;

while ($row = $query->fetch_assoc()) {
    $activeSheet->setCellValue('A' . $rowCount, $row['tenant']);
    $activeSheet->setCellValue('B' . $rowCount, $row['kode_sku']);
    $activeSheet->setCellValue('C' . $rowCount, $row['nama_product']);
    $activeSheet->setCellValue('D' . $rowCount, $row['zona']);
    $activeSheet->setCellValue('E' . $rowCount, $row['tempat_penyimpanan']);
    $activeSheet->setCellValue('F' . $rowCount, $row['status']);
    $activeSheet->setCellValue('G' . $rowCount, $row['fifo_fefo']);
    $activeSheet->setCellValue('H' . $rowCount, $row['uom']);
    $activeSheet->setCellValue('I' . $rowCount, $row['bku_tersimpan']);
    $activeSheet->setCellValue('J' . $rowCount, $row['iku_tersimpan']);
    $activeSheet->setCellValue('K' . $rowCount, $row['item_tersimpan']);
    $activeSheet->setCellValue('L' . $rowCount, $row['bku_expired']);
    $activeSheet->setCellValue('M' . $rowCount, $row['iku_expired']);
    $activeSheet->setCellValue('N' . $rowCount, $row['item_expired']);
    $activeSheet->setCellValue('O' . $rowCount, $row['bku_ned']);
    $activeSheet->setCellValue('P' . $rowCount, $row['iku_ned']);
    $activeSheet->setCellValue('Q' . $rowCount, $row['item_ned']);
    $activeSheet->setCellValue('R' . $rowCount, $row['tanggal_expired']);

    $rowCount++;
}

$writer = new Xlsx($spreadsheet);
// $writer->save($filename);
$writer->save("files/mgm-gdsk/".$filename);

$mail = new PHPMailer(true);

try {
    $mail->isSMTP();
	$mail->Host = 'smtp.larksuite.com';
	$mail->SMTPAuth = true;
	$mail->Username = 'no-reply@freshfactory.id';
	$mail->Password = 'T8L23W3yndDVc8iJ';
	$mail->SMTPSecure = 'tls';
	$mail->Port = 587;

    	//Set who the message is to be sent from, you need permission to that email as 'send as'
	$mail->SetFrom('report@freshfactory.id', 'Fresh Factory');

	//Set an alternative reply-to address
	$mail->addReplyTo('mgm-report@freshfactory.id', 'Report Fresh Factory');


	 $mail->addAddress('zulfikar@freshfactory.id', 'Zulfikar');
	 $mail->addAddress('livia.gunawan@freshfactory.id', 'Livia Gunawan');
	 $mail->addAddress('Hasan@mgmbosco.com', 'Hasan');
	 $mail->addAddress('arham.syam@mgmbosco.com', 'Arham Syam');
	 $mail->addAddress('admops.warehouse.mbm@mgmbosco.com', 'Admobs Warehouse');
	 $mail->addAddress('annisa.sari@mgmbosco.com', 'Annisa Sari');
	 $mail->addAddress('m.novel@mgmbosco.com', 'M. Novel');
	 $mail->addAddress('asep.jaelani@mgmbosco.com', 'Asep Jaelani');
	 $mail->addAddress('warman@mgmbosco.com', 'Warman');
	 $mail->addAddress('admin.inventory@mgmbosco.com', 'Admin Inventory');

    $mail->isHTML(true);
    $mail->Subject = $subject . $tanggal;
    $mail->Body = $body_content . $tanggal;

    $mail->addAttachment("files/mgm-gdsk/".$filename);

    $mail->send();
    echo 'Report has been sent';
} catch (Exception $e) {
    echo "Message could not be sent. Mailer Error: {$mail->ErrorInfo}";
}

ob_end_flush();
?>