///<summary>this code is similar to code in the phone tracking server. But here, we frequently only change clockStatus and ColorBar by setting employeeNum=-1. If employeeNum is not -1, then EmployeeName also gets set. If employeeNum==0, then clears employee from that row.</summary>
public static void SetPhoneStatus(ClockStatusEnum clockStatus, int extens, long employeeNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), clockStatus, extens, employeeNum);
return;
}
string command = @"SELECT phoneempdefault.EmployeeNum,Description,phoneempdefault.EmpName, "
+ "NoColor "
+ "FROM phone "
+ "LEFT JOIN phoneempdefault ON phone.Extension=phoneempdefault.PhoneExt "
+ "WHERE phone.Extension=" + POut.Long(extens);
DataTable tablePhone = Db.GetTable(command);
if (tablePhone.Rows.Count == 0)
{
return;
}
long empNum = PIn.Long(tablePhone.Rows[0]["EmployeeNum"].ToString());
string empName = PIn.String(tablePhone.Rows[0]["EmpName"].ToString());
//if these values are null because of missing phoneempdefault row, they will default to false
//PhoneEmpStatusOverride statusOverride=(PhoneEmpStatusOverride)PIn.Int(tablePhone.Rows[0]["StatusOverride"].ToString());
bool isDefaultNoColor = PIn.Bool(tablePhone.Rows[0]["NoColor"].ToString());
bool isInUse = false;
if (tablePhone.Rows[0]["Description"].ToString() == "In use")
{
isInUse = true;
}
Color colorBar = GetColorBar(clockStatus, empNum, isInUse, isDefaultNoColor);
string clockStatusStr = clockStatus.ToString();
if (clockStatus == ClockStatusEnum.None)
{
clockStatusStr = "";
}
command = "UPDATE phone SET ClockStatus='" + POut.String(clockStatusStr) + "', "
+ "ColorBar=" + colorBar.ToArgb().ToString() + ", "
+ "EmployeeNum=" + POut.Long(empNum) + ", "
+ "EmployeeName='" + POut.String(empName) + "' "
+ "WHERE Extension=" + extens;
Db.NonQ(command);
}
/*
* private static Document[] RefreshAndFill(string command) {
* //No need to check RemotingRole; no call to db.
* return Fill(Db.GetTable(command));
* }*/
///<summary>Usually, set just the extension before passing in the doc. Inserts a new document into db, creates a filename based on Cur.DocNum, and then updates the db with this filename. Should always refresh the document after calling this method in order to get the correct filename for RemotingRole.ClientWeb.</summary>
public static long Insert(Document doc, Patient pat)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
doc.DocNum = Meth.GetLong(MethodBase.GetCurrentMethod(), doc, pat);
return(doc.DocNum);
}
doc.DocNum = Crud.DocumentCrud.Insert(doc);
//If the current filename is just an extension, then assign it a unique name.
if (doc.FileName == Path.GetExtension(doc.FileName))
{
string extension = doc.FileName;
doc.FileName = "";
string s = pat.LName + pat.FName;
for (int i = 0; i < s.Length; i++)
{
if (Char.IsLetter(s, i))
{
doc.FileName += s.Substring(i, 1);
}
}
doc.FileName += doc.DocNum.ToString() + extension; //ensures unique name
//there is still a slight chance that someone manually added a file with this name, so quick fix:
string command = "SELECT FileName FROM document WHERE PatNum=" + POut.Long(doc.PatNum);
DataTable table = Db.GetTable(command);
string[] usedNames = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
usedNames[i] = PIn.String(table.Rows[i][0].ToString());
}
while (IsFileNameInList(doc.FileName, usedNames))
{
doc.FileName = "x" + doc.FileName;
}
/*Document[] docList=GetAllWithPat(doc.PatNum);
* while(IsFileNameInList(doc.FileName,docList)) {
* doc.FileName="x"+doc.FileName;
* }*/
Update(doc);
}
return(doc.DocNum);
}
///<summary>Returns an array of all patient names who are using this medication.</summary>
public static string[] GetPatNamesForMed(long medicationNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <string[]>(MethodBase.GetCurrentMethod(), medicationNum));
}
string command =
"SELECT CONCAT(CONCAT(CONCAT(CONCAT(LName,', '),FName),' '),Preferred) FROM medicationpat,patient "
+ "WHERE medicationpat.PatNum=patient.PatNum "
+ "AND medicationpat.MedicationNum=" + POut.Long(medicationNum);
DataTable table = Db.GetTable(command);
string[] retVal = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
retVal[i] = PIn.String(table.Rows[i][0].ToString());
}
return(retVal);
}
///<summary>Supply claimnums. Called from X12 to begin the sorting process on claims going to one clearinghouse.</summary>
public static List <X12TransactionItem> GetX12TransactionInfo(List <long> claimNums) //ArrayList queueItemss){
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <X12TransactionItem> >(MethodBase.GetCurrentMethod(), claimNums));
}
StringBuilder str = new StringBuilder();
for (int i = 0; i < claimNums.Count; i++)
{
if (i > 0)
{
str.Append(" OR");
}
str.Append(" claim.ClaimNum=" + POut.Long(claimNums[i])); //((ClaimSendQueueItem)queueItems[i]).ClaimNum.ToString());
}
string command;
command = "SELECT carrier.ElectID,claim.ProvBill,inssub.Subscriber,"
+ "claim.PatNum,claim.ClaimNum,CASE WHEN inssub.Subscriber!=claim.PatNum THEN 1 ELSE 0 END AS subscNotPatient "
+ "FROM claim,insplan,inssub,carrier "
+ "WHERE claim.PlanNum=insplan.PlanNum "
+ "AND claim.InsSubNum=inssub.InsSubNum "
+ "AND carrier.CarrierNum=insplan.CarrierNum "
+ "AND (" + str.ToString() + ") "
+ "ORDER BY carrier.ElectID,claim.ProvBill,inssub.Subscriber,subscNotPatient,claim.PatNum";
DataTable table = Db.GetTable(command);
List <X12TransactionItem> retVal = new List <X12TransactionItem>();
//object[,] myA=new object[5,table.Rows.Count];
X12TransactionItem item;
for (int i = 0; i < table.Rows.Count; i++)
{
item = new X12TransactionItem();
item.PayorId0 = PIn.String(table.Rows[i][0].ToString());
item.ProvBill1 = PIn.Long(table.Rows[i][1].ToString());
item.Subscriber2 = PIn.Long(table.Rows[i][2].ToString());
item.PatNum3 = PIn.Long(table.Rows[i][3].ToString());
item.ClaimNum4 = PIn.Long(table.Rows[i][4].ToString());
retVal.Add(item);
}
return(retVal);
}
///<summary>Gets all PatientRace entries from the db for the specified patient and includes the non-db fields Description, IsEthnicity, and
///HiearchicalCode.</summary>
public static List <PatientRace> GetForPatient(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <PatientRace> >(MethodBase.GetCurrentMethod(), patNum));
}
string command = @"SELECT patientrace.*,COALESCE(cdcrec.Description,'') Description,
(CASE WHEN cdcrec.HeirarchicalCode LIKE 'E%' THEN 1 ELSE 0 END) IsEthnicity,
COALESCE(cdcrec.HeirarchicalCode,'') HeirarchicalCode
FROM patientrace
LEFT JOIN cdcrec ON cdcrec.CdcrecCode=patientrace.CdcrecCode
WHERE PatNum=" + POut.Long(patNum);
DataTable table = Db.GetTable(command);
List <PatientRace> listPatientRaces = Crud.PatientRaceCrud.TableToList(table);
for (int i = 0; i < table.Rows.Count; i++)
{
switch (listPatientRaces[i].CdcrecCode)
{
case PatientRace.DECLINE_SPECIFY_RACE_CODE:
listPatientRaces[i].Description = Lans.g("PatientRaces", "DECLINED TO SPECIFY");
listPatientRaces[i].IsEthnicity = false;
break;
case PatientRace.DECLINE_SPECIFY_ETHNICITY_CODE:
listPatientRaces[i].Description = Lans.g("PatientRaces", "DECLINED TO SPECIFY");
listPatientRaces[i].IsEthnicity = true;
break;
case PatientRace.MULTI_RACE_CODE:
listPatientRaces[i].Description = Lans.g("PatientRaces", "MULTIRACIAL");
listPatientRaces[i].IsEthnicity = false;
break;
default:
listPatientRaces[i].Description = PIn.String(table.Rows[i]["Description"].ToString());
listPatientRaces[i].IsEthnicity = (table.Rows[i]["IsEthnicity"].ToString() == "1");
listPatientRaces[i].HeirarchicalCode = PIn.String(table.Rows[i]["HeirarchicalCode"].ToString());
break;
}
}
return(listPatientRaces);
}
///<summary>Returns a list of all brands dependend on this generic. Only gets run if this is a generic.</summary>
public static string[] GetBrands(long medicationNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <string[]>(MethodBase.GetCurrentMethod(), medicationNum));
}
string command =
"SELECT MedName FROM medication "
+ "WHERE GenericNum=" + medicationNum.ToString()
+ " AND MedicationNum !=" + medicationNum.ToString(); //except this med
DataTable table = Db.GetTable(command);
string[] retVal = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
retVal[i] = PIn.String(table.Rows[i][0].ToString());
}
return(retVal);
}
///<summary>Gets all X12 835 etrans entries relating to a specific claim.</summary>
public static List<Etrans> GetErasOneClaim(string claimIdentifier,DateTime dateClaimService) {
//The main goal of this check is to prevent null claimIdentifiers from causing an exception.
//However, an empty claim identifier should also return an empty list because that is a terrible identifier IMO.
if(string.IsNullOrEmpty(claimIdentifier)) {
return new List<Etrans>();
}
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetObject<List<Etrans>>(MethodBase.GetCurrentMethod(),claimIdentifier,dateClaimService);
}
string claimId=claimIdentifier;
if(claimId.Length>16) {
//Our claim identifiers in the database can be longer than 20 characters (mostly when using replication).
//When the claim identifier is sent out on the claim, it is truncated to 20 characters.
//Therefore, if the claim identifier is longer than 20 characters,
//then it was truncated when sent out, so we have to look for claims beginning with the claim identifier given if there is not an exact match.
//We also send shorter identifiers for some clearinghouses. For example, the maximum claim identifier length for Denti-Cal is 17 characters.
claimId=claimId.Substring(0,16);
}
string command="SELECT * FROM etrans"
+" INNER JOIN etransmessagetext ON etransmessagetext.EtransMessageTextNum=etrans.EtransMessageTextNum"
+" AND etransmessagetext.MessageText REGEXP 'CLP."+POut.String(claimId)+"'"
//CLP = match CLP, . = match any character, then up to 16 other chars after.
+" WHERE Etype="+POut.Int((int)EtransType.ERA_835)+" AND etrans.DateTimeTrans >= "+POut.Date(dateClaimService);
if(claimIdentifier.Length<16) {
DataTable tableEtrans=Db.GetTable(command);
List<Etrans> listEtrans=Crud.EtransCrud.TableToList(tableEtrans);
List<Etrans> retVal=new List<Etrans>();
for(int i=0;i<tableEtrans.Rows.Count;i++) {
string messageText=PIn.String(tableEtrans.Rows[i]["MessageText"].ToString());
string separator=messageText.Substring(3,1);//The character that is used as a separator is always at the third index of the message text.
if(messageText.Contains("CLP"+separator+claimId+separator)) {
retVal.Add(listEtrans[i]);//This Etrans has an exact match for the claimIdentifier, it's an accurate search result.
}
}
return retVal;
}
else {
//If the claimIdentifier is > 16 we trust it's unique enough we don't need to do more searching.
//Plus, we cannot trust any characters after the 16th character, since the identifier might have been truncated at some point.
return Crud.EtransCrud.SelectMany(command);
}
}
/*
* ///<Summary>Gets all task lists in the general tab with no heirarchy. This allows us to loop through the list to grab useful heirarchy info. Only used when viewing user tab. Not guaranteed to get all tasklists, because we exclude those with a DateType.</Summary>
* public static List<TaskList> GetAllGeneral(){
* //THIS WON'T WORK BECAUSE THERE ARE TOO MANY REPEATING TASKLISTS.
* string command="SELECT * FROM tasklist WHERE DateType=0 ";
* }*/
///<summary>The table passed in can contain additional columns: "NewTaskCount", "ParentDesc1", "ParentDesc2". These additional columns are used when getting a list of task lists for trunks.</summary>
private static List <TaskList> TableToList(DataTable table)
{
//No need to check RemotingRole; no call to db.
List <TaskList> retVal = Crud.TaskListCrud.TableToList(table);
string desc;
//Check if the table passed in contains any of the special columns used to fill task lists for trunks. If not, simply return.
if (!table.Columns.Contains("NewTaskCount"))
{
return(retVal);
}
//The following ParentDesc columns are optional.
bool hasParentDesc = false;
if (table.Columns.Contains("ParentDesc1") &&
table.Columns.Contains("ParentDesc2"))
{
hasParentDesc = true;
}
//There were special non db columns passed in that need to be set.
//Loop through the entire list of task lists and set their corresponding non db columns to the values of the table passed in.
for (int i = 0; i < retVal.Count; i++)
{
//We know at this point that the table passed in contains a NewTaskCount column.
retVal[i].NewTaskCount = PIn.Int(table.Rows[i]["NewTaskCount"].ToString());
if (hasParentDesc) //Check for optional parent descriptions.
//Create visual heirarchy of tasklists
{
desc = PIn.String(table.Rows[i]["ParentDesc1"].ToString());
if (desc != "")
{
retVal[i].ParentDesc = desc + "/";
}
desc = PIn.String(table.Rows[i]["ParentDesc2"].ToString());
if (desc != "")
{
retVal[i].ParentDesc = desc + "/" + retVal[i].ParentDesc;
}
}
}
return(retVal);
}
///<summary>Only used once. Gets a list of subscriber names from the database that have the specified plan. Used to display in the insplan window. The returned list never includes the inssub that we're viewing.</summary>
public static string[] GetSubscribersForPlan(long planNum, long excludeSub)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <string[]>(MethodBase.GetCurrentMethod(), planNum, excludeSub));
}
string command = "SELECT CONCAT(CONCAT(LName,', '),FName) "
+ "FROM inssub LEFT JOIN patient ON patient.PatNum=inssub.Subscriber "
+ "WHERE inssub.PlanNum=" + POut.Long(planNum) + " "
+ "AND inssub.InsSubNum !=" + POut.Long(excludeSub) + " "
+ " ORDER BY LName,FName";
DataTable table = Db.GetTable(command);
string[] retStr = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
retStr[i] = PIn.String(table.Rows[i][0].ToString());
}
return(retStr);
}
protected override List <Pref> TableToList(DataTable table)
{
//Can't use Crud.PrefCrud.TableToList(table) because it will fail the first time someone runs 7.6 before conversion.
List <Pref> listPrefs = new List <Pref>();
bool containsPrefNum = (table.Columns.Contains("PrefNum"));
foreach (DataRow row in table.Rows)
{
Pref pref = new Pref();
if (containsPrefNum)
{
pref.PrefNum = PIn.Long(row["PrefNum"].ToString());
}
pref.PrefName = PIn.String(row["PrefName"].ToString());
pref.ValueString = PIn.String(row["ValueString"].ToString());
//no need to load up the comments. Especially since this will fail when user first runs version 5.8.
listPrefs.Add(pref);
}
return(listPrefs);
}
///<summary>Returns a list of insplans that are dependent on the Cur carrier. Used to display in carrier edit.</summary>
public static string[] DependentPlans(Carrier Cur)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <string[]>(MethodBase.GetCurrentMethod(), Cur));
}
string command = "SELECT CONCAT(CONCAT(LName,', '),FName) FROM patient,insplan,inssub"
+ " WHERE patient.PatNum=inssub.Subscriber"
+ " AND insplan.PlanNum=inssub.PlanNum"
+ " AND insplan.CarrierNum = '" + POut.Long(Cur.CarrierNum) + "'"
+ " ORDER BY LName,FName";
DataTable table = Db.GetTable(command);
string[] retStr = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
retStr[i] = PIn.String(table.Rows[i][0].ToString());
}
return(retStr);
}
///<summary>Returns a list of insplans that are dependent on the Cur employer. The list includes carriage returns for easy display. Used before deleting an employer to make sure employer is not in use.</summary>
public static string DependentInsPlans(Employer Cur){
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetString(MethodBase.GetCurrentMethod(),Cur);
}
string command="SELECT carrier.CarrierName,CONCAT(CONCAT(patient.LName,', '),patient.FName) "
+"FROM insplan "
+"LEFT JOIN inssub ON insplan.PlanNum=inssub.PlanNum "
+"LEFT JOIN patient ON inssub.Subscriber=patient.PatNum "
+"LEFT JOIN carrier ON insplan.CarrierNum=carrier.CarrierNum "
+"WHERE insplan.EmployerNum = "+POut.Long(Cur.EmployerNum);
DataTable table=Db.GetTable(command);
string retStr="";
for(int i=0;i<table.Rows.Count;i++){
if(i>0){
retStr+="\r\n";//return, newline for multiple names.
}
retStr+=PIn.String(table.Rows[i][1].ToString())+": "+PIn.String(table.Rows[i][0].ToString());
}
return retStr;
}
///<summary>Surround with try/catch.
///No need to pass in usernum, it is set before the remoting role and passed in for logging.</summary>
public static void Update(Carrier carrier, Carrier oldCarrier, long userNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), carrier, oldCarrier, userNum);
return;
}
string command;
DataTable table;
if (CultureInfo.CurrentCulture.Name.EndsWith("CA")) //Canadian. en-CA or fr-CA
{
if (carrier.IsCDA)
{
if (carrier.ElectID == "")
{
throw new ApplicationException(Lans.g("Carriers", "Carrier Identification Number required."));
}
if (!Regex.IsMatch(carrier.ElectID, "^[0-9]{6}$"))
{
throw new ApplicationException(Lans.g("Carriers", "Carrier Identification Number must be exactly 6 numbers."));
}
}
//so the edited carrier looks good, but now we need to make sure that the original was allowed to be changed.
command = "SELECT ElectID,IsCDA FROM carrier WHERE CarrierNum = '" + POut.Long(carrier.CarrierNum) + "'";
table = Db.GetTable(command);
if (PIn.Bool(table.Rows[0]["IsCDA"].ToString()) && //if original carrier IsCDA
PIn.String(table.Rows[0]["ElectID"].ToString()).Trim() != "" && //and the ElectID was already set
PIn.String(table.Rows[0]["ElectID"].ToString()) != carrier.ElectID) //and the ElectID was changed
{
command = "SELECT COUNT(*) FROM etrans WHERE CarrierNum= " + POut.Long(carrier.CarrierNum)
+ " OR CarrierNum2=" + POut.Long(carrier.CarrierNum);
if (Db.GetCount(command) != "0")
{
throw new ApplicationException(Lans.g("Carriers", "Not allowed to change Carrier Identification Number because it's in use in the claim history."));
}
}
}
Crud.CarrierCrud.Update(carrier, oldCarrier);
InsEditLogs.MakeLogEntry(carrier, oldCarrier, InsEditLogType.Carrier, userNum);
}
///<summary>Returns a list of patient names that are attached to this referral. Used to display in the referral edit window.</summary>
public static string[] GetPats(long refNum, bool IsFrom)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <string[]>(MethodBase.GetCurrentMethod(), refNum, IsFrom));
}
string command = "SELECT CONCAT(CONCAT(patient.LName,', '),patient.FName) "
+ "FROM patient,refattach,referral "
+ "WHERE patient.PatNum=refattach.PatNum "
+ "AND refattach.ReferralNum=referral.ReferralNum "
+ "AND refattach.IsFrom=" + POut.Bool(IsFrom)
+ " AND referral.ReferralNum=" + refNum.ToString();
DataTable table = Db.GetTable(command);
string[] retStr = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
retStr[i] = PIn.String(table.Rows[i][0].ToString());
}
return(retStr);
}
///<summary></summary>
public static void FillCache(DataTable table)
{
//No need to check RemotingRole; no call to db.
PrefC.Dict = new Dictionary <string, Pref>();
Pref pref;
//PrefName enumpn;
//Can't use Crud.PrefCrud.TableToList(table) because it will fail the first time someone runs 7.6 before conversion.
for (int i = 0; i < table.Rows.Count; i++)
{
pref = new Pref();
if (table.Columns.Contains("PrefNum"))
{
pref.PrefNum = PIn.Long(table.Rows[i]["PrefNum"].ToString());
}
pref.PrefName = PIn.String(table.Rows[i]["PrefName"].ToString());
pref.ValueString = PIn.String(table.Rows[i]["ValueString"].ToString());
//no need to load up the comments. Especially since this will fail when user first runs version 5.8.
PrefC.Dict.Add(pref.PrefName, pref);
}
}
///<summary>Returns a list of patients that are dependent on the Cur employer. The list includes carriage returns for easy display. Used before deleting an employer to make sure employer is not in use.</summary>
public static string DependentPatients(Employer Cur)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetString(MethodBase.GetCurrentMethod(), Cur));
}
string command = "SELECT CONCAT(CONCAT(LName,', '),FName) FROM patient"
+ " WHERE EmployerNum = '" + POut.Long(Cur.EmployerNum) + "'";
DataTable table = Db.GetTable(command);
string retStr = "";
for (int i = 0; i < table.Rows.Count; i++)
{
if (i > 0)
{
retStr += "\r\n"; //return, newline for multiple names.
}
retStr += PIn.String(table.Rows[i][0].ToString());
}
return(retStr);
}
public static RegistrationKey GetByKey(string regKey)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <RegistrationKey>(MethodBase.GetCurrentMethod(), regKey));
}
if (!Regex.IsMatch(regKey, @"^[A-Z0-9]{16}$"))
{
throw new ApplicationException("Invalid registration key format.");
}
string command = "SELECT * FROM registrationkey WHERE RegKey='" + POut.String(regKey) + "'";
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
throw new ApplicationException("Invalid registration key.");
}
RegistrationKey key = null;
for (int i = 0; i < table.Rows.Count; i++)
{
key = new RegistrationKey();
key.RegistrationKeyNum = PIn.Int(table.Rows[i][0].ToString());
key.PatNum = PIn.Int(table.Rows[i][1].ToString());
key.RegKey = PIn.String(table.Rows[i][2].ToString());
key.Note = PIn.String(table.Rows[i][3].ToString());
key.DateStarted = PIn.Date(table.Rows[i][4].ToString());
key.DateDisabled = PIn.Date(table.Rows[i][5].ToString());
key.DateEnded = PIn.Date(table.Rows[i][6].ToString());
key.IsForeign = PIn.Bool(table.Rows[i][7].ToString());
//key.UsesServerVersion =PIn.PBool(table.Rows[i][8].ToString());
key.IsFreeVersion = PIn.Bool(table.Rows[i][9].ToString());
key.IsOnlyForTesting = PIn.Bool(table.Rows[i][10].ToString());
//key.VotesAllotted =PIn.PInt(table.Rows[i][11].ToString());
}
//if(key.DateDisabled.Year>1880){
// throw new ApplicationException("This key has been disabled. Please call for assistance.");
//}
return(key);
}
public List <T> ToList <T>()
{
Type tp = typeof(T);
//List<object> list=new List<object>();
List <T> list = new List <T>();
FieldInfo[] fieldInfo = tp.GetFields();
Object obj = default(T);
for (int i = 0; i < Rows.Count; i++)
{
ConstructorInfo constructor = tp.GetConstructor(System.Type.EmptyTypes);
obj = constructor.Invoke(null);
for (int f = 0; f < fieldInfo.Length; f++)
{
if (fieldInfo[f].FieldType == typeof(int))
{
fieldInfo[f].SetValue(obj, PIn.Long(Rows[i][f]));
}
else if (fieldInfo[f].FieldType == typeof(bool))
{
fieldInfo[f].SetValue(obj, PIn.Bool(Rows[i][f]));
}
else if (fieldInfo[f].FieldType == typeof(string))
{
fieldInfo[f].SetValue(obj, PIn.String(Rows[i][f]));
}
else if (fieldInfo[f].FieldType.IsEnum)
{
object val = ((object[])Enum.GetValues(fieldInfo[f].FieldType))[PIn.Long(Rows[i][f])];
fieldInfo[f].SetValue(obj, val);
}
}
list.Add((T)obj);
//Collection
}
return(list); //(List<T>)list.Cast<T>();
}
///<summary></summary>
private static List <ClaimPaySplit> ClaimPaySplitTableToList(DataTable table)
{
//No need to check RemotingRole; no call to db.
List <ClaimPaySplit> splits = new List <ClaimPaySplit>();
ClaimPaySplit split;
for (int i = 0; i < table.Rows.Count; i++)
{
split = new ClaimPaySplit();
split.DateClaim = PIn.Date(table.Rows[i]["DateService"].ToString());
split.ProvAbbr = Providers.GetAbbr(PIn.Long(table.Rows[i]["ProvTreat"].ToString()));
split.PatName = PIn.String(table.Rows[i]["patName_"].ToString());
split.PatNum = PIn.Long(table.Rows[i]["PatNum"].ToString());
split.Carrier = PIn.String(table.Rows[i]["CarrierName"].ToString());
split.FeeBilled = PIn.Double(table.Rows[i]["feeBilled_"].ToString());
split.InsPayAmt = PIn.Double(table.Rows[i]["insPayAmt_"].ToString());
split.ClaimNum = PIn.Long(table.Rows[i]["ClaimNum"].ToString());
split.ClaimPaymentNum = PIn.Long(table.Rows[i]["ClaimPaymentNum"].ToString());
split.PaymentRow = PIn.Int(table.Rows[i]["PaymentRow"].ToString());
splits.Add(split);
}
return(splits);
}
///<summary>Gets a list of all dunnings.</summary>
public static Dunning[] Refresh()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <Dunning[]>(MethodBase.GetCurrentMethod()));
}
string command = "SELECT * FROM dunning "
+ "ORDER BY BillingType,AgeAccount,InsIsPending";
DataTable table = Db.GetTable(command);
Dunning[] List = new Dunning[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
List[i] = new Dunning();
List[i].DunningNum = PIn.Long(table.Rows[i][0].ToString());
List[i].DunMessage = PIn.String(table.Rows[i][1].ToString());
List[i].BillingType = PIn.Long(table.Rows[i][2].ToString());
List[i].AgeAccount = PIn.Byte(table.Rows[i][3].ToString());
List[i].InsIsPending = (YN)PIn.Long(table.Rows[i][4].ToString());
List[i].MessageBold = PIn.String(table.Rows[i][5].ToString());
}
return(List);
}
///<summary></summary>
public static List <LabTurnaround> GetForLab(long laboratoryNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <LabTurnaround> >(MethodBase.GetCurrentMethod(), laboratoryNum));
}
string command = "SELECT * FROM labturnaround WHERE LaboratoryNum=" + POut.Long(laboratoryNum);
DataTable table = Db.GetTable(command);
List <LabTurnaround> retVal = new List <LabTurnaround>();
LabTurnaround lab;
for (int i = 0; i < table.Rows.Count; i++)
{
lab = new LabTurnaround();
lab.LabTurnaroundNum = PIn.Long(table.Rows[i][0].ToString());
lab.LaboratoryNum = PIn.Long(table.Rows[i][1].ToString());
lab.Description = PIn.String(table.Rows[i][2].ToString());
lab.DaysPublished = PIn.Int(table.Rows[i][3].ToString());
lab.DaysActual = PIn.Int(table.Rows[i][4].ToString());
retVal.Add(lab);
}
return(retVal);
}
///<summary>Attempts to fill the list of engineers from the wikilist. Fills with empty if something failed</summary>
private static void FillEngineerList()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod());
return;
}
_listEngineers = new List <Engineer>();
try {
string command = "SELECT Title,EmployeeNum FROM wikilist_employees WHERE Title LIKE '%Engineer%'";
DataTable dt = Db.GetTable(command);
foreach (DataRow dr in dt.Rows)
{
Employee emp = Employees.GetEmp(PIn.Long(dr["EmployeeNum"].ToString()));
Userod user = Userods.GetUserByEmployeeNum(emp.EmployeeNum);
Engineer newEngineer = new Engineer(user, emp, PIn.String(dr["Title"].ToString()));
_listEngineers.Add(newEngineer);
}
}
catch (Exception e) {
//Do nothing
}
}
///<summary>Gets the names of tables in InnoDB format, comma delimited (excluding the 'phone' table). Returns empty string if none.</summary>
public static string GetInnodbTableNames()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetString(MethodBase.GetCurrentMethod()));
}
//Using COUNT(*) with INFORMATION_SCHEMA is buggy. It can return "1" even if no results.
string command = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables "
+ "WHERE TABLE_SCHEMA='" + POut.String(DataConnection.GetDatabaseName()) + "' "
+ "AND TABLE_NAME!='phone' " //this table is used internally at OD HQ, and is always innodb.
+ "AND ENGINE NOT LIKE 'MyISAM'";
DataTable table = Db.GetTable(command);
string tableNames = "";
for (int i = 0; i < table.Rows.Count; i++)
{
if (tableNames != "")
{
tableNames += ",";
}
tableNames += PIn.String(table.Rows[i][0].ToString());
}
return(tableNames);
}
///<summary>Simpler way to get a string from a DataRow.</summary>
public static string GetString(this DataRow row, string columnName)
{
return(PIn.String(row[columnName].ToString()));
}
///<summary>Backs up the database to the same directory as the original just in case the user did not have sense enough to do a backup first.
///Does not work for Oracle, due to some MySQL specific commands inside.</summary>
public static void MakeABackup(string serverName = "", string user = "", string pass = "", bool doVerify = false)
{
//This function should always make the backup on the server itself, and since no directories are
//referred to (all handled with MySQL), this function will always be referred to the server from
//client machines.
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), serverName, user, pass, doVerify);
return;
}
//UpdateStreamLinePassword is purposefully named poorly and used in an odd fashion to sort of obfuscate it from our users.
//GetStringNoCache() will return blank if pref does not exist.
if (PrefC.GetStringNoCache(PrefName.UpdateStreamLinePassword) == "abracadabra")
{
return;
}
string currentServerName = DataConnection.GetServerName().ToLower();
bool useSameServer = string.IsNullOrWhiteSpace(serverName) || currentServerName.Equals(serverName, StringComparison.CurrentCultureIgnoreCase);
if (!string.IsNullOrWhiteSpace(serverName) && currentServerName == "localhost" && serverName.ToLower() != "localhost") //there could be a mismatch but technically the same server
{
useSameServer = serverName.Equals(Environment.MachineName, StringComparison.CurrentCultureIgnoreCase);
}
if (serverName.ToLower() == "localhost" && currentServerName != "localhost") //there could be a mismatch but technically the same server
{
useSameServer = currentServerName.Equals(Environment.MachineName, StringComparison.CurrentCultureIgnoreCase);
}
//only used in two places: upgrading version, and upgrading mysql version.
//Both places check first to make sure user is using mysql.
//we have to be careful to throw an exception if the backup is failing.
DataConnection dcon = new DataConnection();
//if they provided a different server where they want their backup to be, we need a separate connection for that
DataConnection dconBackupServer = useSameServer ? new DataConnection() : new DataConnection(serverName, "", user, pass, DatabaseType.MySql);
//Check that the backup server does not already contain this database
string command = "SELECT database()";
DataTable table = dcon.GetTable(command);
string oldDb = PIn.String(table.Rows[0][0].ToString());
string newDb = oldDb + "backup_" + DateTime.Today.ToString("MM_dd_yyyy");
command = "SHOW DATABASES";
table = dconBackupServer.GetTable(command);
string[] databases = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
databases[i] = table.Rows[i][0].ToString();
}
if (Contains(databases, newDb)) //if the new database name already exists
//find a unique one
{
int uniqueID = 1;
string originalNewDb = newDb;
do
{
newDb = originalNewDb + "_" + uniqueID.ToString();
uniqueID++;
}while(Contains(databases, newDb));
}
command = "CREATE DATABASE `" + newDb + "` CHARACTER SET utf8";
dconBackupServer.NonQ(command); //create the backup db on the backup server
//But get the tables from the current, not the backup server
command = "SHOW FULL TABLES WHERE Table_type='BASE TABLE'"; //Tables, not views. Does not work in MySQL 4.1, however we test for MySQL version >= 5.0 in PrefL.
table = dcon.GetTable(command);
//Set the connection to the new database now that it has been created
dconBackupServer = useSameServer?new DataConnection(newDb):new DataConnection(serverName, newDb, user, pass, DatabaseType.MySql);
foreach (DataRow row in table.Rows)
{
string tableName = row[0].ToString();
//First create the table on the new db
MiscDataEvent.Fire(ODEventType.MiscData, $"Backing up table: {tableName}");
//also works with views. Added backticks around table name for unusual characters.
command = $"SHOW CREATE TABLE `{oldDb}`.`{tableName}`";
DataTable dtCreate = dcon.GetTable(command);
command = PIn.ByteArray(dtCreate.Rows[0][1]);
//The backup database tables will be MyISAM because it is significantly faster at doing bulk inserts.
command = command.Replace("ENGINE=InnoDB", "ENGINE=MyISAM");
dconBackupServer.NonQ(command);
//Then copy the data into the new table
if (useSameServer)
{
//If on the same server we can select into directly, which is faster
command = $"INSERT INTO `{newDb}`.`{tableName}` SELECT * FROM `{oldDb}`.`{tableName}`"; //Added backticks around table name for unusual characters.
dconBackupServer.NonQ(command);
}
else
{
long count = PIn.Long(dcon.GetCount($"SELECT COUNT(*) FROM `{oldDb}`.`{tableName}`"));
int limit = 10000;
if (tableName == "documentmisc") //This table can have really large rows so just to be safe, handle the backup one row at a time
{
limit = 1;
}
int offset = 0;
while (count > offset)
{
DataTable dtOld = dcon.GetTable($" SELECT * FROM `{oldDb}`.`{tableName}` LIMIT {limit} OFFSET {offset}");
offset += dtOld.Rows.Count;
dconBackupServer.BulkCopy(dtOld, tableName);
}
}
}
//Verify that the old database and the new backup have the same number of rows
if (doVerify)
{
List <string> listTablesFailed = new List <string>();
foreach (DataRow dbTable in table.Rows)
{
string tableName = dbTable[0].ToString();
MiscDataEvent.Fire(ODEventType.MiscData, $"Verifying backup: {tableName}");
int ctOld = PIn.Int(dcon.GetCount($"SELECT COUNT(*) FROM `{oldDb}`.`{tableName}`"));
int ctNew = PIn.Int(dconBackupServer.GetCount($"SELECT COUNT(*) FROM `{newDb}`.`{tableName}`"));
if (ctOld != ctNew)
{
listTablesFailed.Add(tableName);
}
}
if (listTablesFailed.Count > 0)
{
throw new Exception($@"Failed to create database backup because the following tables contained a different number of rows than expected:
{string.Join(", ",listTablesFailed)}." );
}
}
}
///<summary></summary>
public static DataTable GetOrderTable(long patNum, bool includeDiscontinued)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), patNum, includeDiscontinued));
}
DataTable table = new DataTable("orders");
DataRow row;
table.Columns.Add("date");
table.Columns.Add("DateTime", typeof(DateTime));
table.Columns.Add("description");
table.Columns.Add("MedicalOrderNum");
table.Columns.Add("MedicationPatNum");
table.Columns.Add("prov");
table.Columns.Add("status");
table.Columns.Add("type");
List <DataRow> rows = new List <DataRow>();
string command = "SELECT DateTimeOrder,Description,IsDiscontinued,MedicalOrderNum,MedOrderType,ProvNum "
+ "FROM medicalorder WHERE PatNum = " + POut.Long(patNum);
if (!includeDiscontinued) //only include current orders
{
command += " AND IsDiscontinued=0"; //false
}
DataTable rawOrder = Db.GetTable(command);
DateTime dateT;
MedicalOrderType medOrderType;
long medicalOrderNum;
bool isDiscontinued;
for (int i = 0; i < rawOrder.Rows.Count; i++)
{
row = table.NewRow();
dateT = PIn.DateT(rawOrder.Rows[i]["DateTimeOrder"].ToString());
medOrderType = (MedicalOrderType)PIn.Int(rawOrder.Rows[i]["MedOrderType"].ToString());
medicalOrderNum = PIn.Long(rawOrder.Rows[i]["MedicalOrderNum"].ToString());
row["DateTime"] = dateT;
row["date"] = dateT.ToShortDateString();
row["description"] = PIn.String(rawOrder.Rows[i]["Description"].ToString());
if (medOrderType == MedicalOrderType.Laboratory)
{
List <LabPanel> listPanelsForOrder = LabPanels.GetPanelsForOrder(medicalOrderNum);
for (int p = 0; p < listPanelsForOrder.Count; p++)
{
row["description"] += "\r\n "; //new row for each panel
List <LabResult> listResults = LabResults.GetForPanel(listPanelsForOrder[p].LabPanelNum);
if (listResults.Count > 0)
{
row["description"] += listResults[0].DateTimeTest.ToShortDateString() + " - ";
}
row["description"] += listPanelsForOrder[p].ServiceName;
}
}
row["MedicalOrderNum"] = medicalOrderNum.ToString();
row["MedicationPatNum"] = "0";
row["prov"] = Providers.GetAbbr(PIn.Long(rawOrder.Rows[i]["ProvNum"].ToString()));
isDiscontinued = PIn.Bool(rawOrder.Rows[i]["IsDiscontinued"].ToString());
if (isDiscontinued)
{
row["status"] = "Discontinued";
}
else
{
row["status"] = "Active";
}
row["type"] = medOrderType.ToString();
rows.Add(row);
}
//Medications are deprecated for 2014 edition
//MedicationPats
//command="SELECT DateStart,DateStop,MedicationPatNum,CASE WHEN medication.MedName IS NULL THEN medicationpat.MedDescript ELSE medication.MedName END MedName,PatNote,ProvNum "
// +"FROM medicationpat "
// +"LEFT OUTER JOIN medication ON medication.MedicationNum=medicationpat.MedicationNum "
// +"WHERE PatNum = "+POut.Long(patNum);
//if(!includeDiscontinued) {//exclude invalid orders
// command+=" AND DateStart > "+POut.Date(new DateTime(1880,1,1))+" AND PatNote !='' "
// +"AND (DateStop < "+POut.Date(new DateTime(1880,1,1))+" "//no date stop
// +"OR DateStop >= "+POut.Date(DateTime.Today)+")";//date stop hasn't happened yet
//}
//DataTable rawMed=Db.GetTable(command);
//DateTime dateStop;
//for(int i=0;i<rawMed.Rows.Count;i++) {
// row=table.NewRow();
// dateT=PIn.DateT(rawMed.Rows[i]["DateStart"].ToString());
// row["DateTime"]=dateT;
// if(dateT.Year<1880) {
// row["date"]="";
// }
// else {
// row["date"]=dateT.ToShortDateString();
// }
// row["description"]=PIn.String(rawMed.Rows[i]["MedName"].ToString())+", "
// +PIn.String(rawMed.Rows[i]["PatNote"].ToString());
// row["MedicalOrderNum"]="0";
// row["MedicationPatNum"]=rawMed.Rows[i]["MedicationPatNum"].ToString();
// row["prov"]=Providers.GetAbbr(PIn.Long(rawMed.Rows[i]["ProvNum"].ToString()));
// dateStop=PIn.Date(rawMed.Rows[i]["DateStop"].ToString());
// if(dateStop.Year<1880 || dateStop>DateTime.Today) {//not stopped or in the future
// row["status"]="Active";
// }
// else {
// row["status"]="Discontinued";
// }
// row["type"]="Medication";
// rows.Add(row);
//}
//Sorting-----------------------------------------------------------------------------------------
rows.Sort(new MedicalOrderLineComparer());
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
return(table);
}
///<summary>Also updates existing journal entry splits linked to this account that have not been locked.</summary>
public static void Update(Account acct, Account acctOld)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), acct, acctOld);
return;
}
Crud.AccountCrud.Update(acct, acctOld);
if (acct.Description == acctOld.Description)
{
return; //No need to update splits on attached journal entries.
}
//The account was renamed, so update journalentry.Splits.
string command = @"SELECT je2.*,account.Description
FROM journalentry
INNER JOIN journalentry je2 ON je2.TransactionNum=journalentry.TransactionNum
INNER JOIN account ON account.AccountNum=je2.AccountNum
WHERE journalentry.AccountNum=" + POut.Long(acct.AccountNum) + @"
AND journalentry.DateDisplayed > " + POut.Date(PrefC.GetDate(PrefName.AccountingLockDate)) + @"
ORDER BY je2.TransactionNum" ;
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return;
}
List <JournalEntry> listJournalEntries = Crud.JournalEntryCrud.TableToList(table);
//Construct a dictionary that has the description for each JournalEntryNum.
Dictionary <long, string> dictJournalEntryDescriptions = table.Rows.Cast <DataRow>()
.GroupBy(x => PIn.Long(x["JournalEntryNum"].ToString()))
.ToDictionary(x => x.Key, x => PIn.String(x.First()["Description"].ToString()));
//Now we will loop through all the journal entries and find the other journal entries that are attached to the same transaction and update
//those splits.
List <int> listIndexesForTrans = new List <int>();
long curTransactionNum = listJournalEntries[0].TransactionNum;
for (int i = 0; i < listJournalEntries.Count; i++)
{
if (listJournalEntries[i].TransactionNum == curTransactionNum)
{
listIndexesForTrans.Add(i);
continue;
}
UpdateJournalEntrySplits(listJournalEntries, listIndexesForTrans, dictJournalEntryDescriptions, acct);
curTransactionNum = listJournalEntries[i].TransactionNum;
listIndexesForTrans.Clear();
listIndexesForTrans.Add(i);
}
UpdateJournalEntrySplits(listJournalEntries, listIndexesForTrans, dictJournalEntryDescriptions, acct);
}
public static void AddUnreads(long taskNum, long curUserNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), taskNum, curUserNum);
return;
}
//if the task is done, don't add unreads
string command = "SELECT TaskStatus,UserNum,ReminderGroupId,DateTimeEntry," + DbHelper.Now() + " DbTime "
+ "FROM task WHERE TaskNum = " + POut.Long(taskNum);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return; //only happens when a task was deleted by one user but left open on another user's computer.
}
TaskStatusEnum taskStatus = (TaskStatusEnum)PIn.Int(table.Rows[0]["TaskStatus"].ToString());
long userNumOwner = PIn.Long(table.Rows[0]["UserNum"].ToString());
if (taskStatus == TaskStatusEnum.Done) //
{
return;
}
//Set it unread for the original owner of the task.
if (userNumOwner != curUserNum) //but only if it's some other user
{
SetUnread(userNumOwner, taskNum);
}
//Set it for this user if a future repeating task, so it will be new when "due". Doing this here so we don't check every row below.
//Only for future dates because we don't want to mark as new if it was already "due" and you added a note or something.
if ((PIn.String(table.Rows[0]["ReminderGroupId"].ToString()) != "") && //Is a reminder
(PIn.DateT(table.Rows[0]["DateTimeEntry"].ToString()) > PIn.DateT(table.Rows[0]["DbTime"].ToString()))) //Is "due" in the future by DbTime
{
SetUnread(curUserNum, taskNum); //Set unread for current user only, other users dealt with below.
}
//Then, for anyone subscribed
long userNum;
bool isUnread;
//task subscriptions are not cached yet, so we use a query.
//Get a list of all subscribers to this task
command = @"SELECT
tasksubscription.UserNum,
(CASE WHEN taskunread.UserNum IS NULL THEN 0 ELSE 1 END) IsUnread
FROM tasksubscription
INNER JOIN tasklist ON tasksubscription.TaskListNum = tasklist.TaskListNum
INNER JOIN taskancestor ON taskancestor.TaskListNum = tasklist.TaskListNum
AND taskancestor.TaskNum = " + POut.Long(taskNum) + " ";
command += "LEFT JOIN taskunread ON taskunread.UserNum = tasksubscription.UserNum AND taskunread.TaskNum=taskancestor.TaskNum";
table = Db.GetTable(command);
List <long> listUserNums = new List <long>();
for (int i = 0; i < table.Rows.Count; i++)
{
userNum = PIn.Long(table.Rows[i]["UserNum"].ToString());
isUnread = PIn.Bool(table.Rows[i]["IsUnread"].ToString());
if (userNum == userNumOwner || //already set
userNum == curUserNum || //If the current user is subscribed to this task. User has obviously already read it.
listUserNums.Contains(userNum) ||
isUnread) //Unread currently exists
{
continue;
}
listUserNums.Add(userNum);
}
SetUnreadMany(listUserNums, taskNum); //This no longer results in duplicates like it used to
}
///<summary>This is a potential fix to be backported to 13.2 so that patient lists can be used for MU1 2013. on large databases these queries take way to long to run. (At least several minutes).</summary>
public static DataTable GetListOrderBy2014Retro(List <EhrPatListElement> elementList)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), elementList));
}
DataTable table = new DataTable();
string select = "SELECT patient.PatNum,patient.LName,patient.FName";
string from = "FROM patient";
string where = "WHERE TRUE "; //Makes formatting easier when adding additional clauses because they will all be AND clauses.
for (int i = 0; i < elementList.Count; i++)
{
switch (elementList[i].Restriction)
{
case EhrRestrictionType.Birthdate: //---------------------------------------------------------------------------------------------------------------------------
select += ",patient.BirthDate ";
from += ""; //only selecting from patient table
where += "AND ((YEAR(CURDATE())-YEAR(DATE(patient.Birthdate))) - (RIGHT(CURDATE(),5)<RIGHT(DATE(patient.Birthdate),5)))" + GetOperandText(elementList[i].Operand) + "" + PIn.String(elementList[i].CompareString) + " ";
break;
case EhrRestrictionType.Gender: //------------------------------------------------------------------------------------------------------------------------------
select += ",patient.Gender"; //will look odd if user adds multiple gender columns, enum needs to be "decoded" when filling grid.
break;
case EhrRestrictionType.LabResult: //---------------------------------------------------------------------------------------------------------------------------
select += ",labresult" + i + ".ObsValue"; //format column name when filling grid.
from += ",labresult AS labresult" + i + ", labpanel AS labpanel" + i;
where += "AND labpanel" + i + ".LabpanelNum=labresult" + i + ".LabpanelNum AND patient.PatNum=labpanel" + i + ".PatNum "; //join
where += "AND labresult" + i + ".TestId='" + elementList[i].CompareString + "' "
+ "AND labresult" + i + ".ObsValue" + GetOperandText(elementList[i].Operand) + "'" + PIn.String(elementList[i].LabValue) + "' "; //filter
break;
case EhrRestrictionType.Medication: //--------------------------------------------------------------------------------------------------------------------------
select += ",'X'"; //select+=",medicationpat"+i+".DateStart";
from += ",medication AS medication" + i + ", medicationpat AS medicationpat" + i;
where += "AND medicationpat" + i + ".PatNum=patient.PatNum "; //join
//This is unusual. Part of the join logic is in the code below because medicationPat.MedicationNum might be 0 if it came from newcrop.
where += "AND ((medication" + i + ".MedicationNum=MedicationPat" + i + ".MedicationNum AND medication" + i + ".MedName LIKE '%" + PIn.String(elementList[i].CompareString) + "%') "
+ " OR (medication" + i + ".MedicationNum=0 AND medicationpat" + i + ".MedDescript LIKE '%" + PIn.String(elementList[i].CompareString) + "%')) ";
break;
case EhrRestrictionType.Problem: //-----------------------------------------------------------------------------------------------------------------------------
select += ",Concat('(',diseasedef" + i + ".ICD9Code,') - ',diseasedef" + i + ".DiseaseName) as Disease" + i + " "; //Name of problem will be in column title.
from += ",disease AS disease" + i + ", diseasedef AS diseasedef" + i;
where += "AND diseasedef" + i + ".DiseaseDefNum=disease" + i + ".DiseaseDefNum AND disease" + i + ".PatNum=patient.PatNum "; //join
where += "AND diseasedef" + i + ".ICD9Code='" + PIn.String(elementList[i].CompareString) + "' "; //filter
break;
default:
//Can happen because EhrRestrictionType was expanded for 2014.
//If we reach this point in the code, we will effectively just ignore the pat list element.
continue;
}
}
string command = select + " " + from + " " + where + " ORDER BY Patient.LName, Patient.FName";
return(Db.GetTable(command));
}
public static DataTable GetListOrderBy2014(List <EhrPatListElement2014> elementList)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), elementList));
}
DataTable table = new DataTable();
string select = "SELECT patient.PatNum,patient.LName,patient.FName";
string from = "FROM patient";
string where = "WHERE TRUE "; //Makes formatting easier when adding additional clauses because they will all be AND clauses.
for (int i = 0; i < elementList.Count; i++)
{
switch (elementList[i].Restriction)
{
case EhrRestrictionType.Birthdate: //---------------------------------------------------------------------------------------------------------------------------
select += ",patient.BirthDate, ((YEAR(CURDATE())-YEAR(DATE(patient.Birthdate))) - (RIGHT(CURDATE(),5)<RIGHT(DATE(patient.Birthdate),5))) AS Age";
from += ""; //only selecting from patient table
where += "AND ((YEAR(CURDATE())-YEAR(DATE(patient.Birthdate))) - (RIGHT(CURDATE(),5)<RIGHT(DATE(patient.Birthdate),5)))" + GetOperandText(elementList[i].Operand) + "" + PIn.String(elementList[i].CompareString) + " ";
break;
case EhrRestrictionType.Gender: //------------------------------------------------------------------------------------------------------------------------------
select += ",patient.Gender"; //will look odd if user adds multiple gender columns, enum needs to be "decoded" when filling grid.
break;
case EhrRestrictionType.LabResult: //---------------------------------------------------------------------------------------------------------------------------
//TODO Units
from += ",ehrlab AS ehrlab" + i + ",ehrlabresult AS ehrlabresult" + i + " ";
where += "AND ehrlab" + i + ".PatNum=patient.PatNum AND ehrlab" + i + ".EhrLabNum=ehrlabresult" + i + ".EhrLabNum "; //join
where += "AND ('" + elementList[i].CompareString + "'=ehrlabresult" + i + ".ObservationIdentifierID OR '"
+ elementList[i].CompareString + "'=ehrlabresult" + i + ".ObservationIdentifierIDAlt) "; //filter, LOINC of lab observation
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND ehrlabresult" + i + ".ObservationDateTime >=" + POut.Date(elementList[i].StartDate) + " "; //on or after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND ehrlabresult" + i + ".ObservationDateTime <=" + POut.Date(elementList[i].EndDate) + " "; //on or before this date
}
switch (elementList[i].LabValueType)
{
//CE and CWE should be SNOMEDCT codes, string compare elementList[i].LabValue to ehrlabresult.ObservationValueCodedElementID or ObservationValueCodedElementIDAlt
case HL70125.CE:
case HL70125.CWE:
select += ",(CASE WHEN ehrlabresult" + i + ".ObservationValueCodedElementID='' THEN ehrlabresult" + i + ".ObservationValueCodedElementIDAlt ELSE ehrlabresult" + i + ".ObservationValueCodedElementID END) AS LabValue";
where += "AND (ehrlabresult" + i + ".ObservationValueCodedElementID='" + elementList[i].LabValue + "' OR "
+ "ehrlabresult" + i + ".ObservationValueCodedElementIDAlt='" + elementList[i].LabValue + "') "
+ "AND (ehrlabresult" + i + ".ValueType='CWE' OR ehrlabresult" + i + ".ValueType='CE') ";
break;
//DT is stored as a string in ehrlabresult.ObservationValueDateTime as YYYY[MM[DD]]
case HL70125.DT:
select += ",ehrlabresult" + i + ".ObservationValueDateTime "; //+DbHelper.DateFormatColumn("RPAD(ehrlabresult"+i+".ObservationValueDateTime,8,'01')","%m/%d/%Y");
where += "AND " + DbHelper.DtimeToDate("RPAD(ehrlabresult" + i + ".ObservationValueDateTime,8,'01')")
+ GetOperandText(elementList[i].Operand) + "'" + POut.String(elementList[i].LabValue) + "' "
+ "AND ehrlabresult" + i + ".ValueType='DT' ";
break;
//TS is YYYYMMDDHHMMSS, string compare
case HL70125.TS:
select += ",ehrlabresult" + i + ".ObservationValueDateTime "; //+DbHelper.DateTFormatColumn("ehrlabresult"+i+".ObservationValueDateTime","%m/%d/%Y %H:%i:%s");
where += "AND ehrlabresult" + i + ".ObservationValueDateTime " //+POut.DateT(PIn.DateT(DbHelper.DateTFormatColumn("ehrlabresult"+i+".ObservationValueDateTime","%m/%d/%Y %H:%i:%s")))
+ GetOperandText(elementList[i].Operand) + "'" + POut.String(elementList[i].LabValue) + "' "
+ "AND ehrlabresult" + i + ".ValueType='TS' ";
break;
//00:00:00
case HL70125.TM:
select += ",ehrlabresult" + i + ".ObservationValueTime";
where += "AND ehrlabresult" + i + ".ObservationValueTime" + GetOperandText(elementList[i].Operand) + "'" + POut.TSpan(PIn.TSpan(elementList[i].LabValue)) + "' "
+ "AND ehrlabresult" + i + ".ValueType='TM' ";
break;
case HL70125.SN:
select += ",CONCAT(CONCAT(CONCAT(ehrlabresult" + i + ".ObservationValueComparator,ehrlabresult" + i + ".ObservationValueNumber1),ehrlabresult" + i + ".ObservationValueSeparatorOrSuffix),ehrlabresult" + i + ".ObservationValueNumber2)";
where += "AND ehrlabresult" + i + ".ValueType='SN' ";
break;
case HL70125.NM:
select += ",ehrlabresult" + i + ".ObservationValueNumeric";
where += "AND ehrlabresult" + i + ".ObservationValueNumeric" + GetOperandText(elementList[i].Operand) + POut.Double(PIn.Double(elementList[i].LabValue)) + " "
+ "AND ehrlabresult" + i + ".ValueType='NM' ";
break;
case HL70125.FT:
case HL70125.ST:
case HL70125.TX:
select += ",ehrlabresult" + i + ".ObservationValueText";
//where+="AND ehrlabresult"+i+".ObservationValueText"+GetOperandText(elementList[i].Operand)+POut.String(elementList[i].LabValue)+" "
where += "AND (ehrlabresult" + i + ".ValueType='FT' OR ehrlabresult" + i + ".ValueType='ST' OR ehrlabresult" + i + ".ValueType='TX') ";
break;
}
select += ",ehrlabresult" + i + ".ObservationDateTime ";
//select+=",labresult"+i+".ObsValue,labresult"+i+".DateTimeTest";//format column name when filling grid.
//from+=",labresult AS labresult"+i+", labpanel AS labpanel"+i;
//where+="AND labpanel"+i+".LabpanelNum=labresult"+i+".LabpanelNum AND patient.PatNum=labpanel"+i+".PatNum ";//join
//where+="AND labresult"+i+".TestId='"+elementList[i].CompareString+"' "
// +"AND labresult"+i+".ObsValue"+GetOperandText(elementList[i].Operand)+"'"+PIn.String(elementList[i].LabValue)+"' ";//filter
//if(elementList[i].StartDate!=null && elementList[i].StartDate.Year>1880) {
// where+="AND labresult"+i+".DateTimeTest>"+POut.Date(elementList[i].StartDate)+" ";//after this date
//}
//if(elementList[i].EndDate!=null && elementList[i].EndDate.Year>1880) {
// where+="AND labresult"+i+".DateTimeTest<"+POut.Date(elementList[i].EndDate)+" ";//before this date
//}
break;
case EhrRestrictionType.Medication: //--------------------------------------------------------------------------------------------------------------------------
select += ",medicationpat" + i + ".DateStart"; //Name of medication will be in column title.
from += ",medication AS medication" + i + ", medicationpat AS medicationpat" + i;
where += "AND medicationpat" + i + ".PatNum=patient.PatNum "; //join
//This is unusual. Part of the join logic is in the code below because medicationPat.MedicationNum might be 0 if it came from newcrop.
where += "AND ((medication" + i + ".MedicationNum=MedicationPat" + i + ".MedicationNum AND medication" + i + ".MedName LIKE '%" + PIn.String(elementList[i].CompareString) + "%') "
+ " OR (medication" + i + ".MedicationNum=0 AND medicationpat" + i + ".MedDescript LIKE '%" + PIn.String(elementList[i].CompareString) + "%')) ";
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND medicationpat" + i + ".DateStart>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND medicationpat" + i + ".DateStart<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.Problem: //-----------------------------------------------------------------------------------------------------------------------------
select += ",disease" + i + ".DateStart"; //Name of problem will be in column title.
from += ",disease AS disease" + i + ", diseasedef AS diseasedef" + i;
where += "AND diseasedef" + i + ".DiseaseDefNum=disease" + i + ".DiseaseDefNum AND disease" + i + ".PatNum=patient.PatNum "; //join
where += "AND (diseasedef" + i + ".ICD9Code='" + PIn.String(elementList[i].CompareString) + "' OR diseasedef" + i + ".SnomedCode='" + PIn.String(elementList[i].CompareString) + "') "; //filter
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND disease" + i + ".DateStart>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND disease" + i + ".DateStart<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.Allergy: //-----------------------------------------------------------------------------------------------------------------------------
select += ",allergy" + i + ".DateAdverseReaction"; //Name of allergy will be in column title.
from += ",allergy AS allergy" + i + ", allergydef AS allergydef" + i;
where += "AND allergydef" + i + ".AllergyDefNum=allergy" + i + ".AllergyDefNum AND allergy" + i + ".PatNum=patient.PatNum "; //join
where += "AND allergydef" + i + ".Description='" + PIn.String(elementList[i].CompareString) + "' "; //filter
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND allergy" + i + ".DateAdverseReaction>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND allergy" + i + ".DateAdverseReaction<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.CommPref: //----------------------------------------------------------------------------------------------------------------------------
select += ",patient.PreferContactConfidential";
from += ""; //only selecting from patient table
where += "AND patient.PreferContactConfidential=" + PIn.Int(contactMethodHelper(elementList[i].CompareString)) + " ";
break;
default:
//should never happen.
continue;
}
}
string command = select + " " + from + " " + where;
return(Db.GetTable(command));
}
