reportdco.java

海鲜超市管理 登录 修改 增加 等功能jsp+servlet

/*
 * 创建日期 2006-12-21
 * TODO
 */
package module.income.dco;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import module.income.dto.ReportDto;
import conn.ConnSky;

/**
 * @author HuiPengyu
 * TODO 
 */
public class ReportDco {
	public List selectReport(String fDate,String lDate){
		ReportDto dto = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		List list = new ArrayList();
		String sql = "select d.dept_name, to_char(i.business_date,'yyyy-mm-dd') bdate, i.daily_income ,sum(t.bill_item_expense) sum,t.dept_id " +
					" from table_income i,table_dept d,table_bill b,table_bill_item t " +
					" where to_char(i.business_date,'yyyy-mm-dd') = ? and to_char(i.business_date,'yyyy-mm-dd') = to_char(b.bill_date,'yyyy-mm-dd') " +
					" and i.dept_id = t.dept_id and i.dept_id = d.dept_id and t.bill_id = b.bill_id " +
					" group by (t.dept_id, d.dept_name,i.business_date,i.daily_income)";
		ResultSet rs = null;
		try{
			conn = ConnSky.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,fDate);
			rs = pstmt.executeQuery();
			while(rs.next()){
				dto = new ReportDto();
				dto.setName(rs.getString("dept_name"));
				dto.setDate(rs.getString("bdate"));
				dto.setIncome(rs.getString("daily_income"));
				dto.setExpense(rs.getString("sum"));
				list.add(dto);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				rs.close();
				pstmt.close();
				conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return list;
	}

}