<?php

/**
 * Subclass for performing query and update operations on the 'contract' table.
 *
 * 
 *
 * @package lib.model
 */ 
class ContractPeer extends BaseContractPeer
{
	public static function nbActiveContract($serviceid = 1,$startdate = null,$enddate= null)
	{
		$c = new Criteria();
		$c->add(self::SERVICE_ID,$serviceid);
		$c->add(self::CONTRACT_STATUS_ID,sfConfig::get('app_contract_status_active'));
		if (!$startdate) $startdate = date('Y-m-d');
		$c->add(self::STARTDATE,$startdate,Criteria::LESS_EQUAL);
		if ($enddate)
		{
			$cton1 = $c->getNewCriterion(self::ENDDATE,null);
			$cton2 = $c->getNewCriterion(self::ENDDATE,$enddate,Criteria::LESS_EQUAL);
			$cton1->addOr($cton2);
			$c->add($cton1);
		}
		else
		{
			$c->add(self::ENDDATE,null);
		}
		return self::doCount($c);
	}
	
	public static function activeContracts($reading_cycle_id,$tariffid = null,$districtid=null,$ismetered=0)
	{
		$cycle = ReadingCyclePeer::retrieveByPk($reading_cycle_id);
		$c = new Criteria();

		if ($districtid) $c->add(self::DISTRICT_ID,$districtid);
		if ($ismetered) $c->add(self::IS_METERED,1);
		
		$c->add(self::SERVICE_ID,$cycle->getServiceId());
		$c->add(self::CONTRACT_STATUS_ID,sfConfig::get('app_contract_status_active'));
		$c->add(self::STARTDATE,$cycle->getStartdate(),Criteria::LESS_EQUAL);
		if ($cycle->getEnddate())
		{
			$cton1 = $c->getNewCriterion(self::ENDDATE,null);
			$cton2 = $c->getNewCriterion(self::ENDDATE,$cycle->getEnddate(),Criteria::LESS_EQUAL);
			$cton1->addOr($cton2);
			$c->add($cton1);
		}
		else
		{
			$c->add(self::ENDDATE,null);
		}
		if ($tariffid)
		{
			$c->add(self::TARIFF_ID,$tariffid);
			$c->addAscendingOrderByColumn(self::TARIFF_ID);
		}
		$c->addDescendingOrderByColumn(self::SUPPLYPOINT_1);
		$c->addDescendingOrderByColumn(self::SUPPLYPOINT_2);
		$c->addDescendingOrderByColumn(self::SUPPLYPOINT_3);
		$c->addDescendingOrderByColumn(self::DISTRICT_ID);
		
		return self::doSelect($c);		
	}
	
	public static function contractBalance($contractid)
	{
		$balance = '';
	  $con = Propel::getConnection();
		
		$query = 'SELECT 
						(SELECT SUM(%s) FROM %s WHERE %s=%s and %s=0) as receipts,
						(SELECT SUM(%s) FROM %s WHERE %s=%s and %s=1 and %s=0) as invoices';
		
		$query = sprintf($query, ReceiptPeer::AMOUNT, ReceiptPeer::TABLE_NAME, ReceiptPeer::CONTRACT_ID, $contractid, ReceiptPeer::IS_CANCELED,
								InvoicePeer::TOTALAMOUNT, InvoicePeer::TABLE_NAME, InvoicePeer::CONTRACT_ID, $contractid, InvoicePeer::IS_FULLYPAID, InvoicePeer::IS_CANCELED);
	  
		$stmt = $con->prepareStatement($query);
	  $rs = $stmt->executeQuery();
	 	while($rs->next())
		{
			$balance = $rs->getFloat('invoices') - $rs->getFloat('receipts');
		}
		return round($balance,2);
	}
	
	public static function contractTotalBalance($contractid)
	{
		$balance = '';
	  $con = Propel::getConnection();
		
		$query = 'SELECT 
						(SELECT SUM(%s) FROM %s WHERE %s=%s and %s=0)as receipts,
						(SELECT SUM(%s) FROM %s WHERE %s=%s and %s=0) as invoices';
		
		$query = sprintf($query, ReceiptPeer::AMOUNT, ReceiptPeer::TABLE_NAME, ReceiptPeer::CONTRACT_ID, $contractid, ReceiptPeer::IS_CANCELED,
								InvoicePeer::TOTALAMOUNT, InvoicePeer::TABLE_NAME, InvoicePeer::CONTRACT_ID, $contractid, InvoicePeer::IS_CANCELED);
	  
		$stmt = $con->prepareStatement($query);
	  $rs = $stmt->executeQuery();
	 	while($rs->next())
		{
			$balance = $rs->getFloat('invoices') - $rs->getFloat('receipts');
		}
		return round($balance,2);
	}
	
	
	public static function getAutocomplete($keyword,$field='',$status='')
	{
	  $contracts = array();

	  $con = Propel::getConnection();
		if ($field) 
		{
			switch($field){
				case 'supplypoint1': $elt = self::SUPPLYPOINT_1; break;
				case 'supplypoint2': $elt = self::SUPPLYPOINT_2; break;
				case 'supplypoint3': $elt = self::SUPPLYPOINT_3; break;
				case 'supply_city':  $elt = self::SUPPLY_CITY; break;
				case 'supply_zip': 	 $elt = self::SUPPLY_ZIP; break;
				case 'billing_city': $elt = self::BILLING_CITY; break;
				case 'billing_zip':  $elt = self::BILLING_ZIP; break;
			}

			$query = 'SELECT DISTINCT ' . $elt . ' AS name
				FROM ' . self::TABLE_NAME . '
				WHERE ' . $elt . ' LIKE \'%' . $keyword . '%\'';
		} 
		else 
		{			
			$query = 'SELECT ' . self::ID . ' AS id, 
				CONCAT_WS(\' , \','. self::ID .',' . CustomerPeer::NAME . ','.
				self::NAME . ') AS name
				FROM ' . self::TABLE_NAME . '
				INNER JOIN ' . CustomerPeer::TABLE_NAME .' ON ' . CustomerPeer::ID . ' = ' . self::CUSTOMER_ID . '
				WHERE (' . 
				self::NAME . ' LIKE \'%' . $keyword . '%\'
				OR ' . self::ID . ' = \'' . $keyword . '\'
				OR ' . CustomerPeer::NAME . ' LIKE \'%' . $keyword . '%\'
				)';
			if ($status)
			{
				$query .= ' AND ' . self::CONTRACT_STATUS_ID . '='. $status;
			}
			$query .= ' ORDER BY ' . self::NAME;
		}

	  $stmt = $con->prepareStatement($query);
	  $rs = $stmt->executeQuery();
	  while ($rs->next())
	  {
			if ($field)
			{
				$contracts[] = array('name' => $rs->getString('name'));
			}
			else 
			{
				$contracts[] = array('name' => rtrim($rs->getString('name'),', '), 'id' => $rs->getString('id'));
			}
	  }
	  return $contracts;
	}

}