<?php 
 // Include main file
include_once('../libs/main.php');

// Check dependant Login
include_once('checkLogin.php');
if(isset($_GET['type']) && $_GET['type']=="future"){

    $query="SELECT DISTINCT t.dependant_id FROM `tbl_treatment_plans` t
    INNER JOIN 
        tbl_dependant d 
        ON t.dependant_id = d.dependant_id 
    INNER JOIN tbl_clinic_patient cp
        ON cp.clinic_patient_depid= d.dependant_id AND cp.clinic_patient_cid=1878
    LEFT JOIN 
        tbl_appointment a 
        ON t.dependant_id = a.appointment_dependant_id
        AND a.appointment_date > CURDATE() 
        AND a.appointment_status IN ('scheduled') AND a.appointment_clinic_id=1878
    LEFT JOIN 
        tbl_appointment_procedure ap 
        ON a.appointment_id = ap.appointment_procedure_aid 
        AND ap.appointment_procedure_code IN (11111,11112,11113,11114,11117) 

    WHERE t.clinic_id = 1878 
        AND t.dependant_id IS NOT NULL
        AND a.appointment_id IS NULL 
        AND t.procedure_code IN (11111,11112,11113,11114,11117)
        AND t.treatment_plan_status='Pending'
        AND cp.is_active=1";
    // $arg['debug']=true;
    $arg  = array('query' => $query);
    $treatment_plansObj = new treatment_plans();
    $treatment_plansObj->resultAs='array';
    $allPlans = $treatment_plansObj->getAllRecords($arg);
    $allPatient = array_column($allPlans,'dependant_id');
    $allPatient = implode(",",$allPatient);

    $reminderObj = new reminder();
    $fields = array('d.dependant_fname as fname', 'd.dependant_lname as lname', 'r.due_date', 'r.interval_length as scaling');
    $tables = array('tbl_dependant d');
    $jfields = array('r.dependant_id = d.dependant_id AND r.dependant_id IN ('.$allPatient.')');
    $jtype = array("INNER");
    $where = "r.interval_length = 6 AND r.due_date >= CURDATE()  AND r.clinic_id = 1878 ";
    $arg = array(
        'fields' => $fields,
        'where' => $where,
        'joinTables' => $tables,
        'joinFields' => $jfields,
        'joinTypes' => $jtype
    );
    $allreminder = $reminderObj->getAllRecords($arg);
    // $fields = array('dependant_fname as fname', 'dependant_lname as lname', 'due_date', 'scaling_interval as scaling','patient_id');
    // $where = "r.scaling_interval = 3 AND r.due_date >= CURDATE()  AND r.clinic_id = 2  AND a.appointment_dependant_id IS NULL ";
    // $tables = array('tbl_appointment a','tbl_dependant d');
    // $jfields = array('r.dependant_id = a.appointment_dependant_id AND a.appointment_date > CURDATE()  AND (a.appointment_status="scheduled" OR a.appointment_status="completed") ','d.dependant_id = r.dependant_id');
    // $jtype = array("LEFT","INNER");
    // $arg = array(
    //     'fields' => $fields,
    //     'where' => $where,
    //     'joinTables' => $tables,
    //     'joinFields' => $jfields,
    //     'joinTypes' => $jtype
    // );
    // $allreminder = $reminderObj->getAllRecords($arg);
    // print_r("<pre>");
    // print_r($allreminder);
    // exit;
    if ($allreminder && count($allreminder) > 0) {
        $delimiter = ",";
        $filename = "Future-6-Months-Uptown-dental.csv";

        // Set headers for downloading the CSV file
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '";');

        // Create a file pointer connected to the output stream
        $f = fopen('php://output', 'w');

        // Write the column headers
        $fields = array('FIRST NAME', 'LAST NAME', 'Scaling', 'Due Date');
        fputcsv($f, $fields, $delimiter);

        // Write rows to the CSV
        foreach ($allreminder as $key => $r) {
            $lineData = array(
                w::d($r->fname),
                w::d($r->lname),
                $r->scaling,
                $r->due_date
            );
            fputcsv($f, $lineData, $delimiter);
        }

        fclose($f);
        exit();
    }

}
if(isset($_GET['type']) && $_GET['type']=="past"){

    // $reminderObj = new reminder();
    // $fields =array('dependant_fname as fname','dependant_lname as lname','due_date','scaling_interval as scaling','patient_id');
    // $where ="r.scaling_interval = 3 AND r.due_date <  CURDATE() AND r.clinic_id = 2 AND a.appointment_dependant_id IS NULL ";
    // $tables = array('tbl_appointment a','tbl_dependant d');
    // $jfields = array('r.dependant_id = a.appointment_dependant_id AND a.appointment_date > DATE_SUB(CURDATE(), INTERVAL 3 MONTH)  AND (a.appointment_status="scheduled" OR a.appointment_status="completed") ','d.dependant_id = r.dependant_id');
    // $jtype   = array("LEFT","INNER"); 
    // $arg =array('fields'=>$fields,'where'=>$where,'joinTables' =>$tables,'joinFields' =>$jfields,'joinTypes' => $jtype);
    // $allreminder =$reminderObj->getAllRecords($arg);
    
    $query="SELECT DISTINCT t.dependant_id FROM `tbl_treatment_plans` t
    INNER JOIN 
        tbl_dependant d 
        ON t.dependant_id = d.dependant_id 
    INNER JOIN tbl_clinic_patient cp
        ON cp.clinic_patient_depid= d.dependant_id AND cp.clinic_patient_cid=1878
    LEFT JOIN 
        tbl_appointment a 
        ON t.dependant_id = a.appointment_dependant_id
        AND a.appointment_date > CURDATE() 
        AND a.appointment_status IN ('scheduled') AND a.appointment_clinic_id=1878
    LEFT JOIN 
        tbl_appointment_procedure ap 
        ON a.appointment_id = ap.appointment_procedure_aid 
        AND ap.appointment_procedure_code IN (11111,11112,11113,11114,11117) 

    WHERE t.clinic_id = 1878 
        AND t.dependant_id IS NOT NULL
        AND a.appointment_id IS NULL 
        AND t.procedure_code IN (11111,11112,11113,11114,11117)
        AND t.treatment_plan_status='Pending'
        AND cp.is_active=1";
    // $arg['debug']=true;
    $arg  = array('query' => $query);
    $treatment_plansObj = new treatment_plans();
    $treatment_plansObj->resultAs='array';
    $allPlans = $treatment_plansObj->getAllRecords($arg);
    $allPatient = array_column($allPlans,'dependant_id');
    $allPatient = implode(",",$allPatient);

    $reminderObj = new reminder();
    $fields = array('d.dependant_fname as fname', 'd.dependant_lname as lname', 'r.due_date', 'r.interval_length as scaling');
    $tables = array('tbl_dependant d');
    $jfields = array('r.dependant_id = d.dependant_id AND r.dependant_id IN ('.$allPatient.')');
    $jtype = array("INNER");
    $where = "r.interval_length = 6 AND r.due_date < CURDATE()  AND r.clinic_id = 1878 ";
    $arg = array(
        'fields' => $fields,
        'where' => $where,
        'joinTables' => $tables,
        'joinFields' => $jfields,
        'joinTypes' => $jtype
    );
    $allreminder = $reminderObj->getAllRecords($arg);
    if($allreminder && count($allreminder)>0){
        $delimiter = ","; 
        $filename =  "Past-6-Months-Uptown-Dental.csv";     
        // Set headers for downloading the CSV file
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '";'); 

        // Create a file pointer 
        $f = fopen('php://output', 'w');
        $fields = array('FIRST NAME', 'LAST NAME', 'Scaling','Due Date'); 
        fputcsv($f, $fields, $delimiter); 
        foreach($allreminder as $key =>$r){
            $lineData = array(w::d($r->fname), w::d($r->lname),$r->scaling,$r->due_date); 
            fputcsv($f, $lineData, $delimiter);  
        }
        fclose($f);
        
    }
}
if(isset($_GET['type']) && $_GET['type']=="treatment"){
    $treatment_plansObj = new treatment_plans();
    // $fields = array("DISTINCT t.dependant_id","subcategory as category", "d.dependant_fname as fname", "d.dependant_lname as lname");
    // $tables = array('tbl_appointment a','tbl_dependant d');
    // $jfields = array('t.dependant_id = a.appointment_dependant_id AND a.appointment_date > CURDATE() AND a.appointment_status="scheduled" ','d.dependant_id = t.dependant_id',);
    // $jtype   = array("LEFT","INNER"); 
    // $orderBy = array('subcategory ASC');
    // $where ="clinic_id = 2 AND (subcategory = 'Major Restorative' OR subcategory = 'Implants' OR subcategory = 'Orthodontic')  AND procedure_date BETWEEN CURDATE() - INTERVAL 3 MONTH AND CURDATE() AND treatment_plan_status IN ('Pending') AND t.dependant_id IS NOT NULL  AND a.appointment_dependant_id IS NULL ";
    // $arg =array('fields'=>$fields,'where'=>$where,'joinTables' =>$tables,'joinFields' =>$jfields,'joinTypes' => $jtype,'orderBy'=>$orderBy);
    $query ='SELECT 
        t.dependant_id,
        t.subcategory as category,
    d.dependant_fname AS fname,
    d.dependant_lname AS lname

    FROM 
        tbl_treatment_plans t
    INNER JOIN 
        tbl_dependant d 
        ON t.dependant_id = d.dependant_id 
    INNER JOIN tbl_clinic_patient cp
    ON cp.clinic_patient_depid= d.dependant_id
    LEFT JOIN 
        tbl_appointment a 
        ON t.dependant_id = a.appointment_dependant_id
        AND a.appointment_date > CURDATE() 
        AND a.appointment_status IN ("scheduled")
    LEFT JOIN 
        tbl_appointment_procedure ap 
        ON a.appointment_id = ap.appointment_procedure_aid 
        AND ap.appointment_procedure_code = t.procedure_code
    WHERE 
        t.clinic_id = 2 
        AND t.dependant_id IS NOT NULL
        AND a.appointment_id IS NULL 
        AND t.procedure_code >= 10000 AND t.procedure_code < 90000
        AND t.treatment_plan_status="Pending"
        AND cp.is_active=1
        AND procedure_date BETWEEN "2024-06-01" AND "2024-11-30"
        -- AND (subcategory = "Major Restorative" OR subcategory = "Implants" OR subcategory = "Orthodontic")
        AND NOT EXISTS (
            SELECT 1 
            FROM tbl_treatment_plans t2 
            WHERE 
                t2.dependant_id = t.dependant_id 
                AND t2.subcategory = t.subcategory 
                AND t2.treatment_plans_id != t.treatment_plans_id 
                AND t2.clinic_id = 2 AND t2.treatment_plan_status IN ("Completed","Scheduled")
        )
    ';
    // $query ="SELECT DISTINCT t.dependant_id,t.subcategory as category,t.patient_id,t.procedure_code,d.dependant_fname AS fname,
    //     d.dependant_lname AS lname FROM `tbl_treatment_plans` t
    // INNER JOIN 
    //     tbl_dependant d 
    //     ON t.dependant_id = d.dependant_id 
    // INNER JOIN tbl_clinic_patient cp
    //     ON cp.clinic_patient_depid= d.dependant_id
    // LEFT JOIN 
    //     tbl_appointment a 
    //     ON t.dependant_id = a.appointment_dependant_id
    //     AND a.appointment_date > CURDATE() 
    //     AND a.appointment_status IN ('scheduled')
    // LEFT JOIN 
    //     tbl_appointment_procedure ap 
    //     ON a.appointment_id = ap.appointment_procedure_aid 
    //     AND ap.appointment_procedure_code IN (11111,11112,11113,11114,11117) 

    // WHERE t.clinic_id = 2 
    //     AND t.dependant_id IS NOT NULL
    //     AND a.appointment_id IS NULL 
    //     AND t.procedure_code IN (11111,11112,11113,11114,11117)
    //     AND t.treatment_plan_status='Pending'
    //     AND cp.is_active=1";
    // $arg['debug']=true;
    $arg  = array('query' => $query);
    $allPlans = $treatment_plansObj->getAllRecords($arg);
   
    if($allPlans && count($allPlans)>0){
        $delimiter = ","; 
        $filename =  "Past-6-Months-Treatmentplans-Roseland.csv";     
        // Set headers for downloading the CSV file
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '";'); 

        // Create a file pointer 
        $f = fopen('php://output', 'w');
        $fields = array('FIRST NAME', 'LAST NAME', 'Category'); 
        fputcsv($f, $fields, $delimiter); 
        foreach($allPlans as $key =>$p){
            $lineData = array(w::d($p->fname), w::d($p->lname),$p->category); 
            fputcsv($f, $lineData, $delimiter);  
        }
        fclose($f);
    }
}
if(isset($_GET['type']) && $_GET['type']=="overdue"){
    $appointmentObj = new appointment();
    // $appointmentObj->resultAs ="array";
    $fields =array('DISTINCT a.appointment_dependant_id as dependantId');
    $where ="a.appointment_clinic_id = 2
        AND a.appointment_status = 'completed'
        AND a.appointment_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND  DATE_SUB(CURDATE(), INTERVAL 9 MONTH)
        AND cp.is_active = 1
        AND a.appointment_dependant_id NOT IN (
            SELECT DISTINCT a2.appointment_dependant_id 
            FROM tbl_appointment a2
            INNER JOIN tbl_clinic_patient cp2 
            ON cp2.clinic_patient_depid = a2.appointment_dependant_id
            WHERE 
                a2.appointment_date > DATE_SUB(CURDATE(), INTERVAL 9 MONTH)
                AND a2.appointment_clinic_id = 2
                AND cp2.is_active = 1
                AND (
                    (a2.appointment_status IN ('scheduled', 'requested', 'waitlist') AND a2.appointment_metadata NOT LIKE '%\"floating\":true%') 
                    OR a2.appointment_status = 'completed'
                )
        )";
        $tables = array('tbl_clinic_patient cp');
        $jfields = array('cp.clinic_patient_depid = a.appointment_dependant_id');
        $jtype   = array("INNER"); 
        $arg =array('fields'=>$fields,'where'=>$where,'joinTables' =>$tables,'joinFields' =>$jfields,'joinTypes' => $jtype);
        $allDependant =$appointmentObj->getAllRecords($arg);
        
        if($allDependant && count($allDependant)>0){
            $delimiter = ","; 
            $filename =  "Overdue-9-12-Months";     
            // Set headers for downloading the CSV file
            header('Content-Type: text/csv');
            header('Content-Disposition: attachment; filename="' . $filename . '";'); 
    
            // Create a file pointer 
            $f = fopen('php://output', 'w');
            $fields = array('FIRST NAME', 'LAST NAME'); 
            fputcsv($f, $fields, $delimiter); 
            foreach($allDependant as $key =>$d){
                $dependantObj= new dependant();
                $dependant =$dependantObj->fromId($d->dependantId);
                $lineData = array(w::d($dependant->dependant_fname), w::d($dependant->dependant_lname)); 
                fputcsv($f, $lineData, $delimiter);  
            }
            fclose($f);
        }
}        
exit; 
