summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--LedgerSMB/Admin.pm42
-rw-r--r--LedgerSMB/DBObject/Reconciliation.pm25
-rw-r--r--LedgerSMB/DBObject/User.pm20
-rw-r--r--UI/Admin/main.html2
-rw-r--r--UI/reconciliation/search.html50
-rw-r--r--UI/reconciliation/upload.html5
-rw-r--r--scripts/admin.pl39
-rw-r--r--scripts/payment.pl2
-rw-r--r--scripts/recon.pl26
-rw-r--r--sql/modules/Reconciliaton.sql55
-rw-r--r--sql/modules/admin.sql25
11 files changed, 197 insertions, 94 deletions
diff --git a/LedgerSMB/Admin.pm b/LedgerSMB/Admin.pm
index 8e6c5dfd..34acecb3 100644
--- a/LedgerSMB/Admin.pm
+++ b/LedgerSMB/Admin.pm
@@ -10,7 +10,7 @@ sub save_user {
my $self = shift @_;
- my $entity_id = shift @{ $self->exec_method( procname => "save_user" ) };
+ my $entity_id = shift @{ $self->exec_method( funcname => "save_user" ) };
$self->merge($entity_id);
my $employee = LedgerSMB::DBObject::Employee->new(base=>$self, copy=>'list',
@@ -52,8 +52,8 @@ sub save_user {
$contact->save_workphone(person=>$employee);
$contact->save_email(person=>$employee);
- my $roles = $self->exec_method( procname => "all_roles" );
- my $user_roles = $self->exec_method(procname => "get_user_roles", args=>[ $self->{ modifying_user } ] );
+ my $roles = $self->exec_method( funcname => "all_roles" );
+ my $user_roles = $self->exec_method(funcname => "get_user_roles", args=>[ $self->{ modifying_user } ] );
my %active_roles;
for my $role (@{$user_roles}) {
@@ -76,13 +76,13 @@ sub save_user {
elsif ($active_roles{$role} && !($self->{incoming_roles}->{$role} )) {
# do remove function
- $status = $self->exec_method(procname => "remove_user_from_role",
+ $status = $self->exec_method(funcname => "remove_user_from_role",
args=>[ $self->{ modifying_user }, $role ]
}
elsif ($self->{incoming_roles}->{$role} and !($active_roles{$role} )) {
# do add function
- $status = $self->exec_method(procname => "add_user_to_role",
+ $status = $self->exec_method(funcname => "add_user_to_role",
args=>[ $self->{ modifying_user }, $role ]
);
}
@@ -93,14 +93,14 @@ sub save_group {
my $self = shift @_;
- my $existant = shift @{ $self->exec_method (procname=> "is_group", args=>[$self->{modifying_group}]) };
+ my $existant = shift @{ $self->exec_method (funcname=> "is_group", args=>[$self->{modifying_group}]) };
- my $group = shift @{ $self->exec_method (procname=> "save_group") };
+ my $group = shift @{ $self->exec_method (funcname=> "save_group") };
# first we grab all roles
- my $roles = $self->exec_method( procname => "all_roles" );
- my $user_roles = $self->exec_method(procname => "get_user_roles",
+ my $roles = $self->exec_method( funcname => "all_roles" );
+ my $user_roles = $self->exec_method(funcname => "get_user_roles",
args=>[ $self->{ group_name } ]
);
@@ -126,7 +126,7 @@ sub save_group {
# do remove function
$status = $self->exec_method(
- procname => "remove_group_from_role",
+ funcname => "remove_group_from_role",
args=>[ $self->{ modifying_user }, $role ]
);
}
@@ -134,7 +134,7 @@ sub save_group {
# do add function
$status = $self->exec_method(
- procname => "add_group_to_role",
+ funcname => "add_group_to_role",
args=>[ $self->{ modifying_user }, $role ]
);
}
@@ -146,7 +146,7 @@ sub delete_user {
my $self = shift @_;
- my $status = shift @{ $self->exec_method(procname=>'delete_user', args=>[$self->{modifying_user}]) };
+ my $status = shift @{ $self->exec_method(funcname=>'delete_user', args=>[$self->{modifying_user}]) };
if ($status) {
@@ -163,7 +163,7 @@ sub delete_group {
my $self = shift @_;
- my $status = shift @{ $self->exec_method(procname=>'delete_group', args=>[$self->{groupname}])};
+ my $status = shift @{ $self->exec_method(funcname=>'delete_group', args=>[$self->{groupname}])};
if ($status) {
@@ -176,4 +176,20 @@ sub delete_group {
}
}
+sub get_entire_user {
+
+ my $self = shift @_;
+ my $id = shift @_;
+ my $user = LedgerSMB::DBObject::User->new(base=>$self,copy=>'all');
+ $user->get($id);
+
+}
+
+sub get_roles {
+
+ my $self = shift @_;
+
+ return $self->exec_method(funcname=>'get_roles',args=>[$self->{company}]);
+}
+
1; \ No newline at end of file
diff --git a/LedgerSMB/DBObject/Reconciliation.pm b/LedgerSMB/DBObject/Reconciliation.pm
index 7a7ba313..1b5e9b97 100644
--- a/LedgerSMB/DBObject/Reconciliation.pm
+++ b/LedgerSMB/DBObject/Reconciliation.pm
@@ -160,15 +160,24 @@ sub new_report {
# 0 is success
# 1 is found, but mismatch
# 2 is not found
+
+ # in_scn INT,
+ #in_amount INT,
+ #in_account INT,
+ #in_user TEXT,
+ #in_date TIMESTAMP
$code = $self->exec_method(
funcname=>'reconciliation__add_entry',
args=>[
$report_id,
+ $entry->{scn},
+ $entry->{amount}, # needs leading 0's trimmed.
+ $entry->{account},
+ $self->{user},
+ $self->{date}
]
);
- $entry{report_id} = $report_id;
- $entry{code} = $self->add_entry( $entry );
-
+ $entry{report_id} = $report_id;
}
$self->exec_method(funcname=>'reconciliation__pending_transactions', args=>[$report_id, $date]);
@@ -221,7 +230,7 @@ sub entry {
sub search {
my $self = shift @_;
-
+ my $type = shift;
return $self->exec_method(
funcname=>'reconciliation__search',
args=>[$self->{date_begin}, $self->{date_end}, $self->{account}, $self->{status}]
@@ -246,4 +255,12 @@ sub get_report_list {
args=>[$self->{account},$self->{report}]
);
}
+
+sub get_accounts {
+
+ my $self = shift @_;
+ return $self->exec_method(
+ funcname=>'reconciliation__account_list',
+ );
+}
1; \ No newline at end of file
diff --git a/LedgerSMB/DBObject/User.pm b/LedgerSMB/DBObject/User.pm
index c84c6336..b09951a9 100644
--- a/LedgerSMB/DBObject/User.pm
+++ b/LedgerSMB/DBObject/User.pm
@@ -1,6 +1,7 @@
package LedgerSMB::DBObject::User;
use base qw/LedgerSMB::DBObject/;
+use Data::Dumper;
sub save {
@@ -11,7 +12,7 @@ sub save {
if ( $user->{id} && $self->{is_a_user} ) {
# doesn't check for the password - that's done in the sproc.
- $self->{id} = shift @{ $self->exec_method(procname=>'admin__save_user',
+ $self->{id} = shift @{ $self->exec_method(funcname=>'admin__save_user',
args=>[$user->{id}, $self->{username}, $self->{password}] ) };
if (!$self->{id}) {
@@ -29,7 +30,7 @@ sub save {
elsif ($self->{is_a_user}) {
# No user ID, meaning, creating a new one.
- $self->{id} = shift @{ $self->exec_method(procname=>'admin__save_user',
+ $self->{id} = shift @{ $self->exec_method(funcname=>'admin__save_user',
args=>[undef, $self->{username}, $self->{password}] ) };
}
return 1;
@@ -38,18 +39,18 @@ sub save {
sub get {
my $self = shift @_;
-
- my ($user_id, $username) = @{ $self->exec_method(procname=>'admin__get_user',
- args=>[$self->{id}])};
+ my $id = shift;
+ my $user = @{ $self->exec_method(funcname=>'admin__get_user',
+ args=>[$id])}[0];
- return {id=>$user_id, username=>$username};
+ return $user;
}
sub remove {
my $self = shift;
- my $code = $self->exec_method(procname=>"admin__delete_user", args=>[$self->{id}, $self->{username}]);
+ my $code = $self->exec_method(funcname=>"admin__delete_user", args=>[$self->{id}, $self->{username}]);
$self->{id} = undef; # never existed..
return $code->[0];
@@ -59,7 +60,7 @@ sub save_prefs {
my $self = shift @_;
- my $pref_id = $self->exec_method(procname=>"admin__save_preferences",
+ my $pref_id = $self->exec_method(funcname=>"admin__save_preferences",
args=>[
'language',
'stylesheet',
@@ -74,7 +75,8 @@ sub get_all_users {
my $self = shift @_;
- $self->{users} = $self->exec_method( procname=>"user__get_all_users" );
+ my @ret = $self->exec_method( funcname=>"user__get_all_users" );
+ $self->{users} = \@ret;
}
1;
diff --git a/UI/Admin/main.html b/UI/Admin/main.html
index adb03840..219e70e4 100644
--- a/UI/Admin/main.html
+++ b/UI/Admin/main.html
@@ -24,7 +24,7 @@
<tr>
<td></td>
<td>
- <a href="/admin.pl?action=edit_user&amp;user=<?lsmb user.id?>">
+ <a href="admin.pl?action=edit_user&amp;user=<?lsmb user.id?>">
<?lsmb user.id?>
</a>
</td>
diff --git a/UI/reconciliation/search.html b/UI/reconciliation/search.html
index a0cabf9f..d79ec3fc 100644
--- a/UI/reconciliation/search.html
+++ b/UI/reconciliation/search.html
@@ -1,25 +1,25 @@
-<form name="reconciliation__search" method="POST" action="recon.pl">
- <input type="hidden" name="action" value="search">
- <div>
- Date:<br/>
- <input type="input" size="15" name="date_begin" alt="<?lsmb date_format ?>"/> to
- <input type="input" size="15" name="date_end" alt="<?lsmb date_format ?>"/><br/>
- </div>
-
- <div>
- Account:<br/>
- <input type="input" size="15" name="account" alt="Chart account #"/>
- </div>
-
- <div>
- Status:<br/>
- <select name="status">
-
- <option value="1">Approved</option>
- <option value="0">Unapproved</option>
- </select>
- </div>
- <div>
- <input type="submit" value="Search!">
- </div>
-</form> \ No newline at end of file
+<?lsmb PROCESS 'ui-header.html' ?>
+<?lsmb PROCESS 'elements.html' ?>
+
+<div class="title">Search Reconciliation Reports</div>
+
+<div class="body">
+ <form name="reconciliation__search" method="post" action="recon.pl" id="reconciliation__search">
+ <input type="hidden" name="action" value="<?lsmb mode?>_search">
+ <div>
+ Date:<br>
+ <input type="input" size="15" name="date_begin" alt="&lt;?lsmb date_format ?&gt;"> to <input type="input" size="15" name="date_end" alt="&lt;?lsmb date_format ?&gt;"><br>
+ </div>
+ <div>
+ Account:<br>
+ <select name="account">
+ <?lsmb FOR account IN accounts?>
+ <option value="<?lsmb account.id?>"><?lsmb account.name?></option>
+ <?lsmb END?>
+ </select>
+ </div>
+ <div>
+ <input type="submit" value="Search!">
+ </div>
+ </form>
+</div> \ No newline at end of file
diff --git a/UI/reconciliation/upload.html b/UI/reconciliation/upload.html
index fdf3ac4e..b90d59be 100644
--- a/UI/reconciliation/upload.html
+++ b/UI/reconciliation/upload.html
@@ -1,4 +1,7 @@
-<div>
+<?lsmb PROCESS 'ui-header.html' ?>
+<?lsmb PROCESS 'elements.html' ?>
+
+<div class="body">
<div class="title">
New Reconciliation Report
</div>
diff --git a/scripts/admin.pl b/scripts/admin.pl
index 9eeb9454..29cf03c2 100644
--- a/scripts/admin.pl
+++ b/scripts/admin.pl
@@ -6,11 +6,12 @@ require 'lsmb-request.pl';
use LedgerSMB::Template;
use LedgerSMB::DBObject::Admin;
use LedgerSMB::DBObject::User;
+use Data::Dumper;
sub new_user {
# uses the same page as create_user, only pre-populated.
- #my ($class, $request) = @_;
+ #my ($request) = @_;
my $request = shift @_;
my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all');
@@ -41,7 +42,9 @@ sub new_user {
user => $user,
template => 'Admin/edit_user',
language => $user->{language},
- format => 'HTML', path=>'UI');
+ format => 'HTML',
+ path=>'UI'
+ );
$template->render(
{
@@ -55,7 +58,7 @@ sub new_user {
sub edit_user {
# uses the same page as create_user, only pre-populated.
- my ($class, $request) = @_;
+ my ($request) = @_;
my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'user_id');
my $all_roles = $admin->get_roles();
@@ -81,12 +84,12 @@ sub edit_user {
);
}
else {
- my $edited_user = $admin->get_entire_user();
+ my $edited_user = $admin->get_entire_user($request->{user});
$template->render(
{
user=>$edited_user,
roles=>$all_roles,
- user_roles=>$admin->get_user_roles($request->{username})
+ user_roles=>$admin->get_user_roles($request->{user})
}
);
}
@@ -94,7 +97,7 @@ sub edit_user {
sub edit_group {
- my ($class, $request) = @_;
+ my ($request) = @_;
my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all');
my $all_roles = $admin->role_list();
@@ -129,7 +132,7 @@ sub edit_group {
sub create_group {
- my ($class, $request) = @_;
+ my ($request) = @_;
my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all');
my $all_roles = $admin->get_roles();
@@ -156,7 +159,7 @@ sub create_group {
sub delete_group {
- my ($class, $request) = @_;
+ my ($request) = @_;
my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all');
@@ -177,7 +180,7 @@ sub delete_group {
sub delete_user {
- my ($class, $request) = @_;
+ my ($request) = @_;
my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all');
@@ -198,10 +201,10 @@ sub delete_user {
sub new_group {
- my ($class, $request) = @_;
+ my ($request) = @_;
my $template = LedgerSMB::Template->new( user=>$user,
- template=>'Admin/new_group.html', language=>$user->{language},
+ template=>'Admin/new_group', language=>$user->{language},
format=>'HTML', path=>'UI');
$template->render();
@@ -209,36 +212,32 @@ sub new_group {
sub cancel {
- &mainpage(@_);
+ &main(@_);
}
sub __default {
- &mainpage(@_);
+ &main(@_);
}
-sub mainpage {
+sub main {
my ($request) = @_;
my $template;
- # We need to test for a login here first.
-
-
-
my $user = LedgerSMB::DBObject::User->new(base=>$request, copy=>'all');
my $ret = $user->get_all_users();
$template = LedgerSMB::Template->new(
user=>$user,
- template=>'Admin/main.html',
+ template=>'Admin/main',
language=>$user->{language},
format=>'HTML',
path=>'UI'
);
- $template->render({users=>$ret});
+ $template->render( { users=>$user->{users} } );
}
#eval { do "scripts/custom/admin.pl"};
diff --git a/scripts/payment.pl b/scripts/payment.pl
index 6dfbed05..9037aca8 100644
--- a/scripts/payment.pl
+++ b/scripts/payment.pl
@@ -197,7 +197,7 @@ sub get_search_results {
form => $payment,
columns => \@columns,
heading => $heading,
- hiddens => $payment->take_top_level,
+ hiddens => $payment->take_top_level,
rows => $rows,
buttons => [{
value => 'reverse_payments',
diff --git a/scripts/recon.pl b/scripts/recon.pl
index a6076775..f128e1d8 100644
--- a/scripts/recon.pl
+++ b/scripts/recon.pl
@@ -71,33 +71,49 @@ status
=cut
+sub pending_search {
+
+ &search(shift @_,"pending");
+}
+
+sub approved_search {
+
+ &search(shift @_,"approved");
+}
+
sub search {
- my ($request) = @_;
+ my ($request,$type) = @_;
if ($request->type() eq "POST") {
# WE HAS DATUMS
# INTENTIONAL BAD PLURALIZATION OF LATIN
my $search = LedgerSMB::DBObject::Reconciliation->new(base => $request, copy => 'all');
- my $results = $search->search();
+ my $results = $search->search($type);
my $total = $search->total();
- my $template = LedgerSMB::Template->new( user => $user,
- template => 'reconciliation/report', language => $user->{language},
+ my $template = LedgerSMB::Template->new(
+ user => $user,
+ template => 'reconciliation/report',
+ language => $user->{language},
format => 'HTML',
path=>"UI");
return $template->render({report => $results, total => $total});
} else {
+ my $recon = LedgerSMB::DBObject::Reconciliation->new(base=>$request, copy=>'all');
+
my $template = LedgerSMB::Template->new(
user => $user,
template=>'reconciliation/search',
language=>$user->{language},
format=>'HTML',
- path=>"UI"
+ path=>"UI",
+ mode=>$type,
+ accounts=>$recon->get_accounts();
);
return $template->render();
}
diff --git a/sql/modules/Reconciliaton.sql b/sql/modules/Reconciliaton.sql
index b8aa2d97..045d5e93 100644
--- a/sql/modules/Reconciliaton.sql
+++ b/sql/modules/Reconciliaton.sql
@@ -2,7 +2,7 @@ CREATE TABLE reports (
id bigserial primary key not null,
report_id int NOT NULL,
account text not null,
- scn text not null, -- What is SCN?
+ scn text not null, -- SCN is the check #
their_balance numeric,
our_balance numeric,
errorcode INT,
@@ -15,6 +15,11 @@ CREATE TABLE reports (
approved boolean not null default 'f'
);
+CREATE TABLE coa_to_account (
+ chart_id int not null references chart(id),
+ account text not null CHECK (account ~ '[0-9]{7}(xxx)')
+);
+
CREATE TABLE report_corrections (
id serial primary key not null,
@@ -57,7 +62,7 @@ CREATE OR REPLACE FUNCTION reconciliation__correct_ledger (in_report_id INT, in_
-- After that, it is required to update the general ledger.
full_reason := "User % is filing a reconciliation correction on the general ledger, changing amount % to amount %.
Their reason given is: %", in_user, current_row.our_balance, in_new_amount, reason;
- select update_ledger(current_row.lid, in_new_amount, full_reason)
+ perform select reconciliation__update_ledger(current_row.lid, in_new_amount, full_reason)
ELSE IF current_row.our_balance = in_new_amount THEN
-- This should be something, does it equal the original
-- balance? If so, there's no change.
@@ -243,7 +248,7 @@ $$ language 'sql';
create or replace function reconciliation__add_entry(
in_report_id INT,
in_scn INT,
- in_amount INT,
+ in_amount numeric,
in_account INT,
in_user TEXT,
in_date TIMESTAMP
@@ -257,9 +262,13 @@ create or replace function reconciliation__add_entry(
BEGIN
SELECT INTO la FROM acc_trans gl
- WHERE gl.source = in_scn
- and gl.account = in_account
- and gl.amount = in_amount;
+ JOIN chart c on gl.chart_id = c.id
+ JOIN ap ON gl.trans_id = ap.id
+ JOIN coa_to_account cta on cta.chart_id = gl.chart_id
+ WHERE gl.source ~ in_scn -- does it look like it?
+ and cta.account = in_account
+ and gl.amount = in_amount
+ AND gl.transdate = in_date;
lid := NULL;
IF NOT FOUND THEN
@@ -414,18 +423,28 @@ CREATE OR REPLACE FUNCTION reconciliation__search (
in_date_begin DATE,
in_date_end DATE,
in_account TEXT,
- in_status BOOLEAN
+ in_status TEXT
) RETURNS setof reports AS $$
DECLARE
row reports;
statement text;
where_stmt text;
+ v_status BOOLEAN;
+ v_accum NUMERIC;
BEGIN
+
+ if in_status = "pending" then
+ v_status = 'ft'::bool;
+ ELSIF in_status = "approved" THEN
+
+ v_status = 't'::bool;
+ END IF;
+
IF in_date_begin IS NOT NULL
or in_date_end IS NOT NULL
or in_account IS NOT NULL
- or in_status IS NOT NULL
+ or v_status IS NOT NULL
THEN
statement = "select pr.* from reports pr ";
statement = statement + "join acc_trans at on pr.ledger_id = at.entry_id ";
@@ -446,9 +465,9 @@ CREATE OR REPLACE FUNCTION reconciliation__search (
IF in_status IS NOT NULL THEN
- if in_status == 't'::bool THEN
+ if v_status == 't'::bool THEN
where_stmt = where_stmt + " approved = 't'::bool AND ";
- ELSIF in_status == 'f'::bool THEN
+ ELSIF v_status == 'f'::bool THEN
where_stmt = where_stmt + " approved = 'f'::bool AND ";
END IF;
@@ -464,4 +483,18 @@ CREATE OR REPLACE FUNCTION reconciliation__search (
END LOOP;
END IF;
- END; \ No newline at end of file
+ END;
+$$ language 'plpgsql';
+
+create type recon_accounts as (
+ name text,
+ id int
+);
+
+create or replace function reconciliation__get_accounts () returns setof recon_accounts as $$
+ SELECT
+ coa.accno || ' ' || coa.description as name,
+ coa.id as id
+ FROM chart coa, coa_to_account cta
+ WHERE cta.chart_id = coa.id;
+$$ language sql; \ No newline at end of file
diff --git a/sql/modules/admin.sql b/sql/modules/admin.sql
index 11c2e5b2..e2023a5f 100644
--- a/sql/modules/admin.sql
+++ b/sql/modules/admin.sql
@@ -255,7 +255,7 @@ CREATE OR REPLACE FUNCTION admin__save_user(
--- The entity is expected to already BE created. See admin.pm.
- if admin_is_user(in_username) then
+ if admin__is_user(in_username) then
-- uhm, this is bad.
RAISE EXCEPTION 'Fatal exception: Username already exists in Postgres; not
@@ -357,7 +357,7 @@ CREATE OR REPLACE FUNCTION admin__delete_user(in_username TEXT) returns INT as $
$$ language 'plpgsql';
-comment on function admin_delete_user(text) is $$
+comment on function admin__delete_user(text) is $$
Drops the provided user, as well as deletes the entity and user configuration data.
$$;
@@ -380,7 +380,7 @@ CREATE OR REPLACE FUNCTION admin__delete_group (in_dbname TEXT, in_group_name TE
END;
$$ language 'plpgsql';
-comment on function admin_delete_group(text,text) IS $$
+comment on function admin__delete_group(text,text) IS $$
Deletes the input group from the database. Not designed to be used to
remove a login-capable user.
$$;
@@ -441,11 +441,28 @@ create or replace view user_listable as
join users u on u.entity_id = e.id;
-create or replace function user_get_all_users () returns setof user_listable as $$
+create or replace function user__get_all_users () returns setof user_listable as $$
select * from user_listable;
$$ language sql;
+create or replace function admin__get_roles (in_database text) returns setof text as $$
+DECLARE
+ v_rol text;
+BEGIN
+ FOR v_rol in
+ SELECT
+ rolname
+ from
+ pg_roles
+ where
+ rolname ~ ('^lsmb_' || in_database)
+ LOOP
+ RETURN NEXT v_rol;
+ END LOOP;
+END;
+$$ language plpgsql;
+
commit; \ No newline at end of file