/***
Convert a broken-down localtime table into an epoch time.
@function mktime
@tparam PosixTm broken-down localtime
@treturn int seconds since epoch
@see mktime(3)
@see localtime
*/
static int
Pmktime(lua_State *L)
{
struct tm t;
time_t epoch;
checknargs(L, 1);
totm(L, 1, &t);
if ((epoch = mktime(&t)) < 0)
return 0;
return pushintresult(epoch);
}
/***
Write a time out according to a format.
@function strftime
@string format specifier with `%` place-holders
@tparam PosixTm tm broken-down local time
@treturn string *format* with place-holders plugged with *tm* values
@see strftime(3)
*/
static int
Pstrftime(lua_State *L)
{
char tmp[256];
const char *fmt = luaL_checkstring(L, 1);
struct tm t;
totm(L, 2, &t);
checknargs(L, 2);
strftime(tmp, sizeof(tmp), fmt, &t);
return pushstringresult(tmp);
}
void dspCheckRegister::sFillList()
{
MetaSQLQuery mql = mqlLoad("checkRegister", "detail");
ParameterList params;
if (!setParams(params))
return;
q = mql.toQuery(params);
_check->populate(q, true);
if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
if(_showDetail->isChecked())
_check->expandAll();
QString tots("SELECT SUM(currToCurr(checkhead_curr_id, bankaccnt_curr_id,"
" checkhead_amount, checkhead_checkdate)) AS amount, "
" currConcat(bankaccnt_curr_id) AS currAbbr "
" FROM bankaccnt LEFT OUTER JOIN"
" checkhead ON ((bankaccnt_id=checkhead_bankaccnt_id)"
" AND (checkhead_checkdate BETWEEN <? value(\"startDate\") ?> AND <? value(\"endDate\") ?> ) "
" AND (NOT checkhead_void)"
" <? if exists(\"check_number\") ?>"
" AND (CAST(checkhead_number AS text) ~ <? value(\"check_number\") ?>)"
" <? endif ?>"
" <? if exists(\"recip\") ?>"
" <? if exists(\"recip_type_v\") ?>"
" AND (checkhead_recip_type = 'V' )"
" <? endif ?>"
" <? if exists(\"recip_type_c\") ?>"
" AND (checkhead_recip_type = 'C' )"
" <? endif ?>"
" <? if exists(\"recip_type_t\") ?>"
" AND (checkhead_recip_type = 'T' )"
" <? endif ?>"
" <? if exists(\"recip_id\") ?>"
" AND (checkhead_recip_id = <? value(\"recip_id\") ?> )"
" <? endif ?>"
" <? endif ?>)"
" LEFT OUTER JOIN "
" checkrecip ON ((checkhead_recip_id=checkrecip_id) "
" AND (checkhead_recip_type=checkrecip_type)) "
" WHERE(bankaccnt_id=<? value(\"bankaccnt_id\") ?>) "
"GROUP BY bankaccnt_curr_id;" );
MetaSQLQuery totm(tots);
q = totm.toQuery(params); // reused from above
if(q.first())
{
_total->setDouble(q.value("amount").toDouble());
_totalCurr->setText(q.value("currAbbr").toString());
}
else if (q.lastError().type() != QSqlError::NoError)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void dspCheckRegister::sFillList()
{
MetaSQLQuery mql = mqlLoad(":/ap/displays/CheckRegister/FillListDetail.mql");
ParameterList params;
if (!setParams(params))
return;
q = mql.toQuery(params);
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
_check->clear();
XTreeWidgetItem *header = NULL;
int checkid = -1;
while (q.next())
{
if (q.value("checkid").toInt() != checkid)
{
checkid = q.value("checkid").toInt();
header = new XTreeWidgetItem( _check, header, checkid, q.value("extra").toInt(),
q.value("f_void"), q.value("f_misc"),
q.value("f_printed"), q.value("f_posted"), q.value("number"),
q.value("description"), q.value("f_checkdate"),
q.value("f_amount"), q.value("currAbbr"));
}
else if (header)
{
XTreeWidgetItem *item = new XTreeWidgetItem( header, checkid, 0);
item->setText(4, q.value("number"));
item->setText(5, q.value("description"));
item->setText(7, q.value("f_amount"));
}
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
if(_showDetail->isChecked())
_check->expandAll();
QString tots("SELECT formatMoney(SUM(currToCurr(checkhead_curr_id,"
" bankaccnt_curr_id,"
" checkhead_amount,"
" checkhead_checkdate))) AS f_amount,"
" currConcat(bankaccnt_curr_id) AS currAbbr "
"FROM checkhead, bankaccnt "
"WHERE ((NOT checkhead_void)"
" AND (checkhead_checkdate BETWEEN <? value(\"startDate\") ?> AND <? value(\"endDate\") ?>) "
" AND (bankaccnt_id=checkhead_bankaccnt_id) "
" AND (checkhead_bankaccnt_id=<? value(\"bankaccnt_id\") ?>)"
" <? if exists(\"check_number\") ?>"
" AND (checkhead_number=<? value(\"check_number\") ?>)"
" <? endif ?>"
" <? if exists(\"recip\") ?>"
" <? if exists(\"recip_type_v\") ?>"
" AND (checkhead_recip_type = 'V' )"
" <? endif ?>"
" <? if exists(\"recip_type_c\") ?>"
" AND (checkhead_recip_type = 'C' )"
" <? endif ?>"
" <? if exists(\"recip_type_t\") ?>"
" AND (checkhead_recip_type = 'T' )"
" <? endif ?>"
" <? if exists(\"recip_id\") ?>"
" AND (checkhead_recip_id = <? value(\"recip_id\") ?> )"
" <? endif ?>"
" <? endif ?>)"
" GROUP BY bankaccnt_curr_id;" );
MetaSQLQuery totm(tots);
q = totm.toQuery(params); // reused from above
if(q.first())
{
_total->setText(q.value("f_amount").toString());
_totalCurr->setText(q.value("currAbbr").toString());
}
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
void dspSummarizedBacklogByWarehouse::sFillList()
{
_so->clear();
XTreeWidgetItem *orderLine = NULL;
ParameterList params;
if (setParams(params))
{
QString sql( "SELECT cohead_id, cohead_holdtype, cohead_number, cust_name,"
" cohead_created, cohead_orderdate, cohead_packdate, pack_head_id,"
" 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,"
" MIN(coitem_scheddate) AS scheddate,"
" SUM((noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) *"
" (currToBase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio) ) AS sales,"
" SUM((noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) * stdcost(item_id) ) AS cost,"
" SUM((noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) *"
" ((currToBase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio) - stdcost(item_id)) ) AS margin,"
" 'curr' AS sales_xtnumericrole,"
" 'curr' AS cost_xtnumericrole,"
" 'curr' AS margin_xtnumericrole,"
" COALESCE(cosmisc_id, -1) AS cosmisc_id, "
" formatShipmentNumber(cosmisc_id) AS cosmisc_number, "
" CASE WHEN (cosmisc_shipped IS NULL) THEN 0"
" WHEN (cosmisc_shipped) THEN 1"
" WHEN (NOT cosmisc_shipped) THEN 2"
" END AS shipstatus,"
" COALESCE(cosmisc_shipvia, '') AS shipvia,"
" CASE WHEN (cosmisc_shipdate IS NULL) THEN ''"
" ELSE formatDate(cosmisc_shipdate)"
" END AS shipdate,"
" ( (COALESCE(cobmisc_cohead_id,0) > 0)"
" AND (SUM(noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) > 0)"
" ) AS overbilled,"
" formatboolyn(CASE WHEN (pack_head_id IS NOT NULL) THEN TRUE"
" ELSE FALSE"
" END) AS packed "
"FROM coitem, itemsite, item, cust, cohead "
" LEFT OUTER JOIN cosmisc ON (cosmisc_cohead_id=cohead_id) "
" LEFT OUTER JOIN (SELECT DISTINCT cobmisc_cohead_id FROM cobmisc) AS cobmisc ON (cobmisc_cohead_id=cohead_id) "
" LEFT OUTER JOIN pack ON (cohead_id = pack_head_id)"
"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 NOT IN ('C','X'))"
" AND (coitem_scheddate BETWEEN <? value(\"startDate\") ?>"
" AND <? value(\"endDate\") ?>)"
"<? if exists(\"warehous_id\") ?>"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
"<? endif ?>"
"<? 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 ?>"
") "
"GROUP BY cohead_id, cohead_number, cust_name,"
" cohead_holdtype, cohead_orderdate, cohead_packdate,"
" cosmisc_shipped, cosmisc_shipvia, cosmisc_shipdate,"
" cosmisc_id, cobmisc_cohead_id, cohead_created, pack_head_id "
" ORDER BY "
"<? if exists(\"orderByShipDate\") ?>scheddate,"
"<? elseif exists(\"orderByPackDate\") ?>cohead_packdate,"
"<? endif ?>"
" cohead_number, cosmisc_shipped;");
MetaSQLQuery mql(sql);
q = mql.toQuery(params);
if (q.first())
{
double totalSales = 0.0;
double totalCost = 0.0;
double totalMargin = 0.0;
int coheadid = -1;
bool unshipped = FALSE;
bool overbilled = FALSE;
do
{
if ( (coheadid != q.value("cohead_id").toInt()) || (!orderLine) )
{
coheadid = q.value("cohead_id").toInt();
unshipped = FALSE;
overbilled = FALSE;
orderLine = new XTreeWidgetItem( _so, orderLine,
q.value("cohead_id").toInt(), -1,
q.value("cohead_number"), q.value("cust_name"),
q.value("f_holdtype"), q.value("cohead_orderdate"),
q.value("scheddate"), q.value("cohead_packdate"),
formatMoney(q.value("sales").toDouble()),
formatMoney(q.value("cost").toDouble()),
formatMoney(q.value("margin").toDouble()),
q.value("cohead_created"), q.value("packed"));
totalSales += q.value("sales").toDouble();
totalCost += q.value("cost").toDouble();
totalMargin += q.value("margin").toDouble();
}
if (q.value("overbilled").toBool())
{
overbilled = TRUE;
orderLine->setTextColor("red");
}
if (q.value("shipstatus").toInt())
{
XTreeWidgetItem *shipLine = new XTreeWidgetItem(orderLine, -1,
q.value("cosmisc_id").toInt());
if (q.value("shipstatus").toInt() == 1)
{
shipLine->setText(0, tr("Yes"));
shipLine->setTextColor("green");
if (!unshipped && !overbilled)
orderLine->setTextColor("green");
}
else if (q.value("shipstatus").toInt() == 2)
{
shipLine->setText(0, tr("No"));
shipLine->setTextColor("blue");
if(!overbilled)
orderLine->setTextColor("blue");
unshipped = TRUE;
}
shipLine->setText(1, q.value("shipvia"));
shipLine->setText(2, q.value("cosmisc_number"));
shipLine->setText(3, q.value("shipdate"));
}
}
while (q.next());
if (_showPrices->isChecked())
new XTreeWidgetItem( _so, orderLine, -1,
"", "", tr("Total Backlog"), "", "", "",
formatMoney(totalSales),
formatCost(totalCost),
formatMoney(totalMargin) );
QString tots = "SELECT COUNT(cohead_id) AS totalorders "
"FROM ( SELECT DISTINCT cohead_id "
" FROM cohead, coitem, itemsite, cust "
" WHERE ( (coitem_cohead_id=cohead_id)"
" AND (coitem_itemsite_id=itemsite_id)"
" AND (cohead_cust_id=cust_id)"
" AND (coitem_status NOT IN ('C','X'))"
" AND (coitem_scheddate BETWEEN <? value(\"startDate\") ?>"
" AND <? value(\"endDate\") ?>)"
"<? if exists(\"warehous_id\") ?>"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
"<? endif ?>"
"<? 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;";
MetaSQLQuery totm(tots);
q = totm.toQuery(params);
if (q.first())
_totalSalesOrders->setText(q.value("totalorders").toString());
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
QString cnts = "SELECT COUNT(coitem.*) AS totalitems "
"FROM cohead, coitem, itemsite, cust "
"WHERE ( (coitem_cohead_id=cohead_id)"
" AND (coitem_itemsite_id=itemsite_id)"
" AND (cohead_cust_id=cust_id)"
" AND (coitem_status NOT IN ('C','X'))"
" AND (coitem_scheddate BETWEEN <? value(\"startDate\") ?>"
" AND <? value(\"endDate\") ?>)"
"<? if exists(\"warehous_id\") ?>"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
"<? endif ?>"
"<? 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 ?>"
");";
MetaSQLQuery cntm(cnts);
q = cntm.toQuery(params);
if (q.first())
_totalLineItems->setText(q.value("totalitems").toString());
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
QString qtys = "SELECT formatQty(SUM(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) AS f_totalqty "
"FROM cohead, coitem, itemsite, item, cust "
"WHERE ( (coitem_cohead_id=cohead_id)"
" AND (coitem_itemsite_id=itemsite_id)"
" AND (itemsite_item_id=item_id)"
" AND (cohead_cust_id=cust_id)"
" AND (coitem_status NOT IN ('C','X'))"
" AND (coitem_scheddate BETWEEN <? value(\"startDate\") ?>"
" AND <? value(\"endDate\") ?>)"
"<? if exists(\"warehous_id\") ?>"
" AND (itemsite_warehous_id=<? value(\"warehous_id\") ?>)"
"<? endif ?>"
"<? 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 ?>"
");";
MetaSQLQuery qtym(qtys);
q = qtym.toQuery(params);
if (q.first())
_totalQty->setText(q.value("f_totalqty").toString());
else if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
}
else
{
_totalSalesOrders->clear();
_totalLineItems->clear();
_totalQty->clear();
}
}
}
void dspCheckRegister::sFillList()
{
XSqlQuery dspFillList;
MetaSQLQuery mql = mqlLoad("checkRegister", "detail");
ParameterList params;
if (!setParams(params))
return;
dspFillList = mql.toQuery(params);
_check->populate(dspFillList, true);
if (ErrorReporter::error(QtCriticalMsg, this, tr("Error Retrieving Check Register Information"),
dspFillList, __FILE__, __LINE__))
{
return;
}
if(_showDetail->isChecked())
_check->expandAll();
QString tots("SELECT SUM(currToCurr(checkhead_curr_id, bankaccnt_curr_id,"
" checkhead_amount, checkhead_checkdate)) AS amount, "
" currConcat(bankaccnt_curr_id) AS currAbbr "
" FROM bankaccnt LEFT OUTER JOIN"
" checkhead ON ((bankaccnt_id=checkhead_bankaccnt_id)"
" AND (checkhead_checkdate BETWEEN <? value(\"startDate\") ?> AND <? value(\"endDate\") ?> ) "
" AND (NOT checkhead_void)"
" <? if exists(\"check_number\") ?>"
" AND (CAST(checkhead_number AS text) ~ <? value(\"check_number\") ?>)"
" <? endif ?>"
" <? if exists(\"recip\") ?>"
" <? if exists(\"recip_type_v\") ?>"
" AND (checkhead_recip_type = 'V' )"
" <? endif ?>"
" <? if exists(\"recip_type_c\") ?>"
" AND (checkhead_recip_type = 'C' )"
" <? endif ?>"
" <? if exists(\"recip_type_t\") ?>"
" AND (checkhead_recip_type = 'T' )"
" <? endif ?>"
" <? if exists(\"recip_id\") ?>"
" AND (checkhead_recip_id = <? value(\"recip_id\") ?> )"
" <? endif ?>"
" <? endif ?>)"
" LEFT OUTER JOIN "
" checkrecip ON ((checkhead_recip_id=checkrecip_id) "
" AND (checkhead_recip_type=checkrecip_type)) "
" WHERE(bankaccnt_id=<? value(\"bankaccnt_id\") ?>) "
"GROUP BY bankaccnt_curr_id;" );
MetaSQLQuery totm(tots);
dspFillList = totm.toQuery(params); // reused from above
if(dspFillList.first())
{
_total->setDouble(dspFillList.value("amount").toDouble());
_totalCurr->setText(dspFillList.value("currAbbr").toString());
}
else if (ErrorReporter::error(QtCriticalMsg, this, tr("Error Retrieving Check Register Information"),
dspFillList, __FILE__, __LINE__))
{
return;
}
}