function getTimelineAttribute()
{
$date = new Carbon();
$date->subDays(2);
$today = Carbon::now();
// Assignments that need to be completed in 2 days
$waiting_assignments = DB::table('assignments')->select(DB::raw('id, point, created_at, due_date as date, \'reminder\' as type'))->where('due_date', '>', $date->toDateTimeString())->where('due_date', '>=', $today->toDateTimeString())->whereNotExists(function ($query) {
$query->select(DB::raw(1))->from('users_assignments')->whereRaw('users_assignments.assignment_id = assignments.id')->where('users_assignments.user_id', '=', $this->id);
});
$timeline = DB::table('users_assignments')->select(DB::raw('assignment_id, point, null, created_at as date, \'assignment\' as type'))->where('user_id', '=', $this->id)->union($waiting_assignments)->orderBy('date', 'desc')->get();
return $timeline;
}
public function GetProfilePrices($id)
{
$date = new Carbon();
$date->subDays(4);
$prices = DB::table('profile_history_prices')->where('created_at', '>', $date->toDateTimeString())->where('profile_id', $id)->get(['created_at', 'price']);
$hashtag = $this->GetProfileById($id);
if ($hashtag->created_at > $date) {
array_unshift($prices, array('price' => 0, 'created_at' => $hashtag->created_at->toDateTimeString()));
array_unshift($prices, array('price' => 0, 'created_at' => $date->toDateTimeString()));
}
return $prices;
}
/**
* Execute the command.
*
* @return void
*/
public function handle()
{
$companies = Company::all();
$parameter = array();
$report_positions = array();
foreach ($companies as $company) {
$parameter[$company->id] = array();
//Accounts
$accounts = $company->Accounts->count();
$accounts_no_vehicle = $company->Accounts()->doesntHave('Vehicles')->count();
$accounts_with_vehicle = $accounts - $accounts_no_vehicle;
$parameter[$company->id]['Accounts'] = array($accounts, $accounts_with_vehicle, $accounts_no_vehicle);
//Vehicles
$vehicles = $company->Vehicles->count();
$vehicles_with_device = $company->Vehicles()->has('Device')->count();
$vehicles_no_device = $vehicles - $vehicles_with_device;
$parameter[$company->id]['Vehicles'] = array($vehicles, $vehicles_with_device, $vehicles_no_device);
//Devices
$devices = $company->Devices()->count();
$devices_no_vehicle = $devices - $vehicles_with_device;
$parameter[$company->id]['Devices'] = array($devices, $vehicles_with_device, $devices_no_vehicle);
//Positions
$now = new Carbon();
$now->subDays(5);
$vehicles = $company->Vehicles()->has('Device')->get();
$positions = array();
foreach ($vehicles as $vehicle) {
$position = $vehicle->Positions()->orderBy('memory_index', 'desc')->first();
if ($position) {
if ($position->date < $now) {
$positions[] = (object) array('name' => $vehicle->Account->name, 'serial' => $position->serial, 'plate' => $vehicle->plate, 'date' => $position->date, 'obs' => 'Veículo não reportando há pelo menos 5 dias');
}
} else {
$positions[] = (object) array('name' => $vehicle->Account->name, 'serial' => $vehicle->Device->serial, 'plate' => $vehicle->plate, 'date' => null, 'obs' => 'Veículo não possui registros');
}
}
usort($positions, function ($a, $b) {
return $a->date > $b->date;
});
$report_positions[$company->id] = $positions;
$no_positions = count($positions);
$positions_last_week = $vehicles_with_device - $no_positions;
$parameter[$company->id]['Positions'] = array($vehicles_with_device, $positions_last_week, $no_positions);
}
Storage::put('notreporting.dat', serialize($report_positions));
Storage::put('dashboard.dat', serialize($parameter));
}
protected function calculateObservedDay(Carbon $date)
{
if ($date->dayOfWeek === Carbon::SUNDAY) {
return $date->addDays(1);
} elseif ($date->dayOfWeek === Carbon::SATURDAY) {
return $date->subDays(1);
}
return $date;
}
/**
* Restricts to dates after $days days before today.
* @param object $query
* @param integer $days
* @return object $query
*/
public function scopePast($query, $days)
{
$date = new Carbon();
$date->subDays($days);
return $query->where('date', '>=', $date);
}
/**
* @param Device $thermostat
*
* @return array
*/
public function getHeatDistribution(Device $thermostat)
{
$return = ['today' => ['night' => 0, 'morning' => 0, 'afternoon' => 0, 'evening' => 0], 'yesterday' => ['night' => 0, 'morning' => 0, 'afternoon' => 0, 'evening' => 0], 'average' => ['night' => 0, 'morning' => 0, 'afternoon' => 0, 'evening' => 0]];
// get total for today, grouped per hour
// today:
$result = $thermostat->logEntries()->onDay(new Carbon())->withLogValue('is_heating_on')->groupBy('hour')->whereNotNull('is_heating_on.value')->get([DB::Raw('date_format(log_entries.time,"%H") as `hour`'), DB::Raw('SUM(is_heating_on.value) * 5 as is_heating_on')]);
$total = $result->sum('is_heating_on');
foreach ($result as $entry) {
$hour = intval($entry->hour);
$total += $entry->is_heating_on;
if ($hour >= 0 && $hour < 6) {
$return['today']['night'] += intval($entry->is_heating_on);
}
if ($hour >= 6 && $hour < 12) {
$return['today']['morning'] += intval($entry->is_heating_on);
}
if ($hour >= 12 && $hour < 18) {
$return['today']['afternoon'] += intval($entry->is_heating_on);
}
if ($hour >= 18 && $hour <= 23) {
$return['today']['evening'] += intval($entry->is_heating_on);
}
}
// for each hour loop and group.
// get total for yesterday, grouped per hour
$result = $thermostat->logEntries()->onDay(Carbon::now()->subDay())->withLogValue('is_heating_on')->groupBy('hour')->whereNotNull('is_heating_on.value')->get([DB::Raw('date_format(log_entries.time,"%H") as `hour`'), DB::Raw('SUM(is_heating_on.value) * 5 as is_heating_on')]);
$total = $result->sum('is_heating_on');
foreach ($result as $entry) {
$hour = intval($entry->hour);
$total += $entry->is_heating_on;
if ($hour >= 0 && $hour < 6) {
$return['yesterday']['night'] += intval($entry->is_heating_on);
}
if ($hour >= 6 && $hour < 12) {
$return['yesterday']['morning'] += intval($entry->is_heating_on);
}
if ($hour >= 12 && $hour < 18) {
$return['yesterday']['afternoon'] += intval($entry->is_heating_on);
}
if ($hour >= 18 && $hour <= 23) {
$return['yesterday']['evening'] += intval($entry->is_heating_on);
}
}
// get average (using log entries AND summaries).
// log entries first:
// (summaries start after five days)
// go back five days.
$today = new Carbon();
$today->startOfDay();
$today->subDays(5);
$result = $thermostat->logEntries()->withLogValue('is_heating_on')->after($today)->groupBy('hour')->whereNotNull('is_heating_on.value')->get([DB::Raw('date_format(log_entries.time,"%H") as `hour`'), DB::Raw('SUM(is_heating_on.value) * 5 as is_heating_on')]);
$total = $result->sum('is_heating_on');
foreach ($result as $entry) {
$hour = intval($entry->hour);
$total += $entry->is_heating_on;
if ($hour >= 0 && $hour < 6) {
$return['average']['night'] += intval($entry->is_heating_on);
}
if ($hour >= 6 && $hour < 12) {
$return['average']['morning'] += intval($entry->is_heating_on);
}
if ($hour >= 12 && $hour < 18) {
$return['average']['afternoon'] += intval($entry->is_heating_on);
}
if ($hour >= 18 && $hour <= 23) {
$return['average']['evening'] += intval($entry->is_heating_on);
}
}
// then, summaries!
$result = $thermostat->summaryEntries()->withSummaryValue('is_heating_on')->before($today)->groupBy('hour')->whereNotNull('is_heating_on.value')->get([DB::Raw('date_format(summary_entries.time,"%H") as `hour`'), DB::Raw('SUM(is_heating_on.value) as is_heating_on')]);
$total = $result->sum('is_heating_on');
foreach ($result as $entry) {
$hour = intval($entry->hour);
$total += $entry->is_heating_on;
if ($hour >= 0 && $hour < 6) {
$return['average']['night'] += intval($entry->is_heating_on);
}
if ($hour >= 6 && $hour < 12) {
$return['average']['morning'] += intval($entry->is_heating_on);
}
if ($hour >= 12 && $hour < 18) {
$return['average']['afternoon'] += intval($entry->is_heating_on);
}
if ($hour >= 18 && $hour <= 23) {
$return['average']['evening'] += intval($entry->is_heating_on);
}
}
// do percentage for each
foreach ($return as $type => $entry) {
$sum = $entry['night'] + $entry['morning'] + $entry['afternoon'] + $entry['evening'];
if ($sum > 0) {
$return[$type]['night'] = round($entry['night'] / $sum * 100);
$return[$type]['morning'] = round($entry['morning'] / $sum * 100);
$return[$type]['afternoon'] = round($entry['afternoon'] / $sum * 100);
$return[$type]['evening'] = round($entry['evening'] / $sum * 100);
}
}
return $return;
}
public function getOrderedBalancesByDate(Account $acc, Carbon $date)
{
$end = $date->copy();
$start = $date->subDays(7);
return $this->createQueryBuilder('ab')->leftJoin('ab.account', 'acc')->where('acc = :account')->andWhere('ab.created_at >= :start')->andWhere('ab.created_at <= :end')->addOrderBy('ab.created_at', 'ASC')->setParameters(['account' => $acc, 'start' => $start, 'end' => $end])->getQuery()->getResult();
}
public function showLast($id, Request $request)
{
$pag = $request->input('pag');
// dd($request);
$new = array('dataini' => $request->input('dataini'), 'datafin' => $request->input('datafin'), 'search' => $request->input('search'), 'page' => $request->input('page'));
$request->replace($new);
$vehicle = Vehicle::find($id);
$now = new Carbon();
$now->subDays(5);
if ($request->input('search')) {
$filter = \DataFilter::source($vehicle->Positions());
} else {
$filter = \DataFilter::source($vehicle->Positions()->limit(10));
}
// $filter = \DataFilter::source($vehicle->Positions());
$filter->add('dataini', 'Data Inicial', 'datetime')->format('d/m/Y H:i:s')->scope(function ($query, $value) {
$test = (bool) strtotime($value);
if ($test) {
return $query->whereRaw("date >= ?", array($value));
} else {
return $query;
}
});
$filter->add('datafin', 'Data Final', 'datetime')->format('d/m/Y H:i:s')->scope(function ($query, $value) {
$test = (bool) strtotime($value);
if ($test) {
return $query->whereRaw("date <= ?", array($value));
} else {
return $query;
}
});
$filter->submit('Buscar');
$filter->reset('Limpar');
$filter->add('pag', '', 'select')->options(array('' => 'Itens por Página', 10 => '10', 20 => '20', 30 => '30', 40 => '40', 50 => '50', 100 => '100', 200 => '200', 500 => '500', 1000 => '1000'));
$grid = \DataGrid::source($filter);
$grid->attributes(array("class" => "table table-striped"));
$grid->add('<input type="checkbox" name="ids[]" value="{{ $id }}" onclick="checkSelected()">', '<input type="checkbox" name="todos" id="todos" onclick="selectTodos()">');
$grid->add('date|strtotime|date[d/m/Y H:i:s]', 'Data', true);
$grid->add('<span id="{{$id}}" class="address" geo-lat="{{ $latitude }}" geo-lng="{{ $longitude }}"></span>', 'Endereço');
$grid->add('<i class="fa fa-lg fa-circle {{ $ignition == 1 ? \'on\' : \'off\' }}">', 'Ignição');
$grid->add('speed', 'Velocidade');
$grid->add('<a class="btn btn-success btn-xs" title="Ver no Mapa" href="/positions/showMap/{{$id}}"><i class="fa fa-lg fa-map-marker"></i></a><a class="btn btn-danger btn-xs" title="Informações" href="/positions/showInfo/{{$id}}"><i class="fa fa-lg fa-info"></i></a><button type="button" class="btn btn-info btn-xs" title="Buscar Endereço" onclick="searchAddr(\'{{$id}}\')"><i class="fa fa-lg fa-search"></i></button>', 'Ações', true);
$grid->orderBy('memory_index', 'desc');
if ($pag) {
$grid->paginate($pag);
}
return view('positions::last', compact('filter', 'grid', 'vehicle'));
}
/**
* Summarize the report entries from 5 days ago, to comply with Google Nest's
* data retention rules.
*/
public function summarize()
{
// go back five days.
$today = new Carbon();
$today->startOfDay();
$today->subDays(5);
echo "<pre>\n";
echo "Starting on " . $today->format('d M Y') . "\n";
// these have our interest.
$summaries = [];
$logToSave = ['has_leaf', 'is_heating_on'];
// indicates that these entries 'add minutes', ie. each entry is +5 minutes.
$addsMinutes = ['has_leaf', 'is_heating_on'];
// get all log entries
$logEntries = $this->_helper->getLogEntriesForDay($today);
// debug info:
echo 'Found ' . $logEntries->count() . ' log entries to summarize' . "\n";
// loop all:
/** @var Report $logEntry */
foreach ($logEntries as $logEntry) {
// get current hour.
$hour = intval($logEntry->time->format('H'));
// summary entry:
if (!isset($summaries[$hour])) {
// make time:
$time = clone $logEntry->time;
$time->hour = $hour;
$time->minute = 0;
$time->second = 0;
// set initial variables:
$summaries[$hour]['time'] = $time;
$summaries[$hour]['device'] = intval($logEntry->device_id);
$summaries[$hour]['entries'] = [];
}
// get relevant log entries:
$values = $this->_helper->getLogValuesByEntry($logEntry, $logToSave);
// loop them
/** @var LogValue $value */
foreach ($values as $value) {
$name = $value->name;
// some are booleans, they add to a total time:
if (in_array($name, $addsMinutes)) {
$value = intval($value->value);
if ($value == 1) {
$summaries[$hour]['entries'][$name] = isset($summaries[$hour]['entries'][$name]) ? $summaries[$hour]['entries'][$name] + 5 : 5;
}
}
// others are numbers and become an average (like temp).
}
}
// save all summaries built:
foreach ($summaries as $summary) {
$entry = $this->_helper->getSummaryEntry($summary['device'], $summary['time']);
foreach ($summary['entries'] as $name => $value) {
$value = $this->_helper->createSummaryValue($entry, $name, $value);
if ($value === false) {
echo 'Could not save summary value for time ' . $summary['time']->format('d M Y H:i:s') . ' because value with name "' . $name . '" is already saved for entry #' . $entry->id . "!\n";
}
}
echo "Saved " . $entry->summaryValues()->count() . " summarized fields (+values) for time " . $entry->time->format('d M Y H:i:s') . ".\n";
}
echo "Done summarizing.\n";
}
public function postArchivo()
{
//upload del archivo
$file = Input::file("archivo");
if (Input::hasFile('archivo')) {
$file->move("public/excel", $file->getClientOriginalName());
}
Excel::load('public/excel/' . $file->getClientOriginalName(), function ($archivo) {
$resultados = $archivo->get();
Cache::flush();
$comisiones = VistaComision::where('pagada', 0)->where('cancelada', 0)->OrderBy('id')->get();
$fecha_fin = new Carbon('last sunday');
$date = new Carbon('last sunday');
$fecha_inicio = $date->subDays(6);
//cuento cuantos periodos hay con las mismas fechas de inicio y fin, para determinar si debo crear uno nuevo
$ultimo_periodo_comision = PeriodoComision::orderBy('id', 'desc')->first();
$periodo_comision_count = PeriodoComision::where('fecha_inicio', '=', $fecha_inicio)->where('fecha_fin', '=', $fecha_fin)->count();
if ($periodo_comision_count == 0) {
$new_periodo = new PeriodoComision();
$new_periodo->fecha_inicio = $fecha_inicio;
$new_periodo->fecha_fin = $fecha_fin;
$new_periodo->folio = $ultimo_periodo_comision->folio + 1;
$new_periodo->save();
$periodo_actual = $new_periodo;
} else {
$periodo_actual = $ultimo_periodo_comision;
}
//estas son las que se pagan
foreach ($comisiones as $comision_r => $comision) {
foreach ($resultados as $resultado => $excel) {
if ($excel->pkc == $comision->id) {
$plan_pago = PlanPagoVenta::select('plan_pago_venta.*', 'plan_pago.porcentaje_anticipo')->leftJoin('plan_pago', 'plan_pago_venta.plan_pago_id', '=', 'plan_pago.id')->where('venta_id', '=', $comision->id)->where('plan_pago_venta.activo', 1)->firstorFail();
$venta = VistaComision::find($comision->id);
//lo que al vendedor se le ha dado de esa comisión
$pagado = $venta->pagado;
$tipo_venta = $venta->nombre_corto;
$porcentaje_anticipo = $plan_pago->porcentaje_anticipo;
switch ($porcentaje_anticipo) {
case 0:
$porcentaje = 0.3;
break;
case $porcentaje_anticipo <= 30:
$porcentaje = 0.3;
break;
case $porcentaje_anticipo == 0 and $plan_pago->numero_pagos == 2:
$porcentaje = 0.5;
break;
default:
$porcentaje = $porcentaje_anticipo / 100;
break;
}
$anticipo = intval($venta->total * $porcentaje);
//lo que el cliente debe de dar mensualmente
$pago_regular = $plan_pago->pago_regular;
//calcula pago de comision por pago regular
$comision_mensual = $comision->total_comisionable / $comision->numero_pagos;
//lo que el cliente pagó en los recibos de la semana
$pago_semanal = $excel->importe;
if ($tipo_venta == "V") {
//aplica regla de 3 para pagos superiores al pago regular solo ventas de contrato
$comision_semanal = $pago_semanal * $comision_mensual / $pago_regular;
} else {
$comision_semanal = $comision_mensual;
}
$diferencia_pagos = $pago_regular - $pago_semanal;
$resto_comision = $comision->por_pagar;
if ($resto_comision > 0) {
/*---------CONTRATOS----------*/
switch ($pago_semanal) {
case $pago_semanal < $pago_regular and $diferencia_pagos >= 5 or $pagado == 0 and $pago_semanal < $anticipo and $diferencia_pagos >= 5:
$abono_comision = 0;
break;
case $pago_semanal >= $anticipo and $tipo_venta == "V":
$abono_comision = round($resto_comision, 2);
break;
case $comision_semanal + $pagado > $resto_comision and $resto_comision < $comision_mensual:
$abono_comision = round($resto_comision, 2);
break;
case $comision_semanal >= $resto_comision and $pago_semanal > $pago_regular:
$abono_comision = round($resto_comision, 2);
break;
case $pago_semanal <= $pago_regular and $diferencia_pagos <= 5 and $pagado == 0 and $pago_semanal >= $anticipo and $resto_comision < $comision_mensual:
$abono_comision = round($resto_comision, 2);
break;
case $comision_semanal >= $resto_comision and $pago_semanal >= $pago_regular and $pagado == 0:
$abono_comision = round($resto_comision, 2);
break;
case $pago_semanal == $pago_regular and $pagado == 0:
$abono_comision = round($comision_semanal, 2);
break;
default:
$abono_comision = round($comision_semanal, 2);
break;
}
/*---------FIN CONTRATOS----------*/
} else {
$abono_comision = 'ya pagado';
}
if ($comision->pagado > 0) {
$pagados = ($comision->pagado + $abono_comision) * $comision->numero_pagos / $comision->total_comisionable;
# code...
} else {
$pagados = 1;
}
$meses_pagados = round($pagados, 0, PHP_ROUND_HALF_UP);
$observaciones = $meses_pagados . ' de ' . $comision->numero_pagos;
/*--------GUARDA REGISTROS-------------*/
//determina si el abono cae entre las fechas de inicio y fin de cuando se creó la lista de comisiones, para evitar duplicar el pago al volver a subir el mismo archivo
if ($excel->pago->between($fecha_inicio, $fecha_fin) == 1) {
//aqui se van a crear los abonos
$abono_count = AbonoComision::where('periodo_comision_id', '=', $periodo_actual->id)->where('comision_id', '=', $excel->pkc)->count();
if ($abono_count == 0) {
//guarda el pago
$new_abono = new AbonoComision();
$new_abono->periodo_comision_id = $ultimo_periodo_comision->id + 1;
$new_abono->comision_id = $excel->pkc;
$new_abono->monto = $abono_comision;
$new_abono->fecha = $fecha_fin->toDateString();
$new_abono->asesor_id = $excel->pk_vendedor;
$new_abono->save();
}
}
/*---------------FIN GUARDA REGISTROS------------*/
/*---------ECHO ---------*/
/*echo $excel->pago->between($fecha_inicio, $fecha_fin)','.$comision->asesor_id.','.$comision->porcentaje.','.$observaciones.'<br>';*/
/*echo $excel->pkc.'---'. $abono_comision.'<br>';/*.' pago regular '.$plan_pago->pago_regular.' recibo '.
$excel->importe.'pago mensual = '.$pago_mensual..' --- '.$observaciones.' ---% '.$comision->porcentaje.' -- '.$abono_comision/*' por pagar '.$comision->por_pagar.'<br>' */
/*--------FIN ECHO ---------*/
}
}
}
/*foreach($resultados as $resultado => $excel){
if ($excel->compra >= $fecha_inicio) {
$excel->pkc. 'nueva'.'<br>';
}
}*/
})->all();
$ultimo_periodo_comision = PeriodoComision::orderBy('id', 'desc')->first();
return Redirect::action('ComisionControlador@getAbonos', $ultimo_periodo_comision->id);
}
