KOKINIO - MANAGER

Edit File: Cliente.php

<?php

namespace App;

use Illuminate\Foundation\Auth\User as Authenticatable;
use DB;

class Cliente extends Authenticatable
{
    //
    protected $table = 'cliente';
    public $incrementing = false;

public function Clientes($id = null, $cliente = null) {
      $where = "";
      $wherec = "";
      if($id != null && $id != 'null'){
        $where = " AND c.id = '".$id."'";
      }
      if($cliente != null){
        $wherec = " AND c.cliente_id = '".$cliente."'";
      }
        $sql = "SELECT c.id, c.registro, c.nombre, c.ape_p, c.ape_m, c.ine, c.rfc, c.sexo, c.fecha_nac,
                			  c.edo_civil, c.profesion, c.calle, c.numero, c.num_interior, c.colonia, c.delegacion, c.cp,
                        c.lada, c.tel, c.email, c.whatsapp, c.password, c.status_id, c.estado_id, e.estado, r.rol,
                        c.created_at, c.patrocinador patrocinador_id, c.rol_id, c2.nombre patrocinador, c2.ape_p patrocinador_p,
                        c2.ape_m patrocinador_m, c.nivel, p.pais, c.pais_id, c3.nombre created_by, c.active_id, pe.categoria
                FROM cliente c
                JOIN estado e ON e.id = c.estado_id
                JOIN pais p ON p.id = c.pais_id
                JOIN rol r ON r.id  = c.rol_id
                JOIN cliente c3 ON c3.id = c.cliente_id $wherec
                LEFT JOIN cliente c2 ON c2.id = c.patrocinador
                LEFT JOIN premio_cliente pc ON pc.cliente_id = c.id
                LEFT JOIN premio pe ON pe.id = pc.premio_id
                WHERE c.status_id = 1 $where 
                ORDER BY c.active_id DESC, c.nombre, c.ape_p
                LIMIT 30
                ";
       $query = DB::select(DB::raw($sql));
       if(count($query) != null){
         return $query;
       }
     }

public function ClientesCount($id = null, $cliente = null){
      $where = "";
      $wherec = "";
      if($id != null && $id != 'null'){
        $where = " AND c.id = '".$id."'";
      }
      if($cliente != null){
        $wherec = " AND c.cliente_id = '".$cliente."'";
      }
         $sql = "SELECT count(1) total
                FROM cliente c
                JOIN estado e ON e.id = c.estado_id
                JOIN pais p ON p.id = c.pais_id
                JOIN rol r ON r.id  = c.rol_id
                JOIN cliente c3 ON c3.id = c.cliente_id $wherec
                LEFT JOIN cliente c2 ON c2.id = c.patrocinador
                WHERE c.status_id = 1 $where 
                ";
       $query = DB::select(DB::raw($sql));
       if(count($query) != null){
         return $query;
       }
     }
     public function ClientesWithLimit($id = null, $cliente = null, $limit = null){
      $where = "";
      $wherec = "";
      $limitCondition = " LIMIT 0, 100 ";
      if($id != null && $id != 'null'){
        $where = " AND c.id = '".$id."'";
      }
      if($cliente != null){
        $wherec = " AND c.cliente_id = '".$cliente."'";
      }
      if($limit != null && $limit != 'null') {
        $limitCondition = "LIMIT ".$limit;
      }
        $sql = "SELECT c.id, c.registro, c.nombre, c.ape_p, c.ape_m, c.ine, c.rfc, c.sexo, c.fecha_nac,
                			  c.edo_civil, c.profesion, c.calle, c.numero, c.num_interior, c.colonia, c.delegacion, c.cp,
                        c.lada, c.tel, c.email, c.whatsapp, c.password, c.status_id, c.estado_id, e.estado, r.rol,
                        c.created_at, c.patrocinador patrocinador_id, c.rol_id, c2.nombre patrocinador, c2.ape_p patrocinador_p,
                        c2.ape_m patrocinador_m, c.nivel, p.pais, c.pais_id, c3.nombre created_by, c.active_id
                FROM cliente c
                JOIN estado e ON e.id = c.estado_id
                JOIN pais p ON p.id = c.pais_id
                JOIN rol r ON r.id  = c.rol_id
                JOIN cliente c3 ON c3.id = c.cliente_id $wherec
                LEFT JOIN cliente c2 ON c2.id = c.patrocinador
                WHERE c.status_id = 1 $where 
                ORDER BY c.active_id DESC, c.nombre, c.ape_p
                $limitCondition";
       $query = DB::select(DB::raw($sql));
       if(count($query) != null){
         return $query;
       }
     }
     public function clientesToDeactivate() {
        $sql = "  UPDATE cliente set active_id = 0 where id not in (
                  SELECT cliente_id
                  FROM comision
                  WHERE ano = YEAR(NOW()) AND mes = MONTH(NOW() - interval 1 month) 
                        and status_id = 1 and comision_3meses is not null and comision_3meses != 0
                ) AND status_id = 1 and active_id = 1;
                ";
       $query = DB::select(DB::raw($sql));
       if(count($query) != null){
         return $query;
       }
     }
     public function clientesToActivate() {
      $sql = "UPDATE cliente set active_id = 1 where id in (
              SELECT cliente_id
              FROM comision
              WHERE ano = YEAR(NOW()) AND mes = MONTH(NOW() - interval 1 month) 
                    and status_id = 1 and comision_3meses is not null and comision_3meses != 0
            ) AND status_id = 1 and active_id = 1;;
              ";
     $query = DB::select(DB::raw($sql));
     if(count($query) != null){
       return $query;
     }
   }
   public function ClientsSearchBar($client){
      $sql = "SELECT id, registro, nombre, ape_p, ape_m
              FROM cliente
              WHERE status_id = 1 AND (
                CONCAT(nombre, ' ' ,ape_p) like '%$client%'
                OR
                CONCAT(nombre, ' ' ,ape_m) like '%$client%'
                OR
                CONCAT(ape_p , ' ' ,nombre) like '%$client%'
                OR
                CONCAT(ape_m , ' ' ,nombre) like '%$client%'
                OR
                CONCAT(nombre , ' ' ,ape_p, ' ', ape_m) like '%$client%'
                OR
                CONCAT_WS('', registro, nombre, ape_p, ape_m) like '%$client%'
              )
              LIMIT 0, 100";
     $query = DB::select(DB::raw($sql));
     if(count($query) != null){
       return $query;
     }
   }
}