Powerful and easy to use cms to build websites, blogs or ecommerce stores.
HTML backendblogblog-engineblog-platformbloggingcmscontent-managementcontent-management-systemecommerceecommerce-platformno-codepage-builderphpphp-cmsphp7php8self-hostedshopping-cartweb-applicationwebsite-builder
-- Admin
-- get all entries
CREATE PROCEDURE getAll(
IN start INT,
IN limit INT,
IN cursor INT, -- current cursor (admin_id) for cursor pagination
IN status INT,
IN search CHAR,
IN email CHAR,
IN admin_id ARRAY,
IN phone_number CHAR,
-- return array of admin
OUT fetch_all,
OUT fetch_one
)
BEGIN
SELECT * FROM admin WHERE 1 = 1
@IF isset(:status) AND !empty(:status)
THEN
AND admin.status = :status
END @IF
@IF isset(:email) AND !empty(:email)
THEN
AND admin.email = :email
END @IF
@IF isset(:phone_number) AND !empty(:phone_number)
THEN
AND admin.phone_number = :phone_number
END @IF
@IF isset(:admin_id) AND !empty(:admin_id) AND is_array(:admin_id)
THEN
AND admin.admin_id IN (:admin_id)
END @IF
-- search
@IF isset(:search) AND !empty(:search)
THEN
AND (admin.username LIKE '%' || :search || '%' OR admin.first_name LIKE '%' || :search || '%' OR admin.last_name LIKE '%' || :search || '%')
END @IF
-- cursor pagination
@IF isset(:cursor)
THEN
AND `admin`.admin_id > :cursor
END @IF
ORDER BY admin.status DESC, admin.admin_id
-- limit
@IF isset(:limit)
THEN
LIMIT :limit OFFSET :start
END @IF;
-- SELECT FOUND_ROWS() as count;
SELECT count(*) FROM (
@SQL_COUNT(admin.admin_id, admin) -- this takes previous query removes limit and replaces select columns with parameter admin_id
) as count;
END
-- get user information
CREATE PROCEDURE get(
IN username CHAR,
IN email CHAR,
IN token CHAR,
IN admin_auth_token CHAR,
IN admin_id INT,
IN status INT,
IN role_id INT,
OUT fetch_row
)
BEGIN
SELECT _.*, role.name as role,role.permissions FROM admin AS _
LEFT JOIN role ON (_.role_id = role.role_id)
@IF isset(:admin_auth_token)
THEN
LEFT JOIN admin_auth_token ON (_.admin_id = admin_auth_token.admin_id)
END @IF
WHERE 1 = 1
@IF isset(:username) && !isset(:email)
THEN
AND _.username = :username
END @IF
@IF isset(:email) && !isset(:username)
THEN
AND _.email = :email
END @IF
@IF isset(:email) && isset(:username)
THEN
AND _.email = :email OR _.username = :username
END @IF
@IF isset(:admin_id) && :admin_id
THEN
AND _.admin_id = :admin_id
END @IF
@IF isset(:status)
THEN
AND _.status = :status
END @IF
@IF isset(:token)
THEN
AND _.token = :token
END @IF
@IF isset(:admin_auth_token)
THEN
AND admin_auth_token.token = :admin_auth_token
END @IF
@IF isset(:role_id)
THEN
AND _.role_id = :role_id
END @IF
LIMIT 1;
END
-- Add new admin
CREATE PROCEDURE add(
IN admin ROW,
OUT insert_id
)
BEGIN
-- allow only table fields and set defaults for missing values
@FILTER(:admin, admin)
INSERT INTO admin
( @KEYS(:admin) )
VALUES ( :admin )
END
-- Update admin
CREATE PROCEDURE edit(
IN username CHAR,
IN email CHAR,
IN admin_id INT,
IN admin ROW,
OUT affected_rows
)
BEGIN
-- allow only table fields and set defaults for missing values
@FILTER(:admin, admin)
UPDATE admin
SET @LIST(:admin)
WHERE 1 = 1
@IF isset(:email)
THEN
AND email = :email
END @IF
@IF isset(:admin_id)
THEN
AND admin_id = :admin_id
END @IF
@IF isset(:username)
THEN
AND username = :username
END @IF
END
-- delete admin
CREATE PROCEDURE delete(
IN admin_id ARRAY,
OUT affected_rows
)
BEGIN
DELETE FROM admin WHERE admin_id IN (:admin_id);
END
-- set role
CREATE PROCEDURE setRole(
IN admin_id INT,
IN role CHAR,
IN role_id INT
OUT insert_id
)
BEGIN
UPDATE admin
SET
@IF isset(:role_id)
THEN
role_id = :role_id
END @IF
@IF isset(:role)
THEN
role_id = (SELECT role_id FROM role WHERE name = :role)
END @IF
WHERE admin_id = :admin_id
END