<?php
include('functionReports.php');
// Discription: Days <= 3 meets the KPI - 11202025 - RJ
$month = $_GET['month'] ?? date('m');
$periodStart = date('Y') . '-' . $month . '-01';
$periodEnd = date('Y') . '-' . $month . '-31';

$pspId = $_GET['pspId'] ?? '';

// Main query for patient data
// for dupixent use pilot filter else dnont use it - 11202025 - RJ
if($pspId=='17'){
    $stmt = $pdo->prepare("
    SELECT 
    CASE
        WHEN (true) THEN (SELECT tblwcpart5.prescriberName FROM tblwcpart5 WHERE tblwcpart5.fileId = rem.fileId)
    END AS 'PrescriberName',
    CASE
        WHEN (true) THEN (SELECT tblpatient.province FROM tblpatient WHERE tblpatient.id = rem.pId)
    END AS 'Province',
        pdetails.patientId AS 'PatientNumber',
        p.province,
                CASE when (pdetails.enrolmentFormReceiveDate!='' and pdetails.enrolmentFormReceiveDate!='0000-00-00 00:00:00' and pdetails.enrolmentFormReceiveDate is NOT NULL) then (DATE(pdetails.enrolmentFormReceiveDate)) else DATE(pdetails.createdDate) END AS  'EnrollmentFormReceivedDate',
        DATE(rem.enrollmentCompleteDate) AS 'EnrollmentCompleteDate',
        DATEDIFF(rem.enrollmentCompleteDate, pdetails.enrolmentFormReceiveDate) AS 'Days',
        rem.notesForNav AS 'Note'
    FROM tblreimbursement AS rem
    JOIN tblpatientdetails AS pdetails ON pdetails.fileId = rem.fileId
    left JOIN tblpatient AS p ON p.id = rem.pId and p.isDeleted='N'
    WHERE 
    pdetails.pspId=? AND
    (select tblphysicians.physicianType from tblphysicians where isdeleted='N' and CONCAT(tblphysicians.firstName,' ',tblphysicians.lastName)=(Select tblwcpart5.prescriberName from tblwcpart5 where tblwcpart5.pId=p.id and p.isDeleted='N' limit 1))='Pilot'
    	AND DATE(rem.enrollmentCompleteDate) BETWEEN ? and ?");
}
    	
else if(isset($pspId) && $pspId!=undefined && $pspId!='17'){
    $stmt = $pdo->prepare("
        SELECT 
        CASE
            WHEN (true) THEN (SELECT tblwcpart5.prescriberName FROM tblwcpart5 WHERE tblwcpart5.fileId = rem.fileId)
        END AS 'PrescriberName',
        CASE
            WHEN (true) THEN (SELECT tblpatient.province FROM tblpatient WHERE tblpatient.id = rem.pId)
        END AS 'Province',
            CASE
            WHEN (pdetails.isPsp='N') then (pdetails.exPatientId)
            ELSE (pdetails.patientId) END AS 'PatientNumber',
            p.province,
                    CASE when (pdetails.enrolmentFormReceiveDate!='' and pdetails.enrolmentFormReceiveDate!='0000-00-00 00:00:00' and pdetails.enrolmentFormReceiveDate is NOT NULL) then (DATE(pdetails.enrolmentFormReceiveDate)) else DATE(pdetails.createdDate) END AS  'EnrollmentFormReceivedDate',
            DATE(rem.enrollmentCompleteDate) AS 'EnrollmentCompleteDate',
            DATEDIFF(rem.enrollmentCompleteDate, pdetails.enrolmentFormReceiveDate) AS 'Days',
            rem.notesForNav AS 'Note'
        FROM tblreimbursement AS rem
        JOIN tblpatientdetails AS pdetails ON pdetails.fileId = rem.fileId
        left JOIN tblpatient AS p ON p.id = rem.pId and p.isDeleted='N'
        WHERE 
        pdetails.pspId=? AND DATE(rem.enrollmentCompleteDate) BETWEEN ? and ?");
}
    	
$stmt->execute([$pspId,$periodStart, $periodEnd]);
$patients = $stmt->fetchAll();

function businessDays($startDate, $endDate) {
    $start = new DateTime($startDate);
    $end = new DateTime($endDate);
    $businessDays = 0;
    while ($start < $end) {
        $day = $start->format('N'); // 'N' gives 1 (Monday) to 7 (Sunday)
        if ($day < 6) {             // Monday (1) to Friday (5) are business days
            $businessDays++;
        }
        $start->modify('+1 day');
    }
    
    return $businessDays;
}

// foreach ($patients as &$patient) {
   
// }
unset($patient); // Unset the reference after the loop

$data = [];
foreach ($patients as $patient) {
     if ($patient['EnrollmentCompleteDate'] != '' && $patient['EnrollmentFormReceivedDate'] != '0000-00-00' && $patient['EnrollmentFormReceivedDate'] != '' && $patient['EnrollmentCompleteDate'] >= $patient['EnrollmentFormReceivedDate']) {
        $patient['Days'] = businessDays($patient['EnrollmentFormReceivedDate'],$patient['EnrollmentCompleteDate']);
    }else{
        $patient['Days'] = '';
    }
    $data[] = [
        "PatientNumber" => $patient['PatientNumber'] ?? '',
        "Province" => $patient['province'] ?? '',
        "EnrollmentFormReceivedDate" => $patient['EnrollmentFormReceivedDate'] ?? '',
        "EnrollmentCompleteDate" => $patient['EnrollmentCompleteDate'] ?? '',
        "Days" => $patient['Days'] ?? '',
        "Note" => $patient['Note'] ?? '',
        "PrescriberName" => $patient['PrescriberName'] ?? '',
        "PSPID" => $pspId
    ];
}
unset($patient);
// Calculate data for second sheet
// $frequency = [];
// $totalPatients = 0;
// $meetingKPI = 0;

// foreach ($patients as $patient) {
//     echo $patient['Days'];
//     if (isset($patient['Days']) && is_numeric($patient['Days']) ) {
//         $days = (int)$patient['Days'];
//         if (!isset($frequency[$days])) {
//             $frequency[$days] = 0;
//         }
//         $frequency[$days]++;
//         $totalPatients++;
//         if ($days <= 3) {
//             $meetingKPI++;
//         }
//     }
// }
// unset($patient);
// ksort($frequency);

// $percentageMeetingKPI = ($totalPatients > 0) ? ($meetingKPI / $totalPatients) * 100 : 0;

// $percentile80 = null;
// if ($totalPatients > 0) {
//     $target = 0.8 * $totalPatients;
//     $cumulative = 0;
//     foreach ($frequency as $days => $count) {
//         $cumulative += $count;
//         if ($cumulative >= $target) {
//             $percentile80 = $days;
//             break;
//         }
//     }
// }

// $secondSheetData = [
//     'title' => "SKPI1 - " . date('F Y', strtotime($periodStart)),
//     'filter' => "Filter for Enrollment Form Received date is " . date('F Y', strtotime($periodStart)),
//     'kpi' => "Percentage of patients meeting KPI (3 days or less): " . number_format($percentageMeetingKPI, 2) . "%",
//     'percentile' => "80% of $totalPatients is " . number_format(0.8 * $totalPatients, 1) . " so 80th percentile is " . ($percentile80 !== null ? $percentile80 : 'N/A') . " days",
//     'table' => [['Days', 'Number of Patients', 'Percentage']]
// ];

// foreach ($frequency as $days => $count) {
//     $percentage = ($totalPatients > 0) ? ($count / $totalPatients) * 100 : 0;
//     $secondSheetData['table'][] = [$days, $count, number_format($percentage, 2) . "%"];
// }

// $secondSheetData['table'][] = ['Grand Total', $totalPatients, '100%'];

// Calculate data for second sheet
$frequency = [];
$validPatients = 0;        // patients with Days > 0
$meetingKPI = 0;          // Days <= 3 among valid patients
$missingInfoCount = 0;

foreach ($data as $patient) {
    // normalize/trimming in case Days is string with spaces
    if (isset($patient['Days']) && $patient['Days'] !== '' && is_numeric(trim($patient['Days']))) {
        $days = (int) trim($patient['Days']);
        if ($days >= 0) {
            if (!isset($frequency[$days])) {
                $frequency[$days] = 0;
            }
            $frequency[$days]++;
            $validPatients++;
            if($pspId == '17'){
                if ($days <= 3) {
                    $meetingKPI++;
                }
            }
            else if($pspId == '26'){
                if ($days <= 4) {
                    $meetingKPI++;
                }
            }
        } else {
            // zero or negative -> count as missing (per your original logic)
            $missingInfoCount++;
        }
    } else {
        // not set or non-numeric -> missing
        $missingInfoCount++;
    }
}

ksort($frequency);

// Grand total (for display) = valid + missing
$totalPatients = $validPatients + $missingInfoCount;

// KPI percent: show both of valid and of all (choose what you need)
$percentageMeetingKPI_valid = ($validPatients > 0) ? ($meetingKPI / $validPatients) * 100 : 0;
$percentageMeetingKPI_all   = ($totalPatients > 0) ? ($meetingKPI / $totalPatients) * 100 : 0;

// 80th percentile: compute using only valid patients (frequency sums to validPatients)
$percentile80 = null;
if ($validPatients > 0) {
    $target = 0.8 * $validPatients; // IMPORTANT: based on valid rows
    if($pspId=='17'){
        $target = 0.8 * $validPatients;
    }
    else if($pspId=='26'){
        $target = 0.9 * $validPatients;
    }
    $cumulative = 0;
    foreach ($frequency as $days => $count) {
        $cumulative += $count;
        if ($cumulative >= $target) {
            $percentile80 = $days;
            break;
        }
    }
}
// 80th percentile (all patients, missing as bucket)
$percentile80_all = null;
if ($totalPatients > 0) {
    // Combine valid frequency + missing bucket
    $frequencyAll = $frequency;
    if ($missingInfoCount > 0) {
        // Put missing values at the end so they don't artificially lower the percentile
        $frequencyAll['MISSING'] = $missingInfoCount;
    }

    // Sort while keeping 'MISSING' last
    uksort($frequencyAll, function($a, $b) {
        if ($a === 'MISSING') return 1;
        if ($b === 'MISSING') return -1;
        return $a <=> $b;
    });

    $target = 0.8 * $totalPatients;
    
    if($pspId=='17'){
        $target = 0.8 * $totalPatients;
    }
    else if($pspId=='26'){
        $target = 0.9 * $totalPatients;
    }
    $cumulative = 0;
    foreach ($frequencyAll as $days => $count) {
        $cumulative += $count;
        if ($cumulative >= $target) {
            $percentile80_all = $days;
            break;
        }
    }
}

// 80th percentile (all, missing info included as its own bucket)

$secondSheetData = [
    'title' => "Please verify the details SKPI1 - " . date('F Y', strtotime($periodStart)),
    'filter' => "Filter for Enrollment Form Complete date is " . date('F Y', strtotime($periodStart)),
    'kpi_valid' => "Percentage meeting KPI (3 days or less) — of valid rows: " . number_format($percentageMeetingKPI_valid, 2) . "%",
    'kpi_all' => "Percentage meeting KPI (3 days or less) — of all rows: " . number_format($percentageMeetingKPI_all, 2) . "%",
    'percentile' => "80% of $validPatients valid patients is " . number_format(0.8 * $validPatients, 1) . " so 80th percentile is " . ($percentile80 !== null ? $percentile80 : 'N/A') . " days",
    'percentile_all' => "80% of $totalPatients total patients is " . number_format(0.8 * $totalPatients, 1) . " so 80th percentile (all) is " . ($percentile80_all !== null ? $percentile80_all : 'N/A') . " days",


    'table' => [['Days', 'Number of Patients', 'Percentage (of grand total)']]
];

// build sheet
if($pspId=='17'){
$secondSheetData = [
    'title' => "SKPI1 - " . date('F Y', strtotime($periodStart)),
    'filter' => "Filter for Enrollment Form Complete date is " . date('F Y', strtotime($periodStart)),
    'kpi_valid' => "Percentage meeting KPI (3 days or less) — of valid rows: " . number_format($percentageMeetingKPI_valid, 2) . "%",
    'kpi_all' => "Percentage meeting KPI (3 days or less) — of all rows: " . number_format($percentageMeetingKPI_all, 2) . "%",
    'percentile' => "80% of $validPatients valid patients is " . number_format(0.8 * $validPatients, 1) . " so 80th percentile is " . ($percentile80 !== null ? $percentile80 : 'N/A') . " days",
    'percentile_all' => "80% of $totalPatients total patients is " . number_format(0.8 * $totalPatients, 1) . " so 80th percentile (all) is " . ($percentile80_all !== null ? $percentile80_all : 'N/A') . " days",


    'table' => [['Days', 'Number of Patients', 'Percentage (of grand total)']]
];
}
else if($pspId=='26'){
    $secondSheetData = [
    'title' => "SKPI1 - " . date('F Y', strtotime($periodStart)),
    'filter' => "Filter for Enrollment Form Complete date is " . date('F Y', strtotime($periodStart)),
    'kpi_valid' => "Percentage meeting KPI (4 days or less) — of valid rows: " . number_format($percentageMeetingKPI_valid, 2) . "%",
    'kpi_all' => "Percentage meeting KPI (4 days or less) — of all rows: " . number_format($percentageMeetingKPI_all, 2) . "%",
    'percentile' => "90% of $validPatients valid patients is " . number_format(0.9 * $validPatients, 1) . " so 90th percentile is " . ($percentile80 !== null ? $percentile80 : 'N/A') . " days",
    'percentile_all' => "90% of $totalPatients total patients is " . number_format(0.9 * $totalPatients, 1) . " so 90th percentile (all) is " . ($percentile80_all !== null ? $percentile80_all : 'N/A') . " days",


    'table' => [['Days', 'Number of Patients', 'Percentage (of grand total)']]
];
}

foreach ($frequency as $days => $count) {
    $percentageOfTotal = ($totalPatients > 0) ? ($count / $totalPatients) * 100 : 0;
    $secondSheetData['table'][] = [$days, $count, number_format($percentageOfTotal, 2) . "%"];
}

$secondSheetData['table'][] = ['MISSING INFO or Negative values', $missingInfoCount, $totalPatients > 0 ? number_format(($missingInfoCount / $totalPatients) * 100, 1) . '%' : '0%'];
$secondSheetData['table'][] = ['Grand Total', $totalPatients, '100%'];

?>

<!DOCTYPE html>
<html>
<head>
    <title>Entries Grid with Date & Time</title>
    <link href="https://client.rxconnections.ca/client/kendo/js/kendoui/styles/kendo.common.min.css?67d7a1" rel="stylesheet">
    <link href="https://client.rxconnections.ca/client/kendo/js/kendoui/styles/kendo.default.min.css?67d7a1" rel="stylesheet">
    <link href="https://client.rxconnections.ca/client/kendo/js/kendoui/styles/kendo.default.mobile.min.css?67d7a1" rel="stylesheet">
    <link href="https://client.rxconnections.ca/client/kendo/js/kendoui/styles/kendo.silver.min.css?67d7a1" rel="stylesheet">
    <link href="https://client.rxconnections.ca/client/kendo/js/kendoui/styles/kendo.dataviz.min.css?67d7a1" rel="stylesheet">
    <link href="https://client.rxconnections.ca/client/kendo/js/kendoui/styles/kendo.dataviz.silver.min.css?67d7a1" rel="stylesheet">
    <script src="https://client.cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
    <script src="https://client.rxconnections.ca/client/kendo/js/kendoui/js/jquery.min.js?67d7a1"></script>
    <script src="https://client.rxconnections.ca/client/kendo/js/kendoui/js/kendo.custom.min.js?67d7a1"></script>
    <style>
        html {
            font: 75% 'Lucida Sans Unicode','Lucida Grande',Arial,helvetica,sans-serif;
        }
        .me_center_div {
            text-align: center !important;
            width: 100%;
        }
        .me_right_div {
            text-align: right !important;
            width: 100%;
        }
        .me_file_div {
            background-image: url('images/icons/185.png');
            background-repeat: no-repeat;
            background-position: 0 2px;
            padding-left: 15px;
        }
        .entry_link {
            border-bottom: 1px dotted #000000;
            color: #3661A1 !important;
            text-decoration: none !important;
        }
        .mf_grid_title {
            line-height: 30px;
            margin: 0px;
            font-size: 18px;
            font-family: Helvetica, Arial, sans-serif; 
            font-weight: 400;
            text-align: center;
        }
        span.k-icon.k-i-filter {
            color: #1f87ca;
        }
        .filter-btn {
            font-weight: 500;
            margin: 0px 5px;
            line-height: 15px;
        }
    </style>
</head>
<body>
<div class="k-content">
    <div id="mf_grid"></div>
</div>
<script>
var secondSheetData = <?php echo json_encode($secondSheetData); ?>;
</script>
<script>
$(document).ready(function() {
    var data = <?php echo json_encode($data); ?>;
    console.log(data);
    $("#mf_grid").kendoGrid({
        dataSource: {
            data: data,
            pageSize: 100
        },
        height: 700,
        sortable: true,
        scrollable: true,
        filterable: true,
        columnMenu: true,
        pageable: {
            refresh: true,
            pageSizes: true,
            buttonCount: 5
        },
        toolbar: ["excel"],
        excel: {
            fileName: "SKPI1.xlsx",
            filterable: true,
            allPages: true
        },
        schema: {
          model: {
            fields: {
                    EnrollmentFormReceivedDate: { type: "date" },
                    EnrollmentCompleteDate: { type: "date" }
            }
          }
        },
        columns: [
            { field: "PatientNumber", title: "Patient Number" },
            { field: "PrescriberName", title: "Prescriber Name" },
            { field: "Province", title: "Province" },
            { field: "EnrollmentFormReceivedDate", title: "Enrollment Form Received Date", format: "{0:yyyy-MM-dd}",filterable: {
                    ui: "datetimepicker"
                }},
            { field: "EnrollmentCompleteDate", title: "Enrollment Complete Date", format: "{0:yyyy-MM-dd}" ,filterable: {
                    ui: "datetimepicker"
                }},
            { 
                field: "Days", 
                title: "Days", 
                template: function(dataItem) {
                    var color = (parseInt(dataItem.Days) > 3 && dataItem.PSPID=='17') ? 'style="background-color: #ffcccc;"' : (parseInt(dataItem.Days) > 4 && dataItem.PSPID=='26') ? 'style="background-color: #ffcccc;"' : '';
                    return '<div ' + color + '>' + dataItem.Days + '</div>';
                }
            },
            { field: "Note", title: "Note" }
        ],
        excelExport: function(e) {
            e.workbook.sheets[0].rows.forEach(row => {
                row.cells.forEach(cell => {
                    // if (cell.value && typeof cell.value === "number" && cell.value > 3) {
                    //     cell.background = "#ffcccc";
                    // }
                });
            });
            var sheet = e.workbook.sheets[0];
            var filter = e.sender.dataSource.filter();
            if (filter && filter.filters.length > 0) {
                sheet.name += " (Filtered)";
            }

            var secondSheet = {
                name: "SKPI1",
                rows: []
            };

            secondSheet.rows.push({
                cells: [{ value: secondSheetData.title, bold: true, colSpan: 3 }]
            });
            secondSheet.rows.push({
                cells: [{ value: "Enrollment Received to Enrollment Complete" }]
            });
            secondSheet.rows.push({
                cells: [{ value: secondSheetData.filter }]
            });
            // secondSheet.rows.push({
            //     cells: [{ value: "Checked all negative values - Edited the dates and re-ran the reports" }]
            // });
            secondSheet.rows.push({
                cells: [{ value: secondSheetData.kpi_valid }]
            });
            secondSheet.rows.push({
                cells: [{ value: secondSheetData.kpi_all }]
            });
            secondSheet.rows.push({
                cells: [{ value: secondSheetData.percentile }]
            });
             secondSheet.rows.push({
                cells: [{ value: secondSheetData.percentile_all }]
            });
            
            secondSheet.rows.push({
                cells: [{ value: "" }]
            });
            secondSheet.rows.push({
                cells: [
                    { value: "Days", bold: true },
                    { value: "Number of Patients", bold: true },
                    { value: "Percentage", bold: true }
                ]
            });

            secondSheetData.table.forEach(function(row, index) {
                if (index > 0) {
                    var cells = [
                        { value: row[0] },
                        { value: row[1] },
                        { value: row[2] }
                    ];
                    if (row[0] === 'Grand Total') {
                        cells.forEach(function(cell) {
                            cell.bold = true;
                        });
                    }
                    secondSheet.rows.push({ cells: cells });
                }
            });

            e.workbook.sheets.push(secondSheet);
        }
    });
});
</script>
</body>
</html>