Example #1
0
void WomatlCluster::setWoid(int pWoid)
{
  _source = WorkOrder;
  _sourceId = pWoid;

  bool qual = FALSE;
  QString sql( "SELECT womatl_id AS womatlid, item_number,"
               "       wo_id, uom_name, item_descrip1, item_descrip2,"
               "       womatl_qtyreq AS _qtyreq, womatl_qtyiss AS _qtyiss,"
               "       formatQtyPer(womatl_qtyper) AS qtyper,"
               "       formatScrap(womatl_scrap) AS scrap,"
               "       formatQtyPer(womatl_qtyreq) AS qtyreq,"
               "       formatQtyPer(womatl_qtyiss) AS qtyiss,"
               "       formatQtyPer(womatl_qtywipscrap) AS qtywipscrap "
               "FROM womatl, wo, itemsite, item, uom "
               "WHERE ( (womatl_wo_id=wo_id)"
               " AND (womatl_itemsite_id=itemsite_id)"
               " AND (itemsite_item_id=item_id)"
               " AND (womatl_uom_id=uom_id)"
               " AND (wo_id=:wo_id)"
               " AND (womatl_issuemethod IN (" );

  if (_type & Push)
  {
    sql += "'S'";
    qual = TRUE;
  }

  if (_type & Pull)
  {
    if (qual)
      sql += ",";
    else
      qual = TRUE;

    sql += "'L'";
  }

  if (_type & Mixed)
  {
    if (qual)
      sql += ",";

    sql += "'M'";
  }

  sql += ")) );";

  XSqlQuery query;
  query.prepare(sql);
  query.bindValue(":wo_id", pWoid);
  query.exec();
  if (query.first())
  {
    _womatl.prepare(sql);
    _womatl.bindValue(":wo_id", pWoid);
    _womatl.exec();
    _itemNumber->populate(query);
  }
  else
  {
    _id = -1;
    _woid = -1;
    _valid = FALSE;
    _required = 0.0;
    _issued  = 0.0;
    
    emit newId(-1);
    emit newQtyRequired(formatQtyPer(0.0));
    emit newQtyIssued(formatQtyPer(0.0));
    emit newQtyScrappedFromWIP(formatQtyPer(0.0));

    _itemNumber->clear();
  }
}
Example #2
0
void BOM::sFillList(int pItemid, bool)
{
  if (_item->isValid() && (pItemid == _item->id()))
  {
    q.prepare( "SELECT bomhead_docnum, bomhead_revision,"
               "       bomhead_revisiondate,"
               "       formatQty(bomhead_batchsize) AS f_batchsize,"
               "       bomhead_requiredqtyper "
               "FROM bomhead "
               "WHERE (bomhead_item_id=:item_id);" );
    q.bindValue(":item_id", _item->id());
    q.exec();
    if (q.first())
    {
      _documentNum->setText(q.value("bomhead_docnum"));
      _revision->setText(q.value("bomhead_revision"));
      _revisionDate->setDate(q.value("bomhead_revisiondate").toDate());
      _batchSize->setText(q.value("f_batchsize"));
      if(q.value("bomhead_requiredqtyper").toDouble()!=0)
      {
        _doRequireQtyPer->setChecked(true);
        _requiredQtyPer->setText(q.value("bomhead_requiredqtyper").toString());
      }
    }
    else
    {
      _documentNum->clear();
      _revision->clear();
      _revisionDate->clear();
      _batchSize->clear();
    }
    
    QString sql( "SELECT bomitem_id, item_id, bomitem_seqnumber,"
                 "       item_number, (item_descrip1 || ' ' || item_descrip2) AS item_description,"
                 "       item_invuom,"
                 "       CASE WHEN (bomitem_issuemethod = 'S') THEN :push"
                 "            WHEN (bomitem_issuemethod = 'L') THEN :pull"
                 "            WHEN (bomitem_issuemethod = 'M') THEN :mixed"
                 "            ELSE :error"
                 "       END AS issuemethod,"
                 "       formatQtyPer(bomitem_qtyper) AS f_qtyper,"
                 "       formatScrap(bomitem_scrap) AS f_scrap,"
                 "       formatDate(bomitem_effective, :always) AS f_effective,"
                 "       formatDate(bomitem_expires, :never) AS f_expires,"
                 "       (bomitem_configtype<>'N') AS config "
                 "FROM bomitem, item "
                 "WHERE ((bomitem_item_id=item_id)"
                 " AND (bomitem_parent_item_id=:item_id)" );
    
    if (!_showExpired->isChecked())
      sql += " AND (bomitem_expires > CURRENT_DATE)";
    
    if (!_showFuture->isChecked())
      sql += " AND (bomitem_effective <= CURRENT_DATE)";
    
    sql += ") "
           "ORDER BY bomitem_seqnumber, bomitem_effective";
    
    int bomitemid = _bomitem->id();
    _bomitem->clear();
    
    XListViewItem *selected = 0;
    
    q.prepare(sql);
    q.bindValue(":push", tr("Push"));
    q.bindValue(":pull", tr("Pull"));
    q.bindValue(":mixed", tr("Mixed"));
    q.bindValue(":error", tr("Error"));
    q.bindValue(":always", tr("Always"));
    q.bindValue(":never", tr("Never"));
    q.bindValue(":item_id", _item->id());
    q.exec();
    while (q.next())
    {
      XListViewItem *last = new XListViewItem( _bomitem, _bomitem->lastItem(),
                                               q.value("bomitem_id").toInt(), q.value("item_id").toInt(),
                                               q.value("bomitem_seqnumber"), q.value("item_number"),
                                               q.value("item_description"), q.value("item_invuom"),
                                               q.value("issuemethod"), q.value("f_qtyper"),
                                               q.value("f_scrap"), q.value("f_effective"),
                                               q.value("f_expires") );
      if (q.value("config").toBool())
        last->setColor("blue");
      
      if (q.value("bomitem_id").toInt() == bomitemid)
        selected = last;
    }
    
    if (selected)
    {
      _bomitem->setSelected(selected, TRUE);
      _bomitem->ensureItemVisible(selected);
    }
    
    sql = "SELECT item_picklist,"
          "       COUNT(*) AS total,"
          "       COALESCE(SUM(bomitem_qtyper * (1 + bomitem_scrap))) AS qtyper "
          "FROM bomitem, item "
          "WHERE ( (bomitem_item_id=item_id)"
          " AND (bomitem_parent_item_id=:item_id)";
    
    if (!_showExpired->isChecked())
      sql += " AND (bomitem_expires > CURRENT_DATE)";
    
    if (!_showFuture->isChecked())
      sql += " AND (bomitem_effective <= CURRENT_DATE)";
    
    sql += " ) "
           "GROUP BY item_picklist;";
    
    q.prepare(sql);
    q.bindValue(":item_id", _item->id());
    q.exec();
    bool   foundPick    = FALSE;
    bool   foundNonPick = FALSE;
    int    totalNumber  = 0;
    double totalQtyPer  = 0.0;
    while (q.next())
    {
      totalNumber += q.value("total").toInt();
      totalQtyPer += q.value("qtyper").toDouble();
      
      if (q.value("item_picklist").toBool())
      {
        foundPick = TRUE;
        _pickNumber->setText(q.value("total").toString());
        _pickQtyPer->setText(formatQtyPer(q.value("qtyper").toDouble()));
      }
      else
      {
        foundNonPick = TRUE;
        _nonPickNumber->setText(q.value("total").toString());
        _nonPickQtyPer->setText(formatQtyPer(q.value("qtyper").toDouble()));
      }
    }
    
    if (!foundPick)
    {
      _pickNumber->setText("0");
      _pickQtyPer->setText(formatQty(0.0));
    }
    
    if (!foundNonPick)
    {
      _nonPickNumber->setText("0");
      _nonPickQtyPer->setText(formatQty(0.0));
    }
    
    _totalNumber->setText(QString("%1").arg(totalNumber));
    _totalQtyPer->setText(formatQtyPer(totalQtyPer));
    _totalQtyPerCache = totalQtyPer;
    
    if (_privleges->check("ViewCosts"))
    {
      sql = "SELECT formatCost(p.item_maxcost) AS f_maxcost,"
            "       formatCost(COALESCE(SUM(bomitem_qtyper * (1 + bomitem_scrap) * stdCost(c.item_id)))) AS f_stdcost,"
            "       formatCost(COALESCE(SUM(bomitem_qtyper * (1 + bomitem_scrap) * ROUND(actCost(c.item_id),4)))) AS f_actcost "
            "FROM bomitem, item AS c, item AS p "
            "WHERE ( (bomitem_parent_item_id=p.item_id)"
            " AND (bomitem_item_id=c.item_id)"
            " AND (p.item_id=:item_id)";
      
      if (!_showExpired->isChecked())
        sql += " AND (bomitem_expires > CURRENT_DATE)";
      
      if (!_showFuture->isChecked())
        sql += " AND (bomitem_effective <= CURRENT_DATE)";
      
      sql += " ) "
             "GROUP BY p.item_maxcost;";
      
      q.prepare(sql);
      q.bindValue(":item_id", _item->id());
      q.exec();
      if (q.first())
      {
        _currentStdCost->setText(q.value("f_stdcost").toString());
        _currentActCost->setText(q.value("f_actcost").toString());
        _maxCost->setText(q.value("f_maxcost").toString());
      }
    }
  }
  else if (!_item->isValid())
  {
    _documentNum->clear();
    _revision->clear();
    _revisionDate->clear();
    _batchSize->clear();
    
    _bomitem->clear();
  }
}