<?php
class Category
{
	function getEndDay($month, $year)
	{
		if(($month < 8 && $month % 2 != 0) || ($month >= 8 && $month % 2 == 0))
			$endday = 31;
		else
			$endday = 30;
		if($month == 2)
			if($year % 4 == 0 && ($year % 100 != 0 || $year % 400 == 0))
				$endday = 29;
			else
				$endday = 28;
		return $endday;
	}

	function getBudgetList($month, $year)
	{
		$endday = Category::getEndDay($month, $year);
		$prevmonth = $month-1;
		$prevyear = $year;
		if($prevmonth == 0)
		{
			$prevyear = $year - 1;
			$prevmonth = 12;
		}
		//figure out what last month was for each category, and what was this month
		//we're going to copy categories we don't know about to this month.
		//if they were deleted, then we copy them as deleted.
		$sql = "
					select
						c.id,
						cp.id as cp_id,
						c.amount,
						coalesce(cp2.deleted, c.deleted) as deleted,
						cp2.amount as prevamount
					from
						category c
						left outer join category_period cp
						on
							c.id = cp.category_id
							and cp.period = '$year-$month'
						left outer join category_period cp2
						on
							c.id = cp2.category_id
							and cp2.period = '$prevyear-$prevmonth'
					where
						cp.id is null";
		$cats = sql_fetch_map($sql, "id");

		//if there isn't something set, set it to last month
		foreach($cats as $id => $data)
		{
			if(!empty($data['prevamount']))
				$amount = $data['prevamount'];
			else
				$amount = $data['amount'];
			if(!empty($data['deleted']))
				$deleted = $data['deleted'];
			else
				$deleted = 0;
			sql_query("insert into category_period(category_id, period, amount, deleted) values({$data['id']}, '$year-$month', $amount, $deleted)");
		}
		$beginOfMonth = makeDate($year, $month, 1);
		$endOfMonth = makeDate($year, $month, $endday);
		$cats = sql_fetch_map("
					select 
						c.id, 
						c.name, 
						sum(e.amount / e.span_months) as total, 
						cp.amount, 
						c.color, 
						cp.amount - sum(e.amount / e.span_months) as "left",
						sum(e.amount / e.span_months) as spent
					from
						category c
						inner join category_period cp
						on
							c.id = cp.category_id
							and cp.period = '$year-$month'
						left outer join expense e
						on
							e.category_id = c.id
							and e.date + (e.span_months - 1 || ' months')::interval >= '$beginOfMonth' 
							and e.date <= '$endOfMonth'
							and e.deleted = 0
					where cp.deleted = 0
					group by
						c.id, c.name, cp.amount, c.color
					order by
						amount desc", 'id');
		return $cats;
	}

	function getCategoryOptions($month, $year)
	{
		$cats = sql_fetch_simple_map("
					select
						c.id,
						c.name
					from
						category c
						inner join category_period cp
						on
							c.id = cp.category_id
							and cp.period = '$year-$month'
					where cp.deleted = 0
					order by
						name", 'id', 'name');
		return $cats;
	}

	function getUnusedColors()
	{
		global $strings;
		$answer = array();
		$colors = sql_fetch_simple_map("select distinct color from category where deleted = 0", 'color', 'color');
		foreach($strings['colors'] as $color => $v)
		{
			if(!isset($colors[$color]))
			{
				$answer[$color] = $color;
			}
		}
		return $answer;
	}

	function getBlankInfo()
	{
		$colors = Category::getUnusedColors();
		$color = reset($colors);
		return array('name' => '', 'amount' => '', 'color' => $color, 'description' => '', 'comments' => '');
	}

	function &insert($info, $month, $year)
	{
		$info['name'] = ticks($info['name']);
		$info['description'] = ticks($info['description']);
		$info['comments'] = ticks($info['comments']);
		$info['color'] = ticks($info['color']);
		$catId = sql_insert("insert into category (name, amount, color, description, comments) values({$info['name']}, {$info['amount']}, {$info['color']}, {$info['description']}, {$info['comments']})", "category_id_seq");
		sql_query("insert into category_period(category_id, period, amount) values($catId, '$year-$month', {$info['amount']})");
		$answer = &new Category($catId);
		return $answer;
	}

	function delete($id, $month, $year)
	{
		//delete means don't use it for new months.
		sql_query("update category_period set deleted = 1 where category_id = $id and period = '$year-$month'");
		sql_query("update category_period set deleted = 1 where category_id = $id");
	}

	function Category($catId)
	{
		$this->id = $catId;
	}

	function getInfo($month, $year)
	{
		$answer = sql_fetch_one("
				select
					c.id, cp.id as period_id, c.name, coalesce(cp.amount, c.amount) as amount, c.color, c.description, c.comments
				from
					category c
					left outer join category_period cp
					on
						c.id = cp.category_id
						and cp.period = '$year-$month'
				where
					c.id = {$this->id}");
		if(empty($answer['period_id']))
		{
			$amount = $answer['amount'];
			$answer['period_id'] = sql_insert("insert into category_period (category_id, period, amount) values({$this->id}, '$year-$month', {$amount})", 'category_period_id_seq');
		}
		return $answer;
	}

	function setInfo($info, $month, $year)
	{
		$info['name'] = ticks($info['name']);
		$info['description'] = ticks($info['description']);
		$info['comments'] = ticks($info['comments']);
		$info['color'] = ticks($info['color']);
		sql_query("update category set
						name = {$info['name']}, amount = {$info['amount']}, color = {$info['color']}, description = {$info['description']},
						comments = {$info['comments']}
					where id = {$this->id}");
		$catPeriodId = sql_fetch_one_cell("select id from category_period where category_id = {$this->id} and period = '$year-$month'");
		if($catPeriodId)
		{
			sql_query("update category_period set
							amount = {$info['amount']} where id = $catPeriodId");
		}
		else
		{
			sql_query("insert into category_period (category_id, period, amount) values({$this->id}, '$year-$month', {$info['amount']})");
		}
	}
}

?>