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 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 WbcImport() { var table = GetWbcDataTable(); var paramObj = View.CurrentObject as ImportForexRatesParam; using (var loader = new SqlServerLoader2((SqlConnection)paramObj.Session.Connection)) { loader.ColumnMappings.Add("ConversionDate", "ConversionDate"); loader.ColumnMappings.Add("FromCcyCode", "FromCcyCode"); loader.ColumnMappings.Add("Rate", "Rate"); //loader.TempTableName = "TmpForexRate1"; loader.CreateSql = @" CREATE TABLE {TempTable} ( ConversionDate datetime, FromCcyCode nvarchar(50), Rate [decimal](19, 6) );"; var convDate = table.Rows[0]["ConversionDate"]; loader.SqlStringReplacers.Add("ConvDate", string.Format("{0:yyyy-MM-dd}", convDate)); loader.PersistSql = @"DELETE FROM dbo.ForexRate WHERE ConversionDate = '{ConvDate}' INSERT INTO ForexRate ( Oid, ConversionDate, FromCurrency, ToCurrency, ConversionRate ) SELECT NEWID() AS Oid, '{ConvDate}' AS ConversionDate, (SELECT c1.Oid FROM Currency c1 WHERE c1.Name LIKE 'AUD' AND c1.GCRecord IS NULL) AS FromCurrency, c2.Oid AS ToCurrency, t1.Rate FROM {TempTable} t1 INNER JOIN Currency c2 ON c2.Name LIKE t1.FromCcyCode AND c2.GCRecord IS NULL WHERE t1.Rate <> 0.00 UNION ALL SELECT NEWID(), '{ConvDate}', c2.Oid AS FromCurrency, (SELECT c1.Oid FROM Currency c1 WHERE c1.Name LIKE 'AUD' AND c1.GCRecord IS NULL) AS ToCurrency, 1 / t1.Rate AS Rate FROM {TempTable} t1 INNER JOIN Currency c2 ON c2.Name LIKE t1.FromCcyCode AND c2.GCRecord IS NULL WHERE t1.Rate <> 0.00 "; var messagesText = loader.Execute(table); return(messagesText); } }
private void Import() { var paramObj = (ImportArOpenInvoicesParam)View.CurrentObject; var objSpace = (XPObjectSpace)ObjectSpace; using (var csvReader = DataObjectFactory.CreateCachedReaderFromCsv(paramObj.FilePath)) { var loader = new SqlServerLoader2((SqlConnection)objSpace.Connection); loader.SqlStringReplacers.Add("AsAtDate", string.Format("{0:yyyy-MM-dd}", paramObj.AsAtDate.Date)); loader.CreateSql = paramObj.CreateSql; loader.PersistSql = paramObj.PersistSql; var messagesText = loader.Execute(csvReader); new Xafology.ExpressApp.SystemModule.GenericMessageBox( messagesText, "Import Successful" ); } }
public void Import() { var paramObj = (ImportApInvoiceBalanceParam)View.CurrentObject; var objSpace = (XPObjectSpace)ObjectSpace; //var importer = new ApInvoiceBalanceImporter(objSpace); using (var csvReader = DataObjectFactory.CreateCachedReaderFromCsv(paramObj.FilePath)) { var loader = new SqlServerLoader2((SqlConnection)objSpace.Connection); loader.CreateSql = paramObj.CreateSql; loader.PersistSql = paramObj.PersistSql; var messagesText = loader.Execute(csvReader); new Xafology.ExpressApp.SystemModule.GenericMessageBox( messagesText, "Import Successful" ); } }
public void Import() { var paramObj = View.CurrentObject as ImportApPmtDistnParam; var objSpace = (XPObjectSpace)ObjectSpace; var csvColumns = objSpace.GetObjects <ImportApPmtDistnColumn>() .Where(x => x.ImportApPmtDistnParam == paramObj) .OrderBy(x => x.Ordinal); using (var csvReader = DataObjectFactory.CreateCachedReaderFromCsv(paramObj.FilePath)) { var ih = new ImportHelper(); foreach (var csvColumn in csvColumns) { string name = csvColumn.Name; Type type = ih.ParseType(csvColumn.TypeName); csvReader.Columns.Add(new LumenWorks.Framework.IO.Csv.Column() { Name = name, Type = type }); } var loader = new SqlServerLoader2((SqlConnection)objSpace.Connection); loader.CreateSql = paramObj.CreateSql; loader.PersistSql = paramObj.PersistSql; var messagesText = loader.Execute(csvReader); new Xafology.ExpressApp.SystemModule.GenericMessageBox( messagesText, "Import Successful" ); } }
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); } } }
private void ImportAction_Execute(object sender, SimpleActionExecuteEventArgs e) { string messagesText = string.Empty; var paramObj = (ImportTrialBalanceParam)View.CurrentObject; var conn = (SqlConnection)((XPObjectSpace)ObjectSpace).Connection; using (var loader = new SqlServerLoader2(conn)) { loader.SqlStringReplacers.Add("FromDate", string.Format("{0:yyyy-MM-dd}", paramObj.FromDate.Date)); loader.SqlStringReplacers.Add("ToDate", string.Format("{0:yyyy-MM-dd}", paramObj.ToDate.Date)); loader.ColumnMappings.Add("Company", "Company"); loader.ColumnMappings.Add("Account", "Account"); loader.ColumnMappings.Add("BS or P&L", "BS or PL"); loader.ColumnMappings.Add("Account Class", "Account Class"); loader.ColumnMappings.Add("Account Desc", "Account Desc"); loader.ColumnMappings.Add("Period Name", "Period Name"); loader.ColumnMappings.Add("AUD Beg Bal", "AUD Beg Bal"); loader.ColumnMappings.Add("AUD PTD Dr", "AUD PTD Dr"); loader.ColumnMappings.Add("AUD PTD Cr", "AUD PTD Cr"); loader.ColumnMappings.Add("AUD PTD Net", "AUD PTD Net"); loader.ColumnMappings.Add("AUD End Bal", "AUD End Bal"); loader.CreateSql = @"CREATE TABLE {TempTable} ( [Company] [nvarchar](10) NULL, [Account] [nvarchar](10) NULL, [BS or PL] [nvarchar](10) NULL, [Account Class] [nvarchar](50) NULL, [Account Desc] [nvarchar](255) NULL, [Period Name] [nvarchar](10) NULL, [AUD Beg Bal] [money] NULL, [AUD PTD Dr] [money] NULL, [AUD PTD Cr] [money] NULL, [AUD PTD Net] [money] NULL, [AUD End Bal] [money] NULL )"; loader.PersistSql = @"DELETE FROM [TB_TrialBalance] WHERE DateKey BETWEEN '{FromDate}' AND '{ToDate}'; INSERT INTO [TB_TrialBalance] ( [Oid], [Company], [Account], [BS or PL], [Account Class], [Account Desc], [Period Name], [AUD Beg Bal], [AUD PTD Dr], [AUD PTD Cr], [AUD PTD Net], [AUD End Bal], [DateKey] ) SELECT NEWID() AS Oid, [Company], [Account], [BS or PL], [Account Class], [Account Desc], [Period Name], [AUD Beg Bal], [AUD PTD Dr], [AUD PTD Cr], [AUD PTD Net], [AUD End Bal], CAST ( '1-' + [Period Name] AS datetime ) AS DateKey FROM {TempTable}"; var sourceReader = DataObjectFactory.CreateCachedReaderFromCsv(paramObj.FilePath); messagesText = loader.Execute(sourceReader); } new Xafology.ExpressApp.SystemModule.GenericMessageBox( messagesText, "Import Successful" ); }
private void ImportAction_Execute(object sender, SimpleActionExecuteEventArgs e) { string messagesText = string.Empty; var paramObj = (ImportArBalanceParam)View.CurrentObject; var conn = (SqlConnection)((XPObjectSpace)ObjectSpace).Connection; using (var loader = new SqlServerLoader2(conn)) { loader.ColumnMappings.Add("Customer", "Customer"); loader.ColumnMappings.Add("Customer Number", "Customer Number"); loader.ColumnMappings.Add("Collector", "Collector"); loader.ColumnMappings.Add("Outstanding Amount", "Outstanding Amount"); loader.ColumnMappings.Add("Current", "Current"); loader.ColumnMappings.Add("1-30 Days", "1-30 Days"); loader.ColumnMappings.Add("31-60 Days", "31-60 Days"); loader.ColumnMappings.Add("61-90 Days", "61-90 Days"); loader.ColumnMappings.Add("91-180 Days", "91-180 Days"); loader.ColumnMappings.Add("181-360 Days", "181-360 Days"); loader.ColumnMappings.Add("361+ Days", "361+ Days"); loader.ColumnMappings.Add("Account", "Account"); loader.ColumnMappings.Add("Report Date", "Report Date"); loader.CreateSql = @"CREATE TABLE {TempTable} ( [Customer] nvarchar(255), [Customer Number] nvarchar(255), [Collector] nvarchar(255), [Outstanding Amount] float, [Current] float, [1-30 Days] float, [31-60 Days] float, [61-90 Days] float, [91-180 Days] float, [181-360 Days] float, [361+ Days] float, [Account] nvarchar(255), [Report Date] datetime )"; loader.PersistSql = @"INSERT INTO VHAFinance.dbo.ArGlBalance ( [Customer], [Customer Number], [Collector], [Outstanding Amount], [Current], [1-30 Days], [31-60 Days], [61-90 Days], [91-180 Days], [181-360 Days], [361+ Days], [Account], [Report Date] ) SELECT [Customer], [Customer Number], [Collector], [Outstanding Amount], [Current], [1-30 Days], [31-60 Days], [61-90 Days], [91-180 Days], [181-360 Days], [361+ Days], [Account], [Report Date] FROM {TempTable}"; var sourceTable = DataObjectFactory.CreateTableFromExcelXml(paramObj.FilePath, "Upload"); messagesText = loader.Execute(sourceTable); } new Xafology.ExpressApp.SystemModule.GenericMessageBox( messagesText, "Import Successful" ); }