com.fgm.dataParser

Class ContractsParser

java.lang.Object

com.fgm.dataParser.ContractsParser

public class ContractsParser
extends java.lang.Object

The Class ContractsParser.

Field Summary

Fields

Modifier and Type	Field and Description
private java.sql.Connection	_dbConnection The _db connection.
protected org.apache.log4j.Logger	_logger The _logger.
static java.util.Map<java.lang.String,java.lang.String>	columnMap The Constant columnMap.
private java.lang.String	database The database.
private java.lang.String	driver The driver.
private java.lang.String	host The host.
static java.util.List<java.lang.String>	NULLABLE_COLUMNS The Constant NULLABLE_COLUMNS.
static java.util.Map<java.lang.String,java.lang.String>	OBLIGATIONS_COLUMN_MAP The Constant OBLIGATIONS_COLUMN_MAP.
static java.lang.String	OBLIGATIONS_TABLE_NAME The Constant OBLIGATIONS_TABLE_NAME.
private java.lang.String[]	obligationsBlackList The obligations black list.
private java.lang.String[]	obligationsWhiteList The obligations white list.
private java.lang.String[]	obligationsWhiteListColumnNames The obligations white list column names.
static java.util.Map<java.lang.String,java.lang.String>	OUTLAYS_COLUMN_MAP The Constant OUTLAYS_COLUMN_MAP.
static java.lang.String	OUTLAYS_TABLE_NAME The Constant OUTLAYS_TABLE_NAME.
private java.lang.String[]	outlaysBlackList The outlays black list.
private java.lang.String[]	outlaysWhiteList The outlays white list.
private java.lang.String[]	outlaysWhiteListColumnNames The outlays white list column names.
private java.lang.String	password The password.
private static java.lang.String	PROP_FILE The Constant PROP_FILE.
private java.lang.String	user The user.

Constructor Summary

Constructors

Constructor and Description
ContractsParser() Instantiates a new contracts parser.

Method Summary

Methods

Modifier and Type	Method and Description
private java.lang.String[]	buildFindSql(java.lang.String[] whiteList, java.util.Map columnsMap) build a list of sqls to used for finding duplicates in the table.
private java.lang.Boolean	checkForFields(java.util.Map row, java.util.Map columnsMap) check row against the columnMap 1.
private void	closeDBConnection() Close db connection.
private java.lang.Boolean	findDuplicateRow(java.util.Map row, java.lang.String tableName, java.lang.String[] columnNames, java.lang.String[] columnValues) look for duplicate row in the database table.
private java.sql.Connection	getDBConnection() Gets the DB connection.
private int	getSqlType(java.lang.String columnName) Budget Center = budget_center java.sql.Types.VARCHAR Sub-Budget Center = sub_budget_center java.sql.Types.VARCHAR Executing Budget Center = executing_budget_center java.sql.Types.VARCHAR Appropriation Year = appropriation_year java.sql.Types.DECIMAL Appropriation = appropriation java.sql.Types.VARCHAR Requisition ID = requisition_id java.sql.Types.VARCHAR PO Reference = po_reference java.sql.Types.VARCHAR ACR = acr java.sql.Types.VARCHAR Obligation Date = obligation_date java.sql.Types.DATE Obligated Amount = obligated_amount java.sql.Types.DECIMAL Expensed Amount = expensed_amount java.sql.Types.DECIMAL Expensed Date = expensed_date java.sql.Types.DATE Voucher ID = voucher_id java.sql.Types.VARCHAR
private void	initDBConnection() Inits the database connection.
static void	main(java.lang.String[] args) The main method.
java.lang.String	parseCognosXLSXFile(java.lang.String filename) main interface to the client parses XLSX file and stores in the database.
private java.util.ArrayList	parseCOGNOSXLSXFile(java.lang.String filename) parses XLXS file and stores key/value pair in ArrayList.
static void	printMap(java.util.Map mp) util function to print key/value pairs in the mao.
private java.lang.String	saveCOGNOSData(java.lang.String tableName, java.util.ArrayList data, java.lang.String[] whiteListColumnNames, java.lang.String[] whiteList, java.util.Map columnsMap) Saves COGNOS data into database table using whitelists 1000 at a time using batch function.
private java.lang.String	saveDataToTable(java.util.ArrayList dataToSave, java.lang.String tableName, java.util.Map columnsMap) save data to table using columnsMap and batching 1000 at a time.
private void	setVarchar(java.sql.PreparedStatement stmt, int col, java.lang.String var, int type) set type of column specified in the parameter.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

_logger

protected org.apache.log4j.Logger _logger

The _logger.

PROP_FILE

private static final java.lang.String PROP_FILE

The Constant PROP_FILE.

See Also:

Constant Field Values

OBLIGATIONS_TABLE_NAME

public static final java.lang.String OBLIGATIONS_TABLE_NAME

The Constant OBLIGATIONS_TABLE_NAME.

See Also:

Constant Field Values

OUTLAYS_TABLE_NAME

public static final java.lang.String OUTLAYS_TABLE_NAME

The Constant OUTLAYS_TABLE_NAME.

See Also:

Constant Field Values

columnMap

public static final java.util.Map<java.lang.String,java.lang.String> columnMap

The Constant columnMap.

OBLIGATIONS_COLUMN_MAP

public static final java.util.Map<java.lang.String,java.lang.String> OBLIGATIONS_COLUMN_MAP

The Constant OBLIGATIONS_COLUMN_MAP.

OUTLAYS_COLUMN_MAP

public static final java.util.Map<java.lang.String,java.lang.String> OUTLAYS_COLUMN_MAP

The Constant OUTLAYS_COLUMN_MAP.

NULLABLE_COLUMNS

public static final java.util.List<java.lang.String> NULLABLE_COLUMNS

The Constant NULLABLE_COLUMNS.

obligationsWhiteList

private java.lang.String[] obligationsWhiteList

The obligations white list.

obligationsWhiteListColumnNames

private java.lang.String[] obligationsWhiteListColumnNames

The obligations white list column names.

obligationsBlackList

private java.lang.String[] obligationsBlackList

The obligations black list.

outlaysWhiteList

private java.lang.String[] outlaysWhiteList

The outlays white list.

outlaysWhiteListColumnNames

private java.lang.String[] outlaysWhiteListColumnNames

The outlays white list column names.

outlaysBlackList

private java.lang.String[] outlaysBlackList

The outlays black list.

_dbConnection

private java.sql.Connection _dbConnection

The _db connection.

host

private java.lang.String host

The host.

database

private java.lang.String database

The database.

driver

private java.lang.String driver

The driver.

user

private java.lang.String user

The user.

password

private java.lang.String password

The password.

Constructor Detail

ContractsParser

public ContractsParser()

Instantiates a new contracts parser.

Method Detail

initDBConnection

private void initDBConnection()
                       throws java.sql.SQLException

Inits the database connection.

Throws:

java.sql.SQLException - the SQL exception

buildFindSql

private java.lang.String[] buildFindSql(java.lang.String[] whiteList,
                              java.util.Map columnsMap)

build a list of sqls to used for finding duplicates in the table.

Parameters:

whiteList - the white list

columnsMap - the columns map

Returns:

the string[]

getDBConnection

private java.sql.Connection getDBConnection()
                                     throws java.sql.SQLException

Gets the DB connection.

Returns:

the DB connection

Throws:

java.sql.SQLException - the SQL exception

closeDBConnection

private void closeDBConnection()
                        throws java.sql.SQLException

Close db connection.

Throws:

java.sql.SQLException - the SQL exception

parseCognosXLSXFile

public java.lang.String parseCognosXLSXFile(java.lang.String filename)

main interface to the client parses XLSX file and stores in the database.

Parameters:

filename - the filename

Returns:

the string

checkForFields

private java.lang.Boolean checkForFields(java.util.Map row,
                               java.util.Map columnsMap)

check row against the columnMap 1. make sure all the keys exists in the row 2. non-nullable columns should have a value in it

Parameters:

row - the row

columnsMap - the columns map

Returns:

the boolean

saveCOGNOSData

private java.lang.String saveCOGNOSData(java.lang.String tableName,
                              java.util.ArrayList data,
                              java.lang.String[] whiteListColumnNames,
                              java.lang.String[] whiteList,
                              java.util.Map columnsMap)

Saves COGNOS data into database table using whitelists 1000 at a time using batch function.

Parameters:

tableName - the table name

data - the data

whiteListColumnNames - the white list column names

whiteList - the white list

columnsMap - the columns map

Returns:

the string

saveDataToTable

private java.lang.String saveDataToTable(java.util.ArrayList dataToSave,
                               java.lang.String tableName,
                               java.util.Map columnsMap)

save data to table using columnsMap and batching 1000 at a time.

Parameters:

dataToSave - the data to save

tableName - the table name

columnsMap - the columns map

Returns:

the string

printMap

public static void printMap(java.util.Map mp)

util function to print key/value pairs in the mao.

Parameters:

mp - the mp

parseCOGNOSXLSXFile

private java.util.ArrayList parseCOGNOSXLSXFile(java.lang.String filename)

parses XLXS file and stores key/value pair in ArrayList.

Parameters:

filename - the filename

Returns:

the array list

getSqlType

private int getSqlType(java.lang.String columnName)

Budget Center = budget_center java.sql.Types.VARCHAR Sub-Budget Center = sub_budget_center java.sql.Types.VARCHAR Executing Budget Center = executing_budget_center java.sql.Types.VARCHAR Appropriation Year = appropriation_year java.sql.Types.DECIMAL Appropriation = appropriation java.sql.Types.VARCHAR Requisition ID = requisition_id java.sql.Types.VARCHAR PO Reference = po_reference java.sql.Types.VARCHAR ACR = acr java.sql.Types.VARCHAR Obligation Date = obligation_date java.sql.Types.DATE Obligated Amount = obligated_amount java.sql.Types.DECIMAL Expensed Amount = expensed_amount java.sql.Types.DECIMAL Expensed Date = expensed_date java.sql.Types.DATE Voucher ID = voucher_id java.sql.Types.VARCHAR

Parameters:

columnName - the column name

Returns:

the sql type

findDuplicateRow

private java.lang.Boolean findDuplicateRow(java.util.Map row,
                                 java.lang.String tableName,
                                 java.lang.String[] columnNames,
                                 java.lang.String[] columnValues)

look for duplicate row in the database table.

Parameters:

row - the row

tableName - the table name

columnNames - the column names

columnValues - the column values

Returns:

the boolean

setVarchar

private void setVarchar(java.sql.PreparedStatement stmt,
              int col,
              java.lang.String var,
              int type)
                 throws java.sql.SQLException

set type of column specified in the parameter.

Parameters:

stmt - the stmt

col - the col

var - the var

type - the type

Throws:

java.sql.SQLException - the SQL exception

main

public static void main(java.lang.String[] args)

The main method.

Parameters:

args - the arguments