[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [413] trunk
- Subject: SF.net SVN: ledger-smb: [413] trunk
- From: ..hidden..
- Date: Mon, 30 Oct 2006 19:30:52 -0800
Revision: 413
http://svn.sourceforge.net/ledger-smb/?rev=413&view=rev
Author: einhverfr
Date: 2006-10-30 19:30:39 -0800 (Mon, 30 Oct 2006)
Log Message:
-----------
Defaults table revision. Most COA's and System->defaults are known to be broken.
Modified Paths:
--------------
trunk/Changelog
trunk/LedgerSMB/AA.pm
trunk/LedgerSMB/AM.pm
trunk/LedgerSMB/CP.pm
trunk/LedgerSMB/CT.pm
trunk/LedgerSMB/Form.pm
trunk/LedgerSMB/HR.pm
trunk/LedgerSMB/IC.pm
trunk/LedgerSMB/IR.pm
trunk/LedgerSMB/IS.pm
trunk/LedgerSMB/JC.pm
trunk/LedgerSMB/OE.pm
trunk/LedgerSMB/PE.pm
trunk/LedgerSMB/RP.pm
trunk/LedgerSMB/Session/DB.pm
trunk/LedgerSMB/User.pm
trunk/README.svn-status
trunk/bin/admin.pl
trunk/sql/Canada-English_General-chart.sql
trunk/sql/Default-chart.sql
trunk/sql/Pg-functions.sql
trunk/sql/Pg-tables.sql
Modified: trunk/Changelog
===================================================================
--- trunk/Changelog 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/Changelog 2006-10-31 03:30:39 UTC (rev 413)
@@ -4,6 +4,7 @@
* 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)
Modified: trunk/LedgerSMB/AA.pm
===================================================================
--- trunk/LedgerSMB/AA.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/AA.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -213,8 +213,10 @@
$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);
Modified: trunk/LedgerSMB/AM.pm
===================================================================
--- trunk/LedgerSMB/AM.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/AM.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -59,9 +59,21 @@
# 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 @@
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 @@
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 @@
$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 @@
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 @@
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,25 +1686,21 @@
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'|;
+ 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, $_);
}
- $query .= qq|, closedto = |.$dbh->quote($form->{closedto});
- if ($form->{audittrail}) {
- $query .= qq|, audittrail = '1'|;
- } else {
- $query .= qq|, audittrail = '0'|;
- }
-
- # set close in defaults
- $dbh->do($query) || $form->dberror($query);
-
if ($form->{removeaudittrail}) {
$query = qq|
DELETE FROM audittrail
Modified: trunk/LedgerSMB/CP.pm
===================================================================
--- trunk/LedgerSMB/CP.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/CP.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -88,8 +88,12 @@
$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 @@
$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 @@
$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);
Modified: trunk/LedgerSMB/CT.pm
===================================================================
--- trunk/LedgerSMB/CT.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/CT.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -111,7 +111,7 @@
($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 @@
$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 @@
$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
Modified: trunk/LedgerSMB/Form.pm
===================================================================
--- trunk/LedgerSMB/Form.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/Form.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -68,7 +68,7 @@
$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 @@
$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 @@
$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 @@
}
$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 @@
$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,9 +2646,9 @@
$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 @@
$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 @@
$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 @@
}
}
- $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 @@
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 @@
} 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|";
Modified: trunk/LedgerSMB/HR.pm
===================================================================
--- trunk/LedgerSMB/HR.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/HR.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -93,7 +93,7 @@
} else {
- $query = qq|SELECT current_date FROM defaults|;
+ $query = qq|SELECT current_date|;
($form->{startdate}) = $dbh->selectrow_array($query);
}
Modified: trunk/LedgerSMB/IC.pm
===================================================================
--- trunk/LedgerSMB/IC.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/IC.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -953,7 +953,7 @@
my @a = qw(partnumber description);
my $sortorder = $form->sort_order(..hidden.., \%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 @@
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);
Modified: trunk/LedgerSMB/IR.pm
===================================================================
--- trunk/LedgerSMB/IR.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/IR.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -72,7 +72,13 @@
($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 @@
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 @@
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;
Modified: trunk/LedgerSMB/IS.pm
===================================================================
--- trunk/LedgerSMB/IS.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/IS.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -48,8 +48,9 @@
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 @@
($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 @@
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 @@
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;
Modified: trunk/LedgerSMB/JC.pm
===================================================================
--- trunk/LedgerSMB/JC.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/JC.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -45,7 +45,7 @@
# 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);
Modified: trunk/LedgerSMB/OE.pm
===================================================================
--- trunk/LedgerSMB/OE.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/OE.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -776,7 +776,9 @@
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 @@
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 @@
$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 @@
}
- $query = qq|SELECT current_date FROM defaults|;
+ $query = qq|SELECT current_date|;
($form->{transdate}) = $dbh->selectrow_array($query);
# foreign exchange rates
Modified: trunk/LedgerSMB/PE.pm
===================================================================
--- trunk/LedgerSMB/PE.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/PE.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -283,7 +283,7 @@
}
$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 @@
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 @@
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 @@
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 @@
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 @@
# 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 @@
$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};
Modified: trunk/LedgerSMB/RP.pm
===================================================================
--- trunk/LedgerSMB/RP.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/RP.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -572,7 +572,7 @@
$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 @@
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 @@
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|
Modified: trunk/LedgerSMB/Session/DB.pm
===================================================================
--- trunk/LedgerSMB/Session/DB.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/Session/DB.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -52,7 +52,8 @@
$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){
Modified: trunk/LedgerSMB/User.pm
===================================================================
--- trunk/LedgerSMB/User.pm 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/LedgerSMB/User.pm 2006-10-31 03:30:39 UTC (rev 413)
@@ -131,7 +131,9 @@
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 @@
$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 @@
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 @@
$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 @@
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;
Modified: trunk/README.svn-status
===================================================================
--- trunk/README.svn-status 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/README.svn-status 2006-10-31 03:30:39 UTC (rev 413)
@@ -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
Modified: trunk/bin/admin.pl
===================================================================
--- trunk/bin/admin.pl 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/bin/admin.pl 2006-10-31 03:30:39 UTC (rev 413)
@@ -880,7 +880,8 @@
$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);
Modified: trunk/sql/Canada-English_General-chart.sql
===================================================================
--- trunk/sql/Canada-English_General-chart.sql 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/sql/Canada-English_General-chart.sql 2006-10-31 03:30:39 UTC (rev 413)
@@ -66,5 +66,17 @@
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');
--
Modified: trunk/sql/Default-chart.sql
===================================================================
--- trunk/sql/Default-chart.sql 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/sql/Default-chart.sql 2006-10-31 03:30:39 UTC (rev 413)
@@ -73,5 +73,17 @@
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');
Modified: trunk/sql/Pg-functions.sql
===================================================================
--- trunk/sql/Pg-functions.sql 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/sql/Pg-functions.sql 2006-10-31 03:30:39 UTC (rev 413)
@@ -1,3 +1,4 @@
+CREATE LANGUAGE plpgsql;
--
CREATE FUNCTION del_yearend() RETURNS OPAQUE AS '
begin
Modified: trunk/sql/Pg-tables.sql
===================================================================
--- trunk/sql/Pg-tables.sql 2006-10-31 02:03:06 UTC (rev 412)
+++ trunk/sql/Pg-tables.sql 2006-10-31 03:30:39 UTC (rev 413)
@@ -59,27 +59,27 @@
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,18 +565,7 @@
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;
CREATE RULE ap_id_track_i AS ON insert TO ap
@@ -708,7 +697,6 @@
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 @@
-- 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 @@
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)
+);
+
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.