///<summary></summary> public static List <BugSubmission> GetAllInRange(DateTime dateFrom, DateTime dateTo, List <string> listVersionFilters = null, bool useConnectionStore = false) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <BugSubmission> >(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listVersionFilters, useConnectionStore)); } bool hasSelections = (!listVersionFilters.IsNullOrEmpty()); List <BugSubmission> listBugSubs = new List <BugSubmission>(); DataAction.RunBugsHQ(() => { string command = $@"SELECT * FROM bugsubmission WHERE {DbHelper.BetweenDates("SubmissionDateTime",dateFrom,dateTo)} {(hasSelections ? $@" AND ({string.Join(" OR ",listVersionFilters.Select(x => (x=="Mobile" ? "DbInfoJson like '%\"DeviceId\":%' and DbInfoJson NOT LIKE '%\"DeviceId\":null%'" : "DbVersion LIKE '"+POut.String(x)+"%'" ))) })" : "" )} " ; listBugSubs = Crud.BugSubmissionCrud.SelectMany(command); }, useConnectionStore); return(listBugSubs); }
public static DataTable GetReportData(List <long> listProvNums, List <long> listUnearnedTypeDefNums, List <long> listClinicNums , bool hasClinicsEnabled, DateTime dateFrom, DateTime dateTo) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), listProvNums, listUnearnedTypeDefNums, listClinicNums, hasClinicsEnabled, dateFrom, dateTo)); } string command = $@"SELECT paysplit.DatePay, CONCAT(patient.LName,', ',patient.FName),provider.Abbr," ; if (hasClinicsEnabled) { command += "clinic.Abbr,"; } command += $@"procedurecode.ProcCode, procedurecode.Descript,paysplit.SplitAmt FROM paysplit INNER JOIN definition ON definition.DefNum=paysplit.UnearnedType AND definition.ItemValue!='' INNER JOIN patient ON patient.PatNum=paysplit.PatNum LEFT JOIN procedurelog ON procedurelog.ProcNum=paysplit.ProcNum LEFT JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum LEFT JOIN provider ON provider.ProvNum=paysplit.ProvNum LEFT JOIN clinic ON clinic.ClinicNum=paysplit.ClinicNum WHERE paysplit.ProvNum IN ({string.Join(",",listProvNums)}) AND paysplit.UnearnedType IN ({string.Join(",",listUnearnedTypeDefNums)}) AND {DbHelper.BetweenDates("paysplit.DatePay",dateFrom,dateTo)} " ; if (listClinicNums.Count > 0) { command += $"AND paysplit.ClinicNum IN ({string.Join(",",listClinicNums)}) "; } command += "ORDER BY paysplit.DatePay,patient.LName,patient.FName"; return(Db.GetTable(command)); }
public static DataTable GetMissingPaymentsTable(DateTime dateStart, DateTime dateEnd) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); } string command = "SELECT xchargetransaction.* " + "FROM xchargetransaction " + "WHERE " + DbHelper.BetweenDates("TransactionDateTime", dateStart, dateEnd) + " " + "AND xchargetransaction.ResultCode=0"; //Valid entries to count have result code 0 List <XChargeTransaction> listTrans = Crud.XChargeTransactionCrud.SelectMany(command); command = "SELECT payment.* " + "FROM payment " //only payments with the same PaymentType as the X-Charge PaymentType for the clinic + "INNER JOIN (" + "SELECT ClinicNum,PropertyValue PaymentType FROM programproperty " + "WHERE ProgramNum=" + POut.Long(Programs.GetProgramNum(ProgramName.Xcharge)) + " AND PropertyDesc='PaymentType'" + ") paytypes ON paytypes.ClinicNum=payment.ClinicNum AND paytypes.PaymentType=payment.PayType " + "WHERE DateEntry BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd); List <Payment> listPays = Crud.PaymentCrud.SelectMany(command); for (int i = listTrans.Count - 1; i >= 0; i--) //Looping backwards in order to remove items { XChargeTransaction tran = listTrans[i]; Payment pay = listPays.Where(x => x.PatNum == tran.PatNum) .Where(x => x.DateEntry.Date == tran.TransactionDateTime.Date) .Where(x => x.PayAmt.Equals(tran.Amount)) .FirstOrDefault(); if (pay == null) //The XCharge transaction does not have a corresponding payment. { continue; } listTrans.RemoveAt(i); listPays.Remove(pay); //So that the same payment does not get counted for more than one XCharge transaction. } DataTable table = Crud.XChargeTransactionCrud.ListToTable(listTrans); List <string> listColumnsToKeep = new List <string> { "TransactionDateTime", "TransType", "ClerkID", "ItemNum", "PatNum", "CreditCardNum", "Expiration", "Result", "Amount" }; //Remove columns we don't want. for (int i = table.Columns.Count - 1; i >= 0; i--) { if (table.Columns[i].ColumnName.In(listColumnsToKeep)) { continue; } table.Columns.RemoveAt(i); } //Reorder the column in the order we want them. for (int i = 0; i < listColumnsToKeep.Count; i++) { table.Columns[listColumnsToKeep[i]].SetOrdinal(i); } return(table); }
///<summary>Returns a list of bug submissions and their corresponding bugs. ///If a bugsubmission is not associated to a bug then the BugObj field on the bugsubmission object will be null. ///Performs grouping logic in order to minimize the amount of bugsubmissions in the return results.</summary> public static List <BugSubmission> GetBugSubsForRegKeys(List <string> listRegKeys, DateTime dateFrom, DateTime dateTo) { if (listRegKeys == null || listRegKeys.Count == 0) { return(new List <BugSubmission>()); //No point in going through middle tier. } if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <BugSubmission> >(MethodBase.GetCurrentMethod(), listRegKeys, dateFrom, dateTo)); } List <BugSubmission> listRetVals = new List <BugSubmission>(); DataAction.RunBugsHQ(() => { string command = "SELECT * FROM bugsubmission " + "LEFT JOIN bug ON bug.BugId=bugsubmission.BugId " + "WHERE bugsubmission.RegKey IN(" + string.Join(",", listRegKeys.Select(x => "'" + POut.String(x) + "'")) + ") " + "AND " + DbHelper.BetweenDates("SubmissionDateTime", dateFrom, dateTo) + " " + "ORDER BY bug.CreationDate DESC, bugsubmission.SubmissionDateTime DESC"; //The query selects all columns for the bugsubmission and bug tables in one query. Hopefully we never have conflicting columns that differ. DataTable table = Db.GetTable(command); //Make a clone of the table structure for the bug objects and only fill it with entries where the BugId row is valid. DataTable tableBugs = table.Clone(); foreach (DataRow row in table.Select().Where(x => PIn.Long(x["BugId"].ToString(), false) != 0)) { tableBugs.ImportRow(row); } //Extract all of the bug objects from the subset table. List <Bug> listBugs = Crud.BugCrud.TableToList(tableBugs); //Extract all of the bugsubmission objects from the results. List <BugSubmission> listBugSubs = Crud.BugSubmissionCrud.TableToList(table); //Associate any bug object with its corresponding bugsubmission object. listBugSubs.ForEach(x => x.BugObj = listBugs.FirstOrDefault(y => y.BugId == x.BugId)); //Group the bug submissions by RegKey, ExceptionStackTrace, and BugId. //Each grouping will be ordered by ProgramVersion individually. //Take the first bugsubmission from each group and add it to listGroupedBugSubs for the return value. foreach (BugSubmission bugSub in listBugSubs) { if (bugSub.TagCustom != null) //Used to skip already considered bug submissions via grouping logic. { continue; } List <BugSubmission> listGroupedSubs = listBugSubs.FindAll(x => x.RegKey == bugSub.RegKey && x.ExceptionStackTrace == bugSub.ExceptionStackTrace && x.BugId == bugSub.BugId); listGroupedSubs.ForEach(x => x.TagCustom = true); //Flag all bugsubmissions in this group as handled. //Only add the most pertinent item from the group to our return value. listRetVals.Add(listGroupedSubs.OrderByDescending(x => new Version(x.TryGetPrefValue(PrefName.ProgramVersion, "0.0.0.0"))).First()); } }, false); return(listRetVals); }
///<summary></summary> public static List <BugSubmission> GetAllInRange(DateTime dateFrom, DateTime dateTo) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <BugSubmission> >(MethodBase.GetCurrentMethod(), dateFrom, dateTo)); } List <BugSubmission> listBugSubs = new List <BugSubmission>(); DataAction.RunBugsHQ(() => { string command = "SELECT * FROM bugsubmission WHERE " + DbHelper.BetweenDates("SubmissionDateTime", dateFrom, dateTo); listBugSubs = Crud.BugSubmissionCrud.TableToList(DataCore.GetTable(command)); }, false); return(listBugSubs); }
///<summary>Returns a list of bug submissions and any associated bugs. ///If a bugSubmission is not associated to a bug then bug entry will be null.</summary> public static List <Tuple <BugSubmission, Bug> > GetBugSubsForRegKeys(List <string> listRegKeys, DateTime dateFrom, DateTime dateTo) { if (listRegKeys == null || listRegKeys.Count == 0) { return(new List <Tuple <BugSubmission, Bug> >()); //No point in going through middle tier. } if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <Tuple <BugSubmission, Bug> > >(MethodBase.GetCurrentMethod(), listRegKeys, dateFrom, dateTo)); } List <long> listBugIDs = new List <long>(); List <Tuple <BugSubmission, Bug> > listSubBugs = new List <Tuple <BugSubmission, Bug> >(); DataAction.RunBugsHQ(() => { string command = "SELECT * FROM bugsubmission " + "LEFT JOIN bug ON bug.BugId=bugsubmission.BugId " + "WHERE bugsubmission.RegKey IN(" + string.Join(",", listRegKeys.Select(x => "'" + POut.String(x) + "'")) + ") " + "AND " + DbHelper.BetweenDates("SubmissionDateTime", dateFrom, dateTo) + " " + "ORDER BY bug.CreationDate DESC, bugsubmission.SubmissionDateTime DESC"; DataTable table = DataCore.GetTable(command); DataTable tableBugs = table.Clone(); //Clones structure, not row data. foreach (DataRow row in table.Rows) { if (PIn.Long(row["BugId"].ToString(), false) == 0) { continue; //Do not import rows that do not have bugs, LEFT JOIN bug must be successful. } tableBugs.ImportRow(row); } List <Bug> listBugs = Crud.BugCrud.TableToList(tableBugs); List <BugSubmission> listBugSubs = Crud.BugSubmissionCrud.TableToList(table); foreach (BugSubmission sub in listBugSubs) { if (sub.TagOD != null) { continue; //Already handled. } //Grouping mimics FormBugSubmissions.cs List <BugSubmission> listGroupedSubs = listBugSubs.FindAll(x => x.RegKey == sub.RegKey && x.ExceptionStackTrace == sub.ExceptionStackTrace && x.BugId == sub.BugId); listGroupedSubs.ForEach(x => x.TagOD = true); //Used to skip already considered bug submissions via grouping logic. BugSubmission subMax = listGroupedSubs.OrderByDescending(x => new Version(x.Info.DictPrefValues[PrefName.ProgramVersion])).First(); Bug bug = listBugs.FirstOrDefault(x => x.BugId == subMax.BugId); //Can be null. listSubBugs.Add(new Tuple <BugSubmission, Bug>(subMax, bug)); } }, false); return(listSubBugs); }
///<summary>Gets a list of AsapComms (along with a few more fields) for use in the Web Sched History window. To view for all patients or clinics, ///pass in null for those parameters.</summary> public static List <AsapCommHist> GetHist(DateTime dateFrom, DateTime dateTo, List <long> listPatNums = null, List <long> listClinicNums = null) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <AsapCommHist> >(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listPatNums, listClinicNums)); } string command = @" SELECT asapcomm.*," + DbHelper.Concat("patient.LName", "', '", "patient.FName") + @" PatientName,COALESCE(schedule.StartTime,'00:00:00') StartTime, COALESCE(schedule.StopTime,'00:00:00') StopTime,COALESCE(schedule.SchedDate,'0001-01-01') SchedDate, COALESCE(emailmessage.BodyText,'') EmailMessageText,COALESCE(smstomobile.MsgText,'') SMSMessageText FROM asapcomm INNER JOIN patient ON patient.PatNum=asapcomm.PatNum LEFT JOIN schedule ON schedule.ScheduleNum=asapcomm.ScheduleNum LEFT JOIN emailmessage ON emailmessage.EmailMessageNum=asapcomm.EmailMessageNum LEFT JOIN smstomobile ON smstomobile.GuidMessage=asapcomm.GuidMessageToMobile WHERE " + DbHelper.BetweenDates("asapcomm.DateTimeEntry", dateFrom, dateTo) + " "; if (listPatNums != null) { if (listPatNums.Count == 0) { return(new List <AsapCommHist>()); } command += "AND asapcomm.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ") "; } if (listClinicNums != null) { if (listClinicNums.Count == 0) { return(new List <AsapCommHist>()); } command += "AND asapcomm.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") "; } DataTable table = Db.GetTable(command); List <AsapCommHist> listHists = Crud.AsapCommCrud.TableToList(table).Select(x => new AsapCommHist() { AsapComm = x }).ToList(); for (int i = 0; i < listHists.Count; i++) { listHists[i].PatientName = PIn.String(table.Rows[i]["PatientName"].ToString()); listHists[i].DateTimeSlotStart = PIn.Date(table.Rows[i]["SchedDate"].ToString()).Add(PIn.Time(table.Rows[i]["StartTime"].ToString())); listHists[i].DateTimeSlotEnd = PIn.Date(table.Rows[i]["SchedDate"].ToString()).Add(PIn.Time(table.Rows[i]["StopTime"].ToString())); listHists[i].EmailMessageText = PIn.String(table.Rows[i]["EmailMessageText"].ToString()); listHists[i].SMSMessageText = PIn.String(table.Rows[i]["SMSMessageText"].ToString()); } return(listHists); }
///<summary>Returns all jobs that the Query department would care about in regards to the date range passed in. ///This method will only return jobs that are in certain categories and are in the Complete or Documentaion phases. ///Optionally pass in a list of JobNums to exclude from the result set.</summary> public static List <Job> GetForQueries(DateTime dateFrom, DateTime dateTo, List <long> listExcludeJobNums = null) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <Job> >(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listExcludeJobNums)); } string command = $@"SELECT * FROM job WHERE Category='{JobCategory.Query}' AND PhaseCur IN ('{JobPhase.Complete}','{JobPhase.Cancelled}') AND {DbHelper.BetweenDates("DateTimeEntry",dateFrom,dateTo)} " ; if (!listExcludeJobNums.IsNullOrEmpty()) { command += $"AND JobNum NOT IN ({string.Join(",",listExcludeJobNums)})"; } return(Crud.JobCrud.SelectMany(command)); }
///<summary>Returns a list of BugSubmissionHashes based on DateTimeEntry and given date range.</summary> public static List <BugSubmissionHash> GetMany(DateTime dateTimeFrom = default, DateTime dateTimeTo = default, bool useConnectionStore = false) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <BugSubmissionHash> >(MethodBase.GetCurrentMethod(), dateTimeFrom, dateTimeTo, useConnectionStore)); } List <BugSubmissionHash> listRetVals = new List <BugSubmissionHash>(); DataAction.RunBugsHQ(() => { string command = $@"SELECT * FROM bugsubmissionhash "; if (dateTimeFrom != DateTime.MinValue && dateTimeTo != DateTime.MinValue) { command += $@"WHERE " + DbHelper.BetweenDates("DateTimeEntry", dateTimeFrom, dateTimeTo); } command += "ORDER BY DateTimeEntry DESC"; listRetVals = Crud.BugSubmissionHashCrud.SelectMany(command); }, useConnectionStore); return(listRetVals); }
///<summary>Returns all jobs for the date range and phases passed in. An empty list of phases will be treated as "Any" phase. ///Optionally pass in a list of JobNums to exclude from the result set.</summary> public static List <Job> GetForSearch(DateTime dateFrom, DateTime dateTo, List <JobPhase> listPhases, List <long> listPriorities, List <long> listExcludeJobNums = null) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <Job> >(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listPhases, listPriorities, listExcludeJobNums)); } string command = $@"SELECT * FROM job WHERE {DbHelper.BetweenDates("DateTimeEntry",dateFrom,dateTo)} " ; if (!listPhases.IsNullOrEmpty()) { command += $"AND PhaseCur IN ({string.Join(",",listPhases.Select(x => $"'{x}'"))}) "; } if (!listExcludeJobNums.IsNullOrEmpty()) { command += $"AND JobNum NOT IN ({string.Join(",",listExcludeJobNums)}) "; } if (!listPriorities.IsNullOrEmpty()) { command += $"AND Priority IN ({string.Join(",",listPriorities.Select(x => $"'{x}'"))})"; } return(Crud.JobCrud.SelectMany(command)); }
public static DataSet GetMonthlyGoalDataSet(DateTime dateFrom, DateTime dateTo, List <Provider> listProvs, List <Clinic> listClinics, bool hasAllProvs , bool hasAllClinics, PPOWriteoffDateCalc writeoffPayType, bool isCEMT = false) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetDS(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvs, listClinics, hasAllProvs, hasAllClinics, writeoffPayType, isCEMT)); } List <long> listClinicNums = listClinics.Select(x => x.ClinicNum).ToList(); List <long> listProvNums = listProvs.Select(x => x.ProvNum).ToList(); #region Procedures string whereProv = ""; if (!hasAllProvs && listProvNums.Count > 0) { whereProv = "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums) + ") "; } string whereClin = ""; if (!hasAllClinics && listClinicNums.Count > 0) { whereClin = "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinicNums) + ") "; } string command = "SELECT " + "procedurelog.ProcDate,procedurelog.ClinicNum," + "SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(cp.WriteOff),0) Production " + "FROM procedurelog " + "LEFT JOIN (SELECT SUM(claimproc.WriteOff) AS WriteOff, claimproc.ProcNum FROM claimproc " + "WHERE claimproc.Status=7 " //only CapComplete writeoffs are subtracted here. + "GROUP BY claimproc.ProcNum) cp ON procedurelog.ProcNum=cp.ProcNum " + "WHERE procedurelog.ProcStatus = 2 " + whereProv + whereClin + "AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + " " + "AND procedurelog.ProcDate <= " + POut.Date(dateTo) + " " + "GROUP BY ClinicNum,YEAR(procedurelog.ProcDate),MONTH(procedurelog.ProcDate),DAY(procedurelog.ProcDate)"; //Does not work for Oracle. Consider enhancing with DbHelper.Year(),DbHelper.Month() command += " ORDER BY ClinicNum,ProcDate"; DataTable tableProduction = new DataTable(); if (isCEMT) { tableProduction = Db.GetTable(command); } else { tableProduction = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command)); } tableProduction.TableName = "tableProduction"; #endregion #region Adjustments if (!hasAllProvs && listProvNums.Count > 0) { whereProv = "AND adjustment.ProvNum IN (" + string.Join(",", listProvNums) + ") "; } if (!hasAllClinics && listClinicNums.Count > 0) { whereClin = "AND adjustment.ClinicNum IN (" + string.Join(",", listClinicNums) + ") "; } command = "SELECT " + "adjustment.AdjDate," + "adjustment.ClinicNum," + "SUM(adjustment.AdjAmt) Adjustment " + "FROM adjustment " + "WHERE adjustment.AdjDate >= " + POut.Date(dateFrom) + " " + "AND adjustment.AdjDate <= " + POut.Date(dateTo) + " " + whereProv + whereClin + "GROUP BY ClinicNum,YEAR(adjustment.AdjDate),MONTH(adjustment.AdjDate),DAY(adjustment.AdjDate)"; command += " ORDER BY ClinicNum,AdjDate"; DataTable tableAdj = new DataTable(); if (isCEMT) { tableAdj = Db.GetTable(command); } else { tableAdj = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command)); } tableAdj.TableName = "tableAdj"; #endregion #region TableInsWriteoff if (!hasAllProvs && listProvNums.Count > 0) { whereProv = "AND claimproc.ProvNum IN (" + string.Join(",", listProvNums) + ") "; } if (!hasAllClinics && listClinicNums.Count > 0) { whereClin = "AND claimproc.ClinicNum IN (" + string.Join(",", listClinicNums) + ") "; } if (writeoffPayType == PPOWriteoffDateCalc.InsPayDate) { command = "SELECT " + "claimproc.DateCP Date," + "claimproc.ClinicNum," + "SUM(claimproc.WriteOff) WriteOff " + "FROM claimproc " + "WHERE claimproc.DateCP >= " + POut.Date(dateFrom) + " " + "AND claimproc.DateCP <= " + POut.Date(dateTo) + " " + whereProv + whereClin + "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + ") "//received or supplemental + "GROUP BY ClinicNum,DATE(claimproc.DateCP) " + "ORDER BY ClinicNum,DateCP"; } else if (writeoffPayType == PPOWriteoffDateCalc.ProcDate) { command = "SELECT " + "claimproc.ProcDate Date," + "claimproc.ClinicNum," + "SUM(claimproc.WriteOff) WriteOff " + "FROM claimproc " + "WHERE claimproc.ProcDate >= " + POut.Date(dateFrom) + " " + "AND claimproc.ProcDate <= " + POut.Date(dateTo) + " " + whereProv + whereClin + "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "//received or supplemental or notreceived + "GROUP BY ClinicNum,DATE(claimproc.ProcDate) " + "ORDER BY ClinicNum,ProcDate"; } else // writeoffPayType==PPOWriteoffDateCalc.ClaimPayDate { command = "SELECT " + "claimsnapshot.DateTEntry Date," + "claimproc.ClinicNum," + "SUM(CASE WHEN claimsnapshot.WriteOff=-1 THEN 0 ELSE claimsnapshot.WriteOff END) WriteOff " + "FROM claimproc " + "INNER JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimProc.ClaimProcNum " + "WHERE " + DbHelper.BetweenDates("claimsnapshot.DateTEntry", dateFrom, dateTo) + " " + whereProv + whereClin + "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "//received or supplemental or notreceived + "GROUP BY ClinicNum,DATE(claimsnapshot.DateTEntry) " + "ORDER BY ClinicNum,claimsnapshot.DateTEntry"; } DataTable tableInsWriteoff = new DataTable(); if (isCEMT) { tableInsWriteoff = Db.GetTable(command); } else { tableInsWriteoff = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command)); } tableInsWriteoff.TableName = "tableInsWriteoff"; #endregion #region TableSched DataTable tableSched = new DataTable(); //Reads from the procedurelog table instead of claimproc because we are looking for scheduled procedures. if (!hasAllProvs && listProvNums.Count > 0) { whereProv = "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums) + ") "; } if (!hasAllClinics && listClinicNums.Count > 0) { whereClin = "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinicNums) + ") "; } command = "SELECT " + DbHelper.DtimeToDate("t.AptDateTime") + " SchedDate,SUM(t.Fee-t.WriteoffEstimate) Amount,ClinicNum " + "FROM (SELECT appointment.AptDateTime,IFNULL(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits),0) Fee,appointment.ClinicNum,"; if (ReportsComplex.RunFuncOnReportServer(() => Prefs.GetBoolNoCache(PrefName.ReportPandIschedProdSubtractsWO))) { //Subtract both PPO and capitation writeoffs command += "SUM(IFNULL(CASE WHEN WriteOffEstOverride != -1 THEN WriteOffEstOverride ELSE WriteOffEst END,0)) WriteoffEstimate "; } else { //Always subtract CapEstimate writeoffs from scheduled production. This is so that the scheduled production will match actual production //when the procedures are set complete. Nathan decided this 01/05/2017. command += "SUM(IFNULL((CASE WHEN claimproc.Status=" + POut.Int((int)ClaimProcStatus.Estimate) + " THEN 0 " + "WHEN WriteOffEstOverride != -1 THEN WriteOffEstOverride ELSE WriteOffEst END),0)) WriteoffEstimate "; } command += "FROM appointment " + "LEFT JOIN procedurelog ON appointment.AptNum = procedurelog.AptNum AND procedurelog.ProcStatus=" + POut.Int((int)ProcStat.TP) + " " + "LEFT JOIN claimproc ON procedurelog.ProcNum = claimproc.ProcNum " + "AND claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Estimate) + "," + POut.Int((int)ClaimProcStatus.CapEstimate) + ") " + " AND (WriteOffEst != -1 OR WriteOffEstOverride != -1) " + "WHERE appointment.AptStatus = " + POut.Int((int)ApptStatus.Scheduled) + " " + "AND " + DbHelper.DtimeToDate("appointment.AptDateTime") + " >= " + POut.Date(dateFrom) + " " + "AND " + DbHelper.DtimeToDate("appointment.AptDateTime") + " <= " + POut.Date(dateTo) + " " + whereProv + whereClin + " GROUP BY procedurelog.ProcNum) t " //without this, there can be duplicate proc rows due to the claimproc join with dual insurance. + "GROUP BY SchedDate,ClinicNum " + "ORDER BY SchedDate"; if (isCEMT) { tableSched = Db.GetTable(command); } else { tableSched = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command)); } tableSched.TableName = "tableSched"; #endregion #region TableProdGoal //1. Find all schedules for the month //2. ClinicNum will come from the schedule's operatory //3. Fetch HourlyProdGoalAmt from provider on the schedule //4. Sum scheduled hours, grouped by prov and clinic //5. Multiply the scheduled hours by the provider's HourlyProdGoalAmt DataTable tableProdGoal = new DataTable(); if (!hasAllProvs && listProvNums.Count > 0) { whereProv = "AND schedule.ProvNum IN (" + string.Join(",", listProvNums) + ") "; } if (!hasAllClinics && listClinicNums.Count > 0) { whereClin = "AND COALESCE(operatory.ClinicNum,0) IN (" + string.Join(",", listClinicNums) + ") "; } //Fetch all schedules for the month and associated information (clinic from operatory, HourlyProdGoalAmt from provider) command = "SELECT " + DbHelper.DtimeToDate("schedule.SchedDate") + @" AS SchedDate, schedule.StartTime AS StartTime, schedule.StopTime AS StopTime, COALESCE(operatory.ClinicNum,0) AS ClinicNum, provider.HourlyProdGoalAmt AS ProvProdGoal, provider.ProvNum AS ProvNum FROM schedule INNER JOIN provider ON provider.ProvNum=schedule.ProvNum LEFT JOIN scheduleop ON scheduleop.ScheduleNum=schedule.ScheduleNum LEFT JOIN operatory ON scheduleop.OperatoryNum=operatory.OperatoryNum WHERE schedule.SchedType=" + POut.Int((int)ScheduleType.Provider) + " " + "AND schedule.Status=" + POut.Int((int)SchedStatus.Open) + " " + "AND schedule." + DbHelper.BetweenDates("SchedDate", dateFrom, dateTo) + " " + whereProv + whereClin; if (isCEMT) { tableProdGoal = Db.GetTable(command); } else { tableProdGoal = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command)); } tableProdGoal.TableName = "tableProdGoal"; #endregion #region WriteOffAdjustments DataTable tableWriteOffAdjustments = new DataTable(); if (!hasAllProvs && listProvNums.Count > 0) { whereProv = "AND claimproc.ProvNum IN (" + string.Join(",", listProvNums) + ") "; } if (!hasAllClinics && listClinicNums.Count > 0) { whereClin = "AND claimproc.ClinicNum IN (" + string.Join(",", listClinicNums) + ") "; } if (writeoffPayType == PPOWriteoffDateCalc.ClaimPayDate) { //Insurance WriteOff Adjustments---------------------------------------------------------------------------- command = $@"SELECT claimproc.DateCP Date,claimproc.ClinicNum, -SUM(CASE WHEN COALESCE(claimsnapshot.WriteOff,-1)=-1 THEN 0 ELSE claimsnapshot.WriteOff END) WriteOffEst, SUM(claimproc.WriteOff) WriteOff, claimproc.ClaimNum FROM claimproc LEFT JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimproc.ClaimProcNum WHERE claimproc.DateCP BETWEEN {POut.Date(dateFrom)} AND {POut.Date(dateTo)} AND claimproc.Status IN ({(int)ClaimProcStatus.Received},{(int)ClaimProcStatus.Supplemental}) {whereProv} {whereClin} GROUP BY ClinicNum,DATE(claimproc.DateCP)" ; if (isCEMT) { tableWriteOffAdjustments = Db.GetTable(command); } else { tableWriteOffAdjustments = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command)); } } tableWriteOffAdjustments.TableName = "tableWriteOffAdjustments"; #endregion WriteOffAdjustments DataSet dataSet = new DataSet(); dataSet.Tables.Add(tableProduction); dataSet.Tables.Add(tableAdj); dataSet.Tables.Add(tableInsWriteoff); dataSet.Tables.Add(tableWriteOffAdjustments); dataSet.Tables.Add(tableSched); dataSet.Tables.Add(tableProdGoal); return(dataSet); }
public static DataTable GetOverPaidProcs(long patNum, List <long> listProvNums, List <long> listClinics, DateTime dateStart, DateTime dateEnd) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), patNum, listProvNums, listClinics, dateStart, dateEnd)); } List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() => Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList() ); #region Completed Procs string command = "SELECT "; if (PrefC.GetBool(PrefName.ReportsShowPatNum)) { command += DbHelper.Concat("CAST(patient.PatNum AS CHAR)", "'-'", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI"); } else { command += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI"); } command += @" AS 'patientName', procedurelog.ProcDate, procedurecode.ProcCode, procedurelog.ToothNum, provider.Abbr, (procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)) AS fee, patient.PatNum, procedurelog.ProcNum FROM procedurelog INNER JOIN patient ON patient.PatNum=procedurelog.PatNum INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum INNER JOIN provider ON provider.ProvNum=procedurelog.ProvNum WHERE procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " AND " + DbHelper.BetweenDates("procedurelog.ProcDate", dateStart, dateEnd) + " " + "AND procedurelog.ProcFee>0 "; if (listProvNums != null && listProvNums.Count > 0) { command += "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") "; } if (listClinics != null && listClinics.Count > 0) { command += "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinics.Select(x => POut.Long(x))) + ") "; } if (patNum > 0) { command += "AND procedurelog.PatNum=" + POut.Long(patNum) + " "; } command += "ORDER BY procedurelog.ProcDate,patientName,procedurecode.ProcCode,provider.Abbr"; DataTable rawCompletedProcTable = Db.GetTable(command); Dictionary <long, DataRow> dictCompletedProcRows = rawCompletedProcTable.Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString())); #endregion DataTable table = new DataTable(); if (dictCompletedProcRows.Count == 0) { return(table); } #region ClaimProcs List <long> listPatNums = rawCompletedProcTable.Select().Select(x => PIn.Long(x["PatNum"].ToString())).Distinct().ToList(); command = @"SELECT MIN(claimproc.ProcNum) ProcNum,MIN(claimproc.PatNum) PatNum,MIN(claimproc.ProcDate) ProcDate,SUM(claimproc.InsPayAmt) insPayAmt, SUM(claimproc.Writeoff) writeoff FROM claimproc WHERE claimproc.Status NOT IN(" + string.Join(",", new List <int> { (int)ClaimProcStatus.Preauth, (int)ClaimProcStatus.CapEstimate, (int)ClaimProcStatus.CapComplete, (int)ClaimProcStatus.Estimate, (int)ClaimProcStatus.InsHist } .Select(x => POut.Int(x))) + ") " + "AND " + DbHelper.BetweenDates("claimproc.ProcDate", dateStart, dateEnd) + " " + "AND claimproc.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ") " + @"GROUP BY claimproc.ProcNum HAVING SUM(claimproc.InsPayAmt+claimproc.Writeoff)>0 ORDER BY NULL" ; Dictionary <long, DataRow> dictClaimProcRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString())); #endregion #region Patient Payments command = @"SELECT paysplit.ProcNum,SUM(paysplit.SplitAmt) ptAmt FROM paysplit WHERE paysplit.ProcNum>0 AND paysplit.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + $@") "; if (listHiddenUnearnedDefNums.Count > 0) { command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) "; } command += @" GROUP BY paysplit.ProcNum ORDER BY NULL" ; Dictionary <long, DataRow> dictPatPayRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString())); #endregion #region Adjustments command = @"SELECT adjustment.ProcNum,SUM(adjustment.AdjAmt) AdjAmt FROM adjustment WHERE adjustment.ProcNum>0 AND adjustment.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + @") GROUP BY adjustment.ProcNum ORDER BY NULL" ; Dictionary <long, DataRow> dictAdjRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString())); #endregion //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("patientName"); table.Columns.Add("ProcDate", typeof(DateTime)); table.Columns.Add("ProcCode"); table.Columns.Add("ToothNum"); table.Columns.Add("Abbr"); table.Columns.Add("fee"); table.Columns.Add("insPaid"); table.Columns.Add("wo"); table.Columns.Add("ptPaid"); table.Columns.Add("adjAmt"); table.Columns.Add("overPay"); table.Columns.Add("PatNum"); DataRow row; foreach (KeyValuePair <long, DataRow> kvp in dictCompletedProcRows) { long procNum = kvp.Key; decimal procFeeAmt = PIn.Decimal(kvp.Value["fee"].ToString()); decimal insPaidAmt = 0; decimal woAmt = 0; decimal ptPaidAmt = 0; decimal adjAmt = 0; if (dictClaimProcRows.ContainsKey(procNum)) { insPaidAmt = PIn.Decimal(dictClaimProcRows[procNum]["insPayAmt"].ToString()); woAmt = PIn.Decimal(dictClaimProcRows[procNum]["writeoff"].ToString()); } if (dictPatPayRows.ContainsKey(procNum)) { ptPaidAmt = PIn.Decimal(dictPatPayRows[procNum]["ptAmt"].ToString()); } if (dictAdjRows.ContainsKey(procNum)) { adjAmt = PIn.Decimal(dictAdjRows[procNum]["AdjAmt"].ToString()); } decimal overPay = procFeeAmt - insPaidAmt - woAmt - ptPaidAmt + adjAmt; if (!overPay.IsLessThanZero()) { continue; //No overpayment. Not need to continue; } row = table.NewRow(); row["patientName"] = PIn.String(kvp.Value["patientName"].ToString()); row["ProcDate"] = PIn.Date(kvp.Value["ProcDate"].ToString()); row["ProcCode"] = PIn.String(kvp.Value["ProcCode"].ToString()); row["ToothNum"] = PIn.String(kvp.Value["ToothNum"].ToString()); row["Abbr"] = PIn.String(kvp.Value["Abbr"].ToString());; row["fee"] = procFeeAmt.ToString(); row["insPaid"] = insPaidAmt.ToString(); row["wo"] = woAmt.ToString(); row["ptPaid"] = ptPaidAmt.ToString(); row["adjAmt"] = adjAmt.ToString(); row["overPay"] = overPay.ToString(); row["PatNum"] = PIn.Long(kvp.Value["PatNum"].ToString()); table.Rows.Add(row); } return(table); }
///<summary>If not using clinics then supply an empty list of clinicNums.</summary> public static DataTable GetAppointmentTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums, bool hasClinicsEnabled, bool isShowRecall, bool isShowNewPat, bool isShowASAP, SortAndFilterBy sortBy, List <ApptStatus> listApptStatusesToExclude, List <long> listConfirmationStatuses, string formSender) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, hasClinicsEnabled, isShowRecall, isShowNewPat, isShowASAP, sortBy, listApptStatusesToExclude, listConfirmationStatuses, formSender)); } //Appointment status conditions string whereApptStatus = ""; if (listApptStatusesToExclude.Count > 0) { whereApptStatus += " appointment.AptStatus NOT IN (" + string.Join(",", listApptStatusesToExclude.Select(x => POut.Int((int)x))) + ") AND "; } //Provider Conditions string whereProv = ""; if (listProvNums.Count > 0) { whereProv += " (appointment.ProvNum IN(" + string.Join(",", listProvNums) + ") " + " OR appointment.ProvHyg IN(" + string.Join(",", listProvNums) + ")) AND "; } //Clinic Conditions string whereClinics = ""; if (hasClinicsEnabled && listClinicNums.Count > 0) { whereClinics += " appointment.ClinicNum IN(" + string.Join(",", listClinicNums) + ") AND "; } //Appointment confirmation conditions string whereConfStatus = ""; if (listConfirmationStatuses.Count > 0) { whereConfStatus += " appointment.Confirmed IN (" + string.Join(",", listConfirmationStatuses) + ") AND "; } //WebSched Appointments string innerJoinWebSchedBoth = ""; List <LogSources> listSources = new List <LogSources>(); if (isShowNewPat) { listSources.Add(LogSources.WebSchedNewPatAppt); } if (isShowRecall) { listSources.Add(LogSources.WebSched); } if (isShowASAP) { listSources.Add(LogSources.WebSchedASAP); } if (listSources.Count > 0) { List <int> listPerms = new List <int>() { (int)Permissions.AppointmentCreate }; if (sortBy == SortAndFilterBy.SecurityLogDate) { listPerms.Add((int)Permissions.AppointmentEdit); listPerms.Add((int)Permissions.AppointmentMove); } innerJoinWebSchedBoth = " INNER JOIN securitylog ON appointment.AptNum=securitylog.FKey" + " AND securitylog.PermType IN (" + string.Join(",", listPerms.Select(x => POut.Int(x))) + ") " + " AND securitylog.LogSource IN (" + string.Join(",", listSources.Select(x => (int)x)) + ") "; } //Query string command = @"SELECT "; if (sortBy == SortAndFilterBy.SecDateTEntry) { command += "appointment.SecDateTEntry,"; } if (sortBy == SortAndFilterBy.SecurityLogDate) { command += "securitylog.LogDateTime,"; } command += @"appointment.AptDateTime, patient.PatNum, TRIM(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),CASE WHEN LENGTH(patient.Preferred) > 0 THEN CONCAT(CONCAT('(',patient.Preferred),') ') ELSE '' END),patient.FName), ' '),patient.MiddleI)) PatName, patient.Birthdate, appointment.AptDateTime, LENGTH(appointment.Pattern)*5, appointment.ProcDescript, patient.HmPhone, patient.WkPhone, patient.WirelessPhone, COALESCE(clinic.Description,'" + POut.String(Lans.g("formSender", "Unassigned")) + @"') ClinicDesc, appointment.SecDateTEntry AS 'DateTimeCreated', appointment.Confirmed, appointment.Note, appointment.AptNum FROM appointment INNER JOIN patient ON appointment.PatNum=patient.PatNum " + innerJoinWebSchedBoth + @" LEFT JOIN clinic ON appointment.ClinicNum=clinic.ClinicNum WHERE " + whereApptStatus + whereProv + whereClinics + whereConfStatus; if (sortBy == SortAndFilterBy.SecDateTEntry) { command += " " + DbHelper.BetweenDates("appointment.SecDateTEntry", dateStart, dateEnd) + " ORDER BY appointment.ClinicNum,appointment.SecDateTEntry,PatName"; } else if (sortBy == SortAndFilterBy.AptDateTime) { command += " appointment.AptDateTime BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd.AddDays(1)) + " ORDER BY appointment.ClinicNum,appointment.AptDateTime,PatName"; } else if (sortBy == SortAndFilterBy.SecurityLogDate && listSources.Count > 0) { command += " securitylog.LogDateTime BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd.AddDays(1)) + " ORDER BY securitylog.LogDateTime,appointment.AptDateTime,PatName"; } DataTable table = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command)); return(table); }