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


namespace RNGWcfService1.ContextClass
{
    public class CASTINGHARDNESDbContext : DbContext
    {

        public CASTINGHARDNESDbContext() : base()
        {
            Database.SetInitializer<CASTINGHARDNESDbContext>(null);
            base.Database.Connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["RNGConnectionStrings"].ConnectionString;
        }

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

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<CASTINGHARDNES>().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 CASTINGHARDNES_AddNew(string Date, string Unit, string ProcessName,string IsActive, string UserID, string FinalYear, string Status,
            ref int Casting_Id, List<CASTINGHARDNESItems> ListCASTINGHARDNESItems, ref string errorDesc)
        {
            errorDesc = "";

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


            var paramDate = new SqlParameter("pDate", Date);
            var paramUnit = new SqlParameter("pUnit", Unit);
            var paramProcessName = new SqlParameter("pProcessName", ProcessName);
           

            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 paramCasting_Id = new SqlParameter("pCasting_Id", System.Data.SqlDbType.Int);
            paramCasting_Id.Direction = System.Data.ParameterDirection.Output;


            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_CASTINGHARDNES_AddNew @pDate, @pUnit,@pProcessName,@pIsActive,@pUserID,@pFinalYear,@pStatus,@pCasting_Id OUT",
                                                ReturnValueParam, paramDate, paramUnit, paramProcessName, paramIsActive, paramUserID, paramFinalYear, paramStatus, paramCasting_Id);

                    errorNo = Convert.ToInt32(ReturnValueParam.Value);

                    if (errorNo >= 0) //**** Success
                    {


                        Casting_Id = Convert.ToInt32(paramCasting_Id.Value);
                        if (Casting_Id > 0 && ListCASTINGHARDNESItems != null)
                        {
                            if (ListCASTINGHARDNESItems.Count > 0)
                            {
                                foreach (CASTINGHARDNESItems objProd in ListCASTINGHARDNESItems)
                                {
                                    var ReturnValueParam1 = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int);
                                    ReturnValueParam1.Direction = System.Data.ParameterDirection.Output;

                                    var paramID = new SqlParameter("pCasting_Id", Casting_Id);
                                    var ParamItem = new SqlParameter("pItem", objProd.Item);
                                    var paramProductionDate = new SqlParameter("pProductionDate", objProd.ProductionDate);
                                    var paramTotalProductionpcs = new SqlParameter("pTotalProductionpcs", objProd.TotalProductionpcs);
                                    var paramRequiredHardness = new SqlParameter("pRequiredHardness", objProd.RequiredHardness);
                                    var paramAvgCheckPoint = new SqlParameter("pAvgCheckPoint", objProd.AvgCheckPoint);
                                    var paramHeatNo = new SqlParameter("pHeatNo", objProd.HeatNo);
                                    var paramSample1 = new SqlParameter("pSample1", objProd.Sample1);
                                    var paramSample2 = new SqlParameter("pSample2", objProd.Sample2);
                                    var paramSample3 = new SqlParameter("pSample3", objProd.Sample3);
                                    var paramSample4 = new SqlParameter("pSample4", objProd.Sample4);
                                    var paramSample5 = new SqlParameter("pSample5", objProd.Sample5);
                                    var paramSample6 = new SqlParameter("pSample6", objProd.Sample6);
                                    var paramRemarks = new SqlParameter("pRemarks", objProd.Remarks);
                                    var paramOther1 = new SqlParameter("pOther1", objProd.Other1);
                                    var paramOther2 = new SqlParameter("pOther2", objProd.Other2);
                                    var paramOther3 = new SqlParameter("pOther3", objProd.Other3);
                                    var paramOther4 = new SqlParameter("pOther4", objProd.Other4);

                                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_CASTINGHARDNESITEM_AddNew @pCasting_Id,@pItem,@pProductionDate, @pTotalProductionpcs,@pRequiredHardness, @pAvgCheckPoint,@pHeatNo,@pSample1,@pSample2,@pSample3,@pSample4,@pSample5,@pSample6,@pRemarks,@pOther1,@pOther2,@pOther3,@pOther4",
                                                ReturnValueParam1, paramID, ParamItem, paramProductionDate, paramTotalProductionpcs, paramRequiredHardness, paramAvgCheckPoint, paramHeatNo, paramSample1, paramSample2, paramSample3, paramSample4, paramSample5, paramSample6, paramRemarks, paramOther1, paramOther2, paramOther3, paramOther4);

                                    errorNo = Convert.ToInt32(ReturnValueParam1.Value);


                                    if (errorNo < 0)
                                    {
                                        dbContextTransaction.Rollback();
                                        GetErrorDescription(errorNo, ref errorDesc);
                                        break;
                                    }
                                }
                            }
                        }



                        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 CASTINGHARDNES_Edit(int Casting_Id, string Date, string Unit, string ProcessName, string IsActive, string UserID, string FinalYear, string Status,
            List<CASTINGHARDNESItems> ListCASTINGHARDNESItems, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramCasting_Id = new SqlParameter("pCasting_Id", Casting_Id);
            var paramDate = new SqlParameter("pDate", Date);
            var paramUnit = new SqlParameter("pUnit", Unit);
            var paramProcessName = new SqlParameter("pProcessName", ProcessName);


            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 dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_CASTINGHARDNES_Edit @pCasting_Id,@pDate, @pUnit,@pProcessName,@pIsActive,@pUserID,@pFinalYear,@pStatus",
                                                ReturnValueParam, paramCasting_Id, paramDate, paramUnit, paramProcessName, paramIsActive, paramUserID, paramFinalYear, paramStatus);

                    errorNo = Convert.ToInt32(ReturnValueParam.Value);

                    if (errorNo >= 0) //**** Success
                    {


                        Casting_Id = Convert.ToInt32(paramCasting_Id.Value);
                        if (Casting_Id > 0 && ListCASTINGHARDNESItems != null)
                        {
                            if (ListCASTINGHARDNESItems.Count > 0)
                            {
                                foreach (CASTINGHARDNESItems objProd in ListCASTINGHARDNESItems)
                                {
                                    var ReturnValueParam1 = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int);
                                    ReturnValueParam1.Direction = System.Data.ParameterDirection.Output;

                                    var paramID = new SqlParameter("pCasting_Id", Casting_Id);
                                    var ParamItem = new SqlParameter("pItem", objProd.Item);
                                    var paramProductionDate = new SqlParameter("pProductionDate", objProd.ProductionDate);
                                    var paramTotalProductionpcs = new SqlParameter("pTotalProductionpcs", objProd.TotalProductionpcs);
                                    var paramRequiredHardness = new SqlParameter("pRequiredHardness", objProd.RequiredHardness);
                                    var paramAvgCheckPoint = new SqlParameter("pAvgCheckPoint", objProd.AvgCheckPoint);
                                    var paramHeatNo = new SqlParameter("pHeatNo", objProd.HeatNo);
                                    var paramSample1 = new SqlParameter("pSample1", objProd.Sample1);
                                    var paramSample2 = new SqlParameter("pSample2", objProd.Sample2);
                                    var paramSample3 = new SqlParameter("pSample3", objProd.Sample3);
                                    var paramSample4 = new SqlParameter("pSample4", objProd.Sample4);
                                    var paramSample5 = new SqlParameter("pSample5", objProd.Sample5);
                                    var paramSample6 = new SqlParameter("pSample6", objProd.Sample6);
                                    var paramRemarks = new SqlParameter("pRemarks", objProd.Remarks);
                                    var paramOther1 = new SqlParameter("pOther1", objProd.Other1);
                                    var paramOther2 = new SqlParameter("pOther2", objProd.Other2);
                                    var paramOther3 = new SqlParameter("pOther3", objProd.Other3);
                                    var paramOther4 = new SqlParameter("pOther4", objProd.Other4);

                                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_CASTINGHARDNESITEM_AddNew @pCasting_Id,@pItem,@pProductionDate, @pTotalProductionpcs,@pRequiredHardness, @pAvgCheckPoint,@pHeatNo,@pSample1,@pSample2,@pSample3,@pSample4,@pSample5,@pSample6,@pRemarks,@pOther1,@pOther2,@pOther3,@pOther4",
                                                ReturnValueParam1, paramID, ParamItem, paramProductionDate, paramTotalProductionpcs, paramRequiredHardness, paramAvgCheckPoint, paramHeatNo, paramSample1, paramSample2, paramSample3, paramSample4, paramSample5, paramSample6,paramRemarks,paramOther1,paramOther2, paramOther3, paramOther4);

                                    errorNo = Convert.ToInt32(ReturnValueParam1.Value);


                                    if (errorNo < 0)
                                    {
                                        dbContextTransaction.Rollback();
                                        GetErrorDescription(errorNo, ref errorDesc);
                                        break;
                                    }
                                }
                            }
                        }



                        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 CASTINGHARDNESItem_AddNew(int Casting_Id, string Item, string ProductionDate, string TotalProductionpcs, string RequiredHardness, string AvgCheckPoint, string HeatNo, string Sample1, string Sample2,
          string Sample3, string Sample4, string Sample5, string Sample6, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramCasting_Id = new SqlParameter("pCasting_Id", Casting_Id);


            var ParamItem = new SqlParameter("pItem", Item);
            var paramProductionDate = new SqlParameter("pProductionDate", ProductionDate);
            var paramTotalProductionpcs = new SqlParameter("pTotalProductionpcs", TotalProductionpcs);
            var paramRequiredHardness = new SqlParameter("pRequiredHardness", RequiredHardness);
            var paramAvgCheckPoint = new SqlParameter("pAvgCheckPoint", AvgCheckPoint);
            var paramHeatNo = new SqlParameter("pHeatNo", HeatNo);
            var paramSample1 = new SqlParameter("pSample1", Sample1);
            var paramSample2 = new SqlParameter("pSample2", Sample2);
            var paramSample3 = new SqlParameter("pSample3", Sample3);
            var paramSample4 = new SqlParameter("pSample4", Sample4);
            var paramSample5 = new SqlParameter("pSample5", Sample5);
            var paramSample6 = new SqlParameter("pSample6", Sample6);
            var dbContext = this.Database;
            using (var dbContextTransaction = dbContext.BeginTransaction())
            {
                int errorNo = 0;

                try
                {

                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_CASTINGHARDNESITEM_AddNew @pCasting_Id,@pItem,@pProductionDate, @pTotalProductionpcs,@pRequiredHardness, @pAvgCheckPoint,@pHeatNo,@pSample1,@pSample2,@pSample3,@pSample4,@pSample5,@pSample6",
                        ReturnValueParam, paramCasting_Id, ParamItem, paramProductionDate, paramTotalProductionpcs, paramRequiredHardness, paramAvgCheckPoint, paramHeatNo, paramSample1, paramSample2, paramSample3, paramSample4, paramSample5, paramSample6);

                    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 CASTINGHARDNESItem_edit(int CastingHardnessItem_ID, string Item, string ProductionDate, string TotalProductionpcs, string RequiredHardness, string AvgCheckPoint, string HeatNo, string Sample1, string Sample2,
       string Sample3, string Sample4, string Sample5, string Sample6,string Remarks ,ref string errorDesc)
        {
            errorDesc = "";

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

            var paramCastingHardnessItem_ID = new SqlParameter("pCastingHardnessItem_ID", CastingHardnessItem_ID);
            var ParamItem = new SqlParameter("pItem", Item);
            var paramProductionDate = new SqlParameter("pProductionDate", ProductionDate);
            var paramTotalProductionpcs = new SqlParameter("pTotalProductionpcs", TotalProductionpcs);
            var paramRequiredHardness = new SqlParameter("pRequiredHardness", RequiredHardness);
            var paramAvgCheckPoint = new SqlParameter("pAvgCheckPoint", AvgCheckPoint);
            var paramHeatNo = new SqlParameter("pHeatNo", HeatNo);
            var paramSample1 = new SqlParameter("pSample1", Sample1);
            var paramSample2 = new SqlParameter("pSample2", Sample2);
            var paramSample3 = new SqlParameter("pSample3", Sample3);
            var paramSample4 = new SqlParameter("pSample4", Sample4);
            var paramSample5 = new SqlParameter("pSample5", Sample5);
            var paramSample6 = new SqlParameter("pSample6", Sample6);
            var paramRemarks = new SqlParameter("pRemarks", Remarks);
          
            var dbContext = this.Database;
            using (var dbContextTransaction = dbContext.BeginTransaction())
            {
                int errorNo = 0;

                try
                {

                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_CASTINGHARDNESITEM_edit @pCastingHardnessItem_ID,@pItem,@pProductionDate, @pTotalProductionpcs,@pRequiredHardness, @pAvgCheckPoint,@pHeatNo,@pSample1,@pSample2,@pSample3,@pSample4,@pSample5,@pSample6,@pRemarks",
                        ReturnValueParam, paramCastingHardnessItem_ID, ParamItem, paramProductionDate, paramTotalProductionpcs, paramRequiredHardness, paramAvgCheckPoint, paramHeatNo, paramSample1, paramSample2, paramSample3, paramSample4, paramSample5, paramSample6, paramRemarks);

                    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<CASTINGHARDNES> CASTINGHARDNES_GetAll(int Casting_Id)
        {
            var paramCasting_Id = new SqlParameter("pCasting_Id", Casting_Id);



            return this.Database.SqlQuery<CASTINGHARDNES>(
                            "Sp_CASTINGHARDNES_GetAll @pCasting_Id",
                            paramCasting_Id).ToList();
        }

        public virtual List<CASTINGHARDNESItems> CASTINGHARDNESItems_GetAll(int CastingHardnessItem_ID, int Casting_Id)
        {
            var paramCastingHardnessItem_ID = new SqlParameter("pCastingHardnessItem_ID", CastingHardnessItem_ID);

            var paramCasting_Id = new SqlParameter("pCasting_Id", Casting_Id);

            return this.Database.SqlQuery<CASTINGHARDNESItems>(
                            "Sp_CASTINGHARDNESITEM_GetAll @pCastingHardnessItem_ID,@pCasting_Id",
                            paramCastingHardnessItem_ID, paramCasting_Id).ToList();
        }
    }
}