package grad.util.notice;
import java.util.*;
import java.sql.*;
import grad.util.DataBase;

public class op_notice {
  public op_notice() {
  }
  /**
   * 查詢指定課程 所發布的所有公告
   * @param courseid
   * @return
   */
public ArrayList getAllnoticeByCourse(String courseid)
  {
   Connection conn=null;
   Statement st=null;
   ResultSet rs=null;
   String sql ="select * from t_notice where course_id='"+courseid+"'"+" order by date desc" ;
   ArrayList al=new ArrayList();
   try{
     DataBase ds = new DataBase();
     conn = ds.conn;
     st = conn.createStatement();
     rs = st.executeQuery(sql);
     while (rs.next()) {
        notice ni=new notice();
        ni.setid(rs.getInt(1));
        ni.settitle(rs.getString(2));
        ni.setdetail(rs.getString(3));
        ni.setdate(rs.getString(4));
        ni.setcourse_id(rs.getString(5));
        al.add(ni);

     }
     return al;
   }catch(Exception e){System.out.println(e.getMessage());return null;}

  }
  /**
   * 按公告id查詢公告詳細信息
   * @param id
   * @return
   */
  public notice getOneNoticeById(String id)
  {
    Connection conn=null;
   Statement st=null;
   ResultSet rs=null;
   notice ni=new notice();
   String sql ="select * from t_notice where id='"+id+"'" ;
   try{
     DataBase ds = new DataBase();
     conn = ds.conn;
     st = conn.createStatement();
     rs = st.executeQuery(sql);
     if (rs.next()) {
        ni.setid(rs.getInt(1));
        ni.settitle(rs.getString(2));
        ni.setdetail(rs.getString(3));
        ni.setdate(rs.getString(4));
        ni.setcourse_id(rs.getString(5));
     }
     return ni;
   }catch(Exception e){System.out.println(e.getMessage());return null;}
  }
  /**
   * 查看學生所選擇課程發布的全部公告
   * @param stuid
   * @param course_id
   * @return
   */
  public ArrayList getAllnoticeByStu(String stuid,String course_id)
  {
   Connection conn=null;
   Statement st=null;
   ResultSet rs=null;
   String sql ="select b.* from xk_record a,t_notice b "+
                " where a.course_id=b.course_id "+
                " and b.course_id= '"+course_id+"'"+
                " and a.user_code='"+stuid+"'"+
                " order by b.date desc ";
   ArrayList al=new ArrayList();
   try{
     DataBase ds = new DataBase();
     conn = ds.conn;
     st = conn.createStatement();
     rs = st.executeQuery(sql);
     while (rs.next()) {
        notice ni=new notice();
        ni.setid(rs.getInt(1));
        ni.settitle(rs.getString(2));
        ni.setdetail(rs.getString(3));
        ni.setdate(rs.getString(4));
        ni.setcourse_id(rs.getString(5));
        al.add(ni);

     }
     return al;
   }catch(Exception e){System.out.println(e.getMessage());return null;}

  }
  /**
   * 添加公告
   * @param ni
   */
  public void addNotice(notice ni)
  {
    Connection conn = null;
    Statement st = null;
    String sql =
        "insert into t_notice(title,detail,date,course_id)values(" +
        " '" +grad.util.DataFormate.toGb(ni.gettitle()) + "'," +
        " '" + grad.util.DataFormate.toGb(ni.getdetail()) + "'," +
        " getdate()," +
        " '" + ni.getcourse_id() + "')";
    System.out.println(sql);
    try {
      DataBase ds = new DataBase();
      conn = ds.conn;
      st = conn.createStatement();
      st.executeUpdate(sql);
    }
    catch (Exception e) {
      System.out.println(e.getMessage());
      return;
    }
  }
  /**
   * 刪除公告
   * @param id
   */
  public void delNotice(String id)
   {
     Connection conn = null;
     Statement st = null;
     String sql =
         "delete from t_notice where id='"+id+"'";
     System.out.println(sql);
     try {
       DataBase ds = new DataBase();
       conn = ds.conn;
       st = conn.createStatement();
       st.executeUpdate(sql);

     }
     catch (Exception e) {
       System.out.println(e.getMessage());
       return;
     }
   }

}