Пример конвертера данных из CSV-файлов в базу BGBilling - 2

import bitel.billing.server.admin.bean.*;
import bitel.billing.server.util.*;
import com.Ostermiller.util.*;
import java.util.*;
import bitel.billing.common.*;
import java.io.*;
import java.sql.*;
import bitel.billing.server.contract.bean.*;
import bitel.billing.server.contract.*;
import ru.bitel.common.*;
import java.math.*;
import java.util.regex.*;
import java.text.*;
 
int LOADED_GROUP = 18;
int PATTERN_ID = 11;
int PAYMENT_TYPE_ID = 19;
int CHARGE_TYPE_ID = 3;
int CITY_ID = 3;
int ADDRESS_PARAM_ID = 14;
int PHONE_PARAM_ID = 7;
int FIO_PARAM_ID = 1;
 
java.util.Date curdate = new java.util.Date();
Calendar now = new GregorianCalendar();
 
DecimalFormat format6 = new DecimalFormat( "000000" );
DecimalFormat format7 = new DecimalFormat( "0000000" );
 
public void main( setup, con, conSlave )
{
	ContractRemover cr = new ContractRemover( Setup.getSetup(), con, null );
 
	long timeStart = System.currentTimeMillis();
 
	// удаление ранее загруженных договоров
	query = "SELECT id FROM contract";
	//query = "SELECT	id FROM  contract WHERE gr & (1<<?) != 0 ";
	ps  = con.prepareStatement( query );
	//ps.setInt( 1, LOADED_GROUP );
	rs = ps.executeQuery();
 
	int count = 0;
	while ( rs.next() )
	{
		int cid = rs.getInt( 1 );
		cr.deleteContract( cid );
		count ++;				
	}
	ps.close();
	//
	print( "Removed " + count + " contracts.." );
 
	loadFile( "/home/light/data/1.csv", setup, con, 7 );
	loadFile( "/home/light/data/2_C.csv", setup, con, 8 );
 
	long timeEnd = System.currentTimeMillis();
 
	print( "Process time: " + (timeEnd - timeStart)/1000 + " s." );
}
 
private void loadFile( file, setup, con, patternId )
{
	int rowNum = 0;
 
	print( "Loading " + file );
 
	ContractManager cm = new ContractManager( con );
	ContractTariffManager ctm = new ContractTariffManager( con );
	ContractRemover cr = new ContractRemover( Setup.getSetup(), con, null );
	ContractParameterManager cpm = new ContractParameterManager( con );	
	PaymentManager paymentManager = new PaymentManager( con );
	ChargeManager chargeManager = new ChargeManager( con );
	BalanceUtils bu = new BalanceUtils( con );
 
	String query = "INSERT INTO contract_balance(yy, mm, cid, summa1) VALUES(?,?,?,?)";
	PreparedStatement psInsertBalance = con.prepareStatement( query );
	psInsertBalance.setInt( 1, now.get( Calendar.YEAR ) );
	psInsertBalance.setInt( 2, now.get( Calendar.MONTH ) + 1 );
 
	String[][] data = ExcelCSVParser.parse( new InputStreamReader( new FileInputStream( file ), "windows-1251" ) , '\t' );
	if( data != null && data.length > 0 )
	{
		final int rowCount = data.length;
		for( rowNum = 1; rowNum < rowCount; rowNum++ )
       {
			String[] fields = data[rowNum];
			if( fields.length < 9 )
			{
				error( "Incorrect line " + rowNum );
				continue;
			} 
 
			/*
			if( rowNum > 50 )
			{
				print( "Break" );
				break;
			}
			*/
 
			if( rowNum % 100 == 0 )
			{
				System.out.println( "Count => " + rowNum );
			}
 
 			String fio = fields[0];
			String licSchet = fields[1];
			String address = fields[2];
			String phone = fields[3];
			String tariff = fields[4];
 
			BigDecimal saldo = BigDecimal.ZERO;
			BigDecimal pay = BigDecimal.ZERO;
			BigDecimal charge = BigDecimal.ZERO;
 
			try
			{
				saldo = new BigDecimal( fields[5].replaceAll( " ", "0" ) );
				pay = new BigDecimal( fields[6].replaceAll( " ", "0" ) );
				charge = new BigDecimal( fields[7].replaceAll( " ", "0" ) );
			}
			catch( Exception ex )
			{
				error( "Error load summ file: " + file + "; line: " + rowNum + "; fio: " + fio );
			}
 
			//print( "FIO:" + fio + "; licChet: " + licSchet );			
 
			String pswd = PswdGen.generatePassword( 5, "0123456789" );	
 
			String title = licSchet;
			if( patternId == 7 )
			{
				title = format7.format( Utils.parseLong( title ) );
			}
			else
			{	
				title = "С" + format6.format( Utils.parseLong( title.substring( 1 ) ) );
			}
 
			// создание договора
			Contract contract = cm.createFromPattern( patternId, title, TimeUtils.convertDateToCalendar( curdate ), pswd );
 
			if( "1".equals( tariff ) )
			{
				contract.setGroups( contract.getGroups() | (1L << 3) );
			}
			else
			{
				contract.setGroups( contract.getGroups() | (1L<< 2) );
			}
 
			contract.setComment( fio );
			cm.updateContract( contract );			
 
			setAddress( con, cpm, contract.getID(), address );
 
			if( Utils.notBlankString( phone ) )
			{
				cpm.setStringParam( contract.getID(), PHONE_PARAM_ID, phone, User.USER_SERVER );
			}
			cpm.setStringParam( contract.getID(), FIO_PARAM_ID, fio, User.USER_SERVER );
 
			// баланс
			if( saldo.compareTo( BigDecimal.ZERO ) != 0 )
			{
				psInsertBalance.setInt( 3, contract.getID() );
				psInsertBalance.setBigDecimal( 4, saldo );
				psInsertBalance.executeUpdate();
			}
 
			// приход
			if( pay.compareTo( BigDecimal.ZERO ) != 0 )
			{
				Payment payment = new Payment();
				payment.setPaymentTypeID( PAYMENT_TYPE_ID );
				payment.setContractID( contract.getID() );
				payment.setDate( curdate );
				payment.setSumma( pay );
				payment.setComment( "Импорт" );
				payment.setUserID( User.USER_SERVER );
 
				paymentManager.updatePayment( payment );
			}
 
			// расход			
			if( charge.compareTo( BigDecimal.ZERO ) != 0 )
			{
				Charge c = new Charge();
				c.setChargeTypeID( CHARGE_TYPE_ID );
				c.setContractID( contract.getID() );
				c.setChargeDate( curdate );
				c.setSumma( charge );
				c.setComment( "Импорт" );
				c.setUserID( User.USER_SERVER );
 
				chargeManager.updateCharge( c );
			}
 
			bu.updateBalance( curdate, contract.getID() );
 
			//	установка тарифа
			ContractTariff contractTariff = new ContractTariff();
			contractTariff.setContractID( contract.getID() );
			if( tariff.equals( "1" ) )
			{
				contractTariff.setTariffPlanID( 42 );
			}
			else
			{
				contractTariff.setTariffPlanID( 43 );
			}
			contractTariff.setComment( "" );
			contractTariff.setDate1( TimeUtils.convertDateToCalendar( curdate ) );
			ctm.updateContractTariff( contractTariff );
		}
	}
}
 
Map streetMap;
Map houseMap;
 
private void setAddress( con, cpm, cid, address )
{
	String[] tokens = address.split( "," );
	if( tokens.length < 3 )
	{
		error( "Error process address: " + address );
		return;
	}
 
	if( streetMap == null )
	{
		streetMap = new HashMap();
 
		String query = "SELECT id, title FROM address_street";
		PreparedStatement ps = con.prepareStatement( query );
		ResultSet rs = ps.executeQuery();
		while( rs.next() )
		{			
			streetMap.put( rs.getString( 2 ), rs.getInt( 1 ) );		
 
			//print( "Loaded street: " + rs.getString( 2 ) + " => " + rs.getString( 1 ) );
		}
		ps.close();
	}
 
	if( houseMap == null )
	{
		houseMap = new HashMap();
 
		String query = "SELECT streetid, house, frac, id FROM address_house";
		PreparedStatement ps = con.prepareStatement( query );
		ResultSet rs = ps.executeQuery();
		while( rs.next() )
		{
			String key = rs.getString( 1 ) + rs.getString( 2 ) + rs.getString( 3 );
			int id = rs.getInt( 4 );			
 
			houseMap.put( key, id );
 
			//print( "Loaded house: " + key + " => " + id );
		}
		ps.close();
	}
 
	int tokenIndex = 0;
	String street = tokens[tokenIndex++].trim();
	String house = tokens[tokenIndex++].substring( 2 ).trim();
	String frac = "";
	String flat = "";
 
	// поддержка домов вида 2Б
	for( int i = 0; i < house.length(); i++ )
	{
		if( !Character.isDigit( house.charAt( i ) ) )
		{
			house = house.substring( 0, i );
			frac = house.substring( i ) + " ";
			break;
		}
	}
 
	while( tokenIndex < tokens.length )
	{
		String token = tokens[tokenIndex];
 
		if( token.indexOf( "кв." ) >= 0 )
		{
			flat = token.substring( 3 ).trim();
		}
		else if (token.indexOf( "корп." ) >= 0 )
		{
			frac += token.trim();
		}
		else
		{
			error( "Error take address token: " + address );
			return;
		}
		tokenIndex++;
	}
 
	//print( "Street: " + street + "; house: " + house + "; frac: " + frac + "; flat: " + flat );
 
	Integer streetId = streetMap.get( street );
	if( streetId == null )
	{
		String query = "INSERT INTO address_street(cityid, title) VALUES(?, ?)";
		PreparedStatement ps = con.prepareStatement( query, PreparedStatement.RETURN_GENERATED_KEYS );
		ps.setInt( 1, CITY_ID );
		ps.setString( 2, street );
		ps.executeUpdate();
 
		streetId = ServerUtils.lastInsertId( ps );
		streetMap.put( street, streetId );
 
		ps.close();
	}
 
	String houseKey = streetId + house + frac;
 
	//print( "streetId: " + streetId + "; House key: " + houseKey );
 
	Integer houseId = houseMap.get( houseKey );
	if( houseId == null )	
	{
		print( "Insert house: " + houseKey );
 
		String query = "INSERT INTO address_house(streetid, house, frac) VALUES(?,?,?)";
		PreparedStatement ps = con.prepareStatement( query, PreparedStatement.RETURN_GENERATED_KEYS );
		ps.setInt( 1, streetId );
		ps.setString( 2, house );
		ps.setString( 3, frac );
		ps.executeUpdate();
 
		houseId = ServerUtils.lastInsertId( ps );
		houseMap.put( houseKey, houseId );
 
		ps.close();
	}
 
	//print( "cid: " + cid + "; streetId: " + streetId + "; houseId: " + houseId );
 
	ContractAddressParamValue addressParam = new ContractAddressParamValue();
	addressParam.setHouseId( houseId );
	addressParam.setFlat( flat );
	addressParam.setRoom( "" );
	cpm.setAddressParam( cid, ADDRESS_PARAM_ID, addressParam, User.USER_SERVER );
}