QString ExportHelper::generateHTML(QString qtext, ParameterList ¶ms, QString &errmsg)
{
if (DEBUG)
qDebug("ExportHelper::generateHTML(%s..., %d params, errmsg) entered",
qPrintable(qtext.left(80)), params.size());
if (qtext.isEmpty())
return QString::null;
QTextDocument doc(0);
QTextCursor cursor(&doc);
QTextTableFormat tablefmt;
bool valid;
QVariant includeheaderVar = params.value("includeHeaderLine", &valid);
bool includeheader = (valid ? includeheaderVar.toBool() : false);
if (DEBUG)
qDebug("generateHTML(qtest, params, errmsg) includeheader = %d, valid = %d",
includeheader, valid);
MetaSQLQuery mql(qtext);
XSqlQuery qry = mql.toQuery(params);
if (qry.first())
{
int cols = qry.record().count();
int expected = qry.size();
if (includeheader)
expected++;
// presize the table
cursor.insertTable((expected < 0 ? 1 : expected), cols, tablefmt);
if (includeheader)
{
tablefmt.setHeaderRowCount(1);
for (int p = 0; p < cols; p++)
{
cursor.insertText(qry.record().fieldName(p));
cursor.movePosition(QTextCursor::NextCell);
}
}
do {
for (int i = 0; i < cols; i++)
{
cursor.insertText(qry.value(i).toString());
cursor.movePosition(QTextCursor::NextCell);
}
} while (qry.next());
}
if (qry.lastError().type() != QSqlError::NoError)
errmsg = qry.lastError().text();
return doc.toHtml();
}
QString ExportHelper::generateXML(QString qtext, QString tableElemName, ParameterList ¶ms, QString &errmsg, int xsltmapid)
{
if (DEBUG)
qDebug("ExportHelper::generateXML(%s..., %s, %d params, errmsg, %d) entered",
qPrintable(qtext.left(80)), qPrintable(tableElemName),
params.size(), xsltmapid);
if (DEBUG)
{
QStringList plist;
for (int i = 0; i < params.size(); i++)
plist.append("\t" + params.name(i) + ":\t" + params.value(i).toString());
qDebug("generateXML parameters:\n%s", qPrintable(plist.join("\n")));
}
QDomDocument xmldoc("xtupleimport");
QDomElement rootelem = xmldoc.createElement("xtupleimport");
xmldoc.appendChild(rootelem);
if (! qtext.isEmpty())
{
MetaSQLQuery mql(qtext);
XSqlQuery qry = mql.toQuery(params);
if (qry.first())
{
do {
QDomElement tableElem = xmldoc.createElement(tableElemName);
if (DEBUG)
qDebug("generateXML starting %s", qPrintable(tableElemName));
for (int i = 0; i < qry.record().count(); i++)
{
QDomElement fieldElem = xmldoc.createElement(qry.record().fieldName(i));
if (qry.record().value(i).isNull())
fieldElem.appendChild(xmldoc.createTextNode("[NULL]"));
else
fieldElem.appendChild(xmldoc.createTextNode(qry.record().value(i).toString()));
tableElem.appendChild(fieldElem);
}
rootelem.appendChild(tableElem);
} while (qry.next());
}
if (qry.lastError().type() != QSqlError::NoError)
errmsg = qry.lastError().text();
}
if (xsltmapid < 0)
return xmldoc.toString();
else
return XSLTConvertString(xmldoc.toString(), xsltmapid, errmsg);
}
QSqlRecord XSqlQueryProto::record() const
{
XSqlQuery *item = qscriptvalue_cast<XSqlQuery*>(thisObject());
if (item)
return item->record();
return QSqlRecord();
}
void MQLEdit::execQuery()
{
if(!_loggedIn) {
QMessageBox::warning(this, tr("Not Connected"),
tr("You must be connected to a database in order to execute a query."));
return;
}
_results->_table->setRowCount(0);
_results->_table->setColumnCount(0);
_sql->_log->clear();
_log->_log->clear();
_log->_log->append(tr("---- Parsing Query ----\n"));
MetaSQLQuery mql(_text->toPlainText());
_log->_log->append(mql.parseLog());
if(mql.isValid()) {
_log->_log->append(tr("Query parsed."));
_log->_log->append(tr("---- Executing Query ----"));
ParameterList plist = _pEdit->getParameterList();
XSqlQuery qry = mql.toQuery(plist);
_sql->_log->append(qry.executedQuery());
if(qry.isActive()) {
QSqlRecord rec = qry.record();
int ncols = rec.count();
_results->_table->setColumnCount(ncols);
int c;
for(c = 0; c < ncols; c++) {
_results->_table->setHorizontalHeaderItem(c, new QTableWidgetItem(rec.fieldName(c)));
}
int nrows = 0;
while(qry.next()) {
_results->_table->setRowCount(nrows + 1);
for(c = 0; c < ncols; c++) {
_results->_table->setItem(nrows, c, new QTableWidgetItem(qry.value(c).toString()));
}
nrows++;
}
showResults();
} else {
_log->_log->append(tr("Failed to execute query."));
QSqlError err = qry.lastError();
_log->_log->append(err.text());
}
} else {
_log->_log->append(tr("ERROR: Invalid query!"));
showLog();
}
}
void ItemLineEdit::sHandleCompleter()
{
if (!hasFocus())
return;
QString stripped = text().trimmed().toUpper();
if (stripped.isEmpty())
return;
int width = 0;
QSqlQueryModel* model = static_cast<QSqlQueryModel *>(_completer->model());
QTreeView * view = static_cast<QTreeView *>(_completer->popup());
_parsed = true;
XSqlQuery numQ;
if (_useQuery)
{
numQ.prepare(QString("SELECT * FROM (%1) data WHERE (item_number ~* :number) LIMIT 10")
.arg(QString(_sql)).remove(";"));
numQ.bindValue(":number", "^" + stripped);
}
else
{
QString pre( "SELECT DISTINCT item_id, item_number, "
"(item_descrip1 || ' ' || item_descrip2) AS itemdescrip, "
"item_upccode AS description " );
QStringList clauses;
clauses = _extraClauses;
clauses << "(item_number ~* :searchString OR item_upccode ~* :searchString)";
numQ.prepare(buildItemLineEditQuery(pre, clauses, QString::null, _type).replace(";"," ORDER BY item_number LIMIT 10;"));
numQ.bindValue(":searchString", QString(text().trimmed().toUpper()).prepend("^"));
}
numQ.exec();
if (numQ.first())
{
int numberCol = numQ.record().indexOf("item_number");
int descripCol = numQ.record().indexOf("itemdescrip");
model->setQuery(numQ);
_completer->setCompletionPrefix(stripped);
for (int i = 0; i < model->columnCount(); i++)
{
if ( (i == numberCol) ||
(i == descripCol) )
{
view->resizeColumnToContents(i);
width += view->columnWidth(i);
}
else
view->hideColumn(i);
}
}
else
model->setQuery(QSqlQuery());
if (width > 350)
width = 350;
QRect rect;
rect.setHeight(height());
rect.setWidth(width);
rect.setBottomLeft(QPoint(0, height() - 2));
_completer->complete(rect);
_parsed = false;
}
void VirtualClusterLineEdit::sHandleCompleter()
{
if (!hasFocus())
return;
QString stripped = text().trimmed().toUpper();
if (stripped.isEmpty())
return;
int width = 0;
QSqlQueryModel* model = static_cast<QSqlQueryModel *>(_completer->model());
QTreeView * view = static_cast<QTreeView *>(_completer->popup());
_parsed = true;
XSqlQuery numQ;
numQ.prepare(_query + _numClause +
(_extraClause.isEmpty() || !_strict ? "" : " AND " + _extraClause) +
((_hasActive && ! _showInactive) ? _activeClause : "") +
QString(" ORDER BY %1 LIMIT 10;").arg(_numColName));
numQ.bindValue(":number", "^" + stripped);
numQ.exec();
if (numQ.first())
{
int numberCol = numQ.record().indexOf("number");
int nameCol = numQ.record().indexOf("name");
int descripCol = numQ.record().indexOf("description");
model->setQuery(numQ);
_completer->setCompletionPrefix(stripped);
for (int i = 0; i < model->columnCount(); i++)
{
if ( (i != numberCol) &&
(!_hasName || i != nameCol ) &&
(!_hasDescription || i != descripCol) )
{
view->hideColumn(i);
}
}
view->resizeColumnToContents(numberCol);
width += view->columnWidth(numberCol);
if (_hasName)
{
view->resizeColumnToContents(nameCol);
width += view->columnWidth(nameCol);
}
if (_hasDescription)
{
view->resizeColumnToContents(descripCol);
width += view->columnWidth(descripCol);
}
}
else
model->setQuery(QSqlQuery());
if (width > 350)
width = 350;
QRect rect;
rect.setHeight(height());
rect.setWidth(width);
rect.setBottomLeft(QPoint(0, height() - 2));
_completer->complete(rect);
_parsed = false;
}
QString ExportHelper::generateXML(const int qryheadid, ParameterList ¶ms, QString &errmsg, int xsltmapid)
{
if (DEBUG)
qDebug("ExportHelper::generateXML(%d, %d params, errmsg, %d) entered",
qryheadid, params.size(), xsltmapid);
if (DEBUG)
{
QStringList plist;
for (int i = 0; i < params.size(); i++)
plist.append("\t" + params.name(i) + ":\t" + params.value(i).toString());
qDebug("generateXML parameters:\n%s", qPrintable(plist.join("\n")));
}
QDomDocument xmldoc("xtupleimport");
QDomElement rootelem = xmldoc.createElement("xtupleimport");
xmldoc.appendChild(rootelem);
XSqlQuery itemq;
QString tableElemName;
QString schemaName;
itemq.prepare("SELECT * FROM qryitem WHERE qryitem_qryhead_id=:id ORDER BY qryitem_order;");
itemq.bindValue(":id", qryheadid);
itemq.exec();
while (itemq.next())
{
QString qtext;
tableElemName = itemq.value("qryitem_name").toString();
if (itemq.value("qryitem_src").toString() == "REL")
{
schemaName = itemq.value("qryitem_group").toString();
qtext = "SELECT * FROM " +
(schemaName.isEmpty() ? QString("") : schemaName + QString(".")) +
itemq.value("qryitem_detail").toString();
}
else if (itemq.value("qryitem_src").toString() == "MQL")
{
QString tmpmsg;
bool valid;
qtext = MQLUtil::mqlLoad(itemq.value("qryitem_group").toString(),
itemq.value("qryitem_detail").toString(),
tmpmsg, &valid);
if (! valid)
errmsg = tmpmsg;
}
else if (itemq.value("qryitem_src").toString() == "CUSTOM")
qtext = itemq.value("qryitem_detail").toString();
if (! qtext.isEmpty())
{
MetaSQLQuery mql(qtext);
XSqlQuery qry = mql.toQuery(params);
if (qry.first())
{
do {
QDomElement tableElem = xmldoc.createElement(tableElemName);
if (DEBUG)
qDebug("exportXML starting %s", qPrintable(tableElemName));
if (! schemaName.isEmpty())
tableElem.setAttribute("schema", schemaName);
for (int i = 0; i < qry.record().count(); i++)
{
QDomElement fieldElem = xmldoc.createElement(qry.record().fieldName(i));
if (qry.record().value(i).isNull())
fieldElem.appendChild(xmldoc.createTextNode("[NULL]"));
else
fieldElem.appendChild(xmldoc.createTextNode(qry.record().value(i).toString()));
tableElem.appendChild(fieldElem);
}
rootelem.appendChild(tableElem);
} while (qry.next());
}
if (qry.lastError().type() != QSqlError::NoError)
errmsg = qry.lastError().text();
}
}
if (itemq.lastError().type() != QSqlError::NoError)
errmsg = itemq.lastError().text();
if (xsltmapid < 0)
return xmldoc.toString();
else
return XSLTConvertString(xmldoc.toString(), xsltmapid, errmsg);
}
QString ExportHelper::generateDelimited(QString qtext, ParameterList ¶ms, QString &errmsg)
{
if (DEBUG)
qDebug("ExportHelper::generateDelimited(%s..., %d params, errmsg) entered",
qPrintable(qtext.left(80)), params.size());
if (qtext.isEmpty())
return QString::null;
if (DEBUG)
{
QStringList plist;
for (int i = 0; i < params.size(); i++)
plist.append("\t" + params.name(i) + ":\t" + params.value(i).toString());
qDebug("generateDelimited parameters:\n%s", qPrintable(plist.join("\n")));
}
bool valid;
QString delim = params.value("delim", &valid).toString();
if (! valid)
delim = ",";
if (DEBUG)
qDebug("generateDelimited(qtest, params, errmsg) delim = %s, valid = %d",
qPrintable(delim), valid);
QVariant includeheaderVar = params.value("includeHeaderLine", &valid);
bool includeheader = (valid ? includeheaderVar.toBool() : false);
if (DEBUG)
qDebug("generateDelimited(qtest, params, errmsg) includeheader = %d, valid = %d",
includeheader, valid);
QStringList line;
MetaSQLQuery mql(qtext);
XSqlQuery qry = mql.toQuery(params);
if (qry.first())
{
QStringList field;
int cols = qry.record().count();
if (includeheader)
{
for (int p = 0; p < cols; p++)
field.append(qry.record().fieldName(p));
line.append(field.join(delim));
}
QString tmp;
do {
field.clear();
for (int p = 0; p < cols; p++)
{
tmp = qry.value(p).toString();
if (tmp.contains(delim))
{
tmp.replace("\"", "\"\"");
tmp = "\"" + tmp + "\"";
}
field.append(tmp);
}
line.append(field.join(delim));
} while (qry.next());
}
if (qry.lastError().type() != QSqlError::NoError)
errmsg = qry.lastError().text();
return line.join("\n");
}
