Ejemplo n.º 1
0
 ///<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;
 }
Ejemplo n.º 2
0
        ///<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;
        }