void dspAROpenItemsByCustomer::sFillList()
{
_aropen->clear();
q.prepare( "SELECT aropen_id, aropen_docnumber, aropen_ordernumber,"
" CASE WHEN (aropen_doctype='C') THEN :creditMemo"
" WHEN (aropen_doctype='D') THEN :debitMemo"
" WHEN (aropen_doctype='I') THEN :invoice"
" WHEN (aropen_doctype='R') THEN :cashdeposit"
" ELSE :other"
" END AS f_doctype,"
" formatDate(aropen_docdate) AS f_docdate,"
" formatDate(aropen_duedate) AS f_duedate,"
" formatMoney(aropen_amount) AS f_amount,"
" formatMoney(aropen_paid) AS f_paid,"
" CASE WHEN (aropen_doctype IN ('C', 'R')) THEN ((aropen_amount - aropen_paid) * -1)"
" WHEN (aropen_doctype IN ('I', 'D')) THEN (aropen_amount - aropen_paid)"
" ELSE (aropen_amount - aropen_paid)"
" END AS balance,"
" currConcat(aropen_curr_id) AS currAbbr,"
" currToBase(aropen_curr_id,"
" CASE WHEN (aropen_doctype IN ('C', 'R')) THEN ((aropen_amount - aropen_paid) * -1)"
" WHEN (aropen_doctype IN ('I', 'D')) THEN (aropen_amount - aropen_paid)"
" ELSE (aropen_amount - aropen_paid)"
" END, aropen_docdate) AS base_balance "
" FROM aropen "
" WHERE ( (aropen_open)"
" AND (aropen_cust_id=:cust_id) "
" AND (aropen_duedate BETWEEN :startDate AND :endDate) ) "
" ORDER BY aropen_docdate;" );
_dates->bindValue(q);
q.bindValue(":cust_id", _cust->id());
q.bindValue(":creditMemo", tr("C/M"));
q.bindValue(":debitMemo", tr("D/M"));
q.bindValue(":invoice", tr("Invoice"));
q.bindValue(":cashdeposit", tr("C/D"));
q.exec();
if (q.first())
{
double total= 0.0;
XTreeWidgetItem * last = 0;
do
{
last = new XTreeWidgetItem( _aropen, last, q.value("aropen_id").toInt(),
q.value("f_doctype"), q.value("aropen_docnumber"),
q.value("aropen_ordernumber"), q.value("f_docdate"),
q.value("f_duedate"), q.value("f_amount"),
q.value("f_paid"), formatMoney(q.value("balance").toDouble()),
q.value("currAbbr"),
formatMoney(q.value("base_balance").toDouble()));
total += q.value("base_balance").toDouble();
}
while (q.next());
new XTreeWidgetItem( _aropen, last, -1,
QVariant(tr("Total")), "", "", "", "", "", "", "", "",
formatMoney(total) );
}
}
void selectedPayments::sFillList()
{
_apselect->clear();
QString sql( "SELECT apopen_id, apselect_id,"
" (bankaccnt_name || '-' || bankaccnt_descrip) AS f_bank,"
" (vend_number || '-' || vend_name) AS f_vendor,"
" CASE WHEN (apopen_doctype='V') THEN :voucher"
" When (apopen_doctype='D') THEN :debitMemo"
" END AS doctype,"
" apopen_docnumber, apopen_ponumber, apselect_amount,"
" apopen_invcnumber,"
" currToBase(apselect_curr_id, apselect_amount, "
" CURRENT_DATE) AS apselect_amount_base, "
" currConcat(apselect_curr_id) AS currAbbr "
"FROM apopen, apselect, vend, bankaccnt "
"WHERE ( (apopen_vend_id=vend_id)"
" AND (apselect_apopen_id=apopen_id)"
" AND (apselect_bankaccnt_id=bankaccnt_id)"
"<? if exists(\"bankaccntid\") ?>"
" AND (bankaccnt_id=<? value(\"bankaccntid\") ?>)"
"<? endif ?>"
" ) "
"ORDER BY bankaccnt_name, vend_number, apopen_docnumber;" );
ParameterList params;
setParams(params);
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
XTreeWidgetItem *last = 0;
double running = 0;
while (q.next())
{
running += q.value("apselect_amount_base").toDouble();
last = new XTreeWidgetItem(_apselect, last,
q.value("apopen_id").toInt(),
q.value("apselect_id").toInt(),
q.value("f_bank"),
q.value("f_vendor"),
q.value("doctype"),
q.value("apopen_docnumber"),
q.value("apopen_invcnumber"),
q.value("apopen_ponumber"),
formatMoney(q.value("apselect_amount").toDouble()),
q.value("currAbbr"),
formatMoney(running) );
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void changePoitemQty::sPopulate(int pPoitemid)
{
if (pPoitemid == -1)
{
_currentQtyReceived->clear();
_newQtyReceived->clear();
_currentQtyOrdered->clear();
_newQty->clear();
_currentQtyBalance->clear();
_newQtyBalance->clear();
_freight->clear();
}
else
{
q.prepare( "SELECT poitem_qty_ordered, poitem_qty_received, poitem_qty_returned,"
" poitem_freight "
"FROM poitem "
"WHERE (poitem_id=:poitem_id);" );
q.bindValue(":poitem_id", pPoitemid);
q.exec();
if (q.first())
{
_currentQtyOrdered->setText(formatQty(q.value("poitem_qty_ordered").toDouble()));
_currentQtyReceived->setText(formatQty(q.value("poitem_qty_received").toDouble()));
_currentQtyBalance->setText(formatQty((q.value("poitem_qty_ordered").toDouble() - q.value("poitem_qty_received").toDouble())));
_newQtyReceived->setText(formatQty(q.value("poitem_qty_received").toDouble()));
_cacheFreight = q.value("poitem_freight").toDouble();
_freight->setText(formatMoney(q.value("poitem_freight").toDouble()));
sQtyChanged();
}
}
}
void dspBriefSalesHistoryByCustomerType::sFillList()
{
_sohist->clear();
if (!checkParameters())
return;
QString sql( "SELECT custtype_id, custtype_code, cust_name,"
" cohist_ordernumber, cohist_invcnumber,"
" formatDate(cohist_orderdate) AS f_orderdate,"
" formatDate(cohist_invcdate) AS f_invcdate,"
" SUM(round(cohist_qtyshipped * cohist_unitprice,2)) AS extended,"
" formatMoney(SUM(round(cohist_qtyshipped * cohist_unitprice,2))) AS f_extended "
"FROM cohist, cust, custtype, itemsite, item "
"WHERE ( (cohist_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (cohist_cust_id=cust_id)"
" AND (cust_custtype_id=custtype_id)"
" AND (cohist_invcdate BETWEEN :startDate AND :endDate)" );
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_customerType->isSelected())
sql += " AND (custtype_id=:custtype_id)";
else if (_customerType->isPattern())
sql += " AND (custtype_code ~ :custtype_pattern)";
sql += ") "
"GROUP BY custtype_id, custtype_code, cust_name,"
" cohist_ordernumber, cohist_invcnumber,"
" cohist_orderdate, cohist_invcdate "
"ORDER BY cohist_invcdate, cohist_orderdate;";
q.prepare(sql);
_dates->bindValue(q);
_warehouse->bindValue(q);
_customerType->bindValue(q);
q.exec();
if (q.first())
{
double totalSales = 0.0;
do
{
new XListViewItem( _sohist, _sohist->lastItem(), q.value("custtype_id").toInt(),
q.value("custtype_code"), q.value("cust_name"),
q.value("cohist_ordernumber"), q.value("cohist_invcnumber"),
q.value("f_orderdate"), q.value("f_invcdate"),
q.value("f_extended") );
totalSales += q.value("extended").toDouble();
}
while (q.next());
new XListViewItem( _sohist, _sohist->lastItem(), -1,
"", "", "", "", "", tr("Totals"),
formatMoney(totalSales) );
}
}
void dspSummarizedSalesByCustomerType::sFillList()
{
_sohist->clear();
if (!checkParameters())
return;
QString sql( "SELECT custtype_id, custtype_code, warehous_code,"
" formatSalesPrice(minprice) AS f_minprice,"
" formatSalesPrice(maxprice) AS f_maxprice,"
" formatSalesPrice(avgprice) AS f_avgprice,"
" formatQty(totalunits) AS f_totalunits,"
" formatQty(totalsales) AS f_totalsales, totalsales "
"FROM ( SELECT custtype_id, custtype_code, warehous_code,"
" MIN(cohist_unitprice) AS minprice, MAX(cohist_unitprice) AS maxprice,"
" AVG(cohist_unitprice) AS avgprice, SUM(cohist_qtyshipped) AS totalunits,"
" SUM(round(cohist_qtyshipped * cohist_unitprice,2)) AS totalsales "
" FROM cohist, cust, custtype, itemsite, item, warehous "
" WHERE ( (cohist_cust_id=cust_id)"
" AND (cust_custtype_id=custtype_id)"
" AND (cohist_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (cohist_invcdate BETWEEN :startDate AND :endDate)" );
if (_warehouse->isSelected())
sql += "AND (warehous_id=:warehous_id)";
if (_customerType->isSelected())
sql += " AND (custtype_id=:custtype_id)";
else if (_customerType->isPattern())
sql += " AND (custtype_code ~ :custtype_pattern)";
sql += ") "
"GROUP BY custtype_id, custtype_code, warehous_code ) AS data "
"ORDER BY custtype_code, warehous_code;";
q.prepare(sql);
_warehouse->bindValue(q);
_customerType->bindValue(q);
_dates->bindValue(q);
q.exec();
_sohist->populate(q);
if (q.first())
{
double totalSales = 0.0;
do
totalSales += q.value("totalsales").toDouble();
while (q.next());
new XTreeWidgetItem(_sohist,
_sohist->topLevelItem(_sohist->topLevelItemCount() - 1),
-1,
QVariant(tr("Totals")), "", "", "", "", "",
formatMoney(totalSales) );
}
}
void dspBriefSalesHistoryByCustomer::sFillList()
{
_sohist->clear();
if (!checkParameters())
return;
QString sql( "SELECT cohist_ordernumber, cohist_ponumber, cohist_invcnumber,"
" formatDate(cohist_orderdate) AS f_orderdate,"
" formatDate(cohist_invcdate, 'Return') AS f_invcdate,"
" SUM(round(cohist_qtyshipped * cohist_unitprice,2)) AS extended,"
" formatMoney(SUM(round(cohist_qtyshipped * cohist_unitprice,2))) AS f_extended "
"FROM cohist, itemsite, item "
"WHERE ( (cohist_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (cohist_invcdate BETWEEN :startDate AND :endDate)"
" AND (cohist_cust_id=:cust_id)" );
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_productCategory->isSelected())
sql += " AND (item_prodcat_id=:prodcat_id)";
else if (_productCategory->isPattern())
sql += " AND (item_prodcat_id IN (SELECT prodcat_id FROM prodcat WHERE (prodcat_code ~ :prodcat_pattern)))";
sql += ") "
"GROUP BY cohist_ordernumber, cohist_ponumber, cohist_invcnumber,"
" cohist_orderdate, cohist_invcdate "
"ORDER BY cohist_invcdate, cohist_orderdate;";
q.prepare(sql);
_dates->bindValue(q);
q.bindValue(":cust_id", _cust->id());
_warehouse->bindValue(q);
_productCategory->bindValue(q);
q.exec();
if (q.first())
{
double totalSales = 0.0;
XTreeWidgetItem *last = 0;
do
{
last = new XTreeWidgetItem( _sohist, last, -1,
q.value("cohist_ordernumber"), q.value("cohist_ponumber"),
q.value("cohist_invcnumber"), q.value("f_orderdate"),
q.value("f_invcdate"), q.value("f_extended") );
totalSales += q.value("extended").toDouble();
}
while (q.next());
XTreeWidgetItem *totals = new XTreeWidgetItem(_sohist, last, -1, QVariant(tr("Totals")));
totals->setText(5, formatMoney(totalSales));
}
}
void dspBacklogByItem::sFillList()
{
_soitem->clear();
if (_item->isValid())
{
MetaSQLQuery mql = mqlLoad(":/so/displays/SalesOrderItems.mql");
ParameterList params;
_dates->appendValue(params);
_warehouse->appendValue(params);
params.append("item_id", _item->id());
params.append("openOnly");
params.append("orderByScheddate");
q = mql.toQuery(params);
XTreeWidgetItem *last = 0;
double totalBacklog = 0.0;
while (q.next())
{
last = new XTreeWidgetItem(_soitem, last,
q.value("cohead_id").toInt(),
q.value("coitem_id").toInt(),
q.value("cohead_number"),
q.value("coitem_linenumber"),
q.value("cust_name"),
formatDate(q.value("cohead_orderdate").toDate()),
formatDate(q.value("coitem_scheddate").toDate()),
q.value("uom_name"),
formatQty(q.value("coitem_qtyord").toDouble()),
formatQty(q.value("coitem_qtyshipped").toDouble()),
formatQty(q.value("qtybalance").toDouble()),
formatMoney(q.value("baseextpricebalance").toDouble()) );
totalBacklog += q.value("baseextpricebalance").toDouble();
}
if (_showPrices->isChecked())
{
last = new XTreeWidgetItem(_soitem, last, -1, -1,
"", "", tr("Total Backlog"), "", "", "", "", "",
formatMoney(totalBacklog) );
}
}
else
_soitem->clear();
}
void dspInvoiceRegister::sFillList()
{
display::sFillList();
list()->expandAll();
// calculate subtotals and grand total for debit and credit columns and add rows for them
double debittotal = 0.0;
double credittotal = 0.0;
for (int i = 0; i < list()->topLevelItemCount(); i++)
{
double debitsum = 0.0;
double creditsum = 0.0;
XTreeWidgetItem *item = 0;
for (int j = 0; j < list()->topLevelItem(i)->childCount(); j++)
{
item = list()->topLevelItem(i)->child(j);
// qDebug("in loop @ %d %p", j, item);
if (item)
{
debitsum += item->rawValue("debit").toDouble();
creditsum += item->rawValue("credit").toDouble();
debittotal += item->rawValue("debit").toDouble();
credittotal += item->rawValue("credit").toDouble();
}
}
if (item)
{
// qDebug("adding subtotal %p", item);
item = new XTreeWidgetItem(list()->topLevelItem(i), -1, -1, tr("Subtotal"));
item->setData(list()->column("debit"), Qt::EditRole, formatMoney(debitsum));
item->setData(list()->column("credit"), Qt::EditRole, formatMoney(creditsum));
}
}
XTreeWidgetItem *item = new XTreeWidgetItem(list(), -1, -1, tr("Total"));
item->setData(list()->column("debit"), Qt::EditRole, formatMoney(debittotal));
item->setData(list()->column("credit"), Qt::EditRole, formatMoney(credittotal));
}
void dspBookingsByProductCategory::sFillList()
{
if (!checkParameters())
return;
_soitem->clear();
MetaSQLQuery mql = mqlLoad(":/so/displays/SalesOrderItems.mql");
ParameterList params;
_dates->appendValue(params);
_warehouse->appendValue(params);
_productCategory->appendValue(params);
params.append("orderByOrderdate");
q = mql.toQuery(params);
XTreeWidgetItem *last = 0;
bool exchangeError = false;
while (q.next())
{
if (q.value("baseunitprice").toDouble() < 0.0)
exchangeError = true;
last = new XTreeWidgetItem(_soitem, last,
q.value("coitem_id").toInt(),
q.value("cohead_number"),
formatDate(q.value("cohead_orderdate").toDate()),
q.value("cust_number"),
q.value("cust_name"),
q.value("item_number"),
formatQty(q.value("coitem_qtyord").toDouble()),
formatSalesPrice(q.value("baseunitprice").toDouble()),
formatMoney(q.value("baseextprice").toDouble()) );
}
if (exchangeError)
QMessageBox::warning( this, tr("Currency Exchange Rate Error"),
tr("One or more of the Prices could not be converted to Base Currency.\n"
"These Prices have been set to a negative value.") );
}
void dspSummarizedGLTransactions::sFillList()
{
_gltrans->clear();
QString sql( "SELECT gltrans_id, accnt_id,"
" formatGLAccount(accnt_id) AS account,"
" accnt_descrip,"
" formatDate(gltrans_date) AS transdate,"
" gltrans_source, gltrans_doctype, gltrans_docnumber,"
" firstLine(gltrans_notes) AS f_notes,"
" CASE WHEN (gltrans_amount < 0) THEN (gltrans_amount * -1)"
" ELSE 0"
" END AS debit,"
" CASE WHEN (gltrans_amount > 0) THEN gltrans_amount"
" ELSE 0"
" END AS credit,"
" gltrans_username "
"FROM gltrans, accnt "
"WHERE ( (gltrans_accnt_id=accnt_id)"
" AND (gltrans_date BETWEEN :startDate AND :endDate) ");
if (_selectedSource->isChecked())
sql += " AND (gltrans_source=:source)";
if (_unpostedTransactions->isChecked())
sql += " AND (NOT gltrans_posted)";
else if (_postedTransactions->isChecked())
sql += " AND (gltrans_posted)";
sql += ") "
"ORDER BY accnt_id, gltrans_date, gltrans_created;";
q.prepare(sql);
_dates->bindValue(q);
q.bindValue(":source", _source->currentText());
q.exec();
if (q.first())
{
XListViewItem *header = NULL;
int accntid = -1;
double debits = 0.0;
double credits = 0.0;
do
{
if (accntid != q.value("accnt_id").toInt())
{
if (header)
{
if (debits > 0)
{
header->setText(5, formatMoney(debits));
debits = 0;
}
if (credits > 0)
{
header->setText(6, formatMoney(credits));
credits = 0;
}
}
accntid = q.value("accnt_id").toInt();
header = new XListViewItem( _gltrans, _gltrans->lastItem(), accntid,
q.value("account"), q.value("accnt_descrip") );
}
XListViewItem *item = new XListViewItem( header,
accntid, q.value("gltrans_id").toInt(),
q.value("transdate"), q.value("f_notes"),
q.value("gltrans_source"), q.value("gltrans_doctype"),
q.value("gltrans_docnumber"), "", "",
q.value("gltrans_username") );
if (q.value("debit") != 0.0)
{
debits += q.value("debit").toDouble();
item->setText(5, formatMoney(q.value("debit").toDouble()));
}
if (q.value("credit") != 0.0)
{
credits += q.value("credit").toDouble();
item->setText(6, formatMoney(q.value("credit").toDouble()));
}
}
while (q.next());
if (header)
{
if (debits > 0)
header->setText(5, formatMoney(debits));
if (credits > 0)
header->setText(6, formatMoney(credits));
}
}
}
void dspSalesHistoryByCustomer::sFillList()
{
_sohist->clear();
if (!checkParameters())
return;
QString sql( "SELECT cohist_id,"
" cohist_ordernumber, cohist_invcnumber,"
" formatDate(cohist_orderdate) AS f_orderdate,"
" formatDate(cohist_invcdate, 'Return') AS f_invcdate,"
" item_number, (item_descrip1 || ' ' || item_descrip2) AS description,"
" formatQty(cohist_qtyshipped) AS f_shipped " );
sql += ", formatSalesPrice(cohist_unitprice) AS f_unitprice,"
" round((cohist_qtyshipped * cohist_unitprice),2) AS extprice,"
" formatMoney(round(cohist_qtyshipped * cohist_unitprice,2)) AS f_extprice ";
sql += ", formatSalesPrice(cohist_unitcost) AS f_unitcost,"
" (cohist_qtyshipped * cohist_unitcost) AS extcost,"
" formatMoney(cohist_qtyshipped * cohist_unitcost) AS f_extcost ";
sql += "FROM cohist, itemsite, item, prodcat "
"WHERE ( (cohist_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (item_prodcat_id=prodcat_id)"
" AND (cohist_invcdate BETWEEN :startDate AND :endDate)"
" AND (cohist_cust_id=:cust_id)";
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_productCategory->isSelected())
sql += " AND (prodcat_id=:prodcat_id)";
else if (_productCategory->isPattern())
sql += " AND (prodcat_code ~ :prodcat_pattern)";
sql += " ) "
"ORDER BY cohist_invcdate, item_number;";
q.prepare(sql);
_warehouse->bindValue(q);
_productCategory->bindValue(q);
_dates->bindValue(q);
q.bindValue(":cust_id", _cust->id());
q.exec();
XTreeWidgetItem *last = 0;
if (q.first())
{
double totalSales = 0.0;
double totalCosts = 0.0;
do
{
last = new XTreeWidgetItem( _sohist, last,
q.value("cohist_id").toInt(),
q.value("cohist_invcnumber").toInt(),
q.value("cohist_ordernumber"),
q.value("cohist_invcnumber"),
q.value("f_orderdate"),
q.value("f_invcdate"),
q.value("item_number"),
q.value("description"),
q.value("f_shipped"),
q.value("f_unitprice"),
q.value("f_extprice"),
q.value("f_unitcost"),
q.value("f_extcost"));
totalSales += q.value("extprice").toDouble();
totalCosts += q.value("extcost").toDouble();
}
while (q.next());
if ( (_showPrices->isChecked()) || (_showCosts->isChecked()) )
{
XTreeWidgetItem *totals = new XTreeWidgetItem(_sohist, last, -1);
totals->setText(5, tr("Total"));
totals->setText(EXTPRICE_COL, formatMoney(totalSales));
totals->setText(EXTCOST_COL, formatCost(totalCosts));
}
}
}
const QString PivotCell::formatMoney(int fraction, bool showThousandSeparator) const
{
return formatMoney("", MyMoneyMoney::denomToPrec(fraction), showThousandSeparator);
}
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());
}
}
}
void dspSalesHistoryByShipTo::sFillList()
{
if (!checkParameters())
return;
_sohist->clear();
MetaSQLQuery mql = mqlLoad(":/so/displays/SalesHistory.mql");
ParameterList params;
_dates->appendValue(params);
_warehouse->appendValue(params);
_productCategory->appendValue(params);
params.append("shipto_id", _shipTo->id());
params.append("orderByInvcdateItem");
q = mql.toQuery(params);
if (q.first())
{
double totalUnits = 0.0;
double totalSales = 0.0;
double totalCosts = 0.0;
bool exchangeError = false;
XTreeWidgetItem *last = 0;
do
{
if (q.value("baseunitprice").toDouble() < 0.0)
exchangeError = true;
QString invoicedate = tr("Return");
if (q.value("cohist_invcdate").toString() != "")
invoicedate = formatDate(q.value("cohist_invcdate").toDate());
last = new XTreeWidgetItem( _sohist, last, q.value("cohist_id").toInt(),
q.value("cohist_ordernumber"),
q.value("invoicenumber"),
formatDate(q.value("cohist_orderdate").toDate()),
invoicedate,
q.value("item_number"),
q.value("itemdescription"),
formatQty(q.value("cohist_qtyshipped").toDouble()),
formatSalesPrice(q.value("baseunitprice").toDouble()),
formatMoney(q.value("baseextprice").toDouble()),
formatCost(q.value("cohist_unitcost").toDouble()),
formatMoney(q.value("extcost").toDouble()) );
totalUnits += q.value("cohist_qtyshipped").toDouble();
totalSales += q.value("baseextprice").toDouble();
totalCosts += q.value("extcost").toDouble();
}
while (q.next());
XTreeWidgetItem *totals = new XTreeWidgetItem(_sohist, last, -1);
totals->setText(5, tr("Totals"));
totals->setText(6, formatQty(totalUnits));
totals->setText(EXTPRICE_COL, formatMoney(totalSales));
totals->setText(EXTCOST_COL, formatCost(totalCosts));
if ( (exchangeError) && (_showPrices->isChecked()) )
QMessageBox::warning( this, tr("Currency Exchange Rate Error"),
tr("One or more of the Prices could not be converted to Base Currency.\n"
"These Prices have been set to a negative value.") );
}
}
void dspSummarizedSalesByCustomerTypeByItem::sFillList()
{
_sohist->clear();
if (!checkParameters())
return;
QString sql( "SELECT itemsite_id, item_number, item_descrip, warehous_code,"
" formatSalesPrice(minprice) AS f_minprice,"
" formatSalesPrice(maxprice) AS f_maxprice,"
" formatSalesPrice(avgprice) AS f_avgprice,"
" formatQty(totalunits) AS f_totalunits,"
" totalsales, formatMoney(totalsales) AS f_totalsales "
"FROM ( SELECT itemsite_id, item_number, (item_descrip1 || ' ' || item_descrip2) AS item_descrip,"
" warehous_code, MIN(cohist_unitprice) AS minprice, MAX(cohist_unitprice) AS maxprice,"
" AVG(cohist_unitprice) AS avgprice, SUM(cohist_qtyshipped) AS totalunits,"
" SUM(round(cohist_qtyshipped * cohist_unitprice,2)) AS totalsales "
" FROM cohist, cust, custtype, itemsite, item, warehous "
" WHERE ( (cohist_cust_id=cust_id)"
" AND (cust_custtype_id=custtype_id)"
" AND (cohist_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (cohist_invcdate BETWEEN :startDate AND :endDate)" );
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_customerType->isSelected())
sql += " AND (custtype_id=:custtype_id)";
else if (_customerType->isPattern())
sql += " AND (custtype_code ~ :custtype_pattern)";
sql += ") "
"GROUP BY itemsite_id, item_number, item_descrip1, item_descrip2, warehous_code ) AS data "
"ORDER BY item_number, warehous_code;";
q.prepare(sql);
_warehouse->bindValue(q);
_customerType->bindValue(q);
_dates->bindValue(q);
q.exec();
if (q.first())
{
double totalSales = 0.0;
XTreeWidgetItem *last = 0;
do
{
last = new XTreeWidgetItem(_sohist, last, q.value("itemsite_id").toInt(),
q.value("item_number"),
q.value("item_descrip"),
q.value("warehous_code"),
q.value("f_minprice"),
q.value("f_maxprice"),
q.value("f_avgprice"),
q.value("f_totalunits"),
q.value("f_totalsales") );
totalSales += q.value("totalsales").toDouble();
}
while (q.next());
new XTreeWidgetItem( _sohist, last, -1,
"", tr("Totals"), "", "", "", "", "",
formatMoney(totalSales) );
_sohist->setDragString("itemsiteid=");
}
}
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");
}
void dspTimePhasedOpenAPItems::sFillCustom()
{
if (!_periods->isPeriodSelected())
{
if (isVisible())
QMessageBox::warning( this, tr("Select Calendar Periods"),
tr("Please select one or more Calendar Periods") );
return;
}
_columnDates.clear();
_apopen->clear();
_apopen->setColumnCount(2);
QString sql("SELECT vend_id, vend_number, vend_name");
int columns = 1;
QList<QTreeWidgetItem*> selected = _periods->selectedItems();
for (int i = 0; i < selected.size(); i++)
{
PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i];
sql += QString(", openAPItemsValue(vend_id, %2) AS bucket%1")
.arg(columns++)
.arg(cursor->id());
_apopen->addColumn(formatDate(cursor->startDate()), _bigMoneyColumn, Qt::AlignRight);
_columnDates.append(DatePair(cursor->startDate(), cursor->endDate()));
}
sql += " FROM vend ";
if (_selectedVendor->isChecked())
sql += "WHERE (vend_id=:vend_id)";
else if (_selectedVendorType->isChecked())
sql += "WHERE (vend_vendtype_id=:vendtype_id)";
else if (_vendorTypePattern->isChecked())
sql += "WHERE (vend_vendtype_id IN (SELECT vendtype_id FROM vendtype WHERE (vendtype_code ~ :vendtype_code))) ";
sql += "ORDER BY vend_number;";
q.prepare(sql);
q.bindValue(":vend_id", _vend->id());
q.bindValue(":vendtype_id", _vendorTypes->id());
q.bindValue(":vendtype_code", _vendorType->text().upper());
q.exec();
if (q.first())
{
Q3ValueVector<Numeric> totals(columns);;
XTreeWidgetItem *last = 0;
do
{
double lineTotal = 0.0;
last = new XTreeWidgetItem( _apopen, last, q.value("vend_id").toInt(),
q.value("vend_number"), q.value("vend_name") );
for (int column = 1; column < columns; column++)
{
QString bucketName = QString("bucket%1").arg(column);
last->setText((column + 1), formatMoney(q.value(bucketName).toDouble()));
totals[column] += q.value(bucketName).toDouble();
lineTotal += q.value(bucketName).toDouble();
}
if (lineTotal == 0.0)
{
delete last;
last = _apopen->topLevelItem(_apopen->topLevelItemCount() - 1);
}
}
while (q.next());
// Add the totals row
XTreeWidgetItem *total = new XTreeWidgetItem(_apopen, last, -1, QVariant(tr("Totals:")));
for (int column = 1; column < columns; column++)
total->setText((column + 1), formatMoney(totals[column].toDouble()));
}
}
void dspSummarizedSalesByItem::sFillList()
{
if (!checkParameters())
return;
QString sql( "SELECT item_id, item_number,"
" (item_descrip1 || ' ' || item_descrip2) AS itemdescription,"
" formatDate(MIN(cohist_invcdate)) AS f_firstdate,"
" formatDate(MAX(cohist_invcdate)) AS f_lastdate,"
" formatQty(SUM(cohist_qtyshipped)) AS f_totalunits,"
" formatMoney(SUM(round(cohist_qtyshipped * cohist_unitprice,2))) AS f_totalsales,"
" SUM(round(cohist_qtyshipped * cohist_unitprice,2)) AS totalsales "
"FROM cohist, cust, item, itemsite "
"WHERE ( (cohist_cust_id=cust_id)"
" AND (cohist_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (cohist_invcdate BETWEEN :startDate AND :endDate)" );
if (_productCategory->isSelected())
sql += " AND (item_prodcat_id=:prodcat_id)";
else if (_productCategory->isPattern())
sql += " AND (item_prodcat_id IN (SELECT prodcat_id FROM prodcat WHERE (prodcat_code ~ :prodcat_pattern)))";
if (_customerType->isSelected())
sql += " AND (cust_custtype_id=:custtype_id)";
else if (_customerType->isPattern())
sql += " AND (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ :custtype_pattern)))";
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
sql += ") "
"GROUP BY item_id, item_number, item_descrip1, item_descrip2 ";
if (_orderByItemNumber->isChecked())
sql += "ORDER BY item_number;";
else if (_orderByQtyVolume->isChecked())
sql += "ORDER BY SUM(cohist_qtyshipped) DESC;";
else if (_orderBySalesVolume->isChecked())
sql += "ORDER BY SUM(cohist_qtyshipped * cohist_unitprice) DESC;";
q.prepare(sql);
_warehouse->bindValue(q);
_customerType->bindValue(q);
_productCategory->bindValue(q);
_dates->bindValue(q);
q.exec();
_sohist->populate(q);
if (q.first())
{
double totalSales = 0.0;
do
totalSales += q.value("totalsales").toDouble();
while (q.next());
new XListViewItem( _sohist, _sohist->lastItem(), -1,
QVariant(tr("Totals")), "", "", "", "",
formatMoney(totalSales) );
}
}
void dspInvoiceRegister::sFillList()
{
_gltrans->clear();
QString sql( "SELECT gltrans_id, formatDate(gltrans_date) AS f_date, gltrans_source,"
" CASE WHEN(gltrans_doctype='IN') THEN 1"
" WHEN(gltrans_doctype='CM') THEN 2"
" WHEN(gltrans_doctype='DM') THEN 3"
" WHEN(gltrans_doctype='CD') THEN 4"
" ELSE -1"
" END AS altId,"
" CASE WHEN(gltrans_doctype='IN') THEN :invoice"
" WHEN(gltrans_doctype='CM') THEN :creditmemo"
" WHEN(gltrans_doctype='DM') THEN :debitmemo"
" WHEN(gltrans_doctype='CD') THEN :cashdeposit"
" ELSE gltrans_doctype"
" END AS doctype,"
" gltrans_docnumber,"
" CASE WHEN(gltrans_doctype='IN') THEN"
" (SELECT invchead_shipto_name"
" FROM aropen LEFT OUTER JOIN"
" invchead"
" ON (invchead_id=aropen_cobmisc_id"
" AND invchead_cust_id=aropen_cust_id)"
" WHERE ((aropen_docnumber=gltrans_docnumber)"
" AND (aropen_doctype='I')))"
" ELSE firstLine(gltrans_notes)"
" END AS f_notes,"
" (formatGLAccount(accnt_id) || ' - ' || accnt_descrip) AS f_accnt,"
" CASE WHEN (gltrans_amount < 0) THEN formatMoney(ABS(gltrans_amount))"
" ELSE ''"
" END AS f_debit,"
" CASE WHEN (gltrans_amount < 0) THEN ABS(gltrans_amount)"
" ELSE 0"
" END AS debit,"
" CASE WHEN (gltrans_amount > 0) THEN formatMoney(gltrans_amount)"
" ELSE ''"
" END AS f_credit,"
" CASE WHEN (gltrans_amount > 0) THEN gltrans_amount"
" ELSE 0"
" END AS credit "
"FROM gltrans, accnt "
"WHERE ((gltrans_accnt_id=accnt_id)"
" AND (gltrans_doctype IN ('IN', 'CM', 'DM', 'CD'))"
" AND (gltrans_source = 'A/R')"
" AND (gltrans_date BETWEEN :startDate AND :endDate)" );
if (_selectedAccount->isChecked())
sql += " AND (gltrans_accnt_id=:accnt_id)";
sql += ") "
"ORDER BY gltrans_date, gltrans_docnumber;";
q.prepare(sql);
_dates->bindValue(q);
q.bindValue(":accnt_id", _account->id());
q.bindValue(":invoice", tr("Invoice"));
q.bindValue(":creditmemo", tr("Credit Memo"));
q.bindValue(":debitmemo", tr("Debit Memo"));
q.bindValue(":cashdeposit", tr("Customer Deposit"));
q.exec();
XTreeWidgetItem * parent = 0;
XTreeWidgetItem * last = 0;
QString date;
double debit = 0.0, credit = 0.0;
double totdebit = 0.0, totcredit = 0.0;
while(q.next())
{
if(0 == parent || date != q.value("f_date").toString())
{
if(parent)
{
last = new XTreeWidgetItem(parent, last, -2, -2, QVariant(""), "", "", tr("Subtotal"), "", "", formatMoney(debit), formatMoney(credit));
parent->setExpanded(TRUE);
}
date = q.value("f_date").toString();
parent = new XTreeWidgetItem(_gltrans, parent, -1, -2, QVariant(date));
last = 0;
debit = 0.0;
credit = 0.0;
}
last = new XTreeWidgetItem(parent, last, q.value("gltrans_id").toInt(),
q.value("altId").toInt(),
QVariant(""), q.value("gltrans_source"), q.value("doctype"),
q.value("gltrans_docnumber"), q.value("f_notes"), q.value("f_accnt"),
q.value("f_debit"), q.value("f_credit"));
debit += q.value("debit").toDouble();
totdebit += q.value("debit").toDouble();
credit += q.value("credit").toDouble();
totcredit += q.value("credit").toDouble();
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
if(parent)
{
last = new XTreeWidgetItem(parent, last, -2, -2, QVariant(""), "", "", tr("Subtotal"), "", "", formatMoney(debit), formatMoney(credit));
parent->setExpanded(TRUE);
}
last = new XTreeWidgetItem(_gltrans, parent, -3, -2, QVariant(""), "", "", tr("Total"), "", "", formatMoney(totdebit), formatMoney(totcredit));
}
void dspAROpenItems::sFillList()
{
_aropen->clear();
q.prepare( "SELECT aropen_id, aropen_docnumber, aropen_ordernumber,"
" CASE WHEN (aropen_doctype='C') THEN :creditMemo"
" WHEN (aropen_doctype='D') THEN :debitMemo"
" WHEN (aropen_doctype='I') THEN :invoice"
" WHEN (aropen_doctype='R') THEN :cashdeposit"
" ELSE :other"
" END AS f_doctype,"
" formatDate(aropen_docdate) AS f_docdate,"
" formatDate(aropen_duedate) AS f_duedate,"
" formatMoney(aropen_amount) AS f_amount,"
" formatMoney(aropen_paid) AS f_paid,"
" CASE WHEN (aropen_doctype IN ('C', 'R')) THEN ((aropen_amount - aropen_paid) * -1)"
" WHEN (aropen_doctype IN ('I', 'D')) THEN (aropen_amount - aropen_paid)"
" ELSE (aropen_amount - aropen_paid)"
" END AS f_balance,"
" currConcat(aropen_curr_id) AS currAbbr,"
" currToBase(aropen_curr_id,"
" CASE WHEN (aropen_doctype IN ('C', 'R')) THEN ((aropen_amount - aropen_paid) * -1)"
" WHEN (aropen_doctype IN ('I', 'D')) THEN (aropen_amount - aropen_paid)"
" ELSE (aropen_amount - aropen_paid)"
" END, CURRENT_DATE) AS base_balance,"
" cust_id, cust_number, cust_name "
"FROM aropen LEFT OUTER JOIN custinfo ON (aropen_cust_id=cust_id) "
"WHERE ( (aropen_open)"
" AND (aropen_duedate BETWEEN :startDate AND :endDate) ) "
"ORDER BY aropen_docnumber;" );
_dates->bindValue(q);
q.bindValue(":creditMemo", tr("C/M"));
q.bindValue(":debitMemo", tr("D/M"));
q.bindValue(":invoice", tr("Invoice"));
q.bindValue(":cashdeposit", tr("C/D"));
q.exec();
XTreeWidgetItem * last = 0;
double total= 0.0;
while (q.next())
{
last = new XTreeWidgetItem( _aropen, last, q.value("aropen_id").toInt(),
q.value("cust_id").toInt(),
q.value("f_doctype"),
q.value("aropen_docnumber"),
q.value("cust_number"), q.value("cust_name"),
q.value("aropen_ordernumber"),
q.value("f_docdate"),
q.value("f_duedate"), q.value("f_amount"),
q.value("f_paid"),
q.value("f_balance"), q.value("currAbbr") );
total += q.value("base_balance").toDouble();
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
new XTreeWidgetItem( _aropen, last, -1,
QVariant(tr("Total")), "", "", "", "", "", "", "", "",
formatMoney(total), CurrDisplay::baseCurrAbbr() );
}
void dspBacklogByItem::sFillList()
{
_soitem->clear();
if (_item->isValid())
{
QString sql( "SELECT cohead_id, coitem_id, cohead_number, coitem_linenumber, cust_name, "
" formatDate(cohead_orderdate) AS f_orderdate,"
" formatDate(coitem_scheddate) AS f_scheddate,"
" uom_name,"
" formatQty(coitem_qtyord) AS f_qtyord,"
" formatQty(coitem_qtyshipped) AS f_qtyshipped,"
" formatQty(noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) AS f_balance,"
" formatMoney(round((noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) * (coitem_price / coitem_price_invuomratio),2)) AS f_amount,"
" round((noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) * (coitem_price / coitem_price_invuomratio),2) AS backlog "
"FROM cohead, coitem, cust, itemsite, item, uom "
"WHERE ( (coitem_cohead_id=cohead_id)"
" AND (cohead_cust_id=cust_id)"
" AND (coitem_status NOT IN ('C','X'))"
" AND (coitem_itemsite_id=itemsite_id)"
" AND (coitem_qty_uom_id=uom_id)"
" AND (itemsite_item_id=item_id)"
" AND (itemsite_item_id=:item_id)"
" AND (coitem_scheddate BETWEEN :startDate AND :endDate)" );
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
sql += ") "
"ORDER BY coitem_scheddate";
q.prepare(sql);
_warehouse->bindValue(q);
_dates->bindValue(q);
q.bindValue(":item_id", _item->id());
q.exec();
XTreeWidgetItem *last = 0;
double totalBacklog = 0.0;
while (q.next())
{
last = new XTreeWidgetItem(_soitem, last,
q.value("cohead_id").toInt(),
q.value("coitem_id").toInt(),
q.value("cohead_number"),
q.value("coitem_linenumber"),
q.value("cust_name"),
q.value("f_orderdate"),
q.value("f_scheddate"),
q.value("uom_name"),
q.value("f_qtyord"),
q.value("f_qtyshipped"),
q.value("f_balance"),
q.value("f_amount"));
totalBacklog += q.value("backlog").toDouble();
}
if (_showPrices->isChecked())
{
last = new XTreeWidgetItem(_soitem, last, -1, -1,
"", "", tr("Total Backlog"), "", "", "", "", "",
formatMoney(totalBacklog) );
}
}
else
_soitem->clear();
}
void dspSalesHistoryByBilltoName::sFillList()
{
_sohist->clear();
if (!checkParameters())
return;
QString sql( "SELECT cohist_id, cohist_billtoname,"
" cohist_ordernumber, cohist_invcnumber,"
" formatDate(cohist_orderdate) AS f_orderdate,"
" formatDate(cohist_invcdate) AS f_invcdate,"
" item_number,"
" cohist_qtyshipped, formatQty(cohist_qtyshipped) AS f_shipped " );
sql += ", formatSalesPrice(cohist_unitprice) AS f_unitprice,"
" round(cohist_qtyshipped * cohist_unitprice, 2) AS extprice,"
" formatMoney(round(cohist_qtyshipped * cohist_unitprice, 2)) AS f_extprice ";
sql += ", formatCost(cohist_unitcost) AS f_unitcost,"
" (cohist_qtyshipped * cohist_unitcost) AS extcost,"
" formatCost(cohist_qtyshipped * cohist_unitcost) AS f_extcost ";
sql += "FROM cohist, itemsite, item "
"WHERE ( (cohist_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (cohist_invcdate BETWEEN :startDate AND :endDate)"
" AND (UPPER(cohist_billtoname) ~ UPPER(:billToName))";
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_productCategory->isSelected())
sql += " AND (item_prodcat_id=:prodcat_id)";
else if (_productCategory->isPattern())
sql += " AND (item_prodcat_id IN (SELECT prodcat_id FROM prodcat WHERE (prodcat_code ~ :prodcat_pattern)))";
sql += ") "
"ORDER BY cohist_invcdate, cohist_billtoname, item_number";
q.prepare(sql);
_warehouse->bindValue(q);
_productCategory->bindValue(q);
_dates->bindValue(q);
q.bindValue(":billToName", _billtoName->text());
q.exec();
if (q.first())
{
double totalUnits = 0.0;
double totalSales = 0.0;
double totalCosts = 0.0;
XTreeWidgetItem *last = 0;
do
{
last = new XTreeWidgetItem( _sohist, last, q.value("cohist_id").toInt(),
q.value("cohist_billtoname"),
q.value("cohist_ordernumber"),
q.value("cohist_invcnumber"),
q.value("f_orderdate"),
q.value("f_invcdate"),
q.value("item_number"),
q.value("f_shipped"),
q.value("f_unitprice"),
q.value("f_extprice"),
q.value("f_unitcost"),
q.value("f_extcost"));
totalUnits += q.value("cohist_qtyshipped").toDouble();
totalSales += q.value("extprice").toDouble();
totalCosts += q.value("extcost").toDouble();
}
while (q.next());
XTreeWidgetItem *totals = new XTreeWidgetItem(_sohist, last, -1,
QVariant(tr("Total Sales")));
totals->setText(6, formatQty(totalUnits));
totals->setText(EXTPRICE_COL, formatMoney(totalSales));
totals->setText(EXTCOST_COL, formatCost(totalCosts));
}
}
/*
Note that the SELECTs here are UNIONs of the gltrans 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;
// fill receipts list
currid = _receipts->id();
_receipts->clear();
q.prepare("SELECT gltrans_id AS id, 1 AS altid,"
" jrnluse_use AS use, gltrans_journalnumber AS jrnlnum,"
" COALESCE(date(jrnluse_date), gltrans_date) AS f_jrnldate,"
" COALESCE(bankrecitem_cleared, FALSE) AS cleared,"
" gltrans_date AS f_date,"
" gltrans_docnumber AS docnumber,"
" gltrans_notes AS notes,"
" currToLocal(bankaccnt_curr_id, gltrans_amount, gltrans_date) * -1 AS amount,"
" COALESCE(date(jrnluse_date), gltrans_date) AS jrnldate,"
" gltrans_date AS sortdate "
" FROM (bankaccnt CROSS JOIN gltrans) LEFT OUTER JOIN bankrecitem "
" ON ((bankrecitem_source='GL') AND (bankrecitem_source_id=gltrans_id)"
" AND (bankrecitem_bankrec_id=:bankrecid)) "
" LEFT OUTER JOIN jrnluse ON (jrnluse_number=gltrans_journalnumber AND jrnluse_use='C/R')"
" WHERE ((gltrans_accnt_id=bankaccnt_accnt_id)"
" AND (NOT gltrans_deleted) "
" AND (NOT gltrans_rec)"
" AND (gltrans_amount < 0)"
" AND (bankaccnt_id=:bankaccntid) ) "
" UNION ALL "
"SELECT bankadj_id AS id, 2 AS altid,"
" '' AS use, NULL AS jrnlnum, bankadj_date AS f_jrnldate,"
" COALESCE(bankrecitem_cleared, FALSE) AS cleared,"
" bankadj_date AS f_date,"
" bankadj_docnumber AS docnumber,"
" bankadjtype_name AS notes,"
" (CASE WHEN(bankadjtype_iscredit=true) THEN (bankadj_amount * -1) ELSE bankadj_amount END) AS amount,"
" bankadj_date AS jrnldate,"
" bankadj_date AS sortdate "
" FROM (bankadjtype CROSS JOIN bankadj) "
" LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='AD') "
" AND (bankrecitem_source_id=bankadj_id) "
" AND (bankrecitem_bankrec_id=:bankrecid)) "
" WHERE ( (((bankadjtype_iscredit=false) AND (bankadj_amount > 0)) OR ((bankadjtype_iscredit=true) AND (bankadj_amount < 0))) "
" AND (bankadj_bankadjtype_id=bankadjtype_id) "
" AND (NOT bankadj_posted) "
" AND (bankadj_bankaccnt_id=:bankaccntid) ) "
"ORDER BY jrnldate, jrnlnum, sortdate; ");
q.bindValue(":bankaccntid", _bankaccnt->id());
q.bindValue(":bankrecid", _bankrecid);
q.exec();
if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.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 (q.next())
{
if(q.value("use").toString() == "C/R")
{
if(q.value("jrnlnum").toInt() != jrnlnum || (0 == parent))
{
if(parent != 0)
{
parent->setText(0, (cleared ? tr("Yes") : tr("No")));
parent->setText(4, amountNull ? tr("?????") : formatMoney(amount));
}
jrnlnum = q.value("jrnlnum").toInt();
last = new XTreeWidgetItem( _receipts, last,
jrnlnum, 3, "", formatDate(q.value("f_jrnldate").toDate()), q.value("jrnlnum"));
parent = last;
cleared = true;
amount = 0.0;
amountNull = true;
lastChild = 0;
}
cleared = (cleared && q.value("cleared").toBool());
amount += q.value("amount").toDouble();
amountNull = q.value("amount").isNull();
lastChild = new XTreeWidgetItem( parent, lastChild,
q.value("id").toInt(), q.value("altid").toInt(),
(q.value("cleared").toBool() ? tr("Yes") : tr("No")),
formatDate(q.value("f_date").toDate()), q.value("docnumber"),
q.value("notes"),
q.value("amount").isNull() ? tr("?????") : formatMoney(q.value("amount").toDouble()) );
}
else
{
if(parent != 0)
{
parent->setText(0, (cleared ? tr("Yes") : tr("No")));
parent->setText(4, formatMoney(amount));
}
parent = 0;
cleared = true;
amount = 0.0;
amountNull = true;
lastChild = 0;
last = new XTreeWidgetItem( _receipts, last,
q.value("id").toInt(), q.value("altid").toInt(),
(q.value("cleared").toBool() ? tr("Yes") : tr("No")),
formatDate(q.value("f_date").toDate()), q.value("docnumber"),
q.value("notes"),
q.value("amount").isNull() ? tr("?????") : formatMoney(q.value("amount").toDouble()) );
}
}
if(parent != 0)
{
parent->setText(0, (cleared ? tr("Yes") : tr("No")));
parent->setText(4, amountNull ? tr("?????") : formatMoney(amount));
}
if(currid != -1)
_receipts->setCurrentItem(_receipts->topLevelItem(currid));
if(_receipts->currentItem())
_receipts->scrollToItem(_receipts->currentItem());
// fill receipts cleared value
q.prepare("SELECT COALESCE(SUM(amount),0.0) AS cleared_amount"
" FROM ( SELECT currToLocal(bankaccnt_curr_id, gltrans_amount * -1, gltrans_date) AS amount"
" FROM bankaccnt, gltrans, bankrecitem"
" WHERE ((gltrans_accnt_id=bankaccnt_accnt_id)"
" AND (bankrecitem_source='GL')"
" AND (bankrecitem_source_id=gltrans_id)"
" AND (bankrecitem_bankrec_id=:bankrecid)"
" AND (bankrecitem_cleared)"
" AND (NOT gltrans_deleted)"
" AND (NOT gltrans_rec)"
" AND (gltrans_amount < 0)"
" AND (bankaccnt_id=:bankaccntid) ) "
" UNION ALL"
" SELECT CASE WHEN(bankadjtype_iscredit=true) THEN (bankadj_amount * -1) ELSE bankadj_amount END AS amount"
" FROM bankrecitem, bankadj, bankadjtype "
" WHERE ( (bankrecitem_source='AD')"
" AND (bankrecitem_source_id=bankadj_id)"
" AND (bankrecitem_bankrec_id=:bankrecid)"
" AND (bankrecitem_cleared)"
" AND (bankadj_bankadjtype_id=bankadjtype_id)"
" AND (NOT bankadj_posted)"
" AND (((bankadjtype_iscredit=false) AND (bankadj_amount > 0)) OR (bankadjtype_iscredit=true AND (bankadj_amount < 0))) "
" AND (bankadj_bankaccnt_id=:bankaccntid) ) ) AS data;");
q.bindValue(":bankaccntid", _bankaccnt->id());
q.bindValue(":bankrecid", _bankrecid);
q.exec();
if (q.first())
_clearedReceipts->setDouble(q.value("cleared_amount").toDouble());
else if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
// fill checks list
currid = _checks->id();
_checks->clear();
q.prepare("SELECT gltrans_id AS id, 1 AS altid,"
" COALESCE(bankrecitem_cleared, FALSE) AS cleared,"
" gltrans_date AS transdate,"
" gltrans_docnumber AS docnumber,"
" gltrans_notes AS notes,"
" currToLocal(bankaccnt_curr_id, gltrans_amount, gltrans_date) AS amount,"
" gltrans_date AS sortdate, "
" 'curr' AS amount_xtnumericrole "
" FROM (bankaccnt CROSS JOIN gltrans) LEFT OUTER JOIN bankrecitem "
" ON ((bankrecitem_source='GL') AND (bankrecitem_source_id=gltrans_id)"
" AND (bankrecitem_bankrec_id=:bankrecid)) "
" WHERE ((gltrans_accnt_id=bankaccnt_accnt_id)"
" AND (NOT gltrans_deleted)"
" AND (NOT gltrans_rec)"
" AND (gltrans_amount > 0)"
" AND (bankaccnt_id=:bankaccntid) ) "
" UNION ALL "
"SELECT bankadj_id AS id, 2 AS altid,"
" COALESCE(bankrecitem_cleared, FALSE) AS cleared,"
" bankadj_date AS transdate,"
" bankadj_docnumber AS docnumber,"
" bankadjtype_name AS notes,"
" CASE WHEN(bankadjtype_iscredit=false) THEN (bankadj_amount * -1) ELSE bankadj_amount END AS amount,"
" bankadj_date AS sortdate, "
" 'curr' AS amount_xtnumericrole "
" FROM (bankadjtype CROSS JOIN bankadj) "
" LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='AD') "
" AND (bankrecitem_source_id=bankadj_id) "
" AND (bankrecitem_bankrec_id=:bankrecid)) "
" WHERE ( (((bankadjtype_iscredit=true) AND (bankadj_amount > 0)) OR ((bankadjtype_iscredit=false) AND (bankadj_amount < 0))) "
" AND (bankadj_bankadjtype_id=bankadjtype_id) "
" AND (NOT bankadj_posted) "
" AND (bankadj_bankaccnt_id=:bankaccntid) ) "
"ORDER BY sortdate; ");
q.bindValue(":bankaccntid", _bankaccnt->id());
q.bindValue(":bankrecid", _bankrecid);
q.exec();
if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
_checks->populate(q, TRUE);
if(currid != -1)
_checks->setCurrentItem(_checks->topLevelItem(currid));
if(_checks->currentItem())
_checks->scrollToItem(_checks->currentItem());
// fill checks cleared value
q.prepare("SELECT COALESCE(SUM(amount),0.0) AS cleared_amount"
" FROM ( SELECT currToLocal(bankaccnt_curr_id, gltrans_amount, gltrans_date) AS amount"
" FROM bankaccnt, gltrans, bankrecitem"
" WHERE ((gltrans_accnt_id=bankaccnt_accnt_id)"
" AND (bankrecitem_source='GL')"
" AND (bankrecitem_source_id=gltrans_id)"
" AND (bankrecitem_bankrec_id=:bankrecid)"
" AND (bankrecitem_cleared)"
" AND (NOT gltrans_deleted)"
" AND (NOT gltrans_rec)"
" AND (gltrans_amount > 0)"
" AND (bankaccnt_id=:bankaccntid) ) "
" UNION ALL"
" SELECT CASE WHEN(bankadjtype_iscredit=false) THEN (bankadj_amount * -1) ELSE bankadj_amount END AS amount"
" FROM bankadj, bankadjtype, bankrecitem"
" WHERE ( (bankrecitem_source='AD')"
" AND (bankrecitem_source_id=bankadj_id)"
" AND (bankrecitem_bankrec_id=:bankrecid)"
" AND (bankrecitem_cleared)"
" AND (bankadj_bankadjtype_id=bankadjtype_id)"
" AND (NOT bankadj_posted)"
" AND (((bankadjtype_iscredit=true) AND (bankadj_amount > 0)) OR ((bankadjtype_iscredit=false) AND (bankadj_amount < 0)))"
" AND (bankadj_bankaccnt_id=:bankaccntid) ) ) AS data;");
q.bindValue(":bankaccntid", _bankaccnt->id());
q.bindValue(":bankrecid", _bankrecid);
q.exec();
if (q.first())
_clearedChecks->setDouble(q.value("cleared_amount").toDouble());
else if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
// calculate cleared balance
q.prepare("SELECT (COALESCE(SUM(amount),0.0) + :begBal) AS cleared_amount,"
" :endBal AS end_amount,"
" (:endBal - (:begBal + (COALESCE(SUM(amount),0.0)))) AS diff_amount,"
" round(:endBal - (:begBal + COALESCE(SUM(amount),0.0)), 2) AS diff_value"
" FROM ( SELECT currToLocal(bankaccnt_curr_id, gltrans_amount * -1, gltrans_date) AS amount"
" FROM bankaccnt, gltrans, bankrecitem"
" WHERE ((gltrans_accnt_id=bankaccnt_accnt_id)"
" AND (bankrecitem_source='GL')"
" AND (bankrecitem_source_id=gltrans_id)"
" AND (bankrecitem_bankrec_id=:bankrecid)"
" AND (bankrecitem_cleared)"
" AND (NOT gltrans_rec)"
" AND (bankaccnt_id=:bankaccntid) ) "
" UNION ALL"
" SELECT CASE WHEN(bankadjtype_iscredit=true) THEN (bankadj_amount * -1) ELSE bankadj_amount END AS amount"
" FROM bankadj, bankadjtype, bankrecitem"
" WHERE ( (bankrecitem_source='AD')"
" AND (bankrecitem_source_id=bankadj_id)"
" AND (bankrecitem_bankrec_id=:bankrecid)"
" AND (bankrecitem_cleared)"
" AND (bankadj_bankadjtype_id=bankadjtype_id)"
" AND (NOT bankadj_posted)"
" AND (bankadj_bankaccnt_id=:bankaccntid) ) ) AS data;");
q.bindValue(":bankaccntid", _bankaccnt->id());
q.bindValue(":bankrecid", _bankrecid);
q.bindValue(":endBal", endBal);
q.bindValue(":begBal", begBal);
q.bindValue(":curr_id", _currency->id());
q.bindValue(":effective", _startDate->date());
q.bindValue(":expires", _endDate->date());
q.exec();
bool enableRec = FALSE;
if(q.first())
{
_clearBal->setDouble(q.value("cleared_amount").toDouble());
_endBal2->setDouble(q.value("end_amount").toDouble());
_diffBal->setDouble(q.value("diff_amount").toDouble());
QString stylesheet;
if(q.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 (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
//_reconcile->setEnabled(enableRec);
qApp->restoreOverrideCursor();
}
QString KLocale::formatMoney(double num, const QString &symbol, int precision) const
{
return d->formatMoney(num, symbol, precision);
}
void dspARApplications::sFillList()
{
if ( (_selectedCustomer->isChecked()) && (!_cust->isValid()) )
{
QMessageBox::warning( this, tr("Select Customer"),
tr("You must select a Customer whose A/R Applications you wish to view.") );
_cust->setFocus();
return;
}
if (!_dates->startDate().isValid())
{
QMessageBox::critical( this, tr("Enter Start Date"),
tr("You must enter a valid Start Date.") );
_dates->setFocus();
return;
}
if (!_dates->endDate().isValid())
{
QMessageBox::critical( this, tr("Enter End Date"),
tr("You must enter a valid End Date.") );
_dates->setFocus();
return;
}
if ( (!_cashReceipts->isChecked()) && (!_creditMemos->isChecked()) )
{
QMessageBox::critical( this, tr("Select Document Type"),
tr("You must indicate which Document Type(s) you wish to view.") );
_cashReceipts->setFocus();
return;
}
QString sql( "SELECT arapply_id, cust_number, cust_name,"
" formatDate(arapply_postdate) AS f_postdate,"
" arapply_source_doctype, arapply_fundstype, "
" CASE WHEN (arapply_source_doctype IN ('C','R')) THEN TEXT(arapply_source_docnumber)"
" ELSE arapply_refnumber"
" END AS source,"
" arapply_target_doctype,"
" TEXT(arapply_target_docnumber) AS target,"
" formatMoney(arapply_applied) AS f_applied, arapply_applied "
"FROM arapply, custinfo "
"WHERE ( (arapply_cust_id=cust_id)"
" AND (arapply_postdate BETWEEN <? value(\"startDate\") ?> AND <? value(\"endDate\") ?>)"
" AND (arapply_source_doctype IN ("
"<? if exists(\"creditMemos\") ?>"
" <? if exists(\"cashReceipts\") ?>"
" 'K', 'C', 'R' "
" <? else ?>"
" 'C', 'R' "
" <? endif ?>"
"<? else ?>"
" 'K' "
"<? endif ?>"
"))"
"<? if exists(\"cust_id\") ?>"
" AND (cust_id=<? value(\"cust_id\") ?>)"
"<? elseif 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_id\") ?>)))"
"<? endif ?>"
") "
"ORDER BY arapply_postdate, source;"
);
ParameterList params;
if (_cashReceipts->isChecked())
params.append("cashReceipts");
if (_creditMemos->isChecked())
params.append("creditMemos");
params.append("startDate", _dates->startDate());
params.append("endDate", _dates->endDate());
if (_selectedCustomer->isChecked())
params.append("cust_id", _cust->id());
else if (_selectedCustomerType->isChecked())
params.append("custtype_id", _customerTypes->id());
else if (_customerTypePattern->isChecked())
params.append("custtype_pattern", _customerType->text());
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
if (q.first())
{
_arapply->clear();
double total = 0;
XTreeWidgetItem* last = 0;
do
{
QString fundstype = q.value("arapply_fundstype").toString();
QString doctype;
if (q.value("arapply_source_doctype") == "C")
doctype = tr("Credit Memo");
else if (q.value("arapply_source_doctype") == "R")
doctype = tr("Cash Deposit");
else if (fundstype == "A")
doctype = tr("AmEx");
else if (fundstype == "C")
doctype = tr("Check");
else if (fundstype == "D")
doctype = tr("Discover");
else if (fundstype == "K")
doctype = tr("Cash");
else if (fundstype == "M")
doctype = tr("M/C");
else if (fundstype == "R")
doctype = tr("Other C/C");
else if (fundstype == "T")
doctype = tr("Cert. Check");
else if (fundstype == "V")
doctype = tr("Visa");
else if (fundstype == "W")
doctype = tr("Wire Trans.");
else if (fundstype == "O")
doctype = tr("Other");
QString targetdoctype = q.value("arapply_target_doctype").toString();
if (targetdoctype == "D")
targetdoctype = tr("Debit Memo");
else if (targetdoctype == "I")
targetdoctype = tr("Invoice");
else if (targetdoctype == "K")
targetdoctype = tr("A/P Check");
else
targetdoctype = tr("Other");
last = new XTreeWidgetItem( _arapply, last,
q.value("arapply_id").toInt(),
q.value("cust_number"),
q.value("cust_name"),
q.value("f_postdate"),
(q.value("arapply_source_doctype") == "C") ?
"C/M" : ((q.value("arapply_source_doctype") == "R") ? "Cash Deposit" : fundstype),
doctype,
q.value("source"),
q.value("arapply_target_doctype").toString(),
targetdoctype,
q.value("target"), q.value("f_applied") );
total += q.value("arapply_applied").toDouble();
}
while (q.next());
last = new XTreeWidgetItem(_arapply, last, -1, "", tr("Total Applications:"));
last->setText(9, formatMoney(total));
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void dspAROpenItems::sFillList()
{
_aropen->clear();
MetaSQLQuery mql = mqlLoad(":/ar/arOpenItems.mql");
ParameterList params;
_dates->appendValue(params);
params.append("invoice", tr("Invoice"));
params.append("creditMemo", tr("C/M"));
params.append("debitMemo", tr("D/M"));
params.append("cashdeposit", tr("C/D"));
if (_incidentsOnly->isChecked())
params.append("incidentsOnly");
q = mql.toQuery(params);
XSqlQuery incident;
incident.prepare( "SELECT incdt_id, incdt_number::TEXT AS incdtnumber,"
" incdt_summary, incdt_assigned_username "
"FROM incdt "
"WHERE (incdt_aropen_id=:aropen_id);" );
if (q.first())
{
XTreeWidgetItem *document = 0;
XTreeWidgetItem *last = 0;
double total= 0.0;
do
{
last = document = new XTreeWidgetItem( _aropen, last, q.value("aropen_id").toInt(), q.value("cust_id").toInt(),
q.value("doctype"),
q.value("aropen_docnumber"),
q.value("cust_number"), q.value("cust_name"),
q.value("aropen_ordernumber"),
formatDate(q.value("aropen_docdate").toDate()),
formatDate(q.value("aropen_duedate").toDate()),
formatMoney(q.value("aropen_amount").toDouble()),
formatMoney(q.value("aropen_paid").toDouble()),
formatMoney(q.value("balance").toDouble()),
q.value("currAbbr") );
total += q.value("base_balance").toDouble();
incident.bindValue(":aropen_id", q.value("aropen_id").toInt());
incident.exec();
if (incident.first())
{
do
{
new XTreeWidgetItem( document, -1, incident.value("incdt_id").toInt(),
"",
"",
incident.value("incdtnumber"), incident.value("incdt_summary"),
incident.value("incdt_assigned_username"),
"",
"", "",
"",
"", "" );
}
while (incident.next());
}
}
while (q.next());
new XTreeWidgetItem( _aropen, last, -1,
QVariant(tr("Total")), "", "", "", "", "", "", "", "",
formatMoney(total), CurrDisplay::baseCurrAbbr() );
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void dspAPOpenItemsByVendor::sFillList()
{
_apopen->clear();
q.prepare( "SELECT apopen_id, apopen_ponumber, apopen_docnumber,"
" CASE WHEN (apopen_doctype='C') THEN :creditMemo"
" WHEN (apopen_doctype='D') THEN :debitMemo"
" WHEN (apopen_doctype='V') THEN :voucher"
" ELSE :other"
" END AS f_doctype,"
" apopen_invcnumber AS invoicenumber,"
" formatDate(apopen_docdate) AS f_docdate,"
" formatDate(apopen_duedate) AS f_duedate,"
" formatMoney(apopen_amount) AS f_amount,"
" formatMoney(apapplied(apopen_id,:asofdate)) AS f_paid,"
" CASE WHEN (apopen_doctype='C') THEN ((apopen_amount - apapplied(apopen_id,:asofdate)) * -1)"
" WHEN (apopen_doctype IN ('V', 'D')) THEN (apopen_amount - apapplied(apopen_id,:asofdate))"
" ELSE (apopen_amount - apapplied(apopen_id,:asofdate))"
" END AS balance, currConcat(apopen_curr_id) AS currAbbr,"
" currToBase(apopen_curr_id,"
" CASE WHEN (apopen_doctype='C') THEN ((apopen_amount - apapplied(apopen_id,:asofdate)) * -1)"
" WHEN (apopen_doctype IN ('V', 'D')) THEN (apopen_amount - apapplied(apopen_id,:asofdate))"
" ELSE (apopen_amount - apopen_paid)"
" END, apopen_docdate) AS base_balance "
"FROM apopen "
" WHERE ( (COALESCE(apopen_closedate,date :asofdate + integer '1')>:asofdate) "
" AND (apopen_docdate<=:asofdate)"
" AND (apopen_vend_id=:vend_id) "
" AND (apopen_duedate BETWEEN :startDate AND :endDate) "
" AND ((currtobase(apopen_curr_id,apopen_amount,:asofdate) - apapplied(apopen_id,:asofdate)) > 0)) "
" ORDER BY apopen_docdate;" );
_dates->bindValue(q);
q.bindValue(":vend_id", _vend->id());
q.bindValue(":creditMemo", tr("C/M"));
q.bindValue(":debitMemo", tr("D/M"));
q.bindValue(":voucher", tr("Voucher"));
q.bindValue(":asofdate", _asOf->date());
q.exec();
if (q.first())
{
double total= 0.0;
XTreeWidgetItem *last = 0;
do
{
last = new XTreeWidgetItem( _apopen, last, q.value("apopen_id").toInt(),
q.value("f_doctype"), q.value("apopen_docnumber"),
q.value("apopen_ponumber"), q.value("invoicenumber"), q.value("f_docdate"),
q.value("f_duedate"), q.value("f_amount"),
q.value("f_paid"), formatMoney(q.value("balance").toDouble()),
q.value("currAbbr"),
formatMoney(q.value("base_balance").toDouble()));
total += q.value("base_balance").toDouble();
}
while (q.next());
last = new XTreeWidgetItem( _apopen, last, -1,
QVariant(tr("Total")), "", "", "", "", "", "", "", "", "",
formatMoney(total) );
}
}
void dspRWTransactions::sFillList()
{
_gltrans->clear();
QString sql( "SELECT gltrans_id, formatDate(gltrans_date) AS f_date,"
" gltrans_source, gltrans_doctype, gltrans_docnumber,"
" firstLine(gltrans_notes) AS f_notes,"
" (formatGLAccount(accnt_id)||' - '|| accnt_descrip) AS f_accnt,"
" CASE WHEN (gltrans_amount < 0) THEN formatMoney(abs(gltrans_amount))"
" ELSE ''"
" END AS f_debit,"
" CASE WHEN (gltrans_amount > 0) THEN formatMoney(gltrans_amount)"
" ELSE ''"
" END AS f_credit,"
" formatBoolYN(gltrans_exported) AS f_exported,"
" CASE WHEN (gltrans_amount < 0) THEN abs(gltrans_amount)"
" ELSE 0"
" END AS debit,"
" CASE WHEN (gltrans_amount > 0) THEN gltrans_amount"
" ELSE 0"
" END AS credit "
"FROM gltrans, accnt "
"WHERE ((gltrans_accnt_id=accnt_id)"
" AND (gltrans_date BETWEEN :startDate AND :endDate)" );
if (_selectedAccount->isChecked())
sql += " AND (gltrans_accnt_id=:accnt_id)";
if (_showExported->isChecked())
sql += " AND (gltrans_exported)";
else if (_showUnexported->isChecked())
sql += " AND (NOT gltrans_exported)";
if (_arDist->isChecked())
sql += " AND ((gltrans_source='A/R') AND (gltrans_doctype IN ('IN', 'CM')))";
else if (_apDist->isChecked())
sql += " AND ((gltrans_source='A/P') AND (gltrans_doctype='VO'))";
else
sql += " AND (NOT ((gltrans_source='A/R') AND (gltrans_doctype IN ('IN', 'CM'))))"
" AND (NOT ((gltrans_source='A/P') AND (gltrans_doctype='VO')))";
sql += ") "
"ORDER BY accnt_number, accnt_profit, accnt_sub, gltrans_journalnumber, gltrans_date, gltrans_docnumber;";
q.prepare(sql);
_dates->bindValue(q);
q.bindValue(":accnt_id", _account->id());
q.exec();
double debit = 0.0;
double credit = 0.0;
XTreeWidgetItem *last = 0;
while(q.next())
{
debit += q.value("debit").toDouble();
credit += q.value("credit").toDouble();
last = new XTreeWidgetItem( _gltrans, last, q.value("gltrans_id").toInt(),
q.value("f_date"), q.value("gltrans_source"), q.value("gltrans_doctype"),
q.value("gltrans_docnumber"), q.value("f_notes"),
q.value("f_accnt"), q.value("f_debit"), q.value("f_credit"), q.value("f_exported") );
}
last = new XTreeWidgetItem( _gltrans, last, -1, "", "", "", "", tr("Total:"), "", formatMoney(debit), formatMoney(credit));
}
void dspTimePhasedPlannedREByPlannerCode::sFillList()
{
if(_useAveragePrice->isChecked() && !(_startEvalDate->isValid() && _endEvalDate->isValid()))
{
QMessageBox::information(this, tr("Average Price Requires Dates"),
tr("The Average Price option requires that you specify a valid\n"
"date range to evaluate the average price."));
return;
}
_plannedRE->clear();
_plannedRE->setColumnCount(1);
QString sql("SELECT ");
bool show = FALSE;
int columns = 1;
QList<QTreeWidgetItem*> selected = _periods->selectedItems();
for (int i = 0; i < selected.size(); i++)
{
PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i];
if (show)
sql += ",";
else
show = TRUE;
sql += QString(" SUM(plannedCost(plancode_id, warehous_id, '%1', %2)) AS cost%3,")
.arg((_useStandardCost->isChecked()) ? 'S' : 'A')
.arg(cursor->id())
.arg(columns);
if (_useListPrice->isChecked())
sql += QString(" SUM(plannedRevenue(plancode_id, warehous_id, 'L', %1)) AS revenue%2 ")
.arg(cursor->id())
.arg(columns++);
else
sql += QString(" SUM(plannedRevenue(plancode_id, warehous_id, 'A', %1, date('%2'), date('%3'))) AS revenue%4 ")
.arg(cursor->id())
.arg(_startEvalDate->date().toString()) // NOT locale format
.arg(_endEvalDate->date().toString()) // NOT locale format
.arg(columns++);
_plannedRE->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight);
_columnDates.append(DatePair(cursor->startDate(), cursor->endDate()));
}
if (show)
{
sql += " FROM plancode, warehous";
if (_warehouse->isSelected())
sql += " WHERE ( (warehous_id=:warehous_id)";
if (_plannerCode->isSelected())
{
if (_warehouse->isSelected())
sql += " AND (plancode_id=:plancode_id)";
else
sql += " WHERE ( (plancode_id=:plancode_id)";
}
else if (_plannerCode->isPattern())
{
if (_warehouse->isSelected())
sql += " AND (plancode_code ~ :plancode_pattern)";
else
sql += " WHERE ( (plancode_code ~ :plancode_pattern)";
}
if ( (_warehouse->isSelected()) || (!_plannerCode->isAll()) )
sql += ");";
else
sql += ";";
q.prepare(sql);
_warehouse->bindValue(q);
_plannerCode->bindValue(q);
q.exec();
if (q.first())
{
XTreeWidgetItem *cost = new XTreeWidgetItem( _plannedRE, 0, QVariant(tr("Cost")),
formatMoney(q.value("cost1").toDouble()) );
XTreeWidgetItem *revenue = new XTreeWidgetItem( _plannedRE, cost, 0, QVariant(tr("Revenue")),
formatMoney(q.value("revenue1").toDouble()) );
XTreeWidgetItem *profit = new XTreeWidgetItem( _plannedRE, revenue, 0, QVariant(tr("Gross Profit")),
formatMoney(q.value("revenue1").toDouble() - q.value("cost1").toDouble() ) );
for (int bucketCounter = 1; bucketCounter < columns; bucketCounter++)
{
cost->setText(bucketCounter, formatMoney(q.value(QString("cost%1").arg(bucketCounter)).toDouble()));
revenue->setText(bucketCounter, formatMoney(q.value(QString("revenue%1").arg(bucketCounter)).toDouble()));
profit->setText( bucketCounter,
formatMoney( q.value(QString("revenue%1").arg(bucketCounter)).toDouble() -
q.value(QString("cost%1").arg(bucketCounter)).toDouble() ) );
}
}
}
}
void dspTimePhasedSalesByProductCategory::sCalculate()
{
if (!_periods->isPeriodSelected())
{
if (isVisible())
QMessageBox::warning( this, tr("Select Calendar Periods"),
tr("Please select one or more Calendar Periods") );
return;
}
_sohist->clear();
_sohist->setColumnCount(3);
_columnDates.clear();
QString sql("SELECT prodcat_id, warehous_id, prodcat_code, warehous_code");
if (_salesDollars->isChecked())
sql += ", TEXT('$') AS uom";
else if (_inventoryUnits->isChecked())
sql += ", uom_name AS uom";
else if (_capacityUnits->isChecked())
sql += ", itemcapuom(item_id) AS uom";
else if (_altCapacityUnits->isChecked())
sql += ", itemaltcapuom(item_id) AS uom";
int columns = 1;
QList<QTreeWidgetItem*> selected = _periods->selectedItems();
for (int i = 0; i < selected.size(); i++)
{
PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i];
if (_salesDollars->isChecked())
sql += QString(", SUM(shipmentsByItemValue(itemsite_id, %2)) AS bucket%1")
.arg(columns++)
.arg(cursor->id());
else if (_inventoryUnits->isChecked())
sql += QString(", SUM(shipmentsByItemQty(itemsite_id, %2)) AS bucket%1")
.arg(columns++)
.arg(cursor->id());
else if (_capacityUnits->isChecked())
sql += QString(", SUM(shipmentsByItemQty(itemsite_id, %2) * itemcapinvrat(item_id)) AS bucket%1")
.arg(columns++)
.arg(cursor->id());
else if (_altCapacityUnits->isChecked())
sql += QString(", SUM(shipmentsByItemQty(itemsite_id, %2) * itemaltcapinvrat(item_id)) AS bucket%1")
.arg(columns++)
.arg(cursor->id());
_sohist->addColumn(formatDate(cursor->startDate()), _qtyColumn, Qt::AlignRight);
_columnDates.append(DatePair(cursor->startDate(), cursor->endDate()));
}
sql += " FROM itemsite, item, uom, warehous, prodcat "
"WHERE ( (itemsite_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (item_prodcat_id=prodcat_id)";
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_productCategory->isSelected())
sql += "AND (prodcat_id=:prodcat_id)";
else if (_productCategory->isPattern())
sql += "AND (prodcat_code ~ :prodcat_pattern) ";
sql += ") "
"GROUP BY prodcat_id, warehous_id, prodcat_code, uom, warehous_code;";
q.prepare(sql);
_warehouse->bindValue(q);
_productCategory->bindValue(q);
q.exec();
if (q.first())
{
Q3ValueVector<Numeric> totals(columns);;
XTreeWidgetItem *last = 0;
do
{
last = new XTreeWidgetItem( _sohist, last,
q.value("prodcat_id").toInt(),
q.value("warehous_id").toInt(),
q.value("prodcat_code"),
q.value("uom"),
q.value("warehous_code") );
for (int column = 1; column < columns; column++)
{
QString bucketName = QString("bucket%1").arg(column);
totals[column] += q.value(bucketName).toDouble();
if ( (_inventoryUnits->isChecked()) || (_capacityUnits->isChecked()) || (_altCapacityUnits->isChecked()) )
last->setText((column + 2), formatQty(q.value(bucketName).toDouble()));
else if (_salesDollars->isChecked())
last->setText((column + 2), formatMoney(q.value(bucketName).toDouble()));
}
}
while (q.next());
// Add the totals row
XTreeWidgetItem *total = new XTreeWidgetItem(_sohist, last, -1, QVariant(tr("Totals:")));
for (int column = 1; column < columns; column++)
{
if ( (_inventoryUnits->isChecked()) || (_capacityUnits->isChecked()) || (_altCapacityUnits->isChecked()) )
total->setText((column + 2), formatQty(totals[column].toDouble()));
else if (_salesDollars->isChecked())
total->setText((column + 2), formatMoney(totals[column].toDouble()));
}
}
}
