KOKINIO - MANAGER

Edit File: 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));
    }
}