Subversion Repositories SmartDukaan

<?php
App::uses('AppController', 'Controller');
/**
 * Administration Controller
 *
 * @property Administration $Administration
 * @property PaginatorComponent $Paginator
 */
class AdministrationController extends AppController {

/**
 * Components
 *
 * @var array
 */
        public $components = array('Paginator');

        public function beforeFilter() {
        parent::beforeFilter();
        $this->layout = 'admin';
    }

        public function dashboard(){
                $this->loadModel('User');
                //daily userbase
                $sql = "SELECT count(User.id) AS count,date(User.created) AS date FROM users User WHERE datediff(now(),created) <= 30 AND activated = 1 GROUP BY date(created) ORDER BY id DESC";
                $userbase = $this->User->query($sql);
                $sql = "SELECT count(Click.id) AS count,date(created) AS date FROM clicks Click WHERE datediff(now(),created) <= 30 GROUP BY date(created) ORDER BY id DESC";
                $clicks = $this->User->query($sql);
                $this->set(compact('userbase','clicks'));
                $sql = "SELECT count(id) AS count,date(created) AS date FROM orders WHERE datediff(now(),created) <= 30 AND status = 'ORDER_CREATED' GROUP BY date(created) ORDER BY id DESC";
                $orders = $this->User->query($sql);             
                // $sql = "select count(id) AS count,versioncode from devices group by versioncode";
                $sql = "SELECT COUNT( * ) AS count, a.c AS versioncode FROM (SELECT MAX( versioncode ) c, user_id FROM devices GROUP BY user_id) a GROUP BY a.c";
                $devices = $this->User->query($sql);
                $sql = "SELECT count(distinct user_id) AS count,date(created) AS date FROM `brand_preferences` WHERE datediff(now(),created) <= 30 group by date(created) order by id desc";
                $preferences_set = $this->User->query($sql);            
                $sql = "select count(distinct user_id) AS count from brand_preferences";
                $usersWithBrandPreferencesSet = $this->User->query($sql);
                $sql = "select count(id) AS count from users";
                $totalUsers = $this->User->query($sql);         
                $sql = "SELECT DATE( created ) AS date, COUNT( DISTINCT (user_id) ) AS count FROM user_urls WHERE DATE( created ) > CURDATE( ) - INTERVAL 30 DAY GROUP BY date order by id desc";
                $activeUsers = $this->User->query($sql);
                $sql = "select date(created) date, count(distinct user_id) count from (select id,user_id, created from orders union all select id,user_id, created from flipkartorders) a where date(created) > curdate() - interval 30 day group by date order by a.id desc";
                $activeBuyers = $this->User->query($sql);
                $sql = "SELECT count(id) as count,date(mobile_number_last_updated) as date from users where mobile_verified=1 and datediff(now(),mobile_number_last_updated) <= 30 group by date(mobile_number_last_updated) ORDER BY date(mobile_number_last_updated) desc";
                $dailyVerifications = $this->User->query($sql);
                $this->set(compact('userbase','clicks','orders','devices','preferences_set','usersWithBrandPreferencesSet','totalUsers','activeUsers','activeBuyers','dailyVerifications'));
        }
}