/// <summary>
/// Constructs a DataView from the datatable which contains all sections available, plus the # of forums in the section.
/// Sections and forums are sorted on OrderNo ascending, then on Name ascending.
/// </summary>
/// <param name="excludeEmptySections">If set to true, empty sections are ignored.</param>
/// <returns>
/// DataView with all the sections available, including statistics, directly bindable to webcontrols
/// </returns>
public static DataView GetAllSectionsWStatisticsAsDataView(bool excludeEmptySections)
{
// join with a derived table, which calculates the number of forums per section. This allows us to re-use the
// scalar values in multiple places (projection and where clause), without re-calculating the scalar per row.
var qf = new QueryFactory();
var q = qf.Create()
.Select(SectionFields.SectionID,
SectionFields.SectionName,
SectionFields.SectionDescription,
SectionFields.OrderNo,
qf.Field("ForumCountList", "ForumCount").As("AmountForums"))
.From(qf.Section.InnerJoin(
qf.Create()
.Select(ForumFields.ForumID.Count().As("ForumCount"),
ForumFields.SectionID)
.GroupBy(ForumFields.SectionID)
.As("ForumCountList"))
.On(ForumFields.SectionID.Source("ForumCountList")==SectionFields.SectionID))
.OrderBy(SectionFields.OrderNo.Ascending(), SectionFields.SectionName.Ascending());
if(excludeEmptySections)
{
q.AndWhere(qf.Field("ForumCountList", "ForumCount")!=0);
}
TypedListDAO dao = new TypedListDAO();
var results = dao.FetchAsDataTable(q);
return results.DefaultView;
}
/// <summary>
/// Gets the threads and accompanying statistics info, in the supportqueue specified. Only the threads which are in the forums in the list of
/// accessable forums are returned.
/// </summary>
/// <param name="accessableForums">A list of accessable forums IDs, which the user has permission to access.</param>
/// <param name="supportQueueID">The ID of the support queue to retrieve the threads for.</param>
/// <returns>a dataView of Active threads</returns>
public static DataView GetAllThreadsInSupportQueueAsDataView(List<int> accessableForums, int supportQueueID)
{
// return null, if the user does not have a valid list of forums to access
if(accessableForums == null || accessableForums.Count <= 0)
{
return null;
}
var qf = new QueryFactory();
var q = qf.Create();
var projectionFields = new List<object>(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf));
projectionFields.AddRange(new[] {
ForumFields.ForumName,
UserFields.NickName.Source("PlacedInQueueUser").As("NickNamePlacedInQueue"),
SupportQueueThreadFields.PlacedInQueueByUserID,
SupportQueueThreadFields.PlacedInQueueOn,
UserFields.NickName.Source("ClaimedThreadUser").As("NickNameClaimedThread"),
SupportQueueThreadFields.ClaimedByUserID,
SupportQueueThreadFields.ClaimedOn});
q.Select(projectionFields.ToArray());
q.From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf)
.InnerJoin(qf.Forum).On(ThreadFields.ForumID == ForumFields.ForumID)
.InnerJoin(qf.SupportQueueThread).On(ThreadFields.ThreadID == SupportQueueThreadFields.ThreadID)
.InnerJoin(qf.User.As("PlacedInQueueUser"))
.On(SupportQueueThreadFields.PlacedInQueueByUserID == UserFields.UserID.Source("PlacedInQueueUser"))
.LeftJoin(qf.User.As("ClaimedThreadUser"))
.On(SupportQueueThreadFields.ClaimedByUserID == UserFields.UserID.Source("ClaimedThreadUser")));
q.Where((ThreadFields.ForumID == accessableForums).And(SupportQueueThreadFields.QueueID == supportQueueID));
q.OrderBy(ThreadFields.ThreadLastPostingDate.Ascending());
TypedListDAO dao = new TypedListDAO();
var threadsInQueue = dao.FetchAsDataTable(q);
return threadsInQueue.DefaultView;
}
/// <summary>
/// Gets all sections.
/// </summary>
/// <returns>SectionCollection</returns>
public static SectionCollection GetAllSections()
{
var q = new QueryFactory().Section.OrderBy(SectionFields.OrderNo.Ascending(), SectionFields.SectionName.Ascending());
SectionCollection sections = new SectionCollection();
sections.GetMulti(q);
return sections;
}
private static void TestConnectionCreationForTripleStore(LinqTripleStore ts1)
{
IRdfContext context = new RdfDataContext(ts1);
QueryFactory<Track> factory = new QueryFactory<Track>(ts1.QueryMethod, context);
IRdfQuery<Track> qry1 = context.ForType<Track>();
IRdfConnection<Track> rdfConnection = factory.CreateConnection(qry1);
Assert.IsNotNull(rdfConnection);
}
public void ConstructorTest()
{
mocks = new MockRepository();
TripleStore ts = mocks.CreateMock<TripleStore>("http://www.tempuri.com");
IRdfContext context = mocks.CreateMock<RdfDataContext>(ts);
QueryFactory<Track> factory = new QueryFactory<Track>(ts.QueryType, context);
Assert.IsNotNull(factory);
}
/// <summary>
/// Checks the if thread is already bookmarked.
/// </summary>
/// <param name="userID">User ID.</param>
/// <param name="threadID">Thread ID.</param>
/// <returns>true if the thread is bookmarked</returns>
public static bool CheckIfThreadIsAlreadyBookmarked(int userID, int threadID)
{
var qf = new QueryFactory();
var q = qf.Create()
.Select(BookmarkFields.ThreadID)
.Where((BookmarkFields.ThreadID == threadID).And(BookmarkFields.UserID == userID));
var dao = new TypedListDAO();
return dao.GetScalar<int?>(q, null) != null;
}
protected DatabaseTest()
{
ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["BolfTracker"];
var providerName = connectionStringSettings.ProviderName;
var connectionString = connectionStringSettings.ConnectionString;
var providerFactory = DbProviderFactories.GetFactory(providerName);
DatabaseFactory = new DatabaseFactory(providerFactory, connectionString);
QueryFactory = new QueryFactory(true, true);
UnitOfWork = new UnitOfWork(DatabaseFactory);
}
public void Parse(string filter, IQuery query)
{
_query = query;
_mainTable = query.MainTable;
_column = null;
_comparison = null;
_concat = null;
_current = null;
_f = QueryFactory.Instance;
_bracketStack = new Stack<StackItem>();
using (_reader = new StringReader(filter))
{
while (true)
{
var part = this.ReadPart();
if (part == string.Empty) break;
this.DealPart(part);
}
}
query.Where = _current;
}
/// <summary>
/// Gets the bookmark statistics for the user with id passed in.
/// </summary>
/// <param name="userID">User ID.</param>
/// <returns></returns>
public static DataTable GetBookmarkStatisticsAsDataTable(int userID)
{
var qf = new QueryFactory();
var q = qf.Create()
.Select(BookmarkFields.ThreadID.CountDistinct().As("AmountThreads"),
MessageFields.MessageID.Count().As("AmountPostings"),
ThreadFields.ThreadLastPostingDate.Max().As("LastPostingDate"))
.From(qf.Bookmark
.InnerJoin(qf.Thread).On(BookmarkFields.ThreadID == ThreadFields.ThreadID)
.InnerJoin(qf.Message).On(ThreadFields.ThreadID == MessageFields.ThreadID))
.Where(BookmarkFields.UserID == userID);
var dao = new TypedListDAO();
return dao.FetchAsDataTable(q);
}
/// <summary>
/// Gets the row count for the set of threads in which the user specified participated with one or more messages for the page specified.
/// Threads which aren't visible for the calling user are filtered out.
/// </summary>
/// <param name="accessableForums">A list of accessable forums IDs, which the user calling the method has permission to access.</param>
/// <param name="participantUserID">The participant user ID of the user of which the threads have to be obtained.</param>
/// <param name="forumsWithThreadsFromOthers">The forums with threads from others.</param>
/// <param name="callingUserID">The calling user ID.</param>
/// <returns>a dataView of the threads requested</returns>
public static int GetRowCountLastThreadsForUserAsDataView(List<int> accessableForums, int participantUserID,
List<int> forumsWithThreadsFromOthers, int callingUserID)
{
// return null, if the user does not have a valid list of forums to access
if(accessableForums == null || accessableForums.Count <= 0)
{
return 0;
}
var qf = new QueryFactory();
var q = qf.Create()
.Select(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf))
.From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf))
.Where((ThreadFields.ForumID == accessableForums)
.And(ThreadFields.ThreadID.In(qf.Create()
.Select(MessageFields.ThreadID)
.Where(MessageFields.PostedByUserID == participantUserID)))
.And(ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, callingUserID)));
var dao = new TypedListDAO();
return dao.GetScalar<int>(qf.Create().Select(Functions.CountRow()).From(q), null);
}
/// <summary>
/// Builds form clause for the query specified for a fetch of all threads with statistics.
/// </summary>
/// <param name="qf">The query factory to use.</param>
/// <returns>ready to use join operand</returns>
internal static IJoinOperand BuildFromClauseForAllThreadsWithStats(QueryFactory qf)
{
return qf.Thread
.LeftJoin(qf.User.As("ThreadStarterUser")).On(ThreadFields.StartedByUserID == UserFields.UserID.Source("ThreadStarterUser"))
.InnerJoin(qf.Message.As("LastMessage")).On((ThreadFields.ThreadID == MessageFields.ThreadID.Source("LastMessage"))
.And(MessageFields.MessageID.Source("LastMessage").Equal(
qf.Create()
.Select(MessageFields.MessageID)
.Where(MessageFields.ThreadID == MessageFields.ThreadID.Source("LastMessage"))
.Limit(1)
.OrderBy(MessageFields.PostingDate.Descending())
.ToScalar()
.ForceRowLimit()))) // force the row limit otherwise the scalar won't have the TOP 1, which will force
// the engine to remove the orderby / distinct as it otherwise fails.
.LeftJoin(qf.User.As("LastPostingUser"))
.On(MessageFields.PostedByUserID.Source("LastMessage") == UserFields.UserID.Source("LastPostingUser"));
}
/// <summary>
/// Returns an entity collection with all User entities of users who are not currently in the given Role
/// </summary>
/// <param name="roleID">Role to use as filter</param>
/// <returns>entitycollection with data requested</returns>
public static UserCollection GetAllUsersNotInRole(int roleID)
{
var qf = new QueryFactory();
var q = qf.User
.Where(UserFields.UserID.NotIn(qf.Create().Select(RoleUserFields.UserID).Where(RoleUserFields.RoleID == roleID)))
.OrderBy(UserFields.NickName.Ascending());
UserCollection users = new UserCollection();
users.GetMulti(q);
return users;
}
public static TableValueConstructor NewTableValueConstructor(this QueryFactory factory, params RowValueConstructor[] rows)
{
return(factory.NewTableValueConstructor(ArrayQueryHelper.NewAQ(rows, false)));
}
/// <summary>
/// Checks if the message with the ID specified is first message in thread with id specified.
/// </summary>
/// <param name="threadID">The thread ID.</param>
/// <param name="messageID">The message ID.</param>
/// <returns>true if message is first message in thread, false otherwise</returns>
public static bool CheckIfMessageIsFirstInThread(int threadID, int messageID)
{
// use a scalar query, which obtains the first MessageID in a given thread. We sort on posting date ascending, and simply read
// the first messageid. If that's not available or not equal to messageID, the messageID isn't the first post in the thread, otherwise it is.
var qf = new QueryFactory();
var q = qf.Create()
.Select(MessageFields.MessageID)
.Where(MessageFields.ThreadID == threadID)
.OrderBy(MessageFields.PostingDate.Ascending())
.Limit(1);
var dao = new TypedListDAO();
var firstMessageId = dao.GetScalar<int?>(q, null);
if(firstMessageId.HasValue)
{
return firstMessageId.Value == messageID;
}
// not found.
return false;
}
public static ColumnReferences NewSelectClause(this QueryFactory factory, params ColumnReference[] cols)
{
return(NewSelectClause(factory, SetQuantifier.All, cols.Select(col => new ColumnReferenceInfo(col)).ToArray()));
}
public void CreateQueryTest_PersistentTripleStore()
{
TripleStore ts = CreatePersistentTripleStore();
IRdfContext context = new RdfDataContext(ts);
QueryFactory<Track> factory = new QueryFactory<Track>(ts.QueryType, context);
Assert.AreEqual(factory.QueryType, ts.QueryType);
IRdfQuery<Track> query = factory.CreateQuery<Track>();
Assert.IsNotNull(query);
Assert.IsTrue(query is RdfN3Query<Track>);
}
public static ColumnReferences NewSelectClause(this QueryFactory factory, SetQuantifier quantifier, params ColumnReferenceInfo[] cols)
{
return(factory.NewSelectClause(quantifier, ArrayQueryHelper.NewAQ(cols, false)));
}
public static ColumnReferences NewSelectClause(this QueryFactory factory, params ColumnReferenceInfo[] cols)
{
return(NewSelectClause(factory, SetQuantifier.All, cols));
}
public static NamedColumnsJoin NewNamedColumnsJoin(this QueryFactory factory, params String[] columnNames)
{
return(factory.NewNamedColumnsJoin(ArrayQueryHelper.NewAQ(columnNames, false)));
}
public static TableAlias NewTableAlias(this QueryFactory factory, String tableNameAlias, params String[] renamedColumns)
{
return(factory.NewTableAlias(tableNameAlias, renamedColumns.Length > 0 ? ArrayQueryHelper.NewAQ(renamedColumns, false) : null));
}
public static RowDefinition NewRow(this QueryFactory factory, params ValueExpression[] elements)
{
return(factory.NewRow(ArrayQueryHelper.NewAQ(elements, false)));
}
/// <summary>
/// Finds the users matching the filter criteria.
/// </summary>
/// <param name="filterOnRole"><see langword="true"/> if [filter on role]; otherwise, <see langword="false"/>.</param>
/// <param name="roleID">Role ID.</param>
/// <param name="filterOnNickName"><see langword="true"/> if [filter on nick name]; otherwise, <see langword="false"/>.</param>
/// <param name="nickName">Name of the nick.</param>
/// <param name="filterOnEmailAddress"><see langword="true"/> if [filter on email address]; otherwise, <see langword="false"/>.</param>
/// <param name="emailAddress">Email address.</param>
/// <returns>User objects matching the query</returns>
public static UserCollection FindUsers(bool filterOnRole, int roleID, bool filterOnNickName, string nickName, bool filterOnEmailAddress, string emailAddress)
{
var qf = new QueryFactory();
var q = qf.User
.OrderBy(UserFields.NickName.Ascending());
if(filterOnRole)
{
q.AndWhere(UserFields.UserID.In(qf.Create().Select(RoleUserFields.UserID).Where(RoleUserFields.RoleID == roleID)));
}
if(filterOnNickName)
{
q.AndWhere(UserFields.NickName.Like("%" + nickName + "%"));
}
if(filterOnEmailAddress)
{
q.AndWhere(UserFields.EmailAddress.Like("%" + emailAddress + "%"));
}
UserCollection toReturn = new UserCollection();
toReturn.GetMulti(q);
return toReturn;
}
public static FromClause NewFromClause(this QueryFactory factory, params TableReference[] tableRefs)
{
return(factory.NewFromClause(ArrayQueryHelper.NewAQ(tableRefs, false)));
}
/// <summary>
/// Gets the total number of threads in support queues. Only the count of threads which are in the forums in the list of
/// accessable forums are returned.
/// </summary>
/// <param name="accessableForums">A list of accessable forums IDs, which the user has permission to access.</param>
/// <returns>total number of threads in support queues</returns>
public static int GetTotalNumberOfThreadsInSupportQueues(List<int> accessableForums)
{
// return 0, if the user does not have a valid list of forums to access
if(accessableForums == null || accessableForums.Count <= 0)
{
return 0;
}
var qf = new QueryFactory();
var q = qf.Create()
.Select(SupportQueueThreadFields.ThreadID.Count().As("NumberOfThreadsInQueues"))
.From(qf.SupportQueueThread.InnerJoin(qf.Thread).On(SupportQueueThreadFields.ThreadID == ThreadFields.ThreadID))
.Where(ThreadFields.ForumID == accessableForums);
var dao = new TypedListDAO();
return dao.GetScalar<int>(q, null);
}
public static GroupByClause NewGroupByClause(this QueryFactory factory, params GroupingElement[] elements)
{
return(factory.NewGroupByClause(ArrayQueryHelper.NewAQ(elements, false)));
}
public static ColumnReferenceByName ColumnName(this QueryFactory factory, String columnName)
{
return(factory.ColumnName(null, columnName));
}
public static OrderByClause NewOrderByClause(this QueryFactory factory, params SortSpecification[] sortSpecs)
{
return(factory.NewOrderByClause(ArrayQueryHelper.NewAQ(sortSpecs, false)));
}
public static IQueryable <Position> FindAll()
{
return(from p in QueryFactory.CreateLinqQuery <Position>()
orderby p.Name
select p);
}
public static TableNameDirect TableNameDirect(this QueryFactory q, String tableName)
{
return(q.TableNameDirect(null, tableName));
}
/// <summary>
/// Builds the projection for a dynamic query which contains thread and statistics information.
/// </summary>
/// <param name="qf">The query factory to use.</param>
/// <returns>The fields for the projection</returns>
/// <remarks>Doesn't add the forum fields</remarks>
internal static object[] BuildQueryProjectionForAllThreadsWithStats(QueryFactory qf)
{
var toReturn = new List<object>()
{
ThreadFields.ThreadID,
ThreadFields.ForumID,
ThreadFields.Subject,
ThreadFields.StartedByUserID,
ThreadFields.ThreadLastPostingDate,
ThreadFields.IsSticky,
ThreadFields.IsClosed,
ThreadFields.MarkedAsDone,
ThreadFields.NumberOfViews,
UserFields.NickName.Source("ThreadStarterUser"),
qf.Create()
.Select(MessageFields.MessageID.Count())
.CorrelatedOver(MessageFields.ThreadID == ThreadFields.ThreadID)
.ToScalar()
.As("AmountMessages"),
UserFields.UserID.Source("LastPostingUser").As("LastPostingByUserID"),
UserFields.NickName.Source("LastPostingUser").As("NickNameLastPosting"),
MessageFields.MessageID.Source("LastMessage").As("LastMessageID")
};
return toReturn.ToArray();
}
public static TableNameDirect TableNameDirect(this QueryFactory q, String schemaName, String tableName)
{
return(q.SQLVendor.CommonFactory.TableNameDirect(schemaName, tableName));
}
/// <summary>
/// Gets all the banned users as a dataview. This is returned as a dataview because only the nicknames are required, so a dynamic list is
/// used to avoid unnecessary data fetching.
/// </summary>
/// <returns>dataview with the nicknames of the users which are banned on the useraccount: the IsBanned property is set for these users.</returns>
/// <remarks>This list of nicknames is cached in the application object so these users can be logged off by force.</remarks>
public static DataView GetAllBannedUserNicknamesAsDataView()
{
var qf = new QueryFactory();
var q = qf.Create()
.Select(UserFields.NickName)
.Where(UserFields.IsBanned == true);
var dao = new TypedListDAO();
var results = dao.FetchAsDataTable(q);
return results.DefaultView;
}
public Item FindByUid(long uid) => ReadItem(QueryFactory.Query(TableName).Where("uid", uid).FirstOrDefault());
/// <summary>
/// Gets the bookmarks with statistics for the user specified.
/// </summary>
/// <param name="userID">User ID.</param>
/// <returns></returns>
public static DataView GetBookmarksAsDataView(int userID)
{
var qf = new QueryFactory();
var q = qf.Create()
.Select(new List<object>(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf))
{
ForumFields.ForumName,
ForumFields.SectionID
}.ToArray())
.From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf)
.InnerJoin(qf.Forum).On(ThreadFields.ForumID==ForumFields.ForumID))
.Where(ThreadFields.ThreadID.In(qf.Create().Select(BookmarkFields.ThreadID).Where(BookmarkFields.UserID==userID)))
.OrderBy(ThreadFields.ThreadLastPostingDate.Descending());
var dao = new TypedListDAO();
var bookmarkedThreads = dao.FetchAsDataTable(q);
return bookmarkedThreads.DefaultView;
}
public Cube FindById(long uid) => ReadCube(QueryFactory.Query(TableName).Where("uid", uid).FirstOrDefault());
/// <summary>
/// Gets the last pageSize threads in which the user specified participated with one or more messages for the page specified.
/// Threads which aren't visible for the calling user are filtered out. If pageNumber is 0, pageSize is used to limit the list to the pageSize
/// </summary>
/// <param name="accessableForums">A list of accessable forums IDs, which the user calling the method has permission to access.</param>
/// <param name="participantUserID">The participant user ID of the user of which the threads have to be obtained.</param>
/// <param name="forumsWithThreadsFromOthers">The forums with threads from others.</param>
/// <param name="callingUserID">The calling user ID.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="pageNumber">The page number to fetch.</param>
/// <returns>a dataView of the threads requested</returns>
public static DataView GetLastThreadsForUserAsDataView(List<int> accessableForums, int participantUserID,
List<int> forumsWithThreadsFromOthers, int callingUserID, int pageSize, int pageNumber)
{
// return null, if the user does not have a valid list of forums to access
if(accessableForums == null || accessableForums.Count <= 0)
{
return null;
}
int numberOfThreadsToFetch = pageSize;
if(numberOfThreadsToFetch <= 0)
{
numberOfThreadsToFetch = 25;
}
var qf = new QueryFactory();
var q = qf.Create()
.Select(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf))
.From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf))
.Where((ThreadFields.ForumID == accessableForums)
.And(ThreadFields.ThreadID.In(qf.Create()
.Select(MessageFields.ThreadID)
.Where(MessageFields.PostedByUserID == participantUserID)))
.And(ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, callingUserID)))
.OrderBy(ThreadFields.ThreadLastPostingDate.Descending());
if(pageNumber <= 0)
{
// no paging
// get the last numberOfThreadsToFetch, so specify a limit equal to the numberOfThreadsToFetch specified
q.Limit(numberOfThreadsToFetch);
}
else
{
// use paging
q.Page(pageNumber, numberOfThreadsToFetch);
}
var dao = new TypedListDAO();
var lastThreads = dao.FetchAsDataTable(q);
return lastThreads.DefaultView;
}
public bool Delete(long uid) => QueryFactory.Query(TableName).Where("uid", uid).Delete() == 1;
public static QueryExpression CallFunction(this QueryFactory factory, SQLFunctionLiteral function)
{
return(CallFunction(factory, null, function));
}
public bool Delete(long id)
{
return(QueryFactory.Query(TableName).Where("id", id).Delete() == 1);
}
/// <summary>
/// Gets the support queue of the thread with the threadID specified.
/// </summary>
/// <param name="threadID">The thread ID.</param>
/// <param name="trans">The transaction currently in progress. Can be null if no transaction is in progress.</param>
/// <returns>
/// The requested supportqueue entity, or null if the thread isn't in a support queue.
/// </returns>
public static SupportQueueEntity GetQueueOfThread(int threadID, Transaction trans)
{
// the relation supportqueue - thread is stored in a SupportQueueThread entity. Use that entity as a filter for the support queue. If
// that entity doesn't exist, the thread isn't in a supportqueue.
var qf = new QueryFactory();
var q = qf.SupportQueueThread
.From(QueryTarget.InnerJoin(qf.SupportQueue).On(SupportQueueThreadFields.QueueID == SupportQueueFields.QueueID))
.Where(SupportQueueThreadFields.ThreadID == threadID);
// use a supportqueue collection to fetch the support queue, which will contain 0 or 1 entities after the fetch
SupportQueueCollection supportQueues = new SupportQueueCollection();
// if a transaction has been specified, we've to add the collection to the transaction so the fetch takes place inside the same transaction so no
// deadlocks occur on sqlserver
if(trans != null)
{
trans.Add(supportQueues);
}
supportQueues.GetMulti(q);
if(supportQueues.Count > 0)
{
// in a queue, return the instance
return supportQueues[0];
}
else
{
// not in a queue, return null
return null;
}
}
/// <summary>
/// Gets all forum entities which belong to a given section.
/// </summary>
/// <param name="sectionID">The section ID from which forums should be retrieved</param>
/// <returns>Entity collection with entities for all forums in this section sorted alphabitacally</returns>
public static ForumCollection GetAllForumsInSection(int sectionID)
{
var q = new QueryFactory().Forum
.Where(ForumFields.SectionID == sectionID)
.OrderBy(ForumFields.OrderNo.Ascending(), ForumFields.ForumName.Ascending());
var toReturn = new ForumCollection();
toReturn.GetMulti(q);
return toReturn;
}
public string Index()
{
if (isRuning == true)
{
return("正在运行中");
}
lock (lockObj)
{
isRuning = true;
try
{
var repositoryNSFactory = RepositoryFactory.Create <ProductSaleByDayNSEntity>();
using (var tran = DBTool.BeginTransaction())
{
if (repositoryNSFactory.GetList(m => m.CreateDate > DateTime.Now.Date).Count > 100)
{
tran.Complete();
return("");
}
}
var shopRepository = RepositoryFactory.Create <ShopEntity>();
shopRepository.Delete(QueryFactory.Create <ShopEntity>());
using (var tran = DBTool.BeginTransaction())
{
for (int i = 0; i < 10; i++)
{
var shop = new ShopEntity
{
SysNo = Guid.NewGuid(),
ShopCode = "00" + i,
ShopName = "测试店铺" + i,
ShopType = i % 3,
IsDelete = false,
};
shopRepository.Add(shop);
}
lstShop = shopRepository.GetList(QueryFactory.Create <ShopEntity>());
tran.Complete();
}
var importGroupId = Guid.NewGuid();
var random = new Random();
repositoryNSFactory.Delete(QueryFactory.Create <ProductSaleByDayNSEntity>());
var tempDate = new DateTime(2018, 1, 1);
while (tempDate <= DateTime.Now)
{
using (var tran = DBTool.BeginTransaction())
{
foreach (var p in dicProduct)
{
var tempNS = new ProductSaleByDayNSEntity();
tempNS.SysNo = Guid.NewGuid();
tempNS.DataSource = lstDataSource[random.Next(lstDataSource.Count)];
var shop = lstShop[random.Next(lstShop.Count)];
tempNS.ShopID = shop.SysNo;
tempNS.ProductID = p.Key;
tempNS.OutProductID = p.Value;
tempNS.ProductName = p.Value;
tempNS.Sales = random.Next(100000);
tempNS.StatisticalDate = tempDate;
tempNS.CreateDate = DateTime.Now;
tempNS.CreateUserID = Guid.NewGuid();
tempNS.ImportGroupId = importGroupId;
repositoryNSFactory.Add(tempNS);
}
tempDate = tempDate.AddDays(1);
tran.Complete();
}
}
}
finally
{
isRuning = false;
}
return("初始化成功");
}
}
/// <summary>
/// Retrieves for all available sections all forums with all relevant statistical information. This information is stored per forum in a
/// DataView which is stored in the returned Dictionary, with the SectionID where the forum is located in as Key.
/// </summary>
/// <param name="availableSections">SectionCollection with all available sections</param>
/// <param name="accessableForums">List of accessable forums IDs.</param>
/// <param name="forumsWithOnlyOwnThreads">The forums for which the calling user can view other users' threads. Can be null</param>
/// <param name="userID">The userid of the calling user.</param>
/// <returns>
/// Dictionary with per key (sectionID) a dataview with forum information of all the forums in that section.
/// </returns>
/// <remarks>Uses dataviews because a dynamic list is executed to retrieve the information for the forums, which include aggregate info about
/// # of posts.</remarks>
public static Dictionary<int, DataView> GetAllAvailableForumsDataViews(SectionCollection availableSections, List<int> accessableForums,
List<int> forumsWithThreadsFromOthers, int userID)
{
Dictionary<int, DataView> toReturn = new Dictionary<int, DataView>();
// return an empty list, if the user does not have a valid list of forums to access
if (accessableForums == null || accessableForums.Count <= 0)
{
return toReturn;
}
// fetch all forums with statistics in a dynamic list, while filtering on the list of accessable forums for this user.
// Create the filter separate of the query itself, as it's re-used multiple times.
IPredicateExpression threadFilter = ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, userID);
var qf = new QueryFactory();
var q = qf.Create()
.Select(ForumFields.ForumID,
ForumFields.ForumName,
ForumFields.ForumDescription,
ForumFields.ForumLastPostingDate,
// add a scalar query which retrieves the # of threads in the specific forum.
// this will result in the query:
// (
// SELECT COUNT(ThreadID) FROM Thread
// WHERE ForumID = Forum.ForumID AND threadfilter.
// ) As AmountThreads
qf.Create()
.Select(ThreadFields.ThreadID.Count())
.CorrelatedOver(ThreadFields.ForumID == ForumFields.ForumID)
.Where(threadFilter)
.ToScalar().As("AmountThreads"),
// add a scalar query which retrieves the # of messages in the threads of this forum.
// this will result in the query:
// (
// SELECT COUNT(MessageID) FROM Message
// WHERE ThreadID IN
// (
// SELECT ThreadID FROM Thread WHERE ForumID = Forum.ForumID AND threadfilter
// )
// ) AS AmountMessages
qf.Create()
.Select(MessageFields.MessageID.Count())
.Where(MessageFields.ThreadID.In(
qf.Create()
.Select(ThreadFields.ThreadID)
.CorrelatedOver(ThreadFields.ForumID == ForumFields.ForumID)
.Where(threadFilter)))
.ToScalar().As("AmountMessages"),
ForumFields.HasRSSFeed,
ForumFields.SectionID)
.Where(ForumFields.ForumID == accessableForums)
.OrderBy(ForumFields.OrderNo.Ascending(), ForumFields.ForumName.Ascending());
var results = new TypedListDAO().FetchAsDataTable(q);
// Now per section create a new DataView in memory using in-memory filtering on the DataTable.
foreach(SectionEntity section in availableSections)
{
// Create view for current section and filter out rows we don't want. Do this with in-memory filtering of the dataview, so we don't
// have to execute multiple queries.
DataView forumsInSection = new DataView(results, "SectionID=" + section.SectionID, string.Empty, DataViewRowState.CurrentRows);
// add to sorted list with SectionID as key
toReturn.Add(section.SectionID, forumsInSection);
}
// return the dictionary
return toReturn;
}
public void QueryTypeTest()
{
mocks = new MockRepository();
QueryType queryType = QueryType.RemoteSparqlStore; // TODO: Initialize to an appropriate value
TripleStore ts = new TripleStore("http://www.tempuri.com");
IRdfContext context = mocks.CreateMock<RdfDataContext>(ts);
QueryFactory<Task> target = new QueryFactory<Task>(queryType, context);
QueryType val = QueryType.RemoteSparqlStore; // TODO: Assign to an appropriate value for the property
Assert.AreEqual(val, target.QueryType, "LinqToRdf.QueryFactory<T>.QueryType was not set correctly.");
// Assert.Fail("Generics testing must be manually provided.");
}
/// <summary>
/// Sends email to all users subscribed to a specified thread, except the user who initiated the thread update.
/// </summary>
/// <param name="threadID">The thread that was updated.</param>
/// <param name="initiatedByUserID">The user who initiated the update (who will not receive notification).</param>
private static void SendThreadReplyNotifications(int threadID, int initiatedByUserID, string emailTemplate,
Dictionary<string, string> emailData)
{
// get list of subscribers to thread, minus the initiator. Do this by fetching the subscriptions plus the related user entity entity instances.
// The related user entities are loaded using a prefetch path.
var qf = new QueryFactory();
var q = qf.ThreadSubscription
.Where((ThreadSubscriptionFields.ThreadID == threadID).And(ThreadSubscriptionFields.UserID != initiatedByUserID))
.WithPath(ThreadSubscriptionEntity.PrefetchPathUser);
ThreadSubscriptionCollection subscriptions = new ThreadSubscriptionCollection();
subscriptions.GetMulti(q);
if(subscriptions.Count <= 0)
{
// no subscriptions, nothing to do
return;
}
// now collect all email addresses into an array so we can pass that to the email routine.
string[] toAddresses = new string[subscriptions.Count];
for(int i=0;i<subscriptions.Count;i++)
{
toAddresses[i] = subscriptions[i].User.EmailAddress;
}
// use template to construct message to send.
StringBuilder mailBody = new StringBuilder(emailTemplate);
string applicationURL = string.Empty;
emailData.TryGetValue("applicationURL", out applicationURL);
if (!string.IsNullOrEmpty(emailTemplate))
{
// Use the existing template to format the body
string siteName = string.Empty;
emailData.TryGetValue("siteName", out siteName);
string threadSubject = string.Empty;
string threadUrl = string.Empty;
ThreadEntity thread = ThreadGuiHelper.GetThread(threadID);
if(thread != null)
{
threadSubject = thread.Subject;
threadUrl = applicationURL + string.Format("Messages.aspx?ThreadID={0}", thread.ThreadID);
}
else
{
// thread doesn't exist, exit
return;
}
mailBody.Replace("[SiteURL]", applicationURL);
mailBody.Replace("[SiteName]", siteName);
mailBody.Replace("[ThreadSubject]", threadSubject);
mailBody.Replace("[ThreadURL]", threadUrl);
}
// format the subject
string subject = string.Empty;
emailData.TryGetValue("emailThreadNotificationSubject", out subject);
subject += applicationURL;
string fromAddress = string.Empty;
emailData.TryGetValue("defaultFromEmailAddress", out fromAddress);
try
{
//send message
HnDGeneralUtils.SendEmail(subject, mailBody.ToString(), fromAddress, toAddresses, emailData, true);
}
catch(SmtpFailedRecipientsException)
{
// swallow as it shouldn't have any effect on further operations
}
catch(SmtpException)
{
// swallow, as there's nothing we can do
}
// rest: problematic, so bubble upwards.
}
public void CreateExpressionTranslatorTest()
{
TripleStore ts = CreateOnlineSparqlTripleStore();
IRdfContext context = new RdfDataContext(ts);
QueryFactory<Track> factory = new QueryFactory<Track>(ts.QueryType, context);
Assert.AreEqual(factory.QueryType, ts.QueryType);
IQueryFormatTranslator translator = factory.CreateExpressionTranslator();
Assert.IsNotNull(translator);
Assert.IsTrue(translator is LinqToSparqlExpTranslator<Track>);
}
public static OrdinaryGroupingSet NewGroupingElement(this QueryFactory factory, params NonBooleanExpression[] expressions)
{
return(factory.NewGroupingElement(ArrayQueryHelper.NewAQ(expressions, false)));
}
/// <summary>
/// Returns a DataView object that contains a complete list of threads list for
/// the requested forum and required date & time interval
/// </summary>
/// <param name="forumID">ID of Forum for which the Threadlist is required</param>
/// <param name="limiter">Limits the Threadlist to between now and; last 48 Hrs, Last Week, Last Month, Last Year</param>
/// <param name="minNumberOfThreadsToFetch">The minimum number of threads to fetch if there are less threads available in the limiter interval</param>
/// <param name="minNumberOfNonStickyVisibleThreads">The minimum number of non-sticky visible threads to show. If the # of threads is lower than
/// this number (due to the limiter value), the minNumberOfThreadsToFetch are fetched</param>
/// <param name="canViewNormalThreadsStartedByOthers">If set to true, the user calling the method has the right to view threads started by others.
/// Otherwise only the threads started by the user calling the method are returned.</param>
/// <param name="userID">The userid of the user calling the method.</param>
/// <returns>DataView with all the threads</returns>
public static DataView GetAllThreadsInForumAsDataView(int forumID, ThreadListInterval limiter, short minNumberOfThreadsToFetch,
short minNumberOfNonStickyVisibleThreads, bool canViewNormalThreadsStartedByOthers, int userID)
{
DateTime limiterDate;
// convert the limiter enum to a datetime which we can use in the filters on the thread data, where we'll use the limiter date
// as a filter for the last posting date of a post in a given thread.
switch (limiter)
{
case ThreadListInterval.Last24Hours:
limiterDate = DateTime.Today.AddHours(-24);
break;
case ThreadListInterval.Last48Hours:
limiterDate = DateTime.Today.AddHours(-48);
break;
case ThreadListInterval.LastWeek:
limiterDate = DateTime.Today.AddDays(-7);
break;
case ThreadListInterval.LastMonth:
limiterDate = DateTime.Today.AddMonths(-1);
break;
case ThreadListInterval.LastYear:
limiterDate = DateTime.Today.AddYears(-1);
break;
default:
limiterDate = DateTime.Today.AddHours(-48);
break;
}
var qf = new QueryFactory();
var q = qf.Create();
q.Select(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf));
q.From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf));
q.Where(((ThreadFields.IsSticky == true).Or(ThreadFields.ThreadLastPostingDate >= limiterDate)).And(ThreadFields.ForumID == forumID));
// if the user can't view threads started by others, filter out threads started by users different from userID
if(!canViewNormalThreadsStartedByOthers)
{
// caller can't view threads started by others: add a filter so that threads not started by calling user aren't enlisted.
// however sticky threads are always returned so the filter contains a check so the limit is only applied on threads which aren't sticky
// add a filter for sticky threads, add it with 'OR', so sticky threads are always accepted
q.AndWhere((ThreadFields.StartedByUserID == userID).Or(ThreadFields.IsSticky == true));
}
q.OrderBy(ThreadFields.IsSticky.Descending(), ThreadFields.IsClosed.Ascending(), ThreadFields.ThreadLastPostingDate.Descending());
var dao = new TypedListDAO();
var threads = dao.FetchAsDataTable(q);
// count # non-sticky threads. If it's below a given minimum, refetch everything, but now don't fetch on date filtered but at least the
// set minimum. Do this ONLY if the user can view other user's threads. If that's NOT the case, don't refetch anything.
DataView stickyThreads = new DataView(threads, ThreadFieldIndex.IsSticky.ToString() + "=false", "", DataViewRowState.CurrentRows);
if((stickyThreads.Count < minNumberOfNonStickyVisibleThreads) && canViewNormalThreadsStartedByOthers)
{
// not enough threads available, fetch again,
// first fetch the sticky threads.
q = qf.Create();
q.Select(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf));
q.From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf));
q.Where((ThreadFields.IsSticky == true).And(ThreadFields.ForumID == forumID));
q.OrderBy(ThreadFields.ThreadLastPostingDate.Descending());
threads = dao.FetchAsDataTable(q);
// then fetch the rest. Fetch it into the same datatable object to append the rows to the already fetched sticky threads (if any)
q = qf.Create();
q.Select(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf));
q.From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf));
q.Where((ThreadFields.IsSticky == false).And(ThreadFields.ForumID == forumID));
q.Limit(minNumberOfThreadsToFetch);
q.OrderBy(ThreadFields.ThreadLastPostingDate.Descending());
dao.FetchAsDataTable(q, threads);
// sort closed threads to the bottom. Do this in-memory as it's a sort operation after projection. Doing it on the server would mean
// a sort operation before projection.
return new DataView(threads, string.Empty, ThreadFieldIndex.IsClosed.ToString() + " ASC", DataViewRowState.CurrentRows);
}
else
{
return threads.DefaultView;
}
}
private void TestTranUpdate(bool isCommit)
{
var repository = GetRepository();
var queryCount = QueryFactory.Create <ProductSaleByDayNSEntity>();
queryCount.And(m => m.DataSource == "测试来源修改");
var preCount = repository.Count(queryCount);
ProductSaleByDayNSEntity model;
using (var tran = DBTool.BeginTransaction())
{
var query = QueryFactory.Create <ProductSaleByDayNSEntity>();
query.And(m => m.DataSource != "测试来源修改");
query.OrderByDescing(m => m.StatisticalDate);
query.StarSize = new Random().Next(5);
query.Rows = 1;
model = repository.GetPaging(query).ToList()[0];
model.DataSource = "测试来源修改";
model.ProductName = "测试商品修改";
//根据主键更新其他字段
var r = repository.Update(model);
Assert.True(r);
var nextCount = repository.Count(queryCount);
Assert.AreEqual(preCount + 1, nextCount);
var entity = repository.Get(new ProductSaleByDayNSEntity {
SysNo = model.SysNo
});
Assert.NotNull(entity);
Assert.AreEqual(model.SysNo, entity.SysNo);
Assert.AreEqual(model.DataSource, entity.DataSource);
Assert.AreEqual(model.ProductName, entity.ProductName);
if (isCommit)
{
tran.Complete();
}
}
if (isCommit)
{
var nextCount = repository.Count(queryCount);
Assert.AreEqual(preCount + 1, nextCount);
var entity = repository.Get(new ProductSaleByDayNSEntity {
SysNo = model.SysNo
});
Assert.NotNull(entity);
Assert.AreEqual(model.SysNo, entity.SysNo);
Assert.AreEqual(model.DataSource, entity.DataSource);
Assert.AreEqual(model.ProductName, entity.ProductName);
}
else
{
var nextCount = repository.Count(queryCount);
Assert.AreEqual(preCount, nextCount);
var entity = repository.Get(new ProductSaleByDayNSEntity {
SysNo = model.SysNo
});
Assert.NotNull(entity);
Assert.AreEqual(model.SysNo, entity.SysNo);
Assert.AreNotEqual(model.DataSource, entity.DataSource);
Assert.AreNotEqual(model.ProductName, entity.ProductName);
}
}
/// <summary>
/// Gets the last message in thread, and prefetches the user + usertitle entities.
/// </summary>
/// <param name="threadID">Thread ID.</param>
/// <returns>fetched messageentity with the userentity + usertitle entity fetched as well of the user who posted the message.</returns>
public static MessageEntity GetLastMessageInThreadWithUserInfo(int threadID)
{
var qf = new QueryFactory();
var q = qf.Message
.Where(MessageFields.MessageID.Equal(
qf.Create()
.Select(MessageFields.MessageID.Source("LastMessage"))
.Where((MessageFields.ThreadID == MessageFields.ThreadID.Source("LastMessage"))
.And(MessageFields.ThreadID.Source("LastMessage")==threadID))
.Limit(1)
.OrderBy(MessageFields.PostingDate.Source("LastMessage").Descending())
.ToScalar()
.ForceRowLimit()))
.WithPath(MessageEntity.PrefetchPathPostedByUser.WithSubPath(UserEntity.PrefetchPathUserTitle));
MessageCollection messages = new MessageCollection();
messages.GetMulti(q);
if(messages.Count<=0)
{
// not found
return null;
}
return messages[0];
}
/// <summary>
/// Will return the StartMessageNo for including it in the URL when redirecting to a page with messages in the given
/// thread. The page started with StartMessageNo will contain the message with ID messageID. Paging is done using the
/// maxAmountMessagesPerPage property in Application.
/// </summary>
/// <param name="threadID">ID of the thread to which the messages belong</param>
/// <param name="messageID"></param>
/// <returns></returns>
public static int GetStartAtMessageForGivenMessageAndThread(int threadID, int messageID, int maxAmountMessagesPerPage)
{
var qf = new QueryFactory();
var q = qf.Create()
.Select(MessageFields.MessageID)
.Where(MessageFields.ThreadID == threadID)
.OrderBy(MessageFields.PostingDate.Ascending())
.Distinct();
var dao = new TypedListDAO();
var dynamicList = dao.FetchAsDataTable(q);
int startAtMessage = 0;
int rowIndex = 0;
if (dynamicList.Rows.Count > 0)
{
// there are messages. Find the row with messageID. There can be only one row with this messageID
for (int i = 0; i < dynamicList.Rows.Count; i++)
{
if (((int)dynamicList.Rows[i]["MessageID"]) == messageID)
{
// found the row
rowIndex = i;
break;
}
}
}
startAtMessage = (rowIndex / maxAmountMessagesPerPage) * maxAmountMessagesPerPage;
// done
return startAtMessage;
}
private static void FetchKeysForIndividualFetches()
{
var qf = new QueryFactory();
var q = qf.SalesOrderHeader
.Select(() => SalesOrderHeaderFields.SalesOrderId.ToValue<int>())
.Limit(IndividualKeysAmount);
KeysForIndividualFetches = new DataAccessAdapter().FetchQuery(q);
if (KeysForIndividualFetches.Count < IndividualKeysAmount)
{
throw new InvalidOperationException("Can't fetch the keys for the individual benchmarks");
}
}
public UserModel getUser(string accountNumber)
{
var db = new QueryFactory(connection, new MySqlCompiler());
return(db.Query("users").Where("account_number", accountNumber).First <UserModel>());
}
