public IHttpActionResult Add(User postModel)
{
var logonName = this.GetCurrentUserContext();
using (var dbContext = new BillingsDbContext())
{
var createdBy = dbContext.Users.FirstOrDefault(u => u.LogonName == logonName);
var user = dbContext.Users.FirstOrDefault(u => u.LogonName == postModel.LogonName);
if (user != null) return this.Ok(-1);
//this line doesn't make sense.
//postModel.CreatedById = user.Id;
postModel.CreatedBy = createdBy;
dbContext.Users.Add(postModel);
dbContext.SaveChanges();
return this.Ok(postModel.Id);
//user already exists?
}
}
public ActionResult Index(HttpPostedFileBase file)
{
try
{
var fileName = Path.GetFileName(file.FileName);
var physicalFileLocation = Server.MapPath("~/App_Data");
if (fileName != null)
{
var path = Path.Combine(physicalFileLocation, fileName);
file.SaveAs(path);
var excel = new ExcelQueryFactory(path);
#region Mappings
excel.AddMapping<ImportBill>(p => p.PeriodEndDate, "Period End Date");
excel.AddMapping<ImportBill>(p => p.Type, "Type");
excel.AddMapping<ImportBill>(p => p.OwingOffice, "Owning Office");
excel.AddMapping<ImportBill>(p => p.Currency, "Currency");
excel.AddMapping<ImportBill>(p => p.ServiceLine, "Service Line");
excel.AddMapping<ImportBill>(p => p.BstRefNumber, "BST Ref #");
excel.AddMapping<ImportBill>(p => p.TransactionDate, "Transaction Date");
excel.AddMapping<ImportBill>(p => p.ProjectNumber, "Proj #");
excel.AddMapping<ImportBill>(p => p.ProjectName, "Proj Name");
excel.AddMapping<ImportBill>(p => p.InvoiceNumber, "Invoice #");
excel.AddMapping<ImportBill>(p => p.Fee, "Fee");
excel.AddMapping<ImportBill>(p => p.Consultants, "Consultants");
excel.AddMapping<ImportBill>(p => p.Reimbursable, "Reimbursables");
excel.AddMapping<ImportBill>(p => p.Tax, "Tax");
excel.AddMapping<ImportBill>(p => p.ForeignWhTax, "Foreign W/H Tax");
excel.AddMapping<ImportBill>(p => p.Total, "Total");
excel.AddMapping<ImportBill>(p => p.Sl1Code, "SL1 EVC");
excel.AddMapping<ImportBill>(p => p.Sl1Name, "SL1 Name");
excel.AddMapping<ImportBill>(p => p.Sl1Percentage, "SL1 %");
excel.AddMapping<ImportBill>(p => p.Sl2Code, "SL2 EVC");
excel.AddMapping<ImportBill>(p => p.Sl2Name, "SL2 Name");
excel.AddMapping<ImportBill>(p => p.Sl2Percentage, "SL2 %");
excel.AddMapping<ImportBill>(p => p.Hawaii, "Hawaii");
excel.AddMapping<ImportBill>(p => p.InputDate, "Inpute Date");
excel.AddMapping<ImportBill>(p => p.GbpRate, "GBP Rate");
excel.AddMapping<ImportBill>(p => p.UsdRate, "UDS Rate"); //Column misspelled
excel.AddMapping<ImportBill>(p => p.Company, "Company");
excel.AddMapping<ImportBill>(p => p.ServiceLineOrg, "Service Line Org");
excel.AddMapping<ImportBill>(p => p.Country, "Project Country");
excel.AddMapping<ImportBill>(p => p.Region, "Project Region");
#endregion
var excelFileRecords = from c in excel.Worksheet<ImportBill>("Upload")
select c;
#region save data set to ms sql database
using (var dbContext = new BillingsDbContext())
{
var numberOfRecords = excelFileRecords.Count();
var counter = 0;
var logger = DependencyResolver.Current.GetService<ILogger>();
logger.Information("Number of records " + numberOfRecords);
foreach (var record in excelFileRecords)
{
try
{
var bill = new Bill();
var periodEndDate = DateTime.MinValue;
DateTime.TryParse(record.PeriodEndDate, out periodEndDate);
if (periodEndDate != DateTime.MinValue)
{
bill.PeriodEndDate = periodEndDate;
}
var transactionDate = DateTime.MinValue;
DateTime.TryParse(record.TransactionDate, out transactionDate);
if (transactionDate != DateTime.MinValue)
{
bill.TransactionDate = transactionDate;
}
var inputDate = DateTime.MinValue;
DateTime.TryParse(record.InputDate, out inputDate);
if (inputDate != DateTime.MinValue)
{
bill.InputDate = inputDate;
}
var fee = 0m;
decimal.TryParse(record.Fee, out fee);
if (fee != 0m)
{
bill.Fee = fee;
}
var consultants = 0m;
decimal.TryParse(record.Consultants, out consultants);
if (consultants != 0m)
{
bill.Consultants = consultants;
}
var reimbursables = 0m;
decimal.TryParse(record.Reimbursable, out reimbursables);
if (reimbursables != 0m)
{
bill.Reimbursable = reimbursables;
}
var tax = 0m;
decimal.TryParse(record.Tax, out tax);
if (tax != 0m)
{
bill.Tax = tax;
}
var foreignWhTax = 0m;
decimal.TryParse(record.ForeignWhTax, out foreignWhTax);
if (foreignWhTax != 0m)
{
bill.ForeignWhTax = foreignWhTax;
}
var total = 0m;
decimal.TryParse(record.Total, out total);
if (total != 0m)
{
bill.Total = total;
}
var gbpRate = decimal.MinValue;
decimal.TryParse(record.GbpRate, out gbpRate);
if (gbpRate != decimal.MinValue)
{
bill.GbpRate = gbpRate;
}
var usdRate = decimal.MinValue;
decimal.TryParse(record.UsdRate, out usdRate);
if (usdRate != decimal.MinValue)
{
bill.UsdRate = usdRate;
}
bill.Type = record.Type;
bill.OwingOffice = record.OwingOffice;
bill.Currency = record.Currency;
bill.ServiceLine = record.ServiceLine;
bill.BstRefNumber = record.BstRefNumber;
bill.ProjectNumber = record.ProjectNumber;
bill.ProjectName = record.ProjectName;
bill.InvoiceNumber = record.InvoiceNumber;
bill.Sl1Code = record.Sl1Code;
bill.Sl1Name = record.Sl1Name;
bill.Sl1Percentage = record.Sl1Percentage;
bill.Sl2Code = record.Sl2Code;
bill.Sl2Name = record.Sl2Name;
bill.Sl2Percentage = record.Sl2Percentage;
bill.Hawaii = record.Hawaii;
bill.Company = record.Company;
bill.ServiceLineOrg = record.ServiceLineOrg;
bill.Country = record.Country;
bill.Region = record.Region;
dbContext.Bills.Add(bill);
dbContext.SaveChanges();
counter++;
logger.Information("Record " + counter + " saved: " + record.PeriodEndDate + "," + record.TransactionDate + ", " +
record.ProjectNumber);
}
catch (Exception ex)
{
logger.Error(ex.Message, ex);
}
}
}
}
#endregion save data set to ms sql database
TempData.Add(Alerts.SUCCESS, "Data imported successfully.");
return View();
}
catch (Exception ex)
{
var logger = DependencyResolver.Current.GetService<ILogger>();
logger.Error(ex.Message, ex);
TempData.Add(Alerts.ERROR,
"Opps! Something went wrong when trying to upload file We've logged this, but it might work if you try it again.");
return View();
}
}
public IHttpActionResult Save(User postModel)
{
using (var dbContext = new BillingsDbContext())
{
if (postModel.Id > 0)
{
var user = dbContext.Users.FirstOrDefault(u => u.LogonName == postModel.LogonName);
user.FullName = postModel.FullName;
user.Role = postModel.Role;
user.IsActive = postModel.IsActive;
}
else
{
dbContext.Users.Add(postModel);
}
dbContext.SaveChanges();
return this.Ok(postModel.Id);
}
}