void dspInvoiceRegister::sFillList()
{
_gltrans->clear();
QString sql( "SELECT gltrans_id, formatDate(gltrans_date) AS f_date, gltrans_source,"
" CASE WHEN(gltrans_doctype='IN') THEN 1"
" WHEN(gltrans_doctype='CM') THEN 2"
" WHEN(gltrans_doctype='DM') THEN 3"
" WHEN(gltrans_doctype='CD') THEN 4"
" ELSE -1"
" END AS altId,"
" CASE WHEN(gltrans_doctype='IN') THEN :invoice"
" WHEN(gltrans_doctype='CM') THEN :creditmemo"
" WHEN(gltrans_doctype='DM') THEN :debitmemo"
" WHEN(gltrans_doctype='CD') THEN :cashdeposit"
" ELSE gltrans_doctype"
" END AS doctype,"
" gltrans_docnumber,"
" CASE WHEN(gltrans_doctype='IN') THEN"
" (SELECT invchead_shipto_name"
" FROM aropen LEFT OUTER JOIN"
" invchead"
" ON (invchead_id=aropen_cobmisc_id"
" AND invchead_cust_id=aropen_cust_id)"
" WHERE ((aropen_docnumber=gltrans_docnumber)"
" AND (aropen_doctype='I')))"
" ELSE firstLine(gltrans_notes)"
" END AS f_notes,"
" (formatGLAccount(accnt_id) || ' - ' || accnt_descrip) AS f_accnt,"
" CASE WHEN (gltrans_amount < 0) THEN formatMoney(ABS(gltrans_amount))"
" ELSE ''"
" END AS f_debit,"
" CASE WHEN (gltrans_amount < 0) THEN ABS(gltrans_amount)"
" ELSE 0"
" END AS debit,"
" CASE WHEN (gltrans_amount > 0) THEN formatMoney(gltrans_amount)"
" ELSE ''"
" END AS f_credit,"
" CASE WHEN (gltrans_amount > 0) THEN gltrans_amount"
" ELSE 0"
" END AS credit "
"FROM gltrans, accnt "
"WHERE ((gltrans_accnt_id=accnt_id)"
" AND (gltrans_doctype IN ('IN', 'CM', 'DM', 'CD'))"
" AND (gltrans_source = 'A/R')"
" AND (gltrans_date BETWEEN :startDate AND :endDate)" );
if (_selectedAccount->isChecked())
sql += " AND (gltrans_accnt_id=:accnt_id)";
sql += ") "
"ORDER BY gltrans_date, gltrans_docnumber;";
q.prepare(sql);
_dates->bindValue(q);
q.bindValue(":accnt_id", _account->id());
q.bindValue(":invoice", tr("Invoice"));
q.bindValue(":creditmemo", tr("Credit Memo"));
q.bindValue(":debitmemo", tr("Debit Memo"));
q.bindValue(":cashdeposit", tr("Customer Deposit"));
q.exec();
XTreeWidgetItem * parent = 0;
XTreeWidgetItem * last = 0;
QString date;
double debit = 0.0, credit = 0.0;
double totdebit = 0.0, totcredit = 0.0;
while(q.next())
{
if(0 == parent || date != q.value("f_date").toString())
{
if(parent)
{
last = new XTreeWidgetItem(parent, last, -2, -2, QVariant(""), "", "", tr("Subtotal"), "", "", formatMoney(debit), formatMoney(credit));
parent->setExpanded(TRUE);
}
date = q.value("f_date").toString();
parent = new XTreeWidgetItem(_gltrans, parent, -1, -2, QVariant(date));
last = 0;
debit = 0.0;
credit = 0.0;
}
last = new XTreeWidgetItem(parent, last, q.value("gltrans_id").toInt(),
q.value("altId").toInt(),
QVariant(""), q.value("gltrans_source"), q.value("doctype"),
q.value("gltrans_docnumber"), q.value("f_notes"), q.value("f_accnt"),
q.value("f_debit"), q.value("f_credit"));
debit += q.value("debit").toDouble();
totdebit += q.value("debit").toDouble();
credit += q.value("credit").toDouble();
totcredit += q.value("credit").toDouble();
}
if (q.lastError().type() != QSqlError::None)
{
systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
return;
}
if(parent)
{
last = new XTreeWidgetItem(parent, last, -2, -2, QVariant(""), "", "", tr("Subtotal"), "", "", formatMoney(debit), formatMoney(credit));
parent->setExpanded(TRUE);
}
last = new XTreeWidgetItem(_gltrans, parent, -3, -2, QVariant(""), "", "", tr("Total"), "", "", formatMoney(totdebit), formatMoney(totcredit));
}
