<?php

App::uses('Model', 'Model');

/**
 * Application model for Cake.
 *
 * Add your application-wide methods in the class below, your models
 * will inherit them.
 *
 * @package       app.Model
 */

class User extends AppModel { 

	var $virtualFields = array(
		'name' => "CONCAT(User.first_name,' ', User.last_name)"
	);

	public $belongsTo = array(
						'State' => array('className' => 'State', 'foreignKey' => 'state_id', 'fields' => array('id', 'state'), 'type'=>'left'), 
						'Division' => array('className' => 'Division', 'foreignKey' => 'division_id', 'fields' => array('id', 'title'), 'type'=>'left'),
						'Region' => array('className' => 'Region', 'foreignKey' => 'region_id', 'fields' => array('id', 'title'), 'type'=>'left'),
						'Depot' => array('className' => 'Depot', 'foreignKey' => 'depot_id', 'fields' => array('id', 'title'), 'type'=>'left'),
						'Role' => array('className' => 'Role', 'foreignKey' => 'role_id',  'type'=>'left'),
						'File' => array('className' => 'File', 'foreignKey' => 'file_id', 'fields' => array('id', 'name'), 'type'=>'left'),
			);
    
	function getrolepermission($id){
		 $permissionData = $this->query("SELECT p.controller,p.action FROM `rolepermissions` as r,permissions p where r.permission_id = p.id and r.`role_id`='".$id."' ");
		 return $permissionData;
	}
	
	public function usersList($depot_id=0){
		$conditions = array('User.status'=>ACTIVE);
		if ($depot_id){
			$conditions['User.depot_id'] = $depot_id;
		}
		$users = $this->find('all', array('fields' => array('User.id', 'CONCAT_WS(" ", User.first_name, User.last_name) as name'), 'conditions'=>$conditions, 'order'=>array('name'=>'asc')));
		$list = array();
		if($users){
			foreach($users as $user){
				$list[$user['User']['id']] = $user['0']['name'];
			}
		}
		return $list;
	}
	
	public function usersListByRole($user, $isService=false, $varified=false){
		$conditions = array('User.status'=>ACTIVE);
		
		if($varified){
			$conditions['Role.grade <='] = 3;
		}
		switch($user['Role']['grade']){
			case 2:
				if($user['role_id'] == DIVISIONMANAGER){
					$conditions['User.division_id'] = $user['division_id'];
				}								
			break;
			case 3:
				$conditions['User.region_id'] = $user['region_id'];
				$conditions['AND']=array('User.depot_id IS NOT null') ;//= 'NOT NULL'
			break;
			case 4:
				$conditions['User.depot_id'] = $user['depot_id'];
			break;
			case 5:
				$conditions[] = 'User.id IN (SELECT uid2 FROM user_relations WHERE user_id = '.$user['id'].')';
			break;
			case 6:
				$conditions['User.id'] = $user['id'];
			break;
		}
		//pr($conditions);
		$users = $this->find('all', array('fields' => array('User.id', 'CONCAT_WS(" ", User.first_name, User.last_name) as name'), 'conditions'=>$conditions, 'order'=>array('name'=>'asc')));
		$list = array();
		$jsonList = array();
		if($users){
			foreach($users as $user){
				$list[$user['User']['id']] = $user['0']['name'];
				$jsonList[] = array('id'=>$user['User']['id'], 'value' => $user['0']['name']);
			}
		}
		if($isService){
			return $jsonList;
		}else{
			return $list;
		}
	}
	
	
	function currentmonthVisit($user = null){
		$d1 = date('Y-m-01');
		$d2 = date('Y-m-d', strtotime("+1 days"));
		$conditions = "";
		
		if ($user){
			switch($user['Role']['grade']){
				case 2:
				if($user['role_id'] == DIVISIONMANAGER){
					$conditions = "AND User.division_id = ".$user['division_id'];
				}								
				break;
				case 3:
					$conditions = "AND User.region_id = ".$user['region_id'];
				break;
				case 4:
					$conditions = "AND User.depot_id = ".$user['depot_id'];
				break;
				case 5:
					$conditions = "AND User.id IN (SELECT uid2 FROM user_relations WHERE user_id = ".$user['id'].")";
				break;
				case 6:
					$conditions = "AND User.id = ".$user['id'];
				break;
			}
		}
		$visits = $this->query("SELECT User.id, User.first_name, User.last_name, (COUNT(Order.id) + (SELECT COUNT(id) FROM customer_visits WHERE user_id = User.id AND visiting_date BETWEEN '$d1' AND '$d2') + (SELECT COUNT(id) FROM redistributor_sales WHERE user_id = User.id AND sale_date BETWEEN '$d1' AND '$d2' ) ) AS totVisits FROM users AS User LEFT JOIN orders AS `Order` ON (User.id=Order.user_id AND Order.order_date BETWEEN '$d1' AND '$d2') WHERE User.status = ".ACTIVE." $conditions GROUP BY User.id ORDER BY totVisits desc LIMIT 20");
		return $visits;
	}
	
	/* function saleActivity(&$users, $params){
		$userIds = array();
		foreach($users as $user){
			$userIds[] = $user['User']['id'];
		}
		
		//$sale_activity = $this->query("SELECT `Sale`.`user_id`, `Sale`.`brand_id`, (IFNULL(SUM(Sale.cal_qty),0) + IFNULL((SELECT SUM(cal_qty) FROM redistributor_saleitems WHERE user_id = Sale.user_id AND sale_date LIKE \"".$params['date']."%\" AND brand_id = Sale.brand_id ),0)) AS total_sale, (IFNULL(SUM(Sale.amount),0) + IFNULL((SELECT SUM(amount) FROM redistributor_saleitems WHERE user_id = Sale.user_id AND sale_date LIKE \"".$params['date']."%\" AND brand_id = Sale.brand_id),0)) AS total_amount FROM  `sales` AS `Sale` WHERE `Sale`.`user_id` IN (".implode(",", $userIds).") AND `Sale`.`sale_date` LIKE \"".$params['date']."%\" GROUP BY `Sale`.`user_id`, `Sale`.`brand_id` ");
		
		//$sale_activity = $this->query("SELECT `Sale`.`user_id`, `Sale`.`brand_id`, (IFNULL(SUM(Sale.cal_qty),0) + IFNULL(rdSale.rdQty,0) ) AS total_sale, (IFNULL(SUM(Sale.amount),0) + IFNULL(rdSale.rdAmt,0)) AS total_amount FROM `sales` AS `Sale` LEFT JOIN (SELECT user_id, brand_id, SUM(cal_qty) AS rdQty, SUM(amount) AS rdAmt FROM redistributor_saleitems WHERE sale_date LIKE \"".$params['date']."%\" GROUP BY user_id, brand_id ) AS rdSale ON rdSale.user_id = `Sale`.`user_id` AND rdSale.brand_id = `Sale`.`brand_id` WHERE `Sale`.`user_id` IN (".implode(",", $userIds).") AND `Sale`.`sale_date` LIKE \"".$params['date']."%\" GROUP BY `Sale`.`user_id`, `Sale`.`brand_id` ");
		
		//$activity = array();
		//foreach($sale_activity as $sa){
		//	$uid = $sa['Sale']['user_id'];
		//	$bid = $sa['Sale']['brand_id'];
		//	$activity[$uid][$bid ] = $sa['0'];
		//}//
		
		$qry = "SELECT user_id, brand_id,brand_group_id, SUM(cal_qty) AS total_sale, SUM(amount) AS total_amount, sale_date FROM sales WHERE sale_date LIKE '".$params['date']."%' AND user_id IN (".implode(",", $userIds).") GROUP BY user_id, brand_group_id  UNION
		SELECT user_id, brand_id, brand_group_id, SUM(cal_qty) AS total_sale, SUM(amount) AS total_amount, sale_date FROM redistributor_saleitems WHERE sale_date LIKE '".$params['date']."%' AND user_id IN (".implode(",", $userIds).") GROUP BY user_id, brand_group_id UNION
		SELECT user_id, brand_id, brand_group_id, SUM(cal_qty) AS total_sale, SUM(amount) AS total_amount, sale_date FROM consumer_sales WHERE sale_date LIKE '".$params['date']."%' AND user_id IN (".implode(",", $userIds).") GROUP BY user_id, brand_group_id  UNION
		SELECT user_id, brand_id, brand_group_id, SUM(cal_qty) AS total_sale, SUM(amount) AS total_amount, sale_date FROM consumer_redistribution_sales WHERE sale_date LIKE '".$params['date']."%' AND user_id IN (".implode(",", $userIds).") GROUP BY user_id, brand_group_id";
		
		//pr($qry);
		
		//$qry = "SELECT User.id, IFNULL(`Sale`.`brand_id`,rdSale.brand_id) AS brandId, (IFNULL(SUM(Sale.cal_qty),0) + IFNULL(rdSale.rdQty,0)) AS total_sale, (IFNULL(SUM(Sale.amount),0) + IFNULL(rdSale.rdAmt,0)) AS total_amount 				
			//	FROM users AS User LEFT JOIN sales AS Sale ON Sale.user_id = User.id AND Sale.sale_date LIKE '".$params['date']."%'
			//	LEFT JOIN (SELECT user_id, brand_id, SUM(cal_qty) AS rdQty, SUM(amount) AS rdAmt, sale_date FROM redistributor_saleitems WHERE sale_date LIKE '".$params['date']."%' GROUP BY user_id, brand_id ) AS rdSale ON rdSale.user_id = User.id 
			//	WHERE  User.id IN (".implode(",", $userIds).") GROUP BY User.`id`, brandId";//
		$sale_activity = $this->query($qry);
		$activity = array();
		foreach($sale_activity as $sa){
			$uid = $sa['0']['user_id'];
			$bid = $sa['0']['brand_group_id'];
			//$activity[$uid][$bid ] = $sa['0'];
			
			
			
			if(isset($activity[$uid][$bid]['total_sale'])){
				$activity[$uid][$bid]['total_sale'] += $sa['0']['total_sale'];
			}else{
				$activity[$uid][$bid]['total_sale'] = $sa['0']['total_sale'];
			}
			
			if(isset($activity[$uid][$bid]['total_amount'])){
				$activity[$uid][$bid]['total_amount'] += $sa['0']['total_amount'];
			}else{
				$activity[$uid][$bid]['total_amount'] = $sa['0']['total_amount'];
			}
			
		}
		
		foreach($users as $key=>$user){
			$user_id = $user['User']['id'];
			$users[$key]['sales'] = $activity[$user_id];			
		}
		//pr($sale_activity);
	} */
	
	
	
	function saleActivity(&$users, $params){
	    //pr($params);exit;
        $date = $params['date'];
		$userIds = array();
		foreach($users as $user){
			$userIds[] = $user['User']['id'];
		}
		//pr($userIds);exit;
		//$sale_activity = $this->query("SELECT `Sale`.`user_id`, `Sale`.`brand_id`, (IFNULL(SUM(Sale.cal_qty),0) + IFNULL((SELECT SUM(cal_qty) FROM redistributor_saleitems WHERE user_id = Sale.user_id AND sale_date LIKE \"".$params['date']."%\" AND brand_id = Sale.brand_id ),0)) AS total_sale, (IFNULL(SUM(Sale.amount),0) + IFNULL((SELECT SUM(amount) FROM redistributor_saleitems WHERE user_id = Sale.user_id AND sale_date LIKE \"".$params['date']."%\" AND brand_id = Sale.brand_id),0)) AS total_amount FROM  `sales` AS `Sale` WHERE `Sale`.`user_id` IN (".implode(",", $userIds).") AND `Sale`.`sale_date` LIKE \"".$params['date']."%\" GROUP BY `Sale`.`user_id`, `Sale`.`brand_id` ");
		
		/*$sale_activity = $this->query("SELECT `Sale`.`user_id`, `Sale`.`brand_id`, (IFNULL(SUM(Sale.cal_qty),0) + IFNULL(rdSale.rdQty,0) ) AS total_sale, (IFNULL(SUM(Sale.amount),0) + IFNULL(rdSale.rdAmt,0)) AS total_amount FROM `sales` AS `Sale` LEFT JOIN (SELECT user_id, brand_id, SUM(cal_qty) AS rdQty, SUM(amount) AS rdAmt FROM redistributor_saleitems WHERE sale_date LIKE \"".$params['date']."%\" GROUP BY user_id, brand_id ) AS rdSale ON rdSale.user_id = `Sale`.`user_id` AND rdSale.brand_id = `Sale`.`brand_id` WHERE `Sale`.`user_id` IN (".implode(",", $userIds).") AND `Sale`.`sale_date` LIKE \"".$params['date']."%\" GROUP BY `Sale`.`user_id`, `Sale`.`brand_id` ");
		
		$activity = array();
		foreach($sale_activity as $sa){
			$uid = $sa['Sale']['user_id'];
			$bid = $sa['Sale']['brand_id'];
			$activity[$uid][$bid ] = $sa['0'];
		}*/
        $cond ='';

        if(isset($params['type'])){
            switch($params['type']){
                case "daily":
                    $cond .= " AND sale_date = '$date'";
                    break;
                case 'weekly':
                    $d1 = date('Y-m-d', $params['strtotime']);
                    $d2 =  date('Y-m-d', strtotime('+6 days', $params['strtotime']));
                    $cond .= " AND sale_date between '".$d1."' AND '".$d2."'";
                    break;
                case "between":
                    if ( !empty( $params['query']['from']) && !empty( $params['query']['to'])){
                        $cond .= ' AND sale_date BETWEEN "'. $params['query']['from'].'" AND "'. $params['query']['to'].'"';

                    } elseif (!empty( $params['query']['from'])){
                        $cond .= ' AND sale_date >= "'. $params['query']['from'].'"';

                    } elseif (!empty( $params['query']['to'])){
                        $d1 = date('Y-m-d', strtotime('-1 day', strtotime($params['query']['to'])));
                        $cond .= ' AND sale_date BETWEEN "'. $d1.'" AND "'. $params['query']['to'].'"';
                        //$cond .= ' AND sale_date <= "'. $params['query']['to'].'"';

                    }

                    break;
                case "monthly":
                    $d1 = date('Y-m-01', $params['strtotime']);
                    $d2 = date('Y-m-t', $params['strtotime']);

                    $cond .= " AND sale_date between '".$d1."' AND '".$d2."'";

                    break;
            }
        }


        if (!empty($params['depot_id']) || !empty($params['region_id']) ){
            if (!empty($params['region_id']) && empty($params['depot_id']) ){
                $cond .=' AND depot_id IN (SELECT id from depots WHERE region_id = '.$params['region_id'].')';
            }
            if (!empty($params['depot_id'])){
                $cond .=' AND depot_id='.$params['depot_id'];
            }
        }

        if($params['brand_group_id']){
		    $cond .= ' AND brand_group_id='.$params['brand_group_id'];
        }

        //pr($cond);exit;
		$qry = "SELECT user_id, brand_id,brand_group_id, SUM(cal_qty) AS total_sale, SUM(amount) AS total_amount, sale_date FROM sales WHERE   user_id IN (".implode(",", $userIds).") $cond GROUP BY user_id, brand_group_id  UNION
		SELECT user_id, brand_id, brand_group_id, SUM(cal_qty) AS total_sale, SUM(amount) AS total_amount, sale_date FROM redistributor_saleitems WHERE   user_id IN (".implode(",", $userIds).") $cond GROUP BY user_id, brand_group_id UNION
		SELECT user_id, brand_id, brand_group_id, SUM(cal_qty) AS total_sale, SUM(amount) AS total_amount, sale_date FROM consumer_sales WHERE   user_id IN (".implode(",", $userIds).") $cond GROUP BY user_id, brand_group_id  UNION
		SELECT user_id, brand_id, brand_group_id, SUM(cal_qty) AS total_sale, SUM(amount) AS total_amount, sale_date FROM consumer_redistribution_sales WHERE   user_id IN (".implode(",", $userIds).") $cond GROUP BY user_id, brand_group_id";


		
		//pr($qry);exit;
		
		/*$qry = "SELECT User.id, IFNULL(`Sale`.`brand_id`,rdSale.brand_id) AS brandId, (IFNULL(SUM(Sale.cal_qty),0) + IFNULL(rdSale.rdQty,0)) AS total_sale, (IFNULL(SUM(Sale.amount),0) + IFNULL(rdSale.rdAmt,0)) AS total_amount 				
				FROM users AS User LEFT JOIN sales AS Sale ON Sale.user_id = User.id AND Sale.sale_date LIKE '".$params['date']."%'
				LEFT JOIN (SELECT user_id, brand_id, SUM(cal_qty) AS rdQty, SUM(amount) AS rdAmt, sale_date FROM redistributor_saleitems WHERE sale_date LIKE '".$params['date']."%' GROUP BY user_id, brand_id ) AS rdSale ON rdSale.user_id = User.id 
				WHERE  User.id IN (".implode(",", $userIds).") GROUP BY User.`id`, brandId";*/
		$sale_activity = $this->query($qry); //pr($sale_activity);exit;
		$activity = array();
		foreach($sale_activity as $sa){
			$uid = $sa['0']['user_id'];
			$bid = $sa['0']['brand_group_id'];
			//$activity[$uid][$bid ] = $sa['0'];
			
			
			
			if(isset($activity[$uid][$bid]['total_sale'])){
				$activity[$uid][$bid]['total_sale'] += $sa['0']['total_sale'];
			}else{
				$activity[$uid][$bid]['total_sale'] = $sa['0']['total_sale'];
			}
			
			if(isset($activity[$uid][$bid]['total_amount'])){
				$activity[$uid][$bid]['total_amount'] += $sa['0']['total_amount'];
			}else{
				$activity[$uid][$bid]['total_amount'] = $sa['0']['total_amount'];
			}
			
		}
		
		foreach($users as $key=>$user){
			$user_id = $user['User']['id'];
			$users[$key]['sales'] = isset($activity[$user_id])?  $activity[$user_id] : array();
		}
		//pr($sale_activity);
	}
	
	function updateCustRegDepot($data,$user){
		if(!empty($data['region_id']) && !empty($data['depot_id'])){ 
			$this->query("UPDATE `customers` SET `region_id`= ".$data['region_id'].",`depot_id`=".$data['depot_id']." WHERE `user_id` = ".$user."");
		}else if(!empty($data['region_id']) && empty($data['depot_id'])){
			$this->query("UPDATE `customers` SET `region_id`= ".$data['region_id']." WHERE `user_id` = ".$user."");
		}else if(!empty($data['depot_id']) && empty($data['region_id'])){
			$this->query("UPDATE `customers` SET `depot_id`=".$data['depot_id']." WHERE `user_id` = ".$user."");
		}
	}
}