public int ImportFromCsv(string csvFile, DateTime reportDate, bool isCompleted, DateTime?payoutStartDate)
{
int errorCount = 0;
int payoutCount = 0;
int reservationCount = 0;
int resolutionCount = 0;
string channel = "Airbnb";
string account = GetAccountFromFilename(csvFile);
string inputSource = string.Format("{0} {1}", reportDate.ToString("yyyy-MM-dd"), GetInputSourceFromFilePath(csvFile));
if (DuplicateInputSource(inputSource))
{
DeleteFileIfAllowed(csvFile);
return(0);
}
if (!File.Exists(csvFile))
{
var inputError = new InputError
{
InputSource = inputSource,
Row = 0,
Section = "Input File Check",
Message = string.Format("Input file '{0}' does not exist.", csvFile),
OriginalText = csvFile,
CreatedTime = DateTime.UtcNow
};
_context.InputErrors.Add(inputError);
_context.SaveChanges(); // save errors
return(-1000000); // large number of errors
}
using (StreamReader sr = new StreamReader(csvFile))
{
int totalCols = 14;
int startRow = 2; // starting row for reservation data
int payoutDateCol = 0;
int typeCol = 1;
int confirmationCodeCol = 2;
int checkinDateCol = 3;
int nightCol = 4;
int guestCol = 5;
int listingCol = 6;
int detailCol = 7;
int referenceCol = 8;
int currencyCol = 9;
int amountCol = 10;
int payoutCol = 11;
bool isOwnerRow = false;
bool isRowError = false;
List <InputError> errorRows = new List <InputError>();
char delimiter = (char)0;
OwnerPayout payout = null;
List <Reservation> reservations = null;
List <Resolution> resolutions = null;
DateTime today = DateTime.UtcNow;
var propertyProvider = new PropertyProvider(_context);
int row = 0;
while (!sr.EndOfStream)
{
string line = sr.ReadLine();
row++;
if (row < startRow)
{
continue; // first row is header
}
if (delimiter == (char)0)
{
delimiter = (line.Count(x => x == '\t') > 2 ? '\t' : ',');
}
string[] columns = ParseLine(line, delimiter, totalCols);
if (columns.Count() != totalCols)
{
errorRows.Add(new InputError
{
InputSource = inputSource,
Row = row,
Section = "Parse",
Message = string.Format("The total number of columns {0:d} does not match {1:d}", columns.Count(), totalCols),
OriginalText = line,
CreatedTime = DateTime.UtcNow
});
}
try
{
// exit loop if there is no transaction date on a row
if (isCompleted && columns[payoutDateCol] == null)
{
if (payout != null && isOwnerRow && !isRowError)
{
QueuePayout(payout, reservations, resolutions);
payoutCount++;
payout = null; // start a new input section cycle
}
break;
}
string type = columns[typeCol];
string confirmationCode = columns[confirmationCodeCol];
var payoutType = type.StartsWith("Payout") ? PayoutType.Payout :
(type.StartsWith("Reservation") ? PayoutType.Reservation :
(type.StartsWith("Resolution") ? PayoutType.Resolution : PayoutType.Other));
if (payoutType == PayoutType.Payout)
{
// payout row following a reservation or resolution row start an input cycle, we queue up the payout
if (isCompleted && payout != null && !isOwnerRow && !isRowError)
{
if (payoutStartDate == null || payout.PayoutDate.Value.Date > payoutStartDate.Value.Date)
{
QueuePayout(payout, reservations, resolutions);
payoutCount++;
}
else
{
sr.Close();
break; // payout date comes in in ascending order; so we can skip dates before payoutStartDate
}
payout = null; // start a new input section cycle
}
// owner row following another owner row is a payout split, we add the amount up
else if (isCompleted && payout != null && isOwnerRow && !isRowError)
{
payout.PayoutAmount += GetSafeNumber(columns[payoutCol]);
continue;
}
isOwnerRow = true;
isRowError = false;
payout = new OwnerPayout();
reservations = new List <Reservation>();
resolutions = new List <Resolution>();
payout.PayoutDate = ConversionHelper.EnsureUtcDate(GetSafeDate(columns[payoutDateCol]));
payout.AccountNumber = GetAccountNumber(columns[detailCol]);
payout.Source = account;
payout.PayoutAmount = GetSafeNumber(columns[payoutCol]);
payout.CreatedDate = today;
payout.ModifiedDate = today;
payout.InputSource = inputSource;
}
else if (payoutType == PayoutType.Resolution || payoutType == PayoutType.Other)
{
if (!isCompleted && resolutions == null)
{
resolutions = new List <Resolution>();
}
resolutionCount++;
isOwnerRow = false;
Resolution resolution = new Resolution();
resolution.ResolutionDate = (payout != null && payout.PayoutDate != null) ?
payout.PayoutDate :
ConversionHelper.EnsureUtcDate(GetSafeDate(columns[payoutDateCol]));
resolution.ResolutionType = columns[typeCol];
resolution.ResolutionDescription = columns[detailCol];
resolution.ResolutionAmount = GetSafeNumber(columns[amountCol]);
resolution.ConfirmationCode = columns[confirmationCodeCol];
resolution.Impact = string.Empty; // fill in later
resolution.CreatedDate = today;
resolution.ModifiedDate = today;
resolution.InputSource = inputSource;
resolution.ApprovalStatus = RevenueApprovalStatus.NotStarted;
// resolution has confirmation code associated with
if (!string.IsNullOrEmpty(resolution.ConfirmationCode))
{
string listingTitle = columns[listingCol];
string propertyCode = propertyProvider.GetPropertyCodeByListing(account, Unquote(listingTitle));
resolution.PropertyCode = propertyCode;
if (string.IsNullOrEmpty(resolution.ResolutionDescription))
{
resolution.ResolutionDescription = listingTitle;
}
}
// for future payout, link to
if (!isCompleted)
{
resolution.OwnerPayoutId = 0;
}
if (resolutions != null)
{
resolutions.Add(resolution);
}
}
else if (payoutType == PayoutType.Reservation)
{
if (!isCompleted && reservations == null)
{
reservations = new List <Reservation>();
}
reservationCount++;
isOwnerRow = false;
Reservation r = new Reservation();
r.ListingTitle = columns[listingCol];
string propertyCode = propertyProvider.GetPropertyCodeByListing(account, Unquote(r.ListingTitle));
r.TransactionDate = (payout != null && payout.PayoutDate != null) ?
payout.PayoutDate :
ConversionHelper.EnsureUtcDate(GetSafeDate(columns[payoutDateCol]));
r.PropertyCode = propertyCode;
r.ConfirmationCode = columns[confirmationCodeCol];
r.CheckinDate = ConversionHelper.EnsureUtcDate(GetSafeDate(columns[checkinDateCol]));
r.Nights = (int)GetSafeNumber(columns[nightCol]);
r.CheckoutDate = r.CheckinDate.Value.AddDays(r.Nights);
r.TotalRevenue = GetSafeNumber(columns[amountCol]);
r.GuestName = columns[guestCol];
r.Reference = columns[referenceCol];
CurrencyType currency;
if (Enum.TryParse(columns[currencyCol], true, out currency) == true)
{
r.Currency = currency;
}
else
{
r.Currency = CurrencyType.USD;
}
// non-input fields
r.Source = account;
r.Channel = channel;
r.LocalTax = 0;
r.DamageWaiver = 0;
r.AdminFee = 0;
r.PlatformFee = 0;
r.TaxRate = 0;
r.IsFutureBooking = isCompleted ? false : true;
r.IncludeOnStatement = true;
r.ApprovalStatus = RevenueApprovalStatus.NotStarted;
// house keeping fields
r.CreatedDate = today;
r.ModifiedDate = today;
r.InputSource = inputSource;
if (!isCompleted)
{
r.OwnerPayoutId = 0; // link to Owner Payout placeholder record for future payout
}
// if property is not fund, we use a placehoder property so it can be fixed later
if (string.IsNullOrEmpty(propertyCode))
{
errorRows.Add(new InputError
{
InputSource = inputSource,
Row = row,
Section = "Reservation",
Message = string.Format("Property from listing title '{0}' and account '{1}' does not exist", r.ListingTitle, account),
OriginalText = line,
CreatedTime = DateTime.UtcNow
});
r.PropertyCode = AppConstants.DEFAULT_PROPERTY_CODE;
//isRowError = true;
//errorCount++;
}
if (r.TotalRevenue < 0)
{
errorRows.Add(new InputError
{
InputSource = inputSource,
Row = row,
Section = "Reservation",
Message = string.Format("[PropertyCode] = '{0}' and [ConfirmationCode] = '{1}' [TotalRevenue] = {2}",
r.PropertyCode, r.ConfirmationCode, r.TotalRevenue.ToString("C2")),
OriginalText = "Excel row",
CreatedTime = DateTime.UtcNow
});
}
if (reservations != null && !string.IsNullOrEmpty(r.PropertyCode))
{
reservations.Add(r);
}
}
}
catch (Exception ex)
{
errorRows.Add(new InputError
{
InputSource = inputSource,
Row = row,
Section = "Exception",
Message = "Data parse exception: " + ex.Message,
OriginalText = line,
CreatedTime = DateTime.UtcNow
});
isRowError = true;
errorCount++;
}
}
try
{
// last one
if (isCompleted & payout != null && !isRowError)
{
if (payoutStartDate == null || payout.PayoutDate.Value.Date > payoutStartDate.Value.Date)
{
QueuePayout(payout, reservations, resolutions);
payoutCount++;
}
}
// save reservations and resoutions for future payout if there is no error
if (errorCount == 0 && !isCompleted)
{
if (resolutions != null && resolutions.Count() > 0)
{
_context.Resolutions.AddRange(resolutions);
}
if (reservations != null && reservations.Count() > 0)
{
_context.Reservations.AddRange(reservations);
}
_context.SaveChanges(); // Save Reservations
}
else if (errorCount == 0 && isCompleted)
{
_context.SaveChanges(); // save OwnerPayouts, Reservations, and Resolutions
}
// save process errors for resolution
if (errorRows.Count > 0)
{
_context.InputErrors.AddRange(errorRows);
_context.SaveChanges(); // save errors
}
}
catch (Exception ex)
{
var inputError = new InputError
{
InputSource = inputSource,
Row = 0,
Section = "Exception",
Message = "Data saving error: " + ex.Message,
OriginalText = "Database saving",
CreatedTime = DateTime.UtcNow
};
_context.InputErrors.Add(inputError);
_context.SaveChanges(); // save errors
errorCount = 100000; // a large number
}
sr.Close();
}
DeleteFileIfAllowed(csvFile);
if (errorCount > 0)
{
return(-errorCount);
}
else if (isCompleted)
{
return(payoutCount);
}
else
{
return(reservationCount);
}
}
public int ImportFromExcel(string excelFile, DateTime reportDate, bool isCompleted)
{
int errorCount = 0;
int payoutCount = 0;
int reservationCount = 0;
int resolutionCount = 0;
string channel = "Airbnb";
string account = GetAccountFromFilename(excelFile);
string inputSource = string.Format("{0} {1}", reportDate.ToString("yyyy-MM-dd"), GetInputSourceFromFilePath(excelFile));
string excelPath = Path.Combine(UrlHelper.DataRootUrl(), excelFile);
if (!File.Exists(excelPath))
{
var inputError = new InputError
{
InputSource = inputSource,
Row = 0,
Section = "Input File Check",
Message = string.Format("Input file '{0}' does not exist.", excelPath),
OriginalText = excelPath,
CreatedTime = DateTime.UtcNow
};
_context.InputErrors.Add(inputError);
_context.SaveChanges(); // save errors
return(-1000000); // large number of error
}
FileInfo excelFileInfo = new FileInfo(excelPath);
using (var package = new ExcelPackage(excelFileInfo))
{
ExcelWorkbook workBook = package.Workbook;
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
int totalCols = 14;
int startRow = 2; // starting row for reservation data
int payoutDateCol = 1;
int typeCol = 2;
int confirmationCodeCol = 3;
int checkinDateCol = 4;
int nightCol = 5;
int guestCol = 6;
int listingCol = 7;
int detailCol = 8;
int referenceCol = 9;
int currencyCol = 10;
int amountCol = 11;
int payoutCol = 12;
//int hostFeeCol = 13;
//int cleanFeeCol = 14;
ExcelWorksheet currentWorksheet = workBook.Worksheets[1];
bool isOwnerRow = false;
bool isRowError = false;
List <InputError> errorRows = new List <InputError>();
OwnerPayout payout = null;
List <Reservation> reservations = null;
List <Resolution> resolutions = null;
DateTime today = DateTime.UtcNow;
PropertyProvider propertyProvider = new PropertyProvider(_context);
for (int row = startRow; row <= currentWorksheet.Dimension.End.Row; row++)
{
if (currentWorksheet.Dimension.End.Column != totalCols)
{
errorRows.Add(new InputError
{
InputSource = inputSource,
Row = row,
Section = "Parse",
Message = string.Format("The total number of columns {0:d} does not match {1:d}", currentWorksheet.Dimension.End.Column, totalCols),
OriginalText = "Excel row",
CreatedTime = DateTime.UtcNow
});
}
try
{
if (isCompleted && currentWorksheet.Cells[row, payoutDateCol].Value == null)
{
if (payout != null && isOwnerRow && !isRowError)
{
QueuePayout(payout, reservations, resolutions);
payoutCount++;
}
break; // exit loop at the end of row
}
string type = GetSafeCellString(currentWorksheet.Cells[row, typeCol].Value);
var payoutType = type.StartsWith("Payout") ? PayoutType.Payout :
(type.StartsWith("Reservation") ? PayoutType.Reservation :
(type.StartsWith("Resolution") ? PayoutType.Resolution : PayoutType.Other));
if (payoutType == PayoutType.Payout)
{
if (isCompleted && payout != null && !isOwnerRow && !isRowError)
{
QueuePayout(payout, reservations, resolutions);
payoutCount++;
}
isOwnerRow = true;
isRowError = false;
payout = new OwnerPayout();
reservations = new List <Reservation>();
resolutions = new List <Resolution>();
payout.PayoutDate = GetSafeDate(currentWorksheet.Cells[row, payoutDateCol].Value);
payout.PayoutDate = ConversionHelper.EnsureUtcDate(payout.PayoutDate);
payout.AccountNumber = GetAccountNumber(currentWorksheet.Cells[row, detailCol].Value);
payout.Source = account;
payout.PayoutAmount = GetSafeNumber(currentWorksheet.Cells[row, payoutCol].Value);
payout.CreatedDate = today;
payout.ModifiedDate = today;
payout.InputSource = inputSource;
}
else if (payoutType == PayoutType.Resolution)
{
// business rule: one resolution per reservastion
resolutionCount++;
isOwnerRow = false;
Resolution resolution = new Resolution();
resolution.ResolutionDate = GetSafeDate(currentWorksheet.Cells[row, payoutDateCol].Value);
resolution.ResolutionDate = ConversionHelper.EnsureUtcDate(resolution.ResolutionDate);
resolution.ResolutionType = GetSafeCellString(currentWorksheet.Cells[row, typeCol].Value);
resolution.ResolutionDescription = GetSafeCellString(currentWorksheet.Cells[row, detailCol].Value);
resolution.ResolutionAmount = GetSafeNumber(currentWorksheet.Cells[row, amountCol].Value);
resolution.ConfirmationCode = string.Empty;
resolution.Impact = string.Empty; // fill in later
resolution.CreatedDate = today;
resolution.ModifiedDate = today;
resolution.InputSource = inputSource;
if (!isCompleted)
{
resolution.OwnerPayoutId = 0; // link to Owner Payout placeholder record for future payout
}
if (resolutions != null)
{
resolutions.Add(resolution);
}
}
else if (payoutType == PayoutType.Reservation)
{
if (!isCompleted && reservations == null)
{
reservations = new List <Reservation>();
}
reservationCount++;
isOwnerRow = false;
Reservation r = new Reservation();
r.ListingTitle = GetSafeCellString(currentWorksheet.Cells[row, listingCol].Value);
string propertyCode = propertyProvider.GetPropertyCodeByListing(account, Unquote(r.ListingTitle));
r.TransactionDate = GetSafeDate(currentWorksheet.Cells[row, payoutDateCol].Value);
r.TransactionDate = ConversionHelper.EnsureUtcDate(r.TransactionDate);
r.PropertyCode = propertyCode;
r.ConfirmationCode = GetSafeCellString(currentWorksheet.Cells[row, confirmationCodeCol].Value);
r.CheckinDate = GetSafeDate(currentWorksheet.Cells[row, checkinDateCol].Value);
r.CheckinDate = ConversionHelper.EnsureUtcDate(r.CheckinDate);
r.Nights = (int)GetSafeNumber(currentWorksheet.Cells[row, nightCol].Value);
r.CheckoutDate = r.CheckinDate.Value.AddDays(r.Nights);
r.TotalRevenue = GetSafeNumber(currentWorksheet.Cells[row, amountCol].Value);
//r.HostFee = GetSafeNumber(currentWorksheet.Cells[row, hostFeeCol].Value); // not needed per Jason
//r.CleanFee = GetSafeNumber(currentWorksheet.Cells[row, cleanFeeCol].Value); // not needed per Jason
r.GuestName = GetSafeCellString(currentWorksheet.Cells[row, guestCol].Value);
r.Reference = GetSafeCellString(currentWorksheet.Cells[row, referenceCol].Value);
CurrencyType currency;
if (Enum.TryParse(GetSafeCellString(currentWorksheet.Cells[row, currencyCol].Value), true, out currency) == true)
{
r.Currency = currency;
}
else
{
r.Currency = CurrencyType.USD;
}
// non-input fields
r.Source = account;
r.Channel = channel;
r.LocalTax = 0;
r.DamageWaiver = 0;
r.AdminFee = 0;
r.PlatformFee = 0;
r.TaxRate = 0;
r.IsFutureBooking = isCompleted ? false : true;
r.IncludeOnStatement = false;
r.ApprovalStatus = RevenueApprovalStatus.NotStarted;
// house keeping fields
r.CreatedDate = today;
r.ModifiedDate = today;
r.InputSource = inputSource;
if (!isCompleted)
{
r.OwnerPayoutId = 0; // link to Owner Payout placeholder record for future payout
}
// if property is not fund, we use a placehoder property so it can be fixed later
if (string.IsNullOrEmpty(propertyCode))
{
errorRows.Add(new InputError
{
InputSource = inputSource,
Row = row,
Section = "Reservation",
Message = string.Format("Property from listing title '{0}' and account '{1}' does not exist", Unquote(r.ListingTitle), account),
OriginalText = "Excel row",
CreatedTime = DateTime.UtcNow
});
r.PropertyCode = AppConstants.DEFAULT_PROPERTY_CODE;
//isRowError = true;
//errorCount++;
}
if (reservations != null && !string.IsNullOrEmpty(r.PropertyCode))
{
reservations.Add(r);
}
}
}
catch (Exception ex)
{
errorRows.Add(new InputError
{
InputSource = inputSource,
Row = row,
Section = "Exception",
Message = "Data parse exception: " + ex.Message,
OriginalText = "Excel row",
CreatedTime = DateTime.UtcNow
});
isRowError = true;
errorCount++;
}
}
try
{
// save reservations and resoutions for future payout if there is no error
if (errorCount == 0 && !isCompleted)
{
if (resolutions != null && resolutions.Count() > 0)
{
_context.Resolutions.AddRange(resolutions);
}
if (reservations != null && reservations.Count() > 0)
{
_context.Reservations.AddRange(reservations);
}
_context.SaveChanges(); // Save Reservations
}
else if (errorCount == 0 && isCompleted)
{
_context.SaveChanges(); // save OwnerPayouts, Reservations, and Resolutions
}
if (errorRows.Count > 0)
{
_context.InputErrors.AddRange(errorRows);
_context.SaveChanges(); // Save errors
}
}
catch (Exception ex)
{
var inputError = new InputError
{
InputSource = inputSource,
Row = 0,
Section = "Exception",
Message = "Data saving error: " + ex.Message,
OriginalText = "Database saving",
CreatedTime = DateTime.UtcNow
};
_context.InputErrors.Add(inputError);
_context.SaveChanges(); // save errors
errorCount = 100000; // a large number
}
}
}
}
try
{
excelFileInfo.Delete();
}
catch
{
// ignore if cannot delete
}
if (errorCount > 0)
{
return(-errorCount);
}
else if (isCompleted)
{
return(payoutCount);
}
else
{
return(reservationCount);
}
}
