///<summary>Deletes FeeScheds that are hidden and not attached to any insurance plans. Returns the number of deleted fee scheds.</summary>
public static long CleanupAllowedScheds()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetLong(MethodBase.GetCurrentMethod());
}
long result;
//Detach allowed FeeSchedules from any hidden InsPlans.
string command = "UPDATE insplan "
+ "SET AllowedFeeSched=0 "
+ "WHERE IsHidden=1";
Db.NonQ(command);
//Delete unattached FeeSchedules.
command = "DELETE FROM feesched "
+ "WHERE FeeSchedNum NOT IN (SELECT AllowedFeeSched FROM insplan) "
+ "AND FeeSchedType=" + POut.Int((int)FeeScheduleType.Allowed);
result = Db.NonQ(command);
//Delete all orphaned fees.
command = "DELETE FROM fee "
+ "WHERE FeeSched NOT IN (SELECT FeeSchedNum FROM feesched)";
Db.NonQ(command);
return(result);
}
///<summary>Upserts the InvalidType.SmsTextMsgReceivedUnreadCount signal which tells all client machines to update the received unread SMS
///message count. There should only be max one of this signal IType in the database.</summary>
public static List <SmsFromMobiles.SmsNotification> UpsertSmsNotification()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <SmsFromMobiles.SmsNotification> >(MethodBase.GetCurrentMethod()));
}
string command = "SELECT ClinicNum,COUNT(*) AS CountUnread FROM smsfrommobile WHERE SmsStatus=0 AND IsHidden=0 GROUP BY ClinicNum "
+ "ORDER BY ClinicNum";
List <SmsFromMobiles.SmsNotification> ret = Db.GetTable(command).AsEnumerable()
.Select(x => new SmsFromMobiles.SmsNotification()
{
ClinicNum = PIn.Long(x["ClinicNum"].ToString()),
Count = PIn.Int(x["CountUnread"].ToString()),
}).ToList();
//Insert as structured data signal so all workstations won't have to query the db to get the counts. They will get it directly from Signalod.MsgValue.
string json = SmsFromMobiles.SmsNotification.GetJsonFromList(ret);
//FKeyType SmsMsgUnreadCount is written to db as a string.
command = "SELECT * FROM signalod WHERE IType=" + POut.Int((int)InvalidType.SmsTextMsgReceivedUnreadCount)
+ " AND FKeyType='" + POut.String(KeyType.SmsMsgUnreadCount.ToString()) + "' ORDER BY SigDateTime DESC LIMIT 1";
DataTable table = Db.GetTable(command);
Signalod sig = Crud.SignalodCrud.TableToList(table).FirstOrDefault();
if (sig != null && sig.MsgValue == json) //No changes, not need to insert a new signal.
{
return(ret); //Return the list of notifications, but do not update the existing signal.
}
Signalods.Insert(new Signalod()
{
IType = InvalidType.SmsTextMsgReceivedUnreadCount,
FKeyType = KeyType.SmsMsgUnreadCount,
MsgValue = json,
RemoteRole = RemotingClient.RemotingRole
});
return(ret);
}
///<summary>Gets all Faq's associated to the given manual page name and version. If version is blank will query for all versions.</summary>
public static List <Faq> GetAllForNameAndVersion(string pageName, int version)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Faq> >(MethodBase.GetCurrentMethod(), pageName, version));
}
string command = $@"
SELECT faq.*
FROM faqmanualpagelink
INNER JOIN manualpage ON manualpage.ManualPageNum=faqmanualpagelink.ManualPageNum
INNER JOIN faq ON faq.FaqNum=faqmanualpagelink.FaqNum
WHERE manualpage.FileName LIKE '%{POut.String(pageName)}%'" ;
if (version > 0)
{
command += $"AND faq.ManualVersion={POut.Int(version)}";
}
List <Faq> retVal = new List <Faq>();
DataAction.RunManualPublisherHQ(() => {
retVal = Crud.FaqCrud.SelectMany(command);
});
return(retVal);
}
///<summary>Gets logs from the passed in datetime and before.</summary>
public static List <InsEditLog> GetLogsForPlan(long planNum, long carrierNum, long employerNum, DateTime dateStart, DateTime dateEnd)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <InsEditLog> >(MethodBase.GetCurrentMethod(), planNum, carrierNum, employerNum, dateStart, dateEnd));
}
List <InsEditLog> retVal = new List <InsEditLog>();
string command = @"SELECT inseditlog.* FROM inseditlog ";
command += @"LEFT JOIN insplan ON insplan.PlanNum = inseditlog.FKey
AND insplan.PlanNum LIKE '" + POut.Long(planNum) + @"%'
AND inseditlog.LogType = " + POut.Int((int)InsEditLogType.InsPlan) + @"
LEFT JOIN carrier ON carrier.CarrierNum = inseditlog.FKey
AND carrier.CarrierNum = " + POut.Long(carrierNum) + @"
AND inseditlog.LogType = " + POut.Int((int)InsEditLogType.Carrier) + @"
LEFT JOIN employer ON employer.EmployerNum = inseditlog.FKey
AND employer.EmployerNum = " + POut.Long(employerNum) + @"
AND inseditlog.LogType = " + POut.Int((int)InsEditLogType.Employer) + @"
WHERE (DateTStamp BETWEEN " + POut.DateT(dateStart) + " AND " + POut.DateT(dateEnd == DateTime.MinValue ? DateTime.Now : dateEnd) + @")
AND (insplan.PlanNum IS NOT NULL OR carrier.CarrierNum IS NOT NULL OR employer.EmployerNum IS NOT NULL
OR (inseditlog.LogType = " + POut.Int((int)InsEditLogType.Benefit) + " AND inseditlog.ParentKey = " + POut.Long(planNum) + "))";
List <InsEditLog> listLogs = Crud.InsEditLogCrud.SelectMany(command); //get all of the logs
//get any logs that show that InsPlan's PlanNum changed
return(GetChangedLogs(listLogs).OrderBy(x => x.DateTStamp)
.ThenBy(x => x.LogType != InsEditLogType.InsPlan)
.ThenBy(x => x.LogType != InsEditLogType.Carrier)
.ThenBy(x => x.LogType != InsEditLogType.Employer)
.ThenBy(x => x.LogType != InsEditLogType.Benefit)
.ThenBy(x => x.FKey)
//primary keys first
.ThenBy(x => x.LogType == InsEditLogType.Benefit ? x.FieldName != "BenefitNum"
: x.LogType == InsEditLogType.Carrier ? x.FieldName != "CarrierNum"
: x.LogType == InsEditLogType.Employer ? x.FieldName != "EmployerNum"
: x.FieldName != "PlanNum").ToList());
}
///<summary>Returns the query string of the coverage section for the patients. It is known that patNumStr is not empty</summary>
private static string GetWhereCoverageStr(bool isProcsGeneral, DateTime renewDate, string patNumStr)
{
//No remoting role check; no call to db
return((isProcsGeneral?"":$@"LEFT JOIN procedurelog pl ON pl.ProcNum=claimproc.ProcNum
LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
LEFT JOIN (
SELECT inssub.InsSubNum,COALESCE(cp.FromCode,pc.ProcCode) AS FromCode,COALESCE(cp.ToCode,pc.ProcCode) AS ToCode
FROM inssub
INNER JOIN benefit b ON b.PlanNum=inssub.PlanNum
LEFT JOIN covcat cc ON cc.CovCatNum=b.CovCatNum
LEFT JOIN covspan cp ON cp.CovCatNum=cc.CovCatNum
LEFT JOIN procedurecode pc ON pc.CodeNum=b.CodeNum
WHERE b.BenefitType={(int)InsBenefitType.Limitations}
AND b.QuantityQualifier={(int)BenefitQuantity.None}
AND b.TimePeriod IN ({(int)BenefitTimePeriod.ServiceYear},{(int)BenefitTimePeriod.CalendarYear})
AND (cc.CovCatNum IS NOT NULL OR b.CodeNum!=0)
) procCheck ON procCheck.InsSubNum=claimproc.InsSubNum AND pc.ProcCode BETWEEN procCheck.FromCode AND procCheck.ToCode
" )
+ $@"WHERE claimproc.Status IN ({POut.Int((int)ClaimProcStatus.NotReceived)},{POut.Int((int)ClaimProcStatus.Received)},"
+ $@"{POut.Int((int)ClaimProcStatus.Adjustment)},{POut.Int((int)ClaimProcStatus.Supplemental)})
AND claimproc.ProcDate BETWEEN {POut.Date(renewDate)} AND {POut.Date(renewDate.AddYears(1).AddDays(-1))}
AND claimproc.PatNum IN ({patNumStr}){(isProcsGeneral?"":$@"
AND procCheck.InsSubNum IS NULL")}" );
}
///<summary>Returns true if the patient passed in has any outstanding non-ins payment plans with them as the guarantor.</summary>
public static bool HasOutstandingPayPlansNoIns(long guarNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), guarNum));
}
string command = "SELECT SUM(paysplit.SplitAmt) FROM paysplit "
+ "INNER JOIN payplan ON paysplit.PayPlanNum=payplan.PayPlanNum "
+ "WHERE payplan.PlanNum=0 "
+ "AND payplan.Guarantor=" + POut.Long(guarNum);
double amtPaid = PIn.Double(Db.GetScalar(command));
command = "SELECT SUM(payplancharge.Principal+payplancharge.Interest) FROM payplancharge "
+ "INNER JOIN payplan ON payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "WHERE payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " AND payplan.PlanNum=0 "
+ "AND payplan.Guarantor=" + POut.Long(guarNum);
double totalCost = PIn.Double(Db.GetScalar(command));
if (totalCost - amtPaid < .01)
{
return(false);
}
return(true);
}
/// <summary>Moves all family and superfamily level popups for a patient being deleted so that those popups stay in the family/superfamily.</summary>
public static void MoveForDeletePat(Patient pat)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), pat);
return;
}
string command = "UPDATE popup ";
if (pat.PatNum == pat.Guarantor) //When deleting the guarantor, move all superfamily popups to the superfamily head
{
command += "SET PatNum = " + POut.Long(pat.SuperFamily) + " "
+ "WHERE PopupLevel = " + POut.Int((int)EnumPopupLevel.SuperFamily) + " "
+ "AND PatNum = " + POut.Long(pat.PatNum);
}
else //Move all family/superfamily popups to the guarantor
{
command += "SET PatNum = " + POut.Long(pat.Guarantor) + " "
+ "WHERE (PopupLevel = " + POut.Int((int)EnumPopupLevel.Family) + " "
+ "OR PopupLevel = " + POut.Int((int)EnumPopupLevel.SuperFamily) + ") "
+ "AND PatNum = " + POut.Long(pat.PatNum);
}
Db.NonQ(command);
}
///<summary>Returns a list of AlertItems for the given clinicNum.</summary>
public static List <AlertItem> RefreshForClinicAndTypes(long clinicNum, List <AlertType> listAlertTypes = null)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <AlertItem> >(MethodBase.GetCurrentMethod(), clinicNum, listAlertTypes));
}
if (listAlertTypes == null || listAlertTypes.Count == 0)
{
return(new List <AlertItem>());
}
long provNum = 0;
if (Security.CurUser != null && Userods.IsUserCpoe(Security.CurUser))
{
provNum = Security.CurUser.ProvNum;
}
string command = "";
if (DataConnection.DBtype == DatabaseType.MySql)
{
command = "SELECT * FROM alertitem "
+ "WHERE Type IN (" + String.Join(",", listAlertTypes.Cast <int>().ToList()) + ") "
//For AlertType.RadiologyProcedures we only care if the alert is associated to the current logged in provider.
//When provNum is 0 the initial WHEN check below will not bring any rows by definition of the FKey column.
+ "AND (CASE TYPE WHEN " + POut.Int((int)AlertType.RadiologyProcedures) + " THEN FKey=" + POut.Long(provNum) + " "
+ "ELSE ClinicNum = " + POut.Long(clinicNum) + " OR ClinicNum=-1 END)";
}
else //oracle
//Case statements cannot change column return results unless they are within the SELECT case.
{
command = "SELECT AlertItemNum,CASE Type WHEN 3 THEN ClinicNum ELSE 0 END ClinicNum,Description,Type,Severity,Actions,FormToOpen,CASE Type WHEN 3 THEN 0 ELSE FKey END FKey,ItemValue "
+ "FROM alertitem "
+ "WHERE Type IN (" + String.Join(",", listAlertTypes.Cast <int>().ToList()) + ") ";
}
return(Crud.AlertItemCrud.SelectMany(command));
}
///<summary>Gets the first Active TP from the DB for the patient. Returns null if no Active TP is found for this patient.</summary>
public static TreatPlan GetActiveForPat(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <TreatPlan>(MethodBase.GetCurrentMethod(), patNum));
}
string command = "SELECT * FROM treatplan WHERE PatNum=" + POut.Long(patNum) + " AND TPStatus=" + POut.Int((int)TreatPlanStatus.Active);
return(Crud.TreatPlanCrud.SelectOne(command));
}
/// <summary>Gets info directly from database. Used from PayPlan and Account windows to get the amount paid so far on one payment plan.</summary>
public static double GetAmtPaid(PayPlan payPlan)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <double>(MethodBase.GetCurrentMethod(), payPlan));
}
string command;
if (payPlan.PlanNum == 0) //Patient payment plan
{
command = "SELECT SUM(paysplit.SplitAmt) FROM paysplit "
+ "WHERE paysplit.PayPlanNum = " + POut.Long(payPlan.PayPlanNum) + " "
+ "GROUP BY paysplit.PayPlanNum";
}
else //Insurance payment plan
{
command = "SELECT SUM(claimproc.InsPayAmt) "
+ "FROM claimproc "
+ "WHERE claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Received) + "," + POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapClaim) + ") "
+ "AND claimproc.PayPlanNum=" + POut.Long(payPlan.PayPlanNum);
}
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return(0);
}
return(PIn.Double(table.Rows[0][0].ToString()));
}
///<summary>Also alters the db table for the list itself. Throws exception if number of columns does not match.</summary>
public static void UpdateNamesAndWidths(string listName, List <WikiListHeaderWidth> columnDefs)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), columnDefs);
return;
}
string command = "DESCRIBE wikilist_" + POut.String(listName);
DataTable tableListDescription = Db.GetTable(command);
if (tableListDescription.Rows.Count != columnDefs.Count)
{
throw new ApplicationException("List schema has been altered. Unable to save changes to list.");
}
//rename Columns with dummy names in case user is renaming a new column with an old name.---------------------------------------------
for (int i = 0; i < tableListDescription.Rows.Count; i++)
{
if (tableListDescription.Rows[i][0].ToString().ToLower() == POut.String(listName) + "num")
{
//skip primary key
continue;
}
command = "ALTER TABLE wikilist_" + POut.String(listName) + " CHANGE " + POut.String(tableListDescription.Rows[i][0].ToString()) + " " + POut.String(dummyColName + i) + " TEXT NOT NULL";
Db.NonQ(command);
command =
"UPDATE wikiListHeaderWidth SET ColName='" + POut.String(dummyColName + i) + "' "
+ "WHERE ListName='" + POut.String(listName) + "' "
+ "AND ColName='" + POut.String(tableListDescription.Rows[i][0].ToString()) + "'";
Db.NonQ(command);
}
//rename columns names and widths-------------------------------------------------------------------------------------------------------
for (int i = 0; i < tableListDescription.Rows.Count; i++)
{
if (tableListDescription.Rows[i][0].ToString().ToLower() == listName + "num")
{
//skip primary key
continue;
}
command = "ALTER TABLE wikilist_" + POut.String(listName) + " CHANGE " + POut.String(dummyColName + i) + " " + POut.String(columnDefs[i].ColName) + " TEXT NOT NULL";
Db.NonQ(command);
command = "UPDATE wikiListHeaderWidth "
+ "SET ColName='" + POut.String(columnDefs[i].ColName) + "', ColWidth='" + POut.Int(columnDefs[i].ColWidth) + "' "
+ "WHERE ListName='" + POut.String(listName) + "' "
+ "AND ColName='" + POut.String(dummyColName + i) + "'";
Db.NonQ(command);
}
//handle width of PK seperately because we do not rename the PK column, ever.
command = "UPDATE wikiListHeaderWidth SET ColWidth='" + POut.Int(columnDefs[0].ColWidth) + "' "
+ "WHERE ListName='" + POut.String(listName) + "' AND ColName='" + POut.String(columnDefs[0].ColName) + "'";
Db.NonQ(command);
RefreshCache();
}
///<summary>Returns the number of partial batch insurance payments or if there are any claimprocs with status Received that have InsPayAmts but
///are not associated to a claim payment. Used to warn users that reports will be inaccurate until insurance payments are finalized.</summary>
public static int GetPartialPaymentsCount()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetInt(MethodBase.GetCurrentMethod()));
}
//Union together two queries that look for incomplete insurance payments.
//The first query will look for any partial batch insurance payments.
//The second query will look for any claim payments (InsPayAmt > 0) that do not have a claim payment (no check / not finalized).
string command = @"
SELECT COUNT(*)
FROM ((SELECT claimpayment.ClaimPaymentNum
FROM claimpayment
WHERE claimpayment.IsPartial = 1
AND claimpayment.CheckDate <= " + POut.Date(DateTime.Now) + @"
AND claimpayment.CheckDate >= " + POut.Date(DateTime.Now.AddMonths(-1)) + @")
UNION ALL
(SELECT claimproc.ClaimProcNum
FROM claimproc
WHERE claimproc.ClaimPaymentNum = 0
AND claimproc.InsPayAmt != 0
AND claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Received) + "," + POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapClaim) + @")
AND claimproc.DateEntry <= " + POut.Date(DateTime.Now) + @"
AND claimproc.DateEntry >= " + POut.Date(DateTime.Now.AddMonths(-1)) + @")
) partialpayments" ;//claimproc.DateEntry is updated when payment is received.
return(PIn.Int(Db.GetCount(command), false));
}
///<summary>Used only from FormReferenceSelect to get the list of references.</summary>
public static DataTable GetReferenceTable(bool limit, long[] billingTypes, bool showBadRefs, bool showUsed, bool showGuarOnly, string city, string state, string zip,
string areaCode, string specialty, int superFam, string lname, string fname, string patnum, int age)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), limit, billingTypes, showBadRefs, showUsed, showGuarOnly, city, state, zip, areaCode, specialty, superFam, lname, fname, patnum, age));
}
string billingSnippet = "";
if (billingTypes.Length != 0)
{
for (int i = 0; i < billingTypes.Length; i++)
{
if (i == 0)
{
billingSnippet += "AND (";
}
else
{
billingSnippet += "OR ";
}
billingSnippet += "BillingType=" + POut.Long(billingTypes[i]) + " ";
if (i == billingTypes.Length - 1)
{
billingSnippet += ") ";
}
}
}
string phonedigits = "";
for (int i = 0; i < areaCode.Length; i++)
{
if (Regex.IsMatch(areaCode[i].ToString(), "[0-9]"))
{
phonedigits = phonedigits + areaCode[i];
}
}
string regexp = "";
for (int i = 0; i < phonedigits.Length; i++)
{
if (i < 1)
{
regexp = "^[^0-9]?"; //Allows phone to start with "("
}
regexp += phonedigits[i] + "[^0-9]*";
}
DataTable table = new DataTable();
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("CustReferenceNum");
table.Columns.Add("PatNum");
table.Columns.Add("FName");
table.Columns.Add("LName");
table.Columns.Add("HmPhone");
table.Columns.Add("State");
table.Columns.Add("City");
table.Columns.Add("Zip");
table.Columns.Add("Specialty");
table.Columns.Add("age");
table.Columns.Add("SuperFamily");
table.Columns.Add("DateMostRecent");
table.Columns.Add("TimesUsed");
table.Columns.Add("IsBadRef");
List <DataRow> rows = new List <DataRow>();
string command = @"SELECT * FROM (SELECT cr.*,p.LName,p.FName,p.HmPhone,p.State,p.City,p.Zip,p.Birthdate,pf.FieldValue,
(SELECT COUNT(*) FROM patient tempp WHERE tempp.SuperFamily=p.SuperFamily AND tempp.SuperFamily<>0) AS SuperFamily,
(SELECT COUNT(*) FROM custrefentry tempcre WHERE tempcre.PatNumRef=cr.PatNum) AS TimesUsed
FROM custreference cr
INNER JOIN patient p ON cr.PatNum=p.PatNum
LEFT JOIN patfield pf ON cr.PatNum=pf.PatNum AND pf.FieldName='Specialty'
WHERE cr.CustReferenceNum<>0 " ; //This just makes the following AND statements brainless.
command += "AND (p.PatStatus=" + POut.Int((int)PatientStatus.Patient) + " OR p.PatStatus=" + POut.Int((int)PatientStatus.NonPatient) + ") " //excludes deleted, etc.
+ billingSnippet;
if (age > 0)
{
command += "AND p.Birthdate <" + POut.Date(DateTime.Now.AddYears(-age)) + " ";
}
if (regexp != "")
{
command += "AND (p.HmPhone REGEXP '" + POut.String(regexp) + "' )";
}
command += (lname.Length > 0?"AND (p.LName LIKE '" + POut.String(lname) + "%' OR p.Preferred LIKE '" + POut.String(lname) + "%') ":"")
+ (fname.Length > 0?"AND (p.FName LIKE '" + POut.String(fname) + "%' OR p.Preferred LIKE '" + POut.String(fname) + "%') ":"")
+ (city.Length > 0?"AND p.City LIKE '" + POut.String(city) + "%' ":"")
+ (state.Length > 0?"AND p.State LIKE '" + POut.String(state) + "%' ":"")
+ (zip.Length > 0?"AND p.Zip LIKE '" + POut.String(zip) + "%' ":"")
+ (patnum.Length > 0?"AND p.PatNum LIKE '" + POut.String(patnum) + "%' ":"")
+ (specialty.Length > 0?"AND pf.FieldValue LIKE '" + POut.String(specialty) + "%' ":"")
+ (showBadRefs?"":"AND cr.IsBadRef=0 ")
+ (showGuarOnly?"AND p.Guarantor=p.PatNum":"");
if (limit)
{
command = DbHelper.LimitOrderBy(command, 40);
}
command += @") AS tempcustref WHERE PatNum<>0 "; //Once again just making AND statements brainless.
if (superFam > 0)
{
command += "AND SuperFamily>" + POut.Int(superFam) + " ";
}
if (showUsed)
{
command += "AND TimesUsed>0 ";
}
DataTable rawtable = Db.GetTable(command);
for (int i = 0; i < rawtable.Rows.Count; i++)
{
row = table.NewRow();
row["CustReferenceNum"] = rawtable.Rows[i]["CustReferenceNum"].ToString();
row["PatNum"] = rawtable.Rows[i]["PatNum"].ToString();
row["FName"] = rawtable.Rows[i]["FName"].ToString();
row["LName"] = rawtable.Rows[i]["LName"].ToString();
row["HmPhone"] = rawtable.Rows[i]["HmPhone"].ToString();
row["State"] = rawtable.Rows[i]["State"].ToString();
row["City"] = rawtable.Rows[i]["City"].ToString();
row["Zip"] = rawtable.Rows[i]["Zip"].ToString();
row["Specialty"] = rawtable.Rows[i]["FieldValue"].ToString();
row["age"] = Patients.DateToAge(PIn.Date(rawtable.Rows[i]["Birthdate"].ToString())).ToString();
row["SuperFamily"] = rawtable.Rows[i]["SuperFamily"].ToString();
DateTime recentDate = PIn.DateT(rawtable.Rows[i]["DateMostRecent"].ToString());
row["DateMostRecent"] = "";
if (recentDate.Year > 1880)
{
row["DateMostRecent"] = recentDate.ToShortDateString();
}
row["TimesUsed"] = rawtable.Rows[i]["TimesUsed"].ToString();
row["IsBadRef"] = rawtable.Rows[i]["IsBadRef"].ToString();
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
return(table);
}
/// <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;
string result;
//claim.InsSubNum/2
command = "SELECT COUNT(*) FROM claim WHERE InsSubNum = " + POut.Long(subNum);
result = Db.GetScalar(command);
if (result != "0")
{
throw new ApplicationException(Lans.g("FormInsPlan", "Subscriber has existing claims and so the subscriber cannot be deleted."));
}
command = "SELECT COUNT(*) FROM claim WHERE InsSubNum2 = " + POut.Long(subNum);
result = Db.GetScalar(command);
if (result != "0")
{
throw new ApplicationException(Lans.g("FormInsPlan", "Subscriber has existing claims and so the subscriber cannot be deleted."));
}
//claimproc.InsSubNum
if (strict)
{
command = "SELECT COUNT(*) FROM claimproc WHERE InsSubNum = " + POut.Long(subNum) + " AND Status != " + POut.Int((int)ClaimProcStatus.Estimate); //ignore estimates
result = Db.GetScalar(command);
if (result != "0")
{
throw new ApplicationException(Lans.g("FormInsPlan", "Subscriber has existing claim procedures and so the subscriber cannot be deleted."));
}
}
//etrans.InsSubNum
command = "SELECT COUNT(*) FROM etrans WHERE InsSubNum = " + POut.Long(subNum);
result = Db.GetScalar(command);
if (result != "0")
{
throw new ApplicationException(Lans.g("FormInsPlan", "Subscriber has existing etrans entry and so the subscriber cannot be deleted."));
}
//payplan.InsSubNum
command = "SELECT COUNT(*) FROM payplan WHERE InsSubNum = " + POut.Long(subNum);
result = Db.GetScalar(command);
if (result != "0")
{
throw new ApplicationException(Lans.g("FormInsPlan", "Subscriber has existing insurance linked payment plans and so the subscriber cannot be deleted."));
}
}
///<summary>Gets all tasks for the main trunk.</summary>
public static List <Task> RefreshMainTrunk(bool showDone, DateTime startDate, long currentUserNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Task> >(MethodBase.GetCurrentMethod(), showDone, startDate, currentUserNum));
}
//startDate only applies if showing Done tasks.
string command = "SELECT task.*,"
+ "(SELECT COUNT(*) FROM taskunread WHERE task.TaskNum=taskunread.TaskNum "
+ "AND taskunread.UserNum=" + POut.Long(currentUserNum) + ") IsUnread, "
+ "(SELECT LName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") LName, "
+ "(SELECT FName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") FName, "
+ "(SELECT Preferred FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") Preferred "
+ "FROM task "
+ "WHERE TaskListNum=0 "
+ "AND DateTask < " + POut.Date(new DateTime(1880, 01, 01)) + " "
+ "AND IsRepeating=0";
if (showDone)
{
command += " AND (TaskStatus !=" + POut.Long((int)TaskStatusEnum.Done)
+ " OR DateTimeFinished > " + POut.Date(startDate) + ")"; //of if done, then restrict date
}
else
{
command += " AND TaskStatus !=" + POut.Long((int)TaskStatusEnum.Done);
}
command += " ORDER BY DateTimeEntry";
DataTable table = Db.GetTable(command);
return(TableToList(table));
}
///<summary>Gets all tasks for the repeating trunk. Always includes "done".</summary>
public static List <Task> RefreshRepeatingTrunk()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Task> >(MethodBase.GetCurrentMethod()));
}
string command = "SELECT task.*, "
+ "(SELECT LName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") LName, "
+ "(SELECT FName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") FName, "
+ "(SELECT Preferred FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") Preferred "
+ "FROM task "
+ "WHERE TaskListNum=0 "
+ "AND DateTask < " + POut.Date(new DateTime(1880, 01, 01)) + " "
+ "AND IsRepeating=1 "
+ "ORDER BY DateTimeEntry";
DataTable table = Db.GetTable(command);
return(TableToList(table));
}
///<summary>All repeating items for one date type with no heirarchy.</summary>
public static List <Task> RefreshRepeating(TaskDateType dateType, long currentUserNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Task> >(MethodBase.GetCurrentMethod(), dateType, currentUserNum));
}
string command =
"SELECT task.*, "
+ "(SELECT COUNT(*) FROM taskunread WHERE task.TaskNum=taskunread.TaskNum "
+ "AND taskunread.UserNum=" + POut.Long(currentUserNum) + ") IsUnread, " //Not sure if this makes sense here
+ "(SELECT LName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") LName, "
+ "(SELECT FName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") FName, "
+ "(SELECT Preferred FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") Preferred "
+ "FROM task "
+ "WHERE IsRepeating=1 "
+ "AND DateType=" + POut.Long((int)dateType) + " "
+ "ORDER BY DateTimeEntry";
DataTable table = Db.GetTable(command);
return(TableToList(table));
}
///<summary>Gets all 'new' tasks for a user.</summary>
public static List <Task> RefreshUserNew(long userNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Task> >(MethodBase.GetCurrentMethod(), userNum));
}
string command = "SELECT task.*,1 AS IsUnread, "
//we fill the IsUnread column with 1's because we already know that they are all unread
+ "(SELECT tasklist.Descript FROM tasklist WHERE task.TaskListNum=tasklist.TaskListNum) ParentDesc, "
+ "(SELECT LName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") LName, "
+ "(SELECT FName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") FName, "
+ "(SELECT Preferred FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") Preferred "
+ "FROM task,taskunread "
+ "WHERE task.TaskNum=taskunread.TaskNum "
+ "AND taskunread.UserNum = " + POut.Long(userNum) + " "
+ "GROUP BY task.TaskNum " //in case there are duplicate unreads
+ "ORDER BY task.DateTimeEntry";
DataTable table = Db.GetTable(command);
return(TableToList(table));
}
///<summary>May not return correct values if notes are stored with newline characters.</summary>
public static List <long> GetNumsByNote(string oldNote)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), oldNote));
}
oldNote = oldNote.Replace("\r", "");
//oldNote=oldNote.Replace("\r","").Replace("\n","\r\n");
//oldNote=oldNote.Replace("\r","").Replace("\n","*?");
string command = "SELECT TreatPlanNum FROM treatplan WHERE REPLACE(Note,'\\r','')='" + POut.String(oldNote) + "' " +
"AND TPStatus IN (" + POut.Int((int)TreatPlanStatus.Active) + "," + POut.Int((int)TreatPlanStatus.Inactive) + ")";
//string command="SELECT TreatPlanNum FROM treatplan WHERE Note='"+POut.String(oldNote)+"' "+
// "AND TPStatus IN ("+POut.Int((int)TreatPlanStatus.Active)+","+POut.Int((int)TreatPlanStatus.Inactive)+")";
return(Db.GetListLong(command));
}
///<summary>Helper method that is only useful for Oracle. This method is really just here for exposure for the lack of Oracle functionality.
///Oracle will cut up a section of the CLOB column using SUBSTR. The portion is dictated by starting at startIndex for substringLength chars.
///When using MySQL you simply order by the column name because it is smart enough to allow users to ORDER BY 'text' data type.</summary>
public static string ClobOrderBy(string columnName, int startIndex = 1, int substringLength = 1000)
{
if (DataConnection.DBtype == DatabaseType.Oracle)
{
return("DBMS_LOB.SUBSTR(" + columnName + "," + POut.Int(substringLength) + "," + POut.Int(startIndex) + ")");
}
return(columnName);
}
public static DataTable GetProvList(DateTime dt)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dt));
}
#if DEBUG
_elapsedTimeProvList = "";
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
System.Diagnostics.Stopwatch stopWatchTotal = new System.Diagnostics.Stopwatch();
_elapsedTimeProvList = "Elapsed time for GetProvList:\r\n";
stopWatch.Restart();
stopWatchTotal.Restart();
#endif
Random rnd = new Random();
string rndStr = rnd.Next(1000000).ToString();
string command;
command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = @"CREATE TABLE tempdash" + rndStr + @" (
ProvNum bigint NOT NULL PRIMARY KEY,
production decimal NOT NULL,
income decimal NOT NULL
) DEFAULT CHARSET=utf8" ;
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "CREATE TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//providers
command = @"INSERT INTO tempdash" + rndStr + @" (ProvNum)
SELECT ProvNum
FROM provider WHERE IsHidden=0
ORDER BY ItemOrder" ;
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "providers: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//production--------------------------------------------------------------------
//procs
command = @"UPDATE tempdash" + rndStr + @"
SET production=(SELECT SUM(ProcFee*(UnitQty+BaseUnits)) FROM procedurelog
WHERE procedurelog.ProvNum=tempdash" + rndStr + @".ProvNum
AND procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + @"
AND ProcDate=" + POut.Date(dt) + ")";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "production - procs: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//capcomplete writeoffs were skipped
//adjustments
command = @"UPDATE tempdash" + rndStr + @"
SET production=production+(SELECT IFNULL(SUM(AdjAmt),0) FROM adjustment
WHERE adjustment.ProvNum=tempdash" + rndStr + @".ProvNum
AND AdjDate=" + POut.Date(dt) + ")";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "production - adjustments: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//insurance writeoffs
if (PrefC.GetBool(PrefName.ReportsPPOwriteoffDefaultToProcDate)) //use procdate
{
command = @"UPDATE tempdash" + rndStr + @"
SET production=production-(SELECT IFNULL(SUM(WriteOff),0) FROM claimproc
WHERE claimproc.ProvNum=tempdash" + rndStr + @".ProvNum
AND ProcDate=" + POut.Date(dt) + @"
AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) )" ; //received or supplemental or notreceived
}
else
{
command = @"UPDATE tempdash" + rndStr + @"
SET production=production-(SELECT IFNULL(SUM(WriteOff),0) FROM claimproc
WHERE claimproc.ProvNum=tempdash" + rndStr + @".ProvNum
AND DateCP=" + POut.Date(dt) + @"
AND (claimproc.Status=1 OR claimproc.Status=4) )" ;//received or supplemental
}
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "production - writeoffs: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//income------------------------------------------------------------------------
//patient income
command = @"UPDATE tempdash" + rndStr + @"
SET income=(SELECT SUM(SplitAmt) FROM paysplit
WHERE paysplit.ProvNum=tempdash" + rndStr + @".ProvNum
AND DatePay=" + POut.Date(dt) + ")";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "income - patient: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//ins income
command = @"UPDATE tempdash" + rndStr + @"
SET income=income+(SELECT IFNULL(SUM(InsPayAmt),0) FROM claimproc
WHERE claimproc.ProvNum=tempdash" + rndStr + @".ProvNum
AND DateCP=" + POut.Date(dt) + ")";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "income - insurance: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//final queries
command = "SELECT * FROM tempdash" + rndStr + @"";
DataTable table = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "SELECT * : " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
stopWatchTotal.Stop();
_elapsedTimeProvList += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
_elapsedTimeProvList += "Total: " + stopWatchTotal.Elapsed.ToString();
if (_showElapsedTimesForDebug)
{
System.Windows.Forms.MessageBox.Show(_elapsedTimeProvList);
}
#endif
return(table);
}
public static DataTable GetProvList(DateTime dt)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dt));
}
string command;
command = "DROP TABLE IF EXISTS tempdash;";
Db.NonQ(command);
command = @"CREATE TABLE tempdash (
ProvNum bigint NOT NULL PRIMARY KEY,
production decimal NOT NULL,
income decimal NOT NULL
) DEFAULT CHARSET=utf8" ;
Db.NonQ(command);
//providers
command = @"INSERT INTO tempdash (ProvNum)
SELECT ProvNum
FROM provider WHERE IsHidden=0
ORDER BY ItemOrder" ;
Db.NonQ(command);
//production--------------------------------------------------------------------
//procs
command = @"UPDATE tempdash
SET production=(SELECT SUM(ProcFee*(UnitQty+BaseUnits)) FROM procedurelog
WHERE procedurelog.ProvNum=tempdash.ProvNum
AND procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + @"
AND ProcDate=" + POut.Date(dt) + ")";
Db.NonQ(command);
//capcomplete writeoffs were skipped
//adjustments
command = @"UPDATE tempdash
SET production=production+(SELECT IFNULL(SUM(AdjAmt),0) FROM adjustment
WHERE adjustment.ProvNum=tempdash.ProvNum
AND AdjDate=" + POut.Date(dt) + ")";
Db.NonQ(command);
//insurance writeoffs
if (PrefC.GetBool(PrefName.ReportsPPOwriteoffDefaultToProcDate)) //use procdate
{
command = @"UPDATE tempdash
SET production=production-(SELECT IFNULL(SUM(WriteOff),0) FROM claimproc
WHERE claimproc.ProvNum=tempdash.ProvNum
AND ProcDate=" + POut.Date(dt) + @"
AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) )" ; //received or supplemental or notreceived
}
else
{
command = @"UPDATE tempdash
SET production=production-(SELECT IFNULL(SUM(WriteOff),0) FROM claimproc
WHERE claimproc.ProvNum=tempdash.ProvNum
AND DateCP=" + POut.Date(dt) + @"
AND (claimproc.Status=1 OR claimproc.Status=4) )" ;//received or supplemental
}
Db.NonQ(command);
//income------------------------------------------------------------------------
//patient income
command = @"UPDATE tempdash
SET income=(SELECT SUM(SplitAmt) FROM paysplit
WHERE paysplit.ProvNum=tempdash.ProvNum
AND DatePay=" + POut.Date(dt) + ")";
Db.NonQ(command);
//ins income
command = @"UPDATE tempdash
SET income=income+(SELECT IFNULL(SUM(InsPayAmt),0) FROM claimproc
WHERE claimproc.ProvNum=tempdash.ProvNum
AND DateCP=" + POut.Date(dt) + ")";
Db.NonQ(command);
//final queries
command = "SELECT * FROM tempdash";
DataTable table = Db.GetTable(command);
command = "DROP TABLE IF EXISTS tempdash;";
Db.NonQ(command);
return(table);
}
///<summary>Gets all 'open ticket' tasks for a user. An open ticket is a task that was created by this user, is attached to a patient, and is not done.</summary>
public static List <Task> RefreshOpenTickets(long userNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Task> >(MethodBase.GetCurrentMethod(), userNum));
}
string command = "SELECT task.*, "
+ "(SELECT COUNT(*) FROM taskunread WHERE task.TaskNum=taskunread.TaskNum "
+ "AND taskunread.UserNum=" + POut.Long(userNum) + ") AS IsUnread, "
+ "(SELECT tasklist.Descript FROM tasklist WHERE task.TaskListNum=tasklist.TaskListNum) ParentDesc, "
+ "(SELECT LName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") LName, "
+ "(SELECT FName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") FName, "
+ "(SELECT Preferred FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") Preferred "
+ "FROM task "
+ "WHERE NOT EXISTS(SELECT * FROM taskancestor,tasklist "
+ "WHERE taskancestor.TaskNum=task.TaskNum "
+ "AND tasklist.TaskListNum=taskancestor.TaskListNum "
+ "AND tasklist.DateType!=0) " //if any ancestor is a dated list, then we don't want that task
//+"AND NOT EXISTS(SELECT * FROM taskancestor,tasksubscription "//a different set of ancestors
//+"WHERE taskancestor.TaskNum=task.TaskNum "
//+"AND tasksubscription.TaskListNum=taskancestor.TaskListNum "
//+"AND tasksubscription.UserNum="+POut.Long(userNum)+") "//if this user is subscribed to any ancestor list, then we won't include it
+ "AND task.DateType=0 " //this only handles tasks directly in the dated trunks
+ "AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + " "
+ "AND task.IsRepeating=0 "
+ "AND task.UserNum=" + POut.Long(userNum) + " "
+ "AND TaskStatus != " + POut.Int((int)TaskStatusEnum.Done) + " "
+ "ORDER BY DateTimeEntry";
DataTable table = Db.GetTable(command);
return(TableToList(table));
}
///<summary>Delete the attachment for the claim currently attached to the 835 with the specified segment index.
///Safe to run even if no claim is currently attached at the specified index.</summary>
public static void Delete(long etransNum, int clpSegmentIndex)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), etransNum, clpSegmentIndex);
return;
}
string command = "DELETE FROM etrans835attach WHERE EtransNum=" + POut.Long(etransNum) + " AND ClpSegmentIndex=" + POut.Int(clpSegmentIndex);
Db.NonQ(command);
}
///<summary>0 is not allowed, because that would be a trunk. 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.</summary>
public static List <Task> RefreshChildren(long listNum, bool showDone, DateTime startDate, long currentUserNum, long userNumInbox)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Task> >(MethodBase.GetCurrentMethod(), listNum, showDone, startDate, currentUserNum, userNumInbox));
}
//startDate only applies if showing Done tasks.
string command =
"SELECT task.*, "
+ "(SELECT COUNT(*) FROM taskunread WHERE task.TaskNum=taskunread.TaskNum "; //the count turns into a bool
//if(PrefC.GetBool(PrefName.TasksNewTrackedByUser)) {//we don't bother with this. Always get IsUnread
//if a task is someone's inbox,
if (userNumInbox > 0)
{
//then restrict by that user
command += "AND taskunread.UserNum=" + POut.Long(userNumInbox) + ") IsUnread, ";
}
else
{
//otherwise, restrict by current user
command += "AND taskunread.UserNum=" + POut.Long(currentUserNum) + ") IsUnread, ";
}
command += "(SELECT LName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") LName, "
+ "(SELECT FName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") FName, "
+ "(SELECT Preferred FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") Preferred ";
command += "FROM task "
+ "WHERE TaskListNum=" + POut.Long(listNum);
if (showDone)
{
command += " AND (TaskStatus !=" + POut.Long((int)TaskStatusEnum.Done)
+ " OR DateTimeFinished > " + POut.Date(startDate) + ")"; //of if done, then restrict date
}
else
{
command += " AND TaskStatus !=" + POut.Long((int)TaskStatusEnum.Done);
}
command += " ORDER BY DateTimeEntry";
DataTable table = Db.GetTable(command);
return(TableToList(table));
}
///<summary>Gets table for main provider edit list. SchoolClass is usually zero to indicate all providers. IsAlph will sort aphabetically instead of by ItemOrder.</summary>
public static DataTable Refresh(long schoolClass, bool isAlph)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), schoolClass, isAlph));
}
string command = "SELECT Abbr,LName,FName,provider.IsHidden,provider.ItemOrder,provider.ProvNum,GradYear,Descript,MAX(UserName) UserName, PatCount " //Max function used for Oracle compatability (some providers may have multiple user names).
+ "FROM provider LEFT JOIN schoolclass ON provider.SchoolClassNum=schoolclass.SchoolClassNum "
+ "LEFT JOIN userod ON userod.ProvNum=provider.ProvNum "
+ "LEFT JOIN (SELECT PriProv, COUNT(*) PatCount FROM patient "
+ "WHERE patient.PatStatus!=" + POut.Int((int)PatientStatus.Deleted) + " AND patient.PatStatus!=" + POut.Int((int)PatientStatus.Deceased) + " "
+ "GROUP BY PriProv) pat ON provider.ProvNum=pat.PriProv ";
if (schoolClass != 0)
{
command += "WHERE provider.SchoolClassNum=" + POut.Long(schoolClass) + " ";
}
command += "GROUP BY Abbr,LName,FName,provider.IsHidden,provider.ItemOrder,provider.ProvNum,GradYear,Descript ";
if (isAlph)
{
command += "ORDER BY GradYear,Descript,LName,FName";
}
else
{
command += "ORDER BY ItemOrder";
}
return(Db.GetTable(command));
}
///<summary>Gets all tasks for one of the 3 dated trunks. startDate only applies if showing Done.</summary>
public static List <Task> RefreshDatedTrunk(DateTime date, TaskDateType dateType, bool showDone, DateTime startDate, long currentUserNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Task> >(MethodBase.GetCurrentMethod(), date, dateType, showDone, startDate, currentUserNum));
}
DateTime dateFrom = DateTime.MinValue;
DateTime dateTo = DateTime.MaxValue;
if (dateType == TaskDateType.Day)
{
dateFrom = date;
dateTo = date;
}
else if (dateType == TaskDateType.Week)
{
dateFrom = date.AddDays(-(int)date.DayOfWeek);
dateTo = dateFrom.AddDays(6);
}
else if (dateType == TaskDateType.Month)
{
dateFrom = new DateTime(date.Year, date.Month, 1);
dateTo = dateFrom.AddMonths(1).AddDays(-1);
}
string command =
"SELECT task.*, "
+ "(SELECT COUNT(*) FROM taskunread WHERE task.TaskNum=taskunread.TaskNum "
+ "AND taskunread.UserNum=" + POut.Long(currentUserNum) + ") IsUnread, " //Not sure if this makes sense here
+ "(SELECT LName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") LName, "
+ "(SELECT FName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") FName, "
+ "(SELECT Preferred FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") Preferred "
+ "FROM task "
+ "WHERE DateTask >= " + POut.Date(dateFrom)
+ " AND DateTask <= " + POut.Date(dateTo)
+ " AND DateType=" + POut.Long((int)dateType);
if (showDone)
{
command += " AND (TaskStatus !=" + POut.Long((int)TaskStatusEnum.Done)
+ " OR DateTimeFinished > " + POut.Date(startDate) + ")"; //of if done, then restrict date
}
else
{
command += " AND TaskStatus !=" + POut.Long((int)TaskStatusEnum.Done);
}
command += " ORDER BY DateTimeEntry";
DataTable table = Db.GetTable(command);
return(TableToList(table));
}
private static List <Appointment> GetAppointmentsToSendReview(ReviewInvitationTrigger trigger, long programNum, bool isNewPatient)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Appointment> >(MethodBase.GetCurrentMethod(), trigger, programNum, isNewPatient));
}
string minutesToWaitCompleted = ProgramProperties.GetPropVal(programNum, PropertyDescs.ApptSetCompletedMinutes);
string minutesToWaitTimeArrived = ProgramProperties.GetPropVal(programNum, PropertyDescs.ApptTimeArrivedMinutes);
string minutesToWaitTimeDismissed = ProgramProperties.GetPropVal(programNum, PropertyDescs.ApptTimeDismissedMinutes);
string command = "SELECT * "
+ "FROM appointment "
+ "LEFT JOIN securitylog ON securitylog.FKey=appointment.AptNum "
+ "AND securitylog.PermType=" + POut.Int((int)Permissions.AppointmentEdit) + " AND securitylog.LogText LIKE '%Set Complete%' "
+ "LEFT JOIN commlog ON commlog.PatNum=appointment.PatNum "
+ "AND commlog.CommSource=" + POut.Int((int)CommItemSource.ProgramLink) + " "
+ "AND DATE(commlog.DateTimeEnd)=" + DbHelper.Curdate() + " "
+ "AND commlog.ProgramNum=" + POut.Long(programNum) + " "
+ "WHERE ISNULL(commlog.PatNum) AND appointment.AptDateTime BETWEEN " + DbHelper.Curdate() + " AND " + DbHelper.Now() + " + INTERVAL 1 HOUR "//Hard code an hour to allow for appointments that have an early DateTimeArrived
+ "AND appointment.IsNewPatient=" + POut.Bool(isNewPatient) + " ";
if (trigger == ReviewInvitationTrigger.AppointmentCompleted)
{
command += "AND appointment.AptStatus=" + POut.Int((int)ApptStatus.Complete) + " "
+ "AND NOT ISNULL(securitylog.PatNum) "
+ "AND securitylog.LogDateTime + INTERVAL " + minutesToWaitCompleted + " MINUTE <=" + DbHelper.Now() + " ";
}
else if (trigger == ReviewInvitationTrigger.AppointmentTimeArrived)
{
command += "AND appointment.AptStatus IN (" + POut.Int((int)ApptStatus.Scheduled) + "," + POut.Int((int)ApptStatus.Complete) + ") "
+ "AND ((appointment.AptStatus=" + POut.Int((int)ApptStatus.Complete) + " AND NOT ISNULL(securitylog.PatNum) AND securitylog.LogDateTime + INTERVAL " + minutesToWaitCompleted + " MINUTE <=" + DbHelper.Now() + ") "
+ "OR (appointment.DateTimeArrived>" + DbHelper.Curdate() + " AND appointment.DateTimeArrived + INTERVAL " + minutesToWaitTimeArrived + " MINUTE<=" + DbHelper.Now() + ")) ";
}
else if (trigger == ReviewInvitationTrigger.AppointmentTimeDismissed)
{
command += "AND appointment.AptStatus IN (" + POut.Int((int)ApptStatus.Scheduled) + "," + POut.Int((int)ApptStatus.Complete) + ") "
+ "AND ((appointment.AptStatus=" + POut.Int((int)ApptStatus.Complete) + " AND NOT ISNULL(securitylog.PatNum) AND securitylog.LogDateTime + INTERVAL 90 MINUTE <=" + DbHelper.Now() + ") "
+ "OR (appointment.DateTimeDismissed>" + DbHelper.Curdate() + " AND appointment.DateTimeDismissed + INTERVAL " + minutesToWaitTimeDismissed + " MINUTE<=" + DbHelper.Now() + ")) ";
}
return(Crud.AppointmentCrud.SelectMany(command));
}
///<summary>Returns a count of the number of C, EC, and EO procedures attached to a group note. Takes the ProcNum of a group note.
///Used when deleting group notes to determine which permission to check.</summary>
public static int GetCountCompletedProcsForGroup(long groupNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetInt(MethodBase.GetCurrentMethod(), groupNum));
}
string command = "SELECT COUNT(*) FROM procgroupitem "
+ "INNER JOIN procedurelog ON procedurelog.ProcNum=procgroupitem.ProcNum "
+ "AND procedurelog.ProcStatus IN (" + POut.Int((int)ProcStat.C) + ", " + POut.Int((int)ProcStat.EO) + ", " + POut.Int((int)ProcStat.EC) + ") "
+ "WHERE GroupNum = " + POut.Long(groupNum);
return(PIn.Int(Db.GetCount(command)));
}
public static DataTable GetData(List <long> listProvNums, List <long> listClinicNums, DateTime dateStart, DateTime dateEnd, bool includeNoNotes,
bool includeUnsignedNotes, ToothNumberingNomenclature toothNumberFormat, ProcNoteGroupBy groupBy)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listProvNums, listClinicNums, dateStart, dateEnd, includeNoNotes, includeUnsignedNotes,
toothNumberFormat, groupBy));
}
string whereNoNote = "";
string whereUnsignedNote = "";
string whereNotesClause = "";
if (includeNoNotes)
{
whereNoNote = @"
LEFT JOIN (
SELECT procedurelog.PatNum,procedurelog.ProcDate
FROM procedurelog
INNER JOIN procnote ON procnote.ProcNum=procedurelog.ProcNum
INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum
AND procedurecode.ProcCode NOT IN ('D9986','D9987')
WHERE procedurelog.ProcDate BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + @"
AND (procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " OR (procedurelog.ProcStatus=" + POut.Int((int)ProcStat.EC)
+ @" AND procedurecode.ProcCode='~GRP~'))"
+ @" GROUP BY procedurelog.PatNum,procedurelog.ProcDate
) hasNotes ON hasNotes.PatNum=procedurelog.PatNum AND hasNotes.ProcDate=procedurelog.ProcDate " ;
whereNotesClause = "AND (n1.ProcNum IS NOT NULL OR hasNotes.PatNum IS NULL) ";
}
if (includeUnsignedNotes)
{
if (includeNoNotes)
{
whereNotesClause = "AND (n1.ProcNum IS NOT NULL OR hasNotes.PatNum IS NULL OR unsignedNotes.ProcNum IS NOT NULL)";
}
else
{
whereNotesClause = "AND (n1.ProcNum IS NOT NULL OR unsignedNotes.ProcNum IS NOT NULL)";
}
whereUnsignedNote = @"
LEFT JOIN procnote unsignedNotes ON unsignedNotes.ProcNum=procedurelog.ProcNum
AND unsignedNotes.Signature=''
AND unsignedNotes.EntryDateTime= (SELECT MAX(n2.EntryDateTime)
FROM procnote n2
WHERE unsignedNotes.ProcNum = n2.ProcNum) " ;
}
string command = @"SELECT MAX(procedurelog.ProcDate) ProcDate,MAX(CONCAT(CONCAT(patient.LName, ', '),patient.FName)) PatName,procedurelog.PatNum,
(CASE WHEN COUNT(procedurelog.ProcNum)=1 THEN MAX(procedurecode.ProcCode) ELSE '' END) ProcCode,
(CASE WHEN COUNT(procedurelog.ProcNum)=1 THEN MAX(procedurecode.Descript) ELSE '" + Lans.g("FormRpProcNote", "Multiple procedures") + @"' END) Descript,
(CASE WHEN COUNT(procedurelog.ProcNum)=1 THEN MAX(procedurelog.ToothNum) ELSE '' END) ToothNum,
(CASE WHEN COUNT(procedurelog.ProcNum)=1 THEN MAX(procedurelog.Surf) ELSE '' END) Surf "
+ (includeNoNotes || includeUnsignedNotes?",(CASE WHEN MAX(n1.ProcNum) IS NOT NULL THEN 'X' ELSE '' END) AS Incomplete ":"")
+ (includeNoNotes?",(CASE WHEN MAX(hasNotes.PatNum) IS NULL THEN 'X' ELSE '' END) AS HasNoNote ":"")
+ (includeUnsignedNotes?",(CASE WHEN MAX(unsignedNotes.ProcNum) IS NOT NULL THEN 'X' ELSE '' END) AS HasUnsignedNote ":"") + @"
FROM procedurelog
INNER JOIN patient ON procedurelog.PatNum = patient.PatNum
INNER JOIN procedurecode ON procedurelog.CodeNum = procedurecode.CodeNum
AND procedurecode.ProcCode NOT IN ('D9986','D9987')
" + (includeNoNotes || includeUnsignedNotes?"LEFT":"INNER") + @" JOIN procnote n1 ON procedurelog.ProcNum = n1.ProcNum
AND (n1.Note LIKE '%""""%' OR n1.Note REGEXP '" + @"\[Prompt:""[a-zA-Z_0-9 ]+""\]') "//looks for either "" (pre 17.3) or [Prompt:"{word}"] (post 17.3)
+ @" AND n1.EntryDateTime= (SELECT MAX(n2.EntryDateTime)
FROM procnote n2
WHERE n1.ProcNum = n2.ProcNum) "
+ whereNoNote + " "
+ whereUnsignedNote + @"
WHERE procedurelog.ProcDate BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + @"
AND (procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C)
+ " OR (procedurelog.ProcStatus=" + POut.Int((int)ProcStat.EC) + " "
+ @" AND procedurecode.ProcCode='~GRP~')) "
+ whereNotesClause;
if (listProvNums.Count > 0)
{
command += @"AND procedurelog.ProvNum IN (" + String.Join(",", listProvNums) + ") ";
}
if (listClinicNums.Count > 0)
{
command += @"AND procedurelog.ClinicNum IN (" + String.Join(",", listClinicNums) + ") ";
}
if (groupBy == ProcNoteGroupBy.Patient)
{
command += @"GROUP BY procedurelog.PatNum ";
}
else if (groupBy == ProcNoteGroupBy.DateAndPatient)
{
command += @"GROUP BY procedurelog.ProcDate,procedurelog.PatNum ";
}
else
{
command += "GROUP BY procedurelog.ProcNum ";
}
command += @"ORDER BY ProcDate, LName";
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
foreach (DataRow row in table.Rows)
{
row["ToothNum"] = Tooth.ToInternat(row["ToothNum"].ToString(), toothNumberFormat);
}
return(table);
}
