/// <summary>Gets info directly from database. Used from PayPlan and Account windows to get the amount paid so far on one payment plan.</summary>
public static double GetAmtPaid(PayPlan payPlan)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <double>(MethodBase.GetCurrentMethod(), payPlan));
}
string command;
if (payPlan.PlanNum == 0) //Patient payment plan
{
command = "SELECT SUM(paysplit.SplitAmt) FROM paysplit "
+ "WHERE paysplit.PayPlanNum = " + POut.Long(payPlan.PayPlanNum) + " "
+ "GROUP BY paysplit.PayPlanNum";
}
else //Insurance payment plan
{
command = "SELECT SUM(claimproc.InsPayAmt) "
+ "FROM claimproc "
+ "WHERE claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Received) + "," + POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapClaim) + ") "
+ "AND claimproc.PayPlanNum=" + POut.Long(payPlan.PayPlanNum);
}
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return(0);
}
return(PIn.Double(table.Rows[0][0].ToString()));
}
///<summary></summary>
public static void Update(PayPlan payPlan) {
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
Meth.GetVoid(MethodBase.GetCurrentMethod(),payPlan);
return;
}
Crud.PayPlanCrud.Update(payPlan);
}
///<summary></summary>
public static long Insert(PayPlan payPlan){
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb){
payPlan.PayPlanNum=Meth.GetLong(MethodBase.GetCurrentMethod(),payPlan);
return payPlan.PayPlanNum;
}
return Crud.PayPlanCrud.Insert(payPlan);
}
///<summary></summary>
public static void Update(PayPlan payPlan)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), payPlan);
return;
}
Crud.PayPlanCrud.Update(payPlan);
}
///<summary></summary>
public static long Insert(PayPlan payPlan)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
payPlan.PayPlanNum = Meth.GetLong(MethodBase.GetCurrentMethod(), payPlan);
return(payPlan.PayPlanNum);
}
return(Crud.PayPlanCrud.Insert(payPlan));
}
///<summary>Gets all payplan charges for the payplans passed in where the specified patient is the Guarantor. Based on today's date.
///Will return both credits and debits. Does not return insurance payment plan charges.</summary>
public static List <PayPlanCharge> GetDueForPayPlan(PayPlan payPlan, long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <PayPlanCharge> >(MethodBase.GetCurrentMethod(), payPlan, patNum));
}
return(GetDueForPayPlans(new List <PayPlan> {
payPlan
}, patNum));
}
///<summary></summary>
public PayPlan Copy()
{
PayPlan p = new PayPlan();
p.PayPlanNum = PayPlanNum;
p.PatNum = PatNum;
p.Guarantor = Guarantor;
p.PayPlanDate = PayPlanDate;
p.APR = APR;
p.Note = Note;
p.PlanNum = PlanNum;
return(p);
}
///<summary>Returns the sum of all payment plan entries for guarantor and/or patient.</summary>
public static double ComputeBal(long patNum,PayPlan[] list,List<PayPlanCharge> chargeList)
{
//No need to check RemotingRole; no call to db.
double retVal=0;
for(int i=0;i<list.Length;i++){
//one or both of these conditions may be met:
if(list[i].Guarantor==patNum){
retVal+=GetAccumDue(list[i].PayPlanNum,chargeList);
}
if(list[i].PatNum==patNum){
retVal-=GetTotalPrinc(list[i].PayPlanNum,chargeList);
}
}
return retVal;
}
///<summary>Called from FormPayPlan. Also deletes all attached payplancharges. Throws exception if there are any paysplits attached.</summary>
public static void Delete(PayPlan plan){
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
Meth.GetVoid(MethodBase.GetCurrentMethod(),plan);
return;
}
string command="SELECT COUNT(*) FROM paysplit WHERE PayPlanNum="+plan.PayPlanNum.ToString();
if(Db.GetCount(command)!="0"){
throw new ApplicationException
(Lans.g("PayPlans","You cannot delete a payment plan with payments attached. Unattach the payments first."));
}
command="DELETE FROM payplancharge WHERE PayPlanNum="+plan.PayPlanNum.ToString();
Db.NonQ(command);
command="DELETE FROM payplan WHERE PayPlanNum ="+plan.PayPlanNum.ToString();
Db.NonQ(command);
}
///<summary>Called from FormPayPlan. Also deletes all attached payplancharges. Throws exception if there are any paysplits attached.</summary>
public static void Delete(PayPlan plan)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), plan);
return;
}
string command;
if (plan.PlanNum == 0 || plan.IsDynamic) //Patient payment plan
{
command = "SELECT COUNT(*) FROM paysplit WHERE PayPlanNum=" + POut.Long(plan.PayPlanNum);
if (Db.GetCount(command) != "0")
{
throw new ApplicationException
(Lans.g("PayPlans", "You cannot delete a payment plan with patient payments attached. Unattach the payments first."));
}
}
else //Insurance payment plan
{
command = "SELECT COUNT(*) FROM claimproc WHERE PayPlanNum=" + POut.Long(plan.PayPlanNum) + " AND claimproc.Status IN ("
+ POut.Int((int)ClaimProcStatus.Received) + "," + POut.Int((int)ClaimProcStatus.Supplemental) + ")";
if (Db.GetCount(command) != "0")
{
throw new ApplicationException
(Lans.g("PayPlans", "You cannot delete a payment plan with insurance payments attached. Unattach the payments first."));
}
//if there are any unreceived items, detach them here, then proceed deleting
List <ClaimProc> listClaimProcs = ClaimProcs.GetForPayPlans(new List <long> {
plan.PayPlanNum
});
foreach (ClaimProc claimProc in listClaimProcs)
{
claimProc.PayPlanNum = 0;
ClaimProcs.Update(claimProc);
}
}
command = "DELETE FROM payplancharge WHERE PayPlanNum=" + POut.Long(plan.PayPlanNum);
Db.NonQ(command);
command = $"DELETE FROM payplanlink WHERE PayPlanNum={POut.Long(plan.PayPlanNum)}";
Db.NonQ(command);
command = "DELETE FROM payplan WHERE PayPlanNum =" + POut.Long(plan.PayPlanNum);
Db.NonQ(command);
}
///<summary>Called from FormPayPlan. Also deletes all attached payplancharges. Throws exception if there are any paysplits attached.</summary>
public static void Delete(PayPlan plan)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), plan);
return;
}
string command = "SELECT COUNT(*) FROM paysplit WHERE PayPlanNum=" + plan.PayPlanNum.ToString();
if (Db.GetCount(command) != "0")
{
throw new ApplicationException
(Lans.g("PayPlans", "You cannot delete a payment plan with payments attached. Unattach the payments first."));
}
command = "DELETE FROM payplancharge WHERE PayPlanNum=" + plan.PayPlanNum.ToString();
Db.NonQ(command);
command = "DELETE FROM payplan WHERE PayPlanNum =" + plan.PayPlanNum.ToString();
Db.NonQ(command);
}
///<summary>Get all payment plans for this patient with the insurance plan identified by PlanNum and InsSubNum attached (marked used for tracking expected insurance payments) that have not been paid in full. Only returns plans with no claimprocs currently attached or claimprocs from the claim identified by the claimNum sent in attached. If claimNum is 0 all payment plans with planNum, insSubNum, and patNum not paid in full will be returned.</summary>
public static List <PayPlan> GetValidInsPayPlans(long patNum, long planNum, long insSubNum, long claimNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <PayPlan> >(MethodBase.GetCurrentMethod(), patNum, planNum, insSubNum, claimNum));
}
string command = "";
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += "SELECT payplan.*,MAX(claimproc.ClaimNum) ClaimNum";
}
else
{
command += "SELECT payplan.PayPlanNum,payplan.PatNum,payplan.Guarantor,payplan.PayPlanDate,"
+ "payplan.APR,payplan.Note,payplan.PlanNum,payplan.CompletedAmt,payplan.InsSubNum,MAX(claimproc.ClaimNum) ClaimNum";
}
command += " FROM payplan"
+ " LEFT JOIN claimproc ON claimproc.PayPlanNum=payplan.PayPlanNum"
+ " WHERE payplan.PatNum=" + POut.Long(patNum)
+ " AND payplan.PlanNum=" + POut.Long(planNum)
+ " AND payplan.InsSubNum=" + POut.Long(insSubNum);
if (claimNum > 0)
{
command += " AND (claimproc.ClaimNum IS NULL OR claimproc.ClaimNum=" + POut.Long(claimNum) + ")"; //payplans with no claimprocs attached or only claimprocs from the same claim
}
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += " GROUP BY payplan.PayPlanNum";
}
else
{
command += " GROUP BY payplan.PayPlanNum,payplan.PatNum,payplan.Guarantor,payplan.PayPlanDate,"
+ "payplan.APR,payplan.Note,payplan.PlanNum,payplan.CompletedAmt,payplan.InsSubNum";
}
command += " HAVING payplan.CompletedAmt>SUM(COALESCE(claimproc.InsPayAmt,0))"; //has not been paid in full yet
if (claimNum == 0) //if current claimproc is not attached to a claim, do not return payplans with claimprocs from existing claims already attached
{
command += " AND (MAX(claimproc.ClaimNum) IS NULL OR MAX(claimproc.ClaimNum)=0)";
}
command += " ORDER BY payplan.PayPlanDate";
DataTable payPlansWithClaimNum = Db.GetTable(command);
List <PayPlan> retval = new List <PayPlan>();
for (int i = 0; i < payPlansWithClaimNum.Rows.Count; i++)
{
PayPlan planCur = new PayPlan();
planCur.PayPlanNum = PIn.Long(payPlansWithClaimNum.Rows[i]["PayPlanNum"].ToString());
planCur.PatNum = PIn.Long(payPlansWithClaimNum.Rows[i]["PatNum"].ToString());
planCur.Guarantor = PIn.Long(payPlansWithClaimNum.Rows[i]["Guarantor"].ToString());
planCur.PayPlanDate = PIn.Date(payPlansWithClaimNum.Rows[i]["PayPlanDate"].ToString());
planCur.APR = PIn.Double(payPlansWithClaimNum.Rows[i]["APR"].ToString());
planCur.Note = payPlansWithClaimNum.Rows[i]["Note"].ToString();
planCur.PlanNum = PIn.Long(payPlansWithClaimNum.Rows[i]["PlanNum"].ToString());
planCur.CompletedAmt = PIn.Double(payPlansWithClaimNum.Rows[i]["CompletedAmt"].ToString());
planCur.InsSubNum = PIn.Long(payPlansWithClaimNum.Rows[i]["InsSubNum"].ToString());
if (claimNum > 0 && payPlansWithClaimNum.Rows[i]["ClaimNum"].ToString() == claimNum.ToString())
{
//if a payplan exists with claimprocs from the same claim as the current claimproc attached, always only return that one payplan
//claimprocs from one claim are not allowed to be attached to different payplans
retval.Clear();
retval.Add(planCur);
break;
}
retval.Add(planCur);
}
return(retval);
}
///<summary></summary>
public static List <PayPlanCharge> GetForDownPayment(PayPlanTerms terms, Family family, List <PayPlanLink> listPayPlanLinks, PayPlan payplan)
{
//No remoting role check; no call to db
//Create a temporary variable to keep track of the original PeriodPayment.
decimal periodPaymentTemp = terms.PeriodPayment;
double aprTemp = terms.APR;
//Set the PeriodPayment to the current DownPayment so that the full amount of the down payment gets generated.
//E.g. there are several procedures attached to the payment plan and the down payment only covers one and a half (partial proc).
terms.PeriodPayment = (decimal)terms.DownPayment;
terms.APR = 0;//downpayments should pay on principal only
List <PayPlanCharge> listDownPayments = PayPlanEdit.GetListExpectedCharges(new List <PayPlanCharge>(), terms, family, listPayPlanLinks, payplan, true
, true, new List <PaySplit>());
listDownPayments.ForEach(x => {
x.Note = "Down Payment";
x.ChargeDate = DateTime.Today.Date;
x.Interest = 0;
});
//Put the PeriodPayment back to the way it was upon entry.
terms.PeriodPayment = periodPaymentTemp;
terms.APR = aprTemp;
return(listDownPayments);
}
///<summary>Get all payment plans for this patient with the insurance plan identified by PlanNum and InsSubNum attached (marked used for tracking expected insurance payments) that have not been paid in full. Only returns plans with no claimprocs currently attached or claimprocs from the claim identified by the claimNum sent in attached. If claimNum is 0 all payment plans with planNum, insSubNum, and patNum not paid in full will be returned.</summary>
public static List<PayPlan> GetValidInsPayPlans(long patNum,long planNum,long insSubNum,long claimNum) {
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetObject<List<PayPlan>>(MethodBase.GetCurrentMethod(),patNum,planNum,insSubNum,claimNum);
}
string command="";
if(DataConnection.DBtype==DatabaseType.MySql) {
command+="SELECT payplan.*,MAX(claimproc.ClaimNum) ClaimNum";
}
else {
command+="SELECT payplan.PayPlanNum,payplan.PatNum,payplan.Guarantor,payplan.PayPlanDate,"
+"payplan.APR,payplan.Note,payplan.PlanNum,payplan.CompletedAmt,payplan.InsSubNum,MAX(claimproc.ClaimNum) ClaimNum";
}
command+=" FROM payplan"
+" LEFT JOIN claimproc ON claimproc.PayPlanNum=payplan.PayPlanNum"
+" WHERE payplan.PatNum="+POut.Long(patNum)
+" AND payplan.PlanNum="+POut.Long(planNum)
+" AND payplan.InsSubNum="+POut.Long(insSubNum);
if(claimNum>0) {
command+=" AND (claimproc.ClaimNum IS NULL OR claimproc.ClaimNum="+POut.Long(claimNum)+")";//payplans with no claimprocs attached or only claimprocs from the same claim
}
if(DataConnection.DBtype==DatabaseType.MySql) {
command+=" GROUP BY payplan.PayPlanNum";
}
else {
command+=" GROUP BY payplan.PayPlanNum,payplan.PatNum,payplan.Guarantor,payplan.PayPlanDate,"
+"payplan.APR,payplan.Note,payplan.PlanNum,payplan.CompletedAmt,payplan.InsSubNum";
}
command+=" HAVING payplan.CompletedAmt>SUM(COALESCE(claimproc.InsPayAmt,0))";//has not been paid in full yet
if(claimNum==0) {//if current claimproc is not attached to a claim, do not return payplans with claimprocs from existing claims already attached
command+=" AND (MAX(claimproc.ClaimNum) IS NULL OR MAX(claimproc.ClaimNum)=0)";
}
command+=" ORDER BY payplan.PayPlanDate";
DataTable payPlansWithClaimNum=Db.GetTable(command);
List<PayPlan> retval=new List<PayPlan>();
for(int i=0;i<payPlansWithClaimNum.Rows.Count;i++) {
PayPlan planCur=new PayPlan();
planCur.PayPlanNum=PIn.Long(payPlansWithClaimNum.Rows[i]["PayPlanNum"].ToString());
planCur.PatNum=PIn.Long(payPlansWithClaimNum.Rows[i]["PatNum"].ToString());
planCur.Guarantor=PIn.Long(payPlansWithClaimNum.Rows[i]["Guarantor"].ToString());
planCur.PayPlanDate=PIn.Date(payPlansWithClaimNum.Rows[i]["PayPlanDate"].ToString());
planCur.APR=PIn.Double(payPlansWithClaimNum.Rows[i]["APR"].ToString());
planCur.Note=payPlansWithClaimNum.Rows[i]["Note"].ToString();
planCur.PlanNum=PIn.Long(payPlansWithClaimNum.Rows[i]["PlanNum"].ToString());
planCur.CompletedAmt=PIn.Double(payPlansWithClaimNum.Rows[i]["CompletedAmt"].ToString());
planCur.InsSubNum=PIn.Long(payPlansWithClaimNum.Rows[i]["InsSubNum"].ToString());
if(claimNum>0 && payPlansWithClaimNum.Rows[i]["ClaimNum"].ToString()==claimNum.ToString()) {
//if a payplan exists with claimprocs from the same claim as the current claimproc attached, always only return that one payplan
//claimprocs from one claim are not allowed to be attached to different payplans
retval.Clear();
retval.Add(planCur);
break;
}
retval.Add(planCur);
}
return retval;
}
private void toolBarButPayPlan_Click() {
PayPlan payPlan=new PayPlan();
payPlan.PatNum=PatCur.PatNum;
payPlan.Guarantor=PatCur.Guarantor;
payPlan.PayPlanDate=DateTimeOD.Today;
payPlan.CompletedAmt=PatCur.EstBalance;
PayPlans.Insert(payPlan);
FormPayPlan FormPP=new FormPayPlan(PatCur,payPlan);
FormPP.TotalAmt=PatCur.EstBalance;
FormPP.IsNew=true;
FormPP.ShowDialog();
if(FormPP.GotoPatNum!=0){
ModuleSelected(FormPP.GotoPatNum);//switches to other patient.
}
else{
ModuleSelected(PatCur.PatNum);
}
}
///<summary>The supplied payment plan should already have been saved in the database.</summary>
public FormPayPlan(Patient patCur,PayPlan payPlanCur)
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
PatCur=patCur.Copy();
PayPlanCur=payPlanCur.Copy();
FamCur=Patients.GetFamily(PatCur.PatNum);
SubList=InsSubs.RefreshForFam(FamCur);
InsPlanList=InsPlans.RefreshForSubList(SubList);
Lan.F(this);
}
///<summary>Returns a list of overcharged payplans from the listPayPlanNums. Only necessary for Dynamic Payment Plans.
///Returns an empty list if none are overcharged.</summary>
public static List <PayPlan> GetOverchargedPayPlans(List <long> listPayPlanNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <PayPlan> >(MethodBase.GetCurrentMethod(), listPayPlanNums));
}
List <PayPlan> listDynamicPayPlansForPatient = new List <PayPlan>();
foreach (long payPlanNum in listPayPlanNums)
{
if (payPlanNum == 0)
{
continue; //do not include installment plans
}
PayPlan payPlanCur = PayPlans.GetOne(payPlanNum);
if (payPlanCur.IsDynamic) //Only add Dynamic PayPlans to the list since they are all we care about
{
listDynamicPayPlansForPatient.Add(payPlanCur);
}
}
#region Get Data
List <PayPlanLink> listPayPlanLinksAll = PayPlanLinks.GetForPayPlans(listPayPlanNums);
List <long> listProcedureLinkFKeys = listPayPlanLinksAll.Where(x => x.LinkType == PayPlanLinkType.Procedure).Select(x => x.FKey).ToList();
List <long> listAdjustmentLinkFKeys = listPayPlanLinksAll.Where(x => x.LinkType == PayPlanLinkType.Adjustment).Select(x => x.FKey).ToList();
List <PayPlanCharge> listPayPlanCharges = PayPlanCharges.GetForPayPlans(listPayPlanNums);
List <Procedure> listProcsAttachedToPayPlan = Procedures.GetManyProc(listProcedureLinkFKeys, false);
List <Adjustment> listAdjsAttachedToPayPlan = Adjustments.GetMany(listAdjustmentLinkFKeys);
List <ClaimProc> listClaimProcsForProcs = ClaimProcs.GetForProcs(listProcedureLinkFKeys);
List <Adjustment> listAdjForProcs = Adjustments.GetForProcs(listProcedureLinkFKeys);
#endregion Get Data
List <PayPlan> listPayPlansOvercharged = new List <PayPlan>();
foreach (PayPlan payPlanCur in listDynamicPayPlansForPatient)
{
List <PayPlanLink> listLinksForPayPlan = listPayPlanLinksAll.FindAll(x => x.PayPlanNum == payPlanCur.PayPlanNum);
//Get total amount that has been debited for the current pay plan thus far.
decimal amtDebitedTotal = listPayPlanCharges.FindAll(x => x.PayPlanNum == payPlanCur.PayPlanNum && x.ChargeType == PayPlanChargeType.Debit)
.Sum(x => (decimal)x.Principal);
#region Sum Linked Production
decimal totalPrincipalForPayPlan = 0;
foreach (PayPlanLink payPlanLink in listLinksForPayPlan)
{
PayPlanProductionEntry productionEntry = null;
if (payPlanLink.LinkType == PayPlanLinkType.Procedure)
{
Procedure proc = listProcsAttachedToPayPlan.FirstOrDefault(x => x.ProcNum == payPlanLink.FKey);
if (proc != null)
{
productionEntry = new PayPlanProductionEntry(proc, payPlanLink, listClaimProcsForProcs, listAdjForProcs);
}
}
else if (payPlanLink.LinkType == PayPlanLinkType.Adjustment)
{
Adjustment adj = listAdjsAttachedToPayPlan.FirstOrDefault(x => x.AdjNum == payPlanLink.FKey);
if (adj != null)
{
productionEntry = new PayPlanProductionEntry(adj, payPlanLink);
}
}
if (productionEntry != null)
{
if (productionEntry.AmountOverride == 0)
{
totalPrincipalForPayPlan += productionEntry.AmountOriginal;
}
else
{
totalPrincipalForPayPlan += productionEntry.AmountOverride;
}
}
}
#endregion Sum Linked Production
//If the total that has been debited thus far exceeds the total principal for the pay plan, it is overcharged.
if (amtDebitedTotal.IsGreaterThan(totalPrincipalForPayPlan))
{
listPayPlansOvercharged.Add(payPlanCur);
}
}
return(listPayPlansOvercharged);
}
///<summary>The supplied payment plan should already have been saved in the database.</summary>
public FormPayPlan(Patient patCur,PayPlan payPlanCur){
//
// Required for Windows Form Designer support
//
InitializeComponent();
PatCur=patCur.Copy();
PayPlanCur=payPlanCur.Copy();
FamCur=Patients.GetFamily(PatCur.PatNum);
SubList=InsSubs.RefreshForFam(FamCur);
InsPlanList=InsPlans.RefreshForSubList(SubList);
FormPayPlanOpts=new FormPaymentPlanOptions(PayPlanCur.PaySchedule);
_formPayPlanRecalculate=new FormPayPlanRecalculate();
Lan.F(this);
}
