/home/dvjjulio/test.istyle.mx/app/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, pe.id dad, c.contract_url
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, c.contract_url
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;
}
}
}