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 select new OrgMatch { FmOrgId = fmO.Id, FmOrgName = fmO.Name, EIK = fmO.EIK, ApexOrgNameEn = aO.Name, MatchType = "Org Name" }).ToList(); fmOrgs = fmOrgs.Where(fmO => !orgNameMatches.Any(m => m.FmOrgName == fmO.Name)).ToList(); var personTrimmedNameMatches = (from fmO in fmOrgs join p in ApexPersons on fmO.TrimmedName equals p.TrimmedName select new OrgMatch { ApexPersonId = fmO.Id, FmOrgName = fmO.Name, EIK = fmO.EIK, ApexPersonNameEn = p.Name, ApexPersonNameBg = p.NameBg, MatchType = "Person Trimmed Name" }).ToList(); fmOrgs = fmOrgs.Where(fmO => !personTrimmedNameMatches.Any(m => m.FmOrgName == fmO.Name)).ToList(); var orgTrimmedNameMatches = (from fmO in fmOrgs join aO in ApexOrgs on fmO.TrimmedName equals aO.TrimmedName select new OrgMatch { FmOrgId = fmO.Id, ApexOrgId = aO.Id, FmOrgName = fmO.Name, EIK = fmO.EIK, ApexOrgNameEn = aO.Name, MatchType = "Org Trimmed Name" }).ToList(); fmOrgs = fmOrgs.Where(fmO => !orgTrimmedNameMatches.Any(m => m.FmOrgName == fmO.Name)).ToList(); var matches = egnMatches .Concat(eikMatches) .Concat(personNameMatches) .Concat(orgNameMatches) .Concat(personTrimmedNameMatches) .Concat(orgTrimmedNameMatches) //TODO take the first org in case of duplicated names, should be investigated .GroupBy(m => m.FmOrgName) .Select(g => g.First()); #region Fill Excell Workbook book = new Workbook(); Worksheet sheet = book.Worksheets.Add("Organizations"); WorksheetRow row = sheet.Table.Rows.Add(); row.Cells.Add("Match Type"); row.Cells.Add("EIK"); row.Cells.Add("Organization FM"); row.Cells.Add("Organization FM Id"); row.Cells.Add("Organization Apex"); row.Cells.Add("Organization Apex Id"); row.Cells.Add("Person Apex"); row.Cells.Add("Person Apex Id"); foreach (var m in matches) { WorksheetRow nextRow = sheet.Table.Rows.Add(); nextRow.Cells.Add(m.MatchType); nextRow.Cells.Add(m.EIK); nextRow.Cells.Add(m.FmOrgName); nextRow.Cells.Add(m.FmOrgId.HasValue? m.FmOrgId.Value.ToString() : ""); nextRow.Cells.Add(m.ApexOrgNameEn); nextRow.Cells.Add(m.ApexOrgId.HasValue ? m.ApexOrgId.Value.ToString() : ""); if (!string.IsNullOrEmpty(m.ApexPersonNameEn) || !string.IsNullOrEmpty(m.ApexPersonNameBg)) { nextRow.Cells.Add(m.ApexPersonNameEn + "(" + m.ApexPersonNameBg + ")"); } else { nextRow.Cells.Add(""); } nextRow.Cells.Add(m.ApexPersonId.HasValue ? m.ApexPersonId.Value.ToString() : ""); } foreach (var org in fmOrgs) { WorksheetRow nextRow = sheet.Table.Rows.Add(); nextRow.Cells.Add("No Match"); nextRow.Cells.Add(org.EIK); nextRow.Cells.Add(org.Name); nextRow.Cells.Add(org.Id.ToString()); } #endregion book.Save(@"Organizations.xls"); int matched = matches.Count(); int unmatched = fmOrgs.Count(); Console.WriteLine(matched + "/" + (matched + unmatched)); }
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()) .Materialize(r => new { LotId = r.Field<int>("lotId"), }) .Select(l => l.LotId) .ToArray(); command = conn.CreateStoreCommand(selectQuery + @"WHERE 1=1 {0} ORDER BY " + orderBy + @" DESC OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", new DbClause("and r.LotId in " + string.Format("({0})", string.Join(",", filteredResults))), new DbClause("{0}", offset), new DbClause("{0}", limit)); } else { clauses = clauses .Union(new List<DbClause> { new DbClause("{0}", offset), new DbClause("{0}", limit) }) .ToList(); command = conn.CreateStoreCommand(selectQuery + @"WHERE 1=1 {0} {1} {2} {3} {4} {5} {6} {7} {8} {9} ORDER BY " + orderBy + @" DESC OFFSET {10} ROWS FETCH NEXT {11} ROWS ONLY", clauses.ToArray()); } var queryResult = command .Materialize(r => new Tuple<int, PersonReportRatingDO> ( r.Field<int>("allResultsCount"), new PersonReportRatingDO() { Lin = r.Field<int?>("lin"), LotId = r.Field<int>("lotId"), PartIndex = r.Field<int>("partIndex"), RatingSubClasses = r.Field<string>("RatingSubClasses"), Limitations = !string.IsNullOrEmpty(r.Field<string>("Limitations")) ? r.Field<string>("Limitations").Replace(GvaConstants.ConcatenatingExp, ", ") : null, FirstIssueDate = r.Field<DateTime?>("FirstIssueDate"), DateValidFrom = r.Field<DateTime?>("DocDateValidFrom"), DateValidTo = r.Field<DateTime?>("DocDateValidTo"), RatingTypes = r.Field<string>("RatingTypes"), Sector = r.Field<string>("Sector"), RatingClass = r.Field<string>("RatingClass"), AuthorizationCode = r.Field<string>("AuthorizationCode"), AircraftTypeCategory = r.Field<string>("AircraftTypeCategory"), AircraftTypeGroup = r.Field<string>("AircraftTypeGroup"), LocationIndicator = r.Field<string>("LocationIndicator"), RatingLevel = r.Field<string>("RatingLevel") })) .ToList(); var ratings = queryResult.Select(q => q.Item2).ToList(); int count = queryResult.Select(q => q.Item1).FirstOrDefault(); return new Tuple<int, List<PersonReportRatingDO>>(count, ratings); }
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); }
static int Main(string[] args) { if (args.Length == 0) { Console.WriteLine("Invalid arguments."); Console.WriteLine("Usage: Gva.ProjectionRebuilder <setAlias> <projectionName>"); return 1; } string setAlias = args[0]; string projectionName = args[1]; var builder = new ContainerBuilder(); builder.RegisterModule(new CommonModule()); builder.RegisterModule(new CommonApiModule()); builder.RegisterModule(new DocsApiModule()); builder.RegisterModule(new GvaApiModule()); builder.RegisterModule(new RegsApiModule()); builder.Register(c => new UnathorizedUserContext()).As<UserContext>().SingleInstance(); var container = builder.Build(); Stopwatch timer = new Stopwatch(); timer.Start(); List<int> lotIds; using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DbContext"].ConnectionString)) { lotIds = conn.CreateStoreCommand(@" select l.LotId from Lots l inner join LotSets ls on ls.LotSetId = l.LotSetId where {0}", new DbClause("ls.Alias = {0}", setAlias)) .Materialize(r => r.Field<int>("LotId")) .ToList(); } Console.WriteLine("Found {0} lots.", lotIds.Count); int done = 0; foreach (var lotId in lotIds) { using (var scope = container.BeginLifetimeScope()) { try { var unitOfWork = scope.Resolve<IUnitOfWork>(); var lotRepository = scope.Resolve<ILotRepository>(); var projection = scope .Resolve<ILotEventHandler[]>() .Where(h => h is IProjection && h.GetType().FullName == projectionName) .Cast<IProjection>() .Single(); var lot = lotRepository.GetLotIndex(lotId, fullAccess: true); projection.RebuildLot(lot); unitOfWork.Save(); } catch (Exception e) { Console.WriteLine("Error at lotId: {0}.", lotId); Console.WriteLine(e); } } done++; if (done % 100 == 0) { Console.WriteLine("Done {0} lots.", done); } } Console.WriteLine("Done {0} lots.", done); timer.Stop(); Console.WriteLine("Rebuilding completed in - {0} minutes.", timer.Elapsed.TotalMinutes); return 0; }