public void OnPrepareQuery(IListRequestHandler handler, SqlQuery query)
{
var user = (UserDefinition)Authorization.UserDefinition;
if (!Authorization.HasPermission(PermissionKeys.Tenants))
query.Where(fldTenantId == user.TenantId);
}
public void SubQueryCanBeUsedAsCriteriaUsingVar()
{
var query = new SqlQuery()
.From("ParentTable")
.Select("ParentColumn");
query.Where(new Criteria(query.SubQuery()
.From("SubTable")
.Take(1)
.Select("SubColumn")) >= 1);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT ParentColumn FROM ParentTable WHERE " +
"((SELECT TOP 1 SubColumn FROM SubTable) >= @p1)"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void OnPrepareQuery(IListRequestHandler handler, SqlQuery query)
{
if (ReferenceEquals(null, Target) ||
handler.Request.EqualityFilter == null ||
!attr.HandleEqualityFilter)
return;
object value;
if (handler.Request.EqualityFilter.TryGetValue(Target.PropertyName, out value) ||
handler.Request.EqualityFilter.TryGetValue(Target.Name, out value))
{
if (value == null || value as string == "")
return;
var values = new List<object>();
if (!(value is string) && value is IEnumerable)
{
foreach (var val in (IEnumerable)value)
values.Add(itemKeyField.ConvertValue(val, CultureInfo.InvariantCulture));
}
else
{
values.Add(itemKeyField.ConvertValue(value, CultureInfo.InvariantCulture));
}
if (values.Count > 0)
{
var ls = new Alias(itemKeyField.Fields.TableName, "__ls");
query.Where(Criteria.Exists(
query.SubQuery()
.From(ls)
.Select("1")
.Where(
new Criteria(ls[thisKeyField]) == new Criteria((Field)((IIdRow)handler.Row).IdField) &
new Criteria(ls[itemKeyField]).In(values))
.ToString()));
}
handler.IgnoreEqualityFilter(Target.PropertyName);
handler.IgnoreEqualityFilter(Target.Name);
}
}
public static Dictionary<Int64, string> GetIdNameDictionary(IDbConnection connection, IIdRow row, StringField nameField,
IEnumerable<Int64> idList)
{
var list = new List<Int64>(idList);
var dictionary = new Dictionary<Int64, string>();
if (list.Count <= 0)
return dictionary;
list.Sort();
var theRow = (Row)row;
Int64[] part = null;
const int step = 100;
var i = 0;
while (i < list.Count)
{
var start = i;
var end = start + step;
if (end >= list.Count)
end = list.Count - 1;
var len = end - start + 1;
if (part == null || len != part.Length)
part = new Int64[len];
list.CopyTo(start, part, 0, len);
var query = new SqlQuery().Select(((Field)row.IdField).Name).Select(nameField.Name).From(theRow.Table);
query.Where(new Criteria((Field)row.IdField).In(part));
using (var reader = SqlHelper.ExecuteReader(connection, query))
while (reader.Read())
dictionary[reader.ToInt64(0).Value] = reader.AsString(1);
i += step;
}
return dictionary;
}
public static AuditLogListResponse List(string schema, AuditLogListRequest request)
{
#if COREFX
var fld = Dependency.Resolve<IAuditLogRow>();
#else
var fld = Dependency.Resolve<IAuditLogRow>(schema);
#endif
Authorization.ValidateLoggedIn();
var response = new AuditLogListResponse();
using (var connection = SqlConnections.NewByKey("Default"))
{
response.Entities = new List<Row>();
var row = ((Row)fld).CreateNew();
if (request.Sort == null ||
request.Sort.Length == 0)
request.Sort = new SortBy[] { new SortBy(fld.DateField.Name, true) };
var query = new SqlQuery().From(row)
.Select(
(Field)fld.IdField,
fld.EntityTypeIdField,
fld.EntityIdField,
fld.ParentTypeIdField,
fld.OldParentIdField,
fld.NewParentIdField,
fld.DateField,
(Field)fld.UserIdField,
fld.AuditTypeIdField,
fld.OldAuditDataField,
fld.NewAuditDataField)
.OrderBy(
(Field)fld.IdField)
.ApplySkipTakeAndCount(request.Skip, request.Take, request.ExcludeTotalCount)
.ApplySort(request.Sort);
if (request.EntityTypeId != null &&
request.EntityId != null)
{
var pEntityId = query.AddParam(request.EntityId);
var pEntityTypeId = query.AddParam(request.EntityTypeId);
query.Where(~(
~(
new Criteria(0, fld.EntityTypeIdField) == pEntityTypeId &
new Criteria(0, fld.EntityIdField) == pEntityId) |
~(
new Criteria(0, fld.ParentTypeIdField) == pEntityTypeId &
~(
new Criteria(0, fld.OldParentIdField) == pEntityId |
new Criteria(0, fld.NewParentIdField) == pEntityId))));
}
else
{
if (request.EntityTypeId != null)
query.WhereEqual(fld.EntityTypeIdField, request.EntityTypeId);//Convert.ToInt32(request.EntityTypeId.Value));
if (request.EntityId != null)
query.WhereEqual(fld.EntityIdField, request.EntityId.Value);
}
response.TotalCount = query.ForEach(connection, delegate()
{
response.Entities.Add(row.Clone());
});
response.SetSkipTakeTotal(query);
response.IdNameLookups = new Dictionary<EntityType, Dictionary<long, string>>();
response.FieldTitles = new Dictionary<EntityType, Dictionary<string, string>>();
response.ForeignEntityTypes = new Dictionary<EntityType, Dictionary<string, string>>();
response.EntityTitles = new Dictionary<EntityType, string>();
var lookups = response.IdNameLookups;
var titles = response.FieldTitles;
var foreigns = response.ForeignEntityTypes;
var entities = response.EntityTitles;
Action<EntityType, Int64> addLookup = (entityType, id) => {
Dictionary<long, string> lookup;
if (!lookups.TryGetValue(entityType, out lookup))
{
lookup = new Dictionary<long, string>();
lookups[entityType] = lookup;
}
if (!lookup.ContainsKey(id))
lookup[id] = null;
};
Action<EntityType, string> addTitle = (entityType, field) =>
{
Dictionary<string, string> lookup;
if (!titles.TryGetValue(entityType, out lookup))
{
lookup = new Dictionary<string, string>();
titles[entityType] = lookup;
}
if (!lookup.ContainsKey(field))
lookup[field] = null;
};
Action<EntityType> addEntity = (entityType) =>
{
if (!entities.ContainsKey(entityType))
{
//Row r;
String s = null;
// TODO: FIX!
//if (schema.TypeToTable.TryGetValue(entityType, out r))
// s = LocalText.TryGet(1055, "Db." + r.Table + ".EntitySingular", false);
s = s ?? Enum.GetName(typeof(EntityType), entityType);
entities[entityType] = s;
}
};
Action<EntityType, string, EntityType> addForeign = (entityType, field, foreignType) =>
{
Dictionary<string, string> foreign;
if (!foreigns.TryGetValue(entityType, out foreign))
{
foreign = new Dictionary<string, string>();
foreigns[entityType] = foreign;
}
if (!foreign.ContainsKey(field))
foreign[field] = Enum.GetName(typeof(EntityType), foreignType);
};
foreach (var entity in response.Entities)
{
addEntity(fld.EntityTypeIdField[entity]);
addLookup(fld.EntityTypeIdField[entity], fld.EntityIdField[entity].Value);
if (fld.ParentTypeIdField[entity] != null)
addEntity(fld.ParentTypeIdField[entity]);
//if (entity.UserId != null)
// addLookup(UserRow.TableName, entity.UserId.Value);
Row theRow;
if (((AuditType?)fld.AuditTypeIdField[entity] == AuditType.Insert ||
(AuditType?)fld.AuditTypeIdField[entity] == AuditType.Update) &&
(fld.OldAuditDataField[entity] != null || fld.NewAuditDataField[entity] != null))
{
theRow = RowRegistry.GetConnectionRow(RowRegistry.DefaultConnectionKey, fld.EntityTypeIdField[entity]);
if (theRow == null)
continue;
UpdateAuditDataDictionary ud = new UpdateAuditDataDictionary();
if (fld.OldAuditDataField[entity] != null)
ud.Old = JsonConvert.DeserializeObject<Dictionary<string, object>>(fld.OldAuditDataField[entity].TrimToNull() ?? "{}", JsonSettings.Tolerant);
if (fld.NewAuditDataField[entity] != null)
ud.New = JsonConvert.DeserializeObject<Dictionary<string, object>>(fld.OldAuditDataField[entity].TrimToNull() ?? "{}", JsonSettings.Tolerant);
for (var i = 0; i < 2; i++)
{
var d = (i == 0) ? ud.Old : ud.New;
if (d != null)
foreach (var p in d)
{
addTitle(fld.EntityTypeIdField[entity], p.Key);
if (p.Value != null &&
p.Value is Int16 ||
p.Value is Int32 ||
p.Value is Int64)
{
var f = theRow.FindField(p.Key);
if (!ReferenceEquals(null, f) &&
f.ForeignTable != null)
{
//EntityType foreignType;
//if (schema.TableToType.TryGetValue(f.ForeignTable, out foreignType))
{
addForeign(fld.EntityTypeIdField[entity], p.Key, f.ForeignTable);
addLookup(f.ForeignTable, Convert.ToInt64(p.Value));
}
}
}
}
}
}
}
foreach (var pair in response.IdNameLookups)
{
Row entity = RowRegistry.GetConnectionRow(RowRegistry.DefaultConnectionKey, pair.Key);
if (entity != null)
{
var idRow = entity as IIdRow;
var nameRow = entity as INameRow;
if (idRow != null &&
nameRow != null)
{
var lookup = pair.Value;
var idName = GetIdNameDictionary(connection, (IIdRow)entity, ((INameRow)entity).NameField, lookup.Keys);
foreach (var p in idName)
lookup[p.Key] = p.Value;
}
}
}
foreach (var pair in response.FieldTitles)
{
Row entity = RowRegistry.GetConnectionRow(RowRegistry.DefaultConnectionKey, pair.Key);
if (entity != null)
{
var lookup = pair.Value;
var keys = new string[lookup.Keys.Count];
lookup.Keys.CopyTo(keys, 0);
foreach (var key in keys)
{
Field f;
if (key.EndsWith("Id"))
{
var s = key.Substring(0, key.Length - 2);
f = entity.FindField(s);
if (!ReferenceEquals(null, f))
{
lookup[key] = f.Title;
continue;
}
}
f = entity.FindField(key);
if (!ReferenceEquals(null, f))
lookup[key] = f.Title;
}
}
}
return response;
}
}
internal static string ProcessReplaceFields(string s, Dictionary<string, Field> replaceFields, ISaveRequestHandler handler)
{
if (replaceFields == null)
return s;
var row = handler.Row;
// foreign / calculated fields might not be available yet in new row
// so load them from database
// TODO: if referenced foreign fields changed on update,
// values might be wrong in before update where we set filename
// so need to handle update in AfterSave just like create
if (handler.IsCreate &&
replaceFields.Values.Any(x => !x.IsTableField()))
{
var idField = (Field)(((IIdRow)handler.Row).IdField);
row = handler.Row.Clone();
var query = new SqlQuery()
.From(row);
foreach (var field in replaceFields.Values)
query.Select(field);
query.Where(idField == new ValueCriteria(idField.AsObject(row)));
query.GetFirst(handler.Connection);
}
foreach (var p in replaceFields)
{
var val = p.Value.AsObject(row);
string str;
var colon = p.Key.IndexOf(":");
if (colon >= 0)
str = String.Format("{0:" + p.Key.Substring(colon + 1, p.Key.Length - colon - 2) + "}", val);
else
str = Convert.ToString(val ?? "", CultureInfo.InvariantCulture);
str = StringHelper.SanitizeFilename(str).Replace('\\', '_').Replace("..", "_");
if (string.IsNullOrWhiteSpace(str))
str = "_";
while (str.EndsWith("."))
str = str.Substring(0, str.Length - 1) + "_";
s = s.Replace(p.Key, str);
}
while (s.IndexOf("//") > 0)
s = s.Replace("//", "/_/");
return s;
}