public async Task <IActionResult> GetSettingsAsync([FromRoute] string settingType) { string sql = @"SELECT ID_RESOURCE_SETTING as Code, NM_SETTING as Description FROM RESOURCE_SETTING WHERE CD_SETTING = @Type ORDER BY Description ASC"; var settingNames = await readStore.QueryAsync <DropdownCode>(sql, new { type = settingType }); return(Ok(settingNames)); }
public async Task <IActionResult> GetSymptomsForCase([FromRoute] int caseId) { string sql = @"select c.ID_CASE ,s.CD_SYMPTOM SymptomCode ,s.NM_SYMPTOM SymptomName ,cs.DT_ONSET OnsetDate ,cs.AM_ONSET_TIME OnsetTime ,cs.DS_SYMPTOM_OTHER Other ,case when cs.CD_SYMPTOM is not null then 1 else 0 end as HasSymptom from ( select * from ( select ROW_NUMBER() over(partition by cd_icd9 order by dt_effective) row_num , * from dbo.SYMPTOM_SET ) active where row_num = 1 )as ss inner join dbo.SYMPTOM_ICD9 i on ss.ID_SYMPTOM_SET = i.ID_SYMPTOM_SET inner join dbo.epi_case c on ss.cd_icd9 = c.CD_ICD9 inner join dbo.SYMPTOM s on i.CD_SYMPTOM = s.cd_symptom left join dbo.CASE_SYMPTOM cs on c.ID_CASE = cs.ID_CASE and s.CD_SYMPTOM = cs.CD_SYMPTOM where c.id_case = @caseId"; var symptomList = await readStore.QueryAsync <CaseSymptomDto>(sql, new { caseId }); return(Ok(symptomList)); }
public async Task <IActionResult> GetLayoutsByTags([FromQuery] GetLayoutsByTags query) { int?count = null; IQueryable <SurveyLayout> dataSet = readContext.SurveyLayout.Where(x => !string.IsNullOrEmpty(x.NmLayout)); //if blank tags skip if (query.Tags != null) { List <string> tagQueries = new List <string>(); foreach (var tag in query.Tags) { tagQueries.Add($"select UID_LAYOUT from dbo.SURVEY_LAYOUT_TAG where DS_TAG = '{tag.Replace("'", "''")}'"); } var sql = string.Join(" intersect ", tagQueries); var layoutIds = await readStore.QueryAsync <Guid>(sql); dataSet = dataSet .Where(layout => layoutIds.Contains(layout.UID)); count = layoutIds.Count(); } var layoutList = await dataSet .Select(layout => new LayoutListDto { LayoutId = layout.UID, LayoutName = layout.NmLayout, Tags = layout.SurveyLayoutTag .Select(t => t.DsTag) .ToList(), Surveys = layout.Surveys .Select(survey => new SurveyListDto { SurveyName = survey.NmSurvey, Icd9 = survey.CdIcd9, OutbreakId = survey.IdOutbreak, EffectiveDate = survey.DtEffective, DiseaseName = readContext.Icd9.Where(x => x.CdIcd9.Equals(survey.CdIcd9)).Select(item => item.NmIcd9).FirstOrDefault(), OutbreakName = readContext.Outbreak.Where(x => x.IdOutbreak.Equals(survey.IdOutbreak)).Select(item => item.NmOutbreak).FirstOrDefault() }) .OrderByDescending(item => item.EffectiveDate).ToList() }) .ToPagedListAsync(query.Paging, count ?? dataSet.Count()); return(Ok(layoutList)); }
private async Task <IEnumerable <DropdownCode> > GetCodesDropdownAsync(string codeType, MerlinReadStore db) { string sql = @"SELECT CD_VALUE Code, DS_DESC Description FROM dbo.CODES WHERE CD_TYPE = @type AND (DT_EXPIRED > CURRENT_TIMESTAMP OR DT_EXPIRED IS NULL) AND DS_DESC NOT IN (' ','NOT AVAILABLE') ORDER BY ID_SEQUENCE, DS_DESC"; return(await db.QueryAsync <DropdownCode>(sql, new { type = codeType })); }
public async Task <string> GetDescription(string value, params string[] types) { string sql = @"SELECT top 1 DS_DESC as Description FROM CODES WHERE CD_TYPE = @type AND CD_VALUE = @value;" ; foreach (string type in types) { var result = await db.QueryAsync <dynamic>(sql, new { type, value }); if (result.FirstOrDefault() != null) { return(result.FirstOrDefault().Description); } } return(null); }
public async Task <IActionResult> GetByZipCode([FromRoute] string zip) { //this is not correct, we should not be choosing a single zip for anyone string sql = $@"SELECT zip, c.CD_COUNTY as county, state, PO_NAME as City FROM ELR.dbo.GDT_POSTAL_BOUNDARY p left join merlin.dbo.county c on c.nm_name = p.county WHERE zip like @zip"; var addresses = await readStore.QueryAsync <AddressDto>(sql, new { zip = $"{zip}%" }); return(Ok(addresses)); }
public async Task <IEnumerable <string> > GetPksForTableAsync(string tableName) { //cache this with dictionary to improve speed if (keyColumnLookup.TryGetValue(tableName, out var keys)) { return(keys); } string sql = @"select u.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS c inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u on c.CONSTRAINT_NAME = u.CONSTRAINT_NAME where c.constraint_type = 'PRIMARY KEY' and c.TABLE_NAME = @tableName"; var keyColumns = await readStore.QueryAsync <string>(sql, new { tableName }); keyColumnLookup[tableName] = keyColumns; return(keyColumns); }
public async Task <IActionResult> GetOutbreakSettingInformationById([FromRoute] int OutbreakId) { string sql = @"SELECT OS.ID_OUTBREAK as OutbreakId, OS.ID_SETTING as Id, OS.ID_RESOURCE_SETTING SettingFacilityId, OS.DS_RELATION_OUTBREAK, CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.CD_SETTING ELSE RS.CD_SETTING END AS SettingType, SETTING.DS_DESC, OS.DS_SETTING_OTHER AS OtherType , ISNULL(OS.IN_PRIMARY,0) IsPrimary, CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.NM_FACILITY ELSE RS.NM_SETTING END AS SettingName, CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.IN_US_ADDRESS ELSE OS.IN_US_ADDRESS END AS IN_US_ADDRESS, CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.NM_CONTACT ELSE RS.NM_CONTACT END AS SettingContact, CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.DS_CONTACT_PHN ELSE RS.DS_PHN END AS SettingContactPhone, OS.DT_CLOSED, OS.DS_COMMENTS FROM OUTBREAK_SETTINGS OS LEFT JOIN RESOURCE_SETTING RS ON RS.ID_RESOURCE_SETTING = OS.ID_RESOURCE_SETTING LEFT JOIN CODES SETTING ON SETTING.CD_TYPE = 'OB_SETTING' AND SETTING.CD_VALUE = CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.CD_SETTING ELSE RS.CD_SETTING END WHERE OS.ID_OUTBREAK = @OutbreakId"; string addressSql = @"SELECT OS.ID_SETTING, CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.DS_ADDRESS ELSE RS.DS_ADDR1_NAME END AS AddressLine1, CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.DS_ADDRESS2 ELSE RS.DS_ADDR2 END AS AddressLine2, CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.DS_CITY ELSE RS.DS_CITY END AS City, CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.CD_STATE ELSE RS.CD_STATE END AS State, STATE.DS_DESC, CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.DS_ZIP ELSE RS.DS_ZIP END AS Zip, CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.CD_COUNTY ELSE RS.CD_COUNTY END AS County, COUNTY.DS_DESC, CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.CD_COUNTRY ELSE RS.CD_COUNTRY END AS Country, COUNTRY.DS_DESC FROM OUTBREAK_SETTINGS OS LEFT JOIN RESOURCE_SETTING RS ON RS.ID_RESOURCE_SETTING = OS.ID_RESOURCE_SETTING LEFT JOIN CODES COUNTY ON COUNTY.CD_TYPE ='COUNTY' AND COUNTY.CD_VALUE = CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.CD_COUNTY ELSE RS.CD_COUNTY END LEFT JOIN CODES STATE ON STATE.CD_TYPE ='STATE' AND STATE.CD_VALUE = CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.CD_STATE ELSE RS.CD_STATE END LEFT JOIN CODES COUNTRY ON COUNTRY.CD_TYPE ='COUNTRY' AND COUNTRY.CD_VALUE = CASE WHEN OS.ID_RESOURCE_SETTING IS NULL THEN OS.CD_COUNTRY ELSE RS.CD_COUNTRY END WHERE OS.ID_SETTING = @Id"; var outbreakSettings = await readStore.QueryAsync <OutbreakSettingInformationDTO>(sql, new { OutbreakId }); foreach (var item in outbreakSettings) { item.Address = await readStore.QuerySingleAsync <AddressDto>(addressSql, new { item.Id }); } return(Ok(outbreakSettings)); }