<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Best_users extends CI_Controller {
	public function __construct(){
		parent::__construct();
		$this->load->model('common_model');
		if(get_cookie('admin_id'))
		{
			$this->session->set_userdata('admin_id',get_cookie('admin_id'));
		}
		if(!$this->session->userdata('admin_id'))
		{
			redirect('admin/login');
		}
	}
	public function index(){
		$content_data = array();
		if($this->input->post('year')){
			if($this->input->post('year')=='all'){
				$qryBest = $this->db->query("select *,sum(count_book) count_book,sum(count_participation) count_participation,sum(booking_total) booking_total, sum(checkin_sum) checkin_sum from (
    
								 SELECT shop_carts.user_id,count(case when (is_booking = 1) then 1 end) count_book,count(case when (checkin_amount > 0) then 1 end) count_participation,checkin_amount,username,sum(total_sum) booking_total,sum(checkin_amount) as checkin_sum,path,filename,extension FROM `shop_booking_details` left join shop_carts on shop_carts.id = shop_booking_details.cart_id left join shop_orders on shop_carts.id = shop_orders.cart_id left join evt_events on evt_events.id = shop_booking_details.event_id left join feu_userdetails on shop_carts.user_id = feu_userdetails.user_id left join sys_files on sys_files.id = feu_userdetails.avatar_file_id WHERE payed = 1 and is_booking = 1 and shop_booking_details.feature_type = 1 AND username is not NULL group by shop_carts.user_id 
								    
								    union ALL
								 
								    
								 SELECT shop_carts.user_id,count(case when (event_id > 0) then 1 end) count_book,count(case when (checkin_amount > 0) then 1 end) count_participation,checkin_amount,username,sum(total_sum) booking_total,sum(checkin_amount) as checkin_sum,path,filename,extension FROM `shop_articles_carts` left join shop_carts on shop_carts.id = shop_articles_carts.cart_id left join shop_orders on shop_carts.id = shop_orders.cart_id left join evt_events on evt_events.id = shop_articles_carts.event_id left join feu_userdetails on shop_carts.user_id = feu_userdetails.user_id left join sys_files on sys_files.id = feu_userdetails.avatar_file_id WHERE payed = 1 and event_id > 0 and username is not NULL group by shop_carts.user_id 
								    
								) a GROUP BY a.user_id ORDER BY a.booking_total DESC");
				$resultBest = $qryBest->result_array();
				$resultBest1 = array();
			} else {
				$qryBest = $this->db->query("SELECT shop_carts.user_id,count(case when (is_booking = 1) then 1 end) count_book,count(case when (checkin_amount > 0) then 1 end) count_participation,checkin_amount,username,sum(total_sum) booking_total,sum(checkin_amount) as checkin_sum,path,filename,extension FROM `shop_booking_details` left join shop_carts on shop_carts.id = shop_booking_details.cart_id left join shop_orders on shop_carts.id = shop_orders.cart_id left join evt_events on evt_events.id = shop_booking_details.event_id left join feu_userdetails on shop_carts.user_id = feu_userdetails.user_id left join sys_files on sys_files.id = feu_userdetails.avatar_file_id WHERE payed = 1 and is_booking = 1 and YEAR(FROM_UNIXTIME(time_from)) = ".$this->input->post('year')." and shop_booking_details.feature_type = 1 AND username is not NULL group by shop_carts.user_id ORDER BY sum(total_sum) DESC");
				$resultBest = $qryBest->result_array();

				$qryBest1 = $this->db->query("SELECT shop_carts.user_id,count(case when (event_id > 0) then 1 end) count_book,count(case when (checkin_amount > 0) then 1 end) count_participation,checkin_amount,username,sum(total_sum) booking_total,sum(checkin_amount) as checkin_sum,path,filename,extension FROM `shop_articles_carts` left join shop_carts on shop_carts.id = shop_articles_carts.cart_id left join shop_orders on shop_carts.id = shop_orders.cart_id left join evt_events on evt_events.id = shop_articles_carts.event_id left join feu_userdetails on shop_carts.user_id = feu_userdetails.user_id left join sys_files on sys_files.id = feu_userdetails.avatar_file_id WHERE payed = 1 and event_id > 0 and YEAR(FROM_UNIXTIME(time_from)) = ".$this->input->post('year')." and username is not NULL group by shop_carts.user_id ORDER BY sum(total_sum) DESC");
				$resultBest1 = $qryBest1->result_array();
			}
			
			$content_data['resultBest']= array_merge($resultBest, $resultBest1);
		} else {
			$qryBest = $this->db->query("SELECT shop_carts.user_id,count(case when (is_booking = 1) then 1 end) count_book,count(case when (checkin_amount > 0) then 1 end) count_participation,checkin_amount,username,sum(total_sum) booking_total,sum(checkin_amount) as checkin_sum,path,filename,extension FROM `shop_booking_details` left join shop_carts on shop_carts.id = shop_booking_details.cart_id left join shop_orders on shop_carts.id = shop_orders.cart_id left join evt_events on evt_events.id = shop_booking_details.event_id left join feu_userdetails on shop_carts.user_id = feu_userdetails.user_id left join sys_files on sys_files.id = feu_userdetails.avatar_file_id WHERE payed = 1 and is_booking = 1 and YEAR(FROM_UNIXTIME(time_from)) = ".date('Y')." and shop_booking_details.feature_type = 1  AND username is not NULL group by shop_carts.user_id ORDER BY sum(total_sum) DESC");
			$content_data['resultBest'] = $resultBest = $qryBest->result_array();
		}
		$year = date('Y');
		if($this->input->post('year')){
			$year = $this->input->post('year');
		}
		$arr = [];
		
		if($content_data['resultBest']){
			foreach ($content_data['resultBest'] as $key => $value) {
				$months = [];
				if($this->input->post('year')){
					if($this->input->post('year')=='all'){
						$where = "is_booking = 0 AND payed = 1 AND user_id = ".$value['user_id']." AND article_id in (4,5)";
						$membership = $this->common_model->getAllRowMultipleJoin('shop_orders',$where,array(array('shop_carts','shop_carts.id = shop_orders.cart_id'),array('shop_articles_carts','shop_carts.id = shop_articles_carts.cart_id')),'','sum(total_sum) order_total','shop_carts.user_id');
						if($membership){
							$content_data['resultBest'][$key]['order_total'] = $membership['order_total'];
							$content_data['resultBest'][$key]['full_total'] = $value['booking_total'] + $membership['order_total'];
						}
					} else {
						$where = "YEAR(FROM_UNIXTIME(shop_orders.created)) = ".$year." AND is_booking = 0 AND payed = 1 AND user_id = ".$value['user_id']." AND article_id in (4,5)";
						$membership = $this->common_model->getAllRowMultipleJoin('shop_orders',$where,array(array('shop_carts','shop_carts.id = shop_orders.cart_id'),array('shop_articles_carts','shop_carts.id = shop_articles_carts.cart_id')),'','sum(total_sum) order_total','shop_carts.user_id');
						if($membership){
							$content_data['resultBest'][$key]['order_total'] = $membership['order_total'];
							$content_data['resultBest'][$key]['full_total'] = $value['booking_total'] + $membership['order_total'];
						}
					} 
				} else {
					$where = "YEAR(FROM_UNIXTIME(shop_orders.created)) = ".$year." AND is_booking = 0 AND payed = 1 AND user_id = ".$value['user_id']." AND article_id in (4,5)";
					$membership = $this->common_model->getAllRowMultipleJoin('shop_orders',$where,array(array('shop_carts','shop_carts.id = shop_orders.cart_id'),array('shop_articles_carts','shop_carts.id = shop_articles_carts.cart_id')),'','sum(total_sum) order_total','shop_carts.user_id');
					if($membership){
						$content_data['resultBest'][$key]['order_total'] = $membership['order_total'];
						$content_data['resultBest'][$key]['full_total'] = $value['booking_total'] + $membership['order_total'];
					}
				}
				if($this->input->post('year')){
					if($this->input->post('year')=='all'){
						for($i=2014; $i<=date('Y');$i++){
							$months = [];
							$qryparticipate = $this->db->query("SELECT YEAR(FROM_UNIXTIME(time_from)), MONTH(FROM_UNIXTIME(time_from)) month, order_id, event_id FROM `shop_booking_details` left join shop_carts on shop_carts.id = shop_booking_details.cart_id left join shop_orders on shop_carts.id = shop_orders.cart_id left join evt_events on evt_events.id = shop_booking_details.event_id WHERE shop_carts.user_id =  ".$value['user_id']." and payed = 1 and YEAR(FROM_UNIXTIME(time_from)) = ".$i." GROUP BY shop_booking_details.cart_id ORDER BY month ASC");
							$resultparticipate = $qryparticipate->result_array();
							
							$qryparticipate_old = $this->db->query("SELECT YEAR(FROM_UNIXTIME(time_from)), MONTH(FROM_UNIXTIME(time_from)) month, order_id, event_id FROM `shop_articles_carts` left join shop_carts on shop_carts.id = shop_articles_carts.cart_id left join shop_orders on shop_carts.id = shop_orders.cart_id left join evt_events on evt_events.id = shop_articles_carts.event_id WHERE shop_carts.user_id = ".$value['user_id']." and payed = 1 and YEAR(FROM_UNIXTIME(time_from)) = ".$i." GROUP BY shop_articles_carts.cart_id ORDER BY month ASC");
							$resultparticipate_old = $qryparticipate_old->result_array();

							$resultparticipate = array_merge($resultparticipate, $resultparticipate_old);
							//pr($resultparticipate);
							if($resultparticipate){
								foreach ($resultparticipate as $k => $val) {
									array_push($months, $val['month']);
								}
							}
							$content_data['resultBest'][$key]['years'][$i]['months'] = $months;
						}
					} else {
						$qryparticipate = $this->db->query("SELECT MONTH(FROM_UNIXTIME(time_from)) month, order_id, event_id FROM `shop_booking_details` left join shop_carts on shop_carts.id = shop_booking_details.cart_id left join shop_orders on shop_carts.id = shop_orders.cart_id left join evt_events on evt_events.id = shop_booking_details.event_id WHERE shop_carts.user_id =  ".$value['user_id']." and payed = 1 and YEAR(FROM_UNIXTIME(time_from)) = ".$year." GROUP BY shop_booking_details.cart_id ORDER BY month ASC");
						$resultparticipate = $qryparticipate->result_array();
						
						$qryparticipate_old = $this->db->query("SELECT MONTH(FROM_UNIXTIME(time_from)) month, order_id, event_id FROM `shop_articles_carts` left join shop_carts on shop_carts.id = shop_articles_carts.cart_id left join shop_orders on shop_carts.id = shop_orders.cart_id left join evt_events on evt_events.id = shop_articles_carts.event_id WHERE shop_carts.user_id = ".$value['user_id']." and payed = 1 and YEAR(FROM_UNIXTIME(time_from)) = ".$year." GROUP BY shop_articles_carts.cart_id ORDER BY month ASC");
						$resultparticipate_old = $qryparticipate_old->result_array();

						$resultparticipate = array_merge($resultparticipate, $resultparticipate_old);

						if($resultparticipate){
							foreach ($resultparticipate as $k => $val) {
								array_push($months, $val['month']);
							}
						}
						$content_data['resultBest'][$key]['months'] = $months;
					}
				} else {
					$qryparticipate = $this->db->query("SELECT MONTH(FROM_UNIXTIME(time_from)) month, order_id, event_id FROM `shop_booking_details` left join shop_carts on shop_carts.id = shop_booking_details.cart_id left join shop_orders on shop_carts.id = shop_orders.cart_id left join evt_events on evt_events.id = shop_booking_details.event_id WHERE shop_carts.user_id =  ".$value['user_id']." and payed = 1 and YEAR(FROM_UNIXTIME(time_from)) = ".$year." GROUP BY shop_booking_details.cart_id ORDER BY month ASC");
					$resultparticipate = $qryparticipate->result_array();
					
					$qryparticipate_old = $this->db->query("SELECT MONTH(FROM_UNIXTIME(time_from)) month, order_id, event_id FROM `shop_articles_carts` left join shop_carts on shop_carts.id = shop_articles_carts.cart_id left join shop_orders on shop_carts.id = shop_orders.cart_id left join evt_events on evt_events.id = shop_articles_carts.event_id WHERE shop_carts.user_id = ".$value['user_id']." and payed = 1 and YEAR(FROM_UNIXTIME(time_from)) = ".$year." GROUP BY shop_articles_carts.cart_id ORDER BY month ASC");
					$resultparticipate_old = $qryparticipate_old->result_array();

					$resultparticipate = array_merge($resultparticipate, $resultparticipate_old);

					if($resultparticipate){
						foreach ($resultparticipate as $k => $val) {
							array_push($months, $val['month']);
						}
					}
					$content_data['resultBest'][$key]['months'] = $months;
				}
				
			}
		}
		foreach ($content_data['resultBest'] as $keys => $rows)
		{
		    $vc_array_name[$keys] = $rows['full_total'];
		}
		array_multisort($vc_array_name, SORT_DESC, $content_data['resultBest']);
		if($this->input->post('year')){
			if($this->input->post('year')=='all'){
				//pr($content_data['resultBest']);
			}
		}
		
		$data['loginDetails'] = $this->common_model->getAllRow('feu_userdetails',array('id'=>$this->session->userdata('admin_id')),array('usr_users','feu_userdetails.user_id = usr_users.id')) ;
		
		$data['content'] = $this->load->view('admin/users/best_users_list', $content_data, TRUE);
		$this->load->view('admin/layout', $data);
	}
}