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