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

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

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

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Galvaniz>().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 Galvaniz_AddNew(string DocNo, int MetalType, DateTime Date, string furancestarttime, string furancemelttime, string stoptime, string tempwhenstartrts, string tempwhenmelt, string tempwhenstops, string energymtroread, string energymtrmeltread, string cread, ref int GalvanizId, 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 paramfurancestarttime = new SqlParameter("pfurancestarttime", furancestarttime);
            var paramfurancemelttime = new SqlParameter("pfurancemelttime", furancemelttime);
            var paramstoptime = new SqlParameter("pstoptime", stoptime);
            var paramtempwhenstartrts = new SqlParameter("ptempwhenstartrts", tempwhenstartrts);
            var paramtempwhenmelt = new SqlParameter("ptempwhenmelt", tempwhenmelt);

            var paramtempwhenstops = new SqlParameter("ptempwhenstops", tempwhenstops);
            var paramenergymtroread = new SqlParameter("penergymtroread", energymtroread);
            var paramenergymtrmeltread = new SqlParameter("penergymtrmeltread", energymtrmeltread);
            var paramcread = new SqlParameter("pcread", cread);


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


            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_Galvanizshop_AddNew   @pDocNo,@pMetalType,@pDate,@pfurancestarttime,@pfurancemelttime,@pstoptime,@ptempwhenstartrts,@ptempwhenmelt,@ptempwhenstops,@penergymtroread,@penergymtrmeltread,@pcread,@pGalvanizId OUT",
                                                ReturnValueParam, paramDocNo, paramMetalType, paramDate, paramfurancestarttime, paramfurancemelttime, paramstoptime, paramtempwhenstartrts, paramtempwhenmelt, paramtempwhenstops, paramenergymtroread, paramenergymtrmeltread, paramcread, paramGalvanizId);

                    errorNo = Convert.ToInt32(ReturnValueParam.Value);


                    GalvanizId = Convert.ToInt32(paramGalvanizId.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 Galvaniz_Edit(int GalvanizId, string DocNo, int MetalType, DateTime Date, string furancestarttime, string furancemelttime, string stoptime, string tempwhenstartrts, string tempwhenmelt, string tempwhenstops, string energymtroread, string energymtrmeltread, string cread, ref string errorDesc)
        {
            errorDesc = "";

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



            var paramGalvanizId = new SqlParameter("pGalvanizId", GalvanizId);
            var paramDocNo = new SqlParameter("pDocNo", DocNo);
            var paramMetalType = new SqlParameter("pMetalType", MetalType);
            var paramDate = new SqlParameter("pDate", Date);

            var paramfurancestarttime = new SqlParameter("pfurancestarttime", furancestarttime);
            var paramfurancemelttime = new SqlParameter("pfurancemelttime", furancemelttime);
            var paramstoptime = new SqlParameter("pstoptime", stoptime);
            var paramtempwhenstartrts = new SqlParameter("ptempwhenstartrts", tempwhenstartrts);
            var paramtempwhenmelt = new SqlParameter("ptempwhenmelt", tempwhenmelt);

            var paramtempwhenstops = new SqlParameter("ptempwhenstops", tempwhenstops);
            var paramenergymtroread = new SqlParameter("penergymtroread", energymtroread);
            var paramenergymtrmeltread = new SqlParameter("penergymtrmeltread", energymtrmeltread);
            var paramcread = new SqlParameter("pcread", cread);




            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_Galvanizshop_Edit  @pGalvanizId,@pDocNo,@pMetalType,@pDate,@pfurancestarttime,@pfurancemelttime,@pstoptime,@ptempwhenstartrts,@ptempwhenmelt,@ptempwhenstops,@penergymtroread,@penergymtrmeltread,@pcread",
                                                ReturnValueParam, paramGalvanizId, paramDocNo, paramMetalType, paramDate, paramfurancestarttime, paramfurancemelttime, paramstoptime, paramtempwhenstartrts, paramtempwhenmelt, paramtempwhenstops, paramenergymtroread, paramenergymtrmeltread, paramcread);

                    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<Galvaniz> Galvaniz_GetAll(int GalvanizId)
        {
            var paramGalvanizId = new SqlParameter("pGalvanizId", GalvanizId);


            return this.Database.SqlQuery<Galvaniz>(
                            "Sp_Galvaniz_Getall @pGalvanizId",
                            paramGalvanizId).ToList();
        }

        








        public virtual int Galvanizproduction_AddNew(int DrawingNo, string Qty, string mcweight, string glweight, string coatingthickness, int GalvanizId,  ref string errorDesc)
        {
            errorDesc = "";

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

           


            var paramDrawingNo = new SqlParameter("pDrawingNo", DrawingNo);
            var paramQty = new SqlParameter("pQty", Qty);
            var parammcweight = new SqlParameter("pmcweight", mcweight);

            var paramglweight = new SqlParameter("pglweight", glweight);
            var paramcoatingthickness = new SqlParameter("pcoatingthickness", coatingthickness);
            var paramGalvanizId = new SqlParameter("pGalvanizId", GalvanizId);
            
            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_Galvanizproduction_AddNew   @pDrawingNo,@pQty,@pmcweight,@pglweight,@pcoatingthickness,@pGalvanizId",
                                                ReturnValueParam, paramDrawingNo, paramQty, parammcweight, paramglweight, paramcoatingthickness, paramGalvanizId);

                    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 Galvanizproduction_Edit(int GalvanizproductId, int DrawingNo, string Qty, string mcweight, string glweight, string coatingthickness, ref string errorDesc)
        {
            errorDesc = "";

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



            var paramGalvanizproductId = new SqlParameter("pGalvanizproductId", GalvanizproductId);
            var paramDrawingNo = new SqlParameter("pDrawingNo", DrawingNo);
            var paramQty = new SqlParameter("pQty", Qty);
            var parammcweight = new SqlParameter("pmcweight", mcweight);

            var paramglweight = new SqlParameter("pglweight", glweight);
            var paramcoatingthickness = new SqlParameter("pcoatingthickness", coatingthickness);
          

            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_Galvanizproduction_Edit   @pGalvanizproductId,@pDrawingNo,@pQty,@pmcweight,@pglweight,@pcoatingthickness",
                                                ReturnValueParam, paramGalvanizproductId, paramDrawingNo, paramQty, parammcweight, paramglweight, paramcoatingthickness);

                    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<production> production_GetAll(int GalvanizproductId, int GalvanizId)
        {
            var paramGalvanizproductId = new SqlParameter("pGalvanizproductId", GalvanizproductId);
            var paramGalvanizId = new SqlParameter("pGalvanizId", GalvanizId);


            return this.Database.SqlQuery<production>(
                            "Sp_Galvanizproduction_Getall @pGalvanizproductId,@pGalvanizId",
                            paramGalvanizproductId, paramGalvanizId).ToList();
        }

        








        public virtual int Galvanizbreckdown_AddNew(int GalvanizId, string Starttime, string StopTime, string Reason, string partchange, ref string errorDesc)
        {
            errorDesc = "";

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


           

              var paramGalvanizId = new SqlParameter("pGalvanizId", GalvanizId);
            var paramStarttime = new SqlParameter("pStarttime", Starttime);
            var parammcStopTime = new SqlParameter("pStopTime", StopTime);

            var paramglReason = new SqlParameter("pReason", Reason);
            var parampartchange = new SqlParameter("ppartchange", partchange);
           
            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_Galvanizbreckdown_AddNew @pGalvanizId,@pStarttime,@pStopTime,@pReason,@ppartchange",
                                                ReturnValueParam, paramGalvanizId, paramStarttime, parammcStopTime, paramglReason, parampartchange);

                    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 Galvanizbreckdown_Edit(int GalvanizbreakkId, string Starttime, string StopTime, string Reason, string partchange, ref string errorDesc)
        {
            errorDesc = "";

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




            var paramGalvanizbreakkId = new SqlParameter("pGalvanizbreakkId", GalvanizbreakkId);
            var paramStarttime = new SqlParameter("pStarttime", Starttime);
            var parammcStopTime = new SqlParameter("pStopTime", StopTime);

            var paramglReason = new SqlParameter("pReason", Reason);
            var parampartchange = new SqlParameter("ppartchange", partchange);

            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_Galvanizbreckdown_edit @pGalvanizbreakkId,@pStarttime,@pStopTime,@pReason,@ppartchange",
                                                ReturnValueParam, paramGalvanizbreakkId, paramStarttime, parammcStopTime, paramglReason, parampartchange);

                    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<breckdown> breckdown_GetAll(int GalvanizbreakkId, int GalvanizId)
        {
            var paramGalvanizbreakkId = new SqlParameter("pGalvanizbreakkId", GalvanizbreakkId);
            var paramGalvanizId = new SqlParameter("pGalvanizId", GalvanizId);


            return this.Database.SqlQuery<breckdown>(
                            "Sp_Galvanizbreckdown_Getall @pGalvanizbreakkId,@pGalvanizId",
                            paramGalvanizbreakkId, paramGalvanizId).ToList();
        }



        






        public virtual int materialstock_AddNew(int GalvanizId, string Itemname, string received, string issue, ref string errorDesc)
        {
            errorDesc = "";

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

          


            var paramGalvanizId = new SqlParameter("pGalvanizId", GalvanizId);
            var paramItemname = new SqlParameter("pItemname", Itemname);
            var paramreceived = new SqlParameter("preceived", received);

            var paramglissue = new SqlParameter("pissue", issue);
        

            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_Galvanizshopmateriyalstock_AddNew   @pGalvanizId,@pItemname,@preceived,@pissue",
                                                ReturnValueParam, paramGalvanizId, paramItemname, paramreceived, paramglissue);

                    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 materialstock_Edit(int GalvanizMaterialId, string Itemname, string received, string issue, ref string errorDesc)
        {
            errorDesc = "";

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

            var paramGalvanizMaterialId = new SqlParameter("pGalvanizMaterialId", GalvanizMaterialId);
            var paramItemname = new SqlParameter("pItemname", Itemname);
            var paramreceived = new SqlParameter("preceived", received);
            var paramglissue = new SqlParameter("pissue", issue);


            var dbContext = this.Database;

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

                try
                {
                    dbContext.ExecuteSqlCommand("Exec @ReturnValue = Sp_Galvanizshopmateriyalstock_edit  @pGalvanizMaterialId,@pItemname,@preceived,@pissue",
                                                ReturnValueParam, paramGalvanizMaterialId, paramItemname, paramreceived, paramglissue);

                    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<materiyalstock> materiyalstock_GetAll(int GalvanizMaterialId, int GalvanizId)
        {
            var paramGalvanizMaterialId = new SqlParameter("pGalvanizMaterialId", GalvanizMaterialId);
            var paramGalvanizId = new SqlParameter("pGalvanizId", GalvanizId);


            return this.Database.SqlQuery<materiyalstock>(
                            "Sp_Galvanizshopmateriyalstock_Getall @pGalvanizMaterialId,@pGalvanizId",
                            paramGalvanizMaterialId, paramGalvanizId).ToList();
        }



    }
}