69 lines
3.7 KiB
PHP
Executable File
69 lines
3.7 KiB
PHP
Executable File
<?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;
|
|
}
|
|
} |