Content Comparison

CREATE DEFINER=`root`@`%` FUNCTION `pms_current_buffered_months`(traineeId VARCHAR(50), dt DATE, mnthsmargin INTEGER) RETURNS int(11)
BEGIN
  DECLARE PMS INTEGER DEFAULT 0;

SELECT 
    COUNT(*)
INTO PMS FROM
    tcs.ProgrammeMembership
WHERE
    personId = traineeId
        AND DATE_ADD(programmeStartDate,
        INTERVAL mnthsmargin MONTH) <= dt
        AND DATE_ADD(programmeEndDate,
        INTERVAL - 1 * mnthsmargin MONTH) >= dt;
  RETURN PMS;
END

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