<?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 CustomerVisit extends AppModel {
	public $belongsTo = array(
			'User' => array('className' => 'User', 'foreignKey' => 'user_id', 'fields' => array('first_name', 'last_name'), 'type'=>'inner'), 
			'Customer' => array('className' => 'Customer', 'foreignKey' => 'customer_id', 'fields' => array('first_name', 'last_name'), 'type'=>'left'), 
			'Depot' => array('className' => 'Depot', 'foreignKey' => 'depot_id', 'type'=>'inner', 'fields' => array('id', 'title','region_id')),
			'LgArea' => array('className' => 'LgArea', 'foreignKey' => 'lg_area_id', 'type'=>'left'),
			'State' => array('className' => 'State', 'foreignKey' => 'state_id', 'type'=>'left'),
		);
	
	public function visibleConditions(&$conditions, $user){
		switch($user['Role']['grade']){
			case 6:
				$conditions['CustomerVisit.user_id'] = $user['id'];
			break;
			case 5:
				$conditions['OR'] = array('CustomerVisit.user_id'=> $user['id'], 'CustomerVisit.user_id IN (SELECT uid2 FROM user_relations WHERE user_id ='. $user['id'].' )' );
			break;
			case 4:
				$conditions['CustomerVisit.depot_id'] = $user['depot_id'];
			break;
			case 3:
				$conditions['OR'] = array('CustomerVisit.user_id'=> $user['id'], 'CustomerVisit.user_id IN (SELECT id FROM users WHERE region_id ='. $user['region_id'].' )' );
			break;
		}
	}
	
	public function visibleroleConditions(&$conditions, $user, &$paginate){
		switch($user['Role']['grade']){
			case 6:
				$conditions['CustomerVisit.user_id'] = $user['id'];
			break;
			case 5:
				$paginate['joins'][] = array('table' => '(SELECT uid2 FROM user_relations WHERE user_id = '.$user['id'].')', 'type'=>'left', 'alias'=>'UserRelation', 'conditions'=>array('CustomerVisit.user_id = UserRelation.uid2'));
				
				//$conditions['OR'] = array('CustomerVisit.user_id'=> $user['id'], 'CustomerVisit.user_id IN (SELECT uid2 FROM user_relations WHERE user_id ='. $user['id'].' )' );
				$conditions['OR'] = array('CustomerVisit.user_id'=> $user['id'], 'UserRelation.uid2 IS NOT NULL' );
			break;
			case 4:
				$conditions['CustomerVisit.depot_id'] = $user['depot_id'];
			break;
			case 3:
				$paginate['joins'][] = array('table' => '(SELECT id FROM users WHERE region_id = '.$user['region_id'].')', 'type'=>'left', 'alias'=>'User2', 'conditions'=>array('CustomerVisit.user_id = User2.id'));
				
				//$conditions['OR'] = array('CustomerVisit.user_id'=> $user['id'], 'CustomerVisit.user_id IN (SELECT id FROM users WHERE region_id ='. $user['region_id'].' )' );
				$conditions['OR'] = array('CustomerVisit.user_id'=> $user['id'], 'User2.id IS NOT NULL' );
								
			break;
			case 2:
                if($user['role_id'] == DIVISIONMANAGER){
                    $conditions[] = 'CustomerVisit.depot_id IN (SELECT id FROM depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = '.$user['division_id'].'))';
                }
                break;
		}
	}

    /*Customer Visit Custom Pagination Year wise customer visits tables*/

    function customerVisitAdditionalParam($params, &$paginate, &$conditions){

        $paginate['limit'] = 20;
        $paginate['params'] = $params;
        $paginate['user'] = $params['user'];

        //return true;
    }

/* 
    public function paginate( $conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) { //pr($extra);//exit;

        $result = array();

        $cvConditions = 1;
        $cv_UnionC = "";
        $joinsStr = "";
        $params = $extra['params'];
        $user = $params['user'];
        $cvisityear_tables = $params['cvisityear_tables'];
        $order = 'CustomerVisit.created desc';

        if ($user) {
            switch ($user['Role']['grade']) {

                case 6:

                    $cvConditions = " CustomerVisit.user_id =" .$user['id'];
                    break;
                case 5:

                    $joinsStr = " LEFT JOIN (SELECT uid2 FROM user_relations WHERE user_id = ".$user['id'].") AS UserRelation ON CustomerVisit.user_id = UserRelation.uid2 ";
                    $cvConditions = " (CustomerVisit.user_id  = ".$user['id']." OR UserRelation.uid2 IS NOT NULL  )";
                    break;
                case 4:

                    // $cvConditions = " CustomerVisit.user_id =" .$user['id'];
                    //$conditions['CustomerVisit.depot_id'] = $user['depot_id'];
                    $cvConditions = " CustomerVisit.depot_id =" .$user['depot_id'];
                    break;
                case 3:

                    $joinsStr = " LEFT JOIN (SELECT id FROM users WHERE region_id = ".$user['region_id'].") AS User2 ON CustomerVisit.user_id = User2.id ";
                    $cvConditions = " (CustomerVisit.user_id  = ".$user['id']." OR User2.id IS NOT NULL  )";

                    break;
                case 2:
                    if($user['role_id'] == DIVISIONMANAGER){
                        $cvConditions = " ( CustomerVisit.depot_id IN (SELECT id FROM depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = ".$user['division_id'].")))";
                    }
                    break;

            }
        }



        switch($params['type']){
            case 'between':
                $d1 = $params['query']['from'];
                if(empty( $params['query']['to'])){
                    $d2 = date('Y-m-d');
                }else{
                    $d2 =  $params['query']['to'];
                }
                $cvConditions .= " AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
                $cv_UnionC .= " AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
                break;
            case 'monthly':
                $d1 = date('Y-m-01', $params['strtotime']);
                $d2 = date('Y-m-t', $params['strtotime']);
                $cvConditions .= " AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
                $cv_UnionC .= " AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
                break;

        }
        //echo $cvConditions;

        if ( !empty($params['query']['q']) && !empty($params['query']['q'])){
            $cvConditions .= ' AND ( 
                             concat_ws(" ", User.first_name, User.last_name) LIKE "%'.$params['query']['q'].'%"
                             OR concat_ws(" ", Customer.first_name, Customer.last_name) LIKE "%'.$params['query']['q'].'%"
                             )';
        }
        //pr($cvConditions);exit;

        if ( !empty($params['query']['mfrom']) && !empty($params['query']['mto'])){
            $cvConditions .= " AND CustomerVisit.modified between '".$params['query']['mfrom']."' AND '".$params['query']['mto']."'";
            $cv_UnionC .= " AND CustomerVisit.modified between '".$params['query']['mfrom']."' AND '".$params['query']['mto']."'";
        }


        if (!empty($params['query']['dv']) || !empty($params['query']['dp']) || !empty($params['query']['rg']) ){
            if (!empty($params['query']['dv']) && empty($params['query']['rg']) && empty($params['query']['dp']) ) {
                $cvConditions .= " AND (CustomerVisit.depot_id IN (SELECT id from depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = " . $params['query']['dv'] . ")))";
                $cv_UnionC .= " AND (CustomerVisit.depot_id IN (SELECT id from depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = " . $params['query']['dv'] . ")))";
            }
            if (!empty($params['query']['rg']) && empty($params['query']['dp']) ){
                $cvConditions .= " AND (CustomerVisit.depot_id IN (SELECT id from depots WHERE region_id = ".$params['query']['rg']."))";
                $cv_UnionC .= " AND (CustomerVisit.depot_id IN (SELECT id from depots WHERE region_id = ".$params['query']['rg']."))";
            }
            if (!empty($params['query']['dp'])){
                $cvConditions .= " AND CustomerVisit.depot_id = ".$params['query']['dp'];
                $cv_UnionC .= " AND CustomerVisit.depot_id = ".$params['query']['dp'];
            }

        }
        // if (!empty($params['query']['dp'])){
            // $cvConditions .= " AND CustomerVisit.depot_id = ".$params['query']['dp'];
            // $cv_UnionC .= " AND CustomerVisit.depot_id = ".$params['query']['dp'];
        // }

        if (!empty($params['query']['usr']) ){
            $cvConditions .= " And CustomerVisit.user_id =". $params['query']['usr'];
            $cv_UnionC .= " And CustomerVisit.user_id =". $params['query']['usr'];
        }

        //via app 
        if (isset($params['query']['via_app']) && $params['query']['via_app']!='' ){
            $cvConditions .= " AND CustomerVisit.via_app = ".$params['query']['via_app'];
        }
        
        //Not On Day Route 
        if (isset($params['query']['not_on_day_route']) && $params['query']['not_on_day_route'] == 1 ){
            $cvConditions .= " AND CustomerVisit.not_on_day_route = ".$params['query']['not_on_day_route'];
        }
            
        if(empty($params['query']['export'])){
            $start = ($page-1)*$limit;
            $limitStr = "LIMIT $start,  $limit";
        }else{
            $offset = !empty($params['query']['offset']) ? $params['query']['offset']: "" ;
            if($offset == ''){$offset = 0;}
            $limitStr = "LIMIT $offset,  10000";
        }



        if(!empty($cvisityear_tables)){
            if(count($cvisityear_tables)>1){
                $cvisityear_tabSqlArray = array();
                foreach ($cvisityear_tables as $year => $cv_tableName){
                    $cvisityear_tabSqlArray[] = " ( SELECT * FROM $cv_tableName as CustomerVisit WHERE 1 $cv_UnionC )";
                }
                $cvisityear_tabSql = "( ".implode(" UNION ALL ", $cvisityear_tabSqlArray)." )"; // UNION operation For Multiple customer_visits table as per year wise organized

            }else{
                $cvisityear_tabSql = $cvisityear_tables[key($cvisityear_tables)]; // Single customer_visits table as per year wise organized
            }
            //pr($cvisityear_tabSql);
        }else{
            $cvisityear_tabSql ='customer_visits';
        }


        $cvSQL = "SELECT CustomerVisit.*,User.first_name,User.last_name,Customer.first_name, Customer.last_name,Customer.phone,Customer.description,Depot.id, Depot.title,Depot.region_id,LgArea.*,State.* FROM $cvisityear_tabSql CustomerVisit 
 INNER JOIN users User ON User.id = CustomerVisit.user_id
 LEFT JOIN customers Customer ON Customer.id = CustomerVisit.customer_id
 INNER JOIN depots Depot ON Depot.id = CustomerVisit.depot_id 
 LEFT JOIN lg_areas LgArea ON LgArea.id = CustomerVisit.lg_area_id 
 LEFT JOIN states State ON State.id = CustomerVisit.state_id 
 $joinsStr WHERE $cvConditions
 ORDER BY $order $limitStr";

        //echo $cvSQL;


        $result = $this->query($cvSQL);
        //pr($result);exit;

        return $result;
    }

 */

/* 

    public function paginateCount($conditions = null, $recursive = 0,$extra = array()) {//pr($extra);exit;
        $countResult = array();

        $cvConditions = 1;
        $cv_UnionC="";
        $joinsStr = "";
        $params = $extra['params'];
        $user = $params['user'];
        $cvisityear_tables = $params['cvisityear_tables'];
        $order = 'CustomerVisit.created desc';

        if ($user) {
            switch ($user['Role']['grade']) {

                case 6:

                    $cvConditions = " CustomerVisit.user_id =" .$user['id'];
                    break;
                case 5:

                    $joinsStr = " LEFT JOIN (SELECT uid2 FROM user_relations WHERE user_id = ".$user['id'].") AS UserRelation ON CustomerVisit.user_id = UserRelation.uid2 ";
                    $cvConditions = " (CustomerVisit.user_id  = ".$user['id']." OR UserRelation.uid2 IS NOT NULL  )";
                    break;
                case 4:

                    //$cvConditions = " CustomerVisit.user_id =" .$user['id'];
                    $cvConditions = " CustomerVisit.depot_id =" .$user['depot_id'];
                    break;
                case 3:

                    $joinsStr = " LEFT JOIN (SELECT id FROM users WHERE region_id = ".$user['region_id'].") AS User2 ON CustomerVisit.user_id = User2.id ";
                    $cvConditions = " (CustomerVisit.user_id  = ".$user['id']." OR User2.id IS NOT NULL  )";

                    break;
                case 2:
                    if($user['role_id'] == DIVISIONMANAGER){
                        $cvConditions = " ( CustomerVisit.depot_id IN (SELECT id FROM depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = ".$user['division_id'].")))";
                    }
                    break;

            }
        }



        switch($params['type']){
            case 'between':
                $d1 = $params['query']['from'];
                if(empty( $params['query']['to'])){
                    $d2 = date('Y-m-d');
                }else{
                    $d2 =  $params['query']['to'];
                }
                $cvConditions .= " AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
                $cv_UnionC .= " AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
                break;
            case 'monthly':
                $d1 = date('Y-m-01', $params['strtotime']);
                $d2 = date('Y-m-t', $params['strtotime']);
                $cvConditions .= " AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
                $cv_UnionC .= " AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
                break;

        }
        //echo $cvConditions;

        if ( !empty($params['query']['q']) && !empty($params['query']['q'])){
            $cvConditions .= ' AND ( 
                             concat_ws(" ", User.first_name, User.last_name) LIKE "%'.$params['query']['q'].'%"
                             OR concat_ws(" ", Customer.first_name, Customer.last_name) LIKE "%'.$params['query']['q'].'%"
                             )';
        }
        //pr($cvConditions);exit;

        if ( !empty($params['query']['mfrom']) && !empty($params['query']['mto'])){
            $cvConditions .= " AND CustomerVisit.modified between '".$params['query']['mfrom']."' AND '".$params['query']['mto']."'";
            $cv_UnionC .= " AND CustomerVisit.modified between '".$params['query']['mfrom']."' AND '".$params['query']['mto']."'";
        }


        if (!empty($params['query']['dv']) || !empty($params['query']['dp']) || !empty($params['query']['rg']) ){
            if (!empty($params['query']['dv']) && empty($params['query']['rg']) && empty($params['query']['dp']) ) {
                $cvConditions .= " AND (CustomerVisit.depot_id IN (SELECT id from depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = " . $params['query']['dv'] . ")))";
                $cv_UnionC .= " AND (CustomerVisit.depot_id IN (SELECT id from depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = " . $params['query']['dv'] . ")))";
            }
            if (!empty($params['query']['rg']) && empty($params['query']['dp']) ){
                $cvConditions .= " AND (CustomerVisit.depot_id IN (SELECT id from depots WHERE region_id = ".$params['query']['rg']."))";
                $cv_UnionC .= " AND (CustomerVisit.depot_id IN (SELECT id from depots WHERE region_id = ".$params['query']['rg']."))";
            }
            if (!empty($params['query']['dp'])){
                $cvConditions .= " AND CustomerVisit.depot_id = ".$params['query']['dp'];
                $cv_UnionC .= " AND CustomerVisit.depot_id = ".$params['query']['dp'];
            }

        }
        // if (!empty($params['query']['dp'])){
            // $cvConditions .= " AND CustomerVisit.depot_id = ".$params['query']['dp'];
            // $cv_UnionC .= " AND CustomerVisit.depot_id = ".$params['query']['dp'];
        // }

        if (!empty($params['query']['usr']) ){
            $cvConditions .= " And CustomerVisit.user_id =". $params['query']['usr'];
            $cv_UnionC .= " And CustomerVisit.user_id =". $params['query']['usr'];
        }

        //via app 
        if (isset($params['query']['via_app']) && $params['query']['via_app']!='' ){
            $cvConditions .= " AND CustomerVisit.via_app = ".$params['query']['via_app'];
        }

        //Not On Day Route 
        if (isset($params['query']['not_on_day_route']) && $params['query']['not_on_day_route'] == 1 ){
            $cvConditions .= " AND CustomerVisit.not_on_day_route = ".$params['query']['not_on_day_route'];
        }


        if(!empty($cvisityear_tables)){
            if(count($cvisityear_tables)>1){
                $cvisityear_tabSqlArray = array();
                foreach ($cvisityear_tables as $year => $cv_tableName){
                    $cvisityear_tabSqlArray[] = " ( SELECT * FROM $cv_tableName as CustomerVisit WHERE 1 $cv_UnionC )";
                }
                $cvisityear_tabSql = "( ".implode(" UNION ALL ", $cvisityear_tabSqlArray)." )"; // UNION operation For Multiple customer_visits table as per year wise organized

            }else{
                $cvisityear_tabSql = $cvisityear_tables[key($cvisityear_tables)]; // Single customer_visits table as per year wise organized
            }
            //pr($cvisityear_tabSql);
        }else{
            $cvisityear_tabSql ='customer_visits';
        }



        $cvSQL = "SELECT COUNT(*) AS `count` FROM $cvisityear_tabSql CustomerVisit 
 INNER JOIN users User ON User.id = CustomerVisit.user_id
 LEFT JOIN customers Customer ON Customer.id = CustomerVisit.customer_id
 INNER JOIN depots Depot ON Depot.id = CustomerVisit.depot_id 
 LEFT JOIN lg_areas LgArea ON LgArea.id = CustomerVisit.lg_area_id 
 LEFT JOIN states State ON State.id = CustomerVisit.state_id 
 $joinsStr WHERE $cvConditions";

        //echo $cvSQL;


        $countResult = $this->query($cvSQL); //pr($countResult['0']['0']['count']);


        if(!empty($countResult['0']['0']['count'])){
            $count_total = $countResult['0']['0']['count'];
        }else{
            $count_total = 0;
        }


        return $count_total;
    }

 */
/* 
    public function customerVisitAll($params,$user=null){
        //$date = $params['date'];

        $result = array();

        $cvConditions = 1;
        $cv_UnionC ="";
        $joinsStr = "";
        $cvisityear_tables = $params['cvisityear_tables'];
        $order = 'CustomerVisit.created desc';

        if ($user) {
            switch ($user['Role']['grade']) {

                case 6:

                    $cvConditions = " CustomerVisit.user_id =" .$user['id'];
                    break;
                case 5:

                    $joinsStr = " LEFT JOIN (SELECT uid2 FROM user_relations WHERE user_id = ".$user['id'].") AS UserRelation ON CustomerVisit.user_id = UserRelation.uid2 ";
                    $cvConditions = " (CustomerVisit.user_id  = ".$user['id']." OR UserRelation.uid2 IS NOT NULL  )";
                    break;
                case 4:

                    //$cvConditions = " CustomerVisit.user_id =" .$user['id'];
                    $cvConditions = " CustomerVisit.depot_id =" .$user['depot_id'];
                    break;
                case 3:

                    $joinsStr = " LEFT JOIN (SELECT id FROM users WHERE region_id = ".$user['region_id'].") AS User2 ON CustomerVisit.user_id = User2.id ";
                    $cvConditions = " (CustomerVisit.user_id  = ".$user['id']." OR User2.id IS NOT NULL  )";

                    break;
                case 2:
                    if($user['role_id'] == DIVISIONMANAGER){
                        $cvConditions = " ( CustomerVisit.depot_id IN (SELECT id FROM depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = ".$user['division_id'].")))";
                    }
                    break;

            }
        }



        switch($params['type']){
            case 'between':
                $d1 = $params['query']['from'];
                if(empty( $params['query']['to'])){
                    $d2 = date('Y-m-d');
                }else{
                    $d2 =  $params['query']['to'];
                }
                $cvConditions .= " AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
                $cv_UnionC .= " AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
                break;
            case 'monthly':
                $d1 = date('Y-m-01', $params['strtotime']);
                $d2 = date('Y-m-t', $params['strtotime']);
                $cvConditions .= " AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
                $cv_UnionC .= " AND CustomerVisit.visiting_date between '$d1' AND '$d2 23:59:59'";
                break;

        }
        //echo $cvConditions;

        if ( !empty($params['query']['q']) && !empty($params['query']['q'])){
            $cvConditions .= ' AND ( 
                             concat_ws(" ", User.first_name, User.last_name) LIKE "%'.$params['query']['q'].'%"
                             OR concat_ws(" ", Customer.first_name, Customer.last_name) LIKE "%'.$params['query']['q'].'%"
                             )';
        }
        //pr($cvConditions);exit;

        if ( !empty($params['query']['mfrom']) && !empty($params['query']['mto'])){
            $cvConditions .= " AND CustomerVisit.modified between '".$params['query']['mfrom']."' AND '".$params['query']['mto']."'";
            $cv_UnionC .= " AND CustomerVisit.modified between '".$params['query']['mfrom']."' AND '".$params['query']['mto']."'";
        }

        if (!empty($params['query']['dv']) || !empty($params['query']['dp']) || !empty($params['query']['rg']) ){
            if (!empty($params['query']['dv']) && empty($params['query']['rg']) && empty($params['query']['dp']) ) {
                $cvConditions .= " AND (CustomerVisit.depot_id IN (SELECT id from depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = " . $params['query']['dv'] . ")))";
                $cv_UnionC .= " AND (CustomerVisit.depot_id IN (SELECT id from depots WHERE region_id IN (SELECT region_id from divisions_regions WHERE division_id = " . $params['query']['dv'] . ")))";
            }
            if (!empty($params['query']['rg']) && empty($params['query']['dp']) ){
                $cvConditions .= " AND (CustomerVisit.depot_id IN (SELECT id from depots WHERE region_id = ".$params['query']['rg']."))";
                $cv_UnionC .= " AND (CustomerVisit.depot_id IN (SELECT id from depots WHERE region_id = ".$params['query']['rg']."))";
            }
            if (!empty($params['query']['dp'])){
                $cvConditions .= " AND CustomerVisit.depot_id = ".$params['query']['dp'];
                $cv_UnionC .= " AND CustomerVisit.depot_id = ".$params['query']['dp'];
            }

        }


        // if (!empty($params['query']['dp'])){
            // $cvConditions .= " AND CustomerVisit.depot_id = ".$params['query']['dp'];
            // $cv_UnionC .= " AND CustomerVisit.depot_id = ".$params['query']['dp'];
        // }

        if (!empty($params['query']['usr']) ){
            $cvConditions .= " And CustomerVisit.user_id =". $params['query']['usr'];
            $cv_UnionC .= " And CustomerVisit.user_id =". $params['query']['usr'];
        }


        if(empty($params['query']['export'])){
            $limitStr = "";
        }else{
            $offset = !empty($params['query']['offset']) ? $params['query']['offset']: "" ;
            if($offset == ''){$offset = 0;}
            $limitStr = "LIMIT $offset,  10000";
        }





        if(!empty($cvisityear_tables)){
            if(count($cvisityear_tables)>1){
                $cvisityear_tabSqlArray = array();
                foreach ($cvisityear_tables as $year => $cv_tableName){
                    $cvisityear_tabSqlArray[] = " ( SELECT * FROM $cv_tableName as CustomerVisit WHERE 1 $cv_UnionC )";
                }
                $cvisityear_tabSql = "( ".implode(" UNION ALL ", $cvisityear_tabSqlArray)." )"; // UNION operation For Multiple customer_visits table as per year wise organized

            }else{
                $cvisityear_tabSql = $cvisityear_tables[key($cvisityear_tables)]; // Single customer_visits table as per year wise organized
            }
            //pr($cvisityear_tabSql);
        }else{
            $cvisityear_tabSql ='customer_visits';
        }


        $cvSQL = "SELECT CustomerVisit.*,User.first_name,User.last_name,Customer.first_name, Customer.last_name,Customer.phone,Customer.description,Depot.id, Depot.title,Depot.region_id,LgArea.*,State.* FROM $cvisityear_tabSql CustomerVisit 
 INNER JOIN users User ON User.id = CustomerVisit.user_id
 LEFT JOIN customers Customer ON Customer.id = CustomerVisit.customer_id
 INNER JOIN depots Depot ON Depot.id = CustomerVisit.depot_id 
 LEFT JOIN lg_areas LgArea ON LgArea.id = CustomerVisit.lg_area_id 
 LEFT JOIN states State ON State.id = CustomerVisit.state_id 
 $joinsStr WHERE $cvConditions
 ORDER BY $order $limitStr";

        //echo $cvSQL;


        $result = $this->query($cvSQL);
        //pr($result);exit;

        return $result;

    }

 */
 
 
private function buildConditions($params, $user) {
    $cvConditions = ""; // Start with an empty condition

    // Check the user role and adjust conditions accordingly
    if ($user) {
        switch ($user['Role']['grade']) {
            case 6:
                $cvConditions .= "CustomerVisit.user_id = " . $user['id'];
                break;
            case 5:
                $cvConditions .= "CustomerVisit.user_id = " . $user['id'];
                $cvConditions .= " OR CustomerVisit.user_id IN (SELECT uid2 FROM user_relations WHERE user_id = " . $user['id'] . ")";
                break;
            case 4:
                $cvConditions .= "CustomerVisit.depot_id = " . $user['depot_id'];
                break;
            case 3:
                $cvConditions .= "CustomerVisit.user_id = " . $user['id'];
                $cvConditions .= " OR CustomerVisit.user_id IN (SELECT id FROM users WHERE region_id = " . $user['region_id'] . ")";
                break;
            case 2:
                if ($user['role_id'] == DIVISIONMANAGER) {
                    $cvConditions .= "CustomerVisit.depot_id IN (SELECT id FROM depots WHERE region_id IN (SELECT region_id FROM divisions_regions WHERE division_id = " . $user['division_id'] . "))";
                }
                break;
            default:
                // Handle case where role grade is 1 or any other grade not specifically handled
                break;
        }
    }

    // Date conditions
    if ($params['type'] == 'between') {
        $d1 = $params['query']['from'];
        $d2 = empty($params['query']['to']) ? date('Y-m-d') : $params['query']['to'];
        $cvConditions .= " AND CustomerVisit.visiting_date BETWEEN '$d1' AND '$d2 23:59:59'";
    } elseif ($params['type'] == 'monthly') {
        $d1 = date('Y-m-01', $params['strtotime']);
        $d2 = date('Y-m-t', $params['strtotime']);
        $cvConditions .= " AND CustomerVisit.visiting_date BETWEEN '$d1' AND '$d2 23:59:59'";
    }

    // Additional query filters
    if (!empty($params['query']['q'])) {
        $cvConditions .= ' AND ( 
                             concat_ws(" ", User.first_name, User.last_name) LIKE "%' . $params['query']['q'] . '%"
                             OR concat_ws(" ", Customer.first_name, Customer.last_name) LIKE "%' . $params['query']['q'] . '%"
                             )';
    }

    if (!empty($params['query']['mfrom']) && !empty($params['query']['mto'])) {
        $cvConditions .= " AND CustomerVisit.modified BETWEEN '" . $params['query']['mfrom'] . "' AND '" . $params['query']['mto'] . "'";
    }

    if (!empty($params['query']['dv']) || !empty($params['query']['dp']) || !empty($params['query']['rg'])) {
        if (!empty($params['query']['dv'])) {
            $cvConditions .= " AND CustomerVisit.depot_id IN (SELECT id FROM depots WHERE region_id IN (SELECT region_id FROM divisions_regions WHERE division_id = " . $params['query']['dv'] . "))";
        }
        if (!empty($params['query']['rg'])) {
            $cvConditions .= " AND CustomerVisit.depot_id IN (SELECT id FROM depots WHERE region_id = " . $params['query']['rg'] . ")";
        }
        if (!empty($params['query']['dp'])) {
            $cvConditions .= " AND CustomerVisit.depot_id = " . $params['query']['dp'];
        }
    }

    if (!empty($params['query']['usr'])) {
        $cvConditions .= " AND CustomerVisit.user_id = " . $params['query']['usr'];
    }
	
	if (!empty($params['query']['cid'])) {
        $cvConditions .= " AND CustomerVisit.customer_id = " . $params['query']['cid'];
    }

    if (isset($params['query']['via_app']) && $params['query']['via_app'] != '') {
        $cvConditions .= " AND CustomerVisit.via_app = " . $params['query']['via_app'];
    }

    if (isset($params['query']['not_on_day_route']) && $params['query']['not_on_day_route'] == 1) {
        $cvConditions .= " AND CustomerVisit.not_on_day_route = " . $params['query']['not_on_day_route'];
    }

    // Ensure there is no leading "AND" ,  Remove the leading "AND" if present
    //$cvConditions = ltrim($cvConditions, ' AND');
	$cvConditions = preg_replace('/^(\s*AND\s*)/', '', $cvConditions);

    return $cvConditions;
}


private function buildYearTablesSql($cvisityear_tables, $cvConditions) {
    if (!empty($cvisityear_tables)) {
        if (count($cvisityear_tables) > 1) {
            $cvisityear_tabSqlArray = array();
            foreach ($cvisityear_tables as $year => $cv_tableName) {
                $cvisityear_tabSqlArray[] = " (SELECT * FROM $cv_tableName as CustomerVisit WHERE $cvConditions)";
            }
            return "( " . implode(" UNION ALL ", $cvisityear_tabSqlArray) . " )";
        } else {
            return $cvisityear_tables[key($cvisityear_tables)];
        }
    } else {
        return 'customer_visits';
    }
}

private function buildLimitString($page, $limit, $params) {
    // Default LIMIT values
    $limitString = '';

    if (empty($params['query']['export'])) {
        // Calculate start index for pagination
        $start = ($page - 1) * $limit;
        $limitString = "LIMIT $start, $limit";
    } else {
        // Use offset from query parameters or default to 0
        $offset = !empty($params['query']['offset']) ? (int)$params['query']['offset'] : 0;
        $limitString = "LIMIT $offset, 10000";
    }

    // Check if LIMIT string is empty and avoid appending it in that case
    return $limitString;
}

public function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
    $params = $extra['params'];
    $cvisityear_tables = $params['cvisityear_tables'];

    $cvConditions = $this->buildConditions($params, $extra['user']);
    $cvisityear_tabSql = $this->buildYearTablesSql($cvisityear_tables, $cvConditions);

    $limitStr = $this->buildLimitString($page, $limit, $params);
	
	$order = !empty($order) ? $order : "CustomerVisit.created desc";
	
	$order = "CustomerVisit.created desc";

    $cvSQL = "SELECT CustomerVisit.*, User.first_name, User.last_name, Customer.first_name, Customer.last_name, Customer.phone, Customer.description, Depot.id, Depot.title, Depot.region_id, LgArea.*, State.*
              FROM $cvisityear_tabSql CustomerVisit
              INNER JOIN users User ON User.id = CustomerVisit.user_id
              LEFT JOIN customers Customer ON Customer.id = CustomerVisit.customer_id
              INNER JOIN depots Depot ON Depot.id = CustomerVisit.depot_id
              LEFT JOIN lg_areas LgArea ON LgArea.id = CustomerVisit.lg_area_id
              LEFT JOIN states State ON State.id = CustomerVisit.state_id
              WHERE $cvConditions
              ORDER BY $order $limitStr";

    return $this->query($cvSQL);
}


public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
    $params = $extra['params'];
    $cvisityear_tables = $params['cvisityear_tables'];

    $cvConditions = $this->buildConditions($params, $extra['user']);
    $cvisityear_tabSql = $this->buildYearTablesSql($cvisityear_tables, $cvConditions);

    $cvSQL = "SELECT COUNT(*) AS `count`
              FROM $cvisityear_tabSql CustomerVisit
              INNER JOIN users User ON User.id = CustomerVisit.user_id
              LEFT JOIN customers Customer ON Customer.id = CustomerVisit.customer_id
              INNER JOIN depots Depot ON Depot.id = CustomerVisit.depot_id
              LEFT JOIN lg_areas LgArea ON LgArea.id = CustomerVisit.lg_area_id
              LEFT JOIN states State ON State.id = CustomerVisit.state_id
              WHERE $cvConditions";

    $countResult = $this->query($cvSQL);

    return !empty($countResult[0][0]['count']) ? $countResult[0][0]['count'] : 0;
}

public function customerVisitAll($params, $user = null) {
	
	$order = !empty($order) ? $order : "CustomerVisit.created desc";
    $cvisityear_tables = $params['cvisityear_tables'];

    $cvConditions = $this->buildConditions($params, $user);
    $cvisityear_tabSql = $this->buildYearTablesSql($cvisityear_tables, $cvConditions);

    $limitStr = $this->buildLimitString(null, null, $params); // No pagination in this method

    $cvSQL = "SELECT CustomerVisit.*, User.first_name, User.last_name, Customer.first_name, Customer.last_name, Customer.phone, Customer.description, Depot.id, Depot.title, Depot.region_id, LgArea.*, State.*
              FROM $cvisityear_tabSql CustomerVisit
              INNER JOIN users User ON User.id = CustomerVisit.user_id
              LEFT JOIN customers Customer ON Customer.id = CustomerVisit.customer_id
              INNER JOIN depots Depot ON Depot.id = CustomerVisit.depot_id
              LEFT JOIN lg_areas LgArea ON LgArea.id = CustomerVisit.lg_area_id
              LEFT JOIN states State ON State.id = CustomerVisit.state_id
              WHERE $cvConditions
              ORDER BY $order $limitStr";

    return $this->query($cvSQL);
}


 
 
	
	
	public function updateCustomerVisitYearTable($data,$cvisityear_tables){//pr($data);//pr($cvisityear_tables);exit;
        if(!empty($data['CustomerVisit']['id']) && !empty($cvisityear_tables)){

            $data['CustomerVisit']['modified'] = date('Y-m-d H:i:s');

            $set_column_valueStr = "";
            $i = 0;
            foreach ($data['CustomerVisit'] as $cName => $cValue){
                if($i>0){
                    $add_comma = " , ";
                }else{
                    $add_comma = "";
                }
                $set_column_valueStr .= ' '.$add_comma.' '.$cName.' = "'.$cValue.'"';
                $i++;
            }
            //echo $set_column_valueStr;exit;

            $result = false;

            foreach ($cvisityear_tables as $year => $cv_tableName) {
                $visitsSel[$year] = $this->query("SELECT CustomerVisit.* FROM $cv_tableName CustomerVisit 
                WHERE CustomerVisit.id =".$data['CustomerVisit']['id']);
                if(!empty($visitsSel[$year])){
                    $this->query("UPDATE $cv_tableName SET  $set_column_valueStr WHERE id = ".$data['CustomerVisit']['id']);
                    $result = true;


                }

            }
        }else{
            $result  = false;
        }
        return $result;
    }

    public function getSingleVisitYearTable($id,$cvisityear_tables){
        if(!empty($id)){

            if(!empty($cvisityear_tables)) {
                foreach ($cvisityear_tables as $year => $cv_tableName) {
                    $cvSQL = "SELECT CustomerVisit.*,User.first_name,User.last_name,Customer.first_name, Customer.last_name,Customer.phone,Customer.description,Depot.id, Depot.title,Depot.region_id,LgArea.*,State.* FROM $cv_tableName CustomerVisit 
 INNER JOIN users User ON User.id = CustomerVisit.user_id
 LEFT JOIN customers Customer ON Customer.id = CustomerVisit.customer_id
 INNER JOIN depots Depot ON Depot.id = CustomerVisit.depot_id 
 LEFT JOIN lg_areas LgArea ON LgArea.id = CustomerVisit.lg_area_id 
 LEFT JOIN states State ON State.id = CustomerVisit.state_id

WHERE CustomerVisit.id =".$id;
                    //echo $cvSQL;
                    $visitsSel[$year] = $this->query($cvSQL);
                }
            }

            $result = array();
            if (!empty($visitsSel)) {
                foreach ($visitsSel as $year => $visits) {
                    foreach ($visits as $visit) {
                        if(empty($result) && !empty($visit)){
                            $result =  $visit;
                        }
                    }
                }
            }

            //pr($result);exit;
            return $result;

        }

    }

    public function deleteSingleVisitYearTable($id,$cvisityear_tables){
        if(!empty($id) && !empty($cvisityear_tables)){

            $result = false;

            foreach ($cvisityear_tables as $year => $cv_tableName) {
                $visitsSel[$year] = $this->query("SELECT CustomerVisit.* FROM $cv_tableName CustomerVisit 
                WHERE CustomerVisit.id =".$id);
                if(!empty($visitsSel[$year])){
                    $this->query("DELETE FROM $cv_tableName WHERE id = ".$id);
                    $result = true;


                }

            }
        }else{
            $result  = false;
        }
        return $result;
    }

    /*-----------------------------------------------------
     * Function: customer_visits_table_auto_organise
     * Desc: rename current customer_visits table to customer_visits_[YEAR] (eg: customer_visits_2021)
     * create a structure of renamed table to customer_visits with autoincrement value to the table
     * run via cron
     * Note: [YEAR] is the year value of the just prev year of the new year
     * CRON JOB WILL FIRE FROM 72.. CRM SERVER
     * Author: Sridhar
     * Date: 15 Nov 2021
     *-----------------------------------------------------*/
    public function customer_visits_table_auto_organise(){
        $yearValue = date('Y', strtotime("-1 days"));   //1 day back
        $today = date('Y-m-d H:i:s');
        //$today = date('2021-01-01 00:00:00'); //comment after test
        $todayMonth = date('m', strtotime($today));
        $todayDay = date('d', strtotime($today));
        $todayHour = date('H', strtotime($today));
        $todayMinute = date('i', strtotime($today));
        //pr($todayMonth);pr($todayDay);pr($todayHour); pr($todayMinute);
        //pr(date('H:i:s'));exit;

        $current_cvTableName = "customer_visits";
        $cvTableName_year = "customer_visits_".$yearValue;



        $databaseName = !empty(ConnectionManager::$config->default['database']) ? ConnectionManager::$config->default['database'] : "smalutio_distribution";

        $its_time_to_run = false;
        //$its_time_to_run = true;  // comment after test

        //DATE and TIME check
        if(($todayMonth == 12 && $todayDay==31 && $todayHour >= 23 && $todayMinute==59) || ($todayMonth == 1 && $todayDay==1 && $todayHour < 1 && $todayMinute <= 1 )){
            $its_time_to_run = true;
        }

        //pr($its_time_to_run);exit;


        if($its_time_to_run){
            //QUERY FOR RUN PROCESS

            $cvSQL = "
            SET @ytabname = '$cvTableName_year';
SET @s:=CONCAT('RENAME TABLE `$databaseName`.`$current_cvTableName` TO `$databaseName`.', @ytabname);

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @s:=CONCAT('CREATE TABLE `$databaseName`.`$current_cvTableName` LIKE `$databaseName`.', @ytabname);

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


SET @s:=CONCAT('SELECT `AUTO_INCREMENT` INTO @AutoInc
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = \"$databaseName\" AND TABLE_NAME =\"', @ytabname,'\"');


PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @s:=CONCAT('ALTER TABLE `$databaseName`.`$current_cvTableName` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=', @AutoInc);

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

            
            ";

            //echo $cvSQL; exit;


            $res = $this->query($cvSQL); //pr($res);

            return $res;



        }else{
            return false;
        }

    }

}