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;
}
}
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;
}
}
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);
}
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") );
}
}
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.") );
}
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");
}
}
}
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());
}
}
}
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();
}
}
}
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;
}
}
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;
}
/*
* /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 ());
}
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;
}
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));
}
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;
}
}
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();
}
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() ) );
}
}
}
}
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();
}
}
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");
}
}
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);
}
}
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);
}
}
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;
}
}
}
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());
}
}
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);
}
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();
}
}
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);
}
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()));
}
}
}
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");
}
}
}
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;
}