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

namespace TinyHeels.App_Code
{
    class DakhalDetail : Dakhal
    {
        private int detailId;

        public int DetailId
        {
            get { return detailId; }
            set { detailId = value; }
        }

        private int dakhalId;

        public int DakhalId
        {
            get { return dakhalId; }
            set { dakhalId = value; }
        }

        private double dakhalAmount;

        public double DakhalAmount
        {
            get { return dakhalAmount; }
            set { dakhalAmount = value; }
        }

        private string particulars;

        public string Particulars
        {
            get { return particulars; }
            set { particulars = value; }
        }

        private string note;

        public string Note
        {
            get { return note; }
            set { note = value; }
        }
        DBConnection strcon = new DBConnection();

        //----------------------------
        public void insertDetail()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "INSERT INTO V2Detail(V2Voucher,V2Amount,V2Particulars,V2Note)"
               + "VALUES(@V2Voucher,@Amount,@particulars,@note)";
                cmd.Parameters.AddWithValue("@V2Voucher", this.dakhalId);
                cmd.Parameters.AddWithValue("@Amount", this.dakhalAmount);
                cmd.Parameters.AddWithValue("@particulars", this.particulars);
                cmd.Parameters.AddWithValue("@note", this.note);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = connection;
                cmd.ExecuteNonQuery();

                connection.Close();
                updateDakhalAmount(this.dakhalId);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        public bool checkStartingBalance()
        {
            SqlConnection connection = strcon.connect();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT COUNT(*) from V2Detail T, Voucher D where D.VoucherDate='" + Date + "' and T.V2Particulars='Starting Balance' AND T.V2Voucher=D.VoucherId";
            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 void insertStartingBalance()
        {
            try
            {
                //---- insert into Voucherand get the id
                StartBalance = dakhalAmount;
                int id = setStartingBalance();
                //---- insert Detail
                SqlConnection connection = strcon.connect();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "INSERT INTO V2Detail(V2Voucher,V2Amount,V2Particulars,V2Note)"
                + "VALUES(@V2Voucher,@Amount,@particulars,@note)";
                cmd.Parameters.AddWithValue("@V2Voucher", id);
                cmd.Parameters.AddWithValue("@Amount", this.dakhalAmount);
                cmd.Parameters.AddWithValue("@particulars", this.particulars);
                cmd.Parameters.AddWithValue("@note", this.note);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = connection;
                cmd.ExecuteNonQuery();

                connection.Close();
                //------ update VoucherAmount
                updateDakhalAmount(id);
            }
            catch (Exception ex)
            {
                throw new Exception("Could not insert detail data " + ex);
            }

        }
        //-------------------------------------
        public void updateStartingBalance()
        {
            try
            {
                int id = getIdByDate();
                SqlConnection connection = strcon.connect();
                string sql = "update Voucher set VoucharStartBalance='" + this.dakhalAmount + "' where VoucherDate='" + this.Date + "'";
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = connection;
                cmd.ExecuteNonQuery();

                string sql2 = "update V2Detail set V2Amount='" + this.dakhalAmount + "' where V2Voucher='" + id + "' AND V2Note='Starting Balance'";
                SqlCommand cmd2 = new SqlCommand(sql2, connection);
                cmd2.CommandType = CommandType.Text;
                cmd2.Connection = connection;
                cmd2.ExecuteNonQuery();
                connection.Close();

            }
            catch (Exception ex)
            {
                throw new Exception("I think you should the account first");
            }
        }
        //-------------------------------------
        public void updateDakhalAmount(int id)
        {
            try
            {

                SqlConnection connection = strcon.connect();
                string sql = "update Voucher set VoucherAmount=((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')) where VoucherId='" + 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 delete()
        //{
        //    try
        //    {
        //        SqlConnection connection = strcon.connect();
        //        SqlCommand cmd = new SqlCommand();
        //        cmd.CommandText = "Delete from dakhaldetail where orderId='" + this.orderId + "' AND V2Voucher='" + this.dakhalId + "' AND V2Note='Purchase'";
        //        cmd.CommandType = CommandType.Text;
        //        cmd.Connection = connection;
        //        cmd.ExecuteNonQuery();



        //        connection.Close();
        //        updateDakhalAmount(this.dakhalId);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception("Could not delete?");
        //    }
        //}
        //----------------
        //public void deleteVoucher()
        //{
        //    try
        //    {
        //        SqlConnection connection = strcon.connect();
        //        SqlCommand cmd = new SqlCommand();
        //        cmd.CommandText = "Delete from dakhaldetail where orderId='" + this.orderId + "' AND V2Note='Purchase'";
        //        cmd.CommandType = CommandType.Text;
        //        cmd.Connection = connection;
        //        cmd.ExecuteNonQuery();
        //        connection.Close();
        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception("Could not delete? " + ex);
        //    }
        //}
        //----------------
        //public void deleteexp()
        //{
        //    try
        //    {
        //        SqlConnection connection = strcon.connect();
        //        SqlCommand cmd = new SqlCommand();
        //        cmd.CommandText = "Delete from dakhaldetail where orderId='" + this.orderId + "'  AND V2Note='Expenditure'";
        //        cmd.Connection = connection;
        //        cmd.CommandType = CommandType.Text;
        //        cmd.ExecuteNonQuery();


        //        connection.Close();
        //        //------ update VoucherAmount
        //        updateDakhalAmount(this.dakhalId);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception("Could not delete?" + ex);
        //    }
        //}
        //----------------
        //public void deleteInstallment()
        //{
        //    try
        //    {
        //        SqlConnection connection = strcon.connect();
        //        SqlCommand cmd = new SqlCommand();
        //        cmd.CommandText = "Delete from dakhaldetail where orderId='" + this.orderId + "' AND V2Note='Installment'";
        //        cmd.Connection = connection;
        //        cmd.CommandType = CommandType.Text;
        //        cmd.ExecuteNonQuery();

        //        connection.Close();
        //        updateDakhalAmount(this.dakhalId);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception("Could not delete?");
        //    }
        //}
        //public void update()
        //{
        //    try
        //    {
        //        SqlConnection connection = strcon.connect();
        //        SqlCommand cmd = new SqlCommand();
        //        cmd.CommandText = "update dakhaldetail set amount=@amount where orderId='" + this.orderId + "' AND V2Voucher='" + this.dakhalId + "' AND V2Note='Purchase'";
        //        cmd.Parameters.AddWithValue("@amount", this.dakhalAmount);
        //        cmd.Connection = connection;
        //        cmd.CommandType = CommandType.Text;
        //        cmd.ExecuteNonQuery();


        //        connection.Close();
        //        updateDakhalAmount(this.dakhalId);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception("Could not update " + ex);
        //    }
        //}
        //public void updateAdvance()
        //{
        //    try
        //    {
        //        SqlConnection connection = strcon.connect();
        //        SqlCommand cmd = new SqlCommand();
        //        cmd.CommandText = "update dakhaldetail set amount=@amount where orderId='" + this.orderId + "' AND V2Voucher='" + this.dakhalId + "' AND V2Note='Advance'";
        //        cmd.Parameters.AddWithValue("@amount", this.dakhalAmount);
        //        cmd.Connection = connection;
        //        cmd.CommandType = CommandType.Text;
        //        cmd.ExecuteNonQuery();


        //        connection.Close();
        //        updateDakhalAmount(this.dakhalId);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception("Could not update " + ex);
        //    }
        //}
        //public void updateExpenditure()
        //{
        //    try
        //    {
        //        SqlConnection connection = strcon.connect();
        //        SqlCommand cmd = new SqlCommand();
        //        cmd.CommandText = "update dakhaldetail set amount=@amount where orderId='" + this.orderId + "' AND V2Seriel='" + this.dakhalId + "' AND V2Note='Expenditure'";
        //        cmd.Parameters.AddWithValue("@amount", this.dakhalAmount);
        //        cmd.Connection = connection;
        //        cmd.CommandType = CommandType.Text;
        //        cmd.ExecuteNonQuery();


        //        connection.Close();
        //        updateDakhalAmount(this.dakhalId);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception("Could not update " + ex);
        //    }
        //}
        public DataTable getDetailById()
        {
            SqlConnection connection = strcon.connect();
            using (SqlDataAdapter ds1 = new SqlDataAdapter("SELECT * from V2Detail where V2Voucher='" + this.DakhalId + "'", connection))
            {
                DataSet ds = new DataSet();
                ds1.Fill(ds);
                DataTable dt = new DataTable();
                dt = ds.Tables[0];
                connection.Close();
                return dt;
            }
        }
    }
}
