Falcon Space. Working with the catalog, shopping cart, and orders

We are talking about a certain trading platform, such as MarketPlace, where products are presented in the form of a Catalog,
it is possible to add the selected product to the Cart and make Purchase order.
This is available to both authorized users and non-authorized users.

Demo address https://demo.web-automation.ru/catalog

Nomenclature

There are two ways the trading platform works:

1.When the product list is fixed, and is managed by the site administrator.

It is responsible for configuring product characteristics and attributes. and there can be many suppliers offering the same product, but each for its own price, and with its own description of this product. Accordingly, the supplier can only adjust its own price and individual description on the offered product.
Then a list of suppliers and prices will be displayed on one product, and the product cover will show the minimum price and the number of offers from suppliers.

Product

product Cover

2. Without an item. Each supplier has a unique product.then the supplier can manage all the characteristics and attributes of their products themselves.

The cover and product contain just one supplier and the price of the product.Which of the options will be configured is determined in the stored directory procedures.

Display or hide product prices for display is also configured in the catalog's CP.

Product and Supplier Relationship diagram

Product categories

Product categories are in as_cat_categories.
There is a field isdisabled, which can be used to disable category output to a folder.
To make nested categories, you should correctly place parentID and level.
To fill categories with products, you need to register the link in as_cat_categoryproducts.

Product attributes (filters)

All possible product attributes are in as_cat_filters.
Attributes are usually assigned to categories in the table as_cat_categoryFilters, and products, in turn, inherit attributes from all parent categories.
The data type of attribute values is specified in dataTypeID(string, integer, fractional).
Filling in attributes with product-specific values goes to as_cat_productAttrs.

In this table, one of the three columns is filled in according to the set attribute type.

Attribute Value as a value selection from the list (reference list)

If the sqlGetValues field is filled in on the attribute, it means it has a finite number of set values, and for each product, one of these values will be selected from the list.
sqlGetValuesspecifies a stored procedure for selecting a list of attribute values.
Accordingly, in the database, you need to create additional reference tables for these attributes in the form as_cat_cust_{attribute name} (id, name)
In as_cat_productAttrs  then the column intValue   - ID of the entry from the table specified in sqlGetValues will be filled in.

Attributes as filters

Product attributes should also be among the search filters.
The visibleInFilter Field determines whether the attribute will be entered in the filter list.

There are the following types of filters in the catalog:
  • checks - check marks
  • radio - radio switches
  • select - select from the search list
  • switch-Yes-no switch
  • text - input string
  • color - color selection
  • range - select the range

Field typeID defines the filter type.
If the display option is a slider, then the defValuefield must be filled in by . template - {minimum,maximum,step}
For the select filter, add the element Not selected.
To assign each category its own set of filters, you need to specify the relationships in as_cat_categoryfilters

Attributes displayed on the product cover

If the attribute is filled in with the showForProductInList attribute, this attribute will be displayed immediately on the product cover in the product list display mode

Scheme " Link Products-Categories-Attributes (filters)"

Adding products to cart

When you click on the corresponding icon on the product, it goes to the user's shopping cart, in the amount specified in the field next to it.

Bucket table ord_carts. If the user is not authorized, the usernamefield will be equal to null.
The table ord_cartItemscontains items that were added to the cart.

About the ord_cartTypes table

It is used to configure which bucket works on the catalog

Placing an order

Click on the button Place an order   the order form opens below

The form is standard, registered in Falcon with the code cartOrder, i.e. you can view its storage, and accordingly it is possible to modify it.
SaveItem   spelled creating an order. All orders are stored in ord_orders, and go through the bucket table to the order positions (they are also the basket positions).

How do I make a united order?

  • When an order goes directly to the supplier, the Checkoutbutton will be displayed opposite each manufacturerin the shopping Cart. In this case
  • specify ord_cartTypes.isSingleOrder = 0
  • and in cartOrderwill be passed itemID = {cartGuid}_{supplierID}
  • To make a single button Place an orderin the shopping Cart, for example, when the order must go to the Warehouse, that is, regardless of how many suppliers we have selected the product, you must
  • specify in ord_cartTypes.isSingleOrder = 1
  • and in cartOrderwill be passed itemID = {cartGuid}_0

Adding products to favorites

Only an authorized user can add a product to favorites by clicking on the corresponding icon on the product.
Physically, an entry about a favorite product is added to ord_favorites.

Address Of The Favorite https://falcon.web-automation.ru/favorites

The scheme “Shopping Cart - Orders - Favorites Link”

Main Stored procedures in the directory

  • Get details for the product profile-cat_getem (examples of the procedure are better taken from the demo)
CREATE PROCEDURE [dbo].[cat_getItem]
	@itemID int,
	@username nvarchar(32),
	@langID int = 0
AS
BEGIN
	select 0 OneToOneProduct, -- Whether the item is the same for all, or unique for each supplier
		   *,				  -- All the main features of the product
		   REPLACE([desc], char(10), '
')  shortDesc,  -- Value to insert in the short description
       --     'Meta Title1' MetaTitle,
       -- 'Meta Desc2' MetaDescription,
       -- 'Meta Keys3' MetaKeywords,
		   -- Minimum price for all suppliers of this product:
		   dbo.as_num(cast(  isnull((select min(price) from as_cat_supplierProducts where productID = as_cat_products.id), 0) as nvarchar),2) price,
		   ''

'' CustomMakeup  -- Customized product page layout. If empty, but the default layout of the Product page is output
	  from as_cat_products where id = @itemID

	-- We determine at what level the product is located, that is, how many categories it is nested in
	-- (Maybe it's for breadcrumbs?)

	declare @level int

	CREATE TABLE dbo.#cats (id int, code nvarchar(256), name nvarchar(256),  level int, parentID int)

	insert into #cats
	select top 1 id, code, name, [level], parentID
	  from as_cat_categories
	 where id in (select categoryID from as_cat_categoryProducts where productID = @itemID)

	select top 1 @level = level from #cats

	while (@level>0)
	begin
		insert into #cats
		select id, code, name, [level], parentID
		  from as_cat_categories
		 where id in (select parentID from #cats where level=@level)

		set @level = @level -1
	end

	select * from #cats

	-- Determining which filters are assigned to the product

	select as_cat_productAttrs.productID,
		   as_cat_productAttrs.id attrID,
		   as_cat_filters.code attrCode,
		   as_cat_filters.title title,
		   (case dataTypeID when 2 then cast(intValue as nvarchar)
							when 3 then cast(decimalValue as nvarchar)
							else value end )  value,
		   (select code from as_cat_filterTypes where id = as_cat_filters.typeID) typeCode,
		   (select code from as_dataTypes where id = as_cat_filters.dataTypeID) dataType,
		   sqlGetValues,
		   cacheMinutes
	  from as_cat_productAttrs inner join as_cat_filters on as_cat_filters.id = as_cat_productAttrs.filterID
	 where productID = @itemID
	 order by as_cat_filters.ord

	-- Output a list of suppliers that offer this product

	select as_cat_supplierProducts.id supplierProductID,
		  dbo.as_num(cast(  price as nvarchar), 2) price,
		  isnull(as_cat_suppliers.name,code) supplierName,
				 dbo.cat_getSupplierLink(supplierID) supplierLink
	  from as_cat_supplierProducts
	 inner join as_cat_suppliers on as_cat_suppliers.id = as_cat_supplierProducts.supplierID
	 --inner join as_cat_categories on as_cat_categories.code=as_cat_suppliers.code
	 where productID = @itemID --and as_cat_categories.isDisabled='False'
	 --order by price desc

	drop table #cats
END

  • Preparation of filtration products - cat_preSearch
CREATE PROCEDURE [dbo].[cat_preSearch]
	@filters DictionaryParameter READONLY,
	@cats nvarchar(256),
	@username nvarchar(32),
	@langID int = 0
AS
BEGIN
-- preliminary preparation procedure in the catalog search


CREATE TABLE dbo.#cats (id int, code nvarchar(256), name nvarchar(256),  level int, parentID int, icon nvarchar(32))
insert into #cats
select id, code,name, level, parentID, icon from as_cat_categories where code in (select Value from dbo.split(@cats, ',') )

declare @catCount int
select @catCount = count(*) from #cats

declare @selectedCatID int
select top 1 @selectedCatID=id from #cats order by level desc

	-- SELECT 1. return Cats (Category chain)
	select *, isnull(icon, 'fa-cube') Icon from #cats order by level

	-- SELECT 2. return Filters (filter data - for building a filter on the left)
   		select
		as_cat_filters.id,
		as_cat_filters.title,
		as_cat_filters.tooltip,
		as_cat_filters.Code,
		as_cat_filters.defValue,
		as_cat_filters.sqlGetValues,
		as_cat_filters.cacheMinutes,
		(select code from as_cat_filterTypes where id = as_cat_filters.typeID) typeCode,
		(select top 1 Value from @filters where [Key]=as_cat_filters.code) commaSelected
	from as_cat_filters left join as_cat_categoryFilters on as_cat_filters.id = as_cat_categoryFilters.filterID
	where
		(as_cat_categoryFilters.categoryID in (select id from #cats) or as_cat_categoryFilters.id is null)
		and visibleInFilter='1'
	order by as_cat_categoryFilters.ord

	-- SELECT 3. return InnerCats (list of child categories)
	select *, isnull(icon, 'fa-cube') Icon from as_cat_categories t1 where parentID = @selectedCatID or (parentID is null and @catCount=0)

	drop table #cats
END
  • The procedure of filtration products - cat_search
CREATE PROCEDURE [dbo].[cat_search]
	@filters ExtendedDictionaryParameter READONLY,
	@selectedCatID int,
	@sort nvarchar(24),
	@page int,
	@username nvarchar(32),
	@langID int = 0
AS
BEGIN
-- catalog search procedure


declare @PAGE_SIZE int = 15

declare @title nvarchar(max) = '', @bannerImage nvarchar(256), @bannerText nvarchar(max)
set @title  = isnull(	(select name from as_cat_categories where id =@selectedCatID), 'Product catalog')
set @bannerText  = replace( isnull(	(select [desc] from as_cat_categories where id =@selectedCatID), ''), '''', '"')
set @bannerImage = dbo.rs_resourceLink('catBanner', @selectedCatID, 0)

exec as_print @str = @bannerText
exec as_print @str = @bannerImage


declare @allCatIDs table (id int)
insert into @allCatIDs(id) values (@selectedCatID)

--insert into @allCatIDs (id) select id from as_cat_categories where parentID in (select id from @allCatIDs) and isDisabled = 'false'
--insert into @allCatIDs (id) select id from as_cat_categories where parentID in (select id from @allCatIDs) and isDisabled = 'false'
--insert into @allCatIDs (id) select id from as_cat_categories where parentID in (select id from @allCatIDs) and isDisabled = 'false'
--insert into @allCatIDs (id) select id from as_cat_categories where parentID in (select id from @allCatIDs) and isDisabled = 'false'
--insert into @allCatIDs (id) select id from as_cat_categories where parentID in (select id from @allCatIDs) and isDisabled = 'false'
declare @allCatIDsStr nvarchar(500)

-- list of all parent categories (to identify common attributes that we should search for)
SELECT @allCatIDsStr = STUFF((
        select ','+ cast(id as nvarchar)
        from @allCatIDs
    FOR XML PATH('')
    ),1,1,'')


DECLARE @s nvarchar(max);
DECLARE @ParmDefinition nvarchar(max);

-- SELECT 1. return Products (выдаем найденные товары)
SET @s = N'
	declare  @productIDs table (id int)

insert into @productIDs (id)
select id from as_cat_products prods '+
	 ' where  id in (select productID from  as_cat_categoryProducts where ( categoryID in ('+@allCatIDsStr+') or '+cast(isnull(@selectedCatID, 0)as nvarchar)+'=0 )
																	or ( parentID in ('+@allCatIDsStr+') or '+cast(isnull(@selectedCatID, 0)as nvarchar)+'=0 )) ';

-- collecting a filter by attributes (continuation of the Dynamic SELECT 1 query for products)
declare @key nvarchar(256), @value nvarchar(512), @fTitle nvarchar(512), @fTypeCode nvarchar(32)
DECLARE prodCursor CURSOR FOR  SELECT [Key], Value, Title, Value2 from @filters
OPEN prodCursor
FETCH NEXT FROM prodCursor
INTO @key,@value,@fTitle, @fTypeCode
WHILE @@FETCH_STATUS = 0
BEGIN
	-- exec as_print @key
	-- exec as_print @value
    if(@fTypeCode in ('checks', 'radio', 'select', 'switch', 'range' )) begin
		set @title = @title + ' '+@fTitle
	end

	set @s = @s + ' and ( '''+@key+'''='''' or exists (
	select as_cat_productAttrs.id from as_cat_productAttrs inner join as_cat_filters on as_cat_filters.id = as_cat_productAttrs.filterID
	where productID = prods.id
		and as_cat_filters.code = '''+@key+'''
		and (
			(as_cat_filters.typeID in (3,4) and (     -- checkboxes and radio
				datatypeID =2 and exists( select value from  dbo.split('''+@value+''', '','') where value=as_cat_productAttrs.intValue) or
				datatypeID =3 and exists( select value from  dbo.split('''+@value+''', '','') where value=as_cat_productAttrs.decimalValue) or
				datatypeID =1 and exists( select value from  dbo.split('''+@value+''', '','') where value=as_cat_productAttrs.value)
			))
			or
			(as_cat_filters.typeID in (1) and (     -- range
				datatypeID =2 and
				(as_cat_productAttrs.intValue  >= (select min(try_cast(value as int)) from  dbo.split('''+@value+''', '','')) and
						(as_cat_productAttrs.intValue  <= (select max(try_cast(value as int)) from  dbo.split('''+@value+''', '','')))
				) or
				datatypeID =3 and
				(as_cat_productAttrs.decimalValue  >= (select min(try_cast(value as decimal(18,2))) from  dbo.split('''+@value+''', '','')) and
						(as_cat_productAttrs.decimalValue  <= (select max(try_cast(value as decimal(18,2))) from  dbo.split('''+@value+''', '','')))
				) or
				datatypeID =1 and
				1=1 -- don't compare string values by range
			)

			or as_cat_filters.typeID not in (3,4,1)
	    )
	)
    -- processing global parameters the Search field and the warehouse Switch
    or ('''+@key+'''=''textsearch'' and lower(prods.name) like ''%''+lower('''+@value+''')+''%'')
	 or ('''+@key+'''=''instock'')
	))'
   -- exec as_print @s
    FETCH NEXT FROM prodCursor
INTO @key,@value, @fTitle, @fTypeCode

END
CLOSE prodCursor;
DEALLOCATE prodCursor;

--- adding sorting to the Dynamic query SELECT 1
declare @sortStatement nvarchar(512)
set @sortStatement = (case @sort
				when 'new' then ' order by name '
				when 'price' then ' order by as_cat_products.price '
				when 'discount' then ' order by id '
				else ' order by id desc '
				end	)

-- define select parameters
/*
DECLARE parametersCursor CURSOR FOR  SELECT [Key], Value from @filters
OPEN parametersCursor

FETCH NEXT FROM parametersCursor
INTO @key,@value

WHILE @@FETCH_STATUS = 0
BEGIN
	set @s = @s + ' and ( '''+@key+'''='''' or exists ('


    FETCH NEXT FROM parametersCursor
INTO @key,@value

END
CLOSE parametersCursor;
DEALLOCATE parametersCursor;
*/

-- defining what to select in a Dynamic query SELECT 1
set @s = @s +
	' select *,
		dbo.as_num(cast(  isnull((select min(price) from as_cat_supplierProducts where productID =as_cat_products.id),price)  as nvarchar), 2) price,
		(select count(price) from as_cat_supplierProducts where productID =as_cat_products.id and price>0) supplierCount,
		'''' SupplierName, -- for OneToOneProduct = 1
		'''' SupplierLink,
		0 SupplierProductID,
		[dbo].[cat_getProductImageURL](as_cat_products.id, 0) img

	from as_cat_products where id in (select id from @productIDs)
	'+@sortStatement+'
	OFFSET ((@page - 1) * @PAGE_SIZE) ROWS
	FETCH NEXT @PAGE_SIZE ROWS ONLY;


	-- SELECT 2 Catalog settings - возвращаем настройки каталога + результат операции (Result, Msg)
	select ''Not found'' EmptyText,
		0 OneToOneProduct,
	'''+@title+''' Title,
    	'''+isnull(@bannerImage, '')+''' BannerImage,
        '''+isnull(@bannerText, '')+''' BannerText,
		@sort Sort,
		@page [Page],
		@PAGE_SIZE PageSize,
		(select count(*) from @productIDs) Total,
		1 Result, '''' Msg   ,
    '''+@title+''' MetaTitle,
     '''+@title+''' MetaDescription,
        '''' MetaKeywords


	-- SELECT 3 Product attrs - returning all attributes of the found products
	select
	as_cat_productAttrs.productID,
as_cat_productAttrs.id attrID,
as_cat_filters.code attrCode,
as_cat_filters.title title,
(case dataTypeID when 2 then cast(intValue as nvarchar)   when 3 then cast(decimalValue as nvarchar) else value end )  value,
 (select code from as_cat_filterTypes where id = as_cat_filters.typeID) typeCode,
 (select code from as_dataTypes where id = as_cat_filters.dataTypeID) dataType,
 sqlGetValues,
 cacheMinutes
 from as_cat_productAttrs inner join as_cat_filters on as_cat_filters.id = as_cat_productAttrs.filterID
where showForProductInList=1 and productID in (select id from @productIDs)
order by as_cat_productAttrs.productID, as_cat_filters.ord

	'
set @s = replace(@s, '{parameters}', '1')

-- executing a dynamic query SELECT 1 (Goods), SELECT 2 (Settings of catalog), SELECT 3 (Attributes of goods)
EXECUTE sp_executesql @s, N'@sort nvarchar(24), @selectedCatID int, @page int, @PAGE_SIZE int ', @sort = @sort, @selectedCatID = @selectedCatID, @page = @page, @PAGE_SIZE = @PAGE_SIZE;

-- SELECT 4. return Sorts Returning sorting options
select 'favorite' [Key], 'By popularity' Value
union
select 'new' [Key], 'By novelty' Value
union
select 'price' [Key], 'At the price' Value
--union
--select 'discount' [Key], 'At a discount' Value
select @s

END

Falcon Space is a functional web development platform on a narrow stack MS SQL/Bootstrap. Falcon Space Gettting started
{sp-shortDemostandLinks}