EVOLUTION-NINJA

Edit File: report_details.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://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.4/xlsx.full.min.js"></script>

<style>

table {
        width: 90%;
        border-collapse: collapse;
        text-align: left;
    }

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

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

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

.table-container {
               width: 91%;
    margin: auto;
    overflow-x: auto;
    height: 552px;
    overflow-y: scroll;
    border: 1px solid #999;
    }

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

h4{
      text-align:center;
        font-size:20px;
        margin-bottom:30px;
     margin-left:-150px;
        font-weight:bold;
        color:#4B0B0B;
    }

.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 header fixed while scrolling */
    /*.table-container th {*/
    /*    position: sticky;*/
    /*    top: 0;*/
    /*    background-color: #f4f4f4;*/
    /*    z-index: 2;*/
    /*}*/
#reportTable{
  width: 100%;
    margin: auto;
    /*  overflow-y: auto;*/
    /*max-height: 400px;*/
    
  
}
#reportTable, th, td {
  border-collapse: collapse;

}
#reportTable td{
padding:5px 10px;
border: 1px solid black;
font-size: 15px;
vertical-align: middle !important;

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

}

/*#reportTable .width{*/
/*   padding:0px 120px !important;*/
/*}*/

/*#reportTable .reduced-width{*/
/*      padding:0px 0px !important;*/

/*}*/
  .heading{
      text-align:center;
        font-size:22px;
      margin-left:-150px;
        font-weight:bold;
        color:#4B0B0B;
        /*border:none !important;*/
    }
</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">Reports</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/ MLC/MP</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>

<div class="constuencydetail row">
                
                    <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>
                        </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 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>
                 <div class="constuencydetail row">
                        <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="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="2">Progress</option>
                                <option value="3">Action plan</option>
                        </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>

<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 mt-4" id="reportContainer">
<table class="table mt-3" id="reportTable">
    <thead>
        <tr>
           <td colspan="17" class="heading">Nirman Vision</td>
         </tr>
        <tr>
         <td colspan="17" style="text-align:center;font-size: 14px;font-weight: 600;padding: 6px;">
          <span id="yearchange"></span> ಸಾಲಿನಲ್ಲಿ ಕೈಗೊಳ್ಳುತ್ತಿರುವ ಕಾಮಗಾರಿಗಳ ಘೋಷ್ವಾರ<span id="monthChange"></span></td>
      
          </tr>
        <tr>
            <th rowspan="2">SL NO</th>
            <th class="project-column width text-center" rowspan="2" >Work Details</th>
             <th class="reduced-width" rowspan="2">ADM Details</th>
            <th class="reduced-width" rowspan="2">No Of Works</th>
            <th class="reduced-width">ADM Amount</th>
             
            <th class="reduced-width">Previous Year  Amount Release </th>
            <th class="reduced-width">Present Year Amount Release </th>
            <th class="reduced-width">Total Release</th>
            <th class="reduced-width">Expenditure</th>
            <th colspan="3" class="reduced-width text-center">Stage Of Work </th>
            <th class="reduced-width width text-center" rowspan="2" >Remarks</th>
        </tr>
        <tr>
            
            
            
            <th colspan="5" class="text-center">Lakhs in Rupees</th>
            <th>completed</th>
            <th>Progress</th>
            <th>Not Started</th>

</tr>
    </thead>
    <tbody id="reportTableBody">
        
    </tbody>
</table>

</div>
</div>

</div>
<script>
$(document).ready(function () {
    function fetchReport(formData = null) {
        // Reset the report data and totals
        $('#reportTableBody').empty();
        $('#totalAmount').text('0');
        $('#totalReleaseAmount').text('0');
        $('#totalExpenses').text('0');
        $('#notStartedCount').text('0');
        $('#workingInProgressCount').text('0');
        $('#physicallyCount').text('0');
        $('#financiallyCount').text('0');
        $('#lastReleasedAmountTotal').text('0');
    
        $('#totalSlNo').text('0');
        $('#totalPresentYearRelease').text('0');
        $('#totalPreviousYearRelease').text('0');

let ajaxOptions = {
            url: "<?php echo base_url('get_report'); ?>",
            type: 'POST',
            dataType: 'json',
            success: function (response) {
                console.log(response);

if (response.result === 1) {
                    let departmentGroups = {};

// Group data by department, constituency, financial year, and funds
                    $.each(response.table, function (index, row) {
                        const departmentName = row.department_name;
                        const currentConstituencyName = row.constituency_name;
                        const financialYear = row.financial_year;
                       const funds = row.fund_name ? row.fund_name : '';

if (!departmentGroups[departmentName]) {
                            departmentGroups[departmentName] = {};
                        }

if (!departmentGroups[departmentName][currentConstituencyName]) {
                            departmentGroups[departmentName][currentConstituencyName] = {};
                        }

if (!departmentGroups[departmentName][currentConstituencyName][financialYear]) {
                            departmentGroups[departmentName][currentConstituencyName][financialYear] = {};
                        }

if (!departmentGroups[departmentName][currentConstituencyName][financialYear][funds]) {
                            departmentGroups[departmentName][currentConstituencyName][financialYear][funds] = [];
                        }

departmentGroups[departmentName][currentConstituencyName][financialYear][funds].push(row);
                    });

let totalOverall = 0, totalReleaseOverall = 0, totalExpenseOverall = 0;
                    let presentYearTotalReleaseOverall = 0, previousYearTotalReleaseOverall = 0;
                    let workingInProgressOverall = 0, physicallyOverall = 0,financiallyOverall=0;  notStartedOverall = 0;
                    let totalSlNoOverall = 0 ,  grandTotalLastReleasedAmount=0;
                     let  totalOverallAdm =0 ;

// Loop through each department
                    $.each(departmentGroups, function (departmentName, constituencies) {
                        let departmentHeader = `
                            <tr>
                                <td colspan="16" style="text-align:center"><strong>${departmentName}</strong></td>
                            </tr>
                        `;
                        $('#reportTableBody').append(departmentHeader);

// Loop through each constituency
                        $.each(constituencies, function (currentConstituencyName, financialYears) {
                            let constituencyRow = `
                                <tr>
                                    <td colspan="16" style="text-align:left !important; background-color:#f2f2f2;"><strong>${currentConstituencyName}</strong></td>
                                </tr>
                            `;
                            $('#reportTableBody').append(constituencyRow);

// Loop through each financial year
                            $.each(financialYears, function (financialYear, fundsData) {
                                let financialYearRow = `
                                    <tr>
                                        <td colspan="16" style="text-align:center !important;"><strong> ${financialYear}</strong></td>
                                    </tr>
                                `;
                                $('#reportTableBody').append(financialYearRow);

// Loop through each fund type
                                $.each(fundsData, function (funds, projects) {
                                    let fundRow = `
                                        <tr>
                                            <td colspan="16" style="text-align:left !important;"><strong> ${funds}</strong></td>
                                        </tr>
                                    `;
                                    $('#reportTableBody').append(fundRow);

let total = 0, totalRelease = 0, totalExpense = 0;
                                    let presentYearTotalRelease = 0, previousYearTotalRelease = 0;
                                    let workingInProgressCount = 0, physicallyCount = 0,financiallyCount=0, notStartedCount = 0;
                                    let totalSlNo = 0,mostRecentLastReleasedDate=0 , lastReleasedAmount =0 ,totalAdm=0 , adm_date =0;
                                    let lastReleasedAmountTotal = 0, previousYearReleased = 0  ;
                                   
                                    // Loop through projects
                                    $.each(projects, function (index, project) {
                                        let p_amount = parseFloat(project.total_released_amount) || 0;
                                        let amount = parseFloat(project.Total_amount) || 0;
                                        let presentYearReleased = parseFloat(project.present_year_released) || 0;
                                        // let previousYearReleased = parseFloat(project.previous_year_released) || 0;
                                        let previousYearReleased = p_amount-presentYearReleased;
                                        let releaseAmount = previousYearReleased + presentYearReleased ;
                                        let lastReleasedAmount = parseFloat(project.last_released_amount) || 0;
                                        let lastReleasedDate = project.last_released_date? new Date(project.last_released_date).toLocaleDateString('en-GB') : '';
                                        let adm_date= project.adm_date? new Date(project.adm_date).toLocaleDateString('en-GB') : '';

let expenses = parseFloat(project.total_expenditure) || 0;
                                        let adm_amount = parseFloat(project.adm_amount) || 0;

let newRow = `
                                            <tr>
                                                <td>${index + 1}</td>
                                                <td>${project.project_name}</td>
                                                <td>${adm_date}</td>
                                                <td>${slNo}</td>
                                                <td>${amount.toFixed(2)}</td>
                                                <td>${previousYearReleased.toFixed(2)}</td>
                                                <td>${presentYearReleased.toFixed(2)}</td>
                                                <td>${releaseAmount.toFixed(2)}</td>
                                                <td>${expenses.toFixed(2)}</td>
                                                <td>${physicallyComplete}</td>
                                                <td>${workingInProgress}</td>
                                                <td>${notStarted}</td>
                                                <td>${project.remarks ||''}</td>
                                            </tr>
                                        `;
                                        $('#reportTableBody').append(newRow);

total += amount;
                                        totalAdm += adm_amount;
                                        totalRelease += releaseAmount;
                                        totalExpense += expenses;
                                        totalSlNo += slNo;
                                        presentYearTotalRelease += presentYearReleased;
                                        previousYearTotalRelease += previousYearReleased;
                                        lastReleasedAmountTotal += lastReleasedAmount;

});

let totalsRow = `
                                        <tr>
                                            <th colspan="2"></th>
                                            <th></th>
                                            <th>${totalSlNo}</th>
                                            <th>${total.toFixed(2)}</th>
                                            <th>${previousYearTotalRelease.toFixed(2)}</th>
                                             <th>${presentYearTotalRelease.toFixed(2)}</th>
                                            <th>${totalRelease.toFixed(2)}</th>
                                            <th>${totalExpense.toFixed(2)}</th>
                                             <th>${physicallyCount}</th>
                                            <th>${workingInProgressCount}</th>
                                            <th>${notStartedCount}</th>
                                        
                                           
                                            <td></td>
                                        </tr>
                                    `;
                                    $('#reportTableBody').append(totalsRow);

totalOverall += total;
                                     totalOverallAdm += totalAdm;
                                     totalReleaseOverall += totalRelease;
                                     totalExpenseOverall += totalExpense;
                                     presentYearTotalReleaseOverall += presentYearTotalRelease;
                                     previousYearTotalReleaseOverall += previousYearTotalRelease;
                                     grandTotalLastReleasedAmount += lastReleasedAmountTotal; // Accumulate last released amount
                                     notStartedOverall += notStartedCount;
                                     workingInProgressOverall += workingInProgressCount;
                                     physicallyOverall += physicallyCount;
                                     financiallyOverall += financiallyCount;
                                     totalSlNoOverall += totalSlNo;
                                });
                            });
                        });
                    });

// Display grand totals
                   let grandTotalsRow = `
    <tr style="font-weight:bold;">
        <th colspan="2">Total</th>
        <th></th>
        <th>${totalSlNoOverall}</th>
        <th>${totalOverall.toFixed(2)}</th>
        <th>${previousYearTotalReleaseOverall.toFixed(2)}</th>
         <th>${presentYearTotalReleaseOverall.toFixed(2)}</th>
        <th>${totalReleaseOverall.toFixed(2)}</th>
        <th>${totalExpenseOverall.toFixed(2)}</th>
         <th>${physicallyOverall}</th>
        <th>${workingInProgressOverall}</th>
        <th>${notStartedOverall}</th>

<td></td>
    </tr>
`;
$('#reportTableBody').append(grandTotalsRow);
$('#totalAmount').text(totalOverall.toFixed(2));
$('#totalReleaseAmount').text(totalReleaseOverall.toFixed(2));
$('#totalExpenses').text(totalExpenseOverall.toFixed(2));
$('#notStartedCount').text(notStartedOverall);
$('#workingInProgressCount').text(workingInProgressOverall);
$('#physicallyCount').text(physicallyOverall);
$('#financiallyCount').text(financiallyOverall);
$('#totalSlNo').text(totalSlNoOverall);
$('#totalPresentYearRelease').text(presentYearTotalReleaseOverall.toFixed(2));
$('#totalPreviousYearRelease').text(previousYearTotalReleaseOverall.toFixed(2));
$('#totalAdmAmount').text(totalOverallAdm.toFixed(2)); // Add ADM amount total display
$('#totalLastReleasedAmount').text(grandTotalLastReleasedAmount.toFixed(2));
                } else {
                    alert(response.message);
                }
            },
            error: function (xhr, status, error) {
                console.error('AJAX error:', error);
            }
        };

if (formData) {
            ajaxOptions['data'] = formData;
            ajaxOptions['processData'] = false;
            ajaxOptions['contentType'] = false;
        }

$.ajax(ajaxOptions);
    }
 
    
    fetchReport();

$('#report').submit(function (e) {
        e.preventDefault();
        let formData = new FormData(this);
        fetchReport(formData);
    });
    
          $('#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; max-width:90px !important; vertical-align: middle !important; }
                            th {padding:0px 3px !important;font-size:14px !important; }
                            .width{padding:0px 100px !important; } 
                         /* Landscape orientation for printing */
                          @media print
                                {
                                  @page {
                                      size: landscape;
                                       }
                                }
                         
                         
                          </style>
                    </head>
                    <body>
                        <div class=" mt-4 ml-1 mr-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>
// function updateYearChange() {
//     const dateInput = document.getElementById('date').value;
//     if (dateInput) {
//         const date = new Date(dateInput);
//         const month = date.toLocaleString('kn-IN', { month: 'long' }); // Kannada month name
//         const year = date.getFullYear();
//         document.getElementById('monthChange').textContent = `(${month} - ${year})`;
//     }
// }
// </script>

let fiscalYear;
        if (currentMonth >= 4) {
            // From April to December
            fiscalYear = `${currentYear}-${currentYear + 1}`;
        } else {
           
            fiscalYear = `${currentYear - 1}-${currentYear}`;
        }

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>

</script>

</script>

</script>

<?php echo view('includes/footer'); ?>