using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace MyApplication
{
    public class ExpenseDAL
    {
        string connStr = "Data Source=(local);Initial Catalog=MyProject;User ID=sa;Password=digidigi";
        SqlConnection mysqlconn;

        public ExpenseDAL()
        {
            mysqlconn = new SqlConnection(connStr);
            if (mysqlconn.State == ConnectionState.Closed)
            {
                mysqlconn.Open();
            }
        }
        /// <summary>
        /// open database connection
        /// </summary>
        private  void OpenDBConn()
        {
            mysqlconn = new SqlConnection(connStr);
            if (mysqlconn.State == ConnectionState.Closed)
            {
                mysqlconn.Open();
            }
        }
        /// <summary>
        /// close database connection
        /// </summary>
        private void CloseDBConn()
        {
            if (mysqlconn.State == ConnectionState.Open)
            {
                mysqlconn.Close();
            }
        }

        public void insertExpenseAccount(ExpenseAccountInfo eainfo)
        {
            OpenDBConn();
            SqlCommand ExpAccount = new SqlCommand("ExpenseAccount_Insert", mysqlconn);

            ExpAccount.CommandType = CommandType.StoredProcedure;
            ExpAccount.Parameters.Add("@ExpenseID", SqlDbType.UniqueIdentifier);
            ExpAccount.Parameters.Add("@Amount", SqlDbType.Money);
            ExpAccount.Parameters["@ExpenseID"].Value = eainfo.ExpID;
            ExpAccount.Parameters["@Amount"].Value = eainfo.Amount;
            ExpAccount.ExecuteNonQuery();

            CloseDBConn();
        }

        public DataTable GetAllExpenseAccountByStatus(string status)        
        {
            OpenDBConn();
            SqlCommand ExpAccount = new SqlCommand("ExpenseAccount_GetAllByStatus", mysqlconn);
            ExpAccount.CommandType = CommandType.StoredProcedure;

            ExpAccount.Parameters.Add("@AppStatus", SqlDbType.VarChar, 50);
            ExpAccount.Parameters["@AppStatus"].Value = status;

            SqlDataAdapter sda = new SqlDataAdapter(ExpAccount);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            DataTable mydt = ds.Tables[0];

            CloseDBConn();
            return mydt;
           
        }

        public DataTable GetAllExpenseAccountByName(string UserName)
        {
            OpenDBConn();
            SqlCommand ExpAccount = new SqlCommand("ExpenseAccount_GetAllByUserName", mysqlconn);
            ExpAccount.CommandType = CommandType.StoredProcedure;

            ExpAccount.Parameters.Add("@UserName", SqlDbType.VarChar, 50);
            ExpAccount.Parameters["@UserName"].Value = UserName;

            SqlDataAdapter sda = new SqlDataAdapter(ExpAccount);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            DataTable mydt = ds.Tables[0];

            CloseDBConn();
            return mydt;

        }
    }
}