Skip to content

armgit / Vvveb

Public Imported from GitHub
0

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

2.6 KB
Raw Blame
-- Vendors

	-- get one vendor

	CREATE PROCEDURE get(
		IN vendor_id INT,
		IN slug CHAR,
		OUT fetch_row 
	)
	BEGIN
		-- vendor
		SELECT *
			FROM vendor as _ -- (underscore) _ means that data will be kept in main array
		WHERE 1 = 1 
		
		@IF isset(:vendor_id)
		THEN
			AND vendor_id = :vendor_id
		END @IF				
		
		@IF isset(:slug)
		THEN
			AND slug = :slug
		END @IF	

		LIMIT 1;	
		 -- SELECT *,vendor_option_id as _ 
			-- FROM vendor_option  WHERE vendor_id = :vendor_id;
			--@EACH(vendor_option, vendor_option_value) 
				-- SELECT *, vendor_option_value_id as _ FROM vendor_option_value pov 
					-- WHERE vendor_option_id = :vendor_option[vendor_option_id];

	END
	



	-- Edit vendor

	CREATE PROCEDURE edit(
		IN vendor ROW,
		IN vendor_id INT,
		OUT affected_rows
	)
	BEGIN

		-- SELECT * FROM vendor_option WHERE vendor_id = :vendor_id;

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



	-- Add new vendor

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

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

		
		INSERT INTO vendor_to_site 
		
			( vendor_id, site_id )
			
		VALUES ( @result.vendor, :site_id );
			

	END


	-- get all vendors 

	CREATE PROCEDURE getAll(

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

		SELECT * FROM vendor
		
			LEFT JOIN vendor_to_site p2s ON (vendor.vendor_id = p2s.vendor_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 vendor.vendor_id > :cursor

			
		END @IF

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


	END


	-- delete vendor

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

		DELETE FROM vendor WHERE vendor_id IN (:vendor_id)
	 
	END