void dspCostedSingleLevelBOM::sFillList()
{
  ParameterList params;
  if (!setParams(params))
    return;
  
  dspCostedBOMBase::sFillList();

  XSqlQuery qq;
  qq.prepare( "SELECT formatCost(actcost(:item_id)) AS actual,"
              "       formatCost(stdcost(:item_id)) AS standard;" );
  qq.bindValue(":item_id", _item->id());
  qq.exec();
  if (qq.first())
  {
    XTreeWidgetItem *last = new XTreeWidgetItem(list(), -1, -1, tr("Actual Cost"), "");
    last->setText(11, qq.value("actual").toString());

    last = new XTreeWidgetItem(list(), last, -1, -1, tr("Standard Cost"), "" );
    last->setText(11, qq.value("standard").toString());
  }
  else if (ErrorReporter::error(QtCriticalMsg, this, tr("Error Retrieving BOM Cost Information"),
                                qq, __FILE__, __LINE__))
  {
    return;
  }
}

void reconcileBankaccount::sChecksToggleCleared()
{
  XTreeWidgetItem *item = (XTreeWidgetItem*)_checks->currentItem();

  if(0 == item)
    return;

  _checks->scrollToItem(item);

  q.prepare("SELECT toggleBankrecCleared(:bankrecid, :source, :sourceid) AS cleared");
  q.bindValue(":bankrecid", _bankrecid);
  q.bindValue(":sourceid", item->id());
  if(item->altId()==1)
    q.bindValue(":source", "GL");
  else if(item->altId()==2)
    q.bindValue(":source", "AD");
  q.exec();
  if(q.first())
    item->setText(0, (q.value("cleared").toBool() ? tr("Yes") : tr("No") ));
  else
  {
    populate();
    if (q.lastError().type() != QSqlError::NoError)
    {
      systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
      return;
    }
  }
}

void printChecksReview::markSelected( int actionId )
{
  QString action;
  switch(actionId)
  {
    case ActionPrinted:
      action = tr("Printed");
      break;
    case ActionVoided:
      action = tr("Voided");
      break;
    case ActionReplaced:
      action = tr("Replace");
      break;
    case ActionUnmark:
    default:
      actionId = -1;
      action = "";
  };

  QList<XTreeWidgetItem*> selected = _checks->selectedItems();
  for (int i = 0; i < selected.size(); i++)
  {
    XTreeWidgetItem *cursor = (XTreeWidgetItem*)selected[i];
    cursor->setText(1, action);
    cursor->setAltId(actionId);
  }
}

void reconcileBankaccount::sChecksToggleCleared()
{
  XSqlQuery reconcileChecksToggleCleared;
  XTreeWidgetItem *item = (XTreeWidgetItem*)_checks->currentItem();

  if(0 == item)
    return;

  _checks->scrollToItem(item);

  double rate = item->rawValue("doc_exchrate").toDouble();
  double baseamount = item->rawValue("base_amount").toDouble();
  double amount = item->rawValue("amount").toDouble();
  
  if (_allowEdit->isChecked() && item->text(0) != tr("Yes"))
  {
    ParameterList params;
    params.append("transtype", "check");
    params.append("bankaccntid", _bankaccnt->id());
    params.append("bankrecid", _bankrecid);
    params.append("sourceid", item->id());
    if(item->altId()==1)
      params.append("source", "GL");
    else if(item->altId()==2)
      params.append("source", "SL");
    else if(item->altId()==3)
      params.append("source", "AD");
    toggleBankrecCleared newdlg(this, "", TRUE);
    newdlg.set(params);
    newdlg.exec();
    populate();
  }
  else
  {
    reconcileChecksToggleCleared.prepare("SELECT toggleBankrecCleared(:bankrecid, :source, :sourceid, :currrate, :amount) AS cleared");
    reconcileChecksToggleCleared.bindValue(":bankrecid", _bankrecid);
    reconcileChecksToggleCleared.bindValue(":sourceid", item->id());
    if(item->altId()==1)
      reconcileChecksToggleCleared.bindValue(":source", "GL");
    else if(item->altId()==2)
      reconcileChecksToggleCleared.bindValue(":source", "SL");
    else if(item->altId()==3)
      reconcileChecksToggleCleared.bindValue(":source", "AD");
    reconcileChecksToggleCleared.bindValue(":currrate", rate);
    reconcileChecksToggleCleared.bindValue(":amount", amount);
    reconcileChecksToggleCleared.exec();
    if(reconcileChecksToggleCleared.first())
      item->setText(0, (reconcileChecksToggleCleared.value("cleared").toBool() ? tr("Yes") : tr("No") ));
    else
    {
      populate();
      if (reconcileChecksToggleCleared.lastError().type() != QSqlError::NoError)
      {
        systemError(this, reconcileChecksToggleCleared.lastError().databaseText(), __FILE__, __LINE__);
        return;
      }
    }
  }
}

void dspBriefSalesHistoryByCustomer::sFillList()
{
  _sohist->clear();

  if (!checkParameters())
    return;

  QString sql( "SELECT cohist_ordernumber, cohist_ponumber, cohist_invcnumber,"
               "       formatDate(cohist_orderdate) AS f_orderdate,"
               "       formatDate(cohist_invcdate, 'Return') AS f_invcdate,"
               "       SUM(round(cohist_qtyshipped * cohist_unitprice,2)) AS extended,"
               "       formatMoney(SUM(round(cohist_qtyshipped * cohist_unitprice,2))) AS f_extended "
               "FROM cohist, itemsite, item "
               "WHERE ( (cohist_itemsite_id=itemsite_id)"
               " AND (itemsite_item_id=item_id)"
               " AND (cohist_invcdate BETWEEN :startDate AND :endDate)"
               " AND (cohist_cust_id=:cust_id)" );

  if (_warehouse->isSelected())
    sql += " AND (itemsite_warehous_id=:warehous_id)";

  if (_productCategory->isSelected())
    sql += " AND (item_prodcat_id=:prodcat_id)";
  else if (_productCategory->isPattern())
    sql += " AND (item_prodcat_id IN (SELECT prodcat_id FROM prodcat WHERE (prodcat_code ~ :prodcat_pattern)))";

  sql += ") "
         "GROUP BY cohist_ordernumber, cohist_ponumber, cohist_invcnumber,"
         "         cohist_orderdate, cohist_invcdate "
         "ORDER BY cohist_invcdate, cohist_orderdate;";

  q.prepare(sql);
  _dates->bindValue(q);
  q.bindValue(":cust_id", _cust->id());
  _warehouse->bindValue(q);
  _productCategory->bindValue(q);
  q.exec();
  if (q.first())
  {
    double        totalSales = 0.0;

    XTreeWidgetItem *last = 0;
    do
    {
      last = new XTreeWidgetItem( _sohist, last, -1, 
			       q.value("cohist_ordernumber"), q.value("cohist_ponumber"),
			       q.value("cohist_invcnumber"), q.value("f_orderdate"),
			       q.value("f_invcdate"), q.value("f_extended") );

      totalSales += q.value("extended").toDouble();
    }
    while (q.next());

    XTreeWidgetItem *totals = new XTreeWidgetItem(_sohist, last, -1, QVariant(tr("Totals")));
    totals->setText(5, formatMoney(totalSales));
  }
}

void printShippingForm::sEditShippingFormWatermark()
{
  QList<QTreeWidgetItem*>selected = _shipformWatermarks->selectedItems();
  for (int counter = 0; counter < selected.size(); counter++)
  {
    XTreeWidgetItem *cursor = static_cast<XTreeWidgetItem*>(selected[counter]);
    ParameterList params;
    params.append("watermark", cursor->text(1));
    params.append("showPrices", (cursor->text(2) == tr("Yes")));

    editICMWatermark newdlg(this, "", TRUE);
    newdlg.set(params);
    if (newdlg.exec() == XDialog::Accepted)
    {
      cursor->setText(1, newdlg.watermark());
      cursor->setText(2, ((newdlg.showPrices()) ? tr("Yes") : tr("No")));
    }
  }
}

void dspFinancialReport::sEditPeriodLabel()
{
  XTreeWidgetItem * item = (XTreeWidgetItem*)_periods->currentItem();
  if(!item)
    return;

  bool ok;
  QString text = QInputDialog::getText( tr("Alternate Label"),
        tr("Enter an alternate label for the period %1:").arg(item->text(0)),
        QLineEdit::Normal, item->text(1), &ok, this );
  if(ok)
    item->setText(1, text);
}

void dspCostedSingleLevelBOM::sFillList()
{
  dspCostedBOMBase::sFillList();

  XSqlQuery qq;
  qq.prepare( "SELECT formatCost(actcost(:item_id)) AS actual,"
              "       formatCost(stdcost(:item_id)) AS standard;" );
  qq.bindValue(":item_id", _item->id());
  qq.exec();
  if (qq.first())
  {
    XTreeWidgetItem *last = new XTreeWidgetItem(list(), -1, -1, tr("Actual Cost"), "");
    last->setText(11, qq.value("actual").toString());

    last = new XTreeWidgetItem(list(), last, -1, -1, tr("Standard Cost"), "" );
    last->setText(11, qq.value("standard").toString());
  }
  else if (qq.lastError().type() != QSqlError::NoError)
  {
    systemError(this, qq.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }
}

void dspCostedIndentedBOM::sFillList()
{
  dspCostedBOMBase::sFillList();

  XSqlQuery qq;
  qq.prepare( "SELECT formatCost(SUM(bomdata_actextendedcost)) AS actextendedcost,"
             "       formatCost(SUM(bomdata_stdextendedcost)) AS stdextendedcost,"
             "       formatCost(actcost(:item_id)) AS actual,"
             "       formatCost(stdcost(:item_id)) AS standard "
             "FROM indentedbom(:item_id,"
             "                 :revision_id,0,0)"
             "WHERE (bomdata_bomwork_level=1) "
             "GROUP BY actual, standard;" );
  qq.bindValue(":item_id", _item->id());
  qq.bindValue(":revision_id",_revision->id());
  qq.exec();
  if (qq.first())
  {
    XTreeWidgetItem *last = new XTreeWidgetItem(list(), -1, -1);
    last->setText(1, tr("Total Cost"));
    if(_useStandardCosts->isChecked())
      last->setText(11, qq.value("stdextendedcost").toString());
    else
      last->setText(11, qq.value("actextendedcost").toString());

    last = new XTreeWidgetItem( list(), -1, -1);
    last->setText(1, tr("Actual Cost"));
    last->setText(11, qq.value("actual").toString());

    last = new XTreeWidgetItem( list(), -1, -1);
    last->setText(1, tr("Standard Cost"));
    last->setText(11, qq.value("standard").toString());
  }
  else if (qq.lastError().type() != QSqlError::NoError)
  {
    systemError(this, qq.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }
  list()->expandAll();
}

void dspTimePhasedOpenAPItems::sFillCustom()
{
  if (!_periods->isPeriodSelected())
  {
    if (isVisible())
      QMessageBox::warning( this, tr("Select Calendar Periods"),
                            tr("Please select one or more Calendar Periods") );
    return;
  }

  _columnDates.clear();
  _apopen->clear();
  _apopen->setColumnCount(2);

  QString sql("SELECT vend_id, vend_number, vend_name");

  int columns = 1;
  QList<QTreeWidgetItem*> selected = _periods->selectedItems();
  for (int i = 0; i < selected.size(); i++)
  {
    PeriodListViewItem *cursor = (PeriodListViewItem*)selected[i];
    sql += QString(", openAPItemsValue(vend_id, %2) AS bucket%1")
          .arg(columns++)
          .arg(cursor->id());

    _apopen->addColumn(formatDate(cursor->startDate()), _bigMoneyColumn, Qt::AlignRight);
    _columnDates.append(DatePair(cursor->startDate(), cursor->endDate()));
  }

  sql += " FROM vend ";

  if (_selectedVendor->isChecked())
    sql += "WHERE (vend_id=:vend_id)";
  else if (_selectedVendorType->isChecked())
    sql += "WHERE (vend_vendtype_id=:vendtype_id)";
  else if (_vendorTypePattern->isChecked())
    sql += "WHERE (vend_vendtype_id IN (SELECT vendtype_id FROM vendtype WHERE (vendtype_code ~ :vendtype_code))) ";

  sql += "ORDER BY vend_number;";

  q.prepare(sql);
  q.bindValue(":vend_id", _vend->id());
  q.bindValue(":vendtype_id", _vendorTypes->id());
  q.bindValue(":vendtype_code", _vendorType->text().upper());
  q.exec();
  if (q.first())
  {
    Q3ValueVector<Numeric> totals(columns);;
    XTreeWidgetItem *last = 0;

    do
    {
      double lineTotal = 0.0;

      last = new XTreeWidgetItem( _apopen, last, q.value("vend_id").toInt(),
                                 q.value("vend_number"), q.value("vend_name") );

      for (int column = 1; column < columns; column++)
      {
        QString bucketName = QString("bucket%1").arg(column);
        last->setText((column + 1), formatMoney(q.value(bucketName).toDouble()));
        totals[column] += q.value(bucketName).toDouble();
        lineTotal += q.value(bucketName).toDouble();
      }

      if (lineTotal == 0.0)
      {
        delete last;
        last = _apopen->topLevelItem(_apopen->topLevelItemCount() - 1);
      }
    }
    while (q.next());

//  Add the totals row
    XTreeWidgetItem *total = new XTreeWidgetItem(_apopen, last, -1, QVariant(tr("Totals:")));
    for (int column = 1; column < columns; column++)
      total->setText((column + 1), formatMoney(totals[column].toDouble()));
  }
}

void voucherItem::sToggleReceiving(QTreeWidgetItem *pItem)
{
  double n;
  QString s;
  XTreeWidgetItem* item = (XTreeWidgetItem*)pItem;
  if(item->id() == -1)
    return;
  if (item->text(3) == "Yes")
  {
    item->setText(3, "No");
    if (item->text(0) == "Receiving")
    {
    	n = _qtyToVoucher->toDouble();
    	_qtyToVoucher->setText(item->text(2));
    	n = n - _qtyToVoucher->toDouble();
    	_qtyToVoucher->setText(s.setNum(n));

      n = _uninvoicedReceived->toDouble();
      _uninvoicedReceived->setText(item->text(2));
      n = n + _uninvoicedReceived->toDouble();
      _uninvoicedReceived->setText(s.setNum(n));
    }
    else
    {
    	n = _qtyToVoucher->toDouble();
    	_qtyToVoucher->setText(item->text(2));
    	n = n - _qtyToVoucher->toDouble();
    	_qtyToVoucher->setText(s.setNum(n));

      n = _uninvoicedRejected->toDouble();
      _uninvoicedRejected->setText(item->text(2));
      n = n + _rejected->toDouble();
      _uninvoicedRejected->setText(s.setNum(n));
    }
  }
  else 
  {
    item->setText(3, "Yes");
    if (item->text(0) == "Receiving")
    {
    	n = _qtyToVoucher->toDouble();
    	_qtyToVoucher->setText(item->text(2));
    	n = n + _qtyToVoucher->toDouble();
    	_qtyToVoucher->setText(s.setNum(n));

      n = _uninvoicedReceived->toDouble();
      _uninvoicedReceived->setText(item->text(2));
      n = n - _uninvoicedReceived->toDouble();
      _uninvoicedReceived->setText(s.setNum(n));
    }
    else
    {
      n = _qtyToVoucher->toDouble();
      _qtyToVoucher->setText(item->text(2));
      n = n + _qtyToVoucher->toDouble();
      _qtyToVoucher->setText(s.setNum(n));

      n = _uninvoicedRejected->toDouble();
      _uninvoicedRejected->setText(item->text(2));
      n = n + _uninvoicedRejected->toDouble();
      _uninvoicedRejected->setText(s.setNum(n));
    }
  }

//Check PO Close flag

  if ( ((_ordered->toDouble() <= (_received->toDouble() - _rejected->toDouble()))) && (_uninvoicedReceived->toDouble() == 0) && (_uninvoicedRejected->toDouble() == 0) )
        _closePoitem->setChecked(true);
  else
	_closePoitem->setChecked(false);
  
//  Save the voitem information
  if (_voitemid != -1)
  {
    q.prepare( "UPDATE voitem "
               "SET voitem_qty=:voitem_qty "
               "WHERE (voitem_id=:voitem_id);" );
    q.bindValue(":voitem_id", _voitemid);
  }
  else
  {
//  Get next voitem id
    q.prepare("SELECT NEXTVAL('voitem_voitem_id_seq') AS voitemid");
    q.exec();
    if (q.first())
      _voitemid = (q.value("voitemid").toInt());
    else if (q.lastError().type() != QSqlError::NoError)
    {
      systemError(this, _rejectedMsg.arg(q.lastError().databaseText()),
                  __FILE__, __LINE__);
      reject();
      return;
    }
    
    q.prepare( "INSERT INTO voitem "
               "(voitem_id, voitem_vohead_id, voitem_poitem_id, voitem_close, voitem_qty, voitem_freight) "
               "VALUES "
               "(:voitem_id, :vohead_id, :poitem_id, :voitem_close, :voitem_qty, :voitem_freight);" );
  }

//.........这里部分代码省略.........

void reconcileBankaccount::sReceiptsToggleCleared()
{
  XTreeWidgetItem *item = (XTreeWidgetItem*)_receipts->currentItem();
  XTreeWidgetItem *child = 0;
  bool setto = true;

  if(0 == item)
    return;

  _receipts->scrollToItem(item);
  if(item->altId() == 3)
  {
    setto = item->text(0) == tr("No");
    for (int i = 0; i < item->childCount(); i++)
    {
      child = item->child(i);
      if(child->text(0) != (setto ? tr("Yes") : tr("No")))
      {
        q.prepare("SELECT toggleBankrecCleared(:bankrecid, :source, :sourceid) AS cleared");
        q.bindValue(":bankrecid", _bankrecid);
        q.bindValue(":sourceid", child->id());
        if(child->altId()==1)
          q.bindValue(":source", "GL");
        else if(child->altId()==2)
          q.bindValue(":source", "AD");
        q.exec();
        if(q.first())
          child->setText(0, (q.value("cleared").toBool() ? tr("Yes") : tr("No") ));
	else if (q.lastError().type() != QSqlError::NoError)
	{
	  systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
	  return;
	}
      }
    }
    item->setText(0, (setto ? tr("Yes") : tr("No")));
  }
  else
  {
    q.prepare("SELECT toggleBankrecCleared(:bankrecid, :source, :sourceid) AS cleared");
    q.bindValue(":bankrecid", _bankrecid);
    q.bindValue(":sourceid", item->id());
    if(item->altId()==1)
      q.bindValue(":source", "GL");
    else if(item->altId()==2)
      q.bindValue(":source", "AD");
    q.exec();
    if(q.first())
    {
      item->setText(0, (q.value("cleared").toBool() ? tr("Yes") : tr("No") ));

      item = (XTreeWidgetItem*)item->QTreeWidgetItem::parent();
      if(item != 0 && item->altId() == 3)
      {
        setto = true;
	for (int i = 0; i < item->childCount(); i++)
        {
          setto = (setto && (item->child(i)->text(0) == tr("Yes")));
        }
        item->setText(0, (setto ? tr("Yes") : tr("No")));
      }
    }
    else
    {
      populate();
      if (q.lastError().type() != QSqlError::NoError)
      {
	systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
	return;
      }
    }
  }
}

/* 
   Note that the SELECTs here are UNIONs of the gltrans table (in the base
   currency) and the bankadj table (in the bank account's currency).
*/
void reconcileBankaccount::populate()
{
  qApp->setOverrideCursor(QCursor(Qt::WaitCursor));

  double begBal = _openBal->localValue();
  double endBal = _endBal->localValue();

  int currid = -1;

  // fill receipts list
  currid = _receipts->id();
  _receipts->clear();
  q.prepare("SELECT gltrans_id AS id, 1 AS altid,"
            "       jrnluse_use AS use, gltrans_journalnumber AS jrnlnum,"
            "       COALESCE(date(jrnluse_date), gltrans_date) AS f_jrnldate,"
            "       COALESCE(bankrecitem_cleared, FALSE) AS cleared,"
            "       gltrans_date AS f_date,"
            "       gltrans_docnumber AS docnumber,"
            "       gltrans_notes AS notes,"
            "       currToLocal(bankaccnt_curr_id, gltrans_amount, gltrans_date) * -1 AS amount,"
            "       COALESCE(date(jrnluse_date), gltrans_date) AS jrnldate,"
            "       gltrans_date AS sortdate "
            "  FROM (bankaccnt CROSS JOIN gltrans) LEFT OUTER JOIN bankrecitem "
            "    ON ((bankrecitem_source='GL') AND (bankrecitem_source_id=gltrans_id)"
            "        AND (bankrecitem_bankrec_id=:bankrecid)) "
            "       LEFT OUTER JOIN jrnluse ON (jrnluse_number=gltrans_journalnumber AND jrnluse_use='C/R')"
            " WHERE ((gltrans_accnt_id=bankaccnt_accnt_id)"
            "   AND (NOT gltrans_deleted) "
            "   AND (NOT gltrans_rec)"
            "   AND (gltrans_amount < 0)"
            "   AND (bankaccnt_id=:bankaccntid) ) "
            " UNION ALL "
            "SELECT bankadj_id AS id, 2 AS altid,"
            "       '' AS use, NULL AS jrnlnum, bankadj_date AS f_jrnldate,"
            "       COALESCE(bankrecitem_cleared, FALSE) AS cleared,"
            "       bankadj_date AS f_date,"
            "       bankadj_docnumber AS docnumber,"
            "       bankadjtype_name AS notes,"
            "       (CASE WHEN(bankadjtype_iscredit=true) THEN (bankadj_amount * -1) ELSE bankadj_amount END) AS amount,"
            "       bankadj_date AS jrnldate,"
            "       bankadj_date AS sortdate "
            "  FROM (bankadjtype CROSS JOIN bankadj) "
            "               LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='AD') "
            "                 AND (bankrecitem_source_id=bankadj_id) "
            "                 AND (bankrecitem_bankrec_id=:bankrecid)) "
            " WHERE ( (((bankadjtype_iscredit=false) AND (bankadj_amount > 0)) OR ((bankadjtype_iscredit=true) AND (bankadj_amount < 0))) "
            "   AND (bankadj_bankadjtype_id=bankadjtype_id) "
            "   AND (NOT bankadj_posted) "
            "   AND (bankadj_bankaccnt_id=:bankaccntid) ) "
            "ORDER BY jrnldate, jrnlnum, sortdate; ");
  q.bindValue(":bankaccntid", _bankaccnt->id());
  q.bindValue(":bankrecid", _bankrecid);
  q.exec();
  if (q.lastError().type() != QSqlError::NoError)
  {
    systemError(this, q.lastError().databaseText(), __FILE__, __LINE__);
    return;
  }

  int jrnlnum = 0;
  XTreeWidgetItem * parent = 0;
  XTreeWidgetItem * lastChild = 0;
  XTreeWidgetItem * last = 0;
  bool cleared = TRUE;
  double amount = 0.0;
  bool amountNull = true;
  while (q.next())
  {
    if(q.value("use").toString() == "C/R")
    {
      if(q.value("jrnlnum").toInt() != jrnlnum || (0 == parent))
      {
        if(parent != 0)
        {
          parent->setText(0, (cleared ? tr("Yes") : tr("No")));
          parent->setText(4, amountNull ? tr("?????") : formatMoney(amount));
        }
        jrnlnum = q.value("jrnlnum").toInt();
        last = new XTreeWidgetItem( _receipts, last,
          jrnlnum, 3, "", formatDate(q.value("f_jrnldate").toDate()), q.value("jrnlnum"));
        parent = last;
        cleared = true;
        amount = 0.0;
	amountNull = true;
        lastChild = 0;
      }
      cleared = (cleared && q.value("cleared").toBool());
      amount += q.value("amount").toDouble();
      amountNull = q.value("amount").isNull();
      
      lastChild = new XTreeWidgetItem( parent, lastChild,
        q.value("id").toInt(), q.value("altid").toInt(),
        (q.value("cleared").toBool() ? tr("Yes") : tr("No")),
        formatDate(q.value("f_date").toDate()), q.value("docnumber"),
        q.value("notes"),
	q.value("amount").isNull() ? tr("?????") : formatMoney(q.value("amount").toDouble()) );
//.........这里部分代码省略.........