public void DBContextTest2()
{
DirectoryFile intouchFile1 = new DirectoryFile("abc", "abc", Model.Enum.FileStatus.ReadFromFileSystem);
intouchFile1.FileContent = FileUtility.ReadFile("..\\..\\TestFiles\\bank_20160517220202_test2.csv");
DirectoryFile intouchFile2 = new DirectoryFile("abc", "abc", Model.Enum.FileStatus.ReadFromFileSystem);
intouchFile2.FileContent = FileUtility.ReadFile("..\\..\\TestFiles\\bank_20160517220202_test3.csv");
using (BusinessAccountingEntities context = new BusinessAccountingEntities())
{
context.Database.ExecuteSqlCommand("delete from ODS.BankIntouch");
context.SaveChanges();
}
ApplicationController.ProcessBankIntouch(intouchFile1);
ApplicationController.ProcessBankIntouch(intouchFile2);
int i = 0;
using (BusinessAccountingEntities context = new BusinessAccountingEntities())
{
var query = (from row in context.BankIntouch
select row).ToList();
i = query.Count;
}
Assert.AreEqual(4, i);
}
/// <summary>
/// Process all Intouch Bank CSVs
/// </summary>
private static void ProcessBankIntouchData(DirectoryFile directoryFile)
{
List <BankIntouch> BankIntouchODS = new List <BankIntouch>();
List <BankIntouch> BankIntouchNew = new List <BankIntouch>();
int newRows = 0;
try
{
logger.Info($"Started processing file [{directoryFile.FileName}]");
List <string[]> BankIntouchFileString = new List <string[]>();
List <BankIntouch> BankIntouchFile = new List <BankIntouch>();
FileUtility.CSVStringReader(directoryFile.FileContent, ",", BankIntouchFileString, 9, true);
foreach (string[] bankIntouchString in BankIntouchFileString)
{
BankIntouch BankIntouch = new BankIntouch
{
Date = Convert.ToDateTime(bankIntouchString[0]),
BankDescription = bankIntouchString[1],
AccountNumber = bankIntouchString[2],
EmployeeName = bankIntouchString[3],
Description = bankIntouchString[4],
UserComments = bankIntouchString[5],
Amount = Convert.ToDecimal(bankIntouchString[6]),
Net = Convert.ToDecimal(bankIntouchString[7]),
VAT = Convert.ToDecimal(bankIntouchString[8])
};
BankIntouchFile.Add(BankIntouch);
}
using (BusinessAccountingEntities context = new BusinessAccountingEntities())
{
var query = (from row in context.BankIntouch
select row).ToList();
BankIntouchODS = query;
foreach (BankIntouch bankIntouch in BankIntouchFile)
{
if (!BankIntouchODS.Contains(bankIntouch) && !String.IsNullOrEmpty(bankIntouch.Description))
{
BankIntouchNew.Add(bankIntouch);
newRows++;
}
}
foreach (BankIntouch bankIntouch in BankIntouchNew)
{
context.BankIntouch.Add(bankIntouch);
}
context.SaveChanges();
logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]");
logger.Info($"Finished processing file [{directoryFile.FileName}]");
}
}
catch (Exception e)
{
logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]");
throw;
}
}
/// <summary>
/// Process all Invoice CSVs
/// </summary>
public static void ProcessInvoiceData(DirectoryFile directoryFile)
{
List <Invoice> InvoiceODS = new List <Invoice>();
List <Invoice> InvoiceNew = new List <Invoice>();
int newRows = 0;
try
{
logger.Info($"Started processing file [{directoryFile.FileName}]");
List <string[]> InvoiceFileString = new List <string[]>();
List <Invoice> InvoiceFile = new List <Invoice>();
FileUtility.CSVStringReader(directoryFile.FileContent, ",", InvoiceFileString, 9, true);
foreach (string[] invoiceString in InvoiceFileString)
{
Invoice Invoice = new Invoice
{
Date = Convert.ToDateTime(invoiceString[0]),
Description = invoiceString[1],
ContractReference = invoiceString[2],
ClientName = invoiceString[3],
InvoiceReference = invoiceString[4],
Total = Convert.ToDecimal(invoiceString[5]),
VAT = Convert.ToDecimal(invoiceString[6]),
Net = Convert.ToDecimal(invoiceString[7])
};
InvoiceFile.Add(Invoice);
}
using (BusinessAccountingEntities context = new BusinessAccountingEntities())
{
var query = (from row in context.Invoice
select row).ToList();
InvoiceODS = query;
foreach (Invoice invoice in InvoiceFile)
{
if (!InvoiceODS.Contains(invoice))
{
InvoiceNew.Add(invoice);
newRows++;
}
}
foreach (Invoice invoice in InvoiceNew)
{
context.Invoice.Add(invoice);
}
context.SaveChanges();
logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]");
logger.Info($"Finished processing file [{directoryFile.FileName}]");
}
}
catch (Exception e)
{
logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]");
throw;
}
}
/// <summary>
/// Process all Intouch ExpenseEdit CSVs
/// </summary>
public static void ProcessExpenseEditData(DirectoryFile directoryFile)
{
List <ExpenseEdit> ExpenseEditODS = new List <ExpenseEdit>();
List <ExpenseEdit> ExpenseEditNew = new List <ExpenseEdit>();
int newRows = 0;
try
{
logger.Info($"Started processing file [{directoryFile.FileName}]");
List <string[]> ExpenseEditFileString = new List <string[]>();
List <ExpenseEdit> ExpenseEditFile = new List <ExpenseEdit>();
FileUtility.CSVStringReader(directoryFile.FileContent, ",", ExpenseEditFileString, 9, true);
foreach (string[] expenseEditString in ExpenseEditFileString)
{
ExpenseEdit ExpenseEdit = new ExpenseEdit
{
Date = Convert.ToDateTime(expenseEditString[0]),
Description = expenseEditString[1],
Detail = expenseEditString[2],
Total = Convert.ToDecimal(expenseEditString[3]),
EffectiveDate = Convert.ToDateTime(expenseEditString[4]),
EffectiveDescription = expenseEditString[5]
};
ExpenseEditFile.Add(ExpenseEdit);
}
using (BusinessAccountingEntities context = new BusinessAccountingEntities())
{
var query = (from row in context.ExpenseEdit
select row).ToList();
ExpenseEditODS = query;
foreach (ExpenseEdit expenseEdit in ExpenseEditFile)
{
if (!ExpenseEditODS.Contains(expenseEdit))
{
ExpenseEditNew.Add(expenseEdit);
newRows++;
}
}
foreach (ExpenseEdit expenseEdit in ExpenseEditNew)
{
context.ExpenseEdit.Add(expenseEdit);
}
context.SaveChanges();
logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]");
logger.Info($"Finished processing file [{directoryFile.FileName}]");
}
}
catch (Exception e)
{
logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]");
throw;
}
}
/// <summary>
/// Process all Intouch Bank CSVs
/// </summary>
public static void ProcessBankData(DirectoryFile directoryFile)
{
List <Bank> BankODS = new List <Bank>();
List <Bank> BankNew = new List <Bank>();
int newRows = 0;
try
{
logger.Info($"Started processing file [{directoryFile.FileName}]");
List <string[]> BankFileString = new List <string[]>();
List <Bank> BankFile = new List <Bank>();
FileUtility.CSVStringReader(directoryFile.FileContent, ",", BankFileString, 9, true);
foreach (string[] bankString in BankFileString)
{
Bank Bank = new Bank
{
FITID = Convert.ToInt64(bankString[0]),
PostedDate = Convert.ToDateTime(bankString[1]),
Amount = Convert.ToDecimal(bankString[2]),
PayeeFriendlyName = bankString[3],
Activity = bankString[4],
TransferType = bankString[5]
};
BankFile.Add(Bank);
}
using (BusinessAccountingEntities context = new BusinessAccountingEntities())
{
var query = (from row in context.Bank
select row).ToList();
BankODS = query;
foreach (Bank bank in BankFile)
{
if (!BankODS.Contains(bank))
{
BankNew.Add(bank);
newRows++;
}
}
foreach (Bank bank in BankNew)
{
context.Bank.Add(bank);
}
context.SaveChanges();
logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]");
logger.Info($"Finished processing file [{directoryFile.FileName}]");
}
}
catch (Exception e)
{
logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]");
throw;
}
}
/// <summary>
/// Process all Bank Cater Allen XMLs
/// </summary>
public static void ProcessBankCaterAllenData(DirectoryFile directoryFile)
{
List <BankCaterAllen> BankCaterAllenODS = new List <BankCaterAllen>();
List <BankCaterAllen> BankCaterAllenNew = new List <BankCaterAllen>();
int newRows = 0;
try
{
logger.Info($"Started processing file [{directoryFile.FileName}]");
XDocument xdoc1 = XDocument.Parse(directoryFile.FileContent);
var query = from txn in xdoc1.Descendants("STMTTRN")
select new BankCaterAllen
{
FITID = Convert.ToInt64(txn.Element("FITID").Value),
TxnType = txn.Element("TRNTYPE").Value,
PostedDate = Convert.ToDateTime(
txn.Element("DTPOSTED").Value.Substring(0, 4)
+ "-" + txn.Element("DTPOSTED").Value.Substring(4, 2)
+ "-" + txn.Element("DTPOSTED").Value.Substring(6, 2)),
Amount = Convert.ToDecimal(txn.Element("TRNAMT").Value),
Name = txn.Element("NAME").Value
};
List <BankCaterAllen> BankCaterAllenFile = query.ToList();
using (BusinessAccountingEntities context = new BusinessAccountingEntities())
{
var dbquery = (from row in context.BankCaterAllen
select row).ToList();
BankCaterAllenODS = dbquery;
foreach (BankCaterAllen bankCaterAllen in BankCaterAllenFile)
{
if (!BankCaterAllenODS.Contains(bankCaterAllen))
{
BankCaterAllenNew.Add(bankCaterAllen);
newRows++;
}
}
foreach (BankCaterAllen bankCaterAllen in BankCaterAllenNew)
{
context.BankCaterAllen.Add(bankCaterAllen);
}
context.SaveChanges();
logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]");
logger.Info($"Finished processing file [{directoryFile.FileName}]");
}
}
catch (Exception e)
{
logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]");
throw;
}
}
/// <summary>
/// Process all Intouch BankEdit CSVs
/// </summary>
public static void ProcessBankEditData(DirectoryFile directoryFile)
{
List <BankEdit> BankEditODS = new List <BankEdit>();
List <BankEdit> BankEditNew = new List <BankEdit>();
int newRows = 0;
try
{
logger.Info($"Started processing file [{directoryFile.FileName}]");
List <string[]> BankEditFileString = new List <string[]>();
List <BankEdit> BankEditFile = new List <BankEdit>();
FileUtility.CSVStringReader(directoryFile.FileContent, ",", BankEditFileString, 9, true);
foreach (string[] bankEditString in BankEditFileString)
{
BankEdit BankEdit = new BankEdit
{
FITID = Convert.ToInt64(bankEditString[0]),
EffectiveDate = Convert.ToDateTime(bankEditString[1]),
EffectiveAmount = Convert.ToDecimal(bankEditString[2])
};
BankEditFile.Add(BankEdit);
}
using (BusinessAccountingEntities context = new BusinessAccountingEntities())
{
var query = (from row in context.BankEdit
select row).ToList();
BankEditODS = query;
foreach (BankEdit bankEdit in BankEditFile)
{
if (!BankEditODS.Contains(bankEdit))
{
BankEditNew.Add(bankEdit);
newRows++;
}
}
foreach (BankEdit bankEdit in BankEditNew)
{
context.BankEdit.Add(bankEdit);
}
context.SaveChanges();
logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]");
logger.Info($"Finished processing file [{directoryFile.FileName}]");
}
}
catch (Exception e)
{
logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]");
throw;
}
}
/// <summary>
/// Process Salary CSV
/// </summary>
public static void ProcessSalaryData(DirectoryFile directoryFile)
{
List <Salary> SalaryDIM = new List <Salary>();
List <Salary> SalaryNew = new List <Salary>();
int newRows = 0;
try
{
logger.Info($"Started processing file [{directoryFile.FileName}]");
List <string[]> SalaryString = new List <string[]>();
List <Salary> SalaryFile = new List <Salary>();
FileUtility.CSVStringReader(directoryFile.FileContent, ",", SalaryString, 13, true);
foreach (string[] salaryString in SalaryString)
{
Salary salary = new Salary
{
SalaryDate = Convert.ToDateTime(salaryString[1]),
TaxCode = salaryString[2],
TotalPay = Convert.ToDecimal(salaryString[3]),
TaxDeducted = Convert.ToDecimal(salaryString[4]),
EmployeeNIC = Convert.ToDecimal(salaryString[5]),
EmployeePension = Convert.ToDecimal(salaryString[6]),
SickPay = Convert.ToDecimal(salaryString[7]),
ParentingPay = Convert.ToDecimal(salaryString[8]),
StudentLoan = Convert.ToDecimal(salaryString[9]),
NetPay = Convert.ToDecimal(salaryString[10]),
EmployerNIC = Convert.ToDecimal(salaryString[11]),
EmployerPension = Convert.ToDecimal(salaryString[12])
};
SalaryFile.Add(salary);
}
using (BusinessAccountingEntities context = new BusinessAccountingEntities())
{
context.Database.ExecuteSqlCommand("delete from DIM.Salary");
foreach (Salary salary in SalaryFile)
{
context.Salary.Add(salary);
}
context.SaveChanges();
logger.Info($"Added [{newRows.ToString()}] rows to DIM from file [{directoryFile.FileName}]");
logger.Info($"Finished processing file [{directoryFile.FileName}]");
}
}
catch (Exception e)
{
logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]");
throw;
}
}
/// <summary>
/// Process DividendThreshold CSV
/// </summary>
public static void ProcessDividendThresholdData(DirectoryFile directoryFile)
{
List <DividendThreshold> DividendThresholdDIM = new List <DividendThreshold>();
List <DividendThreshold> DividendThresholdNew = new List <DividendThreshold>();
int newRows = 0;
try
{
logger.Info($"Started processing file [{directoryFile.FileName}]");
List <string[]> DividendThresholdString = new List <string[]>();
List <DividendThreshold> DividendThresholdFile = new List <DividendThreshold>();
FileUtility.CSVStringReader(directoryFile.FileContent, ",", DividendThresholdString, 7, true);
foreach (string[] dividendThresholdString in DividendThresholdString)
{
DividendThreshold dividendThreshold = new DividendThreshold
{
ThresholdID = Convert.ToInt32(dividendThresholdString[0]),
FinYear = dividendThresholdString[1],
MinAmount = Convert.ToDecimal(dividendThresholdString[2]),
MaxAmount = Convert.ToDecimal(dividendThresholdString[3]),
TaxPct = Convert.ToDecimal(dividendThresholdString[4]),
IsOptimumBand = Convert.ToInt16(dividendThresholdString[5]),
NetAmountCarryOver = Convert.ToDecimal(dividendThresholdString[6])
};
DividendThresholdFile.Add(dividendThreshold);
}
using (BusinessAccountingEntities context = new BusinessAccountingEntities())
{
context.Database.ExecuteSqlCommand("delete from DIM.DividendThreshold");
foreach (Model.DividendThreshold dividendThreshold in DividendThresholdFile)
{
context.DividendThreshold.Add(dividendThreshold);
}
context.SaveChanges();
logger.Info($"Added [{newRows.ToString()}] rows to DIM from file [{directoryFile.FileName}]");
logger.Info($"Finished processing file [{directoryFile.FileName}]");
}
}
catch (Exception e)
{
logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]");
throw;
}
}
/// <summary>
/// Process PayeeMapping CSV
/// </summary>
public static void ProcessPayeeMappingData(DirectoryFile directoryFile)
{
List <PayeeMapping> PayeeMappingDIM = new List <PayeeMapping>();
List <PayeeMapping> PayeeMappingNew = new List <PayeeMapping>();
int newRows = 0;
try
{
logger.Info($"Started processing file [{directoryFile.FileName}]");
List <string[]> PayeeMappingString = new List <string[]>();
List <PayeeMapping> PayeeMappingFile = new List <PayeeMapping>();
FileUtility.CSVStringReader(directoryFile.FileContent, ",", PayeeMappingString, 9, true);
foreach (string[] payeeMappingString in PayeeMappingString)
{
PayeeMapping payeeMapping = new PayeeMapping
{
ID = Convert.ToInt32(payeeMappingString[0]),
PayeeID = Convert.ToInt32(payeeMappingString[1]),
PayeeFriendlyName = payeeMappingString[2],
PayeeSourceName = payeeMappingString[3],
PayeeSource = payeeMappingString[4]
};
PayeeMappingFile.Add(payeeMapping);
}
using (BusinessAccountingEntities context = new BusinessAccountingEntities())
{
context.Database.ExecuteSqlCommand("delete from ODS.PayeeMapping");
foreach (PayeeMapping payeeMapping in PayeeMappingFile)
{
context.PayeeMapping.Add(payeeMapping);
}
context.SaveChanges();
logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]");
logger.Info($"Finished processing file [{directoryFile.FileName}]");
}
}
catch (Exception e)
{
logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]");
throw;
}
}
/// <summary>
/// Process TransactionType CSV
/// </summary>
public static void ProcessTransactionTypeData(DirectoryFile directoryFile)
{
List <Model.TransactionType> TransactionTypeDIM = new List <Model.TransactionType>();
List <Model.TransactionType> TransactionTypeNew = new List <Model.TransactionType>();
int newRows = 0;
try
{
logger.Info($"Started processing file [{directoryFile.FileName}]");
List <string[]> TransactionTypeString = new List <string[]>();
List <Model.TransactionType> TransactionTypeFile = new List <Model.TransactionType>();
FileUtility.CSVStringReader(directoryFile.FileContent, ",", TransactionTypeString, 9, true);
foreach (string[] transactionTypeString in TransactionTypeString)
{
Model.TransactionType transactionType = new Model.TransactionType
{
TransactionTypeID = Convert.ToInt32(transactionTypeString[0]),
TransactionCategory = transactionTypeString[1],
TransactionSubCategory = transactionTypeString[2],
Activity = transactionTypeString[3],
};
TransactionTypeFile.Add(transactionType);
}
using (BusinessAccountingEntities context = new BusinessAccountingEntities())
{
context.Database.ExecuteSqlCommand("delete from DIM.TransactionType");
foreach (Model.TransactionType transactionType in TransactionTypeFile)
{
context.TransactionType.Add(transactionType);
}
context.SaveChanges();
logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]");
logger.Info($"Finished processing file [{directoryFile.FileName}]");
}
}
catch (Exception e)
{
logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]");
throw;
}
}
/// <summary>
/// Take in a list of transactions and add them to Entity if not already exists
/// </summary>
private static void AddTransaction(ApplicationSettings application, List <Transaction> TransactionList, string source)
{
List <string> TransactionFactSourceID = new List <string>();
List <Transaction> TransactionNew = new List <Transaction>();
int newRows = 0;
try
{
logger.Info($"Started processing [{source}] transaction rows");
using (BusinessAccountingEntities context = new BusinessAccountingEntities())
{
var queryTransaction = (from row in context.Transaction
where row.TransactionSource == source
select row.SourceID).ToList();
TransactionFactSourceID = queryTransaction;
foreach (Transaction transaction in TransactionList)
{
if (!TransactionFactSourceID.Contains(transaction.SourceID))
{
TransactionNew.Add(transaction);
}
newRows++;
}
foreach (Transaction transaction in TransactionNew)
{
context.Transaction.Add(transaction);
}
context.SaveChanges();
logger.Info($"Added [{newRows.ToString()}] rows from [{source}] to Fact");
logger.Info($"Finished processing [{source}] transaction rows");
}
}
catch (Exception e)
{
logger.Error($"Exception while trying to process [{source}] transaction rows. Exception [{e.ToString()}]");
throw;
}
}
/// <summary>
/// Static method to process Date
/// </summary>
public static void LoadDates(ApplicationSettings application)
{
DateTime startDate;
DateTime endDate;
int newRows = 0;
try
{
logger.Info($"Started processing Date");
startDate = application.StartDate;
endDate = application.EndDate;
using (BusinessAccountingEntities context = new BusinessAccountingEntities())
{
context.Database.ExecuteSqlCommand("delete from DIM.Date");
while (DateTime.Compare(startDate, endDate) < 0)
{
Date newDateRow = new Date
{
LongDate = startDate
};
newRows++;
context.Date.Add(newDateRow);
startDate = startDate.AddDays(1);
}
context.SaveChanges();
logger.Info($"Added [{newRows.ToString()}] rows to Date");
logger.Info($"Finished processing Date");
}
}
catch (Exception e)
{
logger.Error($"Exception while trying to process Date. Exception [{e.ToString()}]");
throw;
}
}