mx4Ubjk8

1var sync = {
2    query: function(dbn, q, async, sdbn, sq, fdbn, fq, ass) {
3        if (q == "") return;
4        var r = DAMP.qry(dbn, q, async, sdbn, sq, fdbn, fq, ass);
5        if (err != "null") throw new Error(err);
6        return result;
7    }
8};
9
10function isInteger(value) {
11    return (value == parseInt(value));
12}
13
14//usefull when  using curl for get data.
15function leftTrim (s){
16    return s.replace(/^\s+/,"");
17};
18
19var queryWithCurlyParse = function(qry) {
20    var regex_connection= /(.*?)[\s\S]*?{/g;
21    var regex_query = /\{([^}]+)\}/g;
22
23    var c = regex_connection.exec(qry);
24    var connection = (c) ? c[0].replace(/{/g, "") : null;
25
26    var q = regex_query.exec(qry);
27    var query = (q) ? q[1]: null;
28    return {
29        connection: connection,
30        query: query
31    }
32};
33
34var hasQueryWithCurly = function(qry) {
35    var regex_query = /\{([^}]+)\}/g;
36    var q = regex_query.exec(qry);
37    if(q) {
38        return true;
39    }
40    return false;
41};
42var ctr = 0;
43
44var synchro = {
45    query: function(s, t) {
46        var tt = t || "[TGT]";
47        var r = sync.query(tt, s);
48        return r;
49    },
50    save: function(tbn, r4, tgt) {
51        var tgts = tgt || "[TGT]";
52        if (r4.length > 500) {
53            while (r4.length > 500) {
54                var r5 = r4.splice(0, 500);
55                synchro.save(tbn, r5, tgts);
56            }
57            synchro.save(tbn, r4, tgts);
58            return;
59        }
60        ctr += r4.length;
61
62        DAMP.upsert(tgts, '0',
63            JSON.stringify(
64                [{
65                    tbl_name: tbn,
66                    rows: r4
67                }]
68            )
69        );
70        if (DAMP.submit)
71            DAMP.submit(
72                JSON.stringify({
73                    columnNames: [
74                        '__record_report',
75
76                    ],
77                    columnTypes: [
78                        'VARCHAR'
79                    ],
80                    rows: [
81                        [
82                            r4.length,
83                        ]
84                    ]
85                }));
86    }
87};
88
89var Base64 = {
90    characters: "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=" ,
91    encode: function( string ) {
92        var characters = Base64.characters;
93        var result     = '';
94
95        var i = 0;
96        do {
97            var a = string.charCodeAt(i++);
98            var b = string.charCodeAt(i++);
99            var c = string.charCodeAt(i++);
100
101            a = a ? a : 0;
102            b = b ? b : 0;
103            c = c ? c : 0;
104
105            var b1 = ( a >> 2 ) & 0x3F;
106            var b2 = ( ( a & 0x3 ) << 4 ) | ( ( b >> 4 ) & 0xF );
107            var b3 = ( ( b & 0xF ) << 2 ) | ( ( c >> 6 ) & 0x3 );
108            var b4 = c & 0x3F;
109
110            if( ! b ) {
111                b3 = b4 = 64;
112            } else if( ! c ) {
113                b4 = 64;
114            }
115
116            result += Base64.characters.charAt( b1 ) + Base64.characters.charAt( b2 ) + Base64.characters.charAt( b3 ) + Base64.characters.charAt( b4 );
117
118        } while ( i < string.length );
119
120        return result;
121    } ,
122
123    decode: function( string ){
124        var characters = Base64.characters;
125        var result     = '';
126
127        var i = 0;
128        do {
129            var b1 = Base64.characters.indexOf( string.charAt(i++) );
130            var b2 = Base64.characters.indexOf( string.charAt(i++) );
131            var b3 = Base64.characters.indexOf( string.charAt(i++) );
132            var b4 = Base64.characters.indexOf( string.charAt(i++) );
133
134            var a = ( ( b1 & 0x3F ) << 2 ) | ( ( b2 >> 4 ) & 0x3 );
135            var b = ( ( b2 & 0xF  ) << 4 ) | ( ( b3 >> 2 ) & 0xF );
136            var c = ( ( b3 & 0x3  ) << 6 ) | ( b4 & 0x3F );
137
138            result += String.fromCharCode(a) + (b?String.fromCharCode(b):'') + (c?String.fromCharCode(c):'');
139
140        } while( i < string.length );
141
142        return result;
143    }
144};
145
146function sqlv_custom(e, r) {
147    return void 0 === e ? null : "string" == typeof e ? "'" + (e = e.replace(/[']/, "''")) + "'" : e
148};
149
150var build_query = {
151    from_object: function(query, params) {
152        var n = {};
153        Object.keys(query).forEach(function(v) {
154            
155            var s = (s = query[v]).replace(/<<(.*?)>>/g, function(e, n) {
156                return sqlv_custom(params[n.toLowerCase()])
157            });
158
159            if(hasQueryWithCurly(s)) {
160                var cc = queryWithCurlyParse(s);
161                
162                try {
163                    var d = qry(cc.connection, cc.query);
164                    n[v] = d;
165                } catch(e) {
166                    if(e.toString().indexOf("Destination unreachable") == -1) {
167                        throw e;
168                    } else {
169                        n[v] = {
170                            responseCode: 503,
171                            responseMessage: e.toString()
172                        };
173                    }
174                }
175
176            } else {
177                n[v] = synchro.query(s);
178            }
179
180        });
181        n.responseCode = 200;
182        return n;
183    },
184    from_array: function(query, params) {
185        var n = [];
186        for (var o in query) {
187            var s = (s = query[o]).replace(/<<(.*?)>>/g, function(e, n) {
188                return sqlv_custom(params[n.toLowerCase()])
189            });
190            if(hasQueryWithCurly(s)) {
191                var cc = queryWithCurlyParse(s);
192
193                try {
194                    var d = qry(cc.connection, cc.query);
195                    n.push(d);
196                } catch(e) {
197                    if(e.toString().indexOf("Destination unreachable") == -1) {
198                        throw e;
199                    } else {
200                        n.push({
201                            responseCode: 503,
202                            responseMessage: e.toString()
203                        })
204                    }
205                }
206
207            } else {
208                n.push(synchro.query(s));
209            }
210            
211        }
212        return n;
213    },
214    from_string: function(query, params) {
215        var s = query.replace(/<<(.*?)>>/g, function(e, n) {
216            return sqlv_custom(params[n.toLowerCase()])
217        });
218
219        if(hasQueryWithCurly(s)) {
220            var cc = queryWithCurlyParse(s);
221
222            try {
223                var d = qry(cc.connection, cc.query)
224                return d;
225            } catch(e) {
226                if(e.toString().indexOf("Destination unreachable") == -1) {
227                    throw e;
228                } else {
229                    return {
230                        responseCode: 503,
231                        responseMessage: e.toString()
232                    }
233                }
234            }
235
236        } else {
237            return synchro.query(s);
238        }
239
240    }
241};
242
243function init_ws(e, r) {
244    if(e.inject && typeof e.inject == "function") {
245        e.inject(e, r);
246    } else if(e.use == "execute_batch") {
247        synchro.save('logs', [{ts:new Date(), msg: dump(r)}], '[TGT]');
248        if (
249            r.params["batchname"] == 'mapping_position'
250            || r.params["batchname"] == 'position'
251            || r.params["batchname"] == 'division'
252            || r.params["batchname"] == 'amount_policy')
253            {
254            if (r.params["filename"] && r.params["filename"].length > 0) {
255                var res = submitBatch(r.params["batchname"], r.params["filename"], r.params["user_company_id"]);
256                r.response = JSON.stringify(res);
257                DAMP.WSResponse(r);
258            } else {
259                r.response = JSON.stringify({
260                    responseCode: 500,
261                    responseMessage: "filename cannot be empty"
262                });
263                DAMP.WSResponse(r);
264            }
265        } else if (r.params["batchname"] == 'upsert_mapping_position'){
266            if (r.params['request_id'] && r.params['category_id'] && r.params['group_category_id'] && r.params['state']) {
267                var res = upsertMappingPos(r.params['request_id'], parseInt(r.params['category_id']), parseInt(r.params['group_category_id']), parseInt(r.params['state']), parseInt(r.params['user_company_id']));
268                r.response = JSON.stringify(res);
269                DAMP.WSResponse(r);
270            } else {
271                r.response = JSON.stringify({
272                    responseCode: 500,
273                    responseMessage: "mandatory parameters: request_id, category_id, group_category_id, state",
274                    request_id: r.params['request_id'],
275                    position_id: r.params['position_id'], 
276                    group_category_id: r.params['group_category_id'],
277                    state: r.params['state']
278                });
279                DAMP.WSResponse(r);
280            }
281        } else if (r.params["batchname"] == 'upsert_position'){
282            // validate params
283            if (r.params['request_id']) {
284                // call function processor
285                var res = upsertPos(r.params['request_id']);
286                r.response = JSON.stringify(res);
287                DAMP.WSResponse(r);
288            } else {
289                r.response = JSON.stringify({
290                    responseCode: 500,
291                    responseMessage: "mandatory parameters: request_id",
292                    request_id: r.params['request_id'],
293                    position_id: r.params['position_id'], 
294                    group_category_id: r.params['group_category_id'],
295                    state: r.params['state']
296                });
297                DAMP.WSResponse(r);
298            }
299        } else if (r.params["batchname"] == 'upsert_division'){
300            // validate params
301            if (r.params['request_id'] && r.params['division_id'] && r.params['division_code'] && r.params['division'] && r.params['state'] && r.params['user_company_id']) {
302                // call function processor
303                var res = upsertDivision(r.params['request_id'],r.params['division_id'],r.params['division_code'],r.params['division'],r.params['description'],r.params['head_of_division'],r.params['flow_member'],r.params['flowtype'],r.params['flow_minimum_action'],r.params['state'],r.params['user_company_id']);
304                r.response = JSON.stringify(res);
305                DAMP.WSResponse(r);
306            } else {
307                r.response = JSON.stringify({
308                    responseCode: 500,
309                    responseMessage: "mandatory parameters: request_id,division_id,division_code,division,state,user_company_id",
310                    request_id: r.params['request_id'],
311                    division_id: r.params['division_id'], 
312                    division_code: r.params['division_code'],
313                    division: r.params['division'],
314                    state: r.params['state'],
315                    user_company_id: r.params['user_company_id'],
316                });
317                DAMP.WSResponse(r);
318            }
319        } else if (r.params["batchname"] == 'upsert_amount_policy'){
320            // validate params
321            if (r.params['request_id'] && r.params['policy_id'] && r.params['policy_name'] && r.params['maximum_amount'] && r.params['state'] && r.params['user_company_id']) {
322                var res = upsertAmountPolicy(r.params['request_id'], r.params['policy_id'], r.params['policy_name'], r.params['maximum_amount'], r.params['expense_category_id'], r.params['position_layer_id'], r.params['state'], r.params['user_company_id']);
323                r.response = JSON.stringify(res);
324                DAMP.WSResponse(r);
325            } else {
326                r.response = JSON.stringify({
327                    responseCode: 500,
328                    responseMessage: "mandatory parameters: request_id, policy_id, policy_name, maximum_amount, state",
329                    request_id: r.params['request_id'],
330                    policy_id: r.params['policy_id'], 
331                    policy_name: r.params['policy_name'],
332                    expense_category_id: r.params['expense_category_id'],
333                    position_layer_id: r.params['position_layer_id'],
334                    state: r.params['state']
335                });
336                DAMP.WSResponse(r);
337            }
338        } else {
339            r.response = JSON.stringify({
340                responseCode: 400,
341                responseMessage: "invalid batchname"
342            });
343            DAMP.WSResponse(r);
344        }
345    } // Start By Hafid
346    else if(e.use == "cimb_api_gabung") {
347
348        try {
349            
350            if(!r.params["action"]) {
351                throw "Parameter \'action\' required";
352            }
353            
354            switch(r.params["action"]) {
355
356                case 'get_batch_list':
357                    var query = {
358                        offset: "select coalesce(<<offset>>, 0) as offset",
359                        limit: "select coalesce(<<limit>>, 10) as lim",
360                        total_data: "call get_import_batch_filter_count(<<batch_name>>, <<startdate>>, <<enddate>>, <<status>>)",
361                        data: "call get_import_batch_filter(<<batch_name>>, <<offset>>, <<limit>>, <<startdate>>, <<enddate>>, <<status>>, <<sort_field>>, <<sort_direction>>)"
362                    };
363                    r.response = JSON.stringify(build_query.from_object(query, r.params));
364                    DAMP.WSResponse(r);
365                break;
366                case 'get_batch_detail':
367                    var query = {
368                        offset: "select coalesce(<<offset>>, 0) as offset",
369                        limit: "select coalesce(<<limit>>, 10) as lim",
370                        total_data: "call get_import_batch_detail_count(<<batch_name>>, <<batch_id>>)",
371                        data: "call get_import_batch_detail(<<batch_name>>, <<batch_id>>, <<offset>>, <<limit>>)"
372                    };
373                    r.response = JSON.stringify(build_query.from_object(query, r.params));
374                    DAMP.WSResponse(r);
375                break;
376                case 'start_batch':
377                    synchro.save('logs', [{ts:new Date(), msg: dump(r)}], '[TGT]');
378                    if (
379                        r.params["batchname"] == 'mapping_position'
380                        || r.params["batchname"] == 'position'
381                        || r.params["batchname"] == 'division'
382                        || r.params["batchname"] == 'amount_policy')
383                        {
384                        if (r.params["filename"] && r.params["filename"].length > 0) {
385                            var res = submitBatch(r.params["batchname"], r.params["filename"], r.params["user_company_id"]);
386                            r.response = JSON.stringify(res);
387                            DAMP.WSResponse(r);
388                        } else {
389                            r.response = JSON.stringify({
390                                responseCode: 500,
391                                responseMessage: "filename cannot be empty"
392                            });
393                            DAMP.WSResponse(r);
394                        }
395                    } else if (r.params["batchname"] == 'upsert_mapping_position'){
396                        if (r.params['request_id'] && r.params['category_id'] && r.params['group_category_id'] && r.params['state']) {
397                            var res = upsertMappingPos(r.params['request_id'], parseInt(r.params['category_id']), parseInt(r.params['group_category_id']), parseInt(r.params['state']), parseInt(r.params['user_company_id']));
398                            r.response = JSON.stringify(res);
399                            DAMP.WSResponse(r);
400                        } else {
401                            r.response = JSON.stringify({
402                                responseCode: 500,
403                                responseMessage: "mandatory parameters: request_id, category_id, group_category_id, state",
404                                request_id: r.params['request_id'],
405                                position_id: r.params['position_id'], 
406                                group_category_id: r.params['group_category_id'],
407                                state: r.params['state']
408                            });
409                            DAMP.WSResponse(r);
410                        }
411                    } else if (r.params["batchname"] == 'upsert_position'){
412                        // validate params
413                        if (r.params['request_id']) {
414                            // call function processor
415                            var res = upsertPos(r.params['request_id']);
416                            r.response = JSON.stringify(res);
417                            DAMP.WSResponse(r);
418                        } else {
419                            r.response = JSON.stringify({
420                                responseCode: 500,
421                                responseMessage: "mandatory parameters: request_id",
422                                request_id: r.params['request_id'],
423                                position_id: r.params['position_id'], 
424                                group_category_id: r.params['group_category_id'],
425                                state: r.params['state']
426                            });
427                            DAMP.WSResponse(r);
428                        }
429                    } else if (r.params["batchname"] == 'upsert_division'){
430                        // validate params
431                        if (r.params['request_id']) {
432                            // call function processor
433                            var res = upsertDivision(r.params['request_id']);
434                            r.response = JSON.stringify(res);
435                            DAMP.WSResponse(r);
436                        } else {
437                            r.response = JSON.stringify({
438                                responseCode: 500,
439                                responseMessage: "mandatory parameters: request_id",
440                                request_id: r.params['request_id'],
441                                position_id: r.params['position_id'], 
442                                group_category_id: r.params['group_category_id'],
443                                state: r.params['state']
444                            });
445                            DAMP.WSResponse(r);
446                        }
447                    } else if (r.params["batchname"] == 'upsert_'){
448                        // validate params
449                        if (r.params['request_id']) {
450                            // call function processor
451                            var res = upsertAmount(r.params['request_id']);
452                            r.response = JSON.stringify(res);
453                            DAMP.WSResponse(r);
454                        } else {
455                            r.response = JSON.stringify({
456                                responseCode: 500,
457                                responseMessage: "mandatory parameters: request_id",
458                                request_id: r.params['request_id'],
459                                position_id: r.params['position_id'], 
460                                group_category_id: r.params['group_category_id'],
461                                state: r.params['state']
462                            });
463                            DAMP.WSResponse(r);
464                        }
465                    } else {
466                        r.response = JSON.stringify({
467                            responseCode: 400,
468                            responseMessage: "invalid batchname"
469                        });
470                        DAMP.WSResponse(r);
471                    }
472                break;
473                default:
474                    throw 'Action not registered'
475                break;
476            }
477
478        } catch(e) {
479            r.response = JSON.stringify({
480                responseCode: 400,
481                responseMessage: e.toString()
482            });
483            DAMP.WSResponse(r);
484        }
485    }
486    // End By Hafid
487    else {
488        if(typeof(e.query) == "string") {
489            try {
490                r.response = JSON.stringify(
491                    build_query.from_string(e.query, r.params)
492                );
493                DAMP.WSResponse(r);
494            } catch(e) {
495                DAMP.log(e), r.responseCode = "520", DAMP.WSResponse(r)
496            }
497        } else if(e.query instanceof Object) {
498            try {
499                r.response = JSON.stringify(
500                    build_query.from_object(e.query, r.params)
501                );
502                DAMP.WSResponse(r);
503            } catch(e) {
504                // throw JSON.stringify(e);
505                DAMP.log(e), r.responseCode = "520", DAMP.WSResponse(r)
506            }
507        } else if(e.query instanceof Array) {
508            try {
509                r.response = JSON.stringify(
510                    build_query.from_object(e.query, r.params)
511                );
512                DAMP.WSResponse(r);
513            } catch (e) {
514                DAMP.log(e), r.responseCode = "520", DAMP.WSResponse(r)
515            }
516        }
517    }
518};
519
520// JOBS CIMB
521var MD5 = function(d){var r = M(V(Y(X(d),8*d.length)));return r.toLowerCase()};function M(d){for(var _,m="0123456789ABCDEF",f="",r=0;r<d.length;r++)_=d.charCodeAt(r),f+=m.charAt(_>>>4&15)+m.charAt(15&_);return f}function X(d){for(var _=Array(d.length>>2),m=0;m<_.length;m++)_[m]=0;for(m=0;m<8*d.length;m+=8)_[m>>5]|=(255&d.charCodeAt(m/8))<<m%32;return _}function V(d){for(var _="",m=0;m<32*d.length;m+=8)_+=String.fromCharCode(d[m>>5]>>>m%32&255);return _}function Y(d,_){d[_>>5]|=128<<_%32,d[14+(_+64>>>9<<4)]=_;for(var m=1732584193,f=-271733879,r=-1732584194,i=271733878,n=0;n<d.length;n+=16){var h=m,t=f,g=r,e=i;f=md5_ii(f=md5_ii(f=md5_ii(f=md5_ii(f=md5_hh(f=md5_hh(f=md5_hh(f=md5_hh(f=md5_gg(f=md5_gg(f=md5_gg(f=md5_gg(f=md5_ff(f=md5_ff(f=md5_ff(f=md5_ff(f,r=md5_ff(r,i=md5_ff(i,m=md5_ff(m,f,r,i,d[n+0],7,-680876936),f,r,d[n+1],12,-389564586),m,f,d[n+2],17,606105819),i,m,d[n+3],22,-1044525330),r=md5_ff(r,i=md5_ff(i,m=md5_ff(m,f,r,i,d[n+4],7,-176418897),f,r,d[n+5],12,1200080426),m,f,d[n+6],17,-1473231341),i,m,d[n+7],22,-45705983),r=md5_ff(r,i=md5_ff(i,m=md5_ff(m,f,r,i,d[n+8],7,1770035416),f,r,d[n+9],12,-1958414417),m,f,d[n+10],17,-42063),i,m,d[n+11],22,-1990404162),r=md5_ff(r,i=md5_ff(i,m=md5_ff(m,f,r,i,d[n+12],7,1804603682),f,r,d[n+13],12,-40341101),m,f,d[n+14],17,-1502002290),i,m,d[n+15],22,1236535329),r=md5_gg(r,i=md5_gg(i,m=md5_gg(m,f,r,i,d[n+1],5,-165796510),f,r,d[n+6],9,-1069501632),m,f,d[n+11],14,643717713),i,m,d[n+0],20,-373897302),r=md5_gg(r,i=md5_gg(i,m=md5_gg(m,f,r,i,d[n+5],5,-701558691),f,r,d[n+10],9,38016083),m,f,d[n+15],14,-660478335),i,m,d[n+4],20,-405537848),r=md5_gg(r,i=md5_gg(i,m=md5_gg(m,f,r,i,d[n+9],5,568446438),f,r,d[n+14],9,-1019803690),m,f,d[n+3],14,-187363961),i,m,d[n+8],20,1163531501),r=md5_gg(r,i=md5_gg(i,m=md5_gg(m,f,r,i,d[n+13],5,-1444681467),f,r,d[n+2],9,-51403784),m,f,d[n+7],14,1735328473),i,m,d[n+12],20,-1926607734),r=md5_hh(r,i=md5_hh(i,m=md5_hh(m,f,r,i,d[n+5],4,-378558),f,r,d[n+8],11,-2022574463),m,f,d[n+11],16,1839030562),i,m,d[n+14],23,-35309556),r=md5_hh(r,i=md5_hh(i,m=md5_hh(m,f,r,i,d[n+1],4,-1530992060),f,r,d[n+4],11,1272893353),m,f,d[n+7],16,-155497632),i,m,d[n+10],23,-1094730640),r=md5_hh(r,i=md5_hh(i,m=md5_hh(m,f,r,i,d[n+13],4,681279174),f,r,d[n+0],11,-358537222),m,f,d[n+3],16,-722521979),i,m,d[n+6],23,76029189),r=md5_hh(r,i=md5_hh(i,m=md5_hh(m,f,r,i,d[n+9],4,-640364487),f,r,d[n+12],11,-421815835),m,f,d[n+15],16,530742520),i,m,d[n+2],23,-995338651),r=md5_ii(r,i=md5_ii(i,m=md5_ii(m,f,r,i,d[n+0],6,-198630844),f,r,d[n+7],10,1126891415),m,f,d[n+14],15,-1416354905),i,m,d[n+5],21,-57434055),r=md5_ii(r,i=md5_ii(i,m=md5_ii(m,f,r,i,d[n+12],6,1700485571),f,r,d[n+3],10,-1894986606),m,f,d[n+10],15,-1051523),i,m,d[n+1],21,-2054922799),r=md5_ii(r,i=md5_ii(i,m=md5_ii(m,f,r,i,d[n+8],6,1873313359),f,r,d[n+15],10,-30611744),m,f,d[n+6],15,-1560198380),i,m,d[n+13],21,1309151649),r=md5_ii(r,i=md5_ii(i,m=md5_ii(m,f,r,i,d[n+4],6,-145523070),f,r,d[n+11],10,-1120210379),m,f,d[n+2],15,718787259),i,m,d[n+9],21,-343485551),m=safe_add(m,h),f=safe_add(f,t),r=safe_add(r,g),i=safe_add(i,e)}return Array(m,f,r,i)}function md5_cmn(d,_,m,f,r,i){return safe_add(bit_rol(safe_add(safe_add(_,d),safe_add(f,i)),r),m)}function md5_ff(d,_,m,f,r,i,n){return md5_cmn(_&m|~_&f,d,_,r,i,n)}function md5_gg(d,_,m,f,r,i,n){return md5_cmn(_&f|m&~f,d,_,r,i,n)}function md5_hh(d,_,m,f,r,i,n){return md5_cmn(_^m^f,d,_,r,i,n)}function md5_ii(d,_,m,f,r,i,n){return md5_cmn(m^(_|~f),d,_,r,i,n)}function safe_add(d,_){var m=(65535&d)+(65535&_);return(d>>16)+(_>>16)+(m>>16)<<16|65535&m}function bit_rol(d,_){return d<<_|d>>>32-_};
522
523function checkJobReady(batchName){
524    var checked = synchro.query("select count(1) as c from import_batch where batch_status = 'running' and batch_name = '" + batchName + "'", "[TGT]");
525    var c = parseInt(checked[0]['c']);
526    if (c > 0) {
527        return false;
528    }
529    return true;
530};
531
532function getBatchId(batchName, fileName){
533    return MD5(batchName + fileName);
534};
535
536function isBatchIdExists(batchId){
537    var checked = synchro.query("select count(1) as c from import_batch where batch_id = '" + batchId + "'", "[TGT]");
538    var c = parseInt(checked[0]['c']);
539    if (c > 0) {
540        return true;
541    }
542    return false;
543};
544
545function addBatch(batchId, batchName, fileName, user_company_id){
546    if (isBatchIdExists(batchId) == false) {
547        var b = {}
548        b['batch_id'] = batchId;
549        b['batch_name'] = batchName;
550        b['batch_status'] = 'running';
551        b['batch_filename'] = fileName;
552        b['user_company_id'] = user_company_id;
553        synchro.save('import_batch', [b], "[TGT]");
554        return batchId;
555    }
556    return null;
557};
558
559function submitBatch(batchName, fileName, user_company_id){
560    var batchId = getBatchId(batchName, fileName);
561    var q = '';
562    if (isBatchIdExists(batchId) == false) {
563        var insertedBatchId = addBatch(batchId, batchName, fileName, user_company_id);
564        if (insertedBatchId) {
565            return {
566                responseCode: 200,
567                responseMessage: "success"
568            };
569        } else {
570            return {
571                responseCode: 500,
572                responseMessage: "error while create new batch"
573            };
574        }
575    } else {
576        return {
577            responseCode: 400,
578            responseMessage: "batch id already exists"
579        };
580    }
581};
582
583function buildCategoryRelation(t, o, currTime){
584    return {
585        company_id: o['company_id'],
586        position_category_relation_id: o['position_category_relation_id'],
587        rule_id: o['rule_id'],
588        rule_name: o['rule_name'],
589        state: o['state'],
590        type: t,
591        created_date: currTime,
592        updated_date: currTime
593    }
594};
595
596function buildInsertAmountPolicy(o){
597    return {
598        company_id: o['company_id'],
599        name: o['name'],
600        maximum_amount: o['maximum_amount'],
601        created_date: new Date(),
602        updated_date: new Date(),
603        state: o['state']
604    }
605}
606
607function buildUpdateAmountPolicy(o){
608    return {
609        id: o['id'],
610        company_id: o['company_id'],
611        name: o['name'],
612        maximum_amount: o['maximum_amount'],
613        updated_date: new Date(),
614        state: o['state']
615    }
616}
617
618function processImportMappingPos(fileName, insertedBatchId){
619    synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_MAPPING_POSITION]["+insertedBatchId+"][1]"+fileName+" start"}], '[TGT]');
620    var q = '';
621    
622    try {
623        // check file exist with select data
624        q = "select position_id, group_category_id from \"" + fileName + "\"";
625        var dataCSV = synchro.query(q, "cimbimports");
626        var rowId = 0;
627        var objCSV = [];
628        dataCSV.forEach(function(o){
629            rowId++;
630            var obj = {
631                batch_id : insertedBatchId,
632                row_id: rowId,
633                position_id: o['position_id'],
634                group_category_id: o['group_category_id'],
635                process_status: 'processing'
636            };
637            objCSV.push(obj);
638        });
639        synchro.save('import_mapping_position:batch_id,row_id', objCSV, "[TGT]");
640        
641        // update batch data_total
642        q = "update import_batch set data_total = " + dataCSV.length + " where batch_id = '" + insertedBatchId + "'";
643        synchro.query(q, "[TGT]")
644        // validate group_category_id
645        q = "update import_mapping_position set process_status = 'error', description = concat_ws('|', description, 'company_expense_group cannot be empty') where batch_id = '" + insertedBatchId + "' and group_category_id is null";
646        synchro.query(q, "[TGT]")
647        q = "update import_mapping_position set process_status = 'error', description = 'company_expense_group id not exists' where batch_id = '" + insertedBatchId + "' and group_category_id not in (select id from jojonomic_db.company_expense_group)";
648        synchro.query(q, "[TGT]")
649        // validate position_id
650        q = "update import_mapping_position set process_status = 'error', description = concat_ws('|', description, 'position_id cannot be empty') where batch_id = '" + insertedBatchId + "' and position_id is null";
651        synchro.query(q, "[TGT]");
652        q = "update import_mapping_position set process_status = 'error', description = concat_ws('|', description, 'organigram id not exists') where batch_id = '" + insertedBatchId + "' and position_id not in (select id from jojoflow_db.organigram)";
653        synchro.query(q, "[TGT]");
654        q = "update import_mapping_position set process_status = 'error', description = concat_ws('|', description, 'rule id not exists') where batch_id = '" + insertedBatchId + "' and position_id not in (select organigram_id from jojoflow_db.organigram_rule where state=1)";
655        synchro.query(q, "[TGT]");
656        // q = "update import_mapping_position set process_status = 'error', description = concat_ws('|', description, 'multiple rule id') where batch_id = '" + insertedBatchId + "' and position_id in (select organigram_id from jojoflow_db.organigram_rule where state=1 group by organigram_id having count(1) > 1)";
657        // synchro.query(q, "[TGT]")
658        // insert valid data to target table expense_db.position_category_relation
659        q = "select org.company_id, org.id as position_id, org.name as position_name, rel.company_expense_id as expense_id, rel.company_expense_group_id as expense_group_id, 1 as state, current_timestamp as created_date, current_timestamp as updated_date";
660        q += " from import_mapping_position imp";
661        q += " left join jojonomic_db.company_expense_group_relation rel on rel.company_expense_group_id = imp.group_category_id and rel.state = 1";
662        q += " left join jojoflow_db.organigram org on org.id = imp.position_id and org.state = 1";
663        q += " left join jojoflow_db.organigram_rule rul on rul.organigram_id = org.id and rul.state = 1";
664        q += " where imp.process_status = 'processing' and imp.batch_id = '" + insertedBatchId + "'";
665        var newData = synchro.query(q, "[TGT]");
666        var objRelations = [];
667        var currTime = new Date();
668        newData.forEach(function(o){
669            var obj = {
670                company_id: o['company_id'],
671                position_id: o['position_id'],
672                position_name: o['position_name'],
673                expense_id: o['expense_id'],
674                expense_group_id: o['expense_group_id'],
675                state: 1,
676                created_date: currTime,
677                updated_date: currTime
678            };
679            objRelations.push(obj);
680        });
681        synchro.save('expense_db.position_category_relation:expense_id,expense_group_id,position_id', objRelations, "[TGT]");
682        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_MAPPING_POSITION]["+insertedBatchId+"][2]upsert expense_db.position_category_relation"}], '[TGT]');
683        
684        // insert to expense_db.category_rule_relation
685        // category_type = 1 -> masukan 1 row dengan type 1
686        // category_type = 2 -> masukan 2 row dengan type 2 pada row pertama kemudian type 3 pada row kedua
687        // category_type = 4 -> masukan 2 row dengan type 14 pada row pertama kemudian type 15 pada row kedua
688        // category_type = 5 -> masukan 1 row dengan type 13
689        // category_type = 6 -> masukan 2 row dengan type 16 pada row pertama kemudian type 17 pada row kedua
690        q = "select exp.category_type, target.id as position_category_relation_id, org.company_id, rul.rule_id, rule.name as rule_name, 1 as state";
691        q += " from import_mapping_position imp";
692        q += " left join jojonomic_db.company_expense_group_relation rel on rel.company_expense_group_id = imp.group_category_id and rel.state = 1";
693        q += " left join jojonomic_db.company_expense exp on exp.id = rel.company_expense_id";
694        q += " left join jojoflow_db.organigram org on org.id = imp.position_id and org.state = 1";
695        q += " left join jojoflow_db.organigram_rule rul on rul.organigram_id = org.id and rul.state = 1";
696        q += " left join expense_db.position_category_relation target on target.expense_id = rel.company_expense_id and target.position_id = org.id";
697        q += " left join jojoflow_db.rule rule on rule.id = rul.rule_id";
698        q += " where org.id is not null and imp.process_status = 'processing' and imp.batch_id = '" + insertedBatchId + "'";
699        var targetRes = synchro.query(q, "[TGT]");
700        var objCategories = [];
701        targetRes.forEach(function(o){
702            var categoryType = parseInt(o['category_type']);
703            if (categoryType == 1) {
704                objCategories.push(buildCategoryRelation(1, o, currTime));
705            } else if (categoryType == 2) {
706                objCategories.push(buildCategoryRelation(2, o, currTime));
707                objCategories.push(buildCategoryRelation(3, o, currTime));
708            } else if (categoryType == 4) {
709                objCategories.push(buildCategoryRelation(14, o, currTime));
710                objCategories.push(buildCategoryRelation(15, o, currTime));
711            } else if (categoryType == 5) {
712                objCategories.push(buildCategoryRelation(13, o, currTime));
713            } else if (categoryType == 6) {
714                objCategories.push(buildCategoryRelation(16, o, currTime));
715                objCategories.push(buildCategoryRelation(17, o, currTime));
716            }
717        });
718        synchro.save('expense_db.category_rule_relation:company_id,position_category_relation_id,rule_id,type', objCategories, "[TGT]")
719        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_MAPPING_POSITION]["+insertedBatchId+"][3] upsert expense_db.category_rule_relation"}], '[TGT]');
720
721        // update status 'processing' to 'success'
722        q = "update import_mapping_position set process_status = 'success' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
723        synchro.query(q, "[TGT]")
724        // update summary and status done to import_batch
725        q = "update import_batch set batch_endate = current_timestamp, batch_status = 'done', data_success = (select count(1) from import_mapping_position where process_status = 'success' and batch_id = '" + insertedBatchId + "'), data_error = (select count(1) from import_mapping_position where process_status = 'error' and batch_id = '" + insertedBatchId + "') where batch_id = '" + insertedBatchId + "'";
726        synchro.query(q, "[TGT]");
727        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_MAPPING_POSITION]["+insertedBatchId+"][4] " + fileName + " finished"}], '[TGT]');
728        
729    } catch (err) {
730        // update summary and status done to import_batch
731        var errorMessage = JSON.stringify(err.message).split("'").join("")
732        errorMessage = errorMessage.split('"').join('')
733        errorMessage = errorMessage.replace("java.sql.SQLException: ", "")
734        if(errorMessage.toString().toLowerCase().indexOf("java.lang.reflect.InvocationTargetException".toLowerCase()) != -1) {
735            errorMessage = "Invalid header format";
736        }
737        q = "update import_batch set batch_endate = current_timestamp, batch_status = 'failed', description = '" + errorMessage + "', data_total = 0, data_success = (select count(1) from import_mapping_position where process_status = 'success' and batch_id = '" + insertedBatchId + "'), data_error = (select count(1) from import_mapping_position where process_status = 'error' and batch_id = '" + insertedBatchId + "') where batch_id = '" + insertedBatchId + "'";
738        synchro.query(q, "[TGT]");
739        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_MAPPING_POSITION]["+insertedBatchId+"][99] " + fileName + " error:" + JSON.stringify(err.message).replace("'", "")}], '[TGT]');
740    }
741};
742
743function upsertMappingPos(requestId, categoryId, groupCategoryId, state, user_company_id){
744    var batchName = 'upsert_mapping_position';
745    var batchId = getBatchId(batchName, requestId);
746    var q = '';
747    var allowedState = [1,4];
748    // validate request body
749    // validate group_category_id
750    q = "select count(1) c from jojonomic_db.company_expense_group where id = " + groupCategoryId;
751    var validateCat = synchro.query(q, "[TGT]");
752    validateCat = parseInt(validateCat[0]['c'])
753    // validate category_id
754    q = "select count(1) c from jojonomic_db.company_expense where id = " + categoryId;
755    var validatePos = synchro.query(q, "[TGT]");
756    validatePos = parseInt(validatePos[0]['c']);
757    var exec = true;
758    if (!requestId) {
759        exec = false;
760        return {
761            responseCode: 400,
762            responseMessage: 'invalid request id'
763        };
764    }
765    if (!(allowedState.indexOf(state) >= 0)) {
766        exec = false;
767        return {
768            responseCode: 400,
769            responseMessage: 'state is not alowed'
770        };
771    }
772    if (validateCat < 1) {
773        exec = false;
774        return {
775            responseCode: 400,
776            responseMessage: 'invalid group_category_id'
777        };
778    }
779    if (validatePos < 1) {
780        exec = false;
781        return {
782            responseCode: 400,
783            responseMessage: 'invalid category_id'
784        };
785    } 
786    if (exec){
787        if (isBatchIdExists(batchId) == false) {
788            // add batch
789            var insertedBatchId = addBatch(batchId, batchName, requestId, user_company_id);
790            var upsertData = {
791                batch_id: insertedBatchId,
792                category_id: categoryId,
793                group_category_id: groupCategoryId,
794                state: state,
795                process_status: 'processing'
796            };
797            synchro.save('import_upsert_mapping_position', [upsertData], "[TGT]");
798            // insert valid data to target table expense_db.position_category_relation
799            q = "select org.company_id, org.id as position_id, org.name as position_name, imp.category_id as expense_id, imp.group_category_id as expense_group_id, imp.state as state";
800            q += " from import_upsert_mapping_position imp";
801            q += " left join expense_db.position_category_relation posrel on posrel.expense_group_id = imp.group_category_id and posrel.expense_id = imp.category_id";
802            q += " left join jojoflow_db.organigram org on org.id = posrel.position_id  and org.company_id = posrel.company_id";
803            q += " where posrel.id is not null  and org.id is not null";
804            q += " and imp.process_status = 'processing' and imp.batch_id = '" + insertedBatchId + "'";
805            var updateData = synchro.query(q, "[TGT]");
806            var objRelations = [];
807            var currTime = new Date();
808            updateData.forEach(function(o){
809                var obj = {
810                    company_id: o['company_id'],
811                    position_id: o['position_id'],
812                    position_name: o['position_name'],
813                    expense_id: o['expense_id'],
814                    expense_group_id: o['expense_group_id'],
815                    state: o['state'],
816                    updated_date: currTime
817                };
818                objRelations.push(obj);
819            });
820            synchro.save('expense_db.position_category_relation:company_id,expense_id,expense_group_id,position_id', objRelations, "[TGT]");
821            
822            q = "select exp.category_type, target.id as position_category_relation_id, org.company_id, rul.rule_id, rule.name as rule_name, imp.state";
823            q += " from import_upsert_mapping_position imp";
824            q += " left join expense_db.position_category_relation posrel on posrel.expense_group_id = imp.group_category_id and posrel.expense_id = imp.category_id ";
825            q += " left join jojonomic_db.company_expense exp on exp.id = imp.category_id";
826            q += " left join jojoflow_db.organigram org on org.id = posrel.position_id and org.company_id = posrel.company_id";
827            q += " left join jojoflow_db.organigram_rule rul on rul.organigram_id = org.id and rul.state = 1";
828            q += " left join expense_db.position_category_relation target on target.expense_id = imp.category_id and target.position_id = org.id";
829            q += " left join jojoflow_db.rule rule on rule.id = rul.rule_id";
830            q += " where posrel.id is not null and org.id is not null";
831            q += " and imp.process_status = 'processing' and imp.batch_id = '" + insertedBatchId + "'";
832            var targetRes = synchro.query(q, "[TGT]");
833            var objCategories = [];
834            targetRes.forEach(function(o){
835                var categoryType = parseInt(o['category_type']);
836                if (categoryType == 1) {
837                    objCategories.push(buildCategoryRelation(1, o, currTime));
838                } else if (categoryType == 2) {
839                    objCategories.push(buildCategoryRelation(2, o, currTime));
840                    objCategories.push(buildCategoryRelation(3, o, currTime));
841                } else if (categoryType == 4) {
842                    objCategories.push(buildCategoryRelation(14, o, currTime));
843                    objCategories.push(buildCategoryRelation(15, o, currTime));
844                } else if (categoryType == 5) {
845                    objCategories.push(buildCategoryRelation(13, o, currTime));
846                } else if (categoryType == 6) {
847                    objCategories.push(buildCategoryRelation(16, o, currTime));
848                    objCategories.push(buildCategoryRelation(17, o, currTime));
849                }
850            });
851            DAMP.log(JSON.stringify(objCategories));
852            synchro.save('expense_db.category_rule_relation:company_id,position_category_relation_id,rule_id,type', objCategories, "[TGT]");
853            // update status 'processing' to 'success'
854            q = "update import_upsert_mapping_position set process_status = 'success' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
855            synchro.query(q, "[TGT]")
856            // update summary and status done to import_batch
857            q = "update import_batch set batch_endate = current_timestamp, batch_status = 'done', data_success = (select count(1) from import_upsert_mapping_position where process_status = 'success' and batch_id = '" + insertedBatchId + "'), data_error = (select count(1) from import_upsert_mapping_position where process_status = 'error' and batch_id = '" + insertedBatchId + "') where batch_id = '" + insertedBatchId + "'";
858            synchro.query(q, "[TGT]");
859            return {
860                responseCode: 200,
861                responseMessage: "success"
862            };
863        } else {
864            return {
865                responseCode: 400,
866                responseMessage: 'batchId already exist'
867            };
868        }
869    }
870};
871
872// import position START
873
874function processChildPosition(organigram_Id,organigram_Head,company_Id,user_Company_Id){
875    //find division_flow
876    var VariableFlow = [];
877    q = "select distinct division_id from jojoflow_db.organigram_division where organigram_id = "+organigram_Id+" and company_id = "+company_Id+" and state=1";
878    var resDivisions = synchro.query(q, "[TGT]");
879    if (resDivisions.length > 0){
880        resDivisions.forEach( function(division) {
881            //get division flow  save to variable, --but not used so i skip
882            q = "select id,division_id,flow_id from jojoflow_db.division_flow where division_id = "+division.division_id+" and state = 1 order by id desc limit 1";
883            var result = synchro.query(q, '[TGT]');
884            if (result) {
885                result.forEach( function(data) {
886                    var divisionFlow = {
887                        flow_id: data.flow_id
888                    };
889                    VariableFlow.push(divisionFlow);
890                });
891            }
892        });
893    }
894
895    //create flow 
896    var FlowName = "FL-ORG-"+organigram_Id;
897    synchro.save("jojoflow_db.flow" ,[{name: FlowName, company_id: company_Id, type: 1, minimum_action: 0, state: 1, created_date: new Date(), created_by: user_Company_Id}] ,'[TGT]');
898
899    //get inserted flow
900    q = "select max(id) id from jojoflow_db.flow where name = '"+FlowName+"' and state = 1 and company_id = "+company_Id;
901    result = synchro.query(q,'[TGT]')
902    var insertedFlow = result[0].id;
903
904    //save flow on variable
905    VariableFlow.push({flow_id:insertedFlow});
906
907    //create flow_member based on head
908    q = "call proc_get_member_by_supervisor("+insertedFlow+",'"+organigram_Head+"')";
909    synchro.query(q,'[TGT]');
910
911    //delete organigram_flow without checking
912    q = "update jojoflow_db.organigram_flow set state = 4,updated_by = "+user_Company_Id+", updated_date = NOW() where organigram_id = "+organigram_Id+" and state = 1";
913    var exec = synchro.query(q,'[TGT]');
914
915    //create organigram_flow
916    synchro.save('jojoflow_db.organigram_flow', [{organigram_id: organigram_Id, flow_id: insertedFlow, state: 1, created_by: user_Company_Id, created_date:new Date()}] ,'[TGT]');
917
918    //create rule
919    var RuleName = "RL-ORG-"+organigram_Id;
920    synchro.save('jojoflow_db.rule', [{name: RuleName, company_id: company_Id, state: 1, created_date: new Date(), created_by: user_Company_Id}] , '[TGT]');
921
922    //get InsertedRule
923    q = "select max(id) id from jojoflow_db.rule where name = '"+RuleName+"' and company_id = "+company_Id+" and state = 1";
924    result = synchro.query(q,'[TGT]');
925    var insertedRule = result[0].id;
926
927    //delete rule_flow based on rule organigram
928    q = "update jojoflow_db.rule_flow set state = 4, updated_date = NOW() where rule_id in (select distinct rule_id from jojoflow_db.organigram_rule where organigram_id = "+organigram_Id+" and state=1) and state = 1";
929    exec = synchro.query(q,'[TGT]');
930
931    //delete organigram_rule without checking
932    q = "update jojoflow_db.organigram_rule set state = 4,updated_by = "+user_Company_Id+", updated_date = NOW() where organigram_id = "+organigram_Id+" and state = 1";
933    exec = synchro.query(q,'[TGT]');
934
935
936    objInsVariableFlow = [];
937    VariableFlow.forEach( function(data, index) {
938        //add order and rule
939        objInsVariableFlow.push({rule_id:insertedRule,flow_id:data.flow_id, '`order`':index+1,state:1,created_date:new Date()});
940    });
941
942    //create rule_flow from variable
943    synchro.save('jojoflow_db.rule_flow',objInsVariableFlow,'[TGT]')
944
945    //create organigram_rule
946    synchro.save('jojoflow_db.organigram_rule', [{organigram_id: organigram_Id, rule_id: insertedRule, state: 1, created_by: user_Company_Id, created_date: new Date()}] ,'[TGT]');
947
948    //process child of supervisor
949    q = "select distinct id from jojoflow_db.organigram where organigram_head_id = "+organigram_Id+" and company_id = "+company_Id+" and state =1";
950    var childs = synchro.query(q,'[TGT]');
951
952    if (childs.length > 0) {
953        childs.forEach( function(child) {
954            processChildPosition(child.id,organigram_Id,company_Id,user_Company_Id);
955        });
956    }
957}
958
959function processImportPos(fileName, insertedBatchId){
960    synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][1]"+fileName+" Start Importing"}], '[TGT]');
961    var q = '';
962    var VariableFlow = [];
963    var objInsVariableFlow = [];
964    try {
965        // check file exist with select data
966        q = "select trim(position_id) position_id, trim(position_code) position_code, trim(position) position, trim(email) email, trim(divisions) divisions, trim(layer_code) layer_code, trim(supervisor_code) supervisor_code from \"" + fileName + "\"";
967        var dataCSV = synchro.query(q, "cimbimports");
968
969            // get uploader
970        q = "select id as user_company_id, employee_id, company_id, group_id, email from jojonomic_db.company_user where id = (select i.user_company_id from import_batch i where i.batch_id = '" + insertedBatchId + "')";
971        var uploader = synchro.query(q);
972        var u_user_company = uploader[0]['user_company_id'];
973        var u_company_id = uploader[0]['company_id'];
974
975        // insert csv data from /synchro/cimb-imports/{filename}.csv to table import_mapping_position
976        var rowId = 0;
977        var objCSV = [];
978        var objCSVdivisions = [];
979        dataCSV.forEach(function(o){
980            rowId++;
981            var obj = {
982                batch_id : insertedBatchId,
983                row_id: rowId,
984                position_id: o['position_id'],
985                position_code: o['position_code'],
986                position: o['position'],
987                email: o['email'],
988                division: o['divisions'],
989                layer_code: o['layer_code'],
990                supervisor_code: o['supervisor_code'],
991                process_status: 'processing',
992                description: '',
993                company_id: u_company_id,
994                user_company_id: u_user_company
995            };
996            objCSV.push(obj);
997        });
998        synchro.save('import_position:batch_id,row_id', objCSV, "[TGT]");
999        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][2]"+fileName+" Done Importing"}], '[TGT]');
1000
1001        // update batch data_total
1002        q = "update import_batch set data_total = " + dataCSV.length + " where batch_id = '" + insertedBatchId + "'";
1003        synchro.query(q, "[TGT]");
1004
1005        /******** START VALIDATE BATCH ********/
1006
1007        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][3]"+fileName+" Start Validate Batch"}], '[TGT]');
1008
1009        //position_id is null :all
1010        q = "update import_position set process_status = 'error', description = 'position_id cannot be empty' where batch_id = '" + insertedBatchId + "' and position_id is null";
1011        synchro.query(q, "[TGT]");
1012
1013        //validate position  :update
1014        q = "update import_position set process_status = 'error', description = concat_ws('|', description, 'Organigram not exists') where batch_id = '" + insertedBatchId + "' and position_id > 0 and position_id not in (select distinct id from jojoflow_db.organigram where company_id  = "+u_company_id+" and state = 1 )";
1015        synchro.query(q, "[TGT]");
1016
1017        //position_code is null :all
1018        q = "update import_position set process_status = 'error', description = concat_ws('|',description,'position_code is null') where batch_id = '" + insertedBatchId + "' and position_code is null";
1019        synchro.query(q, "[TGT]");
1020
1021        //position is null :all
1022        q = "update import_position set process_status = 'error', description = concat_ws('|', description, 'position is null') where batch_id = '" + insertedBatchId + "' and position is null";
1023        synchro.query(q, "[TGT]");
1024
1025        //position length <=3 :all
1026        q = "update import_position set process_status = 'error', description = concat_ws('|', description, 'position must > 3 chars') where batch_id = '" + insertedBatchId + "' and length(position) < 4";
1027        synchro.query(q, "[TGT]");
1028
1029        //position code != supervisor :all
1030        q = "update import_position set process_status = 'error', description = concat_ws('|', description, 'Parent cant be on the same position') where batch_id = '" + insertedBatchId + "' and position_code = supervisor_code";
1031        synchro.query(q, "[TGT]");
1032
1033        //position code != supervisor :all
1034        q = "update import_position set process_status = 'error', description = concat_ws('|', description, 'Parent cant be on the same position') where batch_id = '" + insertedBatchId + "' and position_id = (select max(id) from jojoflow_db.organigram o where o.code = import_position.supervisor_code and o.state = 1 and o.company_id = "+u_company_id+")";
1035        synchro.query(q, "[TGT]");
1036
1037        //supervisor_code :all
1038        q = "update import_position set process_status = 'error', description = concat_ws('|', description, 'supervisor_code not exists') where not exists (select 1 from jojoflow_db.organigram org where org.code = import_position.supervisor_code and org.company_id = import_position.company_id) and import_position.supervisor_code is not null and batch_id = '" + insertedBatchId + "'";
1039        synchro.query(q, "[TGT]");
1040
1041        /*** END VALIDATE ***/
1042
1043        // UPDATING & VERIFY NEEDED DATA  :all
1044
1045        // update organigram_head_id :all
1046        q = "update import_position set organigram_head_id = (select max(id) from jojoflow_db.organigram org where org.code = import_position.supervisor_code and org.company_id = import_position.company_id)  where import_position.supervisor_code is not null and batch_id = '" + insertedBatchId + "'";
1047        synchro.query(q, "[TGT]")
1048
1049        // update layer_id :all
1050        q = "update import_position set layer_id = (select max(id) from jojoflow_db.layer di where di.code = import_position.layer_code and di.company_id = import_position.company_id) where batch_id = '" + insertedBatchId + "' and import_position.layer_code is not null";
1051        synchro.query(q, "[TGT]")
1052
1053        //validate layer
1054        q = "update import_position set process_status = 'error', description = concat_ws('|', description, 'layer not exists') where batch_id = '" + insertedBatchId + "' and layer_code is not null and layer_id is null";
1055        synchro.query(q, "[TGT]");
1056
1057        // validate email
1058        q = "update import_position set email_valid = 'yes' where batch_id = '" + insertedBatchId + "' and email is not null and exists (select 1 from jojonomic_db.company_user u where u.email = import_position.email)";
1059        synchro.query(q, "[TGT]")
1060
1061        /** START VALIDATING DIVISION **/
1062        q = "select row_id,division from import_position where batch_id = '"+insertedBatchId+"' and division is not null";
1063        var result = synchro.query(q, '[TGT]');
1064
1065        if (result) {
1066            result.forEach(function(data) {
1067                var divisions = data.division.split(';')
1068                var i = 0;
1069                while (i < divisions.length) {
1070                    var check_Division = divisions[i].trim();
1071                    q = "select count(1) c from jojoflow_db.division where code = '"+check_Division+"' and state = 1 and company_id = "+ u_company_id;
1072                    result = synchro.query(q, "[TGT]");
1073                    if (result[0].c == 0) {
1074                        q = "   update import_position set  \
1075                                process_status = 'error',\
1076                                description = concat_ws('|',description, 'division "+check_Division+" not exists')\
1077                                where batch_id = '" + insertedBatchId + "'\
1078                                and row_id = "+ data.row_id;
1079                        result = synchro.query(q, "[TGT]");
1080                    }
1081                    i++;
1082                }
1083            });
1084        }
1085
1086        /** END VALIDATING DIVISION **/
1087
1088        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][4]"+fileName+" Done Validate"}], '[TGT]');
1089
1090        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][5]"+fileName+" Start Inserting"}], '[TGT]');
1091
1092        /** START INSERTING DATA **/
1093        q = "select\
1094                row_id,\
1095                position_id id,\
1096                position_code code,\
1097                position name,\
1098                email,\
1099                email_valid,\
1100                division,\
1101                layer_code,\
1102                layer_id,\
1103                supervisor_code,\
1104                organigram_head_id,\
1105                company_id,\
1106                user_company_id\
1107            from import_position\
1108            where batch_id = '"+insertedBatchId+"'\
1109            and position_id = 0\
1110            and process_status = 'processing'";
1111        var DataInsert = synchro.query(q,'[TGT]');
1112
1113        DataInsert.forEach(function(data) {
1114            //check position code 
1115            q = "   select count(1) c from jojoflow_db.organigram o where o.code = '" + data.code + "'\
1116                            and o.state = 1 and o.company_id = "+data.company_id;
1117            resultq = synchro.query(q, "[TGT]");
1118
1119            if(resultq[0].c > 0){
1120                q = "   update import_position set process_status = 'error', \
1121                        description = concat_ws('|',description, 'Code already used for Job Position')\
1122                        where batch_id = '" + insertedBatchId + "'\
1123                        and row_id = "+ data.row_id;
1124                resultq = synchro.query(q, "[TGT]");
1125            }else{
1126                var obj = [{
1127                        company_id : data.company_id,
1128                        name : data.name,
1129                        code : data.code,
1130                        organigram_head_id : data.organigram_head_id,
1131                        description : data.description,
1132                        state : 1,
1133                        created_date : new Date(),
1134                        updated_date : new Date(),
1135                        created_by : data.user_company_id,
1136                        updated_by : data.user_company_id
1137                }];
1138                synchro.save('jojoflow_db.organigram',obj,"[TGT]");
1139
1140                q = "select max(id) id from jojoflow_db.organigram where code = '"+data.code+"' and state = 1 and company_id = "+data.company_id;
1141                result = synchro.query(q,'[TGT]')
1142                var insertedOrganigram = result[0].id;
1143                
1144                if (data.email && data.email_valid) {
1145                    //create organigram_member
1146                    synchro.save('jojoflow_db.organigram_member', [{organigram_id: insertedOrganigram, user_company_id: data.user_company_id, created_date : new Date(), created_by: data.user_company_id}] ,'[TGT]')
1147                }
1148
1149                //division if exists
1150                if (data.division) {
1151                    divisions = data.division.split(';');
1152                    if (divisions.length > 0) {
1153                        i = 0;
1154                        while (i < divisions.length) {
1155                            var check_Division = divisions[i].trim();
1156                            q = "select id,name,code,head_organigram_id from jojoflow_db.division where id = ( select max(id) from jojoflow_db.division where code = '"+check_Division+"' and state = 1 and company_id = "+ data.company_id+")";
1157                            result = synchro.query(q,"[TGT]");
1158
1159                            //create organigram_division
1160                            synchro.save('jojoflow_db.organigram_division', [{organigram_id: insertedOrganigram, division_id: result[0].id, company_id: data.company_id, state: 1, created_date: new Date()}] ,'[TGT]')
1161
1162                            //get division flow  save to variable, --but not used so i skip
1163                            q = "select id,division_id,flow_id from jojoflow_db.division_flow where division_id = "+result[0].id+" and state = 1 order by id desc limit 1";
1164                            var result = synchro.query(q, '[TGT]');
1165                            VariableFlow = [];
1166                            if (result) {
1167                                result.forEach( function(data) {
1168                                    var divisionFlow = {
1169                                        flow_id: data.flow_id
1170                                    };
1171                                    VariableFlow.push(divisionFlow);
1172                                });
1173                            }
1174                            i++;
1175                        }
1176                    }
1177                }
1178
1179                //create layer_organigram
1180                if (data.layer_id) {
1181                    obj = [{
1182                        company_id: data.company_id,
1183                        layer_id: data.layer_id,
1184                        organigram_id: insertedOrganigram,
1185                        state: 1,
1186                        created_date: new Date(),
1187                        created_by: data.user_company_id
1188                    }];
1189                    synchro.save('jojoflow_db.layer_organigram', obj ,'[TGT]');
1190                }
1191
1192                if (data.supervisor_code && data.organigram_head_id) {
1193                    //create flow 
1194                    var FlowName = "FL-ORG-"+insertedOrganigram;
1195                    synchro.save("jojoflow_db.flow" ,[{name: FlowName, company_id: data.company_id, type: 1, minimum_action: 0, state: 1, created_date: new Date(), created_by: data.user_company_id}] ,'[TGT]');
1196
1197                    //get inserted flow
1198                    q = "select max(id) id from jojoflow_db.flow where name = '"+FlowName+"' and state = 1 and company_id = "+data.company_id;
1199                    result = synchro.query(q,'[TGT]')
1200                    var insertedFlow = result[0].id;
1201
1202                    //save to variable
1203                    VariableFlow.push({flow_id:insertedFlow});
1204
1205                    //create flow_member
1206                    q = "call proc_get_member_by_supervisor("+insertedFlow+",'"+data.organigram_head_id+"')";
1207                    synchro.query(q,'[TGT]');
1208
1209                    //create organigram_flow
1210                    synchro.save('jojoflow_db.organigram_flow', [{organigram_id: insertedOrganigram, flow_id: insertedFlow, state: 1, created_by: data.user_company_id, created_date:new Date()}] ,'[TGT]');
1211
1212                    //create rule
1213                    var RuleName = "RL-ORG-"+insertedOrganigram;
1214                    synchro.save('jojoflow_db.rule', [{name: RuleName, company_id: data.company_id, state: 1, created_date: new Date(), created_by: data.user_company_id}] , '[TGT]');
1215
1216                    //get InsertedRule
1217                    q = "select max(id) id from jojoflow_db.rule where name = '"+RuleName+"' and company_id = "+data.company_id+" and state = 1";
1218                    result = synchro.query(q,'[TGT]');
1219                    var insertedRule = result[0].id;
1220
1221                    //create rule_flow based on saved flow id,order on variable? 
1222                    objInsVariableFlow = [];
1223                    VariableFlow.forEach( function(data, index) {
1224                        objInsVariableFlow.push({rule_id:insertedRule,flow_id:data.flow_id, '`order`':index+1,state:1,created_date:new Date()});
1225                    });
1226                    synchro.save('jojoflow_db.rule_flow',objInsVariableFlow,'[TGT]');
1227
1228                    //create organigram_rule
1229                    synchro.save('jojoflow_db.organigram_rule', [{organigram_id: insertedOrganigram, rule_id: insertedRule, state: 1, created_by: data.user_company_id, created_date: new Date()}] ,'[TGT]');
1230
1231                }
1232            }
1233        });
1234
1235        /** DONE INSERTING DATA **/
1236
1237        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][6]"+fileName+" Done Inserting"}], '[TGT]');
1238        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][7]"+fileName+" Start Updating"}], '[TGT]');
1239
1240        /** START UPDATING DATA **/
1241        q = "select\
1242                row_id,\
1243                position_id id,\
1244                position_code code,\
1245                position name,\
1246                email,\
1247                email_valid,\
1248                division,\
1249                layer_code,\
1250                layer_id,\
1251                supervisor_code,\
1252                organigram_head_id,\
1253                company_id,\
1254                user_company_id\
1255            from import_position\
1256            where batch_id = '"+insertedBatchId+"'\
1257            and position_id > 0\
1258            and process_status = 'processing'";
1259        var DataUpdate = synchro.query(q,'[TGT]');
1260
1261        DataUpdate.forEach( function(data) {
1262            //check position code 
1263            q = "   select count(1) c from jojoflow_db.organigram o where o.code = '" + data.code + "'\
1264                    and id != "+data.id+" and o.state = 1 and o.company_id = "+data.company_id;
1265            result = synchro.query(q, "[TGT]");
1266
1267            if(result[0].c > 0){
1268                q = "   update import_position set process_status = 'error', \
1269                        description = concat_ws('|',description, 'Code already used for Job Position')\
1270                        where batch_id = '" + insertedBatchId + "'\
1271                        and row_id = "+ data.row_id;
1272                result = synchro.query(q, "[TGT]");
1273            }else{
1274                var obj = [{
1275                        id: data.id,
1276                        company_id : data.company_id,
1277                        name : data.name,
1278                        code : data.code,
1279                        organigram_head_id : data.organigram_head_id,
1280                        description : data.description,
1281                        state : 1,
1282                        updated_date : new Date(),
1283                        updated_by : data.user_company_id
1284                }];
1285                synchro.save('jojoflow_db.organigram:id',obj,"[TGT]");
1286
1287                if (data.email && data.email_valid) {
1288                    //check organigram_member
1289                    q = "select count(1) c from jojoflow_db.organigram_member where organigram_id = "+data.id+" and user_company_id = "+data.user_company_id;
1290                    result = synchro.query(q, "[TGT]");
1291
1292                    if(result[0].c > 0){
1293                        //update organigram member if exist
1294                        synchro.save('jojoflow_db.organigram_member:organigram_id,user_company_id',[{organigram_id: data.id, user_company_id: data.user_company_id, updated_by: data.user_company_id}],"[TGT]");
1295                    }else{
1296                        //create if not exist
1297                        synchro.save('jojoflow_db.organigram_member', [{organigram_id: data.id, user_company_id: data.user_company_id, created_date : new Date(), created_by: data.user_company_id}] ,'[TGT]')
1298                    }
1299                }
1300
1301                // //delete organigram_divison
1302                // synchro.query("update jojoflow_db.organigram_division set state = 4,updated_date = NOW() where organigram_id = "+data.id+" and company_id = "+data.company_id+" and state = 1",'[TGT]');
1303
1304                if (data.division) {
1305                    divisions = data.division.split(';');
1306                    if (divisions.length > 0) {
1307                        i = 0;
1308                        while (i < divisions.length) {
1309                            var check_Division = divisions[i].trim();
1310                            q = "select id,name,code,head_organigram_id from jojoflow_db.division where id = ( select max(id) from jojoflow_db.division where code = '"+check_Division+"' and state = 1 and company_id = "+ data.company_id+")";
1311                            result = synchro.query(q,"[TGT]");
1312
1313                            //check organigram division
1314                            q = "select max(id) id from jojoflow_db.organigram_division where organigram_id = "+data.id+" and division_id = "+result[0].id+" and state=1 and company_id="+data.company_id;
1315                            resultdiv = synchro.query(q,"[TGT]");
1316
1317                            if (resultdiv.length > 0) {
1318                                synchro.save('jojoflow_db.organigram_division:id', [{id:resultdiv[0].id,organigram_id: data.id, division_id: result[0].id, company_id: data.company_id, state: 1, updated_date: new Date()}] ,'[TGT]');
1319                            }else{
1320                                //create organigram_division
1321                                synchro.save('jojoflow_db.organigram_division', [{organigram_id: data.id, division_id: result[0].id, company_id: data.company_id, state: 1, created_date: new Date()}] ,'[TGT]')
1322                            }
1323
1324                            //get division flow  save to variable, --but not used so i skip
1325                            q = "select id,division_id,flow_id,`order` orders from jojoflow_db.division_flow where division_id = "+result[0].id+" and state = 1 order by id desc limit 1";
1326                            result = synchro.query(q, '[TGT]');
1327                            VariableFlow = [];
1328                            if (result.length > 0) {
1329                                result.forEach( function(data) {
1330                                    var divisionFlow = {
1331                                        flow_id: data.flow_id
1332                                    };
1333                                    VariableFlow.push(divisionFlow);
1334                                });
1335                            }
1336                            i++;
1337                        }
1338                    }
1339                }
1340
1341                //delete layer_organigram
1342                // synchro.query("update jojoflow_db.layer_organigram set state = 4,updated_by = "+data.user_company_id+", updated_date = NOW()  where organigram_id = "+data.id+" and company_id = "+data.company_id+" and state = 1",'[TGT]');
1343
1344                //create layer_organigram
1345                if (data.layer_id) {
1346                    q = "select max(id) id from jojoflow_db.layer_organigram where layer_id = "+data.layer_id+" and organigram_id = "+data.id+" and company_id = "+data.company_id+" and state = 1";
1347                    result = synchro.query(q, '[TGT]');
1348                    if (result.length > 0) {
1349                        obj = [{
1350                            id: result[0].id,
1351                            company_id: data.company_id,
1352                            layer_id: data.layer_id,
1353                            organigram_id: data.id,
1354                            state: 1,
1355                            created_by: data.user_company_id
1356                        }];
1357                    synchro.save('jojoflow_db.layer_organigram:id', obj ,'[TGT]');
1358                    }else{
1359                        obj = [{
1360                            company_id: data.company_id,
1361                            layer_id: data.layer_id,
1362                            organigram_id: data.id,
1363                            state: 1,
1364                            created_date: new Date(),
1365                            created_by: data.user_company_id
1366                        }];
1367                    synchro.save('jojoflow_db.layer_organigram', obj ,'[TGT]');
1368                    }
1369                }
1370
1371                //
1372                if (data.email && data.email_valid) {
1373                    //create flow 
1374                    var FlowName = "FL-ORG-"+data.id;
1375                    synchro.save("jojoflow_db.flow" ,[{name: FlowName, company_id: data.company_id, type: 1, minimum_action: 0, state: 1, created_date: new Date(), created_by: data.user_company_id}] ,'[TGT]');
1376
1377                    //get inserted flow
1378                    q = "select max(id) id from jojoflow_db.flow where name = '"+FlowName+"' and state = 1 and company_id = "+data.company_id;
1379                    result = synchro.query(q,'[TGT]')
1380                    var insertedFlow = result[0].id;
1381
1382                    //save flow on variable
1383                    VariableFlow.push({flow_id:insertedFlow});
1384
1385                    //create flow_member based on head
1386                    if (data.organigram_head_id) {
1387                        q = "call proc_get_member_by_supervisor("+insertedFlow+",'"+data.organigram_head_id+"')";
1388                        synchro.query(q,'[TGT]');
1389                    }
1390
1391                    //delete organigram_flow without checking
1392                    q = "update jojoflow_db.organigram_flow set state = 4,updated_by = "+data.user_company_id+", updated_date = NOW() where organigram_id = "+data.id+" and state = 1";
1393                    var exec = synchro.query(q,'[TGT]');
1394
1395                    //create organigram_flow
1396                    synchro.save('jojoflow_db.organigram_flow', [{organigram_id: data.id, flow_id: insertedFlow, state: 1, created_by: data.user_company_id, created_date:new Date()}] ,'[TGT]');
1397
1398                    //create rule
1399                    var RuleName = "RL-ORG-"+data.id;
1400                    synchro.save('jojoflow_db.rule', [{name: RuleName, company_id: data.company_id, state: 1, created_date: new Date(), created_by: data.user_company_id}] , '[TGT]');
1401
1402                    //get InsertedRule
1403                    q = "select max(id) id from jojoflow_db.rule where name = '"+RuleName+"' and company_id = "+data.company_id+" and state = 1";
1404                    result = synchro.query(q,'[TGT]');
1405                    var insertedRule = result[0].id;
1406
1407                    //delete rule_flow based on rule organigram
1408                    q = "update jojoflow_db.rule_flow set state = 4, updated_date = NOW() where rule_id in (select distinct rule_id from jojoflow_db.organigram_rule where organigram_id = "+data.id+" and state=1) and state = 1";
1409                    exec = synchro.query(q,'[TGT]');
1410
1411                    //delete organigram_rule without checking
1412                    q = "update jojoflow_db.organigram_rule set state = 4,updated_by = "+data.user_company_id+", updated_date = NOW() where organigram_id = "+data.id+" and state = 1";
1413                    exec = synchro.query(q,'[TGT]');
1414
1415
1416                    objInsVariableFlow = [];
1417                    VariableFlow.forEach( function(data, index) {
1418                        //add order and rule
1419                        objInsVariableFlow.push({rule_id:insertedRule,flow_id:data.flow_id, '`order`':index+1,state:1,created_date:new Date()});
1420                    });
1421
1422                    //create rule_flow from variable
1423                    synchro.save('jojoflow_db.rule_flow',objInsVariableFlow,'[TGT]')
1424
1425                    //create organigram_rule
1426                    synchro.save('jojoflow_db.organigram_rule', [{organigram_id: data.id, rule_id: insertedRule, state: 1, created_by: data.user_company_id, created_date: new Date()}] ,'[TGT]');
1427
1428
1429                    if(data.organigram_head_id){
1430                        //process child of supervisor
1431                        q = "select distinct id from jojoflow_db.organigram where organigram_head_id = "+data.organigram_head_id+" and company_id = "+data.company_id+" and state =1";
1432                        var childs = synchro.query(q,'[TGT]');
1433
1434                        if (childs.length > 0) {
1435                            childs.forEach( function(child) {
1436                                processChildPosition(child.id,data.organigram_head_id,data.company_id,data.user_company_id);
1437                            });
1438                        }
1439                    }
1440                }
1441            }
1442        });
1443        /** DONE UPDATING DATA **/
1444        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][7]"+fileName+" Done Updating"}], '[TGT]');
1445
1446        // update status 'processing' to 'success'
1447        q = "update import_position set process_status = 'success' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
1448        synchro.query(q, "[TGT]")
1449        // update summary and status done to import_batch
1450        q = "update import_batch set batch_endate = current_timestamp, batch_status = 'done', data_success = (select count(1) from import_position where process_status = 'success' and batch_id = '" + insertedBatchId + "'), data_error = (select count(1) from import_position where process_status = 'error' and batch_id = '" + insertedBatchId + "') where batch_id = '" + insertedBatchId + "'";
1451        synchro.query(q, "[TGT]");
1452        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][8] finish"}], '[TGT]');
1453
1454    }catch (err){
1455        var errorMessage = JSON.stringify(err.message).split("'").join("")
1456        errorMessage = errorMessage.split('"').join('')
1457        errorMessage = errorMessage.replace("java.sql.SQLException: ", "")
1458        errorMessage = errorMessage.replace(/[^\w\s]/gi, '')
1459        if(errorMessage.toString().toLowerCase().indexOf("java.lang.reflect.invocationtargetexception") != -1) {
1460            errorMessage = "Invalid header format";
1461        } else if (errorMessage.toString().toLowerCase().indexOf("invalid column or variable") != -1) {
1462            errorMessage = "Invalid header format";
1463        }
1464        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][99] " + fileName + " error:" + errorMessage}], '[TGT]');
1465        q = "update import_batch set batch_endate = current_timestamp, batch_status = 'failed', description = '" + errorMessage + "', data_total = (select count(1) from import_mapping_position where batch_id = '" + insertedBatchId + "'), data_success = (select count(1) from import_mapping_position where process_status = 'success' and batch_id = '" + insertedBatchId + "'), data_error = (select count(1) from import_mapping_position where process_status = 'error' and batch_id = '" + insertedBatchId + "') where batch_id = '" + insertedBatchId + "'";
1466        synchro.query(q, "[TGT]");
1467    }
1468}
1469
1470// import position END
1471
1472function upsertPos(){
1473    
1474};
1475
1476function processImportDivision(fileName, insertedBatchId){
1477    synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][1]"+fileName+" Start Importing"}], '[TGT]');
1478    var q = '';
1479    
1480    try {
1481        // insert data from CSV to import-Division
1482        q = "   select trim(division_id) division_id,trim(division_code) division_code,\
1483                trim(division) division,trim(description) description,trim(head_of_division) head_of_division,\
1484                trim(flow_member) flow_member,trim(flow_type) flow_type,trim(flow_minimum_action) flow_minimum_action \
1485                from \"" + fileName + "\"";
1486        var dataCSV = synchro.query(q, "cimbimports");
1487        var rowId = 0;
1488        var objCSV = [];
1489        dataCSV.forEach(function (o) {
1490            rowId++;
1491            var obj = {
1492                batch_id: insertedBatchId,
1493                row_id: rowId,
1494                division_id: o.division_id,
1495                division_code: o.division_code,
1496                division: o.division,
1497                description: o.description,
1498                head_of_division: o.head_of_division,
1499                flow_type: o.flow_type,
1500                flow_minimum_action: o.flow_minimum_action,
1501                flow_member: o.flow_member,
1502                process_status: 'processing'
1503            };
1504            objCSV.push(obj);
1505        });
1506        synchro.save('import_division:batch_id,row_id', objCSV, "[TGT]");
1507        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][2]"+fileName+" Done Importing"}], '[TGT]');
1508
1509
1510        // update import_divison column user_company_id
1511        q = "update import_division set user_company_id = (select x.user_company_id from import_batch x where x.batch_id = '" + insertedBatchId + "' ) where batch_id = '" + insertedBatchId + "'";
1512        synchro.query(q, "[TGT]");
1513        
1514        // update import_batch column data_total
1515        q = "update import_batch set data_total = " + dataCSV.length + " where batch_id = '" + insertedBatchId + "'";
1516        synchro.query(q, "[TGT]");
1517
1518        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][3]"+fileName+" Start Validating"}], '[TGT]');
1519        /*************** START VALIDATE  ****************/
1520
1521        //validate column user_company_id
1522        q = "   update import_division set process_status = 'error', \
1523                log_message = 'user_company_id invalid' where batch_id = '" + insertedBatchId + "' \
1524                and user_company_id not in \
1525                    (select distinct x.id from jojonomic_db.company_user x where x.id = import_division.user_company_id)";
1526        var dataPostinger = synchro.query("select x.company_id,x.id company_user_id from jojonomic_db.company_user x where x.id = (select user_company_id from import_batch where batch_id ='"+ insertedBatchId +"')", "[TGT]");
1527        
1528        //cancel if user upload is not exist :all
1529        if(dataPostinger.length == 0){
1530            q = "   update import_division set process_status = 'error',\
1531                    log_message = 'uploader not registered.' \
1532                    where batch_id = '" + insertedBatchId + "'\
1533                    and process_status = 'processing'";
1534            synchro.query(q, "[TGT]");
1535        }else{
1536            //check multiple division code setting
1537            q = "   select state \
1538                    from jojoflow_db.setting \
1539                    where company_id = "+ dataPostinger[0].company_id +" and name = 'SETTING_MULTIPLE_DIVISION_CODE'";
1540            var settingMultiple = synchro.query(q, "[TGT]");
1541
1542            //check setting exist
1543            if(settingMultiple.length == 0){
1544                q = "   update import_division set process_status = 'error',\
1545                        log_message = 'setting multiple code not found' \
1546                        where process_status = 'processing'\
1547                        and batch_id = '" + insertedBatchId + "'";
1548                synchro.query(q, "[TGT]");
1549            }else{
1550                //division id
1551                q = "   update import_division set process_status = 'error',\
1552                            log_message = concat_ws('|',log_message,'division id cannot be empty') \
1553                            where batch_id = '" + insertedBatchId + "'\
1554                            and division_id is null";
1555                synchro.query(q, "[TGT]");
1556
1557                //division code
1558                q = "   update import_division set process_status = 'error',\
1559                            log_message = concat_ws('|',log_message,'division code cannot be empty') \
1560                            where batch_id = '" + insertedBatchId + "'\
1561                            and division_code is null ";
1562                synchro.query(q, "[TGT]");
1563
1564                //division name
1565                q = "   update import_division set process_status = 'error',\
1566                            log_message = concat_ws('|',log_message,'division name cannot be empty') \
1567                            where batch_id = '" + insertedBatchId + "'\
1568                            and division is null ";
1569                synchro.query(q, "[TGT]");
1570                //
1571
1572                //Division Name > 3 :all
1573                q = "   update import_division set process_status = 'error', \
1574                            log_message = concat_ws('|',log_message, 'division name less than 3 characters')\
1575                            where batch_id = '" + insertedBatchId + "'\
1576                            and length(division) < 4";
1577                synchro.query(q, "[TGT]");
1578
1579                //check head of division :all
1580                q = "   update import_division a \
1581                        set a.process_status = 'error', \
1582                        a.log_message = concat_ws('|',log_message,'head of division not exist')\
1583                        where a.batch_id = '" + insertedBatchId + "'\
1584                        and a.head_of_division is not null\
1585                        and a.head_of_division not in (select `code` from jojoflow_db.organigram where state = 1 and company_id = " + dataPostinger[0].company_id  +")";
1586                synchro.query(q, "[TGT]");
1587
1588                //division id exist or not :update
1589                q = "   update import_division set process_status = 'error', \
1590                            log_message = concat_ws('|',log_message,'division id not exist')\
1591                            where batch_id = '" + insertedBatchId + "'\
1592                            and division_id > 0 and division_id not in \
1593                            (select id from\
1594                                jojoflow_db.division x where x.state = 1 and x.company_id =  " + dataPostinger[0].company_id + ")";
1595                synchro.query(q, "[TGT]");
1596
1597                //validate kolom flow_member,flow_type numeric and semicolon only
1598                q = " call func_validate_importdivision('"+insertedBatchId+"') ";
1599                synchro.query(q, "[TGT]");
1600
1601                //Flow type mandatory if flow member not null :update
1602                q = "   update import_division a set process_status = 'error', \
1603                            log_message = concat_ws('|',log_message, 'flow type mandatory if flow member not null')\
1604                            where batch_id = '" + insertedBatchId + "'\
1605                            and division_id > 0 and flow_member is not null \
1606                            and flow_type is null";
1607                synchro.query(q, "[TGT]");
1608
1609                //Flow type only accept 1,2,3 :update
1610                q = "   update import_division a set process_status = 'error', \
1611                            log_message = concat_ws('|',log_message, 'flow type not acceptable')\
1612                            where batch_id = '" + insertedBatchId + "'\
1613                            and division_id > 0 and flow_type is not null \
1614                            and flow_type not in ('1','2','3')";
1615                synchro.query(q, "[TGT]");
1616
1617                //Flow minimum action mandatory if flow type = 3 :update
1618                q = "   update import_division a set process_status = 'error', \
1619                            log_message = concat_ws('|',log_message, 'flow minimum action mandatory if flow type = 3')\
1620                            where batch_id = '" + insertedBatchId + "'\
1621                            and division_id > 0 and flow_minimum_action is null \
1622                            and flow_type = 3";
1623                synchro.query(q, "[TGT]");
1624
1625                /*************** DONE VALIDATE ****************/
1626                synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][4] " + fileName + " Done Validating"}], '[TGT]');
1627                
1628
1629                /*************** START INSERTING ****************/
1630                synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][5] " + fileName + " Start Inserting"}], '[TGT]');
1631                //multiple division code not enabled
1632                if (settingMultiple[0].state == 4) {
1633                    q = "   select a.row_id, \
1634                            a.division_code code,\
1635                            a.division name,\
1636                            a.description,\
1637                            1 as state,\
1638                            case when a.head_of_division is null then 0\
1639                            else\
1640                            (select x.id from jojoflow_db.organigram x where a.head_of_division = x.code and x.state = 1 and x.company_id ="+dataPostinger[0].company_id+" limit 1) end head_organigram_id,\
1641                            "+ dataPostinger[0].company_id +" as company_id\
1642                            from import_division a\
1643                            where a.process_status = 'processing'\
1644                            and a.division_id = 0 \
1645                            and a.batch_id = '" + insertedBatchId + "'";
1646
1647                    var dataInsert = synchro.query(q, "[TGT]");
1648                    dataInsert.forEach(function(o){
1649                        //check division code if exist on another division
1650                        q = "   select count(1) c from jojoflow_db.division d where d.code = '" + o.code + "'\
1651                                and d.state = 1 and d.company_id = "+dataPostinger[0].company_id;
1652                        resultq = synchro.query(q, "[TGT]");
1653
1654                        if(resultq[0].c > 0){
1655                            q = "   update import_division set process_status = 'error', \
1656                                    log_message = concat_ws('|',log_message, 'division_code exist')\
1657                                    where batch_id = '" + insertedBatchId + "'\
1658                                    and row_id = "+ o.row_id;
1659                            resultq = synchro.query(q, "[TGT]");
1660                        }else{
1661                            var obj = [{
1662                                    company_id : o.company_id,
1663                                    name : o.name,
1664                                    code : o.code,
1665                                    head_organigram_id : o.head_organigram_id,
1666                                    description : o.description,
1667                                    state : o.state,
1668                                    created_date : new Date(),
1669                                    updated_date : new Date(),
1670                                    created_by : dataPostinger[0].company_user_id,
1671                                    updated_by : dataPostinger[0].company_user_id
1672                            }];
1673                            synchro.save('jojoflow_db.division',obj,"[TGT]");
1674                        }
1675                    });
1676                }else if(settingMultiple[0].state == 1){ //multiple division code enabled
1677                    q = "   select a.row_id,\
1678                            a.division_code code,\
1679                            a.division name,\
1680                            a.description,\
1681                            1 as state,\
1682                            case when a.head_of_division is null then 0\
1683                            else\
1684                            (select x.id from jojoflow_db.organigram x where a.head_of_division = x.code and x.state = 1 and x.company_id ="+dataPostinger[0].company_id+" limit 1) end head_organigram_id,\
1685                            "+ dataPostinger[0].company_id +" as company_id\
1686                            from syn_staging.import_division a\
1687                            where a.process_status = 'processing'\
1688                            and a.division_id = 0 \
1689                            and a.batch_id = '" + insertedBatchId + "'";
1690
1691                    var dataInsert = synchro.query(q, "[TGT]");
1692                    obj = dataInsert.map(function(o) {
1693                        return {
1694                            company_id : o.company_id,
1695                            name : o.name,
1696                            code : o.code,
1697                            head_organigram_id : o.head_organigram_id,
1698                            description : o.description,
1699                            state : o.state,
1700                            created_date : new Date(),
1701                            updated_date : new Date(),
1702                            created_by : dataPostinger[0].company_user_id,
1703                            updated_by : dataPostinger[0].company_user_id
1704                        }});
1705                    synchro.save('jojoflow_db.division',obj,"[TGT]")
1706                };
1707
1708                synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][6] " + fileName + " Done Inserting"}], '[TGT]');
1709
1710                /*************** DONE INSERTING  ****************/
1711
1712
1713                /*************** START VALIDATE  ****************/
1714                synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][7] " + fileName + " Start Validate Update"}], '[TGT]');
1715
1716                q = "select a.row_id,\
1717                        a.division_id id,\
1718                        a.division_code code,\
1719                        a.division name,\
1720                        a.description,\
1721                        1 as state,\
1722                        case when a.head_of_division is null then 0\
1723                        else\
1724                        (select x.id from jojoflow_db.organigram x where a.head_of_division = x.code and x.state = 1 and x.company_id ="+dataPostinger[0].company_id+" limit 1) end head_organigram_id,\
1725                        flow_member,\
1726                        flow_type,\
1727                        flow_minimum_action,\
1728                        process_status,\
1729                        "+ dataPostinger[0].company_id +" as company_id\
1730                        from import_division a\
1731                        where a.batch_id = '" + insertedBatchId + "'\
1732                        and a.division_id > 0";
1733
1734                var resultq = synchro.query(q, "[TGT]");
1735                if (resultq.length > 0) {
1736                    resultq.forEach(function(o){
1737                        if(o.flow_member){
1738                            var listMember = o.flow_member.split(';');
1739                            if (listMember.length > 0) {
1740                                if(o.flow_minimum_action > listMember.length ){
1741                                    q = "   update import_division set process_status = 'error', \
1742                                            log_message = concat_ws('|',log_message, 'flow minimum action must be less or equal to flow member')\
1743                                            where batch_id = '" + insertedBatchId + "'\
1744                                            and row_id = "+ o.row_id;
1745                                    synchro.query(q, "[TGT]");
1746                                }else{
1747                                    var i = 0;
1748                                    while(i < listMember.length){
1749                                        q = "select count(1) c from jojoflow_db.organigram where id = '"+listMember[i]+"' and state = 1 and company_id = "+ dataPostinger[0].company_id;
1750                                        result = synchro.query(q, "[TGT]");
1751                                        if (result[0].c == 0) {
1752                                            q = "   update import_division set  \
1753                                                    process_status = 'error',\
1754                                                    log_message = concat_ws('|',log_message, 'member "+listMember[i]+" not exist')\
1755                                                    where batch_id = '" + insertedBatchId + "'\
1756                                                    and row_id = "+ o.row_id;
1757                                            result = synchro.query(q, "[TGT]");
1758                                        }
1759                                        i++;
1760                                    }
1761                                }
1762                            }
1763                        }
1764                    });
1765                }
1766                synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][8] " + fileName + " Done Validate Update"}], '[TGT]');
1767                /*************** DONE VALIDATE  ****************/
1768
1769                /*************** START UPDATING  ****************/
1770                synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][9] " + fileName + " Start Updating"}], '[TGT]');
1771                q = "select a.row_id,\
1772                        a.division_id id,\
1773                        a.division_code code,\
1774                        a.division name,\
1775                        a.description,\
1776                        1 as state,\
1777                        case when a.head_of_division is null or a.head_of_division = 0 then 0\
1778                        else\
1779                        (select x.id from jojoflow_db.organigram x where a.head_of_division = x.code and x.state = 1 and x.company_id ="+dataPostinger[0].company_id+" limit 1) end head_organigram_id,\
1780                        flow_member,\
1781                        flow_type,\
1782                        flow_minimum_action,\
1783                        process_status,\
1784                        "+ dataPostinger[0].company_id +" as company_id\
1785                        from import_division a\
1786                        where a.batch_id = '" + insertedBatchId + "'\
1787                        and a.division_id > 0\
1788                        and process_status = 'processing'";
1789
1790                var resultq = synchro.query(q, "[TGT]");
1791                if (resultq.length > 0) {
1792                    resultq.forEach(function(o){
1793                        var DivisionCodeStatus = true;
1794                        if (settingMultiple[0].state == 4) {
1795                            //check division code if exist on another division
1796                            q = "   select count(1) c from jojoflow_db.division d where d.code = '" + o.code + "'\
1797                                    and d.id != "+o.id +" and d.state = 1 and d.company_id = "+dataPostinger[0].company_id;
1798                            resultq = synchro.query(q, "[TGT]");
1799
1800                            if(resultq[0].c > 0){
1801                                q = "   update import_division set process_status = 'error', \
1802                                        log_message = concat_ws('|',log_message, 'division_code exist')\
1803                                        where process_status = 'processing'\
1804                                        and row_id = "+ o.row_id +"\
1805                                        and batch_id = '" + insertedBatchId + "'";
1806                                result = synchro.query(q, "[TGT]");
1807                                DivisionCodeStatus = false;
1808                            }
1809                        }
1810                        if (DivisionCodeStatus) {
1811                            var dataUpdate = [{
1812                                id: o.id,
1813                                company_id: o.company_id,
1814                                name: o.name,
1815                                code: o.code,
1816                                head_organigram_id: o.head_organigram_id,
1817                                description: o.description,
1818                                state: o.state,
1819                                updated_date: new Date(),
1820                                updated_by: dataPostinger[0].company_user_id
1821                            }];
1822                            synchro.save('jojoflow_db.division:id',dataUpdate,"[TGT]");
1823
1824                            //check division_flow
1825                            q = "select id,division_id,flow_id,type,`order` from jojoflow_db.division_flow where id in (select max(id) from jojoflow_db.division_flow where state = 1 and division_id = "+o.id+")";
1826                            var dataDivFlow = synchro.query(q, "[TGT]");
1827                            var divisionName;
1828                            if (dataDivFlow.length > 0) {
1829                                q = "update jojoflow_db.flow set type = "+o.flow_type+", minimum_action = "+o.flow_minimum_action+",updated_date = NOW(), updated_by = '"+dataPostinger[0].company_user_id+"' where id = "+dataDivFlow[0].flow_id;
1830                                synchro.query(q, "[TGT]");
1831
1832                                q = "update jojoflow_db.division_flow set type = null where id = "+dataDivFlow[0].id;
1833                                synchro.query(q, "[TGT]");
1834
1835                                q = "update jojoflow_db.flow_member set state = 4,updated_date = NOW()\
1836                                    where flow_id = '"+ dataDivFlow[0].flow_id +"' and state = 1";
1837                                synchro.query(q, "[TGT]");
1838                                if(o.flow_member){
1839                                    var listMember = o.flow_member.split(';');
1840                                    if (listMember.length > 0) {
1841                                        var i = 0;
1842                                        while(i < listMember.length){
1843                                            // updateData = [{
1844                                            //     flow_id: dataDivFlow[0].flow_id,
1845                                            //     organigram_id: listMember[i],
1846                                            //     `order`: i + 1,
1847                                            //     state: 1,
1848                                            //     active: 1,
1849                                            //     created_date:new Date(),
1850                                            //     updated_date: new Date()
1851                                            // }];
1852                                            // synchro.save('jojoflow_db.flow_member',updateData,"[TGT]");
1853                                            q = "insert into jojoflow_db.flow_member (flow_id,organigram_id,`order`,state,active,created_date,updated_date) values ("+dataDivFlow[0].flow_id+","+listMember[i]+","+ (parseInt(i)+1) +",1,1,now(),now())";
1854                                            synchro.query(q,"[TGT]");
1855                                            i++;
1856                                        }
1857                                    }
1858                                }
1859                            }else{
1860                               if (o.flow_member) {
1861                                    var listMember = o.flow_member.split(';');
1862                                    if (listMember.length > 0) {
1863                                        divisionName = 'FL-DVS-'+o.id;
1864                                        var insertFlow = [{
1865                                            name : divisionName,
1866                                            company_id : dataPostinger[0].company_id,
1867                                            type: o.flow_type,
1868                                            minimum_action: o.flow_minimum_action,
1869                                            division_id: o.id,
1870                                            state: 1,
1871                                            created_date: new Date(),
1872                                            updated_date: new Date(),
1873                                            created_by: dataPostinger[0].company_user_id,
1874                                            updated_by: dataPostinger[0].company_user_id
1875                                         }];
1876                                        synchro.save('jojoflow_db.flow',insertFlow,"[TGT]");
1877
1878                                        //get flow id
1879                                        q = "   select max(id) id from jojoflow_db.flow\
1880                                                where name='"+ divisionName +"' and division_id = "+o.id+ " and company_id = "+dataPostinger[0].company_id;
1881                                        result = synchro.query(q, "[TGT]");
1882                                        var flow_id = result[0].id;
1883                                        var i = 0;
1884                                        while (i < listMember.length) {
1885                                            q = "insert into jojoflow_db.flow_member (flow_id,organigram_id,`order`,state,active,created_date,updated_date) values ("+flow_id+","+listMember[i]+","+ (parseInt(i)+1) +",1,1,now(),now())";
1886                                            synchro.query(q,"[TGT]");
1887                                            i++;
1888                                        }
1889                                        //create division flow
1890                                        var insertDivFlow = [{
1891                                            division_id: o['id'],
1892                                            flow_id: flow_id,
1893                                            state: 1,
1894                                            created_date:new Date(),
1895                                            updated_date:new Date(),
1896                                            created_by: dataPostinger[0].company_user_id,
1897                                            updated_by: dataPostinger[0].company_user_id
1898                                        }];
1899                                        synchro.save('jojoflow_db.division_flow',insertDivFlow,"[TGT]")
1900                                    }
1901                                } 
1902                            }
1903                        }
1904                    });
1905                }
1906
1907                synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][10] " + fileName + " Done Updating"}], '[TGT]');
1908                /*************** DONE UPDATING  ****************/
1909            }
1910        }
1911        
1912        // update status 'processing' to 'success'
1913        q = "update import_division set process_status = 'success' \
1914        where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
1915        synchro.query(q, "[TGT]");
1916
1917         // update status 'processing' to 'success'
1918        q = "update import_division set description = log_message ,log_message = null\
1919        where batch_id = '" + insertedBatchId + "'";
1920        synchro.query(q, "[TGT]");
1921
1922        // update summary and status done to import_batch
1923        q = "update import_batch set description = 'finish', batch_endate = current_timestamp, batch_status = 'done', \
1924        data_success = (select count(1) from import_division where process_status = 'success' and batch_id = '" + insertedBatchId + "'), \
1925        data_error = (select count(1) from import_division where process_status = 'error' and batch_id = '" + insertedBatchId + "') \
1926        where batch_id = '" + insertedBatchId + "'";
1927        synchro.query(q, "[TGT]");
1928        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][11] " + fileName + " finished"}], '[TGT]');
1929
1930    } catch (err) {
1931        // update summary and status done to import_batch
1932        // throw err;
1933        var errorMessage = JSON.stringify(err.message).split("'").join("")
1934        errorMessage = errorMessage.split('"').join('')
1935        errorMessage = errorMessage.replace("java.sql.SQLException: ", "")
1936        if(errorMessage.toString().toLowerCase().indexOf("java.lang.reflect.InvocationTargetException".toLowerCase()) != -1) {
1937            errorMessage = "Invalid header format";
1938        }
1939        q = "update import_batch set batch_endate = current_timestamp, batch_status = 'failed', description = '" + errorMessage + "', data_total = (select count(1) from import_division where batch_id = '" + insertedBatchId + "'), data_success = (select count(1) from import_division where process_status = 'success' and batch_id = '" + insertedBatchId + "'), data_error = (select count(1) from import_division where process_status = 'error' and batch_id = '" + insertedBatchId + "') where batch_id = '" + insertedBatchId + "'";
1940        synchro.query(q, "[TGT]");
1941        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][99] " + fileName + " error:" + JSON.stringify(err.message).replace("'", "")}], '[TGT]');
1942    }
1943};
1944
1945function processImportAmountPolicy(fileName, insertedBatchId){
1946    synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][1]"+fileName+" start"}], '[TGT]');
1947    var q = '';
1948    
1949    try {
1950        // insert from csv into import_amount_policy
1951        //***************** START ******************
1952        q = "select trim(id) id , trim(policy_name) policy_name, trim(maximum_amount) maximum_amount, trim(expense_category_id) expense_category_id, trim(position_layer_id) position_layer_id from \"" + fileName + "\"";
1953        var dataCSV = synchro.query(q, "cimbimports");
1954        var rowId = 0;
1955        var objCSV = [];
1956        var regexMaxAmount = "^[0-9]+(.[0-9]+)*$" ;
1957        var regexPolicyID = "^[0-9]*$" ;
1958        dataCSV.forEach(function(o){
1959            rowId++;
1960            var obj = {
1961                batch_id : insertedBatchId,
1962                row_id: rowId,
1963                policy_id: o['id'],
1964                policy_name: o['policy_name'],
1965                maximum_amount: o['maximum_amount'],
1966                expense_category_id: o['expense_category_id'],
1967                position_layer_id: o['position_layer_id'],
1968                process_status: 'processing'
1969            };
1970            objCSV.push(obj);
1971        });
1972        synchro.save('import_amount_policy:batch_id,row_id', objCSV, "[TGT]");
1973        
1974        // update import_amount_policy kolom user_company_id
1975        q = "update import_amount_policy set user_company_id = (select x.user_company_id from import_batch x where x.batch_id = '" + insertedBatchId + "' ) where batch_id = '" + insertedBatchId + "'";
1976        synchro.query(q, "[TGT]");
1977        
1978        // update import_batch column data_total
1979        q = "update import_batch set data_total = " + dataCSV.length + " where batch_id = '" + insertedBatchId + "'";
1980        synchro.query(q, "[TGT]");
1981        //***************** END ******************
1982        
1983        // validasi data required
1984        //***************** START ******************
1985        //validate kolom user_company_id
1986        q = "update import_amount_policy set process_status = 'error', description = concat_ws('|', description, 'user_company_id invalid') where batch_id = '" + insertedBatchId + "' and user_company_id not in (select distinct x.id from jojonomic_db.company_user x where x.id = import_amount_policy.user_company_id)";
1987        // DAMP.log(q.toString());
1988        synchro.query(q, "[TGT]");
1989        
1990        //validate kolom id required
1991        q = "update import_amount_policy set process_status = 'error', description = concat_ws('|', description, 'policy_id cannot be empty') where batch_id = '" + insertedBatchId + "' and policy_id is null";
1992        // DAMP.log(q.toString());
1993        synchro.query(q, "[TGT]");
1994        
1995        //validate kolom policy_name required
1996        q = "update import_amount_policy set process_status = 'error', description = concat_ws('|', description, 'policy_name cannot be empty') where batch_id = '" + insertedBatchId + "' and policy_name is null";
1997        synchro.query(q, "[TGT]");
1998        
1999        //validate kolom maximum_amount required
2000        q = "update import_amount_policy set process_status = 'error', description = concat_ws('|', description, 'maximum_amount cannot be empty') where batch_id = '" + insertedBatchId + "' and maximum_amount is null";
2001        synchro.query(q, "[TGT]");
2002        //***************** END ******************
2003        
2004        // validasi content data
2005        //***************** START ******************
2006        //validate kolom policy_id integer only
2007        q = "update import_amount_policy set process_status = 'error', description = concat_ws('|', description, 'policy_id Incorrect Integer value') where batch_id = '" + insertedBatchId + "' and policy_id  not regexp'"+regexPolicyID+"' ";
2008        // DAMP.log(q.toString());
2009        synchro.query(q, "[TGT]");
2010        
2011        //validate kolom policy_name length > 3
2012        q = "update import_amount_policy set process_status = 'error', description = concat_ws('|', description, 'policy_name length less than 3 characters') where batch_id = '" + insertedBatchId + "' and length(policy_name) <= 3";
2013        // DAMP.log(q.toString());
2014        synchro.query(q, "[TGT]");
2015        
2016        //validate kolom maximum_amount numeric only
2017        q = "update import_amount_policy set process_status = 'error', description = concat_ws('|', description, 'maximum_amount Incorrect decimal value') where batch_id = '" + insertedBatchId + "' and maximum_amount not regexp '"+regexMaxAmount+"' ";
2018        // DAMP.log(q.toString());
2019        synchro.query(q, "[TGT]");
2020        
2021        //validate kolom policy_id invalid
2022        q = "update import_amount_policy set process_status = 'error', description = concat_ws('|', description, 'policy_id Invalid value') where batch_id = '"+insertedBatchId+"' and policy_id <> '0' and policy_id not in (select x.id from expense_db.amount_policy x where x.state = 1)";
2023        // DAMP.log(q.toString());
2024        synchro.query(q, "[TGT]");
2025        
2026        //validate kolom expense_category_id numeric and semicolon only
2027        q = " call func_validate_amountpolicy('"+insertedBatchId+"') ";
2028        synchro.query(q, "[TGT]");
2029        
2030        
2031        //add by arif @11-0-2020
2032        //********************  VALIDATE CATEGORY ID *********************
2033        //********************  START *********************
2034        q = "select a.row_id, a.batch_id, a.expense_category_id, (select x.company_id from jojonomic_db.company_user x where x.id = a.user_company_id) company_id from import_amount_policy a where batch_id = '"+insertedBatchId+"' and process_status = 'processing' and expense_category_id is not null" ;
2035        //DAMP.log(q);
2036        var targetRes = synchro.query(q, "[TGT]");
2037        targetRes.forEach(function(o){
2038            if(o['expense_category_id']) {
2039                q = "select count(*) c from jojonomic_db.company_expense a where a.company_id = '"+o['company_id']+"' and a.id in ("+o['expense_category_id'].replace(/;/g,',')+") ";
2040                var result = synchro.query(q, "[TGT]");
2041                result = parseInt(result[0]['c']) ;
2042                if(result > 0) {
2043                    var carSplit = o['expense_category_id'].split(";");
2044                    if(carSplit.length > 0) {
2045                        var i = 0 ;
2046                        while(i < carSplit.length) {
2047                            q = "select count(*) c from jojonomic_db.company_expense a where a.company_id = '"+o['company_id']+"' and a.id = "+carSplit[i]+" ";
2048                            //DAMP.log(q);
2049                            var resCheck = synchro.query(q, "[TGT]");
2050                            //DAMP.log(resCheck.toString());
2051                            resCheck = parseInt(resCheck[0]['c']) ;
2052                            //DAMP.log(resCheck.toString()) ;
2053                            if(resCheck == 0) {
2054                                q = "update import_amount_policy set process_status = 'error', description = concat_ws('|', description, 'expense_category_id "+carSplit[i]+" invalid') where batch_id = '"+insertedBatchId+"' and row_id = "+o['row_id']+" ";
2055                                //DAMP.log(q);
2056                                synchro.query(q, "[TGT]");
2057                            }
2058                            
2059                            i++ ;
2060                        }
2061                    }
2062                }else {
2063                    q = "update import_amount_policy set process_status = 'error', description = concat_ws('|', description, 'expense_category_id invalid') where batch_id = '"+insertedBatchId+"' and row_id = "+o['row_id']+" ";
2064                    synchro.query(q, "[TGT]");
2065                }
2066            }
2067        });
2068        //********************  END *********************
2069        
2070        //********************  VALIDATE LAYER ID *********************
2071        //********************  START *********************
2072        q = "select a.row_id, a.batch_id, a.position_layer_id, (select x.company_id from jojonomic_db.company_user x where x.id = a.user_company_id) company_id from import_amount_policy a where batch_id = '"+insertedBatchId+"' and process_status = 'processing' and position_layer_id is not null" ;
2073        var targetRes = synchro.query(q, "[TGT]");
2074        targetRes.forEach(function(o){
2075            if(o['position_layer_id']) {
2076                q = "select count(*) c from jojoflow_db.layer a where a.company_id = '"+o['company_id']+"' and a.id in ("+o['position_layer_id'].replace(/;/g,',')+") ";
2077                var result = synchro.query(q, "[TGT]");
2078                result = parseInt(result[0]['c']) ;
2079                if(result > 0) {
2080                    var carSplit = o['position_layer_id'].split(";");
2081                    if(carSplit.length > 0) {
2082                        var i = 0 ;
2083                        while(i < carSplit.length) {
2084                            q = "select count(*) c from jojoflow_db.layer a where a.company_id = '"+o['company_id']+"' and a.id = " +carSplit[i]+ " ";
2085                            var resCheck = synchro.query(q, "[TGT]");
2086                            resCheck = parseInt(resCheck[0]['c']) ;
2087                            
2088                            if(resCheck == 0) {
2089                                q = "update import_amount_policy set process_status = 'error', description = concat_ws('|', description, 'position_layer_id " +carSplit[i]+ " invalid') where batch_id = '"+insertedBatchId+"' and row_id = "+o['row_id']+" ";
2090                                synchro.query(q, "[TGT]");
2091                            }
2092                            
2093                            i++ ;
2094                        }
2095                    }
2096                }else {
2097                    q = "update import_amount_policy set process_status = 'error', description = concat_ws('|', description, 'position_layer_id invalid' ) where batch_id = '"+insertedBatchId+"' and row_id = "+o['row_id']+" ";
2098                    synchro.query(q, "[TGT]");
2099                }
2100            }
2101        });
2102        //********************  END *********************
2103        //***************** END ******************
2104        
2105        // INSERT DATA VALID
2106        //***************** START ******************
2107        q = " select a.row_id, (select x.company_id ";
2108        q += "        from jojonomic_db.company_user x ";
2109        q += "            where x.id = a.user_company_id) company_id, ";
2110        q += "              a.policy_name as name, ";
2111        q += "              round(a.maximum_amount,2) maximum_amount, ";
2112        q += "              now() as created_date, ";
2113        q += "              '1' as state, a.expense_category_id, a.position_layer_id ";
2114        q += " from import_amount_policy a ";
2115        q += " where a.batch_id = '"+insertedBatchId+"' ";
2116        q += " and a.process_status = 'processing' ";
2117        q += " and a.policy_id = '0' ";
2118        // DAMP.log(q.toString());
2119        // DAMP.log('test arif');
2120        var targetRes = synchro.query(q, "[TGT]");
2121        // DAMP.log('test arif');
2122        targetRes.forEach(function(o){
2123            var objInsertAmountPolicy = [];
2124            objInsertAmountPolicy.push(buildInsertAmountPolicy(o)) ;
2125            
2126            synchro.save('expense_db.amount_policy', objInsertAmountPolicy, "[TGT]") ;
2127            
2128            var qry = "select max(a.id) id from expense_db.amount_policy a where a.name = (select x.policy_name from import_amount_policy x where x.row_id = "+o.row_id+" and x.batch_id = '"+insertedBatchId+"') " ;
2129             //DAMP.log(qry.toString());
2130            var resultPolicyID = synchro.query(qry, "[TGT]");
2131            
2132            if(o['expense_category_id']) {
2133                q = " select a.id as expense_id, '"+o['company_id']+"' as company_id, "+resultPolicyID[0]['id']+" as amount_policy_id, ";
2134                q += "       now() as created_date, ";
2135                q += "           1 as state ";
2136                q += " from jojonomic_db.company_expense a ";
2137                q += " where a.company_id = '"+o['company_id']+"' ";
2138                q += " and a.id in ("+o['expense_category_id'].replace(/;/g,',')+") ";
2139                // DAMP.log(q.toString());
2140                var resultAmountCategRelation = synchro.query(q, "[TGT]");
2141                
2142                if(resultAmountCategRelation.length > 0) {
2143                    var dataAmountCategRelation = resultAmountCategRelation.map(function(data) {
2144                                                      return {
2145                                                        expense_id : data.expense_id,
2146                                                        company_id: data.company_id,
2147                                                        amount_policy_id: data.amount_policy_id,
2148                                                        created_date: new Date(),
2149                                                        updated_date: new Date(),
2150                                                        state: data.state
2151                                                        }
2152                                                    });
2153                    synchro.save('expense_db.amount_policy_category_relation', dataAmountCategRelation, "[TGT]") ;
2154                }
2155            }
2156            
2157            if(o['position_layer_id']) {
2158                q = " select a.id as layer_id, '"+o['company_id']+"' as company_id, "+resultPolicyID[0]['id']+" as amount_policy_id, ";
2159                q += "       now() as created_date, ";
2160                q += "       '1' as state ";
2161                q += " from jojoflow_db.layer a ";
2162                q += " where a.company_id = '"+o['company_id']+"' ";
2163                q += " and a.id in ("+o['position_layer_id'].replace(/;/g,',')+") ";
2164                // DAMP.log(q.toString());
2165                var resultAmountLayerRelation = synchro.query(q, "[TGT]");
2166                
2167                if(resultAmountLayerRelation.length > 0) {
2168                    var dataAmountLayerRelation = resultAmountLayerRelation.map(function(data) {
2169                                                      return {
2170                                                        layer_id : data.layer_id,
2171                                                        company_id: data.company_id,
2172                                                        amount_policy_id: data.amount_policy_id,
2173                                                        created_date: new Date(),
2174                                                        updated_date: new Date(),
2175                                                        state: data.state
2176                                                        }
2177                                                    });
2178                    synchro.save('expense_db.amount_policy_layer_relation', dataAmountLayerRelation, "[TGT]") ;
2179                }
2180            }
2181            
2182            
2183        });
2184        if(targetRes.length > 0) {
2185            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] insert expense_db.amount_policy"}], '[TGT]');
2186            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] insert expense_db.amount_policy_category_relation"}], '[TGT]');
2187            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] insert expense_db.amount_policy_layer_relation"}], '[TGT]');
2188        }
2189        //***************** END ******************
2190        
2191        // UPDATE DATA VALID
2192        //***************** START ******************
2193        q = " select a.policy_id as id,  (select x.company_id ";
2194        q += "        from jojonomic_db.company_user x ";
2195        q += "            where x.id = a.user_company_id) company_id, ";
2196        q += "              a.policy_name as name, ";
2197        q += "              round(a.maximum_amount,2) maximum_amount, ";
2198        q += "              now() as updated_date, ";
2199        q += "              '1' as state, a.expense_category_id, a.position_layer_id ";
2200        q += " from import_amount_policy a ";
2201        q += " where a.batch_id = '"+insertedBatchId+"' ";
2202        q += " and a.process_status = 'processing' ";
2203        q += " and a.policy_id <> '0' ";
2204        
2205        var targetRes = synchro.query(q, "[TGT]");
2206        targetRes.forEach(function(o){
2207            var objUpdateAmountPolicy = [];
2208            objUpdateAmountPolicy.push(buildUpdateAmountPolicy(o)) ;
2209            
2210            synchro.save('expense_db.amount_policy:id', objUpdateAmountPolicy, "[TGT]") ;
2211            
2212            q = " update expense_db.amount_policy_category_relation set state = 4, updated_date = now()  ";
2213            q += " where amount_policy_id = "+o['id']+"  ";
2214            q += " and company_id = '"+o['company_id']+"' ";
2215            //q += " and expense_id in ( ";
2216            //q += " select a.id  ";
2217            //q += " from jojonomic_db.company_expense a ";
2218            //q += " where a.company_id = '"+o['company_id']+"' ";
2219            //q += " and a.id in ("+o['expense_category_id'].replace(/;/g,',')+")) ";
2220            synchro.query(q, "[TGT]");
2221            
2222            if(o['expense_category_id']) {      
2223                q = " select a.id as expense_id, '"+o['company_id']+"' as company_id, "+o['id']+" as amount_policy_id, ";
2224                q += "       now() as created_date, ";
2225                q += "           1 as state ";
2226                q += " from jojonomic_db.company_expense a ";
2227                q += " where a.company_id = '"+o['company_id']+"' ";
2228                q += " and a.id in ("+o['expense_category_id'].replace(/;/g,',')+") ";
2229                var resultAmountCategRelation = synchro.query(q, "[TGT]");
2230                
2231                if(resultAmountCategRelation.length > 0) {
2232                    var dataAmountCategRelation = resultAmountCategRelation.map(function(data) {
2233                                                      return {
2234                                                        expense_id : data.expense_id,
2235                                                        company_id: data.company_id,
2236                                                        amount_policy_id: data.amount_policy_id,
2237                                                        created_date: new Date(),
2238                                                        state: data.state
2239                                                        }
2240                                                    });
2241                    synchro.save('expense_db.amount_policy_category_relation', dataAmountCategRelation, "[TGT]") ;
2242                }
2243            }
2244            
2245            q = " update expense_db.amount_policy_layer_relation set state = 4, updated_date = now() ";
2246            q += " where company_id = '"+o['company_id']+"' ";
2247            q += " and amount_policy_id = "+o['id']+" ";
2248            //q += " and layer_id in ( ";
2249            //q += " select a.id as layer_id ";
2250            //q += " from jojoflow_db.layer a ";
2251            //q += " where a.company_id = '"+o['company_id']+"' ";
2252            //q += " and a.id in ("+o['position_layer_id'].replace(/;/g,',')+")) ";
2253            synchro.query(q, "[TGT]");
2254            if(o['position_layer_id']) {        
2255                q = " select a.id as layer_id, '"+o['company_id']+"' as company_id, "+o['id']+" as amount_policy_id, ";
2256                q += "       now() as created_date, ";
2257                q += "       '1' as state ";
2258                q += " from jojoflow_db.layer a ";
2259                q += " where a.company_id = '"+o['company_id']+"' ";
2260                q += " and a.id in ("+o['position_layer_id'].replace(/;/g,',')+") ";
2261                var resultAmountLayerRelation = synchro.query(q, "[TGT]");
2262                
2263                if(resultAmountLayerRelation.length > 0) {
2264                    var dataAmountLayerRelation = resultAmountLayerRelation.map(function(data) {
2265                                                      return {
2266                                                        layer_id : data.layer_id,
2267                                                        company_id: data.company_id,
2268                                                        amount_policy_id: data.amount_policy_id,
2269                                                        created_date: new Date(),
2270                                                        state: data.state
2271                                                        }
2272                                                    });
2273                    synchro.save('expense_db.amount_policy_layer_relation', dataAmountLayerRelation, "[TGT]") ;
2274                }
2275            }
2276            
2277        }) ;
2278        if(targetRes.length > 0) {
2279            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] update expense_db.amount_policy"}], '[TGT]');
2280            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] delete expense_db.amount_policy_category_relation"}], '[TGT]');
2281            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] insert expense_db.amount_policy_category_relation"}], '[TGT]');
2282            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] delete expense_db.amount_policy_layer_relation"}], '[TGT]');
2283            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] insert expense_db.amount_policy_layer_relation"}], '[TGT]');         
2284        }
2285        //***************** END ******************
2286        
2287
2288        // update status 'processing' to 'success'
2289        q = "update import_amount_policy set process_status = 'success' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
2290        synchro.query(q, "[TGT]")
2291        // update summary and status done to import_batch
2292        q = "update import_batch set batch_endate = current_timestamp,description = 'finish', batch_status = 'done', data_success = (select count(1) from import_amount_policy where process_status = 'success' and batch_id = '" + insertedBatchId + "'), data_error = (select count(1) from import_amount_policy where process_status = 'error' and batch_id = '" + insertedBatchId + "') where batch_id = '" + insertedBatchId + "'";
2293        synchro.query(q, "[TGT]");
2294        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"] " + fileName + " finished"}], '[TGT]');
2295        
2296    } catch (err) {
2297        // update summary and status done to import_batch
2298        var errorMessage = JSON.stringify(err.message).split("'").join("")
2299        errorMessage = errorMessage.split('"').join('')
2300        errorMessage = errorMessage.replace("java.sql.SQLException: ", "")
2301        if(errorMessage.toString().toLowerCase().indexOf("java.lang.reflect.InvocationTargetException".toLowerCase()) != -1) {
2302            errorMessage = "Invalid header format";
2303        }
2304        q = "update import_batch set batch_endate = current_timestamp, batch_status = 'failed', description = '" + errorMessage + "', data_total = 0, data_success = (select count(1) from import_amount_policy where process_status = 'success' and batch_id = '" + insertedBatchId + "'), data_error = (select count(1) from import_amount_policy where process_status = 'error' and batch_id = '" + insertedBatchId + "') where batch_id = '" + insertedBatchId + "'";
2305        synchro.query(q, "[TGT]");
2306        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][99] " + fileName + " error:" + JSON.stringify(err.message).replace("'", "")}], '[TGT]');
2307    }
2308};
2309
2310function upsertDivision(RequestId, DivisionId, DivisionCode, Division, Description,  HeadOfDivision, FlowMember, FlowType, FlowMinimumAction , State, user_company_id){
2311
2312    var batchName = 'upsert_division';
2313    var batchId = getBatchId(batchName, RequestId);
2314    var q = '';
2315    var allowedState = [1,4];
2316    var allowedFlowType = [1,2,3];
2317    var insertedBatchId = '';
2318
2319    try {
2320
2321        //val updater
2322        var dataPostinger = synchro.query("select x.company_id,x.id company_user_id from jojonomic_db.company_user x where x.id = "+user_company_id, "[TGT]");
2323
2324        // validate DivisionID
2325        var exec = true;
2326        if (!RequestId) {
2327            exec = false;
2328            throw 'invalid request id';
2329        }
2330
2331        if (dataPostinger.length == 0) {
2332            exec = false;
2333            throw 'invalid user_company_id';
2334        }
2335
2336        if (!(allowedState.indexOf(parseInt(State)) >= 0)) {
2337            exec = false;
2338            throw 'state is not allowed';
2339        }
2340
2341        var settingMultiple;
2342        if (dataPostinger.length != 0) {
2343            q = "select state from jojoflow_db.setting where company_id = "+ dataPostinger[0].company_id +" and name = 'SETTING_MULTIPLE_DIVISION_CODE'";
2344            settingMultiple = synchro.query(q, "[TGT]");
2345            if(settingMultiple.length == 0){
2346                exec = false;
2347                throw 'Setting multiple code not found';
2348            }else {
2349                q = "select count(1) c from jojoflow_db.division where id = '" + DivisionId + "' and company_id = "+dataPostinger[0].company_id;
2350                var valDivisionId = synchro.query(q, "[TGT]");
2351                valDivisionId = parseInt(valDivisionId[0]['c']);
2352
2353                if (valDivisionId < 1) {
2354                    exec = false;
2355                    throw 'invalid division_id';
2356                };
2357                if (parseInt(State) == 1) {
2358                    if (Division.length <= 3) {
2359                        exec = false;
2360                        throw 'division name less than 3 characters';
2361                    };
2362
2363                    if(Description){
2364                        Description = Description.trim();
2365                    };
2366
2367                    if(HeadOfDivision){
2368                        HeadOfDivision = HeadOfDivision.trim();
2369                    };
2370
2371                    if(FlowMember){
2372                        FlowMember = FlowMember.trim();
2373                    };
2374
2375                    if(FlowMinimumAction){
2376                        FlowMinimumAction = FlowMinimumAction.trim();
2377                        if(!isInteger(FlowMinimumAction)){
2378                            exec = false;
2379                            throw 'flow minimum action must be int';
2380                        }
2381                    }else{
2382                        FlowMinimumAction = null;
2383                    };
2384
2385                    if(FlowType){
2386                        FlowType = FlowType.trim();
2387                        if(!isInteger(FlowType)){
2388                            exec = false;
2389                            throw 'flow type must be int';
2390                        }
2391                    }else{
2392                        FlowType = null;
2393                    };
2394
2395                    if (FlowMember != null && FlowType == null) {
2396                        exec = false;
2397                        throw 'Flow type cannot be empty, when flow_member is not null';
2398                    };
2399
2400                    if(FlowType){
2401                        if (!(allowedFlowType.indexOf(parseInt(FlowType)) >= 0)) {
2402                            exec = false;
2403                            throw 'flow type is not allowed';
2404                        };
2405
2406                        if (parseInt(FlowType) == 3 && (FlowMinimumAction == null || FlowMinimumAction.length < 1)) {
2407                            exec = false;
2408                            throw 'flow minimum action mandatory if flow type = 3';
2409                        };
2410                    };
2411
2412                    if (HeadOfDivision) {
2413                        q = "select count(1) c from jojoflow_db.organigram where `code` ='"+HeadOfDivision+"' and state = 1 and company_id ="+dataPostinger[0].company_id;
2414                        var valHeadOfDivision = synchro.query(q, "[TGT]");
2415                        valHeadOfDivision = parseInt(valHeadOfDivision[0]['c']);
2416                        if (valHeadOfDivision < 1) {
2417                            exec = false;
2418                            throw 'invalid head_of_division';
2419                        }
2420                    };
2421
2422
2423                    if (!HeadOfDivision){
2424                        HeadOfDivision = 0;
2425                    };
2426
2427                    if(settingMultiple[0].state == 4){
2428                        q = "select count(1) c from jojoflow_db.division d where d.code = '" + DivisionCode + "'\
2429                             and d.id != '"+DivisionId +"' and d.state = 1 and d.company_id ="+dataPostinger[0].company_id;
2430                        var valMultiDivCode = synchro.query(q, "[TGT]");
2431                        valMultiDivCode = parseInt(valMultiDivCode[0]['c']);
2432                        if (valMultiDivCode >= 1) {
2433                            exec = false;
2434                            throw 'division_code is exists';
2435                        };
2436                    };
2437
2438                    if(FlowMember){
2439                        var listMember = FlowMember.split(";");
2440                        if(listMember.length > 0){
2441                            //error if minimum action greater than flow member
2442                            if(FlowMinimumAction > listMember.length ){
2443                                exec = false;
2444                                throw 'flow minimum action must be less or equal flow_member';
2445                            }else{
2446                                i = 0;
2447                                stopMember = false;
2448                                while(i < listMember.length && stopMember == false){
2449                                    q = "select count(1) c from jojoflow_db.organigram where id = '"+listMember[i]+"' and state = 1 and company_id = "+ dataPostinger[0].company_id;
2450                                    result = synchro.query(q, "[TGT]");
2451                                    if (result[0].c == 0) {
2452                                        exec = false;
2453                                        stopMember = true;
2454                                        throw ['member ', listMember[i],' not exists'].join(' ');
2455                                    }
2456                                    i++;
2457                                }
2458                            }
2459                        }
2460                    }
2461                }
2462            }
2463        }
2464
2465        if (exec) {
2466            if (isBatchIdExists(batchId) == false) {
2467                insertedBatchId = addBatch(batchId, batchName, RequestId, user_company_id);
2468                var upsertData = [{
2469                    batch_id: insertedBatchId,
2470                    division_id: DivisionId,
2471                    division_code: DivisionCode,
2472                    division: Division,
2473                    description: Description,
2474                    head_of_division: HeadOfDivision,
2475                    flow_member: FlowMember,
2476                    flow_type: FlowType,
2477                    flow_minimum_action: FlowMinimumAction,
2478                    state: State,
2479                    user_company_id: user_company_id,
2480                    process_status: 'processing'
2481                }];
2482                synchro.save('import_upsert_division', upsertData, "[TGT]");
2483
2484                if (parseInt(State) == 1) {
2485                    var HeadOrganigramID;
2486                    if (HeadOfDivision != 0) {
2487                        q = "select x.id from jojoflow_db.organigram x where x.code = '"+HeadOfDivision+"' and state = 1 and x.company_id ="+dataPostinger[0].company_id;
2488                        var HeadOrganigram = synchro.query(q,"[TGT]");
2489                        HeadOrganigramID = HeadOrganigram[0].id;
2490                    }else{
2491                        HeadOrganigramID = HeadOfDivision;
2492                    }
2493                    
2494                    var dataUpdate = [{
2495                        id: DivisionId,
2496                        company_id: dataPostinger[0].company_id,
2497                        name: Division,
2498                        code: DivisionCode,
2499                        head_organigram_id: HeadOrganigramID,
2500                        description: Description,
2501                        state: State,
2502                        updated_date: new Date(),
2503                        updated_by: dataPostinger[0].company_user_id
2504                    }];
2505                    synchro.save('jojoflow_db.division:id',dataUpdate,"[TGT]");
2506
2507                    //check division_flow
2508                    q = "select id,division_id,flow_id,type,`order` from jojoflow_db.division_flow where id in (select max(id) from jojoflow_db.division_flow where state = 1 and division_id = "+DivisionId+")";
2509                    var dataDivFlow = synchro.query(q, "[TGT]");
2510
2511                    if (dataDivFlow.length > 0) {
2512                        q = "update jojoflow_db.division_flow set type = null where id = "+dataDivFlow[0].id;
2513                                synchro.query(q, "[TGT]"); 
2514
2515                        q = "update jojoflow_db.flow_member set state = 4,updated_date = NOW()\
2516                            where flow_id = '"+ dataDivFlow[0].flow_id +"' and state=1";
2517                        synchro.query(q, "[TGT]");
2518
2519                        q = "update jojoflow_db.flow set type = "+FlowType+", minimum_action = "+FlowMinimumAction+",updated_date = NOW(), updated_by = '"+dataPostinger[0].company_user_id+"' where id = "+dataDivFlow[0].flow_id;
2520                        synchro.query(q, "[TGT]");
2521
2522                        var divisionName;
2523                        if(FlowMember){
2524                            var listMember = FlowMember.split(';');
2525                            if(listMember.length > 0){
2526
2527                                var i = 0;
2528                                while(i < listMember.length){
2529                                    // updateData = [{
2530                                    //     flow_id: dataDivFlow[0].flow_id,
2531                                    //     organigram_id: listMember[i],
2532                                    //     `order`: i + 1,
2533                                    //     state: 1,
2534                                    //     active: 1,
2535                                    //     created_date:new Date(),
2536                                    //     updated_date: new Date()
2537                                    // }];
2538                                    // synchro.save('jojoflow_db.flow_member',updateData,"[TGT]");
2539                                    q = "insert into jojoflow_db.flow_member (flow_id,organigram_id,`order`,state,active,created_date,updated_date) values ("+dataDivFlow[0].flow_id+","+listMember[i]+","+ (parseInt(i)+1) +",1,1,now(),now())";
2540                                    synchro.query(q,"[TGT]");
2541                                    i++;
2542                                }
2543                            }
2544                        }
2545                    }else{
2546                        if(FlowMember){
2547                            var listMember = FlowMember.split(';');
2548                            if(listMember.length > 0){
2549                                divisionName = 'FL-DVS-'+DivisionId;
2550                                var insertFlow = [{
2551                                    name : divisionName,
2552                                    company_id : dataPostinger[0].company_id,
2553                                    type: FlowType,
2554                                    minimum_action: FlowMinimumAction,
2555                                    division_id: DivisionId,
2556                                    state: 1,
2557                                    created_date: new Date(),
2558                                    updated_date: new Date(),
2559                                    created_by: dataPostinger[0].company_user_id,
2560                                    updated_by: dataPostinger[0].company_user_id
2561                                 }];
2562                                synchro.save('jojoflow_db.flow',insertFlow,"[TGT]");
2563
2564                                //get flow id
2565                                q = "select max(id) id from jojoflow_db.flow\
2566                                        where name='"+ divisionName +"' and division_id = "+DivisionId+ " and company_id = "+dataPostinger[0].company_id;
2567                                result = synchro.query(q, "[TGT]");
2568                                var flow_id = result[0].id;
2569
2570                                i = 0;
2571                                while(i < listMember.length){
2572                                    // updateData = [{
2573                                    //     flow_id: dataDivFlow[0].flow_id,
2574                                    //     organigram_id: listMember[i],
2575                                    //     `order`: i + 1,
2576                                    //     state: 1,
2577                                    //     active: 1,
2578                                    //     created_date:new Date(),
2579                                    //     updated_date: new Date()
2580                                    // }];
2581                                    // synchro.save('jojoflow_db.flow_member',updateData,"[TGT]");
2582                                    q = "insert into jojoflow_db.flow_member (flow_id,organigram_id,`order`,state,active,created_date,updated_date) values ("+flow_id+","+listMember[i]+","+ (parseInt(i)+1) +",1,1,now(),now())";
2583                                    synchro.query(q,"[TGT]");
2584                                    i++;
2585                                }
2586                                //create division flow
2587                                var insertDivFlow = [{
2588                                    division_id: DivisionId,
2589                                    flow_id: flow_id,
2590                                    state: 1,
2591                                    created_date:new Date(),
2592                                    updated_date:new Date(),
2593                                    created_by: dataPostinger[0].company_user_id,
2594                                    updated_by: dataPostinger[0].company_user_id
2595                                }];
2596                                synchro.save('jojoflow_db.division_flow',insertDivFlow,"[TGT]")
2597                            }
2598                        }
2599                    }
2600                }else{
2601                    q = "update jojoflow_db.division set state = 4, updated_date=now() where id = "+DivisionId+" and state=1";
2602                    synchro.query(q, "[TGT]");
2603
2604                    //get flow id
2605                    q = "select max(flow_id) id from jojoflow_db.division_flow\
2606                            where division_id = "+DivisionId;
2607                    result = synchro.query(q, "[TGT]");
2608                    var flow_id = result[0].id;
2609                    
2610                    q = "update jojoflow_db.flow_member set state = 4, updated_date=now() where flow_id = "+flow_id+" and state=1";
2611                    synchro.query(q, "[TGT]");
2612
2613                    q = "update jojoflow_db.division_flow set state = 4, updated_date=now() where division_id = "+DivisionId+" and state=1";
2614                    synchro.query(q, "[TGT]");
2615
2616                    
2617                    q = "update jojoflow_db.flow set state = 4, updated_date=now() where division_id = "+DivisionId+" and state=1";
2618                    synchro.query(q, "[TGT]");
2619                };
2620
2621                // update status 'processing' to 'success'
2622                q = "update import_upsert_division set process_status = 'success' \
2623                where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
2624                synchro.query(q, "[TGT]");
2625
2626                 // update status 'processing' to 'success'
2627                q = "update import_upsert_division set description = log_message ,log_message = null\
2628                where batch_id = '" + insertedBatchId + "'";
2629                synchro.query(q, "[TGT]");
2630
2631                // update summary and status done to import_batch
2632                q = "update import_batch set description = 'finish', batch_endate = current_timestamp, batch_status = 'done', \
2633                data_success = (select count(1) from import_upsert_division where process_status = 'success' and batch_id = '" + insertedBatchId + "'), \
2634                data_error = (select count(1) from import_upsert_division where process_status = 'error' and batch_id = '" + insertedBatchId + "') \
2635                where batch_id = '" + insertedBatchId + "'";
2636                synchro.query(q, "[TGT]");
2637                return {
2638                    responseCode: 200,
2639                    responseMessage: "success"
2640                };
2641            } else {
2642                return {
2643                    responseCode: 400,
2644                    responseMessage: 'batchId already exist'
2645                };
2646            }
2647        };
2648    } catch(err) {
2649        var errorMessage = JSON.stringify(err.toString()).split("'").join("")
2650        errorMessage = errorMessage.split('"').join('')
2651        errorMessage = errorMessage.replace("java.sql.SQLException: ", "")
2652        errorMessage = errorMessage.replace(/[^\w\s]/gi, '')
2653        // update status 'processing' to 'success'
2654        q = "update import_upsert_division set process_status = 'error' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
2655        synchro.query(q, "[TGT]")
2656        // update summary and status done to import_batch
2657        q = "update import_batch set batch_endate = current_timestamp, batch_status = 'failed', data_success = (select count(1) from import_upsert_division where process_status = 'success' and batch_id = '" + insertedBatchId + "'), data_error = (select count(1) from import_upsert_division where process_status = 'error' and batch_id = '" + insertedBatchId + "') where batch_id = '" + insertedBatchId + "'";
2658        synchro.query(q, "[TGT]");
2659        q = "update import_upsert_division set description='"+errorMessage+"' where batch_id='"+insertedBatchId+"'";
2660        synchro.query(q, "[TGT]");
2661        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_UPSERT_DIVISION][99] " + batchId + " error:" + JSON.stringify(err.toString()).replace("'", "")}], '[TGT]');
2662        return {
2663            responseCode: 400,
2664            responseMessage: err.toString()
2665        };
2666    }
2667};
2668
2669function upsertAmountPolicy(requestId, policyId, policyName, maximumAmount, expenseCategoryId, positionLayerId, state, userCompanyId) {
2670    var batchName = 'upsert_amount_policy';
2671    var batchId = getBatchId(batchName, requestId);
2672    var insertedBatchId = "";
2673    var q = '';
2674    var allowedState = [1,4];
2675
2676    try {
2677        // validate request body
2678        // validate policy id
2679        q = "select count(1) c from expense_db.amount_policy where id = " + policyId;
2680        var validatePolicyID = synchro.query(q, "[TGT]");
2681        validatePolicyID = parseInt(validatePolicyID[0]['c']);
2682        
2683        // validate user_company_id
2684        q = "select count(1) c from jojonomic_db.company_user where id = " + userCompanyId;
2685        var validateUser = synchro.query(q, "[TGT]");
2686        validateUser = parseInt(validateUser[0]['c']);
2687        
2688        // validate policy_name
2689        var validatePolicyName = false ;
2690        if(policyName.length <= 3) {
2691            validatePolicyName = true ;
2692        }
2693
2694        var validateExpense = false ;
2695        var validateLayer = false ;
2696        if(state == '1') {
2697            if(expenseCategoryId) {
2698                // validate expense_category_id
2699                q = "select count(*) c from jojonomic_db.company_expense a where a.company_id = (select x.company_id from jojonomic_db.company_user x where x.id = "+userCompanyId+") and a.id in ("+expenseCategoryId.replace(/;/g,',')+") ";
2700                var result = synchro.query(q, "[TGT]");
2701                result = parseInt(result[0]['c']) ;
2702                if(result > 0) {
2703                    var carSplit = expenseCategoryId.split(";");
2704                    if(carSplit.length > 0) {
2705                        var i = 0 ;
2706                        while(i < carSplit.length) {
2707                            q = "select count(*) c from jojonomic_db.company_expense a where a.company_id = (select x.company_id from jojonomic_db.company_user x where x.id = "+userCompanyId+") and a.id = "+carSplit[i]+" ";
2708                            //DAMP.log(q);
2709                            var resCheck = synchro.query(q, "[TGT]");
2710                            //DAMP.log(resCheck.toString());
2711                            resCheck = parseInt(resCheck[0]['c']) ;
2712                            //DAMP.log(resCheck.toString()) ;
2713                            if(resCheck == 0) {
2714                                validateExpense = true ;
2715                            }           
2716                            i++ ;
2717                        }
2718                    }
2719                }else {
2720                    validateExpense = true ;
2721                }
2722            }
2723            
2724            if(positionLayerId) {
2725                // validate position_layer_id
2726                q = "select count(*) c from jojoflow_db.layer a where a.company_id = (select x.company_id from jojonomic_db.company_user x where x.id = "+userCompanyId+") and a.id in ("+positionLayerId.replace(/;/g,',')+") ";
2727                var result = synchro.query(q, "[TGT]");
2728                result = parseInt(result[0]['c']) ;
2729                if(result > 0) {
2730                    var carSplit = positionLayerId.split(";");
2731                    if(carSplit.length > 0) {
2732                        var i = 0 ;
2733                        while(i < carSplit.length) {
2734                            q = "select count(*) c from jojoflow_db.layer a where a.company_id = (select x.company_id from jojonomic_db.company_user x where x.id = "+userCompanyId+") and a.id = " +carSplit[i]+ " ";
2735                            var resCheck = synchro.query(q, "[TGT]");
2736                            resCheck = parseInt(resCheck[0]['c']) ;
2737                                        
2738                            if(resCheck == 0) {
2739                                validateLayer = true ;
2740                            }  
2741                            i++ ;
2742                        }
2743                    }
2744                }else {
2745                    validateLayer = true ;
2746                }
2747            }
2748        }
2749
2750        var exec = true;
2751        if (!requestId) {
2752            exec = false;
2753            throw 'invalid request id';
2754        }
2755        if (!(allowedState.indexOf(parseInt(state)) >= 0)) {
2756            exec = false;
2757            throw 'state is not alowed';
2758        }
2759        if (validatePolicyID < 1) {
2760            exec = false;
2761            throw 'invalid policy_id';
2762        }
2763        if(validatePolicyName) {
2764            exec = false;
2765            throw 'policy_name length is invalid';
2766        }
2767        if (validateUser < 1) {
2768            exec = false;
2769            throw 'invalid user_company_id';
2770        }
2771        if(state == '1') {
2772            if (validateExpense) {
2773                exec = false;
2774                throw 'invalid expense_category_id';
2775            }
2776            if (validateLayer) {
2777                exec = false;
2778                throw 'invalid position_layer_id';
2779            }
2780        }
2781
2782        if (exec){
2783            if (isBatchIdExists(batchId) == false) {
2784                // add batch
2785                insertedBatchId = addBatch(batchId, batchName, requestId, userCompanyId);
2786                var upsertData = {
2787                    batch_id: insertedBatchId,
2788                    policy_id: policyId,
2789                    policy_name: policyName,
2790                    maximum_amount: maximumAmount,
2791                    expense_category_id: expenseCategoryId,
2792                    position_layer_id: positionLayerId,
2793                    user_company_id: userCompanyId,
2794                    state: state,
2795                    process_status: 'processing'
2796                };
2797                synchro.save('import_upsert_amount_policy', [upsertData], "[TGT]");
2798                
2799                if(state == '1') {
2800                    // UPDATE DATA VALID
2801                    //***************** START ******************
2802                    q = " select a.policy_id as id,  (select x.company_id ";
2803                    q += "        from jojonomic_db.company_user x ";
2804                    q += "            where x.id = a.user_company_id) company_id, ";
2805                    q += "              a.policy_name as name, ";
2806                    q += "              round(a.maximum_amount,2) maximum_amount, ";
2807                    q += "              now() as updated_date, ";
2808                    q += "              a.state as state, a.expense_category_id, a.position_layer_id ";
2809                    q += " from import_upsert_amount_policy a ";
2810                    q += " where a.batch_id = '" + insertedBatchId + "' ";
2811                    q += " and a.process_status = 'processing' ";
2812                    q += " and a.policy_id = "+policyId+" ";
2813
2814                    var targetRes = synchro.query(q, "[TGT]");
2815                    targetRes.forEach(function (o) {
2816                        var objUpdateAmountPolicy = [];
2817                        objUpdateAmountPolicy.push(buildUpdateAmountPolicy(o));
2818
2819                        synchro.save('expense_db.amount_policy:id', objUpdateAmountPolicy, "[TGT]");
2820
2821                        
2822                        q = " update expense_db.amount_policy_category_relation set state = 4, updated_date = now()  ";
2823                        q += " where amount_policy_id = " + o['id'] + "  ";
2824                        q += " and company_id = '" + o['company_id'] + "' ";
2825                        //q += " and expense_id in ( ";
2826                        //q += " select a.id  ";
2827                        //q += " from jojonomic_db.company_expense a ";
2828                        //q += " where a.company_id = '" + o['company_id'] + "' ";
2829                        //q += " and a.id in (" + o['expense_category_id'].replace(/;/g, ',') + ")) ";
2830                        synchro.query(q, "[TGT]");
2831                        if (o['expense_category_id']) {
2832                            q = " select a.id as expense_id, '" + o['company_id'] + "' as company_id, " + o['id'] + " as amount_policy_id, ";
2833                            q += "       now() as created_date, ";
2834                            q += "           1 as state ";
2835                            q += " from jojonomic_db.company_expense a ";
2836                            q += " where a.company_id = '" + o['company_id'] + "' ";
2837                            q += " and a.id in (" + o['expense_category_id'].replace(/;/g, ',') + ") ";
2838                            var resultAmountCategRelation = synchro.query(q, "[TGT]");
2839
2840                            if (resultAmountCategRelation.length > 0) {
2841                                var dataAmountCategRelation = resultAmountCategRelation.map(function (data) {
2842                                    return {
2843                                        expense_id: data.expense_id,
2844                                        company_id: data.company_id,
2845                                        amount_policy_id: data.amount_policy_id,
2846                                        created_date: new Date(),
2847                                        state: data.state
2848                                    }
2849                                });
2850                                synchro.save('expense_db.amount_policy_category_relation', dataAmountCategRelation, "[TGT]");
2851                            }
2852                        }
2853                        
2854                        q = " update expense_db.amount_policy_layer_relation set state = 4, updated_date = now() ";
2855                        q += " where company_id = '" + o['company_id'] + "' ";
2856                        q += " and amount_policy_id = " + o['id'] + " ";
2857                        //q += " and layer_id in ( ";
2858                        //q += " select a.id as layer_id ";
2859                        //q += " from jojoflow_db.layer a ";
2860                        //q += " where a.company_id = '" + o['company_id'] + "' ";
2861                        //q += " and a.id in (" + o['position_layer_id'].replace(/;/g, ',') + ")) ";
2862                        synchro.query(q, "[TGT]");
2863                        
2864                        if (o['position_layer_id']) {
2865                            q = " select a.id as layer_id, '" + o['company_id'] + "' as company_id, " + o['id'] + " as amount_policy_id, ";
2866                            q += "       now() as created_date, ";
2867                            q += "       '1' as state ";
2868                            q += " from jojoflow_db.layer a ";
2869                            q += " where a.company_id = '" + o['company_id'] + "' ";
2870                            q += " and a.id in (" + o['position_layer_id'].replace(/;/g, ',') + ") ";
2871                            var resultAmountLayerRelation = synchro.query(q, "[TGT]");
2872
2873                            if (resultAmountLayerRelation.length > 0) {
2874                                var dataAmountLayerRelation = resultAmountLayerRelation.map(function (data) {
2875                                    return {
2876                                        layer_id: data.layer_id,
2877                                        company_id: data.company_id,
2878                                        amount_policy_id: data.amount_policy_id,
2879                                        created_date: new Date(),
2880                                        state: data.state
2881                                    }
2882                                });
2883                                synchro.save('expense_db.amount_policy_layer_relation', dataAmountLayerRelation, "[TGT]");
2884                            }
2885                        }
2886
2887                    });
2888                }else {
2889                    q = "update expense_db.amount_policy set state = 4, updated_date=now() where id = "+policyId+"";
2890                    synchro.query(q, "[TGT]");
2891                    
2892                    q = "update expense_db.amount_policy_category_relation set state = 4, updated_date=now() where amount_policy_id = "+policyId+"";
2893                    synchro.query(q, "[TGT]");
2894                    
2895                    q = "update expense_db.amount_policy_layer_relation set state = 4, updated_date=now() where amount_policy_id = "+policyId+"";
2896                    synchro.query(q, "[TGT]");
2897                }
2898                // update status 'processing' to 'success'
2899                q = "update import_upsert_amount_policy set process_status = 'success' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
2900                synchro.query(q, "[TGT]")
2901                // update summary and status done to import_batch
2902                q = "update import_batch set batch_endate = current_timestamp, batch_status = 'done', data_success = (select count(1) from import_upsert_amount_policy where process_status = 'success' and batch_id = '" + insertedBatchId + "'), data_error = (select count(1) from import_upsert_amount_policy where process_status = 'error' and batch_id = '" + insertedBatchId + "') where batch_id = '" + insertedBatchId + "'";
2903                synchro.query(q, "[TGT]");
2904                
2905                return {
2906                    responseCode: 200,
2907                    responseMessage: "success"
2908                };
2909
2910            } else {
2911                return {
2912                    responseCode: 400,
2913                    responseMessage: 'batchId already exist'
2914                };
2915            }
2916        }
2917
2918    } catch(err) {
2919
2920        var errorMessage = JSON.stringify(err.toString()).split("'").join("");
2921        errorMessage = errorMessage.split('"').join('')
2922        errorMessage = errorMessage.replace("java.sql.SQLException: ", "")
2923        errorMessage = errorMessage.replace(/[^\w\s]/gi, '')
2924        // update status 'processing' to 'success'
2925        q = "update import_upsert_amount_policy set process_status = 'error' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
2926        synchro.query(q, "[TGT]")
2927        // update summary and status done to import_batch
2928        q = "update import_batch set batch_endate = current_timestamp, batch_status = 'failed', data_success = (select count(1) from import_upsert_amount_policy where process_status = 'success' and batch_id = '" + insertedBatchId + "'), data_error = (select count(1) from import_upsert_amount_policy where process_status = 'error' and batch_id = '" + insertedBatchId + "') where batch_id = '" + insertedBatchId + "'";
2929        synchro.query(q, "[TGT]");
2930        q = "update import_upsert_amount_policy set description='"+errorMessage+"' where batch_id='"+insertedBatchId+"'";
2931        synchro.query(q, "[TGT]");
2932        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_UPSERT_AMOUNT_POLICY]["+insertedBatchId+"][99] " + batchId + " error:" + JSON.stringify(err.toString()).replace("'", "")}], '[TGT]');
2933        return {
2934            responseCode: 400,
2935            responseMessage: err.toString()
2936        };
2937
2938    }
2939
2940};
2941
2942function dump(arr,level) {
2943    var dumped_text = "";
2944    if(!level) level = 0;
2945    
2946    //The padding given at the beginning of the line.
2947    var level_padding = "";
2948    for(var j=0;j<level+1;j++) level_padding += "    ";
2949    
2950    if(typeof(arr) == 'object') { //Array/Hashes/Objects 
2951        for(var item in arr) {
2952            var value = arr[item];
2953            
2954            if(typeof(value) == 'object') { //If it is an array,
2955                dumped_text += level_padding + "'" + item + "' ...\n";
2956                dumped_text += dump(value,level+1);
2957            } else {
2958                dumped_text += level_padding + "'" + item + "' => \"" + value + "\"\n";
2959            }
2960        }
2961    } else { //Stings/Chars/Numbers etc.
2962        dumped_text = "===>"+arr+"<===("+typeof(arr)+")";
2963    }
2964    return dumped_text;
2965};
2966
2967var is_allow = true;
2968function PublishServiceWS(e) {
2969    DAMP.WSStart(e.name);
2970    try {
2971        for (;;) {
2972            // try {
2973                var r = DAMP.WSFetchRequest(e.name);
2974                r.params["auth_bearer_token"] = "";
2975                r.params["auth_basic_username"] = "";
2976                r.params["auth_basic_password"] = "";
2977                if(typeof r.headers["authorization"] != "undefined") {
2978                    var authorization = r.headers["authorization"];
2979
2980                    if(authorization && authorization.indexOf("Bearer") != -1) {
2981                        var token = authorization.match(/Bearer (.*)/);
2982                        if(token) {
2983                            r.params["auth_bearer_token"] = token[1];
2984
2985                        }
2986                    }
2987                    if(authorization && authorization.indexOf("Basic") != -1) {
2988                        var token = authorization.match(/Basic (.*)/);
2989                        if(token) {
2990                            var token_b64 = token[1];
2991                            var basic = Base64.decode(token_b64).split(":");
2992                            if(basic) {
2993                                r.params["auth_basic_username"] = basic[0];
2994                                r.params["auth_basic_password"] = basic[1];
2995                                // throw JSON.stringify({
2996                                //     usr: basic[0],
2997                                //     pass: basic[1]
2998                                // });
2999                            }
3000                        }
3001                    }
3002                }
3003                // throw JSON.stringify(r.params);
3004
3005                if(typeof(e.guard) == "string") {
3006                    var c = build_query.from_string(
3007                        e.guard, r.params
3008                    );
3009                    
3010                    if(c.length <= 0) {
3011                        r.response = JSON.stringify({
3012                            responseCode: 401,
3013                            responseMessage: "Unauthorized"
3014                        });
3015                        DAMP.WSResponse(r);
3016                    } else {   
3017                        init_ws(e, r);
3018                    }
3019                } else if(e.guard instanceof Array) {
3020                    var is_allow = true;
3021                    for(var i=0; i < e.guard.length; i++) {
3022                        if(is_allow) {
3023                            var o = e.guard[i];
3024                            var b = build_query.from_string(
3025                                o.query, r.params
3026                            );
3027                            if(b.length <= 0 || b instanceof Object) {
3028                                is_allow = false;
3029                            }
3030                        }
3031                    }
3032                    if(is_allow) {
3033                        init_ws(e, r);
3034                    } else {
3035                        var code, message;
3036                        if(!o.error) {
3037                            code = 401;
3038                            message = "Unauthorized"
3039                        } else {
3040                            code = o.error.code;
3041                            message = o.error.message;
3042                        }
3043                        r.response = JSON.stringify({
3044                            responseCode: code,
3045                            responseMessage: message
3046                        });
3047                        DAMP.WSResponse(r);
3048                    }
3049                } else {
3050                    init_ws(e, r);
3051                }
3052            // } catch(err) {
3053            //     r.responseCode = "520";
3054            //     DAMP.WSResponse(r)
3055            // }
3056        }
3057    } finally {
3058        DAMP.WSStop(e.name);
3059    }
3060}