예제 #1
0
        /// <summary>
        /// Возвращает датасет полных новостей из архива.
        /// </summary>
        /// <returns></returns>
        public static DataSet GetArchiveNews(PagingArgs args, out int total_count, String[] offices)
        {
            String query = "SELECT n.ID,Caption,Text,AuthorID,CreateTime,ExpireTime,OfficeID, PostID,u.LastName FROM News n,Users u";
            DateTime dt = DateTime.Now.Date;
            query += " WHERE n.AuthorID=u.ID AND ExpireTime<=CONVERT(datetime, '" + dt.ToString(CultureInfo.InvariantCulture.DateTimeFormat) + "', 101)";

                query += " AND (OfficeID=" + String.Join(" OR OfficeID=", offices) + ")";

            Procedure procedure = new Procedure("uiGetObjectsPage");
            procedure.Add("@PageIndex", args.PageIndex);
            procedure.Add("@PageSize", args.PageSize);
            procedure.Add("@OrderField", args.SortExpression);
            procedure.Add("@IsOrderASC", args.SortOrderASC);
            procedure.Add("@Query", query);
            procedure.AddReturnValueParameter();
            DataSet ds = procedure.ExecDataSet();
            total_count = Convert.ToInt32(procedure.GetReturnValue());

            return ds;
        }
예제 #2
0
 /// <summary>
 /// Возвращает имена неприкрепленных файлов.
 /// </summary>
 public static DataSet GetUnnecessaryAttachments()
 {
     // вернуть имена файлов неприкрепленных файлов
     Procedure procedure = new Procedure("GetUnnecessaryAttachments");
     DataSet ds = procedure.ExecDataSet();
     return ds;
 }
예제 #3
0
        ///// <summary>
        ///// Возвращает датасет редакторов всех новостей.
        ///// </summary>
        ///// <returns>Датасет редакторов всех новостей.</returns>
        //public static DataSet GetNewsEditors()
        //{
        //    String query = "select distinct u.* from ";
        //    query += ObjectMapper.GetDBTableName(typeof(Person)) + " u, ";
        //    query += ObjectMapper.GetDBTableName(typeof(GroupMembership)) + " pg, ";
        //    query += ObjectMapper.GetDBTableName(typeof(Group)) + " g ";
        //    query += " where u.ID = pg.PersonID";
        //    query += " and pg.GroupID = g.ID";
        //    query += " and (g.GroupID = '" + Group.GroupsEnum.OfficeNewsEditor + "' or g.GroupID='" + Group.GroupsEnum.GeneralNewsEditor + "')";
        //    Query q = new Query(query);
        //    DataSet ds = q.ExecDataSet();
        //    return ds;
        //}
        /// <summary>
        /// Возвращает датасет с результатами поиска новостей.
        /// </summary>
        /// <param name="SearchTerms">Искомые слова в тексте и заголовках.</param>
        /// <param name="SearchAuthor">Искомый автор.</param>
        public static DataSet SearchNews(PagingArgs args,
                                            out int total_count,
                                            string searchTerms,
                                            int searchAuthorID,
                                            NewsStatus newsStatus,
                                            int officeID,
                                            string[] offices,
                                            SearchPeriod period
                                           )
        {
            if (searchTerms == null)
                searchTerms = String.Empty;

            // Period

            DateTime searchDateTime = new DateTime(1754, 1, 1);
            switch (period)
            {
                case SearchPeriod.Day:
                    searchDateTime = DateTime.Now.AddDays(-1);
                    break;
                case SearchPeriod.ThreeDays:
                    searchDateTime = DateTime.Now.AddDays(-3);
                    break;
                case SearchPeriod.Week:
                    searchDateTime = DateTime.Now.AddDays(-7);
                    break;
                case SearchPeriod.Month:
                    searchDateTime = DateTime.Now.AddDays(-30);
                    break;
                case SearchPeriod.HalfYear:
                    searchDateTime = DateTime.Now.AddMonths(-6);
                    break;
                case SearchPeriod.Year:
                    searchDateTime = DateTime.Now.AddYears(-1);
                    break;
            }

            // Author

            String[] aTerms;
            // aTerms = strSearchAuthor.Split(new char[] { ' ' });
            String searchQuery = "SELECT t1.ID,Caption,Text,AuthorID,CreateTime,ExpireTime,OfficeID, PostID, t2.LastName FROM [Portal].[dbo].[News] t1 inner join (select ID,LastName from [Portal].[dbo].[Users]";
            if (searchAuthorID != 0)
            {
                searchQuery += " where (";
                /*searchQuery += "    FirstName LIKE '%" + String.Join("%' OR FirstName LIKE '%", aTerms) + "%'";
                searchQuery += " OR MiddleName LIKE '%" + String.Join("%' OR MiddleName LIKE '%", aTerms) + "%'";
                searchQuery += " OR LastName LIKE '%" + String.Join("%' OR LastName LIKE '%", aTerms) + "%'";*/
                searchQuery += "ID = " + searchAuthorID;
                searchQuery += " )";
            }
            searchQuery += ") as t2 on t1.AuthorID = t2.ID ";
            searchQuery += " WHERE (	CreateTime >= CONVERT(datetime, '" + searchDateTime.ToString(CultureInfo.InvariantCulture.DateTimeFormat) + "', 101)";

            // Terms

            aTerms = searchTerms.Split(new char[] { ' ' });
            searchQuery += " AND (( Caption LIKE '%" + String.Join("%' AND Caption LIKE '%", aTerms) + "%' )";
            searchQuery += " OR ( Text LIKE '%" + String.Join("%' AND Text LIKE '%", aTerms) + "%' ))";

            switch (newsStatus)
            {
                case NewsStatus.ActualNews:
                    searchQuery += " AND (ExpireTime > GetDate())";
                    break;
                case NewsStatus.ArchiveNews:
                    searchQuery += " AND (ExpireTime <= GetDate())";
                    break;
            }

            if (officeID == -1)
            {
                searchQuery += " AND (OfficeID=" + String.Join(" OR OfficeID=", offices) + ")";
            }
            else
                searchQuery += " AND OfficeID=" + officeID;

            searchQuery += ")";
            Procedure procedure = new Procedure("uiGetObjectsPage");
            procedure.Add("@PageIndex", args.PageIndex);
            procedure.Add("@PageSize", args.PageSize);
            procedure.Add("@OrderField", args.SortExpression);
            procedure.Add("@IsOrderASC", args.SortOrderASC);
            procedure.Add("@Query", searchQuery);
            procedure.AddReturnValueParameter();
            DataSet ds = procedure.ExecDataSet();
            total_count = Convert.ToInt32(procedure.GetReturnValue());
            return ds;
        }
예제 #4
0
 /// <summary>
 /// Возвращает датасет с аттачментами для данной новости.
 /// </summary>
 /// <param name="NewsID">ID новости.</param>
 /// <returns></returns>
 public static DataSet GetNewsAttachments(int newsID)
 {
     Procedure procedure = new Procedure("GetNewsAttachments");
     procedure.Add("@NewsID", newsID);
     DataSet ds = procedure.ExecDataSet();
     return ds;
 }