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

$clinicIds =array(206);
$clinicObj = new clinic();
foreach ($clinicIds as $key => $clinicId) { 
    $clinic =$clinicObj->fromId($clinicId);
    $clinicName =$clinic->clinic_name;
    
    $zipname = str_replace(" ","-",$clinicName).'.zip';
    $zipObj = new ZipArchive;
    $zipObj->open($zipname, ZipArchive::CREATE);
    
    $treatment_plansObj = new treatment_plans();
    $fields = array('category','COUNT(dependant_id) AS dependant_count','SUM(amount) AS total_amount','COUNT(treatment_plans_id) AS total_plans');
    $where = ' clinic_id = '.$clinicId.' AND ( treatment_plan_status="Pending" OR treatment_plan_status="Scheduled" )  ';
    $groupBy = array('category');
    $orderBy = array('category ASC');
    $arg = array('fields' => $fields, 'where' => $where, 'groupBy' => $groupBy,'orderBy' => $orderBy);
    $allPlans = $treatment_plansObj->getAllRecords($arg);   
    
    if($allPlans && count($allPlans)>0){
        $delimiter = ","; 
        $filename =  "Category.csv";      
        // Create a file pointer 
        $f = fopen($filename, 'w');
        $fields = array('Category', 'Total Patient', 'Total Plans','Total Amount');  
        fputcsv($f, $fields, $delimiter); 
        foreach($allPlans as $key => $plan) {
            $lineData = array($plan->category, $plan->dependant_count,$plan->total_plans,$plan->total_amount); 
            fputcsv($f, $lineData, $delimiter);         
        }  
    }
    $zipObj->addFile($filename);
    
    $fields = array('subcategory','COUNT(dependant_id) AS dependant_count','SUM(amount) AS total_amount','COUNT(treatment_plans_id) AS total_plans');
    $groupBy = array('subcategory');
    $orderBy = array('subcategory ASC');
    $arg = array('fields' => $fields, 'where' => $where, 'groupBy' => $groupBy,'orderBy' => $orderBy);
    $allPlans = $treatment_plansObj->getAllRecords($arg);
    if ($allPlans && count($allPlans) > 0) {
        $delimiter = ","; 
        $filename =  "Sub-Category.csv";      
        // Create a file pointer 
        $f = fopen($filename, 'w');
        $fields = array('SubCategory', 'Total Patient', 'Total Plans','Total Amount');  
        fputcsv($f, $fields, $delimiter); 
        foreach ($allPlans as $key => $plan) {
            $lineData=array($plan->subcategory,$plan->dependant_count,$plan->total_plans,$plan->total_amount);
            fputcsv($f, $lineData, $delimiter);         
        }
    }
    $zipObj->addFile($filename);

    $fields = array('subcategory_desc','COUNT(dependant_id) AS dependant_count','SUM(amount) AS total_amount','COUNT(treatment_plans_id) AS total_plans');
    $groupBy = array('subcategory_desc');
    $orderBy = array('subcategory_desc ASC');
    $arg = array('fields' => $fields, 'where' => $where, 'groupBy' => $groupBy,'orderBy' => $orderBy);
    $allPlans = $treatment_plansObj->getAllRecords($arg);
    if ($allPlans && count($allPlans) > 0) {
        
        $delimiter = ","; 
        $filename =  "Sub-Sub-Category.csv";      
        // Create a file pointer 
        $f = fopen($filename, 'w');
        $fields = array('SubSubCategory', 'Total Patient', 'Total Plans','Total Amount');  
        fputcsv($f, $fields, $delimiter); 
        foreach ($allPlans as $key => $plan) {
            $lineData=array($plan->subcategory_desc,$plan->dependant_count,$plan->total_plans,$plan->total_amount);
            fputcsv($f, $lineData, $delimiter);         
        }
    }
    $zipObj->addFile($filename);

    $reminderObj = new reminder();
    $startDate  = date("Y-m-d");
    $past3Months  = date("Y-m-d", strtotime("-3 months"));
    $future3months = date("Y-m-d", strtotime("+3 months"));
    $fields = array('COUNT(dependant_id) AS dependant_count');
    $where ='clinic_id='.$clinicId.' AND due_date BETWEEN "'.$past3Months.'" AND "'.$startDate.'"';
    $arg = array('fields' => $fields, 'where' => $where);
    $reminder = $reminderObj->fromFields($arg);   
    if($reminder){
        $delimiter = ","; 
        $filename =  "overdue-reminder.csv";      
        // Create a file pointer 
        $f = fopen($filename, 'w');
        $fields = array('Total Overdue');  
        fputcsv($f, $fields, $delimiter);
        $lineData=array($reminder->dependant_count);
        fputcsv($f, $lineData, $delimiter);   
    }
    $zipObj->addFile($filename);

    $reminderObj = new reminder();
    $startDate  = date("Y-m-d");
    $past3Months  = date("Y-m-d", strtotime("-3 months"));
    $future3months = date("Y-m-d", strtotime("+3 months"));
    $fields = array('COUNT(dependant_id) AS dependant_count');
    $where ='clinic_id='.$clinicId.' AND due_date BETWEEN "'.$startDate.'" AND "'.$future3months.'"';
    $arg = array('fields' => $fields, 'where' => $where);
    $reminder = $reminderObj->fromFields($arg);   
    if($reminder){
        $delimiter = ","; 
        $filename =  "future-reminder.csv";      
        // Create a file pointer 
        $f = fopen($filename, 'w');
        $fields = array('Total Overdue');  
        fputcsv($f, $fields, $delimiter);
        $lineData=array($reminder->dependant_count);
        fputcsv($f, $lineData, $delimiter);   
    }
    $zipObj->addFile($filename);

    $startDate  = date("Y-m-d");
	$past60Days = date("Y-m-d", strtotime("-60 days"));
	$appointmentObj = new appointment();
	$query  ='SELECT COUNT(app.appointment_dependant_id) as dependant_count,
				(SELECT COUNT(*) FROM tbl_appointment AS app1 WHERE app1.appointment_dependant_id=app.appointment_dependant_id 
                                AND app1.appointment_clinic_id="'.$clinicId.'" AND app1.appointment_status IN (\'scheduled\')
                                AND app1.appointment_metadata NOT LIKE \'%"floating":true%\'
                                AND app1.appointment_date >"'.$startDate.'") AS featurecount from tbl_appointment AS app
						where app.appointment_clinic_id='.$clinicId.' AND app.appointment_status IN (\'scheduled\') 
                        AND appointment_metadata LIKE \'%"noshow":true%\' AND 
                        app.appointment_metadata NOT LIKE \'%"floating":true%\' AND 
                        app.appointment_date BETWEEN "'.$past60Days.'" AND "'.$startDate.'" HAVING featurecount=0';
    $arg  = array('query' => $query);
    $allAppointments = $appointmentObj->getAllRecords($arg);
    if($allAppointments && count($allAppointments)>0){
        
        $delimiter = ","; 
        $filename =  "No-Show.csv";      
        // Create a file pointer 
        $f = fopen($filename, 'w');
        $fields = array('Total No-Show');    
        fputcsv($f, $fields, $delimiter);
        foreach($allAppointments as $key => $appointment){
            $lineData=array($appointment->dependant_count);
            fputcsv($f, $lineData, $delimiter);   
        }
        $zipObj->addFile($filename);
    }   
   

    $query  ='SELECT COUNT(app.appointment_dependant_id) as dependant_count,
				(SELECT COUNT(*) FROM tbl_appointment AS app1 WHERE app1.appointment_dependant_id=app.appointment_dependant_id 
                AND app1.appointment_clinic_id="'.$clinicId.'" AND app1.appointment_status IN (\'scheduled\')
                    AND app1.appointment_metadata NOT LIKE \'%"floating":true%\'
                    AND app1.appointment_date >"'.$startDate.'") AS featurecount from tbl_appointment AS app
                    where app.appointment_clinic_id='.$clinicId.' AND app.appointment_status IN (\'cancelled\') 
                    AND app.appointment_date BETWEEN "'.$past60Days.'" AND "'.$startDate.'" HAVING featurecount=0';
    $arg  = array('query' => $query);
   
    $allAppointments = $appointmentObj->getAllRecords($arg);
    if($allAppointments && count($allAppointments)>0){
        foreach ($allAppointments as $cancelledkey => $appointment) {
            $delimiter = ","; 
            $filename =  "Cancelled.csv";      
            // Create a file pointer 
            $f = fopen($filename, 'w');
            $fields = array('Total Cancelled');        
            fputcsv($f, $fields, $delimiter);
            foreach($allAppointments as $key => $appointment){
                $lineData=array($appointment->dependant_count);
                fputcsv($f, $lineData, $delimiter);   
            }
        }	
        $zipObj->addFile($filename);
    }
    
$zipObj->close(); 
header('Content-Type: application/zip');
header('Content-disposition: attachment; filename='.$zipname);
header('Content-Length: ' . filesize($zipname));
readfile($zipname);
} 
exit; 

