Index: openacs-4/packages/accounts-finance/tcl/distribution-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/accounts-finance/tcl/distribution-procs.tcl,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/accounts-finance/tcl/distribution-procs.tcl	15 Jun 2012 22:24:45 -0000	1.1
@@ -0,0 +1,149 @@
+ad_library {
+
+    distribution based routines used for statistics, modeling etc
+    @creation-date 23 May 2012
+    @cvs-id $Id:
+}
+
+namespace eval acc_fin {}
+
+ad_proc -public qaf_distribution_points_create {
+    distribution_p_list
+    {number_of_points ""}
+    {min_sum_of_outputs ""}
+    {interpolate_rightmost_p "0"}
+    {y_col "0"}
+    {x_col "1"}
+} {
+    Given a distribution curve represented as a discrete, ordered set of value- probability pairs (y,x) in a list, for example: { {1 .5} { 3 .3} { 6 .1} { 12 .06} { 250 .04} }. Any extra columns are ignored. Rightmost (last) entry is highest y value; Intervals are not assumed to be equal. The sum of the probabilities should equal 1.  Returns a list of a random set of the discrete numbers that approximate the distribution.  (FUTURE IMPLEMENTATIONs: To accomodate a varying rightmost discrete number which represents a wide range of perhaps infrequent numbers, set interpolate_rightmost_p to 1 use interpolation on the rightmost discrete number.  If the discrete numbers represent a curve, set interpolate_p to 1.)
+} {
+    set amount_p [expr { [string length $min_sum_of_outputs] > 0 } ]
+    set count_p [expr { [string length $number_of_points] > 0 } ]
+        
+    # count_max is the number of discrete numbers
+    set count_max [llength $distribution_p_list]
+    set curve_error 0
+    # build support arrays
+    set area(-1) 0
+    set count 0
+    set total_pct 0
+#ns_log Notice "qaf_distribution_points_create: y_col '$y_col' x_col '$x_col'"
+#ns_log Notice "qaf_distribution_points_create: distribution_p_list $distribution_p_list"
+    foreach row $distribution_p_list {
+        set yvalue [lindex $row $y_col]
+        set frequency [lindex $row $x_col]
+        # p_val(index) discrete values
+        set p_val($count) $yvalue
+        # area(index) is the area under the distribution curve to the left of the sale amt
+#ns_log Notice "qaf_distribution_points_create: yvalue '$yvalue' frequency '$frequency'"
+        # total_pct adds all the rcp amounts to confirm it is 100%
+        # frequency must be a number
+        if { [ad_var_type_check_number_p $frequency] } {
+#            ns_log Notice "qaf_distribution_points_create: frequency $frequency"
+            set area($count) [expr { $area([expr { $count - 1 } ]) + $frequency } ] 
+            set total_pct [expr { $total_pct + $frequency } ]
+        } else {
+            set curve_error 1
+        }
+        incr count
+    }
+    if { $total_pct != 1. } {
+        # distribution is not 100% represented
+        # recalculate distribution to 100% representation
+        # ie. divide each frequency by the total
+#ns_log Notice "qaf_distribution_points_create: distribution_p_list $distribution_p_list"
+        set area(-1) 0
+        set count 0
+        set total_check 0.
+        foreach row $distribution_p_list {
+            set yvalue [lindex $row $y_col]
+            set frequency [lindex $row $x_col]
+            if { [ad_var_type_check_number_p $frequency] } {
+                set area($count) [expr { $area([expr { $count - 1 } ]) + ( $frequency / $total_pct ) } ]
+                set total_check [expr { $total_check + $frequency } ]
+            }
+            incr count
+        }
+        if { $total_check != 1. } {
+            ns_log Warning "qaf_distribution_points_create: unable to represent distribution equal to 1. total_check = ${total_check}"
+        }
+        set total_pct $total_check
+    }
+        
+    # initial set conditions
+    set data_sum 0.
+    set point_count 0
+    set data_list [list ]
+
+    if { $total_pct != 0 } {
+        # every case assumes to reach target
+        while { ( $amount_p && ( $data_sum < $min_sum_of_outputs ) ) || ( $count_p && ($point_count < $number_of_points ) ) } {
+            
+            set point_seed [expr { rand() } ]
+            set count 0
+            # We have area under a normalized curve, let's find interval 
+            while { $point_seed > $area($count) } {
+            incr count
+            }
+            
+            if { $count > $count_max } {
+                ns_log Warning "qaf_distribution_points_create: Count is right of rightmost discrete point. count $count count_max ${count_max} point_seed $point_seed"
+                set p_y $p_val($count_max)
+            } else {
+                set p_y $p_val($count)
+            }
+            
+            incr point_count
+            lappend data_list $p_y
+            set data_sum [expr { $data_sum + $p_y } ]
+        }
+    }
+    return $data_list
+}
+
+ad_proc -public qaf_discrete_dist_report {
+    population_list
+} {
+    Given a list of numbers, returns a paired list of discrete numbers and their frequency (as a decimal)
+} {
+    # make a probability distribution table
+    set pcount [llength $population_list]
+    
+    foreach pnumber $population_list {
+        if { [string length $pnumber] == 0 } {
+            #ignore
+            incr pcount -1
+        } else {
+            if { [info exists pop_array($pnumber) ] } {
+                incr pop_array($pnumber)
+            } else {
+                set pop_array($pnumber) 1
+            }
+        }
+    }
+    set frequencies_tot 0.
+    foreach {discrete_nbr count} [array get pop_array] {
+        set frequency [expr { $count / ( $pcount * 1.) } ]
+        set disc_array(${discrete_nbr}) $frequency
+        set frequencies_tot [expr { $frequencies_tot + $frequency } ]
+    }
+    if { $frequencies_tot != 1. } {
+        ns_log Warning "qaf_discrete_dist_report: Total of frequencies does not equal 1, but: ${frequencies_tot}"
+    }
+    set discrete_list [array names disc_array]
+    ns_log Notice "qaf_discrete_dist_report: discrete_list: $discrete_list"
+    set discrete_ord_list [lsort -real $discrete_list]
+    if { [catch { lsort -real $discrete_list } result] }  {
+        # there was an error
+        ns_log Notice "qaf_discrete_dist_report: discrete numbers contains non-real numbers."
+        set discrete_ord_list [lsort $discrete_list]
+    } else {
+        set discrete_ord_list $result
+    }
+    set distribution_list [list ]
+    foreach arr_name $discrete_ord_list {
+        lappend distribution_list $arr_name $disc_array($arr_name)
+    }
+
+    return $distribution_list
+}
\ No newline at end of file
Index: openacs-4/packages/accounts-finance/tcl/growth-rate-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/accounts-finance/tcl/growth-rate-procs.tcl,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/accounts-finance/tcl/growth-rate-procs.tcl	15 Jun 2012 22:24:45 -0000	1.1
@@ -0,0 +1,90 @@
+ad_library {
+
+    growth rate based routines used for modeling etc
+    @creation-date 23 May 2012
+    @cvs-id $Id:
+}
+
+namespace eval acc_fin {}
+
+ad_proc -public acc_fin::Fourt_Woodlock {
+    hh
+    tr
+    tu
+    mr
+    rr
+    ru
+} {
+    Returns the value of purchases per unit time, used in projecting sales revenue.
+    HH is the total number of households in the geographic area.
+    TR ("trial rate") is the percentage of households that purchase the product for the first time in a given time period. 
+    TU ("trial units") is the number of units purchased on the first purchase occasion. 
+    MR ("measured repeat") is the percentage of customers who will purchase it at least one more time within the first period of the product's launch. 
+    RR ("repeats per repeater") is the number of repeat purchases within that same year. 
+    RU ("repeat units") is the number of repeat units purchased for each repeat event.
+  See the Fourt-Woodlock equation:     http://en.wikipedia.org/wiki/Fourt-Woodlock_equation
+} {
+    
+    # v = ( hh * tr * tu ) + ( hh * tr * mr * rr * ru )
+        # or v = (hh * tr ) * ( tu + mr * rr * ru)
+        # where,
+        # v = value of purchases per unit time
+  
+    return $purchase_value
+} 
+
+        
+ad_proc -public acc_fin::logistic_curve {
+    t
+} {
+    The logistic function is a population growth curve. See http://en.wikipedia.org/wiki/Logistic_curve
+    Essentialy, this goes from 0.000 to 0.9999 between t = -10 to 10 with 4 significant digits.
+} {
+    # p(t) = 1 / ( 1 + pow(e,-t) ) = 1 / (1 + exp( -t)
+    # P might be considered to denote a population, where e is Euler's number and the variable t a unit of time
+    # the derivative provides a rate number at any point:
+    #d/dt P(t) = p(t) * ( 1 - P(t))
+    set tminus [expr { -1. * $t } ]
+    set p [expr { 1. / ( 1. + exp( $tminus ) ) } ]
+    return $p 
+}
+
+ad_proc -public acc_fin::logistic_curve_rate {
+    t
+} {
+    The rate of the logistic function  See http://en.wikipedia.org/wiki/Logistic_curve
+} {
+    # the derivative provides a rate number at any point:
+    #d/dt P(t) = p(t) * ( 1 - P(t))
+    set tminus [expr { -1. * $t } ]
+    set p_of_t [expr { 1. / ( 1. + exp( $tminus ) ) } ]
+    set rate [expr { $p_of_t * ( 1. - $p_of_t ) } ]
+    return $rate
+}
+
+
+ad_proc -public acc_fin::pos_sine_cycle {
+    t
+} {
+    The sine function is the basis of some patterns in modelling. This function result rises from 0 to 2 and back to 0 along a sine shaped curve between t = 0 and t = 360 degrees.
+} {
+    set pi [expr { acos(0) * 2. } ]
+    set trad [expr { $pi * $t / 180. } ]
+    # adjust t so that cycle begins at 0.
+    set trad [expr { $trad - ( $pi / 2. ) } ]
+    set f [expr { sin( $trad ) + 1. } ]
+    return $f
+}
+
+ad_proc -public acc_fin::pos_sine_cycle_rate {
+    t
+} {
+    The rate of the pos_sine_cycle at t degrees
+} {
+    set pi [expr { acos(0) * 2. } ]
+    set trad [expr { $pi * $t / 180. } ]
+    # adjust t so that cycle begins at 0.
+    set trad [expr { $trad - ( $pi / 2. ) } ]
+    set f [expr { cos( $trad ) } ]
+    return $f
+}
\ No newline at end of file
Index: openacs-4/packages/accounts-finance/tcl/math-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/accounts-finance/tcl/math-procs.tcl,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/accounts-finance/tcl/math-procs.tcl	15 Jun 2012 22:24:45 -0000	1.1
@@ -0,0 +1,34 @@
+ad_library {
+
+    math routines used for modeling etc
+    @creation-date 25 May 2012
+    @cvs-id $Id:
+}
+
+namespace eval acc_fin {}
+
+ad_proc -public interpolatep1p2_at_x {
+    p1_x
+    P1_y
+    p2_x
+    p2_y
+    p3_x
+} {
+    returns y value of third point (p3), given cartesion points p1(x,y), p2(x,y)
+} {
+    # interpolate, y=mx+b, slope = Dy/Dx = m, b = y axis intercept
+    if { $p2_x != $p1_x } {
+        set m [expr { ($p2_y - $p1_y) / ($p2_x - $p1_x) }]
+        if { $p2_x != 0 } {
+            set b [expr { $p2_y / ( $m * $p2_x ) } ]
+        } else { $p1_y != 0 } {
+            set b [expr { $p1_y / ( $m * $p1_x ) } ]
+        }
+        set p3_y [expr { ( $m * $p3_x ) + $b } ]
+    } else {
+        # vertical line. 
+        set p3_y ""
+    }
+    return $p3_y
+}
+ 
\ No newline at end of file
Index: openacs-4/packages/accounts-finance/tcl/number-progression-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/accounts-finance/tcl/number-progression-procs.tcl,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/accounts-finance/tcl/number-progression-procs.tcl	15 Jun 2012 22:24:45 -0000	1.1
@@ -0,0 +1,44 @@
+ad_library {
+
+    number-progression based routines 
+    @creation-date 26 May 2012
+    @cvs-id $Id:
+}
+
+namespace eval acc_fin {}
+
+ad_proc -public qaf_triangular_numbers {
+    number_of_points
+} {
+    Creates a progression of triangular numbers (see http://en.wikipedia.org/wiki/Triangular_number ) as a list.
+} {
+# code
+    set triangle_list [list ]
+    if { $number_of_points > 0 } {
+        set triangle_number 0
+        for {set i 0} { $i < $number_of_points } { incr i } {
+            set count [expr { $i + 1 } ]
+            set triangle_number [expr { $triangle_number + $count } ]
+            lappend triangle_list $triangle_number
+        }
+    }
+    return $triangle_list
+}
+
+
+ad_proc -public qaf_harmonic_terms {
+    number_of_points
+} {
+   Creates a progression of Harmonic series terms as decimal values defined as 1 + 1/2 + 1/3 + 1/4 + .. + 1/n. ( See http://en.wikipedia.org/wiki/Harmonic_series_%28mathematics%29 )
+} {
+  # code
+    set harmonic_list [list ]
+    if { $number_of_points > 0 } {
+        for {set i 0} { $i < $number_of_points } { incr i } {
+            set denominator [expr { $i + 1 } ]
+            set harmonic_number [expr { 1. / $denominator } ]
+            lappend harmonic_list $harmonic_number
+        }
+    }
+    return $harmonic_list
+}
Index: openacs-4/packages/spreadsheet/sql/postgresql/simple-table-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/spreadsheet/sql/postgresql/simple-table-create.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/spreadsheet/sql/postgresql/simple-table-create.sql	15 Jun 2012 22:18:39 -0000	1.1
@@ -0,0 +1,55 @@
+-- spreadsheet--simple-create.sql
+--
+-- @author Dekka Corp.
+-- @for OpenACS.org
+-- @cvs-id
+--
+
+-- we are not going to reference acs_objects directly, so that this can be used
+-- separate from acs-core.
+CREATE TABLE qss_simple_object_id_map (
+     sheet_id integer,
+     object_id integer 
+        -- sheet_id can be constrained to object_id for permissions
+);
+
+
+CREATE SEQUENCE qss_simple_id_seq start 10000;
+SELECT nextval ('qss_simple_id_seq');
+
+
+CREATE TABLE qss_simple_table (
+        id integer not null primary key,
+        template_id integer,
+        instance_id integer,
+        -- object_id of mounted instance (context_id)
+        user_id integer,
+        -- user_id of user that created spreadsheet
+        name varchar(40),
+        title varchar(80),
+        cell_count integer,
+        row_count integer,
+        trashed varchar(1),
+        popularity integer,
+        flags varchar(12),
+        last_modified timestamptz,
+        created timestamptz,
+        comments text
+     );    
+
+create index qss_simple_table_id_idx on qss_simple_table (id);
+create index qss_simple_table_template_id_idx on qss_simple_table (template_id);
+create index qss_simple_table_instance_id_idx on qss_simple_table (instance_id);
+create index qss_simple_table_user_id_idx on qss_simple_table (user_id);
+create index qss_simple_table_trashed_idx on qss_simple_table (trashed);
+
+    CREATE TABLE qss_simple_cells (
+        table_id integer not null,
+        --  should be a value from qss_simple_table.id
+        -- no need to track revisions. Each table is a new revision.
+        cell_rc varchar(20) not null, 
+        cell_value varchar(1025)
+        -- user input value
+        );
+
+create index qss_simple_cells_idx on qss_simple_cells (table_id);
Index: openacs-4/packages/spreadsheet/sql/postgresql/simple-table-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/spreadsheet/sql/postgresql/simple-table-drop.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/spreadsheet/sql/postgresql/simple-table-drop.sql	15 Jun 2012 22:18:39 -0000	1.1
@@ -0,0 +1,17 @@
+-- spreadsheet-simple-drop.sql
+--
+-- @author Dekka Corp.
+-- @for OpenACS.org
+-- @cvs-id
+--
+DROP index qss_simple_cells_idx;
+DROP TABLE qss_simple_cells;
+
+DROP index qss_simple_table_id_idx;
+DROP index qss_simple_table_template_id_idx;
+DROP index qss_simple_table_instance_id_idx;    
+DROP index qss_simple_table_user_id_idx;        
+DROP TABLE qss_simple_table;
+
+DROP TABLE qss_simple_object_id_map;
+DROP SEQUENCE qss_simple_id_seq;
Index: openacs-4/packages/spreadsheet/tcl/form-helper-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/spreadsheet/tcl/form-helper-procs.tcl,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/spreadsheet/tcl/form-helper-procs.tcl	15 Jun 2012 22:18:39 -0000	1.1
@@ -0,0 +1,298 @@
+ad_library {
+
+    routines for helping render form data or presentation for form data
+    @creation-date 15 May 2012
+    @cs-id $Id:
+}
+
+ad_proc -public qss_txt_to_tcl_list_of_lists {
+    textarea
+    linebreak
+    delimiter
+} {
+    Converts a csv/txt style table into a tcl list_of_lists
+} {
+    set lists_list [list]
+    set row_list [split $textarea $linebreak]
+    # clean the rows of any extra linefeeds etc
+
+    foreach row $row_list {
+        set columns_list [split $row $delimiter]
+        # rebuild columns_list to clean it of any remaining invisible characters
+        set column_set [list ]
+        foreach column $columns_list {
+            regsub -all -- {[\n\r\f\v]} $column {} col_version2
+            regsub -expanded -all -- {[[:cntrl:]]} $col_version2 {} col_version3
+            lappend column_set $col_version3
+        }
+        set columns_list $column_set
+
+        set columns [llength $columns_list]
+        ns_log Notice "qss_txt_to_tcl_list_of_lists: col len $columns, columns_list ${columns_list}"
+        if { $columns > 0 } {
+            lappend lists_list $columns_list
+        }
+    }
+    return $lists_list
+}
+
+ad_proc -public qss_txt_table_stats { 
+    textarea 
+} {
+    determines the best guess linebreak_char delimiter rows_count columns_count of a cvs/txt table
+    and returns these values a list
+} {
+    # scan to guess # of rows and cols
+
+    set linebreaks_list [list \n \r \f \v ]
+    set array table_arr
+
+    # determine row delimiter
+    set lineC 0
+    set max_rows 0
+    foreach linebreak $linebreaks_list {
+        set row_set [split $textarea $linebreak]
+        set linesC [llength $row_set]
+#ns_log Notice "qss_txt_table_stats: rows $linesC for linebreak_idx/lineC $lineC"
+        if { $linesC > $max_rows } {
+            set linebreak_idx $lineC
+            set max_rows $linesC
+            set linebreak_char $linebreak
+#            set rows_set $row_set 
+            # remove any remaining delimiters
+            set rows_set [list ]
+            foreach line $row_set {
+                regsub -all -- {[\n\r\f\v]} $line {} line2
+#                regsub -expanded -all -- {[[:cntrl:]]} $line2 {} line3
+                lappend rows_set $line2
+            }
+            set rows_count [llength $rows_set]
+        }
+        incr lineC
+    }
+#ns_log Notice "qss_txt_table_stats: rows_set: $rows_set"
+
+
+    set rowsC [llength $rows_set]
+    # determine column delimiter
+    set delimiters_list [list "\t" " " "," "|" "!"]
+
+    set delimC 0
+    set columns_arr(0-avg) 0.
+    foreach delimiter $delimiters_list {
+        array unset columns_arr
+        set max_cols 0
+        set many_cols_sum 0.
+        set few_cols_sum 0.
+        set many_cols_rows 0
+        set few_cols_rows 0
+        set colC_list [list]
+        set cols_sum 0.
+        # get average number of rows and avg variance for each delim
+        # When avg cols/row is > 2 ignore rows with (0 or) one column when calculating avg variance
+        #   Do this by counting these rows, averaging them, then averaging to the other set if <= 2.
+
+        # if there is a significant median value, use it instead.
+        foreach row $rows_set {
+            set col_set [split $row $delimiter]
+            set colsC [llength $col_set]
+ ns_log Notice "qss_txt_table_stats: delimiter $delimiter colsC $colsC"
+            if { [info exists columns_arr(${colsC})] } {
+                set columns_arr(${colsC}) [expr { $columns_arr(${colsC}) + 1 } ]
+            } else {
+                set columns_arr(${colsC}) 1
+            }
+            set cols_sum [expr { $cols_sum + $colsC } ]
+            lappend colC_list $colsC
+            if { $colsC > 2 } {
+                set many_cols_sum [expr { $many_cols_sum + $colsC } ]
+                incr many_cols_rows
+            } else {
+                set few_cols_sum [expr { $few_cols_sum + $colsC } ]
+                incr few_cols_rows
+            }
+        }
+        if { $few_cols_rows > 0 } {
+            set few_cols_avg [expr { $few_cols_sum / $few_cols_rows } ]
+        } else {
+            set few_cols_avg 0
+        }
+        if { $many_cols_rows > 0 } {
+            set many_cols_avg [expr { $many_cols_sum / $many_cols_rows } ]
+        } else {
+            set many_cols_avg 0
+        }
+        set cols_avg [expr { $cols_sum / $max_rows } ]
+        if { $cols_avg > 2 } {
+            set cols_avg $many_cols_avg
+#            set rowsC $many_cols_rows
+        } else {
+            set cols_avg $few_cols_avg
+#            set rowsC $few_cols_rows
+        }
+        # determine variance
+        set sum2 0.
+        set rowCt 0
+        foreach colCt $colC_list {
+            if { $colCt > 0 } {
+                set sum2 [expr { $sum2 + pow( $colCt - $cols_avg , 2. ) } ]
+                incr rowCt
+            }
+        }
+        if { $rowCt > 1 } {
+            set variance [expr { $sum2 / ( $rowCt - 1. ) } ]
+        } else {
+            set variance 99999.
+        }
+
+        #what is median of columns?
+        set median 0
+        foreach { column count } [array get columns_arr] {
+            if { $count > $median } {
+                set median_old $median
+                set median $column
+            }
+        }
+
+        # column count expands (not contracts) if delimeter is shared in data
+        if { $median_old == 0 } {
+            set median_old $median
+        }
+        set median_diff [expr { $median - $median_old } ]
+
+        set median_pct_diff [expr { $median_diff / $median_old } ]
+
+            
+        if { $median_pct_diff > 1.1 && $median_pct_diff < 2.0 } {
+            set median_old2 $median_old
+            set median_old $median
+            set median $median_old2
+        }
+# if row and column delimiter are same (such as space), manually step through table collecting info?
+# determine likely matrix size and variations, then step through to sqrt(max_rows) looking for data type patterns.
+# NOT IMPLEMENTED
+
+        # For best guess, the average converges toward the median..
+        set median_diff_abs [expr { abs( $median_diff ) } ]
+        if { $variance < $median_diff_abs && $cols_avg < $median } {
+            set bguess $cols_avg
+            set bguessD $variance
+        } else {
+            set bguess $median
+            set bguessD $median_diff_abs
+        }
+
+        set table_arr(${delimC}-avg) $cols_avg
+        set table_arr(${delimC}-variance) $variance
+        set table_arr(${delimC}-median) $median
+        set table_arr(${delimC}-medianD) $median_old
+        set table_arr(${delimC}-bguess) $bguess
+        set table_arr(${delimC}-bguessD) $bguessD
+        set table_arr(${delimC}-rows) $rowCt
+        set table_arr(${delimC}-delim) $delimiter
+        set table_arr(${delimC}-linebrk) $linebreak_char
+        incr delimC
+#ns_log Notice "qss_txt_table_stats: delimC $delimC cols_avg $cols_avg variance $variance median $median median_old $median_old bguess $bguess bguessD $bguessD rowCt $rowCt"
+    }
+    set bguessD $table_arr(0-bguessD)
+    set bguess $table_arr(0-bguess)
+    set rows_count $table_arr(0-rows)
+    set delimiter $table_arr(0-delim)
+    for { set i 0 } { $i < $delimC } { incr i } {
+        if { $table_arr(${i}-bguessD) <= $bguessD && $table_arr(${i}-bguess) > 1 } {
+            if { ( $bguess > 1 && $table_arr(${i}-bguess) < $bguess ) || $bguess < 2 } {
+                set bguess $table_arr(${i}-bguess)
+                set bguessD $table_arr(${i}-bguessD)
+                set rows_count $table_arr(${i}-rows)
+                set delimiter $table_arr(${i}-delim)
+            }
+        }
+    }
+    set return_list [list $linebreak_char $delimiter $rows_count $bguess]
+#    ns_log Notice "qss_txt_table_stats: return_list $return_list"
+    return $return_list
+}
+
+
+
+ad_proc -public qss_list_of_lists_to_html_table { 
+    table_list_of_lists 
+    {table_attribute_list ""}
+    {td_attribute_lists ""}
+} {
+    Converts a tcl list_of_lists to an html table, returns table as text/html
+    table_attribute_list can be a list of attribute pairs to pass to the TABLE tag: attribute1 value1 attribute2 value2..
+    The td_attribute_lists adds attributes to TD tags at the same position as table_list_of_lists 
+    the list is represented {row1 {cell1} {cell2} {cell3} .. {cell x} } {row2 {cell1}...}
+    Note that attribute - value pairs in td_attribute_lists can be added uniquely to each TD tag.
+} {
+    set table_html "<table"
+    foreach {attribute value} $table_attribute_list {
+        regsub -all -- {\"} $value {\"} value
+        append table_html " $attribute=\"$value\""
+    }
+    append table_html ">\n"
+    set row_i 0
+    set column_i 0
+    #setup repeat pattern for formatting rows, if last formatting row is not blank
+    set repeat_last_row_p 0
+    if { [llength [lindex $td_attribute_lists end] ] > 0 } {
+        # this feature only comes into play if td_attrubte_lists is not as long as table_list_of_lists
+        set repeat_last_row_p 1
+        set repeat_row [expr { [llength $td_attribute_lists] - 1 } ]
+    }
+    foreach row_list $table_list_of_lists {
+        append table_html "<tr>"
+        foreach column $row_list {
+            append table_html "<td"
+            if { $repeat_last_row_p && $row_i > $repeat_row } {
+                set attribute_value_list [lindex [lindex $td_attribute_lists $repeat_row] $column_i]
+
+            } else {
+                set attribute_value_list [lindex [lindex $td_attribute_lists $row_i] $column_i]
+            }
+            foreach {attribute value} $attribute_value_list {
+                regsub -all -- {\"} $value {\"} value
+                append table_html " $attribute=\"$value\""
+            }
+            append table_html ">${column}</td>"
+            incr column_i
+        }
+        append table_html "</tr>\n"
+        incr row_i
+        set column_i 0
+    }
+    append table_html "</table>\n"
+    return $table_html
+}
+
+ad_proc -public qss_lists_to_text { 
+    table_list_of_lists
+    {row_delimiter "\n"}
+    {column_delimiter ","}
+ } {
+    Converts a tcl list_of_lists to content suitable to be used with a textarea tag.
+} {
+    foreach row_list $table_list_of_lists {
+        set col_delim ""
+        foreach column $row_list {
+            append table_html $col_delim
+            append table_html $column
+            set col_delim $column_delimiter
+        }
+        append table_html $row_delimiter
+    }
+    return $table_html
+}
+
+
+ad_proc -public qss_form_table_to_table_lists {
+    table_array_name
+} {
+    returns a table represented as a list of lists from a table represtented as an array.
+} {
+    upvar $table_array_name table_array
+
+    # get array indices as a sorted list
+    set array_idx_list [lsort [array names table_array]]
+}
Index: openacs-4/packages/spreadsheet/tcl/simple-table-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/spreadsheet/tcl/simple-table-procs.tcl,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/spreadsheet/tcl/simple-table-procs.tcl	15 Jun 2012 22:18:39 -0000	1.1
@@ -0,0 +1,391 @@
+ad_library {
+
+    API for the qss_simple_table 
+    @creation-date 15 May 2012
+    @cs-id $Id:
+}
+
+ad_proc -public qss_table_create { 
+    cells_list_of_lists
+    name
+    title
+    comments
+    {template_id ""}
+    {flags ""}
+    {instance_id ""}
+    {user_id ""}
+} {
+    Creates simple table. returns table_id, or 0 if error. instance_id is usually package_id
+} {
+
+#code.  Be sure to check permissions
+
+#CREATE TABLE qss_simple_table (
+#        id integer not null primary key,
+#        instance_id integer,
+#        -- object_id of mounted instance (context_id)
+#        user_id integer,
+#        -- user_id of user that created spreadsheet
+#        name varchar(40),
+#        title varchar(80),
+#        cell_count integer,
+#        row_count integer,
+#        trashed varchar(1),
+#        popularity integer,
+#        flags varchar(12),
+#        last_modified timestamptz,
+#        created timestamptz,
+#        comments text
+#     );    
+
+#    CREATE TABLE qss_simple_cells (
+#        table_id integer not null,
+#        --  should be a value from qss_simple_table.id
+#        -- no need to track revisions. Each table is a new revision.
+#        cell_rc varchar(20) not null, 
+#        cell_value varchar(1025)
+#        -- user input value
+#        );
+    if { $instance_id eq "" } {
+        # set instance_id package_id
+        set instance_id [ad_conn package_id]
+    }
+    if { $user_id eq "" } {
+        set user_id [ad_conn user_id]
+        set untrusted_user_id [ad_conn untrusted_user_id]
+    }
+    set create_p [permission::permission_p -party_id $user_id -object_id $instance_id -privilege create]
+    ns_log Notice "qss_table_create: create_p $create_p with raw rows cells_list_of_lists [llength $cells_list_of_lists]"
+    if { $create_p } {
+        set table_id [db_nextval qss_simple_id_seq]
+        ns_log Notice "qss_table_create: new table_id $table_id"
+        set table_exists_p [db_0or1row simple_table_get_id {select name from qss_simple_table where id = :table_id } ]
+        if { !$table_exists_p } {
+            if { $template_id eq "" } {
+                set template_id $table_id
+            }
+            db_transaction {
+                db_dml simple_table_create { insert into qss_simple_table
+                    (id,template_id,name,title,comments,instance_id,user_id)
+                    values (:table_id,:template_id,:name,:title,:comments,:instance_id,:user_id) }
+                set row 0
+                set cells 0
+                foreach row_list $cells_list_of_lists {
+                    incr row
+                    set column 0
+                    foreach cell_value $row_list {
+                        incr column
+                        incr cells
+                        set cell_rc "r[string range "0000" 0 [expr { 3 - [string length $row] } ] ]${row}c[string range "0000" 0 [expr { 3 - [string length $column] } ] ]${column}"
+                        # if cell_value has length of zero, then don't insert
+                        if { [string length $cell_value] > 0 } {
+#ns_log Notice "qss_table_create: cell_rc $cell_rc cell_value $cell_value"
+                            db_dml qss_simple_cells_create { insert into qss_simple_cells
+                                (table_id,cell_rc,cell_value)
+                                values (:table_id,:cell_rc,:cell_value)
+                            }
+                        }
+                    }
+                }
+ns_log Notice "qss_table_create: total $row rows, $cells cells"
+                db_dml simple_table_update_rc { update qss_simple_table
+                    set row_count =:row,cell_count =:cells
+                    where id = :table_id }
+
+            } on_error {
+                set table_id 0
+                ns_log Error "qss_table_create: general psql error during db_dml"
+            }
+        } else {
+            set table_id 0
+            ns_log Warning "qss_table_create: table already exists for table_id $table_id"
+        }
+    }
+    return $table_id
+}
+
+ad_proc -public qss_table_stats { 
+    table_id
+    {instance_id ""}
+    {user_id ""}
+} {
+    Returns table stats as a list: name, title, comments, cell_count, row_count, template_id, flags, trashed, popularity, time last_modified, time created, user_id.
+    Columns not listed, as those might vary.
+} {
+    if { $instance_id eq "" } {
+        # set instance_id package_id
+        set instance_id [ad_conn package_id]
+    }
+    if { $user_id eq "" } {
+        set user_id [ad_conn user_id]
+        set untrusted_user_id [ad_conn untrusted_user_id]
+    }
+    # check permissions
+    set read_p [permission::permission_p -party_id $user_id -object_id $instance_id -privilege read]
+
+    if { $read_p } {
+        set return_list_of_lists [db_list_of_lists simple_table_stats { select name, title, comments, cell_count, row_count, template_id, flags, trashed, popularity, last_modified, created, user_id from qss_simple_table where id = :table_id and instance_id = :instance_id } ] 
+        # convert return_lists_of_lists to return_list
+        set return_list [lindex $return_list_of_lists 0]
+        
+    } else {
+        set return_list [list ]
+    }
+    return $return_list
+}
+
+ad_proc -public qss_tables { 
+    {instance_id ""}
+    {user_id ""}
+    {template_id ""}
+} {
+    Returns a list of table_ids available. If table_id is included, the results are scoped to tables with same template. If user_id is included, the results are scoped to the user.
+} {
+    if { $instance_id eq "" } {
+        # set instance_id package_id
+        set instance_id [ad_conn package_id]
+    }
+    if { $user_id eq "" } {
+        set party_id [ad_conn user_id]
+        set untrusted_user_id [ad_conn untrusted_user_id]
+    } else {
+        set party_id $user_id
+    }
+    set read_p [permission::permission_p -party_id $party_id -object_id $instance_id -privilege read]
+
+    if { $read_p } {
+        if { $template_id eq "" } {
+            if { $user_id ne "" } {
+                set return_list [db_list simple_tables_user_list { select id from qss_simple_table where instance_id = :instance_id and user_id = :user_id } ]
+            } else {
+                set return_list [db_list simple_tables_list { select id from qss_simple_table where instance_id = :instance_id } ]
+            }
+        } else {
+            set has_template [db_0or1row simple_table_template "select template_id as db_template_id from qss_simple_table where template_id= :template_id"]
+            if { $has_template && [info exists db_template_id] && $template_id > 0 } {
+                if { $user_id ne "" } {
+                    set return_list [db_list simple_tables_t_u_list { select id from qss_simple_table where instance_id = :instance_id and user_id = :user_id and template_id = :template_id } ]
+                } else {
+                    set return_list [db_list simple_tables_list { select id from qss_simple_table where instance_id = :instance_id and template_id = :template_id } ]
+                }
+            } else {
+                set return_list [list ]
+            }
+        }
+    } else {
+        set return_list [list ]
+    }
+    return $return_list
+} 
+
+ad_proc -public qss_table_read { 
+    table_id
+    {instance_id ""}
+    {user_id ""}
+    
+} {
+    Reads table with id. Returns table as list_of_lists of cells.
+} {
+    if { $instance_id eq "" } {
+        # set instance_id package_id
+        set instance_id [ad_conn package_id]
+    }
+    if { $user_id eq "" } {
+        set user_id [ad_conn user_id]
+        set untrusted_user_id [ad_conn untrusted_user_id]
+    }
+    set read_p [permission::permission_p -party_id $user_id -object_id $instance_id -privilege read]
+    set cells_list_of_lists [list ]
+    if { $read_p } {
+
+        set cells_data_lists [db_list_of_lists qss_simple_cells_table_read { select cell_rc, cell_value from qss_simple_cells
+            where table_id =:table_id order by cell_rc } ]
+
+        set prev_row "0001"
+        set row_list [list ]
+        foreach cell_list $cells_data_lists {
+            set cell_rc [lindex $cell_list 0]
+            set cell_value [lindex $cell_list 1]
+
+            # following based on "0000" format used in create/write cell_rc r0001c0001
+            set row [string range $cell_rc 1 4]
+            set column [string range $cell_rc 6 9]
+#            ns_log Notice "qss_table_read: cell ${cell_rc} ($row,$column) value ${cell_value}"     
+            # build row list
+            if { $row eq $prev_row } {
+                # add cell to same row
+                set column_next [expr { [llength $row_list ] + 1 } ]
+                set cols_to_add [expr { $column - $column_next } ]
+                # add blank cells, if needed
+                for {set i 1} {$i < $cols_to_add} {incr i } {
+                    lappend row_list ""
+                }
+                lappend row_list $cell_value
+            } else {
+                # row finished, add row_list to cells_list_of_lists
+                lappend cells_list_of_lists $row_list
+                # start new row
+                set row_list [list $cell_value]
+            }
+            set prev_row $row
+        }
+        lappend cells_list_of_lists $row_list
+    }
+    return $cells_list_of_lists
+}
+
+ad_proc -public qss_table_write {
+    cells_list_of_lists
+    name
+    title
+    comments
+    table_id
+    {template_id ""}
+    {flags ""}
+    {instance_id ""}
+    {user_id ""}
+} {
+    Writes a simple table.
+} {
+    if { $instance_id eq "" } {
+        # set instance_id package_id
+        set instance_id [ad_conn package_id]
+    }
+    if { $user_id eq "" } {
+        set user_id [ad_conn user_id]
+        set untrusted_user_id [ad_conn untrusted_user_id]
+    }
+    set write_p [permission::permission_p -party_id $user_id -object_id $instance_id -privilege write]
+    if { $write_p } {
+        set table_exists_p [db_0or1row simple_table_get_id {select user_id as creator_id from qss_simple_table where id = :table_id } ]
+        if { $table_exists_p } {
+        
+            db_transaction {
+                db_dml simple_table_update { update qss_simple_table
+                    set name =:name,title =:title,comments=:comments 
+                    where id = :table_id and instance_id=:instance_id and user_id=:user_id }
+                
+                # get list of cell_rc referencs in this table. We need to track updates, and delete any remaining ones.
+                set cells_list [db_list simple_table_cells_list {select cell_rc from qss_simple_cells where table_id =:table_id } ]
+                set cells 0
+                set row 0
+                foreach row_list $cells_list_of_lists {
+                    incr row
+                    set column 0
+                    foreach cell_value $row_list {
+                        incr cells
+                        incr column
+                        set cell_rc "r[string range "0000" 0 [expr { 3 - [string length $row] } ] ]${row}c[string range "0000" 0 [expr { 3 - [string length $column] } ] ]${column}"
+                        set cell_idx [lsearch -exact $cells_list $cell_rc]
+                        set cell_length [string length $cell_value]
+#          ns_log Notice "qss_table_write: row $row column $column cell_rc $cell_rc cell_idx $cell_idx cell_length $cell_length"
+                        # if cell_value has length of zero, then don't update. It will get deleted if it already exists..
+                        if { $cell_idx > -1 && $cell_length > 0 } {
+                            db_dml qss_simple_cells_update { update qss_simple_cells 
+                                set cell_value=:cell_value 
+                                where table_id =:table_id and cell_rc =:cell_rc }
+                            
+                            # remove cell from cell_list, so that we can delete remaining old cells
+                            set cells_list [lreplace $cells_list $cell_idx $cell_idx]
+                            
+                        } elseif { $cell_length > 0 } {
+                            db_dml qss_simple_cells_create { insert into qss_simple_cells
+                                (table_id,cell_rc,cell_value)
+                                values (:table_id,:cell_rc,:cell_value)
+                            }
+                        }
+                    }
+                }
+                db_dml simple_table_update_rc { update qss_simple_table
+                    set row_count =:row,cell_count =:cells
+                    where id = :table_id }
+
+                # delete remaining cells in cells_list from qss_simple_cells
+                foreach cell_rc $cells_list {
+                    db_dml qss_simple_cells_delete { delete from qss_simple_cells
+                        where table_id =:table_id and cell_rc=:cell_rc }
+                }
+                
+            } on_error {
+                set success 0
+                ns_log Error "qss_table_write: general db error during db_dml"
+            }
+        } else {
+            set success 0
+            ns_log Warning "qss_table_write: no table exists for table_id $table_id"
+        }
+
+        set success 1
+    } else {
+        set success 0
+    }
+    return $success
+}
+
+
+ad_proc -public qss_table_delete {
+    {table_id ""}
+    {instance_id ""}
+    {user_id ""}
+} {
+    Table_id can be a list of table_id's. Deletes table_id (subject to permission check).
+    Returns 1 if deleted. Returns 0 if there were any issues.
+} {
+    if { $instance_id eq "" } {
+        # set instance_id package_id
+        set instance_id [ad_conn package_id]
+    }
+    if { $user_id eq "" } {
+        set user_id [ad_conn user_id]
+        set untrusted_user_id [ad_conn untrusted_user_id]
+    }
+    set delete_p [permission::permission_p -party_id $user_id -object_id $instance_id -privilege delete]
+    set success $delete_p
+    if { $delete_p } {
+        db_transaction {
+            db_dml simple_table_delete { delete from qss_simple_table 
+                where id=:table_id and instance_id =:instance_id and user_id=:user_id }
+            
+            db_dml qss_simple_cells_delete_table { delete from qss_simple_cells
+                where table_id =:table_id }
+            set success 1
+        } on_error {
+            set success 0
+            ns_log Error "qss_table_delete: general db error during db_dml"
+        }
+    }
+        
+    return $success
+}
+
+ad_proc -public qss_table_trash {
+    {trash_p "1"}
+    {table_id ""}
+    {instance_id ""}
+    {user_id ""}
+} {
+    Table_id can be a list of table_id's. Trashes/untrashes table_id (subject to permission check).
+    set trash_p to 1 (default) to trash table. Set trash_p to '0' to untrash. 
+    Returns 1 if successful, otherwise returns 0
+} {
+    if { $instance_id eq "" } {
+        # set instance_id package_id
+        set instance_id [ad_conn package_id]
+    }
+    if { $user_id eq "" } {
+        set user_id [ad_conn user_id]
+        set untrusted_user_id [ad_conn untrusted_user_id]
+    }
+    set delete_p [permission::permission_p -party_id $user_id -object_id $instance_id -privilege delete]
+    if { $delete_p } {
+        if { $trash_p } {
+            db_dml simple_table_trash_tog { update qss_simple_table set trashed = '1'
+                where id=:table_id and instance_id =:instance_id and user_id=:user_id }
+        } else {
+            db_dml simple_table_trash_tog { update qss_simple_table set trashed = '0'
+                where id=:table_id and instance_id =:instance_id and user_id=:user_id }
+        }
+    }
+    return $delete_p
+}
+