<?php  
// Autoload Composer untuk TCPDF  
require 'vendor/autoload.php';  
  
// Fetch current date and calculate the start and end dates for the week  
$current_date = date('Y-m-d');  
$start_of_week = date('Y-m-d', strtotime('monday this week'));  
$end_of_week = date('Y-m-d', strtotime('sunday this week'));  
$start_of_last_week = date('Y-m-d', strtotime('monday last week'));  
$end_of_last_week = date('Y-m-d', strtotime('sunday last week'));  
  
// Konfigurasi database  
$host = 'localhost';  
$user = 'wiselie_nanda';  
$password = 'Clemira123';  
$database = 'wiselie_inventory';  
  
// Koneksi ke database  
$db = new mysqli($host, $user, $password, $database);  
if ($db->connect_error) {  
    die("Connection failed: " . $db->connect_error);  
}  
  
// Fetch total outgoing and total resi counts for the week using scan_in  
$sql_outgoing = "SELECT COUNT(DISTINCT resi) as total_resi, COUNT(resi) as total_outgoing FROM awb WHERE DATE(scan_in) BETWEEN '{$start_of_week}' AND '{$end_of_week}'";  
$result_outgoing = $db->query($sql_outgoing);  
$total_resi = 0;  
$total_outgoing = 0;  
if ($row = $result_outgoing->fetch_assoc()) {  
    $total_resi = $row['total_resi'];  
    $total_outgoing = $row['total_outgoing'];  
}  
  
// Function to fetch top outgoing products  
function fetchTopOutgoingProducts($db, $start_of_week, $end_of_week, $start_of_last_week, $end_of_last_week, $categorie_id, $table) {  
    $top_outgoing = [];  
    $sql_top_outgoing = "SELECT part_number, SUM(qty) as total_qty FROM {$table} WHERE categorie_id = {$categorie_id} AND DATE(date) BETWEEN '{$start_of_week}' AND '{$end_of_week}' GROUP BY part_number ORDER BY total_qty DESC LIMIT 10";  
    $result_top_outgoing = $db->query($sql_top_outgoing);  
  
    while ($row = $result_top_outgoing->fetch_assoc()) {  
        $part_number = $row['part_number'];  
        $quantity_current = $row['total_qty'];  
  
        // Fetch product name  
        $sql_product_name = "SELECT name FROM products WHERE part_number = '{$part_number}' LIMIT 1";  
        $result_product_name = $db->query($sql_product_name);  
        $product_name = $result_product_name->fetch_assoc()['name'];  
  
        // Fetch quantity for the same product from the previous week  
        $sql_top_outgoing_last = "SELECT SUM(qty) as total_qty_last FROM {$table} WHERE categorie_id = {$categorie_id} AND part_number = '{$part_number}' AND DATE(date) BETWEEN '{$start_of_last_week}' AND '{$end_of_last_week}'";  
        $result_top_outgoing_last = $db->query($sql_top_outgoing_last);  
        $quantity_last = $result_top_outgoing_last->fetch_assoc()['total_qty_last'] ?? 0;  
  
        // Calculate percentage change  
        $percentage_change = $quantity_last > 0 ? (($quantity_current - $quantity_last) / $quantity_last) * 100 : 0;  
  
        $top_outgoing[] = [  
            'part_number' => $part_number,  
            'name' => $product_name,  
            'quantity_current' => $quantity_current,  
            'quantity_last' => $quantity_last,  
            'percentage_change' => number_format($percentage_change, 2)  
        ];  
    }  
  
    return $top_outgoing;  
}  
  
// Fetch Top 10 Outgoing Products by Quantity for Surabaya, Jakarta, Bali, and Makassar  
$top_outgoing_surabaya = fetchTopOutgoingProducts($db, $start_of_week, $end_of_week, $start_of_last_week, $end_of_last_week, 7, 'sales');  
$top_outgoing_jakarta = fetchTopOutgoingProducts($db, $start_of_week, $end_of_week, $start_of_last_week, $end_of_last_week, 7, 'sales_jakarta');  
$top_outgoing_bali = fetchTopOutgoingProducts($db, $start_of_week, $end_of_week, $start_of_last_week, $end_of_last_week, 7, 'sales_bali');  
$top_outgoing_makassar = fetchTopOutgoingProducts($db, $start_of_week, $end_of_week, $start_of_last_week, $end_of_last_week, 7, 'sales_makassar');  
  
// Create new PDF document  
$pdf = new TCPDF();  
$pdf->SetCreator(PDF_CREATOR);  
$pdf->SetAuthor('Wiselie');  
$pdf->SetTitle('Wiselie Weekly Report System');  
$pdf->SetHeaderData('', 0, 'Wiselie Weekly Report System', 'Weekly Comparison Report from ' . $start_of_week . ' to ' . $end_of_week);  
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));  
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));  
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);  
$pdf->SetMargins(10, 10, 10);  
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);  
$pdf->AddPage();  
  
// HTML content  
$html = '  
<h2>Summary</h2>  
<table border="1" cellpadding="5">  
    <tr>  
        <th>Type</th>  
        <th>Total</th>  
    </tr>  
    <tr>  
        <td>Total Resi</td>  
        <td>' . $total_resi . '</td>  
    </tr>  
    <tr>  
        <td>Total Outgoing</td>  
        <td>' . $total_outgoing . '</td>  
    </tr>  
</table>  
<br>  
<h2>Top 10 Outgoing Products by Quantity - Surabaya</h2>  
<table border="1" cellpadding="5">  
    <tr>  
        <th>Part Number</th>  
        <th>Name</th>  
        <th>Quantity (This Week)</th>  
        <th>Quantity (Last Week)</th>  
        <th>% Change</th>  
    </tr>  
';  
  
foreach ($top_outgoing_surabaya as $outgoing) {  
    $html .= '  
    <tr>  
        <td>' . $outgoing['part_number'] . '</td>  
        <td>' . $outgoing['name'] . '</td>  
        <td>' . $outgoing['quantity_current'] . '</td>  
        <td>' . $outgoing['quantity_last'] . '</td>  
        <td>' . $outgoing['percentage_change'] . '%</td>  
    </tr>  
    ';  
}  
  
$html .= '  
</table>  
<br>  
<h2>Top 10 Outgoing Products by Quantity - Jakarta</h2>  
<table border="1" cellpadding="5">  
    <tr>  
        <th>Part Number</th>  
        <th>Name</th>  
        <th>Quantity (This Week)</th>  
        <th>Quantity (Last Week)</th>  
        <th>% Change</th>  
    </tr>  
';  
  
foreach ($top_outgoing_jakarta as $outgoing) {  
    $html .= '  
    <tr>  
        <td>' . $outgoing['part_number'] . '</td>  
        <td>' . $outgoing['name'] . '</td>  
        <td>' . $outgoing['quantity_current'] . '</td>  
        <td>' . $outgoing['quantity_last'] . '</td>  
        <td>' . $outgoing['percentage_change'] . '%</td>  
    </tr>  
    ';  
}  
  
$html .= '  
</table>  
<br>  
<h2>Top 10 Outgoing Products by Quantity - Bali</h2>  
<table border="1" cellpadding="5">  
    <tr>  
        <th>Part Number</th>  
        <th>Name</th>  
        <th>Quantity (This Week)</th>  
        <th>Quantity (Last Week)</th>  
        <th>% Change</th>  
    </tr>  
';  
  
foreach ($top_outgoing_bali as $outgoing) {  
    $html .= '  
    <tr>  
        <td>' . $outgoing['part_number'] . '</td>  
        <td>' . $outgoing['name'] . '</td>  
        <td>' . $outgoing['quantity_current'] . '</td>  
        <td>' . $outgoing['quantity_last'] . '</td>  
        <td>' . $outgoing['percentage_change'] . '%</td>  
    </tr>  
    ';  
}  
  
$html .= '  
</table>  
<br>  
<h2>Top 10 Outgoing Products by Quantity - Makassar</h2>  
<table border="1" cellpadding="5">  
    <tr>  
        <th>Part Number</th>  
        <th>Name</th>  
        <th>Quantity (This Week)</th>  
        <th>Quantity (Last Week)</th>  
        <th>% Change</th>  
    </tr>  
';  
  
foreach ($top_outgoing_makassar as $outgoing) {  
    $html .= '  
    <tr>  
        <td>' . $outgoing['part_number'] . '</td>  
        <td>' . $outgoing['name'] . '</td>  
        <td>' . $outgoing['quantity_current'] . '</td>  
        <td>' . $outgoing['quantity_last'] . '</td>  
        <td>' . $outgoing['percentage_change'] . '%</td>  
    </tr>  
    ';  
}  
  
$html .= '  
</table>  
';  
  
// Output the HTML content  
$pdf->writeHTML($html, true, false, true, false, '');  
  
// Close and output PDF document  
$pdf->Output('wiselie_weekly_report_system.pdf', 'D');  
  
// Tutup koneksi database  
$db->close();  
?>  
