using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using GB5Shared.Query.FrameWork.CriteriaConfig; namespace FrameworkDAL.Query.User { public static class UserQB { public const string GET_USER = @"SELECT U.USERID AS UserId, U.USERCODE AS UserCode, U.USERNAME AS UserName, U.USERLOGINNAME AS UserLoginName, U.PASSWORD AS UserPassword, U.DOB AS UserDOB, U.USERTYPE AS UserType, U.NATURE AS UserNature, U.SECURITYLEVEL AS UserSecurityLevel, U.SECURITYQUESTIONID AS SecurityQuestionId, SQ.SECURITYQUESTION AS SecurityQuestionQuestion, U.SECURITYANSWER AS UserSecurityAnswer, U.REMARKS AS UserRemarks, U.ISEMPLOYEE AS UserIsEmployee, U.ISPARTNER AS UserIsPartner, U.PARTNERID AS UserPartnerId, U.PARTNERUSERID AS PartnerUserId, U.USERCODE AS PartnerUserCode, U.USERNAME AS PartnerUserName, U.VALIDFROM AS UserValidFrom, U.VALIDTO AS UserValidTo, U.ROLEID AS RoleId, R.ROLECODE AS RoleCode, R.ROLENAME AS RoleName, U.PRIMARYMAIL AS UserPrimaryMail, U.PRIMARYMOBILE AS UserPrimaryMobile, U.PRIMARYPHONE AS UserPrimaryPhone, U.PASSWORDPOLICYID AS PasswordPolicyId, PP.PASSWORDPOLICYCODE AS PasswordPolicyCode, PP.PASSWORDPOLICYNAME AS PasswordPolicyName, U.WORKOUID AS UserWorkOuId, U.WORKPERIODID AS UserWorkPeriodId, U.WORKPARTYBRANCHID AS UserWorkPartyBranchId, U.WORKSTOREID AS UserWorkStoreId, U.WORKDATE AS UserWorkDate, U.LASTSUCCESSLOGINON AS UserLastSuccessLoginOn, U.LASTFAILUREATTEMPTON AS UserLastFailureAttemptOn, U.PREVIOUSPASSWORD1 AS UserPreviousPassword1, U.PREVIOUSPASSWORD2 AS UserPreviousPassword2, U.PREVIOUSPASSWORD3 AS UserPreviousPassword3, U.PASSWORDCHANGEDON AS UserPasswordChangedOn, U.PREVIOUSLOGIN AS UserPreviousLogin, U.WORKREGIONID AS UserWorkRegionId, U.MODEOFOPERATION AS UserModeofOperation, U.USERCRITERIACONFIGID AS UserCriteriaConfigId, U.SORTORDER AS UserSortOrder, U.STATUS AS UserStatus, U.VERSION AS UserVersion, U.SOURCETYPE AS UserSourceType, U.DEFAULTIMAGEID AS ImageId, I.IMAGELINKURL AS ImageLinkURL, I.IMAGENAME AS ImageName, U.CREATEDBYID AS UserCreatedById, U.CREATEDON AS UserCreatedOn, U.MODIFIEDBYID AS UserModifiedById, U.MODIFIEDON AS UserModifiedOn, U.DEFAULTCMSID AS UserDefaultCMSId, U.USERDATEFORMAT AS UserDateFormat, U.USERCURRENCYFORMAT AS UserCurrencyFormat, U.USERTIMEFORMAT AS UserTimeFormat, U.USERQUANTITYFORMAT AS UserQuantityFormat, U.THEMEOPTION AS UserThemeOption, U.NOOFSESSIONS AS UserNoOfSessions, U.LOGINONWORKDAYSONLY AS UserLoginonWorkDaysOnly, U.LOGINFROMTIME AS UserLoginFromTime, U.LOGINTOTIME AS UserLoginToTime, U.LOGINMACHINE AS UserLoginMachine, U.LOGINIP AS UserLoginIp, U.DIGITALSIGNEDFILE AS UserDigitalSignedFile, U.DIGITALFILEPASSWORD AS UserDigitalFilePassword, M.MENUCODE AS DefaultMenuCode, M.MENUNAME AS DefaultMenuName, U.WORKFINANCEBOOKID AS WorkFinanceBookId, U.TIMEZONEID AS TimeZoneId, U.DEFAULTDEVICEID AS DefaultDeviceId, U.DELIMITER AS UserDelimiter, U.ISOUTOFOFFICE AS UserIsOutOfOffice, U.OUTOFOFFICEFROMDATE AS UserOutOfOfficeFromDate, U.OUTOFOFFICETODATE AS UserOutOfOfficeToDate, U.OUTOFOFFICEREMARKS AS UserOutOfOfficeRemarks, U.ESIGNATURECMSID AS UserESignaturecmsId, U.MMAILID AS MMailId, MA.MAILADDRESS AS MMailMailId, U.PUNCHTYPE AS UserPunchType, U.MFAUSERSETTING AS UserMFAUserSetting, U.MFAUSERACCOUNTID AS UserMFAUserAccountId, U.MFAUSERSECRETKEY AS UserMFAUserSecretKey, U.MFAQRCODE AS UserMFAQRCode, U.SHOWQRCODE AS UserShowQRCode, U.NUMBEROFFAILUREATTEMPTS AS UserNumberOfFailureAttempts, U.THUMBNAIL AS UserThumbNail, U.THEMEMODE AS UserThemeMode, U.LANGUAGEID AS UserLanguageId, U.CRITERIARUNTYPE AS UserCriteriaRunType, U.DIGITALKEYLOCATION AS UserDigitalKeyLocation, ISNULL(E.DEPARTMENTID, -1) AS DepartmentId, DEP.DEPARTMENTCODE AS DepartmentCode, DEP.DEPARTMENTNAME AS DepartmentName, ISNULL(E.DESIGNATIONID, -1) AS DesignationId, DES.GCMCODE AS DesignationCode, DES.GCMNAME AS DesignationName, MD.MODULEID AS DefaultModuleId, MD.MODULECODE AS DefaultModuleCode, MD.MODULENAME AS DefaultModuleName, U.DEFAULTMENUID AS DefaultMenuId FROM MUSER U LEFT JOIN MMENU M ON U.DEFAULTMENUID = M.MENUID LEFT JOIN MMODULE MD ON M.MODULEID = MD.MODULEID LEFT JOIN MIMAGE I ON U.DEFAULTIMAGEID = I.IMAGEID LEFT JOIN MMAIL MA ON U.MMAILID = MA.MAILID LEFT JOIN MPASSWORDPOLICY PP ON U.PASSWORDPOLICYID = PP.PASSWORDPOLICYID LEFT JOIN MROLE R ON U.ROLEID = R.ROLEID LEFT JOIN MSECURITYQUESTION SQ ON U.SECURITYQUESTIONID = SQ.SECURITYQUESTIONID LEFT JOIN MEMPLOYEE E ON U.USERID = E.EMPLOYEEID LEFT JOIN MDEPARTMENT DEP ON E.DEPARTMENTID = DEP.DEPARTMENTID LEFT JOIN MGCM DES ON E.DESIGNATIONID = DES.GCMID WHERE U.USERID = @userid AND U.TENANTID = @clientid; "; public const string GET_USERID_PG = @"SELECT U.userid AS ""UserId"", U.usercode AS ""UserCode"", U.username AS ""UserName"", U.userloginname AS ""UserLoginName"", U.password AS ""UserPassword"", U.dob AS ""UserDOB"", U.usertype AS ""UserType"", U.nature AS ""UserNature"", U.securitylevel AS ""UserSecurityLevel"", U.securityquestionid AS ""SecurityQuestionId"", SQ.securityquestion AS ""SecurityQuestionQuestion"", U.securityanswer AS ""UserSecurityAnswer"", U.remarks AS ""UserRemarks"", U.isemployee AS ""UserIsEmployee"", U.ispartner AS ""UserIsPartner"", U.partnerid AS ""UserPartnerId"", U.partneruserid AS ""PartnerUserId"", U.usercode AS ""PartnerUserCode"", U.username AS ""PartnerUserName"", U.validfrom AS ""UserValidFrom"", U.validto AS ""UserValidTo"", U.roleid AS ""RoleId"", R.rolecode AS ""RoleCode"", R.rolename AS ""RoleName"", U.primarymail AS ""UserPrimaryMail"", U.primarymobile AS ""UserPrimaryMobile"", U.primaryphone AS ""UserPrimaryPhone"", U.passwordpolicyid AS ""PasswordPolicyId"", PP.passwordpolicycode AS ""PasswordPolicyCode"", PP.passwordpolicyname AS ""PasswordPolicyName"", U.workouid AS ""UserWorkOuId"", U.workperiodid AS ""UserWorkPeriodId"", U.workpartybranchid AS ""UserWorkPartyBranchId"", U.workstoreid AS ""UserWorkStoreId"", U.workdate AS ""UserWorkDate"", U.lastsuccessloginon AS ""UserLastSuccessLoginOn"", U.lastfailureattempton AS ""UserLastFailureAttemptOn"", U.previouspassword1 AS ""UserPreviousPassword1"", U.previouspassword2 AS ""UserPreviousPassword2"", U.previouspassword3 AS ""UserPreviousPassword3"", U.passwordchangedon AS ""UserPasswordChangedOn"", U.previouslogin AS ""UserPreviousLogin"", U.workregionid AS ""UserWorkRegionId"", U.modeofoperation AS ""UserModeofOperation"", U.usercriteriaconfigid AS ""UserCriteriaConfigId"", U.sortorder AS ""UserSortOrder"", U.status AS ""UserStatus"", U.version AS ""UserVersion"", U.sourcetype AS ""UserSourceType"", U.defaultimageid AS ""ImageId"", I.imagelinkurl AS ""ImageLinkURL"", I.imagename AS ""ImageName"", U.createdbyid AS ""UserCreatedById"", U.createdon AS ""UserCreatedOn"", U.modifiedbyid AS ""UserModifiedById"", U.modifiedon AS ""UserModifiedOn"", U.defaultcmsid AS ""UserDefaultCMSId"", U.userdateformat AS ""UserDateFormat"", U.usercurrencyformat AS ""UserCurrencyFormat"", U.usertimeformat AS ""UserTimeFormat"", U.userquantityformat AS ""UserQuantityFormat"", U.themeoption AS ""UserThemeOption"", U.noofsessions AS ""UserNoOfSessions"", U.loginonworkdaysonly AS ""UserLoginonWorkDaysOnly"", U.loginfromtime AS ""UserLoginFromTime"", U.logintotime AS ""UserLoginToTime"", U.loginmachine AS ""UserLoginMachine"", U.loginip AS ""UserLoginIp"", U.digitalsignedfile AS ""UserDigitalSignedFile"", U.digitalfilepassword AS ""UserDigitalFilePassword"", U.defaultmenuid AS ""DefaultMenuId"", M.menucode AS ""DefaultMenuCode"", M.menuname AS ""DefaultMenuName"", U.workfinancebookid AS ""WorkFinanceBookId"", U.timezoneid AS ""TimeZoneId"", U.defaultdeviceid AS ""DefaultDeviceId"", U.delimiter AS ""UserDelimiter"", U.isoutofoffice AS ""UserIsOutOfOffice"", U.outofofficefromdate AS ""UserOutOfOfficeFromDate"", U.outofofficetodate AS ""UserOutOfOfficeToDate"", U.outofofficeremarks AS ""UserOutOfOfficeRemarks"", U.esignaturecmsid AS ""UserESignaturecmsId"", U.mmailid AS ""MMailId"", MA.mailaddress AS ""MMailMailId"", U.punchtype AS ""UserPunchType"", U.mfausersetting AS ""UserMFAUserSetting"", U.mfauseraccountid AS ""UserMFAUserAccountId"", U.mfausersecretkey AS ""UserMFAUserSecretKey"", U.mfaqrcode AS ""UserMFAQRCode"", U.showqrcode AS ""UserShowQRCode"", U.numberoffailureattempts AS ""UserNumberOfFailureAttempts"", U.thumbnail AS ""UserThumbNail"", U.thememode AS ""UserThemeMode"", U.languageid AS ""UserLanguageId"", U.criteriaruntype AS ""UserCriteriaRunType"", U.digitalkeylocation AS ""UserDigitalKeyLocation"" FROM muser U LEFT JOIN mmenu M ON U.defaultmenuid = M.menuid LEFT JOIN mimage I ON U.defaultimageid = I.imageid LEFT JOIN mmail MA ON U.mmailid = MA.mailid LEFT JOIN mpasswordpolicy PP ON U.passwordpolicyid = PP.passwordpolicyid LEFT JOIN mrole R ON U.roleid = R.roleid LEFT JOIN msecurityquestion SQ ON U.securityquestionid = SQ.securityquestionid WHERE U.userid = @userid AND U.tenantid = @clientid; "; public const string GET_USER_PG = @"SELECT a.userid AS UserId, a.usercode AS UserCode, a.username AS UserName, a.password AS UserPassword, a.lastsuccessloginon AS UserLastSuccessLoginOn, a.status AS CheckStatus, a.validfrom AS UserValidFrom, a.validto AS UserValidTo, a.roleid AS RoleId, a.workouid AS UserWorkOuId, d.organizationunitcode AS UserWorkOuCode, d.organizationunitname AS UserWorkOuName, a.workperiodid AS UserWorkPeriodId, a.workpartybranchid AS UserWorkPartyBranchId, c.partyid AS UserWorkPartyId, a.workstoreid AS UserWorkStoreId, a.workdate AS UserWorkDate, a.modeofoperation AS CheckModeofOperation, a.usercriteriaconfigid AS UserCriteriaConfigId, a.userdateformat AS UserDateFormat, a.usertimeformat AS UserTimeFormat, a.usercurrencyformat AS UserCurrencyFormat, a.userquantityformat AS UserQuantityFormat, a.delimiter AS UserDelimiter, a.userloginname AS UserLoginName, a.primarymail AS UserPrimaryMail, a.noofsessions AS CheckUserNoOfSessions, a.loginonworkdaysonly AS CheckUserLoginOnWorkDaysOnly, a.loginfromtime AS CheckUserLoginFromTime, a.logintotime AS CheckUserLoginToTime, a.loginmachine AS UserLoginMachine, a.loginip AS UserLoginIp, a.workfinancebookid AS WorkFinanceBookId, b.timezoneid AS TimeZoneId, b.utcoffset AS TimeZone, b.displayname AS TimeZoneDisplayName, COALESCE(vv.counteroperationid, -1) AS CounterOperationId, COALESCE(e.isencryptionrequired, 0) AS IsEncryptionRequired, dblevelsetting.selectlistoperationtype AS SelectlistOperationType, dblevelsetting.expirytime AS ExpiryTime, dblevelsetting.gracetime AS GraceTime, dblevelsetting.isipbasedcheckingrequired AS IsIpBasedCheckingRequired, dblevelsetting.attachmentoption AS TempAttachmentOption, a.mfausersetting AS CheckUserMFAUserSetting, a.mfauseraccountid AS UserMFAUserAccountId, a.mfausersecretkey AS UserMFAUserSecretKey, a.mfaqrcode AS UserMFAQRCode, a.showqrcode AS CheckUserShowQRCode, a.passwordchangedon AS UserPasswordChangedOn, a.ispartner AS CheckIsPartner, CASE WHEN COALESCE(emp.num,0)=0 THEN 1 ELSE 0 END AS CheckIsEmployee, a.usertype AS UserType FROM muser a -- EMPLOYEE CHECK (LATERAL) LEFT JOIN LATERAL ( SELECT COUNT(*) AS num FROM memployee WHERE employeeid = a.userid ) emp ON TRUE -- COUNTER OPERATION LEFT JOIN ( SELECT counteroperationid, ouid, periodid, counterstatus, cashierid FROM tcounteroperation ) vv ON vv.ouid = a.workouid AND vv.periodid = a.workperiodid AND vv.counterstatus = 0 AND vv.cashierid = a.userid -- DEVELOPER ENCRYPTION SETTING LEFT JOIN ( SELECT isencryptionrequired FROM mdeveloper WHERE developerid = @developerid ) e ON TRUE -- TIMEZONE INNER JOIN mtimezone b ON a.timezoneid = b.timezoneid -- PARTY BRANCH INNER JOIN mpartybranch c ON a.workpartybranchid = c.partybranchid -- ORGANIZATION UNIT INNER JOIN morganizationunit d ON a.workouid = d.ouid -- DB LEVEL SETTING INNER JOIN mdblevelsetting dblevelsetting ON a.tenantid = dblevelsetting.tenantid WHERE a.usercode = @usercode AND a.tenantid = @clientid; "; public const string GET_USER_ORACLE = @";"; public const string GET_USER_MYSQL = @";"; public const string GET_USER_SETTING_DETAIL = @" SELECT U.USERID AS UserId, U.USERCODE AS UserCode, U.WORKOUID AS OuId, OU.ORGANIZATIONUNITCODE AS OuCode, OU.ORGANIZATIONUNITNAME AS OuName, OU.OULOGO1 AS OuLogo1, OU.OULOGO2 AS OuLogo2, U.WORKPERIODID AS PeriodId, P.PERIODCODE AS PeriodCode, P.PERIODNAME AS PeriodName, OU.PERIODGROUP AS PeriodGroup, U.WORKPARTYBRANCHID AS PartyBranchId, PB.PARTYBRANCHCODE AS PartyBranchCode, PB.PARTYBRANCHNAME AS PartyBranchName, U.WORKSTOREID AS StoreId, S.STORECODE AS StoreCode, S.STORENAME AS StoreName, U.WORKREGIONID AS RegionId, R.REGIONCODE AS RegionCode, R.REGIONNAME AS RegionName, U.WORKDATE AS WorkDate, U.USERDATEFORMAT AS DateFormat, U.USERCURRENCYFORMAT AS CurrencyFormat, U.USERTIMEFORMAT AS TimeFormat, U.USERQUANTITYFORMAT AS QuantityFormat, U.DEFAULTMENUID AS DefaultMenuId, M.MENUCODE AS DefaultMenuCode, M.MENUNAME AS DefaultMenuName, U.WORKFINANCEBOOKID AS WorkFinanceBookId, FB.FINANCEBOOKNAME AS WorkFinanceBookName, U.DEFAULTDEVICEID AS DeviceId, D.DEVICENAME AS DeviceName, U.TIMEZONEID AS TimeZoneId, TZ.DISPLAYNAME AS TimeZoneName, PB.PARTYID AS PartyId, PT.PARTYNAME AS PartyName, U.CITYID AS CityId, CT.CITYNAME AS CityName, U.ZIPID AS ZipId, ZP.ZIPNAME AS ZipName, U.ROUTEID AS RouteId, RT.ROUTENAME AS RouteName, U.ACTIVITYTYPEID AS ActivityTypeId, ATY.ACTIVITYTYPENAME AS ActivityTypeName, U.ACTIVITYID AS ActivityId, AC.ACTIVITYNAME AS ActivityName FROM MUSER U LEFT JOIN MORGANIZATIONUNIT OU ON U.WORKOUID = OU.OUID LEFT JOIN MPERIOD P ON U.WORKPERIODID = P.PERIODID LEFT JOIN MPARTYBRANCH PB ON U.WORKPARTYBRANCHID = PB.PARTYBRANCHID LEFT JOIN MSTORE S ON U.WORKSTOREID = S.STOREID LEFT JOIN MREGION R ON U.WORKREGIONID = R.REGIONID LEFT JOIN MMENU M ON U.DEFAULTMENUID = M.MENUID LEFT JOIN MFINANCEBOOK FB ON U.WORKFINANCEBOOKID = FB.FINANCEBOOKID LEFT JOIN MDEVICE D ON U.DEFAULTDEVICEID = D.DEVICEID LEFT JOIN MTIMEZONE TZ ON U.TIMEZONEID = TZ.TIMEZONEID LEFT JOIN MPARTY PT ON PB.PARTYID = PT.PARTYID LEFT JOIN MCITY CT ON U.CITYID = CT.CITYID LEFT JOIN MZIP ZP ON U.ZIPID = ZP.ZIPID LEFT JOIN MROUTE RT ON U.ROUTEID = RT.ROUTEID LEFT JOIN MACTIVITYTYPE ATY ON U.ACTIVITYTYPEID = ATY.ACTIVITYTYPEID LEFT JOIN MACTIVITY AC ON U.ACTIVITYID = AC.ACTIVITYID WHERE U.USERID = @userid;"; public const string GET_USER_SETTING_DETAIL_PG = @" SELECT u.userid AS ""UserId"", u.usercode AS ""UserCode"", u.workouid AS ""OuId"", ou.organizationunitcode AS ""OuCode"", ou.organizationunitname AS ""OuName"", ou.oulogo1 AS ""OuLogo1"", ou.oulogo2 AS ""OuLogo2"", u.workperiodid AS ""PeriodId"", p.periodcode AS ""PeriodCode"", p.periodname AS ""PeriodName"", ou.periodgroup AS ""PeriodGroup"", u.workpartybranchid AS ""PartyBranchId"", pb.partybranchcode AS ""PartyBranchCode"", pb.partybranchname AS ""PartyBranchName"", u.workstoreid AS ""StoreId"", s.storecode AS ""StoreCode"", s.storename AS ""StoreName"", u.workregionid AS ""RegionId"", r.regioncode AS ""RegionCode"", r.regionname AS ""RegionName"", u.workdate AS ""WorkDate"", u.userdateformat AS ""DateFormat"", u.usercurrencyformat AS ""CurrencyFormat"", u.usertimeformat AS ""TimeFormat"", u.userquantityformat AS ""QuantityFormat"", u.defaultmenuid AS ""DefaultMenuId"", m.menucode AS ""DefaultMenuCode"", m.menuname AS ""DefaultMenuName"", u.workfinancebookid AS ""WorkFinanceBookId"", fb.financebookname AS ""WorkFinanceBookName"", u.defaultdeviceid AS ""DeviceId"", d.devicename AS ""DeviceName"", u.timezoneid AS ""TimeZoneId"", tz.displayname AS ""TimeZoneName"", pb.partyid AS ""PartyId"", pt.partyname AS ""PartyName"", u.cityid AS ""CityId"", ct.cityname AS ""CityName"", u.zipid AS ""ZipId"", zp.zipname AS ""ZipName"", u.routeid AS ""RouteId"", rt.routename AS ""RouteName"", u.activitytypeid AS ""ActivityTypeId"", aty.activitytypename AS ""ActivityTypeName"", u.activityid AS ""ActivityId"", ac.activityname AS ""ActivityName"" FROM muser u LEFT JOIN morganizationunit ou ON u.workouid = ou.ouid LEFT JOIN mperiod p ON u.workperiodid = p.periodid LEFT JOIN mpartybranch pb ON u.workpartybranchid = pb.partybranchid LEFT JOIN mstore s ON u.workstoreid = s.storeid LEFT JOIN mregion r ON u.workregionid = r.regionid LEFT JOIN mmenu m ON u.defaultmenuid = m.menuid LEFT JOIN mfinancebook fb ON u.workfinancebookid = fb.financebookid LEFT JOIN mdevice d ON u.defaultdeviceid = d.deviceid LEFT JOIN mtimezone tz ON u.timezoneid = tz.timezoneid LEFT JOIN mparty pt ON pb.partyid = pt.partyid LEFT JOIN mcity ct ON u.cityid = ct.cityid LEFT JOIN mzip zp ON u.zipid = zp.zipid LEFT JOIN mroute rt ON u.routeid = rt.routeid LEFT JOIN mactivitytype aty ON u.activitytypeid = aty.activitytypeid LEFT JOIN mactivity ac ON u.activityid = ac.activityid WHERE u.userid = @userid;"; public const string GET_PRIMARYMOBILE_NUMBER_ALREADY_PRESENT = "Select COUNT(USERID) from MUSER where PRIMARYMOBILE=':primarymobile' and userid<>:userid"; public const string GET_USER_MFA_AVAILABLE = "select MFAQRCode as UserMFAQRCode from MUSER where USERID=:userid"; public const string GET_EMPLOYEE_ID_BASED_ON_USER_CODE = "select EMPLOYEEID from MEMPLOYEE where EMPLOYEECODE=':employeecode'"; public const string SAVE_USER = @"INSERT INTO MUSER ( USERID, USERCODE, USERNAME, USERLOGINNAME, PASSWORD, DOB, USERTYPE, NATURE, SECURITYLEVEL, SECURITYQUESTIONID, SECURITYANSWER, REMARKS, ISEMPLOYEE, ISPARTNER, PARTNERID, PARTNERUSERID, VALIDFROM, VALIDTO, ROLEID, PRIMARYMAIL, PRIMARYMOBILE, PRIMARYPHONE, PASSWORDPOLICYID, WORKOUID, WORKPERIODID, WORKPARTYBRANCHID, WORKSTOREID, WORKDATE, LASTSUCCESSLOGINON, LASTFAILUREATTEMPTON, PREVIOUSPASSWORD1, PREVIOUSPASSWORD2, PREVIOUSPASSWORD3, PASSWORDCHANGEDON, PREVIOUSLOGIN, WORKREGIONID, MODEOFOPERATION, USERCRITERIACONFIGID, SORTORDER, STATUS, VERSION, SOURCETYPE, TENANTID, DEFAULTIMAGEID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, DEFAULTCMSID, USERDATEFORMAT, USERCURRENCYFORMAT, USERTIMEFORMAT, USERQUANTITYFORMAT, THEMEOPTION, NOOFSESSIONS, LOGINONWORKDAYSONLY, LOGINFROMTIME, LOGINTOTIME, LOGINMACHINE, LOGINIP, DIGITALSIGNEDFILE, DIGITALFILEPASSWORD, DEFAULTMENUID, WORKFINANCEBOOKID, TIMEZONEID, DEFAULTDEVICEID, DELIMITER, ISOUTOFOFFICE, OUTOFOFFICEFROMDATE, OUTOFOFFICETODATE, OUTOFOFFICEREMARKS, ESIGNATURECMSID, MMAILID, PUNCHTYPE, MFAUSERSETTING, MFAUSERACCOUNTID, MFAUSERSECRETKEY, MFAQRCODE, SHOWQRCODE, NUMBEROFFAILUREATTEMPTS, THUMBNAIL, THEMEMODE, LANGUAGEID, CRITERIARUNTYPE, DIGITALKEYLOCATION ) VALUES ( @UserId, @UserCode, @UserName, @UserLoginName, @UserPassword, @UserDOB, @UserType, @UserNature, @UserSecurityLevel, @SecurityQuestionId, @UserSecurityAnswer, @UserRemarks, @UserIsEmployee, @UserIsPartner, @UserPartnerId, @PartnerUserId, @UserValidFrom, @UserValidTo, @RoleId, @UserPrimaryMail, @UserPrimaryMobile, @UserPrimaryPhone, @PasswordPolicyId, @UserWorkOuId, @UserWorkPeriodId, @UserWorkPartyBranchId, @UserWorkStoreId, @UserWorkDate, @UserLastSuccessLoginOn, @UserLastFailureAttemptOn, @UserPreviousPassword1, @UserPreviousPassword2, @UserPreviousPassword3, @UserPasswordChangedOn, @UserPreviousLogin, @UserWorkRegionId, @UserModeofOperation, @UserCriteriaConfigId, @UserSortOrder, @UserStatus, @UserVersion, @UserSourceType, @TenantId, @ImageId, @UserCreatedById, @UserCreatedOn, @UserModifiedById, @UserModifiedOn, @UserDefaultCMSId, @UserDateFormat, @UserCurrencyFormat, @UserTimeFormat, @UserQuantityFormat, @UserThemeOption, @UserNoOfSessions, @UserLoginonWorkDaysOnly, @UserLoginFromTime, @UserLoginToTime, @UserLoginMachine, @UserLoginIp, @UserDigitalSignedFile, @UserDigitalFilePassword, @DefaultMenuId, @WorkFinanceBookId, @TimeZoneId, @DefaultDeviceId, @UserDelimiter, @UserIsOutOfOffice, @UserOutOfOfficeFromDate, @UserOutOfOfficeToDate, @UserOutOfOfficeRemarks, @UserESignaturecmsId, @MMailId, @UserPunchType, @UserMFAUserSetting, @UserMFAUserAccountId, @UserMFAUserSecretKey, @UserMFAQRCode, @UserShowQRCode, @UserNumberOfFailureAttempts, @UserThumbNail, @UserThemeMode, @UserLanguageId, @UserCriteriaRunType, @UserDigitalKeyLocation ); "; public const string SAVE_USER_PG = @"INSERT INTO muser ( userid, usercode, username, userloginname, password, dob, usertype, nature, securitylevel, securityquestionid, securityanswer, remarks, isemployee, ispartner, partnerid, partneruserid, validfrom, validto, roleid, primarymail, primarymobile, primaryphone, passwordpolicyid, workouid, workperiodid, workpartybranchid, workstoreid, workdate, lastsuccessloginon, lastfailureattempton, previouspassword1, previouspassword2, previouspassword3, passwordchangedon, previouslogin, workregionid, modeofoperation, usercriteriaconfigid, sortorder, status, version, sourcetype, tenantid, defaultimageid, createdbyid, createdon, modifiedbyid, modifiedon, defaultcmsid, userdateformat, usercurrencyformat, usertimeformat, userquantityformat, themeoption, noofsessions, loginonworkdaysonly, loginfromtime, logintotime, loginmachine, loginip, digitalsignedfile, digitalfilepassword, defaultmenuid, workfinancebookid, timezoneid, defaultdeviceid, delimiter, isoutofoffice, outofofficefromdate, outofofficetodate, outofofficeremarks, esignaturecmsid, mmailid, punchtype, mfausersetting, mfauseraccountid, mfausersecretkey, mfaqrcode, showqrcode, numberoffailureattempts, thumbnail, thememode, languageid, criteriaruntype, digitalkeylocation ) VALUES ( @UserId, @UserCode, @UserName, @UserLoginName, @UserPassword, @UserDOB, @UserType, @UserNature, @UserSecurityLevel, @SecurityQuestionId, @UserSecurityAnswer, @UserRemarks, @UserIsEmployee, @UserIsPartner, @UserPartnerId, @PartnerUserId, @UserValidFrom, @UserValidTo, @RoleId, @UserPrimaryMail, @UserPrimaryMobile, @UserPrimaryPhone, @PasswordPolicyId, @UserWorkOuId, @UserWorkPeriodId, @UserWorkPartyBranchId, @UserWorkStoreId, @UserWorkDate, @UserLastSuccessLoginOn, @UserLastFailureAttemptOn, @UserPreviousPassword1, @UserPreviousPassword2, @UserPreviousPassword3, @UserPasswordChangedOn, @UserPreviousLogin, @UserWorkRegionId, @UserModeofOperation, @UserCriteriaConfigId, @UserSortOrder, @UserStatus, @UserVersion, @UserSourceType, @TenantId, @ImageId, @UserCreatedById, @UserCreatedOn, @UserModifiedById, @UserModifiedOn, @UserDefaultCMSId, @UserDateFormat, @UserCurrencyFormat, @UserTimeFormat, @UserQuantityFormat, @UserThemeOption, @UserNoOfSessions, @UserLoginonWorkDaysOnly, @UserLoginFromTime, @UserLoginToTime, @UserLoginMachine, @UserLoginIp, @UserDigitalSignedFile, @UserDigitalFilePassword, @DefaultMenuId, @WorkFinanceBookId, @TimeZoneId, @DefaultDeviceId, @UserDelimiter, @UserIsOutOfOffice, @UserOutOfOfficeFromDate, @UserOutOfOfficeToDate, @UserOutOfOfficeRemarks, @UserESignaturecmsId, @MMailId, @UserPunchType, @UserMFAUserSetting, @UserMFAUserAccountId, @UserMFAUserSecretKey, @UserMFAQRCode, @UserShowQRCode, @UserNumberOfFailureAttempts, @UserThumbNail, @UserThemeMode, @UserLanguageId, @UserCriteriaRunType, @UserDigitalKeyLocation ); "; public const string SAVE_USER_ORACLE = @";"; public const string SAVE_USER_MYSQL = @";"; public const string UPDATE_USER = @"UPDATE MUSER SET USERCODE = @UserCode, USERNAME = @UserName, USERLOGINNAME = @UserLoginName, PASSWORD = ISNULL(@UserPassword, PASSWORD), DOB = @UserDOB, USERTYPE = @UserType, NATURE = @UserNature, SECURITYLEVEL = @UserSecurityLevel, SECURITYQUESTIONID = @SecurityQuestionId, SECURITYANSWER = @UserSecurityAnswer, REMARKS = @UserRemarks, ISEMPLOYEE = @UserIsEmployee, ISPARTNER = @UserIsPartner, PARTNERID = @UserPartnerId, PARTNERUSERID = @PartnerUserId, VALIDFROM = @UserValidFrom, VALIDTO = @UserValidTo, ROLEID = @RoleId, PRIMARYMAIL = @UserPrimaryMail, PRIMARYMOBILE = @UserPrimaryMobile, PRIMARYPHONE = @UserPrimaryPhone, PASSWORDPOLICYID = @PasswordPolicyId, WORKOUID = @UserWorkOuId, WORKPERIODID = @UserWorkPeriodId, WORKPARTYBRANCHID = @UserWorkPartyBranchId, WORKSTOREID = @UserWorkStoreId, WORKDATE = @UserWorkDate, LASTSUCCESSLOGINON = @UserLastSuccessLoginOn, LASTFAILUREATTEMPTON = @UserLastFailureAttemptOn, PREVIOUSPASSWORD1 = @UserPreviousPassword1, PREVIOUSPASSWORD2 = @UserPreviousPassword2, PREVIOUSPASSWORD3 = @UserPreviousPassword3, PASSWORDCHANGEDON = @UserPasswordChangedOn, PREVIOUSLOGIN = @UserPreviousLogin, WORKREGIONID = @UserWorkRegionId, MODEOFOPERATION = @UserModeofOperation, USERCRITERIACONFIGID = @UserCriteriaConfigId, SORTORDER = @UserSortOrder, STATUS = @UserStatus, VERSION = @UserVersion, SOURCETYPE = @UserSourceType, DEFAULTIMAGEID = @ImageId, MODIFIEDBYID = @UserModifiedById, MODIFIEDON = @UserModifiedOn, DEFAULTCMSID = @UserDefaultCMSId, USERDATEFORMAT = @UserDateFormat, USERCURRENCYFORMAT = @UserCurrencyFormat, USERTIMEFORMAT = @UserTimeFormat, USERQUANTITYFORMAT = @UserQuantityFormat, THEMEOPTION = @UserThemeOption, NOOFSESSIONS = @UserNoOfSessions, LOGINONWORKDAYSONLY = @UserLoginonWorkDaysOnly, LOGINFROMTIME = @UserLoginFromTime, LOGINTOTIME = @UserLoginToTime, LOGINMACHINE = @UserLoginMachine, LOGINIP = @UserLoginIp, DIGITALSIGNEDFILE = @UserDigitalSignedFile, DIGITALFILEPASSWORD = @UserDigitalFilePassword, DEFAULTMENUID = @DefaultMenuId, WORKFINANCEBOOKID = @WorkFinanceBookId, TIMEZONEID = @TimeZoneId, DEFAULTDEVICEID = @DefaultDeviceId, DELIMITER = @UserDelimiter, ISOUTOFOFFICE = @UserIsOutOfOffice, OUTOFOFFICEFROMDATE = @UserOutOfOfficeFromDate, OUTOFOFFICETODATE = @UserOutOfOfficeToDate, OUTOFOFFICEREMARKS = @UserOutOfOfficeRemarks, ESIGNATURECMSID = @UserESignaturecmsId, MMAILID = @MMailId, PUNCHTYPE = @UserPunchType, MFAUSERSETTING = @UserMFAUserSetting, MFAUSERACCOUNTID = @UserMFAUserAccountId, MFAUSERSECRETKEY = @UserMFAUserSecretKey, MFAQRCODE = @UserMFAQRCode, SHOWQRCODE = @UserShowQRCode, NUMBEROFFAILUREATTEMPTS = @UserNumberOfFailureAttempts, THUMBNAIL = @UserThumbNail, THEMEMODE = @UserThemeMode, LANGUAGEID = ISNULL(@UserLanguageId, LANGUAGEID), CRITERIARUNTYPE = @UserCriteriaRunType, DIGITALKEYLOCATION = @UserDigitalKeyLocation WHERE USERID = @UserId; "; public const string UPDATE_USER_PG = @"UPDATE muser SET usercode = @UserCode, username = @UserName, userloginname = @UserLoginName, password = @UserPassword, dob = @UserDOB, usertype = @UserType, nature = @UserNature, securitylevel = @UserSecurityLevel, securityquestionid = @SecurityQuestionId, securityanswer = @UserSecurityAnswer, remarks = @UserRemarks, isemployee = @UserIsEmployee, ispartner = @UserIsPartner, partnerid = @UserPartnerId, partneruserid = @PartnerUserId, validfrom = @UserValidFrom, validto = @UserValidTo, roleid = @RoleId, primarymail = @UserPrimaryMail, primarymobile = @UserPrimaryMobile, primaryphone = @UserPrimaryPhone, passwordpolicyid = @PasswordPolicyId, workouid = @UserWorkOuId, workperiodid = @UserWorkPeriodId, workpartybranchid = @UserWorkPartyBranchId, workstoreid = @UserWorkStoreId, workdate = @UserWorkDate, lastsuccessloginon = @UserLastSuccessLoginOn, lastfailureattempton = @UserLastFailureAttemptOn, previouspassword1 = @UserPreviousPassword1, previouspassword2 = @UserPreviousPassword2, previouspassword3 = @UserPreviousPassword3, passwordchangedon = @UserPasswordChangedOn, previouslogin = @UserPreviousLogin, workregionid = @UserWorkRegionId, modeofoperation = @UserModeofOperation, usercriteriaconfigid = @UserCriteriaConfigId, sortorder = @UserSortOrder, status = @UserStatus, version = @UserVersion, sourcetype = @UserSourceType, defaultimageid = @ImageId, modifiedbyid = @UserModifiedById, modifiedon = @UserModifiedOn, defaultcmsid = @UserDefaultCMSId, userdateformat = @UserDateFormat, usercurrencyformat = @UserCurrencyFormat, usertimeformat = @UserTimeFormat, userquantityformat = @UserQuantityFormat, themeoption = @UserThemeOption, noofsessions = @UserNoOfSessions, loginonworkdaysonly = @UserLoginonWorkDaysOnly, loginfromtime = @UserLoginFromTime, logintotime = @UserLoginToTime, loginmachine = @UserLoginMachine, loginip = @UserLoginIp, digitalsignedfile = @UserDigitalSignedFile, digitalfilepassword = @UserDigitalFilePassword, defaultmenuid = @DefaultMenuId, workfinancebookid = @WorkFinanceBookId, timezoneid = @TimeZoneId, defaultdeviceid = @DefaultDeviceId, delimiter = @UserDelimiter, isoutofoffice = @UserIsOutOfOffice, outofofficefromdate = @UserOutOfOfficeFromDate, outofofficetodate = @UserOutOfOfficeToDate, outofofficeremarks = @UserOutOfOfficeRemarks, esignaturecmsid = @UserESignaturecmsId, mmailid = @MMailId, punchtype = @UserPunchType, mfausersetting = @UserMFAUserSetting, mfauseraccountid = @UserMFAUserAccountId, mfausersecretkey = @UserMFAUserSecretKey, mfaqrcode = @UserMFAQRCode, showqrcode = @UserShowQRCode, numberoffailureattempts = @UserNumberOfFailureAttempts, thumbnail = @UserThumbNail, thememode = @UserThemeMode, languageid = @UserLanguageId, criteriaruntype = @UserCriteriaRunType, digitalkeylocation = @UserDigitalKeyLocation WHERE userid = @UserId; "; public const string UPDATE_USER_ORACLE = @";"; public const string UPDATE_USER_MYSQL = @";"; public const string DELETE_USER = @"DELETE FROM MUSER WHERE USERID = @userid; "; public const string DELETE_USER_PG = @"DELETE FROM muser WHERE userid = @userid; "; public const string DELETE_USER_ORACLE = @";"; public const string DELETE_USER_MYSQL = @";"; /// /// GB4 parity for LogOutUser's user-existence check (AuthenticationBLL.LogOutUser /// looked the user up by Code + tenant and threw if zero or more-than-one match). /// public const string CHECK_USER_EXISTS_BY_CODE = "SELECT COUNT(*) FROM MUSER WHERE USERCODE = @code AND TENANTID = @clientid"; public const string CHECK_ADMIN_RIGHTS = "SELECT COUNT(*) " + "FROM MROLEVSMENU rvm " + "JOIN MUSER u ON rvm.ROLEID = u.ROLEID " + "WHERE rvm.MENUID = -1499999144 " + "AND u.USERCODE = @code " + "AND SUBSTRING(rvm.ALLOW, 2, 1) = '0' " + "AND u.TENANTID = @clientid " + "GROUP BY u.USERCODE"; public const string GET_SELECTLIST_USER= @"SELECT USERID AS Id, USERCODE AS Code, USERNAME AS Name, THUMBNAIL AS ThumbNail FROM MUSER;"; public const string UPDATE_USER_SETTINGS = @"UPDATE MUSER SET WORKOUID = @OuId, WORKPERIODID = @PeriodId, WORKPARTYBRANCHID = @PartyBranchId, WORKSTOREID = @StoreId, WORKDATE = @WorkDate, WORKREGIONID = @RegionId, USERDATEFORMAT = @UserDateFormat, USERCURRENCYFORMAT = @UserCurrencyFormat, USERTIMEFORMAT = @UserTimeFormat, USERQUANTITYFORMAT = @UserQuantityFormat, DEFAULTMENUID = @DefaultMenuId, WORKFINANCEBOOKID = @WorkFinanceBookId, TIMEZONEID = @TimeZoneId, DEFAULTDEVICEID = @DefaultDeviceId, CITYID = @CityId, ZIPID = @ZipId, ROUTEID = @RouteId, ACTIVITYTYPEID = @ActivityTypeId, ACTIVITYID = @ActivityId, CRITERIARUNTYPE = @CriteriaRunType WHERE USERID = @UserId;"; public const string UPDATE_USER_SETTINGS_PG = @"UPDATE muser SET workouid = @OuId, workperiodid = @PeriodId, workpartybranchid = @PartyBranchId, workstoreid = @StoreId, workdate = @WorkDate, workregionid = @RegionId, userdateformat = @UserDateFormat, usercurrencyformat = @UserCurrencyFormat, usertimeformat = @UserTimeFormat, userquantityformat = @UserQuantityFormat, defaultmenuid = @DefaultMenuId, workfinancebookid = @WorkFinanceBookId, timezoneid = @TimeZoneId, defaultdeviceid = @DefaultDeviceId, cityid = @CityId, zipid = @ZipId, routeid = @RouteId, activitytypeid = @ActivityTypeId, activityid = @ActivityId, criteriaruntype = @CriteriaRunType WHERE userid = @UserId;"; public const string UPDATE_USER_THEME = @"UPDATE MUSER SET THEMEOPTION = @ThemeOption WHERE USERID = @UserId;"; public const string UPDATE_USER_THEME_PG = @"UPDATE muser SET themeoption = @ThemeOption WHERE userid = @UserId;"; public const string UPDATE_USER_THEME_AND_MODE = @"UPDATE MUSER SET THEMEOPTION = @ThemeOption, THEMEMODE = @ThemeMode WHERE USERID = @UserId;"; public const string UPDATE_USER_THEME_AND_MODE_PG = @"UPDATE muser SET themeoption = @ThemeOption, thememode = @ThemeMode WHERE userid = @UserId;"; } }