private void migrateCaa(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("caa");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.CAA")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = r.Field<string>("CODE") == "BG" ? "BGR" : r.Field<string>("CODE"),
Name = r.Field<string>("NAME"),
NameAlt = r.Field<string>("NAME_TRANS"),
Alias = r.Field<string>("CODE") == "BG" ? "BGR" : r.Field<string>("CODE"),
IsActive = r.Field<string>("VALID_YN") == "Y" ? true : false,
ParentValueId = noms["countries"].ByOldId(r.Field<decimal?>("COUNTRY_ID").ToString()).NomValueId(),
TextContentString = JsonConvert.SerializeObject(
new
{
heading = r.Field<string>("HEADING"),
headingAlt = r.Field<string>("HEADING_TRANS"),
subHeading = r.Field<string>("SUBHEADING"),
subHeadingAlt = r.Field<string>("SUBHEADING_TRANS"),
})
})
.ToList();
noms["caa"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["caa"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateAircraftCategories(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("aircraftCategories");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_AC_CATEGORY")
.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();
results.Add(new NomValue() {
OldId = "11",
Code = "VP",
Name = "Мотопарапланер",
NameAlt = "Paramotor-Trike",
Alias = null,
IsActive = true,
ParentValueId = null,
TextContentString = null
});
results.Add(new NomValue() {
OldId = "12",
Code = "GR",
Name = "Автожир",
NameAlt = "Gyroplane",
Alias = null,
IsActive = true,
ParentValueId = null,
TextContentString = null
});
noms["aircraftCategories"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["aircraftCategories"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateAircraftOccurrenceClasses(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("aircraftOccurrenceClasses");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_SHORT_LISTS where domain = 'INCIDENT_CLASS'")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = r.Field<string>("CODE"),
Name = r.Field<string>("MEANING"),
NameAlt = r.Field<string>("MEANING_TRANS"),
Alias = null,
IsActive = true,
ParentValueId = null,
TextContentString = null
})
.ToList();
noms["aircraftOccurrenceClasses"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["aircraftOccurrenceClasses"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateRatingSubClasses(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("ratingSubClasses");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_SUBCLASS_ATSM")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = r.Field<string>("CODE"),
Name = r.Field<string>("DESCRIPTION"),
NameAlt = null,
Alias = null,
IsActive = r.Field<string>("VALID_YN") == "Y" ? true : false,
ParentValueId = noms["ratingClasses"].ByOldId(r.Field<decimal>("CLASS_ID").ToString()).NomValueId(),
TextContentString = null
})
.ToList();
noms["ratingSubClasses"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["ratingSubClasses"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateSchools(INomRepository repo, OracleConnection conn)
{
Func<string, int?> getGradId = (oldId) => noms["graduations"].ByOldId(oldId).NomValueId();
Nom nom = repo.GetNom("schools");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.SCHOOL")
.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 = JsonConvert.SerializeObject(
new
{
isPilotTraining = r.Field<string>("PILOT_TRAINING")== "Y" ? true : false,
graduationId = getGradId(r.Field<decimal?>("GRADUATION_ID").ToString()),
graduationIds = r.Field<string>("GRADUATION_ID_LIST")
.Split(':')
.Select(gi => getGradId(gi))
.Where(gi => gi.HasValue)
.Select(gi => gi.Value)
.ToArray()
})
})
.GroupBy(r => r.Name)
.ToList();
noms["schools"] = new Dictionary<string, NomValue>();
foreach (var rows in results)
{
foreach (var row in rows)
{
noms["schools"][row.OldId] = rows.First();
}
nom.NomValues.Add(rows.First());
}
}
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 migratePersonStatusTypes(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("personStatusTypes");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_SHORT_LISTS where domain = 'STATE_REASON'")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = r.Field<string>("CODE"),
Name = r.Field<string>("MEANING"),
NameAlt = r.Field<string>("MEANING_TRANS"),
Alias = null,
IsActive = r.Field<string>("CODE") != "BR" ? true : false,
ParentValueId = null,
TextContentString = null
})
.ToList();
noms["personStatusTypes"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["personStatusTypes"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateEmploymentCategories(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("employmentCategories");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_JOB_CATEGORY")
.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"),
ParentValueId = (r.Field<decimal?>("STAFF_TYPE_ID") != null) ? noms["staffTypes"][r.Field<decimal?>("STAFF_TYPE_ID").ToString()].NomValueId : (int?)null,
Alias = null,
TextContentString = JsonConvert.SerializeObject(
new
{
codeCA = r.Field<string>("CA_CODE"),
dateValidFrom = r.Field<DateTime?>("DATE_FROM"),
dateValidTo = r.Field<DateTime?>("DATE_TO")
}),
IsActive = true
})
.ToList();
noms["employmentCategories"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["employmentCategories"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateGenders(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("gender");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_SEX")
.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"),
ParentValueId = null,
Alias = r.Field<string>("NAME_TRANS"),
TextContentString = null,
IsActive = true
})
.ToList();
noms["gender"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["gender"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateDocumentRoles(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("documentRoles");
var categoryAliases = new Dictionary<string, string>()
{
{ "T", "check" },
{ "O", "training"},
{ "A", "other"},
{ "P", "docId"},
{ "F", "flying"},
{ "E", "graduation"}
};
Func<string, string, string> getCategoryAliases = (categoryCode, code) =>
{
var categoryAlias = categoryAliases[categoryCode];
if (categoryAlias == "training" && (code == "ENG" || code == "BG"))
{
return "languageCert";
}
return categoryAlias;
};
var roleAliases = new Dictionary<string, string>()
{
{ "ENG", "engCert" },
{ "BG", "bgCert" },
{ "BTT", "basicTrainingTheorExam" },
{ "RT1", "ratingTrainingTheorExam" },
{ "RT2", "ratingTrainingPractExam" },
{ "1", "flyingCheck" },
{ "3", "diploma" },
{ "4", "theoreticalTraining" },
{ "5", "flyingTraining" },
{ "6", "exam" },
{ "7", "simulator" },
{ "08", "education" },
{ "15", "practicalCheck" },
{ "25", "practicalTraining" },
{ "47A", "accessOrderPractEduc" },
{ "48A", "accessOrderWorkAlone" },
{ "49A", "checkAtWork" },
{ "50A", "theorExamTransEducation" },
{ "51A", "practExamPrelimEduc" },
{ "52A", "RPcert" },
{ "53", "practExamToGainAccess" },
{ "54", "practicalExams" }
};
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_DOCUMENT_ROLE")
.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 = roleAliases.ContainsKey(r.Field<string>("CODE")) ? roleAliases[r.Field<string>("CODE")] : null,
IsActive = r.Field<string>("VALID_YN") == "Y" ? true : false,
ParentValueId = null,
TextContentString = JsonConvert.SerializeObject(
new
{
caseTypeAlias =
noms["staffTypes"]
.ByCodeOrDefault(
noms["directions"]
.ByOldId(r.Field<string>("ID_DIRECTION"))
.Code())
.Alias(),
isPersonsOnly = r.Field<string>("PERSON_ONLY") == "Y" ? true : false,
categoryAlias = getCategoryAliases(r.Field<string>("CATEGORY_CODE"), r.Field<string>("CODE"))
})
})
.ToList();
results.Add(new NomValue()
{
OldId = "0",
Name = "Вътрешна проверка",
NameAlt = "Вътрешна проверка",
Alias = null,
IsActive = true,
TextContentString = JsonConvert.SerializeObject(
new
{
direction = 6393,
caseTypeAlia = "flightCrew",
isPersonsOnly = true,
categoryCode = "T",
categoryAlias = "check"
})
});
var systemTypes = new List<Tuple<string, string, string>>()
{
{ new Tuple<string,string, string>("Образование" , "education" , "personEducation")},
{ new Tuple<string,string, string>("Месторабота" , "employment" , "personEmployment")},
{ new Tuple<string,string, string>("Документ за самоличност" , "documentId" , "personDocumentId")},
{ new Tuple<string,string, string>("Лиценз" , "licence" , "personLicence")},
{ new Tuple<string,string, string>("Медицинско свидетелство" , "medical" , "personMedical")},
{ new Tuple<string,string, string>("Заявление" , "application", "personApplication")},
{ new Tuple<string,string, string>("Отчет" , "report" , "personReport")},
{ new Tuple<string,string, string>("Състояние" , "status" , "personStatus")}
};
foreach (var type in systemTypes)
{
results.Add(new NomValue()
{
OldId = null,
Code = null,
Name = type.Item1,
NameAlt = type.Item2,
Alias = type.Item3,
IsActive = true,
ParentValueId = null,
TextContentString = JsonConvert.SerializeObject(
new
{
isPersonsOnly = true,
categoryAlias = "system"
})
});
}
noms["documentRoles"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
if (row.OldId != null)
{
noms["documentRoles"][row.OldId] = row;
}
nom.NomValues.Add(row);
}
}
private void migrateDocumentTypes(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("documentTypes");
string[] codes = {"3","4","5","115"};
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_DOCUMENT_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 = JsonConvert.SerializeObject(
new
{
caseTypeAlias =
noms["staffTypes"]
.ByCodeOrDefault(
noms["directions"]
.ByOldId(r.Field<string>("ID_DIRECTION"))
.Code())
.Alias(),
isPersonsOnly = r.Field<string>("PERSON_ONLY") == "Y" ? true : false,
isIdDocument = codes.Contains(r.Field<string>("CODE")) ? true : false
})
})
.ToList();
noms["documentTypes"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["documentTypes"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateDirections(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("directions");
var nameAlts = new Dictionary<string, string>()
{
{ "F", "Ekipaji" },
{ "T", "OVD"},
{ "G", "TO(AML)"},
{ "M", "TO(SUVD)"},
{ "C", "VS"},
{ "O", "Organizacii"},
};
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_DIRECTION")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = r.Field<string>("ID"),
Name = r.Field<string>("NAME"),
NameAlt = nameAlts[r.Field<string>("ID")],
Alias = null,
IsActive = true,
ParentValueId = null,
TextContentString = null
})
.ToList();
noms["directions"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["directions"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateCountires(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("countries");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_COUNTRY")
.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"),
ParentValueId = null,
Alias = r.Field<string>("CODE") == "BG" ? "BG" : null,
TextContentString = JsonConvert.SerializeObject(
new {
NationalityCodeCA = r.Field<string>("CA_NATIONALITY_CODE"),
Heading = r.Field<string>("HEADING"),
HeadingAlt = r.Field<string>("HEADING_TRANS"),
LicenceCodeCA = r.Field<string>("CA_LICENCE_CODE"),
}),
IsActive = true
})
.GroupBy(r => r.Name)
.ToList();
noms["countries"] = new Dictionary<string, NomValue>();
foreach (var rows in results)
{
foreach(var row in rows)
{
noms["countries"][row.OldId] = rows.First();
}
nom.NomValues.Add(rows.First());
}
}
private void migrateCities(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("cities");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_TOWN_VILLAGE")
.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"),
ParentValueId = noms["countries"].ByOldId(r.Field<decimal?>("COUNTRY_ID").ToString()).NomValueId(),
Alias = null,
TextContentString = JsonConvert.SerializeObject(
new
{
type = r.Field<string>("TV_TYPE"),
notes = r.Field<string>("NOTES"),
postCode = r.Field<string>("POSTAL_CODE_DEFAULT"),
oblCode = r.Field<string>("OBL_CODE"),
obstCode = r.Field<string>("OBST_CODE")
}),
IsActive = r.Field<string>("VALID_YN") == "Y" ? true : false
})
.GroupBy(r => r.Name)
.ToList();
noms["cities"] = new Dictionary<string, NomValue>();
foreach (var rows in results)
{
foreach (var row in rows)
{
noms["cities"][row.OldId] = rows.First();
}
nom.NomValues.Add(rows.First());
}
}
private void migrateLocationIndicators(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("locationIndicators");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_INDICATOR")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = r.Field<string>("CODE"),
Name = r.Field<string>("POSITION"),
NameAlt = r.Field<string>("POSITION_TRANS"),
Alias = null,
IsActive = true,
ParentValueId = null,
TextContentString = JsonConvert.SerializeObject(
new
{
type = r.Field<string>("FLAG_YN"),
})
})
.ToList();
noms["locationIndicators"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["locationIndicators"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateLangLevels(INomRepository repo, OracleConnection conn)
{
var roleAliases = new Dictionary<string, string[]>()
{
{ "L4", new string[] { "bgCert", "engCert" } },
{ "L5", new string[] { "bgCert", "engCert" } },
{ "L6", new string[] { "bgCert", "engCert" } },
{ " L4", new string[] { "engCert" } },
{ " L5", new string[] { "engCert" } },
{ " L6", new string[] { "engCert" } },
{ "B4", new string[] { "bgCert" } },
{ "B5", new string[] { "bgCert" } },
{ "B6", new string[] { "bgCert" } },
};
Nom nom = repo.GetNom("langLevels");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_ENG_LANG")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = r.Field<string>("CODE"),
Name = r.Field<string>("LANG_LEVEL"),
NameAlt = r.Field<string>("LANG_LEVEL_TRANS"),
Alias = null,
IsActive = true,
ParentValueId = null,
TextContentString = JsonConvert.SerializeObject(
new
{
roleAliases = roleAliases[r.Field<string>("CODE")],
seqNumber = r.Field<decimal?>("SEQ_NUMBER").ToString(),
})
})
.ToList();
noms["langLevels"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["langLevels"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateOrganizationKinds(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("organizationKinds");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_SHORT_LISTS where domain = 'TYPE_ORG'")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = r.Field<string>("CODE"),
Name = r.Field<string>("MEANING"),
NameAlt = r.Field<string>("MEANING_TRANS"),
Alias = null,
IsActive = true,
ParentValueId = null,
TextContentString = null
})
.ToList();
noms["organizationKinds"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["organizationKinds"][row.OldId] = row;
nom.NomValues.Add(row);
}
var organizationFmTypes = new List<Tuple<string, string>>()
{
{ new Tuple<string,string>("АД" , "AD")},
{ new Tuple<string,string>("ООД" , "OOD")}
};
foreach (var type in organizationFmTypes)
{
NomValue row = new NomValue()
{
OldId = null,
Code = type.Item2,
Name = type.Item1,
NameAlt = type.Item1,
Alias = null,
IsActive = true,
ParentValueId = null,
TextContentString = null
};
noms["organizationKinds"][Guid.NewGuid().ToString()] = row;
nom.NomValues.Add(row);
}
}
private void migrateLicenceChangeReasons(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("licenceChangeReasons");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_LICENCE_CHANGE_REASON")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = null,
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 = JsonConvert.SerializeObject(
new
{
seqNo = r.Field<decimal?>("SEQ_NO"),
reasonGroup = r.Field<string>("REASON_GROUP")
})
})
.ToList();
noms["licenceChangeReasons"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["licenceChangeReasons"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migratePersonExperienceRoles(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("experienceRoles");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_EXPERIENCE_ROLE")
.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 = JsonConvert.SerializeObject(
new
{
codeCA = r.Field<string>("CA_CODE"),
})
})
.ToList();
noms["experienceRoles"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["experienceRoles"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateLicenceTypeDictionary(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("licenceTypeDictionary");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.LICENCE_DICTIONARY")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = r.Field<string>("CODE"),
Name = r.Field<string>("NAME"),
NameAlt = null,
Alias = null,
IsActive = true,
ParentValueId = noms["staffTypes"].ByOldId(r.Field<decimal?>("STAFF_TYPE_ID").ToString()).NomValueId(),
TextContentString = null
})
.ToList();
noms["licenceTypeDictionary"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["licenceTypeDictionary"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateRatingClassGroups(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("ratingClassGroups");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_RATING_CLASS_GROUP")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = r.Field<string>("CODE"),
Name = r.Field<string>("NAME"),
NameAlt = null,
Alias = null,
IsActive = true,
ParentValueId = null,
TextContentString = JsonConvert.SerializeObject(
new
{
caseTypeAlias = noms["staffTypes"].ByOldId(r.Field<decimal>("STAFF_TYPE_ID").ToString()).Alias
})
})
.ToList();
noms["ratingClassGroups"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["ratingClassGroups"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateLicenceTypes(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("licenceTypes");
var templateNames = new Dictionary<string, string>()
{
{ "103", "flight_licence" },
{ "161", "foreign_licence" },
{ "181", "caa_steward" },
{ "242", "student_flight_licence" },
{ "261", "coordinator" },
{ "262", "flight" },
{ "281", "atcl1" },
{ "289", "convoy" },
{ "301", "student_controller" },
{ "302", "AML_national" },
{ "321", "coordinator_simi" },
{ "323", "rvd_licence" },
{ "324", "AML_III" },
{ "328", "Pilot142_2013" },
{ "329", "CAL03_2013" },
{ "361", "pilot" },
{ "601", "AML" }
};
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_LICENCE_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 = true,
ParentValueId = noms["staffTypes"].ByOldId(r.Field<decimal?>("STAFF_TYPE_ID").ToString()).NomValueId(),
TextContentString = JsonConvert.SerializeObject(
new
{
codeCA = r.Field<string>("CA_CODE"),
dateValidFrom = r.Field<DateTime?>("DATE_FROM"),
dateValidTo = r.Field<DateTime?>("DATE_TO"),
seqNo = r.Field<decimal?>("SEQ_NO"),
prtMaxRatingCount = r.Field<decimal?>("PRT_MAX_RATING_COUNT"),
prtMaxMedCertCount = r.Field<decimal?>("PRT_MAX_MED_CERT_COUNT"),
licenceTypeDictionaryId = noms["licenceTypeDictionary"].ByOldId(r.Field<decimal?>("LICENCE_DICTIONARY_ID").ToString()).NomValueId(),
licenceTypeDictionary1Id = noms["licenceTypeDictionary"].ByOldId(r.Field<decimal?>("LICENCE_DICTIONARY1_ID").ToString()).NomValueId(),
licenceTypeDictionary2Id = noms["licenceTypeDictionary"].ByOldId(r.Field<decimal?>("LICENCE_DICTIONARY2_ID").ToString()).NomValueId(),
prtPrintableDocId = r.Field<decimal?>("PRT_PRINTABLE_DOCUMENT_ID"),
qlfCode = r.Field<string>("LICENCE_CODE") != "FCL.CPA" ? r.Field<string>("QLF_CODE") : null,
templateName = templateNames[r.Field<object>("PRT_PRINTABLE_DOCUMENT_ID").ToString()],
caseTypeAlias = noms["staffTypes"].ByOldId(r.Field<int>("STAFF_TYPE_ID").ToString()).Alias
})
})
.ToList();
noms["licenceTypes"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["licenceTypes"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateRatingTypes(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("ratingTypes");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_RATING_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 = true,
ParentValueId = null,
TextContentString = JsonConvert.SerializeObject(
new
{
codeCA = r.Field<string>("CA_CODE"),
dateValidFrom = r.Field<DateTime?>("DATE_FROM"),
dateValidTo = r.Field<DateTime?>("DATE_TO")
})
})
.ToList();
noms["ratingTypes"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["ratingTypes"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateLim147limitations(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("lim147limitations");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_147_LIMITATION")
.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 = noms["lim147ratings"].ByOldId(r.Field<long?>("ID_147_RATING").ToString()).NomValueId(),
TextContentString = JsonConvert.SerializeObject(
new
{
SortOrder = r.Field<string>("SORT_ORDER"),
})
})
.ToList();
noms["lim147limitations"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["lim147limitations"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateStaffTypes(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("staffTypes");
var aliases = new Dictionary<string, string>()
{
{ "F", "flightCrew" },
{ "T", "ovd"},
{ "G", "to_vs"},
{ "M", "to_suvd"}
};
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_STAFF_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"),
ParentValueId = null,
Alias = aliases[r.Field<string>("CODE")],
TextContentString = JsonConvert.SerializeObject(
new
{
codeCA = r.Field<string>("CA_CODE")
}),
IsActive = r.Field<string>("VALID_YN") == "Y" ? true : false
})
.ToList();
noms["staffTypes"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["staffTypes"][row.OldId] = row;
nom.NomValues.Add(row);
}
Nom trainingStaffTypesNom = repo.GetNom("trainingStaffTypes");
var copiedResults = results.Select(v =>
new NomValue
{
OldId = v.OldId,
Code = v.Code,
Name = v.Name,
NameAlt = v.NameAlt,
ParentValueId = v.ParentValueId,
Alias = v.Alias,
TextContentString = v.TextContentString,
IsActive = v.IsActive
});
noms["trainingStaffTypes"] = new Dictionary<string, NomValue>();
foreach (var row in copiedResults)
{
noms["trainingStaffTypes"][row.OldId] = row;
trainingStaffTypesNom.NomValues.Add(row);
}
NomValue generalDocument =
new NomValue
{
NomValueId = 0,
Name = "Общ документ",
Alias = "general"
};
noms["trainingStaffTypes"]["0"] = generalDocument;
trainingStaffTypesNom.NomValues.Add(generalDocument);
}
private void migrateLim147ratings(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("lim147ratings");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_147_RATING")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = r.Field<string>("SORT_ORDER"),
Name = r.Field<string>("RATING"),
NameAlt = null,
Alias = null,
IsActive = true,
ParentValueId = noms["lim147classes"].ByOldId(r.Field<long?>("ID_147_CLASS").ToString()).NomValueId(),
TextContentString = null
})
.ToList();
noms["lim147ratings"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["lim147ratings"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateAircraftClases66(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("aircraftClases66");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_CATEGORY_66")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = r.Field<string>("CODE"),
Name = r.Field<string>("DESCRIPTION"),
NameAlt = null,
Alias = null,
IsActive = r.Field<string>("VALID_YN") == "Y" ? true : false,
ParentValueId = noms["aircraftGroup66"].ByOldId(r.Field<long?>("ID_AC_66").ToString()).NomValueId(),
TextContentString = JsonConvert.SerializeObject(
new
{
alias = r.Field<string>("CATEGORY")
})
})
.ToList();
noms["aircraftClases66"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["aircraftClases66"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateLimitations66(INomRepository repo, OracleConnection conn)
{
var limitationTypes = new Dictionary<string, string>()
{
{"M", "ATSML"},
{"N", "AMLAircrafts"},
{"F", "FCL"},
{"Y", "AMLCommon"}
};
Nom nom = repo.GetNom("limitations66");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_LIMITATIONS_66")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = r.Field<string>("CODE").Trim(),
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 = JsonConvert.SerializeObject(
new
{
type = limitationTypes[r.Field<string>("GENERAL")],
point = 13
})
})
.ToList();
noms["limitations66"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["limitations66"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateAircraftOperTypes(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("aircraftOperTypes");
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_AC_OPER")
.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["aircraftOperTypes"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["aircraftOperTypes"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
private void migrateAuditPartSections(INomRepository repo, OracleConnection conn)
{
Nom nom = repo.GetNom("auditPartSections");
var auditPartAliases = new Dictionary<string, string>()
{
{ "1", "145"},
{ "2", "MF"},
{ "3", "MG"},
{ "4", "147"}
};
var results = conn.CreateStoreCommand(@"SELECT * FROM CAA_DOC.NM_SECTION")
.Materialize(r =>
new NomValue
{
OldId = r.Field<object>("ID").ToString(),
Code = r.Field<string>("CODE"),
Name = r.Field<string>("NAME"),
NameAlt = null,
Alias = null,
IsActive = true,
Order = r.Field<int?>("SORT_ORDER") ?? 0,
ParentValueId = noms["auditParts"].ByOldId(r.Field<long?>("ID_PART").ToString()).NomValueId(),
TextContentString = JsonConvert.SerializeObject(
new
{
auditPart = r.Field<long?>("ID_PART") != null ? auditPartAliases[r.Field<long>("ID_PART").ToString()] : ""
})
})
.ToList();
noms["auditPartSections"] = new Dictionary<string, NomValue>();
foreach (var row in results)
{
noms["auditPartSections"][row.OldId] = row;
nom.NomValues.Add(row);
}
}
