///<summary>Updates a pref of type double. Returns true if a change was required, or false if no change needed.
///Set doRounding false when the double passed in needs to be Multiple Precision Floating-Point Reliable (MPFR).</summary>
public static bool UpdateDouble(PrefName prefName, double newValue, bool doRounding = true)
{
//Very unusual. Involves cache, so Meth is used further down instead of here at the top.
double curValue = PrefC.GetDouble(prefName);
if (curValue == newValue)
{
return(false); //no change needed
}
string command = "UPDATE preference SET "
+ "ValueString = '" + ((doRounding) ? POut.Double(newValue) : POut.Double(newValue, false)) + "' "
+ "WHERE PrefName = '" + POut.String(prefName.ToString()) + "'";
bool retVal = true;
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
retVal = Meth.GetBool(MethodBase.GetCurrentMethod(), prefName, newValue, doRounding);
}
else
{
Db.NonQ(command);
}
Pref pref = new Pref();
pref.PrefName = prefName.ToString();
pref.ValueString = newValue.ToString();
Prefs.UpdateValueForKey(pref);
return(retVal);
}
///<summary>Returns current clinic limit minus message usage for current calendar month.</summary>
public static double GetClinicBalance(long clinicNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDouble(MethodBase.GetCurrentMethod(), clinicNum));
}
double limit = 0;
if (!PrefC.HasClinicsEnabled)
{
if (PrefC.GetDate(PrefName.SmsContractDate).Year > 1880)
{
limit = PrefC.GetDouble(PrefName.SmsMonthlyLimit);
}
}
else
{
if (clinicNum == 0 && Clinics.GetCount(true) > 0) //Sending text for "Unassigned" patient. Use the first non-hidden clinic. (for now)
{
clinicNum = Clinics.GetFirst(true).ClinicNum;
}
Clinic clinicCur = Clinics.GetClinic(clinicNum);
if (clinicCur != null && clinicCur.SmsContractDate.Year > 1880)
{
limit = clinicCur.SmsMonthlyLimit;
}
}
DateTime dtStart = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
DateTime dtEnd = dtStart.AddMonths(1);
string command = "SELECT SUM(MsgChargeUSD) FROM smstomobile WHERE ClinicNum=" + POut.Long(clinicNum) + " "
+ "AND DateTimeSent>=" + POut.Date(dtStart) + " AND DateTimeSent<" + POut.Date(dtEnd);
limit -= PIn.Double(Db.GetScalar(command));
return(limit);
}
///<summary>Updates a pref of type double. Returns true if a change was required, or false if no change needed.</summary>
public static bool UpdateDouble(PrefName prefName, double newValue)
{
//Very unusual. Involves cache, so Meth is used further down instead of here at the top.
if (!PrefC.Dict.ContainsKey(prefName.ToString()))
{
throw new ApplicationException(prefName + " is an invalid pref name.");
}
if (PrefC.GetDouble(prefName) == newValue)
{
return(false); //no change needed
}
string command = "UPDATE preference SET "
+ "ValueString = '" + POut.Double(newValue) + "' "
+ "WHERE PrefName = '" + POut.String(prefName.ToString()) + "'";
bool retVal = true;
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
retVal = Meth.GetBool(MethodBase.GetCurrentMethod(), prefName, newValue);
}
else
{
Db.NonQ(command);
}
return(retVal);
}
///<summary>Returns a clinic object with ClinicNum=0, and values filled using practice level preferences.
/// Caution: do not attempt to save the clinic back to the DB. This should be used for read only purposes.</summary>
public static Clinic GetPracticeAsClinicZero(string clinicName = null)
{
//No need to check RemotingRole; no call to db.
if (clinicName == null)
{
clinicName = PrefC.GetString(PrefName.PracticeTitle);
}
return(new Clinic {
ClinicNum = 0,
Abbr = clinicName,
Description = clinicName,
Address = PrefC.GetString(PrefName.PracticeAddress),
Address2 = PrefC.GetString(PrefName.PracticeAddress2),
City = PrefC.GetString(PrefName.PracticeCity),
State = PrefC.GetString(PrefName.PracticeST),
Zip = PrefC.GetString(PrefName.PracticeZip),
BillingAddress = PrefC.GetString(PrefName.PracticeBillingAddress),
BillingAddress2 = PrefC.GetString(PrefName.PracticeBillingAddress2),
BillingCity = PrefC.GetString(PrefName.PracticeBillingCity),
BillingState = PrefC.GetString(PrefName.PracticeBillingST),
BillingZip = PrefC.GetString(PrefName.PracticeBillingZip),
PayToAddress = PrefC.GetString(PrefName.PracticePayToAddress),
PayToAddress2 = PrefC.GetString(PrefName.PracticePayToAddress2),
PayToCity = PrefC.GetString(PrefName.PracticePayToCity),
PayToState = PrefC.GetString(PrefName.PracticePayToST),
PayToZip = PrefC.GetString(PrefName.PracticePayToZip),
Phone = PrefC.GetString(PrefName.PracticePhone),
BankNumber = PrefC.GetString(PrefName.PracticeBankNumber),
DefaultPlaceService = (PlaceOfService)PrefC.GetInt(PrefName.DefaultProcedurePlaceService),
InsBillingProv = PrefC.GetLong(PrefName.InsBillingProv),
Fax = PrefC.GetString(PrefName.PracticeFax),
EmailAddressNum = PrefC.GetLong(PrefName.EmailDefaultAddressNum),
DefaultProv = PrefC.GetLong(PrefName.PracticeDefaultProv),
SmsContractDate = PrefC.GetDate(PrefName.SmsContractDate),
SmsMonthlyLimit = PrefC.GetDouble(PrefName.SmsMonthlyLimit),
IsMedicalOnly = PrefC.GetBool(PrefName.PracticeIsMedicalOnly)
});
}
///<summary>Returns a SerializableDictionary with key=PatNum, value=PatAgingData with the filters applied.</summary>
public static SerializableDictionary <long, PatAgingData> GetAgingData(bool isSinglePatient, bool includeChanged, bool excludeInsPending,
bool excludeIfUnsentProcs, bool isSuperBills, List <long> listClinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <SerializableDictionary <long, PatAgingData> >(MethodBase.GetCurrentMethod(), isSinglePatient, includeChanged,
excludeInsPending, excludeIfUnsentProcs, isSuperBills, listClinicNums));
}
SerializableDictionary <long, PatAgingData> dictPatAgingData = new SerializableDictionary <long, PatAgingData>();
string command = "";
string guarOrPat = "guar";
if (isSinglePatient)
{
guarOrPat = "patient";
}
string whereAndClinNum = "";
if (!listClinicNums.IsNullOrEmpty())
{
whereAndClinNum = $@"AND {guarOrPat}.ClinicNum IN ({string.Join(",",listClinicNums)})";
}
if (includeChanged || excludeIfUnsentProcs)
{
command = $@"SELECT {guarOrPat}.PatNum,{guarOrPat}.ClinicNum,MAX(procedurelog.ProcDate) MaxProcDate";
if (excludeIfUnsentProcs)
{
command += ",MAX(CASE WHEN insplan.IsMedical=1 THEN 0 ELSE COALESCE(claimproc.ProcNum,0) END)>0 HasUnsentProcs";
}
command += $@" FROM patient
INNER JOIN patient guar ON guar.PatNum=patient.Guarantor
INNER JOIN procedurelog ON procedurelog.PatNum = patient.PatNum " ;
if (excludeIfUnsentProcs)
{
command += $@"LEFT JOIN claimproc ON claimproc.ProcNum = procedurelog.ProcNum
AND claimproc.NoBillIns=0
AND claimproc.Status = {POut.Int((int)ClaimProcStatus.Estimate)}
AND procedurelog.ProcDate > CURDATE()-INTERVAL 6 MONTH
LEFT JOIN insplan ON insplan.PlanNum=claimproc.PlanNum " ;
}
command += $@"WHERE procedurelog.ProcFee > 0
AND procedurelog.ProcStatus = {POut.Int((int)ProcStat.C)}
{whereAndClinNum}
GROUP BY {guarOrPat}.PatNum
ORDER BY NULL" ;
using (DataTable tableChangedAndUnsent = Db.GetTable(command)) {
foreach (DataRow row in tableChangedAndUnsent.Rows)
{
long patNum = PIn.Long(row["PatNum"].ToString());
if (!dictPatAgingData.ContainsKey(patNum))
{
dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString()));
}
if (includeChanged)
{
dictPatAgingData[patNum].ListPatAgingTransactions
.Add(new PatAgingTransaction(PatAgingTransaction.TransactionTypes.Procedure, PIn.Date(row["MaxProcDate"].ToString())));
}
if (excludeIfUnsentProcs)
{
dictPatAgingData[patNum].HasUnsentProcs = PIn.Bool(row["HasUnsentProcs"].ToString());
}
}
}
}
if (includeChanged)
{
command = $@"SELECT {guarOrPat}.PatNum,{guarOrPat}.ClinicNum,MAX(claimproc.DateCP) maxDateCP
FROM claimproc
INNER JOIN patient ON patient.PatNum = claimproc.PatNum
INNER JOIN patient guar ON guar.PatNum=patient.Guarantor
WHERE claimproc.InsPayAmt > 0
{whereAndClinNum}
GROUP BY {guarOrPat}.PatNum" ;
using (DataTable tableMaxPayDate = Db.GetTable(command)) {
foreach (DataRow row in tableMaxPayDate.Rows)
{
long patNum = PIn.Long(row["PatNum"].ToString());
if (!dictPatAgingData.ContainsKey(patNum))
{
dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString()));
}
dictPatAgingData[patNum].ListPatAgingTransactions
.Add(new PatAgingTransaction(PatAgingTransaction.TransactionTypes.ClaimProc, PIn.Date(row["maxDateCP"].ToString())));
}
}
command = $@"SELECT {guarOrPat}.PatNum,{guarOrPat}.ClinicNum,MAX(payplancharge.ChargeDate) maxDatePPC,
MAX(payplancharge.SecDateTEntry) maxDatePPCSDTE
FROM payplancharge
INNER JOIN patient ON patient.PatNum = payplancharge.PatNum
INNER JOIN patient guar ON guar.PatNum=patient.Guarantor
INNER JOIN payplan ON payplan.PayPlanNum = payplancharge.PayPlanNum
AND payplan.PlanNum = 0 " //don't want insurance payment plans to make patients appear in the billing list
+ $@"WHERE payplancharge.Principal + payplancharge.Interest>0
AND payplancharge.ChargeType = {(int)PayPlanChargeType.Debit} "
//include all charges in the past or due 'PayPlanBillInAdvance' days into the future.
+ $@"AND payplancharge.ChargeDate <= {POut.Date(DateTime.Today.AddDays(PrefC.GetDouble(PrefName.PayPlansBillInAdvanceDays)))}
{whereAndClinNum}
GROUP BY {guarOrPat}.PatNum" ;
using (DataTable tableMaxPPCDate = Db.GetTable(command)) {
foreach (DataRow row in tableMaxPPCDate.Rows)
{
long patNum = PIn.Long(row["PatNum"].ToString());
if (!dictPatAgingData.ContainsKey(patNum))
{
dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString()));
}
dictPatAgingData[patNum].ListPatAgingTransactions
.Add(new PatAgingTransaction(
PatAgingTransaction.TransactionTypes.PayPlanCharge,
PIn.Date(row["maxDatePPC"].ToString()),
secDateTEntryTrans: PIn.Date(row["maxDatePPCSDTE"].ToString()))
);
}
}
}
if (excludeInsPending)
{
command = $@"SELECT {guarOrPat}.PatNum,{guarOrPat}.ClinicNum
FROM claim
INNER JOIN patient ON patient.PatNum=claim.PatNum
INNER JOIN patient guar ON guar.PatNum=patient.Guarantor
WHERE claim.ClaimStatus IN ('U','H','W','S')
AND claim.ClaimType IN ('P','S','Other')
{whereAndClinNum}
GROUP BY {guarOrPat}.PatNum" ;
using (DataTable tableInsPending = Db.GetTable(command)) {
foreach (DataRow row in tableInsPending.Rows)
{
long patNum = PIn.Long(row["PatNum"].ToString());
if (!dictPatAgingData.ContainsKey(patNum))
{
dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString()));
}
dictPatAgingData[patNum].HasPendingIns = true;
}
}
}
DateTime dateAsOf = DateTime.Today; //used to determine when the balance on this date began
if (PrefC.GetBool(PrefName.AgingCalculatedMonthlyInsteadOfDaily)) //if aging calculated monthly, use the last aging date instead of today
{
dateAsOf = PrefC.GetDate(PrefName.DateLastAging);
}
List <PatComm> listPatComms = new List <PatComm>();
using (DataTable tableDateBalsBegan = Ledgers.GetDateBalanceBegan(null, dateAsOf, isSuperBills, listClinicNums)) {
foreach (DataRow row in tableDateBalsBegan.Rows)
{
long patNum = PIn.Long(row["PatNum"].ToString());
if (!dictPatAgingData.ContainsKey(patNum))
{
dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString()));
}
dictPatAgingData[patNum].DateBalBegan = PIn.Date(row["DateAccountAge"].ToString());
dictPatAgingData[patNum].DateBalZero = PIn.Date(row["DateZeroBal"].ToString());
}
listPatComms = Patients.GetPatComms(tableDateBalsBegan.Select().Select(x => PIn.Long(x["PatNum"].ToString())).ToList(), null);
}
foreach (PatComm pComm in listPatComms)
{
if (!dictPatAgingData.ContainsKey(pComm.PatNum))
{
dictPatAgingData[pComm.PatNum] = new PatAgingData(pComm.ClinicNum);
}
dictPatAgingData[pComm.PatNum].PatComm = pComm;
}
return(dictPatAgingData);
}
