예제 #1
0
        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);
        }
예제 #2
0
        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);
        }
예제 #3
0
        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;
        }
예제 #4
0
        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;
        }
예제 #5
0
        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);
        }
예제 #6
0
        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)));
        }
예제 #7
0
        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);
        }