C# SqlConnection.CreateStoreCommand方法代码示例

        private void migrateAircraftCreditorsFm(INomRepository repo, SqlConnection conn)
        {
            Nom nom = repo.GetNom("aircraftCreditorsFm");
            var results = conn.CreateStoreCommand(@"select distinct t_Creditor_Name from Morts_new")
                .Materialize(r =>
                    new NomValue
                    {
                        OldId = null,
                        Code = null,
                        Name = r.Field<string>("t_Creditor_Name"),
                        NameAlt = null,
                        Alias = null,
                        IsActive = true,
                        ParentValueId = null,
                        TextContentString = null
                    })
                .ToList();

            noms["aircraftCreditorsFm"] = new Dictionary<string, NomValue>();
            foreach (var row in results)
            {
                noms["aircraftCreditorsFm"][Guid.NewGuid().ToString()] = row;
                nom.NomValues.Add(row);
            }
        }

        private void migrateAircraftLimitationsFm(INomRepository repo, SqlConnection conn)
        {
            Nom nom = repo.GetNom("aircraftLimitationsFm");
            var results = conn.CreateStoreCommand(@"
                SELECT [Code] code, [Limitation BG] name, [Limitation EN] nameAlt
                FROM [GvaAircraft].[dbo].[LimitAW]")
                .Materialize(r =>
                    new NomValue
                    {
                        OldId = r.Field<string>("code"),
                        Code = r.Field<string>("code"),
                        Name = r.Field<string>("name"),
                        NameAlt = r.Field<string>("nameAlt"),
                        Alias = null,
                        IsActive = true,
                        ParentValueId = null,
                        TextContentString = null
                    })
                .ToList();

            noms["aircraftLimitationsFm"] = new Dictionary<string, NomValue>();
            foreach (var row in results)
            {
                noms["aircraftLimitationsFm"][row.OldId] = row;
                nom.NomValues.Add(row);
            }
        }

        private void migrateOrganizationTypes(INomRepository repo, OracleConnection oracleConection, SqlConnection sqlConnection)
        {
            Nom nom = repo.GetNom("organizationTypes");
            var orgApexTypes = oracleConection.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_FIRM_TYPE")
                .Materialize(r =>
                    new NomValue
                    {
                        OldId = r.Field<object>("ID").ToString(),
                        Code = r.Field<string>("CODE"),
                        Name = r.Field<string>("NAME"),
                        NameAlt = r.Field<string>("NAME_TRANS"),
                        Alias = null,
                        IsActive = r.Field<string>("VALID_YN") == "Y" ? true : false,
                        ParentValueId = null,
                        TextContentString = null
                    })
                .ToList();

            noms["organizationTypes"] = new Dictionary<string, NomValue>();
            foreach (var row in orgApexTypes)
            {
                noms["organizationTypes"][row.OldId] = row;
                nom.NomValues.Add(row);
            }

            List<string> addedCodes = orgApexTypes.Select(a => a.Code).ToList();

            var orgFmTypes = sqlConnection.CreateStoreCommand(@"select * from OrgGrp")
                .Materialize(r =>
                    new NomValue
                    {
                        OldId = null,
                        Code = r.Field<string>("Code"),
                        Name = r.Field<string>("Group Organization"),
                        NameAlt = r.Field<string>("Group Organization"),
                        Alias = null,
                        IsActive = true,
                        ParentValueId = null,
                        TextContentString = null
                    })
                .ToList();

            foreach (var row in orgFmTypes.Where(t => !addedCodes.Contains(t.Code)))
            {
                noms["organizationTypes"][Guid.NewGuid().ToString()] = row;
                nom.NomValues.Add(row);
            }
        }

        private void migrateAircraftCatAWsFm(INomRepository repo, SqlConnection conn)
        {
            Nom nom = repo.GetNom("aircraftCatAWsFm");
            var results = conn.CreateStoreCommand(@"SELECT [Category BG], [Category EN], Code FROM CatAW")
                .Materialize(r =>
                    new NomValue
                    {
                        OldId = null,
                        Code = r.Field<string>("Code"),
                        Name = r.Field<string>("Category BG"),
                        NameAlt = r.Field<string>("Category EN"),
                        Alias = null,
                        IsActive = true,
                        ParentValueId = null,
                        TextContentString = null
                    })
                .ToList();

            noms["aircraftCatAWsFm"] = new Dictionary<string, NomValue>();
            foreach (var row in results)
            {
                if (row.Code == "0" || row.Code == "BLANK")
                {
                    continue;
                }

                noms["aircraftCatAWsFm"][Guid.NewGuid().ToString()] = row; //no old id
                nom.NomValues.Add(row);
            }
        }

        private void migrateAircraftRegStatsesFm(INomRepository repo, SqlConnection conn)
        {
            var aliases = new Dictionary<string, string>()
            {
                { "1", "firstReg"},
                { "2", "lastActiveReg"},
                { "6", "rereged"},
                { "7", "expiredContract"},
                { "8", "changedOwnership"},
                { "9", "totaled"},
                { "11", "removed"}
            };

            var activeNomCodes = new List<string>(){
                "2", "6", "11"
            };

            Nom nom = repo.GetNom("aircraftRegStatsesFm");
            var results = conn.CreateStoreCommand(@"
                SELECT [Code] code, [Registration Status] name
                FROM [GvaAircraft].[dbo].[RegStatus]
                 WHERE code != '3' and code != '5' and code != '12' and code != '0'")
                .Materialize(r =>
                    new NomValue
                    {
                        OldId = r.Field<string>("code"),
                        Code = r.Field<string>("code"),
                        Name = r.Field<string>("name"),
                        NameAlt = null,
                        Alias = aliases.ContainsKey(r.Field<string>("code")) ? aliases[r.Field<string>("code")] : null,
                        IsActive = activeNomCodes.Contains(r.Field<string>("code")) ? true : false,
                        ParentValueId = null,
                        TextContentString = null
                    })
                .ToList();

            noms["aircraftRegStatsesFm"] = new Dictionary<string, NomValue>();
            foreach (var row in results)
            {
                noms["aircraftRegStatsesFm"][row.OldId] = row;
                int parsedCodeToInt = int.Parse(row.Code);
                if (parsedCodeToInt < 12 || parsedCodeToInt == 21)
                {
                    nom.NomValues.Add(row);
                }
            }
        }

        private void migrateCountriesFm(INomRepository repo, SqlConnection conn)
        {
            Nom nom = repo.GetNom("countriesFm");
            var results = conn.CreateStoreCommand(@"select * from Cnts")
                .Materialize(r =>
                    new NomValue
                    {
                        OldId = r.Field<string>("nCntsRecNo"),
                        Code = r.Field<string>("tISO"),
                        Name = r.Field<string>("tNameFullBG"),
                        NameAlt = r.Field<string>("tNameFullEN"),
                        Alias = null,
                        IsActive = true,
                        ParentValueId = null,
                        TextContentString = JsonConvert.SerializeObject(
                            new
                            {
                                code3 = r.Field<string>("tISO3"),
                                heading = r.Field<string>("tNamePrintBG"),
                                headingAlt = r.Field<string>("tNamePrintEN"),
                                shortName = r.Field<string>("tName"),
                            })
                    })
                .ToList();

            noms["countriesFm"] = new Dictionary<string, NomValue>();
            foreach (var row in results)
            {
                noms["countriesFm"][row.OldId] = row;
                nom.NomValues.Add(row);
            }
        }

        static void Main(string[] args)
        {
            OracleConnection oracleConn = new OracleConnection(oracleConnStr);
            SqlConnection sqlConn = new SqlConnection(sqlConnStr);

            oracleConn.Open();
            sqlConn.Open();

            var fmOrgs = sqlConn.CreateStoreCommand("select * from orgs")
            .Materialize(r =>
                new
                {
                    EIK = r.Field<string>("t_EIK_EGN"),
                    Name = r.Field<string>("tNameEN"),
                    Id = r.Field<int>("nOrgID")
                })
            .Where(o => !string.IsNullOrWhiteSpace(o.Name))
            //skip duplicates
            .GroupBy(o => o.Name, StringComparer.InvariantCultureIgnoreCase)
            .Select(g =>
                new FmOrg
                {
                    EIK = g.Select(r => r.EIK).Where(eik => !string.IsNullOrWhiteSpace(eik)).Distinct().SingleOrDefault(),
                    TrimmedName = TrimName(g.Key),
                    Name = g.Key,
                    Id = g.Select(r => r.Id).First()
                })
            .OrderBy(o => o.Name)
            .ToList();

            var ApexOrgs = oracleConn.CreateStoreCommand("select * from CAA_DOC.Firm")
            .Materialize(r =>
                new ApexOrg
            {
                Name = r.Field<string>("NAME_TRANS"),
                TrimmedName = TrimName(r.Field<string>("NAME_TRANS")),
                EIK = r.Field<string>("BULSTAT"),
                Id = r.Field<int>("ID")
            })
            .ToList();

            var ApexPersons = oracleConn.CreateStoreCommand("select * from CAA_DOC.Person")
            .Materialize(r =>
                new ApexPerson
            {
                Name = r.Field<string>("NAME_TRANS") + " " + r.Field<string>("SURNAME_TRANS") + " " + r.Field<string>("FAMILY_TRANS"),
                NameBg = r.Field<string>("NAME") + " " + r.Field<string>("SURNAME") + " " + r.Field<string>("FAMILY"),
                TrimmedName = TrimName(r.Field<string>("NAME_TRANS") + r.Field<string>("SURNAME_TRANS") + r.Field<string>("FAMILY_TRANS")),
                EIK = r.Field<string>("EGN"),
                Id = r.Field<int>("ID")
            })
            .ToList();

            var egnMatches =
                (from fmO in fmOrgs
                join p in ApexPersons on fmO.EIK equals p.EIK
                select new OrgMatch
                {
                    ApexPersonId = p.Id,
                    FmOrgName = fmO.Name,
                    EIK = fmO.EIK,
                    ApexPersonNameEn = p.Name,
                    ApexPersonNameBg = p.NameBg,
                    MatchType = "EGN"
                }).ToList();

            fmOrgs = fmOrgs.Where(fmO => !egnMatches.Any(m => m.FmOrgName == fmO.Name)).ToList();

            var eikMatches =
                (from fmO in fmOrgs
                join aO in ApexOrgs on fmO.EIK equals aO.EIK
                select new OrgMatch
                {
                    ApexOrgId = aO.Id,
                    FmOrgName = fmO.Name,
                    EIK = fmO.EIK,
                    ApexOrgNameEn = aO.Name,
                    MatchType = "EIK"
                }).ToList();

            fmOrgs = fmOrgs.Where(fmO => !eikMatches.Any(m => m.FmOrgName == fmO.Name)).ToList();

            var personNameMatches =
                (from fmO in fmOrgs
                join p in ApexPersons on fmO.Name equals p.Name
                select new OrgMatch
                {
                    ApexPersonId = fmO.Id,
                    FmOrgName = fmO.Name,
                    EIK = fmO.EIK,
                    ApexPersonNameEn = p.Name,
                    ApexPersonNameBg = p.NameBg,
                    MatchType = "Person Name"
                }).ToList();

            fmOrgs = fmOrgs.Where(fmO => !personNameMatches.Any(m => m.FmOrgName == fmO.Name)).ToList();

            var orgNameMatches =
                (from fmO in fmOrgs
                join aO in ApexOrgs on fmO.Name equals aO.Name
//.........这里部分代码省略.........

        private void migrateAircraftProducersFm(INomRepository repo, SqlConnection conn)
        {
            Nom nom = repo.GetNom("aircraftProducersFm");
            var results = conn.CreateStoreCommand(@"select * from Makers")
                .Materialize(r =>
                    new NomValue
                    {
                        OldId = r.Field<string>("nMakerID"),
                        Code = null,
                        Name = r.Field<string>("tNameEN"),
                        NameAlt = r.Field<string>("tNameEN"),
                        Alias = null,
                        IsActive = true,
                        ParentValueId = noms["countriesFm"].ByCode(r.Field<string>("tCntsISO")).NomValueId(),
                        TextContentString = JsonConvert.SerializeObject(
                            new
                            {
                                address = r.Field<string>("tAdrsStreetBG"),
                                addressCity = r.Field<string>("tAdrsCityBG"),
                                addressAlt = r.Field<string>("tAdrsStreetEN"),
                                addressCityAlt = r.Field<string>("tAdrsCityEN"),
                                website = r.Field<string>("t_WebSite"),
                                email = r.Field<string>("t_eMail"),
                                notes = r.Field<string>("tRemark"),
                                printName = r.Field<string>("t_PrintBG"),
                                printNameAlt = r.Field<string>("t_PrintEN"),
                            })
                    })
                .ToList();

            noms["aircraftProducersFm"] = new Dictionary<string, NomValue>();
            foreach (var row in results)
            {
                noms["aircraftProducersFm"][row.OldId] = row;
                nom.NomValues.Add(row);
            }
        }

        public Tuple<int, List<PersonReportDocumentDO>> GetDocuments(
            SqlConnection conn,
            int? roleId = null,
            DateTime? fromDatePeriodFrom = null,
            DateTime? fromDatePeriodTo = null,
            DateTime? toDatePeriodFrom = null,
            DateTime? toDatePeriodTo = null,
            int? typeId = null,
            int? lin = null,
            int? limitationId = null,
            string docNumber = null,
            string publisher = null,
            int? medClassId = null,
            string sortBy = null,
            int offset = 0,
            int limit = 10)
        {
            string limName = limitationId.HasValue ? this.nomRepository.GetNomValue(limitationId.Value).Name : null;
            Dictionary<string, string> sortByToTableColumn = new Dictionary<string, string>()
            {
                {"lin", "p.Lin"},
                {"role", "nv2.Name"},
                {"type", "nv1.Name"},
                {"valid", "d.Valid"},
                {"publisher", "d.Publisher"},
                {"number", "d.DocumentNumber"},
                {"medClass", "nv3.Name"},
                {"fromDate", "d.FromDate"},
                {"toDate", "d.ToDate"},
                {"limitations", "d.Limitations"},
            };
            string orderBy = !string.IsNullOrEmpty(sortBy) && sortByToTableColumn.ContainsKey(sortBy) ? sortByToTableColumn[sortBy] : "d.FromDate";

            var queryResult = conn.CreateStoreCommand(
                    @"SELECT COUNT(*) OVER() as allResultsCount,
                        p.LotId,
                        p.Lin,
                        nv2.Name as Role,
                        nv1.Name as Type,
                        d.DocumentNumber,
                        d.FromDate,
                        d.Date,
                        d.ToDate,
                        d.Publisher,
                        d.Valid,
                        d.Limitations,
                        nv3.Name as MedClass
                        FROM GvaViewPersonDocuments d
                        INNER JOIN GvaViewPersons p ON d.LotId = p.LotId
                        LEFT JOIN LotParts lp on lp.LotPartId = d.LotPartId
                        LEFT JOIN NomValues nv1 ON nv1.NomValueId = d.TypeId
                        LEFT JOIN NomValues nv2 ON nv2.NomValueId = d.RoleId
                        LEFT JOIN NomValues nv3 ON nv3.NomValueId = d.MedClassId
                    WHERE 1=1 {0} {1} {2} {3} {4} {5} {6} {7} {8} {9} {10}
                    ORDER BY " + orderBy + @" DESC
                    OFFSET {11} ROWS FETCH NEXT {12} ROWS ONLY",
                    new DbClause("and d.FromDate >= {0}", fromDatePeriodFrom),
                    new DbClause("and d.FromDate <= {0}", fromDatePeriodTo),
                    new DbClause("and d.ToDate >= {0}", toDatePeriodFrom),
                    new DbClause("and d.ToDate <= {0}", toDatePeriodTo),
                    new DbClause("and p.Lin = {0}", lin),
                    new DbClause("and nv1.NomValueId = {0}", typeId),
                    new DbClause("and nv2.NomValueId = {0}", roleId),
                    new DbClause("and d.DocumentNumber like '%' + {0} + '%'", docNumber),
                    new DbClause("and d.Publisher like '%' + {0} + '%'", publisher),
                    new DbClause("and (d.Limitations like {0} + '$$%' or d.Limitations like '%$$' + {0} or d.Limitations like '%$$' + {0} + '$$%' or d.Limitations like {0})", limName),
                    new DbClause("and nv3.NomValueId = {0}", medClassId),
                    new DbClause("{0}", offset),
                    new DbClause("{0}", limit))
                    .Materialize(r => new Tuple<int, PersonReportDocumentDO>
                        (
                        r.Field<int>("allResultsCount"),
                        new PersonReportDocumentDO()
                        {
                            LotId = r.Field<int>("LotId"),
                            Lin = r.Field<int?>("Lin"),
                            Role = r.Field<string>("Role"),
                            Type = r.Field<string>("Type"),
                            Number = r.Field<string>("DocumentNumber"),
                            FromDate = r.Field<DateTime?>("FromDate") ?? r.Field<DateTime?>("Date"),
                            ToDate = r.Field<DateTime?>("ToDate"),
                            Valid = r.Field<bool?>("Valid"),
                            Publisher = r.Field<string>("Publisher"),
                            Limitations = !string.IsNullOrEmpty(r.Field<string>("Limitations")) ? r.Field<string>("Limitations").Replace(GvaConstants.ConcatenatingExp, ", ") : null,
                            MedClass = r.Field<string>("MedClass")
                        }))
                        .ToList();

            var results = queryResult.Select(q => q.Item2).ToList();
            int count = queryResult.Select(q => q.Item1).FirstOrDefault();

            return new Tuple<int, List<PersonReportDocumentDO>>(count, results);
        }

        public Tuple<int, List<PersonReportRatingDO>> GetRatings(
            SqlConnection conn,
            DateTime? fromDatePeriodFrom = null,
            DateTime? fromDatePeriodTo = null,
            DateTime? toDatePeriodFrom = null,
            DateTime? toDatePeriodTo = null,
            int? ratingClassId = null,
            int? authorizationId = null,
            int? aircraftTypeCategoryId = null,
            int? lin = null,
            int? limitationId = null,
            int? ratingTypeId = null,
            string sortBy = null,
            int? showAllPerPersonId = null,
            int offset = 0,
            int limit = 10)
        {
            string limCode = limitationId.HasValue ? this.nomRepository.GetNomValue(limitationId.Value).Code : null;
            string ratingTypeCode = ratingTypeId.HasValue ? this.nomRepository.GetNomValue(ratingTypeId.Value).Code : null;
            bool showAllPerPerson = showAllPerPersonId.HasValue ? this.nomRepository.GetNomValue(showAllPerPersonId.Value).Code == "Y" : false;

            Dictionary<string, string> sortByToTableColumn = new Dictionary<string, string>()
            {
                {"lin", "p.Lin"},
                {"fromDate", "re.DocDateValidFrom"},
                {"toDate", "re.DocDateValidTo"},
                {"firstIssueDate", "re2.DocDateValidFrom"},
                {"personRatingLevel", "rl.Code"},
                {"ratingTypes", "r.RatingTypes"},
                {"locationIndicator", "li.Code"},
                {"sector", "r.Sector"},
                {"limitations", "re.Limitations"},
                {"authorizationCode", "a.Code"},
            };

            string orderBy = !string.IsNullOrEmpty(sortBy) && sortByToTableColumn.ContainsKey(sortBy) ? sortByToTableColumn[sortBy] : "re.DocDateValidFrom";
            string selectQuery = @"SELECT
                            COUNT(*) OVER() as allResultsCount,
                            p.Lin,
                            r.LotId,
                            lastEdition.RatingPartIndex,
                            re2.DocDateValidFrom AS firstIssueDate,
                            re.RatingSubClasses,
                            re.Limitations,
                            re.DocDateValidFrom,
                            re.DocDateValidTo,
                            r.RatingTypes,
                            r.Sector,
                            rc.Code as RatingClass,
                            a.Code as AuthorizationCode,
                            ct.Code as AircraftTypeCategory,
                            atg.Code as AircraftTypeGroup,
                            li.Code as LocationIndicator,
                            rl.Code as RatingLevel,
                            r.PartIndex
                        FROM  GvaViewPersonRatings r
                        INNER JOIN GvaViewPersons p ON r.LotId = p.LotId
                        INNER JOIN (select
                            r.LotId,
                            max(re.PartIndex) as edition_part_index,
                            re.RatingPartIndex
                            FROM  GvaViewPersonRatings r
                            INNER JOIN GvaViewPersonRatingEditions re on r.LotId = re.LotId and r.PartIndex = re.RatingPartIndex
                            group by re.RatingPartIndex, r.LotId) lastEdition on lastEdition.LotId = r.LotId and lastEdition.RatingPartIndex = r.PartIndex
                        INNER JOIN GvaViewPersonRatingEditions re on lastEdition.LotId = re.LotId and re.PartIndex = lastEdition.edition_part_index
                        INNER JOIN (select
                            r.LotId,
                            min(re.PartIndex) AS edition_part_index,
                            re.RatingPartIndex
                            FROM  GvaViewPersonRatings r
                            INNER JOIN GvaViewPersonRatingEditions re on r.LotId = re.LotId and r.PartIndex = re.RatingPartIndex
                            group by re.RatingPartIndex, r.LotId) firstEdition on firstEdition.LotId = lastEdition.LotId and firstEdition.RatingPartIndex = lastEdition.RatingPartIndex
                        INNER JOIN GvaViewPersonRatingEditions re2 on firstEdition.LotId = re2.LotId and re2.PartIndex = firstEdition.edition_part_index
                        LEFT JOIN NomValues rc ON rc.NomValueId = r.RatingClassId
                        LEFT JOIN NomValues a ON a.NomValueId = r.AuthorizationId
                        LEFT JOIN NomValues ct ON ct.NomValueId = r.AircraftTypeCategoryId
                        LEFT JOIN NomValues atg ON atg.NomValueId = r.AircraftTypeGroupId
                        LEFT JOIN NomValues li ON li.NomValueId = r.LocationIndicatorId
                        LEFT JOIN NomValues rl ON rl.NomValueId = r.RatingLevelId ";

            List<DbClause> clauses = new List<DbClause>()
            {
                new DbClause("and re.DocDateValidFrom >= {0}", fromDatePeriodFrom),
                new DbClause("and re.DocDateValidFrom <= {0}", fromDatePeriodTo),
                new DbClause("and re.DocDateValidTo >= {0}", toDatePeriodFrom),
                new DbClause("and re.DocDateValidTo <= {0}", toDatePeriodTo),
                new DbClause("and p.Lin = {0}", lin),
                new DbClause("and r.RatingClassId = {0}", ratingClassId),
                new DbClause("and r.AuthorizationId = {0}", authorizationId),
                new DbClause("and r.AircraftTypeGroupId = {0}", aircraftTypeCategoryId),
                new DbClause("and (re.Limitations like {0} + '$$%' or re.Limitations like '%$$' + {0} or re.Limitations like '%$$' + {0} + '$$%' or re.Limitations like {0})", limCode),
                new DbClause("and (r.RatingTypes like {0} + ', %' or r.RatingTypes like '%, ' + {0} or r.RatingTypes like '%, ' + {0} + ',%' or r.RatingTypes like {0})", ratingTypeCode)
            };

            DbCommand command = null;
            if (showAllPerPerson)
            {
                var filteredResults = conn.CreateStoreCommand(selectQuery +
                    @"WHERE 1=1 {0} {1} {2} {3} {4} {5} {6} {7} {8} {9}",
                        clauses.ToArray())
//.........这里部分代码省略.........

        public List<PersonReportPaperDO> GetPapers(
                    SqlConnection conn,
                    int? paperId)
        {
            List<PersonReportPaperDO> paperResults = new List<PersonReportPaperDO>();

            var issuedPaperData = conn.CreateStoreCommand(@"SELECT
                                    p.GvaPaperId,
                                    p.Name,
                                    p.FirstNumber,
                                    p.FromDate,
                                    p.ToDate,
                                    ple.StampNumber
                                    FROM GvaViewPersonLicenceEditions ple
                                    RIGHT JOIN GvaPapers p ON ple.PaperId = p.GvaPaperId
                                    WHERE 1=1 {0}",
                                    new DbClause("and p.GvaPaperId = {0}", paperId)
                        )
                        .Materialize(r =>
                            new
                            {
                                PaperId = r.Field<int>("GvaPaperId"),
                                PaperName = r.Field<string>("Name"),
                                FirstNumber = r.Field<int>("FirstNumber"),
                                FromDate = r.Field<DateTime>("FromDate"),
                                ToDate = r.Field<DateTime>("ToDate"),
                                StampNumber = r.Field<string>("StampNumber")
                            }).ToList();

            foreach (var papers in issuedPaperData.GroupBy(p => p.PaperId))
            {
                var allStampNumbersForSplit = papers.Where(p => p.StampNumber != null && p.StampNumber.Contains(GvaConstants.ConcatenatingExp))
                    .SelectMany(p => p.StampNumber.Split(GvaConstants.ConcatenatingExp.ToArray()))
                    .Where(sn => !string.IsNullOrEmpty(sn))
                    .ToList();

                var allOtherStampNumbers = papers.Where(p => p.StampNumber != null && !p.StampNumber.Contains(GvaConstants.ConcatenatingExp))
                    .Select(p => p.StampNumber)
                    .ToList();

                var allNumbers = allStampNumbersForSplit.Union(allOtherStampNumbers);

                var paperInfo = papers.First();
                int? maxNumber = allNumbers.Count() > 0 ? allNumbers.Max(sn => int.Parse(sn.Trim())) : (int?)null;
                int issuedCount = allNumbers.Count();
                int skippedCount = maxNumber.HasValue ? maxNumber.Value - (paperInfo.FirstNumber + issuedCount) + 1 : 0;

                paperResults.Add(new PersonReportPaperDO()
                    {
                        PaperId = paperInfo.PaperId,
                        PaperName = paperInfo.PaperName,
                        FirstNumber = paperInfo.FirstNumber,
                        IssuedCount = maxNumber != null ? issuedCount : 0,
                        SkippedCount = skippedCount,
                        LastIssuedNumber = maxNumber,
                        FromDate = paperInfo.FromDate,
                        ToDate = paperInfo.ToDate
                    });
            }

            return paperResults;
        }

        public Tuple<int, List<PersonReportLicenceDO>> GetLicences(
            SqlConnection conn,
            DateTime? fromDatePeriodFrom = null,
            DateTime? fromDatePeriodTo = null,
            DateTime? toDatePeriodFrom = null,
            DateTime? toDatePeriodTo = null,
            int? lin = null,
            int? licenceTypeId = null,
            int? licenceActionId = null,
            int? limitationId = null,
            string sortBy = null,
            int offset = 0,
            int limit = 10)
        {
            string limName = limitationId.HasValue ? this.nomRepository.GetNomValue(limitationId.Value).Name : null;
            Dictionary<string, string> sortByToTableColumn = new Dictionary<string, string>()
            {
                {"lin", "p.Lin"},
                {"uin", "p.Uin"},
                {"licenceTypeName", "lt.Name"},
                {"licenceCode", "l.PublisherCode + ' ' + l.LicenceTypeCaCode + ' ' + RIGHT('00000' + CAST(l.LicenceNumber AS NVARCHAR(5)),5)"},
                {"names", "p.Names"},
                {"fromDate", "le.DateValidFrom"},
                {"toDate", "le.DateValidTo"},
                {"firstIssueDate", "le.FirstDocDateValidFrom"},
                {"licenceAction", "d.ToDate"},
                {"limitations", "le.Limitations"},
                {"stampNumber", "le.StampNumber"}
            };
            string orderBy = !string.IsNullOrEmpty(sortBy) && sortByToTableColumn.ContainsKey(sortBy) ? sortByToTableColumn[sortBy] : "le.DateValidFrom";

            var queryResult = conn.CreateStoreCommand(
                        @"SELECT
                             COUNT(*) OVER() as allResultsCount,
                             p.LotId,
                             p.Lin,
                             p.Uin AS uin,
                             p.Names,
                             lt.Name AS LicenceTypeName,
                             l.PublisherCode + ' ' + l.LicenceTypeCaCode + ' ' + RIGHT('00000' + CAST(l.LicenceNumber AS NVARCHAR(5)),5) AS LicenceCode,
                             le.DateValidFrom,
                             le.DateValidTo,
                             le.FirstDocDateValidFrom AS FirstIssueDate,
                             la.Name AS LicenceAction,
                             le.StampNumber,
                             le.Limitations
                         FROM
                         GvaViewPersonLicenceEditions le
                         INNER JOIN GvaViewPersonLicences l ON le.LotId = l.LotId and le.LicencePartIndex = l.PartIndex
                         INNER JOIN GvaViewPersons p ON l.LotId = p.LotId
                         INNER JOIN NomValues lt ON lt.NomValueId = l.LicenceTypeId
                         INNER JOIN NomValues la ON la.NomValueId = le.LicenceActionId
                         WHERE 1=1 {0} {1} {2} {3} {4} {5} {6} {7}
                         ORDER BY " + orderBy + @" DESC
                         OFFSET {8} ROWS FETCH NEXT {9} ROWS ONLY",
                        new DbClause("and le.DateValidFrom >= {0}", fromDatePeriodFrom),
                        new DbClause("and le.DateValidFrom <= {0}", fromDatePeriodTo),
                        new DbClause("and le.DateValidTo >= {0}", toDatePeriodFrom),
                        new DbClause("and le.DateValidTo <= {0}", toDatePeriodTo),
                        new DbClause("and p.Lin = {0}", lin),
                        new DbClause("and lt.NomValueId = {0}", licenceTypeId),
                        new DbClause("and la.NomValueId = {0}", licenceActionId),
                        new DbClause("and (le.Limitations like {0} + '$$%' or le.Limitations like '%$$' + {0} or le.Limitations like '%$$%' + {0} + '$$' or le.Limitations like {0})", limName),
                        new DbClause("{0}", offset),
                        new DbClause("{0}", limit))
                         .Materialize(r => new Tuple<int, PersonReportLicenceDO>
                             (
                             r.Field<int>("allResultsCount"),
                             new PersonReportLicenceDO()
                             {
                                 LotId = r.Field<int>("lotId"),
                                 Lin = r.Field<int?>("lin"),
                                 Uin = r.Field<string>("uin"),
                                 Names = r.Field<string>("names"),
                                 LicenceTypeName = r.Field<string>("licenceTypeName"),
                                 LicenceCode = r.Field<string>("licenceCode"),
                                 DateValidFrom = r.Field<DateTime?>("dateValidFrom"),
                                 DateValidTo = r.Field<DateTime?>("dateValidTo"),
                                 FirstIssueDate = r.Field<DateTime?>("firstIssueDate"),
                                 LicenceAction = r.Field<string>("licenceAction"),
                                 StampNumber = r.Field<string>("stampNumber"),
                                 Limitations = !string.IsNullOrEmpty(r.Field<string>("limitations")) ? r.Field<string>("limitations").Replace(GvaConstants.ConcatenatingExp, ", ") : null
                             }))
                            .ToList();

            var results = queryResult.Select(q => q.Item2).ToList();
            int count = queryResult.Select(q => q.Item1).FirstOrDefault();

            return new Tuple<int, List<PersonReportLicenceDO>>(count, results);
        }