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 recover(Request $request, $sid) { $desde = $request->input('desde'); $hasta = $request->input('hasta'); if ($desde < 0) { $desde = 1; } $nLlamadasTot = DB::table('tokens_' . $sid)->count(); if ($hasta > $nLlamadasTot) { $hasta = $nLlamadasTot; } if ($desde > $hasta) { return Redirect::to('survey/' . $sid . '/dispatch')->with('status', 'El limite inferior no puede ser mayor que el limite superior.'); } log::info('[' . $sid . '] Recueperamos las llamadas de el token ' . $desde . ' hasta el ' . $hasta); $rellamadaColumn = $sid . 'X' . SettingsController::getAnwswerGroup($sid) . 'X' . SettingsController::getRellamadaAnswer($sid); $sqlTokensRecuperables = " select tid,token 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'" . " and tid between " . $desde . " and " . $hasta; $tokensRecuperables = DB::select($sqlTokensRecuperables); foreach ($tokensRecuperables as $tokenRecuperable) { //Recuperamos cada llamada LlamadasController::recover($sid, $tokenRecuperable->tid); } return Redirect::to('survey/' . $sid)->with('status', 'Proceso de recuperacion de llamadas, realizado correctamente'); }
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; }