app/include/modelOdbc.js

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

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

//--- 사용하지 않음
class ModelOdbc {
    constructor() {
        this._table = 'devices';
        this._fieldNames = [];
    }

    get table() {
        return {
            findOne: this.findOne.bind(this),
            findAll: this.findAll.bind(this),
            create: this.create.bind(this),
            update: this.update.bind(this),
            destroy: this.destroy.bind(this),
        };
    }

    async findOne(sql, strQuery = null) {
        let results = await this.findAll(sql, strQuery);
        if (0 < results.count) {
            if ((strQuery != null) && (-1 < strQuery.indexOf('NEXTVAL'))) {
                return results[0]['NEXTVAL'];
            } else {
                return results[0];
            }
        } else {
            return null;
        }
    }

    async findAll(sql, strQuery = null) {
        let query = [];

        if (strQuery == null) {
            query.push('SELECT *');
            query.push(`  FROM ${this._table}`);

            //--- WHERE 문 처리
            if (typeof(sql.where) != 'undefined') {
                query.push(' WHERE');
                query.push(this._set(sql.where, 'AND'));
            }

            //--- ORDER BY 문 처리
            if (typeof(sql.order) != 'undefined') {
                query.push(' ORDER BY');
                let data = [];
                for (let idx = 0; idx < sql.order.length; idx++) {
                    let item = sql.order[idx];
                    
                    switch(item[0]) {
                    case 'site': break;
                    default:
                        data.push(`${item[0]} ${item[1]}`);
                        break;
                    }
                }
                query.push(data.join(', '));
            }

            //--- LIMIT 문 처리
            if (typeof(sql.limit) != 'undefined') {
                query.push(` LIMIT ${sql.limit}`);
            }
            query = query.join(' ');
        } else {
            query = strQuery;
        }

        logger.info(query);
        let results = await db.conn.query(query);
        
        if (config.databases.obcon.type == 'Tibero') {
            for (let idx = 0; idx < results.length; idx++) {
                for (let pos = 0; pos < this._fieldNames.length; pos++) {
                    let fieldName = this._fieldNames[pos];
                    if (typeof(results[idx][fieldName.toUpperCase()]) != 'undefined') {
                        results[idx][fieldName] = results[idx][fieldName.toUpperCase()];
                    }
                }
            }
        }
        return results;
    }

    _set(item, delimiter, isIn=false) {
        let data = [];
        for (const [key, value] of Object.entries(item)) {
            if (isIn) {
                // let key1 = (config.databases.obcon.type == 'Tibero') ? key.toUpperCase():key;
                if (this._fieldNames.includes(key) == false) {
                    continue;
                }
            }

            switch(key) {
            case 'site': break;
            default:
                switch(typeof(value)) {
                case 'string':
                    data.push(`${key} = '${value}'`);
                    break;
                case 'number':
                case 'boolean':
                default:
                    data.push(`${key} = ${value}`);
                    break;
                }
                break;
            }
        }
        return data.join(` ${delimiter} `);
    }

    async create(item, strQuery = null) {
        let query = `SELECT seq_${this._table}.NEXTVAL FROM DUAL`.replace('OBICON.', '');
        let id = await this.findOne(null, query);
        item['id'] = id;

        query = [];
        if (strQuery == null) {
            query.push(`INSERT INTO ${this._table} (`);
            let data = [];
            for (const [key, value] of Object.entries(item)) {
                // let key1 = (config.databases.obcon.type == 'Tibero') ? key.toUpperCase():key;
                if (this._fieldNames.includes(key)) {
                    data.push(key);
                }
            }
            query.push(data.join(', '));
            query.push(') VALUES (');
            data = [];
            for (const [key, value] of Object.entries(item)) {
                // let key1 = (config.databases.obcon.type == 'Tibero') ? key.toUpperCase():key;
                if (this._fieldNames.includes(key)) {
                    switch(key) {
                    case 'site': break;
                    default:
                        switch(typeof(value)) {
                        case 'string':
                            data.push(`'${value}'`);
                            break;
                        case 'number':
                        case 'boolean':
                        default:
                            data.push(`${value}`);
                            break;
                        }
                        break;
                    }
                }
            }
            query.push(data.join(', '));
            query.push(')');

            query = query.join(' ');
        } else {
            query = strQuery;
        }

        logger.info(`SQL Create : ${query}`);
        
        let results = await db.conn.query(query);
        results[0] = results.count;
        return results;
    }

    async update(item, sql = null, strQuery = null) {
        let query = [];

        if (strQuery == null) {
            query.push(`UPDATE ${this._table}`);

            query.push('   SET');
            query.push(this._set(item, ',', true));

            //--- WHERE 문 처리
            if (typeof(sql.where) != 'undefined') {
                query.push(' WHERE');
                query.push(this._set(sql.where, 'AND'));
            }
            query = query.join(' ');
        } else {
            query = strQuery;
        }

        logger.info(`SQL Update : ${query}`);
        let results = await db.conn.query(query);
        results[0] = results.count;
        return results;
    }

    async destroy(sql, strQuery = null) {
        let query = [];

        if (strQuery == null) {
            query.push(`DELETE FROM ${this._table}`);
            //--- WHERE 문 처리
            if (typeof(sql.where) != 'undefined') {
                query.push(' WHERE');
                query.push(this._set(sql.where, 'AND'));
            }
            query = query.join(' ');
        } else {
            query = strQuery;
        }

        //--- To-Do : ppp, 테스트 필요
        logger.info(`SQL delete : ${query}`);
        if (-1 < query.indexOf('id = ')) {
            let results = await db.conn.query(query);
            results[0] = results.count;
            return results;
        }
        return 0;
    }

    init() {
    }
}

module.exports = ModelOdbc;