SQLize | PHPize | SQLtest

USE [webPortal] GO /****** Object: StoredProcedure [dbo].[GetCards_JSON] Script Date: 23.07.2025 14:56:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[GetCards_JSON] @kod int AS BEGIN SET NOCOUNT ON; CREATE TABLE #tmpTabGoodsJSON( [id] [uniqueidentifier] NOT NULL, [stringJSON] [varchar](5000) NULL) ALTER TABLE #tmpTabGoodsJSON ADD CONSTRAINT [DF_TmpTabGoods_ID] DEFAULT (NEWSEQUENTIALID()) FOR [id] --ON [PRIMARY] declare @ind int, @rowCountKod int, @rowCountCharact int declare @firm varchar(50) = '', @model varchar(1000) = '', @type varchar(150) = '', @kodG int, @price decimal(9,2) = 0, @stockIndex int = 0, @barCode varchar(50) = '' declare @class1 varchar(150) = null, @class2 varchar(150) = null, @class3 varchar(150) = null, @class4 varchar(150) = null, @class5 varchar(150) = null, @class6 varchar(150) = null declare @typeattribinfo1 varchar(250) = '', @txtValue1 varchar(8000) = '', @typeattribinfo2 varchar(250) = '', @txtValue2 varchar(8000) = '', @typeattribinfo3 varchar(250) = '', @txtValue3 varchar(8000) = '', @typeattribinfo4 varchar(250) = '', @txtValue4 varchar(8000) = '', @typeattribinfo5 varchar(250) = '', @txtValue5 varchar(8000) = '' --insert into #tmpTabGoodsJSON (stringJSON) values ('[') select @firm = g.firmsh, @model = replace(replace(isnull(g.name,''),'"',''''),'\','\\'), @type = replace(replace(isnull(g.SType,''),'"',''''),'\','\\'), @kodG = KodG, @price = isnull(p.price, 0 ), @stockIndex = isnull(s.stockIndex, 0), @barCode = isnull(g.barCode, '') FROM vGoods AS g LEFT OUTER JOIN vPrices AS p ON g.kod = p.kod LEFT OUTER JOIN vStocks AS s ON g.kod = s.kod WHERE g.kod = @kod select @class1 = cl1, @class2 = replace(replace(isnull(cl2,''),'"',''''),'\','\\'), @class3 = replace(replace(isnull(cl3,''),'"',''''),'\','\\'), @class4 = replace(replace(isnull(cl4,''),'"',''''),'\','\\'), @class5 = replace(replace(isnull(cl5,''),'"',''''),'\','\\'), @class6 = replace(replace(isnull(cl6,''),'"',''''),'\','\\') from READ_CARDS.cardsnew.dbo.vClasses6 where KodG = @kodG select @typeattribinfo1 = typeattribinfo from READ_CARDS.cardsnew.dbo.sysattribinfo where kindattrib = 1 and typeattrib = 1 select @typeattribinfo2 = typeattribinfo from READ_CARDS.cardsnew.dbo.sysattribinfo where kindattrib = 1 and typeattrib = 6 select @typeattribinfo3 = typeattribinfo from READ_CARDS.cardsnew.dbo.sysattribinfo where kindattrib = 1 and typeattrib = 7 select @typeattribinfo4 = typeattribinfo from READ_CARDS.cardsnew.dbo.sysattribinfo where kindattrib = 1 and typeattrib = 8 select @txtValue1 = replace(replace(isnull(ga.textattrib,''),'"',''''),'\','\\') from READ_CARDS.cardsnew.dbo.goodsattrib ga join READ_CARDS.cardsnew.dbo.sysattribinfo ai on ga.kind=ai.kindattrib and ga.typeattrib=ai.typeattrib where ga.kod = @kod and ga.textattrib is not null and ga.textattrib != '' and ai.kindattrib = 1 and ga.typeattrib = 1 select @txtValue2 = replace(replace(isnull(ga.textattrib,''),'"',''''),'\','\\') from READ_CARDS.cardsnew.dbo.goodsattrib ga join READ_CARDS.cardsnew.dbo.sysattribinfo ai on ga.kind=ai.kindattrib and ga.typeattrib=ai.typeattrib where ga.kod = @kod and ga.textattrib is not null and ga.textattrib != '' and ai.kindattrib = 1 and ga.typeattrib = 6 select @txtValue3 = replace(replace(isnull(ga.textattrib,''),'"',''''),'\','\\') from READ_CARDS.cardsnew.dbo.goodsattrib ga join READ_CARDS.cardsnew.dbo.sysattribinfo ai on ga.kind=ai.kindattrib and ga.typeattrib=ai.typeattrib where ga.kod = @kod and ga.textattrib is not null and ga.textattrib != '' and ai.kindattrib = 1 and ga.typeattrib = 7 select @txtValue4 = replace(replace(isnull(ga.textattrib,''),'"',''''),'\','\\') from READ_CARDS.cardsnew.dbo.goodsattrib ga join READ_CARDS.cardsnew.dbo.sysattribinfo ai on ga.kind=ai.kindattrib and ga.typeattrib=ai.typeattrib where ga.kod = @kod and ga.textattrib is not null and ga.textattrib != '' and ai.kindattrib = 1 and ga.typeattrib = 8 -- VVS 04.06.2024 set @price = 0 -- VVS 04.06.2024 insert into #tmpTabGoodsJSON (stringJSON) values ('{') --insert into #tmpTabGoodsJSON (stringJSON) values ('"НомерСтроки": ' + cast(@ind as varchar(5)) + ' ,') insert into #tmpTabGoodsJSON (stringJSON) values ('"КодТовара": ' + cast(@kod as varchar(6)) + ' ,') insert into #tmpTabGoodsJSON (stringJSON) values ('"Фирма": "' + isnull(@firm,'') + '" ,') insert into #tmpTabGoodsJSON (stringJSON) values ('"Модель": "' + isnull(@model,'') + '" ,') insert into #tmpTabGoodsJSON (stringJSON) values ('"Тип": "' + isnull(@type,'') + '" ,') insert into #tmpTabGoodsJSON (stringJSON) values ('"Цена": "' + isnull(cast(@price as varchar(10)),'') + '" ,') insert into #tmpTabGoodsJSON (stringJSON) values ('"Валюта": " руб." ,') insert into #tmpTabGoodsJSON (stringJSON) values ('"ШтрихКод": "' + @barCode + '",') insert into #tmpTabGoodsJSON (stringJSON) values ('"НаличиеНаСкладе": "' + case when isnull(@stockIndex,0) = 0 then 'нет' when isnull(@stockIndex,0) = 1 then 'мало' when isnull(@stockIndex,0) = 2 then 'достаточно' when isnull(@stockIndex,0) = 3 then 'много' else 'нет' end + '" ,') insert into #tmpTabGoodsJSON (stringJSON) values ('"КлассификацияТовара": {') --if LEN(@class1) > 1 begin insert into #tmpTabGoodsJSON (stringJSON) -- values ('<Класс1>' + @class1 + '</Класс1>') end insert into #tmpTabGoodsJSON (stringJSON) values ('"Класс1": "' + isnull(@class2,'') + '" ,') insert into #tmpTabGoodsJSON (stringJSON) values ('"Класс2": "' + isnull(@class3,'') + '" ,') insert into #tmpTabGoodsJSON (stringJSON) values ('"Класс3": "' + isnull(@class4,'') + '" ,') insert into #tmpTabGoodsJSON (stringJSON) values ('"Класс4": "' + isnull(@class5,'') + '" ,') insert into #tmpTabGoodsJSON (stringJSON) values ('"Класс5": "' + isnull(@class6,'') + '"') insert into #tmpTabGoodsJSON (stringJSON) values ('},') select @rowCountCharact = COUNT(*) from vGoods gg left join READ_CARDS.cardsnew.dbo.CharactDictionary dict on gg.KodG = dict.KodG left join READ_CARDS.cardsnew.dbo.CharactTable g on g.kod = gg.kod and dict.idcd = g.idcd left join READ_CARDS.cardsnew.dbo.CharactName n on dict.idcn = n.idcn left join READ_CARDS.cardsnew.dbo.units ed on dict.idu = ed.idunits where gg.kod=@kod and g.valuech is not null insert into #tmpTabGoodsJSON (stringJSON) values ('"' + @typeattribinfo1 + '": "' + isnull(@txtValue1,'') + '",') insert into #tmpTabGoodsJSON (stringJSON) values ('"' + @typeattribinfo2 + '": "' + isnull(@txtValue2,'') + '",') insert into #tmpTabGoodsJSON (stringJSON) values ('"' + @typeattribinfo3 + '": "' + isnull(@txtValue3,'') + '",') insert into #tmpTabGoodsJSON (stringJSON) values ('"' + @typeattribinfo4 + '": "' + isnull(@txtValue4,'') + '",') insert into #tmpTabGoodsJSON (stringJSON) values ('"ТехническиеХарактеристики":') insert into #tmpTabGoodsJSON (stringJSON) values ('[') insert into #tmpTabGoodsJSON (stringJSON) select CASE WHEN (ROW_NUMBER() OVER(ORDER BY dict.ord ASC)) < @rowCountCharact THEN '{ ' + ' "Наименование": "' + replace(replace(rtrim(isnull(n.namecd,'')),'"',''''),'\','\\') + '", ' + ' "Значение": "' + replace(replace(rtrim(isnull(g.valuech,'')),'"',''''),'\','\\') + '", ' + ' "ЕдиницаИзмерения": "' + replace(replace(rtrim(isnull(ed.nameunits,'')),'"',''''),'\','\\') + '"' + ' },' ELSE '{ ' + ' "Наименование": "' + replace(replace(rtrim(isnull(n.namecd,'')),'"',''''),'\','\\') + '", ' + ' "Значение": "' + replace(replace(rtrim(isnull(g.valuech,'')),'"',''''),'\','\\') + '", ' + ' "ЕдиницаИзмерения": "' + replace(replace(rtrim(isnull(ed.nameunits,'')),'"',''''),'\','\\') + '"' + ' }' END from vGoods gg left join READ_CARDS.cardsnew.dbo.CharactDictionary dict on gg.KodG = dict.KodG left join READ_CARDS.cardsnew.dbo.CharactTable g on g.kod = gg.kod and dict.idcd = g.idcd left join READ_CARDS.cardsnew.dbo.CharactName n on dict.idcn = n.idcn left join READ_CARDS.cardsnew.dbo.units ed on dict.idu = ed.idunits where gg.kod=@kod and g.valuech is not null order by dict.ord insert into #tmpTabGoodsJSON (stringJSON) values ('],') select @rowCountCharact = COUNT(*) from READ_CARDS.cardsnew.dbo.GoodsAttrib WHERE (kod = @kod) AND (kind = 2 OR kind = 4) AND (LEN(textAttrib) > 1) insert into #tmpTabGoodsJSON (stringJSON) values ('"Файлы":') insert into #tmpTabGoodsJSON (stringJSON) values ('[') insert into #tmpTabGoodsJSON (stringJSON) SELECT CASE WHEN (ROW_NUMBER() OVER(ORDER BY a.kind, a.typeAttrib, a.textAttrib)) < @rowCountCharact THEN '{ "Тип": "' + s.typeAttribInfo + '", "ПолныйПуть": "' + replace(a.textAttrib,'\','\\') + '" },' ELSE '{ "Тип": "' + s.typeAttribInfo + '", "ПолныйПуть": "' + replace(a.textAttrib,'\','\\') + '" }' END FROM READ_CARDS.cardsnew.dbo.GoodsAttrib AS a INNER JOIN READ_CARDS.cardsnew.dbo.sysAttribInfo AS s ON a.kind = s.kindAttrib AND a.typeAttrib = s.typeAttrib WHERE (a.kod = @kod) AND (a.kind = 2 OR a.kind = 4) AND (LEN(a.textAttrib) > 1) insert into #tmpTabGoodsJSON (stringJSON) values (']') insert into #tmpTabGoodsJSON (stringJSON) values ('}') --insert into #tmpTabGoodsJSON (stringJSON) values (']') select id, stringJSON from #tmpTabGoodsJSON order by id drop table #tmpTabGoodsJSON END GO