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

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

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<MaterailPurchaseInward>().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 MaterailPurchaseInward_AddNew(DateTime Date, string VendorName, string UnitName, string Item,string Qty, string Rate,string Amount,string Remarks,string IsActive,string UserID,string FinalYear,string Status, 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 paramVendorName = new SqlParameter("pVendorName", VendorName);
            var paramUnitName = new SqlParameter("pUnitName", UnitName);
            var paramItem = new SqlParameter("pItem", Item);
            var paramQty = new SqlParameter("pQty", Qty);
            var paramRate = new SqlParameter("pRate", Rate);
            var paramAmount = new SqlParameter("pAmount", Amount);
            var paramRemarks = new SqlParameter("pRemarks", Remarks);
            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_MaterailPurchaseInward_AddNew @pDate,@pVendorName,@pUnitName,@pItem,@pQty,@pRate,@pAmount,@pRemarks,@pIsActive,@pUserID,@pFinalYear,@pStatus",
                                                ReturnValueParam, paramDate, paramVendorName, paramUnitName, paramItem,paramQty,paramRate, paramAmount, paramRemarks, paramIsActive, paramUserID, paramFinalYear, paramStatus);

                    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 MaterailPurchaseInward_Edit(int MaterialPurchaseInword_ID, DateTime Date, string VendorName, string UnitName, string Item,string Qty, string Rate, string Amount, string Remarks, string IsActive, string UserID, string FinalYear, string Status, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramMaterialPurchaseInword_ID = new SqlParameter("pMaterialPurchaseInword_ID", MaterialPurchaseInword_ID);
            var paramDate = new SqlParameter("pDate", Date);
            var paramVendorName = new SqlParameter("pVendorName", VendorName);
            var paramUnitName = new SqlParameter("pUnitName", UnitName);
            var paramItem = new SqlParameter("pItem", Item);
            var paramQty = new SqlParameter("pQty", Qty);
            var paramRate = new SqlParameter("pRate", Rate);
            var paramAmount = new SqlParameter("pAmount", Amount);
            var paramRemarks = new SqlParameter("pRemarks", Remarks);
            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_MaterailPurchaseInward_Edit @pMaterialPurchaseInword_ID,@pDate,@pVendorName,@pUnitName,@pItem,@pQty,@pRate,@pAmount,@pRemarks,@pIsActive,@pUserID,@pFinalYear,@pStatus",
                                                ReturnValueParam, paramMaterialPurchaseInword_ID, paramDate, paramVendorName, paramUnitName, paramItem,paramQty,paramRate, paramAmount, paramRemarks, paramIsActive, paramUserID, paramFinalYear, paramStatus);

                    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<MaterailPurchaseInward> MaterailPurchaseInward_GetAll(int MaterialPurchaseInword_ID)
        {
            var paramMaterialPurchaseInword_ID = new SqlParameter("pMaterialPurchaseInword_ID", MaterialPurchaseInword_ID);
      


            return this.Database.SqlQuery<MaterailPurchaseInward>(
                            "Sp_MaterailPurchaseInward_GetAll @pMaterialPurchaseInword_ID",
                             paramMaterialPurchaseInword_ID).ToList();
        }

    }
}