public QueryResult <SyncJob> GetJobs(SyncJobQuery query)
{
var result = _repo.GetJobs(query);
result.Items.ForEach(FillMetadata);
return(result);
}
public QueryResult <SyncJob> GetJobs(SyncJobQuery query)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
using (var cmd = _connection.CreateCommand())
{
cmd.CommandText = BaseJobSelectText;
var whereClauses = new List <string>();
var startIndex = query.StartIndex ?? 0;
if (startIndex > 0)
{
whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM SyncJobs ORDER BY DateLastModified DESC LIMIT {0})",
startIndex.ToString(_usCulture)));
}
if (whereClauses.Count > 0)
{
cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray());
}
cmd.CommandText += " ORDER BY DateLastModified DESC";
if (query.Limit.HasValue)
{
cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(_usCulture);
}
cmd.CommandText += "; select count (Id) from SyncJobs";
var list = new List <SyncJob>();
var count = 0;
using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
list.Add(GetJob(reader));
}
if (reader.NextResult() && reader.Read())
{
count = reader.GetInt32(0);
}
}
return(new QueryResult <SyncJob>()
{
Items = list.ToArray(),
TotalRecordCount = count
});
}
}
public async Task <QueryResult <SyncJob> > GetJobs(SyncJobQuery query)
{
var result = _repo.GetJobs(query);
foreach (var item in result.Items)
{
await FillMetadata(item).ConfigureAwait(false);
}
return(result);
}
public async Task <List <SyncJob> > GetSyncJobs(bool onlyThisDevice = true)
{
var apiClient = _connectionManager.GetApiClient(_serverInfo.ServerInfo.Id);
var query = new SyncJobQuery
{
UserId = AuthenticationService.Current.LoggedInUserId,
TargetId = onlyThisDevice ? apiClient.DeviceId : string.Empty
};
var jobs = await apiClient.GetSyncJobs(query);
if (jobs != null && !jobs.Items.IsNullOrEmpty())
{
return(jobs.Items.ToList());
}
return(new List <SyncJob>());
}
public QueryResult <SyncJob> GetJobs(SyncJobQuery query)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
CheckDisposed();
using (var cmd = _connection.CreateCommand())
{
cmd.CommandText = BaseJobSelectText;
var whereClauses = new List <string>();
if (query.Statuses.Length > 0)
{
var statuses = string.Join(",", query.Statuses.Select(i => "'" + i.ToString() + "'").ToArray());
whereClauses.Add(string.Format("Status in ({0})", statuses));
}
if (!string.IsNullOrWhiteSpace(query.TargetId))
{
whereClauses.Add("TargetId=@TargetId");
cmd.Parameters.Add(cmd, "@TargetId", DbType.String).Value = query.TargetId;
}
if (!string.IsNullOrWhiteSpace(query.UserId))
{
whereClauses.Add("UserId=@UserId");
cmd.Parameters.Add(cmd, "@UserId", DbType.String).Value = query.UserId;
}
if (query.SyncNewContent.HasValue)
{
whereClauses.Add("SyncNewContent=@SyncNewContent");
cmd.Parameters.Add(cmd, "@SyncNewContent", DbType.Boolean).Value = query.SyncNewContent.Value;
}
cmd.CommandText += " mainTable";
var whereTextWithoutPaging = whereClauses.Count == 0 ?
string.Empty :
" where " + string.Join(" AND ", whereClauses.ToArray());
var startIndex = query.StartIndex ?? 0;
if (startIndex > 0)
{
whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM SyncJobs ORDER BY (Select Max(DateLastModified) from SyncJobs where TargetId=mainTable.TargetId) DESC, DateLastModified DESC LIMIT {0})",
startIndex.ToString(_usCulture)));
}
if (whereClauses.Count > 0)
{
cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray());
}
cmd.CommandText += " ORDER BY (Select Max(DateLastModified) from SyncJobs where TargetId=mainTable.TargetId) DESC, DateLastModified DESC";
if (query.Limit.HasValue)
{
cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(_usCulture);
}
cmd.CommandText += "; select count (Id) from SyncJobs" + whereTextWithoutPaging;
var list = new List <SyncJob>();
var count = 0;
using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
list.Add(GetJob(reader));
}
if (reader.NextResult() && reader.Read())
{
count = reader.GetInt32(0);
}
}
return(new QueryResult <SyncJob>()
{
Items = list.ToArray(),
TotalRecordCount = count
});
}
}
public QueryResult <SyncJob> GetJobs(SyncJobQuery query)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
CheckDisposed();
using (WriteLock.Read())
{
using (var connection = CreateConnection(true))
{
var commandText = BaseJobSelectText;
var paramList = new List <object>();
var whereClauses = new List <string>();
if (query.Statuses.Length > 0)
{
var statuses = string.Join(",", query.Statuses.Select(i => "'" + i.ToString() + "'").ToArray());
whereClauses.Add(string.Format("Status in ({0})", statuses));
}
if (!string.IsNullOrWhiteSpace(query.TargetId))
{
whereClauses.Add("TargetId=?");
paramList.Add(query.TargetId);
}
if (!string.IsNullOrWhiteSpace(query.ExcludeTargetIds))
{
var excludeIds = (query.ExcludeTargetIds ?? string.Empty).Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (excludeIds.Length == 1)
{
whereClauses.Add("TargetId<>?");
paramList.Add(excludeIds[0]);
}
else if (excludeIds.Length > 1)
{
whereClauses.Add("TargetId<>?");
paramList.Add(excludeIds[0]);
}
}
if (!string.IsNullOrWhiteSpace(query.UserId))
{
whereClauses.Add("UserId=?");
paramList.Add(query.UserId);
}
if (!string.IsNullOrWhiteSpace(query.ItemId))
{
whereClauses.Add("ItemIds like ?");
paramList.Add("%" + query.ItemId + "%");
}
if (query.SyncNewContent.HasValue)
{
whereClauses.Add("SyncNewContent=?");
paramList.Add(query.SyncNewContent.Value);
}
commandText += " mainTable";
var whereTextWithoutPaging = whereClauses.Count == 0 ?
string.Empty :
" where " + string.Join(" AND ", whereClauses.ToArray());
var startIndex = query.StartIndex ?? 0;
if (startIndex > 0)
{
whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM SyncJobs ORDER BY (Select Max(DateLastModified) from SyncJobs where TargetId=mainTable.TargetId) DESC, DateLastModified DESC LIMIT {0})",
startIndex.ToString(_usCulture)));
}
if (whereClauses.Count > 0)
{
commandText += " where " + string.Join(" AND ", whereClauses.ToArray());
}
commandText += " ORDER BY (Select Max(DateLastModified) from SyncJobs where TargetId=mainTable.TargetId) DESC, DateLastModified DESC";
if (query.Limit.HasValue)
{
commandText += " LIMIT " + query.Limit.Value.ToString(_usCulture);
}
var list = new List <SyncJob>();
var count = connection.Query("select count (Id) from SyncJobs" + whereTextWithoutPaging, paramList.ToArray())
.SelectScalarInt()
.First();
foreach (var row in connection.Query(commandText, paramList.ToArray()))
{
list.Add(GetJob(row));
}
return(new QueryResult <SyncJob>()
{
Items = list.ToArray(),
TotalRecordCount = count
});
}
}
}
