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