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