public static string GenerateInsert(SqlInfo info)
{
var columnsWithoutSelectedPk = info.TableColumns.Where(c => info.SkipPrimaryKey.All(pk => pk != c)).ToList();
if (info.TenantRelated && !columnsWithoutSelectedPk.Contains(_tenantId))
{
columnsWithoutSelectedPk.Add(_tenantId);
}
if (info.JoinTableColumns != null && !string.IsNullOrEmpty(info.JoinTableName))
{
var columnsJonedWithoutSelectedPk = info.JoinTableColumns.Where(c => info.SkipPrimaryKey.All(pk => pk != c)).ToList();
if (info.TenantRelated && !columnsJonedWithoutSelectedPk.Contains(_tenantId))
{
columnsJonedWithoutSelectedPk.Add(_tenantId);
}
var outputKey = info.ReturnPrimarayKey && string.IsNullOrEmpty(info.PrimaryKeyNames.First()) ? "" : "OUTPUT INSERTED." + info.PrimaryKeyNames.First(); //TODO FIX TO MANY KEYS
// use pk from inherite model
var values = columnsJonedWithoutSelectedPk.Select(c => c == info.JoinVersionKeyName ? info.VersionKeyName : c == info.JoinPrimaryKeyNames.First() ? info.PrimaryKeyNames.First() : c);//TODO FIX TO MANY KEYS
var joinClassValues = Values(values);
// return inset into table and join table
return "INSERT INTO " + info.JoinTableName + "(" + Fields(columnsJonedWithoutSelectedPk, info.JoinTableName) + ") VALUES(" + joinClassValues + ")\r\n "
+ "INSERT INTO " + info.TableName + "(" + Fields(columnsWithoutSelectedPk, info.TableName) + ") " + outputKey + " VALUES(" + Values(columnsWithoutSelectedPk) + ") ";
}
// return select from table
return Insert(columnsWithoutSelectedPk, info.TableName, info.ReturnPrimarayKey ? info.PrimaryKeyNames.First() : null) + " "; //TODO FIX TO MANY KEYS
}
public static string GenerateFields(SqlInfo info)
{
return Fields(info.TableColumns, info.TableName);
}
private static List<string> GetFullJoinedColumnList(SqlInfo info)
{
var list = info.JoinTableColumns.ToList();
if (info.TenantRelated && !list.Contains(_tenantId))
{
list.Add(_tenantId);
}
return list;
}
public static string GenerateAndVersionsWithAlias(string selectedFilter, SqlInfo info, string condition = "=")
{
var tableAlias = info.JoinVersionTableName != null ? _joinVersionTableAlias1 : _versionTableAlias1;
return And(new[] { selectedFilter }, tableAlias, condition);
}
public static string GenerateWhereVersions(IEnumerable<string> selectedFilters, SqlInfo info)
{
return Where(selectedFilters, info.VersionTableName) + AndTenantRelated(info.VersionTableName, info.TenantRelated) + " ";
}
public static string GenerateWhereVersionsWithAlias(IEnumerable<string> selectedFilters, SqlInfo info)
{
return Where(selectedFilters, _versionTableAlias1) + AndTenantRelated(_versionTableAlias1, info.TenantRelated) + " ";
}
public static string GenerateValues(SqlInfo info)
{
return Values(info.TableColumns);
}
public static string GenerateRemove(SqlInfo info)
{
if (info.JoinTableColumns != null && !string.IsNullOrEmpty(info.JoinTableName))
return Delete(info.TableName) + " WHERE " + Field(info.TableName, info.PrimaryKeyNames.First()) + " IN (SELECT ItemId FROM " + _tempTable + ");" +//TODO FIX TO MANY KEYS
Delete(info.JoinTableName) + " WHERE " + Field(info.JoinTableName, info.JoinPrimaryKeyNames.First()) + " IN (SELECT ItemId FROM " + _tempTable + ");" + " ";//TODO FIX TO MANY KEYS
return Delete(info.TableName) + " ";
}
public static string GenerateUpdate(SqlInfo info)
{
var columns = info.TableColumns.Where(c => info.SkipPrimaryKey.All(pk => pk != c)).ToList();
return Update(info.TableName) + " "
+ Set(columns, info.TableName) + " "
+ From(info.TableName) + " ";
}
public static string GenerateUpdateJoin(SqlInfo info)
{
// use pk from inherite model
var values = info.JoinTableColumns.Select(c => new KeyValuePair<string,string>(c,c == info.JoinVersionKeyName ? info.VersionKeyName : c == info.JoinPrimaryKeyNames.First() ? info.PrimaryKeyNames.First() : c));//TODO FIX TO MANY KEYS
return Update(info.JoinTableName) + " "
+ Set(values, info.JoinTableName) + " "
+ From(info.JoinTableName) + " ";
}
public static string GenerateSelectByToVersionTable(SqlInfo info)
{
if (info.JoinTableColumns != null && !string.IsNullOrEmpty(info.JoinTableName))
{
// return select from table and join table
return Select(info.TableColumns, info.VersionTableName) + ","
+ Fields(info.JoinTableColumns, info.JoinVersionTableName) + " "
+ From(info.VersionTableName) + " "
+ InnerJoin(info.VersionTableName, info.PrimaryKeyNames.First(), info.JoinVersionTableName, info.JoinPrimaryKeyNames.First()) + " ";//TODO FIX TO MANY KEYS
}
// return select from table
return Select(info.TableColumns, info.VersionTableName) + " " + From(info.VersionTableName) + " ";
}
public static string GenerateSelectByKeyAndSliceDateToVersionTable(SqlInfo info)
{
var versionTableAlias = "versionTable";
var joined = info.JoinTableColumns != null && !string.IsNullOrEmpty(info.JoinTableName);
if (joined)
{
var selectMaxModifiedJoin = @"(SELECT " + string.Join(",", info.PrimaryKeyNames.Select(pk => Field(_versionTableAlias1, pk))) + ", MAX(" + Field(_joinVersionTableAlias1, _sliceDateColumnName) + ") as " + _sliceDateColumnName + " "
+ From(info.VersionTableName + " " + _versionTableAlias1) + " "
+ InnerJoin(info.JoinTableName + " " + _joinVersionTableAlias1, Field(_versionTableAlias1, info.VersionKeyName) + " = " + Field(_joinVersionTableAlias1, info.JoinVersionKeyName)) + " "
+ "{filter} "
+ " GROUP BY " + string.Join(",", info.PrimaryKeyNames.Select(pk => Field(_versionTableAlias1, pk))) + ") " + versionTableAlias;
// join all columns from main table
var comparePrimaryKey = string.Join(" AND ", info.PrimaryKeyNames.Select(pk => Field(versionTableAlias, pk) + " = " + Field(info.JoinVersionTableName, info.JoinPrimaryKeyNames.First())));
var compareSliceDate = Field(versionTableAlias, _sliceDateColumnName) + " = " + Field(info.JoinVersionTableName, _sliceDateColumnName);
var join1 = InnerJoin(info.JoinVersionTableName, comparePrimaryKey + " AND " + compareSliceDate);
// join all columns from joinned table
var compareVersionId = Field(info.JoinVersionTableName, info.JoinVersionKeyName) + " = " + Field(info.VersionTableName, info.VersionKeyName);
var join2 = InnerJoin(info.VersionTableName, compareVersionId);
return Select(info.TableColumns, info.VersionTableName) + " FROM " + selectMaxModifiedJoin + " " + join1 + " " + join2;
}
else
{
var selectMaxModified = @"(SELECT " + string.Join(",", info.PrimaryKeyNames.Select(pk => Field(_versionTableAlias1, pk))) + ", MAX(" + Field(_versionTableAlias1, _sliceDateColumnName) + ") as " + _sliceDateColumnName + " "
+ From(info.VersionTableName, _versionTableAlias1) + " "
+ " {filter} "
+ " GROUP BY " + string.Join(",", info.PrimaryKeyNames.Select(pk => Field(_versionTableAlias1, pk))) + ") " + versionTableAlias;
// join all columns from main table
var comparePrimaryKey = string.Join(" AND ", info.PrimaryKeyNames.Select(pk => Field(versionTableAlias, pk) + " = " + Field(info.VersionTableName, pk)));
var compareSliceDate = Field(versionTableAlias, _sliceDateColumnName) + " = " + Field(info.VersionTableName, _sliceDateColumnName);
var join1 = InnerJoin(info.VersionTableName, comparePrimaryKey + " AND " + compareSliceDate);
return Select(info.TableColumns, info.VersionTableName) + " FROM " + selectMaxModified + " " + join1;
}
}
public static string GenerateSelectAll(SqlInfo info)
{
return GenerateSelectBy(info, null) + " " + WhereTenantRelated(info.TableName, info.TenantRelated) + " ";
}
public static string GenerateInsertToTemp(SqlInfo info)
{
return "DECLARE " + _tempTable + " TABLE (ItemId uniqueidentifier);" +
"INSERT INTO "+ _tempTable + " " +
"SELECT " + Field(info.TableName, info.PrimaryKeyNames.First()) + " FROM " + info.TableName + " ";//TODO FIX TO MANY KEYS
}
public static string GenerateWhereJoinPkVersion(SqlInfo info)
{
return Where(new[] { info.JoinPrimaryKeyNames.First() }, info.JoinVersionTableName) + AndTenantRelated(info.JoinVersionTableName, info.TenantRelated) + " "; //TODO FIX TO MANY KEYS
}
public static string GenerateInsertToVersionTable(SqlInfo info)
{
var columns = GetFullColumnList(info);
var classProperties = Fields(columns, info.TableName);
var classValues = Values(columns);
if (info.JoinTableName != null)
{
var joinFields = GetFullJoinedColumnList(info);
var joinClassProperties = Fields(joinFields, info.JoinTableName);
// use versionId & primary key from inherite model
var values = joinFields.Select(c => c == info.JoinVersionKeyName ? info.VersionKeyName : c == info.JoinPrimaryKeyNames.First() ? info.PrimaryKeyNames.First() : c).ToList(); //TODO FIX TO MANY KEYS
var joinClassValues = Values(values);
return "INSERT INTO " + info.JoinVersionTableName + "(" + joinClassProperties + ")\r\n" +
"VALUES (" + joinClassValues + ")\r\n" +
"INSERT INTO " + info.VersionTableName + "(" + classProperties + ")\r\n" +
"VALUES (" + classValues + ")";
}
if(!info.IsManyToMany)
{
return "INSERT INTO " + info.VersionTableName + "(" + classProperties + ")\r\n" +
"VALUES (" + classValues + ")";
}
return "INSERT INTO " + info.VersionTableName + "(" + classProperties + ")\r\n" +
"VALUES (" + classValues + ")";
}
public static string GenerateOrderBySliceDate(SqlInfo info)
{
return OrderBy("Modified", info.JoinTableName, true);
}
