private static string GetPayPlanAgingQuery(AgingOptions ageOptions)
{
string chargeTypeInclude = "";
if (ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.PayPlanCharges))
{
chargeTypeInclude += (int)PayPlanChargeType.Debit;
}
if (ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.PayPlanCredits))
{
if (chargeTypeInclude != "")
{
chargeTypeInclude += ",";
}
chargeTypeInclude += (int)PayPlanChargeType.Credit;
}
chargeTypeInclude = "(" + chargeTypeInclude + ") ";
string command;
command = @"
SELECT 'PPComplete' TranType,ppc.Guarantor PatNum,ppc.ChargeDate TranDate,
(CASE WHEN ppc.ChargeType != " + POut.Int((int)PayPlanChargeType.Debit) + @" THEN -ppc.Principal
WHEN pp.PlanNum=0 THEN ppc.Principal+ppc.Interest ELSE 0 END) TranAmount
FROM payplancharge ppc
LEFT JOIN payplan pp ON pp.PayPlanNum=ppc.PayPlanNum
WHERE ppc.ChargeDate <= " + POut.Date(ageOptions.DateAsOf) + @"
AND ppc.ChargeType IN " + chargeTypeInclude + " ";
return(command);
}
private static string GetAdjAgingQuery(AgingOptions ageOptions)
{
return(@"
SELECT 'Adj' TranType,adj.PatNum,adj.AdjDate TranDate,adj.AdjAmt TranAmount
FROM adjustment adj
WHERE adj.AdjAmt != 0
AND adj.AdjDate <= " + POut.Date(ageOptions.DateAsOf) + " ");
}
private static string GetProcAgingQuery(AgingOptions ageOptions)
{
return(@"
SELECT 'Proc' TranType,pl.PatNum,pl.ProcDate TranDate,pl.ProcFee*(pl.UnitQty+pl.BaseUnits) TranAmount
FROM procedurelog pl
WHERE pl.ProcStatus=" + (int)ProcStat.C + @"
AND pl.ProcFee != 0
AND pl.ProcDate <= " + POut.Date(ageOptions.DateAsOf) + " ");
}
private static string GetInsEstAgingQuery(AgingOptions ageOptions)
{
string command = @"
SELECT 'InsEst' TranType,cp.PatNum,cp.DateCP TranDate,-cp.InsPayEst TranAmount
FROM claimproc cp
WHERE cp.Status = " + POut.Int((int)ClaimProcStatus.NotReceived) + @"
AND cp.InsPayEst != 0
AND cp.DateCP <= " + POut.Date(ageOptions.DateAsOf);
return(command);
}
private static string GetInsPayAgingQuery(AgingOptions ageOptions)
{
string command = @"
SELECT 'InsPay' TranType,cp.PatNum,cp.DateCP TranDate,-cp.InsPayAmt TranAmount
FROM claimproc cp
WHERE cp.Status IN (" + POut.Int((int)ClaimProcStatus.Received) + "," + POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapClaim) + "," + POut.Int((int)ClaimProcStatus.CapComplete) + @")
AND cp.InsPayAmt != 0
AND cp.PayPlanNum = 0
AND cp.DateCP <= " + POut.Date(ageOptions.DateAsOf);
return(command);
}
private static string GetWriteoffEstAgingQuery(AgingOptions ageOptions)
{
//This will add up claims that have been received on a date later than the run date. This is to attempt to maintain historical information.
//This logic matches a lot of our custom queries and can be removed if it becomes an issue, as it doesn't exist in our internal reports currently.
string command = @"
SELECT 'WriteoffEst' TranType,cp.PatNum,cp.ProcDate TranDate,
-cp.WriteOff TranAmount
FROM claimproc cp
WHERE cp.PatNum != 0
AND ((cp.Status=" + (int)ClaimProcStatus.NotReceived + @")
OR (cp.Status=" + (int)ClaimProcStatus.Received + @" AND cp.DateCP>" + POut.Date(ageOptions.DateAsOf) + @"))
AND cp.ProcDate <= " + POut.Date(ageOptions.DateAsOf);
return(command);
}
///<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 " ;
}
return(command);
}
private static string GetWriteoffAgingQuery(AgingOptions ageOptions)
{
List <ClaimProcStatus> listClaimProcStatus = new List <ClaimProcStatus>()
{
ClaimProcStatus.Received, ClaimProcStatus.Supplemental, ClaimProcStatus.CapClaim, ClaimProcStatus.CapComplete
};
string statusIn = string.Join(",", listClaimProcStatus.Select(x => POut.Int((int)x)));
string command;
if (ageOptions.WriteoffOptions == PPOWriteoffDateCalc.InsPayDate)
{
command = "SELECT 'Writeoff' TranType,"
+ "cp.PatNum,"
+ "cp.DateCP TranDate,"
+ "-cp.Writeoff TranAmount "
+ "FROM claimproc cp "
+ "WHERE cp.Status IN (" + statusIn + ") "
+ "AND cp.WriteOff != 0 "
+ "AND cp.DateCP <= " + POut.Date(ageOptions.DateAsOf);
}
else if (ageOptions.WriteoffOptions == PPOWriteoffDateCalc.ProcDate)
{
command = "SELECT 'Writeoff' TranType,"
+ "cp.PatNum,"
+ "cp.ProcDate TranDate,"
+ "-cp.Writeoff TranAmount "
+ "FROM claimproc cp "
+ "WHERE cp.Status IN (" + statusIn + ") "
+ "AND cp.WriteOff != 0 "
+ "AND cp.ProcDate <= " + POut.Date(ageOptions.DateAsOf);
}
else //AgingOptions.WriteoffAgingOptions.ClaimPayDate
{
command = "SELECT 'Writeoff' TranType,"
+ "cp.PatNum,"
+ "claimsnapshot.DateTEntry TranDate,"
+ "-claimsnapshot.Writeoff TranAmount "
+ "FROM claimproc cp "
+ "INNER JOIN claimsnapshot ON cp.ClaimProcNum=claimsnapshot.ClaimProcNum "
+ "WHERE cp.Status IN (" + statusIn + ") "
+ "AND claimsnapshot.WriteOff != 0 "
+ "AND claimsnapshot.DateTEntry <= " + POut.Date(ageOptions.DateAsOf);
}
return(command);
}
private static string GetWriteoffAgingQuery(AgingOptions ageOptions)
{
string dateStr;
if (ageOptions.WriteoffOptions == AgingOptions.WriteoffAgingOptions.ProcDate)
{
dateStr = " cp.ProcDate ";
}
else
{
dateStr = " cp.DateCP ";
}
string command = @"
SELECT 'Writeoff' TranType,cp.PatNum," + dateStr + @" TranDate,-cp.WriteOff TranAmount
FROM claimproc cp
WHERE cp.Status IN (" + POut.Int((int)ClaimProcStatus.Received) + "," + POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapClaim) + "," + POut.Int((int)ClaimProcStatus.CapComplete) + @")
AND cp.WriteOff != 0
AND " + dateStr + " <= " + POut.Date(ageOptions.DateAsOf);
return(command);
}
///<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 List <AgingPat> GetAgingList(AgingOptions ageOptions)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <AgingPat> >(MethodBase.GetCurrentMethod(), ageOptions));
}
string command = @"SELECT pat.FName, pat.LName, pat.PatNum, ";
if (ageOptions.AgeCredits)
{
command += @"
ROUND(tSums.ChargesOver90-tSums.CreditsOver90,3) BalOver90,
ROUND(tSums.Charges_61_90-tSums.Credits_61_90,3) Bal_61_90,
ROUND(tSums.Charges_31_60-tSums.Credits_31_60,3) Bal_31_60,
ROUND(tSums.Charges_0_30-tSums.Credits_0_30,3) Bal_0_30,
ROUND(tSums.TotalCharges-tSums.TotalCredits,3) BalTotal
FROM (" ;
}
else
{
command += @"ROUND(CASE WHEN tSums.TotalCredits >= tSums.ChargesOver90 THEN 0
ELSE tSums.ChargesOver90-tSums.TotalCredits END,3) BalOver90,
ROUND(CASE WHEN tSums.TotalCredits <= tSums.ChargesOver90 THEN tSums.Charges_61_90
WHEN tSums.ChargesOver90 + tSums.Charges_61_90 <= tSums.TotalCredits THEN 0
ELSE tSums.ChargesOver90 + tSums.Charges_61_90-tSums.TotalCredits END,3) Bal_61_90,
ROUND(CASE WHEN tSums.TotalCredits < tSums.ChargesOver90 + tSums.Charges_61_90 THEN tSums.Charges_31_60
WHEN tSums.ChargesOver90 + tSums.Charges_61_90 + tSums.Charges_31_60 <= tSums.TotalCredits THEN 0
ELSE tSums.ChargesOver90 + tSums.Charges_61_90 + tSums.Charges_31_60-tSums.TotalCredits END,3) Bal_31_60,
ROUND(CASE WHEN tSums.TotalCredits < tSums.ChargesOver90 + tSums.Charges_61_90 + tSums.Charges_31_60 THEN tSums.Charges_0_30
WHEN tSums.ChargesOver90 + tSums.Charges_61_90 + tSums.Charges_31_60 + tSums.Charges_0_30 <= tSums.TotalCredits THEN 0
ELSE tSums.ChargesOver90 + tSums.Charges_61_90 + tSums.Charges_31_60 + tSums.Charges_0_30-tSums.TotalCredits END,3) Bal_0_30,
ROUND(tSums.TotalCharges-tSums.TotalCredits,3) BalTotal
FROM (" ;
}
command += @"
SELECT patient.PatNum, patient.Guarantor,
SUM(CASE WHEN trans.TranAmount > 0 AND trans.TranDate >= " + POut.Date(ageOptions.DateAsOf) + @"-INTERVAL 30 DAY THEN trans.TranAmount ELSE 0 END) Charges_0_30,
SUM(CASE WHEN trans.TranAmount > 0 AND trans.TranDate BETWEEN " + POut.Date(ageOptions.DateAsOf) + @"-INTERVAL 60 DAY AND " + POut.Date(ageOptions.DateAsOf) + @"-INTERVAL 31 DAY THEN trans.TranAmount ELSE 0 END) Charges_31_60,
SUM(CASE WHEN trans.TranAmount > 0 AND trans.TranDate BETWEEN " + POut.Date(ageOptions.DateAsOf) + @"-INTERVAL 90 DAY AND " + POut.Date(ageOptions.DateAsOf) + @"-INTERVAL 61 DAY THEN trans.TranAmount ELSE 0 END) Charges_61_90,
SUM(CASE WHEN trans.TranAmount > 0 AND trans.TranDate < " + POut.Date(ageOptions.DateAsOf) + @"-INTERVAL 90 DAY THEN trans.TranAmount ELSE 0 END) ChargesOver90,
SUM(CASE WHEN trans.TranAmount > 0 THEN trans.TranAmount ELSE 0 END) AS TotalCharges,
-SUM(CASE WHEN trans.TranAmount < 0 AND trans.TranDate >= " + POut.Date(ageOptions.DateAsOf) + @"-INTERVAL 30 DAY THEN trans.TranAmount ELSE 0 END) Credits_0_30,
-SUM(CASE WHEN trans.TranAmount < 0 AND trans.TranDate BETWEEN " + POut.Date(ageOptions.DateAsOf) + @"-INTERVAL 60 DAY AND " + POut.Date(ageOptions.DateAsOf) + @"-INTERVAL 31 DAY THEN trans.TranAmount ELSE 0 END) Credits_31_60,
-SUM(CASE WHEN trans.TranAmount < 0 AND trans.TranDate BETWEEN " + POut.Date(ageOptions.DateAsOf) + @"-INTERVAL 90 DAY AND " + POut.Date(ageOptions.DateAsOf) + @"-INTERVAL 61 DAY THEN trans.TranAmount ELSE 0 END) Credits_61_90,
-SUM(CASE WHEN trans.TranAmount < 0 AND trans.TranDate < " + POut.Date(ageOptions.DateAsOf) + @"-INTERVAL 90 DAY THEN trans.TranAmount ELSE 0 END) CreditsOver90,
-SUM(CASE WHEN trans.TranAmount < 0 THEN trans.TranAmount ELSE 0 END) AS TotalCredits
FROM patient
INNER JOIN (" ;
string transQueries = "";
if (ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.ProcedureFees))
{
transQueries += String.IsNullOrWhiteSpace(transQueries) ? "" : " UNION ALL ";
transQueries += GetProcAgingQuery(ageOptions);
}
if (ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.Adjustments))
{
transQueries += String.IsNullOrWhiteSpace(transQueries) ? "" : " UNION ALL ";
transQueries += GetAdjAgingQuery(ageOptions);
}
if (ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.PayPlanCharges) || ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.PayPlanCredits))
{
transQueries += String.IsNullOrWhiteSpace(transQueries) ? "" : " UNION ALL ";
transQueries += GetPayPlanAgingQuery(ageOptions);
}
if (ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.PatPayments))
{
transQueries += String.IsNullOrWhiteSpace(transQueries) ? "" : " UNION ALL ";
transQueries += GetPatPayAgingQuery(ageOptions);
}
if (ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.InsPayments))
{
transQueries += String.IsNullOrWhiteSpace(transQueries) ? "" : " UNION ALL ";
transQueries += GetInsPayAgingQuery(ageOptions);
}
if (ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.InsEsts))
{
transQueries += String.IsNullOrWhiteSpace(transQueries) ? "" : " UNION ALL ";
transQueries += GetInsEstAgingQuery(ageOptions);
}
if (ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.Writeoffs))
{
transQueries += String.IsNullOrWhiteSpace(transQueries) ? "" : " UNION ALL ";
transQueries += GetWriteoffAgingQuery(ageOptions);
}
if (ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.WriteoffEsts))
{
transQueries += String.IsNullOrWhiteSpace(transQueries) ? "" : " UNION ALL ";
transQueries += GetWriteoffEstAgingQuery(ageOptions);
}
command += transQueries + @"
)trans ON patient.PatNum = trans.PatNum
WHERE TRUE " ;
if (ageOptions.ListBillTypes != null && ageOptions.ListBillTypes.Count > 0)
{
command += " AND patient.BillingType IN (" + String.Join(",", ageOptions.ListBillTypes.Select(x => x.DefNum)) + ") ";
}
if (ageOptions.ListProvs != null && ageOptions.ListProvs.Count > 0)
{
command += @" AND patient.PriProv IN (" + String.Join(",", ageOptions.ListProvs.Select(x => x.ProvNum)) + ") ";
}
if (ageOptions.ListClins != null && ageOptions.ListClins.Count > 0)
{
command += @" AND patient.ClinicNum IN (" + String.Join(",", ageOptions.ListClins.Select(x => x.ClinicNum)) + ") ";
}
if (ageOptions.FamGroup == AgingOptions.FamilyGrouping.Individual)
{
command += @" GROUP BY patient.PatNum
)tSums
INNER JOIN patient pat on pat.PatNum = tSums.PatNum " ;
}
else
{
command += @" GROUP BY patient.Guarantor
)tSums
INNER JOIN patient pat on pat.PatNum = tSums.Guarantor " ;
}
if (ageOptions.ExcludeInactive) //made to match the way regular aging looks a patient status.
{
command += " AND pat.PatStatus != " + (int)PatientStatus.Inactive + " ";
}
if (ageOptions.ExcludeArchive)
{
command += " AND pat.PatStatus != " + (int)PatientStatus.Archived + " ";
}
if (ageOptions.ExcludeBadAddress)
{
command += " AND pat.Zip != '' ";
}
command += "HAVING TRUE ";
if (ageOptions.AgeAccount == AgeOfAccount.Over30)
{
command += @" AND (Bal_31_60 != 0 OR Bal_61_90 != 0 OR BalOver90 != 0) ";
}
else if (ageOptions.AgeAccount == AgeOfAccount.Over60)
{
command += @" AND (Bal_61_90 != 0 OR BalOver90 != 0) ";
}
else if (ageOptions.AgeAccount == AgeOfAccount.Over90)
{
command += @" AND (BalOver90 != 0) ";
}
if (ageOptions.NegativeBalOptions == AgingOptions.NegativeBalAgingOptions.Exclude)
{
command += " AND BalTotal > 0 ";
}
else if (ageOptions.NegativeBalOptions == AgingOptions.NegativeBalAgingOptions.Include)
{
command += " AND BalTotal != 0 ";
}
else
{
command += " AND BalTotal < 0 ";
}
command += " ORDER BY pat.LName, pat.FName ";
DataTable table = Db.GetTable(command);
List <AgingPat> retVal = new List <AgingPat>();
foreach (DataRow row in table.Rows)
{
Patient patLim = new Patient()
{
PatNum = PIn.Long(row["PatNum"].ToString()),
FName = PIn.String(row["FName"].ToString()),
LName = PIn.String(row["LName"].ToString()),
};
AgingPat agingPatCur = new AgingPat()
{
Pat = patLim,
BalZeroThirty = PIn.Double(row["Bal_0_30"].ToString()),
BalThirtySixty = PIn.Double(row["Bal_31_60"].ToString()),
BalSixtyNinety = PIn.Double(row["Bal_61_90"].ToString()),
BalOverNinety = PIn.Double(row["BalOver90"].ToString()),
BalTotal = PIn.Double(row["BalTotal"].ToString()),
};
retVal.Add(agingPatCur);
}
return(retVal);
}
