///<summary>Returns the patient's clinic based on the recall passed in.
///If the patient is no longer associated to a clinic,
/// returns the clinic associated to the appointment (scheduled or completed) with the largest date.
///Returns null if the patient doesn't have a clinic or if the clinics feature is not activate.</summary>
public static Clinic GetClinicForRecall(long recallNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <Clinic>(MethodBase.GetCurrentMethod(), recallNum));
}
if (PrefC.GetBool(PrefName.EasyNoClinics))
{
return(null);
}
string command = "SELECT patient.ClinicNum FROM patient "
+ "INNER JOIN recall ON patient.PatNum=recall.PatNum "
+ "WHERE recall.RecallNum=" + POut.Long(recallNum) + " "
+ DbHelper.LimitAnd(1);
long patientClinicNum = PIn.Long(DataCore.GetScalar(command));
if (patientClinicNum > 0)
{
return(GetFirstOrDefault(x => x.ClinicNum == patientClinicNum));
}
//Patient does not have an assigned clinic. Grab the clinic from a scheduled or completed appointment with the largest date.
command = @"SELECT appointment.ClinicNum,appointment.AptDateTime
FROM appointment
INNER JOIN recall ON appointment.PatNum=recall.PatNum AND recall.RecallNum=" + POut.Long(recallNum) + @"
WHERE appointment.AptStatus IN (" + POut.Int((int)ApptStatus.Scheduled) + "," + POut.Int((int)ApptStatus.Complete) + ")" + @"
ORDER BY AptDateTime DESC" ;
command = DbHelper.LimitOrderBy(command, 1);
long appointmentClinicNum = PIn.Long(DataCore.GetScalar(command));
if (appointmentClinicNum > 0)
{
return(GetFirstOrDefault(x => x.ClinicNum == appointmentClinicNum));
}
return(null);
}
///<summary>Checks dependencies first. Throws exception if can't delete.</summary>
public static void Delete(long laboratoryNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), laboratoryNum);
return;
}
string command;
//check lab cases for dependencies
command = "SELECT LName,FName FROM patient,labcase "
+ "WHERE patient.PatNum=labcase.PatNum "
+ "AND LaboratoryNum =" + POut.Long(laboratoryNum) + " "
+ DbHelper.LimitAnd(30);
DataTable table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string pats = "";
for (int i = 0; i < table.Rows.Count; i++)
{
pats += "\r";
pats += table.Rows[i][0].ToString() + ", " + table.Rows[i][1].ToString();
}
throw new Exception(Lans.g("Laboratories", "Cannot delete Laboratory because cases exist for") + pats);
}
//delete
command = "DELETE FROM laboratory WHERE LaboratoryNum = " + POut.Long(laboratoryNum);
Db.NonQ(command);
}
///<summary> Called when cancelling out of creating a new claimform, and from the claimform window when clicking delete. Returns true if successful or false if dependencies found.</summary>
public static bool Delete(ClaimForm cf)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), cf));
}
//first, do dependency testing
string command = "SELECT * FROM insplan WHERE claimformnum = '"
+ cf.ClaimFormNum.ToString() + "' ";
command += DbHelper.LimitAnd(1);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 1)
{
return(false);
}
//Then, delete the claimform
command = "DELETE FROM claimform "
+ "WHERE ClaimFormNum = '" + POut.Long(cf.ClaimFormNum) + "'";
Db.NonQ(command);
command = "DELETE FROM claimformitem "
+ "WHERE ClaimFormNum = '" + POut.Long(cf.ClaimFormNum) + "'";
Db.NonQ(command);
return(true);
}
///<summary>Gets one OrionProc from the db by ProcNum</summary>
public static OrionProc GetOneByProcNum(long ProcNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <OrionProc>(MethodBase.GetCurrentMethod(), ProcNum));
}
string command = "SELECT * FROM orionproc "
+ "WHERE ProcNum=" + POut.Long(ProcNum) + " " + DbHelper.LimitAnd(1);
return(Crud.OrionProcCrud.SelectOne(command));
}
///<summary>Called from FormInsPlan when user wants to view a benefit note for other subscribers on a plan. Should never include the current subscriber that the user is editing. This function will get one note from the database, not including blank notes. If no note can be found, then it returns empty string.</summary>
public static string GetBenefitNotes(long planNum, long subNumExclude)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetString(MethodBase.GetCurrentMethod(), planNum, subNumExclude));
}
string command = "SELECT BenefitNotes FROM inssub WHERE BenefitNotes != '' AND PlanNum=" + POut.Long(planNum) + " AND InsSubNum !=" + POut.Long(subNumExclude) + " " + DbHelper.LimitAnd(1);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return("");
}
return(PIn.String(table.Rows[0][0].ToString()));
}
public static List <HL7Msg> GetOnePending()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <HL7Msg> >(MethodBase.GetCurrentMethod()));
}
string command = "SELECT * FROM hl7msg WHERE HL7Status=" + POut.Long((int)HL7MessageStatus.OutPending) + " " + DbHelper.LimitAnd(1);
return(Crud.HL7MsgCrud.SelectMany(command)); //Just 0 or 1 item in list for now.
}
public static List <HL7Msg> GetOneExisting(HL7Msg hl7Msg)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <HL7Msg> >(MethodBase.GetCurrentMethod(), hl7Msg));
}
//Might want to change the following query to:
//string command="SELECT * FROM hl7msg WHERE HL7Status IN("+POut.Long((int)HL7MessageStatus.InProcessed)+","+POut.Long((int)HL7MessageStatus.OutSent)
//+") AND MsgText='"+POut.String(hl7Msg.MsgText)+"' "+DbHelper.LimitAnd(1);
string command = "SELECT * FROM hl7msg WHERE MsgText='" + POut.String(hl7Msg.MsgText) + "' " + DbHelper.LimitAnd(1);
return(Crud.HL7MsgCrud.SelectMany(command)); //Just 0 or 1 item in list for now.
}
///<summary>Computes aging for the family specified. Specify guarantor=0 in order to calculate aging for all families.
///Gets all info from database.
///The aging calculation will use the following rules within each family:
///1) The aging "buckets" (0 to 30, 31 to 60, 61 to 90 and Over 90) ONLY include account activity on or
///before AsOfDate.
///2) BalTotal will always include all account activity, even future entries, except when in historical
///mode, where BalTotal will exclude account activity after AsOfDate.
///3) InsEst will always include all insurance estimates, even future estimates, except when in
///historical mode where InsEst excludes insurance estimates after AsOfDate.
///4) PayPlanDue will always include all payment plan charges minus credits, except when in
///historical mode where PayPlanDue excludes payment plan charges and payments after AsOfDate.</summary>
public static void ComputeAging(long guarantor, DateTime AsOfDate, bool historic)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), guarantor, AsOfDate, historic);
return;
}
//Zero out either entire database or entire family.
//Need to zero everything out first to catch former guarantors.
string command = "UPDATE patient SET "
+ "Bal_0_30 = 0"
+ ",Bal_31_60 = 0"
+ ",Bal_61_90 = 0"
+ ",BalOver90 = 0"
+ ",InsEst = 0"
+ ",BalTotal = 0"
+ ",PayPlanDue= 0";
if (guarantor != 0)
{
command += " WHERE Guarantor=" + POut.Long(guarantor);
}
Db.NonQ(command);
if (AsOfDate.Year < 1880)
{
AsOfDate = DateTime.Today;
}
string asOfDate = POut.Date(AsOfDate);
string billInAdvanceDate = POut.Date(AsOfDate.AddDays(PrefC.GetLong(PrefName.PayPlansBillInAdvanceDays)));
if (historic)
{
billInAdvanceDate = POut.Date(DateTime.Today.AddDays(PrefC.GetLong(PrefName.PayPlansBillInAdvanceDays)));
}
string thirtyDaysAgo = POut.Date(AsOfDate.AddDays(-30));
string sixtyDaysAgo = POut.Date(AsOfDate.AddDays(-60));
string ninetyDaysAgo = POut.Date(AsOfDate.AddDays(-90));
string familyPatNums = "";
Collection <string> familyPatNumList = new Collection <string> ();
if (guarantor != 0)
{
familyPatNums = "(";
command = "SELECT p.PatNum FROM patient p WHERE p.Guarantor=" + guarantor;
DataTable tFamilyPatNums = Db.GetTable(command);
for (int i = 0; i < tFamilyPatNums.Rows.Count; i++)
{
if (i > 0)
{
familyPatNums += ",";
}
string patNum = tFamilyPatNums.Rows[i][0].ToString();
familyPatNums += patNum;
familyPatNumList.Add(patNum);
}
familyPatNums += ")";
}
//We use temporary tables using the "CREATE TEMPORARY TABLE" syntax here so that any temporary
//tables created are specific to the current database connection and no actual files are created
//in the database. This will prevent rogue files from collecting in the live database, and will
//prevent aging calculations on one computer from affecting the aging calculations on another computer.
//Unfortunately, this has one side effect, which is that our connector reopens the
//connection every time a command is run, so the temporary tables only last for a single
//command. To get around this issue, we run the aging script as a single command/script.
//Unfortunately, the "CREATE TEMPORARY TABLE" syntax gets replicated if MySQL replication is enabled,
//which becomes a problem becauase the command is then no longer connection specific. Therefore,
//to accomodate to the few offices using database replication with MySQL, when creating the temporary aging tables,
//we append a random string to the temporary table names so the possibility to temporary table
//name collision is practically zero.
//Create a temporary table to calculate aging into temporarily, so that the patient table is
//not being changed by multiple threads if more than one user is calculating aging.
//Since a temporary table is dropped automatically only when the connection is closed,
//and since we use connection pooling, drop them before using.
string tempTableSuffix = CodeBase.MiscUtils.CreateRandomAlphaNumericString(14);//max size for a table name in oracle is 30 chars.
string tempAgingTableName = "tempaging" + tempTableSuffix;
string tempOdAgingTransTableName = "tempodagingtrans" + tempTableSuffix;
if (DataConnection.DBtype == DatabaseType.Oracle)
{
try {
//We would use DROP TEMPORARY TABLE IF EXISTS syntax here but no such syntax exists in Oracle.
command = "DROP TEMPORARY TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
}
catch {
//The tables do not exist. Nothing to do.
}
try {
//We would use DROP TABLE IF EXISTS syntax here but no such syntax exists in Oracle.
command = "DROP TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
}
catch {
//The tables do not exist. Nothing to do.
}
}
else
{
command = "DROP TEMPORARY TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
command = "DROP TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
}
if (DataConnection.DBtype == DatabaseType.Oracle)
{
command = "CREATE GLOBAL TEMPORARY TABLE " + tempAgingTableName + " (" +
"PatNum NUMBER," +
"Guarantor NUMBER," +
"Charges_0_30 NUMBER(38,8) DEFAULT 0," +
"Charges_31_60 NUMBER(38,8) DEFAULT 0," +
"Charges_61_90 NUMBER(38,8) DEFAULT 0," +
"ChargesOver90 NUMBER(38,8) DEFAULT 0," +
"TotalCredits NUMBER(38,8) DEFAULT 0," +
"InsEst NUMBER(38,8) DEFAULT 0," +
"PayPlanDue NUMBER(38,8) DEFAULT 0," +
"BalTotal NUMBER(38,8) DEFAULT 0" +
");";
}
else
{
command = "CREATE TEMPORARY TABLE " + tempAgingTableName + " (" +
"PatNum bigint," +
"Guarantor bigint," +
"Charges_0_30 DOUBLE DEFAULT 0," +
"Charges_31_60 DOUBLE DEFAULT 0," +
"Charges_61_90 DOUBLE DEFAULT 0," +
"ChargesOver90 DOUBLE DEFAULT 0," +
"TotalCredits DOUBLE DEFAULT 0," +
"InsEst DOUBLE DEFAULT 0," +
"PayPlanDue DOUBLE DEFAULT 0," +
"BalTotal DOUBLE DEFAULT 0" +
");";
}
if (guarantor == 0)
{
//We insert all of the patient numbers and guarantor numbers only when we are running aging for everyone,
//since we do not want to examine every patient record when running aging for a single family.
command += "INSERT INTO " + tempAgingTableName + " (PatNum,Guarantor) " +
"SELECT p.PatNum,p.Guarantor " +
"FROM patient p;";
//When there is only one patient that aging is being calculated for, then the indexes actually
//slow the calculation down slightly, but they significantly improve the speed when aging is being
//calculated for all familes.
if (DataConnection.DBtype == DatabaseType.Oracle)
{
command += "CREATE INDEX " + tempAgingTableName.ToUpper() + "_PATNUM ON " + tempAgingTableName + " (PatNum);";
command += "CREATE INDEX " + tempAgingTableName.ToUpper() + "_GUAR ON " + tempAgingTableName + " (Guarantor);";
}
else
{
command += "ALTER TABLE " + tempAgingTableName + " ADD INDEX IDX_" + tempAgingTableName.ToUpper() + "_PATNUM (PatNum);";
command += "ALTER TABLE " + tempAgingTableName + " ADD INDEX IDX_" + tempAgingTableName.ToUpper() + "_GUARANTOR (Guarantor);";
}
}
else
{
//Manually create insert statements to avoid having the database system visit every patient record again.
//In my testing, this saves about 0.25 seconds on an individual family aging calculation on my machine in MySQL.
command += "INSERT INTO " + tempAgingTableName + " (PatNum,Guarantor) VALUES ";
for (int i = 0; i < familyPatNumList.Count; i++)
{
if (i > 0)
{
command += ",";
}
command += "(" + familyPatNumList[i] + "," + guarantor + ")";
}
command += ";";
}
//Create another temporary table which holds a very concise summary of the entire office transaction history,
//so that all transactions can be treated as either a general credit or a general charge in the aging calculation.
//Since we are recreating a temporary table with the same name as last time aging was run,
//the old temporary table gets wiped out.
if (DataConnection.DBtype == DatabaseType.Oracle)
{
command += "CREATE GLOBAL TEMPORARY TABLE " + tempOdAgingTransTableName + " (" +
"PatNum NUMBER," +
"TranDate DATE DEFAULT TO_DATE('0001-01-01', 'yyyy-mm-dd')," +
"TranAmount NUMBER(38,8) DEFAULT 0" +
");";
}
else
{
command += "CREATE TEMPORARY TABLE " + tempOdAgingTransTableName + " (" +
"PatNum bigint," +
"TranDate DATE DEFAULT '0001-01-01'," +
"TranAmount DOUBLE DEFAULT 0" +
");";
}
//Get the completed procedure dates and charges for the entire office history.
command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " +
"SELECT pl.PatNum PatNum," +
"pl.ProcDate TranDate," +
"pl.ProcFee*(pl.UnitQty+pl.BaseUnits) TranAmount " +
"FROM procedurelog pl " +
"WHERE pl.ProcStatus=2 " +
(guarantor == 0?"":(" AND pl.PatNum IN " + familyPatNums)) + ";";
//Paysplits for the entire office history.
command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " +
"SELECT ps.PatNum PatNum," +
"ps.ProcDate TranDate," +
"-ps.SplitAmt TranAmount " +
"FROM paysplit ps " +
"WHERE ps.PayPlanNum=0 " + //Only splits not attached to payment plans.
(guarantor == 0?"":(" AND ps.PatNum IN " + familyPatNums)) + ";";
//Get the adjustment dates and amounts for the entire office history.
command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " +
"SELECT a.PatNum PatNum," +
"a.AdjDate TranDate," +
"a.AdjAmt TranAmount " +
"FROM adjustment a " +
"WHERE a.AdjAmt<>0 " +
(guarantor == 0?"":(" AND a.PatNum IN " + familyPatNums)) + ";";
//Claim payments and capitation writeoffs for the entire office history.
command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " +
"SELECT cp.PatNum PatNum," +
"cp.DateCp TranDate," + //Always use DateCP rather than ProcDate to calculate the date of a claim payment.
"-cp.InsPayAmt-cp.Writeoff TranAmount " +
"FROM claimproc cp " +
"WHERE cp.status IN (1,4,5,7) " + //received, supplemental, CapClaim or CapComplete.
(guarantor == 0?"":(" AND cp.PatNum IN " + familyPatNums)) + ";";
//Payment plan principal for the entire office history.
command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " +
"SELECT pp.PatNum PatNum," +
"pp.PayPlanDate TranDate," +
"-pp.CompletedAmt TranAmount " +
"FROM payplan pp " +
"WHERE pp.CompletedAmt<>0 " +
(guarantor == 0?"":(" AND pp.PatNum IN " + familyPatNums)) + ";";
if (DataConnection.DBtype == DatabaseType.Oracle)
{
//The aging calculation buckets, insurance estimates, and payment plan due amounts are
//not yet calculated for Oracle as they have not been needed yet. Just calculates
//account balance totals.
string tempTotalsTableName = "temptotals" + tempTableSuffix;
command += "CREATE GLOBAL TEMPORARY TABLE " + tempTotalsTableName + " (" +
"PatNum NUMBER DEFAULT 0," +
"BalTotal NUMBER(38,8) DEFAULT 0" +
");";
command += "CREATE INDEX " + tempTotalsTableName.ToUpper() + "_PATNU ON " + tempTotalsTableName + " (PatNum);";
command += "INSERT INTO " + tempTotalsTableName + " " +
"SELECT PatNum,ROUND(SUM(TranAmount),2) FROM " + tempOdAgingTransTableName +
"GROUP BY PatNum;";
command += "UPDATE patient p " +
"SET p.BalTotal=(SELECT t.BalTotal FROM " + tempTotalsTableName + " t WHERE t.PatNum=p.PatNum " + DbHelper.LimitAnd(1) + ");";
Db.NonQ(command);
}
else
{
//Now that we have all of the pertinent transaction history, we will calculate all of the charges for
//the associated patients.
//Calculate over 90 day charges for all specified families.
command += "UPDATE " + tempAgingTableName + " a," +
//Calculate the total charges for each patient during this time period and
//place the results into memory table 'chargesOver90'.
"(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " +
"WHERE t.TranAmount>0 AND t.TranDate<" + DbHelper.DateColumn(ninetyDaysAgo) + " GROUP BY t.PatNum) chargesOver90 " +
//Update the tempaging table with the caculated charges for the time period.
"SET a.ChargesOver90=chargesOver90.TotalCharges " +
"WHERE a.PatNum=chargesOver90.PatNum;";
//Calculate 61 to 90 day charges for all specified families.
command += "UPDATE " + tempAgingTableName + " a," +
//Calculate the total charges for each patient during this time period and
//place the results into memory table 'charges_61_90'.
"(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " +
"WHERE t.TranAmount>0 AND t.TranDate<" + DbHelper.DateColumn(sixtyDaysAgo) + " AND " +
"t.TranDate>=" + DbHelper.DateColumn(ninetyDaysAgo) + " GROUP BY t.PatNum) charges_61_90 " +
//Update the tempaging table with the caculated charges for the time period.
"SET a.Charges_61_90=charges_61_90.TotalCharges " +
"WHERE a.PatNum=charges_61_90.PatNum;";
//Calculate 31 to 60 day charges for all specified families.
command += "UPDATE " + tempAgingTableName + " a," +
//Calculate the total charges for each patient during this time period and
//place the results into memory table 'charges_31_60'.
"(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " +
"WHERE t.TranAmount>0 AND t.TranDate<" + DbHelper.DateColumn(thirtyDaysAgo) + " AND " +
"t.TranDate>=" + DbHelper.DateColumn(sixtyDaysAgo) + " GROUP BY t.PatNum) charges_31_60 " +
//Update the tempaging table with the caculated charges for the time period.
"SET a.Charges_31_60=charges_31_60.TotalCharges " +
"WHERE a.PatNum=charges_31_60.PatNum;";
//Calculate 0 to 30 day charges for all specified families.
command += "UPDATE " + tempAgingTableName + " a," +
//Calculate the total charges for each patient during this time period and
//place the results into memory table 'charges_0_30'.
"(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " +
"WHERE t.TranAmount>0 AND t.TranDate<=" + DbHelper.DateColumn(asOfDate) + " AND " +
"t.TranDate>=" + DbHelper.DateColumn(thirtyDaysAgo) + " GROUP BY t.PatNum) charges_0_30 " +
//Update the tempaging table with the caculated charges for the time period.
"SET a.Charges_0_30=charges_0_30.TotalCharges " +
"WHERE a.PatNum=charges_0_30.PatNum;";
//Calculate the total credits each patient has ever received so we can apply the credits to the aged charges below.
command += "UPDATE " + tempAgingTableName + " a," +
//Calculate the total credits for each patient and store the results in memory table 'credits'.
"(SELECT t.PatNum,-SUM(t.TranAmount) TotalCredits FROM " + tempOdAgingTransTableName + " t " +
"WHERE t.TranAmount<0 AND t.TranDate<=" + DbHelper.DateColumn(asOfDate) + " GROUP BY t.PatNum) credits " +
//Update the total credit for each patient into the tempaging table.
"SET a.TotalCredits=credits.TotalCredits " +
"WHERE a.PatNum=credits.PatNum;";
//Calculate claim estimates for each patient individually on or before the specified date.
command += "UPDATE " + tempAgingTableName + " a," +
//Calculate the insurance estimates for each patient and store the results into
//memory table 't'.
"(SELECT cp.PatNum,SUM(cp.InsPayEst+cp.Writeoff) InsEst " +
"FROM claimproc cp " +
"WHERE cp.PatNum<>0 " +
(historic?(" AND ((cp.Status=0 AND cp.ProcDate<=" + DbHelper.DateColumn(asOfDate) + ") OR " +
"(cp.Status=1 AND cp.DateCP>" + DbHelper.DateColumn(asOfDate) + ")) AND cp.ProcDate<=" + DbHelper.DateColumn(asOfDate) + " "):" AND cp.Status=0 ") +
(guarantor == 0?"":(" AND cp.PatNum IN " + familyPatNums + " ")) +
"GROUP BY cp.PatNum) t " + //not received claims.
//Update the tempaging table with the insurance estimates for each patient.
"SET a.InsEst=t.InsEst " +
"WHERE a.PatNum=t.PatNum;";
//Calculate the payment plan charges for each payment plan guarantor
//on or before the specified date (also considering the PayPlansBillInAdvanceDays setting).
//We cannot exclude payments made outside the specified family, since payment plan
//guarantors can be in another family.
command += "UPDATE " + tempAgingTableName + " a," +
"(SELECT ppc.Guarantor,IFNULL(SUM(ppc.Principal+ppc.Interest),0) PayPlanCharges " +
"FROM payplancharge ppc " +
"WHERE ppc.ChargeDate<=" + DbHelper.DateColumn(billInAdvanceDate) + " " + //bill in adv. date accounts for historic vs current because of how it is set above.
"GROUP BY ppc.Guarantor) c " +
"SET a.PayPlanDue=c.PayPlanCharges " +
"WHERE c.Guarantor=a.PatNum;";
//Calculate the total payments made to each payment plan
//on or before the specified date and store the results in memory table 'p'.
//We cannot exclude payments made outside the specified family, since payment plan
//guarantors can be in another family.
command += "UPDATE " + tempAgingTableName + " a," +
"(SELECT ps.PatNum,SUM(ps.SplitAmt) PayPlanPayments " +
"FROM paysplit ps " +
"WHERE ps.PayPlanNum<>0 " + //only payments attached to payment plans.
(historic?(" AND ps.ProcDate<=" + DbHelper.DateColumn(asOfDate) + " "):"") +
"GROUP BY ps.PatNum) p " +
"SET a.PayPlanDue=a.PayPlanDue-p.PayPlanPayments " +
"WHERE p.PatNum=a.PatNum;";
//Calculate the total balance for each patient.
//In historical mode, only transactions on or before AsOfDate will be included.
command += "UPDATE " + tempAgingTableName + " a," +
//Calculate the total balance for each patient and
//place the results into memory table 'totals'.
"(SELECT t.PatNum,SUM(t.TranAmount) BalTotal FROM " + tempOdAgingTransTableName + " t " +
"WHERE t.TranAmount<>0 " + (historic?(" AND t.TranDate<=" + DbHelper.DateColumn(asOfDate)):"") + " GROUP BY t.PatNum) totals " +
//Update the tempaging table with the caculated charges for the time period.
"SET a.BalTotal=totals.BalTotal " +
"WHERE a.PatNum=totals.PatNum;";
//Update the family aged balances onto the guarantor rows of the patient table
//by placing credits on oldest charges first, then on younger charges.
command += "UPDATE patient p," +
//Sum each colum within each family group inside of the tempaging table so that we are now
//using family amounts instead of individual patient amounts, and store the result into
//memory table 'f'.
"(SELECT a.Guarantor,SUM(a.Charges_0_30) Charges_0_30,SUM(a.Charges_31_60) Charges_31_60," +
"SUM(a.Charges_61_90) Charges_61_90,SUM(a.ChargesOver90) ChargesOver90," +
"SUM(TotalCredits) TotalCredits,SUM(InsEst) InsEst,SUM(PayPlanDue) PayPlanDue," +
"SUM(BalTotal) BalTotal " +
"FROM " + tempAgingTableName + " a " +
"GROUP BY a.Guarantor) f " +
//Perform the update of the patient table based on the family amounts summed into table 'f', and
//distribute the payments into the oldest balances first.
"SET " +
"p.BalOver90=ROUND((CASE " +
//over 90 balance paid in full.
"WHEN f.TotalCredits>=f.ChargesOver90 THEN 0 " +
//over 90 balance partially paid or unpaid.
"ELSE f.ChargesOver90-f.TotalCredits END),2)," +
"p.Bal_61_90=ROUND((CASE " +
//61 to 90 day balance unpaid.
"WHEN f.TotalCredits<=f.ChargesOver90 THEN f.Charges_61_90 " +
//61 to 90 day balance paid in full.
"WHEN f.ChargesOver90+f.Charges_61_90<=f.TotalCredits THEN 0 " +
//61 to 90 day balance partially paid.
"ELSE f.ChargesOver90+f.Charges_61_90-f.TotalCredits END),2)," +
"p.Bal_31_60=ROUND((CASE " +
//31 to 60 day balance unpaid.
"WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90 THEN f.Charges_31_60 " +
//31 to 60 day balance paid in full.
"WHEN f.ChargesOver90+f.Charges_61_90+f.Charges_31_60<=f.TotalCredits THEN 0 " +
//31 to 60 day balance partially paid.
"ELSE f.ChargesOver90+f.Charges_61_90+f.Charges_31_60-f.TotalCredits END),2)," +
"p.Bal_0_30=ROUND((CASE " +
//0 to 30 day balance unpaid.
"WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90+f.Charges_31_60 THEN f.Charges_0_30 " +
//0 to 30 day balance paid in full.
"WHEN f.ChargesOver90+f.Charges_61_90+f.Charges_31_60+f.Charges_0_30<=f.TotalCredits THEN 0 " +
//0 to 30 day balance partially paid.
"ELSE f.ChargesOver90+f.Charges_61_90+f.Charges_31_60+f.Charges_0_30-f.TotalCredits END),2)," +
"p.BalTotal=ROUND(f.BalTotal,2)," +
"p.InsEst=ROUND(f.InsEst,2)," +
"p.PayPlanDue=ROUND(f.PayPlanDue,2) " +
"WHERE p.PatNum=f.Guarantor;"; //Aging calculations only apply to guarantors.
Db.NonQ(command);
}
if (DataConnection.DBtype == DatabaseType.Oracle)
{
try {
//We would use DROP TEMPORARY TABLE IF EXISTS syntax here but no such syntax exists in Oracle.
command = "DROP TEMPORARY TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
}
catch {
//The tables do not exist. Nothing to do.
}
try {
//We would use DROP TABLE IF EXISTS syntax here but no such syntax exists in Oracle.
command = "DROP TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
}
catch {
//The tables do not exist. Nothing to do.
}
}
else
{
command = "DROP TEMPORARY TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
command = "DROP TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
}
}
public static List <HL7Msg> GetOneExisting(HL7Msg hl7Msg)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <HL7Msg> >(MethodBase.GetCurrentMethod(), hl7Msg));
}
string command = "SELECT * FROM hl7msg WHERE MsgText='" + POut.String(hl7Msg.MsgText) + "' " + DbHelper.LimitAnd(1);
return(Crud.HL7MsgCrud.SelectMany(command)); //Just 0 or 1 item in list for now.
}
/// <summary>Will throw an exception if this InsSub is being used anywhere. Set strict true to test against every check.</summary>
public static void ValidateNoKeys(long subNum, bool strict)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), subNum, strict);
return;
}
string command = "SELECT 1 FROM claim WHERE InsSubNum=" + POut.Long(subNum) + " OR InsSubNum2=" + POut.Long(subNum) + " " + DbHelper.LimitAnd(1);
if (!string.IsNullOrEmpty(Db.GetScalar(command)))
{
throw new ApplicationException(Lans.g("FormInsPlan", "Subscriber has existing claims and so the subscriber cannot be deleted."));
}
if (strict)
{
command = "SELECT 1 FROM claimproc WHERE InsSubNum=" + POut.Long(subNum) + " AND Status!=" + POut.Int((int)ClaimProcStatus.Estimate) + " " + DbHelper.LimitAnd(1); //ignore estimates
if (!string.IsNullOrEmpty(Db.GetScalar(command)))
{
throw new ApplicationException(Lans.g("FormInsPlan", "Subscriber has existing claim procedures and so the subscriber cannot be deleted."));
}
}
command = "SELECT 1 FROM etrans WHERE InsSubNum=" + POut.Long(subNum) + " " + DbHelper.LimitAnd(1);
if (!string.IsNullOrEmpty(Db.GetScalar(command)))
{
throw new ApplicationException(Lans.g("FormInsPlan", "Subscriber has existing etrans entry and so the subscriber cannot be deleted."));
}
command = "SELECT 1 FROM payplan WHERE InsSubNum=" + POut.Long(subNum) + " " + DbHelper.LimitAnd(1);
if (!string.IsNullOrEmpty(Db.GetScalar(command)))
{
throw new ApplicationException(Lans.g("FormInsPlan", "Subscriber has existing insurance linked payment plans and so the subscriber cannot be deleted."));
}
}
