Copious Admin and Reconciliation changes.

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2283 4979c152-3d1c-0410-bac9-87ea11338e46

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