# Connection procedure for server init, store handle in global var 'db'
proc mysql:connectdb {} {
  global sql db
  set db [logexec "mysqlconnect -host $sql(host) -user $sql(user) -password $sql(pass) -db $sql(db) -port $sql(port) -socket /var/run/mysqld/mysqld.sock" 0 "Connecting to mysql-server"]
  }

# Database ping to prevent timeout
proc mysql:ping {} {global db; mysql::ping $db}

# Date and Time processing
proc mysql:sqldate2timestamp {sqldate} {
  set date [string range $sqldate 5 6]/[string range $sqldate 8 9]/[string range $sqldate 0 3]
  if [catch "clock scan $date" timestamp] {return -1} {return $timestamp}
  }

proc mysql:timestamp2sqldate {timestamp} {
  return [clock format $timestamp -format "%Y-%m-%d"]
  }

proc mysql:date2sqldate {date} {
  if [scan $date "%02d.%02d.%04d" d m y]==3 {return $y-$m-$d} {return "0000-00-00"}
  }

proc mysql:htmltable {module name table fields descr fieldstyle conversion length control} {
  global db
  upvar cgi cgi
  mysql::ping $db
  set htmlout ""

  if [info exists cgi($name)] {set control $cgi($name)}
  set orderby [lindex $fields [expr [string range $control 0 1]]]
  set order [lindex "ASC DESC" [string index $control 2]]
  set page [expr [string range $control 3 5]]
  htmlout {<table><tr><td><table border=0 cellpadding=0 cellspacing=0><tbody>}
  htmlout {<tr class="sqllistheader"><td width=20px></td>}
  for {set x 0} {$x<[llength $descr]} {incr x} {
    set image ""
    set description [lindex $descr $x]
    set controlstring [format %02d [lsearch $descr $description]]
    if [string match $controlstring* $control] {
      set image "<img src=\"/pics/so_[lindex "up down" [string index $control 2]].gif\">"
      set controlstring $controlstring[expr abs([string index $control 2]-1)]
      } {set controlstring ${controlstring}0}
    set controlstring ${controlstring}[format "%03d" $page]
    htmlout "<td><b><a href=\"/$module?list=$name&$name=$controlstring\">$description</a></b> $image</div></td><td width=20px></td>"
    }
  htmlout {</tr>}
  set x 0
  foreach row [lrange [set rows [mysql::sel $db "SELECT [join $fields ,] from $table ORDER BY $orderby $order" -list]] [expr $page*$length] [expr ($page+1)*$length-1]] {
    incr x; set even [lindex "even odd" [expr $x%2]]
    htmlout "<tr class=\"sqllist$even\"><td></td>"
    for {set y 0} {$y<[llength $row]} {incr y} {
      set field [lindex $row $y]
      catch [lindex $conversion $y]
      htmlout "<td style=\"[lindex $fieldstyle $y]\">$field</td><td></td>"
      }
    htmlout "</tr>"
    }
  htmlout "</tbody></table></td></tr><tr><td style=\"text-align: center;\">Seite "
  for {set x 0} {$x<[expr ([llength $rows]/$length)+1]} {incr x} {
    set control [string replace $control 3 5 [format "%03d" $x]]
    htmlout "<b><a href=\"/$module?list=$name&$name=$control\">[expr $x+1]</a></b>"
    }
  htmlout "</td></tr></table>"
  return $htmlout
  }