592 lines
23 KiB
C#
592 lines
23 KiB
C#
using System;
|
||
using System.Data;
|
||
using System.Windows.Forms;
|
||
using System.Data.SQLite;
|
||
using System.IO;
|
||
using OfficeOpenXml;
|
||
|
||
namespace ПХД
|
||
{
|
||
public partial class Form1 : Form
|
||
{
|
||
private DatabaseManager dbManager;
|
||
|
||
public Form1()
|
||
{
|
||
InitializeComponent();
|
||
dbManager = new DatabaseManager();
|
||
SetupDataGridView();
|
||
}
|
||
|
||
private void Form1_Load(object sender, EventArgs e)
|
||
{
|
||
dbManager = new DatabaseManager();
|
||
dbManager.InitializeDatabase();
|
||
LoadEngines();
|
||
txtReason.Enabled = false;
|
||
datePickerOutOfOrder.Enabled = false;
|
||
}
|
||
|
||
private void LoadEngines()
|
||
{
|
||
try
|
||
{
|
||
dataGridView1.DataSource = dbManager.GetAllEngines();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show($"Ошибка загрузки данных: {ex.Message}");
|
||
}
|
||
}
|
||
|
||
private void btnSave_Click(object sender, EventArgs e)
|
||
{
|
||
try
|
||
{
|
||
string registrationDate = datePickerRegistration.Value.ToString("yyyy-MM-dd");
|
||
string engineType = cmbEngineType.Text;
|
||
double power = double.Parse(txtPower.Text);
|
||
int rpm = int.Parse(cmbRPM.Text);
|
||
string mountingType = cmbMountingType.Text;
|
||
string uniqueNumber = txtUniqueNumber.Text;
|
||
string factoryNumber = txtFactoryNumber.Text;
|
||
string partNumber = txtPartNumber.Text;
|
||
string installationSite = txtInstallationSite.Text;
|
||
bool isOutOfOrder = chkOutOfOrder.Checked;
|
||
string reason = isOutOfOrder ? txtReason.Text : null;
|
||
string outOfOrderDate = isOutOfOrder ? datePickerOutOfOrder.Value.ToString("yyyy-MM-dd") : null;
|
||
|
||
dbManager.AddEngine(
|
||
registrationDate, engineType, power, rpm, mountingType,
|
||
uniqueNumber, factoryNumber, partNumber, installationSite,
|
||
isOutOfOrder, reason, outOfOrderDate
|
||
);
|
||
|
||
LoadEngines();
|
||
MessageBox.Show("Запись успешно сохранена!");
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show($"Произошла ошибка: {ex.Message}");
|
||
}
|
||
}
|
||
|
||
private void SetupDataGridView()
|
||
{
|
||
dataGridView1.AutoGenerateColumns = false;
|
||
dataGridView1.Columns.Clear();
|
||
|
||
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
|
||
{
|
||
Name = "Id",
|
||
HeaderText = "ID",
|
||
DataPropertyName = "Id",
|
||
Width = 50
|
||
});
|
||
|
||
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
|
||
{
|
||
HeaderText = "Дата постановки",
|
||
DataPropertyName = "RegistrationDate",
|
||
Width = 120
|
||
});
|
||
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
|
||
{
|
||
HeaderText = "Тип двигателя",
|
||
DataPropertyName = "EngineType",
|
||
Width = 100
|
||
});
|
||
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
|
||
{
|
||
HeaderText = "Мощность (кВт)",
|
||
DataPropertyName = "Power",
|
||
Width = 100
|
||
});
|
||
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
|
||
{
|
||
HeaderText = "Обороты",
|
||
DataPropertyName = "RPM",
|
||
Width = 100
|
||
});
|
||
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
|
||
{
|
||
HeaderText = "Монтажное исполнение",
|
||
DataPropertyName = "MountingType",
|
||
Width = 150
|
||
});
|
||
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
|
||
{
|
||
HeaderText = "Уникальный номер",
|
||
DataPropertyName = "UniqueNumber",
|
||
Width = 150
|
||
});
|
||
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
|
||
{
|
||
HeaderText = "Заводской номер",
|
||
DataPropertyName = "FactoryNumber",
|
||
Width = 150
|
||
});
|
||
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
|
||
{
|
||
HeaderText = "Номер номенклатуры",
|
||
DataPropertyName = "PartNumber",
|
||
Width = 150
|
||
});
|
||
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
|
||
{
|
||
HeaderText = "Место установки",
|
||
DataPropertyName = "InstallationSite",
|
||
Width = 150
|
||
});
|
||
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
|
||
{
|
||
Name = "IsOutOfOrder", // Имя столбца в DataGridView
|
||
HeaderText = "Вышел из строя",
|
||
DataPropertyName = "IsOutOfOrder", // Имя столбца в DataTable/базе данных
|
||
Width = 120
|
||
});
|
||
|
||
|
||
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
|
||
{
|
||
HeaderText = "Причина",
|
||
DataPropertyName = "Reason",
|
||
Width = 150
|
||
});
|
||
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
|
||
{
|
||
HeaderText = "Дата выхода из строя",
|
||
DataPropertyName = "OutOfOrderDate",
|
||
Width = 150
|
||
});
|
||
}
|
||
|
||
private void btnDelete_Click(object sender, EventArgs e)
|
||
{
|
||
try
|
||
{
|
||
if (dataGridView1.SelectedRows.Count > 0)
|
||
{
|
||
// Получаем ID выбранной строки
|
||
var idCell = dataGridView1.SelectedRows[0].Cells["Id"];
|
||
if (idCell != null && idCell.Value != null)
|
||
{
|
||
int id = Convert.ToInt32(idCell.Value);
|
||
|
||
// Подтверждение удаления
|
||
var result = MessageBox.Show("Вы уверены, что хотите удалить эту запись?",
|
||
"Подтверждение удаления",
|
||
MessageBoxButtons.YesNo,
|
||
MessageBoxIcon.Question);
|
||
if (result == DialogResult.Yes)
|
||
{
|
||
dbManager.DeleteEngine(id);
|
||
LoadEngines(); // Обновляем таблицу
|
||
MessageBox.Show("Запись успешно удалена!");
|
||
}
|
||
}
|
||
else
|
||
{
|
||
MessageBox.Show("Не удалось определить ID записи.");
|
||
}
|
||
}
|
||
else
|
||
{
|
||
MessageBox.Show("Пожалуйста, выберите запись для удаления.");
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show($"Произошла ошибка при удалении записи: {ex.Message}");
|
||
}
|
||
}
|
||
|
||
private void chkOutOfOrder_CheckedChanged(object sender, EventArgs e)
|
||
{
|
||
// Поля блокируются, если чекбокс не установлен
|
||
bool isEnabled = chkOutOfOrder.Checked;
|
||
txtReason.Enabled = isEnabled;
|
||
datePickerOutOfOrder.Enabled = isEnabled;
|
||
}
|
||
private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
|
||
{
|
||
if (dataGridView1.Columns[e.ColumnIndex].Name == "IsOutOfOrder")
|
||
{
|
||
var row = dataGridView1.Rows[e.RowIndex];
|
||
|
||
if (row.Cells["IsOutOfOrder"] != null && row.Cells["IsOutOfOrder"].Value != null)
|
||
{
|
||
var value = row.Cells["IsOutOfOrder"].Value;
|
||
|
||
if (value != DBNull.Value)
|
||
{
|
||
bool isOutOfOrder = Convert.ToBoolean(value);
|
||
|
||
// Подсвечиваем фон ячейки
|
||
e.CellStyle.BackColor = isOutOfOrder
|
||
? System.Drawing.Color.LightCoral // Красный фон
|
||
: System.Drawing.Color.White; // Белый фон
|
||
}
|
||
else
|
||
{
|
||
e.CellStyle.BackColor = System.Drawing.Color.White;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
private void btnClear_Click(object sender, EventArgs e)
|
||
{
|
||
// Очистка текстовых полей
|
||
txtPower.Text = string.Empty;
|
||
txtUniqueNumber.Text = string.Empty;
|
||
txtFactoryNumber.Text = string.Empty;
|
||
txtPartNumber.Text = string.Empty;
|
||
txtInstallationSite.Text = string.Empty;
|
||
txtReason.Text = string.Empty;
|
||
|
||
// Сброс выпадающих списков
|
||
cmbEngineType.SelectedIndex = -1; // Сбрасывает выбор
|
||
cmbRPM.SelectedIndex = -1;
|
||
cmbMountingType.SelectedIndex = -1;
|
||
|
||
// Сброс чекбоксов
|
||
chkOutOfOrder.Checked = false;
|
||
|
||
// Сброс дат
|
||
datePickerRegistration.Value = DateTime.Now;
|
||
datePickerOutOfOrder.Value = DateTime.Now;
|
||
|
||
// Блокировка полей "Неисправность"
|
||
txtReason.Enabled = false;
|
||
datePickerOutOfOrder.Enabled = false;
|
||
LoadEngines();
|
||
|
||
}
|
||
|
||
private void btnSearch_Click(object sender, EventArgs e)
|
||
{
|
||
try
|
||
{
|
||
// Формируем SQL-запрос с фильтрацией
|
||
string query = "SELECT * FROM Engines WHERE 1=1"; // Условие 1=1 позволяет добавлять фильтры динамически
|
||
|
||
if (!string.IsNullOrWhiteSpace(txtPower.Text))
|
||
{
|
||
query += $" AND Power = {txtPower.Text}";
|
||
}
|
||
|
||
if (!string.IsNullOrWhiteSpace(cmbEngineType.Text))
|
||
{
|
||
query += $" AND EngineType LIKE '%{cmbEngineType.Text}%'";
|
||
}
|
||
|
||
if (!string.IsNullOrWhiteSpace(txtUniqueNumber.Text))
|
||
{
|
||
query += $" AND UniqueNumber LIKE '%{txtUniqueNumber.Text}%'";
|
||
}
|
||
|
||
if (!string.IsNullOrWhiteSpace(txtFactoryNumber.Text))
|
||
{
|
||
query += $" AND FactoryNumber LIKE '%{txtFactoryNumber.Text}%'";
|
||
}
|
||
|
||
if (!string.IsNullOrWhiteSpace(txtPartNumber.Text))
|
||
{
|
||
query += $" AND PartNumber LIKE '%{txtPartNumber.Text}%'";
|
||
}
|
||
|
||
if (!string.IsNullOrWhiteSpace(txtInstallationSite.Text))
|
||
{
|
||
query += $" AND InstallationSite LIKE '%{txtInstallationSite.Text}%'";
|
||
}
|
||
|
||
if (chkOutOfOrder.Checked)
|
||
{
|
||
query += " AND IsOutOfOrder = 1";
|
||
}
|
||
|
||
// Выполняем запрос
|
||
DataTable searchResults = dbManager.ExecuteQuery(query);
|
||
dataGridView1.DataSource = searchResults; // Отображаем результаты поиска
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show($"Произошла ошибка при поиске: {ex.Message}");
|
||
}
|
||
}
|
||
|
||
private void экспортToolStripMenuItem_Click(object sender, EventArgs e)
|
||
{
|
||
try
|
||
{
|
||
using (var saveFileDialog = new SaveFileDialog())
|
||
{
|
||
saveFileDialog.Filter = "Excel Files|*.xlsx";
|
||
saveFileDialog.Title = "Сохранить таблицу как Excel файл";
|
||
saveFileDialog.FileName = "Engines.xlsx";
|
||
|
||
if (saveFileDialog.ShowDialog() == DialogResult.OK)
|
||
{
|
||
// Получаем данные из DataGridView
|
||
DataTable table = (DataTable)dataGridView1.DataSource;
|
||
|
||
// Экспортируем в Excel
|
||
ExportToExcel(table, saveFileDialog.FileName);
|
||
|
||
MessageBox.Show("Данные успешно экспортированы!", "Успех", MessageBoxButtons.OK, MessageBoxIcon.Information);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show($"Произошла ошибка при экспорте: {ex.Message}", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
}
|
||
}
|
||
|
||
private void ExportToExcel(DataTable table, string filePath)
|
||
{
|
||
// Указываем контекст лицензии
|
||
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
|
||
|
||
using (var package = new OfficeOpenXml.ExcelPackage())
|
||
{
|
||
// Создаем новый лист
|
||
var worksheet = package.Workbook.Worksheets.Add("Engines");
|
||
|
||
// Заголовки столбцов из DataGridView
|
||
for (int col = 0; col < dataGridView1.Columns.Count; col++)
|
||
{
|
||
worksheet.Cells[1, col + 1].Value = dataGridView1.Columns[col].HeaderText; // Используем HeaderText
|
||
}
|
||
|
||
// Данные из DataTable
|
||
for (int row = 0; row < table.Rows.Count; row++)
|
||
{
|
||
for (int col = 0; col < table.Columns.Count; col++)
|
||
{
|
||
worksheet.Cells[row + 2, col + 1].Value = table.Rows[row][col];
|
||
}
|
||
}
|
||
|
||
// Авторазмер столбцов
|
||
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
|
||
|
||
// Сохраняем файл
|
||
package.SaveAs(new FileInfo(filePath));
|
||
}
|
||
}
|
||
|
||
private void импортToolStripMenuItem_Click(object sender, EventArgs e)
|
||
{
|
||
try
|
||
{
|
||
using (var openFileDialog = new OpenFileDialog())
|
||
{
|
||
openFileDialog.Filter = "Excel Files|*.xlsx";
|
||
openFileDialog.Title = "Выберите Excel файл для импорта";
|
||
|
||
if (openFileDialog.ShowDialog() == DialogResult.OK)
|
||
{
|
||
// Импортируем данные из Excel
|
||
DataTable importedTable = ImportFromExcel(openFileDialog.FileName);
|
||
|
||
// Проверяем, содержит ли таблица строки
|
||
if (importedTable.Rows.Count == 0)
|
||
{
|
||
MessageBox.Show("Файл Excel не содержит данных для импорта.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
|
||
// Загружаем данные в DataGridView
|
||
dataGridView1.DataSource = importedTable;
|
||
|
||
MessageBox.Show("Данные успешно импортированы!", "Успех", MessageBoxButtons.OK, MessageBoxIcon.Information);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show($"Произошла ошибка при импорте: {ex.Message}", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
}
|
||
}
|
||
|
||
|
||
|
||
private DataTable ImportFromExcel(string filePath)
|
||
{
|
||
DataTable table = new DataTable();
|
||
|
||
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
|
||
|
||
using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(filePath)))
|
||
{
|
||
// Получаем первый лист из файла
|
||
var worksheet = package.Workbook.Worksheets[0];
|
||
if (worksheet == null)
|
||
{
|
||
throw new Exception("В файле отсутствуют листы.");
|
||
}
|
||
|
||
// Создаем колонки на основе заголовков DataGridView
|
||
foreach (DataGridViewColumn column in dataGridView1.Columns)
|
||
{
|
||
table.Columns.Add(column.DataPropertyName);
|
||
}
|
||
|
||
// Читаем данные начиная со второй строки
|
||
for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
|
||
{
|
||
DataRow newRow = table.NewRow();
|
||
for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
|
||
{
|
||
// Сопоставляем заголовки Excel с DataPropertyName
|
||
string columnName = worksheet.Cells[1, col].Text.Trim();
|
||
if (table.Columns.Contains(columnName))
|
||
{
|
||
newRow[columnName] = worksheet.Cells[row, col].Text.Trim();
|
||
}
|
||
}
|
||
table.Rows.Add(newRow);
|
||
}
|
||
}
|
||
|
||
return table;
|
||
}
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
private class DatabaseManager
|
||
{
|
||
private string connectionString = "Data Source=Engines.db;Version=3;";
|
||
|
||
public void InitializeDatabase()
|
||
{
|
||
using (var connection = new SQLiteConnection(connectionString))
|
||
{
|
||
connection.Open();
|
||
string createTableQuery = @"
|
||
CREATE TABLE IF NOT EXISTS Engines (
|
||
Id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
RegistrationDate TEXT NOT NULL,
|
||
EngineType TEXT NOT NULL,
|
||
Power REAL NOT NULL,
|
||
RPM INTEGER NOT NULL,
|
||
MountingType TEXT NOT NULL,
|
||
UniqueNumber TEXT NOT NULL,
|
||
FactoryNumber TEXT NOT NULL,
|
||
PartNumber TEXT NOT NULL,
|
||
InstallationSite TEXT NOT NULL,
|
||
IsOutOfOrder INTEGER NOT NULL,
|
||
Reason TEXT,
|
||
OutOfOrderDate TEXT
|
||
)";
|
||
SQLiteCommand command = new SQLiteCommand(createTableQuery, connection);
|
||
command.ExecuteNonQuery();
|
||
}
|
||
}
|
||
|
||
public void AddEngine(
|
||
string registrationDate, string engineType, double power, int rpm,
|
||
string mountingType, string uniqueNumber, string factoryNumber,
|
||
string partNumber, string installationSite, bool isOutOfOrder,
|
||
string reason, string outOfOrderDate)
|
||
{
|
||
using (var connection = new SQLiteConnection(connectionString))
|
||
{
|
||
connection.Open();
|
||
string insertQuery = @"
|
||
INSERT INTO Engines
|
||
(RegistrationDate, EngineType, Power, RPM, MountingType,
|
||
UniqueNumber, FactoryNumber, PartNumber, InstallationSite,
|
||
IsOutOfOrder, Reason, OutOfOrderDate)
|
||
VALUES
|
||
(@RegistrationDate, @EngineType, @Power, @RPM, @MountingType,
|
||
@UniqueNumber, @FactoryNumber, @PartNumber, @InstallationSite,
|
||
@IsOutOfOrder, @Reason, @OutOfOrderDate)";
|
||
|
||
SQLiteCommand command = new SQLiteCommand(insertQuery, connection);
|
||
command.Parameters.AddWithValue("@RegistrationDate", registrationDate);
|
||
command.Parameters.AddWithValue("@EngineType", engineType);
|
||
command.Parameters.AddWithValue("@Power", power);
|
||
command.Parameters.AddWithValue("@RPM", rpm);
|
||
command.Parameters.AddWithValue("@MountingType", mountingType);
|
||
command.Parameters.AddWithValue("@UniqueNumber", uniqueNumber);
|
||
command.Parameters.AddWithValue("@FactoryNumber", factoryNumber);
|
||
command.Parameters.AddWithValue("@PartNumber", partNumber);
|
||
command.Parameters.AddWithValue("@InstallationSite", installationSite);
|
||
command.Parameters.AddWithValue("@IsOutOfOrder", isOutOfOrder ? 1 : 0);
|
||
command.Parameters.AddWithValue("@Reason", reason ?? (object)DBNull.Value);
|
||
command.Parameters.AddWithValue("@OutOfOrderDate", outOfOrderDate ?? (object)DBNull.Value);
|
||
|
||
command.ExecuteNonQuery();
|
||
}
|
||
}
|
||
|
||
public DataTable GetAllEngines()
|
||
{
|
||
using (var connection = new SQLiteConnection(connectionString))
|
||
{
|
||
connection.Open();
|
||
|
||
string selectQuery = @"
|
||
SELECT
|
||
Id, RegistrationDate, EngineType, Power, RPM, MountingType,
|
||
UniqueNumber, FactoryNumber, PartNumber, InstallationSite,
|
||
IsOutOfOrder, Reason, OutOfOrderDate
|
||
FROM Engines";
|
||
|
||
|
||
SQLiteDataAdapter adapter = new SQLiteDataAdapter(selectQuery, connection);
|
||
DataTable table = new DataTable();
|
||
adapter.Fill(table);
|
||
return table;
|
||
}
|
||
}
|
||
|
||
public void DeleteEngine(int id)
|
||
{
|
||
using (var connection = new SQLiteConnection(connectionString))
|
||
{
|
||
connection.Open();
|
||
string deleteQuery = "DELETE FROM Engines WHERE Id = @Id";
|
||
SQLiteCommand command = new SQLiteCommand(deleteQuery, connection);
|
||
command.Parameters.AddWithValue("@Id", id);
|
||
command.ExecuteNonQuery();
|
||
}
|
||
}
|
||
|
||
public DataTable ExecuteQuery(string query)
|
||
{
|
||
using (var connection = new SQLiteConnection(connectionString))
|
||
{
|
||
connection.Open();
|
||
SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, connection);
|
||
DataTable table = new DataTable();
|
||
adapter.Fill(table);
|
||
return table;
|
||
}
|
||
}
|
||
|
||
|
||
|
||
}
|
||
}
|
||
}
|