//Insere na tabela de incomes no banco de dados e atualiza o estoque de produtos
public Response Insert(Income income)
{
using (TransactionScope scope = new TransactionScope())
{
Response resultInsertIncome = base.InsertScalar(income);
if (!resultInsertIncome.Success)
{
return(resultInsertIncome);
}
using (DbCommand command = DbFactory.GetCurrentCommand())
{
foreach (IncomeItem incomeItem in income.IncomeItems)
{
incomeItem.IncomeID = resultInsertIncome.GeneratedId;
command.Parameters.Clear();
command.CommandText = @"INSERT INTO INCOMEITEMS (INCOMEID,PRODUCTID,QUANTITY,UNITYPRICE,PROFIT) VALUES (@INCOMEID,@PRODUCTID,@QUANTITY,@UNITYPRICE,@PROFIT); UPDATE PRODUCTS SET PRICE = @UNITYPRICE, STOCK += @QUANTITY WHERE ID = @PRODUCTID";
command.Parameters.AddWithValue("@INCOMEID", incomeItem.IncomeID);
command.Parameters.AddWithValue("@PRODUCTID", incomeItem.ProductID);
command.Parameters.AddWithValue("@QUANTITY", incomeItem.Quantity);
command.Parameters.AddWithValue("@UNITYPRICE", incomeItem.UnityPrice);
command.Parameters.AddWithValue("@PROFIT", incomeItem.Profit);
Response responseInsert = new DbExecuter().ExecuteQuery(command);
if (!responseInsert.Success)
{
return(responseInsert);
}
}
}
scope.Complete();
return(Response.CreateSuccess("Operação efetuada com sucesso!"));
}
}
//Inserena tabela de sales e recupera o ID gerado e faze update no estoque de products
public Response Insert(Sale sale)
{
using (TransactionScope scope = new TransactionScope())
{
Response resultInsert = base.InsertScalar(sale);
if (!resultInsert.Success)
{
return(resultInsert);
}
using (DbCommand cmd = DbFactory.GetCurrentCommand())
{
foreach (var item in sale.SaleItems)
{
item.SaleID = resultInsert.GeneratedId;
cmd.Parameters.Clear();
cmd.CommandText = @"INSERT INTO SALEITEMS (SALEID,PRODUCTID,QUANTITY,UNITYPRICE) VALUES (@SALEID,@PRODUCTID,@QUANTITY,@UNITYPRICE); UPDATE PRODUCTS SET STOCK -= @QUANTITY WHERE ID = @PRODUCTID";
cmd.Parameters.AddWithValue("@SALEID", item.SaleID);
cmd.Parameters.AddWithValue("@PRODUCTID", item.ProductID);
cmd.Parameters.AddWithValue("@QUANTITY", item.Quantity);
cmd.Parameters.AddWithValue("@UNITYPRICE", item.UnityPrice);
Response responseInsertSale = new DbExecuter().ExecuteQuery(cmd);
if (!responseInsertSale.Success)
{
return(responseInsertSale);
}
}
}
scope.Complete();
return(Response.CreateSuccess("Operação efetuada com sucesso"));
}
}
public void Test2()
{
var tbd = TableDefinition.FromType <TypeTestModel>();
var table = GetSchema().CreateTable("Test1", tbd);
var model = new TypeTestModel();
model.String = "Testing...";
var statement = doob.PgSql.Statements.Insert.Into("TestTable")
.AddColumnsFromTableDefinition(tbd)
.AddValuesFromObject(model)
.AddClause(Returning.Columns(tbd.PrimaryKeys().Select(p => p.GetNameForDb()).ToArray()));
var sql = statement.GetSqlCommand(tbd);
table.Insert(model);
var executor = new DbExecuter(table.GetConnectionString());
var resp = executor.ExecuteReader <TypeTestModel>($"SELECT * FROM {table}");
}
//Método que insere na tabela de checkout no banco de dados e junto atualiza o checkin para inativo e atualiza o status do quarto para disponível
public Response Insert(CheckOut checkOut)
{
using (TransactionScope scope = new TransactionScope())
{
Response resultInsertIncome = base.Insert(checkOut);
if (!resultInsertIncome.Success)
return resultInsertIncome;
using (DbCommand command = DbFactory.GetCurrentCommand())
{
CheckIn checkIn = new CheckIn()
{
ID = checkOut.CheckInID
};
command.Parameters.Clear();
command.CommandText = @"UPDATE CHECKINS SET ACTIVE = @ACTIVE WHERE ID = @CHECKINID; UPDATE ROOMS SET ROOMSTATUS = @ROOMSTATUS WHERE ID = @ROOMID";
command.Parameters.AddWithValue("@CHECKINID", checkOut.CheckInID);
command.Parameters.AddWithValue("@ACTIVE", false);
command.Parameters.AddWithValue("@ROOMID", checkIn.RoomID);
command.Parameters.AddWithValue("@ROOMSTATUS", RoomStatus.Disponível);
Response responseInsert = new DbExecuter().ExecuteQuery(command);
if (!responseInsert.Success)
return responseInsert;
}
scope.Complete();
return Response.CreateSuccess("Operação efetuada com sucesso!");
}
}
public StateUpdater(DbExecuter dbExecuter, string stateId, string stateTableName,
JsonSerializerSettings jsonSettings)
{
_dbExecuter = dbExecuter;
_stateId = stateId;
_stateTableName = stateTableName;
_jsonSettings = jsonSettings;
}
public bool Exists(TipoVaga item)
{
DbCommand command = DbFactory.GetCommand();
command.CommandText = "SELECT ID FROM TIPOSVAGA WHERE NOME = @NOME";
command.Parameters.AddWithValue("@NOME", item.Nome);
DataTable table = new DbExecuter().GetData(command);
return(table.Rows.Count > 0);
}
public void Setup()
{
DbExecuter = new DbExecuter();
MockCODCreator = new Mock <ICODCreator>();
DbExecuter.CODCreator = MockCODCreator.Object;
MockConnectionStringMaker = new Mock <IConnectionStringMaker>();
DbExecuter.ConnectionStringMaker = MockConnectionStringMaker.Object;
}
public PostgreSqlTransactionalStateStorage2(StateReference stateReference,
PostgreSqlTransactionalStateOptions options,
JsonSerializerSettings jsonSettings,
ILogger <PostgreSqlTransactionalStateStorage2 <TState> > logger)
{
_stateId = stateReference.ToString();
_options = options;
_dbExecuter = new DbExecuter(_options.ConnectionString);
_jsonSettings = jsonSettings;
_logger = logger;
_jsonSettings.TypeNameHandling = TypeNameHandling.Auto;
_jsonSettings.DefaultValueHandling = DefaultValueHandling.Include;
_jsonSettings.Converters.Add(new Newtonsoft.Json.Converters.StringEnumConverter());
}
//public OperationResult<IEnumerable<Region>> GetRegions(int petitonLevelId)
//{
// OperationResult<IEnumerable<Region>> result;
// try
// {
// using (var db = new EDEntities())
// {
// var regions = this.FakeRegions.Where(r => r.Level != null && r.Level.ID == petitonLevelId);
// foreach (var fakeRegion in this.FakeRegions)
// {
// // load full level info
// }
// result = OperationResult<IEnumerable<Region>>.Success(regions);
// }
// }
// catch (Exception exc)
// {
// result = OperationResult<IEnumerable<Region>>.ExceptionResult(exc);
// }
// return result;
//}
public OperationResult <IEnumerable <Region> > GetRegions(int petitonLevelId)
{
Func <EDEntities, OperationResult <IEnumerable <Region> > > getRegions = (db) =>
{
var regions = this.FakeRegions.Where(r => r.Level != null && r.Level.ID == petitonLevelId);
foreach (var fakeRegion in this.FakeRegions)
{
// load full level info
}
var result = OperationResult <IEnumerable <Region> > .Success(regions);
return(result);
};
var operationResult = DbExecuter.Execute(getRegions);
return(operationResult);
}
//Autalia o preço dos produtos de acordo com a entrada
public Response UpdatePrice(Income income)
{
using (DbCommand command = DbFactory.GetCurrentCommand())
{
foreach (IncomeItem incomItemEntry in income.IncomeItems)
{
command.Parameters.Clear();
command.CommandText = @"UPDATE PRODUCTS SET PRICE = @PRICE WHERE ID = @ID";
command.Parameters.AddWithValue("@ID", incomItemEntry.ProductID);
command.Parameters.AddWithValue("@PRICE", incomItemEntry.UnityPrice);
Response responseInsert = new DbExecuter().ExecuteQuery(command);
if (!responseInsert.Success)
{
return(responseInsert);
}
}
return(Response.CreateSuccess("Operação efetuada com sucesso!"));
}
}
public TipoVaga GetById(int id)
{
DbCommand command = DbFactory.GetCommand();
command.CommandText = "SELECT * FROM TIPOSVAGA WHERE ID = @ID";
command.Parameters.AddWithValue("@ID", id);
DataTable table = new DbExecuter().GetData(command);
if (table.Rows.Count == 0)
{
return(null);
}
TipoVaga tipoVaga = new TipoVaga();
tipoVaga.ID = id;
tipoVaga.Nome = (string)table.Rows[0]["NOME"];
tipoVaga.Valor = Convert.ToDouble(table.Rows[0]["VALOR"]);
return(tipoVaga);
}
//atualiza
public Response Update(Income income)
{
using (TransactionScope scope = new TransactionScope())
{
Response resultUpdateIncome = base.Update(income);
if (!resultUpdateIncome.Success)
{
return(resultUpdateIncome);
}
Response resultDelete = DeleteWhereId <IncomeItem>("INCOMEID", income.ID);
if (!resultDelete.Success)
{
return(resultDelete);
}
using (DbCommand command = DbFactory.GetCurrentCommand())
{
foreach (IncomeItem incomeItemEntry in income.IncomeItems)
{
incomeItemEntry.IncomeID = income.ID;
command.Parameters.Clear();
command.CommandText = @"INSERT INTO INCOMEITEMS (INCOMEID,PRODUCTID,QUANTITY,UNITYPRICE) VALUES (@INCOMEID,@PRODUCTID,@QUANTITY,@UNITYPRICE); UPDATE PRODUCTS SET STOCK += @QUANTITY WHERE ID = @PRODUCTID";
command.Parameters.AddWithValue("@INCOMEID", incomeItemEntry.IncomeID);
command.Parameters.AddWithValue("@PRODUCTID", incomeItemEntry.ProductID);
command.Parameters.AddWithValue("@QUANTITY", incomeItemEntry.Quantity);
command.Parameters.AddWithValue("@UNITYPRICE", incomeItemEntry.UnityPrice);
Response responseInsert = new DbExecuter().ExecuteQuery(command);
if (!responseInsert.Success)
{
return(responseInsert);
}
}
}
scope.Complete();
return(Response.CreateSuccess("Operação efetuada com sucesso!"));
}
}
public Vaga GetById(int id)
{
DbCommand command = DbFactory.GetCommand();
command.CommandText = "SELECT * FROM VAGAS WHERE ID = @ID";
command.Parameters.AddWithValue("@ID", id);
DataTable table = new DbExecuter().GetData(command);
if (table.Rows.Count == 0)
{
return(null);
}
return(new Vaga()
{
ID = id,
Coberta = (bool)table.Rows[0]["COBERTA"],
Codigo = (string)table.Rows[0]["CODIGO"],
Especial = (bool)table.Rows[0]["ESPECIAL"],
Idoso = (bool)table.Rows[0]["IDOSO"],
Ocupada = (bool)table.Rows[0]["OCUPADA"],
TipoVagaID = (int)table.Rows[0]["TIPOVAGAID"]
});
}
public List <TipoVaga> GetAll()
{
DbCommand command = DbFactory.GetCommand();
command.CommandText = "SELECT * FROM TIPOSVAGA";
DataTable table = new DbExecuter().GetData(command);
if (table.Rows.Count == 0)
{
return(null);
}
List <TipoVaga> tiposVagas = new List <TipoVaga>();
for (int i = 0; i < table.Rows.Count; i++)
{
TipoVaga tipoVaga = new TipoVaga();
tipoVaga.ID = (int)table.Rows[i]["ID"];
tipoVaga.Nome = (string)table.Rows[i]["NOME"];
tipoVaga.Valor = Convert.ToDouble(table.Rows[i]["VALOR"]);
tiposVagas.Add(tipoVaga);
}
return(tiposVagas);
}
public List <Vaga> GetAll()
{
DbCommand command = DbFactory.GetCommand();
command.CommandText = "SELECT * FROM VAGAS";
DataTable table = new DbExecuter().GetData(command);
List <Vaga> vagas = new List <Vaga>();
for (int i = 0; i < table.Rows.Count; i++)
{
Vaga vaga = new Vaga()
{
ID = (int)table.Rows[i]["ID"],
Coberta = (bool)table.Rows[i]["COBERTA"],
Codigo = (string)table.Rows[i]["CODIGO"],
Especial = (bool)table.Rows[i]["ESPECIAL"],
Idoso = (bool)table.Rows[i]["IDOSO"],
Ocupada = (bool)table.Rows[i]["OCUPADA"],
TipoVagaID = (int)table.Rows[i]["TIPOVAGAID"]
};
vagas.Add(vaga);
}
return(vagas);
}
public DTO.Movimentacao LerMovimentacao(string placa)
{
DbCommand command = DbFactory.GetCommand();
command.CommandText =
@"SELECT * FROM MOVIMENTACOES
WHERE PLACA = @PLACA AND VALOR IS NULL";
command.Parameters.AddWithValue("@PLACA", placa);
DataTable table = new DbExecuter().GetData(command);
if (table.Rows.Count == 0)
{
return(null);
}
return(new Movimentacao()
{
ID = (int)table.Rows[0]["ID"],
Cor = (int)table.Rows[0]["COR"],
Entrada = (DateTime)table.Rows[0]["ENTRADA"],
Modelo = (string)table.Rows[0]["MODELO"],
Placa = (string)table.Rows[0]["PLACA"],
VagaID = (int)table.Rows[0]["VAGAID"],
});
}
protected DbExecuter Execute()
{
return(_dbExecuter ?? (_dbExecuter = new DbExecuter(_connectionString)));
}
public OperationResult <Model.BusinessEntities.PetitionEmailVote> CreateEmailVoteRequest(EmailVote vote)
{
OperationResult <Model.BusinessEntities.PetitionEmailVote> emailVoteRequestResult;
Func <EDEntities, OperationResult <Model.BusinessEntities.PetitionEmailVote> > procedure = (db) =>
{
OperationResult <Model.BusinessEntities.PetitionEmailVote> result;
var emailVote = db.PetitionEmailVotes.SingleOrDefault(v => v.PetitionID == vote.ID && v.Email == vote.Email);
if (emailVote != null)
{
var votedPetition = db.Petitions.SingleOrDefault(p => p.ID == vote.ID);
result = emailVote.IsConfirmed
? OperationResult <Model.BusinessEntities.PetitionEmailVote> .Fail(
int.Parse(PetitionVoteOperationResult.AlreadyVotedCode),
PetitionVoteOperationResult.AlreadyVotedMessage)
: OperationResult <Model.BusinessEntities.PetitionEmailVote> .Success(
int.Parse(PetitionVoteOperationResult.WaitingConfirmationCode),
string.Format(PetitionVoteOperationResult.WaitingConfirmationMessage, emailVote.Email),
new Model.BusinessEntities.PetitionEmailVote(emailVote, votedPetition));
return(result);
}
emailVote = new PetitionEmailVote
{
PetitionID = vote.ID,
Email = vote.Email,
CreatedDate = DateTime.Now,
IsConfirmed = false,
Hash = HashGenerator.Generate()
};
db.PetitionEmailVotes.Add(emailVote);
db.SaveChanges();
var petition = new Model.BusinessEntities.Petition(db.Petitions.SingleOrDefault(p => p.ID == emailVote.PetitionID));
var clientEmailVote =
new Model.BusinessEntities.PetitionEmailVote()
{
ID = emailVote.ID,
Petition = petition,
Hash = emailVote.Hash,
Email = emailVote.Email,
CreatedDate = emailVote.CreatedDate,
IsConfirmed = emailVote.IsConfirmed
};
result = OperationResult <Model.BusinessEntities.PetitionEmailVote> .Success(
int.Parse(PetitionVoteOperationResult.EmailVoteRequestCreatedCode),
string.Format(PetitionVoteOperationResult.EmailVoteRequestCreatedMessage, emailVote.Email),
clientEmailVote);
return(result);
};
emailVoteRequestResult = DbExecuter.Execute(procedure);
return(emailVoteRequestResult);
}
