/home/dvjjulio/test.istyle.mx/app/PremioCliente.php
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
use DB;
class PremioCliente extends Model
{
//
protected $table = 'premio_cliente';
public $incrementing = false;
public function totalCajas($id,$year,$month){
$sql = "SELECT c.id, c.registro, c.nombre, c.patrocinador, c.ape_p, c.ape_m, nivel, v.fecha_venta, p.descripcion, MONTH(v.fecha_venta) month, YEAR(v.fecha_venta) year, v.total , SUM(IF(v.total >=2000,1,0)) total_cajas
FROM(
SELECT id, registro, nombre, patrocinador, ape_p, ape_m, 'primer' nivel
FROM cliente c
WHERE patrocinador in ('$id')
UNION ALL
SELECT id, registro, nombre, patrocinador, ape_p, ape_m, 'segunrdo' nivel
FROM cliente WHERE patrocinador in (
SELECT id
FROM cliente WHERE patrocinador = '$id'
)
UNION ALL
SELECT id, registro, nombre, patrocinador, ape_p, ape_m, 'tercer' nivel
FROM cliente WHERE patrocinador in (
SELECT id
FROM cliente WHERE patrocinador in (
SELECT id
FROM cliente WHERE patrocinador = '$id'
)
)
UNION ALL
SELECT id, registro, nombre, patrocinador, ape_p, ape_m, 'cuarto' nivel
FROM cliente WHERE patrocinador in (
SELECT id
FROM cliente WHERE patrocinador in (
SELECT id
FROM cliente WHERE patrocinador in (
SELECT id
FROM cliente WHERE patrocinador = '$id'
)
)
)
)as c
JOIN venta v ON v.cliente_id = c.id AND v.status_id = 1 AND MONTH(v.fecha_venta) = $month AND YEAR(v.fecha_venta) = $year
JOIN producto p ON p.id = v.producto_id AND p.status_id = 1
GROUP BY month";
$query = DB::select(DB::raw($sql));
if(count($query) != null){
return $query;
}
}
public function verifyAward($premio){
$sql = "SELECT *
FROM premio_cliente
WHERE cliente_id = '$premio->cliente_id'
AND status_id = 1 AND completado = 0";
$query = DB::select(DB::raw($sql));
return $this->verifyInsert($query);
}
public function verifyInsert($query){
if (count($query)) {
if($query[0]->mes_1 == null) {
return ['','mes_1','cajas_1'];
} else if($query[0]->mes_2 == null) {
return [$query[0]->id,'mes_2','cajas_2'];
} else if($query[0]->mes_3 == null) {
return [$query[0]->id,'mes_3','cajas_3'];
} else {
return [$query[0]->id,'complete',''];
}
} else {
return ['','mes_1','cajas_1'];
}
}
public function verifyMonthYear($premios, $month, $year, $mes){
$mes = "";
switch($month){
case 'mes_1':
$mes = 'mes_1';
break;
case 'mes_2':
$mes = 'mes_1';
break;
case 'mes_3':
$mes = 'mes_2';
break;
}
$sql = "SELECT *
FROM premio_cliente
WHERE cliente_id = '$premios->cliente_id'
AND status_id = 1 AND completado = 0 AND year = $year AND $mes = $mes ";
$query = DB::select(DB::raw($sql));
return count($query);
}
public function clientePremios(){
$sql = "SELECT pc.id, c.id, concat(c.nombre, ' ', c.ape_p) nombre, p.categoria, pc.mes_1, pc.mes_2, pc.mes_3, pc.cajas_1, pc.cajas_2, pc.cajas_3, pc.year, pc.completado
FROM premio_cliente pc
JOIN cliente c ON c.id = pc.cliente_id AND c.status_id = 1
JOIN premio p ON p.id = pc.premio_id
WHERE pc.status_id = 1
ORDER by pc.year ASC, pc.mes_1 DESC";
$query = DB::select(DB::raw($sql));
if(count($query) != null){
return $query;
}
}
}