/** * 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; }