public bool EditRoleMenu(Hashtable ht)
{
using (SqlTransaction trans = SQLHelp.BeginTransaction())
{
try
{
StringBuilder sbSql4org = new StringBuilder();
List <SqlParameter> List = new List <SqlParameter>();
sbSql4org.Append("delete from RoleOfMenu where RoleId=@RoleId");
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@RoleId", ht["RoleId"].ToString())
};
int number = SQLHelp.ExecuteNonQuery(trans, CommandType.Text, sbSql4org.ToString(), param);
if (!string.IsNullOrWhiteSpace(Convert.ToString(ht["MenuIds"])))
{
string[] MenuIds = ht["MenuIds"].ToString().Split(',');
StringBuilder sbSql4org_ins = new StringBuilder();
sbSql4org_ins.Append(" INSERT INTO RoleOfMenu(RoleId,MenuId)");
for (int i = 0; i < MenuIds.Length; i++)
{
if (!string.IsNullOrWhiteSpace(MenuIds[i]))
{
if (i + 1 == MenuIds.Length)
{
sbSql4org_ins.Append(" SELECT @RoleId" + i + ",@MenuId" + i);
}
else
{
sbSql4org_ins.Append(" SELECT @RoleId" + i + ",@MenuId" + i + " union");
}
List.Add(new SqlParameter("@RoleId" + i, ht["RoleId"].ToString()));
List.Add(new SqlParameter("@MenuId" + i, MenuIds[i]));
}
}
int num = SQLHelp.ExecuteNonQuery(trans, CommandType.Text, sbSql4org_ins.ToString(), List.ToArray());
if (num > 0)
{
trans.Commit();
return(true);
}
else
{
trans.Rollback();
return(false);
}
}
else
{
trans.Commit();
return(true);
}
}
catch (Exception ex)
{
trans.Rollback();
return(false);
}
}
}
public int SendMessage(Hashtable ht, List <Com_Message> list)
{
using (SqlTransaction trans = SQLHelp.BeginTransaction())
{
try
{
StringBuilder sbSql4org = new StringBuilder();
List <SqlParameter> spList = new List <SqlParameter>();
sbSql4org.Append("INSERT INTO Com_Message(Title,Contents,[Type],IsDelete,CreateTime,CreateUID,Receiver,Href,Status,IsSend,ReceiverEmail,CreateName,ReceiverName,Timing,FilePath)");
for (int i = 0; i < list.Count; i++)
{
if (i + 1 == list.Count)//最后一条
{
sbSql4org.Append(@" select @Title" + i + ",@Contents" + i + ",@Type" + i + ",@IsDelete" + i + ",@CreateTime" + i + ",@CreateUID" + i + ",@Receiver" + i + ",@Href" + i + ",@Status" + i + ",@IsSend" + i + ",@ReceiverEmail" + i + ",@CreateName" + i + ",@ReceiverName" + i + ",@Timing" + i + ",@FilePath" + i);
}
else
{
sbSql4org.Append(@" select @Title" + i + ",@Contents" + i + ",@Type" + i + ",@IsDelete" + i + ",@CreateTime" + i + ",@CreateUID" + i + ",@Receiver" + i + ",@Href" + i + ",@Status" + i + ",@IsSend" + i + ",@ReceiverEmail" + i + ",@CreateName" + i + ",@ReceiverName" + i + ",@Timing" + i + ",@FilePath" + i + " union all ");
}
spList.Add(new SqlParameter("@Title" + i, ht["Title"].ToString()));
string href = "";
if (!string.IsNullOrWhiteSpace(list[i].Href))
{
href = "<br/><h3><a href=" + list[i].Href + ">点击此处可查看详细信息</a></h3>";
}
spList.Add(new SqlParameter("@Contents" + i, ht["Contents"].ToString() + href));
spList.Add(new SqlParameter("@Type" + i, ht["Type"].ToString()));
spList.Add(new SqlParameter("@IsDelete" + i, ((int)SysStatus.正常).ToString()));
spList.Add(new SqlParameter("@CreateTime" + i, ht["CreateTime"].ToString()));
spList.Add(new SqlParameter("@CreateUID" + i, ht["CreateUID"].ToString()));
spList.Add(new SqlParameter("@Receiver" + i, list[i].Receiver));
spList.Add(new SqlParameter("@Href" + i, list[i].Href));
spList.Add(new SqlParameter("@Status" + i, ((int)MessageStatus.未读).ToString()));
spList.Add(new SqlParameter("@IsSend" + i, ((int)MessageIsSend.未发送).ToString()));
spList.Add(new SqlParameter("@ReceiverEmail" + i, list[i].ReceiverEmail));
spList.Add(new SqlParameter("@CreateName" + i, ht["CreateName"].ToString()));
spList.Add(new SqlParameter("@ReceiverName" + i, list[i].ReceiverName));
spList.Add(new SqlParameter("@Timing" + i, ht["Timing"].ToString()));
spList.Add(new SqlParameter("@FilePath" + i, Convert.ToString(ht["FilePath"])));
}
int number = SQLHelp.ExecuteNonQuery(trans, System.Data.CommandType.Text, sbSql4org.ToString(), spList.ToArray());
if (number > 0)
{
trans.Commit();
return(number);
}
else
{
trans.Rollback();
return(-1);
}
}
catch (Exception ex)
{
trans.Rollback();
return(-1);
}
}
}
public bool DelVote(Hashtable ht)
{
using (SqlTransaction tans = SQLHelp.BeginTransaction())
{
try
{
string sql = "delete from System_Vote where Id=@Id";
if (ht.ContainsKey("Id") && !string.IsNullOrWhiteSpace(ht["Id"].SafeToString()))
{
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@Id", ht["Id"].ToString())
};
int number = SQLHelp.ExecuteNonQuery(sql, CommandType.Text, param);
if (number < 0)
{
tans.Rollback();
}
sql = "select * from System_VoteDetails where VoteId=@Id";
SqlParameter[] param_sel = new SqlParameter[] {
new SqlParameter("@Id", ht["Id"].ToString())
};
DataTable dt = SQLHelp.ExecuteDataTable(sql, CommandType.Text, param_sel);
sql = "delete from System_VoteDetails where VoteId=@Id";
SqlParameter[] param_del = new SqlParameter[] {
new SqlParameter("@Id", ht["Id"].ToString())
};
int num = SQLHelp.ExecuteNonQuery(sql, CommandType.Text, param_del);
if (dt != null && dt.Rows.Count > 0)
{
string detailsId = "";
foreach (DataRow row in dt.Rows)
{
detailsId += row["Id"].ToString() + ",";
}
if (detailsId.Length > 0)
{
detailsId = detailsId.Substring(0, detailsId.Length - 1);
sql = "delete from System_VoteRecord where DetailsId in (" + detailsId + ")";
int ok = SQLHelp.ExecuteNonQuery(sql, CommandType.Text, null);
tans.Commit();
return(true);
}
}
else
{
tans.Commit();
return(true);
}
}
return(false);
}
catch (Exception)
{
tans.Rollback();
return(false);
}
}
}
public int ReaderMessage(Hashtable ht)
{
using (SqlTransaction trans = SQLHelp.BeginTransaction())
{
try
{
StringBuilder sbSql4org = new StringBuilder();
if (ht.ContainsKey("Status") && !string.IsNullOrWhiteSpace(ht["Status"].ToString()))
{
sbSql4org.Append("update Com_Message set [Status]=" + ht["Status"].ToString() + " where Id in (");
}
else if (ht.ContainsKey("IsDelete") && !string.IsNullOrWhiteSpace(ht["IsDelete"].ToString()))
{
sbSql4org.Append("update Com_Message set [IsDelete]=" + ht["IsDelete"].ToString() + " where Id in (");
}
List <SqlParameter> list = new List <SqlParameter>();
if (!string.IsNullOrWhiteSpace(ht["ids"].ToString()))
{
var ids = ht["ids"].ToString().Split(',');
var strsql = "";
for (int i = 0; i < ids.Length; i++)
{
strsql += "@ids" + i + ",";
list.Add(new SqlParameter("@ids" + i, ids[i]));
}
if (!string.IsNullOrWhiteSpace(strsql))
{
strsql = strsql.Substring(0, strsql.Length - 1);
}
sbSql4org.Append(strsql + ")");
if (ht.ContainsKey("Receiver") && !string.IsNullOrEmpty(ht["Receiver"].ToString()))
{
sbSql4org.Append(" and Receiver=@Receiver");
list.Add(new SqlParameter("@Receiver", ht["Receiver"].ToString()));
}
int number = SQLHelp.ExecuteNonQuery(trans, System.Data.CommandType.Text, sbSql4org.ToString(), list.ToArray());
if (number > 0)
{
trans.Commit();
return(number);
}
else
{
trans.Rollback();
return(-1);
}
}
}
catch (Exception)
{
trans.Rollback();
return(-1);
}
}
return(0);
}
public void sendEmail(int type)
{
using (SqlTransaction trans = SQLHelp.BeginTransaction())
{
try
{
string sql = "select * from System_Message where isSend=" + (int)isSend.未发送 + " and IsDelete!=" + (int)SysStatus.除 + " and [Type]=" + type + " and Timing=" + (int)MessageTiming.定时发送;
DataTable dt = SQLHelp.ExecuteDataTable(sql, CommandType.Text);
string ids = "";
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
DateTime pushDate = Convert.ToDateTime(row["CreateTime"]);
DateTime curDate = DateTime.Now;
TimeSpan ts = pushDate - curDate;
if (ts.Minutes <= 2)
{
ids += row["Id"].ToString() + ",";
}
if (!string.IsNullOrWhiteSpace(ids) && ids.Length > 0)
{
ids = ids.Substring(0, ids.Length - 1);
string upSql = "update System_Message set isSend=" + (int)isSend.已发送 + " where Id in (" + ids + ")";
int number = SQLHelp.ExecuteNonQuery(upSql, CommandType.Text);
trans.Commit();
}
}
foreach (DataRow row in dt.Rows)
{
string Subject = string.IsNullOrWhiteSpace(Convert.ToString(row["Title"])) ? "无题" : row["Title"].ToString();
string Body = string.IsNullOrWhiteSpace(Convert.ToString(row["Contents"])) ? "无内容" : row["Contents"].ToString();
if (!string.IsNullOrWhiteSpace(Convert.ToString(row["ReceiverEmail"])))
{
string href = "";
if (!string.IsNullOrWhiteSpace(Convert.ToString(row["CreatorName"])))
{
Body += "<br/><h4>发件人：" + row["CreatorName"].ToString() + "</h4>";
}
if (!string.IsNullOrWhiteSpace(Convert.ToString(row["Href"])))
{
href = "<br/><h3><a href=" + row["Href"].ToString() + ">点击此处可查看详细信息</a></h3>";
}
SendMailMessage.SendMessage(Subject, Body + href, row["ReceiverEmail"].ToString());
}
}
}
}
catch (Exception ex)
{
_logger.Info("EmailPost：" + ex.Message);
trans.Rollback();
}
}
}
public bool DelRole(Hashtable ht)
{
using (SqlTransaction trans = SQLHelp.BeginTransaction())
{
try
{
StringBuilder sbSql4org = new StringBuilder();
if (ht.ContainsKey("Id") && !string.IsNullOrWhiteSpace(ht["Id"].SafeToString()))
{
sbSql4org.Append("delete from RoleInfo where Id=@Id");
SqlParameter[] param = new SqlParameter[] { new SqlParameter("@Id", ht["Id"].SafeToString()) };
int number = SQLHelp.ExecuteNonQuery(trans, System.Data.CommandType.Text, sbSql4org.ToString(), param);
if (number > 0)
{
StringBuilder sbSql4org2 = new StringBuilder();
sbSql4org2.Append("delete from RoleOfMenu where RoleId=@RoleId");
SqlParameter[] param2 = new SqlParameter[] { new SqlParameter("@RoleId", ht["Id"].SafeToString()) };
int num = SQLHelp.ExecuteNonQuery(trans, System.Data.CommandType.Text, sbSql4org2.ToString(), param2);
trans.Commit();
return true;
}
else
{
trans.Rollback();
return false;
}
}
return false;
}
catch (Exception)
{
trans.Rollback();
return false;
}
}
}
/// <summary>
/// 获得事务对象
/// </summary>
/// <returns></returns>
public SqlTransaction GetTran()
{
return(SQLHelp.BeginTransaction());
}