Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
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);
        }
Exemplo n.º 3
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);
        }
Exemplo n.º 4
0
        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);
        }
Exemplo n.º 5
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);
        }
Exemplo n.º 6
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);
        }
Exemplo n.º 7
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("已有申报的奖项,不允许删除");
            }
        }
Exemplo n.º 8
0
        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();
            }
        }
Exemplo n.º 9
0
        /// <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);
        }