reportbean.java

一个基于Eclipse平台和MySQL数据库的一个超市管理系统功能全面

package com.xdf.supermarket.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import com.xdf.supermarket.db.DBConnection;
import com.xdf.supermarket.dto.ReportDTO;

public class ReportBean extends BaseBean{
	
	/**
	 * 根据部门id，开始时间，结束时间，查询支出和收入
	 */
	public ArrayList getReport (String dept_id,String start,String end){
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;
			ArrayList list = new ArrayList();
			 
			try {
				String dept_name = new DeptBean().getOneDept(dept_id).getDept_name();
				conn = DBConnection.getConnection();
				stmt = conn.createStatement();
				String sql = "select coalesce(x1.t,x2.t) tt,nvl(i,0) ii,nvl(e,0) ee from " +
						"(select daily_income i,to_char(business_date,'yyyy-mm-dd') t " +
						"from table_income where dept_id="+dept_id+" and " +
						"trunc(business_date,'dd') between to_date('"+start+"','yyyy-mm-dd') " +
						"and to_date('"+end+"','yyyy-mm-dd')) x1 left outer join " +
						"(select sum(bill_item_expense) e,to_char(bill_date,'yyyy-mm-dd') t from table_bill b,table_bill_item i " +
						"where b.bill_id=i.bill_id and dept_id="+dept_id+" and " +
						"trunc(bill_date,'dd') between to_date('"+start+"','yyyy-mm-dd') and to_date('"+end+"','yyyy-mm-dd') group by to_char(bill_date,'yyyy-mm-dd')) x2 " +
						"on (x1.t=x2.t) " +
						"union " +
						"select coalesce(x1.t,x2.t) tt,nvl(i,0) ii,nvl(e,0) ee from " +
						"(select daily_income i,to_char(business_date,'yyyy-mm-dd') t " +
						"from table_income where dept_id="+dept_id+" and " +
						"trunc(business_date,'dd') between to_date('"+start+"','yyyy-mm-dd') " +
						"and to_date('"+end+"','yyyy-mm-dd')) x1 right outer join " +
						"(select sum(bill_item_expense) e,to_char(bill_date,'yyyy-mm-dd') t from table_bill b,table_bill_item i " +
						"where b.bill_id=i.bill_id and dept_id="+dept_id+" and " +
						"trunc(bill_date,'dd') between to_date('"+start+"','yyyy-mm-dd') and to_date('"+end+"','yyyy-mm-dd') group by to_char(bill_date,'yyyy-mm-dd')) x2 " +
						"on (x1.t=x2.t) order by tt " ;
				System.out.println(sql);
				rs = stmt.executeQuery(sql);
				while (rs.next()){
					ReportDTO v = new ReportDTO(); 
					v.setDept_name(dept_name);
					v.setDaily_income(rs.getInt("ii"));
					v.setDaily_expense(rs.getInt("ee"));
					v.setBusiness_date(rs.getString("tt"));
					list.add(v);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally{
				close(rs);
				close(stmt);
				close(conn);
			}
			return list;		
		
	}
}