public function importExternalTable($nameFile = null)
{
if (is_null($nameFile)) {
throw new Exception('Nome do arquivo ausente.');
}
$saStatement = new \Telephony\Models\SaStatement();
$sqlLocation = "ALTER TABLE SA_EXTRATO LOCATION (ETL: 'FAT{$nameFile}.TXT')";
$location = new Resultset(null, $saStatement, $saStatement->getReadConnection()->query($sqlLocation));
if (!$location) {
throw new Exception('Erro ao alterar location da tabela EXTRATO.');
}
$statement = new \Telephony\Models\Statement();
$sqlDelete = 'DELETE FROM EXTRATO WHERE MESREF IN (SELECT DISTINCT MESREF FROM SA_EXTRATO)';
$delete = new Resultset(null, $statement, $statement->getReadConnection()->query($sqlDelete));
if (!$delete) {
throw new Exception('Erro ao deletar tabela EXTRATO.');
}
$sqlInsert = 'INSERT INTO EXTRATO (
SELECT NUMNF, NUMIDCLI, MESREF, NUMACS, PLANO, NOME, TPSERV, DATA, HORA, ORIGEM, DESTINO,
NUMCHAM, TIPO, DURACAO,
TO_NUMBER(REPLACE(REPLACE(VALOR, \'.\', \'\'), \',\', \'.\'), \'999999999999.99\'),
OPERLD, TIMID, ALIQUOTA, CODFEBRABAN, TOTALGERAL
FROM SA_EXTRATO)';
$insert = new Resultset(null, $statement, $statement->getReadConnection()->query($sqlInsert));
if (!$insert) {
throw new Exception('Erro ao inserir na tabela EXTRATO.');
}
$saStatement = new \Telephony\Models\SaStatement();
$sqlCount = 'SELECT COUNT(1) M FROM SA_EXTRATO';
$count = new Resultset(null, $saStatement, $saStatement->getReadConnection()->query($sqlCount));
if (!$count) {
throw new Exception('Erro ao contar número de alterações da tabela EXTRATO pela SA_EXTRATO.');
}
return $count->toArray(0)[0]['M'];
}
private function getAnswer($userId, $limit, $offset)
{
$sql = <<<SQL
SELECT
\tu.user_id AS user_id,
\tu.user_nickname AS user_nickname,
\tu.user_cover AS user_cover,
\tquestion.question_id AS question_id,
\tquestion.question_content AS question_content,
\tquestion.question_pics AS question_pics,
\tquestion.answer_num AS answer_num,
\tquestion.question_browsers AS browser_num,
\tquestion.question_follows AS follow_num,
\tquestion.question_addtime AS addtime
FROM question_answer AS qa
LEFT JOIN question ON question.question_id = qa.question_id
LEFT JOIN user_base AS u ON question.user_id = u.user_id
WHERE question.question_state > 0 AND qa.user_id = :uid
GROUP BY question.question_id
ORDER BY question_id DESC
LIMIT {$limit} OFFSET {$offset}
SQL;
$model = new QuestionAnswer();
$result = new ResultSet(null, $model, $model->getReadConnection()->query($sql, ['uid' => $userId]));
return $result->toArray();
}
/**
* Format content for DataTable plugin
* @param \Phalcon\Mvc\Model\Resultset\Simple $query
* @param array $params
* @return array
*/
public static function toDataTable(\Phalcon\Mvc\Model\Resultset\Simple $query, array $params = array())
{
$result = ['draw' => $params['sEcho'], 'recordsFiltered' => $params['iTotal'], 'recordsTotal' => $query->count(), 'data' => []];
foreach ($query->toArray() as $aRow) {
$row = [];
foreach ($params['aColumns'] as $col) {
$row[] = $aRow[substr($col, 2)];
}
// pre define first row as empty for checkboxes
$result['data'][] = array_merge([null], $row);
}
return $result;
}
public function last()
{
$sql = 'select oneproxy_seq_nextval("pd_info")';
$robot = new PdInfo();
$info = new Resultset(null, $robot, $robot->getReadConnection()->query($sql));
if ($info) {
$last = $info->toArray();
if (!empty($last) && is_array($last) && isset($last[0]['oneproxy_seq_nextval("pd_info")'])) {
$pidArr = explode(',', $last[0]['oneproxy_seq_nextval("pd_info")']);
if (is_array($pidArr) && isset($pidArr['0']) && !empty($pidArr['0'])) {
$pid = (int) $pidArr['0'];
if (is_int($pid) and $pid > 0) {
return $pid;
}
}
}
}
return 0;
}
