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

namespace TinyHeels.App_Code
{
    class Dakhal
    {
        public string status { get; set; }
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }

        private DateTime date;

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

        private double amount;

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

        private double startBalance;

        public double StartBalance
        {
            get { return startBalance; }
            set { startBalance = value; }
        }

        private double closeBalance;

        public double CloseBalance
        {
            get { return closeBalance; }
            set { closeBalance = value; }
        }
        DBConnection strcon = new DBConnection();
        //----------------
        public int getIdByDate()
        {
            SqlConnection connection = strcon.connect();
            string sql = "SELECT Top(1) VoucherID from Voucher where Voucherstatus='Open' order by VoucherId DESC";
            SqlCommand cmd = new SqlCommand(sql, connection);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = connection;
            int count = Int32.Parse(cmd.ExecuteScalar().ToString());
            connection.Close();
            return count;
        }
        public double getVoucherAmount()
        {
            SqlConnection connection = strcon.connect();
            string sql = "SELECT Top(1) VoucherAmount from Voucher where Voucherstatus='Open' order by VoucherId DESC";
            SqlCommand cmd = new SqlCommand(sql, connection);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = connection;
            int count = Int32.Parse(cmd.ExecuteScalar().ToString());
            connection.Close();
            return count;
        }
        //----------------
        public int getIdByDate2()
        {
            try
            {
                SqlConnection connection = strcon.connect();

                string sql = "SELECT VoucherId from Voucher where Voucherdate='" + this.date + "'";
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = connection;
                int count = Int32.Parse(cmd.ExecuteScalar().ToString());
                connection.Close();
                return count;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        //----------------
        public DataTable getOpenAccounts()
        {
            SqlConnection connection = strcon.connect();
            using (SqlDataAdapter ds1 = new SqlDataAdapter("SELECT * from Voucher where Voucherstatus='Open'", connection))
            {
                DataSet ds = new DataSet();
                ds1.Fill(ds);
                DataTable dt = new DataTable();
                dt = ds.Tables[0];
                connection.Close();
                return dt;
            }
        }
        public DataTable getById()
        {
            SqlConnection connection = strcon.connect();
            using (SqlDataAdapter ds1 = new SqlDataAdapter("SELECT * from Voucher where VoucherId='" + this.id + "'", connection))
            {
                DataSet ds = new DataSet();
                ds1.Fill(ds);
                DataTable dt = new DataTable();
                dt = ds.Tables[0];
                connection.Close();
                return dt;
            }

        }
        public DataTable getVoucherByDate()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                using (SqlDataAdapter ds1 = new SqlDataAdapter("SELECT * from Voucher where VoucherDate='" + this.date + "'", connection))
                {
                    DataSet ds = new DataSet();
                    ds1.Fill(ds);
                    DataTable dt = new DataTable();
                    dt = ds.Tables[0];
                    connection.Close();
                    return dt;
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.ToString());
            }

        }
        public int setStartingBalance()
        {
            //insert
            try
            {
                SqlConnection connection = strcon.connect();
                string sql = "INSERT INTO Voucher(VoucherDate,VoucherStartBalance,Voucherstatus)"
                + "VALUES(@Date,@StartBalance,@status)";
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.Parameters.AddWithValue("@Date", this.date);
                cmd.Parameters.AddWithValue("@StartBalance", this.startBalance);
                cmd.Parameters.AddWithValue("@status", "Open");
                cmd.Connection = connection;
                cmd.ExecuteNonQuery();
                cmd.CommandText = "SELECT @@IDENTITY";
                int value = Convert.ToInt32(cmd.ExecuteScalar());
                connection.Close();
                return value;
            }
            catch (Exception ex)
            {
                throw new Exception("Could not insert Starting data" + ex);
            }
        }

        //-------------------------------------
        public void closeVoucher()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                string sql = "update Voucher set VoucherCloseBalance=((SELECT COALESCE(sum(V2Amount),0) from V2Detail where V2Voucher='" + id + "' AND V2Particulars='In')-(SELECT COALESCE(sum(V2Amount),0) from V2Detail where V2Voucher='" + id + "' AND V2Particulars='Out')),Voucherstatus='Closed' where VoucherID='" + this.id + "'";
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = connection;
                cmd.ExecuteNonQuery();
                connection.Close();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        //-------------------------------------
        public void openVoucher()
        {
            try
            {
                SqlConnection connection = strcon.connect();

                string sql = "update Voucher set Voucherstatus='Open' where VoucherId='" + this.id + "'";
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = connection;
                cmd.ExecuteNonQuery();
                connection.Close();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        //---------------------
        public bool checkVoucher()
        {
            SqlConnection connection = strcon.connect();
            string sql = "Select count(*) from Voucher where Voucherstatus='Open'";
            SqlCommand cmd = new SqlCommand(sql, connection);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = connection;
            int count = Int32.Parse(cmd.ExecuteScalar().ToString());
            connection.Close();
            if (count > 0)
                return true;
            else
                return false;
        }
        //---------------------
        public bool checkDateVoucher()
        {
            SqlConnection connection = strcon.connect();
            string sql = "Select count(*) from Voucher where Voucherstatus='Open' and VoucherDate='" + this.date + "'";
            SqlCommand cmd = new SqlCommand(sql, connection);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = connection;
            int count = Int32.Parse(cmd.ExecuteScalar().ToString());
            connection.Close();
            if (count > 0)
                return true;
            else
                return false;
        }
        //---------------------
        public double getRecentAmount()
        {
            SqlConnection connection = strcon.connect();
            string sql = "Select top(1) closeBalance from Voucher order by voucherid desc";
            SqlCommand cmd = new SqlCommand(sql, connection);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = connection;
            double count = double.Parse(cmd.ExecuteScalar().ToString());
            connection.Close();
            return count;
        }
    }
}
