<?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 IKU (Level) - ';
$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', 'BRAND');
$activeSheet->setCellValue('C1', 'SKU NUMBER');
$activeSheet->setCellValue('D1', 'NAMA PRODUK');
$activeSheet->setCellValue('E1', 'Sirclo - Tangerang');
$activeSheet->setCellValue('F1', 'Sirclo - Tangerang (ED)');
$activeSheet->setCellValue('G1', 'Sirclo - Jakarta Selatan');
$activeSheet->setCellValue('H1', 'Sirclo - Jakarta Selatan (ED)');
$activeSheet->setCellValue('I1', 'Sirclo - Jakarta Barat');
$activeSheet->setCellValue('J1', 'Sirclo - Jakarta Barat (ED)');
$activeSheet->setCellValue('K1', 'Sirclo - Depok');
$activeSheet->setCellValue('L1', 'Sirclo - Depok (ED)');
$activeSheet->setCellValue('M1', 'Sirclo - Tangerang Selatan');
$activeSheet->setCellValue('N1', 'Sirclo - Tangerang Selatan (ED)');
$activeSheet->setCellValue('O1', 'Sirclo - Jakarta Utara');
$activeSheet->setCellValue('P1', 'Sirclo - Jakarta Utara (ED)');
$activeSheet->setCellValue('Q1', 'Sirclo - Jakarta Selatan II');
$activeSheet->setCellValue('R1', 'Sirclo - Jakarta Selatan II (ED)');
$activeSheet->setCellValue('S1', 'Sirclo - Jakarta Selatan III');
$activeSheet->setCellValue('T1', 'Sirclo - Jakarta Selatan III (ED)');

$query = $db->query("
select sircloStock.sku_number,
        sircloStock.name,   
        sircloStock.company_name,
        sum(sircloStock.tgr_stock) tgr_stock,
        sum(sircloStock.tgr_ed) tgr_ed,
        
        sum(sircloStock.tgs_stock) tgs_stock,
        sum(sircloStock.tgs_ed) tgs_ed,
        
        sum(sircloStock.jks_stock) jks_stock,
        sum(sircloStock.jks_ed) jks_ed,
        
        sum(sircloStock.jks2_stock) jks2_stock,
        sum(sircloStock.jks2_ed) jks2_ed,
        
        sum(sircloStock.jks3_stock) jks3_stock,
        sum(sircloStock.jks3_ed) jks3_ed,
        
        sum(sircloStock.jku_stock) jku_stock,
        sum(sircloStock.jku_ed) jku_ed,
        
        sum(sircloStock.jkb_stock) jkb_stock,
        sum(sircloStock.jkb_ed) jkb_ed,
        
        sum(sircloStock.dpk_stock) dpk_stock,
        sum(sircloStock.dpk_ed) dpk_ed
        
from (
SELECT 
        products.sku_number,
        products.name,   
        tenants.company_name,
        
        SUM(case when warehouses.code = 'STA' and ikuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end) as tgr_stock,
		SUM(case when warehouses.code = 'STA' and ikuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end) as tgr_ed,		
		                
		SUM(case when warehouses.code = 'STS' and ikuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end) as tgs_stock,
		SUM(case when warehouses.code = 'STS' and ikuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end) as tgs_ed,
        
		SUM(case when warehouses.code = 'SJS' and ikuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end) as jks_stock,
		SUM(case when warehouses.code = 'SJS' and ikuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end) as jks_ed,  		
		                
		SUM(case when warehouses.code = 'SJS2' and ikuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end) as jks2_stock,
		SUM(case when warehouses.code = 'SJS2' and ikuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end) as jks2_ed,
		
		SUM(case when warehouses.code = 'SJS3' and ikuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end) as jks3_stock,
		SUM(case when warehouses.code = 'SJS3' and ikuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end) as jks3_ed,
		
		SUM(case when warehouses.code = 'SJU' and ikuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end) as jku_stock,
		SUM(case when warehouses.code = 'SJU' and ikuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end) as jku_ed,        
        
		SUM(case when warehouses.code = 'SJB' and ikuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end) as jkb_stock,
		SUM(case when warehouses.code = 'SJB' and ikuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end) as jkb_ed,        
        
		SUM(case when warehouses.code = 'SDE' and ikuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end) as dpk_stock,
		SUM(case when warehouses.code = 'SDE' and ikuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end) as dpk_ed
      
        FROM 
        ikuses
        LEFT JOIN warehouses ON ikuses.warehouse_id = warehouses.id
        LEFT JOIN products ON ikuses.product_id = products.id
        LEFT JOIN pallets ON ikuses.product_id = pallets.id
        LEFT JOIN reservations ON ikuses.reservation_id = reservations.id
        LEFT JOIN tenants ON products.tenant_id = tenants.id
        WHERE tenants.id IN (
'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',
'6d9c3a2e-a193-4fac-984a-c21ff2113425')
and ikuses.status IN ('stored')
        GROUP BY products.sku_number
        
UNION 
SELECT 
        products.sku_number,
        products.name,   
        tenants.company_name,
        
        SUM(case when warehouses.code = 'STA' and bkuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end)*products.boxContain as tgr_stock,
		SUM(case when warehouses.code = 'STA' and bkuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end)*products.boxContain as tgr_ed,		
		                
		SUM(case when warehouses.code = 'STS' and bkuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end)*products.boxContain as tgs_stock,
		SUM(case when warehouses.code = 'STS' and bkuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end)*products.boxContain as tgs_ed,
        
		SUM(case when warehouses.code = 'SJS' and bkuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end)*products.boxContain as jks_stock,
		SUM(case when warehouses.code = 'SJS' and bkuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end)*products.boxContain as jks_ed,  		
		                
		SUM(case when warehouses.code = 'SJS2' and bkuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end)*products.boxContain as jks2_stock,
		SUM(case when warehouses.code = 'SJS2' and bkuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end)*products.boxContain as jks2_ed,
		
		SUM(case when warehouses.code = 'SJS3' and bkuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end)*products.boxContain as jks3_stock,
		SUM(case when warehouses.code = 'SJS3' and bkuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end)*products.boxContain as jks3_ed,
		
		SUM(case when warehouses.code = 'SJU' and bkuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end)*products.boxContain as jku_stock,
		SUM(case when warehouses.code = 'SJU' and bkuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end)*products.boxContain as jku_ed,        
        
		SUM(case when warehouses.code = 'SJB' and bkuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end)*products.boxContain as jkb_stock,
		SUM(case when warehouses.code = 'SJB' and bkuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end)*products.boxContain as jkb_ed,        
        
		SUM(case when warehouses.code = 'SDE' and bkuses.status = 'stored' and date_expired > CURDATE() then 1 else 0 end)*products.boxContain as dpk_stock,
		SUM(case when warehouses.code = 'SDE' and bkuses.status = 'stored' and date_expired < CURDATE() then 1 else 0 end)*products.boxContain as dpk_ed
                
      
        FROM 
        
        bkuses
        LEFT JOIN warehouses ON bkuses.warehouse_id = warehouses.id
        LEFT JOIN products ON bkuses.product_id = products.id
        LEFT JOIN pallets ON bkuses.product_id = pallets.id
        LEFT JOIN reservations ON bkuses.reservation_id = reservations.id
        LEFT JOIN tenants ON products.tenant_id = tenants.id
        WHERE tenants.id IN (
'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',
'6d9c3a2e-a193-4fac-984a-c21ff2113425')
and bkuses.status IN ('stored')
        GROUP BY products.sku_number
) sircloStock
group by sircloStock.sku_number

");




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['company_name']);
		$activeSheet->setCellValue('C'.$i , $row['sku_number']);
		$activeSheet->setCellValue('D'.$i , $row['name']);
		$activeSheet->setCellValue('E'.$i , $row['tgr_stock']);
		$activeSheet->setCellValue('F'.$i , $row['tgr_ed']);
		$activeSheet->setCellValue('G'.$i , $row['jks_stock']);
		$activeSheet->setCellValue('H'.$i , $row['jks_ed']);
		$activeSheet->setCellValue('I'.$i , $row['jkb_stock']);
		$activeSheet->setCellValue('J'.$i , $row['jkb_ed']);
		$activeSheet->setCellValue('K'.$i , $row['dpk_stock']);
		$activeSheet->setCellValue('L'.$i , $row['dpk_ed']);
		$activeSheet->setCellValue('M'.$i , $row['tgs_stock']);
		$activeSheet->setCellValue('N'.$i , $row['tgs_ed']);
		$activeSheet->setCellValue('O'.$i , $row['jku_stock']);
		$activeSheet->setCellValue('P'.$i , $row['jku_ed']);
		$activeSheet->setCellValue('Q'.$i , $row['jks2_stock']);
		$activeSheet->setCellValue('R'.$i , $row['jks2_ed']);
		$activeSheet->setCellValue('S'.$i , $row['jks3_stock']);
		$activeSheet->setCellValue('T'.$i , $row['jks3_ed']);
		
		$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/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->isSMTP();
    // $mail->Host = 'smtp.gmail.com';  //gmail SMTP server
    // $mail->SMTPAuth = true;
    // $mail->Username = 'dev.freshfactory@gmail.com';   //username
    // $mail->Password = 'BL$^%&ZN';   //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('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('marcia.devana@sirclo.com', 'Marcia Devana');
	$mail->AddCC('cynthia.andhani@sirclo.com', 'Cynthia Andhani');
	$mail->AddCC('vinsensius.soedarso@sirclo.com', 'Vinsensius Soedarso');
	$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->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;
}