public static Dal.Models.ExpertGroup CreateExpertGroup(Dal.Models.ExpertGroup group, Dal.Models.UserInfo user, OleDbConnection conn, OleDbTransaction tran = null)
{
if (group == null || group.ActivityID == null || group.GroupLeader == null || string.IsNullOrEmpty(group.GroupName))
{
throw new Exception("参数错误!");
}
Dal.Models.ExpertGroup groupOrriginal = GetExpertGroup(group.GroupName, group.SpecialtyID.Value, conn, tran);
if (groupOrriginal != null && groupOrriginal.GroupID != group.GroupID)
{
throw new Exception("同一专业下，专家组名称不能重复!");
}
List <Dal.Models.Expert> lstExpert = null;
lstExpert = Expert.GetExpertList(conn, user, null, "0701", group.GroupLeader.ToString(), tran);
if (lstExpert == null || lstExpert.Count == 0)
{
throw new Exception("专家组组长不在专家库中或者已被禁用！");
}
List <string> lstDeputy = null;
if (!string.IsNullOrEmpty(group.DeputyIDs))
{
lstDeputy = group.DeputyIDs.Split(',').ToList();
lstExpert = Expert.GetExpertList(conn, user, null, "0701", group.DeputyIDs, tran);
if (lstExpert == null || lstExpert.Count != lstDeputy.Count)
{
throw new Exception("专家组副组长名单中有不在专家库中或者已被禁用的专家！");
}
}
List <string> lstMember = null;
if (!string.IsNullOrEmpty(group.MemberIDs))
{
lstMember = group.MemberIDs.Split(',').ToList();
lstExpert = Expert.GetExpertList(conn, user, null, "0701", group.MemberIDs, tran);
if (lstExpert == null || lstExpert.Count != lstMember.Count)
{
throw new Exception("专家组成员名单中有不在专家库中或者已被禁用的专家！");
}
}
StringBuilder sbSql = new StringBuilder();
if (group.GroupID != null)
{
// 更新专家组信息
sbSql.Append(" UPDATE ExpertGroup");
sbSql.Append(" SET ActivityID=? ");
sbSql.Append(" ,GroupName=? ");
sbSql.Append(" ,GroupLeader=? ");
sbSql.Append(" ,SpecialtyID=?");
sbSql.Append(" ,ParentID=?");
sbSql.Append(" WHERE GroupID=?;");
Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@ActivityID", OleDbType.Integer)
{
Value = group.ActivityID
}
, new OleDbParameter("@GroupName", OleDbType.VarWChar)
{
Value = group.GroupName
}
, new OleDbParameter("@GroupLeader", OleDbType.Integer)
{
Value = group.GroupLeader
}
, new OleDbParameter("@SpecialtyID", OleDbType.Integer)
{
Value = group.SpecialtyID
}
, new OleDbParameter("@ParentID", OleDbType.Integer)
{
Value = group.ParentID
}
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = group.GroupID
});
// 清空专家组成员 如果参数没有传入专家组成员，则不删除专家组成员
sbSql.Clear();
if (string.IsNullOrEmpty(group.MemberIDs))
{
sbSql.Append(" delete from GroupMember where GroupID = ? and Grade <> '1203' ");
}
else
{
sbSql.Append(" delete from GroupMember where GroupID = ? ");
}
Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = group.GroupID
});
}
else
{
sbSql.Append(" INSERT INTO ExpertGroup ( ");
sbSql.Append(" ActivityID ");
sbSql.Append(" ,GroupName ");
sbSql.Append(" ,GroupLeader ");
sbSql.Append(" ,ParentID ");
sbSql.Append(" ,SpecialtyID");
sbSql.Append(" ) VALUES (?, ?, ?, ?, ? ) ");
Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@ActivityID", OleDbType.Integer)
{
Value = group.ActivityID
}
, new OleDbParameter("@GroupName", OleDbType.VarWChar)
{
Value = group.GroupName
}
, new OleDbParameter("@GroupLeader", OleDbType.Integer)
{
Value = group.GroupLeader
}
, new OleDbParameter("@ParentID", OleDbType.Integer)
{
Value = group.ParentID
}
, new OleDbParameter("@SpecialtyID", OleDbType.Integer)
{
Value = group.SpecialtyID
});
group = GetExpertGroup(group.GroupName, group.SpecialtyID.Value, conn, tran);
}
// 构建添加专家组成员的Sql文
sbSql.Clear();
sbSql.Append(" INSERT INTO GroupMember( ");
sbSql.Append(" GroupID ");
sbSql.Append(" , ExpertID ");
sbSql.Append(" , Grade ");
sbSql.Append(" , Ordinal ");
sbSql.Append(" ) VALUES (?, ?, ?, ?) ");
// 将专家组组长添加到专家组成员表中
Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = group.GroupID
}
, new OleDbParameter("@ExpertID", OleDbType.Integer)
{
Value = group.GroupLeader
}
, new OleDbParameter("@Grade", OleDbType.VarWChar)
{
Value = "1201"
}
, new OleDbParameter("@Ordinal", OleDbType.Integer)
{
Value = 1
});
// 将专家组副组长添加到专家组成员表中
int i = 0;
if (lstDeputy != null)
{
for (i = 0; i < lstDeputy.Count; i++)
{
Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = group.GroupID
}
, new OleDbParameter("@ExpertID", OleDbType.Integer)
{
Value = lstDeputy[i]
}
, new OleDbParameter("@Grade", OleDbType.VarWChar)
{
Value = "1202"
}
, new OleDbParameter("@Ordinal", OleDbType.Integer)
{
Value = i + 2
});
}
}
// 将专家组成员添加到专家组成员表中
if (lstMember != null)
{
for (i = 0; i < lstMember.Count; i++)
{
Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = group.GroupID
}
, new OleDbParameter("@ExpertID", OleDbType.Integer)
{
Value = lstMember[i]
}
, new OleDbParameter("@Grade", OleDbType.VarWChar)
{
Value = "1203"
}
, new OleDbParameter("@Ordinal", OleDbType.Integer)
{
Value = i + (lstDeputy == null ? 0 : lstDeputy.Count) + 2
});
}
}
return(group);
}