public async Task <List <ServicosOS> > GetServicosOrdemServicoResultSet(NpgsqlCommand command)
{
List <ServicosOS> list = new List <ServicosOS>();
command.ExecuteNonQuery();
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
DataTable schemaTable = reader.GetSchemaTable();
JTokenWriter writer = new JTokenWriter();
writer.WriteStartObject();
foreach (DataRow row in schemaTable.Rows)
{
writer.WritePropertyName(row[0].ToString());
writer.WriteValue(reader[row[0].ToString()]);
}
writer.WriteEndObject();
JObject o = (JObject)writer.Token;
var stringJson = o.ToString();
ServicosOS p = JsonConvert.DeserializeObject <ServicosOS>(stringJson);
list.Add(p);
}
return(list);
}
public override async Task <OrdensServico> Inserir(OrdensServico ordemServico)
{
using (var conexao = GetCurrentConnection())
{
conexao.Open();
NpgsqlTransaction transaction = conexao.BeginTransaction();
try
{
string sql = @"INSERT INTO ordensservico(codigofornecedor, dtinicial, dtfinal, valorservicos, valorprodutos, valortotal, codigocondicaopagamento, dtcadastro, dtalteracao, status) VALUES (@codigoFornecedor, @dtInicial, @dtFinal, @valorServicos, @valorProdutos, @valorTotal, @codigoCondicaoPagamento, @dtCadastro, @dtAlteracao, @status) returning codigo;";
NpgsqlCommand command = new NpgsqlCommand(sql, conexao);
command.Parameters.AddWithValue("@codigoFornecedor", ordemServico.codigoFornecedor);
command.Parameters.AddWithValue("@dtInicial", ordemServico.dtInicial);
command.Parameters.AddWithValue("@dtFinal", ordemServico.dtFinal);
command.Parameters.AddWithValue("@valorServicos", ordemServico.valorServicos);
command.Parameters.AddWithValue("@valorProdutos", ordemServico.valorProdutos);
command.Parameters.AddWithValue("@valorTotal", ordemServico.valorTotal);
command.Parameters.AddWithValue("@codigoCondicaoPagamento", ordemServico.codigoCondicaoPagamento);
command.Parameters.AddWithValue("@dtCadastro", ordemServico.dtCadastro);
command.Parameters.AddWithValue("@dtAlteracao", ordemServico.dtAlteracao);
command.Parameters.AddWithValue("@status", ordemServico.status);
Object idInserido = await command.ExecuteScalarAsync();
ordemServico.codigo = (int)idInserido;
int qtdServicos = ordemServico.servicos.Count;
if (qtdServicos > 0)
{
for (int i = 0; i < qtdServicos; i++)
{
ServicosOS servicoOrdemServico = ordemServico.servicos[i];
ordemServico.servicos[i] = await InserirServicosOrdemServico(conexao, servicoOrdemServico, ordemServico.codigo);
}
}
int qtdItens = ordemServico.itens.Count;
if (qtdItens > 0)
{
for (int i = 0; i < qtdItens; i++)
{
ItensCompra itemOrdemServico = ordemServico.itens[i];
ordemServico.itens[i] = await InserirItensOrdemServico(conexao, itemOrdemServico, ordemServico.codigo);
}
}
int qtdParcelas = ordemServico.parcelas.Count;
if (qtdParcelas > 0)
{
for (int i = 0; i < qtdParcelas; i++)
{
ParcelasCompra parcelaordemServico = ordemServico.parcelas[i];
parcelaordemServico.dtEmissao = (DateTime)ordemServico.dtCadastro;
parcelaordemServico.pendente();
ordemServico.parcelas[i] = await InserirParcelasOrdemServico(conexao, parcelaordemServico, ordemServico);
}
}
transaction.Commit();
return(ordemServico);
}
catch
{
transaction.Rollback();
throw;
}
finally
{
conexao.Close();
}
}
}
public async Task <ServicosOS> InserirServicosOrdemServico(NpgsqlConnection conexao, ServicosOS servico, int codigoOS)
{
string sql = @"INSERT INTO servicosos(codigoos, codigoservico, quantidade, valorunitario, total) VALUES (@codigoOS, @codigoServico, @quantidade, @valorUnitario, @total);";
NpgsqlCommand command = new NpgsqlCommand(sql, conexao);
command.Parameters.AddWithValue("@codigoOS", codigoOS);
command.Parameters.AddWithValue("@codigoServico", servico.codigoServico);
command.Parameters.AddWithValue("@quantidade", servico.quantidade);
command.Parameters.AddWithValue("@valorUnitario", servico.valorUnitario);
command.Parameters.AddWithValue("@total", servico.total);
await command.ExecuteScalarAsync();
return(servico);
}
