app/modules/batch/dataCleanup.js

'use strict'
/**
 * Copyright (c) 2017~2020, OBCon Inc.
 * All rights reserved.
 */

/**
 * @file
 * @copyright 2017~2020, OBCon Inc.
 * @author gye hyun james kim [pnuskgh@gmail.com]
 */

let Sequelize = require('sequelize');
let Batch = utils.getClass('include', 'batch.js');
const Op = Sequelize.Op;

//--- mysql -uobcon -p${PASSWD} -D obcondb < d:/zzbackup/CAProtec/obcondb_20200120_010001.sql
class DataCleanup extends Batch {
    constructor() {
        super();
    }

    batchJob(options = {}, callback) {
        let myPromise = new Promise(function(resolve, reject) {
            resolve({ code: 0, message: 'Start application' });
        });

        //--- statusDatetime가 잘못된 데이터를 확인하고 정리 한다.
        for (let idx = 1; idx <= 7; idx++) {
            myPromise = myPromise.then(function(value) {
                logger.info(`code: ${value.code}, message: ${value.message}`);
                return this.runStatusDatetime(value, `devicedata${idx}s`);
            }.bind(this));
        }
        
        //--- 중복된 데이터를 삭제 한다.
        for (let idx = 1; idx <= 7; idx++) {
            myPromise = myPromise.then(function(value) {
                logger.info(`code: ${value.code}, message: ${value.message}`);
                return this.runDeleteMulti(value, `devicedata${idx}s`);
            }.bind(this));
        }

        myPromise = myPromise.then(function(value) {
            logger.info(`code: ${value.code}, message: ${value.message}`);
            callback(value.code, value.message);
        });

        myPromise = myPromise.catch(function(err) {
            logger.info(`code: ${err.code}, message: ${err.message}`);
            callback(err.code, err.message);
        });
    }

    //--- statusDatetime가 잘못된 데이터를 확인하고 정리 한다.
    checkStatusDatetime(value, tablename) {
        return new Promise(function(resolve, reject) {
            let query = {
                where: {
                    [Op.or]: [
                        { statusDatetime: { [Op.lt]: '20180101' } },
                        { statusDatetime: { [Op.gt]: '21001231' } }
                    ]
                }
            };
    
            modules[tablename].model.table.count(query)
            .then(function(count) {
                resolve({ code: 0, message: `${tablename} count - ${count}` });
            }).catch(function(err) {
                //--- err.name          : 에러 이름
                logger.error('error : ' + JSON.stringify(err));
                reject({ code: 1, message: JSON.stringify(err) });
            });
        });
    }

    runStatusDatetime(value, tablename) {
        return new Promise(function(resolve, reject) {
            let query = {
                where: {
                    [Op.or]: [
                        { statusDatetime: { [Op.lt]: '20180101' } },
                        { statusDatetime: { [Op.gt]: '21001231' } }
                    ]
                }
            };
    
            modules[tablename].model.table.destroy(query)
            .then(function(count) {
                resolve({ code: 0, message: `${tablename} delete count - ${count}` });
            }).catch(function(err) {
                logger.error('error : ' + JSON.stringify(err));
                reject({ code: 1, message: JSON.stringify(err) });
            });
        });
    }

    //--- 중복된 데이터를 삭제 한다.
    checkDeleteMulti(value, tablename) {
        return new Promise(function(resolve, reject) {
            let query = {
                attributes: ['id', 'siteKey', 'type', 'deviceKey', 'statusDatetime'],
                order: [ ['siteKey', 'ASC'], ['type', 'ASC'], ['deviceKey', 'ASC'], ['statusDatetime', 'ASC'] ]
            };
    
            let saveStatusDatetime = '';
            let count = 0;
            modules[tablename].model.table.findAll(query)
            .then(function(data) {
                logger.info(`${tablename} read count - ${data.length}`);
                for (let idx = 0; idx < data.length; idx++) {
                    let item = data[idx];

                    let tmpStatusDatetime = `${item.siteKey}_${item.type}_${item.deviceKey}_${item.statusDatetime}`;
                    if (saveStatusDatetime == tmpStatusDatetime) {
                        count = count + 1;
                    } else {
                        saveStatusDatetime = tmpStatusDatetime;
                    }
                }

                resolve({ code: 0, message: `${tablename} multi count - ${count}` })
            }).catch(function(err) {
                logger.error('error : ' + JSON.stringify(err));
                reject({ code: 1, message: JSON.stringify(err) })
            });
        });
    }

    runDeleteMulti(value, tablename) {
        return new Promise(function(resolve, reject) {
            let query = {
                attributes: ['id', 'siteKey', 'type', 'deviceKey', 'statusDatetime'],
                order: [ ['siteKey', 'ASC'], ['type', 'ASC'], ['deviceKey', 'ASC'], ['statusDatetime', 'ASC'] ]
            };
    
            let saveStatusDatetime = '';
            let count = 0;
            let myOrCount = 0;
            let myOr = [];
            modules[tablename].model.table.findAll(query)
            .then(function(data) {
                logger.info(`${tablename} read count - ${data.length}`);
                for (let idx = 0; idx < data.length; idx++) {
                    let item = data[idx];

                    let tmpStatusDatetime = `${item.siteKey}_${item.type}_${item.deviceKey}_${item.statusDatetime}`;
                    if (saveStatusDatetime == tmpStatusDatetime) {
                        count = count + 1;
                        myOrCount = myOrCount + 1;
                        myOr.push({ id: item.id });

                        if (myOrCount == 100) {
                            // query = { where: { id: item.id } };
                            query = { where: { [Op.or]: myOr } };
                            modules[tablename].model.table.destroy(query)
                            .then(function(count) {
                            }).catch(function(err) {
                                logger.error('error : ' + JSON.stringify(err));
                                reject({ code: 1, message: JSON.stringify(err) });
                            });

                            myOrCount = 0;
                            myOr = [];
                        }
                    } else {
                        saveStatusDatetime = tmpStatusDatetime;
                    }
                }

                if (myOrCount == 0) {
                    resolve({ code: 0, message: `${tablename} delete count - ${count}` });
                } else {
                    query = { where: { [Op.or]: myOr } };
                    modules[tablename].model.table.destroy(query)
                    .then(function(result) {
                        resolve({ code: 0, message: `${tablename} delete count - ${count}` });
                    }).catch(function(err) {
                        logger.error('error : ' + JSON.stringify(err));
                        reject({ code: 1, message: JSON.stringify(err) });
                    });
                }
            }).catch(function(err) {
                logger.error('error : ' + JSON.stringify(err));
                reject({ code: 1, message: JSON.stringify(err) })
            });
        });
    }
}

module.exports = DataCleanup;