public static function fetchByRoot(Connection $conn, $id, MembershipEntityInterface $root)
{
if ($root instanceof Area) {
$data = $conn->fetchAssoc('SELECT * FROM `' . EdkTables::MESSAGE_TBL . '` WHERE `id` = :id AND `areaId` = :rootId', [':id' => $id, ':rootId' => $root->getId()]);
} elseif ($root instanceof Group) {
$data = $conn->fetchAssoc('SELECT m.* FROM `' . EdkTables::MESSAGE_TBL . '` m ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON a.`id` = m.`areaId` ' . 'WHERE m.`id` = :id AND a.`groupId` = :rootId', [':id' => $id, ':rootId' => $root->getId()]);
} elseif ($root instanceof Project) {
$data = $conn->fetchAssoc('SELECT m.* FROM `' . EdkTables::MESSAGE_TBL . '` m ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON a.`id` = m.`areaId` ' . 'WHERE m.`id` = :id AND a.`projectId` = :rootId', [':id' => $id, ':rootId' => $root->getId()]);
}
if (false === $data) {
return false;
}
$item = self::fromArray($data);
if ($root instanceof Area) {
$item->area = $root;
} elseif ($root instanceof Group) {
$item->area = Area::fetchByGroup($conn, $data['areaId'], $root);
} elseif ($root instanceof Project) {
$item->area = Area::fetchByProject($conn, $data['areaId'], $root);
}
if (!empty($data['responderId'])) {
$item->responder = User::fetchByCriteria($conn, QueryClause::clause('u.id = :id', ':id', $data['responderId']));
}
return $item;
}
public function listData(DataTable $dataTable)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.name', 'name')->from(CoreTables::PROJECT_TBL, 'i')->where(QueryClause::clause('i.archived = 1'));
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildCountingCondition($qb->getWhere()))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchCell($this->conn);
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchAll($this->conn));
}
public function listData(DataTable $dataTable)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.name', 'name')->field('c.name', 'categoryName')->field('i.memberNum', 'memberNum')->field('i.areaNum', 'areaNum')->from(CoreTables::GROUP_TBL, 'i')->leftJoin(CoreTables::GROUP_CATEGORY_TBL, 'c', QueryClause::clause('c.id = i.categoryId'))->where(QueryClause::clause('i.projectId = :projectId', ':projectId', $this->project->getId()));
$countingQuery = QueryBuilder::select()->from(CoreTables::GROUP_TBL, 'i')->where(QueryClause::clause('i.projectId = :projectId', ':projectId', $this->project->getId()));
$recordsTotal = QueryBuilder::copyWithoutFields($countingQuery)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildCountingCondition($qb->getWhere()))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchCell($this->conn);
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchAll($this->conn));
}
public function listData(DataTable $dataTable)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.name', 'name')->from(CoreTables::GROUP_CATEGORY_TBL, 'i');
$where = QueryClause::clause('i.`projectId` = :projectId', ':projectId', $this->project->getId());
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildCountingCondition($where))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildFetchingCondition($where))->fetchCell($this->conn);
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($where))->fetchAll($this->conn));
}
public function getUserByEmail($email)
{
if (filter_var($email, FILTER_VALIDATE_EMAIL) === false) {
throw new ModelException('Invalid e-mail address');
}
$this->transaction->requestTransaction();
$user = User::fetchByCriteria($this->conn, QueryClause::clause('u.`email` = :email', ':email', $email));
if (empty($user)) {
throw new ItemNotFoundException('The specified user has not been found.');
}
return $user;
}
public function listData(DataTable $dataTable, TranslatorInterface $translator)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.name', 'name')->field('i.entityType', 'entityType')->field('i.displayOrder', 'displayOrder')->from(MilestoneTables::MILESTONE_TBL, 'i')->where(QueryClause::clause('i.`projectId` = :projectId', ':projectId', $this->project->getId()));
$qb->postprocess(function ($row) use($translator) {
$row['entityTypeText'] = $translator->trans($row['entityType']);
return $row;
});
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildCountingCondition($qb->getWhere()))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchCell($this->conn);
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchAll($this->conn));
}
public function listData(DataTable $dataTable)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.name', 'name')->field('i.createdAt', 'createdAt')->field('i.areasAllowed', 'areasAllowed')->field('i.areaRegistrationAllowed', 'areaRegistrationAllowed')->from(CoreTables::PROJECT_TBL, 'i')->where(QueryClause::clause('i.archived = 0'));
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildCountingCondition($qb->getWhere()))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchCell($this->conn);
$qb->postprocess(function (array $row) {
$row['createdAtFormatted'] = $this->timeFormatter->ago($row['createdAt']);
return $row;
});
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchAll($this->conn));
}
public function listData(DataTable $dataTable, TranslatorInterface $translator)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.name', 'name')->field('s.id', 'status')->field('s.name', 'statusName')->field('s.label', 'statusLabel')->field('t.name', 'territory')->field('i.memberNum', 'memberNum')->field('i.percentCompleteness', 'percentCompleteness')->from(CoreTables::AREA_TBL, 'i')->join(CoreTables::TERRITORY_TBL, 't', QueryClause::clause('i.territoryId = t.id'))->join(CoreTables::AREA_STATUS_TBL, 's', QueryClause::clause('i.statusId = s.id'))->where(QueryClause::clause('i.groupId = :groupId', ':groupId', $this->group->getId()));
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildCountingCondition($qb->getWhere()))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchCell($this->conn);
$qb->postprocess(function ($row) use($translator) {
$row['statusName'] = $translator->trans($row['statusName'], [], 'statuses');
$row['percentCompleteness'] .= '%';
return $row;
});
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchAll($this->conn));
}
public function listData(DataTable $dataTable)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.name', 'name')->field('i.locale', 'locale')->field('i.areaNum', 'areaNum')->field('i.requestNum', 'requestNum')->from(CoreTables::TERRITORY_TBL, 'i');
$where = QueryClause::clause('i.`projectId` = :projectId', ':projectId', $this->project->getId());
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildCountingCondition($where))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildFetchingCondition($where))->fetchCell($this->conn);
$qb->postprocess(function ($row) {
$row['removable'] = $row['areaNum'] == 0 && $row['requestNum'] == 0;
return $row;
});
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($where))->fetchAll($this->conn));
}
public function listData(DataTable $dataTable)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.name', 'name')->field('i.registeredAt', 'registeredAt')->field('i.projectNum', 'projectNum')->field('i.groupNum', 'groupNum')->field('i.areaNum', 'areaNum')->field('i.active', 'active')->field('i.admin', 'admin')->from(CoreTables::USER_TBL, 'i');
$where = QueryClause::clause('i.removed = 0');
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildCountingCondition($where))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildFetchingCondition($where))->fetchCell($this->conn);
$qb->postprocess(function (array $row) {
$row['registeredAtFormatted'] = $this->timeFormatter->ago($row['registeredAt']);
return $row;
});
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($where))->fetchAll($this->conn));
}
public function listData(DataTable $dataTable)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.place', 'place')->field('i.title', 'title')->field('i.locale', 'locale')->from(CoreTables::APP_TEXT_TBL, 'i');
if (null === $this->project) {
$where = QueryClause::clause('i.`projectId` IS NULL');
} else {
$where = QueryClause::clause('i.`projectId` = :projectId', ':projectId', $this->project->getId());
}
$qb->where($where);
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildCountingCondition($qb->getWhere()))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchCell($this->conn);
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchAll($this->conn));
}
public static function fetchByProject(Connection $conn, $id, Project $project)
{
$data = $conn->fetchAssoc('SELECT r.*, v.id AS `verifier_id`, v.`name` AS `verifier_name`, ' . 't.`id` AS `territory_id`, t.`name` AS `territory_name`, t.`areaNum` AS `territory_areaNum`, t.`requestNum` as `territory_requestNum` ' . 'FROM `' . CoreTables::AREA_REQUEST_TBL . '` r ' . 'INNER JOIN `' . CoreTables::TERRITORY_TBL . '` t ON t.`id` = r.`territoryId` ' . 'LEFT JOIN `' . CoreTables::USER_TBL . '` v ON v.`id` = r.`verifierId` ' . 'WHERE r.`id` = :id AND r.`projectId` = :projectId', [':id' => $id, ':projectId' => $project->getId()]);
if (null === $data) {
return false;
}
$user = User::fetchByCriteria($conn, QueryClause::clause('u.`id` = :id', ':id', $data['requestorId']));
$item = self::fromArray($data);
$item->setProject($project);
$item->setRequestor($user);
if (!empty($data['verifier_id'])) {
$item->verifier = new Verifier($data['verifier_id'], $data['verifier_name']);
}
$item->setTerritory($item->oldTerritory = Territory::fromArray($data, 'territory'));
return $item;
}
public function listData(DataTable $dataTable, TranslatorInterface $translator)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.name', 'name')->field('i.presentedTo', 'presentedTo')->field('i.listOrder', 'order')->from(LinksTables::LINK_TBL, 'i');
if (null === $this->project) {
$where = QueryClause::clause('i.`projectId` IS NULL');
} else {
$where = QueryClause::clause('i.`projectId` = :projectId', ':projectId', $this->project->getId());
}
$qb->postprocess(function ($row) use($translator) {
$row['presentedToText'] = $translator->trans(Link::presentedToText($row['presentedTo']));
return $row;
});
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildCountingCondition($where))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildFetchingCondition($where))->fetchCell($this->conn);
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($where))->fetchAll($this->conn));
}
public function listData(DataTable $dataTable)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.name', 'name')->field('r.mandatoryCourseNum', 'mandatoryCourseNum')->field('r.passedCourseNum', 'passedCourseNum')->field('r.failedCourseNum', 'failedCourseNum')->from(CoreTables::AREA_TBL, 'i')->join(CourseTables::COURSE_PROGRESS_TBL, 'r', QueryClause::clause('r.areaId = i.id'))->leftJoin(CoreTables::GROUP_TBL, 'g', QueryClause::clause('g.id = i.groupId'));
if (null !== $this->group) {
$qb->where(QueryClause::clause('i.`groupId` = :groupId', ':groupId', $this->group->getId()));
} else {
$qb->where(QueryClause::clause('i.`projectId` = :projectId', ':projectId', $this->project->getId()));
}
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildCountingCondition($qb->getWhere()))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchCell($this->conn);
$qb->postprocess(function ($row) {
if ($row['mandatoryCourseNum'] == 0) {
$row['progress'] = 0;
} else {
$row['progress'] = $row['passedCourseNum'] / $row['mandatoryCourseNum'] * 100;
}
return $row;
});
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchAll($this->conn));
}
public function findUserByNameMail($username, $email)
{
return User::fetchByCriteria($this->conn, QueryOperator::op('AND')->expr(QueryClause::clause('u.`login` = :login', ':login', $username))->expr(QueryClause::clause('u.`email` = :email', ':email', $email)));
}
/**
* @param $membershipEntity Entity whose members we want to view
* @param $id User ID
* @return User
*/
public function getItem(MembershipEntityInterface $membershipEntity, $id)
{
$this->transaction->requestTransaction();
try {
$user = User::fetchLinkedProfile($this->conn, $this->roleResolver, $membershipEntity, Join::create($this->membershipTable(), 'm', QueryClause::clause('m.userId = u.id')), QueryOperator::op('AND')->expr(QueryClause::clause('m.' . $this->entityColumn() . ' = :entityId', ':entityId', $membershipEntity->getId()))->expr(QueryClause::clause('u.`id` = :userId', ':userId', $id)));
if (false === $user) {
throw new ItemNotFoundException('The specified user has not been found.');
}
return $user;
} catch (Exception $exception) {
$this->transaction->requestRollback();
throw $exception;
}
}
public function listData(DataTable $dataTable, TranslatorInterface $translator)
{
$qb = QueryBuilder::select()->field('i.id', 'id');
if (!$this->root instanceof Area) {
$qb->field('a.id', 'areaId');
$qb->field('a.name', 'areaName');
$qb->join(CoreTables::AREA_TBL, 'a', QueryClause::clause('a.id = i.areaId'));
}
$qb->field('i.name', 'name')->field('r.registrationType', 'registrationType')->field('r.startTime', 'startTime')->field('r.endTime', 'endTime')->field('r.participantLimit', 'participantLimit')->field('r.participantNum', 'participantNum')->from(EdkTables::ROUTE_TBL, 'i')->leftJoin(EdkTables::REGISTRATION_SETTINGS_TBL, 'r', QueryClause::clause('r.routeId = i.id'))->where(QueryClause::clause('i.approved = 1'));
if ($this->root instanceof Area) {
$qb->where(QueryClause::clause('i.`areaId` = :areaId', ':areaId', $this->root->getId()));
} elseif ($this->root instanceof Group) {
$qb->where(QueryClause::clause('a.`groupId` = :groupId', ':groupId', $this->root->getId()));
} elseif ($this->root instanceof Project) {
$qb->where(QueryClause::clause('a.`projectId` = :projectId', ':projectId', $this->root->getId()));
}
$qb->postprocess(function ($row) use($translator) {
if (empty($row['startTime'])) {
$row['startTime'] = '--';
} else {
$row['startTime'] = $this->timeFormatter->format(TimeFormatterInterface::FORMAT_DATE_SHORT, $row['startTime']);
}
if (empty($row['endTime'])) {
$row['endTime'] = '--';
} else {
$row['endTime'] = $this->timeFormatter->format(TimeFormatterInterface::FORMAT_DATE_SHORT, $row['endTime']);
}
$row['registrationTypeText'] = $translator->trans(EdkRegistrationSettings::registrationTypeText($row['registrationType']), [], 'edk');
return $row;
});
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildCountingCondition($qb->getWhere()))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchCell($this->conn);
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchAll($this->conn));
}
private function buildSearchClause()
{
$bIdx = 0;
$op = QueryOperator::op(' OR ');
if (!empty($this->searchString)) {
foreach ($this->columns as $column) {
if ($column['type'] == self::TYPE_SEARCHABLE) {
$op->expr(QueryClause::clause($column['db'] . ' LIKE :bIdx' . $bIdx, ':bIdx' . $bIdx, '%' . $this->searchString . '%'));
$bIdx++;
}
}
}
if ($bIdx > 0) {
return $op;
}
return null;
}
public function listData(DataTable $dataTable, TranslatorInterface $translator)
{
$qb = QueryBuilder::select()->field('i.id', 'id');
if (!$this->root instanceof Area) {
$qb->field('a.id', 'areaId');
$qb->field('a.name', 'areaName');
$qb->join(CoreTables::AREA_TBL, 'a', QueryClause::clause('a.id = i.areaId'));
}
$qb->field('i.subject', 'subject')->field('i.createdAt', 'createdAt')->field('i.status', 'status')->field('u.id', 'responderId')->field('u.name', 'responder')->field('i.duplicate', 'duplicate')->from(EdkTables::MESSAGE_TBL, 'i')->leftJoin(CoreTables::USER_TBL, 'u', QueryClause::clause('u.id = i.responderId'))->orderBy('i.status', 'ASC')->orderBy('i.createdAt', 'ASC');
if ($this->root instanceof Area) {
$qb->where(QueryClause::clause('i.`areaId` = :areaId', ':areaId', $this->root->getId()));
} elseif ($this->root instanceof Group) {
$qb->where(QueryClause::clause('a.`groupId` = :groupId', ':groupId', $this->root->getId()));
} elseif ($this->root instanceof Project) {
$qb->where(QueryClause::clause('a.`projectId` = :projectId', ':projectId', $this->root->getId()));
}
$qb->postprocess(function ($row) use($translator) {
$row['statusText'] = $translator->trans(EdkMessage::statusText($row['status']), [], 'edk');
$row['statusLabel'] = EdkMessage::statusLabel($row['status']);
$row['createdAt'] = $this->timeFormatter->ago($row['createdAt']);
return $row;
});
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildCountingCondition($qb->getWhere()))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchCell($this->conn);
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchAll($this->conn));
}
public static function fetchLinkedProfile(Connection $conn, MembershipRoleResolver $roleResolver, IdentifiableInterface $item, Join $join, QueryElement $element)
{
$qb = QueryBuilder::select()->field('u.*')->field('p.*')->field('m.role AS `membership_role`')->field('m.note AS `membership_note`')->field('l.`id`', 'language_id')->field('l.`name`', 'language_name')->field('l.`locale`', 'language_locale')->from(CoreTables::USER_TBL, 'u')->join(CoreTables::USER_PROFILE_TBL, 'p', QueryClause::clause('p.`userId` = u.`id`'))->join(CoreTables::LANGUAGE_TBL, 'l', QueryClause::clause('l.`id` = p.`settingsLanguageId`'))->join($join)->where(QueryOperator::op('AND')->expr(QueryClause::clause('u.`active` = 1 AND u.`removed` = 0'))->expr($element));
$data = $qb->fetchAssoc($conn);
if (false === $data) {
return false;
}
$user = User::fromArray($data);
$membership = new Membership($item, $roleResolver->getRole(get_class($item), $data['membership_role']), $data['membership_note']);
User::installMembershipInformation($user, $membership);
return $user;
}
public function findMember(Connection $conn, MembershipRoleResolver $resolver, $id)
{
return User::fetchLinkedProfile($conn, $resolver, $this, Join::create(CoreTables::AREA_MEMBER_TBL, 'm', QueryClause::clause('m.`userId` = u.`id`')), QueryClause::clause('u.`id` = :id', ':id', $id));
}
public function listData(TranslatorInterface $trans, DataTable $dataTable)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.name', 'name')->field('t.name', 'territory')->field('u.name', 'reportedBy')->field('i.status', 'status')->field('i.commentNum', 'commentNum')->from(CoreTables::AREA_REQUEST_TBL, 'i')->join(CoreTables::USER_TBL, 'u', QueryClause::clause('u.`id` = i.`requestorId`'))->join(CoreTables::TERRITORY_TBL, 't', QueryClause::clause('t.`id` = i.`territoryId`'))->where(QueryClause::clause('i.projectId = :projectId', ':projectId', $this->project->getId()));
$countingQuery = QueryBuilder::select()->from(CoreTables::AREA_REQUEST_TBL, 'i')->where(QueryClause::clause('i.projectId = :projectId', ':projectId', $this->project->getId()));
$recordsTotal = QueryBuilder::copyWithoutFields($countingQuery)->field('COUNT(id)', 'cnt')->where($dataTable->buildCountingCondition($qb->getWhere()))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($countingQuery)->field('COUNT(id)', 'cnt')->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchCell($this->conn);
$qb->postprocess(function ($row) use($trans) {
$row['statusLabel'] = AreaRequest::statusLabel($row['status']);
$row['statusText'] = $trans->trans(AreaRequest::statusText($row['status']), [], 'statuses');
$row['removable'] = $row['status'] == 0 || $row['status'] == 1;
return $row;
});
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchAll($this->conn));
}
public function listData(DataTable $dataTable)
{
$qb = QueryBuilder::select()->field('i.id', 'id');
if (!$this->root instanceof Area) {
$qb->field('a.id', 'areaId');
$qb->field('a.name', 'areaName');
$qb->join(CoreTables::AREA_TBL, 'a', QueryClause::clause('a.id = i.areaId'));
}
$qb->field('i.name', 'name')->field('i.routeFrom', 'routeFrom')->field('i.routeTo', 'routeTo')->field('i.routeLength', 'routeLength')->field('i.updatedAt', 'updatedAt')->field('i.approved', 'approved')->field('i.commentNum', 'commentNum')->from(EdkTables::ROUTE_TBL, 'i');
if ($this->root instanceof Area) {
$qb->where(QueryClause::clause('i.`areaId` = :areaId', ':areaId', $this->root->getId()));
} elseif ($this->root instanceof Group) {
$qb->where(QueryClause::clause('a.`groupId` = :groupId', ':groupId', $this->root->getId()));
} elseif ($this->root instanceof Project) {
$qb->where(QueryClause::clause('a.`projectId` = :projectId', ':projectId', $this->root->getId()));
}
$qb->postprocess(function ($row) {
$row['routeLength'] .= ' km';
$row['updatedAtText'] = $this->timeFormatter->ago($row['updatedAt']);
$row['removable'] = !$row['approved'];
return $row;
});
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildCountingCondition($qb->getWhere()))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchCell($this->conn);
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchAll($this->conn));
}
public function insert(Connection $conn)
{
$this->createdAt = time();
$this->assignmentKey = strtoupper(hash('sha256', uniqid(time() . 'LWDXFDF' . $_SERVER['REMOTE_ADDR'] . rand(-2000000, 2000000) . 'djdjDfjashaXms')));
$checkUser = User::fetchByCriteria($conn, QueryClause::clause('u.`email` = :email', ':email', $this->email));
if (false === $checkUser) {
$this->id = $conn->insert(CoreTables::INVITATION_TBL, ['email' => $this->email, 'role' => $this->role, 'note' => $this->note, 'resourceType' => $this->resourceType, 'resourceName' => $this->resourceName, 'resourceId' => $this->resourceId, 'inviterId' => $this->inviter->getId(), 'createdAt' => $this->createdAt, 'assignmentKey' => $this->assignmentKey]);
} else {
$this->user = $checkUser;
$this->id = $conn->insert(CoreTables::INVITATION_TBL, ['email' => $this->email, 'userId' => $this->user->getId(), 'role' => $this->role, 'note' => $this->note, 'resourceType' => $this->resourceType, 'resourceName' => $this->resourceName, 'resourceId' => $this->resourceId, 'inviterId' => $this->inviter->getId(), 'createdAt' => $this->createdAt, 'assignmentKey' => $this->assignmentKey]);
}
return $this->id;
}
public function createFilterClause()
{
$op = QueryOperator::op(' AND ');
if (null !== $this->status) {
$op->expr(QueryClause::clause('i.statusId = :statusId', ':statusId', $this->status->getId()));
}
if (null !== $this->group) {
$op->expr(QueryClause::clause('i.groupId = :groupId', ':groupId', $this->group->getId()));
}
if (null !== $this->category) {
$op->expr(QueryClause::clause('g.categoryId = :categoryId', ':categoryId', $this->category->getId()));
}
if (null !== $this->territory) {
$op->expr(QueryClause::clause('i.territoryId = :territoryId', ':territoryId', $this->territory->getId()));
}
return $op;
}
public function listData(DataTable $dataTable, TranslatorInterface $translator)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.firstName', 'firstName')->field('i.lastName', 'lastName')->field('r.id', 'routeId')->field('r.name', 'routeName')->field('i.createdAt', 'createdAt')->field('i.age', 'age')->field('i.sex', 'sex')->from(EdkTables::PARTICIPANT_TBL, 'i')->join(EdkTables::ROUTE_TBL, 'r', QueryClause::clause('i.routeId = r.id'))->where(QueryClause::clause('i.`areaId` = :areaId', ':areaId', $this->area->getId()))->orderBy('i.createdAt', 'DESC');
$qb->postprocess(function ($row) use($translator) {
$row['sexText'] = $row['sex'] == 1 ? $translator->trans('SexMale', [], 'edk') : $translator->trans('SexFemale', [], 'edk');
$row['createdAt'] = $this->timeFormatter->ago($row['createdAt']);
return $row;
});
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildCountingCondition($qb->getWhere()))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchCell($this->conn);
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchAll($this->conn));
}
