Estimating Project Costs

Nov 012009

This entry moved to http://www.project-open.org/documentation/rfe_advanced_financial_project_planning

How to prevent SQL Injections

Oct 172009

http://stackoverflow.com/questions/1973/what-is-the-best-way-to-avoid-sql-injection-attacks

Golden Rules:

3: Custom-Written Value Sanitation. Avoid writing your own sanitation routines as much as possible except when it’s absolutely the only option remaining (which is very unlikely in any modern language). Input sanitation is a hard problem, and the costs of getting it wrong are huge. It’s best to leave that job to someone else. When you are forced to rely on this method use white-listing rather than black-listing to sanitize input.

2: Framework / Library Based Value Sanitation. Leave the sanitation routines to the domain experts. Sanitation routines in 1st party frameworks and libraries are typically created by the same folks that wrote the DB or the SQL API. They have a much higher chance of knowing, and properly handling, all of the edge cases than you do. An example of this technique would be using php’s mysql_escape_string() function to sanitize values that will be inserted into strings that serve as dynamic SQL statements.

1: Parameter Binding. Instead of constructing a SQL statement as a raw string which includes user data as in-place literal values, create a SQL statement with tokens where the user data would be. Then bind the user supplied data to the appropriate parameters. The key here is that this binds the provided data to a specific type and a specific use and eliminates any opportunity to change the logic of the SQL statement. This can be used in conjunction with library based input sanitation as well.

http://www.tcl.tk/community/tcl2004/Tcl2003papers/cleverly.htm

Luckily for the Tcl programmer who craves laziness, OpenACS provides a higher-level database API on top of ns_db that hides all the mundane house-keeping chores. Additionaly, bind-variable emulation is available which frees the programmer from worrying about always escaping apostrophes in SQL statements. Bind variables are prefaced with a colon.

 set name "Jim O'Connor"
            set salary 40000

            db_dml new_hire {
                insert into employees (name, salary, dept)
                values (:name, :salary, 'Accounting')
            }

http://openacs.com/forums/message-view?message_id=250625

Posted by Dave Bauer on 01/19/05 04:57 PM

In Postgresql the db_* commands extract the “bind” variables and SQL quote them automatically. In the PG driver itself the [] charactaers are escaped as well for safety.

This means that the effective security of PostgreSQL is the same as Oracle.

This is done at the database api layer.

In addition ad_page_contract has filters and validation features to check input. ad_form and the form builder also have validation features for input.

XOWIKI - db structure

Oct 122009

xowiki pages are managed with the OpenACS Content repository.

XOWIKI pages:

For each page there’s an entry in cr_items

projop=# \d cr_items
                                   Table "public.cr_items"
      Column       |          Type          |                   Modifiers
-------------------+------------------------+------------------------------------------------
 item_id           | integer                | not null
 parent_id         | integer                | not null
 name              | character varying(400) | not null
 locale            | character varying(4)   |
 live_revision     | integer                |
 latest_revision   | integer                |
 publish_status    | character varying(40)  |
 content_type      | character varying(100) |
 storage_type      | character varying(10)  | not null default 'text'::character varying
 storage_area_key  | character varying(100) | not null default 'CR_FILES'::character varying
 tree_sortkey      | bit varying            | not null
 max_child_sortkey | bit varying            |

Page content

Page content is stored in table cr_revisions

projop=# \d cr_revisions
                             Table "public.cr_revisions"
     Column     |           Type           |                Modifiers
----------------+--------------------------+-----------------------------------------
 revision_id    | integer                  | not null
 item_id        | integer                  | not null
 title          | character varying(1000)  |
 description    | text                     |
 publish_date   | timestamp with time zone |
 mime_type      | character varying(200)   | default 'text/plain'::character varying
 nls_language   | character varying(50)    |
 lob            | integer                  |
 content        | text                     |
 content_length | integer                  |

Finding content that can’t be found using ‘search’ such as links etc.

select
		r.item_id,
		r.title,
		i.name
from
		cr_revisions r,
		cr_items i
where
		r.content like '%en:install_main%' and
		r.revision_id = i.live_revision;

Object Model

Pls. see: http://openacs.org/xotcl/show-object?show_source=0&object=%3A%3Axowiki%3A%3APage&show_methods=1

]project-open[ - Getting Things Done Package

Sep 262009

The package provides the following features:

  • Provide a single access point for all tasks assigned to an user
  • Gives users the possibility to create a sortable list of tasks
  • Tasks can added from a list of existing project tasks, forum tasks and workflow tasks
  • Let users add arbitrary tasks not managed with ]po[
  • Provide users with quick access to often used features such as time sheet tracking and relevant task data.
  • Speeds up adminstrative tasks and provides convenience to users working in multitasking environments, such as Help-Desk, Call-Center, Support, etc.

Principal architectural objectives:

  • Implementing a RESTful Web Service
  • Evaluate Open Source JS libraries for use in ]po[
  • Security aspects and error handling when using xmlhttp requests (XHR)

Protoytype

To-Do's

  • Fix Bug: time - logging
  • Show err mess when no tasks have been found for project
  • Allow to delete tasks from To-Do list by drag&drop them to wastebasket
  • Add WF tab (shows all tasks resulting from WF instances)
  • Add Forum tab (shows all FORUM tasks)

Developer Documentation

The package is available from the ]po[ CVS server ("experimental status"):

Package Name:
intranet-gtd-dashboard
CVSROOT:
":pserver:anonymous@cvs.openacs.org:/home/cvsroot"

It had been created in 2008/2009. The ]po[ RESTful interface was not yet available. 

                Table "public.im_gtd_tasks"
       Column       |           Type           | Modifiers
--------------------+--------------------------+-----------
 gtd_task_id        | integer                  | not null
 ref_id             | integer                  | not null
 owner_id           | integer                  |
 created_date       | timestamp with time zone |
 description        | text                     |
 last_modified      | date                     |
 gtd_type_id        | integer                  | not null
 gtd_task_status_id | integer                  | not null
Indexes:
    "im_gtd_tasks_pkey" PRIMARY KEY, btree (gtd_task_id)
Foreign-key constraints:
    "im_gtd_task_type_fk" FOREIGN KEY (gtd_type_id) REFERENCES im_categories(category_id)
    "im_gtd_task_type_status_fk" FOREIGN KEY (gtd_task_status_id) REFERENCES im_categories(category_id)
    "im_gtd_tasks_owner_id_fkey" FOREIGN KEY (owner_id) REFERENCES users(user_id)

]po[ Time Sheet Management

Jun 152009

Content

  • General Information
  • Tables and views used
  • Time sheet Portlets
  • Sample Queries

General information

When time sheet form (/intranet-timesheet2/hours/new) is send to the server,

  • … ]po[ deletes affected time sheet entries for affected day and user and creates new ones
  • ... ]po[ updates im_projects::reported_hours_cache

Please note that

  • ... Paramaters have in impact on values shown in time sheet plugins. In case there's mismath regarding hours reported, check parameter SyncHoursInterval and SyncHoursP
  • If hours don't sum up correctly on the time sheet component in a sub project, there might be hours logged on the main project that are not considered.
fullquery name: update_timesheet_task

        update im_projects
        set reported_hours_cache = (
                select  sum(h.hours)
                from    im_hours h
                where   h.project_id = :project_id
        )
        where project_id = :project_id

fullquery name: delete_timesheet_costs

      DECLARE
         row RECORD;
      BEGIN
         for row in
                select  cost_id
                from    im_costs
                where   cost_type_id = [im_cost_type_timesheet]
                        and project_id = :project_id
                        and cause_object_id = :user_id_from_search
                        and effective_date = to_date(:julian_date, ‘J’)
         loop
                PERFORM im_cost__delete(row.cost_id);
         end loop;
         return 0;
      END;

Tables used

  • im_timesheet_tasks
  • im_hours
  • im_costs

im_timesheet_tasks

  • Contains all timesheet tasks 
projop=# \d im_timesheet_tasks
       Table "public.im_timesheet_tasks"
     Column     |       Type       | Modifiers
----------------+------------------+-----------
 task_id        | integer          | not null
 material_id    | integer          | not null
 uom_id         | integer          | not null
 planned_units  | double precision |
 billable_units | double precision |
 cost_center_id | integer          |
 invoice_id     | integer          |
 priority       | integer          |
 sort_order     | integer          |
 bt_bug_id      | integer          |
Indexes:
    "im_timesheet_tasks_pk" PRIMARY KEY, btree (task_id)
Foreign-key constraints:
    "im_times_tasks_bt_bug_fk" FOREIGN KEY (bt_bug_id) REFERENCES bt_bugs(bug_id)
    "im_timesheet_task_fk" FOREIGN KEY (task_id) REFERENCES im_projects(project_id)
    "im_timesheet_tasks_cost_center_fk" FOREIGN KEY (cost_center_id) REFERENCES im_cost_centers(cost_center_id)
    "im_timesheet_tasks_invoice_fk" FOREIGN KEY (invoice_id) REFERENCES im_invoices(invoice_id)
    "im_timesheet_tasks_material_fk" FOREIGN KEY (material_id) REFERENCES im_materials(material_id)
    "im_timesheet_tasks_uom_fk" FOREIGN KEY (uom_id) REFERENCES im_categories(category_id)

im_costs

A cost item will be created for each time sheet entry 

                    Table "public.im_costs"
         Column         |           Type           | Modifiers
------------------------+--------------------------+-----------
 cost_id                | integer                  | not null
 cost_name              | character varying(400)   | not null
 cost_nr                | character varying(400)   | not null
 project_id             | integer                  |
 customer_id            | integer                  | not null
 cost_center_id         | integer                  |
 provider_id            | integer                  | not null
 investment_id          | integer                  |
 cost_status_id         | integer                  | not null
 cost_type_id           | integer                  | not null
 cause_object_id        | integer                  |
 template_id            | integer                  |
 effective_date         | timestamp with time zone |
 start_block            | timestamp with time zone |
 payment_days           | integer                  |
 amount                 | numeric(12,3)            |
 currency               | character(3)             |
 paid_amount            | numeric(12,3)            |
 paid_currency          | character(3)             |
 vat                    | numeric(12,5)            |
 tax                    | numeric(12,5)            |
 variable_cost_p        | character(1)             |
 needs_redistribution_p | character(1)             |
 parent_id              | integer                  |
 redistributed_p        | character(1)             |
 planning_p             | character(1)             |
 planning_type_id       | integer                  |
 description            | character varying(4000)  |
 note                   | character varying(4000)  |
 last_modified          | timestamp with time zone |
 last_modifying_user    | integer                  |
 last_modifying_ip      | character varying(20)    |
Indexes:
    "im_costs_pk" PRIMARY KEY, btree (cost_id)
    "im_costs_cause_object_idx" btree (cause_object_id)
    "im_costs_start_block_idx" btree (start_block)
Check constraints:
    "im_costs_needs_redist_ck" CHECK (needs_redistribution_p = 't'::bpchar OR needs_redistribution_p = 'f'::bpchar)
    "im_costs_planning_ck" CHECK (planning_p = 't'::bpchar OR planning_p = 'f'::bpchar)
    "im_costs_redist_ck" CHECK (redistributed_p = 't'::bpchar OR redistributed_p = 'f'::bpchar)
    "im_costs_var_ck" CHECK (variable_cost_p = 't'::bpchar OR variable_cost_p = 'f'::bpchar)
Foreign-key constraints:
    "im_cost_template_fk" FOREIGN KEY (template_id) REFERENCES im_categories(category_id)
    "im_costs_cause_fk" FOREIGN KEY (cause_object_id) REFERENCES acs_objects(object_id)
    "im_costs_cost_center_fk" FOREIGN KEY (cost_center_id) REFERENCES im_cost_centers(cost_center_id)
    "im_costs_cost_fk" FOREIGN KEY (cost_id) REFERENCES acs_objects(object_id)
    "im_costs_currency_fk" FOREIGN KEY (currency) REFERENCES currency_codes(iso)
    "im_costs_customer_fk" FOREIGN KEY (customer_id) REFERENCES acs_objects(object_id)
    "im_costs_inv_fk" FOREIGN KEY (investment_id) REFERENCES acs_objects(object_id)
    "im_costs_last_mod_user" FOREIGN KEY (last_modifying_user) REFERENCES users(user_id)
    "im_costs_paid_currency_fk" FOREIGN KEY (paid_currency) REFERENCES currency_codes(iso)
    "im_costs_parent_fk" FOREIGN KEY (parent_id) REFERENCES im_costs(cost_id)
    "im_costs_planning_type_fk" FOREIGN KEY (planning_type_id) REFERENCES im_categories(category_id)
    "im_costs_provider_fk" FOREIGN KEY (provider_id) REFERENCES acs_objects(object_id)
    "im_costs_status_fk" FOREIGN KEY (cost_status_id) REFERENCES im_categories(category_id)
    "im_costs_type_fk" FOREIGN KEY (cost_type_id) REFERENCES im_categories(category_id)
Triggers:
    im_costs_project_cache_del_tr AFTER DELETE ON im_costs FOR EACH ROW EXECUTE PROCEDURE im_cost_project_cache_del_tr()
    im_costs_project_cache_ins_tr AFTER INSERT ON im_costs FOR EACH ROW EXECUTE PROCEDURE im_cost_project_cache_ins_tr()
    im_costs_project_cache_up_tr AFTER UPDATE ON im_costs FOR EACH ROW EXECUTE PROCEDURE im_cost_project_cache_up_tr()

Views used by ]po[ Time Sheet Management

  • im_timesheet_tasks_view

projop=# \d  im_timesheet_tasks_view
            View "public.im_timesheet_tasks_view"
        Column        |           Type           | Modifiers
----------------------+--------------------------+-----------
 task_id              | integer                  |
 material_id          | integer                  |
 uom_id               | integer                  |
 planned_units        | double precision         |
 billable_units       | double precision         |
 cost_center_id       | integer                  |
 invoice_id           | integer                  |
 priority             | integer                  |
 sort_order           | integer                  |
 project_id           | integer                  |
 task_name            | character varying(1000)  |
 task_nr              | character varying(100)   |
 percent_completed    | double precision         |
 task_type_id         | integer                  |
 task_status_id       | integer                  |
 start_date           | timestamp with time zone |
 end_date             | timestamp with time zone |
 reported_hours_cache | double precision         |
 reported_units_cache | double precision         |
View definition:
 SELECT t.task_id, t.material_id, t.uom_id, t.planned_units, t.billable_units, t.cost_center_id, t.invoice_id, t.priority, t.sort_order, p.parent_id AS project_id, p.project_name AS task_name, p.project_nr AS task_nr, p.percent_completed, p.project_type_id AS task_type_id, p.project_status_id AS task_status_id, p.start_date, p.end_date, p.reported_hours_cache, p.reported_hours_cache AS reported_units_cache
   FROM im_projects p, im_timesheet_tasks t
  WHERE t.task_id = p.project_id;

Time sheet Portlets

Timesheet Tasks

Location: /intranet/projects/view?project_id=...

Facts about the Time sheet Component:

  • In order to speed up page creation, hours are taken from im_projects::reported_hours_cache

Samples

Debugging permissions

Mar 242009

Sample

User (user_id:28358) with profile “Sales” (group_id:473) can’t see Employees:

Principal routine to check permissions

create function acs_permission__permission_p(int4,int4,varchar) returns bool as '
         declare
             permission_p__object_id           alias for $1;
             permission_p__party_id            alias for $2;
             permission_p__privilege           alias for $3;
             exists_p                          boolean;
         begin
           return exists (select 1
                          from
					acs_permissions p,
					party_approved_member_map m,
                            	acs_object_context_index c,
					acs_privilege_descendant_map h
                         where
					p.object_id = c.ancestor_id
                           	and h.descendant = permission_p__privilege
                           	and c.object_id = permission_p__object_id
                           	and m.member_id = permission_p__party_id
                           	and p.privilege = h.privilege
                           	and p.grantee_id = m.party_id);
        end;' language 'plpgsql';

Check pre-condition:

a) Check existence of privilige “View users”
b) Check User Matrix: Sale should be allowed to see Employees

Digging in’

a) Check permission:

select im_object_permission_p(463, 28358, ‘view_users’);
returns ‘f’

b) Check if member is (approved) member of “Sales”

select
	*
from
	group_approved_member_map
where
	member_id = 28358 and
	group_id = 473;


 group_id | member_id | rel_id | container_id |    rel_type
----------+-----------+--------+--------------+----------------
      473 |     28358 |  35900 |          473 | membership_rel
(1 row)

c) Check for corresponding entry in “party_approved_member_map”


select
		m.*
from
		party_approved_member_map m
where
		m.member_id = 28358;


 party_id | member_id |  tag
----------+-----------+-------
      473 |     28358 | 35900
       -2 |     28358 | 35900
      438 |     28358 | 35900
    28358 |     28358 |     0
       -2 |     28358 | 28359
      438 |     28358 | 28359
       -1 |     28358 | 28359
      463 |     28358 | 28361
       -2 |     28358 | 28361
      438 |     28358 | 28361
       -1 |     28358 | 28361
       -1 |     28358 | 35900
(12 rows)

d) Check acs_privilege_descendant_map

select
		m.*
from 

		acs_permissions p,
		party_approved_member_map m
where
		m.member_id = 28358 and
		m.oid = 463;	

tbc.

Debugging Stored Procedures

create or replace function workflow_case__add_task_assignment(int4,int4,bool) returns int4 as '
       declare
         add_task_assignment__task_id                alias for $1;
         add_task_assignment__party_id               alias for $2;
         add_task_assignment__permanent_p	      alias for $3;
         v_count                                    integer;
         v_workflow_key                             wf_workflows.workflow_key%TYPE;
         v_context_key                              wf_contexts.context_key%TYPE;
         v_case_id                                  wf_cases.case_id%TYPE;
         v_role_key				     wf_roles.role_key%TYPE;
         v_transition_key                           wf_transitions.transition_key%TYPE;
         v_notification_callback     			wf_context_transition_info.notification_callback%TYPE;
         v_notification_custom_arg   wf_context_transition_info.notification_custom_arg%TYPE;
         callback_rec                record;
         v_assigned_user             record;
       begin
               -- get some needed information

	       RAISE NOTICE ''KHD: workflow_case__add_task_assignment: enter'';

               select ta.case_id, ta.workflow_key, ta.transition_key, tr.role_key, c.context_key
               into   v_case_id, v_workflow_key, v_transition_key, v_role_key, v_context_key
               from   wf_tasks ta, wf_transitions tr, wf_cases c
               where  ta.task_id = add_task_assignment__task_id
                 and  tr.workflow_key = ta.workflow_key
                 and  tr.transition_key = ta.transition_key
                 and  c.case_id = ta.case_id;

               -- make the same assignment as a manual assignment

               IF add_task_assignment__permanent_p = ''t'' and v_role_key is not null THEN
       	    /* We do this up-front, because
       	     * even though the user already had a task assignment,
       	     * he might not have a case assignment.
       	     */
                   perform workflow_case__add_manual_assignment (
                       v_case_id,
                       v_role_key,
                       add_task_assignment__party_id
                   );
               end if;

               -- check that we do not hit the unique constraint

               select count(*) into v_count
               from   wf_task_assignments
               where  task_id = add_task_assignment__task_id
               and    party_id = add_task_assignment__party_id;

               if v_count > 0 then
                   return null;
               end if;

               -- get callback information

               select notification_callback,
       		   notification_custom_arg into callback_rec
       	    from   wf_context_transition_info
       	    where  context_key = v_context_key
       	    and    workflow_key = v_workflow_key
       	    and    transition_key = v_transition_key;

		RAISE NOTICE ''KHD: workflow_case__add_task_assignment -> notification_callback is %: '', callback_rec;

               if FOUND then
                   v_notification_callback := callback_rec.notification_callback;
                   v_notification_custom_arg := callback_rec.notification_custom_arg;
               else
                   v_notification_callback := null;
                   v_notification_custom_arg := null;
               end if;

               -- notify any new assignees

	       RAISE NOTICE ''KHD: workflow_case__add_task_assignment-> add_task_assignment__task_id is %: '', add_task_assignment__task_id;
	       RAISE NOTICE ''KHD: workflow_case__add_task_assignment-> add_task_assignment__party_id is %: '', add_task_assignment__party_id;

               for v_assigned_user in
                   select distinct u.user_id
                   from   users u
                   where  u.user_id not in (
       	            select distinct u2.user_id
       	            from   wf_task_assignments tasgn2,
       	                   party_approved_member_map m2,
       	                   users u2
       	            where  tasgn2.task_id = add_task_assignment__task_id
       	            and    m2.party_id = tasgn2.party_id
       	            and    u2.user_id = m2.member_id)
                   and exists (
                       select 1
                       from   party_approved_member_map m
                       where  m.member_id = u.user_id
                       and    m.party_id = add_task_assignment__party_id
                   )
               LOOP
			RAISE NOTICE ''KHD: workflow_case__add_task_assignment-> add_task_assignment__task_id: %: '', add_task_assignment__task_id;
			RAISE NOTICE ''KHD: workflow_case__add_task_assignment-> v_assigned_user.user_id: %: '', v_assigned_user.user_id;
			RAISE NOTICE ''KHD: workflow_case__add_task_assignment-> v_notification_callback: %: '', v_notification_callback;
			RAISE NOTICE ''KHD: workflow_case__add_task_assignment-> v_notification_custom_arg: %: '', v_notification_custom_arg;

                   PERFORM workflow_case__notify_assignee (
                       add_task_assignment__task_id,
                       v_assigned_user.user_id,
                       v_notification_callback,
                       v_notification_custom_arg
                   );
		RAISE NOTICE ''KHD: workflow_case__add_task_assignment-> CALLED workflow_case__notify_assignee: %: '', v_assigned_user.user_id;

               end loop;

               -- do the insert

               insert into wf_task_assignments (
                   task_id,
                   party_id
               ) values (
                   add_task_assignment__task_id,
                   add_task_assignment__party_id
               );

               return 0;
       end;' language 'plpgsql';

Commonly used sql constructs

Feb 012009

Get all ‘childs’

select
	p_child.project_id
from
	im_projects p_parent,
	im_projects p_child
where
	p_child.tree_sortkey between p_parent.tree_sortkey and tree_right(p_parent.tree_sortkey)
	and p_parent.project_id = 16030;

Get top parent project


        select  main_p.project_id
        from    im_projects p,
                im_projects main_p
        where   p.project_id = :project_id and
                tree_ancestor_key(p.tree_sortkey, 1) = main_p.tree_sortkey

Formatting Date Fields

to_char(u.last_visit, 'YYYY-MM-DD HH:SS') as last_visit_formatted,

All dates are in general of type timestamp which allows the the following operations:
Check if date lies before or after a reference date:p.start_date < '2006-04-04'

Get value name for categories

        select
                im_category_from_id(t.target_language_id) as target_language,
                im_category_from_id(t.source_language_id) as source_language
        from
                im_trans_tasks t,
        where
                and t.project_id = 123;

coalesce

		select
			sum(coalesce(s.billable_units,0)) as task_sum,
			trim(both ' ' from to_char(t.task_sum, :'999999999999D')) as task_sum_
		from
			im_trans_tasks;

CASE

select
        p.project_name as sub_project_name,
        p.project_nr as sub_project_nr,
        p.project_type_id as sub_project_type_id,
        p.project_status_id as sub_project_status_id,
        tree_ancestor_key(p.tree_sortkey, 1) as main_project_sortkey,

        trunc((c.paid_amount *
          im_exchange_rate(c.effective_date::date, c.currency, 'EUR')) :: numeric
          , 2) as paid_amount_converted,
        CASE c.cost_type_id = 3718
                WHEN true THEN
                        TO_CHAR(trunc((c.amount * im_exchange_rate(c.effective_date::date, c.currency, 'EUR') * '90' / 100 ) :: numeric, 2),‘LFM99G999G990D00')
                ELSE
                        trunc((c.amount * im_exchange_rate(c.effective_date::date, c.currency, 'EUR')) :: numeric, 2)
        END as amount_converted,
        c.*
from
        im_costs c
        LEFT OUTER JOIN im_projects p ON (c.project_id = p.project_id)
where
        NOT (c.customer_id = 28148 AND c.cost_type_id = 3718)
        and c.cost_type_id in (3704, 3706, 3718, 3720, 3700)
        and c.effective_date::date >= to_date('2009-09-28', 'YYYY-MM-DD')
        and c.effective_date::date < to_date('2099-12-31', 'YYYY-MM-DD')
        and c.effective_date::date < to_date('2099-12-31', 'YYYY-MM-DD')
        and p.project_status_id in (76, 78, 10000132)

LOOPs

CREATE OR REPLACE FUNCTION im_workflow_NAME__cleanup(INTEGER, text, text)
  RETURNS INTEGER AS '
declare
        p_case_id               alias for $1;
        p_transition_key        alias for $2;
        p_custom_arg            alias for $3;
 
        v_task_id               integer;        v_case_id               integer;
        v_object_id             integer;        v_creation_user         integer;
        v_creation_ip           varchar;        v_journal_id            integer;
        v_transition_key        varchar;        v_workflow_key          varchar;
        r                       record;
 
begin
        v_user_id := p_custom_arg;
 
        -- Select out some frequently used variables of the environment
        select  c.object_id, c.workflow_key, task_id, c.case_id
        into    v_object_id, v_workflow_key, v_task_id, v_case_id
        from    wf_tasks t, wf_cases c
        where   c.case_id = p_case_id
                and t.case_id = c.case_id
                and t.workflow_key = c.workflow_key
                and t.transition_key = p_transition_key;
 
 
        FOR r IN select request_id from notification_requests where object_id = v_object_id and user_id = v_user_id
        LOOP
                select notification_request__delete(r.request_id);
                perform acs_object__delete(r.request_id);
        END LOOP;
 
end;' LANGUAGE 'plpgsql' VOLATILE;

Frame for disposable functions

CREATE OR REPLACE FUNCTION inline_0 ()
RETURNS INTEGER AS '
 
declare
        v_count                 integer;
begin
 
        select count(*) into v_count from im_categories
        where category_id = 5006 or category_id = 5007;
 
        IF      0 != v_count
        THEN
                RAISE NOTICE ''upgrade-4.0.1.0.0-4.0.1.0.1.sql failed - could not add categories'';
                return 0;
        END IF;
 
        PERFORM im_category_new(5006, ''Overtime'', ''Intranet Absence Type'');
        PERFORM im_category_new(5007, ''Reduction in Working Hours'', ''Intranet Absence Type'');
 
        return 1;
 
end;' LANGUAGE 'plpgsql';
 
SELECT inline_0 ();
DROP FUNCTION inline_0 ();

Using the $body$ tag

create or replace function im_name_from_user_id(int4, int4) returns varchar as $body$
        DECLARE
                v_user_id       alias for $1;
                v_name_order    alias for $2;
                v_full_name     varchar(8000);
        BEGIN
                IF 2 == v_name_order THEN
                        select  last_names || ' ' || first_name
                        into    v_full_name
                        from    persons
                        where person_id = v_user_id;
                ELSEIF 3 == v_name_order THEN
                        select  last_names || ', ' || first_name
                        into    v_full_name
                        from    persons
                        where person_id = v_user_id;
                ELSE
                        select  first_names || ' ' || last_name
                        into    v_full_name
                        from    persons
                        where person_id = v_user_id;
                END IF;
                return v_full_name;
        END;$body$ language 'plpgsql';

Check for empty string

        IF      v_type_category_type IS NULL or length(v_type_category_type) = 0
        THEN
                update acs_object_types set type_category_type = ''Intranet SLA Parameter Type'' where object_type = ''im_sla_parameter'';
        END IF;

Auto ID's

create sequence im_customer_project_type_seq;
create table im_customer_project_type (
        id                      integer,
        company_id              integer
                                references im_companies,
        project_type_id         integer not null,
        unique(company_id, project_type_id)
);
ALTER TABLE im_customer_project_type ALTER COLUMN id SET DEFAULT NEXTVAL('im_customer_project_type_seq');

OpenACS / ]project-open[ Quick Reference

Feb 012009

See also:

Control flow / Conditions

Check for existing variable

  • [exists_and_not_null project_id]
  • [info exists project_id]

Localization

Find message in DB:

select * from lang_messages where message like '%Type%'

OpenACS string localization:

[lang::message::lookup "" intranet-core.Execution_Project "Create an 'Execution Project'"]

OpenACS string localization (short version):

[_ intranet-core.Name]
#intranet-core.Filter_Projects#

Localize date to user-locale:

[lc_time_fmt '2009-12-15' %x]

Add message: 

SELECT im_lang_add_message('en_US','intranet-audit','Earned_Value','Earned Value');

Localize numbers / beautifying them for output:

/intranet-invoices/www/view.tcl

set rounding_precision 2
set locale [lang::user::locale]
set amount_pretty [lc_numeric [im_numeric_add_trailing_zeros [expr $amount+0] $rounding_precision] "" $locale]
set val_hours_output [format "%0.2f" $val_hours]

Localize labels etc.

[lang::message::lookup "" intranet-core.Execution_Project "Create an Execution Project"]

Localize categories
Pls. see below

Categories

Create new Category

SELECT im_category_new ('71', 'Potential', 'Intranet Project Status');

Create new Category Hierarchy

SELECT im_category_hierarchy_new (58, 56);

Return id of categories with name CATEGORY_NAME

[im_sub_categories CATEGORY_NAME]

Returns name of category 

[im_category_from_id CATEGORY_ID]

Transitive closure – Returns categories including sub-categories

im_sub_categories [ -include_disabled_p include_disabled_p ] category_list

Note: category_list is a list of category id’s (integer)

Category drop-down in ad_form – Default
Currently return an empty entry for ‘All’

    ...
    -form {
        {absence_type_id:text(im_category_tree),optional {label "[_ intranet-timesheet2.Absence_Type]"} {value $absence_type_id} {custom {category_type "Intranet Absence Type" translate_p 1} } }
     .. 
    }

Category drop-down in ad_form – Custom, using VIEW

 
    set absences_types [im_memoize_list select_absences_types "select absence_type_id, absence_type from im_absence_types order by lower(ABSENCE_TYPE)"]
    set absences_types [linsert $absences_types 0 "All"]
    set absences_types [linsert $absences_types 0 -1]
    set absence_type_list [list]
    foreach { value text } $absences_types {
        regsub -all " " $text "_" category_key
        set text [lang::message::lookup "" intranet-core.$category_key $text]
        lappend absence_type_list [list $text $value]
    }
    ...
    -form {
        {absence_type_id:text(select),optional {label "[_ intranet-timesheet2.Absence_Type]"} {options $absence_type_list }}     .. 
    }

Plain SQL

SELECT
     im_categories.category_id,
     im_categories.category AS absence_type
FROM
     im_categories
WHERE
     im_categories.category_type::text = 'Intranet Absence Type'::text;

Permissions for pages

Is User logged in?:

set current_user_id [ad_maybe_redirect_for_registration]

Using existing permission procedures:

im_timesheet_task_permissions $current_user_id $project_id view read write admin

sets variables $read $write $admin to true/false

Checking privilege:

if {![im_permission $current_user_id "view_projects_all"]} {
    ad_return_complaint 1 "[_ intranet-core.lt_You_dont_have_suffici_2]"
}

Search for privileges:

SELECT * FROM acs_privileges WHERE pretty_name LIKE '%assign%';

Permissions & Privileges

Create a new privilege:

select acs_privilege__create_privilege('add_tickets','Add Tickets','');
select acs_privilege__add_child('admin', 'add_tickets_for_customers');

select im_priv_create('add_tickets', 'P/O Admins');
select im_priv_create('add_tickets', 'Senior Managers');
select im_priv_create('add_tickets', 'Project Managers');
select im_priv_create('add_tickets', 'Employees');
select im_priv_create('add_tickets', 'Customers');

Check if user has privilege

[im_permission $user_id view_costs]

Remove priviliges

delete from acs_permissions where privilege = 'add_view_internal_rates';
delete from acs_privilege_hierarchy where child_privilege = 'add_view_internal_rates';
delete from acs_privileges where privilege = 'add_view_internal_rates';

Packages

Parameters

[parameter::get -package_id [apm_package_id_from_key intranet-mail-import] -parameter "ScanMails" -default 60]

Does PAckage exist

set im_survey_installed_p [llength [info proc [db_string get_view_id "select package_id from apm_packages where package_key = 'intranet-simple-survey'" -default 0]]]

OR

set survey_exists_p [llength [info commands im_package_survsimp_id]]

Dynfields

Create DynField

 
SELECT im_dynfield_attribute_new (
        'im_timesheet_task', 'effort_driven_type_id', 'Fixed Task Type', 'gantt_fixed_task_type', 'integer', 'f', 0, 'f', 'im_timesheet_tasks'
);

Set DynField permissions

PERFORM acs_permission__grant_permission(v_dynfield_id, (select group_id from groups where group_name=''Employees''), ''read'');
create or replace function inline_0 ()
returns integer as '
declare
        v_dynfield_id           integer;
begin

        SELECT im_dynfield_attribute_new (
                'im_timesheet_task', 'effort_driven_type_id', 'Fixed Task Type', 'gantt_fixed_task_type', 'integer', 'f', 0, 'f', 'im_timesheet_tasks'
        );

        PERFORM acs_permission__grant_permission(v_dynfield_id, (select group_id from groups where group_name=''Employees''), ''read'');
        PERFORM acs_permission__grant_permission(v_dynfield_id, (select group_id from groups where group_name=''Employees''), ''write'');

        return 0;

end;' language 'plpgsql';
select inline_0 ();
drop function inline_0 ();

Views

Create View

insert into im_views (view_id, view_name, visible_for, view_type_id)
values (80, 'office_list', 'view_offices', 1400);

Create View Columns

insert into im_view_columns (column_id, view_id, group_id, column_name, column_render_tcl,
extra_select, extra_where, sort_order, visible_for) values (2221,22,NULL,'Quote',
'$quote_date','','',18,'');

User Management

Is user member of a project?

[im_biz_object_member_p $user_id $project_id]

Is user employee?

[im_user_is_employee_p $user_id]

Is user employee?

[im_profile::member_p -profile_id [im_employee_group_id] -user_id $user_id]

Is user customer?

[im_profile::member_p -profile_id [im_customer_group_id] -user_id $user_id]

Is user freelancer?

[im_profile::member_p -profile_id [im_freelance_group_id] -user_id $user_id]

Is user customer?

[im_profile::member_p -profile_id [im_customer_group_id] -user_id $user_id]

Is user Project Manager?

[im_biz_object_admin_p $user_id $project_id]

Is user admin?

[im_is_user_site_wide_or_intranet_admin $user_id]

Current user

po34klaus=# select current_user;
 current_user
--------------
 po34klaus
(1 row)

Database

db_foreach

    set column_sql "
        select
                *
        from
                im_view_columns
        where
                view_id=:view_id
                and group_id is null
        order by
                sort_order
    "
    db_foreach column_list_sql $column_sql {
        if {"" == $visible_for || [eval $visible_for]} {
            lappend column_headers "$column_name"
            lappend column_vars "$column_render_tcl"
        } 
        ....
    }

db_1row
Use: Set variables for a query returning exactly one row.

Sample:

         db_1row sender_get_info_1 "
 
                select
                        pe.first_names as accounting_first_name,
                        pe.last_name as accounting_last_name
                from
                        persons
                where
                        person_id = $accounting_contact_id
 
                       "

db_0or1row
Like db_1row but returns an error if more than one row is returned.

db_string
Use: Getting a single string value from the db

Sample:

set view_id [db_string get_view_id "select view_id from im_views where view_name=:view_name" -default 0]

db_dml – performs a dml statement

db_dml delete "
                delete from im_object_freelance_skill_map
                where
                        object_id = :object_id
                        and skill_type_id = :skill_type_id
                        and skill_id in ([join $skill_id ","])
"

Create a sequence

create sequence im_inquiries_files_seq start 1;

Get id from sequence

set inquiry_files_id [db_string nextval "select nextval('im_inquiries_files_seq');"]

Check if table exists

<ul>
  <li>db_table_exists TABLE_NAME </li>
  <li>[im_table_exists im_freelance_rfqs]</li>
</ul>

DB Transactions

    db_transaction {
        db_dml test "nonsense"
    } on_error {
        ad_return_error "Error in blah/foo/bar" "The error was: $errmsg"
    }

String operations

String concatenation

Alternative to concat & append – in case var’s are ambigious:

set var_amount "amount.${user_id}_$project_type_id"
</p>
 
<strong>Check for empty string</strong>
<ul>
	<li>empty_string_p [VAR]</li>
</ul>

Sample:

if { ![empty_string_p $where_clause] } {
    set where_clause " and $where_clause"
}

Compare strings in tcl

if { -1 != [string compare $list_order_by "min(p.price)"] } {
                ad_return_complaint 1 "here"
 
                set sorted_table_rows [qsort $table_rows [lambda {s} { lindex $s 2 }]]
        } else {
 
                set sorted_table_rows $table_rows
        }

Formating for amounts, etc.

set amount_pretty [im_numeric_add_trailing_zeros [expr $amount+0] 2]

Error handling

Handling expr error

         if {"" != $visible_tcl} {
            set visible 0
            set errmsg ""
            if [catch {
                set visible [expr $visible_tcl]
            } errmsg] {
                ad_return_complaint 1 "$visible_tcl\n$errmsg"
            }
            if {!$visible} { continue }
        }

Handling db error

    if {[catch { db_dml target_languages " select * from im_projects" } errmsg ]} {
        append errors "..."
    }

Page contract

Optional parameters

ad_proc im_costs_base_component { user_id {company_id ""} {project_id ""} } {
    Returns a HTML table containing a list of costs for a particular
    company or project.
} {
...
    }

Passing URL parameters

  1. set primary_contact_link "<a href=primary-contact?[export_url_vars company_id limit_to_users_in_group_id]>Add primary contact</a>\n"

Forms

Arrays – Create form

  1. <input type='checkbox' name='payed_p.$cost_id'>

Arrays – Handle form

ad_proc im_costs_base_component {
   ...
} {
    payed_p:array,optional
}

Add hidden vars to form

  1. [export_form_vars company_id return_url]

Create list from form var array:

set payed_p_list [array names payed_p]
 
set hours_list [array names hours]
foreach hour_rec $hours_list {
    ns_log NOTICE "hour_rec $hour_rec /  value: $hours($hour_rec)"
}

Sanity checks

if {![im_column_exists im_hours internal_note]} {
    ad_return_complaint 1 "Internal error in intranet-timesheet2:<br>
        The field im_hours.internal_note is missing.<br>
        Please notify your system administrator to upgrade
        your system to the latest version.<br>
    "
    ad_script_abort
}

Date & Time operations

Moved to:
http://www.project-open.org/documentation/date_and_time_operations

Math operations


using round in sql

        /packages/intranet-reporting-finance/www/finance-expenses.tcl
        select
                ...
                round((c.paid_amount * im_exchange_rate(c.effective_date::date, c.currency, 'EUR')) :: numeric, 2) as paid_amount_converted,
        from ...

Menus


Add menu:

create or replace function inline_1 ()
returns integer as '
declare
        v_menu                  integer;
        v_parent_menu         integer;
        v_employees             integer;
begin
        select group_id into v_employees from groups where group_name = ''Employees'';
 
        select menu_id into v_parent_menu
        from im_menus where label = ''projects_admin'';
 
        v_menu := im_menu__new (
                null,                                   -- p_menu_id
                ''im_menu'',                            -- object_type
                now(),                                  -- creation_date
                null,                                   -- creation_user
                null,                                   -- creation_ip
                null,                                   -- context_id
                ''intranet-cust-kw'',   -- package_name
                ''project_closure_wf'', -- label
                ''Close Project'',      -- name
                ''/intranet-cust-kw/workflow-close-project?project_id=$project_id'',   -- url
                -10,                                    -- sort_order
                v_parent_menu,                          -- parent_menu_id
                null                                    -- p_visible_tcl
        );
 
        PERFORM acs_permission__grant_permission(v_menu, v_employees, ''read'');
        return 0;
end;' language 'plpgsql';
select inline_1 ();
drop function inline_1();

Get menu items for side menu:

[im_menu_ul_list -no_uls 1 "projects_admin" {}]

Setting Subnavbar:

# Setup the subnavbar
set bind_vars [ns_set create]
ns_set put $bind_vars project_id $project_id
set project_menu_id [db_string parent_menu "select menu_id from im_menus where label='project'" -default 0]
 
set sub_navbar [im_sub_navbar \
    -components \
    -base_url "/intranet/projects/view?project_id=$project_id" \
    $project_menu_id \
    $bind_vars "" "pagedesriptionbar" "project_timesheet_task"]

Setting menu label active:

set menu_label "reporting-finance-expenses"

Context Help

a) Add to [file].tcl

set show_context_help_p 1

b) Add to [file].adp

<property name="show_context_help_p">@show_context_help_p;noquote@</property>

AOL Server API


Avoid absolute file paths when using ‘ns_returnredirect’

ns_returnredirect "upload-files.tcl?inq_id=$security_token"
ns_returnfile 200 "application/zip" $filepath
ns_return 200 text/html 1
set result [ad_parse_template -params $params "/packages/intranet-core/www/related-objects-component"]
[ns_conn url] #returns for example: /intranet/projects
[im_url_with_query] # returns for example: /intranet/projects/view?project%5fid=34352

Portlet/Plugin

Create Plugin

SELECT  im_component_plugin__new (
        null,                           -- plugin_id
        'acs_object',                -- object_type
        now(),                        -- creation_date
        null,                           -- creation_user
        null,                           -- creation_ip
        null,                           -- context_id
        'Home Random Portrait', -- plugin_name
        'intranet-core',             -- package_name
        'right',                        -- location
        '/intranet/index',           -- page_url
        null,                           -- view_name
        5,                              -- sort_order
        'im_random_employee_component'  -- component_tcl
);

Set permissions for plugin 

        select  plugin_id
        into    v_plugin_id
        from    im_component_plugins pl
        where   plugin_name = ''Requests for Quote'';
 
        PERFORM im_grant_permission(v_plugin_id, v_cust_id, ''read'');

Workflow

Actions

im_workflow__set_object_status_id
im_workflow__assign_to_owner

Templates / Control structures in ADP pages

VAR replacements

<%= [eval ad_context_bar $context_bar] %>

Control Structures & Loops

  <multiple name="user_stuff">
     <if @user_stuff.age@ le 21>
         <tr bgcolor="red">
     </if>
     <else>
         <tr bgcolor="green">
     </else>
         <td>@user_stuff.user_name@</td>
         <td>@user_stuff.age@</td>
         <td>@user_stuff.shoesize@</td>
     </tr>
  </multiple>

Callbacks

Define Callback:

Callbacks require a callback definition in *-init.tcl:
Example: intranet-core-init.tcl

ad_proc -public -callback im_dynfield_attribute_after_update {
    {-object_type:required}
    {-attribute_name:required}
} {
    Callback to be executed after an attribute has been changed
} -

Please note the hyphen at the end of the code!

Callback implementation:

Add the function to your [PACKAGE_NAME]-procs.tcl:

ad_proc -public -callback im_dynfield_attribute_after_update -impl intranet-core  {
    {-object_type:required}
    {-attribute_name:required}
} {
    << EXPLAIN >>
} {
    << FUNCTION BODY >>
}

Callback Call

Place a call to the callback anywhere in your code

callback im_dynfield_attribute_after_update -object_type "im_project" -attribute_name "christmas_card"

Include JS/CSS ( ]po[ V4.0 )

 
template::head::add_css -href "/intranet-sencha/css/ext-all.css" -media "screen" -order 1
template::head::add_javascript -src "/intranet-sencha/js/ext-all-debug-w-comments.js" -order 1

Parameters

UPDATE
    apm_parameter_values
SET
    attr_value=0
WHERE
    parameter_id in (
        select
            parameter_id
        from
            apm_parameters
        where
            parameter_name = 'SuppressHttpPort' and
            package_key = 'acs-tcl'
      )

Notifications:

/intranet-helpdesk/www/new.tcl

    notification::new \
        -type_id [notification::type::get_type_id -short_name ticket_notif] \
        -object_id $ticket_id \
        -response_id “” \
        -notif_subject $ticket_name \
        -notif_text $message

Ubuntu GNU/Linux CD Creator

Dec 292008

Reconstructor uses the Desktop(Live), Alternate(Install), or Server disc as a base, and then allows for user customization.

For the Ubuntu Desktop base, you can customize the entire environment. For instance, you can add/remove software, change the default look (splash, themes, fonts, wallpaper, etc.), add desktop links, etc.

For the Alternate and Server bases, you can add any additional software to the disc that you would like installed.

Reconstructor is written in python and is licensed under the GNU General Public License (GPL).

http://reconstructor.aperantis.com/index.php

]project-open[ goes AJAX - Lessons learned

Nov 302008

One thing we have learned today is that going to AJAX requires also a review of current HTML. Even though most pages show only warnings when HTML is validated, they are still not conform when using script libraries.

In our example we’d like extend the Home Page Project Component to introduce tabs. “open” projects and “potential” projects should be shown in the same component, accessible through tabs. In order to implement tabs we are using the YUI TabView Control.

Here’s the current code to create the main table that holds the table of projects (intranet-component-procs.tcl):

Implementation is done as follows:

This results in:

List of projects (erroneous)

As we see, Tab Two Content is visible, clicking on the tabs also results in undesired behaviour.

Firebugs shows us the following code:
Resulting code as listed in Firebug

FF renders based on the definition for each element an additional font tags which then breaks the JS code.

Older Entries Newer Entries