
using System;
using System.Data;
using System.Data.SqlClient;
namespace TinyHeels.App_Code
{
    class Products
    {
        DBConnection strcon = new DBConnection();
        public string code { get; set; }
        public string article { get; set; }
        public string color { get; set; }
        public string size { get; set; }
        public double cost { get; set; }
        public double quantity { get; set; }
        public double price { get; set; }
        public int category { get; set; }

        public bool onSale { get; set; }
        public bool onUpdate { get; set; }

        public DataTable searchByCodeArticle(string str)
        {
            try
            {
                SqlConnection conneciton = strcon.connect();
                using (SqlDataAdapter dat = new SqlDataAdapter("Select code as Code, article as Article, color as Color, size as Size, cost as Cost, price as Price,quantity as Quantity from products where code like '%"+str+"%' Or article like '%"+str+"%'", conneciton))
                {
                    DataSet ds = new DataSet();
                    dat.Fill(ds);
                    DataTable dt = ds.Tables[0];
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        public DataTable getByCode()
        {
            try
            {
                SqlConnection conneciton = strcon.connect();
                using (SqlDataAdapter dat = new SqlDataAdapter("Select * from products where code='"+this.code+"'", conneciton))
                {
                    DataSet ds = new DataSet();
                    dat.Fill(ds);
                    DataTable dt = ds.Tables[0];
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        public DataTable getAll()
        {
            try
            {
                SqlConnection conneciton = strcon.connect();
                using (SqlDataAdapter dat = new SqlDataAdapter("Select * from products", conneciton))
                {
                    DataSet ds = new DataSet();
                    dat.Fill(ds);
                    DataTable dt = ds.Tables[0];
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        public bool checkProductAvailability()
        {
            SqlConnection connection = strcon.connect();
            SqlCommand cmd1 = new SqlCommand();
            cmd1.CommandText = "SELECT COALESCE(count(*),0) from Products where code='" + this.code + "'";
            cmd1.CommandType = CommandType.Text;
            cmd1.Connection = connection;
            int value = Int32.Parse(cmd1.ExecuteScalar().ToString());
            connection.Close();
            if (value > 0)
                return true;
            else
                return false;
        }
        public void deleteProduct()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                string sql = "Delete from products Where code='" + this.code + "'";
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Could not delete product data");
            }
        }
        public void updateProduct()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "update Products set article=@article,color=@color,size=@size,cost=@cost,price=@price,onUpdate=@onUpdate,onSale=@onSale,category=@category,quantity=@quantity+quantity where code='" + this.code + "'";
                cmd.Parameters.AddWithValue("@article", this.article);
                cmd.Parameters.AddWithValue("@color", this.color);
                cmd.Parameters.AddWithValue("@size", this.size);
                cmd.Parameters.AddWithValue("@cost", this.cost);
                cmd.Parameters.AddWithValue("@price", this.price);
                cmd.Parameters.AddWithValue("@quantity", this.quantity);
                cmd.Parameters.AddWithValue("@onUpdate", this.onUpdate);
                cmd.Parameters.AddWithValue("@onSale", this.onSale);
                cmd.Parameters.AddWithValue("@category", this.category);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("There was a problem while updating \ndata in products" + ex);
            }
        }
        public void insertNewProduct()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                string sql = "INSERT INTO products(code,article,color,size,quantity,category,cost,price,onSale,onUpdate)"
                + "VALUES(@code,@article,@color,@size,@quantity,@category,@cost,@price,@onSale,@onUpdate)";
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.Parameters.AddWithValue("@code", this.code);
                cmd.Parameters.AddWithValue("@article", this.article);
                cmd.Parameters.AddWithValue("@color", this.color);
                cmd.Parameters.AddWithValue("@size", this.size);
                cmd.Parameters.AddWithValue("@quantity", this.quantity);
                cmd.Parameters.AddWithValue("@cost", this.cost);
                cmd.Parameters.AddWithValue("@price", this.price);
                cmd.Parameters.AddWithValue("@category", this.category);
                cmd.Parameters.AddWithValue("@onSale", this.onSale);
                cmd.Parameters.AddWithValue("@onUpdate", this.onUpdate);
                cmd.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Error while entering new product data" + ex);
            }
        }
        public DataTable filterItems()
        {
            try
            {
                DataTable dt;
                SqlConnection connection = strcon.connect();
                using (SqlDataAdapter ds = new SqlDataAdapter("Select distinct article from Products where category='" + this.category + "' AND onSale='True'", connection))
                {
                    DataSet dSet = new DataSet();
                    ds.Fill(dSet);
                    dt = new DataTable();
                    dt = dSet.Tables[0];
                    connection.Close();
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Sorry Unable To Get Stock Data"+ex);
            }
        }
        public DataTable filterItemsForSizes()
        {
            try
            {
                DataTable dt;
                SqlConnection connection = strcon.connect();
                using (SqlDataAdapter ds = new SqlDataAdapter("Select distinct size from Products where category='" + this.category + "' AND article='"+this.article+"' AND onSale='True'", connection))
                {
                    DataSet dSet = new DataSet();
                    ds.Fill(dSet);
                    dt = new DataTable();
                    dt = dSet.Tables[0];
                    connection.Close();
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Sorry Unable To Get Stock Data");
            }
        }
        public DataTable filterItemsForColors()
        {
            try
            {
                DataTable dt;
                SqlConnection connection = strcon.connect();
                using (SqlDataAdapter ds = new SqlDataAdapter("Select distinct color from Products where category='" + this.category + "' AND article='" + this.article + "' AND size='"+this.size+"' AND onSale='True'", connection))
                {
                    DataSet dSet = new DataSet();
                    ds.Fill(dSet);
                    dt = new DataTable();
                    dt = dSet.Tables[0];
                    connection.Close();
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Sorry Unable To Get Stock Data");
            }
        }
        public DataTable filterItemsForPrices()
        {
            try
            {
                DataTable dt;
                SqlConnection connection = strcon.connect();
                using (SqlDataAdapter ds = new SqlDataAdapter("Select price,code from Products where category='" + this.category + "' AND article='" + this.article + "' AND size='" + this.size + "' AND color='"+this.color+"' AND onSale='True'", connection))
                {
                    DataSet dSet = new DataSet();
                    ds.Fill(dSet);
                    dt = new DataTable();
                    dt = dSet.Tables[0];
                    connection.Close();
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Sorry Unable To Get Stock Data");
            }
        }
    }
}
