Teamcenter AWC 调用存储过程输出报表

1.前端：

1.1增加导出报表命令：

{

    "commands": {

      ...,

        "ExportBOMCommand": {

            "iconId": "cmdZoomToSelected",

            "title": "{{i18n.ExportBOMCommandTitle}}",

            "description": "{{i18n.ExportBOMCommandDescription}}"

        }

    },

    "commandHandlers": {

       ...,

        "ExportBOMCommandHandler": {

            "id": "ExportBOMCommand",

            "action": "activateExportBOMCommand",

            "activeWhen": true,

            "visibleWhen": {

                "condition": "conditions.true"

            }

        }

    },

    "commandPlacements": {

       ...,

        "activateExportBOMCommand": {

            "actionType": "JSFunction",

            "method": "exportBOM",

            "deps": "js/ExportBOMCommandService",

            "inputData": {

                "ctx": "{{ctx}}"

            }

        }

    },

    "conditions": {

        "objectIsSelected": {

            "expression": "ctx.selected"

        }

    },

    "i18n": {

       ...,

        "ExportBOMCommandTitle": [

            "mysamplemoduleMessages"

        ],

        "ExportBOMCommandDescription": [

            "mysamplemoduleMessages"

        ]

    }

}

1.2 JS：

/**

 * Simple Alert service for sample command Handlers

 *

 * @module js/ExportBOMCommandService

 */

/**

 * Dummy alert.

 * @param {String} text - text to display

 */

export let alert2 = function (text) {

    alert(text); // eslint-disable-line

};

import AwPromiseService from 'js/awPromiseService';

import ywCommonServices from 'js/ywCommonServices';

import messagingService from 'js/messagingService';

import {

    get_URL_service

} from './ywCommonServices';

import $ from 'jquery';

export let exportBOM = function (ctx) {

    var URL_service ="http://localhost:9527/awc/reports/";// get_URL_service();

    var deferred = AwPromiseService.instance.defer();

    var data = [];

    ctx.mselected.forEach(element => {

        console.log(element.uid);

        data.push({"uid":element.uid});

    });

   console.log(data);

/*   已经通了*/

    var xhr = new XMLHttpRequest();

    xhr.responseType = "arraybuffer";

    xhr.open("POST", URL_service+"ExportBOM", true);

   /* xhr.onreadystatechange = function () {

        if (xhr.readyState === 4) {

            if (xhr.status === 200) {

                deferred.resolve(xhr.response);

            } else {

                deferred.reject(xhr.statusText);

            }

        }

    }*/

    xhr.onerror = function () {

        messagingService.showError('导出失败，导出的内容为空！');

            return;

    }

    xhr.onload = function () {

        const blob = new Blob([this.response], {

            type: "application/vnd.ms-excel"

        });

        if (blob.size < 1) {

            alert('导出失败，导出的内容为空！');

            return;

        }

        if (window.navigator.msSaveOrOpenBlob) {

            navigator.msSaveOrOpenBlob(blob, 'test.xlsx')

            navigator.open(blob);

        } else {

            messagingService.showInfo("导出完成！", "导出成功");

            const aLink = document.createElement('a');

            aLink.style.display = 'none';

            aLink.href = window.URL.createObjectURL(blob);

            var timestamp=new Date().getTime();

            aLink.download =timestamp+ '.xlsx';

            document.body.appendChild(aLink);

            aLink.click();

            document.body.removeChild(aLink);

            return;

        }

    }

   // xhr.setRequestHeader("Authorization", "xxx");

    xhr.setRequestHeader("Content-Type", "application/json");

    xhr.send(JSON.stringify(data));

}

export default {

    alert: alert2,

    exportBOM: exportBOM

};

 

1.3：没有添加界面，没有view和viewModel

 

2.后端：

2.1Springboot添加服务：

package com.leoch.tc.service.awcreports.controllers;

import com.leoch.tc.service.awcreports.services.impl.EBOMExportServicesImpl;
import com.teamcenter.util.POIExcel;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@RestController
@RequestMapping(value = "/awc/reports")
@CrossOrigin(origins = "*")
public class ReportController {
    @Autowired
    private Environment environment;
    @Autowired
    EBOMExportServicesImpl ebomExportServices;

    Logger logger = LoggerFactory.getLogger(ReportController.class);
    @RequestMapping(value = { "/ExportBOM" }, method = { RequestMethod.POST, RequestMethod.GET })
    @ResponseBody
    public String exportBOM(@RequestBody String jsonString, HttpServletResponse response) {
        System.out.println("ExportBOM"+jsonString);
        try{
            ebomExportServices.exportEBOM(jsonString, response);
            return "{\"result\":\"ExportBOM Successfully\"}";
        }catch (Exception e){
            return "{\"result\":\"ExportBOM Error"+String.valueOf(e)+"\"}";
        }
    }
}

package com.leoch.tc.service.awcreports.services.impl;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import com.leoch.tc.service.awcreports.services.IEBOMExportServices;
import com.leoch.tc.service.awcreports.utils.POIExcel;
import com.leoch.tc.service.configs.DataSourceConfigure;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.UUID;

@Service("ebomExportServices")
public class EBOMExportServicesImpl  implements IEBOMExportServices {

    private Logger logger = LoggerFactory.getLogger(EBOMExportServicesImpl.class);
    @Autowired
    private DataSourceConfigure dsc ;
//    @Autowired
//    private POIExcel poiExcel;
    public EBOMExportServicesImpl(){

    }

    @Override
    public  void exportEBOM(String jsonString  ,HttpServletResponse response) throws Exception {
        try {
            logger.info(jsonString);
            JSONArray jsonObj = new JSONArray(jsonString);
            Connection conn=dsc.secondaryDataSource().getConnection();
            String uuid= UUID.randomUUID().toString();

            for (int i=0 ;i<jsonObj.size() ;i++) {
               JSONObject pirObj=jsonObj.getJSONObject(i);
                String pitem_rev_uid=  pirObj.getStr("uid");
                    CallableStatement proc = null;
                    ResultSet rs = null;
                    try {
                            proc = conn.prepareCall("{call CALL_GET_EBOM(?,?)}");
                            proc.setString(1, pitem_rev_uid);
                            proc.setString(2, uuid);
                            proc.execute();
                    } catch (Exception e) {
                        new Throwable(String.valueOf(e));
                        logger.error(String.valueOf(e));
                    }
            }
            try {
                ArrayList<String[]> exportDatas = new ArrayList<String[]>();
                String sqlStr = "select * from LB_EBOM where uuid='" + uuid + "'  order by  to_number(EBOM_SEQ)";//pitem_id,CITEM_ID";//
                PreparedStatement ps = conn.prepareStatement(sqlStr);
                ;
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    String[] dataRow = new String[]{rs.getString("BL_LEVEL_STARTING_0"), // 层级
                            rs.getString("PITEM_ID"), // 父项ID
                            rs.getString("PITEM_REVISION_ID"), // 父项版本
                            rs.getString("POBJECT_DESC"), // 父项物料描述
                            rs.getString("POWNING_USER"), // 父项所有
                            rs.getString("PRELEASE_STATUS_LIST"), // 父项所有
                            rs.getString("CITEM_ID"), // 子项ID
                            rs.getString("CITEM_REVISION_ID"), // 子项版本
                            rs.getString("COBJECT_DESC"), // 零组件名称
                            rs.getString("L8_USAGE"), // 用量
                            rs.getString("L8_ENGINEER_UNIT"), // 工程单位
                            rs.getString("L8_NOTE"), // BOM备注
                            rs.getString("COWNING_USER"), // 子项所有者
                            rs.getString("BL_INDENTED_TITLE"), // BOM行
                            rs.getString("CRELEASE_STATUS_LIST"), // 零组件发布状态
                            rs.getString("SEQUENCE_NO")// 查找编号
                    };
                    exportDatas.add(dataRow);
                }
                if (exportDatas.size() > 0) {
                    try {
                        String[] exportColNames = new String[]{"层级", "父项ID", "父项版本", "父项物料描述", "父项所有者", "父项发布状态", "子项ID",
                                "子项版本", "零组件名称", "用量", "工程单位", "BOM备注", "子项所有者", "BOM行", "零组件发布状态", "查找编号"};
                        POIExcel poiExcel=new POIExcel();
                        OutputStream outputStream = response.getOutputStream();
                        poiExcel.exportExceltoClient(exportColNames, exportDatas, "BOM 清单", outputStream);
                        outputStream.flush();
                        outputStream.close();
                    } catch (Exception ex1) {
                        new Throwable(String.valueOf(ex1));
                        logger.error("", ex1);
                    }
                    ps = conn.prepareStatement("delete from LB_EBOM where uuid='" + uuid + "'");
                    rs = ps.executeQuery();
                    conn.commit();
                }
            } catch(Exception e1){
                new Throwable(String.valueOf(e1));
                logger.error(String.valueOf(e1));
            }
        }catch (SQLException sqlEx){
            logger.error(String.valueOf(sqlEx));
            new Throwable(String.valueOf(sqlEx));
        }
    }
}

3.结果：