public static DataTable GetSmsUsageLocal(List <long> listClinicNums, DateTime dateMonth)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, dateMonth));
}
#region Initialize retVal DataTable
List <SmsPhone> listSmsPhones = GetForClinics(listClinicNums);
DateTime dateStart = dateMonth.Date.AddDays(1 - dateMonth.Day); //remove time portion and day of month portion. Remainder should be midnight of the first of the month
DateTime dateEnd = dateStart.AddMonths(1); //This should be midnight of the first of the following month.
//This query builds the data table that will be filled from several other queries, instead of writing one large complex query.
//It is written this way so that the queries are simple to write and understand, and makes Oracle compatibility easier to maintain.
string command = @"SELECT
0 ClinicNum,
' ' PhoneNumber,
' ' CountryCode,
0 SentMonth,
0.0 SentCharge,
0 ReceivedMonth,
0.0 ReceivedCharge
FROM
DUAL" ; //this is a simple way to get a data table with the correct layout without having to query any real data.
DataTable retVal = Db.GetTable(command).Clone(); //use .Clone() to get schema only, with no rows.
retVal.TableName = "SmsUsageLocal";
for (int i = 0; i < listClinicNums.Count; i++)
{
DataRow row = retVal.NewRow();
row["ClinicNum"] = listClinicNums[i];
row["PhoneNumber"] = "No Active Phones";
SmsPhone firstActivePhone = listSmsPhones
.Where(x => x.ClinicNum == listClinicNums[i]) //phones for this clinic
.Where(x => x.DateTimeInactive.Year < 1880) //that are active
.FirstOrDefault(x => x.DateTimeActive == listSmsPhones //and have the smallest active date (the oldest/first phones activated)
.Where(y => y.ClinicNum == x.ClinicNum)
.Where(y => y.DateTimeInactive.Year < 1880)
.Min(y => y.DateTimeActive));
if (firstActivePhone != null)
{
row["PhoneNumber"] = firstActivePhone.PhoneNumber;
row["CountryCode"] = firstActivePhone.CountryCode;
}
row["SentMonth"] = 0;
row["SentCharge"] = 0.0;
row["ReceivedMonth"] = 0;
row["ReceivedCharge"] = 0.0;
retVal.Rows.Add(row);
}
#endregion
#region Fill retVal DataTable
//Sent Last Month
command = "SELECT ClinicNum, COUNT(*), ROUND(SUM(MsgChargeUSD),2) FROM smstomobile "
+ "WHERE DateTimeSent >=" + POut.Date(dateStart) + " "
+ "AND DateTimeSent<" + POut.Date(dateEnd) + " "
+ "AND MsgChargeUSD>0 GROUP BY ClinicNum";
DataTable table = Db.GetTable(command);
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < retVal.Rows.Count; j++)
{
if (retVal.Rows[j]["ClinicNum"].ToString() != table.Rows[i]["ClinicNum"].ToString())
{
continue;
}
retVal.Rows[j]["SentMonth"] = table.Rows[i][1]; //.ToString();
retVal.Rows[j]["SentCharge"] = table.Rows[i][2]; //.ToString();
break;
}
}
//Received Month
command = "SELECT ClinicNum, COUNT(*) FROM smsfrommobile "
+ "WHERE DateTimeReceived >=" + POut.Date(dateStart) + " "
+ "AND DateTimeReceived<" + POut.Date(dateEnd) + " "
+ "GROUP BY ClinicNum";
table = Db.GetTable(command);
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < retVal.Rows.Count; j++)
{
if (retVal.Rows[j]["ClinicNum"].ToString() != table.Rows[i]["ClinicNum"].ToString())
{
continue;
}
retVal.Rows[j]["ReceivedMonth"] = table.Rows[i][1].ToString();
retVal.Rows[j]["ReceivedCharge"] = "0";
break;
}
}
#endregion
return(retVal);
}
///<summary>Returns current clinic limit minus message usage for current calendar month.</summary>
public static double GetClinicBalance(long clinicNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDouble(MethodBase.GetCurrentMethod(), clinicNum));
}
double limit = 0;
if (PrefC.GetBool(PrefName.EasyNoClinics))
{
if (PrefC.GetDate(PrefName.SmsContractDate).Year > 1880)
{
limit = PrefC.GetDouble(PrefName.SmsMonthlyLimit);
}
}
else
{
if (clinicNum == 0 && Clinics.GetCount(true) > 0) //Sending text for "Unassigned" patient. Use the first non-hidden clinic. (for now)
{
clinicNum = Clinics.GetFirst(true).ClinicNum;
}
Clinic clinicCur = Clinics.GetClinic(clinicNum);
if (clinicCur != null && clinicCur.SmsContractDate.Year > 1880)
{
limit = clinicCur.SmsMonthlyLimit;
}
}
DateTime dtStart = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
DateTime dtEnd = dtStart.AddMonths(1);
string command = "SELECT SUM(MsgChargeUSD) FROM smstomobile WHERE ClinicNum=" + POut.Long(clinicNum) + " "
+ "AND DateTimeSent>=" + POut.Date(dtStart) + " AND DateTimeSent<" + POut.Date(dtEnd);
limit -= PIn.Double(Db.GetScalar(command));
return(limit);
}
///<summary>Changes the value of the DateTStamp column to the current time stamp for all diseases of a patient that are the status specified.</summary>
public static void ResetTimeStamps(long patNum, ProblemStatus status)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), patNum, status);
return;
}
string command = "UPDATE disease SET DateTStamp = CURRENT_TIMESTAMP WHERE PatNum =" + POut.Long(patNum);
command += " AND ProbStatus = " + POut.Int((int)status);
Db.NonQ(command);
}
///<summary>Gets a list of all Diseases for a given patient. Show innactive returns all, otherwise only resolved and active problems.</summary>
public static List <Disease> Refresh(bool showInnactive, long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Disease> >(MethodBase.GetCurrentMethod(), showInnactive, patNum));
}
string command = "SELECT disease.* FROM disease "
+ "WHERE PatNum=" + POut.Long(patNum);
if (!showInnactive)
{
command += " AND (ProbStatus=" + POut.Int((int)ProblemStatus.Active) + " OR ProbStatus=" + POut.Int((int)ProblemStatus.Resolved) + ")";
}
return(Crud.DiseaseCrud.SelectMany(command));
}
///<summary>Check to see if column can be deleted, returns true is the column contains only nulls.</summary>
public static bool CheckColumnEmpty(string listName, string colName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), listName, colName));
}
string command = "SELECT COUNT(*) FROM wikilist_" + POut.String(listName) + " WHERE " + POut.String(colName) + "!=''";
return(Db.GetCount(command).Equals("0"));
}
public static List <long> GetChangedSinceDiseaseNums(DateTime changedSince, List <long> eligibleForUploadPatNumList)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), changedSince, eligibleForUploadPatNumList));
}
string strEligibleForUploadPatNums = "";
DataTable table;
if (eligibleForUploadPatNumList.Count > 0)
{
for (int i = 0; i < eligibleForUploadPatNumList.Count; i++)
{
if (i > 0)
{
strEligibleForUploadPatNums += "OR ";
}
strEligibleForUploadPatNums += "PatNum='" + eligibleForUploadPatNumList[i].ToString() + "' ";
}
string command = "SELECT DiseaseNum FROM disease WHERE DateTStamp > " + POut.DateT(changedSince) + " AND (" + strEligibleForUploadPatNums + ")";
table = Db.GetTable(command);
}
else
{
table = new DataTable();
}
List <long> diseasenums = new List <long>(table.Rows.Count);
for (int i = 0; i < table.Rows.Count; i++)
{
diseasenums.Add(PIn.Long(table.Rows[i]["DiseaseNum"].ToString()));
}
return(diseasenums);
}
///<summary>Automatically generate and insert encounter as long as there is no other encounter with that date and provider for that patient. Does not insert an encounter if one of the CQM default encounter prefs are invalid.</summary>
public static void InsertDefaultEncounter(long patNum, long provNum, DateTime date)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), patNum, provNum, date);
return;
}
//Validate prefs. If they are not set, we have nothing to insert so no reason to check.
if (PrefC.GetString(PrefName.CQMDefaultEncounterCodeSystem) == "" || PrefC.GetString(PrefName.CQMDefaultEncounterCodeValue) == "none")
{
return;
}
//If no encounter for date for this patient
string command = "SELECT COUNT(*) NumEncounters FROM encounter WHERE encounter.PatNum=" + POut.Long(patNum) + " "
+ "AND encounter.DateEncounter=" + POut.Date(date) + " "
+ "AND encounter.ProvNum=" + POut.Long(provNum);
int count = PIn.Int(Db.GetCount(command));
if (count > 0) //Encounter already exists for date
{
return;
}
//Insert encounter with default encounter code system and code value set in Setup>EHR>Settings
Encounter encounter = new Encounter();
encounter.PatNum = patNum;
encounter.ProvNum = provNum;
encounter.DateEncounter = date;
encounter.CodeSystem = PrefC.GetString(PrefName.CQMDefaultEncounterCodeSystem);
encounter.CodeValue = PrefC.GetString(PrefName.CQMDefaultEncounterCodeValue);
Insert(encounter);
}
///<summary>For orderBy, use 0 for BillingType and 1 for PatientName.</summary>
public static DataTable GetBilling(bool isSent, int orderBy, DateTime dateFrom, DateTime dateTo, long clinicNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), isSent, orderBy, dateFrom, dateTo, clinicNum));
}
DataTable table = new DataTable();
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("amountDue");
table.Columns.Add("balTotal");
table.Columns.Add("billingType");
table.Columns.Add("insEst");
table.Columns.Add("IsSent");
table.Columns.Add("lastStatement");
table.Columns.Add("mode");
table.Columns.Add("name");
table.Columns.Add("PatNum");
table.Columns.Add("payPlanDue");
table.Columns.Add("StatementNum");
List <DataRow> rows = new List <DataRow>();
string command = "SELECT BalTotal,BillingType,FName,InsEst,statement.IsSent,"
+ "IFNULL(MAX(s2.DateSent)," + POut.Date(DateTime.MinValue) + ") LastStatement,"
+ "LName,MiddleI,statement.Mode_,PayPlanDue,Preferred,"
+ "statement.PatNum,statement.StatementNum "
+ "FROM statement "
+ "LEFT JOIN patient ON statement.PatNum=patient.PatNum "
+ "LEFT JOIN statement s2 ON s2.PatNum=patient.PatNum "
+ "AND s2.IsSent=1 ";
if (PrefC.GetBool(PrefName.BillingIgnoreInPerson))
{
command += "AND s2.Mode_ !=1 ";
}
if (orderBy == 0) //BillingType
{
command += "LEFT JOIN definition ON patient.BillingType=definition.DefNum ";
}
command += "WHERE statement.IsSent=" + POut.Bool(isSent) + " ";
//if(dateFrom.Year>1800){
command += "AND statement.DateSent>=" + POut.Date(dateFrom) + " "; //greater than midnight this morning
//}
//if(dateFrom.Year>1800){
command += "AND statement.DateSent<" + POut.Date(dateTo.AddDays(1)) + " "; //less than midnight tonight
//}
if (clinicNum > 0)
{
command += "AND patient.ClinicNum=" + clinicNum + " ";
}
command += "GROUP BY BalTotal,BillingType,FName,InsEst,statement.IsSent,"
+ "LName,MiddleI,statement.Mode_,PayPlanDue,Preferred,"
+ "statement.PatNum,statement.StatementNum ";
if (orderBy == 0) //BillingType
{
command += "ORDER BY definition.ItemOrder,LName,FName,MiddleI,PayPlanDue";
}
else
{
command += "ORDER BY LName,FName";
}
DataTable rawTable = Db.GetTable(command);
Patient pat;
StatementMode mode;
double balTotal;
double insEst;
double payPlanDue;
DateTime lastStatement;
for (int i = 0; i < rawTable.Rows.Count; i++)
{
row = table.NewRow();
balTotal = PIn.Double(rawTable.Rows[i]["BalTotal"].ToString());
insEst = PIn.Double(rawTable.Rows[i]["InsEst"].ToString());
payPlanDue = PIn.Double(rawTable.Rows[i]["PayPlanDue"].ToString());
row["amountDue"] = (balTotal - insEst).ToString("F");
row["balTotal"] = balTotal.ToString("F");;
row["billingType"] = DefC.GetName(DefCat.BillingTypes, PIn.Long(rawTable.Rows[i]["BillingType"].ToString()));
if (insEst == 0)
{
row["insEst"] = "";
}
else
{
row["insEst"] = insEst.ToString("F");
}
row["IsSent"] = rawTable.Rows[i]["IsSent"].ToString();
lastStatement = PIn.Date(rawTable.Rows[i]["LastStatement"].ToString());
if (lastStatement.Year < 1880)
{
row["lastStatement"] = "";
}
else
{
row["lastStatement"] = lastStatement.ToShortDateString();
}
mode = (StatementMode)PIn.Long(rawTable.Rows[i]["Mode_"].ToString());
row["mode"] = Lans.g("enumStatementMode", mode.ToString());
pat = new Patient();
pat.LName = rawTable.Rows[i]["LName"].ToString();
pat.FName = rawTable.Rows[i]["FName"].ToString();
pat.Preferred = rawTable.Rows[i]["Preferred"].ToString();
pat.MiddleI = rawTable.Rows[i]["MiddleI"].ToString();
row["name"] = pat.GetNameLF();
row["PatNum"] = rawTable.Rows[i]["PatNum"].ToString();
if (payPlanDue == 0)
{
row["payPlanDue"] = "";
}
else
{
row["payPlanDue"] = payPlanDue.ToString("F");
}
row["StatementNum"] = rawTable.Rows[i]["StatementNum"].ToString();
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
return(table);
}
public static void DeleteItem(string listName, long itemNum, string colName = null)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), listName, itemNum, colName);
return;
}
colName = POut.String(string.IsNullOrEmpty(colName)?(listName + "Num"):colName);
string command = $@"DELETE FROM wikilist_{POut.String(listName)} WHERE {colName}={POut.Long(itemNum)}";
Db.NonQ(command);
}
///<summary><para>Surround with try catch. Safely renames list by creating new list, selecting existing list into new list, then deleting existing list.</para>
///<para>This code could be used to either copy or backup lists in the future. (With minor modifications).</para></summary>
public static void Rename(string nameOriginal, string nameNew)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), nameOriginal, nameNew);
return;
}
//Name should already have been validated and available.
string command = "CREATE TABLE wikilist_" + POut.String(nameNew) + " AS SELECT * FROM wikilist_" + POut.String(nameOriginal);
Db.NonQ(command);
//Validate content before altering and deleting things
DataTable tableNew = GetByName(nameNew);
DataTable tableOld = GetByName(nameOriginal);
if (tableNew.Rows.Count != tableOld.Rows.Count)
{
command = "DROP TABLE wikilist_" + POut.String(nameNew);
Db.NonQ(command);
throw new Exception("Error occurred renaming list. Mismatch found in row count. No changes made.");
}
if (tableNew.Columns.Count != tableOld.Columns.Count)
{
command = "DROP TABLE wikilist_" + POut.String(nameNew);
Db.NonQ(command);
throw new Exception("Error occurred renaming list. Mismatch found in column count. No changes made.");
}
for (int r1 = 0; r1 < tableNew.Rows.Count; r1++)
{
for (int r2 = 0; r2 < tableOld.Rows.Count; r2++)
{
if (tableNew.Rows[r1][0] != tableOld.Rows[r2][0])
{
continue; //pk does not match
}
for (int c = 0; c < tableNew.Columns.Count; c++) //both lists have same number of columns
{
if (tableNew.Rows[r1][c] == tableOld.Rows[r2][c])
{
continue; //contents match
}
throw new Exception("Error occurred renaming list. Mismatch Error found in row data. No changes made.");
} //end columns
} //end tableOld
} //end tableNew
//Alter table names----------------------------------------------------------------------------
string priKeyColNameOrig = POut.String(nameOriginal) + "Num";
if (!tableNew.Columns.Contains(priKeyColNameOrig)) //if new table doesn't contain a PK based on the old table name, make the first column the nameNew+"Num" PK column
{
priKeyColNameOrig = POut.String(tableNew.Columns[0].ColumnName);
}
if (DataConnection.DBtype == DatabaseType.MySql)
{
command = "ALTER TABLE wikilist_" + POut.String(nameNew) + " CHANGE " + priKeyColNameOrig + " " + POut.String(nameNew) + "Num bigint NOT NULL auto_increment PRIMARY KEY";
}
else
{
command = "RENAME COLUMN wikilist_" + POut.String(nameNew) + "." + priKeyColNameOrig + " TO " + POut.String(nameNew) + "Num";
}
Db.NonQ(command);
command = "UPDATE wikilistheaderwidth SET ListName='" + POut.String(nameNew) + "' WHERE ListName='" + POut.String(nameOriginal) + "'";
Db.NonQ(command);
command = $@"UPDATE wikilistheaderwidth SET ColName='{POut.String(nameNew)}Num'
WHERE ListName='{POut.String(nameNew)}' AND ColName='{priKeyColNameOrig}'" ;
Db.NonQ(command);
//drop old table---------------------
command = "DROP TABLE wikilist_" + POut.String(nameOriginal);
Db.NonQ(command);
WikiListHeaderWidths.RefreshCache();
}
public static DataTable GetItem(string listName, long itemNum, string colName = null)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listName, itemNum, colName));
}
colName = POut.String(string.IsNullOrEmpty(colName)?(listName + "Num"):colName);
string command = $"SELECT * FROM wikilist_{POut.String(listName)} WHERE {colName}={POut.Long(itemNum)}";
return(Db.GetTable(command));
}
/// <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 (ItemTable.Columns.Count < 2)
{
//if the table contains only a PK column.
return;
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), listName, ItemTable);
return;
}
List <string> listRowSets = ItemTable.Columns.OfType <DataColumn>().Skip(1) //skip 1 because we do not need to update the PK
.Select(x => POut.String(x.ColumnName) + $"='{POut.String(ItemTable.Rows[0][x].ToString())}'").ToList();
string command = $@"UPDATE wikilist_{POut.String(listName)} SET {string.Join(@",
",listRowSets)}
WHERE {POut.String(ItemTable.Columns[0].ColumnName)}={POut.Long(PIn.Long(ItemTable.Rows[0][0].ToString()))}" ;
Db.NonQ(command);
}
///<summary>Check to see if column can be deleted, returns true is the column contains only nulls.</summary>
public static void DeleteColumn(string listName, string colName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), listName, colName);
return;
}
string command = "ALTER TABLE wikilist_" + POut.String(listName) + " DROP " + POut.String(colName);
Db.NonQ(command);
WikiListHeaderWidths.Delete(listName, colName);
}
///<summary>Returns true if there is an active phone for the country code.</summary>
public static bool IsTextingForCountry(params string[] countryCodes)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), countryCodes));
}
if (countryCodes == null || countryCodes.Length == 0)
{
return(false);
}
string command = "SELECT COUNT(*) FROM smsphone WHERE CountryCode IN (" + string.Join(",", countryCodes.Select(x => "'" + POut.String(x) + "'")) + ") AND " + DbHelper.Year("DateTimeInactive") + "<1880";
return(Db.GetScalar(command) != "0");
}
///<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>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 the FeeNum from the database, returns 0 if none found.</summary>
public static long GetFeeNum(long codeNum, long feeSchedNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod(), codeNum, feeSchedNum));
}
string command = "SELECT FeeNum FROM fee WHERE CodeNum=" + POut.Long(codeNum) + " AND FeeSched=" + POut.Long(feeSchedNum);
return(PIn.Long(Db.GetScalar(command)));
}
///<summary>Fetches StatementNums restricted by the DateTStamp, PatNums and a limit of records per patient. If limitPerPatient is zero all StatementNums of a patient are fetched</summary>
public static List <long> GetChangedSinceStatementNums(DateTime changedSince, List <long> eligibleForUploadPatNumList, int limitPerPatient)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), changedSince, eligibleForUploadPatNumList));
}
List <long> statementnums = new List <long>();
string limitStr = "";
if (limitPerPatient > 0)
{
limitStr = "LIMIT " + limitPerPatient;
}
DataTable table;
// there are possibly more efficient ways to implement this using a single sql statement but readability of the sql can be compromised
if (eligibleForUploadPatNumList.Count > 0)
{
for (int i = 0; i < eligibleForUploadPatNumList.Count; i++)
{
string command = "SELECT StatementNum FROM statement WHERE DateTStamp > " + POut.DateT(changedSince) + " AND PatNum='"
+ eligibleForUploadPatNumList[i].ToString() + "' ORDER BY DateSent DESC, StatementNum DESC " + limitStr;
table = Db.GetTable(command);
for (int j = 0; j < table.Rows.Count; j++)
{
statementnums.Add(PIn.Long(table.Rows[j]["StatementNum"].ToString()));
}
}
}
return(statementnums);
}
///<summary>Column is automatically named "Column#" where # is the number of columns+1.</summary>
public static void AddColumn(string listName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), listName);
return;
}
//Find Valid column name-----------------------------------------------------------------------------------------
DataTable columnNames = Db.GetTable("DESCRIBE wikilist_" + POut.String(listName));
string newColumnName = "Column1"; //default in case table has no columns. Should never happen.
for (int i = 0; i < columnNames.Rows.Count + 1; i++) //+1 to guarantee we can find a valid name.
{
newColumnName = "Column" + (1 + i); //ie. Column1, Column2, Column3...
for (int j = 0; j < columnNames.Rows.Count; j++)
{
if (newColumnName == columnNames.Rows[j]["Field"].ToString())
{
newColumnName = "";
break;
}
}
if (newColumnName != "")
{
break; //found a valid name.
}
}
if (newColumnName == "")
{
//should never happen.
throw new ApplicationException("Could not create valid column name.");
}
//Add new column name--------------------------------------------------------------------------------------------
string command = "ALTER TABLE wikilist_" + POut.String(listName) + " ADD COLUMN " + POut.String(newColumnName) + " TEXT NOT NULL";
Db.NonQ(command);
//Add column widths to wikiListHeaderWidth Table-----------------------------------------------------------------
WikiListHeaderWidth headerWidth = new WikiListHeaderWidth();
headerWidth.ColName = newColumnName;
headerWidth.ListName = listName;
headerWidth.ColWidth = 100;
WikiListHeaderWidths.InsertNew(headerWidth);
}