Skip to content

Instantly share code, notes, and snippets.

@damianoporta
Created March 3, 2016 15:36
Show Gist options
  • Save damianoporta/ab1dacf4a7eea5037725 to your computer and use it in GitHub Desktop.
Save damianoporta/ab1dacf4a7eea5037725 to your computer and use it in GitHub Desktop.
<?php
/**
* Conteggia le view alla scheda aziendale
*
* Opzioni:
*
* - companies_ids: (int) ID dell'azienda da filtrare
* - start_date: (datetime) Data di inizio
* - end_date: (datetime) Data di fine
* - interval: (string) Intervallo (today)
* - mobile: (int) 1 = si 0 = no
* - source: (string|int) 'external'
*
* @param Query $query
* @param array $options
* @return Query
*/
public function findCompaniesView(Query $query, array $options)
{
// Se non è stata passata alcuna data di fine imposto quella attuale
if (empty($options['end_date'])) {
$options['end_date'] = 'NOW()';
}
$query
->select(['total_times' => $query->func()->sum('Trackings.times')])
->where([
'Trackings.category' => 'Companies',
'Trackings.action' => 'view',
'Trackings.label' => 'company_id',
'Trackings.value' => $options['companies_ids'],
])
->group('Trackings.value');
// Controllo se devo raggruppare per intervallo
if (!empty($options['interval'])) {
$query->group('DATE(Trackings.created)', true);
}
// Controllo se devo filtrare per mobile
if (!empty($options['mobile'])) {
if ($options['mobile'] == 1) {
$query->where(['Trackings.mobile' => 1]);
} else {
$query->where(['Trackings.mobile' => 0]);
}
}
// Controllo se devo filtrare per sorgente
if (!empty($options['source'])) {
// Controllo se devo recuperare tutti i sorgenti esterni (jobrapido, indeed)
// esluso tuoagente
if (is_string($options['source']) && $options['source'] == 'external') {
$query->where(function ($exp, $q) {
return $exp->gt('Trackings.source_id', 1);
});
}
// Controllo ed eventualmente trasformo in un array
if (!is_array($options['source'])) {
$options['source'] = [$options['source']];
}
$query->where(['Trackings.source_id IN' => $options['source']]);
}
if (!empty($options['start_date'])) {
$query->where([function($exp) use ($options) {
return $exp->between('Trackings.created', $options['start_date'], $options['end_date'], 'date');
}]);
}
return $query;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment