script based price conversion

- in /modules/cronjobs add:
rec_price.php
<?php

$page_security = 'SA_SALESPRICE';
$path_to_root = "../..";

define("_CJ", 1); // needed in hooks.inc in order to skip authentication
define("_CJ_USER", "cron");
define("_CJ_COY", '1');

$_POST["company_login_name"] = _CJ_COY;

include_once($path_to_root . "/includes/session.inc");

include_once($path_to_root . "/sales/includes/sales_db.inc");
include_once($path_to_root . "/sales/includes/db/sales_types_db.inc");
include_once($path_to_root . "/inventory/includes/inventory_db.inc");

//required for working w/ currencies
include_once($path_to_root . "/gl/includes/db/gl_db_currencies.inc");
include_once($path_to_root . "/gl/includes/db/gl_db_rates.inc");

function get_last_exchange_rate($curr_abrev) {
    $sql = "SELECT rate_buy FROM "
        .TB_PREF."exchange_rates "
        ."WHERE curr_code=".db_escape($curr_abrev)." ORDER BY date_ DESC limit 1";

    $result = db_query($sql, "could not get last exchange rate for $curr_abrev ");

    if(db_num_rows($result) == 0)
    return 0;

    $row = db_fetch($result);
    return $row[0];
}

// get a list of all currencies and their latest exchange rates
$curr_from = array();
$curr_base = '';

$cs = get_currencies();
while($c = db_fetch($cs)) {
   if (is_company_currency($c['curr_abrev'])) {
    //is base currency
    $curr_base = $c['curr_abrev'];
//    echo "base = $curr_base\n";
   } else {
    // get the latest exchange rate for this currency
    $r = get_last_exchange_rate($c['curr_abrev']);
    if ($r == 0) {
        echo 'There is no exchange rate for currency: ' . $c['curr_abrev'];
        exit();
    } else {
        $curr_from[$c['curr_abrev']] = $r;
        echo "added " . $c['curr_abrev'] . "\n";
    }
   }
}

// get all the items
$items = get_items();

while ($item = db_fetch($items)) {
    echo "------------------------------------------------------------------------------------\n";
    echo "processing art code=" . $item['stock_id'] . " name=" . $item['description'] . "\n";
   
    // get prices for channels
    $prices = array();
    $prices_list = get_prices($item['stock_id']);
    while ($myrow = db_fetch($prices_list)) {
    echo "adding price for channel id=" . $myrow['sales_type_id'] . " currency=" . $myrow['curr_abrev'] . "\n";
    $prices[$myrow['sales_type_id']][$myrow['curr_abrev']] = array('price_id'=>$myrow['id'], 'price'=>$myrow['price']);
    }

    //foreach sales type (aka channel) perform price count check
    foreach($prices as $sales_type_id=>$price_per_sale_type) {
        echo "checking pricing for channel id=$sales_type_id\n";
    //check currency prices usage (count)
    $count = 0;
    foreach($curr_from as $c=>$r) {
        if (isset($price_per_sale_type[$c]))
            $count++;
    }
    echo "currency usage count =$count\n";

    if ($count == 0) {
            echo "this article has no defined price for any of the foreign currencies in this channel. no processing\n";
            continue;
    }
    if ($count > 1) {
            echo "this article has prices defined for more than one foreign currency in this channel. no processing\n";
            continue;
    }
       
        echo "creating or updating price for base currency $curr_base\n";
    //determine currency for this price
    $price_c = '';
    foreach($curr_from as $c=>$r) {
            if (isset($price_per_sale_type[$c]))
            $price_c = $c;
    }
    echo "price is expressed in $price_c\n";
        $price_in_base_curr = $price_per_sale_type[$price_c]['price'] * $curr_from[$price_c];
       
       

//    print_r($price_per_sale_type);
   
        if (isset($price_per_sale_type[$curr_base])) {
            echo "price id is" . $price_per_sale_type[$curr_base]['price_id'] . "\n";
        echo "This article has a price in $curr_base. update it. price=$price_in_base_curr\n";
        update_item_price($price_per_sale_type[$curr_base]['price_id'], $sales_type_id, $curr_base, $price_in_base_curr);
        } else {
            echo "This article does not have a price in $curr_base. create it. price=$price_in_base_curr\n";
        add_item_price($item['stock_id'], $sales_type_id, $curr_base, $price_in_base_curr);
        }
    }
    echo "------------------------------------------------------------------------------------\n";

}
?>

setup
- create a user (eg cron) and assign the proper rights for operation
- change the user in define('_CJ_USER',.......
- run the script via php-cli (php rec_price.php)

Notes
- this is very very alpha code - use it with care
- most probably you do need such price updates, so use these files as skeletons for developing your own command line scripts

I may be missing something, but aren't there hooks already for maintaining the current Exchange Rate from your base currency (EUR) and others (like USD)

tom