<?php

require_once 'MailHelper.php';

class Cases{
	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);
        // date_timezone_set('Europe/London');
	}

    public function getCases($filter){

        

        $query_string = "SELECT `cases`.id,`cases`.`courtRef`, `cases`.`claimant`, `cases`.`defendant`, `cases`.`behalfOf`,  `cases`.`clientTypeId`, `cases`.`teamId`, `cases`.`solicitorId`, `cases`.`clientId`,  `cases`.`entryType`,`cases`.`courtDate`, `cases`.`endDate`, date_format(`cases`.`courtTime`,'%H:%i') as courtTimeText, `courts`.`courtName`,  `caseTitles`.`name` as caseTitleName, CONCAT(`barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName, `barristers`.`companyName`,  `caseTypes`.`name` as caseTypeName, `caseCategories`.`name` as caseCategoryName,   `fundingTypes`.`name` as fundingTypeName, `caseStatus`.`name` as statusText,   CONCAT(`users`.`fname`, ' ', `users`.`lname`) as username FROM cases, caseTypes, barristers, caseCategories, caseTitles, caseStatus, fundingTypes, courts, users WHERE  `cases`.`courtId` = `courts`.`id` AND `cases`.`barristerId` = `barristers`.`id` AND `cases`.`caseTypeId` = `caseTypes`.`id`  AND `cases`.`caseTitleId` = `caseTitles`.`id` AND `cases`.`categoryId` = `caseCategories`.`id` AND `cases`.`status` = `caseStatus`.`id` AND `cases`.`fundingTypeId` = `fundingTypes`.`id` AND `cases`.`addedBy` = `users`.`id` ";

        if($filter['status'] != 'all'){
            $query_string .= " AND `cases`.`status` = '".$filter['status']."' ";
        }

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

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

        if($filter['solicitorId'] != 'all'){
            $query_string .= " AND `cases`.`solicitorId` = '".$filter['solicitorId']."' ";
        }

        if($filter['clientId'] != 'all'){
            $query_string .= " AND `cases`.`clientId` = '".$filter['clientId']."' ";
        }

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

        if($filter['statusType'] != 'all'){
            if($filter['statusType'] == 'upcoming'){
                $query_string .= " AND `cases`.`courtDate` >= CURDATE() ";
            }else if($filter['statusType'] == 'past'){
                $query_string .= " AND `cases`.`courtDate` < CURDATE() ";
            }
        }

        $query_string .= " ORDER BY `cases`.`courtDate` ASC , `cases`.`courtTime` ASC, `cases`.`id` DESC ";
        

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

        if($query->num_rows > 0){

            $data = [];

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

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


                $data[] = $row;

            }

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

        }else{
            
            $this->_res = ['status'=>'error', 'message'=>' :: No case found', 'data'=>$query_string];
        
        }
        
        return $this->_res;
    }


    public function getCase($filter){

        $string = "SELECT `cases`.*,  date_format(`cases`.`courtTime`,'%H:%i') as courtTimeText, `caseTitles`.`name` as caseTitleName, `caseTypes`.`name` as caseTypeName, `caseCategories`.`name` as caseCategoryName, `fundingTypes`.`name` as fundingTypeName, `caseStatus`.`name` as statusText,   CONCAT(`users`.`fname`, ' ', `users`.`lname`) as username FROM cases, caseTypes, caseCategories, caseTitles, caseStatus, fundingTypes, users WHERE `cases`.`id` = '".$filter['id']."' AND `cases`.`caseTypeId` = `caseTypes`.`id`  AND `cases`.`caseTitleId` = `caseTitles`.`id` AND `cases`.`categoryId` = `caseCategories`.`id` AND `cases`.`status` = `caseStatus`.`id` AND  `cases`.`fundingTypeId` = `fundingTypes`.`id` AND `cases`.`addedBy` = `users`.`id` ";
        $query = $this->_conn->query($string);
        if($query->num_rows == 1){
            $row = $query->fetch_assoc();

            if($row['active'] == '1'){
                $row['activeText'] = 'Active';
                $row['activeColor'] = 'primary';
            }else{
                $row['activeText'] = 'Archieved';
                $row['activeColor'] = 'danger';
            }

            //expence type and additional work
            if($row['expenceTypeId'] && $row['expenceTypeId'] != 0){
                $query_expence = $this->_conn->query("SELECT `name` FROM `expenceTypes` WHERE `id` = '".$row['expenceTypeId']."'");
                $row_expence = $query_expence->fetch_assoc();
                $row['expenceTypeName'] = $row_expence['name'];
            }else{
                $row['expenceTypeName'] = ""; 
            }
            if($row['additionalWorkId'] && $row['additionalWorkId'] != 0){
                $query_expence = $this->_conn->query("SELECT `name` FROM `additionalWorks` WHERE `id` = '".$row['additionalWorkId']."'");
                $row_expence = $query_expence->fetch_assoc();
                $row['additionalWorkName'] = $row_expence['name'];
            }else{
                $row['additionalWorkName'] = ""; 
            }

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

            //get agreed fees
            $checkFees = strpos($row['agreedFees'], '-');
            if($checkFees === false){
                $row['fees'] = null;
            }else{
                $row['fees'] = $row['agreedFees'];
            }

            //get barrister details 
            $query_barrister = $this->_conn->query("SELECT barristers.*, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as fullname FROM barristers, titles WHERE `barristers`.`id` = '".$row['barristerId']."' AND `barristers`.`titleId` = `titles`.`id`");
            $row_barrister = $query_barrister->fetch_assoc();

            $row['barrister'] = $row_barrister;

            //get court 
            $query_court = $this->_conn->query("SELECT * FROM courts WHERE `id` = '".$row['courtId']."'");
            $row_court = $query_court->fetch_assoc();

            $row['court'] = $row_court;

            //get client or solicitor details 
            if($row['clientTypeId'] == 'Solicitor'){
                $query_solicitor = $this->_conn->query("SELECT * FROM solicitorCompany WHERE `id` = '".$row['solicitorId']."'");
                $row_solicitor = $query_solicitor->fetch_assoc();
                //get Team 
                $query_team = $this->_conn->query("SELECT * FROM solicitors WHERE `id` = '".$row['teamId']."'");
                $row_team = $query_team->fetch_assoc();
                
                if($row_solicitor['advancePayment'] == 0 ){
                    if($row_team['advancePayment'] == 1){
                        $row_solicitor['paymentColor'] = 'danger';
                        $row_solicitor['paymentText'] = 'Payment Required';
                    }else{
                        $row_solicitor['paymentColor'] = 'primary';
                        $row_solicitor['paymentText'] = 'Not Required';
                    }
                }else{
                    $row_solicitor['paymentColor'] = 'danger';
                    $row_solicitor['paymentText'] = 'Payment Required';
                }

                $temp = [];
                $temp['solicitor'] = $row_solicitor;
                $temp['team'] = $row_team;

                $row['solicitorCompany'] = $temp;

            }else{
                $query_client = $this->_conn->query("SELECT `clients`.*, `titles`.`name` as titleName, CONCAT(`users`.`fname`, ' ', `users`.`lname`) as username FROM `clients`, `titles`, `users` WHERE `clients`.`id` = '".$row['clientId']."' AND `clients`.`clientTitle` = `titles`.`id` AND `clients`.`addedBy` = `users`.`id`");
                $row_client = $query_client->fetch_assoc();
                $row['client'] = $row_client;
            }

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


    public function getCaseHistory($id){

        $data = [];
        

        $query_entry = $this->_conn->query("SELECT entryType FROM `cases` WHERE `id` = '".$id."'");
        $row_entry = $query_entry->fetch_assoc();
        if($row_entry['entryType'] != 0){
            
            $caseIds = explode(',', $row_entry['entryType']);

            foreach($caseIds as $caseId){

                $query = $this->_conn->query("SELECT `cases`.id,`cases`.`courtRef`, `cases`.`claimant`, `cases`.`defendant`, `cases`.`behalfOf`,  `cases`.`clientTypeId`, `cases`.`teamId`, `cases`.`solicitorId`, `cases`.`clientId`,  `cases`.`entryType`,`cases`.`courtDate`, date_format(`cases`.`courtTime`,'%H:%i') as courtTimeText,   `caseTypes`.`name` as caseTypeName, `fundingTypes`.`name` as fundingTypeName, `courts`.`courtName`, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName, `barristers`.`vatNo`, `barristers`.`legalAidNumber` FROM `cases`, `barristers`, `caseTypes`, `fundingTypes`, `courts`, `titles` WHERE `cases`.`id` = '".$caseId."' AND `cases`.`barristerId` = `barristers`.`id` AND `cases`.`courtId` = `courts`.`id` AND `cases`.`caseTypeId` = `caseTypes`.`id` AND `cases`.`fundingTypeId` = `fundingTypes`.`id` AND `barristers`.`titleId` = `titles`.`id` AND `cases`.`active`= '1'");
                if($query->num_rows == 1){
                    $row = $query->fetch_assoc();

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


        }
        $temp_duplicated = "SELECT `cases`.id,`cases`.`courtRef`, `cases`.`claimant`, `cases`.`defendant`, `cases`.`behalfOf`,  `cases`.`clientTypeId`, `cases`.`teamId`, `cases`.`solicitorId`, `cases`.`clientId`,  `cases`.`entryType`,`cases`.`courtDate`, date_format(`cases`.`courtTime`,'%H:%i') as courtTimeText,   `caseTypes`.`name` as caseTypeName, `fundingTypes`.`name` as fundingTypeName, `courts`.`courtName`, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName, `barristers`.`vatNo`, `barristers`.`legalAidNumber` FROM `cases`, `barristers`, `caseTypes`, `fundingTypes`, `courts`, `titles` WHERE FIND_IN_SET('".$id."', `cases`.`entryType`) AND `cases`.`barristerId` = `barristers`.`id` AND `cases`.`courtId` = `courts`.`id` AND `cases`.`caseTypeId` = `caseTypes`.`id` AND `cases`.`fundingTypeId` = `fundingTypes`.`id` AND `barristers`.`titleId` = `titles`.`id` AND `cases`.`active`= '1'";
        $query_duplicated = $this->_conn->query($temp_duplicated);
        if($query_duplicated->num_rows > 0){
            while($row_duplicated = $query_duplicated->fetch_assoc()){


                if($row_duplicated['clientTypeId'] == 'Solicitor'){
                    $query_soli = $this->_conn->query("SELECT `solicitorCompany`.`id`, `solicitorCompany`.`companyName` as solicitorCompanyName, CONCAT(`solicitors`.`fname`, ' ', `solicitors`.`lname`) as solicitorName FROM solicitorCompany, solicitors WHERE `solicitors`.`id` = '".$row_duplicated['teamId']."' AND `solicitorCompany`.`id` = '".$row_duplicated['solicitorId']."'");
                    $row_soli = $query_soli->fetch_assoc();
                    $row_duplicated['solicitorCompanyName'] = $row_soli['solicitorCompanyName'];
                    $row_duplicated['solicitorName'] = $row_soli['solicitorName'];
                }else{
                    $query_client = $this->_conn->query("SELECT `id`,  CONCAT(`clientFname`, ' ', `clientLname`) as clientName FROM `clients` WHERE `id` = '".$row_duplicated['clientId']."'");
                    $row_client = $query_client->fetch_assoc();
                    $row_duplicated['clientName'] = $row_client['clientName'];
                }

                $data[] = $row_duplicated;
            }
        }

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

        return $this->_res;


    }

    public function saveCase($case){

        foreach($case as $k=>$v) { $case[$k] = filter_var($v, FILTER_SANITIZE_STRING); }

        $case['agreedFees'] = floatval($case['agreedFees']);
        $case['expence'] = floatval($case['expence']);
        $case['commission'] = intval($case['commission']);


        $query_check = $this->_conn->query("SELECT `id` FROM `cases` WHERE `courtDate` = '".$case['courtDate']."' AND `claimant` = '".$case['claimant']."' AND `defendant` = '".$case['defendant']."' AND `caseCategoryId` = '".$case['caseCategoryId']."'");

        if($query_check->num_rows > 0){
            $this->_res = ['status'=>'error', 'message'=>'Case already exist!, Please consider checking inactive case too.', 'data'=>null];
        }else{
            if($case['idReceived'] == 'No'){
                $case['photoIdReceived'] = 'No';
                $case['papperIdReceived'] = 'No';
            }

            $checkFees = strpos($case['agreedFees'], '-');
            if($checkFees !== false){
                $case['agreedFees'] = 0;
            }
    
            $commissionAmount = $case['agreedFees'] * $case['commission'];
            $query_string = "INSERT INTO `cases` (`entryType`, `caseTitleId`, `caseTypeId`, `categoryId`, `clientTypeId`, `solicitorId`, `teamId`, `solicitorRef`, `barristerId`, `claimant`, `defendant`, `behalfOf`, `courtId`, `courtDate`, `endDate`, `courtTime`, `timeEstimate`, `courtRef`, `agreedFees`, `vat`, `expence`, `expenceTypeId`, `additionalWorkId`, `additionalWorkDueDate`, `additionalWorkStatus`, `concession`,`commission`, `commissionAmount`, `fundingTypeId`, `certificateNumber`,`hourlyRate`, `outstandingDocuments`, `outstandingDocumentReceived`, `idReceived`, `photoIdReceived`, `papperIdReceived`, `clientCareLetterReceived`,  `courtBundleReceived`, `attendanceNoteReceived`, `paymentReceived`, `status`, `requestCourtListing`, `courtListingStatus`, `actionRequired`, `listings`, `notes`,`addedBy`) VALUES('0','".$case['caseTitleId']."', '".$case['caseTypeId']."', '".$case['caseCategoryId']."', '".$case['clientTypeId']."', '".$case['solicitorId']."', '".$case['teamId']."', '".$case['solicitorRef']."', '".$case['barristerId']."', '".$case['claimantName']."', '".$case['defendantName']."', '".$case['behalfOf']."', '".$case['courtId']."', '".$case['courtDate']."','".$case['endDate']."', '".$case['courtTime']."', '".$case['timeEstimate']."', '".$case['courtRef']."', '".$case['agreedFees']."', '".$case['vat']."', '".$case['expence']."', '".$case['expenceTypeId']."', '".$case['additionalWorkId']."', '".$case['additionalWorkDueDate']."', '".$case['additionalWorkStatus']."', '".$case['concession']."',  '".$case['commission']."','".$commissionAmount."', '".$case['fundingTypeId']."', '".$case['certificateNumber']."', '".$case['hourlyRate']."',  '".$case['outstandingDocuments']."',  '".$case['outstandingDocumentReceived']."', '".$case['idReceived']."', '".$case['photoIdReceived']."', '".$case['papperIdReceived']."', '".$case['clientCareLetterReceived']."', '".$case['courtBundleReceived']."', '".$case['attendanceNoteReceived']."', '".$case['paymentReceived']."', '".$case['status']."', '".$case['requestCourtListing']."', '".$case['courtListingStatus']."', '".$case['actionRequired']."', '".$case['listings']."', '".$case['notes']."', '".$this->_userId."')";
            $query = $this->_conn->query($query_string);
            if($query){
    
                $id = $this->_conn->insert_id;
    
                $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 = 'New case added to the system. court ref : '.$case['courtRef'].'( Added By : '.$row_username['userName'].')';
                $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
                
                //get case title 
                $query_case_title = $this->_conn->query("SELECT `name` FROM `caseTitles` WHERE id = '".$case['caseTitleId']."'");
                $row_case_title = $query_case_title->fetch_assoc();
                
                //check the client Type
                if($case['clientTypeId'] != 'Solicitor'){
                    //check client
                    $query_check_client = $this->_conn->query("SELECT `id` FROM `clients` WHERE `clientEmail` = '".$case['clientEmail']."'");
                    if($query_check_client->num_rows == 1){
                        $row_check_client = $query_check_client->fetch_assoc();
                        $clientId = $row_check_client['id'];
                    }else{
                        $dateNow = date('Y-m-d');
                        $query_insert_string = "INSERT INTO `clients` (`clientTitle`, `clientFname`, `clientLname`, `clientTelephone`, `clientMobile`, `clientEmail`, `clientAddress`, `clientPostcode`, `dateAdded`, `addedBy`) VALUES('".$case['clientTitle']."', '".$case['clientFname']."', '".$case['clientLname']."', '".$case['clientTelephone']."', '".$case['clientMobile']."', '".$case['clientEmail']."', '".$case['clientAddress']."', '".$case['clientPostcode']."', '".$dateNow."', '".$this->_userId."')";
                        $query_insert_client = $this->_conn->query($query_insert_string);
                        
                        if($query_insert_client){
                            $clientId = $this->_conn->insert_id;
                            $activity = $case['clientFname']."', '".$case['clientLname'].' DPA client has been added to the system. Court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                            $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$clientId."', 'client', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
                
                        }
                    }
    
                    //update client id
                    $query_update = $this->_conn->query("UPDATE `cases` SET `clientId` = '".$clientId."' WHERE `id` = '".$id."'");
                }
                

                    
                $invoiceRef = 'Inv-'.rand(100,999).'-00'.$id;
                $dateNow = $case['courtDate'];
                $commission =  ($case['commission'] / 100) * $case['agreedFees'];
                $caseTotal = $case['agreedFees'] + $case['expence'];
                $totalVat  = $case['vat'] * $caseTotal  / 100;
                $totalSolicitorPayment = $case['agreedFees'] + $case['expence'] + $totalVat;

                $barristerNet = $case['agreedFees']  - $commission;
                $barristerNetTotal = $barristerNet +  $case['expence'];
                $barristerVat = $barristerNet / 100 * $case['vat'];
                $totalBarristerPayment = $barristerNetTotal + $barristerVat;

                //update commission amount 
                $this->_conn->query("UPDATE `cases` SET `commissionAmount` = '".$commission."' WHERE `id` = '".$id."' ");
                
                $activity = 'Comission Amount updated to '.$commission.' court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
        
                //generate barrister invoice
                $this->_conn->query("INSERT INTO `barristerInvoice` (`barristerId`, `invoiceRef`, `invoiceDate`, `dueDate`, `totalAmount`, `dateAdded`, `addedBy`) VALUES('".$case['barristerId']."','".$invoiceRef."', '".$dateNow."', '".$case['endDate']."', '".$totalBarristerPayment."', '".$dateNow."', '".$this->_userId."' )");
                $barristerInvoiceId = $this->_conn->insert_id;

                $activity = 'New Invoice created for Barrister. Amount : '.$totalBarristerPayment.' court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
        
                //insert Itemised Bills
                $details = $row_case_title['name'];
                if(!empty($case['additionalWorkId'])){
                    $query_case_additional = $this->_conn->query("SELECT `name` FROM `additionalWorks` WHERE id = '".$case['additionalWorkId']."'");
                    $row_case_additional = $query_case_additional->fetch_assoc();
                    $details .= " ( Including ".$row_case_additional['name']." ) ";
                }

                if(!empty($case['concession'])){
                    $details .= " ( ".$case['concession']." ) ";
                }
                if(!empty($case['hourlyRate']) && $case['fundingTypeId'] == '7'){
                    $details .= " ( £".number_format($case['hourlyRate'], 2)." Per Hour ) ";
                }

                $this->_conn->query("INSERT INTO `barristerInvoiceItems` (`invoiceId`,`itemType`, `dueDate` ,`caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$barristerInvoiceId."', '1','".$case['endDate']."' ,'".$id."', '".$details."', '".$barristerNet."', '".$vatBarr."', '".$barrTotal."')");

                if(!empty($case['expence'])){
                    $query_case_expence = $this->_conn->query("SELECT `name` FROM `expenceTypes` WHERE id = '".$case['expenceTypeId']."'");
                    $row_case_expence = $query_case_expence->fetch_assoc();
                    $barrExpVat = $case['expence'] * $case['vat'] / 100;
                    $barrExpTotal = $case['expence'] + $barrExpVat;
                    $this->_conn->query("INSERT INTO `barristerInvoiceItems` (`invoiceId`,`itemType`, `dueDate` ,`caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$barristerInvoiceId."', '2','".$case['endDate']."' ,'".$id."', '".$row_case_expence['name']."', '".$case['expence']."', '".$barrExpVat."', '".$barrExpTotal."')");
                }
            
                $temp = "INSERT INTO `solicitorInvoice` (`teamId`, `barristerId`, `clientId`, `invoiceRef`, `invoiceDate`, `dueDate`, `totalAmount`, `dateAdded`, `addedBy`) VALUES('".$case['teamId']."', '".$case['barristerId']."', '".$clientId."', '".$invoiceRef."', '".$dateNow."', '".$case['endDate']."', '".$totalSolicitorPayment."', '".$dateNow."', '".$this->_userId."' )";
                $this->_conn->query($temp);
                $solicitorInvoiceId = $this->_conn->insert_id;
                
                $activity = 'New Invoice created for Solicitor. Amount : '.$totalSolicitorPayment.' court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
        
                $vatSoli = $case['agreedFees'] * $case['vat']/100;
                $soliTotal = $case['agreedFees'] + $vatSoli; 
                $this->_conn->query("INSERT INTO `solicitorInvoiceItems` (`invoiceId`,`itemType`, `dueDate`, `caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$solicitorInvoiceId."','1', '".$case['endDate']."' , '".$id."', '".$details."', '".$case['agreedFees']."', '".$vatSoli."', '".$soliTotal."')");
                if(!empty($case['expence'])){
                    $soliExpVat = $case['expence'] * $case['vat'] / 100;
                    $soliExpTotal = $case['expence'] + $soliExpVat;
                    $this->_conn->query("INSERT INTO `solicitorInvoiceItems` (`invoiceId`,`itemType`, `dueDate`, `caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$solicitorInvoiceId."', '2', '".$case['endDate']."' , '".$id."', '".$row_case_expence['name']."', '".$case['expence']."', '".$soliExpVat."', '".$soliExpTotal."')");
                }
                    
    
                $query_company = $this->_conn->query("SELECT * FROM company");
                $row_company = $query_company->fetch_assoc();
    
                if($row_company['emailBarrister'] == '1' || $row_company['emailSolicitor'] == '1'){
                    $subject = "New Booking in your Diary";
                    $this->_mail->connect();
                    $emails = [];
                    $soliEmails = [];
                    if($row_company['emailBarrister'] == '1'){
                        $email_barrister = $this->_conn->query("SELECT `barristers`.`email` FROM `barristers`, `cases` WHERE `cases`.`id` = '".$id."' AND `cases`.`barristerId` = `barristers`.`id`");
                        $row_email_barrister = $email_barrister->fetch_assoc();
                        if(filter_var($row_email_barrister['email'], FILTER_VALIDATE_EMAIL)) {
                            $emails[] = $row_email_barrister['email'];
                        }

                    }
    
                    if($row_company['emailSolicitor'] == '1'){
                        if($case['clientTypeId'] == 'Solicitor'){
                            $email_soli = $this->_conn->query("SELECT `solicitorCompany`.`email` as solicitorCompanyEmail, `solicitors`.`email` as solicitorEmail  FROM `solicitorCompany`, `cases`, `solicitors` WHERE `cases`.`id` = '".$id."' AND `cases`.`solicitorId` = `solicitorCompany`.`id` AND `cases`.`teamId` = `solicitors`.`id`");
                            $row_email_soli = $email_soli->fetch_assoc();
                            $soliEmails[] = $row_email_soli['solicitorCompanyEmail'];
                            $soliEmails[] = $row_email_soli['solicitorEmail'];
                            // $soliEmails[] = 'info@3boltcourt.com';
                            $email_body_solicitor = $this->generateSolicitorEmailBody($id);
                            $subject_soli = "New Booking added to our diary";
                            $this->_mail->transmitEmail($soliEmails, $subject_soli, $email_body_solicitor);

                        }else{
                        // $email_client = $this->_conn->query("SELECT clientEmail FROM `clients`, `cases` WHERE `cases`.`id` = '".$id."' AND `cases`.`clientId` = `clients`.`id`");
                        // $row_email_client = $email_client->fetch_assoc();
                        // $emails[] = $row_email_client['clientEmail'];
                        }
                    }
    
                    
                    
                    if(count($emails) > 0){
                        
                        $email_body = $this->generateEmailBody($id, 'save');

                        if($this->_mail->transmitEmail($emails, $subject, $email_body)){
                            $this->_res = ['status'=>'ok', 'message'=>'Case details saved. Email Send!', 'data'=>$id];
                        }else{
                            $this->_res = ['status'=>'ok', 'message'=>'Case Details saved, Could not send email!', 'data'=>$id];
                        }
                    }else{
                        $this->_res = ['status'=>'ok', 'message'=>'Case details saved nicely, No Email to send.', 'data'=>$id];
                    }

                    
    
                }else{
                    $this->_res = ['status'=>'ok', 'message'=>'Case details saved nicely, Sending email not required!', 'data'=>$id];
                }
                    
            
            }else{
                $this->_res = ['status'=>'error', 'message'=>'Could not save case details, Internal server Error', 'data'=>$query_string];
            }
        }
        
        // $this->_res = ['status'=>'ok', 'message'=>'Case details added to the system!', 'data'=>$query_string];
        return $this->_res;

    }

    private function generateEmailBody($id, $action){
        
        $case_email = $this->_conn->query("SELECT `cases`.`courtDate`, `cases`.`courtRef`, `cases`.`claimant`, `cases`.`defendant`, `cases`.`endDate`, `cases`.`behalfOf`, `caseTitles`.`name` as caseTitleName, `caseTypes`.`name` as caseTypeName, `caseCategories`.`name` as caseCategoryName, `fundingTypes`.`name` as fundingTypeName, `courts`.`courtName` FROM `cases`, `caseTitles`, `caseTypes`, `caseCategories`, `fundingTypes`, `courts` WHERE `cases`.`id` = '".$id."' AND `cases`.`caseTitleId` = `caseTitles`.`id` AND `cases`.`caseTypeId` = `caseTypes`.`id` AND `cases`.`categoryId` = `caseCategories`.`id` AND `cases`.`fundingTypeId` = `fundingTypes`.`id` AND `cases`.`courtId` = `courts`.`id`");
        $row_email = $case_email->fetch_assoc();

        $body = 
        "
           <h4>Dear All </h4>
           <p>";
        
        if($action == 'save'){
            $body ."This is just a courtesy email to notify you that a new case recorded in our system. Please find below the case details:</p>";
        }else if($action == 'update'){
            $body ."This is just a courtesy email to notify you that Case details has been updated. Please find below the case details:</p>";
        }else if($action == 'remove'){
            $body ."This is just a courtesy email to notify you that Case details has been removed. Please find below the case details:</p>";
        }
           
           
        $body .= "<ul>
           <li> <strong>Court Date</strong> : ".$row_email['courtDate']."</li>
        ";
        if($row_email['endDate'] != $row_email['courtDate']){
            $body .= "<li> <strong>Court Date</strong> : ".$row_email['courtDate']."</li>";
        }

        $body .= "   
           <li> <strong>Case Name</strong> : ".$row_email['courtName']."</li>
            <li> <strong>Case Name</strong> : ".$row_email['caseTitleName']."</li>
            <li> <strong>Case Type</strong> : ".$row_email['caseTypeName']."</li>
            <li> <strong>Case Category</strong> : ".$row_email['caseCategoryName']."</li>
            <li> <strong>Claimanet</strong> : ".$row_email['claimant']."</li>
            <li> <strong>Defendant</strong> : ".$row_email['defendant']."</li>
            <li> <strong>Acting On Behalf Of </strong> : ".$row_email['behalfOf']."</li>
            <li> <strong>Funding Type</strong> : ".$row_email['fundingTypeName']."</li>
           </ul>
           <p>Please do not hesitate to contant us if you have any question regarding the case</p>
           ";

           return $body;

    }

    private function generateSolicitorEmailBody($id){
        
        $case_email = $this->_conn->query("SELECT DATE_FORMAT(`cases`.`courtDate`, '%d-%m-%Y') as courtDate ,`cases`.`solicitorRef`,  `cases`.`courtRef`, `cases`.`claimant`, `cases`.`defendant`, DATE_FORMAT(`cases`.`endDate`, '%d-%m-%Y') as endDate, `cases`.`behalfOf`, `caseTitles`.`name` as caseTitleName, `caseTypes`.`name` as caseTypeName, `caseCategories`.`name` as caseCategoryName, `fundingTypes`.`name` as fundingTypeName, `courts`.`courtName`, `solicitorCompany`.`companyName`, `solicitorCompany`.`tradingName`, `solicitorCompany`.`address`, `solicitorCompany`.`address1`, `solicitorCompany`.`city`, `solicitorCompany`.`postcode`, CONCAT(`titles`.`name`, ' ', `solicitors`.`fname`, ' ', `solicitors`.`lname`) as solicitorName    FROM `cases`, `solicitorCompany`, `solicitors`, `caseTitles`, `caseTypes`, `caseCategories`, `fundingTypes`, `courts`, `titles` WHERE `cases`.`id` = '".$id."' AND `solicitors`.`id` = `cases`.`teamId` AND `solicitorCompany`.`id` = `solicitors`.`companyId` AND `cases`.`caseTitleId` = `caseTitles`.`id` AND `cases`.`caseTypeId` = `caseTypes`.`id` AND `cases`.`categoryId` = `caseCategories`.`id` AND `cases`.`fundingTypeId` = `fundingTypes`.`id` AND `cases`.`courtId` = `courts`.`id` AND `solicitors`.`titleId` = `titles`.`id`");
        $row_email = $case_email->fetch_assoc();

        $query_invoice_ref = $this->_conn->query("SELECT invoiceRef FROM solicitorInvoice WHERE id = (SELECT invoiceId FROM solicitorInvoiceItems WHERE caseId = '".$id."' GROUP BY invoiceId)");
        $row_invoice_ref = $query_invoice_ref->fetch_assoc();

        $query_barristerName = $this->_conn->query("SELECT CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName FROM barristers, titles, cases WHERE barristers.id = cases.barristerId and barristers.titleId = titles.id AND `cases`.`id` = '".$id."'");
        $row_barristerName = $query_barristerName->fetch_assoc();

        $body = 
        "
        <p>
        <strong>".$row_email['solicitorName']."</strong><br />
        ".$row_email['companyName']."<br />
        ".$row_email['address']." ".$row_email['address1']."<br />
        ".$row_email['city']." 
        ".$row_email['postcode']."<br />
        <strong>Your Ref </strong>: ".$row_email['solicitorRef']."<br />
        <strong>Our Ref </strong>:  ".$row_invoice_ref['invoiceRef']."<br /> 
        <strong>Date</strong> : ".$row_email['courtDate'];
        if($row_email['courtDate'] != $row_email['endDate']){
            $body.= ' To '.$row_email['endDate'];
        }
        $body .= "
        </p><br />
        <p>Dear ".$row_email['solicitorName']."</p>
        <p>Re: ".$row_email['claimant']." V ".$row_email['defendant']."</p>      
        <br />
        <p>
        Thank you for your brief/instructions to ".$row_barristerName['barristerName']." on the above named matter.</p><br/>
        <p>
        <strong>Terms of Work </strong>: All barristers at 3 Bolt Court Chambers are regulated by the Bar Standards Board (<a href='www.barstandardsboard.org.uk/'>www.barstandardsboard.org.uk</a>) and all instructions received by our barristers are undertaken on the Standard Contractual Terms for the Supply of Legal Services by Barristers to Authorised Persons 2012 – Updated for the GDPR in 2018 ('<a href='https://www.barcouncilethics.co.uk/wp-content/uploads/2017/10/22.05.18-approved-contractual-terms-for-GDPR-pdf2-1.pdf'>Standard Contractual Terms</a>) unless the barrister to whom those instructions have been sent (a) is to be paid directly by the Legal Aid Board or the Crown Prosecution Service; or (b) has accepted the instructions pursuant to other terms which have been expressly agreed in writing (including by exchange of e-mails). 
        </p>
        <br/>
        <p>
        If the matter is urgent and has been marked as such then we will endeavour to meet your deadline or telephone you to discuss the case. If however the instructions are urgent but have not been marked as such, then please contact one of the clerks.
        </p>
        <br /><br />
        <p>
        <strong>Public Funding</strong> : If your client is assisted by funding from the Legal Aid Agency then please supply a copy of the Certificate of Public Funding if you have not already done so.
        </p>
        <br />
        <p>
        <strong>Complaints </strong>: We are required by the Bar Code of Conduct to ensure that all lay clients are informed (a) of their right to make a complaint, how and to whom this can be done, including their right to complain to the Legal Ombudsman (<a href='www.legalombudsman.org.uk'>www.legalombudsman.org.uk/</a>) at the conclusion of the complaints process, the timeframe for doing so and the full details of how to contact the Legal Ombudsman; and (b) that the lay client may complain directly to Chambers without going through solicitors.  Our Complaints Procedure can be found on our website on https://3boltcourt.com/about-us/complaint-procedure/
        </p>
        <br />
        <p><strong>Privacy Policy </strong>: We have a duty to comply with the General Data Protection Regulations along with our obligations to the BSB and inform data subjects of their rights.  A copy of our Privacy Notice can be found on our website via the following link https://3boltcourt.com/about-us/gdpr-notice/</p><br />
        <p>To enable us to comply with the above obligations, we ask you to provide a copy of this letter to your clients.</p><br />
        <p>Please do not hesitate to contact the clerks if there is any matter you wish to bring to our attention or upon which you require clarification.</p><br />
           
        ";

        return $body;

    }

    

    public function duplicateCase($case){

        $result = "";

        foreach($case as $k=>$v) { $case[$k] = filter_var($v, FILTER_SANITIZE_STRING); }
        //get case details 
        $query_case = $this->_conn->query("SELECT * FROM cases WHERE id = '".$case['caseId']."'");
        // foreach($case as $k=>$v) {$case[$k] = htmlspecialchars($v);}
        
        $checkFees = strpos($case['agreedFees'], '-');
        if($checkFees !== false){
            $case['agreedFees'] = 0;
        }

        $case['agreedFees'] = floatval($case['agreedFees']);
        $case['expence'] = floatval($case['expence']);
        $case['commission'] = intval($case['commission']);

    

        $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');

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

            //check if this case has been duplicated to any other case, If so send error message
            

            $row_case = $query_case->fetch_assoc();
            $id = $case['caseId'];
            $query_duplicated = $this->_conn->query("SELECT `id` FROM `cases` WHERE FIND_IN_SET('".$id."', `cases`.`entryType`)");
            if($row_duplicated->num_rows > 0){
                //duplicated case found. Send error message
                $this->_res = ['status'=>'error', 'message'=>'This cas has been duplicated already. Please duplicate the newest case!', 'data'=>null];
            }else{
                $query_string = "INSERT INTO `cases` ( `entryType`, `caseTitleId`, `caseTypeId`, `categoryId`, `clientTypeId`, `solicitorId`, `teamId`, `solicitorRef`, `clientId`, `barristerId`, `claimant`, `defendant`, `behalfOf`, `courtId`, `courtDate`, `endDate`, `courtTime`, `timeEstimate`, `courtRef`, `agreedFees`, `vat`, `expence`, `expenceTypeId`, `additionalWorkId`, `additionalWorkDueDate`, `additionalWorkStatus`, `concession`,`commission`,  `fundingTypeId`, `outstandingDocuments`, `idReceived`, `photoIdReceived`, `papperIdReceived`, `clientCareLetterReceived`, `outstandingDocumentReceived`, `courtBundleReceived`, `attendanceNoteReceived`, `paymentReceived`, `status`, `requestCourtListing`, `courtListingStatus`, `actionRequired`, `listings`, `notes`,`addedBy`) VALUES('".$id."', '".$case['caseTitleId']."', '".$row_case['caseTypeId']."', '".$row_case['categoryId']."', '".$row_case['clientTypeId']."', '".$row_case['solicitorId']."', '".$case['teamId']."', '".$case['solicitorRef']."',  '".$row_case['clientId']."',  '".$case['barristerId']."', '".$row_case['claimant']."', '".$row_case['defendant']."', '".$row_case['behalfOf']."', '".$case['courtId']."', '".$case['courtDate']."', '".$case['endDate']."','".$case['courtTime']."', '".$case['timeEstimate']."', '".$case['courtRef']."', '".$case['agreedFees']."', '".$case['vat']."', '".$case['expence']."', '".$case['expenceTypeId']."', '".$case['additionalWorkId']."', '".$case['additionalWorkDueDate']."', '".$case['additionalWorkStatus']."', '".$case['concession']."',  '".$case['commission']."','".$case['fundingTypeId']."',  '".$case['outstandingDocuments']."', '".$row_case['idReceived']."', '".$row_case['photoIdReceived']."', '".$row_case['papperIdReceived']."', 'No', '".$case['outstandingDocumentReceived']."', '".$case['courtBundleReceived']."', '".$case['attendanceNoteReceived']."', '".$case['paymentReceived']."', '".$case['status']."', '".$case['requestCourtListing']."', '".$case['courtListingStatus']."', '".$case['actionRequired']."', '".$case['listings']."', '".$case['notes']."', '".$this->_userId."')";
                $query = $this->_conn->query($query_string);
                $newId = $this->_conn->insert_id;

                //get together cases
                if($row_case['entryType'] == '0'){
                    $entryTypeNew = $case['caseId'];
                }else{
                    $id_cases = explode(',', $row_case['entryType']);
                    array_push($id_cases, $case['caseId']);
                    $entryTypeNew = implode(',', $id_cases);
                }
                $temp = "UPDATE `cases` SET `entryType` =  '".$entryTypeNew."' WHERE `id` = '".$newId."'";

                $this->_conn->query($temp);

                $activity = 'Duplicated case has been added to the system, Previous case court Ref : '.$case['courtRef'].' ( Duplicated By : '.$row_username['userName'].')';
                $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$newId."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
                
                if($query){

                    $result .= "Case Created ";
                    //get case title 
                    $query_case_title = $this->_conn->query("SELECT `name` FROM `caseTitles` WHERE id = '".$case['caseTitleId']."'");
                    $row_case_title = $query_case_title->fetch_assoc();
        
                    

                    $commission = $case['commission'] / 100 * $case['agreedFees'];
                    $caseTotal = $case['agreedFees'] + $case['expence'];
                    $totalVat  = $case['vat'] * $caseTotal  / 100;
                    $totalSolicitorPayment = $case['agreedFees'] + $case['expence'] + $totalVat;
                    
                    $barristerNet = $case['agreedFees']  - $commission;
                    $barristerNetTotal = $barristerNet+  $case['expence'];
                    $barristerVat = $barristerNet / 100 * $case['vat'];
                    $totalBarristerPayment = $barristerNetTotal + $barristerVat;

                    $invoiceRef = 'Inv-'.rand(100,999).'-00'.$id;
                    $dateNow = $case['endDate'];
                    
                    //update commission amount 
                    $this->_conn->query("UPDATE `cases` SET `commissionAmount` = '".$commission."' WHERE `id` = '".$newId."' ");
                    
                    $activity = 'Comission Amount updated to '.$commission.' court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                    $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$newId."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
        
                    // //update barrister Invoice
                    if($case['barristerId'] == $row_case['barristerId']){

                        // $result .= "Barrister Id is same | i : ".$case['barristerId']." e :".$row_case['barristerId'];

                        $query_get_barrister_invoice = $this->_conn->query("SELECT barristerInvoice.* FROM barristerInvoice, barristerInvoiceItems WHERE barristerInvoiceItems.caseId = '".$id."' AND barristerInvoiceItems.invoiceId = barristerInvoice.id GROUP BY invoiceId");
                        $row_invoice_barrister = $query_get_barrister_invoice->fetch_assoc();

                        //generate barrister invoice
                        $this->_conn->query("UPDATE `barristerInvoice` SET  `dueDate` = '".$case['endDate']."', `totalAmount` = `totalAmount` + '".$totalBarristerPayment."', `status` = '0' WHERE `id` = '".$row_invoice_barrister['id']."'");
                        
                        $activity = 'Barrister Invoice has been updated. New Barrister Amount is  : '.$totalBarristerPayment.' court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                        $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$newId."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
        
                        //insert Itemised Bills
                        $details = $row_case_title['name'];
                        if(!empty($case['additionalWorkId'])){
                            $query_case_additional = $this->_conn->query("SELECT `name` FROM `additionalWorks` WHERE id = '".$case['additionalWorkId']."'");
                            $row_case_additional = $query_case_additional->fetch_assoc();
                            $details .= " ( Including ".$row_case_additional['name']." ) ";
                        }

                        if(!empty($case['concession'])){
                            $details .= " ( ".$case['concession']." ) ";
                        }
                        if(!empty($case['hourlyRate']) && $case['fundingTypeId'] == '7'){
                            $details .= " ( £".number_format($case['hourlyRate'], 2)." Per Hour ) ";
                        }

                        $vatBarr = $barristerNet * $case['vat'] / 100;
                        $barrTotal = $barristerNet + $vatBarr; 

                        
                        //insert case invoice
                        $tempTotal = $totalBarristerPayment - $totalVat;
                        $this->_conn->query("INSERT INTO `barristerInvoiceItems` (`invoiceId`,`itemType`, `dueDate` ,`caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$row_invoice_barrister['id']."', '1','".$dateNow."' ,'".$newId."', '".$details."', '".$barristerNet."', '".$vatBarr."', '".$barrTotal."')");

                        if(!empty($case['expence'])){
                            $query_case_expence = $this->_conn->query("SELECT `name` FROM `expenceTypes` WHERE id = '".$case['expenceTypeId']."'");
                            $row_case_expence = $query_case_expence->fetch_assoc();
                            $barrExpVat = $case['expence'] * $case['vat'] / 100;
                            $barrExpTotal = $case['expence'] + $barrExpVat;
                            $this->_conn->query("INSERT INTO `barristerInvoiceItems` (`invoiceId`,`itemType`, `dueDate` ,`caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$row_invoice_barrister['id']."', '2','".$case['endDate']."' ,'".$newId."', '".$row_case_expence['name']."', '".$case['expence']."', '".$barrExpVat."', '".$barrExpTotal."')");
                        }

                        $query_get_solicitor_invoice = $this->_conn->query("SELECT solicitorInvoice.* FROM solicitorInvoice, solicitorInvoiceItems WHERE solicitorInvoiceItems.caseId = '".$id."' AND solicitorInvoiceItems.invoiceId = solicitorInvoice.id GROUP BY invoiceId");
                        $row_invoice_solicitor = $query_get_solicitor_invoice->fetch_assoc();
                        $duplicateInvoiceValue = $row_invoice_solicitor['totalAmount'] + $totalSolicitorPayment;

                        $this->_conn->query("UPDATE `solicitorInvoice` SET `teamId` = '".$case['teamId']."', `barristerId` = '".$case['barristerId']."',  `invoiceDate` = '".$case['courtDate']."', `dueDate` = '".$case['endDate']."', `totalAmount` = '".$duplicateInvoiceValue."' , `status` = '0' WHERE id = '".$row_invoice_solicitor['id']."'");

                        $activity = 'Solicitor Invoice updated. New Solicitor Invoice amount is  : '.$duplicateInvoiceValue.' court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                        $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$newId."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
            

                        $vatSoli = $case['agreedFees'] /100 * $case['vat'];
                        $soliTotal = $case['agreedFees'] + $vatSoli; 
                        $this->_conn->query("INSERT INTO `solicitorInvoiceItems` (`invoiceId`, `itemType`, `dueDate`, `caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$row_invoice_solicitor['id']."', '1', '".$case['endDate']."', '".$newId."', '".$details."', '".$case['agreedFees']."', '".$vatSoli."', '".$soliTotal."')");
                        if(!empty($case['expence'])){
                            $soliExpVat = $case['expence'] /100 * $case['vat'];
                            $soliExpTotal = $case['expence'] + $soliExpVat;
                            $this->_conn->query("INSERT INTO `solicitorInvoiceItems` (`invoiceId`, `itemType`, `dueDate`, `caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$row_invoice_solicitor['id']."', '2', '".$case['endDate']."', '".$newId."', '".$row_case_expence['name']."', '".$case['expence']."', '".$soliExpVat."', '".$soliExpTotal."')");
                        }

                    }else{


                        $invoiceRef = 'Inv-'.rand(100,999).'-00'.$newId;
                        $dateNow = $case['courtDate'];
                        
                        //generate barrister invoice
                        $this->_conn->query("INSERT INTO `barristerInvoice` (`barristerId`, `invoiceRef`, `invoiceDate`, `dueDate`, `totalAmount`, `dateAdded`, `addedBy`) VALUES('".$case['barristerId']."', '".$invoiceRef."', '".$case['courtDate']."', '".$case['endDate']."', '".$totalBarristerPayment."', '".$dateNow."', '".$this->_userId."' )");
                        $barristerInvoiceId = $this->_conn->insert_id;

                        $activity = 'New barrister Invoice Generated. Amount  : '.$totalBarristerPayment.' court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                        $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$newId."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
        
                        //insert Itemised Bills
                        $details = $row_case_title['name'];
                        if(!empty($case['additionalWorkId'])){
                            $query_case_additional = $this->_conn->query("SELECT `name` FROM `additionalWorks` WHERE id = '".$case['additionalWorkId']."'");
                            $row_case_additional = $query_case_additional->fetch_assoc();
                            $details .= " ( Including ".$row_case_additional['name']." ) ";
                        }

                        if(!empty($case['concession'])){
                            $details .= " ( ".$case['concession']." ) ";
                        }
                        if(!empty($case['hourlyRate']) && $case['fundingTypeId'] == '7'){
                            $details .= " ( £".number_format($case['hourlyRate'], 2)." Per Hour ) ";
                        }

                        $vatBarr = $barristerNet * $case['vat'] / 100;
                        $barrTotal = $barristerNet + $vatBarr; 

                        
                        //insert case invoice
                        $tempTotal = $totalBarristerPayment - $totalVat;
                        $this->_conn->query("INSERT INTO `barristerInvoiceItems` (`invoiceId`,`itemType`, `dueDate` ,`caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$barristerInvoiceId."', '1','".$case['endDate']."' ,'".$newId."', '".$details."', '".$barristerNet."', '".$vatBarr."', '".$barrTotal."')");

                        if(!empty($case['expence'])){
                            $query_case_expence = $this->_conn->query("SELECT `name` FROM `expenceTypes` WHERE id = '".$case['expenceTypeId']."'");
                            $row_case_expence = $query_case_expence->fetch_assoc();
                            $barrExpVat = $case['expence'] * $case['vat'] / 100;
                            $barrExpTotal = $case['expence'] + $barrExpVat;
                            $this->_conn->query("INSERT INTO `barristerInvoiceItems` (`invoiceId`,`itemType`, `dueDate` ,`caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$barristerInvoiceId."', '2','".$case['endDate']."' ,'".$newId."', '".$row_case_expence['name']."', '".$case['expence']."', '".$barrExpVat."', '".$barrExpTotal."')");
                        }

                        $temp = "INSERT INTO `solicitorInvoice` (`teamId`, `barristerId`, `clientId`, `invoiceRef`, `invoiceDate`, `dueDate`, `totalAmount`, `dateAdded`, `addedBy`) VALUES('".$case['teamId']."', '".$case['barristerId']."', '".$row_case['clientId']."', '".$invoiceRef."', '".$dateNow."', '".$case['endDate']."', '".$totalSolicitorPayment."', '".$dateNow."', '".$this->_userId."' )";
                        $result .= $temp;
                        $this->_conn->query($temp);
                        $solicitorInvoiceId = $this->_conn->insert_id;
                        
                        $activity = 'New Invoice created for Solicitor. Amount : '.$totalSolicitorPayment.' court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                        $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
                
                        $vatSoli = $case['agreedFees'] * $case['vat']/100;
                        $soliTotal = $case['agreedFees'] + $vatSoli; 
                        $this->_conn->query("INSERT INTO `solicitorInvoiceItems` (`invoiceId`,`itemType`, `dueDate`, `caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$solicitorInvoiceId."','1', '".$case['endDate']."' , '".$newId."', '".$details."', '".$case['agreedFees']."', '".$vatSoli."', '".$soliTotal."')");
                        if(!empty($case['expence'])){
                            $soliExpVat = $case['expence'] * $case['vat'] / 100;
                            $soliExpTotal = $case['expence'] + $soliExpVat;
                            $this->_conn->query("INSERT INTO `solicitorInvoiceItems` (`invoiceId`,`itemType`, `dueDate`, `caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$solicitorInvoiceId."', '2', '".$case['endDate']."' , '".$newId."', '".$row_case_expence['name']."', '".$case['expence']."', '".$soliExpVat."', '".$soliExpTotal."')");
                        }

                    }

                    $query_company = $this->_conn->query("SELECT * FROM company");
                    $row_company = $query_company->fetch_assoc();
        
                    if($row_company['emailBarrister'] == '1'){
                        $subject = "New Booking in your Diary";
                        $this->_mail->connect();
                        $emails = [];
                        $soliEmails = [];
                        if($row_company['emailBarrister'] == '1'){
                            $email_barrister = $this->_conn->query("SELECT `barristers`.`email` FROM `barristers`, `cases` WHERE `cases`.`id` = '".$id."' AND `cases`.`barristerId` = `barristers`.`id`");
                            $row_email_barrister = $email_barrister->fetch_assoc();
                            if(filter_var($row_email_barrister['email'], FILTER_VALIDATE_EMAIL)) {
                                $emails[] = $row_email_barrister['email'];
                            }

                        }
                        
                        
                        if(count($emails) > 0){
                            
                            $email_body = $this->generateEmailBody($id, 'save');

                            if($this->_mail->transmitEmail($emails, $subject, $email_body)){
                                $this->_res = ['status'=>'ok', 'message'=>'Case details saved. Email Send!', 'data'=>$newId];
                            }else{
                                $this->_res = ['status'=>'ok', 'message'=>'Case Details saved, Could not send email!', 'data'=>$newId];
                            }
                        }else{
                            $this->_res = ['status'=>'ok', 'message'=>'Case details saved nicely, No Email to send.', 'data'=>$newId];
                        }

                        
        
                    }else{
                        $this->_res = ['status'=>'ok', 'message'=>'Case details saved nicely, Sending email not required!', 'data'=>$newId];
                    }
        
                    // $this->_res = ['status'=>'ok', 'message'=>'Duplicated case saved.', 'data'=>$newId];
                }else{
                    $this->_res = ['status'=>'error', 'message'=>'Could not save case details, Internal server Error', 'data'=>$query_string];
                }
            }

            
        }else{
            $this->_res = ['status'=>'error', 'message'=>'Could not find any case to duplicate!', 'data'=>$case];
        }
        
        // $this->_res = ['status'=>'ok', 'message'=>'Case details added to the system!', 'data'=>$query_string];
        return $this->_res;

    }


    public function updateCase($id, $case){

        foreach($case as $k=>$v) { $case[$k] = filter_var($v, FILTER_SANITIZE_STRING); }
        $query_check = $this->_conn->query("SELECT * FROM `cases` WHERE `id` = '".$id."'");

        if($case['idReceived'] == 'No'){
            $case['photoIdReceived'] = 'No';
            $case['papperIdReceived'] = 'No';
        }

        $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');

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

            $row_check = $query_check->fetch_assoc();
            $string = "UPDATE `cases` SET `caseTitleId` = '".$case['caseTitleId']."', `caseTypeId` = '".$case['caseTypeId']."', `categoryId` = '".$case['caseCategoryId']."', `clientTypeId` = '".$case['clientTypeId']."', `solicitorId` = '".$case['solicitorId']."', `teamId` = '".$case['teamId']."', `solicitorRef` = '".$case['solicitorRef']."', `barristerId` = '".$case['barristerId']."', `claimant` = '".$case['claimantName']."', `defendant` = '".$case['defendantName']."', `behalfOf` = '".$case['behalfOf']."', `courtId` = '".$case['courtId']."', `courtDate` = '".$case['courtDate']."', `endDate` = '".$case['endDate']."', `courtTime` = '".$case['courtTime']."', `timeEstimate` = '".$case['timeEstimate']."', `courtRef` = '".$case['courtRef']."', `agreedFees` = '".$case['agreedFees']."', `vat` = '".$case['vat']."', `expence` = '".$case['expence']."', `expenceTypeId` = '".$case['expenceTypeId']."', `additionalWorkId` = '".$case['additionalWorkId']."', `additionalWorkDueDate` = '".$case['additionalWorkDueDate']."', `additionalWorkStatus` = '".$case['additionalWorkStatus']."', `concession` = '".$case['concession']."',`commission` = '".$case['commission']."',  `fundingTypeId` = '".$case['fundingTypeId']."', `idReceived` = '".$case['idReceived']."', `photoIdReceived` = '".$case['photoIdReceived']."', `papperIdReceived`  = '".$case['papperIdReceived']."', `clientCareLetterReceived`  = '".$case['clientCareLetterReceived']."',  `certificateNumber` = '".$case['certificateNumber']."',  `hourlyRate` = '".$case['hourlyRate']."', `outstandingDocuments` = '".$case['outstandingDocuments']."',  `outstandingDocumentReceived` = '".$case['outstandingDocumentReceived']."', `courtBundleReceived`  = '".$case['courtBundleReceived']."', `attendanceNoteReceived`  = '".$case['attendanceNoteReceived']."', `paymentReceived`  = '".$case['paymentReceived']."', `status`  = '".$case['status']."', `requestCourtListing`  = '".$case['requestCourtListing']."', `courtListingStatus`  = '".$case['courtListingStatus']."', `actionRequired`  = '".$case['actionRequired']."', `listings`  = '".$case['listings']."', `notes`  = '".$case['notes']."', `active` = '".$case['active']."' WHERE `id` = '".$id."' ";
            $query = $this->_conn->query($string);

            $query_case_title = $this->_conn->query("SELECT `name` FROM `caseTitles` WHERE id = '".$case['caseTitleId']."'");
            $row_case_title = $query_case_title->fetch_assoc();

            if($query){

                
                $checkFees = strpos($case['agreedFees'], '-');
                if($checkFees !== false){
                    $case['agreedFees'] = 0;
                }

                $clientId = null;
                //update client id if dpa
                if($case['clientTypeId'] != 'Solicitor'){

                    //check client
                    $query_check_client = $this->_conn->query("SELECT `id` FROM `clients` WHERE `clientEmail` = '".$case['clientEmail']."'");
                    if($query_check_client->num_rows == 1){
                        $row_check_client = $query_check_client->fetch_assoc();
                        $clientId = $row_check_client['id'];

                        $this->_conn->query("UPDATE clients SET `clientTitle` = '".$case['clientTitle']."', `clientFname` = '".$case['clientFname']."', `clientLname` = '".$case['clientLname']."', `clientTelephone` = '".$case['clientTelephone']."', `clientMobile` = '".$case['clientMobile']."', `clientAddress` = '".$case['clientAddress']."', `clientPostcode` = '".$case['clientPostcode']."' WHERE `id` = '".$clientId."'");
                        //update id received, photoid, paper id and client care letter
                        $this->_conn->query("UPDATE `cases` SET `idReceived` = '".$case['idReceived']."', `photoIdReceived` = '".$case['photoIdReceived']."', `papperIdReceived` = '".$case['papperIdReceived']."', `clientCareLetterReceived` = '".$case['clientCareLetterReceived']."' WHERE `id` = '".$id."'");

                    }else{
                        $dateNow = date('Y-m-d');
                        $query_insert_string = "INSERT INTO `clients` (`clientTitle`, `clientFname`, `clientLname`, `clientTelephone`, `clientMobile`, `clientEmail`, `clientAddress`, `clientPostcode`, `dateAdded`, `addedBy`) VALUES('".$case['clientTitle']."', '".$case['clientFname']."', '".$case['clientLname']."', '".$case['clientTelephone']."', '".$case['clientMobile']."', '".$case['clientEmail']."', '".$case['clientAddress']."', '".$case['clientPostcode']."', '".$dateNow."', '".$this->_userId."')";
                        $query_insert_client = $this->_conn->query($query_insert_string);

                        if($query_insert_client){
                            $clientId = $this->_conn->insert_id;
                            $activity = $case['clientFname']."', '".$case['clientLname'].' DPA client has been added to the system. Court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                            $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$clientId."', 'client', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
                        }
                    }

                    //update client id
                    $query_update = $this->_conn->query("UPDATE `cases` SET `clientId` = '".$clientId."' WHERE `id` = '".$id."'");

                }

                //check if the case was active
                if($row_check['active'] != $case['active']){

                    //barrister details
                    $query_active_amount_barr = $this->_conn->query("SELECT invoiceId,  sum(total) as total FROM `barristerInvoiceItems` WHERE `caseId` = '".$id."' ");
                    $row_active_amount_barr = $query_active_amount_barr->fetch_assoc();
                    //solicitorDetails
                    // $string_temp = "SELECT min(invoiceId) as invoiceId,  sum(total) as total FROM `solicitorInvoiceItems` WHERE `caseId` = '".$id."' ";
                    $query_active_amount = $this->_conn->query("SELECT invoiceId,  sum(total) as total FROM `solicitorInvoiceItems` WHERE `caseId` = '".$id."' ");
                    $row_active_amount = $query_active_amount->fetch_assoc();

                    if($row_check['active'] == '0' && $case['active'] == '1'){

                        // unarchieve an case
                        $this->_conn->query("UPDATE solicitorInvoice SET `totalAmount` = `totalAmount` + '".$row_active_amount['total']."' WHERE id = '".$row_active_amount['invoiceId']."'");
                        $this->_conn->query("UPDATE solicitorInvoiceItems SET active = 1 WHERE `caseId` = '".$id."'");

                        $this->_conn->query("UPDATE barristerInvoice SET `totalAmount` = `totalAmount` + '".$row_active_amount_barr['total']."' WHERE id = '".$row_active_amount_barr['invoiceId']."'");
                        $this->_conn->query("UPDATE barristerInvoiceItems SET active = 1 WHERE `caseId` = '".$id."'");


                    }else if($row_check['active'] == '1' && $case['active'] == '0'){

                        //archieve a case
                        // $string_temp = "UPDATE solicitorInvoice SET `totalAmount` = `totalAmount` - '".$row_active_amount['total']."' WHERE id = '".$row_active_amount['id']."'";
                        $this->_conn->query("UPDATE solicitorInvoice SET `totalAmount` = `totalAmount` - '".$row_active_amount['total']."' WHERE id = '".$row_active_amount['invoiceId']."'");
                        $this->_conn->query("UPDATE solicitorInvoiceItems SET active = 0 WHERE `caseId` = '".$id."'");

                        $this->_conn->query("UPDATE barristerInvoice SET `totalAmount` = `totalAmount` - '".$row_active_amount_barr['total']."' WHERE id = '".$row_active_amount_barr['invoiceId']."'");
                        $this->_conn->query("UPDATE barristerInvoiceItems SET active = 0 WHERE `caseId` = '".$id."'");

                    }
                }

                $commission = $case['commission'] / 100 * $case['agreedFees'];
                $caseTotal = $case['agreedFees'] + $case['expence'];
                $totalVat  = $case['vat'] * $caseTotal  / 100;
                $totalSolicitorPayment = $case['agreedFees'] + $case['expence'] + $totalVat;
                
                $barristerNet = $case['agreedFees']  - $commission;
                $barristerNetTotal = $barristerNet+  $case['expence'];
                $barristerVat = $barristerNet / 100 * $case['vat'];
                $totalBarristerPayment = $barristerNetTotal + $barristerVat;


                
                $activity = 'Case Details has been updated.  court ref : '.$case['courtRef'].' ( Updated  By : '.$row_username['userName'].')';
                $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
            

                //update agreed fees & commission

                $get_soli_id = $this->_conn->query("SELECT solicitorInvoice.id, solicitorInvoice.totalAmount FROM solicitorInvoice, solicitorInvoiceItems WHERE solicitorInvoiceItems.caseId = '".$id."' and solicitorInvoiceItems.invoiceId = solicitorInvoice.id");
                $row_soli_id = $get_soli_id->fetch_assoc();
                $get_bar_id = $this->_conn->query("SELECT barristerInvoice.id, barristerInvoice.totalAmount FROM barristerInvoice, barristerInvoiceItems WHERE barristerInvoiceItems.caseId = '".$id."' and barristerInvoiceItems.invoiceId = barristerInvoice.id");
                $row_bar_id = $get_bar_id->fetch_assoc();
        
                if($case['agreedFees'] != $row_check['agreedFees'] || $case['expence'] != $row_check['expence'] || $case['commission'] != $row_check['commission'] || $case['vat'] != $row_check['vat'] || $row_check['barristerId'] != $case['barristerId'] || $row_check['solicitorId'] != $case['solicitorId'] || $row_check['teamId'] != $case['teamId']  || $row_check['clientId'] != $clientId){
                    

                    $this->_conn->query("UPDATE cases SET commissionAmount = '".$commission."' WHERE id = '".$id."'");
                    //update invoices
                    $activity = 'Comission Amount updated to '.$commission.' Court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                    $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
    
                    
                    if($row_check['entryType'] != '0'){

                        //calculate solicitor Invoice
                        //deduct previous charge
                        $commission_old = $row_check['commission'] / 100 * $row_check['agreedFees'];
                        $caseTotalOld = $row_check['agreedFees'] + $row_check['expence'];
                        $totalVatOld  = $row_check['vat'] * $caseTotalOld  / 100;
                        $totalSolicitorPaymentOld = $row_check['agreedFees'] + $row_check['expence'] + $totalVatOld;

                        $oldNet = $row_check['agreedFees'] + $row_check['expence'] - $commission_old;
                        $old_vat = $row_check['vat'] * $oldNet  / 100;
                        $totalBarristerPaymentOld = $oldNet - $old_vat;

                        //add new charge
                        
                        if($case['barristerId'] != $row_check['barristerId']){

                            //generate new invoice for the barrister and solicitor
                            // get previous case value and commission
                            //deduct it from the current invoice
                            //remove related items 

                            $newSolicitorPayment = $row_soli_id['totalAmount'] - $totalSolicitorPaymentOld;
                            $newBarristerPayment = $row_bar_id['totalAmount'] - $totalBarristerPaymentOld;
    
                            $this->_conn->query("UPDATE solicitorInvoice SET `teamId` = '".$case['teamId']."', `barristerId` = '".$case['barristerId']."',   `dueDate` = '".$case['endDate']."',  `totalAmount` = '".$newSolicitorPayment."' WHERE `id` = '".$row_soli_id['id']."'");
                            $this->_conn->query("UPDATE barristerInvoice SET `barristerId` = '".$case['barristerId']."',  `dueDate` = '".$case['endDate']."', `totalAmount` = '".$newBarristerPayment."' WHERE `id` = '".$row_bar_id['id']."'");
                            $this->_conn->query("DELETE FROM `solicitorInvoiceItems` WHERE `caseId` = '".$id."' ");
                            $this->_conn->query("DELETE FROM `barristerInvoiceItems` WHERE `caseId` = '".$id."' ");
                            
                            //generate a new invoice
                            //insert items for that invoice
                            $invoiceRef = 'Inv-'.rand(100,999).'-00'.$id;
                            $dateNow = $case['endDate'];

                            
                            
                            //generate barrister invoice
                            $this->_conn->query("INSERT INTO `barristerInvoice` (`barristerId`, `invoiceRef`, `invoiceDate`, `dueDate`, `totalAmount`, `dateAdded`, `addedBy`) VALUES('".$case['barristerId']."', '".$invoiceRef."', '".$case['courtDate']."', '".$case['endDate']."', '".$totalBarristerPayment."', '".$dateNow."', '".$this->_userId."' )");
                            $barristerInvoiceId = $this->_conn->insert_id;

                            $activity = 'New barrister Invoice Generated. Amount  : '.$totalBarristerPayment.' court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                            $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
            
                            //insert Itemised Bills
                            $details = $row_case_title['name'];
                            if(!empty($case['additionalWorkId'])){
                                $query_case_additional = $this->_conn->query("SELECT `name` FROM `additionalWorks` WHERE id = '".$case['additionalWorkId']."'");
                                $row_case_additional = $query_case_additional->fetch_assoc();
                                $details .= " ( Including ".$row_case_additional['name']." ) ";
                            }

                            if(!empty($case['concession'])){
                                $details .= " ( ".$case['concession']." ) ";
                            }
                            if(!empty($case['hourlyRate']) && $case['fundingTypeId'] == '7'){
                                $details .= " ( £".number_format($case['hourlyRate'], 2)." Per Hour ) ";
                            }

                            $vatBarr = $barristerNet * $case['vat'] / 100;
                            $barrTotal = $barristerNet + $vatBarr; 

                            
                            //insert case invoice
                            $tempTotal = $totalBarristerPayment - $totalVat;
                            $this->_conn->query("INSERT INTO `barristerInvoiceItems` (`invoiceId`,`itemType`, `dueDate` ,`caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$barristerInvoiceId."', '1','".$case['endDate']."' ,'".$id."', '".$details."', '".$barristerNet."', '".$vatBarr."', '".$barrTotal."')");

                            if(!empty($case['expence'])){
                                $query_case_expence = $this->_conn->query("SELECT `name` FROM `expenceTypes` WHERE id = '".$case['expenceTypeId']."'");
                                $row_case_expence = $query_case_expence->fetch_assoc();
                                $barrExpVat = $case['expence'] * $case['vat'] / 100;
                                $barrExpTotal = $case['expence'] + $barrExpVat;
                                $this->_conn->query("INSERT INTO `barristerInvoiceItems` (`invoiceId`,`itemType`, `dueDate` ,`caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$barristerInvoiceId."', '2','".$case['endDate']."' ,'".$id."', '".$row_case_expence['name']."', '".$case['expence']."', '".$barrExpVat."', '".$barrExpTotal."')");
                            }

                            $temp = "INSERT INTO `solicitorInvoice` (`teamId`, `barristerId`, `clientId`, `invoiceRef`, `invoiceDate`, `dueDate`, `totalAmount`, `dateAdded`, `addedBy`) VALUES('".$case['teamId']."', '".$case['barristerId']."', '".$row_case['clientId']."', '".$invoiceRef."', '".$dateNow."', '".$case['endDate']."', '".$totalSolicitorPayment."', '".$dateNow."', '".$this->_userId."' )";
                            $this->_conn->query($temp);
                            $solicitorInvoiceId = $this->_conn->insert_id;
                            
                            $activity = 'New Invoice created for Solicitor. Amount : '.$totalSolicitorPayment.' court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                            $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
                    
                            $vatSoli = $case['agreedFees'] * $case['vat']/100;
                            $soliTotal = $case['agreedFees'] + $vatSoli; 
                            $this->_conn->query("INSERT INTO `solicitorInvoiceItems` (`invoiceId`,`itemType`, `dueDate`, `caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$solicitorInvoiceId."','1', '".$case['endDate']."' , '".$id."', '".$details."', '".$case['agreedFees']."', '".$vatSoli."', '".$soliTotal."')");
                            if(!empty($case['expence'])){
                                $soliExpVat = $case['expence'] * $case['vat'] / 100;
                                $soliExpTotal = $case['expence'] + $soliExpVat;
                                $this->_conn->query("INSERT INTO `solicitorInvoiceItems` (`invoiceId`,`itemType`, `dueDate`, `caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$solicitorInvoiceId."', '2', '".$case['endDate']."' , '".$id."', '".$row_case_expence['name']."', '".$case['expence']."', '".$soliExpVat."', '".$soliExpTotal."')");
                            }

                            
                        }else{
                            $newSolicitorPayment = $row_soli_id['totalAmount'] - $totalSolicitorPaymentOld + $totalSolicitorPayment;
                            $newBarristerPayment = $row_bar_id['totalAmount'] - $totalBarristerPaymentOld + $totalBarristerPayment;
    
                            $this->_conn->query("UPDATE solicitorInvoice SET `teamId` = '".$case['teamId']."', `barristerId` = '".$case['barristerId']."',   `dueDate` = '".$case['endDate']."',  `totalAmount` = '".$newSolicitorPayment."' WHERE `id` = '".$row_soli_id['id']."'");
                            $this->_conn->query("UPDATE barristerInvoice SET `barristerId` = '".$case['barristerId']."',  `dueDate` = '".$case['endDate']."', `totalAmount` = '".$newBarristerPayment."' WHERE `id` = '".$row_bar_id['id']."'");
    
                        }

                        $activity = 'Barrister and Solicitor Invoice Updated. Barrister Invoice Amount '.$newBarristerPayment.' And Solicitor Invoice Amount '.$newSolicitorPayment.' Court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                        $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
    
                    }else{
                        
                        $this->_conn->query("UPDATE solicitorInvoice SET `teamId` = '".$case['teamId']."', `barristerId` = '".$case['barristerId']."',   `dueDate` = '".$case['endDate']."',  `totalAmount` = '".$totalSolicitorPayment."' WHERE `id` = '".$row_soli_id['id']."'");
                        $this->_conn->query("UPDATE barristerInvoice SET `barristerId` = '".$case['barristerId']."',  `dueDate` = '".$case['endDate']."', `totalAmount` = '".$totalBarristerPayment."' WHERE `id` = '".$row_bar_id['id']."'");

                        $activity = 'Barrister and Solicitor Invoice Updated. Barrister Invoice Amount '.$totalBarristerPayment.' And Solicitor Invoice Amount '.$totalSolicitorPayment.' Court ref : '.$case['courtRef'].' ( Added By : '.$row_username['userName'].')';
                        $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'case', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
    
                        
                    }

                    $query_soli_item_one = $this->_conn->query("SELECT `id` FROM `solicitorInvoiceItems` WHERE `caseId` = '".$id."' AND `itemType` = '1'");
                    $query_soli_item_two = $this->_conn->query("SELECT `id` FROM `solicitorInvoiceItems` WHERE `caseId` = '".$id."' AND `itemType` = '2'");
                    $query_barr_item_one = $this->_conn->query("SELECT `id` FROM `barristerInvoiceItems` WHERE `caseId` = '".$id."' AND  `itemType` = '1'");
                    $query_barr_item_two = $this->_conn->query("SELECT `id` FROM `barristerInvoiceItems` WHERE `caseId` = '".$id."' AND  `itemType` = '2'");
                    
                    $row_soli_item_one = $query_soli_item_one->fetch_assoc();
                    if($query_soli_item_two->num_rows == 1){
                        $row_soli_item_two = $query_soli_item_two->fetch_assoc();
                    }

                    $row_barr_item_one = $query_barr_item_one->fetch_assoc();
                    if($query_barr_item_two->num_rows == 1){
                        $row_barr_item_two = $query_barr_item_two->fetch_assoc();
                    }


                    $details = $row_case_title['name'];
                    if(!empty($case['additionalWorkId'])){
                        $query_case_additional = $this->_conn->query("SELECT `name` FROM `additionalWorks` WHERE id = '".$case['additionalWorkId']."'");
                        $row_case_additional = $query_case_additional->fetch_assoc();
                        $details .= " ( Including ".$row_case_additional['name']." ) ";
                    }

                    if(!empty($case['concession'])){
                        $details .= " ( ".$case['concession']." ) ";
                    }
                    if(!empty($case['hourlyRate']) && $case['fundingTypeId'] == '7'){
                        $details .= " ( £".number_format($case['hourlyRate'], 2)." Per Hour ) ";
                    }

                    $vatBarr = $barristerNet * $case['vat'] / 100;
                    $barrTotal = $barristerNet + $vatBarr; 
                    $this->_conn->query("UPDATE `barristerInvoiceItems` SET `dueDate` = '".$case['endDate']."',`details` = '".$details."', `amount` = '".$barristerNet."', `vat` = '".$vatBarr."', `total` = '".$barrTotal."' WHERE `id` = '".$row_barr_item_one['id']."'");
                    
                    if(!empty($case['expence'])){
                        $query_case_expence = $this->_conn->query("SELECT `name` FROM `expenceTypes` WHERE id = '".$case['expenceTypeId']."'");
                        $row_case_expence = $query_case_expence->fetch_assoc();
                        $barrExpVat = $case['expence'] * $case['vat'] / 100;
                        $barrExpTotal = $case['expence'] + $barrExpVat;
                        if(!$row_barr_item_two){
                            $this->_conn->query("INSERT INTO `barristerInvoiceItems` (`invoiceId`,`itemType`, `dueDate` ,`caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$row_bar_id['id']."', '2','".$case['endDate']."' ,'".$id."', '".$row_case_expence['name']."', '".$case['expence']."', '".$barrExpVat."', '".$barrExpTotal."')");
                        }else{
                            $this->_conn->query("UPDATE `barristerInvoiceItems` SET  `dueDate` = '".$case['endDate']."', `details` = '".$details."', `amount` = '".$case['expence']."', `vat` = '".$barrExpVat."', `total` = '".$barrExpTotal."' WHERE `id` = '".$row_barr_item_two['id']."'");
                        }
                            
                    }else{
                        $this->_conn->query("DELETE FROM `barristerInvoiceItems` WHERE `caseId` = '".$id."' AND `itemType` = '2'");
                    }

                    $vatSoli = $case['agreedFees'] /100 * $case['vat'];
                    $soliTotal = $case['agreedFees'] + $vatSoli; 
                    $this->_conn->query("UPDATE `solicitorInvoiceItems` SET  `dueDate` = '".$case['endDate']."', `details` = '".$details."' ,  `amount` = '".$case['agreedFees']."' , `vat` = '".$vatSoli."', `total`= '".$soliTotal."' WHERE `id` = '".$row_soli_item_one['id']."'");
                    if(!empty($case['expence'])){
                        $soliExpVat = $case['expence'] /100 * $case['vat'];
                        $soliExpTotal = $case['expence'] + $soliExpVat;
                        if(!$row_soli_item_two){
                            $this->_conn->query("INSERT INTO `solicitorInvoiceItems` (`invoiceId`, `itemType`, `dueDate`, `caseId`, `details`, `amount`, `vat`, `total`) VALUES('".$row_soli_id['id']."', '2', '".$case['endDate']."', '".$id."', '".$row_case_expence['name']."', '".$case['expence']."', '".$soliExpVat."', '".$soliExpTotal."')");
                        }else{
                            $this->_conn->query("UPDATE `solicitorInvoiceItems` SET `dueDate` = '".$case['endDate']."', `details` = '".$row_case_expence['name']."', `amount` = '".$case['expence']."', `vat` = '".$soliExpVat."', `total` = '".$soliExpTotal."' WHERE `id` = '".$row_soli_item_two['id']."'");
                        }
                    }else{
                        $this->_conn->query("DELETE FROM `solicitorInvoiceItems` WHERE `caseId` = '".$id."' AND `itemType` = '2'");
                    }

                }
                
                if($case['caseTitleId'] != $row_check['caseTitleId'] || $case['fundingTypeId'] != $row_check['fundingTypeId'] || $case['hourlyRate'] != $row_check['hourlyRate'] || $case['expenceTypeId'] != $row_check['expenceTypeId'] || $case['concession'] != $row_check['concession'] || $case['additionalWorkId'] != $row_check['additionalWorkId'] || $case['endDate'] != $row_check['endDate']){

                    //update due date
                    $this->_conn->query("UPDATE solicitorInvoice SET dueDate = '".$case['courtDate']."' WHERE `id` = '".$row_soli_id['id']."'");
                    $this->_conn->query("UPDATE barristerSolicitor SET dueDate = '".$case['courtDate']."' WHERE `id` = '".$row_bar_id['id']."'");

                    $query_soli_item_one = $this->_conn->query("SELECT `id` FROM `solicitorInvoiceItems` WHERE `caseId` = '".$id."' AND `itemType` = '1'");
                    $query_soli_item_two = $this->_conn->query("SELECT `id` FROM `solicitorInvoiceItems` WHERE `caseId` = '".$id."' AND `itemType` = '2'");
                    $query_barr_item_one = $this->_conn->query("SELECT `id` FROM `barristerInvoiceItems` WHERE `caseId` = '".$id."' AND `itemType` = '1'");
                    $query_barr_item_two = $this->_conn->query("SELECT `id` FROM `barristerInvoiceItems` WHERE `caseId` = '".$id."' AND `itemType` = '2'");
                    
                    $row_soli_item_one = $query_soli_item_one->fetch_assoc();
                    if($query_soli_item_two->num_rows == 1){
                        $row_soli_item_two = $query_soli_item_two->fetch_assoc();
                    }
                    $row_barr_item_one = $query_barr_item_one->fetch_assoc();
                    if($query_barr_item_two->num_rows == 1){
                        $row_barr_item_two = $query_barr_item_two->fetch_assoc();
                    }


                    $query_case_title = $this->_conn->query("SELECT `name` FROM `caseTitles` WHERE id = '".$case['caseTitleId']."'");
                    $row_case_title = $query_case_title->fetch_assoc();

                    $details = $row_case_title['name'];
                    
                    if(!empty($case['additionalWorkId'])){
                        $query_case_additional = $this->_conn->query("SELECT `name` FROM `additionalWorks` WHERE id = '".$case['additionalWorkId']."'");
                        $row_case_additional = $query_case_additional->fetch_assoc();
                        $details .= " ( Including ".$row_case_additional['name']." ) ";
                    }
                    if(!empty($case['concession'])){
                        $details .= " ( ".$case['concession']." ) ";
                    }
                    if(!empty($case['hourlyRate']) && $case['fundingTypeId'] == '7'){
                        $details .= " ( £".number_format($case['hourlyRate'], 2)." Per Hour ) ";
                    }


                    $this->_conn->query("UPDATE `barristerInvoiceItems` SET `details` = '".$details."', `dueDate` = '".$case['endDate']."' WHERE `id` = '".$row_barr_item_one['id']."'");
                    $query_case_expence = $this->_conn->query("SELECT `name` FROM `expenceTypes` WHERE id = '".$case['expenceTypeId']."'");
                    $row_case_expence = $query_case_expence->fetch_assoc();
                    if($row_barr_item_two){    
                        $this->_conn->query("UPDATE `barristerInvoiceItems` SET `details` = '".$row_case_expence['name']."', `dueDate` = '".$case['endDate']."' WHERE `id` = '".$row_barr_item_two['id']."'");
                    }
                    
                    

                    $vatSoli = $case['agreedFees'] /100 * $case['vat'];
                    $soliTotal = $case['agreedFees'] + $vatSoli; 
                    $this->_conn->query("UPDATE `solicitorInvoiceItems` SET `details` = '".$details."' ,  `dueDate` = '".$case['endDate']."'  WHERE `id` = '".$row_soli_item_one['id']."'");
                    if($row_soli_item_two){
                        $this->_conn->query("UPDATE `solicitorInvoiceItems` SET `details` = '".$row_case_expence['name']."', `dueDate` = '".$case['endDate']."' WHERE `id` = '".$row_soli_item_two['id']."'");
                    }

                }

                if($row_check['active'] != $case['active'] || $row_check['barristerId'] != $case['barristerId'] || $row_check['courtId'] != $case['courtId'] || $row_check['courtDate'] != $case['courtDate'] || $row_check['endDate'] != $case['endDate']){

                    $query_company = $this->_conn->query("SELECT * FROM company");
                    $row_company = $query_company->fetch_assoc();
        
                    if($row_company['emailBarrister'] == '1'){
                        $this->_mail->connect();
                        $emails = [];
                        $emails_old = [];
                        // $soliEmails = [];
                        // if($row_company['emailBarrister'] == '1'){
                        $email_barrister = $this->_conn->query("SELECT `barristers`.`email` FROM `barristers`, `cases` WHERE `cases`.`id` = '".$id."' AND `cases`.`barristerId` = `barristers`.`id`");
                        $row_email_barrister = $email_barrister->fetch_assoc();
                        if(filter_var($row_email_barrister['email'], FILTER_VALIDATE_EMAIL)) {
                            $emails[] = $row_email_barrister['email'];
                        }

                        if($row_check['barristerId'] != $case['barristerId']){
                            $email_barrister_old = $this->_conn->query("SELECT `barristers`.`email` FROM `barristers` WHERE `barristerId` = '".$row_check['barristerId']."'");
                            $row_email_barrister_old = $email_barrister_old->fetch_assoc();
                            if(filter_var($row_email_barrister_old['email'], FILTER_VALIDATE_EMAIL)) {
                                $emails_old[] = $row_email_barrister_old['email'];
                            }
                        }

                        // }
        
                        
                        
                        if(count($emails) > 0 || count($emails_old) > 0){

                            if(count($emails_old) > 0){
                                $subject = "Case Removed from your Diary";
                                $email_body = $this->generateEmailBody($id, 'delete');
                                $this->_mail->transmitEmail($emails_old, $subject, $email_body);
                            }

                            if($row_check['barristerId'] != $case['barristerId']){
                                $subject = "New case added to your Diary";
                                $email_body = $this->generateEmailBody($id, 'save');
                            }else{
                                if($case['active'] == 0){
                                    $subject = "Case Removed from your Diary";
                                    $email_body = $this->generateEmailBody($id, 'delete');
                                }else{
                                    $subject = "Case details Updated";
                                    $email_body = $this->generateEmailBody($id, 'update');
                                }
                            }
                            

                            if($this->_mail->transmitEmail($emails, $subject, $email_body)){
                                $this->_res = ['status'=>'ok', 'message'=>'Case details saved. Email Send!', 'data'=>$id];
                            }else{
                                $this->_res = ['status'=>'ok', 'message'=>'Case Details saved, Could not send email!', 'data'=>$id];
                            }
                        }else{
                            $this->_res = ['status'=>'ok', 'message'=>'Case details saved nicely, No Email to send.', 'data'=>$id];
                        }

                        
        
                    }else{
                        $this->_res = ['status'=>'ok', 'message'=>'Case details saved nicely, Sending email not required!', 'data'=>$id];
                    }

                }else{
                    $this->_res = ['status'=>'ok', 'message'=>'Case details saved nicely, Sending email not required!', 'data'=>$id];
                }
                
            }else{
                $this->_res = ['status'=>'error', 'message'=>'Could not update case!', 'data'=>null];
            }
        }else{
            $this->_res = ['status'=>'error', 'message'=>'Could not find any case to update', 'data'=>null];
        }
        return $this->_res;
    }

    public function deleteCase($filter){

        $query_auth = $this->_conn->query("SELECT `password` FROM `users` WHERE `id` = '".$this->_userId."'");
        $row_auth = $query_auth->fetch_assoc();
        $password = md5($filter['password']);
        $trace = [];
        if($password == $row_auth['password']){
            
            //find invoice id for solicitor and barrister
            $string_duplicated = "SELECT id FROM `cases` WHERE FIND_IN_SET('".$filter['id']."', `cases`.`entryType`)";
            $query_duplicated = $this->_conn->query($string_duplicated);
            $trace[] = $string_duplicated;
            if($query_duplicated->num_rows > 0){
                $trace[] = 'Case could not delete, duplicated';
                $this->_res = ['status'=>'error', 'message'=>'This case has been duplicated, Please consider deleting duplicated case first !', 'data'=>null];
            
            }else{

                //if the case is a duplicated case
                $query_entry = $this->_conn->query("SELECT * FROM `cases` WHERE `id` = '".$filter['id']."'");
                $row_entry = $query_entry->fetch_assoc();

                

                if($row_entry['entryType'] != 0){
                    $trace[] = 'Entry type is not 0';
                    //if case is duplicated, then calculate the commission for the selected case
                    //deduct the amount from the invoice
                    //delete item from invoice
                    $commission_old = $row_entry['commission'] / 100 * $row_entry['agreedFees'];
                    $caseTotalOld = $row_entry['agreedFees'] + $row_entry['expence'];
                    $totalVatOld  = $row_entry['vat'] * $caseTotalOld  / 100;
                    $totalSolicitorPaymentOld = $row_entry['agreedFees'] + $row_entry['expence'] + $totalVatOld;

                    $oldNet = $row_entry['agreedFees'] + $row_entry['expence'] - $commission_old;
                    $old_vat = $row_entry['vat'] * $oldNet  / 100;
                    $totalBarristerPaymentOld = $oldNet - $old_vat;

                    $get_soli_id = $this->_conn->query("SELECT solicitorInvoice.id, solicitorInvoice.totalAmount FROM solicitorInvoice, solicitorInvoiceItems WHERE solicitorInvoiceItems.caseId = '".$filter['id']."' and solicitorInvoiceItems.invoiceId = solicitorInvoice.id");
                    $row_soli_id = $get_soli_id->fetch_assoc();
                    $get_bar_id = $this->_conn->query("SELECT barristerInvoice.id, barristerInvoice.totalAmount FROM barristerInvoice, barristerInvoiceItems WHERE barristerInvoiceItems.caseId = '".$filter['id']."' and barristerInvoiceItems.invoiceId = barristerInvoice.id");
                    $row_bar_id = $get_bar_id->fetch_assoc();

                    $newSolicitorPayment = $row_soli_id['totalAmount'] - $totalSolicitorPaymentOld;
                    $newBarristerPayment = $row_bar_id['totalAmount'] - $totalBarristerPaymentOld;
                    
                    $string_solicitor = "UPDATE solicitorInvoice SET `totalAmount` = '".$newSolicitorPayment."' WHERE `id` = '".$row_soli_id['id']."'";
                    $trace[] = $string_solicitor;
                    $this->_conn->query($string_solicitor);
                    $this->_conn->query("UPDATE barristerInvoice SET `totalAmount` = '".$newBarristerPayment."' WHERE `id` = '".$row_bar_id['id']."'");
                    $string_delete = "DELETE FROM `solicitorInvoiceItems` WHERE `caseId` = '".$filter['id']."' ";
                    $trace[] = $string_delete;
                    $this->_conn->query($string_delete);
                    $this->_conn->query("DELETE FROM `barristerInvoiceItems` WHERE `caseId` = '".$filter['id']."' ");


                }else{
                    $string_solicitor = "SELECT `invoiceId`, SUM(`amount`) as amount, SUM(`vat`) as vat, SUM(`total`) as total FROM `solicitorInvoiceItems` WHERE `caseId` = '".$filter['id']."' GROUP BY `invoiceId`";
                    $trace[] = $string_solicitor;
                    $query_solicitor = $this->_conn->query($string_solicitor);
                    $row_solicitor = $query_solicitor->fetch_assoc();

                    $query_barrister = $this->_conn->query("SELECT `invoiceId`, SUM(`amount`) as amount, SUM(`vat`) as vat, SUM(`total`) as total FROM `barristerInvoiceItems` WHERE `caseId` = '".$filter['id']."' GROUP BY `invoiceId`");
                    $row_barrister = $query_barrister->fetch_assoc();
                    //delete invoice and items
                    //delete solicitor and barrister invoice items and the payment record
                    $this->_conn->query("DELETE FROM `solicitorPayments` WHERE `invoiceId` = '".$row_solicitor['invoiceId']."' ");
                    $this->_conn->query("DELETE FROM `solicitorInvoiceItems` WHERE `invoiceId` = '".$row_solicitor['invoiceId']."' ");
                    $this->_conn->query("DELETE FROM `barristerPayments` WHERE `invoiceId` = '".$row_barrister['invoiceId']."' ");
                    $this->_conn->query("DELETE FROM `barristerInvoiceItems` WHERE `invoiceId` = '".$row_barrister['invoiceId']."' ");

                    $trace[] = 'all invoice deleted';
                }

                //delete case activities and task
                $this->_conn->query("DELETE FROM `activities` WHERE `modelId` = '".$filter['id']."' AND `modelName` = 'case' ");
                $this->_conn->query("DELETE FROM `tasks` WHERE `modelId` = '".$filter['id']."' AND `modelName` = 'case' ");

                //loop through documents and delete document from folder
                $query_documents = $this->_conn->query("SELECT `id`, `file` FROM `documents` WHERE `modelId` = '".$filter['id']."' AND `modelName` = 'case'");
                if($query_documents->num_rows > 0){
                    $path = '../../documents/case/';
                    //delete all files from the folder
                    while($row_documents = $query_documents->fetch_assoc()){
                        $filePath = $path.$row_documents['file'];
                        if(file_exists($filePath)) unlink($filePath);
                    }

                    //delete all entry in database
                    $this->_conn->query("DELETE FROM `documents` WHERE `modelId` = '".$filter['id']."' AND `modelName` = 'case'");

                    

                }

                

                $query_company = $this->_conn->query("SELECT * FROM company");
                $row_company = $query_company->fetch_assoc();
    
                if($row_company['emailBarrister'] == '1'){
                $subject = "Case removed from your diary";
                $this->_mail->connect();
                $emails = [];
                // $soliEmails = [];
                // if($row_company['emailBarrister'] == '1'){
                $email_barrister = $this->_conn->query("SELECT `barristers`.`email` FROM `barristers`, `cases` WHERE `cases`.`id` = '".$filter['id']."' AND `cases`.`barristerId` = `barristers`.`id`");
                $row_email_barrister = $email_barrister->fetch_assoc();
                if(filter_var($row_email_barrister['email'], FILTER_VALIDATE_EMAIL)) {
                    $emails[] = $row_email_barrister['email'];
                }

                // }

                //delete case
                $this->_conn->query("DELETE FROM `cases` WHERE `id` = '".$filter['id']."'");
                if(count($emails) > 0){

                    $email_body = $this->generateEmailBody($filter['id'], 'delete');

                    if($this->_mail->transmitEmail($emails, $subject, $email_body)){
                        $this->_res = ['status'=>'ok', 'message'=>'Case details saved. Email Send!', 'data'=>$id];
                    }else{
                        $this->_res = ['status'=>'ok', 'message'=>'Case Details saved, Could not send email!', 'data'=>$id];
                    }
                }else{
                    $this->_res = ['status'=>'ok', 'message'=>'Case details saved nicely, No Email to send.', 'data'=>$id];
                }

                    
    
                }else{
                    $this->_res = ['status'=>'ok', 'message'=>'Case details saved nicely, Sending email not required!', 'data'=>$id];
                }
                $this->_res = ['status'=>'ok', 'message'=>'Case and all related data including documents has been deleted!', 'data'=>$trace];
            }

        }else{
            $this->_res = ['status'=>'error', 'message'=>'Authorisation failed. Password did not match!', 'data'=>$data];
        }
        // $this->_res = ['status'=>'error', 'message'=>'Delete function is inactive!', 'data'=>$data];
        return $this->_res;
    }

}

?>