Datanory Production » Datanory - NIBOU

--USE [DMS_EcoSystem]

--GO

--/****** Object:  StoredProcedure [dbo].[sp_rpt_stock_count_history_report]    Script Date: 12/6/2024 12:01:52 PM ******/

--SET ANSI_NULLS ON

--GO

--SET QUOTED_IDENTIFIER ON

--GO

--ALTER procedure [dbo].[sp_rpt_stock_count_history_report]

declare

@stock_count_start_date date,

@stock_count_end_date date,

@branch varchar(max),

@area varchar(max),

@salesman varchar(max),

@tenant_id varchar(50),

@stockCountType nvarchar(200)

select @stock_count_start_date='2024-01-01 00:00:00',@stock_count_end_date='2024-06-10 00:00:00',

@branch=N'BDBA44360C224941833A9125EF87F9AB,07CF8ABF-956D-4C5C-B541-17ED15900347,EC9CBA299A3448C89DE0A43E51523DCE,3543556ACA4E4D8F9FBA2870BA2D1AE5,81DFF21F957F44C19D3CF86399E2867B,9997DBBA-C412-444A-8712-5273260C5373,CA3B8C87A89749AABF20FFEA0972F71B',@area=N'16FF6280C8544440B61DCD1A840D0207,2E03D6B4-B990-4FFE-8821-622E5025AA00,1E6C4AD0-9B3E-4CF9-B3D3-F9CDE0DA8D1C,A08F543987B945E091AD731C9608604F,87791E915FC449E58EAC4C9662DA39D6,D7742372-1B20-4721-927A-412869E583C0,EB8F61A3-7D4E-45E0-87EE-0A2A8B32EFF6,80165083-B164-4465-B00A-A0A283AFBDBC,7F0E13F1-BAAE-4AB1-B546-25D9886DAD18,820A7928A6164635A2A174D59B1261CC,88CDEBC3444E43D2A2B0A1692454078B,04C57D3D29A04ED9AAC70B8BB2AA0341,410343F6-DAE5-41F6-BB66-071C5ACFF4D8,FE07AE11-6A3D-4030-8FA0-DB60E5330BF9',@salesman=N'NIBOU-admin,ac3337e4-cec3-44f7-ae04-b55d8a603be0,ba7de64d9dea4fa39fee490cc732d500,27153f64bbea4a0ea28ae1c7bb981e9f,e0ce3e8f-f593-4265-904c-617ae4525ea5,44df41ab-f9e9-431c-8892-01ec2b2fe6f7,518d3ace-b940-4079-b981-ea72bdb383dd,d02112a5a86f4285a77056250a82aa98,d71824b6-a0c2-490e-9136-8208744c2a4c,82beaa9a25b34657879ac20010f2b295,416b5244-ee12-40d1-8932-eab7b269035b',

@tenant_id=N'NIBOU',

@stockCountType=N'Opening Stock,Closing Stock,Goods Receive Stock'

--version 2 changes 20210925 ES - change sorting by product

create table #azhar_report_performance

(

	id varchar(100),

	description  varchar(100),

	time datetime,

)

insert into #azhar_report_performance

select newid(),'start',getdate()

create table #area

(

area_id varchar(50) collate database_default

)

insert into #area

select * from mcc_fn_splitMVParam(@area,',')

create table #branch

(

branch_id varchar (50) collate DATABASE_DEFAULT

)

INSERT into #branch

select * from mcc_fn_splitMVParam(@branch,',')

create table #salesman

(

salesman_id varchar (50) collate DATABASE_DEFAULT

)

INSERT INTO #salesman select * from mcc_fn_splitMVParam(@salesman,',')

create table #stockCountType

(

stockCountType nvarchar(200) collate DATABASE_DEFAULT

)

INSERT INTO #stockCountType select * from mcc_fn_splitMVParam(@stockCountType,',')

create table #temp_mobile_txn_stock_count

(

	row_no int,

	txn_id varchar(50) collate database_default,

	stock_count_id varchar(50) collate database_default,

	customer_id varchar(50) collate database_default,

	product_id varchar(50) collate database_default,

	expiry_date datetime,

	remark nvarchar(max) collate database_default,

	uom_1 varchar(50),

	uom_2 varchar(50),

	uom_3 varchar(50),

	quantity_1 int,

	quantity_2 int,

	quantity_3 int,

	created_date datetime,

	stock_count_type_desc nvarchar(200)

)

insert into #azhar_report_performance

select newid(),'part1',getdate()

insert into #temp_mobile_txn_stock_count

select 

row_number() over (partition by customer_id,item_id order by s.created_date,txn_no desc) as row_no,

h.txn_id,s.stock_count_id, customer_id, item_id,

s.expiry_date, s.remark, uom1.product_uom_code as uom_1,uom2.product_uom_code as uom_2, uom3.product_uom_code as uom_3, s.quantity_1,s.quantity_2, s.quantity_3, s.created_date,

msct.stock_count_type_desc

from txn_mobile_header h

inner join txn_mobile_stock_count s on h.txn_id = s.txn_id

left join mst_product_uom uom1 on s.uom_1=uom1.product_uom_id

left join mst_product_uom uom2 on s.uom_2=uom2.product_uom_id

left join mst_product_uom uom3 on s.uom_3=uom3.product_uom_id

left join mst_stock_count_type msct on msct.stock_count_type_id = s.stock_count_type_id

insert into #azhar_report_performance

select newid(),'part2',getdate()

select c.customer_code, c.customer_name, mcc.customer_class_description, l1.customer_id, l1.product_id, p.item_name, b.branch_desc, a.area_description, 

m.member_cd, CONCAT(m.first_name, ' ', m.last_name) AS salesman_name, l1.expiry_date as exp_date,  l1.remark, l1.txn_id, b.branch_code, p.item_code,

l1.stock_count_type_desc,

l1.uom_1 as c1_last_count1, isnull(l1.quantity_1, 0) as c1_last_qty1, l1.created_date as c1_stock_date1, 

l1.uom_2 as c1_last_count2, isnull(l1.quantity_2, 0) as c1_last_qty2, l1.created_date as c1_stock_date2,

l1.uom_3 as c1_last_count3, isnull(l1.quantity_3, 0) as c1_last_qty3, l1.created_date as c1_stock_date3,

l2.uom_1 as c2_last_count1, isnull(l2.quantity_1,0) as c2_last_qty1, l2.created_date as c2_stock_date1, 

l2.uom_2 as c2_last_count2, isnull(l2.quantity_2,0) as c2_last_qty2 , l2.created_date as c2_stock_date2,

l2.uom_3 as c2_last_count3, isnull(l2.quantity_3,0) as c2_last_qty3 , l2.created_date as c2_stock_date3,

l3.uom_1 as c3_last_count1, isnull(l3.quantity_1,0) as c3_last_qty1, l3.created_date as c3_stock_date1, 

l3.uom_2 as c3_last_count2, isnull(l3.quantity_2,0) as c3_last_qty2 , l3.created_date as c3_stock_date2,

l3.uom_3 as c3_last_count3, isnull(l3.quantity_3,0) as c3_last_qty3 , l3.created_date as c3_stock_date3

from #temp_mobile_txn_stock_count l1

left join #temp_mobile_txn_stock_count l2 on l1.row_no =l2.row_no + 1 and l1.customer_id=l2.customer_id and l1.product_id=l2.product_id

left join #temp_mobile_txn_stock_count l3 on l2.row_no =l3.row_no + 1 and l2.customer_id=l3.customer_id and l2.product_id=l3.product_id

INNER JOIN txn_mobile_header mh ON mh.customer_id = l1.customer_id and l1.txn_id=mh.txn_id

INNER JOIN txn_mobile_check_in mc ON mc.txn_id = mh.txn_id

inner join customer c on c.customer_id = l1.customer_id

left join mst_customer_class mcc on c.customer_class_id = mcc.customer_class_id

INNER JOIN product_item	p ON l1.product_id = p.item_id

inner join mst_branch b on b.branch_id = c.branch_id

inner join mst_area a on a.area_id = c.area_id

inner join member m on m.member_id = mh.salesman_id

WHERE cast(l1.created_date as date) BETWEEN @stock_count_start_date AND @stock_count_end_date

AND exists (select 1 from #branch b1 where b.branch_id = b1.branch_id)

AND exists (select 1 from #area a1 where a.area_id = a1.area_id)

AND exists (select 1 from #salesman s1 where m.member_id = s1.salesman_id)

AND exists (select 1 from #stockCountType sct1 where l1.stock_count_type_desc = sct1.stockCountType)

AND mh.tenant_id = @tenant_id

--version 2 changes

--ORDER BY  l1.customer_id,l1.product_id,l1.created_date DESC

ORDER BY  l1.customer_id,p.sorting,l1.created_date DESC

--end version 2 changes

insert into #azhar_report_performance

select newid(),'end',getdate()

DROP TABLE #area

DROP TABLE #branch

DROP TABLE #salesman

DROP TABLE #stockCountType

DROP TABLE #temp_mobile_txn_stock_count

--drop table #azhar_report_performance

select * from #azhar_report_performance