/**
* counts how many ads have each location
* @return array
*/
public static function get_location_count()
{
//name used in the cache for storage
$cache_name = 'get_location_count';
if (($locs_count = Core::cache($cache_name)) === NULL) {
$expr_date = is_numeric(core::config('advertisement.expire_date')) ? core::config('advertisement.expire_date') : 0;
$db_prefix = Database::instance('default')->table_prefix();
//get the locations that have ads id_location->num ads
$count_ads = DB::select('l.id_location', array(DB::expr('COUNT("a.id_ad")'), 'count'))->from(array('locations', 'l'))->join(array('ads', 'a'))->using('id_location')->where(DB::expr('IF(' . $expr_date . ' <> 0, DATE_ADD( published, INTERVAL ' . $expr_date . ' DAY), DATE_ADD( NOW(), INTERVAL 1 DAY))'), '>', Date::unix2mysql())->where('a.status', '=', Model_Ad::STATUS_PUBLISHED)->group_by('l.id_location')->order_by('l.order', 'asc')->cached()->execute();
$count_ads = $count_ads->as_array('id_location');
//getting the count of ads into the parents
$parents_count = array();
foreach ($count_ads as $count_ad) {
$id_location = $count_ad['id_location'];
$count = $count_ad['count'];
//adding himself if doesnt exists
if (!isset($parents_count[$id_location])) {
$parents_count[$id_location] = $count_ad;
$parents_count[$id_location]['has_siblings'] = FALSE;
}
$location = new Model_Location($id_location);
//for each parent of this location add the count
$parents_ids = $location->get_parents_ids();
if (count($parents_ids) > 0) {
foreach ($parents_ids as $id) {
if (isset($parents_count[$id])) {
$parents_count[$id]['count'] += $count_ads[$location->id_location]['count'];
} else {
$parents_count[$id]['count'] = $count_ads[$location->id_location]['count'];
}
$parents_count[$id]['has_siblings'] = TRUE;
}
}
}
//get all the locations with level 0 and 1
$locations = new self();
$locations = $locations->where('id_location', '!=', 1)->where('parent_deep', 'IN', array(0, 1))->order_by('order', 'asc')->cached()->find_all();
//generating the array
$locs_count = array();
foreach ($locations as $location) {
$has_siblings = isset($parents_count[$location->id_location]) ? $parents_count[$location->id_location]['has_siblings'] : FALSE;
//they may not have counted the siblings since the count was 0 but he actually has siblings...
if ($has_siblings === FALSE and $location->has_siblings()) {
$has_siblings = TRUE;
}
$locs_count[$location->id_location] = array('id_location' => $location->id_location, 'seoname' => $location->seoname, 'name' => $location->name, 'id_location_parent' => $location->id_location_parent, 'parent_deep' => $location->parent_deep, 'order' => $location->order, 'has_siblings' => $has_siblings, 'count' => isset($parents_count[$location->id_location]) ? $parents_count[$location->id_location]['count'] : 0);
//counting the ads the parent have
}
//cache the result is expensive!
Core::cache($cache_name, $locs_count);
}
return $locs_count;
}
