CY5AT4BN

1/*
2Deployment script for RwEdit
3
4This code was generated by a tool.
5Changes to this file may cause incorrect behavior and will be lost if
6the code is regenerated.
7*/
8
9GO
10SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
11
12SET NUMERIC_ROUNDABORT OFF;
13
14
15GO
16:setvar DatabaseName "RwEdit"
17:setvar DefaultFilePrefix "RwEdit"
18:setvar DefaultDataPath "k:\MSSQL\Data\"
19:setvar DefaultLogPath "l:\MSSQL\Data\"
20
21GO
22:on error exit
23GO
24/*
25Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
26To re-enable the script after enabling SQLCMD mode, execute the following:
27SET NOEXEC OFF; 
28*/
29:setvar __IsSqlCmdEnabled "True"
30GO
31IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
32    BEGIN
33        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
34        SET NOEXEC ON;
35    END
36
37
38GO
39USE [$(DatabaseName)];
40
41
42GO
43/*
44The type for column SubjectEMail in table [dbo].[MailQueueEdit] is currently  NVARCHAR (255) NOT NULL but is being changed to  VARCHAR (255) NOT NULL. Data loss could occur.
45*/
46
47IF EXISTS (select top 1 1 from [dbo].[MailQueueEdit])
48    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
49
50GO
51PRINT N'Dropping [svc.WW.Riki]...';
52
53
54GO
55DROP USER [svc.WW.Riki];
56
57
58GO
59PRINT N'Creating [svc.WW.Riki]...';
60
61
62GO
63CREATE USER [svc.WW.Riki] WITHOUT LOGIN;
64
65
66GO
67REVOKE CONNECT TO [svc.WW.Riki];
68
69
70GO
71PRINT N'Altering [dbo].[MailQueueEdit]...';
72
73
74GO
75ALTER TABLE [dbo].[MailQueueEdit] ALTER COLUMN [SubjectEMail] VARCHAR (255) NOT NULL;
76
77
78GO
79PRINT N'Altering [dbo].[Users]...';
80
81
82GO
83ALTER TABLE [dbo].[Users]
84    ADD [UserPoints] DECIMAL (18, 2) DEFAULT 0 NOT NULL;
85
86
87GO
88PRINT N'Altering [dbo].[asContentEditor]...';
89
90
91GO
92ALTER PROCEDURE [dbo].[asContentEditor](
93		@Language				NVarchar(max) = NULL,		-- + language =  'ru' / 'en' / ...
94		@ThisLng				NVarchar(max) = NULL,		-- + SHOW PAGE ON THIS LNG =  'ru' / 'en' / ...
95		@UserId					NVarchar(max) = NULL,		-- + User Id
96		@Command				NVarchar(max) = NULL,		-- + FunctionName (CustomerPage, GetAdContent, Find, )
97
98		@BusinessId				NVarchar(max) = NULL,
99		@BusinessName			NVarchar(max) = NULL,
100		@BusinessAddress		NVarchar(max) = NULL,
101		@BusinessContact		NVarchar(max) = NULL,
102		@BusinessImageId		NVarchar(max) = NULL,
103
104		@OwnerId				NVarchar(max) = NULL,
105		@BusinessUrl			NVarchar(max) = NULL,
106		@Latitude				NVarchar(max) = NULL,
107		@Longitude				NVarchar(max) = NULL,
108		@MapZoom				NVarchar(max) = NULL,
109
110		@BusinessPageId			NVarchar(max) = NULL,
111		@PageDescription		NVarchar(max) = NULL,
112		@PageAddress			NVarchar(max) = NULL,
113		@PageName				NVarchar(max) = NULL,
114		@PageTemplateId			NVarchar(max) = NULL,
115		@LanguageId				NVarchar(max) = NULL,
116		@LanguageName			NVarchar(max) = NULL,
117		@PageStatusId			NVarchar(max) = NULL,
118		@PageAuditStatusId		NVarchar(max) = NULL,
119		@PageTypeId				NVarchar(max) = NULL,
120		@PageUrl				NVarchar(max) = NULL,
121
122		@PageHTMLLeft			NVarchar(max) = NULL,
123		@PageHTMLRight			NVarchar(max) = NULL,
124		@PageHTMLAdv			NVarchar(max) = NULL,
125
126		@CategoriesCloud		NVarchar(max) = NULL,  -- ÒÔËÒÓÍ Í‡Ú„ÓËÈ ‰Îˇ  Business Ë ËÒÔÓθÁÛÂÚÒˇ ‰Îˇ ÒÔËÒ͇  ͇Ú„ÓËÈ  ‰Îˇ ͇Ú˚ (map)
127
128		@AccessToRoleId			NVarchar(max) = NULL,
129		@AccessToUserId			NVarchar(max) = NULL,
130		@AccessToLanguageId		NVarchar(max) = NULL,
131		@UserEMail				NVarchar(max) = NULL,
132		@ObjectType				NVarchar(max) = NULL,
133		@ObjectId				NVarchar(max) = NULL,
134		@OldAccessRoleId		NVarchar(max) = NULL,
135		@OldAccessLanguageId	NVarchar(max) = NULL,
136
137		@PageItemId				NVarchar(max) = NULL,
138		@PageItemMask			NVarchar(max) = NULL,
139        @PageItemOrderBy		NVarchar(max) = NULL,
140        @PageItemLocation		NVarchar(max) = NULL,
141		@PageItemCaption		NVarchar(max) = NULL,
142        @PageItemTemplateId		NVarchar(max) = NULL,
143        @PageItemTxt_01			NVarchar(max) = NULL,
144        @PageItemTxt_02			NVarchar(max) = NULL,
145        @PageItemTxt_03			NVarchar(max) = NULL,
146        @PageItemTxt_04			NVarchar(max) = NULL,
147        @PageItemImg_01_Id		NVarchar(max) = NULL,
148        @PageItemImg_02_Id		NVarchar(max) = NULL,
149        @PageItemImg_03_Id		NVarchar(max) = NULL,
150        @PageItemImg_04_Id		NVarchar(max) = NULL,
151
152		@OnMapRadius			NVarchar(max) = NULL,
153		@OnMapQuantity			NVarchar(max) = NULL,
154		@OnMapType				NVarchar(max) = NULL,	-- map 'GOOGLE' OR 'BING' OR ....
155
156		@AdvGetQuantity			NVarchar(max) = NULL,	-- Get  NNN banners
157		@AdvSkipQuantity		NVarchar(max) = NULL,	-- Skip NNN banners
158
159		@AdsGroupId				NVarchar(max) = NULL,
160		@AdsGroupName			NVarchar(max) = NULL,
161		@AdsRankId				NVarchar(max) = NULL,
162
163		@ResultMessage			VARCHAR(255)  = NULL OUTPUT  )
164AS
165BEGIN
166
167SET NOCOUNT ON
168SET ANSI_WARNINGS OFF
169
170-- It is command list :
171-------------------
172--	EXEC asContentEditor @Command = 'PAGE_GET_ONE', @BusinessPageId =N'40308', @UserId=N'173', @Language=N'en',@ThisLng=N''
173--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'BUSINESS_GET_BY_USER'
174--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'BUSINESS_GET_BY_ID', @BusinessId = 1
175--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_GET_BY_BUSINESS', @BusinessId = 2
176
177--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_GET_TEMPLATE'
178--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'BUSINESS_SET', @BusinessId = 123456, ***
179--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'BUSINESS_SET', @BusinessId = 0
180--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'BUSINESS_DELETE', @BusinessId = 0
181
182--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_COUNT', @BusinessId = 1, @BusinessPageId = 0
183--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_COUNT', @BusinessId = 0, @BusinessPageId = 1
184
185--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_NEW', @BusinessId = 1
186--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_SET', *****
187--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_TRANSLATE', BusinessPageId = 2, LanguageId = 8
188--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_TO_EDIT_MODE', @BusinessPageId = 1
189--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_SET_CONTENT', *****
190--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_ADD', *****
191
192--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_GET_ITEM', @BusinessPageId = 2
193--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'ITEM_SET', *****
194--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'ITEM_ADD', *****
195--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'ITEM_DEL', *****
196
197--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'ITEM_GET_TEMPLATE'
198--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'IMAGE_GET_BY_BUSINESS' , @BusinessId=2
199
200--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'ACCESS_MANAGEMENT_GET_JSON', @ObjectType = 'OB', @ObjectId = 8
201-- 	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'USER_BY_EMAIL_GET_JSON',  @UserEMail = 'mail@mail.ru', @ObjectType = 'OB', @ObjectId = 8
202--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'ACCESS_USER_ADD', *****
203--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'ACCESS_USER_SET', *****
204--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'ACCESS_USER_DEL', *****
205
206--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'ADSGROUP_CLONE', @Businessid= 12345, @AdsGroupId = 1
207--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'ADSGROUP_DELETE', @Businessid= 12345, @AdsGroupId = 1
208--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'ADSGROUP_ACTIVATE', @Businessid= 12345, @AdsGroupId = 1
209--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'ADSGROUP_DEACTIVATE', @Businessid= 12345, @AdsGroupId = 1
210
211--------------------
212
213Declare @IsEmpty	tinyint = 0x00		--	0x00	- 0		- Undefined
214Declare @IsMaster	tinyint = 0x01		--	0x01	- 1		- Is Master block
215Declare @IsPage		tinyint = 0x02		--	0x02	- 2		- Is Page block
216Declare @IsBanner	tinyint = 0x04		--	0x04	- 4		- Is Banner block
217Declare @IsEmail	tinyint = 0x08		--	0x08	- 8		- Is Email Block
218Declare @Is16		tinyint = 0x10		--	0x10	- 16	- === not used
219Declare @Is32		tinyint = 0x20		--	0x20	- 32	- === not used
220Declare @Is64		tinyint = 0x40		--	0x40	- 64	- === not used
221Declare @IsHidden	tinyint = 0x80		--	0x80	- 128	- Hidden
222
223
224Declare @Sign_ObjectTypeId	bigint					-- ƒÎˇ ‰‡Î¸ÌÂȯÂÈ ‡·ÓÚ˚ Ò Ô‡‚‡ÏË ‰ÓÒÚÛÔ‡ Í ·ËÁÌÂÒÛ ÓÔ‰ÂΡÂÏ
225
226	Select @Sign_ObjectTypeId = DataReferenceId
227	from fn_DataReference( 'AccessObjects', 'en', DEFAULT)
228	where DataReferenceCode = 'OB'
229
230Declare @Sign_RoleOwner							nvarchar(max)	= 'OB:O:Owner'
231
232Declare @Sign_RoleBusinessManager				nvarchar(max)	= 'OB:M:Manager'			-- »‰ÂÌÚËÙË͇ÚÓ ‚·‰Âθˆ‡ Ó·˙ÂÍÚ‡ ‚ ÒÔ‡‚Ó˜ÌËÍ ÓÎÂÈ
233Declare @Sign_RoleTranslatorPage				nvarchar(max)	= 'OB:T:TranslatorPage'			-- œÂ‚Ӊ˜ËÍ ÒÚ‡Ìˈ
234Declare @Sign_RoleTranslator					nvarchar(max)	= 'DR:Translator'				-- œÂ‚Ӊ˜ËÍ ÒÔ‡‚Ó˜ÌËÍÓ‚
235Declare @Sign_Manager							nvarchar(max)	= 'DR:Manager'
236
237Declare @Sign_StatusHidden						nvarchar(max)	= 'S:Hidden'					-- —Ú‡ÚÛÒ Ì‡Á̇˜‡ÂÏ˚È ÒÍ˚Ú˚Ï ·‡ÌÂ‡Ï
238Declare @Sign_StatusNew							nvarchar(max)	= 'M:New'						-- —Ú‡ÚÛÒ Ì‡Á̇˜‡ÂÏ˚È MASTER ÒÚ‡Ìˈ ÔË ÒÓÁ‰‡ÌËË
239Declare @Sign_StatusEdit						nvarchar(max)	= 'T:Edit'						-- —Ú‡ÚÛÒ Ì‡Á̇˜‡ÂÏ˚È DRAFT  ÒÚ‡Ìˈ ÔË ÒÓÁ‰‡ÌËË
240Declare @Sign_StatusEnabled						nvarchar(max)	= 'M:Enabled'					-- —Ú‡ÚÛÒ Ì‡Á̇˜‡ÂÏ˚È MASTER ÒÚ‡Ìˈ : ÔÓ͇Á ̇ RIKI ‡Á¯ÂÌ
241Declare @Sign_StatusDisabled					nvarchar(max)	= 'M:Disabled'					-- —Ú‡ÚÛÒ Ì‡Á̇˜‡ÂÏ˚È MASTER ÒÚ‡Ìˈ : ÔÓ͇Á ̇ RIKI Á‡Ô¢ÂÌ
242Declare @Sign_StatusApproveRequest				nvarchar(max)	= 'S:ApproveRequest'			-- —Ú‡ÚÛÒ Ì‡Á̇˜‡ÂÏ˚È DRAFT  ÒÚ‡Ìˈ - Á‡ÔÓÒ Ì‡ ÔÓ‚ÂÍÛ Í ÒÎÛÊ·Â ÔÓ‰‰ÂÊÍË
243Declare @Sign_StatusApproveRequestPublish		nvarchar(max)	= 'S:ApproveRequestPublish'		-- —Ú‡ÚÛÒ Ì‡Á̇˜‡ÂÏ˚È DRAFT  ÒÚ‡Ìˈ - Á‡ÔÓÒ Ì‡ ÔÓ‚ÂÍÛ Ë ‡‚ÚÓχÚ˘ÂÒÍÛ˛ ÔÛ·ÎË͇ˆË˛
244Declare @Sign_StatusRemoveRequest				nvarchar(max)	= 'O:RemoveRequest'				-- —Ú‡ÚÛÒ Ì‡Á̇˜‡ÂÏ˚È MASTER ÒÚ‡Ìˈ - Á‡ÔÓÒ Ì‡ Û‰‡ÎÂÌËÂ
245Declare @Sign_StatusRemoveRequestConfirmed		nvarchar(max)	= 'R:RemoveRequestConfirmed'	-- —Ú‡ÚÛÒ Ì‡Á̇˜‡ÂÏ˚È MASTER ÒÚ‡Ìˈ - Á‡ÔÓÒ Ì‡ Û‰‡ÎÂÌË Œ ŒÕ◊¿“≈À‹ÕŒ !!
246Declare @Sign_StatusRemoveCopy					nvarchar(max)	= 'R:RemoveCopy'				-- —Ú‡ÚÛÒ Ì‡Á̇˜‡ÂÏ˚È DRAFT  ÒÚ‡Ìˈ - Á‡ÔÓÒ Ì‡ Û‰‡ÎÂÌË DRAFT ( & CLEAN ÂÒÎË Û Ì ÒÚ‡ÚÛÒ = NEW)
247Declare @Sign_StatusRobCommitRequest			nvarchar(max)	= 'R:CommitRequest'
248
249Declare @Sign_AuditStatusAllowed				nvarchar(max)	= 'S:AuditAllowed'				-- —Ú‡ÚÛÒ ¿”ƒ»“¿ page\mail\banner : œÓ͇Á ‡Á¯ÂÌ
250Declare @Sign_AuditStatusBanned					nvarchar(max)	= 'S:AuditBanned'				-- —Ú‡ÚÛÒ ¿”ƒ»“¿ page\mail\banner : œÓ͇Á Á‡Ô¢ÂÌ
251
252Declare @Sign_AdsActive							nvarchar(max)	= 'ADS:Active'					-- Ads Group Active
253Declare @Sign_AdsDeletePending					nvarchar(max)	= 'ADS:DeletePending'			-- Ads Group pending delete
254
255Declare @PageStatus_HiddenId					bigint
256Declare @PageStatus_CommitRequest				bigint
257Declare @PageStatus_NewId						bigint
258Declare @PageStatus_EditId						bigint
259Declare @PageStatus_EnabledId					bigint
260Declare @PageStatus_DisabledId					bigint
261Declare @PageStatus_ApproveRequestPublishId		bigint
262Declare @PageStatus_ApproveRequestId			bigint
263Declare @PageStatus_RemoveRequestConfirmedId	bigint
264Declare @PageStatus_RemoveRequestId				bigint
265Declare @PageStatus_RemoveCopyId				bigint
266
267Declare @PageStatus_AuditAllowedId				bigint
268Declare @PageStatus_AuditBannedId				bigint
269
270Declare @Status_AdsActiveId						bigint
271Declare @Status_AdsDeletePendingId				bigint
272
273Declare @PageType_Page							nvarchar(max)	= 'PAGE'					-- —Ú‡Ìˈ˚
274Declare @PageType_Banner						nvarchar(max)	= 'BANNER'					-- ¡‡ÌÂ˚
275Declare @PageType_EMail							nvarchar(max)	= 'EMAIL'					-- œÓ˜Ú‡
276
277Declare @PageType_PageId						bigint										-- —Ú‡Ìˈ˚ = 10000
278Declare @PageType_BannerId						bigint										-- ¡‡ÌÂ˚	= 10001
279Declare @PageType_EMailId						bigint										-- œÓ˜Ú‡	= 10002
280
281Declare @Old_PageStatusId			bigint
282
283Declare @Access						varchar(255)
284
285Declare @PageHTMLTemplate			nvarchar(max)								-- Page template HTML
286Declare @RootPageId					bigint
287Declare @DraftPageId				bigint
288Declare @ItemParentPageId			bigint
289Declare @ItemOwnerPageId			bigint
290declare @mItemOrderBy				int
291declare @ItemMaskOld				tinyint
292declare @PageMailId					bigint		= 0
293declare @PageBannerId				bigint
294Declare @ItemBusinessPageId			bigint
295Declare @ItemPageBannerId			bigint
296Declare @itemParentBannerId			bigint
297Declare @RebuildPageBannerId		bigint
298
299Declare @CleanPageId				bigint
300Declare @AdsGroupNewId				bigint
301
302	--
303	--	≈ÒÎË ˝ÚÓ ‡·ÓÚ‡ÂÏ ÒÓ ÒÚ‡Ìˈ‡ÏË - Ò‡ÁÛ ÔÓÎÛ˜ËÏ Ô‡‚‡.
304	--	ƒÎˇ ÒÔ‡‚Ó˜ÌËÍÓ‚ -  ·Û‰ÂÏ Ëı ÓÔ‰ÂΡڸ ÓÚ‰ÂθÌÓ.. ÔÓ ÏÂÒÚÛ ËÒÔÓθÁÓ‚‡Ìˡ
305	--
306	IF ISNULL( @ObjectType, '' ) != 'DR'
307		BEGIN
308			-- Get grant access to Business
309			Select @Access = dbo.fn_CheckBusinessPageAccess( @UserId, @BusinessId, @ObjectId, @BusinessPageId, ISNULL( @AccessToLanguageId, @LanguageId ) )
310		END
311
312Declare @BusinessImageName		nvarchar(1024)
313
314DECLARE @ResultHTML				nvarchar(max)
315DECLARE @ResultTEMPLATE			nvarchar(max)
316
317	SET @ResultMessage  = ''
318	SET @ResultHTML		= NULL
319	SET @ResultTEMPLATE = NULL
320
321Declare @NewObjectId			bigint
322Declare @NewPageId				bigint
323Declare @NewItemId				bigint
324Declare @NewObjectTypeId		bigint
325Declare @NewRoleId				bigint
326
327Declare @mRowCount				int
328			SET @mRowCount = 0
329
330Declare @ResultAction			nvarchar(max)
331Declare @mBusinessId			bigint
332Declare @mObjectTypeId			bigint
333
334Declare @ParentPageId			bigint
335
336Declare @NewBusinessPageId		bigint
337Declare @NewChildBusinessPageId	bigint
338Declare @wrkParentId			bigint
339
340Declare @RoleDataReference_Edit bigint
341Declare @Sign_Role_DataReference_Edit nvarchar(max)
342
343SET @Sign_Role_DataReference_Edit = 'DATAREFERENCE_EDITOR'
344
345	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'BUSINESS_GET_BY_USER'
346	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'BUSINESS_GET_BY_ID', @BusinessId = 1
347	--
348	--	EXEC asContentEditor @UserId = 10, @Language = 'ru', @Command = 'BUSINESS_GET_BY_USER'
349	--	EXEC asContentEditor @UserId = 10, @Language = 'ru', @Command = 'BUSINESS_GET_BY_ID', @BusinessId = 84
350	--
351	--	œÓθÁÓ‚‡ÚÂβ ÓÚÓ·‡Ê‡˛ÚÒˇ ÚÓθÍÓ Ú ·ËÁÌÂÒ˚, ‰ÓÒÚÛÔ Í ÍÓÚÓ˚Ï
352	--	ÓÔ‰ÂÎÂÌ Ô‡‚‡ÏË ÔÓθÁÓ‚‡ÚÂΡ (ÎË·Ó Í‡Í ‚·‰ÂΈ, ÎË·Ó ÔÓÎÛ˜ÂÌÌ˚ ÓÚ ‚·‰Âθˆ‡ ‡Á¯ÂÌˡ)
353	--
354
355	if @Command	in ( 'BUSINESS_GET_BY_USER', 'BUSINESS_GET_BY_ID' )
356		begin
357
358			SELECT DISTINCT
359					BUS.BusinessId			,
360					BUS.BusinessName		,
361					BUS.BusinessAddress		,
362					OwnerId					= BUS.CustomerId		,
363					OwnerName				= ISNULL( USR.UserFirstName, '' ) + ' ' + ISNULL( USR.UserLastName, '' ),
364					BUS.BusinessURL			,
365					BUS.Latitude			,
366					BUS.Longitude			,
367					BUS.MapZoom				,
368					BUS.BusinessContact		,
369					BUS.BusinessImageId		,
370					BUS.BusinessImageName	,
371					AccessList				= 'OB'					,				-- Type = Business-Object
372					Invite                  = ISNULL(SA.Status,''),
373					Actions					= ISNULL(REF.DataReferenceCode, '' )
374			into #tmp
375			FROM UsersAccess UA
376				JOIN Business			BUS ON BUS.BusinessId			= UA.ObjectId
377				JOIN Users				USR ON USR.UserId				= BUS.CustomerId
378				JOIN DataReference		 ST ON ST.DataReferenceID		= UA.RoleId
379				LEFT JOIN SAInvite			 SA ON SA.UserId                = @UserId
380										   AND SA.BusinessId            = UA.ObjectId
381				JOIN DataReference		 AR ON AR.LanguageId			= 1
382										   AND AR.DataReferenceOwner    = ST.DataReferenceId
383				JOIN DataReference		REF	ON REF.DataReferenceId		= CONVERT( bigint, AR.DataReferenceName )
384			WHERE UA.UserId			= @UserId
385			  AND UA.ObjectTypeId	= @Sign_ObjectTypeId
386			  AND UA.ObjectId		= ISNULL( @BusinessId, UA.ObjectId )
387
388
389			SELECT *
390			FROM (
391					SELECT  distinct
392							BusinessId			,
393							BusinessName		,
394							BusinessAddress		,
395							OwnerId				,
396							OwnerName			,
397							BusinessUrl			,
398							Latitude			,
399							Longitude			,
400							MapZoom				,
401							BusinessContact		,
402							BusinessImageId		,
403							BusinessImageName	,
404							AccessList			,
405							Invite				,
406							ActionList			= '-' +
407												  (	SELECT	Actions + '-'
408													FROM (	SELECT	DISTINCT
409																	Actions
410															from #tmp
411															WHERE TTT.BusinessId = BusinessId
412															) QR
413													order by Actions
414													FOR XML PATH ('')
415												  )
416					from #tmp TTT
417				) QR
418			WHERE QR.ActionList IS NOT NULL
419			ORDER BY CASE WHEN QR.OwnerId = @UserId THEN 0 ELSE 1 END,
420					 QR.BusinessName
421
422			drop table #tmp
423
424			RETURN
425	end
426	--	EXEC asContentEditor @UserId = 168, @Command = 'SA_GET_BY_USER'
427
428	if @Command	in ( 'SA_GET_BY_USER' )
429		begin
430		declare @ExpirationDate int = Convert(int, dbo.fn_DataReference_Value( 'SystemConfig', 'CountDayForSalesAgentExpiration'))
431		SELECT
432			   ClientId = USR.UserId,
433			   SA.UserEmail,
434		       OwnerName = ISNULL( USR.UserFirstName, '' ) + ' ' + ISNULL( USR.UserLastName, '' ),
435		       OpenDate = USR.UserRegistered,
436			   ExpirationDate =  Dateadd(yy, @ExpirationDate, USR.UserRegistered),
437               BusinessNumber = (SELECT COUNT(*) FROM Business WHERE CustomerId = USR.UserId),
438			   PagesNumber = (SELECT Num = COUNT(BSP.BusinessId) FROM Business BSN Join BusinessPage BSP on BSP.BusinessId = BSN.BusinessId Where BSN.CustomerId = USR.UserId ),
439			   SA.ComissionTotal
440		From SAInvite SA
441		Join Users USR On USR.UserEMail = SA.UserEmail
442
443		 Where SA.UserId = @UserId AND SA.Status = 'Accepted'
444
445
446
447
448			RETURN
449	end
450
451	--
452	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_PREVIEW_ACCES', @BusinessPageId = -999		-- false
453	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_PREVIEW_ACCES', @BusinessPageId = 2898		-- true
454	--
455	--	ÃÓÊÌÓ ÎË ÚÂÍÛ˘ÂÏÛ ÔÓθÁÓ‚‡ÚÂβ ÔÓ͇Á‡Ú¸ PEVIEW - ÚÂÍÛ˘Û˛ ÒÚ‡ÌËˆÛ ?
456	--		ƒ¿, ÂÒÎË Û ÌÂ„Ó ÒÛ˘ÂÒÚ‚Û˛Ú ıÓÚˇ ·˚ ͇ÍË ÌË·Û‰¸ Ô‡‚‡ Í ‰‡ÌÌÓÏÛ ·ËÁÌÂÒÛ.
457	--
458	if @Command	= 'PAGE_PREVIEW_ACCES'
459		begin
460
461			Select top 1
462					AccessAllowed = 'YES'
463			from BusinessPage BP
464				JOIN UsersAccess UA ON UA.UserId		= @UserId
465									AND UA.ObjectTypeId	= @Sign_ObjectTypeId		-- 'OB'
466									AND UA.ObjectId		= BP.BusinessId
467			WHERE BP.BusinessPageId = @BusinessPageId
468
469			-- One row cursor - Access ALLOWED   !
470			-- Empty   cursor - Access RESTRICED !
471			return
472		end
473
474	--	EXEC asContentEditor @UserId = 170, @Language = 'ru', @Command = 'PAGE_GET_ONE', @BusinessPageId= 20099		-- true
475	--
476	--	‘ÓÏËÛÂÏ ‚ÂÏÂÌÌÛ˛ Ú‡·ÎˈÛ, <<	  @PAGE_ACCESS_Pages   >> ‚ ÍÓÚÓÓÈ ÒÓı‡ÌˇÂÏ ËÌÙÓχˆË˛ Ó
477	--	ÒÚ‡Ìˈ‡ı Ë Ô‡‚‡ı ‰ÓÒÚÛÔ‡ ÚÂÍÛ¯Â„Ó ÔÓθÁÓ‚‡ÚÂΡ Í ÌËÏ.
478	--
479	--	›Ú‡ Ú‡·Îˈ‡ ·Û‰ÂÚ ËÒÔÓθÁÓ‚‡Ú¸Òˇ ‰Îˇ ÌÂÒÍÓθÍËı ‡ÁÌ˚ı ÍÓχ̉,
480	--	ÌËÊ ÔÓ ÚÂÍÒÚÛ Ôӈ‰Û˚.
481	--
482	if @Command	IN ( 'PAGE_GET_BY_BUSINESS', 'PAGE_GET_ONE' )
483		begin
484
485			-- 0. ŒÔ‰ÂΡÂÏ ¡ËÁÌÂÒ (ÂÒÎË Á‡‰‡Ì‡ ÚÓθÍÓ ÒÚ‡Ìˈ‡)
486			if @BusinessId IS NULL
487				BEGIN
488					Select @BusinessId = BusinessId
489					FROm BusinessPage
490					WHERE BusinessPageId = @BusinessPageId
491				END
492
493			Declare @ActionList TABLE ( LanguageId bigint			null,
494										ActionCode varchar(128)
495									  )
496
497			--
498			--	‘ÓÏËÛÂÏ Ú‡·ÎËˆÛ ‰ÓÒÚÛÔÓ‚ (ActionCode) ÔÓθÁÓ‚‡ÚÂΡ Í Á‡‰‡ÌÌÓÏÛ ·ËÁÌÂÒÛ,
499			--	Ò Û˜ÂÚÓÏ ‡Á¯ÂÌÌ˚ı ‰Îˇ ÌÂ„Ó ˇÁ˚ÍÓ‚.
500			--
501			Insert into @ActionList
502			Select	DISTINCT
503					UA.LanguageId,
504					ActionCode	= AA.DataReferenceCode
505			FROM UsersAccess	 UA
506				JOIN DataReference	AR
507										ON AR.DataReferenceClass = 'AccessRole'
508									   AND AR.LanguageId		 = 1
509									   AND AR.DataReferenceOwner IS NOT NULL
510									   AND AR.DataReferenceOwner = Convert( varchar(15),UA.RoleId)
511				JOIN fn_DataReference( 'AccessAction', 'en', DEFAULT) AA
512										ON AA.DataReferenceMasterId = Convert( bigint, AR.DataReferenceName )
513			WHERE UA.UserId			= @UserId
514				AND UA.ObjectTypeId	= @Sign_ObjectTypeId
515				AND UA.ObjectId		= @BusinessId
516
517
518			Select @PageType_PageId = DataReferenceId
519			FROM fn_dataReference( 'adPageType'  , 'en', DEFAULT ) PGT
520			WHERE DataReferenceCode	= @PageType_Page
521
522			--
523			-- 1. —ÚÓËÏ ÒÔËÒÓÍ ÒÚ‡Ìˈ ‚˚·‡ÌÌÓ„Ó Ó·˙ÂÍÚ‡ ‰ÓÒÚÛÔÌ˚ı ÚÂÍÛ˘ÂÏÛ ÔÓθÁÓ‚‡ÚÂΡ.
524			--	 Ë ÔÓ‰·Ë‡ÂÏ Í ÌËÏ Ô‡Û(CHILD) - ÂÒÎË Ú‡Í‡ˇ ÂÒÚ¸
525			--
526			Declare @PAGE_ACCESS_Pages TABLE (	BusinessId			bigint			,
527												BusinessPageId		bigint			,
528												PageLanguageId		bigint			,
529												PageStatusId		bigint			,
530												ChildBusinessPageId	bigint			,
531												ChildPageStatusId	bigint			,
532												ActionList			nvarchar(max)	,
533												MasterPageReadOnly	int				NULL,		-- 0 : Read & Write /// 1: Read only !
534												ChildPageReadOnly	int				NULL		-- 0 : Read & Write /// 1: Read only !
535											)
536
537			--
538			--	œÓÎÛ˜‡ÂÏ Ô‡Û ( MASTER-CHILD ) -  Ë Ò‚ˇÁ˚‚‡ÂÏ ËÁ Ò ‡Ì ÒÙÓÏËÓ‚‡ÌÌÓÈ Ú‡·ÎˈÂÈ
539			--	ÒÓÓÚ‚ÂÒڂˡ ‡Á¯ÂÌÌ˚ı fl«€ -—œ»—Œ _ACTION, Á‡ÚÂÏ ÙËθÚÛÂÏ ÂÁÛθڇÚ
540			--	ÔÓ ÒÔËÒÍÛ ˇÁ˚ÍÓ‚-ÒÚ‡ÚÛÒÓ‚ ÒÚ‡Ìˈ, ÍÓÚÓ˚  ‰ÓÒÚÛÔÌ˚ ÔÓθÁÓ‚‡ÚÂβ. ( fn_NestedPageStatus(***) )
541			--
542
543			if @Command	IN ( 'PAGE_GET_BY_BUSINESS' )
544				BEGIN
545					--
546					--	¬ ÒÎÛ˜‡Â ÂÒÎË ˝ÚÓ "√À¿¬Õ¿fl" Á‡ÔËÒ¸ (ıÓÁˇÈ͇)
547					--
548					INSERT INTO @PAGE_ACCESS_Pages
549					Select	PARENT.BusinessId	,
550							PARENT.BusinessPageId,
551							PARENT.LanguageId	,
552							PARENT.PageStatusId	,
553							ChildBusinessPageId	= ISNULL( CHILD.BusinessPageId, 0 ),
554							ChildPageStatusId	= ISNULL( CHILD.PageStatusId  , 0 ),
555							AL.ActionList		,
556							MasterPageReadOnly	= ISNULL( MAX( CASE
557																	WHEN ALLOWED_STATUS.Access_PageStatusId = ISNULL( PARENT.PageStatusId, 0 )
558																		THEN ALLOWED_STATUS.IsReadOnly
559																	ELSE NULL END
560														 ), 1 ),
561							ChildPageReadOnly	= ISNULL( MAX( CASE
562																	WHEN ALLOWED_STATUS.Access_PageStatusId = ISNULL( CHILD.PageStatusId, 0 )
563																		THEN ALLOWED_STATUS.IsReadOnly
564																	ELSE NULL END
565														 ), 1 )
566					FROM BusinessPage PARENT
567--							JOIN fn_dataReference( 'adPageType'  , 'en', DEFAULT ) PGT
568--														ON PGT.DataReferenceCode	= @PageType_Page
569--													   AND PGT.DataReferenceId		= PARENT.PageTypeId
570							JOIN (
571										SELECT	DISTINCT
572												LanguageId	,
573												ActionList	= (	SELECT	'-' + ActionCode + '-'
574																FROM (	SELECT	DISTINCT
575																				ActionCode
576																		from @ActionList
577																		WHERE ISNULL( TMP.LanguageId, -999 ) = ISNULL( LanguageId, -999 )
578																		) QR
579																order by ActionCode
580																FOR XML PATH ('')
581															  )
582										FROM @ActionList TMP
583								 )					    AL
584														ON ISNULL( AL.LanguageId, PARENT.LanguageId ) = PARENT.LanguageId
585						LEFT JOIN BusinessPage		 CHILD
586														ON CHILD.ParentPageId = PARENT.BusinessPageId
587							 JOIN fn_NestedPageStatus( @UserID, @BusinessId )	ALLOWED_STATUS
588														ON ISNULL( ALLOWED_STATUS.LanguageId,  PARENT.LanguageId ) = PARENT.LanguageId
589													   AND ALLOWED_STATUS.Access_PageStatusId IN ( PARENT.PageStatusId, CHILD.PageStatusId )
590													   AND ALLOWED_STATUS.IsReadOnly		  = 0		-- Edit enable !
591					WHERE PARENT.BusinessId		= @BusinessId
592					  AND PARENT.ParentPageId	IS NULL
593					  AND PARENT.BusinessPageId	= COALESCE( @BusinessPageId, PARENT.BusinessPageId  )
594					  AND PARENT.PageTypeId		= @PageType_PageId
595					-- === ANSI WARNING : NULL value in GROUP BY ===
596					GROUP BY PARENT.BusinessId		,
597							 PARENT.BusinessPageId	,
598							 PARENT.LanguageId		,
599							 PARENT.PageStatusId	,
600							 ISNULL( CHILD.BusinessPageId, 0 ),
601							 ISNULL( CHILD.PageStatusId  , 0 ),
602							 AL.ActionList
603
604				END
605			ELSE		-- if @Command	IN ('PAGE_GET_ONE' )
606				BEGIN
607					--
608
609
610					--	¬ ÒÎÛ˜‡Â Á‡ÔÓÒ‡ Ó‰ËÌÓ˜ÌÓÈ Á‡ÔËÒË ( CRAN ËÎË DRAFT - Ì ‚‡ÊÌÓ)
611					--
612					INSERT INTO @PAGE_ACCESS_Pages
613					Select	DISTINCT
614							ONE.BusinessId		,
615							ONE.BusinessPageId	,
616							ONE.LanguageId		,
617							ONE.PageStatusId	,
618							ChildBusinessPageId	= ISNULL( CHILD.BusinessPageId, 0 ),
619							ChildPageStatusId	= ISNULL( CHILD.PageStatusId  , 0 ),
620							AL.ActionList		,
621							MasterPageReadOnly	= ISNULL( MAX( CASE
622																	WHEN ALLOWED_STATUS.Access_PageStatusId = ISNULL( ONE.PageStatusId, 0 )
623																		THEN ALLOWED_STATUS.IsReadOnly
624																	ELSE NULL END
625															  ), 1 ),
626							ChildPageReadOnly	= ISNULL( MAX( CASE
627																	WHEN ALLOWED_STATUS.Access_PageStatusId = ISNULL( CHILD.PageStatusId, 0 )
628																		THEN ALLOWED_STATUS.IsReadOnly
629																	ELSE NULL END
630															  ), 1 )
631					FROM BusinessPage ONE
632--							JOIN fn_dataReference( 'adPageType'  , 'en', DEFAULT ) PGT
633--														ON PGT.DataReferenceCode	= @PageType_Page
634--													   AND PGT.DataReferenceId		= ONE.PageTypeId
635							JOIN (
636										SELECT	DISTINCT
637												LanguageId	,
638												ActionList	= (	SELECT	'-' + ActionCode + '-'
639																FROM (	SELECT	DISTINCT
640																				ActionCode
641																		from @ActionList
642																		WHERE ISNULL( TMP.LanguageId, -999 ) = ISNULL( LanguageId, -999 )
643																		) QR
644																order by ActionCode
645																FOR XML PATH ('')
646															  )
647										FROM @ActionList TMP
648								  )					    AL
649														ON ISNULL( AL.LanguageId, ONE.LanguageId ) = ONE.LanguageId
650						LEFT JOIN BusinessPage		 CHILD
651														ON CHILD.ParentPageId = ONE.BusinessPageId
652
653							 JOIN fn_NestedPageStatus( @UserID, @BusinessId )	ALLOWED_STATUS
654														ON ISNULL( ALLOWED_STATUS.LanguageId,  ONE.LanguageId ) = ONE.LanguageId
655--													   AND ALLOWED_STATUS.Access_PageStatusId = ONE.PageStatusId
656													   AND ALLOWED_STATUS.Access_PageStatusId IN ( ONE.PageStatusId, CHILD.PageStatusId )
657													   AND ALLOWED_STATUS.IsReadOnly		  = 0		-- Edit enable !
658
659						--LEFT JOIN BusinessPage		 CHILD
660						--								ON CHILD.ParentPageId = ONE.BusinessPageId
661					WHERE ONE.BusinessPageId = @BusinessPageId
662					  AND ONE.PageTypeId	 = @PageType_PageId
663					GROUP BY ONE.BusinessId		,
664								 ONE.BusinessPageId	,
665								 ONE.LanguageId		,
666								 ONE.PageStatusId	,
667								 ISNULL( CHILD.BusinessPageId, 0 ),
668								 ISNULL( CHILD.PageStatusId  , 0 ),
669								 AL.ActionList
670	--  EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_GET_ONE', @BusinessPageId = 2757
671	--  EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_GET_ONE', @BusinessPageId = 2898
672
673/*
674Select * from BusinessPage ONE WHERE ONE.BusinessPageId= @BusinessPageId
675
676Select	DISTINCT
677		ONE.BusinessId		,
678		ONE.BusinessPageId	,
679		ONE.LanguageId		,
680		ONE.PageStatusId	,
681		ChildBusinessPageId	= ISNULL( CHILD.BusinessPageId, 0 ),
682		ChildPageStatusId	= ISNULL( CHILD.PageStatusId  , 0 ),
683		AL.ActionList		,
684		MasterPageReadOnly	= 0,	-- NOT USED
685		ChildPageReadOnly	= 0		-- NOT USED
686FROM BusinessPage ONE
687		JOIN fn_dataReference( 'adPageType'  , 'en', DEFAULT ) PGT
688									ON PGT.DataReferenceCode	= @PageType_Page
689									AND PGT.DataReferenceId		= ONE.PageTypeId
690		JOIN (
691					SELECT	DISTINCT
692							LanguageId	,
693							ActionList	= (	SELECT	'-' + ActionCode + '-'
694											FROM (	SELECT	DISTINCT
695															ActionCode
696													from @ActionList
697													WHERE ISNULL( TMP.LanguageId, -999 ) = ISNULL( LanguageId, -999 )
698													) QR
699											order by ActionCode
700											FOR XML PATH ('')
701											)
702					FROM @ActionList TMP
703				)					    AL
704									ON ISNULL( AL.LanguageId, ONE.LanguageId ) = ONE.LanguageId
705			JOIN fn_NestedPageStatus( @UserID, @BusinessId )	ALLOWED_STATUS
706									ON ISNULL( ALLOWED_STATUS.LanguageId,  ONE.LanguageId ) = ONE.LanguageId
707									AND ALLOWED_STATUS.Access_PageStatusId					= ONE.PageStatusId
708									AND ALLOWED_STATUS.IsReadOnly							= 0		-- Edit enable !
709
710	LEFT JOIN BusinessPage		 CHILD
711									ON CHILD.ParentPageId = ONE.BusinessPageId
712
713WHERE ONE.BusinessPageId= @BusinessPageId
714*/
715
716/*
717select * from @ActionList
718
719select * from @PAGE_ACCESS_Pages
720
721*/
722				END
723
724		end
725
726
727
728	--  EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_GET_ONE', @BusinessPageId = 2757
729	--	EXEC asContentEditor @UserId = 10, @Language = 'ru', @Command = 'PAGE_GET_BY_BUSINESS', @BusinessId = 1
730	--	EXEC asContentEditor @UserId = 08, @Language = 'ru', @Command = 'PAGE_GET_BY_BUSINESS', @BusinessId = 2
731	--
732	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_GET_ONE', @BusinessPageId = 1
733	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_GET_ONE', @BusinessPageId = 1745
734	--
735	--	EXEC asContentEditor @UserId = 08, @Language = 'ru', @Command = 'PAGE_GET_ONE', @BusinessPageId = 2
736	--	EXEC asContentEditor @UserId = 10, @Language = 'ru', @Command = 'PAGE_GET_ONE', @BusinessPageId = 2
737	--	EXEC asContentEditor @UserId = 10, @Language = 'ru', @Command = 'PAGE_GET_ONE', @BusinessPageId = 1739
738	--
739	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_GET_ONE', @BusinessPageId = 3
740	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_GET_ONE', @BusinessPageId = 4
741	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_GET_ONE', @BusinessPageId = 5
742	--
743	--	œÓθÁÓ‚‡ÚÂβ ÓÚÓ·‡Ê‡˛ÚÒˇ ÚÓθÍÓ Ú ÒÚ‡Ìˈ˚, ‰ÓÒÚÛÔ Í ÍÓÚÓ˚Ï
744	--	ÓÔ‰ÂÎÂÌ Ô‡‚‡ÏË ÔÓθÁÓ‚‡ÚÂΡ (ÎË·Ó Í‡Í ‚·‰ÂΈ, ÎË·Ó ÔÓÎÛ˜ÂÌÌ˚ ÓÚ ‚·‰Âθˆ‡ ‡Á¯ÂÌˡ)
745	--	¬ ÚÓÏ ˜ËÒΠÏÓÊÂÚ Ó„‡Ì˘˂‡Ú¸Òˇ ‰ÓÒÚÛÔ ÔÓ ˇÁ˚͇Ï.
746	--
747	--	“‡·Îˈ‡ <<@PAGE_ACCESS_Pages>> ÔÓ‰„ÓÚÓ‚ÎÂ̇ ‚˚¯Â....
748	--
749	--	ƒÓÔÓÎÌËÚÂθÌÓ, ÏÓÊÂÚ Ó„‡Ì˘˂‡Ú¸Òˇ ÓÚÓ·‡ÊÂÌËÂ Ó ÒÚ‡Ìˈ (ËÎË Â ÍÓÔËË ‚ ÒÓÒÚÓˇÌËË ‰‡ÍÚËÓ‚‡Ìˡ)
750	--	‚ Á‡‚ËÒËÏÓÒÚË ÓÚ –ŒÀ» ÔÓθÁÓ‚‡ÚÂΡ Ë ‰ÓÒÚÛÔÌ˚ı Ú‡ÍÓÈ –ŒÀ» - —“¿“”—¿’ ÒÚ‡Ìˈ˚
751	--
752
753--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_GET_BY_BUSINESS', @BusinessId = 1090		-- true
754--	EXEC asContentEditor @UserId = 11, @Language = 'ru', @Command = 'PAGE_GET_BY_BUSINESS', @BusinessId = 1090		-- true
755--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_GET_ONE', @BusinessPageId = 2757		-- true
756--	EXEC asContentEditor @UserId = 11, @Language = 'ru', @Command = 'PAGE_GET_ONE', @BusinessPageId = 2757		-- true
757
758	if @Command	IN ( 'PAGE_GET_BY_BUSINESS', 'PAGE_GET_ONE' )
759		begin
760			SET @ResultHTML = ''
761
762			--
763			--	œÓÎÛ˜‡ÂÏ ‰ÂڇθÌÛ˛ ‡Ò¯ËÙÓ‚ÍÛ Ô‡˚ (ËÎË Ó‰ÌÓÈ...) ÒÚ‡Ìˈ
764			--
765			SELECT	BPG.BusinessPageId		,
766					BPG.ParentPageId		,
767					TTT.BusinessId			,
768					BUS.BusinessURL			,
769					BUS.Latitude			,
770					BUS.Longitude			,
771					BUS.MapZoom				,
772					BUS.BusinessImageId		,
773					BusinessImageName		= USI.ImageName		,
774					BUS.BusinessContact		,
775					OwnerId					= BUS.CustomerId	,
776					BPG.PageName			,
777					BPG.PageAddress			,
778					BPG.PageDescription		,
779					BPG.PageTemplateId		,
780					PageTemplateName		= ISNULL( BPT.TemplateName		, '' ),
781					PageTemplateHTML		= ISNULL( BPT.TemplateHTML		, '' ),
782					TTT.PageStatusId		,
783					PageStatusCode			= EMSTA.DataReferenceCode,
784					PageStatusName			= ISNULL( MSTA.DataReferenceName	, '' ),
785					PageTypeCode			= PGT.DataReferenceCode	,
786					PageTypeId				= BPG.PageTypeId		,
787					BPG.AdsGroupId			,
788					ADS.AdsGroupName		,
789					ADS.AdsRankId			,
790					AdsRankName				= ADR.DataReferenceName ,
791					AdsRankValue = ADR.DataReferenceValue,
792					BPG.LanguageId			,
793					Language				= LNG.DataReferenceCode	,
794					LanguageName			= LNG.DataReferenceName	,
795					BPG.PageVersion			,
796					BUS.BusinessName		,
797					BPG.PageHTMLLeft		,
798					BPG.PageHTMLRight		,
799					BPG.PageHTMLTemplate	,
800					BPG.PageHTMLAdv			,
801					TTT.ActionList			,
802					TTT.ChildBusinessPageId	,
803					ChildPageStatusCode		= ECSTA.DataReferenceCode,
804					ChildPageStatusName		= ISNULL( CSTA.DataReferenceName	, '' ),
805					TTT.MasterPageReadOnly	,
806					TTT.ChildPageReadOnly	,
807					BPG.PageAuditStatusId   ,
808					PageAuditStatusCode		= AUDIT.DataReferenceCode,
809					PageAuditStatusName		= AUDIT.DataReferenceCode
810			from @PAGE_ACCESS_Pages TTT
811						JOIN Business												  BUS ON BUS.BusinessId					= TTT.BusinessId
812						JOIN BusinessPage											  BPG ON BPG.BusinessPageId				= TTT.BusinessPageId
813						JOIN BusinessPageTemplate									  BPT ON BPT.PageTemplateId				= BPG.PageTemplateId
814						JOIN fn_DataReference( 'language'    , @Language, DEFAULT )   LNG ON LNG.DataReferenceMasterId		= BPG.LanguageId
815						JOIN fn_DataReference( 'AdPageType'  , @Language, DEFAULT )   PGT ON PGT.DataReferenceMasterId		= BPG.PageTypeId
816						JOIN AdsGroup												  ADS ON ADS.AdsGroupId					= BPG.AdsGroupId
817						JOIN fn_DataReference( 'AdsRank'     , @Language, DEFAULT )   ADR ON ADR.DataReferenceMasterId		= ADS.AdsRankId
818						JOIN fn_DataReference( 'AdPageStatus', @Language, DEFAULT )  MSTA ON MSTA.DataReferenceMasterId		= BPG.PageStatusId
819						JOIN fn_DataReference( 'AdPageStatus', @Language, DEFAULT ) AUDIT ON AUDIT.DataReferenceMasterId	= BPG.PageAuditStatusId
820						JOIN fn_DataReference( 'AdPageStatus', 'en'		, DEFAULT ) EMSTA ON EMSTA.DataReferenceMasterId	= BPG.PageStatusId
821				LEFT	JOIN fn_DataReference( 'AdPageStatus', @Language, DEFAULT )  CSTA ON CSTA.DataReferenceMasterId		= TTT.ChildPageStatusId
822				LEFT	JOIN fn_DataReference( 'AdPageStatus', 'en'		, DEFAULT ) ECSTA ON ECSTA.DataReferenceMasterId	= TTT.ChildPageStatusId
823				LEFT	JOIN UsersImage										          USI ON USI.UserImageId				= BUS.BusinessImageId
824			ORDER BY BPG.PageTypeId		,
825					 BPG.BusinessPageId
826
827			RETURN
828
829	end
830
831	-----------------------------------------------------------
832	--
833	--	Get PageTemplate ( all \ or \  selected type )
834	--
835	--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_GET_TEMPLATE'
836	--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_GET_TEMPLATE', @PageTypeId = 10000
837	--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_GET_TEMPLATE', @PageTypeId = 10002
838	-----------------------------------------------------------
839	--
840	--	œÓÎÛ˜ÂÌË ‚ÒÂı ‰ÓÒÚÛÔÌ˚ı ¯‡·ÎÓÌÓ‚ ‚̯ÌÂ„Ó ‚ˉ‡ ÒÚ‡Ìˈ ( ·‡ÌÂÓ‚ \ ÔËÒÂÏ)
841	--
842	if @Command	IN ( 'PAGE_GET_TEMPLATE' )
843		begin
844
845			Select	BPT.PageTemplateId		,
846					BPT.TemplateName		,
847					BPT.TemplateDescription	,
848					BPT.TemplateHTML		,
849					BPT.PageTypeId			,
850					PageTypeCode			= TYP.DataReferenceCode,
851					PageTypeName			= TYP.DataReferenceName
852			from fn_DataReference('ADPageType', @Language, DEFAULT) TYP
853			  JOIN BusinessPageTemplate BPT ON BPT.PageTypeId = TYP.DataReferenceMasterId
854			WHERE TYP.DataReferenceMasterId = @PageTypeId
855
856			return
857		end
858
859	--
860	--	»ÁÏÂÌÂÌË ӷ˘Â„Ó ÓÔËÒ‡Ìˡ ·ËÁÌÂÒ-Ó·˙ÂÍÚ‡
861	--
862	--	»À» : ƒÓ·‡‚ÎÂÌË Ռ¬Œ√Œ ·ËÁÌÂÒ-Ó·˙ÂÍÚ‡ (ÂÒÎË @BusinessId = 0)
863	--
864	if @Command	= 'BUSINESS_SET'			-- Add & Update
865		BEGIN
866
867			If    Exists ( Select 1 FROM fn_DataReference( 'ForbiddenWords', 'en', DEFAULT ) WHERE DataReferenceName = @BusinessURL )
868			   OR Exists ( Select 1 FROM Business WHERE BusinessId != @BusinessId and BusinessURL = @BusinessURL )
869				BEGIN
870					select BusinessId	= 0,
871						   ErrorMessage = 'BadURL'	--	Code = 'BadURL' used for translate full text: 'This (URL) name is used or present in the banned word list'
872					return
873				END
874
875			Select @BusinessImageName = ImageName
876			from UsersImage
877			Where UserImageId = @BusinessImageId
878
879			IF @BusinessId = 0				-- Add new Business
880				BEGIN
881					INSERT INTO Business(	CustomerId		,
882											BusinessName	,
883											BusinessAddress	,
884											BusinessURL		,
885											Latitude		,
886											Longitude		,
887											MapZoom			,
888											BusinessContact		,
889											BusinessImageId	,
890											BusinessImageName
891										)
892					Values  (	@UserId			,
893								@BusinessName	,
894								@BusinessAddress,
895								@BusinessUrl	,
896								@Latitude		,
897								@Longitude		,
898								@MapZoom		,
899								@BusinessContact,
900								@BusinessImageId,
901								@BusinessImageName
902							)
903
904					SET @NewObjectId = @@IDENTITY
905
906					Select @NewObjectTypeId	= DataReferenceMasterId
907					FROM  fn_DataReference( 'AccessObjects', 'en', DEFAULT)
908					WHERE DataReferenceCode =  'OB'
909
910					SELECT @NewRoleId = DataReferenceMasterId
911					FROM fn_DataReference( 'AccessRole', 'en', DEFAULT)
912					WHERE  DataReferenceCode =  @Sign_RoleOwner
913
914					--
915					--	Assign Access grant
916					--
917					INSERT INTO UsersAccess	(	UserId		,
918												ObjectTypeId,
919												ObjectId	,
920												LanguageId	,
921												RoleId
922											)
923					VALUES	(	@UserId	,
924								@NewObjectTypeId	,
925								@NewObjectId	,
926								NULL			,
927								@NewRoleId
928							)
929
930					--
931					--	Add DEFAULT AdsGroups
932					--		Pages & Banners & EMails
933					--
934
935					Select @Status_AdsActiveId = DataReferenceId
936					from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
937					Where DataReferenceCode  = @Sign_AdsActive
938
939					insert into AdsGroup( BusinessId, AdsGroupName, AdsRankId, AdsStatusId )
940					Select	@NewObjectId		,
941							'DefaultCampaign'	,
942							case @AdsRankId when '' then ADS.DataReferenceId else @AdsRankId  end	,
943							@Status_AdsActiveId
944					from fn_DataReference ( 'AdsRank'	, 'en', DEFAULT) ADS
945					where ADS.DataReferenceCode = 'StandartRank'
946
947					--
948					--	New BusinessId
949					--
950					SET @BusinessId = @NewObjectId
951				END
952			ELSE
953				BEGIN
954
955					IF @Access NOT like '%-BusinessEdit-%'
956						BEGIN
957							return			-- UPDATE DB FAILED!
958						END
959
960					UPDATE Business
961					SET BusinessName		= @BusinessName		,
962						BusinessAddress		= @BusinessAddress	,
963						BusinessURL			= @BusinessUrl		,
964						Latitude			= @Latitude			,
965						Longitude			= @Longitude		,
966						MapZoom				= @MapZoom			,
967						BusinessContact		= @BusinessContact	,
968						BusinessImageId		= @BusinessImageId	,
969						BusinessImageName	= @BusinessImageName
970					WHERE BusinessId = @BusinessId
971
972
973					-- Remove old categories list for this object ::
974					Delete
975					from Categories
976					WHERE BusinessId = @BusinessId
977				END
978
979			-- SET \ Change categories list for this object ::
980			Insert into Categories ( BusinessId, CategoriesId)
981			Select  BusinessId		= @BusinessId,
982					CategoriesId	= DataReferenceID
983			FROM dbo.fn_DataReference( 'AdCategories', 'en', DEFAULT)
984			WHERE @CategoriesCloud like '%<' + Convert( varchar(15), DataReferenceId )  + '>%'
985
986			--
987			--	New business Id
988			--
989			select BusinessId	= @BusinessId,
990				   ErrorMessage = ''
991
992			return
993		END
994
995	--
996	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'BUSINESS_DELETE', @BusinessId = 1102
997	--
998	--	Remove Business & Categories & AccessGrant  if business-object not included AdsGroup
999	--
1000	if @Command	= 'BUSINESS_DELETE'
1001		BEGIN
1002			IF @Access NOT like '%-BusinessEdit-%'
1003				BEGIN
1004					SET @ResultMessage = 'You are not authorized to perform that operation.'
1005					return
1006				END
1007
1008			if EXISTS	(	Select 1
1009							FROM AdsGroup
1010							WHERE BusinessId = @BusinessId
1011						)
1012				BEGIN
1013					SET @ResultMessage = 'This business object exist ADS group. Deleting is not possible.'
1014					return
1015				END
1016
1017			Delete FROM Categories	where BusinessId	= @BusinessId
1018			Delete FROM UsersAccess where ObjectTypeId	= @Sign_ObjectTypeId AND ObjectId = @BusinessId
1019			Delete FROM Business	where BusinessId	= @BusinessId
1020
1021			return
1022		END
1023
1024	--
1025	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_COUNT', @BusinessId = 1234, @BusinessPageId = 0
1026	/*
1027		EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_COUNT', @BusinessId = '2' , @BusinessPageId = '0'
1028		EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_COUNT', @BusinessId = '0' , @BusinessPageId = '2'
1029		EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_COUNT', @BusinessId = '0' , @BusinessPageId = '0'
1030	*/
1031	--
1032	--	—Ú‡ÚËÒÚË͇ Ó ÒÚ‡Ìˈ‡ı  ·ËÁÌÂÒ‡ ‰Îˇ Wizard
1033	--
1034	if @Command	= 'PAGE_COUNT'
1035		BEGIN
1036
1037			if @BusinessId = '0'
1038				BEGIN
1039					Select @BusinessId = BusinessId
1040					FROM BusinessPage
1041					Where BusinessPageId = @BusinessPageId
1042				END
1043
1044			Select @PageType_PageId = DataReferenceId
1045			FROM fn_dataReference( 'adPageType'  , 'en', DEFAULT ) PGT
1046			WHERE DataReferenceCode	= @PageType_Page
1047
1048			Select  BUS.BusinessId	,
1049					CleanPageCount	= sum( case when BP.BusinessPageId IS NOT NULL AND BP.ParentPageId IS     NULL then 1					else 0 end ),
1050					CleanLastPageId	= max( case when BP.BusinessPageId IS NOT NULL AND BP.ParentPageId IS     NULL then BP.BusinessPageId	else 0 end ),
1051					DraftPageCount	= sum( case when BP.BusinessPageId IS NOT NULL AND BP.ParentPageId IS NOT NULL then 1					else 0 end ),
1052					DraftLastPageId = max( case when BP.BusinessPageId IS NOT NULL AND BP.ParentPageId IS NOT NULL then BP.BusinessPageId	else 0 end )
1053			from Business BUS
1054				LEFT JOIN  BusinessPage  BP ON BP.BusinessId	= BUS.BusinessId
1055										   AND BP.PageTypeId	= @PageType_PageId
1056			where BUS.BusinessId = @BusinessId
1057			GROUP BY BUS.BusinessId
1058
1059			return
1060		END
1061
1062
1063	--
1064	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_NEW', @BusinessId = 2, @AdsGroupId = 0
1065	--
1066	--	√ÓÚÓ‚ËÏ ¯‡·ÎÓÌ ‰Îˇ ÒÓÁ‰‡Ìˡ ÌÓ‚ÓÈ ÒÚ‡Ìˈ˚ ‰Îˇ ·ËÁÌÂÒ‡ ( Á̇˜ÂÌˡ ÔÓ ÛÏÓΘ‡Ì˲ )
1067	--	¬ ÒÎÛ˜‡Â ÂÒÎË ÔÓθÁÓ‚‡ÚÂθ Ì ËÏÂÂÚ Ú‡ÍËı Ô‡‚ - ÌË˜Â„Ó Ì ‚ÓÁ‚‡˘‡Ï !!
1068	--
1069	if @Command	= 'PAGE_NEW'
1070		BEGIN
1071
1072			If @Access NOT LIKE '%-PageCreate-%'
1073				BEGIN
1074					return			--	FAILED!
1075				END
1076
1077			if (@AdsGroupId = 0 )					-- if create first page for business
1078				Begin
1079					Select	top 1
1080							@AdsGroupId = AdsGroupId
1081					From AdsGroup
1082					Where BusinessId = @BusinessId
1083				End
1084			If @LanguageId=''
1085				begin
1086				Select @LanguageId = DataReferenceId
1087				from  DataReference
1088				where DataReferenceClass = 'language'
1089				AND LanguageId			= 1
1090				AND DataReferenceCode	= @Language
1091				end
1092			Select @LanguageName= DataReferenceName from(
1093				select Dr.DataReferenceName as DataReferenceName, Dr.DataReferenceMasterId as DataReferenceMasterId from DataReference Dr
1094				join Datareference Dr1 on Dr1.DataReferenceClass='Language' and Dr1.DataReferenceCode=@Language
1095				where Dr.DataReferenceClass='Language'
1096				and Dr.LanguageId=Dr1.DataReferenceId
1097				)AS AllLanguages
1098				where AllLanguages.DataReferenceMasterId=@LanguageId
1099
1100			SELECT	BusinessId			= BUS.BusinessId	,
1101					PageName			= case  @PageName when '' then  BUS.BusinessName else @PageName end,
1102					LanguageId			= @LanguageId		,
1103					LanguageName        = @LanguageName		,
1104                    ActionList			= @Access			,
1105                    PageDescription		= case  @PageDescription when '' then 'Place to enter a brief description your business or current page. Fill line 2-3, max.'else @PageDescription end,
1106					PageTypeId			= PGT.DataReferenceId	,
1107					PageTemplateId		= case @AdsGroupId when '0' then 1 else 1021 end,											-- PAGE TEMPLATE -- ERR DEBUG CHANGE !!
1108					PageHTMLTemplate	= BPT.TemplateHTML		,
1109					PageAddress			= case @PageAddress when '' then BUS.BusinessAddress else @PageAddress end	,
1110					AdsGroupId			= ADSG.AdsGroupId		,
1111					AdsGroupName		= ADSG.AdsGroupName		,
1112					AdsRankId			= ADSG.AdsRankId		,
1113					AdsRankName			= ADSR.DataReferenceName,
1114					AdsRankValue = ADSR.DataReferenceValue
1115			FROM Business BUS
1116			  JOIN fn_dataReference( 'adPageType', 'en', DEFAULT )  PGT
1117											 ON PGT.DataReferenceCode	= @PageType_Page
1118			  JOIN BusinessPageTemplate  BPT ON BPT.PageTemplateId		= 1					-- PAGE TEMPLATE -- ERR DEBUG CHANGE !!
1119			  JOIN AdsGroup				ADSG ON ADSG.AdsGroupId			= @AdsGroupId
1120			  JOIN fn_dataReference( 'AdsRank' , @Language, DEFAULT ) ADSR
1121										     ON ADSR.DataReferenceMasterId = ADSG.AdsRankId
1122			WHERE BUS.BusinessId = @BusinessId
1123
1124			RETURN
1125		END
1126
1127	--
1128	--	ƒÓ·‡‚ÎÂÌË ÌÓ‚ÓÈ ÒÚ‡Ìˈ˚ Í ·ËÁÌÂÒÛ.
1129	--	ÕÓ‚‡ˇ ÒÚ‡Ìˈ‡ ÒÓÒ‰‡ÂÚÒˇ ÒÓ ÒÚ‡ÚÛÒÓÏ NEW
1130	--	Œ‰ÌÓ‚ÂÏÂÌÌÓ ÒÓÁ‰‡ÂÚÒˇ ÍÓÔˡ ÒÚ‡Ìˈ˚ ‚ ÒÓÒÚÓˇÌËË ‰‡ÍÚËÓ‚‡Ìˡ
1131	--
1132	if @Command	= 'PAGE_ADD'
1133		BEGIN
1134
1135			If @Access NOT LIKE '%-PageCreate-%'
1136				BEGIN
1137					SET @ResultMessage = 'Access denied !'
1138					return			--	FAILED!
1139				END
1140
1141			--
1142			--	Add as "New" ( MASTER )
1143			--
1144			Select @PageStatusId = DataReferenceId
1145			from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
1146			where DataReferenceCode = @Sign_StatusNew
1147
1148			Select @PageAuditStatusId = DataReferenceId
1149			from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
1150			where DataReferenceCode = @Sign_AuditStatusAllowed
1151
1152			INSERT INTO BusinessPage	(	BusinessId			,
1153											PageName			,
1154											PageAddress			,
1155											PageDescription		,
1156											LanguageId			,
1157											PageTemplateId		,
1158											PageHTMLTemplate	,
1159											PageStatusId		,
1160											PageAuditStatusId	,
1161											PageTypeId			,
1162											AdsGroupId			,
1163											PageVersion
1164										)
1165			Select	BusinessId			= @BusinessId			,
1166					PageName			= RTRIM(LTRIM( @PageName			)),
1167					PageAddress			= RTRIM(LTRIM( @PageAddress		)),
1168					PageDescription		= RTRIM(LTRIM( @PageDescription	)),
1169					LanguageId			= @LanguageId			,
1170					PageTemplateId		= @PageTemplateId		,
1171					PageHTMLTemplate	= TPL.TemplateHTML		,
1172					PageStatusId		= @PageStatusId			,
1173					PageAuditStatusId	= @PageAuditStatusId	,
1174					PageTypeId			= @PageTypeId			,
1175					AdsGroupId			= @AdsGroupId			,
1176					PageVersion			= CONVERT( varchar(64), getUTCdate(), 120)		-- PageVersion
1177			From BusinessPageTemplate TPL
1178			where TPL.PageTemplateId = @PageTemplateId
1179
1180     		SET @NewPageId = @@IDENTITY
1181
1182			-- Reassign AdsRank :
1183			Update AdsGroup
1184			SET AdsRankId = @AdsRankId
1185			WHERE AdsGroupId = @AdsGroupId
1186
1187			-- Set this page as ROOT ==  REF TO  THIS RECORD !!!!!!!!!!!!!!!!!!!!!!
1188			UPDATE BusinessPage
1189			SET RootPageId = BusinessPageId
1190			WHERE BusinessPageId = @NewPageId
1191
1192			-- œÂ‚‡ˇ ÒÚ‡Ìˈ‡ ‚ ˝ÚÓÈ ADS - „ÛÔÔÂ
1193			--
1194			--	Add as "EMAIL"
1195			--
1196			Select @PageTypeId = DataReferenceId
1197			from dbo.fn_DataReference( 'AdPageType', 'en', DEFAULT)
1198			where DataReferenceCode = 'EMAIL'
1199
1200			SET @PageTemplateId = 3														-- ERR ! DEBUG !! CHANGE !!!!!!!!!!!!!!!!
1201
1202			INSERT INTO BusinessPage	(	BusinessId			,
1203											RootPageId			,		-- ref to root page
1204											PageName			,
1205											PageAddress			,
1206											PageDescription		,
1207											LanguageId			,
1208											PageTemplateId		,
1209											PageHTMLTemplate	,
1210											PageStatusId		,
1211											PageAuditStatusId	,
1212											PageTypeId			,
1213											AdsGroupId			,
1214											PageVersion
1215										)
1216			Select	BusinessId			= @BusinessId			,
1217					RootPageId			= @NewPageId			,
1218					PageName			= RTRIM(LTRIM( @PageName			)),
1219					PageAddress			= RTRIM(LTRIM( @PageAddress		)),
1220					PageDescription		= RTRIM(LTRIM( @PageDescription	)),
1221					LanguageId			= @LanguageId			,
1222					PageTemplateId		= @PageTemplateId		,
1223					PageHTMLTemplate	= TPL.TemplateHTML		,
1224					PageStatusId		= @PageStatusId			,
1225					PageAuditStatusId	= @PageAuditStatusId	,
1226					PageTypeId			= @PageTypeId			,
1227					AdsGroupId			= @AdsGroupId			,
1228					PageVersion			= CONVERT( varchar(64), getUTCdate(), 120)		-- PageVersion
1229			From BusinessPageTemplate TPL
1230			where TPL.PageTemplateId = @PageTemplateId
1231
1232			--
1233			--	Call SELF with "switch this page to EDIT mode"
1234			--
1235			--  asContentEditor(PAGE_TO_EDIT_MODE) return cursor = NULL or table = { BusinessPageId, ChildPageId }
1236			--
1237			-------------------------------------------------------
1238			--		Select	BusinessPageId		= @BusinessPageId,
1239			--				ChildBusinessPageId	= @wrkParentId
1240			-------------------------------------------------------
1241			EXEC asContentEditor	@UserId			= @UserId			 ,
1242									@Language		= @Language			 ,
1243									@Command		= 'PAGE_TO_EDIT_MODE',
1244									@BusinessPageId = @NewPageId
1245
1246			RETURN
1247		END
1248
1249	--
1250	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_TO_EDIT_MODE', @BusinessPageId = 2
1251	--
1252	--	—ÓÁ‰‡ÂÏ "ÍÓÔ˲" ÓÒÌÓ‚ÌÓÈ ÒÚ‡Ìˈ˚ ‰Îˇ ‰‡ÍÚËÓ‚‡Ìˡ
1253	--	ÃÓÊÂÚ ‚˚Á˚‚‡Ú¸Òˇ ÚÓθÍÓ Ì‡ MASTER - ÒÚ‡ÌˈÂ
1254	--
1255	if @Command	= 'PAGE_TO_EDIT_MODE'
1256		BEGIN
1257
1258			If @Access NOT LIKE '%-PageCreate-%'
1259				BEGIN
1260					SET @ResultMessage = 'Access denied !'
1261					return			--	FAILED!
1262				END
1263
1264			Select @PageType_PageId = DataReferenceId
1265			FROM fn_dataReference( 'adPageType'  , 'en', DEFAULT ) PGT
1266			WHERE DataReferenceCode	= @PageType_Page
1267
1268			IF EXISTS (	SELECT 1
1269						FROM BusinessPage
1270						where BusinessPageId = @BusinessPageId
1271						  and PageTypeId	 = @PageType_PageId
1272						  and ParentPageId  IS NOT NULL
1273					  )
1274				BEGIN
1275					-- Its NOT MASTER Record !
1276					SET @ResultMessage = 'Error call. Its not CLEAN PAGE record !'
1277					return
1278				END
1279
1280			--
1281			--	TEST : Exists DRAFT RECORDS ?
1282			--
1283			if EXISTS ( Select 1
1284						From BusinessPage
1285						Where RootPageId = @BusinessPageId
1286						  AND ParentPageId IS NOT NULL
1287					  )
1288				BEGIN
1289					-- ՇȉÂÏ PAGE-DRAFT
1290					SET @wrkParentId = NULL
1291
1292					Select @wrkParentId = BusinessPageId
1293					FROM BusinessPage
1294					WHERE ParentPageId = @BusinessPageId
1295
1296					if @wrkParentId IS NOT NULL
1297						BEGIN
1298							--
1299							-- return EXISTS page IDs
1300							--
1301							Select	BusinessPageId		= @BusinessPageId,
1302									ChildBusinessPageId	= @wrkParentId
1303							return
1304						END
1305					Else
1306						BEGIN
1307							SET @ResultMessage = 'Error call. Exists DRAFT copy and its not PAGE !'
1308
1309							return
1310						END
1311				END
1312
1313			--
1314			-- ◊ÂÌÓ‚ËÍÓ‚ ÌÂÚ. ÕÛÊÌÓ  Ëı ÒÓÁ‰‡Ú¸.
1315			--
1316			--	Get status for "CHILD" page
1317			--	Add as "T:Edit" ( CHILD )
1318			--
1319			Select @PageStatusId = DataReferenceId
1320			from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
1321			where DataReferenceCode = @Sign_StatusEdit
1322
1323			--
1324			--	Create CHILD
1325			--
1326			INSERT INTO BusinessPage (	 [BusinessId]
1327										,[RootPageId]
1328										,[PageDescription]
1329										,[PageName]
1330										,[PageTemplateId]
1331										,[LanguageId]
1332										,[PageStatusId]
1333										,[PageAuditStatusId]
1334										,[PageVersion]
1335										,[PageTypeId]
1336										,[AdsGroupId]
1337										,[PageAddress]
1338										,[PageHTMLLeft]
1339										,[PageHTMLRight]
1340										,[PageHTMLTemplate]
1341										,[PageHTMLAdv]
1342										,[ParentPageId]
1343									)
1344			SELECT	 [BusinessId]
1345					,[RootPageId]
1346					,[PageDescription]
1347					,[PageName]
1348					,[PageTemplateId]
1349					,[LanguageId]
1350					,[PageStatusId]			= @PageStatusId
1351					,[PageAuditStatusId]
1352					,[PageVersion]
1353					,[PageTypeId]
1354					,[AdsGroupId]
1355					,[PageAddress]
1356					,[PageHTMLLeft]
1357					,[PageHTMLRight]
1358					,[PageHTMLTemplate]
1359					,[PageHTMLAdv]
1360					,[ParentPageId]			= BusinessPageId
1361			FROM BusinessPage
1362			WHERE RootPageId = @BusinessPageId
1363
1364
1365			Select @NewChildBusinessPageId = BusinessPageId
1366			FROM BusinessPage
1367			WHERE ParentPageId = @BusinessPageId		-- PAGE - DRAFT
1368
1369			--
1370			--	Create CHILD-PAGE-ITEMS
1371			--
1372			INSERT INTO BusinessPageItem (	 [BusinessPageId]
1373											,[PageItemTemplateId]
1374											,[PageBannerId]
1375											,[PageItemMask]
1376											,[PageItemLocation]
1377											,[PageItemOrderBy]
1378											,[PageItemCaption]
1379											,[PageItemTxt_01]
1380											,[PageItemTxt_02]
1381											,[PageItemTxt_03]
1382											,[PageItemTxt_04]
1383											,[PageItemImg_01_Id]
1384											,[PageItemImg_02_Id]
1385											,[PageItemImg_03_Id]
1386											,[PageItemImg_04_Id]
1387										)
1388			SELECT	BusinessPageId			= @NewChildBusinessPageId,		-- PAGE--˜ÂÌÓ‚ËÍ
1389					BPI.PageItemTemplateId	,
1390					PageBannerId			= BP.BusinessPageId,			-- Ref to BANNER-˜ÂÌÓ‚ËÍ
1391					BPI.PageItemMask		,
1392					BPI.PageItemLocation	,
1393					BPI.PageItemOrderBy		,
1394					BPI.PageItemCaption		,
1395					BPI.PageItemTxt_01		,
1396					BPI.PageItemTxt_02		,
1397					BPI.PageItemTxt_03		,
1398					BPI.PageItemTxt_04		,
1399					BPI.PageItemImg_01_Id	,
1400					BPI.PageItemImg_02_Id	,
1401					BPI.PageItemImg_03_Id	,
1402					BPI.PageItemImg_04_Id
1403			FROM BusinessPageItem BPI
1404			  LEFT JOIN BusinessPage  BP ON BP.ParentPageId   = BPI.PageBannerId
1405			WHERE BPI.BusinessPageId = @BusinessPageId
1406
1407			--
1408			-- return THIS pages IDs
1409			--
1410			Select	BusinessPageId		= @BusinessPageId,
1411					ChildBusinessPageId	= @NewChildBusinessPageId
1412			return
1413
1414		END
1415
1416	--
1417	--	–‰‡ÍÚËÓ‚‡Ìˡ ÓÔËÒ‡Ìˡ ( Á‡„ÓÎÓ‚ÍÓ‚ ) ÒÚ‡Ìˈ˚
1418	--
1419	if @Command	= 'PAGE_SET'
1420		BEGIN
1421
1422			--
1423			--	ACTION : ÔÓÁ‚ÓΡ˛˘Ë ‚ÌÓÒËÚ¸ ËÁÏÂÌÂÌˡ ̇ ÒÚ‡ÌˈÂ
1424			--
1425			If  NOT ( @Access LIKE '%-PageCreate-%' OR @Access LIKE '%-PageTranslate-%' )
1426				BEGIN
1427					return			--	FAILED!
1428				END
1429
1430			Select @Status_AdsActiveId					= DataReferenceId from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT) where DataReferenceCode = @Sign_AdsActive
1431			Select @Status_AdsDeletePendingId			= DataReferenceId from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT) where DataReferenceCode = @Sign_AdsDeletePending
1432			Select @PageStatus_HiddenId					= DataReferenceId from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT) where DataReferenceCode = @Sign_StatusHidden
1433			Select @PageStatus_EnabledId				= DataReferenceId from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT) where DataReferenceCode = @Sign_StatusEnabled
1434			Select @PageStatus_DisabledId				= DataReferenceId from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT) where DataReferenceCode = @Sign_StatusDisabled
1435			Select @PageStatus_RemoveCopyId				= DataReferenceId from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT) where DataReferenceCode = @Sign_StatusRemoveCopy
1436			Select @PageStatus_ApproveRequestId			= DataReferenceId from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT) where DataReferenceCode = @Sign_StatusApproveRequest
1437			Select @PageStatus_ApproveRequestPublishId	= DataReferenceId from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT) where DataReferenceCode = @Sign_StatusApproveRequestPublish
1438			Select @PageStatus_RemoveRequestConfirmedId = DataReferenceId from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT) where DataReferenceCode = @Sign_StatusRemoveRequestConfirmed
1439			Select @PageStatus_CommitRequest            = DataReferenceId from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT) where DataReferenceCode = @Sign_StatusRobCommitRequest
1440
1441			Select	@ParentPageId		= ParentPageId	,
1442					@Old_PageStatusId	= PageStatusId
1443			FROM BusinessPage
1444			WHERE BusinessPageId = @BusinessPageId
1445
1446			if @ParentPageId IS NULL	-- ˜ËÒÚÓ‚ËÍ
1447				BEGIN
1448
1449					UPDATE BusinessPage
1450					SET LanguageId		= @LanguageId	,
1451						PageStatusId	= CASE WHEN PageStatusId = @PageStatus_HiddenId then PageStatusId else  @PageStatusId END
1452					WHERE RootPageId = @BusinessPageId
1453					  AND ParentPageId IS NULL				-- For CLEAN copy ONLY !
1454
1455					--	Disable @LanguageId - pages in other AdsGroup current Business
1456					--	If set now PageStats = Enabled
1457					--	Set DISABLED for other ENABLED CLEAN pages on @LanguageId
1458					--
1459					if @Old_PageStatusId != @PageStatus_EnabledId AND @PageStatusId = @PageStatus_EnabledId
1460						Begin
1461							UPDATE BusinessPage
1462							SET PageStatusId	 = @PageStatus_DisabledId
1463							WHERE BusinessId	 = @BusinessId
1464							  AND RootPageId	!= @BusinessPageId
1465							  AND ParentPageId	 IS NULL
1466							  AND LanguageId	 = @LanguageId
1467							  AND PageStatusId	 = @PageStatus_EnabledId
1468						End
1469
1470				END
1471			else
1472				BEGIN					-- ˜ÂÌÓ‚ËÍ
1473
1474				-- ????
1475				--		LanguageId			--  ÌÛÊÌÓ  Ò ıËÚÓÒÚˇÏË -  ‚ Á‡‚ËÒËÏÓÒÚË ÓÚ ÏÂÒÚ‡
1476				--		PageDescription
1477				--		PageTemplateId
1478
1479					UPDATE BusinessPage
1480					SET PageDescription		= RTRIM(LTRIM( @PageDescription  ))	,
1481						PageName			= RTRIM(LTRIM( @PageName	     ))	,
1482						PageAddress			= RTRIM(LTRIM( @PageAddress		 ))	,
1483						PageStatusId		= @PageStatusId						,
1484						PageVersion			= CONVERT( varchar(64), getUTCdate(), 120)
1485					WHERE BusinessPageId = @BusinessPageId
1486				END
1487
1488			--
1489			--	DEBUG : Auto publication if SET STATUS 'S:ApproveRequest' or 'S:ApproveRequestPublish'
1490			--
1491			IF @PageStatusId in( @PageStatus_ApproveRequestId, @PageStatus_ApproveRequestPublishId)
1492				BEGIN
1493					EXEC asRequestHandling @Command='ADD_REQUEST', @Status=0, @RequestDescription=@PageUrl, @UserId=@UserId, @RequestType='2'
1494			        if @PageUrl in (Null, '')
1495					begin
1496					   EXEC exPagePublication	@DraftPageId = @BusinessPageId
1497					end
1498				END
1499
1500			IF @PageStatusId in( @PageStatus_CommitRequest )
1501				BEGIN
1502					--
1503					--	CHILD page -> MASTER page ( && Items )
1504					--
1505					--UPDATE BusinessPage
1506					--SET PageStatusId = @PageStatus_DisabledId
1507					--WHERE BusinessId	 = @BusinessId
1508					--  AND RootPageId	!= @BusinessPageId
1509					--  AND ParentPageId	 IS NULL
1510					--  AND LanguageId	 = @LanguageId
1511					--  AND PageStatusId	 = @PageStatus_EnabledId
1512
1513					select @NewChildBusinessPageId = ParentPageId from BusinessPage --Get parent of the page that need to be ON.
1514					where BusinessPageId = @BusinessPageId
1515
1516					EXEC exPagePublication	@DraftPageId = @BusinessPageId
1517						-- And SET ACTVATE (ENABLED) for current page
1518
1519
1520					--AAAAAAAAAAAAAAAAAAAA
1521					UPDATE BusinessPage
1522					SET PageStatusId	= CASE WHEN PageStatusId = @PageStatus_HiddenId then PageStatusId else  @PageStatus_EnabledId END
1523					WHERE RootPageId = @NewChildBusinessPageId
1524					AND ParentPageId IS NULL				-- For CLEAN copy ONLY !
1525
1526					--	Disable @LanguageId - pages in other AdsGroup current Business
1527					--	If set now PageStats = Enabled
1528					--	Set DISABLED for other ENABLED CLEAN pages on @LanguageId
1529							UPDATE BusinessPage
1530							SET PageStatusId	 = @PageStatus_DisabledId
1531							WHERE BusinessId	 = @BusinessId
1532							  AND RootPageId	!= @NewChildBusinessPageId
1533							  AND ParentPageId	 IS NULL
1534							  AND LanguageId	 = @LanguageId
1535							  AND PageStatusId	 = @PageStatus_EnabledId
1536					--BBBBBBBBBBBBBBBBBBBB
1537
1538
1539
1540
1541					--Update BusinessPage
1542					--SET PageStatusId = @PageStatus_EnabledId
1543					--WHERE BusinessPageId = @NewChildBusinessPageId
1544				END
1545			--
1546			--	DEBUG : END ---------------------------
1547			--
1548
1549			--
1550			--	DEBUG : Auto remove page (MASTER\CHILD) if SET STATUS 'R:Remove*******PAGE'
1551			--
1552			IF @PageStatusId in ( @PageStatus_RemoveRequestConfirmedId, @PageStatus_RemoveCopyId )
1553				BEGIN
1554					--
1555					--	Send: CLEAN or DRAFT BusinessPageId
1556					--
1557					--	—ÂȘ‡Ò ‚˚ÔÓÎÌˇÂÚÒˇ ‘»«»◊≈— Œ≈ Û‰‡ÎÂÌË PAGE's Ë Ò‚ˇÁ‡ÌÌ˚ı Ò ÌËÏË MAIL & BANNER's
1558					--
1559					--	Remove CHILD page OR MASTER page ( && Items )
1560					--
1561					EXEC exPageRemove	@BusinessPageId = @BusinessPageId
1562
1563					--
1564					--	DEBUG : END ---------------------------
1565					--
1566				END
1567			Else
1568				Begin
1569					-- Activate ADS Group if pending delete...
1570					UPDATE ADS
1571					SET ADS.AdsStatusId = @Status_AdsActiveId
1572					FROM BusinessPage BP
1573					  JOIN AdsGroup ADS ON ADS.AdsGroupId	= BP.AdsGroupId
1574									   AND ADS.AdsStatusId	= @Status_AdsDeletePendingId
1575					WHERE BP.BusinessPageId = @BusinessPageId
1576
1577				End
1578
1579			--
1580			--	Send : CLEAN BusinessPageId
1581			--	Allwise return this page (if possible) or empty cursor
1582			--
1583			if @ParentPageId IS NOT NULL
1584				BEGIN
1585					Set  @BusinessPageId = @ParentPageId					-- Show (return) CLEAN page
1586				END
1587
1588			--
1589			--	≈ÒÎË ÛÒÚ‡ÌÓ‚ÎÂÌ  ÒÚ‡ÚÛÒ : 'S:ApproveRequestPublish'
1590			--			= œËÌÛ‰ËÚÂθÌÓ  ‡Á¯‡ÂÏ ÔÓ͇Á ˝ÚÓÈ ÒÚ‡Ìˈ˚
1591			--
1592			--if @PageStatusId = @PageStatus_ApproveRequestPublishId
1593			--	Begin
1594
1595			--		--	If set now PageStats = Enabled
1596			--		--	Set DISABLED for other ENABLED CLEAN pages on @LanguageId
1597			--		--
1598
1599			--	End
1600
1601
1602			--
1603			--	Õ‡Ï ·Û‰ÛÚ ÌÛÊÌ˚ ÚÓθÍÓ 2 ÔÓΡ :
1604			--		BusinessPageId
1605			--		ChildBusinessPageId
1606			--
1607			EXEC asContentEditor	@UserId			= @UserId,
1608									@Language		= @Language,
1609									@Command		= 'PAGE_GET_ONE',
1610									@BusinessPageId = @BusinessPageId
1611
1612
1613			RETURN
1614		END
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625	--	EXEC asContentEditor @Language = 'en', @UserId = 170, @Command = 'GET_STATUS_FOR_PAGE_OWNER', @PageStatusId = 51301 --
1626
1627		IF @Command = 'GET_STATUS_FOR_PAGE'
1628		BEGIN
1629
1630			DECLARE	@DataReferenceLNG TABLE (
1631								DataReferenceId			bigint				NULL,
1632								DataReferenceMasterId	bigint			NOT NULL,
1633								DataReferenceCode		nvarchar(64)		NULL,
1634								DataReferenceClass		nvarchar(64)	NOT NULL,
1635								DataReferenceOwner		bigint				NULL,
1636								DataReferenceName		nvarchar(255)		NULL,
1637								LanguageId				bigint			NOT NULL,
1638								DataReferenceAlias		nvarchar(64)		NULL
1639
1640							)
1641
1642
1643			Select @PageStatus_ApproveRequestId			= DataReferenceId from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT) where DataReferenceCode = @Sign_StatusApproveRequest
1644			Select @PageStatus_ApproveRequestPublishId	= DataReferenceId from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT) where DataReferenceCode = @Sign_StatusApproveRequestPublish
1645
1646			IF @PageStatusId in (@PageStatus_ApproveRequestId, @PageStatus_ApproveRequestPublishId)
1647			BEGIN
1648			INSERT INTO @DataReferenceLNG
1649			  Select
1650			        DataReferenceId			,
1651					DataReferenceMasterId	,
1652					DataReferenceCode		,
1653					DataReferenceClass		,
1654					DataReferenceOwner		,
1655					DataReferenceName		,
1656					LanguageId				,
1657					DataReferenceAlias
1658				FROM DataReference Where DataReferenceId =  @PageStatusId
1659
1660				INSERT INTO @DataReferenceLNG
1661				Select	DataReferenceId			,
1662					DataReferenceMasterId	,
1663					DataReferenceCode		,
1664					DataReferenceClass		,
1665					DataReferenceOwner		,
1666					DataReferenceName		,
1667					LanguageId				,
1668					DataReferenceAlias
1669				FROM DataReference Where DataReferenceCode =  'M:VerificationOfOwner' AND ISNULL(DataReferenceOwner, ' ') = ' '
1670
1671
1672				SELECT * FROM @DataReferenceLNG
1673			END
1674			ELSE
1675			Select	DataReferenceId			,
1676					DataReferenceMasterId	,
1677					DataReferenceCode		,
1678					DataReferenceClass		,
1679					DataReferenceOwner		,
1680					DataReferenceName		,
1681					LanguageId				,
1682					DataReferenceAlias
1683			FROM fn_DataReference( 'AdPageStatus', @Language, @PageStatusId )
1684
1685			return
1686		END
1687
1688
1689
1690
1691
1692
1693
1694
1695	--
1696	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_TRANSLATE', @BusinessPageId = 2, @LanguageId = 8
1697	--
1698	--	>>> use from "ADSGROUP_CLONE" :
1699	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_TRANSLATE', @BusinessPageId = 2, @LanguageId = 8 , @AdsGroupId = 1234
1700	--
1701	--	Create copy page for translate to oter language
1702
1703
1704
1705
1706	--
1707	if @Command	= 'PAGE_TRANSLATE'
1708		BEGIN
1709
1710			If  @Access NOT LIKE '%-PageCreate-%'
1711				BEGIN
1712					return			--	FAILED!
1713				END
1714
1715			--
1716			-- test - its page = PAGE & Main page &
1717			--
1718			if NOT Exists (	Select 1
1719							FROM BusinessPage
1720							WHERE BusinessPageId = @BusinessPageId
1721							  AND RootPageId	 = @BusinessPageId
1722				)
1723				Begin
1724					Set @ResultMessage = 'For translate need use ONLY MASTER CLEAN PAGE'
1725					return
1726				End
1727
1728			-- if call as "PAGE_TRANSLATE" : Get AdsGroupId
1729			if @AdsGroupId IS NULL
1730				BEGIN
1731					--
1732					-- Only 1 language page in AdsGroup !
1733					--
1734					Select @AdsGroupId = AdsGroupId
1735					From BusinessPage
1736					Where BusinessPageId = @BusinessPageId
1737				END
1738
1739			if Exists (	Select 1
1740						From BusinessPage
1741						where AdsGroupId = @AdsGroupId
1742						  and LanguageId = @LanguageId
1743					  )
1744				Begin
1745					Set @ResultMessage = 'Alredy exists page on LanguageId = ' + Convert( varchar(15), @LanguageId )
1746					return
1747				End
1748
1749			Select @PageStatus_NewId = DataReferenceId
1750			from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
1751			where DataReferenceCode = @Sign_StatusNew
1752
1753			Select @PageStatus_EditId = DataReferenceId
1754			from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
1755			where DataReferenceCode = @Sign_StatusEdit
1756
1757			-- Get DRAFT id for SRC page
1758			Select @DraftPageId = BusinessPageId
1759			FROM  BusinessPage
1760			Where ParentPageId = @BusinessPageId
1761
1762			-- MAIN CLEAN COPY : 1 records
1763			Insert into BusinessPage(	BusinessId			,
1764										PageDescription		,
1765										PageName			,
1766										PageTemplateId		,
1767										LanguageId			,
1768										PageStatusId		,
1769										PageVersion			,
1770										PageTypeId			,
1771										PageAddress			,
1772										PageHTMLLeft		,
1773										PageHTMLRight		,
1774										PageHTMLTemplate	,
1775										PageHTMLAdv			,
1776										ParentPageId		,
1777										AdsGroupId			,
1778										RootPageId			,
1779										PageAuditStatusId	,
1780										DateFirstPublication
1781								)
1782			SELECT  BusinessId			,
1783					PageDescription		,
1784					PageName			= 'Copy: ' + PageName,
1785					PageTemplateId		,
1786					LanguageId			= @LanguageId		,
1787					PageStatusId		= @PageStatus_NewId	,
1788					PageVersion			= null				,
1789					PageTypeId			,
1790					PageAddress			,
1791					PageHTMLLeft		,
1792					PageHTMLRight		,
1793					PageHTMLTemplate	,
1794					PageHTMLAdv			,
1795					ParentPageId		,
1796					AdsGroupId			= @AdsGroupId	,			-- current / or NEW ( if call as ADSGROUP_CLONE )
1797					RootPageId			= null			,
1798					PageAuditStatusId	,
1799					DateFirstPublication = null
1800			FROM  BusinessPage
1801			Where BusinessPageId = @BusinessPageId
1802
1803				Set @RootPageId = @@IDENTITY
1804
1805			UPDATE BusinessPage
1806			SET ParentPageId	= -1 * @BusinessPageId,		-- Ref to src record
1807				RootPageId		= @RootPageId
1808			Where BusinessPageId = @RootPageId
1809
1810			-- Other record
1811			Insert into BusinessPage(	BusinessId			,
1812										PageDescription		,
1813										PageName			,
1814										PageTemplateId		,
1815										LanguageId			,
1816										PageStatusId		,
1817										PageVersion			,
1818										PageTypeId			,
1819										PageAddress			,
1820										PageHTMLLeft		,
1821										PageHTMLRight		,
1822										PageHTMLTemplate	,
1823										PageHTMLAdv			,
1824										ParentPageId		,
1825										AdsGroupId			,
1826										RootPageId			,
1827										PageAuditStatusId	,
1828										DateFirstPublication
1829								)
1830			SELECT  BusinessId			,
1831					PageDescription		,
1832					PageName			= 'Copy: ' + PageName,
1833					PageTemplateId		,
1834					LanguageId			= @LanguageId		,
1835					PageStatusId		= @PageStatus_NewId	,	-- ̇ ÒÎÂ‰Û˛˘ÂÏ ¯‡„ ÒηÂÏ EDIT ‰Îˇ ˜ÂÌÓ‚ËÍÓ‚
1836					PageVersion			= null				,
1837					PageTypeId			,
1838					PageAddress			,
1839					PageHTMLLeft		,
1840					PageHTMLRight		,
1841					PageHTMLTemplate	,
1842					PageHTMLAdv			,
1843					ParentPageId		= -1 * BusinessPageId,		-- REF to primary (source record)
1844					AdsGroupId			= @AdsGroupId	,			-- current / or NEW ( if call as ADSGROUP_CLONE )
1845					RootPageId			= @RootPageId	,
1846					PageAuditStatusId	,
1847					DateFirstPublication = null
1848			FROM  BusinessPage
1849			Where RootPageId	  = @BusinessPageId
1850			  AND BusinessPageId != @BusinessPageId
1851
1852			-- Get interface table ( Old: idClean + idDraft) + ( new: idClean + idDraft )
1853			Declare @Interface table (
1854					SrcCleanId bigint,
1855					SrcDraftId bigint,
1856					DstCleanId bigint,
1857					DstDraftId bigint
1858			)
1859
1860			Insert into @Interface
1861			Select	SrcCleanId = SRC.BusinessPageId		,
1862					SrcDraftId = SRCDRAFT.BusinessPageId,
1863					DstCleanId = DST.BusinessPageId		,
1864					DstDraftId = DSTDRAFT.BusinessPageId
1865			FROM BusinessPage SRC
1866					JOIN BusinessPage      DST ON ( -1 * DST.ParentPageId )		 = SRC.BusinessPageId
1867			  left	JOIN BusinessPage SRCDRAFT ON SRCDRAFT.ParentPageId			 = SRC.BusinessPageId
1868			  left	JOIN BusinessPage DSTDRAFT ON ( -1 * DSTDRAFT.ParentPageId ) = SRCDRAFT.BusinessPageId
1869			WHERE SRC.RootPageId = @BusinessPageId
1870			  AND SRC.ParentPageId IS NULL
1871
1872			--- ITEM's ---------------------------------
1873			Insert into BusinessPageItem(	BusinessPageId		,
1874											PageItemTemplateId	,
1875											PageItemLocation	,
1876											PageItemOrderBy		,
1877											PageItemCaption		,
1878											PageItemTxt_01		,
1879											PageItemTxt_02		,
1880											PageItemTxt_03		,
1881											PageItemTxt_04		,
1882											PageItemImg_01_Id	,
1883											PageItemImg_02_Id	,
1884											PageItemImg_03_Id	,
1885											PageItemImg_04_Id	,
1886											PageItemMask		,
1887											PageBannerId
1888										)
1889			SELECT  BusinessPageId			= CASE	WHEN BPI.BusinessPageId = ONERECORD.SrcCleanId THEN ONERECORD.DstCleanId
1890													ELSE ONERECORD.DstDraftId
1891											  END,
1892					BPI.PageItemTemplateId	,
1893					BPI.PageItemLocation	,
1894					BPI.PageItemOrderBy		,
1895					BPI.PageItemCaption		,
1896					BPI.PageItemTxt_01		,
1897					BPI.PageItemTxt_02		,
1898					BPI.PageItemTxt_03		,
1899					BPI.PageItemTxt_04		,
1900					BPI.PageItemImg_01_Id	,
1901					BPI.PageItemImg_02_Id	,
1902					BPI.PageItemImg_03_Id	,
1903					BPI.PageItemImg_04_Id	,
1904					BPI.PageItemMask		,
1905					PageBannerId			= CASE	WHEN BPI.PageBannerId = INTER.SrcCleanId THEN INTER.DstCleanId
1906													WHEN BPI.PageBannerId = INTER.SrcDraftId THEN INTER.DstDraftId
1907													ELSE null
1908											  END
1909			FROM @Interface ONERECORD
1910					JOIN BusinessPageItem BPI ON BPI.BusinessPageId in ( ONERECORD.SrcCleanId, ONERECORD.SrcDraftId )
1911			  Left	JOIN @Interface     INTER ON BPI.PageBannerId   in ( INTER.SrcCleanId, INTER.SrcDraftId )
1912			Where ONERECORD.SrcCleanId = @BusinessPageId
1913
1914			--  Õ‡‚Ó‰ËÏ ÔÓˇ‰ÓÍ Ò BusinessPage
1915			UPDATE BP
1916			SET BP.ParentPageId = CASE	WHEN BP.BusinessPageId = INTER.DstCleanId THEN null
1917										ELSE INTER.DstCleanId
1918								   END
1919			FROM BusinessPage BP
1920			  JOIN @Interface     INTER ON BP.BusinessPageId   in ( INTER.DstCleanId, INTER.DstDraftId )
1921			WHERE BP.RootPageId = @RootPageId
1922
1923			-- Set EDIT for draft copy
1924			UPDATE BusinessPage
1925			SET PageStatusId = @PageStatus_EditId
1926			WHERE RootPageId = @RootPageId
1927			  AND ParentPageId IS NOT NULL
1928
1929			Declare @GET_TEMP_TO_EDIT TABLE (
1930				BusinessPageId		bigint,
1931				ChildBusinessPageId	bigint
1932			)
1933
1934			IF @DraftPageId IS NULL
1935				Begin
1936					INSERT INTO @GET_TEMP_TO_EDIT
1937					EXEC asContentEditor	@UserId			= @UserId			 ,
1938											@Language		= @Language			 ,
1939											@Command		= 'PAGE_TO_EDIT_MODE',
1940											@BusinessPageId = @RootPageId
1941				End
1942
1943			Set @ResultMessage = 'Copies create succesfull.'
1944
1945			return
1946		End
1947
1948
1949	--
1950	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'ADSGROUP_CLONE', @BusinessId = 1234, @AdsGroupId = 87
1951	--
1952	--	—ÓÁ‰‡ÌË ÔÓÎÌÓÈ ÍÓÔËË ÒÛ˘ÂÒÚ‚Û˛˘ÂÈ AdsGroup
1953	--
1954	if @Command	= 'ADSGROUP_CLONE'
1955		BEGIN
1956			If  @Access NOT LIKE '%-PageCreate-%'
1957				BEGIN
1958					Set @ResultMessage = 'Access denied !'
1959					return												--	FAILED!
1960				END
1961
1962			INSERT INTO AdsGroup(	BusinessId	,
1963									AdsGroupName,
1964									AdsRankId	,
1965									AdsStatusId		)
1966			Select	BusinessId	 ,
1967					AdsGroupName = 'Copy:' + AdsGroupName,
1968					AdsRankId	 ,
1969					AdsStatusId
1970			from AdsGroup
1971			WHERE AdsGroupId = @AdsgroupId
1972
1973			SET @AdsGroupNewId = @@IDENTITY
1974
1975			-- foreach rootBusinessPage &  LanguageId : Call "ToTranslate"
1976			DECLARE CLONE CURSOR FOR
1977			Select	DISTINCT
1978					RootPageId,
1979					LanguageId
1980			From BusinessPage
1981			where AdsgroupId = @AdsgroupId
1982			ORDER BY LanguageId
1983
1984			OPEN  CLONE
1985			FETCH CLONE INTO @CleanPageId, @LanguageId
1986
1987			WHILE ( @@FETCH_STATUS = 0 )
1988				BEGIN
1989
1990					EXEC asContentEditor @UserId		 = @UserId,
1991										 @Language		 = @Language,
1992										 @Command		 = 'PAGE_TRANSLATE',
1993										 @BusinessPageId = @CleanPageId,
1994										 @LanguageId	 = @LanguageId,
1995										 @AdsGroupId	 = @AdsGroupNewId
1996
1997					FETCH CLONE INTO @CleanPageId, @LanguageId
1998				END
1999
2000			CLOSE      CLONE
2001			DEALLOCATE CLONE
2002
2003			Set @ResultMessage = 'Clone ADS Group create succesfull.'
2004
2005			return
2006		END
2007
2008	--
2009	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'ADSGROUP_DELETE', @BusinessId = 1234, @AdsGroupId = 87
2010	--
2011	--	—ÓÁ‰‡ÌË ÔÓÎÌÓÈ ÍÓÔËË ÒÛ˘ÂÒÚ‚Û˛˘ÂÈ AdsGroup
2012	--
2013	if @Command	= 'ADSGROUP_DELETE'
2014		BEGIN
2015			If  @Access NOT LIKE '%-PageCreate-%'
2016				BEGIN
2017					Set @ResultMessage = 'Access denied !'
2018					return												--	FAILED!
2019				END
2020
2021			--
2022			--	For set : "RemoveRequest"
2023			--
2024
2025			Select @PageStatus_NewId = DataReferenceId
2026			from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2027			where DataReferenceCode = @Sign_StatusNew
2028
2029			Select @PageStatus_RemoveRequestId = DataReferenceId
2030			from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2031			where DataReferenceCode = @Sign_StatusRemoveRequest
2032
2033			Select @PageStatus_RemoveCopyId = DataReferenceId
2034			from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2035			where DataReferenceCode = @Sign_StatusRemoveCopy
2036
2037
2038			-- foreach rootBusinessPage : Call "exPageRemove"
2039			DECLARE toRemove CURSOR FOR
2040			Select	BusinessPageId,
2041					PageStatusId
2042			From BusinessPage
2043			where AdsgroupId	 = @AdsgroupId
2044			  AND BusinessPageId = RootPageId
2045
2046			OPEN  toRemove
2047			FETCH toRemove INTO @CleanPageId, @PageStatusId
2048
2049			WHILE ( @@FETCH_STATUS = 0 )
2050				BEGIN
2051
2052					if @PageStatusId = @PageStatus_NewId
2053						Begin
2054							-- CLEAN : Status = NEW :: Set  status : RemoveCopy for DRAFT
2055							--			For REMOVE from DB DRAFT & CLEAN pages !
2056							Update BusinessPage
2057							Set PageStatusId = @PageStatus_RemoveCopyId				-- Remove DRAFT COPY
2058							Where ParentPageId = @CleanPageId
2059						End
2060					Else
2061						Begin
2062							-- CLEAN: Status OTHER: Set status CLEAN := RemoveRequest
2063							Update BusinessPage
2064							Set PageStatusId = @PageStatus_RemoveRequestId			-- Set Status RemoveRequest for CLEAN page
2065							Where BusinessPageId = @CleanPageId
2066						End
2067
2068					--
2069					--	—ÂȘ‡Ò ‚˚ÔÓÎÌˇÂÚÒˇ ‘»«»◊≈— Œ≈ Û‰‡ÎÂÌË PAGE's Ë Ò‚ˇÁ‡ÌÌ˚ı Ò ÌËÏË MAIL & BANNER's
2070					--
2071					--	Remove CHILD page OR MASTER page ( && Items )
2072					--
2073					EXEC exPageRemove	@BusinessPageId = @CleanPageId
2074
2075					FETCH toRemove INTO @CleanPageId, @PageStatusId
2076				END
2077
2078			CLOSE      toRemove
2079			DEALLOCATE toRemove
2080
2081			if not exists (		Select 1
2082								From BusinessPage
2083								where AdsgroupId = @AdsgroupId
2084							)
2085				Begin
2086					-- Delete Ads group if child pages not exists
2087					Delete
2088					from AdsGroup
2089					where AdsGroupId = @AdsgroupId
2090
2091					Set @ResultMessage = 'ADS Group group removed successfully.'
2092				End
2093			Else
2094				Begin
2095					--	And set status for remove adsGroup
2096					Select @Status_AdsDeletePendingId = DataReferenceId
2097					from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2098					Where DataReferenceCode  = @Sign_AdsDeletePending
2099
2100					Update AdsGroup
2101					Set AdsStatusId = @Status_AdsDeletePendingId
2102					where AdsGroupId = @AdsgroupId
2103
2104					Set @ResultMessage = 'ADS Group group marked for removal successfully.'
2105				End
2106
2107			return
2108		END
2109
2110	--
2111	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'ADSGROUP_ACTIVATE', @BusinessId = 1101, @AdsGroupId = 139
2112	--
2113	--	¿ÍÚË‚ËÓ‚‡Ú¸ ˇÁ˚ÍÓ‚˚ ÒÚ‡Ìˈ˚ ˝ÚÓÈ AdsGroup
2114	--
2115	if @Command	= 'ADSGROUP_ACTIVATE'
2116		BEGIN
2117			If  @Access NOT LIKE '%-PageCreate-%'
2118				BEGIN
2119					Set @ResultMessage = 'Access denied !'
2120					return												--	FAILED!
2121				END
2122
2123			Select @PageStatus_EnabledId = DataReferenceId
2124			from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2125			where DataReferenceCode = @Sign_StatusEnabled
2126
2127			Select @PageStatus_DisabledId = DataReferenceId
2128			from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2129			where DataReferenceCode = @Sign_StatusDisabled
2130
2131			Select @PageStatus_AuditAllowedId = DataReferenceId
2132			from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2133			where DataReferenceCode = @Sign_AuditStatusAllowed
2134
2135			---------- Work table --------------
2136			Declare @TBL_AdsPagesActivate table (	BusinessPageId	bigint,
2137													LanguageId		bigint		)
2138
2139			--	Get Disabled languages page list
2140			--
2141			INSERT INTO @TBL_AdsPagesActivate
2142			Select BusinessPageId, LanguageId
2143			from BusinessPage
2144			where AdsGroupId		= @AdsGroupId
2145				and BusinessPageId	= RootPageId
2146				and PageStatusId		= @PageStatus_DisabledId			-- DisabledPage
2147				and PageAuditStatusId	= @PageStatus_AuditAllowedId		-- Show : Allowed
2148
2149			--	Deactivate all ENABLED language pages ( from other group )
2150			--
2151			UPDATE BusinessPage
2152			SET PageStatusId = @PageStatus_DisabledId
2153			Where BusinessId	  = @BusinessId		-- All business pages
2154				AND AdsGroupId   != @AdsGroupId		-- Exclude current group
2155				AND ParentPageId  IS NULL
2156				AND PageStatusId  = @PageStatus_EnabledId
2157				AND LanguageId IN ( Select LanguageId
2158									from @TBL_AdsPagesActivate
2159									)
2160
2161			--	Activate all pages & Mail & Banner for curent Ads Group
2162			--
2163			Update BP
2164			Set PageStatusId = @PageStatus_EnabledId
2165			from  @TBL_AdsPagesActivate TMP
2166				JOIN BusinessPage BP ON BP.RootPageId			= TMP.BusinessPageId
2167									AND BP.ParentPageId		IS NULL
2168									AND BP.PageStatusId		= @PageStatus_DisabledId
2169									AND BP.PageAuditStatusId	= @PageStatus_AuditAllowedId
2170
2171			return
2172		END
2173
2174	--
2175	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'ADSGROUP_DEACTIVATE', @BusinessId = 1101, @AdsGroupId = 139
2176	--
2177	--	¿ÍÚË‚ËÓ‚‡Ú¸ ˇÁ˚ÍÓ‚˚ ÒÚ‡Ìˈ˚ ˝ÚÓÈ AdsGroup
2178	--
2179	if @Command	= 'ADSGROUP_DEACTIVATE'
2180		BEGIN
2181			If  @Access NOT LIKE '%-PageCreate-%'
2182				BEGIN
2183					Set @ResultMessage = 'Access denied !'
2184					return												--	FAILED!
2185				END
2186
2187			Select @PageStatus_EnabledId = DataReferenceId
2188			from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2189			where DataReferenceCode = @Sign_StatusEnabled
2190
2191			Select @PageStatus_DisabledId = DataReferenceId
2192			from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2193			where DataReferenceCode = @Sign_StatusDisabled
2194
2195			--	Set Disabled for languages page current group
2196			--
2197			Update BusinessPage
2198			SET PageStatusId = @PageStatus_DisabledId
2199			where AdsGroupId		= @AdsGroupId				--	¬Ò  ÒÚ‡Ìˈ˚ „ÛÔÔ˚
2200				AND ParentPageId	IS NULL						--	◊ËÒÚÓ‚ËÍË
2201				and PageStatusId	= @PageStatus_EnabledId		--	¿ÍÚË‚Ì˚Â
2202
2203			return
2204		END
2205
2206	--
2207	--	—Óı‡ÌˇÂÏ ÔÓ‰„ÓÚÓ‚ÎÂÌÌ˚È ‰ËÁ‡ÈÌ ÒÚ‡Ìˈ˚ (ÒÛÏχ ÓÚÙÓχÚËÓ‚‡ÌÌ˚ı ·ÎÓÍÓ‚)
2208	--	‚ ·‡ÁÓ‚Û˛ ÒÚÓÍÛ (BusinessPage)
2209	--
2210	--	(¬ÌÛÚÂÌÌËÈ ‚˚ÁÓ‚, ÔÓÒΠÒÓı‡ÌÂÌˡ BusinessPageItem - Á‡ÔËÒÂÈ)
2211	--
2212	if @Command	= 'PAGE_SET_CONTENT'
2213		BEGIN
2214
2215			UPDATE BP
2216			SET PageHTMLLeft		= @PageHTMLLeft			,
2217				PageHTMLRight		= @PageHTMLRight		,
2218				PageHTMLTemplate	= ISNULL( BPT.TemplateHTML, ''),
2219				PageHTMLAdv			= @PageHTMLAdv
2220			FROM BusinessPage BP
2221			  LEFT JOIN BusinessPageTemplate BPT ON BPT.PageTemplateId = BP.PageTemplateId --when 1 then 1021 else BP.PageTemplateId end
2222			WHERE BP.BusinessPageId = @BusinessPageId
2223
2224			RETURN
2225		END
2226
2227
2228
2229	--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_GET_ITEM', @BusinessPageId = 2
2230	--
2231	--	EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'PAGE_GET_ITEM', @BusinessPageId = 1
2232	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'PAGE_GET_ITEM', @BusinessPageId = 2
2233	--
2234	--	¬ÌÛÚÂÌÌÌË ‚˚ÁÓ‚˚, ‰Îˇ ÔÓÎÛ˜ÂÌˡ ‚ÒÂı  ·ÎÓÍÓ‚ Á‡‰‡ÌÌÓÈ ÒÚ‡Ìˈ˚
2235	--
2236	if @Command	= 'PAGE_GET_ITEM'
2237		BEGIN
2238
2239			--
2240			-- ŒÔ‰ÂΡÂÏ ‚·‰Âθˆ‡ ITEMs (◊ËÒÚÓ‚ËÍË ËÎË ◊ÂÌÓ‚ËÍË ?)
2241			--
2242			Select	@ItemOwnerPageId	= RootPageId,
2243					@ItemParentPageId	= ParentPageId
2244			from BusinessPage
2245			WHERE BusinessPageId = @BusinessPageId
2246
2247-- select 1, @ItemOwnerPageId, @ItemParentPageId
2248
2249			if @ItemParentPageId IS NOT NULL
2250				BEGIN
2251					Select @ItemOwnerPageId = BusinessPageId
2252					from BusinessPage
2253					WHERE ParentPageId = @ItemOwnerPageId
2254				END
2255
2256-- select 2, @ItemOwnerPageId, @ItemParentPageId
2257
2258			--
2259			--	«‡·Ë‡ÂÏ ‚Ò  - ̇ C# ·Û‰ÂÏ Ò ÌËÏË ‡Á·Ë‡Ú¸Òˇ..
2260			--
2261			Select	IT.PageItemId			,
2262					IT.BusinessPageId		,
2263					AD.PageName				,
2264					OBJ.BusinessId			,
2265					OBJ.BusinessName		,
2266					IT.PageBannerId			,
2267					IT.PageItemTemplateId	,
2268					IT.PageItemMask			,
2269					IT.PageItemOrderBy		,
2270					IT.PageItemLocation		,
2271					IT.PageItemCaption		,
2272
2273					IT.PageItemTxt_01		,
2274					IT.PageItemTxt_02		,
2275					IT.PageItemTxt_03		,
2276					IT.PageItemTxt_04		,
2277
2278					IT.PageItemImg_01_Id	,
2279					IT.PageItemImg_02_Id	,
2280					IT.PageItemImg_03_Id	,
2281					IT.PageItemImg_04_Id	,
2282
2283					ItemImg_01_Name		= ACI_01.ImageName		,
2284					ItemImg_02_Name		= ACI_02.ImageName		,
2285					ItemImg_03_Name		= ACI_03.ImageName		,
2286					ItemImg_04_Name		= ACI_04.ImageName		,
2287
2288					ItemImg_01_Caption	= ACI_01.ImageCaption	,
2289					ItemImg_02_Caption	= ACI_02.ImageCaption	,
2290					ItemImg_03_Caption	= ACI_03.ImageCaption	,
2291					ItemImg_04_Caption	= ACI_04.ImageCaption	,
2292
2293					TM.ItemTemplateName	,
2294					TM.ItemTemplateHTML
2295			from BusinessPageItem IT
2296					JOIN BusinessPageItemTemplate	 TM ON TM.PageItemTemplateId	= IT.PageItemTemplateId
2297					JOIN BusinessPage				 AD ON AD.BusinessPageId		= IT.BusinessPageId
2298					JOIN Business					OBJ ON OBJ.BusinessId			= AD.BusinessId
2299			  LEFT	JOIN UsersImage					ACI_01 ON ACI_01.UserImageId	= IT.PageItemImg_01_Id
2300			  LEFT	JOIN UsersImage					ACI_02 ON ACI_02.UserImageId	= IT.PageItemImg_02_Id
2301			  LEFT	JOIN UsersImage					ACI_03 ON ACI_03.UserImageId	= IT.PageItemImg_03_Id
2302			  LEFT	JOIN UsersImage					ACI_04 ON ACI_04.UserImageId	= IT.PageItemImg_04_Id
2303			WHERE IT.BusinessPageId = @ItemOwnerPageId
2304			ORDER BY IT.PageItemOrderBy
2305
2306			RETURN
2307		END
2308
2309	--
2310	--	—Óı‡ÌÂÌË (ÔÂÂÁ‡ÔËÒ¸) Ó‰ÌÓ„Ó ËÁ ·ÎÓÍÓ‚ ÒÚ‡Ìˈ˚
2311	--
2312	--	@PageItemId - ›ÚÓ ÏÓÊÂÚ ·˚Ú¸ ·ÎÓÍ ˜ËÒÚÓ‚Ë͇ ËÎË ˜ÂÌÓ‚Ë͇.
2313	--
2314	if @Command	= 'ITEM_SET'
2315		BEGIN
2316
2317			If  NOT ( @Access LIKE '%-PageCreate-%' OR @Access LIKE '%-PageDesign-%' OR @Access LIKE '%-PageTranslate-%' )
2318				BEGIN
2319
2320					SET @ResultMessage	= 'Access denied !'
2321
2322					select	StatusCode		= -1,			--	Error
2323							ExtCode			= 0,
2324							message			= CAST( @PageItemId as nvarchar),
2325							ItemMaskOld		= 0x00,
2326							PageMailId		= 0,
2327							PageBannerId	= 0
2328
2329					return			--	UPDATE FAILED!
2330				END
2331
2332			-- Convert from NVARCHAR to tintint
2333			Set @PageItemMask = convert(tinyint, @PageItemMask)
2334
2335			-- Get old values																  Clean	// Draft
2336			Select	@ItemBusinessPageId	 = BPI.BusinessPageId	,							-- 120	//	121	---------- PAGE ONLY
2337					@RootPageId			 = BP.RootPageId		,							-- 120	//	120 ---------- RootPageId ( clean copy & owner all )
2338					@ItemMaskOld		 = BPI.PageItemMask		,							-- **	//	**
2339					@ItemPageBannerId	 = BPI.PageBannerId		,							-- 130	// 131
2340					@RebuildPageBannerId = BPI.PageBannerId									-- 130	// 131	-- Rebuild HTML for this banner
2341			FROM BusinessPageItem BPI
2342			  JOIN BusinessPage BP ON BP.BusinessPageId = BPI.BusinessPageId				-- 120	// 121
2343			WHERE BPI.PageItemId = @PageItemId												-- 1	// 101
2344
2345			-- »ÏÂÂÚ ÎË ÓÚÌÓ¯ÂÌËÂ Í EMail ?
2346			if (	( ( @ItemMaskOld  & @IsEmail) = @IsEmail )
2347				 OR ( ( @PageItemMask & @IsEmail) = @IsEmail )
2348				)
2349				BEGIN
2350					Select @PageMailId = BusinessPageId
2351					FROM BusinessPage
2352					Where RootPageId = @RootPageId
2353						AND PageTypeId = 10002																	-- MAIL  ERR DEBUG
2354						AND (	 ( (@ItemBusinessPageId  = @RootPageId ) AND (ParentPageId IS NULL     ) )		-- Clean copy
2355							  OR ( (@ItemBusinessPageId != @RootPageId ) AND (ParentPageId IS NOT NULL ) )		-- Draft copy
2356							)
2357				End
2358
2359			-- –‡Á·Ë‡ÂÏÒˇ Ò ·‡ÌÂ‡ÏË --
2360
2361			-- ¡˚Î Ë ÓÒÚ‡ÎÒˇ ·‡ÌÂÓÏ :: œÓ‚ÂËÏ HIDDEN - Ë ÂÒÎË ÌÛÊÌÓ ÛÒÚ‡ÌÓ‚ËÏ ÒÚ‡ÚÛÒ˚
2362			if ( ( @ItemMaskOld & @IsBanner)  = @IsBanner ) AND ( ( @PageItemMask & @IsBanner) = @IsBanner )
2363				Begin
2364					If @ItemBusinessPageId = @RootPageId	--	»ÁÏÂÌˇÂÏ ·ÎÓÍ ˜ËÒÚÓ‚Ë͇
2365						BEGIN
2366							--  ‡ÍÓÈ ÒÚ‡ÚÛÒ ÌÛÊÌÓ ÛÒÚ‡ÌÓ‚ËÚ¸ ˜ËÒÚÓ‚ËÍÛ ?
2367							--
2368							--	Get status for "BANNER" page from "MAIN CLEAN PAGE"
2369							Select @PageStatusId = PageStatusId
2370							FROM BusinessPage
2371							WHERE BusinessPageId = @RootPageId
2372
2373							--	OR GET 'HIDDEN' - status
2374							--
2375							if ( @PageItemMask & @IsHidden ) = @IsHidden
2376								BEGIN
2377									Select @PageStatusId = DataReferenceId
2378									from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2379									where DataReferenceCode = @Sign_StatusHidden
2380								END
2381
2382							UPDATE BusinessPage
2383							Set PageStatusId= @PageStatusId
2384							WHERE  BusinessPageId = @ItemPageBannerId
2385
2386							-- Õ ÌÛÊÌÓ ÔÂÂÙÓÏËÓ‚˚‚‡Ú¸ ·‡ÌÌÂ
2387							SET @RebuildPageBannerId = null
2388
2389						END
2390				End
2391
2392			-- ¡˚Î ‡Ì¸¯Â  ·‡ÌÂÓÏ. “ÂÔÂ¸ ÌÂÚ.
2393			if ( ( @ItemMaskOld & @IsBanner)  = @IsBanner ) AND ( ( @PageItemMask & @IsBanner) != @IsBanner )
2394				BEGIN
2395					--
2396					--		≈ÒÎË Ô˯ÂÎ ˜ËÒÚÓ‚ËÍ
2397					--
2398					If @ItemBusinessPageId = @RootPageId	--	»ÁÏÂÌˇÂÏ ·ÎÓÍ ˜ËÒÚÓ‚Ë͇
2399						BEGIN
2400							--
2401							-- —Ú‡ÚÛÒ ˜ËÒÚÓ‚ËÍÛ : SET 'HIDDEN' - status allwise
2402							--
2403							Select @PageStatusId = DataReferenceId
2404							from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2405							where DataReferenceCode = @Sign_StatusHidden
2406
2407							UPDATE BusinessPage
2408							Set PageStatusId= @PageStatusId
2409							WHERE  BusinessPageId = @ItemPageBannerId
2410						END
2411					Else
2412						Begin
2413							-- Draft copy
2414
2415							--
2416							-- TEST : Statud NEW on MasterPage ?
2417							Select @PageStatusId = DataReferenceId
2418							from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2419							where DataReferenceCode = @Sign_StatusNew
2420
2421							--	œ€“¿≈×fl ! :: Û‰‡ÎËÚ¸ ˜ËÒÚÓ‚ËÍ, ÂÒÎË Û ÌÂ„Ó ÒÚ‡ÚÛÒ NEW
2422							--	≈ÒÎË ÒÚ‡ÚÛÒ ˜ËÒÚÓ‚Ë͇ Ì ‡‚ÂÌ NEW - Û‰‡ÎÂÌË ˜ËÒÚÓ‚Ë͇ Ì ‚˚ÔÓÎÌËÚÒˇ.
2423							--
2424							DELETE CLEAN
2425							From BusinessPage DRAFT
2426							  JOIN BusinessPage CLEAN ON CLEAN.BusinessPageId = DRAFT.ParentPageId
2427													 AND CLEAN.PageStatusId   = @PageStatusId
2428							WHERE DRAFT.BusinessPageId = @ItemPageBannerId
2429
2430							--	”‰‡ÎˇÂÏ ˜ÂÌÓ‚ËÍ
2431							DELETE BP
2432							From BusinessPage BP
2433							WHERE BP.BusinessPageId = @ItemPageBannerId
2434
2435						End
2436
2437					-- Õ ÌÛÊÌÓ ÔÂÂÙÓÏËÓ‚˚‚‡Ú¸ ·‡ÌÌÂ
2438					SET @RebuildPageBannerId = null
2439
2440				END
2441
2442			-- Õ≈ ¡˚Î ‡Ì¸¯Â ·‡ÌÂÓÏ. “ÂÔÂ¸ ƒ¿.
2443			if ( ( @ItemMaskOld & @IsBanner) != @IsBanner ) AND ( ( @PageItemMask & @IsBanner)  = @IsBanner )
2444				BEGIN
2445					If @ItemBusinessPageId = @RootPageId	--	»ÁÏÂÌˇÂÏ ·ÎÓÍ ˜ËÒÚÓ‚Ë͇
2446						BEGIN
2447							--  ‡ÍÓÈ ÒÚ‡ÚÛÒ ÌÛÊÌÓ ÛÒÚ‡ÌÓ‚ËÚ¸ ˜ËÒÚÓ‚ËÍÛ ?
2448							--
2449							--	Get status for "BANNER" page from "MAIN PAGE"
2450							Select @PageStatusId = PageStatusId
2451							FROM BusinessPage
2452							WHERE BusinessPageId = @ItemBusinessPageId
2453
2454							--	OR GET 'HIDDEN' - status
2455							--
2456							if ( @PageItemMask & @IsHidden ) = @IsHidden
2457								BEGIN
2458									Select @PageStatusId = DataReferenceId
2459									from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2460									where DataReferenceCode = @Sign_StatusHidden
2461								END
2462						END
2463					ELSE
2464						BEGIN
2465							-- —Ú‡ÚÛÒ, Ò ÍÓÚÓ˚Ï ·Û‰ÂÚ ÒÓÁ‰‡‚‡Ú¸Òˇ ˜ËÒÚ‚ËÍ ( ‚ Ô‡Â Ò ˜ÂÌÓ‚ËÍÓÏ)
2466							--
2467							--	Get status for "MAIN" page :: Add as "M:NEW" ( MAIN )
2468							--
2469							Select @PageStatusId = DataReferenceId
2470							from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2471							where DataReferenceCode = @Sign_StatusNew
2472						END
2473
2474					--	Create or UPDATE ???
2475					--		ŒÒÓ·ÂÌÌÓÒÚË : ƒÎˇ ˜ËÒÚÓ΂Ë͇ ÏÓ„ÛÚ ·˚Ú¸ 2 ÒËÚÛ‡ˆËË:
2476					--			1. ¡˚Î ·‡ÌÂÓÏ, Ë ‚ ITEM ÂÒÚ¸ ÒÒ˚Î͇ PageBannerId : ÚÓ„‰‡ ÏÂÌˇÂÏ ÚÓθÍÓ ÒÚ‡ÚÛÒ.
2477					--			2. Ì·˚Î ·‡ÌÂÓÏ, ÌÂÚ PageBannerId : ÒÓÁ‰‡ÂÏ ˜ËÒÚÓ‚ËÍ..
2478					--
2479					--		œË ‡·ÓÚÂ Ò ˜ÂÌÓ‚ËÍÓÏ
2480					--			*) —ÓÁ‰‡ÂÏ ˜ËÒÚÓ‚ËÍ Ë ˜ÂÌÓ‚ËÍ.
2481					--
2482					IF ( @ItemPageBannerId IS NOT NULL )		-- ‚ BusinessPage ÒÛ˘ÂÒÚ‚ÛÂÚ Á‡ÔËÒ¸ ‰Îˇ ·‡ÌÂ‡ - Ó·ÌӂΡÂÏ Â„Ó
2483						BEGIN
2484							If @ItemBusinessPageId = @RootPageId	--	»ÁÏÂÌˇÂÏ ·ÎÓÍ ˜ËÒÚÓ‚Ë͇
2485								BEGIN
2486									UPDATE BusinessPage
2487									SET PageStatusId = @PageStatusId
2488									WHERE BusinessPageId = @ItemPageBannerId
2489								END
2490						END
2491					ELSE
2492						BEGIN			-- —ÓÁ‰‡ÂÚÒˇ ·‡ÌÂ, ‚ÔÂ‚˚Â.
2493
2494							-- —ÓÁ‰‡ÂÏ ˜ËÒÚÓ‚ËÍ ¡¿Õ≈–
2495							Select	@PageTypeId			= PS.DataReferenceId,
2496									@PageTemplateId		= BPT.PageTemplateId,
2497									@PageHTMLTemplate	= BPT.TemplateHTML
2498							FROM dbo.fn_DataReference( 'AdPageType', 'en', DEFAULT)  PS
2499							  JOIN BusinessPageTemplate								BPT ON BPT.PageTypeId = PS.DataReferenceId
2500							Where DataReferenceCode = @PageType_Banner
2501
2502							INSERT INTO BusinessPage (	 [BusinessId]
2503														,[RootPageId]
2504														,[PageDescription]
2505														,[PageName]
2506														,[PageTemplateId]
2507														,[LanguageId]
2508														,[PageStatusId]
2509														,[PageAuditStatusId]
2510														,[PageVersion]
2511														,[PageTypeId]
2512														,[AdsGroupId]
2513														,[PageAddress]
2514														,[PageHTMLLeft]
2515														,[PageHTMLRight]
2516														,[PageHTMLTemplate]
2517														,[PageHTMLAdv]
2518														,[ParentPageId]
2519													)
2520							SELECT	BusinessId			,
2521									RootPageId			,
2522									PageDescription		,
2523									PageName			,
2524									PageTemplateId		= @PageTemplateId	,		-- ID - ÿ‡·ÎÓÌ ·‡ÌÂ‡
2525									LanguageId			,
2526									PageStatusId		= @PageStatusId		,		-- NEW or HIDDEN or Status from RootPageId
2527									PageAuditStatusId	,
2528									PageVersion			,
2529									PageTypeId			= @PageTypeId		,		-- BANNER
2530									AdsGroupId			,
2531									PageAddress			,
2532									PageHTMLLeft		= ''				,
2533									PageHTMLRight		= ''				,
2534									PageHTMLTemplate	= @PageHTMLTemplate	,		-- ÿ‡·ÎÓÌ ‰Îˇ ·‡ÌÂ‡
2535									PageHTMLAdv			= ''				,
2536									ParentPageId		= null
2537							FROM BusinessPage
2538							WHERE BusinessPageId = @RootPageId
2539
2540							-- ID - ◊ËÒÚÓ‚Ë͇
2541							SET @ItemPageBannerId		= @@IDENTITY			-- ◊ËÒÚÓ‚ËÍ, Ï.·. ‰Îˇ Ò‚ˇÁ˚‚‡Ìˡ Ò ITEM Ë ÔÂÂÒÚÓÂÌˡ HTML
2542							SET @RebuildPageBannerId	= @ItemPageBannerId
2543
2544							If @ItemBusinessPageId = @RootPageId	--	›ÚÓ ·ÎÓÍ ˜ËÒÚÓ‚Ë͇
2545								BEGIN
2546									UPDATE BusinessPage
2547									SET DateFirstPublication = GetUtcDate()
2548									WHERE BusinessPageId = @ItemPageBannerId
2549								END
2550
2551							--	≈ÒÎË  ËÁÏÂÌˇÂÏ ˜ÂÌÓ‚ËÍ...
2552							if @ItemBusinessPageId != @RootPageId
2553								Begin
2554
2555									--
2556									--	Get status for "CHILD" page
2557									--	Add as "T:Edit" ( CHILD )
2558									--
2559									Select @PageStatusId = DataReferenceId
2560									from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2561									where DataReferenceCode = @Sign_StatusEdit
2562
2563									-- ◊ÂÌÓ‚ËÍ ::
2564									INSERT INTO BusinessPage (	 [BusinessId]
2565																,[RootPageId]
2566																,[PageDescription]
2567																,[PageName]
2568																,[PageTemplateId]
2569																,[LanguageId]
2570																,[PageStatusId]
2571																,[PageAuditStatusId]
2572																,[PageVersion]
2573																,[PageTypeId]
2574																,[AdsGroupId]
2575																,[PageAddress]
2576																,[PageHTMLLeft]
2577																,[PageHTMLRight]
2578																,[PageHTMLTemplate]
2579																,[PageHTMLAdv]
2580																,[ParentPageId]
2581															)
2582									SELECT	 [BusinessId]
2583											,[RootPageId]
2584											,[PageDescription]
2585											,[PageName]
2586											,[PageTemplateId]
2587											,[LanguageId]
2588											,[PageStatusId]			= @PageStatusId		-- EDIT
2589											,[PageAuditStatusId]
2590											,[PageVersion]
2591											,PageTypeId
2592											,[AdsGroupId]
2593											,[PageAddress]
2594											,PageHTMLLeft			= ''
2595											,PageHTMLRight			= ''
2596											,[PageHTMLTemplate]
2597											,PageHTMLAdv			= ''
2598											,[ParentPageId]			= @ItemPageBannerId
2599									FROM BusinessPage
2600									WHERE BusinessPageId = @ItemPageBannerId
2601
2602									SET @ItemPageBannerId		= @@IDENTITY		-- ˜ÂÌÓ‚ËÍ ‰Îˇ Ò‚ˇÁ˚‚‡Ìˡ Ò ITEM Ë ÔÂÂÒÚÓÂÌˡ HTML
2603									SET @RebuildPageBannerId	= @ItemPageBannerId
2604
2605								End
2606
2607						END  -- —ÓÁ‰‡ÂÚÒˇ ·‡ÌÂ, ‚ÔÂ‚˚Â.
2608
2609				END	-- Õ≈ ¡˚Î ‡Ì¸¯Â ·‡ÌÂÓÏ. “ÂÔÂ¸ ƒ¿.
2610
2611			-- ‘ËÍÒËÛÂÏ ËÁÏÂÌÂÌˡ ITEM (‚ ˜‡ÒÚË ˜ËÒÚÓ‚Ë͇: ÔÓˇ‰ÓÍ, χÒ͇, Ò‚ˇÁ͇ Ò ·‡ÌÂÓÏ)
2612			UPDATE BusinessPageItem
2613			SET	PageBannerId		= @ItemPageBannerId		,
2614				PageItemMask		= @PageItemMask			,
2615				PageItemOrderBy		= @PageItemOrderBy		,
2616				PageItemLocation	= @PageItemLocation
2617			WHERE PageItemId = @PageItemId
2618
2619			if @ItemBusinessPageId != @RootPageId	-- ƒÎˇ ˜ÂÌÓ‚Ë͇ -  Á‡ÏÂÌˇÂÏ ÚÂÍÒÚ Ë ËÒÛÌÍË
2620				Begin
2621					UPDATE BusinessPageItem
2622					SET	PageItemTemplateId	= @PageItemTemplateId	,
2623						PageItemCaption		= RTRIM( LTRIM( @PageItemCaption) )	,
2624						PageItemTxt_01		= RTRIM( LTRIM( @PageItemTxt_01	) )	,
2625						PageItemTxt_02		= RTRIM( LTRIM( @PageItemTxt_02	) )	,
2626						PageItemTxt_03		= RTRIM( LTRIM( @PageItemTxt_03	) )	,
2627						PageItemTxt_04		= RTRIM( LTRIM( @PageItemTxt_04	) )	,
2628						PageItemImg_01_Id	= @PageItemImg_01_Id	,
2629						PageItemImg_02_Id	= @PageItemImg_02_Id	,
2630						PageItemImg_03_Id	= @PageItemImg_03_Id	,
2631						PageItemImg_04_Id	= @PageItemImg_04_Id
2632					WHERE PageItemId = @PageItemId
2633				End
2634
2635			-- ‘ËÍÒËÛÂÏ ÌÓ‚Û˛ ‚ÂÒ˲
2636			UPDATE BusinessPage
2637			SET PageVersion	= CONVERT( varchar(64), getUTCdate(), 120)
2638			WHERE BusinessPageId = @ItemPageBannerId
2639
2640			-- ŒÚ‚ÂÚ..
2641			select	StatusCode		= 0,
2642					ExtCode			= 0,
2643					message			= CAST( @PageItemId as nvarchar),
2644					ItemMaskOld		= @ItemMaskOld,
2645					PageMailId		= ISNULL( @PageMailId, 0 ),
2646					PageBannerId	= ISNULL( @RebuildPageBannerId, 0 )
2647
2648			RETURN
2649		END
2650
2651	--
2652	--	ƒÓ·‡‚ÎÂÌË ‚ÓÁÏÓÊÌÓ ÚÓθÍÓ ‰Îˇ DRAFT Page
2653	--
2654	if @Command	= 'ITEM_ADD'
2655		BEGIN
2656
2657			If  NOT ( @Access LIKE '%-PageCreate-%' OR @Access LIKE '%-PageDesign-%' )
2658				BEGIN
2659
2660					SET @ResultMessage	= 'Access denied !'
2661
2662					select	StatusCode		= -1,			--	Error
2663							ExtCode			= 0,
2664							message			= CAST( @PageItemId as nvarchar),
2665							ItemMaskOld		= 0x00,
2666							PageMailId		= 0,
2667							PageBannerId	= 0
2668
2669					return			--	UPDATE FAILED!
2670				END
2671
2672			-- Convert from NCHAR to tinyint
2673			Set @PageItemMask	= convert(tinyint, @PageItemMask)
2674			Set @ItemMaskOld	= @PageItemMask
2675			Set @PageMailId		= 0
2676			Set @PageBannerId	= null
2677
2678			if (( @ItemMaskOld & @IsEmail) = @IsEmail)
2679				BEGIN
2680					Select @PageMailId = MAIL.BusinessPageId
2681					FROM BusinessPage BP
2682					  JOIN BusinessPage MAIL ON MAIL.ParentPageId = BP.RootPageId
2683											AND MAIL.PageTypeId = 10002				-- ¡ÂÂÏ MAIL
2684					where BP.BusinessPageId = @BusinessPageId
2685				End
2686
2687			-- –‡Á·Ë‡ÂÏÒˇ Ò ·‡ÌÂ‡ÏË
2688			if (( @ItemMaskOld & @IsBanner) = @IsBanner)
2689				BEGIN
2690
2691					--
2692					-- ̇ ÓÒÌÓ‚Â PAGE-˜ÂÌÓ‚ËÍ ÒÓÁ‰‡ÂÏ ◊»—“Œ¬»  ‰Îˇ ÌÓ‚Ó„Ó ·‡ÌÂ‡.
2693					--
2694					--	Get status for "MAIN" page
2695					--	Add as "M:NEW" ( MAIN )
2696					--
2697					Select @PageStatusId = DataReferenceId
2698					from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2699					where DataReferenceCode = @Sign_StatusNew
2700
2701					Select	@PageTypeId			= PS.DataReferenceId,
2702							@PageTemplateId		= BPT.PageTemplateId,
2703							@PageHTMLTemplate	= BPT.TemplateHTML
2704					FROM dbo.fn_DataReference( 'AdPageType', 'en', DEFAULT)  PS
2705					  JOIN BusinessPageTemplate								BPT ON BPT.PageTypeId = PS.DataReferenceId
2706					Where DataReferenceCode = @PageType_Banner
2707
2708					INSERT INTO BusinessPage (	 [BusinessId]
2709												,[RootPageId]
2710												,[PageDescription]
2711												,[PageName]
2712												,[PageTemplateId]
2713												,[LanguageId]
2714												,[PageStatusId]
2715												,[PageAuditStatusId]
2716												,[PageVersion]
2717												,[PageTypeId]
2718												,[AdsGroupId]
2719												,[PageAddress]
2720												,[PageHTMLLeft]
2721												,[PageHTMLRight]
2722												,[PageHTMLTemplate]
2723												,[PageHTMLAdv]
2724												,[ParentPageId]
2725											)
2726					SELECT	BusinessId			,
2727							RootPageId			,
2728							PageDescription		,
2729							PageName			,
2730							PageTemplateId		= @PageTemplateId	,		-- ID - ÿ‡·ÎÓÌ ·‡ÌÂ‡
2731							LanguageId			,
2732							PageStatusId		= @PageStatusId		,		-- NEW
2733							PageAuditStatusId	,
2734							PageVersion			,
2735							PageTypeId			= @PageTypeId		,		-- BANNER
2736							AdsGroupId			,
2737							PageAddress			,
2738							PageHTMLLeft		= ''				,
2739							PageHTMLRight		= ''				,
2740							PageHTMLTemplate	= @PageHTMLTemplate	,		-- ÿ‡·ÎÓÌ ·‡ÌÂ‡
2741							PageHTMLAdv			= ''				,
2742							ParentPageId		= null
2743					FROM BusinessPage BP
2744					WHERE BP.BusinessPageId = @BusinessPageId
2745
2746
2747					SET @PageBannerId = @@IDENTITY
2748
2749					-- —ÓÁ‰‡ÂÏ Â„Ó ˜ÂÌÓ‚ËÍ - Ë Á‡ÔÓÏË̇ÂÏ Â„Ó ID
2750					--
2751					--	Get status for "CHILD" page
2752					--	Add as "T:Edit" ( CHILD )
2753					--
2754					Select @PageStatusId = DataReferenceId
2755					from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2756					where DataReferenceCode = @Sign_StatusEdit
2757
2758
2759					INSERT INTO BusinessPage (	 [BusinessId]
2760												,[RootPageId]
2761												,[PageDescription]
2762												,[PageName]
2763												,[PageTemplateId]
2764												,[LanguageId]
2765												,[PageStatusId]
2766												,[PageAuditStatusId]
2767												,[PageVersion]
2768												,[PageTypeId]
2769												,[AdsGroupId]
2770												,[PageAddress]
2771												,[PageHTMLLeft]
2772												,[PageHTMLRight]
2773												,[PageHTMLTemplate]
2774												,[PageHTMLAdv]
2775												,[ParentPageId]
2776											)
2777					SELECT	 [BusinessId]
2778							,[RootPageId]
2779							,[PageDescription]
2780							,[PageName]
2781							,[PageTemplateId]
2782							,[LanguageId]
2783							,[PageStatusId]			= @PageStatusId		-- EDIT
2784							,[PageAuditStatusId]
2785							,[PageVersion]
2786							,PageTypeId
2787							,[AdsGroupId]
2788							,[PageAddress]
2789							,PageHTMLLeft			= ''
2790							,PageHTMLRight			= ''
2791							,[PageHTMLTemplate]
2792							,PageHTMLAdv			= ''
2793							,[ParentPageId]			= @PageBannerId
2794					FROM BusinessPage
2795					WHERE BusinessPageId = @PageBannerId
2796
2797
2798					SET @PageBannerId = @@IDENTITY
2799
2800				END
2801
2802
2803			Select @mItemOrderBy = MAX(PageItemOrderBy)
2804			From BusinessPageItem
2805			Where BusinessPageId = @BusinessPageId
2806
2807			Set @mItemOrderBy = ISNULL( @mItemOrderBy, 0 )  + 1		-- Next #
2808
2809			INSERT INTO BusinessPageItem (	BusinessPageId,
2810											PageItemTemplateId,
2811											PageItemMask		,
2812											PageBannerId		,
2813											PageItemOrderBy		,
2814											PageItemLocation	,
2815											PageItemCaption		,
2816
2817											PageItemTxt_01		,
2818											PageItemTxt_02		,
2819											PageItemTxt_03		,
2820											PageItemTxt_04		,
2821
2822											PageItemImg_01_Id	,
2823											PageItemImg_02_Id	,
2824											PageItemImg_03_Id	,
2825											PageItemImg_04_Id
2826										)
2827			SELECT	BusinessPageId		= @BusinessPageId		,
2828					PageItemTemplateId	= @PageItemTemplateId	,
2829					PageItemMask		= @PageItemMask			,
2830					PageBannerId		= @PageBannerId			,		-- Ref to bannerId (DRAFT)
2831					PageItemOrderBy		= @mItemOrderBy			,
2832					PageItemLocation	= @PageItemLocation		,
2833					PageItemCaption		= RTRIM( LTRIM( @PageItemCaption) )	,
2834
2835					PageItemTxt_01		= RTRIM( LTRIM( @PageItemTxt_01	) )	,
2836					PageItemTxt_02		= RTRIM( LTRIM( @PageItemTxt_02	) )	,
2837					PageItemTxt_03		= RTRIM( LTRIM( @PageItemTxt_03	) )	,
2838					PageItemTxt_04		= RTRIM( LTRIM( @PageItemTxt_04	) )	,
2839
2840					PageItemImg_01_Id	= @PageItemImg_01_Id	,
2841					PageItemImg_02_Id	= @PageItemImg_02_Id	,
2842					PageItemImg_03_Id	= @PageItemImg_03_Id	,
2843					PageItemImg_04_Id	= @PageItemImg_04_Id
2844
2845			SET @NewItemId = @@IDENTITY
2846
2847			UPDATE BusinessPage
2848			SET PageVersion			= CONVERT( varchar(64), getUTCdate(), 120)
2849			WHERE BusinessPageId = @BusinessPageId
2850
2851			select	StatusCode		= 0,
2852					ExtCode			= @mItemOrderBy,
2853					message			= CAST( @NewItemId as nvarchar),
2854					ItemMaskOld		= @ItemMaskOld,
2855					PageMailId		= ISNULL(@PageMailId, 0 ),
2856					PageBannerId	= ISNULL(@PageBannerId, 0 )
2857
2858			RETURN
2859		END
2860
2861	--
2862	--	ŒÔÂ‡ˆËˇ ‚˚ÔÓÎÌˇÂÚÒˇ “ŒÀ‹ Œ Ò ◊≈–ÕŒ¬» ŒÃ !
2863	--
2864	if @Command	= 'ITEM_DEL'
2865		BEGIN
2866
2867			If  NOT ( @Access LIKE '%-PageCreate-%' OR @Access LIKE '%-PageDesign-%' )
2868				BEGIN
2869
2870					SET @ResultMessage	= 'Access denied !'
2871
2872					select	StatusCode		= -1,			--	Error
2873							ExtCode			= 0,
2874							message			= CAST( @PageItemId as nvarchar),
2875							ItemMaskOld		= 0x00,
2876							PageMailId		= 0,
2877							PageBannerId	= 0
2878
2879
2880					return			--	UPDATE FAILED!
2881				END
2882
2883			-- Get old values
2884			Select	@ItemBusinessPageId	 = BPI.BusinessPageId,
2885					@RootPageId			 = BP.RootPageId	 ,
2886					@ItemMaskOld		 = BPI.PageItemMask	 ,
2887					@ItemPageBannerId	 = BPI.PageBannerId
2888			FROM BusinessPageItem BPI
2889			  JOIN BusinessPage BP ON BP.BusinessPageId = BPI.BusinessPageId
2890			WHERE BPI.PageItemId = @PageItemId
2891
2892			if (( @ItemMaskOld & @IsEmail) = @IsEmail)
2893				BEGIN
2894					Select @PageMailId = BusinessPageId
2895					FROM BusinessPage
2896					Where RootPageId = @RootPageId
2897						AND PageTypeId = 10002																	-- MAIL  ERR DEBUG
2898						AND (	 ( (@ItemBusinessPageId  = @RootPageId ) AND (ParentPageId IS NULL     ) )		-- Clean copy
2899							  OR ( (@ItemBusinessPageId != @RootPageId ) AND (ParentPageId IS NOT NULL ) )		-- Draft copy
2900							)
2901				End
2902
2903			-- –‡Á·Ë‡ÂÏÒˇ Ò ·‡ÌÂ‡ÏË
2904			if (( @ItemMaskOld & @IsBanner) = @IsBanner)
2905				BEGIN
2906
2907					-- TEST : Statud NEW on MasterPage ?
2908					Select @PageStatusId = DataReferenceId
2909					from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
2910					where DataReferenceCode = @Sign_StatusNew
2911
2912					--
2913					--	œ˚Ú‡ÂÏÒˇ Û‰‡ÎËÚ¸ ˜ËÒÚÓ‚ËÍ, ÂÒÎË Û ÌÂ„Ó ÒÚ‡ÚÛÒ NEW
2914					--
2915					DELETE BP_OWN
2916					From BusinessPage BP
2917					  JOIN BusinessPage BP_OWN ON BP_OWN.BusinessPageId = BP.ParentPageId
2918											  AND BP_OWN.PageStatusId   = @PageStatusId
2919					WHERE BP.BusinessPageId = @ItemPageBannerId
2920
2921					-- ”‰‡ÎˇÂÏ ˜ÂÌÓ‚ËÍ
2922					DELETE BP
2923					From BusinessPage BP
2924					WHERE BP.BusinessPageId = @ItemPageBannerId
2925				END
2926
2927			-- ÃÓÊÌÓ Û‰‡ÎˇÚ¸ ITEM
2928			DELETE
2929			FROM BusinessPageItem
2930			WHERE PageItemId = @PageItemId
2931
2932			select	StatusCode		= 0,
2933					ExtCode			= 0,
2934					message			= CAST( @PageItemId as nvarchar),
2935					ItemMaskOld		= @ItemMaskOld,
2936					PageMailId		= ISNULL(@PageMailId, 0 ),
2937					PageBannerId	= ISNULL(@PageBannerId, 0 )
2938
2939
2940			RETURN
2941		END
2942
2943
2944
2945	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'ITEM_GET_TEMPLATE'
2946	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'ITEM_GET_TEMPLATE', @PageItemTemplateId = 1
2947	--
2948	--	œÓÎÛ˜‡ÂÏ ÒÔËÒÓÍ ‚ÒÂı ‰ÓÒÚÛÔÌ˚ı ¯‡·ÎÓÌÓ‚ ·ÎÓÍÓ‚
2949	--
2950	if @Command	= 'ITEM_GET_TEMPLATE'
2951		BEGIN
2952
2953			if ISNULL( @PageItemTemplateId, '0') = '0'
2954				SET @PageItemTemplateId= NULL
2955
2956			Select	PageItemTemplateId	,
2957					ItemTemplateName	,
2958					ItemTemplateHTML
2959			from BusinessPageItemTemplate
2960			WHERE PageItemTemplateId = ISNULL( @PageItemTemplateId, PageItemTemplateId )
2961
2962			RETURN
2963		END
2964
2965
2966	------------------------------------------------------------------------------------------------------
2967	-- EXEC asContentEditor @UserId=8 , @Language = 'ru', @Command = 'IMAGE_GET_BY_BUSINESS' , @BusinessId=2
2968	-- EXEC asContentEditor @UserId=1 , @Language = 'ru' ,@Command = 'IMAGE_GET_BY_BUSINESS' , @BusinessId=2
2969	--
2970	--	œÓÎÛ˜‡ÂÏ ÒÔËÒÓÍ ‚ÒÂı ËÒÛÌÍÓ‚ Ò‚ˇÁ‡ÌÌ˚ı Ò ‚·‰ÂθˆÂÏ ÚÂÍÛ˘Â„Ó ·ËÁÌÂÒ‡
2971	--  ≈ÒÎË BusinessId = 0, ·ÂÂÏ ÚÂÍÛ˘Â„Ó ÔÓθÁÓ‚‡ÚÂΡ.
2972	--
2973	if @Command	= 'IMAGE_GET_BY_BUSINESS'
2974		begin
2975
2976			SET @ResultHTML = ''
2977
2978			if    @BusinessId = 0
2979			   OR @Access like '%-PageDesign-%'
2980			   OR @Access like '%-PageCreate-%'
2981				BEGIN
2982
2983					if @BusinessId = 0
2984						BEGIN
2985							SET @OwnerId = @UserId
2986						END
2987					Else
2988						BEGIN
2989							Select @OwnerId = CustomerId
2990							FROM Business
2991							Where BusinessId = @BusinessId
2992						END
2993
2994					-- ‰Îˇ ËÒÍβ˜ÂÌˡ ÔÓ·ÎÂÏ Ò ÔÂÓ·‡ÁÓ‚‡ÌËÂÏ JSON-ÒÚÓÍË ‚ JSON-Ó·˙ÂÍÚ,
2995					-- ÌÛÊÌÓ ‚Ò ‚ÒÚ˜‡˛˘ËÂÒˇ >"< Á‡ÏÂÌËÚ¸ ̇  >\"<
2996
2997					SELECT	@ResultHTML = @ResultHTML
2998							+ '{ '
2999							+ '"UserImageId":'	+ '"' + CONVERT( VARCHAR(15), ISNULL( UserImageId, 0 ) )		+ '",'
3000							+ '"ImageCaption":'	+ '"' + REPLACE(ISNULL( ImageCaption, '&nbsp;' ), '"', '\"')	+ '",'
3001							+ '"ImageName":'	+ '"' + REPLACE(ImageName, '"', '\"')							+ '",'
3002							+ '"ImageAlt":'	+ '"' + coalesce(ImageAlt, '')							+ '",'
3003							+ '},'
3004					FROM UsersImage
3005					WHERE UserId = @OwnerId
3006
3007					SELECT  XMLHTML		= '[' + @ResultHTML + ']',
3008							TEMPLATE	= ''
3009
3010				END
3011			ELSE
3012				BEGIN
3013					SET @ResultMessage	= 'Access denied!'
3014					Select	XMLHTML		= -1,
3015							TEMPLATE	= ''
3016				END
3017
3018			RETURN
3019	end
3020
3021
3022	-- ADSGROUP BUSINESS  ---------------------------------------------------------- Start
3023
3024	--
3025	-- EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'ADSGROUP_GET', @BusinessId = 2
3026	-- EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'ADSGROUP_GET', @BusinessId = 3
3027	-- EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'ADSGROUP_GET', @BusinessId = 3
3028	--
3029	if @Command	= 'ADSGROUP_GET'
3030		begin
3031
3032			--Select @Status_AdsActiveId = DataReferenceId
3033			--from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
3034			--Where DataReferenceCode  = @Sign_AdsActive
3035
3036			Select	ADS.AdsGroupId	,
3037					ADS.BusinessId	,
3038					ADS.AdsGroupName,
3039					ADS.AdsRankId	,
3040					ADS.AdsStatusId	,
3041					AdsStatusCode	= STA.DataReferenceCode,
3042					AdsRankName		= ARG.DataReferenceName
3043			from AdsGroup ADS
3044				JOIN fn_DataReference( 'AdsRank'     , @Language, DEFAULT) ARG ON ARG.DataReferenceMasterId = ADS.AdsRankId
3045				JOIN fn_DataReference( 'AdPageStatus', 'en'     , DEFAULT) STA ON STA.DataReferenceMasterId	= ADS.AdsStatusId
3046			Where ADS.BusinessId	= @BusinessId
3047--			  AND ADS.AdsStatusId	= @Status_AdsActiveId
3048
3049			return
3050		end
3051
3052
3053	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'ADSGROUP_GET_PAGES', @AdsGroupId = 67
3054	--	EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'ADSGROUP_GET_PAGES', @AdsGroupId = 139
3055	--
3056	--	œÓÎÛ˜‡ÂÏ ÒÔËÒÓÍ ˇÁ˚ÍÓ‚ Ë Ò‚ˇÁ‡ÌÌ˚ı Ò ÌËÏË ÒÚ‡Ìˈ ÔÓ „ÛÔÔ ( Ë Ëı ÒÓÒÚÓˇÌËÂ)
3057	--
3058	if @Command	= 'ADSGROUP_GET_PAGES'
3059		begin
3060
3061			Select @BusinessId = BusinessId
3062			from AdsGroup
3063			WHERE AdsGroupId = @AdsGroupId
3064
3065			Declare @ActionListADS TABLE (	LanguageId bigint			null,
3066											ActionCode varchar(128)
3067										)
3068
3069			--
3070			--	‘ÓÏËÛÂÏ Ú‡·ÎËˆÛ ‰ÓÒÚÛÔÓ‚ (ActionCode) ÔÓθÁÓ‚‡ÚÂΡ Í Á‡‰‡ÌÌÓÏÛ ·ËÁÌÂÒÛ,
3071			--	Ò Û˜ÂÚÓÏ ‡Á¯ÂÌÌ˚ı ‰Îˇ ÌÂ„Ó ˇÁ˚ÍÓ‚.
3072			--
3073			Insert into @ActionListADS
3074			Select	DISTINCT
3075					UA.LanguageId,
3076					ActionCode	= AA.DataReferenceCode
3077			FROM UsersAccess	 UA
3078				JOIN DataReference	AR
3079										ON AR.DataReferenceClass = 'AccessRole'
3080									   AND AR.LanguageId		 = 1
3081									   AND AR.DataReferenceOwner IS NOT NULL
3082									   AND AR.DataReferenceOwner = Convert( varchar(15),UA.RoleId)
3083				JOIN fn_DataReference( 'AccessAction', 'en', DEFAULT) AA
3084										ON AA.DataReferenceMasterId = Convert( bigint, AR.DataReferenceName )
3085			WHERE UA.UserId			= @UserId
3086				AND UA.ObjectTypeId	= @Sign_ObjectTypeId
3087				AND UA.ObjectId		= @BusinessId
3088
3089			DECLARE @_NestedPageStatus TABLE (
3090								LanguageId				bigint	NULL,
3091								Access_PageStatusId		bigint	NULL,
3092								IsReadOnly				int		NULL
3093						)
3094
3095			insert into @_NestedPageStatus
3096			Select *
3097			from fn_NestedPageStatus( @UserId, @BusinessId )
3098
3099			Select @PageType_PageId = DataReferenceId
3100			FROM fn_dataReference( 'adPageType'  , 'en', DEFAULT ) PGT
3101			WHERE DataReferenceCode	= @PageType_Page
3102
3103			--
3104			--	RESULT
3105			--
3106			Select	LanguageId			= LNG.DataReferenceMasterId	,
3107					LanguageName		= LNG.DataReferenceName		,
3108					AccessToLanguage	= CASE	WHEN AL.maxActionCode IS NULL				THEN 'false'
3109												WHEN	 ISNULL(NPS_C.IsReadOnly, 1) = 1
3110													 AND ISNULL(NPS_D.IsReadOnly, 1) = 1	THEN 'false'
3111												ELSE 'true'
3112										  END,
3113					CleanPageId			= BP.BusinessPageId			,
3114					CleanPageStatusCode	= STP_C.DataReferenceCode	,
3115					CleanPageAuditCode	= STA_C.DataReferenceCode	,
3116					DraftPageId			= CHILD.BusinessPageId
3117			FROM AdsGroup  ADS
3118					JOIN BusinessPage BP
3119									ON BP.BusinessId	= ADS.BusinessId
3120									AND BP.AdsGroupId	= ADS.AdsGroupId
3121									AND BP.PageTypeId	= @PageType_PageId
3122									AND BP.ParentPageId IS NULL
3123--
3124					JOIN fn_DataReference( 'AdPageStatus', 'en', DEFAULT) STP_C
3125									ON STP_C.DataReferenceId	= BP.PageStatusId
3126
3127					JOIN fn_DataReference( 'AdPageStatus', 'en', DEFAULT) STA_C
3128									ON STA_C.DataReferenceId	= BP.PageAuditStatusId
3129
3130			LEFT	JOIN BusinessPage CHILD
3131									ON CHILD.ParentPageID	=  BP.BusinessPageId
3132									AND CHILD.PageTypeId	= @PageType_PageId
3133
3134					JOIN fn_DataReference( 'language', @Language, DEFAULT ) LNG
3135									ON LNG.DataReferenceMasterId = BP.LanguageId
3136
3137			LEFT	JOIN (	Select	LanguageId	,
3138									maxActionCode = MAX(ActionCode)
3139							from @ActionListADS
3140							where ActionCode in (	'PageDesign',
3141													'PageTranslate'
3142												)
3143							GROUP BY LanguageId
3144						) AL ON ISNULL( AL.LanguageId, BP.LanguageId ) = BP.LanguageId
3145
3146			LEFT JOIN @_NestedPageStatus NPS_C ON NPS_C.Access_PageStatusId						= BP.PageStatusId
3147											  AND ISNULL( NPS_C.LanguageId, BP.LanguageId )		= BP.LanguageId
3148
3149			LEFT JOIN @_NestedPageStatus NPS_D ON NPS_D.Access_PageStatusId						= CHILD.PageStatusId
3150											  AND ISNULL( NPS_D.LanguageId, CHILD.LanguageId )	= CHILD.LanguageId
3151
3152			where ADS.AdsGroupId = @AdsGroupId
3153
3154			return
3155		end
3156
3157
3158	-- EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'ASSIGN_ADSGROUP_BUSINESS_GET_JSON', @BusinessId = 3
3159	-- EXEC asContentEditor @UserId = 52, @Language = 'en',@Command = 'ASSIGN_ADSGROUP_BUSINESS_GET_JSON', @BusinessId = 1098
3160	--	-------------------------------------------------------------------
3161	if @Command	= 'ASSIGN_ADSGROUP_BUSINESS_GET_JSON'
3162		begin
3163
3164			if @Access not  like '%-BusinessEdit-%'
3165				BEGIN
3166					SET @ResultMessage = 'Access denied!'
3167					SET @ResultHTML = -1
3168
3169					SELECT  XMLHTML		= '-1',
3170							TEMPLATE	= @Access
3171
3172					RETURN
3173				END
3174
3175
3176			SET @ResultHTML = '"AdsGroup":['
3177
3178			SELECT	@ResultHTML		= 	@ResultHTML
3179										+ '{ '
3180										+ '"AdsGroupId":'			+ '"' + CONVERT( VARCHAR(15), (ISNULL( ADS.AdsGroupId, 0 ) ) )		+ '",'
3181										+ '"AdsGroupName":'			+ '"' + REPLACE(ISNULL( ADS.AdsGroupName	, '&nbsp;' ), '"', '\"')	+ '",'
3182										+ '"BusinessId":'			+ '"' + CONVERT( VARCHAR(15), (ISNULL( ADS.BusinessId, 0 ) ) )		+ '",'
3183										+ '"AdsRankId":'			+ '"' + CONVERT( VARCHAR(15), (ISNULL( ARG.DataReferenceMasterId, 0 ) ) )		+ '",'
3184										+ '"AdsRankCode":'			+ '"' + REPLACE(ISNULL( ARG.DataReferenceCode	, '&nbsp;' ), '"', '\"')	+ '",'
3185										+ '"AdsRankName":'			+ '"' + REPLACE(ISNULL( ARG.DataReferenceName	, '&nbsp;' ), '"', '\"')	+ '",'
3186										+ '},'
3187			from AdsGroup ADS
3188			JOIN  fn_DataReference ( 'AdsRank'   , @Language, DEFAULT) ARG ON ARG.DataReferenceMasterId =  ADS.AdsRankId
3189			WHERE ADS.BusinessId  =  @BusinessId
3190
3191			-- Collect elements
3192			Set @ResultHTML	= @ResultHTML + '],'
3193
3194			IF @ResultHTML IS NULL
3195			BEGIN
3196				Set @ResultHTML = '"AdsGroup":[],'
3197			END
3198
3199
3200		---------------------------------------------------------
3201		-- AdsRank  is : ------------------------------------- ADSGROUP_RANK_GET_JSON:		{}
3202		---------------------------------------------------------
3203
3204			Set @ResultHTML	= @ResultHTML + '"AdsRank":['
3205
3206			-- ‰Îˇ ËÒÍβ˜ÂÌˡ ÔÓ·ÎÂÏ Ò ÔÂÓ·‡ÁÓ‚‡ÌËÂÏ JSON-ÒÚÓÍË ‚ JSON-Ó·˙ÂÍÚ,
3207			-- ÌÛÊÌÓ ‚Ò ‚ÒÚ˜‡˛˘ËÂÒˇ >"< Á‡ÏÂÌËÚ¸ ̇  >\"<
3208			SELECT	@ResultHTML = @ResultHTML
3209								+ '{ '
3210								+ '"id":'	+ '"' + CONVERT( VARCHAR(15), ISNULL( RAN.DataReferenceMasterId, 0 ) )						+ '",'
3211								+ '"name":'	+ '"' + REPLACE(ISNULL( RAN.DataReferenceName	, '&nbsp;' ), '"', '\"')	+ '",'
3212								+ '"code":'	+ '"' + CONVERT( VARCHAR(15), ISNULL( RAN.DataReferenceCode, 0 ) )			+ '",'
3213								+ '},'
3214			FROM (	Select	DataReferenceMasterId,
3215							DataReferenceName,
3216							DataReferenceCode
3217					FROM fn_DataReference( 'AdsRank', @Language, DEFAULT)
3218				 ) RAN
3219			Order By RAN.DataReferenceMasterId
3220
3221			-- Collect elements
3222			Set @ResultHTML	= @ResultHTML + '],'
3223
3224
3225			-- ----------------------------------------------
3226			SELECT  XMLHTML		= '({'  + @ResultHTML + '})',
3227					TEMPLATE	= ''
3228
3229			return
3230		end
3231
3232
3233	--
3234	-- EXEC asContentEditor @UserId = 11, @Language = 'ru', @Command = 'ADSGROUP_BUSINESS_EDIT_ADD', @AdsGroupId= 5, @BusinessId = 79,@AdsGroupName='hhdhhd', @AdsRankId='52340'
3235	--	-------------------------------------------------------------------
3236	--
3237	IF @Command = 'ADSGROUP_BUSINESS_EDIT_ADD'
3238		BEGIN
3239			SET @ResultMessage=''
3240			if @Access not  like '%-BusinessEdit-%'
3241				BEGIN
3242					SET @ResultMessage = 'Access denied!'
3243
3244					SELECT  XMLHTML		= '-1',
3245							TEMPLATE	= @Access
3246
3247					RETURN
3248				END
3249
3250			if @AdsGroupId = -1
3251				BEGIN
3252
3253					Select @Status_AdsActiveId = DataReferenceId
3254					from dbo.fn_DataReference( 'AdPageStatus', 'en', DEFAULT)
3255					Where DataReferenceCode  = @Sign_AdsActive
3256
3257					INSERT INTO AdsGroup(	AdsGroupName,
3258											BusinessId	,
3259											AdsRankId	,
3260											AdsStatusId		)
3261					VALUES(  	@AdsGroupName,
3262								@BusinessId	 ,
3263								@AdsRankId	 ,
3264								@Status_AdsActiveId	)
3265
3266					SET @AdsGroupId = @@IDENTITY
3267				END
3268			ELSE
3269				BEGIN
3270					UPDATE	AdsGroup
3271					  SET AdsGroupName	= @AdsGroupName,
3272						  AdsRankId		= @AdsRankId
3273					WHERE AdsGroupId = @AdsGroupId
3274				END
3275
3276			if @@ROWCOUNT = 0
3277				BEGIN
3278					SET @ResultMessage = 'DataBase: Chage group business failed !'
3279
3280					SELECT  XMLHTML		= '-1',
3281							TEMPLATE	= ''
3282
3283					return
3284				END
3285
3286			--	Check Set\Add result -------------------------------------
3287 			SET @ResultMessage = ''
3288 			SET @ResultHTML    = '( { "Id":' + @AdsGroupId + ' } )'
3289
3290		END
3291
3292
3293	--
3294	-- EXEC asContentEditor @UserId = 11, @Language = 'ru', @Command = 'ADSGROUP_BUSINESS_DELETE', @AdsGroupId= 8,@BusinessId = 79
3295	--	-------------------------------------------------------------------
3296	--
3297	IF @Command = 'ADSGROUP_BUSINESS_DELETE'
3298		BEGIN
3299			SET @ResultMessage=''
3300			if @Access not  like '%-BusinessEdit-%'
3301				BEGIN
3302					SET @ResultMessage = 'Access denied!'
3303
3304					SELECT  XMLHTML		= '-1',
3305							TEMPLATE	= @Access
3306
3307					RETURN
3308				END
3309
3310				if EXISTS ( SELECT 1 FROM BusinessPage WHERE AdsGroupId = @AdsGroupId )
3311					BEGIN
3312						SET @ResultMessage = 'DataBase: This group is already in use !'
3313
3314						SELECT  XMLHTML		= '-1',
3315								TEMPLATE	= ''
3316
3317						return
3318					END
3319				ELSE
3320					BEGIN
3321						DELETE FROM	AdsGroup WHERE AdsGroupId = @AdsGroupId
3322					END
3323
3324				if @@ROWCOUNT = 0
3325				BEGIN
3326					SET @ResultMessage = 'DataBase: Delete group business failed !'
3327
3328					SELECT  XMLHTML		= '-1',
3329							TEMPLATE	= ''
3330
3331					return
3332				END
3333
3334			--	Check Set\Add result -------------------------------------
3335 			SET @ResultMessage = ''
3336 			SET @ResultHTML    = '( { "Id":' + @AdsGroupId + ' } )'
3337
3338		END
3339
3340
3341	--	ADSGROUP BUSINESS  ---------------------------------------------------------- Stop
3342
3343	--	ACCCESS MANAGEMENT  ---------------------------------------------------------- Start
3344	--
3345	-- EXEC asContentEditor @UserId = 2, @Language = 'ru', @Command = 'ACCESS_MANAGEMENT_GET_JSON', @ObjectType = 'OB', @ObjectId = 8
3346	-- EXEC asContentEditor @UserId = 11, @Language = 'uk', @LanguageId = 1, @Command = 'ACCESS_MANAGEMENT_GET_JSON', @ObjectType = 'DR', @ObjectId = 1006
3347	--	-------------------------------------------------------------------
3348	--
3349	--	ACCESS_MANAGEMENT_GET_JSON = {	ACCESS_USER_GET_JSON:		{},
3350	--									ACCESS_LANGUAGE_GET_JSON:	{},
3351	--									ACCESS_ROLE_GET_JSON:		{}
3352	--								 }
3353	--	-------------------------------------------------------------------
3354	--
3355	--	œÓÎÛ˜‡ÂÏ ÔÓÎÌÛ˛ ËÌÙÓχˆË˛ Ó ÔÓθÁÓ‚‡ÚÂΡı Ë Ëı Ô‡‚‡ı
3356	--	‰Îˇ Á‡‰‡ÌÌÓ„Ó  Ó·˙ÂÍÚ‡ (ÒÔ‡‚Ó˜ÌË͇ ËÎË ·ËÁÌÂÒ-Ó·˙ÂÍÚ‡, ÒÚ‡Ìˈ˚.. )
3357	--
3358	if @Command	= 'ACCESS_MANAGEMENT_GET_JSON'
3359		begin
3360
3361			if  @ObjectType = 'DR'
3362				 BEGIN
3363					Select @Access = dbo.fn_CheckDataReferenceAccess( @UserId, @ObjectId, null, null, @LanguageId )
3364				 END
3365
3366			if @Access not  like '%-DistributedAccess-%'
3367				BEGIN
3368					SET @ResultMessage = 'Access denied!'
3369					SET @ResultHTML = -1
3370
3371					SELECT  XMLHTML		= '-1',
3372							TEMPLATE	= @Access
3373
3374					RETURN
3375				END
3376
3377			-- ----------------------------------------------
3378			--
3379			--	¬ÎÓÊÂÌÌÓÒÚ¸ ÓÎÂÈ Ë Ëı Ô‡‚‡ ̇ ÛÔ‡‚ÎÂÌˡ ‰ÓÒÚÛÔ‡ÏË
3380			--	Ò Û˜ÂÚÓÏ ‡Á¯ÂÌÌ˚ı ˇÁ˚ÍÓ‚ ÚÂÍÛ˘Â„Ó ÔÓθÁÓ‚‡ÚÂΡ
3381			--
3382
3383			Declare @AccessRole TABLE (
3384										RoleId				bigint	null,
3385										LanguageId			bigint	null,
3386										IsDistributedAccess int		null
3387									  )
3388
3389			INSERT INTO @AccessRole
3390			Select	DISTINCT
3391					RoleId				= AR.DataReferenceMasterId,
3392					UA.LanguageId		,
3393					IsDistributedAccess	= CASE
3394											WHEN NESTED.Master_RoleId = AR.DataReferenceMasterId THEN NESTED.Master_DistributedAccess
3395											WHEN NESTED.Child_RoleId  = AR.DataReferenceMasterId THEN NESTED.Child_DistributedAccess
3396											ELSE 0
3397										  END
3398			FROM  UsersAccess	 UA
3399				JOIN fn_NestedRoles( @ObjectType ) NESTED ON NESTED.Master_RoleId = UA.RoleId
3400				JOIN fn_DataReference( 'AccessRole', @Language, DEFAULT) AR
3401							ON AR.DataReferenceMasterId in (NESTED.Master_RoleId, NESTED.Child_RoleId)
3402			WHERE UA.ObjectId	= @ObjectId
3403				and UA.UserId	= @UserId
3404
3405			--
3406			--	for JSON RESULT string
3407			--
3408			SET @ResultHTML = ''
3409
3410			---------------------------------------------------------
3411			-- Access Users is : ------------------------------------ ACCESS_USER_GET_JSON: {}
3412			---------------------------------------------------------
3413
3414			SET @ResultHTML = '"AccessUsers":['
3415
3416			--
3417			-- ‰Îˇ ËÒÍβ˜ÂÌˡ ÔÓ·ÎÂÏ Ò ÔÂÓ·‡ÁÓ‚‡ÌËÂÏ JSON-ÒÚÓÍË ‚ JSON-Ó·˙ÂÍÚ,
3418			-- ÌÛÊÌÓ ‚Ò ‚ÒÚ˜‡˛˘ËÂÒˇ >"< Á‡ÏÂÌËÚ¸ ̇  >\"<
3419			--
3420			SELECT	@ResultHTML = @ResultHTML
3421					+ '{ '
3422					+ '"UserId":'				+ '"' + CONVERT( VARCHAR(15), (ISNULL( UA.UserId, 0 ) ) )				+ '",'
3423					+ '"UserFirstName":'		+ '"' + REPLACE(ISNULL( US.UserFirstName	, '&nbsp;' ), '"', '\"')	+ '",'
3424					+ '"UserLastName":'			+ '"' + REPLACE(ISNULL( US.UserLastName		, '&nbsp;' ), '"', '\"')	+ '",'
3425					+ '"AccessLanguageName":'	+ '"' + REPLACE(ISNULL( lDR.DataReferenceName	, '&nbsp;' ), '"', '\"')	+ '",'
3426					+ '"AccessLanguageId":'		+ '"' + CONVERT( VARCHAR(15), (ISNULL( UA.LanguageId, 0 ) ) )		+ '",'
3427					+ '"AccessRoleName":'		+ '"' + REPLACE(ISNULL( fDR.DataReferenceName	, '&nbsp;' ), '"', '\"')	+ '",'
3428					+ '"AccessRoleCode":'		+ '"' + REPLACE(ISNULL( fDR.DataReferenceCode	, '&nbsp;' ), '"', '\"')	+ '",'
3429					+ '"AccessRoleId":'			+ '"' + CONVERT( VARCHAR(15), (ISNULL( UA.RoleId, 0 ) ) )		+ '",'
3430					+ '"DistributingLang":'		+ '"' + CONVERT( VARCHAR(15), (ISNULL( UAD.LanguageId, 0 ) ) )		+ '",'
3431					+ '},'
3432			FROM  UsersAccess	 UA
3433					-- ÚÓθÍÓ ‡Ì‡Îӄ˘Ì˚ ÏÓËÏ Ô‡‚‡ .....
3434					-- ÚÓθÍÓ Ú ˇÁ˚ÍË, ‰Îˇ ÍÓÚÓ˚ı ˇ ˇ‚Ρ˛Ò¸ ‰ËÒÚË·ÛÚÓÓÏ Ô‡‚ .....
3435					JOIN @AccessRole IAM ON IAM.RoleId = UA.RoleId
3436										AND COALESCE( IAM.LanguageId, UA.LanguageId, -1000 )  = COALESCE( UA.LanguageId, -1000)
3437
3438 					JOIN fn_DataReference( 'AccessObjects', @Language, DEFAULT)  OB ON OB.DataReferenceMasterId  = UA.ObjectTypeId
3439																				   AND OB.DataReferenceCode		 = @ObjectType
3440					JOIN fn_DataReference( 'AccessRole', @Language, DEFAULT)	fDR ON fDR.DataReferenceMasterId = UA.RoleId
3441					JOIN (	select	TOP 1
3442									LanguageId
3443							from UsersAccess
3444							where UserId	 = @UserId
3445							  AND ObjectId   = @ObjectId
3446							ORDER BY LanguageId
3447						 ) UAD on 1=1
3448					JOIN Users												 US ON US.UserId					= UA.UserId
3449			LEFT	JOIN fn_DataReference( 'Language', @Language, DEFAULT)	lDR ON lDR.DataReferenceMasterId	= UA.LanguageId
3450			WHERE UA.ObjectId  = @ObjectId
3451
3452			  --
3453			  --	œË ˝ÚÓÏ, ÚÂÍÛ˘ËÈ ÔÓθÁÓ‚‡ÚÂθ ÏÓÊÂÚ ËÏÂÚ¸ ‰ÓÒÚÛÔ ÍÓ ‚ÒÂÏ ˇÁ˚͇Ï
3454			  --	ÎË·Ó Ê ÔÓθÁÓ‚‡ÚÂÎË ËÁ ÒÔËÒ͇ ËÏÂ˛Ú ˇÁ˚Í ‚ıÓ‰ˇ˘ËÈ ‚ ÒÔËÒÓÍ
3455			  --	ˇÁ˚ÍÓ‚, ÔÓ ÍÓÚÓ˚Ï ÚÂÍÛ˘ËÈ ÔÓθÁÓ‚‡ÚÂθ ËÏÂÂÚ Ô‡‚‡ ̇ ÛÔ‡‚ÎÂÌË ‰ÓÒÚÛÔ‡ÏË
3456			  --
3457			  AND (		IAM.LanguageId IS NULL
3458					OR  COALESCE( UA.LanguageID, -9999 ) IN (
3459																SELECT	DISTINCT
3460																		COALESCE( LanguageId, -9999 )
3461																FROM  @AccessRole IAM
3462																WHERE ISNULL( IAM.IsDistributedAccess, 0 ) = 1
3463															 )
3464				)
3465			ORDER BY US.UserFirstName
3466
3467
3468			-- Collect elements
3469			Set @ResultHTML	= @ResultHTML + '],'
3470
3471
3472			---------------------------------------------------------
3473			-- Languages is : --------------------------------------- ACCESS_LANGUAGE_GET_JSON:	{}
3474			---------------------------------------------------------
3475
3476			Set @ResultHTML	= @ResultHTML +  '"Language":['
3477
3478			SELECT	@ResultHTML = @ResultHTML
3479					+ '{ '
3480					+ '"id":'	+ '"' + CONVERT( VARCHAR(15), ISNULL( LNG.DataReferenceMasterId, 0 ) )		+ '",'
3481					+ '"name":'	+ '"' + REPLACE(ISNULL( LNG.DataReferenceName	, '&nbsp;' ), '"', '\"')	+ '",'
3482					+ '"code":'	+ '"' + CONVERT( VARCHAR(15), ISNULL( LNG.DataReferenceCode, 0 ) )			+ '",'
3483					+ '},'
3484			FROM  (
3485						SELECT	Distinct
3486								fDR.DataReferenceMasterId	,
3487								fDR.DataReferenceName		,
3488								fDR.DataReferenceCode
3489						FROM @AccessRole AR
3490							JOIN fn_DataReference( 'Language', @Language, DEFAULT)		 fDR
3491											ON fDR.DataReferenceMasterId	= COALESCE( AR.LanguageId, fDR.DataReferenceMasterId )
3492						WHERE ISNULL( AR.IsDistributedAccess, 0 ) = 1
3493					) LNG
3494
3495			-- Collect elements
3496			Set @ResultHTML	= @ResultHTML + '],'
3497
3498
3499
3500			---------------------------------------------------------
3501			-- Access Role is : ------------------------------------- ACCESS_ROLE_GET_JSON:		{}
3502			---------------------------------------------------------
3503
3504			Set @ResultHTML	= @ResultHTML + '"AccessRoles":['
3505
3506			-- ‰Îˇ ËÒÍβ˜ÂÌˡ ÔÓ·ÎÂÏ Ò ÔÂÓ·‡ÁÓ‚‡ÌËÂÏ JSON-ÒÚÓÍË ‚ JSON-Ó·˙ÂÍÚ,
3507			-- ÌÛÊÌÓ ‚Ò ‚ÒÚ˜‡˛˘ËÂÒˇ >"< Á‡ÏÂÌËÚ¸ ̇  >\"<
3508			SELECT	@ResultHTML = @ResultHTML
3509					+ '{ '
3510					+ '"id":'	+ '"' + CONVERT( VARCHAR(15), ISNULL( ROL.RoleId, 0 ) )						+ '",'
3511					+ '"name":'	+ '"' + REPLACE(ISNULL( ROL.DataReferenceName	, '&nbsp;' ), '"', '\"')	+ '",'
3512					+ '"code":'	+ '"' + CONVERT( VARCHAR(15), ISNULL( ROL.DataReferenceCode, 0 ) )			+ '",'
3513					+ '},'
3514			FROM (
3515					Select	DISTINCT
3516							AR.RoleId,
3517							DR.DataReferenceName,
3518							DR.DataReferenceCode
3519					FROM @AccessRole AR
3520					  JOIN fn_DataReference( 'AccessRole', @Language, DEFAULT) DR
3521										ON DR.DataReferenceMasterId = AR.RoleId
3522				) ROL
3523
3524			-- Collect elements
3525			Set @ResultHTML	= @ResultHTML + '],'
3526
3527
3528			-- ----------------------------------------------
3529			SELECT  XMLHTML		= '({'  + @ResultHTML + '})',
3530					TEMPLATE	= ''
3531
3532			return
3533	end
3534
3535	--	ACCCESS MANAGEMENT  ---------------------------------------------------------- Stop
3536
3537
3538	------------------------------------------------------------------------------------------------------
3539	-- EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'USER_BY_EMAIL_GET_JSON',  @UserEMail = 'mail@mail.ru', @ObjectType = 'OB', @ObjectId = 8
3540	-- EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'USER_BY_EMAIL_GET_JSON',  @UserEMail = 'mail@mail.ru', @ObjectType = 'DR', @ObjectId = 1000
3541	-- EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'USER_BY_EMAIL_GET_JSON',  @UserEMail = 'mail@mail.ru', @ObjectType = 'DR', @ObjectId = 10001
3542	--
3543	--	œÓËÒÍ ÔÓθÁÓ‚‡ÚÂΡ ÔÓ Â„Ó Email
3544	--	(‰Îˇ Ô‰ÓÒÚ‡‚ÎÂÌˡ ÂÏÛ Ô‡‚ ̇ ‡·ÓÚÛ ÒÓ ÒÔ‡‚Ó˜ÌË͇ÏË ËÎË ·ËÁÌÂÒ-Ó·˙ÂÍÚ‡ÏË)
3545	--
3546	if @Command	= 'USER_BY_EMAIL_GET_JSON'
3547		begin
3548
3549			SET @ResultHTML = ''
3550
3551			if  @ObjectType = 'DR'
3552				 BEGIN
3553					Select @Access = dbo.fn_CheckDataReferenceAccess( @UserId, @ObjectId, null, null, @LanguageId )
3554				 END
3555
3556			if @Access not  like '%-DistributedAccess-%'
3557				BEGIN
3558					SET @ResultMessage = 'Access denied!'
3559					SELECT  XMLHTML		= '-1',
3560							TEMPLATE	= ''
3561
3562					RETURN
3563				END
3564				if  @ObjectType = 'DR'
3565					BEGIN
3566
3567						SELECT @RoleDataReference_Edit = DataReferenceMasterId
3568							FROM fn_DataReference( 'SecurityRoles', 'en', DEFAULT)
3569							WHERE  DataReferenceCode =  @Sign_Role_DataReference_Edit
3570
3571							SELECT	@ResultHTML = @ResultHTML
3572								+ '{ '
3573								+ '"UserId":'			+ '"' + CONVERT( VARCHAR(15), (ISNULL( UR.UserId, 0 ) ) )				+ '",'
3574								+ '"UserFirstName":'	+ '"' + REPLACE(ISNULL( US.UserFirstName	, '&nbsp;' ), '"', '\"')	+ '",'
3575								+ '"UserLastName":'		+ '"' + REPLACE(ISNULL( US.UserLastName		, '&nbsp;' ), '"', '\"')	+ '",'
3576								+ '"UserEMail":'		+ '"' + REPLACE(ISNULL( US.UserEMail		, '&nbsp;' ), '"', '\"')	+ '",'
3577								+ '},'
3578						FROM  UsersRoles UR
3579							JOIN Users US ON US.UserId = UR.UserId
3580										  AND US.UserEMail = @UserEMail
3581							WHERE UR.RoleId = @RoleDataReference_Edit
3582
3583					END
3584				ELSE
3585					BEGIN
3586
3587						SELECT	@ResultHTML = @ResultHTML
3588								+ '{ '
3589								+ '"UserId":'			+ '"' + CONVERT( VARCHAR(15), (ISNULL( UserId, 0 ) ) )				+ '",'
3590								+ '"UserFirstName":'	+ '"' + REPLACE(ISNULL( UserFirstName	, '&nbsp;' ), '"', '\"')	+ '",'
3591								+ '"UserLastName":'		+ '"' + REPLACE(ISNULL( UserLastName	, '&nbsp;' ), '"', '\"')	+ '",'
3592								+ '"UserEMail":'		+ '"' + REPLACE(ISNULL( UserEMail		, '&nbsp;' ), '"', '\"')	+ '",'
3593								+ '},'
3594						FROM  Users
3595						WHERE  UserEMail = @UserEMail
3596					END
3597
3598			SELECT  XMLHTML		= '[' + @ResultHTML + ']',
3599					TEMPLATE	= ''
3600
3601			return
3602		end
3603
3604
3605
3606		-- Exec asContentEditor	@UserId	= 176, @Command = 'CHANGE_BUSINESS_OWNER'
3607
3608
3609
3610--///////////////////////////////////////////////////////\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\--
3611    IF @Command = 'CHANGE_BUSINESS_OWNER'
3612		BEGIN
3613		   SET @ResultMessage = ''
3614		   SELECT @UserEMail = UserEMail
3615				From Users
3616				Where UserId = @UserId
3617
3618
3619			SELECT @AccessToRoleId = DataReferenceId
3620				FROM fn_DataReference( 'AccessRole', 'en', DEFAULT)
3621				WHERE DataReferenceCode = @Sign_RoleOwner
3622
3623
3624
3625
3626
3627			SELECT @mObjectTypeId = DataReferenceId
3628				FROM  fn_DataReference( 'AccessObjects', 'en', DEFAULT)
3629				WHERE DataReferenceCode = 'OB'
3630
3631		   SELECT @ObjectId = BusinessId, @AccessToUserId = UserId
3632		    From SAInvite
3633			Where UserEmail = @UserEMail
3634
3635			   Update Business
3636			   SET CustomerId = @UserId
3637			   Where BusinessId = @ObjectId
3638
3639
3640			   DELETE
3641							FROM UsersAccess
3642							WHERE UserId					= @AccessToUserId
3643								AND ObjectId				= @ObjectId
3644								AND ObjectTypeId			= @mObjectTypeId
3645								AND RoleId					= @AccessToRoleId
3646								AND ISNULL( LanguageId, 0 )	= ISNULL( @AccessToLanguageId, 0 )
3647
3648
3649
3650			  INSERT INTO UsersAccess  (				UserId		,
3651														ObjectTypeId,
3652														ObjectId	,
3653														LanguageId	,
3654														RoleId		)
3655							VALUES(  	@UserId					,
3656										@mObjectTypeId					,
3657										@ObjectId						,
3658										CASE WHEN ISNULL(@AccessToLanguageId,0) = 0 THEN NULL ELSE @AccessToLanguageId END,
3659										@AccessToRoleId				)
3660
3661							SET @mRowCount = @@ROWCOUNT
3662
3663							if @mRowCount = 0
3664								BEGIN
3665									SET @ResultMessage = 'DataBase: Add user grant failed !'
3666
3667									SELECT  XMLHTML		= -1,
3668											TEMPLATE	= ''
3669
3670									return
3671								END
3672
3673
3674		 SELECT @AccessToRoleId = DataReferenceId
3675				FROM fn_DataReference( 'AccessRole', 'en', DEFAULT)
3676				WHERE DataReferenceCode = @Sign_RoleBusinessManager
3677			 EXEC asContentEditor	@UserId					= @UserId,
3678									@Language				= 'en',
3679									@Command				= 'ACCESS_USER_ADD',
3680									@ObjectId				= @ObjectId,
3681									@ObjectType				= 'OB',
3682									@AccessToUserId			= @AccessToUserId,
3683									@AccessToRoleId			= @AccessToRoleId
3684
3685
3686
3687
3688		RETURN
3689		END
3690
3691	-- ///////////// ƒŒ¡¿¬À≈Õ»≈ = »«Ã≈Õ≈Õ»≈ + ”ƒ¿À≈Õ»≈ œ–¿¬ ƒŒ—“”œ¿ ////////////////////////
3692
3693	----------------------------------------------------------------------------------
3694	-- EXEC asContentEditor @UserId = 8, @Language = 'ru', @Command = 'USER_BY_EMAIL_GET_JSON',  @UserEMail = 'mail@mail.ru', @ObjectType = 'OB', @ObjectId = 8
3695
3696	--
3697    --  Command = ACCESS_USER_ADD
3698    --          = ACCESS_USER_SET
3699    --          = ACCESS_USER_DEL -- ???
3700	--
3701	/*		EXEC asContentEditor	@UserId					= 168,
3702									@Language				= 'ru',
3703									@Command				= 'ACCESS_USER_ADD',
3704									@ObjectId				= 1290,
3705									@ObjectType				= 'OB',
3706									@OldAccessRoleId		= '',
3707								    @OldAccessLanguageId	= '',
3708									@AccessToUserId			= 176,
3709									@AccessToRoleId			= 51256
3710
3711
3712
3713
3714		EXEC asContentEditor	@UserId					= 8,
3715								@Language				= 'ru',
3716								@Command				= 'ACCESS_USER_ADD',
3717								@ObjectId				= 1,
3718								@ObjectType				= 'OB',
3719								@OldAccessRoleId		= '',
3720								@OldAccessLanguageId	= '',
3721								@AccessToUserId			= 2,
3722								@AccessToRoleId			= '',
3723								@AccessToLanguageId		= 1
3724	*/
3725	----------------------------------------------------------------------------------
3726	--
3727	--	Õ‡·Ó ÔÓÒΉӂ‡ÚÂÎÌÓ ‚˚ÔÓÎÌˇÂÏ˚ı ·ÎÓÍÓ‚, ‰Îˇ ( Ô‡‚‡ ̇ ‡·ÓÚÛ Ò ÒÔ‡‚Ó˜ÌË͇ÏË Ë ·ËÁÌÂÒ-Ó·˙ÂÍÚ‡ÏË)
3728	--		- ƒÓ·‡‚ÎÂÌË ÔÓθÁÓ‚‡ÚÂβ Ô‡‚
3729	--		- »ÁÏÂÌÂÌË Ô‡‚ ÔÓθÁÓ‚‡ÚÂΡ
3730	--		- ”‰‡ÎÂÌË ÚÂÍÛ¯Ëı ‡Á¯ÂÌˡ ÔÓθÁÓ‚‡ÚÂβ
3731	--		*****
3732	--		- œÓ‰  Ô‡‚‡ÏË ÔÓ‰‡ÁÛÏ‚‡ÂÚÒˇ
3733	--			‡) Œ·˙ÂÍÚ ( ÒÔ‡‚Ó˜ÌËÍ ËÎË ·ËÁÌÂÒ )
3734	--			·) flÁ˚Í, Ò ÍÓÚÓ˚Ï ‡Á¯ÂÌÓ ‡·ÓÚ‡Ú¸
3735	--			‚) –Óθ ÔÓθÁÓ‚‡ÚÂΡ "ROLE" ( Ó„‡Ì˘ÂÌË  ‚ÓÁÏÓÊÌ˚ı ‰ÂÈÒÚ‚ËÈ "ACTION")
3736	--
3737	IF @Command	in ( 'ACCESS_USER_ADD', 'ACCESS_USER_DEL', 'ACCESS_USER_SET'  )			-- Add \ Del \ Change user grant
3738		BEGIN
3739
3740			-- Start of test grant & assign default values
3741
3742			SET @ResultMessage = ''				-- error not found.
3743
3744			-- Replace "OB" / "DR" to id this type ------------------------
3745			SELECT @mObjectTypeId = DataReferenceId
3746			FROM  fn_DataReference( 'AccessObjects', 'en', DEFAULT)
3747			WHERE DataReferenceCode = @ObjectType
3748
3749			IF @AccessToLanguageId = -1		-- ( Add user access -- get language from current user(GRANTOR) )
3750				BEGIN
3751
3752					Select	TOP 1
3753							@AccessToLanguageId = UA.LanguageId
3754					FROM  UsersAccess	 UA
3755						JOIN fn_NestedRoles( @ObjectType ) NESTED ON NESTED.Master_RoleId = UA.RoleId
3756														  AND NESTED.Master_DistributedAccess = 1
3757					WHERE UserId		= @UserId
3758					  AND ObjectId		= @ObjectId
3759					  AND ObjectTypeId	= @mObjectTypeId
3760					Order by UA.LanguageId
3761
3762				END
3763
3764			if  @ObjectType = 'DR'
3765				BEGIN
3766					Select @Access = dbo.fn_CheckDataReferenceAccess( @UserId, @ObjectId, null, null, @AccessToLanguageId  )
3767				END
3768			-- Check acces to this Business or DataReference
3769			if @Access NOT LIKE '%-DistributedAccess-%'
3770				BEGIN
3771						SET @ResultMessage = '1:Access denied!'
3772					PRINT @ResultMessage
3773
3774						SELECT  XMLHTML		= '-1',
3775								TEMPLATE	= ''
3776
3777						return
3778				END
3779
3780			IF @AccessToRoleId = ''
3781				BEGIN					-- For new User assign default minimal role ----------------------------------
3782					SELECT @AccessToRoleId = DataReferenceId
3783					FROM fn_DataReference( 'AccessRole', 'en', DEFAULT)
3784					WHERE DataReferenceCode = CASE WHEN  @ObjectType = 'OB' THEN @Sign_RoleTranslatorPage
3785												   WHEN  @ObjectType = 'DR' THEN @Sign_RoleTranslator
3786											  END
3787				END
3788
3789			-- ---------------------------------------------------------------------
3790			-- "OB" : if assign "Owner"  : set  LanguageTo  = NULL ( all languages )
3791			if  @ObjectType = 'OB'
3792				AND EXISTS (
3793								SELECT 1
3794								FROM fn_DataReference( 'AccessRole', 'en', DEFAULT)
3795								WHERE DataReferenceId	 = @AccessToRoleId
3796									AND DataReferenceCode = @Sign_RoleOwner
3797							)
3798				BEGIN
3799					SET @AccessToLanguageId = NULL				-- FOR OWNER - access to ALL LANGUAGES
3800				END
3801
3802			-- ------------------------------------------------
3803			-- End of test grant & assign default values
3804			-- ------------------------------------------------
3805
3806			-- ------------------------------------------------
3807			-- DELETE User access
3808			-- ------------------------------------------------
3809			IF @Command	= 'ACCESS_USER_DEL'  						-- REMOVE Access
3810				BEGIN
3811
3812					if  @ObjectType = 'OB'
3813						AND EXISTS (
3814										SELECT 1
3815										FROM fn_DataReference( 'AccessRole', 'en', DEFAULT)
3816										WHERE DataReferenceId	= @AccessToRoleId
3817										  AND DataReferenceCode	= @Sign_RoleOwner
3818									)
3819						BEGIN
3820							--	REMOVE record for Owner : restricted.
3821							SET @ResultMessage = 'DataBase : Delete OWNER records restricted !'
3822
3823							SELECT  XMLHTML		= -1,
3824									TEMPLATE	= ''
3825
3826							return
3827						END
3828					ELSE
3829						BEGIN
3830
3831							DELETE
3832							FROM UsersAccess
3833							WHERE UserId					= @AccessToUserId
3834								AND ObjectId				= @ObjectId
3835								AND ObjectTypeId			= @mObjectTypeId
3836								AND RoleId					= @AccessToRoleId
3837								AND ISNULL( LanguageId, 0 )	= ISNULL( @AccessToLanguageId, 0 )
3838
3839							SET @mRowCount = @@ROWCOUNT
3840
3841 							IF @mRowCount = 1
3842 								BEGIN
3843 									SET @ResultMessage = ''
3844									SET @ResultHTML = 1
3845								END
3846							ELSE
3847								BEGIN
3848									SET @ResultMessage = 'DataBase : Delete failed ! Record not found!'
3849
3850									SELECT  XMLHTML		= -1,
3851											TEMPLATE	= ''
3852									return
3853								END
3854						END
3855
3856				END
3857
3858			--  --------------------------------------------------
3859			--	ADD &  UPDATE GRANT
3860			--  --------------------------------------------------
3861			IF @Command	in ( 'ACCESS_USER_ADD', 'ACCESS_USER_SET'  )
3862				BEGIN
3863
3864					-- Check : If OB & owner alredy exists...
3865					if  @ObjectType = 'OB'
3866						AND	Exists (    SELECT 1
3867										FROM UsersAccess  UA
3868											JOIN fn_DataReference( 'AccessRole', 'en', DEFAULT) fDR
3869														ON fDR.DataReferenceId		= UA.RoleId
3870													   AND fDR.DataReferenceCode	= @Sign_RoleOwner
3871										WHERE UA.ObjectId		= @ObjectId
3872											AND UA.ObjectTypeId	= @mObjectTypeId
3873											AND UA.RoleId		= @AccessToRoleId
3874									)
3875							BEGIN
3876								SET @ResultMessage = 'Owner for this business alredy exists !'
3877
3878								SELECT  XMLHTML		= '-1',
3879										TEMPLATE	= ''
3880
3881								return
3882							END
3883
3884					-- This row alredy exist in database. NOT CHANGED !
3885					IF Exists (		Select 1
3886									from UsersAccess  UA
3887									WHERE UA.UserId			= @AccessToUserId
3888										AND UA.ObjectId		= @ObjectId
3889										AND UA.ObjectTypeId	= @mObjectTypeId
3890										AND UA.RoleId		= @AccessToRoleId
3891										AND ISNULL( UA.LanguageId, 0 ) = ISNULL( @AccessToLanguageId, 0 )
3892								)
3893						BEGIN
3894								SET @ResultMessage = 'This user & access-grant for this object alredy exists !'
3895
3896								SELECT  XMLHTML		= -1,
3897										TEMPLATE	= ''
3898
3899								return
3900						END
3901
3902					IF @Command = 'ACCESS_USER_ADD'								-- ADD Access
3903						BEGIN
3904							INSERT INTO UsersAccess  (	UserId		,
3905														ObjectTypeId,
3906														ObjectId	,
3907														LanguageId	,
3908														RoleId		)
3909							VALUES(  	@AccessToUserId					,
3910										@mObjectTypeId					,
3911										@ObjectId						,
3912										CASE WHEN ISNULL(@AccessToLanguageId,0) = 0 THEN NULL ELSE @AccessToLanguageId END,
3913										@AccessToRoleId				)
3914
3915							SET @mRowCount = @@ROWCOUNT
3916
3917							if @mRowCount = 0
3918								BEGIN
3919									SET @ResultMessage = 'DataBase: Add user grant failed !'
3920
3921									SELECT  XMLHTML		= -1,
3922											TEMPLATE	= ''
3923
3924									return
3925								END
3926
3927						END
3928
3929					IF @Command = 'ACCESS_USER_SET'								-- UPDATE Access
3930						BEGIN
3931
3932							UPDATE UsersAccess
3933							SET	RoleId		= @AccessToRoleId,
3934								LanguageId	= CASE WHEN ISNULL(@AccessToLanguageId,0) = 0 THEN NULL ELSE @AccessToLanguageId END
3935							WHERE UserId				  = @AccessToUserId
3936								AND ObjectId			  = @ObjectId
3937								AND ObjectTypeId		  = @mObjectTypeId
3938								AND RoleId				  = @OldAccessRoleId
3939								AND ISNULL( LanguageId, 0 ) = ISNULL( @OldAccessLanguageId, 0 )
3940
3941							SET @mRowCount = @@ROWCOUNT
3942
3943							if @mRowCount = 0
3944								BEGIN
3945									SET @ResultMessage = 'DataBase : Delete failed ! Record not found!'
3946
3947									SELECT  XMLHTML		= -1,
3948											TEMPLATE	= ''
3949
3950									return
3951								END
3952
3953						END
3954
3955					--	Check Set\Add result -------------------------------------
3956 					SET @ResultMessage = ''
3957
3958 					SELECT @ResultHTML  = '( { "Id":'					+ @AccessToRoleId					  + ','
3959 										+ '    "AccessRoleName":"'		+ ISNULL( AR.DataReferenceName, 'nbsp;')+ '",'
3960 										+ '    "AccessRoleCode":"'		+ ISNULL( AR.DataReferenceCode, 'nbsp;')+ '",'
3961 										+ '    "AccessToLanguageId":"'	+ ISNULL( @AccessToLanguageId, '0')+ '",'
3962	 									+ '	   "DistributingLang":'		+ QR.DistributingLang				   +
3963										+ '  } )'
3964					FROM  fn_DataReference( 'AccessRole', @Language, DEFAULT)   AR
3965						JOIN  (
3966								SELECT	top 1  -- ÓÔ‰ÂΡÂÏ Í‡ÍË ˇÁ˚ÍË ‰ÓÒÚÛÔÌ˚ ‰Îˇ ÔθÁÓ‚‡ÚÂΡ, ÍÓÚÓ˚È ‡Á‰‡ÂÚ Ô‡‚‡
3967										DistributingLang = CONVERT( VARCHAR(15), (ISNULL( UA.LanguageId, 0 ) ) )
3968								FROM UsersAccess	UA
3969									JOIN  fn_DataReference( 'AccessObjects', 'en', DEFAULT) DR
3970														ON DR.DataReferenceCode = @ObjectType
3971													AND DR.DataReferenceId   = UA.ObjectTypeId
3972								WHERE UA.UserId		= @UserId
3973									AND UA.ObjectId	= @ObjectId
3974								order by UA.LanguageId
3975
3976							) QR ON 1=1
3977					WHERE AR.DataReferenceMasterId = @AccessToRoleId
3978
3979				END
3980
3981			SELECT  XMLHTML		= @ResultHTML,
3982					TEMPLATE	= ''
3983
3984			return
3985
3986		END	-- @Command	in ( 'ACCESS_USER_ADD', 'ACCESS_USER_DEL', 'ACCESS_USER_SET'  )
3987
3988
3989
3990------------------------------------------------------------------------------------------------------
3991	SELECT  XMLHTML		= @ResultHTML		,
3992			TEMPLATE	= @ResultTEMPLATE
3993
3994END
3995
3996-- The END ---
3997GO
3998PRINT N'Altering [AuditDDL]...';
3999
4000
4001GO
4002ENABLE TRIGGER [AuditDDL]
4003    ON DATABASE;
4004
4005
4006GO
4007PRINT N'Refreshing [dbo].[asAbuseManager]...';
4008
4009
4010GO
4011EXECUTE sp_refreshsqlmodule N'[dbo].[asAbuseManager]';
4012
4013
4014GO
4015PRINT N'Refreshing [dbo].[asMailQueueEditAdd]...';
4016
4017
4018GO
4019EXECUTE sp_refreshsqlmodule N'[dbo].[asMailQueueEditAdd]';
4020
4021
4022GO
4023PRINT N'Refreshing [dbo].[asMailManagement]...';
4024
4025
4026GO
4027EXECUTE sp_refreshsqlmodule N'[dbo].[asMailManagement]';
4028
4029
4030GO
4031PRINT N'Refreshing [dbo].[asSalesAgent]...';
4032
4033
4034GO
4035EXECUTE sp_refreshsqlmodule N'[dbo].[asSalesAgent]';
4036
4037
4038GO
4039PRINT N'Refreshing [dbo].[asAudit]...';
4040
4041
4042GO
4043EXECUTE sp_refreshsqlmodule N'[dbo].[asAudit]';
4044
4045
4046GO
4047PRINT N'Refreshing [dbo].[asContentReader]...';
4048
4049
4050GO
4051EXECUTE sp_refreshsqlmodule N'[dbo].[asContentReader]';
4052
4053
4054GO
4055PRINT N'Refreshing [dbo].[asUserAuth]...';
4056
4057
4058GO
4059EXECUTE sp_refreshsqlmodule N'[dbo].[asUserAuth]';
4060
4061
4062GO
4063PRINT N'Refreshing [dbo].[asUserLanguage]...';
4064
4065
4066GO
4067EXECUTE sp_refreshsqlmodule N'[dbo].[asUserLanguage]';
4068
4069
4070GO
4071PRINT N'Refreshing [dbo].[asRequestHandling]...';
4072
4073
4074GO
4075EXECUTE sp_refreshsqlmodule N'[dbo].[asRequestHandling]';
4076
4077
4078GO
4079PRINT N'Refreshing [dbo].[asRequestTimeout]...';
4080
4081
4082GO
4083EXECUTE sp_refreshsqlmodule N'[dbo].[asRequestTimeout]';
4084
4085
4086GO
4087PRINT N'Update complete.';
4088
4089
4090GO