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