Collapse multiple copies of the same user into one

For now, these are just notes towards the goal

If an extra account is created, it is because you are missing details.

They are creating 'new' history.

If you catch it before there is history, you can just 'set it' to the old one.

If you don't catch it, wait 6 months, now there is history, and you'll need to edit data, and can have impossible to resolve issues. It gets messier the longer you wait.

MCe user module, MC member module:

Find the ones you want to get rid of. Delete the bad ones.

The new one will be LoginHub GUID

image.png image.png image.png image.png

When LoginHub sends you a user,

The most common you will want is either the 'Match Email in Registration Database" or "Match Email in Entity Database"

If there are 2 users with the same email, it will not do a match to either of them.

image.png

Find Dups:

-- Find all rows with duplicate email addresses
SELECT l.laborpk,l.laborid,l.laborname,l.email -- l.*
FROM labor l
JOIN (
    SELECT email
    FROM labor
    WHERE email IS NOT NULL AND email <> ''  -- Ignore NULL or empty emails
    GROUP BY email
    HAVING COUNT(*) > 1
) dup ON l.email = dup.email
ORDER BY l.email, l.laborpk;
 
-- just find list of dups, returns only the emails
    SELECT email
    FROM labor
    WHERE email IS NOT NULL AND email <> ''  -- Ignore NULL or empty emails
    GROUP BY email
    HAVING COUNT(*) > 1

All the tables that have a laborpk

SELECT t.name AS TableName, c.name AS ColName, '          '
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name = 'laborpk'
Order by TableName
Tables with Laborpk:
apsAssignmentRuleLabors
apsAutomationNotes
apsLaborPushConfigurations
apsLaborScheduleExceptions
apsLaborSchedules
apsLicenseInUse
apsLicensePermissions
apsTempAttachments
AssetLabor
AssetNote
AssetRequester
ChatTo
ClassificationLabor
CompanyLabor
CompanyNote
Contract
ContractNote
DebugSQL
DepartmentLabor
DepartmentNote
Document
FavoriteGroup
GISLayerLabor
InOutTransaction
InterfaceUser
KeyData
KeyTransaction
Labor
LaborAddressBook
LaborAsset
LaborAssetFilter
LaborAssetMgr
LaborDocument
LaborNote
LaborPreference
LaborReportGroup
LaborTimeOff
LaborTraining
LaborWorkHours
LaborWorkSchedule
MC_ExceptionLog
MCEmailLog
MCModuleGridColumns
MCRuleLabor
MCRuleLaborAlert
PartNote
PartTransaction
PMNote
PredictiveNote
ProcedureLabor
ProcedureMiscCost
ProcedureNote
ProjectNote
PurchaseOrderDetail
PurchaseOrderNote
PurchaseOrderPending
RepairCenterNote
ReportFieldsUsers
ReportGroup
ReportSchedule
ReportScheduleReport
ReservationLabor
ShopNote
SQLSchedule
TenantLabor
TenantNote
ToolNote
TrainingNote
WOAssign
WOAssignStatus
WOLabor
WOMiscCost
WONote
WORequester
WOStartStop

Has a LaborGUID but not LaborPK

aps TempFileParts

Tables with Other LaborPK columns

TableName,            ColName
apsWOTimer,         LaborPKManager
apsWOTimer,         LaborPKTimer
Asset,                       RotatingLaborPK
LaborNote,              LaborPK_Note
MCEmailLog,           LaborTrainingPK
PartLocation,          ItemCountLaborPK
PartTransaction,    AssetDeleteLaborPK
PartTransaction,    LaborToPK
PartTransaction,    LocationDeleteLaborPK
PartTransaction,    PODeleteLaborPK
PartTransaction,    PODetailDeleteLaborPK
PartTransaction,    ReceiptDeleteLaborPK
PartTransaction,    ReceiptLineDeleteLaborPK
PartTransaction,    WODeleteLaborPK
PartTransaction,    WOPartDeleteLaborPK
Reports,                   SmartEmailLaborPK
ReservationLabor, ReservationLaborPK
Signature,                SignatureLaborPK
WOAssign,               WOlaborPK
WOPart,                   LaborToPK
WOStartStop,          WOLaborPK

Tables with laborID

apsAssignmentRuleLabors
apsAuditLog--
apsSystemQueries--
apsTransactionAuditLog--
apsUIConfig--
Contract
Document
InterfaceUser
Labor
ProcedureLabor
ProcedureMiscCost
PurchaseOrderDetail
PurchaseOrderPending
WOAssign
WOLabor
WOMiscCost

Tables with laborName

apsAssignmentRuleLabors
ChatTo--
Contract
DebugSQL--
Document
InterfaceUser
Labor
MC_ExceptionLog--
ProcedureLabor
ProcedureMiscCost
PurchaseOrderDetail
PurchaseOrderPending
WOAssign
WOLabor
WOMiscCost

So you would go through and find all the records for pk ToKeep and set the pk,id,name to those from ToMerge

update <TABLE> set laborpk = @ToPK, laborid =@ToID, laborname =@ToName where laborpk = @FromPK

-- Declare parameters (SQL Server syntax; adjust for MySQL/PostgreSQL if needed)
DECLARE @FromPK INT = 52;       -- Old laborpk, this one will cease to exist
DECLARE @ToPK   INT = 51;       -- New laborpk, all the one above will become this one
DECLARE @ToID   NVARCHAR(50) = 'New Labor ID';
DECLARE @ToName NVARCHAR(100) = 'New Labor Name';
DECLARE @apsSyncGuid uniqueIdentifier;
 
-- Get the laborid and laborname for the ToPK from the Labor table
SELECT
    @ToID = laborid,
    @ToName = laborname,
    @apsSyncGUID = apsSyncGuid
FROM Labor
WHERE laborpk = @ToPK;
 
-- Safety check: ensure we found a match
IF @ToID IS NULL OR @ToName IS NULL
BEGIN
    PRINT 'Error: No matching labor record found for @ToPK.';
    RETURN;
END
 
-- List of tables to update
-- Each UPDATE is separate to avoid cross-table locking issues
UPDATE apsAssignmentRuleLabors
SET laborpk = @ToPK, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
--apsAuditLog has laborid
 
UPDATE apsAutomationNotes
SET laborpk = @ToPK -- name = @ToName
WHERE laborpk = @FromPK;
 
UPDATE apsLaborPushConfigurations
SET laborpk = @ToPK-- , laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE apsLaborScheduleExceptions
SET laborpk = @ToPK --, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE apsLaborSchedules
SET laborpk = @ToPK --, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE apsLicenseInUse
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE apsLicensePermissions
SET laborpk = @ToPK --, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
-- apsSystemQueries has Laborid
 
UPDATE apsTempAttachments
SET laborpk = @ToPK --, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
-- aopsTempFileParts has a labor GUID
 
-- apsTransactionAuditLog has laborid
 
-- apsUIConfig has laborid
 
UPDATE AssetLabor
SET laborpk = @ToPK --, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE AssetNote
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE AssetRequester
SET laborpk = @ToPK, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE ChatTo
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE ClassificationLabor
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE CompanyLabor
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE CompanyNote
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE Contract
SET laborpk = @ToPK, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE ContractNote
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE DebugSQL
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE DepartmentLabor
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE DepartmentNote
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE Document
SET laborpk = @ToPK, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE FavoriteGroup
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE GISLayerLabor
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE InOutTransaction
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE InterfaceUser
SET laborpk = @ToPK, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE KeyData
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE KeyTransaction
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE Labor
SET laborpk = @ToPK, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE LaborAddressBook
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE LaborAsset
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE LaborAssetFilter
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE LaborAssetMgr
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE LaborDocument
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE LaborNote
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE LaborPreference
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE LaborReportGroup
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE LaborTimeOff
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE LaborTraining
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE LaborWorkHours
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE LaborWorkSchedule
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
WHERE laborpk = @FromPK;
 
UPDATE MC_ExceptionLog
SET laborpk = @ToPK--, laborid = @ToID, laborname = @ToName
*/