<?php
include("db/cn.php");
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

if (isset($_SESSION['user_name']) && isset($_SESSION['user_role']) && isset($_SESSION['outlet_name']) && isset($_SESSION['outlet_address'])) {
    $userName = $_SESSION['user_name'];
    $userRole = $_SESSION['user_role'];
    $outlet_address = $_SESSION['outlet_address'];
    $outlet_name = $_SESSION['outlet_name'];
}

// Get Monthly and Annual Start/End Dates
$month_start = date("Y-m-01");
$month_end = date("Y-m-t");
$year_start = date("Y-01-01");
$year_end = date("Y-12-31");

// Calculate Monthly Sale Revenue (excluding refunds)
$monthly_revenue = 0;
$query = "SELECT discount_amount AS total_revenue 
          FROM log_user_sales 
          WHERE status = '' 
          AND r_order_id = '' 
          AND date_time BETWEEN '$month_start' AND '$month_end' GROUP BY order_id";
$result = $connection->query($query);
while ($row = $result->fetch_assoc()) {
    $monthly_revenue += $row['total_revenue'];
}

// Calculate Annual Sale Revenue (excluding refunds)
$annual_revenue = 0;
$query = "SELECT discount_amount AS total_revenue 
          FROM log_user_sales 
          WHERE status = '' 
          AND r_order_id = '' 
          AND date_time BETWEEN '$year_start' AND '$year_end' GROUP BY order_id";
$result = $connection->query($query);
while ($row = $result->fetch_assoc()) {
    $annual_revenue += $row['total_revenue'];
}

// Calculate Monthly Refunded Sale Revenue
$monthly_refunded_revenue = 0;
$query = "SELECT discount_amount AS total_refunds 
          FROM log_user_sales 
          WHERE status = 'refund' 
          AND date_time BETWEEN '$month_start' AND '$month_end' GROUP BY r_order_id";
$result = $connection->query($query);
while ($row = $result->fetch_assoc()) {
    $monthly_refunded_revenue += $row['total_refunds'];
}

// Calculate Annual Refunded Sale Revenue
$annual_refunded_revenue = 0;
$query = "SELECT discount_amount AS total_refunds 
          FROM log_user_sales 
          WHERE status = 'refund' 
          AND date_time BETWEEN '$year_start' AND '$year_end' GROUP BY r_order_id";
$result = $connection->query($query);
while ($row = $result->fetch_assoc()) {
    $annual_refunded_revenue += $row['total_refunds'];
}

// Calculate Monthly Sale Cost (excluding refunds)
$monthly_cost = 0;
$query = "SELECT sale_cost AS total_cost 
          FROM log_user_sales 
          WHERE status = '' 
          AND date_time BETWEEN '$month_start' AND '$month_end'";
$result = $connection->query($query);
while ($row = $result->fetch_assoc()) {
    $monthly_cost += $row['total_cost'];
}

// Calculate Annual Sale Cost (excluding refunds)
$annual_cost = 0;
$query = "SELECT sale_cost AS total_cost 
          FROM log_user_sales 
          WHERE status = '' 
          AND date_time BETWEEN '$year_start' AND '$year_end'";
$result = $connection->query($query);
while ($row = $result->fetch_assoc()) {
    $annual_cost += $row['total_cost'];
}

// Calculate Monthly Refunded Sale Cost
$monthly_refunded_cost = 0;
$query = "SELECT sale_cost AS total_refunded_cost 
          FROM log_user_sales 
          WHERE status = 'refund' 
          AND date_time BETWEEN '$month_start' AND '$month_end'";
$result = $connection->query($query);
while ($row = $result->fetch_assoc()) {
    $monthly_refunded_cost += $row['total_refunded_cost'];
}

// Calculate Annual Refunded Sale Cost
$annual_refunded_cost = 0;
$query = "SELECT sale_cost AS total_refunded_cost 
          FROM log_user_sales 
          WHERE status = 'refund' 
          AND date_time BETWEEN '$year_start' AND '$year_end' ";
$result = $connection->query($query);
while ($row = $result->fetch_assoc()) {
    $annual_refunded_cost += (int)$row['total_refunded_cost'];
}

// Calculate Monthly Expenses
$monthly_expenses = 0;
$query = "SELECT amount AS total_expenses 
          FROM incash 
          WHERE date_time BETWEEN '$month_start' AND '$month_end'";
$result = $connection->query($query);
while ($row = $result->fetch_assoc()) {
    $monthly_expenses += (int)$row['total_expenses'];
}

// Calculate Annual Expenses
$annual_expenses = 0;
$query = "SELECT amount AS total_expenses 
          FROM incash 
          WHERE date_time BETWEEN '$year_start' AND '$year_end'";
$result = $connection->query($query);
while ($row = $result->fetch_assoc()) {
    $annual_expenses += (int)$row['total_expenses'];
}

// Calculate Net Revenue (excluding refunds)
$monthly_net_revenue = $monthly_revenue -  $monthly_refunded_revenue;
$annual_net_revenue = $annual_revenue - $annual_refunded_revenue;

// Calculate Net Cost (excluding refunds)
$monthly_net_cost = $monthly_cost - $monthly_refunded_cost;
$annual_net_cost = $annual_cost - $annual_refunded_cost ;

// Calculate Net Profit
$monthly_net_profit = $monthly_net_revenue - $monthly_net_cost - $monthly_expenses;
$annual_net_profit = $annual_net_revenue - $annual_net_cost - $annual_expenses;

// Now echo this data in the respective card elements
?>
<!DOCTYPE html>
<html lang="en">

<head>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="description" content="">
    <meta name="author" content="">

    <title>Kashmir Bazar</title>

    <!-- Custom fonts for this template-->
    <link href="../vendor/fontawesome-free/css/all.min.css" rel="stylesheet" type="text/css">
   <link href="https://fonts.googleapis.com/css2?family=Quicksand:wght@200;300;400;500;600;700&display=swap" rel="stylesheet">

    <!-- Custom styles for this template-->
    <link href="../css/sb-admin-2.min.css" rel="stylesheet">
    
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<style>
body {
    font-family: 'Quicksand', sans-serif;
    
}
		    .chart-container {
      position: relative;
      background: rgba(255, 255, 255, 0.9);
      border-radius: 10px;
      padding: 15px;
      box-shadow: 0 0 10px rgba(0, 0, 0, 0.2);
    }

    canvas {
      max-width: 100%;
      height: auto;
    }
.bg-gradient-primary{
    
	background:#0A4657;
	color:#fff;	
	
	}

.btn-primary{
	background:#0A4657;
	color:#fff;
	 border:1px #0A4657 solid;
	
	}  
.btn-primary:hover{
  
  background:#fff;
  border:1px #0A4657 solid;
	color:#0A4657;
	
	}	 
    .chart-container {
      position: relative;
      background: rgba(255, 255, 255, 0.9);
      border-radius: 10px;
      padding: 15px;
      box-shadow: 0 0 10px rgba(0, 0, 0, 0.2);
    }

    canvas {
      max-width: 100%;
      height: auto;
    }
	
	.fa-download:hover{
		color:#953E39;
		}
		
.text-primary{
	  	
		}
  </style>

<body id="page-top">

    <!-- Page Wrapper -->
    <div id="wrapper">

        <!-- Sidebar -->
        <?php
        include("common/sd.php");
		?>
        <!-- End of Sidebar -->

        <!-- Content Wrapper -->
        <div id="content-wrapper" class="d-flex flex-column">

            <!-- Main Content -->
            <div id="content">

                <!-- Topbar -->
                <nav class="navbar navbar-expand navbar-light bg-white topbar mb-4 static-top shadow">

                    <!-- Sidebar Toggle (Topbar) -->
                    <button id="sidebarToggleTop" class="btn btn-link d-md-none rounded-circle mr-3">
                        <i class="fa fa-bars"></i>
                    </button>

                    <!-- Topbar Search -->
                    
  <?php include('common/log.php');  ?>

                    <!-- Topbar Navbar -->
                    
                </nav>
                <!-- End of Topbar -->

                <!-- Begin Page Content -->
                
                
             <div class="container-fluid">
                     <div class="d-sm-flex align-items-center justify-content-between mb-4">
                        <h1 class="h3 mb-0 text-gray-800" style="font-weight:700;">Dashboard</h1>
                        
                    </div>

                    <div class="container-fluid" style="background: linear-gradient(360deg, #060606, #0A4657); border:5px solid #E3EAEC; border-radius:20px;">
                    <div class="row">
                    <div class="col-sm-5"> 
                    <br>
                    <img src="../img/logo231.png" width="170" height="170">
                    <br><br>
                    <h3 class="mb-3 text-white" style="font-size:25px;font-weight:700;">Welcome Admin ,<br>Kashmir Bazar</h3>
                    <p class="text-white btn btn-dark btn-sm">Logged In</p>
                    </div>
                    <div class="col-sm-2"></div>
                    <div class="col-sm-5">
                    
                    <img src="../img/school.png" width="596" height="371" class="img-fluid" />
                    </div>
                    </div>
                    
                    </div>        

<br><br>

                   <div class="container-fluid">
                     <div class="row">

<!-- Earnings (Monthly) Card Example -->
                        <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card border-left-dark shadow h-100 py-2">
                                <div class="card-body">
                                    <div class="row no-gutters align-items-center">
                                        <div class="col mr-2">
                                            <div class="text-xs font-weight-bold text-uppercase mb-1"  style="font-size:13px;color:#0A4657;">
                                                Sale Revenue(Monthly)</div>
                                            <div class="h5 mb-0 font-weight-bold text-gray-800">€.<?= number_format($monthly_net_revenue) ?>/-</div>
                                        </div>
                                        <div class="col-auto">
                                            <i class="fas fa-euro-sign fa-2x" style="color:#0A4657;"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <!-- Earnings (Monthly) Card Example -->
                        <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card border-left-dark shadow h-100 py-2">
                                <div class="card-body">
                                    <div class="row no-gutters align-items-center">
                                        <div class="col mr-2">
                                            <div class="text-xs font-weight-bold text-uppercase mb-1"  style="font-size:13px;color:#0A4657;">
                                                Sale Revenue(Annual)</div>
                                            <div class="h5 mb-0 font-weight-bold text-gray-800">€.<?= number_format($annual_net_revenue) ?>/-</div>
                                        </div>
                                        <div class="col-auto">
                                            <i class="fas fa-euro-sign fa-2x" style="color:#0A4657;"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                   

                       



<!-- Sale Cost (Annual) Card Example -->
<div class="col-xl-3 col-md-6 mb-4">
    <div class="card border-left-dark shadow h-100 py-2">
        <div class="card-body">
            <div class="row no-gutters align-items-center">
                <div class="col mr-2">
                    <div class="text-xs font-weight-bold text-uppercase mb-1" style="font-size:13px;color:#0A4657;">
                        Sale Cost (Monthly)
                    </div>
                    <div class="h5 mb-0 font-weight-bold text-gray-800">
                        €. <?= number_format($monthly_net_cost) ?> /-
                    </div>
                </div>
                <div class="col-auto">
                    <i class="fas fa-euro-sign fa-2x" style="color:#0A4657;"></i>
                </div>
            </div>
        </div>
    </div>

</div>


<!-- Sale Cost (Annual) Card Example -->
                        <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card border-left-dark shadow h-100 py-2">
                                <div class="card-body">
                                    <div class="row no-gutters align-items-center">
                                        <div class="col mr-2">
                                            <div class="text-xs font-weight-bold text-uppercase mb-1" style="font-size:13px;color:#0A4657;">
                                                Sale Cost (Annual)
                                            </div>
                                            <div class="h5 mb-0 font-weight-bold text-gray-800">
                                                €. <?= number_format($annual_net_cost) ?>/-
                                            </div>
                                        </div>
                                        <div class="col-auto">
                                            <i class="fas fa-euro-sign fa-2x" style="color:#0A4657;"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        
                        </div>
      </div>               
                     <div class="row">
                       
                    
                        
                         <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card border-left-dark shadow h-100 py-2">
                                <div class="card-body">
                                    <div class="row no-gutters align-items-center">
                                        <div class="col mr-2">
                                            <div class="text-xs font-weight-bold text-uppercase mb-1"  style="font-size:13px;color:#0A4657;">
                                                Expense (Monthly)</div>
                                            <div class="h5 mb-0 font-weight-bold text-gray-800">€.  <?= number_format((int)$monthly_expenses) ?> /-</div>
                                        </div>
                                        <div class="col-auto">
                                            <i class="fas fa-euro-sign fa-2x " style="color:#0A4657;"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    
                        <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card border-left-dark shadow h-100 py-2">
                                <div class="card-body">
                                    <div class="row no-gutters align-items-center">
                                        <div class="col mr-2">
                                            <div class="text-xs font-weight-bold text-uppercase mb-1"  style="font-size:13px;color:#0A4657;">
                                               Expense (Annual)</div>
                                            <div class="h5 mb-0 font-weight-bold text-gray-800">€.<?= number_format(@(int)$annual_expenses) ?> /-</div>
                                        </div>
                                        <div class="col-auto">
                                            <i class="fas fa-euro-sign fa-2x " style="color:#0A4657;"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        <!-- Earnings (Monthly) Card Example -->
                        <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card border-left-dark shadow h-100 py-2">
                                <div class="card-body">
                                    <div class="row no-gutters align-items-center">
                                        <div class="col mr-2">
                                            <div class="text-xs font-weight-bold text-uppercase mb-1"  style="font-size:13px;color:#0A4657;">
                                               Net-Profit (Monthly)</div>
                                            <div class="h5 mb-0 font-weight-bold text-gray-800">€. <?= number_format(@(int)$monthly_net_profit) ?> /-</div>
                                        </div>
                                        <div class="col-auto">
                                            <i class="fas fa-euro-sign fa-2x " style="color:#0A4657;"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <!-- Earnings (Monthly) Card Example -->
                        <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card border-left-dark shadow h-100 py-2">
                                <div class="card-body">
                                    <div class="row no-gutters align-items-center">
                                        <div class="col mr-2">
                                            <div class="text-xs font-weight-bold text-uppercase mb-1"  style="font-size:13px;color:#0A4657;">
                                               Net-Profit (Annual)</div>
                                            <div class="h5 mb-0 font-weight-bold text-gray-800">€. <?= number_format($annual_net_profit) ?> /-</div>
                                        </div>
                                        <div class="col-auto">
                                            <i class="fas fa-euro-sign fa-2x " style="color:#0A4657;"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <!-- Pending Requests Card Example -->
                        
                        
                     </div>
                     
                   </div>
                   <div class="container-fluid">
                       <div class="row font-weight-bold">
                          
                           <div class="col-6">
                               <?php
                                // Database connection (update with your credentials)
                                $sr = 1;
                                if ($connection->connect_error) {
                                    die("Connection failed: " . $connection->connect_error);
                                }
                                
                                // Find most selling product
                                $sql = "SELECT product_name, COUNT(*) AS count 
                                        FROM log_user_sales 
                                        GROUP BY product_name 
                                        ORDER BY count DESC 
                                        LIMIT 1";
                                $result = $connection->query($sql);
                                
                                // Store product name
                                $mostSelling = "No data";
                                if ($result && $row = $result->fetch_assoc()) {
                                    $mostSelling = $row['product_name'];
                                }
                                ?>
                                
                                <table class="table table-bordered text-center bg-white">
                                    <tr class="bg-dark text-white">
                                        <td colspan="2">Most Selling Product</td>
                                    </tr>
                                    <tr style="background:#0A4657; color:#fff;">
                                        <td>Sr#</td>
                                        <td>Product Name</td>
                                    </tr>
                                    <tr>
                                        <td><?php echo $sr++; ?></td>
                                        <td ><?php echo htmlspecialchars($mostSelling); ?></td>
                                    </tr>
                                </table>
                           </div>
                           <div class="col-6">
                               <table class="table table-bordered text-center bg-white">
                                   <tr class="bg-dark text-white">
                                       <td colspan="4">Low Stock Product </td>
                                   </tr>
                                   <tr style="background:#516D45; color:#fff;">
                                       <td>Sr#</td>
                                       <td>Product Name</td>
                                       <td>Stock</td>
                                   </tr>
                                   <?php
                                   $sr = 1;
                                    $p = mysqli_query($connection,"select * from products where quantity < 5");
                                    while($pr = mysqli_fetch_array($p)){
                                   ?>
                                   <tr>
                                       <td><?php echo $sr++; ?></td>
                                       <td><?php echo $pr["product_name"];?></td>
                                       <td><?php echo $pr["quantity"];?></td>
                                   </tr>
                                   <?php 
                                    }
                                   ?>
                               </table>
                           </div>
                           <div class="col-6">
                                <?php
                                    $sr = 1;
                                    
                                    if ($connection->connect_error) {
                                        die("Connection failed: " . $connection->connect_error);
                                    }
                                    
                                    // Query to get least sold product(s)
                                    $sql = "SELECT product_name, COUNT(*) as sale_count 
                                            FROM log_user_sales 
                                            GROUP BY product_name 
                                            ORDER BY sale_count ASC 
                                            LIMIT 1";
                                    
                                    $result = $connection->query($sql);
                                    
                                    ?>
                                    
                                    <table class="table table-bordered text-center bg-white">
                                        <tr class="bg-dark text-white">
                                            <td colspan="2">Least Selling Product</td>
                                        </tr>
                                        <tr style="background:#0A4657; color:#fff;">
                                            <td>Sr#</td>
                                            <td>Product Name</td>
                                        </tr>
                                        <?php if ($result && $result->num_rows > 0): ?>
                                            <?php while($row = $result->fetch_assoc()): ?>
                                                <tr>
                                                    <td><?= $sr++; ?></td>
                                                    <td><?= htmlspecialchars($row['product_name']); ?></td>
                                                </tr>
                                            <?php endwhile; ?>
                                        <?php else: ?>
                                            <tr>
                                                <td colspan="2">No Data Found</td>
                                            </tr>
                                        <?php endif; ?>
                                    </table>
                           </div>
                           <div class="col-6">
                               <?php
                                $sr = 1;
                                
                               
                                
                                if ($connection->connect_error) {
                                    die("Connection failed: " . $connection->connect_error);
                                }
                                
                                // Get current date
                                $current_date = date('Y-m-d');
                                
                                // Query to get expired products
                                $sql = "SELECT expiry, product_name 
                                        FROM products 
                                        WHERE expiry <= '$current_date' and expiry != ''
                                        ORDER BY expiry ASC";
                                
                                $result = $connection->query($sql);
                                
                                ?>
                                
                                <table class="table table-bordered text-center bg-white">
                                    <tr class="bg-dark text-white">
                                        <td colspan="3">Expiry Products</td>
                                    </tr>
                                    <tr style="background:#0A4657; color:#fff;">
                                        <td>Sr#</td>
                                        <td>Date</td>
                                        <td>Product Name</td>
                                    </tr>
                                    <?php if ($result && $result->num_rows > 0): ?>
                                        <?php while($row = $result->fetch_assoc()): ?>
                                            <tr>
                                                <td><?= $sr++; ?></td>
                                                <td><?= htmlspecialchars($row['expiry']); ?></td>
                                                <td><?= htmlspecialchars($row['product_name']); ?></td>
                                            </tr>
                                        <?php endwhile; ?>
                                    <?php else: ?>
                                        <tr>
                                            <td colspan="3">No Expired Products Found</td>
                                        </tr>
                                    <?php endif; ?>
                                </table>
                                                           </div>
                                                           <div class="col-6">
                                            <?php
                                            $sr = 1;
                                        
                                            if ($connection->connect_error) {
                                                die("Connection failed: " . $connection->connect_error);
                                            }
                                        
                                            // Current date and date 40 days later
                                            $current_date = date('Y-m-d');
                                            $limit_date = date('Y-m-d', strtotime('+40 days'));
                                        
                                            // Query for near to expiry products
                                            $sql_near_expiry = "SELECT expiry, product_name 
                                                                FROM products 
                                                                WHERE expiry > '$current_date' 
                                                                  AND expiry <= '$limit_date' 
                                                                  AND expiry != ''
                                                                ORDER BY expiry ASC";
                                        
                                            $result_near = $connection->query($sql_near_expiry);
                                            ?>
                                        
                                            <table class="table table-bordered text-center bg-white">
                                                <tr class="bg-dark text-white">
                                                    <td colspan="3">Near to Expiry Products</td>
                                                </tr>
                                                <tr style="background:#0A4657; color:#fff;">
                                                    <td>Sr#</td>
                                                    <td>Date</td>
                                                    <td>Product Name</td>
                                                </tr>
                                                <?php if ($result_near && $result_near->num_rows > 0): ?>
                                                    <?php while($row = $result_near->fetch_assoc()): ?>
                                                        <tr>
                                                            <td><?= $sr++; ?></td>
                                                            <td><?= htmlspecialchars($row['expiry']); ?></td>
                                                            <td><?= htmlspecialchars($row['product_name']); ?></td>
                                                        </tr>
                                                    <?php endwhile; ?>
                                                <?php else: ?>
                                                    <tr>
                                                        <td colspan="3">No Near Expiry Products Found</td>
                                                    </tr>
                                                <?php endif; ?>
                                            </table>
                                        </div>

                           <div class="col-6"></div>
                    
                           
                       </div>
                   </div>
                   
              
       


                </div>
                <!-- /.container-fluid -->

            </div>
            <br><br>
            <!-- End of Main Content -->

            <!-- Footer -->
            <?php include("common/main_ft.php");  ?>
            <!-- End of Footer -->

        </div>
        <!-- End of Content Wrapper -->

    </div>
    <!-- End of Page Wrapper -->

    <!-- Scroll to Top Button-->
    <a class="scroll-to-top rounded" href="#page-top">
        <i class="fas fa-angle-up"></i>
    </a>

    <!-- Logout Modal-->
    <div class="modal fade" id="logoutModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel"
        aria-hidden="true">
        <div class="modal-dialog" role="document">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="exampleModalLabel">Ready to Leave?</h5>
                    <button class="close" type="button" data-dismiss="modal" aria-label="Close">
                        <span aria-hidden="true">×</span>
                    </button>
                </div>
                <div class="modal-body">Select "Logout" below if you are ready to end your current session.</div>
                <div class="modal-footer">
                    <button class="btn btn-secondary" type="button" data-dismiss="modal">Cancel</button>
                    <a class="btn btn-primary" href="">Logout</a>
                </div>
            </div>
        </div>
    </div>

       
</div>    
</body>
 <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.10.2/dist/umd/popper.min.js"></script>

   <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.10.2/dist/umd/popper.min.js"></script>
    <!-- Bootstrap core JavaScript-->
    <script src="../vendor/jquery/jquery.min.js"></script>
    <script src="../vendor/bootstrap/js/bootstrap.bundle.min.js"></script>

    <!-- Core plugin JavaScript-->
    <script src="../vendor/jquery-easing/jquery.easing.min.js"></script>

    <!-- Custom scripts for all pages-->
    <script src="../js/sb-admin-2.min.js"></script>

</body>

</html>