///<summary>Set all objects related to orthocases for a patient inactive besides the ones passed in.</summary>
public static void DeactivateOthersForPat(long activeOrthoCaseNum, long activeOrthoScheduleNum, long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), activeOrthoCaseNum, activeOrthoScheduleNum, patNum);
return;
}
//Get all orthocase nums to deactivate.
List <long> listOrthoCaseNums = Refresh(patNum).Where(x => x.OrthoCaseNum != activeOrthoCaseNum).Select(x => x.OrthoCaseNum).ToList();
if (listOrthoCaseNums.Count <= 0)
{
return;
}
//Set all other orthocases inactive besides one being activated
string command = $@"UPDATE orthocase SET orthocase.IsActive={POut.Bool(false)}
WHERE orthocase.OrthoCaseNum IN({string.Join(",",listOrthoCaseNums)})" ;
Db.NonQ(command);
//Set OrthoPlanLinks inactive
command = $@"UPDATE orthoplanlink SET orthoplanlink.IsActive={POut.Bool(false)}
WHERE orthoplanlink.OrthoCaseNum IN({string.Join(",",listOrthoCaseNums)})" ;
Db.NonQ(command);
//Get All OrthoPlanLinks to deactivate
List <long> listOrthoScheduleNums =
OrthoPlanLinks.GetAllForOrthoCasesByType(listOrthoCaseNums, OrthoPlanLinkType.OrthoSchedule).Select(x => x.FKey).ToList();
if (listOrthoScheduleNums.Count <= 0)
{
return;
}
//Set OrthoSchedules inactive
command = $@"UPDATE orthoschedule SET orthoschedule.IsActive={POut.Bool(false)}
WHERE orthoschedule.OrthoScheduleNum IN({string.Join(",",listOrthoScheduleNums)})" ;
Db.NonQ(command);
}
///<summary>For orderBy, use 0 for BillingType and 1 for PatientName.</summary>
public static DataTable GetBilling(bool isSent, int orderBy, DateTime dateFrom, DateTime dateTo, List <long> clinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), isSent, orderBy, dateFrom, dateTo, clinicNums));
}
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");
table.Columns.Add("SuperFamily");
table.Columns.Add("ClinicNum");
string command = "SELECT guar.BalTotal,patient.BillingType,patient.FName,guar.InsEst,statement.IsSent,"
+ "IFNULL(MAX(s2.DateSent)," + POut.Date(DateTime.MinValue) + ") LastStatement,"
+ "patient.LName,patient.MiddleI,statement.Mode_,guar.PayPlanDue,patient.Preferred,"
+ "statement.PatNum,statement.StatementNum,statement.SuperFamily,patient.ClinicNum "
+ "FROM statement "
+ "LEFT JOIN patient ON statement.PatNum=patient.PatNum "
+ "LEFT JOIN patient guar ON guar.PatNum=patient.Guarantor "
+ "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 (clinicNums.Count > 0)
{
command += "AND patient.ClinicNum IN (" + string.Join(",", clinicNums) + ") ";
}
command += "GROUP BY guar.BalTotal,patient.BillingType,patient.FName,guar.InsEst,statement.IsSent,"
+ "patient.LName,patient.MiddleI,statement.Mode_,guar.PayPlanDue,patient.Preferred,"
+ "statement.PatNum,statement.StatementNum,statement.SuperFamily ";
if (orderBy == 0) //BillingType
{
command += "ORDER BY definition.ItemOrder,patient.LName,patient.FName,patient.MiddleI,guar.PayPlanDue";
}
else
{
command += "ORDER BY patient.LName,patient.FName";
}
DataTable rawTable = Db.GetTable(command);
double balTotal;
double insEst;
double payPlanDue;
DateTime lastStatement;
List <Patient> listFamilyGuarantors;
foreach (DataRow rawRow in rawTable.Rows)
{
row = table.NewRow();
if (rawRow["SuperFamily"].ToString() == "0") //not a super statement, just get bal info from guarantor
{
balTotal = PIn.Double(rawRow["BalTotal"].ToString());
insEst = PIn.Double(rawRow["InsEst"].ToString());
payPlanDue = PIn.Double(rawRow["PayPlanDue"].ToString());
}
else //super statement, add all guar positive balances to get bal total for super family
{
listFamilyGuarantors = Patients.GetSuperFamilyGuarantors(PIn.Long(rawRow["SuperFamily"].ToString())).FindAll(x => x.HasSuperBilling);
//exclude fams with neg balances in the total for super family stmts (per Nathan 5/25/2016)
if (PrefC.GetBool(PrefName.BalancesDontSubtractIns))
{
listFamilyGuarantors = listFamilyGuarantors.FindAll(x => x.BalTotal > 0);
insEst = 0;
}
else
{
listFamilyGuarantors = listFamilyGuarantors.FindAll(x => (x.BalTotal - x.InsEst) > 0);
insEst = listFamilyGuarantors.Sum(x => x.InsEst);
}
balTotal = listFamilyGuarantors.Sum(x => x.BalTotal);
payPlanDue = listFamilyGuarantors.Sum(x => x.PayPlanDue);
}
row["amountDue"] = (balTotal - insEst).ToString("F");
row["balTotal"] = balTotal.ToString("F");;
row["billingType"] = Defs.GetName(DefCat.BillingTypes, PIn.Long(rawRow["BillingType"].ToString()));
if (insEst == 0)
{
row["insEst"] = "";
}
else
{
row["insEst"] = insEst.ToString("F");
}
row["IsSent"] = rawRow["IsSent"].ToString();
lastStatement = PIn.Date(rawRow["LastStatement"].ToString());
if (lastStatement.Year < 1880)
{
row["lastStatement"] = "";
}
else
{
row["lastStatement"] = lastStatement.ToShortDateString();
}
row["mode"] = Lans.g("enumStatementMode", ((StatementMode)PIn.Int(rawRow["Mode_"].ToString())).ToString());
row["name"] = Patients.GetNameLF(rawRow["LName"].ToString(), rawRow["FName"].ToString(), rawRow["Preferred"].ToString(), rawRow["MiddleI"].ToString());
row["PatNum"] = rawRow["PatNum"].ToString();
if (payPlanDue == 0)
{
row["payPlanDue"] = "";
}
else
{
row["payPlanDue"] = payPlanDue.ToString("F");
}
row["StatementNum"] = rawRow["StatementNum"].ToString();
row["SuperFamily"] = rawRow["SuperFamily"].ToString();
row["ClinicNum"] = rawRow["ClinicNum"].ToString();
table.Rows.Add(row);
}
return(table);
}
///<summary>The main logic that sends Podium invitations. Set isService true only when the calling method is the Open Dental Service.</summary>
public static void ThreadPodiumSendInvitations(bool isService)
{
long programNum = Programs.GetProgramNum(ProgramName.Podium);
//Consider blocking re-entrance if this hasn't finished.
//Only send invitations if the program link is enabled, the computer name is set to this computer, and eConnector is not set to send invitations
if (!Programs.IsEnabled(ProgramName.Podium) ||
!ODEnvironment.IdIsThisComputer(ProgramProperties.GetPropVal(programNum, PropertyDescs.ComputerNameOrIP)) ||
ProgramProperties.GetPropVal(programNum, PropertyDescs.UseService) != POut.Bool(isService))
{
return;
}
//Keep a consistant "Now" timestamp throughout this method.
DateTime nowDT = MiscData.GetNowDateTime();
if (Podium.DateTimeLastRan == DateTime.MinValue) //First time running the thread.
{
Podium.DateTimeLastRan = nowDT.AddMilliseconds(-PodiumThreadIntervalMS);
}
ReviewInvitationTrigger newPatTrigger = PIn.Enum <ReviewInvitationTrigger>(ProgramProperties.GetPropVal(programNum, PropertyDescs.NewPatientTriggerType));
ReviewInvitationTrigger existingPatTrigger = PIn.Enum <ReviewInvitationTrigger>(ProgramProperties.GetPropVal(programNum, PropertyDescs.ExistingPatientTriggerType));
List <Appointment> listNewPatAppts = GetAppointmentsToSendReview(newPatTrigger, programNum, true);
foreach (Appointment apptCur in listNewPatAppts)
{
Podium.SendData(Patients.GetPat(apptCur.PatNum), apptCur.ClinicNum);
}
List <Appointment> listExistingPatAppts = GetAppointmentsToSendReview(existingPatTrigger, programNum, false);
foreach (Appointment apptCur in listExistingPatAppts)
{
Podium.SendData(Patients.GetPat(apptCur.PatNum), apptCur.ClinicNum);
}
Podium.DateTimeLastRan = nowDT;
}
///<summary>Used to get sheets filled via the web. Passing in a null or empty list of clinic nums will only return sheets that are not assigned to a clinic.</summary>
public static DataTable GetWebFormSheetsTable(DateTime dateFrom, DateTime dateTo, List <long> listClinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listClinicNums));
}
if (listClinicNums == null || listClinicNums.Count == 0)
{
listClinicNums = new List <long>()
{
0
}; //To ensure we filter on at least one clinic (HQ).
}
DataTable table = new DataTable("");
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("date");
table.Columns.Add("dateOnly", typeof(DateTime)); //to help with sorting
table.Columns.Add("dateTime", typeof(DateTime));
table.Columns.Add("description");
table.Columns.Add("time");
table.Columns.Add("timeOnly", typeof(TimeSpan)); //to help with sorting
table.Columns.Add("PatNum");
table.Columns.Add("SheetNum");
table.Columns.Add("IsDeleted");
table.Columns.Add("ClinicNum");
List <DataRow> rows = new List <DataRow>();
string command = "SELECT DateTimeSheet,Description,PatNum,SheetNum,IsDeleted,ClinicNum "
+ "FROM sheet WHERE "
+ "DateTimeSheet >= " + POut.Date(dateFrom) + " AND DateTimeSheet <= " + POut.Date(dateTo.AddDays(1)) + " "
+ "AND IsWebForm = " + POut.Bool(true) + " "
+ "AND (SheetType=" + POut.Long((int)SheetTypeEnum.PatientForm) + " OR SheetType=" + POut.Long((int)SheetTypeEnum.MedicalHistory) + ") "
+ (PrefC.HasClinicsEnabled ? "AND ClinicNum IN (" + string.Join(",", listClinicNums) + ") " : "");
DataTable rawSheet = Db.GetTable(command);
DateTime dateT;
for (int i = 0; i < rawSheet.Rows.Count; i++)
{
row = table.NewRow();
dateT = PIn.DateT(rawSheet.Rows[i]["DateTimeSheet"].ToString());
row["date"] = dateT.ToShortDateString();
row["dateOnly"] = dateT.Date;
row["dateTime"] = dateT;
row["description"] = rawSheet.Rows[i]["Description"].ToString();
row["PatNum"] = rawSheet.Rows[i]["PatNum"].ToString();
row["SheetNum"] = rawSheet.Rows[i]["SheetNum"].ToString();
if (dateT.TimeOfDay != TimeSpan.Zero)
{
row["time"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower();
}
row["timeOnly"] = dateT.TimeOfDay;
row["IsDeleted"] = rawSheet.Rows[i]["IsDeleted"].ToString();
row["ClinicNum"] = PIn.Long(rawSheet.Rows[i]["ClinicNum"].ToString());
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
DataView view = table.DefaultView;
view.Sort = "dateOnly,timeOnly";
table = view.ToTable();
return(table);
}
///<summary>Sets all subs to the value passed in. Returns the number of subs affected.</summary>
public static long SetAllSubsAssignBen(bool isAssignBen)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod(), isAssignBen));
}
string command = "UPDATE inssub SET AssignBen=" + POut.Bool(isAssignBen) + " WHERE AssignBen!=" + POut.Bool(isAssignBen);
return(Db.NonQ(command));
}
///<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);
}
///<summary>Updates various columns based on in memory changes in listSubs.</summary>
public static void UpdateMany(List <BugSubmission> listSubs, params string[] listColumns)
{
if (listSubs.Count == 0 || listColumns.Count() == 0)
{
return;
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), listSubs, listColumns);
return;
}
List <string> listColumnUpdates = new List <string>();
foreach (string column in listColumns)
{
List <string> listCases = new List <string>();
switch (column)
{
#region IsHidden
case "IsHidden":
foreach (BugSubmission sub in listSubs)
{
listCases.Add("WHEN " + POut.Long(sub.BugSubmissionNum) + " THEN " + POut.Bool(sub.IsHidden));
}
break;
#endregion
#region BugId
case "BugId":
foreach (BugSubmission sub in listSubs)
{
listCases.Add("WHEN " + POut.Long(sub.BugSubmissionNum) + " THEN " + POut.Long(sub.BugId));
}
break;
#endregion
}
listColumnUpdates.Add(column + "=(CASE BugSubmissionNum " + string.Join(" ", listCases) + " END)");
}
DataAction.RunBugsHQ(() => {
Db.NonQ("UPDATE bugsubmission SET "
+ string.Join(",", listColumnUpdates) + " "
+ "WHERE BugSubmissionNum IN (" + string.Join(",", listSubs.Select(x => x.BugSubmissionNum)) + ")");
}, false);
}
///<summary>Use by the proximity sensors in WebcamOD/ProximityOD</summary>
public static void SetProximity(bool isProximal, int extension)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), isProximal, extension);
return;
}
string command = "UPDATE phone SET DateTProximal = " + DbHelper.Now() + ", IsProximal = " + POut.Bool(isProximal)
+ " WHERE Extension = " + POut.Int(extension);
Db.NonQ(command);
}
public ClinicPref(long clinicNum, PrefName prefName, bool valueBool)
{
this.ClinicNum = clinicNum;
this.PrefName = prefName;
this.ValueString = POut.Bool(valueBool);
}
///<summary>Gets a Patients Active OrthoCase. Patient can only have one active OrthoCase so it is OK to return 1.</summary>
public static OrthoCase GetActiveForPat(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <OrthoCase>(MethodBase.GetCurrentMethod(), patNum));
}
string command = $"SELECT * FROM orthocase WHERE orthocase.PatNum={POut.Long(patNum)} AND orthocase.IsActive={POut.Bool(true)}";
return(Crud.OrthoCaseCrud.SelectOne(command));
}
///<summary>Searches keywords, title, content. Does not return pagetitles for drafts.</summary>
public static List <string> GetForSearch(string searchText, bool ignoreContent, bool isDeleted = false)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod(), searchText, ignoreContent, isDeleted));
}
List <string> retVal = new List <string>();
DataTable tableResults = new DataTable();
string[] searchTokens = POut.String(searchText).Split(' ');
string command = "";
//Match keywords first-----------------------------------------------------------------------------------
command =
"SELECT PageTitle FROM wikipage "
// \_ 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 '\\_%' AND IsDraft=0 "
+ "AND IsDeleted=" + POut.Bool(isDeleted) + " ";
for (int i = 0; i < searchTokens.Length; i++)
{
command += "AND KeyWords LIKE '%" + POut.String(searchTokens[i]) + "%' ";
}
command +=
"GROUP BY PageTitle "
+ "ORDER BY PageTitle";
tableResults = Db.GetTable(command);
for (int i = 0; i < tableResults.Rows.Count; i++)
{
if (!retVal.Contains(tableResults.Rows[i]["PageTitle"].ToString()))
{
retVal.Add(tableResults.Rows[i]["PageTitle"].ToString());
}
}
//Match PageTitle Second-----------------------------------------------------------------------------------
command =
"SELECT PageTitle FROM wikipage "
+ "WHERE PageTitle NOT LIKE '\\_%' AND IsDraft=0 "
+ "AND IsDeleted=" + POut.Bool(isDeleted) + " ";
for (int i = 0; i < searchTokens.Length; i++)
{
command += "AND PageTitle LIKE '%" + POut.String(searchTokens[i]) + "%' ";
}
command +=
"GROUP BY PageTitle "
+ "ORDER BY PageTitle";
tableResults = Db.GetTable(command);
for (int i = 0; i < tableResults.Rows.Count; i++)
{
if (!retVal.Contains(tableResults.Rows[i]["PageTitle"].ToString()))
{
retVal.Add(tableResults.Rows[i]["PageTitle"].ToString());
}
}
//Match Content third-----------------------------------------------------------------------------------
if (!ignoreContent)
{
command =
"SELECT PageTitle FROM wikipage "
+ "WHERE PageTitle NOT LIKE '\\_%' AND IsDraft=0 "
+ "AND IsDeleted=" + POut.Bool(isDeleted) + " ";
for (int i = 0; i < searchTokens.Length; i++)
{
command += "AND PageContentPlainText LIKE '%" + POut.String(searchTokens[i]) + "%' ";
}
command +=
"GROUP BY PageTitle "
+ "ORDER BY PageTitle";
tableResults = Db.GetTable(command);
for (int i = 0; i < tableResults.Rows.Count; i++)
{
if (!retVal.Contains(tableResults.Rows[i]["PageTitle"].ToString()))
{
retVal.Add(tableResults.Rows[i]["PageTitle"].ToString());
}
}
}
return(retVal);
}
