incomedao.java

分为经理和出纳2个权限

package com.yiboit.cfss.income;

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

import com.yiboit.cfss.db.ManageConnection;

public class IncomeDAO {

	public boolean updateIncome(int incomeId, double income) throws SQLException {
		boolean result = false;
		Connection conn = null;
		
		try {
			conn = ManageConnection.getConnection();

			String sql = "update shop_income set daily_income = ?" +
					", last_modify_date = sysdate where income_id = ?";
			
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, income);
			pstmt.setInt(2, incomeId);

			result = pstmt.executeUpdate() == 1 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
		
		return result;
	}
	
	public boolean addIncome(int deptId, double income) throws SQLException {
		boolean result = false;
		Connection conn = null;

		try {
			conn = ManageConnection.getConnection();

			String sql = "insert into shop_income("
				+ "income_id, dept_id, daily_income"
				+ ", business_date, last_modify_date)"
				+ " values(seq_income_id.nextval, ?, ?"
				+ " , sysdate, sysdate)";
			
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, deptId);
			pstmt.setDouble(2, income);

			result = pstmt.executeUpdate() == 1 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
		return result;
	}

	public List getYears() throws SQLException {
		List result = null;

		Connection conn = null;

		try {
			conn = ManageConnection.getConnection();
			String sql = "select distinct to_char(business_date, 'yyyy') as bdate"
					+ " from shop_income order by bdate desc";

			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				result = new ArrayList<IncomeBean>();
				while (rs.next()) {
					result.add(rs.getString(1));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return result;
	}

	public List getIncomeList(String year, String month, String day,
			String orderby) throws SQLException {
		List<IncomeBean> result = null;
		Connection conn = null;

		try {
			conn = ManageConnection.getConnection();
			String sql = "select i.income_id, d.dept_id, d.dept_name"
					+ ", i.business_date, i.last_modify_date, i.daily_income"
					+ " from shop_income i, shop_dept d"
					+ " where i.dept_id = d.dept_id "
					+ " and to_char(business_date, 'yyyy-MM-dd') = ?"
					+ " order by " + orderby + " desc";

			PreparedStatement pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, year + "-"
					+ ((month.length() < 2) ? "0" + month : month) + "-"
					+ ((day.length() < 2) ? "0" + day : day));

			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				result = new ArrayList<IncomeBean>();
				while (rs.next()) {
					result.add(new IncomeBean(rs.getInt("income_id"), rs
							.getInt("dept_id"), rs.getString("dept_name"), rs
							.getDouble("daily_income"), rs
							.getDate("business_date"), rs
							.getDate("last_modify_date")));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return result;
	}
}