o2oa如何调用数据库服务器中其他库表中的数据？

elffkdx

小公司，我将O2OA部署在公司业务系统同一数据库服务器上，请问在设置O2OA审批表单时，如何在表单中调用 同一数据库服务器中其他库表中的字段值？

论坛管理员

参考：

[JavaScript] 纯文本查看 复制代码

/********************
this.entityManager; //实体管理器
this.applications; //访问系统内服务
this.requestText//请求正文
this.request//请求
this.currentPerson//当前用户
this.response//响应对象。通过this.response.setBody(data)设置响应内容
this.organization; //组织访问
this.org; //组织快速访问方法
this.service; //webSerivces客户端
********************/

print("****************mysql********************");
//print(requestText)
var requestJson;
if(typeof (requestText) == "string"){ 
    requestJson = JSON.parse(requestText);    
}

var response={};
var fun = requestJson["fun"];
var dataObj = requestJson["data"];
//var dataObj = JSON.parse(data);
print("fun="+fun);
print("data="+JSON.stringify(dataObj))


function getDayData(day){
    var result = [];
    
    
    return result;
}
function getWeekData(day){
    var result = [];
    
    
    return result;
}

function getMonthData(day){
    var result = [];
    var sql = "select IP,province , date,count(*) from (select IP,date_format(formatDate , '%Y-%c-%d' ) as date,province from statistics where formatDate >= '2021-01-01 00:00:00' and formatDate <='2021-01-31 23:59:59' ORDER BY formatDate DESC) as temp group by IP order by date desc";
    
    return result;
}

function getYearData(day){
    var result = [];
    
    
    return result;
}


var con = null;
function getConnection(){
    try{
        var dm = Java.type("java.sql.DriverManager");
        var Driver = Java.type("com.mysql.cj.jdbc.Driver");
        var dr = new Driver();
        dm.registerDriver(dr);
        var url = "jdbc:mysql://127.0.0.1:3306/statistics?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&useSSL=false";
        var user = "root";
        var password = "123456";
        con = dm.getConnection(url,user,password);
    }catch(e){
        con = null;
    }
    
    return con;
}

con = getConnection();


function getDataFromDB(){
    var result = []
    if(con!==null){
        try{
            var sql = "select IP,province , date,count(*) from (select IP,date_format(formatDate , '%Y-%c-%d' ) as date,province from statistics where formatDate >= '"+(sDate + " 00:00:00")+"' and formatDate <='"+ (eDate + "23:59:59") +"' ORDER BY formatDate DESC) as temp group by IP order by date desc";
            var stmt=con.createStatement();
            if(fun == "getDay"){
                var value = dataObj["value"];
                if(value && value != ""){
                    var rs = stmt.executeQuery("SELECT * FROM statistics where formatDate >= '"+value+" 00:00:00' and formatDate <= '"+value+" 23:59:59'" ) ;
                    while(rs.next()){
                        print(rs.getString("formatDate"))
                    }
                }
            }else if(fun == "getMonth"){
                
            }
        }catch(e){
            print(e)
        }finally{
            if(rs != null)rs.close();
            if(stmt != null)stmt.close();
            if(con != null)con.close();
        }

    }
    return result;
}




if(fun == "getDay"){
    
}else if(fun == "getMonth"){
    response.data = [];
    // data.month = "2020-12-01" ,每月第一天
    var monthDate = new Date(dataObj.month);
    var nowMonth = monthDate.getMonth(); //当前月 
    var nowYear = monthDate.getFullYear(); //当前年 
    //本月的开始时间
    var monthStartDate = new Date(nowYear, nowMonth, 1); 
    //本月的结束时间
    var monthEndDate = new Date(nowYear, nowMonth+1, 0);
    
    //var sDate = monthStartDate.getFullYear()+"-"+(monthStartDate.getMonth() + 1) + "-" + monthStartDate.getDate();
    //var eDate = monthEndDate.getFullYear()+"-"+(monthEndDate.getMonth() + 1) + "-" + monthEndDate.getDate();
    var sDate = formatDate(monthStartDate);
    var eDate = formatDate(monthEndDate);
    
    if(con!=null){
        try{
            var sql = "select IP,province , date,count(*) from (select IP,date_format(formatDate , '%Y-%m-%d' ) as date,province from statistics where formatDate >= '"+(sDate + " 00:00:00")+"' and formatDate <='"+ (eDate + " 23:59:59") +"' ORDER BY formatDate DESC) as temp group by IP order by date desc";    
            //sql = "SELECT * FROM statistics where formatDate >= '2021-01-06 00:00:00' and formatDate <= '2021-1-06 23:59:59'"
            print("sql="+sql);
            var stmt=con.createStatement();
            var rs = stmt.executeQuery(sql) ;
            while(rs.next()){
                var ip = rs.getString("IP");
                var province = rs.getString("province");
                var date = rs.getString("date");
                response.data.push({"ip":ip,"province":province,"date":date});
                
            }
        }catch(e){
            print(e)
        }finally{   
            if(rs != null)rs.close();
            if(stmt != null)stmt.close();
            if(con != null)con.close();
        }
    }
    
    
}


function formatDate(date) {
    //date = new Date(Date.parse(date.replace(/-/g, "/"))); //转换成Data();
    var y = date.getFullYear();
    var m = date.getMonth() + 1;
    m = m < 10 ? '0' + m : m;
    var d = date.getDate();
    d = d < 10 ? ('0' + d) : d;
    return y + '-' + m + '-' + d;
}


JSON.stringify(response)