///<summary>Construct a payplanproductionentry item for a procedure. Calculates pat portion.</summary>
public PayPlanProductionEntry(Procedure proc, PayPlanLink credit, List <ClaimProc> listClaimProcs, List <Adjustment> listAdjustments)
{
ProductionTag = proc;
LinkedCredit = credit;
ProductionDate = proc.ProcDate;
PriKey = proc.ProcNum;
ProvNum = proc.ProvNum;
ClinicNum = proc.ClinicNum;
PatNum = proc.PatNum;
AmountOriginal = ClaimProcs.GetPatPortion(proc, listClaimProcs, listAdjustments);
AmountOverride = (decimal)credit.AmountOverride;
AmountRemaining = (AmountOverride == 0)?AmountOriginal:AmountOverride;
CreditDate = credit.SecDateTEntry;
Description = $"{ProcedureCodes.GetStringProcCode(proc.CodeNum)} - {ProcedureCodes.GetLaymanTerm(proc.CodeNum)}";
LinkType = PayPlanLinkType.Procedure;
}
///<summary>Should only be called if ODHQ.</summary>
public static List <Procedure> AddEServiceRepeatingChargesHelper(DateTime dateRun, List <RepeatCharge> listRepeatCharges)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Procedure> >(MethodBase.GetCurrentMethod(), dateRun, listRepeatCharges));
}
DateTime monthRun = new DateTime(dateRun.Year, dateRun.Month, 1);
//Get all bills that are due to be posted as of this date.
string command = "SELECT * FROM eservicebilling " +
"WHERE DateOfBill <= " + POut.Date(dateRun.Date, true) + " AND DateTimeProceduresPosted = '0001-01-01 00:00:00'";
List <EServiceBilling> listBillsDue = Crud.EServiceBillingCrud.SelectMany(command);
//This table will all ProcedureCodes which are included in the EServiceCodeLink table.
command = "SELECT * FROM procedurecode "
+ "INNER JOIN eservicecodelink ON procedurecode.CodeNum=eservicecodelink.CodeNum";
List <ProcedureCode> listProcCodes = OpenDentBusiness.Crud.ProcedureCodeCrud.TableToList(DataCore.GetTable(command));
//Get completed procedures that have already been posted.
List <Procedure> listProcsComplete = Procedures.GetCompletedForDateRange(monthRun, dateRun, listProcCodes.Select(x => x.CodeNum).ToList());
List <Procedure> retVal = new List <Procedure>();
foreach (EServiceBilling eServiceBilling in listBillsDue)
{
//List of procedures for this billing cycle was serialized to EServiceBilling.ProceduresJson by AccountMaint thread. Deserialize and post them.
List <Procedure> listProcs = JsonConvert.DeserializeObject <List <Procedure> >(eServiceBilling.ProceduresJson);
foreach (Procedure proc in listProcs)
{
//For a short time in May 2017 this procedure would have been checked against procedures already posted for the same month for the same eService.
//If a duplicate was found then this procedure would have been skipped. This blocking code was removed and now a similar procedure can be posted on the same month.
//This needs to be allowed to happen so that the first month can have 2 charges.
//1) For the pro-rated few days until their next BillingCycleDay is reached.
// Note, pro-rating is a future feature that may be allowed to happen via the signup portal. It is not currently but this particular code would no longer prevent it.
//2) For their typicaly BillingCycleDay charge.
Procedures.Insert(proc);
retVal.Add(proc);
listProcsComplete.Add(proc);
RepeatCharge repeatCharge = listRepeatCharges.Where(x => x.PatNum == proc.PatNum)
.FirstOrDefault(x => x.ProcCode == ProcedureCodes.GetStringProcCode(proc.CodeNum, listProcCodes));
if (repeatCharge != null)
{
RepeatCharges.AllocateUnearned(repeatCharge, proc, dateRun);
}
}
eServiceBilling.DateTimeProceduresPosted = DateTime.Now;
Crud.EServiceBillingCrud.Update(eServiceBilling);
}
return(retVal);
}
public static DataTable GetUnearnedAllocationData(List <long> listClinicNums, List <long> listProvNums,
List <long> listUnearnedTypeNums, bool isExcludeNetZeroUnearned)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, listProvNums, listUnearnedTypeNums, isExcludeNetZeroUnearned));
}
//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(COALESCE(paysplit.SplitAmt,0)) + SUM(COALESCE(alloc.AllocAmt,0)) UnallocAmt
FROM paysplit
LEFT JOIN (
SELECT paysplit.FSplitNum,SUM(paysplit.SplitAmt) AllocAmt
FROM paysplit
WHERE paysplit.FSplitNum != 0
GROUP BY paysplit.FSplitNum
)alloc ON paysplit.SplitNum = alloc.FSplitNum
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))) + ") ";
}
}
command += @"
WHERE paysplit.UnearnedType != 0 " ;
if (listUnearnedTypeNums.Count > 0)
{
command += @"
AND paysplit.UnearnedType IN (" + string.Join(",", listUnearnedTypeNums.Select(x => POut.Long(x))) + ") ";
}
command += @"
AND paysplit.FSplitNum = 0
AND (ABS(paysplit.SplitAmt + alloc.AllocAmt) > 0.005 OR alloc.AllocAmt IS NULL)
GROUP BY patient.Guarantor " ;
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");
retVal.Columns.Add("Patient");
retVal.Columns.Add("Code");
retVal.Columns.Add("Date");
retVal.Columns.Add("Fee");
retVal.Columns.Add("RemAmt");
foreach (DataRow guarRowCur in tableUnallocatedUnearned.Rows)
{
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");
retVal.Rows.Add(guarRow);
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.ProcFee * (unearnedProc.Proc.UnitQty + unearnedProc.Proc.BaseUnits)).ToString("f");
newRow["RemAmt"] = unearnedProc.UnallocatedAmt.ToString("f");
retVal.Rows.Add(newRow);
}
}
return(retVal);
}
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>IComparable.CompareTo implementation. This is used to order benefit lists as well as to group benefits if the type is essentially equal. It doesn't compare values such as percentages or amounts. It only compares types.</summary>
public int CompareTo(object obj)
{
if (!(obj is Benefit))
{
throw new ArgumentException("object is not a Benefit");
}
Benefit ben = (Benefit)obj;
//first by fam
if (CoverageLevel != ben.CoverageLevel)
{
return(CoverageLevel.CompareTo(ben.CoverageLevel));
}
//then by type
if (BenefitType != ben.BenefitType) //if types are different
{
return(BenefitType.CompareTo(ben.BenefitType));
}
//types are the same, so check covCat. This is a loose comparison, ignored if either is 0.
if (CovCatNum != 0 && ben.CovCatNum != 0 && //if both covcats have values
CovCatNum != ben.CovCatNum) //and they are different
//return CovCats.GetOrderShort(CovCatNum).CompareTo(CovCats.GetOrderShort(ben.CovCatNum));
//this line was changed because we really do need to know if they have different covcats.
{
return(CovCats.GetFindIndex(x => x.CovCatNum == CovCatNum).CompareTo(CovCats.GetFindIndex(x => x.CovCatNum == ben.CovCatNum)));
}
//ProcCode
if (CodeNum != ben.CodeNum)
{
if (CovCatNum != 0 && CovCatNum == ben.CovCatNum) //Both benefits are for same custom frequency group or the exam group.
{
return(ProcedureCodes.GetStringProcCode(CodeNum).CompareTo(ProcedureCodes.GetStringProcCode(ben.CodeNum)));
}
int frequencyGroup1 = GetFrequencyGroupNum();
int frequencyGroup2 = ben.GetFrequencyGroupNum();
if (frequencyGroup1 == frequencyGroup2)
{
return(ProcedureCodes.GetStringProcCode(CodeNum)
.CompareTo(ProcedureCodes.GetStringProcCode(ben.CodeNum)));
}
return(frequencyGroup1.CompareTo(frequencyGroup2));
}
//TimePeriod-ServiceYear and CalendarYear are treated as the same.
//if either are not serviceYear or CalendarYear
List <BenefitTimePeriod> listTimePeriods = new List <BenefitTimePeriod>()
{
BenefitTimePeriod.CalendarYear, BenefitTimePeriod.ServiceYear
};
if ((!TimePeriod.In(listTimePeriods) && !ben.TimePeriod.In(listTimePeriods) && TimePeriod != ben.TimePeriod) ||
(TimePeriod.In(listTimePeriods) != ben.TimePeriod.In(listTimePeriods)))
{
return(TimePeriod.CompareTo(ben.TimePeriod));
}
//QuantityQualifier
if (QuantityQualifier != ben.QuantityQualifier) //if different
{
return(QuantityQualifier.CompareTo(ben.QuantityQualifier));
}
//always different if plan vs. pat override
if (PatPlanNum == 0 && ben.PatPlanNum != 0)
{
return(-1);
}
if (PlanNum == 0 && ben.PlanNum != 0)
{
return(1);
}
//Last resort. Can't find any significant differences in the type, so:
return(0); //then values are the same.
}
