Calculated Columns.Example - 2

    function getAmountInUsd($amount, $currency, $paymentDate, $connection) {
        if ($currency === 'USD') {
            return '$' . number_format($amount, 2);
        }
        // calculate the currency field name in table usd_exchange_rate
        // example of such field names: eur_rate, aud_rate
        $currencyFieldName = strtolower($currency) . '_rate';
        // Retrieve the usd exchange rate on payment date
        $sql = sprintf("SELECT %s FROM usd_exchange_rate WHERE exchange_date = '%s'", 
            $currencyFieldName, $paymentDate);
        $currencyRate = $connection->ExecScalarSQL($sql);
        if ($currencyRate) {
            return '$' . number_format(($amount / $currencyRate), 2);
        } else {
            return 'There is no USD exchange rate data on a payment date';
        }
    }

        protected function doCalculateFields($rowData, $fieldName, &$value)
        {
            if ($fieldName == 'amount_in_usd') {
                $value = getAmountInUsd($rowData['amount'], $rowData['currency'], $rowData['payment_date'], $this->GetConnection());
            }
        }

// OnCalculateControlValues event body
 if ((editors['amount'].getValue() >= 0) && editors['currency'].getValue() && editors['payment_date'].getValue()) {
                $.ajax({
                    url: window.location.href,
                    data: {
                        amount: editors['amount'].getValue(),
                        currency: editors['currency'].getValue(), 
                        paymentDate: editors['payment_date'].getValue()
                    },
                    success: function (result) {
                        editors['amount_in_usd'].setValue(result);                
                    },
                    error: function () {
                        editors['amount_in_usd'].setValue('');        
                    }
                });                   
            } else {
                editors['amount_in_usd'].setValue('');
            }

        protected function DoPrepare() {
            // handling Ajax request
            if (GetApplication()->isGetValueSet('amount') && 
                    GetApplication()->isGetValueSet('currency') && 
                        GetApplication()->isGetValueSet('paymentDate')) {
                $value = 
                    getAmountInUsd(
                        GetApplication()->GetGETValue('amount'),        
                        GetApplication()->GetGETValue('currency'),
                        GetApplication()->GetGETValue('paymentDate'),
                        $this->GetConnection()
                    );                    
                echo $value;
                exit;
            }
            
            // auxiliary code
            $this->setDescription(file_get_contents("external_data/doc/calculated_columns2.html"));
            
            $this->setDetailedDescription(
                '<div id="on-before-page-execute" class="event-code">' .
                    extractFunctionCode('getAmountInUsd') . '</div>' .
                '<div id="on-get-calculated-field-value" class="event-code">' .
                    extractMethodCode($this, 'doCalculateFields') . '</div>' .
                '<div id="on-get-form-editor-calculated-value" class="event-code">' .
                    extractClientEventCode($this, 'OnCalculateControlValues') . '</div>' .
                '<div id="on-prepare-page" class="event-code">' .
                    extractMethodCode($this, 'doPrepare') . '</div>'
            );
        }