<?php 
namespace App\Models;
use CodeIgniter\Model;
class PurchaseOrderModel extends Model
{
    protected $table = 'purchase_order';
    protected $primaryKey = 'purchase_order_id';
    protected $allowedFields = ['purchase_order_id','bill_number','return_date','order_number','vendor_id','status','billing_address','billing_city','billing_state','billing_country','billing_postal_code','shipping_address','shipping_city','shipping_state','shipping_country','shipping_postal_code','terms_condition','isactive','order_date','contact_person_name','branch_id','contact_person_mobile','description','subtotal','tax','total'];

    public function getPurchaseOrdersWithProducts($logged_user_branch_id)
    {
        // Select required fields from both tables
        $this->select('purchase_order.*, COUNT(purchase_order_child.purchase_order_id) AS product_count,vendor.vendor_name,SUM(purchase_order_child.qty) AS purchase_qty, SUM(purchase_order_child.received_qty) AS received_qty');
        
        // Join the sales_order_product table based on sales_order_id
        $this->join('purchase_order_child', 'purchase_order_child.purchase_order_id = purchase_order.purchase_order_id');
        $this->join('vendor', 'vendor.vendor_id = purchase_order.vendor_id');
        // Group by sales_order_id to get count of products per sales order
        $this->groupBy('purchase_order.purchase_order_id');
    
        // Add condition to fetch only active sales orders
        $this->where('purchase_order_child.isactive', 1);
        $this ->where('purchase_order.branch_id',$logged_user_branch_id);

        $subquery = "(SELECT COUNT(*) FROM purchase_order_child WHERE purchase_order_child.purchase_order_id = purchase_order.purchase_order_id AND purchase_order_child.is_selected = 1) AS selected_product_count";
        $this->select($subquery, false);
                
        $this->orderBy('purchase_order.purchase_order_id','DESC');
        // Get the results
        return $this->findAll();
    } 
    public function getPurchasePdf($purchase_order_id){
        return $this->db->table('purchase_order')
        ->where('purchase_order_id', $purchase_order_id)
      
        ->join('branches as B','B.branch_id = purchase_order.branch_id','left')
        ->join('vendor as V','V.vendor_id = purchase_order.vendor_id','left')
        ->select('purchase_order.*')
        ->select('V.vendor_name,V.contact_person_mobile1,V.address as vendor_address,V.city as vendor_city,V.state,V.postal_code as vendor_postal,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,B.gstno as company_gstno')
        
        ->get()
        ->getResult();
    }
    public function getPurchaseOrderProductsForPdf($purchase_order_id)
    {
        // Select required fields from the sales_order_product table
        $result = $this->db->table('purchase_order_child')
            ->where('purchase_order_child.purchase_order_id', $purchase_order_id)
            ->join('products', 'products.product_id = purchase_order_child.product_id')
            ->select('purchase_order_child.*, products.product_name')
            ->get()
            ->getResult();
    
        return $result;
    }
    public function getdata($logged_user_branch_id)
    {
        $result = $this->db->table('purchase_order')
      
        ->join('purchase_order_child','purchase_order_child.product_id=purchase_order_child.product_id')
        ->join('products', 'products.product_id = purchase_order_child.product_id')
        ->join('vendor', 'vendor.vendor_id = purchase_order.vendor_id')
        ->select('purchase_order_child.*,vendor.*, products.product_name')
        ->get()
        ->getResult();

    return $result;
    }
}