public string ExecuteHsbc(ImportBankStmtParamItem paramObj)
{
var connection = (SqlConnection)paramObj.Session.Connection;
using (var loader = new SqlServerLoader2((SqlConnection)connection))
{
loader.ColumnMappings.Add("Account number", "BankAccountNumber");
loader.ColumnMappings.Add("Value date (dd/mm/yyyy)", "TranDate");
loader.ColumnMappings.Add("TRN Type", "TranType");
loader.ColumnMappings.Add("Customer reference", "TranRef");
loader.ColumnMappings.Add("Additional narrative", "TranDescription");
loader.ColumnMappings.Add("Credit amount", "Credit");
loader.ColumnMappings.Add("Debit amount", "Debit");
loader.CreateSql = CreateSql + @"
ALTER TABLE {TempTable} ADD [Debit] money, [Credit] money;";
loader.PersistSql = @"UPDATE {TempTable} SET
TranAmount = COALESCE([Credit],0) + COALESCE([Debit],0)
" + PersistSql;
using (var sourceTable = DataObjectFactory.CreateTableFromExcelXml(paramObj.FilePath, "Data"))
{
var messagesText = loader.Execute(sourceTable);
return(messagesText);
}
}
}
public string ExecuteWbc(ImportBankStmtParamItem paramObj)
{
var session = paramObj.Session;
using (var loader = new SqlServerLoader2((SqlConnection)session.Connection))
{
loader.ColumnMappings.Add("TRAN_DATE", "TranDateText");
loader.ColumnMappings.Add("ACCOUNT_NO", "BankAccountNumber");
loader.ColumnMappings.Add("AMOUNT", "TranAmount");
loader.ColumnMappings.Add("NARRATIVE", "TranDescription");
loader.ColumnMappings.Add("TRAN_CODE", "TranCode");
loader.ColumnMappings.Add("SERIAL", "TranRef");
loader.CreateSql = CreateSql + @"
ALTER TABLE {TempTable} ADD [TranDateText] nvarchar(50);";
loader.PersistSql = @"UPDATE {TempTable} SET
TranDate = CAST(TranDateText AS date);
" + PersistSql;
using (var csvReader = DataObjectFactory.CreateReaderFromCsv(paramObj.FilePath, true))
{
var messagesText = loader.Execute(csvReader);
return(messagesText);
}
}
}
public string ExecuteCba(ImportBankStmtParamItem paramObj)
{
var session = paramObj.Session;
using (var loader = new SqlServerLoader2((SqlConnection)session.Connection))
{
loader.ColumnMappings.Add("Process date", "TranDate");
loader.ColumnMappings.Add("Debit", "Debit");
loader.ColumnMappings.Add("Credit", "Credit");
loader.ColumnMappings.Add("Description", "TranDescription");
loader.CreateSql = CreateSql + @"
ALTER TABLE {TempTable} ADD [Debit] money, [Credit] money;";
loader.PersistSql = @"UPDATE {TempTable} SET
TranAmount = COALESCE([Credit],0) - COALESCE([Debit],0),
BankAccountNumber = '{Account}'" + PersistSql;
loader.SqlStringReplacers.Add("Account", paramObj.Account == null ? "" : paramObj.Account.Name);
using (var csvReader = DataObjectFactory.CreateReaderFromCsv(paramObj.FilePath, true))
{
var messagesText = loader.Execute(csvReader);
return(messagesText);
}
}
}
public string ExecuteAnz(ImportBankStmtParamItem paramObj)
{
var session = paramObj.Session;
using (var loader = new SqlServerLoader2((SqlConnection)session.Connection))
{
loader.ColumnMappings.Add("TranDate", "TranDate");
loader.ColumnMappings.Add("BankAccountNumber", "BankAccountNumber");
loader.ColumnMappings.Add("TranType", "TranType");
loader.ColumnMappings.Add("TranRef", "TranRef");
loader.ColumnMappings.Add("TranAmount", "TranAmount");
loader.ColumnMappings.Add("TranDescription", "TranDescription");
loader.ColumnMappings.Add("TranCode", "TranCode");
loader.CreateSql = CreateSql;
loader.PersistSql = PersistSql;
using (var csvReader = DataObjectFactory.CreateReaderFromCsv(paramObj.FilePath, false))
{
csvReader.Columns = new List <LumenWorks.Framework.IO.Csv.Column>
{
new LumenWorks.Framework.IO.Csv.Column {
Name = "TranDate", Type = typeof(DateTime)
},
new LumenWorks.Framework.IO.Csv.Column {
Name = "BankAccountNumber", Type = typeof(string)
},
new LumenWorks.Framework.IO.Csv.Column {
Name = "Column_2", Type = typeof(string)
},
new LumenWorks.Framework.IO.Csv.Column {
Name = "Column_3", Type = typeof(string)
},
new LumenWorks.Framework.IO.Csv.Column {
Name = "Column_4", Type = typeof(string)
},
new LumenWorks.Framework.IO.Csv.Column {
Name = "Currency", Type = typeof(string)
},
new LumenWorks.Framework.IO.Csv.Column {
Name = "Column_6", Type = typeof(string)
},
new LumenWorks.Framework.IO.Csv.Column {
Name = "TranType", Type = typeof(string)
},
new LumenWorks.Framework.IO.Csv.Column {
Name = "TranRef", Type = typeof(string)
},
new LumenWorks.Framework.IO.Csv.Column {
Name = "TranAmount", Type = typeof(decimal)
},
new LumenWorks.Framework.IO.Csv.Column {
Name = "TranDescription", Type = typeof(string)
},
new LumenWorks.Framework.IO.Csv.Column {
Name = "Column_11", Type = typeof(string)
},
new LumenWorks.Framework.IO.Csv.Column {
Name = "Column_12", Type = typeof(string)
},
new LumenWorks.Framework.IO.Csv.Column {
Name = "TranCode", Type = typeof(string)
},
new LumenWorks.Framework.IO.Csv.Column {
Name = "Column_14", Type = typeof(string)
}
};
var messagesText = loader.Execute(csvReader);
return(messagesText);
}
}
}
