/home/dvjjulio/softtrash/app/Peso.php
<?php
namespace Trash;
use Illuminate\Database\Eloquent\Model;
use Trash\Clientes;
use DB;
class Peso extends Model
{
//
protected $table = 'peso_cliente';
public function peso_cliente(){
$sql = "SELECT p.id peso_id, c.id, c.nombre_comercial, p.mes_id, p.year,
CASE WHEN (p.mes_id) = 1 THEN 'Enero'
WHEN (p.mes_id) = 2 THEN 'Febrero'
WHEN (p.mes_id) = 3 THEN 'Marzo'
WHEN (p.mes_id) = 4 THEN 'Abril'
WHEN (p.mes_id) = 5 THEN 'Mayo'
WHEN (p.mes_id) = 6 THEN 'Junio'
WHEN (p.mes_id) = 7 THEN 'Julio'
WHEN (p.mes_id) = 8 THEN 'Agosto'
WHEN (p.mes_id) = 9 THEN 'Septiembre'
WHEN (p.mes_id) = 10 THEN 'Octubre'
WHEN (p.mes_id) = 11 THEN 'Noviembre'
WHEN (p.mes_id) = 12 THEN 'Diciembre'
ELSE 'Error' END AS mes
, co.cuota, u.unidad, p.peso, p.id peso_id
FROM cliente c
JOIN peso_cliente p ON c.id = p.cliente_id
JOIN cuota co ON co.id = c.cuota_id
JOIN unidad u ON u.id = c.unidad_id
WHERE p.mes_id = MONTH(CURDATE()) AND c.status_id = 1 AND year = YEAR(CURDATE())
ORDER BY c.nombre_comercial, p.year, p.mes_id";
$data = \DB::select( \DB::raw( $sql ) );
if(count($data)!=null){
return response()->json(array('status'=>true, 'data'=>$data));
}else{
return response()->json(array('status'=>false));
}
}
public function update_peso($data){
$where_cliente = "";
if($data['mes'] != 0){
$where_cliente .= " mes_id = ".$data['mes']." AND ";
}
if($data['year'] != 0){
$where_cliente .= " year = ".$data['year']." AND ";
}
if($data['cuota'] != 0){
$clientes = $this->getClientesCuota($data);
$where_cliente.=" cliente_id in (".$this->get_Array($clientes).") OR ";
}
if($data['unidad'] != 0){
$clientes = $this->getClientesUnidad($data);
$where_cliente.=" cliente_id in (". $this->get_Array($clientes).") OR ";
}
if($data['cliente'] != 0){
$where_cliente.= " cliente_id = ".$data['cliente']." OR ";
}
$where_cliente = substr($where_cliente,0,-4);
if($data['id']== 1){
return $this->actualiza_peso($data, $where_cliente);
}else {
return $this->filter_peso($where_cliente);
}
}
public function filter_peso($where_cliente){
$sql="SELECT c.id, c.nombre_comercial, p.mes_id, p.year,
CASE WHEN (p.mes_id) = 1 THEN 'Enero'
WHEN (p.mes_id) = 2 THEN 'Febrero'
WHEN (p.mes_id) = 3 THEN 'Marzo'
WHEN (p.mes_id) = 4 THEN 'Abril'
WHEN (p.mes_id) = 5 THEN 'Mayo'
WHEN (p.mes_id) = 6 THEN 'Junio'
WHEN (p.mes_id) = 7 THEN 'Julio'
WHEN (p.mes_id) = 8 THEN 'Agosto'
WHEN (p.mes_id) = 9 THEN 'Septiembre'
WHEN (p.mes_id) = 10 THEN 'Octubre'
WHEN (p.mes_id) = 11 THEN 'Noviembre'
WHEN (p.mes_id) = 12 THEN 'Diciembre'
ELSE 'Error' END AS mes
, co.cuota, u.unidad, p.peso, p.id peso_id
FROM cliente c
JOIN peso_cliente p ON c.id = p.cliente_id AND c.status_id = 1
JOIN cuota co ON co.id = c.cuota_id
JOIN unidad u ON u.id = c.unidad_id
WHERE " .$where_cliente."
Order by c.nombre_comercial, p.year, p.mes_id
";
$data = \DB::select( \DB::raw( $sql ) );
return $data;
}
public function actualiza_peso($data, $where_cliente){
$sql="UPDATE peso_cliente SET peso = ".$data['peso']." WHERE ".$where_cliente;
$data = DB::update( $sql );
if($data!=0){
return response()->json(array('status'=>true, 'message'=>'Pesos Actualizados Correctamente'));
}else{
return response()->json(array('status'=>false,'message'=>'No hubo Actualizacion de Peso'));
}
}
public function getClientesCuota($data){
$clientes = Clientes::where('cuota_id','=',$data['cuota'])->get();
return $this->get_Array($clientes);
}
public function getClientesUnidad($data){
return Clientes::where('unidad_id','=',$data['unidad'])->get();
}
public function get_Array($array){
$obj = "";
foreach ($array as $key => $value) {
$obj.= $value->id.',';
}
$obj = substr($obj,0,-1);
return $obj;
}
public function modifyWeigh($peso,$id){
$sql="UPDATE peso_cliente SET peso = ".$peso." WHERE id =". $id;
$data = DB::update( $sql );
if($data!=0){
return response()->json(array('status'=>true, 'message'=>'Pesos Actualizados Correctamente'));
}else{
return response()->json(array('status'=>false,'message'=>'No hubo Actualizacion de Peso'));
}
}
public function years_months_weigth($year = null){
$monthGroup = "";
$monthWHere = "";
if($year != null){
$monthGroup = ", mes_id";
$monthWHere = " AND year = ".$year;
}
$sql = "SELECT year, mes_id
FROM peso_cliente
WHERE year != 0 $monthWHere
GROUP BY year $monthGroup";
$data = DB::select( DB::raw( $sql ) );
return $data;
}
public function getPesosMonth($year_base, $month_base){
$sql = "SELECT c.id, pc.peso
FROM cliente c
LEFT JOIN peso_cliente pc ON pc.cliente_id = c.id AND year = $year_base and mes_id = $month_base
WHERE c.status_id = 1";
$data = DB::select( DB::raw( $sql ) );
return $data;
}
public function verifyPesosMonth($year_dstinity, $month_destinity, $cliente){
$sql = "SELECT *
FROM peso_cliente
WHERE year = $year_dstinity and mes_id = $month_destinity AND cliente_id = $cliente";
$data = DB::select( DB::raw( $sql ) );
return $data;
}
}