///<summary></summary>
public PatAgingData Copy()
{
PatAgingData patAgingData = (PatAgingData)this.MemberwiseClone();
patAgingData.ListPatAgingTransactions = this.ListPatAgingTransactions.Select(x => x.Copy()).ToList();
return(patAgingData);
}
///<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);
}
