Thanks for the tip, however, the table still returns as null. Here are code snippets of the full round trip from FormCreditRecurringCharges to my plugin.
Code: Select all
object[] parameters = { table };
if (Plugins.HookMethod(this, "FormCreditRecurringCharges.FillGrid", parameters))
{
table = (DataTable)parameters[0];
return;
}
My Plugin.cs switch case:
Code: Select all
case "FormCreditRecurringCharges.FillGrid":
FormCreditRecurringChargesP.FillGrid((OpenDental.FormCreditRecurringCharges)sender, (DataTable)parameters[0]);
return true;
My FormCreditRecurringChargesP.FillGrid() implementation:
Code: Select all
public static void FillGrid(OpenDental.FormCreditRecurringCharges sender, DataTable table)
{
//first, get all the objects we need. Because they are by ref, the original gets altered.
ODGrid gridMain = (ODGrid)sender.Controls.Find("gridMain", true)[0];
Label labelTotal = (Label)sender.Controls.Find("labelTotal", true)[0];
Label labelSelected = (Label)sender.Controls.Find("labelSelected", true)[0];
table= GetRecurringChargeList();
gridMain.BeginUpdate();
gridMain.Columns.Clear();
ODGridColumn col=new ODGridColumn(Lan.g("TableRecurring","PatNum"),110);
gridMain.Columns.Add(col);
col=new ODGridColumn(Lan.g("TableRecurring","Name"),250);
gridMain.Columns.Add(col);
col=new ODGridColumn(Lan.g("TableRecurring","Total Bal"),90,HorizontalAlignment.Right);
gridMain.Columns.Add(col);
col=new ODGridColumn(Lan.g("TableRecurring","ChargeAmt"),100,HorizontalAlignment.Right);
gridMain.Columns.Add(col);
gridMain.Rows.Clear();
OpenDental.UI.ODGridRow row;
for(int i=0;i<table.Rows.Count;i++) {
row=new OpenDental.UI.ODGridRow();
Double famBalTotal=PIn.Double(table.Rows[i]["FamBalTotal"].ToString());
Double chargeAmt=PIn.Double(table.Rows[i]["ChargeAmt"].ToString());
row.Cells.Add(table.Rows[i]["PatNum"].ToString());
row.Cells.Add(table.Rows[i]["PatName"].ToString());
row.Cells.Add(famBalTotal.ToString("c"));
row.Cells.Add(chargeAmt.ToString("c"));
gridMain.Rows.Add(row);
}
gridMain.EndUpdate();
labelTotal.Text=Lan.g(sender,"Total=")+table.Rows.Count.ToString();
labelSelected.Text=Lan.g(sender,"Selected=")+gridMain.SelectedIndices.Length.ToString();
}
My GetRecurringChargeList() implementation that passes gets a DataTable returned and set to the table object:
Code: Select all
public static DataTable GetRecurringChargeList()
{
DataTable newTable = new DataTable();
//This query will return patient information and the latest recurring payment whom:
// -have recurring charges setup and today's date falls within the start and stop range.
// -have a total balance >= recurring charge amount
//NOTE: Query will return patients with or without payments regardless of when that payment occurred, filtering is done below.
string command = "SELECT PatNum,PatName,FamBalTotal,LatestPayment,DateStart,Address,AddressPat,Zip,ZipPat,XChargeToken,CCNumberMasked,CCExpiration,ChargeAmt,PayPlanNum,ProvNum,ClinicNum "
+ "FROM (";
#region Payments
command += "(SELECT 1,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + " PatName,"//The 'SELECT 1' garuntees the UNION will not combine results with payment plans.
+ "guar.BalTotal-guar.InsEst FamBalTotal,CASE WHEN MAX(pay.PayDate) IS NULL THEN " + POut.Date(new DateTime(1, 1, 1)) + " ELSE MAX(pay.PayDate) END LatestPayment,"
+ "cc.DateStart,cc.Address,pat.Address AddressPat,cc.Zip,pat.Zip ZipPat,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,cc.PayPlanNum,cc.DateStop,0 ProvNum,pat.ClinicNum "
+ "FROM (creditcard cc,patient pat,patient guar) "
+ "LEFT JOIN payment pay ON cc.PatNum=pay.PatNum AND pay.IsRecurringCC=1 "
+ "WHERE cc.PatNum=pat.PatNum "
+ "AND pat.Guarantor=guar.PatNum "
+ "AND cc.PayPlanNum=0 ";//Keeps card from showing up in case they have a balance AND is setup for payment plan.
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += "GROUP BY cc.CreditCardNum) ";
}
else
{//Oracle
command += "GROUP BY cc.CreditCardNum,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + ",PatName,guar.BalTotal-guar.InsEst,"
+ "cc.Address,cc.Zip,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,cc.PayPlanNum,cc.DateStop) ";
}
#endregion
command += "UNION ";
#region Payment Plans
command += "(SELECT 2,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + " PatName,";//The 'SELECT 2' garuntees the UNION will not combine results with payments.
//Special select statement to figure out how much is owed on a particular payment plan. This total amount will be Labeled as FamBalTotal for UNION purposes.
command += "ROUND((SELECT CASE WHEN SUM(ppc.Principal+ppc.Interest) IS NULL THEN 0 ELSE SUM(ppc.Principal+ppc.Interest) END "
+ "FROM PayPlanCharge ppc "
+ "WHERE ppc.ChargeDate <= " + DbHelper.Curdate() + " AND ppc.PayPlanNum=cc.PayPlanNum) "
+ "- CASE WHEN SUM(ps.SplitAmt) IS NULL THEN 0 ELSE SUM(ps.SplitAmt) END,2) FamBalTotal,";
command += "CASE WHEN MAX(ps.DatePay) IS NULL THEN " + POut.Date(new DateTime(1, 1, 1)) + " ELSE MAX(pay.PayDate) END LatestPayment,"
+ "cc.DateStart,cc.Address,pat.Address AddressPat,cc.Zip,pat.Zip ZipPat,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,cc.PayPlanNum,cc.DateStop,"
+ "(SELECT ppc1.ProvNum FROM payplancharge ppc1 WHERE ppc1.PayPlanNum=cc.PayPlanNum LIMIT 1) ProvNum,"
+ "(SELECT ppc2.ClinicNum FROM payplancharge ppc2 WHERE ppc2.PayPlanNum=cc.PayPlanNum LIMIT 1) ClinicNum "
+ "FROM creditcard cc "
+ "INNER JOIN patient pat ON pat.PatNum=cc.PatNum "
+ "LEFT JOIN paysplit ps ON ps.PayPlanNum=cc.PayPlanNum AND ps.PayPlanNum<>0 "
+ "LEFT JOIN payment pay ON pay.PayNum=ps.PayNum AND pay.IsRecurringCC=1 "
+ "WHERE cc.PayPlanNum<>0 ";
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += "GROUP BY cc.CreditCardNum) ";
}
else
{//Oracle
command += "GROUP BY cc.CreditCardNum,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + ",PatName,guar.BalTotal-guar.InsEst,"
+ "cc.Address,pat.Address,cc.Zip,pat.Zip,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,cc.PayPlanNum,cc.DateStop) ";
}
#endregion
//Now we have all the results for payments and payment plans, so do an obvious filter. A more thorough filter happens later.
command += ") due "
+ "WHERE FamBalTotal>=ChargeAmt "
+ "AND ChargeAmt>0 "
+ "AND DateStart<=" + DbHelper.Curdate() + " "
+ "AND (DateStop>=" + DbHelper.Curdate() + " OR YEAR(DateStop)<1880) ";
newTable = DataCore.GetTable(command);
return newTable;
}