using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Xml.Serialization;
using System.Xml;
using FIPHARMACY.Models;
using System.Web.Mvc;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using FIPHARMACY.EF;


namespace FIPHARMACY.DAL
{
    public class DataLayer : DbContext
    {
        public DataLayer()
        {
            Database.SetInitializer<DataLayer>(null);
            var objectContext = (this as IObjectContextAdapter).ObjectContext;
            objectContext.CommandTimeout = 120;
        }

        public virtual IEnumerable<M_ItemType> selectItemType()
        {
            var sqlParams = new SqlParameter[] {
            //new SqlParameter { ParameterName = "@ProcId", Value = ProcId }
            };
            //var sqlquery = @"sp_getGapAllowCourse @ProcId";
            var sqlquery = @"sp_selectItemType";
            var sList = this.Database.SqlQuery<M_ItemType>(sqlquery, sqlParams);
            return sList;
        }

        public IEnumerable<Modules> InsertModuleEntry(Modules objMod, int userName)
        {
            var sqlParms = new SqlParameter[]{
             new SqlParameter{ParameterName="@userId",Value=userName},
             new SqlParameter{ParameterName="@moduleName",Value=objMod.MTitle},
                new SqlParameter{ParameterName="@procId",Value=1},
             };
            var sqlQaury = @"sp_ad_ModuleEntry @userId,@moduleName,@procId";

            var IList = this.Database.SqlQuery<Modules>(sqlQaury, sqlParms).ToList();
            return IList;
        }

        public virtual IEnumerable<Modules> GetModules()
        {
            var sqlParams = new SqlParameter[] {
            //new SqlParameter { ParameterName = "@ProcId", Value = ProcId }
            };
            var sqlquery = @"sp_ad_selectModules";
            var sList = this.Database.SqlQuery<Modules>(sqlquery, sqlParams);
            return sList;
        }

        public virtual IEnumerable<CourseTypeMaster> getCourseType()
        {
            var sqlParams = new SqlParameter[] {
            //new SqlParameter { ParameterName = "@ProcId", Value = ProcId }
            };
            var sqlquery = @"sp_ad_selectCourseType";
            var sList = this.Database.SqlQuery<CourseTypeMaster>(sqlquery, sqlParams);
            return sList;
        }

        public List<CourseTypeMaster> getCourseTypeList()
        {
            var sqlParams = new SqlParameter[] {
            //new SqlParameter { ParameterName = "@ProcId", Value = ProcId }
            };
            var sqlquery = @"sp_ad_selectCourseType";
            List<CourseTypeMaster> sList = this.Database.SqlQuery<CourseTypeMaster>(sqlquery, sqlParams).ToList();
            return sList;
        }



        public virtual List<CourseCategory> getCourseCategory()
        {
            var sqlParams = new SqlParameter[] {
            //new SqlParameter { ParameterName = "@ProcId", Value = ProcId }
            };
            var sqlquery = @"sp_ad_selectCourseCategory";
            var sList = this.Database.SqlQuery<CourseCategory>(sqlquery, sqlParams).ToList();
            return sList;
        }

        public virtual IEnumerable<CourseEligibility> getCourseEligibility()
        {
            var sqlParams = new SqlParameter[] {
            //new SqlParameter { ParameterName = "@ProcId", Value = ProcId }
            };
            var sqlquery = @"sp_ad_selectCourseeligibility";
            var sList = this.Database.SqlQuery<CourseEligibility>(sqlquery, sqlParams);
            return sList;
        }
        public List<CourseMaster> getCourseMaster()
        {
            var sqlParams = new SqlParameter[] {
            //new SqlParameter { ParameterName = "@ProcId", Value = ProcId }
            };
            var sqlquery = @"sp_ad_selectCourse ";
            List<CourseMaster> sList = this.Database.SqlQuery<CourseMaster>(sqlquery, sqlParams).ToList();
            return sList;
        }
        public List<ExamMaster> getExam()
        {
            var sqlParams = new SqlParameter[] {
            //new SqlParameter { ParameterName = "@ProcId", Value = ProcId }
            };
            var sqlquery = @"sp_ad_selectExam";
            var sList = this.Database.SqlQuery<ExamMaster>(sqlquery, sqlParams).ToList();
            return sList;
        }
        public virtual IEnumerable<M_menu> GetMenu(int ModuleId)
        {
            var sqlParams = new SqlParameter[] {
                new SqlParameter{ParameterName="@ModuleId",Value=ModuleId},
            };
            var sqlquery = @"sp_ad_selectMenu @ModuleId";
            var sList = this.Database.SqlQuery<M_menu>(sqlquery, sqlParams);
            return sList;
        }

        public virtual IEnumerable<M_page> GetPage(int ModuleId, int MenuId)
        {
            var sqlParams = new SqlParameter[] {
                new SqlParameter{ParameterName="@ModuleId",Value=ModuleId},
                new SqlParameter{ParameterName="@MenuId",Value=MenuId},
            };
            var sqlquery = @"sp_ad_selectPage @ModuleId,@MenuId";
            var sList = this.Database.SqlQuery<M_page>(sqlquery, sqlParams);
            return sList;
        }

        public virtual IEnumerable<M_Login> GetUser()
        {
            var sqlParams = new SqlParameter[] {
            };
            var sqlquery = @"sp_ad_selectUser";
            var sList = this.Database.SqlQuery<M_Login>(sqlquery, sqlParams);
            return sList;
        }

        public virtual IEnumerable<CreateUser> LoginToCreate()
        {
            var sqlParams = new SqlParameter[] {
            };
            var sqlquery = @"sp_ad_LoginToCreate";
            var sList = this.Database.SqlQuery<CreateUser>(sqlquery, sqlParams);
            return sList;
        }

        public IEnumerable<M_menu> InsertMenu(M_menu objMod, int userName)
        {
            var sqlParms = new SqlParameter[]{
             new SqlParameter{ParameterName="@userId",Value=userName},
             new SqlParameter{ParameterName="@moduleId",Value=objMod.ModuleId},
             new SqlParameter{ParameterName="@menuName",Value=objMod.MenuName},
                new SqlParameter{ParameterName="@procId",Value=1},
             };
            var sqlQaury = @"sp_ad_MenuEntry @userId,@moduleId,@menuName,@procId";
            var IList = this.Database.SqlQuery<M_menu>(sqlQaury, sqlParms).ToList();
            return IList;
        }

        public IEnumerable<UserPermission> InsertUserPermission(UserPermission objUser, int userName)
        {
            var sqlParms = new SqlParameter[]{
             new SqlParameter{ParameterName="@UserId",Value=objUser.UserId},
             new SqlParameter{ParameterName="@ModuleId",Value=objUser.ModuleId},
             new SqlParameter{ParameterName="@MenuId",Value=objUser.MenuId},
            new SqlParameter{ParameterName="@PageId",Value=objUser.PageId},
            new SqlParameter{ParameterName="@FDate",Value=objUser.FDate},
            new SqlParameter{ParameterName="@Tdate",Value=objUser.Tdate},
            new SqlParameter{ParameterName="@ProcId",Value=userName},
             };
            var sqlQaury = @"sp_ad_userEntry @UserId,@ModuleId,@MenuId,@PageId,@FDate,@Tdate,@procId";
            var IList = this.Database.SqlQuery<UserPermission>(sqlQaury, sqlParms).ToList();
            return IList;
        }

        public IEnumerable<CreateUser> InsertCreateUser(CreateUser objUser, int userName)
        {
            var sqlParms = new SqlParameter[]{
             new SqlParameter{ParameterName="@UserId",Value=objUser.EmpId},
            new SqlParameter{ParameterName="@ProcId",Value=userName},
             };
            var sqlQaury = @"sp_ad_CreateUser @UserId,@procId";
            var IList = this.Database.SqlQuery<CreateUser>(sqlQaury, sqlParms).ToList();
            return IList;
        }

        public IEnumerable<M_page> InsertPage(M_page objMod, int userName)
        {
            var sqlParms = new SqlParameter[]{
             new SqlParameter{ParameterName="@userId",Value=userName},
             new SqlParameter{ParameterName="@moduleId",Value=objMod.ModuleId},
             new SqlParameter{ParameterName="@MenuId",Value=objMod.MenuId},
             new SqlParameter{ParameterName="@PageName",Value=objMod.PageName},
             new SqlParameter{ParameterName="@PageURL",Value=objMod.PageURL},
                new SqlParameter{ParameterName="@procId",Value=1},
             };
            var sqlQaury = @"sp_ad_PageEntry @userId,@moduleId,@MenuId,@PageName,@PageURL,@procId";
            var IList = this.Database.SqlQuery<M_page>(sqlQaury, sqlParms).ToList();
            return IList;
        }

        public Int32 InsertCourseCategory(CourseCategory objCourseCategory, int userName)
        {
            var sqlParms = new SqlParameter[]{
             new SqlParameter{ParameterName="@CCatName",Value=objCourseCategory.CCatName},
             new SqlParameter{ParameterName="@CCatDesc",Value=objCourseCategory.CCatDesc},
             new SqlParameter{ParameterName="@procId",Value=1},
             };
            var sqlQaury = @"sp_ad_CourseCategory @CCatName,@CCatDesc,@procId";
            int Status = this.Database.ExecuteSqlCommand(sqlQaury, sqlParms);
            return Status;
        }

        public Int32 InsertCourseTypeMaster(CourseTypeMaster objCourseTypeMaster, int userName)
        {
            var sqlParms = new SqlParameter[]{
             new SqlParameter{ParameterName="@CType",Value=objCourseTypeMaster.CType},
             new SqlParameter{ParameterName="@CTypeDesc",Value=objCourseTypeMaster.CTypeDesc},
             new SqlParameter{ParameterName="@procId",Value=1},
             };
            var sqlQaury = @"sp_ad_CourseTypeMaster @CType,@CTypeDesc,@procId";
            int Status = this.Database.ExecuteSqlCommand(sqlQaury, sqlParms);
            return Status;
        }

        public Int32 InsertCourseMaster(CourseMaster objCourseMaster, int userName)
        {
            var sqlParms = new SqlParameter[]{
             new SqlParameter{ParameterName="@CourseName",Value=objCourseMaster.CourseName},
             new SqlParameter{ParameterName="@CCatId",Value=objCourseMaster.CCatId},
             new SqlParameter{ParameterName="@CTypeId",Value=objCourseMaster.CTypeId},
             new SqlParameter{ParameterName="@Duration",Value=objCourseMaster.Duration},
             new SqlParameter{ParameterName="@EligibilityId",Value=objCourseMaster.EligibilityId},
             };
            var sqlQaury = @"sp_ad_CourseMaster @CType,@CTypeDesc,@procId";
            int Status = this.Database.ExecuteSqlCommand(sqlQaury, sqlParms);
            return Status;
        }
        public Int32 InsertExamMaster(ExamMaster objExamMaster, int userName)
        {
            var sqlParms = new SqlParameter[]{

             new SqlParameter{ParameterName="@ExamName",Value=objExamMaster.ExamName},
             new SqlParameter{ParameterName="@Description",Value=objExamMaster.Description},
             new SqlParameter{ParameterName="@DelFlag",Value=objExamMaster.DelFlag},

             };
            var sqlQaury = @"sp_ad_ExamMaster @ExamName,@Description,@DelFlag";
            int Status = this.Database.ExecuteSqlCommand(sqlQaury, sqlParms);
            return Status;
        }

        public virtual IEnumerable<ProductMasterReport> ItemPurchasedReport()
        {
            var sqlParams = new SqlParameter[] {
            };
            var sqlquery = @"sp_ad_GetPurchasedProduct";
            var sList = this.Database.SqlQuery<ProductMasterReport>(sqlquery, sqlParams);
            return sList;
        }

        public virtual IEnumerable<PurchaseProdList> PurchaseProdList(int ItemId)
        {
            var sqlParams = new SqlParameter[] {
                new SqlParameter{ParameterName="@ItemId",Value=ItemId},
            };
            var sqlquery = @"sp_ad_GetPurchaseProdList @ItemId";
            var sList = this.Database.SqlQuery<PurchaseProdList>(sqlquery, sqlParams);
            return sList;
        }


    }
}