using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using RNGClassLibrary;
using System.Data.Entity;
using System.Data.SqlClient;

namespace RNGWcfService1.ContextClass
{
    public class EmployeeMasterDbContext:DbContext
    {
        public EmployeeMasterDbContext() : base()
        {
            Database.SetInitializer<EmployeeMasterDbContext>(null);
            base.Database.Connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["RNGConnectionStrings"].ConnectionString;
        }

        public System.Data.Entity.DbSet<EmployeeMaster> User { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<EmployeeMaster>().MapToStoredProcedures();
        }


        public void GetErrorDescription(int ErrorNo, ref string ErrorDesc)
        {
            var pErrCodeParam = new SqlParameter("pErrorNo", ErrorNo);

            var pErrDescParam = new SqlParameter("pErrorDesc", System.Data.SqlDbType.VarChar, 200);
            pErrDescParam.Direction = System.Data.ParameterDirection.Output;
            pErrDescParam.Value = "";

            this.Database.ExecuteSqlCommand("spErrors_GetErrorDesc @pErrorNo, @pErrorDesc out", pErrCodeParam, pErrDescParam);

            ErrorDesc = Convert.ToString(pErrDescParam.Value);
        }



        public virtual int EmployeeMaster_AddNew(string Name, string Address, string Designation, string MobileNo, string EmailId,string Password,string IsActive,string UserID,string FinalYear,string Status,string UserName, ref string errorDesc)
        {
            errorDesc = "";

            var ReturnValueParam = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int);
            ReturnValueParam.Direction = System.Data.ParameterDirection.Output;

            var paramName = new SqlParameter("pName", Name);
            var paramAddress = new SqlParameter("pAddress", Address);
            var paramDesignation = new SqlParameter("pDesignation", Designation);
            var paramMobileNo = new SqlParameter("pMobileNo", MobileNo);
            var paramEmailId = new SqlParameter("pEmailId", EmailId);
            var paramPassword = new SqlParameter("pPassword", Password);
            var paramIsActive = new SqlParameter("pIsActive", IsActive);
            var paramUserID = new SqlParameter("pUserID", UserID);
            var paramFinalYear = new SqlParameter("pFinalYear", FinalYear);
            var paramStatus = new SqlParameter("pStatus", Status);
            var paramUserName = new SqlParameter("pUserName", UserName);


            var dbContext = this.Database;

            using (var dbContextTransaction = dbContext.BeginTransaction())
            {
                int errorNo = 0;

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_EmployeeMaster_AddNew @pName,@pAddress,@pDesignation,@pMobileNo,@pEmailId,@pPassword,@pIsActive,@pUserID,@pFinalYear,@pStatus,@pUserName",
                                                ReturnValueParam, paramName, paramAddress, paramDesignation, paramMobileNo, paramEmailId, paramPassword, paramIsActive, paramUserID, paramFinalYear, paramStatus, paramUserName);

                    errorNo = Convert.ToInt32(ReturnValueParam.Value);

                    if (errorNo >= 0) //**** Success
                    {
                        dbContextTransaction.Commit();
                        this.SaveChanges();
                    }
                    else //**** Error ****
                    {
                        dbContextTransaction.Rollback();
                        GetErrorDescription(errorNo, ref errorDesc);
                    }
                }
                catch (Exception ex)
                {
                    dbContextTransaction.Rollback();

                    errorNo = -1;
                    errorDesc = Convert.ToString(ex.Message);
                }
                return errorNo;
            }
        }


        public virtual int EmployeeMaster_Edit(int EmployeeId, string Name, string Address, string Designation, string MobileNo, string EmailId, string Password, string IsActive, string UserID, string FinalYear, string Status,string UserName, ref string errorDesc)
        {
            errorDesc = "";

            var ReturnValueParam = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int);
            ReturnValueParam.Direction = System.Data.ParameterDirection.Output;

            var paramEmployeeId = new SqlParameter("pEmployeeId", EmployeeId);
            var paramName = new SqlParameter("pName", Name);
            var paramAddress = new SqlParameter("pAddress", Address);
            var paramDesignation = new SqlParameter("pDesignation", Designation);
            var paramMobileNo = new SqlParameter("pMobileNo", MobileNo);
            var paramEmailId = new SqlParameter("pEmailId", EmailId);
            var paramPassword = new SqlParameter("pPassword", Password);
            var paramIsActive = new SqlParameter("pIsActive", IsActive);
            var paramUserID = new SqlParameter("pUserID", UserID);
            var paramFinalYear = new SqlParameter("pFinalYear", FinalYear);
            var paramStatus = new SqlParameter("pStatus", Status);
            var paramUserName = new SqlParameter("pUserName", UserName);


            var dbContext = this.Database;

            using (var dbContextTransaction = dbContext.BeginTransaction())
            {
                int errorNo = 0;

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_EmployeeMaster_Edit @pEmployeeId,@pName,@pAddress,@pDesignation,@pMobileNo,@pEmailId,@pPassword,@pIsActive,@pUserID,@pFinalYear,@pStatus,@pUserName",
                                                ReturnValueParam, paramEmployeeId, paramName, paramAddress, paramDesignation, paramMobileNo, paramEmailId, paramPassword, paramIsActive, paramUserID, paramFinalYear, paramStatus, paramUserName);

                    errorNo = Convert.ToInt32(ReturnValueParam.Value);

                    if (errorNo >= 0) //**** Success
                    {
                        dbContextTransaction.Commit();
                        this.SaveChanges();
                    }
                    else //**** Error ****
                    {
                        dbContextTransaction.Rollback();
                        GetErrorDescription(errorNo, ref errorDesc);
                    }
                }
                catch (Exception ex)
                {
                    dbContextTransaction.Rollback();

                    errorNo = -1;
                    errorDesc = Convert.ToString(ex.Message);
                }
                return errorNo;
            }
        }


        public virtual List<EmployeeMaster> EmployeeMaster_GetAll(int EmployeeId)
        {
            var paramEmployeeId = new SqlParameter("pEmployeeId", EmployeeId);
           


            return this.Database.SqlQuery<EmployeeMaster>(
                            "Sp_EmployeeMaster_GetAll @pEmployeeId",
                            paramEmployeeId).ToList();
        }

        public virtual int EmployeeMaster_Login(string UserName, string Password, ref int EmployeeId, ref string errorDesc)
        {
            errorDesc = "";
            var ReturnValueParam = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int);
            ReturnValueParam.Direction = System.Data.ParameterDirection.Output;

            var paramUserName = new SqlParameter("pUserName", UserName);
            var paramPassword = new SqlParameter("pPassword", Password);

            var paramEmployeeId = new SqlParameter("pEmployeeId", EmployeeId);
            paramEmployeeId.Direction = System.Data.ParameterDirection.InputOutput;



            var dbContext = this.Database;

            //  using (var dbContextTransaction = dbContext.BeginTransaction())
            //{
            int errorNo = 0;

            try
            {
                dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_Employee_Login @pUserName,@pPassword,@pEmployeeId out",
                                            ReturnValueParam, paramUserName, paramPassword, paramEmployeeId);

                errorNo = Convert.ToInt32(ReturnValueParam.Value);

                if (errorNo >= 0) //**** Success
                {
                    //dbContextTransaction.Commit();
                    this.SaveChanges();

                    EmployeeId = Convert.ToInt32(paramEmployeeId.Value);
                    UserName = Convert.ToString(paramUserName.Value);

                }
                else //**** Error ****
                {
                    // dbContextTransaction.Rollback();
                    GetErrorDescription(errorNo, ref errorDesc);
                }
            }
            catch (Exception ex)
            {
                //dbContextTransaction.Rollback();

                errorNo = -1;
                errorDesc = Convert.ToString(ex.Message);
            }
            return errorNo;
        }

        public virtual int EmployeeMasterChangePassword_Edit(int EmployeeId, string Password, ref string errorDesc)
        {
            errorDesc = "";

            var ReturnValueParam = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int);
            ReturnValueParam.Direction = System.Data.ParameterDirection.Output;

            var paramEmployeeId = new SqlParameter("pEmployeeId", EmployeeId);
            var paramPassword = new SqlParameter("pPassword", Password);
            
            var dbContext = this.Database;

            using (var dbContextTransaction = dbContext.BeginTransaction())
            {
                int errorNo = 0;

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_EmployeeLoginChange_Edit @pEmployeeId,@pPassword",
                                                  ReturnValueParam, paramEmployeeId, paramPassword);

                    errorNo = Convert.ToInt32(ReturnValueParam.Value);

                    if (errorNo >= 0) //**** Success
                    {
                        dbContextTransaction.Commit();
                        this.SaveChanges();
                    }
                    else //**** Error ****
                    {
                        dbContextTransaction.Rollback();
                        GetErrorDescription(errorNo, ref errorDesc);
                    }
                }
                catch (Exception ex)
                {
                    dbContextTransaction.Rollback();

                    errorNo = -1;
                    errorDesc = Convert.ToString(ex.Message);
                }
                return errorNo;
            }
        }
    }
}