bool CSVToolWindow::importStart()
{
QString mapname = atlasWindow()->map();
CSVAtlas *atlas = _atlasWindow->getAtlas();
if (mapname.isEmpty())
{
QStringList mList = atlas->mapList();
if(mList.isEmpty())
{
_msghandler->message(QtWarningMsg, tr("No Maps Loaded"),
tr("<p>There are no maps loaded to select from. "
"Either load an atlas that contains maps or "
"create a new one before continuing."));
return false;
}
mList.sort();
bool valid;
mapname = QInputDialog::getItem(this, tr("Select Map"), tr("Select Map:"),
mList, 0, false, &valid);
if (!valid)
return false;
}
CSVMap map = atlas->map(mapname);
map.simplify();
QList<CSVMapField> fields = map.fields();
if (map.name() != mapname || fields.isEmpty())
{
_msghandler->message(QtWarningMsg, tr("Invalid Map"),
tr("<p>The selected map does not appear to be valid."));
return false;
}
CSVMap::Action action = map.action();
if (action != CSVMap::Insert)
{
_msghandler->message(QtWarningMsg, tr("Action not implemented"),
tr("<p>The action %1 for this map is not supported.")
.arg(CSVMap::actionToName(action)));
return false;
}
if (!_data || _data->rows() < 1)
{
_msghandler->message(QtWarningMsg, tr("No data"),
tr("<p>There are no data to process. "
"Load a CSV file before continuing."));
return false;
}
int total = _data->rows();
int current = 0, error = 0, ignored = 0;
if (! _log)
_log = new LogWindow(this);
if(usetransaction) QSqlQuery begin("BEGIN;");
QString errMsg;
if(!map.sqlPre().trimmed().isEmpty())
{
if(usetransaction) QSqlQuery savepoint("SAVEPOINT presql;");
QSqlQuery pre;
if(!pre.exec(map.sqlPre()))
{
errMsg = QString("ERROR Running Pre SQL query: %1").arg(pre.lastError().text());
_log->_log->append("\n\n----------------------\n");
_log->_log->append(errMsg);
_log->show();
_log->raise();
if(map.sqlPreContinueOnError())
{
_log->_log->append(tr("\n\nContinuing with rest of import\n\n"));
if(usetransaction) QSqlQuery sprollback("ROLLBACK TO SAVEPOINT presql;");
if(usetransaction) QSqlQuery savepoint("RELEASE SAVEPOINT presql;");
}
else
{
if(usetransaction) QSqlQuery rollback("ROLLBACK;");
_msghandler->message(QtWarningMsg, tr("Error"),
tr("<p>There was an error running the Pre SQL "
"query. "
"Aborting transaction."
"\n\n----------------------\n%1").arg(errMsg));
return false;
}
}
}
QString progresstext(tr("Importing %1: %2 rows out of %3"));
int expected = total;
QProgressDialog *progress = new QProgressDialog(progresstext
.arg(map.name()).arg(0).arg(expected),
tr("Cancel"), 0, expected, this);
progress->setWindowModality(Qt::WindowModal);
bool userCanceled = false;
QString query;
QString front;
QString back;
QString value;
QString label;
QVariant var;
QStringList errorList;
for(current = 0; current < total; ++current)
{
if(usetransaction) QSqlQuery savepoint("SAVEPOINT csvinsert;");
if(action == CSVMap::Insert)
{
query = QString("INSERT INTO %1 ").arg(map.table());
front = "(";
back = " VALUES(";
QList<CSVMapField> fields = map.fields();
QMap<QString,QVariant> values;
for (int i = 0; i < fields.size(); i++)
{
switch(fields.at(i).action())
{
case CSVMapField::Action_UseColumn:
{
value = _data->value(current, fields.at(i).column()-1);
if(value.isNull())
{
switch (fields.at(i).ifNullAction())
{
case CSVMapField::UseDefault:
continue;
case CSVMapField::UseEmptyString:
{
var = QVariant(QString(""));
break;
}
case CSVMapField::UseAlternateValue:
{
var = QVariant(fields.at(i).valueAlt());
break;
}
case CSVMapField::UseAlternateColumn:
{
value = _data->value(current, fields.at(i).columnAlt()-1);
if(value.isNull())
{
switch (fields.at(i).ifNullActionAlt())
{
case CSVMapField::UseDefault:
continue;
case CSVMapField::UseEmptyString:
{
var = QVariant(QString(""));
break;
}
case CSVMapField::UseAlternateValue:
{
var = QVariant(fields.at(i).valueAlt());
break;
}
default: // Nothing
var = QVariant(QString::null);
}
}
else
var = QVariant(value);
break;
}
default: // Nothing
var = QVariant(QString::null);
}
}
else
var = QVariant(value);
break;
}
case CSVMapField::Action_UseEmptyString:
{
var = QVariant(QString(""));
break;
}
case CSVMapField::Action_UseAlternateValue:
{
var = QVariant(fields.at(i).valueAlt());
break;
}
case CSVMapField::Action_UseNull:
{
var = QVariant(QString::null);
break;
}
default:
continue;
}
label = ":" + fields.at(i).name();
if(!values.empty())
{
front += ", ";
back += ", ";
}
values.insert(label, var);
front += fields.at(i).name();
back += label;
}
if(values.empty())
{
ignored++;
errMsg = QString("IGNORED Record %1: There are no columns to insert").arg(current+1);
errorList.append(errMsg);
continue;
}
front += ") ";
back += ")";
query += front + back;
QSqlQuery qry;
qry.prepare(query);
QMap<QString,QVariant>::iterator vit;
for(vit = values.begin(); vit != values.end(); ++vit)
qry.bindValue(vit.key(), vit.value());
if(!qry.exec())
{
if(usetransaction) QSqlQuery sprollback("ROLLBACK TO SAVEPOINT csvinsert;");
error++;
errMsg = QString("ERROR Record %1: %2").arg(current+1).arg(qry.lastError().text());
errorList.append(errMsg);
}
}
if (progress->wasCanceled())
{
userCanceled = true;
break;
}
if(! (current % 1000))
{
progress->setLabelText(progresstext.arg(map.name()).arg(current).arg(expected));
progress->setValue(current);
}
}
progress->setValue(total);
if (error || ignored || userCanceled)
{
_log->_log->append(tr("Map: %1\n"
"Table: %2\n"
"Method: %3\n\n"
"Total Records: %4\n"
"# Processed: %5\n"
"# Ignored: %6\n"
"# Errors: %7\n\n")
.arg(map.name()).arg(map.table())
.arg(CSVMap::actionToName(map.action()))
.arg(total).arg(current).arg(ignored).arg(error));
_log->_log->append(errMsg);
_log->_log->append(errorList.join("\n"));
_log->show();
_log->raise();
if (_msghandler && // log messages there's a non-interactive message handler
qobject_cast<XAbstractMessageHandler*>(_msghandler) &&
! qobject_cast<InteractiveMessageHandler*>(_msghandler))
_msghandler->message(error ? QtCriticalMsg : QtWarningMsg,
tr("Import Processing Status"),
_log->_log->toPlainText());
}
if (! userCanceled && ! map.sqlPost().trimmed().isEmpty())
{
QSqlQuery post;
if(!post.exec(map.sqlPost()))
{
errMsg = QString("ERROR Running Post SQL query: %1").arg(post.lastError().text());
_log->_log->append("\n\n----------------------\n");
_log->_log->append(errMsg);
_log->show();
_log->raise();
if(usetransaction) QSqlQuery rollback("ROLLBACK;");
_msghandler->message(QtCriticalMsg, tr("Error"),
tr("<p>There was an error running the post sql "
"query and changes were rolled back. "
"\n\n----------------------\n%1").arg(errMsg));
return false;
}
}
if (userCanceled)
{
if(usetransaction) QSqlQuery rollback("ROLLBACK;");
_log->_log->append(tr("\n\nImport canceled by user. Changes were rolled back."));
return false;
}
if(usetransaction) QSqlQuery commit("COMMIT");
if (! error)
{
_msghandler->message(QtDebugMsg, tr("Import Complete"),
tr("The import of %1 completed successfully.")
.arg(_currentDir));
return true;
}
return false;
}
void CSVAtlasWindow::sMapChanged( int )
{
CSVMap map;
if(!_selectedMap.isEmpty())
{
map = _atlas->map(_selectedMap);
if(tr("Insert") == _action->currentText())
map.setAction(CSVMap::Insert);
else if(tr("Update") == _action->currentText())
map.setAction(CSVMap::Update);
else if(tr("Append") == _action->currentText())
map.setAction(CSVMap::Append);
map.setDescription(_description->toPlainText());
map.setSqlPre(_preSql->toPlainText().trimmed());
map.setSqlPreContinueOnError(_sqlPreContinueOnError->isChecked());
map.setSqlPost(_postSql->toPlainText().trimmed());
for(int r = 0; r < _fields->rowCount(); r++)
{
CSVMapField field = map.field(_fields->item(r, 1)->data(Qt::EditRole).toString());
field.setName(_fields->item(r, 1)->data(Qt::EditRole).toString());
if (qobject_cast<QCheckBox*>(_fields->cellWidget(r, 0)))
field.setIsKey(qobject_cast<QCheckBox*>(_fields->cellWidget(r,0))->isChecked());
else
field.setIsKey(FALSE);
field.setType(QVariant::nameToType(_fields->item(r, 2)->data(Qt::EditRole).toString().toAscii().data()));
if (qobject_cast<QComboBox*>(_fields->cellWidget(r, 4)))
field.setAction(CSVMapField::nameToAction(qobject_cast<QComboBox*>(_fields->cellWidget(r, 4))->currentText()));
else
field.setAction(CSVMapField::Action_Default);
if (qobject_cast<QSpinBox*>(_fields->cellWidget(r, 5)))
field.setColumn(qobject_cast<QSpinBox*>(_fields->cellWidget(r,5))->value());
else
field.setColumn(0);
if (qobject_cast<QComboBox*>(_fields->cellWidget(r, 6)))
field.setIfNullAction(CSVMapField::nameToIfNull(qobject_cast<QComboBox*>(_fields->cellWidget(r, 6))->currentText()));
else
field.setIfNullAction(CSVMapField::Nothing);
if (qobject_cast<QSpinBox*>(_fields->cellWidget(r, 7)))
field.setColumnAlt(qobject_cast<QSpinBox*>(_fields->cellWidget(r, 7))->value());
else
field.setColumnAlt(1);
if (qobject_cast<QComboBox*>(_fields->cellWidget(r, 8)))
field.setIfNullActionAlt(CSVMapField::nameToIfNull(qobject_cast<QComboBox*>(_fields->cellWidget(r, 8))->currentText()));
else
field.setIfNullActionAlt(CSVMapField::Nothing);
field.setValueAlt(_fields->item(r, 9)->data(Qt::EditRole).toString());
map.setField(field);
}
map.simplify();
_atlas->setMap(map);
}
QSqlDatabase db = QSqlDatabase::database();
if (db.isValid())
{
_fields->setRowCount(0);
if(_map->count() && ! _map->currentText().isEmpty())
{
// CODE TO SELECT MAP
_selectedMap = _map->currentText();
map = _atlas->map(_selectedMap);
_table->setTitle(tr("Table: ") + map.table());
_table->setEnabled(TRUE);
_action->setCurrentIndex(map.action());
_description->setText(map.description());
_preSql->setText(map.sqlPre());
_sqlPreContinueOnError->setChecked(map.sqlPreContinueOnError());
_postSql->setText(map.sqlPost());
QSqlRecord record = db.record(map.table());
QStringList fieldnames;
if (record.isEmpty())
{
_msghandler->message(QtWarningMsg, tr("No Existing Table"),
tr("<p>The table %1 does not exist in this "
"database. You may continue to use and edit "
"this map but only those fields that are known "
"will be shown.").arg(map.table()),
QUrl(), QSourceLocation());
fieldnames = map.fieldList();
}
else
{
QStringList fList = map.fieldList();
for(int i = 0; i < fList.size(); ++i)
{
CSVMapField f = map.field(fList.at(i));
if(!record.contains(fList.at(i)))
{
map.removeField(fList.at(i));
MissingField diag(this, f.name(), record);
if(diag.exec() == QDialog::Accepted)
{
f.setName(diag._fields->currentText());
map.setField(f);
}
_atlas->setMap(map);
}
}
for (int i = 0; i < record.count(); i++)
fieldnames.append(record.fieldName(i));
}
_fields->setRowCount(fieldnames.size());
for(int row = 0; row < fieldnames.size(); ++row)
{
CSVMapField mf = map.field(fieldnames.at(row));
QCheckBox *check = new QCheckBox(_fields);
if(!mf.isEmpty())
check->setChecked(mf.isKey());
_fields->setCellWidget(row, 0, check);
_fields->setItem(row, 1, new QTableWidgetItem(fieldnames.at(row)));
if (record.isEmpty())
{
_fields->setItem(row, 2,
new QTableWidgetItem(QVariant::typeToName(mf.type())));
_fields->setItem(row, 3, new QTableWidgetItem(tr("Unknown")));
}
else
{
_fields->setItem(row, 2,
new QTableWidgetItem(QVariant::typeToName(record.field(row).type())));
_fields->setItem(row, 3, new QTableWidgetItem(
(record.field(row).requiredStatus() == QSqlField::Required) ? tr("Yes") :
(record.field(row).requiredStatus() == QSqlField::Optional) ? tr("No") :
tr("Unknown")));
}
QComboBox *actcombo = new QComboBox(_fields);
actcombo->addItems(CSVMapField::actionList());
if (! mf.isEmpty())
actcombo->setCurrentIndex(mf.action());
_fields->setCellWidget(row, 4, actcombo);
QSpinBox *colspinner = new QSpinBox(_fields);
colspinner->setRange(1, 999);
colspinner->setPrefix(tr("Column "));
if(!mf.isEmpty())
colspinner->setValue(mf.column());
_fields->setCellWidget(row, 5, colspinner);
QComboBox *nullcombo = new QComboBox(_fields);
nullcombo->addItems(CSVMapField::ifNullList());
if (! mf.isEmpty())
nullcombo->setCurrentIndex(mf.ifNullAction());
_fields->setCellWidget(row, 6, nullcombo);
QSpinBox *altspinner = new QSpinBox(_fields);
altspinner->setRange(1, 999);
altspinner->setPrefix(tr("Column "));
if (! mf.isEmpty())
altspinner->setValue(mf.columnAlt());
_fields->setCellWidget(row, 7, altspinner);
QComboBox *altnullcombo = new QComboBox(_fields);
altnullcombo->addItems(CSVMapField::ifNullList(TRUE));
if (! mf.isEmpty())
altnullcombo->setCurrentIndex(mf.ifNullActionAlt());
_fields->setCellWidget(row, 8, altnullcombo);
_fields->setItem(row, 9, new QTableWidgetItem(mf.valueAlt()));
RowController *control = new RowController(_fields, row, colspinner);
control->setAction(actcombo);
control->setColumn(colspinner);
control->setIfNull(nullcombo);
control->setAltColumn(altspinner);
control->setAltIfNull(altnullcombo);
control->setAltValue(_fields->item(row, 9));
control->finishSetup();
}
}
else
{
_selectedMap = QString::null;
_table->setTitle(tr("Table: "));
_table->setEnabled(FALSE);
}
}
else
_msghandler->message(QtCriticalMsg, tr("No Database"),
tr("Could not get the database connection."));
}