///<summary>Returns the patient's clinic based on the recall passed in. ///If the patient is no longer associated to a clinic, /// returns the clinic associated to the appointment (scheduled or completed) with the largest date. ///Returns null if the patient doesn't have a clinic or if the clinics feature is not activate.</summary> public static Clinic GetClinicForRecall(long recallNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <Clinic>(MethodBase.GetCurrentMethod(), recallNum)); } if (PrefC.GetBool(PrefName.EasyNoClinics)) { return(null); } string command = "SELECT patient.ClinicNum FROM patient " + "INNER JOIN recall ON patient.PatNum=recall.PatNum " + "WHERE recall.RecallNum=" + POut.Long(recallNum) + " " + DbHelper.LimitAnd(1); long patientClinicNum = PIn.Long(DataCore.GetScalar(command)); if (patientClinicNum > 0) { return(GetFirstOrDefault(x => x.ClinicNum == patientClinicNum)); } //Patient does not have an assigned clinic. Grab the clinic from a scheduled or completed appointment with the largest date. command = @"SELECT appointment.ClinicNum,appointment.AptDateTime FROM appointment INNER JOIN recall ON appointment.PatNum=recall.PatNum AND recall.RecallNum=" + POut.Long(recallNum) + @" WHERE appointment.AptStatus IN (" + POut.Int((int)ApptStatus.Scheduled) + "," + POut.Int((int)ApptStatus.Complete) + ")" + @" ORDER BY AptDateTime DESC" ; command = DbHelper.LimitOrderBy(command, 1); long appointmentClinicNum = PIn.Long(DataCore.GetScalar(command)); if (appointmentClinicNum > 0) { return(GetFirstOrDefault(x => x.ClinicNum == appointmentClinicNum)); } return(null); }
public static DataTable GetCancelledScheduleDateByToothOrSurf(long PatNum, string ToothNum, string Surf) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), PatNum, ToothNum, Surf)); } string optionalclause = ""; if (POut.String(ToothNum) == "") { optionalclause = " AND procedurelog.Surf='" + POut.String(Surf) + "'"; } string command = "SELECT orionproc.DateScheduleBy,procedurelog.ToothNum,procedurelog.Surf " + "FROM orionproc " + "LEFT JOIN procedurelog ON orionproc.ProcNum=procedurelog.ProcNum " + "WHERE procedurelog.PatNum=" + POut.Long(PatNum) + " AND orionproc.Status2=128 " + " AND procedurelog.ToothNum='" + POut.String(ToothNum) + "'" + optionalclause + " AND " + DbHelper.Year("orionproc.DateScheduleBy") + ">1880" + " ORDER BY orionproc.DateScheduleBy "; command = DbHelper.LimitOrderBy(command, 1); return(Db.GetTable(command)); }
///<summary>Gets directly from the database. If the last event is a completed break, then it instead grabs the half-finished clock in. Other possibilities include half-finished clock in which truly was the last event, a finished clock in/out, a half-finished clock out for break, or null for a new employee.</summary> public static ClockEvent GetLastEvent(long employeeNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <ClockEvent>(MethodBase.GetCurrentMethod(), employeeNum)); } string command = "SELECT * FROM clockevent WHERE EmployeeNum=" + POut.Long(employeeNum) + " ORDER BY TimeDisplayed1 DESC"; command = DbHelper.LimitOrderBy(command, 1); ClockEvent ce = Crud.ClockEventCrud.SelectOne(command); if (ce != null && ce.ClockStatus == TimeClockStatus.Break && ce.TimeDisplayed2.Year > 1880) { command = "SELECT * FROM clockevent WHERE EmployeeNum=" + POut.Long(employeeNum) + " " + "AND ClockStatus != 2 " //not a break + "ORDER BY TimeDisplayed1 DESC"; command = DbHelper.LimitOrderBy(command, 1); ce = Crud.ClockEventCrud.SelectOne(command); return(ce); } else { return(ce); } }
///<summary>Gets most recent PayorType for a patient.</summary> public static PayorType GetCurrentType(long patNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <PayorType>(MethodBase.GetCurrentMethod(), patNum)); } string command = DbHelper.LimitOrderBy("SELECT * FROM payortype WHERE PatNum=" + POut.Long(patNum) + " ORDER BY DateStart DESC", 1); return(Crud.PayorTypeCrud.SelectOne(command)); }
///<summary>Used when viewing securityLog from the security admin window. PermTypes can be length 0 to get all types. ///Throws exceptions.</summary> public static SecurityLog[] Refresh(DateTime dateFrom, DateTime dateTo, Permissions permType, long patNum, long userNum, DateTime datePreviousFrom, DateTime datePreviousTo, int limit = 0) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <SecurityLog[]>(MethodBase.GetCurrentMethod(), dateFrom, dateTo, permType, patNum, userNum, datePreviousFrom, datePreviousTo, limit)); } string command = "SELECT securitylog.*,LName,FName,Preferred,MiddleI,LogHash FROM securitylog " + "LEFT JOIN patient ON patient.PatNum=securitylog.PatNum " + "LEFT JOIN securityloghash ON securityloghash.SecurityLogNum=securitylog.SecurityLogNum " + "WHERE LogDateTime >= " + POut.Date(dateFrom) + " " + "AND LogDateTime <= " + POut.Date(dateTo.AddDays(1)) + " " + "AND DateTPrevious >= " + POut.Date(datePreviousFrom) + " " + "AND DateTPrevious <= " + POut.Date(datePreviousTo.AddDays(1)); if (patNum != 0) { command += " AND securitylog.PatNum IN (" + string.Join(",", PatientLinks.GetPatNumsLinkedToRecursive(patNum, PatientLinkType.Merge).Select(x => POut.Long(x))) + ")"; } if (permType != Permissions.None) { command += " AND PermType=" + POut.Long((int)permType); } if (userNum != 0) { command += " AND UserNum=" + POut.Long(userNum); } command += " ORDER BY LogDateTime DESC"; //Using DESC so that the most recent ones appear in the list if (limit > 0) { command = DbHelper.LimitOrderBy(command, limit); } DataTable table = Db.GetTable(command); List <SecurityLog> listLogs = Crud.SecurityLogCrud.TableToList(table); for (int i = 0; i < listLogs.Count; i++) { if (table.Rows[i]["PatNum"].ToString() == "0") { listLogs[i].PatientName = ""; } else { listLogs[i].PatientName = table.Rows[i]["PatNum"].ToString() + "-" + Patients.GetNameLF(table.Rows[i]["LName"].ToString() , table.Rows[i]["FName"].ToString() , table.Rows[i]["Preferred"].ToString() , table.Rows[i]["MiddleI"].ToString()); } listLogs[i].LogHash = table.Rows[i]["LogHash"].ToString(); } return(listLogs.OrderBy(x => x.LogDateTime).ToArray()); }
///<summary>Gets one plannedAppt by patient, ordered by ItemOrder</summary> public static PlannedAppt GetOneOrderedByItemOrder(long patNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <PlannedAppt>(MethodBase.GetCurrentMethod(), patNum)); } string command = "SELECT * FROM plannedappt WHERE PatNum=" + POut.Long(patNum) + " ORDER BY ItemOrder"; command = DbHelper.LimitOrderBy(command, 1); return(Crud.PlannedApptCrud.SelectOne(command)); }
public static AsapComm GetByShortGuid(string shortGuid) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <AsapComm>(MethodBase.GetCurrentMethod(), shortGuid)); } string command = @"SELECT * FROM asapcomm WHERE asapcomm.ShortGUID='" + POut.String(shortGuid) + @"' ORDER BY asapcomm.DateTimeExpire DESC" ; command = DbHelper.LimitOrderBy(command, 1); //In the very unlikely event that the same short guid winds up in the database twice. return(Crud.AsapCommCrud.SelectOne(command)); }
///<summary>Returns any EtransMessageText where the MessageText is identical to the given messageText. ///Otherwise if none returns null.</summary> public static EtransMessageText GetMostRecentForType(EtransType type) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <EtransMessageText>(MethodBase.GetCurrentMethod(), type)); } string command = "SELECT etransmessagetext.* FROM etransmessagetext " + "INNER JOIN etrans ON etrans.EtransMessageTextNum=etransmessagetext.EtransMessageTextNum " + "WHERE Etype=" + POut.Int((int)type) + " " + "ORDER BY etrans.DateTimeTrans DESC"; command = DbHelper.LimitOrderBy(command, 1); //Most recent entry if any. return(Crud.EtransMessageTextCrud.SelectOne(command)); }
///<summary>Returns the last known status for the Listener Service. ///Returns Critical if a signal has not been entered in the last 5 minutes. ///Returns Error if there are ANY error signals that have not been processed.</summary> public static eServiceSignalSeverity GetListenerServiceStatus() { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <eServiceSignalSeverity>(MethodBase.GetCurrentMethod())); } //Additionally, this query will run a subselect to get the count of all unprocessed errors. //Running that query as a subselect here simply saves an extra call to the database. //This subselect should be fine to run here since the query is limited to one result and the count of unprocessed errors should be small. string command = "SELECT eservicesignal.*," //eservicesignal.* is required because we will manually call TableToList() later. + "(SELECT COUNT(*) FROM eservicesignal WHERE Severity=" + POut.Int((int)eServiceSignalSeverity.Error) + " AND IsProcessed=0) PendingErrors, " + DbHelper.Now() + " ServerTime " + "FROM eservicesignal WHERE ServiceCode=" + POut.Int((int)eServiceCode.ListenerService) + " " + "AND Severity IN(" + POut.Int((int)eServiceSignalSeverity.NotEnabled) + "," + POut.Int((int)eServiceSignalSeverity.Working) + "," + POut.Int((int)eServiceSignalSeverity.Error) + "," + POut.Int((int)eServiceSignalSeverity.Critical) + ") " + "ORDER BY SigDateTime DESC, Severity DESC "; command = DbHelper.LimitOrderBy(command, 1); DataTable table = Db.GetTable(command); List <EServiceSignal> listSignal = Crud.EServiceSignalCrud.TableToList(table); if (listSignal.Count == 0) //No signals means the eConnector has never run. Nothing to report. { return(eServiceSignalSeverity.None); } if (listSignal[0].Severity == eServiceSignalSeverity.NotEnabled) //NotEnabled means they don't care what the status is. Nothing to report. { return(eServiceSignalSeverity.NotEnabled); } DateTime dtNow = PIn.DateT(table.Rows[0]["ServerTime"].ToString()); if ( //eConnector exited gracefully and inserted its own critical signal. listSignal[0].Severity == eServiceSignalSeverity.Critical || //eConnector did not exit gracefully but has not inserted a heartbeat in at least 6 minutes. It is considered critical. //Listener is dropping a heartbeat every 5 minutes, so give 1 minute grace period to squelch race condition. listSignal[0].SigDateTime < dtNow.AddMinutes(-6)) { return(eServiceSignalSeverity.Critical); } //We need to flag the service monitor as Error if there are ANY pending errors. if (table.Rows[0]["PendingErrors"].ToString() != "0") { return(eServiceSignalSeverity.Error); } return(listSignal[0].Severity); }
///<summary>Returns the latest ErxLog entry for the specified patient and before the specified dateTimeMax. Can return null. ///Called from Chart when fetching prescriptions from NewCrop to determine the provider on incoming prescriptions.</summary> public static ErxLog GetLatestForPat(long patNum, DateTime dateTimeMax) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <ErxLog>(MethodBase.GetCurrentMethod(), patNum, dateTimeMax)); } string command = DbHelper.LimitOrderBy("SELECT * FROM erxlog WHERE PatNum=" + POut.Long(patNum) + " AND DateTStamp<" + POut.DateT(dateTimeMax) + " ORDER BY DateTStamp DESC", 1); List <ErxLog> listErxLog = Crud.ErxLogCrud.SelectMany(command); if (listErxLog.Count == 0) { return(null); } return(listErxLog[0]); }
/// <summary>Gets the referral number for this patient. If multiple, it returns the first one. If none, it returns 0. Does not consider referred To.</summary> public static long GetReferralNum(long patNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetLong(MethodBase.GetCurrentMethod(), patNum)); } string command = "SELECT ReferralNum " + "FROM refattach " + "WHERE refattach.PatNum =" + POut.Long(patNum) + " " + "AND refattach.IsFrom=1 " + "ORDER BY ItemOrder "; command = DbHelper.LimitOrderBy(command, 1); return(PIn.Long(Db.GetScalar(command))); }
public static ProcNote GetProcNotesForPat(long patNum, DateTime dateStart, DateTime dateEnd) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <ProcNote>(MethodBase.GetCurrentMethod(), patNum, dateStart, dateEnd)); } string query = "SELECT procnote.* FROM procnote " + "INNER JOIN procedurelog ON procedurelog.ProcNum=procnote.ProcNum " + "WHERE procnote.PatNum=" + POut.Long(patNum) + " " + "AND procnote.EntryDateTime BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + " " + "AND procedurelog.ProcStatus!=" + POut.Int((int)ProcStat.D) + " " + "ORDER BY procnote.EntryDateTime DESC"; string command = DbHelper.LimitOrderBy(query, 1); return(Crud.ProcNoteCrud.SelectOne(command)); }
///<summary>returns all eServiceSignals for a given service within the date range, inclusive.</summary> public static List <EServiceSignal> GetServiceHistory(eServiceCode serviceCode, DateTime dateStart, DateTime dateStop, int limit = 0) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <EServiceSignal> >(MethodBase.GetCurrentMethod(), serviceCode, dateStart, dateStop, limit)); } string command = "SELECT * FROM eservicesignal " + "WHERE ServiceCode=" + POut.Int((int)serviceCode) + " " + "AND SigDateTime BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateStop.Date.AddDays(1)) + " " + "ORDER BY SigDateTime DESC, Severity DESC"; if (limit > 0) { command = DbHelper.LimitOrderBy(command, limit); } return(Crud.EServiceSignalCrud.SelectMany(command)); }
///<summary>Updates the employee's ClockStatus if necessary based on their clock events. This method handles future clock events as having ///already occurred. Ex: If I clock out for home at 6:00 but edit my time card to say 7:00, at 6:30 my status will say Home.</summary> public static void UpdateClockStatus(long employeeNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { Meth.GetVoid(MethodBase.GetCurrentMethod(), employeeNum); return; } //Get the last clockevent for the employee. Will include clockevent with "in" before NOW, and "out" anytime before 23:59:59 of TODAY. string command = @"SELECT * FROM clockevent WHERE TimeDisplayed2<=" + DbHelper.DateAddSecond(DbHelper.DateAddDay(DbHelper.Curdate(), "1"), "-1") + " AND TimeDisplayed1<=" + DbHelper.Now() + @" AND EmployeeNum=" + POut.Long(employeeNum) + @" ORDER BY IF(YEAR(TimeDisplayed2) < 1880,TimeDisplayed1,TimeDisplayed2) DESC" ; command = DbHelper.LimitOrderBy(command, 1); ClockEvent clockEvent = Crud.ClockEventCrud.SelectOne(command); Employee employee = GetEmp(employeeNum); Employee employeeOld = employee.Copy(); if (clockEvent != null && clockEvent.TimeDisplayed2 > DateTime.Now) //Future time manual clock out. { employee.ClockStatus = Lans.g("ContrStaff", "Manual Entry"); } else if (clockEvent == null || //Employee has never clocked in (clockEvent.TimeDisplayed2.Year > 1880 && clockEvent.ClockStatus == TimeClockStatus.Home)) //Clocked out for home { employee.ClockStatus = Lans.g("enumTimeClockStatus", TimeClockStatus.Home.ToString()); } else if (clockEvent.TimeDisplayed2.Year > 1880 && clockEvent.ClockStatus == TimeClockStatus.Lunch) //Clocked out for lunch { employee.ClockStatus = Lans.g("enumTimeClockStatus", TimeClockStatus.Lunch.ToString()); } else if (clockEvent.TimeDisplayed1.Year > 1880 && clockEvent.TimeDisplayed2.Year < 1880 && clockEvent.ClockStatus == TimeClockStatus.Break) { employee.ClockStatus = Lans.g("enumTimeClockStatus", TimeClockStatus.Break.ToString()); } else if (clockEvent.TimeDisplayed2.Year > 1880 && clockEvent.ClockStatus == TimeClockStatus.Break) //Clocked back in from break { employee.ClockStatus = Lans.g("ContrStaff", "Working"); } else //The employee has not clocked out yet. { employee.ClockStatus = Lans.g("ContrStaff", "Working"); } Crud.EmployeeCrud.Update(employee, employeeOld); }
///<summary>Returns the last known status for the given eService.</summary> public static eServiceSignalSeverity GetServiceStatus(eServiceCode serviceCode) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <eServiceSignalSeverity>(MethodBase.GetCurrentMethod(), serviceCode)); } //The only statuses within the eServiceSignalSeverity enum are NotEnabled, Working, and Critical. //All other statuses are used for logging purposes and should not be considered within this method. string command = "SELECT * FROM eservicesignal WHERE ServiceCode=" + POut.Int((int)serviceCode) + " " + "ORDER BY SigDateTime DESC, Severity DESC "; command = DbHelper.LimitOrderBy(command, 1); List <EServiceSignal> listSignal = Crud.EServiceSignalCrud.SelectMany(command); if (listSignal.Count == 0) { //NoSignals exist for this service. return(eServiceSignalSeverity.None); } return(listSignal[0].Severity); }
///<summary>Will return null if no picture for this patient.</summary> public static Document GetPatPictFromDb(long patNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <Document>(MethodBase.GetCurrentMethod(), patNum)); } //first establish which category pat pics are in long defNumPicts = 0; Def[] defs = DefC.GetList(DefCat.ImageCats); for (int i = 0; i < defs.Length; i++) { if (Regex.IsMatch(defs[i].ItemValue, @"P")) { defNumPicts = defs[i].DefNum; break; } } if (defNumPicts == 0) //no category set for picts { return(null); } //then find, limit 1 to get the most recent string command = "SELECT * FROM document " + "WHERE document.PatNum=" + POut.Long(patNum) + " AND document.DocCategory=" + POut.Long(defNumPicts) + " ORDER BY DateCreated DESC"; command = DbHelper.LimitOrderBy(command, 1); DataTable table = Db.GetTable(command); Document[] pictureDocs = Fill(table); if (pictureDocs == null || pictureDocs.Length < 1) //no pictures { return(null); } return(pictureDocs[0]); }
///<summary>If not using clinics, or for all clinics with clinics enabled, supply an empty list of clinicNums. If the user is restricted, for all ///clinics supply only those clinics for which the user has permission to access, otherwise it will be run for all clinics.</summary> public static DataTable GetPatTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, List <long> listPatientTypes, bool hasAllProvs, bool hasAllClinics, bool hasPatientTypes, bool isGroupedByPatient, bool isUnearnedIncluded, bool doShowProvSeparate, bool doShowHiddenTPUnearned) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listPatientTypes, hasAllProvs, hasAllClinics, hasPatientTypes, isGroupedByPatient, isUnearnedIncluded, doShowProvSeparate, doShowHiddenTPUnearned)); } //reports should no longer use the cache bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache); List <long> listHiddenUnearnedDefNums = new List <long>(); if (!doShowHiddenTPUnearned) { listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() => Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList() ); } //patient payments----------------------------------------------------------------------------------------- //the selected columns have to remain in this order due to the way the report complex populates the returned sheet string queryPat = "SELECT payment.PayDate DatePay," + "MAX(" + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ") lfname,GROUP_CONCAT(DISTINCT provider.Abbr),"; if (hasClinicsEnabled) { queryPat += "clinic.Abbr clinicAbbr,"; } queryPat += "payment.CheckNum,SUM(COALESCE(paysplit.SplitAmt,0)) amt,payment.PayNum,ItemName,payment.PayType " + "FROM payment " + "LEFT JOIN paysplit ON payment.PayNum=paysplit.PayNum " + "LEFT JOIN patient ON payment.PatNum=patient.PatNum " + "LEFT JOIN provider ON paysplit.ProvNum=provider.ProvNum " + "LEFT JOIN definition ON payment.PayType=definition.DefNum "; if (hasClinicsEnabled) { queryPat += "LEFT JOIN clinic ON clinic.ClinicNum=paysplit.ClinicNum "; } queryPat += "WHERE payment.PayDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " "; if (hasClinicsEnabled && listClinicNums.Count > 0) { queryPat += "AND paysplit.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") "; } if (!hasAllProvs && listProvNums.Count > 0) { queryPat += "AND paysplit.ProvNum IN(" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") "; } if (!hasPatientTypes && listPatientTypes.Count > 0) { queryPat += "AND payment.PayType IN (" + string.Join(",", listPatientTypes.Select(x => POut.Long(x))) + ") "; } if (!isUnearnedIncluded) //UnearnedType of 0 means the paysplit is NOT unearned { queryPat += "AND paysplit.UnearnedType=0 "; } else if (listHiddenUnearnedDefNums.Count > 0 && !doShowHiddenTPUnearned) //Include unearned but not of the TP type. { queryPat += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) "; } queryPat += "GROUP BY payment.PayNum,payment.PayDate,payment.CheckNum,definition.ItemName,payment.PayType "; if (doShowProvSeparate) { queryPat += ",provider.ProvNum "; } if (hasClinicsEnabled) { queryPat += ",clinic.Abbr "; } if (isGroupedByPatient) { queryPat += ",patient.PatNum "; } queryPat += "ORDER BY payment.PayType,payment.PayDate,lfname"; if (!hasPatientTypes && listPatientTypes.Count == 0) { queryPat = DbHelper.LimitOrderBy(queryPat, 0); } return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryPat))); }
///<summary>If not using clinics then supply an empty list of clinicNums. listClinicNums must have at least one item if using clinics.</summary> public static DataTable GetInsTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, List <long> listInsuranceTypes, List <long> listClaimPayGroups, bool hasAllProvs, bool hasAllClinics, bool hasInsuranceTypes, bool isGroupedByPatient, bool hasAllClaimPayGroups, bool doShowProvSeparate) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listInsuranceTypes, listClaimPayGroups, hasAllProvs, hasAllClinics, hasInsuranceTypes, isGroupedByPatient, hasAllClaimPayGroups, doShowProvSeparate)); } string whereProv = ""; if (!hasAllProvs) { whereProv += " AND claimproc.ProvNum IN("; for (int i = 0; i < listProvNums.Count; i++) { if (i > 0) { whereProv += ","; } whereProv += POut.Long(listProvNums[i]); } whereProv += ") "; } string whereClin = ""; //reports should no longer use the cache bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache); if (hasClinicsEnabled) { whereClin += " AND claimproc.ClinicNum IN("; for (int i = 0; i < listClinicNums.Count; i++) { if (i > 0) { whereClin += ","; } whereClin += POut.Long(listClinicNums[i]); } whereClin += ") "; } string whereClaimPayGroup = ""; if (!hasAllClaimPayGroups) { whereClaimPayGroup = " AND PayGroup IN (" + String.Join(",", listClaimPayGroups) + ") "; } string queryIns = @"SELECT claimproc.DateCP,carrier.CarrierName,MAX(" + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + @") lfname,GROUP_CONCAT(DISTINCT provider.Abbr) Provider, "; if (hasClinicsEnabled) { queryIns += "clinic.Abbr Clinic, "; } queryIns += @"claimpayment.CheckNum,SUM(claimproc.InsPayAmt) amt,claimproc.ClaimNum,claimpayment.PayType,COUNT(DISTINCT claimproc.PatNum) countPats FROM claimproc LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum LEFT JOIN patient ON claimproc.PatNum = patient.PatNum LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum LEFT JOIN provider ON provider.ProvNum=claimproc.ProvNum LEFT JOIN claimpayment ON claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum " ; if (hasClinicsEnabled) { queryIns += "LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum "; } queryIns += "WHERE (claimproc.Status=1 OR claimproc.Status=4) " //received or supplemental + whereProv + whereClin + whereClaimPayGroup + "AND claimpayment.CheckDate >= " + POut.Date(dateFrom) + " " + "AND claimpayment.CheckDate <= " + POut.Date(dateTo) + " "; if (!hasInsuranceTypes && listInsuranceTypes.Count > 0) { queryIns += "AND claimpayment.PayType IN ("; for (int i = 0; i < listInsuranceTypes.Count; i++) { if (i > 0) { queryIns += ","; } queryIns += POut.Long(listInsuranceTypes[i]); } queryIns += ") "; } queryIns += @"GROUP BY claimproc.DateCP,claimproc.ClaimPaymentNum,"; if (doShowProvSeparate) { queryIns += @"provider.ProvNum,"; } if (hasClinicsEnabled) { queryIns += "claimproc.ClinicNum,clinic.Abbr,"; } queryIns += "carrier.CarrierName,claimpayment.CheckNum"; if (isGroupedByPatient) { queryIns += ",patient.PatNum"; } queryIns += " ORDER BY claimpayment.PayType,claimproc.DateCP,lfname"; if (!hasInsuranceTypes && listInsuranceTypes.Count == 0) { queryIns = DbHelper.LimitOrderBy(queryIns, 0); } DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryIns)); foreach (DataRow row in table.Rows) { //If there is more than one patient attached to a check, we will append an asterisk to the end. int countPats = PIn.Int(row["countPats"].ToString()); if (countPats > 1) { row["lfname"] = row["lfname"].ToString().TrimEnd() + "*"; } } table.Columns.Remove("countPats"); //Remove this column because we don't want it to show on the report return(table); }
///<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>Used when viewing securityLog from the security admin window. PermTypes can be length 0 to get all types. ///Throws exceptions.</summary> public static SecurityLog[] Refresh(DateTime dateFrom, DateTime dateTo, Permissions permType, long patNum, long userNum, DateTime datePreviousFrom, DateTime datePreviousTo, bool includeArchived, int limit = 0) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <SecurityLog[]>(MethodBase.GetCurrentMethod(), dateFrom, dateTo, permType, patNum, userNum, datePreviousFrom, datePreviousTo, includeArchived, limit)); } string command = "SELECT securitylog.*,LName,FName,Preferred,MiddleI,LogHash FROM securitylog " + "LEFT JOIN patient ON patient.PatNum=securitylog.PatNum " + "LEFT JOIN securityloghash ON securityloghash.SecurityLogNum=securitylog.SecurityLogNum " + "WHERE LogDateTime >= " + POut.Date(dateFrom) + " " + "AND LogDateTime <= " + POut.Date(dateTo.AddDays(1)) + " " + "AND DateTPrevious >= " + POut.Date(datePreviousFrom) + " " + "AND DateTPrevious <= " + POut.Date(datePreviousTo.AddDays(1)); if (patNum != 0) { command += " AND securitylog.PatNum IN (" + string.Join(",", PatientLinks.GetPatNumsLinkedToRecursive(patNum, PatientLinkType.Merge).Select(x => POut.Long(x))) + ")"; } if (permType != Permissions.None) { command += " AND PermType=" + POut.Long((int)permType); } if (userNum != 0) { command += " AND UserNum=" + POut.Long(userNum); } command += " ORDER BY LogDateTime DESC"; //Using DESC so that the most recent ones appear in the list if (limit > 0) { command = DbHelper.LimitOrderBy(command, limit); } DataTable table = Db.GetTable(command); List <SecurityLog> listLogs = Crud.SecurityLogCrud.TableToList(table); for (int i = 0; i < listLogs.Count; i++) { if (table.Rows[i]["PatNum"].ToString() == "0") { listLogs[i].PatientName = ""; } else { listLogs[i].PatientName = table.Rows[i]["PatNum"].ToString() + "-" + Patients.GetNameLF(table.Rows[i]["LName"].ToString() , table.Rows[i]["FName"].ToString() , table.Rows[i]["Preferred"].ToString() , table.Rows[i]["MiddleI"].ToString()); } listLogs[i].LogHash = table.Rows[i]["LogHash"].ToString(); } if (includeArchived) { //This will purposefully throw exceptions. DataTable tableArchive = MiscData.RunFuncOnArchiveDatabase <DataTable>(() => { return(Db.GetTable(command)); }); List <SecurityLog> listLogsArchive = Crud.SecurityLogCrud.TableToList(tableArchive); Dictionary <long, Patient> dictPats = Patients.GetMultPats(listLogsArchive.Select(x => x.PatNum).Distinct().ToList()) .ToDictionary(x => x.PatNum); for (int i = 0; i < listLogsArchive.Count; i++) { Patient pat; if (listLogsArchive[i].PatNum == 0 || !dictPats.TryGetValue(listLogsArchive[i].PatNum, out pat)) { listLogsArchive[i].PatientName = ""; } else { listLogsArchive[i].PatientName = listLogsArchive[i].PatNum + "-" + pat.GetNameLF(); } listLogsArchive[i].LogHash = tableArchive.Rows[i]["LogHash"].ToString(); } listLogs.AddRange(listLogsArchive); //Add archived entries to returned list. } return(listLogs.OrderBy(x => x.LogDateTime).ToArray()); }
///<summary>Used when viewing securityLog from the security admin window. PermTypes can be length 0 to get all types.</summary> public static SecurityLog[] Refresh(DateTime dateFrom, DateTime dateTo, Permissions permType, long patNum, long userNum, DateTime datePreviousFrom, DateTime datePreviousTo, bool includeArchived, int limit = 0) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <SecurityLog[]>(MethodBase.GetCurrentMethod(), dateFrom, dateTo, permType, patNum, userNum, datePreviousFrom, datePreviousTo, includeArchived, limit)); } string command = "SELECT securitylog.*,LName,FName,Preferred,MiddleI,LogHash FROM securitylog " + "LEFT JOIN patient ON patient.PatNum=securitylog.PatNum " + "LEFT JOIN securityloghash ON securityloghash.SecurityLogNum=securitylog.SecurityLogNum " + "WHERE LogDateTime >= " + POut.Date(dateFrom) + " " + "AND LogDateTime <= " + POut.Date(dateTo.AddDays(1)) + " " + "AND DateTPrevious >= " + POut.Date(datePreviousFrom) + " " + "AND DateTPrevious <= " + POut.Date(datePreviousTo.AddDays(1)); if (patNum != 0) { command += " AND securitylog.PatNum IN (" + string.Join(",", PatientLinks.GetPatNumsLinkedToRecursive(patNum, PatientLinkType.Merge).Select(x => POut.Long(x))) + ")"; } if (permType != Permissions.None) { command += " AND PermType=" + POut.Long((int)permType); } if (userNum != 0) { command += " AND UserNum=" + POut.Long(userNum); } command += " ORDER BY LogDateTime DESC"; //Using DESC so that the most recent ones appear in the list if (limit > 0) { command = DbHelper.LimitOrderBy(command, limit); } DataTable table = Db.GetTable(command); List <SecurityLog> listLogs = Crud.SecurityLogCrud.TableToList(table); for (int i = 0; i < listLogs.Count; i++) { if (table.Rows[i]["PatNum"].ToString() == "0") { listLogs[i].PatientName = ""; } else { listLogs[i].PatientName = table.Rows[i]["PatNum"].ToString() + "-" + Patients.GetNameLF(table.Rows[i]["LName"].ToString() , table.Rows[i]["FName"].ToString() , table.Rows[i]["Preferred"].ToString() , table.Rows[i]["MiddleI"].ToString()); } listLogs[i].LogHash = table.Rows[i]["LogHash"].ToString(); } if (includeArchived && dateFrom <= PrefC.GetDate(PrefName.ArchiveDate)) //They are attempting to find security logs that are prior to archived date. { string decryptedPass; CDT.Class1.Decrypt(PrefC.GetString(PrefName.ArchivePassHash), out decryptedPass); string connectionStrOrig = DataConnection.GetCurrentConnectionString(); DatabaseType dbTypeOrig = DataConnection.DBtype; DataConnection dcon = new DataConnection(); //Connect to archive database dcon.SetDb(PrefC.GetString(PrefName.ArchiveServerName) == ""?PrefC.GetString(PrefName.ArchiveServerURI):PrefC.GetString(PrefName.ArchiveServerName), "opendentalarchive", PrefC.GetString(PrefName.ArchiveUserName), decryptedPass, "", "", dbTypeOrig); DataTable tableArchive = Db.GetTable(command); //Query the archive List <SecurityLog> listLogsArchive = Crud.SecurityLogCrud.TableToList(tableArchive); dcon.SetDb(connectionStrOrig, "", dbTypeOrig); //Reconnect to initial db Dictionary <long, Patient> dictPats = Patients.GetMultPats(listLogsArchive.Select(x => x.PatNum).Distinct().ToList()) .ToDictionary(x => x.PatNum); for (int i = 0; i < listLogsArchive.Count; i++) { Patient pat; if (listLogsArchive[i].PatNum == 0 || !dictPats.TryGetValue(listLogsArchive[i].PatNum, out pat)) { listLogsArchive[i].PatientName = ""; } else { listLogsArchive[i].PatientName = listLogsArchive[i].PatNum + "-" + pat.GetNameLF(); } listLogsArchive[i].LogHash = tableArchive.Rows[i]["LogHash"].ToString(); } listLogs.AddRange(listLogsArchive); //Add archived entries to returned list. } return(listLogs.OrderBy(x => x.LogDateTime).ToArray()); }
///<summary>If not using clinics then supply an empty list of clinicNums. listClinicNums must have at least one item if using clinics.</summary> public static DataTable GetInsTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, List <long> listInsuranceTypes, List <long> listClaimPayGroups, bool hasAllProvs, bool hasAllClinics, bool hasInsuranceTypes, bool isGroupedByPatient, bool hasAllClaimPayGroups, bool doShowProvSeparate) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listInsuranceTypes, listClaimPayGroups, hasAllProvs, hasAllClinics, hasInsuranceTypes, isGroupedByPatient, hasAllClaimPayGroups, doShowProvSeparate)); } string whereProv = ""; if (!hasAllProvs) { whereProv += " AND claimproc.ProvNum IN("; for (int i = 0; i < listProvNums.Count; i++) { if (i > 0) { whereProv += ","; } whereProv += POut.Long(listProvNums[i]); } whereProv += ") "; } string whereClin = ""; //reports should no longer use the cache bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics))); if (hasClinicsEnabled) { whereClin += " AND claimproc.ClinicNum IN("; for (int i = 0; i < listClinicNums.Count; i++) { if (i > 0) { whereClin += ","; } whereClin += POut.Long(listClinicNums[i]); } whereClin += ") "; } string whereClaimPayGroup = ""; if (!hasAllClaimPayGroups) { whereClaimPayGroup = " AND PayGroup IN (" + String.Join(",", listClaimPayGroups) + ") "; } string queryIns = @"SELECT claimproc.DateCP,carrier.CarrierName,MAX(" + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + @") lfname,GROUP_CONCAT(DISTINCT provider.Abbr) Provider, "; if (hasClinicsEnabled) { queryIns += "clinic.Description clinicDesc, "; } queryIns += @"claimpayment.CheckNum,SUM(claimproc.InsPayAmt) amt,claimproc.ClaimNum,claimpayment.PayType FROM claimproc LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum LEFT JOIN patient ON claimproc.PatNum = patient.PatNum LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum LEFT JOIN provider ON provider.ProvNum=claimproc.ProvNum LEFT JOIN claimpayment ON claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum " ; if (hasClinicsEnabled) { queryIns += "LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum "; } queryIns += "WHERE (claimproc.Status=1 OR claimproc.Status=4) " //received or supplemental + whereProv + whereClin + whereClaimPayGroup + "AND claimpayment.CheckDate >= " + POut.Date(dateFrom) + " " + "AND claimpayment.CheckDate <= " + POut.Date(dateTo) + " "; if (!hasInsuranceTypes && listInsuranceTypes.Count > 0) { queryIns += "AND claimpayment.PayType IN ("; for (int i = 0; i < listInsuranceTypes.Count; i++) { if (i > 0) { queryIns += ","; } queryIns += POut.Long(listInsuranceTypes[i]); } queryIns += ") "; } queryIns += @"GROUP BY claimproc.DateCP,claimproc.ClaimPaymentNum,"; if (doShowProvSeparate) { queryIns += @"provider.ProvNum,"; } if (hasClinicsEnabled) { queryIns += "claimproc.ClinicNum,clinic.Description,"; } queryIns += "carrier.CarrierName,claimpayment.CheckNum"; if (isGroupedByPatient) { queryIns += ",patient.PatNum"; } queryIns += " ORDER BY claimpayment.PayType,claimproc.DateCP,lfname"; if (!hasInsuranceTypes && listInsuranceTypes.Count == 0) { queryIns = DbHelper.LimitOrderBy(queryIns, 0); } return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryIns))); }