private string GetSSN(SqlDataReader reader)
{
// Do this because the SSN embedded in an undefined component of
// the Patient ID (Internal) field contains a tilde that ends the
// interpretation of the contents of this field #3 component
string ssn = "";
string fullField = UtilitiesDAL.ToString(reader["PIDINTERNALPATIENTIDFULLFIELD"]);
string[] componentArray = fullField.Split('^');
if (componentArray.Length > 4) // Fail if fewer than 5 components are found
{
string selectedComponent = componentArray[4]; // Grab component 5
int tildeOffset = selectedComponent.IndexOf('~');
if (tildeOffset >= 0 && tildeOffset < selectedComponent.Length)
{
ssn = selectedComponent.Substring(tildeOffset + 1);
}
else if (componentArray.Length > 5) // Fail if fewer than 6 components are found
{
selectedComponent = componentArray[5]; // Grab component 6
tildeOffset = selectedComponent.IndexOf('~');
if (tildeOffset >= 0 && tildeOffset < selectedComponent.Length)
{
ssn = selectedComponent.Substring(tildeOffset + 1);
}
}
}
return(ssn.Replace("-", ""));
}
public List <ExplicitMatch> GetExplicitMatchMappings()
{
List <ExplicitMatch> explicitMatchList = new List <ExplicitMatch>();
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = SqlConnectionMain;
// Get previously defined patient ID mappings
cmd.CommandText = "GetExplicitMatchMappings";
reader = cmd.ExecuteReader();
while (reader.Read())
{
ExplicitMatch explicitMatch = new ExplicitMatch();
explicitMatch.DataLoadMismatchTestId = UtilitiesDAL.ToInt(reader["DataLoadMismatchTestID"]);
explicitMatch.MatchedToUserId = UtilitiesDAL.ToInt(reader["MatchedToUserID"]);
explicitMatch.MatchedToTestId = UtilitiesDAL.ToInt(reader["MatchedToTestID"]);
explicitMatchList.Add(explicitMatch);
}
reader.Close();
return(explicitMatchList);
}
public List <AutoMatch> GetAutoMatchMappings()
{
List <AutoMatch> autoMatchList = new List <AutoMatch>();
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = SqlConnectionMain;
// Get previously defined patient ID mappings
cmd.CommandText = "GetAutoMatchMappings";
reader = cmd.ExecuteReader();
while (reader.Read())
{
AutoMatch autoMatch = new AutoMatch();
autoMatch.LastName = UtilitiesDAL.ToString(reader["LastName"]);
autoMatch.FirstName = UtilitiesDAL.ToString(reader["FirstName"]);
autoMatch.Gender = UtilitiesDAL.ToString(reader["Gender"]);
autoMatch.DOB = UtilitiesDAL.ToDateTime(reader["DateOfBirth"]);
autoMatch.SSN = UtilitiesDAL.ToString(reader["SSN"]).Replace("-", "");
autoMatch.MatchedToUserId = UtilitiesDAL.ToInt(reader["MatchedToUserID"]);
autoMatchList.Add(autoMatch);
}
reader.Close();
return(autoMatchList);
}
private void GetUserIdBasedOnSSN(Observation obx)
{
int censusUserId = 0;
string censusGender = "";
DateTime censusDOB = new DateTime();
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = SqlConnectionMain;
// Get the matching UserId for the SSN in the current observation
cmd.CommandText = "GetUserIdBasedOnSSN";
if (obx.SSN.Length < 9)
{
Logger.LogLine("SSN (" + obx.SSN + ") is not long enough");
obx.UserId = 0;
}
else if (SSNCache.Keys.Contains(obx.SSN))
{
Logger.LogLine("Found " + obx.SSN + " in the cache");
obx.UserId = SSNCache[obx.SSN];
}
else
{
try
{
cmd.Parameters.Clear();
cmd.Parameters.Add(new SqlParameter("@SSN", obx.SSN));
reader = cmd.ExecuteReader();
if (reader.Read())
{
censusUserId = UtilitiesDAL.ToInt(reader["UserID"]);
censusGender = UtilitiesDAL.ToString(reader["Gender"]);
censusDOB = UtilitiesDAL.ToDateTime(reader["DOB"]);
}
obx.UserId = censusUserId;
SSNCache[obx.SSN] = censusUserId;
Logger.LogLine("Added " + obx.SSN + " to the cache");
if (censusUserId > 0
&&
!MatchOnGenderAndDOB(obx, censusGender, censusDOB))
{
obx.UserId = 0; // Treat as if user was not found, due to gender/DOB mismatch
}
reader.Close();
}
catch (Exception ex)
{
Logger.LogLine("Unexpected error when trying to process " + obx.SSN + " (" + ex.Message + ")");
}
}
}
private void GetUserIdBasedOnUserCN(Observation obx)
{
int censusUserId = 0;
string censusGender = "";
DateTime censusDOB = new DateTime();
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = SqlConnectionMain;
// Get the matching UserId for the UserCN in the current observation
cmd.CommandText = "GetUserIdBasedOnUserCN";
if (UserCNCache.Keys.Contains(obx.UserCN))
{
Logger.LogLine("Found " + obx.UserCN + " in the cache");
obx.UserId = UserCNCache[obx.UserCN];
}
else
{
cmd.Parameters.Clear();
cmd.Parameters.Add(new SqlParameter("@UserCN", obx.UserCN));
reader = cmd.ExecuteReader();
if (reader.Read())
{
censusUserId = UtilitiesDAL.ToInt(reader["UserID"]);
censusGender = UtilitiesDAL.ToString(reader["Gender"]);
censusDOB = UtilitiesDAL.ToDateTime(reader["DOB"]);
}
obx.UserId = censusUserId;
UserCNCache[obx.UserCN] = censusUserId;
Logger.LogLine("Added " + obx.UserCN + " to the cache");
if (censusUserId > 0
&&
!MatchOnGenderAndDOB(obx, censusGender, censusDOB))
{
obx.UserId = 0; // Treat as if user was not found, due to gender/DOB mismatch
}
reader.Close();
}
}
private void SpecialObxSetup(Observation obx, SqlDataReader reader)
{
// Create the UserCN (username) that is used to identify the user for each observation
string firstNameFirstLetter = (obx.FirstName.Length > 0) ? obx.FirstName.Substring(0, 1) : "";
string lastName = obx.LastName;
string lastFourOfSSN = (obx.SSN.Length == 9) ? obx.SSN.Substring(5, 4) : "XXXX";
obx.UserCN = firstNameFirstLetter + lastName + lastFourOfSSN;
// Extract the actual date part of the message date field
string messageDateStringRaw = UtilitiesDAL.ToString(reader["EntryDate"]);
string messageDateString = "";
foreach (char ch in messageDateStringRaw.ToCharArray())
{
if (!char.IsDigit(ch))
{
break;
}
messageDateString += ch;
}
DateTime messageDateTime;
if (!DateTime.TryParseExact(messageDateString, DateFormats, new CultureInfo("en-US"), DateTimeStyles.None, out messageDateTime))
{
messageDateTime = DateTime.Now;
}
obx.EntryDate = messageDateTime;
int firstComponentSeparatorPosition = obx.ResultData.IndexOf('^');
if (firstComponentSeparatorPosition > -1)
{
obx.ResultData = obx.ResultData.Substring(0, firstComponentSeparatorPosition);
}
// Use the replacement value, if given
if (obx.DataLoadValueErrorID > 0 &&
obx.ReplacementValue != null)
{
obx.ResultData = obx.ReplacementValue;
}
}
public List <Observation> GetObservationList()
{
List <Observation> obxList = new List <Observation>();
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = SqlConnectionMain;
// Get previously processed observations that failed
cmd.CommandText = "GetMessageObservationsFailed";
reader = cmd.ExecuteReader();
while (reader.Read())
{
Observation obx = new Observation();
obx.MessageId = UtilitiesDAL.ToString(reader["MessageID"]);
obx.SequenceId = UtilitiesDAL.ToInt(reader["SequenceID"]);
obx.CustomerString = UtilitiesDAL.ToString(reader["CustomerString"]);
obx.ObservationId = UtilitiesDAL.ToString(reader["OBXID"]);
obx.ObservationIdText = UtilitiesDAL.ToString(reader["OBXIDTEXT"]);
obx.ResultData = UtilitiesDAL.ToString(reader["OBXRESULT"]);
obx.ResultUnits = UtilitiesDAL.ToString(reader["OBXUNITS"]);
obx.ReferenceRange = UtilitiesDAL.ToString(reader["OBXREFRANGE"]);
obx.TestId = UtilitiesDAL.ToInt(reader["TestID"]);
obx.TestNormalRange = UtilitiesDAL.ToString(reader["NormalRange"]);
obx.TestNormalRangeFemale = UtilitiesDAL.ToString(reader["NormalRangeF"]);
obx.InternalPatientId = UtilitiesDAL.ToString(reader["PIDINTERNALPATIENTID"]);
obx.SSN = GetSSN(reader);
obx.LastName = UtilitiesDAL.ToString(reader["PIDLASTNAME"]);
obx.FirstName = UtilitiesDAL.ToString(reader["PIDFIRSTNAME"]);
obx.MiddleName = UtilitiesDAL.ToString(reader["PIDMIDDLENAME"]);
obx.DOB = UtilitiesDAL.ToString(reader["PIDDOB"]);
obx.Gender = UtilitiesDAL.ToString(reader["PIDGENDER"]);
obx.StreetAddress = UtilitiesDAL.ToString(reader["PIDSTREET"]);
obx.OtherAddress = UtilitiesDAL.ToString(reader["PIDOTHER"]);
obx.City = UtilitiesDAL.ToString(reader["PIDCITY"]);
obx.State = UtilitiesDAL.ToString(reader["PIDSTATE"]);
obx.Zip = UtilitiesDAL.ToString(reader["PIDZIP"]);
obx.DataLoadMismatchTestID = UtilitiesDAL.ToInt(reader["DataLoadMismatchTestID"]);
obx.DataLoadValueErrorID = UtilitiesDAL.ToInt(reader["DataLoadValueErrorID"]);
obx.ReplacementValue = UtilitiesDAL.ToNullableString(reader["ReplacementValue"]);
SpecialObxSetup(obx, reader);
obxList.Add(obx);
}
reader.Close();
// Get new never-before-processed observations
cmd.CommandText = "GetMessageObservationsUnattempted";
reader = cmd.ExecuteReader();
while (reader.Read())
{
Observation obx = new Observation();
obx.MessageId = UtilitiesDAL.ToString(reader["MessageID"]);
obx.SequenceId = UtilitiesDAL.ToInt(reader["SequenceID"]);
obx.CustomerString = UtilitiesDAL.ToString(reader["CustomerString"]);
obx.ObservationId = UtilitiesDAL.ToString(reader["OBXID"]);
obx.ObservationIdText = UtilitiesDAL.ToString(reader["OBXIDTEXT"]);
obx.ResultData = UtilitiesDAL.ToString(reader["OBXRESULT"]);
obx.ResultUnits = UtilitiesDAL.ToString(reader["OBXUNITS"]);
obx.ReferenceRange = UtilitiesDAL.ToString(reader["OBXREFRANGE"]);
obx.TestId = UtilitiesDAL.ToInt(reader["TestID"]);
obx.TestNormalRange = UtilitiesDAL.ToString(reader["NormalRange"]);
obx.TestNormalRangeFemale = UtilitiesDAL.ToString(reader["NormalRangeF"]);
obx.InternalPatientId = UtilitiesDAL.ToString(reader["PIDINTERNALPATIENTID"]);
obx.SSN = GetSSN(reader);
obx.LastName = UtilitiesDAL.ToString(reader["PIDLASTNAME"]);
obx.FirstName = UtilitiesDAL.ToString(reader["PIDFIRSTNAME"]);
obx.MiddleName = UtilitiesDAL.ToString(reader["PIDMIDDLENAME"]);
obx.DOB = UtilitiesDAL.ToString(reader["PIDDOB"]);
obx.Gender = UtilitiesDAL.ToString(reader["PIDGENDER"]);
obx.StreetAddress = UtilitiesDAL.ToString(reader["PIDSTREET"]);
obx.OtherAddress = UtilitiesDAL.ToString(reader["PIDOTHER"]);
obx.City = UtilitiesDAL.ToString(reader["PIDCITY"]);
obx.State = UtilitiesDAL.ToString(reader["PIDSTATE"]);
obx.Zip = UtilitiesDAL.ToString(reader["PIDZIP"]);
obx.DataLoadMismatchTestID = 0;
obx.DataLoadValueErrorID = 0;
SpecialObxSetup(obx, reader);
obxList.Add(obx);
}
reader.Close();
FillInUserIdFields(obxList);
return(obxList);
}