OAT » TA Admin

preguntar sobre la funcionalidad esperada (no existe sourceCode en calendarEntries)

Señalamientos creados desde Admin no se pueden editar desde Jueces

--Calendar Entries table update

--add colums to calendar with null for 
ALTER TABLE dbo.calendarEntries ADD calendarEntrySourceCode int NULL;
ALTER TABLE dbo.calendarEntries ADD calendarEntrySourceDescription varchar(250) NULL;
ALTER TABLE dbo.calendarEntries ADD CONSTRAINT FK_calendar_entries_source_code FOREIGN KEY (calendarEntrySourceCode) REFERENCES dbo.catalogValues(catalogValueId);


--change descriptions of existents source codes
UPDATE catalogTypes 
SET catalogTypeDescription = 'source'
WHERE catalogTypeCode = 33

UPDATE catalogValues
SET catalogTypeDescription = 'source'
WHERE catalogTypeCode = 33


-- all ids that create a calendar entry row
--persons with cfi user id
SELECT DISTINCT 
	','+CAST(p.CFI_PersonId AS VARCHAR), p.personId 
FROM calendarEntries ce 
INNER JOIN persons p 
ON p.personId = ce.creatorId 
AND p.CFI_UserId IS NOT NULL
WHERE ce.isDeleted = 0


-- run this query in the cfi db
-- copy all the rows from the column cfi person id of last query and paste it in the IN of the next query
SELECT DISTINCT ue.UserId 
,ar.RoleName
,ar.RoleId 
,p.FullName
,','+CAST(p.PersonId AS VARCHAR)
FROM SUMACUserExtension ue
INNER JOIN SUMACPersonBase p
	ON p.PersonId = ue.PersonId 
	AND p.IsHidden = 0
INNER JOIN aspnet_UsersInRoles uir
	ON uir.UserId = ue.UserId
	AND uir.RoleId IN ('E38BA3CC-DB90-4015-A7B7-B3D8E59162DE', '1404143B-53C7-44F0-B090-2B7A82A5641E') -- system adm y appeals judge
INNER JOIN aspnet_Roles ar 
	ON uir.RoleId = ar.RoleId
WHERE ue.PersonId IN (
	--?
)
--	AND ue.isHidden = 0
ORDER BY RoleName


-- back to coa db
-- copy the rows where are appeal judge role id from the column person id of last query and paste it in the IN of the next query
--edit to judge source
UPDATE ce
SET calendarEntrySourceCode = 109 --judge
FROM calendarEntries ce
INNER JOIN persons p ON p.personId = ce.creatorId 
WHERE calendarEntrySourceCode IS NULL
	AND p.CFI_PersonId IN (
	-- ?
	) -- personIds
	

-- copy the rows where are system adm role id from the column person id of last query and paste it in the IN of the next query
--edit to admin source
UPDATE ce 
SET calendarEntrySourceCode = 108 --admin
FROM calendarEntries ce
INNER JOIN persons p ON p.personId = ce.creatorId
WHERE calendarEntrySourceCode IS NULL
AND p.CFI_PersonId IN (
-- ?
) -- personIds


-- run this query to validate if all entries has been affected by the other two query
-- if are rows affected means that some entry has a person that have another role id 
-- add admin source code to the remaining
UPDATE calendarEntries 
SET calendarEntrySourceCode = 108
WHERE calendarEntrySourceCode IS NULL


-- setting description based on the source codes added
UPDATE calendarEntries 
SET calendarEntrySourceDescription = dbo.getCatalogValueDescription(ce.calendarEntrySourceCode)
FROM calendarEntries ce 
WHERE calendarEntrySourceDescription IS NULL


-- set not null to the two new columns
ALTER TABLE dbo.calendarEntries ALTER COLUMN calendarEntrySourceCode int NOT NULL;
ALTER TABLE dbo.calendarEntries ALTER COLUMN calendarEntrySourceDescription varchar(250) COLLATE Modern_Spanish_100_CI_AI_SC_UTF8 NOT NULL;