package com.nitpro.school.dao;

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

import com.nitpro.school.bean.Teacher;
import com.nitpro.school.bean.WorkContent;
import com.nitpro.school.bean.WorkList;
import com.nitpro.school.bean.WorkPList;
import com.nitpro.school.util.ConnectionPoolHelper;
import com.nitpro.school.util.Utils;

public class WorkDao {
    //————————————————————————————————————————完整作業信息——————————————————————————————————————————//
	//獲得全部作業目錄
	public List<WorkList> getWorkList(){
    	List<WorkList> list = new ArrayList<WorkList>();
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			StringBuffer sql = new StringBuffer("SELECT wl.worklistid, wl.ddate, wl.title,");
			sql.append("te.teacherid, te.name, te.sex, te.birthday, te.job, te.loginname, te.pwd, te.islock ")
			    .append("FROM worklist wl LEFT JOIN teacher te ON(wl.teacherid = te.teacherid)");
			Statement state = conn.createStatement();
			ResultSet rs = state.executeQuery(sql.toString());
			while(rs.next()){
				Teacher teacher = new Teacher();
				teacher.setTeacherId(rs.getInt("teacherid"));
				teacher.setName(rs.getString("name"));
				teacher.setSex(rs.getString("sex"));
				teacher.setBirthday(rs.getDate("birthday"));
				teacher.setJob(rs.getString("job"));
				teacher.setLoginName(rs.getString("loginname"));
				teacher.setPwd(rs.getString("pwd"));
				teacher.setIsLock(rs.getString("isLock"));
				
				WorkList wl = new WorkList();
				wl.setWorkListId(rs.getInt("worklistid"));
				wl.setTitle(rs.getString("title"));
				wl.setDdate(rs.getDate("ddate"));
				wl.setTeacher(teacher);
				
				list.add(wl);
			}
			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return list;
    }

	//獲得完整作業內容
	public WorkList getWorkListById(int workListId){
    	WorkList work = null;
    	Teacher teacher = null;
    	int lastWorkPListId = 0;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			StringBuffer sql = new StringBuffer("SELECT ");
			sql.append("wl.WorkListId, wl.Title wlTitle, wl.DDate,")
			    .append("wpl.WorkPListId, wpl.Title wplTitle,")
			    .append("wc.WorkContentId, wc.Type, wc.Title wcTitle, wc.ForSelect, wc.RightSelect,")
			    .append("te.TeacherId, te.Name, te.Sex, te.BirthDay, te.Job, te.LoginName, te.pwd, te.isLock ")
			    .append("FROM worklist wl ")
			    .append("LEFT JOIN workplist wpl ON(wl.WorkListId = wpl.WorkListId) ")
			    .append("LEFT JOIN workcontent wc ON(wpl.WorkPListId = wc.WorkPListId) ")
			    .append("LEFT JOIN teacher te ON(wl.TeacherId = te.TeacherId) ")
			    .append("WHERE wl.WorkListId = ? ")
			    .append("ORDER BY wpl.WorkPListId, wc.WorkContentId");
			PreparedStatement state = conn.prepareStatement(sql.toString());
			state.setInt(1, workListId);
			ResultSet rs = state.executeQuery();
			while(rs.next()){
				//創建教師
				if(teacher == null){
					teacher = new Teacher();
					teacher.setTeacherId(rs.getInt("teacherid"));
					teacher.setName(rs.getString("name"));
					teacher.setSex(rs.getString("sex"));
					teacher.setBirthday(rs.getDate("birthday"));
					teacher.setJob(rs.getString("job"));
					teacher.setLoginName(rs.getString("loginname"));
					teacher.setPwd(rs.getString("pwd"));
					teacher.setIsLock(rs.getString("isLock"));
				}
				
				//創建作業
				if(work == null){
					work = new WorkList();
					work.setWorkListId(rs.getInt("worklistid"));
					work.setTitle(rs.getString("wlTitle"));
					work.setDdate(rs.getDate("ddate"));
					work.setTeacher(teacher);
				}
				
				//創建作業分段標題
				int workPListId = rs.getInt("WorkPListId");
				if((workPListId != lastWorkPListId)&&(workPListId != 0)){
					lastWorkPListId = workPListId;
					
					WorkPList wpList = new WorkPList();
					wpList.setWorkPListId(rs.getInt("WorkPListId"));
					wpList.setWorkListId(rs.getInt("WorkListId"));
					wpList.setTitle(rs.getString("wplTitle"));
					
					List<WorkPList> workPLists = work.getWorkPList();
					if(workPLists == null){
						workPLists = new ArrayList<WorkPList>(); 
					}
					workPLists.add(wpList);
					work.setWorkPList(workPLists);
				}
				//創建作業內容
				int WorkContentId = rs.getInt("WorkContentId");
				if(WorkContentId != 0){
					WorkContent wc = new WorkContent();
					wc.setWorkContentId(rs.getInt("WorkContentId"));
					wc.setWorkPListId(rs.getInt("WorkPListId"));
					wc.setTitle(rs.getString("wcTitle"));
					wc.setType(rs.getString("Type"));
					wc.setForSelect(rs.getString("ForSelect"));
					wc.setRightSelect(rs.getString("RightSelect"));
					
					List<WorkPList> workPLists = work.getWorkPList();
					WorkPList wpList = workPLists.get(workPLists.size() - 1);//取得最后的一個小標題
					
					List<WorkContent> workContents = wpList.getWorkContent();
					if(workContents == null){
						workContents = new ArrayList<WorkContent>(); 
					}
					workContents.add(wc);
					wpList.setWorkContent(workContents);
				}
			}
			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return work;
    }
    
    //————————————————————————————————————————作業大標題信息——————————————————————————————————————————//
	//只獲得作業大標題信息
	public WorkList getWorkListOnly(int workListId){
    	WorkList work = null;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			StringBuffer sql = new StringBuffer("SELECT ");
			sql.append("wl.WorkListId, wl.Title, wl.DDate,")
			   .append("te.TeacherId, te.Name, te.Sex, te.BirthDay, te.Job, te.LoginName, te.pwd, te.isLock ")
			   .append("FROM worklist wl ")
			   .append("LEFT JOIN teacher te ON(wl.TeacherId = te.TeacherId) ")
			   .append("WHERE wl.WorkListId = ? ");
			PreparedStatement state = conn.prepareStatement(sql.toString());
			state.setInt(1, workListId);
			ResultSet rs = state.executeQuery();
			if(rs.next()){
				Teacher teacher = new Teacher();
				teacher.setTeacherId(rs.getInt("teacherid"));
				teacher.setName(rs.getString("name"));
				teacher.setSex(rs.getString("sex"));
				teacher.setBirthday(rs.getDate("birthday"));
				teacher.setJob(rs.getString("job"));
				teacher.setLoginName(rs.getString("loginname"));
				teacher.setPwd(rs.getString("pwd"));
				teacher.setIsLock(rs.getString("isLock"));
				
				work = new WorkList();
				work.setWorkListId(rs.getInt("worklistid"));
				work.setTitle(rs.getString("Title"));
				work.setDdate(rs.getDate("ddate"));
				work.setTeacher(teacher);
			}
			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return work;
    }

	//增加作業大標題
    public int insertWorkList(WorkList workList){
    	int result = 0;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "INSERT INTO worklist(TeacherId, Title, DDate) VALUES(?, ?, ?)";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setInt(1, workList.getTeacher().getTeacherId());
			state.setString(2, workList.getTitle());
			state.setDate(3, workList.getDdate()!=null ? new java.sql.Date(workList.getDdate().getTime()) : null);
			result = state.executeUpdate();
			
			ResultSet rs = state.executeQuery(Utils.lastIdSql);
			if(rs.next()){
				workList.setWorkListId(rs.getInt("id"));
			}

			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return result;
    }

	//修改作業大標題
    public int updateWorkList(WorkList workList){
    	int result = 0;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "UPDATE worklist SET TeacherId = ?, Title = ?, DDate = ? WHERE WorkListId = ?";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setInt(1, workList.getTeacher().getTeacherId());
			state.setString(2, workList.getTitle());
			state.setDate(3, workList.getDdate()!=null ? new java.sql.Date(workList.getDdate().getTime()) : null);
			state.setInt(4, workList.getWorkListId());
			result = state.executeUpdate();
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return result;
    }
    
    //————————————————————————————————————————作業小標題信息——————————————————————————————————————————//
	//只獲得作業小標題信息
	public WorkPList getWorkPListById(int workPListId){
		WorkPList workp = null;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			StringBuffer sql = new StringBuffer("SELECT ");
			sql.append("WorkPListId, WorkListId, Title ")
			   .append("FROM workplist ")
			   .append("WHERE WorkPListId = ? ");
			PreparedStatement state = conn.prepareStatement(sql.toString());
			state.setInt(1, workPListId);
			ResultSet rs = state.executeQuery();
			if(rs.next()){
				workp = new WorkPList();
				workp.setWorkPListId(rs.getInt("WorkPListId"));
				workp.setWorkListId(rs.getInt("WorkListId"));
				workp.setTitle(rs.getString("Title"));
			}
			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return workp;
    }
    
	//增加作業小標題
    public int insertWorkPList(WorkPList workPList){
    	int result = 0;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "INSERT INTO workplist(WorkListId, Title) VALUES(?, ?)";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setInt(1, workPList.getWorkListId());
			state.setString(2, workPList.getTitle());
			result = state.executeUpdate();
			
			ResultSet rs = state.executeQuery(Utils.lastIdSql);
			if(rs.next()){
				workPList.setWorkPListId(rs.getInt("id"));
			}

			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return result;
    }

	//修改作業小標題
    public int updateWorkPList(WorkPList workPList){
    	int result = 0;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "UPDATE workplist SET WorkListId = ?, Title = ? WHERE WorkPListId = ?";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setInt(1, workPList.getWorkListId());
			state.setString(2, workPList.getTitle());
			state.setInt(3, workPList.getWorkPListId());
			result = state.executeUpdate();
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return result;
    }

    //————————————————————————————————————————作業題目信息——————————————————————————————————————————//
    //獲得作業題目
    
	public WorkContent getWorkContent(int workContentId){
		WorkContent wc = null;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			StringBuffer sql = new StringBuffer("SELECT ");
			sql.append("WorkContentId, WorkPListId, Type, Title, ForSelect, RightSelect ")
			   .append("FROM workcontent ")
			   .append("WHERE WorkContentId = ? ");
			PreparedStatement state = conn.prepareStatement(sql.toString());
			state.setInt(1, workContentId);
			ResultSet rs = state.executeQuery();
			if(rs.next()){
				wc = new WorkContent();
				wc.setWorkContentId(rs.getInt("WorkContentId"));
				wc.setWorkPListId(rs.getInt("WorkPListId"));
				wc.setTitle(rs.getString("Title"));
				wc.setType(rs.getString("Type"));
				wc.setForSelect(rs.getString("ForSelect"));
				wc.setRightSelect(rs.getString("RightSelect"));
			}
			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return wc;
    }

	//增加作業題目
    public int insertWorkContent(WorkContent workContent){
    	int result = 0;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "INSERT INTO workcontent(WorkPListId, Type, Title, ForSelect, RightSelect) VALUES(?, ?, ?, ?, ?)";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setInt(1, workContent.getWorkPListId());
			state.setString(2, workContent.getType());
			state.setString(3, workContent.getTitle());
			state.setString(4, workContent.getForSelect());
			state.setString(5, workContent.getRightSelect());
			result = state.executeUpdate();
			
			ResultSet rs = state.executeQuery(Utils.lastIdSql);
			if(rs.next()){
				workContent.setWorkContentId(rs.getInt("id"));
			}

			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return result;
    }

	//修改作業題目
    public int updateWorkContent(WorkContent workContent){
    	int result = 0;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "UPDATE workcontent SET WorkPListId = ?, Type = ?, Title = ?, ForSelect = ?, RightSelect = ? WHERE WorkContentId = ?";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setInt(1, workContent.getWorkPListId());
			state.setString(2, workContent.getType());
			state.setString(3, workContent.getTitle());
			state.setString(4, workContent.getForSelect());
			state.setString(5, workContent.getRightSelect());
			state.setInt(6, workContent.getWorkContentId());
			result = state.executeUpdate();
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return result;
    }
}