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