void salesHistoryInformation::populate()
{
q.prepare( "SELECT cohist_ordernumber, cohist_invcnumber,"
" cohist_orderdate, cohist_invcdate,"
" cohist_billtoname, cohist_billtoaddress1,"
" cohist_billtoaddress2, cohist_billtoaddress3,"
" cohist_billtocity, cohist_billtostate, cohist_billtozip,"
" cohist_shiptoname, cohist_shiptoaddress1,"
" cohist_shiptoaddress2, cohist_shiptoaddress3,"
" cohist_shiptocity, cohist_shiptostate, cohist_shiptozip,"
" cohist_itemsite_id, cohist_salesrep_id, cohist_commissionpaid,"
" cohist_qtyshipped, cohist_unitprice, cohist_unitcost,"
" formatMoney(cohist_qtyshipped * cohist_unitprice) AS extprice,"
" formatMoney(cohist_qtyshipped * cohist_unitcost) AS extcost,"
" formatMoney(cohist_commission) AS f_commission "
"FROM cohist "
"WHERE (cohist_id=:sohist_id);" );
q.bindValue(":sohist_id", _sohistid);
q.exec();
if (q.first())
{
_orderNumber->setText(q.value("cohist_ordernumber"));
_invoiceNumber->setText(q.value("cohist_invcnumber"));
_orderDate->setDate(q.value("cohist_orderdate").toDate());
_invoiceDate->setDate(q.value("cohist_invcdate").toDate());
_billtoName->setText(q.value("cohist_billtoname"));
_billtoAddress1->setText(q.value("cohist_billtoaddress1"));
_billtoAddress2->setText(q.value("cohist_billtoaddress2"));
_billtoAddress3->setText(q.value("cohist_billtoaddress3"));
_billtoCity->setText(q.value("cohist_billtocity"));
_billtoState->setText(q.value("cohist_billtostate"));
_billtoZip->setText(q.value("cohist_billtozip"));
_shiptoName->setText(q.value("cohist_shiptoname"));
_shiptoAddress1->setText(q.value("cohist_shiptoaddress1"));
_shiptoAddress2->setText(q.value("cohist_shiptoaddress2"));
_shiptoAddress3->setText(q.value("cohist_shiptoaddress3"));
_shiptoCity->setText(q.value("cohist_shiptocity"));
_shiptoState->setText(q.value("cohist_shiptostate"));
_shiptoZip->setText(q.value("cohist_shiptozip"));
_item->setItemsiteid(q.value("cohist_itemsite_id").toInt());
_shipped->setText(formatQty(q.value("cohist_qtyshipped").toDouble()));
_unitPrice->setText(formatSalesPrice(q.value("cohist_unitprice").toDouble()));
_unitCost->setText(formatSalesPrice(q.value("cohist_unitcost").toDouble()));
_extendedPrice->setText(q.value("extprice").toString());
_extendedCost->setText(q.value("extcost").toString());
_salesrep->setId(q.value("cohist_salesrep_id").toInt());
_commission->setText(q.value("f_commission"));
_commissionPaid->setChecked(q.value("cohist_commissionpaid").toBool());
}
}
void dspBookingsByProductCategory::sFillList()
{
if (!checkParameters())
return;
_soitem->clear();
MetaSQLQuery mql = mqlLoad(":/so/displays/SalesOrderItems.mql");
ParameterList params;
_dates->appendValue(params);
_warehouse->appendValue(params);
_productCategory->appendValue(params);
params.append("orderByOrderdate");
q = mql.toQuery(params);
XTreeWidgetItem *last = 0;
bool exchangeError = false;
while (q.next())
{
if (q.value("baseunitprice").toDouble() < 0.0)
exchangeError = true;
last = new XTreeWidgetItem(_soitem, last,
q.value("coitem_id").toInt(),
q.value("cohead_number"),
formatDate(q.value("cohead_orderdate").toDate()),
q.value("cust_number"),
q.value("cust_name"),
q.value("item_number"),
formatQty(q.value("coitem_qtyord").toDouble()),
formatSalesPrice(q.value("baseunitprice").toDouble()),
formatMoney(q.value("baseextprice").toDouble()) );
}
if (exchangeError)
QMessageBox::warning( this, tr("Currency Exchange Rate Error"),
tr("One or more of the Prices could not be converted to Base Currency.\n"
"These Prices have been set to a negative value.") );
}
void characteristicAssignment::populate()
{
q.prepare( "SELECT charass_target_id, charass_target_type, charass_char_id, charass_value, charass_default, charass_price "
"FROM charass "
"WHERE (charass_id=:charass_id);" );
q.bindValue(":charass_id", _charassid);
q.exec();
if (q.first())
{
_targetId = q.value("charass_target_id").toInt();
_targetType = q.value("charass_target_type").toString();
handleTargetType();
_char->setId(q.value("charass_char_id").toInt());
_value->setText(q.value("charass_value").toString());
_listprice->setText(formatSalesPrice(q.value("charass_price").toDouble()));
_default->setChecked(q.value("charass_default").toBool());
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void dspSalesHistoryByShipTo::sFillList()
{
if (!checkParameters())
return;
_sohist->clear();
MetaSQLQuery mql = mqlLoad(":/so/displays/SalesHistory.mql");
ParameterList params;
_dates->appendValue(params);
_warehouse->appendValue(params);
_productCategory->appendValue(params);
params.append("shipto_id", _shipTo->id());
params.append("orderByInvcdateItem");
q = mql.toQuery(params);
if (q.first())
{
double totalUnits = 0.0;
double totalSales = 0.0;
double totalCosts = 0.0;
bool exchangeError = false;
XTreeWidgetItem *last = 0;
do
{
if (q.value("baseunitprice").toDouble() < 0.0)
exchangeError = true;
QString invoicedate = tr("Return");
if (q.value("cohist_invcdate").toString() != "")
invoicedate = formatDate(q.value("cohist_invcdate").toDate());
last = new XTreeWidgetItem( _sohist, last, q.value("cohist_id").toInt(),
q.value("cohist_ordernumber"),
q.value("invoicenumber"),
formatDate(q.value("cohist_orderdate").toDate()),
invoicedate,
q.value("item_number"),
q.value("itemdescription"),
formatQty(q.value("cohist_qtyshipped").toDouble()),
formatSalesPrice(q.value("baseunitprice").toDouble()),
formatMoney(q.value("baseextprice").toDouble()),
formatCost(q.value("cohist_unitcost").toDouble()),
formatMoney(q.value("extcost").toDouble()) );
totalUnits += q.value("cohist_qtyshipped").toDouble();
totalSales += q.value("baseextprice").toDouble();
totalCosts += q.value("extcost").toDouble();
}
while (q.next());
XTreeWidgetItem *totals = new XTreeWidgetItem(_sohist, last, -1);
totals->setText(5, tr("Totals"));
totals->setText(6, formatQty(totalUnits));
totals->setText(EXTPRICE_COL, formatMoney(totalSales));
totals->setText(EXTCOST_COL, formatCost(totalCosts));
if ( (exchangeError) && (_showPrices->isChecked()) )
QMessageBox::warning( this, tr("Currency Exchange Rate Error"),
tr("One or more of the Prices could not be converted to Base Currency.\n"
"These Prices have been set to a negative value.") );
}
}
void dspPricesByItem::sFillList()
{
_price->clear();
if (_item->isValid())
{
double cost = 0.0;
if (_showCosts->isChecked())
{
if (_useStandardCosts->isChecked())
q.prepare( "SELECT (stdCost(item_id) * iteminvpricerat(item_id)) AS cost "
"FROM item "
"WHERE (item_id=:item_id);");
else if (_useActualCosts->isChecked())
q.prepare( "SELECT (actCost(item_id) * iteminvpricerat(item_id)) AS cost "
"FROM item "
"WHERE (item_id=:item_id);");
q.bindValue(":item_id", _item->id());
q.exec();
if (q.first())
cost = q.value("cost").toDouble();
// ToDo
}
QString sql( "SELECT ipsprice_id AS itemid, 1 AS sourcetype,"
" ipshead_name AS schedulename, :customer AS type,"
" cust_name AS typename,"
" CASE WHEN (ipsprice_qtybreak = -1) THEN :na"
" ELSE formatQty(ipsprice_qtybreak)"
" END AS f_qtybreak,"
" ipsprice_price AS price, currConcat(ipshead_curr_id) AS currConcat "
"FROM ipsass, ipshead, ipsprice, cust, item "
"WHERE ( (ipsass_ipshead_id=ipshead_id)"
" AND (ipsprice_ipshead_id=ipshead_id)"
" AND (ipsass_cust_id=cust_id)"
" AND (COALESCE(LENGTH(ipsass_shipto_pattern), 0) = 0)"
" AND (ipsprice_item_id=item_id)"
" AND (item_id=:item_id)" );
if (!_showExpired->isChecked())
sql += " AND (ipshead_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (ipshead_effective <= CURRENT_DATE)";
sql += ") "
"UNION SELECT ipsprice_id AS itemid, 2 AS sourcetype,"
" ipshead_name AS schedulename, :custType AS type,"
" (custtype_code || '-' || custtype_descrip) AS typename,"
" CASE WHEN (ipsprice_qtybreak = -1) THEN :na"
" ELSE formatQty(ipsprice_qtybreak)"
" END AS f_qtybreak,"
" ipsprice_price AS price, currConcat(ipshead_curr_id) AS currConcat "
"FROM ipsass, ipshead, ipsprice, custtype, item "
"WHERE ( (ipsass_ipshead_id=ipshead_id)"
" AND (ipsprice_ipshead_id=ipshead_id)"
" AND (ipsass_custtype_id=custtype_id)"
" AND (ipsprice_item_id=item_id)"
" AND (item_id=:item_id)";
if (!_showExpired->isChecked())
sql += " AND (ipshead_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (ipshead_effective <= CURRENT_DATE)";
sql += ") "
"UNION SELECT ipsprice_id AS itemid, 3 AS sourcetype,"
" ipshead_name AS schedulename, :custTypePattern AS type,"
" (custtype_code || '-' || custtype_descrip) AS typename,"
" CASE WHEN (ipsprice_qtybreak = -1) THEN :na"
" ELSE formatQty(ipsprice_qtybreak)"
" END AS f_qtybreak,"
" ipsprice_price AS price, currConcat(ipshead_curr_id) AS currConcat "
"FROM ipsass, ipshead, ipsprice, custtype, item "
"WHERE ( (ipsass_ipshead_id=ipshead_id)"
" AND (ipsprice_ipshead_id=ipshead_id)"
" AND (coalesce(length(ipsass_custtype_pattern), 0) > 0)"
" AND (custtype_code ~ ipsass_custtype_pattern)"
" AND (ipsprice_item_id=item_id)"
" AND (item_id=:item_id)";
if (!_showExpired->isChecked())
sql += " AND (ipshead_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (ipshead_effective <= CURRENT_DATE)";
sql += ") "
"UNION SELECT ipsprice_id AS itemid, 4 AS sourcetype,"
" ipshead_name AS schedulename, :sale AS type,"
" sale_name AS typename,"
" CASE WHEN (ipsprice_qtybreak = -1) THEN :na"
" ELSE formatQty(ipsprice_qtybreak)"
" END AS f_qtybreak,"
" ipsprice_price AS price, currConcat(ipshead_curr_id) AS currConcat "
"FROM sale, ipshead, ipsprice, item "
"WHERE ( (sale_ipshead_id=ipshead_id)"
" AND (ipsprice_ipshead_id=ipshead_id)"
" AND (ipsprice_item_id=:item_id)";
if (!_showExpired->isChecked())
sql += " AND (sale_enddate > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (sale_startdate <= CURRENT_DATE)";
sql += ") "
"UNION SELECT ipsprice_id AS itemid, 5 AS sourcetype,"
" ipshead_name AS schedulename, :shipTo AS type,"
" (cust_name || '-' || shipto_num) AS typename,"
" CASE WHEN (ipsprice_qtybreak = -1) THEN :na"
" ELSE formatQty(ipsprice_qtybreak)"
" END AS f_qtybreak,"
" ipsprice_price AS price, currConcat(ipshead_curr_id) AS currConcat "
"FROM ipsass, ipshead, ipsprice, cust, shipto, item "
"WHERE ( (ipsass_ipshead_id=ipshead_id)"
" AND (ipsprice_ipshead_id=ipshead_id)"
" AND (ipsass_shipto_id=shipto_id)"
" AND (shipto_cust_id=cust_id)"
" AND (ipsprice_item_id=item_id)"
" AND (item_id=:item_id)";
if (!_showExpired->isChecked())
sql += " AND (ipshead_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (ipshead_effective <= CURRENT_DATE)";
sql += ") "
"UNION SELECT ipsprice_id AS itemid, 6 AS sourcetype,"
" ipshead_name AS schedulename, :shipToPattern AS type,"
" (cust_name || '-' || shipto_num) AS typename,"
" CASE WHEN (ipsprice_qtybreak = -1) THEN :na"
" ELSE formatQty(ipsprice_qtybreak)"
" END AS f_qtybreak,"
" ipsprice_price AS price, currConcat(ipshead_curr_id) AS currConcat "
"FROM ipsass, ipshead, ipsprice, cust, shipto, item "
"WHERE ( (ipsass_ipshead_id=ipshead_id)"
" AND (ipsprice_ipshead_id=ipshead_id)"
" AND (COALESCE(LENGTH(ipsass_shipto_pattern),0) > 0)"
" AND (shipto_num ~ ipsass_shipto_pattern)"
" AND (ipsass_cust_id=cust_id)"
" AND (shipto_cust_id=cust_id)"
" AND (ipsprice_item_id=item_id)"
" AND (item_id=:item_id)";
if (!_showExpired->isChecked())
sql += " AND (ipshead_expires > CURRENT_DATE)";
if (!_showFuture->isChecked())
sql += " AND (ipshead_effective <= CURRENT_DATE)";
sql += ") "
"UNION SELECT item_id AS itemid, 0 AS sourcetype,"
" :listPrice AS schedulename, :na AS type,"
" '' AS typename,"
" :na AS f_qtybreak,"
" item_listprice AS price, currConcat(baseCurrId()) AS currConcat "
"FROM item "
"WHERE ( (NOT item_exclusive)"
" AND (item_id=:item_id) ) "
"ORDER BY price;";
q.prepare(sql);
q.bindValue(":na", tr("N/A"));
q.bindValue(":customer", tr("Customer"));
q.bindValue(":shipTo", tr("Cust. Ship-To"));
q.bindValue(":shipToPattern", tr("Cust. Ship-To Pattern"));
q.bindValue(":custType", tr("Cust. Type"));
q.bindValue(":custTypePattern", tr("Cust. Type Pattern"));
q.bindValue(":sale", tr("Sale"));
q.bindValue(":listPrice", tr("List Price"));
q.bindValue(":item_id", _item->id());
q.exec();
XTreeWidgetItem *last = 0;
while (q.next())
{
double price = q.value("price").toDouble();
last = new XTreeWidgetItem(_price, last, q.value("itemid").toInt(),
q.value("sourcetype").toInt(),
q.value("schedulename"), q.value("type"),
q.value("typename"), q.value("f_qtybreak"),
formatSalesPrice(q.value("price").toDouble()),
q.value("currConcat"),
formatCost(cost),
(price != 0) ? formatPercent(((price - cost) / price)) : QString());
if (cost > price)
last->setTextColor(MARGIN_COL, "red");
}
}
}
void CurrDisplay::sReformat() const
{
if (DEBUG)
qDebug("CD %s::sReformat() entered with _state %d, _format %d",
qPrintable(objectName()), _state, _format);
QString na = tr("N/A");
QString unknown = tr(UNKNOWNSTR);
QString local = "";
switch (_state)
{
case New:
local = "";
break;
case NANew:
local = na;
break;
case Initialized:
if (isZero())
switch (_format)
{
case SalesPrice:
local = formatSalesPrice(0.0, id());
break;
case PurchPrice:
local = formatPurchPrice(0.0, id());
break;
case ExtPrice:
local = formatExtPrice(0.0, id());
break;
case Cost:
local = formatCost(0.0, id());
break;
case Money:
default:
local = formatMoney(0.0, id());
break;
}
else if (_localKnown)
switch (_format)
{
case SalesPrice:
local = formatSalesPrice(_valueLocal, id());
break;
case PurchPrice:
local = formatPurchPrice(_valueLocal, id());
break;
case ExtPrice:
local = formatExtPrice(_valueLocal, id());
break;
case Cost:
local = formatCost(_valueLocal, id());
break;
case Money:
default:
local = formatMoney(_valueLocal, id());
break;
}
else
local = unknown;
break;
case NAInit:
if (isZero())
local = na;
else
switch (_format)
{
case SalesPrice:
local = formatSalesPrice(_valueLocal, id());
break;
case PurchPrice:
local = formatPurchPrice(_valueLocal, id());
break;
case ExtPrice:
local = formatExtPrice(_valueLocal, id());
break;
case Cost:
local = formatCost(_valueLocal, id());
break;
case Money:
default:
local = formatMoney(_valueLocal, id());
break;
}
break;
default:
break;
}
_valueLocalWidget->setText(local);
}
void dspSummarizedSalesByCustomerTypeByItem::sFillList()
{
_sohist->clear();
if (!checkParameters())
return;
QString sql( "SELECT cohist_itemsite_id, item_number, itemdescription, warehous_code,"
" minprice, maxprice, avgprice, totalunits, totalsales "
"FROM ( SELECT cohist_itemsite_id, item_number, itemdescription,"
" warehous_code, MIN(baseunitprice) AS minprice, MAX(baseunitprice) AS maxprice,"
" AVG(baseunitprice) AS avgprice, SUM(cohist_qtyshipped) AS totalunits,"
" SUM(baseextprice) AS totalsales "
" FROM saleshistory "
" WHERE ( (cohist_invcdate BETWEEN :startDate AND :endDate)" );
if (_warehouse->isSelected())
sql += " AND (itemsite_warehous_id=:warehous_id)";
if (_customerType->isSelected())
sql += " AND (cust_custtype_id=:custtype_id)";
else if (_customerType->isPattern())
sql += " AND (custtype_code ~ :custtype_pattern)";
sql += ") "
"GROUP BY cohist_itemsite_id, item_number, itemdescription, warehous_code ) AS data "
"ORDER BY item_number, warehous_code;";
q.prepare(sql);
_warehouse->bindValue(q);
_customerType->bindValue(q);
_dates->bindValue(q);
q.exec();
if (q.first())
{
double totalSales = 0.0;
XTreeWidgetItem *last = 0;
do
{
last = new XTreeWidgetItem(_sohist, last, q.value("cohist_itemsite_id").toInt(),
q.value("item_number"),
q.value("itemdescription"),
q.value("warehous_code"),
formatSalesPrice(q.value("minprice").toDouble()),
formatSalesPrice(q.value("maxprice").toDouble()),
formatSalesPrice(q.value("avgprice").toDouble()),
formatQty(q.value("totalunits").toDouble()),
formatMoney(q.value("totalsales").toDouble()) );
totalSales += q.value("totalsales").toDouble();
}
while (q.next());
new XTreeWidgetItem( _sohist, last, -1,
"", tr("Totals"), "", "", "", "", "",
formatMoney(totalSales) );
_sohist->setDragString("itemsiteid=");
}
}
