Online PHP editor | output for hZavl

<?php /** * @throws \Paycom_Dbal_Exception * @throws \Exception */ private function fetchCertApprovedAndAwaitingVerificationByEeCode( string $clientCode, array $eeCodes = array(), bool $showArchivedEeCert = false ) : array { $qb = $this->connection->createQueryBuilder(); $qb->select([ 'eeCert.eecode AS eeCode', 'eeCert.verification_status AS verificationStatus', 'approvers.approver_type AS approverType', 'approvers.approver AS approverUserName', 'user.userstatus AS approverStatus', 'emp.supervsr1 AS supervisorEeCode', 'sup_user.username AS supervisorUsername', 'sup_user.userstatus AS supervisorStatus' ]) ->from('lms_cm_ee_cert_master', 'master') ->innerJoin( 'master', 'lms_cm_certification', 'cert', $qb->expr()->andX( $qb->expr()->eq('master.clientcode', 'cert.clientcode'), $qb->expr()->eq('master.cert_id', 'cert.id') ) ) ->innerJoin( 'master', 'lms_cm_ee_certification', 'eeCert', $qb->expr()->andX( $qb->expr()->eq('master.clientcode', 'eeCert.clientcode'), $qb->expr()->eq('master.eecode', 'eeCert.eecode'), $qb->expr()->eq('master.id', 'eeCert.ee_cert_master_id') ) ) ->leftJoin( 'eeCert', 'lms_cm_ee_certification', 'eeCert2', $qb->expr()->andX( $qb->expr()->eq('eeCert2.clientcode', 'eeCert.clientcode'), $qb->expr()->eq('eeCert2.eecode', 'eeCert.eecode'), $qb->expr()->eq('eeCert2.ee_cert_master_id', 'eeCert.ee_cert_master_id'), $qb->expr()->gt('eeCert2.added_on', 'eeCert.added_on') ) ) ->leftJoin( 'master', 'lms_cm_approvers', 'approvers', $qb->expr()->andX( $qb->expr()->eq('master.clientcode', 'approvers.clientcode'), $qb->expr()->eq('master.cert_id', 'approvers.cert_id'), $qb->expr()->eq('master.approver_master_id', 'approvers.approver_master_id'), $qb->expr()->eq('master.sequence', 'approvers.sequence') ) ) ->leftJoin( 'approvers', 'on_clusers', 'user', $qb->expr()->andX( $qb->expr()->eq('user.clientcode', 'approvers.clientcode'), $qb->expr()->eq('user.username', 'approvers.approver') ) ) ->leftJoin( 'master', 'empchild', 'emp', $qb->expr()->andX( $qb->expr()->eq('emp.clientcode', 'master.clientcode'), $qb->expr()->eq('emp.eecode', 'master.eecode') ) ) ->leftJoin( 'emp', 'on_clusers', 'sup_user', $qb->expr()->andX( $qb->expr()->eq('sup_user.clientcode', 'emp.clientcode'), $qb->expr()->eq('sup_user.eecode', 'emp.supervsr1') ) ) ->where($qb->expr()->eq('master.clientcode', $qb->createPositionalParameter($clientCode))) ->andWhere($qb->expr()->isNull('eeCert2.id')) ->andWhere($qb->expr()->eq('master.is_deleted', 0)); if ($this->featuresService->isEeCertArchiveRemoveEditMVPFFEnabled($clientCode)) { if(!$showArchivedEeCert) { $qb->andWhere($qb->expr()->eq('master.is_archived', 0)); $qb->andWhere($qb->expr()->eq('cert.is_archived', 0)); } } $qb->andWhere($qb->expr()->eq('eeCert.is_deleted', 0)); $qb->andWhere($qb->expr()->eq('cert.is_deleted', 0)); if (!empty($eeCodes)) { $qb->andWhere($qb->expr()->in( 'eeCert.eecode', $this->connection->batchQuote($eeCodes) )); } $rows = $qb->execute()->fetchAll(); $availableApprovers = $this->certificationApprovalWorkflowRepo->getActiveUsersWithApproveDenyPermission($clientCode); $certDataIndexedByEeCode = []; foreach ($rows as $row) { $eeCode = $row['eeCode']; if (!isset($certDataIndexedByEeCode[$eeCode])) { $certDataIndexedByEeCode[$eeCode] = [ 'eeCode' => $eeCode, 'approvedCerts' => 0, 'waitOnVerifyCerts' => 0, 'isApproverUnavailable' => false ]; } $status = (int)$row['verificationStatus']; if ($status === 1) { $certDataIndexedByEeCode[$eeCode]['approvedCerts']++; } elseif ($status === 0) { $certDataIndexedByEeCode[$eeCode]['waitOnVerifyCerts']++; $approverUsername = $row['approverType'] === CertificationSetUp::APPROVER_TYPE_PRIMARY_SUPERVISOR ? $row['supervisorUsername'] : $row['approverUserName']; $approverStatus = $row['approverType'] === CertificationSetUp::APPROVER_TYPE_PRIMARY_SUPERVISOR ? $row['supervisorStatus'] : $row['approverStatus']; if (empty($approverUsername) || $approverStatus !== 'A' || !in_array($approverUsername, $availableApprovers, true)) { $certDataIndexedByEeCode[$eeCode]['isApproverUnavailable'] = true; } } } foreach ($eeCodes as $eeCode) { if (!isset($certDataIndexedByEeCode[$eeCode])) { $certDataIndexedByEeCode[$eeCode] = [ 'approvedCerts' => 0, 'waitOnVerifyCerts' => 0, 'eeCode' => $eeCode, 'isApproverUnavailable' => false ]; } } return $certDataIndexedByEeCode; }