diff options
| -rw-r--r-- | LedgerSMB/Admin.pm | 42 | ||||
| -rw-r--r-- | LedgerSMB/DBObject/Reconciliation.pm | 25 | ||||
| -rw-r--r-- | LedgerSMB/DBObject/User.pm | 20 | ||||
| -rw-r--r-- | UI/Admin/main.html | 2 | ||||
| -rw-r--r-- | UI/reconciliation/search.html | 50 | ||||
| -rw-r--r-- | UI/reconciliation/upload.html | 5 | ||||
| -rw-r--r-- | scripts/admin.pl | 39 | ||||
| -rw-r--r-- | scripts/payment.pl | 2 | ||||
| -rw-r--r-- | scripts/recon.pl | 26 | ||||
| -rw-r--r-- | sql/modules/Reconciliaton.sql | 55 | ||||
| -rw-r--r-- | sql/modules/admin.sql | 25 |
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&user=<?lsmb user.id?>"> + <a href="admin.pl?action=edit_user&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="<?lsmb date_format ?>"> to <input type="input" size="15" name="date_end" alt="<?lsmb date_format ?>"><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 |