public static DataTable GetDictAmtPlanned(bool patsWithAppts, DateTime dateSince, List <long> listProvNums, List <long> listBillTypes,
string code1, string code2, List <long> listClinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), patsWithAppts, dateSince, listProvNums, listBillTypes, code1, code2, listClinicNums));
}
string command = $@"SELECT procedurelog.PatNum,SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)) AmtPlanned
FROM procedurelog
INNER JOIN patient ON patient.PatNum=procedurelog.PatNum{(string.IsNullOrEmpty(code1)?"":$@"
INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum")}
WHERE procedurelog.ProcStatus={(int)ProcStat.TP}
AND patient.PatStatus={POut.Int((int)PatientStatus.Patient)}{(string.IsNullOrEmpty(code1)?"":$@"
AND procedurecode.ProcCode>='{POut.String(code1)}'
AND procedurecode.ProcCode<='{POut.String(code2)}'")}{(dateSince.Year<=1880?"":$@"
AND procedurelog.DateTP>={POut.DateT(dateSince)}")}{(listProvNums.IsNullOrEmpty() || listProvNums.Contains(0)?"":$@"
AND patient.PriProv IN ({string.Join(",",listProvNums)})")}{(listBillTypes.IsNullOrEmpty() || listBillTypes.Contains(0)?"":$@"
AND patient.BillingType IN ({string.Join(",",listBillTypes)})")}{(!PrefC.HasClinicsEnabled || listClinicNums.IsNullOrEmpty()?"":$@"
AND patient.ClinicNum IN ({string.Join(",",listClinicNums)})")}{(patsWithAppts?"":$@"
AND procedurelog.PatNum NOT IN (
SELECT PatNum FROM appointment
WHERE appointment.AptStatus={POut.Int((int)ApptStatus.Scheduled)}
AND appointment.AptDateTime>={DbHelper.Curdate()})")}
GROUP BY procedurelog.PatNum
HAVING AmtPlanned>0
ORDER BY NULL" ;//Removes filesort reference from query explain
return(Db.GetTable(command));
}
/// <summary>Adds up the total fees for the procedures passed in that have been completed since the last billing day.</summary>
public static double TotalRecurringCharges(long patNum, string procedures, int billingDay)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDouble(MethodBase.GetCurrentMethod(), patNum, procedures, billingDay));
}
//Find the beginning of the current billing cycle, use that date to total charges between now and then for this cycle only.
//Include that date only when we are not on the first day of the current billing cycle.
DateTime startBillingCycle;
if (DateTime.Today.Day > billingDay) //if today is 7/13/2015 and billingDay is 26, startBillingCycle will be 6/26/2015
{
startBillingCycle = new DateTime(DateTime.Today.Year, DateTime.Today.Month, billingDay);
}
else
{
//DateTime.Today.AddMonths handles the number of days in the month and leap years
//Examples: if today was 12/31/2015, AddMonths(-1) would yield 11/30/2015; if today was 3/31/2016, AddMonths(-1) would yield 2/29/2016
startBillingCycle = DateTime.Today.AddMonths(-1);
if (billingDay <= DateTime.DaysInMonth(startBillingCycle.Year, startBillingCycle.Month))
{
//This corrects the issue of a billing cycle day after today but this month doesn't have enough days when last month does
//Example: if today was 11/30/2015 and the pat's billing cycle day was the 31st, startBillingCycle=Today.AddMonths(-1) would be 10/30/2015.
//But this pat's billing cycle day is the 31st and the December has 31 days. This adjusts the start of the billing cycle to 10/31/2015.
//Example 2: if today was 2/29/2016 (leap year) and the pat's billing cycle day was the 30th, startBillingCycle should be 1/30/2016.
//Today.AddMonths(-1) would be 1/29/2016, so this adjusts startBillingCycle to 1/30/2016.
startBillingCycle = new DateTime(startBillingCycle.Year, startBillingCycle.Month, billingDay);
}
}
string procStr = "'" + POut.String(procedures).Replace(",", "','") + "'";
string command = "SELECT SUM(pl.ProcFee) "
+ "FROM procedurelog pl "
+ "INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum "
+ "WHERE pl.ProcStatus=2 "
+ "AND pc.ProcCode IN (" + procStr + ") "
+ "AND pl.PatNum=" + POut.Long(patNum) + " "
+ "AND pl.ProcDate<=" + DbHelper.Curdate() + " ";
//If today is the billingDay or today is the last day of the current month and the billingDay is greater than today
//i.e. billingDay=31 and today is the 30th which is the last day of the current month, only count procs with date after the 31st of last month
if (billingDay == DateTime.Today.Day ||
(billingDay > DateTime.Today.Day &&
DateTime.Today.Day == DateTime.DaysInMonth(DateTime.Today.Year, DateTime.Today.Month)))
{
command += "AND pl.ProcDate>" + POut.Date(startBillingCycle);
}
else
{
command += "AND pl.ProcDate>=" + POut.Date(startBillingCycle);
}
return(PIn.Double(Db.GetScalar(command)));
}
/// <summary>Returns true if the procedure passed in is linked to any other active card on the patient's account.</summary>
public static bool ProcLinkedToCard(long patNum, string procCode, long cardNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), patNum, procCode, cardNum));
}
string command = "SELECT CreditCardNum,Procedures "
+ "FROM creditcard "
+ "WHERE PatNum=" + POut.Long(patNum) + " "
+ "AND DateStart<=" + DbHelper.Curdate() + " AND " + DbHelper.Year("DateStart") + ">1880 "
+ "AND (DateStop>=" + DbHelper.Curdate() + " OR " + DbHelper.Year("DateStop") + "<1880) "
+ "AND CreditCardNum!=" + POut.Long(cardNum);
DataTable table = Db.GetTable(command);
return(table.Rows.OfType <DataRow>().SelectMany(x => x["Procedures"].ToString().Split(',')).Any(x => x == procCode));
}
///<summary>Returns true if there are any active repeating charges on the patient's account, false if there are not.</summary>
public static bool ActiveRepeatChargeExists(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), patNum));
}
//Counts the number of repeat charges that a patient has with a valid start date in the past and no stop date or a stop date in the future
string command = "SELECT COUNT(*) FROM repeatcharge "
+ "WHERE PatNum=" + POut.Long(patNum) + " AND DateStart BETWEEN '1880-01-01' AND " + DbHelper.Curdate() + " "
+ "AND (DateStop='0001-01-01' OR DateStop>=" + DbHelper.Curdate() + ")";
if (Db.GetCount(command) == "0")
{
return(false);
}
return(true);
}
///<summary>Gets all payplan charges for the payplans passed in where the specified patient is the Guarantor. Based on today's date.
///Will return both credits and debits. Does not return insurance payment plan charges.</summary>
public static List <PayPlanCharge> GetDueForPayPlans(List <PayPlan> listPayPlans, long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <PayPlanCharge> >(MethodBase.GetCurrentMethod(), listPayPlans, patNum));
}
if (listPayPlans.Count < 1)
{
return(new List <PayPlanCharge>());
}
string command = "SELECT payplancharge.* FROM payplan "
+ "INNER JOIN payplancharge ON payplancharge.PayPlanNum = payplan.PayPlanNum "
+ "AND payplancharge.ChargeDate <= " + DbHelper.Curdate() + " "
+ "WHERE payplan.Guarantor=" + POut.Long(patNum) + " "
+ "AND payplan.PayPlanNum IN(" + String.Join(", ", listPayPlans.Select(x => x.PayPlanNum).ToList()) + ") "
+ "AND payplan.PlanNum = 0 "; //do not return insurance payment plan charges.
return(Crud.PayPlanChargeCrud.SelectMany(command));
}
///<summary>Gets all payplan charges for the payplans passed in where the any of the patients in the list are the Guarantor or the patient on the
///payment plan. Based on today's date. Will return both credits and debits. Does not return insurance payment plan charges.</summary>
public static List <PayPlanCharge> GetDueForPayPlans(List <long> listPayPlans, List <long> listPatNums)
{
if (listPayPlans.IsNullOrEmpty() || listPatNums.IsNullOrEmpty())
{
return(new List <PayPlanCharge>());
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <PayPlanCharge> >(MethodBase.GetCurrentMethod(), listPayPlans, listPatNums));
}
string command = "SELECT payplancharge.* FROM payplan "
+ "INNER JOIN payplancharge ON payplancharge.PayPlanNum = payplan.PayPlanNum "
+ "AND payplancharge.ChargeDate <= " + DbHelper.Curdate() + " "
+ "WHERE payplan.PatNum IN(" + string.Join(",", listPatNums) + ") OR payplan.Guarantor IN(" + string.Join(",", listPatNums) + ") "
+ "AND payplan.PayPlanNum IN(" + string.Join(", ", listPayPlans) + ") "
+ "AND payplan.PlanNum = 0 "; //do not return insurance payment plan charges.
return(Crud.PayPlanChargeCrud.SelectMany(command));
}
///<summary>Normally, includeDiscontinued is false. User needs to check a box to include discontinued.</summary>
public static List <MedicationPat> Refresh(long patNum, bool includeDiscontinued)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <MedicationPat> >(MethodBase.GetCurrentMethod(), patNum, includeDiscontinued));
}
string command = "SELECT * FROM medicationpat WHERE PatNum = " + POut.Long(patNum);
if (includeDiscontinued) //this only happens when user checks box to show discontinued or for MU.
//no restriction on DateStop
{
}
else //exclude discontinued. This is the default.
{
command += " AND (DateStop < " + POut.Date(new DateTime(1880, 1, 1)) //include all the meds that are not discontinued.
+ " OR DateStop >= ";
command += DbHelper.Curdate() + ")"; //Show medications that are today or a future stopdate - they are not yet discontinued.
}
return(Crud.MedicationPatCrud.SelectMany(command));
}
private static List <Appointment> GetAppointmentsToSendReview(ReviewInvitationTrigger trigger, long programNum, bool isNewPatient)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Appointment> >(MethodBase.GetCurrentMethod(), trigger, programNum, isNewPatient));
}
string minutesToWaitCompleted = ProgramProperties.GetPropVal(programNum, PropertyDescs.ApptSetCompletedMinutes);
string minutesToWaitTimeArrived = ProgramProperties.GetPropVal(programNum, PropertyDescs.ApptTimeArrivedMinutes);
string minutesToWaitTimeDismissed = ProgramProperties.GetPropVal(programNum, PropertyDescs.ApptTimeDismissedMinutes);
string command = "SELECT * "
+ "FROM appointment "
+ "LEFT JOIN securitylog ON securitylog.FKey=appointment.AptNum "
+ "AND securitylog.PermType=" + POut.Int((int)Permissions.AppointmentEdit) + " AND securitylog.LogText LIKE '%Set Complete%' "
+ "LEFT JOIN commlog ON commlog.PatNum=appointment.PatNum "
+ "AND commlog.CommSource=" + POut.Int((int)CommItemSource.ProgramLink) + " "
+ "AND DATE(commlog.DateTimeEnd)=" + DbHelper.Curdate() + " "
+ "AND commlog.ProgramNum=" + POut.Long(programNum) + " "
+ "WHERE ISNULL(commlog.PatNum) AND appointment.AptDateTime BETWEEN " + DbHelper.Curdate() + " AND " + DbHelper.Now() + " + INTERVAL 1 HOUR "//Hard code an hour to allow for appointments that have an early DateTimeArrived
+ "AND appointment.IsNewPatient=" + POut.Bool(isNewPatient) + " ";
if (trigger == ReviewInvitationTrigger.AppointmentCompleted)
{
command += "AND appointment.AptStatus=" + POut.Int((int)ApptStatus.Complete) + " "
+ "AND NOT ISNULL(securitylog.PatNum) "
+ "AND securitylog.LogDateTime + INTERVAL " + minutesToWaitCompleted + " MINUTE <=" + DbHelper.Now() + " ";
}
else if (trigger == ReviewInvitationTrigger.AppointmentTimeArrived)
{
command += "AND appointment.AptStatus IN (" + POut.Int((int)ApptStatus.Scheduled) + "," + POut.Int((int)ApptStatus.Complete) + ") "
+ "AND ((appointment.AptStatus=" + POut.Int((int)ApptStatus.Complete) + " AND NOT ISNULL(securitylog.PatNum) AND securitylog.LogDateTime + INTERVAL " + minutesToWaitCompleted + " MINUTE <=" + DbHelper.Now() + ") "
+ "OR (appointment.DateTimeArrived>" + DbHelper.Curdate() + " AND appointment.DateTimeArrived + INTERVAL " + minutesToWaitTimeArrived + " MINUTE<=" + DbHelper.Now() + ")) ";
}
else if (trigger == ReviewInvitationTrigger.AppointmentTimeDismissed)
{
command += "AND appointment.AptStatus IN (" + POut.Int((int)ApptStatus.Scheduled) + "," + POut.Int((int)ApptStatus.Complete) + ") "
+ "AND ((appointment.AptStatus=" + POut.Int((int)ApptStatus.Complete) + " AND NOT ISNULL(securitylog.PatNum) AND securitylog.LogDateTime + INTERVAL 90 MINUTE <=" + DbHelper.Now() + ") "
+ "OR (appointment.DateTimeDismissed>" + DbHelper.Curdate() + " AND appointment.DateTimeDismissed + INTERVAL " + minutesToWaitTimeDismissed + " MINUTE<=" + DbHelper.Now() + ")) ";
}
return(Crud.AppointmentCrud.SelectMany(command));
}
///<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)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), noIns, patsWithAppts, monthStart, dateSince, aboveAmount, listProviders, listBilling, code1, code2,
listClinicNums, isProcsGeneral));
}
#if DEBUG
Stopwatch sw = Stopwatch.StartNew();
#endif
DataTable table = new DataTable();
DataRow row;
//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");
List <DataRow> rows = new List <DataRow>();
string command = "";
string joinAnnualMax = "";
string joinCoverageInfo = "";
string joinIndInfo = "";
string joinFamInfo = "";
string subSelectPlanned = "";
string cmdFutureApt = @" AND patient.PatNum NOT IN (
SELECT PatNum FROM appointment WHERE AptStatus=" + POut.Int((int)ApptStatus.Scheduled) + @"
AND AptDateTime>=" + DbHelper.Curdate() + ")";
DateTime renewDate = BenefitLogic.ComputeRenewDate(DateTime.Now, monthStart);
List <long> listPatNums = new List <long>();
if ((!listProviders.Contains(0) || !listBilling.Contains(0) || listClinicNums.Count > 0))
{
string cmdPatients = "SELECT PatNum from patient ";
string patWhere = "";
if (!listProviders.Contains(0))
{
patWhere = " AND patient.PriProv IN (" + string.Join(",", listProviders) + ") ";
}
if (!listBilling.Contains(0))
{
patWhere = " AND patient.BillingType IN (" + string.Join(",", listBilling) + ") ";
}
if (listClinicNums.Count > 0)
{
patWhere += " AND patient.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (!patsWithAppts)
{
patWhere += cmdFutureApt;
}
cmdPatients += "WHERE TRUE " + patWhere;
listPatNums = Db.GetListLong(cmdPatients);
if (listPatNums.Count == 0)
{
return(table);
}
}
joinCoverageInfo = @"
SELECT patplan.PatPlanNum,claimproc.InsSubNum,
SUM(CASE WHEN claimproc.Status=" + POut.Int((int)ClaimProcStatus.NotReceived) + @" AND claimproc.InsPayAmt=0
THEN claimproc.InsPayEst ELSE 0 END) AmtPending,
SUM(CASE WHEN claimproc.Status IN (" + POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Adjustment) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + @"
) THEN claimproc.InsPayAmt ELSE 0 END) AmtUsed
FROM claimproc
INNER JOIN patient ON patient.PatNum=claimproc.PatNum
LEFT JOIN patplan ON patplan.PatNum=claimproc.PatNum
AND patplan.InsSubNum=claimproc.InsSubNum
LEFT JOIN procedurelog pl ON pl.ProcNum=claimproc.ProcNum
LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum " ;
if (!isProcsGeneral)
{
joinCoverageInfo += @"
LEFT JOIN (
SELECT isub.InsSubNum,
COALESCE(cp.FromCode,pc.ProcCode) AS FromCode,
COALESCE(cp.ToCode,pc.ProcCode) AS ToCode
FROM inssub isub
INNER JOIN benefit b ON b.PlanNum=isub.PlanNum
AND b.BenefitType=" + (int)InsBenefitType.Limitations + @"
AND b.QuantityQualifier=" + (int)BenefitQuantity.None + @"
AND b.TimePeriod IN (" + (int)BenefitTimePeriod.ServiceYear + "," + (int)BenefitTimePeriod.CalendarYear + @")
LEFT JOIN covcat cc ON cc.CovCatNum=b.CovCatNum
LEFT JOIN covspan cp ON cp.CovCatNum=cc.CovCatNum
LEFT JOIN procedurecode pc ON pc.CodeNum=b.CodeNum
WHERE (cc.CovCatNum IS NOT NULL OR b.CodeNum!=0)
)ProcCheck ON ProcCheck.InsSubNum=claimproc.InsSubNum
AND pc.ProcCode BETWEEN ProcCheck.FromCode AND ProcCheck.ToCode " ;
}
joinCoverageInfo += "WHERE claimproc.Status IN (" + (int)ClaimProcStatus.NotReceived + ", " + (int)ClaimProcStatus.Received
+ ", " + (int)ClaimProcStatus.Adjustment + ", " + (int)ClaimProcStatus.Supplemental + ") ";
if (!isProcsGeneral)
{
joinCoverageInfo += "AND ProcCheck.InsSubNum IS NULL ";
}
joinCoverageInfo += "AND claimproc.ProcDate BETWEEN " + POut.Date(renewDate) + @" AND " + POut.Date(renewDate.AddYears(1)) + @" ";
if (listPatNums.Count > 0)
{
joinCoverageInfo += @"AND patient.PatNum IN (" + string.Join(",", listPatNums) + ") ";
}
else if (!patsWithAppts)
{
joinCoverageInfo += cmdFutureApt;
}
joinIndInfo = joinCoverageInfo + " GROUP BY patplan.PatPlanNum ";
joinFamInfo = joinCoverageInfo + " GROUP BY claimproc.InsSubNum ";
subSelectPlanned = @"
(SELECT COALESCE(SUM(ProcFee),0) AmtPlanned
FROM procedurelog " ;
if (code1 != "")
{
subSelectPlanned += "INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum ";
}
subSelectPlanned += "WHERE ProcStatus=" + (int)ProcStat.TP + " ";
if (code1 != "")
{
subSelectPlanned += "AND procedurecode.ProcCode>='" + POut.String(code1) + "' "
+ " AND procedurecode.ProcCode<='" + POut.String(code2) + "' ";
}
if (dateSince.Year > 1880)
{
subSelectPlanned += "AND procedurelog.DateTP>=" + POut.DateT(dateSince) + " ";
}
subSelectPlanned += "AND PatNum=patient.PatNum ";
subSelectPlanned += "GROUP BY PatNum) ";
joinAnnualMax = @"
SELECT insplan.PlanNum, MAX(CASE WHEN CoverageLevel!=" + POut.Int((int)BenefitCoverageLevel.Family) + @"
THEN MonetaryAmt ELSE -1 END) AnnualMaxInd/*for oracle in case there's more than one*/,
MAX(CASE WHEN CoverageLevel=" + POut.Int((int)BenefitCoverageLevel.Family) + @"
THEN MonetaryAmt ELSE -1 END) AnnualMaxFam/*for oracle in case there's more than one*/
FROM benefit
INNER JOIN insplan ON insplan.PlanNum=benefit.PlanNum
INNER JOIN inssub ON inssub.PlanNum=benefit.PlanNum
INNER JOIN patplan ON patplan.InsSubNum=inssub.InsSubNum
INNER JOIN patient ON patient.PatNum=patplan.PatNum
LEFT JOIN covcat ON benefit.CovCatNum=covcat.CovCatNum
WHERE (covcat.EbenefitCat=" + (int)EbenefitCategory.General + @" OR ISNULL(covcat.EbenefitCat))
AND benefit.BenefitType=" + (int)InsBenefitType.Limitations + @"
AND benefit.MonetaryAmt > 0
AND benefit.QuantityQualifier=" + (int)BenefitQuantity.None + " ";
if (listPatNums.Count > 0)
{
joinAnnualMax += @"AND patient.PatNum IN (" + string.Join(",", listPatNums) + ") ";
}
else if (!patsWithAppts)
{
joinAnnualMax += cmdFutureApt;
}
joinAnnualMax += @"GROUP BY insplan.PlanNum";
command = @"SELECT patient.PatNum, patient.LName, patient.FName,
patient.Email, patient.HmPhone, patient.PreferRecallMethod,
patient.WirelessPhone, patient.WkPhone, patient.Address,
patient.Address2, patient.City, patient.State, patient.Zip,
patient.PriProv, patient.BillingType,
COALESCE(annualMax.AnnualMaxInd,0) ""AnnualMaxInd"",
COALESCE(annualMax.AnnualMaxFam,0) ""AnnualMaxFam"",
IndividualInfo.AmtUsed ""AmountUsedInd"",
FamilyInfo.AmtUsed ""AmountUsedFam"",
IndividualInfo.AmtPending ""AmountPendingInd"",
FamilyInfo.AmtPending ""AmountPendingFam"",
COALESCE(annualMax.AnnualMaxInd,0)-COALESCE(IndividualInfo.AmtUsed,0)-COALESCE(IndividualInfo.AmtPending,0) AS ""$AmtRemainingInd"",
COALESCE(annualMax.AnnualMaxFam,0)-COALESCE(FamilyInfo.AmtUsed,0)-COALESCE(FamilyInfo.AmtPending,0) AS ""$AmtRemainingFam""," +
subSelectPlanned + @"""$TreatmentPlan"", carrier.CarrierName,COALESCE(clinic.Abbr,'Unassigned') clinicAbbr
FROM patient
LEFT JOIN patplan ON patient.PatNum=patplan.PatNum
LEFT JOIN inssub ON patplan.InsSubNum=inssub.InsSubNum
LEFT JOIN insplan ON insplan.PlanNum=inssub.PlanNum
LEFT JOIN carrier ON insplan.CarrierNum=carrier.CarrierNum
LEFT JOIN ("
+ joinIndInfo
+ @")IndividualInfo ON IndividualInfo.PatPlanNum=patplan.PatPlanNum
LEFT JOIN ("
+ joinFamInfo
+ @")FamilyInfo ON FamilyInfo.InsSubNum=inssub.InsSubNum
LEFT JOIN ("
+ joinAnnualMax
+ @") annualMax ON annualMax.PlanNum=inssub.PlanNum
AND (annualMax.AnnualMaxInd>0 OR annualMax.AnnualMaxFam>0)/*may not be necessary*/
LEFT JOIN clinic ON clinic.ClinicNum=patient.ClinicNum
WHERE TRUE
AND patient.PatStatus=" + POut.Int((int)PatientStatus.Patient) + " ";
if (!noIns) //if we don't want patients without insurance
{
command += " AND patplan.Ordinal=1 AND insplan.MonthRenew=" + POut.Int(monthStart) + " ";
}
if (aboveAmount > 0)
{
command += " AND (annualMax.PlanNum IS NULL OR ((annualMax.AnnualMaxInd=-1 OR annualMax.AnnualMaxInd-COALESCE(IndividualInfo.AmtUsed,0) > "
+ POut.Double(aboveAmount) + @")
AND (annualMax.AnnualMaxFam=-1 OR annualMax.AnnualMaxFam-COALESCE(FamilyInfo.AmtUsed,0) > " + POut.Double(aboveAmount) + "))) ";
}
if (listPatNums.Count > 0)
{
command += " AND patient.PatNum IN (" + string.Join(",", listPatNums) + ") ";
}
else if (!patsWithAppts)
{
command += cmdFutureApt;
}
command += @"HAVING $TreatmentPlan > 0 ";
command += @"ORDER BY $TreatmentPlan DESC";
DataTable rawtable = Db.GetTable(command);
#if DEBUG
sw.Stop();
Console.WriteLine("Finishing retreiving query: {0}", sw.ElapsedMilliseconds);
sw = Stopwatch.StartNew();
#endif
ContactMethod contmeth;
for (int i = 0; i < rawtable.Rows.Count; i++)
{
row = table.NewRow();
row["PatNum"] = PIn.Long(rawtable.Rows[i]["PatNum"].ToString());
row["LName"] = rawtable.Rows[i]["LName"].ToString();
row["FName"] = rawtable.Rows[i]["FName"].ToString();
contmeth = (ContactMethod)PIn.Long(rawtable.Rows[i]["PreferRecallMethod"].ToString());
if (contmeth == ContactMethod.None)
{
if (PrefC.GetBool(PrefName.RecallUseEmailIfHasEmailAddress)) //if user only wants to use email if contact method is email
{
if (rawtable.Rows[i]["Email"].ToString() != "")
{
row["contactMethod"] = rawtable.Rows[i]["Email"].ToString();
}
else
{
row["contactMethod"] = Lans.g("FormRecallList", "Hm:") + rawtable.Rows[i]["HmPhone"].ToString();
}
}
else
{
row["contactMethod"] = Lans.g("FormRecallList", "Hm:") + rawtable.Rows[i]["HmPhone"].ToString();
}
}
else if (contmeth == ContactMethod.HmPhone)
{
row["contactMethod"] = Lans.g("FormRecallList", "Hm:") + rawtable.Rows[i]["HmPhone"].ToString();
}
else if (contmeth == ContactMethod.WkPhone)
{
row["contactMethod"] = Lans.g("FormRecallList", "Wk:") + rawtable.Rows[i]["WkPhone"].ToString();
}
else if (contmeth == ContactMethod.WirelessPh)
{
row["contactMethod"] = Lans.g("FormRecallList", "Cell:") + rawtable.Rows[i]["WirelessPhone"].ToString();
}
else if (contmeth == ContactMethod.Email)
{
row["contactMethod"] = rawtable.Rows[i]["Email"].ToString();
}
else if (contmeth == ContactMethod.Mail)
{
row["contactMethod"] = Lans.g("FormRecallList", "Mail");
}
else if (contmeth == ContactMethod.DoNotCall || contmeth == ContactMethod.SeeNotes)
{
row["contactMethod"] = Lans.g("enumContactMethod", contmeth.ToString());
}
row["address"] = rawtable.Rows[i]["Address"].ToString();
if (rawtable.Rows[i]["Address2"].ToString() != "")
{
row["address"] += "\r\n" + rawtable.Rows[i]["Address2"].ToString();
}
row["City"] = rawtable.Rows[i]["City"].ToString();
row["State"] = rawtable.Rows[i]["State"].ToString();
row["Zip"] = rawtable.Rows[i]["Zip"].ToString();
row["annualMaxInd"] = (PIn.Double(rawtable.Rows[i]["AnnualMaxInd"].ToString())).ToString("N");
row["annualMaxFam"] = (PIn.Double(rawtable.Rows[i]["AnnualMaxFam"].ToString())).ToString("N");
row["amountUsedInd"] = (PIn.Double(rawtable.Rows[i]["AmountUsedInd"].ToString())).ToString("N");
row["amountUsedFam"] = (PIn.Double(rawtable.Rows[i]["AmountUsedFam"].ToString())).ToString("N");
row["amountPendingInd"] = (PIn.Double(rawtable.Rows[i]["AmountPendingInd"].ToString())).ToString("N");
row["amountPendingFam"] = (PIn.Double(rawtable.Rows[i]["AmountPendingFam"].ToString())).ToString("N");
row["amountRemainingInd"] = (PIn.Double(rawtable.Rows[i]["$AmtRemainingInd"].ToString())).ToString("N");
row["amountRemainingFam"] = (PIn.Double(rawtable.Rows[i]["$AmtRemainingFam"].ToString())).ToString("N");
row["treatmentPlan"] = (PIn.Double(rawtable.Rows[i]["$TreatmentPlan"].ToString())).ToString("N");
row["carrierName"] = rawtable.Rows[i]["CarrierName"].ToString();
row["clinicAbbr"] = rawtable.Rows[i]["clinicAbbr"].ToString();
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
#if DEBUG
sw.Stop();
Console.WriteLine("Finished Filling query result: {0}", sw.ElapsedMilliseconds);
#endif
return(table);
}
///<summary>Gets all patplans with DateNextClaims that are today or in the past.</summary>
public static DataTable GetOutstandingOrtho()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod()));
}
if (DataConnection.DBtype == DatabaseType.Oracle)
{
throw new ApplicationException("Currently not Oracle compatible. Please call support.");
}
byte orthoMonthsTreat = PrefC.GetByte(PrefName.OrthoDefaultMonthsTreat);
long orthoDefaultAutoCodeNum = PrefC.GetLong(PrefName.OrthoAutoProcCodeNum);
List <long> listOrthoBandingCodeNums = ProcedureCodes.GetOrthoBandingCodeNums();
string command = @"
SELECT CONCAT(patient.LName,', ', patient.FName) Patient,
patient.PatNum,
carrier.CarrierName,
patplan.OrthoAutoNextClaimDate,
IF(patientnote.OrthoMonthsTreatOverride=-1,
" + orthoMonthsTreat + @",
patientnote.OrthoMonthsTreatOverride) MonthsTreat,
patclaims.LastSent,
patclaims.NumSent,
IF(insplan.OrthoAutoProcCodeNumOverride = 0,
" + orthoDefaultAutoCodeNum + @",
insplan.OrthoAutoProcCodeNumOverride) AS AutoCodeNum,
banding.DateBanding,
banding.ProvNum,
banding.ClinicNum,
patplan.PatPlanNum,
inssub.InsSubNum,
insplan.PlanNum
FROM patplan
INNER JOIN inssub ON inssub.InsSubNum = patplan.InsSubNum
INNER JOIN insplan ON insplan.PlanNum = inssub.PlanNum
AND insplan.OrthoType = " + POut.Int((int)OrthoClaimType.InitialPlusPeriodic) + @"
INNER JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
INNER JOIN patient ON patient.PatNum = patplan.PatNum
LEFT JOIN patientnote ON patientnote.PatNum = patient.PatNum
LEFT JOIN (
SELECT MAX(claim.DateSent) LastSent, COUNT(claim.ClaimNum) NumSent, claim.PatNum, claim.InsSubNum, procedurelog.CodeNum
FROM claim
INNER JOIN claimproc ON claimproc.ClaimNum = claim.ClaimNum
INNER JOIN insplan ON claim.PlanNum = insplan.PlanNum
INNER JOIN procedurelog ON procedurelog.ProcNum = claimproc.ProcNum
AND procedurelog.CodeNum =
IF(insplan.OrthoAutoProcCodeNumOverride = 0,
" + orthoDefaultAutoCodeNum + @",
insplan.OrthoAutoProcCodeNumOverride)
WHERE claim.ClaimStatus IN ('S','R')
GROUP BY claim.PatNum, claim.InsSubNum
)patclaims ON patclaims.PatNum = patplan.PatNum
AND patclaims.InsSubNum = patplan.InsSubNum
LEFT JOIN (
SELECT procedurelog.PatNum, MIN(procedurelog.ProcDate) AS DateBanding, procedurelog.ProvNum, procedurelog.ClinicNum
FROM procedurelog " ;
if (listOrthoBandingCodeNums.Count == 0) //The first rendition of ortho auto codes looked for hard coded D codes.
{
command += @"INNER JOIN procedurecode ON procedurecode.CodeNum = procedurelog.CodeNum
AND(procedurecode.ProcCode LIKE 'D8080%' OR procedurecode.ProcCode LIKE 'D8090%') " ;
}
command += @"WHERE procedurelog.ProcStatus = " + POut.Int((int)ProcStat.C) + " ";
if (listOrthoBandingCodeNums.Count > 0)
{
command += @"AND procedurelog.CodeNum IN ( " + String.Join(",", listOrthoBandingCodeNums) + @") ";
}
command += @" GROUP BY procedurelog.PatNum
)banding ON banding.PatNum = patplan.PatNum
WHERE (patplan.OrthoAutoNextClaimDate > " + POut.Date(new DateTime(1880, 1, 1)) + " AND patplan.OrthoAutoNextClaimDate <= " + DbHelper.Curdate() + @")
AND patplan.Ordinal IN (1,2) " ;
//TODO: Consider the edge case where an office falls behind and the patient really needs to create multiple claims.
//E.g. NextClaimDate = 11/01/2016, today is 12/16/2016, this query would only show the Nov. row, but needs to also show a row for 12/01/2016.
return(Db.GetTable(command));
}
///<summary>Returns list of active credit cards.</summary>
public static List <CreditCard> GetActiveCards(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <CreditCard> >(MethodBase.GetCurrentMethod(), patNum));
}
string command = "SELECT * FROM creditcard WHERE PatNum=" + POut.Long(patNum)
+ " AND (" + DbHelper.Year("DateStop") + "<1880 OR DateStop>=" + DbHelper.Curdate() + ") "
+ " AND (" + DbHelper.Year("DateStart") + ">1880 AND DateStart<=" + DbHelper.Curdate() + ") " //Recurring card is active.
+ " AND CCSource NOT IN (" + (int)CreditCardSource.XWeb + "," + (int)CreditCardSource.XWebPortalLogin + ") "; //Not created from the Patient Portal
return(Crud.CreditCardCrud.SelectMany(command));
}
///<summary>Updates the employee's ClockStatus if necessary based on their clock events. This method handles future clock events as having
///already occurred. Ex: If I clock out for home at 6:00 but edit my time card to say 7:00, at 6:30 my status will say Home.</summary>
public static void UpdateClockStatus(long employeeNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), employeeNum);
return;
}
//Get the last clockevent for the employee. Will include clockevent with "in" before NOW, and "out" anytime before 23:59:59 of TODAY.
string command = @"SELECT * FROM clockevent
WHERE TimeDisplayed2<=" + DbHelper.DateAddSecond(DbHelper.DateAddDay(DbHelper.Curdate(), "1"), "-1") + " AND TimeDisplayed1<=" + DbHelper.Now() + @"
AND EmployeeNum=" + POut.Long(employeeNum) + @"
ORDER BY IF(YEAR(TimeDisplayed2) < 1880,TimeDisplayed1,TimeDisplayed2) DESC" ;
command = DbHelper.LimitOrderBy(command, 1);
ClockEvent clockEvent = Crud.ClockEventCrud.SelectOne(command);
Employee employee = GetEmp(employeeNum);
Employee employeeOld = employee.Copy();
if (clockEvent != null && clockEvent.TimeDisplayed2 > DateTime.Now) //Future time manual clock out.
{
employee.ClockStatus = Lans.g("ContrStaff", "Manual Entry");
}
else if (clockEvent == null || //Employee has never clocked in
(clockEvent.TimeDisplayed2.Year > 1880 && clockEvent.ClockStatus == TimeClockStatus.Home)) //Clocked out for home
{
employee.ClockStatus = Lans.g("enumTimeClockStatus", TimeClockStatus.Home.ToString());
}
else if (clockEvent.TimeDisplayed2.Year > 1880 && clockEvent.ClockStatus == TimeClockStatus.Lunch) //Clocked out for lunch
{
employee.ClockStatus = Lans.g("enumTimeClockStatus", TimeClockStatus.Lunch.ToString());
}
else if (clockEvent.TimeDisplayed1.Year > 1880 && clockEvent.TimeDisplayed2.Year < 1880 && clockEvent.ClockStatus == TimeClockStatus.Break)
{
employee.ClockStatus = Lans.g("enumTimeClockStatus", TimeClockStatus.Break.ToString());
}
else if (clockEvent.TimeDisplayed2.Year > 1880 && clockEvent.ClockStatus == TimeClockStatus.Break) //Clocked back in from break
{
employee.ClockStatus = Lans.g("ContrStaff", "Working");
}
else //The employee has not clocked out yet.
{
employee.ClockStatus = Lans.g("ContrStaff", "Working");
}
Crud.EmployeeCrud.Update(employee, employeeOld);
}
///<summary>Returns list of credit cards that are ready for a recurring charge. Filters by ClinicNums in list if provided. List of ClinicNums
///should contain all clinics the current user is authorized to access. Further filtering by selected clinics is done at the UI level to save
///DB calls.</summary>
public static DataTable GetRecurringChargeList(List <long> listClinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums));
}
DataTable table = new DataTable();
//This query will return patient information and the latest recurring payment whom:
// -have recurring charges setup and today's date falls within the start and stop range.
//NOTE: Query will return patients with or without payments regardless of when that payment occurred, filtering is done below.
string command = "SELECT CreditCardNum,PatNum,PatName,FamBalTotal,PayPlanDue," + POut.Date(DateTime.MinValue) + " AS LatestPayment,DateStart,Address,"
+ "AddressPat,Zip,ZipPat,XChargeToken,CCNumberMasked,CCExpiration,ChargeAmt,PayPlanNum,ProvNum,PayPlanPatnum,ClinicNum,Procedures,BillingCycleDay,Guarantor,"
+ "PayConnectToken,PayConnectTokenExp,PaySimpleToken "
+ "FROM (";
#region Payments
//The PayOrder is used to differentiate rows attached to payment plans
command += "(SELECT 1 AS PayOrder,cc.CreditCardNum,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + " PatName,"
+ "guar.LName GuarLName,guar.FName GuarFName,guar.BalTotal-guar.InsEst FamBalTotal,0 AS PayPlanDue,"
+ "cc.DateStart,cc.Address,pat.Address AddressPat,cc.Zip,pat.Zip ZipPat,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,"
+ "cc.PayPlanNum,cc.DateStop,0 ProvNum,0 PayPlanPatNum,pat.ClinicNum,cc.Procedures,pat.BillingCycleDay,pat.Guarantor,cc.PayConnectToken,cc.PayConnectTokenExp,cc.PaySimpleToken "
+ "FROM creditcard cc "
+ "INNER JOIN patient pat ON pat.PatNum=cc.PatNum "
+ "INNER JOIN patient guar ON guar.PatNum=pat.Guarantor "
+ "WHERE cc.PayPlanNum=0 " //Keeps card from showing up in case they have a balance AND is setup for payment plan.
+ "AND CCSource NOT IN (" + (int)CreditCardSource.XWeb + "," + (int)CreditCardSource.XWebPortalLogin + ") "; //Not created from the Patient Portal
if (listClinicNums != null && listClinicNums.Count > 0)
{
command += "AND pat.ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += "GROUP BY cc.CreditCardNum) ";
}
else //Oracle
{
command += "GROUP BY cc.CreditCardNum,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + ",PatName,guar.BalTotal-guar.InsEst,"
+ "cc.Address,pat.Address,cc.Zip,pat.Zip,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,cc.PayPlanNum,cc.DateStop,PayPlanPatNum,"
+ "pat.ClinicNum,cc.Procedures,pat.BillingCycleDay,pat.Guarantor,cc.PayConnectToken,cc.PayConnectTokenExp,cc.PaySimpleToken) ";
}
#endregion
command += "UNION ALL ";
#region Payment Plans
command += "(SELECT 2 AS PayOrder,cc.CreditCardNum,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + " PatName,"
+ "guar.LName GuarLName,guar.FName GuarFName,guar.BalTotal-guar.InsEst FamBalTotal,"
+ "ROUND(COALESCE(ppc.pastCharges,0)-COALESCE(SUM(ps.SplitAmt),0),2) PayPlanDueCalc," //payplancharges-paysplits attached to pp is PayPlanDueCalc
+ "cc.DateStart,cc.Address,pat.Address AddressPat,cc.Zip,pat.Zip ZipPat,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,"
+ "cc.PayPlanNum,cc.DateStop,COALESCE(ppc.maxProvNum,0) ProvNum,COALESCE(ppc.maxPatNum,0) PayPlanPatNum,COALESCE(ppc.maxClinicNum,0) ClinicNum,cc.Procedures,"
+ "pat.BillingCycleDay,pat.Guarantor,cc.PayConnectToken,cc.PayConnectTokenExp,cc.PaySimpleToken "
+ "FROM creditcard cc "
+ "INNER JOIN patient pat ON pat.PatNum=cc.PatNum "
+ "INNER JOIN patient guar ON guar.PatNum=pat.Guarantor "
+ "LEFT JOIN paysplit ps ON ps.PayPlanNum=cc.PayPlanNum AND ps.PayPlanNum<>0 "
+ "LEFT JOIN ("
+ "SELECT PayPlanNum,MAX(ProvNum) maxProvNum,MAX(PatNum) maxPatNum,MAX(ClinicNum) maxClinicNum,"
+ "SUM(CASE WHEN ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " "
+ "AND ChargeDate <= " + DbHelper.Curdate() + " THEN Principal+Interest ELSE 0 END) pastCharges "
+ "FROM payplancharge "
+ "GROUP BY PayPlanNum"
+ ") ppc ON ppc.PayPlanNum=cc.PayPlanNum "
+ "WHERE cc.PayPlanNum>0 "
+ "AND CCSource NOT IN (" + (int)CreditCardSource.XWeb + "," + (int)CreditCardSource.XWebPortalLogin + ") ";//Not created from the Patient Portal
if (listClinicNums != null && listClinicNums.Count > 0)
{
command += "AND ppc.maxClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += "GROUP BY cc.CreditCardNum ";
}
else //Oracle
{
command += "GROUP BY cc.CreditCardNum,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + ",PatName,guar.BalTotal-guar.InsEst,"
+ "cc.Address,pat.Address,cc.Zip,pat.Zip,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,cc.PayPlanNum,cc.DateStop,PayPlanPatNum,"
+ "ClinicNum,cc.Procedues,pat.BillingCycleDay,pat.Guarantor,cc.PayConnectToken,cc.PayConnectTokenExp,cc.PaySimpleToken ";
}
command += "HAVING PayPlanDueCalc>0)"; //don't show cc's attached to payplans when the payplan has nothing due
#endregion
//Now we have all the results for payments and payment plans, so do an obvious filter. A more thorough filter happens later.
command += ") due "
+ "WHERE DateStart<=" + DbHelper.Curdate() + " AND " + DbHelper.Year("DateStart") + ">1880 "
+ "AND (DateStop>=" + DbHelper.Curdate() + " OR " + DbHelper.Year("DateStop") + "<1880) "
+ "ORDER BY GuarLName,GuarFName,PatName,PayOrder DESC";
table = Db.GetTable(command);
//Query for latest payments seperately because this takes a very long time when run as a sub select
if (table.Rows.Count < 1)
{
return(table);
}
command = "SELECT PatNum,MAX(CASE WHEN " + DbHelper.Year("RecurringChargeDate") + " > 1880 "
+ "THEN RecurringChargeDate ELSE PayDate END) RecurringChargeDate "
+ "FROM payment "
+ "WHERE IsRecurringCC=1 AND PayAmt > 0 "
+ "AND PatNum IN (" + string.Join(",", table.Select().Select(x => POut.String(x["PatNum"].ToString()))) + ") " //table has at least 1 row
+ "GROUP BY PatNum";
//dictionary is key=PatNum, value=LatestPayment which will be the lastest date a recurring charge payment was made
Dictionary <long, DateTime> dictPatNumDate = Db.GetTable(command).Select()
.ToDictionary(x => PIn.Long(x["PatNum"].ToString()), x => PIn.Date(x["RecurringChargeDate"].ToString()));
table.Select().Where(x => dictPatNumDate.ContainsKey(PIn.Long(x["PatNum"].ToString()))).ToList()
.ForEach(x => x["LatestPayment"] = dictPatNumDate[PIn.Long(x["PatNum"].ToString())]);
FilterRecurringChargeList(table);
return(table);
}
///<summary>Updates the Procedures column on all cards that have not stopped that are not marked to exclude from sync. Only used at HQ.</summary>
public static void SyncDefaultProcs(List <string> listProcCodes)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), listProcCodes);
return;
}
string command = "UPDATE creditcard SET Procedures='" + string.Join(",", listProcCodes.Select(x => POut.String(x))) + "'"
+ " WHERE (" + DbHelper.Year("DateStop") + "<1880 OR DateStop>=" + DbHelper.Curdate() + ") " //Stop date has not past
+ " AND ExcludeProcSync=0"
+ " AND CCSource NOT IN (" + (int)CreditCardSource.XWeb + "," + (int)CreditCardSource.XWebPortalLogin + ") "; //Not created from the Patient Portal
Db.NonQ(command);
}
///<summary>Returns list of credit cards that are ready for a recurring charge.</summary>
public static DataTable GetRecurringChargeList()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod()));
}
DataTable table = new DataTable();
//This query will return patient information and the latest recurring payment whom:
// -have recurring charges setup and today's date falls within the start and stop range.
// -have a total balance >= recurring charge amount
//NOTE: Query will return patients with or without payments regardless of when that payment occurred, filtering is done below.
string command = "SELECT PatNum,PatName,FamBalTotal,LatestPayment,DateStart,Address,AddressPat,Zip,ZipPat,XChargeToken,CCNumberMasked,CCExpiration,ChargeAmt,PayPlanNum,ProvNum,ClinicNum "
+ "FROM (";
#region Payments
command += "(SELECT 1,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + " PatName," //The 'SELECT 1' garuntees the UNION will not combine results with payment plans.
+ "guar.BalTotal-guar.InsEst FamBalTotal,CASE WHEN MAX(pay.PayDate) IS NULL THEN " + POut.Date(new DateTime(1, 1, 1)) + " ELSE MAX(pay.PayDate) END LatestPayment,"
+ "cc.DateStart,cc.Address,pat.Address AddressPat,cc.Zip,pat.Zip ZipPat,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,cc.PayPlanNum,cc.DateStop,0 ProvNum,pat.ClinicNum "
+ "FROM (creditcard cc,patient pat,patient guar) "
+ "LEFT JOIN payment pay ON cc.PatNum=pay.PatNum AND pay.IsRecurringCC=1 "
+ "WHERE cc.PatNum=pat.PatNum "
+ "AND pat.Guarantor=guar.PatNum "
+ "AND cc.PayPlanNum=0 "; //Keeps card from showing up in case they have a balance AND is setup for payment plan.
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += "GROUP BY cc.CreditCardNum) ";
}
else //Oracle
{
command += "GROUP BY cc.CreditCardNum,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + ",PatName,guar.BalTotal-guar.InsEst,"
+ "cc.Address,cc.Zip,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,cc.PayPlanNum,cc.DateStop) ";
}
#endregion
command += "UNION ";
#region Payment Plans
command += "(SELECT 2,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + " PatName,"; //The 'SELECT 2' garuntees the UNION will not combine results with payments.
//Special select statement to figure out how much is owed on a particular payment plan. This total amount will be Labeled as FamBalTotal for UNION purposes.
command += "ROUND((SELECT CASE WHEN SUM(ppc.Principal+ppc.Interest) IS NULL THEN 0 ELSE SUM(ppc.Principal+ppc.Interest) END "
+ "FROM PayPlanCharge ppc "
+ "WHERE ppc.ChargeDate <= " + DbHelper.Curdate() + " AND ppc.PayPlanNum=cc.PayPlanNum) "
+ "- CASE WHEN SUM(ps.SplitAmt) IS NULL THEN 0 ELSE SUM(ps.SplitAmt) END,2) FamBalTotal,";
command += "CASE WHEN MAX(ps.DatePay) IS NULL THEN " + POut.Date(new DateTime(1, 1, 1)) + " ELSE MAX(pay.PayDate) END LatestPayment,"
+ "cc.DateStart,cc.Address,pat.Address AddressPat,cc.Zip,pat.Zip ZipPat,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,cc.PayPlanNum,cc.DateStop,"
+ "(SELECT ppc1.ProvNum FROM payplancharge ppc1 WHERE ppc1.PayPlanNum=cc.PayPlanNum LIMIT 1) ProvNum,"
+ "(SELECT ppc2.ClinicNum FROM payplancharge ppc2 WHERE ppc2.PayPlanNum=cc.PayPlanNum LIMIT 1) ClinicNum "
+ "FROM creditcard cc "
+ "INNER JOIN patient pat ON pat.PatNum=cc.PatNum "
+ "LEFT JOIN paysplit ps ON ps.PayPlanNum=cc.PayPlanNum AND ps.PayPlanNum<>0 "
+ "LEFT JOIN payment pay ON pay.PayNum=ps.PayNum AND pay.IsRecurringCC=1 "
+ "WHERE cc.PayPlanNum<>0 ";
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += "GROUP BY cc.CreditCardNum) ";
}
else //Oracle
{
command += "GROUP BY cc.CreditCardNum,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + ",PatName,guar.BalTotal-guar.InsEst,"
+ "cc.Address,pat.Address,cc.Zip,pat.Zip,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,cc.PayPlanNum,cc.DateStop) ";
}
#endregion
//Now we have all the results for payments and payment plans, so do an obvious filter. A more thorough filter happens later.
command += ") due "
+ "WHERE FamBalTotal>=ChargeAmt "
+ "AND ChargeAmt>0 "
+ "AND DateStart<=" + DbHelper.Curdate() + " "
+ "AND (DateStop>=" + DbHelper.Curdate() + " OR YEAR(DateStop)<1880) ";
table = Db.GetTable(command);
FilterRecurringChargeList(table);
return(table);
}
///<summary>Automatically closes all payment plans that have no future charges and that are paid off.
///Not really a problem if it fails because the UPDATE statement happens all at once, so at worst, no changes are made to their database.
///Returns the number of payment plans that were closed.</summary>
public static long AutoClose()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod()));
}
string command = "";
DataTable table;
command = "SELECT payplan.PayPlanNum,SUM(payplancharge.Principal) AS Princ,SUM(payplancharge.Interest) AS Interest,"
+ "COALESCE(ps.TotPayments,0) AS TotPay,COALESCE(cp.InsPayments,0) AS InsPay,"
+ "MAX(payplancharge.ChargeDate) AS LastDate "
+ "FROM payplan "
+ "LEFT JOIN payplancharge ON payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " "
+ "LEFT JOIN ("
+ "SELECT paysplit.PayPlanNum, SUM(paysplit.SplitAmt) AS TotPayments "
+ "FROM paysplit "
+ "GROUP BY paysplit.PayPlanNum "
+ ")ps ON ps.PayPlanNum = payplan.PayPlanNum "
+ "LEFT JOIN ( "
+ "SELECT claimproc.PayPlanNum, SUM(claimproc.InsPayAmt) AS InsPayments "
+ "FROM claimproc "
+ "GROUP BY claimproc.PayPlanNum "
+ ")cp ON cp.PayPlanNum = payplan.PayPlanNum "
+ "WHERE payplan.IsClosed = 0 "
+ "GROUP BY payplan.PayPlanNum "
+ "HAVING Princ+Interest <= (TotPay + InsPay) AND LastDate <=" + DbHelper.Curdate();
table = Db.GetTable(command);
string payPlanNums = "";
for (int i = 0; i < table.Rows.Count; i++)
{
if (i != 0)
{
payPlanNums += ",";
}
payPlanNums += table.Rows[i]["PayPlanNum"];
}
if (payPlanNums == "")
{
return(0); //no plans to close.
}
command = "UPDATE payplan SET IsClosed=1 WHERE PayPlanNum IN (" + payPlanNums + ")";
return(Db.NonQ(command));
}
///<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);
}
