<?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("d F Y");
$jam = date('H:i:s');

$subject = 'SOH & Expired Date_FF';
$body_content = 'Laporan Inventory Qty + ED + Minimum Fulfillment 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('2021-06-01');
$level1_end = date_create('2021-06-07');

$level2_start = $month = date_create('2021-06-08');
$level2_end = date_create('2021-06-14');

$level3_start = $month = date_create('2021-06-15');
$level3_end = date_create('2021-06-21');

$level4_start = $month = date_create('2021-06-22');
$level4_end = date_create('2021-06-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', 'DESCRIPTION');
$activeSheet->setCellValue('E1', 'STORE NAME');
$activeSheet->setCellValue('F1', 'STATUS');
$activeSheet->setCellValue('G1', 'EXPIRED DATE');
$activeSheet->setCellValue('H1', 'UMUR SIMPAN');
$activeSheet->setCellValue('I1', 'UMUR FULFILLMENT MINIMUM');
$activeSheet->setCellValue('J1', 'QTY');

$query = $db->query("
SELECT 
	ed.sku_number,
	ed.name,
	ed.product_name,
	ed.company_name,
	CASE 
	WHEN ed.status = 'ordered' THEN 'Dipesan'
	WHEN ed.status = 'packing' THEN 'Sedang Dikemas'
	WHEN ed.status = 'packed' THEN 'Sudah Dikemas'
	WHEN ed.status = 'stored' THEN 'Tersimpan'
	WHEN ed.status = 'received' THEN 'Diterima'
	WHEN ed.status = 'disposal requested' THEN 'Pemusnahan Diminta'
	WHEN ed.status = 'disposal process' THEN 'Pemusnahan Diproses' 
	WHEN ed.status = 'investigate' THEN 'Investigasi'
	WHEN ed.status = 'damaged' THEN 'Rusak'
	WHEN ed.status = 'lost' THEN 'Hilang'
	WHEN ed.status = 'dismantle requested' THEN 'Peralihan Diminta'
	WHEN ed.status = 'dismantle process' THEN 'Peralihan Diproses'
	END as status,
	ed.date_expired,
	DATEDIFF(ed.date_expired,now())-1 as remaining_time,
	ed.minimal_fulfillment,
	SUM(ed.qty) as quantity
	
FROM
(SELECT
    products.sku_number,
    warehouses.name,
    products.name as product_name,
    products.minimal_fulfillment,
    tenants.company_name,
    ikuses.date_expired,
    ikuses.status,
	count(*) as qty
    
    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

where tenants.id IN (
'28c3455e-838e-4153-b473-ae6ad6bfb5bd',
'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',
'5db7438d-da5e-4631-aeca-7932f8979c0e',
'754de64b-8476-4c1a-b70e-b8d4e062438d')
and ikuses.status IN ('ordered', 'packing', 'packed', 'stored', 'received', 'disposal requested', 'disposal process', 'investigate', 'damaged', 'lost', 'dismantle requested', 'dismantle process')
GROUP BY warehouses.id, products.sku_number, ikuses.status, ikuses.date_expired

UNION ALL 
SELECT
    products.sku_number,
    warehouses.name,
    products.name as product_name,
    products.minimal_fulfillment,
    tenants.company_name,
    bkuses.date_expired,
    bkuses.status,
	(count(*) * products.boxContain) as qty
    
    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

where tenants.id IN (
'28c3455e-838e-4153-b473-ae6ad6bfb5bd',
'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',
'5db7438d-da5e-4631-aeca-7932f8979c0e',
'754de64b-8476-4c1a-b70e-b8d4e062438d')
and bkuses.status IN ('ordered', 'packing', 'packed', 'stored', 'received', 'disposal requested', 'disposal process', 'investigate', 'damaged', 'lost', 'dismantle requested', 'dismantle process')
GROUP BY warehouses.id, products.sku_number, bkuses.status, bkuses.date_expired) ed

GROUP BY ed.sku_number, ed.status, ed.date_expired
");




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['status']);
		$activeSheet->setCellValue('G'.$i , $row['date_expired']);
		$activeSheet->setCellValue('H'.$i , $row['remaining_time']);
		$activeSheet->setCellValue('I'.$i , $row['minimal_fulfillment']);
		$activeSheet->setCellValue('J'.$i , $row['quantity']);
		
		$number++;
		$i++;
		
    }
}

// $filename = 'inventory.csv';
$filename='SOH & Expired Date_FF- '.date('D-M-Y').'.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);

// $mail->SMTPKeepAlive = true;   
try {
// MAIL_ENABLED=true
// MAIL_DRIVER=smtp
// MAIL_FROM_ADDRESS=no-reply@freshfactory.id
// MAIL_FROM_NAME="Fresh Factory"
// MAIL_HOST=email-smtp.us-east-1.amazonaws.com
// MAIL_PORT=587
// MAIL_USERNAME=AKIATANBR6APH3YICKSQ
// MAIL_PASSWORD=BAA/uEUxjNQrS2r0Qx9IoipXm+bJIhj73G75i2tI8SAj
// MAIL_ENCRYPTION=tls
// MAIL_TO_SUPPORT_ADDRESS=support@freshfactory.id
// MAIL_TO_SUPPORT_NAME="freshfactory Support"

	$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->addAddress('zulfikar@freshfactory.id', 'Zulfikar');
	$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('data-team@sirclo.com', 'Sirclo Data Team');
	$mail->AddCC('theresia.deviana@sirclo.com', 'Theresia Deviana');
	$mail->AddCC('sandra.puspita@sirclo.com', 'Sandra Puspita');
	

  
    $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;
}