<?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');
$jam = date('H:i:s');

$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', 'HARI INI');
$activeSheet->setCellValue('C1', 'JAM');
$activeSheet->setCellValue('D1', 'PENYEWA');
$activeSheet->setCellValue('E1', 'GUDANG PENYIMPANAN');
$activeSheet->setCellValue('F1', 'TIPE BARANG');
$activeSheet->setCellValue('G1', 'KODE SKU');
$activeSheet->setCellValue('H1', 'NAMA PRODUK');
$activeSheet->setCellValue('I1', 'MASA PENGAWASAN (HARI)');
$activeSheet->setCellValue('J1', 'FULFILMEN IKU MASA PENGAWASAN');
$activeSheet->setCellValue('K1', 'IKU TERSEDIA SEKARANG (FF)');
$activeSheet->setCellValue('L1', 'IKU TERSEDIA SEKARANG (MASA SIMPAN <=0)');
$activeSheet->setCellValue('M1', 'FULFILMEN IKU MASA PENGAWASAN BERIKUTNYA');
$activeSheet->setCellValue('N1', 'PERLU PENGIRIMAN? (Y/N)');
$activeSheet->setCellValue('O1', 'REKOMENDASI #IKU PENGIRIMAN? (TANPA BUFFER & S. FACTOR)');
$activeSheet->setCellValue('P1', 'REKOMENDASI #IKU PENGIRIMAN? (DENGAN BUFFER & S. FACTOR)');
$activeSheet->setCellValue('Q1', 'BUFFER');
$activeSheet->setCellValue('R1', 'SEASONALITY FACTOR');
$activeSheet->setCellValue('S1', 'FF DITERIMA');
$activeSheet->setCellValue('T1', 'FF TERSIMPAN');
$activeSheet->setCellValue('U1', 'FF DIPESAN');
$activeSheet->setCellValue('V1', 'FF SEDANG DIKEMAS');
$activeSheet->setCellValue('W1', 'JUBELIO ON HAND');
$activeSheet->setCellValue('X1', 'JUBELIO AVAILABLE');

$query = $db->query("SELECT 
CURDATE() AS hari_ini,
CURRENT_TIME() AS jam,
p.id AS product_id,
w.id AS warehouse_id,
t.company_name AS penyewa,
w.name AS gudang_penyimpanan,
CASE 
	WHEN isCreator = 0  THEN 'T2T'
	ELSE 'Inventaris'
END AS tipe_barang,
p.sku_number AS kode_sku,
p.name AS nama_produk,
i.Fulfillment AS fulfilment_iku_masa_pengawasan,
i.stock as iku_tersedia_sekarang,
ISNULL(ikuses.date_expired < DATE(NOW())) AS iku_tersedia_sekarang_masa_simpan,

CEIL(i.Fulfillment+(i.Fulfillment/7)) as fulfilment_iku_masa_berikutnya,

IF(SUM(GREATEST(i.Fulfillment-i.stock,0)<=0), 'N', 'Y') AS perlu_pengiriman_yes_no,

GREATEST(i.Fulfillment-i.stock,0) as rekomendasi_iku_pengiriman_no_buffer,

CEIL(GREATEST((i.Fulfillment+(i.Fulfillment/7) + 1 + 10*1/100) - i.stock,0)) as rekomendasi_iku_pengiriman_with_buffer,
(CASE 
        WHEN i.stock = '0' THEN '$ten_percent'
        WHEN i.stock <= '0' THEN '$seven_percent'
        ELSE '$five_percent'
    END) AS buffer_factor, 
(CASE 
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY) BETWEEN '$lv1_s' AND '$lv1_e' THEN '$s_seven_percentage'
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY) BETWEEN '$lv2_s' AND '$lv2_e' THEN '$s_five_percentage'
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY) BETWEEN '$lv3_s' AND '$lv3_e' THEN '$s_two_percentage'
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY) BETWEEN '$lv4_s' AND '$lv4_e' THEN '$s_ten_percentage'
        ELSE '$five_percent'
    END) AS seasonality_factor,
jubelio_products.jubelio_product_id,
jubelio_locations.jubelio_location_id,
SUM(if(ikuses.status = 'received', 1, 0)) AS ff_received,
SUM(if(ikuses.status = 'stored', 1, 0)) AS ff_stored,
SUM(if(ikuses.status = 'ordered', 1, 0)) AS ff_ordered,
SUM(if(ikuses.status = 'packing', 1, 0)) AS ff_packing


FROM products p
INNER JOIN tenants t ON p.tenant_id = t.id
INNER JOIN ikuses ON ikuses.product_id = p.id
INNER JOIN jubelio_products ON jubelio_products.product_id = p.id
INNER JOIN (
	SELECT warehouse_id, product_id, SUM(stock) AS stock, SUM(quantity) AS Fulfillment
	FROM (
			SELECT COUNT(*) AS stock, 0 AS quantity, warehouse_id, product_id, 'Stock' AS TYPE FROM ikuses
			WHERE STATUS IN ('stored', 'received', 'reserved')
			AND DATE(date_expired) >= DATE(NOW())
			GROUP BY product_id, warehouse_id
		UNION
			SELECT 0 AS stock, SUM(op.quantity) AS quantity, o.warehouse_id, op.product_id, 'Fulfillment' AS TYPE FROM order_products op
			INNER JOIN orders o ON op.order_id = o.order_id
			WHERE DATE(o.created_at) <= DATE(NOW())
			AND  DATE(o.created_at) > DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))
			GROUP BY product_id, warehouse_id
	) datas
	GROUP BY warehouse_id, product_id
	) i ON i.product_id = p.id
INNER JOIN warehouses w ON w.id=i.warehouse_id
INNER JOIN jubelio_locations ON jubelio_locations.warehouse_id = w.id
WHERE t.id = '31e0787f-d1a0-4737-b4f7-770191407d4a'
GROUP BY p.id, gudang_penyimpanan

UNION ALL

SELECT 
CURDATE() AS hari_ini,
CURRENT_TIME() AS jam,
tp.id AS product_id,
tw.warehouse_id AS warehouse_id,
t.company_name AS penyewa,
w.name AS gudang_penyimpanan,
CASE 
	WHEN sku_ff LIKE '%TOWS%' THEN REPLACE(LEFT(sku_ff, 4), '', 'TOWS ') 
END AS tipe_barang,
tp.sku_tows AS kode_sku,
tp.name AS nama_produk,

COUNT(otp.quantity) AS fulfilment_iku_masa_pengawasan,
ts.stock AS iku_tersedia_sekarang,
ISNULL(0) AS iku_tersedia_sekarang_masa_simpan,
CEIL(COUNT(otp.quantity)+(COUNT(otp.quantity)/7)) as fulfilment_iku_masa_berikutnya,
IF(SUM(GREATEST(otp.quantity-ts.stock,0)<=0), 'N', 'Y') AS perlu_pengiriman_yes_no,
GREATEST(otp.quantity-ts.stock,0) as rekomendasi_iku_pengiriman_no_buffer,
CEIL(GREATEST((COUNT(otp.quantity)+(COUNT(otp.quantity)/7) + 1 + 12.5*1/100) - ts.stock,0)) as rekomendasi_iku_pengiriman_with_buffer,
(CASE 
        WHEN ts.stock = '0' THEN '$ten_percent'
        WHEN ts.stock <= '0' THEN '$seven_percent'
        ELSE '$five_percent'
    END) AS buffer_factor, 
(CASE 
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY)  BETWEEN '$lv1_s' AND '$lv1_e' THEN '$s_seven_percentage'
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY)  BETWEEN '$lv2_s' AND '$lv2_e' THEN '$s_five_percentage'
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY)  BETWEEN '$lv3_s' AND '$lv3_e' THEN '$s_two_percentage'
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY)  BETWEEN '$lv4_s' AND '$lv4_e' THEN '$s_ten_percentage'
        ELSE '$five_percent'
    END) AS seasonality_factor,
0 AS jb_id,
0 AS jb_id2,
0 AS ff_received,
0 AS ff_stored,
0 AS ff_ordered,
0 AS ff_packing

FROM tows_products tp 
INNER JOIN tenants t ON tp.tenant_id = t.id
INNER JOIN tows_warehouses tw ON tw.tenant_id = t.id
INNER JOIN warehouses w ON w.id = tw.warehouse_id 
INNER JOIN order_tows_products otp ON otp.product_id = tp.id
INNER JOIN tows_stocks ts ON ts.tows_product_id = tp.id

WHERE t.id = '31e0787f-d1a0-4737-b4f7-770191407d4a'


AND DATE(otp.created_at) <= DATE(NOW())
AND  DATE(otp.created_at) > DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))


GROUP BY t.id, gudang_penyimpanan, tp.id

");




// // $query2 = $dbh2->query("select * from data_bak");

$curl = curl_init();

curl_setopt_array($curl, array(
  // CURLOPT_URL => 'https://api.jubelio.com/inventory/?csv=anim%20aute%20eu%20dolor%20adipisicing',
  CURLOPT_URL => 'https://api.jubelio.com/inventory/',
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => '',
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 0,
  CURLOPT_FOLLOWLOCATION => true,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => 'GET',
  CURLOPT_HTTPHEADER => array(
    'Authorization: BearereyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IlVTRVI6MTM5LjU5LjI0My4xMjhsYXJyeS5yaWR3YW5AZnJlZXp5ZnJlc2guY29tIiwiZXhwIjoxNjI0NTYyNzExNjE0LCJpYXQiOjE2MjQ1MTk1MTF9.wSjK1-Dy8nWuLHSg3rPMOasmWksR28Vy76BhszfBHGY'
  ),
));

$response = curl_exec($curl);

curl_close($curl);
// echo $response->data;

$obj = json_decode($response);


		

			
	

if($query->num_rows > 0) {
	$number = 1; //Increment Number
	$buffer_percentage = 1; //Buffer Percentage 
	$seasonality_percentage = 1; //Seasonality Percentage 
	
	$styleArray = array(
		'font' => array(
		'bold' => true
		)
	);
	
    $i = 2;
    while($row = $query->fetch_assoc()) {
		$activeSheet->setCellValue('A'.$i , $number);
		$activeSheet->setCellValue('B'.$i , $tanggal);
		$activeSheet->setCellValue('C'.$i , $jam);
		$activeSheet->setCellValue('D'.$i , $row['penyewa']);
		$activeSheet->setCellValue('E'.$i , $row['gudang_penyimpanan']);
		$activeSheet->setCellValue('F'.$i , $row['tipe_barang']);
		$activeSheet->setCellValue('G'.$i , $row['kode_sku']);
		$activeSheet->setCellValue('H'.$i , $row['nama_produk']);
		$activeSheet->setCellValue('I'.$i , $masa_pengiriman);
		$activeSheet->setCellValue('J'.$i , $row['fulfilment_iku_masa_pengawasan']);
		$activeSheet->setCellValue('K'.$i , $row['iku_tersedia_sekarang']);
		$activeSheet->setCellValue('L'.$i , $row['iku_tersedia_sekarang_masa_simpan']);
		$activeSheet->setCellValue('M'.$i , $row['fulfilment_iku_masa_berikutnya']);
		$activeSheet->setCellValue('N'.$i , $row['perlu_pengiriman_yes_no']);
		
		if($row['fulfilment_iku_masa_pengawasan'] == 0 && $row['iku_tersedia_sekarang'] == 0){
			$activeSheet->setCellValue('O'.$i , "1");
		}else{
			$activeSheet->setCellValue('O'.$i , $row['rekomendasi_iku_pengiriman_no_buffer']);
		}
		if($row['fulfilment_iku_masa_pengawasan'] == 0 && $row['iku_tersedia_sekarang'] == 0){
			$activeSheet->setCellValue('P'.$i , "2");
		}else{
			$activeSheet->setCellValue('P'.$i , $row['rekomendasi_iku_pengiriman_with_buffer']);
		}
		
		
		$activeSheet->setCellValue('Q'.$i , $row['buffer_factor']);
		$activeSheet->setCellValue('R'.$i , $row['seasonality_factor']);

		
		
		$activeSheet->setCellValue('S'.$i , $row['ff_received']);
		$activeSheet->setCellValue('T'.$i , $row['iku_tersedia_sekarang']);
		$activeSheet->setCellValue('U'.$i , $row['ff_ordered']);
		$activeSheet->setCellValue('V'.$i , $row['ff_packing']);
			
			
		foreach($obj->data as $thing ) {
		// Loop over each region
			foreach($thing->location_stocks as $location_stocks) {
				if($row['jubelio_product_id'] == $location_stocks->item_id && $row['jubelio_location_id'] == $location_stocks->location_id){
					$activeSheet->setCellValue('W'.$i, $location_stocks->on_hand);
					$activeSheet->setCellValue('X'.$i, $location_stocks->available);
				}
			}
		}
		
		$activeSheet->setCellValue('Z'.$i , '');
		$number++;
		
		$i++;
		
    }
}

// $filename = 'inventory.csv';
$filename='inventory'.date('m-d-Y_hi').'.xlsx';
 
if (!file_exists('files')) {
    mkdir('files', 0755);
}
$Excel_writer->save('files/'.$filename);