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));
}
