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

/// <summary>
/// LinkmanDA 的摘要說明
/// </summary>
public class LinkmanDA
{
    //定義常量表示字段名稱或SQL語句。
    private const string SQL_INSERT_LINKMAN = "INSERT INTO linkman VALUES(@userid,@name,@phone, @mail,@qq,@birthday,@like,@sex,@note)";
    private const string PARM_COMPANY_ID = "@userid";
    private const string PARM_LINKMAN_NAME = "@name";
    private const string PARM_LINKMAN_PHONE = "@phone";
    private const string PARM_LINKMAN_MAIL = "@mail";
    private const string PARM_LINKMAN_QQ = "@qq";
    private const string PARM_LINKMAN_BIRTHDAY = "@birthday";
    private const string PARM_LINKMAN_LIKE = "@like";
    private const string PARM_LINKMAN_SEX = "@sex";
    private const string PARM_LINKMAN_NOTE = "@note";
    private const string PARM_COMPANY_NAME= "@company";
    private const string SQL_SELECT_COMPANYNAME = "SELECT userid FROM userinfo WHERE username=@company";

    private const string SQL_SELECT_BIRTHDAY = "SELECT linkmanname FROM linkman WHERE linkmanbirthday between @today and @endday";


	public LinkmanDA()
	{
	}
    /// <summary>
    /// 添加聯系人資料
    /// </summary>
    /// <param name="user">聯系人資料實體</param>
    /// <returns>添加是否成功</returns>
    public bool InsertLinkman(LinkmanEntity linkman)
    {
        StringBuilder strSQL = new StringBuilder();
        SqlParameter[] linkmanParms = GetParameters();
        SqlCommand cmd = new SqlCommand();

        // 依次給實體參數賦值
        linkmanParms[1].Value = linkman.Name;
        linkmanParms[2].Value = linkman.Phone;
        linkmanParms[3].Value = linkman.Mail;
        linkmanParms[4].Value = linkman.QQ;
        linkmanParms[5].Value = linkman.Birthday;
        linkmanParms[6].Value = linkman.Like;
        linkmanParms[7].Value = linkman.Sex;
        linkmanParms[8].Value = linkman.Note;
        //因為聯系人表中的USERID是用來存放聯系人所在單位的
        //所以必須通過名稱先獲取這單位的ID
        //獲取單位的ID
        int userid = GetCompanyID(linkman.CompanyName);
        //如果userid為0，表示聯系人所在單位并不是公司用戶，將其設置為空。
        if (userid == 0)
        {
            linkmanParms[0].Value = DBNull.Value;
        }

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

        //獲取數據庫的連接字符串
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            strSQL.Append(SQL_INSERT_LINKMAN);
            //打開數據庫連接，執行命令
            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;
        }
    }
    public SqlDataReader GetBirthdayMan()
    {
        //初始化輸入參數并賦值
        SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@today", SqlDbType.DateTime),
                                          new SqlParameter("@endday", SqlDbType.DateTime) };
       //獲取今天的日期
        parm[0].Value = DateTime.Now.Date;
        //加7天表示一周后的日期
        parm[1].Value = DateTime.Now.Date.AddDays(7);
        //根據日期區間返回SqlDataReader
        SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_BIRTHDAY, parm);
        return rdr;
    }
    #region 私有方法
    /// <summary>
    /// 獲取單位ID的方法
    /// </summary>
    /// <param name="cityname">單位名稱</param>
    /// <returns>該單位的ID</returns>
    private int GetCompanyID(string companyname)
    {
        int userid = 0;

        //創建新參數并給參數賦值，用來指定單位名稱
        SqlParameter parm = new SqlParameter(PARM_COMPANY_NAME, SqlDbType.NVarChar, 20);
        parm.Value = companyname;

        //調用SqlHelper訪問組件的方法返回第一行第一列的值
        try
        {
            userid = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_COMPANYNAME, parm);
        }
        catch
        {
            //出現錯誤時，設置為0
            userid =0;
        }

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

        //首先判斷緩存是否已經存在
        if (parms == null)
        {
            //緩存不存在的情況下，新建參數列表
            parms = new SqlParameter[] {
                    new SqlParameter(PARM_COMPANY_ID, SqlDbType.Int),
                    new SqlParameter(PARM_LINKMAN_NAME, SqlDbType.NVarChar,20),
                    new SqlParameter(PARM_LINKMAN_PHONE, SqlDbType.NVarChar, 20),
                    new SqlParameter(PARM_LINKMAN_MAIL, SqlDbType.NVarChar, 20),
                    new SqlParameter(PARM_LINKMAN_QQ, SqlDbType.NVarChar, 20),
                    new SqlParameter(PARM_LINKMAN_BIRTHDAY, SqlDbType.DateTime),
                    new SqlParameter(PARM_LINKMAN_LIKE, SqlDbType.NVarChar,50),
                    new SqlParameter(PARM_LINKMAN_SEX, SqlDbType.NVarChar,2),
                    new SqlParameter(PARM_LINKMAN_NOTE, SqlDbType.NVarChar,50) };

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