[关闭]
@caos 2015-09-02T08:14:47.000000Z 字数 4922 阅读 2406

润乾报表电子签名教程

工作

在朔黄项目中的流程审批采用了自动电子签章生成的方法来根据具体的流程记录在润乾报表文件中动态生成用户保存的签章图片，并嵌入到报表中，报表使用了润乾4.5版本的设计器，用户的个人签章图片已经在数据库中存储在 shdcm_signature 表中为BOLB类型的二进制文件，根据此情况，我们一共有两种方式实现自动签章的效果：

SQL

  1. SELECT SS.PICTURE pic
  2.   FROM SHDCM_SIGNATURE ss,
  3.        (SELECT F.TRANSACT_PERSON_ID
  4.           FROM DENO_FLOW_RECORD F, DENO_PAYMENT_RECORD p
  5.          WHERE F.DENOMINATE_ID = p.id
  6.            AND f.id = (SELECT MAX(f.id)
  7.                          FROM DENO_FLOW_RECORD F, DENO_PAYMENT_RECORD p
  8.                         WHERE F.DENOMINATE_ID = p.id
  9.                           AND F.RECORD_TYPE_ID = 2
  10.                           and P.YEAR_PERIOD = ?
  11.                           and p.SEASON_PERIOD = ?
  12.     and p.CONTRACT_ID = ?
  13.     AND F.TACHE LIKE '%起草申请单%'  
  14.     )
  15.   )E
  16. WHERE SS.OWNER_ID = e.TRANSACT_PERSON_ID
  1. package com.itclub.deno.dianziqianzhang.supervisorPayment;
  5. import java.io.ByteArrayOutputStream;
  6. import java.io.InputStream;
  7. import java.sql.Blob;
  8. import java.sql.Connection;
  9. import java.sql.ResultSet;
  10. import java.sql.Statement;
  11. import java.util.ArrayList;
  12. import java.util.List;
  14. import com.runqian.report4.usermodel.CellGraphConfig;
  15. import com.runqian.report4.usermodel.Context;
  16. import com.runqian.report4.usermodel.ICellGraphGenerator;
  19.     //工程付款申请流程    施工单位复审
  20.     public class FuHe implements  ICellGraphGenerator {
  22.         /*  数据图层 
  23.          * (non-Javadoc)
  24.          * @see com.runqian.report4.usermodel.ICellGraphGenerator#generate(com.runqian.report4.usermodel.Context, com.runqian.report4.usermodel.CellGraphConfig)
  25.          */
  26.         public byte[] generate(Context arg0, CellGraphConfig arg1) {
  27.             Connection con1 = null;
  28.             byte[] imagebyte=null;
  29.             Long uuid=null;
  31. //          读取程序全局添加的数据源
  32.             Context initCtx = arg0;//全局context
  33.             //String defDsName="report"; //数据源名
  34.             String defDsName = initCtx.getDefDataSourceName();
  37.             java.util.Map paramMap = initCtx.getParamMap(false);
  38.             System.out.println(paramMap);
  39.             String contractId=(String)paramMap.get("contract_id");
  40.             String year=(String)paramMap.get("year");
  41.             String season=(String)paramMap.get("season");
  42.             String seasonTime=(String)paramMap.get("season_time");
  43.             List<Object> uuidList = getUuid(contractId,year,season,seasonTime,defDsName,arg0);
  44.             if(uuidList!=null&&uuidList.size()>0){
  45.                  uuid = (Long) uuidList.get(0);
  46.             }
  47.             try {
  48.             con1 = initCtx.getConnectionFactory(defDsName).getConnection();
  49.             } catch (Exception e) {
  50.                 System.out.println( "[ConnectionManager] - 获取数据库连接失败" );
  51.                 e.printStackTrace();
  52.             }   
  54.             ResultSet rs1=null;
  55.             Statement stmt1=null;
  56.             String sql1="select picture ,ID from shdcm_signature where owner_id="+uuid+" and owner_type=680 ORDER BY ID DESC";  //得到签字图片
  57.             try {   
  58.                  stmt1 = con1.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
  59.                  rs1=stmt1.executeQuery(sql1);
  60.                 System.out.println(rs1.getRow());
  61.                  if (rs1.next()) {
  62.                     //  BLOB  blob  =  (BLOB) rs1.getBlob("picture");
  64.                      Long id = rs1.getLong("id");
  65.                      System.out.println(id);
  67.                      Blob blob = rs1.getBlob("picture");
  69.                         System.out.println(blob);
  71.                         InputStream stream = blob.getBinaryStream();
  72.                         ByteArrayOutputStream baos = new ByteArrayOutputStream();
  73.                         for(int b; (b=stream.read())>=0;){
  74.                             baos.write(b);
  75.                         }
  76.                         imagebyte = baos.toByteArray();
  77.                         System.out.println("获取二进制数据库完成");
  78.                     }
  79.                  else   imagebyte =null;   //如果状态位state=0 或者记录为空则返回null图片
  80.                     rs1.close();
  81.                     stmt1.close();
  82.                     con1.close();
  84.             } catch (Exception e) {
  85.                 System.out.println(e.getMessage());
  86.             }
  88.             return imagebyte;
  89.         }
  91.     private List<Object> getUuid(String contractId, String year, String season, String seasonTime,String defDsName, Context arg0) {
  92.             Connection con = null;
  93.             Context initCtx = arg0;//全局context
  94.             List<Object> uuidList = new ArrayList<Object>();
  95.             try {
  96.                 con = initCtx.getConnectionFactory(defDsName).getConnection();
  97.                 } catch (Exception e) {
  98.                     System.out.println( "[ConnectionManager] - 获取数据库连接失败" );
  99.                     e.printStackTrace();
  100.                 }   
  102.                 ResultSet rs=null;
  103.                 Statement stmt=null;
  104.                 String sql1="SELECT F.TRANSACT_PERSON_ID FROM DENO_FLOW_RECORD F WHERE F.ID = " +
  105.                         " (SELECT MAX(F.ID) FROM DENO_FLOW_RECORD F, DENO_PAYMENT_RECORD R WHERE F.DENOMINATE_ID = R.ID " +
  106.                         " AND F.RECORD_TYPE_ID = 2 AND R.CONTRACT_ID = "+contractId+" AND R.YEAR_PERIOD = "+year+
  107.                         " AND R.SEASON_PERIOD = "+season+" and R.CONTRACT_TYPE_ID = 717"+
  108.                         " and R.payment_season_time = "+seasonTime+" AND F.TACHE like '%监理单位复审%')";
  109.                 try {   
  110.                      stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
  111.                      rs=stmt.executeQuery(sql1);
  112.                      if (rs.next()) {
  113.                             Long  uuid  = rs.getLong(1);
  114.                             uuidList.add(uuid);
  115.                         }
  116.                         rs.close();
  117.                         stmt.close();
  118.                         con.close();
  120.                 } catch (Exception e) {
  121.                     e.printStackTrace();
  122.                 }
  123.             return uuidList;
  124.         }
  126.     }

这两点都能够通过sql查询的方式获得用户的图片签章文件，不过两者同样使用流程任务的任务节点名称作为过滤条件查询对应的签章。

  1. /*施工付款单
  2.  *总数据集
  3.  */
  4. SELECT SS.PICTURE           pic,
  5.        e.transact_date      "处理时间",
  6.        e.transact_person_id,
  7.        e.transact_opinion   "审批意见",
  8.        e.transact_unit_name
  9.   FROM SHDCM_SIGNATURE ss,
  10.        (SELECT F.TRANSACT_PERSON_ID,
  11.                f.transact_date,
  12.                f.transact_opinion,
  13.                f.transact_unit_name
  14.           FROM DENO_FLOW_RECORD F, DENO_PAYMENT_RECORD p
  15.          WHERE F.DENOMINATE_ID = p.id
  16.            AND f.id = (SELECT MAX(f.id)
  17.                          FROM DENO_FLOW_RECORD F, DENO_PAYMENT_RECORD p
  18.                         WHERE F.DENOMINATE_ID = p.id
  19.                           AND F.RECORD_TYPE_ID = 2
  20.                           and P.YEAR_PERIOD = ?
  21.                           and p.SEASON_PERIOD = ?
  22.     and p.CONTRACT_ID = ?
  23. and p.payment_season_time = ?
  24.     AND F.TACHE LIKE '%分公司领导_审批%'  
  25.     )
  26.   )E
  27. WHERE SS.OWNER_ID = e.TRANSACT_PERSON_ID
  30. /*部门字段*/
  31. =if(fengognsilingdaoQZ.select(transact_unit_name)==null,'分公司领导审批',fengognsilingdaoQZ.select(transact_unit_name))
  34. /*审批意见*/
  35. ="   "+fengognsilingdaoQZ.select(审批意见)
  37. /*签章*/
  38. =fengognsilingdaoQZ.select(pic)
  40. /*审批时间*/
  41. =year(fengognsilingdaoQZ.select(处理时间))+"年"+month(fengognsilingdaoQZ.select(处理时间))+"月"+day(fengognsilingdaoQZ.select(处理时间))+"日"
添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注