diff options
| -rw-r--r-- | LedgerSMB/Admin.pm | 179 | ||||
| -rwxr-xr-x | LedgerSMB/Session/DB.pm | 48 | ||||
| -rw-r--r-- | UI/Admin/delete_group.html | 0 | ||||
| -rw-r--r-- | UI/Admin/delete_user.html | 0 | ||||
| -rw-r--r-- | UI/Admin/edit_group.html | 13 | ||||
| -rw-r--r-- | UI/Admin/edit_user.html | 0 | ||||
| -rw-r--r-- | UI/Admin/login.html | 11 | ||||
| -rw-r--r-- | UI/Admin/main.html | 55 | ||||
| -rw-r--r-- | UI/Customer/customer.html | 139 | ||||
| -rw-r--r-- | UI/Reconciliation/approved.html | 28 | ||||
| -rw-r--r-- | UI/Reconciliation/correct.html | 0 | ||||
| -rw-r--r-- | UI/Reconciliation/corrections.html | 39 | ||||
| -rw-r--r-- | UI/Reconciliation/report.html | 52 | ||||
| -rw-r--r-- | UI/Vendor/vendor.html | 139 | ||||
| -rw-r--r-- | bin/admin.pl | 12 | ||||
| -rwxr-xr-x | import_members.pl | 44 | ||||
| -rw-r--r-- | scripts/admin.pl | 155 | ||||
| -rw-r--r-- | sql/modules/admin.sql | 403 |
18 files changed, 1265 insertions, 52 deletions
diff --git a/LedgerSMB/Admin.pm b/LedgerSMB/Admin.pm new file mode 100644 index 00000000..8e6c5dfd --- /dev/null +++ b/LedgerSMB/Admin.pm @@ -0,0 +1,179 @@ +package LedgerSMB::DBObject::Admin; + +use base LedgerSMB::DBObject; + +use LedgerSMB::DBObject::Location; +use LedgerSMB::DBObject::Employee; +use LedgerSMB::DBObject::Contact; + +sub save_user { + + my $self = shift @_; + + my $entity_id = shift @{ $self->exec_method( procname => "save_user" ) }; + $self->merge($entity_id); + + my $employee = LedgerSMB::DBObject::Employee->new(base=>$self, copy=>'list', + merge=>[ + 'salutation', + 'first_name', + 'last_name', + 'employeenumber', + ] + ); + + $employee->{entity_id} = $entity_id->{id}; + $employee->save_employee(); + + my $loc = LedgerSMB::DBObject::Location->new(base=>$self, copy=>'list', + merge=>[ + 'address1', + 'address2', + 'city', + 'state', + 'zipcode', + 'country', + 'companyname', + ] + ); + $loc->save_location(); + $loc->join_to_person(person=>$employee); + + + my $contact = LedgerSMB::DBObject::Contact->new(base=>$self, copy=>'list', + merge=>[ + 'workphone', + 'homephone', + 'email', + ] + ); + + $contact->save_homephone(person=>$employee); + $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 %active_roles; + for my $role (@{$user_roles}) { + + # These are our user's roles. + + $active_roles{$role} = 1; + } + + my $status; + + for my $role ( @{ $roles } ) { + + # These roles are were ALL checked on the page, so they're the active ones. + + if ($active_roles{$role} && $self->{incoming_roles}->{$role}) { + + # do nothing. + } + elsif ($active_roles{$role} && !($self->{incoming_roles}->{$role} )) { + + # do remove function + $status = $self->exec_method(procname => "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", + args=>[ $self->{ modifying_user }, $role ] + ); + } + } +} + +sub save_group { + + my $self = shift @_; + + my $existant = shift @{ $self->exec_method (procname=> "is_group", args=>[$self->{modifying_group}]) }; + + my $group = shift @{ $self->exec_method (procname=> "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", + args=>[ $self->{ group_name } ] + ); + + my %active_roles; + for my $role (@{$user_roles}) { + + # These are our user's roles. + + $active_roles{$role} = 1; + } + + my $status; + + for my $role ( @{ $roles } ) { + + # These roles are were ALL checked on the page, so they're the active ones. + + if ($active_roles{$role} && $self->{incoming_roles}->{$role}) { + + # we don't need to do anything. + } + elsif ($active_roles{$role} && !($self->{incoming_roles}->{$role} )) { + + # do remove function + $status = $self->exec_method( + procname => "remove_group_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_group_to_role", + args=>[ $self->{ modifying_user }, $role ] + ); + } + } +} + + +sub delete_user { + + my $self = shift @_; + + my $status = shift @{ $self->exec_method(procname=>'delete_user', args=>[$self->{modifying_user}]) }; + + if ($status) { + + return 1; + } else { + + my $error = LedgerSMB::Error->new("Delete user failed."); + $error->set_status($status); + return $error; + } +} + +sub delete_group { + + my $self = shift @_; + + my $status = shift @{ $self->exec_method(procname=>'delete_group', args=>[$self->{groupname}])}; + + if ($status) { + + return 1; + } else { + + my $error = LedgerSMB::Error->new("Delete group failed."); + $error->set_status($status); + return $error; + } +} + +1;
\ No newline at end of file diff --git a/LedgerSMB/Session/DB.pm b/LedgerSMB/Session/DB.pm index f94f107f..e4884015 100755 --- a/LedgerSMB/Session/DB.pm +++ b/LedgerSMB/Session/DB.pm @@ -41,17 +41,17 @@ sub session_check { my $checkQuery = $dbh->prepare( "SELECT u.username, s.transaction_id - FROM session as s, users as u - WHERE s.session_id = ? - AND s.users_id = u.id - AND s.last_used > now() - ?::interval" + FROM session as s, users as u + WHERE s.session_id = ? + AND s.users_id = u.id + AND s.last_used > now() - ?::interval" ); my $updateAge = $dbh->prepare( "UPDATE session - SET last_used = now(), - transaction_id = ? - WHERE session_id = ?;" + SET last_used = now(), + transaction_id = ? + WHERE session_id = ?;" ); #must be an integer @@ -150,9 +150,9 @@ sub session_create { # TODO Change this to use %myconfig my $deleteExisting = $dbh->prepare( "DELETE - FROM session - WHERE session.users_id = (select id from users where username = ?) - AND age(last_used) > ?::interval" + FROM session + WHERE session.users_id = (select id from users where username = ?) + AND age(last_used) > ?::interval" ); my $seedRandom = $dbh->prepare("SELECT setseed(?);"); @@ -162,9 +162,9 @@ sub session_create { my $createNew = $dbh->prepare( "INSERT INTO session (session_id, users_id, token, transaction_id) - VALUES(?, (SELECT id - FROM users - WHERE username = ?), ?, ?);" + VALUES(?, (SELECT id + FROM users + WHERE username = ?), ?, ?);" ); # this is assuming that $form->{login} is safe, which might be a bad assumption @@ -217,9 +217,9 @@ sub session_destroy { my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH}; my $deleteExisting = $dbh->prepare( " - DELETE FROM session - WHERE users_id = (select id from users where username = ?) - " ); + DELETE FROM session + WHERE users_id = (select id from users where username = ?) + " ); $deleteExisting->execute($login) || $form->dberror( @@ -243,9 +243,9 @@ sub password_check { my $fetchPassword = $dbh->prepare( "SELECT u.username, uc.password, uc.crypted_password - FROM users as u, users_conf as uc - WHERE u.username = ? - AND u.id = uc.id;" + FROM users as u, users_conf as uc + WHERE u.username = ? + AND u.id = uc.id;" ); $fetchPassword->execute($username) @@ -269,11 +269,11 @@ sub password_check { #password was good, convert to md5 password and null crypted my $updatePassword = $dbh->prepare( "UPDATE users_conf - SET password = md5(?), - crypted_password = null - FROM users - WHERE users_conf.id = users.id - AND users.username = ?;" + SET password = md5(?), + crypted_password = null + FROM users + WHERE users_conf.id = users.id + AND users.username = ?;" ); $updatePassword->execute( $password, $username ) diff --git a/UI/Admin/delete_group.html b/UI/Admin/delete_group.html new file mode 100644 index 00000000..e69de29b --- /dev/null +++ b/UI/Admin/delete_group.html diff --git a/UI/Admin/delete_user.html b/UI/Admin/delete_user.html new file mode 100644 index 00000000..e69de29b --- /dev/null +++ b/UI/Admin/delete_user.html diff --git a/UI/Admin/edit_group.html b/UI/Admin/edit_group.html new file mode 100644 index 00000000..11eb98dc --- /dev/null +++ b/UI/Admin/edit_group.html @@ -0,0 +1,13 @@ +<?lsmb import base.html?> + +<?lsmb if user.username ?> + + <center><b>Edit User</b></center> + +<?lsmb else ?> + + <center><b>Create User</b></center> + +<?lsmb endif ?> + +<form name="submit_user" action="/admin.pl?"></form>
\ No newline at end of file diff --git a/UI/Admin/edit_user.html b/UI/Admin/edit_user.html new file mode 100644 index 00000000..e69de29b --- /dev/null +++ b/UI/Admin/edit_user.html diff --git a/UI/Admin/login.html b/UI/Admin/login.html new file mode 100644 index 00000000..cf48b6ec --- /dev/null +++ b/UI/Admin/login.html @@ -0,0 +1,11 @@ +<?lsmb include index.html ?> + +<?lsmb block 'main'?> + + <div name="login" id="login"> + + + + </div> + +<?lsmb end ?>
\ No newline at end of file diff --git a/UI/Admin/main.html b/UI/Admin/main.html new file mode 100644 index 00000000..56f4b2f8 --- /dev/null +++ b/UI/Admin/main.html @@ -0,0 +1,55 @@ +<?lsmb block main?> +<div class="admin"> + + <!-- first, a list of all active users --> + + <center> + <strong><font size="16"> Administration</font></strong> + </center> + + <?lsmb if message?> + <strong><font color="red"><?lsmb message?></font></strong> + <?lsmb end?> + <form name="userlist" method="POST" action="admin.pl"> + <table> + <tr> + <td></td> + <td>User ID</td> + <td>Username</td> + <td>Active</td> + <td>Date Added</td> + </tr> + + <?lsmb FOREACH user = users.all ?> + + <tr> + <td></td> + <td> + <a href="/admin.pl?action=edit_user&user=<?lsmb user.id?>"> + <?lsmb user.id?> + </a> + </td> + <td> + <?lsmb user.username?> + </td> + <td> + <?lsmb user.active?> + </td> + <td> + <?lsmb user.creation_date?> + </td> + + </tr> + <?lsmb end?> + </table> + + <hr/> + <div class="buttons"> + <button name="method" value="new_user">New User</button> + <button name="method" value="new_group">New Group</button> + <button name="method" value="delete_user">Delete User</button> + <button name="method" value="delete_group">Delete Group</button> + </div> + </form> +</div> +<?end?>
\ No newline at end of file diff --git a/UI/Customer/customer.html b/UI/Customer/customer.html new file mode 100644 index 00000000..fd0933ca --- /dev/null +++ b/UI/Customer/customer.html @@ -0,0 +1,139 @@ +<div class="customer"> + + <font size="17"><?lsmb customer.name?></font> + +<form name="customer" method="POST" action="vendor.pl"> + <table> + + <tr> + <td><b>Shipping Address</b></td> + + <td><b>Billing Address (if different)</p></td> + + </tr> + + <hr/> + + <?lsmb FOREACH entry = customer.ordered ?> + <tr> + <td> + <label for="shipping_<?entry?>"><?entry?>:</label> + <input id="shipping_<?entry?>" type="textarea" name="shipping_<?entry?>" value="<? customer.entry ?>"> + </td> + <td> + <input type="textarea" name="billing_<? entry ?>" value="<? customer.entry ?>"> + </td> + </tr> + <?lsmb end ?> + <tr> + <td> + <label for="startdate">Starting Date:</label> + <input id="startdate" type="textarea" name="startdate" value="<? customer.startdate ?>"> + </td> + <td> + <label for="enddate">End date:</label> + <input id="enddate" type="textarea" name="startdate" value="<? customer.enddate ?>"> + </td> + </tr> + <tr> + <td> + <label for"creditlimit">Credit Limit:</label> + <input id="creditlimit" name="creditlimit" type="textarea" value="<?customer.creditlimit?>"> + </td> + <td> + <label for="terms">Terms:</label> + <input id="terms" name="terms" type="textarea" value="<?customer.terms?>"> days + </td> + <td> + <label for="discount">Discount %:</label> + <input id="discount" name="discount" type="textarea" value="<?customer.discount?>"> + </td> + </tr> + <tr> + + <td> + <label for="taxnumber">Tax Number/SSN:</label> + <input id="taxnumber" name="taxnumber" type="textarea" value="<?customer.taxnumber?>"> + </td> + <td> + <label for"gifi_accno">Subcontract GFI:</label> + <input id="gifi_accno" name="gifi_accno" type="textarea" value="<?customer.gifi_accno?>"> + </td> + <td> + <label for"sic_code">SIC Code:</label> + <input id="sic_code" name="sic_code" type="textarea" value="<?customer.sic_code?>"> + </td> + </tr> + + <tr> + + <td></td> + <td> + <label for"bic">BIC:</label> + <input id="bic" name="bic" type="textarea" value="<?customer.bic?>"> + </td> + <td> + <label for="iban">IBAN:</label> + <input id="iban" name="iban" type="textarea" value="<?customer.iban?>"> + </td> + </tr> + + <tr> + <td> + <label for="notes">Notes:</label><br/> + <textarea name="notes" rows="3" cols="40" id="notes"> + <?customer.notes?> + </textarea> + </td> + </tr> + + <tr> + <hr/> + <br/> + <td> + <button class="submit" type="submit" name="action" value="save" accesskey="S" title="Save [Alt-S]">Save</button> + </td> + <td> + <button class="submit" + type="submit" + name="action" + value="ap_transaction" + accesskey="A" + title="AP Transaction [Alt-A]">AP Transaction</button> + </td> + <td> + <button class="submit" + type="submit" + name="action" + value="customer_invoice" + accesskey="I" + title="customer Invoice [Alt-I]">customer Invoice</button> + </td> + <td> + <button class="submit" + type="submit" + name="action" + value="purchase_order" + accesskey="O" + title="Purchase Order [Alt-O]">Purchase Order</button> + </td> + <td> + <button class="submit" + type="submit" + name="action" + value="rfq" + accesskey="Q" + title="RFQ [Alt-Q]">RFQ</button> + </td> + <td> + <button class="submit" + type="submit" + name="action" + value="pricelist" + accesskey="P" + title="Pricelist [Alt-P]">Pricelist</button> + </td> + </tr> + </table> + </form> +</div>
\ No newline at end of file diff --git a/UI/Reconciliation/approved.html b/UI/Reconciliation/approved.html new file mode 100644 index 00000000..16afc6cc --- /dev/null +++ b/UI/Reconciliation/approved.html @@ -0,0 +1,28 @@ +<div> + <center> + Bank statement for <?lsmbaccount?> on <?lsmbdate?> has been approved! + </center> +</div> + +<table border=0 style="opacity:50%;"> + + <tr> + <td>Clear date</td> + <td>Transaction Type</td> + <td>Our Balance</td> + <td>Their Balance</td> + <td>Error Corrections</td> + <td>Error Code</td> + </tr> + + <?lsmb FOREACH row = records ?> + <tr> + <td><?lsmb row.clear_time ?></td> + <td><?lsmb row.transaction_type ?> </td> + <td><?lsmb row.our_balance ?></td> + <td><?lsmb row.their_balance?></td> + <td><?lsmb row.corrections ?></td> + <td><?lsmb row.errorcode ?></td> + </tr> + <?lsmb END ?> +</table>
\ No newline at end of file diff --git a/UI/Reconciliation/correct.html b/UI/Reconciliation/correct.html new file mode 100644 index 00000000..e69de29b --- /dev/null +++ b/UI/Reconciliation/correct.html diff --git a/UI/Reconciliation/corrections.html b/UI/Reconciliation/corrections.html new file mode 100644 index 00000000..bdf7424a --- /dev/null +++ b/UI/Reconciliation/corrections.html @@ -0,0 +1,39 @@ +<table border=0> + <tr> + <td>Clear date</td> + <td>Transaction Type</td> + <td>Our Balance</td> + <td>Their Balance</td> + </tr> + <tr> + <td><?lsmb entry.clear_time ?></td> + <td><?lsmb entry.transaction_type ?> </td> + <td><?lsmb entry.our_balance ?></td> + <td><?lsmb entry.their_balance?></td> + </tr> +</table> + +<?lsmb IF NOT corrections ?> +Corrections: +<table border=0> + <?lsmb FOREACH row = corrections ?> + <tr> + <td> + <div> + <span><?lsmbrow.user?> at <?lsmbrow.insert_time?></span> + <div> + <?lsmbrow.reason?> + </div> + </div> + </td> + </tr> + <?lsmbEND?> + +</table> +<?lsmbELSE?> + +<div> + No corrections found. +</div> + +<?lsmbEND?>
\ No newline at end of file diff --git a/UI/Reconciliation/report.html b/UI/Reconciliation/report.html new file mode 100644 index 00000000..cd6c8972 --- /dev/null +++ b/UI/Reconciliation/report.html @@ -0,0 +1,52 @@ +<center>Reconciliation Report for [% total.account %] for the month of [%total.month%]</center> + +<center> + [%IF total.errorcode != 0 %] + <div style="color:blue; border-style:solid; border-width:1px; border-color: blue;"> + [%ELSE%] + <div style="color:red; border-style:solid; border-width:1px; border-color: blue;"> + [%END%] + Our Balance: total.our_balance | Bank Balance: total.their_balance + </div> +</center> + +<center>Report generated by [% total.user %]</center> + +[% if recon.error %] +<div style="border-color:red; border-width:1px; border-style:solid; margin:3px;" > + [% recon.error %] +</div> +[%end%] + +<table border=0> + + <tr> + <td>Clear date</td> + <td>Transaction Type</td> + <td>Our Balance</td> + <td>Their Balance</td> + <td>Error Corrections</td> + <td>Error Code</td> + </tr> + + [% FOREACH row = records %] + [%IF row.errorcode != 0 %] + <tr style="background-color:red;"> + [% ELSIF row.id = corrected %] + <tr style="background-color:yellow;"> + [%ELSE%] + <tr> + [%END%] + <td>[% row.clear_time %]</td> + <td>[% row.transaction_type %] </td> + <td>[% row.our_balance %]</td> + <td>[% row.their_balance%]</td> + <td>[% row.corrections %]</td> + [% IF row.errorcode > 0 %] + <td>[% row.errorcode %] <a href="/reconciliation.pl?corrections&entry=[%row.entry_id%]">View Corrections</a> </td> + [%ELSE%] + <td>0</td> + [%END%] + </tr> + [% END %] +</table>
\ No newline at end of file diff --git a/UI/Vendor/vendor.html b/UI/Vendor/vendor.html new file mode 100644 index 00000000..c5753409 --- /dev/null +++ b/UI/Vendor/vendor.html @@ -0,0 +1,139 @@ +<div class="vendor"> + + <font size="17"><?lsmb vendor.name?></font> + +<form name="vendor"> + <table> + + <tr> + <td><b>Shipping Address</b></td> + + <td><b>Billing Address (if different)</p></td> + + </tr> + + <hr/> + + <?lsmb FOREACH entry = vendor.ordered ?> + <tr> + <td> + <label for="shipping_<?entry?>"><?entry?>:</label> + <input id="shipping_<?entry?>" type="textarea" name="shipping_<?entry?>" value="<? vendor.entry ?>"> + </td> + <td> + <input type="textarea" name="billing_<? entry ?>" value="<? vendor.entry ?>"> + </td> + </tr> + <?lsmb end ?> + <tr> + <td> + <label for="startdate">Starting Date:</label> + <input id="startdate" type="textarea" name="startdate" value="<? vendor.startdate ?>"> + </td> + <td> + <label for="enddate">End date:</label> + <input id="enddate" type="textarea" name="startdate" value="<? vendor.enddate ?>"> + </td> + </tr> + <tr> + <td> + <label for"creditlimit">Credit Limit:</label> + <input id="creditlimit" name="creditlimit" type="textarea" value="<?vendor.creditlimit?>"> + </td> + <td> + <label for="terms">Terms:</label> + <input id="terms" name="terms" type="textarea" value="<?vendor.terms?>"> days + </td> + <td> + <label for="discount">Discount %:</label> + <input id="discount" name="discount" type="textarea" value="<?vendor.discount?>"> + </td> + </tr> + <tr> + + <td> + <label for="taxnumber">Tax Number/SSN:</label> + <input id="taxnumber" name="taxnumber" type="textarea" value="<?vendor.taxnumber?>"> + </td> + <td> + <label for"gifi_accno">Subcontract GFI:</label> + <input id="gifi_accno" name="gifi_accno" type="textarea" value="<?vendor.gifi_accno?>"> + </td> + <td> + <label for"sic_code">SIC Code:</label> + <input id="sic_code" name="sic_code" type="textarea" value="<?vendor.sic_code?>"> + </td> + </tr> + + <tr> + + <td></td> + <td> + <label for"bic">BIC:</label> + <input id="bic" name="bic" type="textarea" value="<?vendor.bic?>"> + </td> + <td> + <label for="iban">IBAN:</label> + <input id="iban" name="iban" type="textarea" value="<?vendor.iban?>"> + </td> + </tr> + + <tr> + <td> + <label for="notes">Notes:</label><br/> + <textarea name="notes" rows="3" cols="40" id="notes"> + <?vendor.notes?> + </textarea> + </td> + </tr> + + <tr> + <hr/> + <br/> + <td> + <button class="submit" type="submit" name="action" value="save" accesskey="S" title="Save [Alt-S]">Save</button> + </td> + <td> + <button class="submit" + type="submit" + name="action" + value="ap_transaction" + accesskey="A" + title="AP Transaction [Alt-A]">AP Transaction</button> + </td> + <td> + <button class="submit" + type="submit" + name="action" + value="vendor_invoice" + accesskey="I" + title="Vendor Invoice [Alt-I]">Vendor Invoice</button> + </td> + <td> + <button class="submit" + type="submit" + name="action" + value="purchase_order" + accesskey="O" + title="Purchase Order [Alt-O]">Purchase Order</button> + </td> + <td> + <button class="submit" + type="submit" + name="action" + value="rfq" + accesskey="Q" + title="RFQ [Alt-Q]">RFQ</button> + </td> + <td> + <button class="submit" + type="submit" + name="action" + value="pricelist" + accesskey="P" + title="Pricelist [Alt-P]">Pricelist</button> + </td> + </tr> + </table> + </form> +</div>
\ No newline at end of file diff --git a/bin/admin.pl b/bin/admin.pl index 5a89da02..c1fa2156 100644 --- a/bin/admin.pl +++ b/bin/admin.pl @@ -224,12 +224,12 @@ sub list_users { my $fetchMembers = $dbh->selectall_arrayref( "SELECT uc.name, uc.company, uc.templates, - uc.dbuser, uc.dbdriver, uc.dbname, - uc.dbhost, u.username - FROM users as u, users_conf as uc - WHERE u.id = uc.id - AND u.id > 1 - ORDER BY u.username;", { Slice => {} } + uc.dbuser, uc.dbdriver, uc.dbname, + uc.dbhost, u.username + FROM users as u, users_conf as uc + WHERE u.id = uc.id + AND u.id > 1 + ORDER BY u.username;", { Slice => {} } ); my @memberArray = (); diff --git a/import_members.pl b/import_members.pl index a6aec9de..7a686774 100755 --- a/import_members.pl +++ b/import_members.pl @@ -150,18 +150,18 @@ sub save_member { my $userConfUpdate = $dbh->prepare( "UPDATE users_conf - SET acs = ?, address = ?, businessnumber = ?, - company = ?, countrycode = ?, currency = ?, - dateformat = ?, dbdriver = ?, - dbhost = ?, dbname = ?, dboptions = ?, - dbpasswd = ?, dbport = ?, dbuser = ?, - email = ?, fax = ?, menuwidth = ?, - name = ?, numberformat = ?, crypted_password = ?, - print = ?, printer = ?, role = ?, - sid = ?, signature = ?, stylesheet = ?, - tel = ?, templates = ?, timeout = ?, - vclimit = ? - WHERE id = ?;" + SET acs = ?, address = ?, businessnumber = ?, + company = ?, countrycode = ?, currency = ?, + dateformat = ?, dbdriver = ?, + dbhost = ?, dbname = ?, dboptions = ?, + dbpasswd = ?, dbport = ?, dbuser = ?, + email = ?, fax = ?, menuwidth = ?, + name = ?, numberformat = ?, crypted_password = ?, + print = ?, printer = ?, role = ?, + sid = ?, signature = ?, stylesheet = ?, + tel = ?, templates = ?, timeout = ?, + vclimit = ? + WHERE id = ?;" ); $userConfUpdate->execute( @@ -188,16 +188,16 @@ sub save_member { my $userConfInsert = $dbh->prepare( "INSERT INTO users_conf(acs, address, businessnumber, - company, countrycode, currency, - dateformat, dbdriver, - dbhost, dbname, dboptions, dbpasswd, - dbport, dbuser, email, fax, menuwidth, - name, numberformat, print, printer, role, - sid, signature, stylesheet, tel, templates, - timeout, vclimit, id, crypted_password) - VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, - ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, - ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);" + company, countrycode, currency, + dateformat, dbdriver, + dbhost, dbname, dboptions, dbpasswd, + dbport, dbuser, email, fax, menuwidth, + name, numberformat, print, printer, role, + sid, signature, stylesheet, tel, templates, + timeout, vclimit, id, crypted_password) + VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, + ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, + ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);" ); $userConfInsert->execute( diff --git a/scripts/admin.pl b/scripts/admin.pl new file mode 100644 index 00000000..64fefcb9 --- /dev/null +++ b/scripts/admin.pl @@ -0,0 +1,155 @@ +package LedgerSMB::Scripts::Admin; + +use LedgerSMB::Template; +use LedgerSMB::DBObject::Admin; + +sub new_user { + + # uses the same page as create_user, only pre-populated. + my ($class, $request) = @_; + my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all'); + + if ($request->type() == 'POST') { + + # do the save stuff + + my $entity = $admin->save_user(); + + + my $template = LedgerSMB::Template->new( user => $user, + template => 'admin/edit_user.html', language => $user->{language}, + format => 'html'); + + $template->render($entity); + } else { + + my $template = LedgerSMB::Template->new( user => $user, + template => 'admin/edit_user.html', language => $user->{language}, + format => 'html'); + + $template->render(); + } +} + +sub edit_user { + + # uses the same page as create_user, only pre-populated. + my ($class, $request) = @_; + my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'user_id'); + + my $edited_user = $admin->get_entire_user(); + my $all_roles = $admin->role_list(); + + my $template = LedgerSMB::Template->new( user => $user, + template => 'admin/edit_user.html', language => $user->{language}, + format => 'html'); + + $template->render($edited_user, $all_roles); +} + +sub edit_group { + + my ($class, $request) = @_; + my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all'); + + my $all_roles = $admin->role_list(); + my $group = $admin->get_group(); + + my $template = LedgerSMB::Template->new( user => $user, + template => 'admin/edit_group.html', language => $user->{language}, + format => 'html'); + + $template->render($all_roles); +} + +sub create_group { + + my ($class, $request) = @_; + my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all'); + + my $all_roles = $admin->role_list(); + + my $template = LedgerSMB::Template->new( user => $user, + template => 'admin/edit_group.html', language => $user->{language}, + format => 'html'); + + $template->render($all_roles); +} + +sub delete_group { + + my ($class, $request) = @_; + + my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all'); + + # requires the field modifying_user to be set. + + my $status = $admin->delete_group($request->{modifying_user}); + + # status can either be 1, or an error. + # if there's an error, $status->throw() is called by admin.pm. Or possibly + # in the template itself. + + my $template = LedgerSMB::Template->new ( user=>$user, + template=>'admin/delete_group.html', language=>$user->{language}, + format=>'html'); + + $template->render($status); +} + +sub delete_user { + + my ($class, $request) = @_; + + my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all'); + + # requires the field modifying_user to be set. + + my $status = $admin->delete_user($request->{modifying_user}); + + # status can either be 1, or an error. + # if there's an error, $status->throw() is called by admin.pm. Or possibly + # in the template itself. + + my $template = LedgerSMB::Template->new ( user=>$user, + template=>'admin/delete_user.html', language=>$user->{language}, + format=>'html'); + + $template->render($status); +} + +sub new_user { + + my ($class, $request) = @_; + + my $template = LedgerSMB::Template->new( user=>$user, + template=>'admin/new_user.html', language=>$user->{language}, + format=>'html'); + + $template->render(); +} + +sub new_group { + + my ($class, $request) = @_; + + my $template = LedgerSMB::Template->new( user=>$user, + template=>'admin/new_group.html', language=>$user->{language}, + format=>'html'); + + $template->render(); +} + +sub __default { + + my ($class, $request) = @_; + + # check for login + my $template; + $template = LedgerSMB::Template->new( user=>$user, + template=>'admin/main.html', language=>$user->{language}, + format=>'html'); + $template->render(); +} + +1;
\ No newline at end of file diff --git a/sql/modules/admin.sql b/sql/modules/admin.sql new file mode 100644 index 00000000..103c10c1 --- /dev/null +++ b/sql/modules/admin.sql @@ -0,0 +1,403 @@ +CREATE OR REPLACE FUNCTION admin_add_user_to_role(in_user TEXT, in_role TEXT) returns INT AS $$ + + declare + stmt TEXT; + a_role name; + a_user name; + BEGIN + + -- Issue the grant + select rolname into a_role from pg_roles where rolname = in_role; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot grant permissions of a non-existant role.'; + END IF; + + select rolname into a_user from pg_roles where rolname = in_user; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot grant permissions to a non-existant user.'; + END IF; + + stmt := 'GRANT '|| in_role ||' to '|| in_user; + + EXECUTE stmt; + + return 1; + END; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_remove_user_from_role(in_user TEXT, in_role TEXT) returns INT AS $$ + + declare + stmt TEXT; + a_role name; + a_user name; + BEGIN + + -- Issue the grant + select rolname into a_role from pg_roles where rolname = in_role; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot revoke permissions of a non-existant role.'; + END IF; + + select rolname into a_user from pg_roles where rolname = in_user; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot revoke permissions from a non-existant user.'; + END IF; + + stmt := 'REVOKE '|| in_role ||' FROM '|| in_user; + + EXECUTE stmt; + + return 1; + END; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_add_function_to_group(in_func TEXT, in_role TEXT) returns INT AS $$ + + declare + stmt TEXT; + a_role name; + a_user name; + BEGIN + + -- Issue the grant + select rolname into a_role from pg_roles where rolname = in_role; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot grant permissions of a non-existant role.'; + END IF; + + select rolname into a_user from pg_roles where rolname = in_user; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot grant permissions to a non-existant user.'; + END IF; + + stmt := 'GRANT EXECUTE ON FUNCTION '|| in_func ||' to '|| in_role; + + EXECUTE stmt; + + return 1; + END; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_remove_function_from_group(in_func TEXT, in_role TEXT) returns INT AS $$ + + declare + stmt TEXT; + a_role name; + a_user name; + BEGIN + + -- Issue the grant + select rolname into a_role from pg_roles where rolname = in_role; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot revoke permissions of a non-existant role.'; + END IF; + + select rolname into a_user from pg_roles where rolname = in_user; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot revoke permissions from a non-existant function.'; + END IF; + + stmt := 'REVOKE EXECUTE ON FUNCTION '|| in_func ||' FROM '|| in_role; + + EXECUTE stmt; + + return 1; + END; + + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_add_table_to_group(in_table TEXT, in_role TEXT, in_perm TEXT) returns INT AS $$ + + declare + stmt TEXT; + a_role name; + a_user name; + BEGIN + + -- Issue the grant + select rolname into a_role from pg_roles where rolname = in_role; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot grant permissions of a non-existant role.'; + END IF; + + select table_name into a_table from information_schema.tables + where table_schema NOT IN ('information_schema','pg_catalog','pg_toast') + and table_type='BASE TABLE' + and table_name = in_table; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot grant permissions to a non-existant table.'; + END IF; + + if lower(in_perm) not in ('select','insert','update','delete') THEN + raise exception 'Cannot add unknown permission'; + END IF; + + stmt := 'GRANT '|| in_perm|| 'ON TABLE '|| in_table ||' to '|| in_role; + + EXECUTE stmt; + + return 1; + END; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_remove_table_from_group(in_table TEXT, in_role TEXT) returns INT AS $$ + + declare + stmt TEXT; + a_role name; + a_table text; + BEGIN + + -- Issue the grant + select rolname into a_role from pg_roles where rolname = in_role; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot revoke permissions of a non-existant role.'; + END IF; + + SELECT table_schema, table_name from + + select table_name into a_table from information_schema.tables + where table_schema NOT IN ('information_schema','pg_catalog','pg_toast') + and table_type='BASE TABLE' + and table_name = in_table; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Cannot revoke permissions from a non-existant table.'; + END IF; + + stmt := 'REVOKE '|| in_role ||' FROM '|| in_user; + + EXECUTE stmt; + + return 1; + END; + +$$ language 'plpgsql'; + +create or replace function admin_get_user(in_user TEXT) returns setof user as $$ + + DECLARE + a_user user; + BEGIN + + select * into a_user from user where username = in_user; + IF NOT FOUND THEN + RAISE EXCEPTION 'cannot find user %', in_user; + END IF; + + return a_user; + + END; +$$ language plpgsql; + +create or replace function admin_get_roles_for_user(in_user TEXT) returns setof lsmb_roles as $$ + + declare + u_role lsmb_roles; + a_user user; + begin + select * into a_user from admin_get_user(in_user); + + FOR u_role IN select * from lsmb_roles WHERE user = a_user.id LOOP + + RETURN NEXT a_role; + + END LOOP; + RETURN; + end; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_save_user( + in_id int, + in_username text, + in_password TEXT, + in_dbname TEXT, + in_host TEXT, + in_port TEXT +) returns int AS $$ + DECLARE + + a_user user; + v_entity_id int; + p_id int; + l_id int; + stmt text; + BEGIN + + select * into a_user from user where id = in_id; + + IF NOT FOUND THEN + -- Insert cycle + + --- First, create an entity. + + if admin_is_user(in_username) then + + -- uhm, this is bad. + RAISE EXCEPTION + "Fatal exception: Username already exists in Postgres; not + a valid lsmb user."; + end if; + + v_entity_id := nextval('entity_id_seq'); + + INSERT INTO entity (id, name, entity_class) VALUES ( + v_entity_id, + in_first_name || ' ' || in_last_name, + 3 + ); + + -- create an actual user + insert into users (name, entity_id) VALUES ( + in_username, + v_entity_id + ); + + insert into user_connection (entity_id, database, host, port) + VALUES ( + v_entity_id, + in_database, + in_host, + in_port + ); + + -- Finally, issue the create user statement + + stmt := $$CREATE USER $$||in_username||$$WITH ENCRYPTED PASSWORD '$$||in_password||$$;'$$; + execute stmt; + + return v_entity_id; + + ELSIF FOUND THEN + + -- update cycle + + -- Only update if it's changed. Wewt. + UPDATE entity SET name = in_first_name || ' ' || in_last_name + WHERE entity_id = a_user.entity_id and + name <> in_first_name || ' ' || in_last_name; + + stmt := $$ alter user $$ || in_username || $$ with encrypted password $1$$$ || in_password || $$$1$ $$; + execute stmt; + + update user_connection set database = in_database, host = in_host, port = in_port + where database <> in_database + OR host <> in_host + OR port <> in_port; + + return a_user.id; + + END IF; + + END; +$$ language 'plpgsql'; + +create view role_view as + select * from pg_auth_members m join pg_authid a ON (m.roleid = a.oid); + + +create or replace function admin_is_group(in_group_name text) returns bool as $$ + + DECLARE + + existant_role role_view; + stmt text; + + BEGIN + select * into role_view from role_view where rolname = in_group_name; + + if not found then + return 'f'::bool; + + else + return 't'::bool; + end if; + END; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_create_group(in_group_name TEXT, in_dbname TEXT) RETURNS int as $$ + + DECLARE + + stmt text; + + BEGIN + stmt := 'create role '||in_dbname||'_lsmb_$$' || in_group_name || '$$;'; + execute stmt; + return 1; + END; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION admin_delete_user(in_username TEXT) returns INT as $$ + + DECLARE + stmt text; + a_user user; + BEGIN + + select * into a_user from users where username = in_username; + + IF NOT FOUND THEN + + raise exception "User not found."; + ELSIF FOUND THEN + + stmt := $$ drop user $$ || a_user.username ||; + execute stmt; + + -- also gets user_connection + delete from users where id = a_user.id; + delete from entity where id = a_user.entity_id; + + END IF; + END; + +$$ language 'plpgsql'; + +comment on function admin_delete_user(text) is $$ + Drops the provided user, as well as deletes the entity and user configuration data. +$$; + +CREATE OR REPLACE FUNCTION admin_delete_group (in_group_name TEXT) returns bool as $$ + + DECLARE + stmt text; + a_role role_view; + BEGIN + + select * into a_role from role_view where rolname = in_group_name; + + if not found then + return 'f'::bool; + else + stmt := 'drop role $dbname_lsmb_$$' || in_group_name || '$$;'; + execute stmt; + return 't'::bool; + end if; + END; +$$ language 'plpgsql'; + +comment on function admin_delete_group(text) IS $$ + Deletes the input group from the database. Not designed to be used to + remove a login-capable user. +$$;
\ No newline at end of file |