InformationSystem/Database.cs - master - denispirit/informationSystem

InformationSystem/Database.cs
222 строки10 KB
denispirit
Первый коммит. Отправка в репозиторий
29 сен 2025, 10:39
29 сен 2025, 10:3924ca85c
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Windows.Forms;

namespace InformationSystem
{
    public class Database
    {
        // Строка подключения к базе данных MySQL
        private string connectionString = "Server=localhost;Database=auth_db;Uid=root;Pwd=root;";

        // Метод для проверки существования пользователя
        public int GetUserId(string username)
        {
            using (var connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    string query = "SELECT id FROM users WHERE username = @username";

                    using (var command = new MySqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@username", username);
                        var result = command.ExecuteScalar();
                        return result != null ? Convert.ToInt32(result) : -1;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Ошибка базы данных: " + ex.Message);
                    return -1;
                }
            }
        }

        // Метод для проверки пользователя при авторизации
        public bool ValidateUser(string username, string password)
        {
            using (var connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    string query = "SELECT password_hash, salt FROM users WHERE username = @username";

                    using (var command = new MySqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@username", username);

                        using (var reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                string storedHash = reader.GetString("password_hash");
                                string storedSalt = reader.GetString("salt");

                                return PasswordHasher.VerifyPassword(password, storedHash, storedSalt);
                            }
                        }
                    }
                    return false;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Ошибка базы данных: " + ex.Message);
                    return false;
                }
            }
        }

        // Метод для регистрации нового пользователя
        public bool RegisterUser(string username, string password)
        {
            if (string.IsNullOrWhiteSpace(username) || string.IsNullOrWhiteSpace(password))
            {
                MessageBox.Show("Логин и пароль не могут быть пустыми");
                return false;
            }

            if (username.Length < 3 || username.Length > 50)
            {
                MessageBox.Show("Логин должен быть от 3 до 50 символов");
                return false;
            }

            if (password.Length < 6)
            {
                MessageBox.Show("Пароль должен быть не менее 6 символов");
                return false;
            }

            using (var connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    // Проверяем существование пользователя
                    string checkQuery = "SELECT COUNT(1) FROM users WHERE username = @username";
                    using (var checkCommand = new MySqlCommand(checkQuery, connection))
                    {
                        checkCommand.Parameters.AddWithValue("@username", username);
                        if (Convert.ToInt32(checkCommand.ExecuteScalar()) > 0)
                        {
                            MessageBox.Show("Пользователь с таким логином уже существует");
                            return false;
                        }
                    }

                    // Хешируем пароль
                    var (hash, salt) = PasswordHasher.HashPassword(password);

                    // Регистрируем пользователя
                    string insertQuery = "INSERT INTO users (username, password_hash, salt) VALUES (@username, @password_hash, @salt)";
                    using (var insertCommand = new MySqlCommand(insertQuery, connection))
                    {
                        insertCommand.Parameters.AddWithValue("@username", username);
                        insertCommand.Parameters.AddWithValue("@password_hash", hash);
                        insertCommand.Parameters.AddWithValue("@salt", salt);

                        return insertCommand.ExecuteNonQuery() == 1;
                    }
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show($"Ошибка регистрации: {ex.Message}");
                    return false;
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"Неожиданная ошибка: {ex.Message}");
                    return false;
                }
            }
        }

        // Метод для получения заявок пользователя
        public List<RepairRequest> GetUserRepairRequests(int userId)
        {
            List<RepairRequest> requests = new List<RepairRequest>();

            using (var connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    string query = @"SELECT id, user_id, device_type, brand, model, 
                                            problem_description, status, created_date, completed_date 
                                     FROM repair_requests 
                                     WHERE user_id = @userId 
                                     ORDER BY created_date DESC";

                    using (var command = new MySqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@userId", userId);

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                RepairRequest request = new RepairRequest
                                {
                                    Id = reader.GetInt32("id"),
                                    UserId = reader.GetInt32("user_id"),
                                    DeviceType = reader.GetString("device_type"),
                                    Brand = reader.IsDBNull(reader.GetOrdinal("brand")) ? "" : reader.GetString("brand"),
                                    Model = reader.IsDBNull(reader.GetOrdinal("model")) ? "" : reader.GetString("model"),
                                    ProblemDescription = reader.GetString("problem_description"),
                                    Status = reader.GetString("status"),
                                    CreatedDate = reader.GetDateTime("created_date"),
                                    CompletedDate = reader.IsDBNull(reader.GetOrdinal("completed_date")) ?
                                                  null : (DateTime?)reader.GetDateTime("completed_date")
                                };
                                requests.Add(request);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Ошибка при загрузке заявок: " + ex.Message);
                }
            }
            return requests;
        }

        // Метод для создания новой заявки
        public bool CreateRepairRequest(RepairRequest request)
        {
            using (var connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    string query = @"INSERT INTO repair_requests 
                                    (user_id, device_type, brand, model, problem_description, status, created_date) 
                                    VALUES (@userId, @deviceType, @brand, @model, @problemDesc, @status, @createdDate)";

                    using (var command = new MySqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@userId", request.UserId);
                        command.Parameters.AddWithValue("@deviceType", request.DeviceType);
                        command.Parameters.AddWithValue("@brand", string.IsNullOrEmpty(request.Brand) ? DBNull.Value : (object)request.Brand);
                        command.Parameters.AddWithValue("@model", string.IsNullOrEmpty(request.Model) ? DBNull.Value : (object)request.Model);
                        command.Parameters.AddWithValue("@problemDesc", request.ProblemDescription);
                        command.Parameters.AddWithValue("@status", request.Status);
                        command.Parameters.AddWithValue("@createdDate", request.CreatedDate);

                        return command.ExecuteNonQuery() == 1;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Ошибка при создании заявки: " + ex.Message);
                    return false;
                }
            }
        }
    }
}