public static DataTable GetData(long feeSchedNum, long clinicNum, long provNum, bool isCategories, bool includeBlanks)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), feeSchedNum, clinicNum, provNum, isCategories, includeBlanks));
}
DataTable data = GetDataSet(feeSchedNum, clinicNum, provNum);
DataTable retVal = new DataTable("ProcCodes");
if (isCategories)
{
retVal.Columns.Add(new DataColumn("Category"));
}
retVal.Columns.Add(new DataColumn("Code"));
retVal.Columns.Add(new DataColumn("Desc"));
retVal.Columns.Add(new DataColumn("Abbr"));
retVal.Columns.Add(new DataColumn("Fee"));
List <ProcedureCode> listProcCodes = new List <ProcedureCode>();
if (isCategories)
{
Def[][] arrayDefs = ReportsComplex.RunFuncOnReportServer(() => Defs.GetArrayShortNoCache());
listProcCodes = ReportsComplex.RunFuncOnReportServer(() => ProcedureCodes.GetProcList(arrayDefs))
.OrderBy(x => x.ProcCat).ThenBy(x => x.ProcCode).ToList(); //Ordered by category
}
else
{
listProcCodes = ReportsComplex.RunFuncOnReportServer(() => ProcedureCodes.GetAllCodes()); //Ordered by ProcCode, used for the non-category version of the report if they want blanks.
}
bool isFound;
List <Def> listDefs = Defs.GetDefsNoCache(DefCat.ProcCodeCats);
for (int i = 0; i < listProcCodes.Count; i++)
{
isFound = false;
DataRow row = retVal.NewRow();
if (isCategories)
{
//reports should no longer use the cache.
Def def = listDefs.FirstOrDefault(x => x.DefNum == listProcCodes[i].ProcCat);
row[0] = def == null ? "" : def.ItemName;
row[1] = listProcCodes[i].ProcCode;
row[2] = listProcCodes[i].Descript;
row[3] = listProcCodes[i].AbbrDesc;
}
else
{
row[0] = listProcCodes[i].ProcCode;
row[1] = listProcCodes[i].Descript;
row[2] = listProcCodes[i].AbbrDesc;
}
for (int j = 0; j < data.Rows.Count; j++)
{
if (data.Rows[j]["ProcCode"].ToString() == listProcCodes[i].ProcCode)
{
isFound = true;
double amt = PIn.Double(data.Rows[j]["Amount"].ToString());
if (isCategories)
{
if (amt == -1)
{
row[4] = "";
isFound = false;
}
else
{
row[4] = amt.ToString("n");
}
}
else
{
if (amt == -1)
{
row[3] = "";
isFound = false;
}
else
{
row[3] = amt.ToString("n");
}
}
break;
}
}
if (includeBlanks && !isFound)
{
retVal.Rows.Add(row); //Including a row that has a blank fee.
}
else if (isFound)
{
retVal.Rows.Add(row);
}
//All other rows (empty rows where we don't want blanks) are not added to the dataset.
}
return(retVal);
}
///<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);
}
///<summary>If not using clinics, or for all clinics with clinics enabled, supply an empty list of clinicNums. If the user is restricted, for all
///clinics supply only those clinics for which the user has permission to access, otherwise it will be run for all clinics.</summary>
public static DataTable GetPatTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, List <long> listPatientTypes,
bool hasAllProvs, bool hasAllClinics, bool hasPatientTypes, bool isGroupedByPatient, bool isUnearnedIncluded, bool doShowProvSeparate,
bool doShowHiddenTPUnearned)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listPatientTypes, hasAllProvs, hasAllClinics,
hasPatientTypes, isGroupedByPatient, isUnearnedIncluded, doShowProvSeparate, doShowHiddenTPUnearned));
}
//reports should no longer use the cache
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
List <long> listHiddenUnearnedDefNums = new List <long>();
if (!doShowHiddenTPUnearned)
{
listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
}
//patient payments-----------------------------------------------------------------------------------------
//the selected columns have to remain in this order due to the way the report complex populates the returned sheet
string queryPat = "SELECT payment.PayDate DatePay,"
+ "MAX(" + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ") lfname,GROUP_CONCAT(DISTINCT provider.Abbr),";
if (hasClinicsEnabled)
{
queryPat += "clinic.Abbr clinicAbbr,";
}
queryPat += "payment.CheckNum,SUM(COALESCE(paysplit.SplitAmt,0)) amt,payment.PayNum,ItemName,payment.PayType "
+ "FROM payment "
+ "LEFT JOIN paysplit ON payment.PayNum=paysplit.PayNum "
+ "LEFT JOIN patient ON payment.PatNum=patient.PatNum "
+ "LEFT JOIN provider ON paysplit.ProvNum=provider.ProvNum "
+ "LEFT JOIN definition ON payment.PayType=definition.DefNum ";
if (hasClinicsEnabled)
{
queryPat += "LEFT JOIN clinic ON clinic.ClinicNum=paysplit.ClinicNum ";
}
queryPat += "WHERE payment.PayDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " ";
if (hasClinicsEnabled && listClinicNums.Count > 0)
{
queryPat += "AND paysplit.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
if (!hasAllProvs && listProvNums.Count > 0)
{
queryPat += "AND paysplit.ProvNum IN(" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") ";
}
if (!hasPatientTypes && listPatientTypes.Count > 0)
{
queryPat += "AND payment.PayType IN (" + string.Join(",", listPatientTypes.Select(x => POut.Long(x))) + ") ";
}
if (!isUnearnedIncluded) //UnearnedType of 0 means the paysplit is NOT unearned
{
queryPat += "AND paysplit.UnearnedType=0 ";
}
else if (listHiddenUnearnedDefNums.Count > 0 && !doShowHiddenTPUnearned) //Include unearned but not of the TP type.
{
queryPat += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
queryPat += "GROUP BY payment.PayNum,payment.PayDate,payment.CheckNum,definition.ItemName,payment.PayType ";
if (doShowProvSeparate)
{
queryPat += ",provider.ProvNum ";
}
if (hasClinicsEnabled)
{
queryPat += ",clinic.Abbr ";
}
if (isGroupedByPatient)
{
queryPat += ",patient.PatNum ";
}
queryPat += "ORDER BY payment.PayType,payment.PayDate,lfname";
if (!hasPatientTypes && listPatientTypes.Count == 0)
{
queryPat = DbHelper.LimitOrderBy(queryPat, 0);
}
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryPat)));
}
/// <summary>Retrieves the Unearned Accounts dataset from the database.</summary>
/// <param name="listClinics">The list of clinics to filter by. Pass in an empty list if this should not be filtered by clinic.</param>
/// <returns></returns>
public static DataTable GetUnearnedAccountData(List <long> listClinics)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinics));
}
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
string command = "";
string whereClin = "";
//We used to get original paysplits based on FSplitNum being 0 and having an unearned type and then get the offsetting splits from the
//FSplitNum matching the original paysplit num.
//FSplitNums no longer are important when calculating unearned so they are no included in this logic intentionally.
//The patient table joins are quite slow for large customers, which is why they were moved outside the FROM.
//If a customer complains we might do some logic to get the patnums of any family member in that clinic first, so we can filter down the unions.
if (listClinics.Count > 0)
{
whereClin = "WHERE guar.ClinicNum IN (" + string.Join(",", listClinics) + ") ";
}
command = "SELECT " + DbHelper.Concat("guar.LName", "', '", "guar.FName", "' '", "guar.MiddleI") + ",";
command += DbHelper.GroupConcat("definition.ItemName", true, true, ",");
if (hasClinicsEnabled)
{
command += ",clinic.Abbr";
}
command += ",SUM(splits.Amt) Amount FROM (";
//Prepay. Unearned is simply defined as having an unearned type set. Nothing more.
command += "SELECT paysplit.PatNum, paysplit.SplitAmt Amt,paysplit.UnearnedType "
+ "FROM paysplit "
+ "WHERE paysplit.UnearnedType!=0 ";
if (listHiddenUnearnedDefNums.Count > 0)
{
command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
command += "GROUP BY paysplit.SplitNum";
command += ") splits "
+ "INNER JOIN patient ON patient.PatNum=splits.PatNum "
+ "INNER JOIN patient guar ON guar.PatNum=patient.Guarantor "
+ "LEFT JOIN definition ON definition.DefNum=splits.UnearnedType ";
if (hasClinicsEnabled)
{
command += "LEFT JOIN clinic ON clinic.ClinicNum=guar.ClinicNum ";
}
command += whereClin;
command += "GROUP BY guar.PatNum HAVING ABS(Amount) > 0.005 "; //still won't work for oracle
command += "ORDER BY guar.LName, guar.FName, guar.MiddleI, Amount";
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
return(raw);
}
public static DataTable GetUnearnedAllocationData(List <long> listClinicNums, List <long> listProvNums,
List <long> listUnearnedTypeNums, bool isExcludeNetZeroUnearned, bool showProvider)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, listProvNums, listUnearnedTypeNums, isExcludeNetZeroUnearned, showProvider));
}
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
//get all families that have an unallocated unearned balance.
//from those, remove families that have not had procedures charted/completed after the unearned amount.
//All families
//DatePay = the earliest date of unallocated unearned.
//Unallocated Amt = the total unallocated amt for the patient.
string command = $@"
SELECT patient.Guarantor, MIN(paysplit.DatePay) DatePay, SUM(paysplit.SplitAmt) UnallocAmt{(showProvider ? ", provider.Abbr" : "")}
FROM paysplit
INNER JOIN patient ON patient.PatNum = paysplit.PatNum " ;
if (listClinicNums.Count > 0 || listProvNums.Count > 0)
{
command += "INNER JOIN patient guar ON guar.PatNum = patient.Guarantor ";
if (listClinicNums.Count > 0)
{
command += "AND guar.ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
if (listProvNums.Count > 0)
{
command += "AND guar.PriProv IN (" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") ";
}
}
if (showProvider)
{
command += "LEFT JOIN provider ON provider.ProvNum = paysplit.ProvNum ";
}
command += "WHERE paysplit.UnearnedType != 0 ";
if (listUnearnedTypeNums.Count > 0)
{
command += "AND paysplit.UnearnedType IN (" + string.Join(",", listUnearnedTypeNums.Select(x => POut.Long(x))) + ") ";
}
if (listHiddenUnearnedDefNums.Count > 0)
{
command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
command += $"GROUP BY patient.Guarantor{(showProvider ? ", provider.Abbr" : "")} ";
if (isExcludeNetZeroUnearned)
{
command += "HAVING ABS(UnallocAmt) > 0.005 ";
}
//one row per family
DataTable tableUnallocatedUnearned = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
List <long> listGuarantors = tableUnallocatedUnearned.Rows.OfType <DataRow>().Select(x => PIn.Long(x["Guarantor"].ToString())).ToList();
//all procedures for the families that have not been explicitly paid off.
//Key: GuarantorNum | Val:ListRemainingProcsForFam
List <UnearnedProc> listRemProcs = ReportsComplex.RunFuncOnReportServer(() => Procedures.GetRemainingProcsForFamilies(listGuarantors));
Dictionary <long, List <UnearnedProc> > dictFamRemainingProcs = listRemProcs.GroupBy(x => x.GuarNum)
.ToDictionary(x => x.Key, y => y.ToList());
Dictionary <long, double> dictFamilyBalances = ReportsComplex.RunFuncOnReportServer(() => Ledgers.GetBalancesForFamilies(listGuarantors));
Dictionary <long, string> dictPatNames = ReportsComplex.RunFuncOnReportServer(() =>
Patients.GetPatientNames(Patients.GetAllFamilyPatNums(listGuarantors)));
List <ProcedureCode> listProcCodes = ReportsComplex.RunFuncOnReportServer(() => ProcedureCodes.GetAllCodes());
DataTable retVal = new DataTable();
retVal.Columns.Add("Guar");
retVal.Columns.Add("FamBal");
retVal.Columns.Add("FamUnearned");
retVal.Columns.Add("FamRemAmt");
if (showProvider)
{
retVal.Columns.Add("Prov");
}
retVal.Columns.Add("Patient");
retVal.Columns.Add("Code");
retVal.Columns.Add("Date");
retVal.Columns.Add("Fee");
retVal.Columns.Add("RemAmt");
int rowCount = tableUnallocatedUnearned.Rows.Count; //For brevity
//This has to be a for-loop instead of foreach so we can access the guarantor number from the next iteration
//prior to adding the procedures to the report (to validate whether or not we should add another guarantor row
//for a provider
for (int i = 0; i < rowCount; i++)
{
DataRow guarRowCur = tableUnallocatedUnearned.Rows[i];
int nextIndex = i + 1;
long guarNum = PIn.Long(guarRowCur["Guarantor"].ToString());
DateTime dateFirstUnalloc = PIn.Date(guarRowCur["DatePay"].ToString());
double unallocAmt = PIn.Double(guarRowCur["UnallocAmt"].ToString());
List <UnearnedProc> listUnearnedProcsForGuar;
if (!dictFamRemainingProcs.TryGetValue(guarNum, out listUnearnedProcsForGuar))
{
continue; //This family does not have any procedures that need to have money allocated to.
}
listUnearnedProcsForGuar = listUnearnedProcsForGuar.Where(x => x.Proc.ProcDate >= dateFirstUnalloc).OrderBy(x => x.Proc.ProcDate).ToList();
if (listUnearnedProcsForGuar.Count == 0)
{
continue; //We only want to show families where the procedure was completed after the unallocated prepayment.
}
decimal famRemAmt = listUnearnedProcsForGuar.Sum(x => x.UnallocatedAmt);
DataRow guarRow = retVal.NewRow();
string guarName;
double famBal;
dictPatNames.TryGetValue(guarNum, out guarName);
dictFamilyBalances.TryGetValue(guarNum, out famBal);
guarRow["Guar"] = guarName;
guarRow["FamBal"] = famBal.ToString("f");
guarRow["FamUnearned"] = unallocAmt.ToString("f");
guarRow["FamRemAmt"] = famRemAmt.ToString("f");
if (showProvider)
{
guarRow["Prov"] = guarRowCur["Abbr"];
}
retVal.Rows.Add(guarRow);
//If the next row has the same guarantor, then we know that it is another provider for this account and we should not populate the procedures yet
if (nextIndex < rowCount && guarNum == PIn.Long(tableUnallocatedUnearned.Rows[nextIndex]["Guarantor"].ToString()))
{
continue;
}
foreach (UnearnedProc unearnedProc in listUnearnedProcsForGuar)
{
DataRow newRow = retVal.NewRow();
dictPatNames.TryGetValue(guarNum, out guarName);
string patName;
if (dictPatNames.TryGetValue(unearnedProc.Proc.PatNum, out patName))
{
newRow["Patient"] = patName;
}
newRow["Code"] = ProcedureCodes.GetStringProcCode(unearnedProc.Proc.CodeNum, listProcCodes);
newRow["Date"] = unearnedProc.Proc.ProcDate.ToShortDateString();
newRow["Fee"] = unearnedProc.Proc.ProcFeeTotal.ToString("f");
newRow["RemAmt"] = unearnedProc.UnallocatedAmt.ToString("f");
retVal.Rows.Add(newRow);
}
}
return(retVal);
}
/// <summary>Retrieves the Line Item Unearned dataset from the database.</summary>
/// <param name="listClinics">The list of clinics to filter by. Pass in an empty list if this should not be filtered by clinic.</param>
/// <returns></returns>
public static DataTable GetLineItemUnearnedData(List <long> listClinics, DateTime date1Start, DateTime date2Start, bool showProvider)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinics, date1Start, date2Start, showProvider));
}
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
string command = "";
string whereClin = "";
//This query is kind-of a mess, but we're trying to account for bugs in previous versions.
//Logic is meant to match the account module "Unearned" value logic as closely as possible.
if (listClinics.Count > 0)
{
whereClin = "AND paysplit.ClinicNum IN (" + string.Join(",", listClinics) + ") ";
}
//Outer Select
command = "SELECT results.DatePay," + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + " Patient,"
+ "definition.ItemName,";
if (hasClinicsEnabled)
{
command += "clinic.Abbr Clinic,";
}
if (showProvider)
{
command += "provider.Abbr,";
}
command += "results.SplitAmt FROM (";
command += "SELECT SplitNum,DatePay,PatNum,UnearnedType,ClinicNum,SplitAmt,ProvNum FROM paysplit "
+ "WHERE paysplit.DatePay >= " + POut.Date(date1Start) + " "
+ "AND paysplit.DatePay <= " + POut.Date(date2Start) + " ";
if (listHiddenUnearnedDefNums.Count > 0)
{
command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
command += whereClin
+ "AND UnearnedType!=0 ";
//is to also show unearned that is now allocated.
command += ") results "
+ "INNER JOIN patient ON patient.PatNum=results.PatNum "
+ "LEFT JOIN definition ON definition.DefNum=results.UnearnedType ";
if (showProvider)
{
command += "LEFT JOIN provider on provider.ProvNum=results.ProvNum ";
}
if (hasClinicsEnabled)
{
command += "LEFT JOIN clinic ON clinic.ClinicNum=results.ClinicNum ";
}
command += "ORDER BY results.DatePay,Patient,results.SplitNum";
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
return(raw);
}
public static DataTable GetNetUnearnedData(List <long> listClinicNums, List <long> listProvNums,
List <long> listUnearnedTypeNums, bool isExcludeNetZero)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, listProvNums, listUnearnedTypeNums, isExcludeNetZero));
}
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
//show all families where unearned income was collected and no unallocated procedures exist.
/*All families with unallocated paysplits*/
DataTable retVal = new DataTable();
retVal.Columns.Add("PatientName");
retVal.Columns.Add("GuarantorName");
retVal.Columns.Add("PatUnearnedAmt");
retVal.Columns.Add("FamBal");
string command = @"
SELECT patient.Guarantor, paysplit.PatNum, patient.FName, patient.LName,
guar.FName GuarF, guar.LName GuarL,SUM(paysplit.SplitAmt) UnallocatedAmt
FROM paysplit
INNER JOIN patient ON patient.PatNum = paysplit.PatNum " ;
if (listClinicNums.Count > 0)
{
command += @"
AND patient.ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
if (listProvNums.Count > 0)
{
command += @"
AND patient.PriProv IN (" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") ";
}
command += @"
INNER JOIN patient guar ON guar.PatNum = patient.Guarantor
WHERE paysplit.UnearnedType != 0 " ;
if (listUnearnedTypeNums.Count > 0)
{
command += "AND paysplit.UnearnedType IN (" + string.Join(",", listUnearnedTypeNums.Select(x => POut.Long(x))) + ") ";
}
if (listHiddenUnearnedDefNums.Count > 0)
{
command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
command += "GROUP BY paysplit.PatNum ";
if (isExcludeNetZero)
{
command += "HAVING ABS(UnallocatedAmt) > 0.005 ";
}
DataTable tableUnallocatedPrepayments = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
//get remaining amount for all procedures of the returned families.
List <long> listGuarantorNums = tableUnallocatedPrepayments.Rows.OfType <DataRow>().Select(x => PIn.Long(x["Guarantor"].ToString())).ToList();
if (listGuarantorNums.Count == 0)
{
return(retVal); //No families have paysplits with unallocated prepayments. Return empty table.
}
/*As long as any patient in the family has AT LEAST ONE procedure that is not fully, explicitly paid off, they will not show in this report.*/
List <UnearnedProc> listGuarantorRemainingProcs = ReportsComplex.RunFuncOnReportServer(() =>
Procedures.GetRemainingProcsForFamilies(listGuarantorNums));
Dictionary <long, double> dictFamilyBalances = ReportsComplex.RunFuncOnReportServer(() => Ledgers.GetBalancesForFamilies(listGuarantorNums));
foreach (DataRow rowCur in tableUnallocatedPrepayments.Rows)
{
long unallocatedGuarantor = PIn.Long(rowCur["Guarantor"].ToString());
if (listGuarantorRemainingProcs.Select(x => x.GuarNum).Contains(unallocatedGuarantor))
{
continue; //Has at least one procedure that is not fully paid off.
}
double famBal;
if (!dictFamilyBalances.TryGetValue(unallocatedGuarantor, out famBal))
{
famBal = 0;
}
DataRow newRow = retVal.NewRow();
newRow["PatientName"] = rowCur["LName"].ToString() + ", " + rowCur["FName"].ToString();
newRow["GuarantorName"] = rowCur["GuarL"].ToString() + ", " + rowCur["GuarF"].ToString();
newRow["PatUnearnedAmt"] = PIn.Double(rowCur["UnallocatedAmt"].ToString());
newRow["FamBal"] = famBal.ToString("f");
retVal.Rows.Add(newRow);
}
return(retVal);
}
///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary>
public static DataTable GetActivePatientTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums, List <long> listBillingTypes, bool hasAllProvs, bool hasAllClinics, bool hasAllBilling)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, listBillingTypes, hasAllProvs, hasAllClinics, hasAllBilling));
}
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
List <Provider> listProvs = ReportsComplex.RunFuncOnReportServer(() => Providers.GetAll());
List <Def> listDefs = ReportsComplex.RunFuncOnReportServer(() => Defs.GetDefsNoCache(DefCat.BillingTypes));
List <Clinic> listClinics = ReportsComplex.RunFuncOnReportServer(() => Clinics.GetClinicsNoCache());
DataTable table = new DataTable();
table.Columns.Add("name");
table.Columns.Add("priProv");
table.Columns.Add("Address");
table.Columns.Add("Address2");
table.Columns.Add("City");
table.Columns.Add("State");
table.Columns.Add("Zip");
table.Columns.Add("carrier");
table.Columns.Add("HmPhone");
table.Columns.Add("WkPhone");
table.Columns.Add("WirelessPhone");
table.Columns.Add("billingType");
table.Columns.Add("secProv");
table.Columns.Add("clinic");
DataRow row;
string command = @"
SELECT patient.PatNum,patient.LName,patient.FName,patient.MiddleI,patient.Preferred,carrier.CarrierName,patient.BillingType,patient.PriProv,patient.SecProv,
patient.HmPhone,patient.WkPhone,patient.WirelessPhone,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip,patient.ClinicNum,provider.Abbr
FROM procedurelog
INNER JOIN patient ON patient.PatNum=procedurelog.PatNum AND PatStatus=" + POut.Int((int)PatientStatus.Patient) + @"
LEFT JOIN patplan ON patplan.PatNum=patient.PatNum AND patplan.Ordinal=1
LEFT JOIN inssub ON inssub.InsSubNum=patplan.InsSubNum
LEFT JOIN insplan ON insplan.PlanNum=inssub.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
LEFT JOIN provider ON provider.ProvNum=patient.PriProv
WHERE procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + @"
AND procedurelog.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd);
if (!hasAllProvs)
{
command += @" AND (patient.PriProv IN(" + String.Join(",", listProvNums) + ") OR patient.SecProv IN(" + String.Join(",", listProvNums) + ")) ";
}
if (listClinicNums.Count > 0)
{
command += "AND patient.ClinicNum IN(" + String.Join(",", listClinicNums) + ") ";
}
command += "AND patient.BillingType IN(" + String.Join(",", listBillingTypes) + ") ";
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += "GROUP BY patient.PatNum";
}
else //Oracle
{
command += @"GROUP BY patient.PatNum,patient.LName,patient.FName,patient.MiddleI,patient.Preferred,carrier.CarrierName
,patient.BillingType,patient.PriProv,patient.SecProv,patient.HmPhone,patient.WkPhone,patient.WirelessPhone
,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip,patient.ClinicNum" ;
}
if (!hasClinicsEnabled)
{
command += " ORDER BY provider.Abbr,patient.LName,patient.FName";
}
else //Using clinics
{
command += " ORDER BY patient.ClinicNum,provider.Abbr,patient.LName,patient.FName";
}
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
Patient pat;
for (int i = 0; i < raw.Rows.Count; i++)
{
Def billingType = listDefs.FirstOrDefault(x => x.DefNum == PIn.Long(raw.Rows[i]["BillingType"].ToString()));
row = table.NewRow();
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["name"] = pat.GetNameLF();
row["priProv"] = Providers.GetAbbr(PIn.Long(raw.Rows[i]["PriProv"].ToString()));
row["Address"] = raw.Rows[i]["Address"].ToString();
row["Address2"] = raw.Rows[i]["Address2"].ToString();
row["City"] = raw.Rows[i]["City"].ToString();
row["State"] = raw.Rows[i]["State"].ToString();
row["Zip"] = raw.Rows[i]["Zip"].ToString();
row["Carrier"] = raw.Rows[i]["CarrierName"].ToString();
row["HmPhone"] = raw.Rows[i]["HmPhone"].ToString();
row["WkPhone"] = raw.Rows[i]["WkPhone"].ToString();
row["WirelessPhone"] = raw.Rows[i]["WirelessPhone"].ToString();
row["billingType"] = (billingType == null) ? "" : billingType.ItemValue;
row["secProv"] = Providers.GetLName(PIn.Long(raw.Rows[i]["SecProv"].ToString()), listProvs);
if (hasClinicsEnabled) //Using clinics
{
string clinicDesc = Clinics.GetDesc(PIn.Long(raw.Rows[i]["ClinicNum"].ToString()), listClinics);
row["clinic"] = (clinicDesc == "")?Lans.g("FormRpPayPlans", "Unassigned"):clinicDesc;
}
table.Rows.Add(row);
}
return(table);
}
///<summary></summary>
public static DataTable GetPatUncollected(DateTime dateFrom, DateTime dateTo, List <long> listClinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listClinicNums));
}
#if DEBUG
Stopwatch s = new Stopwatch();
s.Start();
#endif
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
string query = $@"SELECT proc.ProcDate,CONCAT(patient.LName,', ',patient.FName) Patient,procedurecode.AbbrDesc,proc.Fee,
proc.Fee-proc.InsEst PatPortion,
COALESCE(adj.adjAmt,0) Adjustment,
COALESCE(pay.splitAmt,0) Payment,
proc.Fee-proc.InsEst+COALESCE(adj.adjAmt,0)-COALESCE(pay.splitAmt,0) Uncollected
FROM (
SELECT procedurelog.ProcNum,procedurelog.CodeNum,procedurelog.ProcDate,procedurelog.PatNum,
procedurelog.ProcFee*(procedurelog.BaseUnits+procedurelog.UnitQty)
-SUM(IF(claimproc.Status={SOut.Int((int)ClaimProcStatus.CapComplete)},claimproc.WriteOff,0)) Fee,
SUM(IF(claimproc.Status={SOut.Int((int)ClaimProcStatus.NotReceived)},claimproc.InsPayEst,
IF(claimproc.Status IN({SOut.Int((int)ClaimProcStatus.Received)},{SOut.Int((int)ClaimProcStatus.Supplemental)}),claimproc.InsPayAmt,0)))
+SUM(IF(claimproc.Status NOT IN({SOut.Int((int)ClaimProcStatus.CapComplete)},{SOut.Int((int)ClaimProcStatus.InsHist)}),claimproc.WriteOff,0)) InsEst
FROM procedurelog
LEFT JOIN claimproc ON claimproc.ProcNum=procedurelog.ProcNum
AND claimproc.Status IN ({
string.Join(",",new[] { ClaimProcStatus.NotReceived,ClaimProcStatus.Received,ClaimProcStatus.Supplemental,ClaimProcStatus.CapComplete }
.Select(x => SOut.Int((int)x)))})
WHERE procedurelog.ProcStatus={SOut.Int((int)ProcStat.C)}
AND procedurelog.ProcDate BETWEEN {SOut.Date(dateFrom)} AND {SOut.Date(dateTo)}" ;
if (hasClinicsEnabled && listClinicNums.Count > 0)
{
query += $@"
AND procedurelog.ClinicNum IN({string.Join(",",listClinicNums.Select(x => SOut.Long(x)))})" ;
}
query += $@"
GROUP BY procedurelog.ProcNum
ORDER BY NULL
) proc
INNER JOIN patient ON patient.PatNum=proc.PatNum
INNER JOIN procedurecode ON procedurecode.CodeNum=proc.CodeNum
LEFT JOIN (
SELECT adjustment.ProcNum,SUM(adjustment.AdjAmt) adjAmt
FROM adjustment
WHERE adjustment.ProcNum!=0
AND adjustment.ProcDate BETWEEN {SOut.Date(dateFrom)} AND {SOut.Date(dateTo)}
GROUP BY adjustment.ProcNum
ORDER BY NULL
) adj ON adj.ProcNum=proc.ProcNum
LEFT JOIN (
SELECT paysplit.ProcNum,SUM(paysplit.SplitAmt) splitAmt
FROM paysplit
WHERE paysplit.ProcNum!=0 " ;
if (listHiddenUnearnedDefNums.Count > 0)
{
query += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
query += @"GROUP BY paysplit.ProcNum
ORDER BY NULL
) pay ON pay.ProcNum=proc.ProcNum
WHERE proc.Fee-proc.InsEst+COALESCE(adj.adjAmt,0)-COALESCE(pay.splitAmt,0)>0.005
ORDER BY proc.ProcDate,patient.LName,patient.FName,procedurecode.ProcCode" ;
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(query));
#if DEBUG
s.Stop();
Console.WriteLine("Total time to generate report with " + string.Format("{0:#,##0.##}", table.Rows.Count) + " rows: "
+ (s.Elapsed.Hours > 0?(s.Elapsed.Hours + " hours "):"") + (s.Elapsed.Minutes > 0?(s.Elapsed.Minutes + " min "):"")
+ (s.Elapsed.TotalSeconds - (s.Elapsed.Hours * 60 * 60) - (s.Elapsed.Minutes * 60)) + " sec");
#endif
return(table);
}
///<summary>If the user has opted to age credits and debits for payment plans, all payments are included.
///Otherwise, only payments not attached to payment plans are included.
///This is determined by the user's choice for this particular report, NOT their practice-wide preference.</summary>
private static string GetPatPayAgingQuery(AgingOptions ageOptions)
{
string command = @"
SELECT 'PatPay' TranType,ps.PatNum,ps.DatePay TranDate,-ps.SplitAmt TranAmount
FROM paysplit ps
WHERE ps.SplitAmt != 0
AND ps.DatePay <= " + POut.Date(ageOptions.DateAsOf) + " ";
if (!ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.PayPlanCharges) ||
!ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.PayPlanCredits))
{
command += @"
AND ps.PayPlanNum = 0 " ;
}
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).Where(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
if (listHiddenUnearnedDefNums.Count > 0)
{
command += "AND ps.UnearnedType NOT IN (" + string.Join(",", listHiddenUnearnedDefNums) + ") ";
}
return(command);
}
public static DataTable GetOverPaidProcs(long patNum, List <long> listProvNums, List <long> listClinics, DateTime dateStart, DateTime dateEnd)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), patNum, listProvNums, listClinics, dateStart, dateEnd));
}
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
#region Completed Procs
string command = "SELECT ";
if (PrefC.GetBool(PrefName.ReportsShowPatNum))
{
command += DbHelper.Concat("CAST(patient.PatNum AS CHAR)", "'-'", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
else
{
command += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
command += @" AS 'patientName',
procedurelog.ProcDate,
procedurecode.ProcCode,
procedurelog.ToothNum,
provider.Abbr,
(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)) AS fee,
patient.PatNum,
procedurelog.ProcNum
FROM procedurelog
INNER JOIN patient ON patient.PatNum=procedurelog.PatNum
INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum
INNER JOIN provider ON provider.ProvNum=procedurelog.ProvNum
WHERE procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " AND "
+ DbHelper.BetweenDates("procedurelog.ProcDate", dateStart, dateEnd) + " "
+ "AND procedurelog.ProcFee>0 ";
if (listProvNums != null && listProvNums.Count > 0)
{
command += "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") ";
}
if (listClinics != null && listClinics.Count > 0)
{
command += "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinics.Select(x => POut.Long(x))) + ") ";
}
if (patNum > 0)
{
command += "AND procedurelog.PatNum=" + POut.Long(patNum) + " ";
}
command += "ORDER BY procedurelog.ProcDate,patientName,procedurecode.ProcCode,provider.Abbr";
DataTable rawCompletedProcTable = Db.GetTable(command);
Dictionary <long, DataRow> dictCompletedProcRows = rawCompletedProcTable.Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
DataTable table = new DataTable();
if (dictCompletedProcRows.Count == 0)
{
return(table);
}
#region ClaimProcs
List <long> listPatNums = rawCompletedProcTable.Select().Select(x => PIn.Long(x["PatNum"].ToString())).Distinct().ToList();
command = @"SELECT MIN(claimproc.ProcNum) ProcNum,MIN(claimproc.PatNum) PatNum,MIN(claimproc.ProcDate) ProcDate,SUM(claimproc.InsPayAmt) insPayAmt,
SUM(claimproc.Writeoff) writeoff
FROM claimproc
WHERE claimproc.Status NOT IN(" + string.Join(",", new List <int> {
(int)ClaimProcStatus.Preauth,
(int)ClaimProcStatus.CapEstimate, (int)ClaimProcStatus.CapComplete, (int)ClaimProcStatus.Estimate, (int)ClaimProcStatus.InsHist
}
.Select(x => POut.Int(x))) + ") "
+ "AND " + DbHelper.BetweenDates("claimproc.ProcDate", dateStart, dateEnd) + " "
+ "AND claimproc.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ") "
+ @"GROUP BY claimproc.ProcNum
HAVING SUM(claimproc.InsPayAmt+claimproc.Writeoff)>0
ORDER BY NULL" ;
Dictionary <long, DataRow> dictClaimProcRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
#region Patient Payments
command = @"SELECT paysplit.ProcNum,SUM(paysplit.SplitAmt) ptAmt
FROM paysplit
WHERE paysplit.ProcNum>0
AND paysplit.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + $@") ";
if (listHiddenUnearnedDefNums.Count > 0)
{
command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
command += @"
GROUP BY paysplit.ProcNum
ORDER BY NULL" ;
Dictionary <long, DataRow> dictPatPayRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
#region Adjustments
command = @"SELECT adjustment.ProcNum,SUM(adjustment.AdjAmt) AdjAmt
FROM adjustment
WHERE adjustment.ProcNum>0
AND adjustment.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + @")
GROUP BY adjustment.ProcNum
ORDER BY NULL" ;
Dictionary <long, DataRow> dictAdjRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("patientName");
table.Columns.Add("ProcDate", typeof(DateTime));
table.Columns.Add("ProcCode");
table.Columns.Add("ToothNum");
table.Columns.Add("Abbr");
table.Columns.Add("fee");
table.Columns.Add("insPaid");
table.Columns.Add("wo");
table.Columns.Add("ptPaid");
table.Columns.Add("adjAmt");
table.Columns.Add("overPay");
table.Columns.Add("PatNum");
DataRow row;
foreach (KeyValuePair <long, DataRow> kvp in dictCompletedProcRows)
{
long procNum = kvp.Key;
decimal procFeeAmt = PIn.Decimal(kvp.Value["fee"].ToString());
decimal insPaidAmt = 0;
decimal woAmt = 0;
decimal ptPaidAmt = 0;
decimal adjAmt = 0;
if (dictClaimProcRows.ContainsKey(procNum))
{
insPaidAmt = PIn.Decimal(dictClaimProcRows[procNum]["insPayAmt"].ToString());
woAmt = PIn.Decimal(dictClaimProcRows[procNum]["writeoff"].ToString());
}
if (dictPatPayRows.ContainsKey(procNum))
{
ptPaidAmt = PIn.Decimal(dictPatPayRows[procNum]["ptAmt"].ToString());
}
if (dictAdjRows.ContainsKey(procNum))
{
adjAmt = PIn.Decimal(dictAdjRows[procNum]["AdjAmt"].ToString());
}
decimal overPay = procFeeAmt - insPaidAmt - woAmt - ptPaidAmt + adjAmt;
if (!overPay.IsLessThanZero())
{
continue; //No overpayment. Not need to continue;
}
row = table.NewRow();
row["patientName"] = PIn.String(kvp.Value["patientName"].ToString());
row["ProcDate"] = PIn.Date(kvp.Value["ProcDate"].ToString());
row["ProcCode"] = PIn.String(kvp.Value["ProcCode"].ToString());
row["ToothNum"] = PIn.String(kvp.Value["ToothNum"].ToString());
row["Abbr"] = PIn.String(kvp.Value["Abbr"].ToString());;
row["fee"] = procFeeAmt.ToString();
row["insPaid"] = insPaidAmt.ToString();
row["wo"] = woAmt.ToString();
row["ptPaid"] = ptPaidAmt.ToString();
row["adjAmt"] = adjAmt.ToString();
row["overPay"] = overPay.ToString();
row["PatNum"] = PIn.Long(kvp.Value["PatNum"].ToString());
table.Rows.Add(row);
}
return(table);
}
///<summary></summary>
public static DataTable GetRecvBreakdownTable(DateTime dateStart, List <long> listProvNums, bool isWriteoffPay, bool isPayPlan2, string wDate
, string eDate, string bDate, string tableName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, listProvNums, isWriteoffPay, isPayPlan2, wDate, eDate, bDate, tableName));
}
//-------------------------------------------------------------------------------------//
// Create temperary tables for sorting data
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
string query = "";
string whereProv = ""; //used as the provider portion of the where clauses.
//each whereProv needs to be set up separately for each query
switch (tableName)
{
case "TableCharge":
whereProv = "";
if (listProvNums.Count != 0)
{
whereProv += " AND ProvNum IN(" + string.Join(",", listProvNums) + ") ";
}
//This query should only get called when PrefName.PayPlansVersion==AgeCreditsAndDebits
query = "SELECT TranDate, SUM(Amt) Amt "
+ "FROM ( "
+ "SELECT procedurelog.ProcDate TranDate, "
+ "SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)) Amt "
+ "FROM procedurelog "
+ "WHERE procedurelog.ProcDate >= '" + bDate + "' "
+ "AND procedurelog.ProcDate < '" + eDate + "' "
+ "AND procedurelog.ProcStatus = " + POut.Int((int)ProcStat.C) + " "
+ whereProv
+ "GROUP BY procedurelog.ProcDate ";
query += "UNION ALL "
+ "SELECT payplancharge.ChargeDate TranDate, "
+ "-SUM(payplancharge.Principal) Amt "
+ "FROM payplancharge "
+ "WHERE payplancharge.ChargeDate >= '" + bDate + "' "
+ "AND payplancharge.ChargeDate < '" + eDate + "' "
+ "AND payplancharge.ChargeType = " + POut.Int((int)PayPlanChargeType.Credit) + " "
+ whereProv
+ "GROUP BY payplancharge.ChargeDate ";
query += ")tran "
+ "GROUP BY TranDate "
+ "ORDER BY TranDate";
break;
case "TablePayPlanCharge":
if (isPayPlan2)
{
whereProv = "";
if (listProvNums.Count != 0)
{
whereProv += " AND ProvNum IN(" + string.Join(",", listProvNums) + ") ";
}
query = "SELECT payplancharge.ChargeDate, SUM(payplancharge.Principal + payplancharge.Interest) Amt "
+ "FROM payplancharge "
+ "WHERE payplancharge.ChargeType = " + POut.Int((int)PayPlanChargeType.Debit) + " "
+ "AND payplancharge.ChargeDate >= '" + bDate + "' "
+ "AND payplancharge.ChargeDate < '" + eDate + "' "
+ whereProv
+ "GROUP BY payplancharge.ChargeDate "
+ "ORDER BY payplancharge.ChargeDate ";
}
break;
case "TableCapWriteoff":
whereProv = "";
if (listProvNums.Count != 0)
{
whereProv += " AND claimproc.ProvNum IN(" + string.Join(",", listProvNums) + ") ";
}
if (isWriteoffPay)
{
query = "SELECT DateCP, "
+ "SUM(WriteOff) FROM claimproc WHERE "
+ "DateCP >= '" + bDate + "' "
+ "AND DateCP < '" + eDate + "' "
+ "AND Status = '7' " //CapComplete
+ whereProv
+ " GROUP BY DateCP "
+ "ORDER BY DateCP";
}
else
{
query = "SELECT ProcDate, "
+ "SUM(WriteOff) FROM claimproc WHERE "
+ "ProcDate >= '" + bDate + "' "
+ "AND ProcDate < '" + eDate + "' "
+ "AND Status = '7' " //CapComplete
+ whereProv
+ " GROUP BY ProcDate "
+ "ORDER BY ProcDate";
}
break;
case "TableInsWriteoff":
whereProv = "";
if (listProvNums.Count != 0)
{
whereProv += " AND ProvNum IN(" + string.Join(",", listProvNums) + ") ";
}
if (isWriteoffPay)
{
query = "SELECT DateCP, "
+ "SUM(WriteOff) FROM claimproc WHERE "
+ "DateCP >= '" + bDate + "' "
+ "AND DateCP < '" + eDate + "' "
+ "AND Status IN (1,4,5) " //Received, supplemental, capclaim. Otherwise, it's only an estimate. 7-CapCompl handled above.
+ whereProv
+ " GROUP BY DateCP "
+ "ORDER BY DateCP";
}
else
{
query = "SELECT ProcDate, "
+ "SUM(WriteOff) FROM claimproc WHERE "
+ "ProcDate >= '" + bDate + "' "
+ "AND ProcDate < '" + eDate + "' "
+ "AND Status IN (0,1,4,5) " //Notreceived, received, supplemental, capclaim. 7-CapCompl handled above.
+ whereProv
+ " GROUP BY ProcDate "
+ "ORDER BY ProcDate";
}
break;
case "TablePay":
whereProv = "";
if (listProvNums.Count != 0)
{
whereProv += " AND paysplit.ProvNum IN(" + string.Join(",", listProvNums) + ") ";
}
query = "SELECT paysplit.DatePay,SUM(paysplit.splitamt) FROM paysplit "
+ "WHERE paysplit.DatePay >= '" + bDate + "' "
+ "AND paysplit.DatePay < '" + eDate + "' ";
if (listHiddenUnearnedDefNums.Count > 0)
{
query += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
if (!isPayPlan2)
{
query += "AND paysplit.PayPlanNum=0 ";
}
query += whereProv
+ " GROUP BY paysplit.DatePay ORDER BY DatePay";
break;
case "TableIns":
whereProv = "";
if (listProvNums.Count != 0)
{
whereProv += " AND claimproc.ProvNum IN(" + string.Join(",", listProvNums) + ") ";
}
query = "SELECT DateCP,SUM(InsPayamt) "
+ "FROM claimproc WHERE "
+ "Status IN (1,4,5,7) " //Received, supplemental, capclaim, capcomplete.
+ "AND DateCP >= '" + bDate + "' "
+ "AND DateCP < '" + eDate + "' ";
if (!isPayPlan2)
{
query += "AND claimproc.PayPlanNum=0 ";
}
query += whereProv
+ " GROUP BY DateCP ORDER BY DateCP";
break;
case "TableAdj":
whereProv = "";
if (listProvNums.Count != 0)
{
whereProv += " AND ProvNum IN(" + string.Join(",", listProvNums) + ") ";
}
query = "SELECT adjdate, SUM(adjamt) FROM adjustment WHERE "
+ "adjdate >= '" + bDate + "' "
+ "AND adjdate < '" + eDate + "' "
+ whereProv
+ " GROUP BY adjdate ORDER BY adjdate";
break;
case "TableProduction":
whereProv = "";
if (listProvNums.Count != 0)
{
whereProv += " AND ProvNum IN(" + string.Join(",", listProvNums) + ") ";
}
query = "SELECT TranDate, SUM(Amt) Amt "
+ "FROM ( "
+ "SELECT procedurelog.ProcDate TranDate, "
+ "SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)) Amt "
+ "FROM procedurelog "
+ "WHERE procedurelog.ProcDate >= '" + bDate + "' "
+ "AND procedurelog.ProcDate < '" + eDate + "' "
+ "AND procedurelog.ProcStatus = " + POut.Int((int)ProcStat.C) + " "
+ whereProv
+ "GROUP BY procedurelog.ProcDate "
+ ")tran "
+ "GROUP BY TranDate "
+ "ORDER BY TranDate ";
break;
case "TablePayPlanCredit":
whereProv = "";
if (listProvNums.Count != 0)
{
whereProv += " AND ProvNum IN(" + string.Join(",", listProvNums) + ") ";
}
query = "SELECT payplancharge.ChargeDate TranDate, "
+ "SUM(payplancharge.Principal) Amt "
+ "FROM payplancharge "
+ "WHERE payplancharge.ChargeDate >= '" + bDate + "' "
+ "AND payplancharge.ChargeDate < '" + eDate + "' "
+ "AND payplancharge.ChargeType = " + POut.Int((int)PayPlanChargeType.Credit) + " "
+ whereProv
+ "GROUP BY payplancharge.ChargeDate "
+ "ORDER BY payplancharge.ChargeDate ";
break;
}
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(query)));
}