PHP Db::dbSelect方法代码示例

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