This removes problems with databases spread over multiply hosts and make possible db_export to
central backup directory.
/*
Update main or gl company setup.
*/
-function update_company_prefs( $params, $pref = TB_PREF )
+function update_company_prefs($params)
{
- $sql = "UPDATE {$pref}sys_prefs SET value = ";
+ $sql = "UPDATE ".TB_PREF."sys_prefs SET value = ";
foreach($params as $name => $value) {
if (!db_query($sql. db_escape($value). " WHERE name=".db_escape($name),
"The company prefferences could not be updated "))
$prefs can be preference name, array of names, or null for all preferences.
*/
-function get_company_pref($prefs = null, $tbpref = null)
+function get_company_pref($prefs = null)
{
global $SysPrefs, $db_version;
$_SESSION['SysPrefs'] = new sys_prefs();
- if (!isset($tbpref))
- $tbpref = TB_PREF;
+ if (!isset($company))
+ $company = user_company();
- $sql = "SELECT name, value FROM {$tbpref}sys_prefs";
+ $sql = "SELECT name, value FROM ".TB_PREF."sys_prefs";
$result = @db_query($sql); // supress errors before 2.3 db structure upgrade
if(!$result)
return $ret;
}
-function get_company_prefs($tbpref = TB_PREF)
+function get_company_prefs()
{
- return get_company_pref(null, $tbpref);
+ return get_company_pref(null);
}
function set_company_pref($pref, $category, $type, $length, $value)
return explode("\n", $file_data);
}
-function db_backup($conn, $ext='no', $comm='', $tbpref = TB_PREF)
+function db_backup($conn, $ext='no', $comm='', $path=null)
{
if ($conn['tbpref'] != "")
$filename = $conn['dbname'] . "_" . $conn['tbpref'] . date("Ymd_Hi") . ".sql";
else
$filename = $conn['dbname'] . "_" . date("Ymd_Hi") . ".sql";
- return db_export($conn, clean_file_name($filename), $ext, $comm, $tbpref);
+ if (!isset($path))
+ $path = BACKUP_PATH;
+
+ return db_export($conn, $path . clean_file_name($filename), $ext, $comm);
}
// generates a dump of $db database
// $drop and $zip tell if to include the drop table statement or dry to pack
-function db_export($conn, $filename, $zip='no', $comment='', $tbpref = TB_PREF, $no_default=false)
+function db_export($conn, $filename, $zip='no', $comment='', $no_default=false)
{
global $app_title, $version, $power_url, $path_to_root;
+
$error = false;
// set max string size before writing to file
$max_size = 1048576 * 2; // 2 MB
$backupfile = $filename . ".zip";
else
$backupfile = $filename;
- $company = get_company_pref('coy_name', $tbpref);
+ $company = $conn['name']; // get_company_pref('coy_name');
//create comment
$out="# MySQL dump of database '".$conn["dbname"]."' on host '".$conn["host"]."'\n";
}
elseif ($error)
{
- @unlink(BACKUP_PATH . $backupfile);
+ @unlink($backupfile);
return false;
}
}
else
{
- @unlink(BACKUP_PATH . $backupfile);
+ @unlink($backupfile);
return false;
}
}
else
{
- @unlink(BACKUP_PATH . $backupfile);
+ @unlink($backupfile);
return false;
}
return $backupfile;
// saves the string in $fileData to the file $backupfile as gz file or not ($zip)
// returns backup file name if name has changed (zip), else TRUE. If saving failed, return value is FALSE
-function save_to_file($backupfile, $zip, $fileData)
+function save_to_file($path, $zip, $fileData)
{
global $path_to_root;
+ $backupfile = basename($path);
+
if ($zip == "gzip")
{
- if ($zp = @gzopen(BACKUP_PATH . $backupfile, "a9"))
+ if ($zp = @gzopen($path, "a9"))
{
@gzwrite($zp, $fileData);
@gzclose($zp);
// total # of entries "on this disk", total # of entries overall, size of central dir, offset to start of central dir, .zip file comment length
$fileData .= pack('v', 1) . pack('v', 1) . pack('V', strlen($cdrec)) . pack('V', strlen($fr)) . "\x00\x00";
- if ($zp = @fopen(BACKUP_PATH . $backupfile, "a"))
+ if ($zp = @fopen($path, "a"))
{
@fwrite($zp, $fileData);
@fclose($zp);
}
else
{
- if ($zp = @fopen(BACKUP_PATH . $backupfile, "a"))
+ if ($zp = @fopen($path, "a"))
{
@fwrite($zp, $fileData);
@fclose($zp);
//
// Apply one differential data set.
//
-function upgrade_step($index, $conn)
+function upgrade_step($index, $company, $conn)
{
global $path_to_root, $installers;
if ($sql != '')
$ret &= db_import($path_to_root.'/sql/'.$sql, $conn, $force);
- $ret &= $inst->install($pref, $force);
+ $ret &= $inst->install($company, $force);
error_log(_("Database upgrade finished."));
continue;
}
// create security backup
- db_backup($conn, 'no', 'Security backup before upgrade', $conn['tbpref']);
+ db_backup($conn, 'no', 'Security backup before upgrade');
// apply all upgrade data
foreach ($installers as $i => $inst)
{
- $ret = upgrade_step($i, $conn);
+ $ret = upgrade_step($i, $comp, $conn);
if (!$ret)
display_error(
sprintf(_("Database upgrade to version %s failed for company '%s'."),
function read_po_header($order_no, &$order)
{
- $sql = "SELECT ".TB_PREF."purch_orders.*, ".TB_PREF."suppliers.supp_name, ".TB_PREF."suppliers.tax_group_id,
- ".TB_PREF."suppliers.curr_code, ".TB_PREF."locations.location_name, ".TB_PREF."suppliers.tax_algorithm
+ $sql = "SELECT ".TB_PREF."purch_orders.*, "
+ .TB_PREF."suppliers.*, "
+ .TB_PREF."locations.location_name
FROM ".TB_PREF."purch_orders, ".TB_PREF."suppliers, ".TB_PREF."locations
WHERE ".TB_PREF."purch_orders.supplier_id = ".TB_PREF."suppliers.supplier_id
AND ".TB_PREF."locations.loc_code = into_stock_location
$order->order_no = $order_no;
$order->set_supplier($myrow["supplier_id"], $myrow["supp_name"], $myrow["curr_code"],
- $myrow['tax_group_id'], $myrow["tax_included"], $myrow["tax_algorithm"]);
+ $myrow['tax_group_id'], $myrow["tax_included"], @$myrow["tax_algorithm"]);
$order->credit = get_current_supp_credit($order->supplier_id);
// Install procedure. All additional changes
// not included in sql file should go here.
//
- function install($pref, $force)
+ function install($company, $force)
{
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 ".$pref."bank_accounts";
+ $sql = "SELECT id, account_code FROM ".TB_PREF."bank_accounts";
if(!($res = db_query($sql))) {
display_error(_("Cannot retrieve bank accounts codes")
.':<br>'. db_error_msg($db));
return false;
}
while ($acc = db_fetch($res)) {
- $sql = "UPDATE ".$pref."bank_trans SET bank_act='"
+ $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")
}
}
// copy all item codes from stock_master into item_codes
- $sql = "SELECT `stock_id`,`description`,`category_id` FROM ".$pref."stock_master";
+ $sql = "SELECT `stock_id`,`description`,`category_id` FROM ".TB_PREF."stock_master";
$result = db_query($sql);
if (!$result) {
display_error(_("Cannot select stock identificators")
} else {
while ($row = db_fetch_assoc($result)) {
$sql = "INSERT IGNORE "
- .$pref."item_codes (`item_code`,`stock_id`,`description`,`category_id`)
+ .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);
"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 ".$pref."debtor_trans_tax_details dt
- LEFT JOIN ".$pref."trans_tax_details tt
+ 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,
- ".$pref."debtor_trans tr
+ ".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",
"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 ".$pref."supp_invoice_tax_items st
- LEFT JOIN ".$pref."trans_tax_details tt
+ 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
AND st.supp_trans_type = tr.type");
foreach ($move_sql as $tbl => $sql) {
- if (!check_table($pref, $tbl)){
+ 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 ".$pref."trans_tax_details
+ $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['amount']."','".$row['ref']."')";
db_query($sql2, "Cannot move trans tax details from $tbl");
}
- db_query("DROP TABLE ".$pref.$tbl, "cannot remove $tbl");
+ db_query("DROP TABLE ".TB_PREF.$tbl, "cannot remove $tbl");
}
}
// Install procedure. All additional changes
// not included in sql file should go here.
//
- function install($pref, $force)
+ function install($company, $force)
{
- global $db, $systypes_array;
+ global $db, $systypes_array, $db_connections;
if (!$this->preconf)
return false;
+ $pref = $db_connections[$company]['tbpref'];
// Until 2.2 sanitizing text input with db_escape was not
// consequent enough. To avoid comparision problems we have to
// fix this now.
// set item category dflt accounts to values from company GL setup
$prefs = get_company_prefs();
- $sql = "UPDATE {$pref}stock_category SET "
+ $sql = "UPDATE ".TB_PREF."stock_category SET "
."dflt_sales_act = '" . $prefs['default_inv_sales_act'] . "',"
."dflt_cogs_act = '". $prefs['default_cogs_act'] . "',"
."dflt_inventory_act = '" . $prefs['default_inventory_act'] . "',"
foreach($systypes_array as $typeno => $typename) {
$info = get_systype_db_info($typeno);
if ($info == null || $info[3] == null) continue;
- $tbl = str_replace(TB_PREF, $pref, $info[0]);
+ $tbl = $info[0];
$sql = "SELECT DISTINCT {$info[2]} as id,{$info[3]} as ref FROM $tbl";
if ($info[1])
$sql .= " WHERE {$info[1]}=$typeno";
$result = db_query($sql);
if (db_num_rows($result)) {
while ($row = db_fetch($result)) {
- $res2 = db_query("INSERT INTO {$pref}refs VALUES("
+ $res2 = db_query("INSERT INTO ".TB_PREF."refs VALUES("
. $row['id'].",".$typeno.",'".$row['ref']."')");
if (!$res2) {
display_error(_("Cannot copy references from $tbl")
}
}
- if (!($ret = db_query("SELECT MAX(`order_no`) FROM `{$pref}sales_orders`")) ||
+ if (!($ret = db_query("SELECT MAX(`order_no`) FROM `".TB_PREF."sales_orders`")) ||
!db_num_rows($ret))
{
display_error(_('Cannot query max sales order number.'));
$row = db_fetch($ret);
$max_order = $row[0];
$next_ref = $max_order+1;
- $sql = "UPDATE `{$pref}sys_types`
+ $sql = "UPDATE `".TB_PREF."sys_types`
SET `type_no`='$max_order',`next_reference`='$next_ref'
WHERE `type_id`=30";
if(!db_query($sql))
$patchcnt = 0;
if (!$this->beta) {
$n = 16;
- if (check_table($pref, 'company', 'custom1_name')) $patchcnt++;
- if (!check_table($pref, 'company', 'profit_loss_year_act')) $patchcnt++;
- if (!check_table($pref, 'company', 'login_tout')) $patchcnt++;
+ if (check_table($pref, 'company')) // skip in 2.3
+ $n -= 3;
+ else {
+ if (check_table($pref, 'company', 'custom1_name')) $patchcnt++;
+ if (!check_table($pref, 'company', 'profit_loss_year_act')) $patchcnt++;
+ if (!check_table($pref, 'company', 'login_tout')) $patchcnt++;
+ }
if (!check_table($pref, 'stock_category', 'dflt_no_sale')) $patchcnt++;
if (!check_table($pref, 'users', 'sticky_doc_date')) $patchcnt++;
if (!check_table($pref, 'users', 'startup_tab')) $patchcnt++;
}
$sections = array_keys($sections);
sort($sections); sort($area_set);
- import_security_role($pref, $security_headings[$role_id], $sections, $area_set);
+ import_security_role($security_headings[$role_id], $sections, $area_set);
$new_ids[$role_id] = db_insert_id();
}
$result = get_users(true);
}
foreach($users as $old_id => $uids)
foreach( $uids as $id) {
- $sql = "UPDATE {$pref}users set role_id=".$new_ids[$old_id].
+ $sql = "UPDATE ".TB_PREF."users set role_id=".$new_ids[$old_id].
" WHERE id=$id";
$ret = db_query($sql, 'cannot update users roles');
if(!$ret) return false;
return true;
}
-function import_security_role($pref, $name, $sections, $areas)
+function import_security_role($name, $sections, $areas)
{
- $sql = "INSERT INTO {$pref}security_roles (role, description, sections, areas)
+ $sql = "INSERT INTO ".TB_PREF."security_roles (role, description, sections, areas)
VALUES (".db_escape('FA 2.1 '.$name).",".db_escape($name).","
.db_escape(implode(';',$sections)).",".db_escape(implode(';',$areas)).")";
}
}
+ if (empty($keys)) { // comments table have no primary key, so give up
+ continue;
+ }
if ($test)
error_log("Table $table (".implode(',',$keys)."):(".implode(',',$textcols)."):");
// Install procedure. All additional changes
// not included in sql file should go here.
//
- function install($pref, $force)
+ function install($company, $force)
{
global $db_version, $dflt_lang;
if (!$this->beta) {
// all specials below are already done on 2.3beta
- $sql = "SELECT debtor_no, payment_terms FROM {$pref}debtors_master";
+ $sql = "SELECT debtor_no, payment_terms FROM ".TB_PREF."debtors_master";
$result = db_query($sql);
if (!$result) {
}
// update all sales orders and transactions with customer std payment terms
while($cust = db_fetch($result)) {
- $sql = "UPDATE {$pref}debtor_trans SET "
+ $sql = "UPDATE ".TB_PREF."debtor_trans SET "
."payment_terms = '" .$cust['payment_terms']
."' WHERE debtor_no='".$cust['debtor_no']."'";
if (db_query($sql)==false) {
.':<br>'. db_error_msg($db));
return false;
}
- $sql = "UPDATE {$pref}sales_orders SET "
+ $sql = "UPDATE ".TB_PREF."sales_orders SET "
."payment_terms = '" .$cust['payment_terms']
."' WHERE debtor_no='".$cust['debtor_no']."'";
if (db_query($sql)==false) {
return false;
}
}
- if (!$this->update_totals($pref)) {
+ if (!$this->update_totals()) {
display_error("Cannot update order totals");
return false;
}
- if (!$this->update_line_relations($pref)) {
+ if (!$this->update_line_relations()) {
display_error("Cannot update sales document links");
return false;
}
foreach($dropcol as $table => $columns)
foreach($columns as $col) {
- if (db_query("ALTER TABLE `{$pref}{$table}` DROP `$col`")==false) {
+ if (db_query("ALTER TABLE `".TB_PREF."{$table}` DROP `$col`")==false) {
display_error("Cannot drop {$table}.{$col} column:<br>".db_error_msg($db));
return false;
}
}
// remove old preferences table after upgrade script has been executed
- $sql = "DROP TABLE IF EXISTS `{$pref}company`";
+ $sql = "DROP TABLE IF EXISTS `".TB_PREF."company`";
if (!db_query($sql))
return false;
}
$this->update_lang_cfg();
- return update_company_prefs(array('version_id'=>$db_version), $pref);
+ return update_company_prefs(array('version_id'=>$db_version));
}
//
// Checking before install
/*
Update order totals
*/
- function update_totals($pref)
+ function update_totals()
{
global $path_to_root;
include_once("$path_to_root/sales/includes/cart_class.inc");
include_once("$path_to_root/purchasing/includes/po_class.inc");
$cart = new cart(ST_SALESORDER);
- $sql = "SELECT order_no, trans_type FROM {$pref}sales_orders";
+ $sql = "SELECT order_no, trans_type FROM ".TB_PREF."sales_orders";
$orders = db_query($sql);
if (!$orders)
return false;
while ($order = db_fetch($orders)) {
read_sales_order($order['order_no'], $cart, $order['trans_type']);
- $result = db_query("UPDATE {$pref}sales_orders
+ $result = db_query("UPDATE ".TB_PREF."sales_orders
SET total=".$cart->get_trans_total()
." WHERE order_no=".$order[0]);
unset($cart->line_items);
}
unset($cart);
$cart = new purch_order();
- $sql = "SELECT order_no FROM {$pref}purch_orders";
+ $sql = "SELECT order_no FROM ".TB_PREF."purch_orders";
$orders = db_query($sql);
if (!$orders)
return false;
while ($order_no = db_fetch($orders)) {
read_po($order_no[0], $cart);
- $result = db_query("UPDATE {$pref}purch_orders SET total=".$cart->get_trans_total());
+ $result = db_query("UPDATE ".TB_PREF."purch_orders SET total=".$cart->get_trans_total());
unset($cart->line_items);
}
return true;
there can be sales documents with lines not properly linked to parents. This rare
cases will be described in error log.
*/
- function update_line_relations($pref)
+ function update_line_relations()
{
global $path_to_root, $systypes_array;
require_once("$path_to_root/includes/sysnames.inc");
- $sql = "SELECT d.type, trans_no, order_ FROM {$pref}debtor_trans d
- LEFT JOIN {$pref}voided v ON d.type=v.type AND d.trans_no=v.id
+ $sql = "SELECT d.type, trans_no, order_ FROM ".TB_PREF."debtor_trans d
+ LEFT JOIN ".TB_PREF."voided v ON d.type=v.type AND d.trans_no=v.id
WHERE ISNULL(v.type) AND
(d.type=".ST_CUSTDELIVERY
." OR d.type=".ST_SALESINVOICE
if ($src_line['stock_id'] == $doc_line['stock_id']
&& ($src_line['quantity'] >= $doc_line['quantity'])) {
- $sql = "UPDATE {$pref}debtor_trans_details SET src_id = {$src_line['id']}
+ $sql = "UPDATE ".TB_PREF."debtor_trans_details SET src_id = {$src_line['id']}
WHERE id = {$doc_line['id']}";
if (!db_query($sql))
return false;
// Install procedure. All additional changes
// not included in sql file should go here.
//
- function install($pref, $force)
+ function install($company, $force)
{
global $db_version;
set_company_pref('grn_clearing_act', 'glsetup.purchase', 'varchar', 15, 0);
}
if ($this->update_workorders())
-// return update_company_prefs(array('version_id'=>$db_version), $pref);
+// return update_company_prefs(array('version_id'=>$db_version));
return true;
}
//
projects / fa-stable.git / commitdiff