///<summary>Used only to run finance charges, so it ignores negative balances.</summary> public static PatAging[] GetAgingListArray() { if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetObject<PatAging[]>(MethodBase.GetCurrentMethod()); } string command = "SELECT patnum,Bal_0_30,Bal_31_60,Bal_61_90,BalOver90,BalTotal,InsEst,LName,FName,MiddleI,PriProv,BillingType " +"FROM patient "//actually only gets guarantors since others are 0. +" WHERE Bal_0_30 + Bal_31_60 + Bal_61_90 + BalOver90 - InsEst > '0.005'"//more that 1/2 cent +" ORDER BY LName,FName"; DataTable table=Db.GetTable(command); PatAging[] AgingList=new PatAging[table.Rows.Count]; for(int i=0;i<table.Rows.Count;i++){ AgingList[i]=new PatAging(); AgingList[i].PatNum = PIn.Long (table.Rows[i][0].ToString()); AgingList[i].Bal_0_30 = PIn.Double(table.Rows[i][1].ToString()); AgingList[i].Bal_31_60= PIn.Double(table.Rows[i][2].ToString()); AgingList[i].Bal_61_90= PIn.Double(table.Rows[i][3].ToString()); AgingList[i].BalOver90= PIn.Double(table.Rows[i][4].ToString()); AgingList[i].BalTotal = PIn.Double(table.Rows[i][5].ToString()); AgingList[i].InsEst = PIn.Double(table.Rows[i][6].ToString()); AgingList[i].PatName=PIn.String(table.Rows[i][7].ToString()) +", "+PIn.String(table.Rows[i][8].ToString()) +" "+PIn.String(table.Rows[i][9].ToString());; //AgingList[i].Balance=AgingList[i].Bal_0_30+AgingList[i].Bal_31_60 // +AgingList[i].Bal_61_90+AgingList[i].BalOver90; AgingList[i].AmountDue=AgingList[i].BalTotal-AgingList[i].InsEst; AgingList[i].PriProv=PIn.Long(table.Rows[i][10].ToString()); AgingList[i].BillingType=PIn.Long(table.Rows[i][11].ToString()); } return AgingList; }
///<summary>This is only used in the Billing dialog</summary> public static List<PatAging> GetAgingList(string age,DateTime lastStatement,List<long> billingNums,bool excludeAddr, bool excludeNeg,double excludeLessThan,bool excludeInactive,bool includeChanged,bool excludeInsPending, bool excludeIfUnsentProcs,bool ignoreInPerson,long clinicNum) { if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetObject<List<PatAging>>(MethodBase.GetCurrentMethod(),age,lastStatement,billingNums,excludeAddr,excludeNeg,excludeLessThan,excludeInactive,includeChanged,excludeInsPending,excludeIfUnsentProcs,ignoreInPerson,clinicNum); } string command=""; if(includeChanged){ command+=@"DROP TABLE IF EXISTS templastproc; CREATE TABLE templastproc( Guarantor bigint unsigned NOT NULL, LastProc date NOT NULL, PRIMARY KEY (Guarantor)); INSERT INTO templastproc SELECT patient.Guarantor,MAX(ProcDate) FROM procedurelog,patient WHERE patient.PatNum=procedurelog.PatNum AND procedurelog.ProcStatus=2 AND procedurelog.ProcFee>0 GROUP BY patient.Guarantor; DROP TABLE IF EXISTS templastpay; CREATE TABLE templastpay( Guarantor bigint unsigned NOT NULL, LastPay date NOT NULL, PRIMARY KEY (Guarantor)); INSERT INTO templastpay SELECT patient.Guarantor,MAX(DateCP) FROM claimproc,patient WHERE claimproc.PatNum=patient.PatNum AND claimproc.InsPayAmt>0 GROUP BY patient.Guarantor;"; } if(excludeInsPending) { command+=@"DROP TABLE IF EXISTS tempclaimspending; CREATE TABLE tempclaimspending( Guarantor bigint unsigned NOT NULL, PendingClaimCount int NOT NULL, PRIMARY KEY (Guarantor)); INSERT INTO tempclaimspending SELECT patient.Guarantor,COUNT(*) FROM claim,patient WHERE claim.PatNum=patient.PatNum AND (ClaimStatus='U' OR ClaimStatus='H' OR ClaimStatus='W' OR ClaimStatus='S') AND (ClaimType='P' OR ClaimType='S' OR ClaimType='Other') GROUP BY patient.Guarantor;"; } if(excludeIfUnsentProcs) { command+=@"DROP TABLE IF EXISTS tempunsentprocs; CREATE TABLE tempunsentprocs( Guarantor bigint unsigned NOT NULL, UnsentProcCount int NOT NULL, PRIMARY KEY (Guarantor)); INSERT INTO tempunsentprocs SELECT patient.Guarantor,COUNT(*) FROM patient,procedurecode,procedurelog,claimproc WHERE claimproc.procnum=procedurelog.procnum AND patient.PatNum=procedurelog.PatNum AND procedurelog.CodeNum=procedurecode.CodeNum AND claimproc.NoBillIns=0 AND procedurelog.ProcFee>0 AND claimproc.Status=6 AND procedurelog.procstatus=2 GROUP BY patient.Guarantor;"; } command+="SELECT patient.PatNum,Bal_0_30,Bal_31_60,Bal_61_90,BalOver90,BalTotal,BillingType," +"InsEst,LName,FName,MiddleI,PayPlanDue,Preferred, " +"IFNULL(MAX(statement.DateSent),'0001-01-01') AS LastStatement "; if(includeChanged){ command+=",IFNULL(templastproc.LastProc,'0001-01-01') AS LastChange," +"IFNULL(templastpay.LastPay,'0001-01-01') AS LastPayment "; } if(excludeInsPending){ command+=",IFNULL(tempclaimspending.PendingClaimCount,'0') AS ClaimCount "; } if(excludeIfUnsentProcs) { command+=",IFNULL(tempunsentprocs.UnsentProcCount,'0') AS unsentProcCount_ "; } command+= "FROM patient "//actually only gets guarantors since others are 0. +"LEFT JOIN statement ON patient.PatNum=statement.PatNum "; if(ignoreInPerson) { command+="AND statement.Mode_ != 1 "; } if(includeChanged){ command+="LEFT JOIN templastproc ON patient.PatNum=templastproc.Guarantor " +"LEFT JOIN templastpay ON patient.PatNum=templastpay.Guarantor "; } if(excludeInsPending){ command+="LEFT JOIN tempclaimspending ON patient.PatNum=tempclaimspending.Guarantor "; } if(excludeIfUnsentProcs) { command+="LEFT JOIN tempunsentprocs ON patient.PatNum=tempunsentprocs.Guarantor "; } command+="WHERE "; if(excludeInactive){ command+="(patstatus != '2') AND "; } if(PrefC.GetBool(PrefName.BalancesDontSubtractIns)) { command+="(BalTotal"; } else { command+="(BalTotal - InsEst"; } command+=" > '"+POut.Double(excludeLessThan+.005)+"'"//add half a penny for rounding error +" OR PayPlanDue > 0"; if(!excludeNeg){ if(PrefC.GetBool(PrefName.BalancesDontSubtractIns)) { command+=" OR BalTotal < '-.005')"; } else { command+=" OR BalTotal - InsEst < '-.005')"; } } else{ command+=")"; } switch(age){ //where is age 0. Is it missing because no restriction case "30": command+=" AND (Bal_31_60 > '0' OR Bal_61_90 > '0' OR BalOver90 > '0' OR PayPlanDue > 0)"; break; case "60": command+=" AND (Bal_61_90 > '0' OR BalOver90 > '0' OR PayPlanDue > 0)"; break; case "90": command+=" AND (BalOver90 > '0' OR PayPlanDue > 0)"; break; } //if billingNums.Count==0, then we'll include all billing types for(int i=0;i<billingNums.Count;i++){ if(i==0){ command+=" AND (billingtype = '"; } else{ command+=" OR billingtype = '"; } command+=POut.Long(billingNums[i])+"'"; //DefC.Short[(int)DefCat.BillingTypes][billingIndices[i]].DefNum.ToString()+"'"; if(i==billingNums.Count-1){ command+=")"; } } if(excludeAddr){ command+=" AND (zip !='')"; } if(clinicNum>0) { command+=" AND patient.ClinicNum="+clinicNum+" "; } command+=" GROUP BY patient.PatNum,Bal_0_30,Bal_31_60,Bal_61_90,BalOver90,BalTotal,BillingType," +"InsEst,LName,FName,MiddleI,PayPlanDue,Preferred " +"HAVING (LastStatement < "+POut.Date(lastStatement.AddDays(1))+" ";//<midnight of lastStatement date //+"OR PayPlanDue>0 ";we don't have a great way to trigger due to a payplancharge yet if(includeChanged){ command+= "OR LastChange > LastStatement "//eg '2005-10-25' > '2005-10-24 15:00:00' +"OR LastPayment > LastStatement) "; } else{ command+=") "; } if(excludeInsPending){ command+="AND ClaimCount=0 "; } if(excludeIfUnsentProcs) { command+="AND unsentProcCount_=0 "; } command+="ORDER BY LName,FName"; //Debug.WriteLine(command); DataTable table=Db.GetTable(command); List<PatAging> agingList=new List<PatAging>(); PatAging patage; Patient pat; for(int i=0;i<table.Rows.Count;i++){ patage=new PatAging(); patage.PatNum = PIn.Long (table.Rows[i]["PatNum"].ToString()); patage.Bal_0_30 = PIn.Double(table.Rows[i]["Bal_0_30"].ToString()); patage.Bal_31_60= PIn.Double(table.Rows[i]["Bal_31_60"].ToString()); patage.Bal_61_90= PIn.Double(table.Rows[i]["Bal_61_90"].ToString()); patage.BalOver90= PIn.Double(table.Rows[i]["BalOver90"].ToString()); patage.BalTotal = PIn.Double(table.Rows[i]["BalTotal"].ToString()); patage.InsEst = PIn.Double(table.Rows[i]["InsEst"].ToString()); pat=new Patient(); pat.LName=PIn.String(table.Rows[i]["LName"].ToString()); pat.FName=PIn.String(table.Rows[i]["FName"].ToString()); pat.MiddleI=PIn.String(table.Rows[i]["MiddleI"].ToString()); pat.Preferred=PIn.String(table.Rows[i]["Preferred"].ToString()); patage.PatName=pat.GetNameLF(); patage.AmountDue=patage.BalTotal-patage.InsEst; patage.DateLastStatement=PIn.Date(table.Rows[i]["LastStatement"].ToString()); patage.BillingType=PIn.Long(table.Rows[i]["BillingType"].ToString()); patage.PayPlanDue =PIn.Double(table.Rows[i]["PayPlanDue"].ToString()); //if(excludeInsPending && patage.InsEst>0){ //don't add //} //else{ agingList.Add(patage); //} } //PatAging[] retVal=new PatAging[agingList.Count]; //for(int i=0;i<retVal.Length;i++){ // retVal[i]=agingList[i]; //} if(includeChanged){ command="DROP TABLE IF EXISTS templastproc"; Db.NonQ(command); command="DROP TABLE IF EXISTS templastpay"; Db.NonQ(command); } if(excludeInsPending){ command="DROP TABLE IF EXISTS tempclaimspending"; Db.NonQ(command); } if(excludeIfUnsentProcs){ command="DROP TABLE IF EXISTS tempunsentprocs"; Db.NonQ(command); } return agingList; }