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 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 enterPoReturn::sFillList() { _poitem->clear(); if (_po->id() != -1) { q.prepare( "SELECT 1, vendaddr_addr_id AS addr_id " "FROM vendaddrinfo, pohead " "WHERE ((vendaddr_id=pohead_vendaddr_id)" " AND (pohead_id=:pohead_id))" "UNION " "SELECT 2, vend_addr_id AS addr_id " "FROM vendinfo, pohead " "WHERE ((vend_id=pohead_vend_id)" " AND (pohead_id=:pohead_id)) " "ORDER BY 1 " "LIMIT 1;"); q.bindValue(":pohead_id", _po->id()); q.exec(); if (q.first()) _returnAddr->setId(q.value("addr_id").toInt()); else if (q.lastError().type() != QSqlError::None) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } QString sql( "SELECT poitem_id, poitem_linenumber," " warehous_code, " " COALESCE(item_number, <? value(\"nonInventory\") ?>)," " COALESCE(uom_name, <? value(\"na\") ?>)," " poitem_vend_item_number, poitem_vend_uom," " formatQty(poitem_qty_ordered)," " formatQty(poitem_qty_received)," " formatQty(poitem_qty_returned)," " formatQty( COALESCE( ( SELECT SUM(poreject_qty)" " FROM poreject" " WHERE ( (poreject_poitem_id=poitem_id)" " AND (NOT poreject_posted) ) ), 0 ) ) " "FROM poitem LEFT OUTER JOIN " " ( itemsite " " JOIN item ON (itemsite_item_id=item_id) " " JOIN uom ON (item_inv_uom_id=uom_id) " " JOIN whsinfo ON (itemsite_warehous_id=warehous_id) " "<? if exists(\"selectedOnly\") ?>" " JOIN usrsite ON (warehous_id=usrsite_warehous_id) " "<? endif ?>" " ) ON (poitem_itemsite_id=itemsite_id) " "WHERE (poitem_pohead_id= <? value(\"pohead_id\") ?>) " "<? if exists(\"selectedOnly\") ?>" " AND (usrsite_username=current_user) " "<? endif ?>" "ORDER BY poitem_linenumber;" ); ParameterList params; params.append("na", tr("N/A")); params.append("nonInventory", tr("Non-Inventory")); params.append("pohead_id", _po->id()); if (_preferences->boolean("selectedSites")) params.append("selectedOnly"); MetaSQLQuery mql(sql); q = mql.toQuery(params); _poitem->populate(q); if (q.lastError().type() != QSqlError::None) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } } }
void dspInventoryAvailabilityByCustomerType::sFillList() { _avail->clear(); QString sql( "SELECT itemsite_id, coitem_id," " cohead_id, cohead_number, (cust_number||'-'||cust_name) AS custname," " item_number, item_description, uom_name, item_picklist," " qoh, formatQty(qoh) AS f_qoh,sobalance," " formatQty(sobalance) AS f_sobalance," " formatQty(allocated) AS f_allocated," " ordered, formatQty(ordered) AS f_ordered," " (qoh + ordered - sobalance) AS woavail," "<? if exists(\"useReservationNetting\") ?>" " formatQty(coitem_qtyreserved) AS f_soavail," "<? else ?>" " formatQty(qoh + ordered - sobalance) AS f_soavail," "<? endif ?>" " (qoh + ordered - allocated) AS totalavail," " formatQty(qoh + ordered - allocated) AS f_totalavail," " atshipping,formatQty(atshipping) AS f_atshipping," " formatDate(coitem_scheddate) AS f_scheddate," " (coitem_qtyreserved > 0 AND sobalance > coitem_qtyreserved) AS partialreservation," " ((sobalance - coitem_qtyreserved) = 0) AS fullreservation," " reorderlevel " "<? if exists(\"showWoSupply\") ?>, " " wo_id," " wo_status," " wo_number," " wo_ordered," " formatQty(wo_ordered) AS f_wo_ordered," " formatDate(wo_startdate) AS f_wo_startdate, " " formatDate(wo_duedate) AS f_wo_duedate," " COALESCE(wo_latestart,false) AS wo_latestart," " COALESCE(wo_latedue,false) AS wo_latedue " "<? endif ?>" "FROM ( SELECT itemsite_id, coitem_id," " cohead_id, cohead_number, cust_number, cust_name," " item_number, (item_descrip1 || ' ' || item_descrip2) AS item_description," " uom_name, item_picklist," " noNeg(itemsite_qtyonhand) AS qoh," " noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS sobalance," " qtyAllocated(itemsite_id, coitem_scheddate) AS allocated," " qtyOrdered(itemsite_id, coitem_scheddate) AS ordered," " qtyatshipping(coitem_id) AS atshipping," " coitem_qtyreserved," " coitem_scheddate," " CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel " "<? if exists(\"showWoSupply\") ?>, " " COALESCE(wo_id,-1) AS wo_id," " formatwonumber(wo_id) AS wo_number," " noNeg((wo_qtyord-wo_qtyrcv)) AS wo_ordered," " wo_status, wo_startdate, wo_duedate," " ((wo_startdate <= CURRENT_DATE) AND (wo_status IN ('O','E','S','R'))) AS wo_latestart," " (wo_duedate<=CURRENT_DATE) AS wo_latedue " "<? endif ?>" " FROM cohead, cust, itemsite, item, uom, coitem " "<? if exists(\"showWoSupply\") ?> " " LEFT OUTER JOIN wo" " ON ((coitem_itemsite_id=wo_itemsite_id)" " AND (wo_status IN ('E','R','I'))" " AND (wo_qtyord-wo_qtyrcv > 0)" " AND (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned-qtyatshipping(coitem_id)) > " " (SELECT itemsite_qtyonhand FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))" "<? endif ?>" " WHERE ( (coitem_cohead_id=cohead_id)" " AND (coitem_itemsite_id=itemsite_id)" " AND (itemsite_item_id=item_id)" " AND (item_inv_uom_id=uom_id)" " AND (coitem_status <> 'X')" " AND (cohead_cust_id=cust_id)" "<? if 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_pattern\") ?>)))" "<? endif ?>" ")) AS data " " <? if exists(\"onlyShowShortages\") ?>" "WHERE ( ((qoh + ordered - allocated) < 0)" " OR ((qoh + ordered - sobalance) < 0) ) " "<? endif ?>" "ORDER BY cohead_id, cohead_number, item_number" "<? if exists(\"showWoSupply\") ?> ," "wo_duedate" "<? endif ?>" ";"); ParameterList params; _custtype->appendValue(params); if (_onlyShowShortages->isChecked()) params.append("onlyShowShortages"); if (_showWoSupply->isChecked()) params.append("showWoSupply"); if (_useReservationNetting->isChecked()) params.append("useReservationNetting"); MetaSQLQuery mql(sql); q = mql.toQuery(params); if (q.first()) { XTreeWidgetItem *coitem = NULL, *cohead = NULL; XTreeWidgetItem *wo = NULL; int coitemid = -1; int coheadid = -1; do { if (coitemid != q.value("coitem_id").toInt()) { if(coheadid != q.value("cohead_id").toInt()) { coheadid = q.value("cohead_id").toInt(); cohead = new XTreeWidgetItem(_avail, cohead, coheadid, -2, q.value("cohead_number"), q.value("custname")); } coitemid = q.value("coitem_id").toInt(); coitem = new XTreeWidgetItem( cohead, coitem, q.value("itemsite_id").toInt(), q.value("coitem_id").toInt(), q.value("item_number"), q.value("item_description"), q.value("uom_name"), q.value("f_qoh"), q.value("f_sobalance"), q.value("f_allocated"), q.value("f_ordered"), q.value("f_soavail"), q.value("f_totalavail"), q.value("f_atshipping"), q.value("f_scheddate") ); if (q.value("qoh").toDouble() < 0) coitem->setTextColor(3, "red"); else if (q.value("qoh").toDouble() < q.value("reorderlevel").toDouble()) coitem->setTextColor(3, "orange"); if (q.value("woavail").toDouble() < 0.0) coitem->setTextColor(7, "red"); else if (q.value("woavail").toDouble() <= q.value("reorderlevel").toDouble()) coitem->setTextColor(7, "orange"); if (q.value("totalavail").toDouble() < 0.0) coitem->setTextColor(8, "red"); else if (q.value("totalavail").toDouble() <= q.value("reorderlevel").toDouble()) coitem->setTextColor(8, "orange"); if(_useReservationNetting->isChecked()) { if(q.value("partialreservation").toBool()) { coitem->setTextColor(0, "blue"); coitem->setTextColor(1, "blue"); coitem->setTextColor(7, "blue"); } else if(q.value("fullreservation").toBool()) { coitem->setTextColor(0, "green"); coitem->setTextColor(1, "green"); coitem->setTextColor(7, "green"); } } } if ((coitem) && (_showWoSupply->isChecked()) && (q.value("wo_id").toInt() != -1) ) { wo = new XTreeWidgetItem( coitem, wo, q.value("itemsite_id").toInt(),-1, q.value("wo_number"),"", q.value("wo_status"), "", "", "", q.value("f_wo_ordered"), q.value("f_wo_startdate"), q.value("f_wo_duedate"), "" ); if (q.value("wo_latestart").toBool()) wo->setTextColor(7, "red"); if (q.value("wo_latedue").toBool()) wo->setTextColor(8, "red"); } } while (q.next()); } if (q.lastError().type() != QSqlError::None) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } _avail->expandAll(); }
void vendor::populate() { MetaSQLQuery mql( "SELECT vendinfo.*, crmacct_id, " "<? if exists(\"key\") ?>" " CASE WHEN LENGTH(vend_ach_routingnumber) > 0 THEN" " formatbytea(decrypt(setbytea(vend_ach_routingnumber)," " setbytea(<? value(\"key\") ?>), 'bf'))" " ELSE '' END AS routingnum," " CASE WHEN LENGTH(vend_ach_accntnumber) > 0 THEN" " formatbytea(decrypt(setbytea(vend_ach_accntnumber)," " setbytea(<? value(\"key\") ?>), 'bf'))" " ELSE '' END AS accntnum " "<? else ?>" " <? value(\"na\") ?> AS routingnum," " <? value(\"na\") ?> AS accntnum " "<? endif ?>" "FROM vendinfo " " JOIN crmacct ON (vend_id=crmacct_vend_id) " "WHERE (vend_id=<? value(\"vend_id\") ?>);" ); ParameterList params; params.append("vend_id", _vendid); params.append("key", omfgThis->_key); params.append("na", tr("N/A")); q = mql.toQuery(params); if (q.first()) { _notice = FALSE; _cachedNumber = q.value("vend_number").toString(); _number->setText(q.value("vend_number")); _accountNumber->setText(q.value("vend_accntnum")); _vendtype->setId(q.value("vend_vendtype_id").toInt()); _active->setChecked(q.value("vend_active").toBool()); _name->setText(q.value("vend_name")); _contact1->setId(q.value("vend_cntct1_id").toInt()); _contact1->setSearchAcct(q.value("crmacct_id").toInt()); _contact2->setId(q.value("vend_cntct2_id").toInt()); _contact2->setSearchAcct(q.value("crmacct_id").toInt()); _address->setId(q.value("vend_addr_id").toInt()); _defaultTerms->setId(q.value("vend_terms_id").toInt()); _defaultShipVia->setText(q.value("vend_shipvia").toString()); _defaultCurr->setId(q.value("vend_curr_id").toInt()); _poItems->setChecked(q.value("vend_po").toBool()); _restrictToItemSource->setChecked(q.value("vend_restrictpurch").toBool()); _receives1099->setChecked(q.value("vend_1099").toBool()); _match->setChecked(q.value("vend_match").toBool()); _qualified->setChecked(q.value("vend_qualified").toBool()); _notes->setText(q.value("vend_comments").toString()); _poComments->setText(q.value("vend_pocomments").toString()); _taxzone->setId(q.value("vend_taxzone_id").toInt()); if (q.value("vend_fobsource").toString() == "V") { _useVendorFOB->setChecked(TRUE); _vendorFOB->setText(q.value("vend_fob")); } else _useWarehouseFOB->setChecked(TRUE); _achGroup->setChecked(q.value("vend_ach_enabled").toBool()); _routingNumber->setText(q.value("routingnum").toString()); _achAccountNumber->setText(q.value("accntnum").toString()); _useACHSpecial->setChecked(! q.value("vend_ach_use_vendinfo").toBool()); _individualId->setText(q.value("vend_ach_indiv_number").toString()); _individualName->setText(q.value("vend_ach_indiv_name").toString()); _accountType->setCode(q.value("vend_ach_accnttype").toString()); sFillAddressList(); sFillTaxregList(); _comments->setId(_vendid); _crmacctid = q.value("crmacct_id").toInt(); _address->setSearchAcct(_crmacctid); emit newId(_vendid); } else if (q.lastError().type() != QSqlError::NoError) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } else { systemError(this, tr("Could not find the Vendor information. Perhaps the " "Vendor and CRM Account have been disconnected."), __FILE__, __LINE__); return; } emit populated(); }
void items::sFillList( int pItemid, bool pLocal ) { QString sql( "SELECT item_id, item_number, formatBoolYN(item_active)," " (item_descrip1 || ' ' || item_descrip2), classcode_code," " CASE WHEN (item_type='P') THEN text(<? value(\"purchased\") ?>)" " WHEN (item_type='M') THEN text(<? value(\"manufactured\") ?>)" " WHEN (item_type='F') THEN text(<? value(\"phantom\") ?>)" " WHEN (item_type='B') THEN text(<? value(\"breeder\") ?>)" " WHEN (item_type='C') THEN text(<? value(\"coProduct\") ?>)" " WHEN (item_type='Y') THEN text(<? value(\"byProduct\") ?>)" " WHEN (item_type='R') THEN text(<? value(\"reference\") ?>)" " WHEN (item_type='S') THEN text(<? value(\"costing\") ?>)" " WHEN (item_type='T') THEN text(<? value(\"tooling\") ?>)" " WHEN (item_type='A') THEN text(<? value(\"assortment\") ?>)" " WHEN (item_type='O') THEN text(<? value(\"outside\") ?>)" " WHEN (item_type='L') THEN text(<? value(\"planning\") ?>)" " ELSE text(<? value(\"error\") ?>)" " END," " uom_name " "FROM item, classcode, uom " "WHERE ( (item_classcode_id=classcode_id)" " AND (item_inv_uom_id=uom_id)" "<? if exists(\"showPurchased\") ?>" " AND (item_type IN ('P', 'O'))" "<? elseif exists(\"showManufactured\") ?>" " AND (item_type IN ('M', 'F', 'B'))" "<? elseif exists(\"showSold\") ?>" " AND (item_sold)" "<? endif ?>" "<? if exists(\"onlyShowActive\") ?>" " AND (item_active)" "<? endif ?>" ") ORDER BY" "<? if exists(\"ListNumericItemNumbersFirst\") ?>" " toNumeric(item_number, 999999999999999)," "<? endif ?>" " item_number;" ); ParameterList params; if(_showPurchased->isChecked()) params.append("showPurchased"); else if(_showManufactured->isChecked()) params.append("showManufactured"); else if(_showSold->isChecked()) params.append("showSold"); if (!_showInactive->isChecked()) params.append("onlyShowActive"); if (_preferences->boolean("ListNumericItemNumbersFirst")) params.append("ListNumericItemNumbersFirst"); params.append("purchased", tr("Purchased")); params.append("manufactured", tr("Manufactured")); params.append("phantom", tr("Phantom")); params.append("breeder", tr("Breeder")); params.append("coProduct", tr("Co-Product")); params.append("byProduct", tr("By-Product")); params.append("reference", tr("Reference")); params.append("costing", tr("Costing")); params.append("tooling", tr("Tooling")); params.append("outside", tr("Outside Process")); params.append("planning", tr("Planning")); params.append("assortment", tr("Assortment")); params.append("error", tr("Error")); MetaSQLQuery mql(sql); q = mql.toQuery(params); if ((pItemid != -1) && (pLocal)) _item->populate(q, pItemid); else _item->populate(q); }
void returnAuthorizationWorkbench::sFillListDue() { _radue->clear(); if (_cust->isChecked() && !_custInfo->isValid()) { QMessageBox::information( this, tr("Customer not selected"), tr("<p>Please select a customer.") ); _custInfo->setFocus(); return; } //Fill Due Credit List if ((_creditmemo->isChecked()) || (_check->isChecked()) || (_creditcard->isChecked())) { bool bc; bc = false; QString sql ( "SELECT DISTINCT rahead_id, " "CASE " " WHEN rahead_creditmethod = 'M' THEN " " 1 " " WHEN rahead_creditmethod = 'K' THEN " " 2 " " WHEN rahead_creditmethod = 'C' THEN " " 3 " "END, " "rahead_number, cust_name, " "rahead_authdate, " "calcradueamt(rahead_id) AS amount, " "currConcat(rahead_curr_id) AS currency, " "currtobase(rahead_curr_id," " calcradueamt(rahead_id), current_date) AS baseamount, " "CASE " " WHEN rahead_creditmethod = 'M' THEN " " <? value(\"creditmemo\") ?> " " WHEN rahead_creditmethod = 'K' THEN " " <? value(\"check\") ?> " " WHEN rahead_creditmethod = 'C' THEN " " <? value(\"creditcard\") ?> " "END AS creditmethod, " "'curr' AS amount_xtnumericrole, " "'curr' AS baseamount_xtnumericrole " "FROM rahead,custinfo,raitem,custtype " "WHERE ( (rahead_id=raitem_rahead_id) " " AND (rahead_cust_id=cust_id) " " AND (cust_custtype_id=custtype_id) " " AND ((raitem_disposition IN ('R','P') AND rahead_timing = 'R' AND raitem_qtyreceived > raitem_qtycredited) " " OR (raitem_disposition IN ('R','P') AND rahead_timing = 'I' AND raitem_qtyauthorized > raitem_qtycredited) " " OR (raitem_disposition = 'C' AND raitem_qtyauthorized > raitem_qtycredited)) " " AND (raitem_status = 'O') " " AND (rahead_creditmethod != 'N') " " AND (calcradueamt(rahead_id) > 0) " " AND (raitem_disposition IN ('C','R','P')) " " <? if exists(\"cust_id\") ?>" " AND (cust_id=<? value(\"cust_id\") ?>) " " <? elseif exists(\"custtype_id\") ?>" " AND (custtype_id=<? value(\"custtype_id\") ?>) " " <? elseif exists(\"custtype_pattern\") ?>" " AND (custtype_code ~ <? value(\"custtype_pattern\") ?>) " " <? endif ?>" " AND (rahead_creditmethod IN ('$'" // avoid stress over commas " <? if exists(\"doM\") ?>, 'M'<? endif ?>" " <? if exists(\"doK\") ?>, 'K'<? endif ?>" " <? if exists(\"doC\") ?>, 'C'<? endif ?>" " ))" " AND ((SELECT COUNT(*)" " FROM raitem JOIN itemsite ON (itemsite_id=raitem_itemsite_id)" " JOIN site() ON (warehous_id=itemsite_warehous_id)" " WHERE (raitem_rahead_id=rahead_id)) > 0)" " ) " "ORDER BY rahead_authdate,rahead_number;" ); ParameterList params; setParams(params); MetaSQLQuery mql(sql); XSqlQuery radue = mql.toQuery(params); if (radue.first()) _radue->populate(radue,TRUE); else if (radue.lastError().type() != QSqlError::NoError) { systemError(this, radue.lastError().databaseText(), __FILE__, __LINE__); return; } } }
void dspTrialBalances::sFillList() { _trialbal->clear(); if (!_metrics->boolean("ManualForwardUpdate")) { if (!forwardUpdate()) return; } QString sql( "SELECT accnt_id, period_id, accnt_descrip, trialbal_dirty," " period_start, period_end," " formatGLAccount(accnt_id) AS account," " (trialbal_debits) AS debits," " (trialbal_credits) AS credits," " trialbal_beginning AS beginning," " trialbal_ending AS ending," " (trialbal_debits - trialbal_credits) AS diff," " CASE WHEN ((trialbal_beginning*-1.0)<0.0) THEN 'CR' END AS beginningsense," " CASE WHEN ((trialbal_ending*-1.0)<0.0) THEN 'CR' END AS endingsense," " CASE WHEN ((trialbal_debits - trialbal_credits)<0.0) THEN 'CR' END AS diffsense," " 'curr' AS beginning_xtnumericrole," " 'curr' AS debits_xtnumericrole," " 'curr' AS credits_xtnumericrole," " 'curr' AS ending_xtnumericrole," " 'curr' AS diff_xtnumericrole," " 0 AS beginning_xttotalrole," " 0 AS debits_xttotalrole," " 0 AS credits_xttotalrole," " 0 AS ending_xttotalrole," " 0 AS diff_xttotalrole," " CASE WHEN (trialbal_beginning < 0.0) THEN ABS(trialbal_beginning) END AS beginning_qtdisplayrole," " CASE WHEN (trialbal_ending < 0.0) THEN ABS(trialbal_ending) END AS ending_qtdisplayrole," " CASE WHEN ((trialbal_debits - trialbal_credits) < 0.0) THEN ABS(trialbal_debits - trialbal_credits) END AS diff_qtdisplayrole," " CASE WHEN (trialbal_dirty) THEN 'warning' END AS ending_qtforegroundrole " "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 ?>" "<? if not exists(\"showZero\") ?>" " AND (abs(trialbal_beginning)+abs(trialbal_ending)+abs(trialbal_debits)+abs(trialbal_credits) > 0) " "<? endif ?>" ") " "ORDER BY period_start, formatGLAccount(accnt_id);" ); ParameterList params; setParams(params); MetaSQLQuery mql(sql); q = mql.toQuery(params); if (q.first()) { _trialbal->populate(q, true); } else if (q.lastError().type() != QSqlError::NoError) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } }
SetResponse freightBreakdown::set(const ParameterList& pParams) { XDialog::set(pParams); QVariant param; bool valid; ParameterList params; param = pParams.value("order_id", &valid); if (valid) { _orderid = param.toInt(); params.append("order_id", _orderid); } param = pParams.value("cust_id", &valid); if (valid) params.append("cust_id", param.toInt()); param = pParams.value("shipto_id", &valid); if (valid) params.append("shipto_id", param.toInt()); param = pParams.value("orderdate", &valid); if (valid) params.append("orderdate", param.toDate()); param = pParams.value("shipvia", &valid); if (valid) params.append("shipvia", param.toString()); param = pParams.value("curr_id", &valid); if (valid) params.append("curr_id", param.toInt()); param = pParams.value("document_number", &valid); if (valid) _document->setText(param.toString()); param = pParams.value("order_type", &valid); if (valid) { _ordertype = param.toString(); params.append("order_type", _ordertype); if (_ordertype == "SO") _header->setText(tr("Freight Breakdown for Sales Order:")); else if (_ordertype == "QU") _header->setText(tr("Freight Breakdown for Quote:")); else _header->setText(tr("Freight Breakdown for Return Auth.:")); } param = pParams.value("calcfreight", &valid); if (valid) { _calcfreight = param.toBool(); if (_calcfreight == true) _calculated->setChecked(true); else _manual->setChecked(true); } param = pParams.value("mode", &valid); if (valid) { if(param.toString() == "view") _mode = cView; else _mode = cEdit; } QString sql = "SELECT *," " 'weight' AS freightdata_weight_xtnumericrole," " 'salesprice' AS freightdata_price_xtnumericrole," " 'curr' AS freightdata_total_xtnumericrole," " 0 AS freightdata_total_xttotalrole " "FROM freightDetail(<? value(\"order_type\") ?>," " <? value(\"order_id\") ?>," " <? value(\"cust_id\") ?>," " <? value(\"shipto_id\") ?>," " <? value(\"orderdate\") ?>," " <? value(\"shipvia\") ?>," " <? value(\"curr_id\") ?>);"; MetaSQLQuery mql(sql); q = mql.toQuery(params); _freight->populate(q); if (q.lastError().type() != QSqlError::NoError) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return UndefinedError; } return NoError; }
void dspInventoryLocator::sFillList() { ParameterList params; if (! setParams(params)) return; MetaSQLQuery mql("SELECT *, " " <? value(\"na\") ?> AS locationname_xtnullrole," " <? value(\"na\") ?> AS netable_xtnullrole," " <? value(\"na\") ?> AS lotserial_xtnullrole," " <? value(\"na\") ?> AS expiration_xtnullrole," " <? value(\"na\") ?> AS warranty_xtnullrole," " CASE WHEN (itemsite_perishable" " AND itemloc_expiration <= CURRENT_DATE) THEN 'error'" " WHEN (itemsite_warrpurc" " AND itemloc_warrpurc <= CURRENT_DATE) THEN 'error'" " END AS qtforegroundrole," " 'qty' AS qoh_xtnumericrole " "FROM (" " SELECT itemloc_id, 1 AS type, warehous_code," " itemsite_perishable, itemloc_expiration," " itemsite_warrpurc, itemloc_warrpurc," " CASE WHEN (location_id IS NOT NULL) THEN " " (formatLocationName(location_id) || '-' || firstLine(location_descrip))" " END AS locationname," " CASE WHEN (location_id IS NOT NULL) THEN location_netable" " END AS netable," " CASE WHEN (itemsite_controlmethod IN ('L', 'S')) THEN" " formatlotserialnumber(itemloc_ls_id)" " END AS lotserial," " CASE WHEN (itemsite_perishable) THEN itemloc_expiration" " END AS expiration," " CASE WHEN (itemsite_warrpurc) THEN itemloc_warrpurc" " END AS warranty," " itemloc_qty AS qoh " " FROM itemsite, warehous," " itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id) " " WHERE ((itemsite_loccntrl OR (itemsite_controlmethod IN ('L', 'S')))" " AND (itemloc_itemsite_id=itemsite_id)" " AND (itemsite_warehous_id=warehous_id)" " AND (itemsite_item_id=<? value(\"item_id\") ?>)" "<? if exists(\"warehous_id\") ?>" " AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)" "<? endif ?>" " ) " " UNION " " SELECT itemsite_id, 2 AS type, warehous_code," " itemsite_perishable, NULL AS itemloc_expiration," " itemsite_warrpurc, NULL AS itemloc_warrpurc," " NULL AS locationname," " NULL AS netable," " NULL AS lotserial," " NULL AS expiration," " NULL AS warranty," " itemsite_qtyonhand AS qoh " " FROM itemsite, warehous " " WHERE ( (NOT itemsite_loccntrl)" " AND (itemsite_controlmethod NOT IN ('L', 'S'))" " AND (itemsite_warehous_id=warehous_id)" " AND (itemsite_item_id=<? value(\"item_id\") ?>)" "<? if exists(\"warehous_id\") ?>" " AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)" "<? endif ?>" ")) AS dummy " "ORDER BY warehous_code, locationname, lotserial;"); q = mql.toQuery(params); q.exec(); _itemloc->populate(q, true); if (q.lastError().type() != QSqlError::NoError) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } }
void printCheck::sCreateEFT() { XSqlQuery printCreateEFT; XSqlQuery releasenum; releasenum.prepare("SELECT releaseNumber('ACHBatch', :batch);"); QString batch; MetaSQLQuery mql("SELECT *" " FROM <? literal(\"func\") ?>(<? value(\"bank\") ?>," " <? value(\"check\") ?>, <? value(\"key\") ?>);"); ParameterList params; params.append("func", _metrics->value("EFTFunction")); params.append("bank", _bankaccnt->id()); params.append("check",_check->id()); params.append("key", omfgThis->_key); printCreateEFT = mql.toQuery(params); if (printCreateEFT.first()) { batch = printCreateEFT.value("achline_batch").toString(); releasenum.bindValue(":batch", batch); if (eftFileDir.isEmpty()) { eftFileDir = xtsettingsValue("ACHOutputDirectory").toString(); } QString suffixes = "*.ach *.aba *.dat *.txt"; if (! suffixes.contains(_metrics->value("ACHDefaultSuffix"))) suffixes = "*" + _metrics->value("ACHDefaultSuffix") + " " + suffixes; QString filename = QFileDialog::getSaveFileName(this, tr("EFT Output File"), printCheck::eftFileDir + QDir::separator() + "eft" + batch + _metrics->value("ACHDefaultSuffix"), "(" + suffixes + ")"); if (filename.isEmpty()) { releasenum.exec(); return; } QFileInfo fileinfo(filename); eftFileDir = fileinfo.absolutePath(); QFile eftfile(filename); if (! eftfile.open(QIODevice::WriteOnly)) { releasenum.exec(); QMessageBox::critical(this, tr("Could Not Open File"), tr("Could not open %1 for writing EFT data.") .arg(filename)); return; } do { eftfile.write(printCreateEFT.value("achline_value").toString().toLatin1()); eftfile.write("\n"); } while (printCreateEFT.next()); eftfile.close(); if (printCreateEFT.lastError().type() != QSqlError::NoError) { releasenum.exec(); eftfile.remove(); systemError(this, printCreateEFT.lastError().databaseText(), __FILE__, __LINE__); return; } if (confirmAchOK::askOK(this, eftfile)) markCheckAsPrinted(_check->id()); else { releasenum.exec(); XSqlQuery clearq; clearq.prepare("UPDATE checkhead " "SET checkhead_printed=false," " checkhead_ach_batch=NULL " "WHERE (checkhead_id=:checkhead_id);"); clearq.bindValue(":checkhead_id", _check->id()); clearq.exec(); if (clearq.lastError().type() != QSqlError::NoError) { systemError(this, clearq.lastError().databaseText(), __FILE__, __LINE__); return; } } } else if (printCreateEFT.lastError().type() != QSqlError::NoError) { systemError(this, printCreateEFT.lastError().databaseText(), __FILE__, __LINE__); return; } }
void issueLineToShipping::sIssue() { XSqlQuery issueIssue; if (_qtyToIssue->toDouble() <= 0) { XMessageBox::message( (isVisible() ? this : parentWidget()), QMessageBox::Warning, tr("Invalid Quantity to Issue to Shipping"), tr( "<p>Please enter a non-negative, non-zero value to indicate the amount " "of Stock you wish to Issue to Shipping for this Order Line." ), QString::null, QString::null, _snooze ); _qtyToIssue->setFocus(); return; } if(_requireInventory || ("SO" == _ordertype && _metrics->boolean("EnableSOReservations"))) { issueIssue.prepare("SELECT sufficientInventoryToShipItem(:ordertype, :orderitemid, :orderqty) AS result;"); issueIssue.bindValue(":ordertype", _ordertype); issueIssue.bindValue(":orderitemid", _itemid); issueIssue.bindValue(":orderqty", _qtyToIssue->toDouble()); issueIssue.exec(); if (issueIssue.first()) { int result = issueIssue.value("result").toInt(); if (result < 0) { ParameterList errp; if (_ordertype == "SO") errp.append("soitem_id", _itemid); else if (_ordertype == "TO") errp.append("toitem_id", _itemid); QString errs = "<? if exists(\"soitem_id\") ?>" "SELECT item_number, warehous_code " " FROM coitem, item, itemsite, whsinfo " " WHERE ((coitem_itemsite_id=itemsite_id)" " AND (itemsite_item_id=item_id)" " AND (itemsite_warehous_id=warehous_id)" " AND (coitem_id=<? value(\"soitem_id\") ?>));" "<? elseif exists(\"toitem_id\")?>" "SELECT item_number, tohead_srcname AS warehous_code " " FROM toitem, tohead, item " " WHERE ((toitem_item_id=item_id)" " AND (toitem_tohead_id=tohead_id)" " AND (toitem_id=<? value(\"toitem_id\") ?>));" "<? endif ?>" ; MetaSQLQuery errm(errs); issueIssue = errm.toQuery(errp); if (! issueIssue.first() && issueIssue.lastError().type() != QSqlError::NoError) systemError(this, issueIssue.lastError().databaseText(), __FILE__, __LINE__); systemError(this, storedProcErrorLookup("sufficientInventoryToShipItem", result) .arg(issueIssue.value("item_number").toString()) .arg(issueIssue.value("warehous_code").toString()), __FILE__, __LINE__); return; } } else if (issueIssue.lastError().type() != QSqlError::NoError) { systemError(this, issueIssue.lastError().databaseText(), __FILE__, __LINE__); return; } } // check to see if we are over issuing ParameterList params; if (_ordertype == "SO") params.append("soitem_id", _itemid); else if (_ordertype == "TO") params.append("toitem_id", _itemid); params.append("qty", _qtyToIssue->toDouble()); QString sql = "<? if exists(\"soitem_id\") ?>" "SELECT itemsite_costmethod," " (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) <" " (COALESCE(SUM(shipitem_qty), 0) + <? value(\"qty\") ?>)) AS overship" " FROM coitem LEFT OUTER JOIN" " ( shipitem JOIN shiphead" " ON ( (shipitem_shiphead_id=shiphead_id) AND (NOT shiphead_shipped) )" " ) ON (shipitem_orderitem_id=coitem_id)" " JOIN itemsite ON (coitem_itemsite_id=itemsite_id) " " WHERE (coitem_id=<? value(\"soitem_id\") ?>)" " GROUP BY coitem_qtyord, coitem_qtyshipped, coitem_qtyreturned, " " itemsite_costmethod, itemsite_controlmethod;" "<? elseif exists(\"toitem_id\") ?>" "SELECT false AS postprod," " (noNeg(toitem_qty_ordered - toitem_qty_shipped) <" " (COALESCE(SUM(shipitem_qty), 0) + <? value(\"qty\") ?>)) AS overship" " FROM toitem LEFT OUTER JOIN" " ( shipitem JOIN shiphead" " ON ( (shipitem_shiphead_id=shiphead_id) AND (NOT shiphead_shipped) )" " ) ON (shipitem_orderitem_id=toitem_id)" " WHERE (toitem_id=<? value(\"toitem_id\") ?>)" " GROUP BY toitem_qty_ordered, toitem_qty_shipped;" "<? endif ?>"; MetaSQLQuery mql(sql); issueIssue = mql.toQuery(params); if (issueIssue.next() && issueIssue.value("overship").toBool()) { if(XMessageBox::message( (isVisible() ? this : parentWidget()) , QMessageBox::Question, tr("Inventory Overshipped"), tr("<p>You have selected to ship more inventory than required. Do you want to continue?"), tr("Yes"), tr("No"), _snooze, 0, 1) == 1) return; } if (issueIssue.lastError().type() != QSqlError::NoError) { systemError(this, issueIssue.lastError().databaseText(), __FILE__, __LINE__); return; } XSqlQuery rollback; rollback.prepare("ROLLBACK;"); int invhistid = 0; int itemlocSeries = 0; XSqlQuery issue; issue.exec("BEGIN;"); // If this is a lot/serial controlled job item, we need to post production first if (issueIssue.value("itemsite_costmethod").toString() == "J") { XSqlQuery prod; prod.prepare("SELECT postSoItemProduction(:soitem_id, :qty, :ts) AS result;"); prod.bindValue(":soitem_id", _itemid); prod.bindValue(":qty", _qtyToIssue->toDouble()); prod.bindValue(":ts", _transTS); prod.exec(); if (prod.first()) { itemlocSeries = prod.value("result").toInt(); if (itemlocSeries < 0) { rollback.exec(); systemError(this, storedProcErrorLookup("postProduction", itemlocSeries), __FILE__, __LINE__); return; } else if (distributeInventory::SeriesAdjust(itemlocSeries, this) == XDialog::Rejected) { rollback.exec(); QMessageBox::information( this, tr("Issue to Shipping"), tr("Issue Canceled") ); return; } // Need to get the inventory history id so we can auto reverse the distribution when issuing prod.prepare("SELECT invhist_id " "FROM invhist " "WHERE ((invhist_series = :itemlocseries) " " AND (invhist_transtype = 'RM')); "); prod.bindValue(":itemlocseries" , itemlocSeries); prod.exec(); if (prod.first()) invhistid = prod.value("invhist_id").toInt(); else { rollback.exec(); systemError(this, tr("Inventory history not found"), __FILE__, __LINE__); return; } } } issue.prepare("SELECT issueToShipping(:ordertype, :lineitem_id, :qty, :itemlocseries, :ts, :invhist_id) AS result;"); issue.bindValue(":ordertype", _ordertype); issue.bindValue(":lineitem_id", _itemid); issue.bindValue(":qty", _qtyToIssue->toDouble()); issue.bindValue(":ts", _transTS); if (invhistid) issue.bindValue(":invhist_id", invhistid); issue.bindValue(":itemlocseries", itemlocSeries); issue.exec(); if (issue.first()) { int result = issue.value("result").toInt(); if (result < 0) { rollback.exec(); systemError( this, storedProcErrorLookup("issueToShipping", result), __FILE__, __LINE__); return; } else { if (distributeInventory::SeriesAdjust(result, this) == XDialog::Rejected) { rollback.exec(); QMessageBox::information( this, tr("Issue to Shipping"), tr("Issue Canceled") ); return; } // If Transfer Order then insert special pre-assign records for the lot/serial# // so they are available when the Transfer Order is received if (_ordertype == "TO") { XSqlQuery lsdetail; lsdetail.prepare("INSERT INTO lsdetail " " (lsdetail_itemsite_id, lsdetail_created, lsdetail_source_type, " " lsdetail_source_id, lsdetail_source_number, lsdetail_ls_id, lsdetail_qtytoassign) " "SELECT invhist_itemsite_id, NOW(), 'TR', " " :orderitemid, invhist_ordnumber, invdetail_ls_id, (invdetail_qty * -1.0) " "FROM invhist JOIN invdetail ON (invdetail_invhist_id=invhist_id) " "WHERE (invhist_series=:itemlocseries);"); lsdetail.bindValue(":orderitemid", _itemid); lsdetail.bindValue(":itemlocseries", result); lsdetail.exec(); if (lsdetail.lastError().type() != QSqlError::NoError) { rollback.exec(); systemError(this, lsdetail.lastError().databaseText(), __FILE__, __LINE__); return; } } issue.exec("COMMIT;"); accept(); } } else if (issue.lastError().type() != QSqlError::NoError) { rollback.exec(); systemError(this, issue.lastError().databaseText(), __FILE__, __LINE__); return; } }
void dspPoItemsByBufferStatus::sFillList() { _poitem->clear(); QString sql( "SELECT pohead_id, poitem_id, pohead_number," " 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," " 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," " vend_name," " CASE WHEN (bufrsts_type='T') THEN <? value(\"time\") ?>" " ELSE <? value(\"stock\") ?>" " END AS bufrststype," " bufrsts_status," " item_number," " item_descrip1," " uom_name," " formatQty(poitem_qty_ordered) AS f_qtyordered," " formatQty(poitem_qty_received) AS f_qtyreceived," " formatQty(poitem_qty_returned) AS f_qtyreturned," " formatDate(poitem_duedate) AS f_duedate," " (bufrsts_status >66) AS emergency " " FROM pohead, poitem, vend,itemsite, item, uom, bufrsts " " WHERE ((poitem_pohead_id=pohead_id)" " AND (pohead_vend_id=vend_id)" " AND (itemsite_item_id=item_id)" " AND (item_inv_uom_id=uom_id)" " AND (poitem_itemsite_id=itemsite_id)" " AND (pohead_vend_id=vend_id)" " AND (poitem_status='O')" " AND (bufrsts_target_type='P')" " AND (bufrsts_target_id=poitem_id)" " AND (bufrsts_date=current_date)" "<? 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(\"username\") ?>" " AND (pohead_agent_username=<? value(\"username\") ?>)" "<? endif ?>" ") " "ORDER BY bufrsts_status desc, poitem_duedate;" ); ParameterList params; params.append("stock", tr("Stock")); params.append("time", tr("Time")); params.append("closed", tr("Closed")); params.append("unposted", tr("Unposted")); params.append("partial", tr("Partial")); params.append("received", tr("Received")); params.append("open", tr("Open")); if (_warehouse->isSelected()) params.append("warehous_id", _warehouse->id()); if (_selectedPurchasingAgent->isChecked()) params.append("username", _agent->currentText()); 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").toString(), q.value("warehousecode"), q.value("poitemstatus"), q.value("vend_name"), q.value("bufrsts_status"), q.value("bufrststype"), q.value("item_number"), q.value("item_descrip1"), q.value("uom_name"), q.value("f_qtyordered"), q.value("f_qtyreceived") ); last->setText(11, q.value("f_qtyreturned").toString()); last->setText(12, q.value("f_duedate").toString()); last->setText(POITEM_STATUS_COL, q.value("poitem_status").toString()); if (q.value("emergency").toBool()) last->setTextColor(4, "red"); } while (q.next()); } else if (q.lastError().type() != QSqlError::None) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } }
void ContactSearch::sFillList() { _listTab->clear(); if (_search->text().isEmpty() || (!_searchFirst->isChecked() && !_searchLast->isChecked() && !_searchCRMAcct->isChecked() && !_searchTitle->isChecked() && !_searchPhones->isChecked() && !_searchEmail->isChecked() && !_searchWebAddr->isChecked() )) return; QString limits = "<? if exists(\"extraClause\") ?> " " AND " "<? else ?>" " WHERE " "<? endif ?> " "<? if exists(\"searchInactive\") ?> " " true " "<? else ?>" " cntct_active " "<? endif ?>" "<? if reExists(\"search[FLCTPEW]\") ?> " " AND (" " <? if exists(\"searchFirst\") ?> " " COALESCE(cntct_first_name,'') || '\n' " " <? else ?>" " '\n' " " <? endif ?>" " <? if exists(\"searchLast\") ?> " " || COALESCE(cntct_last_name,'') || '\n' " " <? endif ?>" " <? if exists(\"searchCRMAcct\") ?> " " || COALESCE(crmacct_name,'') || '\n' " " <? endif ?>" " <? if exists(\"searchTitle\") ?> " " || COALESCE(cntct_title,'') || '\n' " " <? endif ?>" " <? if exists(\"searchPhones\") ?> " " || COALESCE(cntct_phone,'') || '\n' " " || COALESCE(cntct_phone2,'') || '\n' " " || COALESCE(cntct_fax,'') || '\n' " " <? endif ?>" " <? if exists(\"searchEmail\") ?> " " || COALESCE(cntct_email,'') || '\n' " " <? endif ?>" " <? if exists(\"searchWebAddr\") ?> " " || COALESCE(cntct_webaddr,'') || '\n' " " <? endif ?>" " ~* <? value(\"searchText\") ?> )" "<? endif ?>" "ORDER BY cntct_last_name, cntct_first_name, crmacct_number;"; QString sql = _parent->_query + _parent->_extraClause.replace(":searchAcctId", QString::number(_parent->_searchAcctId)) + limits; ParameterList params; if (_searchFirst->isChecked()) params.append("searchFirst"); if (_searchLast->isChecked()) params.append("searchLast"); if (_searchCRMAcct->isChecked()) params.append("searchCRMAcct"); if (_searchTitle->isChecked()) params.append("searchTitle"); if (_searchPhones->isChecked()) params.append("searchPhones"); if (_searchEmail->isChecked()) params.append("searchEmail"); if (_searchWebAddr->isChecked()) params.append("searchWebAddr"); if (_searchInactive->isChecked()) params.append("searchInactive"); if (! _parent->_extraClause.isEmpty()) params.append("extraClause", _parent->_extraClause); params.append("searchText", _search->text()); MetaSQLQuery mql(sql); XSqlQuery query = mql.toQuery(params); if (query.lastError().type() != QSqlError::None) { QMessageBox::critical(this, tr("A System Error Occurred at %1::%2.") .arg(__FILE__) .arg(__LINE__), query.lastError().databaseText()); return; } else if (query.size() < 1) // no rows found with limit so try without { sql = _parent->_query + limits; MetaSQLQuery mqlAllAccnts(sql); query = mqlAllAccnts.toQuery(params); if (query.lastError().type() != QSqlError::None) { QMessageBox::critical(this, tr("A System Error Occurred at %1::%2.") .arg(__FILE__) .arg(__LINE__), query.lastError().databaseText()); return; } } XTreeWidgetItem *last = 0; while (query.next()) { last = new XTreeWidgetItem(_listTab, last, query.value("cntct_id").toInt(), 0, query.value("cntct_first_name"), query.value("cntct_last_name"), query.value("crmacct_name"), query.value("cntct_title"), query.value("cntct_phone"), query.value("cntct_phone2"), query.value("cntct_fax"), query.value("cntct_email"), query.value("cntct_webaddr")); } }
void dspPartiallyShippedOrders::sFillList() { _so->clear(); ParameterList params; if (setParams(params)) { QString sql( "SELECT CASE WHEN (cohead_holdtype IN ('P', 'C', 'R')) THEN -1" " ELSE cohead_id" " END AS _coheadid, cohead_id," " cohead_holdtype, cohead_number, cust_name," " CASE WHEN (cohead_holdtype='N') THEN <? value(\"none\") ?>" " WHEN (cohead_holdtype='C') THEN <? value(\"credit\") ?>" " WHEN (cohead_holdtype='S') THEN <? value(\"ship\") ?>" " WHEN (cohead_holdtype='P') THEN <? value(\"pack\") ?>" " WHEN (cohead_holdtype='R') THEN <? value(\"return\") ?>" " ELSE <? value(\"other\") ?>" " END AS f_holdtype," " cohead_orderdate," " (MIN(coitem_scheddate)) AS minscheddate," " cohead_packdate," " SUM( (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) *" " (coitem_price / coitem_price_invuomratio) ) AS extprice," " currConcat(cohead_curr_id) AS currAbbr," " SUM(currToBase(cohead_curr_id," " (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) *" " (coitem_price / coitem_price_invuomratio)," " CURRENT_DATE)) AS extprice_base," " 'curr' AS extprice_xtnumericrole," " 'curr' AS extprice_base_xtnumericrole," "<? if exists(\"singlecurrency\") ?>" " 0 AS extprice_xttotalrole " "<? else ?>" " 0 AS extprice_base_xttotalrole " "<? endif ?>" "FROM cohead, itemsite, item, cust, coitem " "WHERE ( (coitem_cohead_id=cohead_id)" " AND (cohead_cust_id=cust_id)" " AND (coitem_itemsite_id=itemsite_id)" " AND (itemsite_item_id=item_id)" " AND (coitem_status='O')" " AND (cohead_id IN ( SELECT DISTINCT coitem_cohead_id" " FROM coitem" " WHERE (coitem_qtyshipped > 0) ))" " AND (coitem_qtyshipped < coitem_qtyord)" " AND (coitem_scheddate BETWEEN <? value(\"startDate\") ?>" " AND <? value(\"endDate\") ?>)" "<? if exists(\"warehous_id\") ?>" " AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)" "<? endif ?>" ") " "GROUP BY cohead_id, cohead_number, cust_name," " cohead_holdtype, cohead_orderdate, cohead_packdate," " cohead_curr_id " "ORDER BY minscheddate, cohead_number;"); MetaSQLQuery mql(sql); q = mql.toQuery(params); _so->populate(q, true); if (q.lastError().type() != QSqlError::NoError) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } _so->setDragString("soheadid="); } }
void dspARApplications::sFillList() { if ( (_selectedCustomer->isChecked()) && (!_cust->isValid()) ) { QMessageBox::warning( this, tr("Select Customer"), tr("You must select a Customer whose A/R Applications you wish to view.") ); _cust->setFocus(); return; } if (!_dates->startDate().isValid()) { QMessageBox::critical( this, tr("Enter Start Date"), tr("You must enter a valid Start Date.") ); _dates->setFocus(); return; } if (!_dates->endDate().isValid()) { QMessageBox::critical( this, tr("Enter End Date"), tr("You must enter a valid End Date.") ); _dates->setFocus(); return; } if ( (!_cashReceipts->isChecked()) && (!_creditMemos->isChecked()) ) { QMessageBox::critical( this, tr("Select Document Type"), tr("You must indicate which Document Type(s) you wish to view.") ); _cashReceipts->setFocus(); return; } QString sql( "SELECT arapply_id, cust_number, cust_name," " formatDate(arapply_postdate) AS f_postdate," " arapply_source_doctype, arapply_fundstype, " " CASE WHEN (arapply_source_doctype IN ('C','R')) THEN TEXT(arapply_source_docnumber)" " ELSE arapply_refnumber" " END AS source," " arapply_target_doctype," " TEXT(arapply_target_docnumber) AS target," " formatMoney(arapply_applied) AS f_applied, arapply_applied " "FROM arapply, custinfo " "WHERE ( (arapply_cust_id=cust_id)" " AND (arapply_postdate BETWEEN <? value(\"startDate\") ?> AND <? value(\"endDate\") ?>)" " AND (arapply_source_doctype IN (" "<? if exists(\"creditMemos\") ?>" " <? if exists(\"cashReceipts\") ?>" " 'K', 'C', 'R' " " <? else ?>" " 'C', 'R' " " <? endif ?>" "<? else ?>" " 'K' " "<? endif ?>" "))" "<? if exists(\"cust_id\") ?>" " AND (cust_id=<? value(\"cust_id\") ?>)" "<? elseif exists(\"custtype_id\") ?>" " AND (cust_custtype_id=<? value(\"custtype_id\") ?>)" "<? elseif exists(\"custtype_pattern\") ?>" " AND (cust_custtype_id IN (SELECT custtype_id FROM custtype" " WHERE (custtype_code ~ <? value(\"custtype_id\") ?>)))" "<? endif ?>" ") " "ORDER BY arapply_postdate, source;" ); ParameterList params; if (_cashReceipts->isChecked()) params.append("cashReceipts"); if (_creditMemos->isChecked()) params.append("creditMemos"); params.append("startDate", _dates->startDate()); params.append("endDate", _dates->endDate()); if (_selectedCustomer->isChecked()) params.append("cust_id", _cust->id()); else if (_selectedCustomerType->isChecked()) params.append("custtype_id", _customerTypes->id()); else if (_customerTypePattern->isChecked()) params.append("custtype_pattern", _customerType->text()); MetaSQLQuery mql(sql); q = mql.toQuery(params); if (q.first()) { _arapply->clear(); double total = 0; XTreeWidgetItem* last = 0; do { QString fundstype = q.value("arapply_fundstype").toString(); QString doctype; if (q.value("arapply_source_doctype") == "C") doctype = tr("Credit Memo"); else if (q.value("arapply_source_doctype") == "R") doctype = tr("Cash Deposit"); else if (fundstype == "A") doctype = tr("AmEx"); else if (fundstype == "C") doctype = tr("Check"); else if (fundstype == "D") doctype = tr("Discover"); else if (fundstype == "K") doctype = tr("Cash"); else if (fundstype == "M") doctype = tr("M/C"); else if (fundstype == "R") doctype = tr("Other C/C"); else if (fundstype == "T") doctype = tr("Cert. Check"); else if (fundstype == "V") doctype = tr("Visa"); else if (fundstype == "W") doctype = tr("Wire Trans."); else if (fundstype == "O") doctype = tr("Other"); QString targetdoctype = q.value("arapply_target_doctype").toString(); if (targetdoctype == "D") targetdoctype = tr("Debit Memo"); else if (targetdoctype == "I") targetdoctype = tr("Invoice"); else if (targetdoctype == "K") targetdoctype = tr("A/P Check"); else targetdoctype = tr("Other"); last = new XTreeWidgetItem( _arapply, last, q.value("arapply_id").toInt(), q.value("cust_number"), q.value("cust_name"), q.value("f_postdate"), (q.value("arapply_source_doctype") == "C") ? "C/M" : ((q.value("arapply_source_doctype") == "R") ? "Cash Deposit" : fundstype), doctype, q.value("source"), q.value("arapply_target_doctype").toString(), targetdoctype, q.value("target"), q.value("f_applied") ); total += q.value("arapply_applied").toDouble(); } while (q.next()); last = new XTreeWidgetItem(_arapply, last, -1, "", tr("Total Applications:")); last->setText(9, formatMoney(total)); } else if (q.lastError().type() != QSqlError::None) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } }
void distributeInventory::sFillList() { q.prepare( "SELECT itemsite_id, " " COALESCE(itemsite_location_id,-1) AS itemsite_location_id," " itemlocdist_lotserial," " (itemsite_controlmethod IN ('L', 'S')) AS lscontrol," " parent.itemlocdist_qty AS qtytodistribute," " ( ( SELECT COALESCE(SUM(child.itemlocdist_qty), 0)" " FROM itemlocdist AS child" " WHERE (child.itemlocdist_itemlocdist_id=parent.itemlocdist_id) ) ) AS qtytagged," " (parent.itemlocdist_qty - ( SELECT COALESCE(SUM(child.itemlocdist_qty), 0)" " FROM itemlocdist AS child" " WHERE (child.itemlocdist_itemlocdist_id=parent.itemlocdist_id) ) ) AS qtybalance " "FROM itemsite, itemlocdist AS parent " "WHERE ( (itemlocdist_itemsite_id=itemsite_id)" " AND (itemlocdist_id=:itemlocdist_id) );" ); q.bindValue(":itemlocdist_id", _itemlocdistid); q.exec(); if (q.first()) { _item->setItemsiteid(q.value("itemsite_id").toInt()); _lotSerial->setText(q.value("itemlocdist_lotserial").toString()); _qtyToDistribute->setText(formatNumber(q.value("qtytodistribute").toDouble(),6)); _qtyTagged->setText(formatNumber(q.value("qtytagged").toDouble(),6)); _qtyRemaining->setText(formatNumber(q.value("qtybalance").toDouble(),6)); if ( (q.value("itemsite_location_id").toInt() != -1) && ( (_mode == cNoIncludeLotSerial) || ( (_mode == cIncludeLotSerial) && (!q.value("lscontrol").toBool()) ) ) ) { _default->setEnabled(TRUE); _defaultAndPost->setEnabled(TRUE); } else { _default->setEnabled(FALSE); _defaultAndPost->setEnabled(FALSE); } QString sql( "SELECT id, type," " locationname," " CASE WHEN defaultlocation THEN <? value(\"yes\") ?>" " ELSE <? value(\"no\") ?>" " END AS defaultlocation," " CASE WHEN (location_netable) THEN <? value(\"yes\") ?>" " ELSE <? value(\"no\") ?>" " END AS netable," " lotserial, f_expiration, expired," " qty," " qtytagged," " (qty + qtytagged) AS balance " "FROM (" "<? if exists(\"cNoIncludeLotSerial\") ?>" "SELECT location_id AS id, <? value(\"locationType\") ?> AS type," " formatLocationName(location_id) AS locationname," " (location_id=itemsite_location_id) AS defaultlocation," " location_netable," " TEXT('') AS lotserial," " TEXT(<? value(\"na\") ?>) AS f_expiration, FALSE AS expired," " ( SELECT COALESCE(SUM(itemloc_qty), 0)" " FROM itemloc " " WHERE ( (itemloc_location_id=location_id)" " AND (itemloc_itemsite_id=itemsite_id) ) ) AS qty," " itemlocdistQty(location_id, itemlocdist_id) AS qtytagged " "FROM itemlocdist, location, itemsite " "WHERE ( (itemlocdist_itemsite_id=itemsite_id)" " AND (itemsite_loccntrl)" " AND (itemsite_warehous_id=location_warehous_id)" " AND (validLocation(location_id, itemsite_id))" " AND (itemlocdist_id=<? value(\"itemlocdist_id\") ?>) ) " "<? elseif exists(\"cIncludeLotSerial\") ?>" "SELECT itemloc_id AS id, <? value(\"itemlocType\") ?> AS type," " COALESCE(formatLocationName(location_id)," " <? value(\"undefined\") ?>) AS locationname," " (location_id IS NOT NULL" " AND location_id=itemsite_location_id) AS defaultlocation," " COALESCE(location_netable, false) AS location_netable," " itemloc_lotserial AS lotserial," " CASE WHEN (itemsite_perishable) THEN formatDate(itemloc_expiration)" " ELSE <? value(\"na\") ?>" " END AS f_expiration," " CASE WHEN (itemsite_perishable) THEN (itemloc_expiration < CURRENT_DATE)" " ELSE FALSE" " END AS expired," " itemloc_qty AS qty," " ( SELECT COALESCE(SUM(target.itemlocdist_qty), 0)" " FROM itemlocdist AS target" " WHERE ( (target.itemlocdist_source_type='I')" " AND (target.itemlocdist_source_id=itemloc_id)" " AND (target.itemlocdist_itemlocdist_id=source.itemlocdist_id)) ) AS qtytagged " "FROM itemlocdist AS source, itemsite, itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id) " "WHERE ( (source.itemlocdist_itemsite_id=itemsite_id)" " AND (itemloc_itemsite_id=itemsite_id)" " AND (source.itemlocdist_id=<? value(\"itemlocdist_id\") ?>) ) " " UNION " "SELECT location_id AS id, <? value(\"locationType\") ?> AS type," " formatLocationName(location_id) AS locationname," " (location_id=itemsite_location_id) AS defaultlocation," " location_netable," " TEXT('') AS lotserial," " TEXT(<? value(\"na\") ?>) AS f_expiration, FALSE AS expired," " ( SELECT COALESCE(SUM(itemloc_qty), 0)" " FROM itemloc " " WHERE ( (itemloc_location_id=location_id)" " AND (itemloc_itemsite_id=itemsite_id) ) ) AS qty," " itemlocdistQty(location_id, itemlocdist_id) AS qtytagged " "FROM itemlocdist, location, itemsite " "WHERE ( (itemlocdist_itemsite_id=itemsite_id)" " AND (itemsite_loccntrl)" " AND (itemsite_warehous_id=location_warehous_id)" " AND (validLocation(location_id, itemsite_id))" " AND (location_id NOT IN (SELECT DISTINCT itemloc_location_id FROM itemloc WHERE (itemloc_itemsite_id=itemsite_id)))" " AND (itemlocdist_id=<? value(\"itemlocdist_id\") ?>) ) " "<? endif ?>" ") AS data " "<? if exists(\"showOnlyTagged\") ?>" "WHERE (qtytagged != 0) " "<? endif ?>" "ORDER BY locationname;"); ParameterList params; if (_mode == cNoIncludeLotSerial) params.append("cNoIncludeLotSerial"); else if (_mode == cIncludeLotSerial) params.append("cIncludeLotSerial"); if (_taggedOnly->isChecked()) params.append("showOnlyTagged"); params.append("locationType", cLocation); params.append("itemlocType", cItemloc); params.append("yes", tr("Yes")); params.append("no", tr("No")); params.append("na", tr("N/A")); params.append("undefined", tr("Undefined")); params.append("itemlocdist_id", _itemlocdistid); MetaSQLQuery mql(sql); q = mql.toQuery(params); _itemloc->clear(); XTreeWidgetItem *last = 0; while (q.next()) { last = new XTreeWidgetItem(_itemloc, last, q.value("id").toInt(), q.value("type").toInt(), q.value("locationname"), q.value("defaultlocation"), q.value("netable"), q.value("lotserial"), q.value("f_expiration"), formatNumber(q.value("qty").toDouble(),6), formatNumber(q.value("qtytagged").toDouble(),6), formatNumber(q.value("balance").toDouble(),6) ); if (q.value("expired").toBool()) last->setTextColor("red"); } } }
void transferOrderList::sFillList() { QString sql; sql = "SELECT DISTINCT tohead_id, tohead_number, tohead_srcname, " " tohead_destname," " tohead_orderdate," " MIN(toitem_schedshipdate) AS scheddate " "<? if exists(\"atshipping\") ?>" "FROM tohead, toitem, tosmisc, toship " "WHERE ((tohead_id=toitem_tohead_id)" " AND (toitem_status<>'X')" " AND (toitem_id=toship_toitem_id)" " AND (toship_tosmisc_id=tosmisc_id)" " AND (NOT tosmisc_shipped)" "<? else ?>" // not restricted to atshipping "FROM tohead, toitem " "WHERE ((tohead_id=toitem_tohead_id)" " AND (toitem_status<>'X')" " <? if exists(\"toitem_statuslist\") ?>" " AND (toitem_status IN (<? literal(\"toitem_statuslist\") ?>))" " <? endif ?>" "<? endif ?>" "<? if exists(\"srcwarehous_id\") ?>" " AND (tohead_src_warehous_id=<? value(\"srcwarehous_id\") ?>)" "<? endif ?>" "<? if exists(\"dstwarehous_id\") ?>" " AND (tohead_dest_warehous_id=<? value(\"dstwarehous_id\") ?>)" "<? endif ?>" ") " "GROUP BY tohead_id, tohead_number, tohead_srcname," " tohead_destname, tohead_orderdate " "ORDER BY tohead_number;"; ParameterList params; if (_type == cToAtShipping) params.append("atshipping"); else { QString toitem_statuslist; bool statusCheck = false; if (_type & cToOpen) { toitem_statuslist += "'O'"; statusCheck = true; } if (_type & cToClosed) { if (statusCheck) toitem_statuslist += ", "; toitem_statuslist += "'C'"; statusCheck = true; } if (statusCheck) params.append("toitem_statuslist", toitem_statuslist); } if (_srcwhs->isSelected()) params.append("srcwarehous_id", _srcwhs->id()); if (_dstwhs->isSelected()) params.append("dstwarehous_id", _dstwhs->id()); MetaSQLQuery mql(sql); XSqlQuery q = mql.toQuery(params); _to->populate(q, _toheadid); }
void selectPayments::sFillList() { if(_ignoreUpdates) return; if (_vendorgroup->isSelectedVend()) _apopen->showColumn(9); else _apopen->hideColumn(9); if ( (_selectDate->currentIndex() == 1 && !_onOrBeforeDate->isValid()) || (_selectDate->currentIndex() == 2 && (!_startDate->isValid() || !_endDate->isValid())) ) return; int _currid = -1; if (_bankaccnt->isValid()) { q.prepare( "SELECT bankaccnt_curr_id " "FROM bankaccnt " "WHERE (bankaccnt_id=:bankaccnt_id);" ); q.bindValue(":bankaccnt_id", _bankaccnt->id()); q.exec(); if (q.first()) _currid = q.value("bankaccnt_curr_id").toInt(); } MetaSQLQuery mql( "SELECT * FROM ( " "SELECT apopen_id, COALESCE(apselect_id, -1) AS apselectid," " (vend_number || '-' || vend_name) AS vendor," " CASE WHEN (apopen_doctype='V') THEN <? value(\"voucher\") ?>" " When (apopen_doctype='D') THEN <? value(\"debitMemo\") ?>" " END AS doctype," " apopen_docnumber, apopen_ponumber," " apopen_duedate," " apopen_docdate, apopen_status, " " CASE WHEN (apopen_status = 'H') THEN 'error' END AS qtforegroundrole, " " (apopen_amount - apopen_paid - " " COALESCE((SELECT SUM(checkitem_amount + checkitem_discount) " " FROM checkitem, checkhead " " WHERE ((checkitem_checkhead_id=checkhead_id) " " AND (checkitem_apopen_id=apopen_id) " " AND (NOT checkhead_deleted) " " AND (NOT checkhead_replaced) " " AND (NOT checkhead_posted)) " " ), 0)) AS amount," " ((apopen_amount - apopen_paid - " " COALESCE((SELECT SUM(checkitem_amount + checkitem_discount) " " FROM checkitem, checkhead " " WHERE ((checkitem_checkhead_id=checkhead_id) " " AND (checkitem_apopen_id=apopen_id) " " AND (NOT checkhead_deleted) " " AND (NOT checkhead_replaced) " " AND (NOT checkhead_posted)) " " ), 0)) / apopen_curr_rate) AS base_amount, " " <? if exists(\"vend_id\") ?>" " COALESCE((apopen_amount - apopen_paid - " " COALESCE((SELECT SUM(checkitem_amount + checkitem_discount) " " FROM checkitem, checkhead " " WHERE ((checkitem_checkhead_id=checkhead_id) " " AND (checkitem_apopen_id=apopen_id) " " AND (NOT checkhead_deleted) " " AND (NOT checkhead_replaced) " " AND (NOT checkhead_posted)) " " ), 0)), 0) AS running_amount, " " <? endif ?>" " COALESCE(SUM(apselect_amount), 0) AS selected," " (COALESCE(SUM(apselect_amount), 0) / apopen_curr_rate) AS base_selected, " " COALESCE(currToBase(apselect_curr_id, SUM(apselect_amount), CURRENT_DATE), 0) AS running_selected," " COALESCE(SUM(apselect_discount),0) AS discount, " " (COALESCE(SUM(apselect_discount),0) / apopen_curr_rate)AS base_discount," " CASE WHEN (apopen_duedate < CURRENT_DATE) THEN 'error' " " ELSE CASE WHEN(apopen_duedate > CURRENT_DATE) THEN 'emphasis' " " ELSE CASE WHEN(CURRENT_DATE <= (apopen_docdate + terms_discdays)) THEN 'altemphasis' " " END " " END " " END AS apopen_duedate_qtforegroundrole, " " apopen_invcnumber," " currConcat(apopen_curr_id) AS curr_concat, " " 'curr' AS amount_xtnumericrole, " " 'curr' AS selected_xtnumericrole, " " 'curr' AS running_selected_xtnumericrole, " " 'curr' AS running_selected_xtrunningrole, " " 'curr' AS discount_xtnumericrole, " " 'curr' AS base_amount_xtnumericrole, " " 'curr' AS base_selected_xtnumericrole, " " 'curr' AS base_discount_xtnumericrole, " " 'curr' AS base_amount_xttotalrole, " " 'curr' AS base_selected_xttotalrole, " " 'curr' AS base_discount_xttotalrole, " " 'curr' AS running_amount_xtrunningrole " "FROM vend, apopen LEFT OUTER JOIN apselect ON (apselect_apopen_id=apopen_id) " "LEFT OUTER JOIN terms ON (apopen_terms_id=terms_id) " "WHERE ( (apopen_open)" " AND (apopen_doctype IN ('V', 'D'))" " AND (apopen_vend_id=vend_id)" "<? if exists(\"vend_id\") ?>" " AND (vend_id=<? value(\"vend_id\") ?>)" "<? elseif exists(\"vendtype_id\") ?>" " AND (vend_vendtype_id=<? value(\"vendtype_id\") ?>)" "<? elseif exists(\"vendtype_pattern\") ?>" " AND (vend_vendtype_id IN (SELECT vendtype_id" " FROM vendtype" " WHERE (vendtype_code ~ <? value(\"vendtype_pattern\") ?>)))" "<? endif ?>" "<? if exists(\"olderDate\") ?>" " AND (apopen_duedate <= <? value(\"olderDate\") ?>)" "<? elseif exists(\"startDate\") ?>" " AND (apopen_duedate BETWEEN <? value(\"startDate\") ?> AND <? value(\"endDate\") ?>)" "<? endif ?>" "<? if exists(\"curr_id\") ?>" " AND (apopen_curr_id=<? value(\"curr_id\") ?>)" "<? endif ?>" ") " "GROUP BY apopen_id, apselect_id, vend_number, vend_name, apopen_curr_rate," " apopen_doctype, apopen_docnumber, apopen_ponumber, vend_curr_id," " apopen_duedate, apopen_docdate, apopen_amount, apopen_paid, " " curr_concat, apopen_curr_id, apselect_curr_id, apopen_invcnumber, apopen_status, terms.terms_discdays " "ORDER BY apopen_duedate, (apopen_amount - apopen_paid) DESC) AS data " "WHERE (amount != 0);"); ParameterList params; if (! setParams(params)) return; params.append("voucher", tr("Voucher")); params.append("debitMemo", tr("Debit Memo")); if (_selectDate->currentIndex()==1) params.append("olderDate", _onOrBeforeDate->date()); else if (_selectDate->currentIndex()==2) { params.append("startDate", _startDate->date()); params.append("endDate", _endDate->date()); } if (_currid >= 0) params.append("curr_id", _currid); q = mql.toQuery(params); _apopen->populate(q,true); if (q.lastError().type() != QSqlError::NoError) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } }
void CRMAcctSearch::sFillList() { if (_search->text().trimmed().length() == 0) return; MetaSQLQuery mql(_listAndSearchQueryString); ParameterList params(*_queryParams); params.append("searchString", _search->text().trimmed().toUpper()); if (! _showInactive->isChecked()) params.append("activeOnly"); if (_searchNumber->isChecked()) params.append("searchNumber"); if (_searchName->isChecked()) params.append("searchName"); // some crmacct types don't have contacts (yet) if (_subtype != CRMAcctLineEdit::SalesRep && _subtype != CRMAcctLineEdit::Taxauth && _subtype != CRMAcctLineEdit::User) { if (_searchContact->isChecked()) params.append("searchContactName"); if (_searchPhone->isChecked()) params.append("searchPhone"); if (_searchEmail->isChecked()) params.append("searchEmail"); } // some crmacct types don't have addresses (yet) if (_subtype != CRMAcctLineEdit::SalesRep && _subtype != CRMAcctLineEdit::User) { if (_searchStreet->isChecked()) params.append("searchStreetAddr"); if (_searchCity->isChecked()) params.append("searchCity"); if (_searchState->isChecked()) params.append("searchState"); if (_searchPostalCode->isChecked()) params.append("searchPostalCode"); if (_searchCountry->isChecked()) params.append("searchCountry"); } if (_searchCombo->isChecked()) params.append("combo_id", _comboCombo->id()); XSqlQuery fillq = mql.toQuery(params); _listTab->populate(fillq); if (ErrorReporter::error(QtCriticalMsg, this, tr("Database Error"), fillq, __FILE__, __LINE__)) return; }
void vendorWorkBench::sPopulate() { ParameterList params; if (! setParams(params)) { clear(); return; } MetaSQLQuery mql("SELECT vend_name, vend_vendtype_id, vend_terms_id," " vend_shipvia, vend_active, vend_cntct1_id," " vend_cntct2_id, crmacct_id," " MIN(pohead_orderdate) AS minpodate, " " MAX(pohead_orderdate) AS maxpodate, " " SUM(currToBase(pohead_curr_id," " (poitem_qty_ordered - poitem_qty_received) * poitem_unitprice," " CURRENT_DATE)) AS backlog " "FROM vendinfo JOIN crmacct ON (crmacct_vend_id=vend_id)" " LEFT OUTER JOIN pohead ON (pohead_vend_id=vend_id)" " LEFT OUTER JOIN poitem ON (poitem_pohead_id=pohead_id" " AND poitem_status='O')" "WHERE (vend_id=<? value(\"vend_id\") ?>) " "GROUP BY vend_name, vend_vendtype_id, vend_terms_id," " vend_shipvia, vend_active, vend_cntct1_id," " vend_cntct2_id, crmacct_id;"); q = mql.toQuery(params); if (q.first()) { _name->setText(q.value("vend_name").toString()); _vendType->setId(q.value("vend_vendtype_id").toInt()); _terms->setId(q.value("vend_terms_id").toInt()); _shipvia->setText(q.value("vend_shipvia").toString()); _active->setChecked(q.value("vend_active").toBool()); _primaryContact->setId(q.value("vend_cntct1_id").toInt()); _secondaryContact->setId(q.value("vend_cntct2_id").toInt()); _crmacctId = q.value("crmacct_id").toInt(); _firstPurchase->setDate(q.value("minpodate").toDate()); _lastPurchase->setDate(q.value("maxpodate").toDate()); _backlog->setDouble(q.value("backlog").toDouble()); } else if (q.lastError().type() != QSqlError::NoError) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } MetaSQLQuery purchbydate("SELECT SUM(currToBase(vohead_curr_id," " vohead_amount," " vohead_gldistdate)) AS purchases " "FROM vohead " "WHERE (vohead_posted" " AND (vohead_gldistdate " " BETWEEN (<? literal(\"older\") ?>)" " AND (<? literal(\"younger\") ?>))" " AND (vohead_vend_id=<? value(\"vend_id\") ?>));"); params.append("older", "DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year'"); params.append("younger", "DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 day'"); q = purchbydate.toQuery(params); if (q.first()) _lastYearsPurchases->setDouble(q.value("purchases").toDouble()); else if (q.lastError().type() != QSqlError::NoError) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } ParameterList ytdparams; ytdparams.append("vend_id", _vend->id()); ytdparams.append("older", "DATE_TRUNC('year', CURRENT_DATE)"); ytdparams.append("younger", "CURRENT_DATE - INTERVAL '1 day'"); q = purchbydate.toQuery(ytdparams); if (q.first()) _ytdPurchases->setDouble(q.value("purchases").toDouble()); else if (q.lastError().type() != QSqlError::NoError) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } MetaSQLQuery balm("SELECT COALESCE(SUM((apopen_amount-apopen_paid) / apopen_curr_rate * " " CASE WHEN (apopen_doctype IN ('D','V')) THEN 1 ELSE -1 END), 0.0) AS balance " "FROM apopen " "WHERE ((apopen_open)" " AND (apopen_vend_id=<? value(\"vend_id\") ?>));"); q = balm.toQuery(params); if (q.first()) _openBalance->setDouble(q.value("balance").toDouble()); else if (q.lastError().type() != QSqlError::NoError) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } }
void printPackingListBatchByShipvia::sPrint() { QPrinter printer(QPrinter::HighResolution); bool setupPrinter = true; if (!_dates->allValid()) { QMessageBox::warning( this, tr("Enter a Valid Start and End Date"), tr("You must enter a valid Start and End Date for this report.") ); _dates->setFocus(); return; } XSqlQuery prtd; QString prts("UPDATE pack SET pack_printed=true" " WHERE ((pack_head_id=<? value('head_id') ?>) " " AND (pack_head_type=<? value('head_type') ?>)" "<? if exists('shiphead_id') ?>" " AND (pack_shiphead_id=<? value('shiphead_id') ?>)" "<? else ?>" " AND (pack_shiphead_id IS NULL)" "<? endif ?>" ");" ); XSqlQuery packq; ParameterList params; _dates->appendValue(params); _warehouse->appendValue(params); if (_metrics->boolean("MultiWhs")) params.append("MultiWhs"); if (_shipvia->isValid()) params.append("shipvia", _shipvia->currentText()); MetaSQLQuery packm = mqlLoad("packingListBatchByShipVia", "print"); packq = packm.toQuery(params); if (packq.lastError().type() != QSqlError::NoError) { systemError(this, packq.lastError().databaseText(), __FILE__, __LINE__); return; } bool userCanceled = false; if (orReport::beginMultiPrint(&printer, userCanceled) == false) { if(!userCanceled) systemError(this, tr("Could not initialize printing system for multiple reports.")); return; } while (packq.next()) { ParameterList params; params.append("head_id", packq.value("pack_head_id").toInt()); params.append("head_type", packq.value("pack_head_type").toString()); if (packq.value("pack_head_type").toString() == "SO") params.append("sohead_id", packq.value("pack_head_id").toInt()); else if (packq.value("pack_head_type").toString() == "TO") params.append("tohead_id", packq.value("pack_head_id").toInt()); if (! packq.value("pack_shiphead_id").isNull()) { params.append("shiphead_id", packq.value("pack_shiphead_id").toInt()); } _warehouse->appendValue(params); if (_metrics->boolean("MultiWhs")) params.append("MultiWhs"); if (packq.value("orderhead_status").toString() != "C") { bool usePickForm; if (_auto->isChecked()) usePickForm = packq.value("pack_shiphead_id").isNull(); else usePickForm = _pick->isChecked(); orReport report(packq.value( usePickForm ? "pickform" : "packform").toString(), params); if (report.isValid()) { if (report.print(&printer, setupPrinter)) { setupPrinter = false; emit finishedPrinting(packq.value("pack_head_id").toInt(), packq.value("pack_head_type").toString(), packq.value("pack_shiphead_id").toInt()); } else { orReport::endMultiPrint(&printer); return; } } else { report.reportError(this); orReport::endMultiPrint(&printer); return; } } MetaSQLQuery mql(prts); prtd = mql.toQuery(params); if (prtd.lastError().type() != QSqlError::NoError) { systemError(this, prtd.lastError().databaseText(), __FILE__, __LINE__); orReport::endMultiPrint(&printer); return; } } orReport::endMultiPrint(&printer); sPopulateShipVia(); }
void vendor::sSave() { struct { bool condition; QString msg; QWidget *widget; } error[] = { { _number->text().trimmed().length() == 0, tr("Please enter a Number for this new Vendor."), _number }, { _name->text().trimmed().length() == 0, tr("Please enter a Name for this new Vendor."), _name }, { _defaultTerms->id() == -1, tr("You must select a Terms code for this Vendor before continuing."), _defaultTerms }, { _vendtype->id() == -1, tr("You must select a Vendor Type for this Vendor before continuing."), _vendtype }, { _achGroup->isChecked() && ! _routingNumber->hasAcceptableInput() && !omfgThis->_key.isEmpty(), tr("The Routing Number is not valid."), _routingNumber }, { _achGroup->isChecked() && ! _achAccountNumber->hasAcceptableInput() && !omfgThis->_key.isEmpty(), tr("The Account Number is not valid."), _achAccountNumber }, { _achGroup->isChecked() && _useACHSpecial->isChecked() && _individualName->text().trimmed().length() == 0 && !omfgThis->_key.isEmpty(), tr("Please enter an Individual Name if EFT Check Printing is enabled and " "'%1' is checked.").arg(_useACHSpecial->title()), _individualName } }; for (unsigned int i = 0; i < sizeof(error) / sizeof(error[0]); i++) if (error[i].condition) { QMessageBox::critical(this, tr("Cannot Save Vendor"), error[i].msg); error[i].widget->setFocus(); return; } if (_number->text().trimmed().toUpper() != _cachedNumber.toUpper()) { q.prepare( "SELECT vend_name " "FROM vendinfo " "WHERE (UPPER(vend_number)=UPPER(:vend_number)) " " AND (vend_id<>:vend_id);" ); q.bindValue(":vend_number", _number->text().trimmed()); q.bindValue(":vend_id", _vendid); q.exec(); if (q.first()) { QMessageBox::critical(this, tr("Vendor Number Used"), tr("<p>The newly entered Vendor Number cannot be " "used as it is already used by the Vendor '%1'. " "Please correct or enter a new Vendor Number." ) .arg(q.value("vend_name").toString()) ); _number->setFocus(); return; } } XSqlQuery rollback; rollback.prepare("ROLLBACK;"); if (! q.exec("BEGIN")) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } int saveResult = _address->save(AddressCluster::CHECK); if (-2 == saveResult) { int answer = QMessageBox::question(this, tr("Question Saving Address"), tr("<p>There are multiple uses of this Vendor's " "Address. What would you like to do?"), tr("Change This One"), tr("Change Address for All"), tr("Cancel"), 2, 2); if (0 == answer) saveResult = _address->save(AddressCluster::CHANGEONE); else if (1 == answer) saveResult = _address->save(AddressCluster::CHANGEALL); } if (saveResult < 0) // not else-if: this is error check for CHANGE{ONE,ALL} { systemError(this, tr("There was an error saving this address (%1).\n" "Check the database server log for errors.") .arg(saveResult), __FILE__, __LINE__); rollback.exec(); _address->setFocus(); return; } QString sql; if (_mode == cEdit) { sql = "UPDATE vendinfo " "SET vend_number=<? value(\"vend_number\") ?>," " vend_accntnum=<? value(\"vend_accntnum\") ?>," " vend_active=<? value(\"vend_active\") ?>," " vend_vendtype_id=<? value(\"vend_vendtype_id\") ?>," " vend_name=<? value(\"vend_name\") ?>," " vend_cntct1_id=<? value(\"vend_cntct1_id\") ?>," " vend_cntct2_id=<? value(\"vend_cntct2_id\") ?>," " vend_addr_id=<? value(\"vend_addr_id\") ?>," " vend_po=<? value(\"vend_po\") ?>," " vend_restrictpurch=<? value(\"vend_restrictpurch\") ?>," " vend_1099=<? value(\"vend_1099\") ?>," " vend_qualified=<? value(\"vend_qualified\") ?>," " vend_comments=<? value(\"vend_comments\") ?>," " vend_pocomments=<? value(\"vend_pocomments\") ?>," " vend_fobsource=<? value(\"vend_fobsource\") ?>," " vend_fob=<? value(\"vend_fob\") ?>," " vend_terms_id=<? value(\"vend_terms_id\") ?>," " vend_shipvia=<? value(\"vend_shipvia\") ?>," " vend_curr_id=<? value(\"vend_curr_id\") ?>," " vend_taxzone_id=<? value(\"vend_taxzone_id\") ?>," " vend_match=<? value(\"vend_match\") ?>," " vend_ach_enabled=<? value(\"vend_ach_enabled\") ?>," "<? if exists(\"key\") ?>" " vend_ach_routingnumber=encrypt(setbytea(<? value(\"vend_ach_routingnumber\") ?>)," " setbytea(<? value(\"key\") ?>), 'bf')," " vend_ach_accntnumber=encrypt(setbytea(<? value(\"vend_ach_accntnumber\") ?>)," " setbytea(<? value(\"key\") ?>), 'bf')," "<? endif ?>" " vend_ach_use_vendinfo=<? value(\"vend_ach_use_vendinfo\") ?>," " vend_ach_accnttype=<? value(\"vend_ach_accnttype\") ?>," " vend_ach_indiv_number=<? value(\"vend_ach_indiv_number\") ?>," " vend_ach_indiv_name=<? value(\"vend_ach_indiv_name\") ?> " "WHERE (vend_id=<? value(\"vend_id\") ?>);" ; } else if (_mode == cNew) sql = "INSERT INTO vendinfo " "( vend_id, vend_number, vend_accntnum," " vend_active, vend_vendtype_id, vend_name," " vend_cntct1_id, vend_cntct2_id, vend_addr_id," " vend_po, vend_restrictpurch," " vend_1099, vend_qualified," " vend_comments, vend_pocomments," " vend_fobsource, vend_fob," " vend_terms_id, vend_shipvia, vend_curr_id," " vend_taxzone_id, vend_match, vend_ach_enabled," " vend_ach_routingnumber, vend_ach_accntnumber," " vend_ach_use_vendinfo," " vend_ach_accnttype, vend_ach_indiv_number," " vend_ach_indiv_name ) " "VALUES " "( <? value(\"vend_id\") ?>," " <? value(\"vend_number\") ?>," " <? value(\"vend_accntnum\") ?>," " <? value(\"vend_active\") ?>," " <? value(\"vend_vendtype_id\") ?>," " <? value(\"vend_name\") ?>," " <? value(\"vend_cntct1_id\") ?>," " <? value(\"vend_cntct2_id\") ?>," " <? value(\"vend_addr_id\") ?>," " <? value(\"vend_po\") ?>," " <? value(\"vend_restrictpurch\") ?>," " <? value(\"vend_1099\") ?>," " <? value(\"vend_qualified\") ?>," " <? value(\"vend_comments\") ?>," " <? value(\"vend_pocomments\") ?>," " <? value(\"vend_fobsource\") ?>," " <? value(\"vend_fob\") ?>," " <? value(\"vend_terms_id\") ?>," " <? value(\"vend_shipvia\") ?>," " <? value(\"vend_curr_id\") ?>, " " <? value(\"vend_taxzone_id\") ?>," " <? value(\"vend_match\") ?>," " <? value(\"vend_ach_enabled\") ?>," "<? if exists(\"key\") ?>" " encrypt(setbytea(<? value(\"vend_ach_routingnumber\") ?>)," " setbytea(<? value(\"key\") ?>), 'bf')," " encrypt(setbytea(<? value(\"vend_ach_accntnumber\") ?>)," " setbytea(<? value(\"key\") ?>), 'bf')," "<? else ?>" " ''," " ''," "<? endif ?>" " <? value(\"vend_ach_use_vendinfo\") ?>," " <? value(\"vend_ach_accnttype\") ?>," " <? value(\"vend_ach_indiv_number\") ?>," " <? value(\"vend_ach_indiv_name\") ?>" " );" ; ParameterList params; params.append("vend_id", _vendid); params.append("vend_vendtype_id", _vendtype->id()); params.append("vend_terms_id", _defaultTerms->id()); params.append("vend_curr_id", _defaultCurr->id()); params.append("vend_number", _number->text().trimmed().toUpper()); params.append("vend_accntnum", _accountNumber->text().trimmed()); params.append("vend_name", _name->text().trimmed()); if (_contact1->id() > 0) params.append("vend_cntct1_id", _contact1->id()); // else NULL if (_contact2->id() > 0) params.append("vend_cntct2_id", _contact2->id()); // else NULL if (_address->id() > 0) params.append("vend_addr_id", _address->id()); // else NULL params.append("vend_comments", _notes->toPlainText()); params.append("vend_pocomments", _poComments->toPlainText()); params.append("vend_shipvia", _defaultShipVia->text()); params.append("vend_active", QVariant(_active->isChecked())); params.append("vend_po", QVariant(_poItems->isChecked())); params.append("vend_restrictpurch", QVariant(_restrictToItemSource->isChecked())); params.append("vend_1099", QVariant(_receives1099->isChecked())); params.append("vend_qualified", QVariant(_qualified->isChecked())); params.append("vend_match", QVariant(_match->isChecked())); if (!omfgThis->_key.isEmpty()) params.append("key", omfgThis->_key); params.append("vend_ach_enabled", QVariant(_achGroup->isChecked())); params.append("vend_ach_routingnumber",_routingNumber->text().trimmed()); params.append("vend_ach_accntnumber", _achAccountNumber->text().trimmed()); params.append("vend_ach_use_vendinfo", QVariant(! _useACHSpecial->isChecked())); params.append("vend_ach_indiv_number", _individualId->text().trimmed()); params.append("vend_ach_indiv_name", _individualName->text().trimmed()); params.append("vend_ach_accnttype", _accountType->code()); if(_taxzone->isValid()) params.append("vend_taxzone_id", _taxzone->id()); if (_useWarehouseFOB->isChecked()) { params.append("vend_fobsource", "W"); params.append("vend_fob", ""); } else if (_useVendorFOB) { params.append("vend_fobsource", "V"); params.append("vend_fob", _vendorFOB->text().trimmed()); } MetaSQLQuery mql(sql); q = mql.toQuery(params); if (q.lastError().type() != QSqlError::NoError) { rollback.exec(); systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } q.exec("COMMIT;"); _NumberGen = -1; omfgThis->sVendorsUpdated(); emit saved(_vendid); if (_mode == cNew) emit newId(_vendid); if(!_ignoreClose) close(); }
void PoLineEdit::setId(int pId) { if ((_x_preferences) && (pId != -1)) { if (_x_preferences->boolean("selectedSites")) { QString msql("SELECT poitem_id " "FROM poitem, itemsite " "WHERE ((poitem_pohead_id=<? value(\"pohead_id\") ?>) " " AND (poitem_itemsite_id=itemsite_id) " " AND (itemsite_warehous_id NOT IN (" " SELECT usrsite_warehous_id " " FROM usrsite " " WHERE (usrsite_username=current_user)))) " "UNION " "SELECT pohead_warehous_id " "FROM pohead " "WHERE ((pohead_id=<? value(\"pohead_id\") ?>) " " AND (pohead_warehous_id NOT IN (" " SELECT usrsite_warehous_id " " FROM usrsite " " WHERE (usrsite_username=current_user))));"); MetaSQLQuery mql(msql); ParameterList params; params.append("pohead_id", pId); XSqlQuery chk = mql.toQuery(params); if (chk.first()) { QMessageBox::critical(this, tr("Access Denied"), tr("You may not view or edit this Purchase Order as it references " "a warehouse for which you have not been granted privileges.")) ; setId(-1); return; } } } QString sql( "SELECT pohead_number, pohead_vend_id," " vend_name, vend_address1, vend_address2, vend_address3," " (vend_city || ' ' || vend_state || ' ' || vend_zip) AS citystatezip " "FROM pohead, vend " "WHERE ( (pohead_vend_id=vend_id)" " AND (pohead_id=:pohead_id)" ); if (_type & (cPOUnposted | cPOOpen | cPOClosed)) { bool qualifier = FALSE; sql += " AND (pohead_status IN ("; if (_type & cPOUnposted) { qualifier = TRUE; sql += "'U'"; } if (_type & cPOOpen) { if (qualifier) sql += ", "; else qualifier = TRUE; sql += "'O'"; } if (_type & cPOClosed) { if (qualifier) sql += ", "; else qualifier = TRUE; sql += "'C'"; } sql += "))"; } sql += " );"; XSqlQuery pohead; pohead.prepare(sql); pohead.bindValue(":pohead_id", pId); pohead.exec(); if (pohead.first()) { _id = pId; _number = pohead.value("pohead_number").toInt(); _vendid = pohead.value("pohead_vend_id").toInt(); _valid = TRUE; emit numberChanged(pohead.value("pohead_number").toString()); emit vendNameChanged(pohead.value("vend_name").toString()); emit vendAddress1Changed(pohead.value("vend_address1").toString()); emit vendAddress2Changed(pohead.value("vend_address2").toString()); emit vendAddress3Changed(pohead.value("vend_address3").toString()); emit vendCityStateZipChanged(pohead.value("citystatezip").toString()); setText(pohead.value("pohead_number").toString()); } else { _id = -1; _number = -1; _vendid = -1; _valid = FALSE; emit numberChanged(""); emit vendNameChanged(""); emit vendAddress1Changed(""); emit vendAddress2Changed(""); emit vendAddress3Changed(""); emit vendCityStateZipChanged(""); setText(""); } emit newId(_id); emit vendidChanged(_vendid); emit numberChanged(_number); emit valid(_valid); if (_mapper->model() && _mapper->model()->data(_mapper->model()->index(_mapper->currentIndex(),_mapper->mappedSection(this))).toString() != text()) _mapper->model()->setData(_mapper->model()->index(_mapper->currentIndex(),_mapper->mappedSection(this)), text()); _parsed = TRUE; }
void releaseWorkOrdersByPlannerCode::sRelease() { /* we're going to use essentially the same query twice: 1 - to print the paperwork 2 - to release the work orders */ QString sql( "SELECT <? if exists(\"paperwork\") ?>" " wo_id, CAST(wo_qtyord AS INTEGER) AS wo_qtyord_int " " <? else ?>" " releaseWo(wo_id, FALSE) " " <? endif ?>" "FROM wo, itemsite, plancode " "WHERE ((wo_itemsite_id=itemsite_id)" " AND (itemsite_plancode_id=plancode_id)" " AND (wo_status='E')" "<? if exists (\"byStartDate\") ?>" " AND (wo_startdate<=<? value(\"cutOffDate\") ?>)" "<? elseif exists (\"byDueDate\") ?>" " AND (wo_duedate<=<? value(\"cutOffDate\") ?>)" "<? endif ?>" "<? if exists(\"warehous_id\") ?>" " AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)" "<? endif ?>" "<? if exists(\"plancode_id\") ?>" " AND (itemsite_plancode_id=<? value(\"plancode_id\") ?>)" "<? elseif exists(\"plancode_pattern\") ?>" " AND (itemsite_plancode_id IN (" " SELECT plancode_id" " FROM plancode" " WHERE (plancode_code ~ <? value(\"plancode_pattern\") ?>)))" "<? endif ?>" ");" ); if (_pickList->isChecked() || _routing->isChecked() || _woLabel->isChecked() || _packingList->isChecked()) { ParameterList wop; if (! setParams(wop)) return; wop.append("paperwork"); MetaSQLQuery wom(sql); q = wom.toQuery(wop); if (q.lastError().type() != QSqlError::None) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } QPrinter printer(QPrinter::HighResolution); bool setupPrinter = TRUE; bool userCanceled = false; while (q.next()) { if (setupPrinter && orReport::beginMultiPrint(&printer, userCanceled) == false) { if(!userCanceled) systemError(this, tr("<p>Could not initialize printing system for " "multiple reports.")); return; } ParameterList params; params.append("wo_id", q.value("wo_id")); params.append("labelTo", q.value("wo_qtyord_int")); if (_pickList->isChecked()) { orReport report("PickList", params); if (report.isValid() && report.print(&printer, setupPrinter)) setupPrinter = FALSE; else { report.reportError(this); orReport::endMultiPrint(&printer); return; } } if (_routing->isChecked()) { orReport report("Routing", params); if (report.isValid() && report.print(&printer, setupPrinter)) setupPrinter = FALSE; else { report.reportError(this); orReport::endMultiPrint(&printer); return; } } if (_woLabel->isChecked()) { orReport report("WOLabel", params); if (report.isValid() && report.print(&printer, setupPrinter)) setupPrinter = FALSE; else { report.reportError(this); orReport::endMultiPrint(&printer); return; } } if (_packingList->isChecked()) { XSqlQuery query; query.prepare( "SELECT cohead_id, findCustomerForm(cohead_cust_id, 'L') AS reportname " "FROM cohead, coitem, wo " "WHERE ( (coitem_cohead_id=cohead_id)" " AND (wo_ordid=coitem_id)" " AND (wo_ordtype='S')" " AND (wo_id=:wo_id) );" ); query.bindValue(":wo_id", q.value("wo_id")); // from outer loop query query.exec(); if (query.first()) { ParameterList params; params.append("sohead_id", query.value("cohead_id")); params.append("head_id", query.value("cohead_id")); params.append("head_type", "SO"); if (_metrics->boolean("MultiWhs")) params.append("MultiWhs"); orReport report(query.value("reportname").toString(), params); if (report.isValid() && report.print(&printer, setupPrinter)) setupPrinter = FALSE; else { report.reportError(this); orReport::endMultiPrint(&printer); return; } } else if (query.lastError().type() != QSqlError::None) { systemError(this, query.lastError().databaseText(), __FILE__, __LINE__); orReport::endMultiPrint(&printer); return; } } } if (! setupPrinter) // we tried to print something orReport::endMultiPrint(&printer); if (QMessageBox::question(this, tr("Print Correctly?"), tr("<p>Did the documents all print correctly?"), QMessageBox::Yes, QMessageBox::No | QMessageBox::Default) == QMessageBox::No) return; } ParameterList params; if (! setParams(params)) return; MetaSQLQuery mql(sql); q = mql.toQuery(params); if (q.lastError().type() != QSqlError::None) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } omfgThis->sWorkOrdersUpdated(-1, TRUE); accept(); }
QString ExportHelper::generateDelimited(QString qtext, ParameterList ¶ms, QString &errmsg) { if (DEBUG) qDebug("ExportHelper::generateDelimited(%s..., %d params, errmsg) entered", qPrintable(qtext.left(80)), params.size()); if (qtext.isEmpty()) return QString::null; if (DEBUG) { QStringList plist; for (int i = 0; i < params.size(); i++) plist.append("\t" + params.name(i) + ":\t" + params.value(i).toString()); qDebug("generateDelimited parameters:\n%s", qPrintable(plist.join("\n"))); } bool valid; QString delim = params.value("delim", &valid).toString(); if (! valid) delim = ","; if (DEBUG) qDebug("generateDelimited(qtest, params, errmsg) delim = %s, valid = %d", qPrintable(delim), valid); QVariant includeheaderVar = params.value("includeHeaderLine", &valid); bool includeheader = (valid ? includeheaderVar.toBool() : false); if (DEBUG) qDebug("generateDelimited(qtest, params, errmsg) includeheader = %d, valid = %d", includeheader, valid); QStringList line; MetaSQLQuery mql(qtext); XSqlQuery qry = mql.toQuery(params); if (qry.first()) { QStringList field; int cols = qry.record().count(); if (includeheader) { for (int p = 0; p < cols; p++) field.append(qry.record().fieldName(p)); line.append(field.join(delim)); } QString tmp; do { field.clear(); for (int p = 0; p < cols; p++) { tmp = qry.value(p).toString(); if (tmp.contains(delim)) { tmp.replace("\"", "\"\""); tmp = "\"" + tmp + "\""; } field.append(tmp); } line.append(field.join(delim)); } while (qry.next()); } if (qry.lastError().type() != QSqlError::NoError) errmsg = qry.lastError().text(); return line.join("\n"); }
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; } }
QString ExportHelper::generateXML(const int qryheadid, ParameterList ¶ms, QString &errmsg, int xsltmapid) { if (DEBUG) qDebug("ExportHelper::generateXML(%d, %d params, errmsg, %d) entered", qryheadid, params.size(), xsltmapid); if (DEBUG) { QStringList plist; for (int i = 0; i < params.size(); i++) plist.append("\t" + params.name(i) + ":\t" + params.value(i).toString()); qDebug("generateXML parameters:\n%s", qPrintable(plist.join("\n"))); } QDomDocument xmldoc("xtupleimport"); QDomElement rootelem = xmldoc.createElement("xtupleimport"); xmldoc.appendChild(rootelem); XSqlQuery itemq; QString tableElemName; QString schemaName; itemq.prepare("SELECT * FROM qryitem WHERE qryitem_qryhead_id=:id ORDER BY qryitem_order;"); itemq.bindValue(":id", qryheadid); itemq.exec(); while (itemq.next()) { QString qtext; tableElemName = itemq.value("qryitem_name").toString(); if (itemq.value("qryitem_src").toString() == "REL") { schemaName = itemq.value("qryitem_group").toString(); qtext = "SELECT * FROM " + (schemaName.isEmpty() ? QString("") : schemaName + QString(".")) + itemq.value("qryitem_detail").toString(); } else if (itemq.value("qryitem_src").toString() == "MQL") { QString tmpmsg; bool valid; qtext = MQLUtil::mqlLoad(itemq.value("qryitem_group").toString(), itemq.value("qryitem_detail").toString(), tmpmsg, &valid); if (! valid) errmsg = tmpmsg; } else if (itemq.value("qryitem_src").toString() == "CUSTOM") qtext = itemq.value("qryitem_detail").toString(); if (! qtext.isEmpty()) { MetaSQLQuery mql(qtext); XSqlQuery qry = mql.toQuery(params); if (qry.first()) { do { QDomElement tableElem = xmldoc.createElement(tableElemName); if (DEBUG) qDebug("exportXML starting %s", qPrintable(tableElemName)); if (! schemaName.isEmpty()) tableElem.setAttribute("schema", schemaName); for (int i = 0; i < qry.record().count(); i++) { QDomElement fieldElem = xmldoc.createElement(qry.record().fieldName(i)); if (qry.record().value(i).isNull()) fieldElem.appendChild(xmldoc.createTextNode("[NULL]")); else fieldElem.appendChild(xmldoc.createTextNode(qry.record().value(i).toString())); tableElem.appendChild(fieldElem); } rootelem.appendChild(tableElem); } while (qry.next()); } if (qry.lastError().type() != QSqlError::NoError) errmsg = qry.lastError().text(); } } if (itemq.lastError().type() != QSqlError::NoError) errmsg = itemq.lastError().text(); if (xsltmapid < 0) return xmldoc.toString(); else return XSLTConvertString(xmldoc.toString(), xsltmapid, errmsg); }
void selectOrderForBilling::sFillList() { _soitem->clear(); if (_so->isValid()) { QString sql( "SELECT coitem_id, coitem_linenumber," " item_number, iteminvpricerat(item_id) AS invpricerat," " warehous_code, coitem_price," " uom_name," " formatQty(coitem_qtyord) AS f_qtyord," " formatQty(coitem_qtyshipped) AS f_qtyshipped," " formatQty(coitem_qtyreturned) AS f_qtyreturned," " formatQty( ( SELECT COALESCE(SUM(coship_qty), 0)" " FROM coship " " WHERE ( (coship_coitem_id=coitem_id)" " AND (NOT coship_invoiced) ) )" " ) AS f_qtyatship," " ( SELECT COALESCE(SUM(cobill_qty), 0)" " FROM cobill, cobmisc " " WHERE ( (cobill_cobmisc_id=cobmisc_id)" " AND (cobill_coitem_id=coitem_id)" " AND (NOT cobmisc_posted) ) ) AS qtytobill," " round(( " " ( SELECT COALESCE(SUM(cobill_qty), 0)" " FROM cobill, cobmisc " " WHERE ( (cobill_cobmisc_id=cobmisc_id)" " AND (cobill_coitem_id=coitem_id)" " AND (NOT cobmisc_posted) ) ) * coitem_qty_invuomratio " " * ( coitem_price / coitem_price_invuomratio)), 2) AS extended, " " formatMoney(round(( " " ( SELECT COALESCE(SUM(cobill_qty), 0)" " FROM cobill, cobmisc " " WHERE ( (cobill_cobmisc_id=cobmisc_id)" " AND (cobill_coitem_id=coitem_id)" " AND (NOT cobmisc_posted) ) ) * coitem_qty_invuomratio " " * (coitem_price / coitem_price_invuomratio)), 2)) AS f_extended, " " formatBoolYN( ( SELECT COALESCE(cobill_toclose, FALSE)" " FROM cobill, cobmisc " " WHERE ((cobill_cobmisc_id=cobmisc_id)" " AND (cobill_coitem_id=coitem_id)" " AND (NOT cobmisc_posted))" " ORDER BY cobill_toclose DESC" " LIMIT 1) ) AS toclose " "FROM coitem, itemsite, item, warehous, uom " "WHERE ( (coitem_itemsite_id=itemsite_id)" " AND (coitem_status <> 'X')" " AND (coitem_qty_uom_id=uom_id)" " AND (itemsite_item_id=item_id)" " AND (itemsite_warehous_id=warehous_id)" " <? if exists(\"showOpenOnly\") ?>" " AND (coitem_status <> 'C')" " <? endif ?>" " AND (coitem_cohead_id=<? value(\"sohead_id\") ?>) ) " "ORDER BY coitem_linenumber;" ); ParameterList params; if (!_showClosed->isChecked()) params.append("showOpenOnly"); params.append("sohead_id", _so->id()); MetaSQLQuery mql(sql); q = mql.toQuery(params); if (q.first()) { double subtotal = 0.0; XTreeWidgetItem* last = 0; do { subtotal += q.value("extended").toDouble(); last = new XTreeWidgetItem(_soitem, last, q.value("coitem_id").toInt(), q.value("coitem_linenumber"), q.value("item_number"), q.value("warehous_code"), q.value("uom_name"), q.value("f_qtyord"), q.value("f_qtyshipped"), q.value("f_qtyreturned"), q.value("f_qtyatship"), formatQty(q.value("qtytobill").toDouble()), q.value("f_extended"), q.value("toclose") ); } while (q.next()); _subtotal->setLocalValue(subtotal); } else { if (q.lastError().type() != QSqlError::None) systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); _subtotal->clear(); } recalculateTax(); } }
void vendor::populate() { MetaSQLQuery mql( "SELECT *, crmacct_id, " "<? if exists(\"key\") ?>" " CASE WHEN LENGTH(vend_ach_routingnumber) > 0 THEN" " formatbytea(decrypt(setbytea(vend_ach_routingnumber)," " setbytea(<? value(\"key\") ?>), 'bf'))" " ELSE '' END AS routingnum," " CASE WHEN LENGTH(vend_ach_accntnumber) > 0 THEN" " formatbytea(decrypt(setbytea(vend_ach_accntnumber)," " setbytea(<? value(\"key\") ?>), 'bf'))" " ELSE '' END AS accntnum " "<? else ?>" " <? value(\"na\") ?> AS routingnum," " <? value(\"na\") ?> AS accntnum " "<? endif ?>" "FROM vendinfo " " JOIN crmacct ON (vend_id=crmacct_vend_id) " "WHERE (vend_id=<? value(\"vend_id\") ?>);" ); ParameterList params; params.append("vend_id", _vendid); params.append("key", omfgThis->_key); params.append("na", tr("N/A")); q = mql.toQuery(params); if (q.first()) { _cachedNumber = q.value("vend_number").toString(); _number->setText(q.value("vend_number")); _accountNumber->setText(q.value("vend_accntnum")); _vendtype->setId(q.value("vend_vendtype_id").toInt()); _active->setChecked(q.value("vend_active").toBool()); _name->setText(q.value("vend_name")); _contact1->setId(q.value("vend_cntct1_id").toInt()); _contact1->setSearchAcct(q.value("crmacct_id").toInt()); _contact2->setId(q.value("vend_cntct2_id").toInt()); _contact2->setSearchAcct(q.value("crmacct_id").toInt()); _address->setId(q.value("vend_addr_id").toInt()); _defaultTerms->setId(q.value("vend_terms_id").toInt()); _defaultShipVia->setText(q.value("vend_shipvia").toString()); _defaultCurr->setId(q.value("vend_curr_id").toInt()); _poItems->setChecked(q.value("vend_po").toBool()); _restrictToItemSource->setChecked(q.value("vend_restrictpurch").toBool()); _receives1099->setChecked(q.value("vend_1099").toBool()); _match->setChecked(q.value("vend_match").toBool()); _qualified->setChecked(q.value("vend_qualified").toBool()); _notes->setText(q.value("vend_comments").toString()); _poComments->setText(q.value("vend_pocomments").toString()); _emailPODelivery->setChecked(q.value("vend_emailpodelivery").toBool()); _ediEmail->setText(q.value("vend_ediemail")); _ediSubject->setText(q.value("vend_edisubject")); _ediFilename->setText(q.value("vend_edifilename")); _ediEmailBody->setText(q.value("vend_ediemailbody").toString()); _ediCC->setText(q.value("vend_edicc").toString()); _taxauth->setId(q.value("vend_taxauth_id").toInt()); if (q.value("vend_fobsource").toString() == "V") { _useVendorFOB->setChecked(TRUE); _vendorFOB->setText(q.value("vend_fob")); } else _useWarehouseFOB->setChecked(TRUE); _achGroup->setChecked(q.value("vend_ach_enabled").toBool()); _routingNumber->setText(q.value("routingnum").toString()); _achAccountNumber->setText(q.value("accntnum").toString()); _useACHSpecial->setChecked(! q.value("vend_ach_use_vendinfo").toBool()); _individualId->setText(q.value("vend_ach_indiv_number").toString()); _individualName->setText(q.value("vend_ach_indiv_name").toString()); if (q.value("vend_ach_accnttype").toString() == "K") _accountType->setCurrentItem(0); else if (q.value("vend_ach_accnttype").toString() == "C") _accountType->setCurrentItem(1); sFillAddressList(); sFillTaxregList(); _comments->setId(_vendid); } else if (q.lastError().type() == QSqlError::NoError) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } q.prepare("SELECT crmacct_id " "FROM crmacct " "WHERE (crmacct_vend_id=:vend_id);"); q.bindValue(":vend_id", _vendid); q.exec(); if (q.first()) _crmacctid = q.value("crmacct_id").toInt(); else if (q.lastError().type() != QSqlError::NoError) { systemError(this, q.lastError().databaseText(), __FILE__, __LINE__); return; } }