public bool GetDependsOnItem(string valueTableName, DependencyItemModel dependence, IndexItemModel item, out IndexItemModel dependsOnItem)
{
var referenceKeyParams = dependence.ReferenceKeysArr;
var foreignKeyParams = dependence.ForeignKeysArr;
var @params = foreignKeyParams
.Select((fk, i) => new { fk, i })
.Select(fk => new KeyValuePair <string, string>(referenceKeyParams[fk.i], item[fk.fk].ToString()))
.ToDictionary(fk => fk.Key, fk => fk.Value);
var dynamicParams = new DynamicParameters();
foreach (var p in @params)
{
if (string.IsNullOrWhiteSpace(p.Value)) // An item has dependency with NULL value is not depends on anything
{
dependsOnItem = null;
return(false);
}
dynamicParams.Add(p.Key, p.Value);
}
var sql = $@"
SELECT TOP 1 * FROM {valueTableName}
WHERE {string.Join(" AND ", @params.Select(p => $@"[{p.Key}] = @{p.Key}"))}
";
dependsOnItem = _connection.Query <object>(sql, param: dynamicParams, transaction: _transaction)
.Select(d => IndexItemModel.FromJObject(JObject.FromObject(d))).FirstOrDefault();
return(true);
}
public IEnumerable <IndexItemModel> GetIndexedItemsBySourceId(IIndexModel model, string id)
{
var items = _connection.Query($@"
SELECT * FROM [{model.ValueTableName}]
WHERE [SourceId] = @Id", param: new { Id = id }, transaction: _transaction) as IEnumerable <IDictionary <string, object> >;
return(items?.Select(i => IndexItemModel.FromJObject(JObject.FromObject(i))));
}
public IndexItemModel GetIndexedItemDestinationId(IIndexModel model, string id)
{
var items = _connection.Query($@"
SELECT * FROM [{model.ValueTableName}]
WHERE [DestinationId] = @Id", param: new { Id = id }, transaction: _transaction) as IEnumerable <IDictionary <string, object> >;
var item = items?.FirstOrDefault();
return(item != null?IndexItemModel.FromJObject(JObject.FromObject(item)) : null);
}
public IEnumerable <IndexItemModel> GetIndexChangedItems(
IIndexModel model,
int limit,
int offset,
out int totalCount)
{
var @params = new DynamicParameters();
@params.Add("Limit", limit > 0 ? limit : 100);
@params.Add("Offset", offset);
@params.Add("ChangedState", ItemState.Changed);
/**
* Do not ignore RelatedItemNotFound and RelatedItemNotSync,
* These items should be checked in code logic
* RelatedItemNotFound: it might be found later
* RelatedItemNotSynced: it will be synced later
*/
@params.Add("ExcludeStates", ItemState.Invalid);
// TODO: RETRY COUNT?
var sql = $@"
SELECT v.*
FROM {model.ValueTableName} v
WHERE
v.[DestinationId] IS NULL
OR v.[State] IS NULL
OR v.[State] = 0
OR (
(v.[State] & @ChangedState) > 0
AND (v.[State] & @ExcludeStates) = 0
)
ORDER BY v.[RetryCount], v.[Id]
OFFSET @Offset ROWS
FETCH NEXT @Limit ROWS ONLY;
";
var countSql = $@"
SELECT COUNT(v.Id)
FROM {model.ValueTableName} v
WHERE
v.[DestinationId] IS NULL
OR v.[State] IS NULL
OR v.[State] = 0
OR (
(v.[State] & @ChangedState) > 0
AND (v.[State] & @ExcludeStates) = 0
)
";
totalCount = _connection
.Query <int>(countSql, param: @params, transaction: _transaction)
.FirstOrDefault();
var result = _connection
.Query(sql, param: @params, transaction: _transaction) as IEnumerable <IDictionary <string, object> >;
return(result.Select(d => IndexItemModel.FromJObject(JObject.FromObject(d))));
}
public virtual IMapper Map(IEnumerable <object> data)
{
if (data == null)
{
return(this);
}
var destinationIdKey = Options?.FirstOrDefault(o => o.Name == "mapper_id_key").Value;
var foreignKeyStr = Options?.FirstOrDefault(o => o.Name == "mapper_foreign_keys").Value;
var foreignKeys = Regex.Split(foreignKeyStr, "[,;|]", RegexOptions.Multiline | RegexOptions.IgnoreCase);
var referenceKeyStr = Options?.FirstOrDefault(o => o.Name == "mapper_reference_keys").Value;
var referenceKeys = Regex.Split(referenceKeyStr, "[,;|]", RegexOptions.Multiline | RegexOptions.IgnoreCase);
var affectedRows = 0;
foreach (var item in data)
{
var jItem = JObject.FromObject(item);
var destinationId = jItem.GetValue(destinationIdKey).ToString();
var queryParams = new DynamicParameters();
var conditions = new List <string>();
// !!! NEVER CHECK FOR DEPENDENCIES. An entity cannot be mapped if its values are required to be check SOURCE v.s DEST with dependencies
for (var i = 0; i < foreignKeys.Length; i++)
{
var foreignValue = jItem.GetValue(foreignKeys[i]);
// Foreign values should NEVER BE NULL
conditions.Add($@"[{referenceKeys[i]}] = @{referenceKeys[i]}");
queryParams.Add(referenceKeys[i], foreignValue?.ToString());
}
var indexedItem = Connection
.Query <object>($@"
SELECT * FROM [{EntityModel.ValueTableName}]
WHERE {string.Join(" AND ", conditions)}
", queryParams)
.Select(i => IndexItemModel.FromJObject(JObject.FromObject(i)))
.FirstOrDefault();
if (indexedItem != null)
{
affectedRows += Connection
.Execute($@"
UPDATE [{EntityModel.ValueTableName}]
SET [DestinationId] = @DestinationId
WHERE [Id] = @Id
",
new {
Id = indexedItem.GetId(),
DestinationId = jItem.GetValue(destinationIdKey).ToString()
});
}
}
Report($@"Mapped {affectedRows} item(s).");
return(this);
}
public IEnumerable <IndexItemModel> GetIndexedItems(
IIndexModel model,
IEnumerable <FilterArgument> filters,
int limit,
int offset,
out int totalCount)
{
var @params = new DynamicParameters();
@params.Add("Limit", limit > 0 ? limit : 100);
@params.Add("Offset", offset);
var filterStrs = new List <string>();
var @where = string.Empty;
if (filters != null && filters.Count() > 0)
{
foreach (var filter in filters)
{
var paramName = StringExtensions.StringExtensions.Random(10);
filterStrs.Add($@"[{filter.Field}] {filter.Op} @Param_{paramName}");
@params.Add($@"Param_{paramName}", filter.Target);
}
//var condition = string.Join(" AND ", filters.Select(f => $@"[{f.Field}] {f.Op} {}"));
@where = string.Join(" AND ", filterStrs);
if (!string.IsNullOrWhiteSpace(@where))
{
@where = $"WHERE {@where}";
}
}
var sql = $@"
SELECT * FROM {model.ValueTableName}
{@where}
ORDER BY [Id]
OFFSET @Offset ROWS
FETCH NEXT @Limit ROWS ONLY;
";
var countSql = $@"
SELECT COUNT(*) FROM {model.ValueTableName}
{@where}
";
totalCount = _connection
.Query <int>(countSql, param: @params, transaction: _transaction)
.FirstOrDefault();
var result = _connection
.Query(sql, param: @params, transaction: _transaction) as IEnumerable <IDictionary <string, object> >;
return(result.Select(d => IndexItemModel.FromJObject(JObject.FromObject(d))));
}
public IndexItemModel GetDependsOnItem(string valueTableName, DependencyItemModel dependence, IndexItemModel item)
{
var referenceKeyParams = Regex.Split(dependence.ReferenceKeys, "[,;|]", RegexOptions.Multiline | RegexOptions.IgnoreCase);
var foreignKeyParams = Regex.Split(dependence.ForeignKeys, "[,;|]", RegexOptions.Multiline | RegexOptions.IgnoreCase);
var @params = foreignKeyParams
.Select((fk, i) => new { fk, i })
.Select(fk => new KeyValuePair <string, string>(referenceKeyParams[fk.i], item[fk.fk].ToString()))
.ToDictionary(fk => fk.Key, fk => fk.Value);
var dynamicParams = new DynamicParameters();
foreach (var p in @params)
{
dynamicParams.Add(p.Key, p.Value);
}
var sql = $@"
SELECT TOP 1 * FROM {valueTableName}
WHERE {string.Join(" AND ", @params.Select(p => $@"[{p.Key}] = @{p.Key}"))}
";
var dependsOnItem = _connection.Query <object>(sql, param: dynamicParams, transaction: _transaction)
.Select(d => IndexItemModel.FromJObject(JObject.FromObject(d))).FirstOrDefault();
return(dependsOnItem);
}
