using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data; //Need this for the DataSet, DataRow classes
using System.Data.SqlClient; //Need this for the SqlCommand, SqlConnection classes
using System.Configuration; //Need this to read from Web.Config file

namespace OnlinePetStoreManagementSystem.ProductManagement.Domain
{
    public class CategoryManager
    {
        /*------------------------------------------------------------------
              Method Name: GetOneCategroy
              Parameters: inCategoryId
              Purpose: Accept the course id from the calling program, to generate
                       a proper SQL to query for one category record from the database.
              Returns: Returns [one] Course object only. And this Course object
                       is filled with the
                       course record obtained from the database
             -------------------------------------------------------------------*/

        //Create an empty DataSet object, ds.
        public Category GetOneCategory(int inCategoryId)
        {
            //This object has the capability to hold information
            DataSet ds = new DataSet();
            //Create an object category object, course
            Category category = new Category();
            using (SqlConnection cn = new SqlConnection())
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        //Obtain connection string information from web.config
                        cn.ConnectionString =
                            ConfigurationManager.ConnectionStrings["WebaConnectionString"].ToString();
                        cmd.Connection = cn;//tell the cmd to use the cn
                        //Suppy the cmd with the necessary SQL
                        //The SELECT statement has a blank, @inCourseId.
                        //The cmd.Parameters.Add(...) is used to inform the SqlCommand object,
                        //about this blank. And, at the same time, a value is supplied to fill up the
                        //blank.
                        cmd.CommandText = "SELECT * FROM Category WHERE CategoryId = @inCategoryId";
                        cmd.Parameters.Add("@inCategoryId", SqlDbType.Int).Value = inCategoryId;
                        //Tell the da (DataAdapter) to use the cmd
                        da.SelectCommand = cmd;
                        //Open an active connection
                        cn.Open();
                        //Tell the da, so that the da can tell the cmd to send the SQL
                        //to the database. Database receives the SQL and returns one course record.
                        //The SqlDataAdapter object, da will capture the category record and write them
                        //into the Dataset(ds). The results are grouped and given a name,
                        //CategoryData. (the named results 'CategoryData' is also
                        //known as a DataTable)
                        da.Fill(ds, "CategoryData");
                        //Note that, there should only be one row of category info
                        //sitting inside the CategoryData DataTable.
                        cn.Close();//close the connection

                    }
                }
            }
            //There is only one datarow in the DataTable, ds.Tables["CategoryData"]
            //Use [0] to that datarow and copy out the category data into the category object.
            DataRow dr = ds.Tables["CategoryData"].Rows[0];
            category.CategoryId = int.Parse(dr["CategoryId"].ToString());
            category.CategoryName = dr["CategoryName"].ToString();
            category.CreatedAt = DateTime.Parse(dr["CreatedAt"].ToString());
            category.CreatedBy = dr["CreatedBy"].ToString();
            category.UpdatedAt = DateTime.Parse(dr["UpdatedAt"].ToString());
            category.UpdatedBy = dr["UpdatedBy"].ToString();

            return category;//return the List to the calling program.
        }//end of GetOneCategory() method

        public List<Category> GetAllCategory()
        {
            /*------------------------------------------------------------------
             Method Name: GetAllCategory
             Parameters: No parameters defined.
             Purpose: Generates a proper SQL to query for all course record from the database
                      which are not marked as deleted.
             Returns: Returns a List of Category objects which is filled with the
                      course records obtained from the database. For example, if there
                      are 4 category records obtained from the database, there should be
                      a List of 4 Category instances. Each instance contain one course information.
             -------------------------------------------------------------------*/
            //Create an empty DataSet object, ds.
            //This object has the capability to hold information
            DataSet ds = new DataSet();
            //Create an empty List, categoryList
            List<Category> categoryList = new List<Category>();
            using (SqlConnection cn = new SqlConnection())
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        //Obtain connection string information from web.config
                        cn.ConnectionString =
                            ConfigurationManager.ConnectionStrings["WebaConnectionString"].ToString();
                        cmd.Connection = cn;//tell the cmd to use the cn
                        //Suppy the cmd with the necessary SQL
                        cmd.CommandText = "SELECT * FROM Category WHERE DeletedAt IS NULL";
                        //Tell the da (DataAdapter) to use the cmd
                        da.SelectCommand = cmd;
                        //Open an active connection
                        cn.Open();
                        //Tell the da, so that the da can tell the cmd to send the SQL
                        //to the database. Database receives the SQL and returns the results.
                        //The SqlDataAdapter object, da will capture the result and write them
                        //into the Dataset(ds). The results are grouped and given a name,
                        //CourseData. (the named results 'CourseData' is also
                        //known as a DataTable)
                        da.Fill(ds, "CategoryData");
                        cn.Close();//close the connection

                    }
                }
            }
            //loop through the datarows in the DataTable, ds.Tables["CourseData"]
            //to fetch all the course records and pump them into the List, courseList.
            foreach (DataRow dr in ds.Tables["CategoryData"].Rows)
            {
                Category category = new Category();
                category.CategoryId = int.Parse(dr["CategoryId"].ToString());
                category.CategoryName = dr["CategoryName"].ToString();
                category.CreatedAt = DateTime.Parse(dr["CreatedAt"].ToString());
                category.CreatedBy = dr["CreatedBy"].ToString();
                category.UpdatedAt = DateTime.Parse(dr["UpdatedAt"].ToString());
                category.UpdatedBy = dr["UpdatedBy"].ToString();
                categoryList.Add(category);
            }
            return categoryList;//return the List to the calling program.
        }//End of GetAllCourse() method


        //Add Category Method
        public int AddCategory(string inCategoryName, string inCreatedBy, string inUpdatedBy)
        {
            int newCategoryId = 0;
            using (SqlConnection cn = new SqlConnection())
            {
                cn.ConnectionString = ConfigurationManager.ConnectionStrings["WebaConnectionString"].ToString();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = cn;//tell the cmd to use the cn
                    cmd.CommandText = "INSERT Category (CategoryName,CreatedBy,UpdatedBy ) " +
                        " OUTPUT Inserted.CategoryId VALUES " +
                        "(@inCategoryName,@inCreatedBy,@inUpdatedBy)";
                    cmd.Parameters.Add("@inCategoryName", SqlDbType.VarChar, 100).Value = inCategoryName;
                    cmd.Parameters.Add("@inCreatedBy", SqlDbType.VarChar, 30).Value = inCreatedBy;
                    cmd.Parameters.Add("@inUpdatedBy", SqlDbType.VarChar, 30).Value = inUpdatedBy;

                    cn.Open();
                    try
                    {
                        newCategoryId = Int32.Parse(cmd.ExecuteScalar().ToString());
                    }
                    catch (SqlException sqlEx)
                    { //Capture unique constraint violation
                        if (sqlEx.Message.Contains("uc_Category_CategoryName") == true)
                        {
                            string messageTemplate = "Unable to save due to {0} category id found in other records.";
                            string message = string.Format(messageTemplate, inCategoryName);
                            //Throw an exception message to the calling program.
                            throw new System.ArgumentException(message); //The method will end processing here.
                        }
                        else
                        {
                            throw new System.ArgumentException(sqlEx.Message);
                        }
                    }
                    cn.Close();
                }//end of using SqlCommand, cmd
            }//end of using SqlConnection, cn
             //If there are no runtime error (or exceptions) occurred, the
             //.NET engine will execute the following before finish executing
             //this AddOneStudent() method.
            return newCategoryId; //return the new record id back to the calling program.
        }//End of AddOneStudent() method


        public int CheckNumberOfCategoryRecords(int inCategoryId)
        {
            /*------------------------------------------------------------------
              Method Name: CheckNumberOfStudentRecords
              Parameters: inCourseId
              Purpose: Accept the course id from the calling program, to generate
                       a proper SELECT COUNT(*) SQL to find out the number of student records
                       linked to a specific course (by checking the course id).
              This method is usually called to display information, or to check for information
              before marking a Course record as deleted.
              Returns: Returns int number of student records to the calling program.
             -------------------------------------------------------------------*/
            int numOfCategoryRecords = 0;

            using (SqlConnection cn = new SqlConnection())
            {
                using (SqlCommand cmd = new SqlCommand())
                {

                    //Obtain connection string information from web.config
                    cn.ConnectionString =
                        ConfigurationManager.ConnectionStrings["WebaConnectionString"].ToString();
                    cmd.Connection = cn;//tell the cmd to use the cn
                                        //Suppy the cmd with the necessary SQL
                                        //The SELECT statement has a blank, @inCourseId.
                                        //The cmd.Parameters.Add(...) is used to inform the SqlCommand object,
                                        //about this blank. And, at the same time, a value is supplied to fill up the
                                        //blank.
                    cmd.CommandText = "SELECT COUNT(CategorytId) FROM Category WHERE CategoryId =  @inCategoryId";
                    cmd.Parameters.Add("@inCategoryId", SqlDbType.Int).Value = inCategoryId;

                    //Open an active connection
                    cn.Open();
                    //Send the SQL to the database server. If the UPDATE operation
                    //is successful, the numOfRecordsAffectd will have a value of 1.
                    //If the UPDATE process is not successful, the numOfRecordsAffected will
                    //have a value of 0.
                    numOfCategoryRecords = int.Parse(cmd.ExecuteScalar().ToString());
                    //sitting inside the CourseData DataTable.
                    cn.Close();//close the connection


                }//end of using(SqlCommand cmd ...)
            }//end of using(SqlConnection cn ...)

            return numOfCategoryRecords;
        }//end of CheckNumberOfStudentRecords() method


        /*------------------------------------------------------------------
        Method Name: UpdateOneCourse
        Parameters: inCourseId, inCourseAbbreviation, inCourseName
        Purpose: Accepts course information from the calling program, to
        prepare an UPDATE course SQL, to update one course record in the
        database.
        Returns: Returns a Boolean value of either true or false.
                 If the update process is successful, true is returned.
                 If the update process is not successful, false is returned.
        -------------------------------------------------------------------*/
        public bool UpdateOneCategory(int inCategoryId, string inCategoryName, string inCreatedBy, string inUpdatedBy)
        {
            //Create an Int variable, numOfRecordsAffected
            int numOfRecordsAffected = 0;
            //Create an empty DataSet object, ds.
            //This object has the capability to hold information
            DataSet ds = new DataSet();
            //Create an object Course object, course
            Category category = new Category();
            using (SqlConnection cn = new SqlConnection())
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    //Obtain connection string information from web.config
                    cn.ConnectionString =
                        ConfigurationManager.ConnectionStrings["WebaConnectionString"].ToString();
                    cmd.Connection = cn;//tell the cmd to use the cn
                                        //Suppy the cmd with the necessary SQL
                                        //The SELECT statement has a blank, @inCourseId.
                                        //The cmd.Parameters.Add(...) is used to inform the SqlCommand object,
                                        //about this blank. And, at the same time, a value is supplied to fill up the
                                        //blank.
                    cmd.CommandText = "UPDATE Category SET CategoryName = @inCategoryName ";
                    cmd.CommandText += ", CreatedBy = @inCreatedBy , CreatedAt = GETDATE() ";
                    cmd.CommandText += ", UpdatedBy = @inUpdatedBy , UpdatedAt = GETDATE() WHERE ";
                    cmd.CommandText += " CategoryId = @inCategoryId";
                    cmd.Parameters.Add("@inCategoryId", SqlDbType.Int).Value = inCategoryId;
                    cmd.Parameters.Add("@inCategoryName", SqlDbType.VarChar, 100).Value = inCategoryName;
                    cmd.Parameters.Add("@inCreatedBy", SqlDbType.VarChar, 30).Value = inCreatedBy;
                    cmd.Parameters.Add("@inUpdatedBy", SqlDbType.VarChar, 30).Value = inUpdatedBy;
                    cn.Open();
                    try
                    {
                        numOfRecordsAffected = cmd.ExecuteNonQuery();
                    }
                    catch (SqlException sqlEx)
                    {
                        if(sqlEx.Message.Contains("uc_Category_CategoryName") == true)
                        {
                            string messageTemplate = "Unable to save due to {0} category id found in other records.";
                            string message = string.Format(messageTemplate, inCategoryName);
                            //Throw an exception message to the calling program.
                            throw new System.ArgumentException(message); //The method will end processing here.
                        }
                    }

                }//end of using (SqlCommand cmd = new SqlCommand())
            }//end of using(SqlConnection cn = new SqlConnection())

            if (numOfRecordsAffected == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }//end of UpdateOneCategroy() method

    }
}