///<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); }