#
# record for multiuser spreadsheet
# $Id: math.txt,v 1.1 2005/06/03 19:13:09 costello Exp $
#
RECORD sc DBTABLE sc
#
# at init load mysql lib and set handle. note that path to .so may
# be different, in debian it seems to be 
# /usr/lib/mysqltcl-2.30/libmysqltcl2.30.so
# in unstable is used today. 
#
# we also declare here common methods, at least "sc_eval" that is used
# to evaluate whole spreadsheet. 
#
SCRIPT_BEGIN AT_INIT
 load /usr/lib/mysqltcl/libmysqltcl.so
 set handle [mysqlconnect -user $DBUSERNAME -password $DBPASSWORD -host $DBSERVERNAME ]
 mysqluse $handle $DBNAME
 proc sc_eval {} {
  mysqlsel $::handle " select f,pos from formula where topic = '$::TOPIC' order by pos "
  mysqlmap $::handle { a_formula a_position } {
   set a_pos [ format "::%s" $a_position ]
   if { [ string range $a_formula 0 0 ] == "\"" } {
     set $a_pos [ string range $a_formula 1 10 ]
   } else {
     set $a_pos [ expr $a_formula ]
   }
  } 
 }
proc ch_incr { letter } {
 if { $letter == "A" } {
  return "B"
 }
 if { $letter == "B" } {
  return "C"
 }
 if { $letter == "C" } {
  return "D"
 }
 if { $letter == "D" } {
  return "E"
 }
 if { $letter == "E" } {
  return "F"
 }
 if { $letter == "F" } {
  return "G"
 }
}
proc sc_sum { b e } {
 set bl [ string range $b 0 0 ]
 set bn [ string range $b 1 2 ]
 set el [ string range $e 0 0 ]
 set en [ string range $e 1 2 ]
 set the_sum 0 
 for { set l $bl } { $l <= $el } { set l [ ch_incr $l ] } {
  for { set n $bn } { $n <= $en } { incr n } {
   set pos [ format "\$::%s%d" $l $n ]
   eval set da_val $pos 
   if { [ scan $da_val "%f" one_value ] == 1 } {
    set the_sum [ expr $the_sum + $one_value ]
   } 
  }
 }
 return $the_sum
}
proc sc_avg { b e } {
 set bl [ string range $b 0 0 ]
 set bn [ string range $b 1 2 ]
 set el [ string range $e 0 0 ]
 set en [ string range $e 1 2 ]
 set the_sum 0 
 set valid_values 0 
 for { set l $bl } { $l <= $el } { set l [ ch_incr $l ] } {
  for { set n $bn } { $n <= $en } { incr n } {
   set pos [ format "\$::%s%d" $l $n ]
   eval set da_val $pos 
   if { [ scan $da_val "%f" one_value ] == 1 } {
    set the_sum [ expr $the_sum + $one_value ]
    set valid_values [ expr $valid_values + 1 ]
   } 
  }
 }
 if { $valid_values == 0 } {
  return 0 
 } else  {
  return [ expr $the_sum / $valid_values ]
 }
}
SCRIPT_END
#
# intelligenze follows, first code for getting value from list:
#
SCRIPT_BEGIN FUNCTION_KEY 4 "Get"
set F {}
 if { [ string len $TOPIC ] > 0 } {
  if { [ string len $POS ] > 0 } {
   mysqlsel $handle " select f from formula where pos = '$POS' and topic = '$TOPIC' "
   mysqlmap $handle { a_formula } {
    set F $a_formula
   } 
 }
}
SCRIPT_END
#
# then code for setting value or expression. sc_eval is called twice because
# it just may be that values from A1, B2, C3 etc. are referenced in 
# formulas
#
SCRIPT_BEGIN BEFORESAVE
if { [ string len $TOPIC ] > 0 } {
 if { [ string len $POS ] > 0 } {
  mysqlexec $handle " delete from formula where pos = '$POS' and topic = '$TOPIC' "
  if { [ string len $F ] > 0 } {
   regsub -all ' $F '' F_with_double_single_quotes
   mysqlexec $handle " insert into formula (pos,f,topic) values ( '$POS','$F_with_double_single_quotes','$TOPIC') "
  }
 sc_eval 
 sc_eval 
 }
}
SCRIPT_END
#
# this code is called after record has been fetched from database. 
# note that this is what happens when several users are viewing the
# same spreadsheet and one saves: the others will get their sheets
# loaded and this script thusly run.
#
SCRIPT_BEGIN AFTERRETRIEVE
if { [ string len $TOPIC ] > 0 } {
  sc_eval
  sc_eval
 }
SCRIPT_END
#
# function key 5 is used to save record. see also code above:
#
FUNCTION_KEY 5 SAVERECORD "Set"
#
# function key 7 is used to change topic, see field topic
#
FUNCTION_KEY 7 SELECTANOTHERRECORD "Find"
#
# function key 8 is used to save record for first time.
#
FUNCTION_KEY 8 SAVERECORDASNEW "NewTopic"
#
# then labels and fields
#
RECFIELD 0 0 15 TOPIC STRING "         A         B          C          D          E     Topic:"
RECFIELD 1 2 10 A1 STRING " 1"
RECFIELD 2 2 10 A2 STRING " 2"
RECFIELD 3 2 10 A3 STRING " 3"
RECFIELD 4 2 10 A4 STRING " 4"
RECFIELD 5 2 10 A5 STRING " 5"
RECFIELD 6 2 10 A6 STRING " 6"
RECFIELD 7 2 10 A7 STRING " 7"
RECFIELD 8 2 10 A8 STRING " 8"
RECFIELD 9 2 10 A9 STRING " 9"
RECFIELD 10 2 10 A10 STRING "10"
RECFIELD 11 2 10 A11 STRING "11"
RECFIELD 12 2 10 A12 STRING "12"
RECFIELD 13 2 10 A13 STRING "13"
RECFIELD 14 2 10 A14 STRING "14"
RECFIELD 15 2 10 A15 STRING "15"
RECFIELD 16 2 10 A16 STRING "16"
RECFIELD 17 2 10 A17 STRING "17"
RECFIELD 18 2 10 A18 STRING "18"
RECFIELD 19 2 10 A19 STRING "19"
RECFIELD 20 2 10 A20 STRING "20"
# column B
RECFIELD 1 14 10 B1 STRING " "
RECFIELD 2 14 10 B2 STRING " "
RECFIELD 3 14 10 B3 STRING " "
RECFIELD 4 14 10 B4 STRING " "
RECFIELD 5 14 10 B5 STRING " "
RECFIELD 6 14 10 B6 STRING " "
RECFIELD 7 14 10 B7 STRING " "
RECFIELD 8 14 10 B8 STRING " "
RECFIELD 9 14 10 B9 STRING " "
RECFIELD 10 14 10 B10 STRING " "
RECFIELD 11 14 10 B11 STRING " "
RECFIELD 12 14 10 B12 STRING " "
RECFIELD 13 14 10 B13 STRING " "
RECFIELD 14 14 10 B14 STRING " "
RECFIELD 15 14 10 B15 STRING " "
RECFIELD 16 14 10 B16 STRING " "
RECFIELD 17 14 10 B17 STRING " "
RECFIELD 18 14 10 B18 STRING " "
RECFIELD 19 14 10 B19 STRING " "
RECFIELD 20 14 10 B20 STRING " "
# column C
RECFIELD 1 25 10 C1 STRING " "
RECFIELD 2 25 10 C2 STRING " "
RECFIELD 3 25 10 C3 STRING " "
RECFIELD 4 25 10 C4 STRING " "
RECFIELD 5 25 10 C5 STRING " "
RECFIELD 6 25 10 C6 STRING " "
RECFIELD 7 25 10 C7 STRING " "
RECFIELD 8 25 10 C8 STRING " "
RECFIELD 9 25 10 C9 STRING " "
RECFIELD 10 25 10 C10 STRING " "
RECFIELD 11 25 10 C11 STRING " "
RECFIELD 12 25 10 C12 STRING " "
RECFIELD 13 25 10 C13 STRING " "
RECFIELD 14 25 10 C14 STRING " "
RECFIELD 15 25 10 C15 STRING " "
RECFIELD 16 25 10 C16 STRING " "
RECFIELD 17 25 10 C17 STRING " "
RECFIELD 18 25 10 C18 STRING " "
RECFIELD 19 25 10 C19 STRING " "
RECFIELD 20 25 10 C20 STRING " "
# column D
RECFIELD 1 36 10 D1 STRING " "
RECFIELD 2 36 10 D2 STRING " "
RECFIELD 3 36 10 D3 STRING " "
RECFIELD 4 36 10 D4 STRING " "
RECFIELD 5 36 10 D5 STRING " "
RECFIELD 6 36 10 D6 STRING " "
RECFIELD 7 36 10 D7 STRING " "
RECFIELD 8 36 10 D8 STRING " "
RECFIELD 9 36 10 D9 STRING " "
RECFIELD 10 36 10 D10 STRING " "
RECFIELD 11 36 10 D11 STRING " "
RECFIELD 12 36 10 D12 STRING " "
RECFIELD 13 36 10 D13 STRING " "
RECFIELD 14 36 10 D14 STRING " "
RECFIELD 15 36 10 D15 STRING " "
RECFIELD 16 36 10 D16 STRING " "
RECFIELD 17 36 10 D17 STRING " "
RECFIELD 18 36 10 D18 STRING " "
RECFIELD 19 36 10 D19 STRING " "
RECFIELD 20 36 10 D20 STRING " "
# column E
RECFIELD 1 47 10 E1 STRING " "
RECFIELD 2 47 10 E2 STRING " "
RECFIELD 3 47 10 E3 STRING " "
RECFIELD 4 47 10 E4 STRING " "
RECFIELD 5 47 10 E5 STRING " "
RECFIELD 6 47 10 E6 STRING " "
RECFIELD 7 47 10 E7 STRING " "
RECFIELD 8 47 10 E8 STRING " "
RECFIELD 9 47 10 E9 STRING " "
RECFIELD 10 47 10 E10 STRING " "
RECFIELD 11 47 10 E11 STRING " "
RECFIELD 12 47 10 E12 STRING " "
RECFIELD 13 47 10 E13 STRING " "
RECFIELD 14 47 10 E14 STRING " "
RECFIELD 15 47 10 E15 STRING " "
RECFIELD 16 47 10 E16 STRING " "
RECFIELD 17 47 10 E17 STRING " "
RECFIELD 18 47 10 E18 STRING " "
RECFIELD 19 47 10 E19 STRING " "
RECFIELD 20 47 10 E20 STRING " "
# column F
RECFIELD 1 58 10 F1 STRING " "
RECFIELD 2 58 10 F2 STRING " "
RECFIELD 3 58 10 F3 STRING " "
RECFIELD 4 58 10 F4 STRING " "
RECFIELD 5 58 10 F5 STRING " "
RECFIELD 6 58 10 F6 STRING " "
RECFIELD 7 58 10 F7 STRING " "
RECFIELD 8 58 10 F8 STRING " "
RECFIELD 9 58 10 F9 STRING " "
RECFIELD 10 58 10 F10 STRING " "
RECFIELD 11 58 10 F11 STRING " "
RECFIELD 12 58 10 F12 STRING " "
RECFIELD 13 58 10 F13 STRING " "
RECFIELD 14 58 10 F14 STRING " "
RECFIELD 15 58 10 F15 STRING " "
RECFIELD 16 58 10 F16 STRING " "
RECFIELD 17 58 10 F17 STRING " "
RECFIELD 18 58 10 F18 STRING " "
RECFIELD 19 58 10 F19 STRING " "
RECFIELD 20 58 10 F20 STRING " "
# column G
RECFIELD 1 69 10 G1 STRING " "
RECFIELD 2 69 10 G2 STRING " "
RECFIELD 3 69 10 G3 STRING " "
RECFIELD 4 69 10 G4 STRING " "
RECFIELD 5 69 10 G5 STRING " "
RECFIELD 6 69 10 G6 STRING " "
RECFIELD 7 69 10 G7 STRING " "
RECFIELD 8 69 10 G8 STRING " "
RECFIELD 9 69 10 G9 STRING " "
RECFIELD 10 69 10 G10 STRING " "
RECFIELD 11 69 10 G11 STRING " "
RECFIELD 12 69 10 G12 STRING " "
RECFIELD 13 69 10 G13 STRING " "
RECFIELD 14 69 10 G14 STRING " "
RECFIELD 15 69 10 G15 STRING " "
RECFIELD 16 69 10 G16 STRING " "
RECFIELD 17 69 10 G17 STRING " "
RECFIELD 18 69 10 G18 STRING " "
RECFIELD 19 69 10 G19 STRING " "
RECFIELD 20 69 10 G20 STRING " "
RECFIELD 22 0 3 POS STRING " " REGEXP "[A-F][0-9]+"
RECFIELD 22 5 60 F STRING "="