Tim KKS, KKD, Freezy, Cakra, DBI dan CNI
";
$body .= "Berikut laporan Fulfillment yang masih belum selesai per tanggal $date";
# === 2. Query data ===
$sql = "
SELECT
t.company_name,
w.name AS warehouse_name,
o.order_id,
CASE
WHEN o.order_type = 'retail' THEN 'Penjualan Retail'
WHEN o.order_type = 'sales' THEN 'Penjualan Online'
WHEN o.order_type = 'transfer' THEN 'Pemindahan'
WHEN o.order_type = 'recall' THEN 'Penarikan Produk'
WHEN o.order_type = 'kurang kirim' THEN 'Kurang Kirim'
ELSE o.order_type
END AS order_type,
DATE(o.handover_at) AS handover_date,
YEAR(o.handover_at) AS handover_year,
MONTH(o.handover_at) AS handover_month,
DAY(o.handover_at) AS handover_day,
TIME(o.handover_at) AS handover_time,
DATE(o.stl_due_date) AS due_date,
YEAR(o.stl_due_date) AS due_year,
MONTH(o.stl_due_date) AS due_month,
DAY(o.stl_due_date) AS due_day,
TIME(o.stl_due_date) AS due_time,
o.payment_type,
CASE
WHEN o.status = 'unprocess' THEN 'Belum Diproses'
WHEN o.status = 'process' THEN 'Diproses'
WHEN o.status = 'packed' THEN 'Sedang Dikemas'
WHEN o.status = 'waiting' THEN 'Menunggu Diambil'
WHEN o.status = 'pickup' THEN 'Sudah Diambil'
WHEN o.status = 'cancel' THEN 'Batal'
WHEN o.status = 'cancel request' THEN 'Batal Diminta'
WHEN o.status = 'delivered' THEN 'Ekspedisi Diterima'
WHEN o.status LIKE 'PoD%' THEN CONCAT('PoD ', o.status)
ELSE o.status
END AS status_fulfillment,
IF(r.return_id IS NOT NULL, 'Y', 'N') AS ret,
r.return_id,
CASE
WHEN r.status = 'Request' THEN 'Didaftar'
WHEN r.status = 'Next' THEN 'Diminta'
WHEN r.status = 'Arrived' THEN 'Sudah Sampai'
WHEN r.status = 'Process' THEN 'Diproses'
WHEN r.status = 'Received' THEN 'Diterima'
WHEN r.status = 'Finish' THEN 'Selesai'
ELSE r.status
END AS status_return,
CASE
WHEN o.stl_status_code = 1 THEN 'Belum Dikerjakan'
WHEN o.stl_status_code = 2 THEN 'Sedang Dikerjakan'
WHEN o.stl_status_code = 3 THEN 'Selesai'
ELSE 'Status Tidak Dikenal'
END AS status_picu,
CASE
WHEN p.status_code = 3 THEN 'Selesai'
ELSE 'Belum Selesai'
END AS status_akhir,
DATEDIFF(DATE(o.stl_due_date), CURDATE()) AS remaining_days,
CASE
WHEN DATE(o.stl_due_date) < CURDATE() THEN 'Late'
ELSE 'Preventif'
END AS due_status,
GREATEST(0, DATEDIFF(CURDATE(), DATE(o.stl_due_date))) AS days_overdue
FROM orders o
LEFT JOIN warehouses w ON o.warehouse_id = w.id
LEFT JOIN tenants t ON o.tenant_id = t.id
LEFT JOIN returns r ON o.order_id = r.order_id
LEFT JOIN penyelesaians p ON p.reference_code = o.reference_code
WHERE o.stl_status_code != 3
AND o.tenant_id IN (
'6d47bb1a-6744-4d07-8d51-86591b4439b9',
'31e0787f-d1a0-4737-b4f7-770191407d4a',
'05e4d4bd-a28d-4458-ab0f-adb5ea8f0253',
'91abb24a-95a6-4dde-ac14-d28fb4811d4a',
'7b25e5bd-f47c-42ae-bf10-660e9fdbf78f',
'93e675fd-cbf5-4720-99d9-e035398d1dee',
'a278ec86-afcc-4ab4-ad75-51b9a4314bc2',
'14cc500a-b1ef-49d7-b509-5b6973b06544'
)
ORDER BY
due_status DESC,
remaining_days ASC;
";
$query = $db->query($sql);
if (!$query) {
die("SQL Query Failed: " . $db->error . "\nSQL: " . $sql);
}
# === 3. Prepare Excel ===
$spreadsheet = new Spreadsheet();
$Excel_writer = new Xlsx($spreadsheet);
$activeSheet = $spreadsheet->getActiveSheet();
$activeSheet->setTitle('Fulfillment Gantung');
# === 4. Dynamic headers ===
$headers = ['No'];
$fields = [];
while ($fieldinfo = $query->fetch_field()) {
$headers[] = $fieldinfo->name;
$fields[] = $fieldinfo->name;
}
# Write header row
$colIndex = 'A';
foreach ($headers as $header) {
$activeSheet->setCellValue($colIndex . '1', $header);
$colIndex++;
}
# === 5. Fill data ===
if ($query->num_rows > 0) {
$rowIndex = 2;
$no = 1;
while ($row = $query->fetch_assoc()) {
$activeSheet->setCellValue("A$rowIndex", $no);
$colIndex = 'B';
foreach ($fields as $field) {
$activeSheet->setCellValue($colIndex . $rowIndex, $row[$field]);
$colIndex++;
}
$rowIndex++;
$no++;
}
}
# === 6. Style header ===
$headerStyle = [
'font' => [
'bold' => true,
'color' => ['rgb' => 'FFFFFF'],
],
'fill' => [
'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
'color' => ['rgb' => '4F81BD'],
],
];
$lastCol = chr(ord('A') + count($headers) - 1);
$activeSheet->getStyle("A1:{$lastCol}1")->applyFromArray($headerStyle);
$activeSheet->freezePane('A2');
foreach (range('A', $lastCol) as $col) {
$activeSheet->getColumnDimension($col)->setAutoSize(true);
}
# === 7. Save file ===
if (!file_exists('files')) mkdir('files', 0755);
$filename = 'Data_Penyelesaian_Gantung_' . date('Y-m-d') . '.xlsx';
$Excel_writer->save("files/$filename");
# === 8. Send email ===
$mail = new PHPMailer(true);
try {
$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;
$mail->setFrom('report@freshfactory.id', 'Fresh Factory');
$mail->addReplyTo('no-reply@freshfactory.id', 'Report Fresh Factory');
$recipients = [
'brayen.constantin@kakina.id' => 'Brayen',
'yayan.fitriyana@cakra-guna.id' => 'Yayan',
'zulfikar@freshfactory.id' => 'Zul',
'larry.ridwan@freshfactory.id' => 'Larry Ridwan',
'andre.septiano@freshfactory.id' => 'Andre Septiano',
'lisa@cakra-guna.id' => 'Lisa',
'fany.purwaningsih@cakra-guna.id' => 'Fanny',
'jasmine.septividiany@freshfactory.id' => 'Jasmine',
'tri.ardianto@freshfactory.id' => 'Tri',
'doryferdinand@kakina.id' => 'Dory',
'windu.susanto@cakra-guna.id' => 'Windu Susanto',
'rishaazmi127@gmail.com' => 'Risha',
'indh.pratiwii9@gmail.com' => 'Indah Pratiwi',
'Arlin.wiandini@kakina.id' => 'Arlin',
'Vina.roikhatul@kakida.id' => 'Vina'
];
foreach ($recipients as $email => $name) {
$mail->addAddress($email, $name);
}
$mail->addAttachment("files/$filename");
$mail->isHTML(true);
$mail->Subject = "$subject $date";
$mail->Body = $body;
$mail->send();
echo "Email sent successfully with attachment: $filename";
} catch (Exception $e) {
echo 'Mailer Error: ' . $mail->ErrorInfo;
}
?>