public static List <Dal.Models.Specialty> GetSpecialtyDropList(int iActivityID, string strUserType, int iUserID, bool bJustTop, OleDbConnection conn, OleDbTransaction tran = null) { string strSql = " SELECT s.* FROM V_SPECIALTY s WHERE ACTIVITYID = ? "; strSql += " and s.SpecialtyID not in (SELECT SpecialtyID FROM Prize)"; if (strUserType == "0804") { strSql += " and s.SpecialtyID in (SELECT u.SpecialtyID FROM UserSpecialty u where UserID = " + iUserID + ") "; } strSql += " AND PARENTID IS NULL ORDER BY ORDINAL"; List <Dal.Models.Specialty> lstSpecialty = Dal.OleDbHlper.GetList <Dal.Models.Specialty>( strSql, conn, CommandType.Text, tran, new OleDbParameter("@ActivityID", OleDbType.Integer) { Value = iActivityID }); if (bJustTop) { return(lstSpecialty); } else { List <Dal.Models.Specialty> lstAllSpecialty = new List <Dal.Models.Specialty>(); foreach (Dal.Models.Specialty item in lstSpecialty) { lstAllSpecialty.Add(item); List <Dal.Models.Specialty> lstChild = Specialty.GetChildList(item.SpecialtyID.Value, conn, tran); if (lstChild.Count > 0) { lstAllSpecialty.AddRange(lstChild); } } return(lstAllSpecialty); } }
public static List <Dal.Models.Specialty> GetSpecialtyList(int iActivityID, bool bJustTop, string specialtyID, OleDbConnection conn, OleDbTransaction tran = null) { string strSql = " SELECT * FROM V_SPECIALTY WHERE ACTIVITYID = ? "; if (specialtyID != null && specialtyID.Length > 0) { strSql += " AND SpecialtyID in (" + specialtyID + ")"; } strSql += "AND PARENTID IS NULL ORDER BY ORDINAL "; List <Dal.Models.Specialty> lstSpecialty = Dal.OleDbHlper.GetList <Dal.Models.Specialty>( strSql, conn, CommandType.Text, tran, new OleDbParameter("@ActivityID", OleDbType.Integer) { Value = iActivityID }); if (bJustTop) { return(lstSpecialty); } else { List <Dal.Models.Specialty> lstAllSpecialty = new List <Dal.Models.Specialty>(); foreach (Dal.Models.Specialty item in lstSpecialty) { lstAllSpecialty.Add(item); List <Dal.Models.Specialty> lstChild = Specialty.GetChildList(item.SpecialtyID.Value, conn, tran); if (lstChild.Count > 0) { lstAllSpecialty.AddRange(lstChild); } } return(lstAllSpecialty); } }
public static int SetPrize( Dal.Models.Prize prize, List <int> LstPrizeLimitNum, OleDbConnection conn, OleDbTransaction tran) { if (prize == null || string.IsNullOrEmpty(prize.PrizeName) || prize.SpecialtyID == null) { throw new Exception("参数错误!"); } Dal.Models.Specialty specialty = Specialty.GetSpecialty(prize.SpecialtyID.Value, conn, tran); // 所选专业不存在 if (specialty == null) { throw new Exception("所选专业不存在!"); } if ((specialty.ChildCount ?? 0) > 0) { throw new Exception("奖项创建失败,该专业下已存在子专业,无法创建奖项。"); } // 同一专业中,奖项不允许重名 Dal.Models.Prize prizeOriginal = GetPrize(prize.PrizeName, prize.SpecialtyID.Value, conn, tran); if (prizeOriginal != null && prize.PrizeID != prizeOriginal.PrizeID) { throw new Exception("同一专业中,奖项不允许重名!"); } //奖项类别为空时,默认类别为综合奖 if (string.IsNullOrEmpty(prize.PrizeTypeCode) || !(new string[] { "0301", "0302", "0303", "0304" }).Contains(prize.PrizeTypeCode)) { prize.PrizeTypeCode = "0301"; } if (prize.ParticipantLimit == null) { prize.ParticipantLimit = Convert.ToInt32( SysConstant.GetConstant(prize.PrizeTypeCode, conn, tran).CorrelationValue); } if (string.IsNullOrEmpty(prize.TemplateCode)) { prize.TemplateCode = specialty.TemplateCode; } StringBuilder sbSql = new StringBuilder(); if (prize.PrizeID == null) { sbSql.Append(" INSERT INTO Prize ( "); sbSql.Append(" PrizeName"); sbSql.Append(" ,SpecialtyID "); sbSql.Append(" ,PrizeTypeCode "); sbSql.Append(" ,TemplateCode "); sbSql.Append(" ,IsMultipleVoting "); sbSql.Append(" ,VoteType "); sbSql.Append(" ,ParticipantLimit "); sbSql.Append(" ,AppendixDiscription "); sbSql.Append(" ,CreateTime "); sbSql.Append(" ,LastUpdateTime "); sbSql.Append(" ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, GETDATE(), GETDATE() ) "); int iResult = Dal.OleDbHlper.ExecuteNonQuery( sbSql.ToString(), conn, CommandType.Text, tran , new OleDbParameter("@PrizeName", OleDbType.VarWChar) { Value = prize.PrizeName } , new OleDbParameter("@SpecialtyID", OleDbType.Integer) { Value = prize.SpecialtyID } , new OleDbParameter("@PrizeTypeCode", OleDbType.VarWChar) { Value = prize.PrizeTypeCode } , new OleDbParameter("@TemplateCode", OleDbType.VarWChar) { Value = prize.TemplateCode } , new OleDbParameter("@IsMultipleVoting", OleDbType.Boolean) { Value = prize.IsMultipleVoting } , new OleDbParameter("@VoteType", OleDbType.VarWChar) { Value = prize.VoteType } , new OleDbParameter("@ParticipantNumber", OleDbType.Integer) { Value = prize.ParticipantLimit } , new OleDbParameter("@AppendixDiscription", OleDbType.VarWChar) { Value = prize.AppendixDiscription } ); prize.PrizeID = GetPrize(prize.PrizeName, prize.SpecialtyID.Value, conn, tran).PrizeID; } else { sbSql.Append(" UPDATE Prize "); sbSql.Append(" SET PrizeName = ? "); sbSql.Append(" ,IsMultipleVoting = ? "); sbSql.Append(" ,VoteType = ? "); sbSql.Append(" ,ParticipantLimit = ? "); sbSql.Append(" ,AppendixDiscription = ? "); sbSql.Append(" ,LastUpdateTime = GETDATE() "); sbSql.Append(" WHERE PrizeID = ? "); int iResult = Dal.OleDbHlper.ExecuteNonQuery( sbSql.ToString(), conn, CommandType.Text, tran , new OleDbParameter("@PrizeName", OleDbType.VarWChar) { Value = prize.PrizeName } , new OleDbParameter("@IsMultipleVoting", OleDbType.Boolean) { Value = prize.IsMultipleVoting } , new OleDbParameter("@VoteType", OleDbType.VarWChar) { Value = prize.VoteType } , new OleDbParameter("@ParticipantLimit", OleDbType.Integer) { Value = prize.ParticipantLimit } , new OleDbParameter("@AppendixDiscription", OleDbType.VarWChar) { Value = prize.AppendixDiscription } , new OleDbParameter("@PrizeID", OleDbType.Integer) { Value = prize.PrizeID } ); } //奖项数量 SetPrizeLevel(prize, LstPrizeLimitNum, conn, tran); SetOrdinal(prize, conn, tran); return(prize.PrizeID.Value); }