/home/dvjjulio/softtrash/app/CombustibleOrden.php
<?php
namespace Trash;
use Illuminate\Database\Eloquent\Model;
use DB;
class CombustibleOrden extends Model
{
//
protected $table = "combustible_orden";
public $timestamps = false;
public function getCombustibleOrden($year = null, $month = null) {
$where = "";
if($month != null && $month != "null"){
$where = " AND MONTH(o.fecha_inicio) = $month ";
}
if(($year == null && $year = "null") || $year == 0){
$year = date('Y');
}
$sql = "SELECT WEEK(o.fecha_inicio) semana, MONTH(o.fecha_inicio) mes, YEAR(o.fecha_inicio) year, o.fecha_inicio, u.nombre, c2.nombre_comercial, o.km kilometraje,
c.placas, co.*
FROM orden o
JOIN combustible_orden co ON co.orden_id = o.id
JOIN camion c ON c.id = o.camion_id
JOIN user u ON u.id = c.user_id and u.status_id = 1
JOIN cliente c2 ON c2.id = o.cliente_id AND c2.status_id = 1
WHERE YEAR(o.fecha_inicio) = '$year' $where AND co.status_id = 1
GROUP BY fecha_inicio, co.orden_id
ORDER BY o.fecha_inicio, c2.nombre_comercial, u.nombre, co.id";
$data = DB::select(DB::raw($sql));
if (count($data) != null){
return $data;
}
}
public function getCombustibleOrdenYear($year = null) {
if(($year == null && $year = "null") || $year == 0){
$year = date('Y');
}
$sql = "SELECT MONTH(o.fecha_inicio) mes, SUM(co.total) total, SUM(co.litros) litros, COUNT(co.nota) notas
FROM orden o
JOIN combustible_orden co ON co.orden_id = o.id
JOIN cliente c2 ON c2.id = o.cliente_id AND c2.status_id = 1
WHERE YEAR(o.fecha_inicio) = '$year' AND co.status_id = 1
GROUP BY MONTH(o.fecha_inicio)
ORDER BY MONTH(o.fecha_inicio)";
$data = DB::select(DB::raw($sql));
if (count($data) != null){
return $data;
}
}
public function getLastKm($ordenId) {
$sql = "SELECT o.id, o.orden_no, o.camion_id, o.km , co.litros
FROM orden o
JOIN combustible_orden co ON co.orden_id = o.id AND co.status_id = 1
JOIN cliente c ON c.id = o.cliente_id AND c.status_id = 1
JOIN giro_empresa ge ON ge.id = c.giro_id AND ge.giro ='Combustible'
WHERE camion_id = (select camion_id from orden o where id = $ordenId )
ORDER BY fecha_inicio DESC LIMIT 1";
$data = DB::select(DB::raw($sql));
if (count($data) != null){
return $data;
}
}
public function updateYield($orderId, $rendimiento){
try{
\DB::beginTransaction();
echo $sql="UPDATE combustible_orden SET rendimiento = $rendimiento WHERE orden_id = $orderId ";
$data = \DB::update( \DB::raw( $sql ));
}catch ( \Exception $e ){
\DB::rollback();
return \Response::json(array('status'=>false));
}
\DB::commit();
return \Response::json(array('status'=>true));
}
}