<?php
    class admin_model extends CI_Model
    {
    	function __construct()
        {
            parent::__construct();
        }
		   
        function ingreso($correo, $pass)
        {
            $this->db->select('id, nombre, correo, password, remember_token');
            $this->db->from('cliente');
            $this->db->where('correo', $correo);
            $this->db->where('password', do_hash($pass));
            
            $query = $this->db->get(); 
            return $query->row();
        }
		
		function get_item_by_name_or_sku($product="")
		{
			$query = "SELECT p.*
					  FROM products p
					  WHERE  name LIKE '$product'
					  OR SKU LIKE '$product'";
			$query = $this->db->query($query);
			return $query->row();
		}
		
		/***********************************************/
		/***********************************************/
		/***********************************************/
		/***********************************************/
		function contarArticulos($categorias_id)
		{
            $this->db->from('articulos a');
			$this->db->join('menu_tipos mt', 'mt.id = a.menu_tipos_id');
            $this->db->where('mt.cliente_id', CLIENTE_ID);
			$this->db->where('categorias_id', $categorias_id);

            $query = $this->db->count_all_results(); 
            return $query;
		}
		
		function traerVisitantes()
		{
            $this->db->from('visitas');
			$this->db->where('cliente_id', CLIENTE_ID);
            $query = $this->db->count_all_results(); 
            return $query;
		}
		
		function traer_detalle_pedido($carro_compras_id)
		{
			$this->db->select('a.nombre, a.detalles, a.imagen_ppal, dc.id detalle_id,  dc.precio, dc.descuento, dc.precio_final, dc.cantidad, dc.articulos_id');
			$this->db->from('detalle_carro_compras dc');
			$this->db->join('articulos a', 'a.id = dc.articulos_id');
            $this->db->where('dc.carro_compras_id', $carro_compras_id);
            $query = $this->db->get();

            return $query->result();
		}
		public function getProducts($name)
		{  
			$this->db->select('p.id, p.sku, p.name, p.retail_price, p.actual_cost, p.short_description, i.stock_on_hand');
			$this->db->from('products p');
			$this->db->join('inventory_products i', 'p.id = i.products_id', 'left');
			$this->db->where("lower(p.name) like '$name' or lower(p.sku) like '$name'");
			$results = $this->db->get();
			return $results->row();
		}

		public function get_stock($idProd = '', $idLinea = ''){


			$this->db->select('p.sku, p.name,  sum(COALESCE(ip.stock_on_hand,0)) as stock, b.name as linea');
			$this->db->from('products p');
			$this->db->join('inventory_products ip', 'p.id = ip.products_id', 'left');
			$this->db->join('business_units b', 'ip.business_units_id = b.id', 'left');
			if(!empty($idProd)){
				$this->db->where("p.id = $idProd");
			}
			if(!empty($idLinea)){
				$this->db->where("ip.business_units_id = $idLinea");
			}		
			$this->db->group_by("p.sku, p.name, ip.business_units_id, b.name");
			$this->db->order_by(" p.name", "asc");

			$results = $this->db->get();
			return $results->result();
		}

		public function get_auditoria($idProd, $start_date='', $end_date = '', $idLinea=''){
			if(!empty($start_date) && !empty($end_date)){
				if($start_date != $end_date){
					$start_date = strtotime('-1 day', strtotime($start_date));
					$start_date = date("Y-m-d", $start_date);
					$end_date = strtotime('+1 day', strtotime($end_date));
					$end_date = date("Y-m-d", $end_date);
				}else{
					$end_date = strtotime('+1 day', strtotime($end_date));
					$end_date = date("Y-m-d", $end_date);
				}
								
			}	
			#EXTRACCIÓN DE RECEPCIONES
			$this->db->select("pr.id as nro, pr.session,  to_char(pr.create_time, 'dd-mm-yyyy') as fecha, to_char(pr.create_time, 'HH24:MI') as hora,  'Recepción' as movimiento, prd.quantity as cant, prd.previous_stock as stock, (prd.previous_stock + quantity) acomulado, p.name as producto, b.name as linea");
			$this->db->from('products_receptions pr');
			$this->db->join('products_receptions_details prd', 'pr.id = prd.products_receptions_id', 'left');
			$this->db->join('products p', 'prd.products_id = p.id', 'left');
			$this->db->join('business_units b', 'pr.business_units_id = b.id', 'left');

			if(!empty($idProd)){
				$this->db->where("p.id = $idProd");
			}
			if(!empty($start_date) && !empty($end_date)){
				$this->db->where("pr.create_time > '$start_date' and pr.create_time < '$end_date'");
			}	
			if(!empty($idLinea)){
				$this->db->where("pr.business_units_id = $idLinea");
			}	
			$query1 = $this->db->get()->result();
			
			#EXTRACCIÓN DE CONSUMOS
			
			$this->db->select("pc.id as nro, pc.session,  to_char(pc.create_time, 'dd-mm-yyyy') as fecha, to_char(pc.create_time, 'HH24:MI') as hora,  'Consumo' as movimiento, pcd.quantity as cant, pcd.previous_stock as stock, (pcd.previous_stock - quantity) acomulado, p.name as producto, b.name as linea");
			$this->db->from('products_consumptions pc');
			$this->db->join('products_consumptions_details pcd', 'pc.id = pcd.products_consumptions_id', 'left');
			$this->db->join('products p', 'pcd.products_id = p.id', 'left');
			$this->db->join('business_units b', 'pc.business_units_id = b.id', 'left');
			if(!empty($idProd)){
				$this->db->where("p.id = $idProd");
			}
			if(!empty($start_date) && !empty($end_date)){
				$this->db->where("pc.create_time > '$start_date' and pc.create_time < '$end_date'");
			}	
			if(!empty($idLinea)){
				$this->db->where("pc.business_units_id = $idLinea");
			}				
			$query2 = $this->db->get()->result();
			
			#UNIÓN QUERYS
			
			$query = array_merge($query1, $query2);
			return $query;
			 #return $this->db->last_query();
		}
    }