<?php  
// Include main file
include_once('../libs/main.php');
// Check billable Login
include_once('checkLogin.php');

// Logical section of page
$billableObj =  new  billable();


if (isset($_REQUEST['act']) && $_REQUEST['act']=='load'){
    
    $records = $_REQUEST['length'];
    $start=0;
    if(isset($_GET['start']) && $_GET['start']!='0'){
        $page = $_GET['start'];
    }
    $start=($page-1)*$limit;
    $limit = array('start'=>$_GET['start'],'record'=>$records);
    
    $where = " 1=1 ";

    if(isset($_REQUEST['unique_id']) && $_REQUEST['unique_id'] != '')
    {
        $where .= " AND appointment_unique_id = '".$_REQUEST['unique_id']."'";
    }
    if(isset($_REQUEST['clinic_id']) && $_REQUEST['clinic_id'] != '')
    {
        $where .= " AND  appointment_clinic_id = '".$_REQUEST['clinic_id']."' ";
    }
    if(isset($_REQUEST['dependant_id']) && $_REQUEST['dependant_id'] != '')
    {
        $where .= " AND  appointment_dependant_id = '".$_REQUEST['dependant_id']."' ";
    }
    if (isset($_REQUEST['start_date']) && $_REQUEST['start_date']!="" && isset($_REQUEST['end_date']) && $_REQUEST['end_date']!="" ){
        $where .= " AND  appointment_date BETWEEN '".$_REQUEST['start_date']."' AND '".$_REQUEST['end_date']."' ";
    }
    
    if (isset($_REQUEST['start_time']) && $_REQUEST['start_time']!="" && isset($_REQUEST['end_time']) && $_REQUEST['end_time']!="" )
    {
        
        $cstart_time = date("h:i A",strtotime($_REQUEST['start_time']));
        $cend_time = date("h:i A",strtotime($_REQUEST['end_time']));
        
        $where .= " AND  (STR_TO_DATE(appointment_time, '%l:%i %p' ) >= STR_TO_DATE('".$cstart_time."', '%l:%i %p' ) AND STR_TO_DATE(appointment_etime, '%l:%i %p' ) <= STR_TO_DATE( '".$cend_time."', '%l:%i %p' ) ) ";
    }
    if(isset($_REQUEST['appointment_status']) && $_REQUEST['appointment_status'] != '')
    {
        $where .= " AND  appointment_status = '".$_REQUEST['appointment_status']."' ";
    }
    if(isset($_REQUEST['billable_month']) && $_REQUEST['billable_month'] != '')
    {
        $where .= " AND  billable_month = '".$_REQUEST['billable_month']."' ";
    }
    if(isset($_REQUEST['billable_year']) && $_REQUEST['billable_year'] != '')
    {
        $where .= " AND  billable_year = '".$_REQUEST['billable_year']."' ";
    }
    if(isset($_REQUEST['billable_status']) && $_REQUEST['billable_status'] != '')
    {
        $where .= " AND billable_status = '".$_REQUEST['billable_status']."' ";
    }
    if(isset($_REQUEST['billable_email']) && $_REQUEST['billable_email'] != '')
    {
        $where .= " AND (clinic_email = '".$_REQUEST['billable_email']."' OR cdoctor_email = '".$_REQUEST['billable_email']."' OR patient_email_address = '".w::e($_REQUEST['billable_email'])."')";
    }
    if(isset($_REQUEST['billable_phone']) && $_REQUEST['billable_phone'] != '')
    {
        $where .= " AND (clinic_phone = '".$_REQUEST['billable_phone']."' OR cdoctor_phone = '".$_REQUEST['billable_phone']."' OR patient_phone = '".w::e($_REQUEST['billable_phone'])."')";
    }

    if(isset($_REQUEST['firstname']) && $_REQUEST['firstname'] != '' && $_REQUEST['lastname'] != '')
    {
        $firstname = trim($_REQUEST['firstname']);
        $lastname = trim($_REQUEST['lastname']);

        $where .= " AND ((p.patient_fname='".w::e($firstname)."' and   p.patient_lname='".w::e($lastname)."') OR (p.patient_fname='".w::e(ucfirst($firstname))."' and   p.patient_lname='".w::e(ucfirst($lastname))."'))";
    }else if(isset($_REQUEST['firstname']) && $_REQUEST['firstname'] != '' && $_REQUEST['lastname'] == ''){

        $firstname = trim($_REQUEST['firstname']);

        $where .= " AND ((p.patient_fname LIKE '%".w::e($firstname)."%') OR (p.patient_fname LIKE '%".w::e(ucfirst($firstname))."%' ))";
    }else if(isset($_REQUEST['lastname']) && $_REQUEST['lastname'] != '' && $_REQUEST['firstname'] == ''){

        $lastname = trim($_REQUEST['lastname']);

        $where .= " AND ((p.patient_lname LIKE '%".w::e($lastname)."%') OR ( p.patient_lname LIKE '%".w::e(ucfirst($lastname))."%'))";
    }
        
    /*if(isset($_REQUEST['patient_fullname']) && $_REQUEST['patient_fullname'] != '')
    {
        $patientname = preg_replace('/\s\s+/', ' ', $_REQUEST['patient_fullname']);
        $fullname = explode(" ", $patientname);
        if($fullname && count($fullname)>0)
        {
            $where ="";
            foreach ($fullname as $key => $f) 
            {
                $where .=" patient_fname LIKE '%".w::e($f)."%' OR patient_fname LIKE '%".w::e(ucfirst($f))."%'  OR patient_lname LIKE '%".w::e($f)."%' OR patient_lname LIKE '%".w::e(ucfirst($f))."%'";
                if( ($key+1) != count($fullname) )
                {
                        $where .=" OR ";
                }
            }
        }
    }*/
    if(isset($_REQUEST['billable_id']) && $_REQUEST['billable_id'] != '' )
    {
        $where .= " AND billable_id = '".$_REQUEST['billable_id']."' ";
    }
    if(isset($_REQUEST['appointment_id']) && $_REQUEST['appointment_id'] != '')
    {
        $where .= " AND appointment_id = '".$_REQUEST['appointment_id']."'";
    }

    if (isset($_REQUEST['search']['value']) && $_REQUEST['search']['value']!=""){
        $where = " billable_id LIKE '%".$_REQUEST['search']['value']."%' OR a.appointment_unique_id LIKE '%".$_REQUEST['search']['value']."%' OR a.appointment_id LIKE '%".$_REQUEST['search']['value']."%' OR  a.appointment_clinic_id LIKE '%".$_REQUEST['search']['value']."%' OR  a.appointment_doctor_id LIKE '%".$_REQUEST['search']['value']."%' OR  a.appointment_patient_id LIKE '%".$_REQUEST['search']['value']."%' OR  a.appointment_dependant_id LIKE '%".$_REQUEST['search']['value']."%' OR  a.appointment_appointmenttype_id LIKE '%".$_REQUEST['search']['value']."%' OR dep.dependant_fname LIKE '%".w::e($_REQUEST['search']['value'])."%' OR dep.dependant_lname LIKE '%".w::e($_REQUEST['search']['value'])."%' OR c.clinic_name LIKE '%".$_REQUEST['search']['value']."%' OR b.billable_month LIKE '%".$_REQUEST['search']['value']."%' OR b.billable_year LIKE '%".$_REQUEST['search']['value']."%' OR b.billable_price LIKE '%".$_REQUEST['search']['value']."%' OR b.billable_tax LIKE '%".$_REQUEST['search']['value']."%' OR b.billable_status LIKE '%".$_REQUEST['search']['value']."%' OR a.appointment_status LIKE '%".$_REQUEST['search']['value']."%' OR at.appointmenttype_name LIKE '%".$_REQUEST['search']['value']."%' OR b.billable_reconciledStatus LIKE '%".$_REQUEST['search']['value']."%' OR p.patient_id LIKE '%".$_REQUEST['search']['value']."%' OR p.patient_fname LIKE '%".w::e($_REQUEST['search']['value'])."%' OR p.patient_lname LIKE '%".w::e($_REQUEST['search']['value'])."%' OR p.patient_email_address LIKE '%".w::e($_REQUEST['search']['value'])."%' OR p.patient_phone LIKE '%".w::e($_REQUEST['search']['value'])."%' OR p.patient_unique_id LIKE '%".$_REQUEST['search']['value']."%' OR a.appointment_date LIKE '%".$_REQUEST['search']['value']."%' OR a.appointment_time LIKE '%".$_REQUEST['search']['value']."%' OR dep.dependant_fname LIKE '%".w::e(ucwords($_REQUEST['search']['value']))."%' OR dep.dependant_lname LIKE '%".w::e(ucwords($_REQUEST['search']['value']))."%' OR dep.dependant_fname LIKE '%".w::e(ucfirst($_REQUEST['search']['value']))."%' OR dep.dependant_lname LIKE '%".w::e(ucfirst($_REQUEST['search']['value']))."%' OR p.patient_fname LIKE '%".w::e(ucfirst($_REQUEST['search']['value']))."%' OR p.patient_lname LIKE '%".w::e(ucfirst($_REQUEST['search']['value']))."%' OR p.patient_fname LIKE '%".w::e(ucwords($_REQUEST['search']['value']))."%' OR p.patient_lname LIKE '%".w::e(ucwords($_REQUEST['search']['value']))."%' OR  p.patient_email_address LIKE '%".w::e(ucfirst($_REQUEST['search']['value']))."%' OR p.patient_email_address LIKE '%".w::e(ucwords($_REQUEST['search']['value']))."%' OR c.clinic_unique_id LIKE '%".$_REQUEST['search']['value']."%' OR c.clinic_phone LIKE '%".$_REQUEST['search']['value']."%' OR c.clinic_email LIKE '%".$_REQUEST['search']['value']."%' OR cd.cdoctor_fullname LIKE '%".$_REQUEST['search']['value']."%' OR cd.cdoctor_email LIKE '%".$_REQUEST['search']['value']."%' OR cd.cdoctor_phone LIKE '%".$_REQUEST['search']['value']."%' OR cd.cdoctor_provider_id LIKE '%".$_REQUEST['search']['value']."%' ";
    }    

    $orderFields = array('billable_id','clinic_id','patient_id');
    
    // Array of order by 
    $orderby=array();
    if (isset($_REQUEST['order']['0']['column']) && $_REQUEST['order']['0']['column']!=""){
        $orderby = array(" ".$orderFields[$_REQUEST['order']['0']['column']]." ".$_REQUEST['order']['0']['dir']);
    }else{
        $orderby = array(" billable_id desc ");
    }
    
    $fields  = array('a.*','c.*','dep.*','b.*','d.*','apt.*','p.patient_id','p.patient_fname','p.patient_lname','p.patient_phone','p.patient_email_address','p.patient_unique_id','cd.cdoctor_fullname','cd.cdoctor_provider_id','cd.cdoctor_email','cd.cdoctor_phone');
    $tables = array('tbl_appointment a','tbl_clinic c','tbl_doctor d','tbl_dependant dep','tbl_appointmenttype apt','tbl_patient p','tbl_cdoctor cd');
    $jfields = array('a.appointment_id=b.billable_appointment_id','a.appointment_clinic_id=c.clinic_id','a.appointment_doctor_id=d.doctor_id','a.appointment_dependant_id=dep.dependant_id','apt.appointmenttype_id=a.appointment_appointmenttype_id',
        'a.appointment_patient_id=p.patient_id',
        'cd.cdoctor_doctor_id=a.appointment_doctor_id');
    $jtype = array("INNER","LEFT","LEFT","LEFT","LEFT" ,"LEFT","LEFT");
    $data   = array('fields' => $fields, 'where' => $where,  'joinTables' =>$tables, 'joinFields' =>$jfields, 'joinTypes' => $jtype,'orderBy'=>$orderby,'limit' => $limit);

    $billableDetails = $billableObj->getAllRecords($data);
    //print_r($billableDetails);exit;
    $recordsTotal   =   $billableObj->getAllCount($data);
     
    if ($billableDetails && count($billableDetails)>0){
        foreach ($billableDetails as $key => $billable){

            if(!is_null($billable->appointment_assignTo))
            {
                $patientObj = new patient();
                $where = 'patient_id IN ('.$billable->appointment_assignTo.')';
                $data = array('where' => $where);
                $patientDetail = $patientObj->getAllRecords($data);
                //print_r($patientDetail);exit;
                $assign_detail = '';
                foreach ($patientDetail as $key => $patientdata) {
                    
                    $assign_detail .= "<b>Id: </b>".$patientdata->patient_id."<br/><b>External Id: </b>".$patientdata->patient_unique_id."<br/><b>First Name: </b>".w::d($patientdata->patient_fname)."<br/><b>Last Name: </b>".w::d($patientdata->patient_lname)."<br/><b>Email: </b>".w::d($patientdata->patient_email_address)."<br/><b>Phone: </b>".w::d($patientdata->patient_phone)."<br/><br/>";

                }

            }
            //<b>ID : </b>".$billable->billable_id."<br/>
            $response[] = array( 
                    "<b>Appointment Id: </b>".$billable->appointment_id."<br/><b>Appointment External Id: </b>".$billable->appointment_unique_id."<br/><b>Clinic Id: </b>".$billable->appointment_clinic_id."<br/><b>Provider Id: </b>".$billable->appointment_doctor_id."<br><b>Sign In Id: </b>".$billable->appointment_patient_id."<br/><b>Patient Id: </b>".$billable->appointment_dependant_id."<br/><b>AppointmentType: </b>".$billable->appointment_appointmenttype_id,

                    "<b>First Name: </b>".w::d($billable->dependant_fname)."<br/><b>Last Name: </b>".w::d($billable->dependant_lname)."<br/><b>Clinic Name: </b>".$billable->clinic_name."<br/><b>Clinic External Id : </b>".$billable->clinic_unique_id."<br/><b>Clinic Phone: </b>".$billable->clinic_phone."<br/><b>Clinic Email: </b>".$billable->clinic_email."<br/><b>Provider Name: </b>".$billable->cdoctor_fullname."<br/><b>Provider External Id : </b>".$billable->cdoctor_provider_id."<br/><b>Provider Email: </b>".$billable->cdoctor_email."<br/><b>Provider Phone: </b>".$billable->cdoctor_phone,

                    

                    "<b>PatientId: </b>".$billable->patient_id."<br/><b>Patient External Id: </b>".$billable->patient_unique_id."<br/><b>First Name: </b>".w::d($billable->patient_fname)."<br/><b>Last Name: </b>".w::d($billable->patient_lname).'<br/><b>Email: </b>'.w::d($billable->patient_email_address)."<br/><b>Phone: </b>".w::d($billable->patient_phone),

                    $assign_detail,
                    
                    //$billable->appointment_date." ".$billable->appointment_time,

                    "<b>Month: </b>".$billable->billable_month."<br/><b>Year: </b>".$billable->billable_year."<br/><b>Price: </b>".$billable->billable_price."<br/><b>Tax: </b>".$billable->billable_tax."<br/><b>Billable: </b>".$billable->billable_status."<br/><b>Appointment: </b>".$billable->appointment_status."<br/><b>Appointment Type: </b>".$billable->appointmenttype_name."<br/><b>ReconciledStatus: </b>".$billable->billable_reconciledStatus."<br><b>AppointmentDate: </b>".$billable->appointment_date." ".$billable->appointment_time."<br><b>Length: </b>".$billable->appointment_length,

                    "<b><a href='billableappointmentdetail.php?type=billable&appoint_id=".$billable->billable_appointment_id."'>View</a></b>",
                    
 
            );
        }
        $result=array('recordsFiltered'=>$recordsTotal,'recordsTotal'=>$recordsTotal,'data'=>$response);
    }else{
        $result=array('recordsFiltered'=>0,'recordsTotal'=>0,'data'=>array());
    }
    echo json_encode($result);
    exit;
}

// Include Template 
include_once('../libs/routs.php');
?>