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