public void GetLeaguerUserList(HttpContext context, OleDbConnection conn)
{
string userName = context.Request["UserName"];
Dal.Models.PageInfo <Dal.Models.UserOrg> pgif = new Dal.Models.PageInfo <Dal.Models.UserOrg>();
int iPageSize = 10;
if (!Int32.TryParse(context.Request["PageSize"] ?? "50", out iPageSize))
{
iPageSize = 10;
}
int iCuttentPage = 1;
if (!Int32.TryParse(context.Request["CuttentPage"] ?? "0", out iCuttentPage))
{
iCuttentPage = 1;
}
pgif.CuttentPage = iCuttentPage;
pgif.PerPage = iPageSize;
Dal.Models.UserInfo user = (Dal.Models.UserInfo)context.Session["UserInfo"];
//List<Dal.Models.UserOrg> userlist = BLL.User.GetUserListAllInfo(Convert.ToInt32(user.UserID), userName, pgif, conn);
List <Dal.Models.UserOrg> userlist = BLL.User.GetLeaguerUserList(user.UserID.Value, userName, pgif, conn);
if (userlist == null)
{
pgif.CuttentPage = 1;
pgif.PerPage = 10;
pgif.RecordNumber = 0;
pgif.PagesCount = 0;
pgif.Table = new DataTable();
context.Response.Write(JsonConvert.SerializeObject(pgif));
}
else
{
//int iRecordNumber = userlist.Count;
//int iPagesCount = Convert.ToInt32(Math.Ceiling((float)iRecordNumber / (float)iPageSize));
//userlist = userlist.Skip(iPageSize * (iCuttentPage - 1)).Take(iPageSize).ToList();
DataTable dtuserlist = Dal.DataTableExtensions.ToDataTable(userlist);
dtuserlist.Columns.Add("UserTypeName");
foreach (DataRow row in dtuserlist.Rows)
{
row["UserTypeName"] = BLL.SysConstant.GetConstant(row["UserType"].ToString(), conn).ConstantValue;
}
//pgif.CuttentPage = iCuttentPage;
//pgif.PerPage = iPageSize;
//pgif.RecordNumber = iRecordNumber;
//pgif.PagesCount = iPagesCount;
pgif.Table = dtuserlist;
string strJson = JsonConvert.SerializeObject(pgif);
context.Response.Write(strJson);
}
}
public void GetExpertRevotePrizeList(HttpContext context, OleDbConnection conn)
{
string specialtys = context.Request["Specialtys"];
string prizeType = context.Request["PrizeType"];
string prizeName = context.Request["PrizeName"];
Dal.Models.UserInfo user = (Dal.Models.UserInfo)context.Session["UserInfo"];
Dal.Models.Activity activity = BLL.Activity.GetActivity(context.Session["ActivityType"].ToString(), conn);
if (activity == null)
{
context.Response.Write("活动未开始！");
}
List <Dal.Models.RevotePrize> lstPrize = null;
if (user.UserType == "0801" || user.UserType == "0802" || user.UserType == "0803")
{
//lstPrize = BLL.Prize.GetPrizeList(specialtys, prizeType, prizeName, conn);
}
else if (user.UserType == "0805")
{
// 专家所评审的项目对应的奖项
BLL.Revote bllRevote = new BLL.Revote();
lstPrize = bllRevote.GetExpertRevotePrize(user.UserID.Value, activity.ActivityID.Value, conn);
}
int iPageSize = 10;
if (!Int32.TryParse(context.Request["PageSize"] ?? "50", out iPageSize))
{
iPageSize = 10;
}
int iCuttentPage = 1;
if (!Int32.TryParse(context.Request["CuttentPage"] ?? "0", out iCuttentPage))
{
iCuttentPage = 1;
}
int iRecordNumber = lstPrize.Count;
int iPagesCount = Convert.ToInt32(Math.Ceiling((float)iRecordNumber / (float)iPageSize));
lstPrize = lstPrize.Skip(iPageSize * (iCuttentPage - 1)).Take(iPageSize).ToList();
Dal.Models.PageInfo <Dal.Models.Declaration> pgif = new Dal.Models.PageInfo <Dal.Models.Declaration>();
pgif.CuttentPage = iCuttentPage;
pgif.PerPage = iPageSize;
pgif.RecordNumber = iRecordNumber;
pgif.PagesCount = iPagesCount;
pgif.Table = Dal.DataTableExtensions.ToDataTable(lstPrize);
string strJson = JsonConvert.SerializeObject(pgif);
context.Response.Write(strJson);
conn.Close();
}
/// <summary>
///
/// </summary>
/// <param name="context"></param>
/// <param name="conn"></param>
public void GetExpertGroupList(HttpContext context, OleDbConnection conn)
{
string strSpecialtyID = context.Request["Specialtys"];
string strActivityType = context.Session["ActivityType"].ToString();
Dal.Models.Activity activity = BLL.Activity.GetActivity(strActivityType, conn);
if (activity == null)
{
context.Response.Write("当前活动已关闭，请联系活动管理人员！");
return;
}
int iPageSize = 10;
if (!Int32.TryParse(context.Request["PageSize"] ?? "50", out iPageSize))
{
iPageSize = 10;
}
int iCuttentPage = 1;
if (!Int32.TryParse(context.Request["CuttentPage"] ?? "0", out iCuttentPage))
{
iCuttentPage = 1;
}
Dal.Models.PageInfo <Dal.Models.Declaration> pgif = new Dal.Models.PageInfo <Dal.Models.Declaration>();
pgif.CuttentPage = iCuttentPage;
pgif.PerPage = iPageSize;
List <Dal.Models.ExpertGroup> lstExpertGroup = BLL.ExpertGroup.GetGroupList(activity.ActivityID.Value, strSpecialtyID, conn);
if (lstExpertGroup.Count > 0)
{
lstExpertGroup = lstExpertGroup.OrderByDescending(r => r.GroupID).ToList();
}
int iRecordNumber = lstExpertGroup.Count;
int iPagesCount = Convert.ToInt32(Math.Ceiling((float)iRecordNumber / (float)iPageSize));
lstExpertGroup = lstExpertGroup.Skip(iPageSize * (iCuttentPage - 1)).Take(iPageSize).ToList();
DataTable dtExpertGroup = Dal.DataTableExtensions.ToDataTable(lstExpertGroup);
pgif.RecordNumber = iRecordNumber;
pgif.PagesCount = iPagesCount;
pgif.Table = dtExpertGroup;
context.Response.Write(JsonConvert.SerializeObject(pgif));
}
/// <summary>
/// 管理用户页面：管理用户列表，返回的一个List
/// </summary>
/// <remarks>
/// 当前登录者为超级管理员 : 返回超级管理员、系统管理员、协会管理员、分会用户
/// 当前登录者为系统管理员 : 返回系统管理员、协会管理员、分会用户
/// 当前登录者为协会管理员 : 返回协会管理员、分会用户
/// 当前登录者为分会用户 : 返回当前登录用户
/// </remarks>
/// <param name="context"></param>
public void GetManagerList(HttpContext context, OleDbConnection conn)
{
string strUserName = context.Request["UserName"];
Dal.Models.UserInfo userLogin = (Dal.Models.UserInfo)context.Session["UserInfo"];
Dal.Models.PageInfo <Dal.Models.UserInfo> pgif = new Dal.Models.PageInfo <Dal.Models.UserInfo>();
List <Dal.Models.UserInfo> lstUser = BLL.User.GetManagerList(userLogin, strUserName, conn);
if (lstUser == null)
{
pgif.CuttentPage = 1;
pgif.PerPage = 10;
pgif.RecordNumber = 0;
pgif.PagesCount = 0;
pgif.Table = null;
context.Response.Write(JsonConvert.SerializeObject(pgif));
}
else
{
int iPageSize = 10;
if (!Int32.TryParse(context.Request["PageSize"] ?? "50", out iPageSize))
{
iPageSize = 10;
}
int iCuttentPage = 1;
if (!Int32.TryParse(context.Request["CuttentPage"] ?? "0", out iCuttentPage))
{
iCuttentPage = 1;
}
int iRecordNumber = lstUser.Count;
int iPagesCount = Convert.ToInt32(Math.Ceiling((float)iRecordNumber / (float)iPageSize));
lstUser = lstUser.Skip(iPageSize * (iCuttentPage - 1)).Take(iPageSize).ToList();
DataTable dtuserlist = Dal.DataTableExtensions.ToDataTable(lstUser);
pgif.CuttentPage = iCuttentPage;
pgif.PerPage = iPageSize;
pgif.RecordNumber = iRecordNumber;
pgif.PagesCount = iPagesCount;
pgif.Table = dtuserlist;
string strJson = JsonConvert.SerializeObject(pgif);
context.Response.Write(strJson);
}
}
public void GetSpecialtyExpertList(HttpContext context, OleDbConnection conn)
{
string strQuery = context.Request["strQuery"];
string strQueryStatus = context.Request["QueryStatus"];
int specialtyId = context.Request["SpecialtyID"] == null ? 0 : Convert.ToInt32(context.Request["SpecialtyID"]);
Dal.Models.UserInfo user = (Dal.Models.UserInfo)context.Session["UserInfo"];
List <Dal.Models.Expert> lstExpert = new List <Dal.Models.Expert>();
List <Dal.Models.Expert> listExpert = BLL.Expert.GetExpertList(conn, user, strQuery, strQueryStatus);
if (listExpert.Count > 0)
{
lstExpert = listExpert.Where(s => (s.SpecialtyIDs ?? "").Contains(specialtyId.ToString())).ToList();
}
int iPageSize = 10;
if (!Int32.TryParse(context.Request["PageSize"] ?? "50", out iPageSize))
{
iPageSize = 10;
}
int iCuttentPage = 1;
if (!Int32.TryParse(context.Request["CuttentPage"] ?? "0", out iCuttentPage))
{
iCuttentPage = 1;
}
int iRecordNumber = lstExpert.Count;
int iPagesCount = Convert.ToInt32(Math.Ceiling((float)iRecordNumber / (float)iPageSize));
lstExpert = lstExpert.Skip(iPageSize * (iCuttentPage - 1)).Take(iPageSize).ToList();
DataTable dtExpert = Dal.DataTableExtensions.ToDataTable(lstExpert);
Dal.Models.PageInfo <Dal.Models.Expert> pgif = new Dal.Models.PageInfo <Dal.Models.Expert>()
{
CuttentPage = iCuttentPage,
PagesCount = iPagesCount,
PerPage = iPageSize,
RecordNumber = iRecordNumber,
Table = dtExpert,
List = lstExpert
};
context.Response.Write(JsonConvert.SerializeObject(pgif));
}
public static List<T> GetList<T>(string strSql, Dal.Models.PageInfo<T> pageInfo, OleDbConnection conn, OleDbTransaction tran = null)
where T : Dal.Models.BaseEntity, new()
{
OleDbCommand cmd = new OleDbCommand("[dbo].[Proc_Pagination]", conn);
cmd.CommandType = CommandType.StoredProcedure;
if (tran != null)
{
cmd.Transaction = tran;
}
cmd.Parameters.Add(new OleDbParameter("@Sql", OleDbType.VarChar) { Value = strSql, Direction = ParameterDirection.Input });
cmd.Parameters.Add(new OleDbParameter("@PerPage", OleDbType.Integer) { Value = pageInfo.PerPage, Direction = ParameterDirection.Input });
cmd.Parameters.Add(new OleDbParameter("@CuttentPage", OleDbType.Integer) { Value = pageInfo.CuttentPage, Direction = ParameterDirection.InputOutput });
cmd.Parameters.Add(new OleDbParameter("@RecordNumber", OleDbType.Integer) { Direction = ParameterDirection.Output });
cmd.Parameters.Add(new OleDbParameter("@PagesCount", OleDbType.Integer) { Direction = ParameterDirection.Output });
IDataReader reader = cmd.ExecuteReader();
List<T> lst = new List<T>();
if (reader != null)
{
reader.Read();
if (reader.NextResult())
{
while (reader.Read())
{
T t = new T();
t.Load<T>(reader);
lst.Add(t);
}
}
reader.Close();
pageInfo.CuttentPage = (int)cmd.Parameters["@CuttentPage"].Value;
pageInfo.RecordNumber = (int)cmd.Parameters["@RecordNumber"].Value;
pageInfo.PagesCount = (int)cmd.Parameters["@PagesCount"].Value;
}
return lst;
}
protected void btnExpertExcel_Click(object sender, EventArgs e)
{
string userIDs = checkedUserID.Value;
userIDs = userIDs.Substring(0, userIDs.Length - 1);
OleDbConnection conn = new OleDbConnection(Dal.OleDbHlper.ConnectionString);
conn.Open();
Dal.Models.PageInfo <Dal.Models.UserInfo> pgif = new Dal.Models.PageInfo <Dal.Models.UserInfo>();
Dal.Models.UserInfo user = (Dal.Models.UserInfo)Session["UserInfo"];
string userName = tbxUserName.Text;
DataTable dtOrg = BLL.User.GetOrganizationlist(Convert.ToInt32(user.UserID), userName, userIDs, conn);
string TempletFileName = Server.MapPath("~/Content/Template/会员用户.xls"); //模板文件
string ReportFileName = Server.MapPath("~/Content/Temp/ExcelTemp/会员用户.xls"); //导出文件
FileStream file = null;
try
{
file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
}
catch (Exception)
{
//Response.Write("<script>alert('模板文件不存在或正在打开');</script>");
hdMsg.Value = "模板文件不存在或正在打开!";
return;
}
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet ws = (HSSFSheet)hssfworkbook.GetSheet("Sheet1");
if (ws == null)//工作薄中没有工作表
{
//Response.Write("<script>alert('工作薄中没有Sheet1工作表');</script>");
hdMsg.Value = "工作薄中没有Sheet1工作表!";
return;
}
int count = dtOrg.Rows.Count;
if (count > 0)
{
for (int i = 0; i < count; i++)
{
int _row = i + 1; //i + 1;
HSSFRow row = (HSSFRow)ws.CreateRow(_row);
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(dtOrg.Rows[i]["OrganizationName"].ToString().Trim());
row.CreateCell(2).SetCellValue(dtOrg.Rows[i]["OrganizationCode"].ToString().Trim());
row.CreateCell(3).SetCellValue(dtOrg.Rows[i]["UserName"].ToString().Trim());
row.CreateCell(4).SetCellValue(dtOrg.Rows[i]["OrganizationAddress"].ToString().Trim());
row.CreateCell(5).SetCellValue(dtOrg.Rows[i]["LegalPersonName"].ToString().Trim());
row.CreateCell(6).SetCellValue(dtOrg.Rows[i]["LegalPersonIDNo"].ToString().Trim());
row.CreateCell(7).SetCellValue(dtOrg.Rows[i]["Linkman"].ToString().Trim());
row.CreateCell(8).SetCellValue(dtOrg.Rows[i]["TelephoneNumber"].ToString().Trim());
row.CreateCell(9).SetCellValue(dtOrg.Rows[i]["MobileNumber"].ToString().Trim());
row.CreateCell(10).SetCellValue(dtOrg.Rows[i]["EmailAddress"].ToString().Trim());
row.CreateCell(11).SetCellValue(dtOrg.Rows[i]["Postcode"].ToString().Trim());
row.CreateCell(12).SetCellValue(dtOrg.Rows[i]["FaxNumber"].ToString().Trim());
}
}
ws.ForceFormulaRecalculation = true;
//先删除上一个临时文件
if (File.Exists(ReportFileName))
{
FileInfo fi = new FileInfo(ReportFileName);
if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)
{
fi.Attributes = FileAttributes.Normal;
}
File.Delete(ReportFileName);
}
//创建临时文件写入数据
using (FileStream filess = File.OpenWrite(ReportFileName))
{
hssfworkbook.Write(filess);
}
System.IO.FileInfo filet = new System.IO.FileInfo(ReportFileName);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("会员用户.xls"));
Response.AddHeader("Content-Length", filet.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.WriteFile(filet.FullName);
Response.End();
conn.Close();
}
public static List <Dal.Models.UserOrg> GetLeaguerUserList(int iLoginUserID, string SearchUserName, Dal.Models.PageInfo <Dal.Models.UserOrg> pgif, OleDbConnection conn, OleDbTransaction trans = null)
{
Dal.Models.UserInfo user = GetUserInfo(iLoginUserID, conn, trans);
List <Dal.Models.UserOrg> lstUser = null;
StringBuilder sbSql = new StringBuilder();
sbSql.Append(" select u.UserID, UserName, [Password], UserType, Email, UserStatus,s.ConstantValue UserStatusText,");
sbSql.Append("LegalPersonName,LegalPersonIDNo, Linkman,TelephoneNumber, MobileNumber,EmailAddress,OrganizationAddress,OrganizationName from Users u");
sbSql.Append(" left join Organization o on o.UserID = u.UserID ");
sbSql.Append(" left join SysConstant s on s.ConstantCode = u.UserStatus");
sbSql.Append(" where u.UserType='0806'");
// 超级管理员、系统管理员、协会管理员：获取全部用户, 一级用户：获取本用户的信息
if (!(new List <string> {
"0801", "0802", "0803"
}).Contains(user.UserType))
{
sbSql.AppendFormat(" and u.UserID = {0}", iLoginUserID);
}
if (SearchUserName != null && SearchUserName.Length > 0)
{
sbSql.Append(" and u.UserName like '%" + SearchUserName + "%'");
}
sbSql.Append(" order by u.CreateTime desc ");
lstUser = Dal.OleDbHlper.GetList <Dal.Models.UserOrg>(sbSql.ToString(), pgif, conn, trans);
return(lstUser);
}
