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); }
///<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 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); }