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 VendorMasterDbContext:DbContext
    {
        public VendorMasterDbContext() : base()
        {
            Database.SetInitializer<VendorMasterDbContext>(null);
            base.Database.Connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["RNGConnectionStrings"].ConnectionString;
        }

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

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<VendorMaster>().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 VendorMaster_AddNew(string NameOfVendor, string Address, string Mobile, string EmailId, string Website,string NameOfContactPerson,string Designation,string ContPersonEmailID,string ContPersonMobile,string JobWork,string ItemName,string IsActive,string UserId,string FinalYear,string Status,string Pin,string GSTNo, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramNameOfVendor = new SqlParameter("pNameOfVendor", NameOfVendor);
            var paramAddress = new SqlParameter("pAddress", Address);
            var paramMobile = new SqlParameter("pMobile", Mobile);
            var paramEmailId = new SqlParameter("pEmailId", EmailId);
            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 paramJobWork = new SqlParameter("pJobWork", JobWork);
            var paramItemName = new SqlParameter("pItemName", ItemName);
            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 paramPin = new SqlParameter("pPin", Pin);
            var paramGSTNo = new SqlParameter("pGSTNo", GSTNo);


            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_VendorMaster_AddNew @pNameOfVendor,@pAddress,@pMobile,@pEmailId,@pWebsite,@pNameOfContactPerson,@pDesignation,@pContPersonEmailID,@pContPersonMobile,@pJobWork,@pItemName,@pIsActive,@pUserId,@pFinalYear,@pStatus,@pPin,@pGSTNo",
                                                ReturnValueParam, paramNameOfVendor, paramAddress, paramMobile, paramEmailId, paramWebsite, paramNameOfContactPerson, paramDesignation, paramContPersonEmailID, paramContPersonMobile, paramJobWork, paramItemName, paramIsActive, paramUserId, paramFinalYear, paramStatus,paramPin,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 VendorMaster_Edit(int Vendor_Id, string NameOfVendor, string Address, string Mobile, string EmailId, string Website, string NameOfContactPerson, string Designation, string ContPersonEmailID, string ContPersonMobile, string JobWork, string ItemName, string IsActive, string UserId, string FinalYear, string Status,string Pin,string GSTNo, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramVendor_Id = new SqlParameter("pVendor_Id", Vendor_Id);
            var paramNameOfVendor = new SqlParameter("pNameOfVendor", NameOfVendor);
            var paramAddress = new SqlParameter("pAddress", Address);
            var paramMobile = new SqlParameter("pMobile", Mobile);
            var paramEmailId = new SqlParameter("pEmailId", EmailId);
            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 paramJobWork = new SqlParameter("pJobWork", JobWork);
            var paramItemName = new SqlParameter("pItemName", ItemName);
            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 paramPin = new SqlParameter("pPin", Pin);
            var paramGSTNo = new SqlParameter("pGSTNo", GSTNo);


            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_VendorMaster_Edit @pVendor_Id,@pNameOfVendor,@pAddress,@pMobile,@pEmailId,@pWebsite,@pNameOfContactPerson,@pDesignation,@pContPersonEmailID,@pContPersonMobile,@pJobWork,@pItemName,@pIsActive,@pUserId,@pFinalYear,@pStatus,@pPin,@pGSTNo",
                                                ReturnValueParam, paramVendor_Id, paramNameOfVendor, paramAddress, paramMobile, paramEmailId, paramWebsite, paramNameOfContactPerson, paramDesignation, paramContPersonEmailID, paramContPersonMobile, paramJobWork, paramItemName, paramIsActive, paramUserId, paramFinalYear, paramStatus,paramPin,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<VendorMaster> VendorMaster_GetAll(int Vendor_Id)
        {
            var paramVendor_Id = new SqlParameter("pVendor_Id", Vendor_Id);



            return this.Database.SqlQuery<VendorMaster>(
                            "Sp_VendrMaster_GetAll @pVendor_Id",
                            paramVendor_Id).ToList();
        }

        public virtual int VendorMasterItem_AddNew(string Item, string MobileNo, 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 paramMobileNo = new SqlParameter("pMobileNo", MobileNo);


            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_VendorItemDetailMaster_AddNew @pItem,@pMobileNo",
                                                ReturnValueParam, paramItem, paramMobileNo);

                    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<VendorMasterItem> VendorMasterItem_GetAll(int VendorItemDetail_ID)
        {
            var paramVendorItemDetail_ID = new SqlParameter("pVendorItemDetail_ID", VendorItemDetail_ID);



            return this.Database.SqlQuery<VendorMasterItem>(
                            "Sp_VendorItemDetailMaster_GetAll @pVendorItemDetail_ID",
                            paramVendorItemDetail_ID).ToList();
        }

    }
}