public static int Count(int iActivityID, int?iParentID, OleDbConnection conn, OleDbTransaction tran = null) { int? iResult = 0; string strSql = " SELECT COUNT(*) FROM Specialty WHERE ActivityID = ? "; if (iParentID == null) { strSql += " and ParentID is null "; iResult = Common.ToInt32(Dal.OleDbHlper.ExecuteScalar(strSql, conn, CommandType.Text , tran, new OleDbParameter("@ActivityID", OleDbType.Integer) { Value = iActivityID })); } else { strSql += " AND ParentID = ? "; iResult = Common.ToInt32(Dal.OleDbHlper.ExecuteScalar(strSql, conn, CommandType.Text, tran , new OleDbParameter("@ActivityID", OleDbType.Integer) { Value = iActivityID } , new OleDbParameter("@ParentID", OleDbType.Integer) { Value = iParentID })); } return(iResult ?? 0); }
public static int GetOrganizationChildCount(OleDbConnection conn, OleDbTransaction trans = null) { string strSql = " select count(*) from Users u where u.UserStatus = '0701' and UserType = '0807' "; object objResult = Dal.OleDbHlper.ExecuteScalar(strSql, conn, CommandType.Text, trans); int?iResult = Common.ToInt32(objResult); return(iResult ?? 0); }
public static int CountNotice(int ActivityID, OleDbConnection conn, OleDbTransaction tran = null) { string strSql = " SELECT COUNT(*) FROM Notice WHERE ActivityID = ? "; int iResult = Common.ToInt32(Dal.OleDbHlper.ExecuteScalar(strSql, conn, CommandType.Text, tran , new OleDbParameter("@ActivityID", OleDbType.Integer) { Value = ActivityID })).Value; return(iResult); }
public static int Count(int iOwnerID, Dal.Models.MediaType type, OleDbConnection conn, OleDbTransaction tran = null) { string strRecord = type == Dal.Models.MediaType.Specialty ? "SpecialtyID" : "PrizeID"; string strSql = " select count(*) from Media where " + strRecord + " = ?"; return(Common.ToInt32(Dal.OleDbHlper.ExecuteScalar(strSql, conn, CommandType.Text, tran , new OleDbParameter("@OwnerID", OleDbType.Integer) { Value = iOwnerID })) ?? 0); }
/// <summary> /// 获取专业的最大奖项序号 /// </summary> /// <param name="iSpecialtyID"></param> /// <returns></returns> public static int Count(int iSpecialtyID, OleDbConnection conn, OleDbTransaction tran = null) { string strSql = " select COUNT(*) from Prize where SpecialtyID = ? "; int?iResult = Common.ToInt32(Dal.OleDbHlper.ExecuteScalar(strSql, conn, CommandType.Text, tran , new OleDbParameter("@SpecialtyID", OleDbType.Integer) { Value = iSpecialtyID })); return(iResult ?? 0); }
public static int SearchSpecialtyChild( int iSpecialtyID, int iActivityID, OleDbConnection conn, OleDbTransaction tran = null) { string strSql = "select count(*) from Specialty where ActivityID = ? and ParentID = ?"; int? iResult = Common.ToInt32(Dal.OleDbHlper.ExecuteScalar(strSql, conn, CommandType.Text, tran , new OleDbParameter("@ActivityID", OleDbType.Integer) { Value = iActivityID }, new OleDbParameter("@ParentID", OleDbType.Integer) { Value = iSpecialtyID })); return(iResult ?? 0); }
public static void DeletePrize( int iSpecialtyID, int iPrizeID, OleDbConnection conn, OleDbTransaction tran = null) { // 已有申报的奖项,不允许删除 string strSql = "select COUNT(*) from Declaration where PrizeID = ? or AdjustedPrize = ? "; int? iResult = Common.ToInt32(Dal.OleDbHlper.ExecuteScalar( strSql, conn, CommandType.Text, tran , new OleDbParameter("@PrizeID", OleDbType.Integer) { Value = iPrizeID } , new OleDbParameter("@AdjustedPrize", OleDbType.Integer) { Value = iPrizeID })); if (iResult == null || iResult == 0) { iResult = 0; //先删除奖项对应的类别和获奖数量; DeletePrizeLevel(iPrizeID, conn, tran); strSql = " DELETE FROM Prize where PrizeID = ? "; Dal.OleDbHlper.ExecuteNonQuery(strSql, conn, CommandType.Text, tran , new OleDbParameter("@PrizeID", OleDbType.Integer) { Value = iPrizeID }); ResetOrdinal(iSpecialtyID, conn, tran); } if (iResult > 0) { throw new Exception("已有申报的奖项,不允许删除"); } }
public static void SaveFile(Dal.Models.UploadFileInfo file, OleDbConnection conn, OleDbTransaction tran = null) { string strRootDirectory = System.Web.HttpContext.Current.Request.PhysicalApplicationPath; if (file.SizeLimit == null || file.AmountLimit == null) { switch (file.Type) { case Dal.Models.FileType.DeclarationAppendix: Dal.Models.Appendix AppendixInfo = BLL.Appendix.GetAppendix(file.CorrelationID.Value, conn, tran); file.SizeLimit = AppendixInfo.SizeLimit; file.AmountLimit = 1; break; case Dal.Models.FileType.DeclarationAtlas: Dal.Models.Atlas AtlasInfo = BLL.Atlas.GetAtlas(file.CorrelationID.Value, conn, tran); file.SizeLimit = AtlasInfo.SizeLimit; file.AmountLimit = AtlasInfo.UploadLimitMax; break; case Dal.Models.FileType.DeclarationMedia: Dal.Models.Media media = BLL.Media.GetMedia(file.CorrelationID.Value, conn, tran); file.SizeLimit = media.SizeLimit; file.AmountLimit = 1; break; default: file.SizeLimit = 0; file.AmountLimit = 0; break; } } byte[] bufferCompressed = null; StringBuilder sb = new StringBuilder(); int iResult = 0; try { using (System.IO.FileStream StreamToZip = new System.IO.FileStream( strRootDirectory + file.URL, System.IO.FileMode.Open, System.IO.FileAccess.Read)) { double dfileSize = StreamToZip.Length / 1048576; if (dfileSize > file.SizeLimit && file.SizeLimit != 0) { throw new Exception("上传文件大小超过设置范围内!"); } //try //{ // bufferCompressed = Compression.CompressFile(StreamToZip); //} //catch //{ // bufferCompressed = null; //} } if (file.CorrelationID == null) { sb.Append(" select UploadFileID from UploadFile where OwnerID = ? and FileName = ? "); iResult = Common.ToInt32(Dal.OleDbHlper.ExecuteScalar(sb.ToString(), conn, CommandType.Text, tran , new OleDbParameter("@OwnerID", OleDbType.Integer) { Value = file.OwnerID } , new OleDbParameter("@FileName", OleDbType.VarWChar) { Value = file.FileName })) ?? 0; if (iResult > 0 && iResult != file.UploadFileID) { throw new Exception("文件名重名!"); } if (file.UploadFileID == null) { InsertFile(file, bufferCompressed, conn, tran); } else { UpdateFile(file, bufferCompressed, conn, tran); } } else { if (file.Type.ToString() == "DeclarationAtlas") { sb.Append(" select UploadFileID from UploadFile where OwnerID = ? and CorrelationID = ? and FileName = ? "); iResult = Common.ToInt32(Dal.OleDbHlper.ExecuteScalar(sb.ToString(), conn, CommandType.Text, tran , new OleDbParameter("@OwnerID", OleDbType.Integer) { Value = file.OwnerID } , new OleDbParameter("@CorrelationID", OleDbType.Integer) { Value = file.CorrelationID } , new OleDbParameter("@FileName", OleDbType.VarWChar) { Value = file.FileName })) ?? 0; if (iResult == 0) { List <Dal.Models.UploadFileInfo> lstUploadFileInfo = BLL.UploadFileInfo.GetFileList( file.OwnerID, file.CorrelationID, "DeclarationAtlas", conn, tran); if (lstUploadFileInfo.Count() == file.AmountLimit && file.AmountLimit != 0) { throw new Exception("超过数量上限!"); } InsertFile(file, bufferCompressed, conn, tran); } else { if (file.UploadFileID == null || iResult == file.UploadFileID.Value) { file.UploadFileID = iResult; UpdateFile(file, bufferCompressed, conn, tran); } else { throw new Exception("文件名重名!"); } } } else { sb.Append(" select UploadFileID from UploadFile where OwnerID = ? and CorrelationID = ? and TypeCode = ? "); iResult = Common.ToInt32(Dal.OleDbHlper.ExecuteScalar(sb.ToString(), conn, CommandType.Text, tran , new OleDbParameter("@OwnerID", OleDbType.Integer) { Value = file.OwnerID } , new OleDbParameter("@CorrelationID", OleDbType.Integer) { Value = file.CorrelationID } , new OleDbParameter("@TypeCode", OleDbType.VarWChar) { Value = file.Type })) ?? 0; if (iResult > 0) { file.UploadFileID = iResult; UpdateFile(file, bufferCompressed, conn, tran); } else { InsertFile(file, bufferCompressed, conn, tran); } } } } catch (Exception ex) { throw ex; } finally { sb = null; bufferCompressed = null; GC.Collect(); } }
/// <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); }