<?php  
$page_title = 'Sales Dashboard';  
require_once('includes/load.php');  
page_require_level(3);  
  
// Initialize variables  
$errors = [];  
$start_date = date('Y-m-d'); // Default start date: Current date  
$end_date = date('Y-m-d');   // Default end date: Current date  

// Function to calculate total omzet for a location within the date range  
function get_total_omzet($location, $start_date, $end_date) {  
    global $db;  
  
    $sql = "SELECT SUM(t.total_price) AS total_omzet   
            FROM (  
                SELECT invoice, MAX(scan_status) AS latest_scan_status, MAX(total_price) AS total_price  
                FROM awb  
                WHERE scan_status IS NOT NULL  
                AND loc = '{$db->escape($location)}'  
                AND DATE(scan_status) BETWEEN '{$db->escape($start_date)}' AND '{$db->escape($end_date)}'  
                GROUP BY invoice  
                HAVING invoice IS NOT NULL  
            ) AS t";  
      
    return find_by_sql($sql);  
}  

// Function to calculate total omzet for Surabaya (NULL or empty loc) within the date range  
function get_total_omzet_surabaya($start_date, $end_date) {  
    global $db;  
  
    $sql = "SELECT SUM(t.total_price) AS total_omzet   
            FROM (  
                SELECT invoice, MAX(scan_status) AS latest_scan_status, MAX(total_price) AS total_price  
                FROM awb  
                WHERE scan_status IS NOT NULL  
                AND (loc IS NULL OR loc = '')  
                AND DATE(scan_status) BETWEEN '{$db->escape($start_date)}' AND '{$db->escape($end_date)}'  
                GROUP BY invoice  
                HAVING invoice IS NOT NULL  
            ) AS t";  
      
    return find_by_sql($sql);  
}  

// Function to calculate total omzet for a location for the previous month  
function get_total_omzet_previous_month($location) {  
    global $db;  
  
    $first_day_of_previous_month = date('Y-m-01', strtotime('first day of previous month'));  
    $last_day_of_previous_month = date('Y-m-t', strtotime('last day of previous month'));  
  
    $sql = "SELECT SUM(t.total_price) AS total_omzet   
            FROM (  
                SELECT invoice, MAX(scan_status) AS latest_scan_status, MAX(total_price) AS total_price  
                FROM awb  
                WHERE scan_status IS NOT NULL  
                AND loc = '{$db->escape($location)}'  
                AND DATE(scan_status) BETWEEN '{$db->escape($first_day_of_previous_month)}' AND '{$db->escape($last_day_of_previous_month)}'  
                GROUP BY invoice  
                HAVING invoice IS NOT NULL  
            ) AS t";  
      
    return find_by_sql($sql);  
}  

// Function to calculate total omzet for Surabaya for the previous month  
function get_total_omzet_surabaya_previous_month() {  
    global $db;  
  
    $first_day_of_previous_month = date('Y-m-01', strtotime('first day of previous month'));  
    $last_day_of_previous_month = date('Y-m-t', strtotime('last day of previous month'));  
  
    $sql = "SELECT SUM(t.total_price) AS total_omzet   
            FROM (  
                SELECT invoice, MAX(scan_status) AS latest_scan_status, MAX(total_price) AS total_price  
                FROM awb  
                WHERE scan_status IS NOT NULL  
                AND (loc IS NULL OR loc = '')  
                AND DATE(scan_status) BETWEEN '{$db->escape($first_day_of_previous_month)}' AND '{$db->escape($last_day_of_previous_month)}'  
                GROUP BY invoice  
                HAVING invoice IS NOT NULL  
            ) AS t";  
      
    return find_by_sql($sql);  
}  

// Check if the filter form is submitted  
if (isset($_POST['filter'])) {  
    $start_date = $_POST['start_date'];  
    $end_date = $_POST['end_date'];  
} else {
    // Default to current month
    $start_date = date('Y-m-01'); // First day of the current month
    $end_date = date('Y-m-t');   // Last day of the current month
}   

// Get total omzet for each location based on selected date range  
$locations = ['Jakarta', 'Surabaya', 'Bali', 'Makassar', 'Semarang', 'Bandung'];
$total_omzet = [];
$total_omzet_previous_month = [];
$total_omzet_all = 0; // Total omzet for all cities
$total_omzet_previous_month_all = 0; // Previous month's total omzet for all cities

foreach ($locations as $location) {
    if ($location === 'Surabaya') {
        $total_omzet[$location] = get_total_omzet_surabaya($start_date, $end_date);
        $total_omzet_previous_month[$location] = get_total_omzet_surabaya_previous_month();
    } else {
        $total_omzet[$location] = get_total_omzet($location, $start_date, $end_date);
        $total_omzet_previous_month[$location] = get_total_omzet_previous_month($location);
    }

    // Sum total omzet for all cities
    $current_month_omzet = !empty($total_omzet[$location]) ? $total_omzet[$location][0]['total_omzet'] : 0;
    $total_omzet_all += $current_month_omzet;

    // Sum previous month's total omzet for all cities
    $previous_month_omzet = !empty($total_omzet_previous_month[$location]) ? $total_omzet_previous_month[$location][0]['total_omzet'] : 0;
    $total_omzet_previous_month_all += $previous_month_omzet;
}

include_once('layouts/header.php');  
?>  
  
<div class="row">  
    <div class="col-md-12">  
        <?php echo display_msg($msg); ?>  
    </div>  
</div>  
  
<!-- Filter Form -->  
<div class="row">  
    <div class="col-md-12">  
        <form action="sales_dashboard.php" method="post" class="form-inline">  
            <div class="form-group">  
                <label for="start_date">Start Date: </label>  
                <input type="date" id="start_date" name="start_date" class="form-control" value="<?php echo htmlspecialchars($start_date); ?>">  
            </div>  
            <div class="form-group">  
                <label for="end_date">End Date: </label>  
                <input type="date" id="end_date" name="end_date" class="form-control" value="<?php echo htmlspecialchars($end_date); ?>">  
            </div>  
            <button type="submit" name="filter" class="btn btn-primary">Filter</button>  
        </form>  
    </div>  
</div>  
  
<!-- Display Total Omzet for All Locations -->  
<div class="row">  
    <?php  
    foreach ($locations as $location): ?>  
        <div class="col-md-3">  
            <div class="panel panel-default">  
                <div class="panel-heading clearfix">  
                    <h3 class="panel-title">Total Omzet <?php echo $location; ?></h3>  
                </div>  
                <div class="panel-body">  
                    <?php 
                    $current_month_omzet = !empty($total_omzet[$location]) ? $total_omzet[$location][0]['total_omzet'] : 0;
                    $previous_month_omzet = !empty($total_omzet_previous_month[$location]) ? $total_omzet_previous_month[$location][0]['total_omzet'] : 0;
                    $difference = $current_month_omzet - $previous_month_omzet;
                    ?>
                    <h4>Total Omzet <?php echo $location; ?> (Current Month):   
                        <strong><?php echo number_format($current_month_omzet, 2); ?></strong>  
                    </h4>
                    <h4>Total Omzet <?php echo $location; ?> (Previous Month):   
                        <strong><?php echo number_format($previous_month_omzet, 2); ?></strong>  
                    </h4>
                    <h4>Difference:   
                        <strong><?php echo number_format($difference, 2); ?></strong>  
                    </h4>
                    <?php if ($current_month_omzet == 0): ?>  
                        <div class="alert alert-info">Tidak ada data penjualan untuk <?php echo $location; ?>.</div>  
                    <?php endif; ?>  
                </div>  
            </div>  
        </div>  
    <?php endforeach; ?>  
</div>  

<!-- Display Total Omzet for All Cities -->  
<div class="row">  
    <div class="col-md-12">  
        <div class="panel panel-default">  
            <div class="panel-heading clearfix">  
                <h3 class="panel-title">Total Omzet Seluruh Kota</h3>  
            </div>  
            <div class="panel-body">  
                <h4>Total Omzet (Current Month):   
                    <strong><?php echo number_format($total_omzet_all, 2); ?></strong>  
                </h4>
                <h4>Total Omzet (Previous Month):   
                    <strong><?php echo number_format($total_omzet_previous_month_all, 2); ?></strong>  
                </h4>
                <h4>Balance:   
                    <strong><?php echo number_format($total_omzet_all - $total_omzet_previous_month_all, 2); ?></strong>  
                </h4>
            </div>  
        </div>  
    </div>  
</div>  
  
<?php include_once('layouts/footer.php'); ?>  
