private Reservation MapToReservation(OffAirbnbRow row)
        {
            var r = new Reservation();

            r.ConfirmationCode = row.ConfirmationCode;
            r.CheckinDate      = ConversionHelper.EnsureUtcDate(row.CheckinDate);
            r.CheckoutDate     = ConversionHelper.EnsureUtcDate(row.CheckoutDate);
            r.Nights           = row.Nights;
            r.Channel          = row.Channel;
            r.PropertyCode     = row.PropertyCode;
            r.TransactionDate  = ConversionHelper.EnsureUtcDate(row.PayoutDate);
            r.GuestName        = string.Format("{0} {1}", row.FirstName, row.LastName);

            r.Source             = OFF_AIRBNB_SOURCE;
            r.InputSource        = OFF_AIRBNB_SOURCE;
            r.IncludeOnStatement = true;
            r.CreatedDate        = ConversionHelper.EnsureUtcDate(DateTime.Now);
            r.ModifiedDate       = r.CreatedDate;

            r.TaxRate      = 0;
            r.DamageWaiver = 0;
            r.IsTaxed      = true; // default for off-airbnb is taxed
            r.TotalRevenue = row.GrossTotal;
            if (r.CheckinDate != null)
            {
                var utcMonth  = ConversionHelper.EnsureUtcDate(r.CheckinDate.Value.AddDays(1));
                var feeAndTax = _context.PropertyFees.Where(x => x.PropertyCode == row.PropertyCode && x.EntryDate < utcMonth)
                                .OrderByDescending(x => x.EntryDate)
                                .FirstOrDefault();
                if (feeAndTax != null && feeAndTax.CityTax != null && feeAndTax.CityTax.Value > 0)
                {
                    var taxRate      = feeAndTax.CityTax != null ? feeAndTax.CityTax.Value : 0;
                    var damageWaiver = feeAndTax.DamageWaiver != null ? feeAndTax.DamageWaiver.Value : 0;

                    // calculate total revenue using formular: (gross - damange wavier) / (1.14 + tax rate)
                    if (row.GrossTotal - damageWaiver > 0)
                    {
                        r.TotalRevenue = (float)Math.Round((row.GrossTotal - damageWaiver) / (1.14 + taxRate), 2);
                    }
                    else // per rule
                    {
                        r.TotalRevenue = 0;
                    }
                }
            }

            return(r);
        }
        private OwnerPayout MapToOwnerPayout(OffAirbnbRow row)
        {
            var p = new OwnerPayout();

            p.PayoutDate        = ConversionHelper.EnsureUtcDate(row.PayoutDate);
            p.DiscrepancyAmount = 0;
            p.IsAmountMatched   = true;
            p.InputSource       = OFF_AIRBNB_SOURCE;
            p.Source            = OFF_AIRBNB_SOURCE;
            p.AccountNumber     = OFF_AIRBNB_PAYTO_ACCOUNT;
            p.CreatedDate       = ConversionHelper.EnsureUtcDate(DateTime.Now);
            p.ModifiedDate      = p.CreatedDate;
            p.Reservations      = new List <Reservation>();

            return(p);
        }
        private OffAirbnbRow ParseOffAirbnbExcelRow(ExcelRange cells, int row)
        {
            if (cells[row, _payoutDateCol].Text == string.Empty &&
                cells[row, _checkinDateCol].Text == string.Empty &&
                cells[row, _nightCol].Text == string.Empty)
            {
                return(null);
            }

            var r = new OffAirbnbRow();

            r.ConfirmationCode = GetSafeCellString(cells[row, _leaseIdCol].Value);
            r.PayoutDate       = GetSafeDate(cells[row, _payoutDateCol].Text);
            r.CheckinDate      = GetSafeDate(cells[row, _checkinDateCol].Text);
            r.Nights           = (int)GetSafeNumber(cells[row, _nightCol].Value);
            r.CheckoutDate     = r.CheckinDate.Value.AddDays(r.Nights);
            r.Channel          = GetChannel(GetSafeCellString(cells[row, _statusCol].Value));
            r.PropertyCode     = ParsePropertyCode(GetSafeCellString(cells[row, _unitNameCol].Value));
            r.LastName         = GetSafeCellString(cells[row, _guestLastNameCol].Value);
            r.FirstName        = GetSafeCellString(cells[row, _guestFirstNameCol].Value);
            r.GrossTotal       = GetSafeNumber(cells[row, _grossTotalCol].Value);

            return(r);
        }
        public int ImportExcel(Stream excelData)
        {
            int      errorCount       = 0;
            int      notFoundCount    = 0;
            int      ownerPayoutCount = 0;
            int      totalCols        = 29;
            int      startRow         = 2; // starting row for off-airbnb reservation data
            string   inputSource      = "Off Airbnb Excel";
            DateTime today            = DateTime.UtcNow;

            using (var package = new ExcelPackage(excelData))
            {
                ExcelWorkbook workBook   = package.Workbook;
                OffAirbnbRow  currentRow = null;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        ExcelWorksheet currentWorksheet = workBook.Worksheets[1];

                        // storage for parsed data
                        var errorRows    = new List <InputError>();
                        var ownerPayouts = new List <OwnerPayout>();

                        for (int row = startRow; row <= currentWorksheet.Dimension.End.Row; row++)
                        {
                            if (currentWorksheet.Dimension.End.Column != totalCols)
                            {
                                var message    = string.Format("The total number of columns {0:d} does not match {1:d}", currentWorksheet.Dimension.End.Column, totalCols);
                                var inputError = CreateInputError(inputSource, row, "Parse", message, "Excel row");
                                errorRows.Add(inputError);
                                errorCount++;
                            }

                            try
                            {
                                OffAirbnbRow r = ParseOffAirbnbExcelRow(currentWorksheet.Cells, row);
                                if (r == null)
                                {
                                    continue;            // skip the row that does not have checkin date or night
                                }
                                currentRow = r;

                                // if property is not fund, we write to input error table to deal with it.
                                if (!EnsurePropertyCode(r.PropertyCode))
                                {
                                    var message    = string.Format("Property {0} does not exist.", r.PropertyCode);
                                    var inputError = CreateInputError(inputSource, row, "Off-Airbnb", message, "Excel row");
                                    errorRows.Add(inputError);
                                    notFoundCount++;
                                }
                                else
                                {
                                    // create an owner payout for each reservation
                                    var ownerPayout = MapToOwnerPayout(r);
                                    ownerPayout.Reservations.Add(MapToReservation(r));
                                    ownerPayouts.Add(ownerPayout);
                                    ownerPayoutCount++;
                                }
                            }
                            catch (Exception ex)
                            {
                                var message    = "Data parse exception: " + ex.Message;
                                var inputError = CreateInputError(inputSource, row, "Exception", message, "Excel row");
                                errorRows.Add(inputError);
                                errorCount++;
                            }
                        }

                        try
                        {
                            // save reservations and resoutions for future payout if there is no error
                            if (errorCount == 0)
                            {
                                if (ownerPayouts != null && ownerPayouts.Count > 0)
                                {
                                    _context.OwnerPayouts.AddRange(ownerPayouts);
                                }

                                if (errorRows.Count() > 0)
                                {
                                    _context.InputErrors.AddRange(errorRows);
                                }

                                _context.SaveChanges(); // Save owner payouts and reservations
                            }
                            else
                            {
                                _context.InputErrors.AddRange(errorRows);
                                _context.SaveChanges(); // save errors
                            }
                        }
                        catch (Exception ex)
                        {
                            var message    = "Data saving error: " + ex.Message;
                            var inputError = CreateInputError(inputSource, 0, "Exception", message, "Database saving");
                            _context.InputErrors.Add(inputError);
                            _context.SaveChanges(); // save errors
                            errorCount = 100000;    // a large number
                        }
                    }
                }
            }

            return(errorCount == 0 ? ownerPayoutCount * 10000 + notFoundCount : -errorCount);
        }