.
***********************************************************************/
class fa2_1 extends fa_patch {
var $previous = ''; // applicable database version
var $version = '2.1'; // version installed
var $description;
var $sql = 'alter2.1.sql';
function __construct() {
$this->description = _('Upgrade from version 2.0 to 2.1');
}
//
// Install procedure. All additional changes
// not included in sql file should go here.
//
function install($company, $force=false)
{
global $db;
/*
Statement below is allowed only for MySQL >=4.0.4:
UPDATE `0_bank_trans`, `0_bank_accounts`
SET 0_bank_trans.bank_act=0_bank_accounts.id
WHERE 0_bank_trans.bank_act=0_bank_accounts.account_code;
*/
$sql = "SELECT id, account_code FROM ".TB_PREF."bank_accounts";
if(!($res = db_query($sql))) {
display_error(_("Cannot retrieve bank accounts codes")
.':
'. db_error_msg($db));
return false;
}
while ($acc = db_fetch($res)) {
$sql = "UPDATE ".TB_PREF."bank_trans SET bank_act='"
.$acc['id']."' WHERE bank_act=".$acc['account_code'];
if (db_query($sql)==false) {
display_error(_("Cannot update bank transactions")
.':
'. db_error_msg($db));
return false;
}
}
// copy all item codes from stock_master into item_codes
$sql = "SELECT `stock_id`,`description`,`category_id` FROM ".TB_PREF."stock_master";
$result = db_query($sql);
if (!$result) {
display_error(_("Cannot select stock identificators")
.':
'. db_error_msg($db));
return false;
} else {
while ($row = db_fetch_assoc($result)) {
$sql = "INSERT IGNORE "
.TB_PREF."item_codes (`item_code`,`stock_id`,`description`,`category_id`)
VALUES('".$row['stock_id']."','".$row['stock_id']."','"
.$row['description']."','".$row['category_id']."')";
$res2 = db_query($sql);
if (!$res2) {
display_error(_("Cannot insert stock id into item_codes")
.':
'. db_error_msg($db));
return false;
}
}
}
// remove obsolete bank_trans_types table
// (DROP queries are skipped during non-forced upgrade)
$sql = "DROP TABLE IF EXISTS `".TB_PREF."bank_trans_types`";
db_query($sql);
//
// Move all debtor and supplier trans tax details to new table
// (INSERT INTO t SELECT ... FROM t ... available after 4.0.14)
// No easy way to restore net amount for 0% tax rate for moved
// FA 2.0 transactions, but who cares?
//
$move_sql =array(
"debtor_trans_tax_details" =>
"SELECT tr.tran_date, tr.type, tr.trans_no, dt.tax_type_id,
dt.rate, dt.included_in_price, dt.amount, tr.reference as ref,
tr.rate as ex_rate
FROM ".TB_PREF."debtor_trans_tax_details dt
LEFT JOIN ".TB_PREF."trans_tax_details tt
ON dt.debtor_trans_no=tt.trans_no
AND dt.debtor_trans_type=tt.trans_type,
".TB_PREF."debtor_trans tr
WHERE tt.trans_type is NULL
AND dt.debtor_trans_no = tr.trans_no
AND dt.debtor_trans_type = tr.type",
"supp_invoice_tax_items" =>
"SELECT tr.tran_date, tr.type, tr.trans_no, st.tax_type_id,
st.rate, st.included_in_price, st.amount, tr.supp_reference as ref,
tr.rate as ex_rate
FROM ".TB_PREF."supp_invoice_tax_items st
LEFT JOIN ".TB_PREF."trans_tax_details tt
ON st.supp_trans_no=tt.trans_no
AND st.supp_trans_type=tt.trans_type,
".$pref."supp_trans tr
WHERE tt.trans_type is NULL
AND st.supp_trans_no = tr.trans_no
AND st.supp_trans_type = tr.type");
foreach ($move_sql as $tbl => $sql) {
if (!check_table(TB_PREF, $tbl)){
$res = db_query($sql, "Cannot retrieve trans tax details from $tbl");
while ($row = db_fetch($res)) {
$net_amount = $row['rate'] == 0 ?
0 : ($row['included_in_price'] ?
($row['amount']/$row['rate']*(100-$row['rate']))
:($row['amount']/$row['rate']*100));
$sql2 = "INSERT INTO ".TB_PREF."trans_tax_details
(trans_type,trans_no,tran_date,tax_type_id,rate,ex_rate,
included_in_price, net_amount, amount, memo)
VALUES ('".$row['type']."','".$row['trans_no']."','"
.$row['tran_date']."','".$row['tax_type_id']."','"
.$row['rate']."','".$row['ex_rate']."','"
.$row['included_in_price']."','".$net_amount
."','".$row['amount']."','".$row['ref']."')";
db_query($sql2, "Cannot move trans tax details from $tbl");
}
db_query("DROP TABLE ".TB_PREF.$tbl, "cannot remove $tbl");
}
}
return true;
}
//
// Checking before install
//
function prepare()
{
// We cannot perform successfull upgrade on system where the
// trans tax details tables was deleted during previous try.
$pref = $this->companies[$company]['tbpref'];
if (check_table($pref, 'debtor_trans_tax_details')
|| check_table($pref, 'supp_invoice_tax_items')) {
display_error(_("Seems that system upgrade to version 2.1 has
been performed for this company already.
If something has gone
wrong and you want to retry upgrade process you MUST perform
database restore from last backup file first."));
return false;
}
return true; // true when ok, fail otherwise
}
};
$install = new fa2_1;