106 lines
4.1 KiB
Transact-SQL
106 lines
4.1 KiB
Transact-SQL
/*
|
||
库房管理库位分页查询脚本（测试库 ERPTOOL_JY_20250826Back）
|
||
修改人:Ld 修改时间:2026-07-31 14:19:37;
|
||
说明：将库房管理新模块使用的库位查询过程改为分页版，不修改原仓储库位页面。
|
||
*/
|
||
|
||
SET NOCOUNT ON;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'dbo.库房管理_库位_查询', N'P') IS NOT NULL
|
||
DROP PROCEDURE dbo.库房管理_库位_查询;
|
||
GO
|
||
|
||
CREATE PROCEDURE dbo.库房管理_库位_查询
|
||
@库房流水号 INT = NULL,
|
||
@库区流水号 INT = NULL,
|
||
@库位名称_check BIT = 0,
|
||
@库位名称 NVARCHAR(50) = NULL,
|
||
@库位状态_check BIT = 0,
|
||
@库位状态 NVARCHAR(20) = NULL,
|
||
@PageCurrent INT = NULL,
|
||
@PageSize INT = NULL,
|
||
@PageCount INT = NULL OUTPUT,
|
||
@ItemCount INT = NULL OUTPUT
|
||
AS
|
||
BEGIN
|
||
SET NOCOUNT ON;
|
||
|
||
DECLARE @tbname NVARCHAR(MAX);
|
||
DECLARE @fieldkey NVARCHAR(1000);
|
||
DECLARE @where NVARCHAR(1000);
|
||
DECLARE @fieldshow NVARCHAR(MAX);
|
||
DECLARE @fieldOrder NVARCHAR(1000);
|
||
DECLARE @locationName NVARCHAR(100);
|
||
DECLARE @locationStatus NVARCHAR(100);
|
||
|
||
SET @库房流水号 = NULLIF(@库房流水号, 0);
|
||
SET @库区流水号 = NULLIF(@库区流水号, 0);
|
||
SET @locationName = REPLACE(ISNULL(@库位名称, N''), N'''', N'''''');
|
||
SET @locationStatus = REPLACE(ISNULL(@库位状态, N''), N'''', N'''''');
|
||
SET @PageCurrent = ISNULL(NULLIF(@PageCurrent, 0), 1);
|
||
SET @PageSize = ISNULL(NULLIF(@PageSize, 0), 20);
|
||
|
||
SET @tbname = N'(
|
||
SELECT
|
||
h.货位流水号 AS 库位流水号,
|
||
h.货位名称 AS 库位名称,
|
||
h.库房流水号,
|
||
k.库房名称,
|
||
h.库区流水号,
|
||
q.库区名称,
|
||
h.排号,
|
||
h.列号,
|
||
h.层号,
|
||
CASE
|
||
WHEN ISNULL(h.是否启用, 1) = 0 THEN N''停用''
|
||
WHEN ISNULL(s.库存数量, 0) > 0 AND ISNULL(h.库位容量, 0) > 0 AND ISNULL(s.库存数量, 0) >= ISNULL(h.库位容量, 0) THEN N''满库位''
|
||
WHEN ISNULL(s.库存数量, 0) > 0 THEN N''有库存''
|
||
ELSE ISNULL(h.库位状态, N''空库位'')
|
||
END AS 库位状态,
|
||
ISNULL(h.库位容量, 0) AS 库位容量,
|
||
ISNULL(s.物料种类, 0) AS 物料种类,
|
||
ISNULL(s.库存数量, 0) AS 库存数量,
|
||
h.操作日期,
|
||
h.修改时间
|
||
FROM dbo.库存管理_货位主文件 h
|
||
LEFT JOIN dbo.库房管理_库房主文件 k ON h.库房流水号 = k.库房流水号
|
||
LEFT JOIN dbo.库房管理_库区主文件 q ON h.库区流水号 = q.库区流水号
|
||
LEFT JOIN
|
||
(
|
||
SELECT 货位流水号, COUNT(DISTINCT 物料流水号) AS 物料种类, SUM(ISNULL(货位存量, 0)) AS 库存数量
|
||
FROM dbo.库存管理_物料与货位对照表
|
||
WHERE ISNULL(货位存量, 0) > 0
|
||
GROUP BY 货位流水号
|
||
) s ON h.货位流水号 = s.货位流水号
|
||
) AS T';
|
||
SET @fieldkey = N'库位流水号';
|
||
SET @fieldshow = N'库位流水号,库位名称,库房流水号,库房名称,库区流水号,库区名称,排号,列号,层号,库位状态,库位容量,物料种类,库存数量,操作日期,修改时间';
|
||
SET @fieldOrder = N'排号 ASC, 列号 ASC, 层号 ASC, 库位名称 ASC, 库位流水号 ASC';
|
||
SET @where = N'1 = 1';
|
||
|
||
IF @库房流水号 IS NOT NULL
|
||
SET @where = @where + N' AND 库房流水号 = ' + CAST(@库房流水号 AS NVARCHAR(20));
|
||
|
||
IF @库区流水号 IS NOT NULL
|
||
SET @where = @where + N' AND 库区流水号 = ' + CAST(@库区流水号 AS NVARCHAR(20));
|
||
|
||
IF @库位名称_check = 1 AND @locationName <> N''
|
||
SET @where = @where + N' AND 库位名称 LIKE N''%' + @locationName + N'%''';
|
||
|
||
IF @库位状态_check = 1 AND @locationStatus <> N''
|
||
SET @where = @where + N' AND 库位状态 = N''' + @locationStatus + N'''';
|
||
|
||
EXEC dbo.sp_xt_pagesplit2
|
||
@tbname,
|
||
@fieldkey,
|
||
@where,
|
||
@fieldshow,
|
||
@fieldOrder,
|
||
@PageCurrent,
|
||
@PageSize,
|
||
@PageCount OUTPUT,
|
||
@ItemCount OUTPUT;
|
||
END
|
||
GO
|