<?php

/**
 * Subclass for performing query and update operations on the 'receipt' table.
 *
 * 
 *
 * @package lib.model
 */ 
class ReceiptPeer extends BaseReceiptPeer
{
	public static function sumReceipts($contractid, $invoiceid = null)
	{
		$c = new Criteria();
		$c->clearSelectColumns()->addSelectColumn('SUM('.self::AMOUNT.')');
		$c->add(self::CONTRACT_ID,$contractid);
		if ($invoiceid) $c->add(self::INVOICE_ID,$invoiceid,Criteria::NOT_EQUAL);
		$c->add(self::IS_CANCELED,0);
		$rs = self::doSelectRS($c);
		$rs->next();
		return round($rs->getFloat(1),2);
	}
	
	public static function sumReceiptsPerInvoice($invoiceid)
	{
		$c = new Criteria();
		$c->clearSelectColumns()->addSelectColumn('SUM('.self::AMOUNT.')');
		$c->add(self::INVOICE_ID,$invoiceid);
		$c->add(self::IS_CANCELED,0);
		$rs = self::doSelectRS($c);
		$rs->next();
		return round($rs->getFloat(1),2);
	}
	
	public static function sumReceiptsPaidInvoices($contractid)
	{
		$c = new Criteria();
		$c->clearSelectColumns()->addSelectColumn('SUM('.self::AMOUNT.')');
		$c->addJoin(self::INVOICE_ID,InvoicePeer::ID);
		$c->add(self::CONTRACT_ID,$contractid);
		$c->add(self::IS_CANCELED,0);
		$c->add(InvoicePeer::IS_CANCELED,0);
		$c->add(InvoicePeer::IS_FULLYPAID,1);
		$rs = self::doSelectRS($c);
		$rs->next();
		return round($rs->getFloat(1),2);
		
	}
	
	public static function sumReceiptsUnpaidInvoices($contractid, $invoiceid = null)
	{
		$c = new Criteria();
		$c->clearSelectColumns()->addSelectColumn('SUM('.self::AMOUNT.')');
		$c->addJoin(self::INVOICE_ID,InvoicePeer::ID);
		$c->add(self::CONTRACT_ID,$contractid);
		if ($invoiceid) $c->add(self::INVOICE_ID,$invoiceid,Criteria::NOT_EQUAL);
		$c->add(self::IS_CANCELED,0);
		$c->add(InvoicePeer::IS_CANCELED,0);
		$c->add(InvoicePeer::IS_FULLYPAID,0);
		$rs = self::doSelectRS($c);
		$rs->next();
		return round($rs->getFloat(1),2);
	}
		
	
	public static function getAutocomplete($keyword,$field='')
	{
	  $receipts = array();

	  $con = Propel::getConnection();
		
		if ($field)
		{

		}
		else 
		{
			$query = 'SELECT ' . self::ID . ' AS id, 
					CONCAT_WS(\' , \','.self::ID.','.self::INVOICE_ID.',' . CustomerPeer::NAME . ') AS name
				FROM ' . self::TABLE_NAME . '
				INNER JOIN ' . ContractPeer::TABLE_NAME . ' ON ' . ContractPeer::ID .'='. self::CONTRACT_ID .'
				INNER JOIN ' . CustomerPeer::TABLE_NAME . ' ON ' . CustomerPeer::ID . '=' . ContractPeer::CUSTOMER_ID .'
				WHERE (' . CustomerPeer::NAME . ' LIKE \'%' . $keyword . '%\'
				OR ' . self::ID . ' = \'' . $keyword . '\'
				OR ' . self::INVOICE_ID . ' = \'' . $keyword . '\' )
				ORDER BY ' . self::ID . ' DESC';
		}

		$stmt = $con->prepareStatement($query);
	 	$rs = $stmt->executeQuery();
		while ($rs->next())
		{
			if ($field) { 
				$receipts[] = array('name' => $rs->getString('name')); 
			}  else {
				$receipts[] = array('name' => $rs->getString('name'), 'id' => $rs->getString('id'));
			}
		}
		return $receipts;
	}
	
	public static function sumReceiptsPerMonth($readingcycleid=0)
	{
		if (!$readingcycleid) 
		{
			$readingcycle = ReadingCyclePeer::getCurrentCycle();
		}
		else
		{
			$readingcycle = ReadingCyclePeer::retrieveByPk($readingcycleid);
		}
		
		$tp = split('-',$readingcycle->getName());
		$lastday = date('Y-m-d',mktime(0,0,0,$tp[1],date('t',mktime(0,0,0,$tp[1],1,$tp[0])),$tp[0]));
		$fistday = date('Y-m-d',mktime(0,0,0,$tp[1],1,$tp[0]));
		
		$c = new Criteria();
		$c->clearSelectColumns()->addSelectColumn('SUM('.self::AMOUNT.')');
		
		$criterion = $c->getNewCriterion(self::PAYMENTDATE,$fistday,Criteria::GREATER_EQUAL);
		$criterion->addAnd($c->getNewCriterion(self::PAYMENTDATE,$lastday,Criteria::LESS_EQUAL));
		$c->add($criterion);
		$c->add(self::IS_CANCELED,0);
		// $c->add(ContractPeer::TARIFF_ID,$tariff->getId());
		
		$rs = self::doSelectRS($c);
		$rs->next();
		return round($rs->getFloat(1),2);
		
	}
	
	// public static function totalMonthlyAmount($startdate, $enddate, $tariff='')
	// {
	// 	// if (!$readingcycleid) 
	// 	// {
	// 	// 	$readingcycle = ReadingCyclePeer::getCurrentCycle();
	// 	// }
	// 	// else
	// 	// {
	// 	// 	$readingcycle = ReadingCyclePeer::retrieveByPk($readingcycleid);
	// 	// }
	// 	// 
	// 	// $tp = split('-',$readingcycle->getName());
	// 	// $lastday = date('Y-m-d',mktime(0,0,0,$tp[1],date('t',mktime(0,0,0,$tp[1],1,$tp[0])),$tp[0]));
	// 	// $fistday = date('Y-m-d',mktime(0,0,0,$tp[1],1,$tp[0]));
	// 	
	// 	$c = new Criteria();
	// 	$c->clearSelectColumns()->addSelectColumn('SUM('.self::AMOUNT.')');
	// 	
	// 	$criterion = $c->getNewCriterion(self::PAYMENTDATE,$startdate,Criteria::GREATER_EQUAL);
	// 	$criterion->addAnd($c->getNewCriterion(self::PAYMENTDATE,$enddate,Criteria::LESS_EQUAL));
	// 	$c->add($criterion);
	// 	$c->add(self::IS_CANCELED,0);
	// 	// $c->add(ContractPeer::TARIFF_ID,$tariff->getId());
	// 	
	// 	$rs = self::doSelectRS($c);
	// 	$rs->next();
	// 	return round($rs->getFloat(1),2);
	// }
	
	public static function totalMonthlyAmount ($startdate, $enddate)
	{
		$totals = array();

	  $con = Propel::getConnection();
	  
		$query = "SELECT contract.tariff_id, tariff.name, 
			YEAR(receipt.paymentdate) AS Year, 
			MONTH(receipt.paymentdate) AS Month, 
			SUM(ABS(receipt.amount)) AS USD
			FROM receipt 
			INNER JOIN contract ON contract.id = receipt.contract_id
			INNER JOIN tariff ON tariff.id = contract.tariff_id
			WHERE receipt.paymentdate >= '".$startdate."' 
				AND receipt.paymentdate <= '".$enddate."' 
				AND receipt.is_canceled=0";
		// if ($tariff) $query .= "	AND invoice.tariff_id=".$tariff->getId();
		$query .= "	GROUP BY tariff_id, YEAR(receipt.paymentdate), MONTH(receipt.paymentdate)
			ORDER BY tariff_id, YEAR(receipt.paymentdate), MONTH(receipt.paymentdate)";

		$stmt = $con->prepareStatement($query);
	 	$rs = $stmt->executeQuery();
		while ($rs->next())
		{
			$totals[] = array('tariff_id' => $rs->getInt('tariff_id'),
										'name' => $rs->getString('name'), 
										'year' => $rs->getString('Year'),
										'month' => $rs->getString('Month'), 
										'amount' => $rs->getFloat('USD')
									);
		}
		return $totals;
	}
	
	public static function totalAmountPerCycle($readingcycleid)
	{
		# code...
	}
}