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

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

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<HardnessRecord>().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 Hardness_AddNew(string DocNo, int MetalType, DateTime Date, string TypeofTretmentHt, string TypeofTretmentCast,string MateriyalGrade,int DrawingNo,string LotQty,string Remarks, ref int HrId, 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 paramTypeofTretmentHt = new SqlParameter("pTypeofTretmentHt", TypeofTretmentHt);

            var paramTypeofTretmentCast = new SqlParameter("pTypeofTretmentCast", TypeofTretmentCast);

            var paramMateriyalGrade = new SqlParameter("pMateriyalGrade", MateriyalGrade);

            var paramDrawingNo = new SqlParameter("pDrawingNo", DrawingNo);

            var paramLotQty = new SqlParameter("pLotQty", LotQty);

            var paramRemarks = new SqlParameter("pRemarks", Remarks);

            var paramHrId = new SqlParameter("pHrId", System.Data.SqlDbType.Int);
            paramHrId.Direction = System.Data.ParameterDirection.Output;

            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_HardnessRecord_AddNew @pDocNo,@pMetalType,@pDate,@pTypeofTretmentHt,@pTypeofTretmentCast,@pMateriyalGrade,@pDrawingNo,@pLotQty,@pRemarks,@pHrId OUT",
                                                ReturnValueParam, paramDocNo, paramMetalType, paramDate, paramTypeofTretmentHt, paramTypeofTretmentCast, paramMateriyalGrade, paramDrawingNo, paramLotQty, paramRemarks, paramHrId);

                    errorNo = Convert.ToInt32(ReturnValueParam.Value);

                    HrId = Convert.ToInt32(paramHrId.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 HardnessLotQty_AddNew(string PerLotQty, string HardnesObtain, int HrId,  ref string errorDesc)
        {
            errorDesc = "";

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

            var paramPerLotQty = new SqlParameter("pPerLotQty", PerLotQty);
            var paramHardnesObtain = new SqlParameter("pHardnesObtain", HardnesObtain);
            var paramHrId = new SqlParameter("pHrId", HrId);
           
            
            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_HardnessLotQty_AddNew  @pPerLotQty,@pHardnesObtain,@pHrId",
                                                ReturnValueParam, paramPerLotQty, paramHardnesObtain, paramHrId);

                    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 HardnessLotQty_Edit(int HlId, string PerLotQty, string HardnesObtain, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramHlId = new SqlParameter("pHlId", HlId);
            var paramPerLotQty = new SqlParameter("pPerLotQty", PerLotQty);
            var paramHardnesObtain = new SqlParameter("pHardnesObtain", HardnesObtain);
         

            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_HardnessLotQty_Edit @pHlId,@pPerLotQty,@pHardnesObtain",
                                                ReturnValueParam, paramHlId,paramPerLotQty, paramHardnesObtain);

                    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<HardnessRecord> HardnessRecord_GetAll(int HrId)
        {
            var paramHrId = new SqlParameter("pHrId", HrId);

            return this.Database.SqlQuery<HardnessRecord>(
                            "Sp_HardnessRecord_Getall @pHrId",
                            paramHrId).ToList();
        }

        public virtual List<HardnessLotQty> HardnessLotQty_GetAll(int HlId,int HrId)
        {
            var paramHlId = new SqlParameter("pHlId", HlId);
            var paramHrId = new SqlParameter("pHrId", HrId);

            return this.Database.SqlQuery<HardnessLotQty>(
                            "Sp_HardnessLotQty_Getall @pHlId,@pHrId",
                           paramHlId,paramHrId).ToList();
        }


        public virtual int Hardness_EditNew(int HrId, string DocNo, int MetalType, DateTime Date, string TypeofTretmentHt, string TypeofTretmentCast, string MateriyalGrade, int DrawingNo, string LotQty, string Remarks, ref string errorDesc)
        {
            errorDesc = "";

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


            var paramHrId = new SqlParameter("pHrId", HrId);

            var paramDocNo = new SqlParameter("pDocNo", DocNo);


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

            var paramDate = new SqlParameter("pDate", Date);

            var paramTypeofTretmentHt = new SqlParameter("pTypeofTretmentHt", TypeofTretmentHt);

            var paramTypeofTretmentCast = new SqlParameter("pTypeofTretmentCast", TypeofTretmentCast);

            var paramMateriyalGrade = new SqlParameter("pMateriyalGrade", MateriyalGrade);

            var paramDrawingNo = new SqlParameter("pDrawingNo", DrawingNo);

            var paramLotQty = new SqlParameter("pLotQty", LotQty);

            var paramRemarks = new SqlParameter("pRemarks", Remarks);



            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_HardnessRecord_Edit @pHrId,@pDocNo,@pMetalType,@pDate,@pTypeofTretmentHt,@pTypeofTretmentCast,@pMateriyalGrade,@pDrawingNo,@pLotQty,@pRemarks",
                                                ReturnValueParam, paramHrId,paramDocNo, paramMetalType, paramDate, paramTypeofTretmentHt, paramTypeofTretmentCast, paramMateriyalGrade, paramDrawingNo, paramLotQty, 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;
            }
        }

    }
}