//取序号的最大值
public static int maxNoticeID(Dal.Models.Notice notice, OleDbConnection conn, OleDbTransaction tran)
{
string strSql = "select max(NoticeID) from Notice where ActivityID=" + notice.ActivityID;
string currentNoticeID = Dal.OleDbHlper.ExecuteScalar(strSql, conn, CommandType.Text, tran).ToString();
currentNoticeID = currentNoticeID == "" ? "0" : currentNoticeID;
return(Convert.ToInt32(currentNoticeID));
}
public static int CreateNotice(Dal.Models.Notice notice, int iActivityID, OleDbConnection conn, OleDbTransaction tran)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append("INSERT INTO NOTICE ( ");
sbSql.Append(" ActivityID");
sbSql.Append(" ,Title");
sbSql.Append(" ,NoticeContent");
sbSql.Append(" ,EffectTime");
sbSql.Append(" ,Ordinal");
sbSql.Append(" ,LastEditTime");
sbSql.Append(" ,Clicks )");
sbSql.Append(" VALUES ( ?, ?, ?, ?, ?,? ,?)");
int iResult = Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@ActivityID", OleDbType.Integer)
{
Value = notice.ActivityID
}
, new OleDbParameter("@Title", OleDbType.VarWChar)
{
Value = notice.Title
}
, new OleDbParameter("@NoticeContent", OleDbType.VarWChar)
{
Value = notice.NoticeContent
}
, new OleDbParameter("@EffectTime", OleDbType.DBDate)
{
Value = notice.EffectTime
}
, new OleDbParameter("@Ordinal", OleDbType.Integer)
{
Value = notice.Ordinal
}
, new OleDbParameter("@LastEditTime", OleDbType.DBDate)
{
Value = notice.CreateTime
}
, new OleDbParameter("@Clicks", OleDbType.Integer)
{
Value = 0
});
if (iResult != 1)
{
throw new Exception("添加公告失败!");
}
else
{
int noticeID = BLL.Notice.maxNoticeID(notice, conn, tran);
notice.NoticeID = noticeID;
SetNoticeOrdinal(notice, conn, tran);
return(noticeID);
}
}
public static int UpdateNotice(Dal.Models.Notice notice, int NoticeID, OleDbConnection conn, OleDbTransaction tran)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append(" Update NOTICE ");
sbSql.Append(" set ActivityID=?,");
sbSql.Append(" Title=?,");
sbSql.Append(" NoticeContent=?,");
sbSql.Append(" EffectTime=?,");
sbSql.Append(" Ordinal=?,");
sbSql.Append(" LastEditTime=?");
sbSql.Append(" where NoticeID=?");
int iResult = Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@ActivityID", OleDbType.Integer)
{
Value = notice.ActivityID
}
, new OleDbParameter("@Title", OleDbType.VarWChar)
{
Value = notice.Title
}
, new OleDbParameter("@NoticeContent", OleDbType.VarWChar)
{
Value = notice.NoticeContent
}
, new OleDbParameter("@EffectTime", OleDbType.DBDate)
{
Value = notice.EffectTime
}
, new OleDbParameter("@Ordinal", OleDbType.Integer)
{
Value = notice.Ordinal
}
, new OleDbParameter("@LastEditTime", OleDbType.DBDate)
{
Value = notice.CreateTime
}
, new OleDbParameter("@NoticeID", OleDbType.Integer)
{
Value = NoticeID
});
if (iResult != 1)
{
throw new Exception("修改失败!");
}
else
{
notice.NoticeID = NoticeID;
SetNoticeOrdinal(notice, conn, tran);
return(iResult);
}
}
public void InitPage(int id)
{
OleDbConnection conn = new OleDbConnection(Dal.OleDbHlper.ConnectionString);
conn.Open();
OleDbTransaction tran = conn.BeginTransaction();
Dal.Models.Notice notice = BLL.Notice.GetNotice(id, conn, tran);
txtTitle.Text = notice.Title;
txtNoticeContent.Text = notice.NoticeContent;
NoticeID.Value = id.ToString();
TbxOrdinal.Text = notice.Ordinal == null ? "" : notice.Ordinal.ToString();
lblTitle.Text = notice.Title;
lblDate.Text = Convert.ToDateTime(notice.LastEditTime).ToString("yyyy-MM-dd");
nContent.InnerHtml = notice.NoticeContent;
Dal.Models.UploadFileInfo dUploadFileInfo = BLL.UploadFileInfo.GetUploadFile(id, null, "NoticeAppendix", conn, tran);
if (dUploadFileInfo != null)
{
StringBuilder sbInnerHtml = new StringBuilder();
sbInnerHtml.Append(dUploadFileInfo.FileName);
sbInnerHtml.AppendFormat("<a class='DeleteNoticeAppendix' onclick='DeleteNoticeAppendix({0});return false;' title='删除' href=''>", id);
sbInnerHtml.Append("<i class='fa fa-trash-o'></i>");
sbInnerHtml.Append("</a>");
sbInnerHtml.AppendFormat(" <a class='DownloadNoticeAppendix' onclick=\"DownloadAppendix({0});return false;\" title='下载' href=''>", id);
sbInnerHtml.Append("<i class='fa fa-download'></i></a></td>");
tdUploadFileInfo.InnerHtml = sbInnerHtml.ToString();
tabLookUploadFileInfo.Visible = true;
tdLookUploadFileInfo.InnerHtml = sbInnerHtml.ToString();
}
else
{
tabLookUploadFileInfo.Visible = false;
tdUploadFileInfo.InnerHtml = "暂未上传附件！";
}
conn.Close();
hdMsg.Value = "";
}
public static int DelNotice(int iNoticeID, OleDbConnection conn, OleDbTransaction tran = null)
{
Dal.Models.Notice n = GetNotice(iNoticeID, conn, tran);
string strSql = "DELETE FROM NOTICE where NoticeID = ? ";
int iResult = Dal.OleDbHlper.ExecuteNonQuery(strSql, conn, CommandType.Text, tran
, new OleDbParameter("@NoticeID", OleDbType.Integer)
{
Value = iNoticeID
});
if (iResult != 1)
{
throw new Exception("删除失败！");
}
else
{
ResetOrdinal(n.ActivityID.Value, conn, tran);
return(iResult);
}
}
//设定公告顺序
public static void SetNoticeOrdinal(Dal.Models.Notice notice, OleDbConnection conn, OleDbTransaction trans = null)
{
if (notice == null || notice.NoticeID == null)
{
throw new Exception("参数错误!");
}
List <Dal.Models.Notice> lstNotice = GetNoticeList(notice.ActivityID.Value, conn, trans).Where(n => n.NoticeID != notice.NoticeID).ToList();
if (notice.Ordinal == null || notice.Ordinal > lstNotice.Count)
{
lstNotice.Add(notice);
}
else if (notice.Ordinal <= 0)
{
lstNotice.Insert(0, notice);
}
else
{
lstNotice.Insert(notice.Ordinal.Value - 1, notice);
}
string strSql = " UPDATE Notice set Ordinal = ? where NoticeID = ? ";
for (int i = 0; i < lstNotice.Count; i++)
{
Dal.OleDbHlper.ExecuteNonQuery(strSql, conn, CommandType.Text, trans
, new OleDbParameter("@Ordinal", OleDbType.Integer)
{
Value = i + 1
}
, new OleDbParameter("@NoticeID", OleDbType.Integer)
{
Value = lstNotice[i].NoticeID.Value
});
}
}
protected void lbkConserve_Click(object sender, EventArgs e)
{
OleDbConnection conn = new OleDbConnection(Dal.OleDbHlper.ConnectionString);
conn.Open();
OleDbTransaction tran = conn.BeginTransaction();
string strActivityType = Session["ActivityType"].ToString();
Dal.Models.Activity activity = BLL.Activity.GetActivity(strActivityType, conn, tran);
if (activity == null)
{
hdMsg.Value = "当前活动已关闭，请联系活动管理人员！";
return;
}
Dal.Models.Notice notice = new Dal.Models.Notice();
if (ViewState["NoticeID"] == null)
{
try
{
notice.ActivityID = Convert.ToInt32(activity.ActivityID);
notice.Title = txtTitle.Text.Trim();
notice.NoticeContent = txtNoticeContent.Text;
notice.CreateTime = DateTime.Now;
notice.EffectTime = DateTime.Now;//Convert.ToDateTime(txtStartDate.Text);
int iOrdinal = 0;
if (!string.IsNullOrEmpty(TbxOrdinal.Text) && Int32.TryParse(TbxOrdinal.Text, out iOrdinal))
{
notice.Ordinal = iOrdinal;//Convert.ToInt32(TbxOrdinal.Text);
}
ViewState["NoticeID"] = BLL.Notice.CreateNotice(notice, Convert.ToInt32(activity.ActivityID), conn, tran);
NoticeID.Value = ViewState["NoticeID"].ToString();
Dal.Models.UploadFileInfo dUploadFileInfo = BLL.UploadFileInfo.GetUploadFile(Convert.ToInt32(NoticeID.Value), null, "NoticeAppendix", conn, tran);
if (dUploadFileInfo != null)
{
StringBuilder sbInnerHtml = new StringBuilder();
sbInnerHtml.Append(dUploadFileInfo.FileName);
sbInnerHtml.AppendFormat("<a class='DeleteNoticeAppendix' onclick='DeleteNoticeAppendix({0});return false;' title='删除' href=''>", NoticeID.Value);
sbInnerHtml.Append("<i class='fa fa-trash-o'></i>");
sbInnerHtml.Append("</a>");
sbInnerHtml.AppendFormat(" <a class='DownloadNoticeAppendix' onclick=\"DownloadAppendix({0});return false;\" title='下载' href=''>", NoticeID.Value);
sbInnerHtml.Append("<i class='fa fa-download'></i></a></td>");
tdUploadFileInfo.InnerHtml = sbInnerHtml.ToString();
}
else
{
tdUploadFileInfo.InnerHtml = "暂未上传附件！";
}
tran.Commit();
hdMsg.Value = "发布成功！";
}
catch (Exception)
{
tran.Rollback();
hdMsg.Value = "发布失败！";
conn.Close();
return;
}
}
else
{
UpdateNoticeBack();
}
conn.Close();
}
