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

标签：String getString rs xhr Teamcenter new import 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.结果：

 

 

 

标签：String,getString,rs,xhr,Teamcenter,new,import,AWC,报表
From： https://www.cnblogs.com/PLM-Teamcenter/p/18172630