<?php
ob_start();
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
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");
$jam = date('H:i:s');

$subject = 'Laporan Inventory + ED - ';
$body_content = 'Laporan Stock 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).'%';
#BUFFER PERCENTAGE##
$five_percent = get_percentage(100,5).'%';
$seven_percent = get_percentage(100,7.5).'%';
$ten_percent = get_percentage(100,10).'%';

#SEASONALITY PERCENTAGE##
$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 = $month = date_create('2022-10-01');
$level1_end = date_create('2022-10-07');

$level2_start = $month = date_create('2022-10-08');
$level2_end = date_create('2022-10-14');

$level3_start = $month = date_create('2022-10-15');
$level3_end = date_create('2022-10-21');

$level4_start = $month = date_create('2022-10-22');
$level4_end = date_create('2022-10-31');

$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();

$activeSheet->setCellValue('A1', 'NO');
$activeSheet->setCellValue('B1', 'SKU NUMBER');
$activeSheet->setCellValue('C1', 'WAREHOUSE');
$activeSheet->setCellValue('D1', 'STORE NAME');
$activeSheet->setCellValue('E1', 'EXPIRED DATE');

$query = $db->query("
SELECT 
	ed.sku_number,
	ed.name,
	ed.product_name,
	ed.company_name,
	ed.date_expired
FROM
(SELECT
    products.sku_number,
    warehouses.name,
    products.name as product_name,
    tenants.company_name,
    ikuses.date_expired
    
    FROM
    ikuses
    
    
    left join warehouses ON ikuses.warehouse_id = warehouses.id
left Join products ON ikuses.product_id = products.id
left Join tenants ON tenants.id = ikuses.tenant_id
left Join order_ikus ON ikuses.id = order_ikus.ikus_id

where tenants.id IN ('0bfaf0ac-d77c-44bc-a5c2-edb26dfa93de'
'2172d44c-573e-49cf-aba5-1dfdb3babe2e',
'228b05ba-9c1a-4167-ba10-d287a6f74c03',
'229aa9a9-19d2-48e5-bfff-be0d3baaa265',
'331c53ed-9d19-43e0-bf11-3e4d8c2a0879',
'5359eee4-845c-4bb6-ba84-d59965e140fe',
'84e58bd0-6d04-42f3-aeeb-82609a6b5c1b',
'96cc0b77-aa76-4090-80cd-042f9827e17f',
'ae5a59d2-8733-4d38-9d72-c1d153c4c7d1',
'c9cfc41c-b9e5-4a02-ab0d-207a9e34c9b3',
'fc7b60dd-7002-4d21-af6b-8f0ec5793bdd',
'0bfaf0ac-d77c-44bc-a5c2-edb26dfa93de',
'2172d44c-573e-49cf-aba5-1dfdb3babe2e',
'229aa9a9-19d2-48e5-bfff-be0d3baaa265',
'8b0e1591-e031-470f-a71e-7c4e05ddb4ed',
'200e2bfb-5859-453d-8c02-e44c10d75217',
'7c5554d4-e366-449a-9f0f-9bb5ac24bc9e')
and ikuses.status IN ('stored', 'received', 'broken')

UNION ALL 
SELECT
    products.sku_number,
    warehouses.name,
    products.name as product_name,
    tenants.company_name,
    bkuses.date_expired
    
    FROM
    bkuses           
    
left join warehouses ON bkuses.warehouse_id = warehouses.id
left Join products ON bkuses.product_id = products.id
left Join tenants ON tenants.id = bkuses.tenant_id
left Join order_bkus ON bkuses.id = order_bkus.bkus_id

where tenants.id IN ('0bfaf0ac-d77c-44bc-a5c2-edb26dfa93de'
'2172d44c-573e-49cf-aba5-1dfdb3babe2e',
'228b05ba-9c1a-4167-ba10-d287a6f74c03',
'229aa9a9-19d2-48e5-bfff-be0d3baaa265',
'331c53ed-9d19-43e0-bf11-3e4d8c2a0879',
'5359eee4-845c-4bb6-ba84-d59965e140fe',
'84e58bd0-6d04-42f3-aeeb-82609a6b5c1b',
'96cc0b77-aa76-4090-80cd-042f9827e17f',
'ae5a59d2-8733-4d38-9d72-c1d153c4c7d1',
'c9cfc41c-b9e5-4a02-ab0d-207a9e34c9b3',
'fc7b60dd-7002-4d21-af6b-8f0ec5793bdd',
'8b0e1591-e031-470f-a71e-7c4e05ddb4ed',
'200e2bfb-5859-453d-8c02-e44c10d75217',
'7c5554d4-e366-449a-9f0f-9bb5ac24bc9e')
and bkuses.status IN ('stored', 'received', 'broken')) ed
WHERE DATE(ed.date_expired) <= DATE(CURRENT_DATE())
");




if($query->num_rows > 0) {
    $number = 1; //Increment Number

    $styleArray = array(
        'font' => array(
            'bold' => true
        )
    );

    $i = 2;
    while($row = $query->fetch_assoc()) {
        $activeSheet->setCellValue('A'.$i , $number);
        $activeSheet->setCellValue('B'.$i , $row['sku_number']);
        $activeSheet->setCellValue('C'.$i , $row['name']);
        $activeSheet->setCellValue('D'.$i , $row['product_name']);
        $activeSheet->setCellValue('E'.$i , $row['company_name']);
        $activeSheet->setCellValue('F'.$i , $row['date_expired']);

        $number++;
        $i++;

    }
}

// $filename = 'inventory.csv';
$filename='stock inventory - ED - '.date('m-d-Y_hms').'.xlsx';


if (!file_exists("files")) {
    mkdir("files", 0755);
}
$Excel_writer = new Xlsx($spreadsheet);
$Excel_writer->save("files/sirclo/".$filename);

// send as an attachment
$mail = new PHPMailer(true);
try {
    $mail->isSMTP();
    $mail->Host = 'email-smtp.us-east-1.amazonaws.com';  //gmail SMTP server
    $mail->SMTPAuth = true;
    $mail->Username = 'AKIATANBR6APH3YICKSQ';   //username
    $mail->Password = 'BAA/uEUxjNQrS2r0Qx9IoipXm+bJIhj73G75i2tI8SAj';   //password
    $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('purchasing1@sirclo.com', 'Purchasing Sirclo');
	$mail->AddCC('mochamad.ridwan@sirclo.com', 'M. Ridwan');
	$mail->AddCC('benny@sirclo.com', 'Benny');
	$mail->AddCC('hardiansyah@sirclo.com', 'Hardiansyah');
	$mail->AddCC('hendra.purnama@sirclo.com', 'Hendra Purnama');
	$mail->AddCC('usep@sirclo.com', 'Usep');
	$mail->AddCC('bangkit.ariyadi@sirclo.com', 'Bangkit Ariyadi');
	$mail->AddCC('harsha@sirclo.com', 'Harsha');
	$mail->AddCC('dewi.yunita@sirclo.com', 'Dewi Yunita');
	// $mail->AddCC('reza.naufal@sirclo.com', 'Reza Naufal');
	$mail->AddCC('christie.christella@sirclo.com', 'Christie Christella');
	$mail->AddCC('gracia@sirclo.com', 'Gracia');
	$mail->AddCC('dicky.franata@sirclo.com', 'Dicky Franata');
	$mail->AddCC('yonanda.habibullah@sirclo.com', 'Yonanda Habibullah');
	$mail->AddCC('bambang.jati@sirclo.com', 'Bambang Jati');
	$mail->AddCC('bayu.prasetyo@sirclo.com', 'Bayu Prasetyo');
	//$mail->AddCC('alifia.khumaira@sirclo.com', 'Alifia Khumaira');
	// $mail->AddCC('bella.putri@sirclo.com', 'Bella Putri');
	$mail->AddCC('bpuntoro@sirclo.com', 'B Puntoro');
	//$mail->AddCC('rosi.mekar@sirclo.com', 'Rosi Mekar');
	$mail->AddCC('benny@sirclo.com', 'Benny');
	$mail->AddCC('handi.indra@sirclo.com', 'Handi Indra');
	$mail->AddCC('yonanda.habibullah@sirclo.com', 'Yonanda Habibullah');
	$mail->AddCC('efi@sirclo.com', 'Efi');
	$mail->AddCC('cindy.rizki@freshfactory.id', 'Cindy Rizky');
	$mail->AddCC('cynthia.andhani@sirclo.com', 'Cynthia Andhani');
	$mail->AddCC('vinsensius.soedarso@sirclo.com', 'Vinsensius Soedarso');
	$mail->AddCC('joecelind.gabriela@sirclo.com', 'Joecelind Gabriela');
	//$mail->AddCC('yolla.rahmanda@sirclo.com', 'Yolla Rahmanda');
	$mail->AddCC('sevira.virgi@sirclo.com', 'Sevira Virgi');
	$mail->AddCC('muhammad.rafi@sirclo.com', 'Muhammad Rafi');
	$mail->AddCC('Armedianto.putra@freshfactory.id', 'Armedianto Putra');
	$mail->AddCC('samantha.tiara@freshfactory.id', 'Samantha Tiara  Widjaja');
	$mail->AddCC('jovita.irena@sirclo.com', 'Jovita Irena');
	//$mail->AddCC('tsania.ghaitsa@sirclo.com', 'Tsania Ghaitsa');
	//$mail->AddCC('tabita.loise@sirclo.com', 'Tabita Loise');
	$mail->AddCC('eleasha.christine@sirclo.com', 'Eleasha Christine');
	$mail->AddCC('mohammad.pradita@sirclo.com', 'Mohammad Pradita');
	//$mail->AddCC('devi.yolanda@sirclo.com', 'Devi Yolanda');
	$mail->AddCC('marcia.devana@sirclo.com', 'Marcia Devana');
	$mail->AddCC('annisa.kharisma@sirclo.com', 'Annisa Kharisma');
	//$mail->AddCC('dinda.jihan@sirclo.com', 'Dinda Jihan');
	$mail->AddCC('karlina.candania@sirclo.com', 'Karlina Candania');
	$mail->AddCC('nurhayati@sirclo.com', 'Nurhayati');
	//$mail->AddCC('theresha.netaneel@sirclo.com', 'Theresha Netaneel');
	$mail->AddCC('moch.firmansyah@sirclo.com', 'Moch Firmansyah');
	$mail->AddCC('asep.sulaeman@sirclo.com', 'Asep Sulaeman');
	$mail->AddCC('rahadian.sukma@sirclo.com', 'Rahadian Sukma');
	$mail->AddCC('heri@sirclo.com', 'Heri');





    // $mail->addAddress('zulfikar@freshfactory.id', 'Zulfikar');


    $mail->addAttachment('files/sirclo/'.$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;
}