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