<?php
class RewardsReportListasRepository extends EntityRepository {    
    
    public function getSalesByCustomerGroupedByProduct($options){            
        $fecha = $this->createFilterFecha($options['startDate'], $options['endDate'], 'v.fecha');
        
        $query = "SELECT "
                . "CONCAT(rcu.name,' ',rcu.last_name)as customer_name,"
                . "rcu.phone,"
                . "CONCAT(p.descripcion,' ',IFNULL(t.descripcion,'')) as product,"
                . "SUM(d.cantidad)as quantity,"
                . "d.precio_sin_impuestos * d.cantidad as subtotal,"
                . "d.descuento_item_monto + d.descuento_orden_monto as discount,"
                . "d.impuestos_monto,"
                . "s.nombre as store "          
                . "FROM "
                . "rewards_records rre "
                . "LEFT JOIN rewards_customers rcu ON rre.reward_customer_id "
                . "LEFT JOIN ventas v ON rre.sales_id = v.id "
                . "LEFT JOIN ventasdetalles d ON d.idVenta = v.id "
                . "LEFT JOIN sucursales s ON v.idSucursal = s.id "
                . "LEFT JOIN productos p ON d.idProducto = p.id "
                . "LEFT JOIN tamanos t ON p.tamano = t.id "
                . "WHERE 1 = 1 "
                . "$fecha "
                . "GROUP BY d.idProducto,s.id";
       
        $result = $this->query($query);

        if($result->num_rows > 0){
            $result = $this->resultToArray($result);
            
            foreach ($result as $key => $row){
                $total = $row['subtotal'] - $row['discount'] + $row['impuestos_monto'];              
                $result[$key]['amount'] = $total;      
            }
          
            return array(
                'data'=>$result
            );
        }else{
            null;
        }
    }
    
    public function createFilterFecha($fechaInicio,$fechaFin,$campoFecha = null ){
        $fecha = null;
        $tools = new Tools();
        if($fechaInicio!=null){
            $fechaInicio = $tools->setFormatDateToDB($fechaInicio);
            if($fechaFin!=null){
                $fechaFin = $tools->setFormatDateToDB($fechaFin);
                $fecha .=" AND $campoFecha BETWEEN '{$fechaInicio}' AND '{$fechaFin}' ";
            }else{
                $fecha .=" AND $campoFecha BETWEEN '{$fechaInicio}' AND '{$fechaInicio}' ";
            }
        }elseif($fechaFin!=null){
            $fecha .=" AND $campoFecha BETWEEN '{$fechaFin}' AND '{$fechaFin}' ";
        }
        
        return $fecha;
    }  
}

