<?php

class StatController extends BaseController {

	/**
	 * Constructor
	 */
	public function __construct() {
		// Establish Filters
		$this->beforeFilter('auth');
		$this->beforeFilter('hasRole:' . Role::ROLE_CRM_STAT);

		if (Auth::check()) {
			$this->user			 = Auth::user();
			$this->projects	 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->get();
		}
		$this->projectPrefix = 'stat';
		parent::__construct();
	}

	public function crm() {
		$input = Input::all();

		if (!Input::has('projectId')) {
			$input['projectId']	 = '';
			$projects						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->get();
		}
		else {
			$projects = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereProjectId($input['projectId'])->get();
		}

		return $this->setupLayout('stat.crm')
										->with('projects', $projects)
										->with('input', $input);
	}

	public function leads() {

		$this->user	 = Auth::user();
		$input			 = Input::all();

		if (Input::has('startDate')) {
			Session::put('stat_startDate', $input['startDate']);
		}
		else {
			$input['startDate'] = Session::get('stat_startDate');
			if (!$input['startDate']) {
				$input['startDate'] = date("d.m.Y");
			}
		}
		if (Input::has('endDate')) {
			Session::put('stat_endDate', $input['endDate']);
		}
		else {
			$input['endDate'] = Session::get('stat_endDate');
			if (!$input['endDate']) {
				$input['endDate'] = date('d.m.Y');
			}
		}

		$startDate = explode('.', $input['startDate']);
		if (checkdate($startDate[1], $startDate[0], $startDate[2])) {
			$startDate = "{$startDate[2]}-{$startDate[1]}-{$startDate[0]}";
		}
		$endDate = explode('.', $input['endDate']);
		if (checkdate($endDate[1], $endDate[0], $endDate[2])) {
			$endDate = "{$endDate[2]}-{$endDate[1]}-{$endDate[0]}";
		}

		$dateWhere	 = DB::raw("`partners`.lead_id IS NOT NULL AND DATE(`partners`.lead_created_at)>=DATE('{$startDate}') AND DATE(`partners`.lead_created_at)<=DATE('{$endDate}')");
//		DB::raw("DATE(`partners`.lead_created_at)>=DATE('2014-10-06') AND DATE(`partners`.lead_created_at1)<=DATE('2014-10-06')")
		$dateWhere2	 = DB::raw("DATE(`leads`.created_at)>=DATE('{$startDate}') AND DATE(`leads`.created_at)<=DATE('{$endDate}')");

		if (!Input::has('projectId')) {
			$input['projectId'] = Session::get($this->projectPrefix . '_work_projectId');
		}
		$project = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		if (!$project) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		$projects = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereProjectId($project->id)->get();

		return $this->setupLayout('stat.leads')
										->with('dateWhere', $dateWhere)
										->with('dateWhere2', $dateWhere2)
										->with('projects', $projects)
										->with('user', $this->user)
										->with('input', $input)
										->with('months', $this->months);
	}

	public function reject() {
		$this->user	 = Auth::user();
		$input			 = Input::all();

		if (Input::has('startDate')) {
			Session::put('stat_startDate', $input['startDate']);
		}
		else {
			$input['startDate'] = Session::get('stat_startDate');
			if (!$input['startDate']) {
				$input['startDate'] = date("d.m.Y");
			}
		}
		if (Input::has('endDate')) {
			Session::put('stat_endDate', $input['endDate']);
		}
		else {
			$input['endDate'] = Session::get('stat_endDate');
			if (!$input['endDate']) {
				$input['endDate'] = date('d.m.Y');
			}
		}

		$startDate = explode('.', $input['startDate']);
		if (checkdate($startDate[1], $startDate[0], $startDate[2])) {
			$startDate = "{$startDate[2]}-{$startDate[1]}-{$startDate[0]}";
		}
		$endDate = explode('.', $input['endDate']);
		if (checkdate($endDate[1], $endDate[0], $endDate[2])) {
			$endDate = "{$endDate[2]}-{$endDate[1]}-{$endDate[0]}";
		}
		$dateWhere = DB::raw("DATE(`partners`.updated_at)>=DATE('{$startDate}') AND DATE(`partners`.updated_at)<=DATE('{$endDate}')");

		if (!Input::has('projectId')) {
			$input['projectId'] = Session::get($this->projectPrefix . '_work_projectId');
		}
		$project = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		$projects = $this->user->projects()->whereUsecrm(1)->whereProjectId($project->id)->get();

		return $this->setupLayout('stat.reject')
										->with('dateWhere', $dateWhere)
										->with('projects', $projects)
										->with('user', $this->user)
										->with('input', $input)
										->with('months', $this->months);
	}

	public function finance() {
//		foreach(Partner::all() as $partner) {
//			$cart = $partner->getCart();
//			if ($cart) {
//				$purchaseprice = 0;
//				foreach($cart as $item) {
//					$product = Product::find($item['id']);
//					$purchaseprice += $product->price_purchase * $item['quant'];
//				}
//				$partner->purchaseprice = $purchaseprice;
//				$partner->save();
//			}
//		}

		$this->user	 = Auth::user();
		$input			 = Input::all();

		if (Input::has('startDate')) {
			Session::put('stat_startDate', $input['startDate']);
		}
		else {
			$input['startDate'] = Session::get('stat_startDate');
			if (!$input['startDate']) {
				$input['startDate'] = date("d.m.Y");
			}
		}
		if (Input::has('endDate')) {
			Session::put('stat_endDate', $input['endDate']);
		}
		else {
			$input['endDate'] = Session::get('stat_endDate');
			if (!$input['endDate']) {
				$input['endDate'] = date('d.m.Y');
			}
		}

		$startDate = explode('.', $input['startDate']);
		if (checkdate($startDate[1], $startDate[0], $startDate[2])) {
			$startDate = "{$startDate[2]}-{$startDate[1]}-{$startDate[0]}";
		}
		$endDate = explode('.', $input['endDate']);
		if (checkdate($endDate[1], $endDate[0], $endDate[2])) {
			$endDate = "{$endDate[2]}-{$endDate[1]}-{$endDate[0]}";
		}
		if (!isset($input['useLeadDate'])) {
			$input['useLeadDate'] = 0;
		}
		if ($input['useLeadDate'] == 1) {
			$dateWhere = DB::raw("DATE(`partners`.lead_created_at)>=DATE('{$startDate}') AND DATE(`partners`.lead_created_at)<=DATE('{$endDate}')");
		}
		else {
			$dateWhere = DB::raw("DATE(`partners`.datetime_of_agree)>=DATE('{$startDate}') AND DATE(`partners`.datetime_of_agree)<=DATE('{$endDate}')");
		}
		$dateWhere2 = DB::raw("DATE(`visits`.created_at)>=DATE('{$startDate}') AND DATE(`visits`.created_at)<=DATE('{$endDate}')");

		if (!Input::has('projectId')) {
			$input['projectId'] = Session::get($this->projectPrefix . '_work_projectId');
		}
		$project = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		if (!$project) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		$projects = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereProjectId($project->id)->get();

		return $this->setupLayout('stat.finance')
										->with('dateWhere', $dateWhere)
										->with('dateWhere2', $dateWhere2)
										->with('projects', $projects)
										->with('user', $this->user)
										->with('input', $input)
										->with('months', $this->months);
	}

	public function managers() {
		$this->user	 = Auth::user();
		$input			 = Input::all();

		if (Input::has('startDate')) {
			Session::put('stat_startDate', $input['startDate']);
		}
		else {
			$input['startDate'] = Session::get('stat_startDate');
			if (!$input['startDate']) {
				$input['startDate'] = date("d.m.Y");
			}
		}
		if (Input::has('endDate')) {
			Session::put('stat_endDate', $input['endDate']);
		}
		else {
			$input['endDate'] = Session::get('stat_endDate');
			if (!$input['endDate']) {
				$input['endDate'] = date('d.m.Y');
			}
		}

		$startDate = explode('.', $input['startDate']);
		if (checkdate($startDate[1], $startDate[0], $startDate[2])) {
			$startDate = "{$startDate[2]}-{$startDate[1]}-{$startDate[0]}";
		}
		$endDate = explode('.', $input['endDate']);
		if (checkdate($endDate[1], $endDate[0], $endDate[2])) {
			$endDate = "{$endDate[2]}-{$endDate[1]}-{$endDate[0]}";
		}
		$dateWhere = DB::raw("DATE(`partners`.created_at)>=DATE('{$startDate}') AND DATE(`partners`.created_at)<=DATE('{$endDate}') AND lead_id IS NOT NULL AND lead_id!=0");
		$dateWhere2 = DB::raw("DATE(`visits`.created_at)>=DATE('{$startDate}') AND DATE(`visits`.created_at)<=DATE('{$endDate}')");

		if (!Input::has('projectId')) {
			$input['projectId'] = Session::get($this->projectPrefix . '_work_projectId');
		}
		$project = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		if (!$project) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		$projects = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereProjectId($project->id)->get();

		return $this->setupLayout('stat.managers')
										->with('dateWhere', $dateWhere)
										->with('dateWhere2', $dateWhere2)
										->with('projects', $projects)
										->with('user', $this->user)
										->with('input', $input)
										->with('months', $this->months);
	}

	public function products() {
		$input = Input::all();

		if (Input::has('startDate')) {
			Session::put('stat_startDate', $input['startDate']);
		}
		else {
			$input['startDate'] = Session::get('stat_startDate');
			if (!$input['startDate']) {
				$input['startDate'] = date("d.m.Y");
			}
		}
		if (Input::has('endDate')) {
			Session::put('stat_endDate', $input['endDate']);
		}
		else {
			$input['endDate'] = Session::get('stat_endDate');
			if (!$input['endDate']) {
				$input['endDate'] = date('d.m.Y');
			}
		}

		$startDate = explode('.', $input['startDate']);
		if (checkdate($startDate[1], $startDate[0], $startDate[2])) {
			$startDate = "{$startDate[2]}-{$startDate[1]}-{$startDate[0]}";
		}
		$endDate = explode('.', $input['endDate']);
		if (checkdate($endDate[1], $endDate[0], $endDate[2])) {
			$endDate = "{$endDate[2]}-{$endDate[1]}-{$endDate[0]}";
		}
		$dateWhere = DB::raw("DATE(`partners`.datetime_of_agree)>=DATE('{$startDate}') AND DATE(`partners`.datetime_of_agree)<=DATE('{$endDate}')");

		if (!Input::has('projectId')) {
			$input['projectId'] = Session::get($this->projectPrefix . '_work_projectId');
		}
		$project = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		if (!Input::has('projectId')) {
			$input['projectId'] = Session::get($this->projectPrefix . '_work_projectId');
		}
		$project = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		if (!$project) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);

		$partners = $project->partners()->where('status', '!=', Partner::STATUS_REJECT)->whereRaw($dateWhere)->get();

		$productsArray = array();
		$products			 = $project->products()->withTrashed()->get();
		if ($products) {
			foreach ($products as $product) {
				$productsArray[$product->id]['product']	 = $product->toArray();
				$productsArray[$product->id]['quant']		 = 0;
				$productsArray[$product->id]['price']		 = 0;
				$productsArray[$product->id]['profit']	 = 0;
			}
		}
		if ($partners) {
			foreach ($partners as $partner) {
				foreach ($partner->getCart() as $cart) {
//					if (isset($productsArray[$cart['id']])) {
					$productsArray[$cart['id']]['quant'] += $cart['quant'];
					$productsArray[$cart['id']]['price'] += $cart['price'] * $cart['quant'];
					$productsArray[$cart['id']]['profit'] += ($cart['price'] * $cart['quant'] - $productsArray[$cart['id']]['product']['price_purchase'] * $cart['quant']);
//					}
				}
			}
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);

		return $this->setupLayout('stat.products')
										->with('dateWhere', $dateWhere)
										->with('project', $project)
										->with('products', $productsArray)
										->with('input', $input);
	}

	public function refrences() {
		$input = Input::all();

		if (Input::has('startDate')) {
			Session::put('stat_startDate', $input['startDate']);
		}
		else {
			$input['startDate'] = Session::get('stat_startDate');
			if (!$input['startDate']) {
				$input['startDate'] = date("d.m.Y");
			}
		}
		if (Input::has('endDate')) {
			Session::put('stat_endDate', $input['endDate']);
		}
		else {
			$input['endDate'] = Session::get('stat_endDate');
			if (!$input['endDate']) {
				$input['endDate'] = date('d.m.Y');
			}
		}

		$startDate = explode('.', $input['startDate']);
		if (checkdate($startDate[1], $startDate[0], $startDate[2])) {
			$startDate = "{$startDate[2]}-{$startDate[1]}-{$startDate[0]}";
		}
		$endDate = explode('.', $input['endDate']);
		if (checkdate($endDate[1], $endDate[0], $endDate[2])) {
			$endDate = "{$endDate[2]}-{$endDate[1]}-{$endDate[0]}";
		}
		$dateWhere = DB::raw("DATE(created_at)>=DATE('{$startDate}') AND DATE(created_at)<=DATE('{$endDate}')");

		if (!Input::has('projectId')) {
			$input['projectId'] = Session::get($this->projectPrefix . '_work_projectId');
		}
		$project = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project						 = $this->user->projects()->whereId($input['projectId'])->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		$managers = User::whereRaw('id in (SELECT user_id FROM users_projects WHERE project_id=?)', array($project->id))->get();
		if (!Input::has('managerId')) {
			$input['managerId'] = Session::get('stat_managerId');
		}
		$manager = false;
		if (!$input['managerId']) {
			$manager						 = User::whereRaw('id in (SELECT user_id FROM users_projects WHERE project_id=?)', array($project->id))->first();
			$input['managerId']	 = $manager->id;
		}
		else {
			$manager = User::find($input['managerId']);
		}
		Session::put('stat_managerId', $input['managerId']);

		$refrences = false;
		if ($input['managerId']) {
			$refrences = Refrence::whereSenderUserId($input['managerId'])->orderBy('created_at', 'DESC')->whereRaw($dateWhere)->get();
		}

		$statDayArray			 = array();
		$statManagersArray = array();
		$statDay					 = Refrence::whereRaw('sender_user_id in (SELECT user_id FROM users_projects WHERE project_id=?)', array($project->id))
						->whereRaw(DB::raw("created_at>=DATE_SUB(NOW(), INTERVAL 10 HOUR)"))
						->groupBy(DB::raw("DATE_FORMAT(created_at,'%H')"))
						->groupBy('sender_user_id')
						->orderBy('hour', 'ASC')
						->get(array(DB::raw("COUNT(*) as cnt, sender_user_id, DATE_FORMAT(created_at,'%H') as hour")));
		if ($statDay) {
			foreach ($statDay as $day) {
				$m																										 = User::find($day->sender_user_id);
				$statManagersArray[$day->sender_user_id]							 = "{$m->first_name} {$m->last_name}";
				$statDayArray[$day->hour][$day->sender_user_id]['cnt'] = $day->cnt;
			}
		}

		return $this->setupLayout('stat.refrences')
										->with('dateWhere', $dateWhere)
										->with('project', $project)
										->with('managers', $managers)
										->with('manager', $manager)
										->with('refrences', $refrences)
										->with('statManagersArray', $statManagersArray)
										->with('statDay', $statDayArray)
										->with('input', $input);
	}

	public function visits() {
		$input = Input::except('page');

		if (Input::has('startDate')) {
			Session::put('stat_startDate', $input['startDate']);
		}
		else {
			$input['startDate'] = Session::get('stat_startDate');
			if (!$input['startDate']) {
				$input['startDate'] = date("d.m.Y");
			}
		}
		if (Input::has('endDate')) {
			Session::put('stat_endDate', $input['endDate']);
		}
		else {
			$input['endDate'] = Session::get('stat_endDate');
			if (!$input['endDate']) {
				$input['endDate'] = date('d.m.Y');
			}
		}

		$startDate = explode('.', $input['startDate']);
		if (checkdate($startDate[1], $startDate[0], $startDate[2])) {
			$startDate = "{$startDate[2]}-{$startDate[1]}-{$startDate[0]}";
		}
		$endDate = explode('.', $input['endDate']);
		if (checkdate($endDate[1], $endDate[0], $endDate[2])) {
			$endDate = "{$endDate[2]}-{$endDate[1]}-{$endDate[0]}";
		}
		$dateWhere = DB::raw("DATE(created_at)>=DATE('{$startDate}') AND DATE(created_at)<=DATE('{$endDate}')");

		if (!Input::has('projectId')) {
			$input['projectId'] = Session::get($this->projectPrefix . '_work_projectId');
		}
		$project = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		if (!Input::has('siteId')) {
			$input['siteId'] = Session::get('stat_' . $input['projectId'] . '_siteId');
		}
		$site = false;
		if (!$input['siteId']) {
			$site = $project->sites()->first();
			if ($site) {
				$input['siteId'] = $site->id;
			}
		}
		if (!$site) {
			$site = $project->sites()->whereId($input['siteId'])->first();
		}
		Session::put('stat_' . $input['projectId'] . '_siteId', $input['siteId']);

		return $this->setupLayout('stat.visits.info')
										->with('dateWhere', $dateWhere)
										->with('project', $project)
										->with('site', $site)
										->with('input', $input);
	}

	public function dashboard() {
		$input = Input::except('page');

		if (Input::has('startDate')) {
			Session::put('stat_startDate', $input['startDate']);
		}
		else {
			$input['startDate'] = Session::get('stat_startDate');
			if (!$input['startDate']) {
				$input['startDate'] = date("d.m.Y");
			}
		}
		if (Input::has('endDate')) {
			Session::put('stat_endDate', $input['endDate']);
		}
		else {
			$input['endDate'] = Session::get('stat_endDate');
			if (!$input['endDate']) {
				$input['endDate'] = date('d.m.Y');
			}
		}

		$startDate = explode('.', $input['startDate']);
		if (checkdate($startDate[1], $startDate[0], $startDate[2])) {
			$startDate = "{$startDate[2]}-{$startDate[1]}-{$startDate[0]}";
		}
		$endDate = explode('.', $input['endDate']);
		if (checkdate($endDate[1], $endDate[0], $endDate[2])) {
			$endDate = "{$endDate[2]}-{$endDate[1]}-{$endDate[0]}";
		}
		$dateWhere = DB::raw("DATE(created_at)>=DATE('{$startDate}') AND DATE(created_at)<=DATE('{$endDate}')");

		if (!Input::has('projectId')) {
			$input['projectId'] = Session::get($this->projectPrefix . '_work_projectId');
		}
		$project = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		if (!Input::has('siteId')) {
			$input['siteId'] = Session::get('stat_' . $input['projectId'] . '_siteId');
		}
		$site = false;
		if (!$input['siteId']) {
			$site = $project->sites()->first();
			if ($site) {
				$input['siteId'] = $site->id;
			}
		}
		if (!$site) {
			$site = $project->sites()->whereId($input['siteId'])->first();
		}
		Session::put('stat_' . $input['projectId'] . '_siteId', $input['siteId']);

		$commonStatWeek = Visit::whereProjectId($input['projectId'])
						->whereSiteId($input['siteId'])
						->whereRaw(DB::raw("DATE(created_at)>=DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))"))
						->groupBy(DB::raw("DATE_FORMAT(created_at,'%d')"))
						->orderBy('month', 'ASC')
						->orderBy('date', 'ASC')
						->remember(2)
						->get(array(DB::raw("COUNT(*) as visits, SUM(views) as views, COUNT(DISTINCT visitor_id) as visitors, DATE_FORMAT(created_at,'%d') as date, DATE_FORMAT(created_at,'%m') as month")));

		$commonStatToday		 = Visit::whereProjectId($input['projectId'])
						->whereSiteId($input['siteId'])
						->whereRaw(DB::raw("DATE(created_at)=DATE(NOW())"))
						->remember(2)
						->first(array(DB::raw("COUNT(*) as visits, SUM(views) as views, COUNT(DISTINCT visitor_id) as visitors")));
		$leadQuantToday			 = Lead::withTrashed()
						->whereProjectId($input['projectId'])
						->whereSiteId($input['siteId'])
						->whereRaw(DB::raw("DATE(created_at)=DATE(NOW())"))
						->remember(2)
						->count(DB::raw('distinct visitor_id'));
		$partnerQuantToday	 = Partner::whereProjectId($input['projectId'])
						->checkStatus(array('statusType' => 'agreed'))
						->whereRaw(DB::raw('DATE(lead_created_at)=DATE(NOW()) AND DATE(datetime_of_agree)=DATE(NOW())'))
						->remember(2)
						->count();
		$visitsCost['Today'] = Visit::whereProjectId($input['projectId'])
										->whereRaw(DB::raw("DATE(created_at)=DATE(NOW())"))
										->sum('cost') / 100;

		$commonStatYeasterday			 = Visit::whereProjectId($input['projectId'])
						->whereSiteId($input['siteId'])
						->whereRaw(DB::raw("DATE(created_at)=DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))"))
						->remember(2)
						->first(array(DB::raw("COUNT(*) as visits, SUM(views) as views, COUNT(DISTINCT visitor_id) as visitors")));
		$leadQuantYeasterday			 = Lead::withTrashed()
						->whereProjectId($input['projectId'])
						->whereSiteId($input['siteId'])
						->whereRaw(DB::raw("DATE(created_at)=DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))"))
						->remember(2)
						->count(DB::raw('distinct visitor_id'));
		$partnerQuantYeasterday		 = Partner::whereProjectId($input['projectId'])
						->checkStatus(array('statusType' => 'agreed'))
						->whereRaw(DB::raw('DATE(lead_created_at)=DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)) AND DATE(datetime_of_agree)=DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))'))
						->remember(2)
						->count();
		$visitsCost['Yeasterday']	 = Visit::whereProjectId($input['projectId'])
										->whereRaw(DB::raw("DATE(created_at)=DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))"))
										->remember(2)
										->sum('cost') / 100;

		$commonStatThisWeek			 = Visit::whereProjectId($input['projectId'])
						->whereSiteId($input['siteId'])
						->whereRaw(DB::raw("created_at>=DATE(DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY))"))
						->remember(2)
						->first(array(DB::raw("COUNT(*) as visits, SUM(views) as views, COUNT(DISTINCT visitor_id) as visitors")));
		$leadQuantThisWeek			 = Lead::withTrashed()
						->whereProjectId($input['projectId'])
						->whereSiteId($input['siteId'])
						->whereRaw(DB::raw("created_at>=DATE(DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY))"))
						->remember(2)
						->count(DB::raw('distinct visitor_id'));
		$partnerQuantThisWeek		 = Partner::whereProjectId($input['projectId'])
						->checkStatus(array('statusType' => 'agreed'))
						->whereRaw(DB::raw('lead_created_at>=DATE(DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY)) AND datetime_of_agree>=DATE(DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY))'))
						->remember(2)
						->count();
		$visitsCost['ThisWeek']	 = Visit::whereProjectId($input['projectId'])
										->whereRaw(DB::raw("created_at>=DATE(DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY))"))
										->remember(2)
										->sum('cost') / 100;

		$commonStatLastWeek			 = Visit::whereProjectId($input['projectId'])
						->whereSiteId($input['siteId'])
						->whereRaw(DB::raw("WEEK(created_at)=WEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK))"))
						->remember(2)
						->first(array(DB::raw("COUNT(*) as visits, SUM(views) as views, COUNT(DISTINCT visitor_id) as visitors")));
		$leadQuantLastWeek			 = Lead::withTrashed()
						->whereProjectId($input['projectId'])
						->whereSiteId($input['siteId'])
						->whereRaw(DB::raw("WEEK(created_at)=WEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK))"))
						->remember(2)
						->count(DB::raw('DISTINCT visitor_id'));
		$partnerQuantLastWeek		 = Partner::whereProjectId($input['projectId'])
						->checkStatus(array('statusType' => 'agreed'))
						->whereRaw(DB::raw('WEEK(lead_created_at)=WEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK)) AND WEEK(datetime_of_agree)=WEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK))'))
						->remember(2)
						->count();
		$visitsCost['LastWeek']	 = Visit::whereProjectId($input['projectId'])
										->whereRaw(DB::raw("WEEK(created_at)=WEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK))"))
										->remember(2)
										->sum('cost') / 100;

		$commonStatDay						 = Visit::whereProjectId($input['projectId'])
						->whereSiteId($input['siteId'])
						->whereRaw(DB::raw("created_at>=DATE_SUB(NOW(), INTERVAL 10 HOUR)"))
						->groupBy(DB::raw("DATE_FORMAT(created_at,'%H')"))
						->orderBy('hour', 'ASC')
						->remember(2)
						->get(array(DB::raw("COUNT(*) as visits, SUM(views) as views, DATE_FORMAT(created_at,'%H') as hour")));
		$commonLeadStatDay				 = Lead::withTrashed()
						->whereProjectId($input['projectId'])
						->whereSiteId($input['siteId'])
						->whereRaw(DB::raw("created_at>=DATE_SUB(NOW(), INTERVAL 10 HOUR)"))
						->groupBy(DB::raw("DATE_FORMAT(created_at,'%H')"))
						->remember(2)
						->get(array(DB::raw("COUNT(DISTINCT visitor_id) as leads, DATE_FORMAT(created_at,'%H') as hour")));
		$commonPartnerStatDay			 = Partner::whereProjectId($input['projectId'])
						->checkStatus(array('statusType' => 'agreed'))
						->whereRaw(DB::raw('lead_created_at>=DATE_SUB(NOW(), INTERVAL 10 HOUR)'))
						->groupBy(DB::raw("DATE_FORMAT(lead_created_at,'%H')"))
						->remember(2)
						->get(array(DB::raw("COUNT(*) as partners, DATE_FORMAT(lead_created_at,'%H') as hour")));
		$commonPartnerStatDayArray = array();
		foreach ($commonPartnerStatDay as $item) {
			if ($item->hour) {
				$commonPartnerStatDayArray[$item->hour] = $item->partners;
			}
		}

		$commonLeadStatWeek					 = Lead::withTrashed()
						->whereProjectId($input['projectId'])
						->whereSiteId($input['siteId'])
						->whereRaw(DB::raw("DATE(created_at)>=DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))"))
						->groupBy(DB::raw("DATE_FORMAT(created_at,'%d')"))
						->orderBy('month', 'ASC')
						->orderBy('date', 'ASC')
						->remember(2)
						->get(array(DB::raw("COUNT(DISTINCT visitor_id) as leads, DATE_FORMAT(created_at,'%d') as date, DATE_FORMAT(created_at,'%m') as month")));
		$commonPartnerStatWeek			 = Partner::whereProjectId($input['projectId'])
						->checkStatus(array('statusType' => 'agreed'))
						->whereRaw(DB::raw('DATE(lead_created_at)>=DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))'))
						->groupBy(DB::raw("DATE_FORMAT(created_at,'%d')"))
						->orderBy('month', 'ASC')
						->orderBy('date', 'ASC')
						->get(array(DB::raw("COUNT(*) as partners, DATE_FORMAT(created_at,'%d') as date, DATE_FORMAT(created_at,'%m') as month")));
		$commonPartnerStatWeekArray	 = array();
		foreach ($commonPartnerStatWeek as $item) {
			if ($item->month && $item->date) {
				$commonPartnerStatWeekArray[$item->month][$item->date] = $item->partners;
			}
		}

		return $this->setupLayout('stat.visits.dashboard')
										->with('dateWhere', $dateWhere)
										->with('project', $project)
										->with('site', $site)
										->with('commonStatDay', $commonStatDay)
										->with('commonLeadStatDay', $commonLeadStatDay)
										->with('commonPartnerStatDay', $commonPartnerStatDayArray)
										->with('commonStatWeek', $commonStatWeek)
										->with('commonLeadStatWeek', $commonLeadStatWeek)
										->with('commonPartnerStatWeek', $commonPartnerStatWeekArray)
										->with('commonStatToday', $commonStatToday)
										->with('leadQuantToday', $leadQuantToday)
										->with('partnerQuantToday', $partnerQuantToday)
										->with('commonStatYeasterday', $commonStatYeasterday)
										->with('leadQuantYeasterday', $leadQuantYeasterday)
										->with('partnerQuantYeasterday', $partnerQuantYeasterday)
										->with('commonStatThisWeek', $commonStatThisWeek)
										->with('leadQuantThisWeek', $leadQuantThisWeek)
										->with('partnerQuantThisWeek', $partnerQuantThisWeek)
										->with('commonStatLastWeek', $commonStatLastWeek)
										->with('leadQuantLastWeek', $leadQuantLastWeek)
										->with('partnerQuantLastWeek', $partnerQuantLastWeek)
										->with('visitsCost', $visitsCost)
										->with('input', $input);
	}

	public function visitsAll() {
		$input = Input::except('page');

		if (Input::has('startDate')) {
			Session::put('stat_startDate', $input['startDate']);
		}
		else {
			$input['startDate'] = Session::get('stat_startDate');
			if (!$input['startDate']) {
				$input['startDate'] = date("d.m.Y");
			}
		}
		if (Input::has('endDate')) {
			Session::put('stat_endDate', $input['endDate']);
		}
		else {
			$input['endDate'] = Session::get('stat_endDate');
			if (!$input['endDate']) {
				$input['endDate'] = date('d.m.Y');
			}
		}

		$startDate = explode('.', $input['startDate']);
		if (checkdate($startDate[1], $startDate[0], $startDate[2])) {
			$startDate = "{$startDate[2]}-{$startDate[1]}-{$startDate[0]}";
		}
		$endDate = explode('.', $input['endDate']);
		if (checkdate($endDate[1], $endDate[0], $endDate[2])) {
			$endDate = "{$endDate[2]}-{$endDate[1]}-{$endDate[0]}";
		}
		$dateWhere = DB::raw("DATE(created_at)>=DATE('{$startDate}') AND DATE(created_at)<=DATE('{$endDate}')");

		if (!Input::has('projectId')) {
			$input['projectId'] = Session::get($this->projectPrefix . '_work_projectId');
		}
		$project = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		if (!Input::has('siteId')) {
			$input['siteId'] = Session::get('stat_' . $input['projectId'] . '_siteId');
		}
		$site = false;
		if (!$input['siteId']) {
			$site = $project->sites()->first();
			if ($site) {
				$input['siteId'] = $site->id;
			}
		}
		if (!$site) {
			$site = $project->sites()->whereId($input['siteId'])->first();
		}
		Session::put('stat_' . $input['projectId'] . '_siteId', $input['siteId']);

		if ($site) {
			$visits = Visit::with('visitParams')->whereProjectId($project->id)->whereSiteId($site->id)->whereRaw($dateWhere)->orderBy('created_at', 'DESC')->paginate(50);
		}
		else {
			$visits = Visit::with('visitParams')->whereProjectId($project->id)->whereRaw($dateWhere)->orderBy('created_at', 'DESC')->paginate(50);
		}

		return $this->setupLayout('stat.visits.all')
										->with('dateWhere', $dateWhere)
										->with('project', $project)
										->with('site', $site)
										->with('visits', $visits)
										->with('input', $input);
	}

	public function visitsLead() {
		$input = Input::except('page');

		if (Input::has('startDate')) {
			Session::put('stat_startDate', $input['startDate']);
		}
		else {
			$input['startDate'] = Session::get('stat_startDate');
			if (!$input['startDate']) {
				$input['startDate'] = date("d.m.Y");
			}
		}
		if (Input::has('endDate')) {
			Session::put('stat_endDate', $input['endDate']);
		}
		else {
			$input['endDate'] = Session::get('stat_endDate');
			if (!$input['endDate']) {
				$input['endDate'] = date('d.m.Y');
			}
		}

		$startDate = explode('.', $input['startDate']);
		if (checkdate($startDate[1], $startDate[0], $startDate[2])) {
			$startDate = "{$startDate[2]}-{$startDate[1]}-{$startDate[0]}";
		}
		$endDate = explode('.', $input['endDate']);
		if (checkdate($endDate[1], $endDate[0], $endDate[2])) {
			$endDate = "{$endDate[2]}-{$endDate[1]}-{$endDate[0]}";
		}
		$dateWhere = DB::raw("DATE(visits.created_at)>=DATE('{$startDate}') AND DATE(visits.created_at)<=DATE('{$endDate}')");

		if (!Input::has('projectId')) {
			$input['projectId'] = Session::get($this->projectPrefix . '_work_projectId');
		}
		$project = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		if (!Input::has('siteId')) {
			$input['siteId'] = Session::get('stat_' . $input['projectId'] . '_siteId');
		}
		$site = false;
		if (!$input['siteId']) {
			$site = $project->sites()->first();
			if ($site) {
				$input['siteId'] = $site->id;
			}
		}
		if (!$site) {
			$site = $project->sites()->whereId($input['siteId'])->first();
		}
		Session::put('stat_' . $input['projectId'] . '_siteId', $input['siteId']);

		if ($site) {
			$visits = Visit::with('visitParams')->join('leads', function($join) {
								$join->on('visits.id', '=', 'leads.visit_id');
							})->where('visits.project_id', $project->id)->where('visits.site_id', $site->id)->whereRaw($dateWhere)->orderBy('visits.created_at', 'DESC')->groupBy('visit_id')->select('visits.*')->paginate(50);
		}
		else {
			return Redirect::to('stat/visits');
		}

		return $this->setupLayout('stat.visits.lead')
										->with('dateWhere', $dateWhere)
										->with('project', $project)
										->with('site', $site)
										->with('visits', $visits)
										->with('input', $input);
	}

	public function costs() {
		$input = Input::except('page');

		if (Input::has('startDate')) {
			Session::put('stat_startDate', $input['startDate']);
		}
		else {
			$input['startDate'] = Session::get('stat_startDate');
			if (!$input['startDate']) {
				$input['startDate'] = date("d.m.Y");
			}
		}
		if (Input::has('endDate')) {
			Session::put('stat_endDate', $input['endDate']);
		}
		else {
			$input['endDate'] = Session::get('stat_endDate');
			if (!$input['endDate']) {
				$input['endDate'] = date('d.m.Y');
			}
		}

		$startDate = explode('.', $input['startDate']);
		if (checkdate($startDate[1], $startDate[0], $startDate[2])) {
			$startDate = "{$startDate[2]}-{$startDate[1]}-{$startDate[0]}";
		}
		$endDate = explode('.', $input['endDate']);
		if (checkdate($endDate[1], $endDate[0], $endDate[2])) {
			$endDate = "{$endDate[2]}-{$endDate[1]}-{$endDate[0]}";
		}
		$dateWhere = DB::raw("DATE(visits.created_at)>=DATE('{$startDate}') AND DATE(visits.created_at)<=DATE('{$endDate}')");

		if (!Input::has('projectId')) {
			$input['projectId'] = Session::get($this->projectPrefix . '_work_projectId');
		}
		$project = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		if (!Input::has('siteId')) {
			$input['siteId'] = Session::get('stat_' . $input['projectId'] . '_siteId');
		}
		$site = false;
		if (!$input['siteId']) {
			$site = $project->sites()->first();
			if ($site) {
				$input['siteId'] = $site->id;
			}
		}
		if (!$site) {
			$site = $project->sites()->whereId($input['siteId'])->first();
		}
		Session::put('stat_' . $input['projectId'] . '_siteId', $input['siteId']);

		return $this->setupLayout('stat.visits.costs')
										->with('dateWhere', $dateWhere)
										->with('project', $project)
										->with('site', $site)
										->with('visitCosts', $project->visitCosts)
										->with('input', $input);
	}

	public function createCost() {
		$input = Input::all();

		if (!isset($input['visitNewCostCost']) || !$input['visitNewCostCost']) {
			$response = array('status' => 'fail');
			return Response::json($response);
		}

		$input['projectId']	 = Session::get($this->projectPrefix . '_work_projectId');
		$project						 = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		$visitCost = VisitCost::create(array(
								'name' => $input['visitNewCostName']
		));

		$visitCost->user()->associate($this->user);
		$visitCost->project()->associate($project);
		$visitCost->cost = $input['visitNewCostCost'];
		$visitCost->save();

		$response = array(
				'status' => 'success',
				'id'		 => $visitCost->id,
				'cost'	 => View::make('stat.visits.cost')->with('visitCost', $visitCost)->render(),
		);
		return Response::json($response);
	}

	public function updateCost() {
		$input = Input::all();

		$input['projectId']	 = Session::get($this->projectPrefix . '_work_projectId');
		$project						 = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		if (!isset($input['pk']) || !isset($input['name'])) {
			$response = array('status' => 'fail');
			return Response::json($response);
		}

		$visitCost = VisitCost::find($input['pk']);
		if ($visitCost == null || !is_numeric($input['pk'])) {
			$response = array('status' => 'fail', 'message' => 'Стоимость не найдена');
			return Response::json($response);
		}

		$visitCost->$input['name'] = $input['value'];
		$visitCost->save();

		$response = array(
				'status' => 'success',
				'id'		 => $visitCost->id,
				'name'	 => $input['name'],
				'cost'	 => View::make('stat.visits.cost')->with('visitCost', $visitCost)->render(),
		);
		return Response::json($response);
	}

	public function deleteCost() {
		$input = Input::all();

		if (!isset($input['costId'])) {
			$response = array('status' => 'fail');
			return Response::json($response);
		}

		$input['projectId']	 = Session::get($this->projectPrefix . '_work_projectId');
		$project						 = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		$visitCost = VisitCost::find($input['costId']);
		if ($visitCost == null || !is_numeric($input['costId'])) {
			$response = array('status' => 'fail', 'message' => 'Стоимость не найдена');
			return Response::json($response);
		}

		$visitCost->delete();

		$response = array(
				'status' => 'success',
				'id'		 => $visitCost->id,
				'cost'	 => View::make('stat.visits.cost')->with('visitCost', $visitCost)->render(),
		);
		return Response::json($response);
	}

	public function restoreCost() {
		$input = Input::all();

		if (!isset($input['costId'])) {
			$response = array('status' => 'fail');
			return Response::json($response);
		}

		$input['projectId']	 = Session::get($this->projectPrefix . '_work_projectId');
		$project						 = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		$visitCost = VisitCost::withTrashed()->find($input['costId']);
		if ($visitCost == null || !is_numeric($input['costId'])) {
			$response = array('status' => 'fail', 'message' => 'Стоимость не найдена');
			return Response::json($response);
		}

		$visitCost->restore();

		$response = array(
				'status' => 'success',
				'id'		 => $visitCost->id,
				'cost'	 => View::make('stat.visits.cost')->with('visitCost', $visitCost)->render(),
		);
		return Response::json($response);
	}

	public function visitsUtmsource() {
		$input = Input::all();

		if (Input::has('startDate')) {
			Session::put('stat_startDate', $input['startDate']);
		}
		else {
			$input['startDate'] = Session::get('stat_startDate');
			if (!$input['startDate']) {
				$input['startDate'] = date("d.m.Y");
			}
		}
		if (Input::has('endDate')) {
			Session::put('stat_endDate', $input['endDate']);
		}
		else {
			$input['endDate'] = Session::get('stat_endDate');
			if (!$input['endDate']) {
				$input['endDate'] = date('d.m.Y');
			}
		}

		$startDate = explode('.', $input['startDate']);
		if (checkdate($startDate[1], $startDate[0], $startDate[2])) {
			$startDate = "{$startDate[2]}-{$startDate[1]}-{$startDate[0]}";
		}
		$endDate = explode('.', $input['endDate']);
		if (checkdate($endDate[1], $endDate[0], $endDate[2])) {
			$endDate = "{$endDate[2]}-{$endDate[1]}-{$endDate[0]}";
		}
		$dateWhere	 = DB::raw("visits.created_at>DATE('{$startDate}') AND visits.created_at<DATE(DATE_ADD('{$endDate}', INTERVAL 1 DAY))");
		$dateWhere2	 = DB::raw("leads.created_at>DATE('{$startDate}') AND leads.created_at<DATE(DATE_ADD('{$endDate}', INTERVAL 1 DAY))");

		if (!Input::has('projectId')) {
			$input['projectId'] = Session::get($this->projectPrefix . '_work_projectId');
		}
		$project = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		if (!Input::has('siteId')) {
			$input['siteId'] = Session::get('stat_' . $input['projectId'] . '_siteId');
		}
		$site = false;
		if (!$input['siteId']) {
			$site = $project->sites()->first();
			if ($site) {
				$input['siteId'] = $site->id;
			}
		}
		if (!$site) {
			$site = $project->sites()->whereId($input['siteId'])->first();
		}
		Session::put('stat_' . $input['projectId'] . '_siteId', $input['siteId']);

		$utmSourceStat = VisitParam::leftJoin('visits_params', function($join) {
							$join->on('visits_params.param_id', '=', 'visit_params.id');
						})->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'visits_params.visit_id');
						})->where('visit_params.name', '=', 'utm_source')
						->where('visits.project_id', $project->id)
						->where('visits.site_id', $site->id)
						->whereRaw($dateWhere)
						->groupBy('visits_params.value')
						->orderBy('cost', 'DESC')
						->get(array(DB::raw('visits_params.value, COUNT(*) as visits, 0 as visitors, 0 as cost, 0 as leads_count, 0 as partners_count')));


		$noUtmSourceStat = Visit::leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'visits.id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->whereNull('visit_params.name')
						->where('visits.project_id', $project->id)
						->where('visits.site_id', $site->id)
						->whereRaw($dateWhere)
						->groupBy('visits_params.value')
						->first(array(DB::raw('visits_params.value, COUNT(*) as visits, 0 as visitors, 0 as cost, 0 as leads_count, 0 as partners_count')));
		if (!$noUtmSourceStat) {
			$noUtmSourceStat											 = new stdClass();
			$noUtmSourceStat->value								 = null;
			$noUtmSourceStat->visits							 = 0;
			$noUtmSourceStat->visitors						 = 0;
			$noUtmSourceStat->cost								 = 0;
			$noUtmSourceStat->leads_count					 = 0;
			$noUtmSourceStat->partners_count			 = 0;
			$noUtmSourceStat->first_leads_count		 = 0;
			$noUtmSourceStat->first_partners_count = 0;
		}

		$utmSourceLeadStat = Lead::withTrashed()
						->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'leads.visit_id');
						})->leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'visits.id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->where('visit_params.name', '=', 'utm_source')
						->where('leads.project_id', $project->id)
						->where('leads.site_id', $site->id)
						->whereRaw($dateWhere2)
						->groupBy('visits_params.value')
						->get(array(DB::raw('visits_params.value, COUNT(DISTINCT visits.visitor_id) as leads_cnt')));

		foreach ($utmSourceLeadStat as $item) {
			foreach ($utmSourceStat as $stat) {
				if ($item->value == $stat->value) {
					$stat->leads_count = $item->leads_cnt;
				}
			}
		}

		$noUtmSourceLeadStat = Lead::withTrashed()
						->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'leads.visit_id');
						})->leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'visits.id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->whereNull('visit_params.name')
						->where('leads.project_id', $project->id)
						->where('leads.site_id', $site->id)
						->whereRaw($dateWhere2)
						->groupBy('visits_params.value')
						->first(array(DB::raw('visits_params.value, COUNT(DISTINCT visits.visitor_id) as leads_cnt')));
		if ($noUtmSourceLeadStat) {
			$noUtmSourceStat->leads_count = $noUtmSourceLeadStat->leads_cnt;
		}

		$utmSourcePartnerStat = Partner::leftJoin('leads', function($join) {
							$join->on('leads.id', '=', 'partners.lead_id');
						})->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'leads.visit_id');
						})->leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'visits.id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->where('visit_params.name', '=', 'utm_source')
						->where('leads.project_id', $project->id)
						->where('leads.site_id', $site->id)
						->whereRaw($dateWhere)
						->checkStatus(array('statusType' => 'agreed', 'type' => 'all'))
						->groupBy('visits_params.value')
						->get(array(DB::raw('visits_params.value, COUNT(*) as partners_cnt')));

		foreach ($utmSourcePartnerStat as $item) {
			foreach ($utmSourceStat as $stat) {
				if ($item->value == $stat->value) {
					$stat->partners_count = $item->partners_cnt;
				}
			}
		}

		$noUtmSourcePartnerStat = Partner::leftJoin('leads', function($join) {
							$join->on('leads.id', '=', 'partners.lead_id');
						})->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'leads.visit_id');
						})->leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'visits.id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->whereNull('visit_params.name')
						->where('leads.project_id', $project->id)
						->where('leads.site_id', $site->id)
						->whereRaw($dateWhere)
						->checkStatus(array('statusType' => 'agreed', 'type' => 'all'))
						->groupBy('visits_params.value')
						->first(array(DB::raw('visits_params.value, COUNT(*) as partners_cnt')));
		if ($noUtmSourcePartnerStat) {
			$noUtmSourceStat->partners_count = $noUtmSourcePartnerStat->partners_cnt;
		}

		$utmSourceLeadStat = Lead::withTrashed()
						->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'leads.visit_id');
						})->leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'leads.visit_id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->where('visit_params.name', '=', 'utm_source')
						->where('leads.project_id', $project->id)
						->where('leads.site_id', $site->id)
						->whereRaw($dateWhere2)
						->groupBy('visits_params.value')
						->get(array(DB::raw('visits_params.value, COUNT(*) as leads_cnt')));

		foreach ($utmSourceLeadStat as $item) {
			foreach ($utmSourceStat as $stat) {
				if ($item->value == $stat->value) {
					$stat->first_leads_count = $item->leads_cnt;
				}
			}
		}

		$noUtmSourceLeadStat = Lead::withTrashed()
						->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'leads.visit_id');
						})->leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'leads.visit_id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->whereNull('visit_params.name')
						->where('leads.project_id', $project->id)
						->where('leads.site_id', $site->id)
						->whereRaw($dateWhere2)
						->groupBy('visits_params.value')
						->first(array(DB::raw('visits_params.value, COUNT(*) as leads_cnt')));
		if ($noUtmSourceLeadStat) {
			$noUtmSourceStat->first_leads_count = $noUtmSourceLeadStat->leads_cnt;
		}

		$utmSourcePartnerStat = Partner::leftJoin('leads', function($join) {
							$join->on('leads.id', '=', 'partners.lead_id');
						})->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', DB::raw('(SELECT id FROM visits WHERE visitor_id=`leads`.`visitor_id` ORDER BY id ASC LIMIT 1)'));
						})->leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'visits.id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->where('visit_params.name', '=', 'utm_source')
						->where('leads.project_id', $project->id)
						->where('leads.site_id', $site->id)
						->whereRaw($dateWhere)
						->checkStatus(array('statusType' => 'agreed', 'type' => 'all'))
						->groupBy('visits_params.value')
						->get(array(DB::raw('visits_params.value, COUNT(*) as partners_cnt')));

		foreach ($utmSourcePartnerStat as $item) {
			foreach ($utmSourceStat as $stat) {
				if ($item->value == $stat->value) {
					$stat->first_partners_count = $item->partners_cnt;
				}
			}
		}

		$noUtmSourcePartnerStat = Partner::leftJoin('leads', function($join) {
							$join->on('leads.id', '=', 'partners.lead_id');
						})->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', DB::raw('(SELECT id FROM visits WHERE visitor_id=`leads`.`visitor_id` ORDER BY id ASC LIMIT 1)'));
						})->leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'visits.id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->whereNull('visit_params.name')
						->where('leads.project_id', $project->id)
						->where('leads.site_id', $site->id)
						->whereRaw($dateWhere)
						->checkStatus(array('statusType' => 'agreed', 'type' => 'all'))
						->groupBy('visits_params.value')
						->first(array(DB::raw('visits_params.value, COUNT(*) as partners_cnt')));
		if ($noUtmSourcePartnerStat) {
			$noUtmSourceStat->first_partners_count = $noUtmSourcePartnerStat->partners_cnt;
		}

		return $this->setupLayout('stat.visits.utmsource')
										->with('dateWhere', $dateWhere)
										->with('project', $project)
										->with('site', $site)
										->with('utmSourceStat', $utmSourceStat)
										->with('noUtmSourceStat', $noUtmSourceStat)
										->with('input', $input);
	}

	public function visitsUtmsourceExt() {
		set_time_limit(0);
		$input = Input::all();

		if (Input::has('startDate')) {
			Session::put('stat_startDate', $input['startDate']);
		}
		else {
			$input['startDate'] = Session::get('stat_startDate');
			if (!$input['startDate']) {
				$input['startDate'] = date("d.m.Y");
			}
		}
		if (Input::has('endDate')) {
			Session::put('stat_endDate', $input['endDate']);
		}
		else {
			$input['endDate'] = Session::get('stat_endDate');
			if (!$input['endDate']) {
				$input['endDate'] = date('d.m.Y');
			}
		}

		$startDate = explode('.', $input['startDate']);
		if (checkdate($startDate[1], $startDate[0], $startDate[2])) {
			$startDate = "{$startDate[2]}-{$startDate[1]}-{$startDate[0]}";
		}
		$endDate = explode('.', $input['endDate']);
		if (checkdate($endDate[1], $endDate[0], $endDate[2])) {
			$endDate = "{$endDate[2]}-{$endDate[1]}-{$endDate[0]}";
		}
		$dateWhere	 = DB::raw("visits.created_at>DATE('{$startDate}') AND visits.created_at<DATE(DATE_ADD('{$endDate}', INTERVAL 1 DAY))");
		$dateWhere2	 = DB::raw("leads.created_at>DATE('{$startDate}') AND leads.created_at<DATE(DATE_ADD('{$endDate}', INTERVAL 1 DAY))");
		$dateWhere3	 = DB::raw("visit_utm_campaign_budgets.date>=DATE('{$startDate}') AND visit_utm_campaign_budgets.date<=DATE('{$endDate}')");

		$remember	 = 1;
		$date1		 = new DateTime(date('Y-m-d'));
		$date2		 = new DateTime($endDate);
		if ($date2 < $date1) {
			$remember = 60;
		}

		if (!Input::has('projectId')) {
			$input['projectId'] = Session::get($this->projectPrefix . '_work_projectId');
		}
		$project = false;
		if (!$input['projectId']) {
			$project						 = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->first();
			$input['projectId']	 = $project->id;
		}
		if (!$project) {
			$project = $this->user->projects()->whereUsecrm(1)->whereType(Project::TYPE_SHOP)->whereId($input['projectId'])->first();
		}
		Session::put($this->projectPrefix . '_work_projectId', $input['projectId']);
		if (!$project) {
			return \App::abort(404);
		}

		if (!Input::has('siteId')) {
			$input['siteId'] = Session::get('stat_' . $input['projectId'] . '_siteId');
		}
		$site = false;
		if (!$input['siteId']) {
			$site = $project->sites()->first();
			if ($site) {
				$input['siteId'] = $site->id;
			}
		}
		if (!$site) {
			$site = $project->sites()->whereId($input['siteId'])->first();
		}
		Session::put('stat_' . $input['projectId'] . '_siteId', $input['siteId']);

//		$utmSourceStat = VisitUtmSource::where('project_id', $project->id)
//						->orderBy('name', 'ASC')
//						->get(array(DB::raw('id, name, params, 0 as visits, 0 as visitors, 0 as cost, 0 as leads_count, 0 as partners_count')));
//		foreach ($utmSourceStat as $item) {
//			$item->value = explode('=', $item->params);
//			$item->value = $item->value[1];
//			$item->name = $item->name;
//		}
//
//		foreach ($utmSourceStat as $item) {
//			$res = VisitParam::leftJoin('visits_params', function($join) {
//								$join->on('visits_params.param_id', '=', 'visit_params.id');
//							})
//							->leftJoin('visits', function($join) {
//								$join->on('visits.id', '=', 'visits_params.visit_id');
//							})
//							->where('visit_params.name', '=', 'utm_source')
//							->where('visits_params.value', '=', $item['value'])
//							->where('visits.project_id', $project->id)
//							->whereRaw($dateWhere)
//							->remember(10)
//							->first(array(DB::raw('COUNT(*) as visits, COUNT(DISTINCT visitor_id) as visitors')));
//			if ($res) {
//				$item->visits		 = $res->visits;
//				$item->visitors	 = $res->visitors;
//			}
//
//			$res = Lead::withTrashed()
//							->leftJoin('visits', function($join) {
//								$join->on('visits.id', '=', 'leads.visit_id');
//							})
//							->leftJoin('visits_params', function($join) {
//								$join->on('visits_params.visit_id', '=', 'visits.id');
//							})
//							->leftJoin('visit_params', function($join) {
//								$join->on('visit_params.id', '=', 'visits_params.param_id');
//							})
//							->where('visit_params.name', '=', 'utm_source')
//							->where('visits_params.value', '=', $item['value'])
//							->where('leads.project_id', $project->id)
//							->whereRaw($dateWhere2)
//							->groupBy('visits_params.value')
//							->remember(10)
//							->first(array(DB::raw('COUNT(DISTINCT visits.visitor_id) as leads_cnt')));
//			if ($res) {
//				$item->leads_cnt = $res->leads_cnt;
//			}
//			$cost = VisitUtmCampaignBudget::leftJoin('visit_utm_campaigns', function($join) {
//							$join->on('visit_utm_campaigns.id', '=', 'visit_utm_campaign_budgets.visit_utm_campaign_id');
//						})
//						->leftJoin('visit_utm_sources', function($join) {
//							$join->on('visit_utm_sources.id', '=', 'visit_utm_campaigns.visit_utm_source_id');
//						})
//						->where('visit_utm_sources.project_id', '=', $project->id)
//						->where('visit_utm_sources.id', '=', $item['id'])
//						->whereRaw($dateWhere3)
//						->sum('amount');
//			if($cost) {
//				$item->cost = $cost;
//			}
//
//			$res = Partner::leftJoin('leads', function($join) {
//								$join->on('leads.id', '=', 'partners.lead_id');
//							})
//							->leftJoin('visits', function($join) {
//								$join->on('visits.id', '=', 'leads.visit_id');
//							})
//							->leftJoin('visits_params', function($join) {
//								$join->on('visits_params.visit_id', '=', 'visits.id');
//							})
//							->leftJoin('visit_params', function($join) {
//								$join->on('visit_params.id', '=', 'visits_params.param_id');
//							})
//							->where('visit_params.name', '=', 'utm_source')
//							->where('visits_params.value', '=', $item['value'])
//							->where('leads.project_id', $project->id)
//							->whereRaw($dateWhere)
//							->checkStatus(array('statusType' => 'agreed', 'type' => 'all'))
//							->groupBy('visits_params.value')
//							->remember(10)
//							->first(array(DB::raw('COUNT(*) as partners_cnt')));
//			if ($res) {
//				$item->partners_cnt = $res->partners_cnt;
//			}
//
//			$utmCampaignStat = VisitUtmCampaign::where('project_id', $project->id)
//							->whereVisitUtmSourceId($item['id'])
//							->get(array(DB::raw('id, name, params, 0 as visits, 0 as visitors, 0 as cost, 0 as leads_count, 0 as partners_count')));
//			foreach ($utmCampaignStat as $item2) {
//				$item2->value = explode('=', $item2->params);
//				$item2->value = $item2->value[1];
//				$item2->name = $item2->name;
//
//				$res = VisitParam::leftJoin('visits_params', function($join) {
//									$join->on('visits_params.param_id', '=', 'visit_params.id');
//								})
//								->leftJoin('visits', function($join) {
//									$join->on('visits.id', '=', 'visits_params.visit_id');
//								})
//								->whereRaw("visits.id IN (
//									select visits.id
//									from `visit_params`
//									left join `visits_params` on `visits_params`.`param_id` = `visit_params`.`id`
//									left join `visits` on `visits`.`id` = `visits_params`.`visit_id`
//									where `visit_params`.`deleted_at` is null
//										and `visit_params`.`name` = 'utm_source'
//										and `visits_params`.`value` = '{$item->value}'
//										and `visits`.`project_id` = {$project->id}
//										and {$dateWhere}
//								)")
//								->where('visit_params.name', '=', 'utm_campaign')
//								->where('visits_params.value', '=', $item2->value)
//								->where('visits.project_id', $project->id)
//								->whereRaw($dateWhere)
//								->remember(10)
//								->first(array(DB::raw('COUNT(*) as visits, COUNT(DISTINCT visitor_id) as visitors, 0 as cost, 0 as leads_count, 0 as partners_count')));
//				if ($res) {
//					$item2->visits		 = $res->visits;
//					$item2->visitors	 = $res->visitors;
//				}
//
//				$res = Lead::withTrashed()
//								->leftJoin('visits', function($join) {
//									$join->on('visits.id', '=', 'leads.visit_id');
//								})
//								->leftJoin('visits_params', function($join) {
//									$join->on('visits_params.visit_id', '=', 'visits.id');
//								})
//								->leftJoin('visit_params', function($join) {
//									$join->on('visit_params.id', '=', 'visits_params.param_id');
//								})
//								->whereRaw("visits.id IN (
//									select visits.id
//									from `visit_params`
//									left join `visits_params` on `visits_params`.`param_id` = `visit_params`.`id`
//									left join `visits` on `visits`.`id` = `visits_params`.`visit_id`
//									where `visit_params`.`deleted_at` is null
//										and `visit_params`.`name` = 'utm_source'
//										and `visits_params`.`value` = '{$item->value}'
//										and `visits`.`project_id` = {$project->id}
//										and {$dateWhere2}
//								)")
//								->where('visit_params.name', '=', 'utm_campaign')
//								->where('visits_params.value', '=', $item2->value)
//								->where('leads.project_id', $project->id)
//								->whereRaw($dateWhere2)
//								->remember(10)
//								->first(array(DB::raw('COUNT(DISTINCT visits.visitor_id) as leads_cnt')));
//				if ($res) {
//					$item2->leads_cnt = $res->leads_cnt;
//				}
//				$cost = VisitUtmCampaignBudget::leftJoin('visit_utm_campaigns', function($join) {
//								$join->on('visit_utm_campaigns.id', '=', 'visit_utm_campaign_budgets.visit_utm_campaign_id');
//							})
//							->where('visit_utm_campaigns.project_id', '=', $project->id)
//							->where('visit_utm_campaigns.id', '=', $item2['id'])
//							->whereRaw($dateWhere3)
//							->sum('amount');
//				if ($cost) {
//					$item2->cost = $cost;
//				}
//
//				$res = Partner::leftJoin('leads', function($join) {
//									$join->on('leads.id', '=', 'partners.lead_id');
//								})
//								->leftJoin('visits', function($join) {
//									$join->on('visits.id', '=', 'leads.visit_id');
//								})
//								->leftJoin('visits_params', function($join) {
//									$join->on('visits_params.visit_id', '=', 'visits.id');
//								})
//								->leftJoin('visit_params', function($join) {
//									$join->on('visit_params.id', '=', 'visits_params.param_id');
//								})
//								->whereRaw("visits.id IN (
//									select visits.id
//									from `visit_params`
//									left join `visits_params` on `visits_params`.`param_id` = `visit_params`.`id`
//									left join `visits` on `visits`.`id` = `visits_params`.`visit_id`
//									where `visit_params`.`deleted_at` is null
//										and `visit_params`.`name` = 'utm_source'
//										and `visits_params`.`value` = '{$item->value}'
//										and `visits`.`project_id` = {$project->id}
//										and {$dateWhere}
//								)")
//								->where('visit_params.name', '=', 'utm_campaign')
//								->where('visits_params.value', '=', $item2->value)
//								->where('leads.project_id', $project->id)
//								->whereRaw($dateWhere)
//								->checkStatus(array('statusType' => 'agreed', 'type' => 'all'))
//								->remember(10)
//								->first(array(DB::raw('COUNT(*) as partners_cnt')));
//				if ($res) {
//					$item2->partners_cnt = $res->partners_cnt;
//				}
//
//			}
//			$item->campaigns = $utmCampaignStat;
//		}

		$utmSourceStat = VisitParam::leftJoin('visits_params', function($join) {
							$join->on('visits_params.param_id', '=', 'visit_params.id');
						})
						->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'visits_params.visit_id');
						})
						->where('visit_params.name', '=', 'utm_source')
						->where('visits.project_id', $project->id)
						->whereRaw($dateWhere)
						->groupBy('visits_params.value')
						->orderBy('visits_params.value', 'ASC')
						->remember($remember)
						->get(array(DB::raw('visits_params.value, COUNT(*) as visits, 0 as visitors, 0 as cost, 0 as leads_count, 0 as partners_count, 0 as partners_profit')));
		$utmSourceLeadStat = Lead::withTrashed()
						->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'leads.visit_id');
						})
						->leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'visits.id');
						})
						->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})
						->where('visit_params.name', '=', 'utm_source')
						->where('leads.project_id', $project->id)
						->whereRaw($dateWhere2)
						->groupBy('visits_params.value')
						->remember($remember)
						->get(array(DB::raw('visits_params.value, COUNT(DISTINCT visits.visitor_id) as leads_cnt')));

		foreach ($utmSourceLeadStat as $item) {
			foreach ($utmSourceStat as $stat) {
				if ($item->value == $stat->value) {
					$stat->leads_count = $item->leads_cnt;
				}
			}
		}
		$utmSourcePartnerStat = Partner::leftJoin('leads', function($join) {
							$join->on('leads.id', '=', 'partners.lead_id');
						})->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'leads.visit_id');
						})->leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'visits.id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->where('visit_params.name', '=', 'utm_source')
						->where('leads.project_id', $project->id)
						->whereRaw($dateWhere)
						->checkStatus(array('statusType' => 'agreed', 'type' => 'all'))
						->groupBy('visits_params.value')
						->get(array(DB::raw('visits_params.value, COUNT(*) as partners_cnt, SUM(cartprice-purchaseprice) as profit')));

		foreach ($utmSourcePartnerStat as $item) {
			foreach ($utmSourceStat as $stat) {
				if ($item->value == $stat->value) {
					$stat->partners_count	 = $item->partners_cnt;
					$stat->partners_profit = $item->profit;
				}
			}
		}

		foreach ($utmSourceStat as $stat) {
//			$ids = VisitParam::leftJoin('visits_params', function($join) {
//								$join->on('visits_params.param_id', '=', 'visit_params.id');
//							})
//							->leftJoin('visits', function($join) {
//								$join->on('visits.id', '=', 'visits_params.visit_id');
//							})
//							->where('visit_params.name', '=', 'utm_source')
//							->where('visits_params.value', '=', $stat->value)
//							->where('visits.project_id', $project->id)
//							->whereRaw($dateWhere)
//							->get(array(DB::raw('visits.id')));
//			$idsArray = array();
//			foreach ($ids as $id) {
//				$idsArray[] = $id->id;
//			}

			$cost = VisitUtmCampaignBudget::leftJoin('visit_utm_campaigns', function($join) {
								$join->on('visit_utm_campaigns.id', '=', 'visit_utm_campaign_budgets.visit_utm_campaign_id');
							})
							->leftJoin('visit_utm_sources', function($join) {
								$join->on('visit_utm_sources.id', '=', 'visit_utm_campaigns.visit_utm_source_id');
							})
							->where('visit_utm_sources.project_id', '=', $project->id)
							->where('visit_utm_sources.params', '=', "utm_source={$stat['value']}")
							->whereRaw($dateWhere3)
							->sum('amount');
			if ($cost) {
				$stat->cost = $cost;
			}

			$utmCampaignStat = VisitParam::leftJoin('visits_params', function($join) {
								$join->on('visits_params.param_id', '=', 'visit_params.id');
							})
							->leftJoin('visits', function($join) {
								$join->on('visits.id', '=', 'visits_params.visit_id');
							})
							->whereRaw("visits.id IN (
								select visits.id
								from `visit_params`
								left join `visits_params` on `visits_params`.`param_id` = `visit_params`.`id`
								left join `visits` on `visits`.`id` = `visits_params`.`visit_id`
								where `visit_params`.`deleted_at` is null
									and `visit_params`.`name` = 'utm_source'
									and `visits_params`.`value` = '{$stat->value}'
									and `visits`.`project_id` = {$project->id}
									and {$dateWhere}
							)")
							->where('visit_params.name', '=', 'utm_campaign')
							->where('visits.project_id', $project->id)
							->whereRaw($dateWhere)
							->groupBy('visits_params.value')
							->orderBy('visits_params.value', 'ASC')
							->remember($remember)
							->get(array(DB::raw('visits_params.value, COUNT(*) as visits, 0 as visitors, 0 as cost, 0 as leads_count, 0 as partners_count, 0 as partners_profit')));

			$utmCampaignLeadStat = Lead::withTrashed()
							->leftJoin('visits', function($join) {
								$join->on('visits.id', '=', 'leads.visit_id');
							})
							->leftJoin('visits_params', function($join) {
								$join->on('visits_params.visit_id', '=', 'visits.id');
							})
							->leftJoin('visit_params', function($join) {
								$join->on('visit_params.id', '=', 'visits_params.param_id');
							})
							->whereRaw("visits.id IN (
								select visits.id
								from `visit_params`
								left join `visits_params` on `visits_params`.`param_id` = `visit_params`.`id`
								left join `visits` on `visits`.`id` = `visits_params`.`visit_id`
								where `visit_params`.`deleted_at` is null
									and `visit_params`.`name` = 'utm_source'
									and `visits_params`.`value` = '{$stat->value}'
									and `visits`.`project_id` = {$project->id}
									and {$dateWhere2}
							)")
							->where('visit_params.name', '=', 'utm_campaign')
							->where('leads.project_id', $project->id)
							->whereRaw($dateWhere2)
							->groupBy('visits_params.value')
							->remember($remember)
							->get(array(DB::raw('visits_params.value, COUNT(DISTINCT visits.visitor_id) as leads_cnt')));

			foreach ($utmCampaignLeadStat as $item) {
				foreach ($utmCampaignStat as $stat2) {
					if ($item->value == $stat2->value) {
						$stat2->leads_count = $item->leads_cnt;
					}
				}
			}

			$utmCampaignPartnerStat = Partner::leftJoin('leads', function($join) {
								$join->on('leads.id', '=', 'partners.lead_id');
							})
							->leftJoin('visits', function($join) {
								$join->on('visits.id', '=', 'leads.visit_id');
							})
							->leftJoin('visits_params', function($join) {
								$join->on('visits_params.visit_id', '=', 'visits.id');
							})
							->leftJoin('visit_params', function($join) {
								$join->on('visit_params.id', '=', 'visits_params.param_id');
							})
							->whereRaw("visits.id IN (
								select visits.id
								from `visit_params`
								left join `visits_params` on `visits_params`.`param_id` = `visit_params`.`id`
								left join `visits` on `visits`.`id` = `visits_params`.`visit_id`
								where `visit_params`.`deleted_at` is null
									and `visit_params`.`name` = 'utm_source'
									and `visits_params`.`value` = '{$stat->value}'
									and `visits`.`project_id` = {$project->id}
									and {$dateWhere}
							)")
							->where('visit_params.name', '=', 'utm_campaign')
							->where('leads.project_id', $project->id)
							->whereRaw($dateWhere)
							->checkStatus(array('statusType' => 'agreed', 'type' => 'all'))
							->groupBy('visits_params.value')
							->get(array(DB::raw('visits_params.value, COUNT(*) as partners_cnt, SUM(cartprice-purchaseprice) as profit')));

			foreach ($utmCampaignPartnerStat as $item) {
				foreach ($utmCampaignStat as $stat2) {
					if ($item->value == $stat2->value) {
						$stat2->partners_count	 = $item->partners_cnt;
						$stat2->partners_profit	 = $item->profit;
					}
				}
			}

			foreach ($utmCampaignStat as $stat2) {
				$cost = VisitUtmCampaignBudget::leftJoin('visit_utm_campaigns', function($join) {
									$join->on('visit_utm_campaigns.id', '=', 'visit_utm_campaign_budgets.visit_utm_campaign_id');
								})
								->where('visit_utm_campaigns.project_id', '=', $project->id)
								->where('visit_utm_campaigns.params', '=', "utm_campaign={$stat2['value']}")
								->whereRaw($dateWhere3)
								->sum('amount');
				if ($cost) {
					$stat2->cost = $cost;
				}
			}

			$stat->campaigns = $utmCampaignStat;
//			unset($ids);
//			unset($idsArray);
		}


		$noUtmSourceStat = Visit::leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'visits.id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->whereNull('visit_params.name')
						->where('visits.project_id', $project->id)
						->whereRaw($dateWhere)
						->groupBy('visits_params.value')
						->first(array(DB::raw('visits_params.value, COUNT(*) as visits, 0 as visitors, 0 as cost, 0 as leads_count, 0 as partners_count')));
		if (!$noUtmSourceStat) {
			$noUtmSourceStat											 = new stdClass();
			$noUtmSourceStat->value								 = null;
			$noUtmSourceStat->visits							 = 0;
			$noUtmSourceStat->visitors						 = 0;
			$noUtmSourceStat->cost								 = 0;
			$noUtmSourceStat->leads_count					 = 0;
			$noUtmSourceStat->partners_count			 = 0;
			$noUtmSourceStat->first_leads_count		 = 0;
			$noUtmSourceStat->first_partners_count = 0;
		}

		$noUtmSourceLeadStat = Lead::withTrashed()
						->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'leads.visit_id');
						})->leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'visits.id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->whereNull('visit_params.name')
						->where('leads.project_id', $project->id)
						->whereRaw($dateWhere2)
						->groupBy('visits_params.value')
						->first(array(DB::raw('visits_params.value, COUNT(DISTINCT visits.visitor_id) as leads_cnt')));
		if ($noUtmSourceLeadStat) {
			$noUtmSourceStat->leads_count = $noUtmSourceLeadStat->leads_cnt;
		}

		$noUtmSourcePartnerStat = Partner::leftJoin('leads', function($join) {
							$join->on('leads.id', '=', 'partners.lead_id');
						})->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'leads.visit_id');
						})->leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'visits.id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->whereNull('visit_params.name')
						->where('leads.project_id', $project->id)
						->whereRaw($dateWhere)
						->checkStatus(array('statusType' => 'agreed', 'type' => 'all'))
						->groupBy('visits_params.value')
						->first(array(DB::raw('visits_params.value, COUNT(*) as partners_cnt')));
		if ($noUtmSourcePartnerStat) {
			$noUtmSourceStat->partners_count = $noUtmSourcePartnerStat->partners_cnt;
		}


		$utmSourceStatAll = VisitParam::leftJoin('visits_params', function($join) {
							$join->on('visits_params.param_id', '=', 'visit_params.id');
						})->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'visits_params.visit_id');
						})->where('visit_params.name', '=', 'utm_source')
						->where('visits.project_id', $project->id)
						->whereRaw($dateWhere)
						->groupBy('visits_params.value')
						->orderBy('visits_params.value', 'ASC')
						->get(array(DB::raw('visits_params.value, COUNT(*) as visits, 0 as visitors, 0 as cost, 0 as leads_count, 0 as partners_count')));

		$utmSourceLeadStat = Lead::withTrashed()
						->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'leads.visit_id');
						})->leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'visits.id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->where('visit_params.name', '=', 'utm_source')
						->where('leads.project_id', $project->id)
						->whereRaw($dateWhere2)
						->groupBy('visits_params.value')
						->get(array(DB::raw('visits_params.value, COUNT(DISTINCT visits.visitor_id) as leads_cnt')));

		foreach ($utmSourceLeadStat as $item) {
			foreach ($utmSourceStat as $stat) {
				if ($item->value == $stat->value) {
					$stat->leads_count = $item->leads_cnt;
				}
			}
		}

		$utmSourcePartnerStat = Partner::leftJoin('leads', function($join) {
							$join->on('leads.id', '=', 'partners.lead_id');
						})->leftJoin('visits', function($join) {
							$join->on('visits.id', '=', 'leads.visit_id');
						})->leftJoin('visits_params', function($join) {
							$join->on('visits_params.visit_id', '=', 'visits.id');
						})->leftJoin('visit_params', function($join) {
							$join->on('visit_params.id', '=', 'visits_params.param_id');
						})->where('visit_params.name', '=', 'utm_source')
						->where('leads.project_id', $project->id)
						->whereRaw($dateWhere)
						->checkStatus(array('statusType' => 'agreed', 'type' => 'all'))
						->groupBy('visits_params.value')
						->get(array(DB::raw('visits_params.value, COUNT(*) as partners_cnt')));

		foreach ($utmSourcePartnerStat as $item) {
			foreach ($utmSourceStat as $stat) {
				if ($item->value == $stat->value) {
					$stat->partners_count = $item->partners_cnt;
				}
			}
		}

		return $this->setupLayout('stat.visits.utmsourceext')
										->with('dateWhere', $dateWhere)
										->with('project', $project)
										->with('site', $site)
										->with('utmSourceStat', $utmSourceStat)
										->with('noUtmSourceStat', $noUtmSourceStat)
										->with('input', $input);
	}

}