incomedco.java

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

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

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

import tools.PageControl;
import conn.ConnSky;
import dto.DeptDto;
import dto.IncomeDto;


public class IncomeDco {
	
	public static int sumNote;
	
	public void select(List list, String curpage){
		PageControl pc = new PageControl();
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		IncomeDto iDto = null;
		DeptDto dDto = null;
		String sql = "select d.Dept_name,d.Dept_id,i.income_id,to_char(i.business_date,'yyyy-mm-dd') dDate,to_char(i.lst_mod_Timestemp,'yyyy-mm-dd') lDate,i.Daily_income " +
						" from Table_dept d,Table_income i where D.Dept_id = i.Dept_id " +
							" order by business_date desc" ;

		try {
			conn = ConnSky.getConn();
			pstmt = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = pstmt.executeQuery();
			rs.absolute(-1);
			sumNote = rs.getRow();
			
			pc.init(Integer.parseInt(curpage), sumNote);
			
			if (sumNote > 0) {
				if (pc.getStart() == 0) {
					rs.absolute(1);
				} else {
					rs.absolute(pc.getStart());
				}
			}
			do {
				iDto = new IncomeDto();
				dDto = new DeptDto();
				dDto.setName(rs.getString("dept_name"));
				dDto.setId(rs.getString("dept_id"));
				iDto.setDueDate(rs.getString("dDate"));
				iDto.setIncomeId(rs.getString("income_id"));
				iDto.setLastDate(rs.getString("lDate"));
				iDto.setDailyIncome(rs.getDouble("Daily_income"));
				list.add(dDto);
				list.add(iDto);
				if (!rs.next()) {
					break;
				}

			} while (rs.getRow() < pc.getEnd() + 1);
			
		} catch (Exception e) {
			e.printStackTrace();
		}		
		
		
	}

	public void selectDept(List list){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DeptDto dto = null;
		String sql = "select dept_name, dept_id, dept_description from table_dept";
		
		try{
			conn = ConnSky.getConn();
			pstmt = conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				dto = new DeptDto();
				dto.setName(rs.getString("dept_name"));
				dto.setId(rs.getString("dept_id"));
				dto.setDescription(rs.getString("dept_description"));
				list.add(dto);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				rs.close();
				pstmt.close();
				conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
	}
	
	public void addIncome(String id, double income){
		Connection conn = null;
		PreparedStatement pstmt = null;
		int flag = 0;
		String sql = "insert into table_income (income_id,dept_id,daily_income,business_date) " +
				" values (income_seq.nextval,?,?,sysdate)";
		
		try{
			conn = ConnSky.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,id);
			pstmt.setDouble(2,income);
			flag = pstmt.executeUpdate();
			conn.commit();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				pstmt.close();
				conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		
		
	}

	public void updateIncome(String deptId, String incomeId, double income ,String dueDate){
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		int flag = 0;
		String sql = "update table_income set daily_income = ?, lst_mod_timestemp = sysdate" +
				" where dept_id = ? and income_id = ? and to_char(business_date,'yyyy-mm-dd')=?";
		
		try{
			conn = ConnSky.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1,income);
			pstmt.setString(2,deptId);
			pstmt.setString(3,incomeId);
			pstmt.setString(4,dueDate);
			flag = pstmt.executeUpdate();
			conn.commit();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				pstmt.close();
				conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
	
	}	

	public boolean check(String deptId){
		boolean flag = true;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from table_income where dept_id =? " +
						" and to_char(business_date,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd')";
		
		try{
			conn = ConnSky.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,deptId);
			rs=pstmt.executeQuery();
			if(rs.next()){
				flag = false;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				rs.close();
				pstmt.close();
				conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		
		return flag;
	}

}