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);
}
}
