<?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).'%';

##this is on hand status# ('ordered', 'packing', 'packed', 'stored', 'received', 'disposal requested', 'disposal process', 'investigate', 'damaged', 'lost', 'dismantle requested', 'dismantle process')
##this is on hand status# ('ordered', 'packing', 'packed', 'stored', 'received', 'disposal requested', 'disposal process', 'investigate', 'damaged', 'lost', 'dismantle requested', 'dismantle process')

###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', 'BARCODE');
$activeSheet->setCellValue('F1', 'STORE NAME');
$activeSheet->setCellValue('G1', 'STATUS');
$activeSheet->setCellValue('H1', 'GRN DATE');
$activeSheet->setCellValue('I1', 'EXPIRED DATE');
$activeSheet->setCellValue('J1', 'UMUR SIMPAN');
$activeSheet->setCellValue('K1', 'UMUR FULFILLMENT MINIMUM');
$activeSheet->setCellValue('L1', 'QTY');

$query = $db->query("
SELECT 
	ed.sku_number,
	ed.name,
	ed.product_name,
	ed.barcode,
	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_stored,
	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,
    JSON_UNQUOTE(JSON_EXTRACT(products.barcode, '$[0].barcode')) as barcode,
    products.minimal_fulfillment,
    tenants.company_name,
    ikuses.date_expired,
    ikuses.date_stored,
    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',
'1c8fa35b-8580-41f8-9fda-26a90d3850e0',
'6d9c3a2e-a193-4fac-984a-c21ff2113425',
'7e8d5ca8-d800-4935-a502-4c833122936e',
'375a0ce2-2936-4669-a70a-8fc57a05b511',
'7678bc69-d2c9-4560-9fb3-b6d2cf237d47',
'8404464e-e0d3-4a76-a5bd-6169c6b05d8c')
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,
    JSON_UNQUOTE(JSON_EXTRACT(products.barcode, '$[0].barcode')) as barcode,
    products.minimal_fulfillment,
    tenants.company_name,
    bkuses.date_expired,
    bkuses.date_stored,
    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',
'1c8fa35b-8580-41f8-9fda-26a90d3850e0',
'6d9c3a2e-a193-4fac-984a-c21ff2113425',
'7e8d5ca8-d800-4935-a502-4c833122936e',
'375a0ce2-2936-4669-a70a-8fc57a05b511',
'7678bc69-d2c9-4560-9fb3-b6d2cf237d47',
'8404464e-e0d3-4a76-a5bd-6169c6b05d8c')
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['barcode']);
		$activeSheet->setCellValue('F'.$i , $row['company_name']);
		$activeSheet->setCellValue('G'.$i , $row['status']);
		$activeSheet->setCellValue('H'.$i , $row['date_stored']);
		$activeSheet->setCellValue('I'.$i , $row['date_expired']);
		$activeSheet->setCellValue('J'.$i , $row['remaining_time']);
		$activeSheet->setCellValue('K'.$i , $row['minimal_fulfillment']);
		$activeSheet->setCellValue('L'.$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 = '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('purchasing1@sirclo.com', 'Purchasing Sirclo');
	$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('christie.christella@sirclo.com', 'Christie Christella');
	$mail->AddCC('gracia@sirclo.com', 'Gracia');
	$mail->AddCC('bambang.jati@sirclo.com', 'Bambang Jati');
	$mail->AddCC('bayu.prasetyo@sirclo.com', 'Bayu Prasetyo');
	$mail->AddCC('bpuntoro@sirclo.com', 'B Puntoro');
	$mail->AddCC('benny@sirclo.com', 'Benny');
	$mail->AddCC('efi@sirclo.com', 'Efi');
	$mail->AddCC('cynthia.andhani@sirclo.com', 'Cynthia Andhani');
	$mail->AddCC('vinsensius.soedarso@sirclo.com', 'Vinsensius Soedarso');
	$mail->AddCC('sevira.virgi@sirclo.com', 'Sevira Virgi');
	$mail->AddCC('muhammad.rafi@sirclo.com', 'Muhammad Rafi');
	$mail->AddCC('jovita.irena@sirclo.com', 'Jovita Irena');
	$mail->AddCC('eleasha.christine@sirclo.com', 'Eleasha Christine');
	$mail->AddCC('mohammad.pradita@sirclo.com', 'Mohammad Pradita');
	$mail->AddCC('marcia.devana@sirclo.com', 'Marcia Devana');
	$mail->AddCC('annisa.kharisma@sirclo.com', 'Annisa Kharisma');
	$mail->AddCC('karlina.candania@sirclo.com', 'Karlina Candania');
	$mail->AddCC('nurhayati@sirclo.com', 'Nurhayati');
	$mail->AddCC('asep.sulaeman@sirclo.com', 'Asep Sulaeman');
	$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->AddCC('heri@sirclo.com', 'Heri');
	$mail->AddCC('hamdani@sirclo.com', 'Hamdani');
	$mail->AddCC('angkasa.agelion@sirclo.com', 'Angkasa Agelion');
	$mail->AddCC('lutpi.septian@sirclo.com', 'Lutpi Septian');
	$mail->AddCC('heri@sirclo.com', 'Heri');
	$mail->AddCC('weko@sirclo.com', 'Weko');
	$mail->AddCC('alfan.fazri@sirclo.com', 'Alfan Fazri');
	$mail->AddCC('satwika.uttama@sirclo.com', 'Satwika Uttama');
	$mail->AddCC('khairizzahra@sirclo.com', 'Khairizzahra');
	$mail->AddCC('maulana@sirclo.com', 'Maulana');
	$mail->AddCC('nindya.anissa@sirclo.com', 'Nindya');
	$mail->AddCC('nurmala.sari@sirclo.com', 'Nurmala');
	$mail->AddCC('intan.nur@sirclo.com', 'Intan');
	$mail->AddCC('ihsan.fadilah@sirclo.com', 'Ihsan');
	$mail->AddCC('vanessa.audrey@freshfactory.id', 'Vanessa A');
	$mail->AddCC('felicia@freshfactory.id', 'Felicia');
	$mail->AddCC('bonifasius.aristomo@sirclo.com', 'Bonifasius');
	$mail->AddCC('megawati.sartika@freshfactory.id', 'Megawati');
	$mail->AddCC('futry.windayani@freshfactory.id', 'Futry');
	



  
    $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;
}