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