[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2283] trunk
- Subject: SF.net SVN: ledger-smb:[2283] trunk
- From: ..hidden..
- Date: Tue, 19 Aug 2008 23:00:36 +0000
Revision: 2283
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2283&view=rev
Author: aurynn_cmd
Date: 2008-08-19 23:00:35 +0000 (Tue, 19 Aug 2008)
Log Message:
-----------
Copious Admin and Reconciliation changes.
Modified Paths:
--------------
trunk/LedgerSMB/Admin.pm
trunk/LedgerSMB/DBObject/Reconciliation.pm
trunk/LedgerSMB/DBObject/User.pm
trunk/UI/Admin/main.html
trunk/UI/reconciliation/search.html
trunk/UI/reconciliation/upload.html
trunk/scripts/admin.pl
trunk/scripts/payment.pl
trunk/scripts/recon.pl
trunk/sql/modules/Reconciliaton.sql
trunk/sql/modules/admin.sql
Modified: trunk/LedgerSMB/Admin.pm
===================================================================
--- trunk/LedgerSMB/Admin.pm 2008-08-19 22:49:39 UTC (rev 2282)
+++ trunk/LedgerSMB/Admin.pm 2008-08-19 23:00:35 UTC (rev 2283)
@@ -10,7 +10,7 @@
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 @@
$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 @@
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 @@
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 @@
# 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 @@
# 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 @@
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 @@
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 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
Modified: trunk/LedgerSMB/DBObject/Reconciliation.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Reconciliation.pm 2008-08-19 22:49:39 UTC (rev 2282)
+++ trunk/LedgerSMB/DBObject/Reconciliation.pm 2008-08-19 23:00:35 UTC (rev 2283)
@@ -160,15 +160,24 @@
# 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 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 @@
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
Modified: trunk/LedgerSMB/DBObject/User.pm
===================================================================
--- trunk/LedgerSMB/DBObject/User.pm 2008-08-19 22:49:39 UTC (rev 2282)
+++ trunk/LedgerSMB/DBObject/User.pm 2008-08-19 23:00:35 UTC (rev 2283)
@@ -1,6 +1,7 @@
package LedgerSMB::DBObject::User;
use base qw/LedgerSMB::DBObject/;
+use Data::Dumper;
sub save {
@@ -11,7 +12,7 @@
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 @@
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 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 @@
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 @@
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} = ..hidden..;
}
1;
Modified: trunk/UI/Admin/main.html
===================================================================
--- trunk/UI/Admin/main.html 2008-08-19 22:49:39 UTC (rev 2282)
+++ trunk/UI/Admin/main.html 2008-08-19 23:00:35 UTC (rev 2283)
@@ -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>
Modified: trunk/UI/reconciliation/search.html
===================================================================
--- trunk/UI/reconciliation/search.html 2008-08-19 22:49:39 UTC (rev 2282)
+++ trunk/UI/reconciliation/search.html 2008-08-19 23:00:35 UTC (rev 2283)
@@ -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
Modified: trunk/UI/reconciliation/upload.html
===================================================================
--- trunk/UI/reconciliation/upload.html 2008-08-19 22:49:39 UTC (rev 2282)
+++ trunk/UI/reconciliation/upload.html 2008-08-19 23:00:35 UTC (rev 2283)
@@ -1,4 +1,7 @@
-<div>
+<?lsmb PROCESS 'ui-header.html' ?>
+<?lsmb PROCESS 'elements.html' ?>
+
+<div class="body">
<div class="title">
New Reconciliation Report
</div>
Modified: trunk/scripts/admin.pl
===================================================================
--- trunk/scripts/admin.pl 2008-08-19 22:49:39 UTC (rev 2282)
+++ trunk/scripts/admin.pl 2008-08-19 23:00:35 UTC (rev 2283)
@@ -6,11 +6,12 @@
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 @@
user => $user,
template => 'Admin/edit_user',
language => $user->{language},
- format => 'HTML', path=>'UI');
+ format => 'HTML',
+ path=>'UI'
+ );
$template->render(
{
@@ -55,7 +58,7 @@
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 @@
);
}
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_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 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 delete_group {
- my ($class, $request) = @_;
+ my ($request) = @_;
my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all');
@@ -177,7 +180,7 @@
sub delete_user {
- my ($class, $request) = @_;
+ my ($request) = @_;
my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all');
@@ -198,10 +201,10 @@
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 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"};
Modified: trunk/scripts/payment.pl
===================================================================
--- trunk/scripts/payment.pl 2008-08-19 22:49:39 UTC (rev 2282)
+++ trunk/scripts/payment.pl 2008-08-19 23:00:35 UTC (rev 2283)
@@ -197,7 +197,7 @@
form => $payment,
columns => ..hidden..,
heading => $heading,
- hiddens => $payment->take_top_level,
+ hiddens => $payment->take_top_level,
rows => $rows,
buttons => [{
value => 'reverse_payments',
Modified: trunk/scripts/recon.pl
===================================================================
--- trunk/scripts/recon.pl 2008-08-19 22:49:39 UTC (rev 2282)
+++ trunk/scripts/recon.pl 2008-08-19 23:00:35 UTC (rev 2283)
@@ -71,33 +71,49 @@
=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();
}
Modified: trunk/sql/modules/Reconciliaton.sql
===================================================================
--- trunk/sql/modules/Reconciliaton.sql 2008-08-19 22:49:39 UTC (rev 2282)
+++ trunk/sql/modules/Reconciliaton.sql 2008-08-19 23:00:35 UTC (rev 2283)
@@ -2,7 +2,7 @@
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,7 +15,12 @@
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,
correction_id int not null default 1,
@@ -57,7 +62,7 @@
-- 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 @@
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 @@
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 @@
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 @@
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 @@
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
Modified: trunk/sql/modules/admin.sql
===================================================================
--- trunk/sql/modules/admin.sql 2008-08-19 22:49:39 UTC (rev 2282)
+++ trunk/sql/modules/admin.sql 2008-08-19 23:00:35 UTC (rev 2283)
@@ -255,7 +255,7 @@
--- 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 @@
$$ 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 @@
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 @@
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
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.