<?php

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

    public function getSolicitors($filter){
        $query_string = "SELECT `solicitorCompany`.*, CONCAT(`users`.`fname`, ' ', `users`.`lname`) as username FROM `solicitorCompany`, `users` WHERE `solicitorCompany`.`addedBy` = `users`.`id`";
        if($filter['active'] != 'all'){
            $query_string.= " AND `solicitorCompany`.`active` = '".$filter['active']."'";
        }
        if($filter['advancePayment'] != 'all'){
            $query_string.= " AND `solicitorCompany`.`advancePayment` = '".$filter['advancePayment']."'";
        }

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

        if($query_solicitors->num_rows > 0){

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

                $query_team = $this->_conn->query("SELECT * FROM `solicitors` WHERE `companyId` = '".$row['id']."'");
                $teams = [];
                while($row_team = $query_team->fetch_assoc()){
                    $teams[] = $row_team;
                }
                $row['teams'] = $teams;


                $query_pending = $this->_conn->query("SELECT COUNT(id) as total FROM `cases` WHERE `solicitorId` = '".$row['id']."' AND `status` = '0'");
                $row_pending = $query_pending->fetch_assoc();
                $row['pending'] = $row_pending['total'];
                $query_all = $this->_conn->query("SELECT COUNT(id) as total FROM `cases` WHERE `solicitorId` = '".$row['id']."' AND `status` = '1'");
                $row_all = $query_all->fetch_assoc();
                $row['all'] = $row_all['total'];

                $data[] = $row;

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

    public function getSolicitor($p){
        $query_string = "SELECT `solicitorCompany`.*, CONCAT(`users`.`fname`, ' ', `users`.`lname`) as username FROM `solicitorCompany`, `users` WHERE `solicitorCompany`.`addedBy` = `users`.`id` AND `solicitorCompany`.`id` = '".$p['id']."'";

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

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

            $row = $query_solicitors->fetch_assoc();
            //get the team
            $query_team = $this->_conn->query("SELECT CONCAT(`titles`.`name`, ' ', `solicitors`.`fname`, ' ', `solicitors`.`lname`) as fullname, `solicitors`.*, CONCAT(`users`.`fname`, ' ', `users`.`lname`) as username FROM `solicitors`, `users`, `titles` WHERE `companyId` = '".$p['id']."' AND `solicitors`.`addedBy` = `users`.`id` AND `solicitors`.`titleId` = `titles`.`id`");
            $teams = [];
            while($row_team = $query_team->fetch_assoc()){
                switch ($row_team['advancePayment']){
                    case '1' :
                        $paymentColor = 'danger';
                        $paymentText = 'Required';
                        break;
                    case '0' :
                        $paymentColor = 'primary';
                        $paymentText = 'Not Required';
                        break;
                }
                $row_team['paymentText'] = $paymentText;
                $row_team['paymentColor'] = $paymentColor;
                $teams[] = $row_team;
            }
            $row['teams'] = $teams;
            //get pending cases
            $query_pending = $this->_conn->query("SELECT * FROM `cases` WHERE `solicitorId` = '".$p['id']."' AND `status` = '1'");
            if($query_pending->num_rows > 0){
                $cases = [];
                while($row_pending = $query_pending->fetch_assoc()){
                    $cases[] = $row_pending;
                }
            }

            $row['cases'] = $cases;

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

    public function saveSolicitor($data){
        $query_check = $this->_conn->query("SELECT `id` FROM `solicitorCompany` WHERE LOWER(`companyName`) = LOWER('".$data['companyName']."')");
        if($query_check->num_rows > 0){
            $this->_res = ['status'=>'error', 'message'=>' :: Solicitor Farm already exist!', 'data'=>null];
        }else{
            $dateNow = date('Y-m-d');
            $query_string = "INSERT INTO `solicitorCompany` (`companyName`,`tradingName`, `address`,`address1`,`city`, `postcode`, `email`, `telephone`, `fax`, `vatNo`, `regNo`, `website`, `advancePayment`, `joinedDate`, `note`, `dateAdded`, `addedBy`) VALUES('".$data['companyName']."', '".$data['tradingName']."', '".$data['address']."', '".$data['address1']."', '".$data['city']."', '".$data['postcode']."', '".$data['email']."', '".$data['telephone']."', '".$data['fax']."', '".$data['vatNo']."', '".$data['regNo']."', '".$data['website']."', '".$data['advancePayment']."', '".$dateNow."', '".$data['notes']."', '".$dateNow."', '".$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 = $data['companyName'].' Solicitor Firm Details added to the system ( Added  By : '.$row_username['userName'].')';
                $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'solicitor', '".$this->_userId."', '".$dateActivity."', '".$activity."')");
            
                $this->_res = ['status'=>'ok', 'message'=>' :: Solicitor Farm details saved nicely, Thanks', 'data'=>$id];
            }else{
                $this->_res = ['status'=>'error', 'message'=>' :: Could not save Solicitor Farm details, Internal server error!', 'data'=>$query_string];
            }
        }
        return $this->_res;
    }


    public function generateReport($data){

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

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

        if($query->num_rows > 0){

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

            $data= [];
            $counter = 1;

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

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

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

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

                $row['index'] = $counter;

                $data[] = $row;

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

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

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

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

    }

    public function updateSolicitor($id, $data){
        $query_check = $this->_conn->query("SELECT `companyName` FROM `solicitorCompany` WHERE `id` = '".$id."'");
        if($query_check->num_rows == 1){

            $query = $this->_conn->query("UPDATE `solicitorCompany` SET `companyName` = '".$data['companyName']."', `tradingName` = '".$data['tradingName']."', `address` = '".$data['address']."', `address1` = '".$data['address1']."', `city` = '".$data['city']."', `postcode` = '".$data['postcode']."', `email` = '".$data['email']."', `telephone` = '".$data['telephone']."', `fax` = '".$data['fax']."', `vatNo` = '".$data['vatNo']."', `regNo` = '".$data['regNo']."' , `website` = '".$data['website']."',  `advancePayment` = '".$data['advancePayment']."', `note` = '".$data['notes']."', `active` = '".$data['active']."' WHERE `id` = '".$id."'");
            if($query){

                $query_username = $this->_conn->query("SELECT CONCAT(`fname`, ' ', `lname`) as userName FROM `users` WHERE `id` = '".$this->_userId."'");
                $row_username = $query_username->fetch_assoc();
                $dateActivity = date('Y-m-d : h:i');

                $activity = $data['companyName'].' Solicitor Firm Details updated to the system ( Updated  By : '.$row_username['userName'].')';
                $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$id."', 'solicitor', '".$this->_userId."', '".$dateActivity."', '".$activity."')");

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

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

    public function getSolicitorTeam($id){
        $query = $this->_conn->query("SELECT * FROM solicitors WHERE id = '".$id."'");
        if($query->num_rows == 1){
            $row = $query->fetch_assoc();
            $this->_res = ['status'=>'ok', 'message'=>'', 'data'=>$row];
        }else{
            $this->_res = ['status'=>'error', 'message'=>'No Solicitor Found!', 'data'=>null];
        }
        return $this->_res;
    }

    public function updateSolicitorTeam($id, $data){

        $query_check = $this->_conn->query("SELECT `fname`, `companyId` FROM `solicitors` WHERE `id` = '".$id."'");
        if($query_check->num_rows == 1){
            $row_check = $query_check->fetch_assoc();
            //update the team
            $string = "UPDATE `solicitors` SET `titleId` = '".$data['titleId']."', `fname`= '".$data['fname']."', `lname` = '".$data['lname']."', `email` = '".$data['email']."', `emailTwo` = '".$data['emailTwo']."', `telephone` = '".$data['telephone']."', `directTelephone` = '".$data['directTelephone']."', `mobile` = '".$data['mobile']."', `note` = '".$data['note']."', `advancePayment` = '".$data['advancePayment']."' ,`active` = '".$data['active']."' WHERE `id` = '".$id."'";
            $query = $this->_conn->query($string);
            if($query){

                $query_username = $this->_conn->query("SELECT CONCAT(`fname`, ' ', `lname`) as userName FROM `users` WHERE `id` = '".$this->_userId."'");
                $row_username = $query_username->fetch_assoc();
                $dateActivity = date('Y-m-d : h:i');

                $activity = $data['fname'].' '.$data['lname'].' Solicitor Details added to the system ( Added  By : '.$row_username['userName'].')';
                $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$row_check['solicitorId']."', 'solicitor', '".$this->_userId."', '".$dateActivity."', '".$activity."')");

                $this->_res = ['status'=>'ok', 'message'=>' :: Solicitor details updated!.','data'=>null];
            }else{
                $this->_res = ['status'=>'error', 'message'=>' :: Could not update solicitor details !','data'=>$string];
            }
        }else{
            $this->_res = ['status'=>'error', 'message'=>' :: No solicitor details found !','data'=>$string];
        }
        return $this->_res;
    }

    public function saveSolicitorTeam($data){

        $dateNow = date('Y-m-d');
        $query_string = "INSERT INTO `solicitors` (`companyId`, `titleId`, `fname`, `lname`, `email`, `emailTwo`, `telephone`, `directTelephone`, `mobile`, `note`, `advancePayment`, `dateAdded`, `addedBy`) VALUES('".$data['solicitorId']."', '".$data['titleId']."', '".$data['fname']."', '".$data['lname']."', '".$data['email']."', '".$data['emailTwo']."', '".$data['telephone']."', '".$data['directTelephone']."', '".$data['mobile']."', '".$data['advancePayment']."', '".$data['note']."', '".$dateNow."', '".$this->_userId."')";
        $query = $this->_conn->query($query_string);
        if($query){

            $query_username = $this->_conn->query("SELECT CONCAT(`fname`, ' ', `lname`) as userName FROM `users` WHERE `id` = '".$this->_userId."'");
            $row_username = $query_username->fetch_assoc();
            $dateActivity = date('Y-m-d : h:i');
            
            $activity = $data['fname'].' '.$data['lname'].' Solicitor Details updated to the system ( Added  By : '.$row_username['userName'].')';
            $this->_conn->query("INSERT INTO `activities` (`modelId`, `modelName`, `userId`, `activityDate`, `activity`) VALUES('".$data['solicitorId']."', 'solicitor', '".$this->_userId."', '".$dateActivity."', '".$activity."')");

            $this->_res = ['status'=>'ok', 'message'=>' :: Solicitor details updated!.','data'=>null];
        }else{
            $this->_res = ['status'=>'error', 'message'=>' :: Could not save solicitor details! !','data'=>$query_string];
        }

        return $this->_res;
    }

}


?>