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