/// <summary> /// 获取实体分页 /// </summary> public DataTable GetHolidaysTableList(HolidaysTO TO, int pageIndex, int pageSize, string orderBy, out int rowCount) { string table = " HolidaysTable left join UserInfo on HolidaysTable.UserCode=UserInfo.UserCode "; string pk = " ID "; string fields = " ID, HolidaysTable.UserCode,UserInfo.UserName, startTime,endTime, DayCount, HolidaysType, Remark,UserInfo.AnnualLeave "; string filter = " UserInfo.UserStatus=1 "; #region 组织查询条件 if (!string.IsNullOrEmpty(TO.UserName)) { filter += string.Format(" and UserName like '%{0}%' ", StringHelper.SQLFilter(TO.UserName)); } if (TO.HolidaysType != 0) { filter += string.Format(" and HolidaysType={0} ", TO.HolidaysType); } if (!string.IsNullOrEmpty(TO.StratTime)) { filter += string.Format(" and '{0}'<startTime ", TO.StratTime + " 00:00:00 "); } if (!string.IsNullOrEmpty(TO.EndTime)) { filter += string.Format(" and '{0}'>endTime ", TO.EndTime + " 23:59:59 "); } #endregion string sort = " MC ASC ";//排序 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, "HolidaysTableList"); rowCount = (int)parameters[7].Value; return(ds.Tables[0]); }
/// <summary> /// 假期统计信息 /// </summary> public DataTable GetHolidaysStatisInfor(HolidaysTO TO, int pageIndex, int pageSize, string orderBy, out int rowCount) { string table = " UserInfo left join (select UserCode,sum(case HolidaysType when 1 then DayCount else 0 end) nianjia,sum(case HolidaysType when 2 then DayCount else 0 end) daoxiu,sum(case HolidaysType when 3 then DayCount else 0 end) jiaban from HolidaysTable group by UserCode)a on a.UserCode=UserInfo.UserCode "; string pk = " ID "; string fields = " UserInfo.UserCode,UserInfo.UserName, isnull(a.nianjia,0) nianjia,isnull(a.daoxiu,0) daoxiu ,isnull(a.jiaban,0) jiaban,UserInfo.AnnualLeave "; string filter = " UserInfo.UserStatus=1 "; #region 组织查询条件 if (!string.IsNullOrEmpty(TO.UserCode) && TO.UserCode != "0") { filter += string.Format(" and UserInfo.UserCode='{0}' ", StringHelper.SQLFilter(TO.UserCode)); } #endregion string sort = " UserInfo.UserCode ASC ";//排序 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, "HolidaysTableList"); rowCount = (int)parameters[7].Value; return(ds.Tables[0]); }
private void StatisHoliday() { #region 假期统计 double yearDay = 0; //年假 double swoppedDay = 0; //调休天数 double workDay = 0; //加班天数 double yearLeave = 0; HolidaysTO to = new HolidaysTO(); string orderBy = " UserName "; if (!string.IsNullOrEmpty(Request.Form["slectCode"])) { userCode = to.UserCode = Request.Form["slectCode"]; } DataTable dt = new HolidaysBLL().GetHolidaysStatisInfor(to, AspNetPager1.CurrentPageIndex, pageSize, orderBy, out rowCount); StringBuilder sb = new StringBuilder(); if (dt != null) { for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; yearDay = Convert.ToDouble(dr["AnnualLeave"]); //1年假 2 倒休 3加班 swoppedDay = Convert.ToDouble(dr["daoxiu"]); workDay = Convert.ToDouble(dr["jiaban"]); yearLeave = Convert.ToDouble(dr["nianjia"]); sb.Append("<tr>"); sb.AppendFormat("<td align='center'>{0}</td>", i + 1 * (pageSize * (pageIndex - 1) + 1)); sb.AppendFormat("<td align='center'>{0}</td>", dr["UserName"]); sb.AppendFormat("<td align='center'>{0}</td>", Convert.ToDouble(dr["AnnualLeave"])); sb.AppendFormat("<td align='center'>{0}</td>", workDay); sb.AppendFormat("<td align='center'>{0}</td>", swoppedDay * -1); sb.AppendFormat("<td align='center'>{0}</td>", yearLeave < 0 ? yearLeave * -1 : yearLeave); sb.AppendFormat("<td align='center'>{0}</td>", (yearDay + workDay + swoppedDay + yearLeave) < 0 ? 0 : yearDay + workDay + swoppedDay + yearLeave); } } holidayList = sb.ToString(); AspNetPager1.RecordCount = rowCount; AspNetPager1.PageSize = pageSize; #endregion }
/// <summary> /// 绑定数据源 /// </summary> private void BindHolidaysList() { HolidaysBLL BLL = new HolidaysBLL(); HolidaysTO to = new HolidaysTO(); int numPerPage = 10;//每页显示数量 string orderBy = " ID desc "; int rowCount = 0; #region 择条件 if (!string.IsNullOrEmpty(txtUserName.Text)) { to.UserName = txtUserName.Text; } if (!string.IsNullOrEmpty(HolidaysType.SelectedValue) && HolidaysType.SelectedValue != "请选择") { to.HolidaysType = PublicEnum.GetEnumDescriptionvalue <PublicEnum.HolidaysType>(HolidaysType.SelectedValue); } if (!string.IsNullOrEmpty(txtStartDate.Text)) { to.StratTime = txtStartDate.Text; } if (!string.IsNullOrEmpty(txtEndDate.Text)) { to.EndTime = txtEndDate.Text; } #endregion DataTable dt = BLL.GetHolidaysTableList(to, AspNetPager1.CurrentPageIndex, numPerPage, orderBy, out rowCount); if (dt != null && dt.Rows.Count > 0) { RepeaterHoliday.DataSource = dt; } else { RepeaterHoliday.DataSource = new DataTable(); } AspNetPager1.RecordCount = rowCount; AspNetPager1.PageSize = numPerPage; RepeaterHoliday.DataBind(); }
/// <summary> /// 假期统计信息 /// </summary> public DataTable GetHolidaysStatisInfor(HolidaysTO TO, int pageIndex, int pageSize, string orderBy, out int rowCount) { return(service.GetHolidaysStatisInfor(TO, pageIndex, pageSize, orderBy, out rowCount)); }