/* publish assignment query
     * 发布作业查询，将发布作业信息从数据库中查询出来，返回Pd_P_assignment
     * 输入参数为：发布作业的序号，即id(String)
     * 若有记录，则返回Pd_P_assignment，若无记录，则返回null
     * Pd_P_assignment包含所有 发布作业 的信息，int id,String name,String time_deadline,String lesson_id,String teacher_id,String content,Blob file
     * */
    public Pd_P_assignment P_assignment_query(String id) throws Exception
    {
        String sql="select * from publish_assignment where as_id = ?";
        PreparedStatement pre = connect.prepareStatement(sql);
        pre.setString(1,id);
        ResultSet rs = pre.executeQuery();
        Pd_P_assignment pdd = new Pd_P_assignment();
        while(rs.next())
        {
            pdd.setId(rs.getInt(1));
            pdd.setName(rs.getString(2));
            pdd.setTime_deadline(rs.getString(3));
            pdd.setLesson_id(rs.getString(4));
            pdd.setTeacher_id(rs.getString(5));
            pdd.setContent(rs.getString(6));
            pdd.setFile(rs.getBlob(7));
            pdd.setType(rs.getString(8));
//            byte[] bytes = blob.getBytes(1, (int) blob.length());
//            String content = new String(bytes, "UTF-8");
//            pdd.setResult_file(content);
        }
        pre.close();
        if(pdd.getId()!=0)
            return pdd;
        else
            return null;
    }
    /* publish assignment Dim Query
     * 发布作业信息模糊查询，输入序号，名称，截止时间，所属课程id，所属老师id，文字信息中的一项或多项，实现模糊查询，6项都不输入则为全部显示
     * 输入参数：序号id(String)，名称name(String)，截止时间time_deadline(String)，所属课程id lesson_id(String)，所属老师id teacher_id(String)，文字信息content(String)
     * 返回参数类型为 wangzhan.Pd_stu[] ,储存所有符合条件的 wangzhan.Pd_stu
     * */
    //    sql="select stu_id from student where stu_id like ? and stu_name like ? and stu_sex like ? and stu_class like ? and stu_major like ? and stu_faculty like ?";
    public Pd_P_assignment[] P_assignment_DimQuery(String id,String name,String time_deadline,String lesson_id,String teacher_id,String content,String type) throws Exception
    {
        String sql="select as_id from publish_assignment where as_id like ? and as_name like ? and as_time_deadline like ? and as_lesson_id like ? " +
                "and as_teacher_id like ? and as_content like ? and as_type like ?";
        PreparedStatement pre = connect.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        pre.setString(1,"%"+id+"%");
        pre.setString(2,"%"+name+"%");
        pre.setString(3,"%"+time_deadline+"%");
        pre.setString(4,"%"+lesson_id+"%");
        pre.setString(5,"%"+teacher_id+"%");
        pre.setString(6,"%"+content+"%");
        pre.setString(7,"%"+type+"%");
        ResultSet rs = pre.executeQuery();
        rs.last();
        int rowCount=rs.getRow();
        rs.beforeFirst();
        Pd_P_assignment[] pdStus = new Pd_P_assignment[rowCount];
        int i=0;
        while(rs.next())
        {
            pdStus[i]=P_assignment_query(rs.getInt(1)+"");
            i++;
        }
        return pdStus;
    }
    public Pd_P_assignment[] P_assignment_DimQueryType(String type) throws Exception
    {
        return P_assignment_DimQuery("","","","","","",type);
    }
    /* submit assignment query
     * 提交作业查询，将提交作业信息从数据库中查询出来，返回Pd_S_assignment
     * 输入参数为：提交作业的序号，即id(String)
     * 若有记录，则返回Pd_S_assignment，若无记录，则返回null
     * Pd_S_assignment包含所有 提交作业 的信息，int id,String student_id,String time,String pu_ass_id,String content,Blob file
     * */
    public Pd_S_assignment S_assignment_query(String id) throws Exception
    {
        String sql="select * from submit_assignment where as_id = ?";
        PreparedStatement pre = connect.prepareStatement(sql);
        pre.setString(1,id);
        ResultSet rs = pre.executeQuery();
        Pd_S_assignment pdd = new Pd_S_assignment();
        while(rs.next())
        {
            pdd.setId(rs.getInt(1));
            pdd.setStudent_id(rs.getString(2));
            pdd.setTime(rs.getString(3));
            pdd.setPu_ass_id(rs.getString(4));
            pdd.setContent(rs.getString(5));
            pdd.setFile(rs.getBlob(6));
            pdd.setState(rs.getString(7));
            pdd.setScore(rs.getInt(8));
            pdd.setPu_name(rs.getString(9));
            pdd.setLesson_name(rs.getString(10));
            pdd.setStudent_name(rs.getString(11));
        }
        pre.close();
        if(pdd.getId()!=0)
            return pdd;
        else
            return null;
    }