/// <summary>
/// 获取实体分页
/// </summary>
public DataTable GetBulletinList(BulletinTO TO, int pageIndex, int pageSize, string orderBy, out int rowCount)
{
string table = " [Bulletin] ";
string pk = " ID ";
string fields = " * ";
string filter = "";// string.Format(" Status={0} ", ConstantsManager.JiLuZhuangTai.Normal);
#region 组织查询条件
//if (!string.IsNullOrEmpty(TO.MC))
//{
// filter += string.Format(" and MC like '%{0}%' ", StringHelper.SQLFilter(TO.MC));
//}
filter += " isdel=1 ";
if (!string.IsNullOrEmpty(TO.Title))
{
filter += string.Format(" and Title like '%{0}%' ", StringHelper.SQLFilter(TO.Title));
}
#endregion
string sort = " ID DESC ";//排序
if (!string.IsNullOrEmpty(orderBy))
{
sort = orderBy;
}
SqlParameter[] parameters =
{
new SqlParameter("@Tables", SqlDbType.VarChar, 1000),
new SqlParameter("@PK", SqlDbType.VarChar, 100),
new SqlParameter("@Fields", SqlDbType.VarChar, 1000),
new SqlParameter("@Pageindex", SqlDbType.Int),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@Filter", SqlDbType.VarChar, 1000),
new SqlParameter("@Sort", SqlDbType.VarChar, 200),
new SqlParameter("@RowCount", SqlDbType.Int)
};
parameters[0].Value = table;
parameters[1].Value = pk;
parameters[2].Value = fields;
parameters[3].Value = pageIndex;
parameters[4].Value = pageSize;
parameters[5].Value = filter;
parameters[6].Value = sort;
parameters[7].Direction = ParameterDirection.Output;
DataSet ds = SqlHelper.RunProcedure("SP_DividePage", parameters, "BulletinList");
rowCount = (int)parameters[7].Value;
return(ds.Tables[0]);
}
/// <summary>
/// 获取参数
/// </summary>
private BulletinTO GetCriteria()
{
BulletinTO criteria = new BulletinTO();
if (this.txtStartDate.Text.Trim() != "")
{
criteria.StartDt = Convert.ToDateTime(this.txtStartDate.Text.Trim());
}
if (this.txtEndDate.Text.Trim() != "")
{
criteria.EndDt = Convert.ToDateTime(this.txtEndDate.Text.Trim());
}
criteria.Title = this.txtTitle.Text.Trim();
criteria.PubDepId = Check.GetInt32(ddlDep.SelectedValue);
return(criteria);
}
private void Bind()
{
//int pagecount = new DAL.Bulletin().GetProcListCountByPK("Bulletin","");
//IList<Models.Bulletin> list = new List<Models.Bulletin>();
//if (pagecount > 0)
// list = new DAL.Bulletin().GetBulletinByList();
//AspNetPager1.RecordCount = pagecount;
//AspNetPager1.PageSize = pageSize;
//this.Repeater1.DataSource = list;
//this.Repeater1.DataBind();
#region 分页相关
//此处正常情况下不需要更改
int _pageNum = 1;//当前页
if (!string.IsNullOrEmpty(Request.Form["pageNum"]))
{
_pageNum = int.Parse(Request.Form["pageNum"]);
parm.pageNum = _pageNum;
}
if (!string.IsNullOrEmpty(Request.Form["numPerPage"]))
{
parm.numPerPage = int.Parse(Request.Form["numPerPage"]); //每页显示条数(默认20)
}
#endregion
#region 查询
/***********查询条件*********************/
BulletinTO sto = new BulletinTO();
if (!string.IsNullOrEmpty(Request.Form["Title"]))
{
parm.DeptName = sto.Title = Request.Form["Title"];
}
//if (!string.IsNullOrEmpty(Request.Form["DeptCode"]))
// parm.DeptCode = sto.DeptCode = Request.Form["DeptCode"];
//if (!string.IsNullOrEmpty(Request.Form["DeptID"]))
//{
// parm.DeptID = sto.DeptID = Request.Form["DeptID"];
// if (sto.DeptID == "0") sto.DeptID = "";
//}
//else
// parm.DeptID = "0";
#endregion
#region 排序
/************排序*************************/
string columName = "";//排序的列明
/***********排序字段*********************/
if (!string.IsNullOrEmpty(Request.Form["orderField"]))
{
if (string.IsNullOrEmpty(Request.Form["orderDirection"]))
{
parm.orderDirection = " asc ";
}
else
{
parm.orderDirection = Request.Form["orderDirection"];
}
parm.orderField = Request.Form["orderField"];
if (Request.Form["orderField"] == "DeptName")//xiaoqu order
{
parm.DeptNameOrder = parm.orderDirection;
columName = " DeptName "; //需要加表别名防止重名报错
}
else if (Request.Form["orderField"] == "DeptCode") //jigou mc order
{
parm.DeptCodeOrder = parm.orderDirection;
columName = " DeptCode ";
}
}
string orderBy = "";
if (!string.IsNullOrEmpty(Request.Form["orderField"]))
{
orderBy = columName + " " + parm.orderDirection;
}
else
{
parm.DeptCodeOrder = "asc";
}
#endregion
#region 列表
StringBuilder sbd = new StringBuilder();
//查询列表
BulletinBLL bulletinBLL = new BulletinBLL();
DataTable dt = bulletinBLL.GetBulletinList(sto, parm.pageNum, parm.numPerPage, orderBy, out parm.totalCount);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
//sbd.AppendFormat("<tr target=\"sid_user\" rel=\"{0}\">", dr["DeptID"]);
//sbd.AppendFormat("<td>{0}</td>", (i + 1) + (_pageNum - 1) * parm.numPerPage);//编号
//sbd.AppendFormat("<td>{0}</td>", dr["DeptCode"]);
//sbd.AppendFormat("<td>{0}</td>", dr["DeptName"]);
//sbd.Append("</tr>");
//sbd.AppendFormat("<div id=\"u68\" class=\"u68_container\" >");
//sbd.AppendFormat("<div id=\"u68_img\" >");
//sbd.AppendFormat("<img src=\"Resoures/Bulletin/u63_normal.gif\" class=\"raw_image\"></div>");
//sbd.AppendFormat("<div id=\"u69\" class=\"u69\" style=\"visibility:hidden;\" >");
//sbd.AppendFormat("<div id=\"u69_rtf\"></div>");
//sbd.AppendFormat("</div>");
//sbd.AppendFormat("</div>");
//sbd.AppendFormat("<div id=\"u70\" class=\"u70\" >");
//sbd.AppendFormat("<div id=\"u70_rtf\"><p style=\"text-align:left;\"><span style=\"font-family:Arial;font-size:14px;font-weight:bold;font-style:normal;text-decoration:none;color:#2C629E;\">关于中秋放假休息的通知</span><span style=\"font-family:Arial;font-size:14px;font-weight:bold;font-style:normal;text-decoration:none;color:#2C629E;\"> </span><span style=\"font-family:Arial;font-size:14px;font-weight:bold;font-style:normal;text-decoration:none;color:#999999;\"> </span></p></div>");
//sbd.AppendFormat("</div>");
//sbd.AppendFormat("<div id=\"u71\" class=\"u71\" >");
//sbd.AppendFormat("<div id=\"u71_rtf\"><p style=\"text-align:left;\"><span style=\"font-family:Arial;font-size:13px;font-weight:normal;font-style:normal;text-decoration:none;color:#333333;\">根据国务院《关于修改<全国年节及纪念日放假办法>的决定》，2013年9月19日至9月21日放假</span><span style=\"font-family:Arial;font-size:13px;font-weight:normal;font-style:normal;text-decoration:none;color:#333333;\">共</span><span style=\"font-family:Arial;font-size:13px;font-weight:normal;font-style:normal;text-decoration:none;color:#333333;\">3</span><span style=\"font-family:Arial;font-size:13px;font-weight:normal;font-style:normal;text-decoration:none;color:#333333;\">天。其中9月19日(星期四，农历中秋节)法定节假日，9月21日(星期六)公休日，9月22日(星期日)公休调至9月20日，9月22日(星期日)照常上班。</span></p></div>");
//sbd.AppendFormat("</div><div id=\"u72\" class=\"u72\" >");
//sbd.AppendFormat("<DIV id=\"u72_line\" class=\"u72_line\" ></DIV>");
//sbd.AppendFormat("</div>");
sbd.AppendFormat("<div style='width:100%;'>{0}</div>", dr["Title"]);
}
parm.DataRowList = sbd.ToString();
#endregion
}
/// <summary>
/// 获取实体分页
/// 后台使用
/// </summary>
public DataTable GetBulletinListByBack(BulletinTO TO, int pageIndex, int pageSize, string orderBy, out int rowCount)
{
string table = " Bulletin b left join UserInfo u on b.PublishUserCode=u.UserCode left join Department d on u.DepartmentId=d.Id ";
string pk = " b.ID ";
string fields = " b.Id,b.PublishUserCode,b.Title,b.Contents,b.PublishTime,u.UserName as PubName,d.RoleName as PubDep ";
string filter = "";// string.Format(" Status={0} ", ConstantsManager.JiLuZhuangTai.Normal);
#region 组织查询条件
//if (!string.IsNullOrEmpty(TO.MC))
//{
// filter += string.Format(" and MC like '%{0}%' ", StringHelper.SQLFilter(TO.MC));
//}
filter += " b.isdel=1 ";
if (!string.IsNullOrEmpty(TO.Title))
{
filter += string.Format(" and b.Title like '%{0}%' ", StringHelper.SQLFilter(TO.Title));
}
if (!string.IsNullOrEmpty(TO.PubName))
{
filter += string.Format(" and u.PubName like '%{0}%' ", StringHelper.SQLFilter(TO.PubName));
}
if (TO.PubDepId > 0)
{
filter += string.Format(" and d.id={0} ", TO.PubDepId);
}
//根据开始日期进行选择
if (TO.StartDt.HasValue)
{
DateTime beginDt = DateTime.Parse(TO.StartDt.Value.ToShortDateString());
filter += string.Format(" and b.PublishTime>='{0}' ", beginDt);
}
//根据结束日期进行选择
if (TO.EndDt.HasValue)
{
var endDate = DateTime.Parse(TO.EndDt.Value.ToShortDateString() + " 23:59:59");
filter += string.Format(" and b.PublishTime<='{0}' ", endDate);
}
#endregion
string sort = " b.ID DESC ";//排序
if (!string.IsNullOrEmpty(orderBy))
{
sort = orderBy;
}
SqlParameter[] parameters =
{
new SqlParameter("@Tables", SqlDbType.VarChar, 1000),
new SqlParameter("@PK", SqlDbType.VarChar, 100),
new SqlParameter("@Fields", SqlDbType.VarChar, 1000),
new SqlParameter("@Pageindex", SqlDbType.Int),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@Filter", SqlDbType.VarChar, 1000),
new SqlParameter("@Sort", SqlDbType.VarChar, 200),
new SqlParameter("@RowCount", SqlDbType.Int)
};
parameters[0].Value = table;
parameters[1].Value = pk;
parameters[2].Value = fields;
parameters[3].Value = pageIndex;
parameters[4].Value = pageSize;
parameters[5].Value = filter;
parameters[6].Value = sort;
parameters[7].Direction = ParameterDirection.Output;
DataSet ds = SqlHelper.RunProcedure("SP_DividePage", parameters, "BulletinList");
rowCount = (int)parameters[7].Value;
return(ds.Tables[0]);
}
/// <summary>
/// 获得数据列表分页
/// </summary>
/// <returns></returns>
public DataTable GetBulletinList(BulletinTO TO, int pageIndex, int pageSize, string orderBy, out int rowCount)
{
return(service.GetBulletinList(TO, pageIndex, pageSize, orderBy, out rowCount));
}