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

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

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<OrderMaster>().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 OrderMaster_AddNew(string CustomerName, string MobileNo, string EmailId, string EmailId2, string NameOfProcess, string Unit, string Shift, DateTime PaningFromDate,DateTime PaningToDate, string RNGNo, string CustomerOrderNo, string Remark,string UserId,string Status, string FinalYear,string Active, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramCustomerName = new SqlParameter("pCustomerName", CustomerName);
            var paramMobileNo = new SqlParameter("pMobileNo", MobileNo);
            var paramEmailId = new SqlParameter("pEmailId", EmailId);
            var paramEmailId2 = new SqlParameter("pEmailId2", EmailId2);
            var paramNameOfProcess = new SqlParameter("pNameOfProcess", NameOfProcess);
            var paramUnit = new SqlParameter("pUnit", Unit);
            var paramShift = new SqlParameter("pShift", Shift);
            var paramPaningFromDate = new SqlParameter("pPaningFromDate", PaningFromDate);
            var paramPaningToDate = new SqlParameter("pPaningToDate", PaningToDate);
            var paramRNGNo = new SqlParameter("pRNGNo", RNGNo);
            var paramCustomerOrderNo = new SqlParameter("pCustomerOrderNo", CustomerOrderNo);
            var paramRemark = new SqlParameter("pRemark", Remark);
            var paramUserId = new SqlParameter("pUserId", UserId);
            var paramStatus = new SqlParameter("pStatus", Status);
            var paramFinalYear = new SqlParameter("pFinalYear", FinalYear);
            var paramActive = new SqlParameter("pActive", Active);
          


            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = spOrderMaster_AddNew @pCustomerName,@pMobileNo,@pEmailId,@pEmailId2,@pNameOfProcess,@pUnit,@pShift,@pPaningFromDate,@pPaningToDate,@pRNGNo,@pCustomerOrderNo,@pRemark,@pUserId,@pStatus,@pFinalYear,@pActive",
                                                ReturnValueParam,paramCustomerName, paramMobileNo, paramEmailId, paramEmailId2, paramNameOfProcess, paramUnit, paramShift, paramPaningFromDate,paramPaningToDate, paramRNGNo, paramCustomerOrderNo, paramRemark, paramUserId, paramStatus, paramFinalYear, paramActive);

                    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 OrderMaster_Edit(int Order_Id, string CustomerName, string MobileNo, string EmailId, string EmailId2, string NameOfProcess, string Unit, string Shift, DateTime PaningFromDate,DateTime PaningToDate, string RNGNo, string CustomerOrderNo, string Remark, string UserId, string Status, string FinalYear, string Active,ref string errorDesc)
        {
            errorDesc = "";

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

            var paramOrder_Id = new SqlParameter("pOrder_Id", Order_Id);
            var paramCustomerName = new SqlParameter("pCustomerName", CustomerName);
            var paramMobileNo = new SqlParameter("pMobileNo", MobileNo);
            var paramEmailId = new SqlParameter("pEmailId", EmailId);
            var paramEmailId2 = new SqlParameter("pEmailId2", EmailId2);
            var paramNameOfProcess = new SqlParameter("pNameOfProcess", NameOfProcess);
            var paramUnit = new SqlParameter("pUnit", Unit);
            var paramShift = new SqlParameter("pShift", Shift);
            var paramPaningFromDate = new SqlParameter("pPaningFromDate", PaningFromDate);
            var paramPaningToDate = new SqlParameter("pPaningToDate", PaningToDate);
            var paramRNGNo = new SqlParameter("pRNGNo", RNGNo);
            var paramCustomerOrderNo = new SqlParameter("pCustomerOrderNo", CustomerOrderNo);
            var paramRemark = new SqlParameter("pRemark", Remark);
            var paramUserId = new SqlParameter("pUserId", UserId);
            var paramStatus = new SqlParameter("pStatus", Status);
            var paramFinalYear = new SqlParameter("pFinalYear", FinalYear);
            var paramActive = new SqlParameter("pActive", Active);
           


            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = spOrderMaster_Edit @pOrder_Id,@pCustomerName,@pMobileNo,@pEmailId,@pEmailId2,@pNameOfProcess,@pUnit,@pShift,@pPaningFromDate,@pPaningToDate,@pRNGNo,@pCustomerOrderNo,@pRemark,@pUserId,@pStatus,@pFinalYear,@pActive",
                                                ReturnValueParam,paramOrder_Id,paramCustomerName, paramMobileNo, paramEmailId, paramEmailId2, paramNameOfProcess, paramUnit, paramShift, paramPaningFromDate,paramPaningToDate, paramRNGNo, paramCustomerOrderNo, paramRemark, paramUserId, paramStatus, paramFinalYear, paramActive);

                    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<OrderMaster> OrderMaster_GetAll(string RNGNo)
        {
            var paramRNGNo = new SqlParameter("pRNGNo", RNGNo);



            return this.Database.SqlQuery<OrderMaster>(
                            "spOrderMaster_GetAll @pRNGNo",
                            paramRNGNo).ToList();
        }

    }
}