|
|
using System;using System.Data;using System.Text;using System.Data.SqlClient;using OT.IDAL;using DBUtility;//Please add references
namespace OT.SQLServerDAL{ /// <summary>
/// 資料訪問類:OTB_SYS_Members
/// </summary>
public partial class OTB_SYS_Members : IOTB_SYS_Members { public OTB_SYS_Members() { } #region Method
/// <summary>
/// 是否存在該記錄
/// </summary>
public bool Exists(string MemberID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) from OTB_SYS_Members"); strSql.Append(" where MemberID=@MemberID "); SqlParameter[] parameters = { new SqlParameter("@MemberID", SqlDbType.VarChar,10) }; parameters[0].Value = MemberID;
return DbHelperSQL.Exists(strSql.ToString(), parameters); } /// <summary>
/// 是否存在該記錄
/// </summary>
public bool ExistsColor(string MemberID, string Calcolor) {
StringBuilder strSql = new StringBuilder(); strSql.Append(" DECLARE @TempID int "); strSql.Append(" SELECT @TempID = count(1) FROM [OTB_SYS_Members] WHERE CalColor=@CalColor AND MemberID<>@MemberID "); SqlParameter[] parameters = { new SqlParameter("@MemberID", SqlDbType.VarChar,10) ,new SqlParameter("@Calcolor", SqlDbType.VarChar,20) }; parameters[0].Value = MemberID; parameters[1].Value = Calcolor; return DbHelperSQL.Exists(strSql.ToString(), parameters); }
/// <summary>
/// 增加一條資料
/// </summary>
public bool Add(OT.Model.OTB_SYS_Members model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into OTB_SYS_Members("); strSql.Append("MemberID,Password,MemberName,Email,GoogleAccount,ContectTEL,ContectExt,ContectFax,ContectCell,Address,BirthDate,ArriveDate,EmergencyContect,EmergencyTEL,EmergencyExt,EmergencyFax,EmergencyCell,EmergencyEMail,JobTitle,JobClass,ChiefID,DepartmentID,LeaveDate,Effective,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate,CalColor,Skype,Line,Wechat,MemberPic,QQ,PrjTypeID,IsOutside,AskLeaveGuid,SysShowMode)"); strSql.Append(" values ("); strSql.Append("@MemberID,@Password,@MemberName,@Email,@GoogleAccount,@ContectTEL,@ContectExt,@ContectFax,@ContectCell,@Address,@BirthDate,@ArriveDate,@EmergencyContect,@EmergencyTEL,@EmergencyExt,@EmergencyFax,@EmergencyCell,@EmergencyEMail,@JobTitle,@JobClass,@ChiefID,@DepartmentID,@LeaveDate,@Effective,@Memo,@CreateUser,getdate(),@CreateUser,getdate(),@CalColor,@Skype,@Line,@Wechat,@MemberPic,@QQ,@PrjTypeID,@IsOutside,@AskLeaveGuid,@SysShowMode)"); SqlParameter[] parameters = { new SqlParameter("@MemberID", SqlDbType.VarChar,50), new SqlParameter("@Password", SqlDbType.VarChar,100), new SqlParameter("@MemberName", SqlDbType.NVarChar,100), new SqlParameter("@Email", SqlDbType.NVarChar,200), new SqlParameter("@GoogleAccount", SqlDbType.VarChar,200), new SqlParameter("@ContectTEL", SqlDbType.VarChar,50), new SqlParameter("@ContectExt", SqlDbType.VarChar,50), new SqlParameter("@ContectFax", SqlDbType.VarChar,50), new SqlParameter("@ContectCell", SqlDbType.VarChar,50), new SqlParameter("@Address", SqlDbType.NVarChar,500), new SqlParameter("@BirthDate", SqlDbType.DateTime), new SqlParameter("@ArriveDate", SqlDbType.DateTime), new SqlParameter("@EmergencyContect", SqlDbType.NVarChar,100), new SqlParameter("@EmergencyTEL", SqlDbType.VarChar,50), new SqlParameter("@EmergencyExt", SqlDbType.VarChar,20), new SqlParameter("@EmergencyFax", SqlDbType.VarChar,50), new SqlParameter("@EmergencyCell", SqlDbType.VarChar,50), new SqlParameter("@EmergencyEMail", SqlDbType.NVarChar,200), new SqlParameter("@JobTitle", SqlDbType.NVarChar,10), new SqlParameter("@JobClass", SqlDbType.VarChar,200), new SqlParameter("@ChiefID", SqlDbType.VarChar,50), new SqlParameter("@DepartmentID", SqlDbType.VarChar,10), new SqlParameter("@LeaveDate", SqlDbType.DateTime), new SqlParameter("@Effective", SqlDbType.Char,1), new SqlParameter("@Memo", SqlDbType.NVarChar), new SqlParameter("@CreateUser", SqlDbType.VarChar,50), new SqlParameter("@CreateDate", SqlDbType.DateTime), new SqlParameter("@ModifyUser", SqlDbType.VarChar,50), new SqlParameter("@ModifyDate", SqlDbType.DateTime), new SqlParameter("@CalColor", SqlDbType.NVarChar,20), new SqlParameter("@Skype", SqlDbType.VarChar,50), new SqlParameter("@Line", SqlDbType.VarChar,50), new SqlParameter("@Wechat", SqlDbType.VarChar,50), new SqlParameter("@MemberPic", SqlDbType.Char,36), new SqlParameter("@QQ", SqlDbType.VarChar,50), new SqlParameter("@PrjTypeID", SqlDbType.VarChar,500), new SqlParameter("@IsOutside", SqlDbType.Char,1), new SqlParameter("@AskLeaveGuid", SqlDbType.VarChar,50), new SqlParameter("@SysShowMode", SqlDbType.Char,1)}; parameters[0].Value = model.MemberID; parameters[1].Value = model.Password; parameters[2].Value = model.MemberName; parameters[3].Value = model.Email; parameters[4].Value = model.GoogleAccount; parameters[5].Value = model.ContectTEL; parameters[6].Value = model.ContectExt; parameters[7].Value = model.ContectFax; parameters[8].Value = model.ContectCell; parameters[9].Value = model.Address; parameters[10].Value = model.BirthDate; parameters[11].Value = model.ArriveDate; parameters[12].Value = model.EmergencyContect; parameters[13].Value = model.EmergencyTEL; parameters[14].Value = model.EmergencyExt; parameters[15].Value = model.EmergencyFax; parameters[16].Value = model.EmergencyCell; parameters[17].Value = model.EmergencyEMail; parameters[18].Value = model.JobTitle; parameters[19].Value = model.JobClass; parameters[20].Value = model.ChiefID; parameters[21].Value = model.DepartmentID; parameters[22].Value = model.LeaveDate; parameters[23].Value = model.Effective; parameters[24].Value = model.Memo; parameters[25].Value = model.CreateUser; parameters[26].Value = model.CreateDate; parameters[27].Value = model.ModifyUser; parameters[28].Value = model.ModifyDate; parameters[29].Value = model.CalColor; parameters[30].Value = model.Skype; parameters[31].Value = model.Line; parameters[32].Value = model.Wechat; parameters[33].Value = model.MemberPic; parameters[34].Value = model.QQ; parameters[35].Value = model.PrjTypeID; parameters[36].Value = model.IsOutside; parameters[37].Value = model.AskLeaveGuid; parameters[38].Value = model.SysShowMode;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } }
/// <summary>
/// 更新一條資料
/// </summary>
public bool Update(OT.Model.OTB_SYS_Members model) { StringBuilder strSql = new StringBuilder(); strSql.Append(" IF @Password <> '' "); strSql.Append(" BEGIN "); strSql.Append(" UPDATE [OTB_SYS_Members] "); strSql.Append(" SET [Password] = @Password "); strSql.Append(" WHERE MemberID = @MemberID "); strSql.Append(" END "); strSql.Append("update OTB_SYS_Members set "); strSql.Append("MemberName=@MemberName,"); strSql.Append("Email=@Email,"); strSql.Append("GoogleAccount=@GoogleAccount,"); strSql.Append("ContectTEL=@ContectTEL,"); strSql.Append("ContectExt=@ContectExt,"); strSql.Append("ContectFax=@ContectFax,"); strSql.Append("ContectCell=@ContectCell,"); strSql.Append("Address=@Address,"); strSql.Append("BirthDate=@BirthDate,"); strSql.Append("ArriveDate=@ArriveDate,"); strSql.Append("EmergencyContect=@EmergencyContect,"); strSql.Append("EmergencyTEL=@EmergencyTEL,"); strSql.Append("EmergencyExt=@EmergencyExt,"); strSql.Append("EmergencyFax=@EmergencyFax,"); strSql.Append("EmergencyCell=@EmergencyCell,"); strSql.Append("EmergencyEMail=@EmergencyEMail,"); strSql.Append("JobTitle=@JobTitle,"); strSql.Append("JobClass=@JobClass,"); strSql.Append("ChiefID=@ChiefID,"); strSql.Append("DepartmentID=@DepartmentID,"); strSql.Append("LeaveDate=@LeaveDate,"); strSql.Append("Effective=@Effective,"); strSql.Append("Memo=@Memo,"); strSql.Append("ModifyUser=@ModifyUser,"); strSql.Append("ModifyDate=getdate(),"); strSql.Append("CalColor=@CalColor,"); strSql.Append("Skype=@Skype,"); strSql.Append("Line=@Line,"); strSql.Append("Wechat=@Wechat,"); strSql.Append("MemberPic=@MemberPic,"); strSql.Append("QQ=@QQ,"); strSql.Append("PrjTypeID=@PrjTypeID,"); strSql.Append("IsOutside=@IsOutside,"); strSql.Append("AskLeaveGuid=@AskLeaveGuid,"); strSql.Append("SysShowMode=@SysShowMode"); strSql.Append(" where MemberID=@MemberID "); SqlParameter[] parameters = { new SqlParameter("@Password", SqlDbType.VarChar,100), new SqlParameter("@MemberName", SqlDbType.NVarChar,100), new SqlParameter("@Email", SqlDbType.NVarChar,200), new SqlParameter("@GoogleAccount", SqlDbType.VarChar,200), new SqlParameter("@ContectTEL", SqlDbType.VarChar,50), new SqlParameter("@ContectExt", SqlDbType.VarChar,50), new SqlParameter("@ContectFax", SqlDbType.VarChar,50), new SqlParameter("@ContectCell", SqlDbType.VarChar,50), new SqlParameter("@Address", SqlDbType.NVarChar,500), new SqlParameter("@BirthDate", SqlDbType.DateTime), new SqlParameter("@ArriveDate", SqlDbType.DateTime), new SqlParameter("@EmergencyContect", SqlDbType.NVarChar,100), new SqlParameter("@EmergencyTEL", SqlDbType.VarChar,50), new SqlParameter("@EmergencyExt", SqlDbType.VarChar,20), new SqlParameter("@EmergencyFax", SqlDbType.VarChar,50), new SqlParameter("@EmergencyCell", SqlDbType.VarChar,50), new SqlParameter("@EmergencyEMail", SqlDbType.NVarChar,200), new SqlParameter("@JobTitle", SqlDbType.NVarChar,10), new SqlParameter("@JobClass", SqlDbType.VarChar,200), new SqlParameter("@ChiefID", SqlDbType.VarChar,50), new SqlParameter("@DepartmentID", SqlDbType.VarChar,10), new SqlParameter("@LeaveDate", SqlDbType.DateTime), new SqlParameter("@Effective", SqlDbType.Char,1), new SqlParameter("@Memo", SqlDbType.NVarChar), new SqlParameter("@CreateUser", SqlDbType.VarChar,50), new SqlParameter("@CreateDate", SqlDbType.DateTime), new SqlParameter("@ModifyUser", SqlDbType.VarChar,50), new SqlParameter("@ModifyDate", SqlDbType.DateTime), new SqlParameter("@CalColor", SqlDbType.NVarChar,20), new SqlParameter("@Skype", SqlDbType.VarChar,50), new SqlParameter("@Line", SqlDbType.VarChar,50), new SqlParameter("@Wechat", SqlDbType.VarChar,50), new SqlParameter("@MemberPic", SqlDbType.Char,36), new SqlParameter("@QQ", SqlDbType.VarChar,50), new SqlParameter("@PrjTypeID", SqlDbType.VarChar,500), new SqlParameter("@IsOutside", SqlDbType.Char,1), new SqlParameter("@AskLeaveGuid", SqlDbType.VarChar,50), new SqlParameter("@SysShowMode", SqlDbType.Char,1), new SqlParameter("@MemberID", SqlDbType.VarChar,50)}; parameters[0].Value = model.Password; parameters[1].Value = model.MemberName; parameters[2].Value = model.Email; parameters[3].Value = model.GoogleAccount; parameters[4].Value = model.ContectTEL; parameters[5].Value = model.ContectExt; parameters[6].Value = model.ContectFax; parameters[7].Value = model.ContectCell; parameters[8].Value = model.Address; parameters[9].Value = model.BirthDate; parameters[10].Value = model.ArriveDate; parameters[11].Value = model.EmergencyContect; parameters[12].Value = model.EmergencyTEL; parameters[13].Value = model.EmergencyExt; parameters[14].Value = model.EmergencyFax; parameters[15].Value = model.EmergencyCell; parameters[16].Value = model.EmergencyEMail; parameters[17].Value = model.JobTitle; parameters[18].Value = model.JobClass; parameters[19].Value = model.ChiefID; parameters[20].Value = model.DepartmentID; parameters[21].Value = model.LeaveDate; parameters[22].Value = model.Effective; parameters[23].Value = model.Memo; parameters[24].Value = model.CreateUser; parameters[25].Value = model.CreateDate; parameters[26].Value = model.ModifyUser; parameters[27].Value = model.ModifyDate; parameters[28].Value = model.CalColor; parameters[29].Value = model.Skype; parameters[30].Value = model.Line; parameters[31].Value = model.Wechat; parameters[32].Value = model.MemberPic; parameters[33].Value = model.QQ; parameters[34].Value = model.PrjTypeID; parameters[35].Value = model.IsOutside; parameters[36].Value = model.AskLeaveGuid; parameters[37].Value = model.SysShowMode; parameters[38].Value = model.MemberID;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } }
/// <summary>
/// 更新資料
/// </summary>
public bool UpdatePop(string MemberID, string MemberName, string Password, string CalColor, string ModifyUser, string SysShowMode) { int effectiverow = 0; StringBuilder strSql = new StringBuilder(); strSql.Append(" UPDATE [OTB_SYS_Members] SET "); strSql.Append(" MemberName =@MemberName, "); strSql.Append(" Password=@Password, "); strSql.Append(" ModifyUser =@ModifyUser, "); strSql.Append(" ModifyDate =GETDATE(), "); strSql.Append(" CalColor =@CalColor "); strSql.Append(" ,SysShowMode =@SysShowMode "); strSql.Append(" WHERE MemberID=@MemberID "); SqlParameter[] parameters = { new SqlParameter("@MemberID", SqlDbType.VarChar,50), new SqlParameter("@MemberName", SqlDbType.NVarChar,100), new SqlParameter("@Password", SqlDbType.VarChar,100), new SqlParameter("@ModifyUser", SqlDbType.VarChar,50), new SqlParameter("@CalColor", SqlDbType.NVarChar,20), new SqlParameter("@SysShowMode", SqlDbType.Char,1)};
parameters[0].Value = MemberID; parameters[1].Value = MemberName; parameters[2].Value = Password; parameters[3].Value = ModifyUser; parameters[4].Value = CalColor; parameters[5].Value = SysShowMode; effectiverow = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (effectiverow > 0) { return true; } else { return false; } }
/// <summary>
/// 刪除一條資料
/// </summary>
public int Delete(string MemberID) { StringBuilder strSql = new StringBuilder(); strSql.Append(" DELETE [OTB_SYS_Members] "); strSql.Append(" WHERE MemberID=@MemberID "); SqlParameter[] parameters = { new SqlParameter("@MemberID", SqlDbType.VarChar,10) }; parameters[0].Value = MemberID;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); return rows;
} /// <summary>
/// 批量刪除資料
/// </summary>
public bool DeleteList(string MemberIDlist) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from OTB_SYS_Members "); strSql.Append(" where MemberID in (" + MemberIDlist + ") "); int rows = DbHelperSQL.ExecuteSql(strSql.ToString()); if (rows > 0) { return true; } else { return false; } }
/// <summary>
/// 得到一個對象實體
/// </summary>
public OT.Model.OTB_SYS_Members GetModel(string MemberID) {
StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 MemberID,Password,MemberName,Email,GoogleAccount,ContectTEL,ContectExt,ContectFax,ContectCell,Address,BirthDate,ArriveDate,EmergencyContect,EmergencyTEL,EmergencyExt,EmergencyFax,EmergencyCell,EmergencyEMail,JobTitle,JobClass,ChiefID,DepartmentID,LeaveDate,Effective,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate,CalColor,Skype,Line,Wechat,MemberPic,QQ,PrjTypeID,IsOutside,AskLeaveGuid,SysShowMode from OTB_SYS_Members "); strSql.Append(" where MemberID=@MemberID "); SqlParameter[] parameters = { new SqlParameter("@MemberID", SqlDbType.VarChar,50) }; parameters[0].Value = MemberID;
OT.Model.OTB_SYS_Members model = new OT.Model.OTB_SYS_Members(); DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["MemberID"] != null && ds.Tables[0].Rows[0]["MemberID"].ToString() != "") { model.MemberID = ds.Tables[0].Rows[0]["MemberID"].ToString(); } if (ds.Tables[0].Rows[0]["Password"] != null && ds.Tables[0].Rows[0]["Password"].ToString() != "") { model.Password = ds.Tables[0].Rows[0]["Password"].ToString(); } if (ds.Tables[0].Rows[0]["MemberName"] != null && ds.Tables[0].Rows[0]["MemberName"].ToString() != "") { model.MemberName = ds.Tables[0].Rows[0]["MemberName"].ToString(); } if (ds.Tables[0].Rows[0]["Email"] != null && ds.Tables[0].Rows[0]["Email"].ToString() != "") { model.Email = ds.Tables[0].Rows[0]["Email"].ToString(); } if (ds.Tables[0].Rows[0]["GoogleAccount"] != null && ds.Tables[0].Rows[0]["GoogleAccount"].ToString() != "") { model.GoogleAccount = ds.Tables[0].Rows[0]["GoogleAccount"].ToString(); } if (ds.Tables[0].Rows[0]["ContectTEL"] != null && ds.Tables[0].Rows[0]["ContectTEL"].ToString() != "") { model.ContectTEL = ds.Tables[0].Rows[0]["ContectTEL"].ToString(); } if (ds.Tables[0].Rows[0]["ContectExt"] != null && ds.Tables[0].Rows[0]["ContectExt"].ToString() != "") { model.ContectExt = ds.Tables[0].Rows[0]["ContectExt"].ToString(); } if (ds.Tables[0].Rows[0]["ContectFax"] != null && ds.Tables[0].Rows[0]["ContectFax"].ToString() != "") { model.ContectFax = ds.Tables[0].Rows[0]["ContectFax"].ToString(); } if (ds.Tables[0].Rows[0]["ContectCell"] != null && ds.Tables[0].Rows[0]["ContectCell"].ToString() != "") { model.ContectCell = ds.Tables[0].Rows[0]["ContectCell"].ToString(); } if (ds.Tables[0].Rows[0]["Address"] != null && ds.Tables[0].Rows[0]["Address"].ToString() != "") { model.Address = ds.Tables[0].Rows[0]["Address"].ToString(); } if (ds.Tables[0].Rows[0]["BirthDate"] != null && ds.Tables[0].Rows[0]["BirthDate"].ToString() != "") { model.BirthDate = DateTime.Parse(ds.Tables[0].Rows[0]["BirthDate"].ToString()); } if (ds.Tables[0].Rows[0]["ArriveDate"] != null && ds.Tables[0].Rows[0]["ArriveDate"].ToString() != "") { model.ArriveDate = DateTime.Parse(ds.Tables[0].Rows[0]["ArriveDate"].ToString()); } if (ds.Tables[0].Rows[0]["EmergencyContect"] != null && ds.Tables[0].Rows[0]["EmergencyContect"].ToString() != "") { model.EmergencyContect = ds.Tables[0].Rows[0]["EmergencyContect"].ToString(); } if (ds.Tables[0].Rows[0]["EmergencyTEL"] != null && ds.Tables[0].Rows[0]["EmergencyTEL"].ToString() != "") { model.EmergencyTEL = ds.Tables[0].Rows[0]["EmergencyTEL"].ToString(); } if (ds.Tables[0].Rows[0]["EmergencyExt"] != null && ds.Tables[0].Rows[0]["EmergencyExt"].ToString() != "") { model.EmergencyExt = ds.Tables[0].Rows[0]["EmergencyExt"].ToString(); } if (ds.Tables[0].Rows[0]["EmergencyFax"] != null && ds.Tables[0].Rows[0]["EmergencyFax"].ToString() != "") { model.EmergencyFax = ds.Tables[0].Rows[0]["EmergencyFax"].ToString(); } if (ds.Tables[0].Rows[0]["EmergencyCell"] != null && ds.Tables[0].Rows[0]["EmergencyCell"].ToString() != "") { model.EmergencyCell = ds.Tables[0].Rows[0]["EmergencyCell"].ToString(); } if (ds.Tables[0].Rows[0]["EmergencyEMail"] != null && ds.Tables[0].Rows[0]["EmergencyEMail"].ToString() != "") { model.EmergencyEMail = ds.Tables[0].Rows[0]["EmergencyEMail"].ToString(); } if (ds.Tables[0].Rows[0]["JobTitle"] != null && ds.Tables[0].Rows[0]["JobTitle"].ToString() != "") { model.JobTitle = ds.Tables[0].Rows[0]["JobTitle"].ToString(); } if (ds.Tables[0].Rows[0]["JobClass"] != null && ds.Tables[0].Rows[0]["JobClass"].ToString() != "") { model.JobClass = ds.Tables[0].Rows[0]["JobClass"].ToString(); } if (ds.Tables[0].Rows[0]["ChiefID"] != null && ds.Tables[0].Rows[0]["ChiefID"].ToString() != "") { model.ChiefID = ds.Tables[0].Rows[0]["ChiefID"].ToString(); } if (ds.Tables[0].Rows[0]["DepartmentID"] != null && ds.Tables[0].Rows[0]["DepartmentID"].ToString() != "") { model.DepartmentID = ds.Tables[0].Rows[0]["DepartmentID"].ToString(); } if (ds.Tables[0].Rows[0]["LeaveDate"] != null && ds.Tables[0].Rows[0]["LeaveDate"].ToString() != "") { model.LeaveDate = DateTime.Parse(ds.Tables[0].Rows[0]["LeaveDate"].ToString()); } if (ds.Tables[0].Rows[0]["Effective"] != null && ds.Tables[0].Rows[0]["Effective"].ToString() != "") { model.Effective = ds.Tables[0].Rows[0]["Effective"].ToString(); } if (ds.Tables[0].Rows[0]["Memo"] != null && ds.Tables[0].Rows[0]["Memo"].ToString() != "") { model.Memo = ds.Tables[0].Rows[0]["Memo"].ToString(); } if (ds.Tables[0].Rows[0]["CreateUser"] != null && ds.Tables[0].Rows[0]["CreateUser"].ToString() != "") { model.CreateUser = ds.Tables[0].Rows[0]["CreateUser"].ToString(); } if (ds.Tables[0].Rows[0]["CreateDate"] != null && ds.Tables[0].Rows[0]["CreateDate"].ToString() != "") { model.CreateDate = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString()); } if (ds.Tables[0].Rows[0]["ModifyUser"] != null && ds.Tables[0].Rows[0]["ModifyUser"].ToString() != "") { model.ModifyUser = ds.Tables[0].Rows[0]["ModifyUser"].ToString(); } if (ds.Tables[0].Rows[0]["ModifyDate"] != null && ds.Tables[0].Rows[0]["ModifyDate"].ToString() != "") { model.ModifyDate = DateTime.Parse(ds.Tables[0].Rows[0]["ModifyDate"].ToString()); } if (ds.Tables[0].Rows[0]["CalColor"] != null && ds.Tables[0].Rows[0]["CalColor"].ToString() != "") { model.CalColor = ds.Tables[0].Rows[0]["CalColor"].ToString(); } if (ds.Tables[0].Rows[0]["Skype"] != null && ds.Tables[0].Rows[0]["Skype"].ToString() != "") { model.Skype = ds.Tables[0].Rows[0]["Skype"].ToString(); } if (ds.Tables[0].Rows[0]["Line"] != null && ds.Tables[0].Rows[0]["Line"].ToString() != "") { model.Line = ds.Tables[0].Rows[0]["Line"].ToString(); } if (ds.Tables[0].Rows[0]["Wechat"] != null && ds.Tables[0].Rows[0]["Wechat"].ToString() != "") { model.Wechat = ds.Tables[0].Rows[0]["Wechat"].ToString(); } if (ds.Tables[0].Rows[0]["MemberPic"] != null && ds.Tables[0].Rows[0]["MemberPic"].ToString() != "") { model.MemberPic = ds.Tables[0].Rows[0]["MemberPic"].ToString(); } if (ds.Tables[0].Rows[0]["QQ"] != null && ds.Tables[0].Rows[0]["QQ"].ToString() != "") { model.QQ = ds.Tables[0].Rows[0]["QQ"].ToString(); } if (ds.Tables[0].Rows[0]["PrjTypeID"] != null && ds.Tables[0].Rows[0]["PrjTypeID"].ToString() != "") { model.PrjTypeID = ds.Tables[0].Rows[0]["PrjTypeID"].ToString(); } if (ds.Tables[0].Rows[0]["IsOutside"] != null && ds.Tables[0].Rows[0]["IsOutside"].ToString() != "") { model.IsOutside = ds.Tables[0].Rows[0]["IsOutside"].ToString(); } if (ds.Tables[0].Rows[0]["AskLeaveGuid"] != null && ds.Tables[0].Rows[0]["AskLeaveGuid"].ToString() != "") { model.AskLeaveGuid = ds.Tables[0].Rows[0]["AskLeaveGuid"].ToString(); } if (ds.Tables[0].Rows[0]["SysShowMode"] != null && ds.Tables[0].Rows[0]["SysShowMode"].ToString() != "") { model.SysShowMode = ds.Tables[0].Rows[0]["SysShowMode"].ToString(); } return model; } else { return null; } }
/// <summary>
/// 獲得資料列表
/// </summary>
public DataSet GetList(int StartRecordIndex, int EndRecordIndex, string strMemberID, string strMemberName, string Effective, string SortExpression) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" RowId,MemberID,MemberName,Ext,ContectExt,DepartmentName,JobtitleID,Effective "); strSql.Append(" FROM "); strSql.Append(" ( "); strSql.Append(" SELECT "); strSql.Append(" CASE @SortExpression WHEN N'MemberID' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY MemberID)) "); strSql.Append(" WHEN N'MemberID DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY MemberID DESC)) "); strSql.Append(" WHEN N'MemberName' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY MemberName)) "); strSql.Append(" WHEN N'MemberName DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY MemberName DESC)) "); strSql.Append(" WHEN N'ContectExt' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY ContectExt)) "); strSql.Append(" WHEN N'ContectExt DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY ContectExt DESC)) "); strSql.Append(" WHEN N'b.DepartmentName' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY b.DepartmentName)) "); strSql.Append(" WHEN N'b.DepartmentName DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY b.DepartmentName DESC)) "); strSql.Append(" WHEN N'JobtitleID' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY JobtitleID)) "); strSql.Append(" WHEN N'JobtitleID DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY JobtitleID DESC)) "); strSql.Append(" WHEN N'a.Effective' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY a.Effective)) "); strSql.Append(" WHEN N'a.Effective DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY a.Effective DESC)) "); strSql.Append(" else "); strSql.Append(" (ROW_NUMBER() OVER(Order BY MemberID)) "); strSql.Append(" END AS RowId,MemberID,MemberName,Ext,a.ContectExt,b.DepartmentName,JobtitleID,a.Effective "); strSql.Append(" FROM OTB_SYS_Members as a "); strSql.Append(" Left join OTB_SYS_Department as b on a.DeptID = b.DepartmentID "); strSql.Append(" WHERE (MemberID like @MemberID OR @MemberID IS NULL OR @MemberID='') "); strSql.Append(" AND (MemberName LIKE @MemberName OR @MemberName IS NULL OR @MemberName='') "); strSql.Append(" AND (a.Effective = @Effective OR @Effective IS NULL OR @Effective='') "); strSql.Append(" ) AS AA "); strSql.Append(" WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex "); strSql.Append(" ORDER BY RowId "); SqlParameter[] parameters = { new SqlParameter("@StartRecordIndex", SqlDbType.Int) ,new SqlParameter("@EndRecordIndex", SqlDbType.Int) ,new SqlParameter("@MemberID", SqlDbType.VarChar,50) ,new SqlParameter("@MemberName", SqlDbType.VarChar,50) ,new SqlParameter("@Effective", SqlDbType.Char,1) ,new SqlParameter("@SortExpression", SqlDbType.NVarChar,500) }; parameters[0].Value = StartRecordIndex; parameters[1].Value = EndRecordIndex; parameters[2].Value = "%" + strMemberID + "%"; parameters[3].Value = "%" + strMemberName + "%"; parameters[4].Value = Effective; parameters[5].Value = SortExpression; return DbHelperSQL.Query(strSql.ToString(), parameters);
}
/// <summary>
/// 獲得資料總筆數
/// </summary>
public int GetListCount(string strMemberID, string strMemberName, string Effective) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT COUNT(0) FROM OTB_SYS_Members mem"); strSql.Append(" WHERE ( MemberID like @MemberID OR @MemberID IS NULL OR @MemberID='')"); strSql.Append(" AND (MemberName LIKE @MemberName OR @MemberName IS NULL OR @MemberName='')"); strSql.Append(" AND (Effective = @Effective OR @Effective IS NULL OR @Effective='')"); SqlParameter[] parameters = { new SqlParameter("@MemberID", SqlDbType.VarChar,50) ,new SqlParameter("@MemberName", SqlDbType.VarChar,50) ,new SqlParameter("@Effective", SqlDbType.Char,1) }; parameters[0].Value = "%" + strMemberID + "%"; parameters[1].Value = "%" + strMemberName + "%"; parameters[2].Value = Effective; return (int)DbHelperSQL.GetSingle(strSql.ToString(), parameters); }
/// <summary>
/// 獲得資料列表
/// </summary>
public DataSet GetList(int StartRecordIndex, int EndRecordIndex, string MemberID, string MemberName, string DepartmentID, string JobTitle, string Effective, string SortExpression) { StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT RowId , "); strSql.Append(" MemberID , "); strSql.Append(" MemberName , "); strSql.Append(" ContectExt , "); strSql.Append(" DeptID , "); strSql.Append(" JobTitleID , "); strSql.Append(" ArriveDate , "); strSql.Append(" LeaveDate , "); strSql.Append(" Effective "); strSql.Append(" FROM ( SELECT CASE @SortExpression "); strSql.Append(" WHEN N'MemberID' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY MemberID ) ) "); strSql.Append(" WHEN N'MemberID DESC' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY MemberID DESC ) ) "); strSql.Append(" WHEN N'MemberName' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY MemberName ) ) "); strSql.Append(" WHEN N'MemberName DESC' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY MemberName DESC ) ) "); strSql.Append(" WHEN N'ContectExt' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY Members.ContectExt ) ) "); strSql.Append(" WHEN N'ContectExt DESC' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY Members.ContectExt DESC ) ) "); strSql.Append(" WHEN N'DeptID' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY DepartmentName ) ) "); strSql.Append(" WHEN N'DeptID DESC' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY DepartmentName DESC ) ) "); strSql.Append(" WHEN N'JobTitleID' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY JobtitleName ) ) "); strSql.Append(" WHEN N'JobTitleID DESC' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY JobtitleName DESC ) ) "); strSql.Append(" WHEN N'ArriveDate' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY ArriveDate ) ) "); strSql.Append(" WHEN N'ArriveDate DESC' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY ArriveDate DESC ) ) "); strSql.Append(" WHEN N'LeaveDate' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY LeaveDate ) ) "); strSql.Append(" WHEN N'LeaveDate DESC' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY LeaveDate DESC ) ) "); strSql.Append(" WHEN N'Effective' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY Members.Effective ) ) "); strSql.Append(" WHEN N'Effective DESC' "); strSql.Append(" THEN ( ROW_NUMBER() OVER ( ORDER BY Members.Effective DESC ) ) "); strSql.Append(" ELSE ( ROW_NUMBER() OVER ( ORDER BY MemberID ) ) "); strSql.Append(" END AS RowId , "); strSql.Append(" MemberID , "); strSql.Append(" MemberName , "); strSql.Append(" Members.ContectExt , "); strSql.Append(" DepartmentName AS DeptID , "); strSql.Append(" JobtitleName AS JobTitleID , "); strSql.Append(" CONVERT(VARCHAR(100), ArriveDate, 111) AS ArriveDate , "); strSql.Append(" CONVERT(VARCHAR(100), LeaveDate, 111) AS LeaveDate , "); strSql.Append(" Members.Effective "); strSql.Append(" FROM OTB_SYS_Members AS Members "); strSql.Append(" LEFT JOIN dbo.OTB_SYS_Departments AS Department ON members.DepartmentID = Department.DepartmentID "); strSql.Append(" LEFT JOIN dbo.OTB_SYS_Jobtitle AS Jobtitle ON members.JobTitle = Jobtitle.JobTitleID "); strSql.Append(" WHERE ( MemberID LIKE @MemberID "); strSql.Append(" OR @MemberID IS NULL "); strSql.Append(" OR @MemberID = '%%' "); strSql.Append(" ) "); strSql.Append(" AND ( MemberName LIKE @MemberName "); strSql.Append(" OR @MemberName IS NULL "); strSql.Append(" OR @MemberName = '%%' "); strSql.Append(" ) "); strSql.Append(" AND ( members.DepartmentID = @DepartmentID "); strSql.Append(" OR @DepartmentID IS NULL "); strSql.Append(" OR @DepartmentID = '' "); strSql.Append(" ) "); strSql.Append(" AND ( members.JobTitle = @JobTitle "); strSql.Append(" OR @JobTitle IS NULL "); strSql.Append(" OR @JobTitle = '' "); strSql.Append(" ) "); strSql.Append(" "); strSql.Append(" AND ( Members.Effective = @Effective "); strSql.Append(" OR @Effective IS NULL "); strSql.Append(" OR @Effective = '' "); strSql.Append(" ) "); strSql.Append(" ) AS AA "); strSql.Append(" WHERE AA.RowId >= @StartRecordIndex "); strSql.Append(" AND AA.RowId <= @EndRecordIndex "); strSql.Append(" ORDER BY RowId ");
SqlParameter[] parameters = { new SqlParameter("@StartRecordIndex", SqlDbType.Int) ,new SqlParameter("@EndRecordIndex", SqlDbType.Int) ,new SqlParameter("@MemberID", SqlDbType.VarChar,10) ,new SqlParameter("@MemberName", SqlDbType.VarChar,50) ,new SqlParameter("@DepartmentID", SqlDbType.VarChar,10) ,new SqlParameter("@JobTitle", SqlDbType.VarChar,10) ,new SqlParameter("@Effective", SqlDbType.Char,1) ,new SqlParameter("@SortExpression", SqlDbType.NVarChar,500) }; parameters[0].Value = StartRecordIndex; parameters[1].Value = EndRecordIndex; parameters[2].Value = "%" + MemberID + "%"; parameters[3].Value = "%" + MemberName + "%"; parameters[4].Value = DepartmentID; parameters[5].Value = JobTitle; parameters[6].Value = Effective; parameters[7].Value = SortExpression; return DbHelperSQL.Query(strSql.ToString(), parameters); }
/// <summary>
/// 獲得資料總筆數
/// </summary>
public int GetListCount(string MemberID, string MemberName, string DepartmentID, string JobTitle, string Effective) { StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT COUNT(0) "); strSql.Append(" FROM OTB_SYS_Members AS Members "); strSql.Append(" LEFT JOIN dbo.OTB_SYS_Departments AS Department ON members.DepartmentID = Department.DepartmentID "); strSql.Append(" LEFT JOIN dbo.OTB_SYS_Jobtitle AS Jobtitle ON members.JobTitle = Jobtitle.JobtitleID "); strSql.Append(" WHERE ( MemberID LIKE @MemberID "); strSql.Append(" OR @MemberID IS NULL "); strSql.Append(" OR @MemberID = '%%' "); strSql.Append(" ) "); strSql.Append(" AND ( Members.MemberName LIKE @MemberName "); strSql.Append(" OR @MemberName IS NULL "); strSql.Append(" OR @MemberName = '%%' "); strSql.Append(" ) "); strSql.Append(" AND ( Members.DepartmentID = @DeptID "); strSql.Append(" OR @DeptID IS NULL "); strSql.Append(" OR @DeptID = '' "); strSql.Append(" ) "); strSql.Append(" AND ( Members.JobTitle = @JobtitleID "); strSql.Append(" OR @JobtitleID IS NULL "); strSql.Append(" OR @JobtitleID = '' "); strSql.Append(" ) "); strSql.Append(" "); strSql.Append(" AND ( Members.Effective = @Effective "); strSql.Append(" OR @Effective IS NULL "); strSql.Append(" OR @Effective = '' "); strSql.Append(" ) "); SqlParameter[] parameters = { new SqlParameter("@MemberID", SqlDbType.VarChar,10) ,new SqlParameter("@MemberName", SqlDbType.VarChar,50) ,new SqlParameter("@DeptID", SqlDbType.VarChar,10) ,new SqlParameter("@JobTitleID", SqlDbType.VarChar,10) ,new SqlParameter("@Effective", SqlDbType.Char,1) }; parameters[0].Value = "%" + MemberID + "%"; parameters[1].Value = "%" + MemberName + "%"; parameters[2].Value = DepartmentID; parameters[3].Value = JobTitle; parameters[4].Value = Effective; return (int)DbHelperSQL.GetSingle(strSql.ToString(), parameters); } /// <summary>
/// 獲得資料列表
/// </summary>
public DataSet SearchMember(int StartRecordIndex, int EndRecordIndex, string strSearch, string SortExpression) { StringBuilder strSql = new StringBuilder(); string strSort = " (ROW_NUMBER() OVER(Order BY MemberID)) "; if (!string.IsNullOrEmpty(SortExpression)) { switch (SortExpression.ToLower()) { case "memberid": strSort = " (ROW_NUMBER() OVER(Order BY MemberID)) "; break; case "memberid desc": strSort = " (ROW_NUMBER() OVER(Order BY MemberID DESC))"; break; case "membername": strSort = " (ROW_NUMBER() OVER(Order BY MemberName))"; break; case "membername desc": strSort = " (ROW_NUMBER() OVER(Order BY MemberName DESC))"; break; case "rulelist": strSort = " (ROW_NUMBER() OVER(Order BY RuleList))"; break; case "rulelist desc": strSort = " (ROW_NUMBER() OVER(Order BY RuleList DESC))"; break; case "rulenmList": strSort = " (ROW_NUMBER() OVER(Order BY RuleNmList))"; break; case "rulenmList desc": strSort = " (ROW_NUMBER() OVER(Order BY RuleNmList DESC))"; break; default: strSort = " (ROW_NUMBER() OVER(Order BY MemberID)) "; break; } } strSql.Append(" DeClare @MainTB Table("); strSql.Append(" RowId int,"); strSql.Append(" MemberID VarChar(10),"); strSql.Append(" MemberName NVarChar(10),"); strSql.Append(" RuleList VarChar(5),"); strSql.Append(" RuleNmList NVarChar(50)"); strSql.Append(" )");
strSql.Append(" Insert @MainTB"); strSql.Append(" SELECT "); strSql.Append(" " + strSort + " "); strSql.Append(" ,MemberID,MemberName,RuleList,RuleNmList"); strSql.Append(" FROM "); strSql.Append(" ("); strSql.Append(" SELECT Emp_Cd As MemberID"); strSql.Append(" ,Emp_Cnm As MemberName"); strSql.Append(" ,Dept_Cd As RuleList"); strSql.Append(" FROM Trdata.Dbo.TrEmp "); strSql.Append(" ) As Emp"); strSql.Append(" Inner Join "); strSql.Append(" ("); strSql.Append(" SELECT Dept_Cd,Dept_Nm As RuleNmList FROM TrData.Dbo.TrDept"); strSql.Append(" ) As Dept On Dept_Cd=RuleList"); if (!string.IsNullOrEmpty(strSearch)) { strSql.Append(" WHERE IsNull(MemberID,'')+IsNull(MemberName,'')+IsNull(RuleList,'')+IsNull(RuleNmList,'') Like @Search"); } strSql.Append(" "); strSql.Append(" SELECT RowId"); strSql.Append(" ,MemberID"); strSql.Append(" ,MemberName "); strSql.Append(" ,RuleList"); strSql.Append(" ,RuleNmList"); strSql.Append(" FROM @MainTB"); strSql.Append(" WHERE RowId>=@StartRecordIndex AND RowId<=@EndRecordIndex"); strSql.Append(" ORDER BY RowId ");
strSql.Append(" SELECT Count(0) FROM @MainTB"); SqlParameter[] parameters = { new SqlParameter("@StartRecordIndex", SqlDbType.Int) ,new SqlParameter("@EndRecordIndex", SqlDbType.Int) ,new SqlParameter("@Search", SqlDbType.NVarChar,50) ,new SqlParameter("@SortExpression", SqlDbType.NVarChar,500) }; parameters[0].Value = StartRecordIndex; parameters[1].Value = EndRecordIndex; parameters[2].Value = "%" + strSearch + "%"; parameters[3].Value = SortExpression; return DbHelperSQL.Query(strSql.ToString(), parameters); }
/// <summary>
/// 獲得資料總筆數
/// </summary>
public int SearchMemberCount(string MemberID, string MemberName, string RuleID, string RuleName) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT Count(0)"); strSql.Append(" FROM OTB_SYS_Members As Main"); strSql.Append(" Left Join ("); strSql.Append(" SELECT Distinct MemberID"); strSql.Append(" ,Stuff((SELECT ',' +RuleID FROM OTB_SYS_MembersToRule WHERE MemberID=T1.MemberID For Xml Path('')),1,1,'') As RuleList"); strSql.Append(" ,Stuff((SELECT ',' +RuleName FROM "); strSql.Append(" (SELECT MemberID,ID.RuleID,RuleName"); strSql.Append(" FROM OTB_SYS_MembersToRule As ID"); strSql.Append(" Left Join (SELECT RuleID,RuleName FROM OTB_SYS_Rules) As Name "); strSql.Append(" On ID.RuleID=Name.RuleID) As T2 "); strSql.Append(" WHERE MemberID=T1.MemberID For Xml Path('')),1,1,'') As RuleNmList"); strSql.Append(" FROM OTB_SYS_MembersToRule As T1"); strSql.Append(" )As Rules On Main.MemberID=Rules.MemberID"); strSql.Append(" WHERE (Main.MemberID Like @MemberID)"); strSql.Append(" And (MemberName Like @MemberName)"); strSql.Append(" And (Rules.RuleList Like @RuleID Or @RuleID='%%')"); strSql.Append(" And (Rules.RuleNmList Like @RuleName Or @RuleName='%%')"); SqlParameter[] parameters = { new SqlParameter("@MemberID", SqlDbType.VarChar,10) ,new SqlParameter("@MemberName", SqlDbType.NVarChar,50) ,new SqlParameter("@RuleID", SqlDbType.VarChar,10) ,new SqlParameter("@RuleName", SqlDbType.NVarChar,50) }; parameters[0].Value = "%" + MemberID + "%"; parameters[1].Value = "%" + MemberName + "%"; parameters[2].Value = "%" + RuleID + "%"; parameters[3].Value = "%" + RuleName + "%"; return (int)DbHelperSQL.GetSingle(strSql.ToString(), parameters); }
#endregion Method
}}
|
