void dspSummarizedBOM::sFillList()
{
_bomitem->clear();
ParameterList params;
if (!setParams(params))
return;
QString sql( "SELECT * FROM summarizedBOM(<? value(\"item_id\") ?>,"
" <? value(\"revision_id\") ?>,"
" <? value(\"expiredDays\") ?>,"
" <? value(\"futureDays\") ?>);" );
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
XTreeWidgetItem *last = 0;
while (q.next())
{
last = new XTreeWidgetItem(_bomitem, last, -1, q.value("bomdata_item_number"),
q.value("bomdata_itemdescription"), q.value("bomdata_uom_name"),
q.value("bomdata_qtyper") );
if (q.value("bomdata_expired").toBool())
last->setTextColor("red");
else if (q.value("bomdata_future").toBool())
last->setTextColor("blue");
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void dspWoHistoryByItem::sFillList()
{
if (!checkParameters())
return;
_wo->clear();
QString sql( "SELECT wo_id,"
" formatWONumber(wo_id) AS wonumber,"
" wo_status, warehous_code,"
" formatQty(wo_qtyord) AS qtyord,"
" formatQty(wo_qtyrcv) AS qtyrcv,"
" formatDate(wo_startdate) AS startdate,"
" formatDate(wo_duedate) AS duedate,"
" ( (wo_startdate <= CURRENT_DATE) AND (wo_status IN ('O','E','S','R')) ) AS latestart,"
" (wo_duedate <= CURRENT_DATE) AS latedue,"
" formatCost(wo_postedvalue) AS value "
"FROM wo, itemsite, warehous "
"WHERE ((wo_itemsite_id=itemsite_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (itemsite_item_id=:item_id)"
" AND (wo_duedate BETWEEN :startDate AND :endDate)" );
if (_showOnlyTopLevel->isChecked())
sql += " AND ( (wo_ordtype<>'W') OR (wo_ordtype IS NULL) )";
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
sql += ") "
"ORDER BY wo_startdate DESC, wo_number, wo_subnumber;";
q.prepare(sql);
_dates->bindValue(q);
_warehouse->bindValue(q);
q.bindValue(":item_id", _item->id());
q.exec();
XTreeWidgetItem *last = 0;
while (q.next())
{
last = new XTreeWidgetItem( _wo, last, q.value("wo_id").toInt(),
q.value("wonumber"), q.value("wo_status"),
q.value("warehous_code"), q.value("qtyord"),
q.value("qtyrcv"), q.value("startdate"),
q.value("duedate") );
if (q.value("latestart").toBool())
last->setTextColor(5, "red");
if (q.value("latedue").toBool())
last->setTextColor(6, "red");
if (_showCost->isChecked())
last->setText(7, q.value("value").toString());
}
}
void dspRunningAvailability::sHandleResort()
{
for (int i = 0; i < _availability->topLevelItemCount(); i++)
{
XTreeWidgetItem *item = _availability->topLevelItem(i);
if (item->data(RUNNINGAVAIL_COL, Qt::DisplayRole).toDouble() < 0)
item->setTextColor(RUNNINGAVAIL_COL, namedColor("error"));
else if (item->data(RUNNINGAVAIL_COL, Qt::DisplayRole).toDouble() < _reorderLevel->toDouble())
item->setTextColor(RUNNINGAVAIL_COL, namedColor("warning"));
else
item->setTextColor(RUNNINGAVAIL_COL, namedColor(""));
}
}
void dspRunningAvailability::sHandleResort()
{
for (int i = 0; i < list()->topLevelItemCount(); i++)
{
XTreeWidgetItem *item = list()->topLevelItem(i);
if (item->data(list()->column("runningavail"), Qt::DisplayRole).toDouble() < 0)
item->setTextColor(list()->column("runningavail"), namedColor("error"));
else if (item->data(list()->column("runningavail"), Qt::DisplayRole).toDouble() < _reorderLevel->toDouble())
item->setTextColor(list()->column("runningavail"), namedColor("warning"));
else
item->setTextColor(list()->column("runningavail"), namedColor(""));
}
}
void exportCustomers::sFillList(int, bool)
{
_cust->clear();
q.prepare( "SELECT cust_id, cust_number, cust_name, cust_address1,"
" formatDate(cust_dateadded) AS f_dateadded,"
" COALESCE( ( SELECT aropen_id"
" FROM aropen "
" WHERE ( (NOT aropen_posted)"
" AND (aropen_cust_id=cust_id) )"
" LIMIT 1 ), 0) AS _aropenid "
"FROM cust "
"WHERE (NOT cust_exported) "
"ORDER BY cust_number;" );
q.exec();
XTreeWidgetItem *last = 0;
while (q.next())
{
last = new XTreeWidgetItem( _cust, last, q.value("cust_id").toInt(),
q.value("cust_number"),
q.value("cust_name"),
q.value("cust_address1"),
q.value("f_dateadded") );
if (q.value("_aropenid").toInt() != 0)
last->setTextColor("red");
}
_cust->setDragString("custid=");
}
void dspPOsByVendor::sFillList()
{
MetaSQLQuery mql = mqlLoad(":/po/displays/POsByVendor/FillListDetail.mql");
ParameterList params;
setParams(params);
q = mql.toQuery(params);
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
_poitem->clear();
XTreeWidgetItem *last = 0;
while (q.next())
{
last = new XTreeWidgetItem(_poitem, last,
q.value("pohead_id").toInt(), -1,
q.value("pohead_number"),
q.value("warehousecode"),
q.value("poitemstatus"), q.value("vend_number"),
q.value("f_date"));
if (q.value("late").toBool())
last->setTextColor(4, "red");
}
}
void dspReservations::sFillList()
{
_allocations->clear();
if (_item->isValid())
{
MetaSQLQuery mql = mqlLoad(":so/displays/Reservations/FillListDetail.mql");
ParameterList params;
params.append("warehous_id", _warehouse->id());
params.append("item_id", _item->id());
q = mql.toQuery(params);
double runningBal = 0;
XTreeWidgetItem *last = 0;
while (q.next())
{
runningBal += q.value("coitem_qtyreserved").toDouble();
last = new XTreeWidgetItem(_allocations, last,
q.value("source_id").toInt(),
q.value("order_number"),
q.value("totalqty"),
q.value("relievedqty"), q.value("balanceqty"),
formatQty(runningBal), q.value("duedate") );
last->setTextColor(5, "red");
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
QString avails("SELECT formatQty(itemsite_qtyonhand) AS f_qoh,"
" formatQty(qtyunreserved(itemsite_id)) AS f_unreserved "
"FROM itemsite "
"WHERE ((itemsite_item_id=<? value(\"item_id\") ?>)"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>));");
MetaSQLQuery availm(avails);
q = availm.toQuery(params);
if (q.first())
{
_qoh->setText(q.value("f_qoh").toString());
_available->setText(q.value("f_unreserved").toString());
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
}
void dspOrders::sFillList()
{
_orders->clear();
if ( (_item->isValid()) &&
( (_leadTime->isChecked()) || (_byDays->isChecked()) ||
((_byDate->isChecked()) && (_date->isValid())) ||
(_byRange->isChecked() && _startDate->isValid() && _endDate->isValid()) ) )
{
MetaSQLQuery mql = mqlLoad(":/im/displays/Orders/FillListDetail.mql");
ParameterList params;
params.append("warehous_id", _warehouse->id());
params.append("item_id", _item->id());
params.append("itemType", _item->itemType());
if (_leadTime->isChecked())
params.append("useLeadTime");
else if (_byDays->isChecked())
params.append("days", _days->value());
else if (_byDate->isChecked())
params.append("date", _date->date());
else if (_byRange->isChecked())
{
params.append("startDate", _startDate->date());
params.append("endDate", _endDate->date());
}
q = mql.toQuery(params);
double runningBal = 0;
XTreeWidgetItem *last = 0;
while (q.next())
{
runningBal += q.value("balanceqty").toDouble();
last = new XTreeWidgetItem(_orders, last,
q.value("source_id").toInt(),
q.value("type").toInt(),
q.value("order_type"), q.value("order_number"),
q.value("totalqty"), q.value("relievedqty"),
q.value("balanceqty"), formatQty(runningBal),
q.value("duedate") );
if (q.value("late").toBool())
last->setTextColor(6, "red");
}
}
}
void dspCountSlipsByWarehouse::sFillList()
{
if (DEBUG)
qDebug("dspCountSlipsByWarehouse::sFillList() about to populate _cntslip");
display::sFillList();
if (_numericSlips->isChecked() && list()->topLevelItemCount() > 1)
{
if (DEBUG)
qDebug("dspCountSlipsByWarehouse::sFillList() looking for slip # gaps "
"in %d items", list()->topLevelItemCount());
XTreeWidgetItem *last =list()->topLevelItem(0);
int slipNumber = last->rawValue("slipnumber").toInt();
for (int i = 1; i < list()->topLevelItemCount(); i++)
{
XTreeWidgetItem *curr = list()->topLevelItem(i);
if (DEBUG)
qDebug("row %d has slipNumber %d and current %d",
i, slipNumber, curr->rawValue("slipnumber").toInt());
if (slipNumber == (curr->rawValue("slipnumber").toInt() - 1) || slipNumber == -1)
slipNumber = curr->rawValue("slipnumber").toInt();
else if (slipNumber >= 0)
{
if (slipNumber == curr->rawValue("slipnumber").toInt() - 2)
curr = new XTreeWidgetItem( list(), last, -1,
QVariant("----"), "----", "----", "----",
tr("Missing Slip #%1").arg(slipNumber + 1),
"----", "----", "----" );
else
curr = new XTreeWidgetItem( list(), last, -1,
QVariant("----"), "----", "----", "----",
tr("Missing Slips #%1 to #%2").arg(slipNumber + 1).arg(curr->rawValue("slipnumber").toInt() - 1),
"----", "----", "----" );
curr->setTextColor(namedColor("error"));
slipNumber = -1;
//i++; // 'cause we just added an item!
}
last = curr;
}
}
}
void boo::sFillList(int pItemid, bool pLocalUpdate)
{
_booitem->clear();
if (_item->itemType() == "J")
{
_closeWO->setEnabled(FALSE);
_closeWO->setChecked(FALSE);
}
int locid = _finalLocation->id();
q.prepare("SELECT location_id, (warehous_code || '-' || formatLocationName(location_id)) AS locationname"
" FROM location, warehous"
" WHERE ( (NOT location_restrict)"
" AND (location_warehous_id=warehous_id) ) "
"UNION "
"SELECT location_id, (warehous_code || '-' || formatLocationName(location_id)) AS locationname"
" FROM location, warehous, locitem"
" WHERE ( (location_warehous_id=warehous_id)"
" AND (location_restrict)"
" AND (locitem_location_id=location_id)"
" AND (locitem_item_id=:item_id) ) "
"ORDER BY locationname;");
q.bindValue(":item_id", pItemid);
q.exec();
_finalLocation->populate(q, locid);
q.prepare( "SELECT boohead_id, boohead_docnum, boohead_revision,"
" boohead_revisiondate, boohead_final_location_id,"
" boohead_closewo "
"FROM boohead "
"WHERE ((boohead_item_id=:item_id) "
"AND (boohead_rev_id=:revision_id));" );
q.bindValue(":item_id", _item->id());
q.bindValue(":revision_id", _revision->id());
q.exec();
if (q.first())
{
_documentNum->setText(q.value("boohead_docnum").toString());
_revision->setNumber(q.value("boohead_revision").toString());
_revisionDate->setDate(q.value("boohead_revisiondate").toDate());
_finalLocation->setId(q.value("boohead_final_location_id").toInt());
_closeWO->setChecked(q.value("boohead_closewo").toBool());
}
q.prepare( "SELECT MAX(booitem_execday) AS leadtime "
"FROM booitem(:item_id,:revision_id);" );
q.bindValue(":item_id", _item->id());
q.bindValue(":revision_id",_revision->id());
q.exec();
if (q.first())
_productionLeadTime->setText(q.value("leadtime").toString());
QString sql( "SELECT booitem_id, booitem_seqnumber,"
" COALESCE(stdopn_number, :none) AS f_stdopnnumber,"
" wrkcnt_code, (booitem_descrip1 || ' ' || booitem_descrip2) AS description,"
" formatDate(booitem_effective, 'Always') AS f_effective,"
" formatDate(booitem_expires, 'Never') AS f_expires,"
" booitem_execday, (booitem_configtype<>'N') AS config "
"FROM wrkcnt,"
" booitem(:item_id,:revision_id) LEFT OUTER JOIN stdopn ON (booitem_stdopn_id=stdopn_id) "
"WHERE ((booitem_wrkcnt_id=wrkcnt_id)" );
if (!_showExpired->isChecked())
sql += " AND (booitem_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (booitem_effective <= CURRENT_DATE)";
sql += ") "
"ORDER BY booitem_seqnumber, booitem_effective";
q.prepare(sql);
q.bindValue(":none", tr("None"));
q.bindValue(":item_id", _item->id());
q.bindValue(":revision_id",_revision->id());
q.exec();
if (q.first())
{
XTreeWidgetItem *selected = 0;
XTreeWidgetItem *last = 0;
do
{
last = new XTreeWidgetItem( _booitem, last, q.value("booitem_id").toInt(),
q.value("booitem_seqnumber"), q.value("f_stdopnnumber"),
q.value("wrkcnt_code"), q.value("description"),
q.value("f_effective"), q.value("f_expires"),
q.value("booitem_execday") );
if (q.value("config").toBool())
last->setTextColor("blue");
if (q.value("booitem_id").toInt() == pItemid)
selected = last;
}
while (q.next());
if ( (selected) && (pLocalUpdate) )
{
_booitem->setCurrentItem(selected);
_booitem->scrollTo(_booitem->currentIndex());
}
}
}
void dspTrialBalances::sFillList()
{
_trialbal->clear();
QString sql( "SELECT accnt_id, period_id, accnt_descrip, trialbal_dirty,"
" formatDate(period_start) AS f_start,"
" formatDate(period_end) AS f_end,"
" formatGLAccount(accnt_id) AS account,"
" formatMoney(abs(trialbal_beginning)) AS f_beginning,"
" (trialbal_beginning*-1) AS beginning,"
" formatMoney(trialbal_debits) AS f_debits,"
" trialbal_debits AS debits,"
" formatMoney(trialbal_credits) AS f_credits,"
" trialbal_credits AS credits,"
" formatMoney(abs(trialbal_ending)) AS f_ending,"
" (trialbal_ending*-1) AS ending,"
" formatMoney(abs(trialbal_debits - trialbal_credits)) AS f_diff,"
" (trialbal_debits - trialbal_credits) AS diff "
"FROM trialbal, accnt, period "
"WHERE ( (trialbal_accnt_id=accnt_id)"
" AND (trialbal_period_id=period_id)"
"<? if exists(\"accnt_id\") ?>"
" AND (trialbal_accnt_id=<? value(\"accnt_id\") ?>)"
"<? endif ?>"
"<? if exists(\"period_id\") ?>"
" AND (period_id=<? value(\"period_id\") ?>)"
"<? endif ?>"
") "
"ORDER BY period_start, formatGLAccount(accnt_id);" );
ParameterList params;
setParams(params);
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
if (q.first())
{
double beginning = 0.0;
double ending = 0.0;
double debits = 0.0;
double credits = 0.0;
double diff = 0.0;
XTreeWidgetItem *last = 0;
do
{
beginning = q.value("beginning").toDouble();
ending = q.value("ending").toDouble();
debits = q.value("debits").toDouble();
credits = q.value("credits").toDouble();
diff = q.value("diff").toDouble();
last = new XTreeWidgetItem(_trialbal, last,
q.value("accnt_id").toInt(),
q.value("period_id").toInt(),
q.value("f_start"), q.value("f_end"),
q.value("account"), q.value("accnt_descrip"),
q.value("f_beginning"),
(beginning<0?tr("CR"):""),
q.value("f_debits"), q.value("f_credits"),
q.value("f_diff"), (diff<0?tr("CR"):""),
q.value("f_ending") );
last->setText(11, (ending<0?tr("CR"):""));
if (q.value("trialbal_dirty").toBool())
last->setTextColor(10, "orange");
}
while (q.next());
QString sql( "SELECT formatMoney(abs(SUM(trialbal_beginning))) AS f_beginning,"
" SUM(trialbal_beginning*-1) AS beginning,"
" formatMoney(SUM(trialbal_debits)) AS f_debits,"
" formatMoney(SUM(trialbal_credits)) AS f_credits,"
" formatMoney(abs(SUM(trialbal_ending))) AS f_ending,"
" SUM(trialbal_ending*-1) AS ending,"
" formatMoney(abs(SUM(trialbal_debits - trialbal_credits))) AS f_diff,"
" SUM(trialbal_debits - trialbal_credits) AS diff "
"FROM trialbal, period "
"WHERE ( (trialbal_period_id=period_id)"
"<? if exists(\"accnt_id\") ?>"
" AND (trialbal_accnt_id=<? value(\"accnt_id\") ?>)"
"<? endif ?>"
"<? if exists(\"period_id\") ?>"
" AND (period_id=<? value(\"period_id\") ?>)"
"<? endif ?>"
");" );
MetaSQLQuery totalmql(sql);
q = totalmql.toQuery(params);
if (q.first())
{
last = new XTreeWidgetItem(_trialbal, last, -1, -1,
"", "", tr("Total"), "",
q.value("f_beginning"),
(q.value("beginning").toDouble()<0?tr("CR"):""),
q.value("f_debits"),
q.value("f_credits"),
q.value("f_diff"),
(q.value("diff").toDouble()<0?tr("CR"):""),
q.value("f_ending") );
last->setText(11, (q.value("ending").toDouble()<0?tr("CR"):""));
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
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 dspInventoryAvailabilityBySourceVendor::sFillList()
{
_availability->clear();
if ((_byDate->isChecked()) && (!_date->isValid()))
{
QMessageBox::critical( this, tr("Enter Valid Date"),
tr( "You have choosen to view Inventory Availabilty as of a given date but have not\n"
"indicated the date. Please enter a valid date." ) );
_date->setFocus();
return;
}
if ((_byDates->isChecked()) && ( (!_startDate->isValid()) || (!_endDate->isValid()) ) )
{
QMessageBox::critical( this, tr("Enter Dates"),
tr( "You have choosen to view Inventory Availabilty as of a given Start and End Date but have not\n"
"indicated the dates. Please enter valid dates." ) );
_startDate->setFocus();
return;
}
QString sql( "SELECT itemsite_id,"
" vend_number,"
" item_number, (item_descrip1 || ' ' || item_descrip2) AS description,"
" warehous_id, warehous_code, itemsite_leadtime,"
" formatQty(qoh) AS f_qoh,"
" formatQty(noNeg(qoh - allocated)) AS f_unallocated,"
" formatQty(noNeg(allocated)) AS f_allocated,"
" formatQty(ordered) AS f_ordered,"
" formatQty(reorderlevel) AS f_reorderlevel,"
" formatQty(outlevel) AS f_outlevel,"
" (qoh - allocated + ordered) AS available,"
" formatQty(qoh - allocated + ordered) AS f_available,"
" ((qoh - allocated + ordered) < 0) AS stockout,"
" ((qoh - allocated + ordered) <= reorderlevel) AS reorder "
"FROM ( SELECT itemsite_id, vend_number,"
" item_number, item_descrip1, item_descrip2,"
" warehous_id, warehous_code, itemsite_leadtime,"
" CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel,"
" CASE WHEN(itemsite_useparams) THEN itemsite_ordertoqty ELSE 0.0 END AS outlevel,"
" itemsite_qtyonhand AS qoh," );
if (_leadTime->isChecked())
sql += " qtyAllocated(itemsite_id, itemsite_leadtime) AS allocated,"
" qtyOrdered(itemsite_id, itemsite_leadtime) AS ordered ";
else if (_byDays->isChecked())
sql += " qtyAllocated(itemsite_id, :days) AS allocated,"
" qtyOrdered(itemsite_id, :days) AS ordered ";
else if (_byDate->isChecked())
sql += " qtyAllocated(itemsite_id, (:date - CURRENT_DATE)) AS allocated,"
" qtyOrdered(itemsite_id, (:date - CURRENT_DATE)) AS ordered ";
else if (_byDates->isChecked())
sql += " qtyAllocated(itemsite_id, :startDate, :endDate) AS allocated,"
" qtyOrdered(itemsite_id, :startDate, :endDate) AS ordered ";
sql += "FROM item, itemsite, warehous, vend, itemsrc "
"WHERE ( (itemsite_active)"
" AND (itemsite_item_id=item_id)"
" AND (itemsrc_item_id=item_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (itemsrc_vend_id=vend_id)";
if (_selectedVendor->isChecked())
sql += " AND (vend_id=:vend_id)";
else if (_selectedVendorType->isChecked())
sql += " AND (vend_vendtype_id=:vendtype_id)";
else if (_vendorTypePattern->isChecked())
sql += " AND (vend_vendtype_id IN (SELECT vendtype_id FROM vendtype WHERE (vendtype_code ~ :vendtype_code))) ";
if (_warehouse->isSelected())
sql += " AND (warehous_id=:warehous_id)";
sql += ") ) AS data ";
if (_showReorder->isChecked())
{
sql += "WHERE ( ((qoh - allocated + ordered) <= reorderlevel) ";
if (_ignoreReorderAtZero->isChecked())
sql += " AND (NOT ( ((qoh - allocated + ordered) = 0) AND (reorderlevel = 0)) ) ) ";
else
sql += ") ";
}
else if (_showShortages->isChecked())
sql += "WHERE ((qoh - allocated + ordered) < 0) ";
if (_preferences->boolean("ListNumericItemNumbersFirst"))
sql += "ORDER BY toNumeric(item_number, 999999999999999), item_number, warehous_code DESC;";
else
sql += "ORDER BY vend_number, item_number, warehous_code DESC;";
q.prepare(sql);
q.bindValue(":days", _days->value());
q.bindValue(":date", _date->date());
q.bindValue(":startDate", _startDate->date());
q.bindValue(":endDate", _endDate->date());
q.bindValue(":vend_id", _vend->id());
q.bindValue(":vendtype_id", _vendorTypes->id());
q.bindValue(":vendtype_code", _vendorType->text().upper());
_warehouse->bindValue(q);
q.exec();
XTreeWidgetItem * last = 0;
while (q.next())
{
last = new XTreeWidgetItem( _availability, last,
q.value("itemsite_id").toInt(),
q.value("vend_number"), q.value("item_number"),
q.value("description"), q.value("warehous_code"),
q.value("itemsite_leadtime"), q.value("f_qoh"),
q.value("f_allocated"), q.value("f_unallocated"),
q.value("f_ordered"), q.value("f_reorderlevel"),
q.value("f_outlevel") );
last->setText(11, q.value("f_available").toString());
if (_byDates->isChecked())
last->setTextColor(5, "grey");
if (q.value("stockout").toBool())
last->setTextColor(11, "red");
else if (q.value("reorder").toBool())
last->setTextColor(11, "orange");
}
}
void dspWoScheduleByParameterList::sFillList()
{
int woid = _wo->id();
_wo->clear();
QString sql( "SELECT wo_id, wo_ordtype,"
" CASE WHEN (wo_ordid IS NULL) THEN -1"
" ELSE wo_ordid"
" END AS orderid,"
" formatWONumber(wo_id) as wonumber,"
" wo_status, wo_priority, warehous_code,"
" item_number, (item_descrip1 || ' ' || item_descrip2) AS description,"
" uom_name,"
" formatQty(wo_qtyord) as ordered,"
" formatQty(wo_qtyrcv) as received,"
" formatDate(wo_startdate) as startdate,"
" formatDate(wo_duedate) as duedate,"
" ((wo_startdate<=CURRENT_DATE)"
" AND (wo_status IN ('O','E','S','R'))) AS latestart,"
" (wo_duedate<=CURRENT_DATE) AS latedue "
"FROM wo, itemsite, warehous, item, uom "
"WHERE ( (wo_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (wo_startdate BETWEEN <? value(\"startDate\") ?>"
" AND <? value(\"endDate\") ?>)"
"<? if exists(\"warehous_id\") ?>"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
"<? endif ?>"
"<? if exists(\"showOnlyRI\") ?>"
" AND (wo_status IN ('R','I'))"
"<? else ?>"
" AND (wo_status<>'C')"
"<? endif ?>"
"<? if exists(\"showOnlyTopLevel\") ?>"
" AND (wo_ordtype<>'W')"
"<? endif ?>"
"<? if exists(\"classcode_id\") ?>"
" AND (item_classcode_id=<? value(\"classcode_id\") ?>)"
"<? elseif exists(\"itemgrp_id\") ?>"
" AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem WHERE (itemgrpitem_itemgrp_id=<? value(\"itemgrp_id\") ?>)))"
"<? elseif exists(\"plancode_id\") ?>"
" AND (itemsite_plancode_id=<? value(\"plancode_id\") ?>)"
"<? elseif exists(\"wrkcnt_id\") ?>"
" AND (wo_id IN (SELECT wooper_wo_id FROM wooper WHERE (wooper_wrkcnt_id=<? value(\"wrkcnt_id\") ?>)))"
"<? elseif exists(\"classcode_pattern\") ?>"
" AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ <? value(\"classcode_pattern\") ?>)))"
"<? elseif exists(\"itemgrp_pattern\") ?>"
" AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem, itemgrp WHERE ( (itemgrpitem_itemgrp_id=itemgrp_id) AND (itemgrp_name ~ <? value(\"itemgrp_pattern\") ?>) ) ))"
"<? elseif exists(\"plancode_pattern\") ?>"
" AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ <? value(\"plancode_pattern\") ?>)))"
"<? elseif exists(\"wrkcnt_pattern\") ?>"
" AND (wo_id IN (SELECT wooper_wo_id FROM wooper, wrkcnt WHERE ((wooper_wrkcnt_id=wrkcnt_id) AND (wrkcnt_code ~ <? value(\"wrkcnt_pattern\") ?>))))"
"<? endif ?>"
") "
"ORDER BY "
"<? if exists(\"sortByStartDate\") ?>"
" wo_startdate,"
"<? elseif exists(\"sortByDueDate\") ?>"
" wo_duedate,"
"<? elseif exists(\"sortByItemNumber\") ?>"
" item_number,"
"<? endif ?>"
" wo_number, wo_subnumber" );
MetaSQLQuery mql(sql);
ParameterList params;
if (! setParams(params))
return;
q = mql.toQuery(params);
while (q.next())
{
XTreeWidgetItem *last = new XTreeWidgetItem( _wo, q.value("wo_id").toInt(), q.value("orderid").toInt(),
q.value("wo_ordtype"), q.value("wonumber"),
q.value("wo_status"), q.value("wo_priority"),
q.value("warehous_code"), q.value("item_number"),
q.value("description"), q.value("uom_name"),
q.value("ordered"), q.value("received"),
q.value("startdate") );
last->setText(11, q.value("duedate").toString());
if (q.value("latestart").toBool())
last->setTextColor(10, "red");
if (q.value("latedue").toBool())
{
last->setTextColor(11, "red");
last->setText(12, tr("Overdue"));
last->setTextColor(12, "red");
}
else
last->setText(12, tr("On Time"));
if(last->id() == woid)
_wo->setCurrentItem(last);
}
sHandleButtons();
}
void dspWoScheduleByParameterList::sFillList()
{
int woid = _wo->id();
_wo->clear();
QString sql( "SELECT wo_id, wo_ordtype,"
" CASE WHEN (wo_ordid IS NULL) THEN -1"
" ELSE wo_ordid"
" END AS orderid,"
" formatWONumber(wo_id) as wonumber,"
" wo_status, wo_priority, warehous_code,"
" item_number, (item_descrip1 || ' ' || item_descrip2) AS description,"
" uom_name,"
" formatQty(wo_qtyord) as ordered,"
" formatQty(wo_qtyrcv) as received,"
" formatDate(wo_startdate) as startdate,"
" formatDate(wo_duedate) as duedate,"
" ((wo_startdate <= CURRENT_DATE) AND (wo_status IN ('O','E','S','R'))) AS latestart,"
" (wo_duedate<=CURRENT_DATE) AS latedue "
"FROM wo, itemsite, warehous, item, uom "
"WHERE ( (wo_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (wo_startdate BETWEEN :startDate AND :endDate)" );
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_parameter->isSelected())
{
if (_parameter->type() == ClassCode)
sql += " AND (item_classcode_id=:classcode_id)";
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem WHERE (itemgrpitem_itemgrp_id=:itemgrp_id)))";
else if (_parameter->type() == PlannerCode)
sql += " AND (itemsite_plancode_id=:plancode_id)";
else if (_parameter->type() == WorkCenter)
sql += " AND (wo_id IN (SELECT wooper_wo_id FROM wooper WHERE (wooper_wrkcnt_id=:wrkcnt_id)))";
}
else if (_parameter->isPattern())
{
if (_parameter->type() == ClassCode)
sql += " AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ :classcode_pattern)))";
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem, itemgrp WHERE ( (itemgrpitem_itemgrp_id=itemgrp_id) AND (itemgrp_name ~ :itemgrp_pattern) ) ))";
else if (_parameter->type() == PlannerCode)
sql += " AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ :plancode_pattern)))";
else if (_parameter->type() == WorkCenter)
sql += " AND (wo_id IN (SELECT wooper_wo_id FROM wooper, wrkcnt WHERE ((wooper_wrkcnt_id=wrkcnt_id) AND (wrkcnt_code ~ :wrkcnt_pattern))))";
}
if (_showOnlyRI->isChecked())
sql += " AND (wo_status IN ('R','I'))";
else
sql += " AND (wo_status<>'C')";
if (_showOnlyTopLevel->isChecked())
sql += " AND (wo_ordtype<>'W')";
sql += ") ";
if(_sortByStartDate->isChecked())
sql += " ORDER BY wo_startdate, wo_number, wo_subnumber";
else if(_sortByDueDate->isChecked())
sql += " ORDER BY wo_duedate, wo_number, wo_subnumber";
else
sql += " ORDER BY item_number, wo_number, wo_subnumber";
q.prepare(sql);
_warehouse->bindValue(q);
_parameter->bindValue(q);
_dates->bindValue(q);
q.exec();
while (q.next())
{
XTreeWidgetItem *last = new XTreeWidgetItem( _wo, q.value("wo_id").toInt(), q.value("orderid").toInt(),
q.value("wo_ordtype"), q.value("wonumber"),
q.value("wo_status"), q.value("wo_priority"),
q.value("warehous_code"), q.value("item_number"),
q.value("description"), q.value("uom_name"),
q.value("ordered"), q.value("received"),
q.value("startdate") );
last->setText(11, q.value("duedate").toString());
if (q.value("latestart").toBool())
last->setTextColor(10, "red");
if (q.value("latedue").toBool())
{
last->setTextColor(11, "red");
last->setText(12, tr("Overdue"));
last->setTextColor(12, "red");
}
else
last->setText(12, tr("On Time"));
if(last->id() == woid)
_wo->setCurrentItem(last);
}
sHandleButtons();
}
void distributeInventory::sFillList()
{
q.prepare( "SELECT itemsite_id, "
" COALESCE(itemsite_location_id,-1) AS itemsite_location_id,"
" itemlocdist_lotserial,"
" (itemsite_controlmethod IN ('L', 'S')) AS lscontrol,"
" parent.itemlocdist_qty AS qtytodistribute,"
" ( ( SELECT COALESCE(SUM(child.itemlocdist_qty), 0)"
" FROM itemlocdist AS child"
" WHERE (child.itemlocdist_itemlocdist_id=parent.itemlocdist_id) ) ) AS qtytagged,"
" (parent.itemlocdist_qty - ( SELECT COALESCE(SUM(child.itemlocdist_qty), 0)"
" FROM itemlocdist AS child"
" WHERE (child.itemlocdist_itemlocdist_id=parent.itemlocdist_id) ) ) AS qtybalance "
"FROM itemsite, itemlocdist AS parent "
"WHERE ( (itemlocdist_itemsite_id=itemsite_id)"
" AND (itemlocdist_id=:itemlocdist_id) );" );
q.bindValue(":itemlocdist_id", _itemlocdistid);
q.exec();
if (q.first())
{
_item->setItemsiteid(q.value("itemsite_id").toInt());
_lotSerial->setText(q.value("itemlocdist_lotserial").toString());
_qtyToDistribute->setText(formatNumber(q.value("qtytodistribute").toDouble(),6));
_qtyTagged->setText(formatNumber(q.value("qtytagged").toDouble(),6));
_qtyRemaining->setText(formatNumber(q.value("qtybalance").toDouble(),6));
if ( (q.value("itemsite_location_id").toInt() != -1) &&
( (_mode == cNoIncludeLotSerial) || ( (_mode == cIncludeLotSerial) && (!q.value("lscontrol").toBool()) ) ) )
{
_default->setEnabled(TRUE);
_defaultAndPost->setEnabled(TRUE);
}
else
{
_default->setEnabled(FALSE);
_defaultAndPost->setEnabled(FALSE);
}
QString sql( "SELECT id, type,"
" locationname,"
" CASE WHEN defaultlocation THEN <? value(\"yes\") ?>"
" ELSE <? value(\"no\") ?>"
" END AS defaultlocation,"
" CASE WHEN (location_netable) THEN <? value(\"yes\") ?>"
" ELSE <? value(\"no\") ?>"
" END AS netable,"
" lotserial, f_expiration, expired,"
" qty,"
" qtytagged,"
" (qty + qtytagged) AS balance "
"FROM ("
"<? if exists(\"cNoIncludeLotSerial\") ?>"
"SELECT location_id AS id, <? value(\"locationType\") ?> AS type,"
" formatLocationName(location_id) AS locationname,"
" (location_id=itemsite_location_id) AS defaultlocation,"
" location_netable,"
" TEXT('') AS lotserial,"
" TEXT(<? value(\"na\") ?>) AS f_expiration, FALSE AS expired,"
" ( SELECT COALESCE(SUM(itemloc_qty), 0)"
" FROM itemloc "
" WHERE ( (itemloc_location_id=location_id)"
" AND (itemloc_itemsite_id=itemsite_id) ) ) AS qty,"
" itemlocdistQty(location_id, itemlocdist_id) AS qtytagged "
"FROM itemlocdist, location, itemsite "
"WHERE ( (itemlocdist_itemsite_id=itemsite_id)"
" AND (itemsite_loccntrl)"
" AND (itemsite_warehous_id=location_warehous_id)"
" AND (validLocation(location_id, itemsite_id))"
" AND (itemlocdist_id=<? value(\"itemlocdist_id\") ?>) ) "
"<? elseif exists(\"cIncludeLotSerial\") ?>"
"SELECT itemloc_id AS id, <? value(\"itemlocType\") ?> AS type,"
" COALESCE(formatLocationName(location_id),"
" <? value(\"undefined\") ?>) AS locationname,"
" (location_id IS NOT NULL"
" AND location_id=itemsite_location_id) AS defaultlocation,"
" COALESCE(location_netable, false) AS location_netable,"
" itemloc_lotserial AS lotserial,"
" CASE WHEN (itemsite_perishable) THEN formatDate(itemloc_expiration)"
" ELSE <? value(\"na\") ?>"
" END AS f_expiration,"
" CASE WHEN (itemsite_perishable) THEN (itemloc_expiration < CURRENT_DATE)"
" ELSE FALSE"
" END AS expired,"
" itemloc_qty AS qty,"
" ( SELECT COALESCE(SUM(target.itemlocdist_qty), 0)"
" FROM itemlocdist AS target"
" WHERE ( (target.itemlocdist_source_type='I')"
" AND (target.itemlocdist_source_id=itemloc_id)"
" AND (target.itemlocdist_itemlocdist_id=source.itemlocdist_id)) ) AS qtytagged "
"FROM itemlocdist AS source, itemsite, itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id) "
"WHERE ( (source.itemlocdist_itemsite_id=itemsite_id)"
" AND (itemloc_itemsite_id=itemsite_id)"
" AND (source.itemlocdist_id=<? value(\"itemlocdist_id\") ?>) ) "
" UNION "
"SELECT location_id AS id, <? value(\"locationType\") ?> AS type,"
" formatLocationName(location_id) AS locationname,"
" (location_id=itemsite_location_id) AS defaultlocation,"
" location_netable,"
" TEXT('') AS lotserial,"
" TEXT(<? value(\"na\") ?>) AS f_expiration, FALSE AS expired,"
" ( SELECT COALESCE(SUM(itemloc_qty), 0)"
" FROM itemloc "
" WHERE ( (itemloc_location_id=location_id)"
" AND (itemloc_itemsite_id=itemsite_id) ) ) AS qty,"
" itemlocdistQty(location_id, itemlocdist_id) AS qtytagged "
"FROM itemlocdist, location, itemsite "
"WHERE ( (itemlocdist_itemsite_id=itemsite_id)"
" AND (itemsite_loccntrl)"
" AND (itemsite_warehous_id=location_warehous_id)"
" AND (validLocation(location_id, itemsite_id))"
" AND (location_id NOT IN (SELECT DISTINCT itemloc_location_id FROM itemloc WHERE (itemloc_itemsite_id=itemsite_id)))"
" AND (itemlocdist_id=<? value(\"itemlocdist_id\") ?>) ) "
"<? endif ?>"
") AS data "
"<? if exists(\"showOnlyTagged\") ?>"
"WHERE (qtytagged != 0) "
"<? endif ?>"
"ORDER BY locationname;");
ParameterList params;
if (_mode == cNoIncludeLotSerial)
params.append("cNoIncludeLotSerial");
else if (_mode == cIncludeLotSerial)
params.append("cIncludeLotSerial");
if (_taggedOnly->isChecked())
params.append("showOnlyTagged");
params.append("locationType", cLocation);
params.append("itemlocType", cItemloc);
params.append("yes", tr("Yes"));
params.append("no", tr("No"));
params.append("na", tr("N/A"));
params.append("undefined", tr("Undefined"));
params.append("itemlocdist_id", _itemlocdistid);
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
_itemloc->clear();
XTreeWidgetItem *last = 0;
while (q.next())
{
last = new XTreeWidgetItem(_itemloc, last,
q.value("id").toInt(), q.value("type").toInt(),
q.value("locationname"), q.value("defaultlocation"),
q.value("netable"), q.value("lotserial"),
q.value("f_expiration"),
formatNumber(q.value("qty").toDouble(),6),
formatNumber(q.value("qtytagged").toDouble(),6),
formatNumber(q.value("balance").toDouble(),6) );
if (q.value("expired").toBool())
last->setTextColor("red");
}
}
}
void dspQOHByParameterList::sFillList()
{
int itemsiteid = _qoh->id();
_qoh->clear();
QString sql( "SELECT itemsite_id, detail,"
" warehous_code, classcode_code, item_number, uom_name,"
" (item_descrip1 || ' ' || item_descrip2) AS itemdescrip,"
" defaultlocation,"
" formatQty(reorderlevel) AS f_reorderlevel,"
" formatQty(qoh) AS f_qoh,"
" formatQty(nnqoh) AS f_nnqoh,"
" formatCost(cost) AS f_cost,"
" formatMoney(noNeg(cost * qoh)) AS f_value,"
" formatMoney(noNeg(cost * nnqoh)) AS f_nnvalue,"
" cost, reorderlevel, qoh, nnqoh "
"FROM ( SELECT itemsite_id,"
" ( (itemsite_loccntrl) OR (itemsite_controlmethod IN ('L', 'S')) ) AS detail,"
" warehous_code, classcode_code, item_number, uom_name, item_descrip1, item_descrip2,"
" CASE WHEN (NOT useDefaultLocation(itemsite_id)) THEN :none"
" ELSE defaultLocationName(itemsite_id)"
" END AS defaultlocation,"
" CASE WHEN (itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel,"
" itemsite_qtyonhand AS qoh,"
" itemsite_nnqoh AS nnqoh," );
if (_useStandardCosts->isChecked())
sql += " stdcost(item_id) AS cost ";
else if (_useActualCosts->isChecked())
sql += " actcost(item_id) AS cost ";
sql += "FROM itemsite, item, classcode, warehous, uom "
"WHERE ( (itemsite_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (item_classcode_id=classcode_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (itemsite_active)";
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_parameter->isSelected())
{
if (_parameter->type() == ClassCode)
sql += " AND (classcode_id=:classcode_id)";
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem WHERE (itemgrpitem_itemgrp_id=:itemgrp_id)))";
}
else if (_parameter->isPattern())
{
if (_parameter->type() == ClassCode)
sql += " AND (classcode_id IN (SELECT classcode_id FROM classcode WHERE classcode_code ~ :classcode_pattern))";
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem, itemgrp WHERE ( (itemgrpitem_itemgrp_id=itemgrp_id) AND (itemgrp_name ~ :itemgrp_pattern) ) ))";
}
else if(_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT DISTINCT itemgrpitem_item_id FROM itemgrpitem))";
if (_showPositive->isChecked())
sql += " AND (itemsite_qtyonhand>0)";
else if (_showNegative->isChecked())
sql += " AND (itemsite_qtyonhand<0)";
sql += ") ) AS data "
"ORDER BY warehous_code";
if (_byItemNumber->isChecked())
sql += ", item_number;";
else
sql += ", noNeg(cost * qoh) DESC;";
q.prepare(sql);
q.bindValue(":none", tr("None"));
q.bindValue(":na", tr("N/A"));
_warehouse->bindValue(q);
_parameter->bindValue(q);
q.exec();
if (q.first())
{
XTreeWidgetItem *selected = 0;
XTreeWidgetItem *last = 0;
double netable = 0.0;
double netableValue = 0.0;
double nonNetable = 0.0;
double nonNetableValue = 0.0;
do
{
last = new XTreeWidgetItem( _qoh, last, q.value("itemsite_id").toInt(), q.value("detail").toInt(),
q.value("warehous_code"), q.value("classcode_code"), q.value("item_number"),
q.value("itemdescrip"), q.value("uom_name"),
q.value("defaultlocation"), q.value("f_reorderlevel"),
q.value("f_qoh"), q.value("f_nnqoh") );
last->setText(9, q.value("f_cost").toString());
last->setText(10, q.value("f_value").toString());
last->setText(11, q.value("f_nnvalue").toString());
if (q.value("qoh").toDouble() < 0)
last->setTextColor(7, "red");
else if (q.value("reorderlevel").toDouble() > q.value("qoh").toDouble())
last->setTextColor(7, "orange");
if (q.value("itemsite_id") == itemsiteid)
selected = last;
if (q.value("qoh").toDouble() > 0.0)
{
netable += q.value("qoh").toDouble();
netableValue += (q.value("cost").toDouble() * q.value("qoh").toDouble());
}
if (q.value("nnqoh").toDouble() > 0.0)
{
nonNetable += q.value("nnqoh").toDouble();
nonNetableValue += (q.value("cost").toDouble() * q.value("nnqoh").toDouble());
}
}
while (q.next());
XTreeWidgetItem *totals = new XTreeWidgetItem(_qoh, last, -1, 0, tr("Totals"));
totals->setText(7, formatQty(netable));
totals->setText(8, formatQty(nonNetable));
totals->setText(10, formatMoney(netableValue));
totals->setText(11, formatMoney(nonNetableValue));
if (selected != NULL)
{
_qoh->setCurrentItem(selected);
_qoh->scrollTo(_qoh->currentIndex());
}
}
}
void dspDetailedInventoryHistoryByLotSerial::sFillList()
{
QString trace;
_invhist->clear();
if (_dateGroup->isChecked())
{
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 ( ((_selected->isChecked() && _lotSerial->number().trimmed().length() == 0)
|| (_pattern->isChecked() && _lotSerialPattern->text().trimmed().length() == 0))
&& (!_item->isValid()) )
{
QMessageBox::warning( this, tr("Enter Lot/Serial #"),
tr("<p>You must enter a Lot/Serial or Item criteria to view Inventory "
"Detail by Lot/Serial #.</p>") );
_lotSerial->setFocus();
return;
}
if (_traceGroup->isChecked())
{
if (_forward->isChecked())
trace="F";
else
trace="B";
}
else
trace="N";
q.prepare( "SELECT * FROM lshist(:itemid,:warehouseid,:lotserial,:pattern,:transType,:startDate,:endDate,:trace,1); ");
_dates->bindValue(q);
if (_item->isValid())
q.bindValue(":itemid", _item->id());
if (_warehouse->isSelected())
q.bindValue(":warehouseid", _warehouse->id());
if (_selected->isChecked())
{
if (_lotSerial->number().trimmed().length() > 0)
q.bindValue(":lotserial", _lotSerial->number().trimmed());
q.bindValue(":pattern", FALSE);
}
else
{
q.bindValue(":lotserial", _lotSerialPattern->text().trimmed());
q.bindValue(":pattern", TRUE);
}
q.bindValue(":transType", _transType->id());
q.bindValue(":trace", trace);
q.exec();
QStack<XTreeWidgetItem*> parent;
XTreeWidgetItem *last = 0;
int level = 1;
while (q.next())
{
while(q.value("lshist_level").toInt() < level)
{
level--;
last = parent.pop();
}
while(q.value("lshist_level").toInt() > level)
{
level++;
parent.push(last);
last = 0;
}
if(!parent.isEmpty() && parent.top())
last = new XTreeWidgetItem(parent.top(), last, q.value("lshist_id").toInt(),
q.value("lshist_warehous_code"),
q.value("lshist_transdate"), q.value("lshist_transtype"),
q.value("lshist_ordernumber"), q.value("lshist_item_number"),
q.value("lshist_locationname"), q.value("lshist_lotserial"),
q.value("lshist_invuom"), q.value("lshist_transqty") );
else
last = new XTreeWidgetItem(_invhist, last, q.value("lshist_id").toInt(),
q.value("lshist_warehous_code"),
q.value("lshist_transdate"), q.value("lshist_transtype"),
q.value("lshist_ordernumber"), q.value("lshist_item_number"),
q.value("lshist_locationname"), q.value("lshist_lotserial"),
q.value("lshist_invuom"), q.value("lshist_transqty") );
if (q.value("lshist_posted").toBool())
{
last->setText(9, q.value("lshist_qty_before").toString());
last->setText(10, q.value("lshist_qty_after").toString());
}
else
last->setTextColor("orange");
}
_invhist->expandAll();
}
void dspCountSlipsByWarehouse::sFillList()
{
QString sql("SELECT cntslip_id, ");
if (_numericSlips->isChecked())
sql += "toNumeric(cntslip_number, 0) AS slipnumber,"
"cntslip_number AS slipnumber_qtdisplayrole," ;
else
sql += "cntslip_number AS slipnumber, ";
sql += " invcnt_tagnumber, warehous_code,"
" item_number, (item_descrip1 || ' ' || item_descrip2) AS descrip,"
" cntslip_entered, getUsername(cntslip_user_id) AS user,"
" cntslip_qty, 'qty' AS cntslip_qty_xtnumericrole,"
" cntslip_posted "
"FROM cntslip, invcnt, itemsite, item, warehous "
"WHERE ((cntslip_cnttag_id=invcnt_id)"
" AND (invcnt_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (cntslip_entered BETWEEN :startDate AND :endDate)";
if (!_showUnposted->isChecked())
sql += " AND (cntslip_posted)";
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
sql += ") "
"ORDER BY cntslip_number";
if (DEBUG)
qDebug("dspCountSlipsByWarehouse::sFillList() about to populate _cntslip");
q.prepare(sql);
_dates->bindValue(q);
_warehouse->bindValue(q);
q.exec();
_cntslip->populate(q);
if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
if (_numericSlips->isChecked() && _cntslip->topLevelItemCount() > 1)
{
if (DEBUG)
qDebug("dspCountSlipsByWarehouse::sFillList() looking for slip # gaps "
"in %d items", _cntslip->topLevelItemCount());
XTreeWidgetItem *last =_cntslip->topLevelItem(0);
int slipNumber = last->rawValue("slipnumber").toInt();
for (int i = 1; i < _cntslip->topLevelItemCount(); i++)
{
XTreeWidgetItem *curr = _cntslip->topLevelItem(i);
if (DEBUG)
qDebug("row %d has slipNumber %d and current %d",
i, slipNumber, curr->rawValue("slipnumber").toInt());
if (slipNumber == (curr->rawValue("slipnumber").toInt() - 1) || slipNumber == -1)
slipNumber = curr->rawValue("slipnumber").toInt();
else if (slipNumber >= 0)
{
if (slipNumber == curr->rawValue("slipnumber").toInt() - 2)
curr = new XTreeWidgetItem( _cntslip, last, -1,
QVariant("----"), "----", "----", "----",
tr("Missing Slip #%1").arg(slipNumber + 1),
"----", "----", "----" );
else
curr = new XTreeWidgetItem( _cntslip, last, -1,
QVariant("----"), "----", "----", "----",
tr("Missing Slips #%1 to #%2").arg(slipNumber + 1).arg(curr->rawValue("slipnumber").toInt() - 1),
"----", "----", "----" );
curr->setTextColor(namedColor("error"));
slipNumber = -1;
//i++; // 'cause we just added an item!
}
last = curr;
}
}
}
void dspWoEffortByWorkOrder::sFillList()
{
_wotc->clear();
if (_wo->isValid())
{
q.prepare( "SELECT wotc_id, usr_username,"
" wooper_seqnumber || ' - ' || wooper_descrip1 || ' - ' ||"
" wooper_descrip2 AS wooper, "
" formatDateTime(wotc_timein) AS timein,"
" formatDateTime(wotc_timeout) AS timeout,"
" NULL AS setup_time, NULL AS run_time,"
" formatInterval(wotcTime(wotc_id)) AS wo_effort "
"FROM usr, wotc LEFT OUTER JOIN "
" wooper ON (wotc_wooper_id=wooper_id) "
"WHERE ((wotc_wo_id=:wo_id)"
" AND (wotc_usr_id=usr_id) "
" AND (wotc_id NOT IN (SELECT DISTINCT wooperpost_wotc_id "
" FROM wooperpost"
" WHERE (wooperpost_wo_id=:wo_id)"
" AND (wooperpost_wotc_id IS NOT NULL)))) "
"UNION "
"SELECT wotc_id, usr_username,"
" CAST(wooperpost_seqnumber AS TEXT) AS wooper, "
" formatDateTime(wotc_timein) AS timein,"
" formatDateTime(wotc_timeout) AS timeout,"
" formatInterval(wooperpost_sutime) AS setup_time,"
" formatInterval(wooperpost_rntime) AS run_time,"
" formatInterval(wotcTime(wotc_id)) AS wo_effort "
"FROM usr, wotc, wooperpost "
"WHERE ((wotc_wo_id=:wo_id)"
" AND (wotc_usr_id=usr_id) "
" AND (wooperpost_wotc_id=wotc_id)) "
"ORDER BY timein, timeout;" );
q.bindValue(":wo_id", _wo->id());
q.exec();
_wotc->populate(q);
if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
// we have to piece together the TOTAL line because one select won't work
XTreeWidgetItem* lastLine = new XTreeWidgetItem(_wotc,
_wotc->topLevelItem(_wotc->topLevelItemCount() - 1),
_wo->id(), "", tr("Total"));
q.prepare("SELECT formatDateTime(MIN(wotc_timein)) AS timein,"
" formatDateTime(MAX(wotc_timeout)) AS timeout,"
" formatInterval(woTimeByWo(wotc_wo_id)) AS wo_effort "
"FROM wotc "
"WHERE (wotc_wo_id=:wo_id) "
"GROUP BY wotc_wo_id;");
q.bindValue(":wo_id", _wo->id());
q.exec();
if (q.first())
{
lastLine->setText(2, q.value("timein"));
lastLine->setText(3, q.value("timeout"));
lastLine->setText(6, q.value("wo_effort"));
}
else if (q.lastError().type() != QSqlError::NoError)
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
q.prepare("SELECT formatInterval(SUM(wooperpost_sutime)) AS setup_time,"
" formatInterval(SUM(wooperpost_rntime)) AS run_time,"
" SUM(wooperpost_sutime) + SUM(wooperpost_rntime) -"
" intervalToMinutes(woTimeByWo(wooperpost_wo_id)) AS variance "
"FROM wooperpost "
"WHERE (wooperpost_wo_id=:wo_id) "
"GROUP BY wooperpost_wo_id;");
q.bindValue(":wo_id", _wo->id());
q.exec();
if (q.first())
{
lastLine->setText(4, q.value("setup_time"));
lastLine->setText(5, q.value("run_time"));
if (fabs(q.value("variance").toDouble()) > 1.5) // rounding errors that appear <= 1 min
lastLine->setTextColor("red");
}
else if (q.lastError().type() != QSqlError::NoError)
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
}
}
void dspPoItemsByBufferStatus::sFillList()
{
_poitem->clear();
QString sql( "SELECT pohead_id, poitem_id, pohead_number,"
" CASE WHEN (itemsite_id IS NULL) THEN ( SELECT warehous_code"
" FROM warehous"
" WHERE (pohead_warehous_id=warehous_id) )"
" ELSE ( SELECT warehous_code"
" FROM warehous"
" WHERE (itemsite_warehous_id=warehous_id) )"
" END AS warehousecode,"
" poitem_status,"
" CASE WHEN(poitem_status='C') THEN <? value(\"closed\") ?>"
" WHEN(poitem_status='U') THEN <? value(\"unposted\") ?>"
" WHEN(poitem_status='O' AND ((poitem_qty_received-poitem_qty_returned) > 0) AND (poitem_qty_ordered>(poitem_qty_received-poitem_qty_returned))) THEN <? value(\"partial\") ?>"
" WHEN(poitem_status='O' AND ((poitem_qty_received-poitem_qty_returned) > 0) AND (poitem_qty_ordered=(poitem_qty_received-poitem_qty_returned))) THEN <? value(\"received\") ?>"
" WHEN(poitem_status='O') THEN <? value(\"open\") ?>"
" ELSE poitem_status"
" END AS poitemstatus,"
" vend_name,"
" CASE WHEN (bufrsts_type='T') THEN <? value(\"time\") ?>"
" ELSE <? value(\"stock\") ?>"
" END AS bufrststype,"
" bufrsts_status,"
" item_number,"
" item_descrip1,"
" uom_name,"
" formatQty(poitem_qty_ordered) AS f_qtyordered,"
" formatQty(poitem_qty_received) AS f_qtyreceived,"
" formatQty(poitem_qty_returned) AS f_qtyreturned,"
" formatDate(poitem_duedate) AS f_duedate,"
" (bufrsts_status >66) AS emergency "
" FROM pohead, poitem, vend,itemsite, item, uom, bufrsts "
" WHERE ((poitem_pohead_id=pohead_id)"
" AND (pohead_vend_id=vend_id)"
" AND (itemsite_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (poitem_itemsite_id=itemsite_id)"
" AND (pohead_vend_id=vend_id)"
" AND (poitem_status='O')"
" AND (bufrsts_target_type='P')"
" AND (bufrsts_target_id=poitem_id)"
" AND (bufrsts_date=current_date)"
"<? if exists(\"warehous_id\") ?>"
" AND (((itemsite_id IS NULL) AND "
" (pohead_warehous_id=<? value(\"warehous_id\") ?>)) OR"
" ((itemsite_id IS NOT NULL) AND"
" (itemsite_warehous_id=<? value(\"warehous_id\") ?>)))"
"<? endif ?>"
"<? if exists(\"username\") ?>"
" AND (pohead_agent_username=<? value(\"username\") ?>)"
"<? endif ?>"
") "
"ORDER BY bufrsts_status desc, poitem_duedate;"
);
ParameterList params;
params.append("stock", tr("Stock"));
params.append("time", tr("Time"));
params.append("closed", tr("Closed"));
params.append("unposted", tr("Unposted"));
params.append("partial", tr("Partial"));
params.append("received", tr("Received"));
params.append("open", tr("Open"));
if (_warehouse->isSelected())
params.append("warehous_id", _warehouse->id());
if (_selectedPurchasingAgent->isChecked())
params.append("username", _agent->currentText());
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
if (q.first())
{
XTreeWidgetItem * last = 0;
do
{
last = new XTreeWidgetItem( _poitem, last,
q.value("pohead_id").toInt(), q.value("poitem_id").toInt(),
q.value("pohead_number").toString(), q.value("warehousecode"),
q.value("poitemstatus"), q.value("vend_name"),
q.value("bufrsts_status"), q.value("bufrststype"), q.value("item_number"),
q.value("item_descrip1"), q.value("uom_name"),
q.value("f_qtyordered"), q.value("f_qtyreceived") );
last->setText(11, q.value("f_qtyreturned").toString());
last->setText(12, q.value("f_duedate").toString());
last->setText(POITEM_STATUS_COL, q.value("poitem_status").toString());
if (q.value("emergency").toBool())
last->setTextColor(4, "red");
}
while (q.next());
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void dspWoBufferStatusByParameterList::sFillList()
{
_wo->clear();
QString sql( "SELECT wo_id, wo_ordtype,"
" CASE WHEN (wo_ordid IS NULL) THEN -1"
" ELSE wo_ordid"
" END AS orderid,"
" formatWONumber(wo_id) as wonumber,"
" wo_status, wo_priority, warehous_code,"
" item_number, (item_descrip1 || ' ' || item_descrip2) AS description,"
" uom_name,"
" formatQty(wo_qtyord) as ordered,"
" formatQty(wo_qtyrcv) as received,"
" CASE WHEN (bufrsts_type='T') THEN :time"
" ELSE :stock"
" END AS bufrststype,"
" bufrsts_status,"
" (bufrsts_status>=66) AS emergency"
" FROM wo, itemsite, warehous, item, uom, bufrsts "
" WHERE ( (wo_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (bufrsts_target_type='W')"
" AND (bufrsts_target_id=wo_id)"
" AND (bufrsts_date=current_date)");
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_parameter->isSelected())
{
if (_parameter->type() == ClassCode)
sql += " AND (item_classcode_id=:classcode_id)";
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem WHERE (itemgrpitem_itemgrp_id=:itemgrp_id)))";
else if (_parameter->type() == PlannerCode)
sql += " AND (itemsite_plancode_id=:plancode_id)";
}
else if (_parameter->isPattern())
{
if (_parameter->type() == ClassCode)
sql += " AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ :classcode_pattern)))";
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem, itemgrp WHERE ( (itemgrpitem_itemgrp_id=itemgrp_id) AND (itemgrp_name ~ :itemgrp_pattern) ) ))";
else if (_parameter->type() == PlannerCode)
sql += " AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ :plancode_pattern)))";
}
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT DISTINCT itemgrpitem_item_id FROM itemgrpitem))";
if (_showOnlyRI->isChecked())
sql += " AND (wo_status IN ('R','I'))";
else
sql += " AND (wo_status<>'C')";
if (_showOnlyTopLevel->isChecked())
sql += " AND (wo_ordtype<>'W')";
sql += ") "
" ORDER BY bufrsts_status DESC, wo_number, wo_subnumber";
q.prepare(sql);
_warehouse->bindValue(q);
_parameter->bindValue(q);
q.bindValue(":stock", tr("Stock"));
q.bindValue(":time", tr("Time"));
q.exec();
XTreeWidgetItem * last = 0;
while (q.next())
{
last = new XTreeWidgetItem( _wo, last, q.value("wo_id").toInt(), q.value("orderid").toInt(),
q.value("wo_ordtype"), q.value("wonumber"),
q.value("wo_status"), q.value("wo_priority"),
q.value("warehous_code"), q.value("item_number"),
q.value("description"), q.value("uom_name"),
q.value("ordered"), q.value("received"),
q.value("bufrststype") );
last->setText(11, q.value("bufrsts_status").toString());
if (q.value("emergency").toBool())
last->setTextColor(11, "red");
}
}
void dspDetailedInventoryHistoryByLocation::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;
}
q.prepare( "SELECT invhist_id,"
" formatDateTime(invhist_transdate) AS transdate,"
" invhist_transtype, (invhist_ordtype || '-' || invhist_ordnumber) AS ordernumber,"
" invhist_invuom,"
" item_number, invdetail_lotserial,"
" 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_location_id=:location_id)"
" 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(":location_id", _location->id());
q.bindValue(":transType", _transType->id());
q.exec();
XTreeWidgetItem *last = 0;
while (q.next())
{
last = new XTreeWidgetItem( _invhist, last, q.value("invhist_id").toInt(),
q.value("transdate"),
q.value("invhist_transtype"),
q.value("ordernumber"),
q.value("item_number"),
q.value("invdetail_lotserial"),
q.value("invhist_invuom"),
q.value("transqty") );
if (q.value("invhist_posted").toBool())
{
last->setText(7, q.value("qohbefore").toString());
last->setText(8, q.value("qohafter").toString());
}
else
last->setTextColor("orange");
}
}
void dspInventoryLocator::sFillList()
{
if (_item->isValid())
{
QString sql( "SELECT itemloc_id, 1 AS type, warehous_code,"
" CASE WHEN (location_id IS NULL) THEN :na"
" ELSE (formatLocationName(location_id) || '-' || firstLine(location_descrip))"
" END AS locationname,"
" CASE WHEN (location_id IS NULL) THEN :na"
" WHEN (location_netable) THEN :yes"
" ELSE :no"
" END AS netable,"
" CASE WHEN (itemsite_controlmethod NOT IN ('L', 'S')) THEN :na"
" ELSE itemloc_lotserial"
" END AS lotserial,"
" CASE WHEN (itemsite_perishable) THEN formatDate(itemloc_expiration)"
" ELSE :na"
" END AS f_expiration,"
" CASE WHEN (itemsite_perishable) THEN (itemloc_expiration <= CURRENT_DATE)"
" ELSE FALSE"
" END AS expired,"
" formatQty(itemloc_qty) AS f_qoh "
"FROM itemsite, warehous,"
" itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id) "
"WHERE ( ( (itemsite_loccntrl) OR (itemsite_controlmethod IN ('L', 'S')) )"
" AND (itemloc_itemsite_id=itemsite_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (itemsite_item_id=:item_id)" );
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
sql += ") "
"UNION SELECT itemsite_id, 2 AS type, warehous_code,"
" :na AS locationname,"
" :na AS netable,"
" :na AS lotserial,"
" :na AS f_expiration,"
" FALSE AS expired,"
" formatQty(itemsite_qtyonhand) AS f_qoh "
"FROM itemsite, warehous "
"WHERE ( (NOT itemsite_loccntrl)"
" AND (itemsite_controlmethod NOT IN ('L', 'S'))"
" AND (itemsite_warehous_id=warehous_id)"
" AND (itemsite_item_id=:item_id)";
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
sql += ") "
"ORDER BY warehous_code, locationname, lotserial;";
q.prepare(sql);
q.bindValue(":yes", tr("Yes"));
q.bindValue(":no", tr("No"));
q.bindValue(":na", tr("N/A"));
q.bindValue(":undefined", tr("Undefined"));
q.bindValue(":item_id", _item->id());
_warehouse->bindValue(q);
q.exec();
_itemloc->clear();
XTreeWidgetItem *last = 0;
while (q.next())
{
last = new XTreeWidgetItem( _itemloc, last,
q.value("itemloc_id").toInt(), q.value("type").toInt(),
q.value("warehous_code"), q.value("locationname"),
q.value("netable"), q.value("lotserial"),
q.value("f_expiration"), q.value("f_qoh") );
if (q.value("expired").toBool())
last->setTextColor("red");
}
}
else
_itemloc->clear();
}
void fixSerial::sFillList()
{
_serial->clear();
QApplication::setOverrideCursor( QCursor(Qt::WaitCursor) );
QString sql = "SELECT nspname ||'.' ||relname AS tablename, nspname, relname, attname, "
" TRIM(quote_literal('\"''') FROM"
" SUBSTRING(pg_catalog.pg_get_expr(d.adbin, d.adrelid)"
" FROM '[' || quote_literal('\"''') || "
" '].*[' || quote_literal('\"''') || ' ]')) AS seq"
" FROM pg_catalog.pg_attribute a, pg_catalog.pg_class,"
" pg_catalog.pg_attrdef d, pg_catalog.pg_namespace "
" WHERE a.attnum > 0"
" AND pg_namespace.oid = pg_class.relnamespace"
" AND NOT a.attisdropped"
" AND a.attnotnull"
" AND a.attrelid = pg_class.oid"
" AND d.adrelid = a.attrelid"
" AND d.adnum = a.attnum"
" AND pg_catalog.pg_get_expr(d.adbin, d.adrelid) ~* 'nextval'"
" AND a.atthasdef "
"ORDER BY relname;" ;
XSqlQuery relq;
relq.prepare(sql);
QString maxStr = "SELECT MAX(<? literal(\"attname\" ?>) AS maxval "
"FROM <? literal(\"tablename\") ?>;" ;
XSqlQuery maxq;
QString seqStr = "SELECT last_value AS currval FROM <? literal(\"seq\") ?>;" ;
XSqlQuery seqq;
XTreeWidgetItem *last = 0;
int rows = 0;
int maxval = 0;
int currval = 0;
int errors = 0;
relq.exec();
while (relq.next())
{
ParameterList params;
params.append("attname", relq.value("attname").toString());
params.append("tablename", relq.value("tablename").toString());
params.append("seq", relq.value("seq").toString());
MetaSQLQuery maxMql = MetaSQLQuery(maxStr);
maxq = maxMql.toQuery(params);
if (maxq.first())
maxval = maxq.value("maxval").toInt();
else if (maxq.lastError().type() != QSqlError::NoError)
{
systemError(this, maxq.lastError().databaseText(), __FILE__, __LINE__);
continue;
}
MetaSQLQuery seqMql = MetaSQLQuery(seqStr);
seqq = seqMql.toQuery(params);
if (seqq.first())
currval = seqq.value("currval").toInt();
else if (seqq.lastError().type() != QSqlError::NoError)
{
systemError(this, seqq.lastError().databaseText(), __FILE__, __LINE__);
continue;
}
rows++;
if (maxval > currval)
errors++;
if ((_showProblems->isChecked() && maxval > currval) ||
! _showProblems->isChecked())
{
last = new XTreeWidgetItem(_serial, last, rows, maxval > currval ? 1 : 0,
relq.value("nspname"),
relq.value("relname"),
relq.value("attname"),
relq.value("seq"),
maxval,
currval);
if (maxval > currval)
last->setTextColor("red");
}
}
QApplication::restoreOverrideCursor();
if (relq.lastError().type() != QSqlError::NoError)
{
systemError(this, relq.lastError().databaseText(), __FILE__, __LINE__);
return;
}
if (errors > 0)
_statusLit->setText(QObject::tr("Found %1 tables with mismatched serial values.")
.arg(errors));
else
_statusLit->setText(QObject::tr("No problems found"));
_fixAll->setEnabled(errors > 0);
}
void dspInventoryAvailabilityByCustomerType::sFillList()
{
_avail->clear();
QString sql( "SELECT itemsite_id, coitem_id,"
" cohead_id, cohead_number, (cust_number||'-'||cust_name) AS custname,"
" item_number, item_description, uom_name, item_picklist,"
" qoh, formatQty(qoh) AS f_qoh,sobalance,"
" formatQty(sobalance) AS f_sobalance,"
" formatQty(allocated) AS f_allocated,"
" ordered, formatQty(ordered) AS f_ordered,"
" (qoh + ordered - sobalance) AS woavail,"
"<? if exists(\"useReservationNetting\") ?>"
" formatQty(coitem_qtyreserved) AS f_soavail,"
"<? else ?>"
" formatQty(qoh + ordered - sobalance) AS f_soavail,"
"<? endif ?>"
" (qoh + ordered - allocated) AS totalavail,"
" formatQty(qoh + ordered - allocated) AS f_totalavail,"
" atshipping,formatQty(atshipping) AS f_atshipping,"
" formatDate(coitem_scheddate) AS f_scheddate,"
" (coitem_qtyreserved > 0 AND sobalance > coitem_qtyreserved) AS partialreservation,"
" ((sobalance - coitem_qtyreserved) = 0) AS fullreservation,"
" reorderlevel "
"<? if exists(\"showWoSupply\") ?>, "
" wo_id,"
" wo_status,"
" wo_number,"
" wo_ordered,"
" formatQty(wo_ordered) AS f_wo_ordered,"
" formatDate(wo_startdate) AS f_wo_startdate, "
" formatDate(wo_duedate) AS f_wo_duedate,"
" COALESCE(wo_latestart,false) AS wo_latestart,"
" COALESCE(wo_latedue,false) AS wo_latedue "
"<? endif ?>"
"FROM ( SELECT itemsite_id, coitem_id,"
" cohead_id, cohead_number, cust_number, cust_name,"
" item_number, (item_descrip1 || ' ' || item_descrip2) AS item_description,"
" uom_name, item_picklist,"
" noNeg(itemsite_qtyonhand) AS qoh,"
" noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS sobalance,"
" qtyAllocated(itemsite_id, coitem_scheddate) AS allocated,"
" qtyOrdered(itemsite_id, coitem_scheddate) AS ordered,"
" qtyatshipping(coitem_id) AS atshipping,"
" coitem_qtyreserved,"
" coitem_scheddate,"
" CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel "
"<? if exists(\"showWoSupply\") ?>, "
" COALESCE(wo_id,-1) AS wo_id,"
" formatwonumber(wo_id) AS wo_number,"
" noNeg((wo_qtyord-wo_qtyrcv)) AS wo_ordered,"
" wo_status, wo_startdate, wo_duedate,"
" ((wo_startdate <= CURRENT_DATE) AND (wo_status IN ('O','E','S','R'))) AS wo_latestart,"
" (wo_duedate<=CURRENT_DATE) AS wo_latedue "
"<? endif ?>"
" FROM cohead, cust, itemsite, item, uom, coitem "
"<? if exists(\"showWoSupply\") ?> "
" LEFT OUTER JOIN wo"
" ON ((coitem_itemsite_id=wo_itemsite_id)"
" AND (wo_status IN ('E','R','I'))"
" AND (wo_qtyord-wo_qtyrcv > 0)"
" AND (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned-qtyatshipping(coitem_id)) > "
" (SELECT itemsite_qtyonhand FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))"
"<? endif ?>"
" WHERE ( (coitem_cohead_id=cohead_id)"
" AND (coitem_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (coitem_status <> 'X')"
" AND (cohead_cust_id=cust_id)"
"<? 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 "
" <? if exists(\"onlyShowShortages\") ?>"
"WHERE ( ((qoh + ordered - allocated) < 0)"
" OR ((qoh + ordered - sobalance) < 0) ) "
"<? endif ?>"
"ORDER BY cohead_id, cohead_number, item_number"
"<? if exists(\"showWoSupply\") ?> ,"
"wo_duedate"
"<? endif ?>"
";");
ParameterList params;
_custtype->appendValue(params);
if (_onlyShowShortages->isChecked())
params.append("onlyShowShortages");
if (_showWoSupply->isChecked())
params.append("showWoSupply");
if (_useReservationNetting->isChecked())
params.append("useReservationNetting");
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
if (q.first())
{
XTreeWidgetItem *coitem = NULL, *cohead = NULL;
XTreeWidgetItem *wo = NULL;
int coitemid = -1;
int coheadid = -1;
do
{
if (coitemid != q.value("coitem_id").toInt())
{
if(coheadid != q.value("cohead_id").toInt())
{
coheadid = q.value("cohead_id").toInt();
cohead = new XTreeWidgetItem(_avail, cohead, coheadid, -2,
q.value("cohead_number"), q.value("custname"));
}
coitemid = q.value("coitem_id").toInt();
coitem = new XTreeWidgetItem( cohead, coitem,
q.value("itemsite_id").toInt(), q.value("coitem_id").toInt(),
q.value("item_number"),
q.value("item_description"), q.value("uom_name"),
q.value("f_qoh"), q.value("f_sobalance"),
q.value("f_allocated"), q.value("f_ordered"),
q.value("f_soavail"), q.value("f_totalavail"),
q.value("f_atshipping"), q.value("f_scheddate") );
if (q.value("qoh").toDouble() < 0)
coitem->setTextColor(3, "red");
else if (q.value("qoh").toDouble() < q.value("reorderlevel").toDouble())
coitem->setTextColor(3, "orange");
if (q.value("woavail").toDouble() < 0.0)
coitem->setTextColor(7, "red");
else if (q.value("woavail").toDouble() <= q.value("reorderlevel").toDouble())
coitem->setTextColor(7, "orange");
if (q.value("totalavail").toDouble() < 0.0)
coitem->setTextColor(8, "red");
else if (q.value("totalavail").toDouble() <= q.value("reorderlevel").toDouble())
coitem->setTextColor(8, "orange");
if(_useReservationNetting->isChecked())
{
if(q.value("partialreservation").toBool())
{
coitem->setTextColor(0, "blue");
coitem->setTextColor(1, "blue");
coitem->setTextColor(7, "blue");
}
else if(q.value("fullreservation").toBool())
{
coitem->setTextColor(0, "green");
coitem->setTextColor(1, "green");
coitem->setTextColor(7, "green");
}
}
}
if ((coitem)
&& (_showWoSupply->isChecked())
&& (q.value("wo_id").toInt() != -1) )
{
wo = new XTreeWidgetItem( coitem, wo,
q.value("itemsite_id").toInt(),-1,
q.value("wo_number"),"",
q.value("wo_status"),
"", "",
"", q.value("f_wo_ordered"),
q.value("f_wo_startdate"), q.value("f_wo_duedate"),
"" );
if (q.value("wo_latestart").toBool())
wo->setTextColor(7, "red");
if (q.value("wo_latedue").toBool())
wo->setTextColor(8, "red");
}
}
while (q.next());
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
_avail->expandAll();
}
void user::sModuleSelected(const QString &pModule)
{
XTreeWidgetItem *granted = NULL;
XTreeWidgetItem *available = NULL;
_availableGroup->clear();
_grantedGroup->clear();
XSqlQuery groups;
groups.prepare("SELECT grp_id, grp_name, grp_descrip, usrgrp_id"
" FROM grp LEFT OUTER JOIN usrgrp"
" ON (usrgrp_grp_id=grp_id AND usrgrp_username=:username);");
groups.bindValue(":username", _cUsername);
groups.exec();
while(groups.next())
{
if (groups.value("usrgrp_id").toInt() == 0)
available = new XTreeWidgetItem(_availableGroup, available, groups.value("grp_id").toInt(), groups.value("grp_name"), groups.value("grp_descrip"));
else
granted = new XTreeWidgetItem(_grantedGroup, granted, groups.value("grp_id").toInt(), groups.value("grp_name"), groups.value("grp_descrip"));
}
if (ErrorReporter::error(QtCriticalMsg, this, tr("Getting Groups"),
groups, __FILE__, __LINE__))
return;
_available->clear();
_granted->clear();
XSqlQuery privs;
privs.prepare( "SELECT priv_id, priv_name, priv_descrip "
"FROM priv "
"WHERE (priv_module=:priv_module) "
"ORDER BY priv_name;" );
privs.bindValue(":priv_module", pModule);
privs.exec();
if (privs.first())
{
granted = NULL;
available = NULL;
// Insert each priv into either the available or granted list
XSqlQuery usrpriv;
usrpriv.prepare( "SELECT priv_id "
"FROM priv, usrpriv "
"WHERE ( (usrpriv_priv_id=priv_id)"
" AND (usrpriv_username=:username)"
" AND (priv_module=:priv_module) );" );
usrpriv.bindValue(":username", _cUsername);
usrpriv.bindValue(":priv_module", _module->currentText());
usrpriv.exec();
XSqlQuery grppriv;
grppriv.prepare("SELECT priv_id"
" FROM priv, grppriv, usrgrp"
" WHERE((usrgrp_grp_id=grppriv_grp_id)"
" AND (grppriv_priv_id=priv_id)"
" AND (usrgrp_username=:username)"
" AND (priv_module=:priv_module));");
grppriv.bindValue(":username", _cUsername);
grppriv.bindValue(":priv_module", _module->currentText());
grppriv.exec();
do
{
if (usrpriv.findFirst("priv_id", privs.value("priv_id").toInt()) == -1 && grppriv.findFirst("priv_id", privs.value("priv_id").toInt()) == -1)
available = new XTreeWidgetItem(_available, available, privs.value("priv_id").toInt(), privs.value("priv_name"), privs.value("priv_descrip"));
else
{
granted = new XTreeWidgetItem(_granted, granted, privs.value("priv_id").toInt(), privs.value("priv_name"), privs.value("priv_descrip"));
if(usrpriv.findFirst("priv_id", privs.value("priv_id").toInt()) == -1)
granted->setTextColor(Qt::gray);
}
}
while (privs.next());
}
}
void dspPoItemsByVendor::sFillList()
{
_poitem->clear();
QString sql( "SELECT pohead_id, poitem_id, pohead_number,"
" poitem_status,"
" CASE WHEN(poitem_status='C') THEN <? value(\"closed\") ?>"
" WHEN(poitem_status='U') THEN <? value(\"unposted\") ?>"
" WHEN(poitem_status='O' AND ((poitem_qty_received-poitem_qty_returned) > 0) AND (poitem_qty_ordered>(poitem_qty_received-poitem_qty_returned))) THEN <? value(\"partial\") ?>"
" WHEN(poitem_status='O' AND ((poitem_qty_received-poitem_qty_returned) > 0) AND (poitem_qty_ordered=(poitem_qty_received-poitem_qty_returned))) THEN <? value(\"received\") ?>"
" WHEN(poitem_status='O') THEN <? value(\"open\") ?>"
" ELSE poitem_status"
" END AS poitemstatus,"
" CASE WHEN (itemsite_id IS NULL) THEN ( SELECT warehous_code"
" FROM warehous"
" WHERE (pohead_warehous_id=warehous_id) )"
" ELSE ( SELECT warehous_code"
" FROM warehous"
" WHERE (itemsite_warehous_id=warehous_id) )"
" END AS warehousecode,"
" COALESCE(item_number, (<? value(\"nonInv\") ?> || poitem_vend_item_number)) AS itemnumber,"
" COALESCE(item_descrip1, firstLine(poitem_vend_item_descrip)) AS itemdescrip,"
" COALESCE(uom_name, poitem_vend_uom) AS itemuom,"
" formatDate(poitem_duedate) AS f_duedate,"
" formatQty(poitem_qty_ordered) AS f_qtyordered,"
" formatQty(poitem_qty_received) AS f_qtyreceived,"
" formatQty(poitem_qty_returned) AS f_qtyreturned,"
" (poitem_duedate < CURRENT_DATE) AS late "
"FROM pohead,"
" poitem LEFT OUTER JOIN"
" ( itemsite JOIN item"
" ON (itemsite_item_id=item_id) JOIN uom ON (item_inv_uom_id=uom_id))"
" ON (poitem_itemsite_id=itemsite_id) "
"WHERE ((poitem_pohead_id=pohead_id)"
"<? if exists(\"warehous_id\") ?>"
" AND (((itemsite_id IS NULL) AND"
" (pohead_warehous_id=<? value(\"warehous_id\") ?>) ) OR"
" ((itemsite_id IS NOT NULL) AND"
" (itemsite_warehous_id=<? value(\"warehous_id\") ?>) ) )"
"<? endif ?>"
"<? if exists(\"agentUsername\") ?>"
" AND (pohead_agent_username=<? value(\"agentUsername\") ?>)"
"<? endif ?>"
"<? if exists(\"poNumber\") ?>"
" AND (pohead_number=<? value(\"poNumber\") ?>)"
"<? endif ?>"
"<? if exists(\"openItems\") ?>"
" AND (poitem_status='O')"
"<? endif ?>"
"<? if exists(\"closedItems\") ?>"
" AND (poitem_status='C')"
"<? endif ?>"
" AND (pohead_vend_id=<? value(\"vend_id\") ?>) ) "
"ORDER BY poitem_duedate, pohead_number, poitem_linenumber;" );
ParameterList params;
setParams(params);
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
if (q.first())
{
XTreeWidgetItem *last = 0;
do
{
last = new XTreeWidgetItem(_poitem, last,
q.value("pohead_id").toInt(),
q.value("poitem_id").toInt(),
q.value("pohead_number"),
q.value("warehousecode"),
q.value("poitemstatus"),
q.value("f_duedate"),
q.value("itemnumber"),
q.value("itemdescrip"),
q.value("itemuom"),
q.value("f_qtyordered"),
q.value("f_qtyreceived"),
q.value("f_qtyreturned") );
last->setText(POITEM_STATUS_COL, q.value("poitem_status"));
if (q.value("late").toBool())
last->setTextColor(3, "red");
}
while (q.next());
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void dspInventoryBufferStatusByParameterList::sFillList()
{
_availability->clear();
QString sql( "SELECT itemsite_id, itemtype,"
" item_number, (item_descrip1 || ' ' || item_descrip2) AS itemdescrip,"
" warehous_id, warehous_code, itemsite_leadtime,"
" CASE WHEN (bufrsts_type='T') THEN :time"
" ELSE :stock"
" END AS bufrststype,"
" bufrsts_status,"
" formatQty(qoh) AS f_qoh,"
" formatQty(allocated) AS f_allocated,"
" formatQty(noNeg(qoh - allocated)) AS f_unallocated,"
" formatQty(ordered) AS f_ordered,"
" formatQty(reorderlevel) AS f_reorderlevel,"
" formatQty(outlevel) AS f_outlevel,"
" formatQty(qoh - allocated + ordered) AS f_available,"
" emergency "
" FROM ( SELECT itemsite_id,"
" CASE WHEN (item_type IN ('P', 'O')) THEN 1"
" WHEN (item_type IN ('M')) THEN 2"
" ELSE 0"
" END AS itemtype,"
" item_number, item_descrip1, item_descrip2,"
" warehous_id, warehous_code, itemsite_leadtime,"
" bufrsts_status, bufrsts_type,"
" itemsite_qtyonhand AS qoh,"
" itemsite_reorderlevel AS reorderlevel,"
" itemsite_ordertoqty AS outlevel,"
" qtyAllocated(itemsite_id, endoftime()) AS allocated,"
" qtyOrdered(itemsite_id, endoftime()) AS ordered,"
" (bufrsts_status > 65) AS emergency "
" FROM item, itemsite, warehous, bufrsts "
" WHERE ( (itemsite_active)"
" AND (itemsite_item_id=item_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (bufrsts_target_type='I')"
" AND (bufrsts_target_id=itemsite_id)"
" AND (bufrsts_date=current_date)");
if (_GreaterThanZero->isChecked())
sql += " AND (bufrsts_status > 0) ";
else if (_EmergencyZone->isChecked())
sql += " AND (bufrsts_status > 65)";
if (_warehouse->isSelected())
sql += " AND (warehous_id=:warehous_id)";
if (_parameter->isSelected())
{
if (_parameter->type() == ClassCode)
sql += " AND (item_classcode_id=:classcode_id)";
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem WHERE (itemgrpitem_itemgrp_id=:itemgrp_id)))";
else if (_parameter->type() == PlannerCode)
sql += " AND (itemsite_plancode_id=:plancode_id)";
}
else if (_parameter->isPattern())
{
if (_parameter->type() == ClassCode)
sql += " AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ :classcode_pattern)))";
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem, itemgrp WHERE ( (itemgrpitem_itemgrp_id=itemgrp_id) AND (itemgrp_name ~ :itemgrp_pattern) ) ))";
else if (_parameter->type() == PlannerCode)
sql += " AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ :plancode_pattern)))";
}
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT DISTINCT itemgrpitem_item_id FROM itemgrpitem))";
sql += ") ) as data ";
sql += "ORDER BY bufrsts_status DESC, item_number, warehous_code DESC;";
q.prepare(sql);
_warehouse->bindValue(q);
_parameter->bindValue(q);
q.bindValue(":stock", tr("Stock"));
q.bindValue(":time", tr("Time"));
q.exec();
XTreeWidgetItem * last = 0;
while (q.next())
{
last = new XTreeWidgetItem( _availability, last,
q.value("itemsite_id").toInt(), q.value("itemtype").toInt(),
q.value("item_number").toString(), q.value("itemdescrip"),
q.value("warehous_code"), q.value("itemsite_leadtime"),
q.value("bufrststype"), q.value("bufrsts_status"),
q.value("f_qoh"), q.value("f_allocated"),
q.value("f_unallocated"), q.value("f_ordered"));
last->setText(10, q.value("f_reorderlevel").toString());
last->setText(11, q.value("f_outlevel").toString());
last->setText(12, q.value("f_available").toString());
if (q.value("emergency").toBool())
last->setTextColor(5, QColor("red"));
}
}
void dspPlannedRevenueExpensesByPlannerCode::sFillList()
{
_planord->clear();
ParameterList params;
setParams(params);
QString sql( "SELECT planord_id, planord_itemsite_id,"
" plonumber, plotype, item_number, itemdescrip,"
" formatDate(planord_duedate) AS duedate,"
" formatQty(planord_qty) AS qty,"
" plofirm,"
" formatMoney(plocost) AS cost,"
" formatMoney(plorevenue) AS revenue,"
" formatMoney(plorevenue - plocost) AS profit,"
" plocost, plorevenue "
"FROM ( SELECT planord_id, planord_itemsite_id, planord_duedate,"
" formatPloNumber(planord_id) AS plonumber,"
" CASE WHEN (planord_type='P') THEN 'P/O'"
" WHEN (planord_type='W') THEN 'W/O'"
" ELSE '?'"
" END AS plotype,"
" item_number,"
" (item_descrip1 || ' ' || item_descrip2) AS itemdescrip,"
" formatDate(planord_duedate),"
" planord_qty, formatBoolYN(planord_firm) AS plofirm,"
"<? if exists(\"useActualCost\") ?>"
" (actcost(item_id) * planord_qty)"
"<? elseif exists(\"useStandardCost\") ?>"
" (stdcost(item_id) * planord_qty)"
"<? endif ?> AS plocost,"
"<? if exists(\"useListPrice\") ?>"
" (item_listprice * planord_qty) "
"<? elseif exists(\"useAveragePrice\") ?>"
" (CASE WHEN(averageSalesPrice(itemsite_id,"
" <? value(\"startEvalDate\") ?>,"
" <? value(\"endEvalDate\") ?>)=0)"
" THEN item_listprice"
" ELSE averageSalesPrice(itemsite_id,"
" <? value(\"startEvalDate\") ?>,"
" <? value(\"endEvalDate\") ?>)"
" END * planord_qty)"
"<? endif ?> AS plorevenue "
"FROM planord, itemsite, item "
"WHERE ((planord_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (item_sold)"
" AND (planord_duedate BETWEEN <? value(\"startDate\") ?>"
" AND <? value(\"endDate\") ?>)"
"<? if 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(\"warehous_id\") ?>"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
"<? endif ?>"
") ) AS data "
"ORDER BY planord_duedate, item_number;" );
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
XTreeWidgetItem *last = NULL;
double cost = 0;
double revenue = 0;
while (q.next())
{
last = new XTreeWidgetItem( _planord, last, q.value("planord_id").toInt(), q.value("planord_itemsite_id").toInt(),
q.value("plonumber"), q.value("plotype"),
q.value("item_number"), q.value("itemdescrip"),
q.value("duedate"), q.value("qty"),
q.value("plofirm"), q.value("cost"),
q.value("revenue"), q.value("profit") );
cost += q.value("plocost").toDouble();
revenue += q.value("plorevenue").toDouble();
if (q.value("plocost").toDouble() > q.value("plorevenue").toDouble())
last->setTextColor(9, "red");
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
last = new XTreeWidgetItem(_planord, last, -1, -1, tr("Totals:"));
last->setText(7, formatMoney(cost));
last->setText(8, formatMoney(revenue));
last->setText(9, formatMoney(revenue - cost));
if (cost > revenue)
last->setTextColor(9, "red");
}