///<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>Creates a snapshot for the claimprocs passed in. Used for reporting purposes. ///If called from Open Dental Service, ignore passed in claimprocs and make snapshots for the entire day of completed procedures in a different method. ///When passing in claimprocs, the implementor will need to ensure that only primary claimprocs are being saved. ///Only creates snapshots if the feature is enabled and if the claimproc is of certain statuses.</summary> public static void CreateClaimSnapshot(List <ClaimProc> listClaimProcs, ClaimSnapshotTrigger triggerType, string claimType) { //No need to check RemotingRole; no call to db. if (!PrefC.GetBool(PrefName.ClaimSnapshotEnabled) || PIn.Enum <ClaimSnapshotTrigger>(PrefC.GetString(PrefName.ClaimSnapshotTriggerType), true) != triggerType) { return; } if (triggerType == ClaimSnapshotTrigger.Service) { CreateClaimSnapShotService(); return; } Dictionary <long, double> dictCompletedProcFees = Procedures.GetProcsFromClaimProcs(listClaimProcs).ToDictionary(x => x.ProcNum, x => x.ProcFee); //This list will be used to check for existing claimsnapshots for the claimprocs passed in. We will update exisiting snapshots. List <ClaimSnapshot> listClaimSnapshotsOld = GetByClaimProcNums(listClaimProcs.Select(x => x.ClaimProcNum).ToList()); //Loop through all the claimprocs and create a claimsnapshot entry for each. foreach (ClaimProc cp in listClaimProcs) { //only create snapshots for 0=NotReceived, 1=Received, 4=Supplemental, 5=CapClaim, 6=Estimate (only if triggerType=Service), //7=CapComplete, and 8=CapEstimate (only if triggerType=Service) if (cp.Status.In(ClaimProcStatus.Preauth, ClaimProcStatus.Adjustment, ClaimProcStatus.Estimate, ClaimProcStatus.CapEstimate)) { continue; } //get the procfee double procFee; if (!dictCompletedProcFees.TryGetValue(cp.ProcNum, out procFee)) { procFee = 0; } //If there is an existing claimsnapshot created Today for the current cp.ProcNum, cp.ClaimProcNum, claimType, and the ClaimSnapshotTrigger //is not Service, then update it. Otherwise, create a new one. //This fixes an issue with reports not showing the correct writeoffs. Ex. A procedure was completed, a claim was created, the claim was deleted, //the writeoff was modified on the claimproc, then a new claim was created. ClaimSnapshot existingSnapshot = listClaimSnapshotsOld.FirstOrDefault(x => x.DateTEntry.Date == DateTime.Today.Date && x.ProcNum == cp.ProcNum && x.ClaimProcNum == cp.ClaimProcNum && x.ClaimType == claimType && x.SnapshotTrigger != ClaimSnapshotTrigger.Service); if (existingSnapshot != null) { SetSnapshotFields(existingSnapshot, cp, procFee, triggerType, claimType); ClaimSnapshots.Update(existingSnapshot); continue; } ClaimSnapshot snapshot = new ClaimSnapshot(); SetSnapshotFields(snapshot, cp, procFee, triggerType, claimType); ClaimSnapshots.Insert(snapshot); } }
///<summary>Gets the eService code associated to the procCode. Returns Undefined if the procedure is not associated to one.</summary> public static eServiceCode GetEService(string procCode) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <eServiceCode>(MethodBase.GetCurrentMethod(), procCode)); } string command = @"SELECT MIN(EService) FROM eservicecodelink INNER JOIN procedurecode ON eservicecodelink.CodeNum=procedurecode.CodeNum WHERE procedurecode.ProcCode='" + POut.String(procCode) + "'"; DataTable table = Db.GetTable(command); if (table.Rows.Count == 0) { return(eServiceCode.Undefined); } return(PIn.Enum(table.Rows[0][0].ToString(), false, eServiceCode.Undefined)); }
public static ErxOption GetErxOption() { Program progCur = Programs.GetCur(ProgramName.eRx); if (progCur == null) { throw new ODException(Lans.g("eRx", "The eRx bridge is missing from the database.")); } List <ProgramProperty> listProgramProperties = ProgramProperties.GetForProgram(progCur.ProgramNum); ProgramProperty propCur = listProgramProperties.FirstOrDefault(x => x.PropertyDesc == PropertyDescs.ErxOption); if (propCur == null) { throw new ODException(Lans.g("eRx", "The eRx Option program property is missing from the database.")); } return(PIn.Enum <ErxOption>(propCur.PropertyValue)); }
///<summary>Get all all eService code links in the table and merges with proc codes.</summary> public static List <EServiceCodeLink> GetAll() { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <EServiceCodeLink> >(MethodBase.GetCurrentMethod())); } string command = "SELECT eservicecodelink.EServiceCodeLinkNum,eservicecodelink.CodeNum,eservicecodelink.EService,procedurecode.ProcCode FROM procedurecode " + "INNER JOIN eservicecodelink ON procedurecode.CodeNum=eservicecodelink.CodeNum"; return(DataCore.GetTable(command) .AsEnumerable() .Select(x => new EServiceCodeLink { EServiceCodeLinkNum = PIn.Long(x["EServiceCodeLinkNum"].ToString()), CodeNum = PIn.Long(x["CodeNum"].ToString()), EService = PIn.Enum <eServiceCode>(x["EService"].ToString()), ProcCode = PIn.String(x["ProcCode"].ToString()), }).ToList()); }
///<summary>Creates a snapshot for the claimprocs passed in. Used for reporting purposes. ///If called from Open Dental Service, ignore passed in claimprocs and make snapshots for the entire day of completed procedures in a different method. ///When passing in claimprocs, the implementor will need to ensure that only primary claimprocs are being saved. ///Only creates snapshots if the feature is enabled and if the claimproc is of certain statuses.</summary> public static void CreateClaimSnapshot(List <ClaimProc> listClaimProcs, ClaimSnapshotTrigger triggerType, string claimType) { //No need to check RemotingRole; no call to db. if (!PrefC.GetBool(PrefName.ClaimSnapshotEnabled) || PIn.Enum <ClaimSnapshotTrigger>(PrefC.GetString(PrefName.ClaimSnapshotTriggerType), true) != triggerType) { return; } if (triggerType == ClaimSnapshotTrigger.Service) { CreateClaimSnapShotService(); return; } Dictionary <long, double> dictCompletedProcFees = Procedures.GetProcsFromClaimProcs(listClaimProcs).ToDictionary(x => x.ProcNum, x => x.ProcFee); //Loop through all the claimprocs and create a claimsnapshot entry for each. foreach (ClaimProc cp in listClaimProcs) { //only create snapshots for 0=NotReceived, 1=Received, 4=Supplemental, 5=CapClaim, 6=Estimate (only if triggerType=Service), //7=CapComplete, and 8=CapEstimate (only if triggerType=Service) if (cp.Status.In(ClaimProcStatus.Preauth, ClaimProcStatus.Adjustment, ClaimProcStatus.Estimate, ClaimProcStatus.CapEstimate)) { continue; } //get the procfee double procFee; if (!dictCompletedProcFees.TryGetValue(cp.ProcNum, out procFee)) { procFee = 0; } ClaimSnapshot snapshot = new ClaimSnapshot(); snapshot.ProcNum = cp.ProcNum; snapshot.Writeoff = cp.WriteOff; snapshot.InsPayEst = cp.InsEstTotal; snapshot.Fee = procFee; snapshot.ClaimProcNum = cp.ClaimProcNum; snapshot.SnapshotTrigger = triggerType; snapshot.ClaimType = claimType; ClaimSnapshots.Insert(snapshot); } }
///<summary>Gets the DataTable to display for treatment finder report</summary> ///<param name="listProviders">Include '0' in the list to get for all providers.</param> ///<param name="listBilling">Include '0' in the list to get for all billing types.</param> ///<param name="listClinicNums">Pass in an empty list to get for all clinics.</param> public static DataTable GetTreatmentFinderList(bool noIns, bool patsWithAppts, int monthStart, DateTime dateSince, double aboveAmount, List <long> listProviders, List <long> listBilling, string code1, string code2, List <long> listClinicNums, bool isProcsGeneral) { //No remoting role check; no call to db Stopwatch sw = null; Stopwatch sTotal = null; if (ODBuild.IsDebug()) { sw = Stopwatch.StartNew(); sTotal = Stopwatch.StartNew(); } DataTable table = new DataTable(); //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("PatNum"); table.Columns.Add("LName"); table.Columns.Add("FName"); table.Columns.Add("contactMethod"); table.Columns.Add("address"); table.Columns.Add("City"); table.Columns.Add("State"); table.Columns.Add("Zip"); table.Columns.Add("annualMaxInd"); table.Columns.Add("annualMaxFam"); table.Columns.Add("amountUsedInd"); table.Columns.Add("amountUsedFam"); table.Columns.Add("amountPendingInd"); table.Columns.Add("amountPendingFam"); table.Columns.Add("amountRemainingInd"); table.Columns.Add("amountRemainingFam"); table.Columns.Add("treatmentPlan"); table.Columns.Add("carrierName"); table.Columns.Add("clinicAbbr"); //dictionary with Key=PatNum, Value=AmtPlanned Dictionary <long, double> dictAmtPlanned = new Dictionary <long, double>(); using (DataTable tablePlanned = GetDictAmtPlanned(patsWithAppts, dateSince, listProviders, listBilling, code1, code2, listClinicNums)) { if (ODBuild.IsDebug()) { sw.Stop(); Console.WriteLine("Get tablePlanned: " + sw.Elapsed.TotalSeconds + " sec, Rows: " + tablePlanned.Rows.Count); sw = Stopwatch.StartNew(); } if (tablePlanned.Rows.Count == 0) { return(table); } dictAmtPlanned = tablePlanned.Select().ToDictionary(x => PIn.Long(x["PatNum"].ToString()), x => PIn.Double(x["AmtPlanned"].ToString())); } string patNumStr = string.Join(",", dictAmtPlanned.Keys.Select(x => POut.Long(x))); DateTime renewDate = BenefitLogic.ComputeRenewDate(DateTime.Now, monthStart); //dictionary with Key=PatPlanNum, Value=Tuple(AmtPending,AmtUsed) Dictionary <long, Tuple <double, double> > dictPatInfo = new Dictionary <long, Tuple <double, double> >(); using (DataTable tablePat = GetPatInfo(isProcsGeneral, renewDate, patNumStr)) { dictPatInfo = tablePat.Select().ToDictionary(x => PIn.Long(x["PatPlanNum"].ToString()), x => Tuple.Create(PIn.Double(x["AmtPending"].ToString()), PIn.Double(x["AmtUsed"].ToString()))); } if (ODBuild.IsDebug()) { sw.Stop(); Console.WriteLine("Get dictPatInfo: " + sw.Elapsed.TotalSeconds + " sec, Count: " + dictPatInfo.Count); sw = Stopwatch.StartNew(); } //dictionary with Key=InsSubNum, Value=Tuple(AmtPending,AmtUsed) Dictionary <long, Tuple <double, double> > dictFamInfo = new Dictionary <long, Tuple <double, double> >(); using (DataTable tableFam = GetFamInfo(isProcsGeneral, renewDate, patNumStr)) { dictFamInfo = tableFam.Select().ToDictionary(x => PIn.Long(x["InsSubNum"].ToString()), x => Tuple.Create(PIn.Double(x["AmtPending"].ToString()), PIn.Double(x["AmtUsed"].ToString()))); } if (ODBuild.IsDebug()) { sw.Stop(); Console.WriteLine("Get dictFamInfo: " + sw.Elapsed.TotalSeconds + " sec, Rows: " + dictFamInfo.Count); sw = Stopwatch.StartNew(); } //dictionary with Key=PlanNum, Value=Tuple(AnnualMaxInd,AnnualMaxFam) Dictionary <long, Tuple <double, double> > dictAnnualMax = new Dictionary <long, Tuple <double, double> >(); using (DataTable tableAnnualMax = GetAnnualMaxInfo(patNumStr)) { dictAnnualMax = tableAnnualMax.Select().ToDictionary(x => PIn.Long(x["PlanNum"].ToString()), x => Tuple.Create(PIn.Double(x["AnnualMaxInd"].ToString()), PIn.Double(x["AnnualMaxFam"].ToString()))); } if (ODBuild.IsDebug()) { sw.Stop(); Console.WriteLine("Get dictAnnualMax: " + sw.Elapsed.TotalSeconds + " sec, Rows: " + dictAnnualMax.Count); sw = Stopwatch.StartNew(); } using (DataTable rawtable = GetTableRaw(noIns, monthStart, patNumStr)) { if (ODBuild.IsDebug()) { sw.Stop(); Console.WriteLine("Get RawTable: " + sw.Elapsed.TotalSeconds + " sec, Rows: " + rawtable.Rows.Count); sw = Stopwatch.StartNew(); } DataRow row; foreach (DataRow rawRow in rawtable.Rows) { row = table.NewRow(); long patNum = PIn.Long(rawRow["PatNum"].ToString()); long patPlanNum = PIn.Long(rawRow["PatPlanNum"].ToString()); long planNum = PIn.Long(rawRow["PlanNum"].ToString()); long insSubNum = PIn.Long(rawRow["InsSubNum"].ToString()); double amtPlanned = dictAmtPlanned.TryGetValue(patNum, out amtPlanned)?amtPlanned:0; Tuple <double, double> tuplePatInfo = dictPatInfo.TryGetValue(patPlanNum, out tuplePatInfo)?tuplePatInfo:Tuple.Create(0d, 0d); double patAmtPending = tuplePatInfo.Item1; double patAmtUsed = tuplePatInfo.Item2; Tuple <double, double> tupleFamInfo = dictFamInfo.TryGetValue(insSubNum, out tupleFamInfo)?tupleFamInfo:Tuple.Create(0d, 0d); double famAmtPending = tupleFamInfo.Item1; double famAmtUsed = tupleFamInfo.Item2; Tuple <double, double> tupleAnnualMax = dictAnnualMax.TryGetValue(planNum, out tupleAnnualMax)?tupleAnnualMax:Tuple.Create(0d, 0d); double patAnnualMax = tupleAnnualMax.Item1; double famAnnualMax = tupleAnnualMax.Item2; if (aboveAmount > 0) { if (dictAnnualMax.ContainsKey(planNum) && ((patAnnualMax != -1 && patAnnualMax - patAmtUsed <= aboveAmount) || (famAnnualMax != -1 && famAnnualMax - famAmtUsed <= aboveAmount))) { continue; } } row["PatNum"] = patNum; row["LName"] = rawRow["LName"].ToString(); row["FName"] = rawRow["FName"].ToString(); ContactMethod contmeth = PIn.Enum <ContactMethod>(rawRow["PreferRecallMethod"].ToString()); switch (contmeth) { case ContactMethod.None: if (PrefC.GetBool(PrefName.RecallUseEmailIfHasEmailAddress) && !string.IsNullOrEmpty(rawRow["Email"].ToString())) { row["contactMethod"] = rawRow["Email"].ToString(); } else { row["contactMethod"] = Lans.g("FormRecallList", "Hm:") + rawRow["HmPhone"].ToString(); } break; case ContactMethod.HmPhone: row["contactMethod"] = Lans.g("FormRecallList", "Hm:") + rawRow["HmPhone"].ToString(); break; case ContactMethod.WkPhone: row["contactMethod"] = Lans.g("FormRecallList", "Wk:") + rawRow["WkPhone"].ToString(); break; case ContactMethod.WirelessPh: row["contactMethod"] = Lans.g("FormRecallList", "Cell:") + rawRow["WirelessPhone"].ToString(); break; case ContactMethod.Email: row["contactMethod"] = rawRow["Email"].ToString(); break; case ContactMethod.Mail: case ContactMethod.DoNotCall: case ContactMethod.SeeNotes: row["contactMethod"] = Lans.g("enumContactMethod", contmeth.ToString()); break; } row["address"] = rawRow["Address"].ToString() + (string.IsNullOrEmpty(rawRow["Address2"].ToString())?"":("\r\n" + rawRow["Address2"].ToString())); row["City"] = rawRow["City"].ToString(); row["State"] = rawRow["State"].ToString(); row["Zip"] = rawRow["Zip"].ToString(); row["annualMaxInd"] = patAnnualMax.ToString("N"); row["annualMaxFam"] = famAnnualMax.ToString("N"); row["amountUsedInd"] = patAmtUsed.ToString("N"); row["amountUsedFam"] = famAmtUsed.ToString("N"); row["amountPendingInd"] = patAmtPending.ToString("N"); row["amountPendingFam"] = famAmtPending.ToString("N"); row["amountRemainingInd"] = (patAnnualMax - patAmtUsed - patAmtPending).ToString("N"); row["amountRemainingFam"] = (famAnnualMax - famAmtUsed - famAmtPending).ToString("N"); row["treatmentPlan"] = amtPlanned.ToString("N"); row["carrierName"] = rawRow["carrierName"].ToString(); if (PrefC.HasClinicsEnabled) { row["clinicAbbr"] = rawRow["clinicAbbr"].ToString(); } table.Rows.Add(row); } } if (ODBuild.IsDebug()) { sw.Stop(); sTotal.Stop(); Console.WriteLine("Finished Filling DataTable: {0}\r\n\tTotal time: {1}\r\n\tRows: {2}", (sw.Elapsed.Minutes > 0?(sw.Elapsed.Minutes + " min "):"") + (sw.Elapsed.TotalSeconds - sw.Elapsed.Minutes * 60) + " sec", (sTotal.Elapsed.Minutes > 0?(sTotal.Elapsed.Minutes + " min "):"") + (sTotal.Elapsed.TotalSeconds - sTotal.Elapsed.Minutes * 60) + " sec", table.Rows.Count); } return(table); }
///<summary>Gets a pref of the specified enum type.</summary> public static T GetEnum <T>(PrefName prefName) where T : struct, IConvertible { return(PIn.Enum <T>(GetInt(prefName))); }
///<summary>Gets the list of patients that need to be on the reactivation list based on the passed in filters.</summary> public static DataTable GetReactivationList(DateTime dateSince, DateTime dateStop, bool groupFamilies, bool showDoNotContact, bool isInactiveIncluded , long provNum, long clinicNum, long siteNum, long billingType, ReactivationListSort sortBy, RecallListShowNumberReminders showReactivations) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateSince, dateStop, groupFamilies, showDoNotContact, isInactiveIncluded, provNum, clinicNum , siteNum, billingType, sortBy, showReactivations)); } //Get information we will need to do the query List <long> listReactCommLogTypeDefNums = Defs.GetDefsForCategory(DefCat.CommLogTypes, isShort: true) .FindAll(x => CommItemTypeAuto.REACT.GetDescription(useShortVersionIfAvailable: true).Equals(x.ItemValue)).Select(x => x.DefNum).ToList(); int contactInterval = PrefC.GetInt(PrefName.ReactivationContactInterval); List <PatientStatus> listPatStatuses = new List <PatientStatus>() { PatientStatus.Patient, PatientStatus.Prospective }; if (isInactiveIncluded) { listPatStatuses.Add(PatientStatus.Inactive); } string strPatStatuses = string.Join(",", listPatStatuses.Select(x => POut.Int((int)x))); //Get the raw set of patients who should be on the reactivation list string cmd = $@"SELECT pat.PatNum, pat.LName, pat.FName, pat.MiddleI, pat.Preferred, pat.Guarantor, pat.PatStatus, pat.Birthdate, pat.PriProv, COALESCE(billingtype.ItemName,'') AS BillingType, pat.ClinicNum, pat.SiteNum, pat.PreferRecallMethod, '' AS ContactMethod, pat.HmPhone, pat.WirelessPhone, pat.WkPhone, {(groupFamilies?"COALESCE(guarantor.Email,pat.Email,'') AS Email,":"pat.Email,")} MAX(proc.ProcDate) AS DateLastProc, COALESCE(comm.DateLastContacted,'') AS DateLastContacted, COALESCE(comm.ContactedCount,0) AS ContactedCount, COALESCE(react.ReactivationNum,0) AS ReactivationNum, COALESCE(react.ReactivationStatus,0) AS ReactivationStatus, COALESCE(react.DoNotContact,0) as DoNotContact, react.ReactivationNote, guarantor.PatNum as GuarNum, guarantor.LName as GuarLName, guarantor.FName as GuarFName FROM patient pat INNER JOIN procedurelog proc ON pat.PatNum=proc.PatNum AND proc.ProcStatus={POut.Int((int)ProcStat.C)} LEFT JOIN appointment appt ON pat.PatNum=appt.PatNum AND appt.AptDateTime >= {DbHelper.Curdate()} LEFT JOIN ( SELECT commlog.PatNum, MAX(commlog.CommDateTime) AS DateLastContacted, COUNT(*) AS ContactedCount FROM commlog WHERE commlog.CommType IN ({string.Join(",",listReactCommLogTypeDefNums)}) GROUP BY commlog.PatNum ) comm ON pat.PatNum=comm.PatNum LEFT JOIN reactivation react ON pat.PatNum=react.PatNum LEFT JOIN definition billingtype ON pat.BillingType=billingtype.DefNum INNER JOIN patient guarantor ON pat.Guarantor=guarantor.PatNum WHERE pat.PatStatus IN ({strPatStatuses}) " ; cmd += provNum > 0?" AND pat.PriProv=" + POut.Long(provNum):""; cmd += clinicNum > -1?" AND pat.ClinicNum=" + POut.Long(clinicNum):""; //might still want to get the 0 clinic pats cmd += siteNum > 0?" AND pat.SiteNum=" + POut.Long(siteNum):""; cmd += billingType > 0?" AND pat.BillingType=" + POut.Long(billingType):""; cmd += showDoNotContact?"":" AND (react.DoNotContact IS NULL OR react.DoNotContact=0)"; cmd += contactInterval > -1?" AND (comm.DateLastContacted IS NULL OR comm.DateLastContacted <= " + POut.DateT(DateTime.Today.AddDays(-contactInterval)) + ") ":""; //set number of contact attempts int maxReminds = PrefC.GetInt(PrefName.ReactivationCountContactMax); if (showReactivations == RecallListShowNumberReminders.SixPlus) { cmd += " AND ContactedCount>=6 "; //don't need to look at pref this only shows in UI if the prefvalue allows it } else if (showReactivations == RecallListShowNumberReminders.Zero) { cmd += " AND (comm.ContactedCount=0 OR comm.ContactedCount IS NULL) "; } else if (showReactivations != RecallListShowNumberReminders.All) { int filter = (int)showReactivations - 1; //if the contactmax pref is not -1 or 0, and the contactmax is smaller than the requested filter, replace the filter with the contactmax cmd += " AND comm.ContactedCount=" + POut.Int((maxReminds > 0 && maxReminds < filter)?maxReminds:filter) + " "; } else if (showReactivations == RecallListShowNumberReminders.All) //get all but filter on the contactmax { cmd += " AND (comm.ContactedCount < " + POut.Int(maxReminds) + " OR comm.ContactedCount IS NULL) "; } cmd += $@" GROUP BY pat.PatNum HAVING MAX(proc.ProcDate) < {POut.Date(dateSince)} AND MAX(proc.ProcDate) >= {POut.Date(dateStop)} AND MIN(appt.AptDateTime) IS NULL " ; //set the sort by switch (sortBy) { case ReactivationListSort.Alphabetical: cmd += " ORDER BY " + (groupFamilies?"guarantor.LName,guarantor.FName,pat.FName":"pat.LName,pat.FName"); break; case ReactivationListSort.BillingType: cmd += " ORDER BY billingtype.ItemName,DateLastContacted" + (groupFamilies?",guarantor.LName,guarantor.FName":""); break; case ReactivationListSort.LastContacted: cmd += " ORDER BY IF(comm.DateLastContacted='' OR comm.DateLastContacted IS NULL,1,0),comm.DateLastContacted" + (groupFamilies?",guarantor.LName,guarantor.FName":""); break; case ReactivationListSort.LastSeen: cmd += " ORDER BY MAX(proc.ProcDate)"; break; } DataTable dtReturn = Db.GetTable(cmd); foreach (DataRow row in dtReturn.Rows) { //FOR REVIEW: currently, we are displaying PreferRecallMethod, which is what RecallList also does. Just want to make sure we don't want to use PreferContactMethod row["ContactMethod"] = Recalls.GetContactFromMethod(PIn.Enum <ContactMethod>(row["PreferRecallMethod"].ToString()), groupFamilies , row["HmPhone"].ToString(), row["WkPhone"].ToString(), row["WirelessPhone"].ToString(), row["Email"].ToString() //guarEmail queried as Email , row["Email"].ToString()); //Pat.Email is also "Email" } return(dtReturn); }