contacts_events-8.x-1.x-dev/modules/village_allocation/src/VillageAllocationQueries.php
modules/village_allocation/src/VillageAllocationQueries.php
<?php
namespace Drupal\village_allocation;
use Drupal\Core\Database\Connection;
use Drupal\Core\Database\Query\SelectInterface;
use Drupal\Core\Entity\EntityTypeManagerInterface;
/**
* VA queries.
*
* @package Drupal\contacts_events_villages
*/
class VillageAllocationQueries {
/**
* Entity type manager.
*
* @var \Drupal\Core\Entity\EntityTypeManagerInterface
*/
protected $entityTypeManager;
/**
* Database.
*
* @var \Drupal\Core\Database\Connection
*/
protected $db;
/**
* VillageAllocationQueries constructor.
*
* @param \Drupal\Core\Database\Connection $db
* Database.
* @param \Drupal\Core\Entity\EntityTypeManagerInterface $entity_type_manager
* Entity type manager.
*/
public function __construct(Connection $db, EntityTypeManagerInterface $entity_type_manager) {
$this->db = $db;
$this->entityTypeManager = $entity_type_manager;
}
/**
* Query to calculate the pitches required for all bookings.
*
* @param int $event_id
* Optional event filter.
*
* @return \Drupal\Core\Database\Query\SelectInterface
* The query.
*/
public function getPitchesQuery($event_id = NULL): SelectInterface {
$query = $this->db->select('commerce_order_item', 'i');
$query->leftJoin('c_events_accommodation__pitch_size', 'p', "p.entity_id = i.purchased_entity and i.type = 'ce_accom_camping'");
$query->leftJoin('commerce_order__village', 'v', 'v.entity_id = i.order_id');
$query->groupBy('i.order_id');
$query->groupBy('v.village_target_id');
$query->addField('i', 'order_id');
$query->addField('v', 'village_target_id', 'village_id');
$query->addExpression('SUM(p.pitch_size_value * i.quantity)', 'pitches');
if ($event_id) {
$query->innerJoin('commerce_order__event', 'e', 'e.entity_id = i.order_id');
$query->condition('e.event_target_id', $event_id);
}
return $query;
}
/**
* Builds the base query used for village allocation.
*
* @param string $event_id
* Event ID.
* @param bool $exclude_already_allocated
* Whether to exclude bookings already allocated.
* @param bool $exclude_bookings_without_group
* Whether to exclude bookings without a VA group.
*
* @return \Drupal\Core\Database\Query\SelectInterface
* Query.
*/
public function getBaseQuery($event_id, $exclude_already_allocated, $exclude_bookings_without_group): SelectInterface {
// Start with all bookings.
$query = $this->db->select('commerce_order', 'o');
$query->condition('o.type', 'contacts_booking');
// Filter to current event.
$query->innerJoin('commerce_order__event', 'e', 'e.entity_id = o.order_id');
$query->condition('e.event_target_id', $event_id);
// Join to get the village group.
$query->leftJoin('commerce_order__village_group', 'og', 'og.entity_id = o.order_id');
$query->leftJoin('c_events_village_group', 'g', 'g.id = og.village_group_target_id');
if ($exclude_bookings_without_group) {
// May want to exclude bookings not in a group
// eg automatic allocation we don't care about bookings not in a group
// as they'll be allocated manually.
$query->isNotNull('og.village_group_target_id');
}
// Get the village.
$query->leftJoin('commerce_order__village', 'v', 'o.order_id = v.entity_id');
if ($exclude_already_allocated) {
// May want to exclude bookings already allocated.
$query->isNull('v.village_target_id');
}
// Check it has confirmed tickets.
$query->condition('o.state', 'draft', '<>');
return $query;
}
/**
* Gets groups for automatic allocation.
*
* @param string $event_id
* Event ID.
*
* @return array
* Array of objects containign properties group_id, group_name,
* orders (number of orders), pitches (number), postal_code,
*/
public function getGroupsForAutomaticAllocation($event_id) {
$query = $this->getBaseQuery($event_id, TRUE, TRUE);
$query->leftJoin($this->getPitchesQuery($event_id), 'pitches_to_allocate', 'pitches_to_allocate.order_id = o.order_id');
$query->addField('g', 'id', 'group_id');
$query->addField('g', 'name', 'group_name');
$query->addExpression("COUNT(DISTINCT o.order_id)", 'orders');
$query->addExpression("COALESCE(SUM(pitches_to_allocate.pitches), 0)", 'pitches');
$query->addField('g', 'allocation_sorting_code', 'postal_code');
$query->orderBy('g.allocation_sorting_code');
$query->groupBy('g.id');
$query->groupBy('g.name');
$query->groupBy('g.allocation_sorting_code');
$results = $query->execute()->fetchAllAssoc('group_id');
return $results;
}
/**
* Gets groups for manual allocation.
*
* @param string $event_id
* ID of event to allocate.
* @param int $village
* ID of the village to limit to. This could be 0 to indicate unallocated.
* @param string $village_operator
* Operator to use for the village filter. May be "=" or ">" etc.
* @param array $group_ids
* Array of group IDs to limit to.
*
* @return array
* Array of objects containing properties order_id, group_id, group_name,
* pitches (number).
*/
public function getBookingsInManualAllocationGroups($event_id, $village, string $village_operator, array $group_ids) {
if (empty($group_ids)) {
return [];
}
// Build the main query, limiting to the group IDs we've calculated.
$query = $this->getBaseQuery($event_id, FALSE, FALSE);
$query->leftJoin($this->getPitchesQuery($event_id), 'allocation', 'allocation.order_id = o.order_id');
$query->leftJoin('commerce_order__group_booking', 'group_booking', 'group_booking.entity_id = o.order_id');
$query->addField('o', 'order_id');
$query->addField('g', 'id', 'group_id');
$query->addExpression("IFNULL(g.name, 'Unknown')", 'group_name');
$query->addField('allocation', 'pitches');
$query->addField('group_booking', 'group_booking_target_id', 'group_booking_id');
// Limit to specific camping groups.
$query->condition('g.id', $group_ids, 'IN');
// Apply the village filter. A village id of 0 in the filter represents
// unallocated village (which doesn't really exist) so ensure nulls are
// converted to 0 for filtering.
$village_operator = $this->checkOperatorAllowed($village_operator);
$query->where("IFNULL(allocation.village_id, 0) $village_operator :village", [
':village' => $village,
]);
$query->orderBy('order_id');
$results = $query->execute()->fetchAllAssoc('order_id');
return $results;
}
/**
* Gets village stats.
*
* @param string $event_id
* The event being allocated.
*
* @return array
* Array of arrays with keys size, reserved, assigned.
*/
public function getVillageStats($event_id) {
$query = $this->db->select('c_events_village', 'v');
$query->leftJoin('commerce_order__village', 'ov', 'ov.village_target_id = v.id');
$query->leftJoin($this->getPitchesQuery($event_id), 'p', 'p.order_id = ov.entity_id and p.village_id = v.id');
$query->addExpression("COALESCE(SUM(v.pitches), 0)", 'size');
$query->addExpression("COALESCE(SUM(v.pitches * (1 - (v.fill_value / 100))), 0)", 'reserved');
$query->addExpression("COALESCE(SUM(p.pitches), 0)", 'assigned');
$query->condition('v.event', $event_id);
$stats = $query->execute()->fetchAssoc();
return $stats;
}
/**
* Gets booking stats for an event.
*
* @param string $event_id
* The event ID.
*
* @return array
* Array of arrays with keys churches, bookings, pitches.
*/
public function getBookingStats($event_id) {
$query = $this->getBaseQuery($event_id, TRUE, TRUE);
// Pitches subquery.
$query->leftJoin($this->getPitchesQuery($event_id), 'p', 'p.order_id = o.order_id');
$query->addExpression("COUNT(DISTINCT og.village_group_target_id)", 'churches');
$query->addExpression("COUNT(DISTINCT o.order_id)", 'bookings');
$query->addExpression("COALESCE(SUM(p.pitches), 0)", 'pitches');
$stats = $query->execute()->fetchAssoc();
return $stats;
}
/**
* Gets villages by their allocation order.
*
* Also adds a surrogate property (pitches_allocated) to each village
* entity with the calculated number of pitches currently allocated.
*
* @param string $event_id
* ID of event to retrieve villages.
*
* @return \Drupal\contacts_events_villages\Entity\Village[]
* Array of villages.
*
* @throws \Drupal\Component\Plugin\Exception\InvalidPluginDefinitionException
* @throws \Drupal\Component\Plugin\Exception\PluginNotFoundException
*/
public function getVillagesInOrder($event_id) {
$storage = $this->entityTypeManager->getStorage('c_events_village');
$query = $storage->getQuery();
$query->accessCheck(TRUE);
$query->condition('event', $event_id);
$query->sort('weight');
$villages = $storage->loadMultiple($query->execute());
$pitch_sizes_allocated = $this->getAllocatedVillageSizes($event_id);
foreach ($villages as $id => $village) {
$pitches_allocated = isset($pitch_sizes_allocated[$id])
? (float) $pitch_sizes_allocated[$id]->pitches_allocated
: 0;
// Add a surrogate property to the village that holds pitches allocated.
$village->pitches_allocated = $pitches_allocated;
}
return $villages;
}
/**
* Calculates the number of allocated pitches per village.
*
* @return array
* Array of objects keyed by village ID.
* Each object contains fields village_id and pitches_allocated.
*/
private function getAllocatedVillageSizes($event_id) {
// @todo Filter out cancelled orders?
$q = $this->db->select('commerce_order', 'o');
$q->join('commerce_order_item', 'i', "o.order_id = i.order_id and i.type = 'ce_accom_camping'");
$q->join('c_events_accommodation', 'a', 'i.purchased_entity = a.id');
$q->join('c_events_accommodation__pitch_size', 'p', 'p.entity_id = a.id');
$q->join('commerce_order__village', 'ov', 'ov.entity_id = o.order_id');
$q->join('c_events_village', 'v', 'v.id = ov.village_target_id');
$q->condition('v.event', $event_id);
$q->groupBy('v.id');
$q->addField('v', 'id', 'village_id');
$q->addExpression('SUM(p.pitch_size_value * i.quantity)', 'pitches_allocated');
$results = $q->execute()->fetchAllAssoc('village_id');
return $results;
}
/**
* Gets the village IDs allocated to a group.
*
* @param int $group_id
* ID of the group.
*
* @return array
* Array of village IDs to which the group has been allocated. There can
* be more than 1 if the group contains bookings split across multiple
* villages.
*/
public function getVillageIdsAllocatedToGroup($group_id) {
$q = $this->db->select('commerce_order', 'o');
$q->leftJoin('commerce_order__village_group', 'vg', 'o.order_id = vg.entity_id');
$q->innerJoin('commerce_order__village', 'v', 'o.order_id = v.entity_id');
$q->condition('vg.village_group_target_id', $group_id);
$q->addField('v', 'village_target_id', 'village_id');
$q->distinct();
$village_ids = $q->execute()->fetchCol();
return $village_ids;
}
/**
* Gets villages that have had bookings assigned with a similar postcode.
*
* Used in automatic allocation to find a village for a group so groups that
* are geographically close can be located together.
*
* @param int $event_id
* ID of event being validated.
* @param string $postcode
* Postal code.
*
* @return array
* Array of village IDs.
*/
public function getVillagesContainingBookingsWithSimilarPostcode($event_id, $postcode) {
$query = $this->getBaseQuery($event_id, FALSE, FALSE);
// Limit to those already allocated.
$query->isNotNull('v.village_target_id');
// Calculate similar postcode.
$partial_postcode = substr($postcode, 0, strpos($postcode, ' '));
$query->condition('g.allocation_sorting_code', $partial_postcode . '%', 'LIKE');
// Only need to bring back village ID.
$query->addField('v', 'village_target_id', 'village_id');
return $query->execute()->fetchCol();
}
/**
* Gets all bookings in a group that haveb't been assigned.
*
* @param int $event_id
* Event ID.
* @param int $group_id
* Group ID.
*
* @return \Drupal\commerce_order\Entity\Order[]
* Array of bookings
*
* @throws \Drupal\Component\Plugin\Exception\InvalidPluginDefinitionException
* @throws \Drupal\Component\Plugin\Exception\PluginNotFoundException
*/
public function getUnassignedBookingsInGroup($event_id, $group_id) {
$query = $this->getBaseQuery($event_id, TRUE, TRUE);
$query->condition('g.id', $group_id);
$query->addField('o', 'order_id');
$ids = $query->execute()->fetchCol();
return $this->entityTypeManager->getStorage('commerce_order')
->loadMultiple($ids);
}
/**
* Gets the booking IDs that contain village hosts for the event.
*
* @param int $event_id
* Event ID.
*
* @return array
* Array of order IDs.
*/
public function getBookingIdsWithVillageHosts($event_id) {
$query = $this->db->select('contacts_event__village_host_teams', 'ht');
$query->innerJoin('c_events_team_app', 'app', 'app.team = ht.village_host_teams_target_id');
$query->innerJoin('contacts_ticket', 't', 't.id = app.ticket');
$query->innerJoin('commerce_order_item', 'oi', 'oi.order_item_id = t.order_item');
$query->addField('oi', 'order_id');
$query->condition('app.state', 'approved');
$query->condition('ht.entity_id', $event_id);
return $query->execute()->fetchCol();
}
/**
* Checks that the operator is allowed.
*
* @param string $operator
* The operator to check.
*
* @throws \Exception
* Exception thrown if operator is invalid.
*
* @return string
* The operator input.
*/
private function checkOperatorAllowed($operator) {
if (!in_array($operator, ['=', '>', '<', '>=', '<=', '<>', 'IN'])) {
throw new \Exception('Invalid operator');
}
return $operator;
}
/**
* Counts bookings with missing group information for a specific event.
*
* Note that this does not include bookings who have selected "No Group" as
* their camping group (as this is a valid option) but rather counts those
* where group information is totally missing (NULL), which will need to be
* fixed manually.
*
* @param int $event_id
* ID of event.
*
* @return int
* Number of bookings with a null group.
*/
public function countBookingsWithoutGroup($event_id) : int {
$query = $this->getBaseQuery($event_id, FALSE, FALSE);
$query->isNull('og.village_group_target_id');
return $query->countQuery()->execute()->fetchField();
}
}
