using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace FrameworkDAL.Query.UserLogin { public static class UserLoginQB { public const string GET_USERLOGINID_SQL = @"SELECT u.USERID AS UserId, u.USERCODE AS UserCode, u.USERNAME AS UserFullName, u.USERLOGINNAME AS UserLoginName, u.PREVIOUSLOGIN AS LastSuccessLoginOn, u.LASTFAILUREATTEMPTON AS LastFailureAttemptOn, u.WORKSTOREID AS StoreId, u.THUMBNAIL AS UserThumbNail, s.STORENAME AS StoreName, 0 AS NoofNewTasks, 0 AS NoofNewInbox, u.WORKOUID AS OUID, ou.ORGANIZATIONUNITNAME AS OUName, ou.OULOGO1 AS Logo1, ou.OULOGO2 AS Logo2, '' AS OULogoURI, pb.PARTYBRANCHID AS PartyBranchId, pb.PARTYBRANCHNAME AS PartyBranchName, '' AS PartyBranchCityName, '' AS WorkRegion, '' AS WrokGeoCode, REPLACE(img.IMAGELINKURL, '{BaseURI}', -1) AS ImageURL, p.PERIODID AS WorkPeriodId, p.FROMDATE AS WorkPeriodFromDate, p.TODATE AS WorkPeriodToDate, ISNULL(pp.PARTNERPRODUCTID, 0) AS PartnerProductId, COALESCE(ppbr.APPNAME, pbr.APPNAME, 'GoodBooks') AS AppName, COALESCE(ppbr.PRIMARYCOLOR, pbr.PRIMARYCOLOR, '#003399') AS PrimaryColor, COALESCE(ppbr.SECONDARYCOLOR, pbr.SECONDARYCOLOR, '#0055CC') AS SecondaryColor, COALESCE(ppbr.ACCENTCOLOR, pbr.ACCENTCOLOR, '#FF6600') AS AccentColor, COALESCE(ppbr.FONTFAMILY, pbr.FONTFAMILY, 'Inter') AS FontFamily FROM MUSER u INNER JOIN MPARTYBRANCH pb ON u.WORKPARTYBRANCHID = pb.PARTYBRANCHID INNER JOIN MORGANIZATIONUNIT ou ON u.WORKOUID = ou.OUID INNER JOIN MIMAGE img ON u.DEFAULTIMAGEID = img.IMAGEID INNER JOIN MPERIOD p ON u.WORKPERIODID = p.PERIODID INNER JOIN MSTORE s ON u.WORKSTOREID = s.STOREID LEFT JOIN TPARTNERPRODUCT pp ON pp.PARTNERPRODUCTID = @partnerproductid LEFT JOIN TPARTNER prt ON prt.PARTNERID = pp.PARTNERID LEFT JOIN TPARTNERBRAND pbr ON pbr.PARTNERID = prt.PARTNERID LEFT JOIN TPARTNERPRODUCTBRAND ppbr ON ppbr.PARTNERPRODUCTID = pp.PARTNERPRODUCTID WHERE u.USERID = @userid AND u.TENANTID = @clientid; "; public const string GET_USERLOGINCODE_SQL = @"SELECT u.USERID AS UserId, u.USERCODE AS UserCode, u.USERNAME AS UserFullName, u.USERLOGINNAME AS UserLoginName, u.PREVIOUSLOGIN AS LastSuccessLoginOn, u.LASTFAILUREATTEMPTON AS LastFailureAttemptOn, u.THUMBNAIL AS UserThumbNail, 0 AS NoofNewTasks, 0 AS NoofNewInbox, u.WORKOUID AS OUID, ou.ORGANIZATIONUNITNAME AS OUName, ou.OULOGO1 AS Logo1, ou.OULOGO2 AS Logo2, '' AS OULogoURI, pb.PARTYBRANCHID AS PartyBranchId, pb.PARTYBRANCHNAME AS PartyBranchName, '' AS PartyBranchCityName, '' AS WorkRegion, '' AS WrokGeoCode, '' AS ImageURL FROM MUSER u INNER JOIN MPARTYBRANCH pb ON u.WORKPARTYBRANCHID = pb.PARTYBRANCHID INNER JOIN MORGANIZATIONUNIT ou ON u.WORKOUID = ou.OUID WHERE u.USERCODE = @UserCode AND u.TENANTID = @clientid;"; public const string GET_USER_NO_OF_READ_PERIOD_SQL = @"SELECT A.NOOFREADPERIOD FROM MUSERACCESSRIGHTS A WHERE A.USERID = @UserId AND A.OUID = @OuId;"; public const string GET_ALL_PERIOD_SQL = @"SELECT FROMDATE AS PeriodFromDate FROM MPERIOD WHERE PERIODID <> -1 AND TENANTID = @clientid ORDER BY FROMDATE DESC;"; public const string GET_USERLOGINID_PG = @"SELECT u.userid AS ""UserId"", u.usercode AS ""UserCode"", u.username AS ""UserFullName"", u.userloginname AS ""UserLoginName"", u.previouslogin AS ""LastSuccessLoginOn"", u.lastfailureattempton AS ""LastFailureAttemptOn"", u.workstoreid AS ""StoreId"", u.thumbnail AS ""UserThumbNail"", s.storename AS ""StoreName"", 0 AS ""NoofNewTasks"", 0 AS ""NoofNewInbox"", u.workouid AS ""OUID"", ou.organizationunitname AS ""OUName"", ou.oulogo1 AS ""Logo1"", ou.oulogo2 AS ""Logo2"", '' AS ""OULogoURI"", pb.partybranchid AS ""PartyBranchId"", pb.partybranchname AS ""PartyBranchName"", '' AS ""PartyBranchCityName"", '' AS ""WorkRegion"", '' AS ""WrokGeoCode"", REPLACE(img.imagelinkurl, '{BaseURI}', :baseuri) AS ""ImageURL"", p.periodid AS ""WorkPeriodId"", p.fromdate AS ""WorkPeriodFromDate"", p.todate AS ""WorkPeriodToDate"", COALESCE(pp.partnerproductid, 0) AS ""PartnerProductId"", COALESCE(ppbr.appname, pbr.appname, 'GoodBooks') AS ""AppName"", COALESCE(ppbr.primarycolor, pbr.primarycolor, '#003399') AS ""PrimaryColor"", COALESCE(ppbr.secondarycolor, pbr.secondarycolor, '#0055CC') AS ""SecondaryColor"", COALESCE(ppbr.accentcolor, pbr.accentcolor, '#FF6600') AS ""AccentColor"", COALESCE(ppbr.fontfamily, pbr.fontfamily, 'Inter') AS ""FontFamily"" FROM muser u INNER JOIN mpartybranch pb ON u.workpartybranchid = pb.partybranchid INNER JOIN morganizationunit ou ON u.workouid = ou.ouid INNER JOIN mimage img ON u.defaultimageid = img.imageid INNER JOIN mperiod p ON u.workperiodid = p.periodid INNER JOIN mstore s ON u.workstoreid = s.storeid LEFT JOIN tpartnerproduct pp ON pp.partnerproductid = :partnerproductid LEFT JOIN tpartner prt ON prt.partnerid = pp.partnerid LEFT JOIN tpartnerbrand pbr ON pbr.partnerid = prt.partnerid LEFT JOIN tpartnerproductbrand ppbr ON ppbr.partnerproductid = pp.partnerproductid WHERE u.userid = :userid AND u.tenantid = :clientid;"; public const string GET_USERLOGINCODE_PG = @"SELECT u.userid AS ""UserId"", u.usercode AS ""UserCode"", u.username AS ""UserFullName"", u.userloginname AS ""UserLoginName"", u.previouslogin AS ""LastSuccessLoginOn"", u.lastfailureattempton AS ""LastFailureAttemptOn"", u.thumbnail AS ""UserThumbNail"", 0 AS ""NoofNewTasks"", 0 AS ""NoofNewInbox"", u.workouid AS ""OUID"", ou.organizationunitname AS ""OUName"", ou.oulogo1 AS ""Logo1"", ou.oulogo2 AS ""Logo2"", '' AS ""OULogoURI"", pb.partybranchid AS ""PartyBranchId"", pb.partybranchname AS ""PartyBranchName"", '' AS ""PartyBranchCityName"", '' AS ""WorkRegion"", '' AS ""WrokGeoCode"", '' AS ""ImageURL"" FROM muser u INNER JOIN mpartybranch pb ON u.workpartybranchid = pb.partybranchid INNER JOIN morganizationunit ou ON u.workouid = ou.ouid WHERE u.usercode = :UserCode AND u.tenantid = :clientid;"; public const string GET_USER_NO_OF_READ_PERIOD_PG = @"SELECT a.noofreadperiod FROM museraccessrights a WHERE a.userid = :userid AND a.ouid = :ouid;"; public const string GET_ALL_PERIOD_PG = @"SELECT FROMDATE AS ""PeriodFromDate"" FROM MPERIOD WHERE PERIODID <> -1 AND tenantid = :clientid ORDER BY FROMDATE DESC;"; // Hop 1 for partner/white-label branding — MCLIENTDETAILS/MSERVERCONFIG live only // in the GB5System database (see PartnerBrandDAL.GetResolvedBrandForClient / // DBLevelSettingDAL.GetDBLevelSetting for the same pattern) and must never be // joined into a tenant-DB query. Resolve PartnerProductId here first, then pass // it into GET_USERLOGINID_SQL/_PG as @partnerproductid. // // MSERVERCONFIG.PARTNERPRODUCTID (keyed by the CONNECTIONNAME the user logged in // through) wins when set — this is what lets one physical tenant DB present // different partner labels depending on which connection/config was used to reach // it. Falls back to MCLIENTDETAILS.PARTNERPRODUCTID (keyed by ClientId) otherwise, // which is today's behavior for every tenant that hasn't set a per-connection // override. Both columns use -1 as "not set". // MCLIENTDETAILS.PARTNERPRODUCTID is the single source of truth for this // assignment — there is no separate override column on MSERVERCONFIG (an earlier // version of this query assumed one; it does not exist on every server's live // schema and has been removed here — same fix already applied to Partner's // PartnerBrandQB.GET_ROUTING_BY_CONNECTION_NAME). ISNULL guards against a NULL // PartnerProductId (client not yet assigned a partner) so this never throws. public const string GET_PARTNERPRODUCTID_BY_CLIENT_ID = @" SELECT DISTINCT ISNULL(CD.PARTNERPRODUCTID, -1) AS PartnerProductId FROM MCLIENTDETAILS CD WHERE CD.CLIENTID = @ClientId"; public const string GET_USERLOGINID_ORACLE = @""; public const string GET_USERLOGINID_MYSQL = @""; public const string GET_USERLOGINCODE_ORACLE = @""; public const string GET_USERLOGINCODE_MYSQL = @""; public const string GET_USER_NO_OF_READ_PERIOD_ORACLE = @""; public const string GET_USER_NO_OF_READ_PERIOD_MYSQL = @""; public const string GET_ALL_PERIOD_ORACLE = @""; public const string GET_ALL_PERIOD_MYSQL = @""; } }