using System;
using System.Collections.Generic;
using LHDN.CRM.Models.Models;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LHDN.CRM.Models.ViewModels;
using LHDN.CRM.BC.Common;
using System.Data;

namespace LHDN.CRM.BC.Repositories
{
    public class UserRepository : BaseRepository<CRMContext, UserProfile>
    {
        public UserInfoViewModel GetUserByLoginID(string strLoginID)
        {
            UserInfoViewModel user = new UserInfoViewModel();
            DataSet dsUser = ExecuteSPSingleConn("proc_GetUserProfileByLogin", "@LoginID", strLoginID);
           
            if (dsUser != null && dsUser.Tables.Count == 6)
            {
                if (dsUser.Tables[0] != null && dsUser.Tables[0].Rows.Count > 0)
                {
                    user = DataTableToObject<UserInfoViewModel>(dsUser.Tables[0]);
                }

                if (user != null)
                {
                    if (dsUser.Tables[1] != null && dsUser.Tables[1].Rows.Count > 0)
                    {
                        user.Manager = DataTableToObject<WorkflowUserViewModel>(dsUser.Tables[1]);
                    }

                    if (dsUser.Tables[2] != null && dsUser.Tables[2].Rows.Count > 0)
                    {
                        user.RoleGroups = DataTableToList<RoleInfo>(dsUser.Tables[2]).ToList();
                    }
                    else
                    {
                        throw new SystemAccessUnauthorizedException(String.Format(Constant.ExceptionMessages.NoUserLoginAccess, strLoginID));
                    }

                    if (dsUser.Tables[3] != null && dsUser.Tables[3].Rows.Count > 0)
                    {
                        user.RolesObj = DataTableToList<UserRole>(dsUser.Tables[3]).Distinct(new UserRoleComparer()).ToList();
                    }
                    else
                    {
                        throw new NotFoundException(String.Format(Constant.ExceptionMessages.NoUserLoginAccess, strLoginID));
                    }

                    if (dsUser.Tables[4] != null && dsUser.Tables[4].Rows.Count > 0)
                    {
                        UserUnitViewModel unit = new UserUnitViewModel();
                        UserUnitViewModel branch = new UserUnitViewModel();
                        //user.Unit = DataTableToObject<UserUnitViewModel>(dsUser.Tables[4]);
                        unit.UnitID = dsUser.Tables[4].Rows[0]["UnitId"].ToIntSafe();
                        unit.UnitName = dsUser.Tables[4].Rows[0]["UnitName"].ToStringSafe();
                        branch.UnitID = dsUser.Tables[4].Rows[0]["BranchID"].ToIntSafe();
                        branch.UnitName = dsUser.Tables[4].Rows[0]["BranchName"].ToStringSafe();
                        unit.Branch = branch;
                        user.Unit = unit;
                    }
                    if (dsUser.Tables[5] != null && dsUser.Tables[5].Rows.Count > 0)
                    {
                        if (dsUser.Tables[5].Rows[0]["HasManagerRole"] != null)
                        {
                            user.HasManagerRole = Convert.ToBoolean(dsUser.Tables[5].Rows[0]["HasManagerRole"].ToString());
                        }
                    }
                }

            }

            if (user == null)
            { throw new SystemAccessUnauthorizedException(String.Format(Constant.ExceptionMessages.NoUserLoginAccess, strLoginID)); }

            //if(dsUser.Tables[0] != null && dsUser.Tables[0].Rows[0]["ShortName"].ToString() !="")
            //{
            user.ShortName = dsUser.Tables[0].Rows[0]["ShortName"].ToString();
            //}
            //else
            //{
            //    user.ShortName = Utilities.GetUserShortname(user.DisplayName);
            //}

            DataSet ds = ExecuteSP("proc_GetTindakanSusulanByUserProfileID", "@UserProfileID", user.UserProfileID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    user.RoleTindakanSusulan = DataTableToList<TindakanSusulanViewModel>(ds.Tables[0]).ToList();
                }
            }


            return user;
        }

        public UserInfoViewModel GetUserEmailByLoginID(string strLoginID)
        {
            return ExecuteSP<UserInfoViewModel>("proc_GetUserEmailByLoginID", "@LoginID", strLoginID).FirstOrDefault();
        }

        public List<BranchListViewModel> GetBranchByHierarchy(int intUnitID)
        {
            DataSet ds = ExecuteSP("proc_GetBranchByHierarchy", "@UnitID", intUnitID);
            List<BranchListViewModel> lstBranch = new List<BranchListViewModel>();
            if (ds != null && ds.Tables.Count == 1)
                lstBranch = DataTableToList<BranchListViewModel>(ds.Tables[0]).ToList();

            return lstBranch;
        }

        public void UpdateProfileDateByRole(int intFunctionalRoleID, DateTime dtTrxDateTime)
        {
            ExecuteUpdateSP("proc_UpdateProfileDateByRole", "@FunctionalRoleID", intFunctionalRoleID,
                                            "@TrxDatetime", dtTrxDateTime);

        }

        public DataSet GetUserRoleListing()
        {
            return ExecuteSP("proc_GetUserRoleListing");
        }

        public UserRoleMatrix GetUserRoleMatrix()
        {
            UserRoleMatrix matrix = new UserRoleMatrix();
            DataSet ds = ExecuteSP("proc_GetUserRoleMatrix");

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

            if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
            {
                matrix.TaskCategory = DataTableToList<TaskCategory>(ds.Tables[1]).ToList();
            }
            if (ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0)
            {
                matrix.FunctionalTasks = DataTableToList<FunctionalTask>(ds.Tables[2]).ToList();
            }

            foreach (TaskCategory category in matrix.TaskCategory)
            {
                category.FunctionalTasks = matrix.FunctionalTasks.Where(x => x.Category == category.LookupParamID).ToList();
            }

            if (ds.Tables[3] != null && ds.Tables[3].Rows.Count > 0)
            {
                matrix.UserRoleTasks = DataTableToList<UserRoleTask>(ds.Tables[3]).ToList();
            }

            return matrix;
        }

        public List<TaskCategory> GetFuncTaskList(int intFunctionalRoleID = 0)
        {

            List<TaskCategory> taskCat = new List<TaskCategory>();

            List<FunctionalTask> funcTask = new List<FunctionalTask>();
            DataSet ds = ExecuteSP("proc_GetFuncTaskList", "@FunctionalRoleID", intFunctionalRoleID);

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

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

            foreach (TaskCategory task in taskCat)
            {
                task.FunctionalTasks = funcTask.Where(x => x.Category == task.LookupParamID).ToList();
                // Flag as header Select All True if every element is selected
                if (task.FunctionalTasks.Count == task.FunctionalTasks.Where(x => x.Selected == true).Count())
                {
                    task.SelectAll = true;
                }
                else
                {
                    task.SelectAll = false;
                }
            }

            return taskCat;
        }

        public UserRoleDetailViewModel GetUserRoleDetails(int intFunctionalRoleID = 0)
        {
            UserRoleDetailViewModel roleDetail = new UserRoleDetailViewModel();

            roleDetail = ExecuteSP<UserRoleDetailViewModel>("proc_GetUserRoleDetails", "@FunctionalRoleID", intFunctionalRoleID).FirstOrDefault();

            return roleDetail;
        }

        public int InsertFunctionalRoleDetails(UserRoleDetailViewModel roleDetail, string strLastUpdatedBy, DateTime dtLastUpdatedDateTime)
        {
            DataTable dTblFunctionalTask = RetriveFunctionalTaskTable(roleDetail);
            DataTable dTblRoleMember = RetriveRoleMembersTable(roleDetail);

            return ExecuteUpdateSP("proc_InsertFunctionalRoleDetails",
                                  "@RoleName", roleDetail.RoleName,
                                  "@IsActive", roleDetail.IsActive,
                                  "@tblFunctionalRoleTask", dTblFunctionalTask,
                                  "@tblRoleMembers", dTblRoleMember,
                                  "@CreatedBy", strLastUpdatedBy,
                                  "@CreatedDateTime", dtLastUpdatedDateTime);

        }

        public int UpdateFunctionalRoleDetails(UserRoleDetailViewModel roleDetail, string strLastUpdatedBy, DateTime dtLastUpdatedDateTime)
        {
            DataTable dTblFunctionalTask = RetriveFunctionalTaskTable(roleDetail);
            DataTable dTblRoleMember = RetriveRoleMembersTable(roleDetail);

            return ExecuteUpdateSP("proc_UpdateFunctionalRoleDetails",
                                  "@FunctionalRoleID", roleDetail.FunctionalRoleID,
                                  "@RoleName", roleDetail.RoleName,
                                  "@IsActive", roleDetail.IsActive,
                                  "@tblFunctionalRoleTask", dTblFunctionalTask,
                                  "@tblRoleMembers", dTblRoleMember,
                                  "@UpdatedBy", strLastUpdatedBy,
                                  "@UpdatedDateTime", dtLastUpdatedDateTime);


        }

        public int FindDuplicateRoleName(UserRoleDetailViewModel roleDetail)
        {
            return ExecuteUpdateSP("proc_FindDuplicateRoleName",
                                  "@FunctionalRoleID", roleDetail.FunctionalRoleID,
                                  "@RoleName", roleDetail.RoleName);

        }

        public List<RoleUnit> GetUserRoleMembersUnits(int intFunctionalRoleID = 0)
        {
            List<RoleUnit> roleUnits = new List<RoleUnit>();

            List<RoleMember> roleMembers = new List<RoleMember>();
            DataSet ds = ExecuteSP("proc_GetUserRoleMembersUnits", "@FunctionalRoleID", intFunctionalRoleID);

            if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                roleUnits = DataTableToList<RoleUnit>(ds.Tables[0]).ToList();
                roleUnits = roleUnits.OrderBy(x => x.BranchName).OrderBy(x => x.UnitName).ToList();
            }

            if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
            {
                roleMembers = DataTableToList<RoleMember>(ds.Tables[1]).ToList();
                //roleMembers = roleMembers.OrderBy(x => x.DisplayName).ToList();
            }

            foreach (RoleUnit unit in roleUnits)
            {
                unit.RoleMembers = roleMembers.Where(x => x.UnitID == unit.UnitID).OrderBy(x => x.DisplayName).ToList();
                if (!string.IsNullOrEmpty(unit.BranchName))
                    unit.DisplayBranchName = " (" + unit.AdditionalInfo + ")";
                else
                    unit.DisplayBranchName = "";
            }

            return roleUnits;
        }

        public List<PeoplePickerViewModel> SearchUsersByKeyword(PeoplePickerViewModel obj)
        {
            var data = ExecuteSP<PeoplePickerViewModel>("proc_SearchUsersByKeyword",
                                                          "@Keyword", obj.Keyword
                                                         ).ToList();

            return data;
        }

        private DataTable RetriveFunctionalTaskTable(UserRoleDetailViewModel roleDetail)
        {
            DataTable dTblFunctionalTask = new DataTable();

            dTblFunctionalTask.Columns.Add("FunctionalTaskID", typeof(int));

            List<FunctionalTask> lst = roleDetail.TaskCategory.SelectMany(x => x.FunctionalTasks).ToList();

            List<int> lstFunctionalTask = roleDetail.TaskCategory.SelectMany(x => x.FunctionalTasks).Select(a => new { FunctionalTaskID = a.FunctionalTaskID, Selected = a.Selected }).Where(b => b.Selected == true).Select(x => x.FunctionalTaskID).ToList();

            foreach (var value in lstFunctionalTask)
            {
                var row = dTblFunctionalTask.NewRow();

                row["FunctionalTaskID"] = value;
                dTblFunctionalTask.Rows.Add(row);
            }
            return dTblFunctionalTask;
        }

        private DataTable RetriveRoleMembersTable(UserRoleDetailViewModel roleDetail)
        {
            DataTable dTblRoleMember = new DataTable();
            dTblRoleMember.Columns.Add("UnitID", typeof(int));
            dTblRoleMember.Columns.Add("UserProfileID", typeof(long));

            List<RoleMember> lstRoleMember = roleDetail.RoleUnits.SelectMany(x => x.RoleMembers)
                        .Select(a => new RoleMember { UserProfileID = a.UserProfileID, UnitID = a.UnitID }).ToList();
            dTblRoleMember = lstRoleMember.ToDataTable<RoleMember>();
            dTblRoleMember.Columns.Remove("DisplayName");
            return dTblRoleMember;
        }


        public List<WorkflowUserViewModel> SearchUserByUnitID(int iUnitID)
        {

            List<WorkflowUserViewModel> lstUser = new List<WorkflowUserViewModel>();
            lstUser = ExecuteSP<WorkflowUserViewModel>("proc_SearchUserByUnitID",
                        "@UnitID", iUnitID).ToList();

            return lstUser;
        }


        public List<WorkflowUserViewModel> SearchUserByName(string strName)
        {

            List<WorkflowUserViewModel> lstUser = new List<WorkflowUserViewModel>();
            lstUser = ExecuteSP<WorkflowUserViewModel>("proc_SearchUserByName",
                        "@Name", strName).ToList();

            return lstUser;
        }

        public DataSet GetUserProfileList(string strSortExpr, int intPageIndex, int intPageSize, string Name, long lgManagerUserProfileID, int UnitID)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@Name", Name,
                                        "@ManagerUserProfileID",lgManagerUserProfileID,
                                        "@UnitID",UnitID

                                    });

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

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

        public UserProfileViewModel GetUserProfileDetailsByID(int intUserProfileID)
        {
            UserProfileViewModel lstUser = new UserProfileViewModel();
            lstUser = ExecuteSP<UserProfileViewModel>("proc_GetUserProfileByID", "@UserProfileID", intUserProfileID).FirstOrDefault();
            if (lstUser != null && lstUser.ManagerUserProfileID > 0)
            {
                lstUser.UserProfileManager = ExecuteSP<UserProfileManager>("proc_GetManagerUserProfileByID", "@UserProfileID", lstUser.ManagerUserProfileID).ToList();
            }

            if (lstUser != null && lstUser.UserProfileID > 0)
            {
                //lstUser.UserRoles = ExecuteSP<UserFunctionalRoleMember>("proc_GetUserProfileRoleByID", "@UserProfileID", lstUser.UserProfileID).ToList();
                var AllUserRole = ExecuteSP<UserFunctionalRoleMember>("proc_GetUserProfileRoleByID", "@UserProfileID", lstUser.UserProfileID).ToList();
                foreach (var value in AllUserRole)
                {
                    if (value.UnitID != lstUser.UnitID)
                    {
                        if (lstUser.AdditionalUnitID != value.UnitID)
                        {
                            lstUser.AdditionalUnitID = value.UnitID;
                            if (lstUser.AdditionalUnitIDMultiple == null)
                            {
                                lstUser.AdditionalUnitIDMultiple = lstUser.AdditionalUnitID.ToString();
                            }
                            else
                            {
                                lstUser.AdditionalUnitIDMultiple = lstUser.AdditionalUnitID + "," + lstUser.AdditionalUnitIDMultiple;
                            }

                        }
                    }
                }

                lstUser.UserFunctionRoles = AllUserRole.Where(x => x.UnitID == lstUser.UnitID).ToList();
                lstUser.UserAdditionalFunctionRoles = AllUserRole.Where(x => x.UnitID != lstUser.UnitID).ToList();
                lstUser.UserActiveCondition = GetExistingUserActiveCondition(lstUser.UserProfileID);
                lstUser.AdditionalUnitUserProfileManager = GetAdditionUnitManagerUserProfileID(lstUser.UserProfileID, lstUser.UnitID);
            }

            return lstUser;
        }

        public UserProfileViewModel UpdateUserProfileDetail(UserProfileViewModel model)
        {
            UserProfileViewModel modal = new UserProfileViewModel();

            modal = ExecuteSP<UserProfileViewModel>("proc_UpdateUserProfileDetail",
                                                    "@UserProfileID", model.UserProfileID,
                                                    "@LoginID", model.LoginID,
                                                    "@DisplayName", model.DisplayName,
                                                    "@Email", model.Email,
                                                    "@Gred", model.Gred,
                                                    "@NRIC", model.NRIC,
                                                    "@ManagerUserProfileID", model.ManagerUserProfileID,
                                                    "@UnitID", model.UnitID,
                                                    "@IsActive", model.IsActive,
                                                    "@JawatanID", model.JawatanID, // CR Jan2020 (Chew)
                                                    "@ShortName", model.ShortName,
                                                    "@CreatedBy", model.CreatedBy,
                                                    "@CreatedDateTime", model.CreatedDateTime,
                                                    "@LastUpdatedBy", model.LastUpdatedBy,
                                                    "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();


            return modal;
        }

        public void UpdateRolesForUser(UserProfileViewModel model, int roleId, bool isdefaultUnit)
        {
            // true mean get default unit ID, False mean get additional unit id
            if (isdefaultUnit)
            {
                ExecuteSP<UserProfileViewModel>("proc_UpdateUserFunctionalRole",
                                           "@FunctionalRoleID", roleId,
                                           "@UserProfileID", model.UserProfileID,
                                           "@UnitID", model.UnitID == null ? 0 : model.UnitID,
                                           "@CreatedBy", model.LastUpdatedBy,
                                           "@CreatedDateTime", model.LastUpdatedDateTime,
                                           "@ManagerUserProfileID", model.ManagerUserProfileID == null ? 0 : model.ManagerUserProfileID).FirstOrDefault();
            }
            else
            {
                ExecuteSP<UserProfileViewModel>("proc_UpdateUserFunctionalRole",
                           "@FunctionalRoleID", roleId,
                           "@UserProfileID", model.UserProfileID,
                           "@UnitID", model.AdditionalUnitID == null ? 0 : model.AdditionalUnitID,
                           "@CreatedBy", model.LastUpdatedBy,
                           "@CreatedDateTime", model.LastUpdatedDateTime,
                           "@ManagerUserProfileID", model.ManagerUserProfileID == null ? 0 : model.ManagerUserProfileID).FirstOrDefault();
            }

        }

        public void DeleteRolesForUser(UserProfileViewModel model, string RemoveFunctionalRolesID, bool isDefaultUnit)
        {
            if (isDefaultUnit)
            {
                ExecuteSP<UserProfileViewModel>("proc_DeleteFunctionalRoleByID",
                                  "@FunctionRolesID", RemoveFunctionalRolesID,
                                  "@UserProfileID", model.UserProfileID,
                                  "@UnitID", model.UnitID).FirstOrDefault();
            }
            else
            {
                ExecuteSP<UserProfileViewModel>("proc_DeleteFunctionalRoleByID",
                                  "@FunctionRolesID", RemoveFunctionalRolesID,
                                  "@UserProfileID", model.UserProfileID,
                                  "@UnitID", model.RemoveAdditionalUnitID).FirstOrDefault();
            }

        }
        public void DeleteUnitAndRole(UserProfileViewModel model, string RemoveFunctionalRolesID)
        {
            ExecuteSP<UserProfileViewModel>("proc_DeleteFunctionalRoleByUnitID",
                                            "@UnitID", RemoveFunctionalRolesID,
                                            "@UserProfileID", model.UserProfileID).FirstOrDefault();
        }

        //Delete All Default Unit role when default unit change by user
        public void DeleteAllRoleForDefaultUnitRole(UserProfileViewModel model, int OriUnitID)
        {
            ExecuteSP<UserProfileViewModel>("proc_DeleteDefaultUnitRoleByUnitID",
                                              "@UnitID", OriUnitID,
                                              "@UserProfileID", model.UserProfileID).FirstOrDefault();
        }

        public List<UserRoleListViewModel> SearchRoleByName(string strName)
        {

            List<UserRoleListViewModel> lstRole = new List<UserRoleListViewModel>();
            lstRole = ExecuteSP<UserRoleListViewModel>("proc_SearchRoleByName",
                        "@Name", strName).ToList();

            return lstRole;
        }

        public List<UnitTypeViewModel> SearchUnitTypeByName(string strName)
        {

            List<UnitTypeViewModel> lstRole = new List<UnitTypeViewModel>();
            lstRole = ExecuteSP<UnitTypeViewModel>("proc_SearchUnitTypeByName",
                        "@Name", strName).ToList();

            return lstRole;
        }

        public UserRequestProfileViewModel GetUserRequestProfileDetailByID(int intWFUserProfileId)
        {
            UserRequestProfileViewModel lstUser = new UserRequestProfileViewModel();
            lstUser = ExecuteSP<UserRequestProfileViewModel>("proc_GetWFUserProfileByID", "@WFUserProfileID", intWFUserProfileId).FirstOrDefault();
            if (lstUser != null && lstUser.ManagerUserProfileID > 0)
            {
                lstUser.UserProfileManager = ExecuteSP<WFUserProfileManager>("proc_GetWFManagerUserProfileByID", "@UserProfileID", lstUser.ManagerUserProfileID).ToList();
            }

            if (lstUser != null && lstUser.WFUserProfileID > 0)
            {
                var AllUserRole = ExecuteSP<WFUserFunctionalRoleMember>("proc_GetWFUserProfileRoleByID", "@WFUserProfileID", lstUser.WFUserProfileID).ToList();
                foreach (var value in AllUserRole)
                {
                    if (value.UnitID != lstUser.UnitID)
                    {
                        if (lstUser.AdditionalUnitID != value.UnitID)
                        {
                            lstUser.AdditionalUnitID = value.UnitID;
                            if (lstUser.AdditionalUnitIDMultiple == null)
                            {
                                lstUser.AdditionalUnitIDMultiple = lstUser.AdditionalUnitID.ToString();
                            }
                            else
                            {
                                lstUser.AdditionalUnitIDMultiple = lstUser.AdditionalUnitID + "," + lstUser.AdditionalUnitIDMultiple;
                            }

                        }
                    }
                }

                lstUser.UserFunctionRoles = AllUserRole.Where(x => x.UnitID == lstUser.UnitID).ToList();
                lstUser.UserAdditionalFunctionRoles = AllUserRole.Where(x => x.UnitID != lstUser.UnitID).ToList();
                lstUser.WorkFlowCatatanHistory = GetUserWorkFlowCatatanHistory(lstUser.WFUserProfileID);
                lstUser.UserWorkFlow = GetExistingUserWorkFlow(lstUser.WFUserProfileID);
                lstUser.UserWFActiveCondition = GetExistingWFUserActiveCondition(lstUser.WFUserProfileID);
                lstUser.AdditionalUnitUserProfileManager = GetWFAdditionUnitManagerUserProfileID(lstUser.WFUserProfileID, lstUser.UnitID);
            }

            return lstUser;
        }

        public void DeleteWFAllRoleForDefaultUnitRole(UserRequestProfileViewModel model, int OriUnitID)
        {
            ExecuteSP<UserProfileViewModel>("proc_DeleteWorkflowDefaultUnitRoleByUnitID",
                                            "@UnitID", OriUnitID,
                                            "@WFUserProfileID", model.WFUserProfileID).FirstOrDefault();
        }

        public UserRequestProfileViewModel UpdateWFUserProfileDetail(UserRequestProfileViewModel model)
        {
            UserRequestProfileViewModel modal = new UserRequestProfileViewModel();

            modal = ExecuteSP<UserRequestProfileViewModel>("proc_UpdateWorkflowUserProfileDetail",
                                                    "@WFUserProfileID", model.WFUserProfileID,
                                                    "@UserProfileID", modal.UserProfileID == null ? 0 : modal.UserProfileID,
                                                    "@LoginID", model.LoginID,
                                                    "@DisplayName", model.DisplayName,
                                                    "@Email", model.Email,
                                                    "@Gred", model.Gred,
                                                    "@NRIC", model.NRIC,
                                                    "@ManagerUserProfileID", model.ManagerUserProfileID,
                                                    "@UnitID", model.UnitID,
                                                    "@IsActive", model.IsActive,
                                                    "@JawatanID", model.JawatanID, // CR Jan2020 (Chew)
                                                    "@ShortName", model.ShortName,
                                                    "@WFStatusID", model.WFStatusID,
                                                    "@WFStatusDesc", model.WFStatusDesc,
                                                    "@JenisPermohonanType",model.JenisPermohonanTypeID,
                                                    "@CreatedBy", model.CreatedBy,
                                                    "@CreatedByName", model.CreatedByName,
                                                    "@CreatedByEmail", model.CreatedByEmail,
                                                    "@CreatedDateTime", model.CreatedDateTime,
                                                    "@LastUpdatedBy", model.LastUpdatedBy,
                                                    "@LastUpdatedByName", model.LastUpdatedByName,
                                                    "@LastUpdatedByEmail", model.LastUpdatedByEmail,
                                                    "@LastUpdatedDateTime", model.LastUpdatedDateTime,
                                                    "@IsSendApproval",model.IsSendApproval).FirstOrDefault();


            return modal;
        }

        public void UpdateWFRolesForUser(UserRequestProfileViewModel model, int roleId, bool isdefaultUnit)
        {
            // true mean get default unit ID, False mean get additional unit id
            if (isdefaultUnit)
            {
                ExecuteSP<UserRequestProfileViewModel>("proc_UpdateWFUserFunctionalRole",
                                                       "@FunctionalRoleID", roleId,
                                                       "@UserProfileID", model.WFUserProfileID,
                                                       "@UnitID", model.UnitID == null ? 0 : model.UnitID,
                                                       "@CreatedBy", model.CreatedBy,
                                                       "@CreatedDateTime", model.CreatedDateTime,
                                                       "@LastUpdatedBy", model.LastUpdatedBy,
                                                       "@LastUpdatedDateTime", model.LastUpdatedDateTime,
                                                       "@ManagerUserProfileID",model.ManagerUserProfileID ==null ? 0 : model.ManagerUserProfileID).FirstOrDefault();
            }
            else
            {
                ExecuteSP<UserRequestProfileViewModel>("proc_UpdateWFUserFunctionalRole",
                                                        "@FunctionalRoleID", roleId,
                                                        "@UserProfileID", model.WFUserProfileID,
                                                        "@UnitID", model.AdditionalUnitID == null ? 0 : model.AdditionalUnitID,
                                                        "@CreatedBy", model.CreatedBy,
                                                        "@CreatedDateTime", model.CreatedDateTime,
                                                       "@LastUpdatedBy", model.LastUpdatedBy,
                                                       "@LastUpdatedDateTime", model.LastUpdatedDateTime,
                                                       "@ManagerUserProfileID", model.ManagerUserProfileID == null ? 0 : model.ManagerUserProfileID).FirstOrDefault();
            }

        }

        public void DeleteWFRolesForUser(UserRequestProfileViewModel model, string RemoveFunctionalRolesID, bool isDefaultUnit)
        {
            if (isDefaultUnit)
            {
                ExecuteSP<UserRequestProfileViewModel>("proc_DeleteWorkflowFunctionalRoleByID",
                                                        "@FunctionRolesID", RemoveFunctionalRolesID,
                                                        "@UserProfileID", model.WFUserProfileID,
                                                        "@UnitID", model.UnitID).FirstOrDefault();
            }
            else
            {
                ExecuteSP<UserRequestProfileViewModel>("proc_DeleteWorkflowFunctionalRoleByID",
                                                      "@FunctionRolesID", RemoveFunctionalRolesID,
                                                      "@UserProfileID", model.WFUserProfileID,
                                                      "@UnitID", model.RemoveAdditionalUnitID).FirstOrDefault();
            }

        }

        public void DeleteWFUnitAndRole(UserRequestProfileViewModel model, string RemoveFunctionalRolesID)
        {
            ExecuteSP<UserRequestProfileViewModel>("proc_DeleteWorkflowFunctionalRoleByUnitID",
                                                    "@UnitID", RemoveFunctionalRolesID,
                                                    "@UserProfileID", model.WFUserProfileID).FirstOrDefault();
        }

        public DataSet GetWorkflowUserProfileList(string strSortExpr, int intPageIndex, int intPageSize, string Name, long lgManagerUserProfileID, int UnitID,int WFStatusID,int JenisPermohonanType)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@Name", Name,
                                        "@ManagerUserProfileID",lgManagerUserProfileID,
                                        "@UnitID",UnitID,
                                        "@WFStatusID",WFStatusID,
                                        "@JenisPermohonanType", JenisPermohonanType

                                    });

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

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

        public List<UserProfileViewModel> CheckDuplicateByLoginID(string UserLoginID)
        {
            List<UserProfileViewModel> lstUser = new List<UserProfileViewModel>();
            lstUser = ExecuteSP<UserProfileViewModel>("proc_GetUserProfilebyLoginID",
                                                      "@LoginID", UserLoginID).ToList();

            return lstUser;
        }

        public List<UserRequestProfileViewModel> CheckWFUserDuplicateLogin(string UserLoginID)
        {
            List<UserRequestProfileViewModel> lstUser = new List<UserRequestProfileViewModel>();
            lstUser = ExecuteSP<UserRequestProfileViewModel>("proc_GetWFUserProfilebyLoginID",
                                                      "@LoginID", UserLoginID).ToList();

            return lstUser;
        }



        public List<FunctionalRoleMember> GetFunctionalRoleMemberByUnitID(int UnitID)
        {
            List<FunctionalRoleMember> lst = new List<FunctionalRoleMember>();
            lst = ExecuteSP<FunctionalRoleMember>("proc_GetFunctionalRoleMemberByUnitID",
                                                  "@UnitID", UnitID).ToList();

            return lst;

        }

        public FunctionalRoleTask GetFunctionalRoleTaskByFunctionalRoleID(int FunctionalRoleID, int FunctionalTaskID)
        {
            FunctionalRoleTask lst = new FunctionalRoleTask();
            lst = ExecuteSP<FunctionalRoleTask>("proc_GetFunctionalRoleTaskByFunctionalRoleID",
                                                      "@FunctionalRoleID", FunctionalRoleID,
                                                      "@FunctionalTaskID", FunctionalTaskID).FirstOrDefault();

            return lst;
        }

        public List<UserWorkFlowViewModel> GetReviewerUserProfileById(int UnitID, string FunctionalTaskCode, bool isReviewAction)
        {
            List<UserWorkFlowViewModel> lst = new List<UserWorkFlowViewModel>();
            lst = ExecuteSP<UserWorkFlowViewModel>("proc_GetReviewerUserProfileByID",
                                                   "@UnitID", UnitID,
                                                   "@FunctionalTaskCode", FunctionalTaskCode,
                                                   "@IsReviewAction", isReviewAction).ToList();

            return lst;
        }

        public UserWorkFlowViewModel InsertUserWorkflow(UserWorkFlowViewModel model)
        {
            UserWorkFlowViewModel objUserWorkflow = new UserWorkFlowViewModel();

            objUserWorkflow = ExecuteSP<UserWorkFlowViewModel>("proc_InsertUserWorkflowProfile",
                                                    "@WorkflowID", model.WorkflowID,
                                                    "@WFUserProfileID", model.WFUserProfileID,
                                                    "@ActionType", model.ActionType,
                                                    "@ActionDesc", model.ActionTypeDesc,
                                                    "@AssignToTypeID", model.AssignToTypeID,
                                                    "@AssignToLogin", model.AssignToLogin,
                                                    "@AssignToName", model.AssignToName,
                                                    "@AssignToEmail", model.AssignToEmail,
                                                    "@WFStatusID", model.WFStatusID,
                                                    "@WFPrevStageStatusID", model.WFPrevStageStatusID,
                                                    "@StartDate", model.StartDate,
                                                    "@Remarks", model.Remarks,
                                                    "@AssignBy", model.AssignBy,
                                                    "@AssignByName", model.AssignByName,
                                                    "@AssignByEmail", model.AssignByEmail,
                                                    "@CreatedBy", model.CreatedBy,
                                                    "@CreatedDateTime", model.CreatedDateTime,
                                                    "@LastUpdatedBy", model.LastUpdatedBy,
                                                    "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();


            return objUserWorkflow;
        }

        public UserWorkFlowViewModel InsertUserWorkflowAssignUser(UserWorkFlowViewModel model)
        {

            return ExecuteSP<UserWorkFlowViewModel>("proc_InsertUserWorkflowAssignUser",
                                                     "@WorkflowID", model.WorkflowID,
                                                     "@AssignToLogin", model.AssignToLogin,
                                                     "@AssignToName", model.AssignToName,
                                                     "@AssignToEmail", model.AssignToEmail,
                                                     "@CreatedBy", model.CreatedBy,
                                                     "@CreatedDateTime", model.CreatedDateTime,
                                                     "@LastUpdatedBy", model.LastUpdatedBy,
                                                     "@LastUpdatedDateTime", model.LastUpdatedDateTime).FirstOrDefault();
        }

        public UserWorkFlowViewModel DeleteUserWorkflowAssignUser(UserWorkFlowViewModel userWFModel)
        {
            return ExecuteSP<UserWorkFlowViewModel>("proc_DeleteUserWorkflowAssignUser",
                                                    "@WorkflowID", userWFModel.WorkflowID,
                                                    "@AssignToLogin", userWFModel.AssignToLogin,
                                                    "@CreatedBy", userWFModel.CreatedBy,
                                                    "@LastUpdatedBy", userWFModel.LastUpdatedBy).FirstOrDefault();
        }

        public List<UserWorkflowCatatanHistoryViewModel> GetUserWorkFlowCatatanHistory(long WFUserProfileID)
        {
            List<UserWorkflowCatatanHistoryViewModel> lst = new List<UserWorkflowCatatanHistoryViewModel>();
            lst = ExecuteSP<UserWorkflowCatatanHistoryViewModel>("proc_GetUserWorkflowCatatanHistory",
                                                                "@WFUserProfileID", WFUserProfileID).ToList();

            return lst;
        }

        public UserWorkFlowViewModel GetExistingUserWorkFlow(long WFUserProfileID)
        {
            DataSet ds = ExecuteSP("proc_GetUserWorkflowByID", "@WFUserProfileID", WFUserProfileID);
            UserWorkFlowViewModel Workflow = DataTableToList<UserWorkFlowViewModel>(ds.Tables[0]).FirstOrDefault();
            if (Workflow != null)
            {
                if (ds != null)
                {
                    if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                    {
                        Workflow = DataTableToList<UserWorkFlowViewModel>(ds.Tables[0]).FirstOrDefault();
                    }

                    if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
                        Workflow.wfUser = DataTableToList<UserWorkFlowViewModel>(ds.Tables[1]).ToList();
                }
            }

            return Workflow;
        }

        public List<UserWFAssignNameViewModel> GetExistingUserNameWorkFlowByID(long WFUserProfileID)
        {
            List<UserWFAssignNameViewModel> lst = new List<UserWFAssignNameViewModel>();
            lst = ExecuteSP<UserWFAssignNameViewModel>("proc_GetUserWorkflowByName",
                                                       "@WFUserProfileID", WFUserProfileID).ToList();

            return lst;
        }

        public UserWorkFlowViewModel InsertUserWorkflowHistory(UserWorkFlowViewModel oriModel, DateTime now)
        {
            return ExecuteSP<UserWorkFlowViewModel>("proc_InsertUserWorkflowHistory",
                                                    "@WFUserProfileID", oriModel.WFUserProfileID,
                                                    "@WFStatusID", oriModel.WFStatusID,
                                                    "@StartDate", oriModel.StartDate,
                                                    "@CompletedDate", now,
                                                    "@DecisionID", oriModel.DecisionID,
                                                    "@DecisionDesc", oriModel.DecisionDesc,
                                                    "@AssignBy", oriModel.AssignBy,
                                                    "@AssignByName", oriModel.AssignByName,
                                                    "@AssignByEmail", oriModel.AssignByEmail,
                                                    "@CreatedDateTime", now,
                                                    "@ClosedRemark", oriModel.ClosedRemarks).FirstOrDefault();
        }

        public UserWorkFlowViewModel GetExistingUserWorkFlowHistoryByID(long WFUserProfileId)
        {
            UserWorkFlowViewModel lst = new UserWorkFlowViewModel();
            lst = ExecuteSP<UserWorkFlowViewModel>("proc_GetUserWorkflowHistoryByID",
                                                   "@WFUserProfileID", WFUserProfileId).FirstOrDefault();

            return lst;
        }

        public long UpdateWFUserProfileForAssign(UserWorkFlowViewModel objModel)
        {
            long i = 0;

            i = ExecuteUpdateSP("proc_UpdateWFUserProfileForUserWFAssign",
                "@WFUserProfileID", objModel.WFUserProfileID,
                "@WFStatusID", objModel.WFStatusID,
                "@WFStatusDesc", objModel.WFStatusDesc,
                "@RequestedBy", objModel.RequestedBy,
                "@RequestedByName", objModel.RequestedByName,
                "@RequestedByEmail", objModel.RequestedByEmail,
                "@RequestedDateTime", objModel.RequestedDateTime,
                "@ReviewedBy", objModel.ReviewBy,
                "@ReviewedByName", objModel.ReviewByName,
                "@ReviewedByEmail", objModel.ReviewByEmail,
                "@ReviewedDateTime", objModel.ReviewedDateTime,
                "@ApprovedBy", objModel.ApprovedBy,
                "@ApprovedByName", objModel.ApprovedByName,
                "@ApprovedByEmail", objModel.ApprovedByEmail,
                "@ApprovedDateTime", objModel.ApprovedDateTime == null ? (DateTime?)null : objModel.ApprovedDateTime,
                "@LastUpdatedBy", objModel.LastUpdatedBy,
                "@LastUpdatedByName", objModel.LastUpdatedByName,
                "@LastUpdatedByEmail", objModel.LastUpdatedByEmail,
                "@LastUpdatedDateTime", objModel.LastUpdatedDateTime);

            return i;
        }

        public List<UserRequestProfileViewModel> GetAllWFApproverUserList(int intWFStatusID)
        {
            List<UserRequestProfileViewModel> lst = new List<UserRequestProfileViewModel>();
            lst = ExecuteSP<UserRequestProfileViewModel>("proc_GetAllApprovedUserByApprovedID",
                                                         "@WFStatusID", intWFStatusID).ToList();

            return lst;
        }

        public UserRequestProfileViewModel GetNewAddedRevieobjUserwerByUser (UserRequestProfileViewModel objUser,string FunctionalTaskCode)
        {
            UserRequestProfileViewModel userNewReviewer = new UserRequestProfileViewModel();
            userNewReviewer = ExecuteSP<UserRequestProfileViewModel>("proc_GetNewReviewerByID",
                                                         "@UserProfileID", objUser.UserProfileID,
                                                         "@UnitID",objUser.UnitID,
                                                         "@FunctionalTaskCode", FunctionalTaskCode).FirstOrDefault();

            return userNewReviewer;
        }

        public List<UserRequestProfileViewModel> GetNewAddedReviewerForAdditionUnitByUser(UserRequestProfileViewModel objUser, string FunctionalTaskCode)
        {
            List<UserRequestProfileViewModel> userNewReviewer = new List<UserRequestProfileViewModel>();
            userNewReviewer = ExecuteSP<UserRequestProfileViewModel>("proc_GetAdditionUnitReviewerByID",
                                                         "@UserProfileID", objUser.UserProfileID,
                                                         "@UnitID", objUser.UnitID,
                                                         "@FunctionalTaskCode", FunctionalTaskCode).ToList();

            return userNewReviewer;
        }

        public List<UserProfileViewModel> GetAllUserProfileList()
        {
            List<UserProfileViewModel> lst = new List<UserProfileViewModel>();
            lst = ExecuteSP<UserProfileViewModel>("proc_GetAllUserProfile").ToList();

            return lst;
        }

        public UserRequestProfileViewModel UpdateUserProfile(UserRequestProfileViewModel WFUser)
        {

            UserRequestProfileViewModel modal = new UserRequestProfileViewModel();

            modal = ExecuteSP<UserRequestProfileViewModel>("proc_UpdateUserProfileDetail",
                                                    "@UserProfileID", WFUser.UserProfileID,
                                                    "@LoginID", WFUser.LoginID,
                                                    "@DisplayName", WFUser.DisplayName,
                                                    "@Email", WFUser.Email,
                                                    "@Gred", WFUser.Gred,
                                                    "@NRIC", WFUser.NRIC,
                                                    "@ManagerUserProfileID", WFUser.ManagerUserProfileID == null ? 0 : WFUser.ManagerUserProfileID,
                                                    "@UnitID", WFUser.UnitID,
                                                    "@IsActive", WFUser.IsActive,
                                                    "@JawatanID", WFUser.JawatanID, // CR Jan2020 (Chew)
                                                    "@ShortName", WFUser.ShortName,
                                                    "@CreatedBy", WFUser.CreatedBy,
                                                    "@CreatedDateTime", WFUser.CreatedDateTime,
                                                    "@LastUpdatedBy", WFUser.LastUpdatedBy,
                                                    "@LastUpdatedDateTime", WFUser.LastUpdatedDateTime).FirstOrDefault();


            return modal;

        }

        public List<WFUserFunctionalRoleMember> GetWFUserFunctionalRoleMember(long WFUserProfileID)
        {
            List<WFUserFunctionalRoleMember> lstWFUserFunctionRoleMember = new List<WFUserFunctionalRoleMember>();
            lstWFUserFunctionRoleMember = ExecuteSP<WFUserFunctionalRoleMember>("proc_GetWFUserProfileRoleByID", "@WFUserProfileID", WFUserProfileID).ToList();

            return lstWFUserFunctionRoleMember;
        }

        public List<UserFunctionalRoleMember> GetUserFunctionalRoleMember(long? UserProfileID)
        {
            List<UserFunctionalRoleMember> lstWFUserFunctionRoleMember = new List<UserFunctionalRoleMember>();
            lstWFUserFunctionRoleMember = ExecuteSP<UserFunctionalRoleMember>("proc_GetUserProfileRoleByID", "@UserProfileID", UserProfileID).ToList();

            return lstWFUserFunctionRoleMember;
        }



        public void InsertUserFunctionalRoleMember(WFUserFunctionalRoleMember objUserRole, long? UserProfileID)
        {
            ExecuteSP<UserRequestProfileViewModel>("proc_InsertUserFunctionalRoleMember",
                                      "@FunctionalRoleID", objUserRole.FunctionalRoleID,
                                      "@UserProfileID", UserProfileID,
                                      "@UnitID", objUserRole.UnitID,
                                      "@CreatedBy", objUserRole.CreatedBy,
                                      "@CreatedDateTime", objUserRole.CreatedDatetime).FirstOrDefault();
        }

        public void UpdateUserFunctionalRoleMember(WFUserFunctionalRoleMember objUserRole, long? UserProfileID)
        {
            ExecuteSP<UserRequestProfileViewModel>("proc_InsertUserFunctionalRoleMember",
                                      "@FunctionalRoleID", objUserRole.FunctionalRoleID,
                                      "@UserProfileID", UserProfileID,
                                      "@UnitID", objUserRole.UnitID,
                                      "@CreatedBy", objUserRole.CreatedBy,
                                      "@CreatedDateTime", objUserRole.CreatedDatetime).FirstOrDefault();
        }

        public void UpdateUserFunctionalRoleMember(long WFUserProfileID, long? UserProfileID)
        {
            ExecuteSP("proc_UpdateUserFunctionalRoleMember",
                        "@WFUserProfileID", WFUserProfileID,
                        "@UserProfileID", UserProfileID);
        }

        public void UpdateWFUserProfileStatus(UserRequestProfileViewModel objWFUser)
        {
            ExecuteSP("proc_UpdateWFUserWFStatus",
                          "@WFUserProfileID", objWFUser.WFUserProfileID,
                          "@UserProfileID", objWFUser.UserProfileID,
                          "@WFStatusID", objWFUser.WFStatusID,
                          "@WFStatusDesc", objWFUser.WFStatusDesc,
                          "@JenisPermohonanType",objWFUser.JenisPermohonanTypeID,
                          "@LastUpdatedBy", objWFUser.LastUpdatedBy,
                          "@LastUpdatedByName", objWFUser.LastUpdatedByName,
                          "@LastUpdatedDateTime", objWFUser.LastUpdatedDateTime);
        }

        public UserRequestProfileViewModel UpdateEscalateCaseToDirectManager(long? UserProfileID)
        {
            UserRequestProfileViewModel modal = new UserRequestProfileViewModel();
            modal = ExecuteSP<UserRequestProfileViewModel>("proc_EscalateCaseToDirectManager",
                                                          "@UserProfileID", UserProfileID).FirstOrDefault() ;

            return modal;
           //ExecuteSP("proc_EscalateCaseToDirectManager",
           //           "@UserProfileID", UserProfileID);
        }

        public DataSet GetDashboardMyPendingUserRequest(string strAssignTo, UserRequestDashboardViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@AssignTo", strAssignTo});

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

            return ExecuteSP("proc_GetDashboardMyPendingUserRequest", lstParam.ToArray());

        }


        public DataSet GetDashboardMyCreatedUserRequest(string strAssignFrom, UserRequestDashboardViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@AssignFrom", strAssignFrom});

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

            return ExecuteSP("proc_GetDashboardMyCreatedUserRequest", lstParam.ToArray());

        }

        public UserWFActiveConditionViewModel GetExistingWFUserActiveCondition(long WFUserProfileID)
        {
            UserWFActiveConditionViewModel objWFUserActiveCondition = new UserWFActiveConditionViewModel();
            objWFUserActiveCondition = ExecuteSP<UserWFActiveConditionViewModel>("proc_GetWFUserActiveConditionByID", "@WFUserProfileID", WFUserProfileID).FirstOrDefault();

            return objWFUserActiveCondition;
        }

        public long UpdateWFUserActiveCondition(UserRequestProfileViewModel model)
        {
            long i = 0;
            i = ExecuteUpdateSP("proc_UpdateWFUserWFAktiveCondition",
                                 "@WFActivePeriodID",model.WFActivePeriodID,
                                        "@WFUserProfileID", model.WFUserProfileID,
                                        "@ActiveCondition", model.ActiveCondition,
                                        "@ActiveConditionDesc", model.ActiveConditionDesc,
                                        "@DormantDateFrom", model.DormantDateFrom,
                                        "@DormantDateTo", model.DormantDateTo,
                                        "@ExpiryDate", model.ExpiryDate);

            return i;
        }

        public void UpdateUserActiveCondition(long WFUserProfileID,long? UserProfileID)
        {
            ExecuteSP("proc_UpdateUserActiveCondition",
                          "@WFUserProfileID", WFUserProfileID,
                          "@UserProfileID", UserProfileID);
        }

        public UserWFActiveConditionViewModel GetExistingUserActiveCondition(long? UserProfileID)
        {
            UserWFActiveConditionViewModel objWFUserActiveCondition = new UserWFActiveConditionViewModel();
            objWFUserActiveCondition = ExecuteSP<UserWFActiveConditionViewModel>("proc_GetUserActiveConditionByID", "@UserProfileID", UserProfileID).FirstOrDefault();

            return objWFUserActiveCondition;
        }

        public void UpdateUserProfileStatusByActiveCondition()
        {
            ExecuteSP("proc_UpdateUserProfileStatusByActiveCondition");
        }

        public List<UserWFActiveConditionViewModel> GetAllUserActionConditionList()
        {
            List<UserWFActiveConditionViewModel> objAllUserActiveCondition = new List<UserWFActiveConditionViewModel>();
            objAllUserActiveCondition = ExecuteSP<UserWFActiveConditionViewModel>("proc_GetAllUserActiveCondition").ToList();

            return objAllUserActiveCondition;
        }

        public UserRequestProfileViewModel CancelRequestWFUserProfile(UserRequestProfileViewModel modal)
        {
            UserRequestProfileViewModel model = new UserRequestProfileViewModel();
            model = ExecuteSP<UserRequestProfileViewModel>("proc_CancelUserProfileRequest", "@WFUserProfileID", modal.WFUserProfileID).FirstOrDefault();
            return model;
        }

        public UserRequestProfileViewModel InsertUserWFHistoryForCancelRequest(UserRequestProfileUpdateViewModel modal,DateTime now)
        {
            UserRequestProfileViewModel model = new UserRequestProfileViewModel();
            model = ExecuteSP<UserRequestProfileViewModel>("proc_InsertWFHistoryCancelUserProfileRequest",
                                                           "@WFUserProfileID", modal.WFUserProfile.WFUserProfileID,
                                                           "@AssignBy" , modal.WFUserProfile.LastUpdatedBy,
                                                           "@AssignByName", modal.WFUserProfile.LastUpdatedByName,
                                                           "@AssignByEmail", modal.WFUserProfile.LastUpdatedByEmail,
                                                           "@WFStatusID",modal.WFUserProfile.WFStatusID,
                                                           "@DecisionID",modal.WFUserProfile.UserWorkFlow.DecisionID,
                                                           "@DecisionDesc", modal.WFUserProfile.UserWorkFlow.DecisionDesc,
                                                           "@Remarks",modal.WFUserProfile.UserWorkFlow.Remarks,
                                                           "@WFPreStageStatusID", modal.WFUserProfile.UserWorkFlow.WFPrevStageStatusID,
                                                           "@LastUpdatedByDateTime",modal.WFUserProfile.LastUpdatedDateTime,
                                                           "@StartDate", modal.WFUserProfile.UserWorkFlow.StartDate,
                                                           "@CompletedDate", now).FirstOrDefault();
            return model;
        }

        public void InsertUserWFHistoryForClosedStatus(UserRequestProfileViewModel objWFUser)
        {
            ExecuteSP("proc_InsertWFHistoryForClosedStatus",
                                                           "@WFUserProfileID", objWFUser.WFUserProfileID,
                                                           "@AssignBy", objWFUser.LastUpdatedBy,
                                                           "@AssignByName", objWFUser.LastUpdatedByName,
                                                           "@AssignByEmail", objWFUser.LastUpdatedByEmail,
                                                           "@WFStatusID", objWFUser.WFStatusID,
                                                           "@Remarks", objWFUser.Remarks,
                                                           "@LastUpdatedByDateTime", objWFUser.LastUpdatedDateTime);
        }

        public UserActiveConditionViewModel GetExistingUserActiveCondition(long UserProfileID)
        {
            UserActiveConditionViewModel objUserActiveCondition = new UserActiveConditionViewModel();
            objUserActiveCondition = ExecuteSP<UserActiveConditionViewModel>("proc_GetUserActiveConditionByID", "@UserProfileID", UserProfileID).FirstOrDefault();

            return objUserActiveCondition;
        }

        public long UpdateUserProfileActiveCondition(UserProfileViewModel model)
        {
            long i = 0;
            i = ExecuteUpdateSP("proc_UpdateUserAktiveCondition",
                                 "@ActivePeriodID", model.ActivePeriodID,
                                        "@UserProfileID", model.UserProfileID,
                                        "@ActiveCondition", model.ActiveCondition,
                                        "@ActiveConditionDesc", model.ActiveConditionDesc,
                                        "@DormantDateFrom", model.DormantDateFrom,
                                        "@DormantDateTo", model.DormantDateTo,
                                        "@ExpiryDate", model.ExpiryDate);

            return i;
        }

        public UserRequestProfileViewModel UpdateWFProfileDetailByUserProfileID(long UserProfileID)
        {
            UserRequestProfileViewModel model = new UserRequestProfileViewModel();
            model = ExecuteSP<UserRequestProfileViewModel>("proc_UpdateWFProfileDetailByUserProfileID",
                                                           "@UserProfileID", UserProfileID).FirstOrDefault();

            return model;
        }

        public void UpdateWFUserFunctionalRoleMemberByID(UserRequestProfileViewModel objUser, long UserProfileID)
        {
            ExecuteSP("proc_UpdateWFUserFunctionalRoleMemberByID",
                     "@WFUserProfileID", objUser.WFUserProfileID,
                     "@UserProfileID",  UserProfileID,
                     "@WFFunctionalRoleMemberID", objUser.WFUserFunctionalRoleMemberID);
        }

        public void UpdateWFUserActiveConditionByID(UserRequestProfileViewModel objUserRequest, long UserProfileID)
        {
            ExecuteSP("proc_UpdateWFUserActiveConditionByID",
                      "@WFUserProfileID", objUserRequest.WFUserProfileID,
                      "@UserProfileID", UserProfileID,
                      "@WFActivePeriod", objUserRequest.WFActivePeriodID);
        }

        public WFUserFunctionalRoleMember GetExistingWFUserFunctionalRoleMember(long WFUserProfileID)
        {
            WFUserFunctionalRoleMember model = new WFUserFunctionalRoleMember();
            model = ExecuteSP<WFUserFunctionalRoleMember>("proc_GetWFUserProfileRoleByID", "@WFUserProfileID", WFUserProfileID).FirstOrDefault();
            return model;
        }

        public UserRequestProfileViewModel UpdateEscalateCaseToDirectManagerInUserProfile(long UserProfileID,int? UnitID)
        {
            UserRequestProfileViewModel modal = new UserRequestProfileViewModel();
            modal = ExecuteSP<UserRequestProfileViewModel>("proc_EscalateCaseToDirectManagerFromUserProfile",
                                                          "@UserProfileID", UserProfileID,
                                                          "@UnitID",UnitID).FirstOrDefault();

            return modal;
        }

        public UserWorkFlowViewModel UpdateWorkFlowHistoryForApprovedStatus(UserWorkFlowViewModel oriModel, DateTime now)
        {
            return ExecuteSP<UserWorkFlowViewModel>("proc_InsertUserWorkflowHistoryForApprovedStatus",
                                                    "@WFUserProfileID", oriModel.WFUserProfileID,
                                                    "@AssignToTypeID",oriModel.AssignToTypeID,
                                                    "@WFStatusID", oriModel.WFStatusID,
                                                    "@StartDate", now,
                                                    "@CompletedDate", now,
                                                    "@DecisionID", oriModel.DecisionID,
                                                    "@DecisionDesc", oriModel.DecisionDesc,
                                                    "@WFPrevStageStatusID", oriModel.WFPrevStageStatusID,
                                                    "@AssignBy", oriModel.AssignBy,
                                                    "@AssignByName", oriModel.AssignByName,
                                                    "@AssignByEmail", oriModel.AssignByEmail,
                                                    "@CreatedDateTime", now,
                                                    "@ClosedRemark", oriModel.ClosedRemarks).FirstOrDefault();
        }

        //public List<UserRequestProfileViewModel> GetAllWFClosedUserList(int intWFStatusID,string FunctionalTaskCode)
        //{
        //    List<UserRequestProfileViewModel> lst = new List<UserRequestProfileViewModel>();
        //    lst = ExecuteSP<UserRequestProfileViewModel>("proc_GetAllClosedUserByID",
        //                                                 "@WFStatusID", intWFStatusID,
        //                                                 "@FunctionalTaskCode", FunctionalTaskCode).ToList();

        //    return lst;
        //}

        public List<UserWorkFlowViewModel> GetExistingUserWorkFlowByID(UserRequestProfileViewModel objUser,int WFStatusID, bool IsDelete)
        {
            List<UserWorkFlowViewModel> objWFDetail = new List<UserWorkFlowViewModel>();
            objWFDetail = ExecuteSP<UserWorkFlowViewModel>("proc_GetWorkFlowUserRequestByID",
                                                         "@UnitID", objUser.UnitID,
                                                         "@WFStatusID", WFStatusID,
                                                         "@UserProfileID",objUser.UserProfileID,
                                                         "@IsDelete", IsDelete).ToList();

            return objWFDetail;
        }

        public UserWorkFlowViewModel GetExistingAssignUserByWorkFlowByID(long WorkflowID,string AssignToLogin)
        {
            UserWorkFlowViewModel objWFDetail = new UserWorkFlowViewModel();
            objWFDetail = ExecuteSP<UserWorkFlowViewModel>("proc_GetWorkFlowAssignUserByWorkflowID",
                                                         "@WorkflowID", WorkflowID,
                                                         "@AssignToLogin", AssignToLogin).FirstOrDefault();

            return objWFDetail;
        }

        public List<WFUserProfileManager> GetWFAdditionUnitManagerUserProfileID(long WFUserProfileID,int? intMainUnitID)
        {
            List<WFUserProfileManager> objWFManagerUserProfile = new List<WFUserProfileManager>();
            objWFManagerUserProfile = ExecuteSP<WFUserProfileManager>("proc_GetWFAdditionMangerUserProfileByID",
                                                                    "@WFUserProfileID", WFUserProfileID,
                                                                    "@UnitID" , intMainUnitID).ToList();

            return objWFManagerUserProfile;
        }
        public List<UserProfileManager> GetAdditionUnitManagerUserProfileID(long UserProfileID, int? intMainUnitID)
        {
            List<UserProfileManager> objWFManagerUserProfile = new List<UserProfileManager>();
            objWFManagerUserProfile = ExecuteSP<UserProfileManager>("proc_GetAdditionMangerUserProfileByID",
                                                                    "@UserProfileID", UserProfileID,
                                                                    "@UnitID", intMainUnitID).ToList();

            return objWFManagerUserProfile;
        }

        public void UpdateAdditionUnitMangerIDByID(long? ManagerUserProfileID, int? UnitID,long UserProfileID, bool IsUserProfileScreen)
        {
            if(IsUserProfileScreen)
            {
                ExecuteSP<UserRequestProfileViewModel>("proc_UpdateUserProfileUnitAdditionalManagerIDByID",
                                                     "@ManagerUserProfileID", ManagerUserProfileID == null ? 0 : ManagerUserProfileID,
                                                     "@UnitID", UnitID,
                                                     "@UserProfileID", UserProfileID).FirstOrDefault();
            }
            else
            {
                ExecuteSP<UserRequestProfileViewModel>("proc_UpdateUnitAdditionalManagerIDByID",
                                                     "@ManagerUserProfileID", ManagerUserProfileID == null ? 0 : ManagerUserProfileID,
                                                     "@UnitID", UnitID,
                                                     "@WFUserProfileID", UserProfileID).FirstOrDefault();
            }
             
        }
    }

}
