KOKINIO - MANAGER

Edit File: 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 IN (".$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, v.factura_api_id
              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";

//   $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)*(n.comision_mx)) 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 = 1 
    //           WHERE c.patrocinador IN ($id) AND c.status_id = 1 
    //           GROUP BY c.id, c.nombre, apellidos, c.registro, c.patrocinador, c.tel, c.lada, v.created_at, 
    //           n.nivel, co.pagado, pr.comisiona, pa.active_id, pai.pais, n.comision_mx, v.fecha_venta, pa.nombre, pa.ape_p
    //           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 = " WHERE status_id = 1 ";

try {
        if($year != null){
          $group = " MONTH(v.fecha_venta), YEAR(v.fecha_venta) ";
          $where .= " AND 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;
        }
      } catch (\Exception $e) {
        // var_dump($e->getMessage());
      }

return null;
    }

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;
      }
      // echo $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";

$sql = "SELECT GROUP_CONCAT(CONCAT('\"', v.id, '\"')) id, null pago, null nombrePago, GROUP_CONCAT(CONCAT(v.cantidad, ' ', p.descripcion) SEPARATOR ' | ') descripcion,
                    SUM(v.cantidad) cantidad, SUM(v.total) total, GROUP_CONCAT(v.comentario SEPARATOR ' | ') comentario, 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, SUM(p.costo_mx),
                    SUM(p.costo_eu), SUM(p.costo_gua), c.lada, c.tel, c.colonia, v.approved, c2.nombre creado, true parent, i.status, i.tipo_factura
              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
              LEFT JOIN factura i ON i.factura_api_id = v.factura_api_id
              WHERE  v.status_id = 1 ".$venta." ". $wherem ." ". $wherey ."
              GROUP BY pago, nombrePago, 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, c.lada, c.tel, c.colonia, v.approved, creado, parent, i.status, i.tipo_factura
              ORDER BY v.created_at DESC
              $limitCondition";

$query = DB::select(DB::raw($sql));
       if(count($query) != null){
         return $query;
       }
     }

}