<?php

class BudgetCostGroup extends \Eloquent {

	use SoftDeletingTrait;

	protected $guarded	 = array();
	public static $rules = array();
	
	public function budgetCosts()
  {
    return $this->hasMany('BudgetCost', 'budget_cost_group_id');
  }
	
	public function project() {
		return $this->belongsToMany('Project', 'budget_cost_groups_project');
	}
	
  public function setPlanAmountAttribute($value) {
		$value													 = str_replace(',', '.', $value);
		$value													 = str_replace(' ', '', $value);
		$this->attributes['plan_amount'] = round($value * 100, 0);
	}

	public function getPlanAmountAttribute() {
		return round($this->attributes['plan_amount'] / 100, 2);
	}

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

		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveGroupSum($projectId, $startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("d.m.Y", 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($projectId)
						                             ->whereIsdebt(0)
						                             ->whereIsarchived(1)
						                             ->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id='.$this->id.')')
						                             ->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																				 ->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						                             ->whereType(BudgetOperation::TYPE_OUTCOMING)
						                             ->sum('amount');

		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveGroupSumOutcome($projectId, $startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("d.m.Y", 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($projectId)
						                             ->whereIsnooutcome(0)
						                             ->whereIsdebt(0)
						                             ->whereIsarchived(1)
						                             ->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id='.$this->id.')')
						                             ->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																				 ->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						                             ->whereType(BudgetOperation::TYPE_OUTCOMING)
						                             ->sum('amount');

		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getTotalGroupSum($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='.$this->id.')')
						                             ->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						                             ->whereType(BudgetOperation::TYPE_OUTCOMING)
						                             ->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getTotalGroupSumOutcome($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)
						                             ->whereIsnooutcome(0)
																				 ->whereIsdebt(0)
						                             ->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id='.$this->id.')')
						                             ->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						                             ->sum('amount');
		
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveTotalGroupSum($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("d.m.Y", 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='.$this->id.')')
																				 ->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																				 ->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						                             ->whereType(BudgetOperation::TYPE_OUTCOMING)
						                             ->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
	public function getArchiveTotalGroupSumOutcome($startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("d.m.Y", 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='.$this->id.')')
																				 ->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																				 ->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						                             ->whereType(BudgetOperation::TYPE_OUTCOMING)
						                             ->sum('amount');
		return round($operaionsOutAmount / 100, 2);
	}
	
		public function getGroupPercent($projectId) {
		  $percent = BudgetCostGroupProject::whereProjectId($projectId)
						                           ->whereBudgetCostGroupId($this->id)
						                           ->first();
		return $percent['attributes'];
	}
	
//---------------------------------------------------------------------------------------------------------//
	
	public function getProjectArchiveGroupSum($projectId, $startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("d.m.Y", 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($projectId)
						                             ->whereIsdebt(0)
						                             ->whereIsarchived(1)
						                             ->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id='.$this->id.')')
						                             ->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																				 ->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						                             ->whereType(BudgetOperation::TYPE_OUTCOMING)
						                             ->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 getProjectArchiveGroupSumOutcome($projectId, $startDate = null, $endDate = null) {
		if (!isset($startDate)) {
			$startDate = date("d.m.Y", 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($projectId)
						                             ->whereIsnooutcome(0)
						                             ->whereIsdebt(0)
						                             ->whereIsarchived(1)
						                             ->whereRaw('budget_cost_id IN (SELECT id FROM budget_costs WHERE budget_cost_group_id='.$this->id.')')
						                             ->whereRaw(DB::raw('DATE(date)>=?'), array($startDate))                             
																				 ->whereRaw(DB::raw('DATE(date)<=?'), array($endDate))
						                             ->whereType(BudgetOperation::TYPE_OUTCOMING)
						                             ->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;
	}
	
}