[Phpgroupware-cvs] CVS: bookkeeping/doc/examples/sql create_tables.mysql,NONE,1.1 create_tables.pgsql,NONE,1.1 drop_tables.mysql,NONE,1.1 lang_english.sql,NONE,1.1

[Luca - De Whiskey's - De Vitis <address@hidden>]

Update of /cvsroot/phpgroupware/bookkeeping/doc/examples/sql
In directory subversions:/tmp/cvs-serv15597/bookkeeping/doc/examples/sql

Added Files:
        create_tables.mysql create_tables.pgsql drop_tables.mysql 
        lang_english.sql 
Log Message:


Minor changes to directory structure:
- Moved old sql files to a more suitable place.
- Moved chora/docs to chora/doc


--- NEW FILE ---
# For acl support, phpgw_acl is being used. The acl_appname will be the
# bookkeeping_id preceeded by 'bookkeeping_'. The acl_location contains a
# number, representing a module. The user from acl_account has permissions
# given in acl_rights on the given module_id and bookkeeping_id.

# phpgw_bk_bookkeeping_accounts - phpgw_bk_ba
# phpgw_bk_account_type - phpgw_bk_at
# phpgw_bk_accounts - phpgw_bk_a
# phpgw_bk_categories - phpgw_bk_c
# phpgw_bk_modules - phpgw_bk_m
# phpgw_bk_bookkeeping_modules - phpgw_bk_bm
# phpgw_bk_locations - phpgw_bk_l
# phpgw_bk_groups - phpgw_bk_g
# phpgw_bk_group_rights -  phpgw_bk_gr
# phpgw_bk_group_phpgw_accounts - phpgw_bk_gpa
# phpgw_bk_transactions - phpgw_bk_t

# bookkeeping_id 0 is reserved for general bookkeeping settings.
# phpgw_bk_bookkeeping_accounts
CREATE TABLE phpgw_bk_ba (
             bookkeeping_account_id     int(11) NOT NULL auto_increment,
             bookkeeping_account        varchar(255) NOT NULL,
             PRIMARY KEY (bookkeeping_account_id)
);

# Cash, bank.
# phpgw_bk_account_types
CREATE TABLE phpgw_bk_at (
             account_type_id    int(11) NOT NULL auto_increment,
             account_type       varchar(255) NOT NULL,
             PRIMARY KEY (account_type_id)
);

# An account is 'place' where money is kept.
# phpgw_bk_accounts
CREATE TABLE phpgw_bk_a (
             account_id                 int(11) NOT NULL auto_increment,
             account_type_id            int(11) NOT NULL,
             bookkeeping_account_id     int(11) NOT NULL,
             account                    varchar(255) NOT NULL,
             PRIMARY KEY (account_id)
);

# Cateories can be entered as number or selected by name. A user can
# use the categories he has access to.
# phpgw_bk_categories
CREATE TABLE phpgw_bk_c (
             category_id                int(11) NOT NULL auto_increment,
             parent_category_id         int(11),
             bookkeeping_account_id     int(11) NOT NULL,
             category_number            int(11),
             category                   varchar(255) NOT NULL,
             description                text,
             PRIMARY KEY (category_id)
);

# phpgw_bk_modules
CREATE TABLE phpgw_bk_m (
             module_id          int(11) NOT NULL auto_increment,
             module             varchar(255) NOT NULL,
             module_order       int(11),
             PRIMARY KEY (module_id)
);

# phpgw_bk_bookkeeping_modules
CREATE TABLE phpgw_bk_bm (
             bookkeeping_account_id     int(11) NOT NULL,
             module_id                  int(11) NOT NULL
);

# phpgw_bk_locations
CREATE TABLE phpgw_bk_l (
             location_id        int(11) NOT NULL auto_increment,
             location           varchar(255),
             module_id          int(11) NOT NULL,
             PRIMARY KEY (location_id)
);

# phpgw_bk_groups
CREATE TABLE phpgw_bk_g (
             group_id                   int(11) NOT NULL auto_increment,
             group_                     varchar(255) NOT NULL,
             bookkeeping_account_id     int(11) NOT NULL,
             PRIMARY KEY (group_id)
);

# phpgw_bk_group_rights
CREATE TABLE phpgw_bk_gr (
             group_id           int(11) NOT NULL,
             location_id        int(11) NOT NULL,
             acl_right          int(11) NOT NULL
);

# phpgw_bk_group_phpgw_accounts
CREATE TABLE phpgw_bk_gpa (
             group_id           int(11) NOT NULL,
             phpgw_account_id   int(11) NOT NULL
);

# The registration of financial transactions
# phpgw_bk_transactions
CREATE TABLE phpgw_bk_t (
             transaction_id             int(11) NOT NULL auto_increment,
             bookkeeping_account_id     int(11) NOT NULL,
             date_transaction           int(11) NOT NULL,
             category_id                int(11) NOT NULL,
             description                text,
             amount                     decimal(20,2) NOT NULL,
             phpgw_account_id           int(11) NOT NULL,
             account_id                 int(11) NOT NULL,
             PRIMARY KEY (transaction_id)
);

--- NEW FILE ---
CREATE TABLE phpgw_bk_general (
             name            varchar(255) NOT NULL,
             value           text
);

CREATE TABLE phpgw_bk_general_logging (
             date            int NOT NULL,
             name            varchar(255) NOT NULL,
             value           text
);

CREATE TABLE phpgw_bk_entries (
             entry_id        serial,
             account_id      int NOT NULL,
             income_expense  char(1) NOT NULL,
             date_entry      int NOT NULL,
             category_id     int NOT NULL,
             description     text,
             amount          numeric(20,2) NOT NULL
);

CREATE TABLE phpgw_bk_entries_logging (
             date            int NOT NULL,
             entry_id        int NOT NULL,
             account_id      int NOT NULL,
             income_expense  char(1) NOT NULL,
             date_entry      int NOT NULL,
             category_id     int NOT NULL,
             description     text,
             amount          numeric(20,2) NOT NULL
);           

CREATE TABLE phpgw_bk_categories (
             category_id     serial,
             description     varchar(255) NOT NULL,
             income_expense  char(1) NOT NULL
);

CREATE TABLE phpgw_bk_categories_logging (
             date            int NOT NULL,
             category_id     int NOT NULL,
             description     varchar(255) NOT NULL,
             income_expense  char(1) NOT NULL
);

INSERT INTO phpgw_bk_general VALUES ('admins',NULL);

--- NEW FILE ---
DROP TABLE phpgw_bk_configuration;
DROP TABLE phpgw_bk_bookkeeping;
DROP TABLE phpgw_bk_account_types;
DROP TABLE phpgw_bk_accounts;
DROP TABLE phpgw_bk_categories;
DROP TABLE phpgw_bk_currencies;
DROP TABLE phpgw_bk_transactions;
DROP TABLE phpgw_bk_declarations;

--- NEW FILE ---
DELETE from lang WHERE app_name='bookkeeping';

INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('bookkeeping','common','en','Bookkeeping');
INSERT INTO lang (message_id, app_name, lang, content) VALUES('bookkeeping 
admins','common','en','Bookkeeping admins');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('entries','bookkeeping','en','Entries');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('categories','bookkeeping','en','Categories');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('statistics','bookkeeping','en','Statistics');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('reports','bookkeeping','en','Reports');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('logging','bookkeeping','en','Logging');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('date','bookkeeping','en','Date');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('category','bookkeeping','en','Category');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('description','bookkeeping','en','Description');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('amount','bookkeeping','en','Amount');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('view','bookkeeping','en','View');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('edit','bookkeeping','en','Edit');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('delete','bookkeeping','en','Delete');
INSERT INTO lang (message_id, app_name, lang, content) VALUES('switch 
to','bookkeeping','en','Switch to');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('income','bookkeeping','en','Income');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('expenses','bookkeeping','en','Expenses');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('add','bookkeeping','en','Add');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('expense','bookkeeping','en','Expense');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('loginid','bookkeeping','en','LoginID');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('firstname','bookkeeping','en','Firstname');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('lastname','bookkeeping','en','Lastname');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('admin','bookkeeping','en','Admin');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('proceed','bookkeeping','en','Proceed.');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('add_income','bookkeeping','en','Add income');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('add_expense','bookkeeping','en','Add expense');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('edit_income','bookkeeping','en','Edit income');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('edit_expense','bookkeeping','en','Edit expense');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('view_income','bookkeeping','en','View income');
INSERT INTO lang (message_id, app_name, lang, content) 
VALUES('view_expense','bookkeeping','en','View expense');