<?php

    class report_model extends CI_Model
    {
    	function __construct()
        {
            parent::__construct();
        }	
         function suma($where)
	{
           $query="select staff.first_name, staff.last_name, (select sum(net_total) from sales_transactions where folio_doc is not null and pendient = 0 and  (  staff_id = staff.id or staff_id=usuario_ot)  and to_char(create_time, 'MM')='".$where."') as ventas, staff_comision.value from staff inner join staff_comision on staff_comision.staff_id = staff.id where staff.staff_categories_id = 400";
           $result= $this->db->query($query);
           
           return $result->result();
 
        }
        function update($id,$fechax,$tabla,$campo)
        {
            $this->db->set($campo, $fechax); //value that used to update column  
        $this->db->where('id', $id); //which row want to upgrade  
            $this->db->update($tabla);
        }
              
          function suma1()
	{
           $query='select staff.first_name, staff.last_name,  (select sum(net_total) from sales_transactions where folio_doc is not null and pendient = 0 and (  staff_id = staff.id or staff_id=usuario_ot) ) as ventas, staff_comision.value from staff inner join staff_comision on staff_comision.staff_id = staff.id where staff.staff_categories_id = 400';
           $result= $this->db->query($query);
           
           return $result->result();
            
        }
        function get_cost($where)
        {
        	$this->db->select('sum(p.actual_cost) actual_cost');
			$this->db->from('sales_transactions st');
			$this->db->join('sales_transactions_details std', 'std.sales_transactions_id = st.id');
			$this->db->join('products p', 'p.id = std.products_id');
        	foreach ($where as $key => $value) 
        	{
				$this->db->where("$key", $value);
			}
			$query = $this->db->get();
			 
            return $query->row()->actual_cost;
        }
		
		function get_daily_sales_products_details($where)
        {
        	$this->db->select('p.id as idp, pc.name category, p.name product, p.sku, p.cod_sap, SUM(std.quantity_items) quantity,SUM(std.total_price) venta_total');
			$this->db->from('sales_transactions st');
			$this->db->join('sales_transactions_details std', 'std.sales_transactions_id = st.id');
			$this->db->join('products p', 'p.id = std.products_id');
			$this->db->join('product_categories pc', 'pc.id = p.product_categories_id');
        	foreach ($where as $key => $value) 
        	{
				$this->db->where("$key", $value);
			}
			$this->db->group_by('p.name, pc.name ,p.sku, p.cod_sap, idp');
			$this->db->order_by('pc.name');
			$query = $this->db->get();

            return $query->result();
        }

        function get_comision_porcentaje($vendedor_id)
        {
        	$this->db->select('sc.value');
			$this->db->from('staff_comision sc');
        	$this->db->where("staff_id", $vendedor_id);
			$query = $this->db->get();
            return $query->result();
        }

        function get_lineas_compradas($venta_id)
        {
        	$this->db->select('st.products_id, st.quantity_items, st.total_price, st.item_price, p.product_categories_id');
			$this->db->from('sales_transactions_details st');
        	$this->db->where("st.sales_transactions_id", $venta_id);
			$this->db->join('products p', 'p.id = st.products_id');
			$query = $this->db->get();

            return $query->result();
        }

        function calcular_comision($productos, $comision)
        {
        	
        	$total=0;
        	foreach ($productos as $key => $value) {
        		$linea = $value->product_categories_id;
        		switch ($linea) {
        			case '1':
        				$porcentaje = $comision[0]->value;
        				$total = ($value->total_price/1.19 * $porcentaje) / 100;

        				break;
        			case '2':
        				$porcentaje = $comision[1]->value;
        				$total = ($value->total_price/1.19 * $porcentaje) / 100;

        				break;
        			default:
        				$porcentaje = $comision[2]->value;
        				$total = ($value->total_price/1.19 * $porcentaje) / 100;

        				break;
        		}
        	}
            return $total;
		}
		
		function comentarios($id) {
			$query = $this->db->query("SELECT comentarios_ot.comentario, comentarios_ot.fecha_comment, concat(staff.first_name, ' ', staff.last_name) as usuario from comentarios_ot inner join staff on staff.id = comentarios_ot.usuario where comentarios_ot.id_ot = " . $id . " order by fecha_comment desc");

			return $query->num_rows() === 0 ? NULL : $query->result();
		}

		function getVentas($where)#REPORTE VENTAS MENSUALES
        {	
        	$this->db->select('sum(st.gross_total) venta_total');
			$this->db->from("sales_transactions st");
			$this->db->where($where);
			$query = $this->db->get();
            return $query->row();
        }

		function getVentasDay($where) #REPORTE VENTA DIARIAS
		{
			$this->db->select('sum(s.staff_payment)as sueldo, sum(s.sales_commission) as comision ');
			$this->db->from('sessions s');
			$this->db->join('sales_transactions st', 's.id = st.sessions_id', 'left');
			$this->db->where($where);
			$this->db->group_by("s.id");
			$query = $this->db->get();
			return $query->row();
		}


		function getComisionesStaff($where, $gruop_by = null) #REPORTES
		{
			$this->db->select('(sum(s.staff_payment)/ count(s.id)) as sueldo, (sum(s.sales_commission)/ count(s.id)) as comision ');
			$this->db->from('sessions s');
			$this->db->join('sales_transactions st', 's.id = st.sessions_id', 'left');
			$this->db->where($where);
			if(!empty($gruop_by)){
				$this->db->group_by($gruop_by);
			}
			$query = $this->db->get();
			return $query->result();
		}

    }