/**
* @param \Roomify\Bat\Event\Event $event
* @param $granularity
*
* @return bool
*/
public function storeEvent(Event $event, $granularity = Event::BAT_HOURLY)
{
$stored = TRUE;
$transaction = db_transaction();
try {
// Itemize an event so we can save it
$itemized = $event->itemize(new EventItemizer($event, $granularity));
//Write days
foreach ($itemized[Event::BAT_DAY] as $year => $months) {
foreach ($months as $month => $days) {
db_merge($this->day_table_no_prefix)->key(array('unit_id' => $event->getUnitId(), 'year' => $year, 'month' => $month))->fields($days)->execute();
}
}
if ($granularity == Event::BAT_HOURLY && isset($itemized[Event::BAT_HOUR])) {
// Write Hours
foreach ($itemized[Event::BAT_HOUR] as $year => $months) {
foreach ($months as $month => $days) {
foreach ($days as $day => $hours) {
// Count required as we may receive empty hours for granular events that start and end on midnight
if (count($hours) > 0) {
db_merge($this->hour_table_no_prefix)->key(array('unit_id' => $event->getUnitId(), 'year' => $year, 'month' => $month, 'day' => substr($day, 1)))->fields($hours)->execute();
}
}
}
}
//If we have minutes write minutes
foreach ($itemized[Event::BAT_MINUTE] as $year => $months) {
foreach ($months as $month => $days) {
foreach ($days as $day => $hours) {
foreach ($hours as $hour => $minutes) {
db_merge($this->minute_table_no_prefix)->key(array('unit_id' => $event->getUnitId(), 'year' => $year, 'month' => $month, 'day' => substr($day, 1), 'hour' => substr($hour, 1)))->fields($minutes)->execute();
}
}
}
}
}
} catch (\Exception $e) {
$stored = FALSE;
$transaction->rollback();
watchdog_exception('BAT Event Save Exception', $e);
}
return $stored;
}
/**
* @param \DateTime $start_date
* @param \DateTime $end_date
* @param $unit_ids
*
* @return array
*/
public function buildQueries(\DateTime $start_date, \DateTime $end_date, $unit_ids)
{
$queries = array();
$queries[Event::BAT_DAY] = 'SELECT * FROM ' . $this->day_table . ' WHERE ';
$queries[Event::BAT_HOUR] = 'SELECT * FROM ' . $this->hour_table . ' WHERE ';
$queries[Event::BAT_MINUTE] = 'SELECT * FROM ' . $this->minute_table . ' WHERE ';
// Create a mock event which we will use to determine how to query the database
$mock_event = new Event($start_date, $end_date, new Unit(0, 0, NULL), -10);
// We don't need a granular event even if we are retrieving granular data - since we don't
// know what the event break-down is going to be we need to get the full range of data from
// days, hours and minutes.
$itemized = $mock_event->itemize(new EventItemizer($mock_event, Event::BAT_DAILY));
$year_count = 0;
$query_parameters = '';
foreach ($itemized[Event::BAT_DAY] as $year => $months) {
if ($year_count > 0) {
// We are dealing with multiple years so add an OR
$query_parameters .= ' OR ';
}
$query_parameters .= 'year IN (' . $year . ') ';
$query_parameters .= 'AND month IN (' . implode(",", array_keys($months)) . ') ';
if (count($unit_ids) > 0) {
// Unit ids are defined so add this as a filter
$query_parameters .= 'AND unit_id in (' . implode(",", $unit_ids) . ') ';
}
$year_count++;
}
// Add parameters to each query
$queries[Event::BAT_DAY] .= $query_parameters;
$queries[Event::BAT_HOUR] .= $query_parameters;
$queries[Event::BAT_MINUTE] .= $query_parameters;
// Clean up and add ordering information
$queries[Event::BAT_DAY] .= ' ORDER BY unit_id, year, month';
$queries[Event::BAT_HOUR] .= ' ORDER BY unit_id, year, month, day';
$queries[Event::BAT_MINUTE] .= ' ORDER BY unit_id, year, month, day, hour';
return $queries;
}
/**
* @group failing
*/
public function testEventItemizeIncludingTwoYearsAndFebruary()
{
$event_state = 5;
$start_date = new \DateTime('2015-12-31 10:00');
$end_date = new \DateTime('2016-04-30 12:12');
$unit = new Unit(1, 2, array());
$event = new Event($start_date, $end_date, $unit, $event_state);
$itemized = $event->itemize(new EventItemizer($event));
// First day should be -1
$this->assertEquals($itemized[Event::BAT_DAY]['2015']['12']['d31'], '-1');
// Hours 10am to 23pm included should be 5
for ($i = 10; $i <= 23; $i++) {
$this->assertEquals($itemized[Event::BAT_HOUR]['2015']['12']['d31']['h' . $i], '5');
}
// Every day of February should be 5
for ($i = 1; $i <= 29; $i++) {
$this->assertEquals($itemized[Event::BAT_DAY]['2016']['2']['d' . $i], '5');
}
// Every day of March should be 5
for ($i = 1; $i <= 31; $i++) {
$this->assertEquals($itemized[Event::BAT_DAY]['2016']['3']['d' . $i], '5');
}
// The first 29 days of April should be 5
for ($i = 1; $i <= 29; $i++) {
$this->assertEquals($itemized[Event::BAT_DAY]['2016']['4']['d' . $i], '5');
}
// Hours 00am to 11pm included should be 5
for ($i = 0; $i <= 11; $i++) {
$this->assertEquals($itemized[Event::BAT_HOUR]['2016']['4']['d30']['h' . $i], '5');
}
// Hour 12 should be -1
$this->assertEquals($itemized[Event::BAT_HOUR]['2016']['4']['d30']['h12'], '-1');
// Minutes 1-12 should be 5
for ($i = 0; $i <= 12; $i++) {
if ($i <= 9) {
$index = 'm0' . $i;
} else {
$index = 'm' . $i;
}
$this->assertEquals($itemized[Event::BAT_MINUTE]['2016']['4']['d30']['h12'][$index], '5');
}
}
/**
* Provides an itemized array of events keyed by the unit_id and divided by day,
* hour and minute.
*
* @param \DateTime $start_date
* @param \DateTime $end_date
* @param String $granularity
*
* @return array
*/
public function getEventsItemized(\DateTime $start_date, \DateTime $end_date, $granularity = Event::BAT_HOURLY)
{
// The final events we will return
$events = array();
$keyed_units = $this->keyUnitsById();
$db_events = $this->store->getEventData($start_date, $end_date, array_keys($keyed_units));
// Create a mock itemized event for the period in question - since event data is either
// in the database or the default value we first create a mock event and then fill it in
// accordingly
$mock_event = new Event($start_date, $end_date, new Unit(0, 0), $this->default_value);
$itemized = $mock_event->itemize(new EventItemizer($mock_event, $granularity));
// Cycle through each unit retrieved and provide it with a fully configured itemized mock event
foreach ($db_events as $unit => $event) {
// Add the mock event
$events[$unit] = $itemized;
$events[$unit][Event::BAT_DAY] = $this->itemizeDays($db_events, $itemized, $unit, $keyed_units);
// Handle hours
if (isset($itemized[Event::BAT_HOUR]) || isset($db_events[$unit][Event::BAT_HOUR])) {
$events[$unit][Event::BAT_HOUR] = $this->itemizeHours($db_events, $itemized, $unit, $keyed_units);
} else {
// No hours - set an empty array
$events[$unit][Event::BAT_HOUR] = array();
}
// Handle minutes
if (isset($itemized[Event::BAT_MINUTE]) || isset($db_events[$unit][Event::BAT_MINUTE])) {
$events[$unit][Event::BAT_MINUTE] = $this->itemizeMinutes($db_events, $itemized, $unit, $keyed_units);
} else {
// No minutes - set an empty array
$events[$unit][Event::BAT_MINUTE] = array();
}
}
// Check to see if any events came back from the db
foreach ($keyed_units as $id => $unit) {
// If we don't have any db events add mock events (itemized)
if (isset($events[$id]) && count($events[$id]) == 0 || !isset($events[$id])) {
$empty_event = new Event($start_date, $end_date, $unit, $unit->getDefaultValue());
$events[$id] = $empty_event->itemize(new EventItemizer($empty_event, $granularity));
}
}
return $events;
}
/**
* @param \Roomify\Bat\Event\Event $event
* @param $granularity
*
* @return bool
*/
public function storeEvent(Event $event, $granularity = Event::BAT_HOURLY)
{
$stored = TRUE;
// Get existing event data from db
$existing_events = $this->getEventData($event->getStartDate(), $event->getEndDate(), array($event->getUnitId()));
try {
// Itemize an event so we can save it
$itemized = $event->itemize(new EventItemizer($event, $granularity));
// Write days
foreach ($itemized[Event::BAT_DAY] as $year => $months) {
foreach ($months as $month => $days) {
$values = array_values($days);
$keys = array_keys($days);
// Because SQLite does not have a nice merge first we have to check if a row exists to determine whether to do an insert or an update
if (isset($existing_events[$event->getUnitId()][EVENT::BAT_DAY][$year][$month])) {
$command = "UPDATE {$this->day_table} SET ";
foreach ($days as $day => $value) {
$command .= "{$day} = {$value},";
}
$command = rtrim($command, ',');
$command .= " WHERE unit_id = " . $event->getUnitId() . " AND year = {$year} AND month = {$month}";
$this->pdo->exec($command);
} else {
$this->pdo->exec("INSERT INTO {$this->day_table} (unit_id, year, month, " . implode(', ', $keys) . ") VALUES (" . $event->getUnitId() . ", {$year}, {$month}, " . implode(', ', $values) . ")");
}
}
}
if ($granularity == Event::BAT_HOURLY && isset($itemized[Event::BAT_HOUR])) {
// Write Hours
foreach ($itemized[Event::BAT_HOUR] as $year => $months) {
foreach ($months as $month => $days) {
foreach ($days as $day => $hours) {
// Count required as we may receive empty hours for granular events that start and end on midnight
if (count($hours) > 0) {
$values = array_values($hours);
$keys = array_keys($hours);
if (isset($existing_events[$event->getUnitId()][EVENT::BAT_HOUR][$year][$month][$day])) {
$command = "UPDATE {$this->hour_table} SET ";
foreach ($hours as $hour => $value) {
$command .= "{$hour} = {$value},";
}
$command = rtrim($command, ',');
$command .= " WHERE unit_id = " . $event->getUnitId() . " AND year = {$year} AND month = {$month} AND day = " . substr($day, 1);
$this->pdo->exec($command);
} else {
$this->pdo->exec("INSERT INTO {$this->hour_table} (unit_id, year, month, day, " . implode(', ', $keys) . ") VALUES (" . $event->getUnitId() . ", {$year}, {$month}, " . substr($day, 1) . ", " . implode(', ', $values) . ")");
}
}
}
}
}
// If we have minutes write minutes
foreach ($itemized[Event::BAT_MINUTE] as $year => $months) {
foreach ($months as $month => $days) {
foreach ($days as $day => $hours) {
foreach ($hours as $hour => $minutes) {
$values = array_values($minutes);
$keys = array_keys($minutes);
if (isset($existing_events[$event->getUnitId()][EVENT::BAT_MINUTE][$year][$month][$day][$hour])) {
$command = "UPDATE {$this->minute_table} SET ";
foreach ($minutes as $minute => $value) {
$command .= "{$minute} = {$value},";
}
$command = rtrim($command, ',');
$command .= " WHERE unit_id = " . $event->getUnitId() . " AND year = {$year} AND month = {$month} AND day = " . substr($day, 1) . " AND hour = " . substr($hour, 1);
$this->pdo->exec($command);
} else {
$this->pdo->exec("INSERT INTO {$this->minute_table} (unit_id, year, month, day, hour, " . implode(', ', $keys) . ") VALUES (" . $event->getUnitId() . ", {$year}, {$month}, " . substr($day, 1) . ", " . substr($hour, 1) . ", " . implode(', ', $values) . ")");
}
}
}
}
}
}
} catch (\Exception $e) {
$stored = FALSE;
}
return $stored;
}
public function testEventItemizeEventOneDay()
{
$event_state = 5;
$start_date = new \DateTime('2016-03-01 00:00');
$end_date = new \DateTime('2016-03-01 23:59');
$unit = new Unit(1, 2, array());
$event = new Event($start_date, $end_date, $unit, $event_state);
$itemized = $event->itemize(new EventItemizer($event));
$this->assertEquals($itemized[Event::BAT_DAY]['2016']['3']['d1'], '5');
}
