void dspTimePhasedCapacityByWorkCenter::sFillList() { _columnDates.clear(); _load->setColumnCount(1); QString sql("SELECT wrkcnt_id, wrkcnt_code "); int columns = 1; QList<QTreeWidgetItem*> selected = _periods->selectedItems(); for (int i = 0; i < selected.size(); i++) { PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i]; sql += QString(", formatTime(workCenterCapacity(wrkcnt_id, %1)) AS bucket%2") .arg(cursor->id()) .arg(columns++); _load->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight); _columnDates.append(DatePair(cursor->startDate(), cursor->endDate())); } sql += " FROM wrkcnt "; if (_warehouse->isSelected()) sql += "WHERE (wrkcnt_warehous_id=:warehous_id) "; sql += "ORDER BY wrkcnt_code;"; q.prepare(sql); _warehouse->bindValue(q); q.exec(); _load->populate(q); }
void displayTimePhased::sFillList() { ParameterList params; if(!setParams(params)) return; if(_data->_baseColumns == -1) _data->_baseColumns = list()->columnCount(); list()->clear(); list()->setColumnCount(_data->_baseColumns); _columnDates.clear(); _column = 0; QList<XTreeWidgetItem*> selected = _data->_periods->selectedItems(); for (int i = 0; i < selected.size(); i++) { PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i]; QString bucketname = QString("bucket_%1").arg(cursor->id()); list()->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight, true, bucketname); _columnDates.append(DatePair(cursor->startDate(), cursor->endDate())); } display::sFillList(); }
void dspTimePhasedBookingsByCustomer::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(2); _columnDates.clear(); QString sql("SELECT cust_id, cust_number, cust_name"); int columns = 1; QList<QTreeWidgetItem*> selected = _periods->selectedItems(); for (int i = 0; i < selected.size(); i++) { PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i]; QString bucketname = QString("bucket%1").arg(columns++); sql += QString(", bookingsByCustomerValue(cust_id, %1) AS %2," " 'curr' AS %3_xtnumericrole, 0 AS %4_xttotalrole ") .arg(cursor->id()) .arg(bucketname) .arg(bucketname) .arg(bucketname); _soitem->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight, true, bucketname); _columnDates.append(DatePair(cursor->startDate(), cursor->endDate())); } sql += " FROM cust "; if (_customerType->isSelected()) sql += "WHERE (cust_custtype_id=:custtype_id) "; else if (_customerType->isPattern()) sql += "WHERE (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ :custtype_pattern))) "; sql += "ORDER BY cust_number;"; q.prepare(sql); _customerType->bindValue(q); q.exec(); _soitem->populate(q); }
void dspTimePhasedProductionByItem::sCalculate() { _columnDates.clear(); _production->setColumnCount(3); QString sql("SELECT itemsite_id, item_number, warehous_code, uom_name"); int columns = 1; QList<QTreeWidgetItem*> selected = _periods->selectedItems(); for (int i = 0; i < selected.size(); i++) { PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i]; QString bucketname = QString("bucket%1").arg(columns++); sql += QString(", summProd(itemsite_id, %1) AS %2," " 'qty' AS %3_xtnumericrole ") .arg(cursor->id()) .arg(bucketname) .arg(bucketname); _production->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight, true, bucketname); _columnDates.append(DatePair(cursor->startDate(), cursor->endDate())); } sql += " FROM itemsite, item, uom, warehous " "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 += ") " "ORDER BY item_number;"; q.prepare(sql); _warehouse->bindValue(q); _plannerCode->bindValue(q); q.exec(); _production->populate(q); }
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 dspTimePhasedOpenAPItems::sFillCustom() { if (!_periods->isPeriodSelected()) { if (isVisible()) QMessageBox::warning( this, tr("Select Calendar Periods"), tr("Please select one or more Calendar Periods") ); return; } _columnDates.clear(); _apopen->clear(); _apopen->setColumnCount(2); QString sql("SELECT vend_id, vend_number, vend_name"); int columns = 1; QList<QTreeWidgetItem*> selected = _periods->selectedItems(); for (int i = 0; i < selected.size(); i++) { PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i]; sql += QString(", openAPItemsValue(vend_id, %2) AS bucket%1") .arg(columns++) .arg(cursor->id()); _apopen->addColumn(formatDate(cursor->startDate()), _bigMoneyColumn, Qt::AlignRight); _columnDates.append(DatePair(cursor->startDate(), cursor->endDate())); } sql += " FROM vend "; if (_selectedVendor->isChecked()) sql += "WHERE (vend_id=:vend_id)"; else if (_selectedVendorType->isChecked()) sql += "WHERE (vend_vendtype_id=:vendtype_id)"; else if (_vendorTypePattern->isChecked()) sql += "WHERE (vend_vendtype_id IN (SELECT vendtype_id FROM vendtype WHERE (vendtype_code ~ :vendtype_code))) "; sql += "ORDER BY vend_number;"; q.prepare(sql); q.bindValue(":vend_id", _vend->id()); q.bindValue(":vendtype_id", _vendorTypes->id()); q.bindValue(":vendtype_code", _vendorType->text().upper()); q.exec(); if (q.first()) { Q3ValueVector<Numeric> totals(columns);; XTreeWidgetItem *last = 0; do { double lineTotal = 0.0; last = new XTreeWidgetItem( _apopen, last, q.value("vend_id").toInt(), q.value("vend_number"), q.value("vend_name") ); for (int column = 1; column < columns; column++) { QString bucketName = QString("bucket%1").arg(column); last->setText((column + 1), formatMoney(q.value(bucketName).toDouble())); totals[column] += q.value(bucketName).toDouble(); lineTotal += q.value(bucketName).toDouble(); } if (lineTotal == 0.0) { delete last; last = _apopen->topLevelItem(_apopen->topLevelItemCount() - 1); } } while (q.next()); // Add the totals row XTreeWidgetItem *total = new XTreeWidgetItem(_apopen, last, -1, QVariant(tr("Totals:"))); for (int column = 1; column < columns; column++) total->setText((column + 1), formatMoney(totals[column].toDouble())); } }
void dspTimePhasedSalesByItem::sCalculate() { if (!_periods->isPeriodSelected()) { if (isVisible()) QMessageBox::warning( this, tr("Select Calendar Periods"), tr("Please select one or more Calendar Periods") ); return; } QString sql("SELECT itemsite_id, item_number"); _sohist->clear(); _sohist->setColumnCount(3); _columnDates.clear(); if (_salesDollars->isChecked()) sql += ", TEXT('$') AS uom, warehous_code"; else if (_inventoryUnits->isChecked()) sql += ", uom_name AS uom, warehous_code"; int columns = 1; QList<QTreeWidgetItem*> selected = _periods->selectedItems(); for (int i = 0; i < selected.size(); i++) { PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i]; QString bucketname = QString("bucket%1").arg(columns++); if (_salesDollars->isChecked()) sql += QString(", shipmentsByItemValue(itemsite_id, %1) AS %2," " 'curr' AS %3_xtnumericrole, 0 AS %4_xttotalrole ") .arg(cursor->id()) .arg(bucketname) .arg(bucketname) .arg(bucketname); else if (_inventoryUnits->isChecked()) sql += QString(", shipmentsByItemQty(itemsite_id, %1) AS %2," " 'qty' AS %3_xtnumericrole, 0 AS %4_xttotalrole ") .arg(cursor->id()) .arg(bucketname) .arg(bucketname) .arg(bucketname); _sohist->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight, true, bucketname); _columnDates.append(DatePair(cursor->startDate(), cursor->endDate())); } sql += " FROM itemsite, item, uom, warehous " "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 (_productCategory->isSelected()) sql += "AND (item_prodcat_id=:prodcat_id)"; else if (_productCategory->isPattern()) sql += "AND (item_prodcat_id IN (SELECT prodcat_id FROM prodcat WHERE (prodcat_code ~ :prodcat_pattern))) "; sql += ") " "ORDER BY item_number;"; q.prepare(sql); _warehouse->bindValue(q); _productCategory->bindValue(q); q.exec(); _sohist->populate(q); }
void dspTimePhasedOpenARItems::sFillCustom() { XSqlQuery dspFillCustom; if (!_periods->isPeriodSelected()) { if (isVisible()) QMessageBox::warning( this, tr("Select Calendar Periods"), tr("Please select one or more Calendar Periods") ); return; } _columnDates.clear(); list()->setColumnCount(2); QString sql("SELECT cust_id, cust_number, cust_name"); QStringList linetotal; int columns = 1; QList<XTreeWidgetItem*> selected = _periods->selectedItems(); for (int i = 0; i < selected.size(); i++) { PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i]; QString bucketname = QString("bucket%1").arg(columns++); sql += QString(", openARItemsValue(cust_id, %2) AS %1," " 'curr' AS %3_xtnumericrole, 0 AS %4_xttotalrole") .arg(bucketname) .arg(cursor->id()) .arg(bucketname) .arg(bucketname); list()->addColumn(formatDate(cursor->startDate()), _bigMoneyColumn, Qt::AlignRight, true, bucketname); _columnDates.append(DatePair(cursor->startDate(), cursor->endDate())); linetotal << QString("openARItemsValue(cust_id, %2)").arg(cursor->id()); } list()->addColumn(tr("Total"), _bigMoneyColumn, Qt::AlignRight, true, "linetotal"); sql += ", " + linetotal.join("+") + " AS linetotal," " 'curr' AS linetotal_xtnumericrole," " 0 AS linetotal_xttotalrole," " (" + linetotal.join("+") + ") = 0.0 AS xthiddenrole " "FROM custinfo LEFT OUTER JOIN custgrpitem ON (cust_id = custgrpitem_cust_id) " "<? if exists('cust_id') ?>" "WHERE (cust_id=<? value('cust_id') ?>)" "<? elseif exists('custtype_id') ?>" "WHERE (cust_custtype_id=<? value('custtype_id') ?>)" "<? elseif exists('custgrp_id') ?>" "WHERE (custgrpitem_custgrp_id=<? value('custgrp_id') ?>)" "<? elseif exists('custtype_pattern') ?>" "WHERE (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ <? value('custtype_pattern') ?>))) " "<? endif ?>" "ORDER BY cust_number;"; MetaSQLQuery mql(sql); ParameterList params; if (! setParams(params)) return; dspFillCustom = mql.toQuery(params); list()->populate(dspFillCustom); if (dspFillCustom.lastError().type() != QSqlError::NoError) { systemError(this, dspFillCustom.lastError().databaseText(), __FILE__, __LINE__); return; } }
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<XTreeWidgetItem*> selected = _periods->selectedItems(); for (int i = 0; i < selected.size(); i++) { PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i]; QString bucketname = QString("bucket%1").arg(columns++); if (_salesDollars->isChecked()) sql += QString(", SUM(bookingsByItemValue(itemsite_id, %1)) AS %2," " 'curr' AS %3_xtnumericrole, 0 AS %4_xttotalrole ") .arg(cursor->id()) .arg(bucketname) .arg(bucketname) .arg(bucketname); else if (_inventoryUnits->isChecked()) sql += QString(", SUM(bookingsByItemQty(itemsite_id, %1)) AS %2," " 'qty' AS %3_xtnumericrole, 0 AS %4_xttotalrole ") .arg(cursor->id()) .arg(bucketname) .arg(bucketname) .arg(bucketname); else if (_capacityUnits->isChecked()) sql += QString(", SUM(bookingsByItemQty(itemsite_id, %1) * itemcapinvrat(item_id)) AS %2," " 'qty' AS %3_xtnumericrole, 0 AS %4_xttotalrole ") .arg(cursor->id()) .arg(bucketname) .arg(bucketname) .arg(bucketname); else if (_altCapacityUnits->isChecked()) sql += QString(", SUM(bookingsByItemQty(itemsite_id, %1) * itemaltcapinvrat(item_id)) AS %2," " 'qty' AS %3_xtnumericrole, 0 AS %4_xttotalrole ") .arg(cursor->id()) .arg(bucketname) .arg(bucketname) .arg(bucketname); _soitem->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight, true, bucketname); _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(); _soitem->populate(q, true); }
void dspTimePhasedSalesByProductCategory::sCalculate() { if (!_periods->isPeriodSelected()) { if (isVisible()) QMessageBox::warning( this, tr("Select Calendar Periods"), tr("Please select one or more Calendar Periods") ); return; } _sohist->clear(); _sohist->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(shipmentsByItemValue(itemsite_id, %2)) AS bucket%1") .arg(columns++) .arg(cursor->id()); else if (_inventoryUnits->isChecked()) sql += QString(", SUM(shipmentsByItemQty(itemsite_id, %2)) AS bucket%1") .arg(columns++) .arg(cursor->id()); else if (_capacityUnits->isChecked()) sql += QString(", SUM(shipmentsByItemQty(itemsite_id, %2) * itemcapinvrat(item_id)) AS bucket%1") .arg(columns++) .arg(cursor->id()); else if (_altCapacityUnits->isChecked()) sql += QString(", SUM(shipmentsByItemQty(itemsite_id, %2) * itemaltcapinvrat(item_id)) AS bucket%1") .arg(columns++) .arg(cursor->id()); _sohist->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( _sohist, last, q.value("prodcat_id").toInt(), q.value("warehous_id").toInt(), q.value("prodcat_code"), q.value("uom"), q.value("warehous_code") ); 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()); // Add the totals row XTreeWidgetItem *total = new XTreeWidgetItem(_sohist, 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 dspTimePhasedSalesByCustomerGroup::sFillList() { _sohist->clear(); if (!_periods->isPeriodSelected()) return; _sohist->clear(); _sohist->setColumnCount(2); QString sql("SELECT cust_id, cust_number, cust_name"); int columns = 1; QList<XTreeWidgetItem*> selected = _periods->selectedItems(); for (int i = 0; i < selected.size(); i++) { PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i]; QString bucketname = QString("bucket%1").arg(columns++); if (_productCategory->isSelected()) sql += QString(", shipmentsByCustomerValue(cust_id, %1, :prodcat_id) AS %2," " 'curr' AS %3_xtnumericrole, 0 AS %4_xttotalrole ") .arg(cursor->id()) .arg(bucketname) .arg(bucketname) .arg(bucketname); else if (_productCategory->isPattern()) sql += QString(", shipmentsByCustomerValue(cust_id, %1, :prodcat_pattern) AS %2," " 'curr' AS %3_xtnumericrole, 0 AS %4_xttotalrole ") .arg(cursor->id()) .arg(bucketname) .arg(bucketname) .arg(bucketname); else sql += QString(", shipmentsByCustomerValue(cust_id, %1) AS %2," " 'curr' AS %3_xtnumericrole, 0 AS %4_xttotalrole ") .arg(cursor->id()) .arg(bucketname) .arg(bucketname) .arg(bucketname); _sohist->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight, true, bucketname); _columnDates.append(DatePair(cursor->startDate(), cursor->endDate())); } sql += " FROM cust, custgrp, custgrpitem " "WHERE ( (custgrpitem_cust_id=cust_id)" " AND (custgrpitem_custgrp_id=custgrp_id)"; if (_customerGroup->isSelected()) sql += " AND (custgrp_id=:custgrp_id)"; else if (_customerGroup->isPattern()) sql += " AND (custgrp_name ~ :custgrp_pattern)"; sql += ") " "ORDER BY cust_number;"; q.prepare(sql); _customerGroup->bindValue(q); _productCategory->bindValue(q); q.exec(); _sohist->populate(q); }
void dspTimePhasedProductionByPlannerCode::sCalculate() { _columnDates.clear(); _production->setColumnCount(3); QString sql("SELECT plancode_id, warehous_id, plancode_code, warehous_code, "); 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]; QString bucketname = QString("bucket%1").arg(columns++); if (_inventoryUnits->isChecked()) sql += QString(", SUM(summProd(itemsite_id, %1)) AS %2," " 'qty' AS %3_xtnumericrole ") .arg(cursor->id()) .arg(bucketname) .arg(bucketname); else if (_capacityUnits->isChecked()) sql += QString(", SUM(summProd(itemsite_id, %1) * itemcapinvrat(item_id)) AS %2," " 'qty' AS %3_xtnumericrole ") .arg(cursor->id()) .arg(bucketname) .arg(bucketname); else if (_altCapacityUnits->isChecked()) sql += QString(", SUM(summProd(itemsite_id, %1) * itemaltcapinvrat(item_id)) AS %2," " 'qty' AS %3_xtnumericrole ") .arg(cursor->id()) .arg(bucketname) .arg(bucketname); _production->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight, true, bucketname); _columnDates.append(DatePair(cursor->startDate(), cursor->endDate())); } sql += " FROM itemsite, item, uom, warehous, plancode " "WHERE ((itemsite_item_id=item_id)" " AND (item_inv_uom_id=uom_id)" " AND (itemsite_warehous_id=warehous_id)" " AND (itemsite_plancode_id=plancode_id)"; if (!_showInactive->isChecked()) sql += " AND (itemsite_active)"; if (_warehouse->isSelected()) sql += " AND (itemsite_warehous_id=:warehous_id)"; if (_plannerCode->isSelected()) sql += " AND (plancode_id=:plancode_id)"; else if (_plannerCode->isPattern()) sql += " AND (plancode_code ~ :plancode_pattern)"; sql += " ) " "GROUP BY plancode_id, warehous_id, plancode_code, warehous_code, uom;"; q.prepare(sql); _warehouse->bindValue(q); _plannerCode->bindValue(q); q.exec(); _production->populate(q, TRUE); }
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 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 dspTimePhasedSalesByCustomerByItem::sFillList() { _sohist->clear(); if (!_periods->isPeriodSelected()) return; _sohist->clear(); _sohist->setColumnCount(2); QString sql("SELECT cust_id, cust_number, cust_name"); int columns = 1; QList<QTreeWidgetItem*> selected = _periods->selectedItems(); for (int i = 0; i < selected.size(); i++) { PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i]; if (_productCategory->isSelected()) sql += QString(", shipmentsByCustomerValue(cust_id, %1, :prodcat_id) AS bucket%3") .arg(cursor->id()) .arg(columns++); else if (_productCategory->isPattern()) sql += QString(", shipmentsByCustomerValue(cust_id, %1, :prodcat_pattern) AS bucket%3") .arg(cursor->id()) .arg(columns++); else sql += QString(", shipmentsByCustomerValue(cust_id, %1) AS bucket%2") .arg(cursor->id()) .arg(columns++); _sohist->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight); _columnDates.append(DatePair(cursor->startDate(), cursor->endDate())); } sql += " FROM cust "; if (_customerType->isSelected()) sql += "WHERE (cust_custtype_id=:custtype_id)"; else if (_customerType->isPattern()) sql += "WHERE (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ :custtype_pattern))) "; if (_byCustomer->isChecked()) sql += "ORDER BY cust_number;"; else if (_bySales->isChecked()) sql += "ORDER BY bucket1 DESC;"; q.prepare(sql); _customerType->bindValue(q); _productCategory->bindValue(q); q.exec(); if (q.first()) { Q3ValueVector<Numeric> totals(columns);; XTreeWidgetItem *last = 0; do { last = new XTreeWidgetItem( _sohist, last, q.value("cust_id").toInt(), q.value("cust_number"), q.value("cust_name") ); for (int column = 1; column < columns; column++) { QString bucketName = QString("bucket%1").arg(column); last->setText((column + 1), formatMoney(q.value(bucketName).toDouble())); totals[column] += q.value(bucketName).toDouble(); } } while (q.next()); // Add the totals row XTreeWidgetItem *total = new XTreeWidgetItem(_sohist, last, -1, QVariant(tr("Totals:"))); for (int column = 1; column < columns; column++) total->setText((column + 1), formatMoney(totals[column].toDouble())); } }
void dspTimePhasedBookingsByCustomer::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(2); _columnDates.clear(); QString sql("SELECT cust_id, cust_number, cust_name"); int columns = 1; QList<QTreeWidgetItem*> selected = _periods->selectedItems(); for (int i = 0; i < selected.size(); i++) { PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i]; sql += QString(", bookingsByCustomerValue(cust_id, %2) 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 cust "; if (_customerType->isSelected()) sql += "WHERE (cust_custtype_id=:custtype_id) "; else if (_customerType->isPattern()) sql += "WHERE (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ :custtype_pattern))) "; sql += "ORDER BY cust_number;"; q.prepare(sql); _customerType->bindValue(q); q.exec(); if (q.first()) { Q3ValueVector<Numeric> totals(columns);; XTreeWidgetItem *last = 0; do { last = new XTreeWidgetItem( _soitem, last, q.value("cust_id").toInt(), q.value("cust_number"), q.value("cust_name") ); for (int column = 1; column < columns; column++) { QString bucketName = QString("bucket%1").arg(column); last->setText((column + 1), formatMoney(q.value(bucketName).toDouble())); totals[column] += q.value(bucketName).toDouble(); } } while (q.next()); XTreeWidgetItem *total = new XTreeWidgetItem(_soitem, last, -1, QVariant(tr("Totals:"))); for (int column = 1; column < columns; column++) total->setText((column + 1), formatMoney(totals[column].toDouble())); } }