using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using OT.IDAL;
using DBUtility;//Please add references
namespace OT.SQLServerDAL
{
///
/// 資料訪問類:OTB_SYS_Members
///
public partial class OTB_SYS_Members : IOTB_SYS_Members
{
public OTB_SYS_Members()
{ }
#region Method
///
/// 是否存在該記錄
///
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);
}
///
/// 是否存在該記錄
///
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);
}
///
/// 增加一條資料
///
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;
}
}
///
/// 更新一條資料
///
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;
}
}
///
/// 更新資料
///
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;
}
}
///
/// 刪除一條資料
///
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;
}
///
/// 批量刪除資料
///
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;
}
}
///
/// 得到一個對象實體
///
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;
}
}
///
/// 獲得資料列表
///
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);
}
///
/// 獲得資料總筆數
///
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);
}
///
/// 獲得資料列表
///
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);
}
///
/// 獲得資料總筆數
///
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);
}
///
/// 獲得資料列表
///
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);
}
///
/// 獲得資料總筆數
///
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
}
}