/home/dvjjulio/test.istyle.mx/app/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;
}
}
}