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