EVOLUTION-NINJA

Edit File: payment_abstract.php

<?php echo view('includes/sidebar'); ?>
<script type="text/javascript" src="<?php echo base_url('public/assets/js/jquery-3.6.0.min.js'); ?>"></script>
<script src="<?php echo base_url('public/assets/toastr/toastr.min.js');?>"></script>
<link rel="stylesheet" href="<?php echo base_url('public/assets/toastr/toastr.min.css');?>">
<link rel="stylesheet" href="https://cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/html2canvas/1.4.1/html2canvas.min.js"></script>

<link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/jquery.dataTables.min.css">
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>

th,
    td {
        padding: 10px;
        border: 1px solid #ddd;
    }

th {
        background-color: #f4f4f4;
        font-weight: bold;
       
    }

tr:nth-child(even) {
        background-color: #f9f9f9;
    }

/* Make the table scrollable horizontally */
    .table-container {
        width: 100%;
        overflow-x: auto;
    }

/* Styling for totals and grand total row */
    .total-row {
        font-weight: bold;
        background-color: #e0e0e0;
    }

.heading {
        text-align: center;
        font-size: 22px;
        margin-left: -150px;
        font-weight: bold;
        color: #4B0B0B;
        /*border:none !important;*/
    }

.fieldsbox {
        width: 93%
    }

/* Additional styles for responsive view */
    @media screen and (max-width: 768px) {

th,
        td {
            font-size: 14px;
            padding: 8px;
        }

.total-row th,
        .total-row td {
            font-size: 14px;
            padding: 8px;
        }

.fieldsbox {
            width: 100%
        }
    }

.table-container th {
        position: sticky;
        top: 0;
        background-color: #f4f4f4;
        z-index: 2;
         padding-bottom:30px;
    }

#reportTable {
        width: 90%;
        margin:auto;

}

#reportTable,
    th,
    td {
        border-collapse: collapse;

}

#reportTable td {
        padding: 10px 10px;
        border: 1px solid black;

}

#reportTable th {
        padding: 0px 10px;
        border: 1px solid black;
        font-size: 13px;
          vertical-align: middle;

}

#reportTable .width {
        padding: 0px 120px;
    }
</style>

<section class="home-section fullpage"> 
    <div class="home-content">
        <i class='bx bx-menu'></i>
        
    </div>
    <div class="row m-0">
        <div class="col-sm-12">
            <h1 class="crt-pjt">Payment Abstract Report</h1>
        </div>
    </div>
    <form id="report">
        <div class="row m-0">
            <div class="constuency_page">
                <div class="constuencydetails">

<div class="constuencydetail row">
                        <div class="col-sm-4">
                            <label for="constituency" class="form-label inpfieldname">Constituency Name</label>
                            <select class="form-select form-control fieldsbox" id="constituency" name="constituency">
                                <option value=""></option>
                            </select>
                        </div>
                        <div class="col-sm-4">
                            <label for="mla" class="form-label inpfieldname">MLA</label>
                            <select class="form-select form-control fieldsbox" id="mla" name="mla">
                                <option value=""></option>
                            </select>
                        </div>
                        
                   
                   
                   
                   
                   
                   
                    
                    
                   
                   
                   
                   
                   
                   
                        <div class="col-sm-4">
                            <label for="department" class="form-label inpfieldname">Department</label>
                            <select class="form-select form-control fieldsbox" id="department" name="department">
                                <option value=""></option>
                            </select>
                        </div>
                        
                       <div class="col-sm-4">
                            <label for="engineer" class="form-label inpfieldname">Fund Wise</label><br>
                            <select class="form-select form-control fieldsbox" aria-label="Default select example"
                                id="Fund"  name="fund">
                                <option value=""></option>
                            </select>
                        </div>

<div class="col-sm-4">
                            <label for="year" class="form-label inpfieldname">Financial Year</label>
                            <select class="form-select form-control fieldsbox" id="year" name="year">
                                <option value="">Select</option>
                                
                                <option value="2028-29">2028-29</option>
                                <option value="2027-28">2027-28</option>
                                <option value="2026-27">2026-27</option>
                                <option value="2025-26">2025-26</option>
                                <option value="2024-25">2024-25</option>
                                <option value="2023-24">2023-24</option>
                                <option value="2022-23">2022-23</option>
                                <option value="2021-22">2021-22</option>
                                <option value="2020-21">2020-21</option>
                                <option value="2019-20">2019-20</option>
                                <option value="2018-19">2018-19</option>
                                <option value="2017-18">2017-18</option>
                                <option value="2016-17">2016-17</option>
                                <option value="2015-16">2015-16</option>
                                <option value="2014-15">2014-15</option>
                            </select>
                        </div>
                        <div class="col-sm-4">
                            <label for="Apm" class="form-label inpfieldname">APM</label><br>
                            <select class="form-select form-control fieldsbox" aria-label="Default select example"
                                id="apm" name="apm">
                                <option value=""></option>
                            </select>
                        </div>
                    </div>

<div class="constuencydetail row">
                       
                        <div class="col-sm-4">
                            <label for="engineer" class="form-label inpfieldname">Engineer</label><br>
                            <select class="form-select form-control fieldsbox" aria-label="Default select example"
                                id="Engineer" name="engineer">
                                <option value=""></option>
                            </select>
                        </div>
                        
                        
                          <div class="col-sm-4">
                        <label for="engineer" class="form-label inpfieldname">Status</label><br>
                         <select class="form-select form-control fieldsbox" aria-label="Default select example"
                                id="status"  name="Status">
                                <option value="">All</option>
                                <option value="1">Progress</option>  
                                <option value="2">Not started</option>
                                <option value="3">completed</option>
                                
                           <?php if (session('role') == 0): ?>
                              <option value="4">hide</option>
                            <?php endif; ?>                                             
                        </select>
                   </div>
                   <div class="col-sm-4">
                        <label for="engineer" class="form-label inpfieldname">Date</label><br>
                       <input type="text" class="form-control fieldsbox" id="date" name="date" onchange="updateYearChange()"  placeholder="dd/mm/yy">
                   </div>
                   
                   
                    </div>
           
                </div>
            </div>

<div class="col-sm-6 buttns-cnl">
                <button type="submit" class="submit" style="width:200px">Get report</button>
            </div>
        </div>
    </form>

<?php
$currentMonth = date('n'); // 1–12
$currentYear  = date('Y');

if ($currentMonth >= 4) {
    // April to December → current year - next year
    $financialYear = $currentYear . '-' . substr($currentYear + 1, -2);
} else {
    // January to March → previous year - current year
    $financialYear = ($currentYear - 1) . '-' . substr($currentYear, -2);
}
?>

<div class=" mt-4 ml-3 mr-3">
        <button id="printBtn" class="btn btn-primary">Print PDF</button>
         <button id="printExcel" class="btn btn-primary">Export To Excel</button>
        <div class="table-container" id="reportContainer">

<table class="table mt-3" id="reportTable" class="display">
                <div id="firstPagePrintHeader">
    <div style="text-align:center;font-size:20px;font-weight:bold;">
        Mysore Nirmithi Kendra
    </div>
    <div style="text-align:center;font-size:14px;font-weight:600;">
        <span id="yearchange"></span> ಸಾಲಿನಲ್ಲಿ ಕೈಗೊಳ್ಳುತ್ತಿರುವ ಕಾಮಗಾರಿಗಳ ಘೋಷ್ವಾರ 
        <span id="monthChange"></span>
    </div>
</div>

<tr>
                        <th rowspan="2">SL NO</th>
                        <th class="width" rowspan="2">Department and Taluk</th>
                        <th rowspan="2" class="hide-print"></th>
                      
                        <th rowspan="2">No Of Works</th>
                        <th>Estimated Amount</th>
                        <th>Previous Year Amount Release</th>
                        <th> <span id="yearHeading"><?php echo $financialYear ?></span><br>Amount Release</th>
                        <th>Total Release</th>
                        <th>Expenditure</th>
                        <th>Payment</th>
                        <th colspan="4" class="text-center">Stage Of Work </th>
                        
                         <th></th>
                    </tr>
                    <tr>
                        
                        
                        
                        <th colspan="6" style="text-align:center">Lakhs in Rupees</th>
                            
                        <th>Fully Completed</th> 
                        <th>Completed Against Amount Released</th>
                        <th>Progress</th>
                        <th>Not Started</th>
                        <th >Excess Payment</th>
         
                        
                        
                  
                        
                    </tr>
                    
                </thead>
                <tbody id="reportTableBody">
                    
                </tbody>

</table>
        </div>

</div>

</div>
 <script>
const userRole = <?php echo (int) session()->get('role'); ?>;
$(document).ready(function () {

function fetchReport(formData = null) {
        $.ajax({
            url: "<?php echo base_url('get_report_cash'); ?>",
            type: 'POST',
            data: formData ? formData : {},
            processData: formData ? false : true,
            contentType: formData ? false : 'application/x-www-form-urlencoded; charset=UTF-8',
            dataType: 'json',
            success: function (response) {
                if (response.result === 1) {
                    $('#reportTableBody').empty();
                    $('#totalAmount, #previousYearAmount, #presentYearAmount, #totalReleaseAmount, #totalExpenses, #totalPayment, #notStartedCount, #workingInProgressCount, #physicallyCompletedCount, #financiallyCompletedCount, #releasedAmountAgainstCompleted, #excess_payment').text('0');

let totalsByDepartment = {};
                    let grandTotal = {
                        totalOverall: 0,
                        totalReleaseOverall: 0,
                        totalExpenseOverall: 0,
                        totalPaymentOverall: 0,
                        totalPreviousOverall: 0,
                        totalPresentOverall: 0,
                        workingInProgressOverall: 0,
                        physicallycompletedOverall: 0,
                        releasedAmountAgainstCompletedOverall: 0,
                        notStartedOverall: 0,
                        totalSerialValue: 0,
                        totalExcessPayment: 0
                    };

let serialCounter = 1;

$.each(response.table, function (index, project) {
                        let p_amount = parseFloat(project.total_released_amount) || 0;
                        let present_year_released = parseFloat(project.present_year_released) || 0;
                        let previous_year_released = p_amount - present_year_released;

let releaseAmount = previous_year_released + present_year_released; // total released
                        let payment = parseFloat(project.total_cash) || 0; // total payment
                        let expenses = parseFloat(project.total_expenditure) || 0;
                        let amount = parseFloat(project.Total_amount) || 0;
                        let workingStage = project.status ? project.status : '';
                        let serialNumberValue = parseInt(project.sl_no) || 0;
                        const releasedAgainstCompleted = parseInt(project.released_against_completed);

let excess_payment = (payment > releaseAmount) ? (releaseAmount - payment) : 0;

// Count statuses
                        if (releasedAgainstCompleted === 1) {
                            grandTotal.releasedAmountAgainstCompletedOverall += serialNumberValue;
                        } else if (workingStage === '3') {
                            grandTotal.physicallycompletedOverall += serialNumberValue;
                        } else if (workingStage === '1') {
                            grandTotal.workingInProgressOverall += serialNumberValue;
                        } else if (workingStage === '2') {
                            grandTotal.notStartedOverall += serialNumberValue;
                        }

// Initialize department group if not exists
                        if (!totalsByDepartment[project.department_name]) {
                            totalsByDepartment[project.department_name] = {
                                department_id: project.department_id,
                                amount: 0,
                                previous_year_released: 0,
                                present_year_released: 0,
                                releaseAmount: 0,
                                expenses: 0,
                                payment: 0,
                                serialNumberValue: 0,
                                notStarted: 0,
                                workingInProgress: 0,
                                physicallycompleted: 0,
                                financiallycompleted: 0,
                                releasedAgainstCompleted: 0,
                                excess_payment: 0
                            };
                        }

let dept = totalsByDepartment[project.department_name];
                        dept.amount += amount;
                        dept.previous_year_released += previous_year_released;
                        dept.present_year_released += present_year_released;
                        dept.releaseAmount += releaseAmount;
                        dept.expenses += expenses;
                        dept.payment += payment;
                        dept.serialNumberValue += serialNumberValue;
                        dept.excess_payment += excess_payment;

if (releasedAgainstCompleted === 1) {
                            dept.releasedAgainstCompleted += serialNumberValue;
                        } else if (workingStage === '3') {
                            dept.physicallycompleted += serialNumberValue;
                        } else if (workingStage === '1') {
                            dept.workingInProgress += serialNumberValue;
                        } else if (workingStage === '2') {
                            dept.notStarted += serialNumberValue;
                        }

// Update grand totals
                        grandTotal.totalOverall += amount;
                        grandTotal.totalReleaseOverall += releaseAmount;
                        grandTotal.totalExpenseOverall += expenses;
                        grandTotal.totalPaymentOverall += payment;
                        grandTotal.totalPreviousOverall += previous_year_released;
                        grandTotal.totalPresentOverall += present_year_released;
                        grandTotal.totalSerialValue += serialNumberValue;
                        grandTotal.totalExcessPayment += excess_payment;
                    });

// Render department rows
                    $.each(totalsByDepartment, function (departmentName, totals) {
                        let departmentId = totals.department_id;
                        let newRow = `
                            <tr>
                                <td>${serialCounter++}</td> 
                                <td>${departmentName}</td>
                                <td class="hide-print"><button class="btn btn-link viewTalukBtn" data-id="${departmentId}">View All Works</button></td>
                                <td>${totals.serialNumberValue}</td>
                                <td>${totals.amount.toFixed(2)}</td>
                                <td>${totals.previous_year_released.toFixed(2)}</td>
                                <td>${totals.present_year_released.toFixed(2)}</td>
                                <td>${totals.releaseAmount.toFixed(2)}</td>
                                <td>${totals.expenses.toFixed(2)}</td>
                                <td>${totals.payment.toFixed(2)}</td>
                                <td>${totals.physicallycompleted}</td>
                                <td>${totals.releasedAgainstCompleted}</td>
                                <td>${totals.workingInProgress}</td>
                                <td>${totals.notStarted}</td>
                                <td>${totals.excess_payment.toFixed(2)}</td>
                            </tr>
                        `;
                        $('#reportTableBody').append(newRow);
                    });

// Grand total row
                    let grandTotalRow = `
                        <tr style="font-weight: bold;">
                            <th colspan="2">Total</th>
                            <th class="hide-print"></th>
                            <th>${grandTotal.totalSerialValue}</th>
                            <th>${grandTotal.totalOverall.toFixed(2)}</th>
                            <th>${grandTotal.totalPreviousOverall.toFixed(2)}</th>
                            <th>${grandTotal.totalPresentOverall.toFixed(2)}</th>
                            <th>${grandTotal.totalReleaseOverall.toFixed(2)}</th>
                            <th>${grandTotal.totalExpenseOverall.toFixed(2)}</th>
                            <th>${grandTotal.totalPaymentOverall.toFixed(2)}</th>
                            <th>${grandTotal.physicallycompletedOverall}</th>
                            <th>${grandTotal.releasedAmountAgainstCompletedOverall}</th>
                            <th>${grandTotal.workingInProgressOverall}</th>
                            <th>${grandTotal.notStartedOverall}</th>
                            <th>${grandTotal.totalExcessPayment.toFixed(2)}</th>
                        </tr>
                    `;
                    $('#reportTableBody').append(grandTotalRow);

// Update summary fields
                    $('#totalAmount').text(grandTotal.totalOverall.toFixed(2));
                    $('#totalReleaseAmount').text(grandTotal.totalReleaseOverall.toFixed(2));
                    $('#previousYearAmount').text(grandTotal.totalPreviousOverall.toFixed(2));
                    $('#presentYearAmount').text(grandTotal.totalPresentOverall.toFixed(2));
                    $('#totalExpenses').text(grandTotal.totalExpenseOverall.toFixed(2));
                    $('#totalPayment').text(grandTotal.totalPaymentOverall.toFixed(2));
                    $('#completedCount').text(grandTotal.physicallycompletedOverall);
                    $('#workingInProgressCount').text(grandTotal.workingInProgressOverall);
                    $('#releasedAmountAgainstCompleted').text(grandTotal.releasedAmountAgainstCompletedOverall);
                    $('#notStartedCount').text(grandTotal.notStartedOverall);
                    $('#excess_payment').text(grandTotal.totalExcessPayment.toFixed(2));
                } else {
                    alert(response.message);
                }
            },
            error: function (xhr, status, error) {
                console.error('AJAX error:', error);
            }
        });
    }

fetchReport();

$('#report').submit(function (e) {
                e.preventDefault();

var formData = new FormData(this);

fetchReport(formData);
            });
                // Print functionality
        $('#printBtn').click(function() {
            printReport();
        });

$('#printExcel').click(function () {
        generateExcel();
    });
    
    function generateExcel() {
        const worksheet = XLSX.utils.table_to_sheet(document.getElementById('reportTable'));
        const wb = XLSX.utils.book_new();
        XLSX.utils.book_append_sheet(wb, worksheet, "Report");
        XLSX.writeFile(wb, "report.xlsx");
    }
    
        function printReport() {
            // Create a new window
            const printWindow = window.open('', '_blank');
            const reportContent = $('#reportContainer').html(); // Get the HTML of the report

printWindow.document.write(`
                <html>
                    <head>
                        <title>Report</title>
                        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
                                <style>
                            body { font-family: Arial, sans-serif; margin:0 5px !important; }
                            .heading { font-size: 20px; font-weight: bold; text-align: center; margin-bottom: 20px; }
                            table { width: 100%; border-collapse: collapse; }
                            th, td { padding:2px 2px !important; text-align:center; border:1px solid black !important; vertical-align: middle !important; }
                            th {padding:0px 2px !important;font-size:15px !important;  }
                            .width{padding:0px 30px !important;} 
                         
                          /* Landscape orientation for printing */
                   @media print
{ /* Hide the original heading rows everywhere */ 
.print-first-page-only { display: none !important; } /* Create a printed-only header that appears only on page 1 */ 
#firstPageHeader { display: table-header-group; } /* Prevent table thead from repeating */
thead { display: table-header-group; } @page { size: landscape; margin: 10mm; } 
    
}

@media print {
    .hide-print {
        display: none !important;
    }
}

</style>
                    </head>
                    <body>
                        <div class="container mt-4 ml-1">
                         
                            <div class="table-container">${reportContent}</div>
                        </div>
                    </body>
                </html>
            `);

printWindow.document.close(); // Close the document to complete the rendering
            printWindow.onload = function() {
                printWindow.print(); // Trigger print when the document is fully loaded
                printWindow.close(); // Close the print window after printing
            };
        }
       });
   
    </script>

// Validate input format (dd/mm/yy)
    const dateParts = dateInput.split('/');
    if (dateParts.length === 3) {
        const day = parseInt(dateParts[0], 10);
        const month = parseInt(dateParts[1], 10) - 1; // Month is zero-indexed in JavaScript
        const year = parseInt( dateParts[2], 10); // Prefix '20' for 2-digit year
        
        const date = new Date(year, month, day);

// Format month and year
        const monthName = date.toLocaleString('kn-IN', { month: 'long' }); // Kannada month name
        const yearFull = date.getFullYear();
        
        document.getElementById('monthChange').textContent = `(${monthName} - ${yearFull})`;
    } else {
        // alert('Please enter a valid date in dd/mm/yy format');
    }
}
</script>

</script>

if (!$('#year').val()) {
            $('#yearchange').text(fiscalYear);
        }

$('#year').change(function () {
            const selectedYear = parseInt($(this).val());
            const selectedMonth = currentMonth;

let fiscalSelectedYear;
            if (selectedMonth >= 4) {
         
                fiscalSelectedYear = `${selectedYear}-${selectedYear + 1}`;
            } else {
       
                fiscalSelectedYear = `${selectedYear - 1}-${selectedYear}`;
            }

$('#yearchange').text(fiscalSelectedYear);
        });
    });
</script>

});

</script>

$.ajax({
                url: '<?php echo base_url("fetch-constituency"); ?>',
                method: 'GET',
                success: function (response) {
                    if (response.length > 0) {
                        var options = '<option value="">Select Constituency</option>';
                        $.each(response, function (key, value) {
                            options += `<option value="${value.id}">${value.constituency_name}</option>`;
                        });
                        $('#constituency').html(options);
                    } else {
                        console.log('No constituency found');
                    }
                },
                error: function (xhr, status, error) {
                    console.error("Error fetching constituency:", error);
                }
            });
        });
    </script>

$.ajax({
                url: '<?php echo base_url("fetch-department"); ?>',
                method: 'GET',
                success: function (response) {

if (response.length > 0) {
                        var options = '<option value="">Select Department</option>';

$.each(response, function (key, value) {
                            options += `<option value="${value.id}">${value.department_name}</option>`;
                        });
                        $('#department').html(options);
                    } else {
                        console.log('No department found');
                    }
                },
                error: function (xhr, status, error) {
                    console.error("Error fetching department:", error);
                }
            });
        });

</script>

$.ajax({
                url: '<?php echo base_url("fetch-mla"); ?>',
                method: 'GET',
                success: function (response) {
                    if (response.length > 0) {
                        var options = '<option value="">Select </option>';
                        $.each(response, function (key, value) {
                            options += `<option value="${value.id}">${value.mla}</option>`;
                        });
                        $('#mla').html(options);
                    } else {
                        console.log('No mla found');
                    }
                },
                error: function (xhr, status, error) {
                    console.error("Error fetching mla:", error);
                }
            });
        });
    </script>

$.ajax({
                url: '<?php echo base_url("fetch-apm"); ?>',
                method: 'GET',
                success: function (response) {

if (response.length > 0) {
                        var options = '<option value="">Select APM</option>';

$.each(response, function (key, value) {
                            options += `<option value="${value.id}">${value.username}</option>`;
                        });
                        $('#apm').html(options);
                    } else {
                        console.log('No apm found');
                    }
                },
                error: function (xhr, status, error) {
                    console.error("Error fetching apm:", error);
                }
            });
        });
    </script>

$.ajax({
                url: '<?php echo base_url("fetch-engineer"); ?>',
                method: 'GET',
                success: function (response) {

if (response.length > 0) {
                        var options = '<option value="">Select Engineer</option>';

$.each(response, function (key, value) {
                            options += `<option value="${value.id}">${value.username}</option>`;
                        });
                        $('#Engineer').html(options);
                    } else {
                        console.log('No Engineer found');
                    }
                },
                error: function (xhr, status, error) {
                    console.error("Error fetching engineer:", error);
                }
            });
        });

</script>
    
    <script>
    $(document).ready(function () {
   
    $.ajax({
        url: '<?php echo base_url("fetch-fund"); ?>', 
        method: 'GET',
        success: function (response) {
          
            if (response.length > 0) {
                var options = '<option value="">Select fund</option>'; 
               
                $.each(response, function (key, value) {
                    options += `<option value="${value.id}">${value.fund_name}</option>`;
                });
                $('#Fund').html(options); 
            } else {
               console.log('No fund found');
            }
        },
        error: function (xhr, status, error) {
            console.error("Error fund engineer:", error);
        }
    });
});

</script>