...
| Code Block |
|---|
CREATE DEFINER=`root`@`%` FUNCTION `pms_current_in_window`(traineeId VARCHAR(50), dt DATE, mnthsmarginbefore INTEGER, mnthsmarginafter INTEGER) RETURNS int(11)
BEGIN
DECLARE PMS INTEGER DEFAULT 0;
SELECT
COUNT(*)
INTO PMS FROM
tcs.ProgrammeMembership
WHERE
personId = traineeId
AND DATE_ADD(programmeEndDate,
INTERVAL mnthsmarginbefore MONTH) >= dt
AND DATE_ADD(programmeStartDate,
INTERVAL - 1 * mnthsmarginafter MONTH) <= dt;
RETURN PMS;
END |
E. Unique programme membership matching local office and programme details:
| Code Block |
|---|
CREATE DEFINER=`root`@`%` PROCEDURE `2_kav_a`()
BEGIN
UPDATE stats_formra f
JOIN rr.formra wtea ON f.id = wtea.id
JOIN (SELECT pm.personId
,pm.programmeID
,p.stdSpecialtyProgramme
,p.programmeNumber
,pp.owner AS managingDeanery
,pm.programmeStartDate
,cm.curriculumCompletionDate
,c.name AS curriculumName
,s.name AS specialtyName
,pm.uuid AS programmeMembershipUuid
FROM tcs.CurriculumMembership cm
JOIN tcs.Curriculum c ON cm.curriculumId = c.id
JOIN tcs.Specialty s ON c.specialtyId = s.id
JOIN tcs.ProgrammeMembership pm ON cm.programmeMembershipUuid = pm.uuid
LEFT JOIN rr.stdProgramme p ON pm.ProgrammeID = p.programmeTisId
LEFT JOIN tcs.Programme pp ON pm.ProgrammeID = pp.id
WHERE s.name NOT IN ('Academic', 'Foundation')
) c
ON c.personId = wtea.traineeTisId
AND c.managingDeanery = wtea.localOfficeNameStd
AND (
c.stdSpecialtyProgramme = wtea.programmeSpecialty
OR c.stdSpecialtyProgramme = wtea.cctSpecialty1
OR c.stdSpecialtyProgramme = wtea.cctSpecialty2
OR c.specialtyName = wtea.programmeSpecialty
OR c.specialtyName = wtea.cctSpecialty1
OR c.specialtyName = wtea.cctSpecialty2
OR c.curriculumName = wtea.programmeSpecialty
OR c.CurriculumName = wtea.cctSpecialty1
OR c.CurriculumName = wtea.cctSpecialty2
)
SET
f.linkedPmUuid = c.programmeMembershipUuid, linkedBy = "3. Kav indate"
WHERE linkedPmUuid IS NULL AND
pm_current_buffered_months(c.programmeMembershipUuid, wtea.submissionDateDate, 6) AND -- started 6+ months before, finishes 6+ months after submission date
pms_current_buffered_months(f.traineeTisId, wtea.submissionDateDate, 6) = 1 AND -- only pm current 6+ months before, 6+ months after
pms_current_buffered_months(f.traineeTisId, wtea.submissionDateDate, 0) = 1; -- only pm current when submitted
END |
| Code Block |
|---|
CREATE DEFINER=`root`@`%` PROCEDURE `2_kav_b`()
BEGIN
UPDATE stats_formrb f
JOIN rr.formrb wtea ON f.id = wtea.id
JOIN (SELECT pm.personId
,pm.programmeID
,p.stdSpecialtyProgramme
,p.programmeNumber
,pp.owner as managingDeanery
,pm.programmeStartDate
,cm.curriculumCompletionDate
,c.name as curriculumName
,s.name as specialtyName
,pm.uuid as programmeMembershipUuid
FROM tcs.CurriculumMembership cm
JOIN tcs.Curriculum c ON cm.curriculumId = c.id
JOIN tcs.Specialty s ON c.specialtyId = s.id
JOIN tcs.ProgrammeMembership pm ON cm.programmeMembershipUuid = pm.uuid
LEFT JOIN rr.stdProgramme p ON pm.ProgrammeID = p.programmeTisId
LEFT JOIN tcs.Programme pp ON pm.ProgrammeID = pp.id
WHERE s.name NOT IN ('Academic', 'Foundation')
) c
ON c.personId = wtea.traineeTisId
AND c.managingDeanery = wtea.localOfficeNameStd
AND (
c.stdSpecialtyProgramme = wtea.programmeSpecialty
OR c.stdSpecialtyProgramme = wtea.dualSpecialty
OR c.specialtyName = wtea.programmeSpecialty
OR c.specialtyName = wtea.dualSpecialty
OR c.curriculumName = wtea.programmeSpecialty
OR c.curriculumName = wtea.dualSpecialty
)
SET
f.linkedPmUuid = c.programmeMembershipUuid, linkedBy = "3. Kav indate"
WHERE linkedPmUuid IS NULL AND
pm_current_buffered_months(c.programmeMembershipUuid, wtea.submissionDateDate, 6) AND -- started 6+ months before, finishes 6+ months after submission date
pms_current_buffered_months(f.traineeTisId, wtea.submissionDateDate, 6) = 1 AND -- only pm current 6+ months before, 6+ months after
pms_current_buffered_months(f.traineeTisId, wtea.submissionDateDate, 0) = 1; -- only pm current when submitted
END |