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

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

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<OrderItemMaster>().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 OrderItemMaster_AddNew(string RNGNo, string Item, string Spacification, string CastWeight, string FinishedWeight, string OrderQty, string StockOnFloor,string BalanceForProduction,string NoofPattenrnsforplate,string NoOfBoxNeeded,string BoxPerin150KG,string NoOfBoxPerShift,string NoOfShiftForTotalOrder,string TotalNoOfShift12hours,string BoxWeight,string TotalOredWeight,string MetalGrade,string Column1, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramRNGNo = new SqlParameter("pRNGNo", RNGNo);
            var paramItem = new SqlParameter("pItem", Item);
            var paramSpacification = new SqlParameter("pSpacification", Spacification);
            var paramCastWeight = new SqlParameter("pCastWeight", CastWeight);
            var paramFinishedWeight = new SqlParameter("pFinishedWeight", FinishedWeight);
            var paramOrderQty = new SqlParameter("pOrderQty", OrderQty);
            var paramStockOnFloor = new SqlParameter("pStockOnFloor", StockOnFloor);
            var paramBalanceForProduction = new SqlParameter("pBalanceForProduction", BalanceForProduction);
            var paramNoofPattenrnsforplate = new SqlParameter("pNoofPattenrnsforplate", NoofPattenrnsforplate);
            var paramNoOfBoxNeeded = new SqlParameter("pNoOfBoxNeeded", NoOfBoxNeeded);
            var paramBoxPerin150KG = new SqlParameter("pBoxPerin150KG", BoxPerin150KG);
            var paramNoOfBoxPerShift = new SqlParameter("pNoOfBoxPerShift", NoOfBoxPerShift);
            var paramNoOfShiftForTotalOrder = new SqlParameter("pNoOfShiftForTotalOrder", NoOfShiftForTotalOrder);
            var paramTotalNoOfShift12hours = new SqlParameter("pTotalNoOfShift12hours", TotalNoOfShift12hours);
            var paramBoxWeight = new SqlParameter("pBoxWeight", BoxWeight);
            var paramTotalOredWeight = new SqlParameter("pTotalOredWeight", TotalOredWeight);
            var paramMetalGrade = new SqlParameter("pMetalGrade", MetalGrade);
            var paramColumn1 = new SqlParameter("pColumn1", Column1);

            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = spOrderItemMaster_AddNew @pRNGNo,@pItem,@pSpacification,@pCastWeight,@pFinishedWeight,@pOrderQty,@pStockOnFloor,@pBalanceForProduction,@pNoofPattenrnsforplate,@pNoOfBoxNeeded,@pBoxPerin150KG,@pNoOfBoxPerShift,@pNoOfShiftForTotalOrder,@pTotalNoOfShift12hours,@pBoxWeight,@pTotalOredWeight,@pMetalGrade,@pColumn1",
                                                ReturnValueParam, paramRNGNo, paramItem, paramSpacification, paramCastWeight, paramFinishedWeight, paramOrderQty, paramStockOnFloor, paramBalanceForProduction, paramNoofPattenrnsforplate, paramNoOfBoxNeeded, paramBoxPerin150KG, paramNoOfBoxPerShift, paramNoOfShiftForTotalOrder, paramTotalNoOfShift12hours, paramBoxWeight, paramTotalOredWeight, paramMetalGrade, paramColumn1);

                    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 OrderItemMaster_Edit(int Item_ID, string RNGNo, string Item, string Spacification, string CastWeight, string FinishedWeight, string OrderQty, string StockOnFloor, string BalanceForProduction, string NoofPattenrnsforplate, string NoOfBoxNeeded, string BoxPerin150KG, string NoOfBoxPerShift, string NoOfShiftForTotalOrder, string TotalNoOfShift12hours, string BoxWeight, string TotalOredWeight, string MetalGrade, 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 paramRNGNo = new SqlParameter("pRNGNo", RNGNo);
            var paramItem = new SqlParameter("pItem", Item);
            var paramSpacification = new SqlParameter("pSpacification", Spacification);
            var paramCastWeight = new SqlParameter("pCastWeight", CastWeight);
            var paramFinishedWeight = new SqlParameter("pFinishedWeight", FinishedWeight);
            var paramOrderQty = new SqlParameter("pOrderQty", OrderQty);
            var paramStockOnFloor = new SqlParameter("pStockOnFloor", StockOnFloor);
            var paramBalanceForProduction = new SqlParameter("pBalanceForProduction", BalanceForProduction);
            var paramNoofPattenrnsforplate = new SqlParameter("pNoofPattenrnsforplate", NoofPattenrnsforplate);
            var paramNoOfBoxNeeded = new SqlParameter("pNoOfBoxNeeded", NoOfBoxNeeded);
            var paramBoxPerin150KG = new SqlParameter("pBoxPerin150KG", BoxPerin150KG);
            var paramNoOfBoxPerShift = new SqlParameter("pNoOfBoxPerShift", NoOfBoxPerShift);
            var paramNoOfShiftForTotalOrder = new SqlParameter("pNoOfShiftForTotalOrder", NoOfShiftForTotalOrder);
            var paramTotalNoOfShift12hours = new SqlParameter("pTotalNoOfShift12hours", TotalNoOfShift12hours);
            var paramBoxWeight = new SqlParameter("pBoxWeight", BoxWeight);
            var paramTotalOredWeight = new SqlParameter("pTotalOredWeight", TotalOredWeight);
            var paramMetalGrade = new SqlParameter("pMetalGrade", MetalGrade);


            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = spOrderItemMaster_Edit @pItem_ID,@pRNGNo,@pItem,@pSpacification,@pCastWeight,@pFinishedWeight,@pOrderQty,@pStockOnFloor,@pBalanceForProduction,@pNoofPattenrnsforplate,@pNoOfBoxNeeded,@pBoxPerin150KG,@pNoOfBoxPerShift,@pNoOfShiftForTotalOrder,@pTotalNoOfShift12hours,@pBoxWeight,@pTotalOredWeight,@pMetalGrade",
                                                ReturnValueParam,paramItem_ID, paramRNGNo, paramItem, paramSpacification, paramCastWeight, paramFinishedWeight, paramOrderQty, paramStockOnFloor, paramBalanceForProduction, paramNoofPattenrnsforplate, paramNoOfBoxNeeded, paramBoxPerin150KG, paramNoOfBoxPerShift, paramNoOfShiftForTotalOrder, paramTotalNoOfShift12hours, paramBoxWeight, paramTotalOredWeight, paramMetalGrade);

                    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<OrderItemMaster> OrderItemMaster_GetAll(int Item_ID,string RNGNo)
        {
            var paramItem_ID = new SqlParameter("pItem_ID", Item_ID);
            var paramRNGNo = new SqlParameter("pRNGNo", RNGNo);



            return this.Database.SqlQuery<OrderItemMaster>(
                            "spOrderItemMaster_GetAll @pItem_ID,@pRNGNo",
                            paramItem_ID, paramRNGNo).ToList();
        }
    }
}