void creditMemoItem::sPriceUOMChanged()
{
if(_pricingUOM->id() == -1 || _qtyUOM->id() == -1)
return;
if(_pricingUOM->id() == _invuomid)
_priceinvuomratio = 1.0;
else
{
XSqlQuery invuom;
invuom.prepare("SELECT itemuomtouomratio(item_id, :uom_id, item_inv_uom_id) AS ratio"
" FROM item"
" WHERE(item_id=:item_id);");
invuom.bindValue(":item_id", _item->id());
invuom.bindValue(":uom_id", _pricingUOM->id());
invuom.exec();
if(invuom.first())
_priceinvuomratio = invuom.value("ratio").toDouble();
else
systemError(this, invuom.lastError().databaseText(), __FILE__, __LINE__);
}
_ratio->setText(formatUOMRatio(_priceinvuomratio));
updatePriceInfo();
}
void creditMemoItem::sPopulateItemInfo()
{
XSqlQuery uom;
uom.prepare("SELECT uom_id, uom_name"
" FROM item"
" JOIN uom ON (item_inv_uom_id=uom_id)"
" WHERE(item_id=:item_id)"
" UNION "
"SELECT uom_id, uom_name"
" FROM item"
" JOIN itemuomconv ON (itemuomconv_item_id=item_id)"
" JOIN uom ON (itemuomconv_to_uom_id=uom_id)"
" WHERE((itemuomconv_from_uom_id=item_inv_uom_id)"
" AND (item_id=:item_id))"
" UNION "
"SELECT uom_id, uom_name"
" FROM item"
" JOIN itemuomconv ON (itemuomconv_item_id=item_id)"
" JOIN uom ON (itemuomconv_from_uom_id=uom_id)"
" WHERE((itemuomconv_to_uom_id=item_inv_uom_id)"
" AND (item_id=:item_id))"
" ORDER BY uom_name;");
uom.bindValue(":item_id", _item->id());
uom.exec();
_qtyUOM->populate(uom);
_pricingUOM->populate(uom);
XSqlQuery item;
item.prepare( "SELECT item_inv_uom_id, item_price_uom_id,"
" iteminvpricerat(item_id) AS iteminvpricerat, formatUOMRatio(iteminvpricerat(item_id)) AS f_invpricerat,"
" item_listprice, "
" stdCost(item_id) AS f_cost,"
" getItemTaxType(item_id, :taxauth) AS taxtype_id "
" FROM item"
" WHERE (item_id=:item_id);" );
item.bindValue(":item_id", _item->id());
item.exec();
if (item.first())
{
_invuomid = item.value("item_inv_uom_id").toInt();
_priceRatio = item.value("iteminvpricerat").toDouble();
_qtyUOM->setId(item.value("item_inv_uom_id").toInt());
_pricingUOM->setId(item.value("item_price_uom_id").toInt());
_priceinvuomratio = item.value("iteminvpricerat").toDouble();
_qtyinvuomratio = 1.0;
_ratio->setText(item.value("f_invpricerat").toString());
// {_listPrice,_unitCost}->setBaseValue() because they're stored in base
_listPrice->setBaseValue(item.value("item_listprice").toDouble());
_unitCost->setBaseValue(item.value("f_cost").toDouble());
_taxType->setId(item.value("taxtype_id").toInt());
}
else if (item.lastError().type() != QSqlError::None)
{
systemError(this, item.lastError().databaseText(), __FILE__, __LINE__);
return;
}
if (_invoiceNumber != -1)
{
XSqlQuery cmitem;
cmitem.prepare( "SELECT invcitem_warehous_id,"
" invcitem_qty_uom_id, invcitem_qty_invuomratio,"
" invcitem_price_uom_id, invcitem_price_invuomratio,"
" invcitem_billed * invcitem_qty_invuomratio AS f_billed,"
" currToCurr(invchead_curr_id, :curr_id, "
" invcitem_price / invcitem_price_invuomratio, invchead_invcdate) AS invcitem_price_local "
"FROM invchead, invcitem "
"WHERE ( (invcitem_invchead_id=invchead_id)"
" AND (invchead_invcnumber=:invoiceNumber::TEXT)"
" AND (invcitem_item_id=:item_id) ) "
"LIMIT 1;" );
cmitem.bindValue(":invoiceNumber", _invoiceNumber);
cmitem.bindValue(":item_id", _item->id());
cmitem.bindValue(":curr_id", _netUnitPrice->id());
cmitem.exec();
if (cmitem.first())
{
_qtyUOM->setId(cmitem.value("invcitem_qty_uom_id").toInt());
_pricingUOM->setId(cmitem.value("invcitem_price_uom_id").toInt());
_priceinvuomratio = cmitem.value("invcitem_price_invuomratio").toDouble();
_ratio->setText(formatUOMRatio(_priceinvuomratio));
_salePrice->setLocalValue(cmitem.value("invcitem_price_local").toDouble() * _priceinvuomratio);
if (_mode == cNew)
_netUnitPrice->setLocalValue(cmitem.value("invcitem_price_local").toDouble() * _priceinvuomratio);
_warehouse->setId(cmitem.value("invcitem_warehous_id").toInt());
_qtyShippedCache = cmitem.value("f_billed").toDouble();
_qtyShipped->setText(formatQty(cmitem.value("f_billed").toDouble() / _qtyinvuomratio));
}
else if (cmitem.lastError().type() != QSqlError::None)
{
systemError(this, cmitem.lastError().databaseText(), __FILE__, __LINE__);
_salePrice->clear();
return;
}
}
}
void purchaseOrderItem::populate()
{
q.prepare( "SELECT pohead_number, poitem_linenumber, poitem_itemsite_id,"
" poitem_itemsrc_id, poitem_vend_item_number, poitem_vend_item_descrip,"
" poitem_vend_uom, formatUOMRatio(poitem_invvenduomratio) AS invvenduomratio,"
" poitem_invvenduomratio,"
" poitem_expcat_id, poitem_duedate,"
" formatDate(poitem_date_promise) AS f_promisedate,"
" poitem_qty_ordered,"
" formatQty(poitem_qty_received) AS f_qtyreceived,"
" pohead_curr_id, pohead_orderdate, "
" poitem_unitprice,"
" poitem_freight,"
" poitem_unitprice * poitem_qty_ordered AS f_extended,"
" poitem_comments, poitem_prj_id,"
" poitem_bom_rev_id,poitem_boo_rev_id, "
" COALESCE(coitem_prcost, 0.0) AS overrideCost "
"FROM pohead, poitem LEFT OUTER JOIN coitem ON (poitem_soitem_id=coitem_id) "
"WHERE ( (poitem_pohead_id=pohead_id) "
" AND (poitem_id=:poitem_id) );" );
q.bindValue(":poitem_id", _poitemid);
q.exec();
if (q.first())
{
_poNumber->setText(q.value("pohead_number").toString());
_lineNumber->setText(q.value("poitem_linenumber").toString());
_dueDate->setDate(q.value("poitem_duedate").toDate());
_ordered->setText(formatQty(q.value("poitem_qty_ordered").toDouble()));
_received->setText(q.value("f_qtyreceived").toString());
_unitPrice->set(q.value("poitem_unitprice").toDouble(),
q.value("pohead_curr_id").toInt(),
q.value("pohead_orderdate").toDate(), false);
_freight->setLocalValue(q.value("poitem_freight").toDouble());
_extendedPrice->setLocalValue(q.value("f_extended").toDouble());
_notes->setText(q.value("poitem_comments").toString());
_project->setId(q.value("poitem_prj_id").toInt());
if(q.value("overrideCost").toDouble() > 0)
_overriddenUnitPrice = true;
if (q.value("poitem_itemsite_id").toInt() == -1)
{
_nonInventoryItem->setChecked(TRUE);
_expcat->setId(q.value("poitem_expcat_id").toInt());
sPopulateItemSourceInfo(-1);
_vendorItemNumber->setText(q.value("poitem_vend_item_number").toString());
_vendorDescrip->setText(q.value("poitem_vend_item_descrip").toString());
_vendorUOM->setText(q.value("poitem_vend_uom").toString());
}
else
{
_inventoryItem->setChecked(TRUE);
_item->setItemsiteid(q.value("poitem_itemsite_id").toInt());
_bomRevision->setId(q.value("poitem_bom_rev_id").toInt());
_booRevision->setId(q.value("poitem_boo_rev_id").toInt());
sPopulateItemSourceInfo(_item->id());
}
_itemsrcid = q.value("poitem_itemsrc_id").toInt();
_vendorItemNumber->setText(q.value("poitem_vend_item_number").toString());
_vendorDescrip->setText(q.value("poitem_vend_item_descrip").toString());
if (_itemsrcid == -1)
{
_vendorUOM->setText(q.value("poitem_vend_uom").toString());
_invVendorUOMRatio->setText(q.value("invvenduomratio").toString());
_invVendUOMRatio = q.value("poitem_invvenduomratio").toDouble();
}
else
{
q.prepare( "SELECT itemsrc_id, itemsrc_vend_item_number,"
" itemsrc_vend_item_descrip, itemsrc_vend_uom,"
" itemsrc_minordqty, formatQty(itemsrc_minordqty) AS f_minordqty,"
" itemsrc_multordqty, formatQty(itemsrc_multordqty) AS f_multordqty,"
" itemsrc_invvendoruomratio "
"FROM itemsrc "
"WHERE (itemsrc_id=:itemsrc_id);" );
q.bindValue(":itemsrc_id", _itemsrcid);
q.exec();
if (q.first())
{
_vendorItemNumber->setEnabled(FALSE);
_vendorItemNumberList->setEnabled(FALSE);
_vendorDescrip->setEnabled(FALSE);
_vendorUOM->setEnabled(FALSE);
if(_vendorItemNumber->text().isEmpty())
_vendorItemNumber->setText(q.value("itemsrc_vend_item_number").toString());
if(_vendorDescrip->text().isEmpty())
_vendorDescrip->setText(q.value("itemsrc_vend_item_descrip").toString());
_vendorUOM->setText(q.value("itemsrc_vend_uom").toString());
_minOrderQty->setText(q.value("f_minordqty").toString());
_orderQtyMult->setText(q.value("f_multordqty").toString());
_invVendorUOMRatio->setText(formatUOMRatio(q.value("itemsrc_invvendoruomratio").toDouble()));
_invVendUOMRatio = q.value("itemsrc_invvendoruomratio").toDouble();
_minimumOrder = q.value("itemsrc_minordqty").toDouble();
_orderMultiple = q.value("itemsrc_multordqty").toDouble();
}
// ToDo
}
q.prepare( "SELECT DISTINCT char_id, char_name,"
" COALESCE(b.charass_value, (SELECT c.charass_value FROM charass c WHERE ((c.charass_target_type='I') AND (c.charass_target_id=:item_id) AND (c.charass_default) AND (c.charass_char_id=char_id)) LIMIT 1)) AS charass_value"
" FROM charass a, char "
" LEFT OUTER JOIN charass b"
" ON (b.charass_target_type='PI'"
" AND b.charass_target_id=:poitem_id"
" AND b.charass_char_id=char_id) "
" WHERE ( (a.charass_char_id=char_id)"
" AND (a.charass_target_type='I')"
" AND (a.charass_target_id=:item_id) ) "
" ORDER BY char_name;" );
q.bindValue(":item_id", _item->id());
q.bindValue(":poitem_id", _poitemid);
q.exec();
int row = 0;
QModelIndex idx;
while(q.next())
{
_itemchar->insertRow(_itemchar->rowCount());
idx = _itemchar->index(row, 0);
_itemchar->setData(idx, q.value("char_name"), Qt::DisplayRole);
_itemchar->setData(idx, q.value("char_id"), Qt::UserRole);
idx = _itemchar->index(row, 1);
_itemchar->setData(idx, q.value("charass_value"), Qt::DisplayRole);
_itemchar->setData(idx, _item->id(), Qt::UserRole);
row++;
}
_comments->setId(_poitemid);
}
}
void purchaseOrderItem::sPopulateItemSourceInfo(int pItemid)
{
bool skipClear = false;
_itemchar->removeRows(0, _itemchar->rowCount());
if (_mode == cNew)
{
if (pItemid != -1)
{
if(_metrics->boolean("RequireStdCostForPOItem"))
{
q.prepare("SELECT stdCost(:item_id) AS result");
q.bindValue(":item_id", pItemid);
q.exec();
if(q.first() && q.value("result").toDouble() == 0.0)
{
QMessageBox::critical( this, tr("Selected Item Missing Cost"),
tr("<p>The selected item has no Std. Costing information. "
"Please see your controller to correct this situation "
"before continuing."));
_item->setId(-1);
return;
}
}
q.prepare( "SELECT itemsrc_id, itemsrc_vend_item_number,"
" itemsrc_vend_item_descrip, itemsrc_vend_uom,"
" itemsrc_minordqty, formatQty(itemsrc_minordqty) AS f_minordqty,"
" itemsrc_multordqty, formatQty(itemsrc_multordqty) AS f_multordqty,"
" itemsrc_invvendoruomratio,"
" (CURRENT_DATE + itemsrc_leadtime) AS earliestdate "
"FROM pohead, itemsrc "
"WHERE ( (itemsrc_vend_id=pohead_vend_id)"
" AND (itemsrc_item_id=:item_id)"
" AND (pohead_id=:pohead_id) );" );
q.bindValue(":item_id", pItemid);
q.bindValue(":pohead_id", _poheadid);
q.exec();
if (q.first())
{
_itemsrcid = q.value("itemsrc_id").toInt();
_vendorItemNumber->setText(q.value("itemsrc_vend_item_number").toString());
_vendorDescrip->setText(q.value("itemsrc_vend_item_descrip").toString());
_vendorUOM->setText(q.value("itemsrc_vend_uom").toString());
_minOrderQty->setText(q.value("f_minordqty").toString());
_orderQtyMult->setText(q.value("f_multordqty").toString());
_invVendorUOMRatio->setText(formatUOMRatio(q.value("itemsrc_invvendoruomratio").toDouble()));
_earliestDate->setDate(q.value("earliestdate").toDate());
_invVendUOMRatio = q.value("itemsrc_invvendoruomratio").toDouble();
_minimumOrder = q.value("itemsrc_minordqty").toDouble();
_orderMultiple = q.value("itemsrc_multordqty").toDouble();
if (_ordered->toDouble() != 0)
sDeterminePrice();
_ordered->setFocus();
if(_metrics->boolean("UseEarliestAvailDateOnPOItem"))
_dueDate->setDate(_earliestDate->date());
skipClear = true;
}
}
if(!skipClear)
{
_itemsrcid = -1;
_vendorItemNumber->clear();
_vendorDescrip->clear();
_vendorUOM->setText(_item->uom());
_minOrderQty->clear();
_orderQtyMult->clear();
_invVendorUOMRatio->setText("1.0");
_earliestDate->setDate(omfgThis->dbDate());
_invVendUOMRatio = 1;
_minimumOrder = 0;
_orderMultiple = 0;
}
q.prepare( "SELECT DISTINCT char_id, char_name,"
" COALESCE(b.charass_value, (SELECT c.charass_value FROM charass c WHERE ((c.charass_target_type='I') AND (c.charass_target_id=:item_id) AND (c.charass_default) AND (c.charass_char_id=char_id)) LIMIT 1)) AS charass_value"
" FROM charass a, char "
" LEFT OUTER JOIN charass b"
" ON (b.charass_target_type='PI'"
" AND b.charass_target_id=:poitem_id"
" AND b.charass_char_id=char_id) "
" WHERE ( (a.charass_char_id=char_id)"
" AND (a.charass_target_type='I')"
" AND (a.charass_target_id=:item_id) ) "
" ORDER BY char_name;" );
q.bindValue(":item_id", pItemid);
q.bindValue(":poitem_id", _poitemid);
q.exec();
int row = 0;
QModelIndex idx;
while(q.next())
{
_itemchar->insertRow(_itemchar->rowCount());
idx = _itemchar->index(row, 0);
_itemchar->setData(idx, q.value("char_name"), Qt::DisplayRole);
_itemchar->setData(idx, q.value("char_id"), Qt::UserRole);
idx = _itemchar->index(row, 1);
_itemchar->setData(idx, q.value("charass_value"), Qt::DisplayRole);
_itemchar->setData(idx, pItemid, Qt::UserRole);
row++;
}
}
}
/*
Note that the SELECTs here are UNIONs of the gltrans table (in the base
currency), sltrans table (in the base currency) and the bankadj table
(in the bank account's currency).
*/
void reconcileBankaccount::populate()
{
qApp->setOverrideCursor(QCursor(Qt::WaitCursor));
double begBal = _openBal->localValue();
double endBal = _endBal->localValue();
int currid = -1;
ParameterList params;
params.append("bankaccntid", _bankaccnt->id());
params.append("bankrecid", _bankrecid);
// fill receipts list
currid = _receipts->id();
_receipts->clear();
MetaSQLQuery mrcp = mqlLoad("bankrec", "receipts");
XSqlQuery rcp = mrcp.toQuery(params);
if (rcp.lastError().type() != QSqlError::NoError)
{
systemError(this, rcp.lastError().databaseText(), __FILE__, __LINE__);
return;
}
int jrnlnum = 0;
XTreeWidgetItem * parent = 0;
XTreeWidgetItem * lastChild = 0;
XTreeWidgetItem * last = 0;
bool cleared = TRUE;
double amount = 0.0;
bool amountNull = true;
while (rcp.next())
{
if(rcp.value("use").toString() == "C/R")
{
if(rcp.value("jrnlnum").toInt() != jrnlnum || (0 == parent))
{
if(parent != 0)
{
parent->setText(0, (cleared ? tr("Yes") : tr("No")));
parent->setText(8, amountNull ? tr("?????") : formatMoney(amount));
}
jrnlnum = rcp.value("jrnlnum").toInt();
last = new XTreeWidgetItem( _receipts, last,
jrnlnum, 9, "", formatDate(rcp.value("f_jrnldate").toDate()), tr("JS"), rcp.value("jrnlnum"));
parent = last;
cleared = true;
amount = 0.0;
amountNull = true;
lastChild = 0;
}
cleared = (cleared && rcp.value("cleared").toBool());
amount += rcp.value("amount").toDouble();
amountNull = rcp.value("amount").isNull();
lastChild = new XTreeWidgetItem( parent, lastChild,
rcp.value("id").toInt(), rcp.value("altid").toInt(),
(rcp.value("cleared").toBool() ? tr("Yes") : tr("No")),
formatDate(rcp.value("f_date").toDate()), rcp.value("doc_type"), rcp.value("docnumber"),
rcp.value("notes"),
rcp.value("doc_curr"),
rcp.value("doc_exchrate").isNull() ? tr("?????") : formatUOMRatio(rcp.value("doc_exchrate").toDouble()),
rcp.value("base_amount").isNull() ? tr("?????") : formatMoney(rcp.value("base_amount").toDouble()),
rcp.value("amount").isNull() ? tr("?????") : formatMoney(rcp.value("amount").toDouble()) );
}
else
{
if(parent != 0)
{
parent->setText(0, (cleared ? tr("Yes") : tr("No")));
parent->setText(8, formatMoney(amount));
}
parent = 0;
cleared = true;
amount = 0.0;
amountNull = true;
lastChild = 0;
last = new XTreeWidgetItem( _receipts, last,
rcp.value("id").toInt(), rcp.value("altid").toInt(),
(rcp.value("cleared").toBool() ? tr("Yes") : tr("No")),
formatDate(rcp.value("f_date").toDate()), rcp.value("doc_type"), rcp.value("docnumber"),
rcp.value("notes"),
rcp.value("doc_curr"),
rcp.value("doc_exchrate").isNull() ? tr("?????") : formatUOMRatio(rcp.value("doc_exchrate").toDouble()),
rcp.value("base_amount").isNull() ? tr("?????") : formatMoney(rcp.value("base_amount").toDouble()),
rcp.value("amount").isNull() ? tr("?????") : formatMoney(rcp.value("amount").toDouble()) );
}
}
if(parent != 0)
{
parent->setText(0, (cleared ? tr("Yes") : tr("No")));
parent->setText(8, amountNull ? tr("?????") : formatMoney(amount));
}
if(currid != -1)
_receipts->setCurrentItem(_receipts->topLevelItem(currid));
if(_receipts->currentItem())
_receipts->scrollToItem(_receipts->currentItem());
// fill checks list
currid = _checks->id();
_checks->clear();
MetaSQLQuery mchk = mqlLoad("bankrec", "checks");
XSqlQuery chk = mchk.toQuery(params);
if (chk.lastError().type() != QSqlError::NoError)
{
systemError(this, chk.lastError().databaseText(), __FILE__, __LINE__);
return;
}
_checks->populate(chk, TRUE);
if(currid != -1)
_checks->setCurrentItem(_checks->topLevelItem(currid));
if(_checks->currentItem())
_checks->scrollToItem(_checks->currentItem());
params.append("summary", true);
// fill receipts cleared value
rcp = mrcp.toQuery(params);
if (rcp.first())
_clearedReceipts->setDouble(rcp.value("cleared_amount").toDouble());
else if (rcp.lastError().type() != QSqlError::NoError)
{
systemError(this, rcp.lastError().databaseText(), __FILE__, __LINE__);
return;
}
// fill checks cleared value
chk = mchk.toQuery(params);
if (chk.first())
_clearedChecks->setDouble(chk.value("cleared_amount").toDouble());
else if (chk.lastError().type() != QSqlError::NoError)
{
systemError(this, chk.lastError().databaseText(), __FILE__, __LINE__);
return;
}
// calculate cleared balance
MetaSQLQuery mbal = mqlLoad("bankrec", "clearedbalance");
params.append("endBal", endBal);
params.append("begBal", begBal);
params.append("curr_id", _currency->id());
params.append("effective", _startDate->date());
params.append("expires", _endDate->date());
XSqlQuery bal = mbal.toQuery(params);
bool enableRec = FALSE;
if(bal.first())
{
_clearBal->setDouble(bal.value("cleared_amount").toDouble());
_endBal2->setDouble(bal.value("end_amount").toDouble());
_diffBal->setDouble(bal.value("diff_amount").toDouble());
QString stylesheet;
if(bal.value("diff_value").toDouble() == 0.0)
{
if(_startDate->isValid() && _endDate->isValid())
enableRec = TRUE;
}
else
stylesheet = QString("* { color: %1; }").arg(namedColor("error").name());
_diffBal->setStyleSheet(stylesheet);
}
else if (bal.lastError().type() != QSqlError::NoError)
{
systemError(this, bal.lastError().databaseText(), __FILE__, __LINE__);
return;
}
//_reconcile->setEnabled(enableRec);
qApp->restoreOverrideCursor();
}
