レポートバックグラウンドデータ処理

7804 ワード

//1.informix     , 。

//update statistics for table year_decrsubjtotal



1. raq datestart , form  。

	var d_s = form.datekey.value;

        	var d_start = d_s.substr(0,4);

        	var m = d_s.substr(5,2);

        	

			if(m=='04' || m=='05' || m=='06'){

				d_start = d_start+"-04-01";

			}else if(m=='07' || m=='08' || m=='09'){

				d_start = d_start+"-07-01";

			}else if(m=='10' || m=='11' || m=='12'){

				d_start = d_start+"-10-01";

			}else{

				d_start = d_start+"-01-01";

			}

			

			form.datestart.value = d_start;

			

2. 。

	(1).cbsCard_No_tran_q.java( Object[] objs = (Object[]) maprst.get(ikey)  ) //16

	String isql = "insert into card_no_tran_q(datekey,quarter," +

						"deptid,dqfkd,qmkld,qmkslp," +

						"ctrancx,ctrancxamt,ctranqx,ctranqxamt,ctranxf,ctranxfamt," +

						"ctranzz,ctranqzzamt,atmsbcnt,ctranatm,ctranatmamt)" +

						" VALUES('" + oDateKeyStr+ "','"+quarter+"',?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	

	(2).cbsPaymentStruc_q.java(insert into * select * from  )

	sql = "delete from paymentStruc_q where year =" + i_year + " and quarter=" + i_quarter;

			logger.info("sql=" + sql);

			stm.executeUpdate(sql);

			/*  paymentStruc paymentStruc_q  */

			sql = "insert into paymentStruc_q (year,quarter,deptid,Itemid,qcnt,qamt) select " + i_year + "," + i_quarter + ",deptid,Itemid,sum(cnt),sum(amt) from paymentStruc where datekey>='"

					+ Qbegin + "' and datekey<='" + date + "' group by deptid,Itemid";

			logger.info("sql=" + sql);

	(3).cbsBillBusiness_q6016( )

	String sql= "select deptid,sum(case when subjid in ('80602','80604') then crcnt else 0 end) as zl1cnt,sum(case when subjid in ('80602','80604') then cramt else 0 end) as zl1amt, sum(case when"+

				 " subjid in ('13010102','13010104') then decnt else 0 end) as zl2cnt,sum(case when subjid in ('13010102','13010104') then deamt else 0 end) as zl2amt,sum(case when subjid in"+

				 " ('13010102','13010104') and datekey='"+date+"' then debal else 0 end) as zl2bal, sum(case when subjid in ('60201','60202') then crcnt else 0 end) as zl3cnt,sum(case when subjid in ('60201','60202') then"+

				 " cramt else 0 end) as zl3amt,sum(case when subjid in ('60201','60202') and datekey='"+date+"' then crbal else 0 end) as zl3bal, sum(case when subjid in ('13010101','13010103') then decnt else 0 end) as"+

				 " zl4cnt,sum(case when subjid in ('13010101','13010103') then deamt else 0 end) as zl4amt,sum(case when subjid in ('13010101','13010103') and datekey='"+date+"' then debal else 0 end) as zl4bal, sum(case"+

				 " when subjid in ('20210111','20210112','20210113','20210114','20210311','20210312','20210313','20210314') then crcnt else 0 end) as zl5cnt,sum(case when subjid in"+

				 " ('20210111','20210112','20210113','20210114','20210311','20210312','20210313','20210314') then cramt else 0 end) as zl5amt,sum(case when subjid in"+

				 " ('20210111','20210112','20210113','20210114','20210311','20210312','20210313','20210314') and datekey='"+date+"' then crbal else 0 end) as zl5bal, sum(case when subjid in"+

				 " ('20040201','20040202','20040203','20040204','20040211','20040212','20040213','20040214') then crcnt else 0 end) as zl6cnt,sum(case when subjid in"+

				 " ('20040201','20040202','20040203','20040204','20040211','20040212','20040213','20040214') then cramt else 0 end) as zl6amt,sum(case when subjid in"+

				 " ('20040201','20040202','20040203','20040204','20040211','20040212','20040213','20040214') and datekey='"+date+"' then crbal else 0 end) as zl6bal, sum(case when subjid in ('13037101','13037103') and datekey='"+date+"' then"+

				 " debal else 0 end) as zl7bal from s_subjtotal_org where datekey>='"+Qbegin+"' and datekey<='"+date+"' group by deptid";

				Statement stm = conn.createStatement();

				String dsql = " DELETE FROM billbusiness_q WHERE year=" + y + " and quarter=" + quarter; //  

				stm.executeUpdate(dsql);

				int flag = 0;

				String isql = "insert into billbusiness_q(year,quarter,deptid,zl,cnt1,amt1,cbal) VALUES(?,?,?,?,?,?,?)";

				logger.debug(" sql=" + isql);

				System.out.println(isql);				

				pst = conn.prepareStatement(isql);

				ResultSet rs = stm.executeQuery(sql);

				while (rs.next()) {

					

					pst.setInt(1, y);

					pst.setInt(2, quarter);

					pst.setString(3, rs.getString("deptid"));

					pst.setString(4, "1");

					pst.setInt(5, rs.getInt("zl1cnt"));

					pst.setDouble(6, rs.getDouble("zl1amt"));

					pst.setDouble(7,0);

					pst.addBatch();

					

					pst.setInt(1, y);

					pst.setInt(2, quarter);

					pst.setString(3, rs.getString("deptid"));

					pst.setString(4, "2");

					pst.setInt(5, rs.getInt("zl2cnt"));

					pst.setDouble(6, rs.getDouble("zl2amt"));

					pst.setDouble(7, rs.getDouble("zl2bal"));

					pst.addBatch();

					

					pst.setInt(1, y);

					pst.setInt(2, quarter);

					pst.setString(3, rs.getString("deptid"));

					pst.setString(4, "3");

					pst.setInt(5, rs.getInt("zl3cnt"));

					pst.setDouble(6, rs.getDouble("zl3amt"));

					pst.setDouble(7, rs.getDouble("zl3bal"));

					pst.addBatch();

					

					pst.setInt(1, y);

					pst.setInt(2, quarter);

					pst.setString(3, rs.getString("deptid"));

					pst.setString(4, "4");

					pst.setInt(5, rs.getInt("zl4cnt"));

					pst.setDouble(6, rs.getDouble("zl4amt"));

					pst.setDouble(7, rs.getDouble("zl4bal"));

					pst.addBatch();

					

					pst.setInt(1, y);

					pst.setInt(2, quarter);

					pst.setString(3, rs.getString("deptid"));

					pst.setString(4, "5");

					pst.setInt(5, rs.getInt("zl5cnt"));

					pst.setDouble(6, rs.getDouble("zl5amt"));

					pst.setDouble(7, rs.getDouble("zl5bal"));

					pst.addBatch();

					

					pst.setInt(1, y);

					pst.setInt(2, quarter);

					pst.setString(3, rs.getString("deptid"));

					pst.setString(4, "6");

					pst.setInt(5, rs.getInt("zl6cnt"));

					pst.setDouble(6, rs.getDouble("zl6amt"));

					pst.setDouble(7, rs.getDouble("zl6bal"));

					pst.addBatch();

					

					pst.setInt(1, y);

					pst.setInt(2, quarter);

					pst.setString(3, rs.getString("deptid"));

					pst.setString(4, "7");

					pst.setInt(5, 0);

					pst.setDouble(6, 0);

					pst.setDouble(7, rs.getDouble("zl7bal"));

					pst.addBatch();

					

					flag = flag+7;

					

					if (flag == 30) {

						flag = 0;

						pst.executeBatch();

					}

				}

				if (flag > 0)

					pst.executeBatch();

				rs.close();