private async Task <LabSummaryResultDto> GetLabResult(int caseId, LabSummaryDto labSummaryItem)
{
string fieldPrefix = string.Empty;
string sql = string.Empty;
var labResult = new LabSummaryResultDto();
switch (labSummaryItem.Relation)
{
case "SEROSPECIE":
labSummaryItem.Relation = "SEROTYPE";
goto case "SEROTYPE";
case "STRAIN":
case "SEROGROUP":
case "SEROTYPE":
case "BIOTYPE":
//Dropdowns
labResult.Values = await GetLabSummaryDropdownValues(labSummaryItem.Relation);
fieldPrefix = "CD_";
break;
default:
fieldPrefix = "DS_";
break;
}
sql = $"SELECT {fieldPrefix}{labSummaryItem.Relation} FROM EXT_LAB " +
"WHERE ID_CASE = @CaseId";
labResult.Label = labSummaryItem.Description;
labResult.Value = await readStore.QuerySingleAsync <string>(sql, new { caseId });
return(labResult);
}
public async Task <long> GetNextAsync(string key)
{
//we cannot use parameters here so we need to be very sure no SQL injection is possible
if (Regex.IsMatch(key, @"^\w+$"))
{
return(await db.QuerySingleAsync <int>($"select next value for dbo.{key};"));
}
throw new InvalidOperationException(
$"Potentionally dangerous sequence name '{key}' in DbSequenceGenerator.");
}
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));
}
