Project/Open Core : Companies
Project/Open Core

Companies

Companies represent legal units, as oposed to offices that represent physical locations. So every company needs to have atleast one "main office", but may have associated several offices.

Companies permissions are managed using membership of the "admin_group" similar to projects.

create table im_companies (
        company_id             integer
                                constraint im_companies_pk
                                primary key
                                constraint im_companies_cust_id_fk
                                references acs_objects,
                                -- avoid using the OpenACS permission system
                                -- because we have to ask frequently:
                                -- "Who has read permissions on this object".
        admin_group_id          integer not null
                                constraint im_companies_admin_group_fk
                                references groups,
        company_name           varchar(1000) not null
                                constraint im_companies_name_un unique,
                                -- where are the files in the filesystem?
        company_path           varchar(100) not null
                                constraint im_companies_path_un unique,
        main_office_id          integer not null
                                constraint im_companies_office_fk
                                references im_offices,
        company_status_id      integer not null
                                constraint im_companies_cust_stat_fk
                                references categories,
        company_type_id        integer not null
                                constraint im_companies_cust_type_fk
                                references categories,
        crm_status_id           integer
                                constraint im_companies_crm_status_fk
                                references categories,
        primary_contact_id      integer
                                constraint im_companies_prim_cont_fk
                                references users,
        accounting_contact_id   integer
                                constraint im_companies_acc_cont_fk
                                references users,
        note                    varchar(4000),
        referral_source         varchar(1000),
        annual_revenue_id       integer
                                constraint im_companies_ann_rev_fk
                                references categories,
                                -- keep track of when status is changed
        status_modification_date date,
                                -- and what the old status was
        old_company_status_id  integer
                                constraint im_companies_old_cust_stat_fk
                                references categories,
                                -- is this a company we can bill?
        billable_p              char(1) default('f')
                                constraint im_companies_billable_p_ck
                                check(billable_p in ('t','f')),
                                -- Who in Client Services is the manager?
        manager_id              integer
                                constraint im_companies_manager_fk
                                references users,
                                -- How much do they pay us?
        contract_value          integer,
                                -- When does the company start?
        start_date              date,
        vat_number              varchar(100)
);