var model = require('./model');
let oraConfig = require('../application.json').oracle;
var oracledb = require('oracledb');
oracledb.fetchAsBuffer = [oracledb.BLOB];
oracledb.fetchAsString = [oracledb.CLOB];
var pool;
//创建连接池
var poolconfig = Object.assign(oraConfig, {});
poolconfig.poolMax = 20;
poolconfig.queueRequests = true;
oracledb.createPool(poolconfig, function (err, pl) {
    pool = pl;
});

//普通sql语句执行
var executesql = function (sqlstr, callback, type) {
    oracledb.getConnection(
        oraConfig,
        function (err, connection) {
            if (err) {
                callback({
                    resultStatus: model.resultStatus.error(err.message)
                });
            } else {
                connection.execute(sqlstr, (err, result) => {
                    if (err) {
                        type && connection.rollback();
                        callback({
                            resultStatus: model.resultStatus.error(err.message)
                        });
                    } else {
                        type && connection.commit();
                        callback({
                            resultStatus: model.resultStatus.success,
                            data: (type ? result.rowsAffected : createObject(result))
                        });
                    }
                    connection.close();
                });
            }
        }
    );
}
//不带返回错误结果的普通sql语句执行，适用于嵌套查询数据
var executesqlwithoutstatus = function (sqlstr, callback, type) {
    oracledb.getConnection(
        oraConfig,
        function (err, connection) {
            if (err) {
                callback();
            } else {
                connection.execute(sqlstr, (err, result) => {
                    connection.commit();
                    if (err) {
                        callback();
                    } else {
                        callback((type ? result.rowsAffected : createObject(result)));
                    }
                    connection.close();
                });
            }
        }
    );
}
//执行oracle函数
var executefun = function (fun_name, bindvars, cursor, callback) {
    oracledb.getConnection(
        oraConfig,
        function (err, connection) {
            if (err) {
                callback({
                    resultStatus: model.resultStatus.error(err.message)
                });
            } else {
                var plsql = "begin :ret :=";
                var temp = "";
                for (var item in bindvars) {
                    temp = temp + ":" + item + ",";
                }
                temp = temp.substr(0, temp.length - 1);
                plsql = plsql + fun_name + "(" + temp + "); end;";
                if (cursor) {
                    bindvars["ret"] = { type: oracledb.CURSOR, dir: oracledb.BIND_OUT };
                } else {
                    bindvars["ret"] = { type: oracledb.STRING, dir: oracledb.BIND_OUT };
                }
                connection.execute(plsql, bindvars, (err, result) => {
                    if (err) {
                        callback({
                            resultStatus: model.resultStatus.error(err.message)
                        });
                        connection.close();
                    } else {
                        if (cursor) {
                            fetchRowsFromRS(result.outBinds.ret, 0, resx => {
                                connection.close();
                                callback({
                                    resultStatus: model.resultStatus.success,
                                    data: resx
                                });
                                connection.close();
                            });
                        } else {
                            callback({
                                resultStatus: model.resultStatus.success,
                                data: result.outBinds.ret
                            });
			     connection.close();
                        }
                        
                    }
                });
            }
        }
    );
}
//执行oracle函数
var executefunwithoutstatus = function (fun_name, bindvars, cursor, callback) {
    oracledb.getConnection(
        oraConfig,
        function (err, connection) {
            if (err) {
                callback({
                    resultStatus: model.resultStatus.error(err.message)
                });
              //  connection.close();
            } else {
                var plsql = "begin :ret :=";
                var temp = "";
                for (var item in bindvars) {
                    temp = temp + ":" + item + ",";
                }
                temp = temp.substr(0, temp.length - 1);
                plsql = plsql + fun_name + "(" + temp + "); end;";
                if (cursor) {
                    bindvars["ret"] = { type: oracledb.CURSOR, dir: oracledb.BIND_OUT };
                } else {
                    bindvars["ret"] = { type: oracledb.STRING, dir: oracledb.BIND_OUT };
                }
                connection.execute(plsql, bindvars, (err, result) => {
                    if (err) {
                        callback();
                //        connection.close();
                    } else {
                        if (cursor) {
                            fetchRowsFromRS(result.outBinds.ret, 0, resx => {
                                callback(resx);
                            });
                        } else {
                            callback(result.outBinds.ret);
                        }
                        connection.close();
                    }
                });
            }
        }
    );
}
//测试
var test = function (fuc_name, bindvars, cursor) {
    return new Promise(function (resolve, reject) {
        oracledb.getConnection(
            oraConfig,
            function (err, connection) {
                if (err) {
                    reject({
                        resultStatus: model.resultStatus.error(err.message)
                    });
                }
                var plsql = "begin :ret :=";
                var temp = ""
                for (var item in bindvars) {
                    temp = temp + ":" + item + ",";
                }
                temp = temp.substr(0, temp.length - 1);
                plsql = plsql + fuc_name + "(" + temp + "); end;";
                if (cursor) {
                    bindvars["ret"] = { type: oracledb.CURSOR, dir: oracledb.BIND_OUT };
                } else {
                    bindvars["ret"] = { type: oracledb.STRING, dir: oracledb.BIND_OUT };
                }
                connection.execute(plsql, bindvars, (err, result) => {
                    if (err) {
                        reject({
                            resultStatus: model.resultStatus.error(err.message)
                        });
                    } else {
                        resolve({
                            resultStatus: model.resultStatus.success,
                            data: result.outBinds.ret
                        });
                    }
                });
            });
    });

}
//批量执行函数,共用同一个从连接池获取的连接
var executefunByBatch = function (connection,fun_name, bindvars, cursor, callback) {
    var plsql = "begin :ret :=";
    var temp = "";
    for (var item in bindvars) {
        temp = temp + ":" + item + ",";
    }
    temp = temp.substr(0, temp.length - 1);
    plsql = plsql + fun_name + "(" + temp + "); end;";
    if (cursor) {
        bindvars["ret"] = { type: oracledb.CURSOR, dir: oracledb.BIND_OUT };
    } else {
        bindvars["ret"] = { type: oracledb.STRING, dir: oracledb.BIND_OUT };
    }
    connection.execute(plsql, bindvars, (err, result) => {
        if (err) {
            callback({
                resultStatus: model.resultStatus.error(err.message)
            });
        } else {
            if (cursor) {
                fetchRowsFromRS(result.outBinds.ret, 0, resx => {
                    callback({
                        resultStatus: model.resultStatus.success,
                        data: resx
                    });
                });
            } else {
                callback({
                    resultStatus: model.resultStatus.success,
                    data: result.outBinds.ret
                });
            }
        }
    });
}
//获取连接
var getConnectionFromPool = function (callback) {
    pool.getConnection(
        function (err, connection) { 
            if (err) {
                callback(null);
            }else{
                callback(connection);
            }
        }
    );
}

//执行函数后，根据游标获取返回值
function fetchRowsFromRS(resultSet, numRows, callback) {
    var cols=resultSet.metaData;
    resultSet.getRows( // get numRows rows
        100,//数据量少，（党委和年纪）
        function (err, rows) {
            var temp = [];
            if (!err) {
                rows.map(item => {
                    var obj = {};
                    for (var i = 0; i < cols.length; i++) {
                        obj[cols[i].name.toLowerCase()] = item[i];
                    }
                    temp.push(obj);
                });
            }
            resultSet.close();
            callback(temp);
        }
    )
}

//执行普通sql后，根据结果构建json对象
function createObject(result) {
    var temp = [];
    result.rows.map(item => {
        var obj = {};
        for (var i = 0; i < result.metaData.length; i++) {
            obj[result.metaData[i].name.toLowerCase()] = item[i];
        }
        temp.push(obj);
    });
    return temp;
}

module.exports = {
    executesql: executesql,
    executefun: executefun,
    executesqlwithoutstatus: executesqlwithoutstatus,
    executefunwithoutstatus:executefunwithoutstatus,
    executefunByBatch: executefunByBatch,
    getConnectionFromPool:getConnectionFromPool
};
