Correcting behavior of session timeout to release locks and create new session.

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

5 files changed, 68 insertions, 24 deletions

diff --git a/LedgerSMB/Auth/DB.pm b/LedgerSMB/Auth/DB.pm
index 0a474af1..b24fd6d1 100755
--- a/LedgerSMB/Auth/DB.pm
+++ b/LedgerSMB/Auth/DB.pm
@@ -47,19 +47,7 @@ sub session_check {
     my $dbh = $form->{dbh};
 
     my $checkQuery = $dbh->prepare(
-        "SELECT u.username, s.transaction_id 
-           FROM session as s
-	   JOIN users as u ON (s.users_id = u.id)
-          WHERE s.session_id = ?
-            AND token = ?
-            AND s.last_used > now() - ?::interval"
-    );
-
-    my $updateAge = $dbh->prepare(
-        "UPDATE session 
-           SET last_used = now()
-         WHERE session_id = ?;"
-    );
+        "SELECT * FROM session_check(?, ?)");
 
     my ($sessionID, $token, $company) = split(/:/, $cookie);
 
@@ -77,7 +65,7 @@ sub session_check {
         $timeout = "$form->{timeout} seconds";
     }
 
-    $checkQuery->execute( $sessionID, $token, $timeout )
+    $checkQuery->execute( $sessionID, $token)
       || $form->dberror(
         __FILE__ . ':' . __LINE__ . ': Looking for session: ' );
     my $sessionValid = $checkQuery->rows;
@@ -85,22 +73,19 @@ sub session_check {
     if ($sessionValid) {
 
         #user has a valid session cookie, now check the user
-        my ( $sessionLogin, $sessionTransaction ) = $checkQuery->fetchrow_array;
+        my ( $session_ref) =  $checkQuery->fetchrow_hashref('NAME_lc');
 
         my $login = $form->{login};
 
         $login =~ s/[^a-zA-Z0-9._+\@'-]//g;
-        if (( $sessionLogin eq $login ))
+        if (( $session_ref ))
         {
 
 
 
-            $updateAge->execute( $sessionID )
-              || $form->dberror(
-                __FILE__ . ':' . __LINE__ . ': Updating session age: ' );
 
             my $newCookieValue =
-              $sessionID . ':' . $token . ':' . $form->{company};
+              $session_ref->{session_id} . ':' . $session_ref->{token} . ':' . $form->{company};
 
             #now update the cookie in the browser
             print qq|Set-Cookie: LedgerSMB=$newCookieValue; path=$path;\n|;
diff --git a/scripts/payment.pl b/scripts/payment.pl
index 5cea05ff..551ebf90 100644
--- a/scripts/payment.pl
+++ b/scripts/payment.pl
@@ -69,7 +69,7 @@ TT2 system. (hopefully it will... )
 =cut
 
 sub payments {
-    my ($request)  = @_;
+    my ($request) = @_;
     my $payment =  LedgerSMB::DBObject::Payment->new({'base' => $request});
     $payment->get_metadata();
     my $template = LedgerSMB::Template->new(
@@ -81,6 +81,20 @@ sub payments {
     );
     $template->render($payment);
 }
+
+sub display_payments {
+    my ($request) = @_;
+    my $payment =  LedgerSMB::DBObject::Payment->new({'base' => $request});
+    $payment->get_payment_detail_data();
+    my $template = LedgerSMB::Template->new(
+        user     => $request->{_user},
+        locale   => $request->{_locale},
+        path     => 'UI/payments',
+        template => 'payments_filter',
+        format   => 'HTML', 
+    );
+    $template->render($payment);
+}
   
 
 sub payment {
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index afd4abcc..c39e6945 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -316,7 +316,7 @@ $$
 DECLARE resultrow record;
 BEGIN
         FOR resultrow IN
-          SELECT curr FROM ar
+          SELECT curr AS curr FROM ar
           WHERE amount <> paid
           OR paid IS NULL
           AND in_account_class=2 
diff --git a/sql/modules/Roles.sql b/sql/modules/Roles.sql
index 1425ff87..8ae539e9 100644
--- a/sql/modules/Roles.sql
+++ b/sql/modules/Roles.sql
@@ -1,3 +1,4 @@
+GRANT ALL ON SCHEMA public TO public; -- required for Pg 8.2
 -- Contacts
 CREATE ROLE lsmb_<?lsmb dbname ?>__read_contact
 WITH INHERIT NOLOGIN;
@@ -1382,5 +1383,6 @@ grant select on menu_node, menu_attribute, menu_acl to public;
 GRANT select on chart, gifi, country to public;
  grant select on employee to public;
  GRANT SELECT ON parts, partsgroup TO public;
- GRANT SELECT ON language TO public;
-GRANT SELECT ON business, exchangerate, shipto, tax TO public;
+ GRANT SELECT ON language, project TO public;
+GRANT SELECT ON business, exchangerate, department, shipto, tax TO public;
+
diff --git a/sql/modules/Session.sql b/sql/modules/Session.sql
new file mode 100644
index 00000000..a44ed924
--- /dev/null
+++ b/sql/modules/Session.sql
@@ -0,0 +1,43 @@
+CREATE OR REPLACE FUNCTION session_check(in_session_id int, in_token text) 
+RETURNS session AS
+$$
+DECLARE out_row session%ROWTYPE;
+BEGIN
+        UPDATE session 
+           SET last_used = now()
+         WHERE session_id = in_session_id
+               AND token = in_token
+               AND last_used > now() - (SELECT value FROM defaults
+				WHERE setting_key = 'timeout')::interval
+	       AND users_id = (select id from users 
+			where username = SESSION_USER);
+	IF FOUND THEN
+		SELECT * INTO out_row WHERE session_id = in_session_id;
+	ELSE
+		DELETE FROM SESSION 
+		WHERE users_id IN (select id from users
+                        where username = SESSION_USER); 
+		-- the above query also releases all discretionary locks by the
+                -- session
+
+		IF NOT FOUND THEN
+			SELECT id FROM users WHERE username = SESSION_USER;
+			IF NOT FOUND THEN
+				RAISE EXCEPTION 'User Not Known';
+			END IF;
+			
+		END IF;
+		INSERT INTO session(users_id, token, last_used, transaction_id)
+		SELECT id, md5(random()), now(), 0 
+		  FROM users WHERE username = SESSION_USER;
+		-- TODO-- remove transaction_id field from session table
+
+		SELECT * INTO out_row FROM session 
+		 WHERE session_id = currval('session_session_id_seq');
+	END IF;
+	RETURN out_row;
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMENT ON FUNCTION session_check(int, text) IS 
+$$ Return code is 0 for failure, 1 for success. $$;