package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.toolsbean.DB;
import com.valuebean.AreaBean;

public class AreaDao {
	private DB mydb=null;
	
	public AreaDao(){
		mydb=new DB();
	}
	public List getAreas(){
		String sql="select a.*,t.num from tb_area as a left join (select voter_ip,count(id) as num from tb_voter group by voter_ip) t on (t.voter_ip between a.area_ipStart and a.area_ipEnd)";
		List options=getList(sql,null);		
		return options;
	}
	public List getAreasForDay(){
		String sql="select a.*,t.num from tb_area as a left join (select voter_ip,count(id) as num from tb_voter where day(voter_votetime)=day(getdate()) group by voter_ip) t on (t.voter_ip between a.area_ipStart and a.area_ipEnd)";
		List options =getList(sql,null);
		return options;		
	}
	public List getAreasForMonth(){
		String sql="select a.*,t.num from tb_area as a left join (select voter_ip,count(id) as num from tb_voter where month(voter_votetime)=month(getdate()) group by voter_ip) t on (t.voter_ip between a.area_ipStart and a.area_ipEnd)";
		List options =getList(sql,null);
		return options;		
	}
	private List getList(String sql,Object[] params){
		List options=null;
		DB mydb=new DB();
		mydb.doPstm(sql,params);
		try {
			ResultSet rs = mydb.getRs();
			if(rs!=null){
				options=new ArrayList();
				while(rs.next()){
					AreaBean single=new AreaBean();
					single.setAreaIpStart(rs.getLong(1));
					single.setAreaIpEnd(rs.getLong(2));
					single.setAreaName(rs.getString(3));
					single.setAreaBallot(rs.getInt(4));
					options.add(single);
				}
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return options;
	}
	public void closed(){
		mydb.closed();
	}
}