/// <summary>
/// 设置用户角色
/// </summary>
/// <remarks>
/// author: zp
/// createtime: 2010.07.13
/// </remarks>
/// <param name="userid">用户编号</param>
/// <param name="roleid">角色编号</param>
/// <returns></returns>
public bool SetUserRole(String userid, int roleid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"IF EXISTS(SELECT * FROM M_System_User_Role_Rel WHERE User_ID=@userid)
UPDATE M_System_User_Role_Rel SET User_RoleID = @roleid WHERE USER_ID =@userid
ELSE
INSERT INTO M_System_User_Role_Rel(User_ID,User_RoleID,User_ShowName) VALUES(@userid,@roleid,'')");
SqlParameter[] prms =
{
new SqlParameter("@userid", userid),
new SqlParameter("@roleid", roleid)
};
try
{
int i = SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), prms);
if (i == 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
}
/// <summary>
/// 更新浏览次数
/// </summary>
/// <param name="eventItemGuid"></param>
/// <returns></returns>
public bool UpdateBrowserCnt(Guid eventItemGuid)
{
bool isSuccess = false;
StringBuilder sbSql = new StringBuilder();
sbSql.Append("IF EXISTS (SELECT * FROM M_EventItemBrowseCnts WHERE EventItemGUID=@EventItemGUID)");
sbSql.Append(" UPDATE M_EventItemBrowseCnts SET BrowseCnt=BrowseCnt+1 WHERE EventItemGUID=@EventItemGUID");
sbSql.Append(" ELSE");
sbSql.Append(" INSERT INTO M_EventItemBrowseCnts(EventItemGUID,BrowseCnt) VALUES (@EventItemGUID,@BrowseCnt)");
SqlParameter[] prms =
{
new SqlParameter("@EventItemGUID", SqlDbType.UniqueIdentifier, 16),
new SqlParameter("@BrowseCnt", SqlDbType.Int)
};
prms[0].Value = eventItemGuid;
prms[1].Value = 1;
try
{
isSuccess = SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, sbSql.ToString(), prms) > 0;
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
return(isSuccess);
}
/// <summary>
/// 根据用户编号修改角色
/// </summary>
/// <remarks>
/// author: zp
/// createtime: 2010.07.13
/// </remarks>
/// <param name="userid">用户编号</param>
/// <param name="roleid">修改后的角色编号</param>
/// <returns></returns>
public bool UpdateUserRole(String userid, int roleid)
{
SqlParameter[] prms =
{
new SqlParameter("@userid", userid),
new SqlParameter("@roleid", roleid)
};
try
{
int i = SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, "update M_System_User_Role_Rel set User_RoleID=@roleid where User_ID=@userid", prms);
if (i != 1)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
}
/// <summary>
/// 更新发送状态
/// </summary>
/// <param name="mailID"></param>
/// <param name="enumMailStatus"></param>
/// <returns></returns>
public bool UpdateSendStatus(int mailID, string remark, UserMailStatus enumMailStatus)
{
bool isSuccess = false;
string updateSQL = "Update M_User_Mail Set MailStatus=@MailStatus, SendDate=@SendDate,Remark=@Remark Where MailID=@MailID";
SqlParameter[] prms =
{
new SqlParameter("@MailStatus", (int)enumMailStatus),
new SqlParameter("@SendDate", DateTime.Now),
new SqlParameter("@MailID", mailID),
new SqlParameter("@Remark", remark)
};
try
{
isSuccess = SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, updateSQL, prms) > 0;
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
return(isSuccess);
}
public bool Update(UserMailEntity entity)
{
bool isSuccess = false;
string insertSQL = "UPDATE M_User_Mail SET UserID=@UserID,EventItemID=@EventItemID,CreateDate=@CreateDate,MailStatus=@MailStatus,Remark=@Remark,SendDate=@SendDate WHERE MailID=@MailID";
SqlParameter[] prms = new SqlParameter[] {
new SqlParameter("@UserID", SqlDbType.Int, 4),
new SqlParameter("@EventItemID", SqlDbType.Int, 4),
new SqlParameter("@CreateDate", SqlDbType.DateTime),
new SqlParameter("@MailStatus", SqlDbType.Int, 4),
new SqlParameter("@Remark", SqlDbType.NVarChar, 500),
new SqlParameter("@SendDate", SqlDbType.DateTime),
new SqlParameter("@MailID", SqlDbType.Int, 4)
};
prms[0].Value = entity.UserID;
prms[1].Value = entity.EventItemID;
prms[2].Value = entity.CreateDate;
prms[3].Value = entity.MailStatus;
prms[4].Value = entity.Remark;
prms[5].Value = entity.SendDate;
prms[6].Value = entity.MailID;
try
{
isSuccess = SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, insertSQL, prms) > 0;
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
return(isSuccess);
}
public bool UpdateNewWordState(Dictionary <int, string> wordList)
{
bool returnValue = false;
if (wordList != null)
{
string ids = "";
foreach (int _id in wordList.Keys)
{
ids += _id + ",";
}
ids = ids.TrimEnd(',');
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"UPDATE dbo.M_System_SensitiveWords SET IsNeedRecheck=0 WHERE SWID IN ({0})"
, ids);
try
{
if (SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), null) > 0)
{
returnValue = true;
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
}
}
return(returnValue);
}
public bool UpdateUser(UserEntity info)
{
SqlParameter[] prms =
{
new SqlParameter("@EMail", info.EMail),
new SqlParameter("@Name", info.Name),
new SqlParameter("@Company", info.Company),
new SqlParameter("@Position", info.Position),
new SqlParameter("@Department", info.Department),
new SqlParameter("@Address", info.Address),
new SqlParameter("@UserID", info.UserID),
new SqlParameter("@NickName", info.NickName)
};
string sql = "update M_User set NickName=@NickName, EMail = @EMail,Name = @Name,Company = @Company,Position = @Position,Department = @Department,Address = @Address where UserID = @UserID";
try
{
SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, sql, prms);
return(true);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
}
/// <summary>
/// 添加回复
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public OperationResult <AddReplyCommonEntity> ArticleReply_Insert(EventItemReplyEntity entity)
{
try
{
AddReplyCommonEntity acEntity = new AddReplyCommonEntity();
ReplyCommonEntity rcEntity = new ReplyCommonEntity();
using (SqlConnection con = new SqlConnection(SQlHelper.MyConnectStr))
{
SqlTransaction trans = null;
con.Open();
trans = con.BeginTransaction();
DateTime? nowDate = entity.ReplyTime == null ? DateTime.Now : entity.ReplyTime;
SqlParameter[] prms =
{
new SqlParameter("@ReplyID", entity.ReplyID),
new SqlParameter("@CommentID", entity.CommentID),
new SqlParameter("@UserID", entity.UserID),
new SqlParameter("@IsAnonymous", entity.IsAnonymous),
new SqlParameter("@ReplyParentID", entity.ReplyParentID),
new SqlParameter("@ReplyContent", entity.ReplyContent),
new SqlParameter("@ReplyTime", nowDate),
new SqlParameter("@CheckTypeID", entity.CheckTypeID),
new SqlParameter("@ViewStateID", entity.ViewStateID)
};
SqlParameter[] commPrms =
{
new SqlParameter("@CommentID", entity.CommentID),
new SqlParameter("@UserID", entity.UserID),
};
prms[0].Direction = ParameterDirection.Output;
int state = SQlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "M_EventItemReply_Insert", prms);
trans.Commit();
DataTable table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "M_ReplyCommon_Get", commPrms).Tables[0];
if (table != null && table.Rows.Count != 0)
{
rcEntity = ConvertDataTable <ReplyCommonEntity> .ConvertToList(table)[0];
}
acEntity.ReplyID = prms[0].Value.ToInt();
acEntity.ReplyTime = nowDate;
acEntity.ReviewLikeCount = rcEntity.ReviewLikeCount;
acEntity.IsReviewLike = rcEntity.IsReviewLike;
acEntity.ReplyCount = rcEntity.ReplyCount;
if (state > 0)
{
return(new OperationResult <AddReplyCommonEntity>(OperationResultType.Success, "回复完成！", acEntity));
}
else
{
return(new OperationResult <AddReplyCommonEntity>(OperationResultType.NoChanged, "评论已被删除,无法回复！", acEntity));
}
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <AddReplyCommonEntity>(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
public int WriteNumber(int recommendId, string phoneNum)
{
int returnValue = 0;
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"INSERT INTO dbo.M_User_Recommend_Mobile
( RecommendID, Mobile )
VALUES ( {0},'{1}');
SELECT @@IDENTITY;"
, recommendId.ToString()
, phoneNum);
try
{
object obj = SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), null);
if (obj != null)
{
returnValue = int.Parse(obj.ToString());
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
}
return(returnValue);
}
/// <summary>
/// 添加分组关联项
/// </summary>
/// <param name="groupEntity"></param>
/// <param name="groupRelEntityList"></param>
/// <returns></returns>
public bool InsertGroupRel(EventItemGroupEntity groupEntity, List <EventItemGroupRelEntity> groupRelEntityList)
{
bool isSuccess = false;
SqlTransaction trans = null;
try
{
StringBuilder sbInsert = new StringBuilder();
string insertGroupSQL = "INSERT INTO M_EventItemGroups(GroupEventName,PublishTime,CreatedTime,GroupState) VALUES (@GroupEventName,@PublishTime,@CreatedTime,@GroupState);SELECT @@IDENTITY;";
string insertRelSQL = "INSERT INTO M_EventItem_Group_Rel(EventGroupID,EventItemID,DisplayOrder) VALUES({0},{1},{2});";
SqlParameter[] prms =
{
new SqlParameter("@GroupEventName", SqlDbType.VarChar, 100),
new SqlParameter("@PublishTime", SqlDbType.DateTime),
new SqlParameter("@CreatedTime", SqlDbType.DateTime),
new SqlParameter("@GroupState", SqlDbType.Int)
};
prms[0].Value = groupEntity.GroupEventName;
prms[1].Value = groupEntity.PublishTime;
prms[2].Value = groupEntity.CreatedTime;
prms[3].Value = groupEntity.GroupState;
//去除重专题文章
//groupRelEntityList = groupRelEntityList.Distinct(new EventItemGroupRelEntityDistinct()).ToList();
using (SqlConnection conn = new SqlConnection(SQlHelper.MyConnectStr))
{
conn.Open();
trans = conn.BeginTransaction();
int groupEventID = Convert.ToInt32(SQlHelper.ExecuteScalar(trans, CommandType.Text, insertGroupSQL, prms));
if (groupRelEntityList != null && groupRelEntityList.Count > 0)
{
foreach (EventItemGroupRelEntity groupRelEntity in groupRelEntityList)
{
sbInsert.AppendFormat(insertRelSQL, groupEventID, groupRelEntity.EventItemID, groupRelEntity.DisplayOrder);
}
int num2 = SQlHelper.ExecuteNonQuery(trans, CommandType.Text, sbInsert.ToString());
}
isSuccess = true;
trans.Commit();
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
if (trans != null)
{
trans.Rollback();
}
return(false);
}
return(isSuccess);
}
public bool Insert(UserMailEntity entity)
{
bool isSuccess = false;
string insertSQL = "INSERT INTO M_User_Mail(UserID,EventItemID,CreateDate,MailStatus,Remark) VALUES (@UserID,@EventItemID,@CreateDate,@MailStatus,@Remark)";
SqlParameter[] prms = new SqlParameter[] {
new SqlParameter("@UserID", SqlDbType.Int, 4),
new SqlParameter("@EventItemID", SqlDbType.Int, 4),
new SqlParameter("@CreateDate", SqlDbType.DateTime),
new SqlParameter("@MailStatus", SqlDbType.Int, 4),
new SqlParameter("@Remark", SqlDbType.NVarChar, 500)
};
prms[0].Value = entity.UserID;
prms[1].Value = entity.EventItemID;
prms[2].Value = entity.CreateDate;
prms[3].Value = entity.MailStatus;
prms[4].Value = entity.Remark;
try
{
isSuccess = SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, insertSQL, prms) > 0;
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
return(isSuccess);
}
/// <summary>
/// 为信息维护员添加某一节点的权限
/// </summary>
/// <remarks>
/// create by perry
/// create time 2010-07-22
/// </remarks>
/// <param name="userid"></param>
/// <returns></returns>
public Boolean AddInfoManagerWithFun(String userid, int functionid)
{
SqlParameter[] prms =
{
new SqlParameter("@userid", userid),
new SqlParameter("@functionid", functionid)
};
try
{
int i = SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, "insert into M_System_User_Fun_Rel(UFRel_FunctionID,UFRel_UserID) values(@functionid,@userid)", prms);
if (i == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
}
public bool BindPhone(int userid, string phone, string code, string pwd, out string msg)
{
msg = string.Empty;
SqlTransaction trans = null;
using (SqlConnection con = new SqlConnection(SQlHelper.MyConnectStr))
{
try
{
con.Open();
trans = con.BeginTransaction();
if (CheckSmsCode(phone, code, trans))
{
string check = "select count(*) from M_User where Telphone=@Telphone";
string sql = "update M_User set LoginName=@LoginName,Telphone=@Telphone,PassWord=@Pwd where UserID=@UserID";
SqlParameter[] prms =
{
new SqlParameter("@Telphone", phone),
new SqlParameter("@LoginName", phone),
new SqlParameter("@UserID", userid),
new SqlParameter("@Pwd", pwd)
};
int cnt = int.Parse(SQlHelper.ExecuteScalar(trans, CommandType.Text, check, prms).ToString());
if (cnt == 0)
{
SQlHelper.ExecuteNonQuery(trans, CommandType.Text, sql, prms);
trans.Commit();
return(true);
}
else
{
trans.Rollback();
msg = "此手机号已经被其他账号绑定，请更换另一个手机号后重试！";
return(false);
}
}
else
{
msg = "验证码无效";
trans.Rollback();
return(false);
}
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
msg = ex.Message;
LogUtil.WriteLog(ex);
return(false);
}
}
}
/// <summary>
/// 删除功能菜单
/// 如果功能菜单已被用户使用，则不可删除
/// </summary>
/// <param name="functionID"></param>
/// <returns></returns>
public bool Delete(int functionID)
{
bool isSuccess = false;
SqlTransaction trans = null;
string selectFunsql = "SELECT Function_Level FROM M_System_Function WHERE Function_ID=@Function_ID";
string delFunsql = "DELETE FROM M_System_Function WHERE Function_ID=@Function_ID";
string delFunSecondLevelsql = "DELETE FROM M_System_Function WHERE Function_ParentID=@Function_ID";
string delRoleFunRelsql = "DELETE FROM M_System_Role_Fun_Rel WHERE RFRel_FunctionID=@Function_ID";
string delUserFunRelsql = "DELETE FROM M_System_User_Fun_Rel WHERE UFRel_FunctionID=@Function_ID";
SqlParameter[] prms =
{
new SqlParameter("@Function_ID", functionID)
};
try
{
using (SqlConnection conn = new SqlConnection(SQlHelper.MyConnectStr))
{
conn.Open();
trans = conn.BeginTransaction();
int funlevel = Convert.ToInt32(SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, selectFunsql, prms));
if (funlevel == 1)
{
SQlHelper.ExecuteNonQuery(trans, CommandType.Text, delFunsql, prms);
SQlHelper.ExecuteNonQuery(trans, CommandType.Text, delFunSecondLevelsql, prms);
SQlHelper.ExecuteNonQuery(trans, CommandType.Text, delRoleFunRelsql, prms);
SQlHelper.ExecuteNonQuery(trans, CommandType.Text, delUserFunRelsql, prms);
}
else
{
SQlHelper.ExecuteNonQuery(trans, CommandType.Text, delFunsql, prms);
SQlHelper.ExecuteNonQuery(trans, CommandType.Text, delRoleFunRelsql, prms);
SQlHelper.ExecuteNonQuery(trans, CommandType.Text, delUserFunRelsql, prms);
}
trans.Commit();
isSuccess = true;
}
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
LogUtil.WriteLog(ex);
return(false);
}
return(isSuccess);
}
/// <summary>
/// 添加
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool Insert(AdminEntity entity)
{
bool isSuccess = false;
try
{
SqlParameter[] prms =
{
new SqlParameter("@TrueName", SqlDbType.NVarChar, 20),
new SqlParameter("@User_Name", SqlDbType.NVarChar, 20),
new SqlParameter("@User_Pwd", SqlDbType.NVarChar, 100),
new SqlParameter("@User_Sex", SqlDbType.Int),
new SqlParameter("@User_Tel", SqlDbType.NVarChar, 15),
new SqlParameter("@User_Tel_Private", SqlDbType.NVarChar, 15),
new SqlParameter("@User_Mobile", SqlDbType.NVarChar, 15),
new SqlParameter("@User_Mail", SqlDbType.NVarChar, 50),
new SqlParameter("@User_PhotoPath", SqlDbType.NVarChar, 200),
new SqlParameter("@User_DeptID", SqlDbType.Int),
new SqlParameter("@RegisterDate", SqlDbType.DateTime),
new SqlParameter("@IsValid", SqlDbType.Bit),
new SqlParameter("@User_PositionID", SqlDbType.Int),
};
prms[0].Value = entity.TrueName;
prms[1].Value = entity.User_Name;
prms[2].Value = entity.User_Pwd;
prms[3].Value = entity.User_Sex;
prms[4].Value = entity.User_Tel;
prms[5].Value = entity.User_Tel_Private;
prms[6].Value = entity.User_Mobile;
prms[7].Value = entity.User_Mail;
prms[8].Value = entity.User_PhotoPath;
prms[9].Value = entity.User_DeptID;
prms[10].Value = entity.RegisterDate;
prms[11].Value = entity.IsValid;
prms[12].Value = entity.User_PositionID;
StringBuilder sbSql = new StringBuilder();
sbSql.Append(@" INSERT INTO M_System_User(TrueName,User_Name,User_Pwd,User_Sex,User_Tel, User_Tel_Private,User_Mobile,User_Mail,
User_PhotoPath,User_DeptID,RegisterDate,IsValid,User_PositionID)");
sbSql.Append("VALUES(");
sbSql.Append(@"@TrueName,@User_Name,@User_Pwd,@User_Sex,@User_Tel, @User_Tel_Private,@User_Mobile,@User_Mail,
@User_PhotoPath,@User_DeptID,@RegisterDate,@IsValid,@User_PositionID");
sbSql.Append(" )");
isSuccess = SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, sbSql.ToString(), prms) > 0 ? true : false;
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
return(isSuccess);
}
/// <summary>
/// 删除展会信息（假删除，讲展会信息的公共状态修改）
/// </summary>
/// <param name="exhibitionID"></param>
/// <returns></returns>
public bool DeleteExhibition(int exhibitionID)
{
bool returnValue = false;
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"UPDATE dbo.M_Exhibition_Main SET StateTypeID = 0 WHERE ExhibitionID = {0}"
, exhibitionID.ToString());
returnValue = SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), null) > 0;
return(returnValue);
}
/// <summary>
/// 删除与我相关的消息,在删除评论之前删除
/// </summary>
/// <param name="commmentID"></param>
public void Relate_Delete(int commmentID)
{
try
{
SqlParameter[] parms = { new SqlParameter("@CommentID", commmentID) };
SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "M_Relate_Del", parms);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
}
}
/// <summary>
/// 添加角色并和菜单关联
/// </summary>
/// <param name="roleEntity"></param>
/// <param name="roleFunRelList"></param>
/// <returns></returns>
public bool Insert(SysRoleEntity roleEntity, List <SysRoleFunctionRelEntity> roleFunRelList)
{
bool isSuccess = false;
SqlTransaction trans = null;
try
{
SqlParameter[] rolePrms =
{
new SqlParameter("@Role_Name", SqlDbType.NVarChar, 20),
new SqlParameter("@CreateTime", SqlDbType.DateTime)
};
rolePrms[0].Value = roleEntity.Role_Name;
rolePrms[1].Value = roleEntity.CreateTime;
string roleSql = " INSERT INTO M_System_Role(Role_Name, CreateTime) VALUES (@Role_Name, @CreateTime);SELECT @@IDENTITY; ";
string roleFunRelSql = "INSERT INTO M_System_Role_Fun_Rel(RFRel_FunctionID, RFRel_RoleID) VALUES(@RFRel_FunctionID, @RFRel_RoleID)";
using (SqlConnection conn = new SqlConnection(SQlHelper.MyConnectStr))
{
conn.Open();
trans = conn.BeginTransaction();
object result = SQlHelper.ExecuteScalar(trans, CommandType.Text, roleSql, rolePrms);
int pkid = Convert.ToInt32(result);
foreach (SysRoleFunctionRelEntity entity in roleFunRelList)
{
SqlParameter[] roleFunRelPrms =
{
new SqlParameter("@RFRel_FunctionID", entity.RFRel_FunctionID),
new SqlParameter("@RFRel_RoleID", pkid)
};
SQlHelper.ExecuteNonQuery(trans, CommandType.Text, roleFunRelSql, roleFunRelPrms);
}
trans.Commit();
isSuccess = true;
}
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
LogUtil.WriteLog(ex);
return(false);
}
return(isSuccess);
}
/// <summary>
/// 编辑用户的权限(带事务)
/// </summary>
/// <remarks>
/// author: zp
/// createtime: 2010.07.16
/// </remarks>
/// <param name="userid">用户编号</param>
/// <param name="list">权限集合</param>
/// <returns></returns>
public bool EditUserFunRel(String userid, List <FunctionEntity> list)
{
#region
SqlTransaction trans = null;
using (SqlConnection conn = new SqlConnection(SQlHelper.MyConnectStr))
{
SqlParameter[] prms =
{
new SqlParameter("@userid", userid)
};
conn.Open();
trans = conn.BeginTransaction();
try
{
if (SQlHelper.ExecuteNonQuery(trans, CommandType.Text, "delete from M_System_User_Fun_Rel where UFRel_UserID=@userid", prms) >= 0)
{
#region
foreach (FunctionEntity var in list)
{
SqlParameter[] prms_fun =
{
new SqlParameter("@userid", userid),
new SqlParameter("@funid", var.Function_ID)
};
if (!(SQlHelper.ExecuteNonQuery(trans, CommandType.Text, "insert into M_System_User_Fun_Rel values(@funid,@userid)", prms_fun) >= 0))
{
trans.Rollback();
return(false);
}
}
trans.Commit();
return(true);
#endregion
}
else
{
trans.Rollback();
return(false);
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
trans.Rollback();
return(false);
}
}
#endregion
}
public bool SMS_Update(string content, int isSend, string modelKey, int smsID)
{
try
{
string sql = " UPDATE M_SignSMS SET Content='{0}',IsSend={1},SendTime='{2}',ModelKey='{3}' WHERE SmsID='{4}' ";
sql = string.Format(sql, content, isSend, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), modelKey, smsID);
return(int.Parse(SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, sql).ToString()) > 0);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(true);
}
}
/// <summary>
/// 添加一条
/// </summary>
/// <param name="up"></param>
/// <param name="down"></param>
/// <param name="horizontal"></param>
/// <returns></returns>
public int Add(string up, string down, string horizontal)
{
int returnValue = 0;
using (SqlConnection con = new SqlConnection(SQlHelper.MyConnectStr))
{
SqlTransaction trans = null;
con.Open();
trans = con.BeginTransaction();
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT MAX(CoupletID) AS MaxID FROM dbo.NY_Couplet");
object obj = SQlHelper.ExecuteScalar(trans, CommandType.Text, strSql.ToString(), null);
if (obj != null)
{
returnValue = obj.ToInt() + 1;
StringBuilder strCouplet = new StringBuilder();
strCouplet.AppendFormat(@"INSERT INTO dbo.NY_Couplet( CoupletID ,CoupletContentTypeID ,CoupletContent ,OrderBy ,StateTypeID ,CoupletTypeID)
VALUES ( {0} ,0 ,'{1}' ,1000 ,1 ,0);
INSERT INTO dbo.NY_Couplet( CoupletID ,CoupletContentTypeID ,CoupletContent ,OrderBy ,StateTypeID ,CoupletTypeID)
VALUES ( {0} ,1 ,'{2}' ,1000 ,1 ,0);
INSERT INTO dbo.NY_Couplet( CoupletID ,CoupletContentTypeID ,CoupletContent ,OrderBy ,StateTypeID ,CoupletTypeID)
VALUES ( {0} ,2 ,'{3}' ,1000 ,1 ,0);"
, returnValue.ToString(), up, down, horizontal);
SQlHelper.ExecuteNonQuery(trans, CommandType.Text, strCouplet.ToString(), null);
}
trans.Commit();
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
returnValue = 0;
trans.Rollback();
}
finally
{
trans.Dispose();
con.Close();
con.Dispose();
}
}
return(returnValue);
}
public OperationResult <bool> DialogMessages_Delete(int messageID)
{
try
{
string sql = " DELETE FROM M_DialogMessages WHERE MessageID={0} ";
sql = string.Format(sql, messageID);
bool result = int.Parse(SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, sql).ToString()) > 0;
return(new OperationResult <bool>(OperationResultType.Success, "删除完成！", result));
}
catch (Exception ex)
{
return(new OperationResult <bool>(OperationResultType.Success, "异常结果:" + ex.Message, false));
}
}
public OperationResult <bool> ExhibitionEmail_UpdateSendTypeID(int bookListCustomerID, int sendTypeID)
{
try
{
string sql = " UPDATE M_Exhibition_BookList_Customer_Rel SET SendTypeID={0} WHERE BookListCustomerID={1} ";
sql = string.Format(sql, sendTypeID, bookListCustomerID);
bool state = int.Parse(SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, sql).ToString()) > 0;
return(new OperationResult <bool>(OperationResultType.Success, "数据完成查询", state));
}
catch (Exception ex)
{
return(new OperationResult <bool>(OperationResultType.Error, ex.Message, false));
}
}
/// <summary>
/// 将用户下与我相关的未读信息设置成已读
/// </summary>
/// <param name="userId"></param>
public void Relate_UpdateRead(int userId)
{
try
{
string sql = string.Empty;
sql = " UPDATE [dbo].[M_MobileMessages] SET IsRead=1 WHERE ToUserID={0} AND MessageType IN(1,2) ";
sql = string.Format(sql, userId);
SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, sql);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
}
}
public bool SignBook_Delete(int signID)
{
try
{
string sql = " DELETE FROM M_SignBook WHERE SignID='{0}' ";
sql = string.Format(sql, signID);
return(int.Parse(SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, sql).ToString()) > 0);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(true);
}
}
public bool SignBook_Update(string customerKey, int state)
{
try
{
string sql = " UPDATE M_SignBook SET IsSign={0} WHERE CustomerKey='{1}' ";
sql = string.Format(sql, state, customerKey);
return(int.Parse(SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, sql).ToString()) > 0);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(true);
}
}
public bool SignBook_Update(SignBookEntity entity)
{
try
{
string sql = " UPDATE M_SignBook SET Company='{0}',Department='{1}',Position='{2}',Email='{3}' WHERE SignID='{4}' ";
sql = string.Format(sql, entity.Company, entity.Department, entity.Position, entity.Email, entity.SignID);
return(int.Parse(SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, sql).ToString()) > 0);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(true);
}
}
/// <summary>
/// 添加日历信息
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public string InsertFestival(FestivalEntity entity)
{
string flg = "0";
string sql = "select * from M_Festival where FestivalName=@FestivalName and StartTime=@StartTime and EndTime=@EndTime and FestivalType=@FestivalType";
string sql1 = "insert into M_Festival(FestivalName,FestivalShortName,StartTime,EndTime,FestivalType,FestivalWeight) values (@FestivalName,@FestivalShortName,@StartTime,@EndTime,@FestivalType,@FestivalWeight)";
SqlParameter[] pars = { new SqlParameter("FestivalName", SqlDbType.NVarChar, 50),
new SqlParameter("FestivalShortName", SqlDbType.NVarChar, 50),
new SqlParameter("StartTime", SqlDbType.DateTime),
new SqlParameter("EndTime", SqlDbType.DateTime),
new SqlParameter("FestivalType", SqlDbType.Int),
new SqlParameter("FestivalWeight", SqlDbType.Int) };
pars[0].Value = entity.FestivalName;
pars[1].Value = entity.FestivalShortName;
pars[2].Value = entity.StartTime;
pars[3].Value = entity.EndTime;
pars[4].Value = entity.FestivalType;
pars[5].Value = entity.FestivalWeight;
SqlTransaction trans = null;
using (SqlConnection conn = new SqlConnection(SQlHelper.MyConnectStr))
{
try
{
conn.Open();
trans = conn.BeginTransaction();
DataTable db = SQlHelper.ExecuteDataset(trans, CommandType.Text, sql, pars).Tables[0];
if (db.Rows.Count == 0)
{
SQlHelper.ExecuteNonQuery(trans, CommandType.Text, sql1, pars);
flg = "1";
trans.Commit();
}
else
{
flg = "2";
}
}
catch (Exception e)
{
LogUtil.WriteLog(e);
if (trans != null)
{
flg = "0"; //数据传输错误！
trans.Rollback();
}
}
}
return(flg);
}
/// <summary>
/// 评论举报
/// </summary>
/// <param name="entity">举报实体</param>
/// <returns></returns>
public OperationResult <bool> Report_Insert(ReportEntity entity)
{
try
{
using (SqlConnection con = new SqlConnection(SQlHelper.MyConnectStr))
{
SqlTransaction trans = null;
con.Open();
trans = con.BeginTransaction();
SqlParameter[] prms =
{
new SqlParameter("@FromUserID", entity.FromUserID),
new SqlParameter("@ReportTypeID", entity.ReportTypeID),
new SqlParameter("@ReportContent", entity.ReportContent),
new SqlParameter("@ReportInfoTypeID", entity.ReportInfoTypeID),
new SqlParameter("@ReportInfoID", entity.ReportInfoID),
new SqlParameter("@CreateTime", entity.CreateTime == null ? DateTime.Now :entity.CreateTime)
};
string insert_sql = string.Empty;
string select_sql = string.Empty;
select_sql = " SELECT COUNT(1) FROM M_Report WHERE FromUserID={0} AND ReportInfoTypeID={1} AND ReportInfoID={2} ";
select_sql = string.Format(select_sql, entity.FromUserID, entity.ReportInfoTypeID, entity.ReportInfoID);
int count = int.Parse(SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, select_sql).ToString());
if (count > 0)
{
return(new OperationResult <bool>(OperationResultType.NoChanged, "您已举报过了！", false));
}
insert_sql = " INSERT INTO M_Report(FromUserID,ReportTypeID,ReportContent,ReportInfoTypeID,ReportInfoID,CreateTime) ";
insert_sql += " VALUES(@FromUserID,@ReportTypeID,@ReportContent,@ReportInfoTypeID,@ReportInfoID,@CreateTime) ";
bool state = SQlHelper.ExecuteNonQuery(trans, CommandType.Text, insert_sql, prms) > 0;
trans.Commit();
if (state)
{
return(new OperationResult <bool>(OperationResultType.Success, "举报成功！", state));
}
else
{
return(new OperationResult <bool>(OperationResultType.Error, "举报失败！", state));
}
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <bool>(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
public bool Update(FunctionEntity entity)
{
bool isSuccess = false;
StringBuilder sbSql = new StringBuilder();
sbSql.Append(" UPDATE M_System_Function");
sbSql.Append(" SET");
sbSql.Append(" Function_Name=@Function_Name,Function_URL=@Function_URL,Function_ParentID=@Function_ParentID,Function_Order=@Function_Order,");
sbSql.Append(" Function_isValid=@Function_isValid,Function_Level=@Function_Level,Function_URL_New=@Function_URL_New,Function_IsNew=@Function_IsNew,CreateDate=@CreateDate");
sbSql.Append(" WHERE Function_ID=@Function_ID");
SqlParameter[] prms =
{
new SqlParameter("@Function_Name", SqlDbType.NVarChar, 20),
new SqlParameter("@Function_URL", SqlDbType.NText),
new SqlParameter("@Function_ParentID", SqlDbType.Int),
new SqlParameter("@Function_Order", SqlDbType.NVarChar, 20),
new SqlParameter("@Function_isValid", SqlDbType.Int),
new SqlParameter("@Function_Level", SqlDbType.Int),
new SqlParameter("@Function_URL_New", SqlDbType.NVarChar, 200),
new SqlParameter("@Function_IsNew", SqlDbType.Int),
new SqlParameter("@CreateDate", SqlDbType.DateTime),
new SqlParameter("@Function_ID", SqlDbType.Int)
};
prms[0].Value = entity.Function_Name;
prms[1].Value = entity.Function_URL;
prms[2].Value = entity.Function_ParentID;
prms[3].Value = entity.Function_Order;
prms[4].Value = entity.Function_isValid;
prms[5].Value = entity.Function_Level;
prms[6].Value = entity.Function_URL_New;
prms[7].Value = entity.Function_IsNew;
prms[8].Value = entity.CreateDate;
prms[9].Value = entity.Function_ID;
try
{
isSuccess = SQlHelper.ExecuteNonQuery(SQlHelper.MyConnectStr, CommandType.Text, sbSql.ToString(), prms) > 0;
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
return(isSuccess);
}
