public override DataTable Extract()
{
DateTime sd = Period;
DateTime ed = Period.AddMonths(1);
var reader = new RoomDataReader(Connection);
_ds = reader.ReadRoomDataClean(sd, ed, ClientID, RoomID);
return(_ds.Tables["RoomDataClean"]);
}
public override DataTable Extract()
{
//get all store data for period
DateTime sd = Period;
DateTime ed = Period.AddMonths(1);
var reader = new StoreDataReader(Connection);
return(reader.ReadStoreDataClean(sd, ed, ClientID, ItemID, StoreDataCleanOption.RechargeItems));
}
private DataCleanCommand GetDataCleanCommand(BillingCategory bc)
{
return(new DataCleanCommand
{
BillingCategory = bc,
StartDate = Period,
EndDate = Period.AddMonths(1),
ClientID = ClientID
});
}
public override DataTable Extract()
{
DateTime sd = Period;
DateTime ed = Period.AddMonths(1);
// Data for all clients must be selected for transferred duration calculations so always pass 0 for clientId
var reader = new ToolDataReader(Connection);
_ds = reader.ReadToolDataClean(sd, ed, 0, ResourceID);
return(_ds.Tables["ToolDataClean"]);
}
protected virtual void FillOutputTable(DataTable dtOutput, DataTable dtToolDataClean)
{
DateTime sd = Period;
DateTime ed = Period.AddMonths(1);
foreach (DataRow dr in dtToolDataClean.Rows)
{
if (dr.RowState != DataRowState.Deleted)
{
// ClientID check. dtToolDataClean contains data for all clients even when ClientID > 0 because of transferred duration calculation.
int clientId = dr.Field <int>("ClientID");
if (ClientID == 0 || ClientID == clientId)
{
DateTime actDate = Convert.ToDateTime(dr["BeginDateTime"]).Date;
double schedDuration = dr.Field <double>("SchedDuration");
if ((actDate >= sd && actDate < ed) && schedDuration != 0)
{
DataRow ndr = dtOutput.NewRow();
ndr.SetField("Period", Period);
ndr.SetField("ClientID", clientId);
ndr.SetField("ResourceID", dr.Field <int>("ResourceID"));
ndr.SetField("RoomID", dr.Field <int>("RoomID"));
ndr.SetField("ActDate", actDate);
ndr.SetField("AccountID", dr.Field <int>("AccountID"));
ndr.SetField("Uses", dr.Field <double>("Uses"));
ndr.SetField("SchedDuration", schedDuration);
ndr.SetField("ActDuration", dr.Field <double>("ActDuration"));
ndr.SetField("Overtime", dr.Field <double>("Overtime"));
ndr.SetField("Days", 0D); //calulated in ToolDataAdjust
ndr.SetField("Months", 0D); //calulated in ToolDataAdjust
ndr.SetField("IsStarted", dr.Field <bool>("IsStarted"));
ndr.SetField("ChargeMultiplier", dr.Field <double>("ChargeMultiplier"));
ndr.SetField("ReservationID", dr.Field <int>("ReservationID"));
ndr.SetField("ChargeDuration", dr.Field <double>("ChargeDuration"));
ndr.SetField("TransferredDuration", dr.Field <double>("TransferredDuration"));
ndr.SetField("MaxReservedDuration", dr.Field <double>("MaxReservedDuration"));
ndr.SetField("ChargeBeginDateTime", dr.Field <DateTime>("ChargeBeginDateTime"));
ndr.SetField("ChargeEndDateTime", dr.Field <DateTime>("ChargeEndDateTime"));
ndr.SetField("IsActive", dr.Field <bool>("IsActive"));
ndr.SetField("IsCancelledBeforeAllowedTime", dr.Field <bool>("IsCancelledBeforeAllowedTime"));
dtOutput.Rows.Add(ndr);
}
}
}
}
}
private DataTable GetClientAccountDataForRoom()
{
DateTime sd = Period;
DateTime ed = Period.AddMonths(1);
using (var cmd = Connection.CreateCommand("dbo.ClientAccount_Select"))
using (var adap = new SqlDataAdapter(cmd))
{
cmd.Parameters.AddWithValue("Action", "ForRoomData");
cmd.Parameters.AddWithValue("sDate", sd);
cmd.Parameters.AddWithValue("eDate", ed);
AddParameterIf(cmd, "ClientID", ClientID > 0, ClientID);
var dt = new DataTable();
adap.Fill(dt);
return(dt);
}
}
protected override void FillOutputTable(DataTable dtOutput, DataTable dtToolDataClean)
{
foreach (DataRow dr in dtToolDataClean.Rows)
{
if (dr.RowState != DataRowState.Deleted)
{
// ClientID check. dtToolDataClean contains data for all clients even when ClientID > 0 because of transferred duration calculation.
int clientId = dr.Field <int>("ClientID");
if (ClientID == 0 || ClientID == clientId)
{
DateTime actDate = dr.Field <DateTime>("BeginDateTime").Date;
if ((actDate >= Period && actDate < Period.AddMonths(1)) && dr.Field <double>("SchedDuration") != 0D)
{
DataRow ndr = dtOutput.NewRow();
ndr.SetField("Period", new DateTime(actDate.Year, actDate.Month, 1));
ndr.SetField("ClientID", clientId);
ndr.SetField("ResourceID", dr.Field <int>("ResourceID"));
ndr.SetField("RoomID", dr.Field <int>("RoomID"));
ndr.SetField("ActDate", actDate);
ndr.SetField("AccountID", dr.Field <int>("AccountID"));
ndr.SetField("Uses", dr.Field <double>("Uses"));
ndr.SetField("SchedDuration", dr.Field <double>("SchedDuration"));
ndr.SetField("ActDuration", dr.Field <double>("ActDuration"));
ndr.SetField("Overtime", dr.Field <double>("Overtime"));
ndr.SetField("Days", 0D); //calculated in ToolDataAdjust
ndr.SetField("Months", 0D); //calulated in ToolDataAdjust
ndr.SetField("IsStarted", dr.Field <bool>("IsStarted"));
ndr.SetField("ChargeMultiplier", dr.Field <double>("ChargeMultiplier"));
ndr.SetField("ReservationID", dr.Field <int>("ReservationID"));
ndr.SetField("ChargeDuration", dr.Field <double>("ChargeDuration"));
ndr.SetField("TransferredDuration", 0D); //not applicable in old billing model
ndr.SetField("MaxReservedDuration", dr.Field <double>("MaxReservedDuration"));
ndr.SetField("ChargeBeginDateTime", dr.Field <DateTime>("ChargeBeginDateTime"));
ndr.SetField("ChargeEndDateTime", dr.Field <DateTime>("ChargeEndDateTime"));
ndr.SetField("IsActive", dr.Field <bool>("IsActive"));
ndr.SetField("IsCancelledBeforeAllowedTime", dr.Field <bool>("IsCancelledBeforeAllowedTime"));
dtOutput.Rows.Add(ndr);
}
}
}
}
}
//in this function, we first find all the days an event belongs to, then agg by day
//don't need to worry about charged rooms as clean data only has charged rooms
private DataTable AggRoomDataClean(DataTable dtRoomData)
{
DataTable dtAggRoomDataClean = new DataTable("AggRoomDataClean");
dtAggRoomDataClean.Columns.Add("ClientID", typeof(int));
dtAggRoomDataClean.Columns.Add("RoomID", typeof(int));
dtAggRoomDataClean.Columns.Add("EntryDT", typeof(DateTime));
dtAggRoomDataClean.Columns.Add("Entries", typeof(double));
dtAggRoomDataClean.Columns.Add("Duration", typeof(double));
DataTable dtClient = _ds.Tables["Client"];
DataTable dtRoom = _ds.Tables["Room"];
dtRoomData.Columns.Add("eDay", typeof(int)); //used below in the compute
dtRoomData.Columns.Add("Entries", typeof(double));
_ds.Tables.Add(dtAggRoomDataClean);
if (dtRoomData.Rows.Count == 0)
{
return(dtAggRoomDataClean);
}
double entries;
double duration;
DataRow ndr;
// need to use for (not foreach) because the enumeration changes when a row is added and this causes an exception
int rowCount = dtRoomData.Rows.Count;
for (int i = 0; i < rowCount; i++)
{
DataRow drRoomDataClean = dtRoomData.Rows[i];
bool passbackRoom = drRoomDataClean.Field <bool>("PassbackRoom");
DateTime entryDate = drRoomDataClean.Field <DateTime>("EntryDT");
drRoomDataClean.SetField("eDay", entryDate.Day);
drRoomDataClean.SetField("Entries", 1D);
if (passbackRoom)
{
DateTime exitDate = drRoomDataClean.Field <DateTime>("exitDT");
//note that no room event can be longer than MaxTime hours
if (entryDate.Day != exitDate.Day)
{
//entry date and exit date are different, we have to create a new data row
DateTime newDate = new DateTime(exitDate.Year, exitDate.Month, exitDate.Day);
// [2021-01-27 jg] We now will put the entire Entry amount (1) on the day the entry occurred and zero on the day the exit occurred.
var entryDuration = newDate.Subtract(entryDate).TotalHours;
var exitDuration = exitDate.Subtract(newDate).TotalHours;
drRoomDataClean.SetField("ExitDT", newDate);
drRoomDataClean.SetField("Duration", entryDuration);
drRoomDataClean.SetField("Entries", 1D);
ndr = dtRoomData.NewRow();
ndr.ItemArray = drRoomDataClean.ItemArray; //start by copying everything
ndr.SetField("EntryDT", newDate);
ndr.SetField("eDay", newDate.Day);
ndr.SetField("ExitDT", exitDate);
ndr.SetField("Duration", exitDuration);
ndr.SetField("Entries", 0D);
dtRoomData.Rows.Add(ndr);
}
}
}
DateTime sd = Period;
DateTime ed = Period.AddMonths(1);
// delete the records that are out of bound of current month, typically it happens when user enters
// at the last day of month and exit the first day of month etc.
DataRow[] fdrDelete = dtRoomData.Select($"EntryDT < '{sd}' OR EntryDT >= '{ed}'");
foreach (DataRow dr in fdrDelete)
{
dtRoomData.Rows.Remove(dr); // Remove is the same as calling Delete and then AcceptChanges per the Microsoft docs
}
// at this point, the client/room has proper number of records
// so, at this time, agg by day and add records to new dt
// TODO: make this more efficient
int upperbound = ed.Subtract(sd).Days + 1;
for (int i = 1; i <= upperbound; i++)
{
foreach (DataRow drClient in dtClient.Rows)
{
foreach (DataRow drRoom in dtRoom.Rows)
{
int cid = drClient.Field <int>("ClientID");
int rid = drRoom.Field <int>("RoomID");
string filter = $"eDay = {i} AND ClientID = {cid} AND RoomID = {rid}";
object val = dtRoomData.Compute("SUM(Entries)", filter);
if (!Utility.IsDBNullOrNull(val))
{
entries = Utility.ConvertToDouble(val);
duration = Utility.ConvertToDouble(dtRoomData.Compute("SUM(Duration)", filter));
ndr = dtAggRoomDataClean.NewRow();
ndr.SetField("ClientID", cid);
ndr.SetField("RoomID", rid);
ndr.SetField("EntryDT", sd.AddDays(i - 1));
ndr.SetField("Entries", entries);
ndr.SetField("Duration", duration);
dtAggRoomDataClean.Rows.Add(ndr);
}
}
}
}
// These are already existing rows in RoomDataClean, there's nothing to be inserted, updated, or deleted
// so this is just a precaution to make sure nothing happens later.
dtAggRoomDataClean.AcceptChanges();
return(dtAggRoomDataClean);
}
//This handles generation of toolData table since 2011-04-01
public override void ProcessCleanData(DataTable dtToolDataClean)
{
// must be done before any changes are made to the table, this will be used later to calculate transfer durations
_durations = GetReservationDurations(dtToolDataClean);
dtToolDataClean.Columns.Add("Uses", typeof(double));
dtToolDataClean.Columns.Add("TransferredDuration", typeof(double));
dtToolDataClean.Columns.Add("IsCancelledBeforeAllowedTime", typeof(bool));
dtToolDataClean.Columns.Add("ChargeBeginDateTime", typeof(DateTime));
dtToolDataClean.Columns.Add("ChargeEndDateTime", typeof(DateTime));
dtToolDataClean.Columns.Add("ChargeDuration", typeof(double));
//handled started separately from unstarted
double schedDuration, chargeDuration;
DateTime beginDateTime, endDateTime, actualBeginDateTime, actualEndDateTime, chargeBeginDateTime, chargeEndDateTime;
int toolDataCleanRowCount = dtToolDataClean.Rows.Count;
//2011-12-05 Get ReservationHistory so we can calculate the max charge time
//DataTable dtResHistory = GetReservationHistory();
for (int i = 0; i < toolDataCleanRowCount; i++)
{
DataRow dr = dtToolDataClean.Rows[i];
// ClientID check. dtToolDataClean contains data for all clients even when ClientID > 0 because of transferred duration calculation.
if (ClientID == 0 || ClientID == dr.Field <int>("ClientID"))
{
//only chargeable activities get written to Tooldata
if (IsChargeable(dr))
{
//this means reservation was started
schedDuration = dr.Field <double>("SchedDuration");
beginDateTime = dr.Field <DateTime>("BeginDateTime");
endDateTime = beginDateTime.AddMinutes(schedDuration); //to prevent auto end's modified end time, which is wrong
actualBeginDateTime = dr.Field <DateTime>("ActualBeginDateTime");
actualEndDateTime = dr.Field <DateTime>("ActualEndDateTime");
dr["IsCancelledBeforeAllowedTime"] = IsCancelledBeforeAllowedTime(dr);
DateTime begDate = (beginDateTime > actualBeginDateTime) ? actualBeginDateTime : beginDateTime;
DateTime finDate = (endDateTime > actualEndDateTime) ? endDateTime : actualEndDateTime;
chargeBeginDateTime = begDate;
chargeEndDateTime = finDate;
chargeDuration = (chargeEndDateTime - chargeBeginDateTime).TotalMinutes;
dr["ChargeBeginDateTime"] = begDate;
dr["ChargeEndDateTime"] = finDate;
dr["ChargeDuration"] = chargeDuration;
begDate = begDate.Date;
//if the finDate is right on the edge, eg. the next day at 00:00am, we don't need to create a new row, because it's not crossing day
if (finDate == new DateTime(finDate.Year, finDate.Month, finDate.Day, 0, 0, 0))
{
finDate = finDate.AddSeconds(-1);
}
finDate = finDate.Date;
if (begDate != finDate)
{
//handle case where reservation starts and ends on different days
double runningOverTime = 0D;
int loopDays = finDate.Subtract(begDate).Days;
for (int j = 0; j <= loopDays; j++)
{
DateTime bDate = begDate.AddDays(j);
DateTime fDate = begDate.AddDays(j + 1);
DataRow adr = dtToolDataClean.NewRow();
adr.ItemArray = dr.ItemArray; //start by copying everything
adr["ChargeBeginDateTime"] = (bDate > chargeBeginDateTime) ? bDate : chargeBeginDateTime;
adr["ChargeEndDateTime"] = (fDate < chargeEndDateTime) ? fDate : chargeEndDateTime;
adr["ChargeDuration"] = (adr.Field <DateTime>("ChargeEndDateTime") - adr.Field <DateTime>("ChargeBeginDateTime")).TotalMinutes;
adr["Uses"] = adr.Field <double>("ChargeDuration") / chargeDuration;
adr["MaxReservedDuration"] = GetMaxReservedDuration(adr);
////2011-11-08 we have to see of MaxReservedDuration is greater than the ChargeDuration - preventing cases when user shortened the reservation after the 2 hours allowed time
//if (!Convert.ToBoolean(adr["IsCancelledBeforeAllowedTime"]))
//{
// if (Convert.ToDouble(adr["MaxReservedDuration"]) > Convert.ToDouble(adr["ChargeDuration"]))
// adr["ChargeDuration"] = adr["MaxReservedDuration"];
//}
//For backward compatibilty reason, I have to repopulate the correct ActDuration as well
adr["ActualBeginDateTime"] = (bDate > actualBeginDateTime) ? bDate : actualBeginDateTime;
adr["ActualEndDateTime"] = (fDate < actualEndDateTime) ? fDate : actualEndDateTime;
var fixDate = new DateTime(2012, 12, 1);
if (Period >= fixDate)
{
/*
* [jg 2013-01-24]
* Only do this for periods after 2012-12-01. Before that time the next line of code was missing which caused the 2nd part of a split
* reservation to not be written to the database. In other words the part that happened on the first day of the period. Note: this is
* only for reservations that cross over two periods. For periods before Dec 2012 we are purposefully doing this wrong so that amounts
* billed previously aren't changed retroactively.
*
* [jg 2020-02-04]
* Note that only ChargeBeginDateTime ChargeEndDateTime, ChargeDuration, ActDuration, and SchedDuration are in ToolData.
* Other columns like BeginDateTime, ActualBeginDateTime, etc are set here only because they're used in calculations or
* checks elsewhere.
*/
adr["BeginDateTime"] = adr["ActualBeginDateTime"]; //for convenience below when writing new table
}
// ActDuration will only be for this part of a multi-part reservation because the actuals were modified above
adr["ActDuration"] = adr.Field <DateTime>("ActualEndDateTime").Subtract(adr.Field <DateTime>("ActualBeginDateTime")).TotalMinutes;
// make sure ActDuration is greater than 0, because if a user finish this reservation on the same day (a cross day reservation originally),
// it's possible to have negative ActDuration
if (adr.Field <double>("ActDuration") < 0D)
{
adr["ActDuration"] = 0D; //since user finished on the previous day, today's reservation actual duration is zero
}
if (adr.Field <DateTime>("ChargeEndDateTime") > endDateTime)
{
adr["OverTime"] = (adr.Field <DateTime>("ChargeEndDateTime") - endDateTime).TotalMinutes - runningOverTime;
runningOverTime += adr.Field <double>("OverTime");
}
else
{
adr["OverTime"] = 0D;
}
// [2022-03-25 jg] Rounding to two digits now
RoundDouble(adr, "ActDuration");
RoundDouble(adr, "ChargeDuration");
RoundDouble(adr, "OverTime");
RoundDouble(adr, "MaxReservedDuration");
dtToolDataClean.Rows.Add(adr); //will add to end
//make sure next month's data is not included.
if (adr.Field <DateTime>("ChargeBeginDateTime") >= Period.AddMonths(1))
{
adr.Delete();
}
}
dr.Delete(); //remove original, multi-day row
}
else
{
dr["Uses"] = 1D;
if (!dr.Field <bool>("IsStarted"))
{
dr["ActDuration"] = dr["SchedDuration"];
}
//2011-11-08 we have to see of MaxReservedDuration is greater than the ChargeDuration - preventing cases when user shortened the reservation after the 2 hours allowed time
if (!dr.Field <bool>("IsCancelledBeforeAllowedTime"))
{
//this will return a list of reservation changigin history ordered by ModifiedDateTime
DataRow[] drsResHistory = ReservationHistoryTable.Select($"ReservationID = {dr["ReservationID"]}");
//must be at least two rows (becuase I inlcude Insert event as well, when reservation is firstly created)
if (drsResHistory.Length > 1)
{
DataRow drPrevious = null;
double maxResTimeAfterTwoHoursLimit = 0.0;
double currentResTime;
bool flagtemp = false;
DateTime beginTimeTemp, endTimeTemp;
foreach (DataRow drResHist in drsResHistory)
{
//the idea here is compare every reservation change and find out the max reservation time
if (drResHist.Field <int>("BeforeMinutes") <= 120)
{
flagtemp = true;
beginTimeTemp = drResHist.Field <DateTime>("BeginDateTime");
endTimeTemp = drResHist.Field <DateTime>("EndDateTime");
currentResTime = (endTimeTemp - beginTimeTemp).TotalMinutes;
if (currentResTime > maxResTimeAfterTwoHoursLimit)
{
maxResTimeAfterTwoHoursLimit = currentResTime;
}
}
if (!flagtemp)
{
//We need to track the last change of reservation right before the 2 hours limit, this is our true chargeTime
drPrevious = drResHist;
}
}
if (drPrevious != null)
{
beginTimeTemp = drPrevious.Field <DateTime>("BeginDateTime");
endTimeTemp = drPrevious.Field <DateTime>("EndDateTime");
currentResTime = (endTimeTemp - beginTimeTemp).TotalMinutes;
if (currentResTime > maxResTimeAfterTwoHoursLimit)
{
maxResTimeAfterTwoHoursLimit = currentResTime;
}
}
//Lastly, make sure we charge the max time - please note that ChargeDuration could still be higher than maxResTimeAfterTwoHoursLimit because of actual tool usage time
if (maxResTimeAfterTwoHoursLimit > dr.Field <double>("ChargeDuration"))
{
dr["ChargeDuration"] = maxResTimeAfterTwoHoursLimit;
}
}
}
// [2022-03-25 jg] Rounding to two digits now
RoundDouble(dr, "ActDuration");
RoundDouble(dr, "ChargeDuration");
RoundDouble(dr, "OverTime");
RoundDouble(dr, "MaxReservedDuration");
}
}
else
{
dr.Delete(); //not chargeable, so remove
}
}
}
}
