<?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 - ';
$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('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', 'NAMA PRODUK');
$activeSheet->setCellValue('D1', 'Sirclo - Tangerang');
$activeSheet->setCellValue('E1', 'Sirclo - Jakarta Selatan');
$activeSheet->setCellValue('F1', 'Sirclo - Jakarta Barat');
$activeSheet->setCellValue('G1', 'Sirclo - Depok');
$activeSheet->setCellValue('H1', 'Sirclo - Jakarta Utara');

$query = $db->query("
SELECT p.sku_number, p.name,
SUM(CASE WHEN w.id = '3ae5e101-25dc-4233-b731-9da7df06df8d'  THEN 1 ELSE 0 END) as 'Sirclo - Tangerang',
SUM(CASE WHEN w.id = '65b22bb0-ff66-4152-ad91-435a10e27c9e'  THEN 1 ELSE 0 END) as 'Sirclo - Jakarta Selatan',
SUM(CASE WHEN w.id = '80259bd6-3b5b-4798-8f5c-2a61a845871c'  THEN 1 ELSE 0 END) as 'Sirclo - Jakarta Barat',
SUM(CASE WHEN w.id = '8fb1afe3-1266-45be-813e-5fe879aacf2a'  THEN 1 ELSE 0 END) as 'Sirclo - Depok',
SUM(CASE WHEN w.id = '99bdad0a-2888-406f-8203-dfac38590921'  THEN 1 ELSE 0 END) as 'Sirclo - Tangerang Selatan',
SUM(CASE WHEN w.id = 'f2f9a282-c49a-48a8-93a2-892a9e39841e'  THEN 1 ELSE 0 END) as 'Sirclo - Jakarta Utara'
		FROM tenants t
		INNER JOIN products p ON t.id = p.tenant_id
		INNER JOIN ikuses o ON o.product_id = p.id
		INNER JOIN warehouses w ON w.id = o.warehouse_id
		WHERE t.id IN ('0bfaf0ac-d77c-44bc-a5c2-edb26dfa93de', '228b05ba-9c1a-4167-ba10-d287a6f74c03', '331c53ed-9d19-43e0-bf11-3e4d8c2a0879', 
		'5359eee4-845c-4bb6-ba84-d59965e140fe', '84e58bd0-6d04-42f3-aeeb-82609a6b5c1b', 'ae5a59d2-8733-4d38-9d72-c1d153c4c7d1','c9cfc41c-b9e5-4a02-ab0d-207a9e34c9b3','fc7b60dd-7002-4d21-af6b-8f0ec5793bdd')
		AND (DATE(o.date_received) <= NOW()
		AND o.status IN ('stored','received'))
		OR (DATE(o.date_ordered) > NOW()
		AND DATE(o.date_received) <= NOW()
		AND o.status IN ('picked up','packed','packing','ordered'))
		OR (DATE(o.date_disposed_request) > NOW()
		AND DATE(o.date_received) <= NOW()
		AND o.status IN ('disposal requested','disposal process','disposed'))
		
		GROUP BY p.name
		ORDER BY t.company_name
");




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['Sirclo - Tangerang']);
		$activeSheet->setCellValue('E'.$i , $row['Sirclo - Jakarta Selatan']);
		$activeSheet->setCellValue('F'.$i , $row['Sirclo - Jakarta Barat']);
		$activeSheet->setCellValue('G'.$i , $row['Sirclo - Depok']);
		$activeSheet->setCellValue('H'.$i , $row['Sirclo - Jakarta Utara']);
		
		$number++;
		$i++;
		
    }
}

// $filename = 'inventory.csv';
$filename='stock inventory - '.date('m-d-Y_hms').'.xlsx';


if (!file_exists("files")) {
    mkdir("files", 0755);
}
$Excel_writer = new Xlsx($spreadsheet);
$Excel_writer->save("files/".$filename);
 
// send as an attachment
$mail = new PHPMailer(true);
try {

	// //SMTP
	// $mail = new PHPMailer(true); //important
	// $mail->CharSet = 'UTF-8';  //not important
	// $mail->isSMTP(); //important
	// $mail->Host = 'smtp.office365.com'; //important
	// $mail->Port       = 587; //important
	// $mail->SMTPSecure = 'tls'; //important
	// $mail->SMTPAuth   = true; //important, your IP get banned if not using this

	// //Auth
	// $mail->Username = 'zulfikar@freshfactory.id';
	// $mail->Password = 'BL$^%&ZNBL^@*&NK';
	    $mail->isSMTP();
    $mail->Host = 'smtp.gmail.com';  //gmail SMTP server
    $mail->SMTPAuth = true;
    $mail->Username = 'dev.freshfactory@gmail.com';   //username
    $mail->Password = 'BL$^%&ZNBL^@*&NK';   //password
    $mail->SMTPSecure = 'ssl';
    $mail->Port = 465;
	//Set who the message is to be sent from, you need permission to that email as 'send as'
	$mail->SetFrom('report@freshfactory.id', 'Report Fresh Factory'); //you need "send to" permission on that account, if dont use yourname@mail.org

	//Set an alternative reply-to address
	$mail->addReplyTo('report@freshfactory.id', 'Report Fresh Factory');
    
	$mail->addAddress('purchasing1@sirclo.com', 'Purchasing Sirclo');
	//$mail->addAddress('pur@sirclo.com', '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('zulfikar@freshfactory.id', 'Zulfikar');
  
    $mail->addAttachment('files/'.$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;
}