<?php
    class warehouse_model extends CI_Model
    {
    	function __construct()
        {
            parent::__construct();
            //$this->load->helper(HELPER_MODEL);
            //define("STAFF_ID", ''.$this->session->userdata('rt_staff').'');
            //define("BUSINESS_UNIT_ID", get_value_by_pk('staff', STAFF_ID, 'login_location_id'));
		}
		
		function warehouses () {
            $query = $this->db->query('
                SELECT id, name, code FROM warehouses ORDER BY id ASC
            ');
            return $query->result();
        }
        
        function stock () {
            $query = $this->db->query("
                SELECT products.id, products.NAME AS producto,
                array_to_string(
                ARRAY (
                SELECT
                CONCAT(warehouses.code, ':', COALESCE((SELECT inventory_products.stock_on_hand FROM inventory_products
                WHERE inventory_products.products_id = products.id AND inventory_products.warehouses_id = warehouses.id), 0))
                FROM warehouses ORDER BY id ASC
                ), ','
                ) AS stock,
                COALESCE((SELECT SUM(inventory_products.stock_on_hand) FROM inventory_products
                WHERE inventory_products.products_id = products.id AND inventory_products.business_units_id=".BUSINESS_UNIT_ID."), 0) AS total
                FROM products
                WHERE products.is_active = true
                AND products.tipo = 2
                GROUP BY products.id, products.name
                ORDER BY products.name ASC
            ");
            return $query->result();
        }
    }