Gecko [ v3.cesa.co.za ]

Name	Size	Permission	Date
error_handling	[ DIR ]	drwxr-sr-x	2025-07-07 15:54
mail	[ DIR ]	drwxr-sr-x	2025-06-14 11:17
ADQAnswers.php	3.16 KB	-rw-r--r--	2026-06-03 05:55
ADQAudit.php	6.51 KB	-rw-r--r--	2026-06-03 05:55
ADQAuditAnswers.php	1.21 KB	-rw-r--r--	2026-06-03 05:55
ADQFirms.php	18.41 KB	-rw-r--r--	2025-12-31 10:56
ADQuestionnairesAnswers.php	1.05 KB	-rw-r--r--	2026-06-03 05:55
ADQuestionnairesAudit.php	2.01 KB	-rw-r--r--	2026-06-03 05:55
ADecQuestionnaires.php	3.31 KB	-rw-r--r--	2026-06-03 05:55
ADecQuestions.php	541 B	-rw-r--r--	2026-06-03 05:55
ADec_Contacts.php	1.61 KB	-rw-r--r--	2026-06-03 05:55
ADec_OfficeContacts.php	7.43 KB	-rw-r--r--	2026-06-03 05:55
ADec_PIUnderwriters.php	742 B	-rw-r--r--	2026-06-03 05:55
ADec_Transformation.php	817 B	-rw-r--r--	2026-06-03 05:55
ADec_tblMemberFirmOffices.php	1.49 KB	-rw-r--r--	2026-06-03 05:55
ADec_tblMemberFirmSkills.php	2.38 KB	-rw-r--r--	2026-06-03 05:55
ADec_tblMemberFirms.php	19.37 KB	-rw-r--r--	2026-06-03 05:55
AnonRespondees.php	4.25 KB	-rw-r--r--	2026-06-05 11:41
AwardsEntries.php	2.4 KB	-rw-r--r--	2026-06-06 10:48
BaseTableClass.php	950 B	-rw-r--r--	2026-04-26 08:24
BatchJob.php	4.46 KB	-rw-r--r--	2026-06-03 05:55
BceAssignmentFeedbackFormsReportService.php	6.77 KB	-rw-r--r--	2026-05-25 13:06
CCEAssignment.php	12.44 KB	-rw-r--r--	2026-05-26 14:26
CCEAssignmentMarking.php	26.26 KB	-rw-r--r--	2026-08-14 12:06
CCEBooks.php	524 B	-rw-r--r--	2026-06-03 05:55
CCEConvenorAuth.php	3.06 KB	-rw-r--r--	2026-08-19 11:18
CCECourseSchedules.php	579 B	-rw-r--r--	2026-06-03 05:55
CCEDocCategories.php	529 B	-rw-r--r--	2026-06-03 05:55
CCEDocuments.php	8.15 KB	-rw-r--r--	2026-08-20 12:16
CCEExam.php	15.87 KB	-rw-r--r--	2026-06-03 06:35
CCEExamQuestion.php	1.76 KB	-rw-r--r--	2026-06-03 05:55
CCEFacilitatorUser.php	931 B	-rw-r--r--	2026-06-03 05:55
CCEMessageLog.php	951 B	-rw-r--r--	2026-06-03 05:55
CCEMessages.php	8.88 KB	-rw-r--r--	2026-07-01 13:20
CCEModuleMarksService.php	7.16 KB	-rw-r--r--	2026-05-26 10:30
CCEModules.php	1.16 KB	-rw-r--r--	2026-06-03 05:55
CCEResourceGroups.php	591 B	-rw-r--r--	2026-06-03 05:55
CCEResources.php	896 B	-rw-r--r--	2026-06-03 05:55
CCESetup.php	256 B	-rw-r--r--	2026-02-09 16:20
CCEStAssigExtension.php	2.07 KB	-rw-r--r--	2026-06-03 05:55
CCEStAssigExtensionRequest.php	6.65 KB	-rw-r--r--	2026-06-03 05:55
CCEStudentAssignment.php	16.61 KB	-rw-r--r--	2026-07-15 14:44
CCEStudentCourses.php	611 B	-rw-r--r--	2026-06-03 05:55
CCEStudentDisplayOrder.php	4.96 KB	-rw-r--r--	2026-05-26 09:32
CCEStudents.php	8.4 KB	-rw-r--r--	2026-08-19 11:18
CCETemplates.php	896 B	-rw-r--r--	2026-06-03 05:55
CCEWeeklySessions.php	2.27 KB	-rw-r--r--	2026-02-09 16:25
CPDSPReview.php	624 B	-rw-r--r--	2026-06-03 05:55
CPDServiceProvider.php	4.57 KB	-rw-r--r--	2026-06-12 11:48
CPDTrainingMaterial.php	3.96 KB	-rw-r--r--	2026-06-12 11:48
CceUploadedFileService.php	34.9 KB	-rw-r--r--	2026-06-24 12:03
Certificates.php	8.93 KB	-rw-r--r--	2026-08-18 15:42
Contact.php	690 B	-rw-r--r--	2026-06-03 05:55
ErrorLog.php	1.22 KB	-rw-r--r--	2026-08-24 10:12
EventSchedules.php	5.39 KB	-rw-r--r--	2025-12-20 10:03
FileUploads.php	2.1 KB	-rw-r--r--	2026-06-03 05:55
HTMLHelper.php	17.2 KB	-rw-r--r--	2026-06-03 05:55
KeyValue.php	4.9 KB	-rw-r--r--	2026-04-25 09:34
Mailer.php	12.78 KB	-rw-r--r--	2026-07-15 09:50
MarketingAttendees.php	36.87 KB	-rw-r--r--	2026-08-14 12:06
MarketingPayments.php	2.52 KB	-rw-r--r--	2026-06-03 05:55
MemberContact.php	1.04 KB	-rw-r--r--	2026-06-03 05:55
MemberFirm.php	17.71 KB	-rw-r--r--	2026-06-03 05:55
MemberFirmOffices.php	752 B	-rw-r--r--	2026-06-03 05:55
MemberFirmSkill.php	18.04 KB	-rw-r--r--	2026-06-03 05:55
MemberPortalTransformationProxy.php	4.44 KB	-rw-r--r--	2026-05-20 09:08
MemberPortalUrls.php	1.82 KB	-rw-r--r--	2026-05-20 09:53
MsgAttachFileService.php	4.92 KB	-rw-r--r--	2026-06-22 08:37
OfficeContact.php	1.01 KB	-rw-r--r--	2026-06-03 05:55
Picklists.php	539 B	-rw-r--r--	2026-06-03 05:55
Questionnaires.php	849 B	-rw-r--r--	2026-01-23 12:39
SchoolAttendeeBulkMailerService.php	34.26 KB	-rw-r--r--	2026-08-11 10:45
SchoolAttendees.php	73.21 KB	-rw-r--r--	2026-08-11 10:45
SchoolCourses.php	7.98 KB	-rw-r--r--	2026-08-19 11:59
SchoolEvents.php	45.54 KB	-rw-r--r--	2026-08-18 15:42
SchoolPayments.php	4.48 KB	-rw-r--r--	2026-04-13 09:33
SchoolPresenters.php	1.86 KB	-rw-r--r--	2026-01-23 12:43
SchoolWeeksCoursesDashboardProxy.php	10.69 KB	-rw-r--r--	2026-08-14 12:32
TrainingProvider.php	8.06 KB	-rw-r--r--	2026-01-23 12:43
TrainingProviderDoc.php	554 B	-rw-r--r--	2026-06-03 05:55
Transformation.php	10.87 KB	-rw-r--r--	2026-05-13 09:36
UnempPractitioners.php	1.43 KB	-rw-r--r--	2026-06-03 05:55
Utils.php	13.72 KB	-rw-r--r--	2026-07-23 07:28
WeeklySessionsParser.php	7.87 KB	-rw-r--r--	2026-02-09 16:20
cceMessageAttachmentUpload.inc.php	5.12 KB	-rw-r--r--	2026-06-03 05:55
cceMessageEditor.inc.php	9.26 KB	-rw-r--r--	2026-07-01 13:20
cce_file_download_endpoint.php	1.75 KB	-rw-r--r--	2026-06-24 08:38
cstUnreadMessages.php	2.1 KB	-rw-r--r--	2026-07-01 13:20
cstWeeksCourses.php	1.84 KB	-rw-r--r--	2026-08-14 12:06
load_phpmailer.php	742 B	-rw-r--r--	2026-07-17 14:57
maill.php	19.45 KB	-rw-r--r--	2026-08-19 12:23
require_ewmysql.php	1.2 KB	-rw-r--r--	2026-08-24 10:12
sqlFunctions.php	10.48 KB	-rw-r--r--	2026-08-24 10:12

Rename

<?php

/*
 * Contact otto@igotafrica.com for details
 */

include_once('BaseTableClass.php');

/**
 * Control the  ADQFirms table
 *
 * @author Otto Saayman
 */
class ADQFirms extends BaseTableClass
{

public function findByMemberFirmIdQuestionnaireIdYear($memberFirmID, $questionnaireId, $auditYear)
    {
        return $this->sqlf->getRow("SELECT * FROM ADQFirms WHERE FirmID = $memberFirmID AND ADQID = $questionnaireId AND AuditYear = $auditYear");
    }

public function checkCreate($memberFirmID, $questionnaireId, $auditYear)
    {

$questionnaireFirm = $this->findByMemberFirmIdQuestionnaireIdYear($memberFirmID, $questionnaireId, $auditYear);

if (!is_countable($questionnaireFirm) || count($questionnaireFirm) <= 0) {

$nowDate = new DateTime();

$questionnaireFirm = array(
                'ADQID' => $questionnaireId,
                'FirmID' => $memberFirmID,
                'DateStarted' => $nowDate->format('Y-m-d H:i:s'),
                'AuditYear' => $auditYear
            );

$this->sqlf->autoExecute('ADQFirms', $questionnaireFirm, 'INSERT');

$questionnaireFirm = $this->findByMemberFirmIdQuestionnaireIdYear($memberFirmID, $questionnaireId, $auditYear);
        }

return $questionnaireFirm;
    }

public function getAllFromQId($qid)
    {

$sql = "SELECT * "
 . "FROM ADQFirms adqf "
 . "INNER JOIN tblMemberFirms tmf ON adqf.FirmID = tmf.FirmID "
 . "WHERE adqf.ADQID=" . $qid . " "
 . "AND tmf.FirmID <> 1312 "
 . "AND adqf.AuditYear > " . (date('Y') - 3) . " "
 . "ORDER BY tmf.FirmName";

return $this->sqlf->getAll($sql);
    }

public function getOverview($filters)
    {

$sql = "SELECT ADQFirms.FirmID,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQFirms.ADQFirmID ELSE NULL END) AS ADQFirmIDFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQFirms.ADQFirmID ELSE NULL END) AS ADQFirmIDQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQFirms.ADQFirmID ELSE NULL END) AS ADQFirmIDPSM,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQFirms.ScorePerc ELSE NULL END) AS ScorePercFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQFirms.ScorePerc ELSE NULL END) AS ScorePercQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQFirms.ScorePerc ELSE NULL END) AS ScorePercPSM,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQFirms.AuditScorePerc ELSE NULL END) AS AuditScorePercFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQFirms.AuditScorePerc ELSE NULL END) AS AuditScorePercQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQFirms.AuditScorePerc ELSE NULL END) AS AuditScorePercPSM,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQFirms.DateStarted ELSE NULL END) AS DateStartedFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQFirms.DateStarted ELSE NULL END) AS DateStartedQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQFirms.DateStarted ELSE NULL END) AS DateStartedPSM,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQFirms.DateCompleted ELSE NULL END) AS DateCompletedFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQFirms.DateCompleted ELSE NULL END) AS DateCompletedQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQFirms.DateCompleted ELSE NULL END) AS DateCompletedPSM,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQFirms.ADQFirmID ELSE NULL END) AS ADQFirmIDFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQFirms.ADQFirmID ELSE NULL END) AS ADQFirmIDQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQFirms.ADQFirmID ELSE NULL END) AS ADQFirmIDPSM,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQFirms.CESAApproved ELSE NULL END) AS CESAApprovedFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQFirms.CESAApproved ELSE NULL END) AS CESAApprovedQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQFirms.CESAApproved ELSE NULL END) AS CESAApprovedPSM,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQAudit.Verified ELSE NULL END) AS VerifiedFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQAudit.Verified ELSE NULL END) AS VerifiedQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQAudit.Verified ELSE NULL END) AS VerifiedPSM,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN (SELECT COUNT(*) AS AnswerCount 
 FROM `ADQAnswers` a 
 INNER JOIN ADQuestions q ON a.ADQuestionID = q.ADQuestionID 
 WHERE q.ADQID = 1 AND a.ADQFirmID = ADQFirms.ADQFirmID) ELSE NULL END) AS AnswerCountFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN (SELECT COUNT(*) AS AnswerCount 
 FROM `ADQAnswers` a 
 INNER JOIN ADQuestions q ON a.ADQuestionID = q.ADQuestionID 
 WHERE q.ADQID = 2 AND a.ADQFirmID = ADQFirms.ADQFirmID) ELSE NULL END) AS AnswerCountQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN (SELECT COUNT(*) AS AnswerCount 
 FROM `ADQAnswers` a 
 INNER JOIN ADQuestions q ON a.ADQuestionID = q.ADQuestionID 
 WHERE q.ADQID = 3 AND a.ADQFirmID = ADQFirms.ADQFirmID) ELSE NULL END) AS AnswerCountPSM,
 tblMemberFirms.FirmName, tblMemberFirms.DateJoined, MAX(ADQAuditID) AS ADQAuditID, 
 MIN(ADQAudit.ADQAuditID) AS FirstADQAuditID, tblMemberFirms.DateJoined
 FROM ADQFirms JOIN tblMemberFirms USING (FirmID)
 LEFT JOIN ADQAudit USING (ADQFirmID)
 WHERE ADQFirms.FirmID<>1312 ";

if (isset($filters["filteraudit"]) && !empty($filters["filteraudit"])) {
            if ($filters["filteraudit"] == "P") {
                $sql .= " AND ADQAudit.Verified=''";
            } else {
                $sql .= " AND ADQAudit.Verified='" . addslashes($filters["filteraudit"]) . "'";
            }
        }

if (isset($filters["auditYear"]) && $filters["auditYear"] != 'all') {
            $sql .= " AND ADQFirms.AuditYear = " . $filters["auditYear"];
        }

$sql .= "
GROUP BY ADQFirms.FirmID, tblMemberFirms.FirmName, tblMemberFirms.DateJoined
ORDER BY FirmName";

// echo $sql . ' ';

return $this->sqlf->getAll($sql);
    }

public function getStats($filters)
    {

$sql = "SELECT COUNT(ADQFirms.FirmID) AS FirmCount,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQFirms.ADQFirmID ELSE NULL END) AS ADQFirmIDFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQFirms.ADQFirmID ELSE NULL END) AS ADQFirmIDQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQFirms.ADQFirmID ELSE NULL END) AS ADQFirmIDPSM,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQFirms.ScorePerc ELSE NULL END) AS ScorePercFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQFirms.ScorePerc ELSE NULL END) AS ScorePercQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQFirms.ScorePerc ELSE NULL END) AS ScorePercPSM,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQFirms.AuditScorePerc ELSE NULL END) AS AuditScorePercFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQFirms.AuditScorePerc ELSE NULL END) AS AuditScorePercQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQFirms.AuditScorePerc ELSE NULL END) AS AuditScorePercPSM,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQFirms.DateStarted ELSE NULL END) AS DateStartedFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQFirms.DateStarted ELSE NULL END) AS DateStartedQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQFirms.DateStarted ELSE NULL END) AS DateStartedPSM,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQFirms.DateCompleted ELSE NULL END) AS DateCompletedFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQFirms.DateCompleted ELSE NULL END) AS DateCompletedQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQFirms.DateCompleted ELSE NULL END) AS DateCompletedPSM,
 SUM(CASE WHEN ADQFirms.ADQID = 1 AND ADQFirms.DateCompleted IS NOT NULL THEN 1 ELSE 0 END) AS DateCompletedFIMSCnt,
 SUM(CASE WHEN ADQFirms.ADQID = 2 AND ADQFirms.DateCompleted IS NOT NULL THEN 1 ELSE 0 END) AS DateCompletedQMSCnt,
 SUM(CASE WHEN ADQFirms.ADQID = 3 AND ADQFirms.DateCompleted IS NOT NULL THEN 1 ELSE 0 END) AS DateCompletedPSMCnt,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQFirms.ADQFirmID ELSE NULL END) AS ADQFirmIDFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQFirms.ADQFirmID ELSE NULL END) AS ADQFirmIDQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQFirms.ADQFirmID ELSE NULL END) AS ADQFirmIDPSM,
 MAX(CASE WHEN ADQFirms.ADQID = 1 THEN ADQFirms.CESAApproved ELSE NULL END) AS CESAApprovedFIMS,
 MAX(CASE WHEN ADQFirms.ADQID = 2 THEN ADQFirms.CESAApproved ELSE NULL END) AS CESAApprovedQMS,
 MAX(CASE WHEN ADQFirms.ADQID = 3 THEN ADQFirms.CESAApproved ELSE NULL END) AS CESAApprovedPSM,
 MAX(ADQAuditID) AS ADQAuditID, MIN(ADQAudit.ADQAuditID) AS FirstADQAuditID
FROM ADQFirms JOIN tblMemberFirms USING (FirmID)
LEFT JOIN ADQAudit USING (ADQFirmID)
WHERE ADQFirms.FirmID<>1312 ";
 if (isset($filters["filteraudit"]) && !empty($filters["filteraudit"])) {
 if ($filters["filteraudit"] == "P") {
 $sql .= " AND ADQAudit.Verified=''";
 } else {
 $sql .= " AND ADQAudit.Verified='" . addslashes($filters["filteraudit"]) . "'";
 }
 }

if (isset($filters["auditYear"]) && $filters["auditYear"] != 'all') {
            $sql .= " AND ADQFirms.AuditYear = " . $filters["auditYear"];
        }

$stats = $this->sqlf->getAll($sql);

return $stats[0];
    }

public function getMSDStartDate($firmId, $qid, $auditYear)
    {
        $sql = "SELECT * FROM ADQFirms adqf "
            . "WHERE adqf.FirmID=" . $firmId . " AND adqf.ADQID=" . $qid . " AND adqf.AuditYear=" . $auditYear;

$aDecQuestionnaire = $this->sqlf->getRow($sql);

if (!is_array($aDecQuestionnaire) || count($aDecQuestionnaire) <= 0) {
 return '';
 }

$MSDDate = new DateTime($aDecQuestionnaire['DateStarted']);

return $MSDDate->format('Y-m-d');
    }

public static function answerCountIndicator($answerCount) {
 if ($answerCount <= 0) {
 return '0';
 } elseif ($answerCount <= 10) {
 return '' . $answerCount . '';
 } elseif ($answerCount <= 20) {
 return '' . $answerCount . '';
 } elseif ($answerCount <= 30) {
 return '' . $answerCount . '';
 } else {
 return '' . $answerCount . '';
 }
 }

/**
     * Get all member firms with verified QMS in MSD system
     * 
     * Returns firms that have completed QMS questionnaires (ADQID=2) 
     * with verified status (Verified='Y' or 'C') in ADQAudit
     * 
     * @return array Array of firms with FirmID, FirmName, CurrentQMSISOCert, and VerifiedStatus
     */
    public function getFirmsWithVerifiedQMS()
    {
        $sql = "SELECT DISTINCT 
                    af.FirmID,
                    tmf.FirmName,
                    tmf.QMSISOCert AS CurrentQMSISOCert,
                    MAX(CASE WHEN adqa.Verified IN ('Y', 'C') THEN 'Y' ELSE 'N' END) AS VerifiedStatus
                FROM ADQFirms af
                INNER JOIN tblMemberFirms tmf ON af.FirmID = tmf.FirmID
                LEFT JOIN ADQAudit adqa ON af.ADQFirmID = adqa.ADQFirmID
                WHERE af.ADQID = 2  -- QMS questionnaire
                    AND af.DateCompleted IS NOT NULL  -- Only completed questionnaires
                    AND adqa.Verified IN ('Y', 'C')  -- Verified or Verified With Comments
                GROUP BY af.FirmID, tmf.FirmName, tmf.QMSISOCert";

return $this->sqlf->getAll($sql);
    }

/**
     * Get firms that should have QMSISOCert reset to 'N'
     * 
     * Returns firms that currently have QMSISOCert='Y' but no longer have
     * a verified QMS in the MSD system. This handles cases where verification
     * was removed or expired.
     * 
     * @return array Array of firms with FirmID, FirmName, and QMSISOCert
     */
    public function getFirmsWithoutVerifiedQMS()
    {
        $sql = "SELECT DISTINCT 
                    tmf.FirmID,
                    tmf.FirmName,
                    tmf.QMSISOCert
                  FROM tblMemberFirms tmf
                  WHERE tmf.QMSISOCert = 'Y'
                    AND tmf.FirmID NOT IN (
                        SELECT DISTINCT af.FirmID
                        FROM ADQFirms af
                        LEFT JOIN ADQAudit adqa ON af.ADQFirmID = adqa.ADQFirmID
                        WHERE af.ADQID = 2
                          AND af.DateCompleted IS NOT NULL
                          AND adqa.Verified IN ('Y', 'C')
                    )";

return $this->sqlf->getAll($sql);
    }

/**
     * Update QMSISOCert status for a specific firm
     * 
     * @param int $firmID The FirmID to update
     * @param string $status The status to set ('Y' or 'N')
     * @return bool True on success, false on failure
     */
    public function updateQMSISOCertStatus($firmID, $status)
    {
        $firmID = intval($firmID);
        $status = $this->sqlf->qstr($status);

$updateSql = "UPDATE tblMemberFirms 
                     SET QMSISOCert = {$status} 
                     WHERE FirmID = {$firmID}";

return $this->sqlf->execute($updateSql);
    }

/**
     * Get list of firms that would be updated (dry-run mode)
     * 
     * Returns an array of firms that would be updated without actually
     * performing the database updates. Useful for testing and preview.
     * 
     * @return array Array with 'toUpdate' (firms to set to 'Y'), 'toReset' (firms to set to 'N'), and 'skipped' counts
     */
    public function getFirmsToUpdate()
    {
        $toUpdate = array();
        $toReset = array();
        $skipped = 0;

// Get firms with verified QMS
        $firms = $this->getFirmsWithVerifiedQMS();

foreach ($firms as $firm) {
            $firmID = $firm['FirmID'];
            $firmName = $firm['FirmName'];
            $currentStatus = $firm['CurrentQMSISOCert'];
            $verifiedStatus = $firm['VerifiedStatus'];

// Only include if status has changed
            if ($currentStatus != $verifiedStatus) {
                $toUpdate[] = array(
                    'FirmID' => $firmID,
                    'FirmName' => $firmName,
                    'CurrentStatus' => $currentStatus,
                    'NewStatus' => $verifiedStatus
                );
            } else {
                $skipped++;
            }
        }

// Get firms that should have QMSISOCert reset to 'N'
        $firmsNoVerified = $this->getFirmsWithoutVerifiedQMS();

foreach ($firmsNoVerified as $firm) {
            $firmID = $firm['FirmID'];
            $firmName = $firm['FirmName'];

$toReset[] = array(
                'FirmID' => $firmID,
                'FirmName' => $firmName,
                'CurrentStatus' => $firm['QMSISOCert'],
                'NewStatus' => 'N'
            );
        }

return array(
            'toUpdate' => $toUpdate,
            'toReset' => $toReset,
            'skipped' => $skipped
        );
    }

/**
     * Sync QMSISOCert status in tblMemberFirms based on MSD verifications
     * 
     * This method:
     * 1. Finds all firms with verified QMS and updates their QMSISOCert to 'Y'
     * 2. Finds firms that should have QMSISOCert reset to 'N' (no longer verified)
     * 
     * @param bool $dryRun If true, only returns what would be updated without making changes
     * @return array Statistics array with 'updated', 'skipped', and 'errors' counts, or dry-run data if $dryRun is true
     */
    public function syncQMSISOCertStatus($dryRun = false)
    {
        include_once($_SERVER['DOCUMENT_ROOT'] . '/php/includes/ErrorLog.php');

// If dry run, return what would be updated
        if ($dryRun) {
            return $this->getFirmsToUpdate();
        }

$updated = 0;
        $skipped = 0;
        $errors = 0;

// Get firms with verified QMS
        $firms = $this->getFirmsWithVerifiedQMS();

// Only update if status has changed
            if ($currentStatus != $verifiedStatus) {
                try {
                    $result = $this->updateQMSISOCertStatus($firmID, $verifiedStatus);

if ($result) {
                        $updated++;
                        ErrorLog::logInfo("Updated QMSISOCert for FirmID {$firmID} ({$firmName}) from '{$currentStatus}' to '{$verifiedStatus}'");
                    } else {
                        $errors++;
                        ErrorLog::logError("Failed to update QMSISOCert for FirmID {$firmID} ({$firmName})");
                    }
                } catch (Exception $e) {
                    $errors++;
                    ErrorLog::logError("Error updating QMSISOCert for FirmID {$firmID} ({$firmName}): " . $e->getMessage());
                }
            } else {
                $skipped++;
            }
        }

// Get firms that should have QMSISOCert reset to 'N'
        $firmsNoVerified = $this->getFirmsWithoutVerifiedQMS();

foreach ($firmsNoVerified as $firm) {
            $firmID = $firm['FirmID'];
            $firmName = $firm['FirmName'];

try {
                $result = $this->updateQMSISOCertStatus($firmID, 'N');

if ($result) {
                    $updated++;
                    ErrorLog::logInfo("Reset QMSISOCert to 'N' for FirmID {$firmID} ({$firmName}) - no verified QMS found");
                } else {
                    $errors++;
                    ErrorLog::logError("Failed to reset QMSISOCert for FirmID {$firmID} ({$firmName})");
                }
            } catch (Exception $e) {
                $errors++;
                ErrorLog::logError("Error resetting QMSISOCert for FirmID {$firmID} ({$firmName}): " . $e->getMessage());
            }
        }

return array(
            'updated' => $updated,
            'skipped' => $skipped,
            'errors' => $errors
        );
    }
}