/// <summary>
/// 取专家组副组长或专家组成员信息
/// </summary>
/// <param name="iSpecialtyID">专业编号</param>
/// <param name="strMemberNames">副组长名或者成员名</param>
/// <param name="strOriginalMemberIDs">原组副组长编号或者成员编号</param>
/// <param name="conn"></param>
/// <param name="tran"></param>
/// <returns>专家组副组长或专家组成员编号</returns>
public string GetMemberIDs(int iSpecialtyID, string strMemberNames, string strOriginalMemberIDs, OleDbConnection conn, OleDbTransaction tran)
{
string strResult = "";
// 如果姓名不为空，取专家编号
if (!string.IsNullOrEmpty(strMemberNames))
{
Dal.Models.Expert expertMember = null;
List <string> lstDeputy = strMemberNames.ToString().Replace("、", ",").Replace("，", ",").Split(',').ToList();
for (int i = 0; i < lstDeputy.Count; i++)
{
// 根据专家名取专家
expertMember = BLL.Expert.GetExpert(lstDeputy[i].ToString(), conn, tran);
if (expertMember == null)
{
throw new Exception("专家：" + lstDeputy[i].ToString() + "不存在！");
}
if (!expertMember.SpecialtyIDs.Contains(iSpecialtyID.ToString()))
{
throw new Exception("专家：" + expertMember.ExpertName + "专业不符！");
}
strResult += expertMember.ExpertID + ",";
}
strResult = strResult.TrimEnd(',');
}
// 如果编号与现有编号不同，合并
if (!string.IsNullOrEmpty(strOriginalMemberIDs) && strOriginalMemberIDs != strResult)
{
if (string.IsNullOrEmpty(strResult))
{
strResult = strOriginalMemberIDs;
}
else
{
string[] arrDeputyID = strOriginalMemberIDs.Split(',');
foreach (string strDeputyID in arrDeputyID)
{
if (!strResult.Split(',').Contains(strDeputyID))
{
strResult += "," + strDeputyID;
}
}
}
}
return(strResult);
}
/// <summary>
/// 专家修改，修改需要参数，ExpertID，ExpertName，Workplace，IDType
/// </summary>
/// <param name="context"></param>
public void EditExpert(HttpContext context, OleDbConnection conn)
{
string id = context.Request["ExpertID"];
Dal.Models.Expert expert = BLL.Expert.GetExpert(Convert.ToInt32(id), conn);
expert.ExpertName = context.Request["ExpertName"];
expert.Workplace = context.Request["Workplace"];
expert.IDType = context.Request["IDType"];
OleDbTransaction tran = conn.BeginTransaction();
try
{
BLL.Expert.UpdateExpert(expert, conn, tran);
tran.Commit();
context.Response.Write("success");
}
catch (Exception ex)
{
tran.Rollback();
context.Response.Write(ex.Message);
}
}
/// <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));
}
public void ImportExpertGroup(HttpContext context, OleDbConnection conn)
{
// 返回数据信息
List <object> lstResult = new List <object>();
if (context.Session["UserInfo"] == null)
{
lstResult.Add("-1");
lstResult.Add("当前会话已结束，请重新登录！");
context.Response.Write(JsonConvert.SerializeObject(lstResult));
return;
}
Dal.Models.UserInfo user = (Dal.Models.UserInfo)context.Session["UserInfo"];
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;
}
if (context.Request.Files.Count <= 0)
{
lstResult.Add("-1");
lstResult.Add("请选择文件！");
context.Response.Write(JsonConvert.SerializeObject(lstResult));
return;
}
// 接收上传文件
string excelFile = UploadFile(context);
// 读取文件中的数据
DataTable dtTemp = BLL.Common.ReadExcelByNPOI(excelFile, true, 0);
if (dtTemp == null)
{
lstResult.Add("-1");
lstResult.Add("读取文件中的数据失败！");
context.Response.Write(JsonConvert.SerializeObject(lstResult));
return;
}
//判断上传文件内容格式是否完整
if (dtTemp.Columns[0].ColumnName != "项目信息" || dtTemp.Columns[15].ColumnName != "上级专家组3" ||
dtTemp.Columns[12].ColumnName != "上级专家组2" || dtTemp.Columns[9].ColumnName != "上级专家组1")
{
lstResult.Add("-1");
lstResult.Add("上传成功！由于上传文件内容与模版内容不符，数据添加失败!");
context.Response.Write(JsonConvert.SerializeObject(lstResult));
return;
}
Dal.Models.ExpertGroup group = null;
Dal.Models.Declaration decl = null;
Dal.Models.Expert expertReviewer = null;
dtTemp.Columns.Add("Ordinal");
dtTemp.Columns.Add("ErrorMessage");
DataTable dtError = dtTemp.Clone();
DataTable dtAllocate = dtTemp.Clone();
OleDbTransaction tran = conn.BeginTransaction();
for (int i = 1; i < dtTemp.Rows.Count; i++)
{
try
{
// 如果没有填写项目评审专家组，略过
if (string.IsNullOrEmpty(dtTemp.Rows[i][4].ToString()) && string.IsNullOrEmpty(dtTemp.Rows[i][6].ToString()))
{
throw new NullReferenceException("项目评审专家组为空！");
}
decl = BLL.Declaration.GetDeclaration(dtTemp.Rows[i][1].ToString(), conn, tran);
//最上级专家组3
group = AddGroup(activity.ActivityID.Value, decl.SpecialtyID.Value, decl.SpecialtyName, user, dtTemp.Rows[i], 15, null, false, conn, tran);
//上级专家组2
group = AddGroup(activity.ActivityID.Value, decl.SpecialtyID.Value, decl.SpecialtyName, user, dtTemp.Rows[i], 12, group == null ? null : group.GroupID, false, conn, tran);
//上级专家组1
group = AddGroup(activity.ActivityID.Value, decl.SpecialtyID.Value, decl.SpecialtyName, user, dtTemp.Rows[i], 9, group == null ? null : group.GroupID, false, conn, tran);
//评审小组
group = AddGroup(activity.ActivityID.Value, decl.SpecialtyID.Value, decl.SpecialtyName, user, dtTemp.Rows[i], 4, group == null ? null : group.GroupID, true, conn, tran);
if (decl == null)
{
throw new Exception("项目：" + dtTemp.Rows[i][1].ToString() + "不存在!");
}
// 设定项目评审专家组
BLL.Declaration.AllocateDeclaration(decl.DeclarationID.Value, group.GroupID, conn, tran);
// 设定审批意见填写人
if (string.IsNullOrEmpty(dtTemp.Rows[i][5].ToString()))
{
// 审批意见填写人为空，则默认为专家组组长
BLL.Declaration.SetExpertReviewer(decl.DeclarationID.Value, group.GroupLeader, conn, tran);
}
else
{
expertReviewer = BLL.Expert.GetExpert(dtTemp.Rows[i][5].ToString(), conn, tran);
if (expertReviewer == null)
{
throw new Exception("审批意见填写人：" + dtTemp.Rows[i][5].ToString() + "不存在!");
}
if (!(expertReviewer.SpecialtyIDs ?? "").Contains(decl.SpecialtyID.ToString()))
{
throw new Exception("审批意见填写人：" + dtTemp.Rows[i][5].ToString() + "与项目专业不匹配!");
}
if (!BLL.ExpertGroup.IsExpertInGroup(group.GroupID.Value, expertReviewer.ExpertID.Value, conn, tran))
{
BLL.ExpertGroup.AddMember(new Dal.Models.GroupMember()
{
ExpertID = expertReviewer.ExpertID, GroupID = group.GroupID
}, conn, tran);
}
BLL.Declaration.SetExpertReviewer(decl.DeclarationID.Value, expertReviewer.ExpertID, conn, tran);
}
dtAllocate.ImportRow(dtTemp.Rows[i]);
}
catch (Exception ex)
{
dtTemp.Rows[i]["ErrorMessage"] = ex.Message;
dtError.ImportRow(dtTemp.Rows[i]);
}
}
tran.Commit();
lstResult.Add(dtTemp.Rows.Count - 1);
lstResult.Add(dtAllocate.Rows.Count);
lstResult.Add(dtAllocate);
lstResult.Add(dtError);
context.Response.Write(JsonConvert.SerializeObject(lstResult));
}
public Dal.Models.ExpertGroup AddGroup(
int iActivityID,
int iSpecialtyID,
string strSpecialtyName,
Dal.Models.UserInfo user,
DataRow row,
int iGroupNameIdx,
int?iParentID,
bool bHasMember,
OleDbConnection conn,
OleDbTransaction tran)
{
Dal.Models.ExpertGroup groupNew = new Dal.Models.ExpertGroup();
Dal.Models.ExpertGroup groupOrriginal = null;
Dal.Models.Expert expertMember = null;
groupNew.ActivityID = iActivityID;
groupNew.SpecialtyID = iSpecialtyID;
groupNew.GroupName = row[iGroupNameIdx].ToString();
groupNew.CreateTime = DateTime.Now;
groupNew.ParentID = iParentID;
#region 专家组长、专家组名、上级专家组
// 判断专家组长是否存在
if (bHasMember)
{
groupNew.LeaderName = row[iGroupNameIdx + 2].ToString();
}
else
{
groupNew.LeaderName = row[iGroupNameIdx + 1].ToString();
}
if (string.IsNullOrEmpty(groupNew.LeaderName) && string.IsNullOrEmpty(groupNew.GroupName))
{
return(null);
}
if (string.IsNullOrEmpty(groupNew.GroupName))
{
throw new Exception("专家组组名不能为空！");
}
if (string.IsNullOrEmpty(groupNew.LeaderName))
{
throw new NullReferenceException("专家组组长不能为空！");
}
// 根据专家名取专家
expertMember = BLL.Expert.GetExpert(groupNew.LeaderName, conn, tran);
if (expertMember == null)
{
throw new Exception("专家：" + groupNew.LeaderName + "不存在！");
}
// 判断专家专业
if (!expertMember.SpecialtyNames.Contains(strSpecialtyName))
{
throw new Exception("专家：" + groupNew.LeaderName + "专业不符！");
}
// 根据专家组组名取专家组
groupOrriginal = BLL.ExpertGroup.GetExpertGroup(groupNew.GroupName, iSpecialtyID, conn, tran);
if (groupOrriginal == null)
{
groupNew.GroupLeader = expertMember.ExpertID;
}
else
{
if (groupOrriginal.GroupLeader != expertMember.ExpertID)
{
throw new Exception("专家组组长：" + groupNew.LeaderName + "信息不符！");
}
if (groupOrriginal.ParentID != iParentID)
{
throw new Exception("同一专家组只能有一个上级专家组！");
}
groupNew.GroupID = groupOrriginal.GroupID;
groupNew.GroupLeader = expertMember.ExpertID;
}
#endregion
#region 专家组副组长
// 取副组长名
if (bHasMember)
{
groupNew.DeputyNames = row[iGroupNameIdx + 3].ToString();
}
else
{
groupNew.DeputyNames = row[iGroupNameIdx + 2].ToString();
}
// 如果副组长名不为空，取副组长专家编号，如果副组长编号与现有专家组副组长编号不同，合并副组长
groupNew.DeputyIDs = GetMemberIDs(iSpecialtyID, groupNew.DeputyNames, groupOrriginal == null ? "" : groupOrriginal.DeputyIDs, conn, tran);
#endregion
#region 专家组成员
// 判断专家组成员是否存在
if (bHasMember)
{
groupNew.MemberNames = row[iGroupNameIdx + 4].ToString();
}
// 如果专家组成员不为空，取专家组成员专家编号，如果专家组成员编号与现有专家组成员编号不同，合并专家组成员
groupNew.MemberIDs = GetMemberIDs(iSpecialtyID, groupNew.MemberNames, groupOrriginal == null ? "" : groupOrriginal.MemberIDs, conn, tran);
#endregion
Dal.Models.ExpertGroup group = BLL.ExpertGroup.CreateExpertGroup(groupNew, user, conn, tran);
return(group);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
OleDbConnection conn = new OleDbConnection(Dal.OleDbHlper.ConnectionString);
conn.Open();
Dal.Models.UserInfo UserInfo = (Dal.Models.UserInfo)Session["UserInfo"];
Dal.Models.Activity act = (Dal.Models.Activity)Session["Activity"];
Dal.Models.TemplateInfo tempInfo = (Dal.Models.TemplateInfo)Session["TemplateInfo"];
Dal.Models.Declaration decl = BLL.Declaration.GetDeclaration(tempInfo.DeclarationID.Value, conn);
txtGrade.Text = "Close";
DeclarationID.Text = tempInfo.DeclarationID.ToString();
if (UserInfo.UserType == "0805" && decl.ExpertReviewGroupID != null && decl.ExpertReviewer != 0)
{
// 获取专家ID
Dal.Models.Expert expert = BLL.Expert.GetExpertByUserID(UserInfo.UserID.Value, conn);
if (expert.ExpertID == decl.ExpertReviewer)
{
txtGrade.Text = "Open";
txtReviewWriter.Text = expert.ExpertName;
FirstComment.Text = decl.ExpertReviewComment;
// HtmlTableRow tr = new HtmlTableRow();
// HtmlTableCell td1 = new HtmlTableCell("td");
// td1.InnerHtml = "<p>评审意见填写人:" + expert.ExpertName + "</p>"
// + "<textarea ID='ExpertReviewComment' class='CommentsDis' rows='3' cols='20' style='height:300px;width:90%;' data='Review'>"
// + decl.ExpertReviewComment + "</textarea>";
// td1.ColSpan = 4;
// td1.Style.Add("border-bottom", "none");
// td1.Style.Add("text-align", "center");
// tr.Cells.Add(td1);
// HtmlTableRow tr2 = new HtmlTableRow();
// HtmlTableCell td2 = new HtmlTableCell("td");
// HtmlTableCell td3 = new HtmlTableCell("td");
// HtmlTableCell td4 = new HtmlTableCell("td");
// HtmlTableCell td5 = new HtmlTableCell("td");
// td2.InnerHtml = "组长（签名）:";
// td2.Style.Add("width", "40%");
// td2.Style.Add("border-top", "none");
// td2.Style.Add("border-right", "none");
// td3.InnerHtml = "";
// td3.Style.Add("width", "10%");
// td3.Style.Add("border-top", "none");
// td3.Style.Add("border-right", "none");
// td3.Style.Add("border-left", "none");
// td4.InnerHtml = "评审日期:";
// td4.Style.Add("width", "40%");
// td4.Style.Add("border-top", "none");
// td4.Style.Add("border-right", "none");
// td4.Style.Add("border-left", "none");
// td5.InnerHtml = "";
// td5.Style.Add("width", "10%");
// td5.Style.Add("border-top", "none");
// td5.Style.Add("border-left", "none");
// tr2.Cells.Add(td2);
// tr2.Cells.Add(td3);
// tr2.Cells.Add(td4);
// tr2.Cells.Add(td5);
// tbReview.Rows.Add(tr);
// tbReview.Rows.Add(tr2);
}
}
conn.Close();
}
}
/// <summary>
/// 删除用户
/// </summary>
/// <remarks>
/// 管理员：删除用户，同时删除对应数据：用户专业
/// 二级用户：删除用户，同时删除用户专业、 将归属该用户的申报表划拨到该用户对应的一级用户
/// 一级用户：（一级用户当前有申报表进入评审阶段的，不允许删除、禁用，
/// 一级用户当前没有申报表进入评审阶段，但是历史上有项目进入评审阶段，可以禁用，不能删除）
/// 删除一级用户、删除该一级用户的二级用户、删除该一级用户及其二级用户的用户专业
/// 删除该一级用户所在单位、删除该一级用户所在单位的申报表、删除相关申报表关联的所有信息
/// （申报表_参与人员、申报表_图片、申报表_附件、申报表_数据）
/// 专家用户：（专家用户有评审信息、投票信息的，不允许删除，有历史评审信息、投票信息的，不允许删除，但是允许禁用）
/// 删除用户、删除专家、删除用户专业、从当前活动专家组中移除专家
/// </remarks>
/// <param name="iUserID"></param>
public static int DeleteUser(int iUserID, int iActivityID, OleDbConnection conn, OleDbTransaction trans = null)
{
int iResult = 0;
object objResult = null;
Dal.Models.UserInfo user = User.GetUserInfo(iUserID, conn, trans);
Dal.Models.Activity activity = Activity.GetActivity(iActivityID, conn, trans);
StringBuilder sbSql = new StringBuilder();
switch (user.UserType)
{
case "0801":
throw new Exception("超级管理员不能被禁用或删除!");
case "0802": // 系统管理员
case "0803": // 协会管理员
case "0804": // 分会用户
// 管理员：删除用户，同时删除对应数据：用户专业
// 如果用户有申报表审查记录，不允许删除
sbSql.Clear();
sbSql.Append(" select COUNT(*) from Declaration where QualificationReviewer = ? ");
objResult = Dal.OleDbHlper.ExecuteScalar(sbSql.ToString(), conn, CommandType.Text, trans
, new OleDbParameter("@QualificationReviewer", OleDbType.Integer)
{
Value = iUserID
});
iResult = Common.ToInt32(objResult) ?? 0;
if (iResult > 0)
{
throw new Exception("用户有申报表审查记录，不允许删除!");
}
// 删除对应数据：用户专业
iResult += RemoveUserSpecialty(iUserID, iActivityID, conn, trans);
// 删除用户
iResult += DeleteUser(iUserID, conn, trans);
break;
case "0805":
// 专家用户：（专家用户在当前活动有专业组、投票信息的，不允许删除禁用，有历史评审信息、投票信息的，不允许删除，但是允许禁用）
Dal.Models.Expert expert = Expert.GetExpertByUserID(iUserID, conn, trans);
// 判断是否在专业组中
sbSql.Clear();
sbSql.Append(" select COUNT(*) from GroupMember where ExpertID = ? ");
objResult = Dal.OleDbHlper.ExecuteScalar(sbSql.ToString(), conn, CommandType.Text, trans
, new OleDbParameter("@ExpertID", OleDbType.Integer)
{
Value = expert.ExpertID
});
iResult = Common.ToInt32(objResult) ?? 0;
if (iResult > 0)
{
throw new Exception("所选专家已在专业组中，不允许删除!");
}
// 判断是否有投票信息
sbSql.Clear();
sbSql.Append(" select COUNT(*) from Vote where Vote.UserID = ? ");
objResult = Dal.OleDbHlper.ExecuteScalar(sbSql.ToString(), conn, CommandType.Text, trans
, new OleDbParameter("@UserID", OleDbType.Integer)
{
Value = iUserID
});
iResult = Common.ToInt32(objResult) ?? 0;
if (iResult > 0)
{
throw new Exception("所选专家有投票信息，不允许删除!");
}
// 删除对应数据：用户专业
iResult += RemoveUserSpecialty(iUserID, iActivityID, conn, trans);
// 删除专家
sbSql.Clear();
sbSql.Append(" delete from Expert where ExpertID = ? ");
iResult += Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, trans
, new OleDbParameter("@ExpertID", OleDbType.Integer)
{
Value = expert.ExpertID
});
// 删除用户
iResult += DeleteUser(iUserID, conn, trans);
break;
case "0806":
// 一级用户：（一级用户当前有申报表进入评审阶段的，不允许删除、禁用，
// 一级用户当前没有申报表进入评审阶段，但是历史上有项目进入评审阶段，可以禁用，不能删除）
// 判断申报表状态
sbSql.Clear();
sbSql.Append(" select COUNT(*) from Declaration ");
sbSql.Append(" inner join Organization on Organization.OrganizationID = Declaration.OrganizationID ");
sbSql.Append(" inner join Prize on Prize.PrizeID = Declaration.PrizeID ");
sbSql.Append(" inner join Specialty on Specialty.SpecialtyID = Prize.PrizeID ");
sbSql.Append(" inner join Activity on Activity.ActivityID = Specialty.ActivityID ");
sbSql.Append(" where Declaration.DeclarationStatus in ('1802', '1803', '1804') ");
sbSql.Append(" and Organization.UserID = ? and Activity.ActivityID = ? ");
objResult = Dal.OleDbHlper.ExecuteScalar(sbSql.ToString(), conn, CommandType.Text, trans
, new OleDbParameter("@UserID", OleDbType.Integer)
{
Value = iUserID
}
, new OleDbParameter("@ActivityID", OleDbType.Integer)
{
Value = iActivityID
});
iResult = Common.ToInt32(objResult) ?? 0;
// 有申报表
if (iResult > 0)
{
throw new Exception("所选用户有申报表进入评审阶段，不允许删除!");
}
// 删除该用户上传的文件
sbSql.Clear();
sbSql.Append(" delete from UploadFile ");
sbSql.Append(" where (TypeCode in ('DeclarationAppendix','DeclarationAtlas') and OwnerID in ");
sbSql.Append(" ( select DeclarationID from Declaration d ");
sbSql.Append(" inner join Organization o on o.OrganizationID = d.OrganizationID ");
sbSql.Append(" where o.UserID = ?)) or ");
sbSql.Append(" (TypeCode = 'DeclarationImage' and OwnerID in ( ");
sbSql.Append(" select DataID from dbo.Declaration_Data dd ");
sbSql.Append(" inner join Declaration d on d.DeclarationID = dd.DeclarationID ");
sbSql.Append(" where d.UserID = ? )) ");
iResult = Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, trans
, new OleDbParameter("@UserID", OleDbType.Integer)
{
Value = iUserID
}
, new OleDbParameter("@UserID", OleDbType.Integer)
{
Value = iUserID
});
// 删除申报表_数据
sbSql.Clear();
sbSql.Append(" delete from Declaration_Data where DeclarationID in ");
sbSql.Append(" ( select DeclarationID from Declaration d inner join Organization o ");
sbSql.Append(" on o.OrganizationID = d.OrganizationID ");
sbSql.Append(" where o.UserID = ?) ");
iResult += Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, trans
, new OleDbParameter("@UserID", OleDbType.Integer)
{
Value = iUserID
});
// 删除申报表
sbSql.Clear();
sbSql.Append(" delete from Declaration where OrganizationID in ");
sbSql.Append(" ( select OrganizationID from Organization where UserID = ?)");
iResult += Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, trans
, new OleDbParameter("@UserID", OleDbType.Integer)
{
Value = iUserID
});
// 删除该一级用户及其二级用户的用户专业
sbSql.Clear();
sbSql.Append(" delete from UserSpecialty where UserID in (select UserID from dbo.Users where UserID= ? OR CreateUser = ? ) ");
iResult += Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, trans
, new OleDbParameter("@UserID", OleDbType.Integer)
{
Value = iUserID
}
, new OleDbParameter("@CreateUser", OleDbType.Integer)
{
Value = iUserID
});
// 删除该一级用户的二级用户
sbSql.Clear();
sbSql.Append(" delete from Users where CreateUser = ? ");
iResult += Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, trans
, new OleDbParameter("@CreateUser", OleDbType.Integer)
{
Value = iUserID
});
// 删除单位
sbSql.Clear();
sbSql.Append(" delete from Organization where UserID = ? ");
iResult += Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, trans
, new OleDbParameter("@UserID", OleDbType.Integer)
{
Value = iUserID
});
// 删除用户
iResult += DeleteUser(iUserID, conn, trans);
break;
case "0807":
// 二级用户：禁用用户，同时删除用户专业、 将归属该用户的申报表划拨到该用户对应的一级用户
// 将归属该用户的申报表划拨到该用户对应的一级用户
iResult = Declaration.AdjustOwner(iUserID, user.CreateUser.Value, activity.ActivityID.Value, conn, trans);
// 删除用户专业
iResult += RemoveUserSpecialty(iUserID, activity.ActivityID.Value, conn, trans);
// 删除用户
iResult += DeleteUser(iUserID, conn, trans);
break;
case "0808":
// 删除用户区域
sbSql.Clear();
sbSql.Append(" delete from UserRegion where UserID = ? ");
Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, trans
, new OleDbParameter("@UserID", OleDbType.Integer)
{
Value = iUserID
});
// 删除用户
iResult += DeleteUser(iUserID, conn, trans);
break;
default:
break;
}
return(iResult);
}
