public static Dal.Models.SysConstant GetConstant(string strConstantCode, OleDbConnection conn = null, OleDbTransaction trans = null) { bool bNewConn = false; if (conn == null) { bNewConn = true; conn = new OleDbConnection(Dal.OleDbHlper.ConnectionString); conn.Open(); } string strSql = " select * from SysConstant where ConstantCode = ? "; IDataReader reader = Dal.OleDbHlper.ExecuteReader(strSql, conn, CommandType.Text, trans , new OleDbParameter("@ConstantCode", OleDbType.VarWChar) { Value = strConstantCode }); Dal.Models.SysConstant cnst = Dal.Models.BaseEntity.GetEntity <Dal.Models.SysConstant>(reader); if (bNewConn) { conn.Close(); conn.Dispose(); } return(cnst); }
/// <summary> /// 导入专家 /// </summary> /// <param name="context"></param> /// <param name="conn"></param> /// <remarks>第一个对象标识:0 没有文件 1 有文件 第二个对象:成功列表 第三个对象:失败列表及失败原因</remarks> public void ImportExpert(HttpContext context, OleDbConnection conn) { List <object> lstResult = new List <object>(); if (context.Session["ActivityType"] == null) { lstResult.Add("-1"); lstResult.Add("当前会话已结束,请重新登录!"); context.Response.Write(JsonConvert.SerializeObject(lstResult)); return; } string strActivityType = context.Session["ActivityType"].ToString(); Dal.Models.Activity activity = BLL.Activity.GetActivity(strActivityType, conn); if (activity == null) { lstResult.Add("-1"); lstResult.Add("当前活动已关闭,请联系活动管理人员!"); context.Response.Write(JsonConvert.SerializeObject(lstResult)); return; } //int specialtyID = Convert.ToInt32(context.Request["SpecialtyID"]); if (context.Request.Files.Count <= 0) { lstResult.Add("-1"); lstResult.Add("请选择文件!"); context.Response.Write(JsonConvert.SerializeObject(lstResult)); return; } // 接收上传文件 string excelFile = UploadFile(context); // 读取文件中的数据 DataTable dtExpert = BLL.Common.ReadExcelByNPOI(excelFile, true, 0); if (dtExpert == null) { lstResult.Add("-1"); lstResult.Add("读取文件中的数据失败!"); context.Response.Write(JsonConvert.SerializeObject(lstResult)); return; } List <DataRow> removelist = new List <DataRow>(); for (int i = 0; i < dtExpert.Rows.Count; i++) { bool IsNull = true; for (int j = 0; j < dtExpert.Columns.Count; j++) { if (!string.IsNullOrEmpty(dtExpert.Rows[i][j].ToString().Trim())) { IsNull = false; break; } } if (IsNull) { removelist.Add(dtExpert.Rows[i]); } } for (int i = 0; i < removelist.Count; i++) { dtExpert.Rows.Remove(removelist[i]); } Dal.Models.UserInfo user = (Dal.Models.UserInfo)context.Session["UserInfo"]; Dal.Models.Expert expert = null; Dal.Models.Expert expertOriginal = null; Dal.Models.UserInfo userOriginal = null; List <Dal.Models.Expert> lstExpert = new List <Dal.Models.Expert>(); //创建专家,如果专家创建失败,加入到这里 List <KeyValuePair <string, Dal.Models.Expert> > lstError = new List <KeyValuePair <string, Dal.Models.Expert> >(); List <Dal.Models.SysConstant> lstConstant = BLL.SysConstant.GetConstantList("09", conn); string strDefaultPassword = BLL.SysConstant.GetAppSettings("DefaultPassword", conn); Dal.Models.SysConstant cnstIDType = null; foreach (DataRow row in dtExpert.Rows) { if (string.IsNullOrEmpty(row["姓名"].ToString().Trim())) { continue; } expert = new Dal.Models.Expert(); expert.ExpertName = row["姓名"].ToString().Trim(); expert.Gender = row["性别"].ToString().Trim(); expert.IDTypeText = row["证件类别"].ToString().Trim(); expert.IDNumber = row["证件号码"].ToString().Trim(); expert.Email = row["电子邮箱"].ToString().Trim(); expert.AcademicTitle = row["职称"].ToString().Trim(); expert.Profession = row["研究方向"].ToString().Trim(); expert.Workplace = row["工作单位"].ToString().Trim(); expert.SpecialtyNames = row["评优专业"].ToString().Trim(); OleDbTransaction tran = conn.BeginTransaction(); try { if (string.IsNullOrEmpty(expert.IDTypeText) || string.IsNullOrEmpty(expert.IDNumber) || string.IsNullOrEmpty(expert.Email) || string.IsNullOrEmpty(expert.SpecialtyNames)) { throw new Exception("专家信息不完整!"); } // 判断证件类别 cnstIDType = lstConstant.Find(delegate(Dal.Models.SysConstant item) { return(item.ConstantValue == expert.IDTypeText); }); if (cnstIDType == null) { throw new Exception("证件类别输入错误!"); } else { expert.IDType = cnstIDType.ConstantCode; if (expert.IDType == "0901" && !BLL.IDOperation.CheckIDCard18(expert.IDNumber)) { throw new Exception("所输入的身份证号码不是有效身份证号码!"); } } // 判断证件号 expertOriginal = BLL.Expert.GetExpert(expert.ExpertName, conn, tran); if (expertOriginal != null) { if (expertOriginal.ExpertName != expert.ExpertName && expertOriginal.IDType == expert.IDType && expertOriginal.IDNumber == expert.IDNumber) { throw new Exception("专家库中存在证件类别和证件号相同,但是不同名的专家!"); } } // 判断邮箱地址 if (!BLL.Common.IsValidEmail(expert.Email)) { throw new Exception("电子邮件地址格式错误!"); } else { userOriginal = BLL.User.GetUserByMail(expert.Email, conn, tran); if (userOriginal != null && (expertOriginal == null || userOriginal.UserID != expertOriginal.UserID)) { throw new Exception("电子邮件地址已被占用,请更换电子邮件地址!"); } } // 判断专业 List <Dal.Models.Specialty> lstSpecialty = BLL.Specialty.GetSpecialtyList(activity.ActivityID.Value, true, conn, tran); string[] specialtyNames = expert.SpecialtyNames.Replace(",", ",").Split(','); expert.SpecialtyNames = ""; List <Dal.Models.Specialty> lstInputSpecialty = null; for (int i = 0; i < specialtyNames.Count(); i++) { lstInputSpecialty = lstSpecialty.Where(s => s.SpecialtyName == specialtyNames[i]).ToList(); if (lstInputSpecialty.Count > 0) { if (user.UserType != "0804" || user.SpecialtyNames.Split().Contains(lstInputSpecialty[i].SpecialtyName)) { expert.SpecialtyIDs += lstInputSpecialty[0].SpecialtyID + ","; expert.SpecialtyNames += lstInputSpecialty[0].SpecialtyName + ","; } } } if (!string.IsNullOrEmpty(expert.SpecialtyIDs)) { expert.SpecialtyIDs = expert.SpecialtyIDs.TrimEnd(','); expert.SpecialtyNames = expert.SpecialtyNames.TrimEnd(','); } else { throw new Exception("评优专业不匹配!"); } // 专家数据入库 if (expertOriginal != null) { expert.ExpertID = expertOriginal.ExpertID; expert.UserID = expertOriginal.UserID; BLL.Expert.UpdateExpert(expert, conn, tran); } else { expert.ExpertID = BLL.Expert.CreateExpert(expert, strDefaultPassword, user.UserID.Value, conn, tran); } if (expert.SpecialtyIDs.Length > 0) { string[] specialtyIDs = expert.SpecialtyIDs.Split(','); for (int i = 0; i < specialtyIDs.Count(); i++) { BLL.User.AddUserSpecialty(expert.UserID.Value, Convert.ToInt32(specialtyIDs[i]), conn, tran); } } //BLL.User.SetUserSpecialty(expert.UserID.Value, activity.ActivityID.Value, expert.SpecialtyIDs, conn, tran); // 添加到成功数据中 lstExpert.Add(expert); tran.Commit(); } catch (Exception ex) { tran.Rollback(); // 添加失败数据及失败原因 lstError.Add(new KeyValuePair <string, Dal.Models.Expert>(ex.Message, expert)); } } lstResult.Add(dtExpert.Rows.Count); lstResult.Add(lstExpert.Count); lstResult.Add(lstExpert); lstResult.Add(lstError); context.Response.Write(JsonConvert.SerializeObject(lstResult)); }