<?php

/**
 * Subclass for performing query and update operations on the 'invoice' table.
 *
 * 
 *
 * @package lib.model
 */ 
class InvoicePeer extends BaseInvoicePeer
{
	public static function sumInvoices($contractid)
	{
		$c = new Criteria();
		$c->clearSelectColumns()->addSelectColumn('SUM('.self::TOTALAMOUNT.')');
		$c->add(self::CONTRACT_ID,$contractid);
		$c->add(self::IS_CANCELED,0);
		$rs = self::doSelectRS($c);
		$rs->next();
		return round($rs->getFloat(1),2);
	}
	
	public static function sumUnpaidInvoices($contractid)
	{
		$c = new Criteria();
		$c->clearSelectColumns()->addSelectColumn('SUM('.self::TOTALAMOUNT.')');
		$c->add(self::CONTRACT_ID,$contractid);
		$c->add(self::IS_FULLYPAID,0);
		$c->add(self::IS_CANCELED,0);
		$rs = self::doSelectRS($c);
		$rs->next();
		return round($rs->getFloat(1),2);
	}

	public static function sumPaidInvoices($contractid)
	{
		$c = new Criteria();
		$c->clearSelectColumns()->addSelectColumn('SUM('.self::TOTALAMOUNT.')');
		$c->add(self::CONTRACT_ID,$contractid);
		$c->add(self::IS_FULLYPAID,1);
		$c->add(self::IS_CANCELED,0);
		$rs = self::doSelectRS($c);
		$rs->next();
		return round($rs->getFloat(1),2);
	}
		
	public static function getInitialBalance($id)
	{
		$c = new Criteria();
		$c->clearSelectColumns()->addSelectColumn('SUM('.self::TOTALAMOUNT.')');
		$c->add(self::CONTRACT_ID,$id);
		$c->add(self::READING_CYCLE_ID,1);
		$rs = self::doSelectRS($c);
		$rs->next();
		return round($rs->getFloat(1),2);
	}
	
	public static function countInvoices($contractid)
	{
		$c = new Criteria();
		$c->add(self::CONTRACT_ID,$contractid);
		$c->add(self::IS_CANCELED,0,Criteria::EQUAL);
		return self::doCount($c);
	}
	
	public static function countInvoicesUnpaid($contractid, $invoiceid=null)
	{
		$c = new Criteria();
		$c->add(self::CONTRACT_ID,$contractid);
		if ($invoiceid) $c->add(self::ID,$invoiceid,Criteria::NOT_EQUAL);
		$c->add(self::IS_FULLYPAID,0,Criteria::EQUAL);
		$c->add(self::IS_CANCELED,0,Criteria::EQUAL);
		return self::doCount($c);
	}
	
	public static function retrieveInvoicesUnpaid($contractid, $invoiceid=null)
	{
		$c = new Criteria();
		$c->add(self::IS_FULLYPAID,0);
		$c->add(self::IS_CANCELED,0);
		$c->add(self::CONTRACT_ID,$contractid);
		if ($invoiceid) $c->add(self::ID,$invoiceid,Criteria::NOT_EQUAL);
		return self::doSelect($c);
	}
	
	public static function hasInvoice($contractid, $cycleid = null)
	{
		$c = new Criteria();
		$c->add(self::CONTRACT_ID,$contractid);
		if ($cycleid) $c->add(self::READING_CYCLE_ID,$cycleid);
		return self::doCount($c);
	}
	
	public static function getAutocomplete($keyword,$field='')
	{
	  $invoices = array();
	
		$c = new Criteria();
		$c->clearSelectColumns()->addSelectColumn(self::ID)->addSelectColumn(CustomerPeer::NAME);
		$c->addSelectColumn(self::BILLINGDATE)->addSelectColumn(ContractPeer::NAME);
		
		$c->addJoin(self::CONTRACT_ID,ContractPeer::ID);
		$c->addJoin(ContractPeer::CUSTOMER_ID,CustomerPeer::ID);
		$c1 = $c->getNewCriterion(self::ID,$keyword);
		$c2 = $c->getNewCriterion(CustomerPeer::NAME,"%$keyword%",Criteria::LIKE);
		$c1->addOr($c2);
		$c->add($c1);
		
		if ($field == 'unpaid') $c->add(self::IS_FULLYPAID,0);
		if ($field == 'paid')   $c->add(self::IS_FULLYPAID,1);
		
		$c->addAscendingOrderByColumn(CustomerPeer::NAME);
		$c->addDescendingOrderByColumn(self::ID);
		
		$rs = self::doSelectRS($c);	
		
		while ($rs->next())
		{
			$invoices[] = array('name' => $rs->getInt(1) . ', ' . $rs->getString(3) . ', ' .$rs->getString(2) . (($rs->getString(4))?' _' . $rs->getString(4):''), 
													'id' => $rs->getInt(1));
		}
		return $invoices;
	}
	
	public static function sumInvoicesPerCycle($readingcycleid='')
	{
		if (!$readingcycleid) 
		{
			$readingcycle = ReadingCyclePeer::getCurrentCycle();
			$readingcycleid = $readingcycle->getId();
		}
		$c = new Criteria();
		$c->clearSelectColumns()->addSelectColumn('SUM('.self::TOTALAMOUNT.')');
		$c->add(self::READING_CYCLE_ID,$readingcycleid);
		$c->add(self::IS_CANCELED,0);
		
		$rs = self::doSelectRS($c);
		$rs->next();
		return round($rs->getFloat(1),2);
	}
	
	public static function reportPerTariff($startdate, $enddate)
	{
	}
	
	// public static function totalConsumptionPerMonth($startdate, $enddate, $tariff='')
	// {
	// 	$totals = array();
	// 
	//   $con = Propel::getConnection();
	//   
	// 	$query = "SELECT invoice.tariff_id, 
	// 		YEAR(invoice.billingdate) AS Year, 
	// 		MONTH(invoice.billingdate) AS Month, 
	// 		SUM(ABS(invoice_reading.consumption)) AS kWh 
	// 		FROM invoice 
	// 		INNER JOIN invoice_reading ON invoice_reading.invoice_id = invoice.id
	// 		WHERE invoice_reading.newdate >= '".$startdate."' 
	// 			AND invoice_reading.newdate <= '".$enddate."' 
	// 			AND invoice.is_canceled=0";
	// 	if ($tariff) $query .= "	AND invoice.tariff_id=".$tariff->getId();
	// 	$query .= "	GROUP BY YEAR(invoice.billingdate), MONTH(invoice.billingdate)
	// 		ORDER BY YEAR(invoice.billingdate), MONTH(invoice.billingdate)";
	// 
	// 	$stmt = $con->prepareStatement($query);
	//  	$rs = $stmt->executeQuery();
	// 	while ($rs->next())
	// 	{
	// 		$totals[] = array('tariff_id' => $rs->getInt('tariff_id'), 
	// 									'year' => $rs->getString('Year'),
	// 									'month' => $rs->getString('Month'), 
	// 									'consumption' => $rs->getFloat('kWh')
	// 								);
	// 	}
	// 	return $totals;
	// }
	
	public static function totalMonthlyConsumption($startdate, $enddate)
	{
		$totals = array();

	  $con = Propel::getConnection();
	  
		$query = "SELECT invoice.tariff_id, tariff.name,
			YEAR(invoice.billingdate) AS Year, 
			MONTH(invoice.billingdate) AS Month, 
			SUM(ABS(invoice_reading.consumption)) AS kWh 
			FROM invoice 
			INNER JOIN invoice_reading ON invoice_reading.invoice_id = invoice.id
			INNER JOIN tariff ON tariff.id = invoice.tariff_id
			WHERE invoice.billingdate >= '".$startdate."' 
				AND invoice.billingdate <= '".$enddate."' 
				AND invoice.is_canceled=0
			GROUP BY tariff_id, YEAR(invoice.billingdate), MONTH(invoice.billingdate)
			ORDER BY tariff_id, YEAR(invoice.billingdate), MONTH(invoice.billingdate)";

		$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('kWh')
									);
		}
		return $totals;
	}
	
	public static function totalMonthlyAmount($startdate, $enddate, $tariff = 0)
	{
		$totals = array();

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

		$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;
	}
	
}