V1rus Private

Viewing: sales.php

<?php
// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

$page_title = 'All Sales';
require_once('includes/load.php');

// Check if database connection is working
if (!isset($db) || !$db) {
    die("Database connection failed. Please check your configuration.");
}

page_require_level(3);

// Get filter parameters with safe defaults
$current_store_id = get_current_store_id();
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
$payment_type = isset($_GET['payment_type']) ? $_GET['payment_type'] : '';
$sale_type = isset($_GET['sale_type']) ? $_GET['sale_type'] : '';

// Build filter conditions with proper escaping
$where_conditions = ["s.store_id = '{$current_store_id}'"];
$where_conditions[] = "s.date BETWEEN '{$start_date} 00:00:00' AND '{$end_date} 23:59:59'";

if(!empty($payment_type)) {
    $where_conditions[] = "s.payment_type = '{$payment_type}'";
}

if(!empty($sale_type)) {
    if($sale_type == 'regular') {
        $where_conditions[] = "s.is_transferred_out = 0";
    } elseif($sale_type == 'transferred') {
        $where_conditions[] = "s.is_transferred_out = 1";
    }
}

$where_clause = implode(' AND ', $where_conditions);

// Use the existing find_by_sql function instead of direct queries
global $db;

// Get filtered sales - FIXED: Using fetch_assoc loop instead of fetch_all
$sql_sales = "SELECT s.*, p.name 
              FROM sales s 
              LEFT JOIN products p ON s.product_id = p.id 
              WHERE {$where_clause} 
              ORDER BY s.date DESC, s.id DESC";
              
$sales_result = $db->query($sql_sales);
$sales = [];

if($sales_result && $sales_result->num_rows > 0) {
    while($row = $sales_result->fetch_assoc()) {
        $sales[] = $row;
    }
}

// Get sales summary using direct query - FIXED: Better error handling
$sql_summary = "SELECT 
                COALESCE(SUM(s.total), 0) as total_sales,
                COALESCE(SUM(CASE WHEN s.payment_type = 'cash' THEN s.total ELSE 0 END), 0) as cash_sales,
                COALESCE(SUM(CASE WHEN s.payment_type = 'online' THEN s.total ELSE 0 END), 0) as online_sales,
                COALESCE(SUM(s.profit), 0) as total_profit
                FROM sales s 
                WHERE {$where_clause}";
                
$summary_result = $db->query($sql_summary);
$sales_summary = [
    'total_sales' => 0,
    'cash_sales' => 0, 
    'online_sales' => 0,
    'total_profit' => 0
];

if($summary_result && $summary_result->num_rows > 0) {
    $sales_summary = $summary_result->fetch_assoc();
}

// Calculate transferred vs regular sales totals
$total_regular_sales = 0;
$total_transferred_sales = 0;
$total_regular_profit = 0;
$total_transferred_profit = 0;

foreach($sales as $sale) {
    $isTransferred = isset($sale['is_transferred_out']) ? $sale['is_transferred_out'] : 0;
    
    if($isTransferred == 1) {
        $total_transferred_sales += isset($sale['total']) ? $sale['total'] : 0;
        $total_transferred_profit += isset($sale['profit']) ? $sale['profit'] : 0;
    } else {
        $total_regular_sales += isset($sale['total']) ? $sale['total'] : 0;
        $total_regular_profit += isset($sale['profit']) ? $sale['profit'] : 0;
    }
}
?>
<?php include_once('layouts/header.php'); ?>

<div class="row">
    <div class="col-md-12">
        <?php echo display_msg($msg); ?>
    </div>
</div>

<!-- Filters -->
<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading">
                <strong>
                    <span class="glyphicon glyphicon-filter"></span>
                    <span>Sales Filters</span>
                </strong>
            </div>
            <div class="panel-body">
                <form method="get" action="sales.php" class="form-inline">
                    <div class="form-group">
                        <label for="start_date">From:</label>
                        <input type="date" class="form-control" id="start_date" name="start_date" value="<?php echo $start_date; ?>">
                    </div>
                    <div class="form-group" style="margin-left: 10px;">
                        <label for="end_date">To:</label>
                        <input type="date" class="form-control" id="end_date" name="end_date" value="<?php echo $end_date; ?>">
                    </div>
                    <div class="form-group" style="margin-left: 10px;">
                        <label for="payment_type">Payment Type:</label>
                        <select class="form-control" id="payment_type" name="payment_type">
                            <option value="">All Payments</option>
                            <option value="cash" <?php echo ($payment_type == 'cash') ? 'selected' : ''; ?>>Cash</option>
                            <option value="online" <?php echo ($payment_type == 'online') ? 'selected' : ''; ?>>Online</option>
                        </select>
                    </div>
                    <div class="form-group" style="margin-left: 10px;">
                        <label for="sale_type">Sale Type:</label>
                        <select class="form-control" id="sale_type" name="sale_type">
                            <option value="">All Sales</option>
                            <option value="regular" <?php echo ($sale_type == 'regular') ? 'selected' : ''; ?>>Regular Sales</option>
                            <option value="transferred" <?php echo ($sale_type == 'transferred') ? 'selected' : ''; ?>>Transferred Out</option>
                        </select>
                    </div>
                    <button type="submit" class="btn btn-primary" style="margin-left: 10px;">
                        <span class="glyphicon glyphicon-search"></span> Filter
                    </button>
                    <button type="button" class="btn btn-success" style="margin-left: 10px;" onclick="printReport()">
                        <span class="glyphicon glyphicon-print"></span> Print Report
                    </button>
                    <a href="sales.php" class="btn btn-default" style="margin-left: 10px;">
                        <span class="glyphicon glyphicon-refresh"></span> Reset
                    </a>
                </form>
            </div>
        </div>
    </div>
</div>

<!-- Sales Summary Cards -->
<div class="row">
    <div class="col-md-2">
        <div class="panel panel-primary">
            <div class="panel-heading">
                <div class="row">
                    <div class="col-xs-12 text-center">
                        <div class="huge">₹<?php echo number_format($sales_summary['total_sales'], 2); ?></div>
                        <div>Total Sales</div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <div class="col-md-2">
        <div class="panel panel-success">
            <div class="panel-heading">
                <div class="row">
                    <div class="col-xs-12 text-center">
                        <div class="huge">₹<?php echo number_format($total_regular_sales, 2); ?></div>
                        <div>Regular Sales</div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <div class="col-md-2">
        <div class="panel panel-info">
            <div class="panel-heading">
                <div class="row">
                    <div class="col-xs-12 text-center">
                        <div class="huge">₹<?php echo number_format($total_transferred_sales, 2); ?></div>
                        <div>Transferred Out</div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <div class="col-md-2">
        <div class="panel panel-green">
            <div class="panel-heading">
                <div class="row">
                    <div class="col-xs-12 text-center">
                        <div class="huge">₹<?php echo number_format($sales_summary['cash_sales'], 2); ?></div>
                        <div>Cash Payments</div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <div class="col-md-2">
        <div class="panel panel-info">
            <div class="panel-heading">
                <div class="row">
                    <div class="col-xs-12 text-center">
                        <div class="huge">₹<?php echo number_format($sales_summary['online_sales'], 2); ?></div>
                        <div>Online Payments</div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <div class="col-md-2">
        <div class="panel panel-warning">
            <div class="panel-heading">
                <div class="row">
                    <div class="col-xs-12 text-center">
                        <div class="huge">₹<?php echo number_format($sales_summary['total_profit'], 2); ?></div>
                        <div>Total Profit</div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Detailed Summary -->
<div class="row">
    <div class="col-md-6">
        <div class="panel panel-default">
            <div class="panel-heading">
                <strong>
                    <span class="glyphicon glyphicon-stats"></span>
                    <span>Sales Breakdown</span>
                </strong>
            </div>
            <div class="panel-body">
                <div class="list-group">
                    <div class="list-group-item">
                        <span class="badge">₹<?php echo number_format($total_regular_sales, 2); ?></span>
                        Total Regular Sales
                    </div>
                    <div class="list-group-item">
                        <span class="badge">₹<?php echo number_format($total_regular_profit, 2); ?></span>
                        Regular Sales Profit
                    </div>
                    <div class="list-group-item">
                        <span class="badge">₹<?php echo number_format($total_transferred_sales, 2); ?></span>
                        Total Transferred Out Value
                    </div>
                    <div class="list-group-item">
                        <span class="badge">₹<?php echo number_format($total_transferred_profit, 2); ?></span>
                        Transferred Out Profit
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading clearfix">
                <strong>
                    <span class="glyphicon glyphicon-th"></span>
                    <span>All Sales - <?php echo get_current_store_name(); ?></span>
                    <small class="text-muted">
                        (<?php echo date('M j, Y', strtotime($start_date)); ?> to <?php echo date('M j, Y', strtotime($end_date)); ?>)
                        <?php if(!empty($payment_type)) echo ' - ' . ucfirst($payment_type) . ' Payments'; ?>
                        <?php if(!empty($sale_type)) echo ' - ' . ucfirst($sale_type) . ' Sales'; ?>
                    </small>
                </strong>
                <div class="pull-right">
                    <a href="add_sale.php" class="btn btn-primary">Add Sale</a>
                </div>
            </div>
            <div class="panel-body">
                <div class="table-responsive">
                    <table class="table table-bordered table-striped" id="sales-table">
                        <thead>
                            <tr>
                                <th class="text-center" style="width: 40px;">#</th>
                                <th>Product Name</th>
                                <th class="text-center" style="width: 8%;">Qty</th>
                                <th class="text-center" style="width: 10%;">Sale Price</th>
                                <th class="text-center" style="width: 10%;">Buy Price</th>
                                <th class="text-center" style="width: 10%;">Total</th>
                                <th class="text-center" style="width: 10%;">Profit</th>
                                <th class="text-center" style="width: 10%;">Payment</th>
                                <th class="text-center" style="width: 10%;">Sale Type</th>
                                <th class="text-center" style="width: 12%;">Date</th>
                                <th class="text-center" style="width: 80px;">Actions</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if(!empty($sales)): ?>
                                <?php $counter = 1; ?>
                                <?php foreach ($sales as $sale): 
                                    $isTransferred = isset($sale['is_transferred_out']) ? $sale['is_transferred_out'] : 0;
                                    $paymentType = isset($sale['payment_type']) ? $sale['payment_type'] : 'cash';
                                    $sale_type_text = ($isTransferred == 1) ? 'Transferred Out' : 'Regular';
                                    $row_class = ($isTransferred == 1) ? 'class="info"' : '';
                                ?>
                                <tr <?php echo $row_class; ?>>
                                    <td class="text-center"><?php echo $counter++; ?></td>
                                    <td><?php echo remove_junk(isset($sale['name']) ? $sale['name'] : ''); ?></td>
                                    <td class="text-center"><?php echo (int)(isset($sale['qty']) ? $sale['qty'] : 0); ?></td>
                                    <td class="text-center">₹<?php echo number_format(isset($sale['price']) ? $sale['price'] : 0, 2); ?></td>
                                    <td class="text-center">₹<?php echo number_format(isset($sale['buy_price']) ? $sale['buy_price'] : 0, 2); ?></td>
                                    <td class="text-center">₹<?php echo number_format(isset($sale['total']) ? $sale['total'] : 0, 2); ?></td>
                                    <td class="text-center <?php echo ((isset($sale['profit']) ? $sale['profit'] : 0) >= 0) ? 'text-success' : 'text-danger'; ?>">
                                        ₹<?php echo number_format(isset($sale['profit']) ? $sale['profit'] : 0, 2); ?>
                                    </td>
                                    <td class="text-center">
                                        <span class="label label-<?php echo ($paymentType == 'cash') ? 'success' : 'info'; ?>">
                                            <?php echo strtoupper($paymentType); ?>
                                        </span>
                                    </td>
                                    <td class="text-center">
                                        <span class="label label-<?php echo ($isTransferred == 1) ? 'info' : 'default'; ?>">
                                            <?php echo $sale_type_text; ?>
                                        </span>
                                    </td>
                                    <td class="text-center"><?php echo date('M j, Y g:i A', strtotime(isset($sale['date']) ? $sale['date'] : 'now')); ?></td>
                                    <td class="text-center">
                                        <div class="btn-group">
                                            <a href="edit_sale.php?id=<?php echo (int)(isset($sale['id']) ? $sale['id'] : 0);?>" class="btn btn-warning btn-xs" title="Edit" data-toggle="tooltip">
                                                <span class="glyphicon glyphicon-edit"></span>
                                            </a>
                                            <a href="delete_sale.php?id=<?php echo (int)(isset($sale['id']) ? $sale['id'] : 0);?>" class="btn btn-danger btn-xs" title="Delete" data-toggle="tooltip">
                                                <span class="glyphicon glyphicon-trash"></span>
                                            </a>
                                        </div>
                                    </td>
                                </tr>
                                <?php endforeach;?>
                            <?php else: ?>
                                <tr>
                                    <td colspan="11" class="text-center">No sales found for the selected criteria.</td>
                                </tr>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<script>
function printReport() {
    var printContent = document.getElementById('sales-table').outerHTML;
    var originalContent = document.body.innerHTML;
    
    var printWindow = window.open('', '_blank');
    printWindow.document.write(`
        <html>
            <head>
                <title>Sales Report - <?php echo get_current_store_name(); ?></title>
                <style>
                    body { font-family: Arial, sans-serif; margin: 20px; }
                    table { width: 100%; border-collapse: collapse; }
                    th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
                    th { background-color: #f2f2f2; }
                    .text-center { text-align: center; }
                    .text-right { text-align: right; }
                    .text-success { color: green; }
                    .text-danger { color: red; }
                    .report-header { text-align: center; margin-bottom: 20px; }
                    .summary { margin-bottom: 20px; }
                </style>
            </head>
            <body>
                <div class="report-header">
                    <h2>Sales Report - <?php echo get_current_store_name(); ?></h2>
                    <p>Period: <?php echo date('M j, Y', strtotime($start_date)); ?> to <?php echo date('M j, Y', strtotime($end_date)); ?></p>
                    <p>Generated on: <?php echo date('M j, Y g:i A'); ?></p>
                </div>
                <div class="summary">
                    <p><strong>Total Sales:</strong> ₹<?php echo number_format($sales_summary['total_sales'], 2); ?></p>
                    <p><strong>Regular Sales:</strong> ₹<?php echo number_format($total_regular_sales, 2); ?></p>
                    <p><strong>Transferred Out Value:</strong> ₹<?php echo number_format($total_transferred_sales, 2); ?></p>
                    <p><strong>Cash Payments:</strong> ₹<?php echo number_format($sales_summary['cash_sales'], 2); ?></p>
                    <p><strong>Online Payments:</strong> ₹<?php echo number_format($sales_summary['online_sales'], 2); ?></p>
                    <p><strong>Total Profit:</strong> ₹<?php echo number_format($sales_summary['total_profit'], 2); ?></p>
                </div>
                ${printContent}
            </body>
        </html>
    `);
    printWindow.document.close();
    printWindow.print();
}
</script>

<?php include_once('layouts/footer.php'); ?>
User / IP	: 216.73.217.108
Host / Server	: 190.92.174.125 / aerosofthealthcare.com
System	: Linux s3739.bom1.stableserver.net 4.18.0-513.24.1.lve.2.el8.x86_64 #1 SMP Fri May 24 12:42:50 UTC 2024 x86_64