public bool CreateDataBaseTable(SPSite site, string tableName, SPFieldCollection fields)
{
string dataBaseTableName = this.Adapter.BuildTableName(tableName);
CacheTableStructXml cacheTableStructXml = new CacheTableStructXml {
TableName = dataBaseTableName
};
foreach (SPField field in fields)
{
if (field.Hidden && field.Id != SPBuiltInFieldId.ID &&
this.Adapter.IncludedFields.All(f => f != field.InternalName))
{
continue;
}
string columnType = this.Adapter.GetDataBaseType(field.Type);
Columns column = new Columns
{
ColumnName =
field.Id == SPBuiltInFieldId.ID
? Constants.ColumnSpPrefix + field.InternalName
: field.InternalName,
ColumnType = columnType
};
cacheTableStructXml.Columns.Add(column);
}
ArchiveCacheCRUD syncCrud = new ArchiveCacheCRUD(site);
ErrorCode result = syncCrud.CreateOrUpdateTable(cacheTableStructXml);
return(result == ErrorCode.NoError);
}
private static void RemoveNotAvalibleColumns(CacheTableStructXml cacheTableStructXml, IEnumerable <string> columnNotAvalibleNames)
{
foreach (
Columns column in
columnNotAvalibleNames.Select(
s =>
cacheTableStructXml.Columns.Where(
c => string.Equals(c.ColumnName, s, StringComparison.InvariantCultureIgnoreCase))
.ToList()).SelectMany(invalidColumns => invalidColumns))
{
cacheTableStructXml.Columns.Remove(column);
}
}
public ErrorCode CreateOrUpdateValues(CacheTableStructXml cacheTableStructXml)
{
RemoveNotAvalibleColumns(cacheTableStructXml, new[] { "Id" });
const string sql = "EXECUTE [dbo].[P_CacheDb_UpdateValues] @CacheTableStructXml";
string xmlString = cacheTableStructXml.ToString();
ErrorCode result = this.StoredProcedureVoid(sql, new
{
CacheTableStructXml = xmlString
});
return(result);
}
public ErrorCode CreateOrUpdateTable(CacheTableStructXml cacheTableStructXml)
{
RemoveNotAvalibleColumns(cacheTableStructXml, new[] { "Id", Constants.BarCodeFieldName, "ItemUrl" });
const string sql = "EXECUTE [dbo].[P_CacheDb_CreateTable] @CacheTableStructXml";
string xmlString = cacheTableStructXml.ToString();
ErrorCode result = this.StoredProcedureVoid(sql, new
{
CacheTableStructXml = xmlString
});
return(result);
}
public bool UpdateDataTableColumns(SPList spList, string fieldXml, bool isDeleted)
{
string tableNameSetting = SettingsProvider.Instance(spList.ParentWeb).TableName(spList);
string tableName = this.Adapter.BuildTableName(tableNameSetting);
if (string.IsNullOrWhiteSpace(tableName))
{
//Logger.WriteError("tableName is null, feature activaiting?", "UpdateDataTableColumns(SPList spList, string fieldXml, bool isDeleted)");
return(true);
}
CacheTableStructXml cacheTableStructXml = new CacheTableStructXml {
TableName = tableName
};
XmlDocument doc = new XmlDocument();
doc.LoadXml(fieldXml);
const string fXmlTypeName = "Type";
const string xStaticNameName = "StaticName";
SPFieldType spFieldType;
XmlElement root = doc.DocumentElement;
Enum.TryParse(root.Attributes[fXmlTypeName].Value, true, out spFieldType);
string internalName = root.Attributes[xStaticNameName].Value;
string columnType = this.Adapter.GetDataBaseType(spFieldType);
Columns column = new Columns
{
ColumnName = internalName,
ColumnType = columnType,
ColumnIsDeleted = isDeleted
};
cacheTableStructXml.Columns.Add(column);
ArchiveCacheCRUD syncCrud = new ArchiveCacheCRUD(spList.ParentWeb.Site);
ErrorCode result = syncCrud.CreateOrUpdateTable(cacheTableStructXml);
return(result == ErrorCode.NoError);
}
public IEnumerable <dynamic> GetCacheTable(List <int> userGroups, int userId, CacheTableStructXml cacheTableStructXml, string sqlFilterString = null, long rowLimit = 0, SPWeb currentWeb = null)
{
if (currentWeb == null)
{
currentWeb = SPContext.Current.Web;
}
if (!userGroups.Contains(userId))
{
userGroups.Add(userId);
}
// собираем колонки
string sqlColums = "";
foreach (Columns column in cacheTableStructXml.Columns)
{
if (!string.IsNullOrWhiteSpace(sqlColums))
{
sqlColums += ",";
}
sqlColums += Constants.SqlScriptTablePrefix + "." + column.ColumnName + " as [" + column.ColumnName + "]";
}
// собираем запрос на пермишены по пользователю и его группам
string permissionsFilter = string.Empty;
if (userGroups.Count > 1)
{
foreach (int uId in userGroups)
{
if (permissionsFilter.EndsWith(")"))
{
permissionsFilter += " OR ";
}
permissionsFilter += "([UserGroupId] = " + uId + ")";
}
}
else
{
permissionsFilter = "[UserGroupId] = " + userGroups.FirstOrDefault();
}
StringBuilder sqlStringBuilder = new StringBuilder();
sqlStringBuilder.Append("SELECT");
if (rowLimit > 0)
{
sqlStringBuilder.AppendFormat(" TOP {0} ", rowLimit);
}
sqlStringBuilder.AppendFormat(" {0} ", sqlColums);
sqlStringBuilder.Append("FROM");
sqlStringBuilder.AppendFormat(" {0} as {1} ", cacheTableStructXml.TableName, Constants.SqlScriptTablePrefix);
sqlStringBuilder.AppendFormat("WHERE ([{0}]=1) ", SP.SqlCache.Common.Box.Fields.IsUpdated);
if (!currentWeb.UserIsSiteAdmin && !currentWeb.UserIsWebAdmin)
{
//Если в талице Permissions не найдено строк для текущих групп пользователя, то считаем, что доступ есть.
sqlStringBuilder.AppendFormat(" AND ([{0}] IN (SELECT [{0}] FROM [{1}] WHERE ({2})) OR NOT EXISTS(SELECT P.* FROM [{1}] as P WHERE P.[{0}] = D.[{0}])",
Constants.ItemUniqueIDFieldName,
Constants.PermissionsDataTableName,
permissionsFilter);
sqlStringBuilder.Append(")");
}
string sqlString = sqlStringBuilder.ToString();
// получаем баез доп. фильтров
if (string.IsNullOrWhiteSpace(sqlFilterString))
{
IEnumerable <dynamic> rows = this.GetDynamicList(sqlString);
return(rows);
}
// получаем c доп. фильтрами
else
{
IEnumerable <dynamic> rows = this.GetDynamicList(sqlString + " AND (" + sqlFilterString + ")");
return(rows);
}
}
