package cn.hmsoft.ltc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import cn.hmsoft.ltc.common.util.DbUtil;
import cn.hmsoft.ltc.model.Course;

/**
 * 课程管理
 * @author Administrator
 *
 */
public class CourseDao {

	/**
	 * 新增课程信息
	 * @param user
	 */
	public void insertCourse(Course course) {
		DbUtil util=new DbUtil();
		util.insert("insert into course(code,code_hubei,name,status,chapterNumber,teachBook,teachOutline,schoolName,batchNumber,editor,edition,press,verificationTextbook,remark) "
				+ "values('" + course.getCode() + "', "
					   + "'" + course.getCode() + "', "
				       + "'" + course.getName() + "', "
				       + "'" + course.getStatus() + "', "
				       + "'" + course.getChapterNumber() + "', "
				       + "'" + course.getTeachBook() + "', "
				       + "'" + course.getTeachOutline() + "', "
				       + "'" + course.getSchoolName() + "', "
				       + "'" + course.getBatchNumber() + "', "
 					   + "'" + course.getEditor() + "', "
 					   + "'" + course.getEdition() + "', "
				       + "'" + course.getPress() + "', "
				       + "'" + course.getVerificationTextbook() + "', "
				       + "'" + course.getRemark() + "')");
	}
	
	public Integer getCourseById(String code) throws SQLException {
		DbUtil util=new DbUtil();
		ResultSet rs = util.query("select id from course where code = '" + code + "'");
		Integer id = null;  //设置课程的默认章节数为10章
		while (rs.next()) {
			id = rs.getInt(1);
		}
		return id;
	}
	
	/**
	 *  查询课程章节数
	 *  @param courseCode 课程编号
	 * @return
	 * @throws SQLException
	 */
	public int selectCourse(String courseCode) throws SQLException {
		DbUtil util=new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("select chapterNumber from course where code = '"+courseCode+"'").toString();
		ResultSet rs = util.query(strSql.toString());
		int chapterNumber = 10;  //设置课程的默认章节数为10章
		while (rs.next()) {
			chapterNumber = rs.getInt(1);
		}
		return chapterNumber;
	}
	
	/**
	 *  查询课程根据学校过滤.
	 *  @param courseCode 课程编号
	 * @return
	 * @throws SQLException
	 */
	public List<Course> selectCourseBySchool(Integer courseId, String schoolName) throws SQLException {
		DbUtil util = new DbUtil();
		List<Course> lstCourse = new ArrayList<Course>();
		StringBuffer strSql = new StringBuffer();
		strSql.append("select id,code,name,status,chapterNumber,teachBook,teachOutline,schoolName,remark,batchNumber,editor,edition,press,verificationTextbook from course where 1=1");
		if (courseId != null) {
			strSql.append(" and id=").append(courseId);
		}
		if (schoolName != null) {
			strSql.append(" and schoolName = '" + schoolName + "'");
		}
		ResultSet rs = util.query(strSql.toString());
		while (rs.next()) {
			Course course = new Course();
			course.setId(rs.getInt(1));
			course.setCode(rs.getString(2));
			course.setName(rs.getString(3));
			course.setStatus(rs.getInt(4));
			course.setChapterNumber(rs.getInt(5));
			course.setTeachBook(rs.getString(6));
			course.setTeachOutline(rs.getString(7));
			course.setSchoolName(rs.getString(8));
			course.setRemark(rs.getString(9));
			course.setBatchNumber(rs.getString(10));
			course.setEditor(rs.getString(11));
			course.setEdition(rs.getString(12));
			course.setPress(rs.getString(13));
			course.setVerificationTextbook(rs.getInt(14));
			course.setStatusName(course.getStatus()==0 ? "停用" : "启用");
			course.setVerificationTextbookName(course.getVerificationTextbook()==0 ? "未校验" : "已校验");
			lstCourse.add(course);
		}
		return lstCourse;
	}
	
	/**
	 *  校验课程编号是否存在
	 * @param courseCode
	 * @return true 不存在，false 存在
	 * @throws SQLException
	 */
	public boolean checkCode(String courseCode) throws SQLException {
		boolean flag = true;
		DbUtil util=new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("select count(*) from course where code = '"+courseCode+"' ").toString();
		ResultSet rs = util.query(strSql.toString());
		int count = 0;
		while (rs.next()) {
			count = rs.getInt(1);
			System.out.println("课程编码code:" + courseCode+" ;count = "+rs.getInt(1));
		}
		if(count>0) {
			flag = false;
		}
		return flag;
	}
	
	/**
	 * 根据ID删除课程信息息
	 * @param id
	 * @return true 成功，false 失败
	 * @throws SQLException
	 */
	public boolean deleteCourse(int id)throws SQLException{
		boolean flag = false;
		DbUtil util=new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("delete from course where id = "+id+" ");
		int count = util.delete(strSql.toString());
		if(count>0) {
			flag = true;; //成功
		}
		return flag;
	}
	
	/**
	 * 查询课程信息 by Id
	 * 
	 * @return
	 * @throws SQLException
	 */
	public Course selectCourseById(Integer id) throws SQLException {
		DbUtil util = new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("select id,code,name,status,chapterNumber,teachBook,teachOutline,schoolName,remark,batchNumber,editor,edition,press,verificationTextbook from course where id = " + id).toString();
		ResultSet rs = util.query(strSql.toString());
		while (rs.next()) {
			Course course = new Course();
			course.setId(rs.getInt(1));
			course.setCode(rs.getString(2));
			course.setName(rs.getString(3));
			course.setStatus(rs.getInt(4));
			course.setChapterNumber(rs.getInt(5));
			course.setTeachBook(rs.getString(6));
			course.setTeachOutline(rs.getString(7));
			course.setSchoolName(rs.getString(8));
			course.setRemark(rs.getString(9));
			course.setBatchNumber(rs.getString(10));
			course.setEditor(rs.getString(11));
			course.setEdition(rs.getString(12));
			course.setPress(rs.getString(13));
			course.setVerificationTextbook(rs.getInt(14));
			course.setStatusName(course.getStatus()==0 ? "停用" : "启用");
			course.setVerificationTextbookName(course.getVerificationTextbook()==0 ? "未校验" : "已校验");
			return course;
		}
		return null;
	}
	
	/**
	 *  查询课程根据学校过滤.
	 *  @param courseCode 课程编号
	 * @return
	 * @throws SQLException
	 */
	public List<Course> selectCourseBySchool() throws SQLException {
		DbUtil util = new DbUtil();
		List<Course> lstCourse = new ArrayList<Course>();
		StringBuffer strSql = new StringBuffer();
		strSql.append("select id,code,name,status,chapterNumber,teachBook,teachOutline,schoolName,remark,batchNumber,editor,edition,press,verificationTextbook from course ").toString();
		ResultSet rs = util.query(strSql.toString());
		while (rs.next()) {
			Course course = new Course();
			course.setId(rs.getInt(1));
			course.setCode(rs.getString(2));
			course.setName(rs.getString(3));
			course.setStatus(rs.getInt(4));
			course.setChapterNumber(rs.getInt(5));
			course.setTeachBook(rs.getString(6));
			course.setTeachOutline(rs.getString(7));
			course.setSchoolName(rs.getString(8));
			course.setRemark(rs.getString(9));
			course.setBatchNumber(rs.getString(10));
			course.setEditor(rs.getString(11));
			course.setEdition(rs.getString(12));
			course.setPress(rs.getString(13));
			course.setVerificationTextbook(rs.getInt(14));
			course.setStatusName(course.getStatus()==0 ? "停用" : "启用");
			course.setVerificationTextbookName(course.getVerificationTextbook()==0 ? "未校验" : "已校验");
			lstCourse.add(course);
		}
		return lstCourse;
	}
	
	/**
	 * 修改课程章节信息
	 * @param paperNo
	 * @param status
	 */
	public void updateCourseByCode(Integer id, Integer chapterNumber) {
		DbUtil util = new DbUtil();
		util.update("update course set chapterNumber=" + chapterNumber + " where id = "+id+" ");
	}
	
	/**
	 * 修改课程校验信息   【教材是否校验】 0:未校验，1：校验
	 * @param id 课程id
	 */
	public void updateCourseById(Integer id) {
		DbUtil util = new DbUtil();
		util.update("update course set verificationTextbook=1 where id = "+id+" ");
	}
}
