Exemple #1
0
void updateCreditStatusByCustomer::sPopulate( int /*pCustid*/ )
{
  q.prepare( "SELECT cust_creditstatus "
             "FROM custinfo "
             "WHERE (cust_id=:cust_id);" );
  q.bindValue(":cust_id", _cust->id());
  q.exec();
  if (q.first())
  {
    QString stylesheet;
    if (q.value("cust_creditstatus").toString() == "G")
    {
      _inGoodStanding->setChecked(TRUE);
      _currentStatus->setText(tr("In Good Standing"));
    }
    else if (q.value("cust_creditstatus").toString() == "W")
    {
      _onCreditWarning->setChecked(TRUE);
      _currentStatus->setText(tr("On Credit Warning"));
      stylesheet = QString("* { color: %1; }").arg(namedColor("warning").name());
    }
    else if (q.value("cust_creditstatus").toString() == "H")
    {
      _onCreditHold->setChecked(TRUE);
      _currentStatus->setText(tr("On Credit Hold"));
      stylesheet = QString("* { color: %1; }").arg(namedColor("error").name());
    }
    _currentStatus->setStyleSheet(stylesheet);
  }
}
void itemPricingScheduleItem::sUpdateMargins()
{
  if (_item->isValid())
  {
    double price = _price->baseValue();
    QString stylesheet;

    if (_stdCost->baseValue() > 0.0)
    {
      _stdMargin->setDouble((price - _stdCost->baseValue()) / price * 100);

      if (_stdCost->baseValue() > price)
        stylesheet = QString("* { color: %1; }").arg(namedColor("error").name());
    }
    else
      _stdMargin->setText("N/A");

    _stdMargin->setStyleSheet(stylesheet);

    if (_actCost->baseValue() > 0.0)
    {
      _actMargin->setDouble((price - _actCost->baseValue()) / price * 100);

      if (_actCost->baseValue() > price)
        stylesheet = QString("* { color: %1; }").arg(namedColor("error").name());
    }
    else
      _actMargin->setText("N/A");

    _actMargin->setStyleSheet(stylesheet);
  }
}
Exemple #3
0
JSHighlighter::JSHighlighter(QTextDocument *document)
  : QSyntaxHighlighter(document)
{
  _commentColor   = namedColor("altemphasis");
  _errorColor     = namedColor("error");
  _extensionColor = namedColor("warning");
  _keywordColor   = namedColor("emphasis");
  _literalColor   = namedColor("future");

  _keyword  << "break"     << "case"    << "catch"          << "continue"
            << "default"   << "delete"  << "do"             << "else"
            << "false"     << "finally" << "for"            << "function"
            << "if"        << "in"      << "instanceof"     << "new"
            << "null"      << "return"  << "switch"         << "this"
            << "throw"     << "true"    << "try"            << "typeof"
            << "var"       << "void"    << "while"          << "with"
            ;
  _extension  << "abstract" << "boolean"   << "byte"         << "char"
              << "class"    << "const"     << "debugger"     << "double"
              << "enum"     << "export"    << "extends"      << "final"
              << "float"    << "goto"      << "implements"   << "import"
              << "int"      << "interface" << "long"         << "native"
              << "package"  << "private"   << "protected"    << "public"
              << "short"    << "static"    << "super"        << "synchronized"
              << "throws"   << "transient" << "volatile"
              ;
}
Exemple #4
0
void dspRunningAvailability::sHandleResort()
{
  for (int i = 0; i < _availability->topLevelItemCount(); i++)
  {
    XTreeWidgetItem *item = _availability->topLevelItem(i);
    if (item->data(RUNNINGAVAIL_COL, Qt::DisplayRole).toDouble() < 0)
      item->setTextColor(RUNNINGAVAIL_COL, namedColor("error"));
    else if (item->data(RUNNINGAVAIL_COL, Qt::DisplayRole).toDouble() < _reorderLevel->toDouble())
      item->setTextColor(RUNNINGAVAIL_COL, namedColor("warning"));
    else
      item->setTextColor(RUNNINGAVAIL_COL, namedColor(""));
  }
}
void dspRunningAvailability::sHandleResort()
{
  for (int i = 0; i < list()->topLevelItemCount(); i++)
  {
    XTreeWidgetItem *item = list()->topLevelItem(i);
    if (item->data(list()->column("runningavail"), Qt::DisplayRole).toDouble() < 0)
      item->setTextColor(list()->column("runningavail"), namedColor("error"));
    else if (item->data(list()->column("runningavail"), Qt::DisplayRole).toDouble() < _reorderLevel->toDouble())
      item->setTextColor(list()->column("runningavail"), namedColor("warning"));
    else
      item->setTextColor(list()->column("runningavail"), namedColor(""));
  }
}
Exemple #6
0
void glSeries::sFillList()
{
  q.prepare( "SELECT glseries_id, (formatGLAccount(accnt_id) || '-' || accnt_descrip) AS account,"
             "       CASE WHEN (glseries_amount < 0) THEN (glseries_amount * -1)"
             "            ELSE 0"
             "       END AS debit,"
             "       CASE WHEN (glseries_amount > 0) THEN glseries_amount"
             "            ELSE 0"
             "       END AS credit,"
             "       'curr' AS debit_xtnumericrole,"
             "       'curr' AS credit_xtnumericrole "
             "FROM glseries, accnt "
             "WHERE ( (glseries_accnt_id=accnt_id)"
             " AND (glseries_sequence=:glseries_sequence) );" );
  q.bindValue(":glseries_sequence", _glsequence);
  q.exec();
  if (q.lastError().type() != QSqlError::NoError)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }
  _glseries->populate(q);
  if (q.lastError().type() != QSqlError::NoError)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }

  q.prepare("SELECT SUM(CASE WHEN (glseries_amount < 0) THEN (glseries_amount * -1)"
            "                              ELSE 0"
            "                         END) AS debit,"
            "       SUM(CASE WHEN (glseries_amount > 0) THEN glseries_amount"
            "                              ELSE 0"
            "                         END ) AS credit,"
            "       SUM(glseries_amount) AS diff,"
            "       (SUM(glseries_amount) <> 0) AS oob "
            "FROM glseries "
            "WHERE (glseries_sequence=:glseries_sequence);" );
  q.bindValue(":glseries_sequence", _glsequence);
  q.exec();
  if (q.first())
  {
    _debits->setDouble(q.value("debit").toDouble());
    _credits->setDouble(q.value("credit").toDouble());
    _diff->setDouble(q.value("diff").toDouble());

    QString stylesheet;
    if (q.value("oob").toBool())
        stylesheet = QString("* { color: %1; }").arg(namedColor("error").name());

    _debits->setStyleSheet(stylesheet);
    _credits->setStyleSheet(stylesheet);
    _diff->setStyleSheet(stylesheet);
  }
  else if (q.lastError().type() != QSqlError::NoError)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }
}
void XLabel::setTextColor(const QString &pColorName)
{
  QColor c = namedColor(pColorName);
  QPalette p = palette();
  p.setColor(foregroundRole(), c);
  setPalette(p);
}
Exemple #8
0
void voucher::sPopulateBalanceDue()
{
  _balance->setLocalValue(_amountToDistribute->localValue() - _amountDistributed->localValue());
  if (_balance->isZero())
    _balance->setPaletteForegroundColor(QColor("black"));
  else
    _balance->setPaletteForegroundColor(namedColor("error"));
}
void applyARDiscount::populate()
{
  XSqlQuery applypopulate;
  applypopulate.prepare("SELECT cust_name, aropen_docnumber, aropen_docdate,"
            "       CASE WHEN (aropen_doctype='I') THEN :invoice "
            "            WHEN (aropen_doctype='C') THEN :creditmemo "
            "            ELSE aropen_doctype "
            "       END AS f_doctype, "
            "       (terms_code || '-' || terms_descrip) AS f_terms, "
            "       determineDiscountDate(terms_id, aropen_docdate) AS discdate, "
            "       terms_discprcnt, applied, "
            "       aropen_amount,aropen_curr_id, "
            "       (determineDiscountDate(terms_id, aropen_docdate) < CURRENT_DATE) AS past "
            "FROM aropen LEFT OUTER JOIN terms ON (aropen_terms_id=terms_id) "
            "            JOIN custinfo ON (cust_id=aropen_cust_id), "
            "     ( SELECT COALESCE(SUM(arapply_applied),0) AS applied "
            "       FROM arapply, aropen "
            "       WHERE ((arapply_target_aropen_id=:aropen_id) "
            "         AND  (arapply_source_aropen_id=aropen_id) "
            "         AND  (aropen_discount)) ) AS data "
            "WHERE (aropen_id=:aropen_id);");
  applypopulate.bindValue(":aropen_id", _aropenid);
  applypopulate.bindValue(":invoice", tr("Invoice"));
  applypopulate.bindValue(":creditmemo", tr("Credit Memo"));
  applypopulate.exec();

  if(applypopulate.first())
  {
    _cust->setText(applypopulate.value("cust_name").toString());

    _doctype->setText(applypopulate.value("f_doctype").toString());
    _docnum->setText(applypopulate.value("aropen_docnumber").toString());
    _docdate->setDate(applypopulate.value("aropen_docdate").toDate());

    _terms->setText(applypopulate.value("f_terms").toString());
    _discdate->setDate(applypopulate.value("discdate").toDate());

    if(applypopulate.value("past").toBool())
    {
      QPalette tmpPalette = _discdate->palette();
      tmpPalette.setColor(QPalette::HighlightedText, namedColor("error"));
      _discdate->setPalette(tmpPalette);
      _discdate->setForegroundRole(QPalette::HighlightedText);
      _discdateLit->setPalette(tmpPalette);
      _discdateLit->setForegroundRole(QPalette::HighlightedText);
    }

    _discprcnt->setDouble(applypopulate.value("terms_discprcnt").toDouble() * 100);

    _owed->setLocalValue(applypopulate.value("aropen_amount").toDouble());
    _applieddiscounts->setLocalValue(applypopulate.value("applied").toDouble());
  }

  else if (applypopulate.lastError().type() != QSqlError::NoError)
    systemError(this, applypopulate.lastError().databaseText(), __FILE__, __LINE__);
}
void standardJournal::sFillList()
{
  q.prepare( "SELECT stdjrnlitem_id,"
             "       CASE WHEN(accnt_id IS NOT NULL) THEN (formatGLAccount(accnt_id) || '-' || accnt_descrip)"
             "            ELSE 'ERROR - NO ACCOUNT SPECIFIED'"
             "       END AS account,"
             "       firstLine(stdjrnlitem_notes) AS note,"
             "       CASE WHEN (stdjrnlitem_amount < 0) THEN (stdjrnlitem_amount * -1)"
             "            ELSE NULL"
             "       END AS debit,"
             "       CASE WHEN (stdjrnlitem_amount > 0) THEN (stdjrnlitem_amount)"
             "            ELSE NULL"
             "       END AS credit,"
             "       'curr' AS debit_xtnumericrole,"
             "       'curr' AS credit_xtnumericrole,"
             "       '' AS debit_xtnullrole,"
             "       '' AS credit_xtnullrole "
             "  FROM stdjrnlitem LEFT OUTER JOIN accnt ON (stdjrnlitem_accnt_id=accnt_id)"
             " WHERE (stdjrnlitem_stdjrnl_id=:stdjrnl_id) "
             " ORDER BY accnt_number, accnt_profit, accnt_sub;" );
  q.bindValue(":stdjrnl_id", _stdjrnlid);
  q.exec();
  _stdjrnlitem->populate(q);

  q.prepare( "SELECT SUM( CASE WHEN (stdjrnlitem_amount < 0) THEN (stdjrnlitem_amount * -1)"
             "                 ELSE 0"
             "            END ) AS debit,"
             "       SUM( CASE WHEN (stdjrnlitem_amount > 0) THEN stdjrnlitem_amount"
             "                 ELSE 0"
             "            END ) AS credit,"
             "       (SUM(stdjrnlitem_amount) <> 0) AS oob "
             "FROM stdjrnlitem "
             "WHERE (stdjrnlitem_stdjrnl_id=:stdjrnl_id);" );
  q.bindValue(":stdjrnl_id", _stdjrnlid);
  q.exec();
  if (q.first())
  {
    _debits->setDouble(q.value("debit").toDouble());
    _credits->setDouble(q.value("credit").toDouble());

    QString stylesheet;
    if (q.value("oob").toBool())
      stylesheet = QString("* { color: %1; }").arg(namedColor("error").name());
    _debits->setStyleSheet(stylesheet);
    _credits->setStyleSheet(stylesheet);
  }
  else
    systemError(this, tr("A System Error occurred at %1::%2.")
                      .arg(__FILE__)
                      .arg(__LINE__) );
}
void JSHighlighter::init()
{
  _commentColor   = namedColor("altemphasis");
  _errorColor     = namedColor("error");
  _extensionColor = namedColor("warning");
  _keywordColor   = namedColor("emphasis");
  _literalColor   = namedColor("future");

  if (_keyword.isEmpty())
    _keyword  << "break"     << "case"    << "catch"          << "continue"
              << "default"   << "delete"  << "do"             << "else"
              << "false"     << "finally" << "for"            << "function"
              << "if"        << "in"      << "instanceof"     << "new"
              << "null"      << "return"  << "switch"         << "this"
              << "throw"     << "true"    << "try"            << "typeof"
              << "var"       << "void"    << "while"          << "with"
              ;

  if (_extension.isEmpty())
    _extension  << "abstract" << "boolean"   << "byte"         << "char"
                << "class"    << "const"     << "debugger"     << "double"
                << "enum"     << "export"    << "extends"      << "final"
                << "float"    << "goto"      << "implements"   << "import"
                << "int"      << "interface" << "long"         << "native"
                << "package"  << "private"   << "protected"    << "public"
                << "short"    << "static"    << "super"        << "synchronized"
                << "throws"   << "transient" << "volatile"
                ;
  
  _kwtest.setPattern("^(" + _keyword.join("|") + ")((?=\\W)|$)");
  _extest.setPattern("^(" + _extension.join("|") + ")((?=\\W)|$)");
  _numerictest.setPattern("^(0[xX][0-9a-fA-F]+)|(-?[0-9]+(\\.[0-9]+)?)");
  _wordtest.setPattern("^\\w+");
  _quotetest.setPattern("^\"[^\"]*\"|'[^']*'");
  _regexptest.setPattern("/[^/]*/[igm]?");

}
void itemListPrice::sUpdateMargins()
{
  if (_item->isValid())
  {
    double price = _listPrice->toDouble() / _cachedRatio;

    _extPrice->setDouble(price);

    QString stylesheet;

    if (_cachedStdCost > 0.0)
    {
      _stdMargin->setDouble((price - _cachedStdCost) / price * 100);

      if (_cachedStdCost > price)
        stylesheet = QString("* { color: %1; }").arg(namedColor("error").name());
    }
    else
      _stdMargin->setText("N/A");

    _stdMargin->setStyleSheet(stylesheet);

    stylesheet = "";
    if (_cachedActCost > 0.0)
    {
      _actMargin->setDouble((price - _cachedActCost) / price * 100);

      if (_cachedActCost > price)
        stylesheet = QString("* { color: %1; }").arg(namedColor("error").name());
    }
    else
      _actMargin->setText("N/A");

    _actMargin->setStyleSheet(stylesheet);
  }
}
void dspCountSlipsByWarehouse::sFillList()
{
  if (DEBUG)
    qDebug("dspCountSlipsByWarehouse::sFillList() about to populate _cntslip");

  display::sFillList();

  if (_numericSlips->isChecked() && list()->topLevelItemCount() > 1)
  {
    if (DEBUG)
      qDebug("dspCountSlipsByWarehouse::sFillList() looking for slip # gaps "
             "in %d items", list()->topLevelItemCount());
    XTreeWidgetItem *last =list()->topLevelItem(0);
    int slipNumber = last->rawValue("slipnumber").toInt();
    for (int i = 1; i < list()->topLevelItemCount(); i++)
    {
      XTreeWidgetItem *curr = list()->topLevelItem(i);
      if (DEBUG)
        qDebug("row %d has slipNumber %d and current %d",
               i, slipNumber, curr->rawValue("slipnumber").toInt());
      if (slipNumber == (curr->rawValue("slipnumber").toInt() - 1) || slipNumber == -1)
        slipNumber = curr->rawValue("slipnumber").toInt();
      else if (slipNumber >= 0)
      {
        if (slipNumber == curr->rawValue("slipnumber").toInt() - 2)
          curr = new XTreeWidgetItem( list(), last, -1,
                                    QVariant("----"), "----", "----", "----",
                                    tr("Missing Slip #%1").arg(slipNumber + 1),
                                    "----", "----", "----" );
        else
          curr = new XTreeWidgetItem( list(), last, -1,
                                    QVariant("----"), "----", "----", "----",
                                    tr("Missing Slips #%1 to #%2").arg(slipNumber + 1).arg(curr->rawValue("slipnumber").toInt() - 1),
                                    "----", "----", "----" );

        curr->setTextColor(namedColor("error"));
        slipNumber = -1;
        //i++; // 'cause we just added an item!
      }
      last = curr;
    }
  }
}
Exemple #14
0
void UsernameLineEdit::setId(const int pId)
{
  QString sql("SELECT usr_username"
              "  FROM usr"
              " WHERE ((usr_id=:id)");

  if(UsersActive == _type)
    sql += " AND (usr_active)";
  else if(UsersInactive == _type)
    sql += " AND (NOT usr_active)";

  sql += " );";

  XSqlQuery query;
  query.prepare(sql);
  query.bindValue(":id", pId);
  query.exec();
  if(query.first())
  {
    _id = pId;
    _valid = true;
    _username = query.value("usr_username").toString();
    setStyleSheet("");
    setText(_username);
  }
  else
  {
    _id = -1;
    _valid = false;
    setStyleSheet(QString("QLineEdit { color: %1 } ").arg(namedColor("error").name()));
  }

  emit newId(_id);
  emit valid(_valid);

  _parsed = true;
}
void applyDiscount::populate()
{
  q.prepare("SELECT (vend_number|| '-' || vend_name) as f_vend,"
            "       CASE WHEN (apopen_doctype='V') THEN :voucher"
            "            WHEN (apopen_doctype='D') THEN :debitmemo"
            "            ELSE apopen_doctype"
            "       END AS f_doctype,"
            "       apopen_docnumber,"
            "       apopen_docdate, "
            "       (terms_code || '-' || terms_descrip) AS f_terms,"
            "       determineDiscountDate(apopen_terms_id, apopen_docdate) AS discdate,"
            "       terms_discprcnt,"
            "       apopen_amount, apopen_discountable_amount, apopen_curr_id, applied, "
            "       noNeg(apopen_discountable_amount *"
            "             CASE WHEN (CURRENT_DATE <= determineDiscountDate(terms_id, apopen_docdate)) THEN terms_discprcnt"
            "             ELSE 0.0 END - applied) AS amount,"
            "       (determineDiscountDate(terms_id, apopen_docdate) < CURRENT_DATE) AS past"
            "  FROM apopen LEFT OUTER JOIN terms ON (apopen_terms_id=terms_id)"
            "              JOIN vend ON (apopen_vend_id=vend_id), "
            "       (SELECT COALESCE(SUM(apapply_amount),0) AS applied"
            "          FROM apapply, apopen"
            "         WHERE ((apapply_target_apopen_id=:apopen_id)"
            "           AND  (apapply_source_apopen_id=apopen_id)"
            "           AND  (apopen_discount)) ) AS data"
            " WHERE (apopen_id=:apopen_id); ");
  q.bindValue(":apopen_id", _apopenid);
  q.bindValue(":voucher", tr("Voucher"));
  q.bindValue(":debitmemo", tr("Debit Memo"));
  q.exec();

  if(q.first())
  {
    _vend->setText(q.value("f_vend").toString());

    _doctype->setText(q.value("f_doctype").toString());
    _docnum->setText(q.value("apopen_docnumber").toString());
    _docdate->setDate(q.value("apopen_docdate").toDate());

    _terms->setText(q.value("f_terms").toString());
    _discdate->setDate(q.value("discdate").toDate());

    if(q.value("past").toBool())
    {
      QPalette tmpPalette = _discdate->palette();
      tmpPalette.setColor(QPalette::HighlightedText, namedColor("error"));
      _discdate->setPalette(tmpPalette);
      _discdate->setForegroundRole(QPalette::HighlightedText);  // why doesn't the date turn ERROR?
      _discdateLit->setPalette(tmpPalette);
      _discdateLit->setForegroundRole(QPalette::HighlightedText);
    }

    _discprcnt->setDouble(q.value("terms_discprcnt").toDouble() * 100);

    _owed->setLocalValue(q.value("apopen_amount").toDouble());
    _discountableOwed->setLocalValue(q.value("apopen_discountable_amount").toDouble());
    _applieddiscounts->setLocalValue(q.value("applied").toDouble());

    _amount->set(q.value("amount").toDouble(),
		 q.value("apopen_curr_id").toInt(), 
		 q.value("apopen_docdate").toDate(), false);
  }
  else if (q.lastError().type() != QSqlError::NoError)
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
}
Exemple #16
0
/* 
   Note that the SELECTs here are UNIONs of the gltrans table (in the base
   currency) and the bankadj table (in the bank account's currency).
*/
void reconcileBankaccount::populate()
{
  qApp->setOverrideCursor(QCursor(Qt::WaitCursor));

  double begBal = _openBal->localValue();
  double endBal = _endBal->localValue();

  int currid = -1;

  // fill receipts list
  currid = _receipts->id();
  _receipts->clear();
  q.prepare("SELECT gltrans_id AS id, 1 AS altid,"
            "       jrnluse_use AS use, gltrans_journalnumber AS jrnlnum,"
            "       COALESCE(date(jrnluse_date), gltrans_date) AS f_jrnldate,"
            "       COALESCE(bankrecitem_cleared, FALSE) AS cleared,"
            "       gltrans_date AS f_date,"
            "       gltrans_docnumber AS docnumber,"
            "       gltrans_notes AS notes,"
            "       currToLocal(bankaccnt_curr_id, gltrans_amount, gltrans_date) * -1 AS amount,"
            "       COALESCE(date(jrnluse_date), gltrans_date) AS jrnldate,"
            "       gltrans_date AS sortdate "
            "  FROM (bankaccnt CROSS JOIN gltrans) LEFT OUTER JOIN bankrecitem "
            "    ON ((bankrecitem_source='GL') AND (bankrecitem_source_id=gltrans_id)"
            "        AND (bankrecitem_bankrec_id=:bankrecid)) "
            "       LEFT OUTER JOIN jrnluse ON (jrnluse_number=gltrans_journalnumber AND jrnluse_use='C/R')"
            " WHERE ((gltrans_accnt_id=bankaccnt_accnt_id)"
            "   AND (NOT gltrans_deleted) "
            "   AND (NOT gltrans_rec)"
            "   AND (gltrans_amount < 0)"
            "   AND (bankaccnt_id=:bankaccntid) ) "
            " UNION ALL "
            "SELECT bankadj_id AS id, 2 AS altid,"
            "       '' AS use, NULL AS jrnlnum, bankadj_date AS f_jrnldate,"
            "       COALESCE(bankrecitem_cleared, FALSE) AS cleared,"
            "       bankadj_date AS f_date,"
            "       bankadj_docnumber AS docnumber,"
            "       bankadjtype_name AS notes,"
            "       (CASE WHEN(bankadjtype_iscredit=true) THEN (bankadj_amount * -1) ELSE bankadj_amount END) AS amount,"
            "       bankadj_date AS jrnldate,"
            "       bankadj_date AS sortdate "
            "  FROM (bankadjtype CROSS JOIN bankadj) "
            "               LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='AD') "
            "                 AND (bankrecitem_source_id=bankadj_id) "
            "                 AND (bankrecitem_bankrec_id=:bankrecid)) "
            " WHERE ( (((bankadjtype_iscredit=false) AND (bankadj_amount > 0)) OR ((bankadjtype_iscredit=true) AND (bankadj_amount < 0))) "
            "   AND (bankadj_bankadjtype_id=bankadjtype_id) "
            "   AND (NOT bankadj_posted) "
            "   AND (bankadj_bankaccnt_id=:bankaccntid) ) "
            "ORDER BY jrnldate, jrnlnum, sortdate; ");
  q.bindValue(":bankaccntid", _bankaccnt->id());
  q.bindValue(":bankrecid", _bankrecid);
  q.exec();
  if (q.lastError().type() != QSqlError::NoError)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }

  int jrnlnum = 0;
  XTreeWidgetItem * parent = 0;
  XTreeWidgetItem * lastChild = 0;
  XTreeWidgetItem * last = 0;
  bool cleared = TRUE;
  double amount = 0.0;
  bool amountNull = true;
  while (q.next())
  {
    if(q.value("use").toString() == "C/R")
    {
      if(q.value("jrnlnum").toInt() != jrnlnum || (0 == parent))
      {
        if(parent != 0)
        {
          parent->setText(0, (cleared ? tr("Yes") : tr("No")));
          parent->setText(4, amountNull ? tr("?????") : formatMoney(amount));
        }
        jrnlnum = q.value("jrnlnum").toInt();
        last = new XTreeWidgetItem( _receipts, last,
          jrnlnum, 3, "", formatDate(q.value("f_jrnldate").toDate()), q.value("jrnlnum"));
        parent = last;
        cleared = true;
        amount = 0.0;
	amountNull = true;
        lastChild = 0;
      }
      cleared = (cleared && q.value("cleared").toBool());
      amount += q.value("amount").toDouble();
      amountNull = q.value("amount").isNull();
      
      lastChild = new XTreeWidgetItem( parent, lastChild,
        q.value("id").toInt(), q.value("altid").toInt(),
        (q.value("cleared").toBool() ? tr("Yes") : tr("No")),
        formatDate(q.value("f_date").toDate()), q.value("docnumber"),
        q.value("notes"),
	q.value("amount").isNull() ? tr("?????") : formatMoney(q.value("amount").toDouble()) );
    }
    else
    {
      if(parent != 0)
      {
        parent->setText(0, (cleared ? tr("Yes") : tr("No")));
        parent->setText(4, formatMoney(amount));
      }
      parent = 0;
      cleared = true;
      amount = 0.0;
      amountNull = true;
      lastChild = 0;
      last = new XTreeWidgetItem( _receipts, last,
        q.value("id").toInt(), q.value("altid").toInt(),
        (q.value("cleared").toBool() ? tr("Yes") : tr("No")),
        formatDate(q.value("f_date").toDate()), q.value("docnumber"),
        q.value("notes"),
	q.value("amount").isNull() ? tr("?????") : formatMoney(q.value("amount").toDouble()) );
    }
  }
  if(parent != 0)
  {
    parent->setText(0, (cleared ? tr("Yes") : tr("No")));
    parent->setText(4, amountNull ? tr("?????") : formatMoney(amount));
  }
  if(currid != -1)
    _receipts->setCurrentItem(_receipts->topLevelItem(currid));
  if(_receipts->currentItem())
    _receipts->scrollToItem(_receipts->currentItem());


  // fill receipts cleared value
  q.prepare("SELECT COALESCE(SUM(amount),0.0) AS cleared_amount"
            "  FROM ( SELECT currToLocal(bankaccnt_curr_id, gltrans_amount * -1, gltrans_date) AS amount"
            "           FROM bankaccnt, gltrans, bankrecitem"
            "          WHERE ((gltrans_accnt_id=bankaccnt_accnt_id)"
            "            AND (bankrecitem_source='GL')"
            "            AND (bankrecitem_source_id=gltrans_id)"
            "            AND (bankrecitem_bankrec_id=:bankrecid)"
            "            AND (bankrecitem_cleared)"
            "            AND (NOT gltrans_deleted)"
            "            AND (NOT gltrans_rec)"
            "            AND (gltrans_amount < 0)"
            "            AND (bankaccnt_id=:bankaccntid) ) "
            "          UNION ALL"
            "         SELECT CASE WHEN(bankadjtype_iscredit=true) THEN (bankadj_amount * -1) ELSE bankadj_amount END AS amount"
            "           FROM bankrecitem, bankadj, bankadjtype "
            "          WHERE ( (bankrecitem_source='AD')"
            "            AND (bankrecitem_source_id=bankadj_id)"
            "            AND (bankrecitem_bankrec_id=:bankrecid)"
            "            AND (bankrecitem_cleared)"
            "            AND (bankadj_bankadjtype_id=bankadjtype_id)"
            "            AND (NOT bankadj_posted)"
            "            AND (((bankadjtype_iscredit=false) AND (bankadj_amount > 0)) OR (bankadjtype_iscredit=true AND (bankadj_amount < 0))) "
            "            AND (bankadj_bankaccnt_id=:bankaccntid) ) ) AS data;");
  q.bindValue(":bankaccntid", _bankaccnt->id());
  q.bindValue(":bankrecid", _bankrecid);
  q.exec();
  if (q.first())
    _clearedReceipts->setDouble(q.value("cleared_amount").toDouble());
  else if (q.lastError().type() != QSqlError::NoError)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }

  // fill checks list
  currid = _checks->id();
  _checks->clear();
  q.prepare("SELECT gltrans_id AS id, 1 AS altid,"
            "       COALESCE(bankrecitem_cleared, FALSE) AS cleared,"
            "       gltrans_date AS transdate,"
            "       gltrans_docnumber AS docnumber,"
            "       gltrans_notes AS notes,"
            "       currToLocal(bankaccnt_curr_id, gltrans_amount, gltrans_date) AS amount,"
            "       gltrans_date AS sortdate, "
            "       'curr' AS amount_xtnumericrole "
            "  FROM (bankaccnt CROSS JOIN gltrans) LEFT OUTER JOIN bankrecitem "
            "    ON ((bankrecitem_source='GL') AND (bankrecitem_source_id=gltrans_id)"
            "        AND (bankrecitem_bankrec_id=:bankrecid)) "
            " WHERE ((gltrans_accnt_id=bankaccnt_accnt_id)"
            "   AND (NOT gltrans_deleted)"
            "   AND (NOT gltrans_rec)"
            "   AND (gltrans_amount > 0)"
            "   AND (bankaccnt_id=:bankaccntid) ) "
            " UNION ALL "
            "SELECT bankadj_id AS id, 2 AS altid,"
            "       COALESCE(bankrecitem_cleared, FALSE) AS cleared,"
            "       bankadj_date AS transdate,"
            "       bankadj_docnumber AS docnumber,"
            "       bankadjtype_name AS notes,"
            "       CASE WHEN(bankadjtype_iscredit=false) THEN (bankadj_amount * -1) ELSE bankadj_amount END AS amount,"
            "       bankadj_date AS sortdate, "
            "       'curr' AS amount_xtnumericrole "
            "  FROM (bankadjtype CROSS JOIN bankadj) "
            "               LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='AD') "
            "                 AND (bankrecitem_source_id=bankadj_id) "
            "                 AND (bankrecitem_bankrec_id=:bankrecid)) "
            " WHERE ( (((bankadjtype_iscredit=true) AND (bankadj_amount > 0)) OR ((bankadjtype_iscredit=false) AND (bankadj_amount < 0))) "
            "   AND (bankadj_bankadjtype_id=bankadjtype_id) "
            "   AND (NOT bankadj_posted) "
            "   AND (bankadj_bankaccnt_id=:bankaccntid) ) "
            "ORDER BY sortdate; ");
  q.bindValue(":bankaccntid", _bankaccnt->id());
  q.bindValue(":bankrecid", _bankrecid);
  q.exec();
  if (q.lastError().type() != QSqlError::NoError)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }
  _checks->populate(q, TRUE);

  if(currid != -1)
    _checks->setCurrentItem(_checks->topLevelItem(currid));
  if(_checks->currentItem())
    _checks->scrollToItem(_checks->currentItem());

  // fill checks cleared value
  q.prepare("SELECT COALESCE(SUM(amount),0.0) AS cleared_amount"
            "  FROM ( SELECT currToLocal(bankaccnt_curr_id, gltrans_amount, gltrans_date) AS amount"
            "           FROM bankaccnt, gltrans, bankrecitem"
            "          WHERE ((gltrans_accnt_id=bankaccnt_accnt_id)"
            "            AND (bankrecitem_source='GL')"
            "            AND (bankrecitem_source_id=gltrans_id)"
            "            AND (bankrecitem_bankrec_id=:bankrecid)"
            "            AND (bankrecitem_cleared)"
            "            AND (NOT gltrans_deleted)"
            "            AND (NOT gltrans_rec)"
            "            AND (gltrans_amount > 0)"
            "            AND (bankaccnt_id=:bankaccntid) ) "
            "          UNION ALL"
            "         SELECT CASE WHEN(bankadjtype_iscredit=false) THEN (bankadj_amount * -1) ELSE bankadj_amount END AS amount"
            "           FROM bankadj, bankadjtype, bankrecitem"
            "          WHERE ( (bankrecitem_source='AD')"
            "            AND (bankrecitem_source_id=bankadj_id)"
            "            AND (bankrecitem_bankrec_id=:bankrecid)"
            "            AND (bankrecitem_cleared)"
            "            AND (bankadj_bankadjtype_id=bankadjtype_id)"
            "            AND (NOT bankadj_posted)"
            "            AND (((bankadjtype_iscredit=true) AND (bankadj_amount > 0)) OR ((bankadjtype_iscredit=false) AND (bankadj_amount < 0)))"
            "            AND (bankadj_bankaccnt_id=:bankaccntid) ) ) AS data;");
  q.bindValue(":bankaccntid", _bankaccnt->id());
  q.bindValue(":bankrecid", _bankrecid);
  q.exec();
  if (q.first())
    _clearedChecks->setDouble(q.value("cleared_amount").toDouble());
  else if (q.lastError().type() != QSqlError::NoError)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }

  // calculate cleared balance
  q.prepare("SELECT (COALESCE(SUM(amount),0.0) + :begBal) AS cleared_amount,"
            "       :endBal AS end_amount,"
            "       (:endBal - (:begBal + (COALESCE(SUM(amount),0.0)))) AS diff_amount,"
            "       round(:endBal - (:begBal + COALESCE(SUM(amount),0.0)), 2) AS diff_value"
            "  FROM ( SELECT currToLocal(bankaccnt_curr_id, gltrans_amount * -1, gltrans_date) AS amount"
            "           FROM bankaccnt, gltrans, bankrecitem"
            "          WHERE ((gltrans_accnt_id=bankaccnt_accnt_id)"
            "            AND (bankrecitem_source='GL')"
            "            AND (bankrecitem_source_id=gltrans_id)"
            "            AND (bankrecitem_bankrec_id=:bankrecid)"
            "            AND (bankrecitem_cleared)"
            "            AND (NOT gltrans_rec)"
            "            AND (bankaccnt_id=:bankaccntid) ) "
            "          UNION ALL"
            "         SELECT CASE WHEN(bankadjtype_iscredit=true) THEN (bankadj_amount * -1) ELSE bankadj_amount END AS amount"
            "           FROM bankadj, bankadjtype, bankrecitem"
            "          WHERE ( (bankrecitem_source='AD')"
            "            AND (bankrecitem_source_id=bankadj_id)"
            "            AND (bankrecitem_bankrec_id=:bankrecid)"
            "            AND (bankrecitem_cleared)"
            "            AND (bankadj_bankadjtype_id=bankadjtype_id)"
            "            AND (NOT bankadj_posted)"
            "            AND (bankadj_bankaccnt_id=:bankaccntid) ) ) AS data;");
  q.bindValue(":bankaccntid", _bankaccnt->id());
  q.bindValue(":bankrecid", _bankrecid);
  q.bindValue(":endBal", endBal);
  q.bindValue(":begBal", begBal);
  q.bindValue(":curr_id",   _currency->id());
  q.bindValue(":effective", _startDate->date());
  q.bindValue(":expires",   _endDate->date());
  q.exec();
  bool enableRec = FALSE;
  if(q.first())
  {
    _clearBal->setDouble(q.value("cleared_amount").toDouble());
    _endBal2->setDouble(q.value("end_amount").toDouble());
    _diffBal->setDouble(q.value("diff_amount").toDouble());

    QString stylesheet;

    if(q.value("diff_value").toDouble() == 0.0)
    {
      if(_startDate->isValid() && _endDate->isValid())
        enableRec = TRUE;
    }
    else
      stylesheet = QString("* { color: %1; }").arg(namedColor("error").name());

    _diffBal->setStyleSheet(stylesheet);
  }
  else if (q.lastError().type() != QSqlError::NoError)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }
  //_reconcile->setEnabled(enableRec);

  qApp->restoreOverrideCursor();
}
/* 
   Note that the SELECTs here are UNIONs of the gltrans table (in the base
   currency), sltrans table (in the base currency) and the bankadj table
   (in the bank account's currency).
*/
void reconcileBankaccount::populate()
{
  qApp->setOverrideCursor(QCursor(Qt::WaitCursor));

  double begBal = _openBal->localValue();
  double endBal = _endBal->localValue();

  int currid = -1;

  ParameterList params;
  params.append("bankaccntid", _bankaccnt->id());
  params.append("bankrecid", _bankrecid);

  // fill receipts list
  currid = _receipts->id();
  _receipts->clear();
  MetaSQLQuery mrcp = mqlLoad("bankrec", "receipts");
  XSqlQuery rcp = mrcp.toQuery(params);
  if (rcp.lastError().type() != QSqlError::NoError)
  {
    systemError(this, rcp.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }

  int jrnlnum = 0;
  XTreeWidgetItem * parent = 0;
  XTreeWidgetItem * lastChild = 0;
  XTreeWidgetItem * last = 0;
  bool cleared = TRUE;
  double amount = 0.0;
  bool amountNull = true;
  while (rcp.next())
  {
    if(rcp.value("use").toString() == "C/R")
    {
      if(rcp.value("jrnlnum").toInt() != jrnlnum || (0 == parent))
      {
        if(parent != 0)
        {
          parent->setText(0, (cleared ? tr("Yes") : tr("No")));
          parent->setText(8, amountNull ? tr("?????") : formatMoney(amount));
        }
        jrnlnum = rcp.value("jrnlnum").toInt();
        last = new XTreeWidgetItem( _receipts, last,
          jrnlnum, 9, "", formatDate(rcp.value("f_jrnldate").toDate()), tr("JS"), rcp.value("jrnlnum"));
        parent = last;
        cleared = true;
        amount = 0.0;
	amountNull = true;
        lastChild = 0;
      }
      cleared = (cleared && rcp.value("cleared").toBool());
      amount += rcp.value("amount").toDouble();
      amountNull = rcp.value("amount").isNull();
      
      lastChild = new XTreeWidgetItem( parent, lastChild,
        rcp.value("id").toInt(), rcp.value("altid").toInt(),
        (rcp.value("cleared").toBool() ? tr("Yes") : tr("No")),
        formatDate(rcp.value("f_date").toDate()), rcp.value("doc_type"), rcp.value("docnumber"),
        rcp.value("notes"),
        rcp.value("doc_curr"),
        rcp.value("doc_exchrate").isNull() ? tr("?????") : formatUOMRatio(rcp.value("doc_exchrate").toDouble()),
        rcp.value("base_amount").isNull() ? tr("?????") : formatMoney(rcp.value("base_amount").toDouble()),
        rcp.value("amount").isNull() ? tr("?????") : formatMoney(rcp.value("amount").toDouble()) );
    }
    else
    {
      if(parent != 0)
      {
        parent->setText(0, (cleared ? tr("Yes") : tr("No")));
        parent->setText(8, formatMoney(amount));
      }
      parent = 0;
      cleared = true;
      amount = 0.0;
      amountNull = true;
      lastChild = 0;
      last = new XTreeWidgetItem( _receipts, last,
        rcp.value("id").toInt(), rcp.value("altid").toInt(),
        (rcp.value("cleared").toBool() ? tr("Yes") : tr("No")),
        formatDate(rcp.value("f_date").toDate()), rcp.value("doc_type"), rcp.value("docnumber"),
        rcp.value("notes"),
        rcp.value("doc_curr"),
        rcp.value("doc_exchrate").isNull() ? tr("?????") : formatUOMRatio(rcp.value("doc_exchrate").toDouble()),
        rcp.value("base_amount").isNull() ? tr("?????") : formatMoney(rcp.value("base_amount").toDouble()),
        rcp.value("amount").isNull() ? tr("?????") : formatMoney(rcp.value("amount").toDouble()) );
    }
  }
  if(parent != 0)
  {
    parent->setText(0, (cleared ? tr("Yes") : tr("No")));
    parent->setText(8, amountNull ? tr("?????") : formatMoney(amount));
  }

  if(currid != -1)
    _receipts->setCurrentItem(_receipts->topLevelItem(currid));
  if(_receipts->currentItem())
    _receipts->scrollToItem(_receipts->currentItem());

  // fill checks list
  currid = _checks->id();
  _checks->clear();
  MetaSQLQuery mchk = mqlLoad("bankrec", "checks");
  XSqlQuery chk = mchk.toQuery(params);
  if (chk.lastError().type() != QSqlError::NoError)
  {
    systemError(this, chk.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }
  _checks->populate(chk, TRUE);

  if(currid != -1)
    _checks->setCurrentItem(_checks->topLevelItem(currid));
  if(_checks->currentItem())
    _checks->scrollToItem(_checks->currentItem());

  params.append("summary", true);

  // fill receipts cleared value
  rcp = mrcp.toQuery(params);
  if (rcp.first())
    _clearedReceipts->setDouble(rcp.value("cleared_amount").toDouble());
  else if (rcp.lastError().type() != QSqlError::NoError)
  {
    systemError(this, rcp.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }

  // fill checks cleared value
  chk = mchk.toQuery(params);
  if (chk.first())
    _clearedChecks->setDouble(chk.value("cleared_amount").toDouble());
  else if (chk.lastError().type() != QSqlError::NoError)
  {
    systemError(this, chk.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }

  // calculate cleared balance
  MetaSQLQuery mbal = mqlLoad("bankrec", "clearedbalance");
  params.append("endBal", endBal);
  params.append("begBal", begBal);
  params.append("curr_id",   _currency->id());
  params.append("effective", _startDate->date());
  params.append("expires",   _endDate->date());
  XSqlQuery bal = mbal.toQuery(params);
  bool enableRec = FALSE;
  if(bal.first())
  {
    _clearBal->setDouble(bal.value("cleared_amount").toDouble());
    _endBal2->setDouble(bal.value("end_amount").toDouble());
    _diffBal->setDouble(bal.value("diff_amount").toDouble());

    QString stylesheet;

    if(bal.value("diff_value").toDouble() == 0.0)
    {
      if(_startDate->isValid() && _endDate->isValid())
        enableRec = TRUE;
    }
    else
      stylesheet = QString("* { color: %1; }").arg(namedColor("error").name());

    _diffBal->setStyleSheet(stylesheet);
  }
  else if (bal.lastError().type() != QSqlError::NoError)
  {
    systemError(this, bal.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }
  //_reconcile->setEnabled(enableRec);

  qApp->restoreOverrideCursor();
}
Exemple #18
0
void XTreeView::setForegroundColor(int row, int col, QString color)
{
  _model->setData(_model->index(row,col), namedColor(color), Qt::ForegroundRole);
}
Exemple #19
0
void dspCountSlipsByWarehouse::sFillList()
{
  QString sql("SELECT cntslip_id, ");

  if (_numericSlips->isChecked())
    sql += "toNumeric(cntslip_number, 0) AS slipnumber,"
           "cntslip_number AS slipnumber_qtdisplayrole," ;
  else
    sql += "cntslip_number AS slipnumber, ";

  sql += " invcnt_tagnumber, warehous_code,"
         " item_number, (item_descrip1 || ' ' || item_descrip2) AS descrip,"
         " cntslip_entered, getUsername(cntslip_user_id) AS user,"
         " cntslip_qty, 'qty' AS cntslip_qty_xtnumericrole,"
         " cntslip_posted "
         "FROM cntslip, invcnt, itemsite, item, warehous "
         "WHERE ((cntslip_cnttag_id=invcnt_id)"
         " AND (invcnt_itemsite_id=itemsite_id)"
         " AND (itemsite_item_id=item_id)"
         " AND (itemsite_warehous_id=warehous_id)"
         " AND (cntslip_entered BETWEEN :startDate AND :endDate)";

  if (!_showUnposted->isChecked())
    sql += " AND (cntslip_posted)";

  if (_warehouse->isSelected())
    sql += " AND (itemsite_warehous_id=:warehous_id)";

  sql += ") "
         "ORDER BY cntslip_number";

  if (DEBUG)
    qDebug("dspCountSlipsByWarehouse::sFillList() about to populate _cntslip");

  q.prepare(sql);
  _dates->bindValue(q);
  _warehouse->bindValue(q);
  q.exec();
  _cntslip->populate(q);
  if (q.lastError().type() != QSqlError::NoError)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }

  if (_numericSlips->isChecked() && _cntslip->topLevelItemCount() > 1)
  {
    if (DEBUG)
      qDebug("dspCountSlipsByWarehouse::sFillList() looking for slip # gaps "
             "in %d items", _cntslip->topLevelItemCount());
    XTreeWidgetItem *last =_cntslip->topLevelItem(0);
    int slipNumber = last->rawValue("slipnumber").toInt();
    for (int i = 1; i < _cntslip->topLevelItemCount(); i++)
    {
      XTreeWidgetItem *curr = _cntslip->topLevelItem(i);
      if (DEBUG)
        qDebug("row %d has slipNumber %d and current %d",
               i, slipNumber, curr->rawValue("slipnumber").toInt());
      if (slipNumber == (curr->rawValue("slipnumber").toInt() - 1) || slipNumber == -1)
        slipNumber = curr->rawValue("slipnumber").toInt();
      else if (slipNumber >= 0)
      {
        if (slipNumber == curr->rawValue("slipnumber").toInt() - 2)
          curr = new XTreeWidgetItem( _cntslip, last, -1,
                                    QVariant("----"), "----", "----", "----",
                                    tr("Missing Slip #%1").arg(slipNumber + 1),
                                    "----", "----", "----" );
        else
          curr = new XTreeWidgetItem( _cntslip, last, -1,
                                    QVariant("----"), "----", "----", "----",
                                    tr("Missing Slips #%1 to #%2").arg(slipNumber + 1).arg(curr->rawValue("slipnumber").toInt() - 1),
                                    "----", "----", "----" );

        curr->setTextColor(namedColor("error"));
        slipNumber = -1;
        //i++; // 'cause we just added an item!
      }
      last = curr;
    }
  }
}
Exemple #20
0
void bbom::sFillList(int pItemid, bool)
{
  if (_item->isValid() && (pItemid == _item->id()))
  {
    QString sql( "SELECT bbomitem.*, item_number,"
                 "       (item_descrip1 || ' ' || item_descrip2) AS descrip,"
                 "       CASE WHEN (item_type='C') THEN :coProduct"
                 "            WHEN (item_type='Y') THEN :byProduct"
                 "            ELSE :error"
                 "       END AS type,"
                 "       uom_name,"
		 "       'qty' AS bbomitem_qtyper_xtnumericrole,"
		 "       CASE WHEN (COALESCE(bbomitem_effective, startoftime()) = startoftime()) THEN :always"
		 "       END AS bbomitem_effective_qtdisplayrole,"
		 "       CASE WHEN (COALESCE(bbomitem_expires, endoftime()) = endoftime()) THEN :never"
		 "       END AS bbomitem_expires_qtdisplayrole,"
                 "       CASE WHEN (item_type='Y') THEN :na"
                 "       END AS bbomitem_costabsorb_qtdisplayrole,"
		 "       'scrap' AS bbomitem_costabsorb_xtnumericrole "
                 "FROM bbomitem, item, uom "
                 "WHERE ( (bbomitem_item_id=item_id)"
                 " AND (item_inv_uom_id=uom_id)"
                 " AND (bbomitem_parent_item_id=:item_id)" );

    if (!_showExpired->isChecked())
      sql += " AND (bbomitem_expires > CURRENT_DATE)";

    if (!_showFuture->isChecked())
      sql += " AND (bbomitem_effective <= CURRENT_DATE)";

    sql += ") "
           "ORDER BY bbomitem_seqnumber, bbomitem_effective";

    q.prepare(sql);
    q.bindValue(":coProduct", tr("Co-Product"));
    q.bindValue(":byProduct", tr("By-Product"));
    q.bindValue(":always",    tr("Always"));
    q.bindValue(":never",     tr("Never"));
    q.bindValue(":error",     tr("Error"));
    q.bindValue(":na",        tr("N/A"));
    q.bindValue(":item_id",   _item->id());
    q.exec();
    _bbomitem->populate(q);
    if (q.lastError().type() != QSqlError::None)
    {
      systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
      return;
    }

    q.prepare( "SELECT SUM(bbomitem_costabsorb) * 100 AS absorb "
               "FROM bbomitem "
               "WHERE (bbomitem_parent_item_id=:item_id);" );
    q.bindValue(":item_id", _item->id());
    q.exec();
    if (q.first())
    {
      _costsAbsorbed->setDouble(q.value("absorb").toDouble());

      if (q.value("absorb").toDouble() == 1.0)
        _costsAbsorbed->setPaletteForegroundColor(QColor("black"));
      else
        _costsAbsorbed->setPaletteForegroundColor(namedColor("error"));
    }
    else if (q.lastError().type() != QSqlError::None)
    {
      systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
      return;
    }
  }
  else if (!_item->isValid())
    _bbomitem->clear();
}
void transferOrderItem::sDetermineAvailability()
{
  if(  (_item->id()==_availabilityLastItemid)
    && (_warehouse->id()==_availabilityLastWarehousid)
    && (_scheduledDate->date()==_availabilityLastSchedDate)
    && (_showAvailability->isChecked()==_availabilityLastShow)
    && (_showIndented->isChecked()==_availabilityLastShowIndent)
    && (_qtyOrdered->toDouble()==_availabilityQtyOrdered) )
    return;

  _availabilityLastItemid	= _item->id();
  _availabilityLastWarehousid	= _warehouse->id();
  _availabilityLastSchedDate	= _scheduledDate->date();
  _availabilityLastShow		= _showAvailability->isChecked();
  _availabilityLastShowIndent	= _showIndented->isChecked();
  _availabilityQtyOrdered	= _qtyOrdered->toDouble();

  _availability->clear();

  if ((_item->isValid()) && (_scheduledDate->isValid()) && (_showAvailability->isChecked()) )
  {
    XSqlQuery availability;
    availability.prepare( "SELECT itemsite_id,"
                          "       qoh,"
                          "       allocated,"
                          "       noNeg(qoh - allocated) AS unallocated,"
                          "       ordered,"
                          "       (qoh - allocated + ordered) AS available "
                          "FROM ( SELECT itemsite_id, itemsite_qtyonhand AS qoh,"
                          "              qtyAllocated(itemsite_id, DATE(:date)) AS allocated,"
                          "              qtyOrdered(itemsite_id, DATE(:date)) AS ordered "
                          "       FROM itemsite, item "
                          "       WHERE ((itemsite_item_id=item_id)"
                          "        AND (item_id=:item_id)"
                          "        AND (itemsite_warehous_id=:warehous_id)) ) AS data;" );
    availability.bindValue(":date", _scheduledDate->date());
    availability.bindValue(":item_id", _item->id());
    availability.bindValue(":warehous_id", _warehouse->id());
    availability.exec();
    if (availability.first())
    {
      _onHand->setText(availability.value("qoh").toString());
      _allocated->setText(availability.value("allocated").toString());
      _unallocated->setText(availability.value("unallocated").toString());
      _onOrder->setText(availability.value("ordered").toString());
      _available->setText(availability.value("available").toString());

      QString stylesheet;
      if (availability.value("available").toDouble() < _qtyOrdered->toDouble())
        stylesheet = QString("* { color: %1; }").arg(namedColor("error").name());
      _available->setStyleSheet(stylesheet);

      if ( (_item->itemType() == "M") )
      {
        if(_showIndented->isChecked())
        {
          availability.prepare("SELECT indentedBOM(:item_id) AS bomwork_set_id;");
          availability.bindValue(":item_id", _item->id());
          availability.exec();
          if(availability.first())
          {
            int _worksetid = availability.value("bomwork_set_id").toInt();
	    if (_worksetid < 0)
	    {
	      systemError(this, storedProcErrorLookup("indentedBOM", _worksetid),
			  __FILE__, __LINE__);
	      return;
	    }
            availability.prepare("SELECT bomwork_id, *, "
                                 "       bomwork_seqnumber AS bomitem_seqnumber,"
                                 "       totalalloc + pendalloc AS totalalloc,"
                                 "       (qoh + ordered - (totalalloc + pendalloc)) AS totalavail,"
                                 "       bomwork_level - 1 AS xtindentrole,"
                                 "       'qty' AS totalalloc_xtnumericrole,"
                                 "       'qty' AS totalavail_xtnumericrole,"
                                 "       'qty' AS pendalloc_xtnumericrole,"
                                 "       'qty' AS ordered_xtnumericrole,"
                                 "       'qty' AS qoh_xtnumericrole,"
                                 "       CASE WHEN (qoh < pendalloc) THEN 'error'"
                                 "       END AS qoh_qtforegroundrole,"
                                 "       CASE WHEN (qoh + ordered - (totalalloc + pendalloc) < 0) THEN 'error'"
                                 "            WHEN (qoh + ordered - (totalalloc + pendalloc) < reorderlevel) THEN 'warning'"
                                 "       END AS totalavail_qtforegroundrole "
                                 "  FROM ( SELECT itemsite_id AS itemsiteid,"
                                 "                CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel,"
                                 "                bomwork_id, bomwork_parent_id,"
                                 "                bomwork_level, bomwork_seqnumber,"
                                 "                item_number, uom_name,"
                                 "                (item_descrip1 || ' ' || item_descrip2) AS item_descrip,"
                                 "                ((bomwork_qtyfxd + bomwork_qtyper * :qty) * (1 + bomwork_scrap)) AS pendalloc,"
                                 "                (qtyAllocated(itemsite_id, DATE(:schedDate)) - ((bomwork_qtyfxd + bomwork_qtyper * :origOrdQty) * (1 + bomwork_scrap))) AS totalalloc,"
                                 "                noNeg(itemsite_qtyonhand) AS qoh,"
                                 "                qtyOrdered(itemsite_id, DATE(:schedDate)) AS ordered"
                                 "           FROM bomwork, item, itemsite, uom"
                                 "          WHERE ( (itemsite_item_id=item_id)"
                                 "            AND   (itemsite_warehous_id=:warehous_id)"
                                 "            AND   (bomwork_item_id=item_id)"
                                 "            AND   (item_inv_uom_id=uom_id)"
                                 "            AND   (bomwork_set_id=:bomwork_set_id)"
                                 "                )"
                                 "       ) AS data "
                                 "ORDER BY bomworksequence(bomwork_id);");
            availability.bindValue(":bomwork_set_id", _worksetid);
            availability.bindValue(":warehous_id",    _warehouse->id());
            availability.bindValue(":qty",            _qtyOrdered->toDouble());
            availability.bindValue(":schedDate",      _scheduledDate->date());
            availability.bindValue(":origQtyOrd",     _originalQtyOrd);
            availability.exec();
            _availability->populate(availability);
	    if (availability.lastError().type() != QSqlError::NoError)
	    {
	      systemError(this, availability.lastError().databaseText(), __FILE__, __LINE__);
	      return;
	    }

            //  All done with the bomwork set, delete it
            availability.prepare("SELECT deleteBOMWorkset(:bomwork_set_id) AS result;");
            availability.bindValue(":bomwork_set_id", _worksetid);
            availability.exec();
	    if (availability.first())
	    {
	      int result = availability.value("result").toInt();
	      if (result < 0)
	      {
		systemError(this, storedProcErrorLookup("deleteBOMWorkset", result),
			    __FILE__, __LINE__);
		return;
	      }
	    }
	    else if (availability.lastError().type() != QSqlError::NoError)
	    {
	      systemError(this, availability.lastError().databaseText(), __FILE__, __LINE__);
	      return;
	    }
          }
        }
        else
        {
          int itemsiteid = availability.value("itemsite_id").toInt();
          availability.prepare( "SELECT itemsiteid, *, "
                                "       (qoh + ordered - (totalalloc + pendalloc)) AS totalavail,"
                                "       'qty' AS totalalloc_xtnumericrole,"
                                "       'qty' AS totalavail_xtnumericrole,"
                                "       'qty' AS pendalloc_xtnumericrole,"
                                "       'qty' AS ordered_xtnumericrole,"
                                "       'qty' AS qoh_xtnumericrole,"
                                "       CASE WHEN (qoh < pendalloc) THEN 'error'"
                                "       END AS qoh_qtforegroundrole,"
                                "       CASE WHEN (qoh + ordered - (totalalloc + pendalloc) < 0) THEN 'error'"
                                "            WHEN (qoh + ordered - (totalalloc + pendalloc) < reorderlevel) THEN 'warning'"
                                "       END AS totalavail_qtforegroundrole "
                                "FROM ( SELECT cs.itemsite_id AS itemsiteid,"
                                "              CASE WHEN(cs.itemsite_useparams) THEN cs.itemsite_reorderlevel ELSE 0.0 END AS reorderlevel,"
                                "              bomitem_seqnumber, item_number,"
                                "              (item_descrip1 || ' ' || item_descrip2) AS item_descrip, uom_name,"
                                "              itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, (bomitem_qtyfxd + bomitem_qtyper * :qty) * (1 + bomitem_scrap)) AS pendalloc,"
                                "              (qtyAllocated(cs.itemsite_id, DATE(:schedDate)) - itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, (bomitem_qtyfxd + bomitem_qtyper * :origQtyOrd) * (1 + bomitem_scrap))) AS totalalloc,"
                                "              noNeg(cs.itemsite_qtyonhand) AS qoh,"
                                "              qtyOrdered(cs.itemsite_id, DATE(:schedDate)) AS ordered "
                                "       FROM itemsite AS cs, itemsite AS ps, item, bomitem, uom "
                                "       WHERE ( (bomitem_item_id=item_id)"
                                "        AND (item_inv_uom_id=uom_id)"
                                "        AND (cs.itemsite_item_id=item_id)"
                                "        AND (cs.itemsite_warehous_id=ps.itemsite_warehous_id)"
                                "        AND (bomitem_parent_item_id=ps.itemsite_item_id)"
		                "        AND (bomitem_rev_id=getActiveRevId('BOM',bomitem_parent_item_id))"
                                "        AND (:schedDate BETWEEN bomitem_effective AND (bomitem_expires-1))"
                                "        AND (ps.itemsite_id=:itemsite_id) ) ) AS data "
                                "ORDER BY bomitem_seqnumber;" );
          availability.bindValue(":itemsite_id", itemsiteid);
          availability.bindValue(":qty", _qtyOrdered->toDouble());
          availability.bindValue(":schedDate", _scheduledDate->date());
          availability.bindValue(":origQtyOrd", _originalQtyOrd);
          availability.exec();
          _availability->populate(availability);
          if (availability.lastError().type() != QSqlError::NoError)
          {
            systemError(this, availability.lastError().databaseText(), __FILE__, __LINE__);
            return;
          }
        }
      }
      else
        _availability->setEnabled(false);
    }
    else if (availability.lastError().type() != QSqlError::NoError)
    {
      systemError(this, availability.lastError().databaseText(), __FILE__, __LINE__);
      return;
    }
  }
  else
  {
    _onHand->clear();
    _allocated->clear();
    _unallocated->clear();
    _onOrder->clear();
    _available->clear();
  }
}
Exemple #22
0
MetaSQLHighlighter::MetaSQLHighlighter(QTextDocument *document)
  : QSyntaxHighlighter(document)
{
  _commentColor   = namedColor("altemphasis");
  _errorColor     = namedColor("error");
  _extensionColor = namedColor("warning");
  _keywordColor   = namedColor("emphasis");
  _literalColor   = namedColor("future");

  // taken from postgresql docs "SQL Key Words"
  _keyword  << "A"
            << "ABORT"
            << "ABS"
            << "ABSOLUTE"
            << "ACCESS"
            << "ACTION"
            << "ADA"
            << "ADD"
            << "ADMIN"
            << "AFTER"
            << "AGGREGATE"
            << "ALIAS"
            << "ALL"
            << "ALLOCATE"
            << "ALSO"
            << "ALTER"
            << "ALWAYS"
            << "ANALYSE"
            << "ANALYZE"
            << "AND"
            << "ANY"
            << "ARE"
            << "ARRAY"
            << "AS"
            << "ASC"
            << "ASENSITIVE"
            << "ASSERTION"
            << "ASSIGNMENT"
            << "ASYMMETRIC"
            << "AT"
            << "ATOMIC"
            << "ATTRIBUTE"
            << "ATTRIBUTES"
            << "AUTHORIZATION"
            << "AVG"
            << "BACKWARD"
            << "BEFORE"
            << "BEGIN"
            << "BERNOULLI"
            << "BETWEEN"
            << "BIGINT"
            << "BINARY"
            << "BIT"
            << "BITVAR"
            << "BIT_LENGTH"
            << "BLOB"
            << "BOOLEAN"
            << "BOTH"
            << "BREADTH"
            << "BY"
            << "C"
            << "CACHE"
            << "CALL"
            << "CALLED"
            << "CARDINALITY"
            << "CASCADE"
            << "CASCADED"
            << "CASE"
            << "CAST"
            << "CATALOG"
            << "CATALOG_NAME"
            << "CEIL"
            << "CEILING"
            << "CHAIN"
            << "CHAR"
            << "CHARACTER"
            << "CHARACTERISTICS"
            << "CHARACTERS"
            << "CHARACTER_LENGTH"
            << "CHARACTER_SET_CATALOG"
            << "CHARACTER_SET_NAME"
            << "CHARACTER_SET_SCHEMA"
            << "CHAR_LENGTH"
            << "CHECK"
            << "CHECKED"
            << "CHECKPOINT"
            << "CLASS"
            << "CLASS_ORIGIN"
            << "CLOB"
            << "CLOSE"
            << "CLUSTER"
            << "COALESCE"
            << "COBOL"
            << "COLLATE"
            << "COLLATION"
            << "COLLATION_CATALOG"
            << "COLLATION_NAME"
            << "COLLATION_SCHEMA"
            << "COLLECT"
            << "COLUMN"
            << "COLUMN_NAME"
            << "COMMAND_FUNCTION"
            << "COMMAND_FUNCTION_CODE"
            << "COMMENT"
            << "COMMIT"
            << "COMMITTED"
            << "COMPLETION"
            << "CONCURRENTLY"
            << "CONDITION"
            << "CONDITION_NUMBER"
            << "CONNECT"
            << "CONNECTION"
            << "CONNECTION_NAME"
            << "CONSTRAINT"
            << "CONSTRAINTS"
            << "CONSTRAINT_CATALOG"
            << "CONSTRAINT_NAME"
            << "CONSTRAINT_SCHEMA"
            << "CONSTRUCTOR"
            << "CONTAINS"
            << "CONTINUE"
            << "CONVERSION"
            << "CONVERT"
            << "COPY"
            << "CORR"
            << "CORRESPONDING"
            << "COUNT"
            << "COVAR_POP"
            << "COVAR_SAMP"
            << "CREATE"
            << "CREATEDB"
            << "CREATEROLE"
            << "CREATEUSER"
            << "CROSS"
            << "CSV"
            << "CUBE"
            << "CUME_DIST"
            << "CURRENT"
            << "CURRENT_DATE"
            << "CURRENT_DEFAULT_TRANSFORM_GROUP"
            << "CURRENT_PATH"
            << "CURRENT_ROLE"
            << "CURRENT_TIME"
            << "CURRENT_TIMESTAMP"
            << "CURRENT_TRANSFORM_GROUP_FOR_TYPE"
            << "CURRENT_USER"
            << "CURSOR"
            << "CURSOR_NAME"
            << "CYCLE"
            << "DATA"
            << "DATABASE"
            << "DATE"
            << "DATETIME_INTERVAL_CODE"
            << "DATETIME_INTERVAL_PRECISION"
            << "DAY"
            << "DEALLOCATE"
            << "DEC"
            << "DECIMAL"
            << "DECLARE"
            << "DEFAULT"
            << "DEFAULTS"
            << "DEFERRABLE"
            << "DEFERRED"
            << "DEFINED"
            << "DEFINER"
            << "DEGREE"
            << "DELETE"
            << "DELIMITER"
            << "DELIMITERS"
            << "DENSE_RANK"
            << "DEPTH"
            << "DEREF"
            << "DERIVED"
            << "DESC"
            << "DESCRIBE"
            << "DESCRIPTOR"
            << "DESTROY"
            << "DESTRUCTOR"
            << "DETERMINISTIC"
            << "DIAGNOSTICS"
            << "DICTIONARY"
            << "DISABLE"
            << "DISCONNECT"
            << "DISPATCH"
            << "DISTINCT"
            << "DO"
            << "DOMAIN"
            << "DOUBLE"
            << "DROP"
            << "DYNAMIC"
            << "DYNAMIC_FUNCTION"
            << "DYNAMIC_FUNCTION_CODE"
            << "EACH"
            << "ELEMENT"
            << "ELSE"
            << "ENABLE"
            << "ENCODING"
            << "ENCRYPTED"
            << "END"
            << "END-EXEC"
            << "EQUALS"
            << "ESCAPE"
            << "EVERY"
            << "EXCEPT"
            << "EXCEPTION"
            << "EXCLUDE"
            << "EXCLUDING"
            << "EXCLUSIVE"
            << "EXEC"
            << "EXECUTE"
            << "EXISTING"
            << "EXISTS"
            << "EXP"
            << "EXPLAIN"
            << "EXTERNAL"
            << "EXTRACT"
            << "FALSE"
            << "FETCH"
            << "FILTER"
            << "FINAL"
            << "FIRST"
            << "FLOAT"
            << "FLOOR"
            << "FOLLOWING"
            << "FOR"
            << "FORCE"
            << "FOREIGN"
            << "FORTRAN"
            << "FORWARD"
            << "FOUND"
            << "FREE"
            << "FREEZE"
            << "FROM"
            << "FULL"
            << "FUNCTION"
            << "FUSION"
            << "G"
            << "GENERAL"
            << "GENERATED"
            << "GET"
            << "GLOBAL"
            << "GO"
            << "GOTO"
            << "GRANT"
            << "GRANTED"
            << "GREATEST"
            << "GROUP"
            << "GROUPING"
            << "HANDLER"
            << "HAVING"
            << "HEADER"
            << "HIERARCHY"
            << "HOLD"
            << "HOST"
            << "HOUR"
            << "IDENTITY"
            << "IF"
            << "IGNORE"
            << "ILIKE"
            << "IMMEDIATE"
            << "IMMUTABLE"
            << "IMPLEMENTATION"
            << "IMPLICIT"
            << "IN"
            << "INCLUDING"
            << "INCREMENT"
            << "INDEX"
            << "INDEXES"
            << "INDICATOR"
            << "INFIX"
            << "INHERIT"
            << "INHERITS"
            << "INITIALIZE"
            << "INITIALLY"
            << "INNER"
            << "INOUT"
            << "INPUT"
            << "INSENSITIVE"
            << "INSERT"
            << "INSTANCE"
            << "INSTANTIABLE"
            << "INSTEAD"
            << "INT"
            << "INTEGER"
            << "INTERSECT"
            << "INTERSECTION"
            << "INTERVAL"
            << "INTO"
            << "INVOKER"
            << "IS"
            << "ISNULL"
            << "ISOLATION"
            << "ITERATE"
            << "JOIN"
            << "K"
            << "KEY"
            << "KEY_MEMBER"
            << "KEY_TYPE"
            << "LANCOMPILER"
            << "LANGUAGE"
            << "LARGE"
            << "LAST"
            << "LATERAL"
            << "LEADING"
            << "LEAST"
            << "LEFT"
            << "LENGTH"
            << "LESS"
            << "LEVEL"
            << "LIKE"
            << "LIMIT"
            << "LISTEN"
            << "LN"
            << "LOAD"
            << "LOCAL"
            << "LOCALTIME"
            << "LOCALTIMESTAMP"
            << "LOCATION"
            << "LOCATOR"
            << "LOCK"
            << "LOGIN"
            << "LOWER"
            << "M"
            << "MAP"
            << "MATCH"
            << "MATCHED"
            << "MAX"
            << "MAXVALUE"
            << "MEMBER"
            << "MERGE"
            << "MESSAGE_LENGTH"
            << "MESSAGE_OCTET_LENGTH"
            << "MESSAGE_TEXT"
            << "METHOD"
            << "MIN"
            << "MINUTE"
            << "MINVALUE"
            << "MOD"
            << "MODE"
            << "MODIFIES"
            << "MODIFY"
            << "MODULE"
            << "MONTH"
            << "MORE"
            << "MOVE"
            << "MULTISET"
            << "MUMPS"
            << "NAME"
            << "NAMES"
            << "NATIONAL"
            << "NATURAL"
            << "NCHAR"
            << "NCLOB"
            << "NESTING"
            << "NEW"
            << "NEXT"
            << "NO"
            << "NOCREATEDB"
            << "NOCREATEROLE"
            << "NOCREATEUSER"
            << "NOINHERIT"
            << "NOLOGIN"
            << "NONE"
            << "NORMALIZE"
            << "NORMALIZED"
            << "NOSUPERUSER"
            << "NOT"
            << "NOTHING"
            << "NOTIFY"
            << "NOTNULL"
            << "NOWAIT"
            << "NULL"
            << "NULLABLE"
            << "NULLIF"
            << "NULLS"
            << "NUMBER"
            << "NUMERIC"
            << "OBJECT"
            << "OCTETS"
            << "OCTET_LENGTH"
            << "OF"
            << "OFF"
            << "OFFSET"
            << "OIDS"
            << "OLD"
            << "ON"
            << "ONLY"
            << "OPEN"
            << "OPERATION"
            << "OPERATOR"
            << "OPTION"
            << "OPTIONS"
            << "OR"
            << "ORDER"
            << "ORDERING"
            << "ORDINALITY"
            << "OTHERS"
            << "OUT"
            << "OUTER"
            << "OUTPUT"
            << "OVER"
            << "OVERLAPS"
            << "OVERLAY"
            << "OVERRIDING"
            << "OWNED"
            << "OWNER"
            << "PAD"
            << "PARAMETER"
            << "PARAMETERS"
            << "PARAMETER_MODE"
            << "PARAMETER_NAME"
            << "PARAMETER_ORDINAL_POSITION"
            << "PARAMETER_SPECIFIC_CATALOG"
            << "PARAMETER_SPECIFIC_NAME"
            << "PARAMETER_SPECIFIC_SCHEMA"
            << "PARTIAL"
            << "PARTITION"
            << "PASCAL"
            << "PASSWORD"
            << "PATH"
            << "PERCENTILE_CONT"
            << "PERCENTILE_DISC"
            << "PERCENT_RANK"
            << "PLACING"
            << "PLI"
            << "POSITION"
            << "POSTFIX"
            << "POWER"
            << "PRECEDING"
            << "PRECISION"
            << "PREFIX"
            << "PREORDER"
            << "PREPARE"
            << "PREPARED"
            << "PRESERVE"
            << "PRIMARY"
            << "PRIOR"
            << "PRIVILEGES"
            << "PROCEDURAL"
            << "PROCEDURE"
            << "PUBLIC"
            << "QUOTE"
            << "RANGE"
            << "RANK"
            << "READ"
            << "READS"
            << "REAL"
            << "REASSIGN"
            << "RECHECK"
            << "RECURSIVE"
            << "REF"
            << "REFERENCES"
            << "REFERENCING"
            << "REGR_AVGX"
            << "REGR_AVGY"
            << "REGR_COUNT"
            << "REGR_INTERCEPT"
            << "REGR_R2"
            << "REGR_SLOPE"
            << "REGR_SXX"
            << "REGR_SXY"
            << "REGR_SYY"
            << "REINDEX"
            << "RELATIVE"
            << "RELEASE"
            << "RENAME"
            << "REPEATABLE"
            << "REPLACE"
            << "RESET"
            << "RESTART"
            << "RESTRICT"
            << "RESULT"
            << "RETURN"
            << "RETURNED_CARDINALITY"
            << "RETURNED_LENGTH"
            << "RETURNED_OCTET_LENGTH"
            << "RETURNED_SQLSTATE"
            << "RETURNING"
            << "RETURNS"
            << "REVOKE"
            << "RIGHT"
            << "ROLE"
            << "ROLLBACK"
            << "ROLLUP"
            << "ROUTINE"
            << "ROUTINE_CATALOG"
            << "ROUTINE_NAME"
            << "ROUTINE_SCHEMA"
            << "ROW"
            << "ROWS"
            << "ROW_COUNT"
            << "ROW_NUMBER"
            << "RULE"
            << "SAVEPOINT"
            << "SCALE"
            << "SCHEMA"
            << "SCHEMA_NAME"
            << "SCOPE"
            << "SCOPE_CATALOG"
            << "SCOPE_NAME"
            << "SCOPE_SCHEMA"
            << "SCROLL"
            << "SEARCH"
            << "SECOND"
            << "SECTION"
            << "SECURITY"
            << "SELECT"
            << "SELF"
            << "SENSITIVE"
            << "SEQUENCE"
            << "SERIALIZABLE"
            << "SERVER_NAME"
            << "SESSION"
            << "SESSION_USER"
            << "SET"
            << "SETOF"
            << "SETS"
            << "SHARE"
            << "SHOW"
            << "SIMILAR"
            << "SIMPLE"
            << "SIZE"
            << "SMALLINT"
            << "SOME"
            << "SOURCE"
            << "SPACE"
            << "SPECIFIC"
            << "SPECIFICTYPE"
            << "SPECIFIC_NAME"
            << "SQL"
            << "SQLCODE"
            << "SQLERROR"
            << "SQLEXCEPTION"
            << "SQLSTATE"
            << "SQLWARNING"
            << "SQRT"
            << "STABLE"
            << "START"
            << "STATE"
            << "STATEMENT"
            << "STATIC"
            << "STATISTICS"
            << "STDDEV_POP"
            << "STDDEV_SAMP"
            << "STDIN"
            << "STDOUT"
            << "STORAGE"
            << "STRICT"
            << "STRUCTURE"
            << "STYLE"
            << "SUBCLASS_ORIGIN"
            << "SUBLIST"
            << "SUBMULTISET"
            << "SUBSTRING"
            << "SUM"
            << "SUPERUSER"
            << "SYMMETRIC"
            << "SYSID"
            << "SYSTEM"
            << "SYSTEM_USER"
            << "TABLE"
            << "TABLESAMPLE"
            << "TABLESPACE"
            << "TABLE_NAME"
            << "TEMP"
            << "TEMPLATE"
            << "TEMPORARY"
            << "TERMINATE"
            << "THAN"
            << "THEN"
            << "TIES"
            << "TIME"
            << "TIMESTAMP"
            << "TIMEZONE_HOUR"
            << "TIMEZONE_MINUTE"
            << "TO"
            << "TOP_LEVEL_COUNT"
            << "TRAILING"
            << "TRANSACTION"
            << "TRANSACTIONS_COMMITTED"
            << "TRANSACTIONS_ROLLED_BACK"
            << "TRANSACTION_ACTIVE"
            << "TRANSFORM"
            << "TRANSFORMS"
            << "TRANSLATE"
            << "TRANSLATION"
            << "TREAT"
            << "TRIGGER"
            << "TRIGGER_CATALOG"
            << "TRIGGER_NAME"
            << "TRIGGER_SCHEMA"
            << "TRIM"
            << "TRUE"
            << "TRUNCATE"
            << "TRUSTED"
            << "TYPE"
            << "UESCAPE"
            << "UNBOUNDED"
            << "UNCOMMITTED"
            << "UNDER"
            << "UNENCRYPTED"
            << "UNION"
            << "UNIQUE"
            << "UNKNOWN"
            << "UNLISTEN"
            << "UNNAMED"
            << "UNNEST"
            << "UNTIL"
            << "UPDATE"
            << "UPPER"
            << "USAGE"
            << "USER"
            << "USER_DEFINED_TYPE_CATALOG"
            << "USER_DEFINED_TYPE_CODE"
            << "USER_DEFINED_TYPE_NAME"
            << "USER_DEFINED_TYPE_SCHEMA"
            << "USING"
            << "VACUUM"
            << "VALID"
            << "VALIDATOR"
            << "VALUE"
            << "VALUES"
            << "VARCHAR"
            << "VARIABLE"
            << "VARYING"
            << "VAR_POP"
            << "VAR_SAMP"
            << "VERBOSE"
            << "VIEW"
            << "VOLATILE"
            << "WHEN"
            << "WHENEVER"
            << "WHERE"
            << "WIDTH_BUCKET"
            << "WINDOW"
            << "WITH"
            << "WITHIN"
            << "WITHOUT"
            << "WORK"
            << "WRITE"
            << "YEAR"
            << "ZONE"
            ;

  // these are metasql key words from http://www.xtuple.org/node/270
  _extension  << "break"
              << "continue"
              << "else"
              << "elseif"
              << "endforeach"
              << "endif"
              << "exists"
              << "foreach"
              << "if"
              << "isFirst"
              << "isLast"
              << "literal"
              << "not"
              << "reExists"
              << "value"
              ;

}