PHP date::unix2mysql方法代码示例

 /**
  * 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;
//.........这里部分代码省略.........

 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
//.........这里部分代码省略.........

 /**
  * 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();
//.........这里部分代码省略.........

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