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