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