public function index($sid)
{
$plugin_settings = DB::table('plugin_settings')->where('key', $sid)->first();
//Validamos si ya esta encuesta ya tenia registro
if ($plugin_settings == null) {
DB::table('plugin_settings')->insert([['plugin_id' => 8, 'model' => 'mkp_recall', 'model_id' => 1, 'key' => $sid]]);
}
$isConfirmation = DB::table('plugin_settings')->where('key', $sid)->select('isConfirmation')->first();
//Primero valida si existe la tabla de survey y la de tokens
$sql = 'select count(1) as existe' . ' from information_schema.tables' . ' where TABLE_NAME in ("survey_' . $sid . '","tokens_' . $sid . '")' . ' and table_schema =database()';
$check = DB::select($sql)[0]->existe;
if ($check != '2') {
if ($check == '0') {
return Redirect()->route('admin')->with('status', 'La encuesta en cuestión aun no ha sido creada.');
} else {
if ($check == '1') {
return Redirect()->route('admin')->with('status', 'Lista de clientes aún no ha sido cargada.');
}
}
}
$surveys_languagesettings = DB::table('surveys_languagesettings')->where('surveyls_survey_id', $sid)->first();
$llamadasPdtesNoRealizadas = DB::table('tokens_' . $sid)->where('completed', 'N')->where('usesleft', '1')->count();
$llamadasPdtesRecuperadas = DB::table('tokens_' . $sid)->where('completed', 'N')->where('usesleft', '<>', '1')->count();
//Dependiendo de si es o no confirmacion hacemos una u otra consulta
if ($isConfirmation->isConfirmation == 0) {
//Encuestas de primera ronda
$rellamadaColumn = $sid . 'X' . SettingsController::getAnwswerGroup($sid) . 'X' . SettingsController::getRellamadaAnswer($sid);
$sqlLlamadasEmitidasRecuperables = "select count(1) as num from tokens_" . $sid . " where token in" . " ( " . "\tselect maxTable.token " . "\tfrom ( select token,max(id) as maxid from survey_" . $sid . " group by token) as maxTable " . "\tinner join survey_" . $sid . " as sv on maxTable.token=sv.token and maxTable.maxId = sv.id " . "\twhere `" . $rellamadaColumn . "`='A1' " . " ) and tokens_" . $sid . ".completed<>'N'";
$llamadasEmitidasRecuperables = DB::select($sqlLlamadasEmitidasRecuperables);
$sqlLlamadasEmitidasNoRecuperables = "select count(1) as num from tokens_" . $sid . " where token in" . " ( " . "\tselect maxTable.token " . "\tfrom ( select token,max(id) as maxid from survey_" . $sid . " group by token) as maxTable " . "\tinner join survey_" . $sid . " as sv on maxTable.token=sv.token and maxTable.maxId = sv.id " . "\twhere `" . $rellamadaColumn . "`='A2' " . " ) and tokens_" . $sid . ".completed<>'N'";
$llamadasEmitidasNORecuperables = DB::select($sqlLlamadasEmitidasNoRecuperables);
} else {
//Encuestas de confirmacion
$contactColumn = $sid . 'X' . SettingsController::getAnwswerGroup($sid) . 'X' . SettingsController::getHaConstactadoAnswer($sid);
$sqlLlamadasEmitidasRecuperables = "select count(1) as num from tokens_" . $sid . " where token in" . " ( " . "\tselect maxTable.token " . "\tfrom ( select token,max(id) as maxid from survey_" . $sid . " group by token) as maxTable " . "\tinner join survey_" . $sid . " as sv on maxTable.token=sv.token and maxTable.maxId = sv.id " . "\twhere `" . $contactColumn . "`='N' " . " ) and tokens_" . $sid . ".completed<>'N'";
$llamadasEmitidasRecuperables = DB::select($sqlLlamadasEmitidasRecuperables);
$sqlLlamadasEmitidasNoRecuperables = "select count(1) as num from tokens_" . $sid . " where token in" . " ( " . "\tselect maxTable.token " . "\tfrom ( select token,max(id) as maxid from survey_" . $sid . " group by token) as maxTable " . "\tinner join survey_" . $sid . " as sv on maxTable.token=sv.token and maxTable.maxId = sv.id " . "\twhere `" . $contactColumn . "`='Y' " . " ) and tokens_" . $sid . ".completed<>'N'";
$llamadasEmitidasNORecuperables = DB::select($sqlLlamadasEmitidasNoRecuperables);
}
$sqlOperadores = "\tSELECT uOp.name as name, " . "\t(select count(1) from tokens_" . $sid . " tk2 where tk2.attribute_1=tok1.operador and completed='N' and usesleft=1) as ptesNuncaRealizadas, " . "\t(select count(1) from tokens_" . $sid . " tk2 where tk2.attribute_1=tok1.operador and completed='N' and usesleft<>1) as ptesRecuperadas, " . " (select count(1) from tokens_" . $sid . " tk2 where tk2.attribute_1=tok1.operador and completed<> 'N' ) as ejecutadas " . "\tFROM " . "\t(select distinct(attribute_1) as operador from tokens_" . $sid . " where attribute_1 is not null group by attribute_1) as tok1 " . "\tleft join usuarios_operadores uOp on tok1.operador=uOp.id " . "\torder by uOp.`order` ";
//var_dump($sqlOperadores);
$llamadasPorOperadores = DB::select($sqlOperadores);
$data = array();
$data['survey_title'] = $surveys_languagesettings->surveyls_title;
$data['sid'] = $sid;
$data['isConfirmation'] = $isConfirmation->isConfirmation;
$data['LlamPdtesNoRealizadas'] = $llamadasPdtesNoRealizadas;
$data['LlamPdtesRecuperadas'] = $llamadasPdtesRecuperadas;
$data['LlamEmitidasRecuperables'] = $llamadasEmitidasRecuperables[0]->num;
$data['LlamEmitidasNoRecuperables'] = $llamadasEmitidasNORecuperables[0]->num;
$data['llamadasPorOperadores'] = $llamadasPorOperadores;
return view('admin/survey', ['data' => $data]);
}
public function getRecallConfig($sid)
{
$recallFieldData = array();
$gid = SettingsController::getAnwswerGroup($sid);
$tablePrefix = $sid . 'X' . $gid . 'X';
$recallFieldData['anws_qid'] = SettingsController::getMotivoRellamarAnswer($sid);
$recallFieldData['contact'] = $tablePrefix . SettingsController::getHaConstactadoAnswer($sid);
$recallFieldData['motiv'] = $tablePrefix . SettingsController::getRellamadaAnswer($sid);
$recallFieldData['anws_code'] = $tablePrefix . SettingsController::getMotivoRellamarAnswer($sid);
return $recallFieldData;
}