public static DataSet GetDataSet()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDS(MethodBase.GetCurrentMethod()));
}
string command = "SELECT 'cell00'";
DataSet ds = new DataSet();
DataTable table = Db.GetTable(command);
table.TableName = "table0";
ds.Tables.Add(table);
return(ds);
}
///<summary>If ClientWeb, then this method is instead run on the server, and the result passed back to the client. And since it's ClientWeb, FillCache will be run on the client.</summary>
public static DataSet GetCacheDs(bool doRefreshServerCache, params InvalidType[] arrayITypes)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDS(MethodBase.GetCurrentMethod(), doRefreshServerCache, arrayITypes));
}
Logger.LogToPath("", LogPath.Signals, LogPhase.Start, "InvalidType(s): " + string.Join(" - ", arrayITypes.OrderBy(x => x.ToString())));
List <InvalidType> listITypes = arrayITypes.ToList();
//so this part below only happens if direct or server------------------------------------------------
bool isAll = false;
if (listITypes.Contains(InvalidType.AllLocal))
{
isAll = true;
}
DataSet ds = new DataSet();
//All Internal OD Tables that are cached go here
if (PrefC.IsODHQ)
{
if (listITypes.Contains(InvalidType.JobPermission) || isAll)
{
ds.Tables.Add(JobPermissions.RefreshCache());
}
if (listITypes.Contains(InvalidType.PhoneComps) || isAll)
{
ds.Tables.Add(PhoneComps.GetTableFromCache(doRefreshServerCache));
}
}
//All cached public tables go here
if (listITypes.Contains(InvalidType.AccountingAutoPays) || isAll)
{
ds.Tables.Add(AccountingAutoPays.GetTableFromCache(doRefreshServerCache));
}
//if(listITypes.Contains(InvalidType.AlertItems) || isAll) {//THIS IS NOT CACHED. But is used to make server run the alert logic in OpenDentalService.
// ds.Tables.Add(AlertItems.RefreshCache());
//}
if (listITypes.Contains(InvalidType.AlertCategories) || isAll)
{
ds.Tables.Add(AlertCategories.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.AlertCategoryLinks) || isAll)
{
ds.Tables.Add(AlertCategoryLinks.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.AppointmentTypes) || isAll)
{
ds.Tables.Add(AppointmentTypes.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.AutoCodes) || isAll)
{
ds.Tables.Add(AutoCodes.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(AutoCodeItems.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(AutoCodeConds.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Automation) || isAll)
{
ds.Tables.Add(Automations.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.AutoNotes) || isAll)
{
ds.Tables.Add(AutoNotes.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(AutoNoteControls.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Carriers) || isAll)
{
ds.Tables.Add(Carriers.GetTableFromCache(doRefreshServerCache)); //run on startup, after telephone reformat, after list edit.
}
if (listITypes.Contains(InvalidType.ClaimForms) || isAll)
{
ds.Tables.Add(ClaimFormItems.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(ClaimForms.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.ClearHouses) || isAll)
{
ds.Tables.Add(Clearinghouses.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.ClinicErxs) || isAll)
{
ds.Tables.Add(ClinicErxs.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.ClinicPrefs) || isAll)
{
ds.Tables.Add(ClinicPrefs.GetTableFromCache(doRefreshServerCache));
}
//InvalidType.Clinics see InvalidType.Providers
if (listITypes.Contains(InvalidType.Computers) || isAll)
{
ds.Tables.Add(Computers.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(Printers.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Defs) || isAll)
{
ds.Tables.Add(Defs.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.DentalSchools) || isAll)
{
ds.Tables.Add(SchoolClasses.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(SchoolCourses.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.DictCustoms) || isAll)
{
ds.Tables.Add(DictCustoms.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Diseases) || isAll)
{
ds.Tables.Add(DiseaseDefs.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(ICD9s.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.DisplayFields) || isAll)
{
ds.Tables.Add(DisplayFields.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.DisplayReports) || isAll)
{
ds.Tables.Add(DisplayReports.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Ebills) || isAll)
{
ds.Tables.Add(Ebills.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.EhrCodes))
{
EhrCodes.UpdateList(); //Unusual pattern for an unusual "table". Not really a table, but a mishmash of hard coded partial code systems that are needed for CQMs.
}
if (listITypes.Contains(InvalidType.ElectIDs) || isAll)
{
ds.Tables.Add(ElectIDs.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Email) || isAll)
{
ds.Tables.Add(EmailAddresses.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(EmailTemplates.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(EmailAutographs.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Employees) || isAll)
{
ds.Tables.Add(Employees.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(PayPeriods.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Employers) || isAll)
{
ds.Tables.Add(Employers.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Fees) || isAll)
{
//Fee Cache follows an unusual pattern. This fills the cache with the HQ fees, and whatever clinics happen to be currently cached.
ds.Tables.Add(Fees.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.FeeScheds) || isAll)
{
ds.Tables.Add(FeeScheds.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.HL7Defs) || isAll)
{
ds.Tables.Add(HL7Defs.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(HL7DefMessages.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(HL7DefSegments.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(HL7DefFields.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.InsCats) || isAll)
{
ds.Tables.Add(CovCats.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(CovSpans.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.InsFilingCodes) || isAll)
{
ds.Tables.Add(InsFilingCodes.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(InsFilingCodeSubtypes.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Languages) || isAll)
{
if (CultureInfo.CurrentCulture.Name != "en-US")
{
ds.Tables.Add(Lans.GetTableFromCache(doRefreshServerCache));
}
}
if (listITypes.Contains(InvalidType.Letters) || isAll)
{
ds.Tables.Add(Letters.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.LetterMerge) || isAll)
{
ds.Tables.Add(LetterMergeFields.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(LetterMerges.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Medications) || isAll)
{
ds.Tables.Add(Medications.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Operatories) || isAll)
{
ds.Tables.Add(Operatories.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.OrthoChartTabs) || isAll)
{
ds.Tables.Add(OrthoChartTabs.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(OrthoChartTabLinks.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.PatFields) || isAll)
{
ds.Tables.Add(PatFieldDefs.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(ApptFieldDefs.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Pharmacies) || isAll)
{
ds.Tables.Add(Pharmacies.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Prefs) || isAll)
{
ds.Tables.Add(Prefs.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.ProcButtons) || isAll)
{
ds.Tables.Add(ProcButtons.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(ProcButtonItems.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.ProcCodes) || isAll)
{
ds.Tables.Add(ProcedureCodes.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(ProcCodeNotes.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Programs) || isAll)
{
ds.Tables.Add(Programs.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(ProgramProperties.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.ProviderErxs) || isAll)
{
ds.Tables.Add(ProviderErxs.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.ProviderIdents) || isAll)
{
ds.Tables.Add(ProviderIdents.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Providers) || isAll)
{
ds.Tables.Add(Providers.GetTableFromCache(doRefreshServerCache));
//Refresh the clinics as well because InvalidType.Providers has a comment that says "also includes clinics". Also, there currently isn't an itype for Clinics.
ds.Tables.Add(Clinics.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.QuickPaste) || isAll)
{
ds.Tables.Add(QuickPasteNotes.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(QuickPasteCats.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.RecallTypes) || isAll)
{
ds.Tables.Add(RecallTypes.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(RecallTriggers.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.ReplicationServers) || isAll)
{
ds.Tables.Add(ReplicationServers.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.RequiredFields) || isAll)
{
ds.Tables.Add(RequiredFields.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(RequiredFieldConditions.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Security) || isAll)
{
ds.Tables.Add(Userods.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(UserGroups.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(GroupPermissions.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(UserGroupAttaches.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Sheets) || isAll)
{
ds.Tables.Add(SheetDefs.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(SheetFieldDefs.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.SigMessages) || isAll)
{
ds.Tables.Add(SigElementDefs.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(SigButDefs.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Sites) || isAll)
{
ds.Tables.Add(Sites.GetTableFromCache(doRefreshServerCache));
if (PrefC.IsODHQ)
{
ds.Tables.Add(SiteLinks.GetTableFromCache(doRefreshServerCache));
}
}
if (listITypes.Contains(InvalidType.SmsBlockPhones) || isAll)
{
ds.Tables.Add(SmsBlockPhones.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.SmsPhones) || isAll)
{
ds.Tables.Add(SmsPhones.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Sops) || isAll) //InvalidType.Sops is currently never used 11/14/2014
{
ds.Tables.Add(Sops.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.StateAbbrs) || isAll)
{
ds.Tables.Add(StateAbbrs.GetTableFromCache(doRefreshServerCache));
}
//InvalidTypes.Tasks not handled here.
if (listITypes.Contains(InvalidType.TimeCardRules) || isAll)
{
ds.Tables.Add(TimeCardRules.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.ToolBut) || isAll)
{
ds.Tables.Add(ToolButItems.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.UserClinics) || isAll)
{
ds.Tables.Add(UserClinics.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.UserQueries) || isAll)
{
ds.Tables.Add(UserQueries.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Vaccines) || isAll)
{
ds.Tables.Add(VaccineDefs.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(DrugManufacturers.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(DrugUnits.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Views) || isAll)
{
ds.Tables.Add(ApptViews.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(ApptViewItems.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(AppointmentRules.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(ProcApptColors.GetTableFromCache(doRefreshServerCache));
}
if (listITypes.Contains(InvalidType.Wiki) || isAll)
{
ds.Tables.Add(WikiListHeaderWidths.GetTableFromCache(doRefreshServerCache));
ds.Tables.Add(WikiPages.RefreshCache());
}
if (listITypes.Contains(InvalidType.ZipCodes) || isAll)
{
ds.Tables.Add(ZipCodes.GetTableFromCache(doRefreshServerCache));
}
Logger.LogToPath("", LogPath.Signals, LogPhase.End);
return(ds);
}
///<summary>If not using clinics then supply an empty list of clinicNums.</summary>
public static DataSet GetPayPlanTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums,
bool hasAllProvs, DisplayPayPlanType displayPayPlanType, bool hideCompletedPlans, bool showFamilyBalance, bool hasDateRange, bool isPayPlanV2)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDS(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, hasAllProvs, displayPayPlanType,
hideCompletedPlans, showFamilyBalance, hasDateRange, isPayPlanV2));
}
string whereProv = "";
if (!hasAllProvs)
{
whereProv += " AND payplancharge.ProvNum IN(";
for (int i = 0; i < listProvNums.Count; i++)
{
if (i > 0)
{
whereProv += ",";
}
whereProv += POut.Long(listProvNums[i]);
}
whereProv += ") ";
}
string whereClin = "";
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
if (hasClinicsEnabled) //Using clinics
{
whereClin += " AND payplancharge.ClinicNum IN(";
for (int i = 0; i < listClinicNums.Count; i++)
{
if (i > 0)
{
whereClin += ",";
}
whereClin += POut.Long(listClinicNums[i]);
}
whereClin += ") ";
}
DataSet ds = new DataSet();
DataTable table = new DataTable("Clinic");
table.Columns.Add("provider");
table.Columns.Add("guarantor");
table.Columns.Add("ins");
table.Columns.Add("princ");
table.Columns.Add("accumInt");
table.Columns.Add("paid");
table.Columns.Add("balance");
table.Columns.Add("due");
if (isPayPlanV2)
{
table.Columns.Add("notDue");
}
table.Columns.Add("famBal");
table.Columns.Add("clinicName");
DataTable tableTotals = new DataTable("Total");
tableTotals.Columns.Add("clinicName");
tableTotals.Columns.Add("princ");
tableTotals.Columns.Add("accumInt");
tableTotals.Columns.Add("paid");
tableTotals.Columns.Add("balance");
tableTotals.Columns.Add("due");
if (isPayPlanV2)
{
tableTotals.Columns.Add("notDue");
}
tableTotals.Columns.Add("famBal");
DataRow row;
string datesql = "CURDATE()"; //This is used to find out how much people owe currently and has nothing to do with the selected range
if (DataConnection.DBtype == DatabaseType.Oracle)
{
datesql = "(SELECT CURRENT_DATE FROM dual)";
}
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
string command = "SELECT FName,LName,MiddleI,PlanNum,Preferred,PlanNum, "
+ "COALESCE((SELECT SUM(Principal+Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " "//for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_accumDue', ";
command += "COALESCE((SELECT SUM(Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " " //for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_accumInt', ";
command += $@"COALESCE((SELECT SUM(SplitAmt) FROM paysplit WHERE paysplit.PayPlanNum=payplan.PayPlanNum ";
if (listHiddenUnearnedDefNums.Count > 0)
{
command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
command += @"AND paysplit.PayPlanNum!=0),0) '_paid',
COALESCE((SELECT SUM(InsPayAmt) FROM claimproc WHERE claimproc.PayPlanNum=payplan.PayPlanNum
AND claimproc.Status IN("
+ POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapClaim)
+ ") AND claimproc.PayPlanNum!=0),0) '_insPaid', ";
command += "COALESCE(CASE "
//When pay plan isn't dynamic, all charges are already created, so we can sum principal from them to get plans total principal.
+ "WHEN payplan.IsDynamic=0 THEN "
+ "(SELECT SUM(Principal) FROM payplancharge "
//for v1, debits are the only ChargeType.
+ "WHERE payplancharge.PayPlanNum=payplan.PayPlanNum AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + ") "
//When pay plan is dynamic, we will get it from dppprincipal, a table constructed to calculate total principal for dynamic pay plans.
+ "WHEN payplan.IsDynamic=1 THEN dppprincipal.TotalPrincipal ELSE 0 END,0)'_principal', "
+ "COALESCE((SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Credit) + "),0) '_credits', " //for v1, will always be 0.
+ "COALESCE((SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Credit) + " AND ChargeDate > " + datesql + "),0) '_notDue', "
+ "patient.PatNum PatNum, "
+ "payplancharge.ProvNum ProvNum ";
if (hasClinicsEnabled)
{
command += ", payplancharge.ClinicNum ClinicNum ";
}
//In order to determine if the patient has completely paid off their payment plan we need to get the total amount of interest as of today.
//Then, after the query has run, we'll add the interest up until today with the total principal for the entire payment plan.
//For this reason, we cannot use _accumDue which only gets the principle up until today and not the entire payment plan principle.
command += ",COALESCE((SELECT SUM(Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " " //for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_interest' "
+ "FROM payplan "
+ "LEFT JOIN patient ON patient.PatNum=payplan.Guarantor "
+ "LEFT JOIN payplancharge ON payplan.PayPlanNum=payplancharge.PayPlanNum "
+ "LEFT JOIN "
//construct dppprincipal (dynamic payment plan principal) table
+ "(SELECT payplanlink.PayPlanNum,"
//Sum total production linked to dynamic pay plan for all linked adjustments and procedures
+ "ROUND(SUM(CASE "
+ "WHEN payplanlink.AmountOverride!=0 THEN payplanlink.AmountOverride " //If override isn't zero, use it in sum
+ "ELSE (CASE " //Otherwise, use adjustment amount or total proc fee for linked production
+ "WHEN payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Adjustment) + " THEN adjustment.AdjAmt "
+ "WHEN payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Procedure) + " "
+ "THEN procedurelog.ProcFee*GREATEST(1,procedurelog.BaseUnits+procedurelog.UnitQty) "
+ "ELSE 0 END)"
//Factor in non-payplan pay splits, adjustments to procedures, and insurance estimates, payments, writeoffs, and estimated writeoffs
+ "-COALESCE(sumsplit.SumSplit,0)+COALESCE(sumprocadj.SumProcAdj,0)-COALESCE(sumins.SumIns,0) "
+ "END),2) AS 'TotalPrincipal' "
+ "FROM payplanlink "
+ "LEFT JOIN adjustment ON adjustment.AdjNum=payplanlink.FKey AND payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Adjustment) + " "
+ "LEFT JOIN procedurelog ON procedurelog.ProcNum=payplanlink.FKey AND payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Procedure) + " "
+ "LEFT JOIN " //Table to sum all paysplits made to linked production outside of pay plan.
+ "(SELECT paysplit.ProcNum,paysplit.AdjNum,SUM(paysplit.SplitAmt) AS 'SumSplit' "
+ "FROM paysplit WHERE paysplit.PayPlanNum=0 AND paysplit.PayPlanChargeNum=0 GROUP BY paysplit.ProcNum,paysplit.AdjNum) "
+ "AS sumsplit ON (payplanlink.FKey=sumsplit.ProcNum AND sumsplit.ProcNum!=0 AND payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Procedure) + ") OR "
+ "(payplanlink.FKey=sumsplit.AdjNum AND sumsplit.AdjNum!=0 AND payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Adjustment) + ") "
+ "LEFT JOIN " //Table to sum all adjustments made to linked procedures.
+ "(SELECT adjustment.ProcNum, SUM(adjustment.AdjAmt) AS 'SumProcAdj' FROM adjustment GROUP BY adjustment.ProcNum) "
+ "AS sumprocadj ON sumprocadj.ProcNum=procedurelog.ProcNum AND payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Procedure) + " "
+ "LEFT JOIN " //Table to sum all ins estimates, ins payments, estimated writeoffs, and writeoffs to linked procedures.
+ "(SELECT claimproc.ProcNum, SUM(CASE "
+ "WHEN claimproc.Status IN (" + string.Join(",", ClaimProcs.GetInsPaidStatuses().Select(x => POut.Int((int)x))) + ") "
+ "THEN claimproc.InsPayAmt+claimproc.WriteOff "
+ "WHEN claimproc.Status IN (" + string.Join(",", ClaimProcs.GetEstimatedStatuses().Select(x => POut.Int((int)x))) + ") "
+ "THEN claimproc.InsPayEst+(CASE "
+ "WHEN claimproc.WriteOffEstOverride!=-1 THEN claimproc.WriteOffEstOverride "
+ "WHEN claimproc.WriteOffEst!=-1 THEN claimproc.WriteOffEst "
+ "ELSE 0 END) "
+ "ELSE 0 END) AS 'SumIns' "
+ "FROM claimproc GROUP BY claimproc.ProcNum) AS sumins "
+ "ON procedurelog.ProcNum=sumins.ProcNum AND payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Procedure) + " "
//Grouped by PayPlanNum so that we can sum total principal for each dynamic pay plan.
+ "GROUP BY PayPlanLink.PayPlanNum) AS dppprincipal ON dppprincipal.PayPlanNum=PayPlan.PayPlanNum AND PayPlan.IsDynamic=1 "
+ "WHERE TRUE "; //Always include true, so that the WHERE clause may always be present.
if (hasDateRange)
{
command += "AND payplan.PayPlanDate >= " + POut.Date(dateStart) + " "
+ "AND payplan.PayPlanDate <= " + POut.Date(dateEnd) + " ";
}
command += whereProv
+ whereClin;
if (displayPayPlanType == DisplayPayPlanType.Insurance)
{
command += "AND payplan.PlanNum!=0 ";
}
else if (displayPayPlanType == DisplayPayPlanType.Patient)
{
command += "AND payplan.PlanNum=0 ";
}
else if (displayPayPlanType == DisplayPayPlanType.Both)
{
//Do not filter the query at all which will show both insurance and patient payment plan types.
}
if (hideCompletedPlans)
{
command += "AND payplan.IsClosed=0 ";
}
command += "GROUP BY FName,LName,MiddleI,Preferred,payplan.PayPlanNum ";
if (hasClinicsEnabled)
{
command += "ORDER BY ClinicNum,LName,FName";
}
else
{
command += "ORDER BY LName,FName";
}
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
List <Provider> listProvs = ReportsComplex.RunFuncOnReportServer(() => Providers.GetAll());
//DateTime payplanDate;
Patient pat;
double princ;
double paid;
double interest;
double accumDue;
double notDue;
decimal famBal = 0;
double princTot = 0;
double paidTot = 0;
double interestTot = 0;
double balanceTot = 0;
double accumDueTot = 0;
double notDueTot = 0;
decimal famBalTot = 0;
string clinicAbbrOld = "";
for (int i = 0; i < raw.Rows.Count; i++)
{
princ = PIn.Double(raw.Rows[i]["_principal"].ToString());
interest = PIn.Double(raw.Rows[i]["_accumInt"].ToString());
if (raw.Rows[i]["PlanNum"].ToString() == "0") //pat payplan
{
paid = PIn.Double(raw.Rows[i]["_paid"].ToString());
}
else //ins payplan
{
paid = PIn.Double(raw.Rows[i]["_insPaid"].ToString());
}
accumDue = PIn.Double(raw.Rows[i]["_accumDue"].ToString());
notDue = PIn.Double(raw.Rows[i]["_notDue"].ToString());
row = table.NewRow();
//payplanDate=PIn.PDate(raw.Rows[i]["PayPlanDate"].ToString());
//row["date"]=raw.Rows[i]["PayPlanDate"].ToString();//payplanDate.ToShortDateString();
pat = new Patient();
pat.LName = raw.Rows[i]["LName"].ToString();
pat.FName = raw.Rows[i]["FName"].ToString();
pat.MiddleI = raw.Rows[i]["MiddleI"].ToString();
pat.Preferred = raw.Rows[i]["Preferred"].ToString();
row["provider"] = Providers.GetLName(PIn.Long(raw.Rows[i]["ProvNum"].ToString()), listProvs);
row["guarantor"] = pat.GetNameLF();
if (raw.Rows[i]["PlanNum"].ToString() == "0")
{
row["ins"] = "";
}
else
{
row["ins"] = "X";
}
row["princ"] = princ.ToString("f");
row["accumInt"] = interest.ToString("f");
row["paid"] = paid.ToString("f");
row["balance"] = (princ + interest - paid).ToString("f");
row["due"] = (accumDue - paid).ToString("f");
if (isPayPlanV2)
{
row["notDue"] = ((princ + interest - paid) - (accumDue - paid)).ToString("f");
}
if (showFamilyBalance)
{
Family famCur = ReportsComplex.RunFuncOnReportServer(() => Patients.GetFamily(PIn.Long(raw.Rows[i]["PatNum"].ToString())));
famBal = (decimal)famCur.ListPats[0].BalTotal;
row["famBal"] = (famBal - (decimal)famCur.ListPats[0].InsEst).ToString("F");
}
if (hasClinicsEnabled) //Using clinics
{
List <Clinic> listClinics = ReportsComplex.RunFuncOnReportServer(() => Clinics.GetClinicsNoCache());
string clinicAbbr = Clinics.GetAbbr(PIn.Long(raw.Rows[i]["ClinicNum"].ToString()), listClinics);
clinicAbbr = (clinicAbbr == "")?Lans.g("FormRpPayPlans", "Unassigned"):clinicAbbr;
if (!String.IsNullOrEmpty(clinicAbbrOld) && clinicAbbr != clinicAbbrOld) //Reset all the total values
{
DataRow rowTot = tableTotals.NewRow();
rowTot["clinicName"] = clinicAbbrOld;
rowTot["princ"] = princTot.ToString();
rowTot["accumInt"] = interestTot.ToString();
rowTot["paid"] = paidTot.ToString();
rowTot["balance"] = balanceTot.ToString();
rowTot["due"] = accumDueTot.ToString();
if (isPayPlanV2)
{
rowTot["notDue"] = notDueTot.ToString();
}
rowTot["famBal"] = famBalTot.ToString();
tableTotals.Rows.Add(rowTot);
princTot = 0;
paidTot = 0;
interestTot = 0;
accumDueTot = 0;
balanceTot = 0;
notDueTot = 0;
famBalTot = 0;
}
row["clinicName"] = clinicAbbr;
clinicAbbrOld = clinicAbbr;
princTot += princ;
paidTot += paid;
interestTot += interest;
accumDueTot += (accumDue - paid);
balanceTot += (princ + interest - paid);
notDueTot += ((princ + interest - paid) - (accumDue - paid));
famBalTot += famBal;
if (i == raw.Rows.Count - 1)
{
DataRow rowTot = tableTotals.NewRow();
rowTot["clinicName"] = clinicAbbrOld;
rowTot["princ"] = princTot.ToString();
rowTot["accumInt"] = interestTot.ToString();
rowTot["paid"] = paidTot.ToString();
rowTot["balance"] = balanceTot.ToString();
rowTot["due"] = accumDueTot.ToString();
if (isPayPlanV2)
{
rowTot["notDue"] = notDueTot.ToString();
}
rowTot["famBal"] = famBalTot.ToString();
tableTotals.Rows.Add(rowTot);
}
}
table.Rows.Add(row);
}
ds.Tables.Add(table);
ds.Tables.Add(tableTotals);
return(ds);
}
public static DataSet GetMonthlyGoalDataSet(DateTime dateFrom, DateTime dateTo, List <Provider> listProvs, List <Clinic> listClinics, bool hasAllProvs
, bool hasAllClinics, PPOWriteoffDateCalc writeoffPayType, bool isCEMT = false)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDS(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvs, listClinics, hasAllProvs, hasAllClinics, writeoffPayType, isCEMT));
}
List <long> listClinicNums = listClinics.Select(x => x.ClinicNum).ToList();
List <long> listProvNums = listProvs.Select(x => x.ProvNum).ToList();
#region Procedures
string whereProv = "";
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
string whereClin = "";
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
string command = "SELECT "
+ "procedurelog.ProcDate,procedurelog.ClinicNum,"
+ "SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(cp.WriteOff),0) Production "
+ "FROM procedurelog "
+ "LEFT JOIN (SELECT SUM(claimproc.WriteOff) AS WriteOff, claimproc.ProcNum FROM claimproc "
+ "WHERE claimproc.Status=7 " //only CapComplete writeoffs are subtracted here.
+ "GROUP BY claimproc.ProcNum) cp ON procedurelog.ProcNum=cp.ProcNum "
+ "WHERE procedurelog.ProcStatus = 2 "
+ whereProv
+ whereClin
+ "AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(dateTo) + " "
+ "GROUP BY ClinicNum,YEAR(procedurelog.ProcDate),MONTH(procedurelog.ProcDate),DAY(procedurelog.ProcDate)"; //Does not work for Oracle. Consider enhancing with DbHelper.Year(),DbHelper.Month()
command += " ORDER BY ClinicNum,ProcDate";
DataTable tableProduction = new DataTable();
if (isCEMT)
{
tableProduction = Db.GetTable(command);
}
else
{
tableProduction = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableProduction.TableName = "tableProduction";
#endregion
#region Adjustments
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND adjustment.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND adjustment.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
command = "SELECT "
+ "adjustment.AdjDate,"
+ "adjustment.ClinicNum,"
+ "SUM(adjustment.AdjAmt) Adjustment "
+ "FROM adjustment "
+ "WHERE adjustment.AdjDate >= " + POut.Date(dateFrom) + " "
+ "AND adjustment.AdjDate <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "GROUP BY ClinicNum,YEAR(adjustment.AdjDate),MONTH(adjustment.AdjDate),DAY(adjustment.AdjDate)";
command += " ORDER BY ClinicNum,AdjDate";
DataTable tableAdj = new DataTable();
if (isCEMT)
{
tableAdj = Db.GetTable(command);
}
else
{
tableAdj = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableAdj.TableName = "tableAdj";
#endregion
#region TableInsWriteoff
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND claimproc.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND claimproc.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (writeoffPayType == PPOWriteoffDateCalc.InsPayDate)
{
command = "SELECT "
+ "claimproc.DateCP Date,"
+ "claimproc.ClinicNum,"
+ "SUM(claimproc.WriteOff) WriteOff "
+ "FROM claimproc "
+ "WHERE claimproc.DateCP >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.DateCP <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + ") "//received or supplemental
+ "GROUP BY ClinicNum,DATE(claimproc.DateCP) "
+ "ORDER BY ClinicNum,DateCP";
}
else if (writeoffPayType == PPOWriteoffDateCalc.ProcDate)
{
command = "SELECT "
+ "claimproc.ProcDate Date,"
+ "claimproc.ClinicNum,"
+ "SUM(claimproc.WriteOff) WriteOff "
+ "FROM claimproc "
+ "WHERE claimproc.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.ProcDate <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "//received or supplemental or notreceived
+ "GROUP BY ClinicNum,DATE(claimproc.ProcDate) "
+ "ORDER BY ClinicNum,ProcDate";
}
else // writeoffPayType==PPOWriteoffDateCalc.ClaimPayDate
{
command = "SELECT "
+ "claimsnapshot.DateTEntry Date,"
+ "claimproc.ClinicNum,"
+ "SUM(CASE WHEN claimsnapshot.WriteOff=-1 THEN 0 ELSE claimsnapshot.WriteOff END) WriteOff "
+ "FROM claimproc "
+ "INNER JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimProc.ClaimProcNum "
+ "WHERE " + DbHelper.BetweenDates("claimsnapshot.DateTEntry", dateFrom, dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "//received or supplemental or notreceived
+ "GROUP BY ClinicNum,DATE(claimsnapshot.DateTEntry) "
+ "ORDER BY ClinicNum,claimsnapshot.DateTEntry";
}
DataTable tableInsWriteoff = new DataTable();
if (isCEMT)
{
tableInsWriteoff = Db.GetTable(command);
}
else
{
tableInsWriteoff = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableInsWriteoff.TableName = "tableInsWriteoff";
#endregion
#region TableSched
DataTable tableSched = new DataTable();
//Reads from the procedurelog table instead of claimproc because we are looking for scheduled procedures.
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
command = "SELECT " + DbHelper.DtimeToDate("t.AptDateTime") + " SchedDate,SUM(t.Fee-t.WriteoffEstimate) Amount,ClinicNum "
+ "FROM (SELECT appointment.AptDateTime,IFNULL(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits),0) Fee,appointment.ClinicNum,";
if (ReportsComplex.RunFuncOnReportServer(() => Prefs.GetBoolNoCache(PrefName.ReportPandIschedProdSubtractsWO)))
{
//Subtract both PPO and capitation writeoffs
command += "SUM(IFNULL(CASE WHEN WriteOffEstOverride != -1 THEN WriteOffEstOverride ELSE WriteOffEst END,0)) WriteoffEstimate ";
}
else
{
//Always subtract CapEstimate writeoffs from scheduled production. This is so that the scheduled production will match actual production
//when the procedures are set complete. Nathan decided this 01/05/2017.
command += "SUM(IFNULL((CASE WHEN claimproc.Status=" + POut.Int((int)ClaimProcStatus.Estimate) + " THEN 0 "
+ "WHEN WriteOffEstOverride != -1 THEN WriteOffEstOverride ELSE WriteOffEst END),0)) WriteoffEstimate ";
}
command += "FROM appointment "
+ "LEFT JOIN procedurelog ON appointment.AptNum = procedurelog.AptNum AND procedurelog.ProcStatus=" + POut.Int((int)ProcStat.TP) + " "
+ "LEFT JOIN claimproc ON procedurelog.ProcNum = claimproc.ProcNum "
+ "AND claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Estimate) + "," + POut.Int((int)ClaimProcStatus.CapEstimate) + ") "
+ " AND (WriteOffEst != -1 OR WriteOffEstOverride != -1) "
+ "WHERE appointment.AptStatus = " + POut.Int((int)ApptStatus.Scheduled) + " "
+ "AND " + DbHelper.DtimeToDate("appointment.AptDateTime") + " >= " + POut.Date(dateFrom) + " "
+ "AND " + DbHelper.DtimeToDate("appointment.AptDateTime") + " <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ " GROUP BY procedurelog.ProcNum) t " //without this, there can be duplicate proc rows due to the claimproc join with dual insurance.
+ "GROUP BY SchedDate,ClinicNum "
+ "ORDER BY SchedDate";
if (isCEMT)
{
tableSched = Db.GetTable(command);
}
else
{
tableSched = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableSched.TableName = "tableSched";
#endregion
#region TableProdGoal
//1. Find all schedules for the month
//2. ClinicNum will come from the schedule's operatory
//3. Fetch HourlyProdGoalAmt from provider on the schedule
//4. Sum scheduled hours, grouped by prov and clinic
//5. Multiply the scheduled hours by the provider's HourlyProdGoalAmt
DataTable tableProdGoal = new DataTable();
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND schedule.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND COALESCE(operatory.ClinicNum,0) IN (" + string.Join(",", listClinicNums) + ") ";
}
//Fetch all schedules for the month and associated information (clinic from operatory, HourlyProdGoalAmt from provider)
command = "SELECT " + DbHelper.DtimeToDate("schedule.SchedDate") + @" AS SchedDate, schedule.StartTime AS StartTime, schedule.StopTime AS StopTime,
COALESCE(operatory.ClinicNum,0) AS ClinicNum, provider.HourlyProdGoalAmt AS ProvProdGoal, provider.ProvNum AS ProvNum
FROM schedule
INNER JOIN provider ON provider.ProvNum=schedule.ProvNum
LEFT JOIN scheduleop ON scheduleop.ScheduleNum=schedule.ScheduleNum
LEFT JOIN operatory ON scheduleop.OperatoryNum=operatory.OperatoryNum
WHERE schedule.SchedType=" + POut.Int((int)ScheduleType.Provider) + " "
+ "AND schedule.Status=" + POut.Int((int)SchedStatus.Open) + " "
+ "AND schedule." + DbHelper.BetweenDates("SchedDate", dateFrom, dateTo) + " "
+ whereProv
+ whereClin;
if (isCEMT)
{
tableProdGoal = Db.GetTable(command);
}
else
{
tableProdGoal = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableProdGoal.TableName = "tableProdGoal";
#endregion
#region WriteOffAdjustments
DataTable tableWriteOffAdjustments = new DataTable();
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND claimproc.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND claimproc.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (writeoffPayType == PPOWriteoffDateCalc.ClaimPayDate)
{
//Insurance WriteOff Adjustments----------------------------------------------------------------------------
command = $@"SELECT claimproc.DateCP Date,claimproc.ClinicNum,
-SUM(CASE WHEN COALESCE(claimsnapshot.WriteOff,-1)=-1 THEN 0 ELSE claimsnapshot.WriteOff END) WriteOffEst,
SUM(claimproc.WriteOff) WriteOff,
claimproc.ClaimNum
FROM claimproc
LEFT JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimproc.ClaimProcNum
WHERE claimproc.DateCP BETWEEN {POut.Date(dateFrom)} AND {POut.Date(dateTo)}
AND claimproc.Status IN ({(int)ClaimProcStatus.Received},{(int)ClaimProcStatus.Supplemental})
{whereProv}
{whereClin}
GROUP BY ClinicNum,DATE(claimproc.DateCP)" ;
if (isCEMT)
{
tableWriteOffAdjustments = Db.GetTable(command);
}
else
{
tableWriteOffAdjustments = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
}
tableWriteOffAdjustments.TableName = "tableWriteOffAdjustments";
#endregion WriteOffAdjustments
DataSet dataSet = new DataSet();
dataSet.Tables.Add(tableProduction);
dataSet.Tables.Add(tableAdj);
dataSet.Tables.Add(tableInsWriteoff);
dataSet.Tables.Add(tableWriteOffAdjustments);
dataSet.Tables.Add(tableSched);
dataSet.Tables.Add(tableProdGoal);
return(dataSet);
}
///<summary>If not using clinics then supply an empty list of clinicNums.</summary>
public static DataSet GetPayPlanTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums,
bool hasAllProvs, DisplayPayPlanType displayPayPlanType, bool hideCompletedPlans, bool showFamilyBalance, bool hasDateRange, bool isPayPlanV2)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDS(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, hasAllProvs, displayPayPlanType,
hideCompletedPlans, showFamilyBalance, hasDateRange, isPayPlanV2));
}
string whereProv = "";
if (!hasAllProvs)
{
whereProv += " AND payplancharge.ProvNum IN(";
for (int i = 0; i < listProvNums.Count; i++)
{
if (i > 0)
{
whereProv += ",";
}
whereProv += POut.Long(listProvNums[i]);
}
whereProv += ") ";
}
string whereClin = "";
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)));
if (hasClinicsEnabled) //Using clinics
{
whereClin += " AND payplancharge.ClinicNum IN(";
for (int i = 0; i < listClinicNums.Count; i++)
{
if (i > 0)
{
whereClin += ",";
}
whereClin += POut.Long(listClinicNums[i]);
}
whereClin += ") ";
}
DataSet ds = new DataSet();
DataTable table = new DataTable("Clinic");
table.Columns.Add("provider");
table.Columns.Add("guarantor");
table.Columns.Add("ins");
table.Columns.Add("princ");
table.Columns.Add("accumInt");
table.Columns.Add("paid");
table.Columns.Add("balance");
table.Columns.Add("due");
if (isPayPlanV2)
{
table.Columns.Add("notDue");
}
table.Columns.Add("famBal");
table.Columns.Add("clinicName");
DataTable tableTotals = new DataTable("Total");
tableTotals.Columns.Add("clinicName");
tableTotals.Columns.Add("princ");
tableTotals.Columns.Add("accumInt");
tableTotals.Columns.Add("paid");
tableTotals.Columns.Add("balance");
tableTotals.Columns.Add("due");
if (isPayPlanV2)
{
tableTotals.Columns.Add("notDue");
}
tableTotals.Columns.Add("famBal");
DataRow row;
string datesql = "CURDATE()"; //This is used to find out how much people owe currently and has nothing to do with the selected range
if (DataConnection.DBtype == DatabaseType.Oracle)
{
datesql = "(SELECT CURRENT_DATE FROM dual)";
}
//Oracle TODO: Either put entire query without GROUP BY in SUBSELECT and then GROUP BY outside, or rewrite query to use joins instead of subselects.
string command = "SELECT FName,LName,MiddleI,PlanNum,Preferred,PlanNum, "
+ "COALESCE((SELECT SUM(Principal+Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " "//for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_accumDue', ";
command += "COALESCE((SELECT SUM(Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " " //for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_accumInt', ";
command += "COALESCE((SELECT SUM(SplitAmt) FROM paysplit WHERE paysplit.PayPlanNum=payplan.PayPlanNum AND paysplit.PayPlanNum!=0),0) '_paid', ";
command += "COALESCE((SELECT SUM(InsPayAmt) FROM claimproc WHERE claimproc.PayPlanNum=payplan.PayPlanNum "
+ "AND claimproc.Status IN("
+ POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapClaim)
+ ") AND claimproc.PayPlanNum!=0),0) '_insPaid', ";
command += "COALESCE((SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + "),0) '_principal', " //for v1, debits are the only ChargeType.
+ "COALESCE((SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Credit) + "),0) '_credits', " //for v1, will always be 0.
+ "COALESCE((SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Credit) + " AND ChargeDate > " + datesql + "),0) '_notDue', "
+ "patient.PatNum PatNum, "
+ "payplancharge.ProvNum ProvNum ";
if (hasClinicsEnabled)
{
command += ", payplancharge.ClinicNum ClinicNum ";
}
//In order to determine if the patient has completely paid off their payment plan we need to get the total amount of interest as of today.
//Then, after the query has run, we'll add the interest up until today with the total principal for the entire payment plan.
//For this reason, we cannot use _accumDue which only gets the principle up until today and not the entire payment plan principle.
command += ",COALESCE((SELECT SUM(Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " " //for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_interest' "
+ "FROM payplan "
+ "LEFT JOIN patient ON patient.PatNum=payplan.Guarantor "
+ "LEFT JOIN payplancharge ON payplan.PayPlanNum=payplancharge.PayPlanNum "
+ "WHERE TRUE "; //Always include true, so that the WHERE clause may always be present.
if (hasDateRange)
{
command += "AND payplan.PayPlanDate >= " + POut.Date(dateStart) + " "
+ "AND payplan.PayPlanDate <= " + POut.Date(dateEnd) + " ";
}
command += whereProv
+ whereClin;
if (displayPayPlanType == DisplayPayPlanType.Insurance)
{
command += "AND payplan.PlanNum!=0 ";
}
else if (displayPayPlanType == DisplayPayPlanType.Patient)
{
command += "AND payplan.PlanNum=0 ";
}
else if (displayPayPlanType == DisplayPayPlanType.Both)
{
//Do not filter the query at all which will show both insurance and patient payment plan types.
}
if (hideCompletedPlans)
{
command += "AND payplan.IsClosed=0 ";
}
command += "GROUP BY FName,LName,MiddleI,Preferred,payplan.PayPlanNum ";
if (hasClinicsEnabled)
{
command += "ORDER BY ClinicNum,LName,FName";
}
else
{
command += "ORDER BY LName,FName";
}
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
List <Provider> listProvs = ReportsComplex.RunFuncOnReportServer(() => Providers.GetAll());
//DateTime payplanDate;
Patient pat;
double princ;
double paid;
double interest;
double accumDue;
double notDue;
decimal famBal = 0;
double princTot = 0;
double paidTot = 0;
double interestTot = 0;
double balanceTot = 0;
double accumDueTot = 0;
double notDueTot = 0;
decimal famBalTot = 0;
string clinicDescOld = "";
for (int i = 0; i < raw.Rows.Count; i++)
{
princ = PIn.Double(raw.Rows[i]["_principal"].ToString());
interest = PIn.Double(raw.Rows[i]["_accumInt"].ToString());
if (raw.Rows[i]["PlanNum"].ToString() == "0") //pat payplan
{
paid = PIn.Double(raw.Rows[i]["_paid"].ToString());
}
else //ins payplan
{
paid = PIn.Double(raw.Rows[i]["_insPaid"].ToString());
}
accumDue = PIn.Double(raw.Rows[i]["_accumDue"].ToString());
notDue = PIn.Double(raw.Rows[i]["_notDue"].ToString());
row = table.NewRow();
//payplanDate=PIn.PDate(raw.Rows[i]["PayPlanDate"].ToString());
//row["date"]=raw.Rows[i]["PayPlanDate"].ToString();//payplanDate.ToShortDateString();
pat = new Patient();
pat.LName = raw.Rows[i]["LName"].ToString();
pat.FName = raw.Rows[i]["FName"].ToString();
pat.MiddleI = raw.Rows[i]["MiddleI"].ToString();
pat.Preferred = raw.Rows[i]["Preferred"].ToString();
row["provider"] = Providers.GetLName(PIn.Long(raw.Rows[i]["ProvNum"].ToString()), listProvs);
row["guarantor"] = pat.GetNameLF();
if (raw.Rows[i]["PlanNum"].ToString() == "0")
{
row["ins"] = "";
}
else
{
row["ins"] = "X";
}
row["princ"] = princ.ToString("f");
row["accumInt"] = interest.ToString("f");
row["paid"] = paid.ToString("f");
row["balance"] = (princ + interest - paid).ToString("f");
row["due"] = (accumDue - paid).ToString("f");
if (isPayPlanV2)
{
row["notDue"] = ((princ + interest - paid) - (accumDue - paid)).ToString("f");
}
if (showFamilyBalance)
{
Family famCur = ReportsComplex.RunFuncOnReportServer(() => Patients.GetFamily(PIn.Long(raw.Rows[i]["PatNum"].ToString())));
famBal = (decimal)famCur.ListPats[0].BalTotal;
row["famBal"] = (famBal - (decimal)famCur.ListPats[0].InsEst).ToString("F");
}
if (hasClinicsEnabled) //Using clinics
{
List <Clinic> listClinics = ReportsComplex.RunFuncOnReportServer(() => Clinics.GetClinicsNoCache());
string clinicDesc = Clinics.GetDesc(PIn.Long(raw.Rows[i]["ClinicNum"].ToString()), listClinics);
clinicDesc = (clinicDesc == "")?Lans.g("FormRpPayPlans", "Unassigned"):clinicDesc;
if (!String.IsNullOrEmpty(clinicDescOld) && clinicDesc != clinicDescOld) //Reset all the total values
{
DataRow rowTot = tableTotals.NewRow();
rowTot["clinicName"] = clinicDescOld;
rowTot["princ"] = princTot.ToString();
rowTot["accumInt"] = interestTot.ToString();
rowTot["paid"] = paidTot.ToString();
rowTot["balance"] = balanceTot.ToString();
rowTot["due"] = accumDueTot.ToString();
if (isPayPlanV2)
{
rowTot["notDue"] = notDueTot.ToString();
}
rowTot["famBal"] = famBalTot.ToString();
tableTotals.Rows.Add(rowTot);
princTot = 0;
paidTot = 0;
interestTot = 0;
accumDueTot = 0;
balanceTot = 0;
notDueTot = 0;
famBalTot = 0;
}
row["clinicName"] = clinicDesc;
clinicDescOld = clinicDesc;
princTot += princ;
paidTot += paid;
interestTot += interest;
accumDueTot += (accumDue - paid);
balanceTot += (princ + interest - paid);
notDueTot += ((princ + interest - paid) - (accumDue - paid));
famBalTot += famBal;
if (i == raw.Rows.Count - 1)
{
DataRow rowTot = tableTotals.NewRow();
rowTot["clinicName"] = clinicDescOld;
rowTot["princ"] = princTot.ToString();
rowTot["accumInt"] = interestTot.ToString();
rowTot["paid"] = paidTot.ToString();
rowTot["balance"] = balanceTot.ToString();
rowTot["due"] = accumDueTot.ToString();
if (isPayPlanV2)
{
rowTot["notDue"] = notDueTot.ToString();
}
rowTot["famBal"] = famBalTot.ToString();
tableTotals.Rows.Add(rowTot);
}
}
table.Rows.Add(row);
}
ds.Tables.Add(table);
ds.Tables.Add(tableTotals);
return(ds);
}
///<summary>If ClientWeb, then this method is instead run on the server, and the result passed back to the client. And since it's ClientWeb, FillCache will be run on the client.</summary>
public static DataSet GetCacheDs(string itypesStr)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDS(MethodBase.GetCurrentMethod(), itypesStr));
}
//so this part below only happens if direct or server------------------------------------------------
List <int> itypes = new List <int>();
string[] strArray = itypesStr.Split(',');
for (int i = 0; i < strArray.Length; i++)
{
itypes.Add(PIn.Int(strArray[i]));
}
bool isAll = false;
if (itypes.Contains((int)InvalidType.AllLocal))
{
isAll = true;
}
DataSet ds = new DataSet();
if (itypes.Contains((int)InvalidType.AccountingAutoPays) || isAll)
{
ds.Tables.Add(AccountingAutoPays.RefreshCache());
}
if (itypes.Contains((int)InvalidType.AutoCodes) || isAll)
{
ds.Tables.Add(AutoCodes.RefreshCache());
ds.Tables.Add(AutoCodeItems.RefreshCache());
ds.Tables.Add(AutoCodeConds.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Automation) || isAll)
{
ds.Tables.Add(Automations.RefreshCache());
}
if (itypes.Contains((int)InvalidType.AutoNotes) || isAll)
{
ds.Tables.Add(AutoNotes.RefreshCache());
ds.Tables.Add(AutoNoteControls.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Carriers) || isAll)
{
ds.Tables.Add(Carriers.RefreshCache()); //run on startup, after telephone reformat, after list edit.
}
if (itypes.Contains((int)InvalidType.ClaimForms) || isAll)
{
ds.Tables.Add(ClaimFormItems.RefreshCache());
ds.Tables.Add(ClaimForms.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ClearHouses) || isAll)
{
ds.Tables.Add(Clearinghouses.RefreshCache()); //kh wants to add an EasyHideClearHouses to disable this
}
if (itypes.Contains((int)InvalidType.Computers) || isAll)
{
ds.Tables.Add(Computers.RefreshCache());
ds.Tables.Add(Printers.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Defs) || isAll)
{
ds.Tables.Add(Defs.RefreshCache());
}
if (itypes.Contains((int)InvalidType.DentalSchools) || isAll)
{
ds.Tables.Add(SchoolClasses.RefreshCache());
ds.Tables.Add(SchoolCourses.RefreshCache());
}
if (itypes.Contains((int)InvalidType.DictCustoms) || isAll)
{
ds.Tables.Add(DictCustoms.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Diseases) || isAll)
{
ds.Tables.Add(DiseaseDefs.RefreshCache());
ds.Tables.Add(ICD9s.RefreshCache());
}
if (itypes.Contains((int)InvalidType.DisplayFields) || isAll)
{
ds.Tables.Add(DisplayFields.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ElectIDs) || isAll)
{
ds.Tables.Add(ElectIDs.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Email) || isAll)
{
ds.Tables.Add(EmailAddresses.RefreshCache());
ds.Tables.Add(EmailTemplates.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Employees) || isAll)
{
ds.Tables.Add(Employees.RefreshCache());
ds.Tables.Add(PayPeriods.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Employers) || isAll)
{
ds.Tables.Add(Employers.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Fees) || isAll)
{
ds.Tables.Add(Fees.RefreshCache());
}
if (itypes.Contains((int)InvalidType.FeeScheds) || isAll)
{
ds.Tables.Add(FeeScheds.RefreshCache());
}
if (itypes.Contains((int)InvalidType.HL7Defs) || isAll)
{
ds.Tables.Add(HL7Defs.RefreshCache());
ds.Tables.Add(HL7DefMessages.RefreshCache());
ds.Tables.Add(HL7DefSegments.RefreshCache());
ds.Tables.Add(HL7DefFields.RefreshCache());
}
if (itypes.Contains((int)InvalidType.InsCats) || isAll)
{
ds.Tables.Add(CovCats.RefreshCache());
ds.Tables.Add(CovSpans.RefreshCache());
}
if (itypes.Contains((int)InvalidType.InsFilingCodes) || isAll)
{
ds.Tables.Add(InsFilingCodes.RefreshCache());
ds.Tables.Add(InsFilingCodeSubtypes.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Languages) || isAll)
{
if (CultureInfo.CurrentCulture.Name != "en-US")
{
ds.Tables.Add(Lans.RefreshCache());
}
}
if (itypes.Contains((int)InvalidType.Letters) || isAll)
{
ds.Tables.Add(Letters.RefreshCache());
}
if (itypes.Contains((int)InvalidType.LetterMerge) || isAll)
{
ds.Tables.Add(LetterMergeFields.RefreshCache());
ds.Tables.Add(LetterMerges.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Operatories) || isAll)
{
ds.Tables.Add(Operatories.RefreshCache());
}
if (itypes.Contains((int)InvalidType.PatFields) || isAll)
{
ds.Tables.Add(PatFieldDefs.RefreshCache());
ds.Tables.Add(ApptFieldDefs.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Pharmacies) || isAll)
{
ds.Tables.Add(Pharmacies.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Prefs) || isAll)
{
ds.Tables.Add(Prefs.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ProcButtons) || isAll)
{
ds.Tables.Add(ProcButtons.RefreshCache());
ds.Tables.Add(ProcButtonItems.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ProcCodes) || isAll)
{
ds.Tables.Add(ProcedureCodes.RefreshCache());
ds.Tables.Add(ProcCodeNotes.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Programs) || isAll)
{
ds.Tables.Add(Programs.RefreshCache());
ds.Tables.Add(ProgramProperties.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ProviderIdents) || isAll)
{
ds.Tables.Add(ProviderIdents.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Providers) || isAll)
{
ds.Tables.Add(Providers.RefreshCache());
}
if (itypes.Contains((int)InvalidType.QuickPaste) || isAll)
{
ds.Tables.Add(QuickPasteNotes.RefreshCache());
ds.Tables.Add(QuickPasteCats.RefreshCache());
}
if (itypes.Contains((int)InvalidType.RecallTypes) || isAll)
{
ds.Tables.Add(RecallTypes.RefreshCache());
ds.Tables.Add(RecallTriggers.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ReplicationServers) || isAll)
{
ds.Tables.Add(ReplicationServers.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Security) || isAll)
{
ds.Tables.Add(Userods.RefreshCache());
ds.Tables.Add(UserGroups.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Sheets) || isAll)
{
ds.Tables.Add(SheetDefs.RefreshCache());
ds.Tables.Add(SheetFieldDefs.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Signals) || isAll)
{
ds.Tables.Add(SigElementDefs.RefreshCache());
ds.Tables.Add(SigButDefs.RefreshCache()); //includes SigButDefElements.Refresh()
}
if (itypes.Contains((int)InvalidType.Sites) || isAll)
{
ds.Tables.Add(Sites.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Sops) || isAll)
{
ds.Tables.Add(Sops.RefreshCache());
}
//InvalidTypes.Tasks not handled here.
if (itypes.Contains((int)InvalidType.TimeCardRules) || isAll)
{
ds.Tables.Add(TimeCardRules.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ToolBut) || isAll)
{
ds.Tables.Add(ToolButItems.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Vaccines) || isAll)
{
ds.Tables.Add(VaccineDefs.RefreshCache());
ds.Tables.Add(DrugManufacturers.RefreshCache());
ds.Tables.Add(DrugUnits.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Views) || isAll)
{
ds.Tables.Add(ApptViews.RefreshCache());
ds.Tables.Add(ApptViewItems.RefreshCache());
ds.Tables.Add(AppointmentRules.RefreshCache());
ds.Tables.Add(ProcApptColors.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Wiki) || isAll)
{
ds.Tables.Add(WikiListHeaderWidths.RefreshCache());
ds.Tables.Add(WikiPages.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ZipCodes) || isAll)
{
ds.Tables.Add(ZipCodes.RefreshCache());
}
return(ds);
}
