示例#1
0
        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();
                }
            }
        }
示例#2
0
        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);
            }
        }
示例#3
0
        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();
                }
            }
        }
示例#4
0
        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();
                }
            }
        }