Snippets

Run SQL scripts


psql [database name]then
 \i /usr/share/ledgersmb/sql/upgrade/1.3-1.2.sqlor
psql [dbname] < /usr/share/ledgersmb/sql/upgrade/1.3-1.2.sql

Select the database version

SELECT * FROM defaults WHERE setting_key = 'version';

Select the default settings

The Defaults settings ( System/Defaults)
select * from defaults order by setting_key;

Select all parts in orders

All parts in order, from '2012-04-21' and '2012-04-22':

select p.partnumber, count(*), sum(qty) from orderitems o join parts p on p.id = o.parts_id where trans_id in (select id from oe where not closed and transdate between '2012-04-21' and '2012-04-22') group by p.partnumber;

Grant permission to manage users (on a terminal)

GRANT lsmb_"your company"__users_manage TO "your username";

List available user roles (access types)

SELECT * FROM lsmb_roles;

Show all users in a company

select * from USERS;

Change expire time on users

Change expire time on users, so they can log in and change password when changing through the web interface is no longer an option:

ALTER USER 'myusername' WITH VALID UNTIL '2011-11-09';