Skip to content

armgit / Vvveb

Public Imported from GitHub
0

Powerful and easy to use cms to build websites, blogs or ecommerce stores.

3.0 KB
Raw Blame
-- Categories

	-- get one manufacturer



	CREATE PROCEDURE get(
		IN manufacturer_id INT,
		IN slug CHAR,
		OUT fetch_row 
	)
	BEGIN
		-- manufacturer
		SELECT *
			FROM manufacturer as _ -- (underscore) _ means that data will be kept in main array
		WHERE 1 = 1 

		@IF isset(:manufacturer_id)
		THEN
			AND manufacturer_id = :manufacturer_id
		END @IF				
		
		@IF isset(:slug)
		THEN
			AND slug = :slug
		END @IF			
		
		LIMIT 1;

		 -- SELECT *,manufacturer_option_id as _ 
			-- FROM manufacturer_option  WHERE manufacturer_id = :manufacturer_id;
			--@EACH(manufacturer_option, manufacturer_option_value) 
				-- SELECT *, manufacturer_option_value_id as _ FROM manufacturer_option_value pov 
					-- WHERE manufacturer_option_id = :manufacturer_option[manufacturer_option_id];

	END
	



	-- Edit manufacturer

	CREATE PROCEDURE edit(
		IN manufacturer ROW,
		IN manufacturer_id INT,
		OUT affected_rows
	)
	BEGIN

		-- SELECT * FROM manufacturer_option WHERE manufacturer_id = :manufacturer_id;

		-- allow only table fields and set defaults for missing values
		@FILTER(:manufacturer, manufacturer)
		
		@IF !empty(:manufacturer)
		THEN
			UPDATE manufacturer 
				
				SET @LIST(:manufacturer) 
				
			WHERE manufacturer_id = :manufacturer_id
		END @IF
	END	



-- Add new manufacturer

	CREATE PROCEDURE add(
		IN manufacturer ROW,
		IN site_id INT,
		OUT fetch_one,
		OUT affected_rows
	)
	BEGIN
		
		-- allow only table fields and set defaults for missing values
		:manufacturer  = @FILTER(:manufacturer, manufacturer)

		INSERT INTO manufacturer 
			
@IF !empty(:manufacturer)
		THEN
			
			( @KEYS(:manufacturer) )
			
		  	VALUES ( :manufacturer ) RETURNING manufacturer_id
			
		@ELSE
			
			() VALUES () RETURNING manufacturer_id
			
		END @IF;

		
		INSERT INTO manufacturer_to_site 
		
			( manufacturer_id, site_id )
			
		VALUES ( @result.manufacturer, :site_id );
			

	END


	-- get all manufacturers 

	CREATE PROCEDURE getAll(

		-- variables
		IN  site_id INT,
		IN  search CHAR,
		
		-- pagination
		IN  start INT,
		IN  limit INT,
		IN cursor INT, -- current cursor (manufacturer_id) for cursor pagination
			
		-- return array of manufacturers for manufacturers query
		OUT fetch_all,
		OUT fetch_one
	)
	BEGIN

		SELECT * FROM manufacturer
		
			LEFT JOIN manufacturer_to_site p2s ON (manufacturer.manufacturer_id = p2s.manufacturer_id) 
			WHERE p2s.site_id = :site_id

			@IF isset(:search)
			THEN 
			
				AND name LIKE CONCAT('%',:search,'%')
				
			END @IF			

			
		-- cursor pagination

			
		@IF isset(:cursor)

			
		THEN

			
		    AND manufacturer.manufacturer_id > :cursor

			
		END @IF

			
		LIMIT :limit OFFSET :start;
		
		SELECT count(*) FROM (
			
			@SQL_COUNT(manufacturer.manufacturer_id, manufacturer) -- this takes previous query removes limit and replaces select columns with parameter product_id
			
		) as count;


	END


	-- delete manufacturer

	CREATE PROCEDURE delete(
		IN  manufacturer_id ARRAY,
		OUT affected_rows
	)
	BEGIN

		DELETE FROM manufacturer WHERE manufacturer_id IN (:manufacturer_id)
	 
	END