using System;
using System.Data;
using System.Data.SqlClient;

namespace TinyHeels.App_Code
{
    class Stock
    {
        DBConnection strcon = new DBConnection();
        public int serial { get; set; }
        public string code { get; set; }
        public DateTime date { get; set; }
        public string time { get; set; }
        public double lastStock { get; set; }
        public double topup { get; set; }
        public double drain { get; set; }
        public double currentStock { get; set; }
        public string particulars { get; set; }

        public int insertNewStock()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                string sql = "INSERT INTO stock(code,date,time,lastStock,topup,drain,currentStock,particulars)"
                + "VALUES(@code,@date,@time,@lastStock,@topup,@drain,@currentStock,@particulars)";
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.Parameters.AddWithValue("@code", this.code);
                cmd.Parameters.AddWithValue("@date", this.date);
                cmd.Parameters.AddWithValue("@time", this.time);
                cmd.Parameters.AddWithValue("@lastStock", this.lastStock);
                cmd.Parameters.AddWithValue("@topup", this.topup);
                cmd.Parameters.AddWithValue("@drain", this.drain);
                cmd.Parameters.AddWithValue("@currentStock", this.currentStock);
                cmd.Parameters.AddWithValue("@particulars", this.particulars);
                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT @@IDENTITY";
                int value = Convert.ToInt32(cmd.ExecuteScalar());
                connection.Close();
                return value;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        public bool checkProductAvailability()
        {
            SqlConnection connection = strcon.connect();
            SqlCommand cmd1 = new SqlCommand();
            cmd1.CommandText = "SELECT count(*) from stock 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 double getLastStock()
        {
            if (checkProductAvailability())
            {
                SqlConnection connection = strcon.connect();
                SqlCommand cmd1 = new SqlCommand();
                cmd1.CommandText = "SELECT TOP(1) currentStock from stock where code='" + this.code + "' order by seriel DESC";
                cmd1.CommandType = CommandType.Text;
                cmd1.Connection = connection;
                double value = double.Parse(cmd1.ExecuteScalar().ToString());
                connection.Close();
                return value;
            }
            else
            {
                return 0.0;
            }
        }
        public void updateCurrentStock()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "update stock set currentStock=lastStock+topUp-drain  where code='" + this.code + "'";
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("There was a problem while updating \ndata in products");
            }
        }
    }
}
