void dspBacklogByCustomer::sPopulateMenu(Q3PopupMenu *pMenu)
{
int selectionCount = 0;
bool multiSelection = FALSE;
bool hasParents = FALSE;
bool hasChildren = FALSE;
for (XListViewItem *cursor = _soitem->firstChild(); cursor; cursor = cursor->itemBelow())
{
if (cursor->isSelected())
{
if ( (++selectionCount > 1) && (!multiSelection) )
multiSelection = TRUE;
if ( (cursor->altId() == -1) && (!hasParents) )
hasParents = TRUE;
if ( (cursor->altId() != -1) && (!hasChildren) )
hasChildren = TRUE;
}
}
int menuItem;
if (selectionCount == 1)
{
menuItem = pMenu->insertItem(tr("Edit Order..."), this, SLOT(sEditOrder()), 0);
if (!_privleges->check("MaintainSalesOrders"))
pMenu->setItemEnabled(menuItem, FALSE);
menuItem = pMenu->insertItem(tr("View Order..."), this, SLOT(sViewOrder()), 0);
if ((!_privleges->check("MaintainSalesOrders")) && (!_privleges->check("ViewSalesOrders")))
pMenu->setItemEnabled(menuItem, FALSE);
if (hasChildren)
{
pMenu->insertSeparator();
menuItem = pMenu->insertItem(tr("Edit Item..."), this, SLOT(sEditItem()), 0);
if (!_privleges->check("MaintainSalesOrders"))
pMenu->setItemEnabled(menuItem, FALSE);
menuItem = pMenu->insertItem(tr("View Item..."), this, SLOT(sViewItem()), 0);
if ((!_privleges->check("MaintainSalesOrders")) && (!_privleges->check("ViewSalesOrders")))
pMenu->setItemEnabled(menuItem, FALSE);
}
}
if (hasParents)
{
pMenu->insertSeparator();
menuItem = pMenu->insertItem(tr("Print Packing List..."), this, SLOT(sPrintPackingList()), 0);
if (!_privleges->check("PrintPackingLists"))
pMenu->setItemEnabled(menuItem, FALSE);
menuItem = pMenu->insertItem(tr("Add to Packing List Batch..."), this, SLOT(sAddToPackingListBatch()), 0);
if (!_privleges->check("MaintainPackingListBatch"))
pMenu->setItemEnabled(menuItem, FALSE);
}
}
/*
* Constructs a reprintInvoices as a child of 'parent', with the
* name 'name' and widget flags set to 'f'.
*
* The dialog will by default be modeless, unless you set 'modal' to
* true to construct a modal dialog.
*/
reprintInvoices::reprintInvoices(QWidget* parent, const char* name, bool modal, Qt::WFlags fl)
: QDialog(parent, name, modal, fl)
{
setupUi(this);
// signals and slots connections
connect(_print, SIGNAL(clicked()), this, SLOT(sPrint()));
connect(_query, SIGNAL(clicked()), this, SLOT(sQuery()));
connect(_numOfCopies, SIGNAL(valueChanged(int)), this, SLOT(sHandleInvoiceCopies(int)));
connect(_watermarks, SIGNAL(itemSelected(int)), this, SLOT(sEditWatermark()));
_invoice->addColumn( tr("Invoice #"), _orderColumn, Qt::AlignRight );
_invoice->addColumn( tr("Doc. Date"), _dateColumn, Qt::AlignCenter );
_invoice->addColumn( tr("Customer"), -1, Qt::AlignLeft );
_invoice->setSelectionMode(Q3ListView::Extended);
_watermarks->addColumn( tr("Copy #"), _dateColumn, Qt::AlignCenter );
_watermarks->addColumn( tr("Watermark"), -1, Qt::AlignLeft );
_watermarks->addColumn( tr("Show Prices"), _dateColumn, Qt::AlignCenter );
_numOfCopies->setValue(_metrics->value("InvoiceCopies").toInt());
if (_numOfCopies->value())
{
int counter = 0;
XListViewItem *cursor = _watermarks->firstChild();
for (; cursor; cursor = cursor->nextSibling(), counter++)
{
cursor->setText(1, _metrics->value(QString("InvoiceWatermark%1").arg(counter)));
cursor->setText(2, ((_metrics->value(QString("InvoiceShowPrices%1").arg(counter)) == "t") ? tr("Yes") : tr("No")));
}
}
}
ParameterList rptTimePhasedProductionByPlannerCode::buildParameters()
{
ParameterList params;
_plannerCode->appendValue(params);
_warehouse->appendValue(params);
XListViewItem *cursor = _periods->firstChild();
QList<QVariant> periodList;
while (cursor)
{
if (cursor->isSelected())
periodList.append(cursor->id());
cursor = cursor->nextSibling();
}
params.append("period_id_list", periodList);
if (_capacityUnits->isChecked())
params.append("capacityUnits");
else if(_altCapacityUnits->isChecked())
params.append("altCapacityUnits");
else if(_inventoryUnits->isChecked())
params.append("inventoryUnits");
if(_showInactive->isChecked())
params.append("showInactive");
return params;
}
ParameterList rptTimePhasedBookingsByProductCategory::buildParameters()
{
ParameterList params;
_productCategory->appendValue(params);
_warehouse->appendValue(params);
if(_inventoryUnits->isChecked())
params.append("inventoryUnits");
else if(_capacityUnits->isChecked())
params.append("capacityUnits");
else if(_altCapacityUnits->isChecked())
params.append("altCapacityUnits");
else if(_salesDollars->isChecked())
params.append("salesDollars");
XListViewItem *cursor = _periods->firstChild();
QList<QVariant> periodList;
while (cursor)
{
if (cursor->isSelected())
periodList.append(cursor->id());
cursor = cursor->nextSibling();
}
params.append("period_id_list", periodList);
return params;
}
void dspTimePhasedProductionByItem::sCalculate()
{
_columnDates.clear();
while (_production->columns() > 3)
_production->removeColumn(3);
QString sql("SELECT itemsite_id, item_number, warehous_code, item_invuom");
int columns = 1;
XListViewItem *cursor = _periods->firstChild();
if (cursor != 0)
{
do
{
if (_periods->isSelected(cursor))
{
sql += QString(", formatQty(summProd(itemsite_id, %2)) AS bucket%1")
.arg(columns++)
.arg(cursor->id());
_production->addColumn(formatDate(((PeriodListViewItem *)cursor)->startDate()), _qtyColumn, Qt::AlignRight);
_columnDates.append(DatePair(((PeriodListViewItem *)cursor)->startDate(), ((PeriodListViewItem *)cursor)->endDate()));
}
}
while ((cursor = cursor->nextSibling()) != 0);
}
sql += " FROM itemsite, item, warehous "
"WHERE ((itemsite_item_id=item_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 reprintInvoices::sEditWatermark()
{
XListViewItem *cursor = _watermarks->selectedItem();
ParameterList params;
params.append("watermark", cursor->text(1));
params.append("showPrices", (cursor->text(2) == tr("Yes")));
editICMWatermark newdlg(this, "", TRUE);
newdlg.set(params);
if (newdlg.exec() == QDialog::Accepted)
{
cursor->setText(1, newdlg.watermark());
cursor->setText(2, ((newdlg.showPrices()) ? tr("Yes") : tr("No")));
}
}
void dspBacklogByCustomer::sPrintPackingList()
{
for (XListViewItem *cursor = _soitem->firstChild(); cursor; cursor = cursor->itemBelow())
{
if ( (cursor->isSelected()) && (cursor->altId() == -1) )
{
ParameterList params;
params.append("sohead_id", cursor->id());
printPackingList newdlg(this, "", TRUE);
newdlg.set(params);
newdlg.exec();
}
}
}
ParameterList rptTimePhasedBookingsByCustomer::buildParameters()
{
ParameterList params;
_customerType->appendValue(params);
XListViewItem *cursor = _periods->firstChild();
QList<QVariant> periodList;
while (cursor)
{
if (cursor->isSelected())
periodList.append(cursor->id());
cursor = cursor->nextSibling();
}
params.append("period_id_list", periodList);
return params;
}
void rptTimePhasedRoughCutByWorkCenter::sPrint()
{
if (_periods->isPeriodSelected())
{
ParameterList params;
if (_selectedWorkCenter->isChecked())
params.append("wkrcnt_id", _workCenters->id());
_warehouse->appendValue(params);
XListViewItem *cursor = _periods->firstChild();
QList<QVariant> periodList;
while (cursor)
{
if (cursor->isSelected())
periodList.append(cursor->id());
cursor = cursor->nextSibling();
}
params.append("period_id_list", periodList);
orReport report("TimePhasedRoughCutByWorkCenter", params);
if (report.isValid())
report.print();
else
{
report.reportError(this);
reject();
}
if (_captive)
accept();
}
else
QMessageBox::critical( this, tr("Incomplete criteria"),
tr( "The criteria you specified is not complete. Please make sure all\n"
"fields are correctly filled out before running the report." ) );
}
void dspTimePhasedCapacityByWorkCenter::sFillList()
{
_columnDates.clear();
while (_load->columns() > 1)
_load->removeColumn(1);
QString sql("SELECT wrkcnt_id, wrkcnt_code ");
int columns = 1;
XListViewItem *cursor = _periods->firstChild();
if (cursor != 0)
{
do
{
if (_periods->isSelected(cursor))
{
sql += QString(", formatTime(workCenterCapacity(wrkcnt_id, %1)) AS bucket%2")
.arg(cursor->id())
.arg(columns++);
_load->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) ";
sql += "ORDER BY wrkcnt_code;";
q.prepare(sql);
_warehouse->bindValue(q);
q.exec();
_load->populate(q);
}
void dspBacklogByCustomer::sAddToPackingListBatch()
{
for (XListViewItem *cursor = _soitem->firstChild(); cursor; cursor = cursor->itemBelow())
{
if ( (cursor->isSelected()) && (cursor->altId() == -1) )
{
q.prepare("SELECT addToPackingListBatch(:sohead_id, :cosmisc_id) AS result;");
q.bindValue(":sohead_id", cursor->id());
q.bindValue(":cosmisc_id", cursor->altId());
q.exec();
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
}
}
void dspSalesHistoryByItem::sFillList()
{
_sohist->clear();
if (!checkParameters())
return;
QString sql( "SELECT cohist_id, cust_name, cohist_ordernumber,"
" formatDate(cohist_orderdate) AS f_orderdate,"
" CASE WHEN (cohist_invcnumber='-1') THEN 'Credit'"
" ELSE TEXT(cohist_invcnumber)"
" END AS invoicenumber,"
" formatDate(cohist_invcdate, 'Return') AS f_invcdate,"
" cohist_qtyshipped, formatQty(cohist_qtyshipped) AS f_shipped " );
if (_showPrices->isChecked())
sql += ", formatSalesPrice(cohist_unitprice) AS f_price,"
" round(cohist_qtyshipped * cohist_unitprice, 2) AS extprice,"
" formatMoney(round(cohist_qtyshipped * cohist_unitprice, 2)) AS f_extprice ";
if (_showCosts->isChecked())
sql += ", formatSalesPrice(cohist_unitcost) AS f_cost,"
" (cohist_qtyshipped * cohist_unitcost) AS extcost,"
" formatMoney(cohist_qtyshipped * cohist_unitcost) AS f_extcost ";
sql += "FROM cohist, cust, itemsite, item "
"WHERE ( (cohist_itemsite_id=itemsite_id)"
" AND (cohist_cust_id=cust_id)"
" AND (itemsite_item_id=item_id)"
" AND (item_id=:item_id)"
" AND (cohist_invcdate BETWEEN :startDate AND :endDate) ";
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_customerType->isSelected())
sql += " AND (cust_custtype_id=:custtype_id)";
else if (_customerType->isPattern())
sql += " AND (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ :custtype_pattern)))";
sql += ") "
"ORDER BY cohist_invcdate, cust_number; ";
q.prepare(sql);
_customerType->bindValue(q);
_warehouse->bindValue(q);
_dates->bindValue(q);
q.bindValue(":item_id", _item->id());
q.exec();
if (q.first())
{
double totalSales = 0.0;
double totalUnits = 0.0;
double totalCost = 0.0;
do
{
XListViewItem *last = new XListViewItem( _sohist, _sohist->lastItem(),
q.value("cohist_id").toInt(),
q.value("cust_name"), q.value("cohist_ordernumber"),
q.value("f_orderdate"), q.value("invoicenumber"),
q.value("f_invcdate"), q.value("f_shipped") );
if (_showPrices->isChecked())
{
last->setText(6, q.value("f_price"));
last->setText(7, q.value("f_extprice"));
if (_showCosts->isChecked())
{
last->setText(8, q.value("f_cost"));
last->setText(9, q.value("f_extcost"));
}
}
else if (_showCosts->isChecked())
{
last->setText(6, q.value("f_cost"));
last->setText(7, q.value("f_extcost"));
}
totalUnits += q.value("cohist_qtyshipped").toDouble();
totalSales += q.value("extprice").toDouble();
totalCost += q.value("extcost").toDouble();
}
while (q.next());
XListViewItem *totals = new XListViewItem(_sohist, _sohist->lastItem(), -1, QVariant(tr("Totals")));
totals->setText(5, formatQty(totalUnits));
if (_showPrices->isChecked())
{
totals->setText(7, formatMoney(totalSales));
if (_showCosts->isChecked())
totals->setText(9, formatMoney(totalCost));
}
else if (_showCosts->isChecked())
totals->setText(7, formatMoney(totalSales));
}
}
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_cohead_id=cohead_id)"
" 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;
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
{
XListViewItem *cursor = _shipformWatermarks->firstChild();
for (int counter = 0; cursor; cursor = cursor->nextSibling(), counter++ )
{
ParameterList params;
params.append("cosmisc_id", reports.value("shiphead_id").toInt());
params.append("shiphead_id", reports.value("shiphead_id").toInt());
params.append("watermark", cursor->text(1));
#if 0
params.append("shipchrg_id", _shipchrg->id());
#endif
if (cursor->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 dspSummarizedBacklogByWarehouse::sFillList()
{
_so->clear();
XListViewItem *orderLine = NULL;
if (_dates->allValid())
{
QString sql( "SELECT cohead_id, cohead_holdtype, cohead_number, cust_name,"
" CASE WHEN (cohead_holdtype='N') THEN :noHold"
" WHEN (cohead_holdtype='C') THEN :credit"
" WHEN (cohead_holdtype='S') THEN :shipping"
" WHEN (cohead_holdtype='P') THEN :packing"
" ELSE :otherHold"
" END AS f_holdtype,"
" formatDate(cohead_orderdate) AS f_orderdate,"
" formatDate(MIN(coitem_scheddate)) AS f_scheddate,"
" formatDate(cohead_packdate) AS f_packdate,"
" formatMoney( SUM( round(noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) *"
" (coitem_price / item_invpricerat), 2) ) ) AS f_sales,"
" formatCost(SUM(noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * stdcost(item_id) ) ) AS f_cost,"
" formatMoney( SUM( noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) *"
" ((coitem_price / item_invpricerat) - stdcost(item_id)) ) ) AS f_margin,"
" SUM( round(noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) *"
" (coitem_price / item_invpricerat), 2) ) AS sales,"
" SUM(noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * stdcost(item_id) ) AS cost,"
" SUM( noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) *"
" ((coitem_price / item_invpricerat) - stdcost(item_id)) ) AS margin,"
" MIN(coitem_scheddate) AS scheddate,"
" COALESCE(cosmisc_id, -1) AS cosmisc_id, "
" formatShipmentNumber(cosmisc_id) AS cosmisc_number, "
" CASE WHEN (cosmisc_shipped IS NULL) THEN 0"
" WHEN (cosmisc_shipped) THEN 1"
" WHEN (NOT cosmisc_shipped) THEN 2"
" END AS shipstatus,"
" COALESCE(cosmisc_shipvia, '') AS shipvia,"
" CASE WHEN (cosmisc_shipdate IS NULL) THEN ''"
" ELSE formatDate(cosmisc_shipdate)"
" END AS shipdate,"
" ( ((SELECT COALESCE(SUM(cobill_qty), 0)"
" FROM cobill, cobmisc"
" WHERE ((cobill_cobmisc_id=cobmisc_id)"
" AND (cobmisc_cohead_id=cohead_id)) ) > 0)"
" AND (SUM(noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) > 0)"
" ) AS overbilled "
"FROM coitem, itemsite, item, cust, cohead LEFT OUTER JOIN cosmisc ON (cosmisc_cohead_id=cohead_id) "
"WHERE ( (coitem_cohead_id=cohead_id)"
" AND (cohead_cust_id=cust_id)"
" AND (coitem_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (coitem_status NOT IN ('C','X'))"
" AND (coitem_scheddate BETWEEN :startDate AND :endDate)" );
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_customerType->isSelected())
sql += " AND (cust_custtype_id=:custtype_id)";
else if (_customerType->isPattern())
sql += " AND (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ :custtype_pattern)))";
sql += ") "
"GROUP BY cohead_id, cohead_number, cust_name, cohead_holdtype,"
" cohead_orderdate, cohead_packdate,"
" cosmisc_shipped, cosmisc_shipvia, cosmisc_shipdate, cosmisc_id ";
if (_shipDate->isChecked())
sql += "ORDER BY scheddate, cohead_number, cosmisc_shipped;";
else if (_packDate->isChecked())
sql += "ORDER BY cohead_packdate, cohead_number, cosmisc_shipped;";
else if (_orderNumber->isChecked())
sql += "ORDER BY cohead_number, cosmisc_shipped;";
q.prepare(sql);
_warehouse->bindValue(q);
_customerType->bindValue(q);
_dates->bindValue(q);
q.bindValue(":noHold", tr("None"));
q.bindValue(":credit", tr("Credit"));
q.bindValue(":shipping", tr("Ship"));
q.bindValue(":packing", tr("Pack"));
q.bindValue(":otherHold", tr("Other"));
q.exec();
if (q.first())
{
double totalSales = 0.0;
double totalCost = 0.0;
double totalMargin = 0.0;
int coheadid = -1;
bool unshipped = FALSE;
bool overbilled = FALSE;
do
{
if ( (coheadid != q.value("cohead_id").toInt()) || (!orderLine) )
{
coheadid = q.value("cohead_id").toInt();
unshipped = FALSE;
overbilled = FALSE;
orderLine = new XListViewItem( _so, orderLine,
q.value("cohead_id").toInt(),
-1,
q.value("cohead_number"), q.value("cust_name"),
q.value("f_holdtype"), q.value("f_orderdate"),
q.value("f_scheddate"), q.value("f_packdate"),
q.value("f_sales"), q.value("f_cost"),
q.value("f_margin") );
totalSales += q.value("sales").toDouble();
totalCost += q.value("cost").toDouble();
totalMargin += q.value("margin").toDouble();
}
if (q.value("overbilled").toBool())
{
overbilled = TRUE;
orderLine->setColor("red");
}
if (q.value("shipstatus").toInt())
{
XListViewItem *shipLine = new XListViewItem(orderLine, -1,
q.value("cosmisc_id").toInt());
if (q.value("shipstatus").toInt() == 1)
{
shipLine->setText(0, tr("Yes"));
shipLine->setColor("green");
if (!unshipped && !overbilled)
orderLine->setColor("green");
}
else if (q.value("shipstatus").toInt() == 2)
{
shipLine->setText(0, tr("No"));
shipLine->setColor("blue");
if(!overbilled)
orderLine->setColor("blue");
unshipped = TRUE;
}
shipLine->setText(1, q.value("shipvia"));
shipLine->setText(2, q.value("cosmisc_number"));
shipLine->setText(3, q.value("shipdate"));
}
}
while (q.next());
if (_showPrices->isChecked())
new XListViewItem( _so, orderLine, -1,
"", "", tr("Total Backlog"), "", "", "",
formatMoney(totalSales),
formatCost(totalCost),
formatMoney(totalMargin) );
sql = "SELECT COUNT(cohead_id) AS totalorders "
"FROM ( SELECT DISTINCT cohead_id "
" FROM cohead, coitem, itemsite, cust "
" WHERE ( (coitem_cohead_id=cohead_id)"
" AND (coitem_itemsite_id=itemsite_id)"
" AND (cohead_cust_id=cust_id)"
" AND (coitem_status NOT IN ('C','X'))"
" AND (coitem_scheddate BETWEEN :startDate AND :endDate)";
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_customerType->isSelected())
sql += " AND (cust_custtype_id=:custtype_id)";
else if (_customerType->isPattern())
sql += " AND (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ :custtype_pattern)))";
sql += ") ) AS data;";
q.prepare(sql);
_warehouse->bindValue(q);
_customerType->bindValue(q);
_dates->bindValue(q);
q.exec();
if (q.first())
_totalSalesOrders->setText(q.value("totalorders").toString());
// ToDo
sql = "SELECT COUNT(coitem.*) AS totalitems "
"FROM cohead, coitem, itemsite, cust "
"WHERE ( (coitem_cohead_id=cohead_id)"
" AND (coitem_itemsite_id=itemsite_id)"
" AND (cohead_cust_id=cust_id)"
" AND (coitem_status NOT IN ('C','X'))"
" AND (coitem_scheddate BETWEEN :startDate AND :endDate)";
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_customerType->isSelected())
sql += " AND (cust_custtype_id=:custtype_id)";
else if (_customerType->isPattern())
sql += " AND (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ :custtype_pattern)))";
sql += ");";
q.prepare(sql);
_warehouse->bindValue(q);
_customerType->bindValue(q);
_dates->bindValue(q);
q.exec();
if (q.first())
_totalLineItems->setText(q.value("totalitems").toString());
// ToDo
sql = "SELECT formatQty(SUM((coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * item_shipinvrat)) AS f_totalqty "
"FROM cohead, coitem, itemsite, item, cust "
"WHERE ( (coitem_cohead_id=cohead_id)"
" AND (coitem_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (cohead_cust_id=cust_id)"
" AND (coitem_status NOT IN ('C','X'))"
" AND (coitem_scheddate BETWEEN :startDate AND :endDate)";
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_customerType->isSelected())
sql += " AND (cust_custtype_id=:custtype_id)";
else if (_customerType->isPattern())
sql += " AND (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ :custtype_pattern)))";
sql += ");";
q.prepare(sql);
_warehouse->bindValue(q);
_customerType->bindValue(q);
_dates->bindValue(q);
q.exec();
if (q.first())
_totalQty->setText(q.value("f_totalqty").toString());
// ToDo
}
else
{
_totalSalesOrders->clear();
_totalLineItems->clear();
_totalQty->clear();
}
}
}
void dspIndentedWhereUsed::sFillList()
{
_bomitem->clear();
if (_item->isValid())
{
q.prepare("SELECT indentedWhereUsed(:item_id) AS workset_id;");
q.bindValue(":item_id", _item->id());
q.exec();
if (q.first())
{
int worksetid = q.value("workset_id").toInt();
QString sql( "SELECT bomwork_id, item_id, bomwork_parent_id,"
" bomwork_seqnumber, item_number, item_invuom,"
" (item_descrip1 || ' ' || item_descrip2) AS itemdescription,"
" formatQtyPer(bomwork_qtyper) AS qtyper,"
" formatScrap(bomwork_scrap) AS scrap,"
" formatDate(bomwork_effective, 'Always') AS effective,"
" formatDate(bomwork_expires, 'Never') AS expires "
"FROM bomwork, item "
"WHERE ( (bomwork_item_id=item_id)"
" AND (bomwork_set_id=:bomwork_set_id)" );
if (!_showExpired->isChecked())
sql += " AND (bomwork_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (bomwork_effective <= CURRENT_DATE)";
sql += ") "
"ORDER BY bomwork_level, item_number DESC;";
q.prepare(sql);
q.bindValue(":bomwork_set_id", worksetid);
q.exec();
while (q.next())
{
if (q.value("bomwork_parent_id").toInt() == -1)
new XListViewItem( _bomitem, q.value("bomwork_id").toInt(), q.value("item_id").toInt(),
q.value("bomwork_seqnumber"), q.value("item_number"),
q.value("itemdescription"), q.value("item_invuom"),
q.value("qtyper"), q.value("scrap"),
q.value("effective"), q.value("expires") );
else
{
XListViewItem *cursor = _bomitem->firstChild();
if (cursor)
{
do
{
if (cursor->id() == q.value("bomwork_parent_id").toInt())
{
new XListViewItem( cursor, q.value("bomwork_id").toInt(), q.value("item_id").toInt(),
q.value("bomwork_seqnumber"), q.value("item_number"),
q.value("itemdescription"), q.value("item_invuom"),
q.value("qtyper"), q.value("scrap"),
q.value("effective"), q.value("expires") );
cursor->setOpen(TRUE);
break;
}
}
while ((cursor = cursor->itemBelow()) != NULL);
}
}
}
q.prepare("SELECT deleteBOMWorkset(:workset_id) AS result;");
q.bindValue(":bomwork_set_id", worksetid);
q.exec();
}
}
}
void billingEditList::sFillList()
{
_cobill->clear();
q.exec("SELECT * FROM billingEditList;");
if (q.first())
{
int thisOrderid;
int orderid = -9999;
XListViewItem *orderLine = NULL;
XListViewItem *lastLine = NULL;
XListViewItem *selected = NULL;
// Fill the list with the query contents
do
{
thisOrderid = q.value("orderid").toInt();
// Check to see if this a new order number
if (thisOrderid != orderid)
{
// New order number, make a new list item header
orderid = thisOrderid;
lastLine = NULL;
XListViewItem *thisLine = new XListViewItem( _cobill, orderLine, q.value("orderid").toInt(), q.value("itemid").toInt(),
q.value("documentnumber"), q.value("ordernumber"),
q.value("cust_number"), q.value("billtoname") );
orderLine = thisLine;
// Add the distribution line
thisLine = new XListViewItem( orderLine, q.value("orderid").toInt(), -1,
"", "",
q.value("sence"), q.value("account"),
"", "", "", q.value("extprice") );
if (q.value("account") == "Not Assigned")
{
thisLine->setColor("red");
orderLine->setColor("red");
}
// If we are looking for a selected order and this is it, cache it
if (thisOrderid == _orderid)
selected = orderLine;
}
else
{
XListViewItem *itemLine = new XListViewItem( orderLine, lastLine, q.value("orderid").toInt(), q.value("itemid").toInt(),
"", "",
q.value("item"), q.value("itemdescrip"),
q.value("item_invuom"), q.value("qtytobill"),
q.value("price"), q.value("extprice") );
// Add the distribution line
XListViewItem *thisLine = new XListViewItem( itemLine, q.value("orderid").toInt(), -1,
"", "",
q.value("sence"), q.value("account"),
"", "", "", q.value("extprice") );
if (q.value("account") == "Not Assigned")
{
thisLine->setColor("red");
itemLine->setColor("red");
orderLine->setColor("red");
}
lastLine = itemLine;
}
}
while (q.next());
// Select and show the select item, if any
if (selected != NULL)
{
_cobill->setSelected(selected, TRUE);
_cobill->ensureItemVisible(selected);
}
}
}
void dspCostedIndentedBOM::sFillList()
{
double totalCosts = 0;
_bomitem->clear();
if (_item->isValid())
{
q.prepare("SELECT indentedBOM(:item_id) AS bomwork_set_id;");
q.bindValue(":item_id", _item->id());
q.exec();
if (q.first())
{
int _worksetid = q.value("bomwork_set_id").toInt();
QString sql( "SELECT bomwork_id, item_id, bomwork_parent_id,"
" bomwork_seqnumber, item_number, item_invuom,"
" (item_descrip1 || ' ' || item_descrip2) AS itemdescription,"
" formatQtyPer(bomwork_qtyper) AS qtyper,"
" formatScrap(bomwork_scrap) AS scrap,"
" formatDate(bomwork_effective, 'Always') AS effective,"
" formatDate(bomwork_expires, 'Never') AS expires," );
if (_useStandardCosts->isChecked())
sql += " formatCost(bomwork_stdunitcost) AS f_unitcost,"
" formatCost(bomwork_qtyper * (1 + bomwork_scrap) * bomwork_stdunitcost) AS f_extendedcost,"
" (bomwork_qtyper * (1 + bomwork_scrap) * bomwork_stdunitcost) AS extendedcost,";
else if (_useActualCosts->isChecked())
sql += " formatCost(bomwork_actunitcost) AS f_unitcost,"
" formatCost(bomwork_qtyper * (1 + bomwork_scrap) * bomwork_actunitcost) AS f_extendedcost,"
" (bomwork_qtyper * (1 + bomwork_scrap) * bomwork_actunitcost) AS extendedcost,";
sql += " bomwork_level "
"FROM bomwork, item "
"WHERE ((bomwork_item_id=item_id)"
" AND (bomwork_set_id=:bomwork_set_id)"
" AND (CURRENT_DATE BETWEEN bomwork_effective AND (bomwork_expires - 1))) "
"UNION SELECT -1 AS bomwork_id, -1 AS item_id, -1 AS bomwork_parent_id,"
" 99999 AS bomwork_seqnumber, costelem_type AS item_number, '' AS item_invuom,"
" '' AS itemdescription,"
" '' AS qtyper, '' AS scrap, '' AS effective, '' AS expires,";
if (_useStandardCosts->isChecked())
sql += " formatCost(itemcost_stdcost) AS f_unitcost,"
" formatCost(itemcost_stdcost) AS f_extendedcost,"
" itemcost_stdcost AS extendedcost,";
else if (_useActualCosts->isChecked())
sql += " formatCost(currToBase(itemcost_curr_id, itemcost_actcost, CURRENT_DATE)) AS f_unitcost,"
" formatCost(currToBase(itemcost_curr_id, itemcost_actcost, CURRENT_DATE)) AS f_extendedcost,"
" currToBase(itemcost_curr_id, itemcost_actcost, CURRENT_DATE) AS extendedcost,";
sql += " -1 AS bomwork_level "
"FROM itemcost, costelem "
"WHERE ( (itemcost_costelem_id=costelem_id)"
" AND (NOT itemcost_lowlevel)"
" AND (itemcost_item_id=:item_id) ) "
"ORDER BY bomwork_level, bomwork_seqnumber, item_number;";
q.prepare(sql);
q.bindValue(":bomwork_set_id", _worksetid);
q.bindValue(":item_id", _item->id());
q.exec();
XListViewItem *last = NULL;
while (q.next())
{
if (q.value("bomwork_parent_id").toInt() == -1)
{
if (q.value("bomwork_id").toInt() == -1)
{
last = new XListViewItem( _bomitem, last, -1, -1,
"", q.value("item_number"),
"", "", "", "", "" );
last->setText(7, "");
last->setText(8, q.value("f_unitcost").toString());
last->setText(9, q.value("f_extendedcost").toString());
}
else
{
last = new XListViewItem( _bomitem, last, q.value("bomwork_id").toInt(), q.value("item_id").toInt(),
q.value("bomwork_seqnumber"), q.value("item_number"),
q.value("itemdescription"), q.value("item_invuom"),
q.value("qtyper"), q.value("scrap"),
q.value("effective"), q.value("expires"),
q.value("f_unitcost"), q.value("f_extendedcost") );
}
totalCosts += q.value("extendedcost").toDouble();
}
else
{
XListViewItem *cursor = _bomitem->firstChild();
if (cursor)
{
do
{
if (cursor->id() == q.value("bomwork_parent_id").toInt())
{
XListViewItem *sibling = NULL;
XListViewItem *child;
if (cursor->firstChild() != NULL)
{
sibling = cursor->firstChild();
while (sibling->nextSibling() != NULL)
sibling = sibling->nextSibling();
}
child = new XListViewItem( cursor, sibling, q.value("bomwork_id").toInt(), q.value("item_id").toInt(),
q.value("bomwork_seqnumber"), q.value("item_number"),
q.value("itemdescription"), q.value("item_invuom"),
q.value("qtyper"), q.value("scrap"),
q.value("effective"), q.value("expires"),
q.value("f_unitcost"), q.value("f_extendedcost") );
cursor->setOpen(TRUE);
break;
}
}
while ((cursor = cursor->itemBelow()) != NULL);
}
}
}
last = new XListViewItem(_bomitem, last, -1, -1);
last->setText(1, tr("Total Cost"));
last->setText(9, formatCost(totalCosts));
q.prepare( "SELECT formatCost(actcost(:item_id)) AS actual,"
" formatCost(stdcost(:item_id)) AS standard;" );
q.bindValue(":item_id", _item->id());
q.exec();
if (q.first())
{
last = new XListViewItem(_bomitem, last, -1, -1);
last->setText(1, tr("Actual Cost"));
last->setText(9, q.value("actual").toString());
last = new XListViewItem( _bomitem, last, -1, -1);
last->setText(1, tr("Standard Cost"));
last->setText(9, q.value("standard").toString());
}
q.prepare( "DELETE FROM bomwork "
"WHERE (bomwork_set_id=:bomwork_set_id);" );
q.bindValue(":bomwork_set_id", _worksetid);
q.exec();
}
}
}
void reprintInvoices::sPrint()
{
QPrinter printer;
bool setupPrinter = TRUE;
XListViewItem *cursor = _invoice->firstChild();
bool userCanceled = false;
if (orReport::beginMultiPrint(&printer, userCanceled) == false)
{
if(!userCanceled)
systemError(this, tr("Could not initialize printing system for multiple reports."));
return;
}
while (cursor != 0)
{
if (_invoice->isSelected(cursor))
{
int counter = 0;
for ( XListViewItem *watermark = _watermarks->firstChild();
watermark; watermark = watermark->nextSibling(), counter++ )
{
q.prepare("SELECT findCustomerForm(:cust_id, 'I') AS _reportname;");
q.bindValue(":cust_id", cursor->altId());
q.exec();
if (q.first())
{
ParameterList params;
params.append("invchead_id", cursor->id());
params.append("showcosts", ((watermark->text(2) == tr("Yes")) ? "TRUE" : "FALSE") );
params.append("watermark", watermark->text(1));
orReport report(q.value("_reportname").toString(), params);
if (report.isValid())
{
if (report.print(&printer, setupPrinter))
setupPrinter = FALSE;
else
{
report.reportError(this);
orReport::endMultiPrint(&printer);
return;
}
}
else
QMessageBox::critical( this, tr("Cannot Find Invoice Form"),
tr( "The Invoice Form '%1' cannot be found.\n"
"One or more of the selected Invoices cannot be printed until a Customer Form Assignment\n"
"is updated to remove any references to this Invoice Form or this Invoice Form is created." )
.arg(q.value("_reportname").toString()) );
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
if (_metrics->boolean("EnableBatchManager"))
{
// TODO: Check for EDI and handle submission to Batch here
q.prepare("SELECT CASE WHEN (COALESCE(shipto_ediprofile_id, -2) = -2)"
" THEN COALESCE(cust_ediprofile_id,-1)"
" ELSE COALESCE(shipto_ediprofile_id,-2)"
" END AS result,"
" COALESCE(cust_emaildelivery, false) AS custom"
" FROM cust, invchead"
" LEFT OUTER JOIN shipto"
" ON (invchead_shipto_id=shipto_id)"
" WHERE ((invchead_cust_id=cust_id)"
" AND (invchead_id=:invchead_id)); ");
q.bindValue(":invchead_id", cursor->id());
q.exec();
if(q.first())
{
if(q.value("result").toInt() == -1)
{
if(q.value("custom").toBool())
{
ParameterList params;
params.append("invchead_id", cursor->id());
deliverInvoice newdlg(this, "", TRUE);
newdlg.set(params);
newdlg.exec();
}
}
else
{
ParameterList params;
params.append("action_name", "TransmitInvoice");
params.append("invchead_id", cursor->id());
submitAction newdlg(this, "", TRUE);
newdlg.set(params);
newdlg.exec();
}
}
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
cursor = cursor->nextSibling();
}
orReport::endMultiPrint(&printer);
_invoice->clearSelection();
_close->setText(tr("&Close"));
_print->setEnabled(FALSE);
}
void dspSalesHistoryByShipTo::sFillList()
{
_sohist->clear();
if (!checkParameters())
return;
QString sql = QString( "SELECT cohist_id, cohist_ordernumber, cohist_invcnumber,"
" formatDate(cohist_orderdate) AS f_orderdate,"
" formatDate(cohist_invcdate, 'Return') AS f_invcdate,"
" item_number, (item_descrip1 || ' ' || item_descrip2) AS description,"
" cohist_qtyshipped, formatQty(cohist_qtyshipped) AS f_shipped " );
if (_showPrices->isChecked())
sql += ", formatSalesPrice(cohist_unitprice) AS f_price,"
" round(cohist_qtyshipped * cohist_unitprice, 2) AS extprice,"
" formatMoney(round(cohist_qtyshipped * cohist_unitprice, 2)) AS f_extprice ";
if (_showPrices->isChecked())
sql += ", formatCost(cohist_unitcost) AS f_cost,"
" (cohist_qtyshipped * cohist_unitcost) AS extcost,"
" formatMoney(cohist_qtyshipped * cohist_unitcost) AS f_extcost ";
sql += "FROM cohist, itemsite, item, prodcat "
"WHERE ( (cohist_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (item_prodcat_id=prodcat_id)"
" AND (cohist_invcdate BETWEEN :startDate AND :endDate)";
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 += " AND (cohist_shipto_id=:shipto_id) ) "
"ORDER BY cohist_invcdate, item_number;";
q.prepare(sql);
_warehouse->bindValue(q);
_productCategory->bindValue(q);
_dates->bindValue(q);
q.bindValue(":shipto_id", _shipTo->id());
q.exec();
if (q.first())
{
double totalUnits = 0.0;
double totalSales = 0.0;
double totalCosts = 0.0;
do
{
XListViewItem *last = new XListViewItem( _sohist, _sohist->lastItem(), q.value("cohist_id").toInt(),
q.value("cohist_ordernumber"), q.value("cohist_invcnumber"),
q.value("f_orderdate"), q.value("f_invcdate"),
q.value("item_number"), q.value("description"),
q.value("f_shipped") );
if (_showPrices->isChecked())
{
last->setText(7, q.value("f_price"));
last->setText(8, q.value("f_extprice"));
if (_showCosts->isChecked())
{
last->setText(9, q.value("f_cost"));
last->setText(10, q.value("f_extcost"));
}
}
else if (_showCosts->isChecked())
{
last->setText(7, q.value("f_cost"));
last->setText(8, q.value("f_extcost"));
}
totalUnits += q.value("cohist_qtyshipped").toDouble();
if (_showPrices->isChecked())
totalSales += q.value("extprice").toDouble();
if (_showCosts->isChecked())
totalCosts += q.value("extcost").toDouble();
}
while (q.next());
XListViewItem *totals = new XListViewItem(_sohist, _sohist->lastItem(), -1);
totals->setText(5, tr("Totals"));
totals->setText(6, formatQty(totalUnits));
if (_showPrices->isChecked())
{
totals->setText(8, formatMoney(totalSales));
if (_showCosts->isChecked())
totals->setText(10, formatCost(totalCosts));
}
else if (_showCosts->isChecked())
totals->setText(8, formatCost(totalCosts));
}
}
void dspBacklogByCustomer::sFillList()
{
_soitem->clear();
QString sql( "SELECT cohead_id, coitem_id, cohead_number, coitem_linenumber,"
" formatDate(cohead_orderdate) AS f_orderdate,"
" formatDate((SELECT MIN(coitem_scheddate) FROM coitem WHERE (coitem_cohead_id=cohead_id))) AS f_shipdate,"
" formatDate(coitem_scheddate) AS f_scheddate,"
" item_number, cohead_custponumber,"
" formatQty(coitem_qtyord) AS f_ordered,"
" formatQty(coitem_qtyshipped) AS f_shipped,"
" formatQty(noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) AS f_balance,"
" formatMoney(round(noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * (coitem_price / item_invpricerat),2)) AS f_backlog,"
" round(noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * (coitem_price / item_invpricerat),2) AS backlog "
"FROM cohead, coitem, itemsite, item "
"WHERE ( (coitem_cohead_id=cohead_id)"
" AND (coitem_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (coitem_status NOT IN ('C','X'))"
" AND (cohead_cust_id=:cust_id)"
" AND (coitem_scheddate BETWEEN :startDate AND :endDate)" );
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
sql += ") "
"ORDER BY coitem_scheddate, cohead_number, coitem_linenumber DESC;";
q.prepare(sql);
_warehouse->bindValue(q);
_dates->bindValue(q);
q.bindValue(":cust_id", _cust->id());
q.exec();
if (q.first())
{
XListViewItem *head = NULL;
int soheadid = -1;
double totalBacklog = 0.0;
do
{
if (soheadid != q.value("cohead_id").toInt())
{
soheadid = q.value("cohead_id").toInt();
head = new XListViewItem( _soitem, head, soheadid, -1,
q.value("cohead_number"), q.value("cohead_custponumber"),
q.value("f_orderdate"), q.value("f_shipdate") );
}
new XListViewItem( head, soheadid, q.value("coitem_id").toInt(),
q.value("coitem_linenumber"), q.value("item_number"),
q.value("f_orderdate"), q.value("f_scheddate"),
q.value("f_ordered"), q.value("f_shipped"),
q.value("f_balance"), q.value("f_backlog") );
totalBacklog += q.value("backlog").toDouble();
}
while (q.next());
if (_showPrices->isChecked())
{
XListViewItem *totals = new XListViewItem(_soitem, head, -1, -1, tr("Total Backlog"));
totals->setText(7, formatMoney(totalBacklog));
}
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void BOM::sFillList(int pItemid, bool)
{
if (_item->isValid() && (pItemid == _item->id()))
{
q.prepare( "SELECT bomhead_docnum, bomhead_revision,"
" bomhead_revisiondate,"
" formatQty(bomhead_batchsize) AS f_batchsize,"
" bomhead_requiredqtyper "
"FROM bomhead "
"WHERE (bomhead_item_id=:item_id);" );
q.bindValue(":item_id", _item->id());
q.exec();
if (q.first())
{
_documentNum->setText(q.value("bomhead_docnum"));
_revision->setText(q.value("bomhead_revision"));
_revisionDate->setDate(q.value("bomhead_revisiondate").toDate());
_batchSize->setText(q.value("f_batchsize"));
if(q.value("bomhead_requiredqtyper").toDouble()!=0)
{
_doRequireQtyPer->setChecked(true);
_requiredQtyPer->setText(q.value("bomhead_requiredqtyper").toString());
}
}
else
{
_documentNum->clear();
_revision->clear();
_revisionDate->clear();
_batchSize->clear();
}
QString sql( "SELECT bomitem_id, item_id, bomitem_seqnumber,"
" item_number, (item_descrip1 || ' ' || item_descrip2) AS item_description,"
" item_invuom,"
" CASE WHEN (bomitem_issuemethod = 'S') THEN :push"
" WHEN (bomitem_issuemethod = 'L') THEN :pull"
" WHEN (bomitem_issuemethod = 'M') THEN :mixed"
" ELSE :error"
" END AS issuemethod,"
" formatQtyPer(bomitem_qtyper) AS f_qtyper,"
" formatScrap(bomitem_scrap) AS f_scrap,"
" formatDate(bomitem_effective, :always) AS f_effective,"
" formatDate(bomitem_expires, :never) AS f_expires,"
" (bomitem_configtype<>'N') AS config "
"FROM bomitem, item "
"WHERE ((bomitem_item_id=item_id)"
" AND (bomitem_parent_item_id=:item_id)" );
if (!_showExpired->isChecked())
sql += " AND (bomitem_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (bomitem_effective <= CURRENT_DATE)";
sql += ") "
"ORDER BY bomitem_seqnumber, bomitem_effective";
int bomitemid = _bomitem->id();
_bomitem->clear();
XListViewItem *selected = 0;
q.prepare(sql);
q.bindValue(":push", tr("Push"));
q.bindValue(":pull", tr("Pull"));
q.bindValue(":mixed", tr("Mixed"));
q.bindValue(":error", tr("Error"));
q.bindValue(":always", tr("Always"));
q.bindValue(":never", tr("Never"));
q.bindValue(":item_id", _item->id());
q.exec();
while (q.next())
{
XListViewItem *last = new XListViewItem( _bomitem, _bomitem->lastItem(),
q.value("bomitem_id").toInt(), q.value("item_id").toInt(),
q.value("bomitem_seqnumber"), q.value("item_number"),
q.value("item_description"), q.value("item_invuom"),
q.value("issuemethod"), q.value("f_qtyper"),
q.value("f_scrap"), q.value("f_effective"),
q.value("f_expires") );
if (q.value("config").toBool())
last->setColor("blue");
if (q.value("bomitem_id").toInt() == bomitemid)
selected = last;
}
if (selected)
{
_bomitem->setSelected(selected, TRUE);
_bomitem->ensureItemVisible(selected);
}
sql = "SELECT item_picklist,"
" COUNT(*) AS total,"
" COALESCE(SUM(bomitem_qtyper * (1 + bomitem_scrap))) AS qtyper "
"FROM bomitem, item "
"WHERE ( (bomitem_item_id=item_id)"
" AND (bomitem_parent_item_id=:item_id)";
if (!_showExpired->isChecked())
sql += " AND (bomitem_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (bomitem_effective <= CURRENT_DATE)";
sql += " ) "
"GROUP BY item_picklist;";
q.prepare(sql);
q.bindValue(":item_id", _item->id());
q.exec();
bool foundPick = FALSE;
bool foundNonPick = FALSE;
int totalNumber = 0;
double totalQtyPer = 0.0;
while (q.next())
{
totalNumber += q.value("total").toInt();
totalQtyPer += q.value("qtyper").toDouble();
if (q.value("item_picklist").toBool())
{
foundPick = TRUE;
_pickNumber->setText(q.value("total").toString());
_pickQtyPer->setText(formatQtyPer(q.value("qtyper").toDouble()));
}
else
{
foundNonPick = TRUE;
_nonPickNumber->setText(q.value("total").toString());
_nonPickQtyPer->setText(formatQtyPer(q.value("qtyper").toDouble()));
}
}
if (!foundPick)
{
_pickNumber->setText("0");
_pickQtyPer->setText(formatQty(0.0));
}
if (!foundNonPick)
{
_nonPickNumber->setText("0");
_nonPickQtyPer->setText(formatQty(0.0));
}
_totalNumber->setText(QString("%1").arg(totalNumber));
_totalQtyPer->setText(formatQtyPer(totalQtyPer));
_totalQtyPerCache = totalQtyPer;
if (_privleges->check("ViewCosts"))
{
sql = "SELECT formatCost(p.item_maxcost) AS f_maxcost,"
" formatCost(COALESCE(SUM(bomitem_qtyper * (1 + bomitem_scrap) * stdCost(c.item_id)))) AS f_stdcost,"
" formatCost(COALESCE(SUM(bomitem_qtyper * (1 + bomitem_scrap) * ROUND(actCost(c.item_id),4)))) AS f_actcost "
"FROM bomitem, item AS c, item AS p "
"WHERE ( (bomitem_parent_item_id=p.item_id)"
" AND (bomitem_item_id=c.item_id)"
" AND (p.item_id=:item_id)";
if (!_showExpired->isChecked())
sql += " AND (bomitem_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (bomitem_effective <= CURRENT_DATE)";
sql += " ) "
"GROUP BY p.item_maxcost;";
q.prepare(sql);
q.bindValue(":item_id", _item->id());
q.exec();
if (q.first())
{
_currentStdCost->setText(q.value("f_stdcost").toString());
_currentActCost->setText(q.value("f_actcost").toString());
_maxCost->setText(q.value("f_maxcost").toString());
}
}
}
else if (!_item->isValid())
{
_documentNum->clear();
_revision->clear();
_revisionDate->clear();
_batchSize->clear();
_bomitem->clear();
}
}
void dspExpiredInventoryByClassCode::sFillList()
{
_expired->clear();
QString sql( "SELECT itemsite_id, itemloc_id, warehous_code, item_number, item_invuom,"
" itemloc_lotserial, formatDate(itemloc_expiration) AS f_expiration,"
" formatQty(itemloc_qty) AS f_qty,"
" formatCost(cost) AS f_unitcost,"
" noNeg(cost * itemloc_qty) AS value,"
" formatMoney(noNeg(cost * itemloc_qty)) AS f_value,"
" cost "
"FROM ( SELECT itemsite_id, itemloc_id, warehous_code, item_number,"
" item_invuom, itemloc_lotserial, itemloc_expiration,"
" itemloc_qty," );
if (_useStandardCosts->isChecked())
sql += " stdcost(itemsite_item_id) AS cost ";
else if (_useActualCosts->isChecked())
sql += " actcost(itemsite_item_id) AS cost ";
sql += "FROM itemloc, itemsite, item, warehous "
"WHERE ( (itemloc_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (itemsite_perishable)"
" AND (itemloc_expiration < (CURRENT_DATE + :thresholdDays))";
if (_warehouse->isSelected())
sql += " AND (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 += ") ) AS data ";
if (_itemNumber->isChecked())
sql += "ORDER BY warehous_code, item_number;";
else if (_expirationDate->isChecked())
sql += "ORDER BY warehous_code, itemloc_expiration;";
else
sql += "ORDER BY warehous_code, noNeg(cost * itemloc_qty) DESC;";
q.prepare(sql);
q.bindValue(":thresholdDays", _thresholdDays->value());
_warehouse->bindValue(q);
_classCode->bindValue(q);
q.exec();
while (q.next())
{
XListViewItem *last = new XListViewItem( _expired, _expired->lastItem(),
q.value("itemsite_id").toInt(), q.value("itemloc_id").toInt(),
q.value("warehous_code"), q.value("item_number"),
q.value("item_invuom"), q.value("itemloc_lotserial"),
q.value("f_expiration"), q.value("f_qty") );
if (_showValue->isChecked())
{
last->setText(6, q.value("f_unitcost").toString());
last->setText(7, q.value("f_value").toString());
}
last->setColor("red");
}
}
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();
while (_soitem->columns() > 3)
_soitem->removeColumn(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 += ", item_invuom AS uom";
else if (_capacityUnits->isChecked())
sql += ", item_capuom AS uom";
else if (_altCapacityUnits->isChecked())
sql += ", item_altcapuom AS uom";
int columns = 1;
XListViewItem *cursor = _periods->firstChild();
if (cursor != 0)
{
do
{
if (_periods->isSelected(cursor))
{
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) * item_capinvrat) AS bucket%1")
.arg(columns++)
.arg(cursor->id());
else if (_altCapacityUnits->isChecked())
sql += QString(", SUM(bookingsByItemQty(itemsite_id, %2) * item_altcapinvrat) AS bucket%1")
.arg(columns++)
.arg(cursor->id());
_soitem->addColumn(formatDate(((PeriodListViewItem *)cursor)->startDate()), _qtyColumn, Qt::AlignRight);
_columnDates.append(DatePair(((PeriodListViewItem *)cursor)->startDate(), ((PeriodListViewItem *)cursor)->endDate()));
}
}
while ((cursor = cursor->nextSibling()) != 0);
}
sql += " FROM itemsite, item, warehous, prodcat "
"WHERE ( (itemsite_item_id=item_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);;
do
{
XListViewItem *item = new XListViewItem( _soitem, _soitem->lastItem(),
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()) )
item->setText((column + 2), formatQty(q.value(bucketName).toDouble()));
else if (_salesDollars->isChecked())
item->setText((column + 2), formatMoney(q.value(bucketName).toDouble()));
}
}
while (q.next());
XListViewItem *total = new XListViewItem(_soitem, _soitem->lastItem(), -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 dspTimePhasedDemandByPlannerCode::sFillList()
{
_columnDates.clear();
while (_demand->columns() > 3)
_demand->removeColumn(3);
QString sql("SELECT plancode_id, warehous_id, plancode_code, warehous_code, ");
if (_inventoryUnits->isChecked())
sql += "item_invuom AS uom";
else if (_capacityUnits->isChecked())
sql += "item_capuom AS uom";
else if (_altCapacityUnits->isChecked())
sql += "item_altcapuom AS uom";
int columns = 1;
XListViewItem *cursor = _periods->firstChild();
if (cursor != 0)
{
do
{
if (_periods->isSelected(cursor))
{
if (_inventoryUnits->isChecked())
sql += QString(", formatQty(SUM(summDemand(itemsite_id, %1))) AS bucket%2")
.arg(cursor->id())
.arg(columns++);
else if (_capacityUnits->isChecked())
sql += QString(", formatQty(SUM(summDemand(itemsite_id, %1) * item_capinvrat)) AS bucket%2")
.arg(cursor->id())
.arg(columns++);
else if (_altCapacityUnits->isChecked())
sql += QString(", formatQty(SUM(summDemand(itemsite_id, %1) * item_altcapinvrat)) AS bucket%2")
.arg(cursor->id())
.arg(columns++);
_demand->addColumn(formatDate(((PeriodListViewItem *)cursor)->startDate()), _timeColumn, Qt::AlignRight);
_columnDates.append(DatePair(((PeriodListViewItem *)cursor)->startDate(), ((PeriodListViewItem *)cursor)->endDate()));
}
}
while ((cursor = cursor->nextSibling()) != 0);
}
sql += " FROM itemsite, item, warehous, plancode "
"WHERE ( (itemsite_active)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (itemsite_item_id=item_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 dspBriefSalesHistoryBySalesRep::sFillList()
{
_sohist->clear();
if (!checkParameters())
return;
QString sql( "SELECT cust_id, cust_name, cohist_ordernumber,"
" CASE WHEN (cohist_invcnumber='-1') THEN 'Credit'"
" ELSE TEXT(cohist_invcnumber)"
" END AS invoicenumber,"
" formatDate(cohist_orderdate) AS f_orderdate,"
" formatDate(cohist_invcdate, 'Return') AS f_invcdate " );
if (_showPrices->isChecked())
sql += ", SUM(round(cohist_qtyshipped * cohist_unitprice,2)) AS extprice,"
" formatMoney(SUM(round(cohist_qtyshipped * cohist_unitprice,2))) AS f_extprice ";
if (_showCosts->isChecked())
sql += ", SUM(cohist_qtyshipped * cohist_unitcost) AS extcost,"
" formatCost(SUM(cohist_qtyshipped * cohist_unitcost)) AS f_extcost ";
sql += "FROM cohist, cust, itemsite, item, prodcat "
"WHERE ( (cohist_itemsite_id=itemsite_id)"
" AND (cohist_cust_id=cust_id)"
" AND (itemsite_item_id=item_id)"
" AND (item_prodcat_id=prodcat_id)"
" AND (cohist_salesrep_id=:salesrep_id)"
" AND (cohist_invcdate BETWEEN :startDate AND :endDate)";
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 cust_id, cust_name, cohist_ordernumber, cohist_invcnumber,"
" cohist_orderdate, cohist_invcdate "
"ORDER BY cohist_invcdate;";
q.prepare(sql);
_warehouse->bindValue(q);
_productCategory->bindValue(q);
_dates->bindValue(q);
q.bindValue(":salesrep_id", _salesrep->id());
q.exec();
if (q.first())
{
double totalSales = 0.0;
double totalCosts = 0.0;
do
{
XListViewItem *last = new XListViewItem( _sohist, _sohist->lastItem(), q.value("cust_id").toInt(),
q.value("cust_name"), q.value("cohist_ordernumber"),
q.value("invoicenumber"), q.value("f_orderdate"),
q.value("f_invcdate") );
if (_showPrices->isChecked())
last->setText(5, q.value("f_extprice"));
if (_showCosts->isChecked())
last->setText(((_showPrices->isChecked()) ? 6 : 5), q.value("f_extcost"));
if (_showPrices->isChecked())
totalSales += q.value("extprice").toDouble();
if (_showCosts->isChecked())
totalCosts += q.value("extcost").toDouble();
}
while (q.next());
if ( (_showPrices->isChecked()) || (_showCosts->isChecked()) )
{
XListViewItem *totals = new XListViewItem(_sohist, _sohist->lastItem(), -1, QVariant(tr("Total Sales")));
if (_showPrices->isChecked())
totals->setText(5, formatMoney(totalSales));
if (_showCosts->isChecked())
totals->setText(((_showPrices->isChecked()) ? 6 : 5), formatCost(totalCosts));
}
}
}
void dspSummarizedGLTransactions::sFillList()
{
_gltrans->clear();
QString sql( "SELECT gltrans_id, accnt_id,"
" formatGLAccount(accnt_id) AS account,"
" accnt_descrip,"
" formatDate(gltrans_date) AS transdate,"
" 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 "
"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 += ") "
"ORDER BY accnt_id, gltrans_date, gltrans_created;";
q.prepare(sql);
_dates->bindValue(q);
q.bindValue(":source", _source->currentText());
q.exec();
if (q.first())
{
XListViewItem *header = NULL;
int accntid = -1;
double debits = 0.0;
double credits = 0.0;
do
{
if (accntid != q.value("accnt_id").toInt())
{
if (header)
{
if (debits > 0)
{
header->setText(5, formatMoney(debits));
debits = 0;
}
if (credits > 0)
{
header->setText(6, formatMoney(credits));
credits = 0;
}
}
accntid = q.value("accnt_id").toInt();
header = new XListViewItem( _gltrans, _gltrans->lastItem(), accntid,
q.value("account"), q.value("accnt_descrip") );
}
XListViewItem *item = new XListViewItem( header,
accntid, q.value("gltrans_id").toInt(),
q.value("transdate"), q.value("f_notes"),
q.value("gltrans_source"), q.value("gltrans_doctype"),
q.value("gltrans_docnumber"), "", "",
q.value("gltrans_username") );
if (q.value("debit") != 0.0)
{
debits += q.value("debit").toDouble();
item->setText(5, formatMoney(q.value("debit").toDouble()));
}
if (q.value("credit") != 0.0)
{
credits += q.value("credit").toDouble();
item->setText(6, formatMoney(q.value("credit").toDouble()));
}
}
while (q.next());
if (header)
{
if (debits > 0)
header->setText(5, formatMoney(debits));
if (credits > 0)
header->setText(6, formatMoney(credits));
}
}
}
void dspDetailedInventoryHistoryByLotSerial::sFillList()
{
_invhist->clear();
if (!_dates->startDate().isValid())
{
QMessageBox::critical( this, tr("Enter Start Date"),
tr("Please enter a valid Start Date.") );
_dates->setFocus();
return;
}
if (!_dates->endDate().isValid())
{
QMessageBox::critical( this, tr("Enter End Date"),
tr("Please enter a valid End Date.") );
_dates->setFocus();
return;
}
if (_lotSerial->text().stripWhiteSpace().length() == 0)
{
QMessageBox::warning( this, tr("Enter Lot/Serial #"),
tr("<p>You must enter a Lot/Serial to view Inventory "
"Detail by Lot/Serial #.</p>") );
_lotSerial->setFocus();
return;
}
q.prepare( "SELECT invhist_id,"
" formatDateTime(invhist_transdate) AS transdate,"
" invhist_transtype, (invhist_ordtype || '-' || invhist_ordnumber) AS ordernumber,"
" invhist_invuom,"
" item_number, invdetail_lotserial,"
" CASE WHEN (invdetail_location_id=-1) THEN :undefined"
" ELSE formatLocationName(invdetail_location_id)"
" END AS locationname,"
" formatQty(invdetail_qty) AS transqty,"
" formatQty(invdetail_qty_before) AS qohbefore,"
" formatQty(invdetail_qty_after) AS qohafter,"
" invhist_posted "
"FROM invdetail, invhist, itemsite, item "
"WHERE ( (invdetail_invhist_id=invhist_id)"
" AND (invhist_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (invdetail_lotserial ~ :lotserial)"
" AND (DATE(invhist_transdate) BETWEEN :startDate AND :endDate)"
" AND (transType(invhist_transtype, :transType)) ) "
"ORDER BY invhist_transdate DESC, invhist_transtype;" );
_dates->bindValue(q);
q.bindValue(":undefined", tr("Undefined"));
q.bindValue(":lotserial", _lotSerial->text());
q.bindValue(":transType", _transType->id());
q.exec();
XListViewItem *last = 0;
while (q.next())
{
last = new XListViewItem( _invhist, last, q.value("invhist_id").toInt(),
q.value("transdate"), q.value("invhist_transtype"),
q.value("ordernumber"), q.value("item_number"),
q.value("locationname"), q.value("invdetail_lotserial"),
q.value("invhist_invuom"), q.value("transqty") );
if (q.value("invhist_posted").toBool())
{
last->setText(8, q.value("qohbefore").toString());
last->setText(9, q.value("qohafter").toString());
}
else
last->setColor("orange");
}
}
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 dspCheckRegister::sFillList()
{
if(!checkParams())
return;
MetaSQLQuery mql = mqlLoad(":/ap/displays/CheckRegister/FillListDetail.mql");
ParameterList params;
params.append("bankaccnt_id", _bankaccnt->id());
_dates->appendValue(params);
if(_showDetail->isChecked())
params.append("showDetail");
q = mql.toQuery(params);
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
_apchk->clear();
if (q.first())
{
XListViewItem *header = NULL;
int apchkid = -1;
do
{
if (q.value("apchkid").toInt() != apchkid)
{
apchkid = q.value("apchkid").toInt();
header = new XListViewItem( _apchk, _apchk->lastItem(), apchkid, q.value("extra").toInt(),
q.value("f_void"), q.value("f_misc"),
q.value("f_printed"), q.value("f_posted"), q.value("number"),
q.value("description"), q.value("f_checkdate"),
q.value("f_amount"), q.value("currAbbr"));
}
else if (header)
{
XListViewItem *item = new XListViewItem( header, apchkid, 0);
item->setText(4, q.value("number"));
item->setText(5, q.value("description"));
item->setText(7, q.value("f_amount"));
}
}
while (q.next());
}
if(_showDetail->isChecked())
_apchk->openAll();
q.prepare( "SELECT formatMoney(SUM(currToCurr(apchk_curr_id, bankaccnt_curr_id,"
" apchk_amount, apchk_checkdate))) AS f_amount,"
" currConcat(bankaccnt_curr_id) AS currAbbr "
"FROM apchk, vend, bankaccnt "
"WHERE ( (apchk_vend_id=vend_id) "
" AND (NOT apchk_void)"
" AND (apchk_checkdate BETWEEN :startDate AND :endDate) "
" AND (bankaccnt_id=apchk_bankaccnt_id) "
" AND (apchk_bankaccnt_id=:bankaccnt_id) )"
" GROUP BY bankaccnt_curr_id;" );
q.bindValue(":bankaccnt_id", _bankaccnt->id());
_dates->bindValue(q);
q.exec();
if(q.first())
{
_total->setText(q.value("f_amount").toString());
_totalCurr->setText(q.value("currAbbr").toString());
}
}
