- 论坛徽章:
- 0
|
由于这段代码查询时间过长,因此,希望能将它改写成sp,但小妹我没写过,希望高手指点一二,最好能写个完整的范例出来,感激不尽!!!
- public boolean setConn(String CUSTNO,String Qdate)
- {
- // int totalRows=1;
- int greatUnit=0;
- int smallUnit=0;
- int nm_ly3204=0,nm_ly3205=0,nm_ly3206=0,nm_ly3211=0,nm_ly3208=0;
- int ax_ly3204=0,ax_ly3205=0,ax_ly3206=0,ax_ly3211=0,ax_ly3208=0;
- String sql2="";
- String sql3="";
-
- ResultSet rs;
- ResultSet rs2;
- ResultSet rs3;
-
- String ly1105="";//名称
- String ly3202="";
- String ly3203="";
- String ly1146="";
- int ly3201=0;
- int ly3204=0;
- int ly3205=0;
- int ly3206=0;
- int ly3211=0;
- int ly3208=0;
- int sum_ly3205=0,sum_ly3206=0;
- int goodsRate=0;//转换率
- float ly3211_b=0.00f,nm_ly3211_b=0.00f,ax_ly3211_b=0.00f;
- String rowLine;
- String fromdate;
- fromdate=Qdate.substring(0,6);
- fromdate=fromdate+"01";
- String flag="";
- try
- {
- db1=new getFromPool();
- if(!db1.isRight)
- {
- db1.restartConn();
- }
- if(!db1.isRight)
- {
- db1=new getFromPool(1);
- }
- db2=new getFromPool();
- if(!db2.isRight)
- {
- db2.restartConn();
- }
- if(!db2.isRight)
- {
- db2=new getFromPool(1);
- }
- Statement stmt = db1.getStatement();
- Statement stmt2 = db2.getStatement();
- String sql="select distinct ly3203 from LYPLIBS.lyf32 a inner join LYPLIBS.lyf11 b on a.ly3230=b.ly1120 and a.ly3212=b.ly1132 and a.ly3203=b.ly1101 where ly3230='"+CUSTNO+"' and ly3201="+Qdate+" and ly3212='"+DcNo+"'";
- System.out.println(sql);
- boolean isRS = stmt.execute(sql);
- if (isRS)
- {
- rs=stmt.getResultSet();
- while(rs.next())
- {
- ly3203=rs.getString(1);
-
- //求共同资料
- sql2="select ly1105,ly1146,ly1112 from LYPLIBS.lyf11 where ly1101='"+ly3203+"' and ly1120='"+CUSTNO+"'";
- System.out.println(sql2);
- rs2=stmt2.executeQuery(sql2);
- if(rs2.next())
- {
- ly1105=rs2.getString(1);
- ly1146=rs2.getString(2);
- goodsRate=rs2.getInt(3);
- }
- rs2.close();
-
- //求A品数量
- sql2="select ly3201,ly3204,ly3205,ly3206,ly3211,ly3208 from LYPLIBS.lyf32 where ly3230='"+CUSTNO+"' and ly3201="+Qdate+" and ly3203='"+ly3203+"' and ly3202='NM'";
- System.out.println(sql2);
- rs2=stmt2.executeQuery(sql2);
- if (rs2.next())
- {
- ly3201=rs2.getInt(1);
- nm_ly3204=rs2.getInt(2);
- nm_ly3205=rs2.getInt(3);
- nm_ly3206=rs2.getInt(4);
- nm_ly3211=rs2.getInt(5);
- nm_ly3208=rs2.getInt(6);
- }
- else
- {
- nm_ly3204=0;
- nm_ly3205=0;
- nm_ly3206=0;
- nm_ly3211=0;
- nm_ly3208=0;
- }
- rs2.close();
-
- //求B品数量
- sql2="select ly3201,ly3204,ly3205,ly3206,ly3211,ly3208 from LYPLIBS.lyf32 where ly3230='"+CUSTNO+"' and ly3201="+Qdate+" and ly3203='"+ly3203+"' and ly3202='AX'";
- System.out.println(sql2);
- rs2=stmt2.executeQuery(sql2);
- if (rs2.next())
- {
- ly3201=rs2.getInt(1);
- ax_ly3204=rs2.getInt("ly3204");
- ax_ly3205=rs2.getInt("ly3205");
- ax_ly3206=rs2.getInt("ly3206");
- ax_ly3211=rs2.getInt("ly3211");
- ax_ly3208=rs2.getInt("ly3208");
- }
- else
- {
- ax_ly3204=0;
- ax_ly3205=0;
- ax_ly3206=0;
- ax_ly3211=0;
- ax_ly3208=0;
- }
- rs2.close();
- sql2=null;
-
- ly3204=nm_ly3204+ax_ly3204;
- ly3205=nm_ly3205+ax_ly3205;
- ly3206=nm_ly3206+ax_ly3206;
- ly3211=nm_ly3211+ax_ly3211;
- ly3208=nm_ly3208+ax_ly3208;
-
- if (goodsRate==0)
- {
- ly3211_b=0;
- nm_ly3211_b=0;
- ax_ly3211_b=0;
- flag="单位转换运算出错,请修改";
- System.out.println(flag);
- }
- else
- {
- ly3211_b=ly3211/goodsRate;
- nm_ly3211_b=nm_ly3211/goodsRate;
- ax_ly3211_b=ax_ly3211/goodsRate;
- }
- total5+=ly3204;
- total6+=ly3205;
- total7+=ly3206;
- total8+=ly3211;
- total16+=ly3208;
- total9+=ly3211_b;
- total10+=nm_ly3211_b;
- total11+=nm_ly3211;
- total12+=ax_ly3211_b;
- total13+=ax_ly3211;
-
-
- writeFile.writeCell(0,totalRows,ly3201,null);
- writeFile.writeCell(1,totalRows,-0,ly3203);
- writeFile.writeCell(2,totalRows,-0,ly1105);
- writeFile.writeCell(3,totalRows,-0,ly1146);
- writeFile.writeCell(4,totalRows,goodsRate,null);
- writeFile.writeCell(5,totalRows,ly3204,null);
- writeFile.writeCell(6,totalRows,ly3205,null);
- writeFile.writeCell(7,totalRows,ly3206,null);
- writeFile.writeCell(8,totalRows,ly3211,null);
- writeFile.writeCell2(9,totalRows,ly3211_b,null);
- writeFile.writeCell2(10,totalRows,nm_ly3211_b,null);
- writeFile.writeCell(11,totalRows,nm_ly3211,null);
- writeFile.writeCell2(12,totalRows,ax_ly3211_b,null);
- writeFile.writeCell(13,totalRows,ax_ly3211,null);
- writeFile.writeCell(14,totalRows,ly3208,null);
- sql2="select sum(ly3205),sum(ly3206) from LYPLIBS.LYF32 where ly3230='"+CUSTNO+"' and ly3201>;="+fromdate+" and ly3201<="+Qdate+" and ly3203='"+ly3203+"'";
- System.out.println(sql2);
- rs2=stmt2.executeQuery(sql2);
- if(rs2.next())
- {
- sum_ly3205=rs2.getInt(1);
- sum_ly3206=rs2.getInt(2);
- writeFile.writeCell(15,totalRows,sum_ly3205,null);
- writeFile.writeCell(16,totalRows,sum_ly3206,null);
- }
- rs2.close();
- sql2="";
- total14+=sum_ly3205;
- total15+=sum_ly3206;
- totalRows++;
- }
- rs.close();
- stmt2.close();
- }
- else
- {
- System.out.println("当时无相关记录");
- }
- stmt.close();
- }
- catch(Exception e)
- {
- e.printStackTrace();
- errorMsg="数据查询出错";
- return false;
- }
- finally
- {
- try
- {
- db1.getConnection().close();
- db2.getConnection().close();
- }catch(Exception dd){
- dd.printStackTrace();
- }
-
- }
- return true;
-
- }
复制代码 |
|