<?php

namespace Garradin;

class Compta_Stats
{
	protected function _parRepartitionCategorie($type)
	{
		$db = DB::getInstance();
		return $db->simpleStatementFetch('SELECT COUNT(*) AS nb, id_categorie
			FROM compta_journal
			WHERE id_categorie IN (SELECT id FROM compta_categories WHERE type = ?)
			AND id_exercice = (SELECT id FROM compta_exercices WHERE cloture = 0)
			GROUP BY id_categorie ORDER BY nb DESC;', SQLITE3_ASSOC, $type);
	}

	public function repartitionRecettes()
	{
		return $this->_parRepartitionCategorie(Compta_Categories::RECETTES);
	}

	public function repartitionDepenses()
	{
		return $this->_parRepartitionCategorie(Compta_Categories::DEPENSES);
	}

	protected function _parType($type)
	{
		return $this->getStats('SELECT strftime(\'%Y%m\', date) AS date,
			SUM(montant) FROM compta_journal
			LEFT JOIN compta_flux ON compta_journal.id = compta_flux.id_journal
			WHERE id_categorie IN (SELECT id FROM compta_categories WHERE type = '.$type.')
			AND id_exercice = (SELECT id FROM compta_exercices WHERE cloture = 0)
			GROUP BY strftime(\'%Y-%m\', date) ORDER BY date;');
	}

	public function recettes()
	{
		return $this->_parType(Compta_Categories::RECETTES);
	}

	public function depenses()
	{
		return $this->_parType(Compta_Categories::DEPENSES);
	}

	public function soldeCompte($compte)
	{
		$db = DB::getInstance();

		if (strpos($compte, '%') !== false)
		{
			$compte = 'LIKE \''. $db->escapeString($compte) . '\'';
		}
		else
		{
			$compte = '= \''. $db->escapeString($compte) . '\'';
		}

		$stats = $this->getStats('SELECT strftime(\'%Y%m\', date) AS date,
			(COALESCE((SELECT SUM(montant) FROM compta_journal
				LEFT JOIN compta_flux ON compta_journal.id = compta_flux.id_journal
				WHERE compte '.$compte.' AND id_exercice = cj.id_exercice
				AND date >= strftime(\'%Y-%m-01\', cj.date)
				AND date <= strftime(\'%Y-%m-31\', cj.date)), 0)
			) AS solde
			FROM compta_journal AS cj
			WHERE (compte '.$compte.')
			AND id_exercice = (SELECT id FROM compta_exercices WHERE cloture = 0)
			GROUP BY strftime(\'%Y-%m\', date) ORDER BY date;');

		$c = 0;
		foreach ($stats as $k=>$v)
		{
			$c += $v;
			$stats[$k] = $c;
		}

		return $stats;
	}

	public function getStats($query)
	{
		$db = DB::getInstance();

		$data = $db->simpleStatementFetchAssoc($query);

		$e = $db->querySingle('SELECT *, strftime(\'%s\', debut) AS debut,
			strftime(\'%s\', fin) AS fin FROM compta_exercices WHERE cloture = 0;', true);

		$y = date('Y', $e['debut']);
		$m = date('m', $e['debut']);
		$max = date('Ym', $e['fin']);

		while ($y . $m <= $max)
		{
			if (!isset($data[$y . $m]))
			{
				$data[$y . $m] = 0;
			}

			if ($m == 12)
			{
				$m = '01';
				$y++;
			}
			else
			{
				$m++;
				$m = str_pad((int)$m, 2, '0', STR_PAD_LEFT);
			}
		}

		ksort($data);

		return $data;
	}
}

?>