���� JFIF �� � ( %"1"%)+...383,7(-.-
![]() Server : Apache/2.4.6 (CentOS) OpenSSL/1.0.2k-fips PHP/7.4.20 System : Linux st2.domain.com 3.10.0-1127.10.1.el7.x86_64 #1 SMP Wed Jun 3 14:28:03 UTC 2020 x86_64 User : apache ( 48) PHP Version : 7.4.20 Disable Function : NONE Directory : /var/www/html/adimi/application/modules/dashboard/models/ |
<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); class reports extends CI_Model { public function __construct() { parent::__construct(); $this->load->database(); } //Count stock report public function count_stock_report() { $this->db->select("a.product_name,a.product_id,a.price,a.product_model,sum(b.quantity) as 'totalSalesQnty',(select sum(product_purchase_details.quantity) from product_purchase_details where product_id= `a`.`product_id`) as 'totalBuyQnty'"); $this->db->from('product_information a'); $this->db->join('invoice_details b', 'b.product_id = a.product_id'); $this->db->where(array('a.status' => 1, 'b.status' => 1)); $this->db->group_by('a.product_id'); $query = $this->db->get(); return $query->num_rows(); } //Stock report public function stock_report($limit, $page) { $this->db->select("a.product_name,a.product_id,a.price,a.product_model,sum(b.quantity) as 'totalSalesQnty',(select sum(product_purchase_details.quantity) from product_purchase_details where product_id= `a`.`product_id`) as 'totalBuyQnty'"); $this->db->from('product_information a'); $this->db->join('invoice_details b', 'b.product_id = a.product_id'); $this->db->where(array('a.status' => 1, 'b.status' => 1)); $this->db->group_by('a.product_id'); $this->db->order_by('a.product_id', 'desc'); $this->db->limit($limit, $page); $query = $this->db->get(); return $query->result_array(); } //Out of stock public function out_of_stock() { $this->db->select(" a.product_name, a.unit, a.product_id, a.price, a.supplier_price, a.product_model, c.category_name, sum(d.quantity) as totalSalesQnty, sum(b.quantity) as totalPurchaseQnty, e.purchase_date as purchase_date, e.purchase_id, (sum(b.quantity) - sum(d.quantity)) as stock "); $this->db->from('product_information a'); $this->db->join('product_category c', 'c.category_id = a.category_id', 'left'); $this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left'); $this->db->join('invoice_details d', 'd.product_id = a.product_id', 'left'); $this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id', 'left'); $this->db->group_by('a.product_id'); $this->db->having('stock < 10', null, false); $this->db->having('totalPurchaseQnty < 10', null, false); $this->db->order_by('a.product_name', 'asc'); $query = $this->db->get(); return $query->result_array(); } //Out of stock count public function out_of_stock_count() { $this->db->query("SET SQL_BIG_SELECTS=1"); // for avoid max_join_size. $this->db->select(" a.product_name, a.unit, a.product_id, a.price, a.supplier_price, a.product_model, c.category_name, sum(d.quantity) as totalSalesQnty, sum(b.quantity) as totalPurchaseQnty, e.purchase_date as purchase_date, e.purchase_id, (sum(b.quantity) - sum(d.quantity)) as stock "); $this->db->from('product_information a'); $this->db->join('product_category c', 'c.category_id = a.category_id', 'left'); $this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left'); $this->db->join('invoice_details d', 'd.product_id = a.product_id', 'left'); $this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id', 'left'); $this->db->group_by('a.product_id'); $this->db->having('stock < 10', null, false); $this->db->having('totalPurchaseQnty < 10', null, false); $this->db->order_by('a.product_name', 'asc'); $query = $this->db->get(); return $query->num_rows(); } //Stock report single item public function stock_report_single_item($product_id) { $this->db->select("a.product_name,a.price,a.product_model,sum(b.quantity) as 'totalSalesQnty',sum(c.quantity) as 'totalBuyQnty'"); $this->db->from('product_information a'); $this->db->join('invoice_details b', 'b.product_id = a.product_id'); $this->db->join('product_purchase_details c', 'c.product_id = a.product_id'); $this->db->where(array('a.product_id' => $product_id, 'a.status' => 1, 'b.status' => 1)); $this->db->group_by('a.product_id'); $this->db->order_by('a.product_id', 'desc'); $query = $this->db->get(); return $query->result_array(); } //Stock Report by date public function stock_report_bydate($product_id, $date, $limit, $page) { $this->db->select(" a.product_name, a.unit, a.product_id, a.price, a.supplier_price, a.product_model, c.category_name, sum(b.quantity) as totalPurchaseQnty, e.purchase_date as purchase_date, e.purchase_id, f.unit_name "); $this->db->from('product_information a'); $this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left'); $this->db->join('product_category c', 'c.category_id = a.category_id'); $this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id'); $this->db->join('unit f', 'f.unit_id = a.unit', 'left'); $this->db->join('variant g', 'g.variant_id = b.variant_id', 'left'); $this->db->join('store_set h', 'h.store_id = b.store_id'); $this->db->where('b.store_id !=', null); $this->db->group_by('a.product_id'); $this->db->order_by('a.product_name', 'asc'); if (empty($product_id)) { $this->db->where(array('a.status' => 1)); } else { //Single product information $this->db->where(array('a.status' => 1, 'e.purchase_date <= ' => $date, 'a.product_id' => $product_id)); } $this->db->limit($limit, $page); $query = $this->db->get(); return $query->result_array(); } //Counter of unique product histor which has been affected public function product_counter($product_id, $date) { $this->db->select(" a.product_name, a.unit, a.product_id, a.price, a.supplier_price, a.product_model, c.category_name, sum(b.quantity) as totalPurchaseQnty, e.purchase_date as purchase_date, e.purchase_id, f.unit_name "); $this->db->from('product_information a'); $this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left'); $this->db->join('product_category c', 'c.category_id = a.category_id'); $this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id'); $this->db->join('unit f', 'f.unit_id = a.unit', 'left'); $this->db->join('variant g', 'g.variant_id = b.variant_id', 'left'); $this->db->join('store_set h', 'h.store_id = b.store_id'); $this->db->where('b.store_id !=', null); $this->db->group_by('a.product_id'); $this->db->order_by('a.product_name', 'asc'); if (empty($product_id)) { $this->db->where(array('a.status' => 1)); } else { //Single product information $this->db->where(array('a.status' => 1, 'e.purchase_date <= ' => $date, 'a.product_id' => $product_id)); } $query = $this->db->get(); return $query->num_rows(); } //Stock report product by date public function stock_report_product_bydate($product_id, $supplier_id, $from_date, $to_date, $per_page, $page) { $this->db->select(" a.product_name, a.unit, a.product_id, a.price, a.supplier_price, a.product_model, c.category_name, sum(b.quantity) as 'totalPurchaseQnty', e.purchase_date as date "); $this->db->from('product_information a'); $this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left'); $this->db->join('product_category c', 'c.category_id = a.category_id'); $this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id'); $this->db->join('unit f', 'f.unit_id = a.unit', 'left'); $this->db->join('variant g', 'g.variant_id = b.variant_id', 'left'); $this->db->join('store_set h', 'h.store_id = b.store_id'); $this->db->where('b.store_id !=', null); $this->db->group_by('a.product_id'); $this->db->order_by('a.product_name', 'asc'); $this->db->limit($per_page, $page); if (empty($supplier_id)) { $this->db->where(array('a.status' => 1)); } else { $this->db->where( array( 'a.status' => 1, 'a.supplier_id' => $supplier_id, 'a.product_id' => $product_id ) ); $this->db->where("STR_TO_DATE(e.purchase_date, '%m-%d-%Y') BETWEEN DATE('".$from_date."') AND DATE('".$to_date."')"); } $query = $this->db->get(); return $query->result_array(); } //Stock report product by date count public function stock_report_product_bydate_count($product_id, $supplier_id, $from_date, $to_date) { $this->db->select(" a.product_name, a.unit, a.product_id, a.price, a.supplier_price, a.product_model, c.category_name, sum(b.quantity) as 'totalPurchaseQnty', e.purchase_date as date "); $this->db->from('product_information a'); $this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left'); $this->db->join('product_category c', 'c.category_id = a.category_id'); $this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id'); $this->db->join('unit f', 'f.unit_id = a.unit', 'left'); $this->db->join('variant g', 'g.variant_id = b.variant_id', 'left'); $this->db->join('store_set h', 'h.store_id = b.store_id'); $this->db->where('b.store_id !=', null); $this->db->group_by('a.product_id'); $this->db->order_by('a.product_name', 'asc'); if (empty($supplier_id)) { $this->db->where(array('a.status' => 1)); } else { $this->db->where( array( 'a.status' => 1, 'a.supplier_id' => $supplier_id, 'a.product_id' => $product_id ) ); $this->db->where("STR_TO_DATE(e.purchase_date, '%m-%d-%Y') BETWEEN DATE('".$from_date."') AND DATE('".$to_date."')"); } $query = $this->db->get(); return $query->num_rows(); } //Stock report supplier by date public function stock_report_supplier_bydate($product_id, $supplier_id, $date, $perpage, $page) { $this->db->select(" a.product_name, a.unit, a.product_id, a.price, a.supplier_price, a.product_model, c.category_name, sum(b.quantity) as 'totalPrhcsCtn', e.purchase_date as purchase_date, "); $this->db->from('product_information a'); $this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left'); $this->db->join('product_category c', 'c.category_id = a.category_id'); $this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id'); $this->db->join('unit f', 'f.unit_id = a.unit', 'left'); $this->db->join('variant g', 'g.variant_id = b.variant_id', 'left'); $this->db->join('store_set h', 'h.store_id = b.store_id'); $this->db->where('b.store_id !=', null); $this->db->group_by('a.product_id'); $this->db->order_by('a.product_name', 'asc'); if (empty($supplier_id)) { $this->db->where(array('a.status' => 1)); } else { $this->db->where('a.status', 1); $this->db->where('e.purchase_date <=', $date); $this->db->where('a.supplier_id', $supplier_id); } $this->db->limit($perpage, $page); $query = $this->db->get(); return $query->result_array(); } //Counter of unique product histor which has been affected public function product_counter_by_supplier($supplier_id, $date) { $this->db->select(" a.product_name, a.unit, a.product_id, a.price, a.supplier_price, a.product_model, c.category_name, sum(b.quantity) as 'totalPrhcsCtn', e.purchase_date as purchase_date, "); $this->db->from('product_information a'); $this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left'); $this->db->join('product_category c', 'c.category_id = a.category_id'); $this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id'); $this->db->join('unit f', 'f.unit_id = a.unit', 'left'); $this->db->join('variant g', 'g.variant_id = b.variant_id', 'left'); $this->db->join('store_set h', 'h.store_id = b.store_id'); $this->db->where('b.store_id !=', null); $this->db->group_by('a.product_id'); $this->db->order_by('a.product_name', 'asc'); if (empty($supplier_id)) { $this->db->where(array('a.status' => 1)); } else { $this->db->where('a.status', 1); $this->db->where('e.purchase_date <=', $date); $this->db->where('a.supplier_id', $supplier_id); } $query = $this->db->get(); return $query->num_rows(); } //get stock report store wise public function stock_report_by_store($from_date, $to_date, $store_id, $perpage, $page, $product_id = false) { $this->db->select(" a.product_name, a.unit, a.product_id, a.price, a.supplier_price, a.product_model, c.category_name, b.date_time as purchase_date, g.variant_name, g.variant_id, ga.variant_id as variant_color, ga.variant_name as variant_color_name, h.store_name, h.store_id, "); $wh = "date_format( str_to_date( date_time, '%m-%d-%Y' ) , '%Y-%m-%d' ) Between '" . $from_date . "' AND'" . $to_date . "'"; $this->db->from('product_information a'); $this->db->join('transfer b', 'b.product_id = a.product_id', 'left'); $this->db->join('product_category c', 'c.category_id = a.category_id'); $this->db->join('unit f', 'f.unit_id = a.unit', 'left'); $this->db->join('variant g', 'g.variant_id = b.variant_id', 'left'); $this->db->join('variant ga', 'ga.variant_id = b.variant_color', 'left'); $this->db->join('store_set h', 'h.store_id = b.store_id'); $this->db->group_by('a.product_id'); $this->db->group_by('g.variant_id'); $this->db->group_by('h.store_id'); $this->db->order_by('a.product_name', 'asc'); $this->db->where('a.status', 1); $this->db->where('b.store_id', $store_id); if(!empty($product_id)){ $this->db->where('a.product_id', $product_id); } $this->db->where($wh); $this->db->limit($perpage, $page); $query = $this->db->get(); return $query->result_array(); } //Stock report variant by date public function stock_report_variant_bydate($from_date, $to_date, $store_id, $perpage, $page) { $this->db->select(" a.product_name, a.unit, a.product_id, a.price, a.supplier_price, a.product_model, c.category_name, sum(b.quantity) as totalPrhcsCtn, b.date_time as purchase_date, g.variant_name, g.variant_id, h.store_name, h.store_id, "); $this->db->from('product_information a'); $this->db->join('transfer b', 'b.product_id = a.product_id', 'left'); $this->db->join('product_category c', 'c.category_id = a.category_id'); $this->db->join('unit f', 'f.unit_id = a.unit', 'left'); $this->db->join('variant g', 'g.variant_id = b.variant_id', 'left'); $this->db->join('store_set h', 'h.store_id = b.store_id'); $this->db->where('b.store_id !=', null); $this->db->where('b.t_store_id =', null); $this->db->group_by('a.product_id'); $this->db->group_by('g.variant_id'); $this->db->group_by('h.store_id'); $this->db->order_by('a.product_name', 'asc'); if (empty($from_date)) { $this->db->where('a.status', 1); } else { $this->db->where('a.status', 1); $this->db->where('b.date_time >=', $from_date); $this->db->where('b.date_time <=', $to_date); $this->db->where('b.store_id', $store_id); } $this->db->limit($perpage, $page); $query = $this->db->get(); return $query->result_array(); } //Counter of unique product histor which has been affected public function stock_report_variant_bydate_count($from_date, $to_date, $store_id, $product_id = false) { $this->db->select(" a.product_name, a.unit, a.product_id, a.price, a.supplier_price, a.product_model, c.category_name, b.date_time as purchase_date, g.variant_name, g.variant_id, h.store_name, h.store_id, "); $wh = "date_format( str_to_date( date_time, '%m-%d-%Y' ) , '%Y-%m-%d' ) Between '" . $from_date . "' AND'" . $to_date . "'"; $this->db->from('product_information a'); $this->db->join('transfer b', 'b.product_id = a.product_id', 'left'); $this->db->join('product_category c', 'c.category_id = a.category_id'); $this->db->join('unit f', 'f.unit_id = a.unit', 'left'); $this->db->join('variant g', 'g.variant_id = b.variant_id', 'left'); $this->db->join('store_set h', 'h.store_id = b.store_id'); $this->db->group_by('a.product_id'); $this->db->group_by('g.variant_id'); $this->db->group_by('h.store_id'); $this->db->order_by('a.product_name', 'asc'); $this->db->where('a.status', 1); $this->db->where('b.store_id', $store_id); if(!empty($product_id)){ $this->db->where('a.product_id', $product_id); } $this->db->where($wh); $query = $this->db->get(); return $query->num_rows(); } // ====================STORE WISE STOCK REPORT ======================= public function store_wise_product($per_page = NULL, $page = NULL) { $this->db->select(' a.store_name, a.store_id, b.*, SUM(b.quantity) as quantity, c.product_name, c.product_id, c.product_model, d.variant_name, d.variant_id '); $this->db->from('store_set a'); $this->db->join('transfer b', 'a.store_id= b.store_id', 'left'); $this->db->join('product_information c', 'c.product_id = b.product_id'); $this->db->join('variant d', 'd.variant_id = b.variant_id'); $this->db->group_by('b.product_id'); $this->db->group_by('b.variant_id'); $this->db->group_by('a.store_id'); $this->db->order_by('a.store_name', 'asc'); $this->db->limit($per_page, $page); $query = $this->db->get(); $result = $query->result(); $test = []; foreach ($result as $product) { $sql = 'select sum(a.quantity) as sell from order_details a where store_id="' . $product->store_id . '" and product_id="' . $product->product_id . '" and variant_id="' . $product->variant_id . '"'; $product_sell = $this->db->query($sql)->row(); $test[] = array_merge((array)$product, (array)$product_sell); } if ($test != NULl && !empty($test)) { return $test; } return false; } // ====================INDIVIDUAL STORE WISE STOCK REPORT ======================= public function individual_store_wise_product($per_page = NULL, $page = NULL) { $store_id = $this->session->userdata('store_id'); $this->db->select(' a.store_name, a.store_id, b.*, SUM(b.quantity) as quantity, c.product_name, c.product_id, c.product_model, d.variant_name, d.variant_id '); $this->db->from('store_set a'); $this->db->join('transfer b', 'a.store_id= b.store_id', 'left'); $this->db->join('product_information c', 'c.product_id = b.product_id'); $this->db->join('variant d', 'd.variant_id = b.variant_id'); $this->db->where('b.store_id=', $store_id); $this->db->group_by('b.product_id'); $this->db->group_by('b.variant_id'); $this->db->group_by('a.store_id'); $this->db->order_by('a.store_name', 'asc'); $this->db->limit($per_page, $page); $query = $this->db->get(); $result = $query->result(); $test = []; foreach ($result as $product) { $sql = 'select sum(a.quantity) as sell from invoice_details a where store_id="' . $store_id . '" and product_id="' . $product->product_id . '" and variant_id="' . $product->variant_id . '"'; $product_sell = $this->db->query($sql)->row(); $test[] = array_merge((array)$product, (array)$product_sell); } if ($test != NULl && !empty($test)) { return $test; } return false; } //=============sales report store wise================= public function retrieve_sales_report_store_wise($store_id, $start_date = null, $end_date = null) { $dateRange = "STR_TO_DATE(a.date, '%m-%d-%Y') BETWEEN DATE('$start_date') AND DATE('$end_date')"; $this->db->select("a.*,b.store_name"); $this->db->from('invoice a'); $this->db->join('store_set b', 'a.store_id = b.store_id'); $this->db->where('a.store_id', $store_id); if ($dateRange) { $this->db->where($dateRange, NULL, FALSE); } $this->db->order_by('a.date', 'desc'); $this->db->limit('500'); $query = $this->db->get(); return $query->result_array(); } //Retrieve todays_sales_report public function todays_total_sales_report() { $today = date('m-d-Y'); $this->db->select('date, invoice, SUM(total_amount) as total_sale'); $this->db->from('invoice'); $this->db->where('date', $today); $query = $this->db->get(); return $query->result_array(); } //Retrieve todays_total_purchase_report public function todays_total_purchase_report() { $today = date('m-d-Y'); $this->db->select('purchase_date, invoice_no, SUM(grand_total_amount) as total_purchase'); $this->db->from('product_purchase'); $this->db->where('purchase_date', $today); $query = $this->db->get(); return $query->result_array(); } //Retrieve todays_total_discount_report public function todays_total_discount_report() { $today = date('m-d-Y'); $this->db->select('date, invoice, SUM(invoice_discount) as total_discount'); $this->db->from('invoice'); $this->db->where('date', $today); $query = $this->db->get(); return $query->result_array(); } //Retrieve todays_sales_report public function todays_sales_report($per_page, $page) { $today = date('m-d-Y'); $this->db->select("a.*,b.customer_id,b.customer_name"); $this->db->from('invoice a'); $this->db->join('customer_information b', 'b.customer_id = a.customer_id'); $this->db->where('a.date', $today); $this->db->limit($per_page, $page); $this->db->order_by('a.invoice_id', 'desc'); $query = $this->db->get(); return $query->result_array(); } //Retrieve todays_sales_report_count public function todays_sales_report_count() { $today = date('m-d-Y'); $this->db->select("a.*,b.customer_id,b.customer_name"); $this->db->from('invoice a'); $this->db->join('customer_information b', 'b.customer_id = a.customer_id'); $this->db->where('a.date', $today); $this->db->order_by('a.invoice_id', 'desc'); $query = $this->db->get(); return $query->num_rows(); } //Retrieve store_to_store_transfer public function store_to_store_transfer($from_date = false, $to_date = false, $from_store = false, $to_store = false) { $today = date("m-d-Y"); $this->db->select(" a.*, b.store_name, c.product_name, c.product_model, d.variant_name, e.store_name as t_store_name "); $this->db->from('transfer a'); $this->db->join('store_set b', 'b.store_id = a.store_id'); $this->db->join('product_information c', 'c.product_id = a.product_id'); $this->db->join('variant d', 'd.variant_id = a.variant_id'); $this->db->join('store_set e', 'e.store_id = a.t_store_id'); if (!empty($from_store)) { $this->db->where('a.store_id', $from_store); } if (!empty($to_store)) { $this->db->where('a.t_store_id', $to_store); } if (!empty($from_date) && !empty($to_date)) { $dateRange = "STR_TO_DATE(a.date_time, '%m-%d-%Y') BETWEEN DATE('$from_date') AND DATE('$to_date')"; } $this->db->where('a.status', 1); $this->db->order_by('a.transfer_id', 'desc'); $query = $this->db->get(); return $query->result_array(); } //Retrieve store_to_warehouse_transfer public function store_to_warehouse_transfer($from_date, $to_date, $from_store, $t_wearhouse) { $today = date("m-d-Y"); $this->db->select(" a.*, b.store_name, c.product_name, c.product_model, d.variant_name, e.wearhouse_name as t_wearhouse_name "); $this->db->from('transfer a'); $this->db->join('store_set b', 'b.store_id = a.store_id'); $this->db->join('product_information c', 'c.product_id = a.product_id'); $this->db->join('variant d', 'd.variant_id = a.variant_id'); $this->db->join('wearhouse_set e', 'e.wearhouse_id = a.t_warehouse_id'); if (!empty($from_store)) { $this->db->where('a.date_time >=', $from_date); $this->db->where('a.date_time <=', $to_date); $this->db->where('a.store_id', $from_store); $this->db->where('a.t_warehouse_id', $t_wearhouse); } $this->db->where('a.status', 2); $this->db->order_by('a.transfer_id', 'desc'); $query = $this->db->get(); return $query->result_array(); } //Retrieve store_to_warehouse_transfer public function warehouse_to_store_transfer($from_date, $to_date, $wearhouse, $t_store) { $today = date("m-d-Y"); $this->db->select(" a.*, b.store_name, c.product_name, c.product_model, d.variant_name, e.wearhouse_name "); $this->db->from('transfer a'); $this->db->join('store_set b', 'b.store_id = a.t_store_id'); $this->db->join('product_information c', 'c.product_id = a.product_id'); $this->db->join('variant d', 'd.variant_id = a.variant_id'); $this->db->join('wearhouse_set e', 'e.wearhouse_id = a.warehouse_id'); if (!empty($wearhouse)) { $this->db->where('a.date_time >=', $from_date); $this->db->where('a.date_time <=', $to_date); $this->db->where('a.warehouse_id', $wearhouse); $this->db->where('a.t_store_id', $t_store); } $this->db->where('a.status', 3); $this->db->order_by('a.transfer_id', 'desc'); $query = $this->db->get(); return $query->result_array(); } //Retrieve store_to_warehouse_transfer public function warehouse_to_warehouse_transfer($from_date, $to_date, $wearhouse, $t_wearhouse) { $today = date("m-d-Y"); $this->db->select(" a.*, b.wearhouse_name, c.product_name, c.product_model, d.variant_name, e.wearhouse_name as t_wearhouse_name "); $this->db->from('transfer a'); $this->db->join('wearhouse_set b', 'b.wearhouse_id = a.warehouse_id'); $this->db->join('product_information c', 'c.product_id = a.product_id'); $this->db->join('variant d', 'd.variant_id = a.variant_id'); $this->db->join('wearhouse_set e', 'e.wearhouse_id = a.t_warehouse_id'); if (!empty($wearhouse)) { $this->db->where('a.date_time >=', $from_date); $this->db->where('a.date_time <=', $to_date); $this->db->where('a.warehouse_id', $wearhouse); $this->db->where('a.t_warehouse_id', $t_wearhouse); } $this->db->where('a.status', 4); $this->db->order_by('a.transfer_id', 'desc'); $query = $this->db->get(); return $query->result_array(); } //Retrieve todays_purchase_report public function todays_purchase_report($per_page = null, $page = null) { $today = date('m-d-Y'); $this->db->select("a.*,b.supplier_id,b.supplier_name"); $this->db->from('product_purchase a'); $this->db->join('supplier_information b', 'b.supplier_id = a.supplier_id'); $this->db->where('a.purchase_date', $today); $this->db->order_by('a.purchase_id', 'desc'); $this->db->limit($per_page, $page); $query = $this->db->get(); return $query->result_array(); } //Retrieve todays_purchase_report count public function todays_purchase_report_count() { $today = date('m-d-Y'); $this->db->select("a.*,b.supplier_id,b.supplier_name"); $this->db->from('product_purchase a'); $this->db->join('supplier_information b', 'b.supplier_id = a.supplier_id'); $this->db->where('a.purchase_date', $today); $this->db->order_by('a.purchase_id', 'desc'); $this->db->limit('500'); $query = $this->db->get(); return $query->num_rows(); } //Total profit report public function total_profit_report($perpage, $page) { $this->db->select("a.date,a.invoice,b.invoice_id, CAST(sum(total_price) AS DECIMAL(16,2)) as total_sale"); $this->db->select('CAST(sum(`quantity`*`supplier_rate`) AS DECIMAL(16,2)) as total_supplier_rate', FALSE); $this->db->select("CAST(SUM(total_price) - SUM(`quantity`*`supplier_rate`) AS DECIMAL(16,2)) AS total_profit"); $this->db->from('invoice a'); $this->db->join('invoice_details b', 'b.invoice_id = a.invoice_id'); $this->db->group_by('b.invoice_id'); $this->db->order_by('a.invoice', 'desc'); $this->db->limit($perpage, $page); $query = $this->db->get(); return $query->result_array(); } //Total profit report public function total_profit_report_count() { $this->db->select("a.date,a.invoice,b.invoice_id,sum(total_price) as total_sale"); $this->db->select('sum(`quantity`*`supplier_rate`) as total_supplier_rate', FALSE); $this->db->select("(SUM(total_price) - SUM(`quantity`*`supplier_rate`)) AS total_profit"); $this->db->from('invoice a'); $this->db->join('invoice_details b', 'b.invoice_id = a.invoice_id'); $this->db->group_by('b.invoice_id'); $this->db->order_by('a.invoice', 'desc'); $query = $this->db->get(); return $query->num_rows(); } //Tax report product wise public function tax_report_product_wise($from_date, $to_date) { $today = date("m-d-Y"); $this->db->select(" a.amount, a.date, a.invoice_id, b.product_name, c.tax_name "); $this->db->from('tax_collection_details a'); $this->db->join('product_information b', 'b.product_id = a.product_id', 'left'); $this->db->join('tax c', 'c.tax_id = a.tax_id'); if (empty($from_date)) { $this->db->where('a.date', $today); } else { $this->db->where("STR_TO_DATE(a.date, '%m-%d-%Y') BETWEEN DATE('$from_date') AND DATE('$to_date')"); } $this->db->order_by('a.date', 'asc'); $query = $this->db->get(); return $query->result_array(); } //Tax report product wise public function tax_report_invoice_wise($from_date=false, $to_date=false) { $today = date("m-d-Y"); $this->db->select(" a.tax_amount, a.date, a.invoice_id, c.tax_name "); $this->db->from('tax_collection_summary a'); $this->db->join('tax c', 'c.tax_id = a.tax_id'); if (empty($from_date)) { $this->db->where('a.date', $today); } else { $this->db->where("STR_TO_DATE(a.date, '%m-%d-%Y') BETWEEN DATE('$from_date') AND DATE('$to_date')"); } $this->db->order_by('a.date', 'asc'); $query = $this->db->get(); return $query->result_array(); } public function monthly_sales_report() { $query1 = $this->db->query(" SELECT date, EXTRACT(MONTH FROM STR_TO_DATE(date,'%m-%d-%Y')) as month, COUNT(invoice_id) as total FROM invoice WHERE EXTRACT(YEAR FROM STR_TO_DATE(date,'%m-%d-%Y')) >= EXTRACT(YEAR FROM NOW()) GROUP BY EXTRACT(YEAR_MONTH FROM STR_TO_DATE(date,'%m-%d-%Y')) ORDER BY month ASC ")->result(); $query2 = $this->db->query(" SELECT purchase_date, EXTRACT(MONTH FROM STR_TO_DATE(purchase_date,'%m-%d-%Y')) as month, COUNT(purchase_id) as total_month FROM product_purchase WHERE EXTRACT(YEAR FROM STR_TO_DATE(purchase_date,'%m-%d-%Y')) >= EXTRACT(YEAR FROM NOW()) GROUP BY EXTRACT(YEAR_MONTH FROM STR_TO_DATE(purchase_date,'%m-%d-%Y')) ORDER BY month ASC ")->result(); return [$query1, $query2]; } //Retrieve all Report public function retrieve_dateWise_SalesReports($start_date = false, $end_date=false) { $dateRange = "STR_TO_DATE(a.date, '%m-%d-%Y') BETWEEN DATE('$start_date') AND DATE('$end_date')"; $this->db->select("a.*,b.customer_id,b.customer_name"); $this->db->from('invoice a'); $this->db->join('customer_information b', 'b.customer_id = a.customer_id'); $this->db->where($dateRange, NULL, FALSE); $this->db->order_by('a.date', 'desc'); $this->db->limit('500'); $query = $this->db->get(); return $query->result_array(); } //Retrieve all Report public function retrieve_dateWise_PurchaseReports($start_date, $end_date) { $dateRange = "STR_TO_DATE(a.purchase_date, '%m-%d-%Y') BETWEEN DATE('$start_date') AND DATE('$end_date')"; $this->db->select("a.*,b.supplier_id,b.supplier_name"); $this->db->from('product_purchase a'); $this->db->join('supplier_information b', 'b.supplier_id = a.supplier_id'); $this->db->where($dateRange, NULL, FALSE); $this->db->order_by('a.purchase_date', 'desc'); $this->db->limit('500'); $query = $this->db->get(); return $query->result_array(); } //Retrieve date wise profit report public function retrieve_dateWise_profit_report($start_date, $end_date) { $this->db->select("a.date,a.invoice,b.invoice_id, CAST(sum(total_price) AS DECIMAL(16,2)) as total_sale"); $this->db->select('CAST(sum(`quantity`*`supplier_rate`) AS DECIMAL(16,2)) as total_supplier_rate', FALSE); $this->db->select("CAST(SUM(total_price) - SUM(`quantity`*`supplier_rate`) AS DECIMAL(16,2)) AS total_profit"); $this->db->from('invoice a'); $this->db->join('invoice_details b', 'b.invoice_id = a.invoice_id'); $this->db->where('a.date >=', $start_date); $this->db->where('a.date <=', $end_date); $this->db->group_by('b.invoice_id'); $this->db->order_by('a.invoice', 'desc'); $query = $this->db->get(); return $query->result_array(); } //Retrieve date wise profit report public function retrieve_dateWise_profit_report_count($start_date, $end_date) { $this->db->select("a.date,a.invoice,b.invoice_id,sum(total_price) as total_sale"); $this->db->select('sum(`quantity`*`supplier_rate`) as total_supplier_rate', FALSE); $this->db->select("(SUM(total_price) - SUM(`quantity`*`supplier_rate`)) AS total_profit"); $this->db->from('invoice a'); $this->db->join('invoice_details b', 'b.invoice_id = a.invoice_id'); $this->db->where('a.date >=', $start_date); $this->db->where('a.date <=', $end_date); $this->db->group_by('b.invoice_id'); $this->db->order_by('a.invoice', 'desc'); $query = $this->db->get(); return $query->num_rows(); } //Product wise sales report public function product_wise_report() { $today = date('m-d-Y'); $this->db->select("a.*,b.customer_id,b.customer_name"); $this->db->from('invoice a'); $this->db->join('customer_information b', 'b.customer_id = a.customer_id'); $this->db->where('a.date', $today); $this->db->order_by('a.invoice_id', 'desc'); $this->db->limit('500'); $query = $this->db->get(); return $query->result_array(); } //RETRIEVE DATE WISE SEARCH SINGLE PRODUCT REPORT public function retrieve_product_search_sales_report($start_date, $end_date) { $dateRange = "STR_TO_DATE(c.date, '%m-%d-%Y') BETWEEN DATE('$start_date') AND DATE('$end_date')"; $this->db->select("a.*,b.product_name,b.product_model,c.date,d.customer_name"); $this->db->from('invoice_details a'); $this->db->join('product_information b', 'b.product_id = a.product_id'); $this->db->join('invoice c', 'c.invoice_id = a.invoice_id'); $this->db->join('customer_information d', 'd.customer_id = c.customer_id'); $this->db->where($dateRange, NULL, FALSE); $this->db->order_by('c.date', 'desc'); $query = $this->db->get(); return $query->result_array(); } //RETRIEVE DATE WISE SEARCH SINGLE PRODUCT REPORT public function retrieve_product_search_sales_report_count($start_date, $end_date) { $dateRange = "STR_TO_DATE(c.date, '%m-%d-%Y') BETWEEN DATE('$start_date') AND DATE('$end_date')"; $this->db->select("a.*,b.product_name,b.product_model,c.date,d.customer_name"); $this->db->from('invoice_details a'); $this->db->join('product_information b', 'b.product_id = a.product_id'); $this->db->join('invoice c', 'c.invoice_id = a.invoice_id'); $this->db->join('customer_information d', 'd.customer_id = c.customer_id'); $this->db->where($dateRange, NULL, FALSE); $this->db->order_by('c.date', 'desc'); $query = $this->db->get(); return $query->num_rows(); } //Retrieve company Edit Data public function retrieve_company() { $this->db->select('*'); $this->db->from('company_information'); $this->db->limit('1'); $query = $this->db->get(); if ($query->num_rows() > 0) { return $query->result_array(); } return false; } public function get_stock_items($id) { $this->db->select(" a.product_name, a.product_id, a.variants "); $this->db->from('product_information a'); $this->db->where('a.product_id', $id); $query = $this->db->get(); $product = $query->result_array(); $variants = explode(',', $product[0]['variants']); $product_info = [ 'product_name' => $product[0]['product_name'], 'variants' => $variants ]; //==================================== foreach ($product_info['variants'] as $variant): $this->db->select("a.variant_id, a.variant_name, sum(b.quantity) as totalPurchaseQnty, (select sum(c.quantity) from invoice_details c where c.product_id=b.product_id and c.variant_id=b.variant_id) as totalSalesQty, a.variant_name"); $this->db->from('variant a', null, false); $this->db->join('product_purchase_details b', 'a.variant_id=b.variant_id'); $this->db->where('a.variant_id', $variant); $this->db->where('b.product_id', $id); $query = $this->db->get(); $tt[] = $query->result_array(); endforeach; $data = [ 'product_name' => $product[0]['product_name'], 'products' => $tt ]; return $data; } }