/// <summary>
/// Инициализирует главную таблицу для SQL view
/// </summary>
/// <param name="table">Базовая таблица</param>
/// <param name="properties">Возвращаемые поля</param>
/// <param name="key">Ключевое поле</param>
/// <param name="isRegister">Реестр (наличие периодов действий в одной версии)</param>
/// <param name="isSimple">генерации кода для выборкиа актуальных значений не требуется</param>
/// <param name="isNotVersioned">признак того что сущность версионная</param>
/// <returns>Инициализированный view контейнер</returns>
public SqlViewModel Init(string table, string[] properties, string[] key, bool isRegister, bool isSimple = false, bool isNotVersioned = false)
{
if (string.IsNullOrEmpty(table))
{
throw new ArgumentNullException("table");
}
if (key == null || key.Length == 0)
{
throw new ArgumentNullException("key");
}
if (properties == null)
{
throw new ArgumentNullException("properties");
}
_root = new SqlViewDataContainer
{
IsSimple = isSimple,
IsRegister = isRegister,
InnerKeys = key,
Properties = properties,
Table = table,
IsNotVersioned = isNotVersioned,
Alias = _pluralizationService.IsPlural(table) ? _pluralizationService.Singularize(table).ToLower() : table.ToLower()
};
return(this);
}
private void CreateVersionJoin(StringBuilder builder, SqlViewDataContainer joinDataContainer)
{
switch (joinDataContainer.JoinType)
{
case JoinType.Inner:
builder.AppendFormat("INNER JOIN (SELECT {0}", Environment.NewLine);
break;
case JoinType.Left:
builder.AppendFormat("LEFT JOIN (SELECT {0}", Environment.NewLine);
break;
default:
throw new Exception(String.Format("Unsupported join: {0}", joinDataContainer.JoinType));
}
var isCommaNeeded = false;
foreach (var property in joinDataContainer.Properties)
{
if (isCommaNeeded)
{
builder.AppendFormat(", {0}", Environment.NewLine);
}
builder.AppendFormat("{0}inner.{1}", joinDataContainer.Alias, property);
isCommaNeeded = true;
}
foreach (var outerKey in joinDataContainer.OuterKeys)
{
if (!joinDataContainer.Properties.Contains(outerKey))
{
builder.AppendFormat(",{2}{0}inner.{1}", joinDataContainer.Alias, outerKey, Environment.NewLine);
}
}
builder.Append(Environment.NewLine);
builder.AppendFormat("FROM [{3}].[{0}] AS {1}inner {2}", joinDataContainer.Table, joinDataContainer.Alias, Environment.NewLine, _schema);
builder.AppendFormat("INNER JOIN (SELECT {0}", Environment.NewLine);
builder.AppendFormat("{0}, MAX(VersionId) AS MaxVersion {1}", string.Join(",", joinDataContainer.OuterKeys), Environment.NewLine);
builder.AppendFormat("FROM [{2}].[{0}] {1}", joinDataContainer.Table, Environment.NewLine, _schema);
builder.AppendFormat("GROUP BY {0}) AS {1}innergrouped {2}", string.Join(",", joinDataContainer.OuterKeys), joinDataContainer.Alias, Environment.NewLine);
var groupExpression = string.Join(" AND ",
joinDataContainer.OuterKeys.Select(s => string.Format("{0}inner.{1}= {0}innergrouped.{1}", joinDataContainer.Alias, s)));
builder.AppendFormat("ON {1} AND {0}inner.VersionId = {0}innergrouped.MaxVersion) AS {2} {3}", joinDataContainer.Alias,
groupExpression, joinDataContainer.Alias, Environment.NewLine);
var equalExpression = joinDataContainer.InnerKeys.Select((t, i) => string.Format("{0}.{1} = {2}.{3}",
_root.Alias, t, joinDataContainer.Alias, joinDataContainer.OuterKeys[i]));
builder.AppendFormat("ON {0} {1}", string.Join(" AND ", equalExpression), Environment.NewLine);
}
/// <summary>
/// Добавляет join c таблицей-словарем в запрос
/// </summary>
/// <param name="builder">SQL запрос</param>
/// <param name="joinDataContainer">Данные о join таблице</param>
private void CreateDictionaryJoin(StringBuilder builder, SqlViewDataContainer joinDataContainer)
{
switch (joinDataContainer.JoinType)
{
case JoinType.Inner:
builder.AppendFormat("INNER JOIN [{3}].[{0}] as {1} {2}", joinDataContainer.Table, joinDataContainer.Alias, Environment.NewLine, _schema);
break;
case JoinType.Left:
builder.AppendFormat("LEFT JOIN [{3}].[{0}] as {1} {2}", joinDataContainer.Table, joinDataContainer.Alias, Environment.NewLine, _schema);
break;
default:
throw new Exception(String.Format("Unsupported join: {0}", joinDataContainer.JoinType));
}
var equalExpression = joinDataContainer.InnerKeys.Select((t, i) => string.Format("{0}.{1} = {2}.{3}",
_root.Alias, t, joinDataContainer.Alias, joinDataContainer.OuterKeys[i]));
builder.AppendFormat("ON {0} {1}", string.Join(" AND ", equalExpression), Environment.NewLine);
}
