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