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

namespace TinyHeels.App_Code
{
    class Ledger
    {
        DBConnection strcon = new DBConnection();
        private int ledgerId;
        public int LedgerId
        {
            get { return ledgerId; }
            set { ledgerId = value; }
        }
        private double credit;
        public double Credit
        {
            get { return credit; }
            set { credit = value; }
        }
        private double debit;
        public double Debit
        {
            get { return debit; }
            set { debit = value; }
        }
        private DateTime date;
        public DateTime Date
        {
            get { return date; }
            set { date = value; }
        }
        private string note;
        public string Note
        {
            get { return note; }
            set { note = value; }
        }
        private string particulars;
        public string Particualrs
        {
            get { return particulars; }
            set { particulars = value; }
        }

        //start of functions
        public void insertCredit()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                string sql = "INSERT INTO Ledger(LedgerDebit,LedgerCredit,LedgerNote,LedgerDate,LedgerParticulars)"
                + "VALUES(@LedgerDebit,@LedgerCredit,@LedgerNote,@LedgerDate,@LedgerParticulars)";
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.Parameters.AddWithValue("@LedgerDebit", 0);
                cmd.Parameters.AddWithValue("@LedgerCredit", this.credit);
                cmd.Parameters.AddWithValue("@LedgerNote", this.note);
                cmd.Parameters.AddWithValue("@LedgerDate", this.date);
                cmd.Parameters.AddWithValue("@LedgerParticulars", this.particulars);
                cmd.ExecuteNonQuery();

                connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        //start of functions
        public void insertDebit()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                string sql = "INSERT INTO Ledger(LedgerDebit,LedgerCredit,LedgerNote,LedgerDate,LedgerParticulars)"
                + "VALUES(@LedgerDebit,@LedgerCredit,@LedgerNote,@LedgerDate,@LedgerParticulars)";
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.Parameters.AddWithValue("@LedgerDebit", this.debit);
                cmd.Parameters.AddWithValue("@LedgerCredit", 0);
                cmd.Parameters.AddWithValue("@LedgerNote", this.note);
                cmd.Parameters.AddWithValue("@LedgerDate", this.date);
                cmd.Parameters.AddWithValue("@LedgerParticulars", this.particulars);
                cmd.ExecuteNonQuery();

                connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        //------------------- delete extra
        public void deleteLedger()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                string sql = "Delete from Ledger where LedgerID='" + this.ledgerId + "'";
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Could not delete Ledger data");
            }
        }
        //------------------------
        public DataTable getDailyLedger()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                using (SqlDataAdapter ds1 = new SqlDataAdapter("SELECT  * from Ledger where LedgerDate='" + 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("Could not get Ledger data");
            }
        }
        //------------------------
        public DataTable getMonthlyLedger()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                using (SqlDataAdapter ds1 = new SqlDataAdapter("SELECT  * from Ledger where DATENAME(month,LedgerDate)='" + Common.month + "' and Year(LedgerDate)='" + Common.year + "'", 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("Could not get Ledger data" + ex);
            }
        }
    }
}
