Index: openacs-4/packages/spreadsheet/sql/postgresql/spreadsheet-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/spreadsheet/sql/postgresql/spreadsheet-create.sql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/spreadsheet/sql/postgresql/spreadsheet-create.sql 26 Aug 2010 05:54:32 -0000 1.3
+++ openacs-4/packages/spreadsheet/sql/postgresql/spreadsheet-create.sql 27 Aug 2010 00:13:22 -0000 1.4
@@ -24,6 +24,9 @@
instance_id integer,
-- object_id of mounted instance (context_id)
+ user_id integer,
+ -- user_id of user that created spreadsheet
+
name_abbrev varchar(40),
-- no spaces, single word reference that can be used in urls, filenames etc
@@ -46,6 +49,8 @@
last_modified timestamptz,
-- should be the max(qss_cells.last_modified) for a sheet_id
+ last_modified_by integer,
+ -- user_id of user that last modified spreadsheet
sheet_status varchar(8)
-- value will likely be one of
@@ -56,7 +61,7 @@
CREATE TABLE qss_cells (
id integer not null primary key,
- sheet_id varchar(40) not null,
+ sheet_id integer not null,
-- should be a value from qss_sheets.sheet_id
cell_row integer not null,
cell_column integer not null,
@@ -103,7 +108,8 @@
last_calculated timestamptz,
-- handy for checking when cell value dependencies have changed
- last_modified timestamptz
+ last_modified timestamptz,
-- data entry (cell value) last changed
-
+ last_modified_by integer
+ -- user that last modified cell
);
Index: openacs-4/packages/spreadsheet/tcl/spreadsheet-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/spreadsheet/tcl/spreadsheet-procs.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/spreadsheet/tcl/spreadsheet-procs.tcl 27 Aug 2010 00:13:22 -0000 1.1
@@ -0,0 +1,166 @@
+ad_library {
+
+ routines for accessing and managing spreadsheets
+ @creation-date 25 August 2010
+ @cs-id $Id:
+}
+
+namespace eval spreadsheet {}
+
+ad_proc -public spreadsheet::new_id {
+} {
+ gets new spreadsheet id
+} {
+ set spreadsheet_id [db_nextval qss_id_seq]
+ return $spreadsheet_id
+}
+
+ad_proc -private spreadsheet::status_q {
+ sheet_id
+} {
+ gets spreadsheet status
+} {
+ db_0or1row get_spreadsheet_status "select sheet_status from qss_sheets where id = :sheet_id"
+ if { ![info exists sheet_status] } {
+ set sheet_status ""
+ }
+ return $sheet_status
+}
+
+ad_proc -public spreadsheet::create {
+ id
+ name_abbrev
+ sheet_title
+ style_ref
+ sheet_description
+ {orientation "RC"}
+} {
+ creates spreadsheet
+ Orientation RC means fixed columns, variable number of rows.
+ Orientation CR means fixed rows, variable number of columns.
+} {
+ # if id exists, assume it's a double click or bad info, ignore
+ set success 0
+ if { [spreadsheet::status_q $id] eq "" } {
+ set package_id [ad_conn package_id]
+ set user_id [ad_conn user_id]
+ set create_p [permission::permission_p -party_id $user_id -object_id $package_id -privilege create]
+ if { $create_p } {
+ db_dml create_new_sheet {insert into qss_sheets
+ (id, instance_id, name_abbrev, style_ref, sheet_description, orientation,row_count,column_count,last_calclated,last_modified, last_modified_by)
+ values (:id, :package_id, :name_abbrev, :style_ref, :sheet_description, :orientation, '0', '0', now(), now(), :user_id ) }
+ }
+ set success $create_p
+ }
+ return $success
+}
+
+ad_proc -public spreadsheet::list {
+ package_id
+ {user_id "0"}
+} {
+ returns list of lists of existing sheets: {id name_abbrev sheet_title last_modified by_user}
+ If user_id is passed, results are sheets that the user has created or modified within package_id.
+} {
+ if { $user_id eq 0 } {
+ set table [db_list_of_lists get_list_of_spreadsheets {select id name_abbrev sheet_title last_modified by_user from qss_sheets where instance_id = :package_id order by sheet_title } ]
+ } else {
+ set table [db_list_of_lists get_list_of_spreadsheets_for_user_id {select id name_abbrev sheet_title last_modified by_user
+ from qss_sheets where ( instance_id = :package_id and user_id = :user_id ) or instance_id in
+ ( select instance_id from qss_cells where sheet_id in ( select id from qss_sheets where instance_id = :package_id unique ) and last_modified_by = :user_id ) order by sheet_title } ]
+ }
+}
+
+ad_proc -public spreadsheet::one {
+ sheet_id
+} {
+ returns attributes of a sheet in list format: {id name_abbrev sheet_title last_modified by_user orientation row_count column_count last_calculated last_modified sheet_status}
+} {
+ set package_id [ad_conn package_id]
+ set user_id [ad_conn user_id]
+ set read_p [permission::permission_p -party_id $user_id -object_id $package_id -privilege read]
+ set sheet_list [db_list get_spreadsheet_attributes {select id name_abbrev sheet_title last_modified by_user orientation row_count column_count last_calculated last_modified sheet_status from qss_sheets where instance_id = :package_id and id = :sheet_id } ]
+}
+
+ad_proc -public spreadsheet::read {
+ sheet_id
+ {start ""}
+ {count ""}
+} {
+ reads spreadsheet, returns list_of_lists
+ If orientation is RC, each element of list is a row.
+ If orientation is CR, each element of list is a column.
+ first element contains header references
+} {
+ if { [ad_var_type_check_number_p $start] && $start > 0 && [ad_var_type_check_number_p $count] && $count > 0 } {
+ set page_start $start
+ set page_size $count
+ }
+ set package_id [ad_conn package_id]
+ set user_id [ad_conn user_id]
+ set read_p [permission::permission_p -party_id $user_id -object_id $package_id -privilege read]
+ # if orientation is RC, start is start_row, count is num_of_rows
+ # if orientation is CR, start is start_col, count is num_of_columns
+ if { $read_p } {
+ if { [info exists $page_start] } {
+ set table [db_list_of_lists get_all_cells_of_sheet {select id, cell_row, cell_column, cell_value, cell_value_sq, cell_format, cell_proc, cell_calc_depth, cell_name, cell_title from qss_cells where sheed_id = :sheet_id} limit :page_size offest :page_start ]
+ } else {
+ set table [db_list_of_lists get_all_cells_of_sheet {select id, cell_row, cell_column, cell_value, cell_value_sq, cell_format, cell_proc, cell_calc_depth, cell_name, cell_title from qss_cells where sheed_id = :sheet_id} ]
+ }
+ } else {
+ set table [list ]
+ }
+ return $table
+}
+
+ad_proc -public spreadsheet::write {
+ id
+ list_of_lists
+} {
+ writes spreadsheet
+ assumes first element of list is a list of header references to columns (if orientatin is RC) or rows (if CR).
+ if row or column reference is not provided, appends new lines.
+ Reserved header references have features automatically attached to them:
+ cell_row (positive integer) if RC orientation, replaces an existing cell_row if it exists.
+ cell_column (positive integer) if CR orientation, replaces an existing cell_column if it exists.
+} {
+
+
+
+}
+
+ad_proc -public spreadsheet::delete {
+ spreadsheet_id
+} {
+ deletes spreadsheet
+} {
+ # validate permission, and confirm its existence
+ set package_id [ad_conn package_id]
+ set user_id [ad_conn user_id]
+ set delete_p [permission::permission_p -party_id $user_id -object_id $package_id -privilege delete]
+ if { $delete_p } {
+ # delete references in qss_cells
+ db_1row {spreadsheet_cell_count "select rows(*) as row_count from qss_cells where sheet_id = :spreadsheet_id and instance_id = :package_id"}
+ if { $row_count > 0 } {
+ db_dml spreadsheet_cells_delete_all "delete from qss_cells where sheet_id = :spreadsheet_id and instance_id = :package_id"
+ }
+ # delete reference in qss_sheets
+ db_1row {spreadsheet_cell_count "select rows(*) as sheet_exists_q from qss_cells where id = :spreadsheet_id and instance_id = :package_id"}
+ if { $sheet_exists_q } {
+ db_dml spreadsheet_delete "delete from qss_sheets where id = :spreadsheet_id and instance_id = :package_id"
+ }
+ set success 1
+ } else {
+ set success 0
+ }
+ return $success
+}
+
+ad_proc -public spreadsheet::list {
+} {
+ returns list_of_lists of available spreadsheets
+ each list item contains:
+ id, name_abbrev, sheet_title,row_count,column_count,last_calculated,last_modified,status
+} {
+
+}