using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace FrameworkDAL.Query.DBLevelSetting { public static class DBLevelSettingQB { // Singleton-row table — every existing read (GET_VERSION_NUMBER_FOR_DATABASE, // GET_DBLEVELSETTING) targets it with no WHERE clause, so this write follows the // same assumption. Runs against a specific tenant's own database via a connection // opened directly by VersionSyncBLL (SqlWorkbench ClientDatabaseId -> MSERVERCONFIG // -> raw connection string) — never through IQueryExecutor/LoginDTO, since the // target database is not the caller's own session database. // BUILDNUMBER must always be BUILDVERSION with dots stripped, converted to int // (CK_MDBLEVELSETTING_BUILDNUMBER enforces this on every UPDATE, not just at insert — // confirmed live on GB5DEMO, e.g. BUILDVERSION '4.1.6.48.1' <-> BUILDNUMBER 416481). // Deriving it here in the same statement guarantees the two columns can never drift, // rather than duplicating the same parsing logic in C#. public const string UPDATE_BUILD_VERSION = @"UPDATE MDBLevelSetting SET BUILDVERSION = @BuildVersion, BUILDNUMBER = CONVERT(INT, REPLACE(@BuildVersion, '.', ''))"; public const string GET_VERSION_NUMBER_FOR_DATABASE = @"Select top 1 BuildNumber as DBLevelSettingBuildNumber, BuildVersion as DBLevelSettingBuildVersion FROM MDBLevelSetting"; public const string GET_VERSION_DETAILS = @"Select '' versionnumber'' ,'' Servicebaseuri,'' febaseuri,'' essfebaseuri,'' message , '' isapplicable '' essuri FROM MDBLevelSetting "; public const string GET_VERSION_NUMBER_FOR_IMPSYS_DATABASE = @"Select top 1 BuildNumber as DBLevelSettingBuildNumber, BuildVersion as DBLevelSettingBuildVersion FROM USCIMPSYS..MDBLevelSetting "; // Replaces the old MATCHING/LOWER/HIGHER trio of =/ queries — comparing // BUILDVERSION/VERSIONNUMBER as plain NVARCHAR with sorts lexicographically, // which silently misorders as soon as any dotted segment reaches double digits // (e.g. "4.1.10" < "4.1.9" as text). Version comparison now happens in C# // (VersionBLL.CompareVersionStrings) by parsing and comparing each dot-separated // segment as an integer — this query just returns every candidate row (there can // legitimately be more than one MAppVersion row with ISAPPLICABLE=0 at once, e.g. // a dual-version rollout window) for the BLL to reason about. public const string GET_DBLEVEL_AND_APPLICABLE_APP_VERSIONS = @" select a.buildversion As DBVersionNumber , b.appversionid AS AppVersionId, b.versionnumber AS AppVersionNumber, b.ServiceBaseURL AS ServiceBaseUri, b.FEBaseURL AS FeBaseUri, b.ESSURL AS EssFEBaseUri, b.IsApplicable AS IsApplicable, b.SecondVersion AS SecondVersion, b.IsSecondApplicable AS IsSecondApplicable, b.SecondServiceBaseURL AS SecondServiceBaseURL, b.SECURITYENABLE AS SecurityEnable FROM MDBLevelSetting a CROSS JOIN MAppVersion b WHERE b.isapplicable = 0"; // ── GetDBLevelSetting ──────────────────────────────────────────────────── // Migrated from GB4: DBLevelSettingService/Get → GetYarnCalculationType BLL // Supports SQL Server (ISNULL) and PostgreSQL (converted via ConvertSqlToPostgres) // ───────────────────────────────────────────────────────────────────────── public const string GET_DBLEVELSETTING = @" SELECT DISTINCT D.YARNCALCULATIONTYPE AS DBLevelSettingYarnCalculationType, D.SMSSERVICETEMPLATE AS DBLevelSettingSMSServiceTemplate, D.REPORTPATH AS DBLevelSettingReportPath, D.LASTSYNCDATE AS DBLevelSettingLastSyncDate, D.WARPPROCESSID AS DBLevelSettingWarpProcessId, D.WEFTPROCESSID AS DBLevelSettingWeftProcessId, D.YARNCODEDEFINEID AS YarnCodeDefineId, D.FABRICCODEDEFINEID AS FabricCodeDefineId, D.PARTYCODEDEFINEID AS PartyCodeDefineId, D.LABELCODEDEFINEID AS LabelCodeDefineId, D.SCRIPTSUPTODATE AS DBLevelSettingScriptSyncUpToDate, D.SCRIPTSLNO AS DBLevelSettingScriptSlNo, D.SYNCSTATUS AS DBLevelSettingSyncStatus, D.BUILDNUMBER AS DBLevelSettingBuildNumber, D.BUILDVERSION AS DBLevelSettingBuildVersion, D.NEWBUILDNUMBER AS DBLevelSettingNewBuildNumber, D.NEWBUILDVERSION AS DBLevelSettingNewBuildVersion, D.ISBRAND AS DBLevelSettingIsBrand, D.ISCATEGORY AS DBLevelSettingIsCategory, D.ISSUBCATEGORY AS DBLevelSettingIsSubCategory, D.ISOU AS DBLevelSettingIsOU, D.ISCOUNTRY AS DBLevelSettingIsCountry, D.ISSTATE AS DBLevelSettingIsState, D.ISCITY AS DBLevelSettingIsCity, D.ISLOCATION AS DBLevelSettingIsLocation, D.ISPINCODE AS DBLevelSettingIsPinCode, D.ISPROBLEMCATEGORY AS DBLevelSettingIsProblemCategory, D.DELIVERYDOCUMENTWISE AS DBLevelSettingDeliveryDocumentWise, D.ISCODEEDIT AS DBLevelSettingIsCodeEdit, D.BARCODEISLOT AS DBLevelSettingBarCodeIsLot, D.BARCODEISSKU AS DBLevelSettingBarCodeIsSKU, D.BARCODEISPACK AS DBLevelSettingBarCodeIsPack, D.BARCODEISPACKNUMBER AS DBLevelSettingBarCodeIsPackNumber, D.MHRFORMULAID AS MHRFormulaId, ISNULL(MHR.FORMULACODE, '') AS MHRFormulaCode, ISNULL(MHR.FORMULANAME, '') AS MHRFormulaName, D.POHFORMULAID AS POHFormulaId, ISNULL(POH.FORMULACODE, '') AS POHFormulaCode, ISNULL(POH.FORMULANAME, '') AS POHFormulaName, D.AOHFORMULAID AS AOHFormulaId, ISNULL(AOH.FORMULACODE, '') AS AOHFormulaCode, ISNULL(AOH.FORMULANAME, '') AS AOHFormulaName, D.SOHFORMULAID AS SOHFormulaId, ISNULL(SOH.FORMULACODE, '') AS SOHFormulaCode, ISNULL(SOH.FORMULANAME, '') AS SOHFormulaName, D.OOHFORMULAID AS OOHFormulaId, ISNULL(OOH.FORMULACODE, '') AS OOHFormulaCode, ISNULL(OOH.FORMULANAME, '') AS OOHFormulaName, D.PRECOSTDWID AS PreCostDWId, D.ISPARTYNAMEWITHCODE AS DBLevelSettingIsPartyNameWithCode, D.SELECTLISTOPERATIONTYPE AS DBLevelSettingSelectlistoperationtype, D.ISCANCELDELETEONLYFORPENDING AS DBLevelSettingIsCancelDeleteOnlyForPending, D.ISCANCELAPPLICABLE AS DBLevelSettingIsCancelApplicable, ISNULL(YCD.CATEGORYNAME, '') AS YarnCodeDefineName, ISNULL(FCD.CATEGORYNAME, '') AS FabricCodeDefineName, ISNULL(PCD.CATEGORYNAME, '') AS PartyCodeDefineName, ISNULL(LCD.CATEGORYNAME, '') AS LabelCodeDefineName, D.ISVERSIONUPDATED AS DBLevelSettingIsVersionUpdated, D.ATTACHMENTOPTION AS DBLevelSettingAttachmentOption, ISNULL(D.AMAZONS3ACCESSKEY, '') AS DBLevelSettingAmazonS3AccessKey, ISNULL(D.AMAZONS3SECRETKEY, '') AS DBLevelSettingAmazonS3SecretKey, ISNULL(D.AMAZONS3BUCKETNAME, '') AS DBLevelSettingAmazonS3BucketName, ISNULL(D.AMAZONS3REGION, '') AS DBLevelSettingAmazonS3Region, D.ISIPBASEDCHECKINGREQUIRED AS DBLevelSettingIsIpBasedCheckingRequired, ISNULL(D.DEFAULTMOBILENUMBER,'') AS DBLevelSettingDefaultMobileNumber, D.PAIDDAYSFORMULAID AS PaidDaysFormulaId, ISNULL(PAY.FORMULACODE, '') AS PaidDaysFormulaCode, ISNULL(PAY.FORMULANAME, '') AS PaidDaysFormulaName, D.BILLDAYSFORMULAID AS BillDaysFormulaId, ISNULL(BILL.FORMULACODE, '') AS BillDaysFormulaCode, ISNULL(BILL.FORMULANAME, '') AS BillDaysFormulaName, D.OTDAYSFORMULAID AS OTDaysFormulaId, ISNULL(OT.FORMULACODE, '') AS OTDaysFormulaCode, ISNULL(OT.FORMULANAME, '') AS OTDaysFormulaName, D.DEFAULTCONTACTROLEID AS DefaultContactRoleId, ISNULL(R.ROLECODE, '') AS DefaultContactRoleCode, ISNULL(R.ROLENAME, '') AS DefaultContactRoleName, D.CONTACTTOUSER AS DBLevelSettingContactToUser, D.ISWORKTYPEVISIBLE AS DBLevelSettingIsWorkTypeVisible, D.INCLUDERESIGNEDEMPLOYEEINPAYPROCESS AS IncludeResignedEmployeeInPayprocess, D.GEOFENCING AS DBLevelSettingGeoFencing, D.ISNAMEEDIT AS DBLevelSettingIsNameEdit, D.BINPOSTINGTYPE AS DBLevelSettingBinPostingType, D.MAXIMUMALLOWEDDISTANCE AS DBLevelSettingMaximumAllowedDistance, D.EXPIRYTIME AS DBLevelSettingExpiryTime, D.GRACETIME AS DBLevelSettingGraceTime, D.ISMULTIPLEBOOKS AS DBLevelSettingIsMultipleBooks, D.D1LABEL AS D1Label, D.D2LABEL AS D2Label, D.D3LABEL AS D3Label, D.D4LABEL AS D4Label, D.D5LABEL AS D5Label, CAST(0 AS SMALLINT) AS DBLevelSettingWebUserLicenses, CAST('1900-01-01' AS DATETIME) AS DBLevelSettingHostingValidTill, CAST('1900-01-01' AS DATETIME) AS DBLevelSettingLicenceValidTill, CAST('1900-01-01' AS DATETIME) AS DBLevelSettingSupportValidTill FROM MDBLEVELSETTING D LEFT JOIN MFORMULA MHR ON D.MHRFORMULAID = MHR.FORMULAID LEFT JOIN MFORMULA POH ON D.POHFORMULAID = POH.FORMULAID LEFT JOIN MFORMULA AOH ON D.AOHFORMULAID = AOH.FORMULAID LEFT JOIN MFORMULA SOH ON D.SOHFORMULAID = SOH.FORMULAID LEFT JOIN MFORMULA OOH ON D.OOHFORMULAID = OOH.FORMULAID LEFT JOIN MCODEDEFINE YCD ON D.YARNCODEDEFINEID = YCD.CODEDEFINEID LEFT JOIN MCODEDEFINE FCD ON D.FABRICCODEDEFINEID = FCD.CODEDEFINEID LEFT JOIN MCODEDEFINE PCD ON D.PARTYCODEDEFINEID = PCD.CODEDEFINEID LEFT JOIN MCODEDEFINE LCD ON D.LABELCODEDEFINEID = LCD.CODEDEFINEID LEFT JOIN MFORMULA PAY ON D.PAIDDAYSFORMULAID = PAY.FORMULAID LEFT JOIN MFORMULA BILL ON D.BILLDAYSFORMULAID = BILL.FORMULAID LEFT JOIN MFORMULA OT ON D.OTDAYSFORMULAID = OT.FORMULAID LEFT JOIN MROLE R ON D.DEFAULTCONTACTROLEID = R.ROLEID;"; // ── GetClientLicenseInfo ───────────────────────────────────────────────── // MCLIENTDETAILS lives only in the GB5System database (see // PartnerBrandDAL.GetResolvedBrandForClient for the same pattern) — it must // never be joined into a tenant-DB query like GET_DBLEVELSETTING above. // ───────────────────────────────────────────────────────────────────────── public const string GET_CLIENT_LICENSE_INFO = @" SELECT ISNULL(CD.WEBUSERLICENSES, 0) AS DBLevelSettingWebUserLicenses, ISNULL(CD.HOSTINGVALIDTILL, CAST('1900-01-01' AS DATETIME)) AS DBLevelSettingHostingValidTill, ISNULL(CD.LICENCEVALIDTILL, CAST('1900-01-01' AS DATETIME)) AS DBLevelSettingLicenceValidTill, ISNULL(CD.SUPPORTVALIDTILL, CAST('1900-01-01' AS DATETIME)) AS DBLevelSettingSupportValidTill FROM MCLIENTDETAILS CD WHERE CD.CLIENTID = @clientid;"; } }