/**
* shortcut fot DateTime::createFromFormat
* @param string $date
* @param string $input_format
* @param string $output_format
* @return mixed
*/
public static function from_format($date, $input_format = 'd/m/yy', $output_format = 'm-d-Y')
{
if ($date === NULL) {
$date = time();
}
$datetime = DateTime::createFromFormat($input_format, $date);
switch ($output_format) {
case 'unix':
return date::unix2mysql($datetime->getTimestamp());
break;
default:
return $datetime->format($output_format);
break;
}
}
public function action_index()
{
//if not god redirect him to the normal profile page
if (Auth::instance()->get_user()->id_role != Model_Role::ROLE_ADMIN) {
HTTP::redirect(Route::url('oc-panel', array('controller' => 'myads', 'action' => 'index')));
}
Core::ocacu();
$this->template->title = __('Welcome');
Breadcrumbs::add(Breadcrumb::factory()->set_title($this->template->title));
$this->template->bind('content', $content);
$content = View::factory('oc-panel/home');
/////////////////////RSS////////////////////////////////
//try to get the RSS from the cache
$rss_url = 'http://feeds.feedburner.com/OpenClassifieds';
$content->rss = Feed::parse($rss_url, 10);
/////////////////////ADS////////////////////////////////
$content->res = new Model_Ad();
//filter ads by status
$content->res = $content->res->where('status', '=', Core::get('status', Model_Ad::STATUS_PUBLISHED));
$content->res = $content->res->order_by('created', 'desc')->limit(10)->find_all();
/////////////////////STATS////////////////////////////////
//Getting the dates and range
$from_date = Core::post('from_date', strtotime('-1 month'));
$to_date = Core::post('to_date', time());
//we assure is a proper time stamp if not we transform it
if (is_string($from_date) === TRUE) {
$from_date = strtotime($from_date);
}
if (is_string($to_date) === TRUE) {
$to_date = strtotime($to_date);
}
//mysql formated dates
$my_from_date = Date::unix2mysql($from_date);
$my_to_date = Date::unix2mysql($to_date);
//dates range we are filtering
$dates = Date::range($from_date, $to_date, '+1 day', 'Y-m-d', array('date' => 0, 'count' => 0), 'date');
//dates displayed in the form
$content->from_date = date('Y-m-d', $from_date);
$content->to_date = date('Y-m-d', $to_date);
//ads published last XX days
$query = DB::select(DB::expr('DATE(published) date'))->select(DB::expr('COUNT(id_ad) count'))->from('ads')->where('status', '=', Model_Ad::STATUS_PUBLISHED)->where('published', 'between', array($my_from_date, $my_to_date))->group_by(DB::expr('DATE( published )'))->order_by('date', 'asc')->execute();
$ads_dates = $query->as_array('date');
//Today
$query = DB::select(DB::expr('COUNT(id_ad) count'))->from('ads')->where('status', '=', Model_Ad::STATUS_PUBLISHED)->where(DB::expr('DATE( created )'), '=', DB::expr('CURDATE()'))->group_by(DB::expr('DATE( published )'))->order_by('published', 'asc')->execute();
$ads = $query->as_array();
$content->ads_today = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Yesterday
$query = DB::select(DB::expr('COUNT(id_ad) count'))->from('ads')->where('status', '=', Model_Ad::STATUS_PUBLISHED)->where(DB::expr('DATE( created )'), '=', date('Y-m-d', strtotime('-1 day')))->group_by(DB::expr('DATE( published )'))->order_by('published', 'asc')->execute();
$ads = $query->as_array();
$content->ads_yesterday = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Last 30 days ads
$query = DB::select(DB::expr('COUNT(id_ad) count'))->from('ads')->where('status', '=', Model_Ad::STATUS_PUBLISHED)->where('published', 'between', array(date('Y-m-d', strtotime('-30 day')), date::unix2mysql()))->execute();
$ads = $query->as_array();
$content->ads_month = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//total ads
$query = DB::select(DB::expr('COUNT(id_ad) count'))->from('ads')->where('status', '=', Model_Ad::STATUS_PUBLISHED)->execute();
$ads = $query->as_array();
$content->ads_total = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
/////////////////////VISITS STATS////////////////////////////////
//visits created last XX days
$query = DB::select(DB::expr('DATE(created) date'))->select(DB::expr('COUNT(id_visit) count'))->from('visits')->where('created', 'between', array($my_from_date, $my_to_date))->group_by(DB::expr('DATE( created )'))->order_by('date', 'asc')->execute();
$visits = $query->as_array('date');
$stats_daily = array();
foreach ($dates as $date) {
$count_views = isset($visits[$date['date']]['count']) ? $visits[$date['date']]['count'] : 0;
$count_ads = isset($ads_dates[$date['date']]['count']) ? $ads_dates[$date['date']]['count'] : 0;
$stats_daily[] = array('date' => $date['date'], 'views' => $count_views, 'ads' => $count_ads);
}
$content->stats_daily = $stats_daily;
//Today
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where(DB::expr('DATE( created )'), '=', DB::expr('CURDATE()'))->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->visits_today = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Yesterday
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where(DB::expr('DATE( created )'), '=', date('Y-m-d', strtotime('-1 day')))->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->visits_yesterday = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Last 30 days visits
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where('created', 'between', array(date('Y-m-d', strtotime('-30 day')), date::unix2mysql()))->execute();
$visits = $query->as_array();
$content->visits_month = isset($visits[0]['count']) ? $visits[0]['count'] : 0;
//total visits
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->execute();
$visits = $query->as_array();
$content->visits_total = isset($visits[0]['count']) ? $visits[0]['count'] : 0;
/////////////////////ORDERS STATS////////////////////////////////
//orders created last XX days
$query = DB::select(DB::expr('DATE(created) date'))->select(DB::expr('COUNT(id_order) count'))->select(DB::expr('SUM(amount) total'))->from('orders')->where('created', 'between', array($my_from_date, $my_to_date))->where('status', '=', Model_Order::STATUS_PAID)->group_by(DB::expr('DATE( created )'))->order_by('date', 'asc')->execute();
$orders = $query->as_array('date');
$stats_orders = array();
foreach ($dates as $date) {
$count_orders = isset($orders[$date['date']]['count']) ? $orders[$date['date']]['count'] : 0;
$count_sum = isset($orders[$date['date']]['total']) ? $orders[$date['date']]['total'] : 0;
$stats_orders[] = array('date' => $date['date'], '#orders' => $count_orders, '$' => $count_sum);
}
$content->stats_orders = $stats_orders;
//Today
$query = DB::select(DB::expr('COUNT(id_order) count'))->from('orders')->where(DB::expr('DATE( created )'), '=', DB::expr('CURDATE()'))->where('status', '=', Model_Order::STATUS_PAID)->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->orders_yesterday = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Yesterday
$query = DB::select(DB::expr('COUNT(id_order) count'))->from('orders')->where(DB::expr('DATE( created )'), '=', date('Y-m-d', strtotime('-1 day')))->where('status', '=', Model_Order::STATUS_PAID)->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->orders_today = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Last 30 days orders
$query = DB::select(DB::expr('COUNT(id_order) count'))->from('orders')->where('created', 'between', array(date('Y-m-d', strtotime('-30 day')), date::unix2mysql()))->where('status', '=', Model_Order::STATUS_PAID)->execute();
$orders = $query->as_array();
$content->orders_month = isset($orders[0]['count']) ? $orders[0]['count'] : 0;
//total orders
$query = DB::select(DB::expr('COUNT(id_order) count'))->from('orders')->where('status', '=', Model_Order::STATUS_PAID)->execute();
$orders = $query->as_array();
$content->orders_total = isset($orders[0]['count']) ? $orders[0]['count'] : 0;
}
public function action_stats()
{
Breadcrumbs::add(Breadcrumb::factory()->set_title(__('My ads'))->set_url(Route::url('oc-panel', array('controller' => 'myads', 'action' => 'index'))));
Breadcrumbs::add(Breadcrumb::factory()->set_title(__('Stats')));
Controller::$full_width = TRUE;
$this->template->scripts['footer'] = array('js/chart.min.js', 'js/chart.js-php.js', 'js/oc-panel/stats/dashboard.js');
$this->template->title = __('Stats');
$this->template->bind('content', $content);
$content = View::factory('oc-panel/profile/stats');
$list_ad = array();
$advert = new Model_Ad();
//single stats for 1 ad
if (is_numeric($id_ad = $this->request->param('id'))) {
$advert = new Model_Ad($id_ad);
if ($advert->loaded()) {
//if admin or moderator user is the advert user ;) hack!!
if ($this->user->id_role == Model_Role::ROLE_ADMIN or $this->user->id_role == Model_Role::ROLE_MODERATOR) {
$user = $advert->user;
} else {
$user = $this->user;
}
if ($user->id_user !== $advert->id_user) {
Alert::set(Alert::ALERT, __("This is not your advertisement."));
HTTP::redirect(Route::url('oc-panel', array('controller' => 'myads', 'action' => 'index')));
}
Breadcrumbs::add(Breadcrumb::factory()->set_title($advert->title));
// make a list of 1 ad (array), and than pass this array to query (IN).. To get correct visits
$list_ad[] = $id_ad;
}
}
//we didnt filter by ad, so lets get them all!
if (empty($list_ad)) {
$ads = new Model_Ad();
$collection_of_user_ads = $ads->where('id_user', '=', $this->user->id_user)->find_all();
$list_ad = array();
foreach ($collection_of_user_ads as $key) {
// make a list of ads (array), and than pass this array to query (IN).. To get correct visits
$list_ad[] = $key->id_ad;
}
}
// if user doesn't have any ads
if (empty($list_ad)) {
$list_ad = array(NULL);
}
$content->advert = $advert;
//Getting the dates and range
$from_date = Core::post('from_date', strtotime('-1 month'));
$to_date = Core::post('to_date', time());
//we assure is a proper time stamp if not we transform it
if (is_string($from_date) === TRUE) {
$from_date = strtotime($from_date);
}
if (is_string($to_date) === TRUE) {
$to_date = strtotime($to_date);
}
//mysql formated dates
$my_from_date = Date::unix2mysql($from_date);
$my_to_date = Date::unix2mysql($to_date);
//dates range we are filtering
$dates = Date::range($from_date, $to_date, '+1 day', 'Y-m-d', array('date' => 0, 'count' => 0), 'date');
//dates displayed in the form
$content->from_date = date('Y-m-d', $from_date);
$content->to_date = date('Y-m-d', $to_date);
/////////////////////CONTACT STATS////////////////////////////////
//visits created last XX days
$query = DB::select(DB::expr('DATE(created) date'))->select(DB::expr('COUNT(contacted) count'))->from('visits')->where('contacted', '=', 1)->where('id_ad', 'in', $list_ad)->where('created', 'between', array($my_from_date, $my_to_date))->group_by(DB::expr('DATE( created )'))->order_by('date', 'asc')->execute();
$contacts_dates = $query->as_array('date');
//Today
$query = DB::select(DB::expr('COUNT(contacted) count'))->from('visits')->where('contacted', '=', 1)->where('id_ad', 'in', $list_ad)->where(DB::expr('DATE( created )'), '=', DB::expr('CURDATE()'))->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$contacts = $query->as_array();
$content->contacts_today = isset($contacts[0]['count']) ? $contacts[0]['count'] : 0;
//Yesterday
$query = DB::select(DB::expr('COUNT(contacted) count'))->from('visits')->where('contacted', '=', 1)->where('id_ad', 'in', $list_ad)->where(DB::expr('DATE( created )'), '=', date('Y-m-d', strtotime('-1 day')))->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$contacts = $query->as_array();
$content->contacts_yesterday = isset($contacts[0]['count']) ? $contacts[0]['count'] : 0;
//
//Last 30 days contacts
$query = DB::select(DB::expr('COUNT(contacted) count'))->from('visits')->where('contacted', '=', 1)->where('id_ad', 'in', $list_ad)->where('created', 'between', array(date('Y-m-d', strtotime('-30 day')), date::unix2mysql()))->execute();
$contacts = $query->as_array();
$content->contacts_month = isset($contacts[0]['count']) ? $contacts[0]['count'] : 0;
//total contacts
$query = DB::select(DB::expr('COUNT(contacted) count'))->where('contacted', '=', 1)->where('id_ad', 'in', $list_ad)->from('visits')->execute();
$contacts = $query->as_array();
$content->contacts_total = isset($contacts[0]['count']) ? $contacts[0]['count'] : 0;
/////////////////////VISITS STATS////////////////////////////////
//visits created last XX days
$query = DB::select(DB::expr('DATE(created) date'))->select(DB::expr('COUNT(id_visit) count'))->from('visits')->where('id_ad', 'in', $list_ad)->where('created', 'between', array($my_from_date, $my_to_date))->group_by(DB::expr('DATE( created )'))->order_by('date', 'asc')->execute();
$visits = $query->as_array('date');
$stats_daily = array();
foreach ($dates as $date) {
$count_contants = isset($contacts_dates[$date['date']]['count']) ? $contacts_dates[$date['date']]['count'] : 0;
$count_visits = isset($visits[$date['date']]['count']) ? $visits[$date['date']]['count'] : 0;
$stats_daily[] = array('date' => $date['date'], 'views' => $count_visits, 'contacts' => $count_contants);
}
$content->stats_daily = $stats_daily;
//Today
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where('id_ad', 'in', $list_ad)->where(DB::expr('DATE( created )'), '=', DB::expr('CURDATE()'))->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$visits = $query->as_array();
$content->visits_today = isset($visits[0]['count']) ? $visits[0]['count'] : 0;
//Yesterday
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where('id_ad', 'in', $list_ad)->where(DB::expr('DATE( created )'), '=', date('Y-m-d', strtotime('-1 day')))->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$visits = $query->as_array();
$content->visits_yesterday = isset($visits[0]['count']) ? $visits[0]['count'] : 0;
//Last 30 days visits
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where('id_ad', 'in', $list_ad)->where('created', 'between', array(date('Y-m-d', strtotime('-30 day')), date::unix2mysql()))->execute();
$visits = $query->as_array();
$content->visits_month = isset($visits[0]['count']) ? $visits[0]['count'] : 0;
//total visits
$query = DB::select(DB::expr('COUNT(id_visit) count'))->where('id_ad', 'in', $list_ad)->from('visits')->execute();
$visits = $query->as_array();
$content->visits_total = isset($visits[0]['count']) ? $visits[0]['count'] : 0;
}
/**
* get popular ads
* @param integer $days number of days to calculate
* @return array id_ad and count
*/
public static function popular_ads($days = 30)
{
$query = DB::select('id_ad', DB::expr('COUNT(id_visit) count'))->from('visits')->where('created', 'between', array(date('Y-m-d', strtotime('-' . $days . ' day')), date::unix2mysql()))->group_by(DB::expr('id_ad'))->order_by('count', 'asc')->execute();
return $query->as_array('id_ad');
}
public function action_index()
{
Breadcrumbs::add(Breadcrumb::factory()->set_title(__('Stats')));
//local files
if (Theme::get('cdn_files') == FALSE) {
$this->template->styles = array('css/datepicker.css' => 'screen');
$this->template->scripts['footer'] = array('js/bootstrap-datepicker.js', 'js/oc-panel/stats/dashboard.js');
} else {
$this->template->styles = array('//cdn.jsdelivr.net/bootstrap.datepicker/0.1/css/datepicker.css' => 'screen');
$this->template->scripts['footer'] = array('//cdn.jsdelivr.net/bootstrap.datepicker/0.1/js/bootstrap-datepicker.js', 'js/oc-panel/stats/dashboard.js');
}
$this->template->title = __('Stats');
$this->template->bind('content', $content);
$content = View::factory('oc-panel/pages/stats/dashboard');
//Getting the dates and range
$from_date = Core::post('from_date', strtotime('-1 month'));
$to_date = Core::post('to_date', time());
//we assure is a proper time stamp if not we transform it
if (is_string($from_date) === TRUE) {
$from_date = strtotime($from_date);
}
if (is_string($to_date) === TRUE) {
$to_date = strtotime($to_date);
}
//mysql formated dates
$my_from_date = Date::unix2mysql($from_date);
$my_to_date = Date::unix2mysql($to_date);
//dates range we are filtering
$dates = Date::range($from_date, $to_date, '+1 day', 'Y-m-d', array('date' => 0, 'count' => 0), 'date');
//dates displayed in the form
$content->from_date = date('Y-m-d', $from_date);
$content->to_date = date('Y-m-d', $to_date);
//ads published last XX days
$query = DB::select(DB::expr('DATE(published) date'))->select(DB::expr('COUNT(id_ad) count'))->from('ads')->where('status', '=', Model_Ad::STATUS_PUBLISHED)->where('published', 'between', array($my_from_date, $my_to_date))->group_by(DB::expr('DATE( published )'))->order_by('date', 'asc')->execute();
$ads_dates = $query->as_array('date');
//Today
$query = DB::select(DB::expr('COUNT(id_ad) count'))->from('ads')->where('status', '=', Model_Ad::STATUS_PUBLISHED)->where(DB::expr('DATE( created )'), '=', DB::expr('CURDATE()'))->group_by(DB::expr('DATE( published )'))->order_by('published', 'asc')->execute();
$ads = $query->as_array();
$content->ads_today = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Yesterday
$query = DB::select(DB::expr('COUNT(id_ad) count'))->from('ads')->where('status', '=', Model_Ad::STATUS_PUBLISHED)->where(DB::expr('DATE( created )'), '=', date('Y-m-d', strtotime('-1 day')))->group_by(DB::expr('DATE( published )'))->order_by('published', 'asc')->execute();
$ads = $query->as_array();
$content->ads_yesterday = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Last 30 days ads
$query = DB::select(DB::expr('COUNT(id_ad) count'))->from('ads')->where('status', '=', Model_Ad::STATUS_PUBLISHED)->where('published', 'between', array(date('Y-m-d', strtotime('-30 day')), date::unix2mysql()))->execute();
$ads = $query->as_array();
$content->ads_month = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//total ads
$query = DB::select(DB::expr('COUNT(id_ad) count'))->from('ads')->where('status', '=', Model_Ad::STATUS_PUBLISHED)->execute();
$ads = $query->as_array();
$content->ads_total = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
/////////////////////VISITS STATS////////////////////////////////
//visits created last XX days
$query = DB::select(DB::expr('DATE(created) date'))->select(DB::expr('COUNT(id_visit) count'))->from('visits')->where('created', 'between', array($my_from_date, $my_to_date))->group_by(DB::expr('DATE( created )'))->order_by('date', 'asc')->execute();
$visits = $query->as_array('date');
$stats_daily = array();
foreach ($dates as $date) {
$count_views = isset($visits[$date['date']]['count']) ? $visits[$date['date']]['count'] : 0;
$count_ads = isset($ads_dates[$date['date']]['count']) ? $ads_dates[$date['date']]['count'] : 0;
$stats_daily[] = array('date' => $date['date'], 'views' => $count_views, 'ads' => $count_ads);
}
$content->stats_daily = $stats_daily;
//Today
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where(DB::expr('DATE( created )'), '=', DB::expr('CURDATE()'))->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->visits_today = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Yesterday
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where(DB::expr('DATE( created )'), '=', date('Y-m-d', strtotime('-1 day')))->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->visits_yesterday = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Last 30 days visits
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where('created', 'between', array(date('Y-m-d', strtotime('-30 day')), date::unix2mysql()))->execute();
$visits = $query->as_array();
$content->visits_month = isset($visits[0]['count']) ? $visits[0]['count'] : 0;
//total visits
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->execute();
$visits = $query->as_array();
$content->visits_total = isset($visits[0]['count']) ? $visits[0]['count'] : 0;
/////////////////////ORDERS STATS////////////////////////////////
//orders created last XX days
$query = DB::select(DB::expr('DATE(created) date'))->select(DB::expr('COUNT(id_order) count'))->select(DB::expr('SUM(amount) total'))->from('orders')->where('created', 'between', array($my_from_date, $my_to_date))->group_by(DB::expr('DATE( created )'))->order_by('date', 'asc')->execute();
$orders = $query->as_array('date');
$stats_orders = array();
foreach ($dates as $date) {
$count_orders = isset($orders[$date['date']]['count']) ? $orders[$date['date']]['count'] : 0;
$count_sum = isset($orders[$date['date']]['total']) ? $orders[$date['date']]['total'] : 0;
$stats_orders[] = array('date' => $date['date'], '#orders' => $count_orders, '$' => $count_sum);
}
$content->stats_orders = $stats_orders;
//Today
$query = DB::select(DB::expr('COUNT(id_order) count'))->from('orders')->where(DB::expr('DATE( created )'), '=', DB::expr('CURDATE()'))->where('status', '=', Model_Order::STATUS_PAID)->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->orders_yesterday = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Yesterday
$query = DB::select(DB::expr('COUNT(id_order) count'))->from('orders')->where(DB::expr('DATE( created )'), '=', date('Y-m-d', strtotime('-1 day')))->where('status', '=', Model_Order::STATUS_PAID)->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->orders_today = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Last 30 days orders
$query = DB::select(DB::expr('COUNT(id_order) count'))->from('orders')->where('created', 'between', array(date('Y-m-d', strtotime('-30 day')), date::unix2mysql()))->where('status', '=', Model_Order::STATUS_PAID)->execute();
$orders = $query->as_array();
$content->orders_month = isset($orders[0]['count']) ? $orders[0]['count'] : 0;
//total orders
$query = DB::select(DB::expr('COUNT(id_order) count'))->from('orders')->where('status', '=', Model_Order::STATUS_PAID)->execute();
$orders = $query->as_array();
$content->orders_total = isset($orders[0]['count']) ? $orders[0]['count'] : 0;
}
/**
* Methode : gestion du listing en ajax
*/
public function resultatAjax()
{
$this->auto_render = FALSE;
if (!request::is_ajax()) {
return FALSE;
}
$arrayCol = array('id', 'username', 'last_login', 'email');
$searchAjax = Search_Model::instance();
$arrayResultat = $searchAjax->indexRecherche($arrayCol, 'users', $this->input);
$display = false;
foreach ($arrayResultat as $row) {
$url = 'users/show/' . $row->id;
$v[] = '<center>' . $row->id . '</center>';
$v[] = html::anchor($url, $row->username);
$v[] = date::FormatDate(date::unix2mysql($row->last_login));
$v[] = $row->email;
$v[] = '<center>' . html::anchor($url, html::image('images/template/drawings.png', array('title' => Kohana::lang('form.edit'), 'class' => 'icon_list'))) . '</center>';
$display .= '[' . parent::json($v) . '],';
unset($v);
}
echo $searchAjax->displayRecherche($display, $this->input->get('sEcho'));
}
echo Kohana::lang('user.last_connect');
?>
: </div>
<div class="info_stat_user"><?php
echo date::FormatDate(date::unix2mysql($user->last_login));
?>
</div>
<div class="spacer"></div>
</div>
<div class="row_stat_user">
<div class="title_stat_user"><?php
echo Kohana::lang('user.last_action');
?>
: </div>
<div class="info_stat_user"><?php
echo date::FormatDate(date::unix2mysql($user->last_action));
?>
</div>
<div class="spacer"></div>
</div>
<div class="row_stat_user">
<div class="title_stat_user"><?php
echo Kohana::lang('user.ip');
?>
: </div>
<div class="info_stat_user"><?php
echo $user->ip;
?>
</div>
<div class="spacer"></div>
</div>
public function action_stats()
{
Breadcrumbs::add(Breadcrumb::factory()->set_title(__('Stats')));
//local files
if (Theme::get('cdn_files') == FALSE) {
$this->template->styles = array('css/datepicker.css' => 'screen');
$this->template->scripts['footer'] = array('js/bootstrap-datepicker.js', 'js/oc-panel/stats/dashboard.js');
} else {
$this->template->styles = array('http://cdn.jsdelivr.net/bootstrap.datepicker/0.1/css/datepicker.css' => 'screen');
$this->template->scripts['footer'] = array('http://cdn.jsdelivr.net/bootstrap.datepicker/0.1/js/bootstrap-datepicker.js', 'js/oc-panel/stats/dashboard.js');
}
$this->template->title = __('Stats');
$this->template->bind('content', $content);
$content = View::factory('oc-panel/profile/stats');
//Getting the dates and range
$from_date = Core::post('from_date', strtotime('-1 month'));
$to_date = Core::post('to_date', time());
//we assure is a proper time stamp if not we transform it
if (is_string($from_date) === TRUE) {
$from_date = strtotime($from_date);
}
if (is_string($to_date) === TRUE) {
$to_date = strtotime($to_date);
}
//mysql formated dates
$my_from_date = Date::unix2mysql($from_date);
$my_to_date = Date::unix2mysql($to_date);
//dates range we are filtering
$dates = Date::range($from_date, $to_date, '+1 day', 'Y-m-d', array('date' => 0, 'count' => 0), 'date');
//dates displayed in the form
$content->from_date = date('Y-m-d', $from_date);
$content->to_date = date('Y-m-d', $to_date);
// user and his ads
$user = Auth::instance()->get_user();
$ads = new Model_Ad();
$collection_of_user_ads = $ads->where('id_user', '=', $user->id_user)->find_all();
$list_ad = array();
foreach ($collection_of_user_ads as $key) {
// make a list of ads (array), and than pass this array to query (IN).. To get correct visits
$list_ad[] = $key->id_ad;
}
// if user doesn't have any ads
if (empty($list_ad)) {
$list_ad = array(NULL);
}
/////////////////////CONTACT STATS////////////////////////////////
//visits created last XX days
$query = DB::select(DB::expr('DATE(created) date'))->select(DB::expr('COUNT(contacted) count'))->from('visits')->where('contacted', '=', 1)->where('id_ad', 'in', $list_ad)->where('created', 'between', array($my_from_date, $my_to_date))->group_by(DB::expr('DATE( created )'))->order_by('date', 'asc')->execute();
$contacts_dates = $query->as_array('date');
//Today
$query = DB::select(DB::expr('COUNT(contacted) count'))->from('visits')->where('contacted', '=', 1)->where('id_ad', 'in', $list_ad)->where(DB::expr('DATE( created )'), '=', DB::expr('CURDATE()'))->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$contacts = $query->as_array();
$content->contacts_today = isset($contacts[0]['count']) ? $contacts[0]['count'] : 0;
//Yesterday
$query = DB::select(DB::expr('COUNT(contacted) count'))->from('visits')->where('contacted', '=', 1)->where('id_ad', 'in', $list_ad)->where(DB::expr('DATE( created )'), '=', date('Y-m-d', strtotime('-1 day')))->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$contacts = $query->as_array();
$content->contacts_yesterday = isset($contacts[0]['count']) ? $contacts[0]['count'] : 0;
//
//Last 30 days contacts
$query = DB::select(DB::expr('COUNT(contacted) count'))->from('visits')->where('contacted', '=', 1)->where('id_ad', 'in', $list_ad)->where('created', 'between', array(date('Y-m-d', strtotime('-30 day')), date::unix2mysql()))->execute();
$contacts = $query->as_array();
$content->contacts_month = isset($contacts[0]['count']) ? $contacts[0]['count'] : 0;
//total contacts
$query = DB::select(DB::expr('COUNT(contacted) count'))->where('contacted', '=', 1)->where('id_ad', 'in', $list_ad)->from('visits')->execute();
$contacts = $query->as_array();
$content->contacts_total = isset($contacts[0]['count']) ? $contacts[0]['count'] : 0;
/////////////////////VISITS STATS////////////////////////////////
//visits created last XX days
$query = DB::select(DB::expr('DATE(created) date'))->select(DB::expr('COUNT(id_visit) count'))->from('visits')->where('id_ad', 'in', $list_ad)->where('created', 'between', array($my_from_date, $my_to_date))->group_by(DB::expr('DATE( created )'))->order_by('date', 'asc')->execute();
$visits = $query->as_array('date');
$stats_daily = array();
foreach ($dates as $date) {
$count_contants = isset($contacts_dates[$date['date']]['count']) ? $contacts_dates[$date['date']]['count'] : 0;
$count_visits = isset($visits[$date['date']]['count']) ? $visits[$date['date']]['count'] : 0;
$stats_daily[] = array('date' => $date['date'], 'views' => $count_visits, 'contacts' => $count_contants);
}
$content->stats_daily = $stats_daily;
//Today
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where('id_ad', 'in', $list_ad)->where(DB::expr('DATE( created )'), '=', DB::expr('CURDATE()'))->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$visits = $query->as_array();
$content->visits_today = isset($visits[0]['count']) ? $visits[0]['count'] : 0;
//Yesterday
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where('id_ad', 'in', $list_ad)->where(DB::expr('DATE( created )'), '=', date('Y-m-d', strtotime('-1 day')))->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$visits = $query->as_array();
$content->visits_yesterday = isset($visits[0]['count']) ? $visits[0]['count'] : 0;
//Last 30 days visits
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where('id_ad', 'in', $list_ad)->where('created', 'between', array(date('Y-m-d', strtotime('-30 day')), date::unix2mysql()))->execute();
$visits = $query->as_array();
$content->visits_month = isset($visits[0]['count']) ? $visits[0]['count'] : 0;
//total visits
$query = DB::select(DB::expr('COUNT(id_visit) count'))->where('id_ad', 'in', $list_ad)->from('visits')->execute();
$visits = $query->as_array();
$content->visits_total = isset($visits[0]['count']) ? $visits[0]['count'] : 0;
}
