V1rus Private

Viewing: ledger.php

<?php
$page_title = 'Ledger Management';
require_once('includes/load.php');
page_require_level(3);

// Initialize variables
$store_id = get_current_store_id();
$all_stores = get_all_stores();

// Check if form is submitted for creating new ledger entry
if(isset($_POST['add_ledger_entry'])){
    $req_fields = array('entry_date','entry_type','amount','description','store_id');
    validate_fields($req_fields);

    if(empty($errors)):
        $entry_date = remove_junk($db->escape($_POST['entry_date']));
        $entry_type = remove_junk($db->escape($_POST['entry_type']));
        $amount = remove_junk($db->escape($_POST['amount']));
        $description = remove_junk($db->escape($_POST['description']));
        $store_id_entry = remove_junk($db->escape($_POST['store_id']));
        $user_id = (int)$_SESSION['user_id'];
        
        if(add_ledger_entry($entry_date, $entry_type, $amount, $description, $store_id_entry, $user_id)):
            $session->msg("s", "Ledger entry added successfully.");
            redirect('ledger.php', false);
        else:
            $session->msg("d", "Failed to add ledger entry.");
            redirect('ledger.php', false);
        endif;
    else:
        $session->msg("d", $errors);
        redirect('ledger.php', false);
    endif;
}

// Check if form is submitted for updating ledger entry
if(isset($_POST['edit_ledger_entry'])){
    $req_fields = array('entry_id','entry_date','entry_type','amount','description','store_id');
    validate_fields($req_fields);

    if(empty($errors)):
        $entry_id = remove_junk($db->escape($_POST['entry_id']));
        $entry_date = remove_junk($db->escape($_POST['entry_date']));
        $entry_type = remove_junk($db->escape($_POST['entry_type']));
        $amount = remove_junk($db->escape($_POST['amount']));
        $description = remove_junk($db->escape($_POST['description']));
        $store_id_entry = remove_junk($db->escape($_POST['store_id']));
        
        if(update_ledger_entry($entry_id, $entry_date, $entry_type, $amount, $description, $store_id_entry)):
            $session->msg("s", "Ledger entry updated successfully.");
            redirect('ledger.php', false);
        else:
            $session->msg("d", "Failed to update ledger entry.");
            redirect('ledger.php', false);
        endif;
    else:
        $session->msg("d", $errors);
        redirect('ledger.php', false);
    endif;
}

// Check if delete ledger entry is requested
if(isset($_GET['delete_ledger_id'])){
    $delete_id = (int)$_GET['delete_ledger_id'];
    
    if(delete_ledger_entry($delete_id)):
        $session->msg("s", "Ledger entry deleted successfully.");
        redirect('ledger.php', false);
    else:
        $session->msg("d", "Failed to delete ledger entry.");
        redirect('ledger.php', false);
    endif;
}

// Get all ledger entries with optional store filter
if(isset($_POST['filter_ledger'])){
    $store_id = remove_junk($db->escape($_POST['store_id']));
    $start_date = remove_junk($db->escape($_POST['start_date']));
    $end_date = remove_junk($db->escape($_POST['end_date']));
} else {
    $start_date = date('Y-m-01'); // First day of current month
    $end_date = date('Y-m-t'); // Last day of current month
}

$ledger_entries = get_all_ledger_entries($start_date, $end_date, $store_id);

// Function to add ledger entry
function add_ledger_entry($entry_date, $entry_type, $amount, $description, $store_id, $user_id) {
    global $db;
    
    $sql = "INSERT INTO ledger (date, type, amount, description, store_id, user_id) 
            VALUES ('{$entry_date}', '{$entry_type}', '{$amount}', '{$description}', '{$store_id}', '{$user_id}')";
    
    return $db->query($sql);
}

// Function to update ledger entry
function update_ledger_entry($entry_id, $entry_date, $entry_type, $amount, $description, $store_id) {
    global $db;
    
    $sql = "UPDATE ledger SET 
            date = '{$entry_date}', 
            type = '{$entry_type}', 
            amount = '{$amount}', 
            description = '{$description}', 
            store_id = '{$store_id}'
            WHERE id = '{$entry_id}'";
    
    return $db->query($sql);
}

// Function to delete ledger entry
function delete_ledger_entry($entry_id) {
    global $db;
    
    $sql = "DELETE FROM ledger WHERE id = '{$entry_id}'";
    return $db->query($sql);
}

// Function to get all ledger entries with filters - FIXED: Using correct column names
function get_all_ledger_entries($start_date = null, $end_date = null, $store_id = null) {
    global $db;
    
    // First, let's check what columns exist in the stores table
    $test_sql = "SHOW COLUMNS FROM stores";
    $columns = find_by_sql($test_sql);
    $store_name_column = 'name'; // default
    
    foreach($columns as $col) {
        if($col['Field'] == 'store_name') {
            $store_name_column = 'store_name';
            break;
        }
    }
    
    $sql = "SELECT l.*, s.{$store_name_column} as store_name, u.username 
            FROM ledger l 
            LEFT JOIN stores s ON l.store_id = s.id 
            LEFT JOIN users u ON l.user_id = u.id 
            WHERE 1=1";
    
    if($start_date) {
        $sql .= " AND DATE(l.date) >= '{$start_date}'";
    }
    
    if($end_date) {
        $sql .= " AND DATE(l.date) <= '{$end_date}'";
    }
    
    if($store_id) {
        $sql .= " AND l.store_id = '{$store_id}'";
    }
    
    $sql .= " ORDER BY l.date DESC, l.id DESC";
    
    return find_by_sql($sql);
}

// Function to get ledger entry by ID
function get_ledger_entry_by_id($entry_id) {
    global $db;
    
    $sql = "SELECT * FROM ledger WHERE id = '{$entry_id}'";
    $result = find_by_sql($sql);
    return count($result) > 0 ? $result[0] : null;
}
?>
<?php include_once('layouts/header.php'); ?>

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

<div class="row">
    <div class="col-md-6">
        <!-- Add/Edit Ledger Entry Form -->
        <div class="panel panel-default">
            <div class="panel-heading">
                <strong>
                    <span class="glyphicon glyphicon-plus"></span>
                    <span><?php echo isset($_GET['edit_ledger_id']) ? 'Edit' : 'Add New'; ?> Ledger Entry</span>
                </strong>
            </div>
            <div class="panel-body">
                <?php 
                $editing_entry = null;
                if(isset($_GET['edit_ledger_id'])) {
                    $editing_entry = get_ledger_entry_by_id((int)$_GET['edit_ledger_id']);
                }
                ?>
                <form method="post" action="ledger.php">
                    <?php if($editing_entry): ?>
                        <input type="hidden" name="entry_id" value="<?php echo $editing_entry['id']; ?>">
                    <?php endif; ?>
                    
                    <div class="form-group">
                        <label>Date</label>
                        <input type="date" class="form-control" name="entry_date" 
                               value="<?php echo $editing_entry ? $editing_entry['date'] : date('Y-m-d'); ?>" required>
                    </div>
                    
                    <div class="form-group">
                        <label>Store</label>
                        <select class="form-control" name="store_id" required>
                            <option value="">Select Store</option>
                            <?php foreach ($all_stores as $store): ?>
                                <option value="<?php echo (int)$store['id']; ?>" 
                                    <?php 
                                    if($editing_entry && $store['id'] == $editing_entry['store_id']) echo 'selected';
                                    elseif(!$editing_entry && $store['id'] == $store_id) echo 'selected';
                                    ?>>
                                    <?php echo $store['name'] ?? $store['store_name']; ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    
                    <div class="form-group">
                        <label>Entry Type</label>
                        <select class="form-control" name="entry_type" required>
                            <option value="">Select Type</option>
                            <option value="expense" <?php echo ($editing_entry && $editing_entry['type'] == 'expense') ? 'selected' : ''; ?>>Expense</option>
                            <option value="income" <?php echo ($editing_entry && $editing_entry['type'] == 'income') ? 'selected' : ''; ?>>Other Income</option>
                        </select>
                    </div>
                    
                    <div class="form-group">
                        <label>Amount (₹)</label>
                        <input type="number" class="form-control" name="amount" step="0.01" min="0" 
                               value="<?php echo $editing_entry ? $editing_entry['amount'] : ''; ?>" required placeholder="Enter amount">
                    </div>
                    
                    <div class="form-group">
                        <label>Description</label>
                        <textarea class="form-control" name="description" rows="3" required placeholder="Enter description"><?php echo $editing_entry ? $editing_entry['description'] : ''; ?></textarea>
                    </div>
                    
                    <?php if($editing_entry): ?>
                        <button type="submit" name="edit_ledger_entry" class="btn btn-warning">Update Entry</button>
                        <a href="ledger.php" class="btn btn-default">Cancel</a>
                    <?php else: ?>
                        <button type="submit" name="add_ledger_entry" class="btn btn-primary">Add Entry</button>
                    <?php endif; ?>
                </form>
            </div>
        </div>
    </div>
    
    <div class="col-md-6">
        <!-- Filter Ledger Entries -->
        <div class="panel panel-default">
            <div class="panel-heading">
                <strong>
                    <span class="glyphicon glyphicon-filter"></span>
                    <span>Filter Ledger Entries</span>
                </strong>
            </div>
            <div class="panel-body">
                <form method="post" action="ledger.php">
                    <div class="form-group">
                        <label>Store</label>
                        <select class="form-control" name="store_id">
                            <option value="">All Stores</option>
                            <?php foreach ($all_stores as $store): ?>
                                <option value="<?php echo (int)$store['id']; ?>" <?php echo ($store['id'] == $store_id) ? 'selected' : ''; ?>>
                                    <?php echo $store['name'] ?? $store['store_name']; ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    
                    <div class="row">
                        <div class="col-md-6">
                            <div class="form-group">
                                <label>Start Date</label>
                                <input type="date" class="form-control" name="start_date" value="<?php echo $start_date; ?>">
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="form-group">
                                <label>End Date</label>
                                <input type="date" class="form-control" name="end_date" value="<?php echo $end_date; ?>">
                            </div>
                        </div>
                    </div>
                    
                    <button type="submit" name="filter_ledger" class="btn btn-success btn-block">
                        <span class="glyphicon glyphicon-filter"></span> Apply Filter
                    </button>
                    <a href="ledger.php" class="btn btn-default btn-block">Reset Filter</a>
                </form>
            </div>
        </div>
        
        <!-- Quick Stats -->
        <div class="panel panel-default">
            <div class="panel-heading">
                <strong>
                    <span class="glyphicon glyphicon-dashboard"></span>
                    <span>Ledger Summary</span>
                </strong>
            </div>
            <div class="panel-body">
                <div class="list-group">
                    <div class="list-group-item">
                        <span class="badge"><?php echo count($ledger_entries); ?></span>
                        Total Entries
                    </div>
                    <div class="list-group-item">
                        <span class="badge text-success">₹<?php 
                            $total_income = 0;
                            foreach($ledger_entries as $entry) {
                                if($entry['type'] == 'income') $total_income += $entry['amount'];
                            }
                            echo number_format($total_income, 2);
                        ?></span>
                        Total Income
                    </div>
                    <div class="list-group-item">
                        <span class="badge text-danger">₹<?php 
                            $total_expenses = 0;
                            foreach($ledger_entries as $entry) {
                                if($entry['type'] == 'expense') $total_expenses += $entry['amount'];
                            }
                            echo number_format($total_expenses, 2);
                        ?></span>
                        Total Expenses
                    </div>
                    <div class="list-group-item">
                        <span class="badge">₹<?php 
                            $net_balance = $total_income - $total_expenses;
                            echo number_format($net_balance, 2);
                        ?></span>
                        Net Balance
                    </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-list"></span>
                    <span>All Ledger Entries</span>
                </strong>
            </div>
            <div class="panel-body">
                <div class="table-responsive">
                    <table class="table table-bordered table-striped table-hover">
                        <thead>
                            <tr class="info">
                                <th class="text-center">#</th>
                                <th class="text-center">Date</th>
                                <th class="text-center">Store</th>
                                <th class="text-center">Type</th>
                                <th class="text-center">Amount</th>
                                <th class="text-center">Description</th>
                                <th class="text-center">Added By</th>
                                <th class="text-center">Actions</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if(count($ledger_entries) > 0): ?>
                                <?php foreach($ledger_entries as $entry): ?>
                                    <tr>
                                        <td class="text-center"><?php echo $entry['id']; ?></td>
                                        <td class="text-center"><?php echo date('d-m-Y', strtotime($entry['date'])); ?></td>
                                        <td class="text-center"><?php echo $entry['store_name']; ?></td>
                                        <td class="text-center">
                                            <span class="label label-<?php echo $entry['type'] == 'income' ? 'success' : 'danger'; ?>">
                                                <?php echo ucfirst($entry['type']); ?>
                                            </span>
                                        </td>
                                        <td class="text-right <?php echo $entry['type'] == 'income' ? 'text-success' : 'text-danger'; ?>">
                                            <strong>₹<?php echo number_format($entry['amount'], 2); ?></strong>
                                        </td>
                                        <td><?php echo $entry['description']; ?></td>
                                        <td class="text-center"><?php echo $entry['username']; ?></td>
                                        <td class="text-center">
                                            <div class="btn-group">
                                                <a href="ledger.php?edit_ledger_id=<?php echo (int)$entry['id']; ?>" 
                                                   class="btn btn-warning btn-xs" 
                                                   title="Edit Entry" 
                                                   data-toggle="tooltip">
                                                    <span class="glyphicon glyphicon-edit"></span>
                                                </a>
                                                <a href="ledger.php?delete_ledger_id=<?php echo (int)$entry['id']; ?>" 
                                                   class="btn btn-danger btn-xs" 
                                                   title="Delete Entry" 
                                                   data-toggle="tooltip"
                                                   onclick="return confirm('Are you sure you want to delete this ledger entry?')">
                                                    <span class="glyphicon glyphicon-trash"></span>
                                                </a>
                                            </div>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php else: ?>
                                <tr>
                                    <td colspan="8" class="text-center">No ledger entries found.</td>
                                </tr>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<?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