<?php 
namespace App\Models;
use CodeIgniter\Model;
class InvoiceModel extends Model
{
    protected $table = 'invoice';
    protected $primaryKey = 'invoice_id';
    protected $allowedFields = ['invoice_id','invoice_number','sale_order','invoice_date','sales_commision','client_name','status','vehicle_reg_number','billing_address','billing_city','billing_state','billing_country','billing_postal_code','terms_condition','job_card_id','isactive',
                                'type','mobile_no','email','invoice_child_id','order_number','mode_of_payment','subtotal','tax','discount','total','terms_condition','branch_id','vehicle_id','paid_advance' ];
    protected $beforeInsert = ['setCreatedBy'];
    protected $beforeUpdate = ['setUpdatedBy'];

    protected function setCreatedBy(array $data)
    {
        $data['data']['created_by'] = (int) session()->get('logged_user');
        return $data;
    }

    protected function setUpdatedBy(array $data)
    {
        $data['data']['updated_by'] = (int) session()->get('logged_user');
        return $data;
    }

    public function getInvoicesPdf($invoice_id){
        return $this->db->table('invoice')
        ->where('invoice_id', $invoice_id)
        ->join('branches as B','B.branch_id = invoice.branch_id','left')
        ->join('vehicle as V','V.vehicle_id = invoice.vehicle_id','left')
        ->join('client as C','V.client_id = C.client_id','left')
        ->select('invoice.*')
        ->select('B.branch_name ,B.address as company_address,B.city as company_city,B.state as company_state,B.postal_code as company_postal_code,B.contact_1 as company_mobile_no_1,B.contact_2 as company_mobile_no_2,B.gstno as company_gstno')
        ->select('C.gstnumber as client_gst')
        ->get()
        ->getResult();
    }

    public function getInvoiceProductsForPdf($invid)
    {
        
        $result = $this->db->table('invoice_child')
            ->where('invoice_child.invoice_id', $invid)
            ->where('invoice_child.isactive', 1)
            ->join('products', 'products.product_id = invoice_child.product_id')
            ->select('invoice_child.*, products.product_name, products.per, products.ml,products.hsn_sac')
            ->get()
            ->getResult();

        return $result;
    }

    public function getInvoicesWithProducts($logged_user_branch_id)
    {
        // Select required fields from both tables
        $this->select('invoice.*, COUNT(invoice_child.invoice_child_id) AS product_count,vehicle.reg_no,SUM(invoice_child.qty) AS product_qty ');
        $this->join('invoice_child', 'invoice_child.invoice_id = invoice.invoice_id AND invoice_child.isactive = 1', 'left');
        $this->join('vehicle', 'vehicle.vehicle_id = invoice.vehicle_id');
        $this->groupBy('invoice.invoice_id');
        $this->where('invoice.branch_id',$logged_user_branch_id);
        $this->orderBy('invoice.invoice_id','desc');
        // Get the results
        return $this->findAll();
    }
    
}