package com.fgkj.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.fgkj.actions.ActionUtil;
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.BadBatt_mon;
import com.fgkj.dto.BattInf;
import com.fgkj.dto.BattMap_information;
import com.fgkj.dto.Batt_Maint_Dealarm;
import com.fgkj.dto.Page;
import com.fgkj.dto.User_inf;

public class BadBatt_monImpl implements BaseDAO,CallBack{

	public List getResults(ResultSet rs) {
		// TODO Auto-generated method stub
		return null;
	}

	public boolean add(Object obj) {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean update(Object obj) {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean del(Object obj) {
		// TODO Auto-generated method stub
		return false;
	}

	public List searchAll() {
		// TODO Auto-generated method stub
		return null;
	}
    
	public List serchByCondition(Object obj) {
		// TODO Auto-generated method stub
		return null;
	}
    //9.1根据stationid查询机房的落后单体总数
	public int serchByStationId(Object obj) {
		BattMap_information binformation=(BattMap_information) obj;
		String sql="select count(num) as nums  from web_site.tb_badbatt_mon " +
				"where " +
				"battgroupid in (select distinct battgroupid from db_battinf.tb_battinf where stationid=? ) " +
				"limit 1 ";
		//System.out.println(sql);
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binformation.getStationId()}, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						int nums=rs.getInt("nums");
						list.add(nums);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return list;
			}
		});
		int nums=0;//最后的结果
		if(list!=null&&list.size()>0){
			nums=(Integer) list.get(list.size()-1);
		}
		return nums;
	}
	
	//9.1根据机房stationid查询机房下电池组单体落后情况
	public String serchMonBad(Object obj){
		BattInf binf=(BattInf) obj;
		String sql="select battgroupid,group_concat(distinct mon_num) as mon_nums from web_site.tb_badbatt_mon " +
				" where " +
				" battgroupid =? " +
				" group by  battgroupid  limit 1";
		//System.out.println(sql);
		List<BadBatt_mon> list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getBattGroupId()}, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						BadBatt_mon bdmon=new BadBatt_mon();
						bdmon.setBattGroupid(rs.getInt("battGroupid"));
						bdmon.setNote(rs.getString("mon_nums"));
						list.add(bdmon);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		});
		String monBads="";//存放指定电池组落后单体信息
		if(list!=null&&list.size()>0){
			monBads=list.get(list.size()-1).getNote();
		}
		return monBads;
		
	}
	//查询落后单体总数
	/*public int searchNums() {
		String sql="select count(num) as nums  " +
				"from web_site.tb_badbatt_mon " +
				"limit 1 ";
		//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()){
						int nums=rs.getInt("nums");
						list.add(nums);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		});
		int nums=0;//最后的结果
		if(list!=null&&list.size()>0){
			nums=(Integer) list.get(list.size()-1);
		}
		return nums;
	}*/
	public int searchNums(Object obj) {
		User_inf uinf=(User_inf) obj;
		String sql="select distinct num,battgroupid,mon_num " +
				" from web_site.tb_badbatt_mon " +
				" where web_site.tb_badbatt_mon.battgroupid in (select distinct db_battinf.tb_battinf.battgroupid " +
				" from db_battinf.tb_battinf,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr,db_user.tb_user_inf  " +
				" where db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId  " +
				" and db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
				" and db_user.tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
				" and db_user.tb_user_inf.uid=? ) " ;
		//System.out.println(sql);
		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()){
						BadBatt_mon bdmon=new BadBatt_mon();
						bdmon.setNum(rs.getInt("num"));
						bdmon.setBattGroupid(rs.getInt("battgroupid"));
						bdmon.setMon_num(rs.getInt("mon_num"));
						list.add(bdmon);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		});
		int nums=0;//最后的结果
		if(list!=null&&list.size()>0){
			nums=(Integer) list.size();
		}
		return nums;
	}

	//落后单体查询：根据条件查询落后单体
	/*public List serchByInfo(Object obj) {
		Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
		BattInf binf = bmd.getBinf();
		Page page=bmd.getPage();
		Connection conn=DBUtil.getConn();
		String numberSql=" SELECT FOUND_ROWS() number";
		String sql="";
		String baseSql=" SELECT SQL_CALC_FOUND_ROWS distinct web_site.tb_badbatt_mon.battgroupid,web_site.tb_badbatt_mon.mon_num,web_site.tb_badbatt_mon.real_cap,web_site.tb_badbatt_mon.stdcap,web_site.tb_badbatt_mon.note  " +
				",db_battinf.tb_battinf.stationname1,db_battinf.tb_battinf.stationname,db_battinf.tb_battinf.battproducer,db_battinf.tb_battinf.battinusedate" +
				",db_battinf.tb_battinf.monvolstd,db_battinf.tb_battinf.battgroupname  " +
				"FROM web_site.tb_badbatt_mon " +
				"LEFT OUTER JOIN db_battinf.tb_battinf ON web_site.tb_badbatt_mon.battgroupid=db_battinf.tb_battinf.BattGroupId  " +
				"where  battinusedate>=? and battinusedate<=? " +
				"and  test_starttime>=? and test_starttime<=? ";
		//用于维护区
		String station1SqlT=" and stationname1!=? ";//全部
		String station1SqlF=" and stationname1=? ";
		if(binf.getStationName1().equals("")){
			baseSql+=station1SqlT;
		}else{
			baseSql+=station1SqlF;
		}
		//用于机房站点
		String stationSqlT=" and stationname!=? ";//全部
		String stationSqlF=" and stationname=? ";
		if(binf.getStationName().equals("")){
			baseSql+=stationSqlT;
		}else{
			baseSql+=stationSqlF;
		}
		//选取蓄电池组条件
		String idSqlT=" and db_battinf.tb_battinf.battgroupid!=?  ";
		String idSqlF=" and db_battinf.tb_battinf.battgroupid=?  ";
		if(binf.getBattGroupId()==0){
			baseSql+=idSqlT;
		}else{
			baseSql+=idSqlF;
		}
		//用于电池类型
		String producerSqlT=" and battproducer!=? ";//全部
		String producerSqlF=" and battproducer=? ";
		if(binf.getBattProducer().equals("")){
			baseSql+=producerSqlT;
		}else{
			baseSql+=producerSqlF;
		}
		String endSql=" ORDER BY web_site.tb_badbatt_mon.battgroupid asc  ";
		String limitSql=" limit ?,? ";
		sql=baseSql+endSql+limitSql;
		//System.out.println(sql);
		List<Batt_Maint_Dealarm> list=DAOHelper.executeQueryLimit(sql, conn, new Object[]{ binf.getBattInUseDate(),binf.getBattInUseDate1(),
			      binf.getBattProductDate(),binf.getBattProductDate1(),
			      binf.getStationName1(),binf.getStationName(),binf.getBattGroupId(),
			      binf.getBattProducer(),
			      (page.getPageCurr()-1)*page.getPageSize(),page.getPageSize()}, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						Batt_Maint_Dealarm bmd = new Batt_Maint_Dealarm();
						BattInf binf=new BattInf();
						binf.setStationName1(rs.getString("stationName1"));
						binf.setStationName(rs.getString("stationName"));
						binf.setBattProducer(rs.getString("battProducer"));
						if(rs.getString("battInUseDate")!=null)
						binf.setBattInUseDate(rs.getDate("battInUseDate"));
						binf.setMonVolStd(rs.getFloat("monVolStd"));
						binf.setBattGroupName(rs.getString("battGroupName"));
						bmd.setBinf(binf);
						BadBatt_mon bdmon=new BadBatt_mon();
						bdmon.setBattGroupid(rs.getInt("battGroupid"));
						bdmon.setMon_num(rs.getInt("mon_num"));
						bdmon.setReal_cap(rs.getFloat("real_cap"));
						bdmon.setStdcap(rs.getFloat("stdcap"));
						if(Integer.parseInt(rs.getString("note"))==AlarmDaoFactory.Alarm_CapAlarm){
							bdmon.setNote("告警");
						}else if(Integer.parseInt(rs.getString("note"))==AlarmDaoFactory.Alarm_CapChange){
							bdmon.setNote("更换");
						}else{
							bdmon.setNote("--");
						}
						bmd.setBdmon(bdmon);
						Page page=new Page();
						bmd.setPage(page);
						list.add(bmd);
					}
				} 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;
	}*/
	//落后单体查询：根据条件查询落后单体(用户管理的电池组)
	public List serchByInfo(Object obj) {
		Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
		BattInf binf = bmd.getBinf();
		User_inf uinf=bmd.getUinf();
		Page page=bmd.getPage();
		Connection conn=DBUtil.getConn();
		String numberSql=" SELECT FOUND_ROWS() number";
		String sql="";
		String baseSql=" SELECT SQL_CALC_FOUND_ROWS distinct web_site.tb_badbatt_mon.battgroupid,web_site.tb_badbatt_mon.mon_num,web_site.tb_badbatt_mon.real_cap,web_site.tb_badbatt_mon.stdcap,web_site.tb_badbatt_mon.note  " +
				",db_battinf.tb_battinf.stationname1,db_battinf.tb_battinf.stationname,db_battinf.tb_battinf.stationname2,db_battinf.tb_battinf.stationname5,db_battinf.tb_battinf.battproducer,db_battinf.tb_battinf.battinusedate" +
				",db_battinf.tb_battinf.monvolstd,db_battinf.tb_battinf.battgroupname  " +
				"FROM web_site.tb_badbatt_mon,db_battinf.tb_battinf " +
				"where web_site.tb_badbatt_mon.battgroupid=db_battinf.tb_battinf.BattGroupId " +
				"and test_starttime>=? and test_starttime<=? " +
				"and db_battinf.tb_battinf.stationname1 like ? and db_battinf.tb_battinf.stationname like ? and db_battinf.tb_battinf.stationname2 like ? and db_battinf.tb_battinf.stationname5 like ? ";
		
		//选取蓄电池组条件
		String idSqlT=" and db_battinf.tb_battinf.battgroupid!=?  and db_battinf.tb_battinf.Station_install=1 ";
		String idSqlF=" and db_battinf.tb_battinf.battgroupid=?   and db_battinf.tb_battinf.Station_install=1 ";
		if(binf.getBattGroupId()==0){
			baseSql+=idSqlT;
		}else{
			baseSql+=idSqlF;
		}
		//用于用户管理的电池组的筛选
		String userSql=" and web_site.tb_badbatt_mon.battgroupid in (select distinct db_battinf.tb_battinf.battgroupid " +
				" from db_battinf.tb_battinf,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr,db_user.tb_user_inf   " +
				" where db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId   " +
				" and db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id  " +
				" and db_user.tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid  " +
				" and db_user.tb_user_inf.uid=? )";
		baseSql+=userSql;
		
		String endSql=" ORDER BY web_site.tb_badbatt_mon.battgroupid asc  ";
		String limitSql=" limit ?,? ";
		sql=baseSql+endSql+limitSql;
		//System.out.println(sql);
		List<Batt_Maint_Dealarm> list=DAOHelper.executeQueryLimit(sql, conn, new Object[]{
			      binf.getBattProductDate(),binf.getBattProductDate1(),"%"+binf.getStationName1()+"%","%"+binf.getStationName()+"%","%"+binf.getStationName2()+"%","%"+binf.getStationName5()+"%",
			      binf.getBattGroupId(),uinf.getUId(),
			      (page.getPageCurr()-1)*page.getPageSize(),page.getPageSize()}, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						Batt_Maint_Dealarm bmd = new Batt_Maint_Dealarm();
						BattInf binf=new BattInf();
						binf.setStationName1(rs.getString("stationName1"));
						binf.setStationName2(rs.getString("stationName2"));
						binf.setStationName5(rs.getString("stationName5"));
						binf.setStationName(rs.getString("stationName"));
						
						binf.setBattProducer(rs.getString("battProducer"));
						if(rs.getString("battInUseDate")!=null)
						binf.setBattInUseDate(rs.getDate("battInUseDate"));
						binf.setMonVolStd(rs.getFloat("monVolStd"));
						binf.setBattGroupName(rs.getString("battGroupName"));
						bmd.setBinf(binf);
						BadBatt_mon bdmon=new BadBatt_mon();
						bdmon.setBattGroupid(rs.getInt("battGroupid"));
						bdmon.setMon_num(rs.getInt("mon_num"));
						bdmon.setReal_cap(rs.getFloat("real_cap"));
						bdmon.setStdcap(rs.getFloat("stdcap"));
						if(Integer.parseInt(rs.getString("note"))==AlarmDaoFactory.Alarm_CapAlarm){
							bdmon.setNote("告警");
						}else if(Integer.parseInt(rs.getString("note"))==AlarmDaoFactory.Alarm_CapChange){
							bdmon.setNote("更换");
						}else{
							bdmon.setNote("--");
						}
						bmd.setBdmon(bdmon);
						Page page=new Page();
						bmd.setPage(page);
						list.add(bmd);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		});	
		//去掉limit条件后的总数
		int number=LimitNumberFactory.GetLimtitNumber(conn, numberSql);
		if(list!=null&&list.size()>0){
			list.get(list.size()-1).getPage().setPageAll(number);
		}
		return list;
	}
    
	//查询存在落后单体得机房个数
	public int searchBadBattInStation(Object obj) {
		User_inf uinf=(User_inf) obj;
		String sql=" select count(distinct db_battinf.tb_battinf.stationid) as nums from db_battinf.tb_battinf where  station_install=1 " +
				" and db_battinf.tb_battinf.battgroupid in (select distinct tb_badbatt_mon.BattGroupId from web_site.tb_badbatt_mon) " +
				" and db_battinf.tb_battinf.stationid in ( select distinct db_user.tb_user_battgroup_baojigroup_battgroup.stationid " +
				" from db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr " +
				" where db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id=db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id  " +
				" and db_user.tb_user_battgroup_baojigroup_usr.uId=? )  ";
		//System.out.println(sql);
		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()){
						list.add(rs.getInt("nums"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		});
		int nums=0;//最后的结果
		if(list!=null&&list.size()>0){
			nums=(Integer) list.get(list.size()-1);
		}
		return nums;
	}
	public static void main(String[] args) throws ParseException {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date1 = sdf.parse("2000-01-01");
		Date date2 = sdf.parse("2020-01-01");
		
		BadBatt_monImpl bimpl=new BadBatt_monImpl();
		BattMap_information binformation=new BattMap_information();
		binformation.setStationId("42070218");
		//int nums=bimpl.serchByStationId(binformation);
		//System.out.println(nums);
		
		BattInf binf = new BattInf();
		binf.setBattProductDate(ActionUtil.getSimpDate(date1));
		binf.setBattProductDate1(ActionUtil.getSimpDate(date2));
		binf.setBattInUseDate(ActionUtil.getSimpDate(date1));
		binf.setBattInUseDate1(ActionUtil.getSimpDate(date2));
		binf.setStationName1("");
		binf.setStationName("");
		binf.setStationName2("");
		binf.setStationName5("");
		binf.setBattGroupId(0);
	    binf.setBattProducer("");
		Page page=new Page();
		page.setPageCurr(1);
		page.setPageSize(10);
		User_inf uinf=new User_inf();
		uinf.setUId(1002);
		Batt_Maint_Dealarm bmd = new Batt_Maint_Dealarm();
		bmd.setBinf(binf);
		bmd.setPage(page);
		bmd.setUinf(uinf);
		/*List<Batt_Maint_Dealarm> list=bimpl.serchByInfo(bmd);
		for (Batt_Maint_Dealarm b : list) {
			System.out.println(b);
		}*/
		int nums=bimpl.searchBadBattInStation(uinf);
		System.out.println(nums);
	}


}