public function action_info()
{
//try to get the info from the cache
$info = Core::cache('action_info', NULL);
//not cached :(
if ($info === NULL) {
$products = new Model_product();
$total_products = $products->count_all();
$last_product = $products->select('created')->order_by('created', 'desc')->limit(1)->find();
$last_product = $last_product->created;
$products = new Model_product();
$first_product = $products->select('created')->order_by('created', 'asc')->limit(1)->find();
$first_product = $first_product->created;
$views = new Model_Visit();
$total_views = $views->count_all();
$users = new Model_User();
$total_users = $users->count_all();
$info = array('site_url' => Core::config('general.base_url'), 'site_name' => Core::config('general.site_name'), 'site_description' => Core::config('general.site_description'), 'created' => $first_product, 'updated' => $last_product, 'email' => Core::config('email.notify_email'), 'version' => Core::VERSION, 'theme' => Core::config('appearance.theme'), 'theme_mobile' => Core::config('appearance.theme_mobile'), 'charset' => Kohana::$charset, 'timezone' => Core::config('i18n.timezone'), 'locale' => Core::config('i18n.locale'), 'currency' => '', 'products' => $total_products, 'views' => $total_views, 'users' => $total_users);
Core::cache('action_info', $info);
}
$this->response->headers('Content-type', 'application/javascript');
$this->response->body(json_encode($info));
}
public function action_index()
{
$this->template->title = __('Stats');
Breadcrumbs::add(Breadcrumb::factory()->set_title($this->template->title)->set_url(Route::url('oc-panel', array('controller' => 'stats'))));
$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', '//cdn.jsdelivr.net/sorttable/2/sorttable.min.js', 'js/oc-panel/stats/dashboard.js');
$this->template->bind('content', $content);
$content = View::factory('oc-panel/pages/stats/dashboard');
$content->title = $this->template->title;
//stats by product
$content->product = NULL;
if ($this->request->param('id')) {
$product = new Model_product();
$product->where('seotitle', '=', $this->request->param('id'))->limit(1)->find();
if ($product->loaded()) {
$content->product = $product;
$this->template->title .= ' ' . $product->title;
$content->title .= ' ' . $product->title;
Breadcrumbs::add(Breadcrumb::factory()->set_title($product->title));
}
}
//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 range we are filtering, 1 year back from the to date.
$dates_year = Date::range(strtotime('-1 year', $from_date), $to_date, '+1 month', 'Y-m', 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);
/////////////////////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));
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->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;
$stats_daily[] = array('date' => $date['date'], 'views' => $count_views);
}
$content->stats_daily = $stats_daily;
//Today
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where(DB::expr('DATE( created )'), '=', date('Y-m-d'));
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->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;
//Current month
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where(DB::expr('MONTH( created )'), '=', date('m'))->where(DB::expr('YEAR( created )'), '=', date('Y'));
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('YEAR(`created`),MONTH(`created`)'))->order_by(DB::expr('YEAR(`created`),MONTH(`created`)'), 'asc')->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->visits_month = 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')));
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->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;
//Current month
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits')->where(DB::expr('YEAR( created )'), '=', date('Y'));
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('YEAR(`created`)'))->order_by(DB::expr('YEAR(`created`)'), 'asc')->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->visits_year = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//total visits
$query = DB::select(DB::expr('COUNT(id_visit) count'))->from('visits');
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->execute();
$visits = $query->as_array();
$content->visits_total = isset($visits[0]['count']) ? $visits[0]['count'] : 0;
//visits by month 1 year from to_date
$query = DB::select(DB::expr('DATE_FORMAT(`created`, "%Y-%m") date'))->select(DB::expr('COUNT(id_visit) count'))->from('visits');
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('YEAR(`created`),MONTH(`created`)'))->order_by(DB::expr('YEAR(`created`),MONTH(`created`)'), 'asc')->execute();
$visits = $query->as_array('date');
$stats_by_month = array();
foreach ($dates_year as $date) {
$count_views = isset($visits[$date['date']]['count']) ? $visits[$date['date']]['count'] : 0;
$stats_by_month[] = array('date' => $date['date'], 'views' => $count_views);
}
$content->stats_by_month = $stats_by_month;
/////////////////////ORDERS STATS////////////////////////////////
//orders created last XX days
$query = DB::select(DB::expr('DATE(pay_date) date'))->select(DB::expr('COUNT(id_order) count'))->select(DB::expr('SUM(amount) total'))->from('orders')->where('pay_date', 'between', array($my_from_date, $my_to_date))->where('status', '=', Model_Order::STATUS_PAID);
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('DATE( pay_date )'))->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'))->select(DB::expr('SUM(amount) total'))->from('orders')->where(DB::expr('DATE( pay_date )'), '=', date('Y-m-d'))->where('status', '=', Model_Order::STATUS_PAID);
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('DATE( pay_date )'))->order_by('pay_date', 'asc')->execute();
$ads = $query->as_array();
$content->orders_today = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
$content->amount_today = isset($ads[0]['total']) ? $ads[0]['total'] : 0;
//Yesterday
$query = DB::select(DB::expr('COUNT(id_order) count'))->select(DB::expr('SUM(amount) total'))->from('orders')->where(DB::expr('DATE( pay_date )'), '=', date('Y-m-d', strtotime('-1 day')))->where('status', '=', Model_Order::STATUS_PAID);
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('DATE( pay_date )'))->order_by('pay_date', 'asc')->execute();
$ads = $query->as_array();
$content->orders_yesterday = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
$content->amount_yesterday = isset($ads[0]['total']) ? $ads[0]['total'] : 0;
//current month
$query = DB::select(DB::expr('COUNT(id_order) count'))->select(DB::expr('SUM(amount) total'))->from('orders')->where(DB::expr('MONTH( pay_date )'), '=', date('m'))->where(DB::expr('YEAR( pay_date )'), '=', date('Y'))->where('status', '=', Model_Order::STATUS_PAID);
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('YEAR(`pay_date`),MONTH(`pay_date`)'))->order_by(DB::expr('YEAR(`pay_date`),MONTH(`pay_date`)'), 'asc')->order_by('pay_date', 'asc')->execute();
$orders = $query->as_array();
$content->orders_month = isset($orders[0]['count']) ? $orders[0]['count'] : 0;
$content->amount_month = isset($orders[0]['total']) ? $orders[0]['total'] : 0;
//current year
$query = DB::select(DB::expr('COUNT(id_order) count'))->select(DB::expr('SUM(amount) total'))->from('orders')->where(DB::expr('YEAR( pay_date )'), '=', date('Y-m-d'))->where('status', '=', Model_Order::STATUS_PAID);
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('YEAR(`pay_date`)'))->order_by(DB::expr('YEAR(`pay_date`)'), 'asc')->order_by('pay_date', 'asc')->execute();
$orders = $query->as_array();
$content->orders_year = isset($orders[0]['count']) ? $orders[0]['count'] : 0;
$content->amount_year = isset($orders[0]['total']) ? $orders[0]['total'] : 0;
//total orders
$query = DB::select(DB::expr('COUNT(id_order) count'))->select(DB::expr('SUM(amount) total'))->from('orders')->where('status', '=', Model_Order::STATUS_PAID);
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->execute();
$orders = $query->as_array();
$content->orders_total = isset($orders[0]['count']) ? $orders[0]['count'] : 0;
$content->amount_total = isset($orders[0]['total']) ? $orders[0]['total'] : 0;
//orders per month
$query = DB::select(DB::expr('DATE_FORMAT(`pay_date`, "%Y-%m") date'))->select(DB::expr('COUNT(id_order) count'))->select(DB::expr('SUM(amount) total'))->from('orders')->where('status', '=', Model_Order::STATUS_PAID);
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('YEAR(`pay_date`),MONTH(`pay_date`)'))->order_by(DB::expr('YEAR(`pay_date`),MONTH(`pay_date`)'), 'asc')->execute();
$orders = $query->as_array('date');
$stats_orders_by_month = array();
foreach ($dates_year 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_by_month[] = array('date' => $date['date'], '#orders' => $count_orders, '$' => $count_sum);
}
$content->stats_orders_by_month = $stats_orders_by_month;
//////////////////////////GROUP BY PRODUCT TOTAL///////////////////
//visits
$query = DB::select(DB::expr('COUNT(id_visit) count'))->select('id_product')->from('visits')->where('id_product', 'is not', NULL)->where('created', 'between', array($my_from_date, $my_to_date))->group_by('id_product')->order_by('count', 'desc')->execute();
$content->visits_product = $query->as_array('id_product');
//orders
$query = DB::select('id_product')->select(DB::expr('COUNT(id_order) count'))->select(DB::expr('SUM(amount) total'))->from('orders')->where('status', '=', Model_Order::STATUS_PAID)->where('pay_date', 'between', array($my_from_date, $my_to_date))->group_by('id_product')->order_by('total', 'desc')->execute();
$content->orders_product = $query->as_array('id_product');
//downloads
$query = DB::select('id_product')->select(DB::expr('COUNT(id_product) count'))->from(array('orders', 'o'))->join(array('downloads', 'd'))->using('id_order')->where('d.created', 'between', array($my_from_date, $my_to_date))->group_by('id_product')->order_by('count', 'desc')->execute();
$content->downloads_product = $query->as_array('id_product');
//licenses
$query = DB::select('id_product')->select(DB::expr('COUNT(id_license) count'))->from('licenses')->where('status', '=', Model_License::STATUS_ACTIVE)->where('created', 'between', array($my_from_date, $my_to_date))->group_by('id_product')->order_by('count', 'desc')->execute();
$content->licenses_product = $query->as_array('id_product');
//tickets closed
$query = DB::select('id_product')->select(DB::expr('COUNT(id_ticket) count'))->from('tickets')->where('status', '=', Model_Ticket::STATUS_CLOSED)->where('id_ticket_parent', '=', NULL)->where('created', 'between', array($my_from_date, $my_to_date))->group_by('id_product')->order_by('count', 'desc')->execute();
$content->tickets_closed_product = $query->as_array('id_product');
//tickets open
$query = DB::select('id_product')->select(DB::expr('COUNT(id_ticket) count'))->from('tickets')->where('id_ticket_parent', '=', NULL)->where('created', 'between', array($my_from_date, $my_to_date))->group_by('id_product')->order_by('count', 'desc')->execute();
$content->tickets_open_product = $query->as_array('id_product');
$products = new Model_Product();
$content->products = $products->find_all();
//for the graphic
$products_total = array();
foreach ($content->products as $p) {
$products_total[] = array('name' => $p->title, '$' => isset($content->orders_product[$p->id_product]) ? round($content->orders_product[$p->id_product]['total'], 2) : 0);
}
$content->products_total = $products_total;
//////////////////////////DOWNLOADS STATS///////////////////
//downloads created last XX days
$query = DB::select(DB::expr('DATE(created) date'))->select(DB::expr('COUNT(id_download) count'))->from('downloads')->where('created', 'between', array($my_from_date, $my_to_date));
$query = $query->group_by(DB::expr('DATE( created )'))->order_by('date', 'asc')->execute();
$downloads = $query->as_array('date');
$stats_downloads = array();
foreach ($dates as $date) {
$count_downloads = isset($downloads[$date['date']]['count']) ? $downloads[$date['date']]['count'] : 0;
$stats_downloads[] = array('date' => $date['date'], '#downloads' => $count_downloads);
}
$content->stats_downloads = $stats_downloads;
//Today
$query = DB::select(DB::expr('COUNT(id_download) count'))->from('downloads')->where(DB::expr('DATE( created )'), '=', date('Y-m-d'));
$query = $query->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->downloads_today = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Yesterday
$query = DB::select(DB::expr('COUNT(id_download) count'))->from('downloads')->where(DB::expr('DATE( created )'), '=', date('Y-m-d', strtotime('-1 day')));
$query = $query->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->downloads_yesterday = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//current month
$query = DB::select(DB::expr('COUNT(id_download) count'))->from('downloads')->where(DB::expr('MONTH( created )'), '=', date('m'))->where(DB::expr('YEAR( created )'), '=', date('Y'));
$query = $query->group_by(DB::expr('YEAR(`created`),MONTH(`created`)'))->order_by(DB::expr('YEAR(`created`),MONTH(`created`)'), 'asc')->order_by('created', 'asc')->execute();
$downloads = $query->as_array();
$content->downloads_month = isset($downloads[0]['count']) ? $downloads[0]['count'] : 0;
//current year
$query = DB::select(DB::expr('COUNT(id_download) count'))->from('downloads')->where(DB::expr('YEAR( created )'), '=', date('Y'));
$query = $query->group_by(DB::expr('YEAR(`created`)'))->order_by(DB::expr('YEAR(`created`)'), 'asc')->order_by('created', 'asc')->execute();
$downloads = $query->as_array();
$content->downloads_year = isset($downloads[0]['count']) ? $downloads[0]['count'] : 0;
//total downloads
$query = DB::select(DB::expr('COUNT(id_download) count'))->from('downloads');
$query = $query->execute();
$downloads = $query->as_array();
$content->downloads_total = isset($downloads[0]['count']) ? $downloads[0]['count'] : 0;
//downloads per month
$query = DB::select(DB::expr('DATE_FORMAT(`created`, "%Y-%m") date'))->select(DB::expr('COUNT(id_download) count'))->from('downloads');
$query = $query->group_by(DB::expr('YEAR(`created`),MONTH(`created`)'))->order_by(DB::expr('YEAR(`created`),MONTH(`created`)'), 'asc')->execute();
$downloads = $query->as_array('date');
$stats_downloads_by_month = array();
foreach ($dates_year as $date) {
$count_downloads = isset($downloads[$date['date']]['count']) ? $downloads[$date['date']]['count'] : 0;
$stats_downloads_by_month[] = array('date' => $date['date'], '#downloads' => $count_downloads);
}
$content->stats_downloads_by_month = $stats_downloads_by_month;
//////////////////////////LICENSES STATS///////////////////
//licenses created last XX days
$query = DB::select(DB::expr('DATE(created) date'))->select(DB::expr('COUNT(id_license) count'))->from('licenses')->where('created', 'between', array($my_from_date, $my_to_date));
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('DATE( created )'))->order_by('date', 'asc')->execute();
$licenses = $query->as_array('date');
$stats_licenses = array();
foreach ($dates as $date) {
$count_licenses = isset($licenses[$date['date']]['count']) ? $licenses[$date['date']]['count'] : 0;
$stats_licenses[] = array('date' => $date['date'], '#licenses' => $count_licenses);
}
$content->stats_licenses = $stats_licenses;
//Today
$query = DB::select(DB::expr('COUNT(id_license) count'))->from('licenses')->where(DB::expr('DATE( created )'), '=', date('Y-m-d'));
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->licenses_today = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Yesterday
$query = DB::select(DB::expr('COUNT(id_license) count'))->from('licenses')->where(DB::expr('DATE( created )'), '=', date('Y-m-d', strtotime('-1 day')));
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->licenses_yesterday = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//current month
$query = DB::select(DB::expr('COUNT(id_license) count'))->from('licenses')->where(DB::expr('MONTH( created )'), '=', date('m'))->where(DB::expr('YEAR( created )'), '=', date('Y'));
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('YEAR(`created`),MONTH(`created`)'))->order_by(DB::expr('YEAR(`created`),MONTH(`created`)'), 'asc')->order_by('created', 'asc')->execute();
$licenses = $query->as_array();
$content->licenses_month = isset($licenses[0]['count']) ? $licenses[0]['count'] : 0;
//current year
$query = DB::select(DB::expr('COUNT(id_license) count'))->from('licenses')->where(DB::expr('YEAR( created )'), '=', date('Y'));
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('YEAR(`created`)'))->order_by(DB::expr('YEAR(`created`)'), 'asc')->order_by('created', 'asc')->execute();
$licenses = $query->as_array();
$content->licenses_year = isset($licenses[0]['count']) ? $licenses[0]['count'] : 0;
//total licenses
$query = DB::select(DB::expr('COUNT(id_license) count'))->from('licenses');
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->execute();
$licenses = $query->as_array();
$content->licenses_total = isset($licenses[0]['count']) ? $licenses[0]['count'] : 0;
//active licenses
$query = DB::select(DB::expr('COUNT(id_license) count'))->where(DB::expr('DATE(active_date)'), '<=', DB::expr('DATE(valid_date)'))->from('licenses');
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->execute();
$licenses = $query->as_array();
$content->licenses_active = isset($licenses[0]['count']) ? $licenses[0]['count'] : 0;
//licenses per month
$query = DB::select(DB::expr('DATE_FORMAT(`created`, "%Y-%m") date'))->select(DB::expr('COUNT(id_license) count'))->select(DB::expr('COUNT(active_date) activated '))->from('licenses');
if ($content->product !== NULL) {
$query = $query->where('id_product', '=', $content->product->id_product);
}
$query = $query->group_by(DB::expr('YEAR(`created`),MONTH(`created`)'))->order_by(DB::expr('YEAR(`created`),MONTH(`created`)'), 'asc')->execute();
$licenses = $query->as_array('date');
$stats_licenses_by_month = array();
foreach ($dates_year as $date) {
$count_licenses = isset($licenses[$date['date']]['count']) ? $licenses[$date['date']]['count'] : 0;
$activated_licenses = isset($licenses[$date['date']]['activated']) ? $licenses[$date['date']]['activated'] : 0;
$stats_licenses_by_month[] = array('date' => $date['date'], '#licenses' => $count_licenses, '#activated' => $activated_licenses);
}
$content->stats_licenses_by_month = $stats_licenses_by_month;
//////////////////////////TICKETS STATS///////////////////
//Today
$query = DB::select(DB::expr('COUNT(id_ticket) count'))->from('tickets')->where(DB::expr('DATE( created )'), '=', date('Y-m-d'));
$query = $query->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->tickets_today = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//Yesterday
$query = DB::select(DB::expr('COUNT(id_ticket) count'))->from('tickets')->where(DB::expr('DATE( created )'), '=', date('Y-m-d', strtotime('-1 day')));
$query = $query->group_by(DB::expr('DATE( created )'))->order_by('created', 'asc')->execute();
$ads = $query->as_array();
$content->tickets_yesterday = isset($ads[0]['count']) ? $ads[0]['count'] : 0;
//current month
$query = DB::select(DB::expr('COUNT(id_ticket) count'))->from('tickets')->where(DB::expr('MONTH( created )'), '=', date('m'))->where(DB::expr('YEAR( created )'), '=', date('Y'));
$query = $query->group_by(DB::expr('YEAR(`created`),MONTH(`created`)'))->order_by(DB::expr('YEAR(`created`),MONTH(`created`)'), 'asc')->order_by('created', 'asc')->execute();
$tickets = $query->as_array();
$content->tickets_month = isset($tickets[0]['count']) ? $tickets[0]['count'] : 0;
//current year
$query = DB::select(DB::expr('COUNT(id_ticket) count'))->from('tickets')->where(DB::expr('YEAR( created )'), '=', date('Y'));
$query = $query->group_by(DB::expr('YEAR(`created`)'))->order_by(DB::expr('YEAR(`created`)'), 'asc')->order_by('created', 'asc')->execute();
$tickets = $query->as_array();
$content->tickets_year = isset($tickets[0]['count']) ? $tickets[0]['count'] : 0;
//read tickets
$query = DB::select(DB::expr('COUNT(id_ticket) count'))->from('tickets')->where('status', '=', Model_Ticket::STATUS_READ);
$query = $query->execute();
$tickets = $query->as_array();
$content->tickets_read = isset($tickets[0]['count']) ? $tickets[0]['count'] : 0;
//hold tickets
$query = DB::select(DB::expr('COUNT(id_ticket) count'))->from('tickets')->where('status', '=', Model_Ticket::STATUS_HOLD);
$query = $query->execute();
$tickets = $query->as_array();
$content->tickets_hold = isset($tickets[0]['count']) ? $tickets[0]['count'] : 0;
//closed tickets
$query = DB::select(DB::expr('COUNT(id_ticket) count'))->from('tickets')->where('status', '=', Model_Ticket::STATUS_CLOSED);
$query = $query->execute();
$tickets = $query->as_array();
$content->tickets_closed = isset($tickets[0]['count']) ? $tickets[0]['count'] : 0;
//total tickets
$query = DB::select(DB::expr('COUNT(id_ticket) count'))->from('tickets');
$query = $query->execute();
$tickets = $query->as_array();
$content->tickets_total = isset($tickets[0]['count']) ? $tickets[0]['count'] : 0;
//tickets created last XX days
//open/created
$query = DB::select(DB::expr('DATE(created) date'))->select(DB::expr('COUNT(id_ticket) count'))->from('tickets')->where('id_ticket_parent', '=', NULL)->where('created', 'between', array($my_from_date, $my_to_date));
$query = $query->group_by(DB::expr('DATE( created )'))->order_by('date', 'asc')->execute();
$query_closed = DB::select(DB::expr('DATE(read_date) date'))->select(DB::expr('COUNT(id_ticket) count'))->from('tickets')->where('read_date', '!=', 'NULL')->where('status', '=', Model_Ticket::STATUS_CLOSED)->where('read_date', 'between', array($my_from_date, $my_to_date));
$query_closed = $query_closed->group_by(DB::expr('DATE( read_date )'))->order_by('date', 'asc')->execute();
$query_answers = DB::select(DB::expr('DATE(created) date'))->select(DB::expr('COUNT(id_ticket) count'))->from('tickets')->where('id_ticket_parent', '!=', NULL)->where('created', 'between', array($my_from_date, $my_to_date));
$query_answers = $query_answers->group_by(DB::expr('DATE( created )'))->order_by('date', 'asc')->execute();
$answers = $query_answers->as_array('date');
$closed = $query_closed->as_array('date');
$tickets = $query->as_array('date');
$stats_tickets = array();
foreach ($dates as $date) {
$count_open = isset($tickets[$date['date']]['count']) ? $tickets[$date['date']]['count'] : 0;
$count_closed = isset($closed[$date['date']]['count']) ? $closed[$date['date']]['count'] : 0;
$count_answers = isset($answers[$date['date']]['count']) ? $answers[$date['date']]['count'] : 0;
$stats_tickets[] = array('date' => $date['date'], '#open' => $count_open, '#closed' => $count_closed, '#answers' => $count_answers);
}
$content->stats_tickets = $stats_tickets;
//tickets per month
$query = DB::select(DB::expr('DATE_FORMAT(`created`, "%Y-%m") date'))->select(DB::expr('COUNT(id_ticket) count'))->from('tickets');
$query = $query->group_by(DB::expr('YEAR(`created`),MONTH(`created`)'))->order_by(DB::expr('YEAR(`created`),MONTH(`created`)'), 'asc')->execute();
$tickets = $query->as_array('date');
$stats_tickets_by_month = array();
foreach ($dates_year as $date) {
$count_tickets = isset($tickets[$date['date']]['count']) ? $tickets[$date['date']]['count'] : 0;
$stats_tickets_by_month[] = array('date' => $date['date'], '#tickets' => $count_tickets);
}
$content->stats_tickets_by_month = $stats_tickets_by_month;
}
public function action_demo()
{
$this->before('demo');
$product = new Model_product();
$product->where('seotitle', '=', $this->request->param('seotitle'))->where('status', '=', Model_Product::STATUS_ACTIVE)->limit(1)->find();
if ($product->loaded()) {
if (Theme::get('premium') != 1) {
$this->redirect($product->url_demo);
}
if (core::config('product.count_visits') == 1) {
//count how many visits has
$hits = new Model_Visit();
$hits = $hits->where('id_product', '=', $product->id_product)->count_all();
} else {
$hits = 0;
}
$this->template->title = $product->title . ' - ' . __('Demo') . ' - ' . $product->category->name;
$this->template->meta_description = __('Demo') . ', ' . __('preview') . ',' . $product->description;
$this->template->bind('product', $product);
//get all the products same category with demo
$products = $product->category->products->where('url_demo', 'IS NOT', NULL)->where('url_demo', '!=', '')->where('status', '=', Model_Product::STATUS_ACTIVE)->cached()->find_all();
$this->template->bind('products', $products);
$skins = NULL;
if ($product->skins != '') {
$skins = explode(',', $product->skins);
}
if (!is_array($skins) or count($skins) <= 0) {
$skins = NULL;
}
$this->template->bind('skins', $skins);
$skin = core::get('skin');
$this->template->bind('skin', $skin);
//product image
if ($product->get_first_image() !== NULL) {
Controller::$image = $product->get_first_image();
}
} else {
Alert::set(Alert::INFO, __('Product not found.'));
$this->redirect(Route::url('default'));
}
}
