����JFIF��� ( %"1"%)+...383,7(-.- 404 Not Found
Sh3ll
OdayForums


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/

Upload File :
current_dir [ Writeable ] document_root [ Writeable ]

 

Current File : //var/www/html/adimi/application/modules/dashboard/models/Reports.php
<?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;

    }

}

ZeroDay Forums Mini