querydaoimpl.java

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

                            + " a,"
                            + TableNameUtil.GSGSJOUT_CODING_ECONOMY
                            + " b,a_bm_ztfl d where a.field020 = b.coding(+) and a.field023=d.bm(+)) resultTable" //yuxiaogang20040401\u00D0\u00DE\u00B8\u00C4\u00A3\u00BA\u00C8\u00A5\u00B5\u00F4\u00C1\u00CB\u00CF\u00DE\u00D6\u00C6\u00CC\u00F5\u00BC\u00FEa.field023 is not null and
                            + " where  ";
                        sql += tempSql;
                        sql += ") ";

                    } else{
                        sql = "select count(*) from (select * from  v_basis02_sub02 where ";
                        sql += tempSql;
                        sql += " ) ";
                    }


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

            if(rs.next()){
                superQueryCount = rs.getString(1);
            }
        } catch(SQLException sqle){
            Debug.println("[QueryDAOImpl]getSuperBiaodashiQueryCount--->>>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){
            }
        }
        Debug.println("[QueryDAOImpl]getSuperBiaodashiQueryCount--->>>superQueryCount:" + superQueryCount);
        return superQueryCount;
    }


    /**
     * 获得未年检企业mail List
     * 应用:年检查询
     */
    public ArrayList getUnPassYearcheckupPrintList(String tempSql,ArrayList querySelectPrintItem) throws
        AppException{
        boolean flag = false;
        String sql = null;
        ArrayList queryResultList = new ArrayList();
        QuerySuperResultModel querySuperResultModel = null;
        String strTemp;
        String strTemp2;
        int i = 0;
        try{

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

            //是否正常数据
            if(tempSql == null || querySelectPrintItem.size() == 0){
                Debug.println(
                    "[QueryDAOImpl]getUnPassYearcheckupPrintList----->tempSql is null or querySelectPrintItem.size=0!");
                return queryResultList;
            }

            /*
             field001     SN;//事务号
             field002     BID;//业务类型
             field004     RegID;//注册号
             field003     innerID;//内部号
             field009     corporationName;//企业名称
             field020     corporationType;//企业类型
             field020     corporationTypeName;//企业类型名称
             field019     principal; //负责人
             field033     abode;//住所
             field038     phonenumber;//联系电话（登记时）
             field037     postalcode;//邮政编码
             field067     workArea;//经营范围
             field096     practiceDate;//成立日期
             field105     GXDW;//管辖单位
             field104     DJJG;//登记机关
             field014     wordName;//字号
             field015     wordNameSpelling;//字号拼音
             field011     corporationEngName;//企业外文名
             field022     tradeCode;//行业代码
             field106     transactionOrgan;//经办科室
             field073     directorUnit;//主管单位
             field016     country;//国家
             field005     unificationCode;//统一代码
             field049     registrationPrincipal;//注册资本
             FIELD050     moneyCategory;//资金货种
             field099     approveDate;//核准日期
             field110     logoutDate;//注销日期
             field097     businessTimelimitFrom;//营业期限自
             field098    businessTimelimitTo;//营业期限至
             */
            sql = "SELECT * FROM (select a.* FROM (SELECT * FROM "
                + TableNameUtil.GSGSJIN_BASIS02
                + " where 1=1 ";
            sql += tempSql;

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

            Debug.println("[QueryDAOImpl]getUnPassYearcheckupPrintList--->>>star!");

            while(rs.next()){
                if(i < querySelectPrintItem.size() && "1".equals(querySelectPrintItem.get(i))){
                    querySuperResultModel = new QuerySuperResultModel();
                    querySuperResultModel.setSN(PubFunc.decodeGB(rs.getString("field001")));
                    strTemp = PubFunc.decodeGB(rs.getString("field002"));
                    strTemp2 = PubFunc.decodeGB(rs.getString("field006"));
                    querySuperResultModel.setBID(PubFunc.decodeGB(rs.getString("field002")));
                    querySuperResultModel.setInnerID(PubFunc.decodeGB(rs.getString("field003")));
                    querySuperResultModel.setRegID(PubFunc.decodeGB(rs.getString("field004")));
                    querySuperResultModel.setCorporationName(PubFunc.decodeGB(rs.getString("field009")));
                    querySuperResultModel.setCorporationType(PubFunc.decodeGB(rs.getString("field020")));
                    //querySuperResultModel.setCorporationTypeName(PubFunc.decodeGB(rs.getString("content")));
///////////////////////////////////////////////////////////////////////////////////////////////
                    /*                    if(strTemp != null && strTemp.length() > 2){
                                            strTemp = strTemp.substring(0,2);
                                            if("01".equals(strTemp)){
                                                querySuperResultModel.setCorporationState("名称");
                                            } else
                                            if("02".equals(strTemp)){
                                                querySuperResultModel.setCorporationState("登记");
                                            } else
                                            if("03".equals(strTemp)){
                                                querySuperResultModel.setCorporationState("变更");
                                            } else
                                            if("04".equals(strTemp)){
                                                querySuperResultModel.setCorporationState("备案");
                                            } else
                                            if("05".equals(strTemp)){
                                                querySuperResultModel.setCorporationState("改制");
                                            } else
                                            if("06".equals(strTemp)){
                                                querySuperResultModel.setCorporationState("迁入迁出");
                                            } else
                                            if("07".equals(strTemp)){
                                                querySuperResultModel.setCorporationState("年检");
                                            } else
                                            if("08".equals(strTemp)){
                                                querySuperResultModel.setCorporationState("注销");
                                            } else
                                            if("09".equals(strTemp)){
                                                querySuperResultModel.setCorporationState("吊销");
                                            }
                                        } else{
                                            querySuperResultModel.setCorporationState("");
                                        }
                     */
//////////////////////////////////////////////////////////////////////////////////////////////////
                    if(strTemp != null && "01".equals(strTemp.substring(0,2))){
                        querySuperResultModel.setCorporationState("名称");
                    } else
                    if(strTemp != null && "07".equals(strTemp.substring(0,2))){
                        querySuperResultModel.setCorporationState("年检");
                    } else if(strTemp != null && strTemp2 != null && "02".equals(strTemp.substring(0,2))){
                        if("1".equals(strTemp2)){
                            querySuperResultModel.setCorporationState("登记");
                        } else
                        if("2".equals(strTemp2)){
                            querySuperResultModel.setCorporationState("注销");
                        } else
                        if("3".equals(strTemp2)){
                            querySuperResultModel.setCorporationState("吊销");
                        } else
                        if("4".equals(strTemp2)){
                            querySuperResultModel.setCorporationState("迁入");
                        } else
                        if("5".equals(strTemp2)){
                            querySuperResultModel.setCorporationState("迁出");
                        }
                    } else{
                        querySuperResultModel.setCorporationState("");
                    }

                    querySuperResultModel.setPrincipal(PubFunc.decodeGB(rs.getString("field019")));
                    querySuperResultModel.setPhonenumber(PubFunc.decodeGB(rs.getString("field038")));
                    querySuperResultModel.setPostalcode(PubFunc.decodeGB(rs.getString("field037")));
                    querySuperResultModel.setAbode(PubFunc.decodeGB(rs.getString("field033")));

                    querySuperResultModel.setWorkArea(PubFunc.decodeGB(rs.getString("field067")));
                    querySuperResultModel.setPracticeDate(PubFunc.decodeGB(rs.getString("field096")));
                    querySuperResultModel.setPunishID("");
                    querySuperResultModel.setLogoutRevokeDate(PubFunc.decodeGB(rs.getString("field110")));

                    queryResultList.add(querySuperResultModel);
                }
                i++;
            }
            Debug.println("[QueryDAOImpl]getUnPassYearcheckupPrintList--->>>end!");
        } catch(SQLException sqle){
            Debug.println("[QueryDAOImpl]getUnPassYearcheckupPrintList--->>>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){
            }
        }
        Debug.println("[QueryDAOImpl]getUnPassYearcheckupPrintList--->>>queryResultList.size():" + queryResultList.size());
        return queryResultList;
    }

    /**
     * 获得查询结果
     * 应用: 超级查询 查询basisc02
     */
    public ArrayList getSuperPrintList(String tempSql,ArrayList querySelectPrintItem) throws
        AppException{
        boolean flag = false;
        String sql = null;
        ArrayList queryResultList = new ArrayList();
        QuerySuperResultModel querySuperResultModel = null;
        String strTemp;
        String strTemp2;
        int i = 0;
        try{

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

            //是否正常数据
            if(tempSql == null || querySelectPrintItem.size() == 0){
                Debug.println(
                    "[QueryDAOImpl]getSuperPrintList----->tempSql is null or querySelectPrintItem.size=0!");
                return queryResultList;
            }

            /*
             field001     SN;//事务号
             field002     BID;//业务类型
             field004     RegID;//注册号
             field003     innerID;//内部号
             field009     corporationName;//企业名称
             field020     corporationType;//企业类型
             field020     corporationTypeName;//企业类型名称
             field019     principal; //负责人
             field033     abode;//住所
             field038     phonenumber;//联系电话（登记时）
             field037     postalcode;//邮政编码
             field067     workArea;//经营范围
             field096     practiceDate;//成立日期
             field105     GXDW;//管辖单位
             field104     DJJG;//登记机关
             field014     wordName;//字号
             field015     wordNameSpelling;//字号拼音
             field011     corporationEngName;//企业外文名
             field022     tradeCode;//行业代码
             field106     transactionOrgan;//经办科室
             field073     directorUnit;//主管单位