<?php

class Project extends Eloquent {

	use SoftDeletingTrait;

	protected $guarded	 = array();
	public static $rules = array();

	const TYPE_SHOP	 = 1;
	const TYPE_HR		 = 2;

	public function sites() {
		return $this->hasMany('Site');
	}

	public function leads() {
		return $this->hasMany('Lead');
	}

	public function products() {
		return $this->hasMany('Product')->orderBy('name');
	}

	public function rejects() {
		return $this->hasMany('Reject');
	}

	public function infoSource() {
		return $this->hasMany('InfoSource');
	}

	public function partners() {
		return $this->hasMany('Partner');
	}

	public function leadsWithTrashed() {
		return $this->hasMany('Lead')->withTrashed();
	}

	public function budgetAccounts() {
		return $this->hasMany('BudgetAccount')->orderBy('name');
	}

	public function budgetCosts() {
		return $this->hasMany('BudgetCost')->orderBy('name');
	}

	public function budgetCostGroups() {
		return $this->belongsToMany('BudgetCostGroup', 'budget_cost_groups_project')->withPivot('amount');
	}

	public function budgetPartners() {
		return $this->hasMany('BudgetPartner')->orderBy('name');
	}

	public function budgetCostsRequests() {
		return $this->hasMany('BudgetCostsRequests');
	}
	
	public function budgetDebtPartners() {
		return $this->hasMany('BudgetDebtPartner')->with('debProject')->orderBy('name');
    }

	public function budgetUploads() {
		return $this->hasMany('BudgetUpload');
	}

	public function budgetOperations() {
		return $this->hasMany('BudgetOperation');
	}
	
  public function budgetMonthPlan() {
		return $this->hasMany('BudgetProjectMonthPlan');
	}
	
	public function visitCosts() {
		return $this->hasMany('VisitCost');
	}

	public function visitUtmSources() {
		return $this->hasMany('VisitUtmSource');
	}

	public function visitUtmCampaigns() {
		return $this->hasMany('VisitUtmCampaign');
	}

	public function users() {
		return $this->belongsToMany('User', 'users_projects');
	}
	
	public function salaryUsers() {
		return $this->belongsToMany('User', 'users_salary_projects');
	}

	public function owner() {
		return $this->belongsTo('User', 'user_id');
	}

	public function checkcalls() {
		return $this->hasMany('Checkcall');
	}

	public function checkquestions() {
		return $this->hasMany('Checkquestion');
	}

	public function fines() {
		return $this->belongsToMany('Fine', 'projects_fines', 'project_id', 'fine_id')->withPivot('value')->remember(60);
	}

	public function suppliers() {
		return $this->hasMany('Supplier');
	}

	public function isMy() {
		$count = Project::whereRaw('id IN (SELECT project_id FROM users_projects WHERE user_id=' . Auth::user()->id . ')')->count();
		if (!$count) {
			return false;
		}
		return true;
	}

	public function scopeMy($query) {
		if (Auth::user()->isAdmin() || Auth::user()->hasAnyRole(array(Role::ROLE_PROJECTS_EDITOR, Role::ROLE_PRODUCTS_EDITOR))) {
			$query->whereRaw('id IN (SELECT users_projects.project_id FROM users_projects INNER JOIN projects ON projects.id=users_projects.project_id AND projects.usecrm=1 WHERE users_projects.user_id=' . Auth::user()->id . ')');
		}
		else {
			$query->where('user_id', Auth::user()->id);
		}
	}

	public function getSum() {
		$operaionsInAmount	 = BudgetOperation::whereProjectId($this->id)
						->whereIn('type', array(BudgetOperation::TYPE_INCOMING, BudgetOperation::TYPE_EXCHANGE, BudgetOperation::TYPE_OPENING_BALANCE))
						->sum('amount');
		$operaionsOutAmount	 = BudgetOperation::whereProjectId($this->id)
						->whereIn('type', array(BudgetOperation::TYPE_OUTCOMING, BudgetOperation::TYPE_EXCHANGE))
						->sum('amount');
		$amount							 = $operaionsInAmount - $operaionsOutAmount;
		return round($amount / 100, 2);
	}

	public function getInSum($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsInAmount = BudgetOperation::whereProjectId($this->id)
						->whereIsnoincome(0)
						->whereIstransit(0)
						->whereType(BudgetOperation::TYPE_INCOMING)
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->sum('amount');
		return round($operaionsInAmount / 100, 2);
	}

	public function getOutSum($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereProjectId($this->id)
																					->whereIsnooutcome(0)
																					->whereIsdebt(0)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}

	public function getProfit() {
		$operaionsInAmount	 = BudgetOperation::whereProjectId($this->id)
						->whereIn('type', array(BudgetOperation::TYPE_INCOMING, BudgetOperation::TYPE_EXCHANGE, BudgetOperation::TYPE_OPENING_BALANCE))
						->sum('amount');
		$operaionsOutAmount	 = BudgetOperation::whereProjectId($this->id)
						->whereIn('type', array(BudgetOperation::TYPE_OUTCOMING, BudgetOperation::TYPE_EXCHANGE))
						->sum('amount');
		$amount							 = round(($operaionsInAmount - $operaionsOutAmount)/100, 2);
		return round($amount - $this->getPlanAmountRest(), 2);
	}

	public function getProfitAll() {
		return round($this->getProfit() + $this->getDebtSum(), 2);
	}

	public function getAllCost($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereProjectId($this->id)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getAllCostOutcome($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereProjectId($this->id)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereIsnooutcome(0)
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveAllCost($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::withTrashed()
						->whereProjectId($this->id)
						->whereIsarchived(1)
						->whereType(BudgetOperation::TYPE_OUTCOMING)
						->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->sum('amount');

		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveAllCostOutcome($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereProjectId($this->id)
																					->whereIsarchived(1)
																					->whereIsnooutcome(0)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');

		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getAllProjectsAllCost($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getAllProjectsAllCostOutcome($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->whereIsnooutcome(0)
																					->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveAllProjectsAllCost($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		$operaionsOutAmount = BudgetOperation::withTrashed()
						->whereType(BudgetOperation::TYPE_OUTCOMING)
						->whereIsarchived(1)
						->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->sum('amount');

		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveAllProjectsAllCostOutcome($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereIsarchived(1)
																					->whereIsnooutcome(0)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');

		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getAllMonthCost($date) {
		$dateArr = explode('-', $date);
			
    $month = $dateArr[1];
    $year  = $dateArr[0];
		
		$startDate = "{$year}-{$month}-1";
		$endDate = date('Y-m-d',mktime( 0, 0, 0, $month+1, 1,   $year ));
		
		$operaionsOutAmount = BudgetOperation::whereProjectId($this->id)
						                             ->whereType(BudgetOperation::TYPE_OUTCOMING)
						                             ->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))
						                             ->whereRaw(DB::raw('DATE(date)<?'), array($endDate))
						                             ->whereIsdebt(0)
						                             ->whereIsarchived(1)
						                             ->withTrashed()
						                             ->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}

	public function getDebtSum($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsInAmount	 = BudgetOperation::whereProjectId($this->id)
						->whereIsdebt(1)
						->whereType(BudgetOperation::TYPE_INCOMING)
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->sum('amount');
		$operaionsOutAmount	 = BudgetOperation::whereProjectId($this->id)
						->whereIsdebt(1)
						->whereType(BudgetOperation::TYPE_OUTCOMING)
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->sum('amount');
		$amount							 = $operaionsOutAmount - $operaionsInAmount;

		$operaionsInAmount	 = BudgetOperation::whereProjectId($this->id)
						->whereType(BudgetOperation::TYPE_OPENING_DEBT_IN)
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->sum('amount');
		$operaionsOutAmount	 = BudgetOperation::whereProjectId($this->id)
						->whereType(BudgetOperation::TYPE_OPENING_DEBT_OUT)
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->sum('amount');
		$amount							 = $amount + $operaionsOutAmount - $operaionsInAmount;

		return round($amount / 100, 2);
	}

	/*public function getPlanAmountAll() {
		$percent = round(BudgetCostGroupProject::whereProjectId($this->id)->sum('amount') / 100, 2);
		$planAmount = $percent * $this->month_plan / 100;
		return $planAmount;
	}*/

	public function getPlanAmountRest() {
		$rest = 0;
		foreach ($this->budgetCostGroups as $budgetCostGroup) {
			$sum = round($budgetCostGroup->pivot->amount / 100, 2) * $this->getCurrentBudgetMonthPlan() / 100;
			$sumRest = $sum - $budgetCostGroup->getGroupSum($this->id);
			if ($sumRest > 0) {
				$rest += $sumRest;
			}
		}
		return $rest;
	}
	
	public function getPlanAmountRestOutcome() {
		$rest = 0;
		foreach ($this->budgetCostGroups as $budgetCostGroup) {
			$sum = round($budgetCostGroup->pivot->amount / 100, 2) * $this->getCurrentBudgetMonthPlan() / 100;
			$sumRest = $sum - $budgetCostGroup->getGroupSumOutcome($this->id);
			if ($sumRest > 0) {
				$rest += $sumRest;
			}
		}
		return $rest;
	}

	public function getUnknownSum($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereProjectId($this->id)
						->whereType(BudgetOperation::TYPE_OUTCOMING)
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->whereBudgetCostId(0)
						->whereIsdebt(0)
						->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getUnknownSumOutcome($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereProjectId($this->id)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->whereBudgetCostId(0)
																					->whereIsnooutcome(0)
																					->whereIsdebt(0)
																					->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveUnknownSum($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereProjectId($this->id)
																					->whereIsarchived(1)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->whereBudgetCostId(0)
																					->whereIsdebt(0)
																					->sum('amount');

		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveUnknownSumOutcome($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereProjectId($this->id)
																					->whereIsarchived(1)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->whereBudgetCostId(0)
																					->whereIsnooutcome(0)
																					->whereIsdebt(0)
																					->sum('amount');

		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getTotalUnknownSum($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereType(BudgetOperation::TYPE_OUTCOMING)
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->whereBudgetCostId(0)
						->whereIsdebt(0)
						->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getTotalUnknownSumOutcome($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->whereBudgetCostId(0)
																					->whereIsnooutcome(0)
																					->whereIsdebt(0)
																					->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}

	public function getArchiveTotalUnknownSum($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereIsarchived(1)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->whereBudgetCostId(0)
																					->whereIsdebt(0)
																					->sum('amount');
		
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveTotalUnknownSumOutcome($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereIsarchived(1)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->whereBudgetCostId(0)
																					->whereIsnooutcome(0)
																					->whereIsdebt(0)
																					->sum('amount');
		
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getUnknownDebtSum($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereProjectId($this->id)
						->whereType(BudgetOperation::TYPE_OUTCOMING)
						->whereIsdebt(1)
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getUnknownDebtSumOutcome($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereProjectId($this->id)
						->whereType(BudgetOperation::TYPE_OUTCOMING)
						->whereIsdebt(1)
						->whereIsnooutcome(0)
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveUnknownDebtSum($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::withTrashed()
						->whereProjectId($this->id)
						->whereType(BudgetOperation::TYPE_OUTCOMING)
						->whereIsarchived(1)
						->whereIsdebt(1)
						->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->sum('amount');
		
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveUnknownDebtSumOutcome($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::withTrashed()
						->whereProjectId($this->id)
						->whereType(BudgetOperation::TYPE_OUTCOMING)
						->whereIsarchived(1)
						->whereIsdebt(1)
						->whereIsnooutcome(0)
						->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                            
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->sum('amount');
		
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getTotalUnknownDebtSum($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereIsdebt(1)
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getTotalUnknownDebtSumOutcome($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereIsdebt(1)
																					->whereIsnooutcome(0)
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveTotalUnknownDebtSum($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		$operaionsOutAmount = BudgetOperation::withTrashed()
						->whereType(BudgetOperation::TYPE_OUTCOMING)
						->whereIsdebt(1)
						->whereIsarchived(1)
						->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->sum('amount');
		
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveTotalUnknownDebtSumOutcome($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereIsdebt(1)
																					->whereIsnooutcome(0)
																					->whereIsarchived(1)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');

		return round($operaionsOutAmount / 100, 2);
	}

	public function getUnknownGroupSum($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereProjectId($this->id)
						->whereIsdebt(0)
						->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id=0)')
						->whereType(BudgetOperation::TYPE_OUTCOMING)
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getUnknownGroupSumOutcome($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereProjectId($this->id)
																					->whereIsnooutcome(0)
																					->whereIsdebt(0)
																					->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id=0)')
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveUnknownGroupSum($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereProjectId($this->id)
																					->whereIsdebt(0)
						                              ->whereIsarchived(1)
																					->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id=0)')
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																				  ->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');
		
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveUnknownGroupSumOutcome($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereProjectId($this->id)
																					->whereIsnooutcome(0)
																					->whereIsdebt(0)
						                              ->whereIsarchived(1)
																					->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id=0)')
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																				  ->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');
		
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getTotalUnknownGroupSum($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereIsdebt(0)
						->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id=0)')
						->whereType(BudgetOperation::TYPE_OUTCOMING)
						->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getTotalUnknownGroupSumOutcome($endDate = null) {
		if (!$endDate) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::whereIsnooutcome(0)
																					->whereIsdebt(0)
																					->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id=0)')
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveTotalUnknownGroupSum($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		$operaionsOutAmount = BudgetOperation::withTrashed()
					                              	->whereIsdebt(0)
						                              ->whereIsarchived(1)
																					->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id=0)')
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																				  ->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveTotalUnknownGroupSumOutcome($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		$operaionsOutAmount = BudgetOperation::withTrashed()
					                              	->whereIsnooutcome(0)
																					->whereIsdebt(0)
						                              ->whereIsarchived(1)
																					->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id=0)')
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																				  ->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
  public function getMonthName($date = null){
		if (!$date){
		  $budgetMonthPlan = $this->budgetMonthPlan()->whereIsarchived(0)->first();
		  if ($budgetMonthPlan){
		    $date = $budgetMonthPlan->budget_date;
		  } else{
			  $date = date('Y-m-d');
		  }
		}
	  $month_arr = array( '01' => 'Январь', '02' => 'Февраль', '03' => 'Март', '04' => 'Апрель', '05' => 'Май', '06' => 'Июнь', '07' => 'Июль', '08' => 'Август', '09' => 'Сентябрь', '10' => 'Октябрь', '11' => 'Ноябрь', '12' => 'Декабрь' );
		$dateArr = explode('-', $date);
			
    $month = $month_arr[ $dateArr[1] ];
    $year  = $dateArr[0];

    $monthName = $month.', '.$year;
		
		return $monthName;
  }

	public function getCurrentBudgetMonthPlan() {
		$budgetMonthPlan = $this->budgetMonthPlan()->whereIsarchived(0)->first();
		if ($budgetMonthPlan){
		  $monthPlan = $budgetMonthPlan->month_plan/100;
		} else{
			$monthPlan = 0;
		}
		return $monthPlan;
	}
	
	// вверху панель кнопок с проектами - кол-во заявок по проекту которые нужно обработать для разных ролей
	 public function getCountRequestProject() {
		$requestsForSign  = 0; // подписать заявок
		$requestsSigned   = 0; // финдиректору - одобрить подписанных
		$requestsApproved = 0; // бух. - оплатить одобренных
		
		$requestsBackOff = BudgetCostsRequests::whereProjectId($this->id)   // возврат от директора
																					->whereApplicantId(Auth::user()->id)
																					->whereStatus(BudgetCostsRequests::STATUS_BACK_OFF)
																					->orderBy('created_at', 'DESC')
																					->count();
		
		if (Auth::user()->hasRole(Role::ROLE_BUDGET_SIGN_COST_REQUEST) && !Auth::user()->isAdmin() ) { 
			$requestsForSign = BudgetCostsRequests::whereProjectId($this->id)
							                              ->whereDirectorId(Auth::user()->id)
																						->whereStatus(BudgetCostsRequests::STATUS_WORK)
																						->orderBy('created_at', 'DESC')
																						->count();
		}
		
		if (Auth::user()->hasRole(Role::ROLE_BUDGET_CFO) && !Auth::user()->isAdmin() ) {
		  $requestsSigned = BudgetCostsRequests::whereProjectId($this->id)
							                             ->whereStatus(BudgetCostsRequests::STATUS_SIGNED)
																					 ->orderBy('created_at', 'DESC')
																					 ->count();
		}
		else if ( Auth::user()->hasAnyRole(array(Role::ROLE_BUDGET_ACCOUNTANT, Role::ROLE_BUDGET_ACC_SECRETARY)) && !Auth::user()->isAdmin() ) {
			$requestsApproved = BudgetCostsRequests::whereProjectId($this->id)
							                               ->whereStatus(BudgetCostsRequests::STATUS_APPROVED)
																						 ->orderBy('created_at', 'DESC')
																						 ->count();
		}
		$result = $requestsBackOff + $requestsForSign + $requestsSigned + $requestsApproved;
						
		return $result;
	}
	
	//----------------------------------- Отчет - расходы проектов по месяцам ----------------------------------------------------------------//
	
		public function getProjectArchiveAllCostOutcome($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereProjectId($this->id)
																					->whereIsarchived(1)
																					->whereIsnooutcome(0)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						                              ->select(DB::raw('sum(budget_operations.amount)/100 as sum, MONTH(budget_operations.date) as month, YEAR(budget_operations.date) as year, budget_operations.date'))
						                              ->groupBy(DB::raw('YEAR(budget_operations.date)'))
																					->groupBy(DB::raw('MONTH(budget_operations.date)'))
						                              ->orderBy('budget_operations.date', 'DESC')
																					->get();

		$operaions = array();
		foreach($operaionsOutAmount as $item){
			$id = $item['month'].'-'.$item['year'];
			$operaions[$id] = $item;
		}
		
		return $operaions;
// Возвращает:
/* [ 
  "10-2014" : {
         "sum" :"1500" ,
	       "month": 10, 
	       "year" :2014, 
	       "date" :"2014-10-01 "
         }, 
  "12-2014" : {
         "sum" :"27000" ,
	       "month": 12, 
	       "year" :2014, 
	       "date" :"2014-12-01 "
         }
    ] */
	}
	
	public function getProjectArchiveAllCost($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereProjectId($this->id)
																					->whereIsarchived(1)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->select(DB::raw('sum(budget_operations.amount)/100 as sum, MONTH(budget_operations.date) as month, YEAR(budget_operations.date) as year, budget_operations.date'))
						                              ->groupBy(DB::raw('YEAR(budget_operations.date)'))
																					->groupBy(DB::raw('MONTH(budget_operations.date)'))
						                              ->orderBy('budget_operations.date', 'DESC')
																					->get();

		$operaions = array();
		foreach($operaionsOutAmount as $item){
			$id = $item['month'].'-'.$item['year'];
			$operaions[$id] = $item;
		}
		
		return $operaions;
	}

	public function getProjectArchiveUnknownGroupSum($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereProjectId($this->id)
																					->whereIsdebt(0)
						                              ->whereIsarchived(1)
																					->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id=0)')
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																				  ->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->select(DB::raw('sum(budget_operations.amount)/100 as sum, MONTH(budget_operations.date) as month, YEAR(budget_operations.date) as year, budget_operations.date'))
						                              ->groupBy(DB::raw('YEAR(budget_operations.date)'))
																					->groupBy(DB::raw('MONTH(budget_operations.date)'))
						                              ->orderBy('budget_operations.date', 'DESC')
																					->get();

		$operaions = array();
		foreach($operaionsOutAmount as $item){
			$id = $item['month'].'-'.$item['year'];
			$operaions[$id] = $item;
		}
		
		return $operaions;
	}
	
	public function getProjectArchiveUnknownGroupSumOutcome($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereProjectId($this->id)
																					->whereIsnooutcome(0)
																					->whereIsdebt(0)
						                              ->whereIsarchived(1)
																					->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id=0)')
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																				  ->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->select(DB::raw('sum(budget_operations.amount)/100 as sum, MONTH(budget_operations.date) as month, YEAR(budget_operations.date) as year, budget_operations.date'))
						                              ->groupBy(DB::raw('YEAR(budget_operations.date)'))
																					->groupBy(DB::raw('MONTH(budget_operations.date)'))
						                              ->orderBy('budget_operations.date', 'DESC')
																					->get();

		$operaions = array();
		foreach($operaionsOutAmount as $item){
			$id = $item['month'].'-'.$item['year'];
			$operaions[$id] = $item;
		}
		
		return $operaions;
	}

	public function getProjectArchiveUnknownDebtSum($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereProjectId($this->id)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereIsarchived(1)
																					->whereIsdebt(1)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->select(DB::raw('sum(budget_operations.amount)/100 as sum, MONTH(budget_operations.date) as month, YEAR(budget_operations.date) as year, budget_operations.date'))
						                              ->groupBy(DB::raw('YEAR(budget_operations.date)'))
																					->groupBy(DB::raw('MONTH(budget_operations.date)'))
						                              ->orderBy('budget_operations.date', 'DESC')
																					->get();

		$operaions = array();
		foreach($operaionsOutAmount as $item){
			$id = $item['month'].'-'.$item['year'];
			$operaions[$id] = $item;
		}
		
		return $operaions;
	}
	
	public function getProjectArchiveUnknownDebtSumOutcome($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereProjectId($this->id)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereIsarchived(1)
																					->whereIsdebt(1)
																					->whereIsnooutcome(0)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                            
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->select(DB::raw('sum(budget_operations.amount)/100 as sum, MONTH(budget_operations.date) as month, YEAR(budget_operations.date) as year, budget_operations.date'))
						                              ->groupBy(DB::raw('YEAR(budget_operations.date)'))
																					->groupBy(DB::raw('MONTH(budget_operations.date)'))
						                              ->orderBy('budget_operations.date', 'DESC')
																					->get();

		$operaions = array();
		foreach($operaionsOutAmount as $item){
			$id = $item['month'].'-'.$item['year'];
			$operaions[$id] = $item;
		}
		
		return $operaions;
	}
	
	public function getProjectArchiveUnknownSum($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereProjectId($this->id)
																					->whereIsarchived(1)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->whereBudgetCostId(0)
																					->whereIsdebt(0)
																					->select(DB::raw('sum(budget_operations.amount)/100 as sum, MONTH(budget_operations.date) as month, YEAR(budget_operations.date) as year, budget_operations.date'))
						                              ->groupBy(DB::raw('YEAR(budget_operations.date)'))
																					->groupBy(DB::raw('MONTH(budget_operations.date)'))
						                              ->orderBy('budget_operations.date', 'DESC')
																					->get();

		$operaions = array();
		foreach($operaionsOutAmount as $item){
			$id = $item['month'].'-'.$item['year'];
			$operaions[$id] = $item;
		}
		
		return $operaions;
	}
	
	public function getProjectArchiveUnknownSumOutcome($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("Y-m-d", time() - 3600 * 24);
		}
		else {
			$date		 = explode('.', $startDate);
			$startDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		
		if (!isset($endDate)) {
			$endDate = date('Y-m-d');
		}
		else {
			$date		 = explode('.', $endDate);
			$endDate = "{$date[2]}-{$date[1]}-{$date[0]}";
		}
		$operaionsOutAmount = BudgetOperation::withTrashed()
																					->whereProjectId($this->id)
																					->whereIsarchived(1)
																					->whereType(BudgetOperation::TYPE_OUTCOMING)
																					->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																					->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
																					->whereBudgetCostId(0)
																					->whereIsnooutcome(0)
																					->whereIsdebt(0)
																					->select(DB::raw('sum(budget_operations.amount)/100 as sum, MONTH(budget_operations.date) as month, YEAR(budget_operations.date) as year, budget_operations.date'))
						                              ->groupBy(DB::raw('YEAR(budget_operations.date)'))
																					->groupBy(DB::raw('MONTH(budget_operations.date)'))
						                              ->orderBy('budget_operations.date', 'DESC')
																					->get();

		$operaions = array();
		foreach($operaionsOutAmount as $item){
			$id = $item['month'].'-'.$item['year'];
			$operaions[$id] = $item;
		}
		
		return $operaions;
	}
	
	public function getSalaryUsers() {
		$users = $this->salaryUsers()->orderBy('last_name')->get();
		$usersList = [];
		
		foreach ($users as $user){
			$usersList[$user->id] = $user->last_name.' '.$user->first_name;
		}
		
		return $usersList;
	}
	
	public function getAllowSalaryUsers() {

		$role = Role::find(Role::ROLE_BUDGET_SALARY_USER);
		$users = $role->users()->orderBy('last_name', 'ASC')->get();
		
		return $users;
	}
    
    public function countAllowSalaryUsers() {

		$role = Role::find(Role::ROLE_BUDGET_SALARY_USER);
		$count = $role->users()->orderBy('last_name', 'ASC')->count('last_name');
		
		return $count;
	}

	public function salaryCosts() {
		$salaryCosts = BudgetCost::select(DB::raw('id'))
														  ->whereBudgetCostGroupId(6)
														  ->remember(60)
														  ->get();
		$costsList = [];

		foreach ($salaryCosts as $salaryCost){
			$costsList[] = $salaryCost->id;
		}

		return $costsList;
	}
	
	// итоговая сумма з/п по месяцам по одному проекту или всем, если нет $isCurrentProject
	public function getAllBudgetSalaryByMonth($monthRange = null, $isCurrentProject = null) {
		if(!$monthRange){
			$monthRange = 1;
		}
		
		$startDate = date("Y-m-01");
		
        $whereProject = '';
        if($isCurrentProject){
          $whereProject = 'budget_salary.project_id = '.$this->id.' AND ';
        }
		$budgetSalaryAmount = BudgetSalary::whereRaw($whereProject.'budget_salary.salary_date >='. DB::raw('DATE("'.$startDate.'") - INTERVAL '.$monthRange.' MONTH'))
						                              ->select(DB::raw('sum(budget_salary.amount)/100 as sum, MONTH(budget_salary.salary_date) as month, YEAR(budget_salary.salary_date) as year, budget_salary.salary_date as date'))
						                              ->groupBy(DB::raw('YEAR(budget_salary.salary_date)'))
																					->groupBy(DB::raw('MONTH(budget_salary.salary_date)'))
						                              ->orderBy('budget_salary.salary_date', 'DESC')
																					->get();

		$operaions = array();
		foreach($budgetSalaryAmount as $item){
			$id = $item['month'].'-'.$item['year'];
			$operaions[$id] = $item;
		}
		
		return $operaions;
// Возвращает:
/* [ 
  "10-2014" : {
         "sum" :"1500" ,
	       "month": 10, 
	       "year" :2014, 
	       "date" :"2014-10-01 "
         }, 
  "12-2014" : {
         "sum" :"27000" ,
	       "month": 12, 
	       "year" :2014, 
	       "date" :"2014-12-01 "
         }
    ] */
	}
	
	// суммы з/п сотрудников по месяцам по одному проекту или всем, если нет $isCurrentProject
	public function getAllUsersBudgetSalaryByMonth($monthRange = null, $isCurrentProject = null) {
		if(!$monthRange){
			$monthRange = 1;
		}
		$startDate = date("Y-m-01");

        $whereProject = '';
        if($isCurrentProject){
          $whereProject = 'budget_salary.project_id = '.$this->id.' AND ';
        }
		$budgetSalaryAmount = BudgetSalary::whereRaw($whereProject.'budget_salary.salary_date >= '. DB::raw('DATE("'.$startDate.'") - INTERVAL '.$monthRange.' MONTH'))
                                          ->select(DB::raw('sum(budget_salary.amount)/100 as sum,
                                                                    MONTH(budget_salary.salary_date) as month, 
                                                                              YEAR(budget_salary.salary_date) as year, 
                                                                              budget_salary.salary_date as date, 
                                                                              budget_salary.salary_user_id as user_id'))
                                          ->groupBy('budget_salary.salary_user_id')
                                          ->groupBy(DB::raw('YEAR(budget_salary.salary_date)'))
                                          ->groupBy(DB::raw('MONTH(budget_salary.salary_date)'))
                                          ->get();

		$allOperaions = array();
		foreach($budgetSalaryAmount as $key => $item){
			$userId = $item['user_id'];
			
			$id = $item['month'].'-'.$item['year'];
			$allOperaions[$userId][$id] = $item;
		}
/* json вид
		 {"message":{"111":{
		                   "4-2015":{"sum":"27500.0000","month":4,"year":2015,"date":"2015-04-07","salary_user_id":111}
		                  },
		            "113":{
		                   "3-2015":{"sum":"10000.0000","month":3,"year":2015,"date":"2015-03-18","salary_user_id":113},
		                   "4-2015":{"sum":"20001.0000","month":4,"year":2015,"date":"2015-04-10","salary_user_id":113},
		                   "5-2015":{"sum":"6500.0000","month":5,"year":2015,"date":"2015-05-02","salary_user_id":113}
		                  }
		            }
		 }
*/
		return $allOperaions;
	}

     /**
	 * Итоговая сумма вып. планов по месяцам по одному проекту
	 *
	 * @return array
     * @Example: [ 
     * @Example:  "10-2014" : {
     * @Example:         "sum" :"1500" ,
     * @Example:           "month": 10, 
     * @Example:           "year" :2014, 
     * @Example:           "date" :"2014-10-01 " } ]
	 */
	public function getAllExecPlanByMonth($monthRange = null) {
		if(!$monthRange){
			$monthRange = 1;
		}
		
		$startDate = date("Y-m-01");

		$totalExecPlan = UserSalesPlan::whereProjectId($this->id)
						                              ->where('users_sales_plan.plan_date', '>=', DB::raw('DATE("'.$startDate.'") - INTERVAL '.$monthRange.' MONTH'))
						                              ->select(DB::raw('sum(users_sales_plan.exec_amount)/100 as sum,
																									          MONTH(users_sales_plan.plan_date) as month, 
																														YEAR(users_sales_plan.plan_date) as year'))
						                              ->groupBy(DB::raw('YEAR(users_sales_plan.plan_date)'))
																					->groupBy(DB::raw('MONTH(users_sales_plan.plan_date)'))
																					->get();

		$operaions = array();
		foreach($totalExecPlan as $item){
			$id = $item['month'].'-'.$item['year'];
			$operaions[$id] = $item;
		}
		
		return $operaions;
	}
	
		// сумма вып. планов продаж за весь выбранный период
	public function getTotalExecPlan($monthRange = null) {
		if(!$monthRange){
			$monthRange = 1;
		}
		$startDate = date("Y-m-01");
		$sum = UserSalesPlan::whereProjectId($this->id)
												->where('plan_date', '>=', DB::raw('DATE("'.$startDate.'") - INTERVAL '.$monthRange.' MONTH'))
												->sum('exec_amount');
		return $sum;
	}
	
		// сумма вып. планов продаж одного сотрудника за весь выбранный период
	public function getUserAllExecPlan($monthRange = null) {
		if(!$monthRange){
			$monthRange = 1;
		}
		$startDate = date("Y-m-01");

		$userAllExecPlan = UsersSalaryProject::where('users_salary_projects.project_id', '=', $this->id)
																						->withTrashed()
																						->where('uspl.plan_date', '>=', DB::raw('DATE("'.$startDate.'") - INTERVAL '.$monthRange.' MONTH'))                   
																						->whereNull('uspl.deleted_at')
																						->select(DB::raw('sum(uspl.exec_amount) as sum, 
																															uspl.salary_user_id as user_id'))
																						->leftJoin('users_sales_plan AS uspl', 'uspl.salary_user_id', '=', 'users_salary_projects.user_id')
																						->groupBy('uspl.salary_user_id')
																						->get();

		$allOperaions = array();
		foreach($userAllExecPlan as $key => $item){
			$userId = $item['user_id'];
			$allOperaions[$userId] = $item['sum'];
		}
/* json вид
		 {"message":{"111":"12000000"}
		                  },
		            "112":"13000000"}
		                  }
		            }
		 }
*/
		return $allOperaions;
	}
	
    /**
	 * Выполненные планы продаж сотрудников по месяцам по одному проекту
	 *
	 * @return array
     * @Example: {"111":{
     * @Example:           "4-2015":{"execPlan":"12000000","month":4,"year":2015,"user_id":111}
     * @Example:          },
     * @Example:    "113":{
     * @Example:           "3-2015":{"execPlan":"14000000","month":3,"year":2015,"user_id":113},
     * @Example:           "4-2015":{"execPlan":"14100000","month":4,"year":2015,"user_id":113},
     * @Example:           "5-2015":{"execPlan":"14200000","month":5,"year":2015,"user_id":113}
     * @Example:          }
     * @Example:    }
	 */
	public function getAllUsersExecPlanByMonth($monthRange = null) {
		if(!$monthRange){
			$monthRange = 1;
		}
		$startDate = date("Y-m-01");

		$budgetSalaryAmount = UsersSalaryProject::where('users_salary_projects.project_id', '=', $this->id)
																						->withTrashed()
																						->where('uspl.plan_date', '>=', DB::raw('DATE("'.$startDate.'") - INTERVAL '.$monthRange.' MONTH'))                   
																						->whereNull('uspl.deleted_at')
																						->select(DB::raw('uspl.exec_amount as execPlan, 
																															MONTH(uspl.plan_date) as month, 
																															YEAR(uspl.plan_date) as year, 
																															uspl.salary_user_id as user_id'))
																						->leftJoin('users_sales_plan AS uspl', 'uspl.salary_user_id', '=', 'users_salary_projects.user_id')
																						->groupBy('uspl.salary_user_id')
																						->groupBy(DB::raw('YEAR(uspl.plan_date)'))
																						->groupBy(DB::raw('MONTH(uspl.plan_date)'))
																						->get();

		$allOperaions = array();
		foreach($budgetSalaryAmount as $key => $item){
			$userId = $item['user_id'];
			
			$id = $item['month'].'-'.$item['year'];
			$allOperaions[$userId][$id] = $item;
		}

		return $allOperaions;
	}
	
    /**
	 * Массив месяц+год (месяц без первого нуля)
	 *
	 * @return array
     * @Example: ['6-2015' : 'Июнь, 2015']
	 */
	public function getMonthsList($monthRange = null) {
		if(!$monthRange){
			$monthRange = 1;
		}
		$month_arr = array( '01' => 'Январь', '02' => 'Февраль', '03' => 'Март', '04' => 'Апрель', '05' => 'Май', '06' => 'Июнь', '07' => 'Июль', '08' => 'Август', '09' => 'Сентябрь', '10' => 'Октябрь', '11' => 'Ноябрь', '12' => 'Декабрь' );

		$startDate = date("Y-m-01");
		$date = new DateTime($startDate);
		$interval = new DateInterval('P1M');

		$months = [];
		// след. месяц
		$date->add($interval);
		$months[$date->format('n-Y')] = $month_arr[ $date->format('m') ].', '.$date->format('Y');
		// текущий
		$date->sub($interval);
		$months[$date->format('n-Y')] = $month_arr[ $date->format('m') ].', '.$date->format('Y');
    // предыдущие
		if($monthRange > 0){
			for ($i = 1; $i <= $monthRange; $i++) {
				$date->sub($interval);
				$months[$date->format('n-Y')] = $month_arr[ $date->format('m') ].', '.$date->format('Y');
			}
		}
		return $months;
	}
    
    /**
	 * Массив всех проектов.
	 *
	 * @return array
     * @View: [ 'id' : 'name' ]
	 */
    public static function getProjectsNameList() {
      $projects = Project::all();
      
      $projectsNameList = [];
      foreach ($projects as $project){
        $projectsNameList[$project->id] = $project->name;
      }
      
      return $projectsNameList;
    }
}