在daoImpl.java导出
来源：互联网 发布：怎么在淘宝注册店铺 编辑：程序博客网 时间：2024/06/04 00:30
billAction
excelStream = billService.BuildXMLDoc3(begin,end,grdj,gjdj,fl);
billService
//测试jdbc
public InputStream BuildXMLDoc3(String beginDate,String endDate, String grdj, String gjdj, String fl) throws IOException,SQLException;
billServiceImpl
//测试jdbc
public InputStream BuildXMLDoc3(String beginDate, String endDate,
String grdj, String gjdj, String fl) throws IOException,SQLException {
return billDao.BuildXMLDoc3(beginDate,endDate, grdj, gjdj, fl);
}
billDao
public InputStream BuildXMLDoc3(String beginDate,String endDate,String grdj, String gjdj, String fl)throws UnsupportedEncodingException,SQLException;
billDaoImpl
//测试jsbc
public InputStream BuildXMLDoc3(String beginDate,String endDate,String grdj, String gjdj, String fl) throws UnsupportedEncodingException, SQLException {
int count = this.countExportBillInfo2(beginDate, endDate,grdj,gjdj,fl);//总记录数
List list = null;
String sbh = this.getNsrsbh();
String mc = this.getNsrmc();
Date d = new Date(); //导出日期
SimpleDateFormat sd = new SimpleDateFormat("yyyyMMdd");
SimpleDateFormat sd2 = new SimpleDateFormat("yyyy-MM-dd");
String beginStr= "";//采集开始日期
if(beginDate !=null && !"".equals(beginDate)){
try {
Date date = sd2.parse(beginDate);
beginStr=sd.format(date);
} catch (ParseException e) {
e.printStackTrace();
}
}
String endStr = "";//采集结束日期
if(endDate !=null && !"".equals(endDate)){
try {
Date date = sd2.parse(endDate);
endStr=sd.format(date);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
StringBuffer sb = new StringBuffer();
sb.append("<?xml version=\"1.0\" encoding=\"GBK\"?>");
sb.append("<body>");
sb.append("<head>");
sb.append("<nsrsbh>");
sb.append(sbh);
sb.append("</nsrsbh>");
sb.append("<nsrmc>");
sb.append(mc);
sb.append("</nsrmc>");
sb.append("<dcrq>");
sb.append(sd.format(d));
sb.append("</dcrq>");
sb.append("<sssq_q>");
sb.append(beginStr);
sb.append("</sssq_q>");
sb.append("<sssq_z>");
sb.append(endStr);
sb.append("</sssq_z>");
sb.append("<records>");
sb.append(count);
sb.append("</records>");
sb.append("</head>");
sb.append("<data>");
Connection conn = this.getSession().connection();
Statement stmt = conn.createStatement();
String sql ="select D.FPDM,cast('吉林省中兴通数据科技有限公司' AS varchar2(20)) FPMC, " +
" A.PJH PJH, JFRQ,cast('ZC' as varchar2(4)) KPLX,B.YHMC,JFJE+A.ZNJ KPJE,'' YFPDM,'' YFPHM,cast(case fylb when '基本采暖费' then '基本采暖费' ELSE FYLB END as varchar2(15)) KPPM," +
" cast('平方米' as varchar2(8))DW,(case fylb when '基本采暖费' then to_number("+grdj+")*to_number("+fl+") ELSE "+grdj+" END) DJ,TRUNC((JFJE+A.ZNJ)/(case fylb when '基本采暖费' then to_number("+grdj+")*to_number("+fl+") ELSE to_number("+grdj+") END) ,2) sl,(JFJE+A.ZNJ) JFJE1 " +
" FROM SF_JMSF_T A,SF_JMYH_T B,SF_JMJS_T C,SF_PJXX_T D,SF_PJXX_MX_T E" +
" WHERE A.ZF=0 AND A.YHBH=B.YHBH AND A.YHBH=C.YHBH AND A.PJCH=D.PJCH AND A.CNQ=C.CNQ" +
" AND C.ZF=0 AND PJLB='机打发票' AND JFJE>0 AND A.PJH=E.PJH AND E.PJBH=D.BH ";
if(beginDate !=null && !"".equals(beginDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') >= '"+beginDate+"'";
}
if(endDate !=null && !"".equals(endDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') <='"+endDate+"'";
}
sql+=" UNION all " +
// " select D.FPDM,cast('吉林省中兴通数据科技有限公司' AS varchar2(20)) FPMC," +
// " A.FPH PJH, JFRQ,cast('ZC' as varchar2(4)) KPLX,B.YHMC,JFJE KPJE,'' YFPDM,'' YFPHM,FYLB KPPM," +
// " cast('吨' as varchar2(8))DW,170 DJ,TRUNC((JFJE/170) ,2),JFJE JFJE1" +
// " FROM SF_JLSF_T A,SF_JLYH_T B,SF_PJXX_T D,SF_PJXX_MX_T E" +
// " WHERE A.ZF=0 AND A.YHBH=B.BH AND A.FPCH=D.PJCH " +
// " AND FPLB='机打发票' AND JFJE>0 AND A.FPH=E.PJH AND E.PJBH=D.BH" ;
" select D.FPDM,cast('吉林省中兴通数据科技有限公司' AS varchar2(20)) FPMC," +
" A.FPH PJH, JFRQ,cast('ZC' as varchar2(4)) KPLX,B.YHMC,JFJE+A.ZNJ KPJE,'' YFPDM,'' YFPHM,cast(case fylb when '基本采暖费' then '基本采暖费' ELSE FYLB END as varchar2(15)) KPPM," +
" cast('平方米' as varchar2(8))DW,(case fylb when '基本采暖费' then to_number("+gjdj+")*to_number("+fl+") ELSE "+gjdj+" END) DJ,TRUNC((JFJE+A.ZNJ)/(case fylb when '基本采暖费' then to_number("+gjdj+")*to_number("+fl+") ELSE to_number("+gjdj+") END) ,2),(JFJE+A.ZNJ) JFJE1" +
" FROM SF_DWSF_T A,SF_DWYH_T B,SF_DWJS_T C,SF_PJXX_T D,SF_PJXX_MX_T E" +
" WHERE A.ZF=0 AND A.YHBH=B.BH AND A.YHBH=C.YHBH AND A.FPCH=D.PJCH AND A.CNQ=C.CNQ" +
" AND C.ZF=0 AND FPLB='机打发票' AND JFJE>0 AND A.FPH=E.PJH AND E.PJBH=D.BH" ;
if(beginDate !=null && !"".equals(beginDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') >= '"+beginDate+"'";
}
if(endDate !=null && !"".equals(endDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') <='"+endDate+"'";
}
sql+=" UNION all " +
// " select D.FPDM,cast('吉林省中兴通数据科技有限公司' AS varchar2(20)) FPMC," +
// " A.FPH PJH, JFRQ,cast('ZC' as varchar2(4)) KPLX,B.YHMC,JFJE+A.ZNJ KPJE,'' YFPDM,'' YFPHM,cast(case fylb when '基本采暖费' then '基本采暖费' ELSE FYLB END as varchar2(15)) KPPM," +
// " cast('平方米' as varchar2(8))DW,(case fylb when '基本采暖费' then to_number("+gjdj+")*to_number("+fl+") ELSE "+gjdj+" END) DJ,TRUNC((JFJE+A.ZNJ)/(case fylb when '基本采暖费' then to_number("+gjdj+")*to_number("+fl+") ELSE to_number("+gjdj+") END) ,2),(JFJE+A.ZNJ) JFJE1" +
// " FROM SF_DWSF_T A,SF_DWYH_T B,SF_DWJS_T C,SF_PJXX_T D,SF_PJXX_MX_T E" +
// " WHERE A.ZF=0 AND A.YHBH=B.BH AND A.YHBH=C.YHBH AND A.FPCH=D.PJCH AND A.CNQ=C.CNQ" +
// " AND C.ZF=0 AND FPLB='机打发票' AND JFJE>0 AND A.FPH=E.PJH AND E.PJBH=D.BH" ;
" select D.FPDM,cast('吉林省中兴通数据科技有限公司' AS varchar2(20)) FPMC," +
" A.FPH PJH, JFRQ,cast('ZC' as varchar2(4)) KPLX,B.YHMC,JFJE KPJE,'' YFPDM,'' YFPHM,FYLB KPPM," +
" cast('吨' as varchar2(8))DW,170 DJ,TRUNC((JFJE/170) ,2),JFJE JFJE1" +
" FROM SF_JLSF_T A,SF_JLYH_T B,SF_PJXX_T D,SF_PJXX_MX_T E" +
" WHERE A.ZF=0 AND A.YHBH=B.BH AND A.FPCH=D.PJCH " +
" AND FPLB='机打发票' AND JFJE>0 AND A.FPH=E.PJH AND E.PJBH=D.BH" ;
if(beginDate !=null && !"".equals(beginDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') >= '"+beginDate+"'";
}
if(endDate !=null && !"".equals(endDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') <='"+endDate+"'";
}
sql+=" UNION all " +
" select D.FPDM,cast('吉林省中兴通数据科技有限公司' AS varchar2(20)) FPMC," +
" A.PJH PJH, JFRQ,cast('ZF' as varchar2(4)) KPLX,B.YHMC,JFJE+A.ZNJ KPJE,'' YFPDM,'' YFPHM,cast(case fylb when '基本采暖费' then '基本采暖费' ELSE FYLB END as varchar2(15)) KPPM," +
" cast('平方米' as varchar2(8))DW,(case fylb when '基本采暖费' then to_number("+grdj+")*to_number("+fl+") ELSE "+grdj+" END) DJ,TRUNC((JFJE+A.ZNJ)/(case fylb when '基本采暖费' then to_number("+grdj+")*to_number("+fl+") ELSE to_number("+grdj+") END),2),(JFJE+A.ZNJ) JFJE1" +
" FROM SF_JMSF_T A,SF_JMYH_T B,SF_JMJS_T C,SF_PJXX_T D,SF_PJXX_MX_T E" +
" WHERE A.ZF=1 AND A.YHBH=B.YHBH AND A.YHBH=C.YHBH AND A.PJCH=D.PJCH AND A.CNQ=C.CNQ" +
" AND C.ZF=0 AND PJLB='机打发票' AND A.PJH=E.PJH AND E.PJBH=D.BH" ;
if(beginDate !=null && !"".equals(beginDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') >= '"+beginDate+"'";
}
if(endDate !=null && !"".equals(endDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') <='"+endDate+"'";
}
sql+=" UNION all " +
" select D.FPDM,cast('吉林省中兴通数据科技有限公司' AS varchar2(20)) FPMC," +
" A.FPH PJH,JFRQ,cast('ZF' as varchar2(4)) KPLX,B.YHMC,JFJE+A.ZNJ KPJE,'' YFPDM,'' YFPHM,cast(case fylb when '基本采暖费' then '基本采暖费' ELSE FYLB END as varchar2(15)) KPPM," +
" cast('平方米' as varchar2(8))DW,(case fylb when '基本采暖费' then to_number("+gjdj+")*to_number("+fl+") ELSE "+gjdj+" END) DJ,TRUNC((JFJE+A.ZNJ)/(case fylb when '基本采暖费' then to_number("+gjdj+")*to_number("+fl+") ELSE to_number("+gjdj+") END),2),(JFJE+A.ZNJ) SFJE1" +
" FROM SF_DWSF_T A,SF_DWYH_T B,SF_DWJS_T C,SF_PJXX_T D,SF_PJXX_MX_T E" +
" WHERE A.ZF=1 AND A.YHBH=B.BH AND A.YHBH=C.YHBH AND A.FPCH=D.PJCH AND A.CNQ=C.CNQ" +
" AND C.ZF=0 AND FPLB='机打发票' AND A.FPH=E.PJH AND E.PJBH=D.BH" ;
if(beginDate !=null && !"".equals(beginDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') >= '"+beginDate+"'";
}
if(endDate !=null && !"".equals(endDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') <='"+endDate+"'";
}
sql+=" UNION all " +
" select D.FPDM,cast('吉林省中兴通数据科技有限公司' AS varchar2(20)) FPMC," +
" A.FPH PJH, JFRQ,cast('ZF' as varchar2(4)) KPLX,B.YHMC,JFJE KPJE,'' YFPDM,'' YFPHM,FYLB KPPM," +
" cast('吨' as varchar2(8))DW,170 DJ,TRUNC((JFJE/170) ,2),JFJE JFJE1" +
" FROM SF_JLSF_T A,SF_JLYH_T B,SF_PJXX_T D,SF_PJXX_MX_T E" +
" WHERE A.ZF=1 AND A.YHBH=B.BH AND A.FPCH=D.PJCH " +
" AND FPLB='机打发票' AND A.FPH=E.PJH AND E.PJBH=D.BH" ;
if(beginDate !=null && !"".equals(beginDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') >= '"+beginDate+"'";
}
if(endDate !=null && !"".equals(endDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') <='"+endDate+"'";
}
sql+=" UNION all " +
" select D.FPDM,cast('吉林省中兴通数据科技有限公司' AS varchar2(20)) FPMC," +
" A.PJH PJH,JFRQ,cast('HZ' as varchar2(4)) KPLX,B.YHMC,JFJE+A.ZNJ KPJE," +
" (select fpDm from sf_pjxx_t where pjch=E.ZFPCH and pjlx='机打发票') ZFPCH, E.ZFPH,cast(case fylb when '基本采暖费' then '基本采暖费' ELSE FYLB END as varchar2(15)) KPPM," +
" cast('平方米' as varchar2(8))DW,(case fylb when '基本采暖费' then to_number("+grdj+")*to_number("+fl+") ELSE "+grdj+" END) DJ,ABS(TRUNC((JFJE+A.ZNJ)/(case fylb when '基本采暖费' then to_number("+grdj+")*to_number("+fl+") ELSE to_number("+grdj+") END),2)),(JFJE+A.ZNJ) JFJE1" +
" FROM SF_JMSF_T A,SF_JMYH_T B,SF_JMJS_T C,SF_PJXX_T D,SF_PJXX_MX_T E" +
" WHERE A.ZF=0 AND A.YHBH=B.YHBH AND A.YHBH=C.YHBH AND A.PJCH=D.PJCH AND A.CNQ=C.CNQ" +
" AND C.ZF=0 AND PJLB='机打发票' AND JFJE<0 AND A.PJH=E.PJH AND E.PJBH=D.BH " ;
if(beginDate !=null && !"".equals(beginDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') >= '"+beginDate+"'";
}
if(endDate !=null && !"".equals(endDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') <='"+endDate+"'";
}
sql+=" UNION all " +
" select D.FPDM,cast('吉林省中兴通数据科技有限公司' AS varchar2(20)) FPMC," +
" A.FPH PJH,JFRQ,cast('HZ' as varchar2(4)) KPLX,B.YHMC,JFJE+A.ZNJ KPJE," +
" (select fpDm from sf_pjxx_t where pjch=E.ZFPCH and pjlx='机打发票') ZFPCH, E.ZFPH,cast(case fylb when '基本采暖费' then '基本采暖费' ELSE FYLB END as varchar2(15)) KPPM," +
" cast('平方米' as varchar2(8))DW,(case fylb when '基本采暖费' then to_number("+gjdj+")*to_number("+fl+") ELSE "+gjdj+" END) DJ,ABS(TRUNC((JFJE+A.ZNJ)/(case fylb when '基本采暖费' then to_number("+gjdj+")*to_number("+fl+") ELSE to_number("+gjdj+") END) ,2)),(JFJE+A.ZNJ) JFJE1" +
" FROM SF_DWSF_T A,SF_DWYH_T B,SF_DWJS_T C,SF_PJXX_T D,SF_PJXX_MX_T E" +
" WHERE A.ZF=0 AND A.YHBH=B.BH AND A.YHBH=C.YHBH AND A.FPCH=D.PJCH AND A.CNQ=C.CNQ" +
" AND C.ZF=0 AND FPLB='机打发票' AND JFJE<0 AND A.FPH=E.PJH AND E.PJBH=D.BH";
if(beginDate !=null && !"".equals(beginDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') >= '"+beginDate+"'";
}
if(endDate !=null && !"".equals(endDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') <='"+endDate+"'";
}
sql+=" UNION all " +
" select D.FPDM,cast('吉林省中兴通数据科技有限公司' AS varchar2(20)) FPMC," +
" A.FPH PJH, JFRQ,cast('HZ' as varchar2(4)) KPLX,B.YHMC,JFJE KPJE,(select fpDm from sf_pjxx_t where pjch=E.ZFPCH and pjlx='机打发票') ZFPCH, E.ZFPH,FYLB KPPM," +
" cast('吨' as varchar2(8))DW,170 DJ,TRUNC((JFJE/170) ,2),JFJE JFJE1" +
" FROM SF_JLSF_T A,SF_JLYH_T B,SF_PJXX_T D,SF_PJXX_MX_T E" +
" WHERE A.ZF=1 AND A.YHBH=B.BH AND A.FPCH=D.PJCH " +
" AND FPLB='机打发票' AND JFJE<0 AND A.FPH=E.PJH AND E.PJBH=D.BH" ;
if(beginDate !=null && !"".equals(beginDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') >= '"+beginDate+"'";
}
if(endDate !=null && !"".equals(endDate)){
sql +=" and to_char(jfrq,'yyyy-MM-dd') <='"+endDate+"'";
}
java.sql.ResultSet rs = stmt.executeQuery(sql);
System.out.println(sql);
int id = 1;
while (rs.next()) {
sb.append("<record id=\"" + (id++) + "\">");
sb.append("<fpdm>");
sb.append(rs.getString("fpdm"));
sb.append("</fpdm><fpmc>");
sb.append(rs.getString("fpmc"));
sb.append("</fpmc><fphm>");
sb.append(rs.getString("pjh"));
sb.append("</fphm><kprq>");
sb.append(rs.getString("jfrq"));
sb.append("</kprq><kplx>");
sb.append(rs.getString("kplx"));
sb.append("</kplx><fkfmc>");
sb.append(rs.getString("yhmc"));
sb.append("</fkfmc><jehj>");
sb.append(rs.getString("kpje"));
sb.append("</jehj><yfpdm>");
sb.append(rs.getString("yfpdm"));
sb.append("</yfpdm><yfphm>");
sb.append(rs.getString("yfphm"));
sb.append("</yfphm><pm>");
sb.append(rs.getString("kppm"));
sb.append("</pm><dw>");
sb.append(rs.getString("dw"));
sb.append("</dw><dj>");
sb.append(rs.getString("dj"));
sb.append("</dj><sl>");
sb.append(rs.getString("sl"));
sb.append("</sl><je>");
sb.append(rs.getString("jfje1"));
sb.append("</je></record>");
}
// list = this.exportBillInfo2(beginDate, endDate,grdj,gjdj,fl, context);//拼接字符串
// for(int j = 0; j < list.size(); j++){//150000
// Object[] obj = (Object[]) list.get(j);
// sb.append("<record id=\"" + (id++) + "\">");
// sb.append("<fpdm>");
// sb.append(obj[0]);
// sb.append("</fpdm>");
// sb.append("<fpmc>");
// sb.append("松原市热力公司发票");
// sb.append("</fpmc>");
// sb.append("<fphm>");
// sb.append(obj[2]);
// sb.append("</fphm>");
// sb.append("<kprq>");
// sb.append(obj[3]);
// sb.append("</kprq>");
// sb.append("<kplx>");
// sb.append(obj[4]);
// sb.append("</kplx>");
// sb.append("<fkfmc>");
// sb.append(obj[5]);
// sb.append("</fkfmc>");
// sb.append("<jehj>");
// sb.append(obj[6]);
// sb.append("</jehj>");;
// sb.append("<yfpdm>");
// sb.append(obj[7]==null?"":obj[7]+"");
// sb.append("</yfpdm>");
// sb.append("<yfphm>");
// sb.append(obj[8]==null?"":obj[8]+"");
// sb.append("</yfphm>");
// sb.append("<pm>");
// sb.append(obj[9]);
// sb.append("</pm>");
// sb.append("<dw>");
// sb.append(obj[10]);
// sb.append("</dw>");
// sb.append("<dj>");
// sb.append(obj[11]);
// sb.append("</dj>");
// sb.append("<sl>");
// sb.append(obj[12]);
// sb.append("</sl>");
// sb.append("<je>");
// sb.append(obj[13]);
// sb.append("</je>");
// sb.append("</record>");
// }
sb.append("</data>");
sb.append("</body>");
ByteArrayInputStream is = new ByteArrayInputStream(sb.toString().getBytes("GBK"));
// InputStream is = new java.io.StringBufferInputStream(sb.toString().get);
return is;
}
- 在daoImpl.java导出
- src/number/DaoImpl.java
- DaoImpl
- MyBatis入门实例 ——DaoImpl.java
- Java.NullPointException 之 Service(业务层) 空 调DAOImpl实例方法，执行报错问题
- SpringMVC的Dao层的DaoImpl.java将数据插入数据库
- java实现人脸识别源码【含测试效果图】——DaoImpl层（BaseDaoUtilImpl）
- java实现人脸识别源码【含测试效果图】——DaoImpl层（UserDaoImpl）
- 在Java中导入导出txt
- 通用DAO和DaoImpl
- dongzuo 模板 活动daoImpl
- 公共Dao DaoImpl
- .DAOImpl session 得不到的问题
- DaoImpl增删改查实例
- 编写DAOImpl的JUnit测试
- Java实现任意文件在bmp中的隐藏与导出
- Java实现任意文件在bmp中的隐藏与导出
- java 导出文件在不同的浏览器上名称乱码
- Ubuntu软件包管理命令
- 完美排序10000条数据
- apache commons类 介绍
- Android虚拟机 Dalvik
- Eclipse使用Maven创建Web时错误：Could not resolve archetype org.apache.maven.archetypes:maven-archetype-webap
- 在daoImpl.java导出
- 浅谈网络语音技术
- JQuery Layer应用示例
- 学习心得---策略模式
- 几种手势操作的最基本实现
- BloomFilter of Guava
- vs下设置动态库输出文件名
- 基于Gist特征的相似图像检索
- 解析下HibernateSessionFactory.java
