AipGateway/Database/server - 복사본.js

const express = require('express');
const cookieParser = require('cookie-parser');
const cors = require("cors");
const fs = require("fs");
const path = require("path");
// const bodyParser = require('body-parser');
const session = require('express-session');
const multer  = require('multer');
const {pool, sql} = require('./config/sql.js');

const app = express();

app.use(session({
    secret: 'secret',
    resave: false,
    saveUninitialized: true,
    cookie: {
        httpOnly: true,
        secure: true, // set this to true on production
        sameSite: 'none',
        maxAge: 60 * 60 * 24 * 1000
    }
}));

app.use(express.json());
app.use(cookieParser());
app.use(express.urlencoded({ extended: true }));
app.use(cors());

app.use("/static", express.static(path.join(__dirname, 'static')));

app.get('/', (req, res) => {
    // if (req.session) {
    //     console.log(res.session);
    // }
    res.sendFile(path.join(__dirname, '/view/dashboard.html'))
    // res.sendFile(path.join(__dirname, '/view/login.html'));
});

app.get('/dashboard', (req, res, next)=>{
    res.sendFile(path.join(__dirname, '/view/dashboard.html'));
});

app.get('/trackingLog', (req, res, next)=>{
    res.sendFile(path.join(__dirname, '/view/trackingLog.html'));
});

app.get('/gatewayLog', (req, res, next)=>{
    res.sendFile(path.join(__dirname, '/view/gatewayLog.html'));
});

app.get('/eventLog', (req, res, next)=>{
    res.sendFile(path.join(__dirname, '/view/eventLog.html'));
});

app.get('/shareAppLog', (req, res, next)=>{
    res.sendFile(path.join(__dirname, '/view/shareAppLog.html'));
});

app.get('/fileEventLog', (req, res, next)=>{
    res.sendFile(path.join(__dirname, '/view/fileEventLog.html'));
});

app.get('/logAnalytics', (req, res, next)=>{
    res.sendFile(path.join(__dirname, '/view/logAnalytics.html'));
});

app.get('/alarmReceive', (req, res, next)=>{
    res.sendFile(path.join(__dirname, '/view/alarmReceive.html'));
});
app.get('/manager', (req, res, next)=>{
    res.sendFile(path.join(__dirname, '/view/manager.html'));
});

app.get('/linked-management/:systemId',(req, res, next)=>{
    if (!req.params.systemId) {
        next();
    }

    res.sendFile(path.join(__dirname, '/view/linked-management-edit.html'));
});

app.get('/linked-management',(req, res, next)=>{
    res.sendFile(path.join(__dirname, '/view/linked-management.html'));
});

app.post('/api/login',(req, res, next)=>{
    const {username, password} = req.body;
    // console.log(username, password);
    // const sqlQuery = 
    //     `select `;
    return res.json({success: 'F', message:'test'})
});

app.post('/api/findSeverName', async (req, res, next)=>{
    const systemName = req.body.SystemName;
    if (systemName) {
        const sqlQuery = `select count(*) as COUNT from TB_LINKED_SYSTEM where SystemName = @systemName and UseYn = 1`;
        const {success, data, error} = await queryHandler(sqlQuery, {systemName: systemName});
        if (success) {
            if (data && data.recordset) {
                return res.json(data.recordset[0]);
            }
        }
        else {
            res.status(500).send(error);
        }
    }
    else{
        return res.json({error: '연동 시스템 명칭을 확인해주세요.\n시스템 명칭 : '+systemName});
    }
    
})

app.post('/api/addLinkedSystem', async (req, res, next)=>{
    const resultObject = {success : 'F', message : '추가하실 연동 시스템 정보를 찾을 수 없습니다.\n파라미터 정보를 다시 확인 해주세요'};
    const {SystemName, SystemDesc} = req.body;
    if (SystemName) {
        const sqlQuery = 
            `INSERT INTO TB_LINKED_SYSTEM (SystemName, SystemDesc) values (@SystemName, @SystemDesc)`;
        const param = {
            SystemName : SystemName, 
            SystemDesc : SystemDesc,
        }
        
        const {success, data, error} = await queryHandler(sqlQuery, param);
        if (success) {
            if (data && data.rowsAffected[0]) {
                resultObject.success = 'S';
                resultObject.message = '연동 시스템 정보가 추가되었습니다.';
            }
            else {
                resultObject.message = '연동 시스템 정보가 추가 되지 않았습니다.'
            }
        }
        else {
            res.status(500).send(error);
        }
    }
    return res.send(resultObject);
});

app.post('/api/deleteLinkedSystem', async (req, res, next)=>{
    const resultObj = {success : 'F', message : '삭제하실 연동 시스템 정보를 찾을 수 없습니다.\n파라미터 정보를 다시 확인 해주세요'};
    const {SystemId} = req.body;
    if (SystemId) {
        try {
            const que = await pool;
            let transaction = new sql.Transaction(que);
            const sqlQuery = 
                `UPDATE TB_LINKED_SYSTEM SET DeletedAt = GETDATE(), UseYn = 0 WHERE SystemId = ${SystemId}`;
            const serverQuery = 
                `UPDATE TB_LINKED_SERVER SET UseYn = 0, DeletedAt = GETDATE() WHERE SystemId = ${SystemId}`;
            const serverListQuery = 
                `SELECT * FROM TB_LINKED_SERVER WHERE SystemId = ${SystemId}`;
            
            await transaction.begin().then(async() => {
                const request = new sql.Request(transaction);
                const result = await request.query(sqlQuery);
                if (result && result.rowsAffected && result.rowsAffected[0] > 0) {
                    const result1 = await request.query(serverListQuery);
                    if (result1 && result1.recordset.length > 0) {
                        let ServerIds = '';
                        result1.recordset.forEach((obj, idx)=>{
                            ServerIds += obj.ServerId;
                            if (result1.recordset.length !== idx + 1 ) {
                                ServerIds += ', ';
                            }
                        });

                        const result2 = await request.query(serverQuery);
                        if (result2 && result2.rowsAffected[0] > 0) {
                            resultObj.success = 'S';
                            resultObj.message = '선택하신 연동 시스템 정보를 삭제하였습니다.'
                        }
                        else {
                            resultObj.success = 'F';
                            resultObj.message = '선택하신 연동 시스템 정보가 삭제 되지 않았습니다.'
                        }
                        const apiListQuery = 
                        `SELECT * FROM TB_LINKED_API_KEY WHERE ServerId IN (${ServerIds})`;
                        const result3 = await request.query(apiListQuery);
                        if (result3 && result3.recordset.length > 0) {
                            const apiQuery = 
                            `UPDATE TB_LINKED_API_KEY SET UseYn = 0, DeletedAt = GETDATE() WHERE ServerId IN (${ServerIds})`; 
                            const result4 = await request.query(apiQuery);
                            if (result4 && result4.rowsAffected && result4.rowsAffected[0] > 0) {
                                transaction.commit();
                                resultObj.success = 'S';
                                resultObj.message = '선택하신 연동 시스템 정보를 삭제하였습니다.';
                            }
                            else {
                                transaction.rollback();
                                resultObj.success = 'F';
                                resultObj.message = '선택하신 연동 시스템 정보가 삭제 되지 않았습니다.';
                            }
                            return res.json(resultObj);
                        }
                        else {
                            transaction.commit();
                            return res.json(resultObj);
                        }
                    }
                    else {
                        transaction.commit();
                        resultObj.success = 'S';
                        resultObj.message = '선택하신 연동 시스템 정보를 삭제하였습니다.'
                    }
                    res.json(resultObj);
                }
                else {
                    transaction.rollback();
                    resultObj.message = '서버 삭제 작업 중 오류가 발생하였습니다.';
                    return res.json(resultObj);
                }
            });
        }
        catch (error) {
            transaction.rollback();
            console.log(error);
            resultObj.message = '서버 삭제 작업 중 오류가 발생하였습니다.';
            resultObj.error = error;
            return res.json(resultObj);
        }
    }
    else {
        return res.send(resultObj);
    }
})

app.post('/api/linked-system/list', async (req, res, next)=>{
    const {page} = req.body;
    let pageNo = 1;
    if (page) {
        pageNo = parseInt(page);
    }

    const pagePerCount = 12;
    const pageSize = 10;

    const totalQuery = `select count(*) as total from TB_LINKED_SYSTEM WHERE UseYn = 1`;
    const query = 
        `SELECT 
            ROW_NUMBER() OVER(ORDER BY SystemId asc) AS rownum,
            S.SystemId as system_id,
            S.SystemName as system_name,
            S.SystemDesc as system_desc,
            COALESCE(CONVERT(CHAR(19), S.CreatedAt, 120), '-') as created_at,
            (SELECT count(*) FROM TB_LINKED_SERVER WHERE SystemId = S.SystemId AND UseYn = 1) as server_count
        FROM TB_LINKED_SYSTEM S WHERE UseYn = 1`;

    await paginationList(page, query, totalQuery, pagePerCount, pageSize, res, null);
})

app.post('/api/linked-system/:systemId', async (req, res, next)=>{
    const {systemId} = req.params;
    if (systemId) {
        const sqlQuery = `SELECT 
                            SystemId as system_id, 
                            SystemName as system_name, 
                            SystemDesc as system_desc 
                        FROM TB_LINKED_SYSTEM 
                        WHERE SystemId = @systemId
                        AND UseYn = 1`;
        try {
            const query = await pool;
            const result = await query.request().input('systemId', systemId).query(sqlQuery);
            if (result) {
                res.json(result.recordset)
            }
        }
        catch(err) {
            res.status(500).send(err);
        }
    }
});

app.post('/api/getSeverList', (req, res, next)=>{
    const {page, systemId} = req.body;
    const pagePerCount = 12;
    const pageSize = 10;
    
    if (page, systemId) {
        const totalQuery = `select count(*) as total from TB_LINKED_SERVER WHERE SystemId=${systemId} AND UseYn = 1`;
        const query = 
            `SELECT 
                ROW_NUMBER() OVER(ORDER BY tls.ServerId asc) AS rownum,
                tls.ServerId as server_id, 
                tls.SystemId as system_id,
                tls.ServerIpAddr as server_ip,
                tls.ServerDesc as server_desc,
                COALESCE(CONVERT(CHAR(19), tls.CreatedAt, 120), '-') as server_created_at,
                tlak.id api_id, 
                ISNULL(tlak.ApiKey, '-') as api_key, 
                COALESCE(CONVERT(CHAR(10), tlak.ExpiredAt, 120), '-') as expired_at, 
                tlak.policyLookupYn as policy_lookup_yn,
                tlak.fileInfoLookupYn as file_info_lookup_yn,
                tlak.applyLabelYn as apply_label_yn,
                tlak.releaseLabelYn as release_label_yn,
                tlak.encryptionFileYn as encryption_file_yn,
                tlak.decryptionFileYn as decryption_file_yn 
            FROM 
                (select * from TB_LINKED_SERVER where SystemId = @systemId and UseYn = 1) tls 
                left outer join (SELECT * FROM TB_LINKED_API_KEY WHERE UseYn = 1) tlak on tls.ServerId = tlak.ServerId`;
        paginationList(page, query, totalQuery, pagePerCount, pageSize, res, {systemId : systemId});
    }
    else {
        res.status(500).send('Not Found Page or SystemId');
    }
});

app.post('/api/getApiList', (req, res, next)=>{
    const {page, systemId} = req.body;
    const pagePerCount = 12;
    const pageSize = 10;
    if (page, systemId) {
        const totalCount = `select count(*) as total from TB_LINKED_SERVER WHERE SystemId=${systemId} AND UseYn = 1`;
        pool.request().query(totalCount, (error, result)=>{
            if (error) {
                console.error('Error executing query:', err.originalError);
                res.status(500).send(err.originalError.info.message);
            }
            if (result.recordset && result.recordset.length) {
                const resultObj = new Pagination(page, pageSize, pagePerCount, result.recordset[0].total);
                const sqlQuery = 
                `SELECT A.* 
                    FROM (
                    SELECT 
                        ROW_NUMBER() OVER(ORDER BY tls.ServerId asc) AS rownum,
                        tls.ServerId as server_id, 
                        tls.SystemId as system_id, 
                        tlak.id api_id, 
                        tlak.ApiKey as api_key, 
                        COALESCE(CONVERT(CHAR(10), tlak.ExpiredAt, 120), '-') as expired_at, 
                        tlak.policyLookupYn as policy_lookup_yn,
                        tlak.fileInfoLookupYn as file_info_lookup_yn,
                        tlak.applyLabelYn as apply_label_yn,
                        tlak.releaseLabelYn as release_label_yn,
                        tlak.encryptionFileYn as encryption_file_yn,
                        tlak.decryptionFileYn as decryption_file_yn 
                    FROM 
                        (select * from TB_LINKED_SERVER where SystemId = ${systemId} and UseYn = 1) tls 
                        join (SELECT * FROM TB_LINKED_API_KEY WHERE UseYn = 1) tlak on tls.ServerId = tlak.ServerId
                ) A WHERE rownum BETWEEN ${resultObj.startRow} and ${resultObj.endRow}`;
                pool.request().query(sqlQuery, (err, result) => {
                    if (err) {
                        console.error('Error executing query:', err.originalError);
                        res.status(500).send(err.originalError.info.message);
                    }

                    resultObj.list = result.recordset;
                    return res.json(resultObj);
                })
            }
        })
    }
    else {
        res.status(500).send('Not Found Page or SystemId');
    }
});

app.post('/api/systemUpdate', async (req, res, next)=>{
    const {SystemDesc, SystemName, SystemId} = req.body;
    const resultObj = { success: 'F', message : '파라미터 정보를 확인해주세요.'};
    if (SystemName, SystemId) {
        let desc = SystemDesc;
        if (!desc) {
            desc = "";
        }
        
        const sqlQuery = 
            `UPDATE TB_LINKED_SYSTEM SET SystemName = @SystemName, SystemDesc = @SystemDesc WHERE SystemId = @SystemId`;
        
        const param = {
            SystemName : SystemName,
            SystemDesc : desc,
            SystemId   : SystemId,
        }
        
        const {data, success, error} = await queryHandler(sqlQuery, param);
        
        if (success) {
            if (data && data.rowsAffected && data.rowsAffected[0] > 0) {
                resultObj.message = '시스템 정보가 수정되었습니다.';
                resultObj.success = 'S';
            }
            else {
                resultObj.message = '시스템 정보가 수정되지 않았습니다.';
            }
        }
        else {
            return res.status(500).send(error);
        }
    }
    res.json(resultObj);
});


app.post('/api/findIpServer', async (req, res, next)=>{
    const {ServerIpAddr, SystemId} = req.body;
    const sqlQuery = 
    `SELECT COUNT(*) AS count FROM TB_LINKED_SERVER WHERE UseYn = 1 AND ServerIpAddr = @ServerIpAddr AND SystemId = @SystemId`;
    const param = {
        ServerIpAddr : ServerIpAddr,
        SystemId : SystemId,
    }
    const {success, error, data} = await queryHandler(sqlQuery, param);
    if (success && data && data.recordset && data.recordset[0]) {
        return res.json(data.recordset[0])
    }
    else {
        return res.status(500).send(error);
    }
});

app.post('/api/addServer', async (req, res, next)=>{
    const {
        ServerIpAddr, 
        ServerDesc, 
        SystemId, 
        policyLookupYn,    
        fileInfoLookupYn,  
        applyLabelYn, 
        releaseLabelYn,    
        encryptionFileYn,  
        decryptionFileYn,
        ApiKey,
        ExpiredAt,
    } = req.body;
    const resultObj = { success: 'F', message : '파라미터 정보를 확인해주세요.'};
    if (ServerIpAddr, SystemId) {
        let desc = ServerDesc;
        if (!desc) {
            desc = "";
        }
        const sqlQuery = 
            `INSERT INTO TB_LINKED_SERVER (SystemId, ServerIpAddr, ServerDesc) VALUES('${SystemId}', '${ServerIpAddr}', '${ServerDesc}');SELECT @@identity as id;`;
        
        try {
            const que = await pool;
            let transaction = new sql.Transaction(que);
            await transaction.begin().then(async() => {
                const request = new sql.Request(transaction);
                request.query(sqlQuery, async (err, result)=>{
                    if (err) {
                        console.error('Error executing query:', err.originalError);
                        res.status(500).send(err.originalError.info.message);
                    }
                    if (result && result.rowsAffected && result.rowsAffected[0] > 0 && result.recordset && result.recordset[0]) {
                        const ServerId = result.recordset[0].id;
                        const apiSqlQuery = 
                            `INSERT INTO 
                                TB_LINKED_API_KEY ( ServerId, ApiKey, ExpiredAt, policyLookupYn, fileInfoLookupYn, applyLabelYn, releaseLabelYn, 
                                                    encryptionFileYn, decryptionFileYn ) 
                                    VALUES ('${ServerId}', '${ApiKey}', '${ExpiredAt}', ${policyLookupYn}, ${fileInfoLookupYn}, ${applyLabelYn}, ${releaseLabelYn}, 
                                            ${encryptionFileYn}, ${decryptionFileYn})`;
                        let result2 = await request.query(apiSqlQuery);
                        if (result2 && result2.rowsAffected && result2.rowsAffected[0] > 0) {
                            transaction.commit();
                            resultObj.success = 'S';
                            resultObj.message = '요청하신 서버 정보가 추가 되었습니다.';
                        }
                        else {
                            resultObj.message = '서버 추가 작업 중 오류가 발생하였습니다.';
                            transaction.rollback();
                        }
                        return res.json(resultObj);
                    }
                    else {
                        resultObj.message = '서버 추가 작업 중 오류가 발생하였습니다.';
                        transaction.rollback();
                        return res.json(resultObj);
                    }
                });
            })
        }
        catch (error) {
            // console.error('Error executing query:', err.originalError);
            // res.status(500).send(err.originalError.info.message);
            console.log(error);
            resultObj.message = '서버 추가 작업 중 오류가 발생하였습니다.';
            resultObj.error = error;
            return res.json(resultObj);
        }
    }
    else {
        res.json(resultObj);
    }
});


app.post('/api/editServer', async (req, res, next)=>{
    const {
        api_id,
        system_id,
        server_ip,
        server_id,           
        server_desc,
        expired_at,
        file_info_lookup_yn,
        policy_lookup_yn,
        release_label_yn,
        decryption_file_yn,
        encryption_file_yn,
        apply_label_yn,
    } = req.body;
    const resultObj = { success: 'F', message : '파라미터 정보를 확인해주세요.'};
    if (system_id, server_ip, expired_at, api_id, server_id) {
        let desc = server_desc;
        if (!desc) {
            desc = "";
        }
        const sqlQuery = 
            `UPDATE TB_LINKED_SERVER SET 
                ServerIpAddr = @server_ip
                , ServerDesc = @desc
            WHERE ServerId = @server_id`;
        
        try {
            const que = await pool;
            let transaction = new sql.Transaction(que);
            await transaction.begin().then(async() => {
                const request = new sql.Request(transaction);
                request.input('server_ip', server_ip);
                request.input('desc', desc);
                request.input('server_id', server_id);
                request.input('expired_at', expired_at);
                request.input('policy_lookup_yn', policy_lookup_yn);
                request.input('file_info_lookup_yn', file_info_lookup_yn);
                request.input('apply_label_yn', apply_label_yn);
                request.input('release_label_yn', release_label_yn);
                request.input('encryption_file_yn', encryption_file_yn);
                request.input('decryption_file_yn', decryption_file_yn);
                request.input('api_id', api_id);
                request.query(sqlQuery, async (err, result)=>{
                    if (err) {
                        console.error('Error executing query:', err.originalError);
                        res.status(500).send(err.originalError.info.message);
                    }
                    if (result && result.rowsAffected && result.rowsAffected[0] > 0) {
                        const apiSqlQuery = 
                            `UPDATE TB_LINKED_API_KEY SET
                                ExpiredAt = @expired_at, 
                                policyLookupYn = @policy_lookup_yn, 
                                fileInfoLookupYn = @file_info_lookup_yn, 
                                applyLabelYn = @apply_label_yn, 
                                releaseLabelYn = @release_label_yn, 
                                encryptionFileYn = @encryption_file_yn, 
                                decryptionFileYn = @decryption_file_yn 
                            WHERE id = @api_id`;
                        let result2 = await request.query(apiSqlQuery);
                        if (result2 && result2.rowsAffected && result2.rowsAffected[0] > 0) {
                            transaction.commit();
                            resultObj.success = 'S';
                            resultObj.message = '요청하신 서버 정보가 수정 되었습니다.';
                        }
                        else {
                            resultObj.message = '서버 수정 작업 중 오류가 발생하였습니다.';
                            transaction.rollback();
                        }
                        return res.json(resultObj);
                    }
                    else {
                        resultObj.message = '서버 수정 작업 중 오류가 발생하였습니다.';
                        transaction.rollback();
                        return res.json(resultObj);
                    }
                });
            })
        }
        catch (error) {
            console.log(error);
            resultObj.message = '서버 수정 작업 중 오류가 발생하였습니다.';
            resultObj.error = error;
            return res.json(resultObj);
        }
    }
    else {
        res.json(resultObj);
    }
});

app.post('/api/deleteServer', async (req, res, next)=>{
    const {ServerId} = req.body;
    const resultObj = { success: 'F', message : '파라미터 정보를 확인해주세요.'};
    if (ServerId) {
        try {
            const que = await pool;
            let transaction = new sql.Transaction(que);
            
            const sqlQuery = 
            `UPDATE TB_LINKED_SERVER SET UseYn = 0, DeletedAt = GETDATE() WHERE ServerId = @ServerId`;
            await transaction.begin().then(async() => {
                const request = new sql.Request(transaction);
                request.input('ServerId', ServerId);
                request.query(sqlQuery, async (err, result)=>{
                    if (err) {
                        console.error('Error executing query:', err.originalError);
                        res.status(500).send(err.originalError.info.message);
                    }
                    if (result && result.rowsAffected && result.rowsAffected[0]) {
                        const apiSqlQuery = 
                            `UPDATE TB_LINKED_API_KEY SET UseYn = 0, DeletedAt = GETDATE() WHERE ServerId = @ServerId`;
                        let result2 = await request.query(apiSqlQuery, ServerId);
                        if (result2 && result2.rowsAffected && result2.rowsAffected[0] > 0) {
                            transaction.commit();
                            resultObj.success = 'S';
                            resultObj.message = '요청하신 서버 정보가 삭제 되었습니다.';
                        }
                        else {
                            resultObj.message = '서버 삭제 작업 중 오류가 발생하였습니다.';
                            transaction.rollback();
                        }
                        return res.json(resultObj);
                    }
                    else {
                        resultObj.message = '서버 삭제 작업 중 오류가 발생하였습니다.';
                        transaction.rollback();
                        return res.json(resultObj);
                    }
                });
            })
        }
        catch (error) {
            console.log(error);
            resultObj.message = '서버 삭제 작업 중 오류가 발생하였습니다.';
            resultObj.error = error;
            return res.json(resultObj);
        }
    }
    else {
        res.json(resultObj);
    }
});

app.post('/api/addApiKey', async (req, res, next)=>{
    const {ServerIpAddr, ServerDesc, SystemId} = req.body;
    const resultObj = { success: 'F', message : '파라미터 정보를 확인해주세요.'};
    if (ServerIpAddr, SystemId) {
        let desc = ServerDesc;
        if (!desc) {
            desc = "";
        }
        const sqlQuery = 
        `INSERT INTO TB_LINKED_API_KEY (SystemId, ServerIpAddr, ServerDesc) VALUES(@SystemId, @ServerIpAddr, @ServerDesc)`;
        const param = {
            ServerIpAddr : ServerIpAddr, 
            ServerDesc : desc, 
            SystemId : SystemId
        }
        const {error, data, success} = await queryHandler(sqlQuery, param);
        if (!success) {
            res.status(500).send(error);
        }
        else {
            if (data && data.rowsAffected && data.rowsAffected[0] > 0) {
                resultObj.message = 'API 키가 추가 되었습니다.';
                resultObj.success = 'S';
            }
            else {
                resultObj.message = 'API 키가 추가 되지 않았습니다.';
            }
        }
    }
    return res.json(resultObj);
});

/**
 * 쿼리 핸들러
 * @param {*} query 쿼리 
 * @param {*} param 쿼리 사용 파라미터
 * @returns 
 */
async function queryHandler(query, param) {
    const resultObj = {success: false, error: null, data: null};
    try {
        const que = await pool;
        const request = que.request();
        if (param) {
            for (let key in param) {
                request.input(key, param[key]);
            }
        }
        const result = await request.query(query);
        if (result) {
            resultObj.data = result;
            resultObj.success = true;
        }
    }
    catch(err) {
        resultObj.error = err;
    }
    return resultObj;
}

app.post('/api/deleteApiKey',async (req, res, next)=>{
    const {ServerId} = req.body;
    const resultObj = { success: 'F', message : '파라미터 정보를 확인해주세요.'};
    if (ServerId) {
        const sqlQuery = 
        `UPDATE TB_LINKED_API_KEY SET UseYn = 0, DeletedAt = GETDATE() WHERE ServerId = @ServerId`;
        const {data, success, error} = await queryHandler(sqlQuery, {ServerId : ServerId});
        if (success && data) {
            if (data.rowsAffected && data.rowsAffected[0] > 0) {
                resultObj.message = 'API 키가 삭제 되었습니다.';
                resultObj.success = 'S';
            }
            else {
                resultObj.message = 'API 키가 삭제 되지 않았습니다.';
            }
        }
        else {
            res.status(500).send(error);
        }
    }
    return res.json(resultObj);
});

app.post('/api/getGatewayLogs', (req, res, next)=>{
    const {page, startDate, endDate, searchType, searchText} = req.body;
    let searchValue = '';
    const pagePerCount = 15;
    const pageSize = 10;
    
    if (page, startDate, endDate) {
        let totalQuery = 
            `SELECT COUNT(*) total FROM TB_AIP_FILE_JOB_LOG WHERE LogDate BETWEEN CONVERT(DATETIME, '${startDate}') and CONVERT(DATETIME, '${endDate}')`;
        if (searchType && searchText) {
            searchValue = ` AND ${searchType} like '%${searchText}%'`;
            if (searchType === 'FileName' || searchType === 'FileOwner') {
                searchValue += ` OR New${searchType} like '%${searchText}%'`;
            }
        }
        totalQuery += searchValue;
        const query = 
            `SELECT 
                ROW_NUMBER() OVER(ORDER BY A.LogDate DESC) AS rownum,
                A.Id id,
                COALESCE(CONVERT(CHAR(19), A.LogDate, 120), '-') as log_date,
                A.ApiId api_id,
                B.ApiName api_name,
                B.ApiDesc api_desc,
                A.FileId file_id,
                A.FileOwner file_owner,
                A.FileName file_name,
                A.FileLabelGuid file_label_guid,
                C.LabelDesc file_label_desc,
                A.FileSize file_size,
                A.NewFileName new_file_name,
                A.NewFileOwner new_file_owner,
                A.NewFileLabelGuid new_file_label_guid,
                D.LabelDesc new_file_label_desc,
                A.NewFileSize new_file_size,
                A.JobOwner job_owner,
                A.JobResult job_result,
                A.JobTime job_time,
                A.JobMessage job_message,
                A.ServerIpAddr server_ip_addr,
                A.ApiGuid api_guid,
                A.ApiKey api_key
            FROM TB_AIP_FILE_JOB_LOG A
            LEFT JOIN TB_AIP_API_INFO B
            ON A.ApiId = B.ApiId
            LEFT JOIN TB_AIP_LABEL C
            ON A.FileLabelGuid = C.LabelGuid
            LEFT JOIN TB_AIP_LABEL D
            ON A.NewFileLabelGuid = D.LabelGuid
            WHERE A.LogDate BETWEEN CONVERT(DATETIME, '${startDate}') and CONVERT(DATETIME, '${endDate}')
            ${searchValue}`;

        paginationList(page, query, totalQuery, pagePerCount, pageSize, res);
    }
    else {
        res.status(500).send('파라미터 정보를 찾을 수 없습니다.');
    }
});

class Pagination {
    constructor(page, pageSize, pagePerCount, totalCount) {
        this.page = parseInt(page) || 1;
        this.currentPage = this.page;
        this.pageSize = pageSize;
        this.pagePerCount = pagePerCount;
        this.totalCount = totalCount;
        this.totalPageCount = this.getTotalPageCount(this.totalCount, this.pagePerCount);
        this.startPage = this.getStartPage(this.page, this.pageSize);
        this.endPage = this.getEndPage(this.startPage, this.totalPageCount, this.pageSize);
        this.startRow = this.getStartRow(this.page, this.pagePerCount);
        this.endRow = this.getEndRow(this.startRow, this.pagePerCount, this.totalCount);
        this.prev = this.getPrev(this.pageSize, this.startPage);
        this.next = this.getNext(this.endPage, this.totalPageCount);
        this.list = [];
    }

    getStartPage(page, pageSize) {
        let start = Math.floor(page / pageSize);
        if (page % pageSize === 0) {
            start--;
        }
        return (start * pageSize) + 1;
    }

    getEndPage(startPage, totalPageCount, pageSize) {
        let endPage = (startPage - 1) + pageSize;
        if (endPage > totalPageCount) {
            endPage = totalPageCount;
        }
        return endPage;
    }

    getStartRow(page, pagePerCount) {
        return ((page - 1) * pagePerCount) + 1;
    }

    getEndRow(startRow, pagePerCount, totalCount) {
        let endRow = startRow + pagePerCount - 1;
        if (endRow > totalCount) {
            endRow = totalCount;
        }
        return endRow;
    }

    getPrev(pageSize, startPage) {
        return (pageSize < startPage) ? (startPage - pageSize) : 0;
    }

    getNext(endPage, totalPageCount) {
        return (endPage + 1 <= totalPageCount) ? (endPage + 1 ) : 0;
    }

    getTotalPageCount(totalCount, pagePerCount) {
        return Math.ceil(totalCount * 1.0 / pagePerCount);
    }
}

async function paginationList(page, query, totalQuery, pagePerCount, pageSize, res, param) {
    try {
        let pageNo = 1;
        if (page) {
            pageNo = parseInt(page);
        }
        const que = await pool;
        const request = que.request();
        const result = await request.query(totalQuery);
        if (result && result.recordset && result.recordset.length) {
            const resultObj = new Pagination(pageNo, pageSize, pagePerCount, result.recordset[0].total);

            const sqlQuery = 
                `SELECT A.* FROM (
                    ${query}
                ) A
                WHERE rownum BETWEEN @startRow AND @endRow`;
            
            request.input('startRow', resultObj.startRow);
            request.input('endRow', resultObj.endRow);
            if (param) {
                for (let key in param) {
                    request.input(key, param[key]);
                }
            }

            const result1 = await request.query(sqlQuery);
            if (result1 && result1.recordset) {
                resultObj.list = result1.recordset;
            }

            return res.json(resultObj);
        }
    }
    catch(err) {
        console.log(err);
        res.status(500).send(err);
    }
    
}

app.listen(5000)