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

Внимание! Данное решение/метод/статья относится к версии 5.1 и для других версий может быть неактуальна! Вам нужно самостоятельно поправить решение под свои нужды или воспользоваться помощью на форуме. Будем признательны, если внизу страницы или отдельной статьёй вы разместите исправленное решение для другой версии или подсказки что надо исправить.

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