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

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

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

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<CustomerMaster>().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 CustomerMaster_AddNew(string Name, string MobileNo, string EmailId, string Address, string City,string State,string Country,string Pin,string Website,string NameOfContactPerson,string Designation,string ContPersonEmailId,string ContPersonMobile,string UserName,string Password,string IsActive,string UserID,string FinalYear,string Status,string GSTNo, 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 paramMobileNo = new SqlParameter("pMobileNo", MobileNo);
            var paramEmailId = new SqlParameter("pEmailId", EmailId);
            var paramAddress = new SqlParameter("pAddress", Address);
            var paramCity = new SqlParameter("pCity", City);
            var paramState = new SqlParameter("pState", State);
            var paramCountry = new SqlParameter("pCountry", Country);
            var paramPin = new SqlParameter("pPin", Pin);
            var paramWebsite = new SqlParameter("pWebsite", Website);
            var paramNameOfContactPerson = new SqlParameter("pNameOfContactPerson", NameOfContactPerson);
            var paramDesignation = new SqlParameter("pDesignation", Designation);
            var paramContPersonEmailId = new SqlParameter("pContPersonEmailId", ContPersonEmailId);
            var paramContPersonMobile = new SqlParameter("pContPersonMobile", ContPersonMobile);
            var paramUserName = new SqlParameter("pUserName", UserName);
            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 paramGSTNo = new SqlParameter("pGSTNo", GSTNo);



            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_CustomerMaster_AddNew @pName,@pMobileNo,@pEmailId,@pAddress,@pCity,@pState,@pCountry,@pPin,@pWebsite,@pNameOfContactPerson,@pDesignation,@pContPersonEmailId,@pContPersonMobile,@pUserName,@pPassword,@pIsActive,@pUserID,@pFinalYear,@pStatus,@pGSTNo",
                                                ReturnValueParam, paramName, paramMobileNo, paramEmailId, paramAddress, paramCity, paramState, paramCountry, paramPin, paramWebsite, paramNameOfContactPerson, paramDesignation, paramContPersonEmailId, paramContPersonMobile, paramUserName, paramPassword, paramIsActive, paramUserID, paramFinalYear, paramStatus,paramGSTNo);

                    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 CustomerMaster_Edit(int CustomerId, string Name, string MobileNo, string EmailId, string Address, string City, string State, string Country, string Pin, string Website, string NameOfContactPerson, string Designation, string ContPersonEmailId, string ContPersonMobile, string UserName, string Password, string IsActive, string UserID, string FinalYear, string Status,string GSTNo, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramCustomerId = new SqlParameter("pCustomerId", CustomerId);
            var paramName = new SqlParameter("pName", Name);
            var paramMobileNo = new SqlParameter("pMobileNo", MobileNo);
            var paramEmailId = new SqlParameter("pEmailId", EmailId);
            var paramAddress = new SqlParameter("pAddress", Address);
            var paramCity = new SqlParameter("pCity", City);
            var paramState = new SqlParameter("pState", State);
            var paramCountry = new SqlParameter("pCountry", Country);
            var paramPin = new SqlParameter("pPin", Pin);
            var paramWebsite = new SqlParameter("pWebsite", Website);
            var paramNameOfContactPerson = new SqlParameter("pNameOfContactPerson", NameOfContactPerson);
            var paramDesignation = new SqlParameter("pDesignation", Designation);
            var paramContPersonEmailId = new SqlParameter("pContPersonEmailId", ContPersonEmailId);
            var paramContPersonMobile = new SqlParameter("pContPersonMobile", ContPersonMobile);
            var paramUserName = new SqlParameter("pUserName", UserName);
            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 paramGSTNo = new SqlParameter("pGSTNo", GSTNo);




            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_CustomerMaster_Edit @pCustomerId,@pName,@pMobileNo,@pEmailId,@pAddress,@pCity,@pState,@pCountry,@pPin,@pWebsite,@pNameOfContactPerson,@pDesignation,@pContPersonEmailId,@pContPersonMobile,@pUserName,@pPassword,@pIsActive,@pUserID,@pFinalYear,@pStatus,@pGSTNo",
                                                ReturnValueParam,paramCustomerId,paramName, paramMobileNo, paramEmailId, paramAddress, paramCity, paramState, paramCountry, paramPin, paramWebsite, paramNameOfContactPerson, paramDesignation, paramContPersonEmailId, paramContPersonMobile, paramUserName, paramPassword, paramIsActive, paramUserID, paramFinalYear, paramStatus, paramGSTNo);

                    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<CustomerMaster> CustomerMaster_GetAll(int CustomerId)
        {
            var paramCustomerId = new SqlParameter("pCustomerId", CustomerId);
        


            return this.Database.SqlQuery<CustomerMaster>(
                            "Sp_CustomerMaster_GetAll @pCustomerId",
                            paramCustomerId).ToList();
        }

        public virtual int ItemMasterDetail_AddNew(string Item, string UserName, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramItem = new SqlParameter("pItem", Item);
            var paramUserName = new SqlParameter("pUserName", UserName);
           

            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_ItemDetailMaster_AddNew @pItem,@pUserName",
                                                ReturnValueParam, paramItem, 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<ItemDetail> ItemDetail_GetAll(int ItemDetail_ID)
        {
            var paramItemDetail_ID = new SqlParameter("pItemDetail_ID", ItemDetail_ID);



            return this.Database.SqlQuery<ItemDetail>(
                            "Sp_ItemDetailMaster_GetAll @pItemDetail_ID",
                            paramItemDetail_ID).ToList();
        }

        public virtual void Item_GetAll(string MobileNo, ref List<CustomerMaster> listOrder,
         ref List<ItemDetail> listOrderProduct)
        {
            var pMobileNoParameter = new SqlParameter("pMobileNo", MobileNo);
         
            var dbcon = this; using (var connection = dbcon.Database.Connection)
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = "EXEC spItemWise_GetAll @pMobileNo";
                command.Parameters.Add(pMobileNoParameter);
           
                using (var reader = command.ExecuteReader())
                {
                    listOrder = ((IObjectContextAdapter)dbcon).ObjectContext.Translate<CustomerMaster>(reader).ToList();
                    reader.NextResult();
                    listOrderProduct = ((IObjectContextAdapter)dbcon).ObjectContext.Translate<ItemDetail>(reader).ToList();
                }
            }
        }
    }
}