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"); }