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); }
///<summary></summary> public static DataTable GetWriteoffTable(DateTime dateStart, DateTime dateEnd, bool isIndividual, string carrierText, PPOWriteoffDateCalc writeoffType) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, isIndividual, carrierText, writeoffType)); } string queryText = ""; //individual if (isIndividual) { queryText = "SET @DateFrom=" + POut.Date(dateStart) + ", @DateTo=" + POut.Date(dateEnd) + ", @CarrierName='%" + POut.String(carrierText) + "%';"; if (writeoffType == PPOWriteoffDateCalc.InsPayDate) { queryText += @"SELECT claimproc.DateCP, CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI), carrier.CarrierName, provider.Abbr, SUM(claimproc.FeeBilled), SUM(claimproc.FeeBilled-claimproc.WriteOff), SUM(claimproc.WriteOff), claimproc.ClaimNum FROM claimproc INNER JOIN insplan ON claimproc.PlanNum = insplan.PlanNum AND insplan.PlanType='p' INNER JOIN patient ON claimproc.PatNum = patient.PatNum INNER JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum AND carrier.CarrierName LIKE @CarrierName INNER JOIN provider ON provider.ProvNum = claimproc.ProvNum WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + @") AND claimproc.DateCP >= @DateFrom AND claimproc.DateCP <= @DateTo GROUP BY claimproc.ClaimNum ORDER BY claimproc.DateCP" ; } else if (writeoffType == PPOWriteoffDateCalc.ProcDate) //use procedure date { queryText += @"SELECT claimproc.ProcDate, CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI), carrier.CarrierName, provider.Abbr, SUM(claimproc.FeeBilled), SUM(claimproc.FeeBilled-claimproc.WriteOff), SUM(claimproc.WriteOff), claimproc.ClaimNum FROM claimproc INNER JOIN insplan ON claimproc.PlanNum = insplan.PlanNum AND insplan.PlanType='p' INNER JOIN patient ON claimproc.PatNum = patient.PatNum INNER JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum AND carrier.CarrierName LIKE @CarrierName INNER JOIN provider ON provider.ProvNum = claimproc.ProvNum WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + @") AND claimproc.ProcDate >= @DateFrom AND claimproc.ProcDate <= @DateTo GROUP BY claimproc.ClaimNum ORDER BY claimproc.ProcDate" ; } else //writeoffType==PPOWriteoffDateCalc.ClaimPayDate { queryText += @"SELECT claimsnapshot.DateTEntry, CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI), carrier.CarrierName, provider.Abbr, SUM(claimproc.FeeBilled), SUM(claimproc.FeeBilled-claimsnapshot.WriteOff), SUM(claimsnapshot.WriteOff), claimproc.ClaimNum FROM claimproc INNER JOIN insplan ON insplan.PlanNum=claimProc.PlanNum AND insplan.PlanType='p' INNER JOIN patient ON patient.PatNum=claimProc.PatNum INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum AND carrier.CarrierName LIKE @CarrierName INNER JOIN provider ON provider.ProvNum=claimProc.ProvNum INNER JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimproc.ClaimProcNum AND claimsnapshot.DateTEntry BETWEEN @DateFrom AND @DateTo WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + @") GROUP BY claimproc.ClaimNum ORDER BY claimsnapshot.DateTEntry" ; } } else { //group if (writeoffType == PPOWriteoffDateCalc.InsPayDate) { queryText = "SET @DateFrom=" + POut.Date(dateStart) + ", @DateTo=" + POut.Date(dateEnd) + ", @CarrierName='%" + POut.String(carrierText) + "%';" + @"SELECT carrier.CarrierName, SUM(claimproc.FeeBilled), SUM(claimproc.FeeBilled-claimproc.WriteOff), SUM(claimproc.WriteOff), claimproc.ClaimNum FROM claimproc INNER JOIN insplan ON claimproc.PlanNum = insplan.PlanNum AND insplan.PlanType='p' INNER JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum AND carrier.CarrierName LIKE @CarrierName WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + @") AND claimproc.DateCP >= @DateFrom AND claimproc.DateCP <= @DateTo GROUP BY carrier.CarrierNum ORDER BY carrier.CarrierName" ; } else if (writeoffType == PPOWriteoffDateCalc.ProcDate) { queryText = "SET @DateFrom=" + POut.Date(dateStart) + ", @DateTo=" + POut.Date(dateEnd) + ", @CarrierName='%" + POut.String(carrierText) + "%';" + @"SELECT carrier.CarrierName, SUM(claimproc.FeeBilled), SUM(claimproc.FeeBilled-claimproc.WriteOff), SUM(claimproc.WriteOff), claimproc.ClaimNum FROM claimproc INNER JOIN insplan ON claimproc.PlanNum = insplan.PlanNum AND insplan.PlanType='p' INNER JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum AND carrier.CarrierName LIKE @CarrierName WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + @") AND claimproc.ProcDate >= @DateFrom AND claimproc.ProcDate <= @DateTo GROUP BY carrier.CarrierNum ORDER BY carrier.CarrierName" ; } else // writeoffType==PPOWriteoffDateCalc.ClaimPayDate { queryText = "SET @DateFrom=" + POut.Date(dateStart) + ", @DateTo=" + POut.Date(dateEnd) + ", @CarrierName='%" + POut.String(carrierText) + "%';" + @"SELECT carrier.CarrierName, SUM(claimproc.FeeBilled), SUM(claimproc.FeeBilled-claimproc.WriteOff), SUM(claimproc.WriteOff), claimproc.ClaimNum FROM claimproc INNER JOIN insplan on claimproc.PlanNum = insplan.PlanNum AND insplan.PlanType='p' INNER JOIN carrier on carrier.CarrierNum = insplan.CarrierNum AND carrier.CarrierName LIKE @CarrierName INNER JOIN claimsnapshot on claimsnapshot.ClaimProcNum=claimproc.ClaimProcNum AND claimsnapshot.DateTEntry >= @DateFrom AND claimsnapshot.DateTEntry <= @DateTo WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + @") GROUP BY carrier.CarrierNum ORDER BY carrier.CarrierName" ; } } return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(queryText))); }
//IMPORTANT NOTE FOR ANYBODY WHO CODES IN HERE: This is used in the CEMT so everything MUST be coded in such a way that they don't use the //cache to look up information. The CEMT does NOT keep copies of the remote database caches when this is used so things such as //PrefC.GetBool or Clinics.GetDesc will return incorrect results. ///<summary>If not using clinics then supply an empty list of clinics.</summary> public static DataSet GetData(DateTime dateFrom, DateTime dateTo, List <Provider> listProvs, List <Clinic> listClinics, bool hasAllProvs , bool hasAllClinics, PPOWriteoffDateCalc writeoffPayType, bool isCEMT = false) { //No need to check RemotingRole; no call to db. DataSet dataSet = GetMonthlyGoalDataSet(dateFrom, dateTo, listProvs, listClinics, hasAllProvs, hasAllClinics, writeoffPayType, isCEMT); DataTable tableProduction = dataSet.Tables["tableProduction"]; DataTable tableAdj = dataSet.Tables["tableAdj"]; DataTable tableInsWriteoff = dataSet.Tables["tableInsWriteoff"]; DataTable tableSched = dataSet.Tables["tableSched"]; DataTable tableProdGoal = dataSet.Tables["tableProdGoal"]; DataTable tableWriteoffAdj = dataSet.Tables["tableWriteOffAdjustments"]; decimal scheduledForDay; decimal productionForDay; decimal adjustsForDay; decimal inswriteoffsForDay; //spk 5/19/05 decimal insWriteoffAdjsForDay; decimal totalproductionForDay; decimal prodGoalForDay; DataTable dt = new DataTable("Total"); dt.Columns.Add(new DataColumn("Date")); dt.Columns.Add(new DataColumn("Weekday")); dt.Columns.Add(new DataColumn("Production")); dt.Columns.Add(new DataColumn("Prod Goal")); dt.Columns.Add(new DataColumn("Scheduled")); dt.Columns.Add(new DataColumn("Adjusts")); if (writeoffPayType == PPOWriteoffDateCalc.ClaimPayDate) { dt.Columns.Add(new DataColumn("Writeoff Est")); dt.Columns.Add(new DataColumn("Writeoff Adj")); } else { dt.Columns.Add(new DataColumn("Writeoff")); } dt.Columns.Add(new DataColumn("Tot Prod")); DataTable dtClinic = new DataTable("Clinic"); dtClinic.Columns.Add(new DataColumn("Date")); dtClinic.Columns.Add(new DataColumn("Weekday")); dtClinic.Columns.Add(new DataColumn("Production")); dtClinic.Columns.Add(new DataColumn("Prod Goal")); dtClinic.Columns.Add(new DataColumn("Scheduled")); dtClinic.Columns.Add(new DataColumn("Adjusts")); if (writeoffPayType == PPOWriteoffDateCalc.ClaimPayDate) { dtClinic.Columns.Add(new DataColumn("Writeoff Est")); dtClinic.Columns.Add(new DataColumn("Writeoff Adj")); } else { dtClinic.Columns.Add(new DataColumn("Writeoff")); } dtClinic.Columns.Add(new DataColumn("Tot Prod")); dtClinic.Columns.Add(new DataColumn("Clinic")); //length of array is number of months between the two dates plus one. //The from date and to date will not be more than one year and must will be within the same year due to FormRpProdInc UI validation enforcement. DateTime[] dates = null; dates = new DateTime[dateTo.Subtract(dateFrom).Days + 1]; //Make a DateTime array with one position for each day in the report. //Preprocess schedules for provider so we can merge them together and not double count them. //For each schedule, find all others for this prov and clinic. (in the case when they're overlapping and multiple clinics, we don't get this far) //Figure out a way to find all hours a provider worked in a single day across multiple schedules. If they overlap (due to multiple operatories) //then we only count one. //Sum up a schedule for the day. Dictionary <Tuple <DateTime, long, long>, List <DataRow> > dictDates = new Dictionary <Tuple <DateTime, long, long>, List <DataRow> >();//We are grouping data rows by day, provnum, and clinicnum for (int j = 0; j < tableProdGoal.Rows.Count; j++) { DateTime date = PIn.Date(tableProdGoal.Rows[j]["SchedDate"].ToString()); long provNum = PIn.Long(tableProdGoal.Rows[j]["ProvNum"].ToString()); long clinicNum = (!hasAllClinics && listClinics.Count == 0) ? 0 : PIn.Long(tableProdGoal.Rows[j]["ClinicNum"].ToString()); if (!dictDates.ContainsKey(Tuple.Create(date, provNum, clinicNum))) { dictDates.Add(Tuple.Create(date, provNum, clinicNum), new List <DataRow>() { tableProdGoal.Rows[j] }); continue; //It's added, no need to do more. } //Date/prov/clinic combo exists in dictionary already, add row to the row collection. dictDates[Tuple.Create(date, provNum, clinicNum)].Add(tableProdGoal.Rows[j]); } List <ProvProdGoal> listProdGoal = new List <ProvProdGoal>(); //Add all spans to a list of spans if they don't overlap. If they do overlap, extend the start/end of an existing span. //Once all spans are added, compare spans in list to other spans and see if they overlap, expand as needed (removing the one merged). //If there is no movement, we are done. foreach (KeyValuePair <Tuple <DateTime, long, long>, List <DataRow> > kvp in dictDates) //For each day (there are no multi-clinic overlaps, can't run report if there are) { double hours = 0; List <SchedRange> listRangeForDay = new List <SchedRange>(); foreach (DataRow row in kvp.Value) //Add all schedule ranges to the list { TimeSpan stopTime = PIn.Time(row["StopTime"].ToString()); TimeSpan startTime = PIn.Time(row["StartTime"].ToString()); SchedRange range = new SchedRange() { StartTime = startTime, EndTime = stopTime }; listRangeForDay.Add(range); } bool hasMovement = true; while (listRangeForDay.Count > 1 && hasMovement) //As they're added, attempt to merge ranges until there's no more movement. { for (int i = listRangeForDay.Count - 1; i >= 0; i--) { SchedRange range1 = listRangeForDay[i]; for (int j = listRangeForDay.Count - 1; j >= 0; j--) { hasMovement = false; SchedRange range2 = listRangeForDay[j]; if (range1.PriKey == range2.PriKey) { continue; } if (range1.StartTime <= range2.StartTime && range1.EndTime >= range2.StartTime) //range2 starts between range1's start and end. Time to merge end time. { if (range2.EndTime >= range1.EndTime) { range1.EndTime = range2.EndTime; } hasMovement = true; } if (range1.StartTime <= range2.EndTime && range1.EndTime >= range2.EndTime) //range2 ends between range1's start and end. Time to merge start time. { if (range2.StartTime <= range1.StartTime) { range1.StartTime = range2.StartTime; } hasMovement = true; } if (hasMovement) { listRangeForDay.RemoveAt(j); --i; } } } } foreach (SchedRange sched in listRangeForDay) { TimeSpan timeDiff = sched.EndTime.Subtract(sched.StartTime); hours += timeDiff.TotalHours; } listProdGoal.Add(new ProvProdGoal() { ClinicNum = kvp.Key.Item3, ProvNum = kvp.Key.Item2, Date = kvp.Key.Item1, Hours = hours, ProdGoal = PIn.Double(kvp.Value[0]["ProvProdGoal"].ToString()) }); } //Get a list of clinics so that we have access to their descriptions for the report. for (int it = 0; it < listClinics.Count; it++) //For each clinic { for (int i = 0; i < dates.Length; i++) //usually 12 months in loop for annual. Loop through the DateTime array, each position represents one date in the report. { dates[i] = dateFrom.AddDays(i); //Monthly/Daily report, add a day DataRow row = dtClinic.NewRow(); row["Date"] = dates[i].ToShortDateString(); row["Weekday"] = dates[i].DayOfWeek.ToString(); scheduledForDay = 0; productionForDay = 0; adjustsForDay = 0; inswriteoffsForDay = 0; //spk 5/19/05 insWriteoffAdjsForDay = 0; prodGoalForDay = 0; for (int j = 0; j < tableProduction.Rows.Count; j++) { if (listClinics[it].ClinicNum == 0 && tableProduction.Rows[j]["ClinicNum"].ToString() != "0") { continue; //Only counting unassigned this time around. } else if (listClinics[it].ClinicNum != 0 && tableProduction.Rows[j]["ClinicNum"].ToString() != POut.Long(listClinics[it].ClinicNum)) { continue; } if (dates[i].Date == PIn.Date(tableProduction.Rows[j]["ProcDate"].ToString()).Date) { productionForDay += PIn.Decimal(tableProduction.Rows[j]["Production"].ToString()); } } for (int j = 0; j < tableAdj.Rows.Count; j++) { if (listClinics[it].ClinicNum == 0 && tableAdj.Rows[j]["ClinicNum"].ToString() != "0") { continue; } else if (listClinics[it].ClinicNum != 0 && tableAdj.Rows[j]["ClinicNum"].ToString() != POut.Long(listClinics[it].ClinicNum)) { continue; } if (dates[i].Date == PIn.Date(tableAdj.Rows[j]["AdjDate"].ToString()).Date) { adjustsForDay += PIn.Decimal(tableAdj.Rows[j]["Adjustment"].ToString()); } } for (int j = 0; j < tableInsWriteoff.Rows.Count; j++) { if (listClinics[it].ClinicNum == 0 && tableInsWriteoff.Rows[j]["ClinicNum"].ToString() != "0") { continue; } else if (listClinics[it].ClinicNum != 0 && tableInsWriteoff.Rows[j]["ClinicNum"].ToString() != POut.Long(listClinics[it].ClinicNum)) { continue; } if (dates[i].Date == PIn.Date(tableInsWriteoff.Rows[j]["Date"].ToString()).Date) { inswriteoffsForDay -= PIn.Decimal(tableInsWriteoff.Rows[j]["Writeoff"].ToString()); } } foreach (DataRow rowCur in tableWriteoffAdj.Rows) { if (rowCur["ClinicNum"].ToString() != POut.Long(listClinics[it].ClinicNum) || dates[i].Date != PIn.Date(rowCur["Date"].ToString()).Date) { continue; } insWriteoffAdjsForDay -= PIn.Decimal(rowCur["WriteOffEst"].ToString()) + PIn.Decimal(rowCur["WriteOff"].ToString()); } for (int j = 0; j < tableSched.Rows.Count; j++) { if (listClinics[it].ClinicNum == 0 && tableSched.Rows[j]["ClinicNum"].ToString() != "0") { continue; } else if (listClinics[it].ClinicNum != 0 && tableSched.Rows[j]["ClinicNum"].ToString() != POut.Long(listClinics[it].ClinicNum)) { continue; } if (dates[i].Date == PIn.Date(tableSched.Rows[j]["SchedDate"].ToString()).Date) { scheduledForDay += PIn.Decimal(tableSched.Rows[j]["Amount"].ToString()); } } for (int j = 0; j < listProdGoal.Count; j++) { if (listClinics[it].ClinicNum == 0 && listProdGoal[j].ClinicNum != 0) { continue; } else if (listClinics[it].ClinicNum != 0 && listProdGoal[j].ClinicNum != listClinics[it].ClinicNum) { continue; } if (dates[i].Date == listProdGoal[j].Date) { prodGoalForDay += (decimal)(listProdGoal[j].Hours * listProdGoal[j].ProdGoal); //Multiply the hours for this schedule by the amount of production goal for this prov. } } totalproductionForDay = productionForDay + adjustsForDay + inswriteoffsForDay + insWriteoffAdjsForDay + scheduledForDay; string clinicDesc = listClinics[it].Description; row["Production"] = productionForDay.ToString("n"); row["Prod Goal"] = prodGoalForDay.ToString("n"); row["Scheduled"] = scheduledForDay.ToString("n"); row["Adjusts"] = adjustsForDay.ToString("n"); if (writeoffPayType == PPOWriteoffDateCalc.ClaimPayDate) { row["Writeoff Est"] = inswriteoffsForDay.ToString("n"); row["Writeoff Adj"] = insWriteoffAdjsForDay.ToString("n"); } else { row["Writeoff"] = inswriteoffsForDay.ToString("n"); } row["Tot Prod"] = totalproductionForDay.ToString("n"); row["Clinic"] = clinicDesc == "" ? Lans.g("FormRpProdInc", "Unassigned"):clinicDesc; dtClinic.Rows.Add(row); } } for (int i = 0; i < dates.Length; i++) //usually 12 months in loop { dates[i] = dateFrom.AddDays(i); DataRow row = dt.NewRow(); row["Date"] = dates[i].ToShortDateString(); row["Weekday"] = dates[i].DayOfWeek.ToString(); scheduledForDay = 0; productionForDay = 0; adjustsForDay = 0; inswriteoffsForDay = 0; insWriteoffAdjsForDay = 0; prodGoalForDay = 0; for (int j = 0; j < tableProduction.Rows.Count; j++) { if (dates[i].Date == PIn.Date(tableProduction.Rows[j]["ProcDate"].ToString()).Date) { productionForDay += PIn.Decimal(tableProduction.Rows[j]["Production"].ToString()); } } for (int j = 0; j < tableAdj.Rows.Count; j++) { if (dates[i].Date == PIn.Date(tableAdj.Rows[j]["AdjDate"].ToString()).Date) { adjustsForDay += PIn.Decimal(tableAdj.Rows[j]["Adjustment"].ToString()); } } for (int j = 0; j < tableInsWriteoff.Rows.Count; j++) { if (dates[i].Date == PIn.Date(tableInsWriteoff.Rows[j]["Date"].ToString()).Date) { inswriteoffsForDay -= PIn.Decimal(tableInsWriteoff.Rows[j]["Writeoff"].ToString()); } } foreach (DataRow rowCur in tableWriteoffAdj.Rows) { if (dates[i].Date == PIn.Date(rowCur["Date"].ToString()).Date) { insWriteoffAdjsForDay -= PIn.Decimal(rowCur["WriteOffEst"].ToString()) + PIn.Decimal(rowCur["WriteOff"].ToString()); } } for (int j = 0; j < tableSched.Rows.Count; j++) { if (dates[i].Date == PIn.Date(tableSched.Rows[j]["SchedDate"].ToString()).Date) { scheduledForDay += PIn.Decimal(tableSched.Rows[j]["Amount"].ToString()); } } for (int j = 0; j < listProdGoal.Count; j++) { if (dates[i].Date == listProdGoal[j].Date) { prodGoalForDay += (decimal)(listProdGoal[j].Hours * listProdGoal[j].ProdGoal); //Multiply the hours for this schedule by the amount of production goal for this prov. } } totalproductionForDay = productionForDay + adjustsForDay + inswriteoffsForDay + insWriteoffAdjsForDay + scheduledForDay; row["Production"] = productionForDay.ToString("n"); row["Prod Goal"] = prodGoalForDay.ToString("n"); row["Scheduled"] = scheduledForDay.ToString("n"); row["Adjusts"] = adjustsForDay.ToString("n"); if (writeoffPayType == PPOWriteoffDateCalc.ClaimPayDate) { row["Writeoff Est"] = inswriteoffsForDay.ToString("n"); row["Writeoff Adj"] = insWriteoffAdjsForDay.ToString("n"); } else { row["Writeoff"] = inswriteoffsForDay.ToString("n"); } row["Tot Prod"] = totalproductionForDay.ToString("n"); dt.Rows.Add(row); } DataSet ds = null; ds = new DataSet("MonthlyData"); ds.Tables.Add(dt); if (listClinics.Count != 0) { ds.Tables.Add(dtClinic); } return(ds); }
///<summary>If not using clinics then supply an empty list of clinicNums.</summary> public static DataTable GetWriteoffTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums , bool hasAllClinics, bool hasClinicsEnabled, PPOWriteoffDateCalc writeoffPayType) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, hasAllClinics , hasClinicsEnabled, writeoffPayType)); } string whereProv = ""; if (listProvNums.Count > 0) { whereProv += " AND claimproc.ProvNum IN(" + string.Join(",", listProvNums) + ") "; } string whereClin = ""; if (hasClinicsEnabled && listClinicNums.Count > 0) //Using clinics { whereClin += " AND claimproc.ClinicNum IN(" + string.Join(",", listClinicNums) + ") "; } string query = "SET @FromDate=" + POut.Date(dateStart) + ", @ToDate=" + POut.Date(dateEnd) + ";"; if (writeoffPayType == PPOWriteoffDateCalc.InsPayDate) { query += "SELECT " + DbHelper.DtimeToDate("claimproc.DateCP") + " date," + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + "," + "carrier.CarrierName," + "provider.Abbr,"; if (hasClinicsEnabled) { query += "clinic.Abbr Clinic,"; } query += "SUM(claimproc.WriteOff) $amount, " + "claimproc.ClaimNum " + "FROM claimproc " //,insplan,patient,carrier,provider " + "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 clinic ON clinic.ClinicNum=claimproc.ClinicNum " + "WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + ") " + whereProv + whereClin + "AND claimproc.DateCP >= @FromDate " + "AND claimproc.DateCP <= @ToDate " + "AND (claimproc.WriteOff > .0001 OR claimproc.WriteOff < -.0001) " + "GROUP BY claimproc.ProvNum,claimproc.DateCP,claimproc.ClinicNum,claimproc.PatNum " + "ORDER BY claimproc.DateCP,claimproc.PatNum"; } else if (writeoffPayType == PPOWriteoffDateCalc.ProcDate) //Means PPOWiteoffDateCalc==ProcDate, so we use the procedure date. { query += "SELECT " + DbHelper.DtimeToDate("claimproc.ProcDate") + " date, " + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ", " + "carrier.CarrierName, " + "provider.Abbr,"; if (hasClinicsEnabled) { query += "clinic.Abbr Clinic,"; } query += "SUM(claimproc.WriteOff) $amount " + "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 clinic ON clinic.ClinicNum=claimproc.ClinicNum " + "WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") " + whereProv + whereClin + "AND claimproc.ProcDate >= @FromDate " + "AND claimproc.ProcDate <= @ToDate " + "AND (claimproc.WriteOff > .0001 OR claimproc.WriteOff < -.0001) " + "GROUP BY claimproc.ProvNum,claimproc.ProcDate,claimproc.ClinicNum,claimproc.PatNum " + "ORDER BY claimproc.ProcDate,claimproc.PatNum"; } else //writeoffPayType==PPOWriteoffDateCalc.ClaimPayDate { query += "SELECT " + DbHelper.DtimeToDate("claimsnapshot.DateTEntry") + " date, " + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ", " + "carrier.CarrierName, " + "provider.Abbr,"; if (hasClinicsEnabled) { query += "clinic.Abbr Clinic,"; } query += "SUM(claimsnapshot.WriteOff) $amount " + "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 clinic ON clinic.ClinicNum=claimproc.ClinicNum " + "INNER JOIN claimsnapshot on claimsnapshot.ClaimProcNum=claimproc.ClaimProcNum " //use claimsnapshot DateTEntry instead of claimproc + "WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") " + whereProv + whereClin + "AND claimsnapshot.DateTEntry >= @FromDate " + "AND claimsnapshot.DateTEntry <= @ToDate " + "AND claimsnapshot.WriteOff > 0 " + "GROUP BY claimproc.ProvNum,claimsnapshot.DateTEntry,claimproc.ClinicNum,claimproc.PatNum " + "ORDER BY claimsnapshot.DateTEntry,claimproc.PatNum"; } return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(query))); }