void BOM::sFillList(int pItemid, bool)
{
if (_item->isValid() && (pItemid == _item->id()))
{
q.prepare( "SELECT * "
"FROM bomhead "
"WHERE ( (bomhead_item_id=:item_id) "
"AND (bomhead_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("bomhead_docnum"));
_revision->setNumber(q.value("bomhead_revision").toString());
_revisionDate->setDate(q.value("bomhead_revisiondate").toDate());
_batchSize->setDouble(q.value("bomhead_batchsize").toDouble());
if(q.value("bomhead_requiredqtyper").toDouble()!=0)
{
_doRequireQtyPer->setChecked(true);
_requiredQtyPer->setDouble(q.value("bomhead_requiredqtyper").toDouble());
}
if (_revision->description() == "Inactive")
{
_save->setEnabled(FALSE);
_new->setEnabled(FALSE);
_documentNum->setEnabled(FALSE);
_revisionDate->setEnabled(FALSE);
_batchSize->setEnabled(FALSE);
_bomitem->setEnabled(FALSE);
}
if ((_revision->description() == "Pending") || (_revision->description() == "Active"))
{
_save->setEnabled(TRUE);
_new->setEnabled(TRUE);
_documentNum->setEnabled(TRUE);
_revisionDate->setEnabled(TRUE);
_batchSize->setEnabled(TRUE);
_bomitem->setEnabled(TRUE);
}
}
else
{
_documentNum->clear();
_revisionDate->clear();
_batchSize->clear();
}
ParameterList params;
setParams(params);
MetaSQLQuery mql = mqlLoad("bomItems", "detail");
q = mql.toQuery(params);
_bomitem->populate(q);
if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
MetaSQLQuery picklistmql("SELECT item_picklist,"
" COUNT(*) AS total,"
" COALESCE(SUM(bomitem_qtyper * (1 + bomitem_scrap))) AS qtyper "
"FROM bomitem(<? value(\"item_id\") ?>,"
" <? value(\"revision_id\") ?>), item "
"WHERE ( (bomitem_item_id=item_id)"
"<? if not exists(\"showExpired\") ?>"
" AND (bomitem_expires > CURRENT_DATE)"
"<? endif ?>"
"<? if not exists(\"showFuture\") ?>"
" AND (bomitem_effective <= CURRENT_DATE)"
"<? endif ?>"
" ) "
"GROUP BY item_picklist;");
q = picklistmql.toQuery(params);
bool foundPick = FALSE;
bool foundNonPick = FALSE;
int totalNumber = 0;
double totalQtyPer = 0.0;
while (q.next())
{
totalNumber += q.value("total").toInt();
totalQtyPer += q.value("qtyper").toDouble();
if (q.value("item_picklist").toBool())
{
foundPick = TRUE;
_pickNumber->setDouble(q.value("total").toDouble());
_pickQtyPer->setDouble(q.value("qtyper").toDouble());
}
else
{
foundNonPick = TRUE;
_nonPickNumber->setDouble(q.value("total").toDouble());
_nonPickQtyPer->setDouble(q.value("qtyper").toDouble());
}
}
if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
if (!foundPick)
{
_pickNumber->setDouble(0);
_pickQtyPer->setDouble(0.0);
}
if (!foundNonPick)
{
_nonPickNumber->setDouble(0);
_nonPickQtyPer->setDouble(0.0);
}
_totalNumber->setDouble(totalNumber);
_totalQtyPer->setDouble(totalQtyPer);
_totalQtyPerCache = totalQtyPer;
if (_privileges->check("ViewCosts"))
{
MetaSQLQuery costsmql("SELECT p.item_maxcost,"
" COALESCE(SUM(itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, bomitem_qtyper * (1 + bomitem_scrap)) * stdCost(c.item_id))) AS stdcost,"
" COALESCE(SUM(itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, bomitem_qtyper * (1 + bomitem_scrap)) * ROUND(actCost(c.item_id),4))) AS actcost "
"FROM bomitem(<? value(\"item_id\") ?>,"
" <? value(\"revision_id\") ?>), item AS c, item AS p "
"WHERE ( (bomitem_item_id=c.item_id)"
" AND (p.item_id=<? value(\"item_id\") ?>)"
"<? if not exists(\"showExpired\") ?>"
" AND (bomitem_expires > CURRENT_DATE)"
"<? endif ?>"
"<? if not exists(\"showFuture\") ?>"
" AND (bomitem_effective <= CURRENT_DATE)"
"<? endif ?>"
" ) "
"GROUP BY p.item_maxcost;");
q = costsmql.toQuery(params);
if (q.first())
{
_currentStdCost->setDouble(q.value("stdcost").toDouble());
_currentActCost->setDouble(q.value("actcost").toDouble());
_maxCost->setDouble(q.value("item_maxcost").toDouble());
}
if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
}
else if (!_item->isValid())
{
_documentNum->clear();
_revision->clear();
_revisionDate->clear();
_batchSize->clear();
_bomitem->clear();
}
}
void BOM::sFillList(int pItemid, bool)
{
if (_item->isValid() && (pItemid == _item->id()))
{
q.prepare( "SELECT * "
"FROM bomhead "
"WHERE ( (bomhead_item_id=:item_id) "
"AND (bomhead_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("bomhead_docnum"));
_revision->setNumber(q.value("bomhead_revision").toString());
_revisionDate->setDate(q.value("bomhead_revisiondate").toDate());
_batchSize->setDouble(q.value("bomhead_batchsize").toDouble());
if(q.value("bomhead_requiredqtyper").toDouble()!=0)
{
_doRequireQtyPer->setChecked(true);
_requiredQtyPer->setDouble(q.value("bomhead_requiredqtyper").toDouble());
}
if (_revision->description() == "Inactive")
{
_save->setEnabled(FALSE);
_new->setEnabled(FALSE);
_documentNum->setEnabled(FALSE);
_revisionDate->setEnabled(FALSE);
_batchSize->setEnabled(FALSE);
_bomitem->setEnabled(FALSE);
}
if ((_revision->description() == "Pending") || (_revision->description() == "Active"))
{
_save->setEnabled(TRUE);
_new->setEnabled(TRUE);
_documentNum->setEnabled(TRUE);
_revisionDate->setEnabled(TRUE);
_batchSize->setEnabled(TRUE);
_bomitem->setEnabled(TRUE);
}
}
else
{
_documentNum->clear();
_revisionDate->clear();
_batchSize->clear();
}
QString sql( "SELECT bomitem_id, item_id, *,"
" (item_descrip1 || ' ' || item_descrip2) AS item_description,"
" uom_name AS issueuom,"
" CASE WHEN (bomitem_issuemethod = 'S') THEN <? value(\"push\") ?>"
" WHEN (bomitem_issuemethod = 'L') THEN <? value(\"pull\") ?>"
" WHEN (bomitem_issuemethod = 'M') THEN <? value(\"mixed\") ?>"
" ELSE <? value(\"error\") ?>"
" END AS issuemethod,"
" 'qtyper' AS bomitem_qtyper_xtnumericrole,"
" 'percent' AS bomitem_scrap_xtnumericrole,"
" CASE WHEN (bomitem_effective = startOfTime()) THEN NULL "
" ELSE bomitem_effective END AS effective,"
" CASE WHEN (bomitem_expires = endOfTime()) THEN NULL "
" ELSE bomitem_expires END AS expires,"
" <? value(\"always\") ?> AS effective_xtnullrole,"
" <? value(\"never\") ?> AS expires_xtnullrole,"
" CASE WHEN (bomitem_expires < CURRENT_DATE) THEN 'expired'"
" WHEN (bomitem_effective >= CURRENT_DATE) THEN 'future'"
" END AS qtforegroundrole "
"FROM bomitem(<? value(\"item_id\") ?>,"
" <? value(\"revision_id\") ?>), item, uom "
"WHERE ((bomitem_item_id=item_id)"
" AND (bomitem_uom_id=uom_id)"
"<? if not exists(\"showExpired\") ?>"
" AND (bomitem_expires > CURRENT_DATE)"
"<? endif ?>"
"<? if not exists(\"showFuture\") ?>"
" AND (bomitem_effective <= CURRENT_DATE)"
"<? endif ?>"
") "
"ORDER BY bomitem_seqnumber, bomitem_effective;"
);
ParameterList params;
setParams(params);
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
_bomitem->populate(q);
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
sql = "SELECT item_picklist,"
" COUNT(*) AS total,"
" COALESCE(SUM(bomitem_qtyper * (1 + bomitem_scrap))) AS qtyper "
"FROM bomitem(<? value(\"item_id\") ?>,"
" <? value(\"revision_id\") ?>), item "
"WHERE ( (bomitem_item_id=item_id)"
"<? if not exists(\"showExpired\") ?>"
" AND (bomitem_expires > CURRENT_DATE)"
"<? endif ?>"
"<? if not exists(\"showFuture\") ?>"
" AND (bomitem_effective <= CURRENT_DATE)"
"<? endif ?>"
" ) "
"GROUP BY item_picklist;";
MetaSQLQuery picklistmql(sql);
q = picklistmql.toQuery(params);
bool foundPick = FALSE;
bool foundNonPick = FALSE;
int totalNumber = 0;
double totalQtyPer = 0.0;
while (q.next())
{
totalNumber += q.value("total").toInt();
totalQtyPer += q.value("qtyper").toDouble();
if (q.value("item_picklist").toBool())
{
foundPick = TRUE;
_pickNumber->setDouble(q.value("total").toDouble());
_pickQtyPer->setDouble(q.value("qtyper").toDouble());
}
else
{
foundNonPick = TRUE;
_nonPickNumber->setDouble(q.value("total").toDouble());
_nonPickQtyPer->setDouble(q.value("qtyper").toDouble());
}
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
if (!foundPick)
{
_pickNumber->setDouble(0);
_pickQtyPer->setDouble(0.0);
}
if (!foundNonPick)
{
_nonPickNumber->setDouble(0);
_nonPickQtyPer->setDouble(0.0);
}
_totalNumber->setDouble(totalNumber);
_totalQtyPer->setDouble(totalQtyPer);
_totalQtyPerCache = totalQtyPer;
if (_privileges->check("ViewCosts"))
{
sql = "SELECT formatCost(p.item_maxcost) AS f_maxcost,"
" COALESCE(SUM(itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, bomitem_qtyper * (1 + bomitem_scrap)) * stdCost(c.item_id))) AS stdcost,"
" COALESCE(SUM(itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, bomitem_qtyper * (1 + bomitem_scrap)) * ROUND(actCost(c.item_id),4))) AS actcost "
"FROM bomitem(<? value(\"item_id\") ?>,"
" <? value(\"revision_id\") ?>), item AS c, item AS p "
"WHERE ( (bomitem_item_id=c.item_id)"
" AND (p.item_id=<? value(\"item_id\") ?>)"
"<? if not exists(\"showExpired\") ?>"
" AND (bomitem_expires > CURRENT_DATE)"
"<? endif ?>"
"<? if not exists(\"showFuture\") ?>"
" AND (bomitem_effective <= CURRENT_DATE)"
"<? endif ?>"
" ) "
"GROUP BY p.item_maxcost;";
MetaSQLQuery costsmql(sql);
q = costsmql.toQuery(params);
if (q.first())
{
_currentStdCost->setDouble(q.value("stdcost").toDouble());
_currentActCost->setDouble(q.value("actcost").toDouble());
_maxCost->setDouble(q.value("maxcost").toDouble());
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
}
else if (!_item->isValid())
{
_documentNum->clear();
_revision->clear();
_revisionDate->clear();
_batchSize->clear();
_bomitem->clear();
}
}
