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

namespace TinyHeels.App_Code
{
    class Payrolls
    {
        DBConnection strcon = new DBConnection();
        private int payrollId;

        public int PayrollId
        {
            get { return payrollId; }
            set { payrollId = value; }
        }

        private string month;

        public string Month
        {
            get { return month; }
            set { month = value; }
        }
        private int year;

        public int Year
        {
            get { return year; }
            set { year = value; }
        }

        private double amount;

        public double Amount
        {
            get { return amount; }
            set { amount = value; }
        }

        private string date;

        public string Date
        {
            get { return date; }
            set { date = value; }
        }


        private string emp;

        public string Emp
        {
            get { return emp; }
            set { emp = value; }
        }

        public string PayrollType { get; set; }
        //-------------------
        public int paySalary()
        {
            //insert
            try
            {
                SqlConnection connection = strcon.connect();
                string sql = "INSERT INTO payroll(PayrollMonth,PayrollYear,PayrollAmount,PayrollDate,PayrollEmployee,PayrollType)"
                + "VALUES(@month,@year,@amount,@date,@emp,@PayrollType)";
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.Parameters.AddWithValue("@month", this.month);
                cmd.Parameters.AddWithValue("@year", this.year);
                cmd.Parameters.AddWithValue("@amount", this.amount);
                cmd.Parameters.AddWithValue("@date", this.date);
                cmd.Parameters.AddWithValue("@emp", this.emp);
                cmd.Parameters.AddWithValue("@PayrollType", this.PayrollType);
                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 void degrade(double dd)
        {
            try
            {
                SqlConnection connection = strcon.connect();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "update payroll set PayrollAmount=PayrollAmount - '" + dd + "' where PayrollEmployee='" + this.emp + "' AND PayrollType='Others'";
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Could not update quantity data");
            }
        }
        public void upgrade(double dd)
        {
            try
            {
                bool x = this.checkT();
                if (x)
                {
                    SqlConnection connection = strcon.connect();
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "update payroll set PayrollAmount=PayrollAmount + '" + dd + "' where PayrollEmployee='" + this.emp + "' AND PayrollType='Others'";
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
                else
                {
                    SqlConnection connection = strcon.connect();
                    string sql = "INSERT INTO payroll(PayrollMonth,PayrollYear,PayrollAmount,PayrollDate,PayrollEmployee,PayrollType)"
                    + "VALUES(@month,@year,@amount,@date,@emp,@PayrollType)";
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    cmd.Parameters.AddWithValue("@month", this.month);
                    cmd.Parameters.AddWithValue("@year", this.year);
                    cmd.Parameters.AddWithValue("@amount", dd);
                    cmd.Parameters.AddWithValue("@date", this.date);
                    cmd.Parameters.AddWithValue("@emp", this.emp);
                    cmd.Parameters.AddWithValue("@PayrollType", this.PayrollType);
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Could not update quantity data" + ex);
            }
        }
        public bool checkPaiment()
        {
            SqlConnection connection = strcon.connect();
            SqlCommand cmd1 = new SqlCommand();
            cmd1.CommandText = "SELECT count(*) from payroll where PayrollMonth='" + this.month + "' AND PayrollYear='" + this.year + "' AND PayrollEmployee='" + this.emp + "' AND PayrollType='Salary'";
            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 bool checkT()
        {
            SqlConnection connection = strcon.connect();
            SqlCommand cmd1 = new SqlCommand();
            cmd1.CommandText = "SELECT count(*) from payroll where PayrollType='Others' AND PayrollEmployee='" + this.emp + "'";
            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 getBalance()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                SqlCommand cmd1 = new SqlCommand();
                cmd1.CommandText = "SELECT (select COALESCE(SUM(PayrollAmount), 0) from payroll where PayrollType='Others') as Balance from payroll where PayrollEmployee='" + this.emp + "'";
                cmd1.CommandType = CommandType.Text;
                cmd1.Connection = connection;
                double value = (double)cmd1.ExecuteScalar();
                connection.Close();
                return value;
            }
            catch (Exception ex)
            {
                return 0;
            }
        }

        public DataTable getPayroll()
        {
            SqlConnection connection = strcon.connect();
            using (SqlDataAdapter ds1 = new SqlDataAdapter("SELECT *  from payroll where PayrollEmployee='" + this.emp + "' OR ( PayrollEmployee='" + this.emp + "' AND PayrollType='Others')", connection))
            //using (SqlDataAdapter ds1 = new SqlDataAdapter("SELECT *  from payroll where PayrollEmployee='" + this.emp + "' AND PayrollMonth='" + this.month + "' AND PayrollYear='" + this.year + "' OR PayrollType='Others'", connection))
            {
                DataSet ds = new DataSet();
                ds1.Fill(ds);
                DataTable dt = new DataTable();
                dt = ds.Tables[0];
                connection.Close();
                return dt;
            }
        }
    }
}
