V1rus Private

Viewing: ledger_report_process.php

<?php
$page_title = 'Ledger Report Print';
$results = '';
require_once('includes/load.php');
// Checkin What level user has permission to view this page
page_require_level(3);
?>
<?php
if(isset($_GET['start_date']) && isset($_GET['end_date'])){
  $start_date = remove_junk($db->escape($_GET['start_date']));
  $end_date = remove_junk($db->escape($_GET['end_date']));
  $ledger_data = get_ledger_data($start_date, $end_date);
  $monthly_summary = get_monthly_summary($start_date, $end_date);
} else {
  $session->msg("d", "Select dates");
  redirect('ledger.php', false);
}

// Reuse the functions from ledger.php
function get_ledger_data($start_date, $end_date) {
  global $db;
  
  $sql = "SELECT 
            DATE(s.date) as transaction_date,
            p.name as product_name,
            s.qty as quantity,
            s.price as total_amount,
            p.buy_price as cost_price,
            (s.price - (p.buy_price * s.qty)) as profit,
            'Sale' as transaction_type
          FROM sales s
          JOIN products p ON s.product_id = p.id
          WHERE DATE(s.date) BETWEEN '{$start_date}' AND '{$end_date}'
          UNION ALL
          SELECT 
            DATE(p.date) as transaction_date,
            p.name as product_name,
            p.quantity as quantity,
            (p.buy_price * p.quantity) as total_amount,
            p.buy_price as cost_price,
            0 as profit,
            'Purchase' as transaction_type
          FROM products p
          WHERE DATE(p.date) BETWEEN '{$start_date}' AND '{$end_date}'
          ORDER BY transaction_date DESC";
  
  return find_by_sql($sql);
}

function get_monthly_summary($start_date, $end_date) {
  global $db;
  
  $sql = "SELECT 
            YEAR(s.date) as year,
            MONTH(s.date) as month,
            SUM(s.price) as total_sales,
            SUM(s.price - (p.buy_price * s.qty)) as total_profit,
            COUNT(s.id) as transaction_count
          FROM sales s
          JOIN products p ON s.product_id = p.id
          WHERE DATE(s.date) BETWEEN '{$start_date}' AND '{$end_date}'
          GROUP BY YEAR(s.date), MONTH(s.date)
          ORDER BY year DESC, month DESC";
  
  return find_by_sql($sql);
}

function get_opening_balance($date) {
  global $db;
  
  $sql = "SELECT COALESCE(SUM(price), 0) as opening_balance 
          FROM sales 
          WHERE DATE(date) < '{$date}'";
  
  $result = find_by_sql($sql);
  return $result ? $result[0]['opening_balance'] : 0;
}
?>
<!doctype html>
<html lang="en-US">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
  <title>Ledger Report</title>
  <link rel="preconnect" href="https://fonts.googleapis.com">
  <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
  <link href="https://fonts.googleapis.com/css2?family=Montserrat:ital,wght@0,100..900;1,100..900&display=swap" rel="stylesheet">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css"/>
  <style>
  @media print {
    html,body{
      font-size: 9.5pt;
      margin: 0;
      padding: 0;
      font-family: "Montserrat", sans-serif;
    }.page-break {
      page-break-before:always;
      width: auto;
      margin: auto;
    }
  }
  .page-break{
    width: 980px;
    margin: 0 auto;
  }
  .sale-head{
    margin: 40px 0;
    font-family: "Montserrat", sans-serif;
    text-align: center;
  }.sale-head h1,.sale-head strong{
    padding: 10px 20px;
    display: block;
  }.sale-head h1{
    margin: 0;
    border-bottom: 1px solid #212121;
    font-family: "Montserrat", sans-serif;
  }.table>thead:first-child>tr:first-child>th{
    border-top: 1px solid #000;
    font-family: "Montserrat", sans-serif;
  }
  table thead tr th {
    text-align: center;
    border: 1px solid #ededed;
  }table tbody tr td{
    vertical-align: middle;
  }.sale-head,table.table thead tr th,table tbody tr td,table tfoot tr td{
    border: 1px solid #212121;
    white-space: nowrap;
    font-family: "Montserrat", sans-serif;
  }.sale-head h1,table thead tr th,table tfoot tr td{
    background-color: #f8f8f8;
  }tfoot{
    color:#000;
    text-transform: uppercase;
    font-weight: 500;
  }
  .summary-box {
    border: 1px solid #ddd;
    padding: 15px;
    margin-bottom: 20px;
    background-color: #f9f9f9;
  }
  </style>
</head>
<body>
  <?php if($ledger_data): ?>
    <div class="page-break">
      <div class="sale-head">
        <h1>Inventory Management System - Ledger Report</h1>
        <strong>Period: <?php echo $start_date; ?> to <?php echo $end_date; ?></strong>
        <strong>Generated on: <?php echo date('Y-m-d H:i:s'); ?></strong>
      </div>
      
      <!-- Summary Section -->
      <div class="row">
        <div class="col-md-12">
          <div class="summary-box">
            <h3>Summary</h3>
            <?php
            $total_sales = array_sum(array_map(function($item) {
              return $item['transaction_type'] == 'Sale' ? $item['total_amount'] : 0;
            }, $ledger_data));
            
            $total_purchases = array_sum(array_map(function($item) {
              return $item['transaction_type'] == 'Purchase' ? $item['total_amount'] : 0;
            }, $ledger_data));
            
            $total_profit = array_sum(array_column($ledger_data, 'profit'));
            $opening_balance = get_opening_balance($start_date);
            $closing_balance = $opening_balance + $total_sales - $total_purchases;
            ?>
            <p><strong>Opening Balance:</strong> ₹<?php echo number_format($opening_balance, 2); ?></p>
            <p><strong>Total Sales:</strong> ₹<?php echo number_format($total_sales, 2); ?></p>
            <p><strong>Total Purchases:</strong> ₹<?php echo number_format($total_purchases, 2); ?></p>
            <p><strong>Total Profit:</strong> ₹<?php echo number_format($total_profit, 2); ?></p>
            <p><strong>Closing Balance:</strong> ₹<?php echo number_format($closing_balance, 2); ?></p>
          </div>
        </div>
      </div>
      
      <!-- Monthly Summary -->
      <?php if(!empty($monthly_summary)): ?>
      <table class="table table-border">
        <thead>
          <tr>
            <th colspan="5" style="text-align: center; background-color: #e9ecef;">MONTHLY SUMMARY</th>
          </tr>
          <tr>
            <th>Month</th>
            <th>Year</th>
            <th>Total Sales</th>
            <th>Total Profit</th>
            <th>Transactions</th>
          </tr>
        </thead>
        <tbody>
          <?php foreach($monthly_summary as $month): ?>
          <tr>
            <td><?php echo DateTime::createFromFormat('!m', $month['month'])->format('F'); ?></td>
            <td><?php echo $month['year']; ?></td>
            <td>₹<?php echo number_format($month['total_sales'], 2); ?></td>
            <td>₹<?php echo number_format($month['total_profit'], 2); ?></td>
            <td><?php echo $month['transaction_count']; ?></td>
          </tr>
          <?php endforeach; ?>
        </tbody>
      </table>
      <?php endif; ?>
      
      <!-- Detailed Transactions -->
      <table class="table table-border">
        <thead>
          <tr>
            <th colspan="6" style="text-align: center; background-color: #e9ecef;">DETAILED TRANSACTIONS</th>
          </tr>
          <tr>
            <th>Date</th>
            <th>Product Name</th>
            <th>Type</th>
            <th>Qty</th>
            <th>Amount</th>
            <th>Profit</th>
          </tr>
        </thead>
        <tbody>
          <?php 
          $running_balance = $opening_balance;
          ?>
          
          <!-- Opening Balance -->
          <tr style="background-color: #f8f9fa;">
            <td><strong><?php echo $start_date; ?></strong></td>
            <td colspan="2"><strong>OPENING BALANCE</strong></td>
            <td></td>
            <td><strong>₹<?php echo number_format($running_balance, 2); ?></strong></td>
            <td></td>
          </tr>
          
          <?php foreach($ledger_data as $transaction): ?>
          <tr>
            <td><?php echo $transaction['transaction_date']; ?></td>
            <td><?php echo $transaction['product_name']; ?></td>
            <td><?php echo $transaction['transaction_type']; ?></td>
            <td><?php echo $transaction['quantity']; ?></td>
            <td>
              <?php if($transaction['transaction_type'] == 'Sale'): ?>
                +₹<?php echo number_format($transaction['total_amount'], 2); ?>
                <?php $running_balance += $transaction['total_amount']; ?>
              <?php else: ?>
                -₹<?php echo number_format($transaction['total_amount'], 2); ?>
                <?php $running_balance -= $transaction['total_amount']; ?>
              <?php endif; ?>
            </td>
            <td>
              <?php if($transaction['transaction_type'] == 'Sale'): ?>
                ₹<?php echo number_format($transaction['profit'], 2); ?>
              <?php else: ?>
                -
              <?php endif; ?>
            </td>
          </tr>
          <?php endforeach; ?>
          
          <!-- Closing Balance -->
          <tr style="background-color: #f8f9fa;">
            <td><strong><?php echo $end_date; ?></strong></td>
            <td colspan="2"><strong>CLOSING BALANCE</strong></td>
            <td></td>
            <td><strong>₹<?php echo number_format($running_balance, 2); ?></strong></td>
            <td></td>
          </tr>
        </tbody>
      </table>
    </div>
  <?php
  else:
    $session->msg("d", "Sorry no transactions found. ");
    redirect('ledger.php', false);
  endif;
  ?>
</body>
</html>
<?php if(isset($db)) { $db->db_disconnect(); } ?>
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