<?php

// require_once 'MailHelper.php';

class Home {


    private $_conn = null;
	private $_res = null;
	private $_userId = null;
    private $_mail = null;

	public function __construct($conn, $userId){
		$this->_conn = $conn;
		$this->_userId = $userId;
        // $this->_mail = new MailHelper($conn);

	}


    public function getCounts($filter){

        $this->getHomeCount($filter);
        return $this->_res;
    }

    private function getHomeCount($filter){
        // $this->_mail->connect();
        // $emails = [];
        // $emails[] = 'info@3boltcourt.com';
        // if($this->_mail->transmitEmail($emails, 'New Case Recorded in the System!', 'test body')){
        //     $this->_res = ['status'=>'ok', 'message'=>'Case details saved nicely, Email Send!', 'data'=>$id];
        // }else{
        //     $this->_res = ['status'=>'ok', 'message'=>'Case details saved nicely, Could not send Email!', 'data'=>$id];
        // }

        $data = [];

        //total fees 
        $query_all_fees = $this->_conn->query("SELECT SUM(agreedFees) as total FROM `cases` WHERE `cases`.`active` = '1' AND `cases`.`courtDate` < CURDATE()");
        $row_all_fees = $query_all_fees->fetch_assoc();
        $data['totalFees'] = $row_all_fees['total'];

        //total Paid to barrister
        $query_all_paid = $this->_conn->query("SELECT SUM(totalAmount) as total FROM `solicitorPayments`");
        $row_all_paid = $query_all_paid->fetch_assoc();
        $data['totalPaid'] = $row_all_paid['total'];

        //total Outstanding
        // $string = "SELECT CAST(SUM(solicitorInvoice.totalAmount) - SUM(solicitorInvoice.totalPaid) AS DECIMAL(11,2) ) as total FROM `solicitorInvoice`,`solicitorInvoiceItems`, `cases`, `solicitorCompany`, solicitors WHERE `solicitorInvoiceItems`.`dueDate` <= CURDATE() AND `solicitorInvoice`.`teamId` = `solicitors`.`id` AND `solicitors`.`companyId` = `solicitorCompany`.`id` AND `solicitorInvoiceItems`.`invoiceId` = `solicitorInvoice`.`id` AND `solicitorInvoiceItems`.`caseId` =`cases`.`id` AND `cases`.`active` = '1' ";
        $query_all_outstanding = $this->_conn->query("SELECT CAST(SUM(solicitorInvoice.totalAmount) - SUM(solicitorInvoice.totalPaid) AS DECIMAL(11,2) ) as total FROM `solicitorInvoice` WHERE solicitorInvoice.dueDate < CURDATE()");
        $row_all_outstanding = $query_all_outstanding->fetch_assoc();
        $data['totalOutstanding'] = $row_all_outstanding['total'];

        //total Commission
        $query_all_commission = $this->_conn->query("SELECT SUM(commissionAmount) as total FROM `cases` WHERE `endDate` < CURDATE()");
        $row_all_commission = $query_all_commission->fetch_assoc();
        $data['totalCommission'] = $row_all_commission['total'];

        //get all cases
        $string_total_cases = "SELECT COUNT(id) as total FROM `cases` WHERE `cases`.`active` = '1'";
        if($filter['barristerId'] != 'all'){
            $string_total_cases .= " AND `cases`.`barristerId` = '".$filter['barristerId']."'";
        }
        $query_all_case = $this->_conn->query($string_total_cases);
        $row_all_case = $query_all_case->fetch_assoc();
        $data['totalCases'] = $row_all_case['total'];

        //upcoming hearings
        $string_upcoming = "SELECT COUNT(id) as total FROM `cases` WHERE `courtDate` >= CURDATE() AND `cases`.`active` = '1'";
        if($filter['barristerId'] != 'all'){
            $string_upcoming .= " AND `cases`.`barristerId` = '".$filter['barristerId']."'";
        }
        $query_hearings = $this->_conn->query($string_upcoming);
        $row_hearings = $query_hearings->fetch_assoc();
        $data['upcomingHearings'] = $row_hearings['total'];

        //upcoming Conferences
        $query_conferences = $this->_conn->query("SELECT COUNT(id) as total FROM `meetings` WHERE `meetingDate` >= CURDATE()");
        $row_conferrences = $query_conferences->fetch_assoc();
        $data['upcomingConferences'] = $row_conferrences['total'];

        //court Listijng Due
        $string_listing = "SELECT COUNT(id) as total FROM `cases` WHERE `requestCourtListing` = 'Yes' AND `courtListingStatus` = 'In Progress' AND `cases`.`active` = '1'";
        if($filter['barristerId'] != 'all'){
            $string_listing .= " AND `cases`.`barristerId` = '".$filter['barristerId']."'";
        }
        $query_court_listing = $this->_conn->query($string_listing);
        $row_court_listing = $query_court_listing->fetch_assoc();
        $data['courtListingDue'] = $row_court_listing['total'];

        //court Bundle 
        $string_bundle = "SELECT COUNT(id) as total FROM `cases` WHERE `courtBundleReceived` = 'No' AND `cases`.`active` = '1'";
        if($filter['barristerId'] != 'all'){
            $string_bundle .= " AND `cases`.`barristerId` = '".$filter['barristerId']."'";
        }

        $query_court_bundle = $this->_conn->query($string_bundle);
        $row_court_bundle = $query_court_bundle->fetch_assoc();
        $data['courtBundleDue'] = $row_court_bundle['total'];

        //attendanceReceived
        $string_attendance = "SELECT COUNT(id) as total FROM `cases` WHERE `attendanceNoteReceived` = 'No' AND `courtDate` <= CURDATE() AND `cases`.`active` = '1'";
        if($filter['barristerId'] != 'all'){
            $string_attendance .= " AND `cases`.`barristerId` = '".$filter['barristerId']."'";
        }
        $query_court_attendance = $this->_conn->query($string_attendance);
        $row_court_attendance = $query_court_attendance->fetch_assoc();
        $data['attendeceDue'] = $row_court_attendance['total'];

        //additional Work
        $string_additional = "SELECT count(`cases`.`id`) as total FROM `cases`, `caseTitles` WHERE ((`cases`.`additionalWorkId` IS NOT NULL AND `cases`.`additionalWorkStatus` = 'Pending' ) OR ((`caseTitles`.`name` LIKE '%DRAFTING%' OR `caseTitles`.`name` LIKE '%PROVIDING%' OR `caseTitles`.`name` LIKE '%ASSISTING%' OR `caseTitles`.`name` LIKE '%AMENDING%') AND `cases`.`attendanceNoteReceived` != 'Yes' ))  AND `cases`.`active` = '1' AND `cases`.`caseTitleId` = `caseTitles`.`id`";
        if($filter['barristerId'] != 'all'){
            $string_additional .= " AND `cases`.`barristerId` = '".$filter['barristerId']."'";
        }
        $query_court_additional = $this->_conn->query($string_additional);
        $row_court_additional = $query_court_additional->fetch_assoc();

        $data['additionalWorkDue'] = $row_court_additional['total'] ;

        //additional Work
        $string_outstanding = "SELECT COUNT(id) as total FROM `cases` WHERE `outstandingDocuments` IS NOT NULL AND `outstandingDocumentReceived` = 'No' AND `cases`.`active` = '1'";
        if($filter['barristerId'] != 'all'){
            $string_outstanding .= " AND `cases`.`barristerId` = '".$filter['barristerId']."'";
        }
        
        $query_court_outstanding = $this->_conn->query($string_outstanding);
        $row_court_outstanding = $query_court_outstanding->fetch_assoc();
        $data['outstandingDocumentDue'] = $row_court_outstanding['total'];


        $chartData = [];
        $months = [];
        $totalCase = [];
        $totalFees = [];
        $totalPayments = [];

        for ($i = 0; $i <= 6; $i++) 
        {
            $dateTemp =  date('Y-m-d', strtotime('last day of ' . -$i . 'month'));
            $month = date('F', strtotime($dateTemp));
            $months[] = $month;
            //run query to check number of let only deal;
            $string_chart_total = "SELECT COUNT(cases.id) as totalCases FROM cases WHERE MONTH(cases.courtDate) = MONTH('".$dateTemp."') AND YEAR(cases.courtDate) = YEAR('".$dateTemp."') AND `cases`.`active` = '1'";
            if($filter['barristerId'] != 'all'){
                $string_chart_total .= " AND `cases`.`barristerId` = '".$filter['barristerId']."'";
            }
            $totalCases = $this->_conn->query($string_chart_total);
            $row_total_cases = $totalCases->fetch_assoc();
            $totalCase[] = $row_total_cases['totalCases'];

            $string_chart_commission = "SELECT SUM(commissionAmount) as totalCommission FROM cases WHERE MONTH(cases.courtDate) = MONTH('".$dateTemp."') AND YEAR(cases.courtDate) = YEAR('".$dateTemp."') AND `cases`.`active` = '1'";
            if($filter['barristerId'] != 'all'){
                $string_chart_commission .= " AND `cases`.`barristerId` = '".$filter['barristerId']."'";
            }
            $totalCommission = $this->_conn->query($string_chart_commission);
            $row_total_commission = $totalCommission->fetch_assoc();
            $totalFees[] = $row_total_commission['totalCommission'];

            $string_chart_barrister = "SELECT SUM(totalPaid) as totalPaid FROM barristerInvoice, barristers WHERE MONTH(barristerInvoice.invoiceDate) = MONTH('".$dateTemp."') AND YEAR(barristerInvoice.invoiceDate) = YEAR('".$dateTemp."') AND `barristerInvoice`.`barristerId` = `barristers`.`id` AND  `barristers`.`active` = '1'";
            if($filter['barristerId'] != 'all'){
                $string_chart_barrister .= " AND `barristers`.`id` = '".$filter['barristerId']."'";
            }
            $totalPayment = $this->_conn->query($string_chart_barrister);
            $row_total_payment = $totalPayment->fetch_assoc();
            $totalPayments[] = $row_total_payment['totalPaid'];

        }

        $chartData['months'] = $months;
        $chartData['totalCases'] = $totalCase;
        $chartData['totalFees'] = $totalFees;
        $chartData['totalPayments'] = $totalPayments;

        $data['chartData'] = $chartData;

        $pieDataLabel = [];
        $pieData = [];

        $query_pie = $this->_conn->query("SELECT * FROM `fundingTypes`");
        while($row_pie = $query_pie->fetch_assoc()){
            $pieDataLabel[] = $row_pie['name'];
            //get case number
            $string_pie_data = "SELECT COUNT(id) as total FROM cases WHERE `fundingTypeId` = '".$row_pie['id']."' AND `cases`.`active` = '1'";
            if($filter['barristerId'] != 'all'){
                $string_pie_data .= " AND `cases`.`barristerId` = '".$filter['barristerId']."'";
            }
            $query_pie_data = $this->_conn->query($string_pie_data);
            $row_pie_data = $query_pie_data->fetch_assoc();
            $pieData[] = $row_pie_data['total'];
        }

        $data['pieLabel'] = $pieDataLabel;
        $data['pieData'] = $pieData;

        $this->_res = ['status'=>'ok', 'message'=>'adadsas', 'data'=>$data];
        

    }

    public function searchNow($searchData){

        $query_string = "";

        switch ($searchData['tableName']) {
            case 'cases' :
                $query_string = "SELECT `cases`.`id`, CONCAT(`cases`.`courtRef`, ' - ',  `caseTitles`.`name`) as modelTitle, CONCAT('/pages/cases/view/',`cases`.`id`) as modelLink, CONCAT(`cases`.`claimant`,' - v -', `cases`.`defendant`, ' - ',`courts`.`courtName`, ' - ', DATE_FORMAT(`cases`.`courtDate`, '%d-%m-%Y'), ' - ', `barristers`.`fname`, ' ', `barristers`.`lname`) as modelDetails FROM `cases`, `courts`, `caseTitles`, `barristers` WHERE (`cases`.`courtRef` LIKE '%".$searchData['filter']."%' OR `cases`.`claimant` LIKE '%".$searchData['filter']."%' OR `cases`.`defendant` LIKE '%".$searchData['filter']."%') AND `cases`.`caseTitleId` = `caseTitles`.`id` AND `cases`.`courtId` = `courts`.`id` AND `cases`.`barristerId` = `barristers`.`id` AND `cases`.`active` = 1";
            break;
            case 'solicitorCompany' : 
                $query_string = "SELECT `solicitorCompany`.`id`, CONCAT(`solicitorCompany`.`companyName`, ' - ', `solicitorCompany`.`tradingName`) as modelTitle, CONCAT('/pages/solicitors/view/', `solicitorCompany`.`id`) as modelLink, CONCAT(`solicitorCompany`.`address`,' ', `solicitorCompany`.`city`, ' ', `solicitorCompany`.`postcode`, ' - ', `solicitorCompany`.`email`) as modelDetails FROM `solicitorCompany` WHERE (`solicitorCompany`.`companyName` LIKE '%".$searchData['filter']."%' OR `solicitorCompany`.`tradingName` LIKE '%".$searchData['filter']."%' OR `solicitorCompany`.`postcode` LIKE '%".$searchData['filter']."%' OR `solicitorCompany`.`email` LIKE '%".$searchData['filter']."%') AND `solicitorCompany`.`active` = 1";
            break;
            case 'barristers' :
                $query_string = "SELECT `barristers`.`id`, CONCAT(`titles`.`name`, ' ', `fname`, ' ', `lname`, ' - ', `companyName`) as modelTitle, CONCAT('/pages/barristers/view', `barristers`.`id`) as modelLink, CONCAT(`companyAddress`, ' ', `companyPostcode`, ' - ', `email`) as modelDetails FROM `barristers`, `titles` WHERE (`fname` LIKE '%".$searchData['filter']."%' OR `lname` LIKE '%".$searchData['filter']."%' OR `companyName` LIKE '%".$searchData['filter']."%' OR `companyPostcode` LIKE '%".$searchData['filter']."%' OR `email` LIKE '%".$searchData['filter']."%') AND `barristers`.`active` = 1 AND `barristers`.`titleId` = `titles`.`id`";
            break;
            case 'clients' :
                $query_string = "SELECT `clients`.`id`, CONCAT(`titles`.`name`, ' ', `clientFname`, ' ', `clientLname`,) as modelTitle, CONCAT('/pages/clients/view', `clients`.`id`) as modelLink, CONCAT(`clientAddress`, ' ', `clientPostcode`, ' - ', `clientMobile`) as modelDetails FROM `clients`, `titles` WHERE (`clientFname` LIKE '%".$searchData['filter']."%' OR `clientLname` LIKE '%".$searchData['filter']."%' OR `clientMobile` LIKE '%".$searchData['filter']."%' OR `clientEmail` LIKE '%".$searchData['filter']."%') AND `clients`.`active` = 1 AND `clients`.`clientTitle` = `titles`.`id`";
            break;
        }

        $query = $this->_conn->query($query_string);

        if($query->num_rows > 0){
            $data = [];

            while($row= $query->fetch_assoc()){
                $data[] = $row;
            }
            $this->_res = ['status'=>'ok', 'message'=>'', 'data'=>$data];
        }else{
            $this->_res = ['status'=>'ok', 'message'=>'', 'data'=>null];
        }

        
        return $this->_res;

    }

    public function getDiaryCount($filter){

        //count all task
        $string = "SELECT `cases`.`id`, 'case' as modelName, CONCAT(date_format(`cases`.`courtTime`,'%H:%i'), ' - ', `caseTitles`.`name`, ' (', `cases`.`timeEstimate`, ' )') as title, `cases`.`courtDate` as start, DATE_ADD(`cases`.`endDate`, INTERVAL 1 Day) as end,  `barristers`.`colorCode` as backgroundColor FROM `cases`, `caseTitles`, `barristers` WHERE `cases`.`caseTitleId` = `caseTitles`.`id` AND `cases`.`barristerId` = `barristers`.`id` AND `cases`.`active` = '1'";
        if($filter[0] != 'all'){
            if($filter[0] == '1'){
                $string .= " AND `cases`.`entryType` != '0' ";
            }else{
                $string .= " AND `cases`.`entryType` = '0' ";
            }
        }

        if($filter[1] != 'all'){
            $string .= " AND `cases`.`caseTypeId` = '".$filter[1]."' ";
        }

        if($filter[2] != 'all'){
            $string .= " AND `cases`.`clientTypeId` = '".$filter[2]."' ";
        }

        if($filter[3] != 'all'){
            $string .= " AND `cases`.`courtId` = '".$filter[3]."' ";
        }

        if($filter[4] != 'all'){
            $string .= " AND `cases`.`fundingTypeId` = '".$filter[4]."' ";
        }

        if($filter[5] != 'all'){
            $string .= " AND `cases`.`courtBundleReceived` = '".$filter[5]."' ";
        }

        if($filter[6] != 'all'){
            $string .= " AND `cases`.`attendanceNoteReceived` = '".$filter[6]."' ";
        }

        if($filter[7] != 'all'){
            $string .= " AND `cases`.`paymentReceived` = '".$filter[7]."' ";
        }

        if($filter[8] == 'selected'){
            $selectedIds = implode(',', $filter[9]);
            $string .= " AND  FIND_IN_SET(`cases`.`barristerId`, '".$selectedIds."') ";
        }
        
        
        $query = $this->_conn->query($string);
        if($query->num_rows > 0){
            $data = [];
            while($row = $query->fetch_assoc()){
                $data[] = $row;
            }
        }
        
        //create record of holidays
        $string_holiday = "SELECT `barristers`.`id`, 'holiday' as modelName, CONCAT( `barristers`.`fname`, ' ', `barristers`.`lname`, ' ( ', `barristerHolidays`.`reason`, ' ) ') as title, `unavailableFrom` as start,  DATE_ADD(`unavailableTo`, INTERVAL 1 Day) as end, `barristers`.`colorCode` as backgroundColor FROM `barristers`, `barristerHolidays` WHERE `barristerHolidays`.`barristerId` = `barristers`.`id`";
        if($filter[8] == 'selected'){
            $selectedIds = implode(',', $filter[9]);
            $string_holiday .= " AND  FIND_IN_SET(`barristers`.`id`, '".$selectedIds."') ";
        }
        
        $query_holidays = $this->_conn->query($string_holiday);
        if($query_holidays->num_rows > 0){
            while($row_holidays = $query_holidays->fetch_assoc()){
                $data[] = $row_holidays;
            }
        }

        $this->_res = ['status'=>'ok', 'message'=>$string, 'data'=>$data];
        return $this->_res;

    }


    public function getDiaryDetails($filter){
        
        $query_string = "";

        switch ($filter['type']){
            case 'upcomingHearings' :
                $query_string = "SELECT `cases`.`id`, `cases`.`clientId`, `cases`.`solicitorId`, `cases`.`teamId`, `caseTitles`.`name`, `cases`.`courtId`, `cases`.`claimant`, `cases`.`defendant`, `cases`.`behalfOf`, `cases`.`clientTypeId`, `courts`.`courtName`, `cases`.`courtDate`,  `cases`.`endDate`, date_format(`cases`.`courtTime`,'%H:%i') as courtTime, `cases`.`attendanceNoteReceived`, `cases`.`courtListingStatus`, `cases`.`additionalWorkStatus`, `cases`.`courtBundleReceived`,`cases`.`outstandingDocuments`, `cases`.`outstandingDocumentReceived`, `cases`.`paymentReceived`,`cases`.`status`, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName, `cases`.`status`, `caseStatus`.`name` as statusText FROM `cases`, `caseTitles`,`courts`, `barristers`, `titles`, `caseStatus` WHERE `cases`.`courtDate` >= CURDATE() AND `cases`.`caseTitleId` = `caseTitles`.`id` AND `cases`.`courtId` = `courts`.`id` AND `cases`.`barristerId` = `barristers`.`id` AND `barristers`.`titleId` = `titles`.`id` AND `cases`.`active` = '1' AND `cases`.`status` = `caseStatus`.`id`";
                if($filter['barristerId'] != 'all'){
                    $query_string .= " AND `cases`.`barristerId` = '".$filter['barristerId']."' ";
                }
                $query_string .= "ORDER BY `cases`.`courtDate`,  `cases`.`courtTime`";
                break;
            case 'courtListingDue' :
                $query_string = "SELECT `cases`.`id`, `cases`.`clientId`, `cases`.`solicitorId`, `caseTitles`.`name`, `cases`.`courtId`,`cases`.`claimant`, `cases`.`defendant`, `cases`.`behalfOf`, `courts`.`courtName`, `cases`.`courtDate`, `cases`.`endDate`,  date_format(`cases`.`courtTime`,'%H:%i') as courtTime, `cases`.`attendanceNoteReceived`, `cases`.`courtListingStatus`, `cases`.`additionalWorkStatus`, `cases`.`courtBundleReceived`, `cases`.`outstandingDocuments`, `cases`.`outstandingDocumentReceived`, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName FROM `cases`, `caseTitles`,`courts`, `barristers`, `titles` WHERE `cases`.`requestCourtListing` = 'Yes' AND `cases`.`courtListingStatus` = 'In Progress' AND `cases`.`caseTitleId` = `caseTitles`.`id` AND `cases`.`courtId` = `courts`.`id` AND `cases`.`barristerId` = `barristers`.`id` AND `barristers`.`titleId` = `titles`.`id` AND `cases`.`active` = '1'";
                if($filter['barristerId'] != 'all'){
                    $query_string .= " AND `cases`.`barristerId` = '".$filter['barristerId']."' ";
                }

                $query_string .= "ORDER BY `cases`.`courtDate`";
                break;
            case 'courtBundleDue' :
                $query_string = "SELECT `cases`.`id`, `cases`.`clientId`, `cases`.`solicitorId`, `caseTitles`.`name`, `cases`.`courtId`,`cases`.`claimant`, `cases`.`defendant`, `cases`.`behalfOf`, `courts`.`courtName`, `cases`.`courtDate`, `cases`.`endDate`, date_format(`cases`.`courtTime`,'%H:%i') as courtTime, `cases`.`attendanceNoteReceived`, `cases`.`courtListingStatus`, `cases`.`additionalWorkStatus`, `cases`.`courtBundleReceived`, `cases`.`outstandingDocuments`, `cases`.`outstandingDocumentReceived`, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName FROM `cases`, `caseTitles`,`courts`, `barristers`, `titles` WHERE `cases`.`courtBundleReceived` = 'No'  AND `cases`.`caseTitleId` = `caseTitles`.`id` AND `cases`.`courtId` = `courts`.`id` AND `cases`.`barristerId` = `barristers`.`id` AND `barristers`.`titleId` = `titles`.`id` AND `cases`.`active` = '1'";
                if($filter['barristerId'] != 'all'){
                    $query_string .= " AND `cases`.`barristerId` = '".$filter['barristerId']."' ";
                }

                $query_string .= "ORDER BY `cases`.`courtDate` ";
                break;
            case 'attendeceDue' :
                $query_string = "SELECT `cases`.`id`, `cases`.`clientId`, `cases`.`solicitorId`, `caseTitles`.`name`, `cases`.`courtId`,`cases`.`claimant`, `cases`.`defendant`, `cases`.`behalfOf`, `courts`.`courtName`, `cases`.`courtDate`, `cases`.`endDate`, date_format(`cases`.`courtTime`,'%H:%i') as courtTime, `cases`.`attendanceNoteReceived`, `cases`.`courtListingStatus`, `cases`.`additionalWorkStatus`, `cases`.`courtBundleReceived`, `cases`.`outstandingDocuments`, `cases`.`outstandingDocumentReceived`, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName FROM `cases`, `caseTitles`,`courts`, `barristers`, `titles` WHERE  `cases`.`attendanceNoteReceived` = 'No' AND  `cases`.`courtDate` <= CURDATE() AND `cases`.`caseTitleId` = `caseTitles`.`id` AND `cases`.`courtId` = `courts`.`id` AND `cases`.`barristerId` = `barristers`.`id` AND `barristers`.`titleId` = `titles`.`id` AND `cases`.`active` = '1'";
                if($filter['barristerId'] != 'all'){
                    $query_string .= " AND `cases`.`barristerId` = '".$filter['barristerId']."' ";
                }

                $query_string .= " ORDER BY `cases`.`courtDate`";
                break;
            case 'additionalWorkDue' :
                $query_string = "SELECT `cases`.`id`, `cases`.`clientId`, `cases`.`solicitorId`, `caseTitles`.`name`, `cases`.`courtId`,`cases`.`claimant`, `cases`.`defendant`, `cases`.`behalfOf`, `courts`.`courtName`, `cases`.`courtDate`, `cases`.`endDate`, date_format(`cases`.`courtTime`,'%H:%i') as courtTime, `cases`.`attendanceNoteReceived`, `cases`.`courtListingStatus`, `cases`.`additionalWorkStatus`, `cases`.`courtBundleReceived`, `cases`.`outstandingDocuments`, `cases`.`outstandingDocumentReceived`, `cases`.`additionalWorkId`, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName FROM cases, caseTitles, courts, titles,barristers  WHERE ((`cases`.`additionalWorkId` IS NOT NULL AND `cases`.`additionalWorkStatus` = 'Pending' ) OR ((`caseTitles`.`name` LIKE '%DRAFTING%' OR `caseTitles`.`name` LIKE '%PROVIDING%' OR `caseTitles`.`name` LIKE '%ASSISTING%' OR `caseTitles`.`name` LIKE '%AMENDING%') AND `cases`.`attendanceNoteReceived` != 'Yes' ))  AND `cases`.`active` = '1' AND `cases`.`caseTitleId` = `caseTitles`.`id` AND `cases`.`courtId` = `courts`.`id` AND `cases`.`barristerId` = `barristers`.`id` AND `barristers`.`titleId` = `titles`.`id`";
                if($filter['barristerId'] != 'all'){
                    $query_string .= " AND `cases`.`barristerId` = '".$filter['barristerId']."' ";
                }
                $query_string .=" order By courtDate";
                break;
            case 'outstandingDocumentDue' :
                $query_string = "SELECT `cases`.`id`, `cases`.`clientId`, `cases`.`solicitorId`, `caseTitles`.`name`, `cases`.`courtId`,`cases`.`claimant`, `cases`.`defendant`, `cases`.`behalfOf`, `courts`.`courtName`, `cases`.`courtDate`, `cases`.`endDate`, date_format(`cases`.`courtTime`,'%H:%i') as courtTime, `cases`.`attendanceNoteReceived`, `cases`.`courtListingStatus`, `cases`.`additionalWorkStatus`, `cases`.`courtBundleReceived`, `cases`.`outstandingDocuments`, `cases`.`outstandingDocumentReceived`, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName FROM `cases`, `caseTitles`,`courts`, `barristers`, `titles`, `outstandingDocuments` WHERE `cases`.`outstandingDocuments` IS NOT NULL AND `outstandingDocumentReceived` = 'No' AND   `cases`.`caseTitleId` = `caseTitles`.`id` AND `cases`.`courtId` = `courts`.`id` AND `cases`.`barristerId` = `barristers`.`id` AND `barristers`.`titleId` = `titles`.`id` AND `cases`.`active` = '1' AND `cases`.`outstandingDocuments` = `outstandingDocuments`.`id`";
                if($filter['barristerId'] != 'all'){
                    $query_string .= " AND `cases`.`barristerId` = '".$filter['barristerId']."' ";
                }

                $query_string .= " ORDER BY `cases`.`courtDate`";
                break;
        }

        $query = $this->_conn->query($query_string);

        //if the request if for additional work then check case titles

        if($filter['type'] == 'additionalWorkDue'){

            while($row = $query->fetch_assoc()){

                if($row['additionalWorkStatus'] != 'Pending'){
                    $row['additionalColor'] = 'primary';
                }else{
                    $row['additionalColor'] = 'danger';
                }

                if($row['attendanceNoteReceived'] == 'No'){
                    $row['attendanceColor'] = 'danger';
                }else{
                    $row['attendanceColor'] = 'primary';
                }

                if($row['courtBundleReceived'] == 'No'){
                    $row['bundleColor'] = 'danger';
                }else{
                    $row['bundleColor'] = 'primary';
                }
                
                if($row['courtListingStatus'] != 'In Progress'){
                    $row['listingColor'] = 'primary';
                }else{
                    $row['listingColor'] = 'danger';
                }

                if($row['additionalWorkId'] != '' && $row['additionalWorkId']){
                    $query_additional = $this->_conn->query("SELECT `name` as additionalWorkName FROM additionalWorks WHERE `id` = '".$row['additionalWorkId']."'");
                    $row_additional = $query_additional->fetch_assoc();
                    $row['additionalWorkName'] = $row_additional['additionalWorkName'];
                }else{
                    $row['additionalWorkName'] = null;
                }

                $data[] = $row;
            } 


            if(count($data) > 0){
                $this->_res = ['status'=>'ok', 'message'=>'', 'data'=>$data];
            }else{
                $this->_res = ['status'=>'error', 'message'=>'', 'data'=>null];
            }

        }else{
            if($query->num_rows > 0){
                $data = [];
                while($row = $query->fetch_assoc()){
    
                    //get clients
                    if($row['clientId']){
                        $query_client = $this->_conn->query("SELECT * FROM clients WHERE id = '".$row['clientId']."'");
                        $row_client = $query_client->fetch_assoc();
                        $row['client'] = $row_client;
                    }else{
                        $row['client'] = null; 
                    }
    
                    if($row['solicitorId']){
                        $query_solicitor = $this->_conn->query("SELECT * FROM solicitorCompany WHERE id = '".$row['solicitorId']."'");
                        $row_solicitor = $query_solicitor->fetch_assoc();
                        $row['solicitor'] = $row_solicitor;
                    }else{
                        $row['solicitor'] = null; 
                    }
                    
                    if($filter['type'] == 'upcomingHearings'){

                        $row['advancePayment'] = 'Payment Required';
                        if($row['clientTypeId'] != 1){
                            $query_soli_payment = $this->_conn->query("SELECT advancePayment FROM solicitorCompany WHERE id = '".$row['solicitorId']."'");
                            $row_soli_payment = $query_soli_payment->fetch_assoc();

                            if($row_soli_payment['advancePayment'] == '0'){
                                $query_team = $this->_conn->query("SELECT advancePayment FROM solicitors WHERE id = '".$row['teamId']."'");
                                $row_team = $query_team->fetch_assoc();
                                if($row_team['advancePayment'] == '0'){
                                    $row['advancePayment'] = 'Payment Not Required';
                                }
                            }
                        }

                        if($row['status'] < 4){
                            $row['statusColor'] = 'primary';
                        }else if($row['status'] > 6 && $row['status']< 10){
                            $row['statusColor'] = 'warning';
                        }else if ($row['status'] == 4){
                            $row['statusColor'] = 'warning';
                        }else{
                            $row['statusColor'] = 'danger';
                        }

                        if($row['paymentReceived'] == 'No' && $row['advancePayment'] == 'Payment Required'){
                            $row['advancePaymentColor'] = 'danger';
                        }else{
                            $row['advancePaymentColor'] = 'primary'; 
                        }

                    }
    
                    //get color corrected and status
                    if($row['attendanceNoteReceived'] == 'No'){
                        $row['attendanceColor'] = 'danger';
                    }else{
                        $row['attendanceColor'] = 'primary';
                    }
    
                    if($row['courtBundleReceived'] == 'No'){
                        $row['bundleColor'] = 'danger';
                    }else{
                        $row['bundleColor'] = 'primary';
                    }
                    if($row['additionalWorkStatus'] != 'Pending'){
                        $row['additionalColor'] = 'primary';
                    }else{
                        $row['additionalColor'] = 'danger';
                    }

                   

                    if($row['courtListingStatus'] != 'In Progress'){
                        $row['listingColor'] = 'primary';
                    }else{
                        $row['listingColor'] = 'danger';
                    }
    
                    if($row['outstandingDocumentReceived'] != 'No'){
                        $row['outstandingColor'] = 'primary';
                    }else{
                        $row['outstandingColor'] = 'danger';
                    }

                    if($row['outstandingDocuments'] != '' && $row['outstandingDocuments']){
                        $query_documents = $this->_conn->query("SELECT `name` as outstandingDocumentName FROM outstandingDocuments WHERE `id` = '".$row['outstandingDocuments']."'");
                        $row_document = $query_documents->fetch_assoc();
                        $row['outstandingDocumentName'] = $row_document['outstandingDocumentName'];
                    }else{
                        $row['outstandingDocumentName'] = null;
                    }

    
                    $data[] = $row;
                }
                $this->_res = ['status'=>'ok', 'message'=>'', 'data'=>$data];
            }else{
                $this->_res = ['status'=>'error', 'message'=>'No details found!', 'data'=>$query_string];
            }
        }


        return $this->_res;
    }

}

?>