void reserveSalesOrderItem::populate() { ParameterList itemp; itemp.append("soitem_id", _itemid); QString sql = "SELECT cohead_number AS order_number," " coitem_linenumber," " itemsite_item_id AS item_id," " warehous_code," " uom_name," " formatQty(itemsite_qtyonhand) AS qtyonhand," " formatQty(qtyReserved(itemsite_id)) AS totreserved," " formatQty(qtyUnreserved(itemsite_id)) AS totunreserved," " formatQty(coitem_qtyord) AS qtyordered," " formatQty(coitem_qtyshipped) AS qtyshipped," " formatQty(coitem_qtyreturned) AS qtyreturned," " formatQty(coitem_qtyreserved) AS qtyreserved," " formatQty(noNeg(coitem_qtyord - coitem_qtyshipped +" " coitem_qtyreturned - coitem_qtyreserved)) AS balance " "FROM cohead, coitem, itemsite, item, warehous, uom " "WHERE ((coitem_cohead_id=cohead_id)" " AND (coitem_itemsite_id=itemsite_id)" " AND (coitem_status <> 'X')" " AND (coitem_qty_uom_id=uom_id)" " AND (itemsite_item_id=item_id)" " AND (itemsite_warehous_id=warehous_id)" " AND (coitem_id=<? value(\"soitem_id\") ?>) );"; MetaSQLQuery itemm(sql); XSqlQuery itemq = itemm.toQuery(itemp); if (itemq.first()) { _salesOrderNumber->setText(itemq.value("order_number").toString()); _salesOrderLine->setText(itemq.value("coitem_linenumber").toString()); _item->setId(itemq.value("item_id").toInt()); _warehouse->setText(itemq.value("warehous_code").toString()); _qtyUOM->setText(itemq.value("uom_name").toString()); _qtyOrdered->setText(itemq.value("qtyordered").toString()); _qtyShipped->setText(itemq.value("qtyshipped").toString()); _balance->setText(itemq.value("balance").toString()); _reserved->setText(itemq.value("qtyreserved").toString()); _onHand->setText(itemq.value("qtyonhand").toString()); _allocated->setText(itemq.value("totreserved").toString()); _unreserved->setText(itemq.value("totunreserved").toString()); } else if (itemq.lastError().type() != QSqlError::None) { systemError(this, itemq.lastError().databaseText(), __FILE__, __LINE__); return; } _qtyToIssue->setText(itemq.value("balance").toString()); }
void issueLineToShipping::populate() { ParameterList itemp; if (_ordertype == "SO") itemp.append("soitem_id", _itemid); else if (_ordertype == "TO") itemp.append("toitem_id", _itemid); itemp.append("ordertype", _ordertype); // TODO: make this an orderitem select QString sql = "<? if exists(\"soitem_id\") ?>" "SELECT cohead_number AS order_number," " itemsite_item_id AS item_id," " warehous_code, uom_name," " coitem_qtyord AS qtyordered," " coitem_qtyshipped AS qtyshipped," " coitem_qtyreturned AS qtyreturned," " noNeg(coitem_qtyord - coitem_qtyshipped +" " coitem_qtyreturned) AS balance " "FROM cohead, coitem, itemsite, item, warehous, uom " "WHERE ((coitem_cohead_id=cohead_id)" " AND (coitem_itemsite_id=itemsite_id)" " AND (coitem_status <> 'X')" " AND (coitem_qty_uom_id=uom_id)" " AND (itemsite_item_id=item_id)" " AND (itemsite_warehous_id=warehous_id)" " AND (coitem_id=<? value(\"soitem_id\") ?>) );" "<? elseif exists(\"toitem_id\") ?>" "SELECT tohead_number AS order_number," " toitem_item_id AS item_id," " warehous_code, toitem_uom AS uom_name," " toitem_qty_ordered AS qtyordered," " toitem_qty_shipped AS qtyshipped," " 0 AS qtyreturned," " noNeg(toitem_qty_ordered -" " toitem_qty_shipped) AS balance " "FROM tohead, toitem, warehous, item " "WHERE ((toitem_tohead_id=tohead_id)" " AND (toitem_status <> 'X')" " AND (tohead_src_warehous_id=warehous_id)" " AND (toitem_id=<? value(\"toitem_id\") ?>) );" "<? endif ?>"; MetaSQLQuery itemm(sql); XSqlQuery itemq = itemm.toQuery(itemp); if (itemq.first()) { _orderNumber->setText(itemq.value("order_number").toString()); _item->setId(itemq.value("item_id").toInt()); _warehouse->setText(itemq.value("warehous_code").toString()); _shippingUOM->setText(itemq.value("uom_name").toString()); _qtyOrdered->setDouble(itemq.value("qtyordered").toDouble()); _qtyShipped->setDouble(itemq.value("qtyshipped").toDouble()); _qtyReturned->setDouble(itemq.value("qtyreturned").toDouble()); _balance->setDouble(itemq.value("balance").toDouble()); } else if (itemq.lastError().type() != QSqlError::NoError) { systemError(this, itemq.lastError().databaseText(), __FILE__, __LINE__); return; } ParameterList shipp; shipp.append("ordertype", _ordertype); shipp.append("orderitem_id", _itemid); sql = "SELECT shiphead_id AS misc_id," " SUM(shipitem_qty) AS qtyatship " "FROM shiphead, shipitem " "WHERE ((shipitem_shiphead_id=shiphead_id)" " AND (NOT shiphead_shipped)" " AND (shiphead_order_type=<? value(\"ordertype\") ?>)" " AND (shipitem_orderitem_id=<? value(\"orderitem_id\") ?>) ) " "GROUP BY shiphead_id;" ; MetaSQLQuery shipm(sql); XSqlQuery shipq = shipm.toQuery(shipp); if (shipq.first()) { _shipment->setType(_ordertype); _shipment->setId(shipq.value("misc_id").toInt()); _qtyAtShip->setDouble(shipq.value("qtyatship").toDouble()); } else if (shipq.lastError().type() != QSqlError::NoError) { systemError( this, shipq.lastError().databaseText(), __FILE__, __LINE__); return; } if (_qtyAtShip->toDouble() == 0.0) _qtyToIssue->setDouble(itemq.value("balance").toDouble()); }
void shipOrder::sFillList() { if (_shipment->isValid()) { calcFreight(); QString ordertype; XSqlQuery shipq; shipq.prepare("SELECT shiphead_order_id, shiphead_shipvia, shiphead_order_type," " shiphead_tracknum, shiphead_freight, shiphead_freight_curr_id," " COALESCE(shipchrg_custfreight, TRUE) AS custfreight," " COALESCE(shiphead_shipdate,CURRENT_DATE) AS effective " "FROM shiphead LEFT OUTER JOIN " " shipchrg ON (shiphead_shipchrg_id=shipchrg_id) " "WHERE ( (NOT shiphead_shipped)" " AND (shiphead_id=:shiphead_id));" ); shipq.bindValue(":shiphead_id", _shipment->id()); shipq.exec(); if (shipq.first()) { _order->setId(shipq.value("shiphead_order_id").toInt(),shipq.value("shiphead_order_type").toString()); _shipVia->setText(shipq.value("shiphead_shipvia").toString()); ordertype = shipq.value("shiphead_order_type").toString(); _tracknum->addItem(shipq.value("shiphead_tracknum").toString(), shipq.value("shiphead_tracknum").toString()); if (shipq.value("custfreight").toBool()) { _freight->setEnabled(TRUE); _freight->set(shipq.value("shiphead_freight").toDouble(), shipq.value("shiphead_freight_curr_id").toInt(), shipq.value("effective").toDate(), false); } else { _freight->setEnabled(FALSE); _freight->set(0, shipq.value("shiphead_freight_curr_id").toInt(), shipq.value("effective").toDate()); } } else if (shipq.lastError().type() != QSqlError::NoError) { systemError(this, shipq.lastError().databaseText(), __FILE__, __LINE__); return; } else { QMessageBox::warning(this, tr("Shipment/Order mismatch"), tr("<p>Shipment #%1 either is not part of " "Order #%2 or has already shipped. Please change " "either the Order # or Shipment #.") .arg(_shipment->number()) .arg(_order->number())); _shipment->clear(); _shipment->setEnabled(false); return; } ParameterList itemp; itemp.append("shiphead_id", _shipment->id()); itemp.append("ordertype", ordertype); if (ordertype == "SO") itemp.append("sohead_id", _order->id()); else if (ordertype == "TO") itemp.append("tohead_id", _order->id()); QString items = "<? if exists(\"sohead_id\") ?>" "SELECT coitem_id," " formatSOlinenumber(coitem_id) AS linenumber, item_number," " (item_descrip1 || ' ' || item_descrip2) AS itemdescrip," " uom_name," " SUM(shipitem_qty) AS shipitem_qty, " " 'qty' AS shipitem_qty_xtnumericrole " "FROM shiphead JOIN shipitem ON (shipitem_shiphead_id=shiphead_id) " " JOIN coitem ON (coitem_id=shipitem_orderitem_id) " " JOIN itemsite ON (itemsite_id=coitem_itemsite_id) " " JOIN item ON (item_id=itemsite_item_id) " " JOIN uom ON (uom_id=coitem_qty_uom_id) " "WHERE ( (shiphead_id=<? value(\"shiphead_id\") ?>)" " AND (NOT shiphead_shipped) ) " "GROUP BY coitem_id, coitem_linenumber, item_number," " uom_name, itemdescrip, coitem_subnumber " "ORDER BY coitem_linenumber, coitem_subnumber;" "<? elseif exists(\"tohead_id\") ?>" "SELECT toitem_id," " toitem_linenumber AS linenumber, item_number," " (item_descrip1 || ' ' || item_descrip2) AS itemdescrip," " uom_name," " SUM(shipitem_qty) AS shipitem_qty, " " 'qty' AS shipitem_qty_xtnumericrole " "FROM shiphead JOIN shipitem ON (shipitem_shiphead_id=shiphead_id) " " JOIN toitem ON (toitem_id=shipitem_orderitem_id) " " JOIN item ON (item_id=toitem_item_id) " " JOIN uom ON (uom_id=item_inv_uom_id) " "WHERE ( (shiphead_id=<? value(\"shiphead_id\") ?>)" " AND (NOT shiphead_shipped) ) " "GROUP BY toitem_id, toitem_linenumber, item_number," " uom_name, itemdescrip " "ORDER BY toitem_linenumber;" "<? endif ?>" ; MetaSQLQuery itemm(items); shipq = itemm.toQuery(itemp); _coitem->populate(shipq); if (shipq.lastError().type() != QSqlError::NoError) { systemError(this, shipq.lastError().databaseText(), __FILE__, __LINE__); return; } QString vals = "<? if exists(\"sohead_id\") ?>" "SELECT SUM(round((shipitem_qty * coitem_qty_invuomratio) * (coitem_price / coitem_price_invuomratio),2)) AS value " "FROM shiphead JOIN shipitem ON (shipitem_shiphead_id=shiphead_id) " " JOIN coitem ON (coitem_id=shipitem_orderitem_id) " "WHERE ( (shiphead_id=<? value(\"shiphead_id\") ?>)" " AND (NOT shiphead_shipped) );" "<? elseif exists(\"tohead_id\") ?>" "SELECT SUM(toitem_stdcost * shipitem_qty) AS value " "FROM shiphead JOIN shipitem ON (shipitem_shiphead_id=shiphead_id) " " JOIN toitem ON (toitem_id=shipitem_orderitem_id) " "WHERE ( (shiphead_id=<? value(\"shiphead_id\") ?>)" " AND (NOT shiphead_shipped) );" "<? endif ?>" ; MetaSQLQuery valm(vals); shipq = valm.toQuery(itemp); // shared parameters if(shipq.first()) _shipValue->setDouble(shipq.value("value").toDouble()); else if (shipq.lastError().type() != QSqlError::NoError) { systemError(this, shipq.lastError().databaseText(), __FILE__, __LINE__); return; } } else { _billToName->clear(); _shipToName->clear(); _shipToAddr1->clear(); _freight->setEnabled(TRUE); _freight->reset(); _shipVia->clear(); _tracknum->clear(); _shipment->clear(); _shipment->setEnabled(false); } }