KOKINIO - MANAGER

Edit File: 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;
        }
    }
}