private AgeOfAccount GenerateLists(List <long> listProvNums, List <long> listClinicNums, List <long> listBillingTypeDefNums)
{
if (!checkProvAll.Checked)
{
listProvNums.AddRange(listProv.SelectedIndices.OfType <int>().Select(x => _listProviders[x].ProvNum).ToList());
}
if (PrefC.HasClinicsEnabled)
{
//if "All" is selected and the user is not restricted, show ALL clinics, including the 0 clinic.
if (checkAllClin.Checked && !Security.CurUser.ClinicIsRestricted)
{
listClinicNums.Clear();
listClinicNums.Add(0);
Clinics.GetDeepCopy().ForEach(x => listClinicNums.Add(x.ClinicNum));
}
else
{
listClinicNums.AddRange(listClin.SelectedItems.OfType <ODBoxItem <Clinic> >().Select(x => x.Tag.ClinicNum).ToList());
}
}
if (!checkBillTypesAll.Checked)
{
for (int i = 0; i < listBillType.SelectedIndices.Count; i++)
{
listBillingTypeDefNums.Add(_listBillingTypeDefs[listBillType.SelectedIndices[i]].DefNum);
}
}
AgeOfAccount accountAge = AgeOfAccount.Any;
if (radioAny.Checked)
{
accountAge = AgeOfAccount.Any;
}
else if (radio30.Checked)
{
accountAge = AgeOfAccount.Over30;
}
else if (radio60.Checked)
{
accountAge = AgeOfAccount.Over60;
}
else if (radio90.Checked)
{
accountAge = AgeOfAccount.Over90;
}
return(accountAge);
}
private void butGenerateQuery_Click(object sender, EventArgs e)
{
if (!Validation())
{
return;
}
DateTime asOfDate = PIn.Date(textDate.Text);
List <long> listProvNums = new List <long>();
List <long> listClinicNums = new List <long>();
List <long> listBillingTypeDefNums = new List <long>();
AgeOfAccount accountAge = GenerateLists(listProvNums, listClinicNums, listBillingTypeDefNums);
bool isForInsAging = false;
string queryStr = RpAging.GetQueryString(asOfDate, checkAgeWriteoffs.Checked, checkHasDateLastPay.Checked, radioGroupByFam.Checked, checkOnlyNeg.Checked,
accountAge, checkIncludeNeg.Checked, checkExcludeInactive.Checked, checkBadAddress.Checked, listProvNums, listClinicNums, listBillingTypeDefNums,
checkExcludeArchive.Checked, checkIncludeInsNoBal.Checked, checkOnlyInsNoBal.Checked, checkAgeNegAdjs.Checked, isForInsAging,
checkAgePatPayPlanPayments.Checked);
MsgBoxCopyPaste msgBox = new MsgBoxCopyPaste(queryStr);
msgBox.Show(this);
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (!checkBillTypesAll.Checked && listBillType.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one billing type must be selected.");
return;
}
if (!checkProvAll.Checked && listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
if (PrefC.HasClinicsEnabled && !checkAllClin.Checked && listClin.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one clinic must be selected.");
return;
}
if (textDate.errorProvider1.GetError(textDate) != "")
{
MsgBox.Show(this, "Invalid date.");
return;
}
DateTime asOfDate = PIn.Date(textDate.Text);
List <long> listProvNums = new List <long>();
List <long> listClinicNums = new List <long>();
List <long> listBillingTypeDefNums = new List <long>();
if (!checkProvAll.Checked)
{
listProvNums = listProv.SelectedIndices.OfType <int>().Select(x => _listProviders[x].ProvNum).ToList();
}
if (PrefC.HasClinicsEnabled)
{
if (checkAllClin.Checked)
{
listClinicNums = listClin.Items.OfType <ODBoxItem <Clinic> >().Select(x => x.Tag.ClinicNum).ToList();
}
else
{
listClinicNums = listClin.SelectedItems.OfType <ODBoxItem <Clinic> >().Select(x => x.Tag.ClinicNum).ToList();
}
}
if (!checkBillTypesAll.Checked)
{
for (int i = 0; i < listBillType.SelectedIndices.Count; i++)
{
listBillingTypeDefNums.Add(_listBillingTypeDefs[listBillType.SelectedIndices[i]].DefNum);
}
}
AgeOfAccount accountAge = AgeOfAccount.Any;
if (radioAny.Checked)
{
accountAge = AgeOfAccount.Any;
}
else if (radio30.Checked)
{
accountAge = AgeOfAccount.Over30;
}
else if (radio60.Checked)
{
accountAge = AgeOfAccount.Over60;
}
else if (radio90.Checked)
{
accountAge = AgeOfAccount.Over90;
}
ReportComplex report = new ReportComplex(true, true);
DataTable tableAging = new DataTable();
tableAging = RpInsAging.GetInsAgingTable
(asOfDate, radioGroupByFam.Checked, accountAge, checkBillTypesAll.Checked, checkProvAll.Checked, listProvNums, listClinicNums, listBillingTypeDefNums);
report.ReportName = Lan.g(this, "Insurance Aging Report");
report.AddTitle("InsAging", Lan.g(this, "Insurance Aging Report"));
report.AddSubTitle("PracTitle", PrefC.GetString(PrefName.PracticeTitle));
report.AddSubTitle("AsOf", Lan.g(this, "As of ") + textDate.Text);
if (radioAny.Checked)
{
report.AddSubTitle("Balance", Lan.g(this, "Any Balance"));
}
else if (radio30.Checked)
{
report.AddSubTitle("Over30", Lan.g(this, "Over 30 Days"));
}
else if (radio60.Checked)
{
report.AddSubTitle("Over60", Lan.g(this, "Over 60 Days"));
}
else if (radio90.Checked)
{
report.AddSubTitle("Over90", Lan.g(this, "Over 90 Days"));
}
if (checkBillTypesAll.Checked)
{
report.AddSubTitle("AllBillingTypes", Lan.g(this, "All Billing Types"));
}
else
{
string subt = _listBillingTypeDefs[listBillType.SelectedIndices[0]].ItemName;
for (int i = 1; i < listBillType.SelectedIndices.Count; i++) //there must be at least one selected
{
subt += ", " + _listBillingTypeDefs[listBillType.SelectedIndices[i]].ItemName;
}
report.AddSubTitle("", subt);
}
string subtitleProvs = "";
if (checkProvAll.Checked)
{
subtitleProvs = Lan.g(this, "All Providers");
}
else
{
subtitleProvs += string.Join(", ", listProv.SelectedIndices.OfType <int>().ToList().Select(x => _listProviders[x].Abbr));
}
report.AddSubTitle("Providers", subtitleProvs);
if (checkAllClin.Checked)
{
report.AddSubTitle("Clinics", Lan.g(this, "All Clinics"));
}
else
{
string subt = "";
for (int i = 0; i < listClin.SelectedIndices.Count; i++)
{
if (i != 0)
{
subt += ", ";
}
if (Security.CurUser.ClinicIsRestricted)
{
subt += _listClinics[listClin.SelectedIndices[i]].Abbr;
}
else
{
if (listClin.SelectedIndices[i] == 0)
{
subt += "Unassigned";
continue;
}
subt += _listClinics[listClin.SelectedIndices[i] - 1].Abbr;
}
}
report.AddSubTitle("Clinics", subt);
}
//Patient Account Aging Query-----------------------------------------------
QueryObject query = report.AddQuery(tableAging, "Date " + DateTime.Today.ToShortDateString());
query.AddColumn((radioGroupByFam.Checked?"GUARANTOR":"PATIENT"), 160, FieldValueType.String);
query.AddColumn("INS PAY EST 0-30", 75, FieldValueType.Number);
query.AddColumn("INS PAY EST 31-60", 75, FieldValueType.Number);
query.AddColumn("INS PAY EST 61-90", 75, FieldValueType.Number);
query.AddColumn("INS PAY EST >90", 75, FieldValueType.Number);
query.AddColumn("INS EST TOTAL", 80, FieldValueType.Number);
query.AddColumn("PAT EST BAL 0-30", 75, FieldValueType.Number);
query.AddColumn("PAT EST BAL 31-60", 75, FieldValueType.Number);
query.AddColumn("PAT EST BAL 61-90", 75, FieldValueType.Number);
query.AddColumn("PAT EST BAL >90", 75, FieldValueType.Number);
query.AddColumn("PAT TOTAL", 80, FieldValueType.Number);
report.AddPageNum();
if (!report.SubmitQueries())
{
return;
}
FormReportComplex FormR = new FormReportComplex(report);
FormR.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (!Validation())
{
return;
}
DateTime asOfDate = PIn.Date(textDate.Text);
List <long> listProvNums = new List <long>();
List <long> listClinicNums = new List <long>();
List <long> listBillingTypeDefNums = new List <long>();
AgeOfAccount accountAge = GenerateLists(listProvNums, listClinicNums, listBillingTypeDefNums);
DataTable tableAging = new DataTable();
bool isForInsAging = false;
tableAging = RpAging.GetAgingTable(asOfDate, checkAgeWriteoffs.Checked, checkHasDateLastPay.Checked, radioGroupByFam.Checked, checkOnlyNeg.Checked,
accountAge, checkIncludeNeg.Checked, checkExcludeInactive.Checked, checkBadAddress.Checked, listProvNums, listClinicNums, listBillingTypeDefNums,
checkExcludeArchive.Checked, checkIncludeInsNoBal.Checked, checkOnlyInsNoBal.Checked, checkAgeNegAdjs.Checked, isForInsAging,
checkAgePatPayPlanPayments.Checked);
ReportComplex report = new ReportComplex(true, false);
report.IsLandscape = checkHasDateLastPay.Checked;
report.ReportName = Lan.g(this, "AGING OF ACCOUNTS RECEIVABLE REPORT");
report.AddTitle("Aging Report", Lan.g(this, "AGING OF ACCOUNTS RECEIVABLE"));
report.AddSubTitle("PracTitle", PrefC.GetString(PrefName.PracticeTitle));
report.AddSubTitle("AsOf", Lan.g(this, "As of ") + textDate.Text);
if (radioAny.Checked)
{
report.AddSubTitle("Balance", Lan.g(this, "Any Balance"));
}
if (radio30.Checked)
{
report.AddSubTitle("Over30", Lan.g(this, "Over 30 Days"));
}
if (radio60.Checked)
{
report.AddSubTitle("Over60", Lan.g(this, "Over 60 Days"));
}
if (radio90.Checked)
{
report.AddSubTitle("Over90", Lan.g(this, "Over 90 Days"));
}
if (checkBillTypesAll.Checked)
{
report.AddSubTitle("AllBillingTypes", Lan.g(this, "All Billing Types"));
}
else
{
string subt = _listBillingTypeDefs[listBillType.SelectedIndices[0]].ItemName;
for (int i = 1; i < listBillType.SelectedIndices.Count; i++)
{
subt += ", " + _listBillingTypeDefs[listBillType.SelectedIndices[i]].ItemName;
}
report.AddSubTitle("", subt);
}
string subtitleProvs = "";
if (checkProvAll.Checked)
{
subtitleProvs = Lan.g(this, "All Providers");
}
else
{
subtitleProvs += string.Join(", ", listProv.SelectedIndices.OfType <int>().ToList().Select(x => _listProviders[x].Abbr));
}
report.AddSubTitle("Providers", subtitleProvs);
if (checkAllClin.Checked)
{
report.AddSubTitle("Clinics", Lan.g(this, "All Clinics"));
}
else
{
string subt = string.Join(", ", listClin.SelectedItems.OfType <ODBoxItem <Clinic> >().Select(x => x.Tag.Abbr));
report.AddSubTitle("Clinics", subt);
}
//Patient Account Aging Query-----------------------------------------------
bool isWoEstIncluded = true;
if (checkAgeWriteoffs.Checked && tableAging.Select().All(x => Math.Abs(PIn.Double(x["InsWoEst"].ToString())) <= 0.005))
{
tableAging.Columns.Remove("InsWoEst");
isWoEstIncluded = false;
}
QueryObject query = report.AddQuery(tableAging, "Date " + DateTime.Today.ToShortDateString());
query.AddColumn((radioGroupByFam.Checked?"GUARANTOR":"PATIENT"), 160, FieldValueType.String);
query.AddColumn("0-30 DAYS", 75, FieldValueType.Number);
query.AddColumn("31-60 DAYS", 75, FieldValueType.Number);
query.AddColumn("61-90 DAYS", 75, FieldValueType.Number);
query.AddColumn("> 90 DAYS", 75, FieldValueType.Number);
query.AddColumn("TOTAL", 80, FieldValueType.Number);
if (isWoEstIncluded)
{
query.AddColumn("-W/O EST", 75, FieldValueType.Number);
}
query.AddColumn("-INS EST", 75, FieldValueType.Number);
query.AddColumn("=PATIENT", 80, FieldValueType.Number);
if (checkHasDateLastPay.Checked)
{
query.AddColumn("", 10); //add some space between the right alligned amounts and the left alligned date
query.AddColumn("LAST PAY DATE", 100, FieldValueType.Date);
}
report.AddPageNum();
report.AddGridLines();
if (!report.SubmitQueries())
{
return;
}
FormReportComplex FormR = new FormReportComplex(report);
FormR.ShowDialog();
DialogResult = DialogResult.OK;
}
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);
}
public static DataTable GetAgingTable(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.GetTable(MethodBase.GetCurrentMethod(), asOfDate, isWoAged, hasDateLastPay, isGroupByFam, isOnlyNeg, accountAge, isIncludeNeg,
isExcludeInactive, isExcludeBadAddress, listProv, listClinicNums, listBillType, isExcludeArchive, isIncludeInsNoBal, isOnlyInsNoBal,
isForceAgeNegAdj, isForInsAging, doAgePatPayPlanPayments));
}
string queryAg = GetQueryString(asOfDate, isWoAged, hasDateLastPay, isGroupByFam, isOnlyNeg, accountAge, isIncludeNeg, isExcludeInactive,
isExcludeBadAddress, listProv, listClinicNums, listBillType, isExcludeArchive, isIncludeInsNoBal, isOnlyInsNoBal, isForceAgeNegAdj, isForInsAging
, doAgePatPayPlanPayments);
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryAg)));
}
public static DataTable GetInsAgingTable(DateTime asOfDate, bool isGroupByFam, AgeOfAccount accountAge,
bool hasBillTypesAll, bool hasProvAll, List <long> listProv, List <long> listClinicNums, List <long> listBillType)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), asOfDate, isGroupByFam,
accountAge, hasBillTypesAll, hasProvAll, listProv, listClinicNums, listBillType));
}
//ins aging---------------------------------------------------------------------------
if (asOfDate.Year < 1880)
{
asOfDate = DateTime.Today;
}
string asOfDateStr = POut.Date(asOfDate);
string thirtyDaysAgo = POut.Date(asOfDate.AddDays(-30));
string sixtyDaysAgo = POut.Date(asOfDate.AddDays(-60));
string ninetyDaysAgo = POut.Date(asOfDate.AddDays(-90));
string command = "SELECT patient.PatNum, ";
if (ReportsComplex.RunFuncOnReportServer(() => (Prefs.GetBoolNoCache(PrefName.ReportsShowPatNum))))
{
command += DbHelper.Concat("patient.PatNum", "' - '", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
else
{
command += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
command += @"patName,
guarAging.InsEst0_30 AS InsBal_0_30,
guarAging.InsEst31_60 AS InsBal_31_60,
guarAging.InsEst61_90 AS InsBal_61_90,
guarAging.InsEst90 AS InsBal_90,
guarAging.InsPayEst AS InsBalTotal
FROM (
SELECT tSums.PatNum," //if grouped by guar, this is the guar's PatNum; if grouped by patient.PatNum rows are individual pats
+ @"tSums.InsPayEst InsPayEst,
tSums.InsOver90 InsEst90,
tSums.Ins61_90 InsEst61_90,
tSums.Ins31_60 InsEst31_60,
tSums.Ins0_30 InsEst0_30
FROM(" ;
command += "SELECT " + (isGroupByFam?"p.Guarantor PatNum,":"trans.PatNum,");
command += "SUM(CASE WHEN trans.TranDate <= " + asOfDateStr + @" AND trans.InsPayEst != 0 THEN trans.InsPayEst ELSE 0 END) InsPayEst,
SUM(CASE WHEN trans.TranDate < " + ninetyDaysAgo + @" THEN IFNULL(trans.InsPayEst,0) ELSE 0 END) InsOver90,
SUM(CASE WHEN trans.TranDate < " + sixtyDaysAgo + @" AND trans.TranDate >= " + ninetyDaysAgo + @" THEN IFNULL(trans.InsPayEst,0) ELSE 0 END) Ins61_90,
SUM(CASE WHEN trans.TranDate < " + thirtyDaysAgo + @" AND trans.TranDate >= " + sixtyDaysAgo + @" THEN IFNULL(trans.InsPayEst,0) ELSE 0 END) Ins31_60,
SUM(CASE WHEN trans.TranDate <= " + asOfDateStr + @" AND trans.TranDate >= " + thirtyDaysAgo + @" THEN IFNULL(trans.InsPayEst,0) ELSE 0 END) Ins0_30
FROM(" ;
#region Regular Claimproc By DateCP
command += @"
SELECT cp.ProcDate TranDate,
(CASE WHEN cp.ProcDate <= " + asOfDateStr + @"
AND (cp.Status = " + (int)ClaimProcStatus.NotReceived + @" OR (cp.Status = " + (int)ClaimProcStatus.Received + " AND cp.DateCP > " + asOfDateStr + @"))
THEN cp.InsPayEst ELSE 0 END) InsPayEst,
cp.PatNum
FROM claimproc cp
WHERE cp.status IN (" + (int)ClaimProcStatus.NotReceived + "," + (int)ClaimProcStatus.Received + ") ";
#endregion Regular Claimproc By DateCP
command += ") trans ";
if (isGroupByFam)
{
command += "INNER JOIN patient p ON p.PatNum=trans.PatNum "
+ "GROUP BY p.Guarantor";
if (!PrefC.GetBool(PrefName.AgingIsEnterprise)) //only if for one fam or if not using famaging table
{
command += " ORDER BY NULL";
}
}
else
{
command += "GROUP BY trans.PatNum";
}
command += ") tSums"
+ ") guarAging "
+ "INNER JOIN patient ON patient.PatNum=guarAging.PatNum ";
command += "WHERE TRUE ";
if (listBillType.Count > 0) //if all bill types is selected, list will be empty
{
command += "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
{
command += "AND patient.PriProv IN (" + string.Join(",", listProv.Select(x => POut.Long(x))) + ") ";
}
if (listClinicNums.Count > 0)
{
//listClin may contain "Unassigned" clinic with ClinicNum 0, in which case it will also be in the query string
command += "AND patient.ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
command += "AND (guarAging.InsEst0_30 > 0.005 OR guarAging.InsEst31_60 > 0.005 OR guarAging.InsEst61_90 > 0.005 OR guarAging.InsEst90 > 0.005 "
+ "OR guarAging.InsPayEst > 0.005) ";
command += "ORDER BY patient.LName,patient.FName";
DataTable insTable = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
//-- regular aging table --------------------------------------------------
bool isOnlyNeg = false;
bool isWoAged = true;
bool isIncludeNeg = true;
bool hasDateLastPay = false;
bool isExcludeInactive = false;
bool isExcludeBadAddress = false;
bool isExcludeArchived = false;
bool isOnlyInsNoBal = false;
bool isIncludeInsNoBal = false;
bool? isForceAgeNegAdj = null;
bool isForInsAging = true;
bool doAgePatPayPlanPayments = false;
DataTable regAging = ReportsComplex.RunFuncOnReportServer(() => RpAging.GetAgingTable(asOfDate, isWoAged, hasDateLastPay, isGroupByFam, isOnlyNeg,
AgeOfAccount.Any, isIncludeNeg, isExcludeInactive, isExcludeBadAddress, listProv, listClinicNums, listBillType, isExcludeArchived, isIncludeInsNoBal,
isOnlyInsNoBal, isForceAgeNegAdj, isForInsAging, doAgePatPayPlanPayments));
//------------ Combine Tables ---------------------------------------------
DataTable insAgingTable = new DataTable();
//define columns here
insAgingTable.Columns.Add("PatName");
insAgingTable.Columns.Add("InsBal_0_30");
insAgingTable.Columns.Add("InsBal_31_60");
insAgingTable.Columns.Add("InsBal_61_90");
insAgingTable.Columns.Add("InsBal_90");
insAgingTable.Columns.Add("InsBal_Total");
insAgingTable.Columns.Add("PatBal_0_30");
insAgingTable.Columns.Add("PatBal_31_60");
insAgingTable.Columns.Add("PatBal_61_90");
insAgingTable.Columns.Add("PatBal_90");
insAgingTable.Columns.Add("PatBal_Total");
insAgingTable.Columns.Add("PatNum"); //this will not show, for correlating the two (ins and pat) aging tables to each other.
List <DataRow> listInsAgingRows = new List <DataRow>();
//loop through the insurance aging table
foreach (DataRow insRow in insTable.Rows)
{
//create a new row with the structure of the new table
DataRow newRow = insAgingTable.NewRow();
//copy the insurance aging table's values over to the new row. (also fill the patient bal columns with -(insurance estimate) for the appropriate bucket.)
newRow["PatNum"] = insRow["patNum"];
newRow["PatName"] = insRow["patName"];
newRow["InsBal_0_30"] = insRow["InsBal_0_30"];
newRow["InsBal_31_60"] = insRow["InsBal_31_60"];
newRow["InsBal_61_90"] = insRow["InsBal_61_90"];
newRow["InsBal_90"] = insRow["InsBal_90"];
newRow["InsBal_Total"] = insRow["InsBalTotal"];
newRow["PatBal_0_30"] = -PIn.Double(insRow["InsBal_0_30"].ToString());
newRow["PatBal_31_60"] = -PIn.Double(insRow["InsBal_31_60"].ToString());
newRow["PatBal_61_90"] = -PIn.Double(insRow["InsBal_61_90"].ToString());
newRow["PatBal_90"] = -PIn.Double(insRow["InsBal_90"].ToString());
newRow["PatBal_Total"] = -PIn.Double(insRow["InsBalTotal"].ToString());
listInsAgingRows.Add(newRow);
}
//-------------------------------------------------------------------------------------------------------------------------------------
//loop through rows in the regular aging table
foreach (DataRow row in regAging.Rows)
{
DataRow insAgingRow = listInsAgingRows.FirstOrDefault(x => x["PatNum"].ToString() == row["PatNum"].ToString());
//check to see if that patient exists in the insurance aging report
if (insAgingRow != null)
{
insAgingRow["PatBal_0_30"] = PIn.Double(insAgingRow["PatBal_0_30"].ToString()) + PIn.Double(row["Bal_0_30"].ToString());
insAgingRow["PatBal_31_60"] = PIn.Double(insAgingRow["PatBal_31_60"].ToString()) + PIn.Double(row["Bal_31_60"].ToString());
insAgingRow["PatBal_61_90"] = PIn.Double(insAgingRow["PatBal_61_90"].ToString()) + PIn.Double(row["Bal_61_90"].ToString());
insAgingRow["PatBal_90"] = PIn.Double(insAgingRow["PatBal_90"].ToString()) + PIn.Double(row["BalOver90"].ToString());
insAgingRow["PatBal_Total"] = PIn.Double(insAgingRow["PatBal_Total"].ToString()) + PIn.Double(row["BalTotal"].ToString());
}
else //if it doesn't create a new row with 0.00 insurance values and fill the patient aging values.
{
DataRow newRow = insAgingTable.NewRow();
newRow["PatName"] = row["patName"];
newRow["InsBal_0_30"] = PIn.Double("0.00");
newRow["InsBal_31_60"] = PIn.Double("0.00");
newRow["InsBal_61_90"] = PIn.Double("0.00");
newRow["InsBal_90"] = PIn.Double("0.00");
newRow["InsBal_Total"] = PIn.Double("0.00");
newRow["PatBal_0_30"] = PIn.Double(row["Bal_0_30"].ToString());
newRow["PatBal_31_60"] = PIn.Double(row["Bal_31_60"].ToString());
newRow["PatBal_61_90"] = PIn.Double(row["Bal_61_90"].ToString());
newRow["PatBal_90"] = PIn.Double(row["BalOver90"].ToString());
newRow["PatBal_Total"] = PIn.Double(row["BalTotal"].ToString());
listInsAgingRows.Add(newRow);
}
}
listInsAgingRows = listInsAgingRows.OrderBy(x => x["PatName"]).ToList();
foreach (DataRow rowCur in listInsAgingRows)
{
if (accountAge == AgeOfAccount.Any)
{
insAgingTable.Rows.Add(rowCur);
}
else if (accountAge == AgeOfAccount.Over30)
{
if (PIn.Double(rowCur["PatBal_31_60"].ToString()) != 0 ||
PIn.Double(rowCur["InsBal_31_60"].ToString()) != 0 ||
PIn.Double(rowCur["PatBal_61_90"].ToString()) != 0 ||
PIn.Double(rowCur["InsBal_61_90"].ToString()) != 0 ||
PIn.Double(rowCur["PatBal_90"].ToString()) != 0 ||
PIn.Double(rowCur["InsBal_90"].ToString()) != 0)
{
insAgingTable.Rows.Add(rowCur);
}
}
else if (accountAge == AgeOfAccount.Over60)
{
if (PIn.Double(rowCur["PatBal_61_90"].ToString()) != 0 ||
PIn.Double(rowCur["InsBal_61_90"].ToString()) != 0 ||
PIn.Double(rowCur["PatBal_90"].ToString()) != 0 ||
PIn.Double(rowCur["InsBal_90"].ToString()) != 0)
{
insAgingTable.Rows.Add(rowCur);
}
}
else
{
if (PIn.Double(rowCur["PatBal_90"].ToString()) != 0 ||
PIn.Double(rowCur["InsBal_90"].ToString()) != 0)
{
insAgingTable.Rows.Add(rowCur);
}
}
}
return(insAgingTable);
}