public InstallmentPlan GetAllInstallmentPlan()
{
InstallmentPlan result = new InstallmentPlan();
PaymentPlanModel pm = new PaymentPlanModel();
result.plans = pm.getAllInstallmentPlan();
result.total_values = pm.getPaymentStatusValues();
return(result);
}
public InstallmentPlan CreateInstallmentPlan(MembershipInstallmentPlanDto installmentPlanDto)
{
var installmentPlan = new InstallmentPlan();
installmentPlan.Name = installmentPlanDto.GetName();
installmentPlan.CreatedDate = DateTime.Now;
installmentPlan.UpdatedDate = DateTime.Now;
installmentPlan.Active = true;
return(installmentPlan);
}
///<summary>Updates one InstallmentPlan in the database.</summary>
public static void Update(InstallmentPlan installmentPlan)
{
string command = "UPDATE installmentplan SET "
+ "PatNum = " + POut.Long(installmentPlan.PatNum) + ", "
+ "DateAgreement = " + POut.Date(installmentPlan.DateAgreement) + ", "
+ "DateFirstPayment = " + POut.Date(installmentPlan.DateFirstPayment) + ", "
+ "MonthlyPayment = '" + POut.Double(installmentPlan.MonthlyPayment) + "', "
+ "APR = " + POut.Float(installmentPlan.APR) + ", "
+ "Note = '" + POut.String(installmentPlan.Note) + "' "
+ "WHERE InstallmentPlanNum = " + POut.Long(installmentPlan.InstallmentPlanNum);
Db.NonQ(command);
}
///<summary>Inserts one InstallmentPlan into the database. Returns the new priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(InstallmentPlan installmentPlan)
{
if (DataConnection.DBtype == DatabaseType.MySql)
{
return(InsertNoCache(installmentPlan, false));
}
else
{
if (DataConnection.DBtype == DatabaseType.Oracle)
{
installmentPlan.InstallmentPlanNum = DbHelper.GetNextOracleKey("installmentplan", "InstallmentPlanNum"); //Cacheless method
}
return(InsertNoCache(installmentPlan, true));
}
}
///<summary>Converts a DataTable to a list of objects.</summary>
public static List<InstallmentPlan> TableToList(DataTable table){
List<InstallmentPlan> retVal=new List<InstallmentPlan>();
InstallmentPlan installmentPlan;
for(int i=0;i<table.Rows.Count;i++) {
installmentPlan=new InstallmentPlan();
installmentPlan.InstallmentPlanNum= PIn.Long (table.Rows[i]["InstallmentPlanNum"].ToString());
installmentPlan.PatNum = PIn.Long (table.Rows[i]["PatNum"].ToString());
installmentPlan.DateAgreement = PIn.Date (table.Rows[i]["DateAgreement"].ToString());
installmentPlan.DateFirstPayment = PIn.Date (table.Rows[i]["DateFirstPayment"].ToString());
installmentPlan.MonthlyPayment = PIn.Double(table.Rows[i]["MonthlyPayment"].ToString());
installmentPlan.APR = PIn.Float (table.Rows[i]["APR"].ToString());
installmentPlan.Note = PIn.String(table.Rows[i]["Note"].ToString());
retVal.Add(installmentPlan);
}
return retVal;
}
///<summary>Converts a DataTable to a list of objects.</summary>
public static List <InstallmentPlan> TableToList(DataTable table)
{
List <InstallmentPlan> retVal = new List <InstallmentPlan>();
InstallmentPlan installmentPlan;
foreach (DataRow row in table.Rows)
{
installmentPlan = new InstallmentPlan();
installmentPlan.InstallmentPlanNum = PIn.Long(row["InstallmentPlanNum"].ToString());
installmentPlan.PatNum = PIn.Long(row["PatNum"].ToString());
installmentPlan.DateAgreement = PIn.Date(row["DateAgreement"].ToString());
installmentPlan.DateFirstPayment = PIn.Date(row["DateFirstPayment"].ToString());
installmentPlan.MonthlyPayment = PIn.Double(row["MonthlyPayment"].ToString());
installmentPlan.APR = PIn.Float(row["APR"].ToString());
installmentPlan.Note = PIn.String(row["Note"].ToString());
retVal.Add(installmentPlan);
}
return(retVal);
}
private void AddFinanceCharge(long PatNum, DateTime date, string APR, double OverallBalance, long PriProv)
{
InstallmentPlan installPlan = InstallmentPlans.GetOneForFam(PatNum);
if (installPlan != null) //Patient has an installment plan so use that APR instead.
{
APR = installPlan.APR.ToString();
}
Adjustment AdjustmentCur = new Adjustment();
AdjustmentCur.PatNum = PatNum;
//AdjustmentCur.DateEntry=PIn.PDate(textDate.Text);//automatically handled
AdjustmentCur.AdjDate = date;
AdjustmentCur.ProcDate = date;
AdjustmentCur.AdjType = PrefC.GetLong(PrefName.FinanceChargeAdjustmentType);
AdjustmentCur.AdjNote = ""; //"Finance Charge";
AdjustmentCur.AdjAmt = Math.Round(((PIn.Double(APR) * .01d / 12d) * OverallBalance), 2);
AdjustmentCur.ProvNum = PriProv;
Adjustments.Insert(AdjustmentCur);
}
///<summary>Inserts one InstallmentPlan into the database. Returns the new priKey.</summary>
public static long Insert(InstallmentPlan installmentPlan){
if(DataConnection.DBtype==DatabaseType.Oracle) {
installmentPlan.InstallmentPlanNum=DbHelper.GetNextOracleKey("installmentplan","InstallmentPlanNum");
int loopcount=0;
while(loopcount<100){
try {
return Insert(installmentPlan,true);
}
catch(Oracle.DataAccess.Client.OracleException ex){
if(ex.Number==1 && ex.Message.ToLower().Contains("unique constraint") && ex.Message.ToLower().Contains("violated")){
installmentPlan.InstallmentPlanNum++;
loopcount++;
}
else{
throw ex;
}
}
}
throw new ApplicationException("Insert failed. Could not generate primary key.");
}
else {
return Insert(installmentPlan,false);
}
}
private InstallmentPlan GetPlanFromCalculationResponse(ObligationCalculationResult response)
{
InstallmentPlan plan = new InstallmentPlan
{
Annuity = (double)response.InstallmentPlanCalculationResult.AnnuityInfo.Where(ai => ai.AnnuitySpecified && ai.Annuity != 0).Select(it => it.Annuity).FirstOrDefault()
};
if (response.InstallmentPlanCalculationResult.EffectiveInterestRateSpecified)
{
plan.EffectiveInterestRate = (double)response.InstallmentPlanCalculationResult.EffectiveInterestRate.GetValueOrDefault();
}
int cnt = 0;
DateTime lastDate = DateTime.MinValue;
plan.Installments = new List <InstallmentPlanRow>();
foreach (System.Data.DataRow item in response.InstallmentPlanCalculationResult.InstallmentPlan.Rows)
{
var r = GetPlanRowFromDataRow(item);
if (r.Disbursement != 0 || r.Annuity != 0 || r.Fee != 0 || r.OtherExpenses != 0)
{
plan.Installments.Add(r);
}
if (r.PrincipalRepayment != 0)
{
cnt++;
lastDate = r.Date.GetValueOrDefault();
}
}
plan.NumberOfInstallments = cnt;
return(plan);
}
///<summary>Adds the xml for one statement. Validation is performed here. Throws an exception if there is a validation failure.</summary>
public static void GenerateOneStatement(XmlWriter writer, Statement stmt, Patient pat, Family fam, DataSet dataSet)
{
Patient guar = fam.ListPats[0];
if (!Regex.IsMatch(guar.State, "^[A-Z]{2}$"))
{
throw new ApplicationException(Lan.g("EHG_Statements", "Guarantor state must be two uppercase characters.") + " " + guar.FName + " " + guar.LName + " #" + guar.PatNum);
}
writer.WriteStartElement("EisStatement");
writer.WriteAttributeString("OutputFormat", "StmOut_Blue6Col");
writer.WriteAttributeString("CreditCardChoice", PrefC.GetString(PrefName.BillingElectCreditCardChoices));
writer.WriteStartElement("Patient");
writer.WriteElementString("Name", guar.GetNameFLFormal());
writer.WriteElementString("Account", guar.PatNum.ToString());
writer.WriteElementString("Address1", guar.Address);
writer.WriteElementString("Address2", guar.Address2);
writer.WriteElementString("City", guar.City);
writer.WriteElementString("State", guar.State);
writer.WriteElementString("Zip", guar.Zip);
string email = "";
Def billingDef = DefC.GetDef(DefCat.BillingTypes, guar.BillingType);
if (billingDef.ItemValue == "E")
{
email = guar.Email;
}
writer.WriteElementString("EMail", email);
//Account summary-----------------------------------------------------------------------
writer.WriteStartElement("AccountSummary");
if (stmt.DateRangeFrom.Year < 1880) //make up a statement date.
{
writer.WriteElementString("PriorStatementDate", DateTime.Today.AddMonths(-1).ToString("MM/dd/yyyy"));
}
else
{
writer.WriteElementString("PriorStatementDate", stmt.DateRangeFrom.AddDays(-1).ToString("MM/dd/yyyy"));
}
DateTime dueDate;
if (PrefC.GetLong(PrefName.StatementsCalcDueDate) == -1)
{
dueDate = DateTime.Today.AddDays(10);
}
else
{
dueDate = DateTime.Today.AddDays(PrefC.GetLong(PrefName.StatementsCalcDueDate));
}
writer.WriteElementString("DueDate", dueDate.ToString("MM/dd/yyyy"));
writer.WriteElementString("StatementDate", stmt.DateSent.ToString("MM/dd/yyyy"));
double balanceForward = 0;
for (int r = 0; r < dataSet.Tables["misc"].Rows.Count; r++)
{
if (dataSet.Tables["misc"].Rows[r]["descript"].ToString() == "balanceForward")
{
balanceForward = PIn.Double(dataSet.Tables["misc"].Rows[r]["value"].ToString());
}
}
writer.WriteElementString("PriorBalance", balanceForward.ToString("F2"));
writer.WriteElementString("RunningBalance", ""); //for future use
writer.WriteElementString("PerPayAdj", ""); //optional
writer.WriteElementString("InsPayAdj", ""); //optional
writer.WriteElementString("Adjustments", ""); //for future use
writer.WriteElementString("NewCharges", ""); //optional
writer.WriteElementString("FinanceCharges", ""); //for future use
DataTable tableAccount = null;
for (int i = 0; i < dataSet.Tables.Count; i++)
{
if (dataSet.Tables[i].TableName.StartsWith("account"))
{
tableAccount = dataSet.Tables[i];
}
}
double credits = 0;
for (int i = 0; i < tableAccount.Rows.Count; i++)
{
credits += PIn.Double(tableAccount.Rows[i]["creditsDouble"].ToString());
}
writer.WriteElementString("Credits", credits.ToString("F2"));
//on a regular printed statement, the amount due at the top might be different from the balance at the middle right.
//This is because of payment plan balances.
//But in e-bills, there is only one amount due.
//Insurance estimate is already subtracted, and payment plan balance is already added.
double amountDue = guar.BalTotal;
//add payplan due amt:
for (int m = 0; m < dataSet.Tables["misc"].Rows.Count; m++)
{
if (dataSet.Tables["misc"].Rows[m]["descript"].ToString() == "payPlanDue")
{
amountDue += PIn.Double(dataSet.Tables["misc"].Rows[m]["value"].ToString());
}
}
if (PrefC.GetBool(PrefName.BalancesDontSubtractIns))
{
writer.WriteElementString("EstInsPayments", ""); //optional.
}
else //this is typical
{
writer.WriteElementString("EstInsPayments", guar.InsEst.ToString("F2")); //optional.
amountDue -= guar.InsEst;
}
InstallmentPlan installPlan = InstallmentPlans.GetOneForFam(guar.PatNum);
if (installPlan != null)
{
//show lesser of normal total balance or the monthly payment amount.
if (installPlan.MonthlyPayment < amountDue)
{
amountDue = installPlan.MonthlyPayment;
}
}
writer.WriteElementString("PatientShare", amountDue.ToString("F2"));
writer.WriteElementString("CurrentBalance", amountDue.ToString("F2")); //this is ambiguous. It seems to be AmountDue, but it could possibly be 0-30 days aging
writer.WriteElementString("PastDue30", guar.Bal_31_60.ToString("F2")); //optional
writer.WriteElementString("PastDue60", guar.Bal_61_90.ToString("F2")); //optional
writer.WriteElementString("PastDue90", guar.BalOver90.ToString("F2")); //optional
writer.WriteElementString("PastDue120", ""); //optional
writer.WriteEndElement(); //AccountSummary
//Notes-----------------------------------------------------------------------------------
writer.WriteStartElement("Notes");
if (stmt.NoteBold != "")
{
writer.WriteStartElement("Note");
writer.WriteAttributeString("FgColor", "Red"); //ColorToHexString(Color.DarkRed));
//writer.WriteAttributeString("BgColor",ColorToHexString(Color.White));
writer.WriteString(Tidy(stmt.NoteBold, 500)); //Limit of 500 char on notes.
writer.WriteEndElement(); //Note
}
if (stmt.Note != "")
{
writer.WriteStartElement("Note");
//writer.WriteAttributeString("FgColor",ColorToHexString(Color.Black));
//writer.WriteAttributeString("BgColor",ColorToHexString(Color.White));
writer.WriteString(Tidy(stmt.Note, 500)); //Limit of 500 char on notes.
writer.WriteEndElement(); //Note
}
writer.WriteEndElement(); //Notes
//Detail items------------------------------------------------------------------------------
writer.WriteStartElement("DetailItems");
//string note;
string descript;
string fulldesc;
string procCode;
string tth;
//string linedesc;
string[] lineArray;
List <string> lines;
DateTime date;
int seq = 0;
for (int i = 0; i < tableAccount.Rows.Count; i++)
{
procCode = tableAccount.Rows[i]["ProcCode"].ToString();
tth = tableAccount.Rows[i]["tth"].ToString();
descript = tableAccount.Rows[i]["description"].ToString();
fulldesc = procCode + " " + tth + " " + descript; //There are frequently CRs within a procedure description for things like ins est.
lineArray = fulldesc.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
lines = new List <string>(lineArray);
//The specs say that the line limit is 30 char. But in testing, it will take 50 char.
//We will use 40 char to be safe.
if (lines[0].Length > 40)
{
string newline = lines[0].Substring(40);
lines[0] = lines[0].Substring(0, 40); //first half
lines.Insert(1, newline); //second half
}
for (int li = 0; li < lines.Count; li++)
{
writer.WriteStartElement("DetailItem"); //has a child item. We won't add optional child note
writer.WriteAttributeString("sequence", seq.ToString());
writer.WriteStartElement("Item");
if (li == 0)
{
date = (DateTime)tableAccount.Rows[i]["DateTime"];
writer.WriteElementString("Date", date.ToString("MM/dd/yyyy"));
writer.WriteElementString("PatientName", tableAccount.Rows[i]["patient"].ToString());
}
else
{
writer.WriteElementString("Date", "");
writer.WriteElementString("PatientName", "");
}
writer.WriteStartElement("Description");
writer.WriteCData(Tidy(lines[li], 40)); //Jessica at DentalXchange says limit is 120. Docs say limit is 30. CData to allow any string.
writer.WriteEndElement(); //Description
if (li == 0)
{
writer.WriteElementString("Charges", tableAccount.Rows[i]["charges"].ToString());
writer.WriteElementString("Credits", tableAccount.Rows[i]["credits"].ToString());
writer.WriteElementString("Balance", tableAccount.Rows[i]["balance"].ToString());
}
else
{
writer.WriteElementString("Charges", "");
writer.WriteElementString("Credits", "");
writer.WriteElementString("Balance", "");
}
writer.WriteEndElement(); //Item
writer.WriteEndElement(); //DetailItem
seq++;
}
/*The code below just didn't work because notes don't get displayed on the statement.
* linedesc=lines[0];
* note="";
* if(linedesc.Length>30) {
* note=linedesc.Substring(30);
* linedesc=linedesc.Substring(0,30);
* }
* for(int l=1;l<lines.Length;l++) {
* if(note!="") {
* note+="\r\n";
* }
* note+=lines[l];
* }
*
* if(note!="") {
* writer.WriteStartElement("Note");
* //we're not going to specify colors here since they're optional
* writer.WriteCData(note);
* writer.WriteEndElement();//Note
* }*/
}
writer.WriteEndElement(); //DetailItems
writer.WriteEndElement(); //Patient
writer.WriteEndElement(); //EisStatement
}
///<summary>Adds the xml for one statement.</summary>
public static void GenerateOneStatement(XmlWriter writer, Statement stmt, Patient pat, Family fam, DataSet dataSet)
{
Patient guar = fam.ListPats[0];
writer.WriteStartElement("Statement");
//writer.WriteAttributeString("CreditCardChoice",PrefC.GetString(PrefName.BillingElectCreditCardChoices"));
//remit address----------------------------------------------------------
writer.WriteStartElement("RemitAddress");
if (!PrefC.GetBool(PrefName.EasyNoClinics) && Clinics.List.Length > 0 && //if using clinics
Clinics.GetClinic(guar.ClinicNum) != null) //and this guar is assigned to a clinic
{
Clinic clinic = Clinics.GetClinic(guar.ClinicNum);
writer.WriteElementString("Name", clinic.Description);
writer.WriteElementString("Address", clinic.Address);
writer.WriteElementString("Address2", clinic.Address2);
writer.WriteElementString("City", clinic.City);
writer.WriteElementString("State", clinic.State);
writer.WriteElementString("Zip", clinic.Zip);
string phone = "";
if (clinic.Phone.Length == 10)
{
phone = "(" + clinic.Phone.Substring(0, 3) + ")" + clinic.Phone.Substring(3, 3) + "-" + clinic.Phone.Substring(6);
}
writer.WriteElementString("Phone", phone);
}
else //not using clinics
{
writer.WriteElementString("Name", PrefC.GetString(PrefName.PracticeTitle));
writer.WriteElementString("Address", PrefC.GetString(PrefName.PracticeAddress));
writer.WriteElementString("Address2", PrefC.GetString(PrefName.PracticeAddress2));
writer.WriteElementString("City", PrefC.GetString(PrefName.PracticeCity));
writer.WriteElementString("State", PrefC.GetString(PrefName.PracticeST));
writer.WriteElementString("Zip", PrefC.GetString(PrefName.PracticeZip));
writer.WriteElementString("Phone", PrefC.GetString(PrefName.PracticePhone));
}
writer.WriteEndElement(); //RemitAddress
//Patient-------------------------------------------------------------------------------
writer.WriteStartElement("Patient");
writer.WriteElementString("Name", guar.GetNameFLFormal());
writer.WriteElementString("AccountNum", guar.PatNum.ToString());
writer.WriteElementString("Address", guar.Address);
writer.WriteElementString("Address2", guar.Address2);
writer.WriteElementString("City", guar.City);
writer.WriteElementString("State", guar.State);
writer.WriteElementString("Zip", guar.Zip);
writer.WriteEndElement(); //Patient
//Account summary-----------------------------------------------------------------------
writer.WriteStartElement("AccountSummary");
if (PrefC.GetLong(PrefName.StatementsCalcDueDate) == -1)
{
writer.WriteElementString("DueDate", Lan.g("FormRpStatement", "Upon Receipt"));
}
else
{
DateTime dueDate = DateTime.Today.AddDays(PrefC.GetLong(PrefName.StatementsCalcDueDate));
writer.WriteElementString("DueDate", dueDate.ToString("MM/dd/yyyy"));
}
writer.WriteElementString("StatementDate", stmt.DateSent.ToString("MM/dd/yyyy"));
DataTable tableAccount = null;
for (int i = 0; i < dataSet.Tables.Count; i++)
{
if (dataSet.Tables[i].TableName.StartsWith("account"))
{
tableAccount = dataSet.Tables[i];
}
}
//on a regular printed statement, the amount due at the top might be different from the balance at the middle right.
//This is because of payment plan balances.
//But in e-bills, there is only one amount due.
//Insurance estimate is already subtracted, and payment plan balance is already added.
double amountDue = guar.BalTotal;
//add payplan due amt:
for (int m = 0; m < dataSet.Tables["misc"].Rows.Count; m++)
{
if (dataSet.Tables["misc"].Rows[m]["descript"].ToString() == "payPlanDue")
{
amountDue += PIn.Double(dataSet.Tables["misc"].Rows[m]["value"].ToString());
}
}
if (PrefC.GetBool(PrefName.BalancesDontSubtractIns))
{
writer.WriteElementString("EstInsPayments", ""); //optional.
}
else //this is typical
{
writer.WriteElementString("EstInsPayments", guar.InsEst.ToString("F2")); //optional.
amountDue -= guar.InsEst;
}
InstallmentPlan installPlan = InstallmentPlans.GetOneForFam(guar.PatNum);
if (installPlan != null)
{
//show lesser of normal total balance or the monthly payment amount.
if (installPlan.MonthlyPayment < amountDue)
{
amountDue = installPlan.MonthlyPayment;
}
}
writer.WriteElementString("AmountDue", amountDue.ToString("F2"));
writer.WriteElementString("Bal_0_30", guar.Bal_0_30.ToString("F2"));
writer.WriteElementString("Bal_31_60", guar.Bal_31_60.ToString("F2"));
writer.WriteElementString("Bal_61_90", guar.Bal_61_90.ToString("F2"));
writer.WriteElementString("BalOver90", guar.BalOver90.ToString("F2"));
writer.WriteEndElement(); //AccountSummary
//Notes-----------------------------------------------------------------------------------
writer.WriteStartElement("Notes");
if (stmt.NoteBold != "")
{
writer.WriteStartElement("Note");
writer.WriteAttributeString("FgColor", ColorToHexString(Color.DarkRed));
writer.WriteCData(stmt.NoteBold);
writer.WriteEndElement(); //Note
}
if (stmt.Note != "")
{
writer.WriteStartElement("Note");
writer.WriteAttributeString("FgColor", ColorToHexString(Color.Black));
writer.WriteCData(stmt.Note);
writer.WriteEndElement(); //Note
}
writer.WriteEndElement(); //Notes
//Detail items------------------------------------------------------------------------------
writer.WriteStartElement("DetailItems");
string descript;
string fulldesc;
string procCode;
string tth;
string[] lineArray;
List <string> lines;
DateTime date;
int seq = 0;
for (int i = 0; i < tableAccount.Rows.Count; i++)
{
procCode = tableAccount.Rows[i]["ProcCode"].ToString();
tth = tableAccount.Rows[i]["tth"].ToString();
descript = tableAccount.Rows[i]["description"].ToString();
fulldesc = procCode + " " + tth + " " + descript;
lineArray = fulldesc.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
lines = new List <string>(lineArray);
//We assume that the line limit is 40 char.
if (lines[0].Length > 40)
{
string newline = lines[0].Substring(40);
lines[0] = lines[0].Substring(0, 40); //first half
lines.Insert(1, newline); //second half
}
for (int li = 0; li < lines.Count; li++)
{
writer.WriteStartElement("Item");
writer.WriteAttributeString("sequence", seq.ToString());
if (li == 0)
{
date = (DateTime)tableAccount.Rows[i]["DateTime"];
writer.WriteElementString("Date", date.ToString("MM/dd/yyyy"));
writer.WriteElementString("PatientName", tableAccount.Rows[i]["patient"].ToString());
}
else
{
writer.WriteElementString("Date", "");
writer.WriteElementString("PatientName", "");
}
writer.WriteElementString("Description", lines[li]);
if (li == 0)
{
writer.WriteElementString("Charges", tableAccount.Rows[i]["charges"].ToString());
writer.WriteElementString("Credits", tableAccount.Rows[i]["credits"].ToString());
writer.WriteElementString("Balance", tableAccount.Rows[i]["balance"].ToString());
}
else
{
writer.WriteElementString("Charges", "");
writer.WriteElementString("Credits", "");
writer.WriteElementString("Balance", "");
}
writer.WriteEndElement(); //Item
seq++;
}
}
writer.WriteEndElement(); //DetailItems
writer.WriteEndElement(); //Statement
}
///<summary>Adds the xml for one statement. Validation is performed here. Throws an exception if there is a validation failure.</summary>
public static void GenerateOneStatement(XmlWriter writer, Statement stmt, Patient pat, Family fam, DataSet dataSet)
{
DataTable tableMisc = dataSet.Tables["misc"];
DataTable tableAccount = dataSet.Tables.OfType <DataTable>().FirstOrDefault(x => x.TableName.StartsWith("account"));
Patient guar = fam.ListPats[0];
if (!Regex.IsMatch(guar.State, "^[A-Z]{2}$"))
{
throw new ApplicationException(Lan.g("EHG_Statements", "Guarantor state must be two uppercase characters.") + " " + guar.FName + " " + guar.LName + " #" + guar.PatNum);
}
writer.WriteStartElement("EisStatement");
writer.WriteAttributeString("OutputFormat", "StmOut_Blue6Col");
writer.WriteAttributeString("CreditCardChoice", PrefC.GetString(PrefName.BillingElectCreditCardChoices));
writer.WriteStartElement("Patient");
writer.WriteElementString("Name", guar.GetNameFLFormal());
writer.WriteElementString("Account", guar.PatNum.ToString());
writer.WriteElementString("Address1", guar.Address);
writer.WriteElementString("Address2", guar.Address2);
writer.WriteElementString("City", guar.City);
writer.WriteElementString("State", guar.State);
writer.WriteElementString("Zip", guar.Zip);
writer.WriteElementString("EMail", guar.Email);
//Account summary-----------------------------------------------------------------------
writer.WriteStartElement("AccountSummary");
if (stmt.DateRangeFrom.Year < 1880) //make up a statement date.
{
writer.WriteElementString("PriorStatementDate", DateTime.Today.AddMonths(-1).ToString("MM/dd/yyyy"));
}
else
{
writer.WriteElementString("PriorStatementDate", stmt.DateRangeFrom.AddDays(-1).ToString("MM/dd/yyyy"));
}
DateTime dueDate;
if (PrefC.GetLong(PrefName.StatementsCalcDueDate) == -1)
{
dueDate = DateTime.Today.AddDays(10);
}
else
{
dueDate = DateTime.Today.AddDays(PrefC.GetLong(PrefName.StatementsCalcDueDate));
}
writer.WriteElementString("DueDate", dueDate.ToString("MM/dd/yyyy"));
writer.WriteElementString("StatementDate", stmt.DateSent.ToString("MM/dd/yyyy"));
double balanceForward = tableMisc.Rows.OfType <DataRow>().Where(x => x["descript"].ToString() == "balanceForward")
.Select(x => PIn.Double(x["value"].ToString())).FirstOrDefault();//defaults to 0
writer.WriteElementString("PriorBalance", balanceForward.ToString("F2"));
writer.WriteElementString("RunningBalance", ""); //for future use
writer.WriteElementString("PerPayAdj", ""); //optional
writer.WriteElementString("InsPayAdj", ""); //optional
writer.WriteElementString("Adjustments", ""); //for future use
double charges = tableAccount.Rows.OfType <DataRow>().Sum(x => PIn.Double(x["chargesDouble"].ToString()));
writer.WriteElementString("NewCharges", charges.ToString("F2")); //optional
writer.WriteElementString("FinanceCharges", ""); //for future use
double credits = tableAccount.Rows.OfType <DataRow>().Sum(x => PIn.Double(x["creditsDouble"].ToString()));
writer.WriteElementString("Credits", credits.ToString("F2"));
//On a regular printed statement, the amount due at the top might be different from the balance at the middle right due to payplan balances.
//But in e-bills, there is only one amount due. Insurance estimate is already subtracted, and payment plan balance is already added.
double amountDue = guar.BalTotal;
if (PrefC.GetInt(PrefName.PayPlansVersion) == 1) //with version 2, payplan debits/credits are aged individually and are included in guar.BalTotal
{
amountDue += tableMisc.Rows.OfType <DataRow>().Where(x => x["descript"].ToString() == "payPlanDue")
.Select(x => PIn.Double(x["value"].ToString())).DefaultIfEmpty(0).Sum(); //add payplan(s) due amt
}
double insEst = 0;
if (!PrefC.GetBool(PrefName.BalancesDontSubtractIns)) //this is typical
{
insEst = guar.InsEst;
}
InstallmentPlan installPlan = InstallmentPlans.GetOneForFam(guar.PatNum);
if (installPlan != null && installPlan.MonthlyPayment < (amountDue - insEst))
{
amountDue = installPlan.MonthlyPayment;
insEst = 0;
}
writer.WriteElementString("EstInsPayments", insEst.ToString("F2")); //optional.
writer.WriteElementString("PatientShare", (amountDue - insEst).ToString("F2"));
writer.WriteElementString("CurrentBalance", amountDue.ToString("F2")); //this is ambiguous. It seems to be AmountDue, but it could be 0-30 days aging
writer.WriteElementString("PastDue30", guar.Bal_31_60.ToString("F2")); //optional
writer.WriteElementString("PastDue60", guar.Bal_61_90.ToString("F2")); //optional
writer.WriteElementString("PastDue90", guar.BalOver90.ToString("F2")); //optional
writer.WriteElementString("PastDue120", ""); //optional
writer.WriteEndElement(); //AccountSummary
//Notes-----------------------------------------------------------------------------------
writer.WriteStartElement("Notes");
if (stmt.NoteBold != "")
{
writer.WriteStartElement("Note");
writer.WriteAttributeString("FgColor", "Red"); //ColorToHexString(Color.DarkRed));
//writer.WriteAttributeString("BgColor",ColorToHexString(Color.White));
writer.WriteCData(stmt.NoteBold.Left(500)); //Limit of 500 char on notes.
writer.WriteEndElement(); //Note
}
if (stmt.Note != "")
{
writer.WriteStartElement("Note");
//writer.WriteAttributeString("FgColor",ColorToHexString(Color.Black));
//writer.WriteAttributeString("BgColor",ColorToHexString(Color.White));
writer.WriteCData(stmt.Note.Left(500)); //Limit of 500 char on notes.
writer.WriteEndElement(); //Note
}
writer.WriteEndElement(); //Notes
//Detail items------------------------------------------------------------------------------
writer.WriteStartElement("DetailItems");
List <string> lines;
int seq = 0;
//Jessica at DentalXchange says limit is 120. Specs say limit is 30.
//If we send more than 50 characters, DentalXChange will break the line at the 50th character, even if it is in the middle of a word, and wrap
//the rest of the line, so up to 70 chars onto line 2, which could easily extend past the end of the description field. The wrapped line will
//also have a different line spacing than if sent as a separate xml element. See the examples in
//...\Programmers Documents\eClaims Clearinghouse and Carrier Specific Details\DentalXChange ClaimConnect\ClaimConnect - EHG - WebClaim\EHG Statements\Examples
//Example: original description:
//line 1: 'D6103 5 bone graft for repair of periimplant defect - does not include flap entry and closure. Placement of a barrier membrane or biologic materials to aid in osseous regeneration are reported separately'
//version 16.2 sent as:
//line 1: 'D6103 5 bone graft for repair of periimplant defect - does not include flap entry and closure. Placement of a barrier '
//line 2: 'membrane or biologic materials to aid in osseous regeneration are reported separately'
//DentalXChange displayed lines as (with line 2 extended past the end of the description field):
//line 1: 'D6103 5 bone graft for repair of periimplant defe -'
//line 2: 'ct - does not include flap entry and closure. Placement of a barrier'
//line 3: 'membrane or biologic materials to aid in osseous r -'
//line 4: 'egeneration are reported separately'
//version 16.3 and up sent and displayed as:
//line 1: 'D6103 5 bone graft for repair of periimplant'
//line 2: 'defect - does not include flap entry and closure.'
//line 3: 'Placement of a barrier membrane or biologic'
//line 4: 'materials to aid in osseous regeneration are'
//line 5: 'reported separately'
const int lineMaxLen = 50;
int firstIndexNewLine;
string lineCur;
foreach (DataRow rowCur in tableAccount.Rows)
{
//There are frequently CRs within a procedure description for things like ins est.
lines = string.Join(" ", new[] { rowCur["ProcCode"].ToString(), rowCur["tth"].ToString(), rowCur["description"].ToString() })
.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries).Select(x => x.Trim()).ToList();
for (int li = 0; li < lines.Count; li++)
{
lineCur = lines[li];
if (lineCur.Length < 1 || lineCur.All(x => char.IsWhiteSpace(x))) //nothing to write
{
continue;
}
writer.WriteStartElement("DetailItem"); //has a child item. We won't add optional child note
writer.WriteAttributeString("sequence", seq.ToString());
writer.WriteStartElement("Item");
writer.WriteElementString("Date", li == 0?PIn.Date(rowCur["DateTime"].ToString()).ToString("MM/dd/yyyy"):"");
writer.WriteElementString("PatientName", li == 0?rowCur["patient"].ToString():"");
if (lineCur.Length > lineMaxLen)
{
firstIndexNewLine = lineMaxLen;
for (int c = lineMaxLen - 2; c > -1; c--) //-2, 1 for length to index and 1 so we can safely check index and index+1
{
if (!char.IsWhiteSpace(lineCur[c]) && char.IsWhiteSpace(lineCur[c + 1]))
{
firstIndexNewLine = c + 1;
break;
}
}
lines.Insert(li + 1, lineCur.Substring(firstIndexNewLine).Trim());
lines[li] = lineCur.Left(firstIndexNewLine);
}
writer.WriteStartElement("Description");
writer.WriteCData(lines[li]); //CData to allow any string, including punctuation, syntax characters and special characters.
writer.WriteEndElement(); //Description
writer.WriteElementString("Charges", li == 0?rowCur["charges"].ToString():"");
writer.WriteElementString("Credits", li == 0?rowCur["credits"].ToString():"");
writer.WriteElementString("Balance", li == 0?rowCur["balance"].ToString():"");
writer.WriteEndElement(); //Item
writer.WriteEndElement(); //DetailItem
seq++;
}
#region Notes Don't Display On Statements
/*The code below just didn't work because notes don't get displayed on the statement.
* linedesc=lines[0];
* note="";
* if(linedesc.Length>30) {
* note=linedesc.Substring(30);
* linedesc=linedesc.Substring(0,30);
* }
* for(int l=1;l<lines.Length;l++) {
* if(note!="") {
* note+="\r\n";
* }
* note+=lines[l];
* }
*
* if(note!="") {
* writer.WriteStartElement("Note");
* //we're not going to specify colors here since they're optional
* writer.WriteCData(note);
* writer.WriteEndElement();//Note
* }*/
#endregion Notes Don't Display On Statements
}
writer.WriteEndElement(); //DetailItems
writer.WriteEndElement(); //Patient
writer.WriteEndElement(); //EisStatement
}
///<summary>Adds the xml for one statement.</summary>
public static void GenerateOneStatement(XmlWriter writer, Statement stmt, Patient pat, Family fam, DataSet dataSet)
{
writer.WriteStartElement("Statement");
writer.WriteStartElement("RecipientAddress");
Patient guar = fam.ListPats[0];
writer.WriteElementString("Name", guar.GetNameFLFormal());
if (PrefC.GetBool(PrefName.StatementAccountsUseChartNumber))
{
writer.WriteElementString("Account", guar.ChartNumber);
}
else
{
writer.WriteElementString("Account", POut.Long(guar.PatNum));
}
writer.WriteElementString("Address1", guar.Address);
writer.WriteElementString("Address2", guar.Address2);
writer.WriteElementString("City", guar.City);
writer.WriteElementString("State", guar.State);
writer.WriteElementString("Zip", guar.Zip);
string email = "";
Def billingDef = Defs.GetDef(DefCat.BillingTypes, guar.BillingType);
if (billingDef.ItemValue == "E")
{
email = guar.Email;
}
writer.WriteElementString("EMail", email);
writer.WriteEndElement(); //RecipientAddress
//Account summary-----------------------------------------------------------------------
if (stmt.DateRangeFrom.Year < 1880) //make up a statement date.
{
writer.WriteElementString("PriorStatementDate", DateTime.Today.AddMonths(-1).ToString("MM/dd/yyyy"));
}
else
{
writer.WriteElementString("PriorStatementDate", stmt.DateRangeFrom.AddDays(-1).ToString("MM/dd/yyyy"));
}
DateTime dueDate;
if (PrefC.GetLong(PrefName.StatementsCalcDueDate) == -1)
{
dueDate = DateTime.Today.AddDays(10);
}
else
{
dueDate = DateTime.Today.AddDays(PrefC.GetLong(PrefName.StatementsCalcDueDate));
}
writer.WriteElementString("DueDate", dueDate.ToString("MM/dd/yyyy"));
writer.WriteElementString("StatementDate", stmt.DateSent.ToString("MM/dd/yyyy"));
double balanceForward = 0;
for (int r = 0; r < dataSet.Tables["misc"].Rows.Count; r++)
{
if (dataSet.Tables["misc"].Rows[r]["descript"].ToString() == "balanceForward")
{
balanceForward = PIn.Double(dataSet.Tables["misc"].Rows[r]["value"].ToString());
}
}
writer.WriteElementString("PriorBalance", balanceForward.ToString("F2"));
DataTable tableAccount = null;
for (int i = 0; i < dataSet.Tables.Count; i++)
{
if (dataSet.Tables[i].TableName.StartsWith("account"))
{
tableAccount = dataSet.Tables[i];
}
}
double credits = 0;
for (int i = 0; i < tableAccount.Rows.Count; i++)
{
credits += PIn.Double(tableAccount.Rows[i]["creditsDouble"].ToString());
}
writer.WriteElementString("Credits", credits.ToString("F2"));
decimal payPlanDue = 0;
double amountDue = guar.BalTotal;
for (int m = 0; m < dataSet.Tables["misc"].Rows.Count; m++)
{
if (dataSet.Tables["misc"].Rows[m]["descript"].ToString() == "payPlanDue")
{
payPlanDue += PIn.Decimal(dataSet.Tables["misc"].Rows[m]["value"].ToString()); //This will be an option once more users are using it.
}
}
writer.WriteElementString("PayPlanDue", payPlanDue.ToString("F2"));
if (PrefC.GetBool(PrefName.BalancesDontSubtractIns))
{
writer.WriteElementString("EstInsPayments", ""); //optional.
}
else //this is typical
{
writer.WriteElementString("EstInsPayments", guar.InsEst.ToString("F2")); //optional.
amountDue -= guar.InsEst;
}
InstallmentPlan installPlan = InstallmentPlans.GetOneForFam(guar.PatNum);
if (installPlan != null)
{
//show lesser of normal total balance or the monthly payment amount.
if (installPlan.MonthlyPayment < amountDue)
{
amountDue = installPlan.MonthlyPayment;
}
}
writer.WriteElementString("AmountDue", amountDue.ToString("F2"));
writer.WriteElementString("PastDue30", guar.Bal_31_60.ToString("F2")); //optional
writer.WriteElementString("PastDue60", guar.Bal_61_90.ToString("F2")); //optional
writer.WriteElementString("PastDue90", guar.BalOver90.ToString("F2")); //optional
//Notes-----------------------------------------------------------------------------------
writer.WriteStartElement("Notes");
if (stmt.NoteBold != "")
{
writer.WriteStartElement("Note");
writer.WriteAttributeString("FgColor", "Red");
writer.WriteCData(stmt.NoteBold);
writer.WriteEndElement(); //Note
}
if (stmt.Note != "")
{
writer.WriteStartElement("Note");
writer.WriteCData(stmt.Note);
writer.WriteEndElement(); //Note
}
writer.WriteEndElement(); //Notes
//Detail items------------------------------------------------------------------------------
writer.WriteStartElement("DetailItems");
//string note;
string descript;
string fulldesc;
string procCode;
string tth;
//string linedesc;
string[] lineArray;
List <string> lines;
DateTime date;
int seq = 0;
for (int i = 0; i < tableAccount.Rows.Count; i++)
{
procCode = tableAccount.Rows[i]["ProcCode"].ToString();
tth = tableAccount.Rows[i]["tth"].ToString();
descript = tableAccount.Rows[i]["description"].ToString();
fulldesc = procCode + " " + tth + " " + descript;
lineArray = fulldesc.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
lines = new List <string>(lineArray);
//The specs say that the line limit is 30 char. But in testing, it will take 50 char.
//We will use 40 char to be safe.
if (lines[0].Length > 40)
{
string newline = lines[0].Substring(40);
lines[0] = lines[0].Substring(0, 40); //first half
lines.Insert(1, newline); //second half
}
for (int li = 0; li < lines.Count; li++)
{
writer.WriteStartElement("DetailItem"); //has a child item. We won't add optional child note
writer.WriteAttributeString("sequence", seq.ToString());
writer.WriteStartElement("Item");
if (li == 0)
{
date = (DateTime)tableAccount.Rows[i]["DateTime"];
writer.WriteElementString("Date", date.ToString("MM/dd/yyyy"));
writer.WriteElementString("PatientName", tableAccount.Rows[i]["patient"].ToString());
}
else
{
writer.WriteElementString("Date", "");
writer.WriteElementString("PatientName", "");
}
writer.WriteElementString("Description", lines[li]);
if (li == 0)
{
writer.WriteElementString("Charges", tableAccount.Rows[i]["charges"].ToString());
writer.WriteElementString("Credits", tableAccount.Rows[i]["credits"].ToString());
writer.WriteElementString("Balance", tableAccount.Rows[i]["balance"].ToString());
}
else
{
writer.WriteElementString("Charges", "");
writer.WriteElementString("Credits", "");
writer.WriteElementString("Balance", "");
}
writer.WriteEndElement(); //Item
writer.WriteEndElement(); //DetailItem
seq++;
}
}
writer.WriteEndElement(); //DetailItems
writer.WriteEndElement(); //Statement
}
///<summary>Updates one InstallmentPlan in the database.</summary>
public static void Update(InstallmentPlan installmentPlan){
string command="UPDATE installmentplan SET "
+"PatNum = "+POut.Long (installmentPlan.PatNum)+", "
+"DateAgreement = "+POut.Date (installmentPlan.DateAgreement)+", "
+"DateFirstPayment = "+POut.Date (installmentPlan.DateFirstPayment)+", "
+"MonthlyPayment = '"+POut.Double(installmentPlan.MonthlyPayment)+"', "
+"APR = "+POut.Float (installmentPlan.APR)+", "
+"Note = '"+POut.String(installmentPlan.Note)+"' "
+"WHERE InstallmentPlanNum = "+POut.Long(installmentPlan.InstallmentPlanNum);
Db.NonQ(command);
}
///<summary>Updates one InstallmentPlan in the database. Uses an old object to compare to, and only alters changed fields. This prevents collisions and concurrency problems in heavily used tables. Returns true if an update occurred.</summary>
public static bool Update(InstallmentPlan installmentPlan, InstallmentPlan oldInstallmentPlan)
{
string command = "";
if (installmentPlan.PatNum != oldInstallmentPlan.PatNum)
{
if (command != "")
{
command += ",";
}
command += "PatNum = " + POut.Long(installmentPlan.PatNum) + "";
}
if (installmentPlan.DateAgreement.Date != oldInstallmentPlan.DateAgreement.Date)
{
if (command != "")
{
command += ",";
}
command += "DateAgreement = " + POut.Date(installmentPlan.DateAgreement) + "";
}
if (installmentPlan.DateFirstPayment.Date != oldInstallmentPlan.DateFirstPayment.Date)
{
if (command != "")
{
command += ",";
}
command += "DateFirstPayment = " + POut.Date(installmentPlan.DateFirstPayment) + "";
}
if (installmentPlan.MonthlyPayment != oldInstallmentPlan.MonthlyPayment)
{
if (command != "")
{
command += ",";
}
command += "MonthlyPayment = '" + POut.Double(installmentPlan.MonthlyPayment) + "'";
}
if (installmentPlan.APR != oldInstallmentPlan.APR)
{
if (command != "")
{
command += ",";
}
command += "APR = " + POut.Float(installmentPlan.APR) + "";
}
if (installmentPlan.Note != oldInstallmentPlan.Note)
{
if (command != "")
{
command += ",";
}
command += "Note = '" + POut.String(installmentPlan.Note) + "'";
}
if (command == "")
{
return(false);
}
command = "UPDATE installmentplan SET " + command
+ " WHERE InstallmentPlanNum = " + POut.Long(installmentPlan.InstallmentPlanNum);
Db.NonQ(command);
return(true);
}
///<summary>Updates one InstallmentPlan in the database. Uses an old object to compare to, and only alters changed fields. This prevents collisions and concurrency problems in heavily used tables. Returns true if an update occurred.</summary>
public static bool Update(InstallmentPlan installmentPlan,InstallmentPlan oldInstallmentPlan){
string command="";
if(installmentPlan.PatNum != oldInstallmentPlan.PatNum) {
if(command!=""){ command+=",";}
command+="PatNum = "+POut.Long(installmentPlan.PatNum)+"";
}
if(installmentPlan.DateAgreement != oldInstallmentPlan.DateAgreement) {
if(command!=""){ command+=",";}
command+="DateAgreement = "+POut.Date(installmentPlan.DateAgreement)+"";
}
if(installmentPlan.DateFirstPayment != oldInstallmentPlan.DateFirstPayment) {
if(command!=""){ command+=",";}
command+="DateFirstPayment = "+POut.Date(installmentPlan.DateFirstPayment)+"";
}
if(installmentPlan.MonthlyPayment != oldInstallmentPlan.MonthlyPayment) {
if(command!=""){ command+=",";}
command+="MonthlyPayment = '"+POut.Double(installmentPlan.MonthlyPayment)+"'";
}
if(installmentPlan.APR != oldInstallmentPlan.APR) {
if(command!=""){ command+=",";}
command+="APR = "+POut.Float(installmentPlan.APR)+"";
}
if(installmentPlan.Note != oldInstallmentPlan.Note) {
if(command!=""){ command+=",";}
command+="Note = '"+POut.String(installmentPlan.Note)+"'";
}
if(command==""){
return false;
}
command="UPDATE installmentplan SET "+command
+" WHERE InstallmentPlanNum = "+POut.Long(installmentPlan.InstallmentPlanNum);
Db.NonQ(command);
return true;
}
///<summary>Inserts one InstallmentPlan into the database. Returns the new priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(InstallmentPlan installmentPlan)
{
return(InsertNoCache(installmentPlan, false));
}
///<summary>Inserts one InstallmentPlan into the database. Provides option to use the existing priKey.</summary>
public static long Insert(InstallmentPlan installmentPlan,bool useExistingPK){
if(!useExistingPK && PrefC.RandomKeys) {
installmentPlan.InstallmentPlanNum=ReplicationServers.GetKey("installmentplan","InstallmentPlanNum");
}
string command="INSERT INTO installmentplan (";
if(useExistingPK || PrefC.RandomKeys) {
command+="InstallmentPlanNum,";
}
command+="PatNum,DateAgreement,DateFirstPayment,MonthlyPayment,APR,Note) VALUES(";
if(useExistingPK || PrefC.RandomKeys) {
command+=POut.Long(installmentPlan.InstallmentPlanNum)+",";
}
command+=
POut.Long (installmentPlan.PatNum)+","
+ POut.Date (installmentPlan.DateAgreement)+","
+ POut.Date (installmentPlan.DateFirstPayment)+","
+"'"+POut.Double(installmentPlan.MonthlyPayment)+"',"
+ POut.Float (installmentPlan.APR)+","
+"'"+POut.String(installmentPlan.Note)+"')";
if(useExistingPK || PrefC.RandomKeys) {
Db.NonQ(command);
}
else {
installmentPlan.InstallmentPlanNum=Db.NonQ(command,true);
}
return installmentPlan.InstallmentPlanNum;
}
