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