BiTel

package ru.skyb.reports;

import bitel.billing.server.admin.reports.BGReportFilter;
import bitel.billing.server.reports.BGCSVReport.CSVFillerDataFields;
import bitel.billing.server.reports.BGCSVReport.ReportResult;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class AddressForAgents
   implements bitel.billing.server.reports.BGCSVReport.CSVFillerData
{
   @Override
   public void fillReport( Connection con, BGReportFilter filter, ReportResult result )
      throws Exception
   {
      List<Map<String, String>> data = new ArrayList<Map<String, String>>( 1000 );
      StringBuilder sb = new StringBuilder();
      int city = filter.getIntParam( "city" );
      String street = filter.getStringParam( "street" );
      String house = filter.getStringParam( "house" );
      String frac = filter.getStringParam( "frac" );
      int size = filter.getIntParam( "size" );
      String query = " SELECT c.id, c.title, flat FROM contract AS c " +
                 " LEFT JOIN contract_parameter_type_2 AS cpt ON cpt.cid = c.id " +
                 " LEFT JOIN address_house AS ah ON ah.id = cpt.hid " +
                 " WHERE (c.date2 > NOW() OR c.date2 IS NULL) AND cpt.pid = 1 " +
                 " AND ah.streetid = " + street + " AND ah.house = " + house + " ORDER BY flat";
      if (!frac.equals("") && !frac.isEmpty())
         {
            query += " AND ah.frac= '/" + frac + "'";
         }
      int i = 0;
       PreparedStatement ps = con.prepareStatement( query );
       //System.out.println( "query=" +  ps.toString() );
        ResultSet rs = ps.executeQuery();
        while ( rs.next() )
        {
              Map<String, String> map = new HashMap<String, String>();
         int cid = rs.getInt(1);
         String title = rs.getString(2);
         int flat = rs.getInt(3);
         for(i = 0; i <= size; i++ )
            {
               if(i != flat)
                  {
                     System.out.println( "i = " + i +" flat " + flat);
                     map.put("number", Integer.toString(i));
                  }
            //map.put("number", title);
            data.add( map );
              }
      }
        result.setData( data );
   }
}

List<Integer> flatList = new ArrayList<Integer>();
while ( rs.next() )
{
   int cid = rs.getInt(1);
   String title = rs.getString(2);
   int flat = rs.getInt(3);
   flatList.add(flat);
}

for(int flat=1; flat<=size; flat++){
   if( !flatList.contains(flat) ){
      Map<String, String> map = new HashMap<String, String>();
      map.put("number", Integer.toString(flat));
      data.add( map );
   }
}

result.setData( data );