· 6 years ago · Aug 01, 2019, 12:44 AM
1CREATE SCHEMA upload;
2 ----------------------------------------------------------------------------------------------------------------------------------------
3 -- Data Status Reference
4 DROP TABLE IF EXISTS upload.mdata_STATUS_UPLOADSTATUS CASCADE;
5 CREATE TABLE IF NOT EXISTS upload.mdata_STATUS_UPLOADSTATUS (
6 ID SERIAL NOT NULL PRIMARY KEY,
7 UPLOADSTATUS_DESC VARCHAR(25) NOT NULL UNIQUE,
8 ACTIVE_STATUS BOOLEAN DEFAULT 'true' NOT NULL
9 );
10 -- Data Status Reference
11 --
12 -- UPLOAD ERROR LIST MASTERDATA
13 DROP TABLE IF EXISTS upload.mdata_STATUS_ERRORCODE CASCADE;
14 CREATE TABLE IF NOT EXISTS upload.mdata_STATUS_ERRORCODE (
15 ID SERIAL NOT NULL PRIMARY KEY,
16 ERROR_CODE VARCHAR(10) NOT NULL UNIQUE,
17 ERROR_DESC TEXT NOT NULL,
18 ACTIVE_STATUS BOOLEAN DEFAULT '1' NOT NULL
19 );
20 -- UPLOAD ERROR LIST MASTERDATA
21 --
22 -- RECORD HISTORY
23 DROP TABLE IF EXISTS upload.thist_DATAUPLOAD CASCADE;
24 CREATE TABLE IF NOT EXISTS upload.thist_DATAUPLOAD (
25 ID SERIAL NOT NULL PRIMARY KEY,
26 UPLOAD_NUMBER VARCHAR(50) NOT NULL UNIQUE,
27 UPLOAD_FILENAME VARCHAR(150) NOT NULL DEFAULT 'none',
28 GROUP_OF VARCHAR(150) NOT NULL DEFAULT '000000',
29 --
30 UPLOAD_BY VARCHAR(50) NOT NULL DEFAULT 'none',
31 UPLOAD_DATE TIMESTAMP NOT NULL DEFAULT NOW(),
32 UPLOAD_NOTE TEXT,
33 UPLOAD_STATUS INTEGER NOT NULL,
34 --
35 APPROVED_BY VARCHAR(50) NOT NULL DEFAULT 'none',
36 APPROVED_DATE TIMESTAMP DEFAULT NULL,
37 APPROVER_NOTE TEXT,
38 --
39 RECORD_VALID INTEGER NOT NULL DEFAULT '0',
40 RECORD_INVALID INTEGER NOT NULL DEFAULT '0',
41 RECORD_TOTAL INTEGER NOT NULL DEFAULT '0',
42 HAS_GENERATED BOOLEAN DEFAULT '0' NOT NULL,
43 ADDITIONAL_INFO TEXT DEFAULT NULL,
44 --
45 CREATED_BY VARCHAR(50) NOT NULL DEFAULT 'none',
46 CREATED_DATE TIMESTAMP NOT NULL DEFAULT NOW(),
47 MODIFIED_BY VARCHAR(50) NOT NULL DEFAULT 'none',
48 MODIFIED_DATE TIMESTAMP DEFAULT NULL::TIMESTAMP,
49 --
50 CONSTRAINT FK_UPLOAD_STATUS FOREIGN KEY (UPLOAD_STATUS) REFERENCES upload.mdata_STATUS_UPLOADSTATUS(ID)
51 ON DELETE CASCADE
52 ON UPDATE CASCADE
53 );
54 -- END RECORD HISTORY
55 ----------------------------------------------------------------------------------------------------------------------------------------
56 -- VIEW GENERATE UPLOAD NUMBER
57 DROP VIEW IF EXISTS upload.vw_GENERATE_REQNUMBER CASCADE;
58 CREATE VIEW upload.vw_GENERATE_REQNUMBER AS
59 SELECT split_part(UPLOAD_NUMBER,'-', 1) AS PREFIX_01,
60 split_part(UPLOAD_NUMBER,'-', 2) AS PREFIX_02,
61 CASE
62 WHEN split_part(UPLOAD_NUMBER,'-', 3) ~ '^[0-9]+$' THEN NULL
63 ELSE split_part(UPLOAD_NUMBER,'-', 3)
64 END AS PREFIX_03,
65 GROUP_OF AS GROUP_OF,
66 TO_CHAR(NOW(),'YYYYMMDD') AS DATE_OF,
67 LPAD((COUNT(1)+1)::varchar, 6, '0')::varchar AS CODE_NUMBER
68 FROM upload.thist_DATAUPLOAD
69 GROUP BY
70 GROUP_OF, TO_CHAR(NOW(),'YYYYMMDD'),
71 split_part(UPLOAD_NUMBER,'-', 1),
72 split_part(UPLOAD_NUMBER,'-', 2),
73 CASE
74 WHEN split_part(UPLOAD_NUMBER,'-', 3) ~ '^[0-9]+$' THEN NULL
75 ELSE split_part(UPLOAD_NUMBER,'-', 3)
76 END;
77 -- END VIEW GENERATE UPLOAD NUMBER
78 ----------------------------------------------------------------------------------------------------------------------------------------
79 -- INSERT UPLOAD STATUS
80 INSERT INTO upload.MDATA_STATUS_UPLOADSTATUS VALUES
81 (1, 'New Uploaded', 'true'),
82 (2, 'Processed', 'true'),
83 (3, 'Checked', 'true'),
84 (4, 'Verified', 'true'),
85 (5, 'Rejected', 'true'),
86 (6, 'Revised', 'true'),
87 (7, 'Expired', 'true'),
88 (8, 'Error', 'true'),
89 (9, 'Finished', 'true');
90 -- END INSERT UPLOAD STATUS
91 --
92 -- INSERT ERROR CODE LIST
93 INSERT INTO upload.MDATA_STATUS_ERRORCODE(ERROR_CODE,ERROR_DESC) VALUES
94 ('0100', 'Mandatory Data Incomplete'),
95 ('0200', 'Reference Not Found in System'),
96 ('0300', 'Duplicate Unique data appear'),
97 ('0400', 'Value Out of Range');
98 -- END INSERT ERROR CODE LIST
99 ----------------------------------------------------------------------------------------------------------------------------------------
100 -- UPLOAD CUSTOMER
101 DROP TABLE IF EXISTS upload.temp_DATAUPLOAD_CUSTOMER CASCADE;
102 CREATE TABLE IF NOT EXISTS upload.temp_DATAUPLOAD_CUSTOMER (
103 ID SERIAL NOT NULL PRIMARY KEY,
104 UPLOAD_NO VARCHAR(50) DEFAULT NULL,
105 RECORD_NO INTEGER DEFAULT 0,
106 ERROR_CODE VARCHAR(200) DEFAULT NULL,
107 --
108 CIF VARCHAR(50) DEFAULT NULL,
109 FULLNAME VARCHAR(100) DEFAULT NULL,
110 --
111 IDNO VARCHAR(50) DEFAULT NULL,
112 NPWP VARCHAR(50) DEFAULT NULL,
113 ADDRESS VARCHAR(255) DEFAULT NULL,
114 ZIPCODE VARCHAR(50) DEFAULT NULL,
115 PHONE VARCHAR(50) DEFAULT NULL,
116 HP VARCHAR(50) DEFAULT NULL,
117 EMAIL VARCHAR(50) DEFAULT NULL,
118 --
119 END_OF_RECORD VARCHAR(6) DEFAULT NULL,
120 PROCESSED_STATUS BOOLEAN NOT NULL DEFAULT false
121 );
122 --
123 DROP TABLE IF EXISTS upload.ref_temp_DATAUPLOAD_CUSTOMER_ERROR CASCADE;
124 CREATE TABLE IF NOT EXISTS upload.ref_temp_DATAUPLOAD_CUSTOMER_ERROR (
125 ID SERIAL NOT NULL PRIMARY KEY,
126 UPLOAD_NO VARCHAR(50) DEFAULT NULL,
127 ID_UPLOADRECORD INTEGER NOT NULL,
128 ERROR_CODE VARCHAR(16) NOT NULL,
129 --
130 CREATED_BY VARCHAR(50) NOT NULL DEFAULT 'none',
131 CREATED_DATE TIMESTAMP NOT NULL DEFAULT NOW(),
132 MODIFIED_BY VARCHAR(50) NOT NULL DEFAULT 'none',
133 MODIFIED_DATE TIMESTAMP DEFAULT NULL::TIMESTAMP,
134 IS_ENABLED BOOLEAN DEFAULT '1' NOT NULL,
135 --
136 CONSTRAINT FK_ID_UPLOADRECORD FOREIGN KEY (ID_UPLOADRECORD) REFERENCES upload.temp_DATAUPLOAD_CUSTOMER(ID)
137 ON DELETE CASCADE
138 ON UPDATE CASCADE,
139 --
140 CONSTRAINT FK_ERROR_CODE FOREIGN KEY (ERROR_CODE) REFERENCES upload.mdata_STATUS_ERRORCODE(ERROR_CODE)
141 ON DELETE CASCADE
142 ON UPDATE CASCADE
143 );
144 -- END UPLOAD CUSTOMER
145 ----------------------------------------------------------------------------------------------------------------------------------------
146 -- HISTORY UPDATE RECORD STATUS
147 CREATE OR REPLACE FUNCTION upload.upload_setSTATUS(
148 var_UPLOAD_NO VARCHAR(50),
149 var_FILENAME VARCHAR(100),
150 var_STATUS INTEGER
151 ) RETURNS VARCHAR AS
152 $BODY$
153 DECLARE varTABLE_TEMP VARCHAR(70);
154 BEGIN
155 -- CHANGE RECORD STATUS
156 UPDATE upload.THIST_DATAUPLOAD
157 SET UPLOAD_STATUS = var_STATUS,
158 MODIFIED_BY = 'schtask-upload-upload',
159 MODIFIED_DATE = NOW()
160 WHERE UPLOAD_FILENAME = var_FILENAME
161 AND UPLOAD_NUMBER = var_UPLOAD_NO;
162 -- END CHANGE RECORD STATUS
163 --
164 -- IF VERIFIED, REJECTED, ERROR, EXPIRED SET TO PROCESSED
165 IF (var_STATUS IN (4,5,7,8)) THEN
166 IF (var_UPLOAD_NO SIMILAR TO 'CST-%') THEN varTABLE_TEMP := 'upload.temp_DATAUPLOAD_CUSTOMER';
167 ELSE
168 END IF;
169 PERFORM upload.upload_setToPROCESSED(var_UPLOAD_NO, varTABLE_TEMP);
170 END IF;
171 -- END IF VERIFIED, REJECTED, ERROR, EXPIRED SET TO PROCESSED
172 RETURN var_UPLOAD_NO;
173 END;
174 $BODY$
175 LANGUAGE plpgsql VOLATILE COST 100;
176 -- END HISTORY UPDATE RECORD STATUS
177 --
178 -- UPDATE UPLOAD RECORD & GENERATED RECORD TO TRUE
179 CREATE OR REPLACE FUNCTION upload.upload_setToPROCESSED(
180 var_UPLOAD_NO VARCHAR(50),
181 var_TABLE_TEMP VARCHAR(70)
182 )RETURNS VARCHAR AS
183 $BODY$
184 BEGIN
185 -- CHANGE RECORD HAS_GENERATED TO TRUE
186 UPDATE upload.THIST_DATAUPLOAD
187 SET HAS_GENERATED = true,
188 MODIFIED_BY = 'schtask-upload',
189 MODIFIED_DATE = NOW()
190 WHERE UPLOAD_NUMBER = var_UPLOAD_NO;
191 -- END RECORD HAS_GENERATED TO TRUE
192 --
193 -- CHANGE RECORD PROCESSED_STATUS TO TRUE
194 EXECUTE FORMAT('
195 UPDATE %s SET PROCESSED_STATUS = true
196 WHERE UPLOAD_NO = $1;', var_TABLE_TEMP
197 ) USING var_UPLOAD_NO;
198 -- END CHANGE RECORD PROCESSED_STATUS TO TRUE
199 RETURN var_UPLOAD_NO;
200 END;
201 $BODY$
202 LANGUAGE plpgsql VOLATILE COST 100;
203 -- END UPDATE UPLOAD RECORD & GENERATED RECORD TO TRUE
204 --
205 -- CALCULATE VALIDATED UPLOAD REQUEST
206 CREATE OR REPLACE FUNCTION upload.upload_calRECORD(
207 var_UPLOAD_NO VARCHAR(70),
208 var_TABLE_ERROR VARCHAR(70),
209 var_TABLE_TEMP VARCHAR(70)
210 ) RETURNS VARCHAR AS
211 $BODY$
212 DECLARE
213 varRECORD_VALID INTEGER;
214 varRECORD_INVALID INTEGER;
215 varRECORD_TOTAL INTEGER;
216 varFILENAME VARCHAR(200);
217 BEGIN
218 -- Count RECORD VALID/INVALID/TOTAL
219 -- END Get Valid Record
220 EXECUTE FORMAT('
221 SELECT COUNT(1) FROM %s
222 WHERE UPLOAD_NO = $1
223 AND ID NOT IN(
224 SELECT ID_UPLOADRECORD
225 FROM %s ERR JOIN %s TMP ON ERR.ID_UPLOADRECORD = TMP.ID
226 WHERE TMP.UPLOAD_NO = $1
227 );', var_TABLE_TEMP, var_TABLE_ERROR, var_TABLE_TEMP
228 ) INTO varRECORD_VALID USING var_UPLOAD_NO;
229 -- END Get Valid Record
230 --
231 -- END Get Invalid Record
232 EXECUTE FORMAT('
233 SELECT COUNT(1) FROM %s
234 WHERE UPLOAD_NO = $1
235 AND ID IN(
236 SELECT ID_UPLOADRECORD
237 FROM %s ERR JOIN %s TMP ON ERR.ID_UPLOADRECORD = TMP.ID
238 WHERE TMP.UPLOAD_NO = $1
239 );', var_TABLE_TEMP, var_TABLE_ERROR, var_TABLE_TEMP
240 ) INTO varRECORD_INVALID USING var_UPLOAD_NO;
241 -- END Get Invalid Record
242 --
243 -- Get Total Record
244 EXECUTE FORMAT('
245 SELECT COUNT(1) FROM %s
246 WHERE UPLOAD_NO = $1;', var_TABLE_TEMP
247 ) INTO varRECORD_TOTAL USING var_UPLOAD_NO;
248 -- END Get Total Record
249 --
250 -- Get Upload Filename
251 SELECT UPLOAD_FILENAME INTO varFILENAME
252 FROM upload.THIST_DATAUPLOAD
253 WHERE UPLOAD_NUMBER = var_UPLOAD_NO;
254 -- END Get Upload Filename
255 --
256 -- Auto reject if Valid 0
257 IF (varRECORD_VALID = 0) THEN PERFORM upload.upload_setSTATUS(var_UPLOAD_NO, varFILENAME, 5);
258 ELSE PERFORM upload.upload_setSTATUS(var_UPLOAD_NO, varFILENAME, 3);
259 END IF;
260 -- END Auto reject if Valid 0
261 --
262 -- Update History Record
263 UPDATE upload.THIST_DATAUPLOAD SET
264 RECORD_VALID = varRECORD_VALID,
265 RECORD_INVALID = varRECORD_INVALID,
266 RECORD_TOTAL = varRECORD_TOTAL,
267 MODIFIED_BY = 'schtask-record',
268 MODIFIED_DATE = NOW()
269 WHERE UPLOAD_NUMBER = var_UPLOAD_NO;
270 -- END Update History Record
271 -- END Count RECORD VALID/INVALID/TOTAL
272 RETURN var_UPLOAD_NO;
273 END;
274 $BODY$
275 LANGUAGE plpgsql VOLATILE COST 100;
276 -- END CALCULATE VALIDATED UPLOAD REQUEST
277 ----------------------------------------------------------------------------------------------------------------------------------------
278 -- Process verify upload data from TEMP
279 CREATE OR REPLACE FUNCTION upload.upload_PROCEED(
280 var_UPLOAD_NO VARCHAR(70)
281 ) RETURNS VARCHAR AS
282 $BODY$
283 DECLARE
284 varTABLE_TEMP VARCHAR(70);
285 BEGIN
286 IF (var_UPLOAD_NO SIMILAR TO 'CST-%') THEN
287 varTABLE_TEMP := 'upload.TEMP_DATAUPLOAD_CUSTOMER';
288 IF (var_UPLOAD_NO SIMILAR TO '%-ADD-%') THEN PERFORM upload.upload_PROCEED_CUSTOMER_ADD(var_UPLOAD_NO);
289 ELSIF (var_UPLOAD_NO SIMILAR TO '%-EDT-%') THEN PERFORM upload.upload_PROCEED_CUSTOMER_EDIT(var_UPLOAD_NO);
290 ELSE
291 END IF;
292 ELSE
293 END IF;
294 -- Set Status to Already Generated
295 PERFORM upload.upload_setToPROCESSED(var_UPLOAD_NO, varTABLE_TEMP);
296 -- END Set Status to Already Generated
297 RETURN var_UPLOAD_NO;
298 END;
299 $BODY$
300 LANGUAGE plpgsql VOLATILE COST 100;
301 -- Process verify upload data from TEMP
302 ----------------------------------------------------------------------------------------------------------------------------------------
303 ----------------------------------------------------------------------------------------------------------------------------------------