public SupplyRequestDetail Add(RestaurantDatabaseSettings ctx, SupplyRequestDetail supplyRequestDetail)
{
SupplyRequestDetail result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
conn.Open();
string query = $"INSERT INTO {SupplyRequestDetail.TableName} (" +
$"{SupplyRequestDetail.ColumnNames.SupplyRequestId}, " +
$"{SupplyRequestDetail.ColumnNames.ProductId}, " +
$"{SupplyRequestDetail.ColumnNames.Count}, " +
$"{SupplyRequestDetail.ColumnNames.Active} " +
$") VALUES (" +
$"{supplyRequestDetail.SupplyRequestId}, " +
$"{supplyRequestDetail.ProductId}, " +
$"{supplyRequestDetail.Count}, " +
$"{1} " +
$")";
OracleCommand cmd = new OracleCommand(query, conn);
cmd.ExecuteNonQuery();
result = supplyRequestDetail;
}
return(result);
}
private IList <SupplyRequestDetail> Get(OracleConnection connection, SupplyRequest supplyRequest)
{
IList <SupplyRequestDetail> result = new List <SupplyRequestDetail>();
string query = $"SELECT " +
$"{SupplyRequestDetail.ColumnNames.SupplyRequestId}, " +
$"{SupplyRequestDetail.ColumnNames.ProductId}, " +
$"{SupplyRequestDetail.ColumnNames.Count}, " +
$"{SupplyRequestDetail.ColumnNames.Active} " +
$"FROM {SupplyRequestDetail.TableName} " +
$"WHERE {SupplyRequestDetail.ColumnNames.Active} = 1 " +
$"AND {SupplyRequestDetail.ColumnNames.SupplyRequestId} = {supplyRequest.Id}";
OracleCommand cmd = new OracleCommand(query, connection);
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SupplyRequestDetail supplyRequestDetail = new SupplyRequestDetail()
{
SupplyRequestId = Convert.ToInt32(reader[SupplyRequestDetail.ColumnNames.SupplyRequestId]),
ProductId = Convert.ToInt32(reader[SupplyRequestDetail.ColumnNames.ProductId]),
Count = Convert.ToDecimal(reader[SupplyRequestDetail.ColumnNames.Count]),
Active = Convert.ToBoolean(Convert.ToInt16(reader[SupplyRequestDetail.ColumnNames.Active].ToString()))
};
result.Add(supplyRequestDetail);
}
reader.Dispose();
return(result);
}
public SupplyRequestDetail Get(RestaurantDatabaseSettings ctx, int supplyRequestId, int productId)
{
SupplyRequestDetail result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"SELECT " +
$"{SupplyRequestDetail.ColumnNames.SupplyRequestId}, " +
$"{SupplyRequestDetail.ColumnNames.ProductId}, " +
$"{SupplyRequestDetail.ColumnNames.Count}, " +
$"{SupplyRequestDetail.ColumnNames.Active} " +
$"FROM {SupplyRequestDetail.TableName} " +
$"WHERE {SupplyRequestDetail.ColumnNames.SupplyRequestId} = {supplyRequestId} " +
$"AND {SupplyRequestDetail.ColumnNames.ProductId} = {productId}";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
result = new SupplyRequestDetail()
{
SupplyRequestId = Convert.ToInt32(reader[SupplyRequestDetail.ColumnNames.SupplyRequestId]),
ProductId = Convert.ToInt32(reader[SupplyRequestDetail.ColumnNames.ProductId]),
Count = Convert.ToDecimal(reader[SupplyRequestDetail.ColumnNames.Count]),
Active = Convert.ToBoolean(Convert.ToInt16(reader[SupplyRequestDetail.ColumnNames.Active].ToString()))
};
}
reader.Dispose();
}
return(result);
}
private SupplyRequestDetail Add(OracleConnection connection, OracleTransaction transaction, int supplyRequestId, SupplyRequestDetail supplyRequestDetail)
{
string query = $"INSERT INTO {SupplyRequestDetail.TableName} (" +
$"{SupplyRequestDetail.ColumnNames.SupplyRequestId}, " +
$"{SupplyRequestDetail.ColumnNames.ProductId}, " +
$"{SupplyRequestDetail.ColumnNames.Count}, " +
$"{SupplyRequestDetail.ColumnNames.Active} " +
$") VALUES (" +
$"{supplyRequestId}, " +
$"{supplyRequestDetail.ProductId}, " +
$"{supplyRequestDetail.Count}, " +
$"{1} " +
$")";
OracleCommand cmd = new OracleCommand(query, connection)
{
Transaction = transaction
};
cmd.ExecuteNonQuery();
supplyRequestDetail.SupplyRequestId = supplyRequestId;
SupplyRequestDetail result = supplyRequestDetail;
return(result);
}
private SupplyRequestDetail Edit(OracleConnection conn, OracleTransaction transaction, SupplyRequestDetail supplyRequestDetail)
{
string query = $"UPDATE {SupplyRequestDetail.TableName} " +
$"SET " +
$"{SupplyRequestDetail.ColumnNames.Count} = {supplyRequestDetail.Count} " +
$"WHERE {SupplyRequestDetail.ColumnNames.SupplyRequestId} = {supplyRequestDetail.SupplyRequestId} " +
$"AND {SupplyRequestDetail.ColumnNames.ProductId} = {supplyRequestDetail.ProductId}";
OracleCommand deleteCommand = new OracleCommand(query, conn);
deleteCommand.Transaction = transaction;
deleteCommand.ExecuteNonQuery();
SupplyRequestDetail result = supplyRequestDetail;
return(result);
}
private bool Delete(OracleConnection conn, OracleTransaction transaction, SupplyRequestDetail supplyRequestDetail)
{
string query = $"DELETE FROM {SupplyRequestDetail.TableName} " +
$"WHERE {SupplyRequestDetail.ColumnNames.SupplyRequestId} = {supplyRequestDetail.SupplyRequestId} " +
$"AND {SupplyRequestDetail.ColumnNames.ProductId} = {supplyRequestDetail.ProductId}";
OracleCommand deleteCommand = new OracleCommand(query, conn)
{
Transaction = transaction
};
deleteCommand.ExecuteNonQuery();
bool result = true;
return(result);
}
public bool Delete(RestaurantDatabaseSettings ctx, SupplyRequestDetail supplyRequestDetail)
{
bool result = false;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
conn.Open();
string query = $"DELETE FROM {SupplyRequestDetail.TableName} " +
$"WHERE {SupplyRequestDetail.ColumnNames.SupplyRequestId} = {supplyRequestDetail.SupplyRequestId} " +
$"AND {SupplyRequestDetail.ColumnNames.ProductId} = {supplyRequestDetail.ProductId}";
OracleCommand deleteCommand = new OracleCommand(query, conn);
deleteCommand.ExecuteNonQuery();
result = true;
}
return(result);
}
public SupplyRequest Add(RestaurantDatabaseSettings ctx, SupplyRequest supplyRequest)
{
SupplyRequest result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
conn.Open();
OracleTransaction transaction = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
supplyRequest.CreationDate = DateTime.Now;
supplyRequest.Code = $"{supplyRequest.ProviderId}{supplyRequest.CreationDate:yyMMddHHmmss}";
string query = $"INSERT INTO {SupplyRequest.TableName} (" +
$"{SupplyRequest.ColumnNames.Code}, " +
$"{SupplyRequest.ColumnNames.ProviderId}, " +
$"{SupplyRequest.ColumnNames.StateId}, " +
$"{SupplyRequest.ColumnNames.CreationDate}, " +
$"{SupplyRequest.ColumnNames.Active} " +
$") VALUES (" +
$"'{supplyRequest.Code}', " +
$"{supplyRequest.ProviderId}, " +
$"{supplyRequest.StateId}, " +
$"TO_DATE('{supplyRequest.CreationDate:ddMMyyyyHHmm}', 'DDMMYYYYHH24MI'), " +
$"{1} " +
$") RETURNING {SupplyRequest.ColumnNames.Id} INTO :{SupplyRequest.ColumnNames.Id}";
OracleCommand cmd = new OracleCommand(query, conn)
{
Transaction = transaction
};
cmd.Parameters.Add(new OracleParameter()
{
ParameterName = $":{SupplyRequest.ColumnNames.Id}",
OracleDbType = OracleDbType.Decimal,
Direction = System.Data.ParameterDirection.Output
});
try
{
cmd.ExecuteNonQuery();
supplyRequest.Id = Convert.ToInt32(cmd.Parameters[$":{SupplyRequest.ColumnNames.Id}"].Value.ToString());
if (supplyRequest.SupplyRequestDetails != null)
{
List <SupplyRequestDetail> recipeDetails = new List <SupplyRequestDetail>();
foreach (var supplyRequestDetail in supplyRequest.SupplyRequestDetails)
{
SupplyRequestDetail createdRecipeDetail = this.Add(conn, transaction, supplyRequest.Id, supplyRequestDetail);
recipeDetails.Add(createdRecipeDetail);
}
}
transaction.Commit();
result = supplyRequest;
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
return(result);
}
