/*
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 SqlServerTextContentManager : ITextContentManager
{
#region Schema Manage
#region Delete Schema
protected virtual string DDL_DropTable
{
get
{
return @"
IF EXISTS (SELECT *
FROM sys.foreign_keys
WHERE object_id = OBJECT_ID(N'[FK_{0}_Cms_Content]') AND parent_object_id = OBJECT_ID(N'[{0}]'))
ALTER TABLE [{0}] DROP CONSTRAINT [FK_{0}_Cms_Content]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}]') AND type in (N'U'))
DROP TABLE [{0}]";
}
}
/// <summary>
/// Deletes the schema.
/// </summary>
/// <param name="dataContext">The data context.</param>
/// <param name="schema">The schema.</param>
public virtual void DeleteSchema(IEverestCmsDataContext dataContext, Cms_Schema schema)
{
schema.aspnet_ApplicationsReference.Load(schema.aspnet_Applications, schema.EntityState);
string appName = schema.aspnet_Applications.ApplicationName;
string ddl = string.Format(DDL_DropTable, ContentProviderHelper.GetTableName(appName, schema.SchemaName));
DbCommand dbCommand = dataContext.ObjectContext.CreateStoreCommand(ddl);
SqlServerContentProvider.Log("SqlServerTextContentManager.DeleteSchema", dbCommand);
using (dbCommand.Connection.CreateConnectionScope())
{
var dbTrans = dataContext.ObjectContext.GetCurrentStoreTransaction();
if (dbTrans != null)
{
dbCommand.Transaction = dbTrans;
}
dbCommand.ExecuteNonQuery();
}
}
#endregion
#region Create Schema
protected virtual string DDL_CreateTable
{
get
{
return @"
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_{0}_Cms_Content]') AND parent_object_id = OBJECT_ID(N'[{0}]'))
ALTER TABLE [{0}] DROP CONSTRAINT [FK_{0}_Cms_Content]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}]') AND type in (N'U'))
DROP TABLE [{0}]
CREATE TABLE [{0}](
[ContentUUID] [uniqueidentifier] NOT NULL
{1}
CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED
(
[ContentUUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [{0}] WITH CHECK ADD CONSTRAINT [FK_{0}_Cms_Content] FOREIGN KEY([ContentUUID])
REFERENCES [Cms_Content] ([UUID]) ON DELETE CASCADE
ALTER TABLE [{0}] CHECK CONSTRAINT [FK_{0}_Cms_Content]
{2}";
}
}
protected virtual string DDL_Column
{
get
{
return ",[{0}] {1} {2} NULL";
}
}
protected virtual string DDL_CreateIndex
{
get
{
return @"
IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_{0}_{1}')
DROP INDEX IX_{0}_{1} ON [{0}];
CREATE INDEX IX_{0}_{1} ON [{0}] ([{1}] {2});
";
}
}
/// <summary>
/// Creates the schema.
/// </summary>
/// <param name="dataContext">The data context.</param>
/// <param name="schema">The schema.</param>
public virtual void CreateSchema(IEverestCmsDataContext dataContext, Cms_Schema schema)
{
schema.aspnet_ApplicationsReference.Load(schema.aspnet_Applications, schema.EntityState);
string appName = schema.aspnet_Applications.ApplicationName;
string ddl = GenereateCreateTableDDL(schema, appName);
DbCommand dbCommand = dataContext.ObjectContext.CreateStoreCommand(ddl);
SqlServerContentProvider.Log("SqlServerTextContentManager.CreateSchema", dbCommand);
using (dbCommand.Connection.CreateConnectionScope())
{
var dbTrans = dataContext.ObjectContext.GetCurrentStoreTransaction();
if (dbTrans != null)
{
dbCommand.Transaction = dbTrans;
}
dbCommand.ExecuteNonQuery();
}
}
protected virtual string GenereateCreateTableDDL(Cms_Schema schema, string appName)
{
string createIndex_DDL;
string columns = ColumnsDDL(schema, out createIndex_DDL);
string ddl = string.Format(DDL_CreateTable, ContentProviderHelper.GetTableName(appName, schema.SchemaName), columns, createIndex_DDL);
return ddl;
}
protected virtual string ColumnsDDL(Cms_Schema schema, out string createIndex_DDL)
{
StringBuilder stringBuilder = new StringBuilder();
StringBuilder createIndexBuilder = new StringBuilder();
if (!schema.Cms_Column.IsLoaded)
{
schema.Cms_Column.Load();
}
foreach (var item in schema.DynamicTableColumns)
{
stringBuilder.AppendFormat(DDL_Column, item.ColumnName, ColumnType(item), item.AllowNull != null && !item.AllowNull.Value ? "NOT" : "");
SortOrder sortOrder = (SortOrder)item.SortOrder;
if (sortOrder != SortOrder.Unspecified)
{
string strSortOrder = sortOrder == SortOrder.Ascending ? "ASC" : "DESC";
createIndexBuilder.AppendFormat(DDL_CreateIndex, ContentProviderHelper.GetTableName(schema.aspnet_Applications.ApplicationName, schema.SchemaName)
, item.ColumnName, strSortOrder);
}
}
createIndex_DDL = createIndexBuilder.ToString();
return stringBuilder.ToString();
}
#endregion
protected virtual string ColumnType(Cms_Column column)
{
ColumnDataType dataType = ((ColumnDataType)Enum.Parse(typeof(ColumnDataType), column.DataType, true));
switch (dataType)
{
case ColumnDataType.Int:
return "[int]";
case ColumnDataType.String:
//why it have Length???
//why to use nvarchar(MAX)??
// nvarchar(max) can not create index.
if (column.Length != null)
{
return string.Format("[nvarchar] ({0})", column.Length.Value);
}
else
return "[nvarchar] (MAX)";
case ColumnDataType.Bool:
return "[bit]";
case ColumnDataType.Decimal:
return "[decimal] (9, 2)";
case ColumnDataType.Datetime:
return "[datetime]";
default:
return string.Format("[{0}]", dataType.ToString());
}
}
#region Update Schema
const string DDL_AlterColumn = "ALTER TABLE [{0}] ALTER COLUMN [{1}] {2} {3} NULL;";
const string DDL_RenameColumn = "EXEC sp_rename N'[{0}].{1}', N'{2}', N'COLUMN';";
const string DDL_RenameTable = "EXEC sp_rename N'[{0}]', N'{1}';";
const string DDL_AddColumn = "ALTER TABLE [{0}] ADD [{1}] {2} {3} NULL;";
const string DDL_DropColumn = "ALTER TABLE [{0}] DROP COLUMN [{1}];";
const string DDL_DropIndex = @"IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_{0}_{1}')
DROP INDEX IX_{0}_{1} ON [{0}];";
public virtual void UpdateSchema(IEverestCmsDataContext dataContext, Cms_Schema schema, string olderSchema, List<Cms_Column> olderColumns)
{
string update_ddl = UpdateSchemaDDL(schema, olderSchema, olderColumns);
if (!string.IsNullOrEmpty(update_ddl))
{
DbCommand dbCommand = dataContext.ObjectContext.CreateStoreCommand(update_ddl);
SqlServerContentProvider.Log("SqlServerTextContentManager.UpdateSchema", dbCommand);
using (dbCommand.Connection.CreateConnectionScope())
{
var dbTrans = dataContext.ObjectContext.GetCurrentStoreTransaction();
if (dbTrans != null)
{
dbCommand.Transaction = dbTrans;
}
dbCommand.ExecuteNonQuery();
}
}
}
protected virtual string UpdateSchemaDDL(Cms_Schema schema, string olderSchema, List<Cms_Column> olderColumns)
{
StringBuilder stringBuilder = new StringBuilder();
List<Cms_Column> oldColumns = new List<Cms_Column>(olderColumns);
if (!schema.aspnet_ApplicationsReference.IsLoaded)
{
schema.aspnet_ApplicationsReference.Load();
}
if (!schema.SchemaName.Equals(olderSchema, StringComparison.OrdinalIgnoreCase))
{
stringBuilder.AppendFormat(DDL_RenameTable, ContentProviderHelper.GetTableName(schema.aspnet_Applications.ApplicationName, olderSchema)
, ContentProviderHelper.GetTableName(schema.aspnet_Applications.ApplicationName, schema.SchemaName));
}
foreach (var item in schema.DynamicTableColumns)
{
var oldColumn = oldColumns.Where(c => c.OriginalUUID == item.OriginalUUID).FirstOrDefault();
string isNull = item.AllowNull != null && !item.AllowNull.Value ? "NOT" : "";
if (oldColumn != null)
{
stringBuilder.AppendFormat(DDL_AlterColumn, ContentProviderHelper.GetTableName(schema.aspnet_Applications.ApplicationName, schema.SchemaName),
oldColumn.ColumnName, ColumnType(item), isNull);
if (item.SortOrder != (int)SortOrder.Unspecified)
{
string strSortOrder = (SortOrder)item.SortOrder == SortOrder.Ascending ? "ASC" : "DESC";
stringBuilder.AppendFormat(DDL_CreateIndex, ContentProviderHelper.GetTableName(schema.aspnet_Applications.ApplicationName, schema.SchemaName)
, item.ColumnName, strSortOrder);
}
if (oldColumn.ColumnName != item.ColumnName)
{
stringBuilder.AppendFormat(DDL_RenameColumn, ContentProviderHelper.GetTableName(schema.aspnet_Applications.ApplicationName, schema.SchemaName),
oldColumn.ColumnName, item.ColumnName);
}
//remove the column from the temp column list
oldColumns.Remove(oldColumn);
}
else
{
stringBuilder.AppendFormat(DDL_AddColumn, ContentProviderHelper.GetTableName(schema.aspnet_Applications.ApplicationName, schema.SchemaName)
, item.ColumnName, ColumnType(item), isNull);
if (item.SortOrder != (int)SortOrder.Unspecified)
{
string strSortOrder = (SortOrder)item.SortOrder == SortOrder.Ascending ? "ASC" : "DESC";
stringBuilder.AppendFormat(DDL_CreateIndex, ContentProviderHelper.GetTableName(schema.aspnet_Applications.ApplicationName, schema.SchemaName)
, item.ColumnName, strSortOrder);
}
}
}
foreach (var item in oldColumns.Where(c => !c.ColumnName.Equals(Cms_Schema.TitleColumnName, StringComparison.InvariantCultureIgnoreCase)))
{
if (item.SortOrder != (int)SortOrder.Unspecified)
{
stringBuilder.AppendFormat(DDL_DropIndex, ContentProviderHelper.GetTableName(schema.aspnet_Applications.ApplicationName, schema.SchemaName), item.ColumnName);
}
stringBuilder.AppendFormat(DDL_DropColumn, ContentProviderHelper.GetTableName(schema.aspnet_Applications.ApplicationName, schema.SchemaName), item.ColumnName);
}
string update_ddl = stringBuilder.ToString();
return update_ddl;
}
#endregion
#endregion
#region Add&Delete&Update
const string SQL_Insert = "INSERT INTO [{0}] ({1}) VALUES ({2})";
/// <summary>
/// Creates the content.
/// </summary>
/// <param name="content">The content.</param>
/// <param name="contentValues">The content values.</param>
/// <returns>Return the cotnent field values</returns>
public IDictionary<string, object> CreateContent(IEverestCmsDataContext dataContext, Cms_Content content, IDictionary<string, object> contentValues)
{
if (!content.Cms_SchemaReference.IsLoaded)
content.Cms_SchemaReference.Load();
if (!content.Cms_Schema.aspnet_ApplicationsReference.IsLoaded)
content.Cms_Schema.aspnet_ApplicationsReference.Load();
DbCommand dbCommand = dataContext.ObjectContext.CreateStoreCommand("A");
StringBuilder columns = new StringBuilder("[ContentUUID]");
StringBuilder values = new StringBuilder("@ContentUUID");
//dbCommand.CreateParameter("ContentId", DbType.Int32, content.ContentId);
dbCommand.CreateParameter("ContentUUID", DbType.Guid, content.UUID);
IDictionary<string, object> contentFieldValues = IterateInsertColumns(content.Cms_Schema, contentValues, columns, values, dbCommand);
dbCommand.CommandText = string.Format(SQL_Insert, ContentProviderHelper.GetTableName(content.Cms_Schema.aspnet_Applications.ApplicationName, content.Cms_Schema.SchemaName),
columns, values);
SqlServerContentProvider.Log("SqlServerTextContentManager.CreateContent", dbCommand);
using (dbCommand.Connection.CreateConnectionScope())
{
var dbTrans = dataContext.ObjectContext.GetCurrentStoreTransaction();
if (dbTrans != null)
{
dbCommand.Transaction = dbTrans;
}
dbCommand.ExecuteNonQuery();
}
return contentFieldValues;
}
/// <summary>
/// Iterates the insert columns.
/// </summary>
/// <param name="schema">The schema.</param>
/// <param name="contentValues">The content values.</param>
/// <param name="columns">The columns.</param>
/// <param name="values">The values.</param>
/// <param name="dbCommand">The db command.</param>
/// <returns></returns>
private IDictionary<string, object> IterateInsertColumns(Cms_Schema schema, IDictionary<string, object> contentValues, StringBuilder columns, StringBuilder values, DbCommand dbCommand)
{
IDictionary<string, object> contentFieldValues = new Dictionary<string, object>(StringComparer.InvariantCultureIgnoreCase);
if (!schema.Cms_Column.IsLoaded)
schema.Cms_Column.Load();
foreach (var item in schema.DynamicTableColumns)
{
var contains = contentValues.Keys.Contains(item.ColumnName, StringComparer.InvariantCultureIgnoreCase);
if (contains || item.ControlType.Equals("Checkbox", StringComparison.InvariantCultureIgnoreCase))
{
columns.AppendFormat(",[{0}]", item.ColumnName);
values.AppendFormat(",@{0}", item.ColumnName);
string value = "";
if (contains && contentValues[item.ColumnName] != null)
{
value = contentValues[item.ColumnName].ToString();
}
contentFieldValues.Add(item.ColumnName, value);
ColumnDataType dataType = (ColumnDataType)Enum.Parse(typeof(ColumnDataType), item.DataType);
switch (dataType)
{
case ColumnDataType.Int:
int intValue = 0;
int.TryParse(value.ToString(), out intValue);
dbCommand.CreateParameter(item.ColumnName, DbType.Int32, intValue);
break;
case ColumnDataType.Decimal:
float floatValue = 0;
float.TryParse(value.ToString(), out floatValue);
dbCommand.CreateParameter(item.ColumnName, DbType.Double, floatValue);
break;
case ColumnDataType.String:
dbCommand.CreateParameter(item.ColumnName, DbType.String, value == null ? "" : value.Trim());
break;
case ColumnDataType.Datetime:
dbCommand.CreateParameter(item.ColumnName, DbType.DateTime, StringExtensions.IsNullOrEmptyTrim(value.ToString()) ? (object)DateTime.Now : value);
break;
case ColumnDataType.Bool:
dbCommand.CreateParameter(item.ColumnName, DbType.Boolean, StringExtensions.IsTrue(value));
break;
default:
break;
}
}
}
return contentFieldValues;
}
/// <summary>
///
/// </summary>
const string SQL_Delete = "DELETE FROM [{0}] WHERE ContentUUID=@ContentUUID";
/// <summary>
/// Deletes the content.
/// </summary>
/// <param name="content">The content.</param>
public void DeleteContent(IEverestCmsDataContext dataContext, Cms_Content content)
{
content.Cms_SchemaReference.Load();
content.Cms_Schema.aspnet_ApplicationsReference.Load();
DbCommand dbCommand = dataContext.ObjectContext.CreateStoreCommand("A");
List<SqlParameter> parameters = new List<SqlParameter>();
dbCommand.CreateParameter("ContentUUID", DbType.Guid, content.UUID);
dbCommand.CommandText = string.Format(SQL_Delete, ContentProviderHelper.GetTableName(content.Cms_Schema.aspnet_Applications.ApplicationName, content.Cms_Schema.SchemaName));
SqlServerContentProvider.Log("SqlServerTextContentManager.DeleteContent", dbCommand);
using (dbCommand.Connection.CreateConnectionScope())
{
var dbTrans = dataContext.ObjectContext.GetCurrentStoreTransaction();
if (dbTrans != null)
{
dbCommand.Transaction = dbTrans;
}
dbCommand.ExecuteNonQuery();
}
}
/// <summary>
///
/// </summary>
const string SQL_Update = "UPDATE [{0}] SET ContentUUID=@ContentUUID {1} WHERE ContentUUID=@ContentUUID";
/// <summary>
/// Updates the content.
/// </summary>
/// <param name="dataContext">The data context.</param>
/// <param name="content">The content.</param>
/// <param name="newContentValues">The new content values.</param>
/// <param name="updateAllField">if set to <c>true</c> [update all field].</param>
/// <returns>Return the cotnent field values</returns>
public IDictionary<string, object> UpdateContent(IEverestCmsDataContext dataContext, Cms_Content content, IDictionary<string, object> newContentValues, bool updateAllField)
{
StringBuilder setColumns = new StringBuilder();
DbCommand dbCommand = dataContext.ObjectContext.CreateStoreCommand("A");
dbCommand.CreateParameter("ContentUUID", DbType.Guid, content.UUID);
content.Cms_SchemaReference.Load();
content.Cms_Schema.aspnet_ApplicationsReference.Load();
IDictionary<string, object> contentFieldValues = IterateUpdateColumns(content.Cms_Schema, newContentValues, setColumns, dbCommand, updateAllField);
dbCommand.CommandText = string.Format(SQL_Update, ContentProviderHelper.GetTableName(content.Cms_Schema.aspnet_Applications.ApplicationName, content.Cms_Schema.SchemaName), setColumns);
SqlServerContentProvider.Log("SqlServerTextContentManager.UpdateContent", dbCommand);
using (dbCommand.Connection.CreateConnectionScope())
{
var dbTrans = dataContext.ObjectContext.GetCurrentStoreTransaction();
if (dbTrans != null)
{
dbCommand.Transaction = dbTrans;
}
dbCommand.ExecuteNonQuery();
}
return contentFieldValues;
}
/// <summary>
/// Iterates the update columns.
/// </summary>
/// <param name="schema">The schema.</param>
/// <param name="contentValues">The content values.</param>
/// <param name="setColumns">The set columns.</param>
/// <param name="dbCommand">The db command.</param>
/// <returns></returns>
private IDictionary<string, object> IterateUpdateColumns(Cms_Schema schema, IDictionary<string, object> contentValues,
StringBuilder setColumns, DbCommand dbCommand, bool updateAllField)
{
IDictionary<string, object> contentFieldValues = new Dictionary<string, object>(StringComparer.InvariantCultureIgnoreCase);
schema.Cms_Column.Load();
foreach (var item in schema.DynamicTableColumns)
{
var containsField = contentValues.Keys.Contains(item.ColumnName, StringComparer.InvariantCultureIgnoreCase);
//The checkbox does not post the field value when unchecked.
if (containsField || (updateAllField && item.ControlType.Equals("Checkbox", StringComparison.InvariantCultureIgnoreCase)))
{
setColumns.AppendFormat(",[{0}]=@{0}", item.ColumnName);
var value = "";
if (containsField && contentValues[item.ColumnName] != null)
{
value = contentValues[item.ColumnName].ToString();
}
//add to content field values
contentFieldValues.Add(item.ColumnName, value);
ColumnDataType dataType = (ColumnDataType)Enum.Parse(typeof(ColumnDataType), item.DataType);
switch (dataType)
{
case ColumnDataType.Int:
int intValue = 0;
int.TryParse(value, out intValue);
dbCommand.CreateParameter(item.ColumnName, DbType.Int32, intValue);
break;
case ColumnDataType.Decimal:
float floatValue = 0;
float.TryParse(value, out floatValue);
dbCommand.CreateParameter(item.ColumnName, DbType.Double, floatValue);
break;
case ColumnDataType.String:
dbCommand.CreateParameter(item.ColumnName, DbType.String, value == null ? "" : value.Trim());
break;
case ColumnDataType.Datetime:
dbCommand.CreateParameter(item.ColumnName, DbType.DateTime, StringExtensions.IsNullOrEmptyTrim(value) ? (object)DateTime.Now : value);
break;
case ColumnDataType.Bool:
dbCommand.CreateParameter(item.ColumnName, DbType.Boolean, StringExtensions.IsTrue(value));
break;
default:
break;
}
}
}
return contentFieldValues;
}
#endregion
#region CopyContent
const string SQL_Copy = @"INSERT INTO [{0}] ({1}) SELECT {2} FROM [{0}] WHERE ContentUUID=@OriginalContentUUID";
public void CopyContent(IEverestCmsDataContext dataContext, Cms_Content originalContent, Cms_Content newContent)
{
if (!originalContent.Cms_SchemaReference.IsLoaded)
originalContent.Cms_SchemaReference.Load();
if (!originalContent.Cms_Schema.Cms_Column.IsLoaded)
{
originalContent.Cms_Schema.Cms_Column.Load();
}
DbCommand dbCommand = dataContext.ObjectContext.CreateStoreCommand("A");
StringBuilder columns = new StringBuilder("[ContentUUID]");
StringBuilder values = new StringBuilder("@ContentUUID");
foreach (var item in originalContent.Cms_Schema.DynamicTableColumns)
{
columns.AppendFormat(",[{0}]", item.ColumnName);
values.AppendFormat(",[{0}]", item.ColumnName);
}
dbCommand.CreateParameter("ContentUUID", DbType.Guid, newContent.UUID);
dbCommand.CreateParameter("OriginalContentUUID", DbType.Guid, originalContent.UUID);
if (!originalContent.Cms_Schema.aspnet_ApplicationsReference.IsLoaded)
originalContent.Cms_Schema.aspnet_ApplicationsReference.Load();
dbCommand.CommandText = string.Format(SQL_Copy, ContentProviderHelper.GetTableName(originalContent.Cms_Schema.aspnet_Applications.ApplicationName,
originalContent.Cms_Schema.SchemaName), columns.ToString(), values.ToString());
SqlServerContentProvider.Log("SqlServerTextContentManager.CopyContent", dbCommand);
using (dbCommand.Connection.CreateConnectionScope())
{
var dbTrans = dataContext.ObjectContext.GetCurrentStoreTransaction();
if (dbTrans != null)
{
dbCommand.Transaction = dbTrans;
}
dbCommand.ExecuteNonQuery();
}
}
#endregion
#region QueryContent
protected virtual IEnumerable<IDictionary<string, object>> QueryContents(IEverestCmsDataContext dataContext, string queryStatement, string orderBy,
IDictionary<string, object> parameterValues, Cms_Schema schema, Cms_Folder folder, IEnumerable<Cms_Folder> folders, Guid? parentUUID, string userName
, bool? isProcessed, int startIndex, int limit)
{
if (startIndex < 1)
{
startIndex = 1;
}
if (limit == int.MaxValue)
{
limit = limit - 1;
}
var schemaApplication = CachedData.GetApplicationBySchema(schema.UUID);
if (StringExtensions.IsNullOrEmptyTrim(orderBy))
{
orderBy = "ContentId DESC,HistoryId DESC";
}
DbCommand dbCommand = dataContext.ObjectContext.CreateStoreCommand("a", CommandType.Text);
var queryExpression = ContentProviderHelper.ContentQueryCondition(queryStatement, parameterValues, folders, parentUUID, dbCommand);
StringBuilder selectColumns = new StringBuilder(",WorkflowContent.ContentId");
var columns = CachedData.GetColumnsBySchema(schema.UUID);
foreach (var column in columns)
{
if (column.VisibleInList != null && column.VisibleInList.Value == true)
{
selectColumns.AppendFormat(",DynamicTable.[{0}]", column.ColumnName);
}
}
var workflowCondition = WorkflowCondition(userName, isProcessed);
string includeFolders = ContentProviderHelper.FoldersQueryExpresion(folders, dbCommand);
dbCommand.CreateParameter("FolderUUID", DbType.Guid, folder.UUID);
dbCommand.CreateParameter("SchemaUUID", DbType.Guid, schema.UUID);
dbCommand.CommandText = BuildQueryContentsSQL(orderBy, ContentProviderHelper.GetTableName(schemaApplication.ApplicationName, schema.SchemaName), queryExpression, selectColumns, workflowCondition, includeFolders);
dbCommand.CreateParameter("startIndex", DbType.Int32, startIndex);
dbCommand.CreateParameter("limit", DbType.Int32, limit);
SqlServerContentProvider.Log("SqlServerTextContentManager.QueryContents", dbCommand);
using (dbCommand.Connection.CreateConnectionScope())
{
IDataReader dataReader = dbCommand.ExecuteReader();
List<IDictionary<string, object>> list = new List<IDictionary<string, object>>();
while (dataReader.Read())
{
IDictionary<string, object> dic = DataReaderToDictionary(dataReader, folder);
list.Add(dic);
}
return list;
}
}
protected virtual string BuildQueryContentsSQL(string orderBy, string tableName, StringBuilder queryExpression, StringBuilder selectColumns, string workflowCondition, string includeFolders)
{
return string.Format(@"
SELECT *
FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY {2}) AS RowIndex
FROM (SELECT 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 ApplicationName FROM aspnet_Applications WHERE ApplicationId=WorkflowContent.ApplicationId) AS [Application],
(SELECT TOP 1 FolderUUID
FROM Cms_ContentInFolder
WHERE ContentUUID= WorkflowContent.UUID AND FolderUUID=@FolderUUID
) AS IncludedByFolderUUID {3}
FROM (SELECT MAX(A.HistoryId) AS GroupedHistoryId,MAX(A.ContentID) AS GroupedContentID
FROM (SELECT MAX(Cms_Content.FolderLevel) AS MaxFolderLevel,OriginalUUID AS MaxOriginalUUID
FROM (SELECT *
FROM Cms_Content
WHERE {5}
) Cms_Content,
[{0}]
WHERE Cms_Content.UUID = [{0}].ContentUUID AND {1}
Group By OriginalUUID
) C ,
(SELECT *
FROM Cms_View_Workflow_Content
WHERE {5}
)A,
[{0}] B
WHERE A.UUID = B.ContentUUID AND A.FolderLevel = C.MaxFolderLevel AND A.OriginalUUID=C.MaxOriginalUUID {4}
GROUP BY A.ContentID
)Grouped,
[{0}] DynamicTable,
(SELECT * FROM Cms_View_Workflow_Content WHERE SchemaUUID=@SchemaUUID) WorkflowContent
WHERE WorkflowContent.UUID = DynamicTable.ContentUUID AND WorkflowContent.ContentId = Grouped.GroupedContentID
AND (WorkflowContent.HistoryId = Grouped.GroupedHistoryId OR (WorkflowContent.HistoryId IS NULL AND Grouped.GroupedHistoryId IS NULL))
)D
)E
WHERE (RowIndex >= @startIndex) AND (RowIndex < @startIndex + @limit)"
, tableName, queryExpression.ToString(), orderBy
, selectColumns.ToString(), workflowCondition, includeFolders);
}
internal static IDictionary<string, object> DataReaderToDictionary(IDataReader dataReader, Cms_Folder folder)
{
IDictionary<string, object> dic = new Dictionary<string, object>(StringComparer.InvariantCultureIgnoreCase);
for (int i = 0; i < dataReader.FieldCount; i++)
{
dic.Add(dataReader.GetName(i), dataReader.GetValue(i));
}
if (folder != null && dic["FolderUUID"] != DBNull.Value)
{
if ((Guid)dic["FolderUUID"] == folder.UUID)
{
dic["IsLocalized"] = true;
dic["Including"] = true;
}
else
{
dic["IsLocalized"] = false;
if (dic["IncludedByFolderUUID"] != DBNull.Value)
{
if ((Guid)dic["IncludedByFolderUUID"] == folder.UUID)
{
dic["Including"] = true;
}
else
{
//dic["Including"] = false;
}
}
else
dic["Including"] = false;
}
}
if (dic.ContainsKey("FilePath") && dic["FilePath"] != DBNull.Value && !StringExtensions.IsNullOrEmptyTrim(dic["FilePath"].ToString()))
{
dic["FilePath"] = Everest.Library.Web.UrlConvertor.PathToUrl(dic["FilePath"].ToString());
}
return dic;
}
/// <summary>
/// Counts the contents.
/// </summary>
/// <param name="dataContext">The data context.</param>
/// <param name="queryStatement">The query statement.The statement like : app_News:id={id} and name like '%name%'</param>
/// <param name="queryString">The query string.</param>
/// <param name="schema">The schema.</param>
/// <param name="folders">The folders.</param>
/// <param name="parentUUID">The parent UUID.</param>
/// <param name="userName">Name of the user.</param>
/// <param name="isProcessed">The is processed.</param>
/// <returns></returns>
protected int CountContents(IEverestCmsDataContext dataContext, string queryStatement, IDictionary<string, object> queryString,
Cms_Schema schema, IEnumerable<Cms_Folder> folders, Guid? parentUUID, string userName, bool? isProcessed)
{
var schemaApplication = CachedData.GetApplicationBySchema(schema.UUID);
DbCommand dbCommand = dataContext.ObjectContext.CreateStoreCommand("A");
var queryExpression = ContentProviderHelper.ContentQueryCondition(queryStatement, queryString, folders, parentUUID, dbCommand);
var workflowCondition = WorkflowCondition(userName, isProcessed);
string includeFolders = ContentProviderHelper.FoldersQueryExpresion(folders, dbCommand);
dbCommand.CommandText = BuildCountContentsSQL(ContentProviderHelper.GetTableName(schemaApplication.ApplicationName, schema.SchemaName), queryExpression, workflowCondition, includeFolders);
SqlServerContentProvider.Log("SqlServerTextContentManager.CountContents", dbCommand);
using (dbCommand.Connection.CreateConnectionScope())
{
object o = dbCommand.ExecuteScalar();
if (o != null)
{
return int.Parse(o.ToString());
}
return 0;
}
}
private static string BuildCountContentsSQL(string tableName, StringBuilder queryExpression, string workflowCondition, string includeFolders)
{
return string.Format(@"
SELECT COUNT(distinct OriginalUUID)
FROM (SELECT Cms_View_Workflow_Content.*
FROM Cms_View_Workflow_Content
WHERE {3} {2}
) Cms_Content
,[{0}]
WHERE Cms_Content.UUID = [{0}].ContentUUID AND {1}"
, tableName, queryExpression.ToString(),
workflowCondition.ToString(), includeFolders);
}
internal static string WorkflowCondition(string userName, bool? isProcessed)
{
PermissionService permissionService = UnityManager.Resolve<PermissionService>();
IEnumerable<string> roles = permissionService.GetRoles(userName);
StringBuilder workflowCondition = new StringBuilder();
if (isProcessed != null)
{
if (isProcessed.Value == true)
{
workflowCondition.AppendFormat(" AND ((ProcessResult <> 0", userName);
workflowCondition.Append(" AND ( 1 <> 1 ");
foreach (var role in roles)
{
workflowCondition.AppendFormat(" OR RoleName = '{0}'", role);
}
//workflowCondition.AppendFormat("OR UserName='{0}'", userName);
workflowCondition.Append("))");
workflowCondition.AppendFormat(" OR (ProcessResult IS NULL))", userName);
}
else
{
workflowCondition.Append(" AND ProcessResult = 0");
workflowCondition.Append(" AND ( 1 <> 1 ");
foreach (var role in roles)
{
workflowCondition.AppendFormat(" OR RoleName = '{0}'", role);
}
//workflowCondition.AppendFormat("OR UserName='{0}'", userName);
workflowCondition.Append(")");
}
return workflowCondition.ToString();
}
else
{
workflowCondition.Append("AND (( 1 <> 1 ");
foreach (var role in roles)
{
workflowCondition.AppendFormat(" OR RoleName = '{0}'", role);
}
workflowCondition.AppendFormat(") OR (RoleName IS NULL))", userName);
}
return workflowCondition.ToString();
}
public IEnumerable<IDictionary<string, object>> QueryContents(IEverestCmsDataContext dataContext, string queryStatement, string orderBy,
IDictionary<string, object> parameterValues, Cms_Folder folder, IEnumerable<Cms_Folder> folders, string userName,
bool? isProcessed, int startIndex, int limit)
{
var schema = CachedData.GetSchemaByFolder(folder.UUID);
return this.QueryContents(dataContext, queryStatement, orderBy, parameterValues, schema, folder, folders, null, userName,
isProcessed, startIndex, limit);
}
public int CountContents(IEverestCmsDataContext dataContext, string queryStatement, IDictionary<string, object> queryString, Cms_Folder folder,
IEnumerable<Cms_Folder> folders, string userName, bool? isProcessed)
{
var schema = CachedData.GetSchemaByFolder(folder.UUID);
return this.CountContents(dataContext, queryStatement, queryString, schema, folders, null, userName, isProcessed);
}
protected virtual string QueryContent_SQL
{
get
{
return @"
SELECT *
FROM (SELECT A.Title,A.UserKey,A.ApplicationId,A.FolderUUID,
A.SchemaUUID,A.UserName,A.BaseUUID,A.OriginalUUID,
A.ModifiedDate,A.PostDate,A.ContentStatus,A.UUID,A.ContentId,B.*
FROM Cms_Content A,
[{0}] B
WHERE A.UUID = B.ContentUUID
) C
WHERE ContentUUID = @ContentUUID";
}
}
public IDictionary<string, object> QueryContent(IEverestCmsDataContext dataContext, Cms_Content content)
{
content.Cms_SchemaReference.Load(content.Cms_Schema, content.EntityState);
content.Cms_Schema.aspnet_ApplicationsReference.Load(content.Cms_Schema.aspnet_Applications, content.EntityState);
DbCommand dbCommand = dataContext.ObjectContext.CreateStoreCommand(string.Format(QueryContent_SQL,
ContentProviderHelper.GetTableName(content.Cms_Schema.aspnet_Applications.ApplicationName, content.Cms_Schema.SchemaName)), CommandType.Text);
dbCommand.CreateParameter("ContentUUID", DbType.Guid, content.UUID);
SqlServerContentProvider.Log("SqlServerTextContentManager.CountContents", dbCommand);
using (dbCommand.Connection.CreateConnectionScope())
{
IDataReader dataReader = dbCommand.ExecuteReader();
while (dataReader.Read())
{
IDictionary<string, object> dic = DataReaderToDictionary(dataReader, null);
return dic;
}
}
return null;
}
#endregion
#region DeleteContentsByFolder
/// <summary>
/// Deletes the contents by folder.
/// </summary>
/// <param name="dataContext">The data context.</param>
/// <param name="folder">The folder.</param>
public void DeleteContentsByFolder(IEverestCmsDataContext dataContext, Cms_Folder folder)
{
//todo:delete the recycle bin content.
string sql = "DELETE FROM Cms_Content WHERE FolderUUID=@FolderUUID";
DbCommand dbCommand = dataContext.ObjectContext.CreateStoreCommand(sql);
SqlServerContentProvider.Log("SqlServerTextContentManager.CreateContent", dbCommand);
dbCommand.CreateParameter("FolderUUID", DbType.Guid, folder.UUID);
using (dbCommand.Connection.CreateConnectionScope())
{
var dbTrans = dataContext.ObjectContext.GetCurrentStoreTransaction();
if (dbTrans != null)
{
dbCommand.Transaction = dbTrans;
}
dbCommand.ExecuteNonQuery();
}
}
#endregion
}
}
|