void dspCostedSingleLevelBOM::sFillList()
{
ParameterList params;
if (!setParams(params))
return;
dspCostedBOMBase::sFillList();
XSqlQuery qq;
qq.prepare( "SELECT formatCost(actcost(:item_id)) AS actual,"
" formatCost(stdcost(:item_id)) AS standard;" );
qq.bindValue(":item_id", _item->id());
qq.exec();
if (qq.first())
{
XTreeWidgetItem *last = new XTreeWidgetItem(list(), -1, -1, tr("Actual Cost"), "");
last->setText(11, qq.value("actual").toString());
last = new XTreeWidgetItem(list(), last, -1, -1, tr("Standard Cost"), "" );
last->setText(11, qq.value("standard").toString());
}
else if (ErrorReporter::error(QtCriticalMsg, this, tr("Error Retrieving BOM Cost Information"),
qq, __FILE__, __LINE__))
{
return;
}
}
void reconcileBankaccount::sChecksToggleCleared()
{
XTreeWidgetItem *item = (XTreeWidgetItem*)_checks->currentItem();
if(0 == item)
return;
_checks->scrollToItem(item);
q.prepare("SELECT toggleBankrecCleared(:bankrecid, :source, :sourceid) AS cleared");
q.bindValue(":bankrecid", _bankrecid);
q.bindValue(":sourceid", item->id());
if(item->altId()==1)
q.bindValue(":source", "GL");
else if(item->altId()==2)
q.bindValue(":source", "AD");
q.exec();
if(q.first())
item->setText(0, (q.value("cleared").toBool() ? tr("Yes") : tr("No") ));
else
{
populate();
if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
}
void printChecksReview::markSelected( int actionId )
{
QString action;
switch(actionId)
{
case ActionPrinted:
action = tr("Printed");
break;
case ActionVoided:
action = tr("Voided");
break;
case ActionReplaced:
action = tr("Replace");
break;
case ActionUnmark:
default:
actionId = -1;
action = "";
};
QList<XTreeWidgetItem*> selected = _checks->selectedItems();
for (int i = 0; i < selected.size(); i++)
{
XTreeWidgetItem *cursor = (XTreeWidgetItem*)selected[i];
cursor->setText(1, action);
cursor->setAltId(actionId);
}
}
void reconcileBankaccount::sChecksToggleCleared()
{
XSqlQuery reconcileChecksToggleCleared;
XTreeWidgetItem *item = (XTreeWidgetItem*)_checks->currentItem();
if(0 == item)
return;
_checks->scrollToItem(item);
double rate = item->rawValue("doc_exchrate").toDouble();
double baseamount = item->rawValue("base_amount").toDouble();
double amount = item->rawValue("amount").toDouble();
if (_allowEdit->isChecked() && item->text(0) != tr("Yes"))
{
ParameterList params;
params.append("transtype", "check");
params.append("bankaccntid", _bankaccnt->id());
params.append("bankrecid", _bankrecid);
params.append("sourceid", item->id());
if(item->altId()==1)
params.append("source", "GL");
else if(item->altId()==2)
params.append("source", "SL");
else if(item->altId()==3)
params.append("source", "AD");
toggleBankrecCleared newdlg(this, "", TRUE);
newdlg.set(params);
newdlg.exec();
populate();
}
else
{
reconcileChecksToggleCleared.prepare("SELECT toggleBankrecCleared(:bankrecid, :source, :sourceid, :currrate, :amount) AS cleared");
reconcileChecksToggleCleared.bindValue(":bankrecid", _bankrecid);
reconcileChecksToggleCleared.bindValue(":sourceid", item->id());
if(item->altId()==1)
reconcileChecksToggleCleared.bindValue(":source", "GL");
else if(item->altId()==2)
reconcileChecksToggleCleared.bindValue(":source", "SL");
else if(item->altId()==3)
reconcileChecksToggleCleared.bindValue(":source", "AD");
reconcileChecksToggleCleared.bindValue(":currrate", rate);
reconcileChecksToggleCleared.bindValue(":amount", amount);
reconcileChecksToggleCleared.exec();
if(reconcileChecksToggleCleared.first())
item->setText(0, (reconcileChecksToggleCleared.value("cleared").toBool() ? tr("Yes") : tr("No") ));
else
{
populate();
if (reconcileChecksToggleCleared.lastError().type() != QSqlError::NoError)
{
systemError(this, reconcileChecksToggleCleared.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
}
}
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 dspWoHistoryByItem::sFillList()
{
if (!checkParameters())
return;
_wo->clear();
QString sql( "SELECT wo_id,"
" formatWONumber(wo_id) AS wonumber,"
" wo_status, warehous_code,"
" formatQty(wo_qtyord) AS qtyord,"
" formatQty(wo_qtyrcv) AS qtyrcv,"
" formatDate(wo_startdate) AS startdate,"
" formatDate(wo_duedate) AS duedate,"
" ( (wo_startdate <= CURRENT_DATE) AND (wo_status IN ('O','E','S','R')) ) AS latestart,"
" (wo_duedate <= CURRENT_DATE) AS latedue,"
" formatCost(wo_postedvalue) AS value "
"FROM wo, itemsite, warehous "
"WHERE ((wo_itemsite_id=itemsite_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (itemsite_item_id=:item_id)"
" AND (wo_duedate BETWEEN :startDate AND :endDate)" );
if (_showOnlyTopLevel->isChecked())
sql += " AND ( (wo_ordtype<>'W') OR (wo_ordtype IS NULL) )";
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
sql += ") "
"ORDER BY wo_startdate DESC, wo_number, wo_subnumber;";
q.prepare(sql);
_dates->bindValue(q);
_warehouse->bindValue(q);
q.bindValue(":item_id", _item->id());
q.exec();
XTreeWidgetItem *last = 0;
while (q.next())
{
last = new XTreeWidgetItem( _wo, last, q.value("wo_id").toInt(),
q.value("wonumber"), q.value("wo_status"),
q.value("warehous_code"), q.value("qtyord"),
q.value("qtyrcv"), q.value("startdate"),
q.value("duedate") );
if (q.value("latestart").toBool())
last->setTextColor(5, "red");
if (q.value("latedue").toBool())
last->setTextColor(6, "red");
if (_showCost->isChecked())
last->setText(7, q.value("value").toString());
}
}
void printShippingForm::sEditShippingFormWatermark()
{
QList<QTreeWidgetItem*>selected = _shipformWatermarks->selectedItems();
for (int counter = 0; counter < selected.size(); counter++)
{
XTreeWidgetItem *cursor = static_cast<XTreeWidgetItem*>(selected[counter]);
ParameterList params;
params.append("watermark", cursor->text(1));
params.append("showPrices", (cursor->text(2) == tr("Yes")));
editICMWatermark newdlg(this, "", TRUE);
newdlg.set(params);
if (newdlg.exec() == XDialog::Accepted)
{
cursor->setText(1, newdlg.watermark());
cursor->setText(2, ((newdlg.showPrices()) ? tr("Yes") : tr("No")));
}
}
}
void configureIM::sEditShippingFormWatermark()
{
XTreeWidgetItem *cursor = (XTreeWidgetItem*)(_shipformWatermarks->currentItem());
if (cursor)
{
ParameterList params;
params.append("watermark", cursor->text(1));
params.append("showPrices", (cursor->text(2) == tr("Yes")));
editICMWatermark newdlg(this, "", TRUE);
newdlg.set(params);
if (newdlg.exec() == XDialog::Accepted)
{
cursor->setText(1, newdlg.watermark());
cursor->setText(2, ((newdlg.showPrices()) ? tr("Yes") : tr("No")));
}
}
}
void dspFinancialReport::sEditPeriodLabel()
{
XTreeWidgetItem * item = (XTreeWidgetItem*)_periods->currentItem();
if(!item)
return;
bool ok;
QString text = QInputDialog::getText( tr("Alternate Label"),
tr("Enter an alternate label for the period %1:").arg(item->text(0)),
QLineEdit::Normal, item->text(1), &ok, this );
if(ok)
item->setText(1, text);
}
void dspCostedSingleLevelBOM::sFillList()
{
dspCostedBOMBase::sFillList();
XSqlQuery qq;
qq.prepare( "SELECT formatCost(actcost(:item_id)) AS actual,"
" formatCost(stdcost(:item_id)) AS standard;" );
qq.bindValue(":item_id", _item->id());
qq.exec();
if (qq.first())
{
XTreeWidgetItem *last = new XTreeWidgetItem(list(), -1, -1, tr("Actual Cost"), "");
last->setText(11, qq.value("actual").toString());
last = new XTreeWidgetItem(list(), last, -1, -1, tr("Standard Cost"), "" );
last->setText(11, qq.value("standard").toString());
}
else if (qq.lastError().type() != QSqlError::NoError)
{
systemError(this, qq.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void unpostedPoReceipts::sFillList()
{
ParameterList fillp;
setParams(fillp);
MetaSQLQuery fillm = mqlLoad(":/sr/unpostedReceipts/FillListDetail.mql");
XSqlQuery fillq = fillm.toQuery(fillp);
_recv->clear();
XTreeWidgetItem *line = 0;
while (fillq.next())
{
line = new XTreeWidgetItem(_recv, line,
fillq.value("recv_id").toInt(),
fillq.value("recv_orderitem_id").toInt(),
fillq.value("recv_order_number"),
fillq.value("recv_order_type"),
fillq.value("vend_number"),
fillq.value("orderitem_linenumber"),
fillq.value("recv_duedate"),
fillq.value("item_number"),
fillq.value("uom_name"),
fillq.value("recv_vend_item_number"),
fillq.value("recv_vend_uom"),
fillq.value("qty_ordered"),
fillq.value("qty_received") );
line->setText(RECV_QTY_COL, fillq.value("recv_qty").toString());
line->setText(RECV_DATE_COL, fillq.value("recv_date").toString());
line->setText(RECV_GLDISTDATE_COL, fillq.value("recv_gldistdate").toString());
}
if (fillq.lastError().type() != QSqlError::None)
{
systemError(this, fillq.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void CRMAcctList::sFillList()
{
MetaSQLQuery mql(_query);
ParameterList params;
if (! _showInactive)
params.append("activeOnly");
XSqlQuery fillq = mql.toQuery(params);
XTreeWidgetItem* last = 0;
_listTab->clear();
while (fillq.next())
{
last = new XTreeWidgetItem(_listTab, last,
fillq.value("id").toInt(),
fillq.value("id").toInt(),
fillq.value("number"),
fillq.value("name"),
fillq.value("cntct_first_name"),
fillq.value("cntct_last_name"),
fillq.value("cntct_phone"),
fillq.value("street"),
fillq.value("addr_city"),
fillq.value("addr_state"),
fillq.value("addr_country"),
fillq.value("addr_postalcode"));
if(_subtype == CRMAcctLineEdit::Vend)
last->setText(10, fillq.value("type"));
}
if (fillq.lastError().type() != QSqlError::None)
{
QMessageBox::critical(this, tr("A System Error Occurred at %1::%2")
.arg(__FILE__).arg(__LINE__),
fillq.lastError().databaseText());
return;
}
}
void dspCostedIndentedBOM::sFillList()
{
dspCostedBOMBase::sFillList();
XSqlQuery qq;
qq.prepare( "SELECT formatCost(SUM(bomdata_actextendedcost)) AS actextendedcost,"
" formatCost(SUM(bomdata_stdextendedcost)) AS stdextendedcost,"
" formatCost(actcost(:item_id)) AS actual,"
" formatCost(stdcost(:item_id)) AS standard "
"FROM indentedbom(:item_id,"
" :revision_id,0,0)"
"WHERE (bomdata_bomwork_level=1) "
"GROUP BY actual, standard;" );
qq.bindValue(":item_id", _item->id());
qq.bindValue(":revision_id",_revision->id());
qq.exec();
if (qq.first())
{
XTreeWidgetItem *last = new XTreeWidgetItem(list(), -1, -1);
last->setText(1, tr("Total Cost"));
if(_useStandardCosts->isChecked())
last->setText(11, qq.value("stdextendedcost").toString());
else
last->setText(11, qq.value("actextendedcost").toString());
last = new XTreeWidgetItem( list(), -1, -1);
last->setText(1, tr("Actual Cost"));
last->setText(11, qq.value("actual").toString());
last = new XTreeWidgetItem( list(), -1, -1);
last->setText(1, tr("Standard Cost"));
last->setText(11, qq.value("standard").toString());
}
else if (qq.lastError().type() != QSqlError::NoError)
{
systemError(this, qq.lastError().databaseText(), __FILE__, __LINE__);
return;
}
list()->expandAll();
}
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 voucherItem::sToggleReceiving(QTreeWidgetItem *pItem)
{
double n;
QString s;
XTreeWidgetItem* item = (XTreeWidgetItem*)pItem;
if(item->id() == -1)
return;
if (item->text(3) == "Yes")
{
item->setText(3, "No");
if (item->text(0) == "Receiving")
{
n = _qtyToVoucher->toDouble();
_qtyToVoucher->setText(item->text(2));
n = n - _qtyToVoucher->toDouble();
_qtyToVoucher->setText(s.setNum(n));
n = _uninvoicedReceived->toDouble();
_uninvoicedReceived->setText(item->text(2));
n = n + _uninvoicedReceived->toDouble();
_uninvoicedReceived->setText(s.setNum(n));
}
else
{
n = _qtyToVoucher->toDouble();
_qtyToVoucher->setText(item->text(2));
n = n - _qtyToVoucher->toDouble();
_qtyToVoucher->setText(s.setNum(n));
n = _uninvoicedRejected->toDouble();
_uninvoicedRejected->setText(item->text(2));
n = n + _rejected->toDouble();
_uninvoicedRejected->setText(s.setNum(n));
}
}
else
{
item->setText(3, "Yes");
if (item->text(0) == "Receiving")
{
n = _qtyToVoucher->toDouble();
_qtyToVoucher->setText(item->text(2));
n = n + _qtyToVoucher->toDouble();
_qtyToVoucher->setText(s.setNum(n));
n = _uninvoicedReceived->toDouble();
_uninvoicedReceived->setText(item->text(2));
n = n - _uninvoicedReceived->toDouble();
_uninvoicedReceived->setText(s.setNum(n));
}
else
{
n = _qtyToVoucher->toDouble();
_qtyToVoucher->setText(item->text(2));
n = n + _qtyToVoucher->toDouble();
_qtyToVoucher->setText(s.setNum(n));
n = _uninvoicedRejected->toDouble();
_uninvoicedRejected->setText(item->text(2));
n = n + _uninvoicedRejected->toDouble();
_uninvoicedRejected->setText(s.setNum(n));
}
}
//Check PO Close flag
if ( ((_ordered->toDouble() <= (_received->toDouble() - _rejected->toDouble()))) && (_uninvoicedReceived->toDouble() == 0) && (_uninvoicedRejected->toDouble() == 0) )
_closePoitem->setChecked(true);
else
_closePoitem->setChecked(false);
// Save the voitem information
if (_voitemid != -1)
{
q.prepare( "UPDATE voitem "
"SET voitem_qty=:voitem_qty "
"WHERE (voitem_id=:voitem_id);" );
q.bindValue(":voitem_id", _voitemid);
}
else
{
// Get next voitem id
q.prepare("SELECT NEXTVAL('voitem_voitem_id_seq') AS voitemid");
q.exec();
if (q.first())
_voitemid = (q.value("voitemid").toInt());
else if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, _rejectedMsg.arg(q.lastError().databaseText()),
__FILE__, __LINE__);
reject();
return;
}
q.prepare( "INSERT INTO voitem "
"(voitem_id, voitem_vohead_id, voitem_poitem_id, voitem_close, voitem_qty, voitem_freight) "
"VALUES "
"(:voitem_id, :vohead_id, :poitem_id, :voitem_close, :voitem_qty, :voitem_freight);" );
}
q.bindValue(":voitem_id", _voitemid);
q.bindValue(":vohead_id", _voheadid);
q.bindValue(":poitem_id", _poitemid);
q.bindValue(":voitem_close", QVariant(_closePoitem->isChecked()));
q.bindValue(":voitem_qty", _qtyToVoucher->toDouble());
q.bindValue(":voitem_freight", _freightToVoucher->localValue());
q.exec();
if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, _rejectedMsg.arg(q.lastError().databaseText()),
__FILE__, __LINE__);
reject();
return;
}
//Update the receipt record
if (item->text(3) == "Yes")
{
if (item->altId() == 1)
q.prepare( "UPDATE recv "
"SET recv_vohead_id=:vohead_id,recv_voitem_id=:voitem_id "
"WHERE (recv_id=:target_id);" );
else if (item->altId() == 2)
q.prepare( "UPDATE poreject "
"SET poreject_vohead_id=:vohead_id,poreject_voitem_id=:voitem_id "
"WHERE (poreject_id=:target_id);" );
}
else
{
if (item->altId() == 1)
q.prepare( "UPDATE recv "
"SET recv_vohead_id=NULL,recv_voitem_id=NULL "
"WHERE ((recv_id=:target_id)"
" AND (recv_vohead_id=:vohead_id));" );
else if (item->altId() == 2)
q.prepare( "UPDATE poreject "
"SET poreject_vohead_id=NULL,poreject_voitem_id=NULL "
"WHERE ((poreject_id=:target_id)"
" AND (poreject_vohead_id=:vohead_id));" );
}
q.bindValue(":vohead_id", _voheadid);
q.bindValue(":voitem_id", _voitemid);
q.bindValue(":target_id", item->id());
q.exec();
if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, _rejectedMsg.arg(q.lastError().databaseText()),
__FILE__, __LINE__);
reject();
return;
}
}
void dspWoScheduleByParameterList::sFillList()
{
int woid = _wo->id();
_wo->clear();
QString sql( "SELECT wo_id, wo_ordtype,"
" CASE WHEN (wo_ordid IS NULL) THEN -1"
" ELSE wo_ordid"
" END AS orderid,"
" formatWONumber(wo_id) as wonumber,"
" wo_status, wo_priority, warehous_code,"
" item_number, (item_descrip1 || ' ' || item_descrip2) AS description,"
" uom_name,"
" formatQty(wo_qtyord) as ordered,"
" formatQty(wo_qtyrcv) as received,"
" formatDate(wo_startdate) as startdate,"
" formatDate(wo_duedate) as duedate,"
" ((wo_startdate<=CURRENT_DATE)"
" AND (wo_status IN ('O','E','S','R'))) AS latestart,"
" (wo_duedate<=CURRENT_DATE) AS latedue "
"FROM wo, itemsite, warehous, item, uom "
"WHERE ( (wo_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (wo_startdate BETWEEN <? value(\"startDate\") ?>"
" AND <? value(\"endDate\") ?>)"
"<? if exists(\"warehous_id\") ?>"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
"<? endif ?>"
"<? if exists(\"showOnlyRI\") ?>"
" AND (wo_status IN ('R','I'))"
"<? else ?>"
" AND (wo_status<>'C')"
"<? endif ?>"
"<? if exists(\"showOnlyTopLevel\") ?>"
" AND (wo_ordtype<>'W')"
"<? endif ?>"
"<? if exists(\"classcode_id\") ?>"
" AND (item_classcode_id=<? value(\"classcode_id\") ?>)"
"<? elseif exists(\"itemgrp_id\") ?>"
" AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem WHERE (itemgrpitem_itemgrp_id=<? value(\"itemgrp_id\") ?>)))"
"<? elseif exists(\"plancode_id\") ?>"
" AND (itemsite_plancode_id=<? value(\"plancode_id\") ?>)"
"<? elseif exists(\"wrkcnt_id\") ?>"
" AND (wo_id IN (SELECT wooper_wo_id FROM wooper WHERE (wooper_wrkcnt_id=<? value(\"wrkcnt_id\") ?>)))"
"<? elseif exists(\"classcode_pattern\") ?>"
" AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ <? value(\"classcode_pattern\") ?>)))"
"<? elseif exists(\"itemgrp_pattern\") ?>"
" AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem, itemgrp WHERE ( (itemgrpitem_itemgrp_id=itemgrp_id) AND (itemgrp_name ~ <? value(\"itemgrp_pattern\") ?>) ) ))"
"<? elseif exists(\"plancode_pattern\") ?>"
" AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ <? value(\"plancode_pattern\") ?>)))"
"<? elseif exists(\"wrkcnt_pattern\") ?>"
" AND (wo_id IN (SELECT wooper_wo_id FROM wooper, wrkcnt WHERE ((wooper_wrkcnt_id=wrkcnt_id) AND (wrkcnt_code ~ <? value(\"wrkcnt_pattern\") ?>))))"
"<? endif ?>"
") "
"ORDER BY "
"<? if exists(\"sortByStartDate\") ?>"
" wo_startdate,"
"<? elseif exists(\"sortByDueDate\") ?>"
" wo_duedate,"
"<? elseif exists(\"sortByItemNumber\") ?>"
" item_number,"
"<? endif ?>"
" wo_number, wo_subnumber" );
MetaSQLQuery mql(sql);
ParameterList params;
if (! setParams(params))
return;
q = mql.toQuery(params);
while (q.next())
{
XTreeWidgetItem *last = new XTreeWidgetItem( _wo, q.value("wo_id").toInt(), q.value("orderid").toInt(),
q.value("wo_ordtype"), q.value("wonumber"),
q.value("wo_status"), q.value("wo_priority"),
q.value("warehous_code"), q.value("item_number"),
q.value("description"), q.value("uom_name"),
q.value("ordered"), q.value("received"),
q.value("startdate") );
last->setText(11, q.value("duedate").toString());
if (q.value("latestart").toBool())
last->setTextColor(10, "red");
if (q.value("latedue").toBool())
{
last->setTextColor(11, "red");
last->setText(12, tr("Overdue"));
last->setTextColor(12, "red");
}
else
last->setText(12, tr("On Time"));
if(last->id() == woid)
_wo->setCurrentItem(last);
}
sHandleButtons();
}
void dspSlowMovingInventoryByClassCode::sFillList()
{
ParameterList params;
if (! setParams(params))
return;
_itemsite->clear();
QString sql( "SELECT itemsite_id, warehous_code, item_number,"
" (item_descrip1 || ' ' || item_descrip2) AS itemdescrip, uom_name,"
" itemsite_qtyonhand, formatQty(itemsite_qtyonhand) AS f_qoh,"
" formatDate(itemsite_datelastused) AS f_datelastused,"
" formatCost(cost) AS f_unitcost,"
" noNeg(cost * itemsite_qtyonhand) AS value,"
" formatMoney(noNeg(cost * itemsite_qtyonhand)) AS f_value,"
" cost "
"FROM ( SELECT itemsite_id, warehous_code, item_number,"
" item_descrip1, item_descrip2, uom_name,"
" itemsite_qtyonhand, itemsite_datelastused,"
"<? if exists(\"useActualCosts\") ?>"
" actcost(itemsite_item_id) "
"<? else ?>"
" stdcost(itemsite_item_id) "
"<? endif ?> AS cost "
"FROM itemsite, item, warehous, uom "
"WHERE ((itemsite_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (itemsite_active)"
" AND (itemsite_datelastused < <? value(\"cutoffDate\") ?>)"
"<? if exists(\"warehous_id\") ?>"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
"<? endif ?>"
"<? if exists(\"classcode_id\") ?>"
" AND (item_classcode_id=<? value(\"classcode_id\") ?>)"
"<? elseif exists(\"classcode_pattern\") ?>"
" AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE classcode_code ~ <? value(\"classcode_pattern\") ?>))"
"<? endif ?>"
") ) AS data "
"ORDER BY warehous_code, "
"<? if exists(\"sortByItem\") ?>"
" item_number"
"<? elseif exists(\"sortByDate\") ?>"
" itemsite_datelastused"
"<? else ?>"
" noNeg(cost * itemsite_qtyonhand) DESC"
"<? endif ?>"
";");
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
double qty = 0.0;
double value = 0.0;
XTreeWidgetItem *last = 0;
while (q.next())
{
last = new XTreeWidgetItem( _itemsite, last, q.value("itemsite_id").toInt(),
q.value("warehous_code"), q.value("item_number"),
q.value("itemdescrip"), q.value("uom_name"),
q.value("f_datelastused"), q.value("f_qoh") );
last->setText(6, q.value("f_unitcost").toString());
last->setText(7, q.value("f_value").toString());
if (q.value("itemsite_qtyonhand").toDouble() > 0.0)
{
qty += q.value("itemsite_qtyonhand").toDouble();
value += q.value("value").toDouble();
}
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
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));
}
}
}
void dspWoBufferStatusByParameterList::sFillList()
{
_wo->clear();
QString sql( "SELECT wo_id, wo_ordtype,"
" CASE WHEN (wo_ordid IS NULL) THEN -1"
" ELSE wo_ordid"
" END AS orderid,"
" formatWONumber(wo_id) as wonumber,"
" wo_status, wo_priority, warehous_code,"
" item_number, (item_descrip1 || ' ' || item_descrip2) AS description,"
" uom_name,"
" formatQty(wo_qtyord) as ordered,"
" formatQty(wo_qtyrcv) as received,"
" CASE WHEN (bufrsts_type='T') THEN :time"
" ELSE :stock"
" END AS bufrststype,"
" bufrsts_status,"
" (bufrsts_status>=66) AS emergency"
" FROM wo, itemsite, warehous, item, uom, bufrsts "
" WHERE ( (wo_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (bufrsts_target_type='W')"
" AND (bufrsts_target_id=wo_id)"
" AND (bufrsts_date=current_date)");
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_parameter->isSelected())
{
if (_parameter->type() == ClassCode)
sql += " AND (item_classcode_id=:classcode_id)";
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem WHERE (itemgrpitem_itemgrp_id=:itemgrp_id)))";
else if (_parameter->type() == PlannerCode)
sql += " AND (itemsite_plancode_id=:plancode_id)";
}
else if (_parameter->isPattern())
{
if (_parameter->type() == ClassCode)
sql += " AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ :classcode_pattern)))";
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem, itemgrp WHERE ( (itemgrpitem_itemgrp_id=itemgrp_id) AND (itemgrp_name ~ :itemgrp_pattern) ) ))";
else if (_parameter->type() == PlannerCode)
sql += " AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ :plancode_pattern)))";
}
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT DISTINCT itemgrpitem_item_id FROM itemgrpitem))";
if (_showOnlyRI->isChecked())
sql += " AND (wo_status IN ('R','I'))";
else
sql += " AND (wo_status<>'C')";
if (_showOnlyTopLevel->isChecked())
sql += " AND (wo_ordtype<>'W')";
sql += ") "
" ORDER BY bufrsts_status DESC, wo_number, wo_subnumber";
q.prepare(sql);
_warehouse->bindValue(q);
_parameter->bindValue(q);
q.bindValue(":stock", tr("Stock"));
q.bindValue(":time", tr("Time"));
q.exec();
XTreeWidgetItem * last = 0;
while (q.next())
{
last = new XTreeWidgetItem( _wo, last, q.value("wo_id").toInt(), q.value("orderid").toInt(),
q.value("wo_ordtype"), q.value("wonumber"),
q.value("wo_status"), q.value("wo_priority"),
q.value("warehous_code"), q.value("item_number"),
q.value("description"), q.value("uom_name"),
q.value("ordered"), q.value("received"),
q.value("bufrststype") );
last->setText(11, q.value("bufrsts_status").toString());
if (q.value("emergency").toBool())
last->setTextColor(11, "red");
}
}
void dspDetailedInventoryHistoryByLocation::sFillList()
{
_invhist->clear();
if (!_dates->startDate().isValid())
{
QMessageBox::critical( this, tr("Enter Start Date"),
tr("Please enter a valid Start Date.") );
_dates->setFocus();
return;
}
if (!_dates->endDate().isValid())
{
QMessageBox::critical( this, tr("Enter End Date"),
tr("Please enter a valid End Date.") );
_dates->setFocus();
return;
}
q.prepare( "SELECT invhist_id,"
" formatDateTime(invhist_transdate) AS transdate,"
" invhist_transtype, (invhist_ordtype || '-' || invhist_ordnumber) AS ordernumber,"
" invhist_invuom,"
" item_number, invdetail_lotserial,"
" formatQty(invdetail_qty) AS transqty,"
" formatQty(invdetail_qty_before) AS qohbefore,"
" formatQty(invdetail_qty_after) AS qohafter,"
" invhist_posted "
"FROM invdetail, invhist, itemsite, item "
"WHERE ( (invdetail_invhist_id=invhist_id)"
" AND (invhist_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (invdetail_location_id=:location_id)"
" AND (DATE(invhist_transdate) BETWEEN :startDate AND :endDate)"
" AND (transType(invhist_transtype, :transType)) ) "
"ORDER BY invhist_transdate DESC, invhist_transtype;" );
_dates->bindValue(q);
q.bindValue(":location_id", _location->id());
q.bindValue(":transType", _transType->id());
q.exec();
XTreeWidgetItem *last = 0;
while (q.next())
{
last = new XTreeWidgetItem( _invhist, last, q.value("invhist_id").toInt(),
q.value("transdate"),
q.value("invhist_transtype"),
q.value("ordernumber"),
q.value("item_number"),
q.value("invdetail_lotserial"),
q.value("invhist_invuom"),
q.value("transqty") );
if (q.value("invhist_posted").toBool())
{
last->setText(7, q.value("qohbefore").toString());
last->setText(8, q.value("qohafter").toString());
}
else
last->setTextColor("orange");
}
}
void dspPoItemsByVendor::sFillList()
{
_poitem->clear();
QString sql( "SELECT pohead_id, poitem_id, pohead_number,"
" poitem_status,"
" CASE WHEN(poitem_status='C') THEN <? value(\"closed\") ?>"
" WHEN(poitem_status='U') THEN <? value(\"unposted\") ?>"
" WHEN(poitem_status='O' AND ((poitem_qty_received-poitem_qty_returned) > 0) AND (poitem_qty_ordered>(poitem_qty_received-poitem_qty_returned))) THEN <? value(\"partial\") ?>"
" WHEN(poitem_status='O' AND ((poitem_qty_received-poitem_qty_returned) > 0) AND (poitem_qty_ordered=(poitem_qty_received-poitem_qty_returned))) THEN <? value(\"received\") ?>"
" WHEN(poitem_status='O') THEN <? value(\"open\") ?>"
" ELSE poitem_status"
" END AS poitemstatus,"
" CASE WHEN (itemsite_id IS NULL) THEN ( SELECT warehous_code"
" FROM warehous"
" WHERE (pohead_warehous_id=warehous_id) )"
" ELSE ( SELECT warehous_code"
" FROM warehous"
" WHERE (itemsite_warehous_id=warehous_id) )"
" END AS warehousecode,"
" COALESCE(item_number, (<? value(\"nonInv\") ?> || poitem_vend_item_number)) AS itemnumber,"
" COALESCE(item_descrip1, firstLine(poitem_vend_item_descrip)) AS itemdescrip,"
" COALESCE(uom_name, poitem_vend_uom) AS itemuom,"
" formatDate(poitem_duedate) AS f_duedate,"
" formatQty(poitem_qty_ordered) AS f_qtyordered,"
" formatQty(poitem_qty_received) AS f_qtyreceived,"
" formatQty(poitem_qty_returned) AS f_qtyreturned,"
" (poitem_duedate < CURRENT_DATE) AS late "
"FROM pohead,"
" poitem LEFT OUTER JOIN"
" ( itemsite JOIN item"
" ON (itemsite_item_id=item_id) JOIN uom ON (item_inv_uom_id=uom_id))"
" ON (poitem_itemsite_id=itemsite_id) "
"WHERE ((poitem_pohead_id=pohead_id)"
"<? if exists(\"warehous_id\") ?>"
" AND (((itemsite_id IS NULL) AND"
" (pohead_warehous_id=<? value(\"warehous_id\") ?>) ) OR"
" ((itemsite_id IS NOT NULL) AND"
" (itemsite_warehous_id=<? value(\"warehous_id\") ?>) ) )"
"<? endif ?>"
"<? if exists(\"agentUsername\") ?>"
" AND (pohead_agent_username=<? value(\"agentUsername\") ?>)"
"<? endif ?>"
"<? if exists(\"poNumber\") ?>"
" AND (pohead_number=<? value(\"poNumber\") ?>)"
"<? endif ?>"
"<? if exists(\"openItems\") ?>"
" AND (poitem_status='O')"
"<? endif ?>"
"<? if exists(\"closedItems\") ?>"
" AND (poitem_status='C')"
"<? endif ?>"
" AND (pohead_vend_id=<? value(\"vend_id\") ?>) ) "
"ORDER BY poitem_duedate, pohead_number, poitem_linenumber;" );
ParameterList params;
setParams(params);
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
if (q.first())
{
XTreeWidgetItem *last = 0;
do
{
last = new XTreeWidgetItem(_poitem, last,
q.value("pohead_id").toInt(),
q.value("poitem_id").toInt(),
q.value("pohead_number"),
q.value("warehousecode"),
q.value("poitemstatus"),
q.value("f_duedate"),
q.value("itemnumber"),
q.value("itemdescrip"),
q.value("itemuom"),
q.value("f_qtyordered"),
q.value("f_qtyreceived"),
q.value("f_qtyreturned") );
last->setText(POITEM_STATUS_COL, q.value("poitem_status"));
if (q.value("late").toBool())
last->setTextColor(3, "red");
}
while (q.next());
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void dspTrialBalances::sFillList()
{
_trialbal->clear();
QString sql( "SELECT accnt_id, period_id, accnt_descrip, trialbal_dirty,"
" formatDate(period_start) AS f_start,"
" formatDate(period_end) AS f_end,"
" formatGLAccount(accnt_id) AS account,"
" formatMoney(abs(trialbal_beginning)) AS f_beginning,"
" (trialbal_beginning*-1) AS beginning,"
" formatMoney(trialbal_debits) AS f_debits,"
" trialbal_debits AS debits,"
" formatMoney(trialbal_credits) AS f_credits,"
" trialbal_credits AS credits,"
" formatMoney(abs(trialbal_ending)) AS f_ending,"
" (trialbal_ending*-1) AS ending,"
" formatMoney(abs(trialbal_debits - trialbal_credits)) AS f_diff,"
" (trialbal_debits - trialbal_credits) AS diff "
"FROM trialbal, accnt, period "
"WHERE ( (trialbal_accnt_id=accnt_id)"
" AND (trialbal_period_id=period_id)"
"<? if exists(\"accnt_id\") ?>"
" AND (trialbal_accnt_id=<? value(\"accnt_id\") ?>)"
"<? endif ?>"
"<? if exists(\"period_id\") ?>"
" AND (period_id=<? value(\"period_id\") ?>)"
"<? endif ?>"
") "
"ORDER BY period_start, formatGLAccount(accnt_id);" );
ParameterList params;
setParams(params);
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
if (q.first())
{
double beginning = 0.0;
double ending = 0.0;
double debits = 0.0;
double credits = 0.0;
double diff = 0.0;
XTreeWidgetItem *last = 0;
do
{
beginning = q.value("beginning").toDouble();
ending = q.value("ending").toDouble();
debits = q.value("debits").toDouble();
credits = q.value("credits").toDouble();
diff = q.value("diff").toDouble();
last = new XTreeWidgetItem(_trialbal, last,
q.value("accnt_id").toInt(),
q.value("period_id").toInt(),
q.value("f_start"), q.value("f_end"),
q.value("account"), q.value("accnt_descrip"),
q.value("f_beginning"),
(beginning<0?tr("CR"):""),
q.value("f_debits"), q.value("f_credits"),
q.value("f_diff"), (diff<0?tr("CR"):""),
q.value("f_ending") );
last->setText(11, (ending<0?tr("CR"):""));
if (q.value("trialbal_dirty").toBool())
last->setTextColor(10, "orange");
}
while (q.next());
QString sql( "SELECT formatMoney(abs(SUM(trialbal_beginning))) AS f_beginning,"
" SUM(trialbal_beginning*-1) AS beginning,"
" formatMoney(SUM(trialbal_debits)) AS f_debits,"
" formatMoney(SUM(trialbal_credits)) AS f_credits,"
" formatMoney(abs(SUM(trialbal_ending))) AS f_ending,"
" SUM(trialbal_ending*-1) AS ending,"
" formatMoney(abs(SUM(trialbal_debits - trialbal_credits))) AS f_diff,"
" SUM(trialbal_debits - trialbal_credits) AS diff "
"FROM trialbal, period "
"WHERE ( (trialbal_period_id=period_id)"
"<? if exists(\"accnt_id\") ?>"
" AND (trialbal_accnt_id=<? value(\"accnt_id\") ?>)"
"<? endif ?>"
"<? if exists(\"period_id\") ?>"
" AND (period_id=<? value(\"period_id\") ?>)"
"<? endif ?>"
");" );
MetaSQLQuery totalmql(sql);
q = totalmql.toQuery(params);
if (q.first())
{
last = new XTreeWidgetItem(_trialbal, last, -1, -1,
"", "", tr("Total"), "",
q.value("f_beginning"),
(q.value("beginning").toDouble()<0?tr("CR"):""),
q.value("f_debits"),
q.value("f_credits"),
q.value("f_diff"),
(q.value("diff").toDouble()<0?tr("CR"):""),
q.value("f_ending") );
last->setText(11, (q.value("ending").toDouble()<0?tr("CR"):""));
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void viewCheckRun::sFillList()
{
_check->clear();
QString sql( "SELECT checkhead_id AS checkid, -1 AS checkitem_id,"
" formatBoolYN(checkhead_void) AS f_void,"
" formatBoolYN(checkhead_misc) AS f_misc,"
" formatBoolYN(checkhead_printed) AS f_printed,"
" CASE WHEN(checkhead_number=-1) THEN TEXT('Unspecified')"
" ELSE TEXT(checkhead_number)"
" END AS number,"
" (checkrecip_number || '-' || checkrecip_name) AS description,"
" formatDate(checkhead_checkdate) AS f_checkdate,"
" formatMoney(checkhead_amount) AS f_amount,"
" CASE WHEN (checkhead_misc) THEN 1"
" ELSE 0"
" END AS misc,"
" checkhead_number, currConcat(checkhead_curr_id) AS curr_concat, "
" 1 AS orderby "
"FROM checkhead LEFT OUTER JOIN"
" checkrecip ON ((checkrecip_id=checkhead_recip_id)"
" AND (checkrecip_type=checkhead_recip_type))"
"WHERE ((checkhead_bankaccnt_id=:bankaccnt_id) "
" AND (NOT checkhead_posted)"
" AND (NOT checkhead_replaced)"
" AND (NOT checkhead_deleted) ) "
"UNION SELECT checkitem_checkhead_id AS checkid, checkitem_id,"
" '' AS f_void, '' AS f_misc, '' AS f_printed,"
" CASE WHEN (checkitem_ranumber IS NOT NULL) THEN"
" checkitem_ranumber::TEXT"
" ELSE checkitem_vouchernumber"
" END AS number,"
" CASE WHEN (checkitem_cmnumber IS NOT NULL) THEN"
" checkitem_cmnumber::TEXT"
" ELSE checkitem_invcnumber"
" END AS description,"
" '' AS f_checkdate,"
" formatMoney(checkitem_amount) AS f_amount,"
" 0 AS misc, checkhead_number, "
" currConcat(checkitem_curr_id) AS curr_concat, "
" 2 AS orderby "
"FROM checkitem, checkhead "
"WHERE ( (checkitem_checkhead_id=checkhead_id)"
" AND (checkhead_bankaccnt_id=:bankaccnt_id) "
" AND (NOT checkhead_posted)"
" AND (NOT checkhead_replaced)"
" AND (NOT checkhead_deleted) ) "
"ORDER BY checkhead_number, checkid, orderby;" );
q.prepare(sql);
q.bindValue(":bankaccnt_id", _bankaccnt->id());
q.exec();
if (q.first())
{
XTreeWidgetItem *header = NULL;
int checkid = -1;
do
{
if (q.value("checkid").toInt() != checkid)
{
checkid = q.value("checkid").toInt();
header = new XTreeWidgetItem( _check, header, checkid, q.value("misc").toInt(),
q.value("f_void"), q.value("f_misc"),
q.value("f_printed"), q.value("number"),
q.value("description"), q.value("f_checkdate"),
q.value("f_amount"), q.value("curr_concat"));
}
else if (header)
{
XTreeWidgetItem *item = new XTreeWidgetItem( header, checkid, 0);
item->setText(3, q.value("number"));
item->setText(4, q.value("description"));
item->setText(6, q.value("f_amount"));
item->setText(7, q.value("curr_concat"));
}
}
while (q.next());
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void reconcileBankaccount::sReceiptsToggleCleared()
{
XTreeWidgetItem *item = (XTreeWidgetItem*)_receipts->currentItem();
XTreeWidgetItem *child = 0;
bool setto = true;
if(0 == item)
return;
_receipts->scrollToItem(item);
if(item->altId() == 3)
{
setto = item->text(0) == tr("No");
for (int i = 0; i < item->childCount(); i++)
{
child = item->child(i);
if(child->text(0) != (setto ? tr("Yes") : tr("No")))
{
q.prepare("SELECT toggleBankrecCleared(:bankrecid, :source, :sourceid) AS cleared");
q.bindValue(":bankrecid", _bankrecid);
q.bindValue(":sourceid", child->id());
if(child->altId()==1)
q.bindValue(":source", "GL");
else if(child->altId()==2)
q.bindValue(":source", "AD");
q.exec();
if(q.first())
child->setText(0, (q.value("cleared").toBool() ? tr("Yes") : tr("No") ));
else if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
}
item->setText(0, (setto ? tr("Yes") : tr("No")));
}
else
{
q.prepare("SELECT toggleBankrecCleared(:bankrecid, :source, :sourceid) AS cleared");
q.bindValue(":bankrecid", _bankrecid);
q.bindValue(":sourceid", item->id());
if(item->altId()==1)
q.bindValue(":source", "GL");
else if(item->altId()==2)
q.bindValue(":source", "AD");
q.exec();
if(q.first())
{
item->setText(0, (q.value("cleared").toBool() ? tr("Yes") : tr("No") ));
item = (XTreeWidgetItem*)item->QTreeWidgetItem::parent();
if(item != 0 && item->altId() == 3)
{
setto = true;
for (int i = 0; i < item->childCount(); i++)
{
setto = (setto && (item->child(i)->text(0) == tr("Yes")));
}
item->setText(0, (setto ? tr("Yes") : tr("No")));
}
}
else
{
populate();
if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
}
}
/*
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();
}
void dspInventoryAvailabilityBySourceVendor::sFillList()
{
_availability->clear();
if ((_byDate->isChecked()) && (!_date->isValid()))
{
QMessageBox::critical( this, tr("Enter Valid Date"),
tr( "You have choosen to view Inventory Availabilty as of a given date but have not\n"
"indicated the date. Please enter a valid date." ) );
_date->setFocus();
return;
}
if ((_byDates->isChecked()) && ( (!_startDate->isValid()) || (!_endDate->isValid()) ) )
{
QMessageBox::critical( this, tr("Enter Dates"),
tr( "You have choosen to view Inventory Availabilty as of a given Start and End Date but have not\n"
"indicated the dates. Please enter valid dates." ) );
_startDate->setFocus();
return;
}
QString sql( "SELECT itemsite_id,"
" vend_number,"
" item_number, (item_descrip1 || ' ' || item_descrip2) AS description,"
" warehous_id, warehous_code, itemsite_leadtime,"
" formatQty(qoh) AS f_qoh,"
" formatQty(noNeg(qoh - allocated)) AS f_unallocated,"
" formatQty(noNeg(allocated)) AS f_allocated,"
" formatQty(ordered) AS f_ordered,"
" formatQty(reorderlevel) AS f_reorderlevel,"
" formatQty(outlevel) AS f_outlevel,"
" (qoh - allocated + ordered) AS available,"
" formatQty(qoh - allocated + ordered) AS f_available,"
" ((qoh - allocated + ordered) < 0) AS stockout,"
" ((qoh - allocated + ordered) <= reorderlevel) AS reorder "
"FROM ( SELECT itemsite_id, vend_number,"
" item_number, item_descrip1, item_descrip2,"
" warehous_id, warehous_code, itemsite_leadtime,"
" CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel,"
" CASE WHEN(itemsite_useparams) THEN itemsite_ordertoqty ELSE 0.0 END AS outlevel,"
" itemsite_qtyonhand AS qoh," );
if (_leadTime->isChecked())
sql += " qtyAllocated(itemsite_id, itemsite_leadtime) AS allocated,"
" qtyOrdered(itemsite_id, itemsite_leadtime) AS ordered ";
else if (_byDays->isChecked())
sql += " qtyAllocated(itemsite_id, :days) AS allocated,"
" qtyOrdered(itemsite_id, :days) AS ordered ";
else if (_byDate->isChecked())
sql += " qtyAllocated(itemsite_id, (:date - CURRENT_DATE)) AS allocated,"
" qtyOrdered(itemsite_id, (:date - CURRENT_DATE)) AS ordered ";
else if (_byDates->isChecked())
sql += " qtyAllocated(itemsite_id, :startDate, :endDate) AS allocated,"
" qtyOrdered(itemsite_id, :startDate, :endDate) AS ordered ";
sql += "FROM item, itemsite, warehous, vend, itemsrc "
"WHERE ( (itemsite_active)"
" AND (itemsite_item_id=item_id)"
" AND (itemsrc_item_id=item_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (itemsrc_vend_id=vend_id)";
if (_selectedVendor->isChecked())
sql += " AND (vend_id=:vend_id)";
else if (_selectedVendorType->isChecked())
sql += " AND (vend_vendtype_id=:vendtype_id)";
else if (_vendorTypePattern->isChecked())
sql += " AND (vend_vendtype_id IN (SELECT vendtype_id FROM vendtype WHERE (vendtype_code ~ :vendtype_code))) ";
if (_warehouse->isSelected())
sql += " AND (warehous_id=:warehous_id)";
sql += ") ) AS data ";
if (_showReorder->isChecked())
{
sql += "WHERE ( ((qoh - allocated + ordered) <= reorderlevel) ";
if (_ignoreReorderAtZero->isChecked())
sql += " AND (NOT ( ((qoh - allocated + ordered) = 0) AND (reorderlevel = 0)) ) ) ";
else
sql += ") ";
}
else if (_showShortages->isChecked())
sql += "WHERE ((qoh - allocated + ordered) < 0) ";
if (_preferences->boolean("ListNumericItemNumbersFirst"))
sql += "ORDER BY toNumeric(item_number, 999999999999999), item_number, warehous_code DESC;";
else
sql += "ORDER BY vend_number, item_number, warehous_code DESC;";
q.prepare(sql);
q.bindValue(":days", _days->value());
q.bindValue(":date", _date->date());
q.bindValue(":startDate", _startDate->date());
q.bindValue(":endDate", _endDate->date());
q.bindValue(":vend_id", _vend->id());
q.bindValue(":vendtype_id", _vendorTypes->id());
q.bindValue(":vendtype_code", _vendorType->text().upper());
_warehouse->bindValue(q);
q.exec();
XTreeWidgetItem * last = 0;
while (q.next())
{
last = new XTreeWidgetItem( _availability, last,
q.value("itemsite_id").toInt(),
q.value("vend_number"), q.value("item_number"),
q.value("description"), q.value("warehous_code"),
q.value("itemsite_leadtime"), q.value("f_qoh"),
q.value("f_allocated"), q.value("f_unallocated"),
q.value("f_ordered"), q.value("f_reorderlevel"),
q.value("f_outlevel") );
last->setText(11, q.value("f_available").toString());
if (_byDates->isChecked())
last->setTextColor(5, "grey");
if (q.value("stockout").toBool())
last->setTextColor(11, "red");
else if (q.value("reorder").toBool())
last->setTextColor(11, "orange");
}
}
void dspWoScheduleByParameterList::sFillList()
{
int woid = _wo->id();
_wo->clear();
QString sql( "SELECT wo_id, wo_ordtype,"
" CASE WHEN (wo_ordid IS NULL) THEN -1"
" ELSE wo_ordid"
" END AS orderid,"
" formatWONumber(wo_id) as wonumber,"
" wo_status, wo_priority, warehous_code,"
" item_number, (item_descrip1 || ' ' || item_descrip2) AS description,"
" uom_name,"
" formatQty(wo_qtyord) as ordered,"
" formatQty(wo_qtyrcv) as received,"
" formatDate(wo_startdate) as startdate,"
" formatDate(wo_duedate) as duedate,"
" ((wo_startdate <= CURRENT_DATE) AND (wo_status IN ('O','E','S','R'))) AS latestart,"
" (wo_duedate<=CURRENT_DATE) AS latedue "
"FROM wo, itemsite, warehous, item, uom "
"WHERE ( (wo_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (item_inv_uom_id=uom_id)"
" AND (itemsite_warehous_id=warehous_id)"
" AND (wo_startdate BETWEEN :startDate AND :endDate)" );
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_parameter->isSelected())
{
if (_parameter->type() == ClassCode)
sql += " AND (item_classcode_id=:classcode_id)";
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem WHERE (itemgrpitem_itemgrp_id=:itemgrp_id)))";
else if (_parameter->type() == PlannerCode)
sql += " AND (itemsite_plancode_id=:plancode_id)";
else if (_parameter->type() == WorkCenter)
sql += " AND (wo_id IN (SELECT wooper_wo_id FROM wooper WHERE (wooper_wrkcnt_id=:wrkcnt_id)))";
}
else if (_parameter->isPattern())
{
if (_parameter->type() == ClassCode)
sql += " AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ :classcode_pattern)))";
else if (_parameter->type() == ItemGroup)
sql += " AND (item_id IN (SELECT itemgrpitem_item_id FROM itemgrpitem, itemgrp WHERE ( (itemgrpitem_itemgrp_id=itemgrp_id) AND (itemgrp_name ~ :itemgrp_pattern) ) ))";
else if (_parameter->type() == PlannerCode)
sql += " AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ :plancode_pattern)))";
else if (_parameter->type() == WorkCenter)
sql += " AND (wo_id IN (SELECT wooper_wo_id FROM wooper, wrkcnt WHERE ((wooper_wrkcnt_id=wrkcnt_id) AND (wrkcnt_code ~ :wrkcnt_pattern))))";
}
if (_showOnlyRI->isChecked())
sql += " AND (wo_status IN ('R','I'))";
else
sql += " AND (wo_status<>'C')";
if (_showOnlyTopLevel->isChecked())
sql += " AND (wo_ordtype<>'W')";
sql += ") ";
if(_sortByStartDate->isChecked())
sql += " ORDER BY wo_startdate, wo_number, wo_subnumber";
else if(_sortByDueDate->isChecked())
sql += " ORDER BY wo_duedate, wo_number, wo_subnumber";
else
sql += " ORDER BY item_number, wo_number, wo_subnumber";
q.prepare(sql);
_warehouse->bindValue(q);
_parameter->bindValue(q);
_dates->bindValue(q);
q.exec();
while (q.next())
{
XTreeWidgetItem *last = new XTreeWidgetItem( _wo, q.value("wo_id").toInt(), q.value("orderid").toInt(),
q.value("wo_ordtype"), q.value("wonumber"),
q.value("wo_status"), q.value("wo_priority"),
q.value("warehous_code"), q.value("item_number"),
q.value("description"), q.value("uom_name"),
q.value("ordered"), q.value("received"),
q.value("startdate") );
last->setText(11, q.value("duedate").toString());
if (q.value("latestart").toBool())
last->setTextColor(10, "red");
if (q.value("latedue").toBool())
{
last->setTextColor(11, "red");
last->setText(12, tr("Overdue"));
last->setTextColor(12, "red");
}
else
last->setText(12, tr("On Time"));
if(last->id() == woid)
_wo->setCurrentItem(last);
}
sHandleButtons();
}
void dspCheckRegister::sFillList()
{
MetaSQLQuery mql = mqlLoad(":/ap/displays/CheckRegister/FillListDetail.mql");
ParameterList params;
if (!setParams(params))
return;
q = mql.toQuery(params);
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
_check->clear();
XTreeWidgetItem *header = NULL;
int checkid = -1;
while (q.next())
{
if (q.value("checkid").toInt() != checkid)
{
checkid = q.value("checkid").toInt();
header = new XTreeWidgetItem( _check, header, checkid, q.value("extra").toInt(),
q.value("f_void"), q.value("f_misc"),
q.value("f_printed"), q.value("f_posted"), q.value("number"),
q.value("description"), q.value("f_checkdate"),
q.value("f_amount"), q.value("currAbbr"));
}
else if (header)
{
XTreeWidgetItem *item = new XTreeWidgetItem( header, checkid, 0);
item->setText(4, q.value("number"));
item->setText(5, q.value("description"));
item->setText(7, q.value("f_amount"));
}
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
if(_showDetail->isChecked())
_check->expandAll();
QString tots("SELECT formatMoney(SUM(currToCurr(checkhead_curr_id,"
" bankaccnt_curr_id,"
" checkhead_amount,"
" checkhead_checkdate))) AS f_amount,"
" currConcat(bankaccnt_curr_id) AS currAbbr "
"FROM checkhead, bankaccnt "
"WHERE ((NOT checkhead_void)"
" AND (checkhead_checkdate BETWEEN <? value(\"startDate\") ?> AND <? value(\"endDate\") ?>) "
" AND (bankaccnt_id=checkhead_bankaccnt_id) "
" AND (checkhead_bankaccnt_id=<? value(\"bankaccnt_id\") ?>)"
" <? if exists(\"check_number\") ?>"
" AND (checkhead_number=<? value(\"check_number\") ?>)"
" <? endif ?>"
" <? if exists(\"recip\") ?>"
" <? if exists(\"recip_type_v\") ?>"
" AND (checkhead_recip_type = 'V' )"
" <? endif ?>"
" <? if exists(\"recip_type_c\") ?>"
" AND (checkhead_recip_type = 'C' )"
" <? endif ?>"
" <? if exists(\"recip_type_t\") ?>"
" AND (checkhead_recip_type = 'T' )"
" <? endif ?>"
" <? if exists(\"recip_id\") ?>"
" AND (checkhead_recip_id = <? value(\"recip_id\") ?> )"
" <? endif ?>"
" <? endif ?>)"
" GROUP BY bankaccnt_curr_id;" );
MetaSQLQuery totm(tots);
q = totm.toQuery(params); // reused from above
if(q.first())
{
_total->setText(q.value("f_amount").toString());
_totalCurr->setText(q.value("currAbbr").toString());
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
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 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;
}
}