//获取后台员工列表
public DataTable GetEmployeeList(string sIsPage, string sSort, int iPageSize, int iPageIndex, int iDoCount, Tz888.Model.Sys.EmployeeInfoTab empModel)
{
return(dal.GetEmployeeList(sIsPage, sSort, iPageSize, iPageIndex, iDoCount, empModel));
}
//获取后台员工列表
public DataTable GetEmployeeList(string sIsPage, string sSort, int iPageSize, int iPageIndex, int iDoCount, Tz888.Model.Sys.EmployeeInfoTab empMode)
{
//@IsPage varchar(10), --是否分页
//@SuperStr varchar(8000),--超级查询语句，##不能为空，必须有超级查询语句
//@Sort varchar(1000), --排序 ##不能为空，必须有排序语句
//@PageSize int, --分页，每页数据数
//@PageIndex int, --页数
//@docount int --是否去统计总数
//新的查询语句
string sSuperStr = "select " +
"a.employeeid,a.loginname, a.employeename+'['+a.loginname+']' as username,c.deptname,b.worktypename,a.enable,d.tem ,a.worktype ,a.deptid " +
"from employeeinfotab as a " +
" left join setworktypetab as b on a.worktype=b.worktype " +
" left join setdeptinfotab as c on a.deptid=c.deptid " +
" left join system d on a.loginname=d.employeeid " +
" where 1=1 ";
//empMode.EmployeeName = tbLoginName.Value.Trim(); //账号
//empMode.DeptID = ddlDept.SelectedValue; //部门
//empMode.sRole = ddlRole.SelectedValue; //角色
//empMode.WorkType = ddlWorkType.SelectedValue; //岗位
//empMode.sStatus = ddlStatus.SelectedValue; //状态
if (empMode.EmployeeName != null && empMode.EmployeeName != "")
{
sSuperStr += " and (loginname like '%" + empMode.EmployeeName.Trim() + "%' or employeename like '%" + empMode.EmployeeName.Trim() + "%' or nickname like '%" + empMode.EmployeeName.Trim() + "%' )";
}
if (empMode.DeptID != null && empMode.DeptID != "")
{
sSuperStr += " and a.deptid='" + empMode.DeptID + "'";
}
if (empMode.WorkType != null && empMode.WorkType != "")
{
sSuperStr += " and a.worktype='" + empMode.WorkType + "'";
}
if (empMode.sStatus != null && empMode.sStatus != "2")
{
sSuperStr += " and a.enable=" + empMode.sStatus.Trim();
}
if (empMode.sRole != null & empMode.sRole.Trim() != "")
{
sSuperStr += " and loginname in(select employeeid from system where tem like '%" + empMode.sRole.Trim() + "%')";
}
DataTable dt = null;
SqlParameter[] par =
{
new SqlParameter("@ispage", SqlDbType.VarChar, 10),
new SqlParameter("@SuperStr", SqlDbType.VarChar, 8000),
new SqlParameter("@Sort", SqlDbType.VarChar, 1000),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@docount", SqlDbType.Int)
};
par[0].Direction = ParameterDirection.Input;
par[0].Value = sIsPage;
par[1].Direction = ParameterDirection.Input;
par[1].Value = sSuperStr;
par[2].Direction = ParameterDirection.Input;
par[2].Value = sSort;
par[3].Direction = ParameterDirection.Input;
par[3].Value = iPageSize;
par[4].Direction = ParameterDirection.Input;
par[4].Value = iPageIndex;
par[5].Direction = ParameterDirection.Input;
par[5].Value = iDoCount;
try
{
dt = DbHelperSQL.RunProcedure("SP_SuperQuery", par, "mytable").Tables[0];
}
catch (Exception err)
{
throw err;
}
finally
{
}
return(dt);
}
