<?php
//include('f.php');
include('connect.php');
function getDayDifference($startDate, $endDate) {
    if (!empty($startDate) && !empty($endDate) && ($startDate!='0000-00-00' && $endDate!='0000-00-00')) {
        $date1 = strtotime($startDate);
        $date2 = strtotime($endDate);
        $diff = $date2 - $date1;
        $days = floor($diff / (60 * 60 * 24));
        return $days;
    }
    return '';
}
function sanitizeDate($date) {
    return ($date === '0000-00-00' || empty($date)) ? '' : date('Y-m-d', strtotime($date));
}
function sanitizePercentage($input) {
    // Use regex to find a number with an optional decimal part
    if (preg_match('/\d+(\.\d+)?/', $input, $matches)) {
        $value = floatval($matches[0]);
        // Clamp the value to the 0-100 range
        if ($value < 0) {
            return 0;
        } elseif ($value > 100) {
            return 100;
        }
        return $value;
    }
    // Return 0 if no numeric value is found
    return 0;
}
function getDaysDifference($startDate, $endDate) {
    if (!empty($startDate) && !empty($endDate)) {
        try {
            $start = new DateTime($startDate);
            $end   = new DateTime($endDate);
            return $start->diff($end)->days;
        } catch (Exception $e) {
            // Handle any conversion errors if needed
            return '';
        }
    }
    return '';
}
function getProvinceCode($province) {
    global $pdo;
   $stmt = $pdo->prepare("SELECT provinceCode from tblprovince  WHERE provinceName=?");                             
                        $stmt->execute([$province]);
                        return $stmt->fetchColumn();
}
function getFSA($postalCode) {
    // Remove any spaces or special characters from the postal code
    $postalCode = strtoupper(preg_replace('/\s+/', '', $postalCode));

    // Check if the postal code is valid (Canadian postal codes are 6 characters long, e.g., A1A1A1)
    if (strlen($postalCode) >= 3) {
        // Extract the first 3 characters (FSA)
        return substr($postalCode, 0, 3);
    }

    // Return an empty string or handle invalid postal codes
    return '';
}
function getdrugName($pdo,$empId){
  $stmt1 = $pdo->prepare("SELECT drugName from tbldrugs where id=?");
  $stmt1->execute([$empId]);
  $patientDetails = $stmt1->fetch();
  //return getInitials();
  return $patientDetails['drugName']; 
  // Echo the JSON response
}
// function getPrimaryInsuranceProvider_L($pdo,$fileId){
//     // element_319
//     $stmt = $pdo->prepare("SELECT element_319 from tblkabi WHERE id=SUBSTRING_INDEX(?, '-', -1)");
//     $stmt->execute([$fileId]);
//     $result = $stmt->fetchColumn();
//     return $result;
// }
function getEarliestCommercializationDate($fileId){
    global $pdo;
    $stmtx = $pdo->prepare("SELECT saEapCom FROM tblsahistory WHERE fileId=? and isDeleted='N' and saEapCom is not NULL and saEapCom != '0000-00-00' ORDER BY saEapCom LIMIT 1");                  
    $stmtx->execute([$fileId]);
    
    // Fetch the dosage
    $fre = $stmtx->fetchColumn();
  
    // Check if the dosage is 600 and return 300 instead
  
    
    // Otherwise, return the fetched dosage
    return $fre;
    
}
function getReimbursementStatus($id) {
    global $pdo;
    $stmt1 = $pdo->prepare("SELECT name from tblreimbursementstatus where id=?");
    $stmt1->execute([$id]);
    $patientDetails = $stmt1->fetchColumn();
    //return getInitials();
    if($patientDetails){
        return $patientDetails; 
    }
    return "";
}
$stmt = $pdo->prepare("WITH LatestIndication AS (
                        SELECT *
                        FROM (
                            SELECT *,
                                   ROW_NUMBER() OVER (PARTITION BY pId, fileId ORDER BY id DESC) AS rn
                            FROM tblindication
                            WHERE isDeleted = 'N'
                        ) t
                        WHERE rn = 1
                    )
                    
                    SELECT 
                        tblpatient.id,
                        tblpatient.gender, 
                        tblpatient.dob, 
                        tblpatient.province, 
                        tblpatient.postalCode, 
                        tblpatient.createdDate,
                        DATE(tblpatientdetails.enrolmentFormReceiveDate) AS enrolmentFormReceiveDate,
                        tblpatientdetails.fileId AS patientFileId, 
                        tblpatientdetails.patientId AS patientuId,
                        tblpatientdetails.innomarPatientId,
                        tblpatientdetails.isTransferOut,
                        tblpatientdetails.medicationId AS medicationId, 
                        tblwcpart5.patientWeight, 
                        tblwcpart5.dopd,
                        tblwcpart5.patientDrugName,
                        tblwcpart5.indication, 
                        tblwcpart5.currentFrequency, 
                        tblwcpart5.dosage, 
                        tblwcpart5.patientWeightUnit,
                        tblwcpart5.prescriberName,
                        wc1.wcFirstAttempt, 
                        wc1.wcCompletionDate,  
                        (Select title from tblwelcomecallstatus where id=wc1.wcStatus) as wc1Status,
                    
                        -- From tblindication (now filtered to latest only)
                        i.ipmctst,
                        i.isHighDoseICS,
                        i.isexacerbations1,
                        i.isexacerbations2,
                        i.eosCount,
                        i.fenoLevel,
                        i.scWeeks,
                        i.scDosing,
                        i.scWeeks2,
                        i.scDosing2,
                        i.totalIgE,
                        i.otherAsthmaScore,
                        i.nos,
                        i.lastSurgeryDate,
                        i.isSurgeryRequired,
                        i.rationale,
                        i.isSystemic,
                        i.otherCrsScore,
                        i.treatment,
                        i.dose,
                        i.dateFrom,
                        i.dateTo,
                        i.coexistingDiseases,
                        i.testScore,
                        i.isTC,
                        i.tcvalue,
                        i.isTCI,
                        i.isTacrolimus,
                        i.tracolimusWeeks,
                        i.tracolimusDosing,
                        i.isPime,
                        i.pimeWeeks,
                        i.pimeDosing,
                        i.isSystemic3,
                        i.systemicWeeks3,
                        i.systemicDosing3,
                        i.isImmuno,
                        i.isMethotrexate,
                        i.methotrexateWeeks,
                        i.methotrexateDosing,
                        i.isCyclosporine,
                        i.cyclosporineWeeks,
                        i.cyclosporineDosing,
                        i.isOther,
                        i.otherName,
                        i.otherWeeks,
                        i.otherDosing,
                        i.isPhoto,
                        i.photoWeeks,
                        i.notAccessibleReason,
                        i.isNoAccessToInfo,
                        i.isHighDosePPI,
                        i.isTopicalCorticosteroids,
                        i.additionalRelevantMedication,
                        CASE 
                            WHEN (tblpatientdetails.patientId like '%IDAC%') THEN 'Idacio'
                            WHEN (tblpatientdetails.patientId like '%TYEN%') THEN 'Tyenne'
                            WHEN (tblpatientdetails.patientId like '%OTUL%') THEN 'Otulfi'
                            END
                        AS Therapy,
                       tblwcpart5.staff as 'StaffName',
                       (Select MIN(tblsahistory.saEapSub) saEapOutcome from tblsahistory where tblsahistory.fileId=tblpatientdetails.fileId and tblpatientdetails.pid=tblsahistory.pId) as 'InitialSub',
                       (Select MIN(tblsahistory.saEapOutcome) from tblsahistory where tblsahistory.fileId=tblpatientdetails.fileId and tblpatientdetails.pid=tblsahistory.pId) as 'InitialAproved'
                    FROM tblpatientdetails
                    LEFT JOIN tblpatient 
                        ON tblpatient.id = tblpatientdetails.pId
                    LEFT JOIN tblwcpart1 AS wc1 
                        ON tblpatientdetails.pId = wc1.pId AND tblpatientdetails.fileId = wc1.fileId
                    LEFT JOIN tblwcpart5 
                        ON tblpatientdetails.pId = tblwcpart5.pId AND tblpatientdetails.fileId = tblwcpart5.fileId
                    LEFT JOIN LatestIndication AS i 
                        ON tblpatientdetails.pId = i.pId AND tblpatientdetails.fileId = i.fileId
                    WHERE tblpatient.isDeleted = 'N' 
                      AND tblpatientdetails.isDeleted = 'N' 
                      AND tblpatientdetails.pspId = '32'
                      AND   tblpatientdetails.enrolmentFormReceiveDate >= '2025-07-26';
                      ;");
            $stmt->execute();
            $patients = $stmt->fetchAll();
// Loop through the patient data and write rows
$trtStartDateFinal='';
$data = [];
                        foreach ($patients as $patient) {
                            
                              $stmt = $pdo->prepare("SELECT r.enrollmentCompleteDate, r.patientStatus, r.patientSubStatus,
                                  r.physicianId, r.pharmacyType, r.pharmacyName, r.treatmentFacilityName, r.treatmentFacilityType,
                                  r.treatmentFacilityProvince, r.treatmentFacilityPostalCode, r.patientType, r.patientTypeChangeReason,
                                  r.sanofiPatientType, r.saEapCom, r.rcd, r.dAppealSub, r.saEapOutcome, r.insuranceType2, r.insuranceType,
                                  r.ipal, r.insuranceTypeStatus, r.reimbursementStatus, r.coveragePercentage, r.ipal_secondary,
                                  r.insuranceType2Status, r.reimbursementStatus2, r.coveragePercentage_secondary, r.insuranceType3,
                                  r.ipal_tertiary, r.insuranceType3Status, r.coveragePercentage_tertiary, r.firstInjDate,  r.isFastStart, r.notFastStartReason,r.trtStrtDate,
                                  r.fileId,
                                  CASE
                                        WHEN (true) THEN (Select userName from tbluser where empid = r.rcby)
                                    END as rcby,r.trtStatus,
                                  r.reimbursementState,
                                  r.reimbursementReason, CONCAT(r.consentVFirst,' ',r.consentVLast) as consentV,
                                  r.dphsp,r.dphsp_tertiary
                                from tblreimbursement as r
                                WHERE r.pId = ?  AND r.fileId = ?");
                                $stmt->execute([$patient['id'],$patient['patientFileId']]);
                                $rim = $stmt->fetch();
                                $trtStartDateFinal=$rim['trtStrtDate'];
                                error_log('check trtDate'.$rim['trtStrtDate'].'| pId'.$patient['id'].' |fileId '.$patient['patientFileId']);
                                
                                if ($patient['medicationId'] == '0' || $isTransferOut == 'Y') {
                                    continue;
                                }
                                
                            $genderMap = ['F' => 'Female', 'M' => 'Male', 'O' => 'Other'];
                            $gender = $genderMap[$patient['gender']] ?? 'Unknown';
                        
                            // Age calculation
                            $age = isset($patient['dob']) ? (new DateTime())->diff(new DateTime($patient['dob']))->y : 'Unknown';
                        
                            // Fetch Status and Substatus names
                           
                        
                           
                        
                            // Fetch dates for patient history
                            $discontinuedDate = '';
                            $reactivationDate = '';
                            if (!empty($patient['patientFileId'])) {
                                $stmt = $pdo->prepare("
                                    SELECT 
                                        MAX(CASE WHEN statusValue = 'discontinued' THEN DATE(createdAt) END) AS discontinuedDate,
                                        MAX(CASE WHEN statusValue = 'reactivation' THEN DATE(createdAt) END) AS reactivationDate
                                    FROM tblstatuslogs 
                                    WHERE operationName = 'Patient Status in CRM Updated' AND fileId = ?");
                                $stmt->execute([$patient['patientFileId']]);
                                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                                $discontinuedDate = $result['discontinuedDate'] ?? '';
                                $reactivationDate = $result['reactivationDate'] ?? '';
                            }
                            
                           
                        
                            // Fetch physician details
                            $physician = [];
                            
                            // use tblwcpart5 acc to physician name
                            if (!empty($patient['prescriberName'])) {
                                $stmt = $pdo->prepare("SELECT * FROM tblphysicians WHERE concat(firstName,' ',lastName) = ?");
                                $stmt->execute([$patient['prescriberName']]);
                                $physician = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
                            }
                            

                            $rcd = $patient['rcd'] ?? null;
                            $dAppealSub = $patient['dAppealSub'] ?? null;
                            $saEapOutcome = $patient['saEapOutcome'] ?? null;
                        
                            // Insurance statuses
                   
                        
                            // Patient weight
                            $patientWeight = $patient['patientWeight'] ?? '';
                            $patientWeightUnit = $patient['patientWeightUnit'] ?? ''; // Corrected the key name (removed extra $)
                            
                            if ($patientWeightUnit == 'LB' && is_numeric($patientWeight)) {
                                // Convert weight from LB to KG
                                $patientWeight = $patientWeight * 0.453592;
                               
                            } 
                            // $firstInjDate=isset($rim['firstInjDate']) ? date('Y-m-d', strtotime($rim['firstInjDate'])) : '';
                            
                            if($patientWeight!='')
                            {
                                $patientWeight=round($patientWeight,2);
                            }
                            
                            $drugName=getdrugName($pdo,$patient['medicationId']);
                            preg_match('/\d+/', $drugName, $matches);
                            // $dosage = (int) $matches[0];
                            $dosage=$patient['dosage'];
                          
                            $innomarPatientId=$patient['innomarPatientId'];
                            if($dosage==0){
                               //$dosage=getDosage($innomarPatientId);
                            }
                             $frequency=$patient['currentFrequency'];
                            if($frequency==''){
                             //  $frequency=getFrequency($innomarPatientId); 
                            }
                        
// Other patient details
$enrollmentCompleteDate = $rim['enrollmentCompleteDate'] ?? null;

if($enrollmentCompleteDate==null || $enrollmentCompleteDate=="0000-00-00 00:00:00"){
 //  $enrollmentCompleteDate=getTherapyData($innomarPatientId,'complete');
}

// $enrolmentFormReceivedDate = $patient['createdDate'] ?? null;
//$enrolmentFormReceivedDate=getTherapyData($innomarPatientId,'receive') ?? '';
$enrolmentFormReceivedDate='';
if($enrolmentFormReceivedDate=='' || $enrolmentFormReceivedDate=='0000-00-00' ){
    $enrolmentFormReceivedDate=$patient['enrolmentFormReceiveDate'];
    if($enrolmentFormReceivedDate=="" || $enrolmentFormReceivedDate=="0000-00-00 00:00:00"){
        // $enrolmentFormReceivedDate=$patient['createdDate'];
    }
}

$enorllmentFormReceivedDate=isset($enrolmentFormReceivedDate) ? $enrolmentFormReceivedDate : (isset($patient['createdDate']) ? date('Y-m-d', strtotime($patient['createdDate'])) : '');

//$commercializationDate = getCommercializationDate($innomarPatientId); // did we get from innomar
$commercializationDate='';
if($commercializationDate=='' || $commercializationDate=='0000-00-00'){ // earliest from sa history
    $commercializationDate=getEarliestCommercializationDate($rim['fileId']);
}
if($commercializationDate=='' || $commercializationDate=='0000-00-00'){ // latest from reimbursement details
  $commercializationDate=$rim['saEapCom'];
}

$patientStatus = '';
if (!empty($rim['patientStatus'])) {
    $stmt = $pdo->prepare("SELECT StatusName FROM tblpatientstatus WHERE StatusID = ? AND isDeleted = 'N' limit 1");
    $stmt->execute([$rim['patientStatus']]);
    $patientStatus = $stmt->fetchColumn() ?: 'Unknown';
}
$patientSubStatus = '';
if (!empty($rim['patientSubStatus']) && !empty($rim['patientStatus'])) {
    $stmt = $pdo->prepare("SELECT SubstatusName FROM tblpatientsubstatus WHERE SubstatusID = ? AND StatusID = ? AND isDeleted = 'N' limit 1");
    $stmt->execute([$rim['patientSubStatus'], $rim['patientStatus']]);
    $patientSubStatus = $stmt->fetchColumn() ?: 'Unknown';
}
// if($patientSubStatus==''){
//     $patientSubStatus=getSubstatus($innomarPatientId);
//     //$patientStatus=getStatus($innomarPatientId);
// }
$sanofiPatientType=$rim['reimbursementState'];
if(!isset($sanofiPatientType) || $sanofiPatientType!=''){
    $stmt = $pdo->prepare("Select name from tblreimbursementstate where id=? limit 1");
    $stmt->execute([$rim['reimbursementState']]);
    $sanofiPatientType = $stmt->fetchColumn() ?: '';
}

$remreason=$rim['reimbursementReason'];
if(!isset($remreason) || $remreason!=''){
    $stmt = $pdo->prepare("Select name from tblreimbursementreason where id=? limit 1");
    $stmt->execute([$rim['reimbursementReason']]);
    $remreason = $stmt->fetchColumn() ?: '';
}

$patientType=$rim['patientType'];
if($patientType==''){
  //  $patientType=getClinicalCriteria($innomarPatientId,'Patient Type in CRM');
}else{
    $parts = explode(' - ', $patientType, 2);
    $patientType = trim($parts[0]);
    //$clinicalCriteria = isset($parts[1]) ? trim($parts[1]) : '';
}

$patientTypeReason=$rim['patientTypeChangeReason'];
if($patientTypeReason==''){
 //   $patientTypeReason=getChangeReason($innomarPatientId);
}


if($rim['insuranceTypeStatus'] == 1){
    $insuranceTypeStatus = 'Active';
}elseif($rim['insuranceTypeStatus'] == 0){
    $insuranceTypeStatus = 'Inactive';
}else{
   $insuranceTypeStatus = 'Inactive';
}
if($rim['insuranceType2Status'] == 1){
    $insuranceTypeStatus2 = 'Active';
}elseif($rim['insuranceType2Status'] == 0){
    $insuranceTypeStatus2 = 'Inactive';
}else{
     $insuranceTypeStatus2 = 'Inactive';
}
if($rim['insuranceType3Status'] == 1){
    $insuranceTypeStatus3 = 'Active';
}elseif($rim['insuranceType3Status'] == 0){
    $insuranceTypeStatus3 = 'Inactive';
}else{
      $insuranceTypeStatus3 = 'Inactive';
}

$insuranceType=$rim['insuranceType'];
$insuranceType2=$rim['insuranceType2'];
$insuranceType3=$rim['insuranceType3'];
$ipal=$rim['ipal'];
if($ipal=='' || $ipal=='Select Insurance Provider'){
    // $ipal=getPrimaryInsuranceProvider_L($pdo,$rim['fileId']);
    $ipal='';
}
$ipal_secondary=$rim['ipal_secondary'];
$ipal_tertiary=$rim['ipal_tertiary'];
$coveragePercentage=$rim['coveragePercentage'];
$coveragePercentage_secondary=$rim['coveragePercentage_secondary'];
$coveragePercentage_tertiary=$rim['coveragePercentage_tertiary'];
$reimbursementStatus=getReimbursementStatus($rim['reimbursementStatus']);
$reimbursementStatus2=getReimbursementStatus($rim['reimbursementStatus2']);

$dopd=$patient['dopd'];
if($dopd=='' || $dopd=='0000-00-00'){
 //   $dopd=getDOPD($innomarPatientId);
}
$ccCRM='';
$cc='';
if($ccCRM==''){
     // $ccCRM=getClinicalCriteria($innomarPatientId,'Clinical Criteria in CRM');
}
if($cc==''){
     // $cc=getClinicalCriteria($innomarPatientId,'Clinical Criteria');
}

// (START) data according to new field added to capture fast start (13/05/2025) - RJ
if($rim['isFastStart']!="" && $rim['isFastStart'] != "N"){
    $ccCRM = "Fast Start Criteria Met";
    $cc = "Fast Start Criteria Met";
}
else if($rim['isFastStart']!="" && $rim['isFastStart'] == "N"){
    //get reason why its not a fast start
    $cc = "Fast Start Criteria Not Met";
    $ccCRM = 'Not Met = '.$rim["notFastStartReason"]; // reason captured in CRM ('add Not met = ' string before data captured in CRM)
}
// (END) data according to new field added to capture fast start (13/05/2025) - RJ

// incase clinic criteria is still blank (for cases in 2025 Jan - April) - RJ
if($cc==""){
    if($sanofiPatientType != "" && $sanofiPatientType != "Undefined" && $sanofiPatientType != "Undefined / Missing Information")
    {
        $cc="Fast Start Criteria Met";
    }
    else{
        $cc="Fast Start Criteria Not Met";
}
}

// incase clinic criteria is still blank (for cases in 2025 Jan - April) - RJ
if($ccCRM==""){
    if($patientType!="" && $patientType != "Undefined" && $patientType != "Undefined / Missing Information"){
        $ccCRM = "Fast Start Criteria Met";
    }
}

// $rcby=isset($rim['rcby']) ? $rim['rcby'] : '';
   $rcby=isset($rim['consentV']) ? $rim['consentV'] : ''; 

$firstInjDate = isset($rim['firstInjDate']) ? $rim['firstInjDate'] : '';
if($firstInjDate=='' || $firstInjDate=='0000-00-00'){
    //$firstInjDate=getClinicalCriteria($innomarPatientId,'On Drug Start Date with Program');
}



// $firstInjDateOffered = isset($rim['firstInjDate']) ? $rim['firstInjDate'] : ''; // this is on drug start date with program
$firstInjDateOffered = isset($rim['trtStrtDate']) ? $rim['trtStrtDate'] : ''; // this is on drug start date

if($firstInjDateOffered=='' || $firstInjDateOffered=='0000-00-00'){
    //$firstInjDateOffered=getClinicalCriteria($innomarPatientId,'First Offered Injection Date');
}

if(!isset($trtStartDateFinal))
{
    // if on drug start date is emply then fill on drug start date with program
    $trtStartDateFinal=$firstInjDate;
}

//on drug start date
$treatmentStartDate=isset($rim['trtStrtDate']) ? $rim['trtStrtDate'] : '';

if($discontinuedDate=='' || $discontinuedDate=='0000-00-00'){
    //$discontinuedDate=getClinicalCriteria($innomarPatientId,'Most Recent Discontinued Date');
}
if($reactivationDate=='' || $reactivationDate=='0000-00-00'){
  //  $reactivationDate=getClinicalCriteria($innomarPatientId,'Most Recent Reactivation Form Received Date');
}
$wcCompletionDate=$patient['wcCompletionDate'];
if($wcCompletionDate=='' || $wcCompletionDate=='0000-00-00'){
  //  $wcCompletionDate=getClinicalCriteria($innomarPatientId,'First Successful Contact Date');
}
$wcFirstAttempt = (!empty($patient['wcFirstAttempt']) && $patient['wcFirstAttempt'] !== "0000-00-00 00:00:00") ? date('Y-m-d', strtotime($patient['wcFirstAttempt'])) : '';
// if($wcFirstAttempt=='' || $wcFirstAttempt=='0000-00-00'){
//     $wcFirstAttempt=getClinicalCriteria($innomarPatientId,'First Outgoing Call Date');
// }
$wcFirstAttempt=(isset($wcFirstAttempt) && $wcFirstAttempt!='' && $wcFirstAttempt!='0000-00-00 00:00:00') ? sanitizeDate($wcFirstAttempt) : sanitizeDate($wcCompletionDate);
if($dosage==''){
    $drugName=$patient['patientDrugName'];
    if (preg_match('/(200|300)\s*mg/', $drugName, $matches)) {
    $dosage = $matches[1]; // This will be either 200 or 300
} else {
    $dosage = ''; // If no match is found
}
}

// 1. Days from Enrolment Received to First Outgoing Call
$daysFromEnrolmentReceivedToFirstOutgoingCall = getDayDifference($enrolmentFormReceivedDate, $wcFirstAttempt);
if($daysFromEnrolmentReceivedToFirstOutgoingCall== '-1'){
    $daysFromEnrolmentReceivedToFirstOutgoingCall='0';
}
                            
// 2. Days from Enrolment Received to Enrolment Complete
$daysFromEnrolmentReceivedToEnrolmentComplete = getDayDifference($enrolmentFormReceivedDate, $enrollmentCompleteDate);
if($daysFromEnrolmentReceivedToEnrolmentComplete== '-1'){
    $daysFromEnrolmentReceivedToEnrolmentComplete='0';
}                            
// 3. Days between Enrolment Complete and Reimbursement Complete
// (Assuming reimbursement complete date is stored in $rim['saEapOutcome'])
$daysBetweenEnrolmentCompleteToReimbursementComplete = getDayDifference($enrollmentCompleteDate, $rim['saEapOutcome']);
                            
// 4. Days from Initial SAPA Submitted to SA Approval
// $daysFromInitialSAPASubmittedToSAApproval = getDayDifference($dAppealSub, $rim['saEapOutcome']);
$daysFromInitialSAPASubmittedToSAApproval='';
if(!empty($patient['InitialSub']) && $patient['InitialSub']!='0000-00-00' && !empty($patient['InitialAproved']) && $patient['InitialAproved']!='0000-00-00'){
    $daysFromInitialSAPASubmittedToSAApproval = getDayDifference($patient['InitialSub'], $patient['InitialAproved']);
}
                            
// 5. Days from Reimbursement Complete to On Drug Start
$daysFromReimbursementCompleteToOnDrugStart = getDayDifference($rim['saEapOutcome'], $trtStartDateFinal);
         
if(!empty($trtStartDateFinal) && $trtStartDateFinal!='0000-00-00'){
    $daysFromEnrolmentCompleteToOnDrugStart = getDayDifference($enrollmentCompleteDate, $trtStartDateFinal); 
}
else{
    $daysFromEnrolmentCompleteToOnDrugStart='';
}
// 6. Number of Days On Drug
// If a discontinuation date is available, use that; otherwise, use today's date.
if (!empty($trtStartDateFinal) && $trtStartDateFinal!='0000-00-00') {
    $drugEndDate = !empty($discontinuedDate) ? $discontinuedDate : date('Y-m-d');
    $numberOfDaysOnDrug = getDayDifference($trtStartDateFinal, $drugEndDate);
 } else {
    $numberOfDaysOnDrug = '';
}

if($numberOfDaysOnDrug!='' && $numberOfDaysOnDrug<0){
    $numberOfDaysOnDrug = '';
}

$PharmacyName = (isset($rim['pharmacyName']) && $rim['pharmacyName']!='')?$rim['pharmacyName']:'';

// if pharmacy is missing then set as treatment status and only show having pharmacy name
if($PharmacyName == ''){
    $PharmacyName=(isset($rim['trtStatus']) && $rim['trtStatus']!='' && $rim['trtStatus']=='On Treatment - Rx Connect')?'Rx Connect':'';
}

$dphsp_tertiary=$rim['dphsp_tertiary'];
$dphsp=$rim['dphsp'];

if($dphsp=='Y'){
    //valid
}
else{
    $insuranceType2='N/A';
    $ipal_secondary='N/A';
    $insuranceTypeStatus2='N/A';
    $coveragePercentage_secondary='N/A';
}

if($dphsp_tertiary=='Y'){
    //valid
}
else{
    $insuranceType3='N/A';
    $ipal_tertiary='N/A';
    $insuranceTypeStatus3='N/A';
    $coveragePercentage_tertiary='N/A';
}

    $data[] = [
        'patientuId'=> $patient['patientuId'],
        'therapy'=> $patient['Therapy'], 
        'gender'=> $gender,
        'patientWeight'=> preg_replace('/[^\d.]/', "", $patientWeight),
        'age'=> $age, 
        'FSA'=> getFSA($patient['postalCode']),
        'province'=> getProvinceCode($patient['province']),
        'wc1Status'=> $patient['wc1Status'],
        'trtStatus'=> empty($patientStatus)?$rim['trtStatus']:($patientStatus!='false')?$patientStatus:'',
        'patientSubStatus'=> (empty($patientSubStatus) || $patientSubStatus!='false' || $patientSubStatus!=false)?$patientSubStatus:'', 
        'enrolmentFormReceivedDate'=> sanitizeDate($enrolmentFormReceivedDate),
        'wcFirstAttempt'=> (isset($wcFirstAttempt) && $wcFirstAttempt!='' && $wcFirstAttempt!='0000-00-00 00:00:00') ? sanitizeDate($wcFirstAttempt) : sanitizeDate($wcCompletionDate),
        'wcCompletionDate'=> sanitizeDate($wcCompletionDate), 
        'enrollmentCompleteDate'=> sanitizeDate($enrollmentCompleteDate),
        'saEapOutcome'=> sanitizeDate($rim['saEapOutcome']),
        'trtStartDateFinal'=> $trtStartDateFinal,
        'discontinuedDate'=> sanitizeDate($discontinuedDate),
        'lastName'=> $physician['lastName'],
        'firstName'=> $physician['firstName'],
        'hospital'=> $physician['hospital'],
        'primarySpecialty'=> $physician['primarySpecialty'],
        'streetAddress'=> $physician['streetAddress'],
        'city'=> $physician['city'],
        'province'=> getProvinceCode($physician['province']),
        'postalCode'=> $physician['postalCode'],
        'pharmacyName'=> $PharmacyName,
        'treatmentFacilityName'=> $rim['treatmentFacilityName'],
        'indication'=> $patient['indication'],
        'dopd'=> sanitizeDate($dopd),
        'enorllmentFormReceivedDate'=> sanitizeDate($enorllmentFormReceivedDate),
        'ldosage'=> ($patient['ldosage']=='')?'N/A':$patient['ldosage'],
        'lfrequency'=> ($patient['lfrequency']=='')?'N/A':$patient['lfrequency'], 
        'dosage'=> $patient['dosage'],
        'currentFrequency'=> $patient['currentFrequency'], 
        'reimbursementStatus'=> $sanofiPatientType,
        'reimbursementReason'=> $remreason,
        'planType1'=> $insuranceType,
        'payerName1'=> $ipal, 
        'payerStatus1'=> $insuranceTypeStatus,
        'Coverage1'=> sanitizePercentage($coveragePercentage),
        'planType2'=> $insuranceType2, 
        'payerName2'=> $ipal_secondary,
        'payerStatus2'=> $insuranceTypeStatus2,
        'Coverage2'=> sanitizePercentage($coveragePercentage_secondary),
        'planType3'=> $insuranceType3,
        'payerName3'=> $ipal_tertiary,
        'payerStatus3'=> $insuranceTypeStatus3, 
        'Coverage3'=> sanitizePercentage($coveragePercentage_tertiary),
        'daysFromEnrolmentReceivedToFirstOutgoingCall'=> $daysFromEnrolmentReceivedToFirstOutgoingCall, 
        'daysFromEnrolmentReceivedToEnrolmentComplete'=> $daysFromEnrolmentReceivedToEnrolmentComplete,
        'daysBetweenEnrolmentCompleteToReimbursementComplete'=> $daysBetweenEnrolmentCompleteToReimbursementComplete,
        'daysFromInitialSAPASubmittedToSAApproval'=> $daysFromInitialSAPASubmittedToSAApproval,
        'daysFromReimbursementCompleteToOnDrugStart'=> $daysFromReimbursementCompleteToOnDrugStart, 
        'daysFromEnrolmentCompleteToOnDrugStart'=> $daysFromEnrolmentCompleteToOnDrugStart,
        'numberOfDaysOnDrug'=> $numberOfDaysOnDrug,
        'firstInjDateOffered'=> sanitizeDate($firstInjDateOffered),
        'rcby'=> $rcby,
        'StaffName' => $patient['StaffName'],
        'secondPlan' => $dphsp,
        'thirdPlan' => $dphsp_tertiary
    ];
    
}

$jsonOptions = JSON_HEX_TAG | JSON_HEX_AMP | JSON_HEX_APOS | JSON_HEX_QUOT;
$jsonPatients = json_encode($data, $jsonOptions);

if ($jsonPatients === false) {
    error_log("JSON Error: " . json_last_error_msg());
    $jsonPatients = '[]';
}

?>

<!DOCTYPE html>
<html>
<head>
    <title>Entries Grid</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://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>

    <!-- jQuery and Kendo UI JS -->
    <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;
		}
		body{
		    overflow:hidden;
		}
		.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;
    }
    #mf_grid {
        width: 100%;        
        overflow-x: auto;   
    }
    th td{
        width: 100%;    
    }
	</style>
	<body>
   
<div class="k-content">
       <div id="mf_grid"></div>
</div>
    <script>

$(document).ready(function() {
    // Use PHP-generated JSON data as the grid's data source
    var data = <?php echo $jsonPatients; ?>;
    
    // Helper: calculate width based on field name length
    function calculateColumnWidth(fieldName, sampleData) {
        var base = 80; // minimum width
        var nameLength = fieldName.length * 10; // scale by field name length
        var sampleLength = sampleData ? sampleData.toString().length * 8 : 0;
        return Math.max(base, nameLength, sampleLength);
    }

    // Build columns dynamically
    var fields = Object.keys(data[0]); // get all field names from first record
    var columns = fields.map(function(field) {
        return {
            field: field,
            title: field.replace(/([A-Z])/g, ' $1'), // prettify camelCase
            width: calculateColumnWidth(field, data[0][field]),
            hidden: false // or true depending on your defaults
        };
    });
    
    // Initialize the Kendo UI Grid
    $("#mf_grid").kendoGrid({
        dataSource: {
            data: data,
            pageSize: 200
        },
        height: 650,
        sortable: true,
        filterable: true,
        columnMenu: true, // Enable column visibility menu
        pageable: true,
        resizable: true, 
        scrollable: true,
        schema: {
          model: {
            fields: {
                    enrolmentFormReceivedDate: { type: "date" },
                    wcFirstAttempt: { type: "date" },
                    wcCompletionDate: { type: "date" },
                    enrollmentCompleteDate: { type: "date" },
                    saEapOutcome: { type: "date" },
                    ReimbursementNavigationCompleteDate: { type: "date" },
                    trtStartDateFinal: { type: "date" },
                    OnDrugStartDateWithProgram: { type: "date" },
                    discontinuedDate: { type: "date" },
                    dopd: { type: "date" },
                    EnrolmentFormReceivedDateDuplicate: { type: "date" },
                    MostRecentReactivationFormReceivedDate: { type: "date" },
                    CommercializationDate: { type: "date" },
                    firstInjDateOffered: { type: "date" }
            }
          }
        },
         toolbar: ["excel"],
        excel: {
            fileName: "FK_Patient_Detailed_Report.xlsx",
            filterable: true,
            allPages: true
        },
        columns: [
        { field: 'patientuId',title:"Patient Number", width: 200},
        { field: 'therapy',title:"Therapy", width: 200}, 
        { field: 'gender',title:"Gender", width: 200},
        { field: 'patientWeight', title:"Weight (kg)", width: 200, hidden: true},
        { field: 'age', title:"Patient Current Age", width: 200}, 
        { field: 'FSA', title:"Patient FSA", width: 200},
        { field: 'province', title:"Patient Province", width: 200},
        { field: 'wc1Status',title:"Welcome Call Status", width: 200},
        { field: 'trtStatus', title:"Patient Current Program Status", width: 200},
        { field: 'patientSubStatus', title:"Patient Current Program Sub Status",hidden: true, width: 200}, 
        { field: 'enrolmentFormReceivedDate',title:"Enrollment Form Received Date", width: 200},
        { field: 'wcFirstAttempt', title:"First Outgoing Call Date",hidden: true, width: 200},
        { field: 'wcCompletionDate', title:"First Successful Contact Date",hidden: true, width: 200}, 
        { field: 'enrollmentCompleteDate', title:"Enrollment Complete Date",hidden: true, width: 200},
        // { field: 'saEapOutcome', title:"First SA Approval Date",hidden: true, width: 200},
        { field: 'trtStartDateFinal',title:"On Drug Start Date",hidden: true, width: 200},
        // { field: 'discontinuedDate', title:"Most Recent Discontinued Date",hidden: true, width: 200},
        { field: 'lastName', title:"Physician Last Name",hidden: true, width: 200},
        { field: 'firstName', title:"Physician First Name",hidden: true, width: 200},
        // { field: 'hospital', title:"Physician Hospital/Site",hidden: true, width: 200},
        { field: 'primarySpecialty', title:"Physician Primary Specialty",hidden: true, width: 200},
        { field: 'streetAddress', title:"Physician Street Address",hidden: true, width: 200},
        { field: 'city', title:"Physician City",hidden: true, width: 200},
        { field: 'province', title:"Physician Province",hidden: true, width: 200},
        { field: 'postalCode', title:"Physician Postal Code",hidden: true, width: 200},
        { field: 'pharmacyName', title:"Pharmacy Name",hidden: true, width: 200},
        // { field: 'treatmentFacilityName', title:"Treatment Facility Name",hidden: true, width: 200},
        { field: 'indication', title:"Primary Diagnosis",hidden: true, width: 200},
        { field: 'dopd', title:"Date Of Primary Diagnosis",hidden: true, width: 200},
        // { field: 'enorllmentFormReceivedDate', title:"First Prescription Date",hidden: true, width: 200},
        { field: 'ldosage',title:"Starting Dose",hidden: true, width: 200},
        { field: 'lfrequency', title:"Starting Frequency",hidden: true, width: 200}, 
        { field: 'dosage', title:"Current Dose",hidden: true, width: 200},
        { field: 'currentFrequency', title:"Current Frequency",hidden: true, width: 200}, 
        { field: 'reimbursementStatus', title:"Reimbursement Status",hidden: true, width: 200},
        { field: 'reimbursementReason',title:"Reimbursement Reason",hidden: true, width: 200},
        { field: 'planType1', title:"Plan Type 1",hidden: true, width: 200},
        { field: 'payerName1', title:"Payer Name 1",hidden: true, width: 200}, 
        { field: 'payerStatus1', title:"Payer Status 1",hidden: true, width: 200},
        { field: 'Coverage1', title:"Coverage % 1",hidden: true, width: 200},
        { field: 'planType2', title:"Plan Type 2",hidden: true, width: 200}, 
        // { field: 'secondPlan', title:"Has Plan 2",hidden: true, width: 200}, 
        { field: 'payerName2', title:"Payer Name 2",hidden: true, width: 200},
        { field: 'payerStatus2', title:"Payer Status 2",hidden: true, width: 200},
        { field: 'Coverage2', title:"Coverage % 2",hidden: true, width: 200},
        // { field: 'thirdPlan', title:"Has Plan 3",hidden: true, width: 200}, 
        { field: 'planType3', title:"Plan Type 3",hidden: true, width: 200},
        { field: 'payerName3', title:"Payer Name 3",hidden: true, width: 200},
        { field: 'payerStatus3', title:"Payer Status 3",hidden: true, width: 200}, 
        { field: 'Coverage3', title:"Coverage % 3",hidden: true, width: 200},
        // { field: 'daysFromEnrolmentReceivedToFirstOutgoingCall', title:"Days From Enrolment Received to First Outgoing Call",hidden: true, width: 200}, 
        // { field: 'daysFromEnrolmentReceivedToEnrolmentComplete', title:"Days from Enrolment Received to Enrolment Complete",hidden: true, width: 200},
        // { field: 'daysBetweenEnrolmentCompleteToReimbursementComplete', title:"Days between Enrolment Complete to Reimbursement Complete",hidden: true, width: 200},
        // { field: 'daysFromInitialSAPASubmittedToSAApproval', title:"Days from Initial SA/PA Submitted Date to SA Approval Date",hidden: true, width: 200},
        // { field: 'daysFromReimbursementCompleteToOnDrugStart', title:"Days from Reimbursement complete and On Drug Start",hidden: true, width: 200}, 
        //  { field: 'daysFromEnrolmentCompleteToOnDrugStart', title:"Days from Enrolment complete and On Drug Start",hidden: true, width: 200}, 
        { field: 'numberOfDaysOnDrug', title:"Number of Days On Drug",hidden: true, width: 200},
        // { field: 'firstInjDateOffered', title:"First Offered Injection Date",hidden: true, width: 200},
        { field: 'StaffName', title:"Program Coordinator",hidden: true, width: 200}
        ]
    });
});
</script>
</body>
</html>
