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 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 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 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 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 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->setColumnCount(2);
QString sql("SELECT vend_id, vend_number, vend_name");
QStringList linetotal;
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(", openAPItemsValue(vend_id, %2) AS %1,"
" 'curr' AS %3_xtnumericrole, 0 AS %4_xttotalrole")
.arg(bucketname)
.arg(cursor->id())
.arg(bucketname)
.arg(bucketname);
_apopen->addColumn(formatDate(cursor->startDate()), _bigMoneyColumn, Qt::AlignRight, true, bucketname);
_columnDates.append(DatePair(cursor->startDate(), cursor->endDate()));
linetotal << QString("openAPItemsValue(vend_id, %1)").arg(cursor->id());
}
_apopen->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 vend "
"<? if exists(\"vend_id\") ?>"
"WHERE (vend_id=<? value (\"vend_id\") ?>)"
"<? elseif exists(\"vendtype_id\") ?>"
"WHERE (vend_vendtype_id=<? value (\"vendtype_id\") ?>)"
"<? elseif exists(\"vendtype_code\") ?>"
"WHERE (vend_vendtype_id IN (SELECT vendtype_id FROM vendtype WHERE (vendtype_code ~ <? value (\"vendtype_pattern\") ?>))) "
"<? endif ?>"
"ORDER BY vend_number;";
MetaSQLQuery mql(sql);
ParameterList params;
if (! setParams(params))
return;
q = mql.toQuery(params);
_apopen->populate(q);
if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void dspTimePhasedDemandByPlannerCode::sFillList()
{
_columnDates.clear();
_demand->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(summDemand(itemsite_id, %1)) AS %2,"
" 'qty' AS %3_xtnumericrole ")
.arg(cursor->id())
.arg(bucketname)
.arg(bucketname);
else if (_capacityUnits->isChecked())
sql += QString(", SUM(summDemand(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(summDemand(itemsite_id, %1) * itemaltcapinvrat(item_id)) AS %2,"
" 'qty' AS %3_xtnumericrole ")
.arg(cursor->id())
.arg(bucketname)
.arg(bucketname);
_demand->addColumn(formatDate(cursor->startDate()), _timeColumn, Qt::AlignRight, true, bucketname);
_columnDates.append(DatePair(cursor->startDate(), cursor->endDate()));
}
sql += " FROM itemsite, item, uom, warehous, plancode "
"WHERE ( (itemsite_active)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (itemsite_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (itemsite_plancode_id=plancode_id)";
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();
_demand->populate(q, TRUE);
}
void dspTimePhasedRoughCutByWorkCenter::sFillList()
{
_columnDates.clear();
_roughCut->clear();
while (_roughCut->columns() > 1)
_roughCut->removeColumn(1);
QString sql("SELECT");
int columns = 1;
bool show = FALSE;
XListViewItem *cursor = _periods->firstChild();
if (cursor)
{
do
{
if (_periods->isSelected(cursor))
{
if (show)
sql += ",";
else
show = TRUE;
sql += QString( " formatTime(SUM(plannedSetupTime(wrkcnt_id, %1))) AS setup%2,"
" formatCost(SUM(plannedSetupTime(wrkcnt_id, %3) * wrkcnt_setuprate / 60.0)) AS setupcost%4,"
" formatTime(SUM(plannedRunTime(wrkcnt_id, %5))) AS run%6,"
" formatCost(SUM(plannedRunTime(wrkcnt_id, %7) * wrkcnt_runrate / 60.0)) AS runcost%8" )
.arg(cursor->id())
.arg(columns)
.arg(cursor->id())
.arg(columns)
.arg(cursor->id())
.arg(columns)
.arg(cursor->id())
.arg(columns++);
_roughCut->addColumn(formatDate(((PeriodListViewItem *)cursor)->startDate()), _qtyColumn, Qt::AlignRight);
_columnDates.append(DatePair(((PeriodListViewItem *)cursor)->startDate(), ((PeriodListViewItem *)cursor)->endDate()));
}
}
while ((cursor = cursor->nextSibling()) != 0);
}
sql += " FROM wrkcnt ";
if (_warehouse->isSelected())
sql += "WHERE (wrkcnt_warehous_id=:warehous_id)";
if (_selectedWorkCenter->isChecked())
{
if (_warehouse->isSelected())
sql += " AND (wrkcnt_id=:wrkcnt_id);";
else
sql += "WHERE (wrkcnt_id=:wrkcnt_id);";
}
if (show)
{
q.prepare(sql);
_warehouse->bindValue(q);
q.bindValue(":wrkcnt_id", _workCenters->id());
q.exec();
if (q.first())
{
XListViewItem *setup = new XListViewItem(_roughCut, 0, QVariant(tr("Setup Time")), q.value("setup1"));
XListViewItem *setupCost = new XListViewItem(_roughCut, setup, 0, QVariant(tr("Setup Cost")), q.value("setupcost1"));
XListViewItem *run = new XListViewItem(_roughCut, setupCost, 0, QVariant(tr("Run Time")), q.value("run1"));
XListViewItem *runCost = new XListViewItem(_roughCut, run, 0, QVariant(tr("Run Cost")), q.value("runcost1"));
for (int column = 1; column < columns; column++)
{
setup->setText((column + 1), q.value(QString("setup%1").arg(column)).toString());
setupCost->setText((column + 1), q.value(QString("setupcost%1").arg(column)).toString());
run->setText((column + 1), q.value(QString("run%1").arg(column)).toString());
runCost->setText((column + 1), q.value(QString("runcost%1").arg(column)).toString());
}
}
}
}
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<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(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(", SUM(shipmentsByItemQty(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(shipmentsByItemQty(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(shipmentsByItemQty(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);
_sohist->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();
_sohist->populate(q, true);
}
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()));
}
}
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);
}
