?using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// TaskDA 的摘要說明
/// </summary>
public class TaskDA
{
        //定義常量表示字段名稱或SQL語句。
    private const string SQL_INSERT_TASK = "INSERT INTO task VALUES(@employeeid,@userid,@title,@begindate,@enddate,@note)";
    private const string PARM_TASK_TITLE = "@title";
    private const string PARM_TASK_NOTE = "@note";
    private const string PARM_TASK_BEGINDATE = "@begindate";
    private const string PARM_TASK_ENDDATE = "@enddate";
    private const string PARM_EMPLOYEE_NAME = "@name";
    private const string PARM_EMPLOYEE_ID = "@employeeid";
    private const string PARM_USER_NAME = "@username";
    private const string PARM_USER_ID = "@userid";
    private const string SQL_SELECT_EMPLOYEEID = "SELECT employeeid FROM employeeinfo WHERE employeename=@name";
    private const string SQL_SELECT_USERID = "SELECT userid FROM userinfo WHERE username=@username";

    public TaskDA()
	{
	}
    /// <summary>
    /// 添加任務
    /// </summary>
    /// <param name="TaskEntity">任務實體</param>
    /// <returns>添加是否成功</returns>
    public bool InsertTask(TaskEntity task)
    {
        StringBuilder strSQL = new StringBuilder();
        SqlParameter[] taskParms = GetParameters();
        SqlCommand cmd = new SqlCommand();

        // 依次給實體參數賦值
        taskParms[2].Value = task.Title;
        taskParms[3].Value = task.Note;
        taskParms[4].Value = task.BeginDate;
        taskParms[5].Value = task.EndDate;
        //獲取員工的ID
        int employeeid = GetEmployeeID(task.Name);
        taskParms[0].Value = employeeid;
        //獲取客戶的ID
        int userid = GetUserID(task.UserName);
        taskParms[1].Value = userid;

        //遍歷所有參數，并將參數添加到SqlCommand命令中
        foreach (SqlParameter parm in taskParms)
            cmd.Parameters.Add(parm);

        //獲取數據庫的連接字符串
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            strSQL.Append(SQL_INSERT_TASK);
            //打開數據庫連接，執行命令
            conn.Open();
            //設置Sqlcommand命令的屬性
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSQL.ToString();
            //執行添加的SqlCommand命令
            int val = cmd.ExecuteNonQuery();
            //清空SqlCommand命令中的參數
            cmd.Parameters.Clear();
            //判斷是否添加成功，注意返回的是添加是否成功，不是影響的行數
            if (val > 0)
                return true;
            else
                return false;
        }
    }
    #region 私有方法
    /// <summary>
    /// 獲取員工ID的方法
    /// </summary>
    /// <param name="cityname">員工名稱</param>
    /// <returns>該員工的ID</returns>
    private int GetEmployeeID(string employeename)
    {
        //創建新參數并給參數賦值，用來指定會員名稱
        SqlParameter parm = new SqlParameter(PARM_EMPLOYEE_NAME, SqlDbType.NVarChar, 20);
        parm.Value = employeename;

        //調用SqlHelper訪問組件的方法返回第一行第一列的值
        //如果用戶填寫錯誤的員工名稱，則給予錯誤提示
        try
        {
            int employeeid = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_EMPLOYEEID, parm);
            return employeeid;
        }
        catch(Exception e)
        {
            throw new Exception("請確認姓名填寫是否正確！");
        }

    }
    /// <summary>
    /// 獲取客戶ID的方法
    /// </summary>
    /// <param name="username">客戶名稱</param>
    /// <returns>該客戶的ID</returns>
    private int GetUserID(string username)
    {
        //創建新參數并給參數賦值，用來指定會員名稱
        SqlParameter parm = new SqlParameter(PARM_USER_NAME, SqlDbType.NVarChar, 20);
        parm.Value = username;

        //調用SqlHelper訪問組件的方法返回第一行第一列的值
        //如果用戶填寫錯誤的客戶名稱，則給予錯誤提示
        try
        {
            int userid = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_USERID, parm);
            return userid;
        }
        catch (Exception e)
        {
            throw new Exception("請確認客戶名稱填寫是否正確！");
        }

    }
    private static SqlParameter[] GetParameters()
    {
        //將SQL_INSERT_WORKLOG做為哈希表緩存的鍵值
        SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_INSERT_TASK);

        //首先判斷緩存是否已經存在
        if (parms == null)
        {
            //緩存不存在的情況下，新建參數列表
            parms = new SqlParameter[] {
                    new SqlParameter(PARM_EMPLOYEE_ID, SqlDbType.Int),
                    new SqlParameter(PARM_USER_ID, SqlDbType.Int),
                    new SqlParameter(PARM_TASK_TITLE, SqlDbType.NVarChar, 20),
                    new SqlParameter(PARM_TASK_NOTE, SqlDbType.NVarChar, 100),
                    new SqlParameter(PARM_TASK_BEGINDATE, SqlDbType.DateTime), 
                    new SqlParameter(PARM_TASK_ENDDATE, SqlDbType.DateTime) };

            //將新建的參數列表添加到哈希表中緩存起來
            SqlHelper.CacheParameters(SQL_INSERT_TASK, parms);
        }
        //返回參數數組
        return parms;
    }
    #endregion
}