void QgsWFSSourceSelect::buildQuery( const QModelIndex &index )
{
  if ( !index.isValid() )
  {
    return;
  }

  const QString typeName = index.sibling( index.row(), MODEL_IDX_NAME ).data().toString();

  //get available fields for wfs layer
  QgsWfsConnection connection( cmbConnections->currentText() );
  QgsWFSDataSourceURI uri( connection.uri().uri() );
  uri.setTypeName( typeName );

  QgsDataProvider::ProviderOptions providerOptions;
  QgsWFSProvider p( uri.uri(), providerOptions, mCaps );
  if ( !p.isValid() )
  {
    QMessageBox *box = new QMessageBox( QMessageBox::Critical, tr( "Server exception" ), tr( "DescribeFeatureType failed" ), QMessageBox::Ok, this );
    box->setAttribute( Qt::WA_DeleteOnClose );
    box->setModal( true );
    box->setObjectName( QStringLiteral( "WFSFeatureTypeErrorBox" ) );
    if ( !property( "hideDialogs" ).toBool() )
      box->open();

    return;
  }

  QModelIndex filterIndex = index.sibling( index.row(), MODEL_IDX_SQL );
  QString sql( filterIndex.data().toString() );
  QString displayedTypeName( typeName );
  if ( !mCaps.setAmbiguousUnprefixedTypename.contains( QgsWFSUtils::removeNamespacePrefix( typeName ) ) )
    displayedTypeName = QgsWFSUtils::removeNamespacePrefix( typeName );
  QString allSql( "SELECT * FROM " + QgsSQLStatement::quotedIdentifierIfNeeded( displayedTypeName ) );
  if ( sql.isEmpty() )
  {
    sql = allSql;
  }

  QgsSQLComposerDialog *d = new QgsSQLComposerDialog( this );

  QgsWFSValidatorCallback *validatorCbk = new QgsWFSValidatorCallback( d, uri, allSql, mCaps );
  d->setSQLValidatorCallback( validatorCbk );

  QgsWFSTableSelectedCallback *tableSelectedCbk = new QgsWFSTableSelectedCallback( d, uri, mCaps );
  d->setTableSelectedCallback( tableSelectedCbk );

  const bool bSupportJoins = mCaps.featureTypes.size() > 1 && mCaps.supportsJoins;
  d->setSupportMultipleTables( bSupportJoins, QgsSQLStatement::quotedIdentifierIfNeeded( displayedTypeName ) );

  QMap< QString, QString > mapTypenameToTitle;
  Q_FOREACH ( const QgsWfsCapabilities::FeatureType f, mCaps.featureTypes )
    mapTypenameToTitle[f.name] = f.title;

  QList< QgsSQLComposerDialog::PairNameTitle > tablenames;
  tablenames << QgsSQLComposerDialog::PairNameTitle(
               QgsSQLStatement::quotedIdentifierIfNeeded( displayedTypeName ), mapTypenameToTitle[typeName] );
  if ( bSupportJoins )
  {
    for ( int i = 0; i < mModel->rowCount(); i++ )
    {
      const QString iterTypename = mModel->index( i, MODEL_IDX_NAME ).data().toString();
      if ( iterTypename != typeName )
      {
        QString displayedIterTypename( iterTypename );
        QString unprefixedIterTypename( QgsWFSUtils::removeNamespacePrefix( iterTypename ) );
        if ( !mCaps.setAmbiguousUnprefixedTypename.contains( unprefixedIterTypename ) )
          displayedIterTypename = unprefixedIterTypename;

        tablenames << QgsSQLComposerDialog::PairNameTitle(
                     QgsSQLStatement::quotedIdentifierIfNeeded( displayedIterTypename ), mapTypenameToTitle[iterTypename] );
      }
    }
  }
  d->addTableNames( tablenames );

  QList< QgsSQLComposerDialog::Function> functionList;
  Q_FOREACH ( const QgsWfsCapabilities::Function &f, mCaps.functionList )
  {
    QgsSQLComposerDialog::Function dialogF;
    dialogF.name = f.name;
    dialogF.returnType = f.returnType;
    dialogF.minArgs = f.minArgs;
    dialogF.maxArgs = f.maxArgs;
    Q_FOREACH ( const QgsWfsCapabilities::Argument &arg, f.argumentList )
    {
      dialogF.argumentList << QgsSQLComposerDialog::Argument( arg.name, arg.type );
    }
    functionList << dialogF;
  }
  d->addFunctions( functionList );

  QList< QgsSQLComposerDialog::Function> spatialPredicateList;
  Q_FOREACH ( const QgsWfsCapabilities::Function &f, mCaps.spatialPredicatesList )
  {
    QgsSQLComposerDialog::Function dialogF;
    dialogF.name = f.name;
    dialogF.returnType = f.returnType;
    dialogF.minArgs = f.minArgs;
    dialogF.maxArgs = f.maxArgs;
    Q_FOREACH ( const QgsWfsCapabilities::Argument &arg, f.argumentList )
    {
      dialogF.argumentList << QgsSQLComposerDialog::Argument( arg.name, arg.type );
    }
    spatialPredicateList << dialogF;
  }
  d->addSpatialPredicates( spatialPredicateList );

  QList< QgsSQLComposerDialog::PairNameType> fieldList;
  QString fieldNamePrefix;
  if ( bSupportJoins )
  {
    fieldNamePrefix = QgsSQLStatement::quotedIdentifierIfNeeded( displayedTypeName ) + ".";
  }
  Q_FOREACH ( const QgsField &field, p.fields().toList() )
  {
    QString fieldName( fieldNamePrefix + QgsSQLStatement::quotedIdentifierIfNeeded( field.name() ) );
    fieldList << QgsSQLComposerDialog::PairNameType( fieldName, field.typeName() );
  }
  if ( !p.geometryAttribute().isEmpty() )
  {
    QString fieldName( fieldNamePrefix + QgsSQLStatement::quotedIdentifierIfNeeded( p.geometryAttribute() ) );
    fieldList << QgsSQLComposerDialog::PairNameType( fieldName, QStringLiteral( "geometry" ) );
  }
  fieldList << QgsSQLComposerDialog::PairNameType( fieldNamePrefix + "*", QString() );

  d->addColumnNames( fieldList, QgsSQLStatement::quotedIdentifierIfNeeded( displayedTypeName ) );

  d->setSql( sql );

  mSQLIndex = index;
  mSQLComposerDialog = d;
  // For testability, do not use exec()
  if ( property( "hideDialogs" ).toBool() )
  {
    d->setAttribute( Qt::WA_DeleteOnClose );
    d->setModal( true );
    d->open();
    connect( d, &QDialog::accepted, this, &QgsWFSSourceSelect::updateSql );
  }
  else
  {
    // But we need to use exec() for real GUI, otherwise it does not look
    // right on Mac
    if ( d->exec() )
    {
      updateSql();
    }
    delete d;
  }
}
Esempio n. 2
0
void dspWoScheduleByWorkOrder::sFillList()
{
  _wo->clear();

  QString sql( "SELECT wo_id, itemsite_id,"
	       " formatWONumber(wo_id) AS wonumber,"
	       " wo_status, wo_priority, warehous_code,"
	       " formatQty(wo_qtyord) AS qtyord,"
	       " formatQty(wo_qtyrcv) AS qtyrcv,"
	       " formatDate(wo_startdate) AS startdate,"
	       " formatDate(wo_duedate) AS duedate,"
	       " ((wo_startdate<=CURRENT_DATE)"
	       "   AND (wo_status IN ('O','E','S','R'))) AS latestart,"
	       " (wo_duedate<=CURRENT_DATE) AS latedue "
	       "FROM wo, itemsite, warehous "
	       "WHERE ((wo_itemsite_id=itemsite_id)"
	       " AND (itemsite_warehous_id=warehous_id)"
	       " AND (wo_startdate BETWEEN <? value(\"startDate\") ?>"
	       "                       AND <? value(\"endDate\") ?>)"
	       " AND (wo_number IN (SELECT wo_number"
	       "                    FROM wo"
	       "                    WHERE (wo_id=<? value(\"wo_id\") ?>)))"
	       "<? if exists(\"showOnlyRI\") ?>"
	       " AND (wo_status IN ('R','I'))"
	       "<? else ?>"
	       " AND (wo_status<>'C')"
	       "<? endif ?>"
	       "<? if exists(\"showOnlyTopLevel\") ?>"
	       " AND (wo_ordtype<>'W')"
	       "<? endif ?>"
	       "<? if exists(\"warehous_id\") ?>"
	       " AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
	       "<? endif ?>"
	       ") "
	       "ORDER BY "
	       "<? if exists(\"sortByStartDate\") ?>"
	       "	wo_startdate,"
	       "<? elseif exists(\"sortByDueDate\") ?>"
	       "	wo_duedate,"
	       "<? endif ?>"
	       " wo_number, wo_subnumber" );

  MetaSQLQuery mql(sql);
  ParameterList params;
  if (! setParams(params))
    return;
  q = mql.toQuery(params);
  q.exec();
  XTreeWidgetItem *last = 0;
  while (q.next())
  {
    last = new XTreeWidgetItem(_wo, last, q.value("wo_id").toInt(),
			       q.value("itemsite_id").toInt(),
			       q.value("wonumber"),
			       q.value("wo_status"),
			       q.value("wo_priority"),
			       q.value("warehous_code"),
			       q.value("qtyord"),
			       q.value("qtyrcv"),
			       q.value("startdate"),
			       q.value("duedate") );

    if (q.value("latestart").toBool())
      last->setTextColor(6, "red");

    if (q.value("latedue").toBool())
      last->setTextColor(7, "red");
  }
  if (q.lastError().type() != QSqlError::None)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }
}
Esempio n. 3
0
void items::sFillList( int pItemid, bool pLocal )
{
  QString sql( "SELECT item_id, item_number, formatBoolYN(item_active),"
               "       (item_descrip1 || ' ' || item_descrip2), classcode_code,"
               "       CASE WHEN (item_type='P') THEN text(<? value(\"purchased\") ?>)"
               "            WHEN (item_type='M') THEN text(<? value(\"manufactured\") ?>)"
			   "            WHEN (item_type='J') THEN text(<? value(\"job\") ?>)"
               "            WHEN (item_type='F') THEN text(<? value(\"phantom\") ?>)"
               "            WHEN (item_type='B') THEN text(<? value(\"breeder\") ?>)"
               "            WHEN (item_type='C') THEN text(<? value(\"coProduct\") ?>)"
               "            WHEN (item_type='Y') THEN text(<? value(\"byProduct\") ?>)"
               "            WHEN (item_type='R') THEN text(<? value(\"reference\") ?>)"
               "            WHEN (item_type='S') THEN text(<? value(\"costing\") ?>)"
               "            WHEN (item_type='T') THEN text(<? value(\"tooling\") ?>)"
               "            WHEN (item_type='A') THEN text(<? value(\"assortment\") ?>)"
               "            WHEN (item_type='O') THEN text(<? value(\"outside\") ?>)"
               "            WHEN (item_type='L') THEN text(<? value(\"planning\") ?>)"
               "            WHEN (item_type='K') THEN text(<? value(\"kit\") ?>)"
               "            ELSE text(<? value(\"error\") ?>)"
               "       END,"
               "       uom_name "
               "FROM item, classcode, uom "
               "WHERE ( (item_classcode_id=classcode_id)"
               " AND (item_inv_uom_id=uom_id)"
               "<? if exists(\"showPurchased\") ?>"
               " AND (item_type IN ('P', 'O'))"
               "<? elseif exists(\"showManufactured\") ?>"
               " AND (item_type IN ('M', 'F', 'B', 'J','K'))"
               "<? elseif exists(\"showSold\") ?>"
               " AND (item_sold)"
               "<? endif ?>"
               "<? if exists(\"onlyShowActive\") ?>"
               " AND (item_active)"
               "<? endif ?>"
               ") ORDER BY"
               "<? if exists(\"ListNumericItemNumbersFirst\") ?>"
               " toNumeric(item_number, 999999999999999),"
               "<? endif ?>"
               " item_number;" );

  ParameterList params;

  if(_showPurchased->isChecked())
    params.append("showPurchased");
  else if(_showManufactured->isChecked())
    params.append("showManufactured");
  else if(_showSold->isChecked())
    params.append("showSold");

  if (!_showInactive->isChecked())
    params.append("onlyShowActive");

  if (_preferences->boolean("ListNumericItemNumbersFirst"))
    params.append("ListNumericItemNumbersFirst");
  
  params.append("purchased", tr("Purchased"));
  params.append("manufactured", tr("Manufactured"));
  params.append("job", tr("Job"));
  params.append("phantom", tr("Phantom"));
  params.append("breeder", tr("Breeder"));
  params.append("coProduct", tr("Co-Product"));
  params.append("byProduct", tr("By-Product"));
  params.append("reference", tr("Reference"));
  params.append("costing", tr("Costing"));
  params.append("tooling", tr("Tooling"));
  params.append("outside", tr("Outside Process"));
  params.append("planning", tr("Planning"));
  params.append("assortment", tr("Assortment"));
  params.append("kit", tr("Kit"));
  params.append("error", tr("Error"));

  MetaSQLQuery mql(sql);
  q = mql.toQuery(params);

  if ((pItemid != -1) && (pLocal))
    _item->populate(q, pItemid);
  else
    _item->populate(q);
}
Esempio n. 4
0
void searchForItem::sFillList()
{
  _item->clear();
  if (_search->text().stripWhiteSpace().length() == 0)    
    return;

  QString sql( "SELECT item_id,"
               "       (item_type IN ('P', 'M', 'B')) AS hasbom,"
               "       (item_type IN ('P', 'M')) AS hasboo,"
               "       (item_type = 'B') AS hasbbom,"
               "       item_number, (item_descrip1 || ' ' || item_descrip2) AS description,"
               "       CASE WHEN (item_type='P') THEN :purchased"
               "            WHEN (item_type='M') THEN :manufactured" 
               "            WHEN (item_type='F') THEN :phantom"
               "            WHEN (item_type='B') THEN :breeder"
               "            WHEN (item_type='C') THEN :coProduct"
               "            WHEN (item_type='Y') THEN :byProduct"
               "            WHEN (item_type='R') THEN :reference"
               "            WHEN (item_type='S') THEN :costing"
               "            WHEN (item_type='T') THEN :tooling"
               "            WHEN (item_type='A') THEN :assortment"
               "            WHEN (item_type='O') THEN :outside"
               "            ELSE :error"
               "       END AS type "
               "FROM item "
               "WHERE ( ( ((:useNumber) AND (item_number ~* :searchString))"
               "       OR ((:useDescrip1) AND (item_descrip1 ~* :searchString))"
               "       OR ((:useDescrip2) AND (item_descrip2 ~* :searchString)) )" );

  if (!_showInactive->isChecked())
    sql += " AND (item_active)";

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

  q.prepare(sql);
  q.bindValue(":purchased", tr("Purchased"));
  q.bindValue(":manufactured", tr("Manufactured"));
  q.bindValue(":phantom", tr("Phantom"));
  q.bindValue(":breeder", tr("Breeder"));
  q.bindValue(":coProduct", tr("Co-Product"));
  q.bindValue(":byProduct", tr("By-Product"));
  q.bindValue(":reference", tr("Reference"));
  q.bindValue(":costing", tr("Costing"));
  q.bindValue(":tooling", tr("Tooling"));
  q.bindValue(":outside", tr("Outside Process"));
  q.bindValue(":assortment", tr("Assortment"));
  q.bindValue(":error", tr("Error"));
  q.bindValue(":useNumber", QVariant(_searchNumber->isChecked(), 0));
  q.bindValue(":useDescrip1", QVariant(_searchDescrip1->isChecked(), 0));
  q.bindValue(":useDescrip2", QVariant(_searchDescrip2->isChecked(), 0));
  q.bindValue(":searchString", _search->text().upper());
  q.exec();
  XTreeWidgetItem * last = 0;
  while (q.next())
  {
    int flag = 0;
    if (q.value("hasbom").toBool())
      flag |= 1;
    if (q.value("hasboo").toBool())
      flag |= 2;
    if (q.value("hasbbom").toBool())
      flag |= 4;

    last = new XTreeWidgetItem( _item, last, q.value("item_id").toInt(), flag,
                                q.value("item_number"), q.value("description"),
                                q.value("type") );
  }
}
Esempio n. 5
0
void printShippingForms::sPrint()
{
  if (!_printNew->isChecked() && !_printDirty->isChecked())
  {
    QMessageBox::warning( this, tr("Cannot Print Shipping Forms"),
                          tr("You must indicate if you wish to print Shipping Forms for New and/or Changed Shipments.") );
    return;
  }

  QString sql( "SELECT shiphead_id, report_name "
               "FROM shiphead, cohead, shipform, report "
               "WHERE ( (NOT shiphead_shipped)"
               " AND (shiphead_order_id=cohead_id)"
	       " AND (shiphead_order_type='SO')"
               " AND (shipform_report_id=report_id)"
               " AND (shipform_id=COALESCE(shiphead_shipform_id, cohead_shipform_id))"
               " AND (shiphead_sfstatus IN (" );

  if (_printNew->isChecked())
  {
    sql += "'N'";

    if (_printDirty->isChecked())
      sql += ", 'D'";
  }
  else if (_printDirty->isChecked())
    sql += "'D'";
  sql += ")) ) "
         "ORDER BY shiphead_id;";

  XSqlQuery reports;
  reports.exec(sql);
  if (reports.first())
  {
    QPrinter printer(QPrinter::HighResolution);

    bool     setupPrinter = TRUE;
    bool userCanceled = false;
    if (orReport::beginMultiPrint(&printer, userCanceled) == false)
    {
      if(!userCanceled)
        systemError(this, tr("Could not initialize printing system for multiple reports."));
      return;
    }

    do
    {
      for (int i = 0; i < _shipformWatermarks->topLevelItemCount(); i++ )
      {
        ParameterList params;

        params.append("cosmisc_id", reports.value("shiphead_id").toInt());
        params.append("shiphead_id", reports.value("shiphead_id").toInt());

        params.append("watermark", _shipformWatermarks->topLevelItem(i)->text(1));

#if 0
        params.append("shipchrg_id", _shipchrg->id());

#endif

        if (_shipformWatermarks->topLevelItem(i)->text(2) == tr("Yes"))
          params.append("showcosts");

        orReport report(reports.value("report_name").toString(), params);
        if (report.print(&printer, setupPrinter))
          setupPrinter = FALSE;
        else
        {
          report.reportError(this);
	  orReport::endMultiPrint(&printer);
          return;
        }
      }

      XSqlQuery setStatus;
      setStatus.prepare( "UPDATE shiphead "
                         "SET shiphead_sfstatus='P' "
                         "WHERE (shiphead_id=:shiphead_id);" );
      setStatus.bindValue(":shiphead_id", reports.value("shiphead_id").toInt());
      setStatus.exec();
    }
    while (reports.next());
    orReport::endMultiPrint(&printer);

    if (_captive)
      accept();
  }
  else
    QMessageBox::warning( this, tr("Cannot Print Shipping Forms"),
                          tr("There are no New or Changed Shipments for which Shipping Forms should be printed.") );

}
Esempio n. 6
0
void dspWoScheduleByItem::sFillList()
{
  _wo->clear();

  if ((_item->isValid()) && (_dates->allValid()))
  {
    QString sql( "SELECT wo_id,"
                 " formatWONumber(wo_id) AS wonumber,"
                 " wo_status, wo_priority, warehous_code,"
                 " formatQty(wo_qtyord) AS qtyord,"
                 " formatQty(wo_qtyrcv) AS qtyrcv,"
                 " formatDate(wo_startdate) AS startdate,"
                 " formatDate(wo_duedate) AS duedate,"
                 " ((wo_startdate<=CURRENT_DATE) AND (wo_status IN ('O','E','S','R'))) AS latestart,"
                 " (wo_duedate<=CURRENT_DATE) AS latedue "
                 "FROM wo, itemsite, warehous "
                 "WHERE ((wo_itemsite_id=itemsite_id)"
                 " AND (itemsite_warehous_id=warehous_id)"
                 " AND (itemsite_item_id=:item_id)"
                 " AND (wo_startdate BETWEEN :startDate AND :endDate)" );

    if (_showOnlyRI->isChecked())
      sql += " AND (wo_status IN ('R','I'))";
    else
      sql += " AND (wo_status<>'C')";

    if (_showOnlyTopLevel->isChecked())
      sql += " AND (wo_ordtype<>'W')";

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

    sql += ") ";

    if (_sortByStartDate->isChecked())
      sql += "ORDER BY wo_startdate, wo_number, wo_subnumber";
    else
      sql += "ORDER BY wo_duedate, wo_number, wo_subnumber";

    q.prepare(sql);
    _warehouse->bindValue(q);
    _dates->bindValue(q);
    q.bindValue(":item_id", _item->id());
    q.exec();
    XTreeWidgetItem *last = 0;
    while (q.next())
    {
      last = new XTreeWidgetItem(_wo, last, q.value("wo_id").toInt(),
				 q.value("wonumber"),
				 q.value("wo_status"),
				 q.value("wo_priority"),
				 q.value("warehous_code"),
				 q.value("qtyord"),
				 q.value("qtyrcv"),
				 q.value("startdate"),
				 q.value("duedate") );

      if (q.value("latestart").toBool())
        last->setTextColor(6, "red");

      if (q.value("latedue").toBool())
        last->setTextColor(7, "red");
    }
  }
}
Esempio n. 7
0
void dspTimePhasedUsageStatisticsByItem::sCalculate()
{
  if (TRUE)
  {
    _columnDates.clear();
    _usage->clear();
    _usage->setColumnCount(2);

    QString sql("SELECT itemsite_id, warehous_code");

    int columns = 1;
    QList<XTreeWidgetItem*> selected = _periods->selectedItems();
    for (int i = 0; i < selected.size(); i++)
    {
      PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i];
      sql += QString( ", summTransR(itemsite_id, %1) AS r_bucket%2,"
		      "summTransI(itemsite_id, %3) AS i_bucket%4,"
		      "summTransS(itemsite_id, %5) AS s_bucket%6,"
		      "summTransC(itemsite_id, %7) AS c_bucket%8," )
	     .arg(cursor->id())
	     .arg(columns)
	     .arg(cursor->id())
	     .arg(columns)
	     .arg(cursor->id())
	     .arg(columns)
	     .arg(cursor->id())
	     .arg(columns);

      sql += QString("summTransA(itemsite_id, %1) AS a_bucket%2")
	     .arg(cursor->id())
	     .arg(columns++);

      _usage->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight);

      _columnDates.append(DatePair(cursor->startDate(), cursor->endDate()));
    }

    sql += QString( " FROM itemsite, warehous "
                    "WHERE ((itemsite_warehous_id=warehous_id)"
                    " AND (itemsite_item_id=%1)" )
           .arg(_item->id());

    if (_warehouse->isSelected())
	sql += QString(" AND (itemsite_warehous_id=%1)")
			    .arg(_warehouse->id());

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

    q.prepare(sql);
    q.exec();
    if (q.first())
    {
      do
      {
        XTreeWidgetItem *received;
        XTreeWidgetItem *issued;
        XTreeWidgetItem *sold;
        XTreeWidgetItem *scrap;
        XTreeWidgetItem *adjustments;

        received    = new XTreeWidgetItem(_usage,           q.value("itemsite_id").toInt(), QVariant(tr("Received")),    q.value("warehous_code") );
        issued      = new XTreeWidgetItem(_usage, received, q.value("itemsite_id").toInt(), QVariant(tr("Issued")),      q.value("warehous_code") );
        sold        = new XTreeWidgetItem(_usage, issued,   q.value("itemsite_id").toInt(), QVariant(tr("Sold")),        q.value("warehous_code") );
        scrap       = new XTreeWidgetItem(_usage, sold,     q.value("itemsite_id").toInt(), QVariant(tr("Scrap")),       q.value("warehous_code") );
        adjustments = new XTreeWidgetItem(_usage, scrap,    q.value("itemsite_id").toInt(), QVariant(tr("Adjustments")), q.value("warehous_code") );

        for (int bucketCounter = 1; bucketCounter < columns; bucketCounter++)
        {
          received->setText((bucketCounter + 1), formatQty(q.value(QString("r_bucket%1").arg(bucketCounter)).toDouble()));
          issued->setText((bucketCounter + 1), formatQty(q.value(QString("i_bucket%1").arg(bucketCounter)).toDouble()));
          sold->setText((bucketCounter + 1), formatQty(q.value(QString("s_bucket%1").arg(bucketCounter)).toDouble()));
          scrap->setText((bucketCounter + 1), formatQty(q.value(QString("c_bucket%1").arg(bucketCounter)).toDouble()));
          adjustments->setText((bucketCounter + 1), formatQty(q.value(QString("a_bucket%1").arg(bucketCounter)).toDouble()));
        }
      }
      while (q.next());
    }
  }
}
Esempio n. 8
0
void WomatlCluster::setId(int pWomatlid)
{
  _source = WoMaterial;
  _sourceId = pWomatlid;

  if (pWomatlid == -1)
    sPopulateInfo(-1);

  else
  {
    bool qual = FALSE;
    QString sql( "SELECT list.womatl_id AS womatlid, item_number,"
                 "       wo_id, item_invuom, item_descrip1, item_descrip2,"
                 "       list.womatl_qtyreq AS _qtyreq, list.womatl_qtyiss AS _qtyiss,"
                 "       formatQtyPer(list.womatl_qtyper) AS qtyper,"
                 "       formatScrap(list.womatl_scrap) AS scrap,"
                 "       formatQtyPer(list.womatl_qtyreq) AS qtyreq,"
                 "       formatQtyPer(list.womatl_qtyiss) AS qtyiss,"
                 "       formatQtyPer(list.womatl_qtywipscrap) AS qtywipscrap "
                 "FROM womatl AS list, womatl AS target, wo, itemsite, item "
                 "WHERE ( (list.womatl_wo_id=wo_id)"
                 " AND (target.womatl_wo_id=wo_id)"
                 " AND (list.womatl_itemsite_id=itemsite_id)"
                 " AND (itemsite_item_id=item_id)"
                 " AND (target.womatl_id=:womatl_id)"
                 " AND (list.womatl_issuemethod IN (" );

    if (_type & Push)
    {
      sql += "'S'";
      qual = TRUE;
    }

    if (_type & Pull)
    {
      if (qual)
        sql += ",";
      else
        qual = TRUE;

      sql += "'L'";
    }

    if (_type & Mixed)
    {
      if (qual)
        sql += ",";

      sql += "'M'";
    }

    sql += ")) );";

    XSqlQuery query;
    query.prepare(sql);
    query.bindValue(":womatl_id", pWomatlid);
    query.exec();
    if (query.first())
    {
      _womatl.prepare(sql);
      _womatl.bindValue(":womatl_id", pWomatlid);
      _womatl.exec();

      emit newId(pWomatlid);

      _valid = TRUE;
      _id = pWomatlid;

      _itemNumber->populate(query);
      _itemNumber->setId(pWomatlid);
    }
    else
    {
      _valid = FALSE;
      _woid = -1;
      _id = -1;

      emit newId(-1);
      emit valid(FALSE);

      _itemNumber->clear();
    }
  }
}
Esempio n. 9
0
void dspPoItemsByVendor::sFillList()
{
  _poitem->clear();

  QString sql( "SELECT pohead_id, poitem_id, pohead_number,"
	       "       poitem_status,"
               "       CASE WHEN(poitem_status='C') THEN <? value(\"closed\") ?>"
               "            WHEN(poitem_status='U') THEN <? value(\"unposted\") ?>"
               "            WHEN(poitem_status='O' AND ((poitem_qty_received-poitem_qty_returned) > 0) AND (poitem_qty_ordered>(poitem_qty_received-poitem_qty_returned))) THEN <? value(\"partial\") ?>"
               "            WHEN(poitem_status='O' AND ((poitem_qty_received-poitem_qty_returned) > 0) AND (poitem_qty_ordered=(poitem_qty_received-poitem_qty_returned))) THEN <? value(\"received\") ?>"
               "            WHEN(poitem_status='O') THEN <? value(\"open\") ?>"
               "            ELSE poitem_status"
               "       END AS poitemstatus,"
               "       CASE WHEN (itemsite_id IS NULL) THEN ( SELECT warehous_code"
               "                                              FROM warehous"
               "                                              WHERE (pohead_warehous_id=warehous_id) )"
               "            ELSE ( SELECT warehous_code"
               "                   FROM warehous"
               "                   WHERE (itemsite_warehous_id=warehous_id) )"
               "       END AS warehousecode,"
               "       COALESCE(item_number, (<? value(\"nonInv\") ?> || poitem_vend_item_number)) AS itemnumber,"
               "       COALESCE(item_descrip1, firstLine(poitem_vend_item_descrip)) AS itemdescrip,"
               "       COALESCE(uom_name, poitem_vend_uom) AS itemuom,"
               "       formatDate(poitem_duedate) AS f_duedate,"
               "       formatQty(poitem_qty_ordered) AS f_qtyordered,"
               "       formatQty(poitem_qty_received) AS f_qtyreceived,"
               "       formatQty(poitem_qty_returned) AS f_qtyreturned,"
               "       (poitem_duedate < CURRENT_DATE) AS late "
               "FROM pohead,"
               "     poitem LEFT OUTER JOIN"
               "     ( itemsite JOIN item"
               "       ON (itemsite_item_id=item_id) JOIN uom ON (item_inv_uom_id=uom_id))"
               "     ON (poitem_itemsite_id=itemsite_id) "
               "WHERE ((poitem_pohead_id=pohead_id)"
	       "<? if exists(\"warehous_id\") ?>"
	       " AND (((itemsite_id IS NULL) AND"
	       "       (pohead_warehous_id=<? value(\"warehous_id\") ?>) ) OR"
	       "      ((itemsite_id IS NOT NULL) AND"
	       "       (itemsite_warehous_id=<? value(\"warehous_id\") ?>) ) )"
	       "<? endif ?>"
	       "<? if exists(\"agentUsername\") ?>"
	       " AND (pohead_agent_username=<? value(\"agentUsername\") ?>)"
	       "<? endif ?>"
	       "<? if exists(\"poNumber\") ?>"
	       " AND (pohead_number=<? value(\"poNumber\") ?>)"
	       "<? endif ?>"
	       "<? if exists(\"openItems\") ?>"
	       " AND (poitem_status='O')"
	       "<? endif ?>"
	       "<? if exists(\"closedItems\") ?>"
	       " AND (poitem_status='C')"
	       "<? endif ?>"
	       " AND (pohead_vend_id=<? value(\"vend_id\") ?>) ) "
	       "ORDER BY poitem_duedate, pohead_number, poitem_linenumber;" );
  ParameterList params;
  setParams(params);
  MetaSQLQuery mql(sql);
  q = mql.toQuery(params);
  if (q.first())
  {
    XTreeWidgetItem *last = 0;
    do
    {
      last = new XTreeWidgetItem(_poitem, last,
				 q.value("pohead_id").toInt(),
				 q.value("poitem_id").toInt(),
				 q.value("pohead_number"),
				 q.value("warehousecode"),
				 q.value("poitemstatus"),
				 q.value("f_duedate"),
				 q.value("itemnumber"),
				 q.value("itemdescrip"),
				 q.value("itemuom"),
				 q.value("f_qtyordered"),
				 q.value("f_qtyreceived"),
				 q.value("f_qtyreturned") );
      last->setText(POITEM_STATUS_COL, q.value("poitem_status"));
      if (q.value("late").toBool())
        last->setTextColor(3, "red");
    }
    while (q.next());
  }
  else if (q.lastError().type() != QSqlError::None)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }
}
Esempio n. 10
0
static void
_pay_give_take (player *pl, command_reader& reader, _m_action act)
{
    if ( ((act == M_PAY) && !pl->has ("command.info.money.pay")) ||
            ((act == M_TAKE || act == M_GIVE) && !pl->has ("command.info.money.give")) )
    {
        pl->message ("§c * §7You are not allowed to do that§c.");
        return;
    }
    else if (reader.arg_count () != 3)
    {
        if (act == M_PAY)
            pl->message ("§c * §7Usage§c: §e/money pay §cplayer amount");
        else if (act == M_GIVE)
            pl->message ("§c * §7Usage§c: §e/money give §cplayer amount");
        else
            pl->message ("§c * §7Usage§c: §e/money take §cplayer amount");
        return;
    }

    std::string target_name = reader.next ();
    double amount = reader.next ().as_float ();

    if (act == M_PAY)
    {
        if (sutils::iequals (target_name, pl->get_username ()))
        {
            pl->message ("§c * §7You cannot send yourself money§c.");
            return;
        }

        if (amount < 0.01)
        {
            pl->message ("§c * §7Invalid amount§c.");
            return;
        }
        else if (amount > pl->bal)
        {
            double left = amount - pl->bal;
            if (left < 10000000.0)
                pl->message ("§c * §7You are §c$" + utils::format_number (left, 2) + " §7short§c.");
            else
                pl->message ("§c * §7Not enough money§c.");
            return;
        }
    }
    else if (act == M_TAKE)
    {
        amount = -amount;
        if (amount >= 0.0)
        {
            pl->message ("§c * §7Invalid amount§c.");
            return;
        }
    }
    else if (amount <= 0.0)
    {
        pl->message ("§c * §7Invalid amount§c.");
        return;
    }

    std::string colored_target_name;

    // the easy way
    player *target = pl->get_server ().get_players ().find (target_name.c_str ());
    if (target)
    {
        if (act == M_PAY)
            pl->bal -= amount;
        target->bal += amount;
        target_name.assign (target->get_username ());
        colored_target_name.assign (target->get_colored_username ());
    }
    else
    {
        // the hard way
        soci::session sql (pl->get_server ().sql_pool ());
        try
        {
            if (!sqlops::player_exists (sql, target_name.c_str ()))
            {
                pl->message ("§c * §7No such player§f: §c" + target_name);
                return;
            }
            if (act == M_PAY)
                pl->bal -= amount;
            sqlops::add_money (sql, target_name.c_str (), amount);
            colored_target_name.assign (sqlops::player_colored_name (sql,
                                        target_name.c_str (), pl->get_server ()));
            target_name.assign (sqlops::player_name (sql, target_name.c_str ()));
        }
        catch (const std::exception& ex)
        {
            pl->message ("§4 * §cAn error has occurred§4.");
            return;
        }
    }

    std::ostringstream ss;
    if (act == M_PAY)
        ss << "§7$" << utils::format_number (amount, 2)
           << " §ehas been sent to " << colored_target_name;
    else if (act == M_GIVE)
        ss << "§7$" << utils::format_number (amount, 2)
           << " §ehas been added to " << colored_target_name << "§e's account";
    else
        ss << "§7$" << utils::format_number (-amount, 2)
           << " §ehas been taken from " << colored_target_name << "§e's account";
    pl->message (ss.str ());
    if ((act != M_TAKE) && (target && target != pl))
    {
        ss.clear ();
        ss.str (std::string ());
        ss << pl->get_colored_username () << " §ehas sent you §7$"
           << utils::format_number (amount, 2);
        target->message (ss.str ());
    }

    if (act == M_TAKE)
        amount = -amount;
    pl->get_logger () (LT_SYSTEM) << pl->get_username () << " has " <<
                                  ((act == M_PAY) ? "sent" : ((act == M_GIVE) ? "given" : "taken"))
                                  << " $" << utils::format_number (amount, 2) <<
                                  ((act == M_TAKE) ? " from " : " to ") << target_name << std::endl;
    return;
}
Esempio n. 11
0
/*
 * /money -
 *
 * Displays the amount of money a player has.
 *
 * Permissions:
 *   - command.info.money
 *       Needed to execute the command.
 *   - command.info.money.others
 *       Required to display the amount of money _other_ players have.
 *   - command.info.money.pay
 *       Whether the player is allowed to send money out of their own account.
 *   - command.info.money.give
 *       Whether the player is allowed to give money to players _wihout_
 *       taking it out of their own account.
 *   - command.info.money.set
 *       Required to modify a player's balance directly.
 */
void
c_money::execute (player *pl, command_reader& reader)
{
    if (!pl->perm (this->get_exec_permission ()))
        return;

    if (!reader.parse (this, pl))
        return;

    bool someone_else = false;
    std::string target_name;
    if (!reader.has_next ())
    {
        target_name.assign (pl->get_username ());
    }
    else
    {
        std::string& a = reader.next ();
        if (sutils::iequals (a, "pay"))
        {
            do_pay (pl, reader);
            return;
        }
        else if (sutils::iequals (a, "give"))
        {
            do_give (pl, reader);
            return;
        }
        else if (sutils::iequals (a, "take"))
        {
            do_take (pl, reader);
            return;
        }
        else if (sutils::iequals (a, "set"))
        {
            do_set (pl, reader);
            return;
        }
        else
        {
            target_name = a;
            someone_else = true;
        }
    }

    //
    // /money [player]
    //
    std::ostringstream ss;
    if (someone_else)
    {
        if (!pl->has ("command.info.money.others"))
        {
            pl->message ("§c * §7You are not allowed to do that§c.");
            return;
        }

        if (sutils::iequals (target_name, pl->get_username ()))
            ss << "§2Balance§8: §a$§f" << utils::format_number (pl->bal, 2);
        else
        {
            player *target = pl->get_server ().get_players ().find (target_name.c_str ());
            if (target)
                ss << target->get_colored_username () << "§e's balance§f: §a$§f" << utils::format_number (target->bal, 2);
            else
            {
                soci::session sql (pl->get_server ().sql_pool ());

                try
                {
                    if (!sqlops::player_exists (sql, target_name.c_str ()))
                    {
                        pl->message ("§c * §7No such player§f: §c" + target_name);
                        return;
                    }

                    ss << sqlops::player_colored_name (sql, target_name.c_str (), pl->get_server ())
                       << "§2's balance§f: §a$§f" << utils::format_number (sqlops::get_money (sql, target_name.c_str ()), 2);
                }
                catch (const std::exception& ex)
                {
                    pl->message ("§4 * §cAn error has occurred§4.");
                    return;
                }
            }
        }
    }
    else
        ss << "§2Balance§f: §a$§f" << utils::format_number (pl->bal, 2);

    pl->message (ss.str ());
}
Esempio n. 12
0
int check_file(const char* filepath)
{
    const std::string filename = mooon::utils::CStringUtils::extract_filename(filepath);
    if (!mooon::db_proxy::is_sql_log_filename(filename))
    {
        fprintf(stderr, "[NOTSQLLOG] %s\n", filepath);
        return 1;
    }

    int fd = open(filepath, O_RDONLY);
    if (-1 == fd)
    {
        fprintf(stderr, "open %s failed: %m\n", filepath);
        return 1;
    }

    mooon::sys::CloseHelper<int> close_helper(fd);
    while (true)
    {
        int32_t length = 0;
        int bytes = read(fd, &length, sizeof(length));
        if (0 == bytes)
        {
            fprintf(stdout, "[NOTEND] %s\n", filepath);
            if (1 == mooon::argument::add_endtag->value())
            {
                if (write_endtag(filepath))
                {
                    fprintf(stdout, "add endtag ok: %s\n", filepath);
                }
                else
                {
                    fprintf(stderr, "[%s]: add endtag failed: %m\n", filepath);
                }
            }

            break;
        }
        if (bytes != sizeof(length))
        {
            fprintf(stderr, "read %s length error: %d\n", filepath, bytes);
            return 1;
        }
        if (0 == length)
        {
            fprintf(stdout, "[END] %s\n", filepath);
            break;
        }
        else
        {
            std::string sql(length, '\0');
            bytes = read(fd, const_cast<char*>(sql.data()), length);
            if (0 == bytes)
            {
                fprintf(stderr, "[BAD] %s\n", filepath);
                return 1;
            }

            ++sg_lines;
        }
    }

    return 0;
}
Esempio n. 13
0
void dspInvoiceRegister::sFillList()
{
  _gltrans->clear();

  QString sql( "SELECT gltrans_id, formatDate(gltrans_date) AS f_date, gltrans_source,"
               "       CASE WHEN(gltrans_doctype='IN') THEN 1"
               "            WHEN(gltrans_doctype='CM') THEN 2"
               "            WHEN(gltrans_doctype='DM') THEN 3"
               "            WHEN(gltrans_doctype='CD') THEN 4"
               "            ELSE -1"
               "       END AS altId,"
               "       CASE WHEN(gltrans_doctype='IN') THEN :invoice"
               "            WHEN(gltrans_doctype='CM') THEN :creditmemo"
               "            WHEN(gltrans_doctype='DM') THEN :debitmemo"
               "            WHEN(gltrans_doctype='CD') THEN :cashdeposit"
               "            ELSE gltrans_doctype"
               "       END AS doctype,"
               "       gltrans_docnumber,"
               "       CASE WHEN(gltrans_doctype='IN') THEN"
               "                (SELECT invchead_shipto_name"
               "                   FROM aropen LEFT OUTER JOIN"
               "                        invchead"
               "                          ON (invchead_id=aropen_cobmisc_id"
               "                          AND invchead_cust_id=aropen_cust_id)"
               "                  WHERE ((aropen_docnumber=gltrans_docnumber)"
               "                    AND  (aropen_doctype='I')))"
               "            ELSE firstLine(gltrans_notes)"
               "       END AS f_notes,"
               "       (formatGLAccount(accnt_id) || ' - ' || accnt_descrip) AS f_accnt,"
               "       CASE WHEN (gltrans_amount < 0) THEN formatMoney(ABS(gltrans_amount))"
               "            ELSE ''"
               "       END AS f_debit,"
               "       CASE WHEN (gltrans_amount < 0) THEN ABS(gltrans_amount)"
               "            ELSE 0"
               "       END AS debit,"
               "       CASE WHEN (gltrans_amount > 0) THEN formatMoney(gltrans_amount)"
               "            ELSE ''"
               "       END AS f_credit,"
               "       CASE WHEN (gltrans_amount > 0) THEN gltrans_amount"
               "            ELSE 0"
               "       END AS credit "
               "FROM gltrans, accnt "
               "WHERE ((gltrans_accnt_id=accnt_id)"
               " AND (gltrans_doctype IN ('IN', 'CM', 'DM', 'CD'))"
               " AND (gltrans_source = 'A/R')"
               " AND (gltrans_date BETWEEN :startDate AND :endDate)" );

  if (_selectedAccount->isChecked())
    sql += " AND (gltrans_accnt_id=:accnt_id)";

  sql += ") "
         "ORDER BY gltrans_date, gltrans_docnumber;";

  q.prepare(sql);
  _dates->bindValue(q);
  q.bindValue(":accnt_id", _account->id());
  q.bindValue(":invoice", tr("Invoice"));
  q.bindValue(":creditmemo", tr("Credit Memo"));
  q.bindValue(":debitmemo", tr("Debit Memo"));
  q.bindValue(":cashdeposit", tr("Customer Deposit"));
  q.exec();

  XTreeWidgetItem * parent = 0;
  XTreeWidgetItem * last = 0;
  QString date;
  double debit = 0.0, credit = 0.0;
  double totdebit = 0.0, totcredit = 0.0;
  while(q.next())
  {
    if(0 == parent || date != q.value("f_date").toString())
    {
      if(parent)
      {
        last = new XTreeWidgetItem(parent, last, -2, -2, QVariant(""), "", "", tr("Subtotal"), "", "", formatMoney(debit), formatMoney(credit));
        parent->setExpanded(TRUE);
      }

      date = q.value("f_date").toString();
      parent = new XTreeWidgetItem(_gltrans, parent, -1, -2, QVariant(date));
      last = 0;
      debit = 0.0;
      credit = 0.0;
    }

    last = new XTreeWidgetItem(parent, last, q.value("gltrans_id").toInt(),
                               q.value("altId").toInt(),
                               QVariant(""), q.value("gltrans_source"), q.value("doctype"),
                               q.value("gltrans_docnumber"), q.value("f_notes"), q.value("f_accnt"),
                               q.value("f_debit"), q.value("f_credit"));

    debit += q.value("debit").toDouble();
    totdebit += q.value("debit").toDouble();
    credit += q.value("credit").toDouble();
    totcredit += q.value("credit").toDouble();
  }

  if (q.lastError().type() != QSqlError::None)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }

  if(parent)
  {
    last = new XTreeWidgetItem(parent, last, -2, -2, QVariant(""), "", "", tr("Subtotal"), "", "", formatMoney(debit), formatMoney(credit));
    parent->setExpanded(TRUE);
  }

  last = new XTreeWidgetItem(_gltrans, parent, -3, -2, QVariant(""), "", "", tr("Total"), "", "", formatMoney(totdebit), formatMoney(totcredit));
}
Esempio n. 14
0
void dspPoItemsByItem::sFillList()
{
  _poitem->clear();

  QString sql( "SELECT pohead_id, poitem_id, warehous_code, pohead_number,"
	       "       poitem_status,"
               "       CASE WHEN(poitem_status='C') THEN <? value(\"closed\") ?>"
               "            WHEN(poitem_status='U') THEN <? value(\"unposted\") ?>"
               "            WHEN(poitem_status='O' AND ((poitem_qty_received-poitem_qty_returned) > 0) AND (poitem_qty_ordered>(poitem_qty_received-poitem_qty_returned))) THEN <? value(\"partial\") ?>"
               "            WHEN(poitem_status='O' AND ((poitem_qty_received-poitem_qty_returned) > 0) AND (poitem_qty_ordered=(poitem_qty_received-poitem_qty_returned))) THEN <? value(\"received\") ?>"
               "            WHEN(poitem_status='O') THEN <? value(\"open\") ?>"
               "            ELSE poitem_status"
               "       END AS poitemstatus,"
               "       vend_name,"
               "       formatDate(poitem_duedate) AS f_duedate, "
               "       formatQty(poitem_qty_ordered) AS f_qtyordered,"
               "       formatQty(poitem_qty_received) AS f_qtyreceived,"
               "       formatQty(poitem_qty_returned) AS f_qtyreturned,"
               "       (poitem_duedate < CURRENT_DATE) AS late "
               "FROM pohead, poitem, vend, itemsite, warehous "
               "WHERE ((poitem_pohead_id=pohead_id)"
               " AND (pohead_vend_id=vend_id)"
	       "<? if exists(\"warehous_id\") ?>"
	       " AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
	       "<? endif ?>"
	       "<? if exists(\"agentUsername\") ?>"
	       " AND (pohead_agent_username=<? value(\"agentUsername\") ?>)"
	       "<? endif ?>"
	       "<? if exists(\"openItems\") ?>"
	       " AND (poitem_status='O')"
	       "<? endif ?>"
	       "<? if exists(\"closedItems\") ?>"
	       " AND (poitem_status='C')"
	       "<? endif ?>"
	       " AND (poitem_itemsite_id=itemsite_id)"
	       " AND (itemsite_warehous_id=warehous_id)"
	       " AND (itemsite_item_id=<? value(\"item_id\") ?>)) "
	       "ORDER BY poitem_duedate;" );

  ParameterList params;
  setParams(params);
  MetaSQLQuery mql(sql);
  q = mql.toQuery(params);

  q.exec();
  if (q.first())
  {
    XTreeWidgetItem *last = 0;
    do
    {
      last = new XTreeWidgetItem(_poitem, last,
				 q.value("pohead_id").toInt(),
				 q.value("poitem_id").toInt(),
				 q.value("warehous_code"),
				 q.value("pohead_number"),
				 q.value("poitemstatus"),
				 q.value("vend_name"),
				 q.value("f_duedate"),
				 q.value("f_qtyordered"),
				 q.value("f_qtyreceived"),
				 q.value("f_qtyreturned") );
      last->setText(POITEM_STATUS_COL, q.value("poitem_status").toString());
      if (q.value("late").toBool())
        last->setTextColor(4, "red");
    }
    while (q.next());
  }
  else if (q.lastError().type() != QSqlError::None)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }
}
Esempio n. 15
0
void createItemSitesByClassCode::sSave()
{
    if (_warehouse->id() == -1)
    {
        QMessageBox::critical( this, tr("Select a Site"),
                               tr( "You must select a Site for this Item Site before creating it.\n" ) );
        _warehouse->setFocus();
        return;
    }

    if ( (_metrics->boolean("InterfaceToGL")) && (_costcat->id() == -1) )
    {
        QMessageBox::critical( this, tr("Cannot Create Item Sites"),
                               tr("You must select a Cost Category for these Item Sites before you may create them.") );
        _costcat->setFocus();
        return;
    }

    if (_plannerCode->id() == -1)
    {
        QMessageBox::critical( this, tr("Cannot Create Item Sites"),
                               tr("You must select a Planner Code for these Item Sites before you may create them.") );
        _plannerCode->setFocus();
        return;
    }

    if (_controlMethod->currentItem() == -1)
    {
        QMessageBox::critical( this, tr("Cannot Create Item Sites"),
                               tr("You must select a Control Method for these Item Sites before you may create them.") );
        _controlMethod->setFocus();
        return;
    }

    if(!_costNone->isChecked() && !_costAvg->isChecked()
            && !_costStd->isChecked() && !_costJob->isChecked())
    {
        QMessageBox::critical(this, tr("Cannot Save Item Site"),
                              tr("<p>You must select a Cost Method for this "
                                 "Item Site before you may save it.") );
        return;
    }

    if (_stocked->isChecked() && _reorderLevel->toDouble() == 0)
    {
        QMessageBox::critical( this, tr("Cannot Save Item Site"),
                               tr("<p>You must set a reorder level "
                                  "for a stocked item before you may save it.") );
        _reorderLevel->setFocus();
        return;
    }

    if ( _locationControl->isChecked() )
    {
        XSqlQuery locationid;
        locationid.prepare( "SELECT location_id "
                            "FROM location "
                            "WHERE (location_warehous_id=:warehous_id)"
                            "LIMIT 1;" );
        locationid.bindValue(":warehous_id", _warehouse->id());
        locationid.exec();
        if (!locationid.first())
        {
            QMessageBox::critical( this, tr("Cannot Save Item Site"),
                                   tr( "You must first create at least one valid "
                                       "Location for this Site before items may be "
                                       "multiply located." ) );
            return;
        }
    }

    QString sql( "INSERT INTO itemsite "
                 "( itemsite_item_id,"
                 "  itemsite_warehous_id, itemsite_qtyonhand, itemsite_value,"
                 "  itemsite_useparams, itemsite_useparamsmanual,"
                 "  itemsite_reorderlevel, itemsite_ordertoqty,"
                 "  itemsite_minordqty, itemsite_maxordqty, itemsite_multordqty,"
                 "  itemsite_safetystock, itemsite_cyclecountfreq,"
                 "  itemsite_leadtime, itemsite_eventfence, itemsite_plancode_id, itemsite_costcat_id,"
                 "  itemsite_supply, itemsite_createpr, itemsite_createwo,"
                 "  itemsite_sold, itemsite_soldranking,"
                 "  itemsite_stocked,"
                 "  itemsite_controlmethod, itemsite_perishable, itemsite_active,"
                 "  itemsite_loccntrl, itemsite_location_id, itemsite_location,"
                 "  itemsite_location_comments, itemsite_notes,"
                 "  itemsite_abcclass, itemsite_freeze, itemsite_datelastused,"
                 "  itemsite_ordergroup, itemsite_mps_timefence, "
                 "  itemsite_autoabcclass, itemsite_costmethod ) "
                 "SELECT item_id,"
                 "       :warehous_id, 0.0, 0.0,"
                 "       :itemsite_useparams, :itemsite_useparamsmanual,"
                 "       :itemsite_reorderlevel, :itemsite_ordertoqty,"
                 "       :itemsite_minordqty, :itemsite_maxordqty, :itemsite_multordqty,"
                 "       :itemsite_safetystock, :itemsite_cyclecountfreq,"
                 "       :itemsite_leadtime, :itemsite_eventfence, :itemsite_plancode_id, :itemsite_costcat_id,"
                 "       :itemsite_supply, :itemsite_createpr, :itemsite_createwo,"
                 "       :itemsite_sold, :itemsite_soldranking,"
                 "       :itemsite_stocked,"
                 "       :itemsite_controlmethod, :itemsite_perishable, TRUE,"
                 "       :itemsite_loccntrl, :itemsite_location_id, :itemsite_location,"
                 "       :itemsite_location_comments, '',"
                 "       :itemsite_abcclass, FALSE, startOfTime(),"
                 "       :itemsite_ordergroup, :itemsite_mps_timefence, "
                 "       FALSE, CASE WHEN(item_type='R') THEN 'N' WHEN(item_type='J') THEN 'J' ELSE :itemsite_costmethod END "
                 "FROM item "
                 "WHERE ( (item_id NOT IN ( SELECT itemsite_item_id"
                 "                          FROM itemsite"
                 "                          WHERE (itemsite_warehous_id=:warehous_id) ) )" );

    if (_classCode->isSelected())
        sql += " AND (item_classcode_id=:classcode_id)";
    else if (_classCode->isPattern())
        sql += " AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ :classcode_pattern)))";

    sql += ");";

    q.prepare(sql);
    q.bindValue(":itemsite_reorderlevel", _reorderLevel->toDouble());
    q.bindValue(":itemsite_ordertoqty", _orderUpToQty->toDouble());
    q.bindValue(":itemsite_minordqty", _minimumOrder->toDouble());
    q.bindValue(":itemsite_maxordqty", _maximumOrder->toDouble());
    q.bindValue(":itemsite_multordqty", _orderMultiple->toDouble());
    q.bindValue(":itemsite_safetystock", _safetyStock->toDouble());
    q.bindValue(":itemsite_cyclecountfreq", _cycleCountFreq->value());
    q.bindValue(":itemsite_leadtime", _leadTime->value());
    q.bindValue(":itemsite_eventfence", _eventFence->value());
    q.bindValue(":itemsite_plancode_id", _plannerCode->id());
    q.bindValue(":itemsite_costcat_id", _costcat->id());
    q.bindValue(":itemsite_useparams", QVariant(_useParameters->isChecked(), 0));
    q.bindValue(":itemsite_useparamsmanual", QVariant(_useParametersOnManual->isChecked(), 0));
    q.bindValue(":itemsite_supply", QVariant(_supply->isChecked(), 0));
    q.bindValue(":itemsite_createpr", QVariant(_createPr->isChecked(), 0));
    q.bindValue(":itemsite_createwo", QVariant(_createWo->isChecked(), 0));
    q.bindValue(":itemsite_sold", QVariant(_sold->isChecked(), 0));
    q.bindValue(":itemsite_stocked", QVariant(_stocked->isChecked(), 0));
    q.bindValue(":itemsite_loccntrl", QVariant(_locationControl->isChecked(), 0));
    q.bindValue(":itemsite_perishable", QVariant(_perishable->isChecked(), 0));
    q.bindValue(":itemsite_soldranking", _soldRanking->value());
    q.bindValue(":itemsite_location_comments", _locationComments->text().stripWhiteSpace());
    q.bindValue(":itemsite_abcclass", _abcClass->currentText());

    q.bindValue(":itemsite_ordergroup", _orderGroup->value());
    q.bindValue(":itemsite_mps_timefence", _mpsTimeFence->value());

    if (_useDefaultLocation->isChecked())
    {
        if (_location->isChecked())
        {
            q.bindValue(":itemsite_location", "");
            q.bindValue(":itemsite_location_id", _locations->id());
        }
        else if (_miscLocation->isChecked())
        {
            q.bindValue(":itemsite_location", _miscLocationName->text().stripWhiteSpace());
            q.bindValue(":itemsite_location_id", -1);
        }
    }
    else
    {
        q.bindValue(":itemsite_location", "");
        q.bindValue(":itemsite_location_id", -1);
    }

    if (_controlMethod->currentItem() == 0)
        q.bindValue(":itemsite_controlmethod", "N");
    else if (_controlMethod->currentItem() == 1)
        q.bindValue(":itemsite_controlmethod", "R");
    else if (_controlMethod->currentItem() == 2)
        q.bindValue(":itemsite_controlmethod", "L");
    else if (_controlMethod->currentItem() == 3)
        q.bindValue(":itemsite_controlmethod", "S");

    if(_costNone->isChecked())
        q.bindValue(":itemsite_costmethod", "N");
    else if(_costAvg->isChecked())
        q.bindValue(":itemsite_costmethod", "A");
    else if(_costStd->isChecked())
        q.bindValue(":itemsite_costmethod", "S");
    else if(_costJob->isChecked())
        q.bindValue(":itemsite_costmethod", "J");

    q.bindValue(":warehous_id", _warehouse->id());
    _classCode->bindValue(q);
    q.exec();

    omfgThis->sItemsitesUpdated();

    accept();
}
Esempio n. 16
0
void dspTimePhasedPlannedREByPlannerCode::sFillList()
{
  if(_useAveragePrice->isChecked() && !(_startEvalDate->isValid() && _endEvalDate->isValid()))
  {
    QMessageBox::information(this, tr("Average Price Requires Dates"),
                                   tr("The Average Price option requires that you specify a valid\n"
                                      "date range to evaluate the average price."));
    return;
  }

  _plannedRE->clear();
  _plannedRE->setColumnCount(1);

  QString       sql("SELECT ");

  bool show    = FALSE;
  int  columns = 1;
  QList<QTreeWidgetItem*> selected = _periods->selectedItems();
  for (int i = 0; i < selected.size(); i++)
  {
    PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i];
    if (show)
      sql += ",";
    else
      show = TRUE;

    sql += QString(" SUM(plannedCost(plancode_id, warehous_id, '%1', %2)) AS cost%3,")
	   .arg((_useStandardCost->isChecked()) ? 'S' : 'A')
	   .arg(cursor->id())
	   .arg(columns);

    if (_useListPrice->isChecked())
      sql += QString(" SUM(plannedRevenue(plancode_id, warehous_id, 'L', %1)) AS revenue%2 ")
	     .arg(cursor->id())
	     .arg(columns++);
    else
      sql += QString(" SUM(plannedRevenue(plancode_id, warehous_id, 'A', %1, date('%2'), date('%3'))) AS revenue%4 ")
	     .arg(cursor->id())
	     .arg(_startEvalDate->date().toString())    // NOT locale format
	     .arg(_endEvalDate->date().toString())      // NOT locale format
	     .arg(columns++);

    _plannedRE->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight);
    _columnDates.append(DatePair(cursor->startDate(), cursor->endDate()));
  }

  if (show)
  {
    sql += " FROM plancode, warehous";

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

    if (_plannerCode->isSelected())
    {
      if (_warehouse->isSelected())
        sql += " AND (plancode_id=:plancode_id)";
      else
        sql += " WHERE ( (plancode_id=:plancode_id)";
    }
    else if (_plannerCode->isPattern())
    {
      if (_warehouse->isSelected())
        sql += " AND (plancode_code ~ :plancode_pattern)";
      else
        sql += " WHERE ( (plancode_code ~ :plancode_pattern)";
    }

    if ( (_warehouse->isSelected()) || (!_plannerCode->isAll()) )
      sql += ");";
    else
      sql += ";";

    q.prepare(sql);
    _warehouse->bindValue(q);
    _plannerCode->bindValue(q);
    q.exec();
    if (q.first())
    {
      XTreeWidgetItem *cost    = new XTreeWidgetItem( _plannedRE, 0, QVariant(tr("Cost")),
                                                  formatMoney(q.value("cost1").toDouble()) );

      XTreeWidgetItem *revenue = new XTreeWidgetItem( _plannedRE, cost, 0, QVariant(tr("Revenue")),
                                                  formatMoney(q.value("revenue1").toDouble()) );

      XTreeWidgetItem *profit  = new XTreeWidgetItem( _plannedRE, revenue,  0, QVariant(tr("Gross Profit")),
                                                  formatMoney(q.value("revenue1").toDouble() - q.value("cost1").toDouble() ) );
                       
      for (int bucketCounter = 1; bucketCounter < columns; bucketCounter++)
      {
        cost->setText(bucketCounter, formatMoney(q.value(QString("cost%1").arg(bucketCounter)).toDouble()));
        revenue->setText(bucketCounter, formatMoney(q.value(QString("revenue%1").arg(bucketCounter)).toDouble()));

        profit->setText( bucketCounter,
                         formatMoney( q.value(QString("revenue%1").arg(bucketCounter)).toDouble() -
                                      q.value(QString("cost%1").arg(bucketCounter)).toDouble() ) );
      }
    }
  }
}
Esempio n. 17
0
void WomatlCluster::setWoid(int pWoid)
{
  _source = WorkOrder;
  _sourceId = pWoid;

  bool qual = FALSE;
  QString sql( "SELECT womatl_id AS womatlid, item_number,"
               "       wo_id, item_invuom, item_descrip1, item_descrip2,"
               "       womatl_qtyreq AS _qtyreq, womatl_qtyiss AS _qtyiss,"
               "       formatQtyPer(womatl_qtyper) AS qtyper,"
               "       formatScrap(womatl_scrap) AS scrap,"
               "       formatQtyPer(womatl_qtyreq) AS qtyreq,"
               "       formatQtyPer(womatl_qtyiss) AS qtyiss,"
               "       formatQtyPer(womatl_qtywipscrap) AS qtywipscrap "
               "FROM womatl, wo, itemsite, item "
               "WHERE ( (womatl_wo_id=wo_id)"
               " AND (womatl_itemsite_id=itemsite_id)"
               " AND (itemsite_item_id=item_id)"
               " AND (wo_id=:wo_id)"
               " AND (womatl_issuemethod IN (" );

  if (_type & Push)
  {
    sql += "'S'";
    qual = TRUE;
  }

  if (_type & Pull)
  {
    if (qual)
      sql += ",";
    else
      qual = TRUE;

    sql += "'L'";
  }

  if (_type & Mixed)
  {
    if (qual)
      sql += ",";

    sql += "'M'";
  }

  sql += ")) );";

  XSqlQuery query;
  query.prepare(sql);
  query.bindValue(":wo_id", pWoid);
  query.exec();
  if (query.first())
  {
    _womatl.prepare(sql);
    _womatl.bindValue(":wo_id", pWoid);
    _womatl.exec();
    _itemNumber->populate(query);
  }
  else
  {
    _id = -1;
    _woid = -1;
    _valid = FALSE;
    _required = 0.0;
    _issued  = 0.0;
    
    emit newId(-1);
    emit newQtyRequired(formatQtyPer(0.0));
    emit newQtyIssued(formatQtyPer(0.0));
    emit newQtyScrappedFromWIP(formatQtyPer(0.0));

    _itemNumber->clear();
  }
}
Esempio n. 18
0
void dspInventoryAvailabilityBySourceVendor::sFillList()
{
  _availability->clear();

  if ((_byDate->isChecked()) && (!_date->isValid()))
  {
    QMessageBox::critical(this, tr("Enter Valid Date"),
                          tr("<p>You have choosen to view Inventory Availability"
			     " as of a given date but have not indicated "
			     "the date.  Please enter a valid date.") );
    _date->setFocus();
    return;
  }

  if ((_byDates->isChecked()) && ( (!_startDate->isValid()) || (!_endDate->isValid()) ) )
  {
    QMessageBox::critical(this, tr("Enter Dates"),
                          tr("<p>You have choosen to view Inventory "
			     "Availability as of a given Start and End Date but "
			     "have not indicated the dates.  Please enter "
			     "valid dates." ) );
    _startDate->setFocus();
    return;
  }

  QString sql( "SELECT itemsite_id,"
               "       vend_number,"
               "       item_number, (item_descrip1 || ' ' || item_descrip2) AS description,"
               "       warehous_id, warehous_code, itemsite_leadtime,"
               "       formatQty(qoh) AS f_qoh,"
               "       formatQty(noNeg(qoh - allocated)) AS f_unallocated,"
               "       formatQty(noNeg(allocated)) AS f_allocated,"
               "       formatQty(ordered) AS f_ordered,"
               "       formatQty(reorderlevel) AS f_reorderlevel,"
               "       formatQty(outlevel) AS f_outlevel,"
               "       (qoh - allocated + ordered) AS available,"
               "       formatQty(qoh - allocated + ordered) AS f_available,"
               "       ((qoh - allocated + ordered) < 0) AS stockout,"
               "       ((qoh - allocated + ordered) <= reorderlevel) AS reorder "
               "FROM ( SELECT itemsite_id, vend_number,"
               "              item_number, item_descrip1, item_descrip2,"
               "              warehous_id, warehous_code, itemsite_leadtime,"
               "              CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel,"
               "              CASE WHEN(itemsite_useparams) THEN itemsite_ordertoqty ELSE 0.0 END AS outlevel,"
               "              itemsite_qtyonhand AS qoh," );

  if (_leadTime->isChecked())
    sql += " qtyAllocated(itemsite_id, itemsite_leadtime) AS allocated,"
           " qtyOrdered(itemsite_id, itemsite_leadtime) AS ordered ";

  else if (_byDays->isChecked())
    sql += " qtyAllocated(itemsite_id, :days) AS allocated,"
           " qtyOrdered(itemsite_id, :days) AS ordered ";

  else if (_byDate->isChecked())
    sql += " qtyAllocated(itemsite_id, (:date - CURRENT_DATE)) AS allocated,"
           " qtyOrdered(itemsite_id, (:date - CURRENT_DATE)) AS ordered ";

  else if (_byDates->isChecked())
    sql += " qtyAllocated(itemsite_id, :startDate, :endDate) AS allocated,"
           " qtyOrdered(itemsite_id, :startDate, :endDate) AS ordered ";

   sql += "FROM item, itemsite, warehous, vend, itemsrc "
          "WHERE ( (itemsite_active)"
          " AND (itemsite_item_id=item_id)"
          " AND (itemsrc_item_id=item_id)"
          " AND (itemsite_warehous_id=warehous_id)"
          " AND (itemsrc_vend_id=vend_id)";

  if (_selectedVendor->isChecked())
    sql += " AND (vend_id=:vend_id)";
  else if (_selectedVendorType->isChecked())
    sql += " AND (vend_vendtype_id=:vendtype_id)";
  else if (_vendorTypePattern->isChecked())
    sql += " AND (vend_vendtype_id IN (SELECT vendtype_id FROM vendtype WHERE (vendtype_code ~ :vendtype_code))) ";

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

  sql += ") ) AS data ";

  if (_showReorder->isChecked())
  {
    sql += "WHERE ( ((qoh - allocated + ordered) <= reorderlevel) ";

    if (_ignoreReorderAtZero->isChecked())
      sql += " AND (NOT ( ((qoh - allocated + ordered) = 0) AND (reorderlevel = 0)) ) ) ";
    else
      sql += ") ";
  }
  else if (_showShortages->isChecked())
    sql += "WHERE ((qoh - allocated + ordered) < 0) ";

  if (_preferences->boolean("ListNumericItemNumbersFirst"))
    sql += "ORDER BY toNumeric(item_number, 999999999999999), item_number, warehous_code DESC;";
  else
    sql += "ORDER BY vend_number, item_number, warehous_code DESC;";

  q.prepare(sql);
  q.bindValue(":days", _days->value());
  q.bindValue(":date", _date->date());
  q.bindValue(":startDate", _startDate->date());
  q.bindValue(":endDate", _endDate->date());
  q.bindValue(":vend_id", _vend->id());
  q.bindValue(":vendtype_id", _vendorTypes->id());
  q.bindValue(":vendtype_code", _vendorType->text().upper());
  _warehouse->bindValue(q);
  q.exec();
  XTreeWidgetItem * last = 0;
  while (q.next())
  {
    last = new XTreeWidgetItem( _availability, last,
                                q.value("itemsite_id").toInt(),
                                q.value("vend_number"), q.value("item_number"),
                                q.value("description"), q.value("warehous_code"),
                                q.value("itemsite_leadtime"), q.value("f_qoh"),
                                q.value("f_allocated"), q.value("f_unallocated"),
                                q.value("f_ordered"), q.value("f_reorderlevel"),
                                q.value("f_outlevel") );

    last->setText(11, q.value("f_available").toString());

    if (_byDates->isChecked())
      last->setTextColor(5, "grey");

    if (q.value("stockout").toBool())
      last->setTextColor(11, "red");
    else if (q.value("reorder").toBool())
      last->setTextColor(11, "orange");
  }
}
Esempio n. 19
0
void dspTimePhasedAvailability::sCalculate()
{
  _columnDates.clear();
  _availability->clear();
  _availability->setColumnCount(3);

  QString sql( "SELECT itemsite_id, itemtype,"
               "       item_number, uom_name, warehous_code,"
               "       reorderlevel " );

  int columns = 1;
  QList<QTreeWidgetItem*> selected = _periods->selectedItems();
  for (int i = 0; i < selected.size(); i++)
  {
    QString bucketname = QString("bucket%1").arg(columns++);
    sql += QString(", %1,"
                   "  'qty' AS %2_xtnumericrole,"
                   "  CASE WHEN (%3 < reorderlevel) THEN 'error' END AS %4_qtforegroundrole "    )
	   .arg(bucketname)
	   .arg(bucketname)
	   .arg(bucketname)
	   .arg(bucketname);
  }

  sql +=       "FROM ( "
               "SELECT itemsite_id,"
               "       CASE WHEN (item_type IN ('F', 'B', 'C', 'Y', 'R')) THEN 0"
               "            WHEN (item_type IN ('M')) THEN 1"
               "            WHEN (item_type IN ('P', 'O')) THEN 2"
               "            ELSE 0"
               "       END AS itemtype,"
               "       item_number, uom_name, warehous_code,"
               "       CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel ";

  columns = 1;
  for (int i = 0; i < selected.size(); i++)
  {
    PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i];
    QString bucketname = QString("bucket%1").arg(columns++);
    sql += QString(", qtyAvailable(itemsite_id, findPeriodStart(%1)) AS %2 " )
	   .arg(cursor->id())
	   .arg(bucketname);

    _availability->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight, true, bucketname);

    _columnDates.append(DatePair(cursor->startDate(), cursor->endDate()));
  }

  sql += " FROM itemsite, item, warehous, uom "
         "WHERE ((itemsite_item_id=item_id)"
         " AND (item_inv_uom_id=uom_id)"
         " AND (itemsite_warehous_id=warehous_id)";

  if (_warehouse->isSelected())
    sql += " AND (itemsite_warehous_id=:warehous_id)";
 
  if (_plannerCode->isSelected())
    sql += " AND (itemsite_plancode_id=:plancode_id)";
  else if (_plannerCode->isPattern())
    sql += " AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ :plancode_pattern)))";

  sql += ") ) AS data "
         "ORDER BY item_number;";

  q.prepare(sql);
  _warehouse->bindValue(q);
  _plannerCode->bindValue(q);
  q.exec();
  if (q.first())
  {
    _availability->populate(q, true);
  }
}
Esempio n. 20
0
void dspSummarizedGLTransactions::sFillList()
{
    _gltrans->clear();

    QString sql( "SELECT accnt_id, gltrans_id,"
                 "       account, accnt_descrip,"
                 "       gltrans_date, gltrans_source, gltrans_doctype, gltrans_docnumber,"
                 "       f_notes,"
                 "       debit, credit,"
                 "       gltrans_username, gltrans_created,"
                 "       CASE WHEN (level=0) THEN accnt_descrip"
                 "            ELSE f_notes"
                 "       END AS descrip_notes,"
                 "       'curr' AS debit_xtnumericrole,"
                 "       'curr' AS credit_xtnumericrole,"
                 "       0 AS debit_xtnumericrole,"
                 "       0 AS credit_xtnumericrole,"
                 "       CASE WHEN (debit=0) THEN '' END AS debit_qtdisplayrole,"
                 "       CASE WHEN (credit=0) THEN '' END AS credit_qtdisplayrole,"
                 "       level AS xtindentrole "
                 "FROM ( "
                 "SELECT DISTINCT accnt_id, -1 AS gltrans_id, 0 AS level,"
                 "       accnt_number, accnt_profit, accnt_sub, "
                 "       formatGLAccount(accnt_id) AS account, accnt_descrip,"
                 "       CAST(NULL AS DATE) AS gltrans_date, '' AS gltrans_source, '' AS gltrans_doctype, '' AS gltrans_docnumber,"
                 "       '' AS f_notes,"
                 "       SUM( CASE WHEN (gltrans_amount < 0) THEN (gltrans_amount * -1) "
                 "                        ELSE 0 "
                 "                   END ) AS debit, "
                 "       SUM( CASE WHEN (gltrans_amount > 0) THEN gltrans_amount "
                 "                       ELSE 0 "
                 "                  END ) AS credit, "
                 "       '' AS gltrans_username, CAST(NULL AS TIMESTAMP) AS gltrans_created "
                 "FROM gltrans, accnt "
                 "WHERE ( (gltrans_accnt_id=accnt_id)"
                 " AND (gltrans_date BETWEEN :startDate AND :endDate) ");

    if (_selectedSource->isChecked())
        sql += " AND (gltrans_source=:source)";

    if (_unpostedTransactions->isChecked())
        sql += " AND (NOT gltrans_posted)";
    else if (_postedTransactions->isChecked())
        sql += " AND (gltrans_posted)";

    sql +=       ") GROUP BY accnt_id, accnt_number, accnt_profit, accnt_sub, accnt_descrip "
                 "UNION "
                 "SELECT accnt_id, gltrans_id, 1 AS level,"
                 "       accnt_number, accnt_profit, accnt_sub, "
                 "       '' AS account, '' AS accnt_descrip,"
                 "       gltrans_date, gltrans_source, gltrans_doctype, gltrans_docnumber,"
                 "       firstLine(gltrans_notes) AS f_notes,"
                 "       CASE WHEN (gltrans_amount < 0) THEN (gltrans_amount * -1)"
                 "            ELSE 0"
                 "       END AS debit,"
                 "       CASE WHEN (gltrans_amount > 0) THEN gltrans_amount"
                 "            ELSE 0"
                 "       END AS credit,"
                 "       gltrans_username, gltrans_created "
                 "FROM gltrans, accnt "
                 "WHERE ( (gltrans_accnt_id=accnt_id)"
                 " AND (gltrans_date BETWEEN :startDate AND :endDate) ";

    if (_selectedSource->isChecked())
        sql += " AND (gltrans_source=:source)";

    if (_unpostedTransactions->isChecked())
        sql += " AND (NOT gltrans_posted)";
    else if (_postedTransactions->isChecked())
        sql += " AND (gltrans_posted)";

    sql += ") ) AS data "
           "ORDER BY accnt_number, accnt_profit, accnt_sub, level, gltrans_date DESC, gltrans_created;";
    q.prepare(sql);
    _dates->bindValue(q);
    q.bindValue(":source", _source->currentText());
    q.exec();
    if (q.first())
    {
        _gltrans->populate(q, true);
    }
}
Esempio n. 21
0
void enterPoReturn::sFillList()
{
  _poitem->clear();

  if (_po->id() != -1)
  {
    q.prepare( "SELECT 1, vendaddr_addr_id AS addr_id, pohead_dropship "
	       "FROM vendaddrinfo, pohead "
	       "WHERE ((vendaddr_id=pohead_vendaddr_id)"
	       "  AND  (pohead_id=:pohead_id))"
	       "UNION "
	       "SELECT 2, vend_addr_id AS addr_id, pohead_dropship "
	       "FROM vendinfo, pohead "
	       "WHERE ((vend_id=pohead_vend_id)"
	       "  AND  (pohead_id=:pohead_id)) "
	       "ORDER BY 1 "
	       "LIMIT 1;");
    q.bindValue(":pohead_id", _po->id());
    q.exec();
    if (q.first())
	{
      _returnAddr->setId(q.value("addr_id").toInt());
	  _dropShip->setChecked(q.value("pohead_dropship").toBool());
	}
    else if (q.lastError().type() != QSqlError::NoError)
    {
      systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
      return;
    }

    QString sql( "SELECT poitem_id, poitem_linenumber,"
               "       warehous_code, "
               "       COALESCE(item_number, <? value(\"nonInventory\") ?>) AS item_number,"
               "       CASE WHEN (LENGTH(TRIM(BOTH '    ' FROM poitem_vend_item_descrip)) <= 0) THEN "
               "                 (item_descrip1 || ' ' || item_descrip2) "
               "            ELSE poitem_vend_item_descrip "
               "       END AS itemdescription, "
               "       COALESCE(uom_name, <? value(\"na\") ?>) AS inv_uom,"
               "       poitem_vend_item_number, poitem_vend_uom,"
               "       poitem_qty_ordered,"
               "       poitem_qty_received,"
               "       poitem_qty_returned,"
               "       COALESCE( ( SELECT SUM(poreject_qty)"
               "                              FROM poreject"
               "                              WHERE ( (poreject_poitem_id=poitem_id)"
               "                               AND (NOT poreject_posted) ) ), 0 ) AS poitem_qty_toreturn, "
               "      'qty' AS poitem_qty_ordered_xtnumericrole, "
               "      'qty' AS poitem_qty_received_xtnumericrole, "
               "      'qty' AS poitem_qty_returned_xtnumericrole, "
               "      'qty' AS poitem_qty_toreturn_xtnumericrole "
               "FROM poitem LEFT OUTER JOIN "
               "     ( itemsite "
               "        JOIN item ON (itemsite_item_id=item_id) "
               "        JOIN uom ON (item_inv_uom_id=uom_id) "
               "        JOIN site() ON (itemsite_warehous_id=warehous_id) "
               "     ) ON (poitem_itemsite_id=itemsite_id) "
               "WHERE (poitem_pohead_id= <? value(\"pohead_id\") ?>) "
               "ORDER BY poitem_linenumber;" );
          
    ParameterList params;
    params.append("na", tr("N/A"));
    params.append("nonInventory", tr("Non-Inventory"));
    params.append("pohead_id", _po->id());
    MetaSQLQuery mql(sql);
    q = mql.toQuery(params);
    _poitem->populate(q);
    if (q.lastError().type() != QSqlError::NoError)
    {
      systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
      return;
    }
  }
}
Esempio n. 22
0
void dspExpediteExceptionsByPlannerCode::sFillList()
{
  _exception->clear();

  QString sql( "SELECT planord_id AS order_id, 1 AS order_code,"
               "       CASE WHEN (planord_type='W') THEN ('PW/O-' || formatPloNumber(planord_id))"
               "            WHEN (planord_type='P') THEN ('PP/O-' || formatPloNumber(planord_id))"
               "            ELSE TEXT(planord_number)"
               "       END AS order_number,"
               "       warehous_code, item_number, (item_descrip1 || ' ' || item_descrip2) AS item_descrip,"
               "       CASE WHEN (planord_type='W') THEN formatDate(planord_startdate)"
               "            ELSE formatDate(planord_duedate)"
               "       END AS f_keydate,"
               "       CASE WHEN (planord_type='W') THEN planord_startdate"
               "            ELSE planord_duedate"
               "       END AS keydate, :releaseOrder AS exception "
               "FROM planord, itemsite, item, warehous "
               "WHERE ( (planord_itemsite_id=itemsite_id)"
               " AND (itemsite_item_id=item_id)"
               " AND (itemsite_warehous_id=warehous_id)"
               " AND ( ( (planord_type='W') AND (planord_startdate <= (CURRENT_DATE + :days)) )"
               "    OR ( (planord_type='P') AND (planord_duedate <= (CURRENT_DATE + :days)) ) ) " );

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

  if (_plannerCode->isSelected())
    sql += " AND (itemsite_plancode_id=:plancode_id)";
  else if (_plannerCode->isPattern())
    sql += " AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ :plancode_pattern)))";

  sql += " ) ";

  sql += "UNION SELECT wo_id AS order_id, 2 AS order_code, ('W/O-' || formatWoNumber(wo_id)) AS order_number,"
         "             warehous_code, item_number, (item_descrip1 || ' ' || item_descrip2) AS item_descrip,"
         "             formatDate(wo_startdate) AS f_keydate, wo_startdate AS keydate, :startProduction AS exception "
         "FROM wo, itemsite, item, warehous "
         "WHERE ( (wo_itemsite_id=itemsite_id)"
         " AND (itemsite_item_id=item_id)"
         " AND (itemsite_warehous_id=warehous_id)"
         " AND (wo_status IN ('O', 'E', 'R'))"
         " AND (wo_startdate <= (CURRENT_DATE + :days))";

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

  if (_plannerCode->isSelected())
    sql += " AND (itemsite_plancode_id=:plancode_id)";
  else if (_plannerCode->isPattern())
    sql += " AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ :plancode_pattern)))";

  sql += " ) ";

  sql += "UNION SELECT wo_id AS order_id, 2 AS order_code, ('W/O-' || formatWoNumber(wo_id)) AS order_number,"
         "             warehous_code, item_number, (item_descrip1 || ' ' || item_descrip2) AS item_descrip,"
         "             formatDate(wo_startdate) AS f_keydate, wo_startdate AS keydate, :expediteProduction AS exception "
         "FROM wo, itemsite, item, warehous "
         "WHERE ( (wo_itemsite_id=itemsite_id)"
         " AND (itemsite_item_id=item_id)"
         " AND (itemsite_warehous_id=warehous_id)"
         " AND (wo_status='I')"
         " AND (wo_startdate <= (CURRENT_DATE + :days))";

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

  if (_plannerCode->isSelected())
    sql += " AND (itemsite_plancode_id=:plancode_id)";
  else if (_plannerCode->isPattern())
    sql += " AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ :plancode_pattern)))";

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

  q.prepare(sql);
  q.bindValue(":releaseOrder", tr("Release Order"));
  q.bindValue(":startProduction", tr("Start Production"));
  q.bindValue(":expediteProduction", tr("Expedite Production"));
  q.bindValue(":days", _days->value());
  _warehouse->bindValue(q);
  _plannerCode->bindValue(q);
  q.exec();
  if (q.first())
  {
    XTreeWidgetItem *last = NULL;

    do
      last = new XTreeWidgetItem( _exception, last, q.value("order_id").toInt(), q.value("order_code").toInt(),
                                q.value("order_number"), q.value("warehous_code"),
                                q.value("item_number"), q.value("item_descrip"),
                                q.value("f_keydate"), q.value("exception") );
    while (q.next());
  }
}
Esempio n. 23
0
void boo::sFillList()
{
  if (_item->itemType() == "J")
  {
    _closeWO->setEnabled(FALSE);
    _closeWO->setChecked(FALSE);
  }

  int locid = _finalLocation->id();
  q.prepare("SELECT location_id, (warehous_code || '-' || formatLocationName(location_id)) AS locationname"
            "  FROM location, warehous"
            " WHERE ( (NOT location_restrict)"
            "   AND   (location_warehous_id=warehous_id) ) "
            "UNION "
            "SELECT location_id, (warehous_code || '-' || formatLocationName(location_id)) AS locationname"
            "  FROM location, warehous, locitem"
            " WHERE ( (location_warehous_id=warehous_id)"
            "   AND   (location_restrict)"
            "   AND   (locitem_location_id=location_id)"
            "   AND   (locitem_item_id=:item_id) ) "
            "ORDER BY locationname;");
  q.bindValue(":item_id", _item->id());
  q.exec();
  _finalLocation->populate(q, locid);

  q.prepare( "SELECT boohead_id, boohead_docnum, boohead_revision,"
             "       boohead_revisiondate, boohead_final_location_id,"
             "       boohead_closewo "
             "FROM boohead "
             "WHERE ((boohead_item_id=:item_id) "
             "AND (boohead_rev_id=:revision_id));" );
  q.bindValue(":item_id", _item->id());
  q.bindValue(":revision_id", _revision->id());
  q.exec();
  if (q.first())
  {
    _booheadid = q.value("boohead_id").toInt();
    _documentNum->setText(q.value("boohead_docnum").toString());
    _revision->setNumber(q.value("boohead_revision").toString());
    _revisionDate->setDate(q.value("boohead_revisiondate").toDate());
    _finalLocation->setId(q.value("boohead_final_location_id").toInt());
    _closeWO->setChecked(q.value("boohead_closewo").toBool());
  }

  if (_revision->description() == "Inactive")
  {
	  _save->setEnabled(FALSE);
          _new->setEnabled(FALSE);
	  _documentNum->setEnabled(FALSE);
	  _revisionDate->setEnabled(FALSE);
	  _closeWO->setEnabled(FALSE);
	  _finalLocation->setEnabled(FALSE);
	  _booitem->setEnabled(FALSE);
  }

  if ((_revision->description() == "Pending") || (_revision->description() == "Active"))
  {
	  _save->setEnabled(TRUE);
          _new->setEnabled(TRUE);
	  _documentNum->setEnabled(TRUE);
	  _revisionDate->setEnabled(TRUE);
	  _closeWO->setEnabled(TRUE);
	  _finalLocation->setEnabled(TRUE);
	  _booitem->setEnabled(TRUE);
  }

  q.prepare( "SELECT MAX(booitem_execday) AS leadtime "
             "FROM booitem(:item_id,:revision_id);" );
  q.bindValue(":item_id", _item->id());
  q.bindValue(":revision_id",_revision->id());
  q.exec();
  if (q.first())
    _productionLeadTime->setText(q.value("leadtime").toString());

  QString sql( "SELECT booitem_id, booitem_seqnumber,"
               "       COALESCE(stdopn_number, <? value(\"none\")?> ) AS f_stdopnnumber,"
               "       wrkcnt_code, (booitem_descrip1 || ' ' || booitem_descrip2) AS description,"
               "       booitem_effective, booitem_expires,"
               "       booitem_execday,"
	       "       CASE WHEN (booitem_configtype<>'N') THEN 'emphasis'"
               "            WHEN (booitem_expires < CURRENT_DATE) THEN 'expired'"
               "            WHEN (booitem_effective >= CURRENT_DATE) THEN 'future'"
               "       END AS qtforegroundrole,"
	       "       CASE WHEN COALESCE(booitem_effective, startOfTime()) ="
               "                 startOfTime() THEN 'Always'"
	       "       END AS booitem_effective_qtdisplayrole,"
	       "       CASE WHEN COALESCE(booitem_expires, endOfTime()) >="
               "                 endOfTime() THEN 'Never'"
	       "       END AS booitem_expires_qtdisplayrole "
               "FROM wrkcnt,"
	       "     booitem(<? value(\"item_id\") ?>,<? value(\"revision_id\") ?>) LEFT OUTER JOIN stdopn ON (booitem_stdopn_id=stdopn_id) "
               "WHERE ((booitem_wrkcnt_id=wrkcnt_id)"
	       "<? if not exists(\"showExpired\") ?> "
	       " AND (booitem_expires > CURRENT_DATE)"
	       "<? endif ?>"
	       "<? if not exists(\"showFuture\") ?> "
	       " AND (booitem_effective <= CURRENT_DATE)"
	       "<? endif ?>"
	       ") "
	       "ORDER BY booitem_seqnumber, booitem_effective" );
  MetaSQLQuery mql(sql);
  ParameterList params;
  if (! setParams(params))
    return;
  q = mql.toQuery(params);
  _booitem->populate(q, true);
  if (q.lastError().type() != QSqlError::NoError)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }
}
bool CPfStadiumsDAOSQLiteEntity::deleteReg(CPfStadiums *reg)
{
    std::string sql("DELETE FROM PF_STADIUMS WHERE X_STADIUM=");
    sql += "'"+reg->getXStadium_str()+"'";
    return exec(sql);
}
Esempio n. 25
0
bool QgsWFSSharedData::computeFilter( QString& errorMsg )
{
  errorMsg.clear();
  mWFSFilter.clear();
  mSortBy.clear();

  QgsOgcUtils::GMLVersion gmlVersion;
  QgsOgcUtils::FilterVersion filterVersion;
  bool honourAxisOrientation = false;
  if ( mWFSVersion.startsWith( "1.0" ) )
  {
    gmlVersion = QgsOgcUtils::GML_2_1_2;
    filterVersion = QgsOgcUtils::FILTER_OGC_1_0;
  }
  else if ( mWFSVersion.startsWith( "1.1" ) )
  {
    honourAxisOrientation = !mURI.ignoreAxisOrientation();
    gmlVersion = QgsOgcUtils::GML_3_1_0;
    filterVersion = QgsOgcUtils::FILTER_OGC_1_1;
  }
  else
  {
    honourAxisOrientation = !mURI.ignoreAxisOrientation();
    gmlVersion = QgsOgcUtils::GML_3_2_1;
    filterVersion = QgsOgcUtils::FILTER_FES_2_0;
  }

  if ( !mURI.sql().isEmpty() )
  {
    QgsSQLStatement sql( mURI.sql() );

    const QgsSQLStatement::NodeSelect* select = dynamic_cast<const QgsSQLStatement::NodeSelect*>( sql.rootNode() );
    Q_ASSERT( select );
    QList<QgsSQLStatement::NodeColumnSorted*> orderBy = select->orderBy();
    Q_FOREACH ( QgsSQLStatement::NodeColumnSorted* columnSorted, orderBy )
    {
      if ( !mSortBy.isEmpty() )
        mSortBy += ",";
      mSortBy += columnSorted->column()->name();
      if ( !columnSorted->ascending() )
      {
        if ( mWFSVersion.startsWith( "2.0" ) )
          mSortBy += " DESC";
        else
          mSortBy += " D";
      }
    }

    QDomDocument filterDoc;
    QDomElement filterElem = QgsOgcUtils::SQLStatementToOgcFilter(
                               sql, filterDoc, gmlVersion, filterVersion, mLayerPropertiesList,
                               honourAxisOrientation, mURI.invertAxisOrientation(),
                               mCaps.mapUnprefixedTypenameToPrefixedTypename,
                               &errorMsg );
    if ( !errorMsg.isEmpty() )
    {
      errorMsg = tr( "SQL statement to OGC Filter error: " ) + errorMsg;
      return false;
    }
    if ( !filterElem.isNull() )
    {
      filterDoc.appendChild( filterElem );
      mWFSFilter = filterDoc.toString();
    }
  }
Esempio n. 26
0
void dspWoOperationsByWorkCenter::sFillList()
{
  _wooper->clear();

  q.prepare( "SELECT wrkcnt_descrip, warehous_code "
             "FROM wrkcnt, warehous "
             "WHERE ( (wrkcnt_warehous_id=warehous_id)"
             " AND (wrkcnt_id=:wrkcnt_id) );" );
  q.bindValue(":wrkcnt_id", _wrkcnt->id());
  q.exec();
  if (q.first())
  {
    _description->setText(q.value("wrkcnt_descrip").toString());
    _warehouse->setText(q.value("warehous_code").toString());
  }
  else if (q.lastError().type() != QSqlError::NoError)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }

  QString sql( "SELECT wooper.*, formatWoNumber(wo_id) AS wonumber, item_number,"
               "       CAST(wooper_scheduled AS DATE) AS scheduled,"
               "       CASE WHEN (wooper_stdopn_id <> -1) THEN ( SELECT stdopn_number FROM stdopn WHERE (stdopn_id=wooper_stdopn_id) )"
               "            ELSE ''"
               "       END AS stdoper,"
               "       (wooper_descrip1 || ' ' || wooper_descrip2) AS descrip, wo_status,"
               "       CASE WHEN (wooper_sucomplete) THEN 0"
               "            ELSE noNeg(wooper_sutime - wooper_suconsumed)"
               "       END AS setup,"
               "       CASE WHEN (wooper_rncomplete) THEN 0"
               "            ELSE noNeg(wooper_rntime - wooper_rnconsumed)"
               "       END AS run,"
               "       noNeg(wo_qtyord - wooper_qtyrcv) AS qtyremain, uom_name,"
               "       CASE WHEN(wo_ordtype='M') THEN :mrp"
               "            WHEN(wo_ordtype='P') THEN :mps"
               "            WHEN(wo_ordtype='S') THEN (:so||'-'||formatSoNumber(wo_ordid))"
               "            WHEN(wo_ordtype='W') THEN (:wo||'-'||formatWoNumber(wo_ordid))"
               "            WHEN(wo_ordtype IS NULL OR wo_ordtype='') THEN :manual"
               "            ELSE wo_ordtype"
               "       END AS source,"
               "       CASE WHEN (date(wooper_scheduled) < CURRENT_DATE) THEN 'error' END AS wooper_scheduled_qtforegroundrole,"
               "       '1' AS setup_xtnumericrole,"
               "       '1' AS run_xtnumericrole,"
               "       CASE WHEN (wooper_sucomplete) THEN :complete END AS setup_qtdisplayrole,"
               "       CASE WHEN (wooper_rncomplete) THEN :complete END AS run_qtdisplayrole,"
               "       'qty' AS qtyremain_xtnumericrole "
               "FROM wooper, wo, itemsite, item, uom "
               "WHERE ( (wooper_wo_id=wo_id)"
               " AND (wo_itemsite_id=itemsite_id)"
               " AND (itemsite_item_id=item_id)"
               " AND (item_inv_uom_id=uom_id)"
               " AND (DATE(wooper_scheduled) BETWEEN :startDate AND :endDate)"
               " AND (wooper_wrkcnt_id=:wrkcnt_id)" );

  if (_loadOnly->isChecked())
    sql += " AND ( ((wooper_sutime - wooper_suconsumed) > 0) OR ((wooper_rntime - wooper_rnconsumed) > 0) )";

  sql += ") "
         "ORDER BY wooper_scheduled, wo_number, wo_subnumber, wooper_seqnumber;";

  q.prepare(sql);
  _dates->bindValue(q);
  q.bindValue(":complete", tr("Complete"));
  q.bindValue(":wrkcnt_id", _wrkcnt->id());
  q.bindValue(":mrp", tr("MRP"));
  q.bindValue(":mps", tr("MPS"));
  q.bindValue(":so", tr("SO"));
  q.bindValue(":wo", tr("WO"));
  q.bindValue(":manual", tr("Manual"));
  q.exec();
  _wooper->populate(q, true);
  if (q.lastError().type() != QSqlError::NoError)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }
}
std::vector<CPfSeasons*>* CPfSeasonsDAOSQLite::findAll()
{
    std::string sql("SELECT * FROM PF_SEASONS ORDER BY N_YEAR ASC");
    return loadVector(sql);
}
Esempio n. 28
0
void dspTimePhasedBookingsByProductCategory::sFillList()
{
  if (!_periods->isPeriodSelected())
  {
    if (isVisible())
      QMessageBox::warning( this, tr("Select Calendar Periods"),
                            tr("Please select one or more Calendar Periods") );
    return;
  }

  _soitem->clear();
  _soitem->setColumnCount(3);

  _columnDates.clear();

  QString sql("SELECT prodcat_id, warehous_id, prodcat_code, warehous_code");

  if (_salesDollars->isChecked())
    sql += ", TEXT('$') AS uom";
  
  else if (_inventoryUnits->isChecked())
    sql += ", uom_name AS uom";

  else if (_capacityUnits->isChecked())
    sql += ", itemcapuom(item_id) AS uom";

  else if (_altCapacityUnits->isChecked())
    sql += ", itemaltcapuom(item_id) AS uom";

  int columns = 1;
  QList<QTreeWidgetItem*> selected = _periods->selectedItems();
  for (int i = 0; i < selected.size(); i++)
  {
    PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i];

    if (_salesDollars->isChecked())
      sql += QString(", SUM(bookingsByItemValue(itemsite_id, %2)) AS bucket%1")
	     .arg(columns++)
	     .arg(cursor->id());

    else if (_inventoryUnits->isChecked())
      sql += QString(", SUM(bookingsByItemQty(itemsite_id, %2)) AS bucket%1")
	     .arg(columns++)
	     .arg(cursor->id());

    else if (_capacityUnits->isChecked())
      sql += QString(", SUM(bookingsByItemQty(itemsite_id, %2) * itemcapinvrat(item_id)) AS bucket%1")
	     .arg(columns++)
	     .arg(cursor->id());

    else if (_altCapacityUnits->isChecked())
      sql += QString(", SUM(bookingsByItemQty(itemsite_id, %2) * itemaltcapinvrat(item_id)) AS bucket%1")
	     .arg(columns++)
	     .arg(cursor->id());

    _soitem->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight);

    _columnDates.append(DatePair(cursor->startDate(), cursor->endDate()));
  }

  sql += " FROM itemsite, item, uom, warehous, prodcat "
         "WHERE ( (itemsite_item_id=item_id)"
         " AND (item_inv_uom_id=uom_id)"
         " AND (itemsite_warehous_id=warehous_id)"
         " AND (item_prodcat_id=prodcat_id)";

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

  if (_productCategory->isSelected())
    sql += "AND (prodcat_id=:prodcat_id) ";
  else if (_productCategory->isPattern())
    sql += "AND (prodcat_code ~ :prodcat_pattern) ";

  sql += ") "
         "GROUP BY prodcat_id, warehous_id, prodcat_code, uom, warehous_code;";

  q.prepare(sql);
  _warehouse->bindValue(q);
  _productCategory->bindValue(q);
  q.exec();
  if (q.first())
  {
    Q3ValueVector<Numeric> totals(columns);;
    XTreeWidgetItem *last = 0;

    do
    {
      last = new XTreeWidgetItem( _soitem, last,
				 q.value("prodcat_id").toInt(),
				 q.value("warehous_id").toInt(),
				 q.value("prodcat_code"),
				 q.value("warehous_code"),
				 q.value("uom") );

      for (int column = 1; column < columns; column++)
      {
        QString bucketName = QString("bucket%1").arg(column);
        totals[column] += q.value(bucketName).toDouble();

        if ( (_inventoryUnits->isChecked()) || (_capacityUnits->isChecked()) || (_altCapacityUnits->isChecked()) )
          last->setText((column + 2), formatQty(q.value(bucketName).toDouble()));
        else if (_salesDollars->isChecked())
          last->setText((column + 2), formatMoney(q.value(bucketName).toDouble()));
      }
    }
    while (q.next());

    XTreeWidgetItem *total = new XTreeWidgetItem(_soitem, last, -1, QVariant(tr("Totals:")));
    for (int column = 1; column < columns; column++)
    {
      if ( (_inventoryUnits->isChecked()) || (_capacityUnits->isChecked()) || (_altCapacityUnits->isChecked()) )
        total->setText((column + 2), formatQty(totals[column].toDouble()));
      else if (_salesDollars->isChecked())
        total->setText((column + 2), formatMoney(totals[column].toDouble()));
    }
  }
}
Esempio n. 29
0
void dspPendingAvailability::sFillList()
{
  if (!checkParameters())
    return;

  _items->clear();

  QString sql( "SELECT itemsite_id, bomitem_seqnumber, item_number, item_descrip, uom_name,"
               "       pendalloc, formatQty(pendalloc) AS f_pendalloc,"
               "       formatQty(totalalloc + pendalloc) AS f_totalalloc,"
               "       qoh, formatQty(qoh) AS f_qoh,"
               "       (qoh + ordered - (totalalloc + pendalloc)) AS totalavail,"
               "       formatQty(qoh + ordered - (totalalloc + pendalloc)) AS f_totalavail,"
               "       reorderlevel "
               "FROM ( SELECT itemsite_id, bomitem_seqnumber, item_number,"
               "              (item_descrip1 || ' ' || item_descrip2) AS item_descrip, uom_name,"
               "              ((itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, bomitem_qtyper * (1 + bomitem_scrap))) * :buildQty) AS pendalloc,"
               "              qtyAllocated(itemsite_id, DATE(:buildDate)) AS totalalloc,"
               "              noNeg(itemsite_qtyonhand) AS qoh,"
               "              qtyOrdered(itemsite_id, DATE(:buildDate)) AS ordered,"
               "              CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel"
			   "       FROM itemsite, item, bomitem(:item_id), uom "
               "       WHERE ( (bomitem_item_id=itemsite_item_id)"
               "        AND (itemsite_item_id=item_id)"
               "        AND (item_inv_uom_id=uom_id)"
               "        AND (itemsite_warehous_id=:warehous_id)" );

  if (_effective->isNull())
    sql += " AND (CURRENT_DATE BETWEEN bomitem_effective AND (bomitem_expires-1))) ) AS data ";
  else
    sql += " AND (:effective BETWEEN bomitem_effective AND (bomitem_expires-1))) ) AS data ";

  sql += "ORDER BY bomitem_seqnumber";

  q.prepare(sql);
  q.bindValue(":buildQty", _qtyToBuild->toDouble());
  q.bindValue(":buildDate", _buildDate->date());
  q.bindValue(":warehous_id", _warehouse->id());
  q.bindValue(":item_id", _item->id());
  q.bindValue(":effective", _effective->date());
  q.exec();
  XTreeWidgetItem *last = 0;
  while (q.next())
  {
    if ( (!_showShortages->isChecked()) ||
         (q.value("totalavail").toDouble() < 0.0) )
    {
      last = new XTreeWidgetItem( _items, last, q.value("itemsite_id").toInt(),
				 q.value("bomitem_seqnumber"), q.value("item_number"),
				 q.value("item_descrip"), q.value("uom_name"),
				 q.value("f_pendalloc"), q.value("f_totalalloc"),
				 q.value("f_qoh"), q.value("f_totalavail")  );

      if (q.value("qoh").toDouble() < q.value("pendalloc").toDouble())
        last->setTextColor(6, "red");

      if (q.value("totalavail").toDouble() < 0.0)
        last->setTextColor(7, "red");
      else if (q.value("totalavail").toDouble() <= q.value("reorderlevel").toDouble())
        last->setTextColor(7, "orange");
    }
  }
}
Esempio n. 30
0
bool LogBook::createDatabaseStructures()
{
	bool result = true;

	QSqlDatabase db = QSqlDatabase::database("LogBook");
	// See if the tables alreadt exist.
	QStringList tableList = db.tables();
	bool systemTableExists = tableList.contains("logbook_system");

	// Get the last record, if it exists
	QSqlQuery query(db);
	if (!systemTableExists || query.exec("SELECT last_script_run FROM logbook_system")) {
		int lastFile = 0;
		if (systemTableExists) {
			while (query.next()) {
				lastFile = query.value(0).toInt();
			}
		}

		QString path = ":/logbook/";
		QDir dir(path);
		QStringList entries = dir.entryList(QDir::Files, QDir::Name);
		QListIterator<QString> entriesIterator(entries);
		QSqlQuery updateQuery(db);
		updateQuery.prepare("UPDATE logbook_system SET last_script_run = :new WHERE last_script_run = :old");
		while (entriesIterator.hasNext() && result) {
			QString fileName = entriesIterator.next();
			int currentFile = fileName.section(".", 0, 0).toInt();
			if (fileName.endsWith("sql") &&  currentFile > lastFile) {
				if (!processSqlFile(QString(path).append(fileName))) {
					result = false;
				} else {
					//If this is the first file, it MUST create logbook_system
					if (!systemTableExists) {
						if(query.exec("SELECT last_script_run FROM logbook_system")){
							if(query.exec("INSERT INTO logbook_system (last_script_run) VALUES (0)")) {
								systemTableExists = true;
							} else {
								result = false;
								qDebug() << "LogBook: Error updateing system table 1.  Error is: " << query.lastError();
							}

						} else {
							result = false;
							qDebug() << "LogBook: Error reading system table 1.  Error is: " << query.lastError();
						}
					}

					// update the record
					updateQuery.bindValue(":new", currentFile);
					updateQuery.bindValue(":old", lastFile);
					QString sql("UPDATE logbook_system SET last_script_run = ");
					sql.append(QVariant(currentFile).toString()).append(" WHERE last_script_run = ").append(QVariant(lastFile).toString());
					if (query.exec(sql)) {
						lastFile = currentFile;
					} else {
						result = false;
						qDebug() << "LogBook: Error updateing system table; bind values are ("
								 << updateQuery.boundValues() << ").  \n\tError is: " << query.lastError()
								 << "\n\tThe query was: " << query.lastQuery() ;
					}
				}
			}
		}
	} else {
		result = false;
		qDebug() << "LogBook: Error reading system table 2.  Error is: " << query.lastError();
	}


	return result;
}