public static function findByCondition($condWhere, $condOrder = 'id', $condTurn = 'DESC') { $class = static::class; $db = new Db(); if (count($condWhere) == 0) { $sql = 'SELECT * FROM `' . static::getTable() . '` ORDER BY `' . $condOrder . '` ' . $condTurn; $res = $db->dbSelect($class, $sql); } else { $places = []; $data = []; foreach ($condWhere as $k => $v) { $places[] = '`' . $k . '` = :' . $k; $data[':' . $k] = $v; } $sql = 'SELECT * FROM `' . static::getTable() . '` WHERE ' . implode(' AND ', $places) . ' ORDER BY `' . $condOrder . '` ' . $condTurn; $res = $db->dbSelect($class, $sql, $data); } if ($res) { return $res; } else { throw new E404Exception(static::getTable()); } }
<?php require_once __DIR__ . '/db/Db.php'; $config = json_decode(file_get_contents(__DIR__ . '/db/config.json')); $db = new Db($config); $actions = ['development', 'documents', 'marketing', 'negotiations', 'support']; $arrResult = []; $arrResult['cols'] = [['label' => 'action', 'type' => 'string'], ['label' => 'value', 'type' => 'number']]; $arrResult['rows'] = []; $sql = 'SELECT action , SUM(duration) val FROM activities WHERE created_at BETWEEN :start AND :end GROUP BY action ORDER BY action'; $values = $db->dbSelect($sql, [':start' => $_GET['start'], ':end' => $_GET['end']]); $i = 0; foreach ($values as $value) { while ($value->action != $actions[$i] && $i < count($actions)) { array_push($arrResult['rows'], ['c' => [0 => ['v' => $actions[$i]], 1 => ['v' => 0]]]); $i++; } array_push($arrResult['rows'], ['c' => [0 => ['v' => $value->action], 1 => ['v' => $value->val]]]); $i++; } echo json_encode($arrResult, JSON_NUMERIC_CHECK);
<?php include __DIR__ . '/db.php'; $resArr = []; $sql = 'SELECT * FROM `game_master` ORDER BY `id` DESC'; $db = new Db(); $resArr = $db->dbSelect($sql); echo json_encode($resArr, JSON_UNESCAPED_UNICODE);
<?php include __DIR__ . '/db.php'; $resArr = []; $sql = 'SELECT `timestamp`, `event_message`, `event_class` FROM `game_details` WHERE `id_game` = :id ORDER BY `id`'; $db = new Db(); $resArr = $db->dbSelect($sql, [':id' => $_GET['id']]); echo json_encode($resArr, JSON_UNESCAPED_UNICODE); // set @n:=0; // select @n:=@n+1 as rownum, ab.* from ( // select a.gamer, a.level, a.count_level, a.sum_level, a.avg_level, b.count_total, b.sum_total, b.avg_total // from // (SELECT gamer, level, COUNT( * ) count_level, SUM( score ) sum_level, AVG(score) avg_level // FROM `game_master` // GROUP BY gamer, level // ) a, // (SELECT gamer, COUNT( * ) count_total, SUM( score ) sum_total, AVG(score) avg_total // FROM `game_master` // GROUP BY gamer // ) b // where a.gamer = b.gamer // order by 8 DESC) ab // set @n:=0; // select @n:=@n+1 as rownum, z.* from ( // SELECT gamer, COUNT( * ) count_total, SUM( score ) sum_total, AVG(score) avg_total // FROM `game_master` // GROUP BY gamer ORDER BY avg_total DESC) z // set @n:=0; // select @n:=@n+1 as rownum, // a.count_1, a.sum_1, a.avg_1, // b.count_2, b.sum_2, b.avg_2,
<?php require_once __DIR__ . '/db/Db.php'; $db = new Db(); $arrResult = []; $arrResult['cols'][0] = ['label' => 'employee', 'type' => 'string']; $arrResult['rows'] = []; $sql = 'SELECT action FROM actions ORDER BY 1'; $actions = $db->dbSelect($sql); foreach ($actions as $action) { array_push($arrResult['cols'], ['label' => $action->action, 'type' => 'number']); } $sql = 'SELECT a.employee_id, CONCAT_WS(\' \', c.first_name, c.last_name) employee, SUM(a.duration) val FROM activities a, contacts c WHERE a.employee_id <> \'\' AND a.employee_id = c.id AND a.created_at BETWEEN :start AND :end GROUP BY 1 ORDER BY 3 DESC'; $employees = $db->dbSelect($sql, [':start' => $_GET['start'], ':end' => $_GET['end']]); foreach ($employees as $employee) { $arrC = []; $arrC[0] = ['v' => $employee->employee]; foreach ($actions as $action) { $sql = 'SELECT `action`, SUM(`duration`) `val` FROM `activities` WHERE `employee_id` = :id AND action = :action AND `created_at` BETWEEN :start AND :end GROUP BY 1'; $values = $db->dbSelect($sql, [':id' => $employee->employee_id, ':action' => $action->action, ':start' => $_GET['start'], ':end' => $_GET['end']])[0]; if (!empty($values)) { array_push($arrC, ['v' => $values->val]); } else { array_push($arrC, ['v' => 0]); }
do { $currentAction = rand(0, 4); $currentEmployeeId = rand(0, 17); $currentCreatedAt = rand(1388520000, 1420059600); $hour = (int) date('G', $currentCreatedAt); $dayOfWeek = (int) date('N', $currentCreatedAt); if ($hour < 9 || $hour > 18 || $dayOfWeek == 6 || $dayOfWeek == 7) { continue; } else { echo $i . ' = ' . $hour . ' $dayOfWeek = ' . $dayOfWeek . '; '; // определяем сотрудника $currentDuration = rand(300, 6000); // длительность активности в секундах // проверяем, была ли уже в этот период времени активность у текущего контакта или текущего сотрудника // если уже была, то повторно выбираем $currentTimestamp и повторно проверяем $sql = 'SELECT * FROM `activities` WHERE ( (:currentCreatedAt BETWEEN `created_at` AND (`created_at`+`duration`)) OR (:currentCreatedAt < `created_at` AND (:currentCreatedAt + :currentDuration) >= `created_at`)) AND (`contact_id` IN (:currentContactId, :currentEmployeeId) OR `employee_id` IN (:currentContactId, :currentEmployeeId))'; $db = new Db(); $select = $db->dbSelect($sql, [':currentCreatedAt' => $currentCreatedAt, ':currentDuration' => $currentDuration, ':currentContactId' => $currentContactId, ':currentEmployeeId' => $currentEmployeeId]); if (empty($select)) { $sql = 'INSERT INTO `activities` ( `action`, `created_at`, `duration`, `contact_id`, `employee_id`) VALUES (:currentAction, :currentCreatedAt, :currentDuration, :currentContactId, :currentEmployeeId)'; $insert = $db->dbExecute($sql, [':currentAction' => $currentAction, ':currentCreatedAt' => $currentCreatedAt, ':currentDuration' => $currentDuration, ':currentContactId' => $currentContactId, ':currentEmployeeId' => $currentEmployeeId]); $i++; } else { continue; } } } while ($i <= $quantityActivities);
<?php require_once __DIR__ . '/db/Db.php'; $arrResult = []; $db = new Db(); $sql = 'SELECT `employee_id`, SUM(`duration`) `val` FROM `activities` WHERE `employee_id` <> \'\' AND `created_at` BETWEEN :start AND :end GROUP BY 1 ORDER BY 2 DESC'; $values1 = $db->dbSelect($sql, [':start' => $_GET['start'], ':end' => $_GET['end']]); foreach ($values1 as $value1) { $sql = 'SELECT (SELECT CONCAT_WS(\' \', first_name, last_name) FROM contacts WHERE id = :id) employee, action, SUM(duration) val FROM activities WHERE employee_id = :id AND created_at BETWEEN :start AND :end GROUP BY 2'; $values2 = $db->dbSelect($sql, [':id' => $value1->employee_id, ':start' => $_GET['start'], ':end' => $_GET['end']]); foreach ($values2 as $value2) { $arrResult[$value2->employee][$value2->action] = $value2->val; } } echo json_encode($arrResult, JSON_UNESCAPED_UNICODE);
<?php require_once __DIR__ . '/db/Db.php'; $config = json_decode(file_get_contents(__DIR__ . '/db/config.json')); $db = new Db($config); $number = cal_days_in_month(CAL_GREGORIAN, 8, 2003); // 31 $arrTimes = ['07:00:00', '10:20:00', '13:40:00', '17:00:00', '20:20:00']; $arrActions = ['development', 'support', 'marketing', 'documents', 'negotiations']; $sql = 'SELECT id FROM contacts WHERE is_employee'; $idEmployees = $db->dbSelect($sql); $sql = 'SELECT id FROM contacts where id'; $idContacts = $db->dbSelect($sql); $day = 10; while ($day < 32) { $currentDate = '2015-12-' . $day . ' '; $quantityEmployeeThisDay = rand(7, 17); $employeeIndexes = array_rand($idEmployees, $quantityEmployeeThisDay); foreach ($employeeIndexes as $employeeIndex) { $quantityActionsThisDay = rand(2, 5); $actionIndexes = array_rand($arrActions, $quantityActionsThisDay); $timeIndexes = array_rand($arrTimes, $quantityActionsThisDay); foreach ($actionIndexes as $key => $actionIndex) { $currentAction = $arrActions[$actionIndex]; $currentDuration = rand(30, 200) * 60; $currentCreatedDate = $currentDate . $arrTimes[$timeIndexes[$key]]; $currentContactId = rand(1, 30); $currentEmployeeId = $idEmployees[$employeeIndex]->id; var_dump($currentAction); var_dump($currentCreatedDate); var_dump($currentDuration);
function actionOperations() { $arrResult = []; if ($_GET['user'] != $_SESSION['username'] || !Application::checkToken($_GET['token'])) { $arrResult['result'] = false; $arrResult['message'] = 'Неавторизованный запрос'; } else { $sql = 'SELECT `oper_date`, `amount`, `balance`, `comment` FROM `accounts` WHERE `owner` = :username ORDER BY `id` DESC'; $db = new Db(); $accounts = $db->dbSelect($sql, [':username' => $_GET['user']]); if (empty($accounts)) { $arrResult['result'] = false; $arrResult['message'] = 'Пользователь не имеет счёта'; } else { $arrResult['result'] = true; $arrResult['message'] = 'Получены операции по счету'; $arrResult['data'] = $accounts; } } echo json_encode($arrResult, JSON_UNESCAPED_UNICODE); }