|
1
|
ALTER PROCEDURE dbo.TR_REQUEST_DOC_MOBILE_Search
|
|
2
|
@p_REQ_ID varchar(15) = NULL,
|
|
3
|
@p_REQ_CODE varchar(50) = NULL,
|
|
4
|
@p_PL_REQ_CODE varchar(50) = NULL,
|
|
5
|
@p_REQ_NAME nvarchar(200) = NULL,
|
|
6
|
@p_REQ_DT varchar(30) = NULL,--
|
|
7
|
@p_REQ_CONTENT NVARCHAR(1000)=NULL,
|
|
8
|
@p_TOTAL_AMT decimal = NULL,
|
|
9
|
@p_NOTES nvarchar(1000) = NULL,
|
|
10
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
11
|
@p_MAKER_ID varchar(12) = NULL,
|
|
12
|
@p_CREATE_DT varchar(30) = NULL,--
|
|
13
|
@p_AUTH_STATUS varchar(50) = NULL,
|
|
14
|
@p_CHECKER_ID varchar(12) = NULL,
|
|
15
|
@p_APPROVE_DT varchar(30) = NULL,--
|
|
16
|
@p_BRANCH_DO VARCHAR(15) = NULL,
|
|
17
|
@p_BRANCH_CREATE VARCHAR(15) = NULL,
|
|
18
|
@p_USER_REQUEST VARCHAR(15) = NULL,
|
|
19
|
@p_BRANCH_LOGIN VARCHAR(15)=NULL,
|
|
20
|
@p_TLNAME_USER VARCHAR(20)=NULL,
|
|
21
|
@p_ROLE_USER VARCHAR(20),
|
|
22
|
@p_TOP INT = 10,
|
|
23
|
@p_PROCESS_STATUS varchar(50) = NULL,
|
|
24
|
@p_FR_DATE varchar(30) = NULL,--
|
|
25
|
@p_TO_DATE varchar(30) = NULL,--
|
|
26
|
@p_TYPE VARCHAR(15),
|
|
27
|
@p_TYPE_TRANFER VARCHAR(15)=NULL,
|
|
28
|
@p_YEAR INT = NULL,
|
|
29
|
@p_IS_TRANSFER VARCHAR(10) = NULL,
|
|
30
|
@p_NGUOIXULY NVARCHAR(15) = NULL,
|
|
31
|
@p_IS_KT bit = NULL,
|
|
32
|
@p_PL_REQ_ID VARCHAR(20)=NULL,
|
|
33
|
------------------BAODNQ 21/10/2022 : TRUYỀN THÊM DEP_CREATE
|
|
34
|
@p_DEP_CREATE VARCHAR(15) = NULL
|
|
35
|
|
|
36
|
AS
|
|
37
|
BEGIN -- PAGING
|
|
38
|
IF(@p_ROLE_USER ='KTT')
|
|
39
|
SET @p_ROLE_USER ='GDDV'
|
|
40
|
DECLARE @DEP_ID VARCHAR(15),@BRANCH_TYPE VARCHAR(20)
|
|
41
|
DECLARE
|
|
42
|
@COST_ID TABLE (
|
|
43
|
COST_ID VARCHAR(15),
|
|
44
|
DVDM_ID VARCHAR(15)
|
|
45
|
)
|
|
46
|
SET @DEP_ID =(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME_USER)
|
|
47
|
INSERT INTO @COST_ID
|
|
48
|
SELECT DT.COST_ID,PC.DVDM_ID FROM dbo.PL_COSTCENTER_DT DT
|
|
49
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
50
|
WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@p_BRANCH_LOGIN
|
|
51
|
|
|
52
|
SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN)
|
|
53
|
IF(EXISTS(SELECT DEP_ID FROM dbo.CM_DEPARTMENT WHERE DEP_ID=@DEP_ID AND DEP_CODE LIKE '069%'))
|
|
54
|
SET @BRANCH_TYPE='HS'
|
|
55
|
|
|
56
|
----NGUOI XU LY 18022021
|
|
57
|
DECLARE @lstREQUEST TABLE (
|
|
58
|
REQ_ID VARCHAR(20),
|
|
59
|
PROCESS_ID VARCHAR(50),
|
|
60
|
DVDM_NAME NVARCHAR(200),
|
|
61
|
TLNAME VARCHAR(200),
|
|
62
|
TLFullName NVARCHAR(200),
|
|
63
|
NOTES NVARCHAR(200)
|
|
64
|
)
|
|
65
|
INSERT INTO @lstREQUEST
|
|
66
|
( REQ_ID,PROCESS_ID, DVDM_NAME,TLNAME,TLFullName, NOTES)
|
|
67
|
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES
|
|
68
|
FROM
|
|
69
|
(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
|
|
70
|
WHERE EXISTS(SELECT TRD.REQ_ID FROM dbo.TR_REQUEST_DOC TRD WHERE TRD.REQ_ID=PRC.REQ_ID)
|
|
71
|
AND PRC.PROCESS_ID ='DMMS' AND PRC.STATUS='C'
|
|
72
|
) PL
|
|
73
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
74
|
LEFT JOIN dbo.TR_REQUEST_DOC TR ON TR.REQ_ID=PL.REQ_ID
|
|
75
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
76
|
LEFT JOIN
|
|
77
|
(
|
|
78
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
79
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
80
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
81
|
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
|
|
82
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
83
|
LEFT JOIN
|
|
84
|
(
|
|
85
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID
|
|
86
|
FROM
|
|
87
|
(
|
|
88
|
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
89
|
dbo.TL_USER TS
|
|
90
|
UNION ALL
|
|
91
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
92
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
93
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
94
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
95
|
) TU
|
|
96
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
97
|
LEFT JOIN(
|
|
98
|
SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC
|
|
99
|
UNION ALL
|
|
100
|
SELECT DVDM.DVDM_ID AS DMMS_ID,DVDM.DEP_ID,DVDM.BRANCH_ID FROM
|
|
101
|
(
|
|
102
|
SELECT CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID FROM dbo.CM_DVDM CD
|
|
103
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.DVDM_ID=CD.DVDM_ID
|
|
104
|
LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID
|
|
105
|
WHERE CD.IS_KHOI <>1
|
|
106
|
GROUP BY CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID
|
|
107
|
) DVDM
|
|
108
|
) Temp ON (Temp.DEP_ID=TU.SECUR_CODE OR CB.BRANCH_TYPE<>'HS') AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
109
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE TM.ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.DMMS_ID=TR.DMMS_ID) AND TempC.ID IS NULL
|
|
110
|
WHERE PL.STATUS='C'
|
|
111
|
UNION ALL
|
|
112
|
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES
|
|
113
|
FROM
|
|
114
|
(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
|
|
115
|
WHERE EXISTS(SELECT TRD.REQ_ID FROM dbo.TR_REQUEST_DOC TRD WHERE TRD.REQ_ID=PRC.REQ_ID)
|
|
116
|
AND PRC.PROCESS_ID <>'DMMS' AND PRC.PROCESS_ID <> 'NEW' AND PRC.PROCESS_ID <>'APPROVE'
|
|
117
|
AND PRC.STATUS='C' AND (PRC.IS_HAS_CHILD=0 OR PRC.IS_HAS_CHILD IS NULL)
|
|
118
|
) PL
|
|
119
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
120
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
121
|
LEFT JOIN
|
|
122
|
(
|
|
123
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
|
|
124
|
FROM (
|
|
125
|
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
126
|
dbo.TL_USER TS
|
|
127
|
UNION ALL
|
|
128
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
129
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
130
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
131
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
132
|
) TU
|
|
133
|
LEFT JOIN
|
|
134
|
(
|
|
135
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
136
|
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID
|
|
137
|
) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
138
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
139
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
140
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
141
|
WHERE STATUS='C'
|
|
142
|
UNION ALL
|
|
143
|
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES
|
|
144
|
FROM
|
|
145
|
(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
|
|
146
|
WHERE EXISTS(SELECT TRD.REQ_ID FROM dbo.TR_REQUEST_DOC TRD WHERE TRD.REQ_ID=PRC.REQ_ID)
|
|
147
|
AND PRC.PROCESS_ID <>'DMMS' AND PRC.PROCESS_ID <> 'NEW' AND PRC.PROCESS_ID <>'APPROVE'
|
|
148
|
AND PRC.STATUS='C' AND PRC.IS_HAS_CHILD=1
|
|
149
|
) PL
|
|
150
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
151
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
152
|
LEFT JOIN
|
|
153
|
(
|
|
154
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
155
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
156
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
157
|
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
|
|
158
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
159
|
LEFT JOIN
|
|
160
|
(
|
|
161
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
|
|
162
|
FROM (
|
|
163
|
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
164
|
dbo.TL_USER TS
|
|
165
|
UNION ALL
|
|
166
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
167
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
168
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
169
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
170
|
) TU
|
|
171
|
LEFT JOIN(
|
|
172
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
173
|
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
174
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
175
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
176
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
177
|
WHERE PL.STATUS='C'
|
|
178
|
|
|
179
|
----
|
|
180
|
|
|
181
|
IF(@P_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0 OR @P_TOP>0)
|
|
182
|
BEGIN
|
|
183
|
-- PAGING BEGIN
|
|
184
|
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_REASON,A.TOTAL_AMT,N'Chờ duyệt' AS AUTH_STATUS_NAME, 'U' AS AUTH_STATUS
|
|
185
|
|
|
186
|
-- SELECT END
|
|
187
|
FROM TR_REQUEST_DOC A
|
|
188
|
INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS = 'C'
|
|
189
|
LEFT JOIN PL_REQUEST_DOC PLRD ON PLRD.REQ_ID = A.PL_REQ_ID
|
|
190
|
|
|
191
|
WHERE 1 = 1
|
|
192
|
AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)
|
|
193
|
AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
|
|
194
|
AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '')
|
|
195
|
AND (A.DEP_CREATE LIKE '%' + @p_DEP_CREATE + '%' OR @p_DEP_CREATE IS NULL OR @p_DEP_CREATE = '')
|
|
196
|
AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CONVERT(DATETIME, @p_FR_DATE, 103))
|
|
197
|
AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CONVERT(DATETIME, @p_TO_DATE, 103))
|
|
198
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
199
|
AND A.RECORD_STATUS = '1'
|
|
200
|
AND (PLRD.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
|
|
201
|
AND (EXISTS(SELECT * FROM @lstREQUEST LSTRQ WHERE A.REQ_ID = LSTRQ.REQ_ID AND LSTRQ.TLNAME = @p_TLNAME_USER))
|
|
202
|
AND ((PLRP.STATUS = 'C' AND PLRP.PROCESS_ID = 'SIGN' AND A.SIGN_USER = @p_TLNAME_USER) OR PLRP.STATUS = 'C' AND PLRP.PROCESS_ID <> 'SIGN')
|
|
203
|
-- AND (PLRP.IS_HAS_CHILD = '0' OR PLRP.IS_HAS_CHILD IS NULL OR (EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD PLRPC WHERE PLRPC.PROCESS_ID = PLRP.ID AND PLRPC.TLNAME = @p_TLNAME_USER AND PLRPC.LEVEL_JOB = '1' AND PLRPC.STATUS_JOB = 'C')))
|
|
204
|
AND (PLRP.IS_HAS_CHILD = '0' OR PLRP.IS_HAS_CHILD IS NULL OR (EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD PLRPC WHERE PLRPC.PROCESS_ID = PLRP.ID AND PLRPC.TLNAME = @p_TLNAME_USER AND PLRPC.STATUS_JOB = 'C' AND EXISTS(SELECT ID FROM PL_REQUEST_PROCESS_CHILD PLRPCC WHERE PLRPCC.PROCESS_ID = PLRP.ID AND PLRPCC.TYPE_JOB = 'XL' AND PLRPCC.STATUS_JOB = 'P'))))
|
|
205
|
ORDER BY A.CREATE_DT DESC
|
|
206
|
-- PAGING END
|
|
207
|
END
|
|
208
|
END -- PAGING
|