using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using MySql.Data.MySqlClient;

using System.Data;

using Sklad_MVP.Models;

​

namespace Sklad_MVP._Repositories

{

    public class ProductRepository : BaseRepository, IProductRepository

    {

​

        // конструкторы

        public ProductRepository(string connectionString)

        {

            this.connectionString = connectionString;

        }

        // Методы

        public void Add(ProductModel productModel)

        {

            using (var connection = new MySqlConnection(connectionString))

            using (var command = new MySqlCommand())

            {

                connection.Open();

                command.Connection = connection;

                command.CommandText = @"insert into sklad(name, qty, price, categoryID, companyID, date)

                                      Values (@name, @qty, @price, @category, @company, @date)";

                command.Parameters.Add("@name", MySqlDbType.VarChar).Value = productModel.Name;

                command.Parameters.Add("@qty", MySqlDbType.Int32).Value = productModel.Qty;

                command.Parameters.Add("@price", MySqlDbType.Double).Value = productModel.Price;

                command.Parameters.Add("@category", MySqlDbType.VarChar).Value = productModel.Category;

                command.Parameters.Add("@company", MySqlDbType.VarChar).Value = productModel.Company;

                command.Parameters.Add("@date", MySqlDbType.DateTime).Value = productModel.Date;

                command.ExecuteNonQuery();

            }

        }

        public void Delete(int id)

        {

            using (var connection = new MySqlConnection(connectionString))

            using (var command = new MySqlCommand())

            {

                connection.Open();

                command.Connection = connection;

                command.CommandText = @"DELETE from sklad WHERE id = @id";

                command.Parameters.Add("@id", MySqlDbType.Int32).Value = id;

                command.ExecuteNonQuery();

            }

        }

        public void Edit(ProductModel productModel)

        {

            using (var connection = new MySqlConnection(connectionString))

            using (var command = new MySqlCommand())

            {

                connection.Open();

                command.Connection = connection;

                command.CommandText = @"update sklad set

                                      name = @name, qty = @qty, price = @price, categoryID = @category, companyID = @company, date = @date 

                                      WHERE id = @id";

                command.Parameters.Add("@name", MySqlDbType.VarChar).Value = productModel.Name;

                command.Parameters.Add("@qty", MySqlDbType.Int32).Value = productModel.Qty;

                command.Parameters.Add("@price", MySqlDbType.Double).Value = productModel.Price;

                command.Parameters.Add("@category", MySqlDbType.VarChar).Value = productModel.Category;

                command.Parameters.Add("@company", MySqlDbType.VarChar).Value = productModel.Company;

                command.Parameters.Add("@date", MySqlDbType.DateTime).Value = productModel.Date;

                command.Parameters.Add("@id", MySqlDbType.Int32).Value = productModel.ID;

                command.ExecuteNonQuery();

            }

        }

        public IEnumerable<ProductModel> GetAllProduct()

        {

​

            ProductView viewfilter = new ProductView();

            var productList = new List<ProductModel>();

            var filterr = from product in productList where product.Category == viewfilter.comboBox2.Text.ToString() select product;

            using (var connection = new MySqlConnection(connectionString))

            using (var command = new MySqlCommand())

            {

                connection.Open();

                command.Connection = connection;

                command.CommandText = @"SELECT sklad.id, sklad.name, sklad.qty, sklad.price, category.name, company.name, sklad.date FROM sklad 

                                      LEFT JOIN category ON sklad.categoryID = category.id 

                                      LEFT JOIN company ON company.id = sklad.companyID";

                using (var reader = command.ExecuteReader())

                {

                    while (reader.Read())

                    {

                        var productModel = new ProductModel();

                        productModel.ID = (int)reader[0];

                        productModel.Name = reader[1].ToString();

                        productModel.Qty = Convert.ToInt32(reader[2]);

                        productModel.Price = Convert.ToDouble(reader[3]);

                        productModel.Category = reader[4].ToString();

                        productModel.Company = reader[5].ToString();

                        productModel.Date = Convert.ToDateTime(reader[6]);

                        productList.Add(productModel);

​

                    }

                }

            }

                return productList;

        }

        public IEnumerable<CategoryModel> GetValueForFilterCombobox()

        {

            var categoryFilterList = new List<CategoryModel>();

            using (var connection = new MySqlConnection(connectionString))

            using (var command = new MySqlCommand())

            {

                connection.Open();

                command.Connection = connection;

                command.CommandText = "SELECT category.name FROM category";

                using (var reader = command.ExecuteReader())

                {

                    while (reader.Read())

                    {

                        var categoryModell = new CategoryModel();

                        categoryModell.Name = reader[0].ToString();

                        categoryFilterList.Add(categoryModell);

                    }

                }

            }

            return categoryFilterList;

        }

        public IEnumerable<CategoryModel> GetCategoryComboboxValue()

        {

            var categoryList = new List<CategoryModel>();

            using (var connection = new MySqlConnection(connectionString))

            using (var command = new MySqlCommand())

            {

                connection.Open();

                command.Connection = connection;

                command.CommandText = "SELECT category.id AS id, CONCAT(category.id, ' | ', category.name) as name FROM category";

                using (var reader = command.ExecuteReader())

                {

                    while (reader.Read())

                    {

                        var categoryModel = new CategoryModel();

                        categoryModel.Id = (int)reader[0];

                        categoryModel.Name = reader[1].ToString();

                        categoryList.Add(categoryModel);

                    }

                }

            }

            return categoryList;

        }

        public IEnumerable<CompanyModel> GetCompanyName()

        {

            var companyList = new List<CompanyModel>();

            using (var connection = new MySqlConnection(connectionString))

            using (var command = new MySqlCommand())

            {

                connection.Open();

                command.Connection = connection;

                command.CommandText = "SELECT company.id AS id, CONCAT(company.id, ' | ', company.name) as name FROM company";

                using (var reader = command.ExecuteReader())

                {

                    while (reader.Read())

                    {

                        var companyModel = new CompanyModel();

                        companyModel.Id = (int)reader[0];

                        companyModel.Name = reader[1].ToString();

                        companyList.Add(companyModel);

                    }

                }

            }

            return companyList;

        }

​

​

        public IEnumerable<ProductModel> GetValueForSearch(string value)

        { 

            var productList = new List<ProductModel>();

            int productId = int.TryParse(value, out _) ? Convert.ToInt32(value) : 0;

            string productName = value;

            using (var connection = new MySqlConnection(connectionString))

            using (var command = new MySqlCommand())

            {

                connection.Open();

                command.Connection = connection;

                command.CommandText = @"Select * from skladplyus where id = @id or name like @name+'%'";

                command.Parameters.Add("@id", MySqlDbType.Int32).Value = productId;

                command.Parameters.Add("@name", MySqlDbType.VarChar).Value = productName;

​

                using (var reader = command.ExecuteReader())

                {

                    while (reader.Read())

                    {

                        var productModel = new ProductModel();

                        productModel.ID = (int)reader[0];

                        productModel.Name = reader[1].ToString();

                        productModel.Qty = (int)reader[2];

                        productModel.Price = (int)reader[3];

                        productModel.Category = reader[4].ToString();

                        productModel.Company = reader[5].ToString();

                        productModel.Date = (DateTime)reader[6];

                        productList.Add(productModel);

                    }

​

                }

            }

            return productList;

        }

​

    }

}

​