609 lines
24 KiB
C#
609 lines
24 KiB
C#
using MySql.Data.MySqlClient;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.Linq;
|
||
using System.Windows.Forms;
|
||
using static System.Windows.Forms.VisualStyles.VisualStyleElement;
|
||
|
||
namespace NeshinaPolina07_11
|
||
{
|
||
public partial class _1Menu : Form
|
||
{
|
||
private const string MODE_PRODUCTS = "PRODUCTS";
|
||
private const string MODE_ORDERS = "ORDERS";
|
||
private string currentMode = MODE_PRODUCTS;
|
||
|
||
public _1Menu()
|
||
{
|
||
InitializeComponent();
|
||
InitializeSortComboBox();
|
||
LoadSuppliers();
|
||
this.Text = "Администратор";
|
||
}
|
||
|
||
private void InitializeSortComboBox()
|
||
{
|
||
cmbSort.Items.Clear();
|
||
cmbSort.Items.Add("Без сортировки");
|
||
cmbSort.Items.Add("По возрастанию (остаток)");
|
||
cmbSort.Items.Add("По убыванию (остаток)");
|
||
cmbSort.SelectedIndex = 0;
|
||
}
|
||
|
||
private void LoadSuppliers()
|
||
{
|
||
cmbSupplier.Items.Clear();
|
||
cmbSupplier.Items.Add(new SupplierItem { Name = "Все поставщики", Id = -1 });
|
||
try
|
||
{
|
||
var db = DB.GetInstance();
|
||
var connection = db.GetConnection();
|
||
string query = "SELECT supplier_id, supplier_name FROM suppliers ORDER BY supplier_name";
|
||
using (MySqlCommand cmd = new MySqlCommand(query, connection))
|
||
using (MySqlDataReader reader = cmd.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
int id = Convert.ToInt32(reader["supplier_id"]);
|
||
string name = reader["supplier_name"].ToString();
|
||
cmbSupplier.Items.Add(new SupplierItem { Id = id, Name = name });
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show("Ошибка загрузки поставщиков: " + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
}
|
||
cmbSupplier.DisplayMember = "Name";
|
||
cmbSupplier.ValueMember = "Id";
|
||
cmbSupplier.SelectedIndex = 0;
|
||
}
|
||
|
||
|
||
private class SupplierItem
|
||
{
|
||
public int Id { get; set; }
|
||
public string Name { get; set; }
|
||
public override string ToString() => Name;
|
||
}
|
||
|
||
private void _1Menu_Load(object sender, EventArgs e)
|
||
{
|
||
LoadData();
|
||
|
||
}
|
||
|
||
private void SwitchMode(string mode)
|
||
{
|
||
currentMode = mode;
|
||
|
||
if (mode == MODE_PRODUCTS)
|
||
{
|
||
lblSort.Visible = true;
|
||
cmbSort.Visible = true;
|
||
lblSupplier.Visible = true;
|
||
cmbSupplier.Visible = true;
|
||
LoadProductsData(txtSearch.Text, cmbSort.SelectedItem?.ToString() ?? "Без сортировки", GetSelectedSupplierId());
|
||
}
|
||
else if (mode == MODE_ORDERS)
|
||
{
|
||
lblSort.Visible = false;
|
||
cmbSort.Visible = false;
|
||
lblSupplier.Visible = false;
|
||
cmbSupplier.Visible = false;
|
||
LoadOrdersData(txtSearch.Text);
|
||
}
|
||
}
|
||
|
||
private void LoadProductsData(string searchQuery = "", string sortOption = "Без сортировки", int? supplierId = null)
|
||
{
|
||
try
|
||
{
|
||
var db = DB.GetInstance();
|
||
var connection = db.GetConnection();
|
||
|
||
string query = @"
|
||
SELECT
|
||
p.product_id,
|
||
p.product_article,
|
||
p.product_name,
|
||
p.unit,
|
||
p.price,
|
||
p.discount,
|
||
p.quantity_in_stock,
|
||
p.description,
|
||
p.photo,
|
||
c.category_name AS category,
|
||
m.manufacturer_name AS manufacturer,
|
||
s.supplier_name AS supplier
|
||
FROM products p
|
||
LEFT JOIN categories c ON p.category_id = c.category_id
|
||
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
|
||
LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id";
|
||
var conditions = new List<string>();
|
||
var parameters = new List<MySqlParameter>();
|
||
if (!string.IsNullOrWhiteSpace(searchQuery))
|
||
{
|
||
string searchPattern = "%" + searchQuery.Trim().ToLower() + "%";
|
||
conditions.Add(@"
|
||
LOWER(COALESCE(p.product_article, '')) LIKE @search OR
|
||
LOWER(COALESCE(p.product_name, '')) LIKE @search OR
|
||
LOWER(COALESCE(p.unit, '')) LIKE @search OR
|
||
LOWER(COALESCE(CAST(p.price AS CHAR), '')) LIKE @search OR
|
||
LOWER(COALESCE(CAST(p.discount AS CHAR), '')) LIKE @search OR
|
||
LOWER(COALESCE(CAST(p.quantity_in_stock AS CHAR), '')) LIKE @search OR
|
||
LOWER(COALESCE(p.description, '')) LIKE @search OR
|
||
LOWER(COALESCE(p.photo, '')) LIKE @search OR
|
||
LOWER(COALESCE(c.category_name, '')) LIKE @search OR
|
||
LOWER(COALESCE(m.manufacturer_name, '')) LIKE @search OR
|
||
LOWER(COALESCE(s.supplier_name, '')) LIKE @search
|
||
");
|
||
parameters.Add(new MySqlParameter("@search", searchPattern));
|
||
}
|
||
if (supplierId.HasValue && supplierId.Value != -1)
|
||
{
|
||
conditions.Add("p.supplier_id = @supplierId");
|
||
parameters.Add(new MySqlParameter("@supplierId", supplierId.Value));
|
||
}
|
||
if (conditions.Count > 0)
|
||
{
|
||
query += " WHERE " + string.Join(" AND ", conditions);
|
||
}
|
||
switch (sortOption)
|
||
{
|
||
case "По возрастанию (остаток)":
|
||
query += " ORDER BY p.quantity_in_stock ASC";
|
||
break;
|
||
case "По убыванию (остаток)":
|
||
query += " ORDER BY p.quantity_in_stock DESC";
|
||
break;
|
||
}
|
||
|
||
using (MySqlCommand cmd = new MySqlCommand(query, connection))
|
||
{
|
||
cmd.Parameters.AddRange(parameters.ToArray());
|
||
using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
|
||
{
|
||
DataTable dt = new DataTable();
|
||
adapter.Fill(dt);
|
||
dgvProducts.DataSource = dt;
|
||
}
|
||
}
|
||
|
||
dgvProducts.AutoResizeColumns();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show("Ошибка загрузки данных о товарах: " + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
}
|
||
}
|
||
|
||
|
||
private void LoadOrdersData(string searchQuery = "")
|
||
{
|
||
try
|
||
{
|
||
var db = DB.GetInstance();
|
||
var connection = db.GetConnection();
|
||
|
||
string query = @"
|
||
SELECT
|
||
o.order_id,
|
||
o.order_date,
|
||
o.delivery_date,
|
||
pp.address AS pickup_point, -- Вместо pickup_point_id
|
||
u.full_name AS user_name, -- Вместо user_id
|
||
o.receive_code,
|
||
os.status_name AS status -- Вместо status_id
|
||
FROM orders o
|
||
LEFT JOIN pickup_points pp ON o.pickup_point_id = pp.point_id
|
||
LEFT JOIN users u ON o.user_id = u.user_id
|
||
LEFT JOIN order_statuses os ON o.status_id = os.status_id";
|
||
|
||
var conditions = new List<string>();
|
||
var parameters = new List<MySqlParameter>();
|
||
|
||
if (!string.IsNullOrWhiteSpace(searchQuery))
|
||
{
|
||
string searchPattern = "%" + searchQuery.Trim().ToLower() + "%";
|
||
conditions.Add(@"
|
||
LOWER(COALESCE(CAST(o.order_id AS CHAR), '')) LIKE @search OR
|
||
LOWER(COALESCE(CAST(o.order_date AS CHAR), '')) LIKE @search OR
|
||
LOWER(COALESCE(CAST(o.delivery_date AS CHAR), '')) LIKE @search OR
|
||
LOWER(COALESCE(pp.address, '')) LIKE @search OR -- Поиск по названию пункта выдачи
|
||
LOWER(COALESCE(u.full_name, '')) LIKE @search OR -- Поиск по имени пользователя
|
||
LOWER(COALESCE(o.receive_code, '')) LIKE @search OR
|
||
LOWER(COALESCE(os.status_name, '')) LIKE @search -- Поиск по названию статуса
|
||
");
|
||
parameters.Add(new MySqlParameter("@search", searchPattern));
|
||
}
|
||
|
||
if (conditions.Count > 0)
|
||
{
|
||
query += " WHERE " + string.Join(" AND ", conditions);
|
||
}
|
||
|
||
|
||
using (MySqlCommand cmd = new MySqlCommand(query, connection))
|
||
{
|
||
cmd.Parameters.AddRange(parameters.ToArray());
|
||
using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
|
||
{
|
||
DataTable dt = new DataTable();
|
||
adapter.Fill(dt);
|
||
dgvProducts.DataSource = dt;
|
||
}
|
||
}
|
||
|
||
dgvProducts.AutoResizeColumns();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show("Ошибка загрузки данных о заказах: " + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
}
|
||
}
|
||
|
||
|
||
|
||
private void PerformSearch()
|
||
{
|
||
if (currentMode == MODE_PRODUCTS)
|
||
{
|
||
LoadProductsData(txtSearch.Text, cmbSort.SelectedItem?.ToString() ?? "Без сортировки", GetSelectedSupplierId());
|
||
}
|
||
else if (currentMode == MODE_ORDERS)
|
||
{
|
||
LoadOrdersData(txtSearch.Text);
|
||
}
|
||
}
|
||
|
||
|
||
private void LoadData(string searchQuery = "", string sortOption = "Без сортировки", int? supplierId = null)
|
||
{
|
||
try
|
||
{
|
||
var db = DB.GetInstance();
|
||
var connection = db.GetConnection();
|
||
|
||
string query = @"
|
||
SELECT
|
||
p.product_id,
|
||
p.product_article,
|
||
p.product_name,
|
||
p.unit,
|
||
p.price,
|
||
p.discount,
|
||
p.quantity_in_stock,
|
||
p.description,
|
||
p.photo,
|
||
c.category_name AS category,
|
||
m.manufacturer_name AS manufacturer,
|
||
s.supplier_name AS supplier
|
||
FROM products p
|
||
LEFT JOIN categories c ON p.category_id = c.category_id
|
||
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
|
||
LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id";
|
||
var conditions = new List<string>();
|
||
var parameters = new List<MySqlParameter>();
|
||
if (!string.IsNullOrWhiteSpace(searchQuery))
|
||
{
|
||
string searchPattern = "%" + searchQuery.Trim().ToLower() + "%";
|
||
conditions.Add(@"
|
||
LOWER(COALESCE(p.product_article, '')) LIKE @search OR
|
||
LOWER(COALESCE(p.product_name, '')) LIKE @search OR
|
||
LOWER(COALESCE(p.unit, '')) LIKE @search OR
|
||
LOWER(COALESCE(CAST(p.price AS CHAR), '')) LIKE @search OR
|
||
LOWER(COALESCE(CAST(p.discount AS CHAR), '')) LIKE @search OR
|
||
LOWER(COALESCE(CAST(p.quantity_in_stock AS CHAR), '')) LIKE @search OR
|
||
LOWER(COALESCE(p.description, '')) LIKE @search OR
|
||
LOWER(COALESCE(p.photo, '')) LIKE @search OR
|
||
LOWER(COALESCE(c.category_name, '')) LIKE @search OR
|
||
LOWER(COALESCE(m.manufacturer_name, '')) LIKE @search OR
|
||
LOWER(COALESCE(s.supplier_name, '')) LIKE @search
|
||
");
|
||
parameters.Add(new MySqlParameter("@search", searchPattern));
|
||
}
|
||
if (supplierId.HasValue && supplierId.Value != -1)
|
||
{
|
||
conditions.Add("p.supplier_id = @supplierId");
|
||
parameters.Add(new MySqlParameter("@supplierId", supplierId.Value));
|
||
}
|
||
if (conditions.Count > 0)
|
||
{
|
||
query += " WHERE " + string.Join(" AND ", conditions);
|
||
}
|
||
switch (sortOption)
|
||
{
|
||
case "По возрастанию (остаток)":
|
||
query += " ORDER BY p.quantity_in_stock ASC";
|
||
break;
|
||
case "По убыванию (остаток)":
|
||
query += " ORDER BY p.quantity_in_stock DESC";
|
||
break;
|
||
}
|
||
|
||
using (MySqlCommand cmd = new MySqlCommand(query, connection))
|
||
{
|
||
cmd.Parameters.AddRange(parameters.ToArray());
|
||
using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
|
||
{
|
||
DataTable dt = new DataTable();
|
||
adapter.Fill(dt);
|
||
dgvProducts.DataSource = dt;
|
||
}
|
||
}
|
||
|
||
dgvProducts.AutoResizeColumns();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show("Ошибка загрузки данных: " + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
}
|
||
}
|
||
|
||
private void button1_Click(object sender, EventArgs e)
|
||
{
|
||
MainForm form1 = new MainForm();
|
||
form1.Show();
|
||
this.Hide();
|
||
}
|
||
|
||
private void txtSearch_TextChanged(object sender, EventArgs e)
|
||
{
|
||
PerformSearch();
|
||
|
||
}
|
||
|
||
private void cmbSort_SelectedIndexChanged_1(object sender, EventArgs e)
|
||
{
|
||
if (currentMode == MODE_PRODUCTS)
|
||
{
|
||
LoadProductsData(txtSearch.Text, cmbSort.SelectedItem?.ToString() ?? "Без сортировки", GetSelectedSupplierId());
|
||
}
|
||
}
|
||
|
||
private void cmbSupplier_SelectedIndexChanged(object sender, EventArgs e)
|
||
{
|
||
if (currentMode == MODE_PRODUCTS)
|
||
{
|
||
int? supplierId = GetSelectedSupplierId();
|
||
string sort = cmbSort.SelectedItem?.ToString() ?? "Без сортировки";
|
||
LoadProductsData(txtSearch.Text, sort, supplierId);
|
||
}
|
||
}
|
||
|
||
private int? GetSelectedSupplierId()
|
||
{
|
||
if (cmbSupplier.SelectedItem is SupplierItem item)
|
||
{
|
||
return item.Id == -1 ? (int?)null : item.Id;
|
||
}
|
||
return null;
|
||
}
|
||
|
||
private void button2_Click(object sender, EventArgs e)
|
||
{
|
||
}
|
||
|
||
private void button3_Click(object sender, EventArgs e)
|
||
{
|
||
|
||
}
|
||
//ДОБАВЛЕНИЕ
|
||
private void button4_Click(object sender, EventArgs e)
|
||
{
|
||
|
||
|
||
if (currentMode == MODE_PRODUCTS)
|
||
{
|
||
var addForm = new AddProduct(this);
|
||
addForm.ShowDialog();
|
||
}
|
||
else if (currentMode == MODE_ORDERS)
|
||
{
|
||
var addOrderForm = new AddOrder();
|
||
if (addOrderForm.ShowDialog() == DialogResult.OK)
|
||
{
|
||
LoadOrdersData(txtSearch.Text);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
MessageBox.Show("Неизвестный режим.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Warning);
|
||
}
|
||
}
|
||
|
||
public void RefreshData()
|
||
{
|
||
if (currentMode == MODE_PRODUCTS)
|
||
{
|
||
PerformSearch();
|
||
}
|
||
else if (currentMode == MODE_ORDERS)
|
||
{
|
||
LoadOrdersData(txtSearch.Text);
|
||
}
|
||
}
|
||
//РЕДАКТИРОВАНИЕ
|
||
private void button5_Click(object sender, EventArgs e)
|
||
{
|
||
if (dgvProducts.SelectedRows.Count == 0)
|
||
{
|
||
MessageBox.Show("Пожалуйста, выберите запись для редактирования.", "Информация",
|
||
MessageBoxButtons.OK, MessageBoxIcon.Information);
|
||
return;
|
||
}
|
||
|
||
var selectedRow = dgvProducts.SelectedRows[0];
|
||
|
||
if (currentMode == MODE_PRODUCTS)
|
||
{
|
||
if (selectedRow.Cells["product_id"].Value == null ||
|
||
!int.TryParse(selectedRow.Cells["product_id"].Value.ToString(), out int productId))
|
||
{
|
||
MessageBox.Show("Невозможно определить ID товара.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
return;
|
||
}
|
||
|
||
using (var editForm = new EditProduct(productId))
|
||
{
|
||
if (editForm.ShowDialog() == DialogResult.OK)
|
||
{
|
||
LoadProductsData(txtSearch.Text, cmbSort.SelectedItem?.ToString() ?? "Без сортировки", GetSelectedSupplierId());
|
||
}
|
||
}
|
||
}
|
||
else if (currentMode == MODE_ORDERS)
|
||
{
|
||
if (selectedRow.Cells["order_id"].Value == null ||
|
||
!int.TryParse(selectedRow.Cells["order_id"].Value.ToString(), out int orderId))
|
||
{
|
||
MessageBox.Show("Невозможно определить ID заказа.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
return;
|
||
}
|
||
|
||
using (var editForm = new EditOrder(orderId))
|
||
{
|
||
if (editForm.ShowDialog() == DialogResult.OK)
|
||
{
|
||
LoadOrdersData(txtSearch.Text);
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
private void button6_Click(object sender, EventArgs e)
|
||
{
|
||
|
||
|
||
if (dgvProducts.SelectedRows.Count == 0)
|
||
{
|
||
MessageBox.Show("Пожалуйста, выберите запись для удаления.", "Информация",
|
||
MessageBoxButtons.OK, MessageBoxIcon.Information);
|
||
return;
|
||
}
|
||
|
||
var selectedRow = dgvProducts.SelectedRows[0];
|
||
|
||
if (currentMode == MODE_PRODUCTS)
|
||
{
|
||
if (selectedRow.Cells["product_id"].Value == null ||
|
||
!int.TryParse(selectedRow.Cells["product_id"].Value.ToString(), out int productId))
|
||
{
|
||
MessageBox.Show("Невозможно определить ID товара.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
return;
|
||
}
|
||
|
||
string productName = selectedRow.Cells["product_name"].Value?.ToString() ?? "неизвестный";
|
||
var result = MessageBox.Show(
|
||
$"Вы действительно хотите удалить товар \"{productName}\"?",
|
||
"Подтверждение удаления",
|
||
MessageBoxButtons.YesNo,
|
||
MessageBoxIcon.Warning);
|
||
|
||
if (result == DialogResult.Yes)
|
||
{
|
||
DeleteProduct(productId);
|
||
}
|
||
}
|
||
else if (currentMode == MODE_ORDERS)
|
||
{
|
||
if (selectedRow.Cells["order_id"].Value == null ||
|
||
!int.TryParse(selectedRow.Cells["order_id"].Value.ToString(), out int orderId))
|
||
{
|
||
MessageBox.Show("Невозможно определить ID заказа.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
return;
|
||
}
|
||
|
||
string orderInfo = $"заказ №{orderId}";
|
||
var result = MessageBox.Show(
|
||
$"Вы действительно хотите удалить {orderInfo}?",
|
||
"Подтверждение удаления",
|
||
MessageBoxButtons.YesNo,
|
||
MessageBoxIcon.Warning);
|
||
|
||
if (result == DialogResult.Yes)
|
||
{
|
||
DeleteOrder(orderId);
|
||
}
|
||
}
|
||
}
|
||
|
||
|
||
private void DeleteProduct(int productId)
|
||
{
|
||
try
|
||
{
|
||
var db = DB.GetInstance();
|
||
var connection = db.GetConnection();
|
||
using (var cmd = new MySqlCommand("DELETE FROM products WHERE product_id = @id", connection))
|
||
{
|
||
cmd.Parameters.AddWithValue("@id", productId);
|
||
int rows = cmd.ExecuteNonQuery();
|
||
if (rows > 0)
|
||
{
|
||
MessageBox.Show("Товар успешно удалён.", "Успех", MessageBoxButtons.OK, MessageBoxIcon.Information);
|
||
LoadProductsData(txtSearch.Text, cmbSort.SelectedItem?.ToString() ?? "Без сортировки", GetSelectedSupplierId());
|
||
}
|
||
else
|
||
{
|
||
MessageBox.Show("Товар не найден.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Warning);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show($"Ошибка при удалении товара: {ex.Message}", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
}
|
||
}
|
||
|
||
private void DeleteOrder(int orderId)
|
||
{
|
||
try
|
||
{
|
||
var db = DB.GetInstance();
|
||
var connection = db.GetConnection();
|
||
using (var cmd = new MySqlCommand("DELETE FROM orders WHERE order_id = @id", connection))
|
||
{
|
||
cmd.Parameters.AddWithValue("@id", orderId);
|
||
int rows = cmd.ExecuteNonQuery();
|
||
if (rows > 0)
|
||
{
|
||
MessageBox.Show("Заказ успешно удалён.", "Успех", MessageBoxButtons.OK, MessageBoxIcon.Information);
|
||
LoadOrdersData(txtSearch.Text);
|
||
}
|
||
else
|
||
{
|
||
MessageBox.Show("Заказ не найден.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Warning);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show($"Ошибка при удалении заказа: {ex.Message}", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
}
|
||
}
|
||
|
||
private void _1Menu_FormClosing(object sender, FormClosingEventArgs e)
|
||
{
|
||
e.Cancel = true;
|
||
this.Hide();
|
||
MainForm form1 = new MainForm();
|
||
form1.Show();
|
||
|
||
}
|
||
|
||
private void товарыToolStripMenuItem_Click(object sender, EventArgs e)
|
||
{
|
||
SwitchMode(MODE_PRODUCTS);
|
||
|
||
}
|
||
|
||
private void заказыToolStripMenuItem_Click(object sender, EventArgs e)
|
||
{
|
||
SwitchMode(MODE_ORDERS);
|
||
|
||
}
|
||
}
|
||
} |