/*
Kooboo is a content management system based on ASP.NET MVC framework. Copyright 2009 Yardi Technology Limited.
This program is free software: you can redistribute it and/or modify it under the terms of the
GNU General Public License version 3 as published by the Free Software Foundation.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program.
If not, see http://www.kooboo.com/gpl3/.
*/
using System;
using System.Data;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.Data.Extensions;
using Everest.Library;
using Everest.Library.Data;
using Everest.Library.Data.Entity;
using Everest.Library.ExtensionMethod;
using Everest.CmsServices.Models;
using Everest.CmsServices.Services;
using Everest.CmsServices.DataRule;
using Everest.Library.Providers.Logging;
namespace Everest.CmsServices.Providers.SqlServer{
public class SqlServerBinaryContentManager : IBinaryContentManager
{
#region IBinaryContentManager Members
public virtual IEnumerable<IDictionary<string, object>> QueryContents(IEverestCmsDataContext dataContext, Cms_Folder folder,
IEnumerable<Cms_Folder> folders, string queryStatement, IDictionary<string, object> parameterValues, string userName, bool? isProcessed, string orderBy, int startIndex, int limit)
{
if (startIndex < 1)
{
startIndex = 1;
}
if (limit == int.MaxValue)
{
limit = limit - 1;
}
if (StringExtensions.IsNullOrEmptyTrim(orderBy))
{
orderBy = "ContentId DESC,HistoryId DESC";
}
var dbCommand = dataContext.ObjectContext.CreateStoreCommand("*");
StringBuilder queryExpression = new StringBuilder();
ContentProviderHelper.CombineUserCondition(queryStatement, parameterValues, dbCommand, queryExpression);
queryExpression.AppendFormat(" AND {0}", ContentProviderHelper.FoldersQueryExpresion(folders, dbCommand));
dbCommand.CreateParameter("FolderUUID", DbType.Guid, folder.UUID);
dbCommand.CreateParameter("SchemaUUID", DbType.Guid, CachedData.GetSchemaByFolder(folder.UUID).UUID);
dbCommand.CreateParameter("startIndex", DbType.Int32, startIndex);
dbCommand.CreateParameter("limit", DbType.Int32, limit);
string workflowQueryExpression = SqlServerTextContentManager.WorkflowCondition(userName, isProcessed);
dbCommand.CommandText = BuildQueryContentsSQL(queryExpression.ToString(), workflowQueryExpression, orderBy);
SqlServerContentProvider.Log("SqlServerBinaryContentManager.QueryContents", dbCommand);
using (dataContext.ObjectContext.Connection.CreateConnectionScope())
{
IDataReader dataReader = dbCommand.ExecuteReader();
List<IDictionary<string, object>> list = new List<IDictionary<string, object>>();
while (dataReader.Read())
{
IDictionary<string, object> dic = SqlServerTextContentManager.DataReaderToDictionary(dataReader, folder);
list.Add(dic);
}
return list;
}
}
protected virtual string BuildQueryContentsSQL(string queryExpression, string workflowQueryExpression, string orderBy)
{
string sql = @"
SELECT *
FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY {2}) AS RowIndex
FROM (SELECT WorkflowContent.ContentId,WorkflowContent.UUID,WorkflowContent.ParentUUID,WorkflowContent.Title,WorkflowContent.UserKey,
WorkflowContent.FolderUUID,WorkflowContent.SchemaUUID,WorkflowContent.ApplicationId,
WorkflowContent.UserName,WorkflowContent.BaseUUID,WorkflowContent.ModifiedDate,WorkflowContent.PostDate,
WorkflowContent.ContentStatus,WorkflowContent.ProcessResult,WorkflowContent.PreResult,WorkflowContent.RoleName,
WorkflowContent.ProcessUserName,WorkflowContent.WorkflowHistoryUUID,WorkflowContent.HistoryId,WorkflowContent.StepName,
WorkflowContent.SequenceOrder,WorkflowContent.StartSequenceOrder,WorkflowContent.OriginalUUID,
(SELECT TOP 1 FolderUUID
FROM Cms_ContentInFolder
WHERE ContentUUID= WorkflowContent.UUID AND FolderUUID=@FolderUUID
) AS IncludedByFolderUUID,
(SELECT ApplicationName
FROM aspnet_Applications
WHERE ApplicationId = WorkflowContent.ApplicationId
) AS [Application],
BinaryContent.FilePath,
BinaryContent.FileSize
FROM (SELECT MAX(A.HistoryId) AS GroupedHistoryId,MAX(A.ContentID) AS GroupedContentID
FROM Cms_View_Workflow_Content A,
(SELECT MAX(Cms_Content.FolderLevel) AS MaxFolderLevel,OriginalUUID AS MaxOriginalUUID
FROM Cms_Content WHERE 1=1 {0} Group By OriginalUUID
) C
WHERE A.FolderLevel = C.MaxFolderLevel AND A.OriginalUUID=C.MaxOriginalUUID {1}
GROUP BY A.ContentID
)Grouped,
(SELECT * FROM Cms_View_Workflow_Content WHERE SchemaUUID=@SchemaUUID) WorkflowContent,
Cms_BinaryContent BinaryContent
WHERE WorkflowContent.ContentId = Grouped.GroupedContentID AND WorkflowContent.UUID = BinaryContent.ContentUUID
AND (WorkflowContent.HistoryId = Grouped.GroupedHistoryId OR (WorkflowContent.HistoryId IS NULL AND Grouped.GroupedHistoryId IS NULL))
)D
)E
WHERE (RowIndex >= @startIndex) AND (RowIndex < @startIndex + @limit)";
return string.Format(sql, queryExpression.ToString(), workflowQueryExpression, orderBy);
}
public virtual int CountContents(IEverestCmsDataContext dataContext, Cms_Folder folder, IEnumerable<Cms_Folder> folders, string queryStatement, IDictionary<string, object> parameterValues,
string userName, bool? isProcessed)
{
DbCommand dbCommand = dataContext.ObjectContext.CreateStoreCommand("A");
StringBuilder queryExpression = new StringBuilder();
ContentProviderHelper.CombineUserCondition(queryStatement, parameterValues, dbCommand, queryExpression);
queryExpression.AppendFormat(" AND {0}", ContentProviderHelper.FoldersQueryExpresion(folders, dbCommand));
var workflowCondition = SqlServerTextContentManager.WorkflowCondition(userName, isProcessed);
dbCommand.CommandText = BuildCountContentSQL(queryExpression, workflowCondition);
SqlServerContentProvider.Log("SqlServerBinaryContentManager.CountContents", dbCommand);
using (dbCommand.Connection.CreateConnectionScope())
{
object o = dbCommand.ExecuteScalar();
if (o != null)
{
return int.Parse(o.ToString());
}
return 0;
}
}
protected virtual string BuildCountContentSQL(StringBuilder stringBuilder, string workflowCondition)
{
return string.Format(@"
SELECT COUNT(distinct OriginalUUID)
FROM Cms_View_Workflow_Content
WHERE 1=1 {0} {1}
"
, stringBuilder.ToString(), workflowCondition);
}
#endregion
}
}
|