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
-- Digital assets
-- get all digital_assets
CREATE PROCEDURE getAll(
IN language_id INT,
IN user_id INT,
IN admin_id INT,
IN product_id INT,
IN file ARRAY, -- filter by file names
IN order_status_id INT,
IN start INT,
IN limit INT,
IN cursor INT, -- current cursor (digital_asset_id) for cursor pagination
OUT fetch_all,
OUT fetch_one
)
BEGIN
-- digital_asset
SELECT DISTINCT digital_asset.digital_asset_id, digital_asset.*, digital_asset_content.name
-- user_id
@IF isset(:user_id)
THEN
, o.customer_order_id, o.order_id, op.name as product_name
END @IF
FROM digital_asset
INNER JOIN digital_asset_content ON digital_asset_content.digital_asset_id = digital_asset.digital_asset_id
AND digital_asset_content.language_id = :language_id
-- user_id
@IF isset(:user_id)
THEN
INNER JOIN product_to_digital_asset ptda ON ptda.digital_asset_id = digital_asset.digital_asset_id
INNER JOIN order_product op ON op.product_id = ptda.product_id
INNER JOIN "order" o ON o.order_id = op.order_id AND o.user_id = :user_id AND o.order_status_id = :order_status_id
END @IF
-- product_id
@IF isset(:product_id)
THEN
INNER JOIN product_to_digital_asset ptdap ON ptdap.digital_asset_id = digital_asset.digital_asset_id AND ptdap.product_id = :product_id
END @IF
-- file
@IF isset(:file) && count(:file) > 0
THEN
AND digital_asset.file IN (:file)
END @IF
WHERE 1 = 1
-- admin_id
@IF isset(:admin_id)
THEN
AND digital_asset.admin_id = :admin_id
END @IF
-- cursor pagination
@IF isset(:cursor)
THEN
AND digital_asset.digital_asset_id > :cursor
END @IF
-- limit
@IF isset(:limit)
THEN
LIMIT :limit OFFSET :start
END @IF;
SELECT count(*) FROM (
@SQL_COUNT(digital_asset.digital_asset_id, digital_asset) -- this takes previous query removes limit and replaces select columns with parameter product_id
) as count;
END
-- get digital_asset
CREATE PROCEDURE get(
IN digital_asset_id INT,
IN user_id INT,
IN admin_id INT,
IN language_id INT,
OUT fetch_row
)
BEGIN
-- digital_asset
SELECT *
FROM digital_asset as _
INNER JOIN digital_asset_content ON digital_asset_content.digital_asset_id = _.digital_asset_id
AND digital_asset_content.language_id = :language_id
-- user_id
@IF isset(:user_id)
THEN
INNER JOIN product_to_digital_asset ptda ON ptda.digital_asset_id = _.digital_asset_id
INNER JOIN order_product op ON op.product_id = ptda.product_id
INNER JOIN "order" o ON o.order_id = op.order_id AND o.user_id = :user_id AND o.order_status_id = :order_status_id
END @IF
WHERE _.digital_asset_id = :digital_asset_id
-- admin_id
@IF isset(:admin_id)
THEN
AND _.admin_id = :admin_id
END @IF;
END
-- add digital_asset
CREATE PROCEDURE add(
IN digital_asset ROW,
IN language_id INT,
OUT fetch_one,
OUT insert_id
)
BEGIN
-- allow only table fields and set defaults for missing values
:digital_asset_data = @FILTER(:digital_asset, digital_asset)
INSERT INTO digital_asset
( @KEYS(:digital_asset_data) )
VALUES ( :digital_asset_data ) RETURNING digital_asset_id;
:digital_asset_content = @FILTER(:digital_asset, digital_asset_content)
INSERT INTO digital_asset_content
( @KEYS(:digital_asset_content), language_id, digital_asset_id )
VALUES ( :digital_asset_content, :language_id, @result.digital_asset);
END
-- edit digital_asset
CREATE PROCEDURE edit(
IN digital_asset ROW,
IN digital_asset_id INT,
IN admin_id INT,
OUT affected_rows,
OUT affected_rows
)
BEGIN
-- allow only table fields and set defaults for missing values
:digital_asset_data = @FILTER(:digital_asset, digital_asset)
UPDATE digital_asset
SET @LIST(:digital_asset_data)
WHERE digital_asset_id = :digital_asset_id
-- admin_id
@IF isset(:admin_id)
THEN
AND digital_asset.admin_id = :admin_id
END @IF;
-- allow only table fields and set defaults for missing values
:digital_asset_content = @FILTER(:digital_asset, digital_asset_content)
UPDATE digital_asset_content
SET @LIST(:digital_asset_content)
WHERE digital_asset_id = :digital_asset_id
@IF isset(:admin_id)
THEN
AND digital_asset_content.digital_asset_id IN (SELECT digital_asset_id FROM digital_asset WHERE digital_asset.admin_id = :admin_id)
END @IF;
END
-- delete digital_asset
CREATE PROCEDURE delete(
IN digital_asset_id ARRAY,
IN admin_id INT,
OUT affected_rows,
OUT affected_rows
)
BEGIN
-- digital_asset
DELETE digital_asset_content FROM digital_asset_content WHERE digital_asset_id IN (:digital_asset_id)
@IF isset(:admin_id)
THEN
INNER JOIN product ON (product.product_id = digital_asset_content.product_id AND digital_asset_content.admin_id = :admin_id)
END @IF;
DELETE FROM digital_asset WHERE digital_asset_id IN (:digital_asset_id)
-- admin_id
@IF isset(:admin_id)
THEN
AND _.admin_id = :admin_id
END @IF;
END