public void fill_table()
{
clear_table();
try
{
connection.Open();
string table = "";
switch (current_table)
{
case Tables.requests: table = "requests"; break;
case Tables.clients: table = "clients"; break;
case Tables.cars: table = "cars"; break;
}
MySqlCommand command = new MySqlCommand($"SELECT * FROM `{table}`;", connection);
MySqlDataReader data = command.ExecuteReader();
//Создание и именование столбцов
for (int i = 0; i < data.FieldCount; i++)
{
DataGridTextColumn column = new DataGridTextColumn();
column.Binding = new Binding(data.GetName(i));
column.Header = fields[data.GetName(i)];
DataGrid.Columns.Add(column);
}
//Заполнение строк данными из базы
while (data.Read())
{
string[] values = new string[data.FieldCount];
for (int i = 0; i < data.FieldCount; i++)
{
values[i] = data[i].ToString();
}
DataGrid.Items.Add(Container_controller.Create_struct(current_table, values));
}
if (data.GetName(0) == "id")
{
DataGrid.Columns[0].Visibility = Visibility.Collapsed;
}
if (current_table == Tables.requests)
{
DataGrid.Columns.Remove(DataGrid.Columns[data.GetOrdinal("parts_to_paint")]);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
}
private void button_delivery_supply_Click(object sender, RoutedEventArgs e)
{
if (DataGrid.SelectedItem == null)
{
MessageBox.Show("Выберите мышью запись перед совершением поставки.");
}
else
{
bool success = true;
Supply sup = (Supply)DataGrid.SelectedItem;
if (sup.delivery_date != "")
{
MessageBox.Show("Данная поставка уже зачислена!", "Внимание", MessageBoxButton.OK, MessageBoxImage.Exclamation);
return;
}
int product_storage_count = int.Parse(Shortcuts.get_one_string_data_from($"SELECT count(*) FROM " +
$"`storage` WHERE `product_name` = '{sup.product_name}' AND " +
$"`supplier` = '{sup.supplier}';", connection));
if (product_storage_count == 1)
{
//Записать в существующего поставщика
Storage st = new Storage();
try
{
connection.Open();
MySqlCommand comm = new MySqlCommand("SELECT * FROM " +
$"`storage` WHERE `product_name` = '{sup.product_name}' AND " +
$"`supplier` = '{sup.supplier}';", connection);
MySqlDataReader data = comm.ExecuteReader();
data.Read();
string[] values = new string[data.FieldCount];
for (int i = 0; i < data.FieldCount; i++)
{
values[i] = data[i].ToString();
}
st = (Storage)Container_controller.Create_struct(Tables.storage, values);
}
catch (Exception ex)
{
success = false;
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
decimal new_prod_amount = st.product_amount + sup.product_amount;
decimal new_price = ((st.average_purchase_price * st.product_amount) + sup.price * sup.product_amount) / (new_prod_amount);
success = Shortcuts.execute_command("UPDATE `storage` SET " +
$"`average_purchase_price` = {new_price.ToString().Replace(',', '.')}, " +
$"`product_amount` = {new_prod_amount.ToString().Replace(',', '.')} " +
$"WHERE `product_name` = '{sup.product_name.ToString().Replace(',', '.')}' AND " +
$"`supplier` = '{sup.supplier}';", connection);
success = Shortcuts.execute_command("UPDATE `supplies` SET " +
$"`delivery_date` = '{DateTime.Now:yyyy-MM-dd}' " +
$"WHERE `id` = {sup.id};", connection);
if (success)
{
MessageBox.Show("Поставка на склад произведена!", "Успех", MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
MessageBox.Show("Поставка не произведена!", "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
else if (product_storage_count == 0)
{
success = Shortcuts.execute_command("INSERT INTO `storage` " +
"(`product_name`, `product_amount`, " +
"`measurement`, `supplier`, `average_purchase_price`) VALUES " +
$"('{sup.product_name}', {sup.product_amount.ToString().Replace(',','.')}, " +
$"'{sup.measurement}', '{sup.supplier}', {(sup.price).ToString().Replace(',', '.')});", connection);
success = Shortcuts.execute_command("UPDATE `supplies` SET " +
$"`delivery_date` = '{DateTime.Now:yyyy-MM-dd}' " +
$"WHERE `id` = {sup.id};", connection);
if (success)
{
MessageBox.Show("Поставка на склад произведена!", "Успех", MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
MessageBox.Show("Поставка не произведена!", "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
else
{
MessageBox.Show("Произошла ошибка в базе!\nПовторяющиеся записи продукта на одного поставщика. " +
"Обратитесь к администратору", "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
fill_table();
}
}