public static function savePassword(\Rebond\Core\User\Model $user)
{
Util\Log::log(Util\Error::PASSWORD_CHANGE, $user->getId(), __FILE__, __LINE__);
$db = new Util\Data();
$query = 'UPDATE core_user SET password = ?, modified_date = ? WHERE id = ?';
return $db->execute($query, [$user->getPassword(), Util\Format::date(time(), 'sqlDatetime'), $user->getId()]);
}
public static function export(\Rebond\Core\ModelInterface $module)
{
$exportPath = \Rebond\Config::path('export');
$tempPath = \Rebond\Config::path('temp');
// TODO add XML model node
// generate data script
$dataScriptPath = $tempPath . 'data.sql';
$table = 'app_' . strtolower($module->getTitle());
$db = new Util\Data();
$result = $db->select('SELECT * FROM ' . $table);
$script = $db->backupData($table, $result);
$result = $db->select('SELECT * FROM cms_content WHERE module_id = ?', [$module->getId()]);
$script .= $db->backupData('cms_content', $result);
File::save($dataScriptPath, 'w', $script);
// create zip
$zipFile = $module->getTitle() . '.zip';
$zip = new \ZipArchive();
$res = $zip->open($exportPath . $zipFile, \ZIPARCHIVE::OVERWRITE);
if (!$res) {
return $res;
}
$modulePath = FULL_PATH . 'Rebond/App/' . $module->getTitle() . '/';
$iterator = new \RecursiveIteratorIterator(new \RecursiveDirectoryIterator($modulePath));
foreach ($iterator as $file) {
$filename = str_replace($modulePath, '', str_replace('\\', '/', $file));
if (file_exists($file) && substr($filename, -1) != '.') {
$zip->addFile($file, $filename);
}
}
$zip->addFile($dataScriptPath, 'data.sql');
$zip->close();
return $zipFile;
}
public static function loadByTitle($title)
{
$db = new Util\Data();
$db->buildQuery('select', self::getList());
$db->buildQuery('from', 'cms_module module');
$db->buildQuery('where', ['module.title = ?', $title]);
return self::map($db);
}
private static function loadFriendlyUrl($pageId)
{
$db = new Util\Data();
$query = 'SELECT p.id, p.friendly_url, p.friendly_url_path
FROM cms_page p
WHERE p.id = ?';
return $db->execute($query, [$pageId]);
}
/**
* Delete Book by sequence
* @param int $sequence
* @return int
*/
public static function deleteBySequence($sequence)
{
if ($sequence == 0) {
return 0;
}
$db = new Util\Data();
$query = 'DELETE FROM bus_book WHERE sequence = ? AND booking_date > NOW()';
return $db->execute($query, [$sequence]);
}
public static function deleteSecure($userId, $type = null)
{
$db = new Util\Data();
if (isset($type)) {
$query = 'DELETE FROM core_user_security WHERE user_id = ? AND type = ?';
return $db->execute($query, [$userId, $type]);
}
$query = 'DELETE FROM core_user_security WHERE user_id = ?';
return $db->execute($query, [$userId]);
}
public static function clear($code)
{
$db = new Util\Data();
if ($code != 0) {
$query = 'DELETE FROM core_log WHERE code = ?';
return $db->execute($query, [$code]);
}
$query = 'DELETE FROM core_log ORDER BY id DESC LIMIT 200';
return $db->execute($query);
}
public static function fillCPU($tournament)
{
$need = $tournament->getSize() - count($tournament->getTournamentPlayers());
$db = new Util\Data();
if ($need > 0) {
$sqlBusyPlayer = 'SELECT DISTINCT tp.player_id
FROM bus_tournament_player tp
JOIN bus_tournament t ON t.id = tp.tournament_id
WHERE t.status < 3';
$rows = $db->select($sqlBusyPlayer);
$busyPlayers = [0];
if (count($rows) > 0) {
$list = $rows->fetchAll(\PDO::FETCH_COLUMN);
foreach ($list as $key => $value) {
$busyPlayers[] = $value;
}
}
$sql = 'SELECT DISTINCT p.id
FROM bus_player p
LEFT JOIN bus_tournament_player tp ON tp.player_id = p.id
WHERE p.active = 1
AND p.user_id = 0
AND (tp.player_id IS NULL OR tp.player_id NOT IN (?))
ORDER BY p.tour_ranking';
$players = $db->select($sql, [implode(',', $busyPlayers)]);
if (count($players) == 0) {
Util\Log::log(Util\Code::CRON, 'Not enough CPU, tournamentId: ' . $tournament->getId() . ', 0 / ' . $need, __FILE__, __LINE__);
return false;
}
$players = $players->fetchAll(\PDO::FETCH_ASSOC);
if (count($players) < $need) {
Util\Log::log(Util\Code::CRON, 'Not enough CPU, tournamentId: ' . $tournament->getId() . ', ' . count($players) . ' / ' . $need, __FILE__, __LINE__);
return false;
}
$playerIds = Engine::findCPU($players, $need, $tournament->getClassification());
if (count($playerIds) < $need) {
Util\Log::log(Util\Code::CRON, 'Not enough CPU found, tournamentId: ' . $tournament->getId() . ', ' . count($playerIds) . ' / ' . $need, __FILE__, __LINE__);
return false;
}
$options = [];
$options['clearSelect'] = true;
$options['select'][] = \Own\Bus\Player\Data::getList(['id']);
$options['where'][] = 'player.id IN (' . implode(',', $playerIds) . ')';
$players = \Own\Bus\Player\Data::loadAll($options);
foreach ($players as $player) {
$tp = new \Own\Bus\TournamentPlayer\Model();
$tp->setTournamentId($tournament->getId());
$tp->setPlayerId($player->getId());
$tp->save();
}
}
return true;
}
public static function updateIsInMatchPlayers($tournament)
{
$db = new Util\Data();
$playerIds = [];
foreach ($tournament->getTournamentPlayers() as $tp) {
$playerIds[] = $tp->getPlayerId();
}
if (count($playerIds) == 0) {
return;
}
$sql = 'UPDATE bus_player SET is_in_match = true, is_in_tournament = true WHERE id IN (' . implode(',', $playerIds) . ')';
$db->execute($sql);
}
public function validateTitle()
{
$v = Util\Validate::validate('title', $this->getModel()->getTitle(), $this->titleValidator);
if ($v->getResult() == ResultType::ERROR) {
return $v;
}
$db = new Util\Data();
$options = [];
$options['where'][] = ['folder.title = ?', $this->getModel()->getTitle()];
$options['where'][] = ['folder.folderId != ?', $this->getModel()->getId()];
$exist = $db->count($options);
if ($exist >= 1) {
$v->setResult(ResultType::ERROR);
$v->setMessage('a folder already exists with this name');
}
return $v;
}
public static function loadFullComponents($options = [])
{
$db = new Util\Data();
$list = 'component.id AS componentId,
component.module_id AS componentModuleId,
CONCAT(module.title, \' - \', component.title) AS componentTitle';
$db->buildQuery('select', $list);
$db->buildQuery('from', 'cms_component component');
$db->buildQuery('join', 'cms_module module ON module.id = component.module_id');
$db->buildQuery('where', 'component.status = 1');
$db->buildQuery('where', 'module.status = 1');
$db->buildQuery('order', 'module.title, component.title');
$db->extendQuery($options);
return self::mapList($db);
}
public static function createForAll($title, $info = null)
{
$db = new Util\Data();
$options = [];
$options['clearSelect'] = true;
$options['select'][] = \Own\Bus\Player\Data::getList(['id', 'race_ranking', 'race_point']);
$options['where'][] = 'user_id != 0';
$players = \Own\Bus\Player\Data::loadAll($options);
foreach ($players as $player) {
$info = $title == 'endRanking' ? [['endRanking', $player->getRaceRanking(), $player->getRacePoint()]] : $info;
$sql = 'SELECT id FROM bus_notification WHERE player_id = ? AND match_id = 0 AND title = ?';
$exists = $db->selectOne($sql, [$player->getId(), $title]);
if (isset($exists)) {
if ($title == 'endRanking') {
continue;
}
break;
}
self::create($player->getId(), 0, $title, $info);
}
}
public static function loadByUserId($userId, $options = [])
{
$db = new Util\Data();
$db->buildQuery('select', self::getList());
$db->buildQuery('from', 'core_role r');
$db->buildQuery('join', 'core_user_role ur ON ur.role_id = r.id');
$db->buildQuery('where', 'ur.user_id = ' . $userId);
$db->extendQuery($options);
return self::mapList($db);
}
public static function loadPartners($playerId)
{
$db = new Util\Data();
$sql = 'SELECT player.id, player.level, user.firstname, user.lastname, GROUP_CONCAT(mc.court_id) as courts, 0 as play
FROM bus_player player
JOIN core_user user ON user.id = player.id
JOIN bus_player_membership pm ON pm.player_id = player.id
JOIN bus_membership membership ON membership.id = pm.membership_id
JOIN bus_membership_court mc ON mc.membership_id = membership.id
WHERE membership.start_date <= CURDATE()
AND membership.end_date >= CURDATE()
AND user.status = 1
AND player.id != ?
GROUP BY player.id, player.level, user.firstname, user.lastname';
$players = $db->select($sql, [$playerId]);
$players = array_column($players->fetchAll(\PDO::FETCH_ASSOC), null, 'id');
$sql = 'SELECT IF(player1_id = ?, player2_id, player1_id) AS id, SUM(1) total
FROM bus_book
WHERE (player1_id = ? OR player2_id = ?)
AND booking_date > NOW() - INTERVAL 6 MONTH
GROUP BY IF(player1_id = ?, player2_id, player1_id)
ORDER BY total DESC
LIMIT 10';
$commonPlayers = $db->select($sql, [$playerId, $playerId, $playerId, $playerId]);
while ($row = $commonPlayers->fetch(\PDO::FETCH_ASSOC)) {
if (array_key_exists($row['id'], $players)) {
$players[$row['id']]['play'] = (int) $row['total'];
}
}
usort($players, function ($a, $b) {
if ($a['play'] == $b['play']) {
return $a['firstname'] > $b['firstname'];
}
return $a['play'] < $b['play'];
});
return $players;
}
public static function updatePlayerPoints($playerIds, $createdDate)
{
// tour
$db = new Util\Data();
$sql = 'SELECT player_id, SUM(tp.points) as points, COUNT(player_id) as total
FROM bus_tournament_player tp
JOIN bus_tournament t ON t.id = tp.tournament_id
WHERE t.start_date > NOW() - INTERVAL ' . Engine::DAY * 336 . ' HOUR
AND tp.points > 0
AND player_id IN (' . implode(',', $playerIds) . ')
GROUP BY player_id
ORDER BY points DESC';
$result = $db->select($sql);
if (count($result) == 0) {
return;
}
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
if ($row['total'] <= 18) {
\Own\Bus\Player\Data::updateTourPointsByPlayerId($row['player_id'], $row['points']);
continue;
}
$sql = 'SELECT SUM(points) as points
FROM (
SELECT points
FROM bus_tournament_player tp
JOIN bus_tournament t ON t.id = tp.tournament_id
WHERE player_id = ?
AND t.start_date > NOW() - INTERVAL ? HOUR
ORDER BY points DESC
LIMIT 18
) AS subquery';
$subRow = $db->selectOne($sql, [$row['player_id'], Engine::DAY * 336]);
if (isset($subRow)) {
\Own\Bus\Player\Data::updateTourPointsByPlayerId($row['player_id'], $subRow['points']);
}
}
// race
$startYearDate = \Own\Bus\Engine::getStartYearDate($createdDate);
$sql = 'SELECT player_id, SUM(tp.points) as points, COUNT(player_id) as total
FROM bus_tournament_player tp
JOIN bus_tournament t ON t.id = tp.tournament_id
WHERE t.start_date > \'' . $startYearDate . '\'
AND tp.points > 0
AND player_id IN (' . implode(',', $playerIds) . ')
GROUP BY player_id
ORDER BY points DESC';
$result = $db->execute($sql);
if (count($result) == 0) {
return;
}
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
if ($row['total'] <= 18) {
\Own\Bus\Player\Data::updateRacePointsByPlayerId($row['player_id'], $row['points']);
continue;
}
$sql = 'SELECT SUM(points) as points
FROM (
SELECT points
FROM bus_tournament_player tp
JOIN bus_tournament t ON t.id = tp.tournament_id
WHERE player_id = ? AND t.start_date > ?
ORDER BY points DESC
LIMIT 18
) AS subquery';
$subRow = $db->selectOne($sql, [$row['player_id'], $startYearDate]);
if (isset($subRow)) {
\Own\Bus\Player\Data::updateRacePointsByPlayerId($row['player_id'], $subRow['points']);
}
}
}
public static function cleanHasViewed()
{
$query = 'UPDATE bus_player_match SET has_viewed = 1 WHERE has_viewed = 0 AND modified_date < NOW() - INTERVAL ? HOUR';
$db = new Util\Data();
return $db->execute($query, [\Own\Bus\Engine::DAY * 7]);
}
public static function updateGameplayBatch()
{
$db = new Util\Data();
$query = 'UPDATE bus_player SET
gameplay = gameplay + ROUND((300 + (CAST(adaptation AS SIGNED) * 50) - CAST(gameplay AS SIGNED)) / 100),
last_gameplay_update = NOW()
WHERE active = 1
AND last_gameplay_update < NOW() - INTERVAL ' . Engine::DAY * 60 / 10 . ' MINUTE';
return $db->execute($query);
}
public static function updateSurfaceBatch()
{
$db = new Util\Data();
$query = 'UPDATE bus_league SET surface = FLOOR(RAND() * 3) + 1';
return $db->execute($query);
}
private function addBookings(array $bookings)
{
$sql = "INSERT INTO bus_book (sequence, court_id, type, player1_id, player2_id, booking_date, title, color, guest, pay) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 0)";
$count = 0;
$bookingsCount = count($bookings);
$db = new Util\Data();
for ($t = 0; $t < $bookingsCount; $t++) {
$time = explode(':', $bookings[$t][0]['time']);
$bookingDate = $this->date;
$bookingDate->setTime($time[0], $time[1], 0);
$bookingDate = $bookingDate->format('Y-m-d H:i:s');
for ($c = 1; $c < count($bookings[$t]); $c++) {
if (isset($bookings[$t][$c]['players'])) {
$playerIds = $bookings[$t][$c]['players'];
$type = BookingType::PARTNER;
$playerIds[1] = $playerIds[1] != 0 ? $playerIds[1] : 1;
if ($playerIds[1] == 0) {
// guest
$type = BookingType::GUEST;
}
$db->execute($sql, ['', $bookings[$t][$c]['court'], $type, $playerIds[0], $playerIds[1], $bookingDate, '', '', '']);
$count++;
} else {
if (isset($bookings[$t][$c]['text'])) {
$text = $bookings[$t][$c]['text'];
if (strcasecmp($text, 'ledig') != 0 && $text != '') {
$color = $this->renderColor($text);
$db->execute($sql, [uniqid(), $bookings[$t][$c]['court'], BookingType::ADMIN, 0, 0, $bookingDate, $text, $color, '']);
$count++;
}
} else {
echo 'nope';
exit;
// should not happen
}
}
}
}
return $count . ' bookings added. (' . $bookingsCount * (count($bookings[0]) - 1) . ')';
}
public static function removeAndAddPlayer()
{
$options = [];
$options['clearSelect'] = true;
$options['select'][] = \Own\Bus\Player\Data::getList(['id', 'username']);
$options['where'][] = 'player.user_id = 0';
$options['where'][] = 'player.active = 1';
$players = \Own\Bus\Player\Data::loadAll($options);
if (count($players) > 0) {
$db = new Util\Data();
$pick = \Own\Bus\Engine::dice(0, count($players) - 1);
$remove = 'UPDATE bus_player SET active = 0 WHERE id = ' . $players[$pick]->getId();
$db->execute($remove);
Util\Log::log(Util\Code::CRON, 'player retired: ' . $players[$pick]->getUsername() . ' (' . $players[$pick]->getId() . ')', __FILE__, __LINE__);
$leagues = \Own\Bus\League\Data::loadAll();
$player = new \Own\Bus\Player\Model();
$player->setRandom(1);
$player->setActive(true);
if (count($leagues) > 0) {
$player->setLeagueId(\Own\Bus\Engine::findLeague($leagues, 1));
}
$player->save();
Util\Log::log(Util\Code::CRON, 'new player: ' . $player->getUsername() . ' (' . $player->getId() . ')', __FILE__, __LINE__);
}
}
public static function loadStatsByPlayerId($playerId, $self = true)
{
$stats = ['winP' => 0, 'lossP' => 0, 'winT' => 0, 'lossT' => 0, 'winL' => 0, 'lossL' => 0, 'totalP' => 0, 'totalT' => 0, 'totalL' => 0, 'win' => 0, 'loss' => 0, 'total' => 0];
$hasViewed1 = $self ? ' AND pm1.has_viewed = true' : '';
$hasViewed2 = $self ? ' AND pm2.has_viewed = true' : '';
$sql = 'SELECT m.winner_id, m.type, m.player_match1_id, p1.id as player1Id, m.player_match2_id, p2.id as player2Id
FROM bus_match m
JOIN bus_player_match pm1 ON pm1.id = m.player_match1_id
JOIN bus_player_match pm2 ON pm2.id = m.player_match2_id
JOIN bus_player p1 ON p1.id = pm1.player_id
JOIN bus_player p2 ON p2.id = pm2.player_id
WHERE m.status = ' . MatchStatus::FINISHED . '
AND (
(pm1.player_id = ' . $playerId . $hasViewed1 . ')
OR
(pm2.player_id = ' . $playerId . $hasViewed2 . ')
)';
$db = new Util\Data();
$result = $db->select($sql);
if (count($result) == 0) {
return $stats;
}
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
if ($playerId == $row['player1Id'] && $row['winner_id'] == $row['player_match1_id'] || $playerId == $row['player2Id'] && $row['winner_id'] == $row['player_match2_id']) {
switch ($row['type']) {
case MatchType::PRACTICE:
$stats['winP']++;
break;
case MatchType::LEAGUE:
$stats['winL']++;
break;
case MatchType::TOURNAMENT:
$stats['winT']++;
break;
}
} else {
switch ($row['type']) {
case MatchType::PRACTICE:
$stats['lossP']++;
break;
case MatchType::LEAGUE:
$stats['lossL']++;
break;
case MatchType::TOURNAMENT:
$stats['lossT']++;
break;
}
}
}
$stats['win'] = $stats['winL'] + $stats['winT'];
$stats['loss'] = $stats['lossL'] + $stats['lossT'];
$stats['total'] = $stats['win'] + $stats['loss'];
$stats['totalP'] = $stats['winP'] + $stats['lossP'];
$stats['totalL'] = $stats['winL'] + $stats['lossL'];
$stats['totalT'] = $stats['winT'] + $stats['lossT'];
return $stats;
}
public static function deleteOldMatches()
{
$db = new Util\Data();
$query = 'DELETE FROM bus_log WHERE match_id IN (SELECT id FROM bus_match WHERE modified_date < NOW() - INTERVAL ? HOUR)';
return $db->execute($query, [\Own\Bus\Engine::DAY * 7]);
}
protected static function condition(Util\Data $db, $option)
{
switch ($option) {
case 'expiration':
$db->buildQuery('where', ['content.use_expiration = 0 || (content.go_live_date <= ? AND content.expiry_date >= ?)', Util\Format::date(time(), 'sqlDatetime'), Util\Format::date(time(), 'sqlDatetime')]);
break;
case 'published':
$db->buildQuery('where', ['content.version IN (?)', [VersionType::PUBLISHED, VersionType::UPDATING]]);
break;
case 'pending':
$db->buildQuery('where', ['content.version IN (?)', [VersionType::PENDING, VersionType::PUBLISHING]]);
break;
case 'preview':
$db->buildQuery('where', ['content.version IN (?)', [VersionType::PENDING, VersionType::PUBLISHED, VersionType::PUBLISHING]]);
break;
case 'deleted':
$db->buildQuery('where', ['content.version = ?', VersionType::DELETED]);
break;
}
}
public static function clean()
{
$sql = 'DELETE FROM bus_notification WHERE has_read = 1 AND created_date < NOW() - INTERVAL ? HOUR';
$db = new Util\Data();
return $db->execute($sql, [\Own\Bus\Engine::DAY * 56]);
}
protected static function mapList(Util\Data $db)
{
$rows = $db->select();
if (count($rows) === 0) {
return [];
}
$models = [];
while ($row = $rows->fetch(\PDO::FETCH_ASSOC)) {
$models[] = self::mapper($row);
}
return $models;
}
public static function moveToRoot($folderId)
{
$query = 'UPDATE core_media SET folder_id = 1 WHERE folder_id = ?';
$db = new Util\Data();
return $db->execute($query, [$folderId]);
}
