///<summary>Only one dimension to the list for now.</summary>
public static List <List <int> > GetAR(DateTime dateFrom, DateTime dateTo, List <DashboardAR> listDashAR)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <List <int> > >(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listDashAR));
}
//assumes that dateFrom is the first of the month and that there are 12 periods
//listDashAR may be empty, in which case, this routine will take about 18 seconds, but the user was warned.
//listDashAR may also me incomplete, especially the most recent month(s).
string command;
List <int> listInt;
listInt = new List <int>();
bool agingWasRun = false;
for (int i = 0; i < 12; i++)
{
DateTime dateLastOfMonth = dateFrom.AddMonths(i + 1).AddDays(-1);
DashboardAR dash = null;
for (int d = 0; d < listDashAR.Count; d++)
{
if (listDashAR[d].DateCalc != dateLastOfMonth)
{
continue;
}
dash = listDashAR[d];
}
if (dash != null) //we found a DashboardAR object from the database for this month, so use it.
{
listInt.Add((int)dash.BalTotal);
continue;
}
agingWasRun = true;
//run historical aging on all patients based on the date entered.
Ledgers.ComputeAging(0, dateLastOfMonth, true);
command = @"SELECT SUM(Bal_0_30+Bal_31_60+Bal_61_90+BalOver90),SUM(InsEst) FROM patient";
DataTable table = Db.GetTable(command);
dash = new DashboardAR();
dash.DateCalc = dateLastOfMonth;
dash.BalTotal = PIn.Double(table.Rows[0][0].ToString());
dash.InsEst = PIn.Double(table.Rows[0][1].ToString());
DashboardARs.Insert(dash); //save it to the db for later.
listInt.Add((int)dash.BalTotal); //and also use it now.
}
if (agingWasRun)
{
Ledgers.RunAging(); //set aging back to normal
}
List <List <int> > retVal = new List <List <int> >();
retVal.Add(listInt);
return(retVal);
}
///<summary>Returns all DashbaordAR(s) for the given time period. Caution, this will run aging and calculate a/r if a month within the given range is missing.
///This can take several seconds per month missing.</summary>
public static List <DashboardAR> GetAR(DateTime dateFrom, DateTime dateTo, List <DashboardAR> listDashAR)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <DashboardAR> >(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listDashAR));
}
//assumes that dateFrom is the first of the month.
string command;
List <DashboardAR> listRet = new List <DashboardAR>();
#if DEBUG
_elapsedTimeAR = "";
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
System.Diagnostics.Stopwatch stopWatchTotal = new System.Diagnostics.Stopwatch();
_elapsedTimeAR = "Elapsed time for GetAR:\r\n";
stopWatchTotal.Start();
#endif
int months = 0;
while (dateTo >= dateFrom.AddMonths(months)) //calculate the number of months between the two dates.
{
months++;
}
for (int i = 0; i < months; i++)
{
DateTime dateLastOfMonth = dateFrom.AddMonths(i + 1).AddDays(-1);
DashboardAR dash = null;
for (int d = 0; d < listDashAR.Count; d++)
{
if (listDashAR[d].DateCalc != dateLastOfMonth)
{
continue;
}
dash = listDashAR[d];
}
if (dash != null) //we found a DashboardAR object from the database for this month, so use it.
{
listRet.Add(dash);
continue;
}
#if DEBUG
stopWatch.Restart();
#endif
//run historical aging on all patients based on the date entered.
command = "SELECT SUM(Bal_0_30+Bal_31_60+Bal_61_90+BalOver90),SUM(InsEst) "
+ "FROM (" + Ledgers.GetAgingQueryString(dateLastOfMonth, isHistoric: true) + ") guarBals";
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
#if DEBUG
stopWatch.Stop();
_elapsedTimeAR += "Aging using Ledgers.GetHistoricAgingQueryString() #" + i + " : " + stopWatch.Elapsed.ToString() + "\r\n";
#endif
dash = new DashboardAR();
dash.DateCalc = dateLastOfMonth;
dash.BalTotal = PIn.Double(table.Rows[0][0].ToString());
dash.InsEst = PIn.Double(table.Rows[0][1].ToString());
DashboardARs.Insert(dash); //save it to the db for later.
if (!string.IsNullOrEmpty(PrefC.ReportingServer.Server)) //only attempt to insert into the reporting server if the reporting server is set up.
{
ReportsComplex.RunFuncOnReportServer(() => (DashboardARs.Insert(dash))); //save it to the db for later.
}
listRet.Add(dash); //and also use it now.
}
#if DEBUG
stopWatchTotal.Stop();
_elapsedTimeAR += "Total: " + stopWatchTotal.Elapsed.ToString();
if (_showElapsedTimesForDebug)
{
System.Windows.Forms.MessageBox.Show(_elapsedTimeAR);
}
#endif
return(listRet);
}
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 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));
}
//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(IFNULL(paysplit.SplitAmt,0)) + SUM(IFNULL(alloc.AllocAmt,0)) UnallocatedAmt
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)
{
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))) + ") ";
}
command += @"
AND paysplit.FSplitNum = 0
AND (paysplit.SplitAmt + alloc.AllocAmt != 0 OR alloc.AllocAmt IS NULL)
GROUP BY paysplit.PatNum,patient.Guarantor,patient.FName,patient.LName,guar.FName,guar.LName " ;
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);
}
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>Returns a SerializableDictionary with key=PatNum, value=PatAgingData with the filters applied.</summary>
public static SerializableDictionary <long, PatAgingData> GetAgingData(bool isSinglePatient, bool includeChanged, bool excludeInsPending,
bool excludeIfUnsentProcs, bool isSuperBills, List <long> listClinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <SerializableDictionary <long, PatAgingData> >(MethodBase.GetCurrentMethod(), isSinglePatient, includeChanged,
excludeInsPending, excludeIfUnsentProcs, isSuperBills, listClinicNums));
}
SerializableDictionary <long, PatAgingData> dictPatAgingData = new SerializableDictionary <long, PatAgingData>();
string command = "";
string guarOrPat = "guar";
if (isSinglePatient)
{
guarOrPat = "patient";
}
string whereAndClinNum = "";
if (!listClinicNums.IsNullOrEmpty())
{
whereAndClinNum = $@"AND {guarOrPat}.ClinicNum IN ({string.Join(",",listClinicNums)})";
}
if (includeChanged || excludeIfUnsentProcs)
{
command = $@"SELECT {guarOrPat}.PatNum,{guarOrPat}.ClinicNum,MAX(procedurelog.ProcDate) MaxProcDate";
if (excludeIfUnsentProcs)
{
command += ",MAX(CASE WHEN insplan.IsMedical=1 THEN 0 ELSE COALESCE(claimproc.ProcNum,0) END)>0 HasUnsentProcs";
}
command += $@" FROM patient
INNER JOIN patient guar ON guar.PatNum=patient.Guarantor
INNER JOIN procedurelog ON procedurelog.PatNum = patient.PatNum " ;
if (excludeIfUnsentProcs)
{
command += $@"LEFT JOIN claimproc ON claimproc.ProcNum = procedurelog.ProcNum
AND claimproc.NoBillIns=0
AND claimproc.Status = {POut.Int((int)ClaimProcStatus.Estimate)}
AND procedurelog.ProcDate > CURDATE()-INTERVAL 6 MONTH
LEFT JOIN insplan ON insplan.PlanNum=claimproc.PlanNum " ;
}
command += $@"WHERE procedurelog.ProcFee > 0
AND procedurelog.ProcStatus = {POut.Int((int)ProcStat.C)}
{whereAndClinNum}
GROUP BY {guarOrPat}.PatNum
ORDER BY NULL" ;
using (DataTable tableChangedAndUnsent = Db.GetTable(command)) {
foreach (DataRow row in tableChangedAndUnsent.Rows)
{
long patNum = PIn.Long(row["PatNum"].ToString());
if (!dictPatAgingData.ContainsKey(patNum))
{
dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString()));
}
if (includeChanged)
{
dictPatAgingData[patNum].ListPatAgingTransactions
.Add(new PatAgingTransaction(PatAgingTransaction.TransactionTypes.Procedure, PIn.Date(row["MaxProcDate"].ToString())));
}
if (excludeIfUnsentProcs)
{
dictPatAgingData[patNum].HasUnsentProcs = PIn.Bool(row["HasUnsentProcs"].ToString());
}
}
}
}
if (includeChanged)
{
command = $@"SELECT {guarOrPat}.PatNum,{guarOrPat}.ClinicNum,MAX(claimproc.DateCP) maxDateCP
FROM claimproc
INNER JOIN patient ON patient.PatNum = claimproc.PatNum
INNER JOIN patient guar ON guar.PatNum=patient.Guarantor
WHERE claimproc.InsPayAmt > 0
{whereAndClinNum}
GROUP BY {guarOrPat}.PatNum" ;
using (DataTable tableMaxPayDate = Db.GetTable(command)) {
foreach (DataRow row in tableMaxPayDate.Rows)
{
long patNum = PIn.Long(row["PatNum"].ToString());
if (!dictPatAgingData.ContainsKey(patNum))
{
dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString()));
}
dictPatAgingData[patNum].ListPatAgingTransactions
.Add(new PatAgingTransaction(PatAgingTransaction.TransactionTypes.ClaimProc, PIn.Date(row["maxDateCP"].ToString())));
}
}
command = $@"SELECT {guarOrPat}.PatNum,{guarOrPat}.ClinicNum,MAX(payplancharge.ChargeDate) maxDatePPC,
MAX(payplancharge.SecDateTEntry) maxDatePPCSDTE
FROM payplancharge
INNER JOIN patient ON patient.PatNum = payplancharge.PatNum
INNER JOIN patient guar ON guar.PatNum=patient.Guarantor
INNER JOIN payplan ON payplan.PayPlanNum = payplancharge.PayPlanNum
AND payplan.PlanNum = 0 " //don't want insurance payment plans to make patients appear in the billing list
+ $@"WHERE payplancharge.Principal + payplancharge.Interest>0
AND payplancharge.ChargeType = {(int)PayPlanChargeType.Debit} "
//include all charges in the past or due 'PayPlanBillInAdvance' days into the future.
+ $@"AND payplancharge.ChargeDate <= {POut.Date(DateTime.Today.AddDays(PrefC.GetDouble(PrefName.PayPlansBillInAdvanceDays)))}
{whereAndClinNum}
GROUP BY {guarOrPat}.PatNum" ;
using (DataTable tableMaxPPCDate = Db.GetTable(command)) {
foreach (DataRow row in tableMaxPPCDate.Rows)
{
long patNum = PIn.Long(row["PatNum"].ToString());
if (!dictPatAgingData.ContainsKey(patNum))
{
dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString()));
}
dictPatAgingData[patNum].ListPatAgingTransactions
.Add(new PatAgingTransaction(
PatAgingTransaction.TransactionTypes.PayPlanCharge,
PIn.Date(row["maxDatePPC"].ToString()),
secDateTEntryTrans: PIn.Date(row["maxDatePPCSDTE"].ToString()))
);
}
}
}
if (excludeInsPending)
{
command = $@"SELECT {guarOrPat}.PatNum,{guarOrPat}.ClinicNum
FROM claim
INNER JOIN patient ON patient.PatNum=claim.PatNum
INNER JOIN patient guar ON guar.PatNum=patient.Guarantor
WHERE claim.ClaimStatus IN ('U','H','W','S')
AND claim.ClaimType IN ('P','S','Other')
{whereAndClinNum}
GROUP BY {guarOrPat}.PatNum" ;
using (DataTable tableInsPending = Db.GetTable(command)) {
foreach (DataRow row in tableInsPending.Rows)
{
long patNum = PIn.Long(row["PatNum"].ToString());
if (!dictPatAgingData.ContainsKey(patNum))
{
dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString()));
}
dictPatAgingData[patNum].HasPendingIns = true;
}
}
}
DateTime dateAsOf = DateTime.Today; //used to determine when the balance on this date began
if (PrefC.GetBool(PrefName.AgingCalculatedMonthlyInsteadOfDaily)) //if aging calculated monthly, use the last aging date instead of today
{
dateAsOf = PrefC.GetDate(PrefName.DateLastAging);
}
List <PatComm> listPatComms = new List <PatComm>();
using (DataTable tableDateBalsBegan = Ledgers.GetDateBalanceBegan(null, dateAsOf, isSuperBills, listClinicNums)) {
foreach (DataRow row in tableDateBalsBegan.Rows)
{
long patNum = PIn.Long(row["PatNum"].ToString());
if (!dictPatAgingData.ContainsKey(patNum))
{
dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString()));
}
dictPatAgingData[patNum].DateBalBegan = PIn.Date(row["DateAccountAge"].ToString());
dictPatAgingData[patNum].DateBalZero = PIn.Date(row["DateZeroBal"].ToString());
}
listPatComms = Patients.GetPatComms(tableDateBalsBegan.Select().Select(x => PIn.Long(x["PatNum"].ToString())).ToList(), null);
}
foreach (PatComm pComm in listPatComms)
{
if (!dictPatAgingData.ContainsKey(pComm.PatNum))
{
dictPatAgingData[pComm.PatNum] = new PatAgingData(pComm.ClinicNum);
}
dictPatAgingData[pComm.PatNum].PatComm = pComm;
}
return(dictPatAgingData);
}
public static string GetQueryString(RpAgingParamObject rpo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetString(MethodBase.GetCurrentMethod(), rpo));
}
//patient aging---------------------------------------------------------------------------
//The aging report always shows historical numbers based on the date entered.
//the selected columns have to remain in this order due to the way the report complex populates the returned sheet
string queryAg = "SELECT ";
if (rpo.IsForInsAging) //get patNum for insAgingReport only
{
queryAg += "patient.PatNum, ";
}
if (ReportsComplex.RunFuncOnReportServer(() => (Prefs.GetBoolNoCache(PrefName.ReportsShowPatNum))))
{
queryAg += DbHelper.Concat("patient.PatNum", "' - '", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
else
{
queryAg += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
queryAg += " patName,guarAging.Bal_0_30,guarAging.Bal_31_60,guarAging.Bal_61_90,guarAging.BalOver90,guarAging.BalTotal,"
+ "guarAging.InsWoEst,guarAging.InsPayEst,guarAging.BalTotal-guarAging.InsPayEst-guarAging.InsWoEst AS ";
if (DataConnection.DBtype == DatabaseType.MySql)
{
queryAg += "$pat";
}
else //Oracle needs quotes.
{
queryAg += "\"$pat\"";
}
//Must select "blankCol" for use with reportComplex to fix spacing of final column
queryAg += (rpo.HasDateLastPay ? ",'' blankCol,guarAging.DateLastPay " : " ")
+ "FROM ("
+ ReportsComplex.RunFuncOnReportServer(() => Ledgers.GetAgingQueryString(asOfDate: rpo.AsOfDate, isHistoric: rpo.IsHistoric,
isInsPayWoCombined: rpo.IsInsPayWoCombined, hasDateLastPay: rpo.HasDateLastPay, isGroupByGuar: rpo.IsGroupByFam, isWoAged: rpo.IsWoAged,
isForceAgeNegAdj: rpo.IsForceAgeNegAdj, doAgePatPayPlanPayments: rpo.DoAgePatPayPlanPayments))
+ ") guarAging "
+ "INNER JOIN patient ON patient.PatNum=guarAging.PatNum ";
List <string> listWhereAnds = new List <string>();
//InsAging will filter for age, but we need to return all in here order for the filtering to be correct
if (!rpo.IsForInsAging)
{
List <string> listAgeOrs = new List <string>();
if (rpo.IsIncludeNeg || rpo.IsOnlyNeg)
{
listAgeOrs.Add("guarAging.BalTotal <= -0.005");
}
if (rpo.IsIncludeInsNoBal || rpo.IsOnlyInsNoBal)
{
listAgeOrs.Add("((ABS(guarAging.InsPayEst) >= 0.005 OR ABS(guarAging.InsWoEst) >= 0.005) "
+ "AND guarAging.Bal_0_30 < 0.005 AND guarAging.Bal_31_60 < 0.005 AND guarAging.Bal_61_90 < 0.005 AND guarAging.BalOver90 < 0.005)");
}
if (!rpo.IsOnlyNeg && !rpo.IsOnlyInsNoBal)
{
listAgeOrs.Add("guarAging.BalOver90 >= 0.005"); //applies to all ages
if (rpo.AccountAge <= AgeOfAccount.Over60)
{
listAgeOrs.Add("guarAging.Bal_61_90 >= 0.005");
}
if (rpo.AccountAge <= AgeOfAccount.Over30)
{
listAgeOrs.Add("guarAging.Bal_31_60 >= 0.005");
}
if (rpo.AccountAge == AgeOfAccount.Any) //only applies to Any age
{
listAgeOrs.Add("guarAging.Bal_0_30 >= 0.005");
}
}
listWhereAnds.Add("(" + string.Join(" OR ", listAgeOrs) + ")");
}
if (rpo.IsExcludeInactive)
{
listWhereAnds.Add("patient.PatStatus != " + (int)PatientStatus.Inactive);
}
if (rpo.IsExcludeArchive)
{
listWhereAnds.Add("patient.PatStatus != " + (int)PatientStatus.Archived);
}
if (rpo.IsExcludeBadAddress)
{
listWhereAnds.Add("patient.Zip != ''");
}
if (rpo.ListBillTypes.Count > 0) //if all bill types is selected, list will be empty
{
listWhereAnds.Add("patient.BillingType IN (" + string.Join(",", rpo.ListBillTypes.Select(x => POut.Long(x))) + ")");
}
if (rpo.ListProvNums.Count > 0) //if all provs is selected, list will be empty
{
listWhereAnds.Add("patient.PriProv IN (" + string.Join(",", rpo.ListProvNums.Select(x => POut.Long(x))) + ")");
}
if (ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)))) //if clinics enabled, at least one clinic will be selected
//listClin may contain "Unassigned" clinic with ClinicNum 0, in which case it will also be in the query string
{
listWhereAnds.Add("patient.ClinicNum IN (" + string.Join(",", rpo.ListClinicNums.Select(x => POut.Long(x))) + ")");
}
if (listWhereAnds.Count > 0)
{
queryAg += "WHERE " + string.Join(" AND ", listWhereAnds) + " ";
}
queryAg += "ORDER BY patient.LName,patient.FName";
return(queryAg);
}
public static string GetQueryString(DateTime asOfDate, bool isWoAged, bool hasDateLastPay, bool isGroupByFam, bool isOnlyNeg, AgeOfAccount accountAge,
bool isIncludeNeg, bool isExcludeInactive, bool isExcludeBadAddress, List <long> listProv, List <long> listClinicNums, List <long> listBillType,
bool isExcludeArchive, bool isIncludeInsNoBal, bool isOnlyInsNoBal, bool?isForceAgeNegAdj, bool isForInsAging, bool doAgePatPayPlanPayments)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetString(MethodBase.GetCurrentMethod(), asOfDate, isWoAged, hasDateLastPay, isGroupByFam, isOnlyNeg, accountAge, isIncludeNeg,
isExcludeInactive, isExcludeBadAddress, listProv, listClinicNums, listBillType, isExcludeArchive, isIncludeInsNoBal, isOnlyInsNoBal,
isForceAgeNegAdj, isForInsAging, doAgePatPayPlanPayments));
}
//patient aging---------------------------------------------------------------------------
//The aging report always shows historical numbers based on the date entered.
//the selected columns have to remain in this order due to the way the report complex populates the returned sheet
string queryAg = "SELECT ";
if (isForInsAging) //get patNum for insAgingReport only
{
queryAg += "patient.PatNum, ";
}
if (ReportsComplex.RunFuncOnReportServer(() => (Prefs.GetBoolNoCache(PrefName.ReportsShowPatNum))))
{
queryAg += DbHelper.Concat("patient.PatNum", "' - '", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
else
{
queryAg += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
queryAg += "patName,guarAging.Bal_0_30,guarAging.Bal_31_60,guarAging.Bal_61_90,guarAging.BalOver90,guarAging.BalTotal,"
+ "guarAging.InsWoEst,guarAging.InsPayEst,guarAging.BalTotal-guarAging.InsPayEst-guarAging.InsWoEst AS ";
if (DataConnection.DBtype == DatabaseType.MySql)
{
queryAg += "$pat";
}
else //Oracle needs quotes.
{
queryAg += "\"$pat\"";
}
bool isHistoric = (asOfDate.Date != DateTime.Today);
bool isInsPayWoCombined = false;
//Must select "blankCol" for use with reportComplex to fix spacing of final column
queryAg += (hasDateLastPay ? ",'' blankCol,guarAging.DateLastPay " : " ")
+ "FROM ("
+ ReportsComplex.RunFuncOnReportServer(() => Ledgers.GetAgingQueryString(asOfDate, null, isHistoric, isInsPayWoCombined, hasDateLastPay,
isGroupByFam, isWoAged, isForceAgeNegAdj, doAgePatPayPlanPayments))
+ ") guarAging "
+ "INNER JOIN patient ON patient.PatNum=guarAging.PatNum "
+ "WHERE ";
List <string> listAgeOrs = new List <string>();
if (isIncludeNeg || isOnlyNeg)
{
listAgeOrs.Add("guarAging.BalTotal < -0.005");
}
if (isIncludeInsNoBal || isOnlyInsNoBal)
{
listAgeOrs.Add("((guarAging.InsPayEst > 0.005 OR guarAging.InsWoEst > 0.005) AND guarAging.Bal_0_30 < 0.005 AND guarAging.Bal_31_60 < 0.005 "
+ "AND guarAging.Bal_61_90 < 0.005 AND guarAging.BalOver90 < 0.005)");
}
if (!isOnlyNeg && !isOnlyInsNoBal)
{
if (accountAge <= AgeOfAccount.Over90)
{
listAgeOrs.Add("guarAging.BalOver90 > 0.005");
}
if (accountAge <= AgeOfAccount.Over60)
{
listAgeOrs.Add("guarAging.Bal_61_90 > 0.005");
}
if (accountAge <= AgeOfAccount.Over30)
{
listAgeOrs.Add("guarAging.Bal_31_60 > 0.005");
}
if (accountAge <= AgeOfAccount.Any)
{
listAgeOrs.Add("guarAging.Bal_0_30 > 0.005");
}
}
queryAg += "(" + string.Join(" OR ", listAgeOrs) + ") ";
if (isExcludeInactive)
{
queryAg += "AND patient.PatStatus != " + (int)PatientStatus.Inactive + " ";
}
if (isExcludeArchive)
{
queryAg += "AND patient.PatStatus != " + (int)PatientStatus.Archived + " ";
}
if (isExcludeBadAddress)
{
queryAg += "AND patient.Zip != '' ";
}
if (listBillType.Count > 0) //if all bill types is selected, list will be empty
{
queryAg += "AND patient.BillingType IN (" + string.Join(",", listBillType.Select(x => POut.Long(x))) + ") ";
}
if (listProv.Count > 0) //if all provs is selected, list will be empty
{
queryAg += "AND patient.PriProv IN (" + string.Join(",", listProv.Select(x => POut.Long(x))) + ") ";
}
if (ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)))) //validated to have at least one clinic selected if clinics are enabled above
//listClin may contain "Unassigned" clinic with ClinicNum 0, in which case it will also be in the query string
{
queryAg += "AND patient.ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
queryAg += "ORDER BY patient.LName,patient.FName";
return(queryAg);
}