Beispiel #1
0
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;
  }
}
Beispiel #2
0
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());
  }
}
Beispiel #3
0
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(""));
  }
}
Beispiel #5
0
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=");
}
Beispiel #6
0
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");
  }
}
Beispiel #7
0
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;
    }
  }
}
Beispiel #8
0
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;
    }
  }
}
Beispiel #10
0
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());
    }
  }
}
Beispiel #11
0
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;
  }
}
Beispiel #12
0
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");
    }
  }
}
Beispiel #13
0
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");
  }
}
Beispiel #14
0
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();
}
Beispiel #15
0
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();
}
Beispiel #16
0
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");
    }
  }
}
Beispiel #17
0
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());
    }
  }
}
Beispiel #18
0
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();
}
Beispiel #19
0
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;
    }
  }
}
Beispiel #20
0
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__);

  }
}
Beispiel #21
0
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;
  }
}
Beispiel #22
0
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");
  }
}
Beispiel #23
0
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");
  }
}
Beispiel #24
0
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();
}
Beispiel #25
0
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);
}
Beispiel #26
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();
}
Beispiel #27
0
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());
    }
}
Beispiel #28
0
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;
  }
}
Beispiel #29
0
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"));
  }
}
Beispiel #30
0
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");
}