# app_script_history

id: 64b8ffe78b10ede23ae615cb
scriptid: 649aa52d99329f5ca7253c1c
tablename: app_script
clientip: 0:0:0:0:0:0:0:1
username: sa
createdate: 2023.07.20 18:49:34.000
script:
/**
 * 脚本中心的脚本，以 main函数为执行入口，相关的参数放在arg中，它是一个JSON字符串化后的数据
 *
 */
function main(arg) {
    println(arg);
    var param = JSON.parse(arg);
    var gguid = newGUID();
    var moduleid = 'dy';//模块ID
    var username = getProperty( "/API创建调阅流程的起草人",  param.creator || "api");
    var userid = getUserIdByName(username);
    var leaderid= 18; //审核人ID
    var billno = newBillNo('', 'dzpz_dy', 'billno', '{11111111}', 'billdate', newDate(), "");
    var dzpz_dy_ds = newDataStore("", "select * from  dzpz_dy");
    dzpz_dy_ds.setUpdateProperty("dzpz_dy", "id", "*", "id");
    dzpz_dy_ds.insertRow(0);
    dzpz_dy_ds.setValue(0, {
        id: gguid, gguid: gguid, userid: userid, username: getStringFromSQL("select showname from app_user where id="+ userid )
         , billdate: newDate(), billno: billno,
        reader: param.dyr, 
        leaderid: leaderid , 
        summary: param.dyyy, corpid: '00',
        needwatermark: 0,//不打水印 
        filetype: 'zip', // 
        email: param.email,
        moredata: JSON.stringify(param.callback_param),
        action_after_pack:'sendMail'
    });

    var dzpz_dymx_ds = newDataStore("", "select * from dzpz_dymx");
    dzpz_dymx_ds.setUpdateProperty('dzpz_dymx', 'id', '*', "id");
    var pzh = param.pzh  ;
    if( pzh==undefined) pzh =[];
    for (var i = 0; i < pzh.length; i++) {
        var row = dzpz_dymx_ds.insertRow(9999999);
       // var code= getStringFromSQL("select code from pzdjex b  where  pzh = '"+pzh[i]+"'");
        dzpz_dymx_ds.setValue(row, { id: newGUID(), gguid: gguid, inner_xh: i + 1, pzh: pzh[i]    });
    }

    var err = updateAll(dzpz_dy_ds, dzpz_dymx_ds);
    if (err != '') return { success: false, messager: err };


    var corpid = getStringFromSQL("select corpid from dzpz_dy where id='" + gguid + "'");
    
    runSQL(""," update dzpz_dymx  set   code= ( select min(b.code) as code  from pzdjex b  where  b.pzh =dzpz_dymx.pzh ) where  dzpz_dymx.gguid='" + gguid + "' ",true );
    var sqls = ["update dzpz_dymx  set bz=null,pz_id= (select max(id) from dzpz_pz where dzpz_pz.pzh= dzpz_dymx.pzh and dzpz_pz.corpid='" + corpid + "' ) where gguid='" + gguid + "' ",
                "update dzpz_dymx  set bz='凭证号不存在' where  gguid='" + gguid + "' and not exists (select dzpz_pz.id from dzpz_pz where  dzpz_pz.pzh= dzpz_dymx.pzh  and dzpz_pz.corpid='" + corpid + "')  " 
             
    ];
    runSQL("", sqls); 


    var config = {
        moduleid: moduleid,//凭证调阅模块代码或ID,
        gguid: gguid,
        flow: 'dy', //工作流的代码或ID
        userid: userid, //发起人ID
        subject: `${param.dyr || ''}因 [${param.dyyy || ''}] 调阅凭证`


    };

    var ret = newWork(config);

    if (!ret.success) return ret;

    //自动递交给初审
/*
    var t = taskInfoForWork('dy', gguid);

    //将任务递交下去，如果任务需要签收，则先签收     
    for (var i = 0; i < t.taskList.length; i++) {
        var task = t.taskList[i];

        //可以递交，那么递交下去
        if (task.canSubmit) println(submitWork(moduleid, gguid, task.taskId));
        //需要先签收 ，那么以第一个候选人签收它
        if (task.canClaim) claimWork(moduleid, gguid, task.taskId, task.candidateUserList[0].userId);

    }
*/

    ret.id = gguid;
    ret.billno= billno;
    return {success:true , id: gguid , content:{billno:billno}  };


}


id: 64c7b3429932ab2156cd1c63
scriptid: 60d095db993254220bd8519f
tablename: app_script
clientip: 60.186.34.220
username: sa
createdate: 2023.07.31 21:18:52.000
script:
//后端导入excel数据包
function backend_importExcelData(excelData) {
    var ret = {
        'success': false,
        'message': ''
    }
    // return { success: false, message: excelData }
    var dsn = '';
    var masterTable = 'pzdj';
    var tableName = 'pzdjex';
    //ret.message="select id,userid,verifiername from pzdj where gguid='" + currentGUID() + "'";
    //return ret;

    runSQL("", "alter table  pzdjex   modify  bz  varchar(4000) ");
    runSQL("", "alter table  pzdjex   modify  rkdh  varchar(1000) ");
    

    var ds = newDataStore(dsn, "select id,userid,verifiername ,corpid from pzdj where gguid='" + currentGUID() + "'");
    ds.setOnceRetrieveCount(1);
    ds.retrieve('');
    if (ds.getRowCount() == 0) {
        ret.message = '请保存单据后再操作此功能!';
        return ret;
    }

    var corpid = ds.getString(0, "corpid");

    // if (ds.getString(0, "userid") != currentUserId()) {
    //     ret.message = '由于您不是制单人，不允许对当前单据做导入操作!';
    //     return ret;
    // }
    if (ds.getString(0, "verifiername") != '') {
        ret.message = '单据已审核，不允许导入操作!';
        return ret;
    }

    runSQL(dsn, "delete from " + tableName + " where gguid='" + currentGUID() + "'");

    //**********************************************
    //导入的标准功能
    var colLabel2ColName = {
        "年度": "y",
        "调整期间": "m",
        "期间": "m",
        "凭证号": "pzh",
        "调整凭证号": "pzh",
        "会计凭证号": "pzh",
        "凭证日期": "pzrq",
        "会计科目代码": "kmdm",
        "会计科目名称": "kmmc",
        "核算账号编码": "code",
        "借方金额": "jfje",
        "贷方金额": "dfje",
        "财会支出明细编码": "ckzcmxdm",
        "财会支出明细名称": "ckzcmxmc",
        "预会支出明细编码": "ykzcmxdm",
        "预会支出明细名称": "ykzcmxmc",
        "入库单号": "rkdh",
        "是否已过账": "posted",
        "摘要": "bz",
        "制单人": "zdr",
        "经办人": "jbr"
    }

    "年度\t调整期间\t凭证号\t凭证日期\t会计科目代码\t会计科目名称\t财会支出明细编码\t财会支出明细名称\t预会支出明细编码\t预会支出明细名称\t核算账号编码\t入库单号\t是否已过账\t摘要\t制单人";


    //var excelSheet;
    //for (var p in excelData) {
    //    excelSheet = excelData[p];
    //}

    var excelSheet = excelData;

    //分析字段对应关系
    var excelColIndex2ColName = {}; //excel 中的列号对应的中文题头
    var excelColName2ColIndex = {}; //中文题头对应的excel中的列号
    // var colLabel2ColName = {}; //中文题头对应到表的字段名


    var duplicateLabelCount = {};
    var colCount = 50; //最多几个列

    var headRow = 0; //题头在第几行
    var dataStartRow = 1; // 数据行从第几行开始
    //有时可能表头有多行合并，所以要检测一下
    for (var i = 0; i < colCount; i++) {
        var v = trim(sys_excelSafeValue(excelSheet, headRow, i));
        if (v == '')
            continue;
        if (duplicateLabelCount[v] == undefined) {
            duplicateLabelCount[v] = 0;
        }
        duplicateLabelCount[v] = duplicateLabelCount[v] + 1;

        if (duplicateLabelCount[v] > 1)
            v = v + duplicateLabelCount[v];
        //拼上后缀

        excelColIndex2ColName['' + i] = v;
        excelColName2ColIndex[v] = '' + i;
    }

    println(JSON.toString(excelColName2ColIndex));
     println(JSON.toString(excelColIndex2ColName));

    var RC = excelSheet.length;

    var data = [];
    for (var row = dataStartRow; row < RC; row++) {
        if (sys_excelSafeValue(excelSheet, row, 1) == null) break; //空行，直接结束
        var oneRowData = {};
        data.push(oneRowData);
        for (var label in excelColName2ColIndex) {
            var excelCol = excelColName2ColIndex[label];
            var dbCol = colLabel2ColName[label];
            if (dbCol != null) {
                oneRowData[dbCol] = sys_excelSafeValue(excelSheet, row, excelCol);
            }
        }

    }
    //println(JSON.toString(data));

    pushMessage("dr_info", "开始导入...", currentUserId());

    var gguid = currentGUID();
    var userid = currentUserId();
    var username = currentUserName();
    var lastmodifieddate = newDate();
    // println('import_ph:' + import_ph);



    var ds = newDataStore(dsn, "select * from " + tableName);
    ds.setUpdateProperty(tableName, "id", "*", "id");
    for (var i = 0; i < data.length; i++) {
        var row = ds.insertRow(9999999);
        pushMessage("dr_info", "导入进度：" + (i + 1) + "/" + data.length, currentUserId());

        var rowData = data[i];
        for (var col in rowData) {
            var value = rowData[col];
            ds.setValue(row, col, value);
        }
        ds.setValue(row, "id", newGuid());
        ds.setValue(row, "gguid", gguid);
        ds.setValue(row, "userid", userid);
        ds.setValue(row, "username", username);
        ds.setValue(row, "lastmodifieddate", lastmodifieddate);
        ds.setValue(row, "corpid", corpid);

        if (ds.getRowCount() == 1000) {
            if (!ds.update(true)) {
                var errInfo = ds.getError().getMessage();
                println(errInfo);
                pushMessage("dr_info", "异常：" + errInfo, currentUserId());
                return;
            }
            ds.reset();
        }
    }

    pushMessage("dr_info", "正在写入数据库...", currentUserId());

    ds.update(true);
    // $alert('导入完成');
    ret.success = true;
    ret.message = ds.getString(0, "userid");

    //更新起始记账期和截止记账期
    var master_ds = newDataStore(dsn, "select * from " + masterTable);
    master_ds.setUpdateProperty(masterTable, "id", "*", "id");
    master_ds.retrieve("gguid='" + currentGUID() + "'");
    master_ds.setValue(0, "jzq_start", getStringFromSQL2(dsn, "select min(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.setValue(0, "jzq_end", getStringFromSQL2(dsn, "select max(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.update(true)

    pushMessage('dr_info', 'over', currentUserId());

    return ret;
}

function sys_excelSafeValue(sheet, row, col) {
    if (row > sheet.length - 1) return null;
    var dataRow = sheet[row];
    if (dataRow == null) return null;
    if (col > dataRow.length - 1) return null;
    return dataRow[col];

}


id: 64c7b3429932ab2156cd1c6c
scriptid: 60d095db993254220bd8519f
tablename: app_script
clientip: 60.186.34.220
username: sa
createdate: 2023.07.31 21:19:13.000
script:
//后端导入excel数据包
function backend_importExcelData(excelData) {
    var ret = {
        'success': false,
        'message': ''
    }
    // return { success: false, message: excelData }
    var dsn = '';
    var masterTable = 'pzdj';
    var tableName = 'pzdjex';
    //ret.message="select id,userid,verifiername from pzdj where gguid='" + currentGUID() + "'";
    //return ret;

    runSQL("", "alter table  pzdjex   modify  bz  varchar(4000) ");
    runSQL("", "alter table  pzdjex   modify  rkdh  varchar(1000) ");
    

    var ds = newDataStore(dsn, "select id,userid,verifiername ,corpid from pzdj where gguid='" + currentGUID() + "'");
    ds.setOnceRetrieveCount(1);
    ds.retrieve('');
    if (ds.getRowCount() == 0) {
        ret.message = '请保存单据后再操作此功能!';
        return ret;
    }

    var corpid = ds.getString(0, "corpid");

    // if (ds.getString(0, "userid") != currentUserId()) {
    //     ret.message = '由于您不是制单人，不允许对当前单据做导入操作!';
    //     return ret;
    // }
    if (ds.getString(0, "verifiername") != '') {
        ret.message = '单据已审核，不允许导入操作!';
        return ret;
    }

    runSQL(dsn, "delete from " + tableName + " where gguid='" + currentGUID() + "'");

    //**********************************************
    //导入的标准功能
    var colLabel2ColName = {
        "年度": "y",
        "调整期间": "m",
        "期间": "m",
        "凭证号": "pzh",
        "调整凭证号": "pzh",
        "会计凭证号": "pzh",
        "凭证日期": "pzrq",
        "会计科目代码": "kmdm",
        "会计科目名称": "kmmc",
        "核算账号编码": "code",
        "借方金额": "jfje",
        "贷方金额": "dfje",
        "财会支出明细编码": "ckzcmxdm",
        "财会支出明细名称": "ckzcmxmc",
        "预会支出明细编码": "ykzcmxdm",
        "预会支出明细名称": "ykzcmxmc",
        "入库单号": "rkdh",
        "是否已过账": "posted",
        "摘要": "bz",
        "制单人": "zdr",
        "经办人": "jbr"
    }

    "年度\t调整期间\t凭证号\t凭证日期\t会计科目代码\t会计科目名称\t财会支出明细编码\t财会支出明细名称\t预会支出明细编码\t预会支出明细名称\t核算账号编码\t入库单号\t是否已过账\t摘要\t制单人";


    //var excelSheet;
    //for (var p in excelData) {
    //    excelSheet = excelData[p];
    //}

    var excelSheet = excelData;

    //分析字段对应关系
    var excelColIndex2ColName = {}; //excel 中的列号对应的中文题头
    var excelColName2ColIndex = {}; //中文题头对应的excel中的列号
    // var colLabel2ColName = {}; //中文题头对应到表的字段名


    var duplicateLabelCount = {};
    var colCount = 50; //最多几个列

    var headRow = 0; //题头在第几行
    var dataStartRow = 1; // 数据行从第几行开始
    //有时可能表头有多行合并，所以要检测一下
    for (var i = 0; i < colCount; i++) {
        var v = trim(sys_excelSafeValue(excelSheet, headRow, i));
        if (v == '')
            continue;
        if (duplicateLabelCount[v] == undefined) {
            duplicateLabelCount[v] = 0;
        }
        duplicateLabelCount[v] = duplicateLabelCount[v] + 1;

        if (duplicateLabelCount[v] > 1)
            v = v + duplicateLabelCount[v];
        //拼上后缀

        excelColIndex2ColName['' + i] = v;
        excelColName2ColIndex[v] = '' + i;
    }

    println(JSON.toString(excelColName2ColIndex));
     println(JSON.toString(excelColIndex2ColName));

    var RC = excelSheet.length;

println("excel数据行数："+RC);
    var data = [];
    for (var row = dataStartRow; row < RC; row++) {
        if (sys_excelSafeValue(excelSheet, row, 1) == null) break; //空行，直接结束
        var oneRowData = {};
        data.push(oneRowData);
        for (var label in excelColName2ColIndex) {
            var excelCol = excelColName2ColIndex[label];
            var dbCol = colLabel2ColName[label];
            if (dbCol != null) {
                oneRowData[dbCol] = sys_excelSafeValue(excelSheet, row, excelCol);
            }
        }

    }
    //println(JSON.toString(data));

    pushMessage("dr_info", "开始导入...", currentUserId());

    var gguid = currentGUID();
    var userid = currentUserId();
    var username = currentUserName();
    var lastmodifieddate = newDate();
    // println('import_ph:' + import_ph);



    var ds = newDataStore(dsn, "select * from " + tableName);
    ds.setUpdateProperty(tableName, "id", "*", "id");
    for (var i = 0; i < data.length; i++) {
        var row = ds.insertRow(9999999);
        pushMessage("dr_info", "导入进度：" + (i + 1) + "/" + data.length, currentUserId());

        var rowData = data[i];
        for (var col in rowData) {
            var value = rowData[col];
            ds.setValue(row, col, value);
        }
        ds.setValue(row, "id", newGuid());
        ds.setValue(row, "gguid", gguid);
        ds.setValue(row, "userid", userid);
        ds.setValue(row, "username", username);
        ds.setValue(row, "lastmodifieddate", lastmodifieddate);
        ds.setValue(row, "corpid", corpid);

        if (ds.getRowCount() == 1000) {
            if (!ds.update(true)) {
                var errInfo = ds.getError().getMessage();
                println(errInfo);
                pushMessage("dr_info", "异常：" + errInfo, currentUserId());
                return;
            }
            ds.reset();
        }
    }

    pushMessage("dr_info", "正在写入数据库...", currentUserId());

    ds.update(true);
    // $alert('导入完成');
    ret.success = true;
    ret.message = ds.getString(0, "userid");

    //更新起始记账期和截止记账期
    var master_ds = newDataStore(dsn, "select * from " + masterTable);
    master_ds.setUpdateProperty(masterTable, "id", "*", "id");
    master_ds.retrieve("gguid='" + currentGUID() + "'");
    master_ds.setValue(0, "jzq_start", getStringFromSQL2(dsn, "select min(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.setValue(0, "jzq_end", getStringFromSQL2(dsn, "select max(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.update(true)

    pushMessage('dr_info', 'over', currentUserId());

    return ret;
}

function sys_excelSafeValue(sheet, row, col) {
    if (row > sheet.length - 1) return null;
    var dataRow = sheet[row];
    if (dataRow == null) return null;
    if (col > dataRow.length - 1) return null;
    return dataRow[col];

}


id: 64c7b3429932ab2156cd1c75
scriptid: 60d095db993254220bd8519f
tablename: app_script
clientip: 60.186.34.220
username: sa
createdate: 2023.07.31 21:32:41.000
script:
//后端导入excel数据包
function backend_importExcelData(excelData) {
    var ret = {
        'success': false,
        'message': ''
    }
    // return { success: false, message: excelData }
    var dsn = '';
    var masterTable = 'pzdj';
    var tableName = 'pzdjex';
    //ret.message="select id,userid,verifiername from pzdj where gguid='" + currentGUID() + "'";
    //return ret;

    runSQL("", "alter table  pzdjex   modify  bz  varchar(4000) ");
    runSQL("", "alter table  pzdjex   modify  rkdh  varchar(1000) ");


    var ds = newDataStore(dsn, "select id,userid,verifiername ,corpid from pzdj where gguid='" + currentGUID() + "'");
    ds.setOnceRetrieveCount(1);
    ds.retrieve('');
    if (ds.getRowCount() == 0) {
        ret.message = '请保存单据后再操作此功能!';
        return ret;
    }

    var corpid = ds.getString(0, "corpid");

    // if (ds.getString(0, "userid") != currentUserId()) {
    //     ret.message = '由于您不是制单人，不允许对当前单据做导入操作!';
    //     return ret;
    // }
    if (ds.getString(0, "verifiername") != '') {
        ret.message = '单据已审核，不允许导入操作!';
        return ret;
    }

    runSQL(dsn, "delete from " + tableName + " where gguid='" + currentGUID() + "'");

    //**********************************************
    //导入的标准功能
    var colLabel2ColName = {
        "年度": "y",
        "调整期间": "m",
        "期间": "m",
        "凭证号": "pzh",
        "调整凭证号": "pzh",
        "会计凭证号": "pzh",
        "凭证日期": "pzrq",
        "会计科目代码": "kmdm",
        "会计科目名称": "kmmc",
        "核算账号编码": "code",
        "借方金额": "jfje",
        "贷方金额": "dfje",
        "财会支出明细编码": "ckzcmxdm",
        "财会支出明细名称": "ckzcmxmc",
        "预会支出明细编码": "ykzcmxdm",
        "预会支出明细名称": "ykzcmxmc",
        "入库单号": "rkdh",
        "是否已过账": "posted",
        "摘要": "bz",
        "制单人": "zdr",
        "经办人": "jbr"
    }

    "年度\t调整期间\t凭证号\t凭证日期\t会计科目代码\t会计科目名称\t财会支出明细编码\t财会支出明细名称\t预会支出明细编码\t预会支出明细名称\t核算账号编码\t入库单号\t是否已过账\t摘要\t制单人";


    //var excelSheet;
    //for (var p in excelData) {
    //    excelSheet = excelData[p];
    //}

    var excelSheet = excelData;

    //分析字段对应关系
    var excelColIndex2ColName = {}; //excel 中的列号对应的中文题头
    var excelColName2ColIndex = {}; //中文题头对应的excel中的列号
    // var colLabel2ColName = {}; //中文题头对应到表的字段名


    var duplicateLabelCount = {};
    var colCount = 50; //最多几个列

    var headRow = 0; //题头在第几行
    var dataStartRow = 1; // 数据行从第几行开始
    //有时可能表头有多行合并，所以要检测一下
    for (var i = 0; i < colCount; i++) {
        var v = trim(sys_excelSafeValue(excelSheet, headRow, i));
        if (v == '')
            continue;
        if (duplicateLabelCount[v] == undefined) {
            duplicateLabelCount[v] = 0;
        }
        duplicateLabelCount[v] = duplicateLabelCount[v] + 1;

        if (duplicateLabelCount[v] > 1)
            v = v + duplicateLabelCount[v];
        //拼上后缀

        excelColIndex2ColName['' + i] = v;
        excelColName2ColIndex[v] = '' + i;
    }

    println(JSON.toString(excelColName2ColIndex));
    println(JSON.toString(excelColIndex2ColName));

    var RC = excelSheet.length;

    println("excel数据行数：" + RC);
    var data = [];
    for (var row = dataStartRow; row < RC; row++) {
        if (sys_excelSafeValue(excelSheet, row, 1) == null) break; //空行，直接结束
        var oneRowData = {};
        data.push(oneRowData);
        for (var label in excelColName2ColIndex) {
            var excelCol = excelColName2ColIndex[label];
            var dbCol = colLabel2ColName[label];
            if (dbCol != null) {
                oneRowData[dbCol] = sys_excelSafeValue(excelSheet, row, excelCol);
            }
        }

    }
    //println(JSON.toString(data));

    pushMessage("dr_info", "开始导入...", currentUserId());

    var gguid = currentGUID();
    var userid = currentUserId();
    var username = currentUserName();
    var lastmodifieddate = newDate();
    // println('import_ph:' + import_ph);



    var ds = newDataStore(dsn, "select * from " + tableName);
    ds.setUpdateProperty(tableName, "id", "*", "id");
    for (var i = 0; i < data.length; i++) {
        var row = ds.insertRow(9999999);
        pushMessage("dr_info", "导入进度：" + (i + 1) + "/" + data.length, currentUserId());

        var rowData = data[i];
        for (var col in rowData) {
            var value = rowData[col];
            ds.setValue(row, col, value);
        }
        ds.setValue(row, "id", newGuid());
        ds.setValue(row, "gguid", gguid);
        ds.setValue(row, "userid", userid);
        ds.setValue(row, "username", username);
        ds.setValue(row, "lastmodifieddate", lastmodifieddate);
        ds.setValue(row, "corpid", corpid);

        if (ds.getRowCount() == 1000) {
            if (!ds.update(true)) {
                var errInfo = ds.getError().getMessage();
                println(errInfo);
                pushMessage("dr_info", "异常：" + errInfo, currentUserId());
                return;
            }
            ds.reset();
        }
    }

    pushMessage("dr_info", "正在写入数据库...", currentUserId());

    ds.update(true);
    // $alert('导入完成');
    ret.success = true;
    ret.message = ds.getString(0, "userid");

    //更新起始记账期和截止记账期
    var master_ds = newDataStore(dsn, "select * from " + masterTable);
    master_ds.setUpdateProperty(masterTable, "id", "*", "id");
    master_ds.retrieve("gguid='" + currentGUID() + "'");
    master_ds.setValue(0, "jzq_start", getStringFromSQL2(dsn, "select min(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.setValue(0, "jzq_end", getStringFromSQL2(dsn, "select max(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.update(true)

    pushMessage('dr_info', 'over', currentUserId());

    return ret;
}

function sys_excelSafeValue(sheet, row, col) {
    if (row > sheet.length - 1) return null;
    var dataRow = sheet[row];
    if (dataRow == null) return null;
    if (col > dataRow.length - 1) return null;
    return dataRow[col];

}


id: 64c7b3429932ab2156cd1c7e
scriptid: 60d095db993254220bd8519f
tablename: app_script
clientip: 60.186.34.220
username: sa
createdate: 2023.07.31 21:32:59.000
script:
//后端导入excel数据包
function backend_importExcelData(excelData) {
    var ret = {
        'success': false,
        'message': ''
    }
    // return { success: false, message: excelData }
    var dsn = '';
    var masterTable = 'pzdj';
    var tableName = 'pzdjex';
    //ret.message="select id,userid,verifiername from pzdj where gguid='" + currentGUID() + "'";
    //return ret;

    println("1111");
    runSQL("", "alter table  pzdjex   modify  bz  varchar(4000) ");
    runSQL("", "alter table  pzdjex   modify  rkdh  varchar(1000) ");


    var ds = newDataStore(dsn, "select id,userid,verifiername ,corpid from pzdj where gguid='" + currentGUID() + "'");
    ds.setOnceRetrieveCount(1);
    ds.retrieve('');
    if (ds.getRowCount() == 0) {
        ret.message = '请保存单据后再操作此功能!';
        return ret;
    }

    var corpid = ds.getString(0, "corpid");

    // if (ds.getString(0, "userid") != currentUserId()) {
    //     ret.message = '由于您不是制单人，不允许对当前单据做导入操作!';
    //     return ret;
    // }
    if (ds.getString(0, "verifiername") != '') {
        ret.message = '单据已审核，不允许导入操作!';
        return ret;
    }

    runSQL(dsn, "delete from " + tableName + " where gguid='" + currentGUID() + "'");

    //**********************************************
    //导入的标准功能
    var colLabel2ColName = {
        "年度": "y",
        "调整期间": "m",
        "期间": "m",
        "凭证号": "pzh",
        "调整凭证号": "pzh",
        "会计凭证号": "pzh",
        "凭证日期": "pzrq",
        "会计科目代码": "kmdm",
        "会计科目名称": "kmmc",
        "核算账号编码": "code",
        "借方金额": "jfje",
        "贷方金额": "dfje",
        "财会支出明细编码": "ckzcmxdm",
        "财会支出明细名称": "ckzcmxmc",
        "预会支出明细编码": "ykzcmxdm",
        "预会支出明细名称": "ykzcmxmc",
        "入库单号": "rkdh",
        "是否已过账": "posted",
        "摘要": "bz",
        "制单人": "zdr",
        "经办人": "jbr"
    }

    "年度\t调整期间\t凭证号\t凭证日期\t会计科目代码\t会计科目名称\t财会支出明细编码\t财会支出明细名称\t预会支出明细编码\t预会支出明细名称\t核算账号编码\t入库单号\t是否已过账\t摘要\t制单人";


    //var excelSheet;
    //for (var p in excelData) {
    //    excelSheet = excelData[p];
    //}

    var excelSheet = excelData;

    //分析字段对应关系
    var excelColIndex2ColName = {}; //excel 中的列号对应的中文题头
    var excelColName2ColIndex = {}; //中文题头对应的excel中的列号
    // var colLabel2ColName = {}; //中文题头对应到表的字段名


    var duplicateLabelCount = {};
    var colCount = 50; //最多几个列

    var headRow = 0; //题头在第几行
    var dataStartRow = 1; // 数据行从第几行开始
    //有时可能表头有多行合并，所以要检测一下
    for (var i = 0; i < colCount; i++) {
        var v = trim(sys_excelSafeValue(excelSheet, headRow, i));
        if (v == '')
            continue;
        if (duplicateLabelCount[v] == undefined) {
            duplicateLabelCount[v] = 0;
        }
        duplicateLabelCount[v] = duplicateLabelCount[v] + 1;

        if (duplicateLabelCount[v] > 1)
            v = v + duplicateLabelCount[v];
        //拼上后缀

        excelColIndex2ColName['' + i] = v;
        excelColName2ColIndex[v] = '' + i;
    }

    println(JSON.toString(excelColName2ColIndex));
    println(JSON.toString(excelColIndex2ColName));

    var RC = excelSheet.length;

    println("excel数据行数：" + RC);
    var data = [];
    for (var row = dataStartRow; row < RC; row++) {
        if (sys_excelSafeValue(excelSheet, row, 1) == null) break; //空行，直接结束
        var oneRowData = {};
        data.push(oneRowData);
        for (var label in excelColName2ColIndex) {
            var excelCol = excelColName2ColIndex[label];
            var dbCol = colLabel2ColName[label];
            if (dbCol != null) {
                oneRowData[dbCol] = sys_excelSafeValue(excelSheet, row, excelCol);
            }
        }

    }
    //println(JSON.toString(data));

    pushMessage("dr_info", "开始导入...", currentUserId());

    var gguid = currentGUID();
    var userid = currentUserId();
    var username = currentUserName();
    var lastmodifieddate = newDate();
    // println('import_ph:' + import_ph);



    var ds = newDataStore(dsn, "select * from " + tableName);
    ds.setUpdateProperty(tableName, "id", "*", "id");
    for (var i = 0; i < data.length; i++) {
        var row = ds.insertRow(9999999);
        pushMessage("dr_info", "导入进度：" + (i + 1) + "/" + data.length, currentUserId());

        var rowData = data[i];
        for (var col in rowData) {
            var value = rowData[col];
            ds.setValue(row, col, value);
        }
        ds.setValue(row, "id", newGuid());
        ds.setValue(row, "gguid", gguid);
        ds.setValue(row, "userid", userid);
        ds.setValue(row, "username", username);
        ds.setValue(row, "lastmodifieddate", lastmodifieddate);
        ds.setValue(row, "corpid", corpid);

        if (ds.getRowCount() == 1000) {
            if (!ds.update(true)) {
                var errInfo = ds.getError().getMessage();
                println(errInfo);
                pushMessage("dr_info", "异常：" + errInfo, currentUserId());
                return;
            }
            ds.reset();
        }
    }

    pushMessage("dr_info", "正在写入数据库...", currentUserId());

    ds.update(true);
    // $alert('导入完成');
    ret.success = true;
    ret.message = ds.getString(0, "userid");

    //更新起始记账期和截止记账期
    var master_ds = newDataStore(dsn, "select * from " + masterTable);
    master_ds.setUpdateProperty(masterTable, "id", "*", "id");
    master_ds.retrieve("gguid='" + currentGUID() + "'");
    master_ds.setValue(0, "jzq_start", getStringFromSQL2(dsn, "select min(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.setValue(0, "jzq_end", getStringFromSQL2(dsn, "select max(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.update(true)

    pushMessage('dr_info', 'over', currentUserId());

    return ret;
}

function sys_excelSafeValue(sheet, row, col) {
    if (row > sheet.length - 1) return null;
    var dataRow = sheet[row];
    if (dataRow == null) return null;
    if (col > dataRow.length - 1) return null;
    return dataRow[col];

}


id: 64c7b3429932ab2156cd1c88
scriptid: 60d095db993254220bd8519f
tablename: app_script
clientip: 60.186.34.220
username: sa
createdate: 2023.07.31 21:36:34.000
script:
//后端导入excel数据包
function backend_importExcelData(excelData) {
    var ret = {
        'success': false,
        'message': ''
    }
    // return { success: false, message: excelData }
    var dsn = '';
    var masterTable = 'pzdj';
    var tableName = 'pzdjex';
    //ret.message="select id,userid,verifiername from pzdj where gguid='" + currentGUID() + "'";
    //return ret;

    println("1111");
    runSQL("", "alter table  pzdjex   modify  bz  varchar(4000) ");
    runSQL("", "alter table  pzdjex   modify  rkdh  varchar(1000) ");


    var ds = newDataStore(dsn, "select id,userid,verifiername ,corpid from pzdj where gguid='" + currentGUID() + "'");
    ds.setOnceRetrieveCount(1);
    ds.retrieve('');
    if (ds.getRowCount() == 0) {
        ret.message = '请保存单据后再操作此功能!';
        return ret;
    }

    var corpid = ds.getString(0, "corpid");

    // if (ds.getString(0, "userid") != currentUserId()) {
    //     ret.message = '由于您不是制单人，不允许对当前单据做导入操作!';
    //     return ret;
    // }
    if (ds.getString(0, "verifiername") != '') {
        ret.message = '单据已审核，不允许导入操作!';
        return ret;
    }

    runSQL(dsn, "delete from " + tableName + " where gguid='" + currentGUID() + "'");

    //**********************************************
    //导入的标准功能
    var colLabel2ColName = {
        "年度": "y",
        "调整期间": "m",
        "期间": "m",
        "凭证号": "pzh",
        "调整凭证号": "pzh",
        "会计凭证号": "pzh",
        "凭证日期": "pzrq",
        "会计科目代码": "kmdm",
        "会计科目名称": "kmmc",
        "核算账号编码": "code",
        "借方金额": "jfje",
        "贷方金额": "dfje",
        "财会支出明细编码": "ckzcmxdm",
        "财会支出明细名称": "ckzcmxmc",
        "预会支出明细编码": "ykzcmxdm",
        "预会支出明细名称": "ykzcmxmc",
        "入库单号": "rkdh",
        "是否已过账": "posted",
        "摘要": "bz",
        "制单人": "zdr",
        "经办人": "jbr"
    }

    "年度\t调整期间\t凭证号\t凭证日期\t会计科目代码\t会计科目名称\t财会支出明细编码\t财会支出明细名称\t预会支出明细编码\t预会支出明细名称\t核算账号编码\t入库单号\t是否已过账\t摘要\t制单人";


    //var excelSheet;
    //for (var p in excelData) {
    //    excelSheet = excelData[p];
    //}

    var excelSheet = excelData;

    //分析字段对应关系
    var excelColIndex2ColName = {}; //excel 中的列号对应的中文题头
    var excelColName2ColIndex = {}; //中文题头对应的excel中的列号
    // var colLabel2ColName = {}; //中文题头对应到表的字段名


    var duplicateLabelCount = {};
    var colCount = 50; //最多几个列

    var headRow = 0; //题头在第几行
    var dataStartRow = 1; // 数据行从第几行开始
    //有时可能表头有多行合并，所以要检测一下
    for (var i = 0; i < colCount; i++) {
        var v = trim(sys_excelSafeValue(excelSheet, headRow, i));
        if (v == '')
            continue;
        if (duplicateLabelCount[v] == undefined) {
            duplicateLabelCount[v] = 0;
        }
        duplicateLabelCount[v] = duplicateLabelCount[v] + 1;

        if (duplicateLabelCount[v] > 1)
            v = v + duplicateLabelCount[v];
        //拼上后缀

        excelColIndex2ColName['' + i] = v;
        excelColName2ColIndex[v] = '' + i;
    }

    println(JSON.toString(excelColName2ColIndex));
    println(JSON.toString(excelColIndex2ColName));

    var RC = excelSheet.length;

    println("excel数据行数：" + RC);
    var data = [];
    for (var row = dataStartRow; row < RC; row++) {
        if (sys_excelSafeValue(excelSheet, row, 3) == null) break; //空行，直接结束
        var oneRowData = {};
        data.push(oneRowData);
        for (var label in excelColName2ColIndex) {
            var excelCol = excelColName2ColIndex[label];
            var dbCol = colLabel2ColName[label];
            if (dbCol != null) {
                oneRowData[dbCol] = sys_excelSafeValue(excelSheet, row, excelCol);
            }
        }

    }
    //println(JSON.toString(data));

    pushMessage("dr_info", "开始导入...", currentUserId());

    var gguid = currentGUID();
    var userid = currentUserId();
    var username = currentUserName();
    var lastmodifieddate = newDate();
    // println('import_ph:' + import_ph);



    var ds = newDataStore(dsn, "select * from " + tableName);
    ds.setUpdateProperty(tableName, "id", "*", "id");
    for (var i = 0; i < data.length; i++) {
        var row = ds.insertRow(9999999);
        pushMessage("dr_info", "导入进度：" + (i + 1) + "/" + data.length, currentUserId());

        var rowData = data[i];
        for (var col in rowData) {
            var value = rowData[col];
            ds.setValue(row, col, value);
        }
        ds.setValue(row, "id", newGuid());
        ds.setValue(row, "gguid", gguid);
        ds.setValue(row, "userid", userid);
        ds.setValue(row, "username", username);
        ds.setValue(row, "lastmodifieddate", lastmodifieddate);
        ds.setValue(row, "corpid", corpid);

        if (ds.getRowCount() == 1000) {
            if (!ds.update(true)) {
                var errInfo = ds.getError().getMessage();
                println(errInfo);
                pushMessage("dr_info", "异常：" + errInfo, currentUserId());
                return;
            }
            ds.reset();
        }
    }

    pushMessage("dr_info", "正在写入数据库...", currentUserId());

    ds.update(true);
    // $alert('导入完成');
    ret.success = true;
    ret.message = ds.getString(0, "userid");

    //更新起始记账期和截止记账期
    var master_ds = newDataStore(dsn, "select * from " + masterTable);
    master_ds.setUpdateProperty(masterTable, "id", "*", "id");
    master_ds.retrieve("gguid='" + currentGUID() + "'");
    master_ds.setValue(0, "jzq_start", getStringFromSQL2(dsn, "select min(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.setValue(0, "jzq_end", getStringFromSQL2(dsn, "select max(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.update(true)

    pushMessage('dr_info', 'over', currentUserId());

    return ret;
}

function sys_excelSafeValue(sheet, row, col) {
    if (row > sheet.length - 1) return null;
    var dataRow = sheet[row];
    if (dataRow == null) return null;
    if (col > dataRow.length - 1) return null;
    return dataRow[col];

}


id: 64c7b3429932ab2156cd1c91
scriptid: 60d095db993254220bd8519f
tablename: app_script
clientip: 60.186.34.220
username: sa
createdate: 2023.07.31 21:36:46.000
script:
//后端导入excel数据包
function backend_importExcelData(excelData) {
    var ret = {
        'success': false,
        'message': ''
    }
    // return { success: false, message: excelData }
    var dsn = '';
    var masterTable = 'pzdj';
    var tableName = 'pzdjex';
    //ret.message="select id,userid,verifiername from pzdj where gguid='" + currentGUID() + "'";
    //return ret;

    println("1111");
    runSQL("", "alter table  pzdjex   modify  bz  varchar(4000) ");
    runSQL("", "alter table  pzdjex   modify  rkdh  varchar(1000) ");


    var ds = newDataStore(dsn, "select id,userid,verifiername ,corpid from pzdj where gguid='" + currentGUID() + "'");
    ds.setOnceRetrieveCount(1);
    ds.retrieve('');
    if (ds.getRowCount() == 0) {
        ret.message = '请保存单据后再操作此功能!';
        return ret;
    }

    var corpid = ds.getString(0, "corpid");

    // if (ds.getString(0, "userid") != currentUserId()) {
    //     ret.message = '由于您不是制单人，不允许对当前单据做导入操作!';
    //     return ret;
    // }
    if (ds.getString(0, "verifiername") != '') {
        ret.message = '单据已审核，不允许导入操作!';
        return ret;
    }

    runSQL(dsn, "delete from " + tableName + " where gguid='" + currentGUID() + "'");

    //**********************************************
    //导入的标准功能
    var colLabel2ColName = {
        "年度": "y",
        "调整期间": "m",
        "期间": "m",
        "凭证号": "pzh",
        "调整凭证号": "pzh",
        "会计凭证号": "pzh",
        "凭证日期": "pzrq",
        "会计科目代码": "kmdm",
        "会计科目名称": "kmmc",
        "核算账号编码": "code",
        "借方金额": "jfje",
        "贷方金额": "dfje",
        "财会支出明细编码": "ckzcmxdm",
        "财会支出明细名称": "ckzcmxmc",
        "预会支出明细编码": "ykzcmxdm",
        "预会支出明细名称": "ykzcmxmc",
        "入库单号": "rkdh",
        "是否已过账": "posted",
        "摘要": "bz",
        "制单人": "zdr",
        "经办人": "jbr"
    }

    "年度\t调整期间\t凭证号\t凭证日期\t会计科目代码\t会计科目名称\t财会支出明细编码\t财会支出明细名称\t预会支出明细编码\t预会支出明细名称\t核算账号编码\t入库单号\t是否已过账\t摘要\t制单人";


    //var excelSheet;
    //for (var p in excelData) {
    //    excelSheet = excelData[p];
    //}

    var excelSheet = excelData;

    //分析字段对应关系
    var excelColIndex2ColName = {}; //excel 中的列号对应的中文题头
    var excelColName2ColIndex = {}; //中文题头对应的excel中的列号
    // var colLabel2ColName = {}; //中文题头对应到表的字段名


    var duplicateLabelCount = {};
    var colCount = 50; //最多几个列

    var headRow = 0; //题头在第几行
    var dataStartRow = 1; // 数据行从第几行开始
    //有时可能表头有多行合并，所以要检测一下
    for (var i = 0; i < colCount; i++) {
        var v = trim(sys_excelSafeValue(excelSheet, headRow, i));
        if (v == '')
            continue;
        if (duplicateLabelCount[v] == undefined) {
            duplicateLabelCount[v] = 0;
        }
        duplicateLabelCount[v] = duplicateLabelCount[v] + 1;

        if (duplicateLabelCount[v] > 1)
            v = v + duplicateLabelCount[v];
        //拼上后缀

        excelColIndex2ColName['' + i] = v;
        excelColName2ColIndex[v] = '' + i;
    }

    println(JSON.toString(excelColName2ColIndex));
    println(JSON.toString(excelColIndex2ColName));

    var RC = excelSheet.length;

    println("excel数据行数：" + RC);
    var data = [];
    for (var row = dataStartRow; row < RC; row++) {
       // if (sys_excelSafeValue(excelSheet, row, 3) == null) break; //空行，直接结束
        var oneRowData = {};
        data.push(oneRowData);
        for (var label in excelColName2ColIndex) {
            var excelCol = excelColName2ColIndex[label];
            var dbCol = colLabel2ColName[label];
            if (dbCol != null) {
                oneRowData[dbCol] = sys_excelSafeValue(excelSheet, row, excelCol);
            }
        }

    }
    //println(JSON.toString(data));

    pushMessage("dr_info", "开始导入...", currentUserId());

    var gguid = currentGUID();
    var userid = currentUserId();
    var username = currentUserName();
    var lastmodifieddate = newDate();
    // println('import_ph:' + import_ph);



    var ds = newDataStore(dsn, "select * from " + tableName);
    ds.setUpdateProperty(tableName, "id", "*", "id");
    for (var i = 0; i < data.length; i++) {
        var row = ds.insertRow(9999999);
        pushMessage("dr_info", "导入进度：" + (i + 1) + "/" + data.length, currentUserId());

        var rowData = data[i];
        for (var col in rowData) {
            var value = rowData[col];
            ds.setValue(row, col, value);
        }
        ds.setValue(row, "id", newGuid());
        ds.setValue(row, "gguid", gguid);
        ds.setValue(row, "userid", userid);
        ds.setValue(row, "username", username);
        ds.setValue(row, "lastmodifieddate", lastmodifieddate);
        ds.setValue(row, "corpid", corpid);

        if (ds.getRowCount() == 1000) {
            if (!ds.update(true)) {
                var errInfo = ds.getError().getMessage();
                println(errInfo);
                pushMessage("dr_info", "异常：" + errInfo, currentUserId());
                return;
            }
            ds.reset();
        }
    }

    pushMessage("dr_info", "正在写入数据库...", currentUserId());

    ds.update(true);
    // $alert('导入完成');
    ret.success = true;
    ret.message = ds.getString(0, "userid");

    //更新起始记账期和截止记账期
    var master_ds = newDataStore(dsn, "select * from " + masterTable);
    master_ds.setUpdateProperty(masterTable, "id", "*", "id");
    master_ds.retrieve("gguid='" + currentGUID() + "'");
    master_ds.setValue(0, "jzq_start", getStringFromSQL2(dsn, "select min(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.setValue(0, "jzq_end", getStringFromSQL2(dsn, "select max(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.update(true)

    pushMessage('dr_info', 'over', currentUserId());

    return ret;
}

function sys_excelSafeValue(sheet, row, col) {
    if (row > sheet.length - 1) return null;
    var dataRow = sheet[row];
    if (dataRow == null) return null;
    if (col > dataRow.length - 1) return null;
    return dataRow[col];

}


id: 64c7b3429932ab2156cd1c9a
scriptid: 60d095db993254220bd8519f
tablename: app_script
clientip: 60.186.34.220
username: sa
createdate: 2023.07.31 21:36:59.000
script:
//后端导入excel数据包
function backend_importExcelData(excelData) {
    var ret = {
        'success': false,
        'message': ''
    }
    // return { success: false, message: excelData }
    var dsn = '';
    var masterTable = 'pzdj';
    var tableName = 'pzdjex';
    //ret.message="select id,userid,verifiername from pzdj where gguid='" + currentGUID() + "'";
    //return ret;

    println("1111");
    runSQL("", "alter table  pzdjex   modify  bz  varchar(4000) ");
    runSQL("", "alter table  pzdjex   modify  rkdh  varchar(1000) ");


    var ds = newDataStore(dsn, "select id,userid,verifiername ,corpid from pzdj where gguid='" + currentGUID() + "'");
    ds.setOnceRetrieveCount(1);
    ds.retrieve('');
    if (ds.getRowCount() == 0) {
        ret.message = '请保存单据后再操作此功能!';
        return ret;
    }

    var corpid = ds.getString(0, "corpid");

    // if (ds.getString(0, "userid") != currentUserId()) {
    //     ret.message = '由于您不是制单人，不允许对当前单据做导入操作!';
    //     return ret;
    // }
    if (ds.getString(0, "verifiername") != '') {
        ret.message = '单据已审核，不允许导入操作!';
        return ret;
    }

    runSQL(dsn, "delete from " + tableName + " where gguid='" + currentGUID() + "'");

    //**********************************************
    //导入的标准功能
    var colLabel2ColName = {
        "年度": "y",
        "调整期间": "m",
        "期间": "m",
        "凭证号": "pzh",
        "调整凭证号": "pzh",
        "会计凭证号": "pzh",
        "凭证日期": "pzrq",
        "会计科目代码": "kmdm",
        "会计科目名称": "kmmc",
        "核算账号编码": "code",
        "借方金额": "jfje",
        "贷方金额": "dfje",
        "财会支出明细编码": "ckzcmxdm",
        "财会支出明细名称": "ckzcmxmc",
        "预会支出明细编码": "ykzcmxdm",
        "预会支出明细名称": "ykzcmxmc",
        "入库单号": "rkdh",
        "是否已过账": "posted",
        "摘要": "bz",
        "制单人": "zdr",
        "经办人": "jbr"
    }

    "年度\t调整期间\t凭证号\t凭证日期\t会计科目代码\t会计科目名称\t财会支出明细编码\t财会支出明细名称\t预会支出明细编码\t预会支出明细名称\t核算账号编码\t入库单号\t是否已过账\t摘要\t制单人";


    //var excelSheet;
    //for (var p in excelData) {
    //    excelSheet = excelData[p];
    //}

    var excelSheet = excelData;

    //分析字段对应关系
    var excelColIndex2ColName = {}; //excel 中的列号对应的中文题头
    var excelColName2ColIndex = {}; //中文题头对应的excel中的列号
    // var colLabel2ColName = {}; //中文题头对应到表的字段名


    var duplicateLabelCount = {};
    var colCount = 50; //最多几个列

    var headRow = 0; //题头在第几行
    var dataStartRow = 1; // 数据行从第几行开始
    //有时可能表头有多行合并，所以要检测一下
    for (var i = 0; i < colCount; i++) {
        var v = trim(sys_excelSafeValue(excelSheet, headRow, i));
        if (v == '')
            continue;
        if (duplicateLabelCount[v] == undefined) {
            duplicateLabelCount[v] = 0;
        }
        duplicateLabelCount[v] = duplicateLabelCount[v] + 1;

        if (duplicateLabelCount[v] > 1)
            v = v + duplicateLabelCount[v];
        //拼上后缀

        excelColIndex2ColName['' + i] = v;
        excelColName2ColIndex[v] = '' + i;
    }

    println(JSON.toString(excelColName2ColIndex));
    println(JSON.toString(excelColIndex2ColName));

    var RC = excelSheet.length;

    println("excel数据行数：" + RC);
    var data = [];
    for (var row = dataStartRow; row < RC; row++) {
         if (sys_excelSafeValue(excelSheet, row, 3) == null) break; //空行，直接结束
        var oneRowData = {};
        data.push(oneRowData);
        for (var label in excelColName2ColIndex) {
            var excelCol = excelColName2ColIndex[label];
            var dbCol = colLabel2ColName[label];
            if (dbCol != null) {
                oneRowData[dbCol] = sys_excelSafeValue(excelSheet, row, excelCol);
            }
        }

    }
    //println(JSON.toString(data));

    pushMessage("dr_info", "开始导入...", currentUserId());

    var gguid = currentGUID();
    var userid = currentUserId();
    var username = currentUserName();
    var lastmodifieddate = newDate();
    // println('import_ph:' + import_ph);



    var ds = newDataStore(dsn, "select * from " + tableName);
    ds.setUpdateProperty(tableName, "id", "*", "id");
    for (var i = 0; i < data.length; i++) {
        var row = ds.insertRow(9999999);
        pushMessage("dr_info", "导入进度：" + (i + 1) + "/" + data.length, currentUserId());

        var rowData = data[i];
        for (var col in rowData) {
            var value = rowData[col];
            ds.setValue(row, col, value);
        }
        ds.setValue(row, "id", newGuid());
        ds.setValue(row, "gguid", gguid);
        ds.setValue(row, "userid", userid);
        ds.setValue(row, "username", username);
        ds.setValue(row, "lastmodifieddate", lastmodifieddate);
        ds.setValue(row, "corpid", corpid);

        if (ds.getRowCount() == 1000) {
            if (!ds.update(true)) {
                var errInfo = ds.getError().getMessage();
                println(errInfo);
                pushMessage("dr_info", "异常：" + errInfo, currentUserId());
                return;
            }
            ds.reset();
        }
    }

    pushMessage("dr_info", "正在写入数据库...", currentUserId());

    ds.update(true);
    // $alert('导入完成');
    ret.success = true;
    ret.message = ds.getString(0, "userid");

    //更新起始记账期和截止记账期
    var master_ds = newDataStore(dsn, "select * from " + masterTable);
    master_ds.setUpdateProperty(masterTable, "id", "*", "id");
    master_ds.retrieve("gguid='" + currentGUID() + "'");
    master_ds.setValue(0, "jzq_start", getStringFromSQL2(dsn, "select min(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.setValue(0, "jzq_end", getStringFromSQL2(dsn, "select max(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.update(true)

    pushMessage('dr_info', 'over', currentUserId());

    return ret;
}

function sys_excelSafeValue(sheet, row, col) {
    if (row > sheet.length - 1) return null;
    var dataRow = sheet[row];
    if (dataRow == null) return null;
    if (col > dataRow.length - 1) return null;
    return dataRow[col];

}


id: 64c7bc809932f3eabab890cd
scriptid: 60d095db993254220bd8519f
tablename: app_script
clientip: 60.186.34.220
username: sa
createdate: 2023.07.31 21:52:17.000
script:
//后端导入excel数据包
function backend_importExcelData(excelData) {
    var ret = {
        'success': false,
        'message': ''
    }
    // return { success: false, message: excelData }
    var dsn = '';
    var masterTable = 'pzdj';
    var tableName = 'pzdjex';
    //ret.message="select id,userid,verifiername from pzdj where gguid='" + currentGUID() + "'";
    //return ret;

    println("1111");
    runSQL("", "alter table  pzdjex   modify  bz  varchar(4000) ");
    runSQL("", "alter table  pzdjex   modify  rkdh  varchar(1000) ");


    var ds = newDataStore(dsn, "select id,userid,verifiername ,corpid from pzdj where gguid='" + currentGUID() + "'");
    ds.setOnceRetrieveCount(1);
    ds.retrieve('');
    if (ds.getRowCount() == 0) {
        ret.message = '请保存单据后再操作此功能!';
        return ret;
    }

    var corpid = ds.getString(0, "corpid");

    // if (ds.getString(0, "userid") != currentUserId()) {
    //     ret.message = '由于您不是制单人，不允许对当前单据做导入操作!';
    //     return ret;
    // }
    if (ds.getString(0, "verifiername") != '') {
        ret.message = '单据已审核，不允许导入操作!';
        return ret;
    }

    runSQL(dsn, "delete from " + tableName + " where gguid='" + currentGUID() + "'");

    //**********************************************
    //导入的标准功能
    var colLabel2ColName = {
        "年度": "y",
        "调整期间": "m",
        "期间": "m",
        "凭证号": "pzh",
        "调整凭证号": "pzh",
        "会计凭证号": "pzh",
        "凭证日期": "pzrq",
        "会计科目代码": "kmdm",
        "会计科目名称": "kmmc",
        "核算账号编码": "code",
        "借方金额": "jfje",
        "贷方金额": "dfje",
        "财会支出明细编码": "ckzcmxdm",
        "财会支出明细名称": "ckzcmxmc",
        "预会支出明细编码": "ykzcmxdm",
        "预会支出明细名称": "ykzcmxmc",
        "入库单号": "rkdh",
        "是否已过账": "posted",
        "摘要": "bz",
        "制单人": "zdr",
        "经办人": "jbr"
    }

    "年度\t调整期间\t凭证号\t凭证日期\t会计科目代码\t会计科目名称\t财会支出明细编码\t财会支出明细名称\t预会支出明细编码\t预会支出明细名称\t核算账号编码\t入库单号\t是否已过账\t摘要\t制单人";


    //var excelSheet;
    //for (var p in excelData) {
    //    excelSheet = excelData[p];
    //}

    var excelSheet = excelData;

    //分析字段对应关系
    var excelColIndex2ColName = {}; //excel 中的列号对应的中文题头
    var excelColName2ColIndex = {}; //中文题头对应的excel中的列号
    // var colLabel2ColName = {}; //中文题头对应到表的字段名


    var duplicateLabelCount = {};
    var colCount = 50; //最多几个列

    var headRow = 0; //题头在第几行
    var dataStartRow = 1; // 数据行从第几行开始
    //有时可能表头有多行合并，所以要检测一下
    for (var i = 0; i < colCount; i++) {
        var v = trim(sys_excelSafeValue(excelSheet, headRow, i));
        if (v == '')
            continue;
        if (duplicateLabelCount[v] == undefined) {
            duplicateLabelCount[v] = 0;
        }
        duplicateLabelCount[v] = duplicateLabelCount[v] + 1;

        if (duplicateLabelCount[v] > 1)
            v = v + duplicateLabelCount[v];
        //拼上后缀

        excelColIndex2ColName['' + i] = v;
        excelColName2ColIndex[v] = '' + i;
    }

    println(JSON.toString(excelColName2ColIndex));
    println(JSON.toString(excelColIndex2ColName));


    var RC = excelSheet.length;

    println("excel数据行数：" + RC);
    var data = [];
    for (var row = dataStartRow; row < RC; row++) {
         if (sys_excelSafeValue(excelSheet, row, 3) == null) break; //空行，直接结束
        var oneRowData = {};
        data.push(oneRowData);
        for (var label in excelColName2ColIndex) {
            var excelCol = excelColName2ColIndex[label];
            var dbCol = colLabel2ColName[label];
            if (dbCol != null) {
                oneRowData[dbCol] = sys_excelSafeValue(excelSheet, row, excelCol);
            }
        }

    }
    //println(JSON.toString(data));

    pushMessage("dr_info", "开始导入...", currentUserId());

    var gguid = currentGUID();
    var userid = currentUserId();
    var username = currentUserName();
    var lastmodifieddate = newDate();
    // println('import_ph:' + import_ph);



    var ds = newDataStore(dsn, "select * from " + tableName);
    ds.setUpdateProperty(tableName, "id", "*", "id");
    for (var i = 0; i < data.length; i++) {
        var row = ds.insertRow(9999999);
        pushMessage("dr_info", "导入进度：" + (i + 1) + "/" + data.length, currentUserId());

        var rowData = data[i];
        for (var col in rowData) {
            var value = rowData[col];
            ds.setValue(row, col, value);
        }
        ds.setValue(row, "id", newGuid());
        ds.setValue(row, "gguid", gguid);
        ds.setValue(row, "userid", userid);
        ds.setValue(row, "username", username);
        ds.setValue(row, "lastmodifieddate", lastmodifieddate);
        ds.setValue(row, "corpid", corpid);

        if (ds.getRowCount() == 1000) {
            if (!ds.update(true)) {
                var errInfo = ds.getError().getMessage();
                println(errInfo);
                pushMessage("dr_info", "异常：" + errInfo, currentUserId());
                return;
            }
            ds.reset();
        }
    }

    pushMessage("dr_info", "正在写入数据库...", currentUserId());

    ds.update(true);
    // $alert('导入完成');
    ret.success = true;
    ret.message = ds.getString(0, "userid");

    //更新起始记账期和截止记账期
    var master_ds = newDataStore(dsn, "select * from " + masterTable);
    master_ds.setUpdateProperty(masterTable, "id", "*", "id");
    master_ds.retrieve("gguid='" + currentGUID() + "'");
    master_ds.setValue(0, "jzq_start", getStringFromSQL2(dsn, "select min(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.setValue(0, "jzq_end", getStringFromSQL2(dsn, "select max(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.update(true)

    pushMessage('dr_info', 'over', currentUserId());

    return ret;
}

function sys_excelSafeValue(sheet, row, col) {
    if (row > sheet.length - 1) return null;
    var dataRow = sheet[row];
    if (dataRow == null) return null;
    if (col > dataRow.length - 1) return null;
    return dataRow[col];

}


id: 64c7bc809932f3eabab890e0
scriptid: 60d095db993254220bd8519f
tablename: app_script
clientip: 60.186.34.220
username: sa
createdate: 2023.07.31 21:54:51.000
script:
//后端导入excel数据包
function backend_importExcelData(excelData) {
    var ret = {
        'success': false,
        'message': ''
    }
    // return { success: false, message: excelData }
    var dsn = '';
    var masterTable = 'pzdj';
    var tableName = 'pzdjex';
    //ret.message="select id,userid,verifiername from pzdj where gguid='" + currentGUID() + "'";
    //return ret;

    println("1111");
    runSQL("", "alter table  pzdjex   modify  bz  varchar(4000) ");
    runSQL("", "alter table  pzdjex   modify  rkdh  varchar(1000) ");


    var ds = newDataStore(dsn, "select id,userid,verifiername ,corpid from pzdj where gguid='" + currentGUID() + "'");
    ds.setOnceRetrieveCount(1);
    ds.retrieve('');
    if (ds.getRowCount() == 0) {
        ret.message = '请保存单据后再操作此功能!';
        return ret;
    }

    var corpid = ds.getString(0, "corpid");

    // if (ds.getString(0, "userid") != currentUserId()) {
    //     ret.message = '由于您不是制单人，不允许对当前单据做导入操作!';
    //     return ret;
    // }
    if (ds.getString(0, "verifiername") != '') {
        ret.message = '单据已审核，不允许导入操作!';
        return ret;
    }

    runSQL(dsn, "delete from " + tableName + " where gguid='" + currentGUID() + "'");

    //**********************************************
    //导入的标准功能
    var colLabel2ColName = {
        "年度": "y",
        "调整期间": "m",
        "期间": "m",
        "凭证号": "pzh",
        "调整凭证号": "pzh",
        "会计凭证号": "pzh",
        "凭证日期": "pzrq",
        "会计科目代码": "kmdm",
        "会计科目名称": "kmmc",
        "核算账号编码": "code",
        "借方金额": "jfje",
        "贷方金额": "dfje",
        "财会支出明细编码": "ckzcmxdm",
        "财会支出明细名称": "ckzcmxmc",
        "预会支出明细编码": "ykzcmxdm",
        "预会支出明细名称": "ykzcmxmc",
        "入库单号": "rkdh",
        "是否已过账": "posted",
        "摘要": "bz",
        "制单人": "zdr",
        "经办人": "jbr"
    }

    "年度\t调整期间\t凭证号\t凭证日期\t会计科目代码\t会计科目名称\t财会支出明细编码\t财会支出明细名称\t预会支出明细编码\t预会支出明细名称\t核算账号编码\t入库单号\t是否已过账\t摘要\t制单人";


    //var excelSheet;
    //for (var p in excelData) {
    //    excelSheet = excelData[p];
    //}

    var excelSheet = excelData;

    //分析字段对应关系
    var excelColIndex2ColName = {}; //excel 中的列号对应的中文题头
    var excelColName2ColIndex = {}; //中文题头对应的excel中的列号
    // var colLabel2ColName = {}; //中文题头对应到表的字段名


    var duplicateLabelCount = {};
    var colCount = 50; //最多几个列

    var headRow = 0; //题头在第几行
    var dataStartRow = 1; // 数据行从第几行开始
    //有时可能表头有多行合并，所以要检测一下
    for (var i = 0; i < colCount; i++) {
        var v = trim(sys_excelSafeValue(excelSheet, headRow, i));
        if (v == '')
            continue;
        if (duplicateLabelCount[v] == undefined) {
            duplicateLabelCount[v] = 0;
        }
        duplicateLabelCount[v] = duplicateLabelCount[v] + 1;

        if (duplicateLabelCount[v] > 1)
            v = v + duplicateLabelCount[v];
        //拼上后缀

        excelColIndex2ColName['' + i] = v;
        excelColName2ColIndex[v] = '' + i;
    }

    println(JSON.toString(excelColName2ColIndex));
    println(JSON.toString(excelColIndex2ColName));


    var RC = excelSheet.length;

    println("excel数据行数：" + RC);
    var data = [];
    for (var row = dataStartRow; row < RC; row++) {
        // if (sys_excelSafeValue(excelSheet, row, 3) == null) break; //空行，直接结束
        var oneRowData = {};
        data.push(oneRowData);
        for (var label in excelColName2ColIndex) {
            var excelCol = excelColName2ColIndex[label];
            var dbCol = colLabel2ColName[label];
            if (dbCol != null) {
                oneRowData[dbCol] = sys_excelSafeValue(excelSheet, row, excelCol);
            }
        }

    }
    //println(JSON.toString(data));

    pushMessage("dr_info", "开始导入...", currentUserId());

    var gguid = currentGUID();
    var userid = currentUserId();
    var username = currentUserName();
    var lastmodifieddate = newDate();
    // println('import_ph:' + import_ph);



    var ds = newDataStore(dsn, "select * from " + tableName);
    ds.setUpdateProperty(tableName, "id", "*", "id");
    for (var i = 0; i < data.length; i++) {
        var row = ds.insertRow(9999999);
        pushMessage("dr_info", "导入进度：" + (i + 1) + "/" + data.length, currentUserId());

        var rowData = data[i];
        for (var col in rowData) {
            var value = rowData[col];
            ds.setValue(row, col, value);
        }
        ds.setValue(row, "id", newGuid());
        ds.setValue(row, "gguid", gguid);
        ds.setValue(row, "userid", userid);
        ds.setValue(row, "username", username);
        ds.setValue(row, "lastmodifieddate", lastmodifieddate);
        ds.setValue(row, "corpid", corpid);

        if (ds.getRowCount() == 1000) {
            if (!ds.update(true)) {
                var errInfo = ds.getError().getMessage();
                println(errInfo);
                pushMessage("dr_info", "异常：" + errInfo, currentUserId());
                return;
            }
            ds.reset();
        }
    }

    pushMessage("dr_info", "正在写入数据库...", currentUserId());

    ds.update(true);
    // $alert('导入完成');
    ret.success = true;
    ret.message = ds.getString(0, "userid");

    //更新起始记账期和截止记账期
    var master_ds = newDataStore(dsn, "select * from " + masterTable);
    master_ds.setUpdateProperty(masterTable, "id", "*", "id");
    master_ds.retrieve("gguid='" + currentGUID() + "'");
    master_ds.setValue(0, "jzq_start", getStringFromSQL2(dsn, "select min(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.setValue(0, "jzq_end", getStringFromSQL2(dsn, "select max(y*100+m) as t from pzdjex where gguid='" + currentGUID() + "'"));
    master_ds.update(true)

    pushMessage('dr_info', 'over', currentUserId());

    return ret;
}

function sys_excelSafeValue(sheet, row, col) {
    if (row > sheet.length - 1) return null;
    var dataRow = sheet[row];
    if (dataRow == null) return null;
    if (col > dataRow.length - 1) return null;
    return dataRow[col];

}


id: 64cb1d2c9932a9c0fc253793
scriptid: 60fa600f9932563b7c99baff
tablename: app_script
clientip: 39.170.92.207
username: sa
createdate: 2023.08.03 11:25:24.000
script:
/**
 * 单据点击保存，前端所有的操作被转译成3种操作:insert , delete , update ,
 *  并且可以在  beforeBillSaveOnServer 中返回一些SQL语句，可以在afterBillSaveOnServer中返回一些SQL语句
 *
 * 系统分别在需要的数据库连接中执行这些语句 ， 当所有语句执行成功后，事件被提交。一些细节如下 ：
 *
 * 1 如果单据中的表，属于不同的数据库连接，那么所有的SQL语句会以连接为单位，对语句进行批量执行
 * 2 这些语句的执行顺序是：对同一个数据库连接中的语句， 先执行 beforeBillSaveOnServer 中返回的SQL语句，再执行单据操作转译得到的SQL语句，
 *   再执行 afterBillSaveOnServer 中返回的SQL语句， 当多个（或一个）数据库连接执行完这些语句，且没有错误时，所有的数据库连接做提交，否则全部回滚
 * 3 不要在 beforeBillSaveOnServer 或 afterBillSaveOnServer 中直接执行数据库操作，而是返回SQL语句，让系统批量执行它们，并控制好事务
 * 4 当执行成功后，系统再执行事件函数  afterBillSaveSuccessfulOnServer 。 此事件中可以执行任意操作，它与单据保存事件结束后才被触发执行，
 *   与单据保存并不在一个事务中。
 *
 *
 * @param gguid  要删除记录的gguid ，等同于主表的id
 * @param session  一个JSON对象，用于在整个单据保存过程中的各个事件之间传递数据。本事件做为单据保存的最后一个事件，session对象在使用后将被丢弃
 *
 *
 * @return 本函数不需要返回值
 *
 */
function afterBillSaveSuccessfulOnServer(gguid, session) {

    var corpid = getStringFromSQL("select corpid from dzpz_dy where id='" + gguid + "'");
    //2022.08.11优化，理论上不应该存在重复号码的凭证，但是加上max肯定没错，避免数据异常导致更新失败
    var sqls = ["update dzpz_dymx  set bz=null,pz_id= (select max(id) from dzpz_pz where dzpz_pz.pzh= dzpz_dymx.pzh and dzpz_pz.corpid='" + corpid + "' ) where gguid='" + gguid + "' ",
    "update dzpz_dymx  set bz='凭证号不存在' where  gguid='" + gguid + "' and not exists (select dzpz_pz.id from dzpz_pz where  dzpz_pz.pzh= dzpz_dymx.pzh  and dzpz_pz.corpid='" + corpid + "')  "

    ];

    runSQL("", sqls, true);
    println("----检测凭证是否存在完成---------")
}


/**
 * 用户任务提交后
 * @param flow 流程定义
 * @param node 开始节点
 * @param gguid 单据的gguid

 */
//@comment (group=工作流事件,caption=用户任务提交后)
function onUserTaskSubmitted(flow, userTask, gguid) {
    // println( flow.id) ; //流程定义的ID
    // println( userTask.name);
    println(JSON.stringify(userTask));
    println(gguid);


    if (userTask.id == 'id_60b3b08f27644fc18af35ba20ecd2bac') {
        var moreData = getStringFromSQL("select moredata from dzpz_dy where id='" + gguid + "'");
        try {
            moreData = JSON.parse(moreData);
            //起草后，递交后调用OA的接口回写数据
            if (moreData.form_id) {
                runTask('oa_callback', { requestid: moreData.form_id });
            }
        } catch (err) {

        }

    }


    if (userTask.id == 'id_6655df8bedc641aa9bacf1720d13ce2d') //领导审批通过后，就开始打包
    {
        //领导审批通过后，就开始打包
       // do_package(gguid);
    }

    println(gguid + " " + userTask.name + " 提交完成")


}

//后台打包
function do_package(gguid) {
    var ret = JSON.parse(runTask("package", { gguid: gguid }));
    return ret;
}



function back_urlShare(url, d, t) {
    var ret = urlShare(url, d, "day", t);
    return ret.url;
}


