...
| 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') AND
((now() BETWEEN cm.CurriculumStartDate AND cm.CurriculumEndDate) OR cm.CurriculumStartDate> now())
) 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 = "2. Kav rules"
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') AND
((now() BETWEEN cm.CurriculumStartDate AND cm.CurriculumEndDate) or cm.CurriculumStartDate> now())
) 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 = "2. Kav rules"
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 |