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

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

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ItemMaster>().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 ItemMaster_AddNew(string DesignName, string Type, string MetalType, string Op_Stock, string MinimumLevel, string MaxLevel, string MeasurementUnitName,string CastWeight,string FinishedWeight,string DrawingWeight,string IsActive,string UserID,string FinalYear,string Status,DateTime CreatedDate,string PatttenOnPlate,string BoxWeight,string MetalGrade,string RequiredHardness,string ItemForIRN2,string MaterialType,string GalvanizeWeight, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramDesignName = new SqlParameter("pDesignName", DesignName);

            var paramType = new SqlParameter("pType", Type);

            var paramMetalType = new SqlParameter("pMetalType", MetalType);

            var paramOp_Stock = new SqlParameter("pOp_Stock", Op_Stock);

            var paramMinimumLevel = new SqlParameter("pMinimumLevel", MinimumLevel);

            var paramMaxLevel = new SqlParameter("pMaxLevel", MaxLevel);

            var paramMeasurementUnitName = new SqlParameter("pMeasurementUnitName", MeasurementUnitName);

            var paramCastWeight = new SqlParameter("pCastWeight", CastWeight);

            var paramFinishedWeight = new SqlParameter("pFinishedWeight", FinishedWeight);

            var paramDrawingWeight = new SqlParameter("pDrawingWeight", DrawingWeight);

            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 paramCreatedDate = new SqlParameter("pCreatedDate", CreatedDate);

            var paramPatttenOnPlate = new SqlParameter("pPatttenOnPlate", PatttenOnPlate);

            var paramBoxWeight = new SqlParameter("pBoxWeight", BoxWeight);

            var paramMetalGrade = new SqlParameter("pMetalGrade", MetalGrade);

            var paramRequiredHardness = new SqlParameter("pRequiredHardness", RequiredHardness);


            var paramItemForIRN2 = new SqlParameter("pItemForIRN2", ItemForIRN2);
            var paramMaterialType = new SqlParameter("pMaterialType", MaterialType);

            var paramGalvanizeWeight = new SqlParameter("pGalvanizeWeight", GalvanizeWeight);
            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_ItemMaster_AddNew @pDesignName,@pType,@pMetalType,@pOp_Stock,@pMinimumLevel,@pMaxLevel,@pMeasurementUnitName,@pCastWeight,@pFinishedWeight,@pDrawingWeight,@pIsActive,@pUserID,@pFinalYear,@pStatus,@pCreatedDate,@pPatttenOnPlate,@pBoxWeight,@pMetalGrade,@pRequiredHardness,@pItemForIRN2,@pMaterialType,@PGalvanizeWeight",
                                                ReturnValueParam, paramDesignName, paramType, paramMetalType, paramOp_Stock, paramMinimumLevel, paramMaxLevel, paramMeasurementUnitName, paramCastWeight, paramFinishedWeight, paramDrawingWeight, paramIsActive, paramUserID, paramFinalYear, paramStatus,paramCreatedDate, paramPatttenOnPlate, paramBoxWeight, paramMetalGrade, paramRequiredHardness,paramItemForIRN2,paramMaterialType, paramGalvanizeWeight);

                    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 ItemMaster_Edit(int Item_Id, string DesignName, string Type, string MetalType, string Op_Stock, string MinimumLevel, string MaxLevel, string MeasurementUnitName, string CastWeight, string FinishedWeight, string DrawingWeight, string IsActive, string UserID, string FinalYear, string Status,DateTime CreatedDate, string PatttenOnPlate,string BoxWeight,string MetalGrade,string RequiredHardness,string ItemForIRN2,string MaterialType,string GalvanizeWeight, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramItem_Id = new SqlParameter("pItem_Id", Item_Id);

            var paramDesignName = new SqlParameter("pDesignName", DesignName);

            var paramType = new SqlParameter("pType", Type);

            var paramMetalType = new SqlParameter("pMetalType", MetalType);

            var paramOp_Stock = new SqlParameter("pOp_Stock", Op_Stock);

            var paramMinimumLevel = new SqlParameter("pMinimumLevel", MinimumLevel);

            var paramMaxLevel = new SqlParameter("pMaxLevel", MaxLevel);

            var paramMeasurementUnitName = new SqlParameter("pMeasurementUnitName", MeasurementUnitName);

            var paramCastWeight = new SqlParameter("pCastWeight", CastWeight);

            var paramFinishedWeight = new SqlParameter("pFinishedWeight", FinishedWeight);

            var paramDrawingWeight = new SqlParameter("pDrawingWeight", DrawingWeight);

            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 paramCreatedDate = new SqlParameter("pCreatedDate", CreatedDate);

            var paramPatttenOnPlate = new SqlParameter("pPatttenOnPlate", PatttenOnPlate);

            var paramBoxWeight = new SqlParameter("pBoxWeight", BoxWeight);

            var paramMetalGrade = new SqlParameter("pMetalGrade", MetalGrade);

            var paramRequiredHardness = new SqlParameter("pRequiredHardness", RequiredHardness);

            var paramItemForIRN2 = new SqlParameter("pItemForIRN2", ItemForIRN2);

            var paramMaterialType = new SqlParameter("pMaterialType", MaterialType);


            var paramGalvanizeWeight = new SqlParameter("pGalvanizeWeight", GalvanizeWeight);

            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_ItemMaster_Edit @pItem_Id,@pDesignName,@pType,@pMetalType,@pOp_Stock,@pMinimumLevel,@pMaxLevel,@pMeasurementUnitName,@pCastWeight,@pFinishedWeight,@pDrawingWeight,@pIsActive,@pUserID,@pFinalYear,@pStatus,@pCreatedDate,@pPatttenOnPlate,@pBoxWeight,@pMetalGrade,@pRequiredHardness,@pItemForIRN2,@pMaterialType,@pGalvanizeWeight",
                                                ReturnValueParam, paramItem_Id, paramDesignName, paramType, paramMetalType, paramOp_Stock, paramMinimumLevel, paramMaxLevel, paramMeasurementUnitName, paramCastWeight, paramFinishedWeight, paramDrawingWeight, paramIsActive, paramUserID, paramFinalYear, paramStatus,paramCreatedDate, paramPatttenOnPlate, paramBoxWeight, paramMetalGrade, paramRequiredHardness, paramItemForIRN2, paramMaterialType, paramGalvanizeWeight);

                    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<ItemMaster> ItemMaster_GetAll(int Item_Id,string IsActive)
        {
            var paramItem_Id = new SqlParameter("pItem_Id", Item_Id);
            var paramIsActive = new SqlParameter("pIsActive", IsActive);



            return this.Database.SqlQuery<ItemMaster>(
                            "Sp_ItemMaster_GetAll @pItem_Id,@pIsActive",
                            paramItem_Id, paramIsActive).ToList();
        }


    }
}