///<summary>If not using clinics, or for all clinics with clinics enabled, supply an empty list of clinicNums. If the user is restricted, for all
///clinics supply only those clinics for which the user has permission to access, otherwise it will be run for all clinics.</summary>
public static DataTable GetPatTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, List <long> listPatientTypes,
bool hasAllProvs, bool hasAllClinics, bool hasPatientTypes, bool isGroupedByPatient, bool isUnearnedIncluded, bool doShowProvSeparate)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listPatientTypes, hasAllProvs, hasAllClinics,
hasPatientTypes, isGroupedByPatient, isUnearnedIncluded, doShowProvSeparate));
}
//reports should no longer use the cache
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)));
//patient payments-----------------------------------------------------------------------------------------
//the selected columns have to remain in this order due to the way the report complex populates the returned sheet
string queryPat = "SELECT payment.PayDate DatePay,"
+ "MAX(" + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ") lfname,GROUP_CONCAT(DISTINCT provider.Abbr),";
if (hasClinicsEnabled)
{
queryPat += "clinic.Description clinicDesc,";
}
queryPat += "payment.CheckNum,SUM(COALESCE(paysplit.SplitAmt,0)) amt,payment.PayNum,ItemName,payment.PayType "
+ "FROM payment "
+ "LEFT JOIN paysplit ON payment.PayNum=paysplit.PayNum "
+ "LEFT JOIN patient ON payment.PatNum=patient.PatNum "
+ "LEFT JOIN provider ON paysplit.ProvNum=provider.ProvNum "
+ "LEFT JOIN definition ON payment.PayType=definition.DefNum ";
if (hasClinicsEnabled)
{
queryPat += "LEFT JOIN clinic ON clinic.ClinicNum=paysplit.ClinicNum ";
}
queryPat += "WHERE payment.PayDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " ";
if (hasClinicsEnabled && listClinicNums.Count > 0)
{
queryPat += "AND paysplit.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
if (!hasAllProvs && listProvNums.Count > 0)
{
queryPat += "AND paysplit.ProvNum IN(" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") ";
}
if (!hasPatientTypes && listPatientTypes.Count > 0)
{
queryPat += "AND payment.PayType IN (" + string.Join(",", listPatientTypes.Select(x => POut.Long(x))) + ") ";
}
if (!isUnearnedIncluded) //UnearnedType of 0 means the paysplit is NOT unearned
{
queryPat += "AND paysplit.UnearnedType=0 ";
}
queryPat += "GROUP BY payment.PayNum,payment.PayDate,payment.CheckNum,definition.ItemName,payment.PayType ";
if (doShowProvSeparate)
{
queryPat += ",provider.ProvNum ";
}
if (hasClinicsEnabled)
{
queryPat += ",clinic.Description ";
}
if (isGroupedByPatient)
{
queryPat += ",patient.PatNum ";
}
queryPat += "ORDER BY payment.PayType,payment.PayDate,lfname";
if (!hasPatientTypes && listPatientTypes.Count == 0)
{
queryPat = DbHelper.LimitOrderBy(queryPat, 0);
}
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryPat)));
}
///<summary>Gets data for the history grid in the SendClaims window. The listEtransType must contain as least one item.</summary>
public static DataTable RefreshHistory(DateTime dateFrom,DateTime dateTo,List<EtransType> listEtransType) {
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetTable(MethodBase.GetCurrentMethod(),dateFrom,dateTo,listEtransType);
}
string command="SELECT (CASE WHEN etrans.PatNum=0 THEN etrans.PatientNameRaw "
+"ELSE CONCAT(CONCAT(patient.LName,', '),patient.FName) END) AS PatName,"
+"(CASE WHEN etrans.carrierNum=0 THEN etrans.CarrierNameRaw ELSE carrier.CarrierName END) AS CarrierName,"
+"clearinghouse.Description AS Clearinghouse,DateTimeTrans,etrans.OfficeSequenceNumber,"
+"etrans.CarrierTransCounter,Etype,etrans.ClaimNum,etrans.EtransNum,etrans.AckCode,etrans.Note,etrans.EtransMessageTextNum,etrans.TranSetId835,"
+"etrans.UserNum,etrans.PatNum "
+"FROM etrans "
+"LEFT JOIN carrier ON etrans.CarrierNum=carrier.CarrierNum "
+"LEFT JOIN patient ON patient.PatNum=etrans.PatNum "
+"LEFT JOIN clearinghouse ON clearinghouse.ClearinghouseNum=etrans.ClearinghouseNum WHERE "
+DbHelper.DtimeToDate("DateTimeTrans")+" >= "+POut.Date(dateFrom)+" AND "
+DbHelper.DtimeToDate("DateTimeTrans")+" <= "+POut.Date(dateTo)+" "
+"AND Etype IN ("+POut.Long((int)listEtransType[0]);
for(int i=1;i<listEtransType.Count;i++){//String.Join doesn't work because there's no way to cast the enums to ints in the function, db uses longs.
command+=", "+POut.Long((int)listEtransType[i]);
}
command+=") "
//For Canada, when the undo button is used from Manage | Send Claims, the ClaimNum is set to 0 instead of deleting the etrans entry.
//For transaction types related to claims where the claimnum=0, we do not want them to show in the history section of Manage | Send Claims because they have been undone.
+"AND (ClaimNum<>0 OR Etype NOT IN ("+POut.Long((int)EtransType.Claim_CA)+","+POut.Long((int)EtransType.ClaimCOB_CA)+","+POut.Long((int)EtransType.Predeterm_CA)+","+POut.Long((int)EtransType.ClaimReversal_CA)+")) "
+"ORDER BY DateTimeTrans";
DataTable table=Db.GetTable(command);
DataTable tHist=new DataTable("Table");
tHist.Columns.Add("patName");
tHist.Columns.Add("CarrierName");
tHist.Columns.Add("Clearinghouse");
tHist.Columns.Add("dateTimeTrans");
tHist.Columns.Add("OfficeSequenceNumber");
tHist.Columns.Add("CarrierTransCounter");
tHist.Columns.Add("etype");
tHist.Columns.Add("Etype");
tHist.Columns.Add("ClaimNum");
tHist.Columns.Add("EtransNum");
tHist.Columns.Add("ack");
tHist.Columns.Add("Note");
tHist.Columns.Add("EtransMessageTextNum");
tHist.Columns.Add("TranSetId835");
tHist.Columns.Add("UserNum");
tHist.Columns.Add("PatNum");
DataRow row;
string etype;
for(int i=0;i<table.Rows.Count;i++) {
row=tHist.NewRow();
row["patName"]=table.Rows[i]["PatName"].ToString();
row["CarrierName"]=table.Rows[i]["CarrierName"].ToString();
row["Clearinghouse"]=table.Rows[i]["Clearinghouse"].ToString();
row["dateTimeTrans"]=PIn.DateT(table.Rows[i]["DateTimeTrans"].ToString()).ToShortDateString();
row["OfficeSequenceNumber"]=table.Rows[i]["OfficeSequenceNumber"].ToString();
row["CarrierTransCounter"]=table.Rows[i]["CarrierTransCounter"].ToString();
row["Etype"]=table.Rows[i]["Etype"].ToString();
etype=Lans.g("enumEtransType",((EtransType)PIn.Long(table.Rows[i]["Etype"].ToString())).ToString());
if(etype.EndsWith("_CA")){
etype=etype.Substring(0,etype.Length-3);
}
row["etype"]=etype;
row["ClaimNum"]=table.Rows[i]["ClaimNum"].ToString();
row["EtransNum"]=table.Rows[i]["EtransNum"].ToString();
if(table.Rows[i]["AckCode"].ToString()=="A"){
row["ack"]=Lans.g("Etrans","Accepted");
}
else if(table.Rows[i]["AckCode"].ToString()=="R") {
row["ack"]=Lans.g("Etrans","Rejected");
}
else if(table.Rows[i]["AckCode"].ToString()=="Recd") {
row["ack"]=Lans.g("Etrans","Received");
}
else {
row["ack"]="";
}
row["Note"]=table.Rows[i]["Note"].ToString();
row["EtransMessageTextNum"]=table.Rows[i]["EtransMessageTextNum"].ToString();
row["TranSetId835"]=table.Rows[i]["TranSetId835"].ToString();
row["UserNum"]=table.Rows[i]["UserNum"].ToString();
row["PatNum"]=table.Rows[i]["PatNum"].ToString();
tHist.Rows.Add(row);
}
return tHist;
}
///<summary>If not using clinics then supply an empty list of clinicNums. listClinicNums must have at least one item if using clinics.</summary>
public static DataTable GetInsTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums,
List <long> listInsuranceTypes, List <long> listClaimPayGroups, bool hasAllProvs, bool hasAllClinics, bool hasInsuranceTypes, bool isGroupedByPatient,
bool hasAllClaimPayGroups, bool doShowProvSeparate)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listInsuranceTypes, listClaimPayGroups,
hasAllProvs, hasAllClinics, hasInsuranceTypes, isGroupedByPatient, hasAllClaimPayGroups, doShowProvSeparate));
}
string whereProv = "";
if (!hasAllProvs)
{
whereProv += " AND claimproc.ProvNum IN(";
for (int i = 0; i < listProvNums.Count; i++)
{
if (i > 0)
{
whereProv += ",";
}
whereProv += POut.Long(listProvNums[i]);
}
whereProv += ") ";
}
string whereClin = "";
//reports should no longer use the cache
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)));
if (hasClinicsEnabled)
{
whereClin += " AND claimproc.ClinicNum IN(";
for (int i = 0; i < listClinicNums.Count; i++)
{
if (i > 0)
{
whereClin += ",";
}
whereClin += POut.Long(listClinicNums[i]);
}
whereClin += ") ";
}
string whereClaimPayGroup = "";
if (!hasAllClaimPayGroups)
{
whereClaimPayGroup = " AND PayGroup IN (" + String.Join(",", listClaimPayGroups) + ") ";
}
string queryIns =
@"SELECT claimproc.DateCP,carrier.CarrierName,MAX("
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + @") lfname,GROUP_CONCAT(DISTINCT provider.Abbr) Provider, ";
if (hasClinicsEnabled)
{
queryIns += "clinic.Description clinicDesc, ";
}
queryIns += @"claimpayment.CheckNum,SUM(claimproc.InsPayAmt) amt,claimproc.ClaimNum,claimpayment.PayType
FROM claimproc
LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum
LEFT JOIN patient ON claimproc.PatNum = patient.PatNum
LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
LEFT JOIN provider ON provider.ProvNum=claimproc.ProvNum
LEFT JOIN claimpayment ON claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum " ;
if (hasClinicsEnabled)
{
queryIns += "LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum ";
}
queryIns += "WHERE (claimproc.Status=1 OR claimproc.Status=4) " //received or supplemental
+ whereProv
+ whereClin
+ whereClaimPayGroup
+ "AND claimpayment.CheckDate >= " + POut.Date(dateFrom) + " "
+ "AND claimpayment.CheckDate <= " + POut.Date(dateTo) + " ";
if (!hasInsuranceTypes && listInsuranceTypes.Count > 0)
{
queryIns += "AND claimpayment.PayType IN (";
for (int i = 0; i < listInsuranceTypes.Count; i++)
{
if (i > 0)
{
queryIns += ",";
}
queryIns += POut.Long(listInsuranceTypes[i]);
}
queryIns += ") ";
}
queryIns += @"GROUP BY claimproc.DateCP,claimproc.ClaimPaymentNum,";
if (doShowProvSeparate)
{
queryIns += @"provider.ProvNum,";
}
if (hasClinicsEnabled)
{
queryIns += "claimproc.ClinicNum,clinic.Description,";
}
queryIns += "carrier.CarrierName,claimpayment.CheckNum";
if (isGroupedByPatient)
{
queryIns += ",patient.PatNum";
}
queryIns += " ORDER BY claimpayment.PayType,claimproc.DateCP,lfname";
if (!hasInsuranceTypes && listInsuranceTypes.Count == 0)
{
queryIns = DbHelper.LimitOrderBy(queryIns, 0);
}
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryIns)));
}
///<summary>Returns list of credit cards that are ready for a recurring charge.</summary>
public static DataTable GetRecurringChargeList()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod()));
}
DataTable table = 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) ";
table = Db.GetTable(command);
FilterRecurringChargeList(table);
return(table);
}
///<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.DatePay 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.DatePay<=" + 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);
}
}
///<summary>Gets all task lists for the main trunk. Pass in the current user. filterClinicFkey and filterRegionFkey are only used for
///NewTaskCount and do not affect which TaskLists are returned by this method. Pass filterClinicFkey=0 and filterRegionFkey=0 to intentionally
///bypass filtering.</summary>
public static List <TaskList> RefreshMainTrunk(long userNum, TaskType taskType, long filterClinicFkey = 0, long filterRegionFkey = 0)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TaskList> >(MethodBase.GetCurrentMethod(), userNum, taskType, filterClinicFkey, filterRegionFkey));
}
string command = @"SELECT tasklist.*,COALESCE(unreadtasks.Count,0) 'NewTaskCount'
FROM tasklist
LEFT JOIN (SELECT tasklist.TaskListNum,COUNT(*) Count
FROM tasklist
INNER JOIN taskancestor ON taskancestor.TaskListNum = tasklist.TaskListNum
INNER JOIN task ON task.TaskNum = taskancestor.TaskNum " ;
if (taskType == TaskType.Reminder)
{
command += "AND COALESCE(task.ReminderGroupId,'') != '' "; //reminders only
}
else if (taskType == TaskType.Normal)
{
command += "AND NOT(COALESCE(task.ReminderGroupId,'') != '' AND task.DateTimeEntry > " + DbHelper.Now() + ") "; //no future reminders
}
else
{
//No filter.
}
if (PrefC.GetBool(PrefName.TasksNewTrackedByUser))
{
command += "AND task.TaskStatus!=" + POut.Int((int)TaskStatusEnum.Done) + " ";
command += @"
LEFT JOIN (
SELECT TaskListInBox,UserNum
FROM userod
GROUP BY TaskListInBox
) usr ON usr.TaskListInBox = tasklist.TaskListNum
INNER JOIN (
SELECT TaskNum,UserNum
FROM taskunread
) isUnread ON isUnread.TaskNum = task.TaskNum AND (CASE WHEN usr.UserNum IS NOT NULL THEN isUnread.UserNum=usr.UserNum ELSE isUnread.UserNum = " + POut.Long(userNum) + @" END)
" ;
}
else
{
command += "AND task.TaskStatus=0 ";
}
command += BuildFilterJoins(filterClinicFkey);
command += "WHERE TRUE "; //gross. But makes this query easier to write.
command += BuildFilterWhereClause(userNum, filterClinicFkey, filterRegionFkey);
command += "GROUP BY tasklist.TaskListNum) unreadtasks ON unreadtasks.TaskListNum=tasklist.TaskListNum "
+ "WHERE Parent=0 "
+ "AND DateTL < " + POut.Date(new DateTime(1880, 01, 01)) + " "
+ "AND IsRepeating=0 "
+ "ORDER BY tasklist.Descript,tasklist.DateTimeEntry";
return(TableToList(Db.GetTable(command)));
}
///<summary>Gets the masked CC# and exp date for all cards setup for monthly charges for the specified patient. Only used for filling [CreditCardsOnFile] variable when emailing statements.</summary>
public static string GetMonthlyCardsOnFile(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetString(MethodBase.GetCurrentMethod(), patNum));
}
string result = "";
string command = "SELECT * FROM creditcard WHERE PatNum=" + POut.Long(patNum)
+ " AND (" + DbHelper.Year("DateStop") + "<1880 OR DateStop>" + DbHelper.Now() + ") "//Recurring card is active.
+ " AND ChargeAmt>0";
List <CreditCard> monthlyCards = Crud.CreditCardCrud.SelectMany(command);
for (int i = 0; i < monthlyCards.Count; i++)
{
if (i > 0)
{
result += ", ";
}
result += monthlyCards[i].CCNumberMasked + " exp:" + monthlyCards[i].CCExpiration.ToString("MM/yy");
}
return(result);
}
///<summary>Gets all task lists for the trunk of the user tab. filterClinicFkey and filterRegionFkey are only used for NewTaskCount and do not
///affect which TaskLists are returned by this method. Pass filterClinicFkey=0 and filterRegionFkey=0 to intentionally bypass filters.</summary>
public static List <TaskList> RefreshUserTrunk(long userNum, long filterClinicFkey = 0, long filterRegionFkey = 0)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TaskList> >(MethodBase.GetCurrentMethod(), userNum, filterClinicFkey, filterRegionFkey));
}
string command = @"SELECT tasklist.*,COALESCE(unreadtasks.Count,0) 'NewTaskCount',t2.Descript 'ParentDesc1',t3.Descript 'ParentDesc2'
FROM tasklist
LEFT JOIN tasksubscription ON tasksubscription.TaskListNum=tasklist.TaskListNum
LEFT JOIN tasklist t2 ON t2.TaskListNum=tasklist.Parent
LEFT JOIN tasklist t3 ON t3.TaskListNum=t2.Parent
LEFT JOIN (
SELECT taskancestor.TaskListNum,COUNT(*) 'Count'
FROM taskancestor
INNER JOIN task ON task.TaskNum=taskancestor.TaskNum
AND NOT(COALESCE(task.ReminderGroupId,'') != '' AND task.DateTimeEntry > " + DbHelper.Now() + ") ";//no future reminders
command += BuildFilterJoins(filterClinicFkey);
if (PrefC.GetBool(PrefName.TasksNewTrackedByUser))
{
command += @"
INNER JOIN taskunread ON taskunread.TaskNum=task.TaskNum
WHERE taskunread.UserNum = " + POut.Long(userNum) + @"
AND task.TaskStatus!=" + POut.Int((int)TaskStatusEnum.Done);
}
else
{
command += @"
WHERE task.TaskStatus=" + POut.Int((int)TaskStatusEnum.New);
}
command += BuildFilterWhereClause(userNum, filterClinicFkey, filterRegionFkey);
command += @"
GROUP BY taskancestor.TaskListNum) unreadtasks ON unreadtasks.TaskListNum = tasklist.TaskListNum
WHERE tasksubscription.UserNum=" + POut.Long(userNum) + @"
AND tasksubscription.TaskListNum!=0
ORDER BY tasklist.Descript,tasklist.DateTimeEntry" ;
return(TableToList(Db.GetTable(command)));
}
///<summary>0 is not allowed, because that would be a trunk. Pass in the current user. Also, if this is in someone's inbox, then pass in the
///userNum whose inbox it is in. If not in an inbox, pass in 0. filterClinicFkey and filterRegionFkey are only used for NewTaskCount and do
///not affect which TaskLists are returned by this method. Pass filterClinicFkey=0 and filterRegionFkey=0 to intentionally bypass filtering.
///</summary>
public static List <TaskList> RefreshChildren(long parent, long userNum, long userNumInbox, TaskType taskType, long filterClinicFkey = 0
, long filterRegionFkey = 0)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TaskList> >(MethodBase.GetCurrentMethod(), parent, userNum, userNumInbox, taskType, filterClinicFkey, filterRegionFkey));
}
string command =
"SELECT tasklist.*,"
+ "(SELECT COUNT(*) FROM taskancestor INNER JOIN task ON task.TaskNum=taskancestor.TaskNum ";
command += BuildFilterJoins(filterClinicFkey);
command += "WHERE taskancestor.TaskListNum=tasklist.TaskListNum ";
if (taskType == TaskType.Reminder)
{
command += "AND COALESCE(task.ReminderGroupId,'') != '' "; //reminders only
}
else if (taskType == TaskType.Normal)
{
command += "AND NOT(COALESCE(task.ReminderGroupId,'') != '' AND task.DateTimeEntry > " + DbHelper.Now() + ") "; //no future reminders
}
else
{
//No filter.
}
if (PrefC.GetBool(PrefName.TasksNewTrackedByUser))
{
command += "AND EXISTS(SELECT * FROM taskunread WHERE taskunread.TaskNum=task.TaskNum ";
//If a task is marked done, we don't care if it is unread. Usually if a task is done all the taskunreads will be cleared.
//Added this for an HQ issue where tasklists always showed you had an unread task, even though there were not any open unread tasks.
command += "AND task.TaskStatus!=" + POut.Int((int)TaskStatusEnum.Done) + " ";
//if a list is someone's inbox,
if (userNumInbox > 0)
{
//then restrict by that user
command += "AND taskunread.UserNum=" + POut.Long(userNumInbox) + ") ";
}
else
{
//otherwise, restrict by current user
command += "AND taskunread.UserNum=" + POut.Long(userNum) + ") ";
}
}
else
{
command += "AND task.TaskStatus=" + POut.Int((int)TaskStatusEnum.New);
}
command += BuildFilterWhereClause(userNum, filterClinicFkey, filterRegionFkey);
command += ") NewTaskCount "
+ "FROM tasklist "
+ "WHERE Parent=" + POut.Long(parent) + " "
+ "ORDER BY tasklist.Descript,tasklist.DateTimeEntry";
return(TableToList(Db.GetTable(command)));
}
///<summary>Used when printing or emailing recall to make a commlog entry without any display.
///Set commSource to the corresponding entity that is making this recall. E.g. Web Sched.
///If the commSource is a 3rd party, set it to ProgramLink and make an overload that accepts the ProgramNum.</summary>
public static Commlog InsertForRecallOrReactivation(long patNum, CommItemMode _mode, int numberOfReminders, long defNumNewStatus, CommItemSource commSource, long userNum, DateTime dateTimeNow, CommItemTypeAuto type = CommItemTypeAuto.RECALL)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <Commlog>(MethodBase.GetCurrentMethod(), patNum, _mode, numberOfReminders, defNumNewStatus, commSource, userNum,
dateTimeNow, type));
}
long commType = Commlogs.GetTypeAuto(type);
string commTypeStr = type == CommItemTypeAuto.RECALL?"Recall":"Reactivation";
string command;
string datesql = "CURDATE()";
if (DataConnection.DBtype == DatabaseType.Oracle)
{
datesql = "(SELECT CURRENT_DATE FROM dual)";
}
if (commType != 0)
{
command = "SELECT * FROM commlog WHERE ";
command += DbHelper.DtimeToDate("CommDateTime") + " = " + datesql;
command += " AND PatNum=" + POut.Long(patNum) + " AND CommType=" + POut.Long(commType)
+ " AND Mode_=" + POut.Long((int)_mode)
+ " AND SentOrReceived=1";
List <Commlog> listComms = Crud.CommlogCrud.SelectMany(command).OrderByDescending(x => x.CommDateTime).ToList();
if (listComms.Count > 0)
{
return(listComms[0]);
}
}
Commlog com = new Commlog();
com.PatNum = patNum;
com.CommDateTime = dateTimeNow;
com.CommType = commType;
com.Mode_ = _mode;
com.SentOrReceived = CommSentOrReceived.Sent;
com.Note = "";
if (numberOfReminders == 0)
{
com.Note = Lans.g("FormRecallList", $"{commTypeStr} reminder.");
}
else if (numberOfReminders == 1)
{
com.Note = Lans.g("FormRecallList", $"Second {commTypeStr} reminder.");
}
else if (numberOfReminders == 2)
{
com.Note = Lans.g("FormRecallList", $"Third {commTypeStr} reminder.");
}
else
{
com.Note = Lans.g("FormRecallList", $"{commTypeStr} reminder:") + " " + (numberOfReminders + 1).ToString();
}
if (defNumNewStatus == 0)
{
com.Note += " " + Lans.g("Commlogs", "Status None");
}
else
{
com.Note += " " + Defs.GetName(DefCat.RecallUnschedStatus, defNumNewStatus);
}
com.UserNum = userNum;
com.CommSource = commSource;
com.CommlogNum = Insert(com);
EhrMeasureEvent newMeasureEvent = new EhrMeasureEvent();
newMeasureEvent.DateTEvent = com.CommDateTime;
newMeasureEvent.EventType = EhrMeasureEventType.ReminderSent;
newMeasureEvent.PatNum = com.PatNum;
newMeasureEvent.MoreInfo = com.Note;
EhrMeasureEvents.Insert(newMeasureEvent);
return(com);
}
///<summary>Returns true if there is an active phone for the country code.</summary>
public static bool IsTextingForCountry(params string[] countryCodes)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), countryCodes));
}
if (countryCodes == null || countryCodes.Length == 0)
{
return(false);
}
string command = "SELECT COUNT(*) FROM smsphone WHERE CountryCode IN (" + string.Join(",", countryCodes.Select(x => "'" + POut.String(x) + "'")) + ") AND " + DbHelper.Year("DateTimeInactive") + "<1880";
return(Db.GetScalar(command) != "0");
}
public static DataTable GetListOrderBy2014(List <EhrPatListElement2014> elementList)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), elementList));
}
DataTable table = new DataTable();
string select = "SELECT patient.PatNum,patient.LName,patient.FName";
string from = "FROM patient";
string where = "WHERE TRUE "; //Makes formatting easier when adding additional clauses because they will all be AND clauses.
for (int i = 0; i < elementList.Count; i++)
{
switch (elementList[i].Restriction)
{
case EhrRestrictionType.Birthdate: //---------------------------------------------------------------------------------------------------------------------------
select += ",patient.BirthDate, ((YEAR(CURDATE())-YEAR(DATE(patient.Birthdate))) - (RIGHT(CURDATE(),5)<RIGHT(DATE(patient.Birthdate),5))) AS Age";
from += ""; //only selecting from patient table
where += "AND ((YEAR(CURDATE())-YEAR(DATE(patient.Birthdate))) - (RIGHT(CURDATE(),5)<RIGHT(DATE(patient.Birthdate),5)))" + GetOperandText(elementList[i].Operand) + "" + PIn.String(elementList[i].CompareString) + " ";
break;
case EhrRestrictionType.Gender: //------------------------------------------------------------------------------------------------------------------------------
select += ",patient.Gender"; //will look odd if user adds multiple gender columns, enum needs to be "decoded" when filling grid.
break;
case EhrRestrictionType.LabResult: //---------------------------------------------------------------------------------------------------------------------------
//TODO Units
from += ",ehrlab AS ehrlab" + i + ",ehrlabresult AS ehrlabresult" + i + " ";
where += "AND ehrlab" + i + ".PatNum=patient.PatNum AND ehrlab" + i + ".EhrLabNum=ehrlabresult" + i + ".EhrLabNum "; //join
where += "AND ('" + elementList[i].CompareString + "'=ehrlabresult" + i + ".ObservationIdentifierID OR '"
+ elementList[i].CompareString + "'=ehrlabresult" + i + ".ObservationIdentifierIDAlt) "; //filter, LOINC of lab observation
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND ehrlabresult" + i + ".ObservationDateTime >=" + POut.Date(elementList[i].StartDate) + " "; //on or after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND ehrlabresult" + i + ".ObservationDateTime <=" + POut.Date(elementList[i].EndDate) + " "; //on or before this date
}
switch (elementList[i].LabValueType)
{
//CE and CWE should be SNOMEDCT codes, string compare elementList[i].LabValue to ehrlabresult.ObservationValueCodedElementID or ObservationValueCodedElementIDAlt
case HL70125.CE:
case HL70125.CWE:
select += ",(CASE WHEN ehrlabresult" + i + ".ObservationValueCodedElementID='' THEN ehrlabresult" + i + ".ObservationValueCodedElementIDAlt ELSE ehrlabresult" + i + ".ObservationValueCodedElementID END) AS LabValue";
where += "AND (ehrlabresult" + i + ".ObservationValueCodedElementID='" + elementList[i].LabValue + "' OR "
+ "ehrlabresult" + i + ".ObservationValueCodedElementIDAlt='" + elementList[i].LabValue + "') "
+ "AND (ehrlabresult" + i + ".ValueType='CWE' OR ehrlabresult" + i + ".ValueType='CE') ";
break;
//DT is stored as a string in ehrlabresult.ObservationValueDateTime as YYYY[MM[DD]]
case HL70125.DT:
select += ",ehrlabresult" + i + ".ObservationValueDateTime "; //+DbHelper.DateFormatColumn("RPAD(ehrlabresult"+i+".ObservationValueDateTime,8,'01')","%m/%d/%Y");
where += "AND " + DbHelper.DateColumn("RPAD(ehrlabresult" + i + ".ObservationValueDateTime,8,'01')")
+ GetOperandText(elementList[i].Operand) + "'" + POut.String(elementList[i].LabValue) + "' "
+ "AND ehrlabresult" + i + ".ValueType='DT' ";
break;
//TS is YYYYMMDDHHMMSS, string compare
case HL70125.TS:
select += ",ehrlabresult" + i + ".ObservationValueDateTime "; //+DbHelper.DateTFormatColumn("ehrlabresult"+i+".ObservationValueDateTime","%m/%d/%Y %H:%i:%s");
where += "AND ehrlabresult" + i + ".ObservationValueDateTime " //+POut.DateT(PIn.DateT(DbHelper.DateTFormatColumn("ehrlabresult"+i+".ObservationValueDateTime","%m/%d/%Y %H:%i:%s")))
+ GetOperandText(elementList[i].Operand) + "'" + POut.String(elementList[i].LabValue) + "' "
+ "AND ehrlabresult" + i + ".ValueType='TS' ";
break;
//00:00:00
case HL70125.TM:
select += ",ehrlabresult" + i + ".ObservationValueTime";
where += "AND ehrlabresult" + i + ".ObservationValueTime" + GetOperandText(elementList[i].Operand) + "'" + POut.TSpan(PIn.TSpan(elementList[i].LabValue)) + "' "
+ "AND ehrlabresult" + i + ".ValueType='TM' ";
break;
case HL70125.SN:
select += ",CONCAT(CONCAT(CONCAT(ehrlabresult" + i + ".ObservationValueComparator,ehrlabresult" + i + ".ObservationValueNumber1),ehrlabresult" + i + ".ObservationValueSeparatorOrSuffix),ehrlabresult" + i + ".ObservationValueNumber2)";
where += "AND ehrlabresult" + i + ".ValueType='SN' ";
break;
case HL70125.NM:
select += ",ehrlabresult" + i + ".ObservationValueNumeric";
where += "AND ehrlabresult" + i + ".ObservationValueNumeric" + GetOperandText(elementList[i].Operand) + POut.Double(PIn.Double(elementList[i].LabValue)) + " "
+ "AND ehrlabresult" + i + ".ValueType='NM' ";
break;
case HL70125.FT:
case HL70125.ST:
case HL70125.TX:
select += ",ehrlabresult" + i + ".ObservationValueText";
//where+="AND ehrlabresult"+i+".ObservationValueText"+GetOperandText(elementList[i].Operand)+POut.String(elementList[i].LabValue)+" "
where += "AND (ehrlabresult" + i + ".ValueType='FT' OR ehrlabresult" + i + ".ValueType='ST' OR ehrlabresult" + i + ".ValueType='TX') ";
break;
}
select += ",ehrlabresult" + i + ".ObservationDateTime ";
//select+=",labresult"+i+".ObsValue,labresult"+i+".DateTimeTest";//format column name when filling grid.
//from+=",labresult AS labresult"+i+", labpanel AS labpanel"+i;
//where+="AND labpanel"+i+".LabpanelNum=labresult"+i+".LabpanelNum AND patient.PatNum=labpanel"+i+".PatNum ";//join
//where+="AND labresult"+i+".TestId='"+elementList[i].CompareString+"' "
// +"AND labresult"+i+".ObsValue"+GetOperandText(elementList[i].Operand)+"'"+PIn.String(elementList[i].LabValue)+"' ";//filter
//if(elementList[i].StartDate!=null && elementList[i].StartDate.Year>1880) {
// where+="AND labresult"+i+".DateTimeTest>"+POut.Date(elementList[i].StartDate)+" ";//after this date
//}
//if(elementList[i].EndDate!=null && elementList[i].EndDate.Year>1880) {
// where+="AND labresult"+i+".DateTimeTest<"+POut.Date(elementList[i].EndDate)+" ";//before this date
//}
break;
case EhrRestrictionType.Medication: //--------------------------------------------------------------------------------------------------------------------------
select += ",medicationpat" + i + ".DateStart"; //Name of medication will be in column title.
from += ",medication AS medication" + i + ", medicationpat AS medicationpat" + i;
where += "AND medicationpat" + i + ".PatNum=patient.PatNum "; //join
//This is unusual. Part of the join logic is in the code below because medicationPat.MedicationNum might be 0 if it came from newcrop.
where += "AND ((medication" + i + ".MedicationNum=MedicationPat" + i + ".MedicationNum AND medication" + i + ".MedName LIKE '%" + PIn.String(elementList[i].CompareString) + "%') "
+ " OR (medication" + i + ".MedicationNum=0 AND medicationpat" + i + ".MedDescript LIKE '%" + PIn.String(elementList[i].CompareString) + "%')) ";
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND medicationpat" + i + ".DateStart>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND medicationpat" + i + ".DateStart<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.Problem: //-----------------------------------------------------------------------------------------------------------------------------
select += ",disease" + i + ".DateStart"; //Name of problem will be in column title.
from += ",disease AS disease" + i + ", diseasedef AS diseasedef" + i;
where += "AND diseasedef" + i + ".DiseaseDefNum=disease" + i + ".DiseaseDefNum AND disease" + i + ".PatNum=patient.PatNum "; //join
where += "AND (diseasedef" + i + ".ICD9Code='" + PIn.String(elementList[i].CompareString) + "' OR diseasedef" + i + ".SnomedCode='" + PIn.String(elementList[i].CompareString) + "') "; //filter
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND disease" + i + ".DateStart>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND disease" + i + ".DateStart<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.Allergy: //-----------------------------------------------------------------------------------------------------------------------------
select += ",allergy" + i + ".DateAdverseReaction"; //Name of allergy will be in column title.
from += ",allergy AS allergy" + i + ", allergydef AS allergydef" + i;
where += "AND allergydef" + i + ".AllergyDefNum=allergy" + i + ".AllergyDefNum AND allergy" + i + ".PatNum=patient.PatNum "; //join
where += "AND allergydef" + i + ".Description='" + PIn.String(elementList[i].CompareString) + "' "; //filter
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND allergy" + i + ".DateAdverseReaction>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND allergy" + i + ".DateAdverseReaction<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.CommPref: //----------------------------------------------------------------------------------------------------------------------------
select += ",patient.PreferContactConfidential";
from += ""; //only selecting from patient table
where += "AND patient.PreferContactConfidential=" + PIn.Int(contactMethodHelper(elementList[i].CompareString)) + " ";
break;
default:
//should never happen.
continue;
}
}
string command = select + " " + from + " " + where;
return(Db.GetTable(command));
}
