[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2991] addons/1.3
- Subject: SF.net SVN: ledger-smb:[2991] addons/1.3
- From: ..hidden..
- Date: Mon, 26 Apr 2010 23:05:25 +0000
Revision: 2991
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2991&view=rev
Author: aurynn_cmd
Date: 2010-04-26 23:05:25 +0000 (Mon, 26 Apr 2010)
Log Message:
-----------
Initial import of the Vendor Auditing codebase.
Added Paths:
-----------
addons/1.3/audit/
addons/1.3/audit/trunk/
addons/1.3/audit/trunk/INSTALL.audit
addons/1.3/audit/trunk/LedgerSMB/
addons/1.3/audit/trunk/LedgerSMB/DBObject/
addons/1.3/audit/trunk/LedgerSMB/DBObject/Vendor/
addons/1.3/audit/trunk/LedgerSMB/DBObject/Vendor/Audit.pm
addons/1.3/audit/trunk/LedgerSMB/Vendor/
addons/1.3/audit/trunk/LedgerSMB/Vendor/Audit.pm
addons/1.3/audit/trunk/UI/
addons/1.3/audit/trunk/UI/Audit/
addons/1.3/audit/trunk/UI/Audit/account.html
addons/1.3/audit/trunk/UI/Audit/address.html
addons/1.3/audit/trunk/UI/Audit/company.html
addons/1.3/audit/trunk/UI/Audit/contact.html
addons/1.3/audit/trunk/UI/Audit/location.html
addons/1.3/audit/trunk/audit.pl
addons/1.3/audit/trunk/scripts/
addons/1.3/audit/trunk/scripts/audit.pl
addons/1.3/audit/trunk/sql/
addons/1.3/audit/trunk/sql/modules/
addons/1.3/audit/trunk/sql/modules/audit/
addons/1.3/audit/trunk/sql/modules/audit/audit_helper.pm
addons/1.3/audit/trunk/sql/modules/audit/auditing.sql
addons/1.3/audit/trunk/sql/modules/audit/menu.sql
addons/1.3/audit/trunk/sql/modules/audit/vendor.pl
Added: addons/1.3/audit/trunk/INSTALL.audit
===================================================================
--- addons/1.3/audit/trunk/INSTALL.audit (rev 0)
+++ addons/1.3/audit/trunk/INSTALL.audit 2010-04-26 23:05:25 UTC (rev 2991)
@@ -0,0 +1,16 @@
+- Vendor Auditing
+
+-- Requirements
+
+ * Superuser access to the database
+ * pl/perl
+ * Write access to a library directory in the Perl path
+
+-- Installation
+
+ * Run addons/apply_addon.sh audit
+ * As database superuser, load sql/modules/audit/auditing.sql file.
+ This will create the appropriate schema and tables, as well as the PLPerlU stored procedure language.
+ It will then load the vendor.pl SQL file, which contains the various required functions for vendor auditing.
+
+
\ No newline at end of file
Added: addons/1.3/audit/trunk/LedgerSMB/DBObject/Vendor/Audit.pm
===================================================================
--- addons/1.3/audit/trunk/LedgerSMB/DBObject/Vendor/Audit.pm (rev 0)
+++ addons/1.3/audit/trunk/LedgerSMB/DBObject/Vendor/Audit.pm 2010-04-26 23:05:25 UTC (rev 2991)
@@ -0,0 +1,94 @@
+package LedgerSMB::DBObject::Vendor::Audit;
+
+use base LedgerSMB::DBObject;
+
+use strict;
+
+
+sub company {
+ my ($self, $id, $entity_id) = @_;
+
+ my @entity = $self->exec_method(
+ funcname=>"entity",
+ schema=>"audit",
+ args=>[$entity_id]
+ );
+ my @company = $self->exec_method(
+ funcname=>"company",
+ schema=>"audit",
+ args=>[$id, $entity_id]
+ );
+ my @union = (@entity, @company);
+ return @union;
+}
+
+sub account {
+
+ my ($self, $id, $entity_id, $meta) = @_;
+
+ my @eca = $self->exec_method(
+ funcname=>"entity_credit_account",
+ args=>[$id, $entity_id, $meta],
+ schema=>"audit"
+ );
+ return @eca;
+
+}
+
+sub eca_to_location {
+ my ($self, $id, $entity_id) = @_;
+
+ my @eca = $self->exec_method(
+ funcname=>"eca_to_location",
+ args=>[$id, $entity_id],
+ schema=>"audit"
+ );
+ return @eca;
+}
+
+sub address {
+ my ($self, $id) = @_;
+
+ my @addresses = $self->exec_method(
+ funcname=>"location",
+ args=>[$id],
+ schema=>"audit"
+ );
+ return @addresses;
+}
+
+sub contacts {
+
+ my ($self, $eca_id) = @_;
+
+ return $self->exec_method(
+ funcname=>"contact_list",
+ args=>[$eca_id],
+ schema=>"audit"
+ );
+}
+
+sub contact {
+
+ my ($self, $eca_id, $contact_class) = @_;
+
+ return $self->exec_method(
+ funcname=>"eca_to_contact",
+ args=>[$eca_id, $contact_class],
+ schema=>"audit"
+ );
+
+}
+
+sub vendors {
+ my ($self) = @_;
+
+ return $self->exec_method(funcname=>"vendors", schema=>"audit");
+}
+
+sub locations {
+ my ($self, $entity_id) = @_;
+
+ return $self->exec_method(funcname=>"location_list", schema=>"audit", args=>[$entity_id]);
+}
+1;
Added: addons/1.3/audit/trunk/LedgerSMB/Vendor/Audit.pm
===================================================================
--- addons/1.3/audit/trunk/LedgerSMB/Vendor/Audit.pm (rev 0)
+++ addons/1.3/audit/trunk/LedgerSMB/Vendor/Audit.pm 2010-04-26 23:05:25 UTC (rev 2991)
@@ -0,0 +1,357 @@
+package LedgerSMB::Vendor::Audit;
+
+#use base LedgerSMB;
+use LedgerSMB::DBObject::Vendor::Audit;
+use LedgerSMB::DBObject::Vendor;
+use LedgerSMB::Template;
+use Data::Dumper;
+use strict;
+
+sub new {
+
+ return bless {}, shift;
+}
+
+sub accounts {
+
+ my ($self, $request) = @_;
+
+ my $vendor = new LedgerSMB::DBObject::Vendor(base=>$request, merge=>['entity_id','account_class','meta_number']);
+
+ $vendor->get();
+ $vendor->accounts();
+ my @accounts = @{$vendor->{accounts}};
+
+ print STDERR Dumper($request);
+ my $locale = $request->{_locale};
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => 'HTML'
+ );
+
+ my $columns;
+ @$columns = qw(type number description credit_limit start_date end_date);
+ my $heading = {
+ type => $locale->text('Type'),
+ number => $locale->text('ID'),
+ description => $locale->text('Description'),
+ start_date => $locale->text('Start Date'),
+ end_date => $locale->text('End Date'),
+ };
+
+ my $rows = [];
+ for my $account (@accounts) {
+
+ my $href = "audit.pl?action=vendor§ion=account&entity_id=".$vendor->{entity_id}."&account_class=".$vendor->{entity_class}."&meta_number=".$account->{meta_number};
+ my $type = ($vendor->{entity_class} == 1 ? 'vendor' : 'customer');
+ my $row = {
+ type => $type,
+ number => {
+ text => $account->{id},
+ href => "$href&account_id=".$account->{id},
+ },
+ description => $account->{description},
+ start_date => $account->{startdate},
+ end_date => $account->{enddate},
+ };
+ push @$rows, $row;
+ }
+ return $template->render({
+ heading => $heading,
+ rows => $rows,
+ columns => $columns,
+ });
+}
+
+sub account {
+
+ # Returns specific account
+
+ my ($self, $request) = @_;
+
+ my $vendor = new LedgerSMB::DBObject::Vendor(base=>$request, merge=>['entity_id','account_class','meta_number']);
+
+ $vendor->get();
+
+ # now for the audit company details
+ my $user = $request->{_user};
+ my $audit = new LedgerSMB::DBObject::Vendor::Audit(base=>$request, merge=>[]); # no merge.
+
+ my @account = $audit->account($request->{account_id},$vendor->{entity_id}, $vendor->{meta_number});
+
+ print STDERR Dumper(@account);
+ my $template = LedgerSMB::Template->new( user=> $user,
+ template=>'account',
+ language => $user->{language},
+ path => 'UI/Audit',
+ format => 'HTML'
+ );
+ return $template->render(
+ {
+ audit=>..hidden..,
+ entity=>$vendor
+ }
+ );
+}
+
+sub company {
+
+ # Returns the audit view for the company values.
+ # This requires 2 database queries to accomplish:
+ # One for the Entity table, and one for the Company table.
+ #
+ # Also requires the company ID #, since the incoming data only provides us the
+ # entity ID
+
+ my ($self, $request) = @_;
+
+ my $vendor = new LedgerSMB::DBObject::Vendor(base=>$request, merge=>['entity_id','account_class','meta_number']);
+
+ $vendor->get();
+
+ # now for the audit company details
+ my $user = $request->{_user};
+ my $audit = new LedgerSMB::DBObject::Vendor::Audit(base=>$request, merge=>[]); # no merge.
+ my @company = $audit->company($vendor->{id},$vendor->{entity_id});
+
+ my $template = LedgerSMB::Template->new( user=> $user,
+ template=>'company',
+ language => $user->{language},
+ path => 'UI/Audit',
+ format => 'HTML'
+ );
+ return $template->render({audit=>..hidden.., entity=>$vendor, });
+}
+
+sub vendors {
+
+ ## This should be returning a list of vendors that are able to be queried
+ ## for audit information.
+ ## Based on this view, it will provide a link to the Company view.
+
+ my ($self, $request) = @_;
+
+ my $audit = new LedgerSMB::DBObject::Vendor::Audit(base=>$request, merge=>[]);
+
+ my @vendors = $audit->vendors();
+ my $user = $request->{_user};
+
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => 'HTML'
+ );
+
+ my $locale = $request->{_locale};
+
+ my $columns;
+ @$columns = qw(name number description start_date end_date);
+ my $heading = {
+ name => $locale->text('Name'),
+ number => $locale->text('Vendor Number'),
+ description => $locale->text('Description'),
+ start_date => $locale->text('Start Date'),
+ end_date => $locale->text('End Date'),
+ };
+
+ my $rows = [];
+ my $href = "audit.pl?action=vendor§ion=company";
+ for my $vendor (@vendors) {
+ my $name;
+ if ($vendor->{auditable}) {
+ $name = {
+ text => $vendor->{name},
+ href => "$href&id=".$vendor->{id}.
+ "&entity_id=".$vendor->{entity_id}.
+ "&meta_number=".$vendor->{number},
+ };
+ }
+ else {
+ $name = $vendor->{name};
+ }
+
+ my $row = {
+ name => $name,
+ number => $vendor->{meta_number},
+ description => $vendor->{description},
+ start_date => $vendor->{start_date},
+ end_date => $vendor->{end_date},
+ };
+ push @$rows, $row;
+ }
+ return $template->render({
+ heading => $heading,
+ rows => $rows,
+ columns => $columns,
+ });
+
+}
+
+sub contacts {
+
+ my ($self, $request) = @_;
+ my $vendor = new LedgerSMB::DBObject::Vendor(base=>$request, merge=>['entity_id','account_class','meta_number']);
+
+ $vendor->get();
+
+ my $audit = new LedgerSMB::DBObject::Vendor::Audit(base=>$request, merge=>[]);
+
+ my @contacts = $audit->contacts($vendor->{entity_id});
+ print STDERR Dumper(@contacts);
+# print STDERR Dumper($vendor);
+
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => 'HTML'
+ );
+ my $columns;
+ my $locale = $request->{_locale};
+ @$columns = qw(class contact description);
+ my $heading = {
+ class => $locale->text('Class'),
+ contact => $locale->text('Contact'),
+ description => $locale->text('Description'),
+ };
+
+ my $rows = [];
+
+
+
+ for my $contact (@contacts) {
+
+ my $href = "audit.pl?action=vendor§ion=contact&entity_id=".$vendor->{entity_id}."&account_class=".$vendor->{entity_class}."&meta_number=".$vendor->{meta_number};
+ my $row = {
+ class => {
+ text => $contact->{contact_class},
+ href => "$href&class=".$contact->{contact_class_id}."&credit_id=".$contact->{credit_id},
+ },
+ contact => $contact->{contact},
+ description => $contact->{description},
+ };
+ push @$rows, $row;
+ }
+ return $template->render({
+ heading => $heading,
+ rows => $rows,
+ columns => $columns,
+ });
+
+}
+
+sub contact {
+
+ my ($self, $request) = @_;
+
+ my $vendor = new LedgerSMB::DBObject::Vendor(base=>$request, merge=>['entity_id','account_class','meta_number']);
+
+ $vendor->get();
+
+ my $audit = new LedgerSMB::DBObject::Vendor::Audit(base=>$request, merge=>[]);
+
+ my @contacts = $audit->contact($request->{credit_id}, $request->{class});
+
+ my $template = LedgerSMB::Template->new(
+ user=> $request->{_user},
+ locale => $request->{_locale},
+ template=>'contact',
+ language => $request->{_user}->{language},
+ path => 'UI/Audit',
+ format => 'HTML'
+ );
+
+ return $template->render({audit=>..hidden.., entity=>$vendor, });
+}
+
+
+sub address {
+
+ #
+ # This will return the audit information for a single address for the specified company.
+ #
+
+ my ($self, $request) = @_;
+
+ my $vendor = new LedgerSMB::DBObject::Vendor(base=>$request, merge=>['entity_id','account_class','meta_number']);
+ $vendor->get();
+ my $user = $request->{_user};
+ my $address = $vendor->address( $request->{location_class} );
+ my $audit = new LedgerSMB::DBObject::Vendor::Audit(base=>$request, merge=>[]); # no merge.
+
+ my @address = $audit->address($request->{address_id});
+
+ print STDERR Dumper(@address);
+ my $template = LedgerSMB::Template->new(
+ user=> $user,
+ template=>'address',
+ language => $user->{language},
+ path => 'UI/Audit',
+ format => 'HTML'
+ );
+ return $template->render({audit=>..hidden.., address=>$address, entity=>$vendor});
+
+}
+
+sub locations {
+
+ # eca__list_locations
+
+ my ($self, $request) = @_;
+
+ my $vendor = new LedgerSMB::DBObject::Vendor(base=>$request, merge=>['entity_id','account_class','meta_number']);
+
+ $vendor->get();
+ my $audit = new LedgerSMB::DBObject::Vendor::Audit(base=>$request, merge=>[]); # no merge.
+ # now for the audit company details
+
+ my @locations = $audit->locations($vendor->{entity_id});
+
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => 'HTML'
+ );
+ my $columns;
+ my $locale = $request->{_locale};
+ @$columns = qw(class id city country created );
+ my $heading = {
+ class => $locale->text('Class'),
+ id => $locale->text('Number'),
+ city => $locale->text('City'),
+ country => $locale->text('Country'),
+ created => $locale->text('Created'),
+ };
+
+ my $rows = [];
+ for my $loc (@locations) {
+
+ my $href = "audit.pl?action=vendor§ion=address&entity_id=".$vendor->{entity_id}."&account_class=".$vendor->{entity_class}."&meta_number=".$vendor->{meta_number};
+ my $row = {
+ class => $loc->{location_class},
+ id => {
+ text => $loc->{id},
+ href => "$href&address_id=".$loc->{id},
+ },
+ city => $loc->{city},
+ country => $loc->{country},
+ created => $loc->{created},
+ };
+ push @$rows, $row;
+ }
+ return $template->render({
+ heading => $heading,
+ rows => $rows,
+ columns => $columns,
+ });
+}
+
+1;
Added: addons/1.3/audit/trunk/UI/Audit/account.html
===================================================================
--- addons/1.3/audit/trunk/UI/Audit/account.html (rev 0)
+++ addons/1.3/audit/trunk/UI/Audit/account.html 2010-04-26 23:05:25 UTC (rev 2991)
@@ -0,0 +1,27 @@
+<?lsmb PROCESS 'ui-header.html' ?>
+<?lsmb PROCESS 'elements.html' ?>
+
+<div class="title">Audit report: Account information for vendor <b><?lsmb entity.name ?></b> </div>
+<br/><br/>
+<div class="audit">
+Account Column
+ <table>
+ <?lsmb FOREACH event IN audit ?>
+ <tr>
+ <td>
+ <b><?lsmb event.col ?></b>
+ </td>
+ <?lsmb IF event.mode == 'UPDATE' ?>
+ <td class="mode"><span class="mode"><i>UPDATED</i></span> from</td>
+ <td class="old"> <span class="old"><?lsmb event.original ? event.original : 'NULL' ?></span> to </td class="old">
+ <td class="new"> <?lsmb event.replacement?> </span>
+ <?lsmb ELSIF event.mode == 'DELETE'?>
+ <td class="mode" colspan="3"><i>DELETED</i></td>
+ <?lsmb END?>
+ <td class="user">
+ by <?lsmb event.by?>
+ </td>
+ </tr>
+ <?lsmb END?>
+ </table>
+</div>
Added: addons/1.3/audit/trunk/UI/Audit/address.html
===================================================================
--- addons/1.3/audit/trunk/UI/Audit/address.html (rev 0)
+++ addons/1.3/audit/trunk/UI/Audit/address.html 2010-04-26 23:05:25 UTC (rev 2991)
@@ -0,0 +1,31 @@
+<?lsmb PROCESS 'ui-header.html' ?>
+<?lsmb PROCESS 'elements.html' ?>
+
+<div class="title">Audit report: <?lsmb address.class ?> address information for vendor <?lsmb entity.name ?> </div>
+
+ <br/>
+ <br/>
+ <div class="audit">
+ Address Column
+ <table>
+ <?lsmb FOREACH event IN audit ?>
+ <tr>
+ <td>
+ <b><?lsmb event.col ?></b>
+ </td>
+ <?lsmb IF event.mode == 'UPDATE' ?>
+ <td class="mode"><span class="mode"><i>UPDATED</i></span> from</td>
+ <td class="old"> <span class="old"><?lsmb event.original ? event.original : 'NULL' ?></span> to </td class="old">
+ <td class="new"> <?lsmb event.replacement?> </span>
+ <?lsmb ELSIF event.mode == 'DELETE'?>
+ <td class="mode" colspan="3"><i>DELETED</i></td>
+ <?lsmb END?>
+ <td class="user">
+ by <?lsmb event.by?>
+ </td>
+ </tr>
+ <?lsmb END?>
+ </table>
+
+ </div>
+</div>
Added: addons/1.3/audit/trunk/UI/Audit/company.html
===================================================================
--- addons/1.3/audit/trunk/UI/Audit/company.html (rev 0)
+++ addons/1.3/audit/trunk/UI/Audit/company.html 2010-04-26 23:05:25 UTC (rev 2991)
@@ -0,0 +1,47 @@
+<?lsmb PROCESS 'ui-header.html' ?>
+<?lsmb PROCESS 'elements.html' ?>
+<body>
+
+ <div class="table_header">
+ <table>
+ <tr>
+ <td>
+ <a href="audit.pl?action=vendor§ion=accounts&meta_number=<?lsmb entity.meta_number ?>&entity_id=<?lsmb entity.entity_id ?>">Accounts</a>
+ </td>
+ <td>
+ <a href="audit.pl?action=vendor§ion=locations&meta_number=<?lsmb entity.meta_number ?>&entity_id=<?lsmb entity.entity_id ?>&id=<?lsmb entity.id ?>">Addresses</a>
+ </td>
+ <td>
+ <a href="audit.pl?action=vendor§ion=contacts&meta_number=<?lsmb entity.meta_number ?>&entity_id=<?lsmb entity.entity_id ?>&id=<?lsmb entity.id ?>">Contacts</a>
+ </td>
+ </tr>
+ </table>
+ </div>
+
+ <div class="title">Audit report: Company information for vendor <?lsmb entity.name ?> </div>
+ <br/>
+ <br/>
+ <div class="audit">
+ Company Column
+ <table>
+ <?lsmb FOREACH event IN audit ?>
+ <tr>
+ <td>
+ <b><?lsmb event.col ?></b>
+ </td>
+ <?lsmb IF event.mode == 'UPDATE' ?>
+ <td class="mode"><span class="mode"><i>UPDATED</i></span> from</td>
+ <td class="old"> <span class="old"><?lsmb event.original ? event.original : 'NULL' ?></span> to </td class="old">
+ <td class="new"> <?lsmb event.replacement?> </span>
+ <?lsmb ELSIF event.mode == 'DELETE'?>
+ <td class="mode" colspan="3"><i>DELETED</i></td>
+ <?lsmb END?>
+ <td class="user">
+ by <?lsmb event.by?>
+ </td>
+ </tr>
+ <?lsmb END?>
+ </table>
+
+ </div>
+</body>
Added: addons/1.3/audit/trunk/UI/Audit/contact.html
===================================================================
--- addons/1.3/audit/trunk/UI/Audit/contact.html (rev 0)
+++ addons/1.3/audit/trunk/UI/Audit/contact.html 2010-04-26 23:05:25 UTC (rev 2991)
@@ -0,0 +1,30 @@
+<?lsmb PROCESS 'ui-header.html' ?>
+<?lsmb PROCESS 'elements.html' ?>
+
+<div class="title">Audit report: Contact information for vendor <?lsmb entity.name ?> </div>
+ <br/>
+ <br/>
+ <div class="audit">
+ Contact Column
+ <table>
+ <?lsmb FOREACH event IN audit ?>
+ <tr>
+ <td>
+ <b><?lsmb event.col ?></b>
+ </td>
+ <?lsmb IF event.mode == 'UPDATE' ?>
+ <td class="mode"><span class="mode"><i>UPDATED</i></span> from</td>
+ <td class="old"> <span class="old"><?lsmb event.original ? event.original : 'NULL' ?></span> to </td class="old">
+ <td class="new"> <?lsmb event.replacement?> </span>
+ <?lsmb ELSIF event.mode == 'DELETE'?>
+ <td class="mode" colspan="3"><i>DELETED</i></td>
+ <?lsmb END?>
+ <td class="user">
+ by <?lsmb event.by?>
+ </td>
+ </tr>
+ <?lsmb END?>
+ </table>
+
+ </div>
+</div>
Added: addons/1.3/audit/trunk/UI/Audit/location.html
===================================================================
--- addons/1.3/audit/trunk/UI/Audit/location.html (rev 0)
+++ addons/1.3/audit/trunk/UI/Audit/location.html 2010-04-26 23:05:25 UTC (rev 2991)
@@ -0,0 +1,23 @@
+<?lsmb PROCESS 'ui-header.html' ?>
+<?lsmb PROCESS 'elements.html' ?>
+
+<div class="title">Audit report: Location for vendor <?lsmb entity.name ?> </div>
+
+<div class="audit">
+<?lsmb FOREACH event IN audit ?>
+
+ <div class="event">
+ Location column
+ <span class="column"><?lsmb event.col ?></span> was
+ <?lsmb IF event.mode == 'UPDATE' ?>
+ <span class="mode">UPDATED</span> from
+ <span class="old"><?lsmb event.original?></span> to
+ <span class="new"><?lsmb event.replacement?></span>
+ <?lsmb ELSIF event.mode == 'DELETE'?>
+ <span class="mode">DELETED</span>
+ <?lsmb END?>
+ by <span class="user"><?lsmb event.by?></span>
+ </div>
+
+<?lsmb END?>
+</div>
\ No newline at end of file
Added: addons/1.3/audit/trunk/audit.pl
===================================================================
--- addons/1.3/audit/trunk/audit.pl (rev 0)
+++ addons/1.3/audit/trunk/audit.pl 2010-04-26 23:05:25 UTC (rev 2991)
@@ -0,0 +1,3 @@
+#!/usr/bin/perl
+
+require 'lsmb-request.pl';
Added: addons/1.3/audit/trunk/scripts/audit.pl
===================================================================
--- addons/1.3/audit/trunk/scripts/audit.pl (rev 0)
+++ addons/1.3/audit/trunk/scripts/audit.pl 2010-04-26 23:05:25 UTC (rev 2991)
@@ -0,0 +1,54 @@
+#!/usr/bin/perl
+
+=pod
+
+=head1 NAME
+
+LedgerSMB::Scripts::vendor - LedgerSMB class defining the Controller
+functions, template instantiation and rendering for vendor editing and display.
+
+=head1 SYOPSIS
+
+This module is the UI controller for the vendor DB access; it provides the
+View interface, as well as defines the Save vendor.
+Save vendor will update or create as needed.
+
+
+=head1 METHODS
+
+=cut
+
+package LedgerSMB::Scripts::audit;
+
+use LedgerSMB::Vendor::Audit;
+use Data::Dumper;
+
+# require 'lsmb-request.pl';
+
+sub vendor {
+ my ($request) = @_;
+
+ my $audit = new LedgerSMB::Vendor::Audit();
+
+ # print STDERR Dumper($request);
+ # corresponds to the various datatypes for audit
+ # Company, Entity, address, etc.
+
+ my $view = $request->{section};
+
+ if (!(defined($view))) {
+ $view = "vendors";
+ }
+
+ if (my $func = $audit->can($view) ) {
+ return $func->($audit,$request);
+ }
+ else {
+ $request->error($request->{_locale}->text("Vendor audit information not found."));
+ }
+}
+
+# Add other auditing here.
+
+
+1;
Added: addons/1.3/audit/trunk/sql/modules/audit/audit_helper.pm
===================================================================
--- addons/1.3/audit/trunk/sql/modules/audit/audit_helper.pm (rev 0)
+++ addons/1.3/audit/trunk/sql/modules/audit/audit_helper.pm 2010-04-26 23:05:25 UTC (rev 2991)
@@ -0,0 +1,112 @@
+package audit_helper;
+
+use strict;
+
+sub new {
+ my $class = shift;
+ my $self = {};
+ $self->{table} = shift;
+ $self->{arguments} = shift;
+ $self->{colnames} = [];
+ $self->{empty} = 1;
+ $self->{query} = "";
+ return bless $self, $class
+}
+
+sub prepare
+{
+ my $self = shift;
+ my $rv = ::spi_exec_query("SELECT * FROM audit.".$self->{table}." LIMIT 1");
+
+ if ($rv->{processed} == 0) {
+ # table is empty.
+ $self->{empty} = 1;
+ } else
+ {
+ $self->{empty} = 0;
+ # get table column names except for trigger ones
+ my @colnames = grep !/^trigger_/, keys %{$rv->{rows}[0]};
+ $self->{colnames} = ..hidden..;
+ }
+ $rv = undef;
+
+ # construct a query to fetch rows from tablelog
+ $self->{query} = "SELECT * FROM audit.".$self->{table};
+ my $where_clause = "";
+ foreach my $colname (keys %{$self->{arguments}}) {
+ $where_clause .= " AND " unless $where_clause eq "";
+ $where_clause .= sprintf "%s = %s", $colname, $self->{arguments}{$colname};
+ }
+ $self->{query}.=" WHERE ".$where_clause unless $where_clause eq "";
+}
+
+sub next_row
+{
+ my $self = shift;
+
+ # nothing to return for the empty table
+ return undef if ($self->{empty});
+
+
+ my $row;
+ my %fetched = {};
+ my @colnames = @{$self->{colnames}};
+ my $query = $self->{query};
+
+ ::elog(::DEBUG, "query: ".$query);
+ my $cr = ::spi_query($query);
+
+ # get one row at a time to avoid running out of memory
+ while (defined($row = ::spi_fetchrow($cr))) {
+ if (lc($row->{trigger_mode}) eq 'delete') {
+ # deal with deleted rows
+ my $result = {mode => uc($row->{trigger_mode}), by => $row->{trigger_user}};
+ foreach (@colnames) {
+ $result->{col} = $_;
+ $result->{original} = $row->{$_};
+ $result->{replacement} = $row->{$_};
+ ::elog(::DEBUG, "op ".$result->{mode}." colname ".$result->{col}." by ".$result->{by});
+ ::return_next($result);
+ }
+ } elsif (lc($row->{trigger_mode}) eq 'update') {
+ # deal with updated rows
+ my $ts = $row->{trigger_changed};
+ # check whether we have a matching row for the one that was fetched
+ if (defined $fetched{$ts}) {
+ # get the row that was added to the hash earlier
+ my $row2 = $fetched{$ts};
+ my ($old, $new);
+ if (lc($row->{trigger_tuple}) eq 'old') {
+ $old = $row;
+ $new = $row2;
+ } elsif (lc($row->{trigger_tuple}) eq 'new') {
+ $new = $row;
+ $old = $row2;
+ } else {
+ die "Wrong trigger_tuple value: ", $row->{trigger_tuple};
+ }
+ my $result = {mode => $old->{trigger_mode}, by => $old->{trigger_user}};
+ # compare column values
+ foreach my $colname (@colnames) {
+ if (($old->{$colname} ne $new->{$colname})) {
+ $result->{col} = $colname;
+ $result->{original} = $old->{$colname};
+ $result->{replacement} = $new->{$colname};
+ ::elog(::DEBUG, "op ".$result->{mode}." colname ".$result->{col}." by ".$result->{by});
+ ::elog(::DEBUG, "old value ".$result->{original}." new value ".$result->{replacement});
+ ::return_next($result);
+ }
+ }
+ # hint perl to release memory for the tuple in hash
+ $fetched{$ts} = undef;
+ } else {
+ # add a new row to the hash
+ $fetched{$ts} = $row;
+ }
+ }
+ $row = undef;
+ }
+ return undef;
+}
+
+1;
\ No newline at end of file
Added: addons/1.3/audit/trunk/sql/modules/audit/auditing.sql
===================================================================
--- addons/1.3/audit/trunk/sql/modules/audit/auditing.sql (rev 0)
+++ addons/1.3/audit/trunk/sql/modules/audit/auditing.sql 2010-04-26 23:05:25 UTC (rev 2991)
@@ -0,0 +1,125 @@
+-- This adds tracking to the vendor and customer tables.
+
+
+CREATE SCHEMA audit;
+REVOKE ALL ON SCHEMA audit FROM PUBLIC;
+
+SELECT * INTO audit.entity FROM entity LIMIT 0;
+SELECT * INTO audit.entity_credit_account FROM entity_credit_account LIMIT 0;
+SELECT * INTO audit.eca_to_location FROM eca_to_location LIMIT 0;
+SELECT * INTO audit.eca_to_contact FROM eca_to_contact LIMIT 0;
+SELECT * INTO audit.location FROM location LIMIT 0;
+SELECT * INTO audit.company FROM company LIMIT 0;
+
+-- entity table
+
+ALTER TABLE audit.entity ADD COLUMN trigger_mode VARCHAR(10);
+ALTER TABLE audit.entity ADD COLUMN trigger_tuple VARCHAR(5);
+ALTER TABLE audit.entity ADD COLUMN trigger_changed TIMESTAMPTZ;
+ALTER TABLE audit.entity ADD COLUMN trigger_id BIGINT;
+ALTER TABLE audit.entity ADD COLUMN trigger_user VARCHAR(32);
+
+
+-- customer table
+
+ALTER TABLE audit.company ADD COLUMN trigger_mode VARCHAR(10);
+ALTER TABLE audit.company ADD COLUMN trigger_tuple VARCHAR(5);
+ALTER TABLE audit.company ADD COLUMN trigger_changed TIMESTAMPTZ;
+ALTER TABLE audit.company ADD COLUMN trigger_id BIGINT;
+ALTER TABLE audit.company ADD COLUMN trigger_user VARCHAR(32);
+
+-- ECA table
+
+ALTER TABLE audit.entity_credit_account ADD COLUMN trigger_mode VARCHAR(10);
+ALTER TABLE audit.entity_credit_account ADD COLUMN trigger_tuple VARCHAR(5);
+ALTER TABLE audit.entity_credit_account ADD COLUMN trigger_changed TIMESTAMPTZ;
+ALTER TABLE audit.entity_credit_account ADD COLUMN trigger_id BIGINT;
+ALTER TABLE audit.entity_credit_account ADD COLUMN trigger_user VARCHAR(32);
+
+-- eca_to_location
+
+ALTER TABLE audit.eca_to_location ADD COLUMN trigger_mode VARCHAR(10);
+ALTER TABLE audit.eca_to_location ADD COLUMN trigger_tuple VARCHAR(5);
+ALTER TABLE audit.eca_to_location ADD COLUMN trigger_changed TIMESTAMPTZ;
+ALTER TABLE audit.eca_to_location ADD COLUMN trigger_id BIGINT;
+ALTER TABLE audit.eca_to_location ADD COLUMN trigger_user VARCHAR(32);
+
+-- eca_to_contact
+
+ALTER TABLE audit.eca_to_contact ADD COLUMN trigger_mode VARCHAR(10);
+ALTER TABLE audit.eca_to_contact ADD COLUMN trigger_tuple VARCHAR(5);
+ALTER TABLE audit.eca_to_contact ADD COLUMN trigger_changed TIMESTAMPTZ;
+ALTER TABLE audit.eca_to_contact ADD COLUMN trigger_id BIGINT;
+ALTER TABLE audit.eca_to_contact ADD COLUMN trigger_user VARCHAR(32);
+
+-- Location
+
+ALTER TABLE audit.location ADD COLUMN trigger_mode VARCHAR(10);
+ALTER TABLE audit.location ADD COLUMN trigger_tuple VARCHAR(5);
+ALTER TABLE audit.location ADD COLUMN trigger_changed TIMESTAMPTZ;
+ALTER TABLE audit.location ADD COLUMN trigger_id BIGINT;
+ALTER TABLE audit.location ADD COLUMN trigger_user VARCHAR(32);
+
+REVOKE ALL ON TABLE audit.entity FROM public;
+
+REVOKE ALL ON TABLE audit.company FROM public;
+
+REVOKE ALL ON TABLE audit.entity_credit_account FROM public;
+
+REVOKE ALL ON TABLE audit.eca_to_location FROM public;
+
+REVOKE ALL ON TABLE audit.eca_to_contact FROM public;
+
+REVOKE ALL ON TABLE audit.location FROM public;
+
+
+
+CREATE TRIGGER entity_log_chg
+ AFTER UPDATE OR DELETE ON entity
+ FOR EACH ROW EXECUTE PROCEDURE table_log('entity', 1, 'audit');
+
+CREATE TRIGGER company_log_chg
+ AFTER UPDATE OR DELETE ON company
+ FOR EACH ROW EXECUTE PROCEDURE table_log('company', 1, 'audit');
+
+CREATE TRIGGER eca_log_chg
+ AFTER UPDATE OR DELETE ON entity_credit_account
+ FOR EACH ROW EXECUTE PROCEDURE table_log('entity_credit_account', 1, 'audit');
+
+CREATE TRIGGER eca_to_location_log_chg
+ AFTER UPDATE OR DELETE ON eca_to_location
+ FOR EACH ROW EXECUTE PROCEDURE table_log('eca_to_location', 1, 'audit');
+
+CREATE TRIGGER location_log_chg
+ AFTER UPDATE OR INSERT OR DELETE ON location
+ FOR EACH ROW EXECUTE PROCEDURE table_log('location', 1, 'audit');
+
+CREATE TRIGGER eca_to_contact_log_chg
+ AFTER UPDATE OR DELETE ON eca_to_contact
+ FOR EACH ROW EXECUTE PROCEDURE table_log('eca_to_contact', 1, 'audit');
+
+
+ALTER FUNCTION table_log() SECURITY DEFINER;
+
+
+-- And now some stored procedures to select from the audit logs
+
+CREATE TYPE audit.display AS (
+ mode text, -- UPDATE or DELETE
+ col text, -- the altered column
+ original text, -- the original value
+ replacement text, -- the new value
+ by text -- The changing role.
+);
+
+
+CREATE TYPE audit.changed AS (
+ id int, -- the primary key, whatever it is
+ changed timestamptz
+);
+
+
+-- For epoch.pl
+
+CREATE LANGUAGE plperlu; -- Requires superuser privileges.
+\i vendor.pl -- Load the DB functions necessary for auditing.
Added: addons/1.3/audit/trunk/sql/modules/audit/menu.sql
===================================================================
--- addons/1.3/audit/trunk/sql/modules/audit/menu.sql (rev 0)
+++ addons/1.3/audit/trunk/sql/modules/audit/menu.sql 2010-04-26 23:05:25 UTC (rev 2991)
@@ -0,0 +1,56 @@
+/* This file sets up the menu entries needed for the reporting administrative interface.
+
+It will initially test for the presence of the Admin menu item; if it is not present, then it will create it.
+*/
+
+BEGIN;
+
+CREATE OR REPLACE FUNCTION auditing__menu_insert (
+
+) RETURNS boolean AS $body$
+
+ DECLARE
+ v_vendor int;
+ v_audit int;
+ r_attrs int;
+ BEGIN
+
+ SELECT id
+ INTO v_audit
+ FROM menu_node
+ WHERE label = 'Audit'
+ AND parent = (select id from menu_node where label = 'Vendors');
+
+ IF NOT FOUND THEN
+ select id into v_vendor from menu_node where label = 'Vendors';
+ v_audit := pg_catalog.nextval(pg_catalog.pg_get_serial_sequence('menu_node', 'id'));
+ INSERT INTO menu_node (id, label, parent, position) VALUES (v_audit, 'Audit', v_vendor, (SELECT max(position) from menu_node where parent = 30) + 1 );
+ END IF;
+
+
+ PERFORM * FROM menu_attribute WHERE node_id = v_audit;
+ IF NOT FOUND THEN
+ INSERT INTO menu_attribute (node_id, attribute, value) VALUES (v_audit, 'menu', 1);
+
+ END IF;
+
+ SELECT count(*) INTO r_attrs FROM menu_attribute WHERE node_id = v_audit;
+
+ IF r_attrs < 2 THEN
+ DELETE FROM menu_attribute WHERE node_id = v_audit;
+ INSERT INTO menu_attribute (node_id, attribute, value) VALUES (v_audit, 'action', 'vendor');
+ INSERT INTO menu_attribute (node_id, attribute, value) VALUES (v_audit, 'module', 'audit.pl');
+ END IF;
+
+ RETURN TRUE;
+
+ END;
+$body$ LANGUAGE PLPGSQL;
+
+
+SELECT auditing__menu_insert();
+
+DROP FUNCTION auditing__menu_insert();
+
+COMMIT;
+
Added: addons/1.3/audit/trunk/sql/modules/audit/vendor.pl
===================================================================
--- addons/1.3/audit/trunk/sql/modules/audit/vendor.pl (rev 0)
+++ addons/1.3/audit/trunk/sql/modules/audit/vendor.pl 2010-04-26 23:05:25 UTC (rev 2991)
@@ -0,0 +1,207 @@
+CREATE OR REPLACE FUNCTION audit.company(in_id INTEGER, in_entity_id INTEGER) RETURNS SETOF audit.display AS
+$$
+
+ BEGIN { strict->import(); }
+ use audit_helper;
+
+ my $row;
+ my ($id, $entity_id) = @_;
+
+ my $ah = new audit_helper('company', {'id' => $id, 'entity_id' => $entity_id});
+ $ah->prepare;
+ while (defined($row = $ah->next_row)) {
+ return_next($row);
+ }
+
+ return undef;
+$$ LANGUAGE plperlu STRICT;
+
+REVOKE EXECUTE ON audit.company(int, int) FROM PUBLIC;
+
+
+CREATE OR REPLACE FUNCTION audit.eca_to_contact(in_credit_id INTEGER, in_contact_class INTEGER) RETURNS SETOF audit.display AS
+$$
+
+ BEGIN { strict->import(); }
+ use audit_helper;
+
+ my $row;
+ my ($credit_id, $contact_class_id) = @_;
+
+ my $ah = new audit_helper('eca_to_contact', {'credit_id' => $credit_id,
+ 'contact_class_id' => $contact_class_id});
+ $ah->prepare;
+ while (defined($row = $ah->next_row)) {
+ return_next($row);
+ }
+
+ return undef;
+$$ LANGUAGE plperlu STRICT;
+
+CREATE OR REPLACE FUNCTION audit.eca_to_location(in_location_id INTEGER, in_credit_id INTEGER) RETURNS SETOF audit.display AS
+$$
+
+ BEGIN { strict->import(); }
+ use audit_helper;
+
+ my $row;
+ my ($location_id, $credit_id) = @_;
+
+ my $ah = new audit_helper('eca_to_location', {'location_id' => $location_id, 'credit_id' => $credit_id});
+ $ah->prepare;
+ while (defined($row = $ah->next_row)) {
+ return_next($row);
+ }
+
+ return undef;
+$$ LANGUAGE plperlu STRICT;
+
+CREATE OR REPLACE FUNCTION audit.entity(in_id INTEGER) RETURNS SETOF audit.display AS
+$$
+
+ BEGIN { strict->import(); }
+ use audit_helper;
+
+ my $row;
+ my $id = shift;
+
+ my $ah = new audit_helper('entity', {'id' => $id});
+ $ah->prepare;
+ while (defined($row = $ah->next_row)) {
+ return_next($row);
+ }
+
+ return undef;
+$$ LANGUAGE plperlu STRICT;
+
+CREATE OR REPLACE FUNCTION audit.entity_credit_account(in_id INTEGER, in_entity_id INTEGER, in_meta_number TEXT) RETURNS SETOF audit.display AS
+$$
+
+ BEGIN { strict->import(); }
+ use audit_helper;
+
+ my $row;
+ my ($id, $entity_id, $meta) = @_;
+
+ my $meta = "'$meta'::varchar"; # Intentional stringification
+
+ my $ah = new audit_helper('entity_credit_account', {'id' => $id,
+ 'entity_id' => $entity_id,
+ 'meta_number'=> $meta });
+ $ah->prepare;
+ while (defined($row = $ah->next_row)) {
+ return_next($row);
+ }
+
+ return undef;
+$$ LANGUAGE plperlu STRICT;
+
+CREATE OR REPLACE FUNCTION audit.location(in_id INTEGER) RETURNS SETOF audit.display AS
+$$
+
+ BEGIN { strict->import(); }
+ use audit_helper;
+
+ my $row;
+ my $id = shift;
+
+ my $ah = new audit_helper('location', {'id' => $id});
+ $ah->prepare;
+ while (defined($row = $ah->next_row)) {
+ return_next($row);
+ }
+
+ return undef;
+$$ LANGUAGE plperlu STRICT;
+
+CREATE TYPE audit.vendor_list AS (
+ id int,
+ entity_id int, --
+ name text, -- entity name
+ meta_number text, -- vendor number
+ class int, -- account class - one or two.
+ auditable boolean
+);
+
+
+CREATE OR REPLACE FUNCTION audit.vendors () RETURNS SETOF audit.vendor_list AS
+$body$
+
+ SELECT DISTINCT c.id,
+ c.entity_id,
+ c.legal_name,
+ v.vendornumber,
+ 1,
+ EXISTS(SELECT * FROM audit.company where id = c.id)
+ FROM company c,
+ vendor v
+ WHERE v.id = c.id
+ ORDER BY id ASC;
+
+$body$ LANGUAGE SQL;
+
+CREATE TYPE audit.location_return AS (
+ id integer,
+ line_one text,
+ line_two text,
+ line_three text,
+ city text,
+ state text,
+ country text,
+ created date,
+ active boolean,
+ location_class text
+);
+
+CREATE OR REPLACE FUNCTION audit.location_list(in_entity_id int) RETURNS SETOF audit.location_return
+AS $body$
+
+ SELECT l.id,
+ l.line_one,
+ l.line_two,
+ l.line_three,
+ l.city,
+ l.state,
+ c.name,
+ l.created,
+ l.active,
+ loc.class
+ FROM location l,
+ eca_to_location etl,
+ entity_credit_account eca,
+ country c,
+ location_class loc
+ WHERE eca.entity_id = $1
+ AND etl.credit_id = eca.id
+ AND l.id = etl.location_id
+ AND l.id IN (SELECT id FROM audit.location)
+ AND c.id = l.country_id
+ AND etl.location_class = loc.id;
+
+$body$ LANGUAGE SQL;
+
+
+CREATE TYPE audit.contact_return AS (
+ credit_id int,
+ contact_class_id int,
+ contact text,
+ description text,
+ contact_class text
+);
+
+
+CREATE OR REPLACE FUNCTION audit.contact_list(
+ in_entity_id int
+) RETURNS SETOF audit.contact_return AS $body$
+
+ SELECT etc.*,
+ cc.class
+ FROM eca_to_contact etc,
+ entity_credit_account eca,
+ contact_class cc
+ WHERE eca.entity_id = $1
+ AND etc.credit_id = eca.id
+ AND (etc.credit_id, etc.contact_class_id) IN (select credit_id, contact_class_id from audit.eca_to_contact where credit_id = etc.credit_id and trigger_mode in ('DELETE','UPDATE'))
+ AND cc.id = etc.contact_class_id;
+
+$body$ LANGUAGE SQL;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.