using LHDN.CRM.Models.Models;
using LHDN.CRM.Models.ViewModels;
using LHDN.CRM.BC.Common;
using System;
using System.Data;
using System.Collections.Generic;
using System.Data.Entity.Validation;
using System.Linq;
using System.Web;

namespace LHDN.CRM.BC.Repositories
{
    public class AdminRepository : BaseRepository<CRMContext, StatusLevel1>
    {
        public List<StatusLevel1> GetAllStatusLevel()
        {
            var lstAllStatus = Context.StatusLevel1.Include("StatusLevel2.StatusLevel3").AsEnumerable().OrderBy(x => x.StatusLevel1Name).ToList();
            foreach (var list in lstAllStatus)
            {
                list.StatusLevel2 = list.StatusLevel2.Where(x => x.IsActive == true).ToList();
                foreach (var listlvl2 in list.StatusLevel2)
                {
                    listlvl2.StatusLevel3 = listlvl2.StatusLevel3.Where(x => x.IsActive == true).ToList();
                }
            }

            return lstAllStatus;

            //return Context.StatusLevel1.Include("StatusLevel2.StatusLevel3").Select(lvl1=>lvl1.StatusLevel1ID,l).ToList();
        }

        public List<StatusLevel2> GetStatusLevel2()
        {
            var lstStatus2 = Context.StatusLevel2.AsEnumerable().OrderBy(x => x.StatusLevel2Name).ToList();           

            return lstStatus2;            
        }

        public List<MainKategori> GetAllKategori()
        {
            var lstAllKategori = Context.MainKategori.Include("SubKategori.InnerSubKategori").AsEnumerable().OrderBy(x => x.Kategori1Name).ToList();
            foreach (var list in lstAllKategori)
            {
                list.SubKategori = list.SubKategori.Where(x => x.IsActive == true).ToList();
                foreach (var listKat2 in list.SubKategori)
                {
                    listKat2.InnerSubKategori = listKat2.InnerSubKategori.Where(x => x.IsActive == true).ToList();
                }
            }
            return lstAllKategori;
        }
        public List<MainKategori> GetKategori1()
        {
            return Context.MainKategori.AsEnumerable().OrderBy(x => x.Kategori1Name).ToList();
        }
        public List<SubKategori> GetKategori2()
        {
            return Context.SubKategori.AsEnumerable().OrderBy(x => x.Kategori2Name).ToList();
        }
        public List<InnerSubKategori> GetKategori3()
        {
            return Context.InnerSubKategori.AsEnumerable().OrderBy(x => x.Kategori3Name).ToList();
        }
        public List<Sumber> GetSumberOnly()
        {
            return Context.Sumber.AsEnumerable().OrderBy(x => x.SumberName).ToList();
        }

        public List<TPFileType> GetAllTPFIleType()
        {
            var lstTPFile = Context.TPFileType.Include("TPFileTypePrefix").AsEnumerable().ToList();
            foreach (var lst in lstTPFile)
            {
                lst.TPFileTypePrefix = lst.TPFileTypePrefix.Where(x => x.IsActive == true).ToList();
            }
            return Context.TPFileType.Include("TPFileTypePrefix").AsEnumerable().ToList();
        }

        public List<ActivityChannel> GetAllActivityChannel()
        {

            var lstActChannel = Context.ActivityChannel.Include("Sumber").AsEnumerable().OrderBy(x => x.ActChannelName).ToList();
            foreach (var lstAct in lstActChannel)
            {
                lstAct.Sumber = lstAct.Sumber.Where(x => x.IsActive == true).ToList();
            }

            return lstActChannel;
        }

        public List<JenisKempen> GetAllJenisKempen()
        {
            return Context.JenisKempen.AsEnumerable().Where(x => x.IsActive == true).OrderBy(x => x.JenisKempenName).ToList();
        }

        public List<Sumber> GetAllSumber()
        {

            return Context.Sumber.AsEnumerable().Where(x => x.IsActive == true).ToList();
        }

        public List<LookupParam> GetAllLookupParam()
        {

            return Context.LookupParam.AsEnumerable().OrderBy(x => x.ParamDesc).ToList();
        }

        public List<AuditEvent> GetAllAuditEvents()
        {
            return Context.AuditEvents.AsEnumerable().ToList();
        }

        public List<DocList> GetAllDocList()
        {
            var lstDocList = Context.DocList.Include("DocListDetail").AsEnumerable().OrderBy(x => x.KategoriDocName).ToList();
            foreach (var lstDoc in lstDocList)
            {
                lstDoc.DocListDetail = lstDoc.DocListDetail.Where(x => x.IsActive == true).ToList();
            }

            return lstDocList;
        }

        public List<LookupParamSys> GetAllLookupParamSysList()
        {
            return Context.LookupParamSys.AsEnumerable().OrderBy(x => x.ParamDesc).ToList();
        }

        public List<ParameterSettings> GetAllParamSettingList()
        {
            return Context.ParameterSettings.AsEnumerable().OrderBy(x => x.ParameterID).ToList(); ;
        }

        public List<KodSelesai> GetAllKodSelesai()
        {
            return Context.KodSelesai.AsEnumerable().OrderBy(x => x.KodSelesaiDesc).ToList();
            //return Context.KodSelesai.Include("TindakanSusulan.TindakanSusulanKempen").AsEnumerable().ToList();            
            //return Context.KodSelesai.Include("TindakanSusulan").AsEnumerable().ToList();
        }

        public List<Unit> GetAllUnit()
        {
            return Context.Unit.AsEnumerable().OrderBy(x => x.UnitName).ToList();
        }

        public List<JenisCawangan> GetAllJenisCawangan()
        {
            return Context.JenisCawangan.AsEnumerable().OrderBy(x => x.JenisCawanganName).ToList();
        }

        public List<UnitType> GetAllUnitType()
        {
            return Context.UnitType.AsEnumerable().Where(x => x.IsActive == true).OrderBy(x => x.TypeName).ToList();
        }

        public List<UnitType> GetAllAdminUnitType()
        {
            return Context.UnitType.AsEnumerable().OrderBy(x => x.TypeName).ToList();
        }


        public List<OutboundCampaign> GetAllCampaign()
        {
            return Context.OutboundCampaign.AsEnumerable().OrderBy(x => x.CampaignName).ToList();
        }

        public List<LookupParamSys> GetAllOclJenisCukai()
        {
            return Context.LookupParamSys.AsEnumerable().ToList();
        }
        public List<Months> GetMonths()
        {
            return Context.Months.AsEnumerable().OrderBy(x => x.MonthID).ToList();
        }

        public List<ChartAction> GetChartAction()
        {
            return Context.ChartAction.AsEnumerable().OrderBy(x => x.ChartActionID).ToList();
        }

        public List<TindakanSusulanViewModel> GetAllTindakanSusulan()
        {
            return ExecuteSP<TindakanSusulanViewModel>("proc_GetAllTindakanSusulan").OrderBy(x => x.TindakanSusulanDesc).ToList();
        }

        public List<TindakanSusulan> GetTindakanSusulanOnly()
        {
            return Context.TindakanSusulan.AsEnumerable().OrderBy(x => x.TindakanSusulanDesc).ToList();
        }

        public List<CCTemplate> GetAllCCTemplate()
        {
            return Context.CCTemplate.AsEnumerable().OrderBy(x => x.CCTemplateName).ToList();
        }

        public List<FieldOperatorViewModel> GetAllFieldOperator()
        {
            return ExecuteSP<FieldOperatorViewModel>("proc_GetAllFieldOperators").OrderBy(x => x.OperatorName).ToList();
        }

        public List<FieldMappingViewModel> GetAllFieldMapping()
        {
            return ExecuteSP<FieldMappingViewModel>("proc_GetAllFieldMapping").ToList();
        }

        public List<FunctionalRole> GetAllFunctionalRoles()
        {
            return ExecuteSP<FunctionalRole>("proc_GetAllFunctionRole").ToList();
        }

        public List<UserViewModel> GetAllUserProfile()
        {
            return ExecuteSP<UserViewModel>("proc_GetAllUserProfile").ToList();
        }

        public List<AdminCCCallResultViewModel> GetAllCCCallResults()
        {
            return ExecuteSP<AdminCCCallResultViewModel>("proc_GetAllCCCallResults").ToList();
        }

        public List<List<TemplatesEmailViewModel>> GetAllEmailTemplatesByType(List<TypeViewModel> lstTemplateType)
        {
            DataTable dbtlTemplateType = Extensions.ToDataTable<TypeViewModel>();

            if (lstTemplateType != null)
            {
                dbtlTemplateType = lstTemplateType.ToDataTable<TypeViewModel>();
            }

            List<TemplatesEmailViewModel> lst = new List<TemplatesEmailViewModel>();

            DataSet ds = ExecuteSP("proc_GetAllEmailTemplatesByType", "@TemplateType", dbtlTemplateType);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lst = DataTableToList<TemplatesEmailViewModel>(ds.Tables[0]).ToList();
                }
            }

            List<List<TemplatesEmailViewModel>> lstGrouped = lst.GroupBy(x => x.Language).Select(grp => grp.ToList()).ToList();
            return lstGrouped;
        }

        public List<TemplateSignatureViewModel> GetAllTemplateSignatureByLoginID(string strUserLoginID)
        {
            return ExecuteSP<TemplateSignatureViewModel>("proc_GetTemplateSignatureByLoginID", "@UserLoginID", strUserLoginID).ToList();
        }

        public List<WorkflowConfigurationViewModel> GetWorkflowConfig()
        {
            List<WorkflowConfigurationViewModel> model = new List<WorkflowConfigurationViewModel>();
            model = ExecuteSP<WorkflowConfigurationViewModel>("proc_GetWorkflowConfig").ToList();

            return model;
        }

        public WorkflowConfigurationViewModel AddNewWorkflowConfig(WorkflowConfigurationViewModel model)
        {
            WorkflowConfigurationViewModel data = new WorkflowConfigurationViewModel();
            data = ExecuteSP<WorkflowConfigurationViewModel>("proc_UpdateBackOfficeWorkflowConfig",
                    "@WFConfigID", model.WFConfigID, "@Kategory1ID", model.Kategori1ID,
                    "@Kategory2ID", model.Kategori2ID, "@Kategory3ID", model.Kategori3ID, "@JenisCawanganID", model.JenisCawanganID,
                    "@DefaultUnitTypeID", model.DefaultUnitTypeID, "@IsIntegrationDMS", model.IsIntegrateDMS,
                    "@IsEscalationReMS", model.IsEscalateReMS, "@IsAutoClosed", model.IsAutoClose,
                    "@AutoClosedUnitTypeID", model.AutoCloseUnitTypeID, "@IsCheckBalance", model.IsAutoCheckBalance,
                    "@IsActive", model.IsActive, "@IsDefault", model.IsDefault, "@JenisCawanganAutoCloseUnitID", model.JenisCawanganAutoCloseUnitID,
                    "@KategoriConsolidate", model.KategoriConsolidate,
                    "@KodAutocloseSysID", model.KodAutocloseSysID,
                    "@JenisCawCheckBalanceID", model.JenisCawCheckBalanceID,
                    "@JenisCukaiLookupID", model.JenisCukaiLookupID,
                    "@CreatedBy", model.CreatedBy, "@CreatedDateTime", DateTime.Now,
                    "@LastUpdatedBy", model.LastUpdatedBy, "@LastUpdatedDateTime", DateTime.Now).FirstOrDefault();
            return data;
        }

        public WorkflowConfigurationViewModel DeleteCurrentWorkflowConfig(WorkflowConfigurationViewModel model, DateTime now)
        {
            WorkflowConfigurationViewModel data = new WorkflowConfigurationViewModel();
            data = ExecuteSP<WorkflowConfigurationViewModel>("proc_DeleteBackOfficeWorkflowConfig", "@WFConfigID", model.WFConfigID,
                                                              "@IsActive", model.IsActive,
                                                              "@LastUpdatedBy", model.LastUpdatedBy, "@LastUpdatedDateTime", now).FirstOrDefault();
            return data;
        }

        public WorkflowConfigurationViewModel GetWorkflowConfigByID(long WFConfigID)
        {
            WorkflowConfigurationViewModel modal = new WorkflowConfigurationViewModel();
            modal = ExecuteSP<WorkflowConfigurationViewModel>("proc_GetWorkflowConfigByID", "@WFConfigID", WFConfigID).FirstOrDefault();

            return modal;
        }

        public RefActChannelViewModel GetRefActChannelByID(int RefActChannelID)
        {
            RefActChannelViewModel modal = new RefActChannelViewModel();
            modal = ExecuteSP<RefActChannelViewModel>("proc_GetRefActChannelByID", "@RefActChannelID", RefActChannelID).FirstOrDefault();

            return modal;
        }

        public RefActChannelViewModel UpdateRefActChannel(RefActChannelViewModel actmodel)
        {
            RefActChannelViewModel modal = new RefActChannelViewModel();
            modal = ExecuteSP<RefActChannelViewModel>("proc_UpdateRefActChannel",
                                                      "@RefActChannelID", actmodel.ActChannelID,
                                                      "@ActChannelName", actmodel.ActChannelName,
                                                      "@IsShowVerificationTab", actmodel.IsShowVerificationTab,
                                                      "@IsShowPenerimaanDocTab", actmodel.IsShowPenerimaanDocTab,
                                                      "@DefaultRoles", actmodel.DefaultRoles,
                                                      "@LastUpdatedBy", actmodel.LastUpdatedBy,
                                                      "@LastUpdatedDateTime", actmodel.LastUpdatedDatetime).FirstOrDefault();

            return modal;
        }

        public List<RefSumberViewModel> GetSumberDetail()
        {
            List<RefSumberViewModel> modal = new List<RefSumberViewModel>();
            modal = ExecuteSP<RefSumberViewModel>("proc_GetAllSumberDetail").ToList();

            return modal;
        }

        public RefSumberViewModel GetSumberByID(int SumberID)
        {
            RefSumberViewModel modal = new RefSumberViewModel();
            modal = ExecuteSP<RefSumberViewModel>("proc_GetSumberByID", "@SumberID", SumberID).FirstOrDefault();

            return modal;
        }

        public RefSumberViewModel UpdateSumberDetail(RefSumberViewModel model)
        {
            RefSumberViewModel modal = new RefSumberViewModel();

            modal = ExecuteSP<RefSumberViewModel>("proc_UpdateRefSumber",
                                             "@SumberID", model.SumberID,
                                             "@ActChannelID", model.ActChannelID,
                                             "@SumberName", model.SumberName,
                                             "@IsActive", model.IsActive,
                                             "@CreatedBy", model.CreatedBy,
                                             "@CreatedDateTime", model.CreatedDateTime,
                                             "@LastUpdatedBy", model.LastUpdatedBy,
                                             "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();

            return modal;
        }

        public RefSumberViewModel RemoveCurrentSumberDetail(RefSumberViewModel model)
        {
            RefSumberViewModel data = new RefSumberViewModel();
            data = ExecuteSP<RefSumberViewModel>("proc_RemoveCurrentSumber", "@SumberID", model.SumberID,
                                                 "@IsActive", model.IsActive,
                                                  "@LastUpdatedBy", model.LastUpdatedBy,
                                                  "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();
            return data;
        }

        public List<JenisKempenViewModel> GetAllJenisKempenHRCC(JenisKempenViewModel model)
        {
            List<JenisKempenViewModel> models = new List<JenisKempenViewModel>();
            models = ExecuteSP<JenisKempenViewModel>("proc_GetJenisKempen", "@IsHRCC", model.IsHRCC).ToList();

            return models;
        }

        public JenisKempenViewModel GetJenisKempenByID(int JenisKempenID)
        {
            JenisKempenViewModel model = new JenisKempenViewModel();
            model = ExecuteSP<JenisKempenViewModel>("proc_GetJenisKempenByID", "@JenisKempenID", JenisKempenID).FirstOrDefault();

            return model;
        }

        public JenisKempenViewModel UpdateJenisKempen(JenisKempenUpdateViewModel model)
        {
            JenisKempenViewModel modal = new JenisKempenViewModel();
            modal = ExecuteSP<JenisKempenViewModel>("proc_UpdateJenisKempen",
                                                     "@JenisKempenID", model.JenisKempen.JenisKempenID,
                                                     "@JenisKempenKategoriSysID", model.JenisKempen.JenisKempenKategoriSysID,
                                                     "@JenisKempenName", model.JenisKempen.JenisKempenName,
                                                     "@IsActive", model.JenisKempen.IsActive,
                                                     "@CreatedBy", model.JenisKempen.CreatedBy,
                                                     "@CreatedDateTime", model.JenisKempen.CreatedDatetime,
                                                     "@LastUpdatedBy", model.JenisKempen.LastUpdatedBy,
                                                     "@LastUpdatedDateTime", model.JenisKempen.LastUpdatedDatetime).FirstOrDefault();

            return modal;
        }

        public void InsertHCLEmailViewByKempenID(int JenisKempenID, string strLastUpdatedName, DateTime dtLastUpdatedTime)
        {
            ExecuteSP("proc_InsertHCLEmailViewByKempenID", "@JenisKempenID", JenisKempenID, "@LastUpdatedBy", strLastUpdatedName,
                      "@LastUpdatedDateTime", dtLastUpdatedTime);
        }

        public JenisKempenViewModel RemoveCurrentKempenDetail(JenisKempenViewModel model)
        {
            JenisKempenViewModel data = new JenisKempenViewModel();
            data = ExecuteSP<JenisKempenViewModel>("proc_RemoveCurrentJenisKempen", "@JenisKempenID", model.JenisKempenID,
                                                 "@IsActive", model.IsActive,
                                                  "@LastUpdatedBy", model.LastUpdatedBy,
                                                  "@LastUpdatedDateTime", model.LastUpdatedDatetime).FirstOrDefault();
            return data;
        }

        public List<AdminKategoryViewModel> GetAllKategoris()
        {
            List<AdminKategoryViewModel> modal = new List<AdminKategoryViewModel>();
            modal = ExecuteSP<AdminKategoryViewModel>("proc_GetAllKategoryDetail").ToList();

            return modal;
        }

        public AdminKategoryViewModel GetKategoriByID(int Kategori1ID)
        {
            AdminKategoryViewModel model = new AdminKategoryViewModel();
            model = ExecuteSP<AdminKategoryViewModel>("proc_GetKategoriByID", "@Kategori1ID", Kategori1ID).FirstOrDefault();

            return model;
        }

        public SubKategori GetSubKategoriById(int Kategori2ID)
        {
            SubKategori subKategori = new SubKategori();
            subKategori = ExecuteSP<SubKategori>("proc_GetSubKategoriByID", "@Kategori2ID", Kategori2ID).FirstOrDefault();

            return subKategori;
        }

        public InnerSubKategori GetInnerSubKategoriById(int StatusLevel3ID)
        {
            InnerSubKategori innerSubKategori = new InnerSubKategori();
            innerSubKategori = ExecuteSP<InnerSubKategori>("proc_GetInnerSubKategoriByID", "@Kategori3ID", StatusLevel3ID).FirstOrDefault();

            return innerSubKategori;
        }

        public MainKategoriViewModel UpdateKategoriLevel(MainKategoriViewModel model)
        {
            MainKategoriViewModel modal = new MainKategoriViewModel();

            if (model.EditMenuMode == 2 || model.EditMenuMode == 1)
            {
                modal = ExecuteSP<MainKategoriViewModel>("proc_UpdateKategori1",
                                                       "@Kategori1ID", model.Kategori1ID,
                                                       "@Kategori1Name", model.Kategori1Name,
                                                       "@DispositionCode1", model.DispositionCode1,
                                                       "@IsActive", model.IsActive,
                                                       "@CreatedBy", model.CreatedBy,
                                                       "@CreatedDateTime", model.CreatedDatetime,
                                                       "@LastUpdatedBy", model.LastUpdatedBy,
                                                       "@LastUpdatedDateTime", model.LastUpdatedDatetime).FirstOrDefault();
            }
            else
                if (model.EditMenuMode == 3 || model.EditMenuMode == 4)
            {
                modal = ExecuteSP<MainKategoriViewModel>("proc_UpdateKategori2",
                                                        "@Kategory1ID", model.Kategori1ID,
                                                        "@Kategory2ID", model.Kategori2ID,
                                                        "@Kategori2Name", model.Kategori2Name,
                                                        "@DispositionCode2", model.DispositionCode2,
                                                        "@IsActive", model.IsActiveSubKategori,
                                                        "@CreatedBy", model.CreatedBy,
                                                        "@CreatedDateTime", model.CreatedDatetime,
                                                        "@LastUpdatedBy", model.LastUpdatedBy,
                                                        "@LastUpdatedDateTime", model.LastUpdatedDatetime).FirstOrDefault();
            }
            else
            {
                modal = ExecuteSP<MainKategoriViewModel>("proc_UpdateKategori3",
                                                           "@Kategori3ID", model.Kategori3ID,
                                                           "@Kategori2ID", model.Kategori2ID,
                                                           "@Kategori3Name", model.Kategori3Name,
                                                           "@DispositionCode3", model.DispositionCode3,
                                                           "@IsActive", model.IsActiveInnerSubKategori,
                                                           "@CreatedBy", model.CreatedBy,
                                                           "@CreatedDateTime", model.CreatedDatetime,
                                                           "@LastUpdatedBy", model.LastUpdatedBy,
                                                           "@LastUpdatedDateTime", model.LastUpdatedDatetime).FirstOrDefault();
            }


            return modal;
        }

        public MainKategoriViewModel DeleteKategoriLevel(MainKategoriViewModel model)
        {
            MainKategoriViewModel data = new MainKategoriViewModel();
            data = ExecuteSP<MainKategoriViewModel>("proc_DeleteKategori1ID", "@Kategori1ID", model.Kategori1ID,
                                                 "@IsActive", model.IsActive,
                                                 "@LastUpdatedBy", model.LastUpdatedBy,
                                                 "@LastUpdatedDateTime", model.LastUpdatedDatetime).FirstOrDefault();
            return data;
        }

        public AdminKategoryViewModel RemoveCurrentKempenDetail(AdminKategoryViewModel model)
        {
            AdminKategoryViewModel data = new AdminKategoryViewModel();
            data = ExecuteSP<AdminKategoryViewModel>("proc_RemoveCurrentSubKategori", "@Kategori2ID", model.Kategori2ID,
                                                 "@IsActive", model.IsActive,
                                                  "@LastUpdatedBy", model.LastUpdatedBy,
                                                  "@LastUpdatedDateTime", model.LastUpdatedDatetime).FirstOrDefault();
            return data;
        }

        public List<CFFMainKategoriViewModel> GetCFFMainKategori()
        {
            return ExecuteSP<CFFMainKategoriViewModel>("proc_GetCFFMainKategori").ToList();
        }

        public List<CFFSubKategoriViewModel> GetAllCFFSubKategori()
        {
            return ExecuteSP<CFFSubKategoriViewModel>("proc_GetAllCFFSubKategori").ToList();
        }

        public List<KodSelesaiViewModel> GetAllAdminKodSelesai()
        {
            List<KodSelesaiViewModel> kodselesai = new List<KodSelesaiViewModel>();
            kodselesai = ExecuteSP<KodSelesaiViewModel>("proc_GetAllKodSelesai").ToList();
            return kodselesai;
        }
        public KodSelesaiViewModel GetKodSelesaiByID(int kodSelesaiID)
        {
            KodSelesaiViewModel model = new KodSelesaiViewModel();
            model = ExecuteSP<KodSelesaiViewModel>("proc_GetKodSelesaiByID", "@KodSelesaiID", kodSelesaiID).FirstOrDefault();

            return model;
        }

        public KodSelesaiViewModel CheckCCResultUsedbySystem(KodSelesaiViewModel model)
        {
            KodSelesaiViewModel modal = new KodSelesaiViewModel();

            modal = ExecuteSP<KodSelesaiViewModel>("proc_GetCCCallResultByID",
                                             "@KodSelesaiID", model.KodSelesaiID,
                                             "@CCCallResultID", model.CCCallResultID,
                                             "@IsSystemUsed", model.IsSystemUsed).FirstOrDefault();

            return modal;
        }

        public KodSelesaiViewModel UpdateKodSelesai(KodSelesaiViewModel model)
        {
            KodSelesaiViewModel modal = new KodSelesaiViewModel();

            modal = ExecuteSP<KodSelesaiViewModel>("proc_UpdateAdminKodSelesai",
                                             "@KodSelesaiID", model.KodSelesaiID,
                                             "@KodSelesaiCode", model.KodSelesaiCode,
                                             "@KodSelesaiDesc", model.KodSelesaiDesc,
                                             "@CCCallResultID", model.CCCallResultID,
                                             "@IsAgentOption", model.IsAgentOption,
                                             "@KategoriPenyelesaianID", model.KategoriPenyelesaianID,
                                             "@IsActive", model.IsActive,
                                             "@CreatedBy", model.CreatedBy,
                                             "@CreatedDateTime", model.CreatedDateTime,
                                             "@LastUpdatedBy", model.LastUpdatedBy,
                                             "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();

            return modal;
        }

        public KodSelesaiViewModel RemoveCurrentAdminKodSelesai(KodSelesaiViewModel model)
        {
            KodSelesaiViewModel data = new KodSelesaiViewModel();
            data = ExecuteSP<KodSelesaiViewModel>("proc_RemoveCurrentKodSelesai", "@KodSelesaiID", model.KodSelesaiID,
                                                  "@IsActive", model.IsActive,
                                                  "@LastUpdatedBy", model.LastUpdatedBy,
                                                  "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();
            return data;
        }

        public AdminCCTemplateNameViewModel GetCCTemplateNameByID(int CCTemplateNameId)
        {
            AdminCCTemplateNameViewModel model = new AdminCCTemplateNameViewModel();
            model = ExecuteSP<AdminCCTemplateNameViewModel>("proc_GetCCTemplateNameByID", "@CCTemplataNameID", CCTemplateNameId).FirstOrDefault();

            return model;
        }

        public AdminCCTemplateNameViewModel UpdateCCTemplateName(AdminCCTemplateNameViewModel model)
        {
            AdminCCTemplateNameViewModel modal = new AdminCCTemplateNameViewModel();

            modal = ExecuteSP<AdminCCTemplateNameViewModel>("proc_UpdateAdminCCTemplateName",
                                     "@CCTemplateNameID", model.CCTemplateNameID,
                                     "@CCTemplateCode", model.CCTemplateCode,
                                     "@CCTemplateName", model.CCTemplateName,
                                     "@CCTemplateType", model.CCTemplateType,
                                     "@IsActive", model.IsActive,
                                     "@CreatedBy", model.CreatedBy,
                                     "@CreatedDateTime", model.CreatedDateTime,
                                     "@LastUpdatedBy", model.LastUpdatedBy,
                                     "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();

            return modal;
        }

        public AdminCCTemplateNameViewModel RemoveCurrentAdminCCTemplateName(AdminCCTemplateNameViewModel model)
        {
            AdminCCTemplateNameViewModel data = new AdminCCTemplateNameViewModel();
            data = ExecuteSP<AdminCCTemplateNameViewModel>("proc_RemoveCurrentCCTemplateName", "@CCTemplateNameID", model.CCTemplateNameID,
                                                 "@IsActive", model.IsActive,
                                                  "@LastUpdatedBy", model.LastUpdatedBy,
                                                  "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();
            return data;
        }

        public DocListViewModel UpdateDocList(DocListViewModel model)
        {
            DocListViewModel DocListModel = new DocListViewModel();

            //Insert and update for Parent Doc (1-Add New Kategory Doc,2 - Edit Current Kategori)
            if (model.EditMenuMode == 2 || model.EditMenuMode == 1)
            {
                DocListModel = ExecuteSP<DocListViewModel>("proc_UpdateDocList",
                                                           "@DocListID", model.DocListID,
                                                           "@KategoriDocName", model.KategoriDocName,
                                                           "@KategoriDocCode", model.KategoriDocCode,
                                                           "@IsActive", model.IsActiveKategoryDoc,
                                                           "@CreatedBy", model.CreatedBy,
                                                           "@CreatedDateTime", model.CreatedDateTime,
                                                           "@LastUpdatedBy", model.LastUpdatedBy,
                                                           "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();
            }
            else
            {
                DocListModel = ExecuteSP<DocListViewModel>("proc_UpdateDocListDetail",
                                                                   "@DocListDetailID", model.DocListDetailID,
                                                                   "@DocListID", model.DocListID,
                                                                   "@DocName", model.DocName,
                                                                   "@DocCode", model.DocCode,
                                                                   "@IsActive", model.IsActiveDocument,
                                                                   "@CreatedBy", model.CreatedBy,
                                                                   "@CreatedDateTime", model.CreatedDateTime,
                                                                   "@LastUpdatedBy", model.LastUpdatedBy,
                                                                   "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();
            }

            return DocListModel;
        }

        public DocListDetail GetDocListByID(int DocListDetailID)
        {
            DocListDetail DocListDetail = new DocListDetail();
            DocListDetail = ExecuteSP<DocListDetail>("proc_GetDocListDetailByID", "@DocListDetailID", DocListDetailID).FirstOrDefault();

            return DocListDetail;
        }

        public DocListViewModel RemoveAdminKategoriDocument(DocListViewModel model)
        {
            DocListViewModel data = new DocListViewModel();
            if (model.EditMenuMode == 2)
            {
                data = ExecuteSP<DocListViewModel>("proc_RemoveParentKategoriDocument", "@DocListID", model.DocListID,
                                            "@IsActive", model.IsActiveKategoryDoc,
                                             "@LastUpdatedBy", model.LastUpdatedBy,
                                             "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();
            }
            else
            {
                data = ExecuteSP<DocListViewModel>("proc_RemoveDocumentDetails",
                                                   "@DocListDetailID", model.DocListDetailID,
                                                    //"@DocListID",model.DocListID,
                                                    "@IsActive", model.IsActiveDocument,
                                                     "@LastUpdatedBy", model.LastUpdatedBy,
                                                     "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();
            }

            return data;
        }

        public AdminStatusLevel1 UpdatedStatusLevel(AdminStatusLevel1 statusModel)
        {
            AdminStatusLevel1 adminStatusModel = new AdminStatusLevel1();

            if (statusModel.EditMenuMode == 2 || statusModel.EditMenuMode == 1)
            {
                adminStatusModel = ExecuteSP<AdminStatusLevel1>("proc_UpdateStatusLevel1",
                                                                   "@StatusLevel1ID", statusModel.StatusLevel1ID,
                                                                   "@StatusLevel1Name", statusModel.StatusLevel1Name,
                                                                   "@IsActive", statusModel.IsActive,
                                                                   "@CreatedBy", statusModel.CreatedBy,
                                                                   "@CreatedDateTime", statusModel.CreatedDatetime,
                                                                   "@LastUpdatedBy", statusModel.LastUpdatedBy,
                                                                   "@LastUpdatedDateTime", statusModel.LastUpdatedDatetime).FirstOrDefault();
            }
            else if (statusModel.EditMenuMode == 3 || statusModel.EditMenuMode == 4)
            {
                adminStatusModel = ExecuteSP<AdminStatusLevel1>("proc_UpdateStatusLevel2",
                                                                "@StatusLevel2ID", statusModel.StatusLevel2ID,
                                                                "@StatusLevel2Name", statusModel.StatusLevel2Name,
                                                                "@StatusLevel1ID", statusModel.StatusLevel1ID,
                                                                "@IsActive", statusModel.IsActiveLevel2,
                                                                "@CreatedBy", statusModel.CreatedBy,
                                                                "@CreatedDateTime", statusModel.CreatedDatetime,
                                                                "@LastUpdatedBy", statusModel.LastUpdatedBy,
                                                                "@LastUpdatedDateTime", statusModel.LastUpdatedDatetime).FirstOrDefault();
            }
            else
            {
                adminStatusModel = ExecuteSP<AdminStatusLevel1>("proc_UpdateStatusLevel3",
                                                "@StatusLevel3ID", statusModel.StatusLevel3ID,
                                                "@StatusLevel3Name", statusModel.StatusLevel3Name,
                                                "@StatusLevel2ID", statusModel.StatusLevel2ID,
                                                "@IsActive", statusModel.IsActiveLevel3,
                                                "@CreatedBy", statusModel.CreatedBy,
                                                "@CreatedDateTime", statusModel.CreatedDatetime,
                                                "@LastUpdatedBy", statusModel.LastUpdatedBy,
                                                "@LastUpdatedDateTime", statusModel.LastUpdatedDatetime).FirstOrDefault();
            }

            return adminStatusModel;
        }

        public StatusLevel2 GetStatusLevel2ById(int StatusLevel2ID)
        {
            StatusLevel2 StatusLevel2 = new StatusLevel2();
            StatusLevel2 = ExecuteSP<StatusLevel2>("proc_GetStatusLevel2ByID", "@StatusLevel2ID", StatusLevel2ID).FirstOrDefault();

            return StatusLevel2;
        }

        public StatusLevel3 GetStatusLevel3ById(int StatusLevel3ID)
        {
            StatusLevel3 StatusLevel3 = new StatusLevel3();
            StatusLevel3 = ExecuteSP<StatusLevel3>("proc_GetStatusLevel3ByID", "@StatusLevel3ID", StatusLevel3ID).FirstOrDefault();

            return StatusLevel3;
        }

        public AdminStatusLevel1 RemoveAdminStatusLevel(AdminStatusLevel1 model)
        {
            AdminStatusLevel1 data = new AdminStatusLevel1();
            if (model.EditMenuMode == 2)
            {
                data = ExecuteSP<AdminStatusLevel1>("proc_RemoveStatusLevel1", "@StatusLevel1ID", model.StatusLevel1ID,
                                                    "@IsActive", model.IsActive,
                                                     "@LastUpdatedBy", model.LastUpdatedBy,
                                                     "@LastUpdatedDateTime", model.LastUpdatedDatetime).FirstOrDefault();
            }
            else
            {
                data = ExecuteSP<AdminStatusLevel1>("proc_RemoveStatusLevel2",
                                                   "@DocListDetailID", model.StatusLevel2ID,
                                                    "@IsActive", model.IsActiveLevel2,
                                                     "@LastUpdatedBy", model.LastUpdatedBy,
                                                     "@LastUpdatedDateTime", model.LastUpdatedDatetime).FirstOrDefault();
            }

            return data;
        }

        public List<RefCFFKategoriUpdateViewModel> GetAllCFFKategori()
        {
            List<RefCFFKategoriUpdateViewModel> objCffKategori = new List<RefCFFKategoriUpdateViewModel>();
            objCffKategori = ExecuteSP<RefCFFKategoriUpdateViewModel>("proc_GetAllCFFKategori").ToList();

            return objCffKategori;
        }

        public RefCFFKategoriUpdateViewModel GetCFFKategoriByID(int CFFKategoriID)
        {
            RefCFFKategoriUpdateViewModel modal = new RefCFFKategoriUpdateViewModel();
            modal = ExecuteSP<RefCFFKategoriUpdateViewModel>("proc_GetCFFKategoriByID", "@CFFKategoriID", CFFKategoriID).FirstOrDefault();

            return modal;
        }

        public RefCFFKategoriUpdateViewModel UpdateCFFKategori(RefCFFKategoriUpdateViewModel model)
        {
            RefCFFKategoriUpdateViewModel modal = new RefCFFKategoriUpdateViewModel();

            modal = ExecuteSP<RefCFFKategoriUpdateViewModel>("proc_UpdateCFFKategori",
                                                            "@CFFKategoriID", model.CFFKategoriID,
                                                            "@SubKategoriName", model.SubKategoriName,
                                                            "@Kategori1ID", model.Kategori1ID,
                                                            "@SkillSet", model.SkillSet,
                                                            "@ManagerFunctionalRoleID", model.FunctionalRoleID,
                                                            "@DefaultUnitID", model.DefaultUnitID,
                                                            "@JenisCawanganID", model.JenisCawanganID,
                                                            "@IsActive", model.IsActive,
                                                            "@CreatedBy", model.CreatedBy,
                                                            "@CreatedDateTime", model.CreatedDateTime,
                                                            "@LastUpdatedBy", model.LastUpdatedBy,
                                                            "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();

            return modal;
        }

        public RefCFFKategoriUpdateViewModel DeleteCFFKategori(RefCFFKategoriUpdateViewModel model)
        {
            RefCFFKategoriUpdateViewModel data = new RefCFFKategoriUpdateViewModel();
            data = ExecuteSP<RefCFFKategoriUpdateViewModel>("proc_RemoveCFFKategori", "@CFFKategoriID", model.CFFKategoriID,
                                                 "@IsActive", model.IsActive,
                                                 "@LastUpdatedBy", model.LastUpdatedBy,
                                                 "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();
            return data;
        }

        public UnitTypeViewModel GetUnitTypeByID(int UnitTypeID)
        {
            UnitTypeViewModel modal = new UnitTypeViewModel();
            modal = ExecuteSP<UnitTypeViewModel>("proc_GetUnitTypeByID", "@UnitTypeID", UnitTypeID).FirstOrDefault();

            return modal;
        }

        public UnitTypeViewModel UpdateUnitType(UnitTypeViewModel model)
        {
            UnitTypeViewModel modal = new UnitTypeViewModel();

            modal = ExecuteSP<UnitTypeViewModel>("proc_UpdateUnitType",
                                                    "@UnitTypeID", model.UnitTypeID,
                                                    "@TypeName", model.TypeName,
                                                    "@IsActive", model.IsActive,
                                                    "@CreatedBy", model.CreatedBy,
                                                    "@CreatedDateTime", model.CreatedDatetime,
                                                    "@LastUpdatedBy", model.LastUpdatedBy,
                                                    "@LastUpdatedDateTime", model.LastUpdatedDatetime).FirstOrDefault();

            return modal;
        }

        public UnitTypeViewModel DeleteUnitType(UnitTypeViewModel model)
        {
            UnitTypeViewModel data = new UnitTypeViewModel();
            data = ExecuteSP<UnitTypeViewModel>("proc_RemoveUnitType", "@UnitTypeID", model.UnitTypeID,
                                                 "@IsActive", model.IsActive,
                                                 "@LastUpdatedBy", model.LastUpdatedBy,
                                                 "@LastUpdatedDateTime", model.LastUpdatedDatetime).FirstOrDefault();
            return data;
        }

        public ParameterSettingViewModel GetParamterSettingByID(int ParameterID)
        {
            ParameterSettingViewModel modal = new ParameterSettingViewModel();
            modal = ExecuteSP<ParameterSettingViewModel>("proc_GetParamterSettingByID", "@ParameterID", ParameterID).FirstOrDefault();

            return modal;
        }

        public ParameterSettingViewModel UpdateParameterSetting(ParameterSettingViewModel model)
        {
            ParameterSettingViewModel modal = new ParameterSettingViewModel();

            modal = ExecuteSP<ParameterSettingViewModel>("proc_UpdateParameterSetting",
                                                    "@ParameterID", model.ParameterID,
                                                    "@ParameterValue", model.ParameterValue,
                                                    "@LastUpdatedBy", model.LastUpdatedBy,
                                                    "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();

            return modal;
        }

        public List<RefActChannelViewModel> GetAllActChannelForAdmin()
        {
            List<RefActChannelViewModel> modal = new List<RefActChannelViewModel>();
            modal = ExecuteSP<RefActChannelViewModel>("proc_GetAllRefActChannel").ToList();

            return modal;
        }

        public List<TindakanSusulanViewModel> GetAdminAllTindakanSusulan()
        {
            return ExecuteSP<TindakanSusulanViewModel>("proc_GetAdminAllTindakanSusulan").OrderBy(x => x.TindakanSusulanDesc).ToList();
        }

        public List<TindakanSusulanKempenViewModel> GetAdminAllTindakanSusulanKempen()
        {
            return ExecuteSP<TindakanSusulanKempenViewModel>("proc_GetAdminAllTindakanSusulanKempen").ToList();
        }

        public TindakanSusulanViewModel GetTindakanSusulanByID(int TindakanSusulanID)
        {
            TindakanSusulanViewModel modal = new TindakanSusulanViewModel();
            modal = ExecuteSP<TindakanSusulanViewModel>("proc_GetTindakanSusulanByID", "@TindakanSusulanID", TindakanSusulanID).FirstOrDefault();

            return modal;
        }
        public TindakanSusulanViewModel GetTindakanSusulanRoleByID(int TindakanSusulanID)
        {
            TindakanSusulanViewModel modal = new TindakanSusulanViewModel();
            modal = ExecuteSP<TindakanSusulanViewModel>("proc_GetTindakanSusulanRoleByID", "@TindakanSusulanID", TindakanSusulanID).FirstOrDefault();

            return modal;
        }
        public List<TindakanSusulanViewModel> GetTindakanSusulanKempenByID(int TindakanSusulanID)
        {
            List<TindakanSusulanViewModel> modal = new List<TindakanSusulanViewModel>();
            modal = ExecuteSP<TindakanSusulanViewModel>("proc_GetTindakanSusulanKempenByID", "@TindakanSusulanID", TindakanSusulanID).ToList();

            return modal;
        }
        public TindakanSusulanViewModel UpdateTindakanSusulan(TindakanSusulanViewModel model)
        {
            //TindakanSusulanViewModel modal = new TindakanSusulanViewModel();

            model = ExecuteSP<TindakanSusulanViewModel>("proc_UpdateTindakanSusulan",
                                                    "@TindakanSusulanID", model.TindakanSusulanID,
                                                    "@KodSelesaiID", model.KodSelesaiID,
                                                    "@TindakanSusulanCode", model.TindakanSusulanCode,
                                                    "@TindakanSusulanDesc", model.TindakanSusulanDesc,
                                                    "@IsActive", model.IsActive,
                                                    "@CreatedBy", model.CreatedBy,
                                                    "@CreatedDateTime", model.CreatedDatetime,
                                                    "@LastUpdatedBy", model.LastUpdatedBy,
                                                    "@LastUpdatedDateTime", model.LastUpdatedDatetime).FirstOrDefault();

            return model;
        }
        public TindakanSusulanViewModel UpdateTindakanSusulanRoles(TindakanSusulanViewModel model)
        {
            TindakanSusulanViewModel modal = new TindakanSusulanViewModel();

            modal = ExecuteSP<TindakanSusulanViewModel>("proc_UpdateTindakanSusulanRoles",
                                                    "@TSRoleID", model.TSRoleID,
                                                    "@TindakanSusulanID", model.TindakanSusulanID,
                                                    "@FunctionalRoleID", model.FunctionalRoleID,
                                                    "@CreatedBy", model.CreatedBy,
                                                    "@CreatedDateTime", model.CreatedDatetime,
                                                    "@LastUpdatedBy", model.LastUpdatedBy,
                                                    "@LastUpdatedDateTime", model.LastUpdatedDatetime).FirstOrDefault();

            return modal;
        }
        public TindakanSusulanViewModel UpdateTindakanSusulanKempen(TindakanSusulanViewModel model)
        {
            TindakanSusulanViewModel modal = new TindakanSusulanViewModel();

            modal = ExecuteSP<TindakanSusulanViewModel>("proc_UpdateTindakanSusulanKempen",
                                                    "@TSKempenID", model.TSKempenID,
                                                    "@TindakanSusulanID", model.TindakanSusulanID,
                                                    "@JenisKempenID", model.JenisKempenID,
                                                    "@CreatedBy", model.CreatedBy,
                                                    "@CreatedDateTime", model.CreatedDatetime,
                                                    "@LastUpdatedBy", model.LastUpdatedBy,
                                                    "@LastUpdatedDateTime", model.LastUpdatedDatetime).FirstOrDefault();

            return modal;
        }

        public TindakanSusulanViewModel DeleteUnusedKempen(string multipleJenisKempen, int TindakanSusulanID)
        {
            return ExecuteSP<TindakanSusulanViewModel>("proc_DeleteUnusedKempen",
                                                       "@MultipleKempenID", multipleJenisKempen,
                                                       "@TindakanSusulanID", TindakanSusulanID).FirstOrDefault();
        }

        public List<AutoCloseSysViewModel> GetAutoCloseSys()
        {
            return ExecuteSP<AutoCloseSysViewModel>("proc_GetAllAutoCloseSys").ToList();
        }

        public List<Template> GetAllTemplate()
        {
            return ExecuteSP<Template>("proc_GetAllTemplate").ToList();
        }

        public List<FunctionalRoleMember> GetAllFunctionalRoleMember()
        {
            return ExecuteSP<FunctionalRoleMember>("proc_GetAllFunctionalRoleMember").ToList();
        }

        public AdminTemplateViewModel GetTemplateByID(int TemplateID)
        {
            AdminTemplateViewModel modal = new AdminTemplateViewModel();
            modal = ExecuteSP<AdminTemplateViewModel>("proc_GetTemplateByID", "@TemplateID", TemplateID).FirstOrDefault();

            return modal;
        }

        public AdminTemplateViewModel UpdateTemplate(AdminTemplateViewModel model)
        {
            AdminTemplateViewModel modal = new AdminTemplateViewModel();

            if (model.TemplateType == Constant.EmailTemplateType.System)
            {
                modal = ExecuteSP<AdminTemplateViewModel>("proc_UpdateTemplate",
                                                           "@TemplateID", model.TemplateID,
                                                           "@TemplateHTML", model.TemplateHTML,
                                                           "@Subject", model.Subject,
                                                           "@TemplateName", model.TemplateName,
                                                           "@LastUpdatedByName",model.LastUpdatedByName,
                                                           "@LastUpdatedBy", model.LastUpdatedBy,
                                                           "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();
            }
            else
            {
                modal = ExecuteSP<AdminTemplateViewModel>("proc_UpdateCustomiseTemplate",
                                                            "@TemplateID", model.TemplateID,
                                                            "@TemplateHTML", model.TemplateHTML,
                                                            "@Subject", model.Subject,
                                                            "@TemplateType", model.TemplateType,
                                                            "@LanguageID", model.LanguageID,
                                                            "@RefTableName", model.RefTableName,
                                                            "@IsSystemTemplate", model.IsSystemTemplate,
                                                            "@TemplateName", model.TemplateName,
                                                            "@IsActive", model.IsActive,
                                                            "@CreatedBy", model.CreatedBy,
                                                            "@CreatedByName", model.CreatedByName,
                                                            "@CreatedDateTime", model.CreatedDateTime,
                                                            "@LastUpdatedBy", model.LastUpdatedBy,
                                                            "@LastUpdatedByName", model.LastUpdatedByName,
                                                            "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();
            }


            return modal;
        }

        public AdminTemplateViewModel DeleteEmailTemplate(AdminTemplateViewModel model)
        {
            AdminTemplateViewModel modal = new AdminTemplateViewModel();

            modal = ExecuteSP<AdminTemplateViewModel>("proc_DeleteEmailTemplate",
                                                       "@TemplateID", model.TemplateID,
                                                       "@LastUpdatedByName", model.LastUpdatedByName,
                                                       "@LastUpdatedBy", model.LastUpdatedBy,
                                                       "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();

            return modal;
        }
        public void InsertBookmarkValue(TemplateUpdateViewModel model, int intNewTemplateID)
        {
            foreach (var value in model.AdminTemplate.TemplateBookmark)
            {
                ExecuteSP("proc_InsertDefaultTemplateBook", "@TemplateID", intNewTemplateID,
                          "@BookmarkName", value.Text);
            }

        }

        public List<TemplateBookmark> GetAdminBookmarkTemplateByID(int intTemplateID)
        {
            List<TemplateBookmark> model = new List<TemplateBookmark>();
            model = ExecuteSP<TemplateBookmark>("proc_GetAllTemplateBookmarkByID", "@TemplateID", intTemplateID).ToList(); ;

            return model;
        }

        public List<StatusLevel1> GetAllAdminStatusLevel()
        {
            var lstAllStatus = Context.StatusLevel1.Include("StatusLevel2.StatusLevel3").AsEnumerable().OrderBy(x => x.StatusLevel1Name).ToList();

            return lstAllStatus;

        }

        public List<MainKategori> GetAllAdminKategori()
        {
            var lstAllKategori = Context.MainKategori.Include("SubKategori.InnerSubKategori").AsEnumerable().OrderBy(x => x.Kategori1Name).ToList();

            return lstAllKategori;
        }
        public List<TPFileType> GetAllAdminTPFIleType()
        {
            var lstTPFile = Context.TPFileType.Include("TPFileTypePrefix").AsEnumerable().ToList();
            return Context.TPFileType.Include("TPFileTypePrefix").AsEnumerable().ToList();
        }
        public List<DocList> GetAllAdminDocList()
        {
            var lstDocList = Context.DocList.Include("DocListDetail").AsEnumerable().OrderBy(x => x.KategoriDocName).ToList();
            return lstDocList;
        }

        //public List<Operators> GetOperators()
        //{
        //    return Context.Operators.Include("FieldDataType").AsEnumerable().ToList();
        //}
        //public List<TPFieldMapping> GetTPFieldMapping()
        //{
        //    return Context.TPFieldMapping.AsEnumerable().ToList();
        //}
        //public List<CaseFieldMapping> GetCaseFieldMapping()
        //{
        //    return Context.CaseFieldMapping.AsEnumerable().ToList();
        //}
        //public List<ActFieldMapping> GetActFieldMapping()
        //{
        //    return Context.ActFieldMapping.AsEnumerable().ToList();
        //}


        public DataSet GetDashboardListing(DashboardListingViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@DashboardName", paging.DashboardName,
                                        "@CreatedBy", paging.CreatedBy
                                    });

            if (!string.IsNullOrEmpty(paging.SortExpression))
            {
                lstParam.AddRange(new object[] {
                "@SortExpression", paging.SortExpression
                });
            }

            return ExecuteSP("proc_GetDashboardListing", lstParam.ToArray());
        }


        public DashboardViewModel GetDashboardDetails(int intDashboardID = 0)
        {
            DashboardViewModel dashboardDetail = new DashboardViewModel();

            DataSet ds = ExecuteSP("proc_GetDashboardDetails", "@DashboardID", intDashboardID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    dashboardDetail = DataTableToList<DashboardViewModel>(ds.Tables[0]).FirstOrDefault();
                }

                if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
                {
                    dashboardDetail.DashboardUnit = DataTableToList<DashboardUnitViewModel>(ds.Tables[1]).ToList();
                }

                if (ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0)
                {
                    dashboardDetail.DashboardZones = DataTableToList<DashboardZonViewModel>(ds.Tables[2]).ToList();
                }
            }


            return dashboardDetail;
        }

        public List<DashboardZonViewModel> GetDashboardZonList(int intDashboardID = 0)
        {
            List<DashboardZonViewModel> lstZonDetail = new List<DashboardZonViewModel>();

            DataSet ds = ExecuteSP("proc_GetDashboardZonList", "@DashboardID", intDashboardID);
            if (ds != null)
            {

                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lstZonDetail = DataTableToList<DashboardZonViewModel>(ds.Tables[0]).ToList();
                }
            }


            return lstZonDetail;
        }

        public int UpdateDashboard(DashboardViewModel dashboardDetailVM, List<DashboardUnitViewModel> dashboardUnitVM,
                                   List<DashboardZonViewModel> dashboardZonVM,
                                   string strLastUpdatedBy, DateTime dtLastUpdatedDateTime)
        {
            int intDashboardID = 0;
            DataTable dTblDashboardDetailVM = dashboardDetailVM.ToDataTable<DashboardViewModel>();
            dTblDashboardDetailVM.Columns.Remove("CreatedBy");
            dTblDashboardDetailVM.Columns.Remove("CreatedDateTime");
            dTblDashboardDetailVM.Columns.Remove("LastUpdatedDateTime");
            dTblDashboardDetailVM.Columns.Remove("LastUpdatedBy");
            dTblDashboardDetailVM.Columns.Remove("LastUpdatedByName");
            dTblDashboardDetailVM.Columns.Remove("DashboardZones");
            dTblDashboardDetailVM.Columns.Remove("DashboardUnit");
            dTblDashboardDetailVM.Columns.Remove("IsAllowAccess");


            DataTable dTblDashboardUnitVM = new DataTable();

            if (dashboardUnitVM != null)
            {
                dTblDashboardUnitVM = dashboardUnitVM.ToDataTable<DashboardUnitViewModel>();               
            }
            else {
                dashboardUnitVM = new List<DashboardUnitViewModel>();
                dTblDashboardUnitVM = dashboardUnitVM.ToDataTable<DashboardUnitViewModel>();
            }
            dTblDashboardUnitVM.Columns.Remove("DisplayName");
            dTblDashboardUnitVM.Columns.Remove("AdditionalInfo");

            DataTable dTblDashboardZonVM = dashboardZonVM != null ? dashboardZonVM.ToDataTable<DashboardZonViewModel>() : new List<DashboardZonViewModel>().ToDataTable<DashboardZonViewModel>();
            dTblDashboardZonVM.Columns.Remove("DashboardID");


            DataSet ds = ExecuteSP("proc_UpdateDashboard"
                                   , "@tblDashboardDetail", dTblDashboardDetailVM
                                   , "@tblDashboardUnit", dTblDashboardUnitVM
                                   , "@tblDashboardZon", dTblDashboardZonVM
                                   , "@LastUpdatedBy", strLastUpdatedBy
                                   , "@LastUpdatedDateTime", dtLastUpdatedDateTime);
            DataTable dTblResult = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    intDashboardID = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

                }
            }
            return intDashboardID;
        }

        public void DeleteDashboard(int intDashboardID)
        {
            ExecuteUpdateSP("proc_DeleteDashboard"
                                   , "@DashboardID", intDashboardID);          
        }


        public List<DashboardSavedViewViewModel> GetDashboardSavedView()
        {
            List<DashboardSavedViewViewModel> lstDashboardSavedView = new List<DashboardSavedViewViewModel>();

            DataSet ds = ExecuteSP("proc_GetDashboardSavedView");
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lstDashboardSavedView = DataTableToList<DashboardSavedViewViewModel>(ds.Tables[0]).ToList();
                }
            }

            return lstDashboardSavedView;
        }

        public int SearchDuplicateDashboardName(DashboardViewModel model)
        {
            return ExecuteUpdateSP("proc_SearchDuplicateDashboardName",
                                  "@DashboardName", model.DashboardName,
                                  "@DashboardID", model.DashboardID);

        }


    }

    public class LookupParamRepository : BaseRepository<CRMContext, LookupParam>
    {
        public bool AddLookupParam(LookupParam model)
        {
            bool blnIsSuccess = false;
            try
            {
                if (model.LookupParamID > 0)
                {
                    Edit(model);
                    Save();
                }
                else
                {
                    Add(model);
                    Save();
                }
                blnIsSuccess = true;
            }
            catch (DbEntityValidationException e)
            {
                string strErr = "";
                foreach (var eve in e.EntityValidationErrors)
                {

                    strErr += string.Format("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
                        eve.Entry.Entity.GetType().Name, eve.Entry.State);
                    foreach (var ve in eve.ValidationErrors)
                    {
                        strErr += String.Format("- Property: \"{0}\", Error: \"{1}\"",
                            ve.PropertyName, ve.ErrorMessage);
                    }
                }
                throw;
            }
            catch (Exception ex)
            {
                string strError = ex.Message;
            }

            return blnIsSuccess;
        }

        public LookupParamViewModel UpdateLookupParam(LookupParamViewModel model)
        {
            LookupParamViewModel objLookupParam = new LookupParamViewModel();
            objLookupParam = ExecuteSP<LookupParamViewModel>("proc_UpdateLookupParam",
                                                             "@LookupParamID", model.LookupParamID,
                                                              "@LookupCode", model.LookupCode,
                                                             "@LookupValue", model.LookupValue,
                                                             "@ParamDesc", model.ParamDesc,
                                                             "@IsActive", model.IsActive,
                                                             "@IsDefault", model.IsDefault,
                                                             "@SeqNo", model.SeqNo == null ? 0 : model.SeqNo,
                                                             "@CreatedBy", model.CreatedBy,
                                                             "@CreatedDateTime", model.CreatedDateTime,
                                                             "@LastUpdatedBy", model.LastUpdatedBy,
                                                             "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();

            return objLookupParam;
        }

        public LookupParam InactivateLookupParam(LookupParam model)
        {
            LookupParam data = new LookupParam();
            data = ExecuteSP<LookupParam>("proc_InactiveLookupParamByID", "@LookupParamID", model.LookupParamID,
                                                              "@IsActive", model.IsActive,
                                                              "@LastUpdatedBy", model.LastUpdatedBy, "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();
            return data;
        }
    }

    public class TPFileTypeRepository : BaseRepository<CRMContext, TPFileTypePrefixCls>
    {
        public TPFileTypeViewModel UpdateFileType(TPFileTypeViewModel model)
        {
            TPFileTypeViewModel TPFileTypeViewModel = new TPFileTypeViewModel();

            if (model.EditMenuMode == 2)
            {
                TPFileTypeViewModel = ExecuteSP<TPFileTypeViewModel>("proc_UpdateTPFileType", "@TPFileTypeID", model.TPFileTypeID, "@TPFileTypeName", model.TPFileTypeName, "@CreatedBy", model.Createdby, "@CreatedDateTime", model.CreatedDateTime, "@LastUpdatedBy", model.LastUpdatedBy, "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();
            }
            else
            {
                TPFileTypeViewModel = ExecuteSP<TPFileTypeViewModel>("proc_UpdateTPFileTypePrefix",
                                                                    "@TPFileTypePrefixID", model.TPFileTypePrefixID,
                                                                    "@TPFileTypeID", model.TPFileTypeID,
                                                                    "@TPFileTypePrefix", model.TPFileTypePrefix,
                                                                    "@IsActive", model.IsActive,
                                                                     "@CreatedBy", model.Createdby,
                                                                     "@CreatedDateTime", model.CreatedDateTime,
                                                                     "@LastUpdatedBy", model.LastUpdatedBy,
                                                                     "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();
            }

            return TPFileTypeViewModel;
        }

        public TPFileTypePrefixCls GetFileTypePrefixByFileTypeID(int intTPFileTypePrefixID)
        {
            TPFileTypePrefixCls TPFileTypePrefix = new TPFileTypePrefixCls();
            TPFileTypePrefix = ExecuteSP<TPFileTypePrefixCls>("proc_GetTPFileTypePrefix", "@TPFileTypePrefixID", intTPFileTypePrefixID).FirstOrDefault();

            return TPFileTypePrefix;
        }

        public List<TPFileTypeViewModel> GetAllFileTypePrefix()
        {
            List<TPFileTypeViewModel> lstTPFileTypePrefix;
            lstTPFileTypePrefix = ExecuteSP<TPFileTypeViewModel>("proc_GetAllTPFileTypePrefix").ToList();

            return lstTPFileTypePrefix;
        }
    }
}
