|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[CM_HANGHOA_Search]
|
|
3
|
@p_HH_CODE varchar(50) = NULL,
|
|
4
|
@p_HH_NAME nvarchar(200) = NULL,
|
|
5
|
@p_HH_GROUP_ID VARCHAR(20) = NULL,
|
|
6
|
@p_HH_TYPE_ID varchar(15) = NULL,
|
|
7
|
@p_DESCRIPTION nvarchar(2000) = NULL,
|
|
8
|
@p_SUP_ID varchar(15) = NULL,
|
|
9
|
@p_PRICE decimal(18) = NULL,
|
|
10
|
@p_UNIT_ID varchar(15) = NULL,
|
|
11
|
@p_NOTES nvarchar(1000) = NULL,
|
|
12
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
13
|
@p_MAKER_ID varchar(12) = NULL,
|
|
14
|
@p_CREATE_DT VARCHAR(20) = NULL,
|
|
15
|
@p_AUTH_STATUS varchar(50) = NULL,
|
|
16
|
@p_CHECKER_ID varchar(12) = NULL,
|
|
17
|
@p_APPROVE_DT VARCHAR = NULL,
|
|
18
|
@p_GD_ID varchar(15) = NULL,
|
|
19
|
--@p_HHGROUP_HH_ID varchar(20) = NULL,
|
|
20
|
--@p_HHGROUP_GROUP_CDT_ID varchar(20) = NULL,
|
|
21
|
--@p_HHGROUP_GROUP_TTCT_ID varchar(20) = NULL,
|
|
22
|
--@p_HHGROUP_TYPE_LIMIT varchar(20) = NULL,
|
|
23
|
@P_TOP INT = 1000
|
|
24
|
|
|
25
|
AS
|
|
26
|
BEGIN -- PAGING
|
|
27
|
IF(@P_TOP is null OR @P_TOP = '' OR @P_TOP = 0)
|
|
28
|
-- PAGING BEGIN
|
|
29
|
SELECT A.*, B.UNIT_CODE, B.UNIT_NAME, C.HH_TYPE_NAME, C.HH_TYPE_CODE,
|
|
30
|
D.HH_GROUP_CODE, D.HH_GROUP_NAME, D.HH_GROUP_ID, E.AUTH_STATUS_NAME,F.CONTENT AS RECORD_STATUS_NAME,G.SUP_CODE,
|
|
31
|
--G.GD_CODE, G.GD_NAME,
|
|
32
|
GRCDT.GROUP_LIMIT_ID AS HHGROUP_GROUP_ID_CDT,sgl1.GROUP_CODE AS HHGROUP_GROUP_CODE_CDT,sgl1.GROUP_DES AS HHGROUP_GROUP_NAME_CDT, GRTTCT.GROUP_LIMIT_ID AS HHGROUP_GROUP_ID_TTCT,sgl.GROUP_CODE AS HHGROUP_GROUP_CODE_TTCT,sgl.GROUP_DES AS HHGROUP_GROUP_NAME_TTCT, DVDM.DVCM_ID,cd.DVDM_CODE,cd.DVDM_NAME --Column all NULL
|
|
33
|
-- SELECT END
|
|
34
|
FROM dbo.CM_HANGHOA A
|
|
35
|
left join CM_UNIT B on A.UNIT_ID = B.UNIT_ID
|
|
36
|
left join dbo.CM_HANGHOA_TYPE C on A.HH_TYPE_ID = C.HH_TYPE_ID
|
|
37
|
left join dbo.CM_HANGHOA_GROUP D on C.HH_GROUP_ID = D.HH_GROUP_ID
|
|
38
|
LEFT JOIN CM_AUTH_STATUS E ON A.AUTH_STATUS = E.AUTH_STATUS
|
|
39
|
LEFT JOIN CM_ALLCODE F ON F.CDNAME = 'RECORD_STATUS' AND F.CDVAL = A.RECORD_STATUS
|
|
40
|
LEFT JOIN CM_SUPPLIER G on G.SUP_ID = A.SUP_ID
|
|
41
|
--left join CM_GOODS G ON G.GD_ID = A.GD_ID
|
|
42
|
left join SYS_HH_GROUP_LIMIT GRCDT ON GRCDT.HH_ID = A.HH_ID AND GRCDT.TYPE_LIMIT = 'CDT'
|
|
43
|
left join SYS_HH_GROUP_LIMIT GRTTCT ON GRTTCT.HH_ID = A.HH_ID AND GRTTCT.TYPE_LIMIT = 'TTCT'
|
|
44
|
left join CM_HANGHOA_DVCM DVDM ON DVDM.HH_ID = A.HH_ID
|
|
45
|
LEFT JOIN CM_DVDM cd ON CD.DVDM_ID = DVDM.DVCM_ID
|
|
46
|
LEFT JOIN SYS_GROUP_LIMIT sgl ON sgl.GROUP_ID = GRTTCT.GROUP_LIMIT_ID
|
|
47
|
LEFT JOIN SYS_GROUP_LIMIT sgl1 ON sgl1.GROUP_ID = GRCDT.GROUP_LIMIT_ID
|
|
48
|
WHERE 1=1
|
|
49
|
AND (A.HH_CODE LIKE '%' + @p_HH_CODE + '%' OR @p_HH_CODE IS NULL OR @p_HH_CODE = '')
|
|
50
|
AND (A.HH_NAME LIKE '%' + @p_HH_NAME + '%' OR @p_HH_NAME IS NULL OR @p_HH_NAME = '')
|
|
51
|
AND (A.HH_TYPE_ID LIKE '%' + @p_HH_TYPE_ID + '%' OR @p_HH_TYPE_ID IS NULL OR @p_HH_TYPE_ID = '')
|
|
52
|
AND (D.HH_GROUP_ID LIKE '%' + @p_HH_GROUP_ID + '%' OR @p_HH_GROUP_ID IS NULL OR @p_HH_GROUP_ID = '')
|
|
53
|
AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
54
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
55
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
56
|
AND (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) =0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
57
|
AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
58
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
59
|
AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) =0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
60
|
--AND (A.GD_ID LIKE '%' + @p_GD_ID + '%' OR @p_GD_ID IS NULL OR @p_GD_ID = '')
|
|
61
|
AND A.RECORD_STATUS = '1'
|
|
62
|
ORDER BY A.CREATE_DT DESC
|
|
63
|
-- PAGING END
|
|
64
|
ELSE
|
|
65
|
-- PAGING BEGIN
|
|
66
|
SELECT TOP(@P_TOP) A.*, B.UNIT_CODE, B.UNIT_NAME, C.HH_TYPE_NAME, C.HH_TYPE_CODE,
|
|
67
|
D.HH_GROUP_CODE, D.HH_GROUP_NAME ,D.HH_GROUP_ID, E.AUTH_STATUS_NAME,F.CONTENT AS RECORD_STATUS_NAME,G.SUP_CODE,
|
|
68
|
--G.GD_CODE, G.GD_NAME,
|
|
69
|
GRCDT.GROUP_LIMIT_ID AS HHGROUP_GROUP_ID_CDT,sgl1.GROUP_CODE AS HHGROUP_GROUP_CODE_CDT,sgl1.GROUP_DES AS HHGROUP_GROUP_NAME_CDT, GRTTCT.GROUP_LIMIT_ID AS HHGROUP_GROUP_ID_TTCT,sgl.GROUP_CODE AS HHGROUP_GROUP_CODE_TTCT,sgl.GROUP_DES AS HHGROUP_GROUP_NAME_TTCT, DVDM.DVCM_ID,cd.DVDM_CODE,cd.DVDM_NAME --Column all NULL
|
|
70
|
-- SELECT END
|
|
71
|
FROM dbo.CM_HANGHOA A
|
|
72
|
LEFT JOIN CM_UNIT B on A.UNIT_ID = B.UNIT_ID
|
|
73
|
LEFT JOIN dbo.CM_HANGHOA_TYPE C on A.HH_TYPE_ID = C.HH_TYPE_ID
|
|
74
|
LEFT JOIN dbo.CM_HANGHOA_GROUP D on C.HH_GROUP_ID = D.HH_GROUP_ID
|
|
75
|
LEFT JOIN CM_AUTH_STATUS E ON A.AUTH_STATUS = E.AUTH_STATUS
|
|
76
|
LEFT JOIN CM_ALLCODE F ON F.CDNAME = 'RECORD_STATUS' AND F.CDVAL = A.RECORD_STATUS
|
|
77
|
LEFT JOIN CM_SUPPLIER G on G.SUP_ID = A.SUP_ID
|
|
78
|
--LEFT JOIN CM_GOODS G ON G.GD_ID = A.GD_ID
|
|
79
|
LEFT JOIN SYS_HH_GROUP_LIMIT GRCDT ON GRCDT.HH_ID = A.HH_ID AND GRCDT.TYPE_LIMIT = 'CDT'
|
|
80
|
LEFT JOIN SYS_HH_GROUP_LIMIT GRTTCT ON GRTTCT.HH_ID = A.HH_ID AND GRTTCT.TYPE_LIMIT = 'TTCT'
|
|
81
|
LEFT JOIN CM_HANGHOA_DVCM DVDM ON DVDM.HH_ID = A.HH_ID
|
|
82
|
LEFT JOIN CM_DVDM cd ON CD.DVDM_ID = DVDM.DVCM_ID
|
|
83
|
LEFT JOIN SYS_GROUP_LIMIT sgl ON sgl.GROUP_ID = GRTTCT.GROUP_LIMIT_ID
|
|
84
|
LEFT JOIN SYS_GROUP_LIMIT sgl1 ON sgl1.GROUP_ID = GRCDT.GROUP_LIMIT_ID
|
|
85
|
WHERE 1=1
|
|
86
|
AND (A.HH_CODE LIKE '%' + @p_HH_CODE + '%' OR @p_HH_CODE IS NULL OR @p_HH_CODE = '')
|
|
87
|
AND (A.HH_NAME LIKE '%' + @p_HH_NAME + '%' OR @p_HH_NAME IS NULL OR @p_HH_NAME = '')
|
|
88
|
AND (A.HH_TYPE_ID LIKE '%' + @p_HH_TYPE_ID + '%' OR @p_HH_TYPE_ID IS NULL OR @p_HH_TYPE_ID = '')
|
|
89
|
--AND (C.HH_GROUP_ID LIKE '%' + @p_HH_GROUP_ID + '%' OR @p_HH_GROUP_ID IS NULL OR @p_HH_GROUP_ID = '')
|
|
90
|
AND (D.HH_GROUP_ID LIKE '%' + @p_HH_GROUP_ID + '%' OR @p_HH_GROUP_ID IS NULL OR @p_HH_GROUP_ID = '')
|
|
91
|
AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
92
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
93
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
94
|
AND (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) =0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
95
|
AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
96
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
97
|
AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) =0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
98
|
--AND (A.GD_ID LIKE '%' + @p_GD_ID + '%' OR @p_GD_ID IS NULL OR @p_GD_ID = '')
|
|
99
|
AND A.RECORD_STATUS = '1'
|
|
100
|
ORDER BY A.CREATE_DT DESC
|
|
101
|
-- PAGING END
|
|
102
|
END -- PAGING
|