1168 lines
45 KiB
C++
1168 lines
45 KiB
C++
#include "widget.h"
|
|
#include <QSqlError>
|
|
#include <QSqlQuery>
|
|
#include <QSqlTableModel>
|
|
#include <QDebug>
|
|
#include <QHBoxLayout>
|
|
#include <QPushButton>
|
|
#include <QLabel>
|
|
#include <QCalendarWidget>
|
|
#include <QVBoxLayout>
|
|
#include <QSqlRecord>
|
|
#include <QFile>
|
|
#include <QStack>
|
|
#include <QFileDialog>
|
|
#include "stable.h"
|
|
QDate lastDayofMonth(QDate date)
|
|
{
|
|
QDate last = date.addMonths(1);
|
|
last.setDate(last.year(), last.month(), 1);
|
|
return last.addDays(-1);
|
|
}
|
|
|
|
QDate firstDayofMonth(QDate date)
|
|
{
|
|
QDate first = date;
|
|
first.setDate(first.year(), first.month(), 1);
|
|
return first;
|
|
}
|
|
|
|
QDate lastDayofWeek(QDate date)
|
|
{
|
|
QDate last = date.addDays(6);
|
|
if(last.month() > date.month())
|
|
last = lastDayofMonth(date);
|
|
return last;
|
|
}
|
|
|
|
QDate divideMonth(QDate date, int n)
|
|
{
|
|
QDate first = firstDayofMonth(date);
|
|
QDate last = lastDayofMonth(date);
|
|
int nRemain = first.daysTo(last) + 1;
|
|
int nDiv = nRemain / n;
|
|
int nMod = nRemain % n;
|
|
int nBefore = 1, nAfter = 1;
|
|
for(int i = 0; i < n; i++)
|
|
{
|
|
nAfter = nBefore + nDiv + ((nMod-- > 0)? 1 : 0) - 1;
|
|
if(nBefore <= date.day() && date.day() <= nAfter)
|
|
break;
|
|
nBefore = nAfter + 1;
|
|
}
|
|
return QDate(date.year(), date.month(), nAfter);
|
|
}
|
|
|
|
Widget::Widget(QWidget *parent)
|
|
: QWidget(parent)
|
|
{
|
|
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
|
|
db.setHostName("bigbird.iptime.org");
|
|
db.setUserName("admin");
|
|
db.setPassword("admin123");
|
|
db.setDatabaseName("concepters");
|
|
if (db.open() == false)
|
|
{
|
|
qDebug() << db.lastError().text();
|
|
return;
|
|
}
|
|
m_pmodelKeyword = new QSqlQueryModel;
|
|
m_pmodelGroup = new QSqlQueryModel;
|
|
m_pmodelCrawling = new QSqlQueryModel;
|
|
|
|
m_pmodelKeyword->setQuery("SELECT id,realtime,searches,start,end,authorship,CASE platform "
|
|
"WHEN 0 THEN 'Naver Cafe' "
|
|
"WHEN 1 THEN 'Naver Blog' "
|
|
"WHEN 2 THEN 'Daum Cafe' "
|
|
"WHEN 3 THEN 'Naver News' "
|
|
"WHEN 4 THEN 'Naver Cafe List' "
|
|
"WHEN 5 THEN 'Daum Cafe List' "
|
|
"WHEN 6 THEN 'Kakao Story Channel' "
|
|
"WHEN 7 THEN 'Kakao Story Tag' "
|
|
"WHEN 8 THEN 'Kakao Story User' "
|
|
"WHEN 9 THEN 'Instagram Tag' "
|
|
"WHEN 10 THEN 'Instagram User' "
|
|
"WHEN 11 THEN 'Facebook Tag' "
|
|
"WHEN 12 THEN 'Facebook User' "
|
|
"WHEN 13 THEN 'Naver Blog Accuracy' "
|
|
"WHEN 14 THEN 'Twitter Tag' "
|
|
"WHEN 15 THEN 'Twitter User' "
|
|
"WHEN 16 THEN 'Youtube Tag' "
|
|
"WHEN 17 THEN 'Youtube User' "
|
|
"ELSE 'UnKnown'"
|
|
"END AS platform FROM keyword where state is null");
|
|
m_pmodelGroup->setQuery("SELECT * FROM datagroup");
|
|
UpdateCrawling();
|
|
|
|
QVBoxLayout *vlayout = new QVBoxLayout();
|
|
{
|
|
QHBoxLayout *hlayout = new QHBoxLayout();
|
|
hlayout->addWidget(setKeywordWidgets());
|
|
hlayout->addWidget(setGroupWidgets());
|
|
vlayout->addLayout(hlayout);
|
|
vlayout->addWidget(setCrawlingWidgets());
|
|
}
|
|
setLayout(vlayout);
|
|
}
|
|
|
|
Widget::~Widget()
|
|
{
|
|
|
|
}
|
|
|
|
QGroupBox *Widget::setKeywordWidgets()
|
|
{
|
|
QVBoxLayout *vlayout = new QVBoxLayout();
|
|
{
|
|
m_ptableKeyword = new QTableView;
|
|
m_ptableKeyword->setModel(m_pmodelKeyword);
|
|
m_ptableKeyword->setSelectionBehavior(QAbstractItemView::SelectRows);
|
|
m_ptableKeyword->setSelectionMode(QAbstractItemView::ExtendedSelection);
|
|
vlayout->addWidget(m_ptableKeyword);
|
|
|
|
connect(m_ptableKeyword->selectionModel(), SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
|
|
this, SLOT(on_keyword_currentRowChanged(QModelIndex)));
|
|
|
|
}
|
|
{
|
|
m_pdeStart = new QDateEdit(QDate::currentDate());
|
|
m_pdeEnd = new QDateEdit(QDate::currentDate());
|
|
|
|
m_pdeStart->setDateRange(QDate(2003, 5, 20),QDate::currentDate());
|
|
m_pdeEnd->setDateRange(QDate(2003, 5, 20),QDate::currentDate().addYears(1));
|
|
|
|
QCalendarWidget *pCalender = new QCalendarWidget();
|
|
m_pdeStart->setCalendarWidget(pCalender);
|
|
m_pdeStart->setCalendarPopup(true);
|
|
|
|
m_pdeEnd->setCalendarWidget(pCalender);
|
|
m_pdeEnd->setCalendarPopup(true);
|
|
|
|
m_pcbRealTime = new QComboBox;
|
|
m_pcbRealTime->addItems(QStringList() << "false" << "true");
|
|
|
|
m_pcbPlatform = new QComboBox;
|
|
m_pcbPlatform->addItems(QStringList() << "Naver Cafe" << "Naver Blog" << "Daum Cafe" << "Naver News" << "Naver Cafe List" << "Daum Cafe List"
|
|
<< "Kakao Story Channel" << "Kakao Story Tag" << "Kakao Story User" << "Instagram Tag" << "Instagram User"
|
|
<< "Facebook Tag" << "Facebook User" << "Naver Blog Accuracy" << "Twitter Tag" << "Twitter User" << "Youtube Tag" << "Youtube User");
|
|
|
|
m_pleKeyword = new QLineEdit;
|
|
m_pleAuthorship = new QLineEdit;
|
|
|
|
m_pcbSplit = new QComboBox;
|
|
m_pcbSplit->addItems(QStringList() << "None" << "Monthly" << "Daily");
|
|
m_pleSplit = new QLineEdit;
|
|
m_pleSplit->setText("1");
|
|
}
|
|
{
|
|
QHBoxLayout *hlayout = new QHBoxLayout();
|
|
hlayout->setAlignment(Qt::AlignLeft|Qt::AlignTop);
|
|
hlayout->addWidget(new QLabel("Real Time:"));
|
|
hlayout->addWidget(m_pcbRealTime);
|
|
hlayout->addWidget(new QLabel("Platform:"));
|
|
hlayout->addWidget(m_pcbPlatform);
|
|
hlayout->addWidget(new QLabel("Start:"));
|
|
hlayout->addWidget(m_pdeStart);
|
|
hlayout->addWidget(new QLabel("End:"));
|
|
hlayout->addWidget(m_pdeEnd);
|
|
hlayout->addWidget(new QLabel("Split:"));
|
|
hlayout->addWidget(m_pcbSplit);
|
|
hlayout->addWidget(m_pleSplit);
|
|
hlayout->addWidget(new QLabel("Authorship:"));
|
|
hlayout->addWidget(m_pleAuthorship);
|
|
vlayout->addLayout(hlayout);
|
|
}
|
|
{
|
|
QHBoxLayout *hlayout = new QHBoxLayout();
|
|
hlayout->setAlignment(Qt::AlignLeft|Qt::AlignTop);
|
|
hlayout->addWidget(new QLabel("Keyword:"));
|
|
hlayout->addWidget(m_pleKeyword);
|
|
vlayout->addLayout(hlayout);
|
|
}
|
|
{
|
|
QHBoxLayout *hlayout = new QHBoxLayout();
|
|
QPushButton *pInsert = new QPushButton("Insert");
|
|
QPushButton *pDelete = new QPushButton("Delete");
|
|
QPushButton *pModify = new QPushButton("Modify");
|
|
hlayout->addWidget(pInsert);
|
|
hlayout->addWidget(pDelete);
|
|
hlayout->addWidget(pModify);
|
|
vlayout->addLayout(hlayout);
|
|
|
|
connect(pInsert, SIGNAL(released()),this, SLOT(on_keyword_button_insert()));
|
|
connect(pDelete, SIGNAL(released()),this, SLOT(on_keyword_button_delete()));
|
|
connect(pModify, SIGNAL(released()),this, SLOT(on_keyword_button_modify()));
|
|
}
|
|
QGroupBox *groupBox = new QGroupBox("Keyword");
|
|
groupBox->setAlignment(Qt::AlignLeft|Qt::AlignTop);
|
|
groupBox->setLayout(vlayout);
|
|
return groupBox;
|
|
}
|
|
|
|
QGroupBox *Widget::setGroupWidgets()
|
|
{
|
|
QVBoxLayout *vlayout = new QVBoxLayout();
|
|
{
|
|
m_ptableGroup = new QTableView;
|
|
m_ptableGroup->setModel(m_pmodelGroup);
|
|
m_ptableGroup->setSelectionBehavior(QAbstractItemView::SelectRows);
|
|
m_ptableGroup->setSelectionMode(QAbstractItemView::SingleSelection);
|
|
vlayout->addWidget(m_ptableGroup);
|
|
|
|
connect(m_ptableGroup->selectionModel(), SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
|
|
this, SLOT(on_group_currentRowChanged(QModelIndex)));
|
|
}
|
|
{
|
|
m_pleGroup = new QLineEdit;
|
|
}
|
|
|
|
{
|
|
QHBoxLayout *hlayout = new QHBoxLayout();
|
|
hlayout->setAlignment(Qt::AlignLeft|Qt::AlignTop);
|
|
hlayout->addWidget(new QLabel("Group Name:"));
|
|
hlayout->addWidget(m_pleGroup);
|
|
vlayout->addLayout(hlayout);
|
|
}
|
|
{
|
|
QHBoxLayout *hlayout = new QHBoxLayout();
|
|
QPushButton *pInsert = new QPushButton("Insert");
|
|
QPushButton *pDelete = new QPushButton("Delete");
|
|
QPushButton *pClear = new QPushButton("Clear");
|
|
QPushButton *pModify = new QPushButton("Modify");
|
|
QPushButton *pImport = new QPushButton("Import");
|
|
QPushButton *pExport = new QPushButton("Export");
|
|
QPushButton *pListExport = new QPushButton("List Export");
|
|
QPushButton *pRefresh = new QPushButton("Refresh");
|
|
hlayout->addWidget(pInsert);
|
|
//hlayout->addWidget(pDelete);
|
|
//hlayout->addWidget(pClear);
|
|
hlayout->addWidget(pModify);
|
|
hlayout->addWidget(pImport);
|
|
hlayout->addWidget(pExport);
|
|
hlayout->addWidget(pListExport);
|
|
hlayout->addWidget(pRefresh);
|
|
connect(pInsert, SIGNAL(released()),this, SLOT(on_group_button_insert()));
|
|
connect(pDelete, SIGNAL(released()),this, SLOT(on_group_button_delete()));
|
|
connect(pClear, SIGNAL(released()),this, SLOT(on_group_button_clear()));
|
|
connect(pModify, SIGNAL(released()),this, SLOT(on_group_button_modify()));
|
|
connect(pImport, SIGNAL(released()),this, SLOT(on_group_button_import()));
|
|
connect(pExport, SIGNAL(released()),this, SLOT(on_group_button_export()));
|
|
connect(pListExport, SIGNAL(released()),this, SLOT(on_group_button_list_export()));
|
|
connect(pRefresh, SIGNAL(released()),this, SLOT(on_group_button_refresh()));
|
|
vlayout->addLayout(hlayout);
|
|
}
|
|
|
|
{
|
|
QHBoxLayout *hlayout = new QHBoxLayout();
|
|
m_pleCopyFrom = new QLineEdit();
|
|
m_pleCopyTo = new QLineEdit();
|
|
//m_pleCopyFrom->setReadOnly(true);
|
|
//m_pleCopyTo->setReadOnly(true);
|
|
QPushButton *pCopyAddFrom = new QPushButton("Add");
|
|
QPushButton *pCopyAddTo = new QPushButton("Add");
|
|
QPushButton *pStart = new QPushButton("Start");
|
|
QPushButton *pClear = new QPushButton("Clear");
|
|
|
|
|
|
hlayout->addWidget(new QLabel("Copy From: "));
|
|
hlayout->addWidget(m_pleCopyFrom);
|
|
hlayout->addWidget(pCopyAddFrom);
|
|
hlayout->addWidget(new QLabel("To :"));
|
|
hlayout->addWidget(m_pleCopyTo);
|
|
hlayout->addWidget(pCopyAddTo);
|
|
hlayout->addWidget(pStart);
|
|
hlayout->addWidget(pClear);
|
|
|
|
connect(pStart, SIGNAL(released()),this, SLOT(on_group_button_copy_start()));
|
|
connect(pClear, SIGNAL(released()),this, SLOT(on_group_button_copy_clear()));
|
|
connect(pCopyAddFrom, SIGNAL(released()),this, SLOT(on_group_button_copy_from()));
|
|
connect(pCopyAddTo, SIGNAL(released()),this, SLOT(on_group_button_copy_to()));
|
|
|
|
vlayout->addLayout(hlayout);
|
|
}
|
|
|
|
{
|
|
QHBoxLayout *hlayout = new QHBoxLayout();
|
|
m_pchbDate = new QCheckBox();
|
|
|
|
m_pdeCopyStart = new QDateEdit(QDate::currentDate());
|
|
m_pdeCopyEnd = new QDateEdit(QDate::currentDate());
|
|
|
|
m_pdeCopyStart->setDateRange(QDate(2003, 5, 20),QDate::currentDate());
|
|
m_pdeCopyEnd->setDateRange(QDate(2003, 5, 20),QDate::currentDate());
|
|
|
|
QCalendarWidget *pCalender = new QCalendarWidget();
|
|
m_pdeCopyStart->setCalendarWidget(pCalender);
|
|
m_pdeCopyStart->setCalendarPopup(true);
|
|
|
|
m_pdeCopyEnd->setCalendarWidget(pCalender);
|
|
m_pdeCopyEnd->setCalendarPopup(true);
|
|
|
|
hlayout->addWidget(new QLabel("Date Check: "));
|
|
hlayout->addWidget(m_pchbDate);
|
|
hlayout->addWidget(new QLabel("Start"));
|
|
hlayout->addWidget(m_pdeCopyStart);
|
|
hlayout->addWidget(new QLabel("End"));
|
|
hlayout->addWidget(m_pdeCopyEnd);
|
|
|
|
hlayout->setAlignment(Qt::AlignLeft);
|
|
|
|
vlayout->addLayout(hlayout);
|
|
|
|
|
|
}
|
|
QGroupBox *groupBox = new QGroupBox("Group");
|
|
groupBox->setAlignment(Qt::AlignLeft|Qt::AlignTop);
|
|
groupBox->setLayout(vlayout);
|
|
return groupBox;
|
|
}
|
|
|
|
QGroupBox *Widget::setCrawlingWidgets()
|
|
{
|
|
QVBoxLayout *vlayout = new QVBoxLayout();
|
|
|
|
{
|
|
QHBoxLayout *hlayout = new QHBoxLayout();
|
|
QPushButton *pInsert = new QPushButton("Insert");
|
|
QPushButton *pDelete = new QPushButton("Delete");
|
|
QPushButton *pModify = new QPushButton("State Modify");
|
|
QPushButton *pRefresh = new QPushButton("Refresh");
|
|
|
|
m_pcbState = new QComboBox();
|
|
m_pcbState->addItems(QStringList() << "Waiting" << "Running" << "Terminated" );
|
|
|
|
connect(pInsert, SIGNAL(released()),this, SLOT(on_crawling_button_insert()));
|
|
connect(pDelete, SIGNAL(released()),this, SLOT(on_crawling_button_delete()));
|
|
connect(pModify, SIGNAL(released()),this, SLOT(on_crawling_button_modify()));
|
|
connect(pRefresh, SIGNAL(released()),this, SLOT(on_crawling_button_refresh()));
|
|
|
|
hlayout->setAlignment(Qt::AlignCenter);
|
|
hlayout->addWidget(pInsert);
|
|
hlayout->addWidget(pDelete);
|
|
hlayout->addWidget(m_pcbState);
|
|
hlayout->addWidget(pModify);
|
|
hlayout->addWidget(pRefresh);
|
|
vlayout->addLayout(hlayout);
|
|
}
|
|
|
|
{
|
|
//m_ptableCrawling = new QTableView;
|
|
m_ptableCrawling = new STable;
|
|
m_ptableCrawling->setModel(m_pmodelCrawling);
|
|
m_ptableCrawling->setSelectionBehavior(QAbstractItemView::SelectRows);
|
|
m_ptableCrawling->setSelectionMode(QAbstractItemView::ExtendedSelection);
|
|
vlayout->addWidget(m_ptableCrawling);
|
|
}
|
|
|
|
QGroupBox *groupBox = new QGroupBox("Crawling");
|
|
groupBox->setAlignment(Qt::AlignLeft|Qt::AlignTop);
|
|
groupBox->setLayout(vlayout);
|
|
return groupBox;
|
|
}
|
|
|
|
void Widget::on_keyword_currentRowChanged(QModelIndex _index)
|
|
{
|
|
if (_index.isValid())
|
|
{
|
|
QSqlRecord rec = m_pmodelKeyword->record(_index.row());
|
|
m_pdeStart->setDate(QDate::fromString(rec.value("start").toString(),"yyyy-MM-dd"));
|
|
m_pdeEnd->setDate(QDate::fromString(rec.value("end").toString(),"yyyy-MM-dd"));
|
|
m_pcbRealTime->setCurrentIndex(rec.value("realtime").toInt());
|
|
m_pleKeyword->setText(rec.value("searches").toString());
|
|
int nSelect = 0;
|
|
QString str = rec.value("platform").toString();
|
|
if (str == QString("Naver Blog")) nSelect = 1;
|
|
if (str == QString("Daum Cafe")) nSelect = 2;
|
|
if (str == QString("Naver News")) nSelect = 3;
|
|
if (str == QString("Naver Cafe List")) nSelect = 4;
|
|
if (str == QString("Daum Cafe List")) nSelect = 5;
|
|
if (str == QString("Kakao Story Channel")) nSelect = 6;
|
|
if (str == QString("Kakao Story Tag")) nSelect = 7;
|
|
if (str == QString("Kakao Story User")) nSelect = 8;
|
|
if (str == QString("Instagram Tag")) nSelect = 9;
|
|
if (str == QString("Instagram User")) nSelect = 10;
|
|
if (str == QString("Facebook Tag")) nSelect = 11;
|
|
if (str == QString("Facebook User")) nSelect = 12;
|
|
if (str == QString("Naver Blog Accuracy")) nSelect = 13;
|
|
if (str == QString("Twitter Tag")) nSelect = 14;
|
|
if (str == QString("Twitter User")) nSelect = 15;
|
|
if (str == QString("Youtube Tag")) nSelect = 16;
|
|
if (str == QString("Youtube User")) nSelect = 17;
|
|
m_pcbPlatform->setCurrentIndex(nSelect);
|
|
}
|
|
}
|
|
|
|
void Widget::on_keyword_button_insert()
|
|
{
|
|
bool ok;
|
|
int nSplit = m_pleSplit->text().toInt(&ok);
|
|
if(!ok || nSplit < 1)
|
|
return;
|
|
|
|
QDate dateStart = m_pdeStart->date();
|
|
QDate dateEnd = m_pdeEnd->date();
|
|
|
|
if(m_pcbRealTime->currentIndex() == 1)
|
|
nSplit = 1;
|
|
|
|
qint64 nDays = dateStart.daysTo(dateEnd) + 1;
|
|
qint64 nDiv = nDays / nSplit;
|
|
qint64 nMod = nDays % nSplit;
|
|
|
|
if(m_pcbSplit->currentIndex() == 0)
|
|
{
|
|
QString strQuery = QString("insert into keyword set "
|
|
"start = STR_TO_DATE('%1', '%Y-%m-%d'),"
|
|
"end = STR_TO_DATE('%2', '%Y-%m-%d'),"
|
|
"searches = '%3',"
|
|
"realtime = %4,"
|
|
"authorship = '%5',"
|
|
"platform = %6")
|
|
.arg(m_pdeStart->date().toString("yyyy-MM-dd"))
|
|
.arg(m_pdeEnd->date().toString("yyyy-MM-dd"))
|
|
.arg(m_pleKeyword->text())
|
|
.arg(m_pcbRealTime->currentIndex())
|
|
.arg(m_pleAuthorship->text())
|
|
.arg(m_pcbPlatform->currentIndex());
|
|
m_pmodelKeyword->setQuery(QString(strQuery.toUtf8()));
|
|
}
|
|
else if(m_pcbSplit->currentIndex() == 1)
|
|
{
|
|
while(true)
|
|
{
|
|
QDate end = divideMonth(dateStart, nSplit);
|
|
if(dateStart > dateEnd)
|
|
break;
|
|
if(end > dateEnd)
|
|
end = dateEnd;
|
|
QString strQuery = QString("insert into keyword set "
|
|
"start = STR_TO_DATE('%1', '%Y-%m-%d'),"
|
|
"end = STR_TO_DATE('%2', '%Y-%m-%d'),"
|
|
"searches = '%3',"
|
|
"realtime = %4,"
|
|
"authorship = '%5',"
|
|
"platform = %6")
|
|
.arg(dateStart.toString("yyyy-MM-dd"))
|
|
.arg(end.toString("yyyy-MM-dd"))
|
|
.arg(m_pleKeyword->text())
|
|
.arg(m_pcbRealTime->currentIndex())
|
|
.arg(m_pleAuthorship->text())
|
|
.arg(m_pcbPlatform->currentIndex());
|
|
|
|
m_pmodelKeyword->setQuery(QString(strQuery.toUtf8()));
|
|
dateStart = end.addDays(1);
|
|
}
|
|
|
|
}
|
|
else
|
|
{
|
|
for(int i = 0; i < nSplit; i++)
|
|
{
|
|
int nAddDay = nDiv + ((nMod > 0)? 1:0) - 1;
|
|
qDebug() << nAddDay;
|
|
QString strQuery = QString("insert into keyword set "
|
|
"start = STR_TO_DATE('%1', '%Y-%m-%d'),"
|
|
"end = STR_TO_DATE('%2', '%Y-%m-%d'),"
|
|
"searches = '%3',"
|
|
"realtime = %4,"
|
|
"authorship = '%5',"
|
|
"platform = %6")
|
|
.arg(dateStart.toString("yyyy-MM-dd"))
|
|
.arg(dateStart.addDays(nAddDay).toString("yyyy-MM-dd"))
|
|
.arg(m_pleKeyword->text())
|
|
.arg(m_pcbRealTime->currentIndex())
|
|
.arg(m_pleAuthorship->text())
|
|
.arg(m_pcbPlatform->currentIndex());
|
|
|
|
m_pmodelKeyword->setQuery(QString(strQuery.toUtf8()));
|
|
nMod--;
|
|
dateStart = dateStart.addDays(nAddDay + 1);
|
|
}
|
|
}
|
|
|
|
/*
|
|
QString strQuery = QString("insert into keyword set "
|
|
"start = STR_TO_DATE('%1', '%Y-%m-%d'),"
|
|
"end = STR_TO_DATE('%2', '%Y-%m-%d'),"
|
|
"searches = '%3',"
|
|
"realtime = %4,"
|
|
"authorship = '%5',"
|
|
"platform = %6")
|
|
.arg(m_pdeStart->date().toString("yyyy-MM-dd"))
|
|
.arg(m_pdeEnd->date().toString("yyyy-MM-dd"))
|
|
.arg(m_pleKeyword->text())
|
|
.arg(m_pcbRealTime->currentIndex())
|
|
.arg(m_pleAuthorship->text())
|
|
.arg(m_pcbPlatform->currentIndex());
|
|
|
|
m_pmodelKeyword->setQuery(QString(strQuery.toUtf8()));
|
|
*/
|
|
m_pmodelKeyword->setQuery("SELECT id,realtime,searches,start,end,authorship,CASE platform "
|
|
"WHEN 0 THEN 'Naver Cafe' "
|
|
"WHEN 1 THEN 'Naver Blog' "
|
|
"WHEN 2 THEN 'Daum Cafe' "
|
|
"WHEN 3 THEN 'Naver News' "
|
|
"WHEN 4 THEN 'Naver Cafe List' "
|
|
"WHEN 5 THEN 'Daum Cafe List' "
|
|
"WHEN 6 THEN 'Kakao Story Channel' "
|
|
"WHEN 7 THEN 'Kakao Story Tag' "
|
|
"WHEN 8 THEN 'Kakao Story User' "
|
|
"WHEN 9 THEN 'Instagram Tag' "
|
|
"WHEN 10 THEN 'Instagram User' "
|
|
"WHEN 11 THEN 'Facebook Tag' "
|
|
"WHEN 12 THEN 'Facebook User' "
|
|
"WHEN 13 THEN 'Naver Blog Accuracy' "
|
|
"WHEN 14 THEN 'Twitter Tag' "
|
|
"WHEN 15 THEN 'Twitter User' "
|
|
"WHEN 16 THEN 'Youtube Tag' "
|
|
"WHEN 17 THEN 'Youtube User' "
|
|
"ELSE 'UnKnown'"
|
|
"END AS platform FROM keyword where state is null");
|
|
}
|
|
|
|
void Widget::on_keyword_button_delete()
|
|
{
|
|
QStringList strList;
|
|
foreach (QModelIndex index,m_ptableKeyword->selectionModel()->selectedIndexes())
|
|
strList.push_back(m_pmodelKeyword->record(index.row()).value("id").toString());
|
|
|
|
foreach (QString str, strList)
|
|
{
|
|
QString strQuery = QString("UPDATE keyword set state = '1' where id = '" + str + "'");
|
|
m_pmodelKeyword->setQuery(QString(strQuery.toUtf8()));
|
|
}
|
|
|
|
m_pmodelKeyword->setQuery("SELECT id,realtime,searches,start,end,authorship,CASE platform "
|
|
"WHEN 0 THEN 'Naver Cafe' "
|
|
"WHEN 1 THEN 'Naver Blog' "
|
|
"WHEN 2 THEN 'Daum Cafe' "
|
|
"WHEN 3 THEN 'Naver News' "
|
|
"WHEN 4 THEN 'Naver Cafe List' "
|
|
"WHEN 5 THEN 'Daum Cafe List' "
|
|
"WHEN 6 THEN 'Kakao Story Channel' "
|
|
"WHEN 7 THEN 'Kakao Story Tag' "
|
|
"WHEN 8 THEN 'Kakao Story User' "
|
|
"WHEN 9 THEN 'Instagram Tag' "
|
|
"WHEN 10 THEN 'Instagram User' "
|
|
"WHEN 11 THEN 'Facebook Tag' "
|
|
"WHEN 12 THEN 'Facebook User' "
|
|
"WHEN 13 THEN 'Naver Blog Accuracy' "
|
|
"WHEN 14 THEN 'Twitter Tag' "
|
|
"WHEN 15 THEN 'Twitter User' "
|
|
"WHEN 16 THEN 'Youtube Tag' "
|
|
"WHEN 17 THEN 'Youtube User' "
|
|
"ELSE 'UnKnown'"
|
|
"END AS platform FROM keyword where state is null");
|
|
}
|
|
|
|
void Widget::on_keyword_button_modify()
|
|
{
|
|
foreach (QModelIndex index,m_ptableKeyword->selectionModel()->selectedIndexes())
|
|
{
|
|
QSqlRecord rec = m_pmodelKeyword->record(index.row());
|
|
QString strQuery = QString("update keyword set "
|
|
"start = STR_TO_DATE('%1', '%Y-%m-%d'),"
|
|
"end = STR_TO_DATE('%2', '%Y-%m-%d'),"
|
|
"searches = '%3',"
|
|
"realtime = %4,"
|
|
"authorship = '%5',"
|
|
"platform = %6 "
|
|
"where id = '%7'")
|
|
.arg(m_pdeStart->date().toString("yyyy-MM-dd"))
|
|
.arg(m_pdeEnd->date().toString("yyyy-MM-dd"))
|
|
.arg(m_pleKeyword->text())
|
|
.arg(m_pcbRealTime->currentIndex())
|
|
.arg(m_pleAuthorship->text())
|
|
.arg(m_pcbPlatform->currentIndex())
|
|
.arg(rec.value("id").toString());
|
|
m_pmodelKeyword->setQuery(QString(strQuery.toUtf8()));
|
|
}
|
|
m_pmodelKeyword->setQuery("SELECT id,realtime,searches,start,end,authorship,CASE platform "
|
|
"WHEN 0 THEN 'Naver Cafe' "
|
|
"WHEN 1 THEN 'Naver Blog' "
|
|
"WHEN 2 THEN 'Daum Cafe' "
|
|
"WHEN 3 THEN 'Naver News' "
|
|
"WHEN 4 THEN 'Naver Cafe List' "
|
|
"WHEN 5 THEN 'Daum Cafe List' "
|
|
"WHEN 6 THEN 'Kakao Story Channel' "
|
|
"WHEN 7 THEN 'Kakao Story Tag' "
|
|
"WHEN 8 THEN 'Kakao Story User' "
|
|
"WHEN 9 THEN 'Instagram Tag' "
|
|
"WHEN 10 THEN 'Instagram User' "
|
|
"WHEN 11 THEN 'Facebook Tag' "
|
|
"WHEN 12 THEN 'Facebook User' "
|
|
"WHEN 13 THEN 'Naver Blog Accuracy' "
|
|
"WHEN 14 THEN 'Twitter Tag' "
|
|
"WHEN 15 THEN 'Twitter User' "
|
|
"WHEN 16 THEN 'Youtube Tag' "
|
|
"WHEN 17 THEN 'Youtube User' "
|
|
"ELSE 'UnKnown'"
|
|
"END AS platform FROM keyword where state is null");
|
|
}
|
|
|
|
void Widget::on_group_currentRowChanged(QModelIndex _index)
|
|
{
|
|
if (_index.isValid())
|
|
{
|
|
QSqlRecord rec = m_pmodelGroup->record(_index.row());
|
|
m_pleGroup->setText(rec.value("name").toString());
|
|
}
|
|
}
|
|
|
|
void Widget::on_group_button_insert()
|
|
{
|
|
QSqlQuery sql;
|
|
sql.exec("SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = \"concepters\" AND TABLE_NAME = \"datagroup\"");
|
|
sql.next();
|
|
QString strQuery = QString("insert into datagroup set "
|
|
"name = '" + m_pleGroup->text() + "'");
|
|
m_pmodelGroup->setQuery(strQuery.toUtf8());
|
|
strQuery = "CREATE TABLE data_";
|
|
strQuery += sql.value(0).toString();
|
|
strQuery += " ("
|
|
"platform_name CHAR(64),"
|
|
"platform_form CHAR(64),"
|
|
"platform_title VARCHAR(256),"
|
|
"article_form CHAR(32),"
|
|
"article_parent VARCHAR(256),"
|
|
"article_id CHAR(128),"
|
|
"article_nickname VARCHAR(256),"
|
|
"article_title VARCHAR(1024),"
|
|
"article_data MEDIUMTEXT,"
|
|
"article_url CHAR(255),"
|
|
"article_hit INT,"
|
|
"article_date DATETIME,"
|
|
"article_order SMALLINT,"
|
|
"article_profile VARCHAR(1024),"
|
|
"article_profileurl VARCHAR(512),"
|
|
"platform_id CHAR(128),"
|
|
"keyword_id INT,"
|
|
"reply_url VARCHAR(1024),"
|
|
"etc TEXT, "
|
|
"KEY article_url (article_url)"
|
|
") CHARSET=utf8";
|
|
sql.exec(strQuery);
|
|
m_pmodelGroup->setQuery("SELECT * FROM datagroup");
|
|
}
|
|
|
|
void Widget::on_group_button_delete()
|
|
{
|
|
foreach (QModelIndex index,m_ptableGroup->selectionModel()->selectedIndexes())
|
|
{
|
|
QSqlRecord rec = m_pmodelGroup->record(index.row());
|
|
QString strQuery = QString("delete from datagroup where id = '" + rec.value("id").toString() + "'");
|
|
m_pmodelGroup->setQuery(QString(strQuery.toUtf8()));
|
|
QSqlQuery sql;
|
|
strQuery = "drop table data_" + rec.value("id").toString();
|
|
sql.exec(strQuery);
|
|
}
|
|
m_pmodelGroup->setQuery("SELECT * FROM datagroup");
|
|
}
|
|
|
|
void Widget::on_group_button_clear()
|
|
{
|
|
foreach (QModelIndex index,m_ptableGroup->selectionModel()->selectedIndexes())
|
|
{
|
|
QSqlRecord rec = m_pmodelGroup->record(index.row());
|
|
QSqlQuery sql;
|
|
sql.exec("delete from data_" + rec.value("id").toString());
|
|
}
|
|
m_pmodelGroup->setQuery("SELECT * FROM datagroup");
|
|
}
|
|
|
|
void Widget::on_group_button_modify()
|
|
{
|
|
foreach (QModelIndex index,m_ptableGroup->selectionModel()->selectedIndexes())
|
|
{
|
|
QSqlRecord rec = m_pmodelGroup->record(index.row());
|
|
m_pmodelGroup->setQuery(QString("update datagroup set name = '" +
|
|
m_pleGroup->text() + "' where id = '" +
|
|
rec.value("id").toString() + "'" ).toUtf8());
|
|
}
|
|
m_pmodelGroup->setQuery("SELECT * FROM datagroup");
|
|
}
|
|
|
|
void Widget::on_group_button_export()
|
|
{
|
|
foreach (QModelIndex index,m_ptableGroup->selectionModel()->selectedIndexes())
|
|
{
|
|
SaveCsv("data_" + m_pmodelGroup->record(index.row()).value("id").toString());
|
|
}
|
|
}
|
|
|
|
void Widget::on_group_button_import()
|
|
{
|
|
|
|
QStringList strgroup;
|
|
foreach (QModelIndex index,m_ptableGroup->selectionModel()->selectedRows(0))
|
|
{
|
|
QSqlRecord rec = m_pmodelGroup->record(index.row());
|
|
strgroup.push_back(rec.value("id").toString());
|
|
}
|
|
|
|
|
|
QString strFilename = QFileDialog::getOpenFileName(0,"Import file",QDir::currentPath(),
|
|
"csv files (*.csv);;All files (*.*)",new QString("Text files (*.csv)"));
|
|
|
|
QFile file(strFilename);
|
|
if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) return;
|
|
|
|
//STable *pNew = new STable;
|
|
QTextStream in(&file);
|
|
int nCount=0;
|
|
QStringList attributes;
|
|
QString strquery;
|
|
QSqlQuery query;
|
|
while(!in.atEnd())
|
|
{
|
|
QString strLine;
|
|
strLine = in.readLine();
|
|
if (nCount == 0)
|
|
{
|
|
QStringList strings = strLine.split(",", QString::SkipEmptyParts);
|
|
if (strings.at(0).trimmed() == QString("#Head#"))
|
|
{
|
|
for (int i = 1; i < strings.size();i++)
|
|
attributes << strings.at(i);
|
|
nCount++;
|
|
|
|
}
|
|
else
|
|
{
|
|
for (int i = 0; i < strings.size();i++)
|
|
attributes << strings.at(i);
|
|
nCount++;
|
|
}
|
|
|
|
strquery = "insert into data_" + strgroup.at(0) + " (";
|
|
|
|
foreach(QString str, attributes)
|
|
{
|
|
strquery += (str.trimmed() + ",");
|
|
}
|
|
|
|
strquery = strquery.left(strquery.length() - 1);
|
|
strquery += ") VALUES (";
|
|
|
|
foreach(QString str,attributes)
|
|
{
|
|
strquery += ":" + str.trimmed().toUpper() + ",";
|
|
}
|
|
strquery = strquery.left(strquery.length() - 1);
|
|
strquery += ")";
|
|
|
|
continue;
|
|
}
|
|
|
|
strLine = strLine.replace("\"","");
|
|
QStringList strings = strLine.split(",");
|
|
|
|
query.prepare(strquery.toUtf8());
|
|
for(int i=0; i<attributes.size();i++)
|
|
{
|
|
|
|
if(attributes.at(i).trimmed() == "article_order")
|
|
{
|
|
|
|
if(strings.at(i).trimmed().length() == 0)
|
|
{
|
|
QString strEmpty;
|
|
query.bindValue(":"+attributes.at(i).toUpper(), strEmpty.toInt());
|
|
}
|
|
else
|
|
{
|
|
query.bindValue(":"+attributes.at(i).toUpper(), strings.at(i).trimmed().toInt());
|
|
}
|
|
|
|
query.bindValue(":"+attributes.at(i).toUpper(), strings.at(i).trimmed().toInt());
|
|
}
|
|
|
|
if(strings.at(i).trimmed().length() == 0)
|
|
{
|
|
QString strEmpty;
|
|
query.bindValue(":"+attributes.at(i).trimmed().toUpper(), strEmpty.toUtf8());
|
|
}
|
|
else
|
|
{
|
|
query.bindValue(":"+attributes.at(i).trimmed().toUpper(), strings.at(i).trimmed().toUtf8());
|
|
}
|
|
}
|
|
|
|
if(query.exec() == false)
|
|
{
|
|
qDebug() << "error : " << query.lastError().text();
|
|
}
|
|
}
|
|
|
|
|
|
file.close();
|
|
}
|
|
|
|
|
|
void Widget::on_group_button_list_export()
|
|
{
|
|
QString strFilename = QFileDialog::getSaveFileName(0,"Import file",QDir::currentPath(),
|
|
"csv files (*.csv);;All files (*.*)",new QString("Text files (*.csv)"));
|
|
|
|
QFile file(strFilename);
|
|
if (!file.open(QIODevice::WriteOnly | QIODevice::Text)) return;
|
|
|
|
//STable *pNew = new STable;
|
|
QTextStream in(&file);
|
|
|
|
for (int i = 0; i < m_pmodelGroup->rowCount(); ++i)
|
|
{
|
|
in << m_pmodelGroup->record(i).value("id").toString() + ","
|
|
+ m_pmodelGroup->record(i).value("name").toString() + ","
|
|
+ m_pmodelGroup->record(i).value("count").toString() + "\n";
|
|
}
|
|
file.close();
|
|
}
|
|
|
|
void Widget::on_group_button_refresh()
|
|
{
|
|
int row = m_ptableGroup->currentIndex().row();
|
|
if (row == -1)
|
|
return;
|
|
|
|
QString strId = m_pmodelGroup->record(row).value("id").toString();
|
|
qDebug() << strId;
|
|
|
|
if (strId.trimmed().isEmpty()) return;
|
|
|
|
QString strQuery;
|
|
QString strQueryUtf;
|
|
QSqlQuery query;
|
|
|
|
strQuery = "select count(*) from data_" + strId.trimmed();
|
|
strQueryUtf = strQuery.toUtf8();
|
|
if(query.exec(strQueryUtf) == false)
|
|
return;
|
|
QString strCount;
|
|
while(query.next())
|
|
strCount = query.value(0).toString().trimmed();
|
|
|
|
strQuery = "update datagroup set count = " + strCount + " where id = " + strId;
|
|
strQueryUtf = strQuery.toUtf8();
|
|
if(query.exec(strQueryUtf) == false)
|
|
return;
|
|
|
|
m_pmodelGroup->setQuery("SELECT * FROM datagroup");
|
|
|
|
}
|
|
|
|
void Widget::SaveCsv(QString _strName)
|
|
{
|
|
QFile file(_strName + ".csv" );
|
|
if(!file.open(QFile::WriteOnly | QFile::Text))
|
|
{
|
|
return;
|
|
}
|
|
QTextStream out(&file);
|
|
//out << QString("\"ID\",\"NICK\",\"DATE\",\"TITLE\",\"DATA\",\"CAFE ID\",\"CAFE NAME\",\"CAFE URL\",\"COMMENT NICK\",\"UPPER COMMENT NICK\",\"COMMENT DATE\",\"COMMENT\"\n");
|
|
QSqlQuery query;
|
|
QString strSelect;
|
|
strSelect = "select ";
|
|
strSelect += "CONVERT(platform_name USING utf8),";
|
|
strSelect += "CONVERT(platform_form USING utf8),";
|
|
strSelect += "CONVERT(platform_title USING utf8),";
|
|
strSelect += "CONVERT(article_form USING utf8),";
|
|
strSelect += "CONVERT(article_parent USING utf8),";
|
|
strSelect += "CONVERT(article_id USING utf8),";
|
|
strSelect += "CONVERT(article_nickname USING utf8),";
|
|
strSelect += "CONVERT(article_title USING utf8),";
|
|
strSelect += "CONVERT(article_data USING utf8),";
|
|
strSelect += "CONVERT(article_url USING utf8),";
|
|
strSelect += "CONVERT(article_hit USING utf8),";
|
|
strSelect += "CONVERT(article_date USING utf8),";
|
|
strSelect += "CONVERT(article_order USING utf8),";
|
|
strSelect += "CONVERT(keyword_id USING utf8),";
|
|
strSelect += "CONVERT(platform_id USING utf8),";
|
|
strSelect += "CONVERT(keyword_id USING utf8),";
|
|
strSelect += "CONVERT(reply_url USING utf8),";
|
|
strSelect += "CONVERT(etc USING utf8)";
|
|
strSelect += " from ";
|
|
strSelect += _strName;
|
|
if (query.exec(strSelect) == false)
|
|
{
|
|
out << query.lastError().text();
|
|
return ;
|
|
}
|
|
|
|
while (query.next())
|
|
{
|
|
for (int i = 0; i < 18; i++)
|
|
{
|
|
QString str = query.value(i).toString();
|
|
str = str.replace(",","");
|
|
str = str.replace("\n","");
|
|
out << "\"" <<str << "\"" << "," ;
|
|
}
|
|
out << "\"\"" << endl;
|
|
}
|
|
file.close();
|
|
}
|
|
|
|
void Widget::on_crawling_button_insert()
|
|
{
|
|
QString strGroupId;
|
|
foreach (QModelIndex index , m_ptableGroup->selectionModel()->selectedIndexes())
|
|
strGroupId = m_pmodelGroup->record(index.row()).value("id").toString();
|
|
if (strGroupId.isEmpty()) return;
|
|
|
|
QString strKeywordId;
|
|
/*
|
|
foreach (QModelIndex index,m_ptableKeyword->selectionModel()->selectedIndexes())
|
|
{
|
|
strKeywordId = m_pmodelKeyword->record(index.row()).value("id").toString();
|
|
qDebug() << strKeywordId;
|
|
}
|
|
*/
|
|
|
|
foreach (QModelIndex index, m_ptableKeyword->selectionModel()->selectedRows())
|
|
{
|
|
strKeywordId = m_pmodelKeyword->record(index.row()).value("id").toString();
|
|
if (strKeywordId.isEmpty()) return;
|
|
|
|
QString strQuery = "insert into crawling set ";
|
|
strQuery += "Keyword_id = '" + strKeywordId + "',";
|
|
strQuery += "DataGroup_id = '" + strGroupId + "',";
|
|
strQuery += "state = 0";
|
|
|
|
m_pmodelCrawling->setQuery(strQuery.toUtf8());
|
|
}
|
|
|
|
|
|
/*
|
|
if (strKeywordId.isEmpty()) return;
|
|
|
|
QString strQuery = "insert into crawling set ";
|
|
strQuery += "Keyword_id = '" + strKeywordId + "',";
|
|
strQuery += "DataGroup_id = '" + strGroupId + "',";
|
|
strQuery += "state = 0";
|
|
|
|
m_pmodelCrawling->setQuery(strQuery.toUtf8());
|
|
*/
|
|
UpdateCrawling();
|
|
}
|
|
|
|
void Widget::on_crawling_button_delete()
|
|
{
|
|
//foreach (QModelIndex index,m_ptableCrawling->selectionModel()->selectedIndexes())
|
|
QStringList strList;
|
|
foreach (QModelIndex index,m_ptableCrawling->selectionModel()->selectedRows(0))
|
|
{
|
|
QSqlRecord rec = m_pmodelCrawling->record(index.row());
|
|
strList.push_back(rec.value("id").toString());
|
|
//QString strQuery = QString("delete from crawling where id = '" + rec.value("id").toString() + "'");
|
|
}
|
|
foreach(QString str,strList)
|
|
{
|
|
QString strQuery = QString("delete from crawling where id = '" + str + "'");
|
|
m_pmodelCrawling->setQuery(QString(strQuery.toUtf8()));
|
|
}
|
|
UpdateCrawling();
|
|
}
|
|
|
|
void Widget::on_group_button_copy_clear()
|
|
{
|
|
m_pleCopyFrom->setText("");
|
|
m_pleCopyTo->setText("");
|
|
}
|
|
|
|
void Widget::on_group_button_copy_from()
|
|
{
|
|
QString strGroupId;
|
|
foreach (QModelIndex index , m_ptableGroup->selectionModel()->selectedIndexes())
|
|
strGroupId = m_pmodelGroup->record(index.row()).value("id").toString();
|
|
if (strGroupId.isEmpty()) return;
|
|
|
|
QString str = m_pleCopyFrom->text();
|
|
|
|
|
|
QStringList strList = str.split(";");
|
|
|
|
foreach(QString _str, strList)
|
|
{
|
|
if(_str.compare(strGroupId) == 0)
|
|
return;
|
|
}
|
|
|
|
QString strTo = m_pleCopyTo->text();
|
|
strList = strTo.split(";");
|
|
|
|
foreach(QString _str, strList)
|
|
{
|
|
if(_str.compare(strGroupId) == 0)
|
|
return;
|
|
}
|
|
|
|
str += (strGroupId + ";");
|
|
m_pleCopyFrom->setText(str);
|
|
}
|
|
|
|
|
|
void Widget::on_group_button_copy_to()
|
|
{
|
|
QString strGroupId;
|
|
foreach (QModelIndex index , m_ptableGroup->selectionModel()->selectedIndexes())
|
|
strGroupId = m_pmodelGroup->record(index.row()).value("id").toString();
|
|
if (strGroupId.isEmpty()) return;
|
|
|
|
QString str = m_pleCopyTo->text();
|
|
QStringList strList = str.split(";");
|
|
|
|
foreach(QString _str, strList)
|
|
{
|
|
if(_str.compare(strGroupId) == 0)
|
|
return;
|
|
}
|
|
|
|
QString strFrom = m_pleCopyFrom->text();
|
|
strList = strFrom.split(";");
|
|
|
|
foreach(QString _str, strList)
|
|
{
|
|
if(_str.compare(strGroupId) == 0)
|
|
return;
|
|
}
|
|
|
|
str += (strGroupId + ";");
|
|
m_pleCopyTo->setText(str);
|
|
}
|
|
|
|
void Widget::on_group_button_copy_start()
|
|
{
|
|
QString strQuery;
|
|
QString strQueryUtf;
|
|
QSqlQuery query;
|
|
|
|
QString strFrom = m_pleCopyFrom->text();
|
|
QString strTo = m_pleCopyTo->text();
|
|
|
|
if(strFrom.length() == 0)
|
|
return;
|
|
if(strTo.length() == 0)
|
|
return;
|
|
|
|
QStringList strListFrom = strFrom.split(";",QString::SkipEmptyParts);
|
|
QStringList strListTo = strTo.split(";",QString::SkipEmptyParts);
|
|
|
|
if(strListFrom.length() == 0)
|
|
return;
|
|
if(strListTo.length() == 0)
|
|
return;
|
|
|
|
bool ok;
|
|
for(int i = 0; i < strListTo.length(); i++)
|
|
{
|
|
strListTo.at(i).toInt(&ok);
|
|
if(!ok)
|
|
return;
|
|
for(int j = 0; j < strListFrom.length(); j++)
|
|
{
|
|
strListFrom.at(i).toInt(&ok);
|
|
if(!ok)
|
|
return;
|
|
if(m_pchbDate->isChecked())
|
|
{
|
|
strQuery = "insert into ";
|
|
strQuery += ("data_" + strListTo.at(i).trimmed());
|
|
strQuery += " (platform_name,platform_form,platform_title,article_form,article_parent,article_id,article_nickname,article_title,article_data,article_url,article_hit,article_date,article_order,article_profileurl,platform_id,keyword_id,reply_url,article_profile,etc) ";
|
|
strQuery += "select platform_name,platform_form,platform_title,article_form,article_parent,article_id,article_nickname,article_title,article_data,article_url,article_hit,article_date,article_order,article_profileurl,platform_id,keyword_id,reply_url,article_profile,etc "
|
|
"from data_" + strListFrom.at(j).trimmed() + " where article_url in (select distinct article_url from data_";
|
|
strQuery += strListFrom.at(j).trimmed();
|
|
strQuery += (" where article_date between '" + m_pdeCopyStart->date().toString("yyyy-MM-dd") + " 00:00:00' and '");
|
|
strQuery += (m_pdeCopyEnd->date().toString("yyyy-MM-dd") + " 23:59:59' and article_form='body')");
|
|
|
|
/*
|
|
strQuery = "insert into ";
|
|
strQuery += ("data_" + strListTo.at(i).trimmed());
|
|
strQuery += (" (select platform_name,platform_form,platform_title,article_form,article_parent,article_id,article_nickname,article_title,article_data,article_url,article_hit,article_date,article_order,article_profile,article_profileurl,platform_id,keyword_id,reply_url,etc from data_" + strListFrom.at(i).trimmed());
|
|
strQuery += (" where article_url in (select distinct article_url from data_" + strListFrom.at(i).trimmed());
|
|
strQuery += (" where article_date between '" + m_pdeCopyStart->date().toString("yyyy-MM-dd") + " 00:00:00' and '");
|
|
strQuery += (m_pdeCopyEnd->date().toString("yyyy-MM-dd") + " 23:59:59' and article_form='body')");
|
|
strQuery += (" and article_date >= '" + m_pdeCopyStart->date().toString("yyyy-MM-dd") + " 00:00:00' and article_date is not null)");
|
|
*/
|
|
}
|
|
else
|
|
{
|
|
strQuery = "insert into ";
|
|
strQuery += ("data_" + strListTo.at(i).trimmed());
|
|
strQuery += " (platform_name,platform_form,platform_title,article_form,article_parent,article_id,article_nickname,article_title,article_data,article_url,article_hit,article_date,article_order,article_profileurl,platform_id,keyword_id,reply_url,article_profile,etc) ";
|
|
strQuery += "select platform_name,platform_form,platform_title,article_form,article_parent,article_id,article_nickname,article_title,article_data,article_url,article_hit,article_date,article_order,article_profileurl,platform_id,keyword_id,reply_url,article_profile,etc "
|
|
"from data_" + strListFrom.at(j).trimmed();
|
|
/*
|
|
strQuery = "insert into ";
|
|
strQuery += ("data_" + strListTo.at(i).trimmed());
|
|
strQuery += " (select platform_name,platform_form,platform_title,article_form,article_parent,article_id,article_nickname,article_title,article_data,article_url,article_hit,article_date,article_order,article_profile,article_profileurl,platform_id,keyword_id,reply_url,etc from ";
|
|
strQuery += ("data_" + strListFrom.at(j).trimmed());
|
|
strQuery += " where (article_url, article_order) not in (select article_url, article_order from ";
|
|
strQuery += ("data_" + strListTo.at(i).trimmed());
|
|
strQuery += "))";
|
|
*/
|
|
}
|
|
qDebug(strQuery.toLatin1());
|
|
strQueryUtf = strQuery.toUtf8();
|
|
if(query.exec(strQueryUtf) == false)
|
|
{
|
|
m_pleCopyFrom->setText("Error");
|
|
m_pleCopyTo->setText("Error");
|
|
qDebug(query.lastError().text().toLatin1());
|
|
return;
|
|
}
|
|
}
|
|
}
|
|
m_pleCopyFrom->setText("Success");
|
|
m_pleCopyTo->setText("Sucess");
|
|
}
|
|
|
|
void Widget::UpdateCrawling()
|
|
{
|
|
m_pmodelCrawling->setQuery("SELECT _crawling.id,_keyword.realtime,_keyword.searches,_keyword.start,_keyword.end, _datagroup.name , "
|
|
"(CASE _keyword.platform "
|
|
"WHEN 0 THEN 'Naver Cafe' "
|
|
"WHEN 1 THEN 'Naver Blog' "
|
|
"WHEN 2 THEN 'Daum Cafe' "
|
|
"WHEN 3 THEN 'Naver News' "
|
|
"WHEN 4 THEN 'Naver Cafe List' "
|
|
"WHEN 5 THEN 'Daum Cafe List' "
|
|
"WHEN 6 THEN 'Kakao Story Channel' "
|
|
"WHEN 7 THEN 'Kakao Story Tag' "
|
|
"WHEN 8 THEN 'Kakao Story User' "
|
|
"WHEN 9 THEN 'Instagram Tag' "
|
|
"WHEN 10 THEN 'Instagram User' "
|
|
"WHEN 11 THEN 'Facebook Tag' "
|
|
"WHEN 12 THEN 'Facebook User' "
|
|
"WHEN 13 THEN 'Naver Blog Accuracy' "
|
|
"WHEN 14 THEN 'Twitter Tag' "
|
|
"WHEN 15 THEN 'Twitter User' "
|
|
"WHEN 16 THEN 'Youtube Tag' "
|
|
"WHEN 17 THEN 'Youtube User' "
|
|
"ELSE 'UnKnown' END ) AS platform , "
|
|
"(CASE _crawling.state WHEN 0 THEN 'Waiting' WHEN 1 THEN 'Running' WHEN 2 THEN 'Terminated' ELSE 'None' END ) AS state "
|
|
"FROM crawling _crawling INNER JOIN keyword _keyword ON _crawling.keyword_id = _keyword.id "
|
|
"inner join datagroup _datagroup on _crawling.datagroup_id = _datagroup.id");
|
|
}
|
|
|
|
void Widget::on_crawling_button_refresh()
|
|
{
|
|
UpdateCrawling();
|
|
}
|
|
|
|
void Widget::on_crawling_button_modify()
|
|
{
|
|
QStringList strList;
|
|
foreach (QModelIndex index,m_ptableCrawling->selectionModel()->selectedRows(0))
|
|
{
|
|
QSqlRecord rec = m_pmodelCrawling->record(index.row());
|
|
strList.push_back(rec.value("id").toString());
|
|
}
|
|
foreach(QString str,strList)
|
|
{
|
|
QString strQuery = QString("update crawling set state = " + QString::number(m_pcbState->currentIndex()) + " where id = '" + str + "'");
|
|
m_pmodelCrawling->setQuery(QString(strQuery.toUtf8()));
|
|
}
|
|
UpdateCrawling();
|
|
}
|