public function listData(DataTable $dataTable)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.place', 'place')->field('i.subject', 'subject')->field('i.locale', 'locale')->from(CoreTables::MAIL_TBL, 'i');
$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')->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 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)
{
$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.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, 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.login', 'login')->field('i.requestTime', 'requestTime')->field('i.requestIp', 'requestIp')->from(CoreTables::USER_REGISTRATION_TBL, 'i');
$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['requestTimeFormatted'] = $this->timeFormatter->ago($row['requestTime']);
$row['requestIpFormatted'] = long2ip($row['requestIp']);
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.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 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, 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 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 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')->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 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));
}
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));
}
/**
* Adds the <tt>ORDER BY</tt> and <tt>LIMIT</tt> clauses to the given SQL query, and returns
* that query for convenience.
*
* @param \Cantiga\Metamodel\QueryBuilder $qb
* @return \Cantiga\Metamodel\QueryBuilder
*/
public function processQuery(QueryBuilder $qb)
{
$qb->limit($this->length, $this->start);
if (sizeof($this->orders) > 0) {
$qb->resetOrders();
foreach ($this->orders as $order) {
$qb->orderBy($order['dbName'], $order['direction']);
}
}
return $qb;
}
