/**
* 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;
}
/**
* 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;
}
public static function needToAnswer($userId, $questionnaireId, $programaId)
{
$dbTable_Questionnaire = new DbTable_Questionnaire();
$questionnaireToAnswer = $dbTable_Questionnaire->needToFinish($questionnaireId, $userId, $programaId)->toArray();
$countQuestionnaireToAnswer = count($questionnaireToAnswer);
$block = array();
for ($index = 0; $index < $countQuestionnaireToAnswer; $index++) {
$block[$questionnaireToAnswer[$index]['Block']][] = $questionnaireToAnswer[$index];
}
$controle = array();
foreach ($block as $key => $value) {
if (!isset($controle[$key])) {
$controle[$key]['bloco'] = $value[0]['Value'];
$controle[$key]['qtd'] = count($block[$key]);
}
}
$plural = $countQuestionnaireToAnswer == 1 ? 0 : 1;
$html = '<li><a href="' . Zend_Controller_Front::getInstance()->getBaseUrl() . '/questionnaire/respond/index/qstn/' . $questionnaireId . '"> <div> <span><span>Falta' . ($plural ? 'm' : '') . '</span>' . $countQuestionnaireToAnswer . '</span> </div> <div>quest' . ($plural ? 'ões' : 'ão') . ' para responder</div> </a></li>';
foreach ($controle as $key => $value) {
//$plural = ($value['qtd'] == 1)?'ão':'ões';
// $html .= "<li><div> <span>{$value['qtd']}</span> </div> <div>Bloco {$value['bloco']}</div></li>";
}
return $html;
}
/**
* @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;
}