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