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.activation_time) AS date FROM users User WHERE datediff(now(),activation_time) <= 30 AND activated = 1 GROUP BY date(activation_time) ORDER BY User.activation_time 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 AND extras NOT LIKE '%notification%'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 ( 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 date desc";

		$activeBuyers = $this->User->query($sql);

		ksort($activeBuyers);

		$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);

		$sql="SELECT date(transaction_time) AS date,COUNT(*) AS count, (COUNT(CASE payout_description WHEN 'Approved' THEN 1 END)) as approved FROM app_transactions where datediff(now(),transaction_time) <= 30 group by date(transaction_time) order by date(transaction_time) desc";

		$app_transactions_clicks_approved = $this->User->query($sql);

		$sql="SELECT x.date,round(((x.approved/x.count)*100),2) as conversion from (SELECT date(transaction_time) AS date,COUNT(*) AS count, (COUNT(CASE payout_description WHEN 'Approved' THEN 1 END)) as approved FROM app_transactions where datediff(now(),transaction_time) <= 30 group by date(transaction_time) order by date(transaction_time) desc) as x ";

		$app_transactions_percentage = $this->User->query($sql);

		$sql="select date(at.transaction_time) AS date, at.app_name,at.app_id, count(*) AS count from  (select app_id from app_transactions where datediff(curdate(),date(transaction_time))<30 group by app_id order by count(1) desc limit 5)a join app_transactions at on a.app_id = at.app_id group by date(at.transaction_time), at.app_id order by date(at.transaction_time)";

		$top_five_apps = $this->User->query($sql);		

		if(!empty($top_five_apps)){

			foreach ($top_five_apps as $key => $app) {

				$apps[$app['at']['app_id']] = $app['at']['app_name'];

				$appinstalls[$app[0]['date']][$app['at']['app_id']] = $app[0]['count'];

			}

		}

		$this->set(compact('userbase','clicks','orders','devices','preferences_set','usersWithBrandPreferencesSet','totalUsers','activeUsers','activeBuyers','dailyVerifications','app_transactions_clicks_approved','app_transactions_percentage','top_five_apps','apps','appinstalls'));

	}

}