question.cs

动易SiteFactory&#8482 网上商店系统1.0源代码

                case 6:
                    builder.Append(" and q.QuestionCreateTime > q.ReplyTime and q.IsSolved=0 and q.IsReply=1 ");
                    break;

                case 7:
                    builder.Append(" and q.QuestionTitle like '%").Append(keyword).Append("%'");
                    break;

                case 8:
                    builder.Append(" and q.QuestionCreator='").Append(keyword).Append("'");
                    break;

                case 9:
                    builder.Append(" and q.ID IN (select DISTINCT QuestionID from PE_Reply where ReplyCreator ='").Append(keyword).Append("')");
                    sortCloumn = "q.ReplyTime";
                    break;

                case 10:
                {
                    builder.Append(GetFilterByComplexSearch(keyword));
                    object input = DBHelper.ExecuteScalarSql("Select Sum(q.Score) from PE_Question q inner join PE_QuestionType t on q.TypeID=t.TypeID Where " + builder.ToString());
                    this.m_TotalScore = DataConverter.CLng(input);
                    break;
                }
            }
            return this.GetQuestionCommon(startRowIndex, maximumRows, builder.ToString(), sortCloumn);
        }

        public QuestionInfo GetQuestionById(int id)
        {
            using (NullableDataReader reader = DBHelper.ExecuteReaderSql("Select q.*,t.TypeName from PE_Question q Inner Join PE_QuestionType t On q.TypeID=t.TypeID Where q.ID=@ID", new Parameters("@ID", DbType.Int32, id)))
            {
                if (reader.Read())
                {
                    return GetInfoByReader(reader, true);
                }
                return new QuestionInfo(true);
            }
        }

        private IList<QuestionInfo> GetQuestionCommon(int startRowIndex, int maximumRows, string filter, string sortCloumn)
        {
            Database database = DatabaseFactory.CreateDatabase();
            DbCommand storedProcCommand = database.GetStoredProcCommand("PR_Common_GetListBySortColumn");
            database.AddInParameter(storedProcCommand, "@StartRows", DbType.Int32, startRowIndex);
            database.AddInParameter(storedProcCommand, "@PageSize", DbType.Int32, maximumRows);
            database.AddInParameter(storedProcCommand, "@PrimaryColumn", DbType.String, "q.ID");
            database.AddInParameter(storedProcCommand, "@SortColumnDbType", DbType.String, "DateTime");
            database.AddInParameter(storedProcCommand, "@SortColumn", DbType.String, sortCloumn);
            database.AddInParameter(storedProcCommand, "@StrColumn", DbType.String, "q.ID,q.TypeID,t.TypeName,q.QuestionTitle,q.QuestionCreateTime,q.QuestionCreator,q.ReplyCreator,q.ReplyTime,q.Score,q.IsPublic,q.IsReply,q.IsSolved");
            database.AddInParameter(storedProcCommand, "@Sorts", DbType.String, "DESC");
            database.AddInParameter(storedProcCommand, "@TableName", DbType.String, "PE_Question q inner join PE_QuestionType t on q.TypeID=t.TypeID");
            database.AddInParameter(storedProcCommand, "@Filter", DbType.String, filter);
            database.AddOutParameter(storedProcCommand, "@Total", DbType.Int32, maximumRows);
            IList<QuestionInfo> list = new List<QuestionInfo>();
            using (NullableDataReader reader = new NullableDataReader(database.ExecuteReader(storedProcCommand)))
            {
                while (reader.Read())
                {
                    list.Add(GetInfoByReader(reader, false));
                }
            }
            this.m_TotalCount = (int) database.GetParameterValue(storedProcCommand, "@Total");
            return list;
        }

        public int GetQuestionStatCount()
        {
            return this.m_StatisticCount;
        }

        public DataTable GetQuestionStatistic(int startRowIndex, int maximumRows)
        {
            Database database = DatabaseFactory.CreateDatabase();
            DbCommand storedProcCommand = database.GetStoredProcCommand("PR_Common_GetListBySortColumn");
            database.AddInParameter(storedProcCommand, "@StartRows", DbType.Int32, startRowIndex);
            database.AddInParameter(storedProcCommand, "@PageSize", DbType.Int32, maximumRows);
            database.AddInParameter(storedProcCommand, "@PrimaryColumn", DbType.String, "ID");
            database.AddInParameter(storedProcCommand, "@SortColumnDbType", DbType.String, "int");
            database.AddInParameter(storedProcCommand, "@SortColumn", DbType.String, "QuestionCount");
            database.AddInParameter(storedProcCommand, "@StrColumn", DbType.String, "ID, QuestionCreator ,QuestionCount , IsReply ,IsSolved");
            database.AddInParameter(storedProcCommand, "@Sorts", DbType.String, "DESC");
            database.AddInParameter(storedProcCommand, "@Filter", DbType.String);
            database.AddInParameter(storedProcCommand, "@TableName", DbType.String, "View_QuestionStatistic");
            database.AddOutParameter(storedProcCommand, "@Total", DbType.Int32, maximumRows);
            DataTable table = new DataTable();
            table.Columns.Add("ID", typeof(int));
            table.Columns.Add("QuestionCreator", typeof(string));
            table.Columns.Add("QuestionCount", typeof(int));
            table.Columns.Add("IsReply", typeof(int));
            table.Columns.Add("IsSolved", typeof(int));
            using (NullableDataReader reader = new NullableDataReader(database.ExecuteReader(storedProcCommand)))
            {
                while (reader.Read())
                {
                    DataRow row = table.NewRow();
                    row["ID"] = reader.GetInt32("ID");
                    row["QuestionCreator"] = reader.GetString("QuestionCreator");
                    row["QuestionCount"] = reader.GetInt32("QuestionCount");
                    row["IsReply"] = reader.GetInt32("IsReply");
                    row["IsSolved"] = reader.GetInt32("IsSolved");
                    table.Rows.Add(row);
                }
            }
            this.m_StatisticCount = (int) database.GetParameterValue(storedProcCommand, "@Total");
            return table;
        }

        public int GetQuestionTotal()
        {
            return this.m_TotalCount;
        }

        public IList<QuestionInfo> GetQuestonsByUser(string userName, int startRowIndex, int maximumRows, int searchType, string keyword)
        {
            Database database = DatabaseFactory.CreateDatabase();
            DbCommand storedProcCommand = database.GetStoredProcCommand("PR_Common_GetListBySortColumn");
            database.AddInParameter(storedProcCommand, "@StartRows", DbType.Int32, startRowIndex);
            database.AddInParameter(storedProcCommand, "@PageSize", DbType.Int32, maximumRows);
            database.AddInParameter(storedProcCommand, "@PrimaryColumn", DbType.String, "ID");
            database.AddInParameter(storedProcCommand, "@SortColumnDbType", DbType.String, "DateTime");
            database.AddInParameter(storedProcCommand, "@SortColumn", DbType.String, "QuestionCreateTime");
            database.AddInParameter(storedProcCommand, "@StrColumn", DbType.String, "ID,QuestionTitle,QuestionCreateTime,ReplyTime,IsReply,IsSolved");
            database.AddInParameter(storedProcCommand, "@Sorts", DbType.String, "DESC");
            database.AddInParameter(storedProcCommand, "@TableName", DbType.String, "PE_Question");
            database.AddInParameter(storedProcCommand, "@Filter", DbType.String);
            database.AddOutParameter(storedProcCommand, "@Total", DbType.Int32, maximumRows);
            string str = " QuestionCreator='" + userName + "' ";
            switch (searchType)
            {
                case 1:
                    str = str + " and IsReply=1 and IsSolved=0 ";
                    storedProcCommand.Parameters["@SortColumn"].Value = "ReplyTime";
                    break;

                case 2:
                    str = str + " and IsSolved=1 ";
                    break;

                case 3:
                    if (!string.IsNullOrEmpty(keyword))
                    {
                        str = str + " and QuestionTitle like '%" + keyword + "%' ";
                    }
                    break;
            }
            storedProcCommand.Parameters["@Filter"].Value = str;
            IList<QuestionInfo> list = new List<QuestionInfo>();
            using (NullableDataReader reader = new NullableDataReader(database.ExecuteReader(storedProcCommand)))
            {
                while (reader.Read())
                {
                    QuestionInfo item = new QuestionInfo();
                    item.Id = reader.GetInt32("ID");
                    item.QuestionTitle = reader.GetString("QuestionTitle");
                    item.QuestionCreateTime = reader.GetDateTime("QuestionCreateTime");
                    item.ReplyTime = reader.GetNullableDateTime("ReplyTime");
                    item.IsReply = reader.GetBoolean("IsReply");
                    item.IsSolved = reader.GetBoolean("IsSolved");
                    list.Add(item);
                }
            }
            this.m_TotalCount = (int) database.GetParameterValue(storedProcCommand, "@Total");
            return list;
        }

        public IList<QuestionInfo> GetSolvedQuestions(int startRowIndex, int maximumRows, int searchType, string keyword)
        {
            string filter = " q.IsSolved=1 ";
            if (!string.IsNullOrEmpty(keyword))
            {
                switch (searchType)
                {
                    case 1:
                        filter = filter + " and q.QuestionTitle like '%" + keyword + "%' ";
                        break;

                    case 2:
                        filter = filter + " and t.TypeID=" + keyword;
                        break;
                }
            }
            return this.GetQuestionCommon(startRowIndex, maximumRows, filter, "q.ReplyTime");
        }

        public int GetTotalScore()
        {
            return this.m_TotalScore;
        }

        public int GetUserPointByUserName(string userName)
        {
            return DataConverter.CLng(DBHelper.ExecuteScalarSql("select (UserPoint-(select Isnull(sum(score),0) from PE_Question where QuestionCreator=@UserName and IsSolved=0)) as UserPoint\r\nfrom PE_Users where UserName=@UserName", new Parameters("@UserName", DbType.String, userName)));
        }

        private static bool HasValue(string complexSearchItem)
        {
            return (!string.IsNullOrEmpty(complexSearchItem) && (complexSearchItem != "0"));
        }

        public bool SaveQuestionSetting(int questionId, int typeId, int score, bool isPublic, bool isSolved)
        {
            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@IsPublic", DbType.Boolean, isPublic);
            cmdParams.AddInParameter("@IsSolved", DbType.Boolean, isSolved);
            cmdParams.AddInParameter("@Score", DbType.Int32, score);
            cmdParams.AddInParameter("@TypeID", DbType.Int32, typeId);
            cmdParams.AddInParameter("@ID", DbType.Int32, questionId);
            return DBHelper.ExecuteSql("UPDATE PE_Question SET TypeID=@TypeID, IsPublic=@IsPublic,IsSolved = @IsSolved,Score=@Score WHERE ID = @ID", cmdParams);
        }

        public bool Update(QuestionInfo info)
        {
            return DBHelper.ExecuteSql("UPDATE PE_Question SET ProductVersion=@ProductVersion,ProductDBType=@ProductDBType,SystemType=@SystemType,TypeId=@TypeId,QuestionTitle=@QuestionTitle,QuestionContent=@QuestionContent,QuestionCreateTime=@QuestionCreateTime,QuestionCreator=@QuestionCreator,IsPublic=@IsPublic,[IP]=@IP,ReplyCreator=@ReplyCreator,ReplyTime=@ReplyTime,IsReply=@IsReply,AntiVirus=@AntiVirus,FireWall=@FireWall,ErrorCode=@ErrorCode,ErrorText=@ErrorText,IsSolved = @IsSolved,Url=@Url,Score=@Score WHERE ID = @ID", GetParameters(info));
        }

        public bool Update(int questionId, string replyCreator, DateTime replyTime, bool isReply, bool isPublic, bool isSolved, int score)
        {
            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@IsPublic", DbType.Boolean, isPublic);
            cmdParams.AddInParameter("@ReplyCreator", DbType.String, replyCreator);
            cmdParams.AddInParameter("@ReplyTime", DbType.DateTime, replyTime);
            cmdParams.AddInParameter("@IsReply", DbType.Boolean, isReply);
            cmdParams.AddInParameter("@IsSolved", DbType.Boolean, isSolved);
            cmdParams.AddInParameter("@Score", DbType.Int32, score);
            cmdParams.AddInParameter("@ID", DbType.Int32, questionId);
            return DBHelper.ExecuteSql("UPDATE PE_Question SET IsPublic=@IsPublic,ReplyCreator=@ReplyCreator,ReplyTime=@ReplyTime,IsReply=@IsReply,IsSolved = @IsSolved,Score=@Score WHERE ID = @ID", cmdParams);
        }

        public bool UpdateCreateTime(DateTime dt, int questionId)
        {
            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@QuestionCreateTime", DbType.DateTime, dt);
            cmdParams.AddInParameter("@ID", DbType.Int32, questionId);
            return DBHelper.ExecuteSql("update PE_Question Set QuestionCreateTime=@QuestionCreateTime Where ID=@ID", cmdParams);
        }
    }
}