<?php

class Barrister{
	private $_conn = null;
	private $_res = null;
	private $_userId = null;
	
    public function __construct($conn, $userId){
		$this->_conn = $conn;
		$this->_userId = $userId;
        // date_timezone_set('Europe/London');
	}

    public function getBarristers($filter){

        $query_string = "SELECT `barristers`.`id`, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as fullname, `barristers`.`email`, `barristers`.`secureEmail`, `barristers`.`mobile`, `barristers`.`telephone`, `barristers`.`companyName`, `barristers`.`legalAidNumber` , `barristers`.`yearOfQol` , `barristers`.`areaOfPracticeId` ,`barristers`.`colorCode` FROM `barristers`, `titles` WHERE `barristers`.`titleId` = `titles`.`id` ";

        if($filter['accessType'] != 'Solicitor'){
            $query_string .= " AND `barristers`.`directAccess` = '".$filter['accessType']."'"; 
        }
        if($filter['active'] != 'all'){
            $query_string .= " AND `barristers`.`active` = '".$filter['active']."'"; 
        }

        $query_string .= " ORDER BY `barristers`.`fname` ";

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

        if($query_barristers->num_rows > 0){ 

            $data = [];
            while($row = $query_barristers->fetch_assoc()){

                $practiceName = [];
                //area of practices
                $practiceIds = explode(',', $row['areaOfPracticeId']);
                foreach($practiceIds as $pId){
                    $query_practice = $this->_conn->query("SELECT `id`, `name` FROM practices WHERE `id` = '".$pId."'");
                    $row_practice = $query_practice->fetch_assoc();
                    $practiceName[] = $row_practice['name'];
                }

                $row['practiceName'] = $practiceName;

                //update about cases
                // get Pending case
                $query_pending = $this->_conn->query("SELECT COUNT(id) as total FROM `cases` WHERE `barristerId` = '".$row['id']."' AND `status` = '0'");
                $row_pending = $query_pending->fetch_assoc();
                $row['pending'] = $row_pending['total'];
                $query_all = $this->_conn->query("SELECT COUNT(id) as total FROM `cases` WHERE `barristerId` = '".$row['id']."' AND `status` = '1'");
                $row_all = $query_all->fetch_assoc();
                $row['all'] = $row_all['total'];
                $data[] = $row;
            }
            $this->_res = ['status'=>'ok', 'message'=>'', 'data'=>$data];
        }else{
            $this->_res = ['status'=>'error', 'message'=>'No Barrister found!', 'data'=>$query_string];
        }
        return $this->_res;
    }


    public function checkBarristerAvailibility($filter){

        $query_cases = $this->_conn->query("SELECT `id` FROM `cases` WHERE `barristerId` = '".$filter['id']."' AND ('".$filter['courtDate']."' BETWEEN `courtDate` AND `endDate`) AND `cases`.`active` = '1'");
        if($query_cases->num_rows > 0){
            $this->_res = ['status'=>'error', 'message'=>'Selected Barrister already has an active case!, Please consider checking the Court Date and End Date', 'data'=>null];
        }else{

            $query_holiday = $this->_conn->query("SELECT `id` FROM `barristerHolidays` WHERE `barristerId` = '".$filter['id']."' AND ('".$filter['courtDate']."' BETWEEN `unavailableFrom` AND `unavailableTo`)");
            if($query_holiday->num_rows > 0){
                $this->_res = ['status'=>'error', 'message'=>'Selected Barrister is not available!, Please consider checking the Court Date and End Date', 'data'=>null];
            }else{
                $this->_res = ['status'=>'ok', 'message'=>'', 'data'=>null];
            }
        }
        return $this->_res;
    }

    public function getBarrister($p){
        $query_string = "SELECT `barristers`.*, `titles`.`name` as title, CONCAT(`users`.`fname`, ' ', `users`.`lname`) as username  FROM `barristers`, `users`, `titles` WHERE `barristers`.`addedBy` = `users`.`id` AND `barristers`.`id` = '".$p['id']."' AND `barristers`.`titleId` = `titles`.`id`";

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

        if($query_barrister->num_rows == 1){

            $row = $query_barrister->fetch_assoc();
            $practiceName = [];
            $practices = [];
            //area of practices
            $practiceIds = explode(',', $row['areaOfPracticeId']);
            foreach($practiceIds as $pId){
                $query_practice = $this->_conn->query("SELECT `id`, `name` FROM practices WHERE `id` = '".$pId."'");
                $row_practice = $query_practice->fetch_assoc();
                $practiceName[] = $row_practice['name'];
                $practices[] = $row_practice;
            }
            $row['practices'] = $practices;
            $row['practiceName'] = $practiceName;

            switch( $row['status']) {
                case '0' :
                    $statusText = 'Inactive';
                    $statusColor = 'danger';
                    break;
                case '1' : 
                    $statusText = 'Active';
                    $statusColor = 'primary';
                    break;
            }

            $row['statusText'] = $statusText;
            $row['statusColor'] = $statusColor;

            switch( $row['directAccess']) {
                case '0' :
                    $accessText = 'No Direct Access';
                    break;
                case '1' : 
                    $accessText = 'Direct Access';
                    break;
            }

            $row['accessText'] = $accessText;


            //get pending cases
            $query_pending = $this->_conn->query("SELECT * FROM `cases` WHERE `barristerId` = '".$p['id']."' AND `status` = '0'");
            if($query_pending->num_rows > 0){
                $cases = [];
                while($row_pending = $query_pending->fetch_assoc()){
                    $cases[] = $row_pending;
                }
            }

            $row['cases'] = $cases;

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

    public function getHolidays($data){
        $query_string = "SELECT `barristerHolidays`.*, concat(`users`.`fname`, ' ', `users`.`lname`) as userName, concat(`barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName FROM barristerHolidays, barristers,  users WHERE barristerHolidays.addedBy = users.id AND barristerHolidays.barristerId = barristers.id";
        if($data['barristerId'] != 'all'){
            $query_string .= " AND `barristerHolidays`.`barristerId` = '".$data['barristerId']."'";
        }

        if($data['id'] != 'all'){
            $query_string .= " AND `barristerHolidays`.`id` = '".$data['id']."'";
        }

        $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'=>'error', 'message'=>'No Holiday found', 'data'=>$query_string]; 
        }
        return $this->_res;
    }

    public function saveHoliday($data){
        $query_check = $this->_conn->query("SELECT * FROM `barristerHolidays`, `cases` WHERE `cases`.`barristerId` = `barristers`.`id` AND (`cases`.`startDate` BETWEEN '".$data['unavailableFrom']."' AND '".$data['unavailableTo']."' OR `cases`.`endDate` BETWEEN '".$data['unavailableFrom']."' AND '".$data['unavailableTo']."' ) AND `cases`.`active` = 1 ");
        if($query_check->num_rows > 0){
            $this->_res = ['status'=>'error', 'message'=>'You have an active case during your requested date!', 'data'=>null];
        }else{
            $dateNow = date('Y-m-d');
            $query = $this->_conn->query("INSERT INTO barristerHolidays(`barristerId`, `unavailableFrom`, `unavailableTo`, `reason`, `addedBy`, `dateAdded`) VALUES('".$data['barristerId']."', '".$data['unavailableFrom']."', '".$data['unavailableTo']."', '".$data['reason']."', '".$this->_userId."', '".$dateNow."')");
            if($query){
                $dateActivity = date('Y-m-d : h:i');

                $activity = 'Barrister availability has been recorded to the system. Unavailable from .'.date_format($data['unavailableFrom'], 'd-m-Y').'. to '.date_format($data['unavailableTo'], 'd-m-Y').'  ( Updated  By : '.$row_username['userName'].')';
                $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$data['barristerId']."', 'barrister', '".$this->_userId."', '".$dateActivity."', '".$activity."')");

                $this->_res = ['status'=>'ok', 'message'=>'Record saved', 'data'=>null];
            }else{
                $this->_res = ['status'=>'error', 'message'=>'Could not save record!', 'data'=>null];
            }
        }
        
        return $this->_res;
    }

    public function updateHoliday($id, $data){
        $query = $this->_conn->query("UPDATE barristerHolidays SET unavailableFrom = '".$data['unavailableFrom']."', unavailableTo = '".$data['unavailableTo']."', reason = '".$data['reason']."' WHERE id = '".$id."'");
        if($query){
            $query_barristerId = $this->_conn->query("SELECT barristerId FROM barristerHolidays WHERE id = '".$id."'");
            $row_barristerId = $query_barristerId->fetch_assoc();
            $dateActivity = date('Y-m-d : h:i');

            $activity = 'Barrister availability has been updated to the system. Unavailable from .'.date_format($data['unavailableFrom'], 'd-m-Y').'. to '.date_format($data['unavailableTo'], 'd-m-Y').'  ( Updated  By : '.$row_username['userName'].')';
            $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$row_barristerId['barristerId']."', 'barrister', '".$this->_userId."', '".$dateActivity."', '".$activity."')");

            $this->_res = ['status'=>'ok', 'message'=>'Record Updated', 'data'=>null];
        }else{
            $this->_res = ['status'=>'error', 'message'=>'Could not update record!', 'data'=>null];
        }
        return $this->_res;
    }

    public function deleteHoliday($data){

        $query_auth = $this->_conn->query("SELECT `password` FROM `users` WHERE `id` = '".$this->_userId."'");
        $row_auth = $query_auth->fetch_assoc();
        $password = md5($data['password']);

        if($password == $row_auth['password']){

            $query_barristerId = $this->_conn->query("SELECT barristerId, unavailableFrom, unavailableTo FROM barristerHolidays WHERE id = '".$id."'");
            $row_barristerId = $query_barristerId->fetch_assoc();
            
            $query_delete = $this->_conn->query("DELETE FROM barristerHolidays WHERE id = '".$data['id']."'");
            if($query_delete){

                $dateActivity = date('Y-m-d : h:i');

                $activity = 'Barrister availability has been deleted. Previous record was : Unavailable from .'.date_format($row_barristerId['unavailableFrom'], 'd-m-Y').'. to '.date_format($row_barristerId['unavailableTo'], 'd-m-Y').'  ( Updated  By : '.$row_username['userName'].')';
                $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$row_barristerId['barristerId']."', 'barrister', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
    
                $this->_res = ['status'=>'ok', 'message'=>'Record Deleted!', 'data'=>null];
            }else{
                $this->_res = ['status'=>'error', 'message'=>'Could Not delete Record', 'data'=>null];
            }
        }else{
            $this->_res = ['status'=>'error', 'message'=>'Authorisation failed. Password did not match!', 'data'=>$data];
        }

        return $this->_res;
    }



    public function saveBarrister($data){
        $practices = [];
		foreach($data['practices'] as $practice){
			$practices[] = $practice['id']; 
		}

		$practiceIds = implode(',', $practices); 

        $dateNow = date('Y-m-d');
        $query_string = "INSERT INTO `barristers` (`titleId`,`fname`, `lname`, `email`, `secureEmail`, `mobile`, `telephone`, `directAccess`, `companyName`, `companyAddress`, `companyPostcode`, `vatNo`, `legalAidNumber`, `yearOfQol`, `areaOfPracticeId`, `zoomLink`, `homeAddress`, `homePostcode`, `note`,`colorCode`, `dateAdded`, `addedBy`) VALUES('".$data['titleId']."', '".$data['fname']."', '".$data['lname']."', '".$data['email']."', '".$data['secureEmail']."', '".$data['mobile']."', '".$data['telephone']."', '".$data['directAccess']."', '".$data['companyName']."', '".$data['companyAddress']."', '".$data['companyPostcode']."', '".$data['vatNo']."', '".$data['legalAidNumber']."', '".$data['yearOfQol']."', '".$practiceIds."', '".$data['zoomLink']."', '".$data['homeAddress']."', '".$data['homePostcode']."', '".$data['notes']."','".$data['colorCode']."', '".$dateNow."', '".$this->_userId."')";
        $query = $this->_conn->query($query_string);
        if($query){
            $id = $this->_conn->insert_id;
            //create activity
            //activityUser
            $query_username = $this->_conn->query("SELECT CONCAT(`fname`, ' ', `lname`) as userName FROM `users` WHERE `id` = '".$this->_userId."'");
            $row_username = $query_username->fetch_assoc();
            $dateActivity = date('Y-m-d : h:i');
            $activity = $data['fname'].' '.$data['lname'].' has been added to the system. ( Added By : '.$row_username['userName'].')';
            $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'barrister', '".$this->_userId."', '".$dateActivity."', '".$activity."')");

            $this->_res = ['status'=>'ok', 'message'=>' :: Barrister saved nicely, Thanks', 'data'=>$id];
        }else{
            $this->_res = ['status'=>'error', 'message'=>' :: Could not save Barrister details, Internal server error!', 'data'=>$query_string];
        }
        
        return $this->_res;
    }


    public function generateReport($data){

        $string = "SELECT `cases`.`id`, `cases`.`clientTypeId`, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname` ) as barristerName, `cases`.`courtDate`, `cases`.`endDate`, `cases`.`agreedFees`, `cases`.`expence`, `agreedFees` - `commissionAmount` as agreedFees, `expence`, `cases`.`vat`, `caseTitles`.`name` as caseTitleName FROM `cases`, `caseTitles`, `barristers`, `titles` WHERE `cases`.`barristerId` = `barristers`.`id` AND `barristers`.`titleId` = `titles`.`id` AND `cases`.`courtDate` BETWEEN '".$data['dateFrom']."' AND '".$data['dateTo']."' AND `cases`.`caseTitleId` = `caseTitles`.`id`  ";
        if($data['barristerId'] != 'all'){
            $string .= " AND cases.barristerId = '".$data['barristerId']."'";
        }
        if($data['fundingTypeId'] != 'all'){
            $string .= " AND cases.fundingTypeId = '".$data['fundingTypeId']."'";
        }
        $string .= " AND `cases`.`active` = 1 GROUP BY `cases`.`id` ORDER BY `cases`.`courtDate` ";

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

        if($query->num_rows > 0){

            $totalFees = 0.00;
            $totalFeesVat = 0.00;
            $totalVatFees = 0.00;
            $totalVatExpence = 0.00;
            $totalExpence = 0.00;
            $totalVat = 0.00;

            $data= [];
            $counter = 1;

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

                if($row['clientTypeId']){
                    if($row['clientTypeId'] == 'Solicitor'){
                        $query_soli = $this->_conn->query("SELECT `solicitorCompany`.`companyName` FROM `solicitorCompany`, `cases` WHERE `cases`.`id` = '".$row['id']."' AND `cases`.`solicitorId` = `solicitorCompany`.`id`");
                        if($query_soli->num_rows == 1){
                            $row_soli = $query_soli->fetch_assoc();
                            $row['solicitorCompany'] = $row_soli['companyName'];
                        }
                    }else{
                        $query_client = $this->_conn->query("SELECT CONCAT(`clientFname`, ' ', `clientLname`) as clientName FROM clients, cases WHERE cases.id = '".$row['id']."' AND cases.clientId = clients.id");
                        if($query_client->num_rows == 1){
                            $row_client = $query_client->fetch_assoc();
                            $row['solicitorCompany'] = $row_client['clientName'];
                        }
                    }
                }

                $tempAgreed = number_format(($row['agreedFees'] * $row['vat']) / 100, 2);
                $tempExpence = number_format(($row['expence'] * $row['vat']) / 100, 2);
                
                //
                $row['vatAgreedFees'] = $tempAgreed;
                $row['vatExpence'] = $tempExpence;

                $totalFees += $row['agreedFees'];
                $totalExpence += $row['expence'];

                $totalVat += $tempAgreed + $tempExpence;
                $totalFeesVat += $row['agreedFees'] + $row['expence'] + $totalVat;
                $totalVatFees += $tempAgreed;
                $totalVatExpence += $tempExpence;
                $row['totalVat'] = $tempAgreed + $tempExpence;
                $row['totalFeesAndVat'] = $row['agreedFees'] + $row['expence'] + $totalVat;

                $row['index'] = $counter;
                $data[] = $row;
                $counter ++;
            }
            
            $response = [];
            $footerData =[];
            $footerData['totalFees'] = $totalFees;
            $footerData['totalFeesAndVat'] = $totalFeesVat;
            $footerData['totalVatFees'] = $totalVatFees;
            $footerData['totalExpVat'] = $totalVatExpence;
            $footerData['totalExpence'] = $totalExpence;
            $footerData['totalVat'] = $totalVat;

            $footerData['totalBalance'] = ($totalFees + $totalExpence) - $totalVat;
            
            $response['footerData'] = $footerData;
            $response['data'] = $data;

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

        }else{
            $this->_res = ['status'=>'error', 'message'=>'No Result found!', 'data'=>$string];            
        }
        return $this->_res;

    }

    public function updateBarrister($id, $data){
        $query_check = $this->_conn->query("SELECT `fname` FROM `barristers` WHERE `id` = '".$id."'");
        if($query_check->num_rows == 1){
            $practices = [];
            foreach($data['practices'] as $practice){
                $practices[] = $practice['id']; 
            }

            $practiceIds = implode(',', $practices); 

            $string = "UPDATE `barristers` SET `titleId` = '".$data['titleId']."',`fname` = '".$data['fname']."', `lname` = '".$data['lname']."', `email` = '".$data['email']."', `secureEmail` = '".$data['secureEmail']."', `mobile` = '".$data['mobile']."', `telephone` = '".$data['telephone']."', `directAccess` = '".$data['directAccess']."', `companyName` = '".$data['companyName']."', `companyAddress` = '".$data['companyAddress']."', `companyPostcode` = '".$data['companyPostcode']."', `vatNo` = '".$data['vatNo']."', `legalAidNumber` = '".$data['legalAidNumber']."', `yearOfQol` = '".$data['yearOfQol']."', `areaOfPracticeId` = '".$practiceIds."', `zoomLink` = '".$data['zoomLink']."', `homeAddress` = '".$data['homeAddress']."', `note` = '".$data['note']."', `active` =  '".$data['active']."' , `colorCode` = '".$data['colorCode']."' WHERE `id` = '".$id."'";
            $query = $this->_conn->query($string);
            if($query){
                
                $query_username = $this->_conn->query("SELECT CONCAT(`fname`, ' ', `lname`) as userName FROM `users` WHERE `id` = '".$this->_userId."'");
                $row_username = $query_username->fetch_assoc();
                $dateActivity = date('Y-m-d : h:i');
                $activity = $data['fname'].' '.$data['lname'].' details has been updated. ( Updated By : '.$row_username['userName'].')';
                $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'barrister', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
            

                $this->_res = ['status'=>'ok', 'message'=>' :: Barrister details updated nicely.','data'=>$id];
            }

        }else{
            $this->_res = ['status'=>'error', 'message'=>' :: No Barrister details found to update!', 'data'=>null];
        }
        return $this->_res;
    }


}


?>