public async Task <IHttpActionResult> Current(string subCruise)
{
var cruise = await _cruiseRepository.GetActiveAsync();
if (null == cruise)
{
return(NotFound());
}
var subCruiseCode = SubCruiseCode.FromString(subCruise ?? string.Empty);
using (var db = DbUtil.Open())
{
return(this.OkCacheControl(new
{
AgeDistribution = await _reportingService.GetAgeDistribution(db, cruise, subCruiseCode),
// Bookings per subcruise is not supported, so we null this one if a subcruise is provided
BookingsByPayment = SubCruiseCode.None != subCruiseCode ? null : await _reportingService.GetNumberOfBookingsByPaymentStatus(db, cruise),
Genders = await _reportingService.GetGenders(db, cruise, subCruiseCode),
TopContacts = await _reportingService.GetTopContacts(db, cruise, subCruiseCode, 15),
TopGroups = await _reportingService.GetTopGroups(db, cruise, subCruiseCode, 15)
},
WebConfig.DynamicDataMaxAge));
}
}
async Task <int> GetTotalCapacity(SqlConnection db, Cruise cruise, SubCruiseCode subCruise)
{
// This query gets a little more data than we need but can be reused if there is ever a need to get stats per cabin type
IDataReader reader;
if (SubCruiseCode.None != subCruise)
{
reader = await db.ExecuteReaderAsync("select BC.[CabinTypeId], count(*) [Count], CT.[Capacity] from [BookingCabin] BC " +
"left join [CabinType] CT on BC.[CabinTypeId] = CT.[Id] " +
"where BC.[CruiseId] = @CruiseId and CT.[SubCruise] = @SubCruise " +
"group by BC.[CabinTypeId], CT.[Capacity]", new { CruiseId = cruise.Id, SubCruise = subCruise });
}
else
{
reader = await db.ExecuteReaderAsync("select [CabinTypeId], count(*), (select [Capacity] from [CabinType] where [Id] = [CabinTypeId]) " +
"from [BookingCabin] where [CruiseId] = @CruiseId " +
"group by [CabinTypeId]", new { CruiseId = cruise.Id });
}
int sum = 0;
while (reader.Read())
{
sum += reader.GetInt32(1) * reader.GetInt32(2);
}
reader.Close();
return(sum);
}
void CheckCabinTypesValidity(SubCruiseCode subCruiseForBooking, List <BookingSource.Cabin> sourceCabins, CruiseCabinWithType[] cabinTypes)
{
var typeDict = cabinTypes.ToDictionary(c => c.Id, c => c.SubCruise);
if (sourceCabins.Any(cabin => !typeDict.ContainsKey(cabin.TypeId) || !subCruiseForBooking.Equals(SubCruiseCode.FromString(typeDict[cabin.TypeId]))))
{
throw new BookingException($"One or more cabin types in the booking are not valid for sub-cruise {subCruiseForBooking}.");
}
}
public async Task <Report[]> GetActiveAsync(Cruise cruise, SubCruiseCode subCruise)
{
using (var db = DbUtil.Open())
{
var result = await db.QueryAsync <Report>("select * from [Report] where [CruiseId] = @CruiseId and [SubCruise] = @SubCruise order by [Date]",
new { Id = cruise.Id, SubCruise = subCruise });
return(result.ToArray());
}
}
public async Task <IHttpActionResult> Summary(string subCruise)
{
var cruise = await _cruiseRepository.GetActiveAsync();
if (null == cruise)
{
return(NotFound());
}
var subCruiseCode = SubCruiseCode.FromString(subCruise ?? string.Empty);
Report[] reports = await _reportRepository.GetActiveAsync(cruise, subCruiseCode);
return(this.OkCacheControl(ReportSummary.FromReports(reports), WebConfig.DynamicDataMaxAge));
}
async Task <int> GetTotalPax(SqlConnection db, Cruise cruise, SubCruiseCode subCruise)
{
if (SubCruiseCode.None != subCruise)
{
return(await db.ExecuteScalarAsync <int>("select count(*) from [BookingPax] " +
"where [BookingCabinId] in (select [Id] from [BookingCabin] where [CruiseId] = @CruiseId " +
"and [CabinTypeId] in (select [Id] from [CabinType] where [SubCruise] = @SubCruise))",
new { CruiseId = cruise.Id, SubCruise = subCruise }));
}
else
{
return(await db.ExecuteScalarAsync <int>("select count(*) from [BookingPax] " +
"where [BookingCabinId] in (select [Id] from [BookingCabin] where [CruiseId] = @CruiseId)",
new { CruiseId = cruise.Id }));
}
}
async Task <int> GetTotalCabins(SqlConnection db, Cruise cruise, SubCruiseCode subCruise)
{
int result;
if (SubCruiseCode.None != subCruise)
{
result = await db.ExecuteScalarAsync <int>("select count(*) from [BookingCabin] where " +
"[BookingId] in (select [Id] from [Booking] where [CruiseId] = @CruiseId) and " +
"[CabinTypeId] in (select [Id] from [CabinType] where [SubCruise] = @SubCruise)",
new { CruiseId = cruise.Id, SubCruise = subCruise });
}
else
{
result = await db.ExecuteScalarAsync <int>("select count(*) from [BookingCabin] where [BookingId] in (select [Id] from [Booking] where [CruiseId] = @CruiseId)",
new { CruiseId = cruise.Id });
}
return(result);
}
public async Task <IHttpActionResult> Excel(bool onlyFullyPaid = false, string updatedSince = null, string subCruise = null)
{
var activeCruise = await _cruiseRepository.GetActiveAsync();
if (null == activeCruise)
{
return(NotFound());
}
SubCruiseCode subCruiseCode = string.IsNullOrEmpty(subCruise) ? SubCruiseCode.First : SubCruiseCode.FromString(subCruise);
DateTime? updatedSinceDate = string.IsNullOrEmpty(updatedSince)
? null
: new DateTime?(DateTime.ParseExact(updatedSince, UpdatedSinceFormat, CultureInfo.InvariantCulture));
var exportToExcelGenerator = new ExportToExcelGenerator(_cabinRepository, _productRepository);
Workbook workbook = await exportToExcelGenerator.ExportToWorkbookAsync(activeCruise, onlyFullyPaid, subCruiseCode.ToString(), updatedSinceDate);
return(CreateHttpResponseMessage(workbook, subCruise));
}
async Task <Report> CreateReport(Cruise cruise, SubCruiseCode subCruise)
{
DateTime today = DateTime.Today;
Report report = new Report {
CruiseId = cruise.Id, SubCruise = subCruise.ToString(), Date = today.Date
};
using (var db = DbUtil.Open())
{
// Don't count bookings per subcruise since technically, one booking may have more than one subcruise
// This is not actually possible in UI today but could be in the future so here we are
report.BookingsCreated = SubCruiseCode.None != subCruise ? 0 : await GetCreatedBookings(db, cruise, today);
report.BookingsTotal = SubCruiseCode.None != subCruise ? 0 : await GetTotalBookings(db, cruise);
report.CabinsTotal = await GetTotalCabins(db, cruise, subCruise);
report.PaxTotal = await GetTotalPax(db, cruise, subCruise);
report.CapacityTotal = await GetTotalCapacity(db, cruise, subCruise);
}
return(report);
}
public async Task <KeyValuePair[]> GetAgeDistribution(SqlConnection db, Cruise cruise, SubCruiseCode subCruise)
{
IEnumerable <KeyValuePair> result;
if (SubCruiseCode.None != subCruise)
{
result = await db.QueryAsync <KeyValuePair>("select substring([Dob], 1, 2) [Key], count(*) [Value] from [BookingPax] " +
"where [BookingCabinId] in (select BC.[Id] from [BookingCabin] BC " +
"left join [CabinType] CT on BC.[CabinTypeId] = CT.[Id] where BC.[CruiseId] = @CruiseId AND CT.[SubCruise] = @SubCruise) " +
"group by substring([Dob], 1, 2)", new { CruiseId = cruise.Id, SubCruise = subCruise });
}
else
{
result = await db.QueryAsync <KeyValuePair>("select substring([Dob], 1, 2) [Key], count(*) [Value] from [BookingPax] " +
"where [BookingCabinId] in (select [Id] from [BookingCabin] where [CruiseId] = @CruiseId) " +
"group by substring([Dob], 1, 2)", new { CruiseId = cruise.Id });
}
int thisYear = DateTime.Now.Year;
int thisYearTwoDigits = thisYear % 100;
Tuple <int, int>[] sourceList = result
.Select(k => Tuple.Create(ToFourDigitYear(int.Parse(k.Key, NumberStyles.None), thisYearTwoDigits), k.Value))
.Where(t => t.Item1 <= thisYear - AgeDistributionMinAge) // if underage, assume fake or placeholder data
.ToArray();
if (!sourceList.Any())
{
return(new KeyValuePair[0]);
}
int ageDistributionEndYear = sourceList.Max(k => k.Item1);
var buckets = new Dictionary <int, int>();
for (int year = AgeDistributionStartYear; year <= ageDistributionEndYear; year++)
{
buckets[year] = 0;
}
foreach (Tuple <int, int> pair in sourceList)
{
// Put everyone who is older than AgeDistributionStartYear into one bucket
int year = pair.Item1 <= AgeDistributionStartYear
? AgeDistributionStartYear
: pair.Item1;
if (buckets.ContainsKey(year))
{
buckets[year] += pair.Item2;
}
}
return(buckets.Select(k => new KeyValuePair(
k.Key == AgeDistributionStartYear ? $"≤{ToPaddedTwoDigitYear(AgeDistributionStartYear)}" : ToPaddedTwoDigitYear(k.Key).ToString(),
k.Value)).ToArray());
}
public async Task <KeyValuePair[]> GetTopGroups(SqlConnection db, Cruise cruise, SubCruiseCode subCruise, int top)
{
IEnumerable <KeyValuePair> result;
if (SubCruiseCode.None != subCruise)
{
result = await db.QueryAsync <KeyValuePair>("select top (@Top) BP.[Group] [Key], COUNT(*) [Value] " +
"from [BookingPax] BP " +
"left join [BookingCabin] BC on BP.[BookingCabinId] = BC.[Id] " +
"left join [CabinType] CT on BC.[CabinTypeId] = CT.[Id] " +
"where BC.[CruiseId] = @CruiseId and CT.[SubCruise] = @SubCruise " +
"group by BP.[Group] " +
"order by COUNT(*) desc", new { Top = top, CruiseId = cruise.Id, SubCruise = subCruise });
}
else
{
result = await db.QueryAsync <KeyValuePair>("select top (@Top) BP.[Group] [Key], COUNT(*) [Value] " +
"from [BookingPax] BP " +
"left join [BookingCabin] BC on BP.[BookingCabinId] = BC.[Id] " +
"where BC.[CruiseId] = @CruiseId " +
"group by BP.[Group] " +
"order by COUNT(*) desc", new { Top = top, CruiseId = cruise.Id });
}
return(result.ToArray());
}
public async Task <KeyValuePair[]> GetGenders(SqlConnection db, Cruise cruise, SubCruiseCode subCruise)
{
IEnumerable <KeyValuePair> result;
if (SubCruiseCode.None != subCruise)
{
result = await db.QueryAsync <KeyValuePair>("select [Gender] [Key], COUNT(*) [Value] from [BookingPax] " +
"where [BookingCabinId] in (select BC.[Id] from [BookingCabin] BC " +
"left join [CabinType] CT on BC.[CabinTypeId] = CT.[Id] " +
"where BC.[CruiseId] = @CruiseId and CT.[SubCruise] = @SubCruise) " +
"group by [Gender]", new { CruiseId = cruise.Id, SubCruise = subCruise });
}
else
{
result = await db.QueryAsync <KeyValuePair>("select [Gender] [Key], COUNT(*) [Value] from [BookingPax] " +
"where [BookingCabinId] in (select BC.[Id] from [BookingCabin] BC " +
"where BC.[CruiseId] = @CruiseId) " +
"group by [Gender]", new { CruiseId = cruise.Id });
}
return(result.ToArray());
}
public async Task <BookingResult> UpdateAsync(Cruise cruise, BookingSource source, bool allowUpdateDetails = false, bool allowUpdateIfLocked = false)
{
BookingSource.ValidateCabins(source);
if (allowUpdateDetails)
{
source.ValidateDetails();
}
Booking booking;
// See CreateAsync regarding the use of transaction + applock here.
var tranOptions = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadUncommitted
};
using (var tran = new TransactionScope(TransactionScopeOption.Required, tranOptions, TransactionScopeAsyncFlowOption.Enabled))
using (var db = DbUtil.Open())
{
var cabinTypes = await _cabinRepository.GetActiveAsync(db, cruise);
var productTypes = await _productRepository.GetActiveAsync(db, cruise);
CheckCabinTypesValidity(SubCruiseCode.FromString(source.SubCruise), source.Cabins, cabinTypes);
await db.GetAppLockAsync(LockResource, LockTimeout);
booking = await FindByReferenceAsync(db, source.Reference);
if (null == booking || booking.CruiseId != cruise.Id)
{
throw new BookingException($"Booking with reference {source.Reference} not found or not in active cruise.");
}
if ((cruise.IsLocked || booking.IsLocked) && !allowUpdateIfLocked)
{
throw new BookingException($"Booking with reference {source.Reference} is locked, may not update.");
}
// Get the booking contents before deleting it so we can detect changes
BookingCabinWithPax[] originalCabins = await GetCabinsForBooking(db, booking.Id);
await DeleteCabins(db, booking);
await CheckCabinsAvailability(db, cruise, source.Cabins, cabinTypes);
await CreateCabins(db, booking, source.Cabins);
await DeleteProducts(db, booking);
await CheckProductsAvailability(db, cruise, source.Products);
await CreateProducts(db, booking, source.Products);
await CreateChanges(db, booking, originalCabins, source.Cabins);
decimal totalPrice = _priceCalculator.CalculatePrice(source.Cabins, source.Products, booking.Discount, cabinTypes, productTypes);
if (allowUpdateDetails)
{
await db.ExecuteAsync(
"update [Booking] set [FirstName] = @FirstName, [LastName] = @LastName, [Email] = @Email, [PhoneNo] = @PhoneNo, [Lunch] = @Lunch, [InternalNotes] = @InternalNotes, [SubCruise] = @SubCruise, [TotalPrice] = @TotalPrice, [Updated] = sysdatetime() where [Id] = @Id",
new
{
FirstName = source.FirstName, LastName = source.LastName, Email = source.Email, PhoneNo = source.PhoneNo, Lunch = source.Lunch, InternalNotes = source.InternalNotes, SubCruise = source.SubCruise,
TotalPrice = totalPrice, Id = booking.Id
});
}
else
{
await db.ExecuteAsync("update [Booking] set [SubCruise] = @SubCruise, [TotalPrice] = @TotalPrice, [Updated] = sysdatetime() where [Id] = @Id",
new { SubCruise = source.SubCruise, TotalPrice = totalPrice, Id = booking.Id });
}
tran.Complete();
}
return(new BookingResult {
Reference = booking.Reference
});
}