///<summary>Returns a string that can be used for securitylog entries.</summary>
public static string GetSecuritylogEntryText(Payment paymentNew, Payment paymentOld, bool isNew, List <Def> listPayTypes = null)
{
string secLogText;
if (listPayTypes == null)
{
listPayTypes = Defs.GetDefsForCategory(DefCat.PaymentTypes);
}
string clinicAbbrNew = Clinics.GetAbbr(paymentNew.ClinicNum);
if (isNew)
{
secLogText = $"Payment created for {Patients.GetLim(paymentNew.PatNum).GetNameLF()} with payment type '" +
GetPaymentTypeDesc(paymentNew, listPayTypes) + "'";
if (!string.IsNullOrEmpty(clinicAbbrNew))
{
secLogText += $", clinic '{clinicAbbrNew}'";
}
secLogText += $", amount '{paymentNew.PayAmt.ToString("c")}'";
}
else
{
secLogText = $"Payment edited for {Patients.GetLim(paymentNew.PatNum).GetNameLF()}";
secLogText += SecurityLogEntryTextHelper(paymentNew.PayAmt.ToString("c"), paymentOld.PayAmt.ToString("c"), "amount");
secLogText += SecurityLogEntryTextHelper(clinicAbbrNew, Clinics.GetAbbr(paymentOld.ClinicNum), "clinic");
secLogText += SecurityLogEntryTextHelper(paymentNew.PayDate.ToShortDateString(), paymentOld.PayDate.ToShortDateString(), "payment date");
secLogText += SecurityLogEntryTextHelper(GetPaymentTypeDesc(paymentNew, listPayTypes), GetPaymentTypeDesc(paymentOld, listPayTypes), "payment type");
}
return(secLogText);
}
///<summary>Supply the index of the cat within Defs.Short.</summary>
public static List <LetterMerge> GetListForCat(int catIndex)
{
//No need to check RemotingRole; no call to db.
long defNum = Defs.GetDefsForCategory(DefCat.LetterMergeCats, true)[catIndex].DefNum;
return(GetWhere(x => x.Category == defNum));
}
///<summary>Returns a DefNum for the special image category specified. Returns 0 if no match found.</summary>
public static long GetImageCat(ImageCategorySpecial specialCat)
{
//No need to check RemotingRole; no call to db.
Def def = Defs.GetDefsForCategory(DefCat.ImageCats, true).FirstOrDefault(x => x.ItemValue.Contains(specialCat.ToString()));
return(def == null ? 0 : def.DefNum);
}
///<summary>Returns the named def. If it can't find the name, then it returns the first def in the category.</summary>
public static long GetByExactNameNeverZero(DefCat myCat, string itemName)
{
//No need to check RemotingRole; no call to db.
List <Def> listDefs = Defs.GetDefsForCategory(myCat);
Def def;
//We have been getting bug submissions from customers where listDefs will be null (e.g. DefCat.ProviderSpecialties cat itemName "General")
//Therefore, we should check for null or and entirely empty category first before looking for a match.
if (listDefs == null || listDefs.Count == 0)
{
//There are no defs for the category passed in, create one because this method should never return zero.
def = new Def();
def.Category = myCat;
def.ItemOrder = 0;
def.ItemName = itemName;
Defs.Insert(def);
Defs.RefreshCache();
return(def.DefNum);
}
//From this point on, we know our list of definitions contains at least one def.
def = listDefs.FirstOrDefault(x => x.ItemName == itemName);
if (def != null)
{
return(def.DefNum);
}
//Couldn't find a match so return the first definition from our list as a last resort.
return(listDefs[0].DefNum);
}
///<summary>Grouped by Category. Used only in FormRpProcCodes.</summary>
public static ProcedureCode[] GetProcList(Def[][] arrayDefs = null)
{
//No need to check RemotingRole; no call to db.
List <ProcedureCode> retVal = new List <ProcedureCode>();
Def[] array = null;
if (arrayDefs == null)
{
array = Defs.GetDefsForCategory(DefCat.ProcCodeCats, true).ToArray();
}
else
{
array = arrayDefs[(int)DefCat.ProcCodeCats];
}
List <ProcedureCode> listProcedureCodes = GetListDeep();
for (int j = 0; j < arrayDefs[(int)DefCat.ProcCodeCats].Length; j++)
{
for (int k = 0; k < listProcedureCodes.Count; k++)
{
if (arrayDefs[(int)DefCat.ProcCodeCats][j].DefNum == listProcedureCodes[k].ProcCat)
{
retVal.Add(listProcedureCodes[k].Copy());
}
}
}
return(retVal.ToArray());
}
///<summary>Returns Color.White if no match found.</summary>
public static Color GetColor(DefCat myCat, long myDefNum)
{
//No need to check RemotingRole; no call to db.
Def def = Defs.GetDefsForCategory(myCat).LastOrDefault(x => x.DefNum == myDefNum);
return(def == null ? Color.White : def.ItemColor);
}
///<summary>Returns empty string if no match found.</summary>
public static string GetValue(DefCat myCat, long myDefNum)
{
//No need to check RemotingRole; no call to db.
Def def = Defs.GetDefsForCategory(myCat).LastOrDefault(x => x.DefNum == myDefNum);
return(def == null ? "" : def.ItemValue);
}
///<summary>Sets the FieldName for each SheetFieldDef in sheetDef.SheetFieldDefs to the Def.DefNum defined as the Patient Image definition.
///Defaults to the first definition in the Image category if Patient Image is not defined.
///This is necessary because the resource for the internal sheet likely does not contain a valid Def primary key.</summary>
public static void SetPatImageFieldNames(SheetDef sheetDef)
{
//We need to figure out which Image Category should be used for any PatImage SheetFieldDefs.
List <Def> listImageDefs = Defs.GetDefsForCategory(DefCat.ImageCats, true);
long defNum = 0;
//A user can define a specific image category as being the Patient Picture definition, see FormDefEditImages.butOK_Click().
//SheetFieldDef.FieldName corresponds to Def.DefNum for a PatImage type SheetFieldDef.
Def def = listImageDefs.FirstOrDefault(x => x.ItemValue.Contains("P"));
if (def == null)
{
def = listImageDefs.FirstOrDefault(); //Default to the first image category definition if one isn't defined as the Patient Image definition.
}
if (def == null) //No Image Category definitions setup.
{
defNum = 0;
}
else
{
defNum = def.DefNum;
}
foreach (SheetFieldDef sheetFieldDef in sheetDef.SheetFieldDefs)
{
if (sheetFieldDef.FieldType != SheetFieldType.PatImage)
{
continue;
}
sheetFieldDef.FieldName = POut.Long(defNum);
}
}
///<summary>Returns the payment type string for the payment.</summary>
public static string GetPaymentTypeDesc(Payment payment, List <Def> listPayTypes = null)
{
if (listPayTypes == null)
{
listPayTypes = Defs.GetDefsForCategory(DefCat.PaymentTypes);
}
return(payment.PayType == 0 ? "Income Transfer" : Defs.GetName(DefCat.PaymentTypes, payment.PayType, listPayTypes));
}
///<summary>Gets list of all appointment type specific DefLinks associated to the WebSchedNewPatApptTypes definition category.</summary>
public static List <DefLink> GetDefLinksForWebSchedNewPatApptApptTypes()
{
//No need to check RemotingRole; no call to db.
//Get all definitions that are associated to the WebSchedNewPatApptTypes category that are linked to an operatory.
List <Def> listWSNPAATDefs = Defs.GetDefsForCategory(DefCat.WebSchedNewPatApptTypes); //Cannot hide defs of this category at this time.
//Return all of the deflinks that are of type Operatory in order to get the operatory specific deflinks.
return(DefLinks.GetDefLinksByTypeAndDefs(DefLinkType.AppointmentType, listWSNPAATDefs.Select(x => x.DefNum).ToList()));
}
///<summary>Returns true if there are any entries in definition that do not have a Category named "General".
///Returning false means the user has ProcButtonCategory customizations.</summary>
public static bool HasCustomCategories()
{
List <Def> listDefs = Defs.GetDefsForCategory(DefCat.ProcButtonCats);
foreach (Def defCur in listDefs)
{
if (!defCur.ItemName.Equals("General"))
{
return(true);
}
}
return(false);
}
///<summary>Returns 0 if it can't find the named def. If the name is blank, then it returns the first def in the category.</summary>
public static long GetByExactName(DefCat myCat, string itemName)
{
//No need to check RemotingRole; no call to db.
List <Def> listDefs = Defs.GetDefsForCategory(myCat);
//jsalmon - The following line doesn't make much sense because the def list could be empty but this is preserving old behavior...
if (itemName == "")
{
return(listDefs[0].DefNum); //return the first one in the list
}
Def def = listDefs.FirstOrDefault(x => x.ItemName == itemName);
return(def == null ? 0 : def.DefNum);
}
public static DataTable GetActiveJobsForUser(long UserNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), UserNum));
}
string command = "SELECT Priority, DateTimeEntry AS 'Date Entered', PhaseCur AS 'Phase', Title"
+ " FROM job"
+ " WHERE UserNumEngineer=" + UserNum + " AND PhaseCur NOT IN"
+ "('" + POut.String(JobStatus.Complete.ToString()) + "','" + POut.String(JobStatus.Cancelled.ToString()) + "','"
+ POut.String(JobStatus.Documentation.ToString()) + "')"
+ " AND Priority!='" + POut.Long(Defs.GetDefsForCategory(DefCat.JobPriorities, true).First(x => x.ItemValue.Contains("OnHold")).DefNum) + "'";
return(Db.GetTable(command));
}
///<Summary>Returns a defnum. If no match, then it returns the first one in the list in that category.
///If there are no defs in the category, 0 is returned.</Summary>
public static long GetTypeAuto(CommItemTypeAuto typeauto)
{
//No need to check RemotingRole; no call to db.
List <Def> listDefs = Defs.GetDefsForCategory(DefCat.CommLogTypes);
Def def = listDefs.FirstOrDefault(x => x.ItemValue == typeauto.ToString());
if (def != null)
{
return(def.DefNum);
}
if (listDefs.Count > 0)
{
return(listDefs[0].DefNum);
}
return(0);
}
///<summary>Will return null if no picture for this patient.</summary>
public static Document GetPatPictFromDb(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <Document>(MethodBase.GetCurrentMethod(), patNum));
}
//first establish which category pat pics are in
long defNumPicts = 0;
Def[] defs = Defs.GetDefsForCategory(DefCat.ImageCats, true).ToArray();
for (int i = 0; i < defs.Length; i++)
{
if (Regex.IsMatch(defs[i].ItemValue, @"P"))
{
defNumPicts = defs[i].DefNum;
break;
}
}
if (defNumPicts == 0) //no category set for picts
{
return(null);
}
//then find, limit 1 to get the most recent
string command = "SELECT * FROM document "
+ "WHERE document.PatNum=" + POut.Long(patNum)
+ " AND document.DocCategory=" + POut.Long(defNumPicts)
+ " ORDER BY DateCreated DESC";
command = DbHelper.LimitOrderBy(command, 1);
DataTable table = Db.GetTable(command);
Document[] pictureDocs = Fill(table);
if (pictureDocs == null || pictureDocs.Length < 1) //no pictures
{
return(null);
}
return(pictureDocs[0]);
}
///<summary>Get all provider operatory availabilities for passed in data.</summary>
private static List <ApptSearchOperatorySchedule> GetAllForDate(DateTime scheduleDate, List <Schedule> listSchedules
, List <Appointment> listAppointments, List <ScheduleOp> listSchedOps, List <long> listOpNums, List <long> listProvNums, long blockoutType)
{
//No need to check RemotingRole; no call to db.
List <ApptSearchOperatorySchedule> listOpScheds = new List <ApptSearchOperatorySchedule>();
List <Operatory> listOps = Operatories.GetWhere(x => x.OperatoryNum.In(listOpNums));
//Remove any ScheduleOps that are not related to the operatories passed in.
listSchedOps.RemoveAll(x => !listOpNums.Contains(x.OperatoryNum));
//Create dictionaries that are comprised of every operatory in question and will keep track of all ProviderNums for specific scenarios.
Dictionary <long, List <long> > dictProvNumsInOpsBySched = listOps.ToDictionary(x => x.OperatoryNum, x => new List <long>());
Dictionary <long, List <long> > dictProvNumsInOpsByOp = listOps.ToDictionary(x => x.OperatoryNum,
x => new List <long>()
{
x.ProvDentist, x.ProvHygienist
}); //Could be a list of two 0's if no providers are associated to this op.
scheduleDate = scheduleDate.Date; //remove time component
foreach (long opNum in listOpNums)
{
ApptSearchOperatorySchedule apptSearchOpSched = new ApptSearchOperatorySchedule();
apptSearchOpSched.SchedDate = scheduleDate;
apptSearchOpSched.ProviderNums = new List <long>();
apptSearchOpSched.OperatoryNum = opNum;
apptSearchOpSched.IsOpAvailable = new bool[288];
for (int j = 0; j < 288; j++)
{
apptSearchOpSched.IsOpAvailable[j] = true; //Set entire operatory schedule to true. True=available.
}
listOpScheds.Add(apptSearchOpSched);
}
#region Fill OpScheds with Providers allowed to work in each operatory
//Make explicit entries into dictProvNumsInOpsBySched if there are any SchedOps for each schedule OR add an entry to every operatory if none found.
foreach (Schedule schedule in listSchedules.FindAll(x => x.SchedDate == scheduleDate)) //use this loop to fill listProvsInOpBySched
{
List <ScheduleOp> listSchedOpsForSchedule = listSchedOps.FindAll(x => x.ScheduleNum == schedule.ScheduleNum);
if (listSchedOpsForSchedule.Count > 0)
{
AddProvNumToOps(dictProvNumsInOpsBySched,
listSchedOpsForSchedule.Select(x => x.OperatoryNum).Distinct().ToList(),
schedule.ProvNum);
}
else //Provider scheduled to work, but not limited to specific operatory to add providerNum to all ops in opsProvPerSchedules
{
AddProvNumToOps(dictProvNumsInOpsBySched,
dictProvNumsInOpsBySched.Keys.ToList(),
schedule.ProvNum);
}
}
//Set each listOpScheds.ProviderNums to the corresponding providers via operatory OR schedules.
foreach (Operatory op in listOps)
{
//If blockoutType is not 0 and 0 is only provNum in listProvNums, we are just looking for blockout schedules in ops.
//Add zero to ProviderNums list for op if op has any blockout for the date we are searching. Unwanted blockouts are filtered out below.
if (blockoutType > 0 && listProvNums.Max() == 0 && dictProvNumsInOpsBySched[op.OperatoryNum].Contains(0))
{
listOpScheds.First(x => x.OperatoryNum == op.OperatoryNum).ProviderNums.Add(0);
}
//If the operatory does not have a primary and secondary provider use all providers from the schedules.
else if (dictProvNumsInOpsByOp[op.OperatoryNum][0] == 0 && dictProvNumsInOpsByOp[op.OperatoryNum][1] == 0)
{
listOpScheds.First(x => x.OperatoryNum == op.OperatoryNum).ProviderNums = dictProvNumsInOpsBySched[op.OperatoryNum];
}
else //Otherwise; only add providers that intersect between schedules and being explicitly assigned to an operatory.
{
List <long> listIntersectingProvNums = dictProvNumsInOpsBySched[op.OperatoryNum].Intersect(dictProvNumsInOpsByOp[op.OperatoryNum]).ToList();
if (listIntersectingProvNums.Count() > 0)
{
listOpScheds.First(x => x.OperatoryNum == op.OperatoryNum).ProviderNums.AddRange(listIntersectingProvNums);
}
}
}
#endregion
#region Remove provider availability for current appointments
List <Appointment> listAppointmentsForDate = listAppointments.FindAll(x => x.Op != 0 && x.AptDateTime.Date == scheduleDate);
foreach (Appointment appt in listAppointmentsForDate) //Remove unavailable slots from schedule
{
ApptSearchOperatorySchedule apptSearchOperatorySchedule = listOpScheds.FirstOrDefault(x => x.OperatoryNum == appt.Op);
if (apptSearchOperatorySchedule == null)
{
continue;
}
int apptStartIndex = (int)appt.AptDateTime.TimeOfDay.TotalMinutes / 5;
for (int j = 0; j < appt.Pattern.Length; j++) //make unavailable all blocks of time during this appointment
{
apptSearchOperatorySchedule.IsOpAvailable[apptStartIndex + j] = false; //set time block to false, meaning something is scheduled here
}
}
#endregion
#region Remove provider availiabilty for blockouts set to Do Not Schedule
List <long> listBlockoutsDoNotSchedule = new List <long>();
List <Def> listBlockoutsAll = Defs.GetDefsForCategory(DefCat.BlockoutTypes, true);
foreach (Def blockout in listBlockoutsAll)
{
if (blockout.ItemValue.Contains(BlockoutType.NoSchedule.GetDescription()))
{
listBlockoutsDoNotSchedule.Add(blockout.DefNum); //do not return results for blockouts set to 'Do Not Schedule'
continue;
}
if (blockoutType != 0 && blockoutType != blockout.DefNum)
{
listBlockoutsDoNotSchedule.Add(blockout.DefNum); //do not return results for blockouts that are not of our requested type
}
}
if (listBlockoutsDoNotSchedule.Count > 0)
{
List <Schedule> listBlockouts = listSchedules.FindAll(x => x.ProvNum == 0 && x.SchedType == ScheduleType.Blockout && x.SchedDate == scheduleDate &&
x.BlockoutType.In(listBlockoutsDoNotSchedule));
foreach (Schedule blockout in listBlockouts)
{
//get length of blockout (how many 5 minute increments does it span)
TimeSpan duration = blockout.StopTime.Subtract(blockout.StartTime);
double fiveMinuteIncrements = Math.Ceiling(duration.TotalMinutes / 5);
int blockoutStartIndex = (int)blockout.StartTime.TotalMinutes / 5;
//Set each operatory as unavailable that has this blockout.
List <ScheduleOp> listSchedOpsForBlockout = listSchedOps.FindAll(x => x.ScheduleNum == blockout.ScheduleNum);
foreach (ScheduleOp schedOp in listSchedOpsForBlockout)
{
ApptSearchOperatorySchedule apptSearchOperatorySchedule = listOpScheds.FirstOrDefault(x => x.OperatoryNum == schedOp.OperatoryNum);
if (apptSearchOperatorySchedule == null)
{
continue;
}
for (int i = 0; i < fiveMinuteIncrements; i++)
{
apptSearchOperatorySchedule.IsOpAvailable[blockoutStartIndex + i] = false;
}
}
}
}
#endregion
//Return all ApptSearchOperatorySchedules for the providers passed in.
return(listOpScheds.FindAll(x => x.ProviderNums.Any(y => y.In(listProvNums))));
}
///<summary>Used to check whether codes starting with T exist and are in a visible category. If so, it moves them to the Obsolete category. If the T code has never been used, then it deletes it.</summary>
public static void TcodesClear()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod());
return;
}
//first delete any unused T codes
string command = @"SELECT CodeNum,ProcCode FROM procedurecode
WHERE CodeNum NOT IN(SELECT CodeNum FROM procedurelog)
AND CodeNum NOT IN(SELECT CodeNum FROM autocodeitem)
AND CodeNum NOT IN(SELECT CodeNum FROM procbuttonitem)
AND CodeNum NOT IN(SELECT CodeNum FROM recalltrigger)
AND CodeNum NOT IN(SELECT CodeNum FROM benefit)
AND ProcCode NOT IN(SELECT CodeValue FROM encounter WHERE CodeSystem='CDT')
AND ProcCode LIKE 'T%'" ;
DataTable table = Db.GetTable(command);
List <long> listCodeNums = new List <long>();
List <string> listRecallCodes = RecallTypes.GetDeepCopy()
.SelectMany(x => x.Procedures.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
.ToList();
for (int i = 0; i < table.Rows.Count; i++)
{
if (!listRecallCodes.Contains(PIn.String(table.Rows[i]["ProcCode"].ToString()))) //The ProcCode is not attached to a recall type.
{
listCodeNums.Add(PIn.Long(table.Rows[i]["CodeNum"].ToString()));
}
}
if (listCodeNums.Count > 0)
{
ProcedureCodes.ClearFkey(listCodeNums); //Zero securitylog FKey column for rows to be deleted.
command = "SELECT FeeNum FROM fee WHERE CodeNum IN(" + String.Join(",", listCodeNums) + ")";
List <long> listFeeNums = Db.GetListLong(command);
Fees.DeleteMany(listFeeNums);
command = "DELETE FROM proccodenote WHERE CodeNum IN(" + String.Join(",", listCodeNums) + ")";
Db.NonQ(command);
command = "DELETE FROM procedurecode WHERE CodeNum IN(" + String.Join(",", listCodeNums) + ")";
Db.NonQ(command);
}
//then, move any other T codes to obsolete category
command = @"SELECT DISTINCT ProcCat FROM procedurecode,definition
WHERE procedurecode.ProcCode LIKE 'T%'
AND definition.IsHidden=0
AND procedurecode.ProcCat=definition.DefNum" ;
table = Db.GetTable(command);
long catNum = Defs.GetByExactName(DefCat.ProcCodeCats, "Obsolete"); //check to make sure an Obsolete category exists.
Def def;
if (catNum != 0) //if a category exists with that name
{
def = Defs.GetDef(DefCat.ProcCodeCats, catNum);
if (!def.IsHidden)
{
def.IsHidden = true;
Defs.Update(def);
Defs.RefreshCache();
}
}
if (catNum == 0)
{
List <Def> listDefs = Defs.GetDefsForCategory(DefCat.ProcCodeCats);
def = new Def();
def.Category = DefCat.ProcCodeCats;
def.ItemName = "Obsolete";
def.ItemOrder = listDefs.Count;
def.IsHidden = true;
Defs.Insert(def);
Defs.RefreshCache();
catNum = def.DefNum;
}
for (int i = 0; i < table.Rows.Count; i++)
{
command = "UPDATE procedurecode SET ProcCat=" + POut.Long(catNum)
+ " WHERE ProcCat=" + table.Rows[i][0].ToString()
+ " AND procedurecode.ProcCode LIKE 'T%'";
Db.NonQ(command);
}
//finally, set Never Used category to be hidden. This isn't really part of clearing Tcodes, but is required
//because many customers won't have that category hidden
catNum = Defs.GetByExactName(DefCat.ProcCodeCats, "Never Used");
if (catNum != 0) //if a category exists with that name
{
def = Defs.GetDef(DefCat.ProcCodeCats, catNum);
if (!def.IsHidden)
{
def.IsHidden = true;
Defs.Update(def);
Defs.RefreshCache();
}
}
}
///<summary>Returns defs from the AdjTypes that contain 'dp' in the ItemValue column.</summary>
public static List <Def> GetDiscountPlanAdjTypes()
{
//No need to check RemotingRole; no call to db.
return(Defs.GetDefsForCategory(DefCat.AdjTypes, true).FindAll(x => x.ItemValue == "dp"));
}
///<summary>Gets the order of the def within Short or -1 if not found.</summary>
public static int GetOrder(DefCat myCat, long myDefNum)
{
//No need to check RemotingRole; no call to db.
//gets the index in the list of unhidden (the Short list).
return(Defs.GetDefsForCategory(myCat, true).FindIndex(x => x.DefNum == myDefNum));
}
///<summary>Get one def from Long. Returns null if not found. Only used for very limited situations.
///Other Get functions tend to be much more useful since they don't return null.
///There is also BIG potential for silent bugs if you use this.ItemOrder instead of GetOrder().</summary>
public static Def GetDef(DefCat myCat, long myDefNum, List <Def> listDefs = null)
{
//No need to check RemotingRole; no call to db.
listDefs = listDefs ?? Defs.GetDefsForCategory(myCat);
return(listDefs.FirstOrDefault(x => x.DefNum == myDefNum));
}
///<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);
}