File: /home/jairicem/bunkerltd.com/app/models/admin/Reports_model.php
<?php
defined('BASEPATH') or exit('No direct script access allowed');
class Reports_model extends CI_Model
{
public function __construct()
{
parent::__construct();
}
public function getBestSeller($start_date, $end_date, $warehouse_id = null)
{
$this->db
->select('product_name, product_code')->select_sum('quantity')
->join('sales', 'sales.id = sale_items.sale_id', 'left')
->where('date >=', $start_date)->where('date <=', $end_date)
->group_by('product_name, product_code')->order_by('sum(quantity)', 'desc')->limit(10);
if ($warehouse_id) {
$this->db->where('sale_items.warehouse_id', $warehouse_id);
}
$q = $this->db->get('sale_items');
if ($q->num_rows() > 0) {
foreach (($q->result()) as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
// public function getmonthlyPurchases()
// {
// $myQuery = "SELECT (CASE WHEN date_format( date, '%b' ) Is Null THEN 0 ELSE date_format( date, '%b' ) END) as month, SUM( COALESCE( total, 0 ) ) AS purchases FROM purchases WHERE date >= date_sub( now( ) , INTERVAL 12 MONTH ) GROUP BY date_format( date, '%b' ) ORDER BY date_format( date, '%m' ) ASC";
// $q = $this->db->query($myQuery);
// if ($q->num_rows() > 0) {
// foreach (($q->result()) as $row) {
// $data[] = $row;
// }
// return $data;
// }
// return FALSE;
// }
public function getChartData()
{
$myQuery = "SELECT S.month,
COALESCE(S.sales, 0) as sales,
COALESCE( P.purchases, 0 ) as purchases,
COALESCE(S.tax1, 0) as tax1,
COALESCE(S.tax2, 0) as tax2,
COALESCE( P.ptax, 0 ) as ptax
FROM ( SELECT date_format(date, '%Y-%m') Month,
SUM(total) Sales,
SUM(product_tax) tax1,
SUM(order_tax) tax2
FROM " . $this->db->dbprefix('sales') . "
WHERE date >= date_sub( now( ) , INTERVAL 12 MONTH )
GROUP BY date_format(date, '%Y-%m')) S
LEFT JOIN ( SELECT date_format(date, '%Y-%m') Month,
SUM(product_tax) ptax,
SUM(order_tax) otax,
SUM(total) purchases
FROM " . $this->db->dbprefix('purchases') . "
GROUP BY date_format(date, '%Y-%m')) P
ON S.Month = P.Month
ORDER BY S.Month";
$q = $this->db->query($myQuery);
if ($q->num_rows() > 0) {
foreach (($q->result()) as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
public function getCosting($date, $warehouse_id = null, $year = null, $month = null)
{
$this->db->select('SUM( COALESCE( purchase_unit_cost, 0 ) * quantity ) AS cost, SUM( COALESCE( sale_unit_price, 0 ) * quantity ) AS sales, SUM( COALESCE( purchase_net_unit_cost, 0 ) * quantity ) AS net_cost, SUM( COALESCE( sale_net_unit_price, 0 ) * quantity ) AS net_sales', false);
if ($date) {
$this->db->where('costing.date', $date);
} elseif ($month) {
$this->load->helper('date');
$last_day = days_in_month($month, $year);
$this->db->where('costing.date >=', $year . '-' . $month . '-01 00:00:00');
$this->db->where('costing.date <=', $year . '-' . $month . '-' . $last_day . ' 23:59:59');
}
if ($warehouse_id) {
$this->db->join('sales', 'sales.id=costing.sale_id')
->where('sales.warehouse_id', $warehouse_id);
}
$q = $this->db->get('costing');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getCustomerOpenReturns($customer_id)
{
$this->db->from('returns')->where('customer_id', $customer_id);
return $this->db->count_all_results();
}
public function getCustomerQuotes($customer_id)
{
$this->db->from('quotes')->where('customer_id', $customer_id);
return $this->db->count_all_results();
}
public function getCustomerReturns($customer_id)
{
return $this->getCustomerSaleReturns($customer_id) + $this->getCustomerOpenReturns($customer_id);
}
public function getCustomerSaleReturns($customer_id)
{
$this->db->from('sales')->where('customer_id', $customer_id)->where('sale_status', 'returned');
return $this->db->count_all_results();
}
public function getCustomerSales($customer_id)
{
$this->db->from('sales')->where('customer_id', $customer_id);
return $this->db->count_all_results();
}
public function getDailyPurchases($year, $month, $warehouse_id = null)
{
$myQuery = "SELECT DATE_FORMAT( date, '%e' ) AS date, SUM( COALESCE( product_tax, 0 ) ) AS tax1, SUM( COALESCE( order_tax, 0 ) ) AS tax2, SUM( COALESCE( grand_total, 0 ) ) AS total, SUM( COALESCE( total_discount, 0 ) ) AS discount, SUM( COALESCE( shipping, 0 ) ) AS shipping
FROM " . $this->db->dbprefix('purchases') . ' WHERE ';
if ($warehouse_id) {
$myQuery .= " warehouse_id = {$warehouse_id} AND ";
}
$myQuery .= " DATE_FORMAT( date, '%Y-%m' ) = '{$year}-{$month}'
GROUP BY DATE_FORMAT( date, '%e' )";
$q = $this->db->query($myQuery, false);
if ($q->num_rows() > 0) {
foreach (($q->result()) as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
public function getDailySales($year, $month, $warehouse_id = null)
{
$myQuery = "SELECT DATE_FORMAT( date, '%e' ) AS date, SUM( COALESCE( product_tax, 0 ) ) AS tax1, SUM( COALESCE( order_tax, 0 ) ) AS tax2, SUM( COALESCE( grand_total, 0 ) ) AS total, SUM( COALESCE( total_discount, 0 ) ) AS discount, SUM( COALESCE( shipping, 0 ) ) AS shipping
FROM " . $this->db->dbprefix('sales') . ' WHERE ';
if ($warehouse_id) {
$myQuery .= " warehouse_id = {$warehouse_id} AND ";
}
$myQuery .= " DATE_FORMAT( date, '%Y-%m' ) = '{$year}-{$month}'
GROUP BY DATE_FORMAT( date, '%e' )";
$q = $this->db->query($myQuery, false);
if ($q->num_rows() > 0) {
foreach (($q->result()) as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
public function getExpenseCategories()
{
$q = $this->db->get('expense_categories');
if ($q->num_rows() > 0) {
foreach (($q->result()) as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
public function getExpenses($date, $warehouse_id = null, $year = null, $month = null)
{
$sdate = $date . ' 00:00:00';
$edate = $date . ' 23:59:59';
$this->db->select('SUM( COALESCE( amount, 0 ) ) AS total', false);
if ($date) {
$this->db->where('date >=', $sdate)->where('date <=', $edate);
} elseif ($month) {
$this->load->helper('date');
$last_day = days_in_month($month, $year);
$this->db->where('date >=', $year . '-' . $month . '-01 00:00:00');
$this->db->where('date <=', $year . '-' . $month . '-' . $last_day . ' 23:59:59');
}
if ($warehouse_id) {
$this->db->where('warehouse_id', $warehouse_id);
}
$q = $this->db->get('expenses');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getMonthlyPurchases($year, $warehouse_id = null)
{
$myQuery = "SELECT DATE_FORMAT( date, '%c' ) AS date, SUM( COALESCE( product_tax, 0 ) ) AS tax1, SUM( COALESCE( order_tax, 0 ) ) AS tax2, SUM( COALESCE( grand_total, 0 ) ) AS total, SUM( COALESCE( total_discount, 0 ) ) AS discount, SUM( COALESCE( shipping, 0 ) ) AS shipping
FROM " . $this->db->dbprefix('purchases') . ' WHERE ';
if ($warehouse_id) {
$myQuery .= " warehouse_id = {$warehouse_id} AND ";
}
$myQuery .= " DATE_FORMAT( date, '%Y' ) = '{$year}'
GROUP BY date_format( date, '%c' ) ORDER BY date_format( date, '%c' ) ASC";
$q = $this->db->query($myQuery, false);
if ($q->num_rows() > 0) {
foreach (($q->result()) as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
public function getMonthlySales($year, $warehouse_id = null)
{
$myQuery = "SELECT DATE_FORMAT( date, '%c' ) AS date, SUM( COALESCE( product_tax, 0 ) ) AS tax1, SUM( COALESCE( order_tax, 0 ) ) AS tax2, SUM( COALESCE( grand_total, 0 ) ) AS total, SUM( COALESCE( total_discount, 0 ) ) AS discount, SUM( COALESCE( shipping, 0 ) ) AS shipping
FROM " . $this->db->dbprefix('sales') . ' WHERE ';
if ($warehouse_id) {
$myQuery .= " warehouse_id = {$warehouse_id} AND ";
}
$myQuery .= " DATE_FORMAT( date, '%Y' ) = '{$year}'
GROUP BY date_format( date, '%c' ) ORDER BY date_format( date, '%c' ) ASC";
$q = $this->db->query($myQuery, false);
if ($q->num_rows() > 0) {
foreach (($q->result()) as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
public function getOrderDiscount($date, $warehouse_id = null, $year = null, $month = null)
{
$sdate = $date . ' 00:00:00';
$edate = $date . ' 23:59:59';
$this->db->select('SUM( COALESCE( order_discount, 0 ) ) AS order_discount', false);
if ($date) {
$this->db->where('date >=', $sdate)->where('date <=', $edate);
} elseif ($month) {
$this->load->helper('date');
$last_day = days_in_month($month, $year);
$this->db->where('date >=', $year . '-' . $month . '-01 00:00:00');
$this->db->where('date <=', $year . '-' . $month . '-' . $last_day . ' 23:59:59');
}
if ($warehouse_id) {
$this->db->where('warehouse_id', $warehouse_id);
}
$q = $this->db->get('sales');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getPOSSetting()
{
$q = $this->db->get('pos_settings');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getProductNames($term, $limit = 5)
{
$this->db->select('id, code, name')
->like('name', $term, 'both')->or_like('code', $term, 'both');
$this->db->limit($limit);
$q = $this->db->get('products');
if ($q->num_rows() > 0) {
foreach (($q->result()) as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
public function getPurchasesTax($start_date = null, $end_date = null)
{
$this->db->select_sum('igst')->select_sum('cgst')->select_sum('sgst')
->select_sum('product_tax')->select_sum('order_tax')
->select_sum('grand_total')->select_sum('paid');
if ($start_date) {
$this->db->where('date >=', $start_date);
}
if ($end_date) {
$this->db->where('date <=', $end_date);
}
$q = $this->db->get('purchases');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getPurchasesTotals($supplier_id)
{
$this->db->select('SUM(COALESCE(grand_total, 0)) as total_amount, SUM(COALESCE(paid, 0)) as paid', false)
->where('supplier_id', $supplier_id);
$q = $this->db->get('purchases');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getReturns($date, $warehouse_id = null, $year = null, $month = null)
{
$sdate = $date . ' 00:00:00';
$edate = $date . ' 23:59:59';
$this->db->select('SUM( COALESCE( grand_total, 0 ) ) AS total', false)
->where('sale_status', 'returned');
if ($date) {
$this->db->where('date >=', $sdate)->where('date <=', $edate);
} elseif ($month) {
$this->load->helper('date');
$last_day = days_in_month($month, $year);
$this->db->where('date >=', $year . '-' . $month . '-01 00:00:00');
$this->db->where('date <=', $year . '-' . $month . '-' . $last_day . ' 23:59:59');
}
if ($warehouse_id) {
$this->db->where('warehouse_id', $warehouse_id);
}
$q = $this->db->get('sales');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getSalesTax($start_date = null, $end_date = null)
{
$this->db->select_sum('igst')->select_sum('cgst')->select_sum('sgst')
->select_sum('product_tax')->select_sum('order_tax')
->select_sum('grand_total')->select_sum('paid');
if ($start_date) {
$this->db->where('date >=', $start_date);
}
if ($end_date) {
$this->db->where('date <=', $end_date);
}
$q = $this->db->get('sales');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getSalesTotals($customer_id)
{
$this->db->select('SUM(COALESCE(grand_total, 0)) as total_amount, SUM(COALESCE(paid, 0)) as paid', false)
->where('customer_id', $customer_id);
$q = $this->db->get('sales');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getStaff()
{
if ($this->Admin) {
$this->db->where('group_id !=', 1);
}
$this->db->where('group_id !=', 3)->where('group_id !=', 4);
$q = $this->db->get('users');
if ($q->num_rows() > 0) {
foreach (($q->result()) as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
public function getStaffDailyPurchases($user_id, $year, $month, $warehouse_id = null)
{
$myQuery = "SELECT DATE_FORMAT( date, '%e' ) AS date, SUM( COALESCE( product_tax, 0 ) ) AS tax1, SUM( COALESCE( order_tax, 0 ) ) AS tax2, SUM( COALESCE( grand_total, 0 ) ) AS total, SUM( COALESCE( total_discount, 0 ) ) AS discount, SUM( COALESCE( shipping, 0 ) ) AS shipping
FROM " . $this->db->dbprefix('purchases') . ' WHERE ';
if ($warehouse_id) {
$myQuery .= " warehouse_id = {$warehouse_id} AND ";
}
$myQuery .= " created_by = {$user_id} AND DATE_FORMAT( date, '%Y-%m' ) = '{$year}-{$month}'
GROUP BY DATE_FORMAT( date, '%e' )";
$q = $this->db->query($myQuery, false);
if ($q->num_rows() > 0) {
foreach (($q->result()) as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
public function getStaffDailySales($user_id, $year, $month, $warehouse_id = null)
{
$myQuery = "SELECT DATE_FORMAT( date, '%e' ) AS date, SUM( COALESCE( product_tax, 0 ) ) AS tax1, SUM( COALESCE( order_tax, 0 ) ) AS tax2, SUM( COALESCE( grand_total, 0 ) ) AS total, SUM( COALESCE( total_discount, 0 ) ) AS discount, SUM( COALESCE( shipping, 0 ) ) AS shipping
FROM " . $this->db->dbprefix('sales') . ' WHERE ';
if ($warehouse_id) {
$myQuery .= " warehouse_id = {$warehouse_id} AND ";
}
$myQuery .= " created_by = {$user_id} AND DATE_FORMAT( date, '%Y-%m' ) = '{$year}-{$month}'
GROUP BY DATE_FORMAT( date, '%e' )";
$q = $this->db->query($myQuery, false);
if ($q->num_rows() > 0) {
foreach (($q->result()) as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
public function getStaffMonthlyPurchases($user_id, $year, $warehouse_id = null)
{
$myQuery = "SELECT DATE_FORMAT( date, '%c' ) AS date, SUM( COALESCE( product_tax, 0 ) ) AS tax1, SUM( COALESCE( order_tax, 0 ) ) AS tax2, SUM( COALESCE( grand_total, 0 ) ) AS total, SUM( COALESCE( total_discount, 0 ) ) AS discount, SUM( COALESCE( shipping, 0 ) ) AS shipping
FROM " . $this->db->dbprefix('purchases') . ' WHERE ';
if ($warehouse_id) {
$myQuery .= " warehouse_id = {$warehouse_id} AND ";
}
$myQuery .= " created_by = {$user_id} AND DATE_FORMAT( date, '%Y' ) = '{$year}'
GROUP BY date_format( date, '%c' ) ORDER BY date_format( date, '%c' ) ASC";
$q = $this->db->query($myQuery, false);
if ($q->num_rows() > 0) {
foreach (($q->result()) as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
public function getStaffMonthlySales($user_id, $year, $warehouse_id = null)
{
$myQuery = "SELECT DATE_FORMAT( date, '%c' ) AS date, SUM( COALESCE( product_tax, 0 ) ) AS tax1, SUM( COALESCE( order_tax, 0 ) ) AS tax2, SUM( COALESCE( grand_total, 0 ) ) AS total, SUM( COALESCE( total_discount, 0 ) ) AS discount, SUM( COALESCE( shipping, 0 ) ) AS shipping
FROM " . $this->db->dbprefix('sales') . ' WHERE ';
if ($warehouse_id) {
$myQuery .= " warehouse_id = {$warehouse_id} AND ";
}
$myQuery .= " created_by = {$user_id} AND DATE_FORMAT( date, '%Y' ) = '{$year}'
GROUP BY date_format( date, '%c' ) ORDER BY date_format( date, '%c' ) ASC";
$q = $this->db->query($myQuery, false);
if ($q->num_rows() > 0) {
foreach (($q->result()) as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
public function getStaffPurchases($user_id)
{
$this->db->select('count(id) as total, SUM(COALESCE(grand_total, 0)) as total_amount, SUM(COALESCE(paid, 0)) as paid', false)
->where('created_by', $user_id);
$q = $this->db->get('purchases');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getStaffSales($user_id)
{
$this->db->select('count(id) as total, SUM(COALESCE(grand_total, 0)) as total_amount, SUM(COALESCE(paid, 0)) as paid', false)
->where('created_by', $user_id);
$q = $this->db->get('sales');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getStockValue()
{
$q = $this->db->query('SELECT SUM(by_price) as stock_by_price, SUM(by_cost) as stock_by_cost FROM ( Select COALESCE(sum(' . $this->db->dbprefix('warehouses_products') . '.quantity), 0)*price as by_price, COALESCE(sum(' . $this->db->dbprefix('warehouses_products') . '.quantity), 0)*cost as by_cost FROM ' . $this->db->dbprefix('products') . ' JOIN ' . $this->db->dbprefix('warehouses_products') . ' ON ' . $this->db->dbprefix('warehouses_products') . '.product_id=' . $this->db->dbprefix('products') . '.id GROUP BY ' . $this->db->dbprefix('products') . '.id ) a');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getSupplierPurchases($supplier_id)
{
$this->db->from('purchases')->where('supplier_id', $supplier_id);
return $this->db->count_all_results();
}
public function getTotalExpenses($start, $end, $warehouse_id = null)
{
$this->db->select('count(id) as total, sum(COALESCE(amount, 0)) as total_amount', false)
->where('date BETWEEN ' . $start . ' and ' . $end);
if ($warehouse_id) {
$this->db->where('warehouse_id', $warehouse_id);
}
$q = $this->db->get('expenses');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getTotalPaidAmount($start, $end)
{
$this->db->select('count(id) as total, SUM(COALESCE(amount, 0)) as total_amount', false)
->where('type', 'sent')
->where('date BETWEEN ' . $start . ' and ' . $end);
$q = $this->db->get('payments');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getTotalPurchases($start, $end, $warehouse_id = null)
{
$this->db->select('count(id) as total, sum(COALESCE(grand_total, 0)) as total_amount, SUM(COALESCE(paid, 0)) as paid, SUM(COALESCE(total_tax, 0)) as tax', false)
->where('status !=', 'pending')
->where('date BETWEEN ' . $start . ' and ' . $end);
if ($warehouse_id) {
$this->db->where('warehouse_id', $warehouse_id);
}
$q = $this->db->get('purchases');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getTotalReceivedAmount($start, $end)
{
$this->db->select('count(id) as total, SUM(COALESCE(amount, 0)) as total_amount', false)
->where('type', 'received')
->where('date BETWEEN ' . $start . ' and ' . $end);
$q = $this->db->get('payments');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getTotalReceivedCashAmount($start, $end)
{
$this->db->select('count(id) as total, SUM(COALESCE(amount, 0)) as total_amount', false)
->where('type', 'received')->where('paid_by', 'cash')
->where('date BETWEEN ' . $start . ' and ' . $end);
$q = $this->db->get('payments');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getTotalReceivedCCAmount($start, $end)
{
$this->db->select('count(id) as total, SUM(COALESCE(amount, 0)) as total_amount', false)
->where('type', 'received')->where('paid_by', 'CC')
->where('date BETWEEN ' . $start . ' and ' . $end);
$q = $this->db->get('payments');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getTotalReceivedChequeAmount($start, $end)
{
$this->db->select('count(id) as total, SUM(COALESCE(amount, 0)) as total_amount', false)
->where('type', 'received')->where('paid_by', 'Cheque')
->where('date BETWEEN ' . $start . ' and ' . $end);
$q = $this->db->get('payments');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getTotalReceivedPPPAmount($start, $end)
{
$this->db->select('count(id) as total, SUM(COALESCE(amount, 0)) as total_amount', false)
->where('type', 'received')->where('paid_by', 'ppp')
->where('date BETWEEN ' . $start . ' and ' . $end);
$q = $this->db->get('payments');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getTotalReceivedStripeAmount($start, $end)
{
$this->db->select('count(id) as total, SUM(COALESCE(amount, 0)) as total_amount', false)
->where('type', 'received')->where('paid_by', 'stripe')
->where('date BETWEEN ' . $start . ' and ' . $end);
$q = $this->db->get('payments');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getTotalReturnedAmount($start, $end)
{
$this->db->select('count(id) as total, SUM(COALESCE(amount, 0)) as total_amount', false)
->where('type', 'returned')
->where('date BETWEEN ' . $start . ' and ' . $end);
$q = $this->db->get('payments');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getTotalReturnSales($start, $end, $warehouse_id = null)
{
$this->db->select('count(id) as total, sum(COALESCE(grand_total, 0)) as total_amount, SUM(COALESCE(paid, 0)) as paid, SUM(COALESCE(total_tax, 0)) as tax', false)
->where('date BETWEEN ' . $start . ' and ' . $end);
if ($warehouse_id) {
$this->db->where('warehouse_id', $warehouse_id);
}
$q = $this->db->get('returns');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getTotalSales($start, $end, $warehouse_id = null)
{
$this->db->select('count(id) as total, sum(COALESCE(grand_total, 0)) as total_amount, SUM(COALESCE(paid, 0)) as paid, SUM(COALESCE(total_tax, 0)) as tax', false)
->where('sale_status !=', 'pending')
->where('date BETWEEN ' . $start . ' and ' . $end);
if ($warehouse_id) {
$this->db->where('warehouse_id', $warehouse_id);
}
$q = $this->db->get('sales');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getWarehouseStockValue($id)
{
$q = $this->db->query('SELECT SUM(by_price) as stock_by_price, SUM(by_cost) as stock_by_cost FROM ( Select sum(COALESCE(' . $this->db->dbprefix('warehouses_products') . '.quantity, 0))*price as by_price, sum(COALESCE(' . $this->db->dbprefix('warehouses_products') . '.quantity, 0))*cost as by_cost FROM ' . $this->db->dbprefix('products') . ' JOIN ' . $this->db->dbprefix('warehouses_products') . ' ON ' . $this->db->dbprefix('warehouses_products') . '.product_id=' . $this->db->dbprefix('products') . '.id WHERE ' . $this->db->dbprefix('warehouses_products') . '.warehouse_id = ? GROUP BY ' . $this->db->dbprefix('products') . '.id ) a', [$id]);
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
public function getWarehouseTotals($warehouse_id = null)
{
$this->db->select('sum(quantity) as total_quantity, count(id) as total_items', false);
$this->db->where('quantity !=', 0);
if ($warehouse_id) {
$this->db->where('warehouse_id', $warehouse_id);
}
$q = $this->db->get('warehouses_products');
if ($q->num_rows() > 0) {
return $q->row();
}
return false;
}
}