Пример #1
0
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();
    }
}
Пример #2
0
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();
  }
}