public static function getDatatable()
{
/*number of users*/
$users = User::select('id')->active()->count();
/*consumption*/
$today = date("Y-m-d");
$id = Session::get('user_id');
$user = User::select(['state_id'])->with('state')->where('id', $id)->first();
$state = $user->state->name;
$consumption = WaterRegister::where('date', '=', $today)->where('state', '=', $state)->sum('value');
/*consumption per hour*/
$consumptionPerHour = $consumption / 60;
/*largest consumer*/
$consumers = DB::table('water_registers')->select('city', DB::raw('sum(value) as total'))->where('date', '=', $today)->where('state', '=', $state)->orderBy('total', 'desc')->groupBy('city')->get();
//manejar errores
$largestConsumer = reset($consumers);
//1er elemento, mayor
$smallestConsumer = end($consumers);
//ultimo elemento, menor
/*Total month consumption*/
$first_day = date("Y-m-01");
$last_day = date("Y-m-t");
$totalConsumption = WaterRegister::where('state', '=', $state)->whereBetween('date', [$first_day, $last_day])->sum('value');
$aux = new StdClass();
$aux->users = $users;
$aux->consumption = $consumption;
$aux->consumptionPerHour = $consumptionPerHour;
$aux->largestConsumer = $largestConsumer->city;
$aux->smallestConsumer = $smallestConsumer->city;
$aux->totalConsumption = $totalConsumption;
$data = [collect($aux)];
//hack super villa, para transformar a coleccion de datos
$datatable = Datatables::of(collect($data));
return $datatable->make(true);
}
public static function showMonthConsumptionGraph()
{
//consumo por hora
$id = Session::get('user_id');
$first_day = date("Y-m-01");
$last_day = date("Y-m-t");
$previous_first_day = date("Y-m-01", strtotime('-1 month'));
$previous_last_day = date("Y-m-t", strtotime('-1 month'));
/*consumo del mes actual*/
$currentMonthConsumption = WaterRegister::where('state', '=', 'Mendoza')->where('user_id', '=', $id)->whereBetween('date', [$first_day, $last_day])->sum('value');
/*consumo del mes anterior*/
$previousMonth = WaterRegister::where('state', '=', 'Mendoza')->where('user_id', '=', $id)->whereBetween('date', [$previous_first_day, $previous_last_day])->sum('value');
$consumption = [['month' => date('M', strtotime('-1 month')), 'value' => $previousMonth], ['month' => date('M'), 'value' => $currentMonthConsumption]];
//Google charts JSON's format
$cols = [["label" => "Mes", "type" => "string"], ["label" => "Consumo", "type" => "number"]];
$aux = [];
$param1 = [];
$param2 = [];
$param3 = [];
$param4 = [];
$cell = [];
$rows = [];
$cell['cols'] = $cols;
foreach ($consumption as $key => $value) {
$param1['v'] = $consumption[$key]['month'];
$param2['v'] = $consumption[$key]['value'];
$rows['c'] = [$param1, $param2];
$param3[] = $rows;
$cell['rows'] = $param3;
}
return response()->json($cell);
}
