public static MenueDetail Get(int id)
{
MenueDetail result = null;
try
{
DBConnection.Connection.Open();
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = DBConnection.Connection;
command.CommandText = $"select {COLUMNS} from {TABLE} where menue_detail_id = :menue_detail_id";
command.Parameters.AddWithValue("menue_detail_id", id);
NpgsqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
result = new MenueDetail()
{
MenueDetailId = reader.GetInt32(0),
MenueId = (reader.IsDBNull(1) ? (long?)null : reader.GetInt32(1)),
ProductId = (reader.IsDBNull(2) ? (long?)null : reader.GetInt32(2)),
};
}
reader.Close();
}
catch (Exception ex)
{
}
finally
{
DBConnection.Connection.Close();
}
return(result);
}
//********************************************************
public int Save()
{
DBConnection.Connection.Open();
try
{
string insert = $"insert into {TABLE} ({COLUMNS}) values (@{COLUMNS.Replace(", ", ", @")})";
string update = $"update {TABLE} set serve_date = @serve_date, type= @type, titel = @titel where menue_id = @menue_id";
NpgsqlCommand cmd = null;
if (!this.MenueId.HasValue) // Insert
{
cmd = new NpgsqlCommand($"select nextval('{TABLE}_seq')", DBConnection.Connection);
this.MenueId = (long)cmd.ExecuteScalar();
//this.PersonUid = Guid.NewGuid().ToString("N");
cmd = new NpgsqlCommand(insert, DBConnection.Connection);
}
else // Update
{
//Set orders with this menueid to canceled when a menue is changed
List <long> usersForNotification = null;
usersForNotification = Order.GetUserForNotification((long)this.MenueId);
foreach (long user in usersForNotification)
{
Notification notification = new Notification();
DateTime date;
DateTime.TryParse(this.ServeDate.ToString(), out date);
notification.UserId = user;
notification.Text = $"Ihre Bestellung {this.TitelOld} für {date.ToShortDateString()} wurde storniert, weil das Menü von der Küche geändert wurde";
notification.Save();
}
if (usersForNotification.Count > 0)
{
Order.SetOrderCanceled(this.MenueId);
}
MenueDetail.DeleteForMenueUpdate((long)this.MenueId);
cmd = new NpgsqlCommand(update, DBConnection.Connection);
}
cmd.Parameters.AddWithValue("@menue_id", this.MenueId);
cmd.Parameters.AddWithValue("@serve_date", this.ServeDate ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@type", this.Type ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@titel", this.Titel ?? (object)DBNull.Value);
cmd.Prepare();
int result = cmd.ExecuteNonQuery();
return(result);
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBConnection.Connection.Close();
}
}