334 lines
13 KiB
C#
334 lines
13 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data.SQLite;
|
||
using System.IO;
|
||
using System.Linq;
|
||
using System.Text;
|
||
|
||
namespace Учет_вещей
|
||
{
|
||
public static class DbManager
|
||
{
|
||
private const string DbFileName = "inventory.db";
|
||
private static readonly string ConnectionString = $"Data Source={DbFileName};Version=3;";
|
||
|
||
// Инициализация базы данных
|
||
public static void InitializeDatabase()
|
||
{
|
||
// Проверяем существование файла базы данных
|
||
bool isNewDatabase = !File.Exists(DbFileName);
|
||
|
||
if (isNewDatabase)
|
||
{
|
||
// Создаем базу данных и необходимые таблицы
|
||
SQLiteConnection.CreateFile(DbFileName);
|
||
|
||
using (var connection = new SQLiteConnection(ConnectionString))
|
||
{
|
||
connection.Open();
|
||
|
||
// Создаем таблицы
|
||
string createUsersTable = @"
|
||
CREATE TABLE Users (
|
||
Username TEXT PRIMARY KEY,
|
||
Password TEXT NOT NULL,
|
||
Role INTEGER NOT NULL
|
||
)";
|
||
|
||
string createDevicesTable = @"
|
||
CREATE TABLE Devices (
|
||
Id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
Name TEXT NOT NULL,
|
||
InventoryNumber TEXT NOT NULL,
|
||
Type INTEGER NOT NULL,
|
||
Location TEXT,
|
||
ResponsiblePerson TEXT,
|
||
State INTEGER NOT NULL,
|
||
RegistrationDate TEXT NOT NULL
|
||
)";
|
||
|
||
using (var command = new SQLiteCommand(createUsersTable, connection))
|
||
{
|
||
command.ExecuteNonQuery();
|
||
}
|
||
|
||
using (var command = new SQLiteCommand(createDevicesTable, connection))
|
||
{
|
||
command.ExecuteNonQuery();
|
||
}
|
||
|
||
// Добавляем предустановленных пользователей
|
||
string insertDefaultUsers = @"
|
||
INSERT INTO Users (Username, Password, Role) VALUES
|
||
('admin', 'admin', 1),
|
||
('user', 'user', 0)";
|
||
|
||
using (var command = new SQLiteCommand(insertDefaultUsers, connection))
|
||
{
|
||
command.ExecuteNonQuery();
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
#region Пользователи
|
||
|
||
// Авторизация пользователя
|
||
public static User Authenticate(string username, string password)
|
||
{
|
||
using (var connection = new SQLiteConnection(ConnectionString))
|
||
{
|
||
connection.Open();
|
||
string query = "SELECT Username, Password, Role FROM Users WHERE Username = @Username AND Password = @Password";
|
||
|
||
using (var command = new SQLiteCommand(query, connection))
|
||
{
|
||
command.Parameters.AddWithValue("@Username", username);
|
||
command.Parameters.AddWithValue("@Password", password);
|
||
|
||
using (var reader = command.ExecuteReader())
|
||
{
|
||
if (reader.Read())
|
||
{
|
||
return new User
|
||
{
|
||
Username = reader["Username"].ToString(),
|
||
Password = reader["Password"].ToString(),
|
||
Role = (UserRole)Convert.ToInt32(reader["Role"])
|
||
};
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region Устройства
|
||
|
||
// Получение всех устройств
|
||
public static List<Device> GetAllDevices()
|
||
{
|
||
List<Device> devices = new List<Device>();
|
||
|
||
using (var connection = new SQLiteConnection(ConnectionString))
|
||
{
|
||
connection.Open();
|
||
string query = "SELECT * FROM Devices";
|
||
|
||
using (var command = new SQLiteCommand(query, connection))
|
||
{
|
||
using (var reader = command.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
devices.Add(ReadDeviceFromDb(reader));
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
return devices;
|
||
}
|
||
|
||
// Добавление нового устройства
|
||
public static void AddDevice(Device device)
|
||
{
|
||
using (var connection = new SQLiteConnection(ConnectionString))
|
||
{
|
||
connection.Open();
|
||
string query = @"
|
||
INSERT INTO Devices (Name, InventoryNumber, Type, Location, ResponsiblePerson, State, RegistrationDate)
|
||
VALUES (@Name, @InventoryNumber, @Type, @Location, @ResponsiblePerson, @State, @RegistrationDate);
|
||
SELECT last_insert_rowid();";
|
||
|
||
using (var command = new SQLiteCommand(query, connection))
|
||
{
|
||
SetDeviceParameters(command, device);
|
||
device.Id = Convert.ToInt32(command.ExecuteScalar());
|
||
}
|
||
}
|
||
}
|
||
|
||
// Обновление существующего устройства
|
||
public static void UpdateDevice(Device device)
|
||
{
|
||
using (var connection = new SQLiteConnection(ConnectionString))
|
||
{
|
||
connection.Open();
|
||
string query = @"
|
||
UPDATE Devices
|
||
SET Name = @Name,
|
||
InventoryNumber = @InventoryNumber,
|
||
Type = @Type,
|
||
Location = @Location,
|
||
ResponsiblePerson = @ResponsiblePerson,
|
||
State = @State,
|
||
RegistrationDate = @RegistrationDate
|
||
WHERE Id = @Id";
|
||
|
||
using (var command = new SQLiteCommand(query, connection))
|
||
{
|
||
command.Parameters.AddWithValue("@Id", device.Id);
|
||
SetDeviceParameters(command, device);
|
||
command.ExecuteNonQuery();
|
||
}
|
||
}
|
||
}
|
||
|
||
// Удаление устройства
|
||
public static void DeleteDevice(int id)
|
||
{
|
||
using (var connection = new SQLiteConnection(ConnectionString))
|
||
{
|
||
connection.Open();
|
||
string query = "DELETE FROM Devices WHERE Id = @Id";
|
||
|
||
using (var command = new SQLiteCommand(query, connection))
|
||
{
|
||
command.Parameters.AddWithValue("@Id", id);
|
||
command.ExecuteNonQuery();
|
||
}
|
||
}
|
||
}
|
||
|
||
// Фильтрация устройств
|
||
public static List<Device> FilterDevices(DeviceType? type = null, DeviceState? state = null,
|
||
string location = null, string searchText = null)
|
||
{
|
||
List<Device> devices = new List<Device>();
|
||
StringBuilder queryBuilder = new StringBuilder("SELECT * FROM Devices WHERE 1=1");
|
||
Dictionary<string, object> parameters = new Dictionary<string, object>();
|
||
|
||
if (type.HasValue)
|
||
{
|
||
queryBuilder.Append(" AND Type = @Type");
|
||
parameters.Add("@Type", (int)type.Value);
|
||
}
|
||
|
||
if (state.HasValue)
|
||
{
|
||
queryBuilder.Append(" AND State = @State");
|
||
parameters.Add("@State", (int)state.Value);
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(location))
|
||
{
|
||
queryBuilder.Append(" AND Location LIKE @Location");
|
||
parameters.Add("@Location", "%" + location + "%");
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(searchText))
|
||
{
|
||
queryBuilder.Append(" AND (Name LIKE @SearchText OR InventoryNumber LIKE @SearchText)");
|
||
parameters.Add("@SearchText", "%" + searchText.ToLower() + "%");
|
||
}
|
||
|
||
using (var connection = new SQLiteConnection(ConnectionString))
|
||
{
|
||
connection.Open();
|
||
|
||
using (var command = new SQLiteCommand(queryBuilder.ToString(), connection))
|
||
{
|
||
foreach (var param in parameters)
|
||
{
|
||
command.Parameters.AddWithValue(param.Key, param.Value);
|
||
}
|
||
|
||
using (var reader = command.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
devices.Add(ReadDeviceFromDb(reader));
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
return devices;
|
||
}
|
||
|
||
// Статистика по типам устройств
|
||
public static Dictionary<DeviceType, int> GetDeviceTypeStats()
|
||
{
|
||
var result = new Dictionary<DeviceType, int>();
|
||
|
||
using (var connection = new SQLiteConnection(ConnectionString))
|
||
{
|
||
connection.Open();
|
||
string query = "SELECT Type, COUNT(*) as Count FROM Devices GROUP BY Type";
|
||
|
||
using (var command = new SQLiteCommand(query, connection))
|
||
{
|
||
using (var reader = command.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
DeviceType type = (DeviceType)Convert.ToInt32(reader["Type"]);
|
||
int count = Convert.ToInt32(reader["Count"]);
|
||
result[type] = count;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
// Экспорт данных в CSV
|
||
public static void ExportToCsv(string filePath)
|
||
{
|
||
var devices = GetAllDevices();
|
||
|
||
var lines = new List<string>
|
||
{
|
||
"ID;Наименование;Инвентарный номер;Тип;Местоположение;Ответственное лицо;Состояние;Дата постановки на учёт"
|
||
};
|
||
|
||
foreach (var device in devices)
|
||
{
|
||
lines.Add($"{device.Id};{device.Name};{device.InventoryNumber};{device.TypeString};" +
|
||
$"{device.Location};{device.ResponsiblePerson};{device.StateString};{device.RegistrationDate:dd.MM.yyyy}");
|
||
}
|
||
|
||
File.WriteAllLines(filePath, lines, Encoding.UTF8);
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region Вспомогательные методы
|
||
|
||
// Чтение устройства из базы данных
|
||
private static Device ReadDeviceFromDb(SQLiteDataReader reader)
|
||
{
|
||
return new Device
|
||
{
|
||
Id = Convert.ToInt32(reader["Id"]),
|
||
Name = reader["Name"].ToString(),
|
||
InventoryNumber = reader["InventoryNumber"].ToString(),
|
||
Type = (DeviceType)Convert.ToInt32(reader["Type"]),
|
||
Location = reader["Location"].ToString(),
|
||
ResponsiblePerson = reader["ResponsiblePerson"].ToString(),
|
||
State = (DeviceState)Convert.ToInt32(reader["State"]),
|
||
RegistrationDate = Convert.ToDateTime(reader["RegistrationDate"])
|
||
};
|
||
}
|
||
|
||
// Установка параметров устройства для команды SQLite
|
||
private static void SetDeviceParameters(SQLiteCommand command, Device device)
|
||
{
|
||
command.Parameters.AddWithValue("@Name", device.Name);
|
||
command.Parameters.AddWithValue("@InventoryNumber", device.InventoryNumber);
|
||
command.Parameters.AddWithValue("@Type", (int)device.Type);
|
||
command.Parameters.AddWithValue("@Location", device.Location ?? string.Empty);
|
||
command.Parameters.AddWithValue("@ResponsiblePerson", device.ResponsiblePerson ?? string.Empty);
|
||
command.Parameters.AddWithValue("@State", (int)device.State);
|
||
command.Parameters.AddWithValue("@RegistrationDate", device.RegistrationDate.ToString("yyyy-MM-dd HH:mm:ss"));
|
||
}
|
||
|
||
#endregion
|
||
}
|
||
}
|