///<summary>Gets a pref of type bool.</summary>
public static bool GetBool(PrefName prefName)
{
return(PIn.Bool(Prefs.GetOne(prefName).ValueString));
}
///<summary>Gets a pref of type datetime.</summary>
public static DateTime GetDateT(PrefName prefName)
{
return(PIn.DateT(Prefs.GetOne(prefName).ValueString));
}
///<summary>Gets a pref of type byte. Used when the pref is a very small integer (0-255).</summary>
public static byte GetByte(PrefName prefName)
{
return(PIn.Byte(Prefs.GetOne(prefName).ValueString));
}
///<summary>Gets a pref of type double.</summary>
public static double GetDouble(PrefName prefName)
{
return(PIn.Double(Prefs.GetOne(prefName).ValueString));
}
///<summary>Gets a pref of type long.</summary>
public static long GetLong(PrefName prefName)
{
return(PIn.Long(Prefs.GetOne(prefName).ValueString));
}
///<summary>Gets a pref of type int32. Used when the pref is an enumeration, itemorder, etc. Also used for historical queries in ConvertDatabase.</summary>
public static int GetInt(PrefName prefName)
{
return(PIn.Int(Prefs.GetOne(prefName).ValueString));
}
///<summary>Returns the number of subscribers moved.</summary>
public static long MoveSubscribers(long insPlanNumFrom, long insPlanNumTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod(), insPlanNumFrom, insPlanNumTo));
}
List <InsSub> listInsSubsFrom = GetListForPlanNum(insPlanNumFrom);
List <long> listBlockedPatNums = new List <long>();
//Perform the same validation as when the user manually drops insplans from FormInsPlan using the Drop button.
for (int i = 0; i < listInsSubsFrom.Count; i++)
{
InsSub insSubFrom = listInsSubsFrom[i];
List <PatPlan> listPatPlanFrom = PatPlans.Refresh(insSubFrom.Subscriber);
for (int j = 0; j < listPatPlanFrom.Count; j++)
{
PatPlan patPlanFrom = listPatPlanFrom[j];
//The following comments and logic are copied from the FormInsPlan Drop button...
//If they have a claim for this ins with today's date, don't let them drop.
//We already have code in place to delete claimprocs when we drop ins, but the claimprocs attached to claims are protected.
//The claim clearly needs to be deleted if they are dropping. We need the user to delete the claim before they drop the plan.
//We also have code in place to add new claimprocs when they add the correct insurance.
List <Claim> listClaims = Claims.Refresh(patPlanFrom.PatNum); //Get all claims for patient.
for (int k = 0; k < listClaims.Count; k++)
{
if (listClaims[k].PlanNum != insPlanNumFrom) //Make sure the claim is for the insurance plan we are about to change, not any other plans the patient might have.
{
continue;
}
if (listClaims[k].DateService != DateTime.Today) //not today
{
continue;
}
//Patient currently has a claim for the insplan they are trying to drop.
if (!listBlockedPatNums.Contains(patPlanFrom.PatNum))
{
listBlockedPatNums.Add(patPlanFrom.PatNum);
}
}
}
}
if (listBlockedPatNums.Count > 0)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < listBlockedPatNums.Count; i++)
{
sb.Append("\r\n");
Patient pat = Patients.GetPat(listBlockedPatNums[i]);
sb.Append("#" + listBlockedPatNums[i] + " " + pat.GetNameFLFormal());
}
throw new ApplicationException(Lans.g("InsSubs", "Before changing the subscribers on the insurance plan being moved from, please delete all of today's claims related to the insurance plan being moved from for the following patients") + ":" + sb.ToString());
}
//This loop mimics some of the logic in PatPlans.Delete().
int insSubMovedCount = 0;
for (int i = 0; i < listInsSubsFrom.Count; i++)
{
InsSub inssub = listInsSubsFrom[i];
long oldInsSubNum = inssub.InsSubNum;
inssub.InsSubNum = 0; //This will allow us to insert a new record.
inssub.PlanNum = insPlanNumTo;
inssub.DateEffective = DateTime.MinValue;
inssub.BenefitNotes = "";
inssub.SubscNote = "";
//Security.CurUser.UserNum gets set on MT by the DtoProcessor so it matches the user from the client WS.
inssub.SecUserNumEntry = Security.CurUser.UserNum;
long insSubNumNew = InsSubs.Insert(inssub);
string command = "SELECT PatNum FROM patplan WHERE InsSubNum=" + POut.Long(oldInsSubNum);
DataTable tablePatsForInsSub = Db.GetTable(command);
if (tablePatsForInsSub.Rows.Count == 0)
{
continue;
}
insSubMovedCount++;
for (int j = 0; j < tablePatsForInsSub.Rows.Count; j++)
{
long patNum = PIn.Long(tablePatsForInsSub.Rows[j]["PatNum"].ToString());
List <PatPlan> listPatPlans = PatPlans.Refresh(patNum);
for (int k = 0; k < listPatPlans.Count; k++)
{
PatPlan patPlan = listPatPlans[k];
if (patPlan.InsSubNum == oldInsSubNum)
{
command = "DELETE FROM benefit WHERE PatPlanNum=" + POut.Long(patPlan.PatPlanNum); //Delete patient specific benefits (rare).
Db.NonQ(command);
patPlan.InsSubNum = insSubNumNew;
PatPlans.Update(patPlan);
}
}
//Now that the plan has changed for the current subscriber, recalculate estimates.
bool prefChanged = false;
//Forcefully set pref false to prevent creating new estimates for all procs (including completed, sent procs)
if (Prefs.UpdateBool(PrefName.ClaimProcsAllowedToBackdate, false))
{
prefChanged = true; //We will turn the preference back on for the user after we finish our computations.
}
Family fam = Patients.GetFamily(patNum);
Patient pat = fam.GetPatient(patNum);
List <ClaimProc> listClaimProcs = ClaimProcs.Refresh(patNum);
List <Procedure> listProcs = Procedures.GetProcsByStatusForPat(patNum, ProcStat.TP, ProcStat.TPi);
listPatPlans = PatPlans.Refresh(patNum);
List <InsSub> listInsSubs = InsSubs.RefreshForFam(fam);
List <InsPlan> listInsPlans = InsPlans.RefreshForSubList(listInsSubs);
List <Benefit> listBenefits = Benefits.Refresh(listPatPlans, listInsSubs);
Procedures.ComputeEstimatesForAll(patNum, listClaimProcs, listProcs, listInsPlans, listPatPlans, listBenefits, pat.Age, listInsSubs);
if (prefChanged)
{
Prefs.UpdateBool(PrefName.ClaimProcsAllowedToBackdate, true); //set back to original value if changed.
}
}
}
InsPlan insPlanFrom = InsPlans.RefreshOne(insPlanNumFrom);
InsPlan planOld = insPlanFrom.Copy();
insPlanFrom.IsHidden = true;
InsPlans.Update(insPlanFrom, planOld);
return(insSubMovedCount);
}
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,
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.HasClinicsEnabledNoCache)) //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);
}
///<summary>Called by local practice db to query HQ for EService setup info. Must remain very lite and versionless. Will be used by signup portal.
///If HasClinics==true then any SignupOut.EServices entries where ClinicNum==0 are invalid and should be ignored.
///If HasClinics==false then SignupOut.EServices should only pay attention items where ClinicNum==0.
///This list is kept completely unfiltered by ClinicNum for forward compatibility reasons.
///The ClinicNum 0 items are always used by the Signup portal to determine default signup preferences.
///However, these items are only used for validation and billing in the case where HasClinics==true.</summary>
public static EServiceSetup.SignupOut GetEServiceSetupFull(SignupPortalPermission permission, bool isSwitchClinicPref = false)
{
//Clinics will be stored in this order at HQ to allow signup portal to display them in proper order.
List <Clinic> clinics = Clinics.GetDeepCopy().OrderBy(x => x.ItemOrder).ToList();
if (PrefC.GetBool(PrefName.ClinicListIsAlphabetical))
{
clinics = clinics.OrderBy(x => x.Abbr).ToList();
}
#if DEBUG
bool isMockChanged = false;
if (WebServiceMainHQProxy.MockWebServiceMainHQ == null)
{
WebServiceMainHQProxy.MockWebServiceMainHQ = new WebServiceMainHQMockDemo();
isMockChanged = true;
}
#endif
EServiceSetup.SignupOut signupOut = ReadXml <EServiceSetup.SignupOut>
(
WebSerializer.DeserializePrimitiveOrThrow <string>
(
GetWebServiceMainHQInstance().EServiceSetup
(
CreateWebServiceHQPayload
(
WriteXml(new EServiceSetup.SignupIn()
{
MethodNameInt = (int)EServiceSetup.SetupMethod.GetSignupOutFull,
HasClinics = PrefC.HasClinicsEnabled,
//ClinicNum is not currently used as input.
ClinicNum = 0,
ProgramVersionStr = PrefC.GetString(PrefName.ProgramVersion),
SignupPortalPermissionInt = (int)permission,
Clinics = clinics
.Select(x => new EServiceSetup.SignupIn.ClinicLiteIn()
{
ClinicNum = x.ClinicNum,
ClinicTitle = x.Abbr,
IsHidden = x.IsHidden,
}).ToList(),
IsSwitchClinicPref = isSwitchClinicPref,
}), eServiceCode.Undefined
)
)
)
);
#if DEBUG
if (isMockChanged)
{
WebServiceMainHQProxy.MockWebServiceMainHQ = null;
}
#endif
//We just got the latest sync info from HQ so update the local db to reflect what HQ says is true.
#region Reconcile Phones
List <SmsPhone> listPhonesHQ = signupOut.Phones.Select(x => new SmsPhone()
{
ClinicNum = x.ClinicNum,
CountryCode = x.CountryCode,
DateTimeActive = x.DateTimeActive,
DateTimeInactive = x.DateTimeInactive,
InactiveCode = x.InactiveCode,
PhoneNumber = x.PhoneNumber,
}).ToList();
bool isCacheInvalid = false;
isCacheInvalid |= SmsPhones.UpdateOrInsertFromList(listPhonesHQ);
#endregion
#region Reconcile practice and clinics
List <EServiceSetup.SignupOut.SignupOutSms> smsSignups = GetSignups <EServiceSetup.SignupOut.SignupOutSms>(signupOut, eServiceCode.IntegratedTexting);
bool isSmsEnabled = false;
if (PrefC.HasClinicsEnabled) //Clinics are ON so loop through all clinics and reconcile with HQ.
{
List <Clinic> listClinicsAll = Clinics.GetDeepCopy();
foreach (Clinic clinicDb in listClinicsAll)
{
WebServiceMainHQProxy.EServiceSetup.SignupOut.SignupOutSms clinicSignup =
smsSignups.FirstOrDefault(x => x.ClinicNum == clinicDb.ClinicNum) ?? new WebServiceMainHQProxy.EServiceSetup.SignupOut.SignupOutSms()
{
//Not found so turn it off.
SmsContractDate = DateTime.MinValue,
MonthlySmsLimit = 0,
IsEnabled = false,
};
Clinic clinicNew = clinicDb.Copy();
clinicNew.SmsContractDate = clinicSignup.SmsContractDate;
clinicNew.SmsMonthlyLimit = clinicSignup.MonthlySmsLimit;
isCacheInvalid |= Clinics.Update(clinicNew, clinicDb);
isSmsEnabled |= clinicSignup.IsEnabled;
}
}
else //Clinics are off so ClinicNum 0 is the practice clinic.
{
WebServiceMainHQProxy.EServiceSetup.SignupOut.SignupOutSms practiceSignup =
smsSignups.FirstOrDefault(x => x.ClinicNum == 0) ?? new WebServiceMainHQProxy.EServiceSetup.SignupOut.SignupOutSms()
{
//Not found so turn it off.
SmsContractDate = DateTime.MinValue,
MonthlySmsLimit = 0,
IsEnabled = false,
};
isCacheInvalid
|= Prefs.UpdateDateT(PrefName.SmsContractDate, practiceSignup.SmsContractDate)
| Prefs.UpdateLong(PrefName.TextingDefaultClinicNum, 0)
| Prefs.UpdateDouble(PrefName.SmsMonthlyLimit, practiceSignup.MonthlySmsLimit);
isSmsEnabled |= practiceSignup.IsEnabled;
}
#endregion
#region Reconcile CallFire
//Turn off CallFire if SMS has been activated.
//This only happens the first time SMS is turned on and CallFire is still activated.
if (isSmsEnabled && Programs.IsEnabled(ProgramName.CallFire))
{
Program callfire = Programs.GetCur(ProgramName.CallFire);
if (callfire != null)
{
callfire.Enabled = false;
Programs.Update(callfire);
Signalods.Insert(new Signalod()
{
IType = InvalidType.Providers
});
signupOut.Prompts.Add("Call Fire has been disabled. Cancel Integrated Texting and access program properties to retain Call Fire.");
}
}
#endregion
#region eConfirmations
if (Prefs.UpdateBool(PrefName.ApptConfirmAutoSignedUp, IsEServiceActive(signupOut, eServiceCode.ConfirmationRequest)))
{
//HQ does not match the local pref. Make it match with HQ.
isCacheInvalid = true;
SecurityLogs.MakeLogEntry(Permissions.Setup, 0, "Automated appointment eConfirmations automatically changed by HQ. Local pref set to "
+ IsEServiceActive(signupOut, eServiceCode.ConfirmationRequest).ToString() + ".");
}
#endregion
if (isCacheInvalid) //Something changed in the db. Alert other workstations and change this workstation immediately.
{
Signalods.Insert(new Signalod()
{
IType = InvalidType.Prefs
});
Prefs.RefreshCache();
Signalods.Insert(new Signalod()
{
IType = InvalidType.Providers
});
Providers.RefreshCache();
Clinics.RefreshCache();
Signalods.Insert(new Signalod()
{
IType = InvalidType.SmsPhones
});
SmsPhones.RefreshCache();
}
return(signupOut);
}
///<summary>If ClientWeb, then this method is instead run on the server, and the result passed back to the client. And since it's ClientWeb, FillCache will be run on the client.</summary>
public static DataSet GetCacheDs(string itypesStr)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDS(MethodBase.GetCurrentMethod(), itypesStr));
}
//so this part below only happens if direct or server------------------------------------------------
List <int> itypes = new List <int>();
string[] strArray = itypesStr.Split(',');
for (int i = 0; i < strArray.Length; i++)
{
itypes.Add(PIn.Int(strArray[i]));
}
bool isAll = false;
if (itypes.Contains((int)InvalidType.AllLocal))
{
isAll = true;
}
DataSet ds = new DataSet();
if (itypes.Contains((int)InvalidType.AccountingAutoPays) || isAll)
{
ds.Tables.Add(AccountingAutoPays.RefreshCache());
}
if (itypes.Contains((int)InvalidType.AutoCodes) || isAll)
{
ds.Tables.Add(AutoCodes.RefreshCache());
ds.Tables.Add(AutoCodeItems.RefreshCache());
ds.Tables.Add(AutoCodeConds.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Automation) || isAll)
{
ds.Tables.Add(Automations.RefreshCache());
}
if (itypes.Contains((int)InvalidType.AutoNotes) || isAll)
{
ds.Tables.Add(AutoNotes.RefreshCache());
ds.Tables.Add(AutoNoteControls.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Carriers) || isAll)
{
ds.Tables.Add(Carriers.RefreshCache()); //run on startup, after telephone reformat, after list edit.
}
if (itypes.Contains((int)InvalidType.ClaimForms) || isAll)
{
ds.Tables.Add(ClaimFormItems.RefreshCache());
ds.Tables.Add(ClaimForms.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ClearHouses) || isAll)
{
ds.Tables.Add(Clearinghouses.RefreshCache()); //kh wants to add an EasyHideClearHouses to disable this
}
if (itypes.Contains((int)InvalidType.Computers) || isAll)
{
ds.Tables.Add(Computers.RefreshCache());
ds.Tables.Add(Printers.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Defs) || isAll)
{
ds.Tables.Add(Defs.RefreshCache());
}
if (itypes.Contains((int)InvalidType.DentalSchools) || isAll)
{
ds.Tables.Add(SchoolClasses.RefreshCache());
ds.Tables.Add(SchoolCourses.RefreshCache());
}
if (itypes.Contains((int)InvalidType.DictCustoms) || isAll)
{
ds.Tables.Add(DictCustoms.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Diseases) || isAll)
{
ds.Tables.Add(DiseaseDefs.RefreshCache());
ds.Tables.Add(ICD9s.RefreshCache());
}
if (itypes.Contains((int)InvalidType.DisplayFields) || isAll)
{
ds.Tables.Add(DisplayFields.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ElectIDs) || isAll)
{
ds.Tables.Add(ElectIDs.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Email) || isAll)
{
ds.Tables.Add(EmailAddresses.RefreshCache());
ds.Tables.Add(EmailTemplates.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Employees) || isAll)
{
ds.Tables.Add(Employees.RefreshCache());
ds.Tables.Add(PayPeriods.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Employers) || isAll)
{
ds.Tables.Add(Employers.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Fees) || isAll)
{
ds.Tables.Add(Fees.RefreshCache());
}
if (itypes.Contains((int)InvalidType.FeeScheds) || isAll)
{
ds.Tables.Add(FeeScheds.RefreshCache());
}
if (itypes.Contains((int)InvalidType.HL7Defs) || isAll)
{
ds.Tables.Add(HL7Defs.RefreshCache());
ds.Tables.Add(HL7DefMessages.RefreshCache());
ds.Tables.Add(HL7DefSegments.RefreshCache());
ds.Tables.Add(HL7DefFields.RefreshCache());
}
if (itypes.Contains((int)InvalidType.InsCats) || isAll)
{
ds.Tables.Add(CovCats.RefreshCache());
ds.Tables.Add(CovSpans.RefreshCache());
}
if (itypes.Contains((int)InvalidType.InsFilingCodes) || isAll)
{
ds.Tables.Add(InsFilingCodes.RefreshCache());
ds.Tables.Add(InsFilingCodeSubtypes.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Languages) || isAll)
{
if (CultureInfo.CurrentCulture.Name != "en-US")
{
ds.Tables.Add(Lans.RefreshCache());
}
}
if (itypes.Contains((int)InvalidType.Letters) || isAll)
{
ds.Tables.Add(Letters.RefreshCache());
}
if (itypes.Contains((int)InvalidType.LetterMerge) || isAll)
{
ds.Tables.Add(LetterMergeFields.RefreshCache());
ds.Tables.Add(LetterMerges.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Operatories) || isAll)
{
ds.Tables.Add(Operatories.RefreshCache());
}
if (itypes.Contains((int)InvalidType.PatFields) || isAll)
{
ds.Tables.Add(PatFieldDefs.RefreshCache());
ds.Tables.Add(ApptFieldDefs.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Pharmacies) || isAll)
{
ds.Tables.Add(Pharmacies.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Prefs) || isAll)
{
ds.Tables.Add(Prefs.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ProcButtons) || isAll)
{
ds.Tables.Add(ProcButtons.RefreshCache());
ds.Tables.Add(ProcButtonItems.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ProcCodes) || isAll)
{
ds.Tables.Add(ProcedureCodes.RefreshCache());
ds.Tables.Add(ProcCodeNotes.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Programs) || isAll)
{
ds.Tables.Add(Programs.RefreshCache());
ds.Tables.Add(ProgramProperties.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ProviderIdents) || isAll)
{
ds.Tables.Add(ProviderIdents.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Providers) || isAll)
{
ds.Tables.Add(Providers.RefreshCache());
}
if (itypes.Contains((int)InvalidType.QuickPaste) || isAll)
{
ds.Tables.Add(QuickPasteNotes.RefreshCache());
ds.Tables.Add(QuickPasteCats.RefreshCache());
}
if (itypes.Contains((int)InvalidType.RecallTypes) || isAll)
{
ds.Tables.Add(RecallTypes.RefreshCache());
ds.Tables.Add(RecallTriggers.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ReplicationServers) || isAll)
{
ds.Tables.Add(ReplicationServers.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Security) || isAll)
{
ds.Tables.Add(Userods.RefreshCache());
ds.Tables.Add(UserGroups.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Sheets) || isAll)
{
ds.Tables.Add(SheetDefs.RefreshCache());
ds.Tables.Add(SheetFieldDefs.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Signals) || isAll)
{
ds.Tables.Add(SigElementDefs.RefreshCache());
ds.Tables.Add(SigButDefs.RefreshCache()); //includes SigButDefElements.Refresh()
}
if (itypes.Contains((int)InvalidType.Sites) || isAll)
{
ds.Tables.Add(Sites.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Sops) || isAll)
{
ds.Tables.Add(Sops.RefreshCache());
}
//InvalidTypes.Tasks not handled here.
if (itypes.Contains((int)InvalidType.TimeCardRules) || isAll)
{
ds.Tables.Add(TimeCardRules.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ToolBut) || isAll)
{
ds.Tables.Add(ToolButItems.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Vaccines) || isAll)
{
ds.Tables.Add(VaccineDefs.RefreshCache());
ds.Tables.Add(DrugManufacturers.RefreshCache());
ds.Tables.Add(DrugUnits.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Views) || isAll)
{
ds.Tables.Add(ApptViews.RefreshCache());
ds.Tables.Add(ApptViewItems.RefreshCache());
ds.Tables.Add(AppointmentRules.RefreshCache());
ds.Tables.Add(ProcApptColors.RefreshCache());
}
if (itypes.Contains((int)InvalidType.Wiki) || isAll)
{
ds.Tables.Add(WikiListHeaderWidths.RefreshCache());
ds.Tables.Add(WikiPages.RefreshCache());
}
if (itypes.Contains((int)InvalidType.ZipCodes) || isAll)
{
ds.Tables.Add(ZipCodes.RefreshCache());
}
return(ds);
}
///<summary>only if ClientWeb</summary>
public static void FillCache(DataSet ds, string itypesStr)
{
List <int> itypes = new List <int>();
string[] strArray = itypesStr.Split(',');
for (int i = 0; i < strArray.Length; i++)
{
itypes.Add(PIn.Int(strArray[i]));
}
bool isAll = false;
if (itypes.Contains((int)InvalidType.AllLocal))
{
isAll = true;
}
if (itypes.Contains((int)InvalidType.AccountingAutoPays) || isAll)
{
AccountingAutoPays.FillCache(ds.Tables["AccountingAutoPay"]);
}
if (itypes.Contains((int)InvalidType.AutoCodes) || isAll)
{
AutoCodes.FillCache(ds.Tables["AutoCode"]);
AutoCodeItems.FillCache(ds.Tables["AutoCodeItem"]);
AutoCodeConds.FillCache(ds.Tables["AutoCodeCond"]);
}
if (itypes.Contains((int)InvalidType.Automation) || isAll)
{
Automations.FillCache(ds.Tables["Automation"]);
}
if (itypes.Contains((int)InvalidType.AutoNotes) || isAll)
{
AutoNotes.FillCache(ds.Tables["AutoNote"]);
AutoNoteControls.FillCache(ds.Tables["AutoNoteControl"]);
}
if (itypes.Contains((int)InvalidType.Carriers) || isAll)
{
Carriers.FillCache(ds.Tables["Carrier"]); //run on startup, after telephone reformat, after list edit.
}
if (itypes.Contains((int)InvalidType.ClaimForms) || isAll)
{
ClaimFormItems.FillCache(ds.Tables["ClaimFormItem"]);
ClaimForms.FillCache(ds.Tables["ClaimForm"]);
}
if (itypes.Contains((int)InvalidType.ClearHouses) || isAll)
{
Clearinghouses.FillCache(ds.Tables["Clearinghouse"]); //kh wants to add an EasyHideClearHouses to disable this
}
if (itypes.Contains((int)InvalidType.Computers) || isAll)
{
Computers.FillCache(ds.Tables["Computer"]);
Printers.FillCache(ds.Tables["Printer"]);
}
if (itypes.Contains((int)InvalidType.Defs) || isAll)
{
Defs.FillCache(ds.Tables["Def"]);
}
if (itypes.Contains((int)InvalidType.DentalSchools) || isAll)
{
SchoolClasses.FillCache(ds.Tables["SchoolClass"]);
SchoolCourses.FillCache(ds.Tables["SchoolCourse"]);
}
if (itypes.Contains((int)InvalidType.DictCustoms) || isAll)
{
DictCustoms.FillCache(ds.Tables["DictCustom"]);
}
if (itypes.Contains((int)InvalidType.Diseases) || isAll)
{
DiseaseDefs.FillCache(ds.Tables["DiseaseDef"]);
ICD9s.FillCache(ds.Tables["ICD9"]);
}
if (itypes.Contains((int)InvalidType.DisplayFields) || isAll)
{
DisplayFields.FillCache(ds.Tables["DisplayField"]);
}
if (itypes.Contains((int)InvalidType.ElectIDs) || isAll)
{
ElectIDs.FillCache(ds.Tables["ElectID"]);
}
if (itypes.Contains((int)InvalidType.Email) || isAll)
{
EmailAddresses.FillCache(ds.Tables["EmailAddress"]);
EmailTemplates.FillCache(ds.Tables["EmailTemplate"]);
}
if (itypes.Contains((int)InvalidType.Employees) || isAll)
{
Employees.FillCache(ds.Tables["Employee"]);
PayPeriods.FillCache(ds.Tables["PayPeriod"]);
}
if (itypes.Contains((int)InvalidType.Employers) || isAll)
{
Employers.FillCache(ds.Tables["Employer"]);
}
if (itypes.Contains((int)InvalidType.Fees) || isAll)
{
Fees.FillCache(ds.Tables["Fee"]);
}
if (itypes.Contains((int)InvalidType.FeeScheds) || isAll)
{
FeeScheds.FillCache(ds.Tables["FeeSched"]);
}
if (itypes.Contains((int)InvalidType.HL7Defs) || isAll)
{
HL7Defs.FillCache(ds.Tables["HL7Def"]);
HL7DefMessages.FillCache(ds.Tables["HL7DefMessage"]);
HL7DefSegments.FillCache(ds.Tables["HL7DefSegment"]);
HL7DefFields.FillCache(ds.Tables["HL7DefField"]);
}
if (itypes.Contains((int)InvalidType.InsCats) || isAll)
{
CovCats.FillCache(ds.Tables["CovCat"]);
CovSpans.FillCache(ds.Tables["CovSpan"]);
}
if (itypes.Contains((int)InvalidType.InsFilingCodes) || isAll)
{
InsFilingCodes.FillCache(ds.Tables["InsFilingCode"]);
InsFilingCodeSubtypes.FillCache(ds.Tables["InsFilingCodeSubtype"]);
}
if (itypes.Contains((int)InvalidType.Languages) || isAll)
{
Lans.FillCache(ds.Tables["Language"]);
}
if (itypes.Contains((int)InvalidType.Letters) || isAll)
{
Letters.FillCache(ds.Tables["Letter"]);
}
if (itypes.Contains((int)InvalidType.LetterMerge) || isAll)
{
LetterMergeFields.FillCache(ds.Tables["LetterMergeField"]);
LetterMerges.FillCache(ds.Tables["LetterMerge"]);
}
if (itypes.Contains((int)InvalidType.Operatories) || isAll)
{
Operatories.FillCache(ds.Tables["Operatory"]);
}
if (itypes.Contains((int)InvalidType.PatFields) || isAll)
{
PatFieldDefs.FillCache(ds.Tables["PatFieldDef"]);
ApptFieldDefs.FillCache(ds.Tables["ApptFieldDef"]);
}
if (itypes.Contains((int)InvalidType.Pharmacies) || isAll)
{
Pharmacies.FillCache(ds.Tables["Pharmacy"]);
}
if (itypes.Contains((int)InvalidType.Prefs) || isAll)
{
Prefs.FillCache(ds.Tables["Pref"]);
}
if (itypes.Contains((int)InvalidType.ProcButtons) || isAll)
{
ProcButtons.FillCache(ds.Tables["ProcButton"]);
ProcButtonItems.FillCache(ds.Tables["ProcButtonItem"]);
}
if (itypes.Contains((int)InvalidType.ProcCodes) || isAll)
{
ProcedureCodes.FillCache(ds.Tables["ProcedureCode"]);
ProcCodeNotes.FillCache(ds.Tables["ProcCodeNote"]);
}
if (itypes.Contains((int)InvalidType.Programs) || isAll)
{
Programs.FillCache(ds.Tables["Program"]);
ProgramProperties.FillCache(ds.Tables["ProgramProperty"]);
}
if (itypes.Contains((int)InvalidType.ProviderIdents) || isAll)
{
ProviderIdents.FillCache(ds.Tables["ProviderIdent"]);
}
if (itypes.Contains((int)InvalidType.Providers) || isAll)
{
Providers.FillCache(ds.Tables["Provider"]);
}
if (itypes.Contains((int)InvalidType.QuickPaste) || isAll)
{
QuickPasteNotes.FillCache(ds.Tables["QuickPasteNote"]);
QuickPasteCats.FillCache(ds.Tables["QuickPasteCat"]);
}
if (itypes.Contains((int)InvalidType.RecallTypes) || isAll)
{
RecallTypes.FillCache(ds.Tables["RecallType"]);
RecallTriggers.FillCache(ds.Tables["RecallTrigger"]);
}
if (itypes.Contains((int)InvalidType.ReplicationServers) || isAll)
{
ReplicationServers.FillCache(ds.Tables["ReplicationServer"]);
}
if (itypes.Contains((int)InvalidType.Security) || isAll)
{
Userods.FillCache(ds.Tables["Userod"]);
UserGroups.FillCache(ds.Tables["UserGroup"]);
}
if (itypes.Contains((int)InvalidType.Sheets) || isAll)
{
SheetDefs.FillCache(ds.Tables["SheetDef"]);
SheetFieldDefs.FillCache(ds.Tables["SheetFieldDef"]);
}
if (itypes.Contains((int)InvalidType.Signals) || isAll)
{
SigElementDefs.FillCache(ds.Tables["SigElementDef"]);
SigButDefs.FillCache(ds.Tables["SigButDef"]); //includes SigButDefElements.Refresh()
}
if (itypes.Contains((int)InvalidType.Sites) || isAll)
{
Sites.FillCache(ds.Tables["Site"]);
}
if (itypes.Contains((int)InvalidType.Sops) || isAll)
{
Sops.FillCache(ds.Tables["Sop"]);
}
if (itypes.Contains((int)InvalidType.TimeCardRules) || isAll)
{
TimeCardRules.FillCache(ds.Tables["TimeCardRule"]);
}
//InvalidTypes.Tasks not handled here.
if (itypes.Contains((int)InvalidType.ToolBut) || isAll)
{
ToolButItems.FillCache(ds.Tables["ToolButItem"]);
}
if (itypes.Contains((int)InvalidType.Vaccines) || isAll)
{
VaccineDefs.FillCache(ds.Tables["VaccineDef"]);
DrugManufacturers.FillCache(ds.Tables["DrugManufacturer"]);
DrugUnits.FillCache(ds.Tables["DrugUnit"]);
}
if (itypes.Contains((int)InvalidType.Views) || isAll)
{
ApptViews.FillCache(ds.Tables["ApptView"]);
ApptViewItems.FillCache(ds.Tables["ApptViewItem"]);
AppointmentRules.FillCache(ds.Tables["AppointmentRule"]);
ProcApptColors.FillCache(ds.Tables["ProcApptColor"]);
}
if (itypes.Contains((int)InvalidType.Wiki) || isAll)
{
WikiListHeaderWidths.FillCache(ds.Tables["WikiListHeaderWidth"]);
WikiPages.FillCache(ds.Tables["WikiPage"]);
}
if (itypes.Contains((int)InvalidType.ZipCodes) || isAll)
{
ZipCodes.FillCache(ds.Tables["ZipCode"]);
}
}
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);
}
///<summary>Gets a color from an int32 pref.</summary>
public static Color GetColor(PrefName prefName)
{
return(Color.FromArgb(PIn.Int(Prefs.GetOne(prefName).ValueString)));
}
///<summary>Returns serialized DbInfo object as JSON string of database info from both the preference table and non preferernce table info.
///Every unique bit of information is individually try / caught so that we return as much information as possible.</summary>
private string GetDbInfoJSON(long patNum, string moduleName)
{
_info = new BugSubmission.SubmissionInfo();
ODException.SwallowAnyException(() => {
//This list is not in a separate method because we want to ensure that future development related to bug submissions don't try to make assumptions
//on which preferences are in an object at any given time.
//Ex. Let's say in version 17.4, the list doesn't contain the payplan version preference, but 17.5 does.
//If we called the method that retrieves the used preferences from WebServiceMainHQ which in this example is on version 17.5,
// it would think all bugsubmission rows contain the payplan version preference when that is not the case.
List <PrefName> listPrefs = new List <PrefName>()
{
PrefName.AtoZfolderUsed,
PrefName.ClaimSnapshotEnabled,
PrefName.ClaimSnapshotRunTime,
PrefName.ClaimSnapshotTriggerType,
PrefName.CorruptedDatabase,
PrefName.DataBaseVersion,
PrefName.EasyNoClinics,
PrefName.LanguageAndRegion,
PrefName.MySqlVersion,
PrefName.PayPlansVersion,
PrefName.ProcessSigsIntervalInSecs,
PrefName.ProgramVersionLastUpdated,
PrefName.ProgramVersion,
PrefName.RandomPrimaryKeys,
PrefName.RegistrationKey,
PrefName.RegistrationKeyIsDisabled,
PrefName.ReplicationFailureAtServer_id,
PrefName.ReportingServerCompName,
PrefName.ReportingServerDbName,
PrefName.ReportingServerMySqlUser,
PrefName.ReportingServerMySqlPassHash,
PrefName.ReportingServerURI,
PrefName.SecurityLogOffAfterMinutes,
PrefName.WebServiceServerName
};
foreach (PrefName pref in listPrefs)
{
_info.DictPrefValues[pref] = Prefs.GetOne(pref).ValueString;
}
});
ODException.SwallowAnyException(() => { _info.CountClinics = Clinics.GetCount(); });
ODException.SwallowAnyException(() => { _info.EnabledPlugins = Programs.GetWhere(x => x.Enabled && !string.IsNullOrWhiteSpace(x.PluginDllName)).Select(x => x.ProgName).ToList(); });
ODException.SwallowAnyException(() => { _info.ClinicNumCur = Clinics.ClinicNum; });
ODException.SwallowAnyException(() => { _info.UserNumCur = Security.CurUser.UserNum; });
ODException.SwallowAnyException(() => { _info.PatientNumCur = patNum; });
ODException.SwallowAnyException(() => { _info.IsOfficeOnReplication = (ReplicationServers.GetCount() > 0 ? true : false); });
ODException.SwallowAnyException(() => { _info.IsOfficeUsingMiddleTier = (RemotingClient.RemotingRole == RemotingRole.ClientWeb ? true : false); });
ODException.SwallowAnyException(() => { _info.WindowsVersion = MiscData.GetOSVersionInfo(); });
ODException.SwallowAnyException(() => { _info.CompName = Security.CurComputerName; });
ODException.SwallowAnyException(() => {
List <UpdateHistory> listHist = UpdateHistories.GetPreviousUpdateHistories(2); //Ordered by newer versions first.
_info.PreviousUpdateVersion = listHist.Count == 2 ? listHist[1].ProgramVersion : ""; //Show the previous version they updated from
_info.PreviousUpdateTime = listHist.Count > 0 ? listHist[0].DateTimeUpdated : DateTime.MinValue; //Show when they updated to the current version.
});
ODException.SwallowAnyException(() => { _info.ModuleNameCur = moduleName; });
ODException.SwallowAnyException(() => { _info.DatabaseName = DataConnection.GetDatabaseName(); });
ODException.SwallowAnyException(() => { _info.OpenDentBusinessVersion = MiscData.GetAssemblyVersion(); });
ODException.SwallowAnyException(() => { _info.OpenDentBusinessMiddleTierVersion = MiscData.GetAssemblyVersionForMiddleTier(); });
return(JsonConvert.SerializeObject(_info, new JsonSerializerSettings {
NullValueHandling = NullValueHandling.Ignore
}));
}
///<summary>Used sometimes for prefs that are not part of the enum, especially for outside developers.</summary>
public static string GetRaw(string prefName)
{
return(Prefs.GetOne(prefName).ValueString);
}
///<summary></summary>
public static DataTable GetPatUncollected(DateTime dateFrom, DateTime dateTo, List <long> listClinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listClinicNums));
}
#if DEBUG
Stopwatch s = new Stopwatch();
s.Start();
#endif
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)));
string query = "SELECT procedurelog.ProcDate,CONCAT(patient.LName,', ',patient.FName)AS Patient, procedurecode.AbbrDesc, "
+ "procedurelog.ProcFee*(procedurelog.BaseUnits+procedurelog.UnitQty)-IFNULL(cp.CapWriteOff,0) Fee,"
+ "IFNULL((procedurelog.ProcFee*(procedurelog.BaseUnits+procedurelog.UnitQty))-IFNULL(cp.CapWriteOff,0)-IFNULL(cp.InsAmt,0)"
+ "-IFNULL(cp.WriteOff,0),0) AS PatPortion,"
+ "IFNULL(adj.AdjAmt,0) AS Adjustment,"
+ "IFNULL(pay.SplitAmt,0) AS Payment,"
+ "IFNULL((procedurelog.ProcFee*(procedurelog.BaseUnits+procedurelog.UnitQty))-IFNULL(cp.CapWriteOff,0)-IFNULL(cp.InsAmt,0)-"
+ "IFNULL(cp.WriteOff,0),0)+IFNULL(adj.AdjAmt,0)-IFNULL(pay.SplitAmt,0) Uncollected "
+ "FROM procedurelog "
+ "INNER JOIN patient ON patient.PatNum=procedurelog.PatNum "
+ "INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum "
+ "LEFT JOIN ( "
+ "SELECT SUM(adjustment.AdjAmt) AdjAmt, adjustment.ProcNum "
+ "FROM adjustment "
+ "WHERE adjustment.ProcNum != 0 "
+ "AND adjustment.ProcDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " "
+ "GROUP BY ProcNum "
+ "ORDER BY NULL "
+ ")adj ON adj.ProcNum=procedurelog.ProcNum "
+ "LEFT JOIN ( "
+ "SELECT claimproc.Status, SUM(CASE WHEN claimproc.Status = " + POut.Int((int)ClaimProcStatus.NotReceived) + " THEN claimproc.InsPayEst "
+ "WHEN claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Received) + "," + POut.Int((int)ClaimProcStatus.Supplemental) + ") "
+ "THEN claimproc.InsPayAmt "
+ "ELSE 0 END) AS InsAmt, "
+ "SUM(IF(claimproc.Status!=" + POut.Int((int)ClaimProcStatus.CapComplete) + ",claimproc.WriteOff,0)) AS WriteOff, "
+ "SUM(IF(claimproc.Status=" + POut.Int((int)ClaimProcStatus.CapComplete) + ",claimproc.WriteOff,0)) AS CapWriteOff, "
+ "claimproc.ProcNum "
+ "FROM claimproc "
+ "WHERE claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.NotReceived) + ","
+ POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapComplete) + ") "
+ "AND claimproc.ProcDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " "
+ "GROUP BY claimproc.ProcNum "
+ "ORDER BY NULL "
+ ")cp ON cp.ProcNum=procedurelog.ProcNum "
+ "LEFT JOIN ( "
+ "SELECT SUM(paysplit.SplitAmt) SplitAmt, paysplit.ProcNum "
+ "FROM paysplit "
+ "INNER JOIN procedurelog "
+ "ON paysplit.ProcNum=procedurelog.ProcNum "
+ "AND procedurelog.ProcStatus=2 " //Complete
+ "AND procedurelog.ProcDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " "
+ "WHERE paysplit.ProcNum != 0 "
+ "GROUP BY paysplit.ProcNum "
+ "ORDER BY NULL "
+ ")pay ON pay.ProcNum=procedurelog.ProcNum "
+ "WHERE procedurelog.ProcStatus=2 "
+ "AND procedurelog.ProcDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " "
+ "AND IFNULL((procedurelog.ProcFee*(procedurelog.BaseUnits+procedurelog.UnitQty))-IFNULL(cp.CapWriteOff,0)-IFNULL(cp.InsAmt,0)-"
+ "IFNULL(cp.WriteOff,0),0)+IFNULL(adj.AdjAmt,0)-IFNULL(pay.SplitAmt,0) > 0.005";
if (hasClinicsEnabled && listClinicNums.Count > 0)
{
query += "AND procedurelog.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
query += "ORDER BY procedurelog.ProcDate,patient.LName,patient.FName,procedurecode.ProcCode";
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(query));
#if DEBUG
s.Stop();
Console.WriteLine("Total time to generate report with " + string.Format("{0:#,##0.##}", table.Rows.Count) + " rows: "
+ (s.Elapsed.Hours > 0?(s.Elapsed.Hours + " hours "):"") + (s.Elapsed.Minutes > 0?(s.Elapsed.Minutes + " min "):"")
+ (s.Elapsed.TotalSeconds - (s.Elapsed.Hours * 60 * 60) - (s.Elapsed.Minutes * 60)) + " sec");
#endif
return(table);
}
