<?php

class Invoice {

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

	public function __construct($conn, $userId){
		$this->_conn = $conn;
		$this->_userId = $userId;
	}
    public function getInvoices($data){

    }

    public function getInvoice($data){

    }

    public function savePayment($data){

    }
    public function removePayment($data){

    }

    // public function getInvoices($filter){

    //    if($filter['tableName'] == 'barrister'){
    //         $query_string = "SELECT `barristerInvoice`.*, SUM(`barristerInvoice`.`totalAmount`) as totalInvoiceAmount, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName,  ( SUM(`barristerInvoice`.`totalAmount`) - SUM(`barristerInvoice`.`totalPaid`)) as totalDue FROM `barristerInvoice`, `barristers`, `titles` WHERE `barristerInvoice`.`barristerId` = `barristers`.`id` AND `barristers`.`titleId` = `titles`.`id`";

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

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

    //         $query_string .=  " AND `barristers`.`active` = '1' GROUP BY `barristerInvoice`.`invoiceRef`  ORDER BY barristerInvoice.dueDate";
    //         if($filter['limit'] != 'all'){
    //             $query_string .= " LIMIT  10";
    //         }

    //     }else if($filter['tableName'] == 'solicitor'){

    //         $query_string = "SELECT `solicitorInvoice`.*, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName, (`solicitorInvoice`.`totalAmount` - `solicitorInvoice`.`totalPaid`) as totalDue, CONCAT(`solicitors`.`fname`, ' ', `solicitors`.`lname`) as solicitorName, `solicitorCompany`.`companyName` FROM `solicitorInvoice`,   `barristers`, `titles`, `solicitors`, `solicitorCompany` WHERE `solicitorInvoice`.`teamId` = `solicitors`.`id` AND `solicitors`.`companyId` = `solicitorCompany`.`id` AND `solicitorInvoice`.`barristerId` = `barristers`.`id` ";
    //         if($filter['solicitorId'] != 'all'){
    //             $query_string .= " AND `solicitorCompany`.`id` = '".$filter['solicitorId']."' ";
    //         }

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

    //         $query_string .=  " AND `solicitorCompany`.`active` ='1' GROUP BY `solicitorInvoice`.`invoiceRef` ORDER BY solicitorInvoice.dueDate";
    //         if($filter['limit'] != 'all'){
    //             $query_string .= " LIMIT  10";
    //         }

    //     }else if($filter['tableName'] == 'client'){

    //         $query_string = "SELECT `solicitorInvoice`.*, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName, (`solicitorInvoice`.`totalAmount` - `solicitorInvoice`.`totalPaid`) as totalDue, CONCAT(`clients`.`clientFname`, ' ', `clients`.`clientLname`) as clientName FROM `solicitorInvoice`, `barristers`, `titles`, `clients` WHERE `solicitorInvoice`.`clientId` = `clients`.`id` AND `solicitorInvoice`.`barristerId` = `barristers`.`id` ";
            
    //         if($filter['clientId'] != 'all'){
    //             $query_string .= " AND `solicitorInvoice`.`clientId` = '".$filter['clientId']."' ";
    //         }

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

    //         $query_string .=  " AND `clients`.`active` = '1' GROUP BY `solicitorInvoice`.`invoiceRef` ORDER BY solicitorInvoice.dueDate";

    //         if($filter['limit'] != 'all'){
    //             $query_string .= " LIMIT  10";
    //         }

    //     }

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

    //     $data = [];

    //     if($query->num_rows > 0){

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

    //             //get case ref 
    //             if($filter['tableName'] == 'barrister'){
    //                 $caseRefString = "SELECT `cases`.`id`, `cases`.`courtRef`, `cases`.`claimant`, `cases`.`defendant` FROM `cases`, `barristerInvoiceItems` WHERE barristerInvoiceItems.invoiceId = '".$row['id']."' AND `cases`.`id` = `barristerInvoiceItems`.`caseId` GROUP BY barristerInvoiceItems.caseId ORDER BY barristerInvoiceItems.id";
    //             }else if($filter['tableName'] == 'solicitor'){
    //                 $caseRefString = "SELECT `cases`.`id`, `cases`.`courtRef`, `cases`.`claimant`, `cases`.`defendant` FROM `cases`, `solicitorInvoiceItems` WHERE solicitorInvoiceItems.invoiceId = '".$row['id']."' AND `cases`.`id` = `solicitorInvoiceItems`.`caseId` GROUP BY solicitorInvoiceItems.caseId ORDER BY solicitorInvoiceItems.id";
    //             }else if($filter['tableName'] == 'client'){
    //                 $caseRefString = "SELECT `cases`.`id`, `cases`.`courtRef`, `cases`.`claimant`, `cases`.`defendant` FROM `cases`, `solicitorInvoiceItems` WHERE solicitorInvoiceItems.invoiceId = '".$row['id']."' AND `cases`.`id` = `solicitorInvoiceItems`.`caseId` GROUP BY solicitorInvoiceItems.caseId ORDER BY solicitorInvoiceItems.id";
    //             }

    //             switch ($row['status']) {
    //                 case '0' :
    //                     $statusColor = 'danger';
    //                     $statusText = "Pending";
    //                     break;

    //                 case '1' : 
    //                     $statusColor = 'primary';
    //                     $statusText = 'Closed';
    //                     break;

    //                 case '2' :
    //                     $statusColor = 'info';
    //                     $statusText = 'Disputed';
    //                     break;

    //                 default :
    //                     $statusColor = 'danger';
    //                     $statusText = 'Pending';
    //                     break;
    //             }

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

    //             $query_case_ref = $this->_conn->query($caseRefString);
    //             $entry = false;

    //             while($row_case_ref = $query_case_ref->fetch_assoc()){
    //                 $row['courtRef'] = $row_case_ref['courtRef'];
    //                 $row['claimant'] = $row_case_ref['claimant'];
    //                 $row['defendant'] = $row_case_ref['defendant'];
    //                 if($filter['caseId'] != 'all'){
    //                     if($filter['caseId'] == $row_case_ref['id']){
    //                         $entry = true;
    //                     }
    //                 }else{
    //                     $entry = true;
    //                 }
    //             }

    //             if($entry){
    //                 $data[] = $row;
    //             }

    //         }

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

    //     }else{
    //         $this->_res = ['status'=>'error', 'message'=>'No Invoice Found', 'data'=>$query_string];
    //     }
    //     return $this->_res;
    // }



    // public function getInvoice($filter){

    //     if($filter['tableName'] == 'client'){
    //         $filter['tableName'] = 'solicitor';
    //     }

    //     $tableName = $filter['tableName'].'Invoice';

    //     $tableNameItems = $filter['tableName'].'InvoiceItems';


    //     $query_string = "SELECT `".$tableName."`.* FROM `".$tableName."` WHERE `".$tableName."`.`id` = '".$filter['id']."' ";

        

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



    //     $data = [];

        

    //     if($query->num_rows == 1){

            

    //         $row = $query->fetch_assoc();



    //         //get all the cases

    //         $query_cases = $this->_conn->query("SELECT  `cases`.*, min(`cases`.`courtDate`) as courtDate, max(`cases`.`endDate`) as endDate,  `caseTypes`.`name` as caseTypeName, `fundingTypes`.`name` as fundingTypeName, `courts`.`courtName`, CONCAT(`titles`.`name`, ' ', `barristers`.`fname`, ' ', `barristers`.`lname`) as barristerName, `barristers`.`vatNo`, `barristers`.`legalAidNumber` FROM `".$tableNameItems."`, `cases`, `barristers`, `caseTypes`, `fundingTypes`, `courts`, `titles` WHERE `".$tableNameItems."`.`invoiceId` = '".$row['id']."' AND `".$tableNameItems."`.`caseId` = `cases`.`id` 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' ORDER BY `cases`.`id` DESC limit 1");

    //         $row_case = $query_cases->fetch_assoc();



    //         $row = array_merge($row, $row_case);



            

    //         if($row['clientTypeId'] == 'Solicitor'){

    //             $query_solicitors = $this->_conn->query("SELECT CONCAT(`titles`.`name`, ' ', `solicitors`.`fname`, `solicitors`.`lname`) as solicitorName, `solicitorCompany`.`companyName`, `solicitorCompany`.`address`, `solicitorCompany`.`address1`, `solicitorCompany`.`city`, `solicitorCompany`.`postcode` FROM `solicitors`, `solicitorCompany`, `titles` WHERE `solicitors`.`id` = '".$row['teamId']."' AND `solicitors`.`companyId` = `solicitorCompany`.`id` AND `solicitors`.`titleId` = `titles`.`id`");

    //             $row_solicitors = $query_solicitors->fetch_assoc();

    //             $row['solicitor'] = $row_solicitors;

    //         }else{

    //             $query_client = $this->_conn->query("SELECT CONCAT(`titles`.`name`, ' ', `clients`.`clientFname`, ' ', `clients`.`clientLname`) as clientName, `clients`.`clientAddress`, `clients`.`clientPostcode` FROM `clients`, `titles` WHERE `clients`.`id` = '".$row['clientId']."' and `clients`.`clientTitle` = `titles`.`id`");

    //             $row_client = $query_client->fetch_assoc();

    //             $row['client'] = $row_client;

    //         }

            



    //         switch ($row['status']) {

    //             case '0' :

    //                 $statusColor = 'danger';

    //                 $statusText = "Pending";

    //                 break;

    //             case '1' : 

    //                 $statusColor = 'primary';

    //                 $statusText = 'Closed';

    //                 break;

    //             case '2' :

    //                 $statusColor = 'info';

    //                 $statusText = 'Disputed';

    //                 break;

    //             default :

    //                 $statusColor = 'dander';

    //                 $statusText = 'Pending';

    //                 break;

    //         }



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

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

            

            

    //         //invoiceTable and payment Table 

    //         $invoiceTable = $filter['tableName'].'Invoice';

    //         $invoiceItemTable = $filter['tableName'].'InvoiceItems';

    //         $paymentTable = $filter['tableName'].'Payments';



    //         $query_payments_string = "SELECT * FROM ( SELECT  `".$invoiceItemTable."`.`id`, `".$invoiceItemTable."`.`invoiceId`, `".$invoiceItemTable."`.`details`, `".$invoiceItemTable."`.`amount`, `".$invoiceItemTable."`.`vat` as vat, `".$invoiceItemTable."`.`total` as total, `".$invoiceItemTable."`.`dueDate`, 'charge' as invoiceType, `cases`.`endDate`  FROM `".$invoiceItemTable."`, `".$invoiceTable."`, `cases`  WHERE `".$invoiceItemTable."`.`invoiceId` = '".$filter['id']."' AND `".$invoiceItemTable."`.`invoiceId` = `".$invoiceTable."`.`id` AND `".$invoiceItemTable."`.`caseId` = `cases`.`id` UNION SELECT `".$paymentTable."`.`id`, `".$paymentTable."`.`invoiceId`, CONCAT(`".$paymentTable."`.`note`, ' | Paid By : ', `".$paymentTable."`.`paymentMethod`) as details, null as total, null as vat, `".$paymentTable."`.`paymentAmount` as total, `".$paymentTable."`.`paymentDate` as dueDate, 'payment' as invoiceType, `cases`.`endDate` FROM `".$paymentTable."`, `".$invoiceItemTable."`, `cases` WHERE `".$paymentTable."`.`invoiceId` = '".$filter['id']."' AND `".$paymentTable."`.`invoiceId` =  `".$invoiceItemTable."`.`invoiceId` AND `cases`.`id` =  `".$invoiceItemTable."`.`caseId`) x ORDER BY dueDate";
    //         // $query_payments_string = "SELECT * FROM ( SELECT  `".$invoiceItemTable."`.`id`, `".$invoiceItemTable."`.`invoiceId`, `".$invoiceItemTable."`.`details`, `".$invoiceItemTable."`.`amount`, `".$invoiceItemTable."`.`vat` as vat, `".$invoiceItemTable."`.`total` as total, `".$invoiceItemTable."`.`dueDate`, 'charge' as invoiceType  FROM `".$invoiceItemTable."`, `".$invoiceTable."`  WHERE `".$invoiceItemTable."`.`invoiceId` = '".$filter['id']."' AND `".$invoiceItemTable."`.`invoiceId` = `".$invoiceTable."`.`id`  UNION SELECT `".$paymentTable."`.`id`, `".$paymentTable."`.`invoiceId`, CONCAT(`".$paymentTable."`.`note`, ' | Paid By : ', `".$paymentTable."`.`paymentMethod`) as details, null as total, null as vat, `".$paymentTable."`.`paymentAmount` as total, `".$paymentTable."`.`paymentDate` as dueDate, 'payment' as invoiceType FROM `".$paymentTable."` WHERE `".$paymentTable."`.`invoiceId` = '".$filter['id']."' ) x ORDER BY dueDate";

    //         $query_payments = $this->_conn->query($query_payments_string);



    //         $payments = [];

    //         $totalAmount = 0.00;

    //         $totalVat = 0.00;

    //         $totalDue = 0.00;

    //         while($row_payments = $query_payments->fetch_assoc()){
                

    //             if($row_payments['invoiceType'] == 'charge'){

    //                 $totalDue = $totalDue + $row_payments['total'];
    //                 $totalAmount = $totalAmount + $row_payments['amount'];
    //                 $totalVat = $totalVat + $row_payments['vat'];

    //             }else{

    //                 $totalDue = $totalDue - $row_payments['total'];

    //             }



    //             $payments[] = $row_payments;    

    //         }

    //         $row['payments'] = $payments;



    //         // $row['payments'] = $query_payments_string;



    //         $balance = [];

    //         $balance['totalAmount'] = $totalAmount;

    //         $balance['totalVat'] = $totalVat;

    //         $balance['totalDue'] = $totalDue;



    //         $row['balance'] = $balance;

            

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

    //     }else{

    //         $this->_res = ['status'=>'error', 'message'=>$filter, 'data'=>$query_payments_string];

    //     }



    //     return $this->_res;

    // }




    // public function savePayment($data){



    //     $invoiceTable = $data['tableName'].'Invoice';

    //     $paymentTable = $data['tableName'].'Payments';



    //     $query_invoice = $this->_conn->query("SELECT `totalAmount`, `totalPaid` FROM `".$invoiceTable."` WHERE `id` = '".$data['invoiceId']."' ");



    //     if($query_invoice->num_rows == 1){

    //         $row_invoice = $query_invoice->fetch_assoc();

    //         $balanceDue = $row_invoice['totalAmount'] - $row_invoice['totalPaid'];



    //         if($balanceDue < $data['amount']){

    //             $this->_res = ['status'=>'error', 'message'=>'Paying amount can not be higher than due amount!'.$balanceDue, 'data'=>null];

    //         }else{

                

    //             $query_insert = $this->_conn->query("INSERT INTO `".$paymentTable."` (`invoiceId`, `paymentAmount`, `paymentMethod`, `note`, `paymentDate`, `addedBy`) VALUES('".$data['invoiceId']."', '".$data['amount']."', '".$data['paymentMethod']."', '".$data['details']."','".$data['paymentDate']."', '".$this->_userId."')");

    //             if($query_insert){

    //                 $payment_id = $this->_conn->insert_id;

    //                 //update the invoice

    //                 if($balanceDue == $data['amount']){

    //                     $status = '1';

    //                 }else{

    //                     $status = '0';

    //                 }



    //                 $query_update = $this->_conn->query("UPDATE `".$invoiceTable."` SET `totalPaid` = `totalPaid` + '".$data['amount']."', `status` = '".$status."' WHERE `id`= '".$data['invoiceId']."'");



    //                 if($query_update){

    //                     $this->_res = ['status'=>'ok', 'message'=>'Payment saved, Invoice updated!', 'data'=>null];

    //                 }else{

    //                     //remove payment

    //                     $this->_conn->query("DELETE FROM `".$paymentTable."` WHERE `id` = '".$payment_id."'");

    //                     $this->_res = ['status'=>'error', 'message'=>'Could not update invoice, Internal server error!', 'data'=>null];

    //                 }

    //             }else{

    //                 $this->_res = ['status'=>'error', 'message'=>'Could not save payment, Internal server error!', 'data'=>null];

    //             }

    //         }

    //     }else{

    //         $this->_res = ['status'=>'error', 'message'=>'No invoice found to add payment!', 'data'=>null];

    //     }



    //     return $this->_res;

    // }



    // public function removePayment($data){
    //     $tableName = $data['tableName'].'Invoice';
    //     $tablePayment = $data['tableName'].'Payments';
    //     $query_auth = $this->_conn->query("SELECT `password` FROM `users` WHERE `id` = '".$this->_userId."'");
    //     $row_auth = $query_auth->fetch_assoc();
    //     $password = md5($data['password']);

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

    //         $query_check = $this->_conn->query("SELECT `invoiceId`, `paymentAmount` FROM `".$tablePayment."` WHERE `id` = '".$data['id']."'");
    //         $row_check = $query_check->fetch_assoc();
    //         //remove payments

    //         $query_delete = $this->_conn->query("DELETE FROM `".$tablePayment."` WHERE `id` = '".$data['id']."'");

    //         if($query_delete){
    //             //update invoice
    //             $query_update = $this->_conn->query("UPDATE `".$tableName."` SET `totalPaid` = `totalPaid` - '".$row_check['paymentAmount']."', `status`= '0' WHERE `id` = '".$row_check['invoiceId']."'");

    //             if($query_update){

    //                 $this->_res = ['status'=>'ok', 'message'=>'Payment record deleted!', 'data'=>null];

    //             }else{

    //                 $this->_res = ['status'=>'ok', 'message'=>'Payment record deleted but could not update Invoice!', 'data'=>null];

    //             }

    //         }else{

    //             $this->_res = ['status'=>'error', 'message'=>'Could not delete payment record!', 'data'=>null];

    //         }



    //     }else{

    //         $this->_res = ['status'=>'error', 'message'=>'Authorisation failed. Password did not match!', 'data'=>null];

    //     }



    //     return $this->_res;



    // }



}



?>


