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 CncMachineDbContext: DbContext
    {

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

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

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<CncMachinProduction>().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 Cncmachine_AddNew(string DocNo, string MetalType, DateTime Date, int dayShift, int NightShift,  ref int Cmpid, int MachinMasterId, ref string errorDesc)
        {
            errorDesc = "";

            var ReturnValueParam = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int);
            ReturnValueParam.Direction = System.Data.ParameterDirection.Output;
            
         
            var paramDocNo = new SqlParameter("pDocNo", DocNo);
            var paramMetalType = new SqlParameter("pMetalType", MetalType);
            var paramDate = new SqlParameter("pDate", Date);
            var paramdayShift = new SqlParameter("pdayShift", dayShift);
            var paramNightShift = new SqlParameter("pNightShift", NightShift);
           
            var paramCmpid = new SqlParameter("pCmpid", System.Data.SqlDbType.Int);
            paramCmpid.Direction = System.Data.ParameterDirection.Output;
            var paramMachinMasterId = new SqlParameter("pMachinMasterId", MachinMasterId);

            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_CncMachine_AddNew  @pDocNo,@pMetalType,@pDate,@pdayShift,@pNightShift,@pCmpid OUT,@pMachinMasterId",
                                                ReturnValueParam, paramDocNo, paramMetalType, paramDate, paramdayShift, paramNightShift, paramCmpid,paramMachinMasterId);

                    errorNo = Convert.ToInt32(ReturnValueParam.Value);


                    Cmpid = Convert.ToInt32(paramCmpid.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 Cncmachineone_AddNew(int CncId, string TimeFromTo, int DrawingNo, string OperatorName, int JobTitle, string CycleTimeMachin, string CycleTimeClamp, string TotalTime, string TargetQuantity, string TargetActual, string Remarks, string RequiredDimensions, string RequiredDimensionstwo, string RequiredDimensionsthree, string ActualDimensions, string ReadingOpening, string ReadingClosing, string TotalReading,string TimeFromEnd,string CycleTotalTime,string RequiredDimensionsfour,string RequiredDimensionsfive,string ActualDimensionstwo, string ActualDimensionsthree, string ActualDimensionsfour, string ActualDimensionsfive, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramCncId = new SqlParameter("pCncId", CncId);
            var paramTimeFromTo = new SqlParameter("pTimeFromTo", TimeFromTo);
            var paramDrawingNo = new SqlParameter("pDrawingNo", DrawingNo);
            var paramOperatorName = new SqlParameter("pOperatorName", OperatorName);
            var paramJobTitle = new SqlParameter("pJobTitle", JobTitle);
            var paramCycleTimeMachin = new SqlParameter("pCycleTimeMachin", CycleTimeMachin);
            var paramCycleTimeClamp = new SqlParameter("pCycleTimeClamp", CycleTimeClamp);
            var paramTotalTimeDifferent = new SqlParameter("pTotalTime", TotalTime);
            var paramTargetQuantity = new SqlParameter("pTargetQuantity", TargetQuantity);
            var paramTargetActual = new SqlParameter("pTargetActual", TargetActual);
            var paramRemarks = new SqlParameter("pRemarks", Remarks);
            var paramRequiredDimensions = new SqlParameter("pRequiredDimensions", RequiredDimensions);
            var paramRequiredDimensionstwo = new SqlParameter("pRequiredDimensionstwo", RequiredDimensionstwo);
            var paramRequiredDimensionsthree = new SqlParameter("pRequiredDimensionsthree", RequiredDimensionsthree);
            var paramActualDimensions = new SqlParameter("pActualDimensions", ActualDimensions);
            var paramReadingOpening = new SqlParameter("pReadingOpening", ReadingOpening);
            var paramReadingClosing = new SqlParameter("pReadingClosing", ReadingClosing);
            var paramTotalReading = new SqlParameter("pTotalReading", TotalReading);
            var paramTimeFromEnd = new SqlParameter("pTimeFromEnd", TimeFromEnd);
            var paramCycleTotalTime = new SqlParameter("pCycleTotalTime", CycleTotalTime);

            var paramRequiredDimensionsfour = new SqlParameter("pRequiredDimensionsfour", RequiredDimensionsfour);
            var paramRequiredDimensionsfive = new SqlParameter("pRequiredDimensionsfive", RequiredDimensionsfive);
            var paramActualDimensionstwo = new SqlParameter("pActualDimensionstwo", ActualDimensionstwo);
            var paramActualDimensionsthree = new SqlParameter("pActualDimensionsthree", ActualDimensionsthree);
            var paramActualDimensionsfour = new SqlParameter("pActualDimensionsfour", ActualDimensionsfour);
            var paramActualDimensionsfive = new SqlParameter("pActualDimensionsfive", ActualDimensionsfive);

            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_CncMachineone_AddNew  @pTimeFromTo,@pDrawingNo,@pOperatorName,@pJobTitle,@pCycleTimeMachin,@pCycleTimeClamp,@pTotalTime,@pTargetQuantity,@pTargetActual,@pRemarks,@pRequiredDimensions,@pRequiredDimensionstwo,@pRequiredDimensionsthree,@pActualDimensions,@pReadingOpening,@pReadingClosing,@pTotalReading,@pCncId,@pTimeFromEnd,@pCycleTotalTime,@pRequiredDimensionsfour,@pRequiredDimensionsfive,@pActualDimensionstwo,@pActualDimensionsthree,@pActualDimensionsfour,@pActualDimensionsfive",
                                                ReturnValueParam, paramTimeFromTo, paramDrawingNo, paramOperatorName, paramJobTitle, paramCycleTimeMachin, paramCycleTimeClamp, paramTotalTimeDifferent, paramTargetQuantity, paramTargetActual, paramRemarks, paramRequiredDimensions, paramRequiredDimensionstwo, paramRequiredDimensionsthree, paramActualDimensions, paramReadingOpening, paramReadingClosing, paramTotalReading, paramCncId, paramTimeFromEnd, paramCycleTotalTime, paramRequiredDimensionsfour, paramRequiredDimensionsfive, paramActualDimensionstwo, paramActualDimensionsthree, paramActualDimensionsfour, paramActualDimensionsfive);

                    errorNo = Convert.ToInt32(ReturnValueParam.Value);

                    CncId = Convert.ToInt32(paramCncId.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 Cncmachinetwo_AddNew(int CncId, string ToolType, string InsertCorner, string Insert, string Make, string Reason,  ref string errorDesc)
        {
            errorDesc = "";

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


            var paramCncId = new SqlParameter("pCncId", CncId);
            var paramToolType = new SqlParameter("pToolType", ToolType);
            var paramInsertCorner = new SqlParameter("pInsertCorner", InsertCorner);
            var paramInsert = new SqlParameter("pInsert", Insert);
            var paramMake = new SqlParameter("pMake", Make);
            var paramReason = new SqlParameter("pReason", Reason);
         
          
            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_CncMachinetwo_AddNew  @pToolType,@pInsertCorner,@pInsert,@pMake,@pReason,@pCncId",
                                                ReturnValueParam, paramToolType, paramInsertCorner, paramInsert, paramMake, paramReason,paramCncId);

                    errorNo = Convert.ToInt32(ReturnValueParam.Value);

                    CncId = Convert.ToInt32(paramCncId.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<CncMachinProduction> CncMachinProduction_GetAll(int CncId)
        {
            var paramCncId = new SqlParameter("pCncId", CncId);

            return this.Database.SqlQuery<CncMachinProduction>(
                            "Sp_Sp_CncMachine_Getall @pCncId",
                            paramCncId).ToList();
        }

        public virtual List<CncMachinProductionOne> CncMachinProductionOne_GetAll(int CncId1,int CncId)
        {
            var paramCncId = new SqlParameter("pCncId", CncId);
            var paramCncId1 = new SqlParameter("pCncId1", CncId1);

            return this.Database.SqlQuery<CncMachinProductionOne>(
                            "Sp_CncMachineone_Getall @pCncId1,@pCncId",
                           paramCncId1,paramCncId).ToList();
        }

        public virtual List<CncMachinProductiontwo> CncMachinProductiontwo_GetAll(int CncId2, int CncId)
        {
            var paramCncId = new SqlParameter("pCncId", CncId);
            var paramCncId2 = new SqlParameter("pCncId2", CncId2);

            return this.Database.SqlQuery<CncMachinProductiontwo>(
                            "Sp_CncMachinetwo_Getall @pCncId2,@pCncId",
                            paramCncId2,paramCncId).ToList();
        }




        public virtual int Cncmachine_Edit(int CncId, string DocNo, string MetalType, DateTime Date, int dayShift, int NightShift,int MachinMasterId, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramCncId = new SqlParameter("pCncId", CncId);
            var paramDocNo = new SqlParameter("pDocNo", DocNo);
            var paramMetalType = new SqlParameter("pMetalType", MetalType);
            var paramDate = new SqlParameter("pDate", Date);
            var paramdayShift = new SqlParameter("pdayShift", dayShift);
            var paramNightShift = new SqlParameter("pNightShift", NightShift);
            var paramMachinMasterId = new SqlParameter("pMachinMasterId", MachinMasterId);



            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_CncMachine_Edit  @pCncId,@pDocNo,@pMetalType,@pDate,@pdayShift,@pNightShift,@pMachinMasterId",
                                                ReturnValueParam, paramCncId,paramDocNo, paramMetalType, paramDate, paramdayShift, paramNightShift, paramMachinMasterId);

                    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 Cncmachineone_Edit(int CncId1, string TimeFromTo, int DrawingNo, string OperatorName, int JobTitle, string CycleTimeMachin, string CycleTimeClamp, string TotalTime, string TargetQuantity, string TargetActual, string Remarks, string RequiredDimensions,string RequiredDimensionstwo, string RequiredDimensionsthree, string ActualDimensions, string ReadingOpening, string ReadingClosing, string TotalReading,string TimeFromEnd,string CycleTotalTime,string RequiredDimensionsfour,string RequiredDimensionsfive,string ActualDimensionstwo,string ActualDimensionsthree,string ActualDimensionsfour,string ActualDimensionsfive, ref string errorDesc)
        {
            errorDesc = "";

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


            var paramCncId1 = new SqlParameter("pCncId1", CncId1);
            var paramTimeFromTo = new SqlParameter("pTimeFromTo", TimeFromTo);
            var paramDrawingNo = new SqlParameter("pDrawingNo", DrawingNo);
            var paramOperatorName = new SqlParameter("pOperatorName", OperatorName);
            var paramJobTitle = new SqlParameter("pJobTitle", JobTitle);
            var paramCycleTimeMachin = new SqlParameter("pCycleTimeMachin", CycleTimeMachin);
            var paramCycleTimeClamp = new SqlParameter("pCycleTimeClamp", CycleTimeClamp);
            var paramTotalTime = new SqlParameter("pTotalTime", TotalTime);
            var paramTargetQuantity = new SqlParameter("pTargetQuantity", TargetQuantity);
            var paramTargetActual = new SqlParameter("pTargetActual", TargetActual);
            var paramRemarks = new SqlParameter("pRemarks", Remarks);
            var paramRequiredDimensions = new SqlParameter("pRequiredDimensions", RequiredDimensions);
            var paramRequiredDimensionstwo = new SqlParameter("pRequiredDimensionstwo", RequiredDimensionstwo);
            var paramRequiredDimensionsthree = new SqlParameter("pRequiredDimensionsthree", RequiredDimensionsthree);
            var paramActualDimensions = new SqlParameter("pActualDimensions", ActualDimensions);
            var paramReadingOpening = new SqlParameter("pReadingOpening", ReadingOpening);
            var paramReadingClosing = new SqlParameter("pReadingClosing", ReadingClosing);
            var paramTotalReading = new SqlParameter("pTotalReading", TotalReading);
            var paramTimeFromEnd = new SqlParameter("pTimeFromEnd", TimeFromEnd);
            var paramCycleTotalTime = new SqlParameter("pCycleTotalTime", CycleTotalTime);

            var paramRequiredDimensionsfour = new SqlParameter("pRequiredDimensionsfour", RequiredDimensionsfour);
            var paramRequiredDimensionsfive = new SqlParameter("pRequiredDimensionsfive", RequiredDimensionsfive);
            var paramActualDimensionstwo = new SqlParameter("pActualDimensionstwo", ActualDimensionstwo);
            var paramActualDimensionsthree = new SqlParameter("pActualDimensionsthree", ActualDimensionsthree);
            var paramActualDimensionsfour = new SqlParameter("pActualDimensionsfour", ActualDimensionsfour);
            var paramActualDimensionsfive = new SqlParameter("pActualDimensionsfive", ActualDimensionsfive);


            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_CncMachineone_Edit  @pCncId1,@pTimeFromTo,@pDrawingNo,@pOperatorName,@pJobTitle,@pCycleTimeMachin,@pCycleTimeClamp,@pTotalTime,@pTargetQuantity,@pTargetActual,@pRemarks,@pRequiredDimensions,@pRequiredDimensionstwo,@pRequiredDimensionsthree,@pActualDimensions,@pReadingOpening,@pReadingClosing,@pTotalReading,@pTimeFromEnd,@pCycleTotalTime,@pRequiredDimensionsfour,@pRequiredDimensionsfive,@pActualDimensionstwo,@pActualDimensionsthree,@pActualDimensionsfour,@pActualDimensionsfive",
                                                ReturnValueParam, paramCncId1, paramTimeFromTo, paramDrawingNo, paramOperatorName, paramJobTitle, paramCycleTimeMachin, paramCycleTimeClamp, paramTotalTime, paramTargetQuantity, paramTargetActual, paramRemarks, paramRequiredDimensions, paramRequiredDimensionstwo, paramRequiredDimensionsthree, paramActualDimensions, paramReadingOpening, paramReadingClosing, paramTotalReading, paramTimeFromEnd, paramCycleTotalTime, paramRequiredDimensionsfour, paramRequiredDimensionsfive, paramActualDimensionstwo, paramActualDimensionsthree, paramActualDimensionsfour, paramActualDimensionsfive);

                    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 Cncmachinetwo_Edit(int CncId2, string ToolType, string InsertCorner, string Insert, string Make, string Reason, ref string errorDesc)
        {
            errorDesc = "";

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




            var paramCncId2 = new SqlParameter("pCncId2", CncId2);
            var paramToolType = new SqlParameter("pToolType", ToolType);
            var paramInsertCorner = new SqlParameter("pInsertCorner", InsertCorner);
            var paramInsert = new SqlParameter("pInsert", Insert);
            var paramMake = new SqlParameter("pMake", Make);
            var paramReason = new SqlParameter("pReason", Reason);


            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_CncMachinetwo_Edit  @pCncId2,@pToolType,@pInsertCorner,@pInsert,@pMake,@pReason",
                                                ReturnValueParam, paramCncId2,paramToolType, paramInsertCorner, paramInsert, paramMake, paramReason);

                    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;
            }
        }

    }
}