Отчёт поиск договоров по тарифу

<?xml version="1.0" encoding="UTF-8"?>
<report title="Поиск договоров по тарифам CSV (Скат)" type="java">
	<contractGroups name="contractGroups" title="Группы договоров"/>
	<contractGroups name="contractGroups2" title="Исключая группы"/>
	<tariffs name="tariffs" title="Тарифы"/>
	<tariffGroups name="tariffGroups" title="Группы тарифов"/>
	<combo name="isOnlyActive" title="Только активные тарифы и группы тарифов">
		<item id="1" title="Да"/>
		<item id="0" title="Нет"/>
	</combo>
	<list name="status" title="Статус">
		<item id="0" title="активен"/>
		<item id="1" title="в отключении"/>
		<item id="2" title="отключен"/>				
		<item id="3" title="закрыт"/>		
		<item id="4" title="приостановлен"/>		
		<item id="5" title="в подключении"/>		
	</list>
	<fields>
		<item id="title" title="Имя"/>
		<item id="comment" title="Комментарий"/>
		<item id="cid" title="#cid"/>
		<item id="status" title="Статус"/>
		<item id="tptitle" title="Тариф"/>
		<item id="tpdate" title="Период"/>
		<item id="tgtitle" title="Группа тарифа"/>
		<item id="tgdate" title="Период"/>
	</fields>
</report>

import java.sql.*;
import java.util.*;
import java.util.regex.*;
import java.sql.*;
import bitel.billing.server.util.*;
import bitel.billing.server.contract.bean.*;
import bitel.billing.common.TimeUtils;
 
public void fillReport( con, filter, result )
{
	// Группы договоров. Типа 536870912.
	long groupMask = filter.getLongParam( "contractGroups" );
	// Исключая группы договоров. Типа 536870912.
	long groupMask2 = filter.getLongParam( "contractGroups2" );
	// Тарифы. Типа "190, 47".
	String tariffsList = filter.getStringParam( "tariffs" );
	// Искать только активные?
	boolean isOnlyActive = filter.getIntParam( "isOnlyActive" ) == 1;
	// Группы тарифов
	String tariffGroups = filter.getStringParam( "tariffGroups" );
	// Статус
	String statuss = filter.getStringParam( "status" );
 
	String query =
		" SELECT contract.id,contract.title,contract.comment,contract.status," +
		"contract_tariff.tpid, contract_tariff.date1, contract_tariff.date2, tariff_plan.title, " +
		"contract_tariff_group.gid, contract_tariff_group.date1, contract_tariff_group.date2, tariff_group.title";
	query += " FROM contract ";
	query += " LEFT JOIN contract_tariff ON contract_tariff.cid=contract.id ";
	query += " LEFT JOIN tariff_plan ON contract_tariff.tpid=tariff_plan.id ";
	query += " LEFT JOIN contract_tariff_group ON  contract_tariff_group.cid=contract.id ";
	query += " LEFT JOIN tariff_group ON contract_tariff_group.gid=tariff_group.id ";
	query += " WHERE (1) ";
 
	if( groupMask > 0 )
	{
		query += " AND contract.gr&?!=0 ";
	}
	if( groupMask2 > 0 )
	{
		query += " AND contract.gr&?=0 ";
	}
	if( tariffsList != null && !tariffsList.isEmpty() )
	{
		query += " AND contract_tariff.tpid in ("+tariffsList+") ";
	}
	if( tariffGroups != null && !tariffGroups.isEmpty() )
	{
		query += " AND contract_tariff_group.gid IN ("+tariffGroups+") ";
	}
	if( statuss != null && !statuss.isEmpty() )
	{
		query += " AND contract.status IN (" + statuss + ") ";
	}
 
	if( isOnlyActive )
	{
		query += " AND (contract_tariff.date1 IS NULL OR TO_DAYS(contract_tariff.date1)<=TO_DAYS(now())) AND (contract_tariff.date2 IS NULL OR TO_DAYS(now())<=TO_DAYS(contract_tariff.date2)) ";
		query += " AND (contract_tariff_group.date1 IS NULL OR TO_DAYS(contract_tariff_group.date1)<=TO_DAYS(now())) AND (contract_tariff_group.date2 IS NULL OR TO_DAYS(now())<=TO_DAYS(contract_tariff_group.date2)) ";
	}
 
	ps = con.prepareStatement( query );
 
	int index = 1;
 
	if( groupMask > 0 )
	{
		ps.setLong( index++, groupMask );
	}
	if( groupMask2 > 0 )
	{
		ps.setLong( index++, groupMask2 );
	}
 
	ResultSet rs = ps.executeQuery();
 
	ArrayList data = new ArrayList( 1000 );
	while( rs.next() )
	{
		cid = rs.getString("contract.id");
	    title = rs.getString("contract.title");
	    comment = rs.getString("contract.comment"); 
	    status = rs.getInt("contract.status");
 
	    tpdate1 = rs.getDate("contract_tariff.date1");
	    tpdate2 = rs.getDate("contract_tariff.date2");
	    tptitle = rs.getString("tariff_plan.title");
 
	    tgdate1 = rs.getDate("contract_tariff_group.date1");
	    tgdate2 = rs.getDate("contract_tariff_group.date2");
	    tgtitle = rs.getString("tariff_group.title");
 
	    map = new HashMap();
	    map.put( "cid", cid );
	    map.put( "title", title );
	    map.put( "comment", comment );
	    map.put( "status", ContractStatus.statusToString( status ) );
	    map.put( "tpdate", TimeUtils.formatPeriod( tpdate1, tpdate2 ) );
	    map.put( "tptitle", tptitle );
	    map.put( "tgdate", TimeUtils.formatPeriod( tgdate1, tgdate2 ) );
	    map.put( "tgtitle", tgtitle );
	    data.add( map );
	}
 
	result.setData( data );
}