USE [cloudERP]
GO
/****** Object:  StoredProcedure [db_masters].[pr_getitemcategorylist_test]    Script Date: 14/01/2020 10:52:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:	<Author,,Amit>
-- Create date: <Create Date,23-10-2018>
-- Description:	<Description,Get list of all user groups>
--[db_masters].[pr_update_item_code_initemmaster] 1,1,1
-- =============================================
create PROCEDURE [db_mm].[pr_update_item_code_initemmaster] 
@coid int,
@userid int = null,
@categoryid int=null
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#tree') IS NOT NULL DROP TABLE #tree
create table #tree 
(
ID INT,
name VARCHAR(100),
parentgroupid INT,
code varchar(20), 
categcode varchar(20),
maxitemid bigint,
lastitemcode varchar(15)
);

INSERT into #tree (id,name,parentgroupid,code) 
SELECT a.id,a.name, a.parentid,a.categorycode
from db_mm.mst_itemcategory a where a.coid=@coid
-- and id in (select itemcategoryid from db_mm.[itemcategories_userrights] where userid = coalesce(@userid,userid))

-- update maxitemid for the category


update #tree set maxitemid = a.itemid 
from 
(select itemcategoryid, max(id) itemid from db_mm.mst_item group by itemcategoryid) a 
where #tree.ID = a.itemcategoryid;

with ctc as (
SELECT  ROW_NUMBER() OVER(Partition by itemcategoryid ORDER BY id) AS RowNumber,* from db_mm.mst_item  
)
update db_mm.mst_item set itemimage =RowNumber 
from ctc  where ctc.id=db_mm.mst_item.id
-- update lastitemcode for the category
update #tree set lastitemcode = a.itemcode 
from 
db_mm.mst_item a 
where #tree.maxitemid = a.id;


WITH x AS
(
-- anchor:
SELECT ID, name, parentgroupid, code,
cast(left(isnull(code,'XX'),2) + 'XXXX' as varchar(6)) as categcode, cast(name as varchar(500)) as categname,
[level] = 0 , isnull(lastitemcode, cast(left(isnull(code,'XX'),2) + 'XXXX0000' as varchar(10))) lastitemcode 
FROM #tree WHERE isnull(parentgroupid,0)=0 and id in (SELECT id from db_mm.mst_itemcategory)
UNION ALL
-- recursive:
SELECT t.ID, t.name , t.parentgroupid,t.code, 
cast(case when x.[level]=0 then left(x.categcode,2) + left(t.code,2) + 'XX' else left(x.categcode,4) + left(t.code,2) end 
as varchar(6)) as categcode, 
cast(x.name + ' :==> ' + t.name as varchar(500)) as categname,
[level] = x.[level] + 1, t.lastitemcode
FROM x INNER JOIN #tree AS t
ON t.parentgroupid = x.ID 

)
update db_mm.mst_item set accessories=categcode
FROM x where x.id=db_mm.mst_item.itemcategoryid



update db_mm.mst_item set accessories=
case
when  len(itemimage)=1 then accessories +'0000' +itemimage
when  len(itemimage)=2 then accessories +'000'+itemimage
when  len(itemimage)=3 then accessories +'00' +itemimage
when  len(itemimage)=4 then accessories +'0'  +itemimage
else  accessories+itemimage  end 

DROP TABLE #tree;
--select id,name from db_mm.mst_dimension
END






