package com.sevenEleven.servlet.admin2;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.sevenEleven.javaBean.CConnectMSSQL;
import com.sevenEleven.javaBean.CDBOracle;

/**
 * Servlet implementation class for Servlet: OneLesson2ListSchemeMaker
 * 
 */
public class OneLesson2ListSchemeMaker extends javax.servlet.http.HttpServlet
		implements javax.servlet.Servlet {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	/*
	 * (non-Java-doc)
	 * 
	 * @see javax.servlet.http.HttpServlet#HttpServlet()
	 */
	public OneLesson2ListSchemeMaker() {
		super();
	}

	/*
	 * (non-Java-doc)
	 * 
	 * @see javax.servlet.http.HttpServlet#doGet(HttpServletRequest request,
	 *      HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doPost(request, response);
	}

	/*
	 * (non-Java-doc)
	 * 
	 * @see javax.servlet.http.HttpServlet#doPost(HttpServletRequest request,
	 *      HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub

		String TIMEID1 = request.getParameter("time1");
		String L2ID1 = request.getParameter("lesson2");
		String TID1 = request.getParameter("teacher1");
		String CRID1 = request.getParameter("classroom1");
		// System.out.print(TIMEID1);
		// System.out.print(L2ID1);
		// System.out.print(TID1);
		// System.out.print(CRID1);
		String sql1 = "insert into lesson2_list values (" + CRID1 + "," + TID1
				+ "," + L2ID1 + ",'" + TIMEID1 + "')";
		// System.out.print(sql1);
		String sqlcheckteacher = "SELECT TIME.TIME_ID,L2_NAME,T_NAME,CR_NAME FROM TIME,LESSON2,TEACHER,CLASSROOM,LESSON2_LIST WHERE TIME.TIME_ID=LESSON2_LIST.TIME_ID AND LESSON2.L2_ID=LESSON2_LIST.L2_ID AND TEACHER.T_ID=LESSON2_LIST.T_ID AND CLASSROOM.CR_ID=LESSON2_LIST.CR_ID AND LESSON2_LIST.TIME_ID='"
				+ TIMEID1 + "' AND LESSON2_LIST.T_ID=" + TID1;

		String sqlcheckclassroom = "SELECT TIME.TIME_ID,L2_NAME,T_NAME,CR_NAME FROM TIME,LESSON2,TEACHER,CLASSROOM,LESSON2_LIST WHERE TIME.TIME_ID=LESSON2_LIST.TIME_ID AND LESSON2.L2_ID=LESSON2_LIST.L2_ID AND TEACHER.T_ID=LESSON2_LIST.T_ID AND CLASSROOM.CR_ID=LESSON2_LIST.CR_ID AND LESSON2_LIST.TIME_ID='"
				+ TIMEID1 + "' AND LESSON2_LIST.CR_ID=" + CRID1;
		String sqlcheckLessonOfTeacher  = "SELECT count(*) FROM TEACHER t WHERE t.T_SKILL = (SELECT L2_NAME FROM LESSON2 L2 WHERE L2.L2_ID =" + L2ID1 + ")";
		// System.out.print(sqlcheckteacher);
	    System.out.print(sqlcheckclassroom);
		CDBOracle db = new CDBOracle();
		CConnectMSSQL check = new CConnectMSSQL();
		List checkteacherlist = check
				.getNestedListFromResultSet(sqlcheckteacher);// 檢查老師沖突
		CConnectMSSQL check3 = new CConnectMSSQL();
		List checkclassroomlist = check3
				.getNestedListFromResultSet(sqlcheckclassroom);// 檢查教室沖突
		// System.out.print(checkteacherlist);
		CConnectMSSQL check4 = new CConnectMSSQL();
        List checkLessonOfTeacher = check4.getNestedListFromResultSet(sqlcheckLessonOfTeacher);//檢查老師的技能與課程是否一致
		request.setCharacterEncoding("gb2312");
		response.setContentType("text/html; charset=gb2312");
		PrintWriter out = response.getWriter();
		System.out.print(checkclassroomlist);
		boolean b = String.valueOf(checkteacherlist).equals("[]");// 在數(shù)據(jù)庫(kù)中檢查老師的狀態(tài)返回值
		boolean c = String.valueOf(checkclassroomlist).equals("[]");
		boolean d = String.valueOf(checkLessonOfTeacher).equals("[]");
		System.out.print(b);
		if (!b) {
			out.print("<script language=javascript>alert('任課老師系統(tǒng)號(hào)："
							+ TID1
							+ "已經(jīng)沒有');this.location.href='info_admin.jsp'</script>");

		}

		else if (!c) {
			out.print("<script language=javascript>alert('教室系統(tǒng)號(hào)："
							+ CRID1
							+ "已經(jīng)被預(yù)訂！請(qǐng)核對(duì)后重新提交');this.location.href='info_admin.jsp'</script>");
		}else if(!d){
			out.print("<script language=javascript>alert('任課老師號(hào)："
					+ TID1
					+"沒有課程ID為: "
					+ L2ID1
					+ " 的技能,請(qǐng)核對(duì)后重新提交');this.location.href='info_admin.jsp'</script>");
		}
		else {
			db.executeUpdate(sql1);
			response.sendRedirect("info_admin.jsp");
		}
	}
}