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