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.IncomeDTO;
import com.xdf.supermarket.util.Constant;

public class IncomeBean extends BaseBean{
	
	/**
	 * 分頁得到某天收入信息,
	 * date 日期，'2006-7-12'
	 * pageno 頁碼 
	 * order 排序字段
	 */
	public ArrayList getOnePageIncome(String date,int pageno,String order){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null; 
		ArrayList list = new ArrayList();
		
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			String sql = "";
			if (order==null||order.equals("")){
				order = "d.dept_name";
			}			
			int pagesize = Constant.INCOME_PAGE_SIZE;
			int first = (pageno-1)*pagesize+1;
			int last = pageno*pagesize;
			
			if (date==null||date.equals("")){
				sql =
				"select income_id,dept_name,daily_income," +
				"to_char(business_date,'yyyy-mm-dd') bd," +
				"to_char(lst_mod_timestemp,'yyyy-mm-dd hh24:mi:ss') lmt,dept_id from " +
				"(select s.*,rownum r from " +
				"(select d.dept_name,i.* from table_dept d,table_income i " +
				"where d.dept_id=i.dept_id and " +
				"trunc(business_date,'dd')=trunc(sysdate,'dd') order by "+order+") s " + 
				"where rownum<="+last+") " + 
				"where r>="+first;
			}else{
				sql =
				"select income_id,dept_name,daily_income," +
				"to_char(business_date,'yyyy-mm-dd') bd," +
				"to_char(lst_mod_timestemp,'yyyy-mm-dd hh24:mi:ss') lmt,dept_id from " +
				"(select s.*,rownum r from " +
				"(select d.dept_name,i.* from table_dept d,table_income i " +
				"where d.dept_id=i.dept_id and " +
				"trunc(business_date,'dd')=to_date('"+date+"','yyyy-mm-dd') order by "+order+") s " + 
				"where rownum<="+last+") " + 
				"where r>="+first;				
			}
			
			rs = stmt.executeQuery(sql);
			while (rs.next()){
				IncomeDTO v = new IncomeDTO(); 
				v.setDept_id(rs.getString("dept_id"));
				v.setIncome_id(rs.getString("income_id"));
				v.setBusiness_date(rs.getString("bd"));
				v.setDaily_income(rs.getString("daily_income"));
				v.setLst_mod_timestemp(rs.getString("lmt"));
				v.setDept_name(rs.getString("dept_name"));
				list.add(v);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return list;		
	}
	/**
	 * 增加收入
	 */
	public boolean addIncome(IncomeDTO dd){
		Connection conn = null;
		Statement stmt = null;
		boolean flag = false;
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			String sql = 
			"insert into table_income values(" +
			"seq_income_id.nextval,'"+
			dd.getDept_id()+"',"+
			dd.getDaily_income()+"," +
			"trunc(sysdate,'dd')," +
			"sysdate)";
			
			int t = stmt.executeUpdate(sql);
			if (t==1)
				flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(stmt);
			close(conn);
		}
		return flag;
	} 
	
	/**
	 * 修改收入
	 */
	public boolean updateIncome(IncomeDTO dd){
		Connection conn = null;
		Statement stmt = null;
		boolean flag = false;
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			String sql = 
			"update table_income set daily_income="+dd.getDaily_income()+
			",lst_mod_timestemp=sysdate " +
			"where income_id="+dd.getIncome_id();
			
			int t = stmt.executeUpdate(sql);
			if (t==1)
				flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(stmt);
			close(conn);
		}
		return flag;
	}
	
	/**
	 * 得到總頁數
	 */
	public int getIncomeTotalPage(String date){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		int total = 0;
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			String sql = "";
			if (date==null||date.equals("")){
				sql = "select count(*) c from table_income where trunc(business_date,'dd')=trunc(sysdate,'dd')";
			}else{
				sql = "select count(*) c from table_income where trunc(business_date,'dd')=to_date('"+date+"','yyyy-mm-dd')";
			}
			rs = stmt.executeQuery(sql);
			rs.next();
			int num = rs.getInt("c");
			total = (num+Constant.INCOME_PAGE_SIZE-1)/Constant.INCOME_PAGE_SIZE;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);			
		}
		return total;
	}
	/**
	 * 根據id得到一個收入
	 */
	public IncomeDTO getOneIncome(String income_id){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		IncomeDTO v = new IncomeDTO();
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			String sql = "select i.*,d.dept_name from table_income i,table_dept d where i.dept_id=d.dept_id and income_id="+income_id; 
			
			rs = stmt.executeQuery(sql);
			if (rs.next()){
				v.setDept_id(rs.getString("dept_id"));
				v.setDaily_income(rs.getString("daily_income"));
				v.setIncome_id(rs.getString("income_id"));
				v.setDept_name(rs.getString("dept_name"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return v;		
	}
	/**
	 * 檢查今天該部門是否已經存在收入
	 */
	public boolean checkIncomeExists(String dept_id){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		boolean flag = false;
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			String sql = "select * from table_income " +
				"where trunc(business_date,'dd')=trunc(sysdate,'dd') " +
				"and dept_id="+dept_id; 
			
			rs = stmt.executeQuery(sql);
			if (rs.next()){
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return flag;		
	}	
}