?using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data.SqlClient;
using System.Data;
/// <summary>
///DALTravel 的摘要說明
/// </summary>
namespace corInfMan.DAL
{
    public class DALTravel
    {
        public DALTravel()
        {
            //
            //TODO: 在此處添加構造函數邏輯
            //
        }
        /// <summary>
        /// 返回是否有出差，是否得到確認
        /// </summary>
        /// <param name="username">查詢用戶名</param>
        /// <returns>0沒審核通過；1審核通過；2通過并已讀；3沒有任何出差記錄</returns>
        public int isCertified(string username)
        {
            string strSql = "select IsCertified from aspnet_Travel where UserName=@username";
            SqlParameter[] parm = { new SqlParameter("@username", SqlDbType.NChar) };
            parm[0].Value = username;
            int check;
            if (corInfMan.Utility.SQLHelper.Exists(strSql, parm))
            {
                check = int.Parse(corInfMan.Utility.SQLHelper.GetSingle(strSql, parm).ToString());
            }
            else
            {
                check = 3;
            }
            return check;
        }
        /// <summary>
        /// 提交申請
        /// </summary>
        /// <param name="now"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="moneyApply"></param>
        /// <param name="reason"></param>
        /// <param name="iscertified"></param>
        /// <param name="username"></param>
        /// <param name="isover"></param>
        /// <param name="moneygiven"></param>
        public void submitApply(DateTime now,DateTime start, DateTime end, int moneyApply, string reason,int iscertified,string username,int isover,int moneygiven)
        {
            string sqlStr="insert into aspnet_Travel (TimeBegin,TimeEnd,MoneyBegin,Reason,IsCertified,UserName,TimeApply,IsOver,MoneyGiven) values(@start,@end,@moneyApply,@reason,@iscertified,@username,@now,@isover,@moneygiven)";
            SqlParameter[] parm = { new SqlParameter("@start", SqlDbType.DateTime), new SqlParameter("@end", SqlDbType.DateTime), new SqlParameter("@moneyApply", SqlDbType.Int),
                                    new SqlParameter("@reason", SqlDbType.NVarChar),new SqlParameter("@iscertified",SqlDbType.Int), new SqlParameter("@username",SqlDbType.NVarChar),
                                    new SqlParameter("@now",SqlDbType.DateTime),new SqlParameter("@isover",SqlDbType.Int),new SqlParameter("@moneygiven",SqlDbType.Int)
                                    };
            parm[0].Value = start;
            parm[1].Value = end;
            parm[2].Value = moneyApply;
            parm[3].Value = reason;
            parm[4].Value = iscertified;
            parm[5].Value = username;
            parm[6].Value = now;
            parm[7].Value = isover;
            parm[8].Value = moneygiven;
         
            corInfMan.Utility.SQLHelper.ExecuteSql(sqlStr, parm);
        }
        /// <summary>
        /// 通過審核的申請
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public DataSet travelOverTable(string username)
        {
            string sqlStr = "select TimeApply, TimeBegin,TimeEnd,MoneyBegin,MoneyEnd,Reason,IsCertified from aspnet_Travel where UserName=@username and IsCertified=1 or IsCertified=2";
            SqlParameter[] parm = { new SqlParameter("@username",SqlDbType.NVarChar)};
            parm[0].Value = username;
            return corInfMan.Utility.SQLHelper.Query(sqlStr, parm);
        }
        /// <summary>
        /// 未通過審核的申請
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public DataSet travelTable(string username)
        {
            string sqlStr = "select TimeApply, TimeBegin,TimeEnd,MoneyBegin,MoneyEnd,Reason,IsCertified from aspnet_Travel where UserName=@username and IsCertified=0 or IsCertified=3";
            SqlParameter[] parm = { new SqlParameter("@username", SqlDbType.NVarChar) };
            parm[0].Value = username;
            return corInfMan.Utility.SQLHelper.Query(sqlStr, parm);
        }
        /// <summary>
        /// 部門經理審核所以出差表
        /// </summary>
        /// <returns></returns>
        public DataSet traveTableList()
        {
            string sqlStr = "select aspnet_Employee.EmployeeName,TimeApply, TimeBegin,TimeEnd,MoneyBegin,MoneyEnd,Reason,IsCertified from aspnet_Travel,aspnet_Employee where aspnet_Travel.UserName=aspnet_Employee.RegName";
            return corInfMan.Utility.SQLHelper.Query(sqlStr);
        }
        /// <summary>
        /// 財務部審核出差表
        /// </summary>
        /// <returns></returns>
        public DataSet travelTableAccountList()
        {
            string sqlStr = "select aspnet_Employee.EmployeeName,TimeApply, TimeBegin,TimeEnd,MoneyBegin,MoneyEnd,Reason,IsCertified from aspnet_Travel,aspnet_Employee where aspnet_Travel.UserName=aspnet_Employee.RegName and aspnet_Travel.MoneyGiven=1 and aspnet_Travel.IsOver=0";
            return corInfMan.Utility.SQLHelper.Query(sqlStr);
        }
        /// <summary>
        /// 部門經理審核通過
        /// </summary>
        /// <param name="name"></param>
        /// <param name="check"></param>
        public void isCertified(string name, int check)
        {
            string sqlStr = "update aspnet_Travel set IsCertified values @check where UserName=@name";
            SqlParameter[] parm = { new SqlParameter("@name",SqlDbType.NChar),new SqlParameter("@check",SqlDbType.Int)};
            parm[0].Value = name;
            parm[1].Value = check;
            corInfMan.Utility.SQLHelper.ExecuteSql(sqlStr, parm);
        }
        public void updateTravel(string name,DateTime dts,DateTime dte,string money,string reason)
        {
            string sqlStr = "update aspnet_Travel set (TimeBegin,TimeEnd,MoneyBegin,Reason) values (@dts,@dte,@money,@reason) where UserName=@name";
            SqlParameter[] parm = { new SqlParameter("name",SqlDbType.NChar),new SqlParameter("@dts",SqlDbType.DateTime),new SqlParameter("@dte",SqlDbType.DateTime),
                                  new SqlParameter("@money",SqlDbType.NChar),new SqlParameter("@reason",SqlDbType.NVarChar)};
            parm[0].Value = name;
            parm[1].Value = dts;
            parm[2].Value = dte;
            parm[3].Value = money;
            parm[4].Value = reason;
            corInfMan.Utility.SQLHelper.ExecuteSql(sqlStr, parm);
        }
    }
}