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