' AS updt
FROM app_settings
" . $whereQuery . "
ORDER BY id
";
return $query;
}
public function getGlobalSettingsQuery($params = [])
{
$whereQuery = ' WHERE 1 = 1 ';
if (!empty($params['key'])) {
$whereQuery .= " AND key ILIKE '%" . pg_escape_string(trim($params['key'])) . "%' ";
}
if (!empty($params['description'])) {
$whereQuery .= " AND description ILIKE '%" . pg_escape_string(trim($params['description'])) . "%' ";
}
if (isset($params['status']) && $params['status'] != -1) {
$whereQuery .= " AND status = " . pg_escape_string($params['status']) . " ";
}
$query = "
SELECT *
FROM
global_settings
" . $whereQuery . "
ORDER BY id
";
return $query;
}
public function getTransportProviderQuery($params = [])
{
$whereQuery = ' WHERE 1 = 1 ';
if (!empty($params['name'])) {
$whereQuery .= " AND name ILIKE '%" . pg_escape_string(trim($params['name'])) . "%' ";
}
$query = "
SELECT
id,
concat('
', name, '') AS Name,
concat('
Client: ', client, '
Token: ', token, '
Code: ', code, '
Access Token: ', access_token, '
Name Alias: ', name_alias) AS Credentials,
(
CASE active
WHEN 1 THEN
'Yes'
ELSE
'No'
END) AS Active,
'
' AS action
FROM
transport_providers
" . $whereQuery . "
ORDER BY
name
";
return $query;
}
public function getTransportProviderAppQuery($params = [])
{
$query = "
SELECT
b.name,
a.country,
a.ios_app_id,
a.android_app_id,
concat('
') AS Action
FROM
transport_provider_apps a,
transport_providers b
WHERE
b.id = a.transport_provider_id
ORDER BY
b.name,
a.country
";
return $query;
}
public function insertOrUpdateAdminQuery($data)
{
$query = "INSERT INTO bko_users
(
pid,
firstname,
lastname,
email,
username,
password,
plevel,
status,
last_login,
loc
)
VALUES
(
" . $data['pid'] . ",
'" . pg_escape_string($data['firstname']) . "',
'" . pg_escape_string($data['lastname']) . "',
'" . pg_escape_string($data['email']) . "',
'" . pg_escape_string($data['username']) . "',
'" . pg_escape_string(md5($data['password'])) . "',
" . $data['plevel'] . ",
" . $data['status'] . ",
" . ($data['last_login'] ? "'" . pg_escape_string($data['last_login']) . "'" : 'NULL') . ",
" . ($data['loc'] ? "'" . pg_escape_string($data['loc']) . "'" : 'NULL') . "
) ON CONFLICT (username) DO UPDATE SET
pid = " . $data['pid'] . ",
firstname = '" . pg_escape_string($data['firstname']) . "',
lastname = '" . pg_escape_string($data['lastname']) . "',
email = '" . pg_escape_string($data['email']) . "',
" . (empty($data['password']) ? "" : "password = '" . pg_escape_string(md5($data['password'])) . "',") . "
plevel = " . $data['plevel'] . ",
status = " . $data['status'] . ",
last_login = " . ($data['last_login'] ? "'" . pg_escape_string($data['last_login']) . "'" : 'NULL') . ",
loc = " . ($data['loc'] ? "'" . pg_escape_string($data['loc']) . "'" : 'NULL') . "
RETURNING *,
CASE WHEN xmax::text::int > 0
THEN 'updated' else 'inserted' END AS active";
return $query;
}
public function getAdminListQuery($params = [])
{
$whereQuery = ' WHERE 1 = 1 ';
if (!empty($params['pid'])) {
$whereQuery .= " AND pid = " . pg_escape_string($params['pid']) . " ";
}
if (!empty($params['plevel'])) {
$whereQuery .= " AND plevel = " . pg_escape_string($params['plevel']) . " ";
}
if (!empty($params['first_name'])) {
$whereQuery .= " AND firstname ILIKE '%" . pg_escape_string(trim($params['first_name'])) . "%' ";
}
if (!empty($params['last_name'])) {
$whereQuery .= " AND lastname ILIKE '%" . pg_escape_string(trim($params['last_name'])) . "%' ";
}
if (!empty($params['email'])) {
$whereQuery .= " AND email ILIKE '%" . pg_escape_string(trim($params['email'])) . "%' ";
}
if (!empty($params['username'])) {
$whereQuery .= " AND username ILIKE '%" . pg_escape_string(trim($params['username'])) . "%' ";
}
if (!empty($params['last_login'])) {
// last_login format: 2020-02-03 - 2020-03-01
$fromToArray = explode(' - ', $params['last_login']);
$fromDate = $fromToArray[0];
$toDate = $fromToArray[1];
$whereQuery .= " AND to_char(last_login, 'YYYY-MM-DD') >= '" . pg_escape_string($fromDate) . "'
AND to_char(last_login, 'YYYY-MM-DD') <= '" . pg_escape_string($toDate) . "' ";
}
$query = "
SELECT *,
to_char(added, 'YYYY-MM-DD') AS added,
to_char(last_login, 'YYYY-MM-DD') AS last_login
FROM
bko_users
" . $whereQuery . "
ORDER BY
username
";
return $query;
}
}