reportdaoimpl.java

一套完整的工商12315的源程序jsp部分在12315里,后台JAVA部分在gs12315src里,没有打包数据库.

package com.gs.pageBuilder.dao;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */
import java.util.*;
import java.sql.*;
import java.text.DateFormat;
import com.gs.util.*;
import com.gs.util.PubFunc;
import com.gs.pageBuilder.util.*;
import com.gs.pageBuilder.model.*;
//import java.sql.Clob;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.io.Writer;
import java.io.File;
import java.io.FileInputStream;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.FileNotFoundException;
import oracle.sql.CLOB;
import java.io.BufferedWriter;
import java.io.BufferedReader;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
import java.text.DateFormat;

public class ReportDAOImpl
    implements ReportDAO {

  private Connection conn;
  private Statement stmt;
  private ResultSet resu, rs;
  private PreparedStatement pstmt;

  public ReportDAOImpl() {
    super();
  }

  /**
   * 获得登记机关列表
   * 应用:
   */
  public ArrayList getReportDJJGList(String reportDJJG) throws
      AppException {
    String sql = null;
    ArrayList reportDJJGList = new ArrayList();
    try {
      conn = Common.getConnection();
      stmt = conn.createStatement();

      //是否正常数据
      if (reportDJJG == null) {
        Debug.println(
            "[ReportDAOImpl]getReportDJJGList----->reportDJJG is null!");
        return reportDJJGList;
      }

      sql = "select * from  "
          + TableNameUtil.DICT_DJJG
          + " where content like'"
          + reportDJJG
          + "%' order by content"
          ;
      Debug.println("[ReportDAOImpl]getReportDJJGList--->>>sql:" + sql);
      rs = stmt.executeQuery(sql);

      while (rs.next()) {
        ArrayList tempList = new ArrayList();
        tempList.add(rs.getString(1));
        tempList.add(rs.getString(2));
        reportDJJGList.add(tempList);
      }
      Debug.println(
          "[ReportDAOImpl]getReportDJJGList--->>>reportDJJGList.size():" +
          reportDJJGList.size());

    }
    catch (SQLException sqle) {
      Debug.println("[ReportDAOImpl]getReportDJJGList--->>>Exception:" +
                    sqle.getMessage());
    }

    finally {
      try {
        this.closePreparedStatement();
      }
      catch (Exception e) {
      }
      try {
        this.closeResultSet();
      }
      catch (Exception e) {
      }
      try {
        this.closeStatement();
      }
      catch (Exception e) {
      }
      try {
        this.closeConnection();
      }
      catch (Exception e) {
      }
    }

    return reportDJJGList;
  }

  /**
   * 得到报表公式数据
   * 应用:得到报表公式数据
   */
  public ArrayList getReportExpression(String page_code) throws
      AppException {
    String sql = null;
    ArrayList reportExpressionList = new ArrayList();
    ReportExpressionModel reportExpressionModel = null;
    try {
      conn = Common.getConnection();
      //conn.setAutoCommit(false);
      stmt = conn.createStatement();

      //是否正常数据
      if (page_code == null) {
        Debug.println(
            "[ReportDAOImpl]getReportExpression----->page_code is null!");
        return reportExpressionList;
      }

      /*
           private String code; //记录编号
           private String pageCode; //报表页面编号
           private String ecplain; //说明
           private String category1; //公式内容 x11y12=x11y12+x11y12+x11y12+x11y12
           private String category2; //错误提示
           private String category3; //0(表内)，1（表间）
       */

      sql = "select * from  "
          + TableNameUtil.UNIVERSAL_REPORT_EXPRESSION
          + " where page_code='"
          + page_code
          + "' order by page_code,category3 desc"
          ;
      Debug.println("[ReportDAOImpl]getReportExpression--->>>sql:" + sql);
      rs = stmt.executeQuery(sql);
     // conn.commit();

      while (rs.next()) {
        reportExpressionModel = new ReportExpressionModel();
        reportExpressionModel.setCode(PubFunc.decodeGB(""));
        reportExpressionModel.setPageCode(PubFunc.decodeGB(rs.getString(
            "page_code")));
        reportExpressionModel.setExplain(PubFunc.decodeGB(rs.getString(
            "explain")));
        reportExpressionModel.setCategory1(PubFunc.decodeGB(rs.getString(
            "category1")));
        reportExpressionModel.setCategory2(PubFunc.decodeGB(rs.getString(
            "category2")));
        reportExpressionModel.setCategory3(PubFunc.decodeGB(rs.getString(
            "category3")));
        reportExpressionList.add(reportExpressionModel);
      }
      Debug.println(
          "[ReportDAOImpl]getReportExpression--->>>reportExpressionList.size():" +
          reportExpressionList.size());

    }
    catch (SQLException sqle) {
      Debug.println("[ReportDAOImpl]getReportExpression--->>>Exception:" +
                    sqle.getMessage());
    }

    finally {
      try {
        this.closePreparedStatement();
      }
      catch (Exception e) {
      }
      try {
        this.closeResultSet();
      }
      catch (Exception e) {
      }
      try {
        this.closeStatement();
      }
      catch (Exception e) {
      }
      try {
        this.closeConnection();
      }
      catch (Exception e) {
      }
    }

    return reportExpressionList;
  }

  /**
   * 得到报表记录数据
   * 应用:得到报表记录数据
   */
  public ReportPageModel getReportBuildPage(String code) throws
      AppException {
    String sql = null;
    ReportPageModel reportPageModel = new ReportPageModel();
    try {
      conn = Common.getConnection();
      //conn.setAutoCommit(false);
      stmt = conn.createStatement();

      //是否正常数据
      if (code == null) {
        Debug.println(
            "[ReportDAOImpl]getReportBuildPage----->code is null!");
        return reportPageModel;
      }

      /*
        CODE VARCHAR2(20) NOT NULL PRIMARY KEY,	//报表编号
        PAGE_CODE VARCHAR2(20) NULL,		//页面编码
        REPORT_EXPLAIN VARCHAR2(200) NULL,	//报表说明
        CONTENT CLOB NULL,			//报表内容
        BUILD_DATE VARCHAR2(12) NULL,		//生成日期
        REPORT_UNIT VARCHAR2(100) NULL,		//报表单位
        REPORT_FROM VARCHAR2(12) NULL,		//报表自
        REPORT_TO VARCHAR2(12) NULL,		//报表至
        REPORT_TYPE VARCHAR2(12) NULL,		//报表类型
        REPORT_REMARK VARCHAR2(200) NULL,	//报表注释
        REPORT_EXTENT VARCHAR2(12) NULL,	//报表范围
        REPORT_DATE VARCHAR2(12) NULL,		//报出日期
        OPERATOR VARCHAR2(12) NULL		//报出人
       */

      sql = "select * from  "
          + TableNameUtil.UNIVERSAL_REPORT
          + " where code='"
          + code
          + "' order by code desc"
          ;
      Debug.println("[ReportDAOImpl]getReportBuildPage--->>>sql:" + sql);
      rs = stmt.executeQuery(sql);
    //  conn.commit();

      if (rs.next()) {
        reportPageModel.setCode(PubFunc.decodeGB(rs.getString("code")));
        reportPageModel.setPageCode(PubFunc.decodeGB(rs.getString("page_code")));
        reportPageModel.setExplain(PubFunc.decodeGB(rs.getString(
            "report_explain")));

        //读取clob
        String clobValue = "";
        try {
          clobValue = rs.getString("content");;
          Debug.println("[ReportDAOImpl]getReportBuildPage--->>>get clob  ok!");
        }
        catch (Exception ex) {
          Debug.println(
              "[ReportDAOImpl]getReportBuildPage--->>>get clob  Exception:" +
              ex.getMessage());
        }

        reportPageModel.setContent(PubFunc.decodeGB(clobValue));
        reportPageModel.setBuildDate(PubFunc.decodeGB(rs.getString("build_date")));
        reportPageModel.setReportUnit(PubFunc.decodeGB(rs.getString(
            "report_unit")));
        reportPageModel.setReportFrom(PubFunc.decodeGB(rs.getString(
            "report_from")));
        reportPageModel.setReportTo(PubFunc.decodeGB(rs.getString("report_to")));
        reportPageModel.setReportType(PubFunc.decodeGB(rs.getString(
            "report_type")));
        reportPageModel.setReportRemark(PubFunc.decodeGB(rs.getString(
            "report_remark")));
        reportPageModel.setReportExtent(PubFunc.decodeGB(rs.getString(
            "report_extent")));
        reportPageModel.setReportDate(PubFunc.decodeGB(rs.getString(
            "report_date")));
        reportPageModel.setOperator(PubFunc.decodeGB(rs.getString("operator")));

      }
    }
    catch (SQLException sqle) {
      Debug.println("[ReportDAOImpl]getReportBuildPage--->>>Exception:" +
                    sqle.getMessage());
    }

    finally {
      try {
        this.closePreparedStatement();
      }
      catch (Exception e) {
      }
      try {
        this.closeResultSet();
      }
      catch (Exception e) {
      }
      try {
        this.closeStatement();
      }
      catch (Exception e) {
      }
      try {
        this.closeConnection();
      }
      catch (Exception e) {
      }
    }

    return reportPageModel;
  }

  /**
   * 得到报表记录数据 详细
   * 应用:得到报表记录数据
   */
  public ArrayList getReportBuildPage(ReportPageModel reportPageModel) throws
      AppException {
    boolean flag = false;
    String sql = null;
    ArrayList reportPageList = new ArrayList();
    ReportPageModel reportPageModelTemp = null;
    try {

      conn = Common.getConnection();
      stmt = conn.createStatement();

      //是否正常数据
      if (reportPageModel == null) {
        Debug.println(
            "[ReportDAOImpl]getReportBuildPage----->reportPageModel is null!");
        return reportPageList;
      }

      /*
                   code 编号
                   page_code 表名
                   explain 说明
                   content 内容
                   build_date 生成日期
                   report_unit 报表单位
                   report_from 起始日期
                   report_to 终止日期
                   report_type 期别
                   report_remark 报表备注
                   report_extent 报表范围
                   report_date报出日期
                   operator 填表人
       */

      sql = "select * from  "
          + TableNameUtil.UNIVERSAL_REPORT
          + " where ";
      if (reportPageModel.getCode() != null) {
        sql = sql + " code='" + reportPageModel.getCode() + "' and ";

      }
      if (reportPageModel.getPageCode() != null) {
        sql = sql + " page_code='" + reportPageModel.getPageCode() + "' and ";

      }
      if (reportPageModel.getExplain() != null) {
        sql = sql + " report_explain like'" + reportPageModel.getExplain() +
            "' and ";

//                if(reportPageModel.getContent()!=null)
//                  sql=sql+" content like'"+reportPageModel.getContent()+"' and ";

      }
      if (reportPageModel.getBuildDate() != null) {
        sql = sql + " build_date ='" + reportPageModel.getContent() + "' and ";

      }
      if (reportPageModel.getReportUnit() != null) {
        sql = sql + " report_unit ='" + reportPageModel.getReportUnit() +
            "' and ";

      }
      if (reportPageModel.getReportFrom() != null) {
        sql = sql + " report_from ='" + reportPageModel.getReportFrom() +
            "' and ";

      }
      if (reportPageModel.getReportTo() != null) {
        sql = sql + " report_to ='" + reportPageModel.getReportTo() + "' and ";

      }

      if (reportPageModel.getReportType() != null) {
        sql = sql + " report_type ='" + reportPageModel.getReportType() +
            "' and ";

      }
      if (reportPageModel.getReportRemark() != null) {
        sql = sql + " report_remark like'" + reportPageModel.getReportRemark() +
            "' and ";

      }

      if (reportPageModel.getReportExtent() != null) {
        sql = sql + " report_extent like'" + reportPageModel.getReportExtent() +
            "' and ";

      }
      if (reportPageModel.getReportDate() != null) {
        sql = sql + " report_date ='" + reportPageModel.getReportDate() +
            "' and ";

      }
      if (reportPageModel.getOperator() != null) {
        sql = sql + " operator like'" + reportPageModel.getOperator() +
            "' and ";

      }
      sql += " 1=1  order by code desc"; //补丁

      Debug.println("[ReportDAOImpl]getReportBuildPage--->>>sql:" + sql);
      rs = stmt.executeQuery(sql);

      while (rs.next()) {
        reportPageModelTemp = new ReportPageModel();
        reportPageModelTemp.setCode(PubFunc.decodeGB(rs.getString("code")));
        reportPageModelTemp.setPageCode(PubFunc.decodeGB(rs.getString(
            "page_code")));
        reportPageModelTemp.setExplain(PubFunc.decodeGB(rs.getString(
            "report_explain")));
        /*
                        //读取clob
                        String clobValue="";
                        try{
                                StringBuffer clobStrBuf = new StringBuffer();
                                Clob clob = rs.getClob("content");
                                if(clob != null){
                                    BufferedReader clobin = new BufferedReader(rs.getClob("content").getCharacterStream());
                                    int c;
                                    while((c = clobin.read()) != -1){
                                        clobStrBuf.append((char) c);
                                    }
                                    clobin.close();
                                clobValue = clobStrBuf.toString();
         Debug.println("[ReportDAOImpl]getReportBuildPage--->>>get clob  ok!");
                            }
                        }catch(Exception ex){
                            Debug.println("[ReportDAOImpl]getReportBuildPage--->>>get clob  Exception:"+ex.getMessage());
                            ex.printStackTrace();
                        }
         reportPageModel.setContent(PubFunc.decodeGB(clobValue));
         */
        reportPageModelTemp.setBuildDate(PubFunc.nullToStr(PubFunc.decodeGB(rs.
            getString("build_date"))));
        reportPageModelTemp.setReportUnit(PubFunc.nullToStr(PubFunc.decodeGB(rs.
            getString("report_unit"))));
        reportPageModelTemp.setReportFrom(PubFunc.nullToStr(PubFunc.decodeGB(rs.
            getString("report_from"))));
        reportPageModelTemp.setReportTo(PubFunc.nullToStr(PubFunc.decodeGB(rs.
            getString("report_to"))));
        reportPageModelTemp.setReportType(PubFunc.nullToStr(PubFunc.decodeGB(rs.
            getString("report_type"))));
        reportPageModelTemp.setReportRemark(PubFunc.nullToStr(PubFunc.decodeGB(
            rs.getString("report_remark"))));
        reportPageModelTemp.setReportExtent(PubFunc.nullToStr(PubFunc.decodeGB(