/** * lista questoes e criterios de um bloco de questionario * * @param type $blocoId * @return type */ public function getAllQuestionsByBlockIdAndCriterionsForView($blocoId) { $questionnaireId = $this->getId(); $questions = DbTable_Questionnaire::getInstance()->getBlockAndCriterionsById($questionnaireId, $blocoId); //echo "<br><br>"; //var_dump('questions',$questions); //$questions = DbTable_Questionnaire::getInstance()->getAllBlocksCriterionsById($this->getId()); $lastBlockId = $lastCriterionId = $lastQuestionId = null; $blocks = array(); foreach ($questions as $question) { $blockId = $question->getBlockId(); $criterionId = $question->getCriterionId(); $questionId = $question->getQuestionId(); if ($lastBlockId != $blockId) { $blocks[$blockId] = array('BlockValue' => $question->getBlockValue(), 'Criterions' => array()); } if ($lastCriterionId != $criterionId) { $blocks[$blockId]['Criterions'][$criterionId] = array('CriterionValue' => $question->getCriterionValue(), 'Questions' => array()); } if ($lastQuestionId != $questionId) { $blocks[$blockId]['Criterions'][$criterionId]['Questions'][$questionId] = array('QuestionId' => $questionId); } $lastCriterionId = $criterionId; $lastBlockId = $blockId; $lastQuestionId = $questionId; } return $blocks; }
/** * Regra para definir elegibilidade do diagnostico para autoavaliacao * * @param type $questionnaireId * @param type $userId * @return boolean */ public function doAutoavaliacaoEligibility($questionnaireId, $userId) { //$blockDb = DbTable_Block::getInstance(); $objQuestionnaire = DbTable_Questionnaire::getInstance(); //dados da empresa $enterpriseRow = DbTable_Enterprise::getInstance()->getEnterpriseByUserId($userId); //id $enterpriseId = $enterpriseRow->getId(); //email $enterpriseEmail = $enterpriseRow->getEmailDefault(); //tipo do questionario $questionnaireType = $objQuestionnaire->getQuestionnaireById($questionnaireId)->getDevolutiveCalcId(); //recupera blocos do questionario $blocks = $objQuestionnaire->getBlocks($questionnaireId); $atLegislacaoBlock = $blocks->current()->getId(); if ($questionnaireType == 1 && $atLegislacaoBlock) { // Score para o bloco de Atendimento a Legislação - Questionario de Diagnóstico $score = $objQuestionnaire->makeScore($questionnaireId, $userId, $atLegislacaoBlock); // Elegibilidade para o Questionário de Autoavaliação //$eligibility = ($score >= $this->score)? 1 : 0; $eligibility = 1; // Grava a elegibilidade na Enterprise, tabela EligibilityHistory $this->setAutoavaliacaoEligibility($enterpriseId, $eligibility); // Envia o E-mail para a empresa $this->sendDiagnosticoFeedback($eligibility, $enterpriseEmail); } return true; }
/** * @REFATORAR * * @param type $blockId * @return type * @throws Exception */ public function getArrayQuestionsAndAlternatives($questionnaireId, $userId, $blockId = null) { try { $arrDevolutiveRAA = array(); $arrCriteria = array(); $arrBlocks = array(); $arrQuestionnaire = array(); $arrRadarData = array(); // Definições do Questionário $questionnaireDefs = $this->questionnaire->getQuestionnaireById($questionnaireId); $arrQuestionnaire['title'] = $questionnaireDefs->getTitle(); $arrQuestionnaire['description'] = $questionnaireDefs->getDescription(); $arrQuestionnaire['long_description'] = $questionnaireDefs->getLongDescription(); $arrQuestionnaire['operation_beginning'] = Vtx_Util_Date::format_dma($questionnaireDefs->getOperationBeginning()); $arrQuestionnaire['operation_ending'] = Vtx_Util_Date::format_dma($questionnaireDefs->getOperationEnding()); // Recupera Dados do Questionario, Bloco e cada um dos enunciados das questoes vinculadas ao Bloco do questionario $questionsDefs = $this->question->getAllByQuestionnaireIdBlockId($questionnaireId, $blockId); //loop em cada QuestionId do QuestionarioId foreach ($questionsDefs as $question_def) { $idBlock = ""; $idCriterion = ""; $questionId = $question_def->getId(); $question_value = $question_def->getQuestao(); //recupera dados da QuestaoId // Grava a questão no array de devolutiva $arrDevolutiveRAA[$questionId]['designation'] = $question_def->getDesignacao(); $arrDevolutiveRAA[$questionId]['value'] = $question_value; $arrDevolutiveRAA[$questionId]['text'] = $question_def->getTexto(); // Verifica se existe Bloco válido e grava nos arrays de blocos e devolutiva $idBlock = $question_def->getBloco(); if ($idBlock != "" && $idBlock != 0) { $arrBlocks[$idBlock] = $question_def->getBlocoTitulo(); $arrDevolutiveRAA[$questionId]['block'] = $question_def->getBloco(); } // Verifica se existe Critério válido e grava nos arrays de critérios e devolutiva $idCriterion = $question_def->getCriterio(); if ($idCriterion != "" && $idCriterion != 0) { $arrCriteria[$idCriterion] = $question_def->getCriterioTitulo(); $arrDevolutiveRAA[$questionId]['criterion'] = $question_def->getCriterio(); } // Verifica se um determinada Empresa respondeu uma questao $isAnswered = $this->question->isAnsweredByEnterprise($questionId, $userId); //Se empresa respondeu a questaoId verificada, entao entra no condicional abaixo if ($isAnswered['status']) { // Recupera a resposta escrita $answer = $this->question->getQuestionAnswer($questionId, $userId); $alternative_id = $answer['alternative_id']; $arrDevolutiveRAA[$questionId]['alternative_id'] = $alternative_id; $arrDevolutiveRAA[$questionId]['write_answer'] = isset($answer['answer_value']) ? $answer['answer_value'] : ""; if (count($answer['annual_result']) > 0) { $arrDevolutiveRAA[$questionId]['annual_result'] = $answer['annual_result']; $arrDevolutiveRAA[$questionId]['annual_result_unit'] = $answer['annual_result_unit']; } else { $arrDevolutiveRAA[$questionId]['annual_result'] = ""; $arrDevolutiveRAA[$questionId]['annual_result_unit'] = ""; } // Recupera o feedback da alternativa escolhida $alternative = $this->alternative->getAlternativeById($alternative_id); $arrDevolutiveRAA[$questionId]['alternative_designation'] = $alternative->getDesignation(); $arrDevolutiveRAA[$questionId]['alternative_feedback'] = $alternative->getFeedbackDefault(); // Recupera o 'Pontos Fortes' do avaliador da resolução da questão $arrDevolutiveRAA[$questionId]['answer_feedback'] = $this->question->getAnswerFeedback($isAnswered['objAnswered']->getAnswerId()); // Recupera o 'Oportunidades de melhoria' do avaliador da resolução da questão $arrDevolutiveRAA[$questionId]['answer_feedback_improve'] = $this->question->getAnswerFeedbackImprove($isAnswered['objAnswered']->getAnswerId()); } // Recupera os dados das alternativas da QuestionId $alternativesDefs = $this->alternative->getAllByQuestionId($questionId, false, 'object'); foreach ($alternativesDefs as $alternative_def) { $arr_alternative[$alternative_def->getDesignation()] = $alternative_def->getValue(); } $arrDevolutiveRAA[$questionId]['alternatives'] = $arr_alternative; } return array($arrDevolutiveRAA, $arrBlocks, $arrCriteria, $arrQuestionnaire); } catch (Vtx_UserException $e) { DbTable_Questionnaire::getInstance()->getAdapter()->rollBack(); return array('status' => false, 'messageError' => $e->getMessage()); } catch (Exception $e) { DbTable_Questionnaire::getInstance()->getAdapter()->rollBack(); throw new Exception($e); } }
public function getArrayDevolutiveRAA($questionnaireId, $userId, $blockId = null) { try { $arrDevolutiveRAA = array(); $arrCriteria = array(); $arrBlocks = array(); $arrQuestionnaire = array(); $arrRadarData = array(); // Definições do Questionário $questionnaireDefs = $this->Questionnaire->getQuestionnaireById($questionnaireId); $arrQuestionnaire['title'] = $questionnaireDefs->getTitle(); $arrQuestionnaire['description'] = $questionnaireDefs->getDescription(); $arrQuestionnaire['long_description'] = $questionnaireDefs->getLongDescription(); $arrQuestionnaire['operation_beginning'] = Vtx_Util_Date::format_dma($questionnaireDefs->getOperationBeginning()); $arrQuestionnaire['operation_ending'] = Vtx_Util_Date::format_dma($questionnaireDefs->getOperationEnding()); // Definições da Questão $questionsDefs = $this->Question->getAllByQuestionnaireIdBlockId($questionnaireId, $blockId); $this->Block = new Model_Block(); foreach ($questionsDefs as $question_def) { $idBlock = ""; $idCriterion = ""; $questionId = $question_def->getId(); $question_value = $question_def->getQuestao(); $perguntas = $this->Block->cacheOrModelBlockById(63); // Grava a questão no array de devolutiva $arrDevolutiveRAA[$questionId]['designation'] = $question_def->getDesignacao(); $arrDevolutiveRAA[$questionId]['value'] = $question_value; $arrDevolutiveRAA[$questionId]['text'] = $question_def->getTexto(); $arrDevolutiveRAA[$questionId]['pergunta'] = $perguntas[$questionId]["QuestionSummary"]; // Verifica se existe Bloco válido e grava nos arrays de blocos e devolutiva $idBlock = $question_def->getBloco(); if ($idBlock != "" && $idBlock != 0) { $arrBlocks[$idBlock] = $question_def->getBlocoTitulo(); $arrDevolutiveRAA[$questionId]['block'] = $question_def->getBloco(); } // Verifica se existe Critério válido e grava nos arrays de critérios e devolutiva $idCriterion = $question_def->getCriterio(); if ($idCriterion != "" && $idCriterion != 0) { $arrCriteria[$idCriterion] = $question_def->getCriterioTitulo(); $arrDevolutiveRAA[$questionId]['criterion'] = $question_def->getCriterio(); } $isAnswered = $this->Question->isAnsweredByEnterprise($questionId, $userId); $isAnsweredVerificador = $this->Question->isAnsweredByVerificador($questionId, $this->userAuth->getUserId(), $userId); //var_dump(array_key_exists("objAnswered",$isAnsweredVerificador));exit; //var_dump($userId); /// print_r($this->Questionnaire->getQuestionsAnsweredByUserId($questionnaireId,43005, $blockId)); //exit; if ($isAnswered['status']) { // Recupera a resposta escrita $answer = $this->Question->getQuestionAnswer($questionId, $userId); if (!array_key_exists("objAnswered", $isAnsweredVerificador)) { $alternative_id = $answer['alternative_id']; $anwser_value = isset($answer['answer_value']) ? $answer['answer_value'] : ""; } else { $alternative_id = $isAnsweredVerificador["objAnswered"]['AlternativeId']; $anwser_value = isset($isAnsweredVerificador["objAnswered"]['AnswerValue']) ? $isAnsweredVerificador["objAnswered"]['AnswerValue'] : ""; } $arrDevolutiveRAA[$questionId]['alternative_id'] = $alternative_id; $arrDevolutiveRAA[$questionId]['write_answer'] = $anwser_value; if (count($answer['annual_result']) > 0) { $arrDevolutiveRAA[$questionId]['annual_result'] = $answer['annual_result']; $arrDevolutiveRAA[$questionId]['annual_result_unit'] = $answer['annual_result_unit']; } else { $arrDevolutiveRAA[$questionId]['annual_result'] = ""; $arrDevolutiveRAA[$questionId]['annual_result_unit'] = ""; } // Recupera o feedback da alternativa escolhida $alternative = $this->Alternative->getAlternativeById($alternative_id); $arrDevolutiveRAA[$questionId]['alternative_designation'] = $alternative->getDesignation(); $arrDevolutiveRAA[$questionId]['alternative_feedback'] = $alternative->getFeedbackDefault(); // Recupera o 'Pontos Fortes' do avaliador da resolução da questão $arrDevolutiveRAA[$questionId]['answer_feedback'] = $this->Question->getAnswerFeedback($isAnswered['objAnswered']->getAnswerId()); // Recupera o 'Oportunidades de melhoria' do avaliador da resolução da questão $arrDevolutiveRAA[$questionId]['answer_feedback_improve'] = $this->Question->getAnswerFeedbackImprove($isAnswered['objAnswered']->getAnswerId()); } // Recupera as alternativas da questão $alternativesDefs = $this->Alternative->getAllByQuestionId($questionId); try { foreach ($alternativesDefs as $alternative_def) { $arr_alternative[$alternative_def['Designation']] = $alternative_def; $arrDevolutiveRAA[$questionId]['alternatives'] = $arr_alternative; } } catch (Excception $e) { } } return array($arrDevolutiveRAA, $arrBlocks, $arrCriteria, $arrQuestionnaire); } catch (Vtx_UserException $e) { DbTable_Questionnaire::getInstance()->getAdapter()->rollBack(); return array('status' => false, 'messageError' => $e->getMessage()); } catch (Exception $e) { DbTable_Questionnaire::getInstance()->getAdapter()->rollBack(); throw new Exception($e); } exit; }
public function getAll($valuesAddress, $colAddress, $questionnaireId = null, $fetch = 'all', $filter = null, $orderBy = null, $format = null, $tipoRelatorio = 'inscricoes', $groupBy = null) { $configDb = Zend_Registry::get('configDb'); $filter = $this->setDefaultFiltersValue($filter); $competitionId = (isset($filter['competition_id']) and $filter['competition_id']) ? $filter['competition_id'] : $configDb->competitionId; /* $currentAutoavaliacaoId = ($configDb->qstn->currentAutoavaliacaoId)? $configDb->qstn->currentAutoavaliacaoId:'"null"'; */ $currentAutoavaliacaoId = $questionnaireId = DbTable_Questionnaire::getInstance()->getQuestionnaireIdByCompetitionId($competitionId); /* * pegar blockIdEmpreendedoris pelo competitionId. * encapsular */ switch ($competitionId) { case 2014: case 2012: $currentBlockIdEmpreendedorismo = "null"; break; case 2013: $currentBlockIdEmpreendedorismo = $configDb->qstn->currentBlockIdEmpreendedorismo ? $configDb->qstn->currentBlockIdEmpreendedorismo : '"null"'; break; default: $currentBlockIdEmpreendedorismo = $configDb->qstn->currentBlockIdEmpreendedorismo ? $configDb->qstn->currentBlockIdEmpreendedorismo : '"null"'; break; } $incluirJoinPontuacao = (isset($filter['incluir_join_pontuacao']) and $filter['incluir_join_pontuacao'] == '1') ? true : false; $camposEmpresa = $incluirJoinPontuacao ? $this->camposEnterprise : $this->menosCamposEnterprise; $incluirJoinRegional = (isset($filter['incluir_join_regional']) and $filter['incluir_join_regional'] == '1') ? true : false; $camposEnderecoEmpresa = $format == 'csv' ? array('StreetNameFull', 'StreetNumber', 'StreetCompletion', 'Cep') : null; $query = $this->select()->distinct()->setIntegrityCheck(false)->from(array('E' => 'Enterprise'), $camposEmpresa)->join(array('USL' => 'UserLocality'), 'USL.EnterpriseId = E.Id', null)->join(array('P' => 'President'), 'P.EnterpriseId = E.Id', array('NomeCompletoUser' => new Zend_Db_Expr("P.Name"), 'CpfUser' => 'Cpf', 'TelefoneContato' => 'Phone', 'DataNascimentoUser' => 'BornDate', 'SexoUser' => 'Gender', 'CelularUser' => 'Cellphone', 'PositionId', 'EducationId', 'EmailUser' => 'Email', 'IdUser' => 'Id', 'PresidentCreated' => 'Created', 'FindUsId'))->joinleft(array('U2' => 'User'), 'U2.Id = USL.UserId', array('LoginUser' => 'Login'))->join(array('AE' => 'AddressEnterprise'), 'AE.EnterpriseId = E.Id', $camposEnderecoEmpresa)->joinleft(array('C' => 'City'), 'C.Id = AE.CityId', array('CityName' => 'Name'))->joinleft(array('S' => 'State'), 'S.Id = AE.StateId', array('Uf'))->joinleft(array('N' => 'Neighborhood'), 'N.Id = AE.NeighborhoodId', array('NeighName' => 'Name')); // $query->joinLeft(array('ECA' => 'EnterpriseCategoryAward'), 'ECA.Id = E.CategoryAwardId', array('DescriptionEca' => 'Description'))->join(array('ECAC' => 'EnterpriseCategoryAwardCompetition'), 'ECAC.EnterpriseId = E.Id AND ECAC.CompetitionId = ' . $competitionId, null)->joinleft(array('ER' => 'EnterpriseReport'), 'ER.EnterpriseId = E.Id AND ER.CompetitionId = ' . $competitionId, array('ReportId' => 'Id', 'ReportTitle' => 'Title'))->joinleft(array('EPR' => 'EnterpriseProgramaRank'), "EPR.EnterpriseIdKey = E.IdKey AND EPR.ProgramaId = {$competitionId}", array('Classificar', 'Desclassificar', 'Justificativa'))->joinleft(array('EXA' => 'Execution'), 'EXA.UserId = USL.UserId AND EXA.QuestionnaireId = ' . $currentAutoavaliacaoId . ' AND EXA.ProgramaId = ' . $competitionId, array('PA' => 'Progress'))->joinLeft(array('ECS' => 'EnterpriseCategorySector'), 'ECS.Id = E.CategorySectorId', array('DescriptionCategorySector' => 'Description')); $this->appendVerifiedJoin($query); //avaliadores $query = $this->_queryAvaliadores($query, $filter, $competitionId, $tipoRelatorio); if ($format == 'csv') { $query = $this->_queryCSV($query, $competitionId); } if ($incluirJoinPontuacao) { $query->joinleft(array('Pos' => 'Position'), 'Pos.Id = P.PositionId', array('CargoPresident' => 'Description'))->joinleft(array('Edu' => 'Education'), 'Edu.Id = P.EducationId', array('Education' => 'Description'))->joinleft(array('EP' => 'ExecutionPontuacao'), 'EP.ExecutionId = EXA.Id', array('NegociosTotal')); /* somente para 2013 traz PontosEmpreendedorismo **/ $queryBeg = FALSE; if ($competitionId == 2013) { $queryBeg = $this->getAdapter()->select()->from(array('BEG' => 'BlockEnterpreneurGrade'), new Zend_Db_Expr("AVG(Points) * 4")); $queryBeg->where("BEG.CompetitionId = (?)", $competitionId); $queryBeg->where('BEG.UserId = USL.UserId'); $queryBeg->where("BEG.QuestionnaireId = (?)", $currentAutoavaliacaoId); $queryBeg->where("BEG.BlockId = (?)", $currentBlockIdEmpreendedorismo); $query->columns(array('PontosEmpreendedorismo' => new Zend_Db_Expr("({$queryBeg})"))); } } // fim incluir join ############ if ($incluirJoinRegional) { /* $queryRegional = "(SELECT CASE WHEN ( SELECT Rcity.Description FROM `ServiceArea` AS `SAcity` JOIN `Regional` AS `Rcity` ON SAcity.RegionalId = Rcity.Id AND Rcity.National = 'N' WHERE SAcity.CityId = AE.CityId ) is not null THEN ( SELECT Rcity.Description FROM `ServiceArea` AS `SAcity` JOIN `Regional` AS `Rcity` ON SAcity.RegionalId = Rcity.Id AND Rcity.National = 'N' WHERE SAcity.CityId = AE.CityId ) WHEN ( SELECT Rstate.Description FROM `ServiceArea` AS `SAstate` JOIN `Regional` AS `Rstate` ON SAstate.RegionalId = Rstate.Id AND Rstate.National = 'N' AND Rstate.Estadual is not null WHERE SAstate.StateId = AE.StateId ) is not null THEN ( SELECT Rstate.Description FROM `ServiceArea` AS `SAstate` JOIN `Regional` AS `Rstate` ON SAstate.RegionalId = Rstate.Id AND Rstate.National = 'N' AND Rstate.Estadual is not null WHERE SAstate.StateId = AE.StateId ) ELSE '' END)"; $query->columns(array('Regional' =>new Zend_Db_Expr($queryRegional))); */ $query = $this->_queryGetRegionalByAddressEnterprise($query); } if ($questionnaireId) { $query->joinLeft(array('EXE' => 'Execution'), 'USL.UserId = EXE.UserId AND EXE.QuestionnaireId = ' . $questionnaireId . ' AND EXE.ProgramaId = ' . $competitionId, array('DevolutivePath', 'EvaluationPath', 'FinalScore')); } if (isset($filter['regional_id']) and $filter['regional_id']) { $regionalId = $filter['regional_id']; $query->join(array('SA' => 'ServiceArea'), "SA.RegionalId = {$regionalId} AND (\n SA.StateId = AE.StateId\n OR SA.CityId = AE.CityId\n OR SA.NeighborhoodId = AE.NeighborhoodId)", null); } if ($colAddress) { $query->where("AE.{$colAddress} in (?)", $valuesAddress); } switch ($tipoRelatorio) { case 'ranking': $filter['verified_subscription'] = 1; break; case 'report-categoria': case 'report-digitador': case 'report-regional-bairros': case 'report-regional-cidades': case 'report-regional-estados': $filter['devolutiva'] = null; break; } if (isset($filter['candidatura']) and $filter['candidatura']) { switch ($filter['candidatura']) { case 'C': //'candidatas' $filter['devolutiva'] = 2; break; case 'I': //'candidatas' $query->where('EXE.DevolutivePath is null'); break; // case 3: 'inscritas' break; } } if (isset($filter['devolutiva']) and $filter['devolutiva']) { switch ($filter['devolutiva']) { case 2: //'candidatas' $query->where('EXE.DevolutivePath is not null'); break; // case 3: 'inscritas' break; } } if (isset($filter['president_name']) and $filter['president_name']) { $query->where("P.Name LIKE (?) OR P.NickName LIKE (?)", '%' . $filter['president_name'] . '%'); } if (isset($filter['education_id']) and $filter['education_id']) { $query->where('P.EducationId = ?', $filter['education_id']); } if (isset($filter['category_award_id']) and $filter['category_award_id']) { $query->where('E.CategoryAwardId = ?', $filter['category_award_id']); } if (isset($filter['category_sector_id']) and $filter['category_sector_id']) { $query->where('E.CategorySectorId = ?', $filter['category_sector_id']); } if (isset($filter['annual_revenue']) and $filter['annual_revenue']) { $query->where('E.AnnualRevenue = (?)', $filter['annual_revenue']); } if ($tipoRelatorio != 'report-status-appraiser') { // nao usar esse where no relatorio de status da avaliacao if (isset($filter['appraiser_id']) and $filter['appraiser_id']) { $query->where('ApE.UserId = (?) or ApESec.UserId = (?) or ApETer.UserId = (?)', $filter['appraiser_id']); } } if (isset($filter['employees_quantity']) and $filter['employees_quantity']) { $eq = trim($filter['employees_quantity']); $query->where('E.EmployeesQuantity = (?)', $eq); } if (isset($filter['cpf']) and $filter['cpf']) { $query->where('P.Cpf LIKE "%' . preg_replace('/[^0-9]/', '', $filter['cpf']) . '%"'); } if (isset($filter['metier_id']) and $filter['metier_id']) { $query->where('E.MetierId = (?)', $filter['metier_id']); } if (isset($filter['status']) and $filter['status']) { $query->where('E.Status = (?)', $filter['status']); } if (isset($filter['coop_name']) and $filter['coop_name']) { $query->where("E.SocialName LIKE (?) OR E.FantasyName LIKE (?)", '%' . $filter['coop_name'] . '%'); } if (isset($filter['cnpj']) and $filter['cnpj']) { $query->where('E.Cnpj LIKE "%' . preg_replace('/[^0-9]/', '', $filter['cnpj']) . '%"'); } if (isset($filter['faixa']) and $filter['faixa']) { $faixa = Vtx_Util_Array::faixaIdadePSMN($filter['faixa']); $query->where(new Zend_Db_Expr("FLOOR(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(P.Borndate,'%Y')) BETWEEN " . preg_replace('/[^0-9]/', '', $faixa[1]) . ' AND ' . preg_replace('/[^0-9]/', '', $faixa[2]))); } if (isset($filter['state_id']) and $filter['state_id']) { $query->where("AE.StateId = (?)", $filter['state_id']); } if (isset($filter['city_id']) and $filter['city_id']) { $query->where("AE.CityId = (?)", $filter['city_id']); } if (isset($filter['neighborhood_id']) and $filter['neighborhood_id']) { $query->where("AE.NeighborhoodId = (?)", $filter['neighborhood_id']); } switch ($tipoRelatorio) { case 'report-global-respostas': $query = $this->_queryReportGlobalRespostas($query, $filter, $competitionId); break; case 'report-global-criterios': $query = $this->_queryReportGlobalCriterios($query); break; case 'lista-avaliador': case 'lista-avaliador-nacional': $query = $this->_queryListaAvaliador($query, $questionnaireId, $competitionId); break; case 'report-regional-bairros': $query = $this->_queryReportRegional($query, 'bairros'); break; case 'report-regional-cidades': $query = $this->_queryReportRegional($query, 'cidades'); break; case 'report-regional-estados': $query = $this->_queryReportRegional($query, 'estados'); break; case 'report-categoria': $query = $this->_queryReportCategoria($query); break; case 'report-categoria-premio': $query = $this->_queryReportCategoriaPremio($query); break; case 'report-inscricoes': $query = $this->_queryReportInscricoes($query, $competitionId); break; case 'report-inscricoes-categoria': $query = $this->_queryReportInscricoesCategoria($query, $competitionId); break; case 'report-digitador': $query = $this->_queryReportDigitador($query, $competitionId); $orderBy = null; break; // case 'classificadas-nacional': // case 'classificadas-nacional': case 'classificadas': $query = $this->_queryClassificadas($query, $queryBeg, $filter, $competitionId); $orderBy = null; break; case 'classificadas-nacional': $query = $this->_queryClassificadasNacional($query, $queryBeg, $filter, $competitionId); $orderBy = null; break; case 'finalistas': $query = $this->_queryFinalistas($query, $queryBeg, $filter, $competitionId); $orderBy = null; break; case 'finalistas-nacional': $query = $this->_queryFinalistasNacional($query, $queryBeg, $filter, $competitionId); $orderBy = null; break; case 'report-status-appraiser': $query = $this->_queryReportStatusAppraiser($query, $competitionId, $filter); $orderBy = 'U.FirstName ASC'; break; case 'report-status-verificador': $query = $this->_queryReportStatusChecker($query, $competitionId); $orderBy = 'U.FirstName ASC'; break; case 'checker-list': //marianam //marianam case 'checker-list-nacional': $query = $this->_queryCheckerList($query, $questionnaireId, $filter, $competitionId); break; case 'candidatas-nacional': $query = $this->_queryCandidatasNacional($query, $queryBeg, $filter, $competitionId); break; } if ($groupBy) { $groupByString = $this->groupByStringFor($groupBy); if ($groupByString) { $query->group($groupByString); } } if ($orderBy) { $query->order($orderBy); } /* if ($orderBy == 'NegociosTotal DESC') { $query->order('PontosEmpreendedorismo DESC'); } */ // echo $query->__toString(); die; //echo $tipoRelatorio;echo $query;echo "< /br>"; $retorno = $this->fetch($query, $fetch); return $retorno; }
public function getArrayDevolutiveReturn($questionnaireId, $userId, $programaId, $blockId = null) { $this->Questionnaire = new Model_Questionnaire(); $this->Alternative = new Model_Alternative(); $this->Question = new Model_Question(); try { $arrDevolutiveRAA = array(); $arrCriteria = array(); $arrBlocks = array(); $arrQuestionnaire = array(); $arrRadarData = array(); // Definições do Questionário //@TODO - por em cache $questionnaireDefs = $this->Questionnaire->getQuestionnaireById($questionnaireId); $arrQuestionnaire['title'] = $questionnaireDefs->getTitle(); $arrQuestionnaire['description'] = $questionnaireDefs->getDescription(); $arrQuestionnaire['long_description'] = $questionnaireDefs->getLongDescription(); $arrQuestionnaire['operation_beginning'] = Vtx_Util_Date::format_dma($questionnaireDefs->getOperationBeginning()); $arrQuestionnaire['operation_ending'] = Vtx_Util_Date::format_dma($questionnaireDefs->getOperationEnding()); // Definições da Questão $questionsDefs = $this->Question->getAllByQuestionnaireIdBlockId($questionnaireId, $blockId); $cacheQuestion = new Vtx_Cache_MPE_QuestionarioCache(); /** * - metodo criado para refatorar 2 metodos que faziam a mesma coisa e estavam dentro do foreach * - metodo que retorna todas alternativas de todas as questoes de um bloco que um Usuario respondeu * - a utilizacao deste metodo reduziu media de 25% a qtd de queries chamadas pelo BD. * - no meu exemplo reduziu de 320 para 235. * - chamada: $isAnswered = $this->Question->setaQuestionResp($QuestionRespDbTable, $questionId); * @author esilva */ $QuestionRespDbTable = $this->Question->getAnswerByBlockId($blockId, $userId, $programaId); //var_dump ($QuestionRespDbTable); exit; /** * * Recupera dados de cada questao de um Bloco: * - enunciado questao * - alternativas * - resposta * - resultado anual * */ foreach ($questionsDefs as $question_def) { $idBlock = ""; $idCriterion = ""; $questionId = $question_def->getId(); $question_value = $question_def->getQuestao(); // Grava a questão no array de devolutiva $arrDevolutiveRAA[$questionId]['designation'] = $question_def->getDesignacao(); $arrDevolutiveRAA[$questionId]['value'] = $question_value; $arrDevolutiveRAA[$questionId]['text'] = $question_def->getTexto(); // Verifica se existe Bloco válido e grava nos arrays de blocos e devolutiva $idBlock = $question_def->getBloco(); if ($idBlock != "" && $idBlock != 0) { $arrBlocks[$idBlock] = $question_def->getBlocoTitulo(); $arrDevolutiveRAA[$questionId]['block'] = $question_def->getBloco(); } // Verifica se existe Critério válido e grava nos arrays de critérios e devolutiva $idCriterion = $question_def->getCriterio(); if ($idCriterion != "" && $idCriterion != 0) { $arrCriteria[$idCriterion] = $idCriterion . ". " . $question_def->getCriterioTitulo(); $arrDevolutiveRAA[$questionId]['criterion'] = $question_def->getCriterio(); } $isAnswered['answerResult'] = null; //se empresa respondeu a questao //$isAnswered = $this->Question->isAnsweredByEnterprise($questionId,$userId, true); $isAnswered = $this->Question->setaQuestionResp($QuestionRespDbTable, $questionId); //var_dump('isAnswered',$isAnswered); if ($isAnswered['status']) { // Recupera a resposta //$answer = $this->Question->getQuestionAnswer($questionId,$userId); //refatorado para otimizar queries executadas $answer = $isAnswered['answerResult']; $alternative_id = $answer['alternative_id']; $arrDevolutiveRAA[$questionId]['alternative_id'] = $alternative_id; $arrDevolutiveRAA[$questionId]['write_answer'] = isset($answer['answer_value']) ? $answer['answer_value'] : ""; if (count($answer['annual_result']) > 0) { $arrDevolutiveRAA[$questionId]['annual_result'] = $answer['annual_result']; $arrDevolutiveRAA[$questionId]['annual_result_unit'] = $answer['annual_result_unit']; } else { $arrDevolutiveRAA[$questionId]['annual_result'] = ""; $arrDevolutiveRAA[$questionId]['annual_result_unit'] = ""; } // Recupera o feedback da alternativa escolhida #$alternative = $this->Alternative->getAlternativeById($alternative_id); //recupera do cache $alternative = $cacheQuestion->alternative($alternative_id, $this->Alternative); $arrDevolutiveRAA[$questionId]['alternative_designation'] = $alternative->getDesignation(); $arrDevolutiveRAA[$questionId]['alternative_feedback'] = $alternative->getFeedbackDefault(); // Recupera os comentarios 1 do avaliador da resolução da questão //REFACTORING //$arrDevolutiveRAA[$questionId]['answer_feedback'] = $this->Question->getAnswerFeedback( $isAnswered['objAnswered']['AnswerIdValue'] );//$isAnswered['objAnswered']->getAnswerId()); $arrDevolutiveRAA[$questionId]['answer_feedback'] = $answer['AnswerFeedback']; // Recupera os comentarios 2 do avaliador da resolução da questão //REFACTORING //$arrDevolutiveRAA[$questionId]['answer_feedback_improve'] = $this->Question->getAnswerFeedbackImprove($isAnswered['objAnswered']['AnswerIdValue']); $arrDevolutiveRAA[$questionId]['answer_feedback_improve'] = $answer['AnswerFeedbackImprove']; //AdditionalInfo $arrDevolutiveRAA[$questionId]['additional_info'] = $answer['AdditionalInfo']; } // Recupera as alternativas da questão //$alternativesDefs = $this->Alternative->getAllByQuestionId($questionId); //recupera do cache $alternativesDefs = $cacheQuestion->alternativasEQuestoes($questionId, $this->Alternative); //var_dump ('alternativesDefs: ', $alternativesDefs); //echo "<br><Br>"; foreach ($alternativesDefs as $alternative_def) { if (is_object($alternative_def)) { $arr_alternative[$alternative_def->getDesignation()] = $alternative_def->getValue(); } else { $arr_alternative[$alternative_def['Designation']] = $alternative_def['Value']; } } $arrDevolutiveRAA[$questionId]['alternatives'] = $arr_alternative; } return array($arrDevolutiveRAA, $arrBlocks, $arrCriteria, $arrQuestionnaire); } catch (Vtx_UserException $e) { DbTable_Questionnaire::getInstance()->getAdapter()->rollBack(); return array('status' => false, 'messageError' => $e->getMessage()); } catch (Exception $e) { DbTable_Questionnaire::getInstance()->getAdapter()->rollBack(); throw new Exception($e); } }
public function setExecutionProgress($QstnId, $UserId) { $qtdTotal = DbTable_Questionnaire::getInstance()->getQuestionnaireTotalQuestions($QstnId)->getQtdTotal(); $qtdFaltante = count(DbTable_Questionnaire::getInstance()->needToFinish($QstnId, $UserId)->toArray()); $qtdRealizado = $qtdTotal - $qtdFaltante; $pctRealizado = round($qtdRealizado / $qtdTotal * 100, 0); $executionRow = DbTable_Execution::getInstance()->fetchRow(array('UserId = ?' => $UserId, 'QuestionnaireId = ?' => $QstnId)); if (!$executionRow) { $Execution = new Model_Execution(); $executionRow = $Execution->initExecution($QstnId, $UserId); } $executionRow->setProgress($pctRealizado); $executionRow->save(); return true; }