public async Task <List <ProgressItem> > LoadByCorrelationIdAsync(Guid correlationId, CancellationToken cancellationToken)
{
var items = new List <ProgressItem>();
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Progress.LoadByCorrelationId");
cmd.AddParameter("@CorrelationId", correlationId);
using (var rdr = await cmd.ExecuteReaderAsync(cancellationToken))
{
while (await rdr.ReadAsync(cancellationToken))
{
var workItem = new ProgressItem
{
CorrelationId = rdr.Map <Guid>(nameof(ProgressItem.CorrelationId)),
Id = rdr.Map <Guid>(nameof(ProgressItem.Id)),
Name = rdr.Map <string>(nameof(ProgressItem.Name)),
Note = rdr.Map <string>(nameof(ProgressItem.Note)),
Queue = rdr.Map <string>(nameof(ProgressItem.Queue)),
RecordedOn = rdr.Map <DateTime>(nameof(ProgressItem.RecordedOn)),
Value = rdr.Map <int>(nameof(ProgressItem.Value)),
WorkId = rdr.Map <Guid>(nameof(ProgressItem.WorkId))
};
items.Add(workItem);
}
}
}, cancellationToken);
return(items);
}
private async Task <decimal> SumAmount(GetExpenseSumQuery query)
{
using (var sql = GetSqlConnection())
{
return(Math.Round(await sql.ExecuteScalarAsync <decimal>(SqlLoader.Load(SqlScripts.GetExpenseSumInDefaultCurrency), query), 2));
}
}
public async Task <IWorkProvider> CreateAsync(string[] queues, CancellationToken cancellationToken = default(CancellationToken))
{
try
{
await SyncLock.WaitAsync(cancellationToken);
if (!_initialized)
{
await SqlRunner.ExecuteAsync(async cmd =>
{
var schemaSetup = await SqlLoader.GetScript("Schema.Setup");
var workSetup = await SqlLoader.GetScript("Work.Setup");
cmd.CommandText = $"{schemaSetup};\r\n{workSetup}";
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, _sqlServerQuidjiboConfiguration.ConnectionString, false, cancellationToken);
_initialized = true;
}
return(new SqlWorkProvider(
_loggerFactory.CreateLogger <SqlWorkProvider>(),
_sqlServerQuidjiboConfiguration.ConnectionString,
queues,
_sqlServerQuidjiboConfiguration.LockInterval,
_sqlServerQuidjiboConfiguration.BatchSize,
_sqlServerQuidjiboConfiguration.DaysToKeep));
}
finally
{
SyncLock.Release();
}
}
public async Task <IProgressProvider> CreateAsync(string[] queues, CancellationToken cancellationToken = default(CancellationToken))
{
if (_provider != null)
{
return(_provider);
}
try
{
await SyncLock.WaitAsync(cancellationToken);
await SqlRunner.ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Progress.Setup");
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, _connectionString, false, cancellationToken);
_provider = new SqlProgressProvider(_connectionString);
return(_provider);
}
finally
{
SyncLock.Release();
}
}
public async Task <IWorkProvider> CreateAsync(string[] queues, CancellationToken cancellationToken = default(CancellationToken))
{
try
{
await SyncLock.WaitAsync(cancellationToken);
if (!_initialized)
{
await SqlRunner.ExecuteAsync(async cmd =>
{
var schemaSetup = await SqlLoader.GetScript("Schema.Setup");
var workSetup = await SqlLoader.GetScript("Work.Setup");
cmd.CommandText = $"{schemaSetup};\r\n{workSetup}";
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, _connectionString, false, cancellationToken);
_initialized = true;
}
return(new SqlWorkProvider(_connectionString, queues, _visibilityTimeout, _batchSize));
}
finally
{
SyncLock.Release();
}
}
public async Task <decimal> GetSum(IncomeGetSumBinding binding)
{
using (var context = GetMainContext())
{
int targetCurrencyId = context.GetCurrencyId(binding.TargetCurrencyId, UserId);
var incomeIds = context.Incomes.WhereUser(UserId)
.Where(binding, context)
.Select(x => x.Id)
.ToList();
if (incomeIds.Count == 0)
{
return(0);
}
using (var sql = GetSqlConnection())
{
var parameters = new GetIncomeSumQuery()
{
IncomeIds = incomeIds,
TargetCurrencyId = targetCurrencyId,
UserId = UserId
};
return(Math.Round(await sql.ExecuteScalarAsync <decimal>(SqlLoader.Load(SqlScripts.GetIncomeSum), parameters), 2));
}
}
}
public async Task <Unit> Handle(UpdateCelestialObjectCommand request, CancellationToken cancellationToken)
{
var sqlParams = new
{
request.Id,
request.Magnitude,
request.AbsoluteMagnitude,
request.Name,
ObjectType = request.ObjectType.ToString(),
request.Designation1,
request.Designation2,
request.Designation3,
request.Designation4,
request.Description,
request.Distance,
request.DistanceTolerance
};
var result = await _dataRepo.ExecuteAsync(
SqlLoader.GetSql(SqlResourceNames.CelestialObjects.CelestialObject_Update),
sqlParams);
if (result == 0)
{
throw new NotFoundException($"Unable to find resource with id '{request.Id}'for deletion.");
}
return(Unit.Value);
}
public List <InvoiceAutoItem> ListLot(Guid id_document)
{
string str = "exec USP_INVOICE_AUTO_REST null, '{0}'";
SqlLoader <InvoiceAutoItem> sqlLoader = this.loader;
object[] idDocument = new object[] { id_document };
return(sqlLoader.GetList(str, idDocument));
}
public List <InvoiceAutoItem> ListRest(long id_store)
{
string str = "exec USP_INVOICE_AUTO_REST {0}, null";
SqlLoader <InvoiceAutoItem> sqlLoader = this.loader;
object[] idStore = new object[] { id_store };
return(sqlLoader.GetList(str, idStore));
}
public async Task <List <WorkItem> > ReceiveAsync(string worker, CancellationToken cancellationToken)
{
var receiveOn = DateTime.UtcNow;
var deleteOn = _daysToKeep > 0 ? receiveOn.AddDays(-_daysToKeep) : receiveOn.AddHours(-1);
if (_receiveSql == null)
{
_receiveSql = await SqlLoader.GetScript("Work.Receive");
if (_queues.Length > 0)
{
_receiveSql = _receiveSql.Replace("@Queue1",
string.Join(",", _queues.Select((x, i) => $"@Queue{i}")));
}
}
var workItems = new List <WorkItem>(_batchSize);
await ExecuteAsync(async cmd =>
{
cmd.CommandText = _receiveSql;
cmd.AddParameter("@Worker", worker);
cmd.AddParameter("@Take", _batchSize);
cmd.AddParameter("@InFlight", StatusFlags.InFlight);
cmd.AddParameter("@VisibleOn", receiveOn.AddSeconds(Math.Max(_visibilityTimeout, 30)));
cmd.AddParameter("@ReceiveOn", receiveOn);
cmd.AddParameter("@MaxAttempts", _maxAttempts);
cmd.AddParameter("@DeleteOn", deleteOn);
// dynamic parameters
for (var i = 0; i < _queues.Length; i++)
{
cmd.Parameters.AddWithValue($"@Queue{i}", _queues[i]);
}
using (var rdr = await cmd.ExecuteReaderAsync(cancellationToken))
{
while (await rdr.ReadAsync(cancellationToken))
{
var workItem = new WorkItem
{
Attempts = rdr.Map <int>(nameof(WorkItem.Attempts)),
CorrelationId = rdr.Map <Guid>(nameof(WorkItem.CorrelationId)),
ExpireOn = rdr.Map <DateTime>(nameof(WorkItem.ExpireOn)),
Id = rdr.Map <Guid>(nameof(WorkItem.Id)),
Name = rdr.Map <string>(nameof(WorkItem.Name)),
Payload = rdr.Map <byte[]>(nameof(WorkItem.Payload)),
Queue = rdr.Map <string>(nameof(WorkItem.Queue)),
ScheduleId = rdr.Map <Guid?>(nameof(WorkItem.ScheduleId))
};
workItem.Token = workItem.Id.ToString();
workItems.Add(workItem);
}
}
}, cancellationToken);
return(workItems);
}
public void Can_get_mapping()
{
var map = SqlLoader.LoadFromConnectionString(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=""C:\Users\Kathleen\Documents\Visual Studio 2013\Projects\CodeFirstMetadata\SchemaTests\TestDatabase.mdf"";Integrated Security=True;Connect Timeout=30");
Assert.AreEqual(7, map.Tables.Count());
Assert.AreEqual(2, map.Views.Count());
Assert.AreEqual(4, map.Tables.First().Columns.Count());
Assert.AreEqual(5, map.Views.First().Columns.Count());
}
public async Task DeleteAsync(Guid id, CancellationToken cancellationToken = default(CancellationToken))
{
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Schedule.Delete");
cmd.AddParameter("@Id", id);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
}
public async Task CompleteAsync(WorkItem item, CancellationToken cancellationToken)
{
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Work.Complete");
cmd.AddParameter("@Id", item.Id);
cmd.AddParameter("@Complete", StatusFlags.Complete);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
}
public async Task CompleteAsync(ScheduleItem item, CancellationToken cancellationToken = default(CancellationToken))
{
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Schedule.Complete");
cmd.AddParameter("@Id", item.Id);
cmd.AddParameter("@EnqueueOn", item.EnqueueOn);
cmd.AddParameter("@EnqueuedOn", item.EnqueuedOn);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
}
public async Task <bool> ExistsAsync(string name, CancellationToken cancellationToken = default(CancellationToken))
{
var count = 0;
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Schedule.Exists");
cmd.AddParameter("@Name", name);
count = (int)await cmd.ExecuteScalarAsync(cancellationToken);
}, cancellationToken);
return(count > 0);
}
public async Task <IEnumerable <CelestialObjectDto> > Handle(SearchCelestialQuery request, CancellationToken cancellationToken)
{
var searchResult = await _dataRepo.QueryAsync <CelestialObjectEntityModel>(
SqlLoader.GetSql(SqlResourceNames.CelestialObjects.CelestialObject_Search),
new { searchText = $"\"{request.SearchText}*\"" });
if (searchResult == null)
{
return(new List <CelestialObjectDto>());
}
return(searchResult.Select(c => _mapper.Map <CelestialObjectDto>(c)));
}
public async Task FaultAsync(WorkItem item, CancellationToken cancellationToken)
{
var faultedOn = DateTime.UtcNow;
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Work.Fault");
cmd.AddParameter("@Id", item.Id);
cmd.AddParameter("@VisibleOn", faultedOn.AddSeconds(Math.Max(_visibilityTimeout, 30)));
cmd.AddParameter("@Faulted", StatusFlags.Faulted);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
}
public async Task <Unit> Handle(DeleteCelestialObjectCommand request, CancellationToken cancellationToken)
{
var result = await _dataRepo.ExecuteAsync(
SqlLoader.GetSql(sqlResourceName: SqlResourceNames.CelestialObjects.CelestialObject_Delete),
new { Id = request.CelestialObjectId });
if (result == 0)
{
throw new NotFoundException($"Unable to find resource with id '{request.CelestialObjectId}'for deletion.");
}
return(Unit.Value);
}
public async Task <CelestialObjectDto> Handle(GetCelestialByIdQuery request, CancellationToken cancellationToken)
{
var celestialObject = await _dataRepo.QueryFirstOrDefaultAsync <CelestialObjectEntityModel>(
SqlLoader.GetSql(SqlResourceNames.CelestialObjects.CelestialObject_Get),
new { Id = request.CelestialId });
if (celestialObject == null)
{
throw new NotFoundException($"No celestial object found for Id: {request.CelestialId}");
}
return(_mapper.Map <CelestialObjectDto>(celestialObject));
}
public IEnumerable <KeyValuePair <int, int> > SumVolumeByDayOfWeek(ConsumationGetBinding binding)
{
using (var sqlConnection = GetSqlConnection())
{
return(sqlConnection.Query <KeyValuePair <int, int> >(SqlLoader.Load(SqlScripts.GetConsumationSumByDayOfWeek),
new {
binding.From,
binding.To,
UserId = UserId
})
.Select(x => new KeyValuePair <int, int>(x.Key == 1 ? 6 : x.Key - 2, x.Value))
.OrderBy(x => x.Key));
}
}
public async Task <DateTime> RenewAsync(WorkItem item, CancellationToken cancellationToken)
{
var lockExpireOn = (item.VisibleOn ?? DateTime.UtcNow).AddSeconds(Math.Max(_visibilityTimeout, 30));
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Work.Renew");
cmd.AddParameter("@Id", item.Id);
cmd.AddParameter("@VisibleOn", lockExpireOn);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
return(lockExpireOn);
}
public List <InvoiceAutoItem> ListRest(long id_store, long id_store_to)
{
var logger = new SimpleLogger();
/*2019 расчет перемещения товара*/
string str = "exec USP_INVOICE_AUTO_REST_2 {0}, null, {1}";
//str = str.Replace("{0}", id_store.ToString());
//str = str.Replace("{1}", id_store_to.ToString());
logger.Trace("Запускаем выборку по складу " + str);
SqlLoader <InvoiceAutoItem> sqlLoader = this.loader;
object[] idStore = new object[] { id_store, id_store_to };
return(sqlLoader.GetList(str, idStore));
//return sqlLoader.GetList(str);
}
public async Task CreateAsync(ScheduleItem item, CancellationToken cancellationToken = default(CancellationToken))
{
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Schedule.Create");
cmd.AddParameter("@Id", item.Id);
cmd.AddParameter("@Name", item.Name);
cmd.AddParameter("@Queue", item.Queue);
cmd.AddParameter("@CronExpression", item.CronExpression);
cmd.AddParameter("@CreatedOn", item.CreatedOn);
cmd.AddParameter("@EnqueuedOn", item.EnqueuedOn);
cmd.AddParameter("@EnqueueOn", item.EnqueueOn);
cmd.AddParameter("@VisibleOn", item.VisibleOn);
cmd.AddParameter("@Payload", item.Payload);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
}
public int GetTimeSum(WebTimeGetBinding binding)
{
using (var db = GetSqlConnection())
{
var parameters = new
{
DeviceValueId = binding.DeviceId,
DomainValueId = binding.DomainId,
WebValueId = binding.WebId,
IsSecured = binding.IsSecured,
From = binding.From,
To = binding.To,
UserId = UserId
};
return(db.ExecuteScalar <int>(SqlLoader.Load(SqlScripts.GetWebTimeTotal), parameters));
}
}
public async Task ReportAsync(ProgressItem item, CancellationToken cancellationToken)
{
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Progress.Create");
cmd.AddParameter("@Id", item.Id);
cmd.AddParameter("@WorkId", item.WorkId);
cmd.AddParameter("@CorrelationId", item.CorrelationId);
cmd.AddParameter("@Name", item.Name);
cmd.AddParameter("@Queue", item.Queue);
cmd.AddParameter("@RecordedOn", item.RecordedOn);
cmd.AddParameter("@Value", item.Value);
cmd.AddParameter("@Note", item.Note);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
await Task.CompletedTask;
}
public async Task <ScheduleItem> LoadByNameAsync(string name, CancellationToken cancellationToken = default(CancellationToken))
{
var item = default(ScheduleItem);
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Schedule.LoadByName");
cmd.AddParameter("@Name", name);
using (var rdr = await cmd.ExecuteReaderAsync(cancellationToken))
{
if (await rdr.ReadAsync(cancellationToken))
{
item = MapScheduleItem(rdr);
}
}
}, cancellationToken);
return(item);
}
public IEnumerable <WebTime> GetTimeSummed(WebTimeGetPagedBinding binding)
{
using (var db = GetSqlConnection())
{
var parameters = new
{
DeviceValueId = binding.DeviceId,
From = binding.From,
Page = binding.Page,
PageSize = binding.PageSize,
To = binding.To,
UserId = UserId
};
var command = new CommandDefinition(SqlLoader.Load(SqlScripts.GetWebTimeSum), parameters);
return(db.Query <WebTime>(command));
}
}
public IEnumerable <TimeByDay> GetTimeTotalByDay(WebTimeGetBinding binding)
{
using (var db = GetSqlConnection())
{
var parameters = new
{
DeviceValueId = binding.DeviceId,
DomainValueId = binding.DomainId,
WebValueId = binding.WebId,
IsSecured = binding.IsSecured,
From = binding.From,
To = binding.To,
UserId = UserId
};
var command = new CommandDefinition(SqlLoader.Load(SqlScripts.GetWebTimeTotalByDay), parameters);
return(db.Query <TimeByDay>(command));
}
}
public async Task <IScheduleProvider> CreateAsync(string[] queues, CancellationToken cancellationToken = default(CancellationToken))
{
try
{
await SyncLock.WaitAsync(cancellationToken);
await SqlRunner.ExecuteAsync(async cmd =>
{
var schemaSetup = await SqlLoader.GetScript("Schema.Setup");
var scheduleSetup = await SqlLoader.GetScript("Schedule.Setup");
cmd.CommandText = $"{schemaSetup};\r\n{scheduleSetup}";
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, _connectionString, false, cancellationToken);
return(await Task.FromResult <IScheduleProvider>(new SqlScheduleProvider(_connectionString, queues)));
}
finally
{
SyncLock.Release();
}
}
public IEnumerable <GroupedByMonth <int> > GetTimeTotalByMonth(WebTimeGetBinding binding)
{
using (var db = GetSqlConnection())
{
var parameters = new
{
DeviceValueId = binding.DeviceId,
DomainValueId = binding.DomainId,
WebValueId = binding.WebId,
IsSecured = binding.IsSecured,
From = binding.From,
To = binding.To,
UserId = UserId
};
var command = new CommandDefinition(SqlLoader.Load(SqlScripts.GetWebTimeTotalByMonth), parameters);
return(db.Query <GetWebTimeTotalByMonth>(command)
.Select(x => new GroupedByMonth <int>(x.Seconds, x.Year, x.Month)));
}
}
