package com.fgkj.dao.impl;

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

import com.fgkj.dao.AlarmDaoFactory;
import com.fgkj.dao.BaseDAO;
import com.fgkj.dao.CallBack;
import com.fgkj.dao.DAOHelper;
import com.fgkj.dao.LimitNumberFactory;
import com.fgkj.db.DBUtil;
import com.fgkj.db.IDatabaseName;
import com.fgkj.dto.BattDischarge_plan;
import com.fgkj.dto.BattInf;
import com.fgkj.dto.BattMap_information;
import com.fgkj.dto.Batt_Maint_Dealarm;
import com.fgkj.dto.Battalarm_data;
import com.fgkj.dto.Page;
import com.fgkj.dto.User_inf;

public class BattMap_informationImpl implements BaseDAO,CallBack{

	public List getResults(ResultSet rs) {
		List list=new ArrayList();
		try {
			while(rs.next()){
				BattMap_information binformation=new BattMap_information();
				binformation.setNum(rs.getInt("num"));
				binformation.setStationId(rs.getString("stationId"));
				binformation.setStationName(rs.getString("stationName"));
				binformation.setAddress(rs.getString("address"));
				binformation.setLongitude(rs.getDouble("longitude"));
				binformation.setLatitude(rs.getDouble("latitude"));
				binformation.setInformation(rs.getString("information"));
				list.add(binformation);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
    //9.1录入机房信息
	public boolean add(Object obj) {
		BattMap_information binformation=(BattMap_information) obj;
		String sql="insert into web_site.tb_battmap_information(StationId,StationName,address,longitude,latitude) values(?,?,?,?,?)";
		Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{binformation.getStationId(),binformation.getStationName(),
			binformation.getAddress(),binformation.getLongitude(),binformation.getLatitude()});
		return bl;
	}

	public boolean update(Object obj) {
		BattMap_information binformation=(BattMap_information) obj;
		String sql="update web_site.tb_battmap_information set StationId=?,StationName=?,address=?,longitude=?,latitude=?,information=? where num=?";
		Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{binformation.getStationId(),binformation.getStationName(),
			binformation.getAddress(),binformation.getLongitude(),binformation.getLatitude(),binformation.getInformation(),binformation.getNum()});
		return bl;
	}

	public boolean del(Object obj) {
		BattMap_information binformation=(BattMap_information) obj;
		//System.out.println(binformation);
		String sql="delete from web_site.tb_battmap_information where num=?";
		Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{binformation.getNum()});
		return bl;
	}
   
	 //9.1百度地图定位查询电池组信息
	public List searchAll() {
		String sql="select num,StationId,StationName,address,longitude,latitude,information from web_site.tb_battmap_information ";
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new BattMap_informationImpl());
		return list;
	}

    //9.1查询未添加物理信息的机房
	public List serchNotInBattMap() {
		String sql="select distinct tb_battinf.stationid,tb_battinf.stationname from db_battinf.tb_battinf " +
				"where tb_battinf.stationid not in(select web_site.tb_battmap_information.stationid from web_site.tb_battmap_information )";
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						BattInf binf=new BattInf();
						binf.setStationId(rs.getString("StationId"));
						binf.setStationName(rs.getString("stationName"));
						list.add(binf);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		});
		return list;
	}
	//9.1查询未添加物理信息的机房(用户管理的)
	public List serchNotInBattMapByUid(Object obj) {
		User_inf uinf=(User_inf) obj;
		String sql="select distinct db_battinf.tb_battinf.stationname,db_battinf.tb_battinf.stationid,db_battinf.tb_battinf.fBSDeviceId " +
				  " from db_battinf.tb_battinf " +
				  " left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.stationid=db_battinf.tb_battinf.stationid " +
				  " left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
				  " left outer join db_user.tb_user_inf on tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
				  " where db_user.tb_user_inf.uid=?  " +
				  " and db_battinf.tb_battinf.stationid not in(select web_site.tb_battmap_information.stationid from web_site.tb_battmap_information )";
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{uinf.getUId()}, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						BattInf binf=new BattInf();
						binf.setStationId(rs.getString("StationId"));
						binf.setStationName(rs.getString("stationName"));
						binf.setFBSDeviceId(rs.getInt("fBSDeviceId"));
						list.add(binf);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		});
		return list;
	}
	//9.1查询已经添加物理信息的机房(用户管理的,l查询全部打开页面)
	public List serchInBattMapByUid(Object obj) {
		User_inf uinf=(User_inf) obj;
		String sql="select distinct tb_battmap_information.num,tb_battmap_information.StationId,tb_battmap_information.StationName,address,longitude,latitude,information,db_battinf.tb_battinf.FBSDeviceId  " +
				"  from web_site.tb_battmap_information,db_battinf.tb_battinf " +
				"  where  tb_battmap_information.StationId=db_battinf.tb_battinf.StationId " +
				"  and tb_battmap_information.StationId in (" +
				"  select distinct db_battinf.tb_battinf.stationid  " +
				"  from db_battinf.tb_battinf " +
				"  left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.stationid=db_battinf.tb_battinf.stationid " +
				"  left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
				"  left outer join db_user.tb_user_inf on tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
				"  where db_user.tb_user_inf.uid=?  ) ";
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{uinf.getUId()}, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						BattMap_information binformation=new BattMap_information();
						binformation.setNum(rs.getInt("num"));
						binformation.setStationId(rs.getString("stationId"));
						binformation.setStationName(rs.getString("stationName"));
						binformation.setAddress(rs.getString("address"));
						binformation.setLongitude(rs.getDouble("longitude"));
						binformation.setLatitude(rs.getDouble("latitude"));
						binformation.setInformation(rs.getString("information"));
						binformation.setFBSDeviceId(rs.getInt("fBSDeviceId"));
						list.add(binformation);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		} );
		return list;
	}
	//9.1查询已经添加物理信息的机房(用户管理的，分页刷新)
	public List serchInBattMapByUid2(Object obj) {
		Page page=(Page) obj;
		int uid=page.getPageAll();
		Connection conn=DBUtil.getConn();
		String numberSql="SELECT FOUND_ROWS() number";//存放总数
		String sql="select SQL_CALC_FOUND_ROWS distinct tb_battmap_information.num,tb_battmap_information.StationId,tb_battmap_information.StationName,address,longitude,latitude,information,db_battinf.tb_battinf.FBSDeviceId,db_battinf.tb_battinf.station_install  " +
				"  from web_site.tb_battmap_information,db_battinf.tb_battinf " +
				"  where  tb_battmap_information.StationId=db_battinf.tb_battinf.StationId " +
				"  and tb_battmap_information.StationId in (" +
				"  select distinct db_battinf.tb_battinf.stationid  " +
				"  from db_battinf.tb_battinf " +
				"  left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.stationid=db_battinf.tb_battinf.stationid " +
				"  left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
				"  left outer join db_user.tb_user_inf on tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
				"  where db_user.tb_user_inf.uid=?  )  " +
				"  order by tb_battmap_information.StationId "+
				"  limit ?,?  " ;
		List<BattMap_information> list=DAOHelper.executeQueryLimit(sql, conn,new Object[]{uid,(page.getPageCurr()-1)*page.getPageSize(),page.getPageSize()}, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						BattMap_information binformation=new BattMap_information();
						binformation.setNum(rs.getInt("num"));
						binformation.setStationId(rs.getString("stationId"));
						binformation.setStationName(rs.getString("stationName"));
						binformation.setAddress(rs.getString("address"));
						binformation.setLongitude(rs.getDouble("longitude"));
						binformation.setLatitude(rs.getDouble("latitude"));
						binformation.setInformation(rs.getString("information"));
						binformation.setFBSDeviceId(rs.getInt("fBSDeviceId"));
						binformation.setInformation(rs.getString("station_install"));
						Page p=new Page();
						binformation.setPage(p);
						list.add(binformation);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		} );
		//去掉limit条件后的总数
		int number=LimitNumberFactory.GetLimtitNumber(conn, numberSql);
		//System.out.println("number: "+number);
		if(list!=null&&list.size()>0){
			list.get(list.size()-1).getPage().setPageAll(number);
		}
		return list;
	}
	//9.1查询已经添加物理信息的机房(用户管理的，3.根据具体id查询告警信息等)
	public List serchInBattMapByUid3(Object obj) {
		BattMap_information binfor=(BattMap_information) obj;
		String sql="select distinct tb_battmap_information.num,tb_battmap_information.StationId,tb_battmap_information.StationName,address,longitude,latitude,information,db_battinf.tb_battinf.FBSDeviceId,tb_battinf.stationName1,tb_battinf.stationName2,tb_battinf.stationName3  " +
				"  from web_site.tb_battmap_information,db_battinf.tb_battinf " +
				"  where  tb_battmap_information.StationId=db_battinf.tb_battinf.StationId " +
				"  and tb_battmap_information.StationId in (" +
				"  select distinct db_battinf.tb_battinf.stationid  " +
				"  from db_battinf.tb_battinf " +
				"  left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.stationid=db_battinf.tb_battinf.stationid " +
				"  left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
				"  left outer join db_user.tb_user_inf on tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
				"  where db_user.tb_user_inf.uid=?  ) " +
				"  and tb_battmap_information.StationId=? ";
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{binfor.getNum(),binfor.getStationId()}, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						BattMap_information binformation=new BattMap_information();
						binformation.setNum(rs.getInt("num"));
						binformation.setStationId(rs.getString("stationId"));
						binformation.setStationName(rs.getString("stationName"));
						binformation.setStationName1(rs.getString("stationName1"));
						binformation.setStationName2(rs.getString("stationName2"));
						binformation.setStationName3(rs.getString("stationName3"));
						binformation.setAddress(rs.getString("address"));
						binformation.setLongitude(rs.getDouble("longitude"));
						binformation.setLatitude(rs.getDouble("latitude"));
						binformation.setInformation(rs.getString("information"));
						binformation.setFBSDeviceId(rs.getInt("fBSDeviceId"));
						list.add(binformation);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		} );
		return list;
	}
	//9.1查询机房所在的所有省份
	public List serchStationName1(){
		String sql="  SELECT  DISTINCT substring_index(substring_index(address,'-',2),'-',1) as stationname1  FROM web_site.tb_BattMap_information  " ;
		//System.out.println(sql);
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						String stationname1=rs.getString("stationname1");
						list.add(stationname1);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		});
		return list;
	}
	//9.1根据省份查询机房所在的所有城市
	public List serchStationName2(Object obj){
		BattMap_information binformation=(BattMap_information) obj;
		String sql=" SELECT  DISTINCT substring_index(substring_index(address,'-',2),'-',-1) as stationname2 FROM web_site.tb_BattMap_information" +
				" where  address  like ? " ;
		//System.out.println(sql);
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binformation.getAddress()+"%"}, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						String stationname2=rs.getString("stationname2");
						list.add(stationname2);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		});
		return list;
	}
	
	//9.1根据省份和城市查询机房所在的所有机房
	public List serchStationName(Object obj){
		BattMap_information binformation=(BattMap_information) obj;
		String sql=" SELECT DISTINCT StationName,longitude,latitude FROM web_site.tb_BattMap_information   " +
				"WHERE address  like ?   ";
		//System.out.println(sql);
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binformation.getAddress()+"%"}, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						BattMap_information binformation=new BattMap_information();
						binformation.setStationName(rs.getString("stationName"));
						binformation.setLongitude(rs.getDouble("longitude"));
						binformation.setLatitude(rs.getDouble("latitude"));
						list.add(binformation);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		});
		return list;
	}
    //9.1地图上根据维护区查询机房经纬度
	public List serchByInfo(Object obj) {
		BattMap_information binformation=(BattMap_information) obj;
		String sql="select num, StationId,StationName,address,longitude,latitude,information from web_site.tb_battmap_information " +
				   " where StationId=?";
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binformation.getStationId()}, new BattMap_informationImpl());
		return list;
	}
    //地图上根据机房名称查询经纬度
	public List serchTudeByStationName(Object obj){
		BattMap_information binformation=(BattMap_information) obj;
		String sql="select num, StationId,StationName,address,longitude,latitude,information from web_site.tb_battmap_information " +
				   " where StationName like ?";
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binformation.getStationName()}, new BattMap_informationImpl());
		return list;
	}
	public static void main(String[] args) {
		BattMap_informationImpl bimpl=new BattMap_informationImpl();
		Batt_Maint_Dealarm bmd=new Batt_Maint_Dealarm();
		Battalarm_data adata=new Battalarm_data();
		adata.setAlm_cleared_type(0);	//告警       	0:选中	100:没有
		adata.setAlm_id(1);				//存在落后单体  1：选中          100:没有
		BattMap_information binformation=new BattMap_information();
		BattDischarge_plan bplan=new BattDischarge_plan();
		bplan.setDischarge_reason(3);	//存在延时：3    100 没有
		bmd.setAdata(adata);
		bmd.setBinformation(binformation);
		bmd.setBplan(bplan);
		
		List<Batt_Maint_Dealarm> list=bimpl.serchByCondition(bmd);
		for (Batt_Maint_Dealarm b : list) {
			System.out.println(b);
		}
		System.out.println(list.size());
	}
	//9.1百度地图定位根据省份查询所有该区域的机房
	public List serchByCondition(Object obj) {
		BattMap_information binformation=(BattMap_information) obj;
		String sql="select num,StationId,StationName,address,longitude,latitude,information from web_site.tb_battmap_information " +
			    	" where address like ? ";
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binformation.getAddress()+"%"}, new BattMap_informationImpl());
		return list;
	}
}