Content Comparison

CREATE DEFINER=`root`@`%` PROCEDURE `2`3x_kav_indate_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 = "23x. Kav rulesindate" 
    WHERE linkedPmUuid IS NULL AND
    pm_current_bufferedin_monthswindow(c.programmeMembershipUuid, wtea.submissionDateDate, 12, 64) = 1 AND -- ended startedwithin 6+12 months before submission date, starts finisheswithin 6+4 months after submission date
    pms_current_bufferedin_monthswindow(f.traineeTisId, wtea.submissionDateDate, 6) = 1 AND -- only pm current 6+ months before, 6+ months after
    pms_current_buffered_months(f.traineeTisId, wtea.submissionDateDate, 012, 4) = 1; -- only pm current when submittedthat falls in the window
END

CREATE DEFINER=`root`@`%` FUNCTION `pm_current_bufferedin_months`window`(theuuid VARCHAR(50), dt DATE, mnthsmarginbefore INTEGER, mnthsmarginmnthsmarginafter INTEGER) RETURNS tinyint(1)
BEGIN
	DECLARE CNT INTEGER DEFAULT 0;
SELECT 
    COUNT(*)
INTO CNT FROM
    tcs.ProgrammeMembership
WHERE
    uuid = theuuid
        AND DATE_ADD(programmeStartDateprogrammeEndDate,
        INTERVAL mnthsmarginmnthsmarginbefore MONTH) <>= dt
        AND DATE_ADD(programmeEndDateprogrammeStartDate,
        INTERVAL - 1 * mnthsmarginmnthsmarginafter MONTH) ><= dt;
        
  RETURN CNT > 0;
END

CREATE DEFINER=`root`@`%` FUNCTION `pms_current_bufferedin_months`window`(traineeId VARCHAR(50), dt DATE, mnthsmarginmnthsmarginbefore 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(programmeStartDateprogrammeEndDate,
        INTERVAL mnthsmarginmnthsmarginbefore MONTH) <>= dt
        AND DATE_ADD(programmeEndDateprogrammeStartDate,
        INTERVAL - 1 * mnthsmarginmnthsmarginafter MONTH) ><= dt;
  RETURN PMS;
END