/// <summary>
/// 获取文章浏览次数
/// </summary>
/// <param name="eventItemGuid"></param>
/// <returns></returns>
public int GetBrowserCnt(Guid eventItemGuid)
{
int broCnts = 0;
StringBuilder sbSql = new StringBuilder();
sbSql.Append("IF EXISTS (SELECT * FROM M_EventItemBrowseCnts WHERE EventItemGUID=@EventItemGUID)");
sbSql.Append(" SELECT 0");
sbSql.Append(" ELSE");
sbSql.Append(" SELECT BrowseCnt FROM M_EventItemBrowseCnts WHERE EventItemGUID=@EventItemGUID");
SqlParameter[] prms =
{
new SqlParameter("@EventItemGUID", SqlDbType.UniqueIdentifier, 16)
};
prms[0].Value = eventItemGuid;
try
{
object result = SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, sbSql.ToString(), prms);
int.TryParse(result.ToString(), out broCnts);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(0);
}
return(broCnts);
}
public int AddSensitiveWord(SensitiveWordEntity item)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into M_System_SensitiveWords(");
strSql.Append("SensitiveWords,StateTypeID,IsNeedRecheck,CreateUserID,CreateTime)");
strSql.Append(" values (");
strSql.Append("@SensitiveWords,@StateTypeID,@IsNeedRecheck,@CreateUserID,@CreateTime)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@SensitiveWords", SqlDbType.NVarChar, 100),
new SqlParameter("@StateTypeID", SqlDbType.Int, 4),
new SqlParameter("@IsNeedRecheck", SqlDbType.Bit, 1),
new SqlParameter("@CreateUserID", SqlDbType.Int, 4),
new SqlParameter("@CreateTime", SqlDbType.DateTime)
};
parameters[0].Value = item.SensitiveWords;
parameters[1].Value = item.StateTypeID;
parameters[2].Value = item.IsNeedRecheck;
parameters[3].Value = item.CreateUserID;
parameters[4].Value = item.CreateTime;
object obj = SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 判断是否可以删除角色
/// 当 用户没有使用该角色时方可删除
/// </summary>
/// <param name="roleID"></param>
/// <returns></returns>
public bool IsCanDelRole(int roleID)
{
bool isCanDel = false;
try
{
SqlParameter[] prms =
{
new SqlParameter("@User_RoleID", roleID)
};
string sql = "SELECT COUNT(*) FROM M_System_User_Role_Rel WHERE User_RoleID=@User_RoleID";
object result = SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, sql, prms);
if (result != null && Convert.ToInt32(result) == 0)
{
isCanDel = true;
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
return(isCanDel);
}
/// <summary>
/// 判断用户是否可以注册
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
public bool IsUseableByUsername(string username)
{
bool isUseable = false; //true 可用 false已存在
try
{
SqlParameter[] prms =
{
new SqlParameter("@User_Name", SqlDbType.NVarChar, 20)
};
prms[0].Value = username;
string sql = "SELECT COUNT(User_Name) FROM M_System_User WHERE User_Name=@User_Name";
object result = SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, sql, prms);
if (result != null && Convert.ToInt32(result) == 0)
{
isUseable = true;
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
return(isUseable);
}
public int Insert(PictureEntity entity)
{
int returnValue = 0;
string sql = "Insert Into M_Pictures(PictureServerID,PicturePath,PictureState) Values(@PictureServerID,@PicturePath,@PictureState);SELECT @@IDENTITY;";
SqlParameter[] prms =
{
new SqlParameter("@PictureServerID", SqlDbType.Int),
new SqlParameter("@PicturePath", SqlDbType.NVarChar,200),
new SqlParameter("@PictureState", SqlDbType.Int)
};
prms[0].Value = entity.PictureServerID;
prms[1].Value = entity.PicturePath;
prms[2].Value = entity.PictureState;
try
{
object obj = SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, sql, prms);
if (obj != null)
{
returnValue = obj.ToInt();
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
}
return(returnValue);
}
/// <summary>
/// 判断该菜单项是可否删除 true可删除 false不可删除
/// </summary>
/// <param name="functionID"></param>
/// <returns></returns>
public bool IsCanDelFunction(int functionID)
{
bool isCandel = false;
StringBuilder sbSql = new StringBuilder();
sbSql.Append(" SELECT COUNT(fun.Function_ID) FROM M_System_Function fun");
sbSql.Append(" INNER JOIN M_System_User_Fun_Rel userfun ON fun.Function_ID=userfun.UFRel_FunctionID");
sbSql.Append(" INNER JOIN M_V_System_User us ON us.[User_ID]=userfun.UFRel_UserID");
sbSql.Append(" WHERE fun.Function_ID=@Function_ID");
SqlParameter[] prms =
{
new SqlParameter("@Function_ID", functionID)
};
try
{
object result = SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, sbSql.ToString(), prms);
if (Convert.ToInt32(result) == 0)
{
isCandel = true;
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
return(isCandel);
}
public bool CheckSmsCode(string phone, string code, SqlTransaction trans)
{
try
{
SqlParameter[] prms =
{
new SqlParameter("@Phone", phone),
new SqlParameter("@VCode", code)
};
string sql = "SELECT count(1) FROM [dbo].[M_SmsCodes] WHERE Phone=@Phone AND VCode=@VCode AND ExpriedTime>=GETDATE()";
if (trans != null)
{
return(int.Parse(SQlHelper.ExecuteScalar(trans, CommandType.Text, sql, prms).ToString()) > 0);
}
else
{
return(int.Parse(SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, sql, prms).ToString()) > 0);
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
}
/// <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);
}
/// <summary>
/// 根据版本号，判断是否进行审核
/// </summary>
/// <param name="versionCode"></param>
/// <returns></returns>
public bool IsVersionCheck(string versionCode)
{
bool returnValue = true;
StringBuilder strSql = new StringBuilder();
try
{
strSql.AppendFormat(@"SELECT [VerState] FROM [M_Version_CheckState] WHERE VersionCode = '{0}'"
, versionCode);
object obj = SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), null);
if (obj != null)
{
if (int.Parse(obj.ToString()) == 0)
{
returnValue = false;
}
}
else
{
returnValue = false;
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(true);
}
return(returnValue);
}
public bool SMS_Insert(SMSEntity entity)
{
bool isSuccess = false;
SqlTransaction trans = null;
string sql = string.Empty;
sql += " INSERT INTO M_SignSMS(Customer,Moblie,IsSend,Content,SendTime,SysUserID) ";
sql += " VALUES(@Customer,@Moblie,@IsSend,@Content,@SendTime,@SysUserID)";
SqlParameter[] prms =
{
new SqlParameter("@Customer", SqlDbType.NVarChar, 128),
new SqlParameter("@Moblie", SqlDbType.NVarChar, 64),
new SqlParameter("@IsSend", SqlDbType.Int),
new SqlParameter("@Content", SqlDbType.NVarChar, 1024),
new SqlParameter("@SendTime", SqlDbType.NVarChar, 48),
new SqlParameter("@SysUserID", SqlDbType.Int),
new SqlParameter("@ModelKey", SqlDbType.VarChar, 24)
};
prms[0].Value = entity.Customer;
prms[1].Value = entity.Moblie;
prms[2].Value = entity.IsSend;
prms[3].Value = entity.Content;
prms[4].Value = entity.SendTime;
prms[5].Value = entity.SysUserID;
prms[6].Value = entity.ModelKey;
try
{
using (SqlConnection conn = new SqlConnection(SQlHelper.MyConnectStr))
{
conn.Open();
trans = conn.BeginTransaction();
SQlHelper.ExecuteScalar(trans, CommandType.Text, sql, prms);
isSuccess = true;
trans.Commit();
}
}
catch (Exception ex)
{
try
{
if (trans != null)
{
trans.Rollback();
}
}
catch
{
LogUtil.WriteLog(ex);
return(false);
}
return(false);
}
return(isSuccess);
}
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);
}
}
}
public OperationResult <bool> DialogMessages_Update(DialogMessagesEntity entity)
{
SqlTransaction trans = null;
try
{
string sql = string.Empty;
sql += " UPDATE M_DialogMessages SET ImageLink=@ImageLink,MoblieType=@MoblieType,ButtonText=@ButtonText ";
sql += ",ArticleLink=@ArticleLink,Contents=@Contents,StartType=@StartType ";
sql += ",StartTime=@StartTime,EndTime=@EndTime,StartCount=@StartCount ";
sql += " WHERE MessageID=@MessageID ";
SqlParameter[] prms =
{
new SqlParameter("@ImageLink", SqlDbType.NVarChar, 128),
new SqlParameter("@MoblieType", SqlDbType.Int),
new SqlParameter("@ButtonText", SqlDbType.NVarChar, 64),
new SqlParameter("@ArticleLink", SqlDbType.NVarChar, 128),
new SqlParameter("@Contents", SqlDbType.NVarChar, 1024),
new SqlParameter("@StartType", SqlDbType.Int),
new SqlParameter("@StartTime", SqlDbType.DateTime),
new SqlParameter("@EndTime", SqlDbType.DateTime),
new SqlParameter("@StartCount", SqlDbType.Int),
new SqlParameter("@MessageID", SqlDbType.Int)
};
prms[0].Value = entity.ImageLink;
prms[1].Value = entity.MoblieType;
prms[2].Value = entity.ButtonText;
prms[3].Value = entity.ArticleLink;
prms[4].Value = entity.Contents;
prms[5].Value = entity.StartType;
prms[6].Value = entity.StartTime;
prms[7].Value = entity.EndTime;
prms[8].Value = entity.StartCount;
prms[9].Value = entity.MessageID;
using (SqlConnection conn = new SqlConnection(SQlHelper.MyConnectStr))
{
conn.Open();
trans = conn.BeginTransaction();
SQlHelper.ExecuteScalar(trans, CommandType.Text, sql, prms);
trans.Commit();
}
return(new OperationResult <bool>(OperationResultType.Success, "数据更新成功！", true));
}
catch (Exception ex)
{
try
{
if (trans != null)
{
trans.Rollback();
}
}
catch { }
return(new OperationResult <bool>(OperationResultType.Error, "异常结果:" + ex.Message, 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);
}
public OperationResult <bool> ExhibitionEmail_Insert(ExhibitionEmailEntity entity)
{
SqlTransaction trans = null;
string sql = string.Empty;
sql += " INSERT INTO M_Exhibition_BookList_Customer_Rel(ExhibitionID,CustomerID,CustomerToken,CustomerEmail,CreateTime,SendTypeID,SendTime,CustomerName) ";
sql += " VALUES(@ExhibitionID,@CustomerID,@CustomerToken,@CustomerEmail,@CreateTime,@SendTypeID,@SendTime,@CustomerName)";
SqlParameter[] prms =
{
new SqlParameter("@ExhibitionID", SqlDbType.Int),
new SqlParameter("@CustomerID", SqlDbType.Int),
new SqlParameter("@CustomerToken", SqlDbType.NVarChar, 64),
new SqlParameter("@CustomerEmail", SqlDbType.NVarChar, 64),
new SqlParameter("@CreateTime", SqlDbType.DateTime),
new SqlParameter("@SendTypeID", SqlDbType.Int),
new SqlParameter("@SendTime", SqlDbType.DateTime),
new SqlParameter("@CustomerName", SqlDbType.NVarChar, 64)
};
prms[0].Value = entity.ExhibitionID;
prms[1].Value = entity.CustomerID;
prms[2].Value = entity.CustomerToken;
prms[3].Value = entity.CustomerEmail;
prms[4].Value = entity.CreateTime;
prms[5].Value = entity.SendTypeID;
prms[6].Value = entity.SendTime;
prms[7].Value = entity.CustomerName;
try
{
using (SqlConnection conn = new SqlConnection(SQlHelper.MyConnectStr))
{
conn.Open();
trans = conn.BeginTransaction();
SQlHelper.ExecuteScalar(trans, CommandType.Text, sql, prms);
trans.Commit();
return(new OperationResult <bool>(OperationResultType.Success, "数据完成查询", true));
}
}
catch (Exception ex)
{
if (trans != null)
{
try
{
trans.Rollback();
}
catch (Exception tranex)
{
LogUtil.WriteLog(tranex);
}
}
return(new OperationResult <bool>(OperationResultType.Error, ex.Message, false));
}
}
/// <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);
}
public int AddShareLog(ShareLogEntity item)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into NY_ShareLog(");
strSql.Append("CreateTime,ShareUserID,MachineCode,ShareTypeID,SystemName,Verson,IPAddress,ExInfo,CoupletID,ImageID,IsView)");
strSql.Append(" values (");
strSql.Append("@CreateTime,@ShareUserID,@MachineCode,@ShareTypeID,@SystemName,@Verson,@IPAddress,@ExInfo,@CoupletID,@ImageID,@IsView)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@CreateTime", SqlDbType.DateTime),
new SqlParameter("@ShareUserID", SqlDbType.Int, 4),
new SqlParameter("@MachineCode", SqlDbType.VarChar, 100),
new SqlParameter("@ShareTypeID", SqlDbType.Int, 4),
new SqlParameter("@SystemName", SqlDbType.VarChar, 20),
new SqlParameter("@Verson", SqlDbType.VarChar, 10),
new SqlParameter("@IPAddress", SqlDbType.VarChar, 20),
new SqlParameter("@ExInfo", SqlDbType.VarChar, 500),
new SqlParameter("@CoupletID", SqlDbType.Int, 4),
new SqlParameter("@ImageID", SqlDbType.Int, 4),
new SqlParameter("@IsView", SqlDbType.Int, 4)
};
parameters[0].Value = item.CreateTime;
parameters[1].Value = item.ShareUserID;
parameters[2].Value = item.MachineCode;
parameters[3].Value = item.ShareTypeID;
parameters[4].Value = item.SystemName;
parameters[5].Value = item.Verson;
parameters[6].Value = item.IPAddress;
parameters[7].Value = item.ExInfo;
parameters[8].Value = item.CoupletID;
parameters[9].Value = item.ImageID;
parameters[10].Value = item.IsView;
try
{
object obj = SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(0);
}
}
/// <summary>
/// 通过调用 up_getExhibitionIsPublish 存储过程来获取某个展会是否开始活动
/// 存储过程中有临时写死的展会id
/// </summary>
/// <returns></returns>
public bool IsPublished()
{
bool returnValue = false;
object obj = SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "up_getExhibitionIsPublish", null);
if (obj != null)
{
returnValue = bool.Parse(obj.ToString());
}
return(returnValue);
}
/// <summary>
/// NickName 随机数
/// </summary>
/// <returns></returns>
public int NickNameMaxID()
{
try
{
string sqlcheck = " INSERT INTO M_User_NickNameMaxID (IsUse) VALUES(1);SELECT SCOPE_IDENTITY() ";
return(int.Parse(SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, sqlcheck).ToString()));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(0);
}
}
public int SignBook_Count(string strWhere = null)
{
try
{
string sql = " SELECT COUNT(1) FROM M_SignBook WHERE 1=1 " + strWhere;
return(int.Parse(SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, sql).ToString()));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(0);
}
}
public bool IsAllowCustomerKey(string customerKey)
{
try
{
string sql = " SELECT COUNT(1) FROM M_SignBook WHERE CustomerKey='{0}' ";
sql = string.Format(sql, customerKey);
return(int.Parse(SQlHelper.ExecuteScalar(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 bool IsExistLuckyNumber(string luckyNumber)
{
try
{
string sql = " SELECT COUNT(1) FROM M_SignBook WHERE LuckyNumber='{0}' ";
sql = string.Format(sql, luckyNumber);
return(int.Parse(SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, sql).ToString()) > 0);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(true);
}
}
/// <summary>
/// 新增分类
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool Insert(CategoriesEntity entity)
{
bool isSuccess = false;
SqlTransaction trans = null;
StringBuilder sbSql = new StringBuilder();
sbSql.Append(" INSERT INTO M_CalendarType(ParentCalendarTypeID,CalendarTypeName,CalendarTypeKind,Descripition,Dismiss,PictureID) values(@ParentCalendarTypeID,@CalendarTypeName,@CalendarTypeKind,@Descripition,@Dismiss,@PictureID)");
SqlParameter[] prms =
{
new SqlParameter("@ParentCalendarTypeID", SqlDbType.Int),
new SqlParameter("@CalendarTypeName", SqlDbType.NVarChar, 100),
new SqlParameter("@CalendarTypeKind", SqlDbType.SmallInt),
new SqlParameter("@Descripition", SqlDbType.NVarChar, 200),
new SqlParameter("@Dismiss", SqlDbType.Bit),
new SqlParameter("@PictureID", SqlDbType.Int)
};
prms[0].Value = entity.ParentCalendarTypeID;
prms[1].Value = entity.CalendarTypeName;
prms[2].Value = entity.CalendarTypeKind;
prms[3].Value = entity.Descripition;
prms[4].Value = entity.Dismiss;
prms[5].Value = entity.PictureID;
try
{
using (SqlConnection conn = new SqlConnection(SQlHelper.MyConnectStr))
{
conn.Open();
trans = conn.BeginTransaction();
SQlHelper.ExecuteScalar(trans, CommandType.Text, sbSql.ToString(), prms);
isSuccess = true;
trans.Commit();
}
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
LogUtil.WriteLog(ex);
return(false);
}
return(isSuccess);
}
/// <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 CheckUserExist(string loginname)
{
try
{
string sqlcheck = "select count(1) from M_User where LoginName=@LoginName";
SqlParameter[] prms =
{
new SqlParameter("@LoginName", loginname)
};
return(int.Parse(SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, sqlcheck, prms).ToString()) > 0);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(true);
}
}
/// <summary>
/// 判断邮箱是否可用
/// </summary>
/// <param name="email"></param>
/// <returns></returns>
public bool IsUseableByEmail(string email, int userID)
{
bool isUseable = false; //true 可用 false已存在
try
{
string sql = null;
object result = null;
if (userID > 0)
{
SqlParameter[] prms =
{
new SqlParameter("@User_Mail", SqlDbType.NVarChar, 50),
new SqlParameter("@User_ID", SqlDbType.Int)
};
prms[0].Value = email;
prms[1].Value = userID;
sql = "SELECT COUNT(User_Mail) FROM M_System_User WHERE [User_ID]!=@User_ID AND User_Mail=@User_Mail";
result = SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, sql, prms);
}
else
{
SqlParameter[] prms =
{
new SqlParameter("@User_Mail", SqlDbType.NVarChar, 50)
};
prms[0].Value = email;
sql = "SELECT COUNT(User_Mail) FROM M_System_User WHERE User_Mail=@User_Mail";
result = SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, sql, prms);
}
if (result != null && Convert.ToInt32(result) == 0)
{
isUseable = true;
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
return(isUseable);
}
/// <summary>
/// 根据用户编号查询角色编号
/// </summary>
/// <remarks>
/// author: zp
/// createtime: 2010.07.13
/// </remarks>
/// <param name="userid">用户编号</param>
/// <returns></returns>
public int GetRoleByUserID(String userid)
{
SqlParameter[] prms =
{
new SqlParameter("@userid", userid)
};
try
{
Object o = SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, "select User_RoleID from M_System_User_Role_Rel where User_ID=@userid", prms);
return(Convert.ToInt32(o.ToString()));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(-1);
}
}
/// <summary>
/// 返回分享总数
/// </summary>
/// <returns></returns>
public int GetShareCount()
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT COUNT(0) AS Cnt FROM NY_ShareLog WITH(NOLOCK)");
object obj = SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), null);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 获取 Message总数
/// </summary>
/// <param name="strWhere">查询条件</param>
/// <returns></returns>
public OperationResult <int> MobileMessage_Total(string strWhere = null)
{
try
{
string sql = string.Empty;
sql = " SELECT COUNT(1) FROM [dbo].[M_MobileMessages] WHERE 1=1 {0} ";
sql = string.Format(sql, strWhere);
int total = int.Parse(SQlHelper.ExecuteScalar(SQlHelper.MyConnectStr, CommandType.Text, sql).ToString());
return(new OperationResult <int>(OperationResultType.Success, "与我相关总数获取成功！", total));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <int>(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
public OperationResult <bool> UserRemind_Insert(UserRemindEntity entity)
{
SqlTransaction trans = null;
try
{
string sql = string.Empty;
sql += " INSERT INTO M_User_Remind(UserID,RemindTypeID,Data,UpdateTime) ";
sql += " VALUES(@UserID,@RemindTypeID,@Data,@UpdateTime) ";
SqlParameter[] prms =
{
new SqlParameter("@UserID", SqlDbType.Int),
new SqlParameter("@RemindTypeID", SqlDbType.Int),
new SqlParameter("@Data", SqlDbType.NVarChar,128),
new SqlParameter("@UpdateTime", SqlDbType.DateTime),
};
prms[0].Value = entity.UserID;
prms[1].Value = entity.RemindTypeID;
prms[2].Value = entity.Data;
prms[3].Value = DateTime.Now;
using (SqlConnection conn = new SqlConnection(SQlHelper.MyConnectStr))
{
conn.Open();
trans = conn.BeginTransaction();
SQlHelper.ExecuteScalar(trans, CommandType.Text, sql, prms);
trans.Commit();
}
return(new OperationResult <bool>(OperationResultType.Success, "数据加入成功！", true));
}
catch (Exception ex)
{
try
{
if (trans != null)
{
trans.Rollback();
}
}
catch { }
return(new OperationResult <bool>(OperationResultType.Error, "异常结果：" + ex.Message, false));
}
}
