public async Task <Guid> SaveChangesAsync()
{
// Dispatch Domain Events collection.
// Choices:
// A) Right BEFORE committing data (EF SaveChanges) into the DB will make a single transaction including
// side effects from the domain event handlers which are using the same DbContext with "InstancePerLifetimeScope" or "scoped" lifetime
// B) Right AFTER committing data (EF SaveChanges) into the DB will make multiple transactions.
// You will need to handle eventual consistency and compensatory actions in case of failures in any of the Handlers.
await DispatchDomainEvents();
using var connection = await _connectionFactory.CreateConnectionAsync();
using var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
var affectedRows = 0;
var transactionId = Guid.NewGuid();
foreach (var change in _changes.Where(c => !c.Sent))
{
change.Sent = true;
switch (change.OperationType)
{
case OperationType.INSERT:
change.DbEntity.Created = _dateTime.UtcNow;
change.DbEntity.CreatedBy = _currentUserService.UserId;
break;
case OperationType.UPDATE:
change.DbEntity.LastModified = _dateTime.UtcNow;
change.DbEntity.LastModifiedBy = _currentUserService.UserId;
break;
}
affectedRows += await connection.ExecuteAsync(change.Sql, change.DbEntity, transaction);
}
foreach (var entry in _integrationEvents)
{
entry.SetTransactionId(transactionId);
affectedRows += await connection.ExecuteAsync(entry.BuildIntegrationEventLogEntryInsertStatement(),
entry, transaction);
}
transaction.Commit();
return(transactionId);
}
public async Task SeedDatabaseAsync()
{
using var connection = await _factory.CreateConnectionAsync();
var tables = await connection.QueryAsync <string>("SHOW TABLES");
if (tables.Any())
{
return;
}
using var transaction = connection.BeginTransaction();
await CreateTables(connection, transaction);
await SeedEnumTables(connection, transaction);
transaction.Commit();
}
public async Task <Bug> GetByIdAsync(Guid id)
{
var sql =
// WorkItem
"SELECT Id, Title, AssignedTo, StateId, TeamId, SprintId, Description, " +
"PriorityId, RepoLink, StateReasonId " +
"FROM WorkItem Where Id = @Id;" +
// Bug
"SELECT EffortOriginalEstimate, EffortRemaining, EffortCompleted, IntegratedInBuild, " +
"StoryPoints, SeverityId, ActivityId, SystemInfo, FoundInBuild " +
"FROM Bug WHERE Id = @Id;" +
// RelatedWorks
"SELECT Id, LinkTypeId, Comment, Url, ReferencedWorkItemId, WorkItemId " +
"FROM RelatedWork WHERE WorkItemId = @Id;" +
// Tags
"SELECT Text FROM Tag WHERE WorkItemId = @Id;" +
// Attachments
"SELECT Path, FileName, MimeType, Created FROM Attachment WHERE WorkItemId = @Id;";
using var connection = await _connectionFactory.CreateConnectionAsync();
using var multi = await connection.QueryMultipleAsync(sql, new { Id = id });
var workItemDao = multi.Read <WorkItemDao>().FirstOrDefault();
if (workItemDao == null)
{
return(null);
}
var bugDao = multi.Read <BugDao>().FirstOrDefault();
var relatedWorkDaos = multi.Read <RelatedWorkDao>();
var tagDaos = multi.Read <TagDao>();
var attachmentDaos = multi.Read <AttachmentDao>();
var bug = bugDao !.ToBug(workItemDao, tagDaos, attachmentDaos, relatedWorkDaos);
return(bug);
}
public async Task <IEnumerable <FilterOcorrenciasVm> > FilterOcorrenciasAsync(FilterOcorrenciasQuery query)
{
query.PrepareStringsForLikeOperation();
var sql = new StringBuilder(
"SELECT DISTINCT o.id, identificador_ocorrencia as IdentificadorOcorrencia, tipo as Tipo, " +
"delegacia_policia_apuracao as DelegaciaPoliciaApuracao, natureza as Natureza, data_hora_fato as DataHoraFato, " +
"data_hora_comunicacao as DataHoraComunicacao, endereco_fato as EnderecoFato, praticado_por_menor as PraticadoPorMenor, " +
"local_periciado as LocalPericiado, tipo_local as TipoLocal, objeto_meio_empregado as ObjetoMeioEmpregado, " +
"o.created as Created, o.created_by as CreatedBy, " +
"(SELECT COUNT(*) FROM pessoas_envolvidas p WHERE id_ocorrencia = o.id) 'PessoasEnvolvidas', " +
"(SELECT COUNT(*) FROM unidades_moveis u WHERE id_ocorrencia = o.id) 'UnidadesMoveis' FROM ocorrencias o " +
"LEFT JOIN pessoas_envolvidas p ON o.id = p.id_ocorrencia " +
"LEFT JOIN unidades_moveis u on o.id = u.id_ocorrencia " +
"WHERE 42 = 42");
if (!string.IsNullOrWhiteSpace(query.IdentificadorOcorrencia))
{
sql.Append(" AND LOWER(identificador_ocorrencia) LIKE @IdentificadorOcorrencia");
}
if (query.Natureza.HasValue)
{
sql.Append(" AND LOWER(natureza) = @Natureza");
}
if (!string.IsNullOrWhiteSpace(query.DelegaciaPoliciaApuracao))
{
sql.Append(" AND LOWER(delegacia_policia_apuracao) LIKE @DelegaciaPoliciaApuracao");
}
if (query.PraticadoPorMenor.HasValue)
{
sql.Append(" AND praticado_por_menor = @PraticadoPorMenor");
}
if (query.LocalPericiado.HasValue)
{
sql.Append(" AND local_periciado = @LocalPericiado");
}
if (!string.IsNullOrWhiteSpace(query.TipoLocal))
{
sql.Append(" AND LOWER(tipo_local) LIKE @TipoLocal");
}
if (!string.IsNullOrWhiteSpace(query.ObjetoMeioEmpregado))
{
sql.Append(" AND LOWER(objeto_meio_empregado) LIKE @ObjetoMeioEmpregado");
}
if (query.CriadoDe.HasValue)
{
sql.Append(" AND o.created > @CriadoDe");
}
if (query.CriadoAte.HasValue)
{
sql.Append(" AND o.created < @CriadoAte");
}
if (!string.IsNullOrWhiteSpace(query.PessoaEnvolvidaNome))
{
sql.Append(" AND LOWER(nome) LIKE @PessoaEnvolvidaNome;");
}
if (!string.IsNullOrWhiteSpace(query.PessoaEnvolvidaEnvolvimento))
{
sql.Append(" AND LOWER(envolvimento) LIKE @PessoaEnvolvidaEnvolvimento");
}
if (!string.IsNullOrWhiteSpace(query.PessoaEnvolvidaCpf))
{
sql.Append(" AND LOWER(cpf) LIKE @PessoaEnvolvidaCpf");
}
if (!string.IsNullOrWhiteSpace(query.PessoaEnvolvidaProfissao))
{
sql.Append(" AND LOWER(profissao) LIKE @PessoaEnvolvidaProfissao");
}
if (!string.IsNullOrWhiteSpace(query.PessoaEnvolvidaGravidadeLesoes))
{
sql.Append(" AND LOWER(gravidade_lesoes) LIKE @PessoaEnvolvidaGravidadeLesoes");
}
if (!string.IsNullOrWhiteSpace(query.PessoaEnvolvidaRacaCor))
{
sql.Append(" AND LOWER(raca_cor) LIKE @PessoaEnvolvidaRacaCor");
}
if (!string.IsNullOrWhiteSpace(query.UnidadeMovelMatriculaResponsavel))
{
sql.Append(" AND LOWER(matricula_responsavel) LIKE @UnidadeMovelMatriculaResponsavel");
}
if (!string.IsNullOrWhiteSpace(query.UnidadeMovelUnidadeResponsavel))
{
sql.Append(" AND LOWER(unidade_responsavel) LIKE @UnidadeMovelUnidadeResponsavel");
}
if (!string.IsNullOrWhiteSpace(query.UnidadeMovelPrefixoVtr))
{
sql.Append(" AND LOWER(prefixo_vtr) LIKE @UnidadeMovelPrefixoVtr");
}
if (!string.IsNullOrWhiteSpace(query.UnidadeMovelResponsavel))
{
sql.Append(" AND LOWER(responsavel) LIKE @UnidadeMovelResponsavel");
}
if (query.PessoaEnvolvidaSexo.HasValue)
{
sql.Append($" AND LOWER(sexo) = LOWER(@PessoaEnvolvidaSexo)");
}
using var connection = await _connectionFactory.CreateConnectionAsync();
return(await connection.QueryAsync <FilterOcorrenciasVm>(sql.ToString(), query));
}
