/// <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));
}
