KOKINIO - MANAGER

Edit File: Comision.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use DB;

class Comision extends Model
{
    //
    protected $table = "comision";
    public $incrementing = false;
    public $timestamps = false;

public function existPay($cliente_id, $year, $month){
      $where = "";
      if($year == null){
        $year = date('Y');
      }
      if($month == null){
        $month = date('m');
      }
      if ($cliente_id != null && $cliente_id != 'vacio') {
        $where = " AND c.cliente_id = '$cliente_id' ";
      }
      $sql = "SELECT c.*, cl.nombre, cl.ape_p, cl.ape_m, cl.lada, cl.tel, cl.active_id,
              (select sum(cp.cantidad) 
                from comision_pago cp 
                join venta v on v.id = cp.venta_id and  Year(v.fecha_venta) = $year and Month(v.fecha_venta) = $month
                where cp.cliente_id = cl.id 
                and cp.status_id = 1) gasto
              FROM comision c
              JOIN cliente cl ON c.cliente_id = cl.id
              WHERE c.ano = $year AND c.mes = $month $where and c.status_id = 1";

$query = DB::select(DB::raw($sql));

if(count($query) != null){
        return $query;
      }
    }

public function totalPagado($year, $month, $clientId){
      if($year == null){
        $year = date('Y');
      }
      if($month == null){
        $month = date('m');
      }
      $sql = "SELECT SUM(comision) total
              FROM comision
              WHERE ano = $year AND mes = $month AND pagado = 1 AND status_id = 1 and cliente_id = '$clientId'";

$query = DB::select(DB::raw($sql));

if(count($query) != null){
        return $query;
      }
    }

public function checkComisionUpdate($cliente_id, $comision = null , $year = null, $month = null, $comision_3meses = null) {
      $whereComision = "";
      if($year == null){
        $year = date('Y');
      }
      if($month == null){
        $month = date('m');
      }
      if($comision_3meses == null){
        $comision_3meses = 0;
      }
      if ($comision != null) {
        $whereComision = " AND comision = $comision ";
      }
      $sql = "SELECT *
              FROM comision
              WHERE cliente_id = '$cliente_id'
              AND ano = $year 
              AND mes = $month 
              -- AND comision_3meses = $comision_3meses
              -- $whereComision
              AND status_id = 1 ";

$query = DB::select(DB::raw($sql));

if(count($query) != null){
        return $query;
      }
    }

public function existComisionCantidad($venta_id, $cliente_id, $cantidad, $year, $month){
      if($year == null){
        $year = date('Y');
      }
      if($month == null){
        $month = date('m');
      }
      $sql = "SELECT *
              FROM comision
              WHERE cliente_id = '$cliente_id'
              AND ano = $year 
              AND mes = $month 
              AND comision_3meses >= ($cantidad + COALESCE((
                                select sum(cp.cantidad) 
                                from comision_pago cp
                                join venta v on cp.venta_id = v.id and v.status_id = 1 and v.id not in ('$venta_id') and Year(v.fecha_venta) = $year and Month(v.fecha_venta) = $month
                                where cp.cliente_id = '$cliente_id'
                                and cp.status_id = 1
                              ), 0))
              AND status_id = 1
              AND pagado = 0";

$query = DB::select(DB::raw($sql));

if(count($query) != null){
        return $query;
      }
    }

public function updateComisionPagado($cliente_id, $year, $month, $paid) {
    if ($month != null){
      $date = $year."-".$month."-01";
    } else {
      $date = 'NOW()';
    }
    $sql = "UPDATE comision SET meses_pagados = $paid
            WHERE mes < $month and ano = $year and cliente_id = '$cliente_id ' 
                AND last_day(str_to_date(concat(ano,'-',mes,'-01'), '%Y-%m-%d')) >= date(last_day('$date') + interval 1 day - interval 4 month)
                AND last_day(str_to_date(concat(ano,'-',mes,'-01'), '%Y-%m-%d')) <=  last_day(date('$date') - interval 1 month)
                AND status_id = 1 
    ";
     $query = DB::select(DB::raw($sql));
     if(count($query) != null){
       return $query;
     }
   }

}