· 6 years ago · Dec 26, 2019, 08:06 AM
1CREATE OR REPLACE FUNCTION ul_upload_ctgr_product(bigint)
2 RETURNS bigint AS
3$BODY$
4DECLARE
5 pUploadHeaderId ALIAS FOR $1;
6
7 vFail character varying(4) := 'FAIL';
8 vOk character varying(2) := 'OK';
9 vYes character varying(1) := 'Y';
10 vNo character varying(1) := 'N';
11 vActionInsert character varying(1) := 'I';
12 vActionUpdate character varying(1) := 'U';
13 vActionError character varying(1) := 'E';
14 vEmpty character varying(1) := '';
15 vSpaceValue character varying(1) := ' ';
16
17 vTenantId bigint;
18 vUserId bigint;
19 vDatetime character varying(14);
20 vSessionId text;
21
22 vCountFail bigint := 0;
23
24 vTypeData character varying := 'CtgrProduct';
25 vModeLogAdd character varying := 'A';
26 vModeLogUpdate character varying := 'U';
27 vNullValueLong bigint := -99;
28
29BEGIN
30 -- siapkan parameter
31 vTenantId = CAST( f_get_upload_parameter(pUploadHeaderId, 'tenantId') AS bigint );
32 vUserId = CAST( f_get_upload_parameter(pUploadHeaderId, 'userId') AS bigint );
33 vDatetime = CAST( f_get_upload_parameter(pUploadHeaderId, 'datetime') AS character varying );
34 vSessionId = CAST( f_get_upload_parameter(pUploadHeaderId, 'sessionId') AS text );
35
36 -- Validasi
37 -- # Category product code wajib diisi
38 -- # Category product name wajib diisi
39 -- # Max character dari category product code adalah 50
40 -- # Max character dari category product name adalah 100
41 -- # Group product yang di input harus terdaftar dalam system, dan Tenant dari group product yang di input harus sesuai dengan tenant login
42 -- # List pada CSV yang diupload, tidak boleh duplikat by category product code
43 -- # Active hanya boleh diisi Y/N
44
45 -- # Category product code wajib diisi
46 UPDATE ul_import_data_ctgr_product A
47 SET status = vFail,
48 message = message || 'Category code must be filled, '
49 WHERE A.upload_header_id = pUploadHeaderId
50 AND TRIM(A.kode_kategori) = vEmpty;
51
52 -- # Max character dari category product code adalah 50
53 UPDATE ul_import_data_ctgr_product A
54 SET status = vFail,
55 message = message || 'Max category code is 50 character, '
56 WHERE A.upload_header_id = pUploadHeaderId
57 AND length(A.kode_kategori) > 100;
58
59 -- # Category product name wajib diisi
60 UPDATE ul_import_data_ctgr_product A
61 SET status = vFail,
62 message = message || 'Category name must be filled, '
63 WHERE A.upload_header_id = pUploadHeaderId
64 AND TRIM(A.nama_kategori) = vEmpty;
65
66 -- # Max character dari category product name adalah 100
67 UPDATE ul_import_data_ctgr_product A
68 SET status = vFail,
69 message = message || 'Max category name is 100 character, '
70 WHERE A.upload_header_id = pUploadHeaderId
71 AND length(A.nama_kategori) > 100;
72
73 -- # Group product code wajib diisi
74 UPDATE ul_import_data_ctgr_product A
75 SET status = vFail,
76 message = message || 'Group product code must be filled, '
77 WHERE A.upload_header_id = pUploadHeaderId
78 AND TRIM(A.group_product_code) = vEmpty;
79
80 -- # Group product yang di input harus terdaftar dalam system, dan Tenant dari group product yang di input harus sesuai dengan tenant login
81 UPDATE ul_import_data_ctgr_product A
82 SET status = vFail,
83 message = message || 'Group product is not registered on system, '
84 WHERE A.upload_header_id = pUploadHeaderId
85 AND TRIM(A.group_product_code) <> vEmpty
86 AND NOT EXISTS(
87 SELECT 1 FROM m_group_product B
88 WHERE A.group_product_code = B.group_product_code
89 AND B.tenant_id = vTenantId
90 AND B.active = vYes
91 );
92
93 -- # List pada CSV yang diupload, tidak boleh duplikat by ctgr product code
94 WITH duplicate_data_ctgr_product AS (
95 SELECT A.kode_kategori
96 FROM ul_import_data_ctgr_product A
97 WHERE A.upload_header_id = pUploadHeaderId
98 AND TRIM(A.kode_kategori) <> vEmpty
99 GROUP BY A.kode_kategori
100 HAVING COUNT(1) > 1
101 )
102 UPDATE ul_import_data_ctgr_product A
103 SET status = vFail,
104 message = message || 'Categori code is duplicate, '
105 WHERE A.upload_header_id = pUploadHeaderId
106 AND EXISTS (
107 SELECT 1 FROM duplicate_data_ctgr_product B WHERE A.kode_kategori = B.kode_kategori
108 );
109
110 -- # Active hanya boleh diisi Y/N
111 UPDATE ul_import_data_ctgr_product A
112 SET status = vFail,
113 message = message || 'Active can only be filled with Y or N, '
114 WHERE A.upload_header_id = pUploadHeaderId
115 AND A.active NOT IN (vYes, vNo);
116
117 -- Cek apakah ada item CSV yang statusnya fail
118 IF EXISTS ( SELECT 1 FROM ul_import_data_ctgr_product B
119 WHERE B.upload_header_id = pUploadHeaderId
120 AND B.status = vFail ) THEN
121
122 -- Sesuaikan message, agar message paling belakang tidak ada koma
123 UPDATE ul_import_data_ctgr_product A
124 SET message = substr(A.message, 1, length(A.message)-2)
125 WHERE A.upload_header_id = pUploadHeaderId
126 AND A.status = vFail
127 AND TRIM(A.message) != vEmpty;
128
129 -- Update semua status menjadi FAIL jika ada salah satu item yang terkena validasi
130 -- Karena jika ada salah 1 item yang fail, maka 1 csv dianggap tidak valid
131 -- ubah update_status menjadi E untuk menandakan bahwa data error / tidak digunakan untuk membuat data
132 UPDATE ul_import_data_ctgr_product A
133 SET status = vFail,
134 update_status = vActionError
135 WHERE upload_header_id = pUploadHeaderId;
136
137 -- Hitung jumlah data yang FAIL
138 SELECT COUNT(1) INTO vCountFail
139 FROM ul_import_data_ctgr_product
140 WHERE upload_header_id = pUploadHeaderId
141 AND status = vFail;
142
143 ELSE
144
145 -- Update status non FAIL to OK
146 UPDATE ul_import_data_ctgr_product
147 SET status = vOk
148 WHERE upload_header_id = pUploadHeaderId;
149
150 -- UPDATE OK data untuk brand code terdaftar
151 WITH update_data_ctgr_product AS (
152 UPDATE m_ctgr_product A SET
153 ctgr_product_name=B.nama_kategori,
154 group_product_id=C.group_product_id,
155 update_datetime=vDatetime,
156 update_user_id=vUserId,
157 version=A.version+1,
158 active=B.active,
159 active_datetime=CASE WHEN A.active = vNo AND B.active = vYes
160 THEN vDatetime
161 ELSE A.active_datetime
162 END,
163 non_active_datetime=CASE WHEN A.active = vYes AND B.active = vNo
164 THEN vDatetime
165 ELSE A.non_active_datetime
166 END
167 FROM ul_import_data_ctgr_product B
168 INNER JOIN m_group_product C ON B.group_product_code = C.group_product_code AND C.tenant_id = vTenantId
169 WHERE B.upload_header_id = pUploadHeaderId
170 AND B.status = vOk
171 AND A.ctgr_product_code = B.kode_kategori
172 AND A.tenant_id = vTenantId
173
174 RETURNING A.*
175 )
176 -- Ubah update_status menjadi U jika data digunakan untuk Update
177 UPDATE ul_import_data_ctgr_product A SET
178 update_status = vActionUpdate
179 WHERE A.upload_header_id = pUploadHeaderId
180 AND A.status = vOk
181 AND EXISTS (
182 SELECT 1 FROM update_data_ctgr_product B WHERE A.kode_kategori = B.ctgr_product_code AND B.tenant_id = vTenantId
183 );
184
185 -- INSERT OK data untuk brand code baru
186 WITH insert_data_ctgr_product AS (
187 INSERT INTO m_ctgr_product(
188 tenant_id, ctgr_product_code, ctgr_product_name,
189 group_product_id, create_datetime, create_user_id, update_datetime,
190 update_user_id, version, active, active_datetime, non_active_datetime)
191 SELECT vTenantId, A.kode_kategori, A.nama_kategori,
192 B.group_product_id, vDatetime, vUserId, vDatetime,
193 vUserId, 0, A.active,
194 CASE WHEN A.active = vYes
195 THEN vDatetime
196 ELSE vSpaceValue
197 END AS active_datetime,
198 CASE WHEN A.active = vNo
199 THEN vDatetime
200 ELSE vSpaceValue
201 END AS non_active_datetime
202 FROM ul_import_data_ctgr_product A
203 INNER JOIN m_group_product B ON A.group_product_code = B.group_product_code AND B.tenant_id = vTenantId
204 WHERE A.upload_header_id = pUploadHeaderId
205 AND A.status = vOk
206 AND NOT EXISTS (
207 SELECT 1 FROM m_ctgr_product Z
208 WHERE A.kode_kategori = Z.ctgr_product_code AND Z.tenant_id = vTenantId
209 )
210 RETURNING *
211 )
212 -- Ubah update_status menjadi I jika data digunakan untuk Insert
213 UPDATE ul_import_data_ctgr_product A SET
214 update_status = vActionInsert
215 WHERE A.upload_header_id = pUploadHeaderId
216 AND A.status = vOk
217 AND EXISTS (
218 SELECT 1 FROM insert_data_ctgr_product B WHERE A.kode_kategori = B.ctgr_product_code AND B.tenant_id = vTenantId
219 );
220
221 -- DELETE Temp Table
222 DELETE FROM tt_ctgr_product_data_log WHERE session_id = vSessionId;
223
224 -- INSERT ke Table tt_ctgr_product_data_log untuk mode log A
225 INSERT INTO tt_ctgr_product_data_log(
226 ctgr_product_id, tenant_id, session_id, mode_log, ou_id,
227 ctgr_product_code, ctgr_product_name, group_product_id,
228 active, active_datetime, non_active_datetime,
229 create_datetime, create_user_id, update_datetime, update_user_id, version
230 )
231 SELECT A.ctgr_product_id, A.tenant_id, vSessionId, vModeLogAdd, vNullValueLong,
232 vSpaceValue, A.ctgr_product_name, A.group_product_id,
233 A.active, A.active_datetime, A.non_active_datetime,
234 A.create_datetime, A.create_user_id, A.update_datetime, A.update_user_id, A.version
235 FROM m_ctgr_product A
236 WHERE EXISTS(
237 SELECT 1
238 FROM ul_import_data_ctgr_product Z
239 WHERE Z.upload_header_id = pUploadHeaderId
240 AND Z.status = vOk
241 AND Z.kode_kategori = A.ctgr_product_code
242 AND update_status = vActionInsert
243 );
244
245 -- INSERT ke Table tt_ctgr_product_data_log untuk mode log U
246 INSERT INTO tt_ctgr_product_data_log(
247 ctgr_product_id, tenant_id, session_id, mode_log, ou_id,
248 ctgr_product_code, ctgr_product_name, group_product_id,
249 active, active_datetime, non_active_datetime,
250 create_datetime, create_user_id, update_datetime, update_user_id, version
251 )
252 SELECT A.ctgr_product_id, A.tenant_id, vSessionId, vModeLogUpdate, vNullValueLong,
253 A.ctgr_product_code, A.ctgr_product_name, A.group_product_id,
254 A.active, A.active_datetime, A.non_active_datetime,
255 A.create_datetime, A.create_user_id, A.update_datetime, A.update_user_id, A.version
256 FROM m_ctgr_product A
257 WHERE EXISTS(
258 SELECT 1
259 FROM ul_import_data_ctgr_product Z
260 WHERE Z.upload_header_id = pUploadHeaderId
261 AND Z.status = vOk
262 AND Z.kode_kategori = A.ctgr_product_code
263 AND update_status IN (vActionInsert, vActionUpdate)
264 );
265
266 -- EXECUTE f_generate_data_log
267 PERFORM f_generate_data_log_all_ou(vTenantId, vSessionId, vTypeData);
268
269 -- DELETE Temp Table
270 DELETE FROM tt_ctgr_product_data_log WHERE session_id = vSessionId;
271
272 END IF;
273
274 RETURN vCountFail;
275
276END;
277$BODY$
278 LANGUAGE plpgsql VOLATILE
279 COST 100;
280 /