Notes

USE [NominInventory]
GO
/****** Object: StoredProcedure [dbo].[SEL_MobileItemInfo] Script Date: 6/25/2024 13:02:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SEL_MobileItemInfo]
@Type NVARCHAR(30),
@LocationKey BIGINT,
@UserKey BIGINT,
@Index INT,
@Latitude NVARCHAR(30),
@Longitude NVARCHAR(30),
@DeviceId NVARCHAR(60),
@SearchValue NVARCHAR(150) = '',
@VendorKey BIGINT = 0,
@IsBarCode CHAR(1) = '',
@SectionKey BIGINT = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @GeneralKey BIGINT,
@Config NVARCHAR(10),
@ConfigHour INT,
@ConfigMinute INT,
@NowHour INT,
@NowMinute INT,
@BeginDate DATETIME,
@EndDate DATETIME,
@NowDate DATE,
@Json NVARCHAR(MAX),
@SQL NVARCHAR(MAX),
@HasSection INT = 0,
@ServerDate DATE,
@LocationType INT = 0

CREATE TABLE #tmp_hour_minute(
Id INT IDENTITY(1, 1),
Val NVARCHAR(5)
)
CREATE TABLE #tmp_order_item(
ItemKey BIGINT,
ItemBarCodeKey BIGINT,
OrderDate DATE,
OrderQty NUMERIC(24, 2)
)
CREATE TABLE #tmp_items(
QuotationQty INT,
ItemKey BIGINT,
ItemId NVARCHAR(150),
ItemName NVARCHAR(250),
ItemBarCodeKey BIGINT,
ItemBarCodeId NVARCHAR(250),
ItemCategoryKey BIGINT,
VendorKey BIGINT,
EndQty NUMERIC(24, 2),
WhEndQty NUMERIC(24, 2),
QuantityInBox INT,
GroupName NVARCHAR(150),
BarCodeBox CHAR(1)
)
CREATE TABLE #tmp_item_all(
ItemKey BIGINT,
ItemId NVARCHAR(150),
ItemName NVARCHAR(250),
ItemCategoryKey BIGINT,
VendorKey BIGINT,
EndQty NUMERIC(24, 2),
WhEndQty NUMERIC(24, 2),
QuantityInBox INT,
GroupName NVARCHAR(150)
)
CREATE TABLE #tmp_ItemSection (
ItemKey BIGINT,
SectionKey BIGINT,
RN INT
)

CREATE INDEX #tmp_ItemSection_indx0 ON #tmp_ItemSection(ItemKey)
SELECT @LocationType = V.TypeKey FROM nmnLocations N WITH(NOLOCK) INNER JOIN VIEW_LocationAndType V WITH(NOLOCK) ON V.LocationKey = N.PKey WHERE N.Pkey = @LocationKey
SET @ServerDate = GETDATE()

--A.Pkey ItemKey, A.Name ItemName, A.QuantityInBox, A.ID ItemId, A.ItemCategoryKey, B.EndQty, B.WhEndQty, N''Бараанууд'' GroupName, A.VendorKey
SELECT @NowHour = DATEPART(HOUR, GETDATE());
SELECT @NowMinute = DATEPART(MINUTE, GETDATE());

SELECT @Config = ISNULL(Value, '15:00') FROM Configuration WHERE Id = 'OrderDateTime' AND DivisionKey = -1

INSERT INTO #tmp_hour_minute (Val)
SELECT value from STRING_SPLIT(@Config, ':')
SELECT @ConfigHour = CAST(Val AS INT) FROM #tmp_hour_minute WHERE Id = 1
SELECT @ConfigMinute = CAST(Val AS INT) FROM #tmp_hour_minute WHERE Id = 2

IF(@NowHour > @ConfigHour OR (@NowHour = @ConfigHour AND @NowMinute >= @ConfigMinute))
BEGIN
SET @NowDate = GETDATE()
SET @EndDate = DATETIMEFROMPARTS(DATEPART(YEAR, @NowDate), DATEPART(MONTH, @NowDate), DATEPART(DAY, @NowDate), 14, 59, 59, 0)
SET @NowDate = DATEADD(DAY, -1, GETDATE())
SET @BeginDate = DATETIMEFROMPARTS(DATEPART(YEAR, @NowDate), DATEPART(MONTH, @NowDate), DATEPART(DAY, @NowDate), 15, 0, 0, 0)
END
ELSE BEGIN
SET @NowDate = DATEADD(DAY, -2, GETDATE())
SET @BeginDate = DATETIMEFROMPARTS(DATEPART(YEAR, @NowDate), DATEPART(MONTH, @NowDate), DATEPART(DAY, @NowDate), 15, 0, 0, 0)
SET @NowDate = DATEADD(DAY, -1, GETDATE())
SET @EndDate = DATETIMEFROMPARTS(DATEPART(YEAR, @NowDate), DATEPART(MONTH, @NowDate), DATEPART(DAY, @NowDate), 14, 59, 59, 0)
END

IF(@Type = 'QuotationList')
BEGIN
INSERT INTO MobileActionLog
VALUES (@UserKey, @LocationKey, GETDATE(), @Longitude, @Latitude, @DeviceId, 'GetQuotationList' , N'Захиалгын санал болгох жагсаалт', '' ,'')

IF ISNULL(@SearchValue, '') = ''
BEGIN
SELECT CAST(ISNULL(A.QuotationQty, 0) AS INT) AS ItemQty, A.ItemKey, B.VendorKey, A.EndQty, A.WhEndQty
INTO #tmp_quotation_item
FROM PrepareQuotationOrder A WITH(NOLOCK)
INNER JOIN nmnItems B WITH(NOLOCK) ON a.ItemKey = B.PKey AND B.IsActive = 1
WHERE B.VendorKey <> 0 AND A.[WeekDay] = 1 AND A.QuotationQty > 0 AND LocationKey = @LocationKey
GROUP BY B.VendorKey, ISNULL(A.QuotationQty, 0), A.ItemKey, A.EndQty, A.WhEndQty
ORDER BY B.VendorKey ASC OFFSET @Index * 100 ROWS FETCH NEXT 20 ROWS ONLY

SELECT ROW_NUMBER()OVER(PARTITION BY A.ItemKey ORDER BY B.PKey) AS RowNo, A.*, B.PKey AS ItemBarCodeKey
INTO #tmp_quotation_itemBarCode
FROM #tmp_quotation_item A
LEFT JOIN nmnItemBarcodes B WITH(NOLOCK) ON A.ItemKey = B.ItemKey

DELETE FROM #tmp_quotation_itemBarCode WHERE RowNo > 1
INSERT INTO #tmp_items (QuotationQty, ItemKey, VendorKey, EndQty, WhEndQty, ItemBarCodeKey)
SELECT ItemQty, ItemKey, VendorKey, EndQty, WHEndQty, ItemBarCodeKey FROM #tmp_quotation_itemBarCode

END
ELSE BEGIN
IF @IsBarCode = 'Y'
BEGIN
INSERT INTO #tmp_items (QuotationQty, ItemKey, VendorKey, EndQty, WhEndQty, ItemBarCodeKey)
SELECT CAST(ISNULL(C.QuotationQty, 0) AS INT), A.ItemKey, B.VendorKey, C.EndQty, C.WhEndQty, A.PKey
FROM nmnItemBarcodes A WITH(NOLOCK)
LEFT JOIN nmnItems B WITH(NOLOCK) ON a.ItemKey = B.PKey AND B.IsActive = 1
LEFT JOIN PrepareQuotationOrder C WITH(NOLOCK) ON B.PKey = C.ItemKey
WHERE A.BarCodeID = @SearchValue OR B.ID = @SearchValue AND B.VendorKey <> 0 AND C.[WeekDay] = 1 AND ISNULL(C.QuotationQty, 0) > 0 AND LocationKey = @LocationKey
END
ELSE BEGIN
SET @SQL = N'
INSERT INTO #tmp_items (QuotationQty, ItemKey, VendorKey, EndQty, WhEndQty)
SELECT CAST(ISNULL(A.QuotationQty, 0) AS INT), A.ItemKey, B.VendorKey, A.EndQty, A.WhEndQty
FROM PrepareQuotationOrder A WITH(NOLOCK)
LEFT JOIN nmnItems B WITH(NOLOCK) ON a.ItemKey = B.PKey AND B.IsActive = 1
LEFT JOIN nmnItemBarcodes C WITH(NOLOCK) ON B.PKey = C.ItemKey
WHERE B.VendorKey <> 0 AND A.[WeekDay] = 1 AND ISNULL(A.QuotationQty, 0) > 0
AND LocationKey = ' + CAST(@LocationKey AS NVARCHAR(30)) + '
AND (B.Id LIKE ''%' + @SearchValue + '%'' OR B.Name LIKE ''%' + @SearchValue + '%'' OR C.BarCodeID LIKE ''%' + @SearchValue + '%'')
GROUP BY B.VendorKey, ISNULL(A.QuotationQty, 0), A.ItemKey, A.EndQty, A.WhEndQty
ORDER BY B.VendorKey ASC OFFSET ' + CAST(@Index * 20 AS NVARCHAR(10)) + ' ROWS FETCH NEXT 20 ROWS ONLY'
--PRINT (@SQL)
EXEC (@SQL)
END
END
INSERT INTO #tmp_order_item
SELECT A.ItemKey, A.ItemBarCodeKey, MAX(A.OrderDate) OrderDate, SUM(A.OrderQty) OrderQty
FROM OrderDtl A WITH(NOLOCK)
INNER JOIN #tmp_items C ON A.ItemKey = C.ItemKey
WHERE A.CreatedDate BETWEEN @BeginDate AND @EndDate AND A.IsDeleted = 0 AND A.OrderStatus > 0 --AND A.LocationKey = @LocationKey
GROUP BY A.ItemKey, A.ItemBarCodeKey

SET @Json = (
SELECT c.CustomerInfID AS custPkey, C.RegistryNumber AS custReg, C.DescriptionL AS custName,
ISNULL(CONVERT(VARCHAR(10), MAX(F.OrderDate), 103), '') AS custLastDate,
B.PKey AS itemBkey, A.ItemKey AS itemPkey, E.Name AS itemName, CASE WHEN ISNULL(B.Qty, 0) > 1 THEN B.Qty ELSE CAST(A.QuotationQty AS INT) END AS itemQty,
E.ID AS itemId, B.BarCodeID as itemBarcode,
CAST(ISNULL(E.QuantityInBox, 0) as INT) AS boxInQty, A.EndQty endQty, A.WhEndQty whEndQty, ISNULL(F.OrderQty, 0) AS orderQty,
CASE WHEN ISNULL(B.Qty, 0) > 1 THEN 'Y' ELSE 'N' END barCodeBox
FROM #tmp_items A
INNER JOIN nmnItemBarcodes B WITH(NOLOCK) ON A.ItemBarCodeKey = b.PKey
INNER JOIN vCustomers C WITH(NOLOCK) ON A.VendorKey = c.CustomerInfID
INNER JOIN nmnItems E WITH(NOLOCK) ON A.ItemKey = e.PKey AND e.IsActive = 1
LEFT JOIN #tmp_order_item F WITH(NOLOCK) ON A.ItemKey = F.ItemKey AND F.ItemBarCodeKey = B.PKey
GROUP BY C.CustomerInfID, C.RegistryNumber, C.DescriptionL, B.PKey, A.ItemKey, E.Name, A.QuotationQty, E.ID, B.BarCodeID, E.QuantityInBox, A.EndQty, A.WhEndQty, F.OrderQty, B.Qty
FOR JSON PATH)
SELECT @Json
END
IF(@Type = 'CustomerItems')
BEGIN
INSERT INTO MobileActionLog
VALUES (@UserKey, @LocationKey, GETDATE(), @Longitude, @Latitude, @DeviceId, 'CustomerItems' , N'Харилцагчийн барааны мэдээлэл харах', '' ,'')
--INNER JOIN OrderNonAutoBrand Z ON Z.PKey = A.ItemCategoryKey AND Z.Type=''category''
SET @SQL = N'
INSERT INTO #tmp_items (ItemKey, ItemName, QuantityInBox, ItemId, ItemCategoryKey, EndQty, WhEndQty, VendorKey)
SELECT A.PKey ItemKey, A.Name ItemName, ISNULL(A.QuantityInBox, 0) QuantityInBox, A.ID ItemId, A.ItemCategoryKey, B.EndQty, B.WhEndQty, A.VendorKey
FROM nmnItems A WITH(NOLOCK)
LEFT JOIN nmnLocationItemMap B WITH(NOLOCK) on A.PKey = B.ItemKey
WHERE A.VendorKey = ' + CAST(@VendorKey AS NVARCHAR(30)) + ' AND B.LocationKey = ' + CAST(@LocationKey AS NVARCHAR(30)) + ' AND A.IsActive = 1 ' +
CASE WHEN ISNULL(@SearchValue, '') = '' THEN '' ELSE ' AND (A.Id LIKE ''%' + @SearchValue + '%'' OR A.Name LIKE ''%' + @SearchValue + '%'' OR E.BarCodeId LIKE ''%' + @SearchValue + '%'') ' END + '
GROUP BY A.PKey, A.Name, A.QuantityInBox, A.ID, A.ItemCategoryKey, B.EndQty, B.WhEndQty, A.VendorKey
ORDER BY A.Name OFFSET ' + CAST(@Index * 20 AS NVARCHAR(10)) + ' ROWS FETCH NEXT 20 ROWS ONLY'
EXEC (@SQL)

SELECT ROW_NUMBER()OVER(PARTITION BY A.ItemKey ORDER BY B.PKey) AS RowNo, A.*, B.PKey AS BarCodeKey, B.BarCodeID BarCodeId, ISNULL(B.Qty, 0) BoxInQty
INTO #tmp_customer_itemBarCode
FROM #tmp_items A LEFT JOIN nmnItemBarcodes B WITH(NOLOCK) ON A.ItemKey = B.ItemKey
DELETE FROM #tmp_customer_itemBarCode WHERE RowNo > 1

TRUNCATE TABLE #tmp_items
INSERT INTO #tmp_items (ItemKey, ItemName, QuantityInBox, ItemId, ItemCategoryKey, EndQty, WhEndQty, ItemBarCodeKey, ItemBarCodeId, BarCodeBox, VendorKey)
SELECT ItemKey, ItemName, CASE WHEN BoxInQty > 1 THEN BoxInQty ELSE QuantityInBox END, ItemId, ItemCategoryKey, EndQty, WhEndQty,
BarCodeKey, BarCodeId, CASE WHEN BoxInQty > 1 THEN 'Y' ELSE 'N' END, VendorKey
FROM #tmp_customer_itemBarCode

INSERT INTO #tmp_order_item
SELECT A.ItemKey, A.ItemBarCodeKey, MAX(A.OrderDate) OrderDate, SUM(A.OrderQty) OrderQty
FROM OrderDtl A WITH(NOLOCK)
INNER JOIN #tmp_items C ON A.ItemKey = C.ItemKey
WHERE A.CreatedDate BETWEEN @BeginDate AND @EndDate AND A.IsDeleted = 0 AND A.OrderStatus > 0 --AND A.LocationKey = @LocationKey
GROUP BY A.ItemKey, A.ItemBarCodeKey

SET @Json = (
SELECT A.ItemKey AS pkey, A.ItemBarCodeKey AS bkey, A.ItemName AS name,
ISNULL(B.Name, '') AS groupName, CAST(ISNULL(A.QuantityInBox, 0) as INT) boxInQty,
ISNULL(D.QuotationQty, 0) AS qty, A.ItemId AS itemId, A.ItemBarCodeId AS barcode,
ISNULL(CONVERT(VARCHAR(10), MAX(F.OrderDate), 103),'') AS orderDate, A.EndQty AS endQty,
A.WhEndQty AS whEndQty, ISNULL(F.OrderQty, 0) AS orderQty, A.BarCodeBox barCodeBox, ISNULL(H.SourceKey, '0') sourceKey ,
CASE WHEN @LocationType = 19 THEN ISNULL(C.WHDisplayQty, 0) ELSE
CASE WHEN @LocationType = 18 THEN ISNULL(C.HyperDisplayQty, 0) ELSE ISNULL(C.SuperDisplayQty, 0) END
END AS itemQty
FROM #tmp_items A WITH(NOLOCK)
LEFT JOIN vItemCategories B WITH(NOLOCK) ON A.ItemCategoryKey = B.Pkey
LEFT JOIN #tmp_order_item F WITH(NOLOCK) ON A.ItemKey = F.ItemKey AND F.ItemBarCodeKey = A.ItemBarCodeKey
LEFT JOIN PrepareQuotationOrder D WITH(NOLOCK) ON A.ItemKey = D.ItemKey AND D.LocationKey = @LocationKey
LEFT JOIN vCustomers H WITH(NOLOCK) ON A.VendorKey = H.CustomerInfID
LEFT JOIN CategoryTransportConfig C WITH(NOLOCK) ON C.CategoryKey = A.ItemCategoryKey AND C.ItemKey IS NULL
GROUP BY A.ItemKey, A.ItemBarCodeKey, A.ItemName, B.Name, A.ItemId, A.ItemBarCodeId, A.QuantityInBox, A.EndQty, A.WhEndQty, F.OrderQty, D.QuotationQty, A.ItemBarCodeKey, A.BarCodeBox, H.SourceKey, C.WHDisplayQty, C.HyperDisplayQty, C.SuperDisplayQty
ORDER BY a.ItemName
FOR JSON PATH)
SELECT @Json
END
IF(@Type = 'ItemsList')
BEGIN
IF PATINDEX('%[A-Z]%', @SearchValue) = 0 AND ISNULL(@SearchValue, '') <> ''
BEGIN
SET @isBarCode = 'Y'
END
INSERT INTO MobileActionLog
VALUES (@UserKey, @LocationKey, GETDATE(), @Longitude, @Latitude, @DeviceId, 'ItemsList' , N'Барааны мэдээлэл харах', '' ,'')

IF EXISTS (SELECT TOP 1 1 FROM userSectionMap WITH(NOLOCK) WHERE userkey = @userKey)
SET @HasSection = 1

IF @IsBarCode = 'Y'
BEGIN
SET @SQL = '
INSERT INTO #tmp_items(ItemKey, ItemBarCodeKey, ItemBarCodeId, BarCodeBox, QuantityInBox, VendorKey, ItemCategoryKey, ItemId, ItemName)
SELECT A.ItemKey, A.PKey, A.BarCodeID, CASE WHEN ISNULL(A.Qty, 0) > 1 THEN ''Y'' ELSE ''N'' END, ISNULL(A.Qty, ISNULL(B.QuantityInBox, 0)), B.VendorKey, B.ItemCategoryKey, B.Id, B.Name
FROM nmnItemBarcodes A WITH(NOLOCK)
INNER JOIN nmnItems B WITH(NOLOCK) ON A.ItemKey = B.Pkey '
--+ CASE WHEN @SectionKey <> 0 THEN ' INNER JOIN OrderNonAutoBrand Z ON Z.PKey = B.ItemCategoryKey AND Z.Type=''category'' ' ELSE '' END
+ CASE WHEN @SectionKey <> 0 THEN 'INNER JOIN imItemAndSections C WITH(NOLOCK) ON B.PKey = C.ItemKey ' ELSE '' END +
--' WHERE A.BarCodeID = ''' + @SearchValue + ''' AND B.IsActive = 1 '
' WHERE B.IsActive = 1 AND A.BarCodeID = ''' + @SearchValue + '''
AND B.vendorkey NOT IN (SELECT CustomerKey FROM orderCustomerConfig WITH(NOLOCK))'
+ CASE WHEN @SectionKey <> 0 THEN 'AND C.SectionKey = ' + CONVERT(NVARCHAR(20), @SectionKey) + '' ELSE '' END

PRINT(@SQL)
EXEC(@SQL)
IF NOT EXISTS(SELECT TOP 1 1 FROM #tmp_items)
BEGIN
SET @SQL = REPLACE(@SQL, ' AND A.BarCodeID', ' AND B.ID')
PRINT(@SQL)
EXEC(@SQL)
END

UPDATE B SET B.EndQty = ISNULL(E.EndQty, 0), B.WhEndQty = ISNULL(E.WHEndQty, 0), B.GroupName = N'Бараанууд'
FROM #tmp_items B WITH(NOLOCK)
LEFT JOIN nmnLocationItemMap E WITH(NOLOCK) ON E.LocationKey = @LocationKey AND B.ItemKey = E.ItemKey
END

ELSE --IF @SearchValue = ''
BEGIN
--IF @HasSection = 1
--BEGIN


SET @SQL = N'
INSERT INTO #tmp_ItemSection
SELECT e.ItemKey, sc.SectionKey, ROW_NUMBER()OVER(PARTITION BY e.ItemKey ORDER by e.IsMainSections DESC) AS RN
FROM UserSectionMap sc WITH(NOLOCK)
INNER JOIN imItemAndSections E WITH(NOLOCK) ON sc.SectionKey = E.SectionKey
INNER JOIN nmnItems A WITH(NOLOCK) ON E.ItemKey = A.pKey AND A.IsActive=1
INNER JOIN automationList L WITH(NOLOCK) ON L.pKey = A.pKey AND L.EndDate >= ''' + CONVERT(NVARCHAR, @ServerDate, 120) + '''
WHERE SC.UserKey = ' + CONVERT(NVARCHAR(20), @UserKey) + ' AND SC.IsDeleted = 0 AND A.vendorkey NOT IN (SELECT CustomerKey FROM orderCustomerConfig WITH(NOLOCK))
AND SC.locationKey = ' + CONVERT(NVARCHAR(20), @LocationKey) + ''
+ CASE WHEN @SectionKey <> 0 THEN ' AND sc.SectionKey = ' + CONVERT(NVARCHAR(20), @SectionKey) + ' ' ELSE '' END +
+ CASE WHEN @SectionKey = 0 THEN ' AND (A.Id LIKE N''%' + @SearchValue + '%'' OR A.Name LIKE N''%' + @SearchValue + '%'') ' ELSE '' END +
'ORDER BY e.Itemkey OFFSET ' + CONVERT(NVARCHAR(10), @Index) + ' * 20 ROWS FETCH NEXT 20 ROWS ONLY
'
--+ CASE WHEN @SearchValue <> '' THEN ' AND (A.Id LIKE N''%' + @SearchValue + '%'' OR A.Name LIKE N''%' + @SearchValue + '%'') ' ELSE '' END +

PRINT (@SQL)
EXEC (@SQL)
DELETE FROM #tmp_ItemSection WHERE RN > 1

INSERT INTO #tmp_item_all (ItemKey, ItemId, ItemName, ItemCategoryKey, VendorKey, QuantityInBox, GroupName)
SELECT A.Pkey ItemKey, A.ID ItemId, A.Name ItemName, A.ItemCategoryKey, A.VendorKey, A.QuantityInBox, N'Бараанууд'
FROM nmnItems A WITH(NOLOCK)
INNER JOIN #tmp_ItemSection D ON A.PKey = D.ItemKey
WHERE A.IsActive = 1 AND A.vendorkey NOT IN (SELECT CustomerKey FROM orderCustomerConfig WITH(NOLOCK))



--END

--ELSE BEGIN
-- SET @SQL = N'
-- INSERT INTO #tmp_item_all (ItemKey, ItemId, ItemName, ItemCategoryKey, VendorKey, QuantityInBox, GroupName)
-- SELECT A.Pkey ItemKey, A.ID ItemId, A.Name ItemName, A.ItemCategoryKey, A.VendorKey, A.QuantityInBox, N''Бараанууд''
-- FROM nmnItems A WITH(NOLOCK)
-- WHERE A.IsActive = 1 '
-- + CASE WHEN @SearchValue <> '' THEN N'AND (A.Id LIKE N''%' + @SearchValue + '%'' OR A.Name LIKE N''%' + @SearchValue + '%'') ' ELSE '' END +
-- 'ORDER BY A.Pkey OFFSET ' + CONVERT(NVARCHAR(10), @Index) + ' * 20 ROWS FETCH NEXT 20 ROWS ONLY
-- '
-- PRINT (@SQL)
-- EXEC (@SQL)
--END
UPDATE A SET A.EndQty = ISNULL(B.EndQty, 0), A.WhEndQty = ISNULL(B.WhEndQty, 0) FROM #tmp_item_all A
LEFT JOIN nmnLocationItemMap B WITH(NOLOCK) ON B.ItemKey = A.ItemKey AND B.LocationKey = @LocationKey AND (B.EndQty <> 0 OR B.WHEndQty <> 0)

SELECT ROW_NUMBER()OVER(PARTITION BY A.ItemKey ORDER BY B.PKey) AS RowNo, A.*, B.PKey AS ItemBarCodeKey, B.BarCodeID ItemBarCodeId,
CASE WHEN B.Qty > 1 THEN B.Qty ELSE A.QuantityInBox END AS BoxInQty, CASE WHEN B.Qty > 1 THEN 'Y' ELSE 'N' END BarCodeBox
INTO #tmp_item_itemBarCode3
FROM #tmp_item_all A
LEFT JOIN nmnItemBarcodes B WITH(NOLOCK) ON A.ItemKey = B.ItemKey

DELETE FROM #tmp_item_itemBarCode3 WHERE RowNo > 1

INSERT INTO #tmp_items (ItemKey, ItemName, QuantityInBox, ItemId, ItemCategoryKey, EndQty, WhEndQty, ItemBarCodeKey, ItemBarCodeId, GroupName, VendorKey, BarCodeBox)
SELECT ItemKey, ItemName, BoxInQty, ItemId, ItemCategoryKey, EndQty, WHEndQty, ItemBarCodeKey, ItemBarCodeId, GroupName, VendorKey, BarCodeBox FROM #tmp_item_itemBarCode3


END
-- ELSE BEGIN
--SET @SQL = '
-- INSERT INTO #tmp_item_all (ItemKey, ItemId, ItemName, ItemCategoryKey, VendorKey, EndQty, WhEndQty, QuantityInBox, GroupName)
-- SELECT A.PKey ItemKey, A.ID ItemId, A.Name ItemName, A.ItemCategoryKey, A.VendorKey, B.EndQty, B.WHEndQty, A.QuantityInBox, N''Бараанууд'' GroupName
-- FROM nmnItems A WITH(NOLOCK) '
-- + CASE WHEN @HasSection = 1 THEN 'INNER JOIN imItemAndSections C WITH(NOLOCK) ON A.PKey = C.ItemKey ' ELSE '' END +
-- 'LEFT JOIN nmnLocationItemMap B WITH(NOLOCK) ON B.ItemKey = A.PKey AND B.LocationKey = ' + CONVERT(NVARCHAR(20), @LocationKey) + '
-- WHERE (A.Id = @SearchValue OR A.Name = @SearchValue) AND A.IsActive = 1 '
-- + CASE WHEN @HasSection = 1 AND @SectionKey <> 0 THEN 'AND C.SectionKey = @SectionKey ' ELSE '' END +
-- 'GROUP BY A.PKey, A.ID, A.Name, A.VendorKey, A.ItemCategoryKey, A.QuantityInBox, B.EndQty, B.WHEndQty
-- ORDER BY A.PKey OFFSET @' + CONVErT(NVARCHAR(10), @Index) + ' * 20 ROWS FETCH NEXT 20 ROWS ONLY
--'

IF NOT EXISTS (SELECT TOP 1 1 FROM #tmp_item_all) AND @IsBarCode = ''
BEGIN

--SELECT A.PKey ItemKey, A.ID ItemId, A.Name ItemName, A.VendorKey, A.ItemCategoryKey, A.QuantityInBox, N'Бараанууд' GroupName, C.PKey ItemBarCodeKey, C.BarCodeID ItemBarCodeId
-- INTO #tmp_info_item1
-- FROM nmnLocationItemMap B WITH(NOLOCK)
-- INNER JOIN nmnItems A WITH(NOLOCK) ON B.ItemKey = A.PKey
-- INNER JOIN nmnItemBarcodes C WITH(NOLOCK) ON A.PKey = C.ItemKey

--WHERE B.LocationKey = @LocationKey AND C.BarCodeID = @SearchValue AND A.IsActive = 1
--GROUP BY A.PKey, A.ID, A.Name, A.VendorKey, A.ItemCategoryKey, A.QuantityInBox, C.PKey, C.BarCodeID
--ORDER BY A.PKey OFFSET @Index * 20 ROWS FETCH NEXT 20 ROWS ONLY

SET @SQL = '
INSERT INTO #tmp_items(ItemKey, ItemBarCodeKey, ItemBarCodeId, BarCodeBox, QuantityInBox, VendorKey, ItemCategoryKey, ItemId, ItemName)
SELECT A.ItemKey, A.PKey, A.BarCodeID, CASE WHEN ISNULL(A.Qty, 0) > 1 THEN ''Y'' ELSE ''N'' END, ISNULL(A.Qty, ISNULL(B.QuantityInBox, 0)), B.VendorKey, B.ItemCategoryKey, B.ID, B.Name
FROM nmnItemBarcodes A
INNER JOIN nmnItems B ON A.ItemKey = B.Pkey '
+ CASE WHEN @SectionKey <> 0 THEN 'INNER JOIN imItemAndSections C WITH(NOLOCK) ON A.PKey = C.ItemKey ' ELSE '' END +
' WHERE B.IsActive = 1 AND A.BarCodeID = ''' + @SearchValue + ''''
+ CASE WHEN @SectionKey <> 0 THEN ' AND C.SectionKey = ' + CONVERT(NVARCHAR(20), @SectionKey) + '' ELSE '' END
PRINT(@SQL)
EXEC(@SQL)

IF NOT EXISTS(SELECT TOP 1 1 FROM #tmp_items)
BEGIN
SET @SQL = REPLACE(@SQL, ' AND A.BarCodeID = ', ' AND B.ID = ')
PRINT(@SQL)
EXEC(@SQL)
END

UPDATE B SET B.EndQty = ISNULL(E.EndQty, 0), B.WhEndQty = ISNULL(E.WHEndQty, 0), B.GroupName = N'Бараанууд'
FROM #tmp_items B WITH(NOLOCK)
LEFT JOIN nmnLocationItemMap E WITH(NOLOCK) ON B.ItemKey = E.ItemKey AND E.LocationKey = @LocationKey



--INSERT INTO #tmp_items(ItemKey, ItemId, ItemName, VendorKey, ItemCategoryKey, QuantityInBox, GroupName, ItemBarCodeKey)
--SELECT ItemKey, ItemId, ItemName, VendorKey, ItemCategoryKey, QuantityInBox, GroupName, ItemBarCodeKey FROM #tmp_info_item1

END

-- IF EXISTS (SELECT TOP 1 1 FROM #tmp_item_all)
-- BEGIN
-- SELECT ROW_NUMBER()OVER(PARTITION BY A.ItemKey ORDER BY B.PKey) AS RowNo, A.*, B.PKey AS ItemBarCodeKey, B.BarCodeID ItemBarCodeId,
-- CASE WHEN B.Qty > 1 THEN B.Qty ELSE A.QuantityInBox END AS BoxInQty, CASE WHEN B.Qty > 1 THEN 'Y' ELSE 'N' END BarCodeBox
-- INTO #tmp_itembarcode_info
-- FROM #tmp_item_all A
-- LEFT JOIN nmnItemBarcodes B WITH(NOLOCK) ON A.ItemKey = B.ItemKey

-- DELETE FROM #tmp_itembarcode_info WHERE RowNo > 1

-- INSERT INTO #tmp_items (ItemKey, ItemName, QuantityInBox, ItemId, ItemCategoryKey, EndQty, WhEndQty, ItemBarCodeKey, ItemBarCodeId, GroupName, VendorKey, BarCodeBox)
-- SELECT ItemKey, ItemName, BoxInQty, ItemId, ItemCategoryKey, EndQty, WHEndQty, ItemBarCodeKey, ItemBarCodeId, GroupName, VendorKey, BarCodeBox FROM #tmp_itembarcode_info
-- END

IF NOT EXISTS (SELECT TOP 1 1 FROM #tmp_item_all) AND @IsBarCode = ''
BEGIN

INSERT INTO #tmp_items(ItemKey, ItemId, ItemName, VendorKey, ItemCategoryKey, QuantityInBox, GroupName, ItemBarCodeKey)
SELECT A.PKey, A.ID, A.Name, A.VendorKey, A.ItemCategoryKey, A.QuantityInBox, N'Бараанууд', MAX(ISNULL(E.PKey, 0))
FROM nmnLocationItemMap B WITH(NOLOCK)
LEFT JOIN nmnItems A WITH(NOLOCK) ON B.ItemKey = A.PKey
LEFT JOIN nmnItemBarcodes E WITH(NOLOCK) ON A.PKey = E.ItemKey
WHERE B.LocationKey = @LocationKey AND (A.Id LIKE N'%' + @SearchValue + '%' OR A.Name LIKE N'%' + @SearchValue + '%')
AND A.IsActive = 1 AND A.vendorkey NOT IN (SELECT CustomerKey FROM orderCustomerConfig WITH(NOLOCK))
AND A.PKey NOT IN (SELECT AA.ItemKey FROM ItemInterrupted AA WITH(NOLOCK) WHERE AA.IsDeleted = 0 AND AA.ItemStatus IN (3, 5, 7) AND AA.DecisionStatus IN (2, 3) AND (AA.EndDate IS NULL OR AA.EndDate >= @ServerDate))
GROUP BY A.PKey, A.ID, A.Name, A.VendorKey, A.ItemCategoryKey, A.QuantityInBox
ORDER BY A.PKey OFFSET @Index * 20 ROWS FETCH NEXT 20 ROWS ONLY

UPDATE B SET B.EndQty = ISNULL(E.EndQty, 0), B.WhEndQty = ISNULL(E.WHEndQty, 0), B.ItemBarCodeId = ISNULL(C.BarCodeID, ''),
B.QuantityInBox = CASE WHEN ISNULL(C.Qty, 0) > 1 THEN C.Qty ELSE B.QuantityInBox END,
B.BarCodeBox = CASE WHEN ISNULL(C.Qty, 0) > 1 THEN 'Y' ELSE 'N' END
FROM #tmp_items B WITH(NOLOCK)
LEFT JOIN nmnLocationItemMap E WITH(NOLOCK) ON B.ItemKey = E.ItemKey AND E.LocationKey = @LocationKey
LEFT JOIN nmnItemBarcodes C WITH(NOLOCK) ON B.ItemBarCodeKey = C.PKey

END

-- UPDATE B SET B.EndQty = ISNULL(E.EndQty, 0), B.WhEndQty = ISNULL(E.WHEndQty, 0), B.ItemBarCodeId = ISNULL(C.BarCodeID, ''),
-- B.QuantityInBox = CASE WHEN ISNULL(C.Qty, 0) > 1 THEN C.Qty ELSE B.QuantityInBox END,
-- B.BarCodeBox = CASE WHEN ISNULL(C.Qty, 0) > 1 THEN 'Y' ELSE 'N' END
-- FROM #tmp_items B WITH(NOLOCK)
-- LEFT JOIN nmnLocationItemMap E WITH(NOLOCK) ON B.ItemKey = E.ItemKey AND E.LocationKey = @LocationKey
-- LEFT JOIN nmnItemBarcodes C WITH(NOLOCK) ON B.ItemBarCodeKey = C.PKey

-- END



INSERT INTO #tmp_order_item
SELECT A.ItemKey, A.ItemBarCodeKey, MAX(A.OrderDate) OrderDate, SUM(A.OrderQty) OrderQty
FROM OrderDtl A WITH(NOLOCK)
INNER JOIN #tmp_items C ON A.ItemKey = C.ItemKey
WHERE A.CreatedDate BETWEEN @BeginDate AND @EndDate AND A.IsDeleted = 0 AND A.OrderStatus > 0 --AND A.LocationKey = @LocationKey
GROUP BY A.ItemKey, A.ItemBarCodeKey

SET @Json = (SELECT A.ItemKey AS pkey, A.ItemBarCodeKey AS bkey, A.GroupName groupName, A.ItemName AS name, CAST(ISNULL(A.QuantityInBox, 0) AS INT) boxInQty,
ISNULL(D.QuotationQty, 0) AS qty, A.ItemId AS itemId, A.ItemBarCodeId AS barcode, ISNULL(CONVERT(VARCHAR(10), MAX(F.OrderDate), 23),'') orderDate,
ISNULL(H.DescriptionL, '') AS clientName, ISNULL(H.CustomerInfID, '0') AS clientKey, ISNULL(A.endQty, 0) AS endQty, ISNULL(A.whEndQty, 0) whEndQty, ISNULL(F.OrderQty, 0) orderQty, ISNULL(A.BarCodeBox, 'N') barCodeBox, ISNULL(H.SourceKey, '0') sourceKey,
CASE WHEN @LocationType = 19 THEN ISNULL(C.WHDisplayQty, 0) ELSE
CASE WHEN @LocationType = 18 THEN ISNULL(C.HyperDisplayQty, 0) ELSE ISNULL(C.SuperDisplayQty, 0) END
END AS itemQty
FROM #tmp_items A
LEFT JOIN #tmp_order_item F WITH(NOLOCK) ON A.ItemKey = F.ItemKey AND F.ItemBarCodeKey = A.ItemBarCodeKey
LEFT JOIN vCustomers H WITH(NOLOCK) ON A.VendorKey = H.CustomerInfID
LEFT JOIN PrepareQuotationOrder D WITH(NOLOCK) ON D.LocationKey = @LocationKey AND A.ItemKey = D.ItemKey
LEFT JOIN CategoryTransportConfig C WITH(NOLOCK) ON C.CategoryKey = A.ItemCategoryKey AND C.ItemKey IS NULL
--LEFT JOIN OrderNonAutoBrand Z WITH(NOLOCK) ON Z.PKey = A.ItemCategoryKey AND Z.Type = 'category' AND @SectionKey <> 0
LEFT JOIN "SMSERVER".NominHelper.[dbo].[nmnMustBeAndDeducteItem_ForVendor] S WITH(NOLOCK) ON S.ItemKey <> A.ItemKey AND S.ItemStatus = 2 AND S.LocationKey = @LocationKey
GROUP BY A.ItemKey, A.ItemBarCodeKey, A.ItemName, A.ItemId, A.ItemBarCodeId, a.groupName, H.DescriptionL, H.CustomerInfID, A.QuantityInBox, a.endQty, a.whEndQty, F.OrderQty, D.QuotationQty, A.BarCodeBox , H.SourceKey, C.WHDisplayQty, C.HyperDisplayQty, C.SuperDisplayQty
ORDER BY A.groupName
FOR JSON PATH)
SELECT @Json
END
SET NOCOUNT OFF
END