<?php

class BudgetStatController extends BaseController {

	/**
	 * Constructor
	 */
	public function __construct() {
		// Establish Filters
		$this->beforeFilter('auth');

		if (Auth::check()) {
			$this->user			 = Auth::user();
			$this->projects	 = $this->user->budgetProjects;
		}
		$this->projectPrefix = 'budget';
		parent::__construct();

		$this->beforeFilter('csrf', array('only' => array('createOperation')));

		if ($this->user && !$this->user->hasAnyRole(array(Role::ROLE_BUDGET))) {
			$this->beforeFilter('hasRole:' . Role::ROLE_BUDGET);
		}
	}

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

		if (!$this->user->budgetProjects->count()) {
			return Redirect::to('/');
		}
		if (Input::has('projectId')) {
			Session::put('work_projectId', $input['projectId']);
			return Redirect::to('budget/stat/costs');
		}

		$endDate = date('d.m.Y');
		if (Input::has('endDate')) {
			$endDate = $input['endDate'];
		}
		$date				 = explode('.', $endDate);
		$endDateRaw	 = "{$date[2]}-{$date[1]}-{$date[0]}";

		$budgetCostStat		 = array(
				BudgetCost::MANAGED_FULL => array(
						1	 => array(),
						2	 => array(),
						3	 => array(),
				),
				BudgetCost::MANAGED_HALF => array(
						1	 => array(),
						2	 => array(),
						3	 => array(),
				),
				BudgetCost::MANAGED_NONE => array(
						1	 => array(),
						2	 => array(),
						3	 => array(),
				),
		);
		$budgetCostStatSum = array(
				'type'	 => array(
						BudgetCost::MANAGED_FULL => 0,
						BudgetCost::MANAGED_HALF => 0,
						BudgetCost::MANAGED_NONE => 0,
				),
				'amount' => array(
						1	 => 0,
						2	 => 0,
						3	 => 0,
				),
		);
		$allCostSum				 = 0;
		$allCostPrevSum		 = 0;
		if (BudgetOperation::whereProjectId($this->workProject->id)->whereRaw(DB::raw('date_format(date, "%Y%m")=?'), array($date[2] . $date[1]))->count()) {
			$allCostSum			 = BudgetOperation::whereProjectId($this->workProject->id)
							->whereIsnooutcome(0)
							->whereType(BudgetOperation::TYPE_OUTCOMING)
							->whereRaw(DB::raw('date_format(date, "%Y%m")=date_format(?, "%Y%m")'), array($endDateRaw))
							->whereRaw(DB::raw('DAY(date)<=?'), array($date[0]))
							->sum('amount');
			$allCostPrevSum	 = BudgetOperation::whereProjectId($this->workProject->id)
							->whereIsnooutcome(0)
							->withTrashed()->whereIsarchived(1)
							->whereType(BudgetOperation::TYPE_OUTCOMING)
							->whereRaw(DB::raw('date_format(date, "%Y%m")=date_format(DATE_SUB(?, INTERVAL 1 MONTH), "%Y%m")'), array($endDateRaw))
							->whereRaw(DB::raw('DAY(date)<=?'), array($date[0]))
							->sum('amount');
			$allCostSum			 = round($allCostSum / 100, 2);
			$allCostPrevSum	 = round($allCostPrevSum / 100, 2);
			foreach ($this->workProject->budgetCosts as $budgetCost) {
				$sum = BudgetOperation::whereProjectId($this->workProject->id)
								->whereIsnooutcome(0)
								->whereBudgetCostId($budgetCost->id)
								->whereType(BudgetOperation::TYPE_OUTCOMING)
								->whereRaw(DB::raw('date_format(date, "%Y%m")=date_format(?, "%Y%m")'), array($endDateRaw))
								->whereRaw(DB::raw('DAY(date)<=?'), array($date[0]))
								->sum('amount');
				if ($sum) {
					$sum		 = round($sum / 100, 2);
					$percent = round(($sum / $allCostSum) * 100, 2);
					$item		 = array(
							'name'		 => $budgetCost->name,
							'amount'	 => $sum,
							'percent'	 => round(($sum / $allCostSum) * 100, 2)
					);
					$budgetCostStatSum['type'][$budgetCost->managed] += $sum;
					if ($percent < 0.5) {
						$budgetCostStat[$budgetCost->managed][3][$budgetCost->id] = $item;
						$budgetCostStatSum['amount'][3] += $sum;
					}
					elseif ($percent >= 0.5 && $percent < 2) {
						$budgetCostStat[$budgetCost->managed][2][$budgetCost->id] = $item;
						$budgetCostStatSum['amount'][2] += $sum;
					}
					else {
						$budgetCostStat[$budgetCost->managed][1][$budgetCost->id] = $item;
						$budgetCostStatSum['amount'][1] += $sum;
					}
				}
			}
		}
		else {
			$allCostSum			 = BudgetOperation::whereProjectId($this->workProject->id)
							->withTrashed()->whereIsarchived(1)
							->whereIsnooutcome(0)
							->whereType(BudgetOperation::TYPE_OUTCOMING)
							->whereRaw(DB::raw('date_format(date, "%Y%m")=date_format(?, "%Y%m")'), array($endDateRaw))
							->whereRaw(DB::raw('DAY(date)<=?'), array($date[0]))
							->sum('amount');
			$allCostPrevSum	 = BudgetOperation::whereProjectId($this->workProject->id)
							->withTrashed()->whereIsarchived(1)
							->whereIsnooutcome(0)
							->withTrashed()->whereIsarchived(1)
							->whereType(BudgetOperation::TYPE_OUTCOMING)
							->whereRaw(DB::raw('date_format(date, "%Y%m")=date_format(DATE_SUB(?, INTERVAL 1 MONTH), "%Y%m")'), array($endDateRaw))
							->whereRaw(DB::raw('DAY(date)<=?'), array($date[0]))
							->sum('amount');
			$allCostSum			 = round($allCostSum / 100, 2);
			$allCostPrevSum	 = round($allCostPrevSum / 100, 2);
			foreach ($this->workProject->budgetCosts as $budgetCost) {
				$sum = BudgetOperation::whereProjectId($this->workProject->id)
								->withTrashed()->whereIsarchived(1)
								->whereIsnooutcome(0)
								->whereBudgetCostId($budgetCost->id)
								->whereType(BudgetOperation::TYPE_OUTCOMING)
								->whereRaw(DB::raw('date_format(date, "%Y%m")=date_format(?, "%Y%m")'), array($endDateRaw))
								->whereRaw(DB::raw('DAY(date)<=?'), array($date[0]))
								->sum('amount');
				if ($sum) {
					$sum		 = round($sum / 100, 2);
					$percent = round(($sum / $allCostSum) * 100, 2);
					$item		 = array(
							'name'		 => $budgetCost->name,
							'amount'	 => $sum,
							'percent'	 => round(($sum / $allCostSum) * 100, 2)
					);
					$budgetCostStatSum['type'][$budgetCost->managed] += $sum;
					if ($percent < 0.5) {
						$budgetCostStat[$budgetCost->managed][3][$budgetCost->id] = $item;
						$budgetCostStatSum['amount'][3] += $sum;
					}
					elseif ($percent >= 0.5 && $percent < 2) {
						$budgetCostStat[$budgetCost->managed][2][$budgetCost->id] = $item;
						$budgetCostStatSum['amount'][2] += $sum;
					}
					else {
						$budgetCostStat[$budgetCost->managed][1][$budgetCost->id] = $item;
						$budgetCostStatSum['amount'][1] += $sum;
					}
				}
			}
		}

		return $this->setupLayout('budget.stat.costs')
										->with('endDate', $endDate)
										->with('budgetCostStat', $budgetCostStat)
										->with('budgetCostStatSum', $budgetCostStatSum)
										->with('allCostPrevSum', $allCostPrevSum)
										->with('allCostSumPercent', ($allCostPrevSum) ? (100 - round(($allCostSum * 100 / $allCostPrevSum), 2)) : 0)
										->with('allCostSum', $allCostSum)
										->with('input', $input);
	}

	//формирование отчета по расходам на экспорт
	public function costsExport() {
		$fileName = "BudgetCostsExport-" . date('Y-m-d');
		Excel::create($fileName, function($excel) {
			$excel->sheet('Лист1', function($sheet) {
				$input = Input::all();
				$projectId	 = $input['projectId'];
				$endDate = date('d.m.Y');
				if (Input::has('endDate')) {
					$endDate = $input['endDate'];
				}
				$date				 = explode('.', $endDate);
				$endDateRaw	 = "{$date[2]}-{$date[1]}-{$date[0]}";
				$project		 = Project::whereUsebudget(1)->whereid($projectId)->first();
				if ($project) {
					$budgetCostStat		 = array(
							BudgetCost::MANAGED_FULL => array(
									1	 => array(),
									2	 => array(),
									3	 => array(),
							),
							BudgetCost::MANAGED_HALF => array(
									1	 => array(),
									2	 => array(),
									3	 => array(),
							),
							BudgetCost::MANAGED_NONE => array(
									1	 => array(),
									2	 => array(),
									3	 => array(),
							),
					);
					$budgetCostStatSum = array(
							'type'	 => array(
									BudgetCost::MANAGED_FULL => 0,
									BudgetCost::MANAGED_HALF => 0,
									BudgetCost::MANAGED_NONE => 0,
							),
							'amount' => array(
									1	 => 0,
									2	 => 0,
									3	 => 0,
							),
					);
					$allCostSum				 = 0;
					$allCostPrevSum		 = 0;
					if (BudgetOperation::whereProjectId($this->workProject->id)->whereRaw(DB::raw('date_format(date, "%Y%m")=?'), array($date[2] . $date[1]))->count()) {
						$allCostSum			 = BudgetOperation::whereProjectId($this->workProject->id)
										->whereIsnooutcome(0)
										->whereType(BudgetOperation::TYPE_OUTCOMING)
										->whereRaw(DB::raw('date_format(date, "%Y%m")=date_format(?, "%Y%m")'), array($endDateRaw))
										->whereRaw(DB::raw('DAY(date)<=?'), array($date[0]))
										->sum('amount');
						$allCostPrevSum	 = BudgetOperation::whereProjectId($this->workProject->id)
										->whereIsnooutcome(0)
										->withTrashed()->whereIsarchived(1)
										->whereType(BudgetOperation::TYPE_OUTCOMING)
										->whereRaw(DB::raw('date_format(date, "%Y%m")=date_format(DATE_SUB(?, INTERVAL 1 MONTH), "%Y%m")'), array($endDateRaw))
										->whereRaw(DB::raw('DAY(date)<=?'), array($date[0]))
										->sum('amount');
						$allCostSum			 = round($allCostSum / 100, 2);
						$allCostPrevSum	 = round($allCostPrevSum / 100, 2);
						foreach ($this->workProject->budgetCosts as $budgetCost) {
							$sum = BudgetOperation::whereProjectId($this->workProject->id)
											->whereIsnooutcome(0)
											->whereBudgetCostId($budgetCost->id)
											->whereType(BudgetOperation::TYPE_OUTCOMING)
											->whereRaw(DB::raw('date_format(date, "%Y%m")=date_format(?, "%Y%m")'), array($endDateRaw))
											->whereRaw(DB::raw('DAY(date)<=?'), array($date[0]))
											->sum('amount');
							if ($sum) {
								$sum		 = round($sum / 100, 2);
								$percent = round(($sum / $allCostSum) * 100, 2);
								$item		 = array(
										'name'		 => $budgetCost->name,
										'amount'	 => $sum,
										'percent'	 => round(($sum / $allCostSum) * 100, 2)
								);
								$budgetCostStatSum['type'][$budgetCost->managed] += $sum;
								if ($percent < 0.5) {
									$budgetCostStat[$budgetCost->managed][3][$budgetCost->id] = $item;
									$budgetCostStatSum['amount'][3] += $sum;
								}
								elseif ($percent >= 0.5 && $percent < 2) {
									$budgetCostStat[$budgetCost->managed][2][$budgetCost->id] = $item;
									$budgetCostStatSum['amount'][2] += $sum;
								}
								else {
									$budgetCostStat[$budgetCost->managed][1][$budgetCost->id] = $item;
									$budgetCostStatSum['amount'][1] += $sum;
								}
							}
						}
					}
					else {
						$allCostSum			 = BudgetOperation::whereProjectId($this->workProject->id)
										->withTrashed()->whereIsarchived(1)
										->whereIsnooutcome(0)
										->whereType(BudgetOperation::TYPE_OUTCOMING)
										->whereRaw(DB::raw('date_format(date, "%Y%m")=date_format(?, "%Y%m")'), array($endDateRaw))
										->whereRaw(DB::raw('DAY(date)<=?'), array($date[0]))
										->sum('amount');
						$allCostPrevSum	 = BudgetOperation::whereProjectId($this->workProject->id)
										->withTrashed()->whereIsarchived(1)
										->whereIsnooutcome(0)
										->withTrashed()->whereIsarchived(1)
										->whereType(BudgetOperation::TYPE_OUTCOMING)
										->whereRaw(DB::raw('date_format(date, "%Y%m")=date_format(DATE_SUB(?, INTERVAL 1 MONTH), "%Y%m")'), array($endDateRaw))
										->whereRaw(DB::raw('DAY(date)<=?'), array($date[0]))
										->sum('amount');
						$allCostSum			 = round($allCostSum / 100, 2);
						$allCostPrevSum	 = round($allCostPrevSum / 100, 2);
						foreach ($this->workProject->budgetCosts as $budgetCost) {
							$sum = BudgetOperation::whereProjectId($this->workProject->id)
											->withTrashed()->whereIsarchived(1)
											->whereIsnooutcome(0)
											->whereBudgetCostId($budgetCost->id)
											->whereType(BudgetOperation::TYPE_OUTCOMING)
											->whereRaw(DB::raw('date_format(date, "%Y%m")=date_format(?, "%Y%m")'), array($endDateRaw))
											->whereRaw(DB::raw('DAY(date)<=?'), array($date[0]))
											->sum('amount');
							if ($sum) {
								$sum		 = round($sum / 100, 2);
								$percent = round(($sum / $allCostSum) * 100, 2);
								$item		 = array(
										'name'		 => $budgetCost->name,
										'amount'	 => $sum,
										'percent'	 => round(($sum / $allCostSum) * 100, 2)
								);
								$budgetCostStatSum['type'][$budgetCost->managed] += $sum;
								if ($percent < 0.5) {
									$budgetCostStat[$budgetCost->managed][3][$budgetCost->id] = $item;
									$budgetCostStatSum['amount'][3] += $sum;
								}
								elseif ($percent >= 0.5 && $percent < 2) {
									$budgetCostStat[$budgetCost->managed][2][$budgetCost->id] = $item;
									$budgetCostStatSum['amount'][2] += $sum;
								}
								else {
									$budgetCostStat[$budgetCost->managed][1][$budgetCost->id] = $item;
									$budgetCostStatSum['amount'][1] += $sum;
								}
							}
						}
					}
					$sheet->loadView('budget.stat.costs_export')
										->with('export', true)
										->with('endDate', $endDate)
										->with('budgetCostStat', $budgetCostStat)
										->with('budgetCostStatSum', $budgetCostStatSum)
										->with('allCostPrevSum', $allCostPrevSum)
										->with('allCostSumPercent', ($allCostPrevSum) ? (100 - round(($allCostSum * 100 / $allCostPrevSum), 2)) : 0)
										->with('allCostSum', $allCostSum)
										->with('input', $input);
				}
			});
		})->store('xlsx');

		return Response::download('../app/storage/exports/' . $fileName . '.xlsx');
	}

}