<?php
error_reporting(E_ALL & ~E_DEPRECATED & ~E_STRICT & ~E_NOTICE);
ini_set('max_execution_time', 300);
ob_start();
ini_set('display_errors', 0);
ini_set('display_startup_errors', 0);
//error_reporting(E_ALL);

require_once "vendor/autoload.php";
require_once "db.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


//Import PHPMailer classes into the global namespace
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

##Params Value###
date_default_timezone_set('Asia/Jakarta');
$tanggal = date("Y-m-d H:i:s");
$tanggal2 = date("Y-m-d");
$jam = date('H:i:s');

$subject = 'Laporan Inventory Movement Batam- ';
$body_content = 'Laporan Inventory Movement Batam tanggal: ';

$spreadsheet = new Spreadsheet();
$Excel_writer = new Xlsx($spreadsheet);

##Data Inbound Sheet 1
$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('B1', 'Tempat Penyimpanan')
    ->setCellValue('B2', 'Lokasi')
    ->setCellValue('B3', 'Tanggal Penarikan Data');
// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('DATA INBOUND');

$spreadsheet->createSheet();

## Data Outbound Sheet 2
$spreadsheet->setActiveSheetIndex(1)
    ->setCellValue('B1', 'Tempat Penyimpanan')
    ->setCellValue('B2', 'Lokasi')
    ->setCellValue('B3', 'Tanggal Penarikan Data');

// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('DATA OUTBOUND');

$spreadsheet->createSheet();
##Sisa Stock Sheet 3
$spreadsheet->setActiveSheetIndex(2)
    ->setCellValue('B1', 'Tempat Penyimpanan')
    ->setCellValue('B2', 'Lokasi')
    ->setCellValue('B3', 'Tanggal Penarikan Data');
// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('SISA STOCK');


##Sheet 1
$activeSheet = $spreadsheet->setActiveSheetIndex(0);


$activeSheet->setCellValue('A6', 'No.');
$activeSheet->setCellValue('B6', 'Kode SJ');
$activeSheet->setCellValue('C6', 'No PO');
$activeSheet->setCellValue('D6', 'Tanggal SJ Diterima');
$activeSheet->setCellValue('E6', 'Kode SKU');
$activeSheet->setCellValue('F6', 'Nama Produk');
$activeSheet->setCellValue('G6', 'Satuan');
$activeSheet->setCellValue('H6', 'QTY');
$activeSheet->setCellValue('I6', 'Status');
$activeSheet->setCellValue('J6', 'EXPIRED DATE');
$activeSheet->setCellValue('K6', 'Tgl IKU/BKU Tersimpan');
$activeSheet->setCellValue('L6', 'NO CONTAINER');
$activeSheet->setCellValue('M6', 'NO POL KENDARAAN');

##Sheet2
$activeSheet2 = $spreadsheet->setActiveSheetIndex(1);

$activeSheet2->setCellValue('A6', 'No');
$activeSheet2->setCellValue('B6', 'Kode Fulfillment');
$activeSheet2->setCellValue('C6', 'Nomor DO');
$activeSheet2->setCellValue('D6', 'Nama Penerima');
$activeSheet2->setCellValue('E6', 'Tgl Pick up');
$activeSheet2->setCellValue('F6', 'Kode SKU');
$activeSheet2->setCellValue('G6', 'Nama Produk');
$activeSheet2->setCellValue('H6', 'Satuan');
$activeSheet2->setCellValue('I6', 'Qty');
$activeSheet2->setCellValue('J6', 'Expired Date');
$activeSheet2->setCellValue('K6', 'No AWB');
$activeSheet2->setCellValue('L6', 'Metode Pengantaran');
$activeSheet2->setCellValue('M6', 'NO POL KENDARAAN');


##Sheet3
$activeSheet3 = $spreadsheet->setActiveSheetIndex(2);

$activeSheet3->setCellValue('A6', 'No');
$activeSheet3->setCellValue('B6', 'Kode SKU');
$activeSheet3->setCellValue('C6', 'Nama Produk');
$activeSheet3->setCellValue('D6', 'Zona');
$activeSheet3->setCellValue('E6', 'Satuan');
$activeSheet3->setCellValue('F6', 'QTY');
$activeSheet3->setCellValue('G6', 'Expiry Date');
$activeSheet3->setCellValue('H6', 'Status');

##SHEET1 DATA
$data1 = $db->query("
SELECT 
    r.nota kode_sj,
    r.po_number,
    r.received_at tanggal_diterima,
    p.sku_number kode_sku,
    p.iku_desc satuan,
    p.name AS product_name,
    COUNT(*) qty,
    CASE 
        WHEN i.status = 'stored' THEN 'Tersimpan'
        WHEN i.status = 'uncome' THEN 'Tidak Datang'
        WHEN i.status = 'rejected' THEN 'Ditolak'
        ELSE i.status
    END AS translated_status,
    CASE 
        WHEN i.date_expired IS NULL THEN 'N/A' 
        ELSE i.date_expired 
    END AS date_expired,
    CASE 
        WHEN i.date_stored IS NULL THEN 'N/A' 
        ELSE i.date_stored 
    END AS date_stored
FROM 
    ikuses i 
LEFT JOIN 
    products p ON p.id = i.product_id 
LEFT JOIN 
    reservations r ON r.id = i.reservation_id 
WHERE 
    i.tenant_id = 'af468cfe-b890-43a0-855d-91152d04dea4'
    AND r.received_at IS NOT NULL
    AND DATE(r.reservation_finish_date) = CURDATE()
	AND i.warehouse_id= '06eb2df8-f0c4-4543-a29a-a7bfcb1e28c1'
    
GROUP BY r.nota, r.received_at, p.sku_number, i.date_expired
");


// SHEET2 DATA
$data2 = $db->query("
    SELECT 
    o.order_id,
    o.booking_id,
    o.customer_name nama_penerima,
    CASE WHEN picked_by = 'freshfactory' THEN 'Dikirim oleh Fresh Factory' 
    WHEN picked_by = 'customer' THEN 'Diambil oleh Pelanggan di Tempat Penyimpanan' 
    ELSE 'Diatur Oleh Tenant' END metode_pengantaran,
    DATE(o.pickup_at) pickup_at,
    p.sku_number,
    p.name,
    p.iku_desc satuan,
    count(oi.ikus_id) qty,
    i.date_expired,
    ol.waybill_no

    FROM 
    order_ikus oi 

    LEFT JOIN ikuses i ON i.id = oi.ikus_id
    LEFT JOIN orders o ON o.order_id = oi.order_id
    LEFT JOIN order_logistics ol ON ol.order_id = oi.order_id

    LEFT JOIN products p ON p.id = i.product_id 

    WHERE i.tenant_id = 'af468cfe-b890-43a0-855d-91152d04dea4'
    AND o.status IN ('pickup')
    AND DATE(o.pickup_at) = CURDATE() 
	AND o.warehouse_id= '06eb2df8-f0c4-4543-a29a-a7bfcb1e28c1'

    GROUP BY oi.order_id, p.sku_number, i.date_expired
");

// SHEET3 DATA
$data3 = $db->query("
    SELECT 
        ed.sku_number,
        ed.product_name,
        ed.storage,
        ed.satuan,
        ed.qty as quantity,
        ed.date_expired,
        ed.status

    FROM
    (SELECT
        products.sku_number,
        products.name as product_name,
        products.storage,
        products.iku_desc satuan,
        ikuses.date_expired,
        count(*) as qty,
        ikuses.status 

        FROM
        ikuses

        LEFT JOIN warehouses ON ikuses.warehouse_id = warehouses.id
        LEFT JOIN products ON ikuses.product_id = products.id

        WHERE ikuses.tenant_id IN (
        'af468cfe-b890-43a0-855d-91152d04dea4')
        AND ikuses.status NOT IN ('picked up', 'uncome', 'reject')
		AND ikuses.warehouse_id= '06eb2df8-f0c4-4543-a29a-a7bfcb1e28c1'

        GROUP BY warehouses.id, products.sku_number, ikuses.status, ikuses.date_expired

        UNION ALL 
        SELECT
        products.sku_number,
        products.name as product_name,
        products.storage,
        products.bku_desc satuan,
        bkuses.date_expired,
        count(*) as qty,
        bkuses.status 

        FROM
        bkuses           

        LEFT JOIN warehouses ON bkuses.warehouse_id = warehouses.id
        LEFT JOIN products ON bkuses.product_id = products.id

        WHERE bkuses.tenant_id IN (
        'af468cfe-b890-43a0-855d-91152d04dea4')
        AND bkuses.status NOT IN ('picked up', 'uncome', 'reject')
		AND bkuses.warehouse_id= '06eb2df8-f0c4-4543-a29a-a7bfcb1e28c1'

        GROUP BY warehouses.id, products.sku_number, bkuses.status, bkuses.date_expired) ed

    GROUP BY ed.sku_number, ed.status, ed.date_expired
");

if($data1->num_rows > 0) {
    $number = 1; //Increment Number

    $styleArray = array(
        'font' => array(
            'bold' => true
        )
    );

    $i = 7;
    while($row = $data1->fetch_assoc()) {
        $activeSheet->setCellValue('C1' , 'Yoshinoya - Batam');
        $activeSheet->setCellValue('C2' , 'Batam');
        $activeSheet->setCellValue('C3' , $tanggal2);
        $activeSheet->setCellValue('A'.$i , $number);
        $activeSheet->setCellValue('B'.$i , $row['kode_sj']);
        $activeSheet->setCellValue('C'.$i , $row['po_number']);
        $activeSheet->setCellValue('D'.$i , $row['tanggal_diterima']);
        $activeSheet->setCellValue('E'.$i , $row['kode_sku']);
        $activeSheet->setCellValue('F'.$i , $row['product_name']);
        $activeSheet->setCellValue('G'.$i , $row['satuan']);
        $activeSheet->setCellValue('H'.$i , $row['qty']);
        $activeSheet->setCellValue('I'.$i , $row['translated_status']);
        $activeSheet->setCellValue('J'.$i , $row['date_expired']);
        $activeSheet->setCellValue('K'.$i , $row['date_stored']);

        $number++;
        $i++;

    }
}

$activeSheet1 = $spreadsheet->setActiveSheetIndex(1);
if($data2->num_rows > 0) {
    $number = 1; //Increment Number

    $styleArray = array(
        'font' => array(
            'bold' => true
        )
    );

    $i = 7;
    while($row = $data2->fetch_assoc()) {
        $activeSheet1->setCellValue('C1' , 'Yoshinoya - Batam');
        $activeSheet1->setCellValue('C2' , 'Batam');
        $activeSheet1->setCellValue('C3' , $tanggal2);
        $activeSheet1->setCellValue('A'.$i , $number);
        $activeSheet1->setCellValue('B'.$i , $row['order_id']);
        $activeSheet1->setCellValue('C'.$i , $row['booking_id']);
        $activeSheet1->setCellValue('D'.$i , $row['nama_penerima']);
        $activeSheet1->setCellValue('E'.$i , $row['pickup_at']);
        $activeSheet1->setCellValue('F'.$i , $row['sku_number']);
        $activeSheet1->setCellValue('G'.$i , $row['name']);
        $activeSheet1->setCellValue('H'.$i , $row['satuan']);
        $activeSheet1->setCellValue('I'.$i , $row['qty']);
        $activeSheet1->setCellValue('J'.$i , $row['date_expired']);
        $activeSheet1->setCellValue('K'.$i , $row['waybill_no']);
        $activeSheet1->setCellValue('L'.$i , $row['metode_pengantaran']);
        $activeSheet1->setCellValue('M'.$i , '');

        $number++;
        $i++;

    }
}

$activeSheet3 = $spreadsheet->setActiveSheetIndex(2);
if($data3->num_rows > 0) {
    $number = 1; //Increment Number

    $styleArray = array(
        'font' => array(
            'bold' => true
        )
    );

    $i = 7;
    while($row = $data3->fetch_assoc()) {
        $activeSheet3->setCellValue('C1' , 'Yoshinoya - Batam');
        $activeSheet3->setCellValue('C2' , 'Batam');
        $activeSheet3->setCellValue('C3' , $tanggal2);
        $activeSheet3->setCellValue('A'.$i , $number);
        $activeSheet3->setCellValue('B'.$i , $row['sku_number']);
        $activeSheet3->setCellValue('C'.$i , $row['product_name']);
        $activeSheet3->setCellValue('D'.$i , $row['storage']);
        $activeSheet3->setCellValue('E'.$i , $row['satuan']);
        $activeSheet3->setCellValue('F'.$i , $row['quantity']);
        $activeSheet3->setCellValue('G'.$i , $row['date_expired']);
        $activeSheet3->setCellValue('H'.$i , $row['status']);

        $number++;
        $i++;

    }
}

// $filename = 'inventory.csv';
$filename='item movement - '.date('m-d-Y_hms').'.xlsx';


if (!file_exists("files")) {
    mkdir("files", 0755);
}
$Excel_writer = new Xlsx($spreadsheet);
$Excel_writer->save("files/yoshinoya/".$filename);
ob_end_clean();
// send as an attachment
$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'); //you need "send to" permission on that account, if dont use yourname@mail.org

    //Set an alternative reply-to address
    $mail->addReplyTo('no-reply@freshfactory.id', 'Report Fresh Factory');

    //$mail->addAddress('zulfikar@freshfactory.id', 'Zulfikar');
    $mail->addAddress('wh.inv.jkt@multirasa.co.id', '');
    $mail->addAddress('wh.staff.jkt@multirasa.co.id', '');
    $mail->addAddress('fariz.hidayat@multirasa.co.id', '');
    $mail->addAddress('yoshinoya.gbt.btm1@multirasa.co.id', '');
    $mail->addAddress('bonie@freshfactory.id', '');
	$mail->AddCC('jusriana.sihombing@multirasa.co.id', '');
	$mail->AddCC('alexius.sugeng@multirasa.co.id', '');
	$mail->AddCC('teja.yuliandi@multirasa.co.id', '');
	$mail->AddCC('arief.daniel@multirasa.co.id', '');
	$mail->AddCC('rizal.fernando@multirasa.co.id', '');
	$mail->AddCC('sampit.simamora@multirasa.co.id', '');
	$mail->AddCC('arief.daniel@multirasa.co.id', '');
	$mail->AddCC('andrew.russell@freshfactory.id', '');
	$mail->AddCC('kesia.meliani@freshfactory.id', '');
	$mail->AddCC('asha.priliani@freshfactory.id', '');
	$mail->AddCC('yoshua.albert@freshfactory.id', '');
	$mail->AddCC('kesia.meliani@freshfactory.id', '');



    $mail->addAttachment('files/yoshinoya/'.$filename);

    $mail->isHTML(true);
    $mail->Subject = "$subject, $tanggal";
    $mail->Body    = "$body_content, $tanggal";

    $mail->send();
    echo 'Message has been sent';
} catch (Exception $e) {
    echo 'Message could not be sent. Mailer Error: '. $mail->ErrorInfo;
}