public byte[] Export(Core.Reports.Enums.ExportType exportType, string[] parameters) { var filterCriteria = string.Empty; var startDate = default(DateTime); var endDate = default(DateTime); var districtList = new List <long>(); var infringementType = default(InfringementType?); var infringementValue = default(decimal?); var registerStatus = default(RegisterStatus?); var periodType = string.Empty; foreach (var parameter in parameters) { var parts = parameter.Split(new[] { '=' }, StringSplitOptions.RemoveEmptyEntries); if (parts.Length != 2) { continue; } if (parts[0].Equals("districtID", StringComparison.InvariantCultureIgnoreCase)) { long districtID = 0; if (long.TryParse(parts[1], out districtID)) { districtList.Add(districtID); } } else if (parts[0].Equals("startDate", StringComparison.InvariantCultureIgnoreCase)) { DateTime.TryParseExact(parts[1], "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.AdjustToUniversal, out startDate); } else if (parts[0].Equals("endDate", StringComparison.InvariantCultureIgnoreCase)) { DateTime.TryParseExact(parts[1], "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.AdjustToUniversal, out endDate); } else if (parts[0].Equals("infringementType", StringComparison.InvariantCultureIgnoreCase)) { InfringementType infringementType_ = 0; if (Enum.TryParse(parts[1], out infringementType_)) { infringementType = infringementType_; } } else if (parts[0].Equals("registerStatus", StringComparison.InvariantCultureIgnoreCase)) { RegisterStatus registerStatus_ = 0; if (Enum.TryParse(parts[1], out registerStatus_)) { registerStatus = registerStatus_; } } else if (parts[0].Equals("infringementValue", StringComparison.InvariantCultureIgnoreCase)) { decimal infringementValue_ = 0; if (decimal.TryParse(parts[1], out infringementValue_)) { infringementValue = infringementValue_; } } else if (parts[0].Equals("periodType", StringComparison.InvariantCultureIgnoreCase)) { periodType = parts[1]; } } if (periodType.Equals("thisMonth", StringComparison.InvariantCultureIgnoreCase)) { var now = DateTime.Now; startDate = new DateTime(now.Year, now.Month, 1); endDate = now.Date.AddDays(1); } else if (periodType.Equals("lastMonth", StringComparison.InvariantCultureIgnoreCase)) { var now = DateTime.Now; startDate = new DateTime(now.Year, now.Month, 1).AddMonths(-1); endDate = startDate.AddMonths(1).AddMilliseconds(-1); } else if (periodType.Equals("thisYear", StringComparison.InvariantCultureIgnoreCase)) { var now = DateTime.Now; startDate = new DateTime(now.Year, 1, 1); endDate = now.Date.AddDays(1); } else if (periodType.Equals("lastYear", StringComparison.InvariantCultureIgnoreCase)) { var now = DateTime.Now; startDate = new DateTime(now.Year, 1, 1).AddYears(-1); endDate = startDate.AddYears(1).AddMilliseconds(-1); } using (var dbContext = new DataContext()) { //dbContext.Database.Log = f => Debug.WriteLine(f); endDate = endDate.AddDays(1).AddMilliseconds(-1); var query = dbContext.OffenceRegister .AsNoTracking() .Include(f => f.Register) .Include(f => f.Register.District) .Include(f => f.Court) .Include(f => f.Register.Person) .Include(f => f.InfringementLocation) .Include(f => f.EvidenceLog) .Include(f => f.EvidenceLog.SpeedLog) .Include(f => f.EvidenceLog.ChargeInfos) .Include(f => f.EvidenceLog.InfringementEvidences) .Include(f => f.EvidenceLog.HandWrittenCaptureLog) .Where(f => (f.Register.DistrictID.HasValue && districtList.Contains(f.Register.DistrictID.Value)) && (f.InfringementDate >= startDate && f.InfringementDate <= endDate)); if (infringementType.HasValue) { query = query.Where(f => f.InfringementType == infringementType.Value); } if (registerStatus.HasValue) { query = query.Where(f => f.Register.RegisterStatus == (int)registerStatus.Value); } if (infringementValue.HasValue) { query = query.Where(f => f.CapturedAmount.HasValue && f.CapturedAmount.Value >= infringementValue.Value); } var offences = query.OrderByDescending(f => f.InfringementDate).ToList(); var models = new List <OutstandingInfringerModel>(); foreach (var offence in offences) { var model = new OutstandingInfringerModel(); model.ReferenceNumber = offence.ReferenceNumber; model.DistrictName = offence.Register.District == null ? string.Empty : offence.Register.District.BranchName; model.OffenceLocation = offence.InfringementLocation == null ? string.Empty : offence.InfringementLocation.Description; model.OutstandingAmount = offence.Register.OutstandingAmount.Value; model.OffenceDate = offence.InfringementDate; model.OffenderName = offence.Register.Person == null ? string.Empty : string.Format("{0} {1}", offence.Register.Person.FirstNames, offence.Register.Person.LastName); model.OffenderEmail = offence.Register.Person == null ? string.Empty : offence.Register.Person.Email; model.OffenderMobile = offence.Register.Person == null ? string.Empty : offence.Register.Person.MobileNumber; model.FormattedRegisterStatus = offence.Register.RegisterStatus.ToString(); if (offence.EvidenceLog != null) { if (offence.EvidenceLog.ChargeInfos != null && offence.EvidenceLog.ChargeInfos.Count > 0) { var firstChargeInfo = offence.EvidenceLog.ChargeInfos.First(); model.OffenceCode = firstChargeInfo.OffenceCode.Code; model.OffenceDescription = firstChargeInfo.PrimaryDescription; } if (offence.EvidenceLog.HandWrittenCaptureLog != null) { model.OffenceLocation = offence.EvidenceLog.HandWrittenCaptureLog.OffenceLocationStreet; if (!string.IsNullOrWhiteSpace(offence.EvidenceLog.HandWrittenCaptureLog.OffenceLocationSuburb)) { model.OffenceLocation += string.Format("\n{0}", offence.EvidenceLog.HandWrittenCaptureLog.OffenceLocationSuburb); } if (!string.IsNullOrWhiteSpace(offence.EvidenceLog.HandWrittenCaptureLog.OffenceLocationTown)) { model.OffenceLocation += string.Format("\n{0}", offence.EvidenceLog.HandWrittenCaptureLog.OffenceLocationTown); } } } models.Add(model); } filterCriteria += string.Format("District: {0} ", districtList.Count == 1 ? dbContext.Districts.Find(districtList[0]).BranchName : "ALL"); filterCriteria += string.Format("Period: {0:yyyy/MM/dd} - {1:yyyy/MM/dd} ", startDate, endDate); filterCriteria += string.Format("Infringement Type: {0} ", infringementType.HasValue ? infringementType.ToString() : "ALL"); if (infringementValue.HasValue && infringementValue.Value > 0) { filterCriteria += string.Format("Values >: {0:0.00} ", infringementValue.Value); } filterCriteria += string.Format("Register Status: {0} ", registerStatus.HasValue ? registerStatus.ToString() : "ALL"); if (exportType == ExportType.PDF) { return(StreamPdfReport(BuildReport(models, filterCriteria))); } else if (exportType == ExportType.Excel) { return(StreamExcelReport(BuildReport(models, filterCriteria))); } else { throw new NotSupportedException(); } } }
public byte[] Export(ExportType exportType, string[] parameters) { var filterCriteria = string.Empty; var districtList = new List <long>(); var startDate = default(DateTime); var endDate = default(DateTime); var periodType = string.Empty; var infringementType = default(InfringementType?); var infringementValue = default(decimal?); foreach (var parameter in parameters) { var parts = parameter.Split(new[] { '=' }, StringSplitOptions.RemoveEmptyEntries); if (parts.Length != 2) { continue; } if (parts[0].Equals("districtID", StringComparison.InvariantCultureIgnoreCase)) { long districtID = 0; if (long.TryParse(parts[1], out districtID)) { districtList.Add(districtID); } } else if (parts[0].Equals("infringementType", StringComparison.InvariantCultureIgnoreCase)) { InfringementType infringementType_ = 0; if (Enum.TryParse(parts[1], out infringementType_)) { infringementType = infringementType_; } } else if (parts[0].Equals("periodType", StringComparison.InvariantCultureIgnoreCase)) { periodType = parts[1]; } else if (parts[0].Equals("startDate", StringComparison.InvariantCultureIgnoreCase)) { DateTime.TryParseExact(parts[1], "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.AdjustToUniversal, out startDate); } else if (parts[0].Equals("endDate", StringComparison.InvariantCultureIgnoreCase)) { DateTime.TryParseExact(parts[1], "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.AdjustToUniversal, out endDate); } else if (parts[0].Equals("infringementValue", StringComparison.InvariantCultureIgnoreCase)) { decimal infringementValue_ = 0; if (decimal.TryParse(parts[1], out infringementValue_)) { infringementValue = infringementValue_; } } } if (periodType.Equals("thisMonth", StringComparison.InvariantCultureIgnoreCase)) { var now = DateTime.Now; startDate = new DateTime(now.Year, now.Month, 1); endDate = now.Date.AddDays(1); } else if (periodType.Equals("lastMonth", StringComparison.InvariantCultureIgnoreCase)) { var now = DateTime.Now; startDate = new DateTime(now.Year, now.Month, 1).AddMonths(-1); endDate = startDate.AddMonths(1).AddMilliseconds(-1); } else if (periodType.Equals("thisYear", StringComparison.InvariantCultureIgnoreCase)) { var now = DateTime.Now; startDate = new DateTime(now.Year, 1, 1); endDate = now.Date.AddDays(1); } else if (periodType.Equals("lastYear", StringComparison.InvariantCultureIgnoreCase)) { var now = DateTime.Now; startDate = new DateTime(now.Year, 1, 1).AddYears(-1); endDate = startDate.AddYears(1).AddMilliseconds(-1); } using (var dbContext = new DataContext()) { //dbContext.Database.Log = f => Debug.WriteLine(f); endDate = endDate.AddDays(1).AddMilliseconds(-1); var query = dbContext.OffenceRegister .AsNoTracking() .Include(f => f.Register) .Include(f => f.Register.District) .Include(f => f.Court) .Where(f => (f.Register.DistrictID.HasValue && districtList.Contains(f.Register.DistrictID.Value)) && (f.InfringementDate >= startDate && f.InfringementDate <= endDate)); if (infringementType.HasValue) { query = query.Where(f => f.InfringementType == infringementType.Value); } if (infringementValue.HasValue) { query = query.Where(f => f.CapturedAmount.HasValue && f.CapturedAmount.Value >= infringementValue.Value); } var entities = query .OrderBy(f => new { f.InfringementDate, f.District.BranchName }) //.OrderBy(f => f.District.BranchName) .ToList(); var models = entities.Select(f => new InfringementStatusModel { InfringementDate = f.InfringementDate, InfringementNumber = f.ReferenceNumber, OriginalAmount = !f.CapturedAmount.HasValue ? 0 : f.CapturedAmount.Value, CurrentAmount = !f.Register.OutstandingAmount.HasValue ? 0 : f.Register.OutstandingAmount.Value, FormattedRegisterStatus = (f.Register.RegisterStatus).ToString(), FormattedInfringementType = (f.InfringementType).ToString(), CourtName = f.Court.CourtName, DistrictName = f.Register.District.BranchName }) .ToList(); filterCriteria += string.Format("District: {0} ", districtList.Count == 1 ? dbContext.Districts.Find(districtList[0]).BranchName : "ALL"); filterCriteria += string.Format("Period: {0:yyyy/MM/dd} - {1:yyyy/MM/dd} ", startDate, endDate); filterCriteria += string.Format("Infringement Type: {0} ", infringementType.HasValue ? infringementType.ToString() : "ALL"); if (infringementValue.HasValue && infringementValue.Value > 0) { filterCriteria += string.Format("Values >: {0:0.00} ", infringementValue.Value); } if (exportType == ExportType.PDF) { return(StreamPdfReport(BuildReport(models, filterCriteria))); } if (exportType == ExportType.Excel) { return(StreamExcelReport(BuildReport(models, filterCriteria))); } return(null); } }
public byte[] Export(ExportType exportType, string[] parameters) { var filterCriteria = string.Empty; var startDate = default(DateTime); var endDate = default(DateTime); var districtList = new List <long>(); var infringementType = default(InfringementType?); var officerID = default(long?); var periodType = string.Empty; foreach (var parameter in parameters) { var parts = parameter.Split(new[] { '=' }, StringSplitOptions.RemoveEmptyEntries); if (parts.Length != 2) { continue; } if (parts[0].Equals("districtID", StringComparison.InvariantCultureIgnoreCase)) { long districtID = 0; if (long.TryParse(parts[1], out districtID)) { districtList.Add(districtID); } } else if (parts[0].Equals("startDate", StringComparison.InvariantCultureIgnoreCase)) { DateTime.TryParseExact(parts[1], "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.AdjustToUniversal, out startDate); } else if (parts[0].Equals("endDate", StringComparison.InvariantCultureIgnoreCase)) { DateTime.TryParseExact(parts[1], "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.AdjustToUniversal, out endDate); } else if (parts[0].Equals("infringementType", StringComparison.InvariantCultureIgnoreCase)) { InfringementType infringementType_ = 0; if (Enum.TryParse(parts[1], out infringementType_)) { infringementType = infringementType_; } } else if (parts[0].Equals("officerID", StringComparison.InvariantCultureIgnoreCase)) { officerID = int.Parse(parts[1]); } else if (parts[0].Equals("periodType", StringComparison.InvariantCultureIgnoreCase)) { periodType = parts[1]; } } if (periodType.Equals("thisMonth", StringComparison.InvariantCultureIgnoreCase)) { var now = DateTime.Now; startDate = new DateTime(now.Year, now.Month, 1); endDate = now.Date.AddDays(1); } else if (periodType.Equals("lastMonth", StringComparison.InvariantCultureIgnoreCase)) { var now = DateTime.Now; startDate = new DateTime(now.Year, now.Month, 1).AddMonths(-1); endDate = startDate.AddMonths(1).AddMilliseconds(-1); } else if (periodType.Equals("thisYear", StringComparison.InvariantCultureIgnoreCase)) { var now = DateTime.Now; startDate = new DateTime(now.Year, 1, 1); endDate = now.Date.AddDays(1); } else if (periodType.Equals("lastYear", StringComparison.InvariantCultureIgnoreCase)) { var now = DateTime.Now; startDate = new DateTime(now.Year, 1, 1).AddYears(-1); endDate = startDate.AddYears(1).AddMilliseconds(-1); } using (var dbContext = new DataContext()) { //dbContext.Database.Log = f => Debug.WriteLine(f); endDate = endDate.AddDays(1).AddMilliseconds(-1); var query = dbContext.OffenceRegister .AsNoTracking() .Include(f => f.Credential) .Include(f => f.Register) .Include(f => f.Register.District) .Include(f => f.Court) .Include(f => f.Register.Person) .Include(f => f.InfringementLocation) .Include(f => f.EvidenceLog) .Include(f => f.EvidenceLog.HandWrittenCaptureLog) .Where(f => (f.Register.DistrictID.HasValue && districtList.Contains(f.Register.DistrictID.Value)) && f.EvidenceLog.HandWrittenCaptureLog != null && f.EvidenceLog.InfringementDate >= startDate && f.EvidenceLog.InfringementDate < endDate); if (officerID.HasValue) { query = query.Where(f => f.Credential.EntityID == officerID.Value && f.Credential.EntityType == Core.Data.Enums.EntityType.User); } if (infringementType.HasValue) { query = query.Where(f => f.InfringementType == infringementType.Value); } var offences = query .OrderBy(f => new { f.InfringementDate, f.CredentialID, f.Court.DistrictID }) //.OrderBy(f => f.CredentialID) //.OrderBy(f => f.Court.DistrictID) .ToList(); var models = new List <OffencesPerOfficerModel>(); foreach (var offence in offences) { var transactionToken = string.Empty; var generatedReferenceNumber = dbContext.GeneratedReferenceNumbers.FirstOrDefault(f => f.ReferenceNumber == offence.ReferenceNumber); if (generatedReferenceNumber != null) { transactionToken = generatedReferenceNumber.ExternalToken; } var model = new OffencesPerOfficerModel(); model.ReferenceNumber = offence.ReferenceNumber; model.InfringementType = (Gateway.Models.Enums.InfringementType)offence.InfringementType; model.DistrictID = offence.DistrictID; model.DistrictName = offence.Register.District == null ? string.Empty : offence.Register.District.BranchName; model.CourtID = offence.CourtID; model.CourtName = offence.Court == null ? string.Empty : offence.Court.CourtName; model.CourtDate = offence.CourtDate; model.OffenderIDNumber = offence.Register.Person == null ? string.Empty : offence.Register.Person.IDNumber; model.OffenderLastName = offence.Register.Person == null ? string.Empty : offence.Register.Person.LastName; model.OffenderFirstName = offence.Register.Person == null ? string.Empty : offence.Register.Person.FirstNames; model.VLN = offence.VLN; model.OffenceLocation = offence.InfringementLocation == null ? string.Empty : offence.InfringementLocation.Description; model.OffenceAmount = offence.CapturedAmount; model.OffenceDate = offence.InfringementDate; model.OutstandingAmount = offence.Register.OutstandingAmount; model.Status = (Gateway.Models.Enums.RegisterStatus)offence.Register.RegisterStatus; model.OfficerExternalNumber = offence.Credential.User.ExternalID; model.OfficerID = offence.Credential.User.ID; model.OfficerName = string.Format("{0} {1}", offence.Credential.User.FirstName, offence.Credential.User.LastName); if (offence.EvidenceLog.HandWrittenCaptureLog != null) { model.OffenceSpeed = offence.EvidenceLog.HandWrittenCaptureLog.Speed; var chargeWithZone = offence.EvidenceLog.ChargeInfos.FirstOrDefault(f => f.OffenceCode != null && f.OffenceCode.Zone.HasValue && f.OffenceCode.Zone.Value > 0); if (chargeWithZone != null) { model.SpeedLimit = chargeWithZone.OffenceCode.Zone; } model.OffenceLocation = offence.EvidenceLog.HandWrittenCaptureLog.OffenceLocationStreet; if (!string.IsNullOrWhiteSpace(offence.EvidenceLog.HandWrittenCaptureLog.OffenceLocationSuburb)) { model.OffenceLocation += string.Format("\n{0}", offence.EvidenceLog.HandWrittenCaptureLog.OffenceLocationSuburb); } if (!string.IsNullOrWhiteSpace(offence.EvidenceLog.HandWrittenCaptureLog.OffenceLocationTown)) { model.OffenceLocation += string.Format("\n{0}", offence.EvidenceLog.HandWrittenCaptureLog.OffenceLocationTown); } } models.Add(model); } filterCriteria += string.Format("District: {0} ", districtList.Count == 1 ? dbContext.Districts.Find(districtList[0]).BranchName : "ALL"); filterCriteria += string.Format("\t Period: {0:yyyy/MM/dd} - {1:yyyy/MM/dd} ", startDate, endDate); filterCriteria += string.Format("\t Infringement Type: {0} ", infringementType.HasValue ? infringementType.ToString() : "ALL"); if (officerID.HasValue) { var officer = dbContext.Users.Find(officerID.Value); filterCriteria += string.Format("\t Officer: {0} {1} ", officer.FirstName, officer.LastName); } else { filterCriteria += string.Format("\t Officer: ALL"); } if (exportType == ExportType.PDF) { return(StreamPdfReport(BuildReport(models, filterCriteria))); } else if (exportType == ExportType.Excel) { return(StreamExcelReport(BuildReport(models, filterCriteria))); } else { throw new NotSupportedException(); } } }
public byte[] Export(ExportType exportType, string[] parameters) { var filterCriteria = string.Empty; var districtList = new List <long>(); var excludeBeforeDate = default(DateTime?); var infringementType = default(InfringementType?); var infringementValue = default(decimal?); foreach (var parameter in parameters) { var parts = parameter.Split(new[] { '=' }, StringSplitOptions.RemoveEmptyEntries); if (parts[0].Equals("districtID", StringComparison.InvariantCultureIgnoreCase) && parts.Length == 2) { long districtID = 0; if (long.TryParse(parts[1], out districtID)) { districtList.Add(districtID); } } else if (parts[0].Equals("infringementType", StringComparison.InvariantCultureIgnoreCase) && parts.Length == 2) { InfringementType infringementType_ = 0; if (Enum.TryParse(parts[1], out infringementType_)) { infringementType = infringementType_; } } else if (parts[0].Equals("excludeBeforeDate", StringComparison.InvariantCultureIgnoreCase) && parts.Length == 2) { DateTime excludeBeforeDate_; if (DateTime.TryParseExact(parts[1], "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.AdjustToUniversal, out excludeBeforeDate_)) { excludeBeforeDate = excludeBeforeDate_; } } else if (parts[0].Equals("infringementValue", StringComparison.InvariantCultureIgnoreCase) && parts.Length == 2) { decimal infringementValue_ = 0; if (decimal.TryParse(parts[1], out infringementValue_)) { infringementValue = infringementValue_; } } } using (var dbContext = new DataContext()) { //dbContext.Database.Log = f => Debug.WriteLine(f); var query = dbContext.OffenceRegister .AsNoTracking() .Include(f => f.District) .Join( dbContext.PaymentTransactionItems .AsNoTracking() .Include(f => f.PaymentTransaction), f => f.ReferenceNumber, f => f.ReferenceNumber, (offenceRegister, paymentTransactionItems) => new { DistrictID = offenceRegister.District.ID, DistrictName = offenceRegister.District.BranchName, Status = paymentTransactionItems.PaymentTransaction.Status, Amount = paymentTransactionItems.Amount, CreatedTimestamp = paymentTransactionItems.PaymentTransaction.CreatedTimestamp, InfringementType = offenceRegister.InfringementType, CapturedAmount = offenceRegister.CapturedAmount, ReferenceNumber = offenceRegister.ReferenceNumber }) .Where( f => districtList.Contains(f.DistrictID) && f.Status == PaymentTransactionStatus.Added); if (excludeBeforeDate.HasValue) { query = query.Where(f => f.CreatedTimestamp >= excludeBeforeDate.Value); } if (infringementType.HasValue) { query = query.Where(f => f.InfringementType == infringementType.Value); } if (infringementValue.HasValue) { query = query.Where(f => f.CapturedAmount >= infringementValue.Value); } var models = query .ToList() .GroupBy(f => new { f.DistrictName, f.DistrictID }) .Select(f => new InfringementPaidModel { DistrictName = f.Key.DistrictName, DistrictID = f.Key.DistrictID, Count = f.GroupBy(g => g.ReferenceNumber).Count(), Value = f.Sum(g => g.Amount) }) .OrderBy(f => f.DistrictName) .ToList(); filterCriteria += string.Format("District: {0} ", districtList.Count == 1 ? dbContext.Districts.Find(districtList[0]).BranchName : "ALL"); if (excludeBeforeDate.HasValue) { filterCriteria += string.Format("Infringements From: {0:yyyy/MM/dd} ", excludeBeforeDate.Value); } filterCriteria += string.Format("Infringement Type: {0} ", infringementType.HasValue ? infringementType.ToString() : "ALL"); if (infringementValue.HasValue && infringementValue.Value > 0) { filterCriteria += string.Format("Values >: {0:0.00} ", infringementValue.Value); } if (exportType == ExportType.PDF) { return(StreamPdfReport(BuildReport(models, filterCriteria))); } else if (exportType == ExportType.Excel) { return(StreamExcelReport(BuildReport(models, filterCriteria))); } else { throw new NotSupportedException(); } } }