--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