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.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, 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');
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 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));
}
public function getRecentlyChangedRoutes($count)
{
$this->transaction->requestTransaction();
try {
$items = $this->conn->fetchAll('SELECT r.`id`, r.`name`, a.`name` AS `areaName`, r.`updatedAt` ' . 'FROM `' . EdkTables::ROUTE_TBL . '` r ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON r.`areaId` = a.`id` ' . $this->createWhereClause() . 'ORDER BY r.`updatedAt` DESC LIMIT ' . $count, [':itemId' => $this->root->getId()]);
foreach ($items as &$item) {
$item['updatedAt'] = $this->timeFormatter->ago($item['updatedAt']);
}
return $items;
} catch (Exception $ex) {
$this->transaction->requestRollback();
throw $ex;
}
}
public function getFeedback(Connection $conn, TimeFormatterInterface $timeFormatter)
{
$stmt = $conn->prepare('SELECT m.`createdAt`, m.`message`, u.`id` AS `user_id`, u.`name`, u.`avatar` FROM `' . CoreTables::AREA_REQUEST_COMMENT_TBL . '` m ' . 'INNER JOIN `' . CoreTables::USER_TBL . '` u ON u.`id` = m.`userId` ' . 'WHERE m.`requestId` = :id ORDER BY m.`id`');
$stmt->bindValue(':id', $this->id);
$stmt->execute();
$result = [];
$direction = 1;
$previousUser = null;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
if ($previousUser != $row['user_id']) {
$direction = $direction == 1 ? 0 : 1;
}
$result[] = ['message' => $row['message'], 'time' => $timeFormatter->ago($row['createdAt']), 'author' => $row['name'], 'avatar' => $row['avatar'], 'dir' => $direction];
$previousUser = $row['user_id'];
}
$stmt->closeCursor();
return $result;
}
public static function processStatus(array $row, TimeFormatterInterface $timeFormatter, $editable)
{
if (!$editable) {
$row['actions'] = ['view'];
} else {
if ($row['type'] == Milestone::TYPE_BINARY) {
$row['actions'] = [$row['progress'] == 100 ? 'cancel' : 'complete', 'view'];
} else {
$row['actions'] = ['update', 'view'];
}
}
if (!empty($row['deadline'])) {
$row['deadline'] = $timeFormatter->format(TimeFormatterInterface::FORMAT_DATE_LONG, $row['deadline']);
} else {
$row['deadline'] = '---';
}
if (!empty($row['completedAt'])) {
$row['completedAt'] = $timeFormatter->format(TimeFormatterInterface::FORMAT_DATE_LONG, $row['completedAt']);
} else {
$row['completedAt'] = '---';
}
return $row;
}
public function ago($utcTimestamp)
{
return $this->timeFormatter->ago($utcTimestamp);
}
