/home/dvjjulio/test.istyle.mx/app/Venta.php
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
use DB;
class Venta extends Model
{
//
protected $table = 'venta';
public $incrementing = false;
public function ventasAll($id = null, $cliente = "vacio", $month = null, $year = null, $registro = null){
$wherec = "";
$venta = "";
$wherer = "";
if($id != null){
$wherem = "";
$wherey = "";
$venta = " AND v.id = '".$id."' ";
}else{
$wherem = " AND MONTH(v.fecha_venta) = MONTH(CURDATE()) ";
$wherey = " AND YEAR(v.fecha_venta) = YEAR(CURDATE()) ";
}
if($month != null){
$wherem = " AND MONTH(v.fecha_venta) = $month ";
}
if($year != null){
$wherey = " AND YEAR(v.fecha_venta) = $year ";
}
if($cliente != "vacio" ){
$wherec = " AND c.id = '".$cliente."' ";
}
if($registro != null ){
$wherer = " AND v.registro_id = '".$registro."' ";
}
$sql = "SELECT null pago, null nombrePago, v.id, v.cliente_id, p.id producto_id, v.cantidad cantidad, v.total,
v.comentario, p.descripcion, c.nombre cliente, c.ape_p, c.ape_m,
c.registro, v.created_at, v.fecha_venta, c.calle, c.numero, c.cp, c.delegacion,
e.estado, p.costo_mx, p.costo_eu, p.costo_gua, c.lada, c.tel, c.colonia, v.approved, c2.nombre creado, true parent
FROM venta v
JOIN producto p ON p.id = v.producto_id AND p.status_id = 1
JOIN cliente c ON c.id = v.cliente_id AND c.status_id = 1 $wherec
JOIN cliente c2 ON c2.id = v.registro_id AND c2.status_id = 1 $wherer
JOIN estado e ON e.id = c.estado_id
WHERE v.status_id = 1 ".$venta." ". $wherem ." ". $wherey ."
ORDER BY v.created_at DESC
";
$query = DB::select(DB::raw($sql));
if(count($query) != null){
return $query;
}
}
public function ventasAllPagos($id = null, $cliente = null, $month = null, $year = null, $registro = null){
$wherec = "";
$venta = "";
$wherer = "";
if($id != null){
$wherem = "";
$wherey = "";
$venta = " AND v.id = '".$id."' ";
}else{
$wherem = " AND MONTH(v.fecha_venta) = MONTH(CURDATE()) ";
$wherey = " AND YEAR(v.fecha_venta) = YEAR(CURDATE()) ";
}
if($month != null){
$wherem = " AND MONTH(v.fecha_venta) = $month ";
}
if($year != null){
$wherey = " AND YEAR(v.fecha_venta) = $year ";
}
if($cliente != null ){
$wherec = " AND c.id = '".$cliente."' ";
}
if($registro != null ){
$wherer = " AND v.registro_id = '".$registro."' ";
}
$sql = "SELECT cp.cantidad pago, c3.nombre nombrePago, v.id, v.cliente_id, p.id producto_id, v.cantidad cantidad, v.total,
v.comentario, p.descripcion, c.nombre cliente, c.ape_p, c.ape_m,
c.registro, v.created_at, v.fecha_venta, c.calle, c.numero, c.cp, c.delegacion,
e.estado, p.costo_mx, p.costo_eu, p.costo_gua, c.lada, c.tel, c.colonia, v.approved, c2.nombre creado, false parent
FROM venta v
JOIN producto p ON p.id = v.producto_id AND p.status_id = 1
JOIN cliente c ON c.id = v.cliente_id AND c.status_id = 1 $wherec
JOIN cliente c2 ON c2.id = v.registro_id AND c2.status_id = 1 $wherer
JOIN estado e ON e.id = c.estado_id
LEFT JOIN comision_pago cp ON cp.venta_id = v.id and cp.status_id = 1
LEFT JOIN cliente c3 ON c3.id = cp.cliente_id and c3.status_id = 1
WHERE v.status_id = 1 ".$venta." ". $wherem ." ". $wherey ."
ORDER BY v.created_at DESC
";
$query = DB::select(DB::raw($sql));
if(count($query) != null){
return $query;
}
}
public function red_first($id,$nivel, $month = null, $year = null, $country = 'Mexico') {
$comisionCountry = [
'Mexico' => 'n.comision_mx',
'USA' => 'n.comision_eu',
'Canada' => 'n.comision_eu',
'Guatemala' => 'n.comision_gua',
'Chile' => 'n.comision_eu',
'El Salvador' => 'n.comision_eu',
];
$wherem = " AND MONTH(v.fecha_venta) = MONTH(CURDATE()) ";
$wherey = " AND YEAR(v.fecha_venta) = YEAR(CURDATE()) ";
$month_ = " MONTH(CURDATE()) ";
$year_ = " YEAR(CURDATE()) ";
if($month != null){
$wherem = " AND MONTH(v.fecha_venta) = $month ";
$month_ = $month;
}
if($year != null){
$wherey = " AND YEAR(v.fecha_venta) = $year ";
$year_ = $year;
}
$sql = "SELECT c.id, c.nombre, CONCAT(c.ape_p,' ',c.ape_m) apellidos, c.registro, c.patrocinador,
c.tel, c.lada, v.created_at, SUM(v.cantidad) cantidad, SUM(v.total) total, n.nivel,
(TRUNCATE((SUM(v.total)/4000), 0)*($comisionCountry[$country])) comision, MONTH(v.fecha_venta) mes, YEAR(v.fecha_venta) year,
pa.nombre patrocinador_nombre, pa.ape_p patrocinador_ape_p, co.pagado, pr.comisiona, pa.active_id, pai.pais
FROM cliente c
JOIN cliente p ON p.id = c.id AND p.status_id = 1
LEFT JOIN pais pai ON pai.id = c.pais_id
LEFT JOIN cliente pa ON c.patrocinador = pa.id
LEFT JOIN comision co ON co.cliente_id = c.id AND co.mes = $month_ AND co.ano = $year_
LEFT JOIN venta v ON v.cliente_id = c.id AND v.status_id = 1 AND v.approved = 1 $wherem".' '."$wherey
LEFT JOIN producto pr ON v.producto_id = pr.id AND pr.status_id = 1
JOIN nivel n ON n.orden = $nivel
WHERE c.patrocinador IN ($id)
AND c.status_id = 1
GROUP BY c.id, MONTH(v.fecha_venta), YEAR(v.fecha_venta)
ORDER BY pa.nombre";
$query = DB::select(DB::raw($sql));
if(count($query) != null){
return $query;
}
}
public function monthYearVenta($year = null){
$group = " YEAR(v.fecha_venta) ";
$where = "";
if($year != null){
$group = " MONTH(v.fecha_venta), YEAR(v.fecha_venta) ";
$where = " WHERE YEAR(v.fecha_venta) = $year";
}
$sql = "SELECT MONTH(v.fecha_venta) mes, YEAR(v.fecha_venta) year
FROM venta v
$where
GROUP BY $group
ORDER BY MONTH(v.fecha_venta) ";
$query = DB::select(DB::raw($sql));
if(count($query) != null){
return $query;
}
}
public function cliente_compra($id, $year = null, $month = null){
$wheremonth= " AND MONTH(v.fecha_venta) = MONTH(CURDATE()) ";
$whereyear= " AND YEAR(v.fecha_venta) = YEAR(CURDATE()) ";
if($month != null){
$wheremonth = " AND MONTH(v.fecha_venta) = $month ";
}
if($year != null){
$whereyear = " AND YEAR(v.fecha_venta) = $year ";
}
$sql = "SELECT v.*
FROM venta v
WHERE v.approved = 1 AND v.status_id = 1 AND v.cliente_id = '".$id."' ".$wheremonth." ".$whereyear."
GROUP BY YEAR(v.fecha_venta), MONTH(v.fecha_venta)";
$query = DB::select(DB::raw($sql));
if(count($query) != null){
return $query;
}
}
public function Get_3_Months($id, $year = null, $month = null){
if($month != null){
$date = $year."-".$month."-01";
} else {
$date = 'NOW()';
}
$sql = "SELECT (sum(comision) - IF(SUM(comision_pagada) IS NULL, 0, SUM(comision_pagada))) comision, date(last_day('$date') + interval 1 day - interval 4 month) firstMonth,
date(last_day('$date') + interval 1 day - interval 3 month) secondMonth, date(last_day('$date') + interval 1 day - interval 2 month) thirdMonth
FROM comision
WHERE cliente_id = '$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 and comentarios = '' and meses_pagados != 1 ";
/*
$sql = "SELECT sum(comision) comision
FROM comision c
WHERE cliente_id = '$id'
AND ano = YEAR('$date') AND mes = MONTH('$date')
AND status_id = 1";
*/
$query = DB::select(DB::raw($sql));
if(count($query) != null){
return $query;
}
}
public function VentasCount($id = null, $cliente = "vacio", $month = null, $year = null, $registro = null){
$wherec = "";
$venta = "";
$wherer = "";
if($id != null){
$wherem = "";
$wherey = "";
$venta = " AND v.id = '".$id."' ";
}else{
$wherem = " AND MONTH(v.fecha_venta) = MONTH(CURDATE()) ";
$wherey = " AND YEAR(v.fecha_venta) = YEAR(CURDATE()) ";
}
if($month != null){
$wherem = " AND MONTH(v.fecha_venta) = $month ";
}
if($year != null){
$wherey = " AND YEAR(v.fecha_venta) = $year ";
}
if($cliente != "vacio" && $cliente != "" ){
$wherec = " AND c.id = '".$cliente."' ";
}
if($registro != null ){
$wherer = " AND v.registro_id = '".$registro."' ";
}
$sql = "SELECT count(1) total, SUM(v.cantidad) as cantidad
FROM venta v
JOIN producto p ON p.id = v.producto_id AND p.status_id = 1
JOIN cliente c ON c.id = v.cliente_id AND c.status_id = 1 $wherec
JOIN cliente c2 ON c2.id = v.registro_id AND c2.status_id = 1 $wherer
JOIN estado e ON e.id = c.estado_id
WHERE v.status_id = 1 ".$venta." ". $wherem ." ". $wherey ."
ORDER BY v.created_at DESC
";
$query = DB::select(DB::raw($sql));
if(count($query) != null){
return $query;
}
}
public function VentasWithLimit($id = null, $limit = null, $cliente = "vacio", $month = null, $year = null, $registro = null){
$wherec = "";
$venta = "";
$wherer = "";
$limitCondition = " LIMIT 0, 100 ";
if($id != null){
$wherem = "";
$wherey = "";
$venta = " AND v.id = '".$id."' ";
}else{
$wherem = " AND MONTH(v.fecha_venta) = MONTH(CURDATE()) ";
$wherey = " AND YEAR(v.fecha_venta) = YEAR(CURDATE()) ";
}
if($month != null){
$wherem = " AND MONTH(v.fecha_venta) = $month ";
}
if($year != null){
$wherey = " AND YEAR(v.fecha_venta) = $year ";
}
if($cliente != "vacio" && $cliente != "" ){
$wherec = " AND c.id = '".$cliente."' ";
}
if($registro != null ){
$wherer = " AND v.registro_id = '".$registro."' ";
}
if($limit != null && $limit != 'null') {
$limitCondition = "LIMIT ".$limit;
}
$sql = "SELECT null pago, null nombrePago, v.id, v.cliente_id, p.id producto_id, v.cantidad cantidad, v.total,
v.comentario, p.descripcion, c.nombre cliente, c.ape_p, c.ape_m,
c.registro, v.created_at, v.fecha_venta, c.calle, c.numero, c.cp, c.delegacion,
e.estado, p.costo_mx, p.costo_eu, p.costo_gua, c.lada, c.tel, c.colonia, v.approved, c2.nombre creado, true parent
FROM venta v
JOIN producto p ON p.id = v.producto_id AND p.status_id = 1
JOIN cliente c ON c.id = v.cliente_id AND c.status_id = 1 $wherec
JOIN cliente c2 ON c2.id = v.registro_id $wherer
JOIN estado e ON e.id = c.estado_id
WHERE v.status_id = 1 ".$venta." ". $wherem ." ". $wherey ."
ORDER BY v.created_at DESC
$limitCondition";
$query = DB::select(DB::raw($sql));
if(count($query) != null){
return $query;
}
}
}