void task::populate()
{
q.prepare( "SELECT prjtask.* "
"FROM prjtask "
"WHERE (prjtask_id=:prjtask_id);" );
q.bindValue(":prjtask_id", _prjtaskid);
q.exec();
if (q.first())
{
_number->setText(q.value("prjtask_number"));
_name->setText(q.value("prjtask_name"));
_descrip->setText(q.value("prjtask_descrip").toString());
_owner->setUsername(q.value("prjtask_owner_username").toString());
_assignedTo->setUsername(q.value("prjtask_username").toString());
_started->setDate(q.value("prjtask_start_date").toDate());
_assigned->setDate(q.value("prjtask_assigned_date").toDate());
_due->setDate(q.value("prjtask_due_date").toDate());
_completed->setDate(q.value("prjtask_completed_date").toDate());
QString status = q.value("prjtask_status").toString();
if("P" == status)
_status->setCurrentIndex(0);
else if("O" == status)
_status->setCurrentIndex(1);
else if("C" == status)
_status->setCurrentIndex(2);
_budgetHours->setText(formatQty(q.value("prjtask_hours_budget").toDouble()));
_actualHours->setText(formatQty(q.value("prjtask_hours_actual").toDouble()));
_budgetExp->setText(formatCost(q.value("prjtask_exp_budget").toDouble()));
_actualExp->setText(formatCost(q.value("prjtask_exp_actual").toDouble()));
_alarms->setId(_prjtaskid);
_comments->setId(_prjtaskid);
sHoursAdjusted();
sExpensesAdjusted();
//if (q.value("prjtask_anyuser").toBool())
// _anyUser->setChecked(TRUE);
//else
// _userList->setChecked(TRUE);
}
else if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
//sFillUserList();
}
void booItem::sCalculateInvRunTime()
{
if ((_runTimePer->toDouble() != 0.0) && (_invProdUOMRatio->toDouble() != 0.0))
{
_invRunTime->setText(formatCost(_runTime->toDouble() / _runTimePer->toDouble() / _invProdUOMRatio->toDouble()));
_invPerMinute->setText(formatCost(_runTimePer->toDouble() / _runTime->toDouble() * _invProdUOMRatio->toDouble()));
}
else
{
_invRunTime->setText(formatCost(0.0));
_invPerMinute->setText(formatCost(0.0));
}
}
void dspItemCostSummary::sFillList()
{
if (_item->isValid())
{
double standardCost = 0.0;
double actualCost = 0.0;
q.prepare( "SELECT itemcost_id,"
" CASE WHEN (costelem_sys) THEN 1"
" ELSE 0"
" END,"
" costelem_type, formatBoolYN(itemcost_lowlevel),"
" formatCost(itemcost_stdcost), formatDate(itemcost_posted, 'Never'),"
" formatcost(itemcost_actcost), formatDate(itemcost_updated, 'Never'),"
" itemcost_stdcost AS stdcost, itemcost_actcost AS actcost "
"FROM itemcost, costelem "
"WHERE ( (itemcost_costelem_id=costelem_id)"
" AND (itemcost_item_id=:item_id) ) "
"ORDER BY itemcost_lowlevel, costelem_type;" );
q.bindValue(":item_id", _item->id());
q.exec();
_itemcost->populate(q, TRUE);
if (q.first())
{
do
{
standardCost += q.value("stdcost").toDouble();
actualCost += q.value("actcost").toDouble();
}
while (q.next());
new XListViewItem( _itemcost, _itemcost->lastItem(), -1,
QVariant(tr("Totals")), "", formatCost(standardCost),
"", formatCost(actualCost) );
}
}
else
_itemcost->clear();
}
void task::populate()
{
q.prepare( "SELECT prjtask_number, prjtask_name, prjtask_descrip, prjtask_status,"
" prjtask_hours_budget, prjtask_hours_actual,"
" prjtask_exp_budget, prjtask_exp_actual "
"FROM prjtask "
"WHERE (prjtask_id=:prjtask_id);" );
q.bindValue(":prjtask_id", _prjtaskid);
q.exec();
if (q.first())
{
_number->setText(q.value("prjtask_number"));
_name->setText(q.value("prjtask_name"));
_descrip->setText(q.value("prjtask_descrip").toString());
QString status = q.value("prjtask_status").toString();
if("P" == status)
_status->setCurrentItem(0);
else if("O" == status)
_status->setCurrentItem(1);
else if("C" == status)
_status->setCurrentItem(2);
_budgetHours->setText(formatQty(q.value("prjtask_hours_budget").toDouble()));
_actualHours->setText(formatQty(q.value("prjtask_hours_actual").toDouble()));
_budgetExp->setText(formatCost(q.value("prjtask_exp_budget").toDouble()));
_actualExp->setText(formatCost(q.value("prjtask_exp_actual").toDouble()));
sHoursAdjusted();
sExpensesAdjusted();
//if (q.value("prjtask_anyuser").toBool())
// _anyUser->setChecked(TRUE);
//else
// _userList->setChecked(TRUE);
}
//sFillUserList();
}
void workCenter::sPopulateOverheadRate()
{
double runRate;
if (_runUseSelectedRate->isChecked())
{
_lbrrate.findFirst("lbrrate_id", _stdRunRate->id());
runRate = _lbrrate.value("lbrrate_rate").toDouble();
}
else
runRate = _runRate->toDouble();
_overheadRate->setText(formatCost( ((_numOfPeople->value() * runRate) * _overheadPrcntOfLabor->toDouble() / 100) +
(_numOfPeople->value() * _overheadPerLaborHour->toDouble()) +
(_numOfMachines->value() * _overheadPerMachHour->toDouble()) ) );
}
void dspSalesHistoryByShipTo::sFillList()
{
if (!checkParameters())
return;
_sohist->clear();
MetaSQLQuery mql = mqlLoad(":/so/displays/SalesHistory.mql");
ParameterList params;
_dates->appendValue(params);
_warehouse->appendValue(params);
_productCategory->appendValue(params);
params.append("shipto_id", _shipTo->id());
params.append("orderByInvcdateItem");
q = mql.toQuery(params);
if (q.first())
{
double totalUnits = 0.0;
double totalSales = 0.0;
double totalCosts = 0.0;
bool exchangeError = false;
XTreeWidgetItem *last = 0;
do
{
if (q.value("baseunitprice").toDouble() < 0.0)
exchangeError = true;
QString invoicedate = tr("Return");
if (q.value("cohist_invcdate").toString() != "")
invoicedate = formatDate(q.value("cohist_invcdate").toDate());
last = new XTreeWidgetItem( _sohist, last, q.value("cohist_id").toInt(),
q.value("cohist_ordernumber"),
q.value("invoicenumber"),
formatDate(q.value("cohist_orderdate").toDate()),
invoicedate,
q.value("item_number"),
q.value("itemdescription"),
formatQty(q.value("cohist_qtyshipped").toDouble()),
formatSalesPrice(q.value("baseunitprice").toDouble()),
formatMoney(q.value("baseextprice").toDouble()),
formatCost(q.value("cohist_unitcost").toDouble()),
formatMoney(q.value("extcost").toDouble()) );
totalUnits += q.value("cohist_qtyshipped").toDouble();
totalSales += q.value("baseextprice").toDouble();
totalCosts += q.value("extcost").toDouble();
}
while (q.next());
XTreeWidgetItem *totals = new XTreeWidgetItem(_sohist, last, -1);
totals->setText(5, tr("Totals"));
totals->setText(6, formatQty(totalUnits));
totals->setText(EXTPRICE_COL, formatMoney(totalSales));
totals->setText(EXTCOST_COL, formatCost(totalCosts));
if ( (exchangeError) && (_showPrices->isChecked()) )
QMessageBox::warning( this, tr("Currency Exchange Rate Error"),
tr("One or more of the Prices could not be converted to Base Currency.\n"
"These Prices have been set to a negative value.") );
}
}
void dspPricesByItem::sFillList()
{
_price->clear();
if (_item->isValid())
{
double cost = 0.0;
if (_showCosts->isChecked())
{
if (_useStandardCosts->isChecked())
q.prepare( "SELECT (stdCost(item_id) * iteminvpricerat(item_id)) AS cost "
"FROM item "
"WHERE (item_id=:item_id);");
else if (_useActualCosts->isChecked())
q.prepare( "SELECT (actCost(item_id) * iteminvpricerat(item_id)) AS cost "
"FROM item "
"WHERE (item_id=:item_id);");
q.bindValue(":item_id", _item->id());
q.exec();
if (q.first())
cost = q.value("cost").toDouble();
// ToDo
}
QString sql( "SELECT ipsprice_id AS itemid, 1 AS sourcetype,"
" ipshead_name AS schedulename, :customer AS type,"
" cust_name AS typename,"
" CASE WHEN (ipsprice_qtybreak = -1) THEN :na"
" ELSE formatQty(ipsprice_qtybreak)"
" END AS f_qtybreak,"
" ipsprice_price AS price, currConcat(ipshead_curr_id) AS currConcat "
"FROM ipsass, ipshead, ipsprice, cust, item "
"WHERE ( (ipsass_ipshead_id=ipshead_id)"
" AND (ipsprice_ipshead_id=ipshead_id)"
" AND (ipsass_cust_id=cust_id)"
" AND (COALESCE(LENGTH(ipsass_shipto_pattern), 0) = 0)"
" AND (ipsprice_item_id=item_id)"
" AND (item_id=:item_id)" );
if (!_showExpired->isChecked())
sql += " AND (ipshead_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (ipshead_effective <= CURRENT_DATE)";
sql += ") "
"UNION SELECT ipsprice_id AS itemid, 2 AS sourcetype,"
" ipshead_name AS schedulename, :custType AS type,"
" (custtype_code || '-' || custtype_descrip) AS typename,"
" CASE WHEN (ipsprice_qtybreak = -1) THEN :na"
" ELSE formatQty(ipsprice_qtybreak)"
" END AS f_qtybreak,"
" ipsprice_price AS price, currConcat(ipshead_curr_id) AS currConcat "
"FROM ipsass, ipshead, ipsprice, custtype, item "
"WHERE ( (ipsass_ipshead_id=ipshead_id)"
" AND (ipsprice_ipshead_id=ipshead_id)"
" AND (ipsass_custtype_id=custtype_id)"
" AND (ipsprice_item_id=item_id)"
" AND (item_id=:item_id)";
if (!_showExpired->isChecked())
sql += " AND (ipshead_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (ipshead_effective <= CURRENT_DATE)";
sql += ") "
"UNION SELECT ipsprice_id AS itemid, 3 AS sourcetype,"
" ipshead_name AS schedulename, :custTypePattern AS type,"
" (custtype_code || '-' || custtype_descrip) AS typename,"
" CASE WHEN (ipsprice_qtybreak = -1) THEN :na"
" ELSE formatQty(ipsprice_qtybreak)"
" END AS f_qtybreak,"
" ipsprice_price AS price, currConcat(ipshead_curr_id) AS currConcat "
"FROM ipsass, ipshead, ipsprice, custtype, item "
"WHERE ( (ipsass_ipshead_id=ipshead_id)"
" AND (ipsprice_ipshead_id=ipshead_id)"
" AND (coalesce(length(ipsass_custtype_pattern), 0) > 0)"
" AND (custtype_code ~ ipsass_custtype_pattern)"
" AND (ipsprice_item_id=item_id)"
" AND (item_id=:item_id)";
if (!_showExpired->isChecked())
sql += " AND (ipshead_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (ipshead_effective <= CURRENT_DATE)";
sql += ") "
"UNION SELECT ipsprice_id AS itemid, 4 AS sourcetype,"
" ipshead_name AS schedulename, :sale AS type,"
" sale_name AS typename,"
" CASE WHEN (ipsprice_qtybreak = -1) THEN :na"
" ELSE formatQty(ipsprice_qtybreak)"
" END AS f_qtybreak,"
" ipsprice_price AS price, currConcat(ipshead_curr_id) AS currConcat "
"FROM sale, ipshead, ipsprice, item "
"WHERE ( (sale_ipshead_id=ipshead_id)"
" AND (ipsprice_ipshead_id=ipshead_id)"
" AND (ipsprice_item_id=:item_id)";
if (!_showExpired->isChecked())
sql += " AND (sale_enddate > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (sale_startdate <= CURRENT_DATE)";
sql += ") "
"UNION SELECT ipsprice_id AS itemid, 5 AS sourcetype,"
" ipshead_name AS schedulename, :shipTo AS type,"
" (cust_name || '-' || shipto_num) AS typename,"
" CASE WHEN (ipsprice_qtybreak = -1) THEN :na"
" ELSE formatQty(ipsprice_qtybreak)"
" END AS f_qtybreak,"
" ipsprice_price AS price, currConcat(ipshead_curr_id) AS currConcat "
"FROM ipsass, ipshead, ipsprice, cust, shipto, item "
"WHERE ( (ipsass_ipshead_id=ipshead_id)"
" AND (ipsprice_ipshead_id=ipshead_id)"
" AND (ipsass_shipto_id=shipto_id)"
" AND (shipto_cust_id=cust_id)"
" AND (ipsprice_item_id=item_id)"
" AND (item_id=:item_id)";
if (!_showExpired->isChecked())
sql += " AND (ipshead_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (ipshead_effective <= CURRENT_DATE)";
sql += ") "
"UNION SELECT ipsprice_id AS itemid, 6 AS sourcetype,"
" ipshead_name AS schedulename, :shipToPattern AS type,"
" (cust_name || '-' || shipto_num) AS typename,"
" CASE WHEN (ipsprice_qtybreak = -1) THEN :na"
" ELSE formatQty(ipsprice_qtybreak)"
" END AS f_qtybreak,"
" ipsprice_price AS price, currConcat(ipshead_curr_id) AS currConcat "
"FROM ipsass, ipshead, ipsprice, cust, shipto, item "
"WHERE ( (ipsass_ipshead_id=ipshead_id)"
" AND (ipsprice_ipshead_id=ipshead_id)"
" AND (COALESCE(LENGTH(ipsass_shipto_pattern),0) > 0)"
" AND (shipto_num ~ ipsass_shipto_pattern)"
" AND (ipsass_cust_id=cust_id)"
" AND (shipto_cust_id=cust_id)"
" AND (ipsprice_item_id=item_id)"
" AND (item_id=:item_id)";
if (!_showExpired->isChecked())
sql += " AND (ipshead_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (ipshead_effective <= CURRENT_DATE)";
sql += ") "
"UNION SELECT item_id AS itemid, 0 AS sourcetype,"
" :listPrice AS schedulename, :na AS type,"
" '' AS typename,"
" :na AS f_qtybreak,"
" item_listprice AS price, currConcat(baseCurrId()) AS currConcat "
"FROM item "
"WHERE ( (NOT item_exclusive)"
" AND (item_id=:item_id) ) "
"ORDER BY price;";
q.prepare(sql);
q.bindValue(":na", tr("N/A"));
q.bindValue(":customer", tr("Customer"));
q.bindValue(":shipTo", tr("Cust. Ship-To"));
q.bindValue(":shipToPattern", tr("Cust. Ship-To Pattern"));
q.bindValue(":custType", tr("Cust. Type"));
q.bindValue(":custTypePattern", tr("Cust. Type Pattern"));
q.bindValue(":sale", tr("Sale"));
q.bindValue(":listPrice", tr("List Price"));
q.bindValue(":item_id", _item->id());
q.exec();
XTreeWidgetItem *last = 0;
while (q.next())
{
double price = q.value("price").toDouble();
last = new XTreeWidgetItem(_price, last, q.value("itemid").toInt(),
q.value("sourcetype").toInt(),
q.value("schedulename"), q.value("type"),
q.value("typename"), q.value("f_qtybreak"),
formatSalesPrice(q.value("price").toDouble()),
q.value("currConcat"),
formatCost(cost),
(price != 0) ? formatPercent(((price - cost) / price)) : QString());
if (cost > price)
last->setTextColor(MARGIN_COL, "red");
}
}
}
void dspSalesHistoryByBilltoName::sFillList()
{
_sohist->clear();
if (!checkParameters())
return;
QString sql( "SELECT cohist_id, cohist_billtoname,"
" cohist_ordernumber, cohist_invcnumber,"
" formatDate(cohist_orderdate) AS f_orderdate,"
" formatDate(cohist_invcdate) AS f_invcdate,"
" item_number,"
" cohist_qtyshipped, formatQty(cohist_qtyshipped) AS f_shipped " );
sql += ", formatSalesPrice(cohist_unitprice) AS f_unitprice,"
" round(cohist_qtyshipped * cohist_unitprice, 2) AS extprice,"
" formatMoney(round(cohist_qtyshipped * cohist_unitprice, 2)) AS f_extprice ";
sql += ", formatCost(cohist_unitcost) AS f_unitcost,"
" (cohist_qtyshipped * cohist_unitcost) AS extcost,"
" formatCost(cohist_qtyshipped * cohist_unitcost) AS f_extcost ";
sql += "FROM cohist, itemsite, item "
"WHERE ( (cohist_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (cohist_invcdate BETWEEN :startDate AND :endDate)"
" AND (UPPER(cohist_billtoname) ~ UPPER(:billToName))";
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 cohist_invcdate, cohist_billtoname, item_number";
q.prepare(sql);
_warehouse->bindValue(q);
_productCategory->bindValue(q);
_dates->bindValue(q);
q.bindValue(":billToName", _billtoName->text());
q.exec();
if (q.first())
{
double totalUnits = 0.0;
double totalSales = 0.0;
double totalCosts = 0.0;
XTreeWidgetItem *last = 0;
do
{
last = new XTreeWidgetItem( _sohist, last, q.value("cohist_id").toInt(),
q.value("cohist_billtoname"),
q.value("cohist_ordernumber"),
q.value("cohist_invcnumber"),
q.value("f_orderdate"),
q.value("f_invcdate"),
q.value("item_number"),
q.value("f_shipped"),
q.value("f_unitprice"),
q.value("f_extprice"),
q.value("f_unitcost"),
q.value("f_extcost"));
totalUnits += q.value("cohist_qtyshipped").toDouble();
totalSales += q.value("extprice").toDouble();
totalCosts += q.value("extcost").toDouble();
}
while (q.next());
XTreeWidgetItem *totals = new XTreeWidgetItem(_sohist, last, -1,
QVariant(tr("Total Sales")));
totals->setText(6, formatQty(totalUnits));
totals->setText(EXTPRICE_COL, formatMoney(totalSales));
totals->setText(EXTCOST_COL, formatCost(totalCosts));
}
}
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,"
" SUM(round(cohist_qtyshipped * cohist_unitprice,2)) AS extprice,"
" formatMoney(SUM(round(cohist_qtyshipped * cohist_unitprice,2))) AS f_extprice,"
" SUM(cohist_qtyshipped * cohist_unitcost) AS extcost,"
" formatCost(SUM(cohist_qtyshipped * cohist_unitcost)) AS f_extcost "
"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;
XTreeWidgetItem *last = 0;
do
{
last = new XTreeWidgetItem(_sohist, 0, q.value("cust_id").toInt(),
q.value("cust_name"),
q.value("cohist_ordernumber"),
q.value("invoicenumber"),
q.value("f_orderdate"),
q.value("f_invcdate"),
q.value("f_extprice"),
q.value("f_extcost"));
totalSales += q.value("extprice").toDouble();
totalCosts += q.value("extcost").toDouble();
}
while (q.next());
if ( (_showPrices->isChecked()) || (_showCosts->isChecked()) )
{
XTreeWidgetItem *totals = new XTreeWidgetItem(_sohist, last, -1, QVariant(tr("Total Sales")));
totals->setText(5, formatMoney(totalSales));
totals->setText(6, formatCost(totalCosts));
}
}
}
void dspSummarizedBacklogByWarehouse::sFillList()
{
_so->clear();
XTreeWidgetItem *orderLine = NULL;
ParameterList params;
if (setParams(params))
{
QString sql( "SELECT cohead_id, cohead_holdtype, cohead_number, cust_name,"
" cohead_created, cohead_orderdate, cohead_packdate, pack_head_id,"
" CASE WHEN (cohead_holdtype='N') THEN <? value(\"none\") ?>"
" WHEN (cohead_holdtype='C') THEN <? value(\"credit\") ?>"
" WHEN (cohead_holdtype='S') THEN <? value(\"ship\") ?>"
" WHEN (cohead_holdtype='P') THEN <? value(\"pack\") ?>"
" WHEN (cohead_holdtype='R') THEN <? value(\"return\") ?>"
" ELSE <? value(\"other\") ?>"
" END AS f_holdtype,"
" MIN(coitem_scheddate) AS scheddate,"
" SUM((noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) *"
" (currToBase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio) ) AS sales,"
" SUM((noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) * stdcost(item_id) ) AS cost,"
" SUM((noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) *"
" ((currToBase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio) - stdcost(item_id)) ) AS margin,"
" 'curr' AS sales_xtnumericrole,"
" 'curr' AS cost_xtnumericrole,"
" 'curr' AS margin_xtnumericrole,"
" 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,"
" ( (COALESCE(cobmisc_cohead_id,0) > 0)"
" AND (SUM(noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) > 0)"
" ) AS overbilled,"
" formatboolyn(CASE WHEN (pack_head_id IS NOT NULL) THEN TRUE"
" ELSE FALSE"
" END) AS packed "
"FROM coitem, itemsite, item, cust, cohead "
" LEFT OUTER JOIN cosmisc ON (cosmisc_cohead_id=cohead_id) "
" LEFT OUTER JOIN (SELECT DISTINCT cobmisc_cohead_id FROM cobmisc) AS cobmisc ON (cobmisc_cohead_id=cohead_id) "
" LEFT OUTER JOIN pack ON (cohead_id = pack_head_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 <? value(\"startDate\") ?>"
" AND <? value(\"endDate\") ?>)"
"<? if exists(\"warehous_id\") ?>"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
"<? endif ?>"
"<? if exists(\"custtype_id\") ?>"
" AND (cust_custtype_id=<? value(\"custtype_id\") ?>)"
"<? elseif exists(\"custtype_pattern\") ?>"
" AND (cust_custtype_id IN (SELECT custtype_id FROM custtype"
" WHERE (custtype_code ~ <? value(\"custtype_pattern\") ?>)))"
"<? endif ?>"
") "
"GROUP BY cohead_id, cohead_number, cust_name,"
" cohead_holdtype, cohead_orderdate, cohead_packdate,"
" cosmisc_shipped, cosmisc_shipvia, cosmisc_shipdate,"
" cosmisc_id, cobmisc_cohead_id, cohead_created, pack_head_id "
" ORDER BY "
"<? if exists(\"orderByShipDate\") ?>scheddate,"
"<? elseif exists(\"orderByPackDate\") ?>cohead_packdate,"
"<? endif ?>"
" cohead_number, cosmisc_shipped;");
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
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 XTreeWidgetItem( _so, orderLine,
q.value("cohead_id").toInt(), -1,
q.value("cohead_number"), q.value("cust_name"),
q.value("f_holdtype"), q.value("cohead_orderdate"),
q.value("scheddate"), q.value("cohead_packdate"),
formatMoney(q.value("sales").toDouble()),
formatMoney(q.value("cost").toDouble()),
formatMoney(q.value("margin").toDouble()),
q.value("cohead_created"), q.value("packed"));
totalSales += q.value("sales").toDouble();
totalCost += q.value("cost").toDouble();
totalMargin += q.value("margin").toDouble();
}
if (q.value("overbilled").toBool())
{
overbilled = TRUE;
orderLine->setTextColor("red");
}
if (q.value("shipstatus").toInt())
{
XTreeWidgetItem *shipLine = new XTreeWidgetItem(orderLine, -1,
q.value("cosmisc_id").toInt());
if (q.value("shipstatus").toInt() == 1)
{
shipLine->setText(0, tr("Yes"));
shipLine->setTextColor("green");
if (!unshipped && !overbilled)
orderLine->setTextColor("green");
}
else if (q.value("shipstatus").toInt() == 2)
{
shipLine->setText(0, tr("No"));
shipLine->setTextColor("blue");
if(!overbilled)
orderLine->setTextColor("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 XTreeWidgetItem( _so, orderLine, -1,
"", "", tr("Total Backlog"), "", "", "",
formatMoney(totalSales),
formatCost(totalCost),
formatMoney(totalMargin) );
QString tots = "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 <? value(\"startDate\") ?>"
" AND <? value(\"endDate\") ?>)"
"<? if exists(\"warehous_id\") ?>"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
"<? endif ?>"
"<? if exists(\"custtype_id\") ?>"
" AND (cust_custtype_id=<? value(\"custtype_id\") ?>)"
"<? elseif exists(\"custtype_pattern\") ?>"
" AND (cust_custtype_id IN (SELECT custtype_id FROM custtype"
" WHERE (custtype_code ~ <? value(\"custtype_pattern\") ?>)))"
"<? endif ?>"
") ) AS data;";
MetaSQLQuery totm(tots);
q = totm.toQuery(params);
if (q.first())
_totalSalesOrders->setText(q.value("totalorders").toString());
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
QString cnts = "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 <? value(\"startDate\") ?>"
" AND <? value(\"endDate\") ?>)"
"<? if exists(\"warehous_id\") ?>"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
"<? endif ?>"
"<? if exists(\"custtype_id\") ?>"
" AND (cust_custtype_id=<? value(\"custtype_id\") ?>)"
"<? elseif exists(\"custtype_pattern\") ?>"
" AND (cust_custtype_id IN (SELECT custtype_id FROM custtype"
" WHERE (custtype_code ~ <? value(\"custtype_pattern\") ?>)))"
"<? endif ?>"
");";
MetaSQLQuery cntm(cnts);
q = cntm.toQuery(params);
if (q.first())
_totalLineItems->setText(q.value("totalitems").toString());
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
QString qtys = "SELECT formatQty(SUM(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) 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 <? value(\"startDate\") ?>"
" AND <? value(\"endDate\") ?>)"
"<? if exists(\"warehous_id\") ?>"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
"<? endif ?>"
"<? if exists(\"custtype_id\") ?>"
" AND (cust_custtype_id=<? value(\"custtype_id\") ?>)"
"<? elseif exists(\"custtype_pattern\") ?>"
" AND (cust_custtype_id IN (SELECT custtype_id FROM custtype"
" WHERE (custtype_code ~ <? value(\"custtype_pattern\") ?>)))"
"<? endif ?>"
");";
MetaSQLQuery qtym(qtys);
q = qtym.toQuery(params);
if (q.first())
_totalQty->setText(q.value("f_totalqty").toString());
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
else
{
_totalSalesOrders->clear();
_totalLineItems->clear();
_totalQty->clear();
}
}
}
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 dspSalesHistoryByCustomer::sFillList()
{
_sohist->clear();
if (!checkParameters())
return;
QString sql( "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,"
" formatQty(cohist_qtyshipped) AS f_shipped " );
sql += ", formatSalesPrice(cohist_unitprice) AS f_unitprice,"
" round((cohist_qtyshipped * cohist_unitprice),2) AS extprice,"
" formatMoney(round(cohist_qtyshipped * cohist_unitprice,2)) AS f_extprice ";
sql += ", formatSalesPrice(cohist_unitcost) AS f_unitcost,"
" (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)"
" AND (cohist_cust_id=:cust_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 += " ) "
"ORDER BY cohist_invcdate, item_number;";
q.prepare(sql);
_warehouse->bindValue(q);
_productCategory->bindValue(q);
_dates->bindValue(q);
q.bindValue(":cust_id", _cust->id());
q.exec();
XTreeWidgetItem *last = 0;
if (q.first())
{
double totalSales = 0.0;
double totalCosts = 0.0;
do
{
last = new XTreeWidgetItem( _sohist, last,
q.value("cohist_id").toInt(),
q.value("cohist_invcnumber").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"),
q.value("f_unitprice"),
q.value("f_extprice"),
q.value("f_unitcost"),
q.value("f_extcost"));
totalSales += q.value("extprice").toDouble();
totalCosts += q.value("extcost").toDouble();
}
while (q.next());
if ( (_showPrices->isChecked()) || (_showCosts->isChecked()) )
{
XTreeWidgetItem *totals = new XTreeWidgetItem(_sohist, last, -1);
totals->setText(5, tr("Total"));
totals->setText(EXTPRICE_COL, formatMoney(totalSales));
totals->setText(EXTCOST_COL, formatCost(totalCosts));
}
}
}
void maintainItemCosts::sFillList()
{
if (_item->isValid())
{
double standardCost = 0.0;
double actualCostBase = 0.0;
double actualCostLocal = 0.0;
q.prepare( "SELECT itemcost_id,"
" CASE WHEN (costelem_sys) THEN 1"
" ELSE 0"
" END,"
" CASE WHEN itemcost_costelem_id = -1 THEN :error "
" ELSE costelem_type "
" END AS costelem_type, formatBoolYN(itemcost_lowlevel),"
" formatCost(itemcost_stdcost), currConcat(baseCurrID()), "
" formatDate(itemcost_posted, 'Never'),"
" formatCost(itemcost_actcost), currConcat(itemcost_curr_id) AS currConcat, "
" formatDate(itemcost_updated, 'Never'),"
" itemcost_stdcost AS stdcost, "
" itemcost_actcost AS actcostLocal, "
" currToBase(itemcost_curr_id, itemcost_actcost, CURRENT_DATE) AS actcostBase, "
" itemcost_curr_id "
"FROM itemcost LEFT OUTER JOIN costelem ON "
" (itemcost_costelem_id=costelem_id)"
"WHERE (itemcost_item_id=:item_id) "
"ORDER BY itemcost_lowlevel, costelem_type;" );
q.bindValue(":item_id", _item->id());
q.bindValue(":error", tr("!ERROR!"));
q.exec();
_itemcost->populate(q, TRUE);
bool multipleCurrencies = false;
int firstCurrency = 0;
bool baseKnown = true;
if (q.first())
{
firstCurrency = q.value("itemcost_curr_id").toInt();
do
{
standardCost += q.value("stdcost").toDouble();
if (q.value("actcostBase").isNull())
baseKnown = false;
else
actualCostBase += q.value("actcostBase").toDouble();
actualCostLocal += q.value("actcostLocal").toDouble();
if (! multipleCurrencies &&
q.value("itemcost_curr_id").toInt() != firstCurrency)
multipleCurrencies = true;
}
while (q.next());
}
XSqlQuery convert;
double actualCost = 0;
if (multipleCurrencies)
{
actualCost = actualCostBase;
convert.prepare("SELECT currConcat(baseCurrId()) AS baseConcat, "
" currConcat(baseCurrId()) AS currConcat;");
}
else
{
actualCost = actualCostLocal;
baseKnown = true; // not necessarily but we can trust actualCost
convert.prepare("SELECT currConcat(baseCurrId()) AS baseConcat, "
" currConcat(:firstCurrency) AS currConcat;" );
convert.bindValue(":firstCurrency", firstCurrency);
}
convert.exec();
if (convert.first())
new XTreeWidgetItem(_itemcost,
_itemcost->topLevelItem(_itemcost->topLevelItemCount() - 1), -1,
QVariant(tr("Totals")),
"",
formatCost(standardCost),
convert.value("baseConcat"),
"",
baseKnown ? formatCost(actualCost) : tr("?????"),
convert.value("currConcat"));
else if (convert.lastError().type() != QSqlError::NoError)
systemError(this, convert.lastError().databaseText(), __FILE__, __LINE__);
}
else
_itemcost->clear();
}
void dspCostedIndentedBOM::sFillList()
{
double totalCosts = 0;
_bomitem->clear();
if (_item->isValid())
{
QString sql( "SELECT 2 AS seqord, "
" bomdata_bomwork_id, bomdata_item_id, bomdata_bomwork_parent_id,"
" bomdata_bomwork_seqnumber, bomdata_item_number, bomdata_uom_name,"
" bomdata_itemdescription,"
" bomdata_qtyper,"
" bomdata_scrap,"
" bomdata_effective,"
" bomdata_expires," );
if (_useStandardCosts->isChecked())
sql += " formatCost(bomdata_stdunitcost) AS f_unitcost,"
" formatCost(bomdata_stdextendedcost) AS f_extendedcost,"
" bomdata_stdextendedcost AS extendedcost, ";
else if (_useActualCosts->isChecked())
sql += " formatCost(bomdata_actunitcost) AS f_unitcost,"
" formatCost(bomdata_actextendedcost) AS f_extendedcost,"
" bomdata_actextendedcost AS extendedcost, ";
sql += "bomdata_bomwork_level "
"FROM indentedbom(:item_id,:revision_id,0,0) ";
q.prepare(sql);
q.bindValue(":item_id", _item->id());
q.bindValue(":revision_id", _revision->id());
q.exec();
QStack<XTreeWidgetItem*> parent;
XTreeWidgetItem *last = 0;
int level = 1;
while(q.next())
{
// If the level this item is on is lower than the last level we just did then we need
// to pop the stack a number of times till we are equal.
while(q.value("bomdata_bomwork_level").toInt() < level)
{
level--;
last = parent.pop();
}
// If the level this item is on is higher than the last level we need to push the last
// item onto the stack a number of times till we are equal. (Should only ever be 1.)
while(q.value("bomdata_bomwork_level").toInt() > level)
{
level++;
parent.push(last);
last = 0;
}
// If there is an item in the stack use that as the parameter to the new xlistviewitem
// otherwise we'll just use the xlistview _layout
if(!parent.isEmpty() && parent.top())
{
last = new XTreeWidgetItem(parent.top(), last, q.value("bomwork_id").toInt(),
q.value("bomdata_item_id").toInt(),
q.value("bomdata_bomwork_seqnumber"),
q.value("bomdata_item_number"),
q.value("bomdata_itemdescription"),
q.value("bomdata_uom_name"),
q.value("bomdata_qtyper"),
q.value("bomdata_scrap"),
q.value("bomdata_effective"),
q.value("bomdata_expires"),
q.value("f_unitcost"),
q.value("f_extendedcost") );
}
else
{
if (q.value("bomdata_bomwork_parent_id").toInt() == -1)
{
if (q.value("bomdata_bomwork_id").toInt() == -1)
{
last = new XTreeWidgetItem( _bomitem,last, -1, -1,
"", q.value("bomdata_item_number"),
"", "", "", "", "" );
last->setText(7, "");
last->setText(8, q.value("f_unitcost").toString());
last->setText(9, q.value("f_extendedcost").toString());
}
else
{
last = new XTreeWidgetItem( _bomitem, last, q.value("bomdata_bomwork_id").toInt(), q.value("bomdata_item_id").toInt(),
q.value("bomdata_bomwork_seqnumber"), q.value("bomdata_item_number"),
q.value("bomdata_itemdescription"), q.value("bomdata_uom_name"),
q.value("bomdata_qtyper"), q.value("bomdata_scrap"),
q.value("bomdata_effective"), q.value("bomdata_expires"),
q.value("f_unitcost"), q.value("f_extendedcost") );
}
}
else
{
last = new XTreeWidgetItem(_bomitem, last, q.value("bomwork_id").toInt(),
q.value("bomdata_item_id").toInt(),
q.value("bomdata_bomwork_seqnumber"),
q.value("bomdata_item_number"),
q.value("bomdata_itemdescription"),
q.value("bomdata_uom_name"),
q.value("bomdata_qtyper"),
q.value("bomdata_scrap"),
q.value("bomdata_effective"),
q.value("bomdata_expires"),
q.value("f_unitcost"),
q.value("f_extendedcost") );
}
totalCosts += q.value("extendedcost").toDouble();
}
}
last = new XTreeWidgetItem(_bomitem, -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 XTreeWidgetItem(_bomitem, -1, -1);
last->setText(1, tr("Actual Cost"));
last->setText(9, q.value("actual").toString());
last = new XTreeWidgetItem( _bomitem, -1, -1);
last->setText(1, tr("Standard Cost"));
last->setText(9, q.value("standard").toString());
}
_bomitem->expandAll();
}
}
void dspItemCostDetail::sFillList(int pItemid, bool pLocale)
{
if ( (pLocale) || (pItemid == _item->id()) )
{
_bom->clear();
if (_item->isValid())
{
QString sql;
if ( (_item->itemType() == "M") ||
(_item->itemType() == "F") ||
(_item->itemType() == "B") ||
(_item->itemType() == "T") ||
(_item->itemType() == "Y") ||
(_item->itemType() == "R") ||
(_item->itemType() == "O") ||
(_item->itemType() == "P") )
{
sql = "SELECT bomitem_id, item_id, seqnumber, item_number,"
" (item_descrip1 || ' ' || item_descrip2) AS itemdescrip, uom_name,"
" formatQtyper(qtyper) AS f_qtyper,"
" formatScrap(bomitem_scrap) AS f_scrap,"
" formatCost(cost) AS f_cost,"
" formatCost(extendedcost) AS f_extendedcost,"
" extendedcost "
"FROM ( SELECT bomitem_id, bomitem_seqnumber AS seqnumber, itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, bomitem_qtyper) AS qtyper, bomitem_scrap,"
" item_id, item_number, item_descrip1, item_descrip2, uom_name, ";
if (_standardCosts->isChecked())
sql += " itemcost_stdcost AS cost,"
" (itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, bomitem_qtyper * (1 + bomitem_scrap)) * itemcost_stdcost) AS extendedcost ";
else
sql += " itemcost_actcost AS cost,"
" (itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, bomitem_qtyper * (1 + bomitem_scrap)) * itemcost_actcost) AS extendedcost ";
sql += "FROM bomitem, item, itemcost, costelem, uom "
"WHERE ( (bomitem_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (CURRENT_DATE BETWEEN bomitem_effective AND (bomitem_expires-1))"
" AND (itemcost_item_id=item_id)"
" AND (itemcost_costelem_id=costelem_id)"
" AND (bomitem_parent_item_id=:item_id)"
" AND (costelem_id=:costelem_id) ) ) AS data "
"ORDER BY seqnumber;";
}
else if (_item->itemType() == "C")
{
sql = "SELECT bbomitem_id, item_id, seqnumber, item_number,"
" (item_descrip1 || ' ' || item_descrip2) AS itemdescrip, uom_name,"
" formatQtyPer(bbomitem_qtyper) AS f_qtyper,"
" formatScrap(bbomitem_costabsorb) AS f_scrap,"
" formatCost(cost) AS f_cost,"
" formatCost(extendedcost) AS f_extendedcost,"
" extendedcost "
"FROM ( SELECT bbomitem_id, bbomitem_seqnumber AS seqnumber, bbomitem_qtyper, bbomitem_costabsorb,"
" item_id, item_number, item_descrip1, item_descrip2, uom_name, ";
if (_standardCosts->isChecked())
sql += " itemcost_stdcost AS cost,"
" (itemcost_stdcost / bbomitem_qtyper * bbomitem_costabsorb) AS extendedcost ";
else
sql += " itemcost_actcost AS cost,"
" (itemcost_actcost / bbomitem_qtyper * bbomitem_costabsorb) AS extendedcost ";
sql += "FROM bbomitem, item, itemcost, uom "
"WHERE ( (bbomitem_parent_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (CURRENT_DATE BETWEEN bbomitem_effective AND (bbomitem_expires-1))"
" AND (itemcost_item_id=bbomitem_parent_item_id)"
" AND (itemcost_costelem_id=:costelem_id)"
" AND (bbomitem_item_id=:item_id) )"
"UNION SELECT source.bbomitem_id, source.bbomitem_seqnumber AS seqnumber,"
" source.bbomitem_qtyper, target.bbomitem_costabsorb,"
" item_id, item_number, item_descrip1, item_descrip2, uom_name,";
if (_standardCosts->isChecked())
sql += " itemcost_stdcost AS cost,"
" (itemcost_stdcost * source.bbomitem_qtyper / target.bbomitem_qtyper * target.bbomitem_costabsorb) AS extendedcost ";
else
sql += " itemcost_actcost AS cost,"
" (itemcost_actcost * source.bbomitem_qtyper / target.bbomitem_qtyper * target.bbomitem_costabsorb) AS extendedcost ";
sql += "FROM item, itemcost, bbomitem AS target, bbomitem AS source, uom "
"WHERE ( (source.bbomitem_parent_item_id=target.bbomitem_parent_item_id)"
" AND (CURRENT_DATE BETWEEN source.bbomitem_effective AND (source.bbomitem_expires-1))"
" AND (CURRENT_DATE BETWEEN target.bbomitem_effective AND (target.bbomitem_expires-1))"
" AND (source.bbomitem_item_id=itemcost_item_id)"
" AND (source.bbomitem_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (item_type='Y')"
" AND (target.bbomitem_item_id=:item_id)"
" AND (itemcost_costelem_id=:costelem_id) ) ) AS data "
"ORDER BY seqnumber;";
}
q.prepare(sql);
q.bindValue(":costelem_id", _costType->id());
q.bindValue(":item_id", _item->id());
q.exec();
double extendedCost = 0.0;
XTreeWidgetItem *last = 0;
while (q.next())
{
last = new XTreeWidgetItem(_bom, last, q.value("bomitem_id").toInt(),
q.value("item_id").toInt(),
q.value("seqnumber"),
q.value("item_number"),
q.value("itemdescrip"),
q.value("uom_name"),
q.value("f_qtyper"),
q.value("f_scrap"),
q.value("f_cost"),
q.value("f_extendedcost") );
extendedCost += q.value("extendedcost").toDouble();
}
last = new XTreeWidgetItem(_bom, last, -1, -1,
"", tr("Totals:"),
"", "", "", "", "", formatCost(extendedCost) );
}
}
}
void task::sExpensesAdjusted()
{
_balanceExp->setText(formatCost(_budgetExp->text().toDouble() - _actualExp->text().toDouble()));
}
void CurrDisplay::sReformat() const
{
if (DEBUG)
qDebug("CD %s::sReformat() entered with _state %d, _format %d",
qPrintable(objectName()), _state, _format);
QString na = tr("N/A");
QString unknown = tr(UNKNOWNSTR);
QString local = "";
switch (_state)
{
case New:
local = "";
break;
case NANew:
local = na;
break;
case Initialized:
if (isZero())
switch (_format)
{
case SalesPrice:
local = formatSalesPrice(0.0, id());
break;
case PurchPrice:
local = formatPurchPrice(0.0, id());
break;
case ExtPrice:
local = formatExtPrice(0.0, id());
break;
case Cost:
local = formatCost(0.0, id());
break;
case Money:
default:
local = formatMoney(0.0, id());
break;
}
else if (_localKnown)
switch (_format)
{
case SalesPrice:
local = formatSalesPrice(_valueLocal, id());
break;
case PurchPrice:
local = formatPurchPrice(_valueLocal, id());
break;
case ExtPrice:
local = formatExtPrice(_valueLocal, id());
break;
case Cost:
local = formatCost(_valueLocal, id());
break;
case Money:
default:
local = formatMoney(_valueLocal, id());
break;
}
else
local = unknown;
break;
case NAInit:
if (isZero())
local = na;
else
switch (_format)
{
case SalesPrice:
local = formatSalesPrice(_valueLocal, id());
break;
case PurchPrice:
local = formatPurchPrice(_valueLocal, id());
break;
case ExtPrice:
local = formatExtPrice(_valueLocal, id());
break;
case Cost:
local = formatCost(_valueLocal, id());
break;
case Money:
default:
local = formatMoney(_valueLocal, id());
break;
}
break;
default:
break;
}
_valueLocalWidget->setText(local);
}
void dspCountTagEditList::sFillList()
{
_cnttag->clear();
QString sql( "SELECT invcnt_id, -1 AS cntslip_id, formatBoolYN(invcnt_priority) AS priority,"
" CASE WHEN (invcnt_tagnumber IS NULL) THEN 'Misc.'"
" ELSE invcnt_tagnumber"
" END AS tagnumber,"
" TEXT('') AS cntslip_number,"
" formatDate(invcnt_tagdate) AS tagdate,"
" item_number, warehous_code,"
" CASE WHEN (location_id IS NOT NULL) THEN"
" location_name"
" ELSE <? value(\"all\") ?> END AS loc_specific, "
" CASE WHEN (invcnt_location_id IS NOT NULL)"
" THEN (SELECT formatQty(SUM(itemloc_qty))"
" FROM itemloc"
" WHERE ((itemloc_itemsite_id=itemsite_id)"
" AND (itemloc_location_id=invcnt_location_id)) )"
" ELSE formatQty(itemsite_qtyonhand)"
" END AS qoh,"
" CASE WHEN (invcnt_qoh_after IS NOT NULL) THEN formatQty(invcnt_qoh_after)"
" WHEN ( ( SELECT SUM(cntslip_qty)"
" FROM cntslip"
" WHERE (cntslip_cnttag_id=invcnt_id) ) IS NOT NULL ) THEN ( SELECT formatQty(SUM(cntslip_qty))"
" FROM cntslip"
" WHERE (cntslip_cnttag_id=invcnt_id) )"
" ELSE ''"
" END AS qohafter,"
" CASE WHEN (invcnt_qoh_after IS NULL) THEN ''"
" ELSE formatQty(invcnt_qoh_after - itemsite_qtyonhand)"
" END AS variance,"
" CASE WHEN (invcnt_qoh_after IS NULL) THEN ''"
" WHEN ((itemsite_qtyonhand = 0) AND (invcnt_qoh_after > 0)) THEN formatScrap(1)"
" WHEN ((itemsite_qtyonhand = 0) AND (invcnt_qoh_after < 0)) THEN formatScrap(-1)"
" WHEN ((itemsite_qtyonhand = 0) AND (invcnt_qoh_after = 0)) THEN formatScrap(0)"
" ELSE (formatScrap((1 - (invcnt_qoh_after / itemsite_qtyonhand)) * -1))"
" END AS varianceprcnt,"
" (stdcost(item_id) * (invcnt_qoh_after - itemsite_qtyonhand)) AS variancecost,"
" item_number AS orderby,"
" CASE WHEN (invcnt_qoh_after IS NULL) THEN FALSE"
" ELSE TRUE"
" END AS hascount "
"FROM invcnt LEFT OUTER JOIN location ON (invcnt_location_id=location_id),"
" item, warehous, itemsite "
"WHERE ( (invcnt_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (NOT invcnt_posted)"
" <? if exists(\"warehous_id\") ?>"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
" <? endif ?>"
" <? if exists(\"classcode_id\") ?>"
" AND (item_classcode_id=<? value(\"classcode_id\") ?>)"
" <? elseif exists(\"classcode_pattern\") ?>"
" AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ <? value(\"classcode_pattern\") ?>)))"
" <? elseif exists(\"plancode_id\") ?>"
" AND (itemsite_plancode_id=<? value(\"plancode_id\") ?>)"
" <? elseif exists(\"plancode_pattern\") ?>"
" AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ <? value(\"plancode_pattern\") ?>)))"
" <? endif ?>"
" <? if exists(\"showSlips\") ?>"
" ) "
"UNION "
"SELECT invcnt_id, cntslip_id, formatBoolYN(invcnt_priority) AS priority,"
" '' AS tagnumber, cntslip_number,"
" formatDate(cntslip_entered) AS tagdate,"
" CASE WHEN (cntslip_posted) THEN <? value(\"posted\") ?>"
" ELSE <? value(\"unposted\") ?>"
" END AS item_number,"
" '' AS warehous_code, "
" '' AS loc_specific, "
" '' AS qoh,"
" formatQty(cntslip_qty) AS qohafter,"
" '' AS variance, '' AS varianceprcnt, 0 AS variancecost,"
" item_number AS orderby,"
" FALSE AS hascount "
"FROM cntslip, invcnt, itemsite, item "
"WHERE ( (cntslip_cnttag_id=invcnt_id)"
" AND (invcnt_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (NOT invcnt_posted)"
" <? if exists(\"warehous_id\") ?>"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
" <? endif ?>"
" <? if exists(\"classcode_id\") ?>"
" AND (item_classcode_id=<? value(\"classcode_id\") ?>)"
" <? elseif exists(\"classcode_pattern\") ?>"
" AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ <? value(\"classcode_pattern\") ?>)))"
" <? elseif exists(\"plancode_id\") ?>"
" AND (itemsite_plancode_id=<? value(\"plancode_id\") ?>)"
" <? elseif exists(\"plancode_pattern\") ?>"
" AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ <? value(\"plancode_pattern\") ?>)))"
" <? endif ?>"
" <? endif ?>"
" ) "
"ORDER BY priority DESC, orderby, invcnt_id, cntslip_id;" );
ParameterList params;
setParams(params);
params.append("all", tr("All"));
params.append("posted", tr("Posted"));
params.append("unposted", tr("Unposted"));
if (_showSlips->isChecked())
params.append("showSlips");
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
if (q.first())
{
XTreeWidgetItem *countTag = NULL;
int invcntid = -1;
do
{
if (q.value("cntslip_id").toInt() == -1)
{
if (invcntid != q.value("invcnt_id").toInt())
{
invcntid = q.value("invcnt_id").toInt();
countTag = new XTreeWidgetItem( _cnttag, countTag,
q.value("invcnt_id").toInt(), q.value("cntslip_id").toInt(),
q.value("priority"), q.value("tagnumber"),
q.value("tagdate"), q.value("item_number"),
q.value("warehous_code"),
q.value("loc_specific"),
q.value("qoh"),
q.value("qohafter"), q.value("variance"),
q.value("varianceprcnt"), formatCost(q.value("variancecost").toDouble()) );
if ( ( (_highlightValue->isChecked()) && (_varianceValue->text().length()) &&
(fabs(q.value("variancecost").toDouble()) > _varianceValue->toDouble()) ) ||
( (_highlightPercent->isChecked()) && (_variancePercent->text().length()) &&
(fabs(q.value("varianceprcnt").toDouble()) > _variancePercent->toDouble()) ) )
countTag->setTextColor("red");
if (!q.value("hascount").toBool())
countTag->setTextColor(7, "blue");
}
}
else if (countTag)
new XTreeWidgetItem( countTag, q.value("invcnt_id").toInt(), q.value("cntslip_id").toInt(),
"", q.value("cntslip_number"),
q.value("tagdate"), q.value("item_number"),
"", "", "",
q.value("qohafter"), "" );
}
while (q.next());
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
_cnttag->expandAll();
if (_searchFor->text().stripWhiteSpace().length())
sSearch(_searchFor->text());
}
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();
}
}
}
