ybWGvPC6

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'] && r.params['position_id'] && r.params['position_code'] && r.params['position'] && r.params['state'] && r.params['user_company_id']) {
284                // call function processor
285                var res = upsertPos(r.params['request_id'],r.params['position_id'],r.params['position_code'],r.params['position'],r.params['email'],r.params['divisions'],r.params['layer_code'],r.params['supervisor_code'],r.params['state'],r.params['user_company_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,position_id,position_code,position,state,user_company_id",
292                    request_id: r.params['request_id'],
293                    position_id: r.params['position_id'], 
294                    position_code: r.params['position_code'],
295                    state: r.params['state'],
296                    user_company_id: r.params['user_company_id'],
297
298                });
299                DAMP.WSResponse(r);
300            }
301        } else if (r.params["batchname"] == 'upsert_division'){
302            // validate params
303            if (r.params['request_id'] && r.params['division_id'] && r.params['division_code'] && r.params['division'] && r.params['state'] && r.params['user_company_id']) {
304                // call function processor
305                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']);
306                r.response = JSON.stringify(res);
307                DAMP.WSResponse(r);
308            } else {
309                r.response = JSON.stringify({
310                    responseCode: 500,
311                    responseMessage: "mandatory parameters: request_id,division_id,division_code,division,state,user_company_id",
312                    request_id: r.params['request_id'],
313                    division_id: r.params['division_id'], 
314                    division_code: r.params['division_code'],
315                    division: r.params['division'],
316                    state: r.params['state'],
317                    user_company_id: r.params['user_company_id'],
318                });
319                DAMP.WSResponse(r);
320            }
321        } else if (r.params["batchname"] == 'upsert_amount_policy'){
322            // validate params
323            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']) {
324                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']);
325                r.response = JSON.stringify(res);
326                DAMP.WSResponse(r);
327            } else {
328                r.response = JSON.stringify({
329                    responseCode: 500,
330                    responseMessage: "mandatory parameters: request_id, policy_id, policy_name, maximum_amount, state",
331                    request_id: r.params['request_id'],
332                    policy_id: r.params['policy_id'], 
333                    policy_name: r.params['policy_name'],
334                    expense_category_id: r.params['expense_category_id'],
335                    position_layer_id: r.params['position_layer_id'],
336                    state: r.params['state']
337                });
338                DAMP.WSResponse(r);
339            }
340        } else {
341            r.response = JSON.stringify({
342                responseCode: 400,
343                responseMessage: "invalid batchname"
344            });
345            DAMP.WSResponse(r);
346        }
347    } // Start By Hafid
348    else if(e.use == "cimb_api_gabung") {
349
350        try {
351            
352            if(!r.params["action"]) {
353                throw "Parameter \'action\' required";
354            }
355            
356            switch(r.params["action"]) {
357
358                case 'get_batch_list':
359                    var query = {
360                        offset: "select coalesce(<<offset>>, 0) as offset",
361                        limit: "select coalesce(<<limit>>, 10) as lim",
362                        total_data: "call get_import_batch_filter_count(<<batch_name>>, <<startdate>>, <<enddate>>, <<status>>)",
363                        data: "call get_import_batch_filter(<<batch_name>>, <<offset>>, <<limit>>, <<startdate>>, <<enddate>>, <<status>>, <<sort_field>>, <<sort_direction>>)"
364                    };
365                    r.response = JSON.stringify(build_query.from_object(query, r.params));
366                    DAMP.WSResponse(r);
367                break;
368                case 'get_batch_detail':
369                    var query = {
370                        offset: "select coalesce(<<offset>>, 0) as offset",
371                        limit: "select coalesce(<<limit>>, 10) as lim",
372                        total_data: "call get_import_batch_detail_count(<<batch_name>>, <<batch_id>>)",
373                        data: "call get_import_batch_detail(<<batch_name>>, <<batch_id>>, <<offset>>, <<limit>>)"
374                    };
375                    r.response = JSON.stringify(build_query.from_object(query, r.params));
376                    DAMP.WSResponse(r);
377                break;
378                case 'start_batch':
379                    synchro.save('logs', [{ts:new Date(), msg: dump(r)}], '[TGT]');
380                    if (
381                        r.params["batchname"] == 'mapping_position'
382                        || r.params["batchname"] == 'position'
383                        || r.params["batchname"] == 'division'
384                        || r.params["batchname"] == 'amount_policy')
385                        {
386                        if (r.params["filename"] && r.params["filename"].length > 0) {
387                            var res = submitBatch(r.params["batchname"], r.params["filename"], r.params["user_company_id"]);
388                            r.response = JSON.stringify(res);
389                            DAMP.WSResponse(r);
390                        } else {
391                            r.response = JSON.stringify({
392                                responseCode: 500,
393                                responseMessage: "filename cannot be empty"
394                            });
395                            DAMP.WSResponse(r);
396                        }
397                    } else if (r.params["batchname"] == 'upsert_mapping_position'){
398                        if (r.params['request_id'] && r.params['category_id'] && r.params['group_category_id'] && r.params['state']) {
399                            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']));
400                            r.response = JSON.stringify(res);
401                            DAMP.WSResponse(r);
402                        } else {
403                            r.response = JSON.stringify({
404                                responseCode: 500,
405                                responseMessage: "mandatory parameters: request_id, category_id, group_category_id, state",
406                                request_id: r.params['request_id'],
407                                position_id: r.params['position_id'], 
408                                group_category_id: r.params['group_category_id'],
409                                state: r.params['state']
410                            });
411                            DAMP.WSResponse(r);
412                        }
413                    } else if (r.params["batchname"] == 'upsert_position'){
414                        // validate params
415                        if (r.params['request_id']) {
416                            // call function processor
417                            var res = upsertPos(r.params['request_id']);
418                            r.response = JSON.stringify(res);
419                            DAMP.WSResponse(r);
420                        } else {
421                            r.response = JSON.stringify({
422                                responseCode: 500,
423                                responseMessage: "mandatory parameters: request_id",
424                                request_id: r.params['request_id'],
425                                position_id: r.params['position_id'], 
426                                group_category_id: r.params['group_category_id'],
427                                state: r.params['state']
428                            });
429                            DAMP.WSResponse(r);
430                        }
431                    } else if (r.params["batchname"] == 'upsert_division'){
432                        // validate params
433                        if (r.params['request_id']) {
434                            // call function processor
435                            var res = upsertDivision(r.params['request_id']);
436                            r.response = JSON.stringify(res);
437                            DAMP.WSResponse(r);
438                        } else {
439                            r.response = JSON.stringify({
440                                responseCode: 500,
441                                responseMessage: "mandatory parameters: request_id",
442                                request_id: r.params['request_id'],
443                                position_id: r.params['position_id'], 
444                                group_category_id: r.params['group_category_id'],
445                                state: r.params['state']
446                            });
447                            DAMP.WSResponse(r);
448                        }
449                    } else if (r.params["batchname"] == 'upsert_'){
450                        // validate params
451                        if (r.params['request_id']) {
452                            // call function processor
453                            var res = upsertAmount(r.params['request_id']);
454                            r.response = JSON.stringify(res);
455                            DAMP.WSResponse(r);
456                        } else {
457                            r.response = JSON.stringify({
458                                responseCode: 500,
459                                responseMessage: "mandatory parameters: request_id",
460                                request_id: r.params['request_id'],
461                                position_id: r.params['position_id'], 
462                                group_category_id: r.params['group_category_id'],
463                                state: r.params['state']
464                            });
465                            DAMP.WSResponse(r);
466                        }
467                    } else {
468                        r.response = JSON.stringify({
469                            responseCode: 400,
470                            responseMessage: "invalid batchname"
471                        });
472                        DAMP.WSResponse(r);
473                    }
474                break;
475                default:
476                    throw 'Action not registered'
477                break;
478            }
479
480        } catch(e) {
481            r.response = JSON.stringify({
482                responseCode: 400,
483                responseMessage: e.toString()
484            });
485            DAMP.WSResponse(r);
486        }
487    }
488    // End By Hafid
489    else {
490        if(typeof(e.query) == "string") {
491            try {
492                r.response = JSON.stringify(
493                    build_query.from_string(e.query, r.params)
494                );
495                DAMP.WSResponse(r);
496            } catch(e) {
497                DAMP.log(e), r.responseCode = "520", DAMP.WSResponse(r)
498            }
499        } else if(e.query instanceof Object) {
500            try {
501                r.response = JSON.stringify(
502                    build_query.from_object(e.query, r.params)
503                );
504                DAMP.WSResponse(r);
505            } catch(e) {
506                // throw JSON.stringify(e);
507                DAMP.log(e), r.responseCode = "520", DAMP.WSResponse(r)
508            }
509        } else if(e.query instanceof Array) {
510            try {
511                r.response = JSON.stringify(
512                    build_query.from_object(e.query, r.params)
513                );
514                DAMP.WSResponse(r);
515            } catch (e) {
516                DAMP.log(e), r.responseCode = "520", DAMP.WSResponse(r)
517            }
518        }
519    }
520};
521
522// JOBS CIMB
523var 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-_};
524
525function checkJobReady(batchName){
526    var checked = synchro.query("select count(1) as c from import_batch where batch_status = 'running' and batch_name = '" + batchName + "'", "[TGT]");
527    var c = parseInt(checked[0]['c']);
528    if (c > 0) {
529        return false;
530    }
531    return true;
532};
533
534function getBatchId(batchName, fileName){
535    return MD5(batchName + fileName);
536};
537
538function isBatchIdExists(batchId){
539    var checked = synchro.query("select count(1) as c from import_batch where batch_id = '" + batchId + "'", "[TGT]");
540    var c = parseInt(checked[0]['c']);
541    if (c > 0) {
542        return true;
543    }
544    return false;
545};
546
547function addBatch(batchId, batchName, fileName, user_company_id){
548    if (isBatchIdExists(batchId) == false) {
549        var b = {}
550        b['batch_id'] = batchId;
551        b['batch_name'] = batchName;
552        b['batch_status'] = 'running';
553        b['batch_filename'] = fileName;
554        b['user_company_id'] = user_company_id;
555        synchro.save('import_batch', [b], "[TGT]");
556        return batchId;
557    }
558    return null;
559};
560
561function submitBatch(batchName, fileName, user_company_id){
562    var batchId = getBatchId(batchName, fileName);
563    var q = '';
564    if (isBatchIdExists(batchId) == false) {
565        var insertedBatchId = addBatch(batchId, batchName, fileName, user_company_id);
566        if (insertedBatchId) {
567            return {
568                responseCode: 200,
569                responseMessage: "success"
570            };
571        } else {
572            return {
573                responseCode: 500,
574                responseMessage: "error while create new batch"
575            };
576        }
577    } else {
578        return {
579            responseCode: 400,
580            responseMessage: "batch id already exists"
581        };
582    }
583};
584
585function buildCategoryRelation(t, o, currTime){
586    return {
587        company_id: o['company_id'],
588        position_category_relation_id: o['position_category_relation_id'],
589        rule_id: o['rule_id'],
590        rule_name: o['rule_name'],
591        state: o['state'],
592        type: t,
593        created_date: currTime,
594        updated_date: currTime
595    }
596};
597
598function buildInsertAmountPolicy(o){
599    return {
600        company_id: o['company_id'],
601        name: o['name'],
602        maximum_amount: o['maximum_amount'],
603        created_date: new Date(),
604        updated_date: new Date(),
605        state: o['state']
606    }
607}
608
609function buildUpdateAmountPolicy(o){
610    return {
611        id: o['id'],
612        company_id: o['company_id'],
613        name: o['name'],
614        maximum_amount: o['maximum_amount'],
615        updated_date: new Date(),
616        state: o['state']
617    }
618}
619
620function processImportMappingPos(fileName, insertedBatchId){
621    synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_MAPPING_POSITION]["+insertedBatchId+"][1]"+fileName+" start"}], '[TGT]');
622    var q = '';
623    
624    try {
625        // check file exist with select data
626        q = "select position_id, group_category_id from \"" + fileName + "\"";
627        var dataCSV = synchro.query(q, "cimbimports");
628        var rowId = 0;
629        var objCSV = [];
630        dataCSV.forEach(function(o){
631            rowId++;
632            var obj = {
633                batch_id : insertedBatchId,
634                row_id: rowId,
635                position_id: o['position_id'],
636                group_category_id: o['group_category_id'],
637                process_status: 'processing'
638            };
639            objCSV.push(obj);
640        });
641        synchro.save('import_mapping_position:batch_id,row_id', objCSV, "[TGT]");
642        
643        // update batch data_total
644        q = "update import_batch set data_total = " + dataCSV.length + " where batch_id = '" + insertedBatchId + "'";
645        synchro.query(q, "[TGT]")
646        // validate group_category_id
647        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";
648        synchro.query(q, "[TGT]")
649        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)";
650        synchro.query(q, "[TGT]")
651        // validate position_id
652        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";
653        synchro.query(q, "[TGT]");
654        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)";
655        synchro.query(q, "[TGT]");
656        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)";
657        synchro.query(q, "[TGT]");
658        // 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)";
659        // synchro.query(q, "[TGT]")
660        // insert valid data to target table expense_db.position_category_relation
661        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";
662        q += " from import_mapping_position imp";
663        q += " left join jojonomic_db.company_expense_group_relation rel on rel.company_expense_group_id = imp.group_category_id and rel.state = 1";
664        q += " left join jojoflow_db.organigram org on org.id = imp.position_id and org.state = 1";
665        q += " left join jojoflow_db.organigram_rule rul on rul.organigram_id = org.id and rul.state = 1";
666        q += " where imp.process_status = 'processing' and imp.batch_id = '" + insertedBatchId + "'";
667        var newData = synchro.query(q, "[TGT]");
668        var objRelations = [];
669        var currTime = new Date();
670        newData.forEach(function(o){
671            var obj = {
672                company_id: o['company_id'],
673                position_id: o['position_id'],
674                position_name: o['position_name'],
675                expense_id: o['expense_id'],
676                expense_group_id: o['expense_group_id'],
677                state: 1,
678                created_date: currTime,
679                updated_date: currTime
680            };
681            objRelations.push(obj);
682        });
683        synchro.save('expense_db.position_category_relation:expense_id,expense_group_id,position_id', objRelations, "[TGT]");
684        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_MAPPING_POSITION]["+insertedBatchId+"][2]upsert expense_db.position_category_relation"}], '[TGT]');
685        
686        // insert to expense_db.category_rule_relation
687        // category_type = 1 -> masukan 1 row dengan type 1
688        // category_type = 2 -> masukan 2 row dengan type 2 pada row pertama kemudian type 3 pada row kedua
689        // category_type = 4 -> masukan 2 row dengan type 14 pada row pertama kemudian type 15 pada row kedua
690        // category_type = 5 -> masukan 1 row dengan type 13
691        // category_type = 6 -> masukan 2 row dengan type 16 pada row pertama kemudian type 17 pada row kedua
692        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";
693        q += " from import_mapping_position imp";
694        q += " left join jojonomic_db.company_expense_group_relation rel on rel.company_expense_group_id = imp.group_category_id and rel.state = 1";
695        q += " left join jojonomic_db.company_expense exp on exp.id = rel.company_expense_id";
696        q += " left join jojoflow_db.organigram org on org.id = imp.position_id and org.state = 1";
697        q += " left join jojoflow_db.organigram_rule rul on rul.organigram_id = org.id and rul.state = 1";
698        q += " left join expense_db.position_category_relation target on target.expense_id = rel.company_expense_id and target.position_id = org.id";
699        q += " left join jojoflow_db.rule rule on rule.id = rul.rule_id";
700        q += " where org.id is not null and imp.process_status = 'processing' and imp.batch_id = '" + insertedBatchId + "'";
701        var targetRes = synchro.query(q, "[TGT]");
702        var objCategories = [];
703        targetRes.forEach(function(o){
704            var categoryType = parseInt(o['category_type']);
705            if (categoryType == 1) {
706                objCategories.push(buildCategoryRelation(1, o, currTime));
707            } else if (categoryType == 2) {
708                objCategories.push(buildCategoryRelation(2, o, currTime));
709                objCategories.push(buildCategoryRelation(3, o, currTime));
710            } else if (categoryType == 4) {
711                objCategories.push(buildCategoryRelation(14, o, currTime));
712                objCategories.push(buildCategoryRelation(15, o, currTime));
713            } else if (categoryType == 5) {
714                objCategories.push(buildCategoryRelation(13, o, currTime));
715            } else if (categoryType == 6) {
716                objCategories.push(buildCategoryRelation(16, o, currTime));
717                objCategories.push(buildCategoryRelation(17, o, currTime));
718            }
719        });
720        synchro.save('expense_db.category_rule_relation:company_id,position_category_relation_id,rule_id,type', objCategories, "[TGT]")
721        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_MAPPING_POSITION]["+insertedBatchId+"][3] upsert expense_db.category_rule_relation"}], '[TGT]');
722
723        // update status 'processing' to 'success'
724        q = "update import_mapping_position set process_status = 'success' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
725        synchro.query(q, "[TGT]")
726        // update summary and status done to import_batch
727        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 + "'";
728        synchro.query(q, "[TGT]");
729        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_MAPPING_POSITION]["+insertedBatchId+"][4] " + fileName + " finished"}], '[TGT]');
730        
731    } catch (err) {
732        // update summary and status done to import_batch
733        var errorMessage = JSON.stringify(err.message).split("'").join("")
734        errorMessage = errorMessage.split('"').join('')
735        errorMessage = errorMessage.replace("java.sql.SQLException: ", "")
736        if(errorMessage.toString().toLowerCase().indexOf("java.lang.reflect.InvocationTargetException".toLowerCase()) != -1) {
737            errorMessage = "Invalid header format";
738        }
739        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 + "'";
740        synchro.query(q, "[TGT]");
741        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_MAPPING_POSITION]["+insertedBatchId+"][99] " + fileName + " error:" + JSON.stringify(err.message).replace("'", "")}], '[TGT]');
742    }
743};
744
745function upsertMappingPos(requestId, categoryId, groupCategoryId, state, user_company_id){
746    var batchName = 'upsert_mapping_position';
747    var batchId = getBatchId(batchName, requestId);
748    var q = '';
749    var allowedState = [1,4];
750    // validate request body
751    // validate group_category_id
752    q = "select count(1) c from jojonomic_db.company_expense_group where id = " + groupCategoryId;
753    var validateCat = synchro.query(q, "[TGT]");
754    validateCat = parseInt(validateCat[0]['c'])
755    // validate category_id
756    q = "select count(1) c from jojonomic_db.company_expense where id = " + categoryId;
757    var validatePos = synchro.query(q, "[TGT]");
758    validatePos = parseInt(validatePos[0]['c']);
759    var exec = true;
760    if (!requestId) {
761        exec = false;
762        return {
763            responseCode: 400,
764            responseMessage: 'invalid request id'
765        };
766    }
767    if (!(allowedState.indexOf(state) >= 0)) {
768        exec = false;
769        return {
770            responseCode: 400,
771            responseMessage: 'state is not alowed'
772        };
773    }
774    if (validateCat < 1) {
775        exec = false;
776        return {
777            responseCode: 400,
778            responseMessage: 'invalid group_category_id'
779        };
780    }
781    if (validatePos < 1) {
782        exec = false;
783        return {
784            responseCode: 400,
785            responseMessage: 'invalid category_id'
786        };
787    } 
788    if (exec){
789        if (isBatchIdExists(batchId) == false) {
790            // add batch
791            var insertedBatchId = addBatch(batchId, batchName, requestId, user_company_id);
792            var upsertData = {
793                batch_id: insertedBatchId,
794                category_id: categoryId,
795                group_category_id: groupCategoryId,
796                state: state,
797                process_status: 'processing'
798            };
799            synchro.save('import_upsert_mapping_position', [upsertData], "[TGT]");
800            // insert valid data to target table expense_db.position_category_relation
801            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";
802            q += " from import_upsert_mapping_position imp";
803            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";
804            q += " left join jojoflow_db.organigram org on org.id = posrel.position_id  and org.company_id = posrel.company_id";
805            q += " where posrel.id is not null  and org.id is not null";
806            q += " and imp.process_status = 'processing' and imp.batch_id = '" + insertedBatchId + "'";
807            var updateData = synchro.query(q, "[TGT]");
808            var objRelations = [];
809            var currTime = new Date();
810            updateData.forEach(function(o){
811                var obj = {
812                    company_id: o['company_id'],
813                    position_id: o['position_id'],
814                    position_name: o['position_name'],
815                    expense_id: o['expense_id'],
816                    expense_group_id: o['expense_group_id'],
817                    state: o['state'],
818                    updated_date: currTime
819                };
820                objRelations.push(obj);
821            });
822            synchro.save('expense_db.position_category_relation:company_id,expense_id,expense_group_id,position_id', objRelations, "[TGT]");
823            
824            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";
825            q += " from import_upsert_mapping_position imp";
826            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 ";
827            q += " left join jojonomic_db.company_expense exp on exp.id = imp.category_id";
828            q += " left join jojoflow_db.organigram org on org.id = posrel.position_id and org.company_id = posrel.company_id";
829            q += " left join jojoflow_db.organigram_rule rul on rul.organigram_id = org.id and rul.state = 1";
830            q += " left join expense_db.position_category_relation target on target.expense_id = imp.category_id and target.position_id = org.id";
831            q += " left join jojoflow_db.rule rule on rule.id = rul.rule_id";
832            q += " where posrel.id is not null and org.id is not null";
833            q += " and imp.process_status = 'processing' and imp.batch_id = '" + insertedBatchId + "'";
834            var targetRes = synchro.query(q, "[TGT]");
835            var objCategories = [];
836            targetRes.forEach(function(o){
837                var categoryType = parseInt(o['category_type']);
838                if (categoryType == 1) {
839                    objCategories.push(buildCategoryRelation(1, o, currTime));
840                } else if (categoryType == 2) {
841                    objCategories.push(buildCategoryRelation(2, o, currTime));
842                    objCategories.push(buildCategoryRelation(3, o, currTime));
843                } else if (categoryType == 4) {
844                    objCategories.push(buildCategoryRelation(14, o, currTime));
845                    objCategories.push(buildCategoryRelation(15, o, currTime));
846                } else if (categoryType == 5) {
847                    objCategories.push(buildCategoryRelation(13, o, currTime));
848                } else if (categoryType == 6) {
849                    objCategories.push(buildCategoryRelation(16, o, currTime));
850                    objCategories.push(buildCategoryRelation(17, o, currTime));
851                }
852            });
853            DAMP.log(JSON.stringify(objCategories));
854            synchro.save('expense_db.category_rule_relation:company_id,position_category_relation_id,rule_id,type', objCategories, "[TGT]");
855            // update status 'processing' to 'success'
856            q = "update import_upsert_mapping_position set process_status = 'success' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
857            synchro.query(q, "[TGT]")
858            // update summary and status done to import_batch
859            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 + "'";
860            synchro.query(q, "[TGT]");
861            return {
862                responseCode: 200,
863                responseMessage: "success"
864            };
865        } else {
866            return {
867                responseCode: 400,
868                responseMessage: 'batchId already exist'
869            };
870        }
871    }
872};
873
874// import position START
875
876function processChildPosition(organigram_Id,organigram_Head,company_Id,user_Company_Id){
877    //find division_flow
878    var VariableFlow = [];
879    q = "select division_id from jojoflow_db.organigram_division where organigram_id = "+organigram_Id+" and company_id = "+company_Id+" and state=1 order by id limit 1";
880    var resDivisions = synchro.query(q, "[TGT]");
881    if (resDivisions.length > 0){
882        resDivisions.forEach( function(division) {
883            //get division flow  save to variable, --but not used so i skip
884            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";
885            var result = synchro.query(q, '[TGT]');
886            if (result) {
887                result.forEach( function(data) {
888                    var divisionFlow = {
889                        flow_id: data.flow_id
890                    };
891                    VariableFlow.push(divisionFlow);
892                });
893            }
894        });
895    }
896    
897    //create flow 
898    var FlowName = "FL-ORG-"+organigram_Id;
899    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]');
900
901    //get inserted flow
902    q = "select max(id) id from jojoflow_db.flow where name = '"+FlowName+"' and state = 1 and company_id = "+company_Id;
903    result = synchro.query(q,'[TGT]')
904    var insertedFlow = result[0].id;
905
906    //save flow on variable
907    VariableFlow.push({flow_id:insertedFlow});
908
909    //create flow_member based on head
910    q = "call proc_get_member_by_supervisor("+insertedFlow+",'"+organigram_Head+"')";
911    synchro.query(q,'[TGT]');
912
913    //check organigram_flow
914    q = "select count(1) c from jojoflow_db.flow where id in (select max(flow_id) from jojoflow_db.organigram_flow where organigram_id = "+organigram_Id+" and state = 1 ) and state =1";
915    result = synchro.query(q, '[TGT]');
916
917    if(result[0].c > 0){
918        //update organigram flow
919        synchro.save('jojoflow_db.organigram_flow:organigram_id,state',[{organigram_id: organigram_Id, flow_id: insertedFlow, state:1, updated_by: user_Company_Id,updated_date: new Date()}],"[TGT]");
920    }else{
921        //create if not exist
922        synchro.save('jojoflow_db.organigram_flow', [{organigram_id: organigram_Id, flow_id:insertedFlow, state:1, created_date : new Date(), created_by: user_Company_Id}] ,'[TGT]')
923    }
924
925    var RuleName = "RL-ORG-"+organigram_Id;
926    var OldRule;
927    //check rule
928    q = "select max(id) id,count(1) c from jojoflow_db.rule where name = '"+RuleName+"' and state=1";
929    result = synchro.query(q, '[TGT]');
930    if(result[0].c > 0){
931        //get rule
932        OldRule = result[0].id;
933    }else{
934        //create rule
935        synchro.save('jojoflow_db.rule', [{name: RuleName, company_id: company_Id, state: 1, created_date: new Date(), created_by: user_Company_Id}] , '[TGT]');
936        //get rule
937        q = "select max(id) id from jojoflow_db.rule where name = 'RL-ORG-"+organigram_Id+"' and state=1";
938        result = synchro.query(q,'[TGT]');
939        OldRule = result[0].id;
940
941    }
942
943    //delete rule_flow based on rule organigram
944    q = "update jojoflow_db.rule_flow set state = 4, updated_date = NOW() where rule_id ="+OldRule+" and state = 1";
945    exec = synchro.query(q,'[TGT]');
946
947    objInsVariableFlow = [];
948    VariableFlow.forEach( function(data, index) {
949        //add order and rule
950        objInsVariableFlow.push({rule_id:OldRule,flow_id:data.flow_id, '`order`':index+1,state:1,created_date:new Date()});
951    });
952
953    //create rule_flow from variable
954    synchro.save('jojoflow_db.rule_flow',objInsVariableFlow,'[TGT]')
955
956    //process child of supervisor
957    q = "select distinct id from jojoflow_db.organigram where organigram_head_id = "+organigram_Id+" and company_id = "+company_Id+" and state =1";
958    var childs = synchro.query(q,'[TGT]');
959
960    if (childs.length > 0) {
961        childs.forEach( function(child) {
962            processChildPosition(child.id,organigram_Id,company_Id,user_Company_Id);
963        });
964    }
965}
966
967function processImportPos(fileName, insertedBatchId){
968    synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][1]"+fileName+" Start Importing"}], '[TGT]');
969    var q = '';
970    var VariableFlow = [];
971    var objInsVariableFlow = [];
972    try {
973        // check file exist with select data
974        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 + "\"";
975        var dataCSV = synchro.query(q, "cimbimports");
976
977            // get uploader
978        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 + "')";
979        var uploader = synchro.query(q);
980        var u_user_company = uploader[0]['user_company_id'];
981        var u_company_id = uploader[0]['company_id'];
982
983        // insert csv data from /synchro/cimb-imports/{filename}.csv to table import_mapping_position
984        var rowId = 0;
985        var objCSV = [];
986        var objCSVdivisions = [];
987        dataCSV.forEach(function(o){
988            rowId++;
989            var obj = {
990                batch_id : insertedBatchId,
991                row_id: rowId,
992                position_id: o['position_id'],
993                position_code: o['position_code'],
994                position: o['position'],
995                email: o['email'],
996                division: o['divisions'],
997                layer_code: o['layer_code'],
998                supervisor_code: o['supervisor_code'],
999                process_status: 'processing',
1000                description: '',
1001                company_id: u_company_id
1002            };
1003            objCSV.push(obj);
1004        });
1005        synchro.save('import_position:batch_id,row_id', objCSV, "[TGT]");
1006        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][2]"+fileName+" Done Importing"}], '[TGT]');
1007
1008        // update batch data_total
1009        q = "update import_batch set data_total = " + dataCSV.length + " where batch_id = '" + insertedBatchId + "'";
1010        synchro.query(q, "[TGT]");
1011
1012        /******** START VALIDATE BATCH ********/
1013
1014        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][3]"+fileName+" Start Validate Batch"}], '[TGT]');
1015
1016        //position_id is null :all
1017        q = "update import_position set process_status = 'error', description = 'position_id cannot be empty' where batch_id = '" + insertedBatchId + "' and position_id is null";
1018        synchro.query(q, "[TGT]");
1019
1020        //validate position  :update
1021        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 )";
1022        synchro.query(q, "[TGT]");
1023
1024        //position_code is null :all
1025        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";
1026        synchro.query(q, "[TGT]");
1027
1028        //position is null :all
1029        q = "update import_position set process_status = 'error', description = concat_ws('|', description, 'position is null') where batch_id = '" + insertedBatchId + "' and position is null";
1030        synchro.query(q, "[TGT]");
1031
1032        //position length <=3 :all
1033        q = "update import_position set process_status = 'error', description = concat_ws('|', description, 'position must > 3 chars') where batch_id = '" + insertedBatchId + "' and length(position) < 4";
1034        synchro.query(q, "[TGT]");
1035
1036        //position code != supervisor :all
1037        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";
1038        synchro.query(q, "[TGT]");
1039
1040        //position code != supervisor :all
1041        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+")";
1042        synchro.query(q, "[TGT]");
1043
1044        //supervisor_code :all
1045        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 + "'";
1046        synchro.query(q, "[TGT]");
1047
1048        /*** END VALIDATE ***/
1049
1050        // UPDATING & VERIFY NEEDED DATA  :all
1051
1052        // update organigram_head_id :all
1053        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 + "'";
1054        synchro.query(q, "[TGT]")
1055
1056        // update layer_id :all
1057        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";
1058        synchro.query(q, "[TGT]")
1059
1060        //validate layer
1061        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";
1062        synchro.query(q, "[TGT]");
1063
1064        // validate email
1065        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)";
1066        synchro.query(q, "[TGT]")
1067
1068        // update user_company_id by email valid
1069        q = "update import_position set user_company_id = (select max(id) from jojonomic_db.company_user cu where cu.email = import_position.email ) where batch_id = '" + insertedBatchId + "' and email_valid = 'yes'";
1070        synchro.query(q, "[TGT]")
1071
1072        /** START VALIDATING DIVISION **/
1073        q = "select row_id,division from import_position where batch_id = '"+insertedBatchId+"' and division is not null";
1074        var result = synchro.query(q, '[TGT]');
1075
1076        if (result) {
1077            result.forEach(function(data) {
1078                var divisions = data.division.split(';')
1079                var i = 0;
1080                while (i < divisions.length) {
1081                    var check_Division = divisions[i].trim();
1082                    q = "select count(1) c from jojoflow_db.division where code = '"+check_Division+"' and state = 1 and company_id = "+ u_company_id;
1083                    result = synchro.query(q, "[TGT]");
1084                    if (result[0].c == 0) {
1085                        q = "   update import_position set  \
1086                                process_status = 'error',\
1087                                description = concat_ws('|',description, 'division "+check_Division+" not exists')\
1088                                where batch_id = '" + insertedBatchId + "'\
1089                                and row_id = "+ data.row_id;
1090                        result = synchro.query(q, "[TGT]");
1091                    }
1092                    i++;
1093                }
1094            });
1095        }
1096
1097        /** END VALIDATING DIVISION **/
1098
1099        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][4]"+fileName+" Done Validate"}], '[TGT]');
1100
1101        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][5]"+fileName+" Start Inserting"}], '[TGT]');
1102
1103        /** START INSERTING DATA **/
1104        q = "select\
1105                row_id,\
1106                position_id id,\
1107                position_code code,\
1108                position name,\
1109                email,\
1110                email_valid,\
1111                division,\
1112                layer_code,\
1113                layer_id,\
1114                supervisor_code,\
1115                organigram_head_id,\
1116                company_id,\
1117                user_company_id\
1118            from import_position\
1119            where batch_id = '"+insertedBatchId+"'\
1120            and position_id = 0\
1121            and process_status = 'processing'";
1122        var DataInsert = synchro.query(q,'[TGT]');
1123
1124        DataInsert.forEach(function(data) {
1125            //check position code 
1126            q = "   select count(1) c from jojoflow_db.organigram o where o.code = '" + data.code + "'\
1127                            and o.state = 1 and o.company_id = "+data.company_id;
1128            resultq = synchro.query(q, "[TGT]");
1129
1130            if(resultq[0].c > 0){
1131                q = "   update import_position set process_status = 'error', \
1132                        description = concat_ws('|',description, 'Code already used for Job Position')\
1133                        where batch_id = '" + insertedBatchId + "'\
1134                        and row_id = "+ data.row_id;
1135                resultq = synchro.query(q, "[TGT]");
1136            }else{
1137                var obj = [{
1138                        company_id : data.company_id,
1139                        name : data.name,
1140                        code : data.code,
1141                        organigram_head_id : data.organigram_head_id,
1142                        description : data.description,
1143                        state : 1,
1144                        created_date : new Date(),
1145                        updated_date : new Date(),
1146                        created_by : u_user_company,
1147                        updated_by : u_user_company
1148                }];
1149                synchro.save('jojoflow_db.organigram',obj,"[TGT]");
1150
1151                q = "select max(id) id from jojoflow_db.organigram where code = '"+data.code+"' and state = 1 and company_id = "+data.company_id;
1152                result = synchro.query(q,'[TGT]')
1153                var insertedOrganigram = result[0].id;
1154                
1155                if (data.email_valid) {
1156                    //create organigram_member
1157                    synchro.save('jojoflow_db.organigram_member', [{organigram_id: insertedOrganigram, user_company_id: data.user_company_id,state:1, created_date : new Date(), created_by : u_user_company}] ,'[TGT]')
1158                }
1159
1160                //division if exists
1161                if (data.division) {
1162                    divisions = data.division.split(';');
1163                    //get division flow  save to variable, --but not used so i skip
1164                    q = "select id as flow_id from jojoflow_db.flow where id = (select flow_id from jojoflow_db.division_flow where division_id = (select max(id) from jojoflow_db.division where code = '"+divisions[0].trim()+"' and state = 1 and company_id = "+data.company_id+") and state = 1 order by id desc limit 1) and state = 1";
1165                    var result = synchro.query(q, '[TGT]');
1166                    VariableFlow = [];
1167                    if (result) {
1168                        result.forEach( function(data) {
1169                            var divisionFlow = {
1170                                flow_id: data.flow_id
1171                            };
1172                            VariableFlow.push(divisionFlow);
1173                        });
1174                    }
1175                    if (divisions.length > 0) {
1176                        i = 0;
1177                        while (i < divisions.length) {
1178                            var check_Division = divisions[i].trim();
1179                            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+")";
1180                            result = synchro.query(q,"[TGT]");
1181
1182                            //create organigram_division
1183                            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]')
1184                            i++;
1185                        }
1186                    }
1187                }
1188
1189                //create layer_organigram
1190                if (data.layer_id) {
1191                    obj = [{
1192                        company_id: data.company_id,
1193                        layer_id: data.layer_id,
1194                        organigram_id: insertedOrganigram,
1195                        state: 1,
1196                        created_date: new Date(),
1197                        created_by : u_user_company
1198                    }];
1199                    synchro.save('jojoflow_db.layer_organigram', obj ,'[TGT]');
1200                }
1201
1202                //ADDED MORE CONDITION
1203
1204                if (data.email_valid && !data.division && data.organigram_head_id) {
1205                    //create flow 
1206                    var FlowName = "FL-ORG-"+insertedOrganigram;
1207                    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 : u_user_company}] ,'[TGT]');
1208
1209                    //get inserted flow
1210                    q = "select max(id) id from jojoflow_db.flow where name = '"+FlowName+"' and state = 1 and company_id = "+data.company_id;
1211                    result = synchro.query(q,'[TGT]')
1212                    var insertedFlow = result[0].id;
1213
1214                    //create flow_member
1215                    q = "call proc_get_member_by_supervisor("+insertedFlow+",'"+data.organigram_head_id+"')";
1216                    synchro.query(q,'[TGT]');
1217
1218                    //create organigram_flow
1219                    synchro.save('jojoflow_db.organigram_flow', [{organigram_id: insertedOrganigram, flow_id: insertedFlow, state: 1, created_by : u_user_company, created_date:new Date()}] ,'[TGT]');
1220
1221                    //create rule
1222                    var RuleName = "RL-ORG-"+insertedOrganigram;
1223                    synchro.save('jojoflow_db.rule', [{name: RuleName, company_id: data.company_id, state: 1, created_date: new Date(), created_by : u_user_company}] , '[TGT]');
1224
1225                    //get InsertedRule
1226                    q = "select max(id) id from jojoflow_db.rule where name = '"+RuleName+"' and company_id = "+data.company_id+" and state = 1";
1227                    result = synchro.query(q,'[TGT]');
1228                    var insertedRule = result[0].id;
1229
1230                    synchro.save('jojoflow_db.rule_flow',[{rule_id:insertedRule,flow_id:insertedFlow, '`order`':1,state:1,created_date:new Date()}],'[TGT]');
1231
1232                    //create organigram_rule
1233                    synchro.save('jojoflow_db.organigram_rule', [{organigram_id: insertedOrganigram, rule_id: insertedRule, state: 1, created_by : u_user_company, created_date: new Date()}] ,'[TGT]');
1234
1235                }else if (data.email_valid && data.division && !data.organigram_head_id) {
1236                    //create rule
1237                    var RuleName = "RL-ORG-"+insertedOrganigram;
1238                    synchro.save('jojoflow_db.rule', [{name: RuleName, company_id: data.company_id, state: 1, created_date: new Date(), created_by : u_user_company}] , '[TGT]');
1239
1240                    //get InsertedRule
1241                    q = "select max(id) id from jojoflow_db.rule where name = '"+RuleName+"' and company_id = "+data.company_id+" and state = 1";
1242                    result = synchro.query(q,'[TGT]');
1243                    var insertedRule = result[0].id;
1244
1245                    if(VariableFlow.length > 0){
1246                        synchro.save('jojoflow_db.rule_flow',[{rule_id:insertedRule,flow_id:VariableFlow[0].flow_id, '`order`':1,state:1,created_date:new Date()}],'[TGT]');
1247                    }
1248
1249                    //create organigram_rule
1250                    synchro.save('jojoflow_db.organigram_rule', [{organigram_id: insertedOrganigram, rule_id: insertedRule, state: 1, created_by : u_user_company, created_date: new Date()}] ,'[TGT]');
1251                }else if (data.email_valid && data.division && data.organigram_head_id) {
1252                    //create flow 
1253                    var FlowName = "FL-ORG-"+insertedOrganigram;
1254                    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 : u_user_company}] ,'[TGT]');
1255
1256                    //get inserted flow
1257                    q = "select max(id) id from jojoflow_db.flow where name = '"+FlowName+"' and state = 1 and company_id = "+data.company_id;
1258                    result = synchro.query(q,'[TGT]')
1259                    var insertedFlow = result[0].id;
1260
1261                    //save flow on variable
1262                    VariableFlow.push({flow_id:insertedFlow});
1263
1264                    //create flow_member
1265                    q = "call proc_get_member_by_supervisor("+insertedFlow+",'"+data.organigram_head_id+"')";
1266                    synchro.query(q,'[TGT]');
1267
1268                    //create organigram_flow
1269                    synchro.save('jojoflow_db.organigram_flow', [{organigram_id: insertedOrganigram, flow_id: insertedFlow, state: 1, created_by : u_user_company, created_date:new Date()}] ,'[TGT]');
1270
1271                    //create rule
1272                    var RuleName = "RL-ORG-"+insertedOrganigram;
1273                    synchro.save('jojoflow_db.rule', [{name: RuleName, company_id: data.company_id, state: 1, created_date: new Date(), created_by : u_user_company}] , '[TGT]');
1274
1275                    //get InsertedRule
1276                    q = "select max(id) id from jojoflow_db.rule where name = '"+RuleName+"' and company_id = "+data.company_id+" and state = 1";
1277                    result = synchro.query(q,'[TGT]');
1278                    var insertedRule = result[0].id;
1279
1280                    objInsVariableFlow = [];
1281                    VariableFlow.forEach( function(data, index) {
1282                        //add order and rule
1283                        objInsVariableFlow.push({rule_id:insertedRule,flow_id:data.flow_id, '`order`':index+1,state:1,created_date:new Date()});
1284                    });
1285
1286                    synchro.save('jojoflow_db.rule_flow',objInsVariableFlow,'[TGT]');
1287
1288                    //create organigram_rule
1289                    synchro.save('jojoflow_db.organigram_rule', [{organigram_id: insertedOrganigram, rule_id: insertedRule, state: 1, created_by : u_user_company, created_date: new Date()}] ,'[TGT]');
1290                }
1291            }
1292        });
1293
1294        /** DONE INSERTING DATA **/
1295
1296        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][6]"+fileName+" Done Inserting"}], '[TGT]');
1297        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][7]"+fileName+" Start Updating"}], '[TGT]');
1298
1299        /** START UPDATING DATA **/
1300        q = "select\
1301                row_id,\
1302                position_id id,\
1303                position_code code,\
1304                position name,\
1305                email,\
1306                email_valid,\
1307                division,\
1308                layer_code,\
1309                layer_id,\
1310                supervisor_code,\
1311                organigram_head_id,\
1312                company_id,\
1313                user_company_id\
1314            from import_position\
1315            where batch_id = '"+insertedBatchId+"'\
1316            and position_id > 0\
1317            and process_status = 'processing'";
1318        var DataUpdate = synchro.query(q,'[TGT]');
1319
1320        DataUpdate.forEach( function(data) {
1321            //check position code 
1322            q = "   select count(1) c from jojoflow_db.organigram o where o.code = '" + data.code + "'\
1323                    and id != "+data.id+" and o.state = 1 and o.company_id = "+data.company_id;
1324            result = synchro.query(q, "[TGT]");
1325
1326            if(result[0].c > 0){
1327                q = "   update import_position set process_status = 'error', \
1328                        description = concat_ws('|',description, 'Code already used for Job Position')\
1329                        where batch_id = '" + insertedBatchId + "'\
1330                        and row_id = "+ data.row_id;
1331                result = synchro.query(q, "[TGT]");
1332            }else{
1333                var obj = [{
1334                        id: data.id,
1335                        company_id : data.company_id,
1336                        name : data.name,
1337                        code : data.code,
1338                        organigram_head_id : data.organigram_head_id,
1339                        description : data.description,
1340                        state : 1,
1341                        updated_date : new Date(),
1342                        updated_by : u_user_company
1343                }];
1344                synchro.save('jojoflow_db.organigram:id',obj,"[TGT]");
1345
1346                //soft delete organigram_member
1347                q = "update jojoflow_db.organigram_member set state=4,updated_by = "+u_user_company+" where organigram_id = "+data.id;
1348                synchro.query(q,'[TGT]');
1349
1350                if (data.email_valid) {
1351                    //create organigram_member
1352                    synchro.save('jojoflow_db.organigram_member', [{organigram_id: data.id, user_company_id: data.user_company_id, state:1, created_date : new Date(), created_by : u_user_company}] ,'[TGT]')
1353                }
1354
1355                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]');
1356
1357                if (data.division) {
1358                    divisions = data.division.split(';');
1359                    if (divisions.length > 0) {
1360                        //get division flow  save to variable, --but not used so i skip
1361                        q = "select id as flow_id from jojoflow_db.flow where id = (select flow_id from jojoflow_db.division_flow where division_id = (select max(id) from jojoflow_db.division where code = '"+divisions[0].trim()+"' and state = 1 and company_id = "+data.company_id+") and state = 1 order by id desc limit 1) and state = 1";
1362                        var result = synchro.query(q, '[TGT]');
1363                        VariableFlow = [];
1364                        if (result) {
1365                            result.forEach( function(data) {
1366                                var divisionFlow = {
1367                                    flow_id: data.flow_id
1368                                };
1369                                VariableFlow.push(divisionFlow);
1370                            });
1371                        }
1372                        i = 0;
1373                        while (i < divisions.length) {
1374                            var check_Division = divisions[i].trim();
1375                            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+")";
1376                            result = synchro.query(q,"[TGT]");
1377
1378                            //create organigram_division
1379                            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]')
1380                            i++;
1381                        }
1382                    }
1383                }
1384
1385                // delete layer_organigram
1386                synchro.query("update jojoflow_db.layer_organigram set state = 4,updated_by = "+u_user_company+", updated_date = NOW()  where organigram_id = "+data.id+" and company_id = "+data.company_id+" and state = 1",'[TGT]');
1387
1388                //create layer_organigram
1389                if (data.layer_id) {
1390                    obj = [{
1391                        company_id: data.company_id,
1392                        layer_id: data.layer_id,
1393                        organigram_id: data.id,
1394                        state: 1,
1395                        created_date: new Date(),
1396                        created_by : u_user_company
1397                    }];
1398                    synchro.save('jojoflow_db.layer_organigram', obj ,'[TGT]');
1399                }
1400
1401
1402                if (data.email_valid && !data.division && data.organigram_head_id) {
1403                    //create flow 
1404                    var FlowName = "FL-ORG-"+data.id;
1405                    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 : u_user_company}] ,'[TGT]');
1406
1407                    //get inserted flow
1408                    q = "select max(id) id from jojoflow_db.flow where name = '"+FlowName+"' and state = 1 and company_id = "+data.company_id;
1409                    result = synchro.query(q,'[TGT]')
1410                    var insertedFlow = result[0].id;
1411
1412                    //create flow_member based on head
1413                    q = "call proc_get_member_by_supervisor("+insertedFlow+",'"+data.organigram_head_id+"')";
1414                    synchro.query(q,'[TGT]');
1415
1416                    //check organigram_flow
1417                    q = "select count(1) c from jojoflow_db.flow where id in (select max(flow_id) from jojoflow_db.organigram_flow where organigram_id = "+data.id+" and state = 1 ) and state =1";
1418
1419                    result = synchro.query(q, '[TGT]');
1420
1421                    if(result[0].c > 0){
1422                        //update organigram flow
1423                        synchro.save('jojoflow_db.organigram_flow:organigram_id,state',[{organigram_id: data.id, flow_id: insertedFlow, state:1, updated_by: u_user_company,updated_date: new Date()}],"[TGT]");
1424                    }else{
1425                        //create if not exist
1426                        synchro.save('jojoflow_db.organigram_flow', [{organigram_id: data.id, flow_id:insertedFlow, state:1, created_date : new Date(), created_by : u_user_company}] ,'[TGT]')
1427                    }
1428
1429                    var RuleName = "RL-ORG-"+data.id;
1430                    var OldRule;
1431                    //check rule
1432                    q = "select max(id) id,count(1) c from jojoflow_db.rule where name = '"+RuleName+"' and state=1";
1433                    result = synchro.query(q, '[TGT]');
1434                    if(result[0].c > 0){
1435                        //get rule
1436                        OldRule = result[0].id;
1437                    }else{
1438                        //create rule
1439                        synchro.save('jojoflow_db.rule', [{name: RuleName, company_id: data.company_id, state: 1, created_date: new Date(), created_by : u_user_company}] , '[TGT]');
1440                        //get rule
1441                        q = "select max(id) id from jojoflow_db.rule where name = 'RL-ORG-"+data.id+"' and state=1";
1442                        result = synchro.query(q,'[TGT]');
1443                        OldRule = result[0].id;
1444
1445                    }
1446
1447                    q = "update jojoflow_db.rule_flow set state = 4,updated_date = now() where rule_id ="+OldRule+" and state=1";
1448                    synchro.query(q,'[TGT]');
1449
1450                    //insert new rule_flow
1451                    synchro.save('jojoflow_db.rule_flow',[{rule_id: OldRule, flow_id: insertedFlow, '`order`':1, state:1, created_date: new Date()}],'[TGT]');
1452
1453                    //soft delete all organigram_rule where id not latest
1454                    q = "update jojoflow_db.organigram_rule set state=4,updated_by = "+u_user_company+",updated_date = now() where rule_id <> "+OldRule;
1455
1456                    //checkk organigram_rule
1457                    q = "select count(1) c from jojoflow_db.organigram_rule where rule_id ="+OldRule+" and state = 1";
1458                    result = synchro.query(q, '[TGT]');
1459
1460                    //insert organigram_rule if no have 
1461                    if(result[0].c <= 0){
1462                        synchro.save('jojoflow_db.organigram_rule',[{organigram_id: data.id,rule_id: OldRule,state:1,created_by:u_user_company,created_date: new Date()}],'[TGT]');
1463                    }
1464
1465                    //process child of supervisor
1466                    q = "select distinct id from jojoflow_db.organigram where organigram_head_id = "+data.id+" and company_id = "+data.company_id+" and state =1";
1467                    var childs = synchro.query(q,'[TGT]');
1468
1469                    if (childs.length > 0) {
1470                        childs.forEach( function(child) {
1471                            processChildPosition(child.id,data.id,data.company_id,u_user_company);
1472                        });
1473                    }
1474                }else if (data.email_valid && data.division && !data.organigram_head_id) {
1475                    //soft delete organigram_flow
1476                    q ="update jojoflow_db.organigram_flow set state = 4, updated_by = "+u_user_company+",updated_date = now() where organigram_id = "+data.id;
1477                    synchro.query(q,'[TGT]');
1478
1479                    //soft delete rule_flow
1480                    q = "update jojoflow_db.rule_flow set state = 4,updated_date = now() where rule_id in (select id from jojoflow_db.rule where name = 'RL-ORG-"+data.id+"') and state=1";
1481                    synchro.query(q,'[TGT]');
1482
1483                    //check if have flow from division_flow
1484                    var OldRule;
1485                    if(VariableFlow.length <= 0){
1486                        statusVariableFlow = false;
1487                        //update organigram_rule
1488                        q = "update jojoflow_db.organigram_rule set state = 4,updated_by = "+u_user_company+",updated_date = now() where organigram_id = "+data.id;
1489                        synchro.query(q,'[TGT]');
1490                    }else{
1491                        statusVariableFlow = true;
1492                        var RuleName = "RL-ORG-"+data.id;
1493                        //check rule
1494                        q = "select max(id) id,count(1) c from jojoflow_db.rule where name = '"+RuleName+"' and state=1";
1495                        result = synchro.query(q, '[TGT]');
1496                        if(result[0].c > 0){
1497                            //get rule
1498                            OldRule = result[0].id;
1499                        }else{
1500                            //create rule
1501                            synchro.save('jojoflow_db.rule', [{name: RuleName, company_id: data.company_id, state: 1, created_date: new Date(), created_by : u_user_company}] , '[TGT]');
1502                            //get rule
1503                            q = "select max(id) id from jojoflow_db.rule where name = 'RL-ORG-"+data.id+"' and state=1";
1504                            result = synchro.query(q,'[TGT]');
1505                            OldRule = result[0].id;
1506                        }
1507                        q = "update jojoflow_db.rule_flow set state = 4,updated_date = now() where rule_id ="+OldRule+" and state=1";
1508                        synchro.query(q,'[TGT]');
1509
1510                        synchro.save('jojoflow_db.rule_flow',[{rule_id:OldRule,flow_id:VariableFlow[0].flow_id, '`order`':1,state:1,created_date:new Date()}]);
1511
1512                        q = "update jojoflow_db.organigram_rule set rule_id = "+OldRule+",updated_by = "+u_user_company+",updated_date = now() where id = (select id from(select max(id) id from jojoflow_db.organigram_rule where organigram_id = "+data.id+" and state=1)a)";
1513                        synchro.query(q,'[TGT]');
1514                    }
1515                    
1516                    //process child of supervisor
1517                    q = "select distinct id from jojoflow_db.organigram where organigram_head_id = "+data.id+" and company_id = "+data.company_id+" and state =1";
1518                    var childs = synchro.query(q,'[TGT]');
1519
1520                    if (childs.length > 0) {
1521                        childs.forEach( function(child) {
1522                            processChildPosition(child.id,data.id,data.company_id,u_user_company);
1523                        });
1524                    }
1525                }else if (data.email_valid && data.division && data.organigram_head_id) {
1526                    var FlowName = "FL-ORG-"+data.id;
1527                    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 : u_user_company}] ,'[TGT]');
1528
1529                    //get inserted flow
1530                    q = "select max(id) id from jojoflow_db.flow where name = '"+FlowName+"' and state = 1 and company_id = "+data.company_id;
1531                    result = synchro.query(q,'[TGT]')
1532                    var insertedFlow = result[0].id;
1533
1534                    //save flow on variable
1535                    VariableFlow.push({flow_id:insertedFlow});
1536
1537                    //create flow_member based on head
1538                    q = "call proc_get_member_by_supervisor("+insertedFlow+",'"+data.organigram_head_id+"')";
1539                    synchro.query(q,'[TGT]');
1540
1541                    //soft delete organigram_flow
1542                    // q ="update jojoflow_db.organigram_flow set state = 4, updated_by = "+u_user_company+",updated_date = now() where organigram_id = "+data.id;
1543                    // synchro.query(q,'[TGT]');
1544
1545                    //check organigram_flow
1546                    q = "select count(1) c from jojoflow_db.flow where id in (select max(flow_id) from jojoflow_db.organigram_flow where organigram_id = "+data.id+" and state = 1 ) and state=1";
1547
1548                    result = synchro.query(q, '[TGT]');
1549
1550                    if(result[0].c > 0){
1551                        //update organigram flow
1552                        synchro.save('jojoflow_db.organigram_flow:organigram_id,state',[{organigram_id: data.id, flow_id: insertedFlow, state:1, updated_by: u_user_company,updated_date: new Date()}],"[TGT]");
1553                    }else{
1554                        //create if not exist
1555                        synchro.save('jojoflow_db.organigram_flow', [{organigram_id: data.id, flow_id:insertedFlow, state:1, created_date : new Date(), created_by : u_user_company}] ,'[TGT]')
1556                    }
1557
1558                    var RuleName = "RL-ORG-"+data.id;
1559                    var OldRule;
1560                    //check rule
1561                    q = "select max(id) id,count(1) c from jojoflow_db.rule where name = '"+RuleName+"' and state=1";
1562                    result = synchro.query(q, '[TGT]');
1563                    if(result[0].c > 0){
1564                        //get rule
1565                        OldRule = result[0].id;
1566                    }else{
1567                        //create rule
1568                        synchro.save('jojoflow_db.rule', [{name: RuleName, company_id: data.company_id, state: 1, created_date: new Date(), created_by : u_user_company}] , '[TGT]');
1569                        //get rule
1570                        q = "select max(id) id from jojoflow_db.rule where name = 'RL-ORG-"+data.id+"' and state=1";
1571                        result = synchro.query(q,'[TGT]');
1572                        OldRule = result[0].id;
1573
1574                    }
1575                    q = "update jojoflow_db.rule_flow set state = 4,updated_date = now() where rule_id ="+OldRule+" and state=1";
1576                    synchro.query(q,'[TGT]');
1577
1578                    objInsVariableFlow = [];
1579                    VariableFlow.forEach( function(data, index) {
1580                        //add order and rule
1581                        objInsVariableFlow.push({rule_id:OldRule,flow_id:data.flow_id, '`order`':index+1,state:1,created_date:new Date()});
1582                    });
1583
1584                    //create rule_flow from variable
1585                    synchro.save('jojoflow_db.rule_flow',objInsVariableFlow,'[TGT]')
1586
1587                    //checkk organigram_rule
1588                    q = "select count(1) c from jojoflow_db.organigram_rule where rule_id ="+OldRule+" and state = 1";
1589                    result = synchro.query(q, '[TGT]');
1590
1591                    //insert organigram_rule if no have 
1592                    if(result[0].c <= 0){
1593                        synchro.save('jojoflow_db.organigram_rule',[{organigram_id: data.id,rule_id: OldRule,state:1,created_by:u_user_company,created_date: new Date()}],'[TGT]');
1594                    }
1595
1596                    //process child of supervisor
1597                    q = "select distinct id from jojoflow_db.organigram where organigram_head_id = "+data.id+" and company_id = "+data.company_id+" and state =1";
1598                    var childs = synchro.query(q,'[TGT]');
1599
1600                    if (childs.length > 0) {
1601                        childs.forEach( function(child) {
1602                            processChildPosition(child.id,data.id,data.company_id,u_user_company);
1603                        });
1604                    }
1605                }else if (data.email_valid && !data.division && !data.organigram_head_id) {
1606                    //soft delete organigram_flow
1607                    q ="update jojoflow_db.organigram_flow set state = 4, updated_by = "+u_user_company+",updated_date = now() where organigram_id = "+data.id;
1608                    synchro.query(q,'[TGT]');
1609
1610                    //soft delete rule_flow
1611                    q = "update jojoflow_db.rule_flow set state = 4,updated_date = now() where rule_id in (select id from jojoflow_db.rule where name = 'RL-ORG-"+data.id+"') and state=1";
1612                    synchro.query(q,'[TGT]');
1613
1614                    //softdelete organigram_rule
1615                    q = "update jojoflow_db.organigram_rule set state=4,updated_by = "+u_user_company+",updated_date = now() where organigram_id ="+data.id;
1616                    synchro.query(q,'[TGT]');
1617
1618                    //process child of supervisor
1619                    q = "select distinct id from jojoflow_db.organigram where organigram_head_id = "+data.id+" and company_id = "+data.company_id+" and state =1";
1620                    var childs = synchro.query(q,'[TGT]');
1621
1622                    if (childs.length > 0) {
1623                        childs.forEach( function(child) {
1624                            processChildPosition(child.id,data.id,data.company_id,u_user_company);
1625                        });
1626                    }
1627                }
1628            }
1629        });
1630        /** DONE UPDATING DATA **/
1631        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][7]"+fileName+" Done Updating"}], '[TGT]');
1632
1633        // update status 'processing' to 'success'
1634        q = "update import_position set process_status = 'success' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
1635        synchro.query(q, "[TGT]")
1636        // update summary and status done to import_batch
1637        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 + "'";
1638        synchro.query(q, "[TGT]");
1639        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][8] finish"}], '[TGT]');
1640    }catch (err){
1641        var errorMessage = JSON.stringify(err.message).split("'").join("")
1642        errorMessage = errorMessage.split('"').join('')
1643        errorMessage = errorMessage.replace("java.sql.SQLException: ", "")
1644        errorMessage = errorMessage.replace(/[^\w\s]/gi, '')
1645        if(errorMessage.toString().toLowerCase().indexOf("java.lang.reflect.invocationtargetexception") != -1) {
1646            errorMessage = "Invalid header format";
1647        } else if (errorMessage.toString().toLowerCase().indexOf("invalid column or variable") != -1) {
1648            errorMessage = "Invalid header format";
1649        }
1650        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_POSITION]["+insertedBatchId+"][99] " + fileName + " error:" + errorMessage}], '[TGT]');
1651        q = "update import_batch set batch_endate = current_timestamp, batch_status = 'failed', description = '" + errorMessage + "', data_total = (select count(1) from import_position where batch_id = '" + insertedBatchId + "'), 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 + "'";
1652        synchro.query(q, "[TGT]");
1653    }
1654}
1655
1656// import position END
1657
1658function upsertPos(request_Id,position_Id,position_Code,position,email,divisions,layer_Code,supervisor_Code,state,user_Company_Id){
1659    var batchName = 'upsert_position';
1660    var batchId = getBatchId(batchName, request_Id);
1661    var q = '';
1662    var allowedState = [1,4];   
1663    var insertedBatchId = '';
1664    var up_Company_ID;
1665    var up_Company_User;
1666    var user_company_id;
1667    var result;
1668    var layer_ID;
1669    var supervisor_ID = null;
1670    var VariableFlow = [];
1671
1672    if(email){
1673        email = email.trim();
1674    }
1675    if(layer_Code){
1676        layer_Code = layer_Code.trim();
1677    }
1678    if(supervisor_Code){
1679        supervisor_Code = supervisor_Code.trim();
1680    }
1681
1682    //val updater
1683    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]");
1684
1685    var exec = true;
1686    var invalidUser = false;
1687
1688    if (dataPostinger.length == 0) {
1689        invalidUser = true;
1690    }else{
1691        up_Company_ID = dataPostinger[0].company_id;
1692        up_Company_User = dataPostinger[0].company_user_id;
1693    }
1694
1695    var data = {
1696        id: position_Id,
1697        code: position_Code,
1698        name: position,
1699        email: email,
1700        divisions: divisions,
1701        layer_code: layer_Code,
1702        supervisor_code: supervisor_Code,
1703        state: parseInt(state),
1704        company_id: up_Company_ID
1705    };
1706
1707
1708    try {
1709        
1710        if (invalidUser){
1711            exec = false;
1712            throw 'invalid uploader';
1713        }
1714
1715        //validate state
1716        if (!(allowedState.indexOf(parseInt(data.state)) >= 0)) {
1717            exec = false;
1718            throw 'State is not allowed';
1719        }
1720
1721        //position length
1722        if (data.name.length < 4) {
1723            exec = false;
1724            throw 'position must be > 3 character'
1725        }
1726
1727        //position_id exists
1728        q = "select count(1) c from jojoflow_db.organigram where id = '" + data.id + "' and company_id = "+data.company_id+" and state = 1";
1729        var result = synchro.query(q, "[TGT]");
1730
1731        if (parseInt(result[0]['c']) < 1) {
1732            exec = false;
1733            throw 'Organigram not exists';
1734        };
1735
1736        if (parseInt(data.state) == 1) {
1737            //check position code 
1738            q = "   select count(1) c from jojoflow_db.organigram o where o.code = '" + data.code + "'\
1739                    and id != '"+data.id+"' and o.state = 1 and o.company_id = "+data.company_id;
1740            result = synchro.query(q, "[TGT]");
1741
1742            if(result[0].c > 0){
1743                exec = false;
1744                throw 'Code already used for Job Position';
1745            }
1746
1747            if (data.email) {
1748                q = "   select max(id) id,count(1) c from jojonomic_db.company_user u where u.email = '" + data.email + "'\
1749                        and u.state = 1 and u.company_id = "+data.company_id;
1750                result = synchro.query(q, "[TGT]");
1751
1752                if(result[0].c > 0){
1753                    user_company_id = result[0].id;
1754                }
1755            }
1756
1757            if (data.divisions) {
1758                var dataCheckDivisions = data.divisions.split(';')
1759                var i = 0;
1760                var stopDivisions = false;
1761                while (i < dataCheckDivisions.length && stopDivisions == false) {
1762                    var check_Division = dataCheckDivisions[i].trim();
1763                    q = "select count(1) c from jojoflow_db.division where code = '"+check_Division+"' and state = 1 and company_id = "+ data.company_id;
1764                    result = synchro.query(q, "[TGT]");
1765                    if (result[0].c < 1) {
1766                        exec = false;
1767                        stopDivisions = true;
1768                        throw ['division ', check_Division,' not exists'].join(' ');
1769                    }
1770                    i++;
1771                }
1772            }
1773
1774            if (data.layer_code) {
1775                q = "   select max(id) id,count(1) c from jojoflow_db.layer o where o.code = '" + data.layer_code + "'\
1776                        and o.state = 1 and o.company_id = "+data.company_id;
1777                result = synchro.query(q, "[TGT]");
1778
1779                if(result[0].c <= 0){
1780                    exec = false;
1781                    throw 'layer_code not exists';
1782                }else{
1783                    layer_ID = result[0].id;
1784                }
1785            }
1786
1787            if (data.supervisor_code) {
1788                q = "select max(id) id,count(1) c from jojoflow_db.organigram o where o.code = '" + data.supervisor_code + "'\
1789                     and o.state = 1 and o.company_id = "+data.company_id;
1790                result = synchro.query(q, "[TGT]");
1791
1792                if(result[0].c <= 0){
1793                    exec = false;
1794                    throw 'supervisor_code not exists';
1795                }else{
1796                    supervisor_ID = result[0].id;
1797                }
1798
1799                if (data.code == data.supervisor_code) {
1800                    exec = false;
1801                    throw 'Parent cant be on the same position';
1802                }
1803
1804                if (data.id == supervisor_ID) {
1805                    exec = false;
1806                    throw 'Parent cant be on the same position';
1807                }
1808            }
1809        }
1810
1811        if (exec) {
1812            if (isBatchIdExists(batchId) == false) {
1813                insertedBatchId = addBatch(batchId, batchName, request_Id, user_Company_Id);
1814                var upsertData = [{
1815                    batch_id : insertedBatchId,
1816                    position_id: data.id,
1817                    position_code: data.code,
1818                    position: data.name,
1819                    email: data.email,
1820                    division: data.divisions,
1821                    layer_code: data.layer_code,
1822                    supervisor_code: data.supervisor_code,
1823                    company_id: data.company_id,
1824                    user_company_id: user_company_id,
1825                    state: data.state,
1826                    process_status: 'processing',
1827                }];
1828                synchro.save('import_upsert_position:batch_id', upsertData, "[TGT]");
1829
1830                if(parseInt(data.state) == 1){
1831                    var obj = [{
1832                            id: data.id,
1833                            company_id : data.company_id,
1834                            name : data.name,
1835                            code : data.code,
1836                            organigram_head_id : supervisor_ID,
1837                            state : 1,
1838                            updated_date : new Date(),
1839                            updated_by : up_Company_User
1840                    }];
1841                    synchro.save('jojoflow_db.organigram:id',obj,"[TGT]");
1842
1843                    //soft delete organigram_member
1844                    q = "update jojoflow_db.organigram_member set state=4,updated_by = "+up_Company_User+" where organigram_id = "+data.id+" and state=1";
1845                    synchro.query(q,'[TGT]');
1846
1847                    if (user_company_id) {
1848                        //create organigram_member
1849                        synchro.save('jojoflow_db.organigram_member', [{organigram_id: data.id, user_company_id: user_company_id, state:1, created_date : new Date(), created_by : up_Company_User}] ,'[TGT]')
1850                    }
1851
1852                    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]');
1853
1854                    if (data.divisions) {
1855                        divisions = data.divisions.split(';');
1856                        if (divisions.length > 0) {
1857                            //get division flow  save to variable, --but not used so i skip
1858                            q = "select id as flow_id from jojoflow_db.flow where id = (select flow_id from jojoflow_db.division_flow where division_id = (select max(id) from jojoflow_db.division where code = '"+divisions[0].trim()+"' and state = 1 and company_id = "+data.company_id+") and state = 1 order by id desc limit 1) and state = 1";
1859                            var result = synchro.query(q, '[TGT]');
1860                            VariableFlow = [];
1861                            if (result) {
1862                                result.forEach( function(data) {
1863                                    var divisionFlow = {
1864                                        flow_id: data.flow_id
1865                                    };
1866                                    VariableFlow.push(divisionFlow);
1867                                });
1868                            }
1869                            i = 0;
1870                            while (i < divisions.length) {
1871                                var check_Division = divisions[i].trim();
1872                                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+")";
1873                                result = synchro.query(q,"[TGT]");
1874
1875                                //create organigram_division
1876                                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]')
1877                                i++;
1878                            }
1879                        }
1880                    }
1881
1882                    // delete layer_organigram
1883                    synchro.query("update jojoflow_db.layer_organigram set state = 4,updated_by = "+up_Company_User+", updated_date = NOW()  where organigram_id = "+data.id+" and company_id = "+data.company_id+" and state = 1",'[TGT]');
1884
1885                    //create layer_organigram
1886                    if (layer_ID) {
1887                        obj = [{
1888                            company_id: data.company_id,
1889                            layer_id: layer_ID,
1890                            organigram_id: data.id,
1891                            state: 1,
1892                            created_date: new Date(),
1893                            created_by : up_Company_User
1894                        }];
1895                        synchro.save('jojoflow_db.layer_organigram', obj ,'[TGT]');
1896                    }
1897
1898
1899                    if (user_company_id && !data.divisions && supervisor_ID) {
1900                        //create flow 
1901                        var FlowName = "FL-ORG-"+data.id;
1902                        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 : up_Company_User}] ,'[TGT]');
1903
1904                        //get inserted flow
1905                        q = "select max(id) id from jojoflow_db.flow where name = '"+FlowName+"' and state = 1 and company_id = "+data.company_id;
1906                        result = synchro.query(q,'[TGT]')
1907                        var insertedFlow = result[0].id;
1908
1909                        //create flow_member based on head
1910                        q = "call proc_get_member_by_supervisor("+insertedFlow+",'"+supervisor_ID+"')";
1911                        synchro.query(q,'[TGT]');
1912
1913                        //check organigram_flow
1914                        q = "select count(1) c from jojoflow_db.flow where id in (select max(flow_id) from jojoflow_db.organigram_flow where organigram_id = "+data.id+" and state = 1 ) and state =1";
1915
1916                        result = synchro.query(q, '[TGT]');
1917
1918                        if(result[0].c > 0){
1919                            //update organigram flow
1920                            synchro.save('jojoflow_db.organigram_flow:organigram_id,state',[{organigram_id: data.id, flow_id: insertedFlow, state:1, updated_by: up_Company_User,updated_date: new Date()}],"[TGT]");
1921                        }else{
1922                            //create if not exist
1923                            synchro.save('jojoflow_db.organigram_flow', [{organigram_id: data.id, flow_id:insertedFlow, state:1, created_date : new Date(), created_by : up_Company_User}] ,'[TGT]')
1924                        }
1925
1926                        var RuleName = "RL-ORG-"+data.id;
1927                        var OldRule;
1928                        //check rule
1929                        q = "select max(id) id,count(1) c from jojoflow_db.rule where name = '"+RuleName+"' and state=1";
1930                        result = synchro.query(q, '[TGT]');
1931                        if(result[0].c > 0){
1932                            //get rule
1933                            OldRule = result[0].id;
1934                        }else{
1935                            //create rule
1936                            synchro.save('jojoflow_db.rule', [{name: RuleName, company_id: data.company_id, state: 1, created_date: new Date(), created_by : up_Company_User}] , '[TGT]');
1937                            //get rule
1938                            q = "select max(id) id from jojoflow_db.rule where name = 'RL-ORG-"+data.id+"' and state=1";
1939                            result = synchro.query(q,'[TGT]');
1940                            OldRule = result[0].id;
1941
1942                        }
1943
1944                        q = "update jojoflow_db.rule_flow set state = 4,updated_date = now() where rule_id ="+OldRule+" and state=1";
1945                        synchro.query(q,'[TGT]');
1946
1947                        //insert new rule_flow
1948                        synchro.save('jojoflow_db.rule_flow',[{rule_id: OldRule, flow_id: insertedFlow, '`order`':1, state:1, created_date: new Date()}],'[TGT]');
1949
1950                        //soft delete all organigram_rule where id not latest
1951                        q = "update jojoflow_db.organigram_rule set state=4,updated_by = "+up_Company_User+",updated_date = now() where rule_id <> "+OldRule;
1952
1953                        //checkk organigram_rule
1954                        q = "select count(1) c from jojoflow_db.organigram_rule where rule_id ="+OldRule+" and state = 1";
1955                        result = synchro.query(q, '[TGT]');
1956
1957                        //insert organigram_rule if no have 
1958                        if(result[0].c <= 0){
1959                            synchro.save('jojoflow_db.organigram_rule',[{organigram_id: data.id,rule_id: OldRule,state:1,created_by:up_Company_User,created_date: new Date()}],'[TGT]');
1960                        }
1961
1962                        //process child of supervisor
1963                        q = "select distinct id from jojoflow_db.organigram where organigram_head_id = "+data.id+" and company_id = "+data.company_id+" and state =1";
1964                        var childs = synchro.query(q,'[TGT]');
1965
1966                        if (childs.length > 0) {
1967                            childs.forEach( function(child) {
1968                                processChildPosition(child.id,data.id,data.company_id,up_Company_User);
1969                            });
1970                        }
1971                    }else if (user_company_id && data.divisions && !supervisor_ID) {
1972                        //soft delete organigram_flow
1973                        q ="update jojoflow_db.organigram_flow set state = 4, updated_by = "+up_Company_User+",updated_date = now() where organigram_id = "+data.id;
1974                        synchro.query(q,'[TGT]');
1975
1976                        //soft delete rule_flow
1977                        q = "update jojoflow_db.rule_flow set state = 4,updated_date = now() where rule_id in (select id from jojoflow_db.rule where name = 'RL-ORG-"+data.id+"') and state=1";
1978                        synchro.query(q,'[TGT]');
1979
1980                        //check if have flow from division_flow
1981                        var OldRule;
1982                        if(VariableFlow.length <= 0){
1983                            statusVariableFlow = false;
1984                            //update organigram_rule
1985                            q = "update jojoflow_db.organigram_rule set state = 4,updated_by = "+up_Company_User+",updated_date = now() where organigram_id = "+data.id;
1986                            synchro.query(q,'[TGT]');
1987                        }else{
1988                            statusVariableFlow = true;
1989                            var RuleName = "RL-ORG-"+data.id;
1990                            //check rule
1991                            q = "select max(id) id,count(1) c from jojoflow_db.rule where name = '"+RuleName+"' and state=1";
1992                            result = synchro.query(q, '[TGT]');
1993                            if(result[0].c > 0){
1994                                //get rule
1995                                OldRule = result[0].id;
1996                            }else{
1997                                //create rule
1998                                synchro.save('jojoflow_db.rule', [{name: RuleName, company_id: data.company_id, state: 1, created_date: new Date(), created_by : up_Company_User}] , '[TGT]');
1999                                //get rule
2000                                q = "select max(id) id from jojoflow_db.rule where name = 'RL-ORG-"+data.id+"' and state=1";
2001                                result = synchro.query(q,'[TGT]');
2002                                OldRule = result[0].id;
2003                            }
2004                            q = "update jojoflow_db.rule_flow set state = 4,updated_date = now() where rule_id ="+OldRule+" and state=1";
2005                            synchro.query(q,'[TGT]');
2006
2007                            synchro.save('jojoflow_db.rule_flow',[{rule_id:OldRule,flow_id:VariableFlow[0].flow_id, '`order`':1,state:1,created_date:new Date()}]);
2008
2009                            q = "update jojoflow_db.organigram_rule set rule_id = "+OldRule+",updated_by = "+up_Company_User+",updated_date = now() where id = (select id from(select max(id) id from jojoflow_db.organigram_rule where organigram_id = "+data.id+" and state=1)a)";
2010                            synchro.query(q,'[TGT]');
2011                        }
2012                        
2013                        //process child of supervisor
2014                        q = "select distinct id from jojoflow_db.organigram where organigram_head_id = "+data.id+" and company_id = "+data.company_id+" and state =1";
2015                        var childs = synchro.query(q,'[TGT]');
2016
2017                        if (childs.length > 0) {
2018                            childs.forEach( function(child) {
2019                                processChildPosition(child.id,data.id,data.company_id,up_Company_User);
2020                            });
2021                        }
2022                    }else if (user_company_id && data.divisions && supervisor_ID) {
2023                        var FlowName = "FL-ORG-"+data.id;
2024                        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 : up_Company_User}] ,'[TGT]');
2025
2026                        //get inserted flow
2027                        q = "select max(id) id from jojoflow_db.flow where name = '"+FlowName+"' and state = 1 and company_id = "+data.company_id;
2028                        result = synchro.query(q,'[TGT]')
2029                        var insertedFlow = result[0].id;
2030
2031                        //save flow on variable
2032                        VariableFlow.push({flow_id:insertedFlow});
2033
2034                        //create flow_member based on head
2035                        q = "call proc_get_member_by_supervisor("+insertedFlow+",'"+supervisor_ID+"')";
2036                        synchro.query(q,'[TGT]');
2037
2038                        //soft delete organigram_flow
2039                        // q ="update jojoflow_db.organigram_flow set state = 4, updated_by = "+up_Company_User+",updated_date = now() where organigram_id = "+data.id;
2040                        // synchro.query(q,'[TGT]');
2041
2042                        //check organigram_flow
2043                        q = "select count(1) c from jojoflow_db.flow where id in (select max(flow_id) from jojoflow_db.organigram_flow where organigram_id = "+data.id+" and state = 1 ) and state=1";
2044
2045                        result = synchro.query(q, '[TGT]');
2046
2047                        if(result[0].c > 0){
2048                            //update organigram flow
2049                            synchro.save('jojoflow_db.organigram_flow:organigram_id,state',[{organigram_id: data.id, flow_id: insertedFlow, state:1, updated_by: up_Company_User,updated_date: new Date()}],"[TGT]");
2050                        }else{
2051                            //create if not exist
2052                            synchro.save('jojoflow_db.organigram_flow', [{organigram_id: data.id, flow_id:insertedFlow, state:1, created_date : new Date(), created_by : up_Company_User}] ,'[TGT]')
2053                        }
2054
2055                        var RuleName = "RL-ORG-"+data.id;
2056                        var OldRule;
2057                        //check rule
2058                        q = "select max(id) id,count(1) c from jojoflow_db.rule where name = '"+RuleName+"' and state=1";
2059                        result = synchro.query(q, '[TGT]');
2060                        if(result[0].c > 0){
2061                            //get rule
2062                            OldRule = result[0].id;
2063                        }else{
2064                            //create rule
2065                            synchro.save('jojoflow_db.rule', [{name: RuleName, company_id: data.company_id, state: 1, created_date: new Date(), created_by : up_Company_User}] , '[TGT]');
2066                            //get rule
2067                            q = "select max(id) id from jojoflow_db.rule where name = 'RL-ORG-"+data.id+"' and state=1";
2068                            result = synchro.query(q,'[TGT]');
2069                            OldRule = result[0].id;
2070
2071                        }
2072                        q = "update jojoflow_db.rule_flow set state = 4,updated_date = now() where rule_id ="+OldRule+" and state=1";
2073                        synchro.query(q,'[TGT]');
2074
2075                        objInsVariableFlow = [];
2076                        VariableFlow.forEach( function(data, index) {
2077                            //add order and rule
2078                            objInsVariableFlow.push({rule_id:OldRule,flow_id:data.flow_id, '`order`':index+1,state:1,created_date:new Date()});
2079                        });
2080
2081                        //create rule_flow from variable
2082                        synchro.save('jojoflow_db.rule_flow',objInsVariableFlow,'[TGT]')
2083
2084                        //checkk organigram_rule
2085                        q = "select count(1) c from jojoflow_db.organigram_rule where rule_id ="+OldRule+" and state = 1";
2086                        result = synchro.query(q, '[TGT]');
2087
2088                        //insert organigram_rule if no have 
2089                        if(result[0].c <= 0){
2090                            synchro.save('jojoflow_db.organigram_rule',[{organigram_id: data.id,rule_id: OldRule,state:1,created_by:up_Company_User,created_date: new Date()}],'[TGT]');
2091                        }
2092
2093                        //process child of supervisor
2094                        q = "select distinct id from jojoflow_db.organigram where organigram_head_id = "+data.id+" and company_id = "+data.company_id+" and state =1";
2095                        var childs = synchro.query(q,'[TGT]');
2096
2097                        if (childs.length > 0) {
2098                            childs.forEach( function(child) {
2099                                processChildPosition(child.id,data.id,data.company_id,up_Company_User);
2100                            });
2101                        }
2102                    }else if (user_company_id && !data.divisions && !supervisor_ID) {
2103                        //soft delete organigram_flow
2104                        q ="update jojoflow_db.organigram_flow set state = 4, updated_by = "+up_Company_User+",updated_date = now() where organigram_id = "+data.id;
2105                        synchro.query(q,'[TGT]');
2106
2107                        //soft delete rule_flow
2108                        q = "update jojoflow_db.rule_flow set state = 4,updated_date = now() where rule_id in (select id from jojoflow_db.rule where name = 'RL-ORG-"+data.id+"') and state=1";
2109                        synchro.query(q,'[TGT]');
2110
2111                        //softdelete organigram_rule
2112                        q = "update jojoflow_db.organigram_rule set state=4,updated_by = "+up_Company_User+",updated_date = now() where organigram_id ="+data.id;
2113                        synchro.query(q,'[TGT]');
2114
2115                        //process child of supervisor
2116                        q = "select distinct id from jojoflow_db.organigram where organigram_head_id = "+data.id+" and company_id = "+data.company_id+" and state =1";
2117                        var childs = synchro.query(q,'[TGT]');
2118
2119                        if (childs.length > 0) {
2120                            childs.forEach( function(child) {
2121                                processChildPosition(child.id,data.id,data.company_id,up_Company_User);
2122                            });
2123                        }
2124                    }
2125                }else{
2126                    //organigram
2127                    q = "update jojoflow_db.organigram set state =4,updated_date = now(),updated_by="+up_Company_User+" where state=1 and id="+data.id;
2128                    synchro.query(q, "[TGT]");
2129
2130                    //organigram_member
2131                    q = "update jojoflow_db.organigram_member set state=4,updated_by="+up_Company_User+" where state=1 and organigram_id="+data.id;
2132                    synchro.query(q, "[TGT]");
2133
2134                    //organigram_division
2135                    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]');
2136
2137                    //layer_organigram
2138                    synchro.query("update jojoflow_db.layer_organigram set state = 4,updated_by = "+up_Company_User+", updated_date = NOW()  where organigram_id = "+data.id+" and company_id = "+data.company_id+" and state = 1",'[TGT]');
2139
2140                    //flow_member
2141                    q = "update jojoflow_db.flow_member set state=4,updated_date = now() where state=1 and flow_id in (select id from jojoflow_db.flow where name ='FL-ORG-"+data.id+"' and state = 1)";
2142                    synchro.query(q,'[TGT]');
2143
2144                    //organigram_flow
2145                    q ="update jojoflow_db.organigram_flow set state = 4, updated_by = "+up_Company_User+",updated_date = now() where organigram_id = "+data.id+" and state=1";
2146                    synchro.query(q,'[TGT]');
2147
2148                    //flow
2149                    q = "update jojoflow_db.flow set state=4,created_date = now(),updated_by="+up_Company_User+" where state=1 and name='FL-ORG-"+data.id+"'";
2150                    synchro.query(q, "[TGT]");
2151
2152                    //soft delete rule_flow
2153                    q = "update jojoflow_db.rule_flow set state = 4,updated_date = now() where rule_id in (select id from jojoflow_db.rule where name = 'RL-ORG-"+data.id+"' and state=1) and state=1";
2154                    synchro.query(q,'[TGT]');
2155
2156                    //organigram_rule
2157                    q ="update jojoflow_db.organigram_rule set state = 4, updated_by = "+up_Company_User+",updated_date = now() where organigram_id = "+data.id+" and state=1";
2158                    synchro.query(q,'[TGT]');
2159
2160                    //rule
2161                    q = "update jojoflow_db.flow set state=4,created_date = now(),updated_by="+up_Company_User+" where state=1 and name='RL-ORG-"+data.id+"'";
2162                    synchro.query(q, "[TGT]");
2163
2164                }
2165                // update status 'processing' to 'success'
2166                q = "update import_upsert_position set process_status = 'success' \
2167                where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
2168                synchro.query(q, "[TGT]");
2169
2170                // update summary and status done to import_batch
2171                q = "update import_batch set description = 'finish', batch_endate = current_timestamp, batch_status = 'done', \
2172                data_success = (select count(1) from import_upsert_position where process_status = 'success' and batch_id = '" + insertedBatchId + "'), \
2173                data_error = (select count(1) from import_upsert_position where process_status = 'error' and batch_id = '" + insertedBatchId + "') \
2174                where batch_id = '" + insertedBatchId + "'";
2175                synchro.query(q, "[TGT]");
2176                return {
2177                    responseCode: 200,
2178                    responseMessage: "success"
2179                };
2180
2181            } else {
2182                return {
2183                    responseCode: 400,
2184                    responseMessage: 'batchId already exist'
2185                };
2186            }
2187        }
2188    } catch(err) {
2189        var errorMessage = JSON.stringify(err.toString()).split("'").join("")
2190        errorMessage = errorMessage.split('"').join('')
2191        errorMessage = errorMessage.replace("java.sql.SQLException: ", "")
2192        errorMessage = errorMessage.replace(/[^\w\s]/gi, '')
2193        // update status 'processing' to 'success'
2194        q = "update import_upsert_position set process_status = 'error' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
2195        synchro.query(q, "[TGT]")
2196        // update summary and status done to import_batch
2197        q = "update import_batch set batch_endate = current_timestamp, batch_status = 'failed', data_success = (select count(1) from import_upsert_position where process_status = 'success' and batch_id = '" + insertedBatchId + "'), data_error = (select count(1) from import_upsert_position where process_status = 'error' and batch_id = '" + insertedBatchId + "') where batch_id = '" + insertedBatchId + "'";
2198        synchro.query(q, "[TGT]");
2199        q = "update import_upsert_position set description='"+errorMessage+"' where batch_id='"+insertedBatchId+"'";
2200        synchro.query(q, "[TGT]");
2201        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_UPSERT_POSITION][99] " + batchId + " error:" + JSON.stringify(err.toString()).replace("'", "")}], '[TGT]');
2202        return {
2203            responseCode: 400,
2204            responseMessage: err.toString()
2205        };
2206    }
2207};
2208
2209function processImportDivision(fileName, insertedBatchId){
2210    synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][1]"+fileName+" Start Importing"}], '[TGT]');
2211    var q = '';
2212    
2213    try {
2214        // insert data from CSV to import-Division
2215        q = "   select trim(division_id) division_id,trim(division_code) division_code,\
2216                trim(division) division,trim(description) description,trim(head_of_division) head_of_division,\
2217                trim(flow_member) flow_member,trim(flow_type) flow_type,trim(flow_minimum_action) flow_minimum_action \
2218                from \"" + fileName + "\"";
2219        var dataCSV = synchro.query(q, "cimbimports");
2220        var rowId = 0;
2221        var objCSV = [];
2222        dataCSV.forEach(function (o) {
2223            rowId++;
2224            var obj = {
2225                batch_id: insertedBatchId,
2226                row_id: rowId,
2227                division_id: o.division_id,
2228                division_code: o.division_code,
2229                division: o.division,
2230                description: o.description,
2231                head_of_division: o.head_of_division,
2232                flow_type: o.flow_type,
2233                flow_minimum_action: o.flow_minimum_action,
2234                flow_member: o.flow_member,
2235                process_status: 'processing'
2236            };
2237            objCSV.push(obj);
2238        });
2239        synchro.save('import_division:batch_id,row_id', objCSV, "[TGT]");
2240        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][2]"+fileName+" Done Importing"}], '[TGT]');
2241
2242
2243        // update import_divison column user_company_id
2244        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 + "'";
2245        synchro.query(q, "[TGT]");
2246        
2247        // update import_batch column data_total
2248        q = "update import_batch set data_total = " + dataCSV.length + " where batch_id = '" + insertedBatchId + "'";
2249        synchro.query(q, "[TGT]");
2250
2251        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][3]"+fileName+" Start Validating"}], '[TGT]');
2252        /*************** START VALIDATE  ****************/
2253
2254        //validate column user_company_id
2255        q = "   update import_division set process_status = 'error', \
2256                log_message = 'user_company_id invalid' where batch_id = '" + insertedBatchId + "' \
2257                and user_company_id not in \
2258                    (select distinct x.id from jojonomic_db.company_user x where x.id = import_division.user_company_id)";
2259        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]");
2260        
2261        //cancel if user upload is not exist :all
2262        if(dataPostinger.length == 0){
2263            q = "   update import_division set process_status = 'error',\
2264                    log_message = 'uploader not registered.' \
2265                    where batch_id = '" + insertedBatchId + "'\
2266                    and process_status = 'processing'";
2267            synchro.query(q, "[TGT]");
2268        }else{
2269            //check multiple division code setting
2270            q = "   select state \
2271                    from jojoflow_db.setting \
2272                    where company_id = "+ dataPostinger[0].company_id +" and name = 'SETTING_MULTIPLE_DIVISION_CODE'";
2273            var settingMultiple = synchro.query(q, "[TGT]");
2274
2275            //check setting exist
2276            if(settingMultiple.length == 0){
2277                q = "   update import_division set process_status = 'error',\
2278                        log_message = 'setting multiple code not found' \
2279                        where process_status = 'processing'\
2280                        and batch_id = '" + insertedBatchId + "'";
2281                synchro.query(q, "[TGT]");
2282            }else{
2283                //division id
2284                q = "   update import_division set process_status = 'error',\
2285                            log_message = concat_ws('|',log_message,'division id cannot be empty') \
2286                            where batch_id = '" + insertedBatchId + "'\
2287                            and division_id is null";
2288                synchro.query(q, "[TGT]");
2289
2290                //division code
2291                q = "   update import_division set process_status = 'error',\
2292                            log_message = concat_ws('|',log_message,'division code cannot be empty') \
2293                            where batch_id = '" + insertedBatchId + "'\
2294                            and division_code is null ";
2295                synchro.query(q, "[TGT]");
2296
2297                //division name
2298                q = "   update import_division set process_status = 'error',\
2299                            log_message = concat_ws('|',log_message,'division name cannot be empty') \
2300                            where batch_id = '" + insertedBatchId + "'\
2301                            and division is null ";
2302                synchro.query(q, "[TGT]");
2303                //
2304
2305                //Division Name > 3 :all
2306                q = "   update import_division set process_status = 'error', \
2307                            log_message = concat_ws('|',log_message, 'division name less than 3 characters')\
2308                            where batch_id = '" + insertedBatchId + "'\
2309                            and length(division) < 4";
2310                synchro.query(q, "[TGT]");
2311
2312                //check head of division :all
2313                q = "   update import_division a \
2314                        set a.process_status = 'error', \
2315                        a.log_message = concat_ws('|',log_message,'head of division not exist')\
2316                        where a.batch_id = '" + insertedBatchId + "'\
2317                        and a.head_of_division is not null\
2318                        and a.head_of_division not in (select `code` from jojoflow_db.organigram where state = 1 and company_id = " + dataPostinger[0].company_id  +")";
2319                synchro.query(q, "[TGT]");
2320
2321                //division id exist or not :update
2322                q = "   update import_division set process_status = 'error', \
2323                            log_message = concat_ws('|',log_message,'division id not exist')\
2324                            where batch_id = '" + insertedBatchId + "'\
2325                            and division_id > 0 and division_id not in \
2326                            (select id from\
2327                                jojoflow_db.division x where x.state = 1 and x.company_id =  " + dataPostinger[0].company_id + ")";
2328                synchro.query(q, "[TGT]");
2329
2330                //validate kolom flow_member,flow_type numeric and semicolon only
2331                q = " call func_validate_importdivision('"+insertedBatchId+"') ";
2332                synchro.query(q, "[TGT]");
2333
2334                //Flow type mandatory if flow member not null :update
2335                q = "   update import_division a set process_status = 'error', \
2336                            log_message = concat_ws('|',log_message, 'flow type mandatory if flow member not null')\
2337                            where batch_id = '" + insertedBatchId + "'\
2338                            and division_id > 0 and flow_member is not null \
2339                            and flow_type is null";
2340                synchro.query(q, "[TGT]");
2341
2342                //Flow type only accept 1,2,3 :update
2343                q = "   update import_division a set process_status = 'error', \
2344                            log_message = concat_ws('|',log_message, 'flow type not acceptable')\
2345                            where batch_id = '" + insertedBatchId + "'\
2346                            and division_id > 0 and flow_type is not null \
2347                            and flow_type not in ('1','2','3')";
2348                synchro.query(q, "[TGT]");
2349
2350                //Flow minimum action mandatory if flow type = 3 :update
2351                q = "   update import_division a set process_status = 'error', \
2352                            log_message = concat_ws('|',log_message, 'flow minimum action mandatory if flow type = 3')\
2353                            where batch_id = '" + insertedBatchId + "'\
2354                            and division_id > 0 and flow_minimum_action is null \
2355                            and flow_type = 3";
2356                synchro.query(q, "[TGT]");
2357
2358                /*************** DONE VALIDATE ****************/
2359                synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][4] " + fileName + " Done Validating"}], '[TGT]');
2360                
2361
2362                /*************** START INSERTING ****************/
2363                synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][5] " + fileName + " Start Inserting"}], '[TGT]');
2364                //multiple division code not enabled
2365                if (settingMultiple[0].state == 4) {
2366                    q = "   select a.row_id, \
2367                            a.division_code code,\
2368                            a.division name,\
2369                            a.description,\
2370                            1 as state,\
2371                            case when a.head_of_division is null then 0\
2372                            else\
2373                            (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,\
2374                            "+ dataPostinger[0].company_id +" as company_id\
2375                            from import_division a\
2376                            where a.process_status = 'processing'\
2377                            and a.division_id = 0 \
2378                            and a.batch_id = '" + insertedBatchId + "'";
2379
2380                    var dataInsert = synchro.query(q, "[TGT]");
2381                    dataInsert.forEach(function(o){
2382                        //check division code if exist on another division
2383                        q = "   select count(1) c from jojoflow_db.division d where d.code = '" + o.code + "'\
2384                                and d.state = 1 and d.company_id = "+dataPostinger[0].company_id;
2385                        resultq = synchro.query(q, "[TGT]");
2386
2387                        if(resultq[0].c > 0){
2388                            q = "   update import_division set process_status = 'error', \
2389                                    log_message = concat_ws('|',log_message, 'division_code exist')\
2390                                    where batch_id = '" + insertedBatchId + "'\
2391                                    and row_id = "+ o.row_id;
2392                            resultq = synchro.query(q, "[TGT]");
2393                        }else{
2394                            var obj = [{
2395                                    company_id : o.company_id,
2396                                    name : o.name,
2397                                    code : o.code,
2398                                    head_organigram_id : o.head_organigram_id,
2399                                    description : o.description,
2400                                    state : o.state,
2401                                    created_date : new Date(),
2402                                    updated_date : new Date(),
2403                                    created_by : dataPostinger[0].company_user_id,
2404                                    updated_by : dataPostinger[0].company_user_id
2405                            }];
2406                            synchro.save('jojoflow_db.division',obj,"[TGT]");
2407                        }
2408                    });
2409                }else if(settingMultiple[0].state == 1){ //multiple division code enabled
2410                    q = "   select a.row_id,\
2411                            a.division_code code,\
2412                            a.division name,\
2413                            a.description,\
2414                            1 as state,\
2415                            case when a.head_of_division is null then 0\
2416                            else\
2417                            (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,\
2418                            "+ dataPostinger[0].company_id +" as company_id\
2419                            from syn_staging.import_division a\
2420                            where a.process_status = 'processing'\
2421                            and a.division_id = 0 \
2422                            and a.batch_id = '" + insertedBatchId + "'";
2423
2424                    var dataInsert = synchro.query(q, "[TGT]");
2425                    obj = dataInsert.map(function(o) {
2426                        return {
2427                            company_id : o.company_id,
2428                            name : o.name,
2429                            code : o.code,
2430                            head_organigram_id : o.head_organigram_id,
2431                            description : o.description,
2432                            state : o.state,
2433                            created_date : new Date(),
2434                            updated_date : new Date(),
2435                            created_by : dataPostinger[0].company_user_id,
2436                            updated_by : dataPostinger[0].company_user_id
2437                        }});
2438                    synchro.save('jojoflow_db.division',obj,"[TGT]")
2439                };
2440
2441                synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][6] " + fileName + " Done Inserting"}], '[TGT]');
2442
2443                /*************** DONE INSERTING  ****************/
2444
2445
2446                /*************** START VALIDATE  ****************/
2447                synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][7] " + fileName + " Start Validate Update"}], '[TGT]');
2448
2449                q = "select a.row_id,\
2450                        a.division_id id,\
2451                        a.division_code code,\
2452                        a.division name,\
2453                        a.description,\
2454                        1 as state,\
2455                        case when a.head_of_division is null then 0\
2456                        else\
2457                        (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,\
2458                        flow_member,\
2459                        flow_type,\
2460                        flow_minimum_action,\
2461                        process_status,\
2462                        "+ dataPostinger[0].company_id +" as company_id\
2463                        from import_division a\
2464                        where a.batch_id = '" + insertedBatchId + "'\
2465                        and a.division_id > 0";
2466
2467                var resultq = synchro.query(q, "[TGT]");
2468                if (resultq.length > 0) {
2469                    resultq.forEach(function(o){
2470                        if(o.flow_member){
2471                            var listMember = o.flow_member.split(';');
2472                            if (listMember.length > 0) {
2473                                if(o.flow_minimum_action > listMember.length ){
2474                                    q = "   update import_division set process_status = 'error', \
2475                                            log_message = concat_ws('|',log_message, 'flow minimum action must be less or equal to flow member')\
2476                                            where batch_id = '" + insertedBatchId + "'\
2477                                            and row_id = "+ o.row_id;
2478                                    synchro.query(q, "[TGT]");
2479                                }else{
2480                                    var i = 0;
2481                                    while(i < listMember.length){
2482                                        q = "select count(1) c from jojoflow_db.organigram where id = '"+listMember[i]+"' and state = 1 and company_id = "+ dataPostinger[0].company_id;
2483                                        result = synchro.query(q, "[TGT]");
2484                                        if (result[0].c == 0) {
2485                                            q = "   update import_division set  \
2486                                                    process_status = 'error',\
2487                                                    log_message = concat_ws('|',log_message, 'member "+listMember[i]+" not exist')\
2488                                                    where batch_id = '" + insertedBatchId + "'\
2489                                                    and row_id = "+ o.row_id;
2490                                            result = synchro.query(q, "[TGT]");
2491                                        }
2492                                        i++;
2493                                    }
2494                                }
2495                            }
2496                        }
2497                    });
2498                }
2499                synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][8] " + fileName + " Done Validate Update"}], '[TGT]');
2500                /*************** DONE VALIDATE  ****************/
2501
2502                /*************** START UPDATING  ****************/
2503                synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][9] " + fileName + " Start Updating"}], '[TGT]');
2504                q = "select a.row_id,\
2505                        a.division_id id,\
2506                        a.division_code code,\
2507                        a.division name,\
2508                        a.description,\
2509                        1 as state,\
2510                        case when a.head_of_division is null or a.head_of_division = 0 then 0\
2511                        else\
2512                        (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,\
2513                        flow_member,\
2514                        flow_type,\
2515                        flow_minimum_action,\
2516                        process_status,\
2517                        "+ dataPostinger[0].company_id +" as company_id\
2518                        from import_division a\
2519                        where a.batch_id = '" + insertedBatchId + "'\
2520                        and a.division_id > 0\
2521                        and process_status = 'processing'";
2522
2523                var resultq = synchro.query(q, "[TGT]");
2524                if (resultq.length > 0) {
2525                    resultq.forEach(function(o){
2526                        var DivisionCodeStatus = true;
2527                        if (settingMultiple[0].state == 4) {
2528                            //check division code if exist on another division
2529                            q = "   select count(1) c from jojoflow_db.division d where d.code = '" + o.code + "'\
2530                                    and d.id != "+o.id +" and d.state = 1 and d.company_id = "+dataPostinger[0].company_id;
2531                            resultq = synchro.query(q, "[TGT]");
2532
2533                            if(resultq[0].c > 0){
2534                                q = "   update import_division set process_status = 'error', \
2535                                        log_message = concat_ws('|',log_message, 'division_code exist')\
2536                                        where process_status = 'processing'\
2537                                        and row_id = "+ o.row_id +"\
2538                                        and batch_id = '" + insertedBatchId + "'";
2539                                result = synchro.query(q, "[TGT]");
2540                                DivisionCodeStatus = false;
2541                            }
2542                        }
2543                        if (DivisionCodeStatus) {
2544                            var dataUpdate = [{
2545                                id: o.id,
2546                                company_id: o.company_id,
2547                                name: o.name,
2548                                code: o.code,
2549                                head_organigram_id: o.head_organigram_id,
2550                                description: o.description,
2551                                state: o.state,
2552                                updated_date: new Date(),
2553                                updated_by: dataPostinger[0].company_user_id
2554                            }];
2555                            synchro.save('jojoflow_db.division:id',dataUpdate,"[TGT]");
2556
2557                            //check division_flow
2558                            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+")";
2559                            var dataDivFlow = synchro.query(q, "[TGT]");
2560                            var divisionName;
2561                            if (dataDivFlow.length > 0) {
2562                                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;
2563                                synchro.query(q, "[TGT]");
2564
2565                                q = "update jojoflow_db.division_flow set type = null where id = "+dataDivFlow[0].id;
2566                                synchro.query(q, "[TGT]");
2567
2568                                q = "update jojoflow_db.flow_member set state = 4,updated_date = NOW()\
2569                                    where flow_id = '"+ dataDivFlow[0].flow_id +"' and state = 1";
2570                                synchro.query(q, "[TGT]");
2571                                if(o.flow_member){
2572                                    var listMember = o.flow_member.split(';');
2573                                    if (listMember.length > 0) {
2574                                        var i = 0;
2575                                        while(i < listMember.length){
2576                                            // updateData = [{
2577                                            //     flow_id: dataDivFlow[0].flow_id,
2578                                            //     organigram_id: listMember[i],
2579                                            //     `order`: i + 1,
2580                                            //     state: 1,
2581                                            //     active: 1,
2582                                            //     created_date:new Date(),
2583                                            //     updated_date: new Date()
2584                                            // }];
2585                                            // synchro.save('jojoflow_db.flow_member',updateData,"[TGT]");
2586                                            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())";
2587                                            synchro.query(q,"[TGT]");
2588                                            i++;
2589                                        }
2590                                    }
2591                                }
2592                            }else{
2593                               if (o.flow_member) {
2594                                    var listMember = o.flow_member.split(';');
2595                                    if (listMember.length > 0) {
2596                                        divisionName = 'FL-DVS-'+o.id;
2597                                        var insertFlow = [{
2598                                            name : divisionName,
2599                                            company_id : dataPostinger[0].company_id,
2600                                            type: o.flow_type,
2601                                            minimum_action: o.flow_minimum_action,
2602                                            division_id: o.id,
2603                                            state: 1,
2604                                            created_date: new Date(),
2605                                            updated_date: new Date(),
2606                                            created_by: dataPostinger[0].company_user_id,
2607                                            updated_by: dataPostinger[0].company_user_id
2608                                         }];
2609                                        synchro.save('jojoflow_db.flow',insertFlow,"[TGT]");
2610
2611                                        //get flow id
2612                                        q = "   select max(id) id from jojoflow_db.flow\
2613                                                where name='"+ divisionName +"' and division_id = "+o.id+ " and company_id = "+dataPostinger[0].company_id;
2614                                        result = synchro.query(q, "[TGT]");
2615                                        var flow_id = result[0].id;
2616                                        var i = 0;
2617                                        while (i < listMember.length) {
2618                                            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())";
2619                                            synchro.query(q,"[TGT]");
2620                                            i++;
2621                                        }
2622                                        //create division flow
2623                                        var insertDivFlow = [{
2624                                            division_id: o['id'],
2625                                            flow_id: flow_id,
2626                                            state: 1,
2627                                            created_date:new Date(),
2628                                            updated_date:new Date(),
2629                                            created_by: dataPostinger[0].company_user_id,
2630                                            updated_by: dataPostinger[0].company_user_id
2631                                        }];
2632                                        synchro.save('jojoflow_db.division_flow',insertDivFlow,"[TGT]")
2633                                    }
2634                                } 
2635                            }
2636                        }
2637                    });
2638                }
2639
2640                synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][10] " + fileName + " Done Updating"}], '[TGT]');
2641                /*************** DONE UPDATING  ****************/
2642            }
2643        }
2644        
2645        // update status 'processing' to 'success'
2646        q = "update import_division set process_status = 'success' \
2647        where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
2648        synchro.query(q, "[TGT]");
2649
2650         // update status 'processing' to 'success'
2651        q = "update import_division set description = log_message ,log_message = null\
2652        where batch_id = '" + insertedBatchId + "'";
2653        synchro.query(q, "[TGT]");
2654
2655        // update summary and status done to import_batch
2656        q = "update import_batch set description = 'finish', batch_endate = current_timestamp, batch_status = 'done', \
2657        data_success = (select count(1) from import_division where process_status = 'success' and batch_id = '" + insertedBatchId + "'), \
2658        data_error = (select count(1) from import_division where process_status = 'error' and batch_id = '" + insertedBatchId + "') \
2659        where batch_id = '" + insertedBatchId + "'";
2660        synchro.query(q, "[TGT]");
2661        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][11] " + fileName + " finished"}], '[TGT]');
2662
2663    } catch (err) {
2664        // update summary and status done to import_batch
2665        // throw err;
2666        var errorMessage = JSON.stringify(err.message).split("'").join("")
2667        errorMessage = errorMessage.split('"').join('')
2668        errorMessage = errorMessage.replace("java.sql.SQLException: ", "")
2669        if(errorMessage.toString().toLowerCase().indexOf("java.lang.reflect.InvocationTargetException".toLowerCase()) != -1) {
2670            errorMessage = "Invalid header format";
2671        }
2672        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 + "'";
2673        synchro.query(q, "[TGT]");
2674        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_DIVISION]["+insertedBatchId+"][99] " + fileName + " error:" + JSON.stringify(err.message).replace("'", "")}], '[TGT]');
2675    }
2676};
2677
2678function processImportAmountPolicy(fileName, insertedBatchId){
2679    synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][1]"+fileName+" start"}], '[TGT]');
2680    var q = '';
2681    
2682    try {
2683        // insert from csv into import_amount_policy
2684        //***************** START ******************
2685        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 + "\"";
2686        var dataCSV = synchro.query(q, "cimbimports");
2687        var rowId = 0;
2688        var objCSV = [];
2689        var regexMaxAmount = "^[0-9]+(.[0-9]+)*$" ;
2690        var regexPolicyID = "^[0-9]*$" ;
2691        dataCSV.forEach(function(o){
2692            rowId++;
2693            var obj = {
2694                batch_id : insertedBatchId,
2695                row_id: rowId,
2696                policy_id: o['id'],
2697                policy_name: o['policy_name'],
2698                maximum_amount: o['maximum_amount'],
2699                expense_category_id: o['expense_category_id'],
2700                position_layer_id: o['position_layer_id'],
2701                process_status: 'processing'
2702            };
2703            objCSV.push(obj);
2704        });
2705        synchro.save('import_amount_policy:batch_id,row_id', objCSV, "[TGT]");
2706        
2707        // update import_amount_policy kolom user_company_id
2708        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 + "'";
2709        synchro.query(q, "[TGT]");
2710        
2711        // update import_batch column data_total
2712        q = "update import_batch set data_total = " + dataCSV.length + " where batch_id = '" + insertedBatchId + "'";
2713        synchro.query(q, "[TGT]");
2714        //***************** END ******************
2715        
2716        // validasi data required
2717        //***************** START ******************
2718        //validate kolom user_company_id
2719        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)";
2720        // DAMP.log(q.toString());
2721        synchro.query(q, "[TGT]");
2722        
2723        //validate kolom id required
2724        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";
2725        // DAMP.log(q.toString());
2726        synchro.query(q, "[TGT]");
2727        
2728        //validate kolom policy_name required
2729        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";
2730        synchro.query(q, "[TGT]");
2731        
2732        //validate kolom maximum_amount required
2733        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";
2734        synchro.query(q, "[TGT]");
2735        //***************** END ******************
2736        
2737        // validasi content data
2738        //***************** START ******************
2739        //validate kolom policy_id integer only
2740        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+"' ";
2741        // DAMP.log(q.toString());
2742        synchro.query(q, "[TGT]");
2743        
2744        //validate kolom policy_name length > 3
2745        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";
2746        // DAMP.log(q.toString());
2747        synchro.query(q, "[TGT]");
2748        
2749        //validate kolom maximum_amount numeric only
2750        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+"' ";
2751        // DAMP.log(q.toString());
2752        synchro.query(q, "[TGT]");
2753        
2754        //validate kolom policy_id invalid
2755        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)";
2756        // DAMP.log(q.toString());
2757        synchro.query(q, "[TGT]");
2758        
2759        //validate kolom expense_category_id numeric and semicolon only
2760        q = " call func_validate_amountpolicy('"+insertedBatchId+"') ";
2761        synchro.query(q, "[TGT]");
2762        
2763        
2764        //add by arif @11-0-2020
2765        //********************  VALIDATE CATEGORY ID *********************
2766        //********************  START *********************
2767        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" ;
2768        //DAMP.log(q);
2769        var targetRes = synchro.query(q, "[TGT]");
2770        targetRes.forEach(function(o){
2771            if(o['expense_category_id']) {
2772                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,',')+") ";
2773                var result = synchro.query(q, "[TGT]");
2774                result = parseInt(result[0]['c']) ;
2775                if(result > 0) {
2776                    var carSplit = o['expense_category_id'].split(";");
2777                    if(carSplit.length > 0) {
2778                        var i = 0 ;
2779                        while(i < carSplit.length) {
2780                            q = "select count(*) c from jojonomic_db.company_expense a where a.company_id = '"+o['company_id']+"' and a.id = "+carSplit[i]+" ";
2781                            //DAMP.log(q);
2782                            var resCheck = synchro.query(q, "[TGT]");
2783                            //DAMP.log(resCheck.toString());
2784                            resCheck = parseInt(resCheck[0]['c']) ;
2785                            //DAMP.log(resCheck.toString()) ;
2786                            if(resCheck == 0) {
2787                                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']+" ";
2788                                //DAMP.log(q);
2789                                synchro.query(q, "[TGT]");
2790                            }
2791                            
2792                            i++ ;
2793                        }
2794                    }
2795                }else {
2796                    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']+" ";
2797                    synchro.query(q, "[TGT]");
2798                }
2799            }
2800        });
2801        //********************  END *********************
2802        
2803        //********************  VALIDATE LAYER ID *********************
2804        //********************  START *********************
2805        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" ;
2806        var targetRes = synchro.query(q, "[TGT]");
2807        targetRes.forEach(function(o){
2808            if(o['position_layer_id']) {
2809                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,',')+") ";
2810                var result = synchro.query(q, "[TGT]");
2811                result = parseInt(result[0]['c']) ;
2812                if(result > 0) {
2813                    var carSplit = o['position_layer_id'].split(";");
2814                    if(carSplit.length > 0) {
2815                        var i = 0 ;
2816                        while(i < carSplit.length) {
2817                            q = "select count(*) c from jojoflow_db.layer a where a.company_id = '"+o['company_id']+"' and a.id = " +carSplit[i]+ " ";
2818                            var resCheck = synchro.query(q, "[TGT]");
2819                            resCheck = parseInt(resCheck[0]['c']) ;
2820                            
2821                            if(resCheck == 0) {
2822                                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']+" ";
2823                                synchro.query(q, "[TGT]");
2824                            }
2825                            
2826                            i++ ;
2827                        }
2828                    }
2829                }else {
2830                    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']+" ";
2831                    synchro.query(q, "[TGT]");
2832                }
2833            }
2834        });
2835        //********************  END *********************
2836        //***************** END ******************
2837        
2838        // INSERT DATA VALID
2839        //***************** START ******************
2840        q = " select a.row_id, (select x.company_id ";
2841        q += "        from jojonomic_db.company_user x ";
2842        q += "            where x.id = a.user_company_id) company_id, ";
2843        q += "              a.policy_name as name, ";
2844        q += "              round(a.maximum_amount,2) maximum_amount, ";
2845        q += "              now() as created_date, ";
2846        q += "              '1' as state, a.expense_category_id, a.position_layer_id ";
2847        q += " from import_amount_policy a ";
2848        q += " where a.batch_id = '"+insertedBatchId+"' ";
2849        q += " and a.process_status = 'processing' ";
2850        q += " and a.policy_id = '0' ";
2851        // DAMP.log(q.toString());
2852        // DAMP.log('test arif');
2853        var targetRes = synchro.query(q, "[TGT]");
2854        // DAMP.log('test arif');
2855        targetRes.forEach(function(o){
2856            var objInsertAmountPolicy = [];
2857            objInsertAmountPolicy.push(buildInsertAmountPolicy(o)) ;
2858            
2859            synchro.save('expense_db.amount_policy', objInsertAmountPolicy, "[TGT]") ;
2860            
2861            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+"') " ;
2862             //DAMP.log(qry.toString());
2863            var resultPolicyID = synchro.query(qry, "[TGT]");
2864            
2865            if(o['expense_category_id']) {
2866                q = " select a.id as expense_id, '"+o['company_id']+"' as company_id, "+resultPolicyID[0]['id']+" as amount_policy_id, ";
2867                q += "       now() as created_date, ";
2868                q += "           1 as state ";
2869                q += " from jojonomic_db.company_expense a ";
2870                q += " where a.company_id = '"+o['company_id']+"' ";
2871                q += " and a.id in ("+o['expense_category_id'].replace(/;/g,',')+") ";
2872                // DAMP.log(q.toString());
2873                var resultAmountCategRelation = synchro.query(q, "[TGT]");
2874                
2875                if(resultAmountCategRelation.length > 0) {
2876                    var dataAmountCategRelation = resultAmountCategRelation.map(function(data) {
2877                                                      return {
2878                                                        expense_id : data.expense_id,
2879                                                        company_id: data.company_id,
2880                                                        amount_policy_id: data.amount_policy_id,
2881                                                        created_date: new Date(),
2882                                                        updated_date: new Date(),
2883                                                        state: data.state
2884                                                        }
2885                                                    });
2886                    synchro.save('expense_db.amount_policy_category_relation', dataAmountCategRelation, "[TGT]") ;
2887                }
2888            }
2889            
2890            if(o['position_layer_id']) {
2891                q = " select a.id as layer_id, '"+o['company_id']+"' as company_id, "+resultPolicyID[0]['id']+" as amount_policy_id, ";
2892                q += "       now() as created_date, ";
2893                q += "       '1' as state ";
2894                q += " from jojoflow_db.layer a ";
2895                q += " where a.company_id = '"+o['company_id']+"' ";
2896                q += " and a.id in ("+o['position_layer_id'].replace(/;/g,',')+") ";
2897                // DAMP.log(q.toString());
2898                var resultAmountLayerRelation = synchro.query(q, "[TGT]");
2899                
2900                if(resultAmountLayerRelation.length > 0) {
2901                    var dataAmountLayerRelation = resultAmountLayerRelation.map(function(data) {
2902                                                      return {
2903                                                        layer_id : data.layer_id,
2904                                                        company_id: data.company_id,
2905                                                        amount_policy_id: data.amount_policy_id,
2906                                                        created_date: new Date(),
2907                                                        updated_date: new Date(),
2908                                                        state: data.state
2909                                                        }
2910                                                    });
2911                    synchro.save('expense_db.amount_policy_layer_relation', dataAmountLayerRelation, "[TGT]") ;
2912                }
2913            }
2914            
2915            
2916        });
2917        if(targetRes.length > 0) {
2918            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] insert expense_db.amount_policy"}], '[TGT]');
2919            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] insert expense_db.amount_policy_category_relation"}], '[TGT]');
2920            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] insert expense_db.amount_policy_layer_relation"}], '[TGT]');
2921        }
2922        //***************** END ******************
2923        
2924        // UPDATE DATA VALID
2925        //***************** START ******************
2926        q = " select a.policy_id as id,  (select x.company_id ";
2927        q += "        from jojonomic_db.company_user x ";
2928        q += "            where x.id = a.user_company_id) company_id, ";
2929        q += "              a.policy_name as name, ";
2930        q += "              round(a.maximum_amount,2) maximum_amount, ";
2931        q += "              now() as updated_date, ";
2932        q += "              '1' as state, a.expense_category_id, a.position_layer_id ";
2933        q += " from import_amount_policy a ";
2934        q += " where a.batch_id = '"+insertedBatchId+"' ";
2935        q += " and a.process_status = 'processing' ";
2936        q += " and a.policy_id <> '0' ";
2937        
2938        var targetRes = synchro.query(q, "[TGT]");
2939        targetRes.forEach(function(o){
2940            var objUpdateAmountPolicy = [];
2941            objUpdateAmountPolicy.push(buildUpdateAmountPolicy(o)) ;
2942            
2943            synchro.save('expense_db.amount_policy:id', objUpdateAmountPolicy, "[TGT]") ;
2944            
2945            q = " update expense_db.amount_policy_category_relation set state = 4, updated_date = now()  ";
2946            q += " where amount_policy_id = "+o['id']+"  ";
2947            q += " and company_id = '"+o['company_id']+"' ";
2948            //q += " and expense_id in ( ";
2949            //q += " select a.id  ";
2950            //q += " from jojonomic_db.company_expense a ";
2951            //q += " where a.company_id = '"+o['company_id']+"' ";
2952            //q += " and a.id in ("+o['expense_category_id'].replace(/;/g,',')+")) ";
2953            synchro.query(q, "[TGT]");
2954            
2955            if(o['expense_category_id']) {      
2956                q = " select a.id as expense_id, '"+o['company_id']+"' as company_id, "+o['id']+" as amount_policy_id, ";
2957                q += "       now() as created_date, ";
2958                q += "           1 as state ";
2959                q += " from jojonomic_db.company_expense a ";
2960                q += " where a.company_id = '"+o['company_id']+"' ";
2961                q += " and a.id in ("+o['expense_category_id'].replace(/;/g,',')+") ";
2962                var resultAmountCategRelation = synchro.query(q, "[TGT]");
2963                
2964                if(resultAmountCategRelation.length > 0) {
2965                    var dataAmountCategRelation = resultAmountCategRelation.map(function(data) {
2966                                                      return {
2967                                                        expense_id : data.expense_id,
2968                                                        company_id: data.company_id,
2969                                                        amount_policy_id: data.amount_policy_id,
2970                                                        created_date: new Date(),
2971                                                        state: data.state
2972                                                        }
2973                                                    });
2974                    synchro.save('expense_db.amount_policy_category_relation', dataAmountCategRelation, "[TGT]") ;
2975                }
2976            }
2977            
2978            q = " update expense_db.amount_policy_layer_relation set state = 4, updated_date = now() ";
2979            q += " where company_id = '"+o['company_id']+"' ";
2980            q += " and amount_policy_id = "+o['id']+" ";
2981            //q += " and layer_id in ( ";
2982            //q += " select a.id as layer_id ";
2983            //q += " from jojoflow_db.layer a ";
2984            //q += " where a.company_id = '"+o['company_id']+"' ";
2985            //q += " and a.id in ("+o['position_layer_id'].replace(/;/g,',')+")) ";
2986            synchro.query(q, "[TGT]");
2987            if(o['position_layer_id']) {        
2988                q = " select a.id as layer_id, '"+o['company_id']+"' as company_id, "+o['id']+" as amount_policy_id, ";
2989                q += "       now() as created_date, ";
2990                q += "       '1' as state ";
2991                q += " from jojoflow_db.layer a ";
2992                q += " where a.company_id = '"+o['company_id']+"' ";
2993                q += " and a.id in ("+o['position_layer_id'].replace(/;/g,',')+") ";
2994                var resultAmountLayerRelation = synchro.query(q, "[TGT]");
2995                
2996                if(resultAmountLayerRelation.length > 0) {
2997                    var dataAmountLayerRelation = resultAmountLayerRelation.map(function(data) {
2998                                                      return {
2999                                                        layer_id : data.layer_id,
3000                                                        company_id: data.company_id,
3001                                                        amount_policy_id: data.amount_policy_id,
3002                                                        created_date: new Date(),
3003                                                        state: data.state
3004                                                        }
3005                                                    });
3006                    synchro.save('expense_db.amount_policy_layer_relation', dataAmountLayerRelation, "[TGT]") ;
3007                }
3008            }
3009            
3010        }) ;
3011        if(targetRes.length > 0) {
3012            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] update expense_db.amount_policy"}], '[TGT]');
3013            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] delete expense_db.amount_policy_category_relation"}], '[TGT]');
3014            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] insert expense_db.amount_policy_category_relation"}], '[TGT]');
3015            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] delete expense_db.amount_policy_layer_relation"}], '[TGT]');
3016            synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][3] insert expense_db.amount_policy_layer_relation"}], '[TGT]');         
3017        }
3018        //***************** END ******************
3019        
3020
3021        // update status 'processing' to 'success'
3022        q = "update import_amount_policy set process_status = 'success' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
3023        synchro.query(q, "[TGT]")
3024        // update summary and status done to import_batch
3025        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 + "'";
3026        synchro.query(q, "[TGT]");
3027        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"] " + fileName + " finished"}], '[TGT]');
3028        
3029    } catch (err) {
3030        // update summary and status done to import_batch
3031        var errorMessage = JSON.stringify(err.message).split("'").join("")
3032        errorMessage = errorMessage.split('"').join('')
3033        errorMessage = errorMessage.replace("java.sql.SQLException: ", "")
3034        if(errorMessage.toString().toLowerCase().indexOf("java.lang.reflect.InvocationTargetException".toLowerCase()) != -1) {
3035            errorMessage = "Invalid header format";
3036        }
3037        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 + "'";
3038        synchro.query(q, "[TGT]");
3039        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_AMOUNT_POLICY]["+insertedBatchId+"][99] " + fileName + " error:" + JSON.stringify(err.message).replace("'", "")}], '[TGT]');
3040    }
3041};
3042
3043function upsertDivision(RequestId, DivisionId, DivisionCode, Division, Description,  HeadOfDivision, FlowMember, FlowType, FlowMinimumAction , State, user_company_id){
3044
3045    var batchName = 'upsert_division';
3046    var batchId = getBatchId(batchName, RequestId);
3047    var q = '';
3048    var allowedState = [1,4];
3049    var allowedFlowType = [1,2,3];
3050    var insertedBatchId = '';
3051
3052    try {
3053
3054        //val updater
3055        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]");
3056
3057        // validate DivisionID
3058        var exec = true;
3059        if (!RequestId) {
3060            exec = false;
3061            throw 'invalid request id';
3062        }
3063
3064        if (dataPostinger.length == 0) {
3065            exec = false;
3066            throw 'invalid user_company_id';
3067        }
3068
3069        if (!(allowedState.indexOf(parseInt(State)) >= 0)) {
3070            exec = false;
3071            throw 'state is not allowed';
3072        }
3073
3074        var settingMultiple;
3075        if (dataPostinger.length != 0) {
3076            q = "select state from jojoflow_db.setting where company_id = "+ dataPostinger[0].company_id +" and name = 'SETTING_MULTIPLE_DIVISION_CODE'";
3077            settingMultiple = synchro.query(q, "[TGT]");
3078            if(settingMultiple.length == 0){
3079                exec = false;
3080                throw 'Setting multiple code not found';
3081            }else {
3082                q = "select count(1) c from jojoflow_db.division where id = '" + DivisionId + "' and company_id = "+dataPostinger[0].company_id;
3083                var valDivisionId = synchro.query(q, "[TGT]");
3084                valDivisionId = parseInt(valDivisionId[0]['c']);
3085
3086                if (valDivisionId < 1) {
3087                    exec = false;
3088                    throw 'invalid division_id';
3089                };
3090                if (parseInt(State) == 1) {
3091                    if (Division.length <= 3) {
3092                        exec = false;
3093                        throw 'division name less than 3 characters';
3094                    };
3095
3096                    if(Description){
3097                        Description = Description.trim();
3098                    };
3099
3100                    if(HeadOfDivision){
3101                        HeadOfDivision = HeadOfDivision.trim();
3102                    };
3103
3104                    if(FlowMember){
3105                        FlowMember = FlowMember.trim();
3106                    };
3107
3108                    if(FlowMinimumAction){
3109                        FlowMinimumAction = FlowMinimumAction.trim();
3110                        if(!isInteger(FlowMinimumAction)){
3111                            exec = false;
3112                            throw 'flow minimum action must be int';
3113                        }
3114                    }else{
3115                        FlowMinimumAction = null;
3116                    };
3117
3118                    if(FlowType){
3119                        FlowType = FlowType.trim();
3120                        if(!isInteger(FlowType)){
3121                            exec = false;
3122                            throw 'flow type must be int';
3123                        }
3124                    }else{
3125                        FlowType = null;
3126                    };
3127
3128                    if (FlowMember != null && FlowType == null) {
3129                        exec = false;
3130                        throw 'Flow type cannot be empty, when flow_member is not null';
3131                    };
3132
3133                    if(FlowType){
3134                        if (!(allowedFlowType.indexOf(parseInt(FlowType)) >= 0)) {
3135                            exec = false;
3136                            throw 'flow type is not allowed';
3137                        };
3138
3139                        if (parseInt(FlowType) == 3 && (FlowMinimumAction == null || FlowMinimumAction.length < 1)) {
3140                            exec = false;
3141                            throw 'flow minimum action mandatory if flow type = 3';
3142                        };
3143                    };
3144
3145                    if (HeadOfDivision) {
3146                        q = "select count(1) c from jojoflow_db.organigram where `code` ='"+HeadOfDivision+"' and state = 1 and company_id ="+dataPostinger[0].company_id;
3147                        var valHeadOfDivision = synchro.query(q, "[TGT]");
3148                        valHeadOfDivision = parseInt(valHeadOfDivision[0]['c']);
3149                        if (valHeadOfDivision < 1) {
3150                            exec = false;
3151                            throw 'invalid head_of_division';
3152                        }
3153                    };
3154
3155
3156                    if (!HeadOfDivision){
3157                        HeadOfDivision = 0;
3158                    };
3159
3160                    if(settingMultiple[0].state == 4){
3161                        q = "select count(1) c from jojoflow_db.division d where d.code = '" + DivisionCode + "'\
3162                             and d.id != '"+DivisionId +"' and d.state = 1 and d.company_id ="+dataPostinger[0].company_id;
3163                        var valMultiDivCode = synchro.query(q, "[TGT]");
3164                        valMultiDivCode = parseInt(valMultiDivCode[0]['c']);
3165                        if (valMultiDivCode >= 1) {
3166                            exec = false;
3167                            throw 'division_code is exists';
3168                        };
3169                    };
3170
3171                    if(FlowMember){
3172                        var listMember = FlowMember.split(";");
3173                        if(listMember.length > 0){
3174                            //error if minimum action greater than flow member
3175                            if(FlowMinimumAction > listMember.length ){
3176                                exec = false;
3177                                throw 'flow minimum action must be less or equal flow_member';
3178                            }else{
3179                                i = 0;
3180                                stopMember = false;
3181                                while(i < listMember.length && stopMember == false){
3182                                    q = "select count(1) c from jojoflow_db.organigram where id = '"+listMember[i]+"' and state = 1 and company_id = "+ dataPostinger[0].company_id;
3183                                    result = synchro.query(q, "[TGT]");
3184                                    if (result[0].c == 0) {
3185                                        exec = false;
3186                                        stopMember = true;
3187                                        throw ['member ', listMember[i],' not exists'].join(' ');
3188                                    }
3189                                    i++;
3190                                }
3191                            }
3192                        }
3193                    }
3194                }
3195            }
3196        }
3197
3198        if (exec) {
3199            if (isBatchIdExists(batchId) == false) {
3200                insertedBatchId = addBatch(batchId, batchName, RequestId, user_company_id);
3201                var upsertData = [{
3202                    batch_id: insertedBatchId,
3203                    division_id: DivisionId,
3204                    division_code: DivisionCode,
3205                    division: Division,
3206                    description: Description,
3207                    head_of_division: HeadOfDivision,
3208                    flow_member: FlowMember,
3209                    flow_type: FlowType,
3210                    flow_minimum_action: FlowMinimumAction,
3211                    state: State,
3212                    user_company_id: user_company_id,
3213                    process_status: 'processing'
3214                }];
3215                synchro.save('import_upsert_division', upsertData, "[TGT]");
3216
3217                if (parseInt(State) == 1) {
3218                    var HeadOrganigramID;
3219                    if (HeadOfDivision != 0) {
3220                        q = "select x.id from jojoflow_db.organigram x where x.code = '"+HeadOfDivision+"' and state = 1 and x.company_id ="+dataPostinger[0].company_id;
3221                        var HeadOrganigram = synchro.query(q,"[TGT]");
3222                        HeadOrganigramID = HeadOrganigram[0].id;
3223                    }else{
3224                        HeadOrganigramID = HeadOfDivision;
3225                    }
3226                    
3227                    var dataUpdate = [{
3228                        id: DivisionId,
3229                        company_id: dataPostinger[0].company_id,
3230                        name: Division,
3231                        code: DivisionCode,
3232                        head_organigram_id: HeadOrganigramID,
3233                        description: Description,
3234                        state: State,
3235                        updated_date: new Date(),
3236                        updated_by: dataPostinger[0].company_user_id
3237                    }];
3238                    synchro.save('jojoflow_db.division:id',dataUpdate,"[TGT]");
3239
3240                    //check division_flow
3241                    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+")";
3242                    var dataDivFlow = synchro.query(q, "[TGT]");
3243
3244                    if (dataDivFlow.length > 0) {
3245                        q = "update jojoflow_db.division_flow set type = null where id = "+dataDivFlow[0].id;
3246                                synchro.query(q, "[TGT]"); 
3247
3248                        q = "update jojoflow_db.flow_member set state = 4,updated_date = NOW()\
3249                            where flow_id = '"+ dataDivFlow[0].flow_id +"' and state=1";
3250                        synchro.query(q, "[TGT]");
3251
3252                        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;
3253                        synchro.query(q, "[TGT]");
3254
3255                        var divisionName;
3256                        if(FlowMember){
3257                            var listMember = FlowMember.split(';');
3258                            if(listMember.length > 0){
3259
3260                                var i = 0;
3261                                while(i < listMember.length){
3262                                    // updateData = [{
3263                                    //     flow_id: dataDivFlow[0].flow_id,
3264                                    //     organigram_id: listMember[i],
3265                                    //     `order`: i + 1,
3266                                    //     state: 1,
3267                                    //     active: 1,
3268                                    //     created_date:new Date(),
3269                                    //     updated_date: new Date()
3270                                    // }];
3271                                    // synchro.save('jojoflow_db.flow_member',updateData,"[TGT]");
3272                                    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())";
3273                                    synchro.query(q,"[TGT]");
3274                                    i++;
3275                                }
3276                            }
3277                        }
3278                    }else{
3279                        if(FlowMember){
3280                            var listMember = FlowMember.split(';');
3281                            if(listMember.length > 0){
3282                                divisionName = 'FL-DVS-'+DivisionId;
3283                                var insertFlow = [{
3284                                    name : divisionName,
3285                                    company_id : dataPostinger[0].company_id,
3286                                    type: FlowType,
3287                                    minimum_action: FlowMinimumAction,
3288                                    division_id: DivisionId,
3289                                    state: 1,
3290                                    created_date: new Date(),
3291                                    updated_date: new Date(),
3292                                    created_by: dataPostinger[0].company_user_id,
3293                                    updated_by: dataPostinger[0].company_user_id
3294                                 }];
3295                                synchro.save('jojoflow_db.flow',insertFlow,"[TGT]");
3296
3297                                //get flow id
3298                                q = "select max(id) id from jojoflow_db.flow\
3299                                        where name='"+ divisionName +"' and division_id = "+DivisionId+ " and company_id = "+dataPostinger[0].company_id;
3300                                result = synchro.query(q, "[TGT]");
3301                                var flow_id = result[0].id;
3302
3303                                i = 0;
3304                                while(i < listMember.length){
3305                                    // updateData = [{
3306                                    //     flow_id: dataDivFlow[0].flow_id,
3307                                    //     organigram_id: listMember[i],
3308                                    //     `order`: i + 1,
3309                                    //     state: 1,
3310                                    //     active: 1,
3311                                    //     created_date:new Date(),
3312                                    //     updated_date: new Date()
3313                                    // }];
3314                                    // synchro.save('jojoflow_db.flow_member',updateData,"[TGT]");
3315                                    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())";
3316                                    synchro.query(q,"[TGT]");
3317                                    i++;
3318                                }
3319                                //create division flow
3320                                var insertDivFlow = [{
3321                                    division_id: DivisionId,
3322                                    flow_id: flow_id,
3323                                    state: 1,
3324                                    created_date:new Date(),
3325                                    updated_date:new Date(),
3326                                    created_by: dataPostinger[0].company_user_id,
3327                                    updated_by: dataPostinger[0].company_user_id
3328                                }];
3329                                synchro.save('jojoflow_db.division_flow',insertDivFlow,"[TGT]")
3330                            }
3331                        }
3332                    }
3333                }else{
3334                    q = "update jojoflow_db.division set state = 4, updated_date=now() where id = "+DivisionId+" and state=1";
3335                    synchro.query(q, "[TGT]");
3336
3337                    //get flow id
3338                    q = "select max(flow_id) id from jojoflow_db.division_flow\
3339                            where division_id = "+DivisionId;
3340                    result = synchro.query(q, "[TGT]");
3341                    var flow_id = result[0].id;
3342                    
3343                    q = "update jojoflow_db.flow_member set state = 4, updated_date=now() where flow_id = "+flow_id+" and state=1";
3344                    synchro.query(q, "[TGT]");
3345
3346                    q = "update jojoflow_db.division_flow set state = 4, updated_date=now() where division_id = "+DivisionId+" and state=1";
3347                    synchro.query(q, "[TGT]");
3348
3349                    
3350                    q = "update jojoflow_db.flow set state = 4, updated_date=now() where division_id = "+DivisionId+" and state=1";
3351                    synchro.query(q, "[TGT]");
3352                };
3353
3354                // update status 'processing' to 'success'
3355                q = "update import_upsert_division set process_status = 'success' \
3356                where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
3357                synchro.query(q, "[TGT]");
3358
3359                 // update status 'processing' to 'success'
3360                q = "update import_upsert_division set description = log_message ,log_message = null\
3361                where batch_id = '" + insertedBatchId + "'";
3362                synchro.query(q, "[TGT]");
3363
3364                // update summary and status done to import_batch
3365                q = "update import_batch set description = 'finish', batch_endate = current_timestamp, batch_status = 'done', \
3366                data_success = (select count(1) from import_upsert_division where process_status = 'success' and batch_id = '" + insertedBatchId + "'), \
3367                data_error = (select count(1) from import_upsert_division where process_status = 'error' and batch_id = '" + insertedBatchId + "') \
3368                where batch_id = '" + insertedBatchId + "'";
3369                synchro.query(q, "[TGT]");
3370                return {
3371                    responseCode: 200,
3372                    responseMessage: "success"
3373                };
3374            } else {
3375                return {
3376                    responseCode: 400,
3377                    responseMessage: 'batchId already exist'
3378                };
3379            }
3380        };
3381    } catch(err) {
3382        var errorMessage = JSON.stringify(err.toString()).split("'").join("")
3383        errorMessage = errorMessage.split('"').join('')
3384        errorMessage = errorMessage.replace("java.sql.SQLException: ", "")
3385        errorMessage = errorMessage.replace(/[^\w\s]/gi, '')
3386        // update status 'processing' to 'success'
3387        q = "update import_upsert_division set process_status = 'error' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
3388        synchro.query(q, "[TGT]")
3389        // update summary and status done to import_batch
3390        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 + "'";
3391        synchro.query(q, "[TGT]");
3392        q = "update import_upsert_division set description='"+errorMessage+"' where batch_id='"+insertedBatchId+"'";
3393        synchro.query(q, "[TGT]");
3394        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_UPSERT_DIVISION][99] " + batchId + " error:" + JSON.stringify(err.toString()).replace("'", "")}], '[TGT]');
3395        return {
3396            responseCode: 400,
3397            responseMessage: err.toString()
3398        };
3399    }
3400};
3401
3402function upsertAmountPolicy(requestId, policyId, policyName, maximumAmount, expenseCategoryId, positionLayerId, state, userCompanyId) {
3403    var batchName = 'upsert_amount_policy';
3404    var batchId = getBatchId(batchName, requestId);
3405    var insertedBatchId = "";
3406    var q = '';
3407    var allowedState = [1,4];
3408
3409    try {
3410        // validate request body
3411        // validate policy id
3412        q = "select count(1) c from expense_db.amount_policy where id = " + policyId;
3413        var validatePolicyID = synchro.query(q, "[TGT]");
3414        validatePolicyID = parseInt(validatePolicyID[0]['c']);
3415        
3416        // validate user_company_id
3417        q = "select count(1) c from jojonomic_db.company_user where id = " + userCompanyId;
3418        var validateUser = synchro.query(q, "[TGT]");
3419        validateUser = parseInt(validateUser[0]['c']);
3420        
3421        // validate policy_name
3422        var validatePolicyName = false ;
3423        if(policyName.length <= 3) {
3424            validatePolicyName = true ;
3425        }
3426
3427        var validateExpense = false ;
3428        var validateLayer = false ;
3429        if(state == '1') {
3430            if(expenseCategoryId) {
3431                // validate expense_category_id
3432                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,',')+") ";
3433                var result = synchro.query(q, "[TGT]");
3434                result = parseInt(result[0]['c']) ;
3435                if(result > 0) {
3436                    var carSplit = expenseCategoryId.split(";");
3437                    if(carSplit.length > 0) {
3438                        var i = 0 ;
3439                        while(i < carSplit.length) {
3440                            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]+" ";
3441                            //DAMP.log(q);
3442                            var resCheck = synchro.query(q, "[TGT]");
3443                            //DAMP.log(resCheck.toString());
3444                            resCheck = parseInt(resCheck[0]['c']) ;
3445                            //DAMP.log(resCheck.toString()) ;
3446                            if(resCheck == 0) {
3447                                validateExpense = true ;
3448                            }           
3449                            i++ ;
3450                        }
3451                    }
3452                }else {
3453                    validateExpense = true ;
3454                }
3455            }
3456            
3457            if(positionLayerId) {
3458                // validate position_layer_id
3459                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,',')+") ";
3460                var result = synchro.query(q, "[TGT]");
3461                result = parseInt(result[0]['c']) ;
3462                if(result > 0) {
3463                    var carSplit = positionLayerId.split(";");
3464                    if(carSplit.length > 0) {
3465                        var i = 0 ;
3466                        while(i < carSplit.length) {
3467                            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]+ " ";
3468                            var resCheck = synchro.query(q, "[TGT]");
3469                            resCheck = parseInt(resCheck[0]['c']) ;
3470                                        
3471                            if(resCheck == 0) {
3472                                validateLayer = true ;
3473                            }  
3474                            i++ ;
3475                        }
3476                    }
3477                }else {
3478                    validateLayer = true ;
3479                }
3480            }
3481        }
3482
3483        var exec = true;
3484        if (!requestId) {
3485            exec = false;
3486            throw 'invalid request id';
3487        }
3488        if (!(allowedState.indexOf(parseInt(state)) >= 0)) {
3489            exec = false;
3490            throw 'state is not alowed';
3491        }
3492        if (validatePolicyID < 1) {
3493            exec = false;
3494            throw 'invalid policy_id';
3495        }
3496        if(validatePolicyName) {
3497            exec = false;
3498            throw 'policy_name length is invalid';
3499        }
3500        if (validateUser < 1) {
3501            exec = false;
3502            throw 'invalid user_company_id';
3503        }
3504        if(state == '1') {
3505            if (validateExpense) {
3506                exec = false;
3507                throw 'invalid expense_category_id';
3508            }
3509            if (validateLayer) {
3510                exec = false;
3511                throw 'invalid position_layer_id';
3512            }
3513        }
3514
3515        if (exec){
3516            if (isBatchIdExists(batchId) == false) {
3517                // add batch
3518                insertedBatchId = addBatch(batchId, batchName, requestId, userCompanyId);
3519                var upsertData = {
3520                    batch_id: insertedBatchId,
3521                    policy_id: policyId,
3522                    policy_name: policyName,
3523                    maximum_amount: maximumAmount,
3524                    expense_category_id: expenseCategoryId,
3525                    position_layer_id: positionLayerId,
3526                    user_company_id: userCompanyId,
3527                    state: state,
3528                    process_status: 'processing'
3529                };
3530                synchro.save('import_upsert_amount_policy', [upsertData], "[TGT]");
3531                
3532                if(state == '1') {
3533                    // UPDATE DATA VALID
3534                    //***************** START ******************
3535                    q = " select a.policy_id as id,  (select x.company_id ";
3536                    q += "        from jojonomic_db.company_user x ";
3537                    q += "            where x.id = a.user_company_id) company_id, ";
3538                    q += "              a.policy_name as name, ";
3539                    q += "              round(a.maximum_amount,2) maximum_amount, ";
3540                    q += "              now() as updated_date, ";
3541                    q += "              a.state as state, a.expense_category_id, a.position_layer_id ";
3542                    q += " from import_upsert_amount_policy a ";
3543                    q += " where a.batch_id = '" + insertedBatchId + "' ";
3544                    q += " and a.process_status = 'processing' ";
3545                    q += " and a.policy_id = "+policyId+" ";
3546
3547                    var targetRes = synchro.query(q, "[TGT]");
3548                    targetRes.forEach(function (o) {
3549                        var objUpdateAmountPolicy = [];
3550                        objUpdateAmountPolicy.push(buildUpdateAmountPolicy(o));
3551
3552                        synchro.save('expense_db.amount_policy:id', objUpdateAmountPolicy, "[TGT]");
3553
3554                        
3555                        q = " update expense_db.amount_policy_category_relation set state = 4, updated_date = now()  ";
3556                        q += " where amount_policy_id = " + o['id'] + "  ";
3557                        q += " and company_id = '" + o['company_id'] + "' ";
3558                        //q += " and expense_id in ( ";
3559                        //q += " select a.id  ";
3560                        //q += " from jojonomic_db.company_expense a ";
3561                        //q += " where a.company_id = '" + o['company_id'] + "' ";
3562                        //q += " and a.id in (" + o['expense_category_id'].replace(/;/g, ',') + ")) ";
3563                        synchro.query(q, "[TGT]");
3564                        if (o['expense_category_id']) {
3565                            q = " select a.id as expense_id, '" + o['company_id'] + "' as company_id, " + o['id'] + " as amount_policy_id, ";
3566                            q += "       now() as created_date, ";
3567                            q += "           1 as state ";
3568                            q += " from jojonomic_db.company_expense a ";
3569                            q += " where a.company_id = '" + o['company_id'] + "' ";
3570                            q += " and a.id in (" + o['expense_category_id'].replace(/;/g, ',') + ") ";
3571                            var resultAmountCategRelation = synchro.query(q, "[TGT]");
3572
3573                            if (resultAmountCategRelation.length > 0) {
3574                                var dataAmountCategRelation = resultAmountCategRelation.map(function (data) {
3575                                    return {
3576                                        expense_id: data.expense_id,
3577                                        company_id: data.company_id,
3578                                        amount_policy_id: data.amount_policy_id,
3579                                        created_date: new Date(),
3580                                        state: data.state
3581                                    }
3582                                });
3583                                synchro.save('expense_db.amount_policy_category_relation', dataAmountCategRelation, "[TGT]");
3584                            }
3585                        }
3586                        
3587                        q = " update expense_db.amount_policy_layer_relation set state = 4, updated_date = now() ";
3588                        q += " where company_id = '" + o['company_id'] + "' ";
3589                        q += " and amount_policy_id = " + o['id'] + " ";
3590                        //q += " and layer_id in ( ";
3591                        //q += " select a.id as layer_id ";
3592                        //q += " from jojoflow_db.layer a ";
3593                        //q += " where a.company_id = '" + o['company_id'] + "' ";
3594                        //q += " and a.id in (" + o['position_layer_id'].replace(/;/g, ',') + ")) ";
3595                        synchro.query(q, "[TGT]");
3596                        
3597                        if (o['position_layer_id']) {
3598                            q = " select a.id as layer_id, '" + o['company_id'] + "' as company_id, " + o['id'] + " as amount_policy_id, ";
3599                            q += "       now() as created_date, ";
3600                            q += "       '1' as state ";
3601                            q += " from jojoflow_db.layer a ";
3602                            q += " where a.company_id = '" + o['company_id'] + "' ";
3603                            q += " and a.id in (" + o['position_layer_id'].replace(/;/g, ',') + ") ";
3604                            var resultAmountLayerRelation = synchro.query(q, "[TGT]");
3605
3606                            if (resultAmountLayerRelation.length > 0) {
3607                                var dataAmountLayerRelation = resultAmountLayerRelation.map(function (data) {
3608                                    return {
3609                                        layer_id: data.layer_id,
3610                                        company_id: data.company_id,
3611                                        amount_policy_id: data.amount_policy_id,
3612                                        created_date: new Date(),
3613                                        state: data.state
3614                                    }
3615                                });
3616                                synchro.save('expense_db.amount_policy_layer_relation', dataAmountLayerRelation, "[TGT]");
3617                            }
3618                        }
3619
3620                    });
3621                }else {
3622                    q = "update expense_db.amount_policy set state = 4, updated_date=now() where id = "+policyId+"";
3623                    synchro.query(q, "[TGT]");
3624                    
3625                    q = "update expense_db.amount_policy_category_relation set state = 4, updated_date=now() where amount_policy_id = "+policyId+"";
3626                    synchro.query(q, "[TGT]");
3627                    
3628                    q = "update expense_db.amount_policy_layer_relation set state = 4, updated_date=now() where amount_policy_id = "+policyId+"";
3629                    synchro.query(q, "[TGT]");
3630                }
3631                // update status 'processing' to 'success'
3632                q = "update import_upsert_amount_policy set process_status = 'success' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
3633                synchro.query(q, "[TGT]")
3634                // update summary and status done to import_batch
3635                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 + "'";
3636                synchro.query(q, "[TGT]");
3637                
3638                return {
3639                    responseCode: 200,
3640                    responseMessage: "success"
3641                };
3642
3643            } else {
3644                return {
3645                    responseCode: 400,
3646                    responseMessage: 'batchId already exist'
3647                };
3648            }
3649        }
3650
3651    } catch(err) {
3652
3653        var errorMessage = JSON.stringify(err.toString()).split("'").join("");
3654        errorMessage = errorMessage.split('"').join('')
3655        errorMessage = errorMessage.replace("java.sql.SQLException: ", "")
3656        errorMessage = errorMessage.replace(/[^\w\s]/gi, '')
3657        // update status 'processing' to 'success'
3658        q = "update import_upsert_amount_policy set process_status = 'error' where batch_id = '" + insertedBatchId + "' and process_status = 'processing'";
3659        synchro.query(q, "[TGT]")
3660        // update summary and status done to import_batch
3661        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 + "'";
3662        synchro.query(q, "[TGT]");
3663        q = "update import_upsert_amount_policy set description='"+errorMessage+"' where batch_id='"+insertedBatchId+"'";
3664        synchro.query(q, "[TGT]");
3665        synchro.save('logs', [{ts:new Date(), msg: "[IMPORT_UPSERT_AMOUNT_POLICY]["+insertedBatchId+"][99] " + batchId + " error:" + JSON.stringify(err.toString()).replace("'", "")}], '[TGT]');
3666        return {
3667            responseCode: 400,
3668            responseMessage: err.toString()
3669        };
3670
3671    }
3672
3673};
3674
3675function dump(arr,level) {
3676    var dumped_text = "";
3677    if(!level) level = 0;
3678    
3679    //The padding given at the beginning of the line.
3680    var level_padding = "";
3681    for(var j=0;j<level+1;j++) level_padding += "    ";
3682    
3683    if(typeof(arr) == 'object') { //Array/Hashes/Objects 
3684        for(var item in arr) {
3685            var value = arr[item];
3686            
3687            if(typeof(value) == 'object') { //If it is an array,
3688                dumped_text += level_padding + "'" + item + "' ...\n";
3689                dumped_text += dump(value,level+1);
3690            } else {
3691                dumped_text += level_padding + "'" + item + "' => \"" + value + "\"\n";
3692            }
3693        }
3694    } else { //Stings/Chars/Numbers etc.
3695        dumped_text = "===>"+arr+"<===("+typeof(arr)+")";
3696    }
3697    return dumped_text;
3698};
3699
3700var is_allow = true;
3701function PublishServiceWS(e) {
3702    DAMP.WSStart(e.name);
3703    try {
3704        for (;;) {
3705            // try {
3706                var r = DAMP.WSFetchRequest(e.name);
3707                r.params["auth_bearer_token"] = "";
3708                r.params["auth_basic_username"] = "";
3709                r.params["auth_basic_password"] = "";
3710                if(typeof r.headers["authorization"] != "undefined") {
3711                    var authorization = r.headers["authorization"];
3712
3713                    if(authorization && authorization.indexOf("Bearer") != -1) {
3714                        var token = authorization.match(/Bearer (.*)/);
3715                        if(token) {
3716                            r.params["auth_bearer_token"] = token[1];
3717
3718                        }
3719                    }
3720                    if(authorization && authorization.indexOf("Basic") != -1) {
3721                        var token = authorization.match(/Basic (.*)/);
3722                        if(token) {
3723                            var token_b64 = token[1];
3724                            var basic = Base64.decode(token_b64).split(":");
3725                            if(basic) {
3726                                r.params["auth_basic_username"] = basic[0];
3727                                r.params["auth_basic_password"] = basic[1];
3728                                // throw JSON.stringify({
3729                                //     usr: basic[0],
3730                                //     pass: basic[1]
3731                                // });
3732                            }
3733                        }
3734                    }
3735                }
3736                // throw JSON.stringify(r.params);
3737
3738                if(typeof(e.guard) == "string") {
3739                    var c = build_query.from_string(
3740                        e.guard, r.params
3741                    );
3742                    
3743                    if(c.length <= 0) {
3744                        r.response = JSON.stringify({
3745                            responseCode: 401,
3746                            responseMessage: "Unauthorized"
3747                        });
3748                        DAMP.WSResponse(r);
3749                    } else {   
3750                        init_ws(e, r);
3751                    }
3752                } else if(e.guard instanceof Array) {
3753                    var is_allow = true;
3754                    for(var i=0; i < e.guard.length; i++) {
3755                        if(is_allow) {
3756                            var o = e.guard[i];
3757                            var b = build_query.from_string(
3758                                o.query, r.params
3759                            );
3760                            if(b.length <= 0 || b instanceof Object) {
3761                                is_allow = false;
3762                            }
3763                        }
3764                    }
3765                    if(is_allow) {
3766                        init_ws(e, r);
3767                    } else {
3768                        var code, message;
3769                        if(!o.error) {
3770                            code = 401;
3771                            message = "Unauthorized"
3772                        } else {
3773                            code = o.error.code;
3774                            message = o.error.message;
3775                        }
3776                        r.response = JSON.stringify({
3777                            responseCode: code,
3778                            responseMessage: message
3779                        });
3780                        DAMP.WSResponse(r);
3781                    }
3782                } else {
3783                    init_ws(e, r);
3784                }
3785            // } catch(err) {
3786            //     r.responseCode = "520";
3787            //     DAMP.WSResponse(r)
3788            // }
3789        }
3790    } finally {
3791        DAMP.WSStop(e.name);
3792    }
3793}