summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--Changelog1
-rwxr-xr-xLedgerSMB/AA.pm6
-rwxr-xr-xLedgerSMB/AM.pm81
-rwxr-xr-xLedgerSMB/CP.pm22
-rwxr-xr-xLedgerSMB/CT.pm10
-rwxr-xr-xLedgerSMB/Form.pm94
-rwxr-xr-xLedgerSMB/HR.pm2
-rwxr-xr-xLedgerSMB/IC.pm28
-rwxr-xr-xLedgerSMB/IR.pm76
-rwxr-xr-xLedgerSMB/IS.pm26
-rwxr-xr-xLedgerSMB/JC.pm2
-rwxr-xr-xLedgerSMB/OE.pm14
-rwxr-xr-xLedgerSMB/PE.pm18
-rwxr-xr-xLedgerSMB/RP.pm8
-rwxr-xr-xLedgerSMB/Session/DB.pm3
-rwxr-xr-xLedgerSMB/User.pm24
-rw-r--r--README.svn-status3
-rwxr-xr-xbin/admin.pl3
-rwxr-xr-xsql/Canada-English_General-chart.sql14
-rwxr-xr-xsql/Default-chart.sql16
-rwxr-xr-xsql/Pg-functions.sql1
-rwxr-xr-xsql/Pg-tables.sql68
22 files changed, 335 insertions, 185 deletions
diff --git a/Changelog b/Changelog
index 12309378..aca88128 100644
--- a/Changelog
+++ b/Changelog
@@ -4,6 +4,7 @@ Database:
* Added script to configure Slony replication (Chris Browne)
* Added defined primary keys to all tables (Chris T)
* Database upgrades now use psql (Chris T)
+* Defaults table now uses a simple key->value system (Chris T)
Security:
* Added whitelist of allowed directories to file editor (Seneca)
diff --git a/LedgerSMB/AA.pm b/LedgerSMB/AA.pm
index 3dfda666..d624eaa0 100755
--- a/LedgerSMB/AA.pm
+++ b/LedgerSMB/AA.pm
@@ -213,8 +213,10 @@ sub post_transaction {
$query = q|
- SELECT fxgain_accno_id, fxloss_accno_id
- FROM defaults|;
+ SELECT (SELECT value FROM defaults
+ WHERE setting_key = 'fxgain_accno_id'),
+ (SELECT value FROM defaults
+ WHERE setting_key = 'fxloss_accno_id')|;
my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
diff --git a/LedgerSMB/AM.pm b/LedgerSMB/AM.pm
index fc2cc677..028d7183 100755
--- a/LedgerSMB/AM.pm
+++ b/LedgerSMB/AM.pm
@@ -59,9 +59,21 @@ sub get_account {
# get default accounts
$query = qq|
- SELECT inventory_accno_id, income_accno_id, expense_accno_id,
- fxgain_accno_id, fxloss_accno_id
- FROM defaults|;
+ SELECT (SELECT value FROM defaults
+ WHERE setting_key = 'inventory_accno_id')
+ AS inventory_accno_id,
+ (SELECT value FROM defaults
+ WHERE setting_key = 'income_accno_id')
+ AS income_accno_id,
+ (SELECT value FROM defaults
+ WHERE setting_key = 'expense_accno_id')
+ AS expense_accno_id,
+ (SELECT value FROM defaults
+ WHERE setting_key = 'fxgain_accno_id')
+ AS fxgain_accno_id,
+ (SELECT value FROM defaults
+ WHERE setting_key = 'fxloss_accno_id')
+ AS fxloss_accno_id|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
@@ -249,8 +261,9 @@ sub delete_account {
for (qw(income_accno_id expense_accno_id)){
$query = qq|
UPDATE parts
- SET $_ = (SELECT $_
- FROM defaults)
+ SET $_ = (SELECT value
+ FROM defaults
+ WHERE setting_key = '$_')
WHERE $_ = ?|;
$sth = $dbh->prepare($query);
@@ -950,7 +963,7 @@ sub recurring_transactions {
my $dbh = $form->{dbh};
- my $query = qq|SELECT curr FROM defaults|;
+ my $query = qq|SELECT value FROM defaults where setting_key = 'curr'|;
my ($defaultcurrency) = $dbh->selectrow_array($query);
$defaultcurrency = $dbh->quote($defaultcurrency =~ s/:.*//g);
@@ -1308,8 +1321,11 @@ sub save_preferences {
$dbh->prepare($query)->execute(@queryargs) || $form->dberror($query);
# get default currency
- $query = qq|SELECT curr, businessnumber
- FROM defaults|;
+ $query = qq|
+ SELECT value, (SELECT value FROM defaults
+ WHERE setting_key = 'businessnumber')
+ FROM defaults
+ WHERE setting_key = 'curr'|;
($form->{currency}, $form->{businessnumber}) =
$dbh->selectrow_array($query);
@@ -1406,10 +1422,16 @@ sub defaultaccounts {
my $dbh = $form->{dbh};
# get defaults from defaults table
- my $query = qq|SELECT * FROM defaults|;
+ my $query = qq|
+ SELECT setting_key, value FROM defaults
+ WHERE setting_key LIKE ?|;
my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $sth->execute('%accno_id') || $form->dberror($query);
+ my $ref;
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)){
+ $form->{$ref->{setting_key}} = $ref->{value};
+ }
my $ref = $sth->fetchrow_hashref(NAME_lc);
for (keys %$ref) { $form->{$_} = $ref->{$_} }
@@ -1644,10 +1666,15 @@ sub closedto {
my $dbh = $form->{dbh};
my $query = qq|
- SELECT closedto, revtrans, audittrail
- FROM defaults|;
+ SELECT (SELECT value FROM defaults
+ WHERE setting_key = 'closedto'),
+ (SELECT value FROM defaults
+ WHERE setting_key = 'revtrans'),
+ (SELECT value FROM defaults
+ WHERE setting_key = 'audittrail')|;
- ($form->{closedto}, $form->{revtrans}, $form->{audittrail}) = $dbh->selectrow_array($query);
+ ($form->{closedto}, $form->{revtrans}, $form->{audittrail})
+ = $dbh->selectrow_array($query);
$dbh->commit;
@@ -1659,24 +1686,20 @@ sub closebooks {
my ($self, $myconfig, $form) = @_;
my $dbh = $form->{dbh};
- my $query = qq|UPDATE defaults SET|;
-
- if ($form->{revtrans}) {
- $query .= qq| revtrans = '1'|;
- } else {
- $query .= qq| revtrans = '0'|;
- }
-
- $query .= qq|, closedto = |.$dbh->quote($form->{closedto});
-
- if ($form->{audittrail}) {
- $query .= qq|, audittrail = '1'|;
- } else {
- $query .= qq|, audittrail = '0'|;
+ my $query = qq|
+ UPDATE defaults SET value = ?
+ WHERE setting_key = ?|;
+ my $sth = $dbh->prepare($query);
+ for (qw(revtrans, closedto, audittrail)){
+
+ if ($form->{$_}){
+ $val = 1;
+ } else {
+ $val = 0;
+ }
+ $sth->execute($val, $_);
}
- # set close in defaults
- $dbh->do($query) || $form->dberror($query);
if ($form->{removeaudittrail}) {
$query = qq|
diff --git a/LedgerSMB/CP.pm b/LedgerSMB/CP.pm
index 8e1a21f6..8cd22162 100755
--- a/LedgerSMB/CP.pm
+++ b/LedgerSMB/CP.pm
@@ -88,8 +88,12 @@ sub paymentaccounts {
$sth->finish;
# get currencies and closedto
- $query = qq|SELECT curr, closedto, current_date
- FROM defaults|;
+ $query = qq|
+ SELECT value, (SELECT value FROM defaults
+ WHERE setting_key = 'closedto'),
+ current_date
+ FROM defaults
+ WHERE setting_key = 'curr'|;
($form->{currencies}, $form->{closedto}, $form->{datepaid}) = $dbh->selectrow_array($query);
@@ -325,8 +329,11 @@ sub post_payment {
$form->{exchangerate} = 1;
}
- my $query = qq|SELECT fxgain_accno_id, fxloss_accno_id
- FROM defaults|;
+ my $query = qq|
+ SELECT (SELECT value FROM defaults
+ WHERE setting_key='fxgain_accno_id'),
+ (SELECT value FROM defaults
+ WHERE setting_key='fxloss_accno_id'|;
my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
@@ -573,8 +580,11 @@ sub post_payments {
$form->{exchangerate} = 1;
}
- my $query = qq|SELECT fxgain_accno_id, fxloss_accno_id
- FROM defaults|;
+ my $query = qq|
+ SELECT (SELECT value FROM defaults
+ WHERE setting_key='fxgain_accno_id'),
+ (SELECT value FROM defaults
+ WHERE setting_key='fxloss_accno_id'|;
my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
diff --git a/LedgerSMB/CT.pm b/LedgerSMB/CT.pm
index f3df8113..c3dcdf29 100755
--- a/LedgerSMB/CT.pm
+++ b/LedgerSMB/CT.pm
@@ -111,7 +111,7 @@ sub create_links {
($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
- $query = qq|SELECT current_date FROM defaults|;
+ $query = qq|SELECT current_date|;
($form->{startdate}) = $dbh->selectrow_array($query);
}
@@ -188,8 +188,10 @@ sub create_links {
$sth->finish;
# get currencies
- $query = qq|SELECT curr AS currencies
- FROM defaults|;
+ $query = qq|
+ SELECT value AS currencies
+ FROM defaults
+ WHERE setting_key = 'curr'|;
($form->{currencies}) = $dbh->selectrow_array($query);
@@ -1085,7 +1087,7 @@ sub pricelist {
$sth->finish;
}
- $query = qq|SELECT curr FROM defaults|;
+ $query = qq|SELECT value FROM defaults where setting_key = 'curr'|;
($form->{currencies}) = $dbh->selectrow_array($query);
$query = qq|SELECT id, partsgroup
diff --git a/LedgerSMB/Form.pm b/LedgerSMB/Form.pm
index 4e1d5aad..22dbf06f 100755
--- a/LedgerSMB/Form.pm
+++ b/LedgerSMB/Form.pm
@@ -68,7 +68,7 @@ sub new {
$self->{lynx} = 1 if $self->{path} =~ /lynx/i;
$self->{version} = "1.1.1";
- $self->{dbversion} = "2.6.18";
+ $self->{dbversion} = "1.2.0";
bless $self, $type;
@@ -1993,9 +1993,9 @@ sub all_years {
$dbh = $self->{dbh};
# get years
- my $query = qq|SELECT (SELECT MIN(transdate) FROM acc_trans),
- (SELECT MAX(transdate) FROM acc_trans)
- FROM defaults|;
+ my $query = qq|
+ SELECT (SELECT MIN(transdate) FROM acc_trans),
+ (SELECT MAX(transdate) FROM acc_trans)|;
my ($startdate, $enddate) = $dbh->selectrow_array($query);
@@ -2167,30 +2167,43 @@ sub create_links {
$sth->finish;
- $query = qq|SELECT d.curr AS currencies, d.closedto, d.revtrans
- FROM defaults d|;
+ for (qw(curr closedto revtrans)){
+ $query = qq|
+ SELECT value FROM defaults
+ WHERE setting_key = '$_'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $self->dberror($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute || $self->dberror($query);
- $ref = $sth->fetchrow_hashref(NAME_lc);
- for (keys %$ref) { $self->{$_} = $ref->{$_} }
- $sth->finish;
+ (undef, $val) = $sth->fetchrow_array();
+ if ($_ eq 'curr'){
+ $form->{currencies} = $val;
+ } else {
+ $form->{$_} = $val;
+ }
+ $sth->finish;
+ }
} else {
- # get date
- $query = qq|
- SELECT current_date AS transdate,
- d.curr AS currencies, d.closedto, d.revtrans
- FROM defaults d|;
+ for (qw(current_date curr closedto revtrans)){
+ $query = qq|
+ SELECT value FROM defaults
+ WHERE setting_key = '$_'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $self->dberror($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute || $self->dberror($query);
- $ref = $sth->fetchrow_hashref(NAME_lc);
- for (keys %$ref) { $self->{$_} = $ref->{$_} }
- $sth->finish;
+ (undef, $val) = $sth->fetchrow_array();
+ if ($_ eq 'curr'){
+ $form->{currencies} = $val;
+ } elsif ($_ eq 'current_date'){
+ $form->{transdate} = $val;
+ } else {
+ $form->{$_} = $val;
+ }
+ $sth->finish;
+ }
if (! $self->{"$self->{vc}_id"}) {
$self->lastname_used($myconfig, $dbh, $vc, $module);
@@ -2279,13 +2292,11 @@ sub current_date {
}
$query = qq|SELECT to_date(?, ?)
- + ? AS thisdate
- FROM defaults|;
+ + ? AS thisdate|;
@queryargs = ($thisdate, $dateformat, $days);
} else {
- $query = qq|SELECT current_date AS thisdate
- FROM defaults|;
+ $query = qq|SELECT current_date AS thisdate|;
@queryargs = ();
}
@@ -2608,16 +2619,14 @@ sub save_recurring {
$interval{'Pg'} =
"(date '$s{startdate}' + interval '$advance $s{unit}')";
- $query = qq|SELECT $interval{$myconfig->{dbdriver}}
- FROM defaults|;
+ $query = qq|SELECT $interval{$myconfig->{dbdriver}}|;
my ($enddate) = $dbh->selectrow_array($query);
# calculate nextdate
$query = qq|
SELECT current_date - date ? AS a,
- date ? - current_date AS b
- FROM defaults|;
+ date ? - current_date AS b|;
$sth = $dbh->prepare($query);
$sth->execute($s{startdate}, $enddate);
@@ -2637,8 +2646,8 @@ sub save_recurring {
$interval{Oracle} = $interval{PgPP} = $interval{Pg};
- $query = qq|SELECT $interval{$myconfig->{dbdriver}}
- FROM defaults|;
+
+ $query = qq|SELECT $interval{$myconfig->{dbdriver}}|;
($nextdate) = $dbh->selectrow_array($query);
}
@@ -2651,8 +2660,7 @@ sub save_recurring {
$nextdate = $self->{recurringnextdate};
- $query = qq|SELECT '$enddate' - date '$nextdate'
- FROM defaults|;
+ $query = qq|SELECT '$enddate' - date '$nextdate'|;
if ($dbh->selectrow_array($query) < 0) {
undef $nextdate;
@@ -2751,7 +2759,11 @@ sub update_defaults {
$dbh = $_[3];
}
- my $query = qq|SELECT $fld FROM defaults FOR UPDATE|;
+ my $query = qq|
+ SELECT value FROM defaults
+ WHERE setting_key = ? FOR UPDATE|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($fld);
($_) = $dbh->selectrow_array($query);
$_ = "0" unless $_;
@@ -2855,11 +2867,13 @@ sub update_defaults {
}
}
- $query = qq|UPDATE defaults
- SET $fld = ?|;
+ $query = qq|
+ UPDATE defaults
+ SET value = ?
+ WHERE setting_key = ?|;
$sth = $dbh->prepare($query);
- $sth->execute($dbvar) || $self->dberror($query);
+ $sth->execute($dbvar, $fld) || $self->dberror($query);
$dbh->commit;
@@ -3028,7 +3042,9 @@ sub audittrail {
if ($audittrail->{id}) {
- $query = qq|SELECT audittrail FROM defaults|;
+ $query = qq|
+ SELECT value FROM defaults
+ WHERE setting_key = 'audittrail'|;
if ($dbh->selectrow_array($query)) {
@@ -3112,7 +3128,7 @@ sub audittrail {
} else {
- $query = qq|SELECT current_timestamp FROM defaults|;
+ $query = qq|SELECT current_timestamp|;
my ($timestamp) = $dbh->selectrow_array($query);
$rv = "$audittrail->{tablename}|$audittrail->{reference}|$audittrail->{formname}|$audittrail->{action}|$timestamp|";
diff --git a/LedgerSMB/HR.pm b/LedgerSMB/HR.pm
index 1abf3e6b..ee5a2c32 100755
--- a/LedgerSMB/HR.pm
+++ b/LedgerSMB/HR.pm
@@ -93,7 +93,7 @@ if ($form->{deductions}) {
} else {
- $query = qq|SELECT current_date FROM defaults|;
+ $query = qq|SELECT current_date|;
($form->{startdate}) = $dbh->selectrow_array($query);
}
diff --git a/LedgerSMB/IC.pm b/LedgerSMB/IC.pm
index c5ff74f6..f96b1294 100755
--- a/LedgerSMB/IC.pm
+++ b/LedgerSMB/IC.pm
@@ -953,7 +953,7 @@ sub all_parts {
my @a = qw(partnumber description);
my $sortorder = $form->sort_order(\@a, \%ordinal);
- my $query = qq|SELECT curr FROM defaults|;
+ my $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
my ($curr) = $dbh->selectrow_array($query);
$curr =~ s/:.*//;
@@ -1678,20 +1678,28 @@ sub create_links {
if ($form->{id}) {
- $query = qq|SELECT weightunit, curr AS currencies
- FROM defaults|;
- ($form->{weightunit}, $form->{currencies}) = $dbh->selectrow_array($query);
+ $query = qq|SELECT value FROM defaults WHERE setting_key = 'weightunit'|;
+ ($form->{weightunit}) = $dbh->selectrow_array($query);
+ $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
+ ($form->{currencies}) = $dbh->selectrow_array($query);
} else {
- $query = qq|SELECT d.weightunit, current_date AS priceupdate,
- d.curr AS currencies,
+ # FIXME left joins not working
+ $query = qq|SELECT (SELECT value FROM defaults
+ WHERE setting_key = 'weightunit') AS weightunit,
+ current_date AS priceupdate,
+ (SELECT value FROM defaults WHERE setting_key = 'curr')
+ AS currencies,
c1.accno AS inventory_accno, c1.description AS inventory_description,
c2.accno AS income_accno, c2.description AS income_description,
c3.accno AS expense_accno, c3.description AS expense_description
- FROM defaults d
- LEFT JOIN chart c1 ON (d.inventory_accno_id = c1.id)
- LEFT JOIN chart c2 ON (d.income_accno_id = c2.id)
- LEFT JOIN chart c3 ON (d.expense_accno_id = c3.id)|;
+ FROM chart c1, chart c2, chartc3
+ WHERE c1.id IN (SELECT value FROM defaults
+ WHERE setting_key = 'inventory_accno_id')
+ AND c2.id IN (SELECT value FROM defaults
+ WHERE setting_key = 'income_accno_id')
+ AND c3.id IN (SELECT value FROM defaults
+ WHERE setting_key = 'expense_accno_id')|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
diff --git a/LedgerSMB/IR.pm b/LedgerSMB/IR.pm
index 60c8abe3..2f794824 100755
--- a/LedgerSMB/IR.pm
+++ b/LedgerSMB/IR.pm
@@ -72,7 +72,13 @@ sub post_invoice {
($null, $form->{department_id}) = split(/--/, $form->{department});
$form->{department_id} *= 1;
- $query = qq|SELECT fxgain_accno_id, fxloss_accno_id FROM defaults d|;
+ $query = qq|
+ SELECT (SELECT value FROM defaults
+ WHERE setting_key = 'fxgain_accno_id')
+ AS fxgain_accno_id,
+ (SELECT value FROM defaults
+ WHERE setting_key = 'fxloss_accno_id')
+ AS fxloss_accno_id|;
my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
$query = qq|
@@ -976,41 +982,71 @@ sub retrieve_invoice {
if ($form->{id}) {
# get default accounts and last invoice number
$query = qq|
- SELECT (SELECT c.accno FROM chart c
- WHERE d.inventory_accno_id = c.id)
+ SELECT (select c.accno FROM chart c
+ WHERE c.id = (SELECT value FROM defaults
+ WHERE setting_key =
+ 'inventory_accno_id'))
AS inventory_accno,
+
(SELECT c.accno FROM chart c
- WHERE d.income_accno_id = c.id)
+ WHERE c.id = (SELECT value FROM defaults
+ WHERE setting_key =
+ 'income_accno_id'))
AS income_accno,
+
(SELECT c.accno FROM chart c
- WHERE d.expense_accno_id = c.id)
+ WHERE c.id = (SELECT value FROM defaults
+ WHERE setting_key =
+ 'expense_accno_id'))
AS expense_accno,
+
(SELECT c.accno FROM chart c
- WHERE d.fxgain_accno_id = c.id)
+ WHERE c.id = (SELECT value FROM defaults
+ WHERE setting_key =
+ 'fxgain_accno_id'))
AS fxgain_accno,
+
(SELECT c.accno FROM chart c
- WHERE d.fxloss_accno_id = c.id)
- AS fxloss_accno, d.curr AS currencies
- FROM defaults d|;
+ WHERE c.id = (SELECT value FROM defaults
+ WHERE setting_key =
+ 'fxloss_accno_id'))
+ AS fxloss_accno,
+ (SELECT value FROM defaults
+ WHERE setting_key = 'curr') AS currencies|;
} else {
$query = qq|
- SELECT (SELECT c.accno FROM chart c
- WHERE d.inventory_accno_id = c.id)
+ SELECT (select c.accno FROM chart c
+ WHERE c.id = (SELECT value FROM defaults
+ WHERE setting_key =
+ 'inventory_accno_id'))
AS inventory_accno,
+
(SELECT c.accno FROM chart c
- WHERE d.income_accno_id = c.id)
+ WHERE c.id = (SELECT value FROM defaults
+ WHERE setting_key =
+ 'income_accno_id'))
AS income_accno,
+
(SELECT c.accno FROM chart c
- WHERE d.expense_accno_id = c.id)
+ WHERE c.id = (SELECT value FROM defaults
+ WHERE setting_key =
+ 'expense_accno_id'))
AS expense_accno,
+
(SELECT c.accno FROM chart c
- WHERE d.fxgain_accno_id = c.id)
+ WHERE c.id = (SELECT value FROM defaults
+ WHERE setting_key =
+ 'fxgain_accno_id'))
AS fxgain_accno,
+
(SELECT c.accno FROM chart c
- WHERE d.fxloss_accno_id = c.id)
- AS fxloss_accno, d.curr AS currencies,
- current_date AS transdate
- FROM defaults d|;
+ WHERE c.id = (SELECT value FROM defaults
+ WHERE setting_key =
+ 'fxloss_accno_id'))
+ AS fxloss_accno,
+ (SELECT value FROM defaults
+ WHERE setting_key = 'curr') AS currencies,
+ current_date AS transdate|;
}
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
@@ -1259,7 +1295,9 @@ sub exchangerate_defaults {
my $var;
# get default currencies
- my $query = qq|SELECT substr(curr,1,3), curr FROM defaults|;
+ my $query = qq|
+ SELECT substr(value,1,3), value FROM defaults
+ WHERE setting_key = 'curr'|;
my $eth = $dbh->prepare($query) || $form->dberror($query);
$eth->execute;
($form->{defaultcurrency}, $form->{currencies}) = $eth->fetchrow_array;
diff --git a/LedgerSMB/IS.pm b/LedgerSMB/IS.pm
index 74b35352..05b4dd06 100755
--- a/LedgerSMB/IS.pm
+++ b/LedgerSMB/IS.pm
@@ -48,8 +48,9 @@ sub invoice_details {
my $query = qq|
SELECT ?::date - ?::date
- AS terms, weightunit
- FROM defaults|;
+ AS terms, value
+ FROM defaults
+ WHERE setting_key = 'weightunit'/|;
my $sth = $dbh->prepare($query);
$sth->execute($form->{duedate}, $form->{transdate})
|| $form->dberror($query);
@@ -817,7 +818,13 @@ sub post_invoice {
($null, $form->{department_id}) = split(/--/, $form->{department});
$form->{department_id} *= 1;
- $query = qq|SELECT fxgain_accno_id, fxloss_accno_id FROM defaults|;
+ $query = qq|
+ SELECT (SELECT value FROM defaults
+ WHERE setting_key = fxgain_accno_id)
+ AS fxgain_accno_id,
+ (SELECT value FROM defaults
+ WHERE setting_key = fxloss_accno_id)
+ AS fxloss_accno_id|;
my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
$query = qq|
@@ -1715,11 +1722,14 @@ sub retrieve_invoice {
if ($form->{id}) {
# get default accounts and last invoice number
- $query = qq|SELECT d.curr AS currencies FROM defaults d|;
+ $query = qq|
+ SELECT value AS currencies FROM defaults
+ WHERE setting_key = 'curr'|;
} else {
$query = qq|
- SELECT d.curr AS currencies, current_date AS transdate
- FROM defaults d|;
+ SELECT value AS currencies, current_date AS transdate
+ FROM defaults
+ WHERE setting_key = 'curr'|;
}
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
@@ -1979,7 +1989,9 @@ sub exchangerate_defaults {
my $var;
# get default currencies
- my $query = qq|SELECT substr(curr,1,3), curr FROM defaults|;
+ my $query = qq|
+ SELECT substr(value,1,3), value FROM defaults
+ WHERE setting_key = 'curr'|;
my $eth = $dbh->prepare($query) || $form->dberror($query);
$eth->execute;
($form->{defaultcurrency}, $form->{currencies}) = $eth->fetchrow_array;
diff --git a/LedgerSMB/JC.pm b/LedgerSMB/JC.pm
index c8974818..fcac1b1e 100755
--- a/LedgerSMB/JC.pm
+++ b/LedgerSMB/JC.pm
@@ -45,7 +45,7 @@ sub get_jcitems {
# connect to database
my $dbh = $form->dbconnect($myconfig);
- my $query = qq|SELECT current_date FROM defaults|;
+ my $query = qq|SELECT current_date|;
($form->{transdate}) = $dbh->selectrow_array($query);
($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
diff --git a/LedgerSMB/OE.pm b/LedgerSMB/OE.pm
index 1a51a6cf..0992b00e 100755
--- a/LedgerSMB/OE.pm
+++ b/LedgerSMB/OE.pm
@@ -776,7 +776,9 @@ sub retrieve {
my $var;
my $ref;
- $query = qq|SELECT curr, current_date FROM defaults|;
+ $query = qq|
+ SELECT value, current_date FROM defaults
+ WHERE setting_key = 'curr'|;
($form->{currencies}, $form->{transdate}) =
$dbh->selectrow_array($query);
@@ -952,7 +954,9 @@ sub exchangerate_defaults {
my $buysell = ($form->{vc} eq "customer") ? "buy" : "sell";
# get default currencies
- my $query = qq|SELECT substr(curr,1,3), curr FROM defaults|;
+ my $query = qq|
+ SELECT substr(value,1,3), value FROM defaults
+ WHERE setting_key = 'curr'|;
($form->{defaultcurrency}, $form->{currencies})
= $dbh->selectrow_array($query);
@@ -1568,7 +1572,9 @@ sub order_details {
$form->format_string(qw(text_amount text_decimal));
- $query = qq|SELECT weightunit FROM defaults|;
+ $query = qq|
+ SELECT value FROM defaults
+ WHERE setting_key = 'weightunit'|;
($form->{weightunit}) = $dbh->selectrow_array($query);
$dbh->commit;
@@ -2193,7 +2199,7 @@ sub get_soparts {
}
- $query = qq|SELECT current_date FROM defaults|;
+ $query = qq|SELECT current_date|;
($form->{transdate}) = $dbh->selectrow_array($query);
# foreign exchange rates
diff --git a/LedgerSMB/PE.pm b/LedgerSMB/PE.pm
index 604e62e7..02b7b59d 100755
--- a/LedgerSMB/PE.pm
+++ b/LedgerSMB/PE.pm
@@ -283,7 +283,7 @@ sub list_stock {
}
$sth->finish;
- $query = qq|SELECT current_date FROM defaults|;
+ $query = qq|SELECT current_date|;
($form->{stockingdate}) = $dbh->selectrow_array($query) if !$form->{stockingdate};
$dbh->disconnect;
@@ -378,8 +378,7 @@ sub get_job {
my $ref;
if ($form->{id}) {
- $query = qq|SELECT weightunit
- FROM defaults|;
+ $query = qq|SELECT value FROM defaults WHERE setting_key = 'weightunit'|;
($form->{weightunit}) = $dbh->selectrow_array($query);
$query = qq|SELECT pr.*,
@@ -396,7 +395,9 @@ sub get_job {
LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
WHERE pr.id = $form->{id}|;
} else {
- $query = qq|SELECT weightunit, current_date AS startdate FROM defaults|;
+ $query = qq|
+ SELECT value, current_date AS startdate FROM defaults
+ WHERE setting_key = 'weightunit'|;
}
$sth = $dbh->prepare($query);
@@ -491,7 +492,7 @@ sub get_customer {
my $ref;
if (! $form->{startdate}) {
- $query = qq|SELECT current_date FROM defaults|;
+ $query = qq|SELECT current_date|;
($form->{startdate}) = $dbh->selectrow_array($query);
}
@@ -654,7 +655,7 @@ sub stock_assembly {
my $rvh = $dbh->prepare($query) || $form->dberror($query);
if (! $form->{stockingdate}) {
- $query = qq|SELECT current_date FROM defaults|;
+ $query = qq|SELECT current_date|;
($form->{stockingdate}) = $dbh->selectrow_array($query);
}
@@ -1375,7 +1376,7 @@ sub project_sales_order {
# connect to database
my $dbh = $form->dbconnect($myconfig);
- my $query = qq|SELECT current_date FROM defaults|;
+ my $query = qq|SELECT current_date|;
my ($transdate) = $dbh->selectrow_array($query);
$form->all_years($myconfig, $dbh);
@@ -1468,8 +1469,7 @@ sub get_jcitems {
$sth->finish;
- $query = qq|SELECT curr
- FROM defaults|;
+ $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
($form->{currency}) = $dbh->selectrow_array($query);
$form->{currency} =~ s/:.*//;
$form->{defaultcurrency} = $form->{currency};
diff --git a/LedgerSMB/RP.pm b/LedgerSMB/RP.pm
index b1cd54d7..fa036bc8 100755
--- a/LedgerSMB/RP.pm
+++ b/LedgerSMB/RP.pm
@@ -572,7 +572,7 @@ sub get_accounts {
$sth->finish;
if ($form->{method} eq 'cash' && !$todate) {
- ($todate) = $dbh->selectrow_array(qq|SELECT current_date FROM defaults|);
+ ($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
}
if ($fromdate) {
@@ -1353,13 +1353,13 @@ sub aging {
my $dbh = $form->dbconnect($myconfig);
my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir';
- my $query = qq|SELECT curr FROM defaults|;
+ my $query = qq|SELECT value FROM defaults WHERE settings_key = 'curr'|;
($form->{currencies}) = $dbh->selectrow_array($query);
($null, $form->{todate}) = $form->from_to($form->{year}, $form->{month}) if $form->{year} && $form->{month};
if (! $form->{todate}) {
- $query = qq|SELECT current_date FROM defaults|;
+ $query = qq|SELECT current_date|;
($form->{todate}) = $dbh->selectrow_array($query);
}
@@ -1721,7 +1721,7 @@ sub tax_report {
my $todate = $form->{todate};
if (! $todate) {
- ($todate) = $dbh->selectrow_array(qq|SELECT current_date FROM defaults|);
+ ($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
}
$cashwhere = qq|
diff --git a/LedgerSMB/Session/DB.pm b/LedgerSMB/Session/DB.pm
index 0c8dda45..7b59a718 100755
--- a/LedgerSMB/Session/DB.pm
+++ b/LedgerSMB/Session/DB.pm
@@ -52,7 +52,8 @@ sub session_check {
$timeout = "$myconfig{timeout} seconds";
}
- $checkQuery->execute($sessionid, $token, $timeout) || $form->dberror('Looking for session: ');
+ $checkQuery->execute($sessionid, $token, $timeout)
+ || $form->dberror('Looking for session: ');
my $sessionValid = $checkQuery->rows;
if($sessionValid){
diff --git a/LedgerSMB/User.pm b/LedgerSMB/User.pm
index 166a3d3c..a16cf7d5 100755
--- a/LedgerSMB/User.pm
+++ b/LedgerSMB/User.pm
@@ -131,7 +131,9 @@ sub login {
or $self->error($DBI::errstr);
# we got a connection, check the version
- my $query = qq|SELECT version FROM defaults|;
+ my $query = qq|
+ SELECT value FROM defaults
+ WHERE setting_key = 'version'|;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
@@ -228,13 +230,8 @@ sub dbconnect_vars {
$form->{dboptions} = $dboptions{$form->{dbdriver}}{$form->{dateformat}};
- if ($form->{dbdriver} =~ /Pg/) {
- $form->{dbconnect} = "dbi:$form->{dbdriver}:dbname=$db";
- }
+ $form->{dbconnect} = "dbi:$form->{dbdriver}:dbname=$db";
- if ($form->{dbdriver} eq 'Oracle') {
- $form->{dbconnect} = "dbi:Oracle:sid=$form->{sid}";
- }
if ($form->{dbhost}) {
$form->{dbconnect} .= ";host=$form->{dbhost}";
@@ -411,7 +408,12 @@ sub process_query {
return unless (-f $filename);
open(FH, "$filename") or $form->error("$filename : $!\n");
+ $ENV{PGPASSWORD} = $form->{dbpasswd};
+ $ENV{PGUSER} = $form->{dbuser};
+ $ENV{PGDATABASE} = $form->{db};
+
open(PSQL, "| psql") or $form->error("psql : $! \n");
+ print PSQL "\\o spool/log \n";
while (<FH>){
print PSQL $_;
}
@@ -516,7 +518,9 @@ sub dbneedsupdate {
$sth->execute || $form->dberror($query);
if ($sth->fetchrow_array) {
- $query = qq|SELECT version FROM defaults|;
+ $query = qq|
+ SELECT value FROM defaults
+ WHERE setting_key = 'version'|;
my $sth = $dbh->prepare($query);
$sth->execute;
@@ -574,7 +578,9 @@ sub dbupdate {
or $form->dberror;
# check version
- $query = qq|SELECT version FROM defaults|;
+ $query = qq|
+ SELECT value FROM defaults
+ WHERE setting_key = 'version'|;
my $sth = $dbh->prepare($query);
# no error check, let it fall through
$sth->execute;
diff --git a/README.svn-status b/README.svn-status
index bfdb8a15..3f2886b1 100644
--- a/README.svn-status
+++ b/README.svn-status
@@ -1,4 +1,3 @@
-Dataset creation is temporarily broken because of changes to the defaults table structure that are not found in the chart.sql files or the application. Will
-fix ASAP.
+Dataset creation is now fixed (and mandatory). Only Default and Canadian English (General) COA's currently work.
Chris T
diff --git a/bin/admin.pl b/bin/admin.pl
index 74f3f6cf..4913b9b3 100755
--- a/bin/admin.pl
+++ b/bin/admin.pl
@@ -880,7 +880,8 @@ sub delete {
$form->{templates} = ($form->{templates}) ? "${LedgerSMB::Sysconfig::templates}/$form->{templates}" : "$templates/$form->{login}";
- $form->error($locale->text("[_1] locked!", ${LedgerSMB::Sysconfig::memberfile}) if (-f ${memberfile}.LCK);
+ $form->error($locale->text("[_1] locked!",
+ ${LedgerSMB::Sysconfig::memberfile})) if (-f ${memberfile}.LCK);
open(FH, ">${memberfile}.LCK") or $form->error("${memberfile}.LCK : $!");
close(FH);
diff --git a/sql/Canada-English_General-chart.sql b/sql/Canada-English_General-chart.sql
index d17caa94..99946c9c 100755
--- a/sql/Canada-English_General-chart.sql
+++ b/sql/Canada-English_General-chart.sql
@@ -66,5 +66,17 @@ insert into chart (accno,description,charttype,gifi_accno,category,link) values
insert into tax (chart_id,rate) values ((select id from chart where accno = '2310'),0.06);
insert into tax (chart_id,rate) values ((select id from chart where accno = '2320'),0.08);
--
-update defaults set inventory_accno_id = (select id from chart where accno = '1520'), income_accno_id = (select id from chart where accno = '4020'), expense_accno_id = (select id from chart where accno = '5010'), fxgain_accno_id = (select id from chart where accno = '4450'), fxloss_accno_id = (select id from chart where accno = '4450'), curr = 'CAD:USD:EUR', weightunit = 'kg';
+
+INSERT INTO defaults (setting_key, value) values ('inventory_accno_id',
+ (select id from chart where accno = '1520'));
+INSERT INTO defaults (setting_key, value) values ('income_accno_id',
+ (select id from chart where accno = '4020'));
+INSERT INTO defaults (setting_key, value) values ('expense_accno_id',
+ (select id from chart where accno = '5010'));
+INSERT INTO defaults (setting_key, value) values ('fxgain_accno_id',
+ (select id from chart where accno = '4450'));
+INSERT INTO defaults (setting_key, value) values ('fxloss_accno_id',
+ (select id from chart where accno = '4450'));
+INSERT INTO defaults (setting_key, value) values ('curr', 'CAD:USD:EUR');
+INSERT INTO defaults (setting_key, value) values ('weightunit', 'kg');
--
diff --git a/sql/Default-chart.sql b/sql/Default-chart.sql
index 3d222463..c0f103b6 100755
--- a/sql/Default-chart.sql
+++ b/sql/Default-chart.sql
@@ -73,5 +73,17 @@ insert into tax (chart_id,rate) values ((select id from chart where accno = '231
insert into tax (chart_id,rate) values ((select id from chart where accno = '2320'),0.14);
insert into tax (chart_id,rate) values ((select id from chart where accno = '2330'),0.3);
--
-update defaults set inventory_accno_id = (select id from chart where accno = '1520'), income_accno_id = (select id from chart where accno = '4020'), expense_accno_id = (select id from chart where accno = '5010'), fxgain_accno_id = (select id from chart where accno = '4450'), fxloss_accno_id = (select id from chart where accno = '5810'), curr = 'USD:CAD:EUR', weightunit = 'kg';
---
+insert into defaults (setting_key, value)
+VALUES ('inventory_accno_id', (select id from chart where accno = '1520'));
+INSERT INTO defaults (setting_key, value)
+VALUES ('income_accno_id', (select id from chart where accno = '4020'));
+INSERT INTO defaults (setting_key, value)
+VALUES ('expense_accno_id', (select id from chart where accno = '5010'));
+INSERT INTO defaults (setting_key, value)
+VALUES ('fxgain_accno_id', (select id from chart where accno = '4450'));
+INSERT INTO defaults (setting_key, value)
+VALUES ('fxloss_accno_id', (select id from chart where accno = '5810'));
+INSERT INTO defaults (setting_key, value)
+VALUES ('curr', 'USD:CAD:EUR');
+INSERT INTO defaults (setting_key, value)
+VALUES ('weightunit', 'kg');
diff --git a/sql/Pg-functions.sql b/sql/Pg-functions.sql
index 26d23473..b42e2083 100755
--- a/sql/Pg-functions.sql
+++ b/sql/Pg-functions.sql
@@ -1,3 +1,4 @@
+CREATE LANGUAGE plpgsql;
--
CREATE FUNCTION del_yearend() RETURNS OPAQUE AS '
begin
diff --git a/sql/Pg-tables.sql b/sql/Pg-tables.sql
index d490110f..92dfbf26 100755
--- a/sql/Pg-tables.sql
+++ b/sql/Pg-tables.sql
@@ -59,27 +59,27 @@ CREATE TABLE defaults (
expense_accno_id int,
fxgain_accno_id int,
fxloss_accno_id int,
- sinumber text,
- sonumber text,
- yearend varchar(5),
- weightunit varchar(5),
- businessnumber text,
- version varchar(8) PRIMARY KEY,
- curr text,
- closedto date,
- revtrans bool DEFAULT 't',
- ponumber text,
- sqnumber text,
- rfqnumber text,
- audittrail bool default 'f',
- vinumber text,
- employeenumber text,
- partnumber text,
- customernumber text,
- vendornumber text,
- glnumber text,
- projectnumber text
-);
+*/
+\COPY defaults FROM stdin WITH DELIMITER |
+sinumber|1
+sonumber|1
+yearend|1
+businessnumber|1
+version|1.2.0
+closedto|\N
+revtrans|1
+ponumber|1
+sqnumber|1
+rfqnumber|1
+audittrail|0
+vinumber|1
+employeenumber|1
+partnumber|1
+customernumber|1
+vendornumber|1
+glnumber|1
+projectnumber|1
+\.
-- */
CREATE TABLE acc_trans (
trans_id int,
@@ -565,17 +565,6 @@ CREATE TABLE jcitems (
notes text
);
--- Session tracking table
-
-
-CREATE TABLE session(
-session_id serial PRIMARY KEY,
-sl_login VARCHAR(50),
-token VARCHAR(32) CHECK(length(token) = 32),
-last_used TIMESTAMP default now(),
-users_id INTEGER NOT NULL references users(id)
-);
-
insert into transactions (id, table_name) SELECT id, 'ap' FROM ap;
@@ -708,7 +697,6 @@ field_id SERIAL PRIMARY KEY,
table_id INT REFERENCES custom_table_catalog,
field_name TEXT
);
-INSERT INTO defaults (version) VALUES ('2.6.18');
INSERT INTO taxmodule (
taxmodule_id, taxmodulename
@@ -718,7 +706,8 @@ INSERT INTO taxmodule (
-- USERS stuff --
CREATE TABLE users (id serial UNIQUE, username varchar(30) primary key);
-COMMENT ON TABLE users IS 'username is the actual primary key here because we don't want duplicate users';
+COMMENT ON TABLE users IS
+$$username is the actual primary key here because we don't want duplicate users$$;
CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred,
acs text,
address text,
@@ -778,3 +767,14 @@ CREATE FUNCTION update_user(int4,text) RETURNS int4 AS $$
COMMENT ON FUNCTION update_user(int4,text) IS $$ Takes int4 which is users.id and text which is username. Will update username based on id. Username is unique $$;
+-- Session tracking table
+
+
+CREATE TABLE session(
+session_id serial PRIMARY KEY,
+sl_login VARCHAR(50),
+token VARCHAR(32) CHECK(length(token) = 32),
+last_used TIMESTAMP default now(),
+users_id INTEGER -- NOT NULL references users(id)
+);
+
generated by cgit v1.2.3 (git 2.46.0) at 2024-11-04 21:13:16 +0000