package dao;

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

import util.DBManager;
import entity.Information;

public class InformationDao {

    private Connection con = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;
    
    // 查询当天所有gtalk报警信息
    public List<Information> queryAll() {
        
        // 按方法所需拼写T-SQL语句
        String sql = "SELECT gtalk_alarm.Game_Type,gtalk_alarm.`DATE`,gtalk_alarm.Alarm_Type,gtalk_alarm.IP,gtalk_alarm.Srv_ID,gtalk_alarm.Srv_Name,gtalk_alarm.Alarm_Message,gtalk_alarm.Contact,gtalk_alarm.Tel "
                   + "FROM gtalk_alarm "
                   + "WHERE TO_DAYS(gtalk_alarm.`DATE`) = TO_DAYS(now()) "
                   + "GROUP BY gtalk_alarm.Game_Type,gtalk_alarm.`DATE`,gtalk_alarm.Alarm_Type,gtalk_alarm.IP,gtalk_alarm.Srv_ID,gtalk_alarm.Srv_Name,gtalk_alarm.Alarm_Message,gtalk_alarm.Contact,gtalk_alarm.Tel "
                   + "ORDER BY gtalk_alarm.`DATE` DESC";
        
        // 创建数据库连接               
        con = DBManager.getConnection();
        
        try {
            // 创建数据库操作对象
            pst = con.prepareStatement(sql);
            
            // 执行数据库操作对象并接受结果集
            rs = pst.executeQuery();
            
            // 创建集合
            List<Information> infoList = new ArrayList<Information>();
            
            // 遍历结果集
            while(rs.next()){
                
                // 实例化信息对象
                Information info = new Information();
                info.setGameType(rs.getString("gtalk_alarm.Game_Type"));
                info.setDate(rs.getString("gtalk_alarm.DATE"));
                info.setAlarmType(rs.getString("gtalk_alarm.Alarm_Type"));
                info.setIp(rs.getString("gtalk_alarm.IP"));
                info.setSrv_id(rs.getString("gtalk_alarm.Srv_ID"));
                info.setSrv_name(rs.getString("gtalk_alarm.Srv_Name"));
                info.setAlarmMessage(rs.getString("gtalk_alarm.Alarm_Message"));
                info.setContact(rs.getString("gtalk_alarm.Contact"));
                info.setTel(rs.getString("gtalk_alarm.Tel"));
                
                // 封装对象进集合
                infoList.add(info);
            }
            
            return infoList;
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        } finally{
            DBManager.close(con,pst,rs);
        }
    }
    
    // 按集合差值limit查询
    public List<Information> queryAll(int limit){
        // 按方法所需拼写T-SQL语句
        String sql = "SELECT gtalk_alarm.Game_Type,gtalk_alarm.`DATE`,gtalk_alarm.Alarm_Type,gtalk_alarm.IP,gtalk_alarm.Srv_ID,gtalk_alarm.Srv_Name,gtalk_alarm.Alarm_Message,gtalk_alarm.Contact,gtalk_alarm.Tel "
                   + "FROM gtalk_alarm "
                   + "WHERE TO_DAYS(gtalk_alarm.`DATE`) = TO_DAYS(now()) "
                   + "GROUP BY gtalk_alarm.Game_Type,gtalk_alarm.`DATE`,gtalk_alarm.Alarm_Type,gtalk_alarm.IP,gtalk_alarm.Srv_ID,gtalk_alarm.Srv_Name,gtalk_alarm.Alarm_Message,gtalk_alarm.Contact,gtalk_alarm.Tel "
                   +