/// <summary></summary>
/// <param name="ItemTable">Should be a DataTable object with a single DataRow containing the item.</param>
public static void UpdateItem(string listName, DataTable ItemTable)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), listName, ItemTable);
return;
}
if (ItemTable.Columns.Count < 2)
{
//if the table contains only a PK column.
return;
}
string command = "UPDATE wikilist_" + POut.String(listName) + " SET "; //inserts empty row with auto generated PK.
for (int i = 1; i < ItemTable.Columns.Count; i++) //start at 1 because we do not need to update the PK
{
command += POut.String(ItemTable.Columns[i].ColumnName) + "='" + POut.String(ItemTable.Rows[0][i].ToString()) + "',";
}
command = command.Trim(',') + " WHERE " + POut.String(listName) + "Num = " + ItemTable.Rows[0][0];
Db.NonQ(command);
}
/*
* ///<summary>not used to update the english version of text. Create new instead.</summary>
* public static void UpdateCur(){
* string command="UPDATE language SET "
+"EnglishComments = '" +POut.PString(Cur.EnglishComments)+"'"
+",IsObsolete = '" +POut.PBool (Cur.IsObsolete)+"'"
+" WHERE ClassType = BINARY '"+POut.PString(Cur.ClassType)+"'"
+" AND English = BINARY '" +POut.PString(Cur.English)+"'";
* NonQ(false);
* }*/
///<summary>No need to refresh after this.</summary>
public static void DeleteItems(string classType, List <string> englishList)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), classType, englishList);
return;
}
string command = "DELETE FROM language WHERE ClassType='" + POut.String(classType) + "' AND (";
for (int i = 0; i < englishList.Count; i++)
{
if (i > 0)
{
command += "OR ";
}
command += "English='" + POut.String(englishList[i]) + "' ";
_languageCache.RemoveKey(classType + englishList[i]);
}
command += ")";
Db.NonQ(command);
}
//also table with special chars: |, <, >, &, ', ", and \
public static DataTable GetTableSpecialChars()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod()));
}
//Special characters in the columns as well as in the column names
string command = "SELECT '" + POut.String("cell00|") + "' AS '|<>','" + POut.String("cell01<") + "' AS '&\\'\"\\\\' "
+ "UNION ALL "
+ "SELECT '" + POut.String("cell10>") + "','" + POut.String("cell11&") + "' "
+ "UNION ALL "
+ "SELECT '" + POut.String("cell20\'") + "','" + POut.String("cell21\"") + "' "
+ "UNION ALL "
+ "SELECT '" + POut.String("cell30\\") + "','" + POut.String("cell31/") + "'";
DataTable table = Db.GetTable(command);
table.TableName = "Table|<>&'\"\\";
table.Columns.Add("DirtyString");
table.Rows[0]["DirtyString"] = DirtyString;
return(table);
}
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);
}
///<summary>Called after file is downloaded. Throws exceptions. It is assumed that this is called from a worker thread. Progress delegate will be called every 100th iteration to inform thread of current progress. Quit flag can be set at any time in order to quit importing prematurely.</summary>
public static void ImportIcd9(string tempFileName, ProgressArgs progress, ref bool quit)
{
if (tempFileName == null)
{
return;
}
//Customers may have an old codeset that has a truncated uppercase description, if so we want to update with new descriptions.
bool IsOldDescriptions = ICD9s.IsOldDescriptions();
HashSet <string> codeHash = new HashSet <string>(ICD9s.GetAllCodes());
string[] lines = File.ReadAllLines(tempFileName);
string[] arrayICD9;
ICD9 icd9 = new ICD9();
for (int i = 0; i < lines.Length; i++) //each loop should read exactly one line of code. and each line of code should be a unique code
{
if (quit)
{
return;
}
if (i % 100 == 0)
{
progress(i + 1, lines.Length);
}
arrayICD9 = lines[i].Split('\t');
if (codeHash.Contains(arrayICD9[0])) //code already exists
{
if (!IsOldDescriptions)
{
continue; //code exists and has updated description
}
string command = "UPDATE icd9 SET description='" + POut.String(arrayICD9[1]) + "' WHERE ICD9Code='" + POut.String(arrayICD9[0]) + "'";
Db.NonQ(command);
continue; //we have updated the description of an existing code.
}
icd9.ICD9Code = arrayICD9[0];
icd9.Description = arrayICD9[1];
ICD9s.Insert(icd9);
}
}
///<summary>Gets all SMS messages for the specified filters.</summary>
///<param name="dateStart">If dateStart is 01/01/0001, then no start date will be used.</param>
///<param name="dateEnd">If dateEnd is 01/01/0001, then no end date will be used.</param>
///<param name="listClinicNums">Will filter by clinic only if not empty and patNum is -1.</param>
///<param name="patNum">If patNum is not -1, then only the messages for the specified patient will be returned, otherwise messages for all
///patients will be returned.</param>
///<param name="phoneNumber">The phone number to search by. Should be just the digits, no formatting.</param>
public static List <SmsToMobile> GetMessages(DateTime dateStart, DateTime dateEnd, List <long> listClinicNums, long patNum = -1, string phoneNumber = "")
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <SmsToMobile> >(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listClinicNums, patNum, phoneNumber));
}
List <string> listCommandFilters = new List <string>();
if (dateStart > DateTime.MinValue)
{
listCommandFilters.Add(DbHelper.DtimeToDate("DateTimeSent") + ">=" + POut.Date(dateStart));
}
if (dateEnd > DateTime.MinValue)
{
listCommandFilters.Add(DbHelper.DtimeToDate("DateTimeSent") + "<=" + POut.Date(dateEnd));
}
if (patNum == -1)
{
//Only limit clinic if not searching for a particular PatNum.
if (listClinicNums.Count > 0)
{
listCommandFilters.Add("ClinicNum IN (" + String.Join(",", listClinicNums.Select(x => POut.Long(x))) + ")");
}
}
else
{
listCommandFilters.Add($"PatNum = {patNum}");
}
if (!string.IsNullOrEmpty(phoneNumber))
{
listCommandFilters.Add($"MobilePhoneNumber = '{POut.String(phoneNumber)}'");
}
string command = "SELECT * FROM smstomobile";
if (listCommandFilters.Count > 0)
{
command += " WHERE " + String.Join(" AND ", listCommandFilters);
}
return(Crud.SmsToMobileCrud.SelectMany(command));
}
///<summary>Returns true if a change was required, or false if no change needed.</summary>
public static bool UpdateDateT(PrefName prefName,DateTime newValue) {
//Very unusual. Involves cache, so Meth is used further down instead of here at the top.
DateTime curValue=PrefC.GetDateT(prefName);
if(curValue==newValue) {
return false;//no change needed
}
string command = "UPDATE preference SET "
+"ValueString = '"+POut.DateT(newValue,false)+"' "
+"WHERE PrefName = '"+POut.String(prefName.ToString())+"'";
bool retVal=true;
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
retVal=Meth.GetBool(MethodBase.GetCurrentMethod(),prefName,newValue);
}
else{
Db.NonQ(command);
}
Pref pref=new Pref();
pref.PrefName=prefName.ToString();
pref.ValueString=POut.DateT(newValue,false);
Prefs.UpdateValueForKey(pref);
return retVal;
}
///<summary>Tells us whether there is an existing enabled HL7Def, excluding the def with excludeHL7DefNum.
///If isMedLabHL7 is true, this will only check to see if a def of type HL7InternalType.MedLabv2_3 is enabled.
///Otherwise, only defs not of that type will be checked.</summary>
public static bool IsExistingHL7Enabled(long excludeHL7DefNum, bool isMedLabHL7)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), excludeHL7DefNum, isMedLabHL7));
}
string command = "SELECT COUNT(*) FROM hl7def WHERE IsEnabled=1 AND HL7DefNum != " + POut.Long(excludeHL7DefNum);
if (isMedLabHL7)
{
command += " AND InternalType='" + POut.String(HL7InternalType.MedLabv2_3.ToString()) + "'";
}
else
{
command += " AND InternalType!='" + POut.String(HL7InternalType.MedLabv2_3.ToString()) + "'";
}
if (Db.GetCount(command) == "0")
{
return(false);
}
return(true);
}
/// <summary>Shifts the column to the right, does nothing if trying to shift the rightmost column.</summary>
public static void ShiftColumnRight(string listName, string colName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), listName, colName);
return;
}
DataTable columnNames = Db.GetTable("DESCRIBE wikilist_" + POut.String(listName));
if (columnNames.Rows.Count < 3)
{
return; //not enough columns to reorder.
}
int index = columnNames.Select().ToList().FindIndex(x => x[0].ToString() == colName);
if (index > 0 && index < columnNames.Rows.Count - 1)
{
string command = $@"ALTER TABLE wikilist_{POut.String(listName)}
MODIFY {POut.String(colName)} TEXT NOT NULL AFTER {POut.String(columnNames.Rows[index+1][0].ToString())}" ;
Db.NonQ(command);
}
}
///<summary>Inserts encounters for a specified code for a specified date range if there is not already an encounter for that code, patient,
///provider, and procdate.</summary>
public static long InsertEncsFromProcDates(DateTime startDate, DateTime endDate, string codeValue, string codeSystem)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod(), startDate, endDate, codeValue, codeSystem));
}
string command = "INSERT INTO encounter (PatNum,ProvNum,CodeValue,CodeSystem,Note,DateEncounter) "
+ "(SELECT PatNum,ProvNum,'" + POut.String(codeValue) + "','" + POut.String(codeSystem) + "',"
+ "'Encounter auto-generated by Open Dental based on completed procedure information.',ProcDate "
+ "FROM procedurelog "
+ "WHERE ProcStatus=2 "
+ "AND ProcDate BETWEEN " + POut.Date(startDate) + " AND " + POut.Date(endDate) + " "
+ "AND NOT EXISTS("//Don't insert if there's already an encounter with this code for this pat,prov,date
+ "SELECT * FROM encounter "
+ "WHERE encounter.PatNum=procedurelog.PatNum "
+ "AND encounter.ProvNum=procedurelog.ProvNum "
+ "AND encounter.DateEncounter=procedurelog.ProcDate "
+ "AND encounter.CodeValue='" + POut.String(codeValue) + "' "
+ "AND encounter.CodeSystem='" + POut.String(codeSystem) + "') "
+ "GROUP BY PatNum,ProvNum,ProcDate)";
return(Db.NonQ(command));
}
///<summary>The only allowed parameters are "InnoDB" or "MyISAM". Converts tables to toEngine type and returns the number of tables converted.</summary>
public static int ConvertTables(string fromEngine, string toEngine)
{
int numtables = 0;
string command = "SELECT DATABASE()";
string database = Db.GetScalar(command);
command = @"SELECT table_name
FROM information_schema.tables
WHERE table_schema='" + database + "' AND information_schema.tables.engine='" + fromEngine + "'";
DataTable results = Db.GetTable(command);
command = "";
if (results.Rows.Count == 0)
{
return(numtables);
}
for (int i = 0; i < results.Rows.Count; i++)
{
command += "ALTER TABLE " + database + "." + POut.String(results.Rows[i]["table_name"].ToString()) + " ENGINE='" + toEngine + "'; ";
numtables++;
}
Db.NonQ(command);
return(numtables);
}
///<summary>This returns the number of plans affected.</summary>
public static void UpdatePlan(TrojanObject troj, long planNum, bool updateBenefits)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), troj, planNum, updateBenefits);
return;
}
long employerNum = Employers.GetEmployerNum(troj.ENAME);
string command;
//for(int i=0;i<planNums.Count;i++) {
command = "UPDATE insplan SET "
+ "EmployerNum=" + POut.Long(employerNum) + ", "
+ "GroupName='" + POut.String(troj.PLANDESC) + "', "
+ "GroupNum='" + POut.String(troj.POLICYNO) + "', "
+ "CarrierNum= " + POut.Long(troj.CarrierNum) + " "
+ "WHERE PlanNum=" + POut.Long(planNum);
Db.NonQ(command);
command = "UPDATE inssub SET "
+ "BenefitNotes='" + POut.String(troj.BenefitNotes) + "' "
+ "WHERE PlanNum=" + POut.Long(planNum);
Db.NonQ(command);
if (updateBenefits)
{
//clear benefits
command = "DELETE FROM benefit WHERE PlanNum=" + POut.Long(planNum);
Db.NonQ(command);
//benefitList
for (int j = 0; j < troj.BenefitList.Count; j++)
{
troj.BenefitList[j].PlanNum = planNum;
Benefits.Insert(troj.BenefitList[j]);
}
InsPlans.ComputeEstimatesForTrojanPlan(planNum);
}
}
///<summary></summary>
public static List <Loinc> GetBySearchString(string searchText)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Loinc> >(MethodBase.GetCurrentMethod(), searchText));
}
string command;
if (DataConnection.DBtype == DatabaseType.MySql)
{
command = "SELECT * FROM loinc WHERE LoincCode LIKE '%" + POut.String(searchText) + "%' OR NameLongCommon LIKE '%" + POut.String(searchText)
+ "%' ORDER BY RankCommonTests=0, RankCommonTests"; //common tests are at top of list.
}
else //oracle
{
command = "SELECT * FROM loinc WHERE LoincCode LIKE '%" + POut.String(searchText) + "%' OR NameLongCommon LIKE '%" + POut.String(searchText) + "%'";
}
return(Crud.LoincCrud.SelectMany(command));
}
///<summary>Sets the GraphicsSimple column to 1. Added to fix machines (lately tablets) that are having graphics problems and cannot start OpenDental.</summary>
public static void SetToSimpleGraphics(string computerName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), computerName);
return;
}
string command = "UPDATE computerpref SET GraphicsSimple=1 WHERE ComputerName='" + POut.String(computerName) + "'";
Db.NonQ(command);
}
///<summary></summary>
public static List <string> GetDeletedPages(string searchText, bool ignoreContent)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod(), searchText, ignoreContent));
}
List <string> retVal = new List <string>();
DataTable tableResults = new DataTable();
DataTable tableNewestDateTimes = new DataTable();
string[] searchTokens = searchText.Split(' ');
string command = "";
command = "SELECT PageTitle, MAX(DateTimeSaved) AS DateTimeSaved FROM wikipagehist GROUP BY PageTitle";
tableNewestDateTimes = Db.GetTable(command);
command =
"SELECT PageTitle,DateTimeSaved FROM wikipagehist "
// \_ represents a literal _ because _ has a special meaning in LIKE clauses.
//The second \ is just to escape the first \. The other option would be to pass the \ through POut.String.
+ "WHERE PageTitle NOT LIKE '\\_%' ";
for (int i = 0; i < searchTokens.Length; i++)
{
command += "AND PageTitle LIKE '%" + POut.String(searchTokens[i]) + "%' ";
}
command +=
"AND PageTitle NOT IN (SELECT PageTitle FROM wikipage WHERE IsDraft=0) " //ignore pages that were re-added after they were deleted
+ "AND IsDeleted=1 "
+ "ORDER BY PageTitle";
tableResults = Db.GetTable(command);
for (int i = 0; i < tableResults.Rows.Count; i++)
{
if (retVal.Contains(tableResults.Rows[i]["PageTitle"].ToString()))
{
//already found this page
continue;
}
for (int j = 0; j < tableNewestDateTimes.Rows.Count; j++)
{
if (tableNewestDateTimes.Rows[j]["PageTitle"].ToString() != tableResults.Rows[i]["PageTitle"].ToString())
{
//not the right page
continue;
}
if (tableNewestDateTimes.Rows[j]["DateTimeSaved"].ToString() != tableResults.Rows[i]["DateTimeSaved"].ToString())
{
//not the right DateTimeSaved
continue;
}
//This page is both deleted and there are no newer revisions of the page exist
retVal.Add(tableResults.Rows[i]["PageTitle"].ToString());
break;
}
}
//Match Content Second-----------------------------------------------------------------------------------
if (!ignoreContent)
{
command =
"SELECT PageTitle,DateTimeSaved FROM wikipagehist "
+ "WHERE PageTitle NOT LIKE '\\_%' ";
for (int i = 0; i < searchTokens.Length; i++)
{
command += "AND PageContent LIKE '%" + POut.String(searchTokens[i]) + "%' ";
}
command +=
"AND PageTitle NOT IN (SELECT PageTitle FROM wikipage WHERE IsDraft=0) " //ignore pages that exist again...
+ "AND IsDeleted=1 "
+ "ORDER BY PageTitle";
tableResults = Db.GetTable(command);
for (int i = 0; i < tableResults.Rows.Count; i++)
{
if (retVal.Contains(tableResults.Rows[i]["PageTitle"].ToString()))
{
//already found this page
continue;
}
for (int j = 0; j < tableNewestDateTimes.Rows.Count; j++)
{
if (tableNewestDateTimes.Rows[j]["PageTitle"].ToString() != tableResults.Rows[i]["PageTitle"].ToString())
{
//not the right page
continue;
}
if (tableNewestDateTimes.Rows[j]["DateTimeSaved"].ToString() != tableResults.Rows[i]["DateTimeSaved"].ToString())
{
//not the right DateTimeSaved
continue;
}
//This page is both deleted and there are no newer revisions of the page exist
retVal.Add(tableResults.Rows[i]["PageTitle"].ToString());
break;
}
}
}
return(retVal);
}
///<summary>Updates a pref string without using the cache classes. Useful for multithreaded connections.</summary>
public static void UpdateStringNoCache(PrefName prefName, string newValue)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), prefName, newValue);
return;
}
string command = "UPDATE preference SET ValueString='" + POut.String(newValue) + "' WHERE PrefName='" + POut.String(prefName.ToString()) + "'";
Db.NonQ(command);
}
/// <summary>When starting up, in an attempt to be fast, it will not add a new computer to the list.</summary>
public static void UpdateHeartBeat(string computerName, bool isStartup)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), computerName, isStartup);
return;
}
if (!isStartup && list == null)
{
RefreshCache(); //adds new computer to list
}
string command = "UPDATE computer SET LastHeartBeat=" + DbHelper.Now() + " WHERE CompName = '" + POut.String(computerName) + "'";
Db.NonQ(command);
}
public static List <Icd10> GetBySearchText(string searchText)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Icd10> >(MethodBase.GetCurrentMethod(), searchText));
}
string[] searchTokens = searchText.Split(' ');
string command = @"SELECT * FROM icd10 ";
for (int i = 0; i < searchTokens.Length; i++)
{
command += (i == 0?"WHERE ":"AND ") + "(Icd10Code LIKE '%" + POut.String(searchTokens[i]) + "%' OR Description LIKE '%" + POut.String(searchTokens[i]) + "%') ";
}
return(Crud.Icd10Crud.SelectMany(command));
}
///<summary>No error checking. Only called from WikiLists after the corresponding column has been dropped from its respective table.</summary>
public static void Delete(string listName, string colName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), listName, colName);
return;
}
string command = "DELETE FROM wikilistheaderwidth WHERE ListName='" + POut.String(listName) + "' AND ColName='" + POut.String(colName) + "'";
Db.NonQ(command);
RefreshCache();
}
///<summary>May not return correct values if notes are stored with newline characters.</summary>
public static List <long> GetNumsByNote(string oldNote)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), oldNote));
}
oldNote = oldNote.Replace("\r", "");
//oldNote=oldNote.Replace("\r","").Replace("\n","\r\n");
//oldNote=oldNote.Replace("\r","").Replace("\n","*?");
string command = "SELECT TreatPlanNum FROM treatplan WHERE REPLACE(Note,'\\r','')='" + POut.String(oldNote) + "' " +
"AND TPStatus IN (" + POut.Int((int)TreatPlanStatus.Active) + "," + POut.Int((int)TreatPlanStatus.Inactive) + ")";
//string command="SELECT TreatPlanNum FROM treatplan WHERE Note='"+POut.String(oldNote)+"' "+
// "AND TPStatus IN ("+POut.Int((int)TreatPlanStatus.Active)+","+POut.Int((int)TreatPlanStatus.Inactive)+")";
return(Db.GetListLong(command));
}
///<summary>Must pass in version as "Maj" or "Maj.Min" or "Maj.Min.Rev". Uses like operator. Can return null if the thread fails to join.</summary>
public static List <Bug> GetByVersion(string versionMajMin, string filter = "")
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Bug> >(MethodBase.GetCurrentMethod(), versionMajMin, filter));
}
//Create an ODThread so that we can safely change the database connection settings without affecting the calling method's connection.
ODThread odThread = new ODThread(new ODThread.WorkerDelegate((ODThread o) => {
//Always set the thread static database connection variables to set the serviceshq db conn.
#if DEBUG
new DataConnection().SetDbT("localhost", "bugs", "root", "", "", "", DatabaseType.MySql, true);
#else
new DataConnection().SetDbT("server", "bugs", "root", "", "", "", DatabaseType.MySql, true);
#endif
string command = "SELECT * FROM bug WHERE (VersionsFound LIKE '" + POut.String(versionMajMin) + "%' OR VersionsFound LIKE '%;" + POut.String(versionMajMin) + "%' OR " +
"VersionsFixed LIKE '" + POut.String(versionMajMin) + "%' OR VersionsFixed LIKE '%;" + POut.String(versionMajMin) + "%') ";
if (filter != "")
{
command += "AND Description LIKE '%" + POut.String(filter) + "%'";
}
o.Tag = Crud.BugCrud.SelectMany(command);
}));
odThread.AddExceptionHandler(new ODThread.ExceptionDelegate((Exception e) => { })); //Do nothing
odThread.Name = "bugsGetByVersionThread";
odThread.Start(true);
if (!odThread.Join(THREAD_TIMEOUT))
{
return(null);
}
return((List <Bug>)odThread.Tag);
}
public static void ClearHeartBeat(string computerName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), computerName);
return;
}
string command = "UPDATE computer SET LastHeartBeat=" + POut.Date(new DateTime(0001, 1, 1), true) + " WHERE CompName = '" + POut.String(computerName) + "'";
Db.NonQ(command);
}
public static DataTable GetListOrderBy(List <EhrPatListElement> elementList, bool isAsc)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), elementList, isAsc));
}
DataTable table = new DataTable();
string command = "DROP TABLE IF EXISTS tempehrlist";
Db.NonQ(command);
command = "CREATE TABLE tempehrlist SELECT patient.PatNum,patient.LName,patient.FName";
for (int i = 0; i < elementList.Count; i++)
{
string compStr = elementList[i].CompareString;
switch (elementList[i].Restriction)
{
case EhrRestrictionType.Birthdate:
command += ",patient.Birthdate ";
break;
case EhrRestrictionType.Problem:
command += ",(SELECT disease.ICD9Num FROM disease WHERE disease.PatNum=patient.PatNum AND disease.ICD9Num IN (SELECT ICD9Num FROM icd9 WHERE ICD9Code LIKE '" + compStr + "%')) `" + compStr + "` ";
break;
case EhrRestrictionType.LabResult:
command += ",(SELECT IFNULL(MAX(ObsValue),0) FROM labresult,labpanel WHERE labresult.LabPanelNum=labpanel.LabPanelNum AND labpanel.PatNum=patient.PatNum AND labresult.TestName='" + compStr + "') `" + compStr + "` ";
break;
case EhrRestrictionType.Medication:
command += ",(SELECT COUNT(*) FROM medication,medicationpat WHERE medicationpat.PatNum=patient.PatNum AND medication.MedicationNum=medicationpat.MedicationNum AND medication.MedName LIKE '%" + compStr + "%') `" + compStr + "` ";
break;
case EhrRestrictionType.Gender:
command += ",patient.Gender ";
break;
}
}
command += "FROM patient";
Db.NonQ(command);
string order = "";
command = "SELECT * FROM tempehrlist ";
for (int i = 0; i < elementList.Count; i++)
{
if (i < 1)
{
command += "WHERE " + GetFilteringText(elementList[i]);
}
else
{
command += "AND " + GetFilteringText(elementList[i]);
}
if (elementList[i].OrderBy)
{
if (elementList[i].Restriction == EhrRestrictionType.Birthdate)
{
order = "ORDER BY Birthdate" + GetOrderBy(isAsc);
}
else if (elementList[i].Restriction == EhrRestrictionType.Gender)
{
order = "ORDER BY Gender" + GetOrderBy(isAsc);
}
else
{
order = "ORDER BY `" + POut.String(elementList[i].CompareString) + "`" + GetOrderBy(isAsc);
}
}
}
command += order;
table = Db.GetTable(command);
command = "DROP TABLE IF EXISTS tempehrlist";
Db.NonQ(command);
return(table);
}
///<summary>Gets a pref of type bool without using the cache.</summary>
public static bool GetBoolNoCache(PrefName prefName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), prefName));
}
string command = "SELECT ValueString FROM preference WHERE PrefName = '" + POut.String(prefName.ToString()) + "'";
return(PIn.Bool(Db.GetScalar(command)));
}
///<summary></summary>
public static void DeleteObject(long sheetDefNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), sheetDefNum);
return;
}
//validate that not already in use by a refferral.
string command = "SELECT LName,FName FROM referral WHERE Slip=" + POut.Long(sheetDefNum);
DataTable table = Db.GetTable(command);
//int count=PIn.PInt(Db.GetCount(command));
string referralNames = "";
for (int i = 0; i < table.Rows.Count; i++)
{
if (i > 0)
{
referralNames += ", ";
}
referralNames += table.Rows[i]["FName"].ToString() + " " + table.Rows[i]["LName"].ToString();
}
if (table.Rows.Count > 0)
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is already in use by referrals. Not allowed to delete.") + " " + referralNames);
}
//validate that not already in use by automation.
command = "SELECT AutomationNum FROM automation WHERE SheetDefNum=" + POut.Long(sheetDefNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is in use by automation. Not allowed to delete."));
}
//validate that not already in use by a laboratory
command = "SELECT Description FROM laboratory WHERE Slip=" + POut.Long(sheetDefNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is in use by laboratories. Not allowed to delete.")
+ "\r\n" + string.Join(", ", table.Select().Select(x => x["Description"].ToString())));
}
//validate that not already in use by clinicPref.
command = "SELECT ClinicNum FROM clinicpref WHERE ValueString='" + POut.Long(sheetDefNum) + "' AND PrefName='" + POut.String(PrefName.SheetsDefaultRx.ToString()) + "'";
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is in use by clinics. Not allowed to delete.")
+ "\r\n" + string.Join(", ", table.Select().Select(x => Clinics.GetAbbr(PIn.Long(x["ClinicNum"].ToString())))));
}
//validate that not already in use by eClipboard
command = "SELECT EClipboardSheetDefNum,ClinicNum FROM eclipboardsheetdef WHERE SheetDefNum=" + POut.Long(sheetDefNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
if (PrefC.HasClinicsEnabled)
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is in use by eClipboard. Not allowed to delete.")
+ "\r\n" + string.Join(", ", table.Select()
.Select(x => Clinics.GetAbbr(PIn.Long(x["ClinicNum"].ToString())))
.Select(x => string.IsNullOrEmpty(x) ? "Default" : x)));
}
else
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is in use by eClipboard. Not allowed to delete."));
}
}
command = "DELETE FROM sheetfielddef WHERE SheetDefNum=" + POut.Long(sheetDefNum);
Db.NonQ(command);
Crud.SheetDefCrud.Delete(sheetDefNum);
}
///<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 = -1)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), clockStatus, extens, employeeNum);
return;
}
string command = @"SELECT phoneempdefault.EmployeeNum,phoneempdefault.IsTriageOperator,Description,phoneempdefault.EmpName,HasColor,phone.ClockStatus "
+ "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)
{
//It would be nice if we could create a phone row for this extension.
return;
}
long empNum = PIn.Long(tablePhone.Rows[0]["EmployeeNum"].ToString());
bool isTriageOperator = PIn.Bool(tablePhone.Rows[0]["IsTriageOperator"].ToString());
string empName = PIn.String(tablePhone.Rows[0]["EmpName"].ToString());
string clockStatusDb = PIn.String(tablePhone.Rows[0]["ClockStatus"].ToString());
Employee emp = Employees.GetEmp(employeeNum);
if (emp != null) //A new employee is going to take over this extension.
{
empName = emp.FName;
empNum = emp.EmployeeNum;
}
else if (employeeNum == 0) //Clear the employee from that row.
{
empName = "";
empNum = 0;
}
//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 hasColor = PIn.Bool(tablePhone.Rows[0]["HasColor"].ToString());
#region DateTimeStart
//When a user shows up as a color on the phone panel, we want a timer to be constantly going to show how long they've been off the phone.
string dateTimeStart = "";
//It's possible that a new user has never clocked in before, therefore their clockStatus will be empty. Simply set it to the status that they are trying to go to.
if (clockStatusDb == "")
{
clockStatusDb = clockStatus.ToString();
}
if (clockStatus == ClockStatusEnum.Break ||
clockStatus == ClockStatusEnum.Lunch)
{
//The user is going on Lunch or Break. Start the DateTimeStart counter so we know how long they have been gone.
dateTimeStart = "DateTimeStart=NOW(), ";
}
else if (clockStatus == ClockStatusEnum.Home)
{
//User is going Home. Always clear the DateTimeStart column no matter what.
dateTimeStart = "DateTimeStart='0001-01-01', ";
}
else //User shows as a color on big phones and is not going to a status of Home, Lunch, or Break. Example: Available, Training etc.
//Get the current clock status from the database.
{
ClockStatusEnum clockStatusCur = (ClockStatusEnum)Enum.Parse(typeof(ClockStatusEnum), clockStatusDb);
//Start the clock if the user is going from a break status to any other non-break status.
if (clockStatusCur == ClockStatusEnum.Home ||
clockStatusCur == ClockStatusEnum.Lunch ||
clockStatusCur == ClockStatusEnum.Break)
{
//The user is clocking in from home, lunch, or break. Start the timer up.
if (hasColor) //Only start up the timer when someone with color clocks in.
{
dateTimeStart = "DateTimeStart=NOW(), ";
}
else //Someone with no color then reset the timer. They are back from break, that's all we need to know.
{
dateTimeStart = "DateTimeStart='0001-01-01', ";
}
}
}
string dateTimeNeedsHelpStart;
if (clockStatus == ClockStatusEnum.NeedsHelp)
{
dateTimeNeedsHelpStart = "DateTimeNeedsHelpStart=NOW(), ";
}
else
{
dateTimeNeedsHelpStart = "DateTimeNeedsHelpStart=" + POut.DateT(DateTime.MinValue) + ", ";
}
#endregion
//Update the phone row to reflect the new clock status of the user.
string clockStatusNew = clockStatus.ToString();
if (clockStatus == ClockStatusEnum.None)
{
clockStatusNew = "";
}
if (clockStatus == ClockStatusEnum.HelpOnTheWay && clockStatusDb == ClockStatusEnum.HelpOnTheWay.ToString()) //If HelpOnTheWay already
{
clockStatusNew = ClockStatusEnum.Available.ToString();
}
command = "UPDATE phone SET ClockStatus='" + POut.String(clockStatusNew) + "', "
+ dateTimeStart
+ dateTimeNeedsHelpStart
//+"ColorBar=-1, " //ColorBar is now determined at runtime by OD using Phones.GetPhoneColor.
+ "EmployeeNum=" + POut.Long(empNum) + ", "
+ "EmployeeName='" + POut.String(empName) + "' "
+ "WHERE Extension=" + extens;
Db.NonQ(command);
//Zero out any duplicate phone table rows for this employee.
//This is possible if a user logged off and another employee logs into their computer. This would cause duplicate entries in the big phones window.
UpdatePhoneToEmpty(employeeNum, extens);
}
///<summary>Also alters the db table for the list itself. Throws exception if number of columns does not match.</summary>
public static void UpdateNamesAndWidths(string listName, List <WikiListHeaderWidth> columnDefs)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), columnDefs);
return;
}
string command = "DESCRIBE wikilist_" + POut.String(listName);
DataTable tableListDescription = Db.GetTable(command);
if (tableListDescription.Rows.Count != columnDefs.Count)
{
throw new ApplicationException("List schema has been altered. Unable to save changes to list.");
}
//rename Columns with dummy names in case user is renaming a new column with an old name.---------------------------------------------
for (int i = 0; i < tableListDescription.Rows.Count; i++)
{
if (tableListDescription.Rows[i][0].ToString().ToLower() == POut.String(listName) + "num")
{
//skip primary key
continue;
}
command = "ALTER TABLE wikilist_" + POut.String(listName) + " CHANGE " + POut.String(tableListDescription.Rows[i][0].ToString()) + " " + POut.String(dummyColName + i) + " TEXT NOT NULL";
Db.NonQ(command);
command =
"UPDATE wikiListHeaderWidth SET ColName='" + POut.String(dummyColName + i) + "' "
+ "WHERE ListName='" + POut.String(listName) + "' "
+ "AND ColName='" + POut.String(tableListDescription.Rows[i][0].ToString()) + "'";
Db.NonQ(command);
}
//rename columns names and widths-------------------------------------------------------------------------------------------------------
for (int i = 0; i < tableListDescription.Rows.Count; i++)
{
if (tableListDescription.Rows[i][0].ToString().ToLower() == listName + "num")
{
//skip primary key
continue;
}
command = "ALTER TABLE wikilist_" + POut.String(listName) + " CHANGE " + POut.String(dummyColName + i) + " " + POut.String(columnDefs[i].ColName) + " TEXT NOT NULL";
Db.NonQ(command);
command = "UPDATE wikiListHeaderWidth "
+ "SET ColName='" + POut.String(columnDefs[i].ColName) + "', ColWidth='" + POut.Int(columnDefs[i].ColWidth) + "' "
+ "WHERE ListName='" + POut.String(listName) + "' "
+ "AND ColName='" + POut.String(dummyColName + i) + "'";
Db.NonQ(command);
}
//handle width of PK seperately because we do not rename the PK column, ever.
command = "UPDATE wikiListHeaderWidth SET ColWidth='" + POut.Int(columnDefs[0].ColWidth) + "' "
+ "WHERE ListName='" + POut.String(listName) + "' AND ColName='" + POut.String(columnDefs[0].ColName) + "'";
Db.NonQ(command);
RefreshCache();
}
public static void SetRsvpStatus(AsapRSVPStatus rsvpStatus, List <string> listShortGuids)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), rsvpStatus, listShortGuids);
return;
}
if (listShortGuids.Count == 0)
{
return;
}
string command = "UPDATE asapcomm SET ResponseStatus=" + POut.Int((int)rsvpStatus)
+ " WHERE ShortGUID IN('" + string.Join("','", listShortGuids.Select(x => POut.String(x))) + "')";
Db.NonQ(command);
}
///<summary></summary>
public static DataTable GetWriteoffTable(DateTime dateStart, DateTime dateEnd, bool isIndividual, string carrierText, bool isWriteoffPay)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, isIndividual, carrierText, isWriteoffPay));
}
string queryText = "";
//individual
if (isIndividual)
{
queryText = "SET @DateFrom=" + POut.Date(dateStart) + ", @DateTo=" + POut.Date(dateEnd)
+ ", @CarrierName='%" + POut.String(carrierText) + "%';";
if (isWriteoffPay)
{
queryText += @"SELECT claimproc.DateCP,
CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),
carrier.CarrierName,
provider.Abbr,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc,insplan,patient,carrier,provider
WHERE provider.ProvNum = claimproc.ProvNum
AND claimproc.PlanNum = insplan.PlanNum
AND claimproc.PatNum = patient.PatNum
AND carrier.CarrierNum = insplan.CarrierNum
AND (claimproc.Status=1 OR claimproc.Status=4) /*received or supplemental*/
AND claimproc.DateCP >= @DateFrom
AND claimproc.DateCP <= @DateTo
AND insplan.PlanType='p'
AND carrier.CarrierName LIKE @CarrierName
GROUP BY claimproc.ClaimNum
ORDER BY claimproc.DateCP" ;
}
else //use procedure date
{
queryText += @"SELECT claimproc.ProcDate,
CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),
carrier.CarrierName,
provider.Abbr,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc,insplan,patient,carrier,provider
WHERE provider.ProvNum = claimproc.ProvNum
AND claimproc.PlanNum = insplan.PlanNum
AND claimproc.PatNum = patient.PatNum
AND carrier.CarrierNum = insplan.CarrierNum
AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) /*received or supplemental or notreceived*/
AND claimproc.ProcDate >= @DateFrom
AND claimproc.ProcDate <= @DateTo
AND insplan.PlanType='p'
AND carrier.CarrierName LIKE @CarrierName
GROUP BY claimproc.ClaimNum
ORDER BY claimproc.ProcDate" ;
}
}
else
{
//group
if (isWriteoffPay)
{
queryText = "SET @DateFrom=" + POut.Date(dateStart) + ", @DateTo=" + POut.Date(dateEnd)
+ ", @CarrierName='%" + POut.String(carrierText) + "%';"
+ @"SELECT carrier.CarrierName,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc,insplan,carrier
WHERE claimproc.PlanNum = insplan.PlanNum
AND carrier.CarrierNum = insplan.CarrierNum
AND (claimproc.Status=1 OR claimproc.Status=4) /*received or supplemental*/
AND claimproc.DateCP >= @DateFrom
AND claimproc.DateCP <= @DateTo
AND insplan.PlanType='p'
AND carrier.CarrierName LIKE @CarrierName
GROUP BY carrier.CarrierNum
ORDER BY carrier.CarrierName" ;
}
else
{
queryText = "SET @DateFrom=" + POut.Date(dateStart) + ", @DateTo=" + POut.Date(dateEnd)
+ ", @CarrierName='%" + POut.String(carrierText) + "%';"
+ @"SELECT carrier.CarrierName,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc,insplan,carrier
WHERE claimproc.PlanNum = insplan.PlanNum
AND carrier.CarrierNum = insplan.CarrierNum
AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) /*received or supplemental or notreceived*/
AND claimproc.ProcDate >= @DateFrom
AND claimproc.ProcDate <= @DateTo
AND insplan.PlanType='p'
AND carrier.CarrierName LIKE @CarrierName
GROUP BY carrier.CarrierNum
ORDER BY carrier.CarrierName" ;
}
}
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(queryText)));
}
///<summary>Used in SheetFiller to fill patient letter with exam sheet information. Will return null if no exam sheet matching the description exists for the patient. Usually just returns one field, but will return a list of fields if it's for a RadioButtonGroup.</summary>
public static List <SheetField> GetFieldFromExamSheet(long patNum, string examDescript, string fieldName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <SheetField> >(MethodBase.GetCurrentMethod(), patNum, examDescript, fieldName));
}
Sheet sheet = Sheets.GetMostRecentExamSheet(patNum, examDescript);
if (sheet == null)
{
return(null);
}
string command = "SELECT * FROM sheetfield WHERE SheetNum="
+ POut.Long(sheet.SheetNum) + " "
+ "AND (RadioButtonGroup='" + POut.String(fieldName) + "' OR ReportableName='" + POut.String(fieldName) + "' OR FieldName='" + POut.String(fieldName) + "')";
return(Crud.SheetFieldCrud.SelectMany(command));
}
