#!/bin/sh  
# \
exec oagwish "$0" "$@"


set auto_path [linsert $auto_path 0  /usr/local/oag/apps/lib/$env(HOST_ARCH)]
set auto_path [linsert $auto_path 0 /usr/local/oag/lib_patch/$env(HOST_ARCH)]
set apsttk 1
package require tdbc::mysql

APSApplication . -name "PS Order Form Category Editor"

if {$env(HOST) == "igbt.aps.anl.gov"} {
    set host localhost
} else {
    set host "igbt.aps.anl.gov"
}

proc BuildGUI {} {
    global status env
    set status Ready...
    APSScrolledStatus .status -parent .userFrame -textVariable status -packOption "-fill both -expand true" -width 50 -height 10

    ttk::frame .userFrame.cat
    APSButton .showCategories -parent .userFrame.cat -text "Edit Categories" -command ShowCategories -width ""
    APSButton .saveCategories -parent .userFrame.cat -text "Upload Category Changes" -command SaveCategories -width ""
    pack .userFrame.cat -anchor nw
    APSDisableButton .userFrame.cat.saveCategories.button

    ttk::frame .userFrame.users
    APSButton .showUsers -parent .userFrame.users -text "Edit Users" -command ShowUsers -width ""
    APSButton .saveUsers -parent .userFrame.users -text "Upload User Changes" -command SaveUsers -width ""
    pack .userFrame.users -anchor nw
    APSDisableButton .userFrame.users.saveUsers.button

    set validUser [lsearch -exact "juw tfors soliday" $env(USER)]
    if {$validUser == -1} {
        set status "Only Ju Wang, Thomas Fors and Robert Soliday are\nallowed to use this program"
        APSDisableButton .userFrame.cat.showCategories.button
        APSDisableButton .userFrame.users.showUsers.button
    }
}

proc UnhidePW {} {
    set num "56 67 97 101 45 86 78 83 47 75 35 47 108 42 58"
    foreach n $num {
        append output [format %c $n]
    }
    return $output
}


proc ShowCategories {} {
    global status host
    APSSetVarAndUpdate status "Listing categories"
    if {[catch {tdbc::mysql::connection create psorders -host $host -user writeapp -passwd [UnhidePW] -db orders} mysqlhandle]} {
        APSSetVarAndUpdate status "Unable to connect to mysql database: $mysqlhandle"
        return
    }
    if {[catch {$mysqlhandle prepare {select sortOrder,`name`,costCode,`group` from orderCategory order by sortOrder}} statement]} {
        APSSetVarAndUpdate status "Unable to read from mysql database: $statement"
        return
    }
    $statement foreach row {
        lappend sortOrder [lindex $row 1]
        lappend name [lindex $row 3]
        lappend costCode [lindex $row 5]
        lappend group [lindex $row 7]
    }
    $statement close
    $mysqlhandle close
    
    set data(ColumnNames) "sortOrder name costCode group"
    set data(ColumnInfo.sortOrder) "type SDDS_LONG"
    set data(Column.sortOrder) [list $sortOrder]
    set data(Column.name) [list $name]
    set data(Column.costCode) [list $costCode]
    set data(Column.group) [list $group]

    global datafile
    set datafile /tmp/[APSTmpString]
    APSAddToTempFileList $datafile $datafile.orig
    sdds save $datafile data
    file copy $datafile $datafile.orig

    exec sddsedit $datafile &
    APSSetVarAndUpdate status "Done listing categories."
    APSSetVarAndUpdate status "Make the required changes in the sddsedit window\nand then click File->Save. Then click the\n\"Upload Category Changes\" button."
    APSEnableButton .userFrame.cat.saveCategories.button
}

proc SaveCategories {} {
    global status datafile host
    APSSetVarAndUpdate status "Saving categories"
    sdds load $datafile data
    sdds load $datafile.orig orig
    
    set sortOrder [lindex $data(Column.sortOrder) 0]
    set name [lindex $data(Column.name) 0]
    set costCode [lindex $data(Column.costCode) 0]
    set group [lindex $data(Column.group) 0]

    set sortOrderOrig [lindex $orig(Column.sortOrder) 0]
    set nameOrig [lindex $orig(Column.name) 0]
    set costCodeOrig [lindex $orig(Column.costCode) 0]
    set groupOrig [lindex $orig(Column.group) 0]

    if {[catch {tdbc::mysql::connection create psorders -host $host -user writeapp -passwd [UnhidePW] -db orders} mysqlhandle]} {
        APSSetVarAndUpdate status "Unable to connect to mysql database: $mysqlhandle"
        return
    }

    #find old rows
    foreach s $sortOrderOrig {
        if {[lsearch -exact $sortOrder $s] == -1} {
            APSSetVarAndUpdate status "Deleting category sortOrder=$s"
            if {[catch {$mysqlhandle prepare {delete from orderCategory where sortOrder=$s}} statement]} {
                APSSetVarAndUpdate status "Unable to write to mysql database: $statement"
                return
            }
            $statement execute
            $statement close
        }
    }

    foreach s $sortOrder n $name c $costCode g $group {
        #find new rows
        set i [lsearch -exact $sortOrderOrig $s]
        if {$i == -1} {
            APSSetVarAndUpdate status "Inserting category sortOrder=$s"
            if {[catch {$mysqlhandle prepare "insert into orderCategory (sortOrder,`name`,costCode,`group`) VALUES ($s, \"$n\", \"$c\", \"$g\")"} statement]} {
                APSSetVarAndUpdate status "Unable to write to mysql database: $statement"
                return
            }
            $statement execute
            $statement close
        } else {
            #Check if name changed
            if {[string compare $n [lindex $nameOrig $i]] != 0} {
                APSSetVarAndUpdate status "Updating category sortOrder=$s name=$n"
                if {[catch {$mysqlhandle prepare "update orderCategory set name=\"$n\" where sortOrder=$s"} statement]} {
                    APSSetVarAndUpdate status "Unable to write to mysql database: $statement"
                    return
                }
                $statement execute
                $statement close
            }
            #Check if costCode changed
            if {[string compare $c [lindex $costCodeOrig $i]] != 0} {
                APSSetVarAndUpdate status "Updating category sortOrder=$s costCode=$c"
                if {[catch {$mysqlhandle prepare "update orderCategory set costCode=\"$c\" where sortOrder=$s"} statement]} {
                    APSSetVarAndUpdate status "Unable to write to mysql database: $statement"
                    return
                }
                $statement execute
                $statement close
            }
            #Check if group changed
            if {[string compare $g [lindex $groupOrig $i]] != 0} {
                APSSetVarAndUpdate status "Updating group sortOrder=$s group=$g"
                if {[catch {$mysqlhandle prepare "update orderCategory set `group`=\"$g\" where sortOrder=$s"} statement]} {
                    APSSetVarAndUpdate status "Unable to write to mysql database: $statement"
                    return
                }
                $statement execute
                $statement close
            }
        }
    }
    $mysqlhandle close
    file copy -force $datafile $datafile.orig

    APSSetVarAndUpdate status "Done saving categories"
}

proc ShowUsers {} {
    global status host
    APSSetVarAndUpdate status "Listing users"
    if {[catch {tdbc::mysql::connection create psorders -host $host -user writeapp -passwd [UnhidePW] -db orders} mysqlhandle]} {
        APSSetVarAndUpdate status "Unable to connect to mysql database: $mysqlhandle"
        return
    }
    if {[catch {$mysqlhandle prepare {select badge,`name`,email from orderUsers order by email}} statement]} {
        APSSetVarAndUpdate status "Unable to read from mysql database: $statement"
        return
    }
    $statement foreach row {
        lappend badge [lindex $row 1]
        lappend name [lindex $row 3]
        lappend email [lindex $row 5]
    }
    $statement close
    $mysqlhandle close
    
    set data(ColumnNames) "badge name email"
    set data(ColumnInfo.badge) "type SDDS_LONG"
    set data(Column.badge) [list $badge]
    set data(Column.name) [list $name]
    set data(Column.email) [list $email]

    global datafileUser
    set datafileUser /tmp/[APSTmpString]
    APSAddToTempFileList $datafileUser $datafileUser.orig
    sdds save $datafileUser data
    file copy $datafileUser $datafileUser.orig

    exec sddsedit $datafileUser &
    APSSetVarAndUpdate status "Done listing users."
    APSSetVarAndUpdate status "Make the required changes in the sddsedit window\nand then click File->Save. Then click the\n\"Upload User Changes\" button."
    APSEnableButton .userFrame.users.saveUsers.button
}

proc SaveUsers {} {
    global status datafileUser host
    APSSetVarAndUpdate status "Saving users"
    sdds load $datafileUser data
    sdds load $datafileUser.orig orig
    
    set badge [lindex $data(Column.badge) 0]
    set name [lindex $data(Column.name) 0]
    set email [lindex $data(Column.email) 0]

    set badgeOrig [lindex $orig(Column.badge) 0]
    set nameOrig [lindex $orig(Column.name) 0]
    set emailOrig [lindex $orig(Column.email) 0]

    if {[catch {tdbc::mysql::connection create psorders -host $host -user writeapp -passwd [UnhidePW] -db orders} mysqlhandle]} {
        APSSetVarAndUpdate status "Unable to connect to mysql database: $mysqlhandle"
        return
    }

    #find old rows
    foreach b $badgeOrig {
        if {[lsearch -exact $badge $b] == -1} {
            APSSetVarAndUpdate status "Deleting user badge=$b"
            if {[catch {$mysqlhandle prepare {delete from orderUsers where badge=$b}} statement]} {
                APSSetVarAndUpdate status "Unable to write to mysql database: $statement"
                return
            }
            $statement execute
            $statement close

            if {[catch {$mysqlhandle prepare {delete from orderUsersSearch where badge=$b}} statement]} {
                APSSetVarAndUpdate status "Unable to write to mysql database: $statement"
                return
            }
            $statement execute
            $statement close

        }
    }

    foreach b $badge n $name e $email {
        #find new rows
        set i [lsearch -exact $badgeOrig $b]
        if {$i == -1} {
            APSSetVarAndUpdate status "Inserting category badge=$b"
            if {[catch {$mysqlhandle prepare "insert into orderUsers (badge,`name`,email) VALUES ($b, \"$n\", \"$e\")"} statement]} {
                APSSetVarAndUpdate status "Unable to write to mysql database: $statement"
                return
            }
            $statement execute
            $statement close

            set n [string tolower $n]
            set len [string length $n]
            for {set j 0} {$j < $len} {incr j} {
                lappend nn [string trim [string range $n 0 $j]]
            }
            lappend nn [string range [lindex $n 0] 0 0][string range [lindex $n end] 0 0]
            set n [lindex $n end]
            set len [string length $n]
            for {set j 0} {$j < $len} {incr j} {
                lappend nn [string trim [string range $n 0 $j]]
            }
            set nn [lsort -unique $nn]
            foreach n $nn {
                if {[catch {$mysqlhandle prepare "insert into orderUsersSearch (`badge`,`keys`) VALUES ($b, \"$n\")"} statement]} {
                    APSSetVarAndUpdate status "Unable to write to mysql database: $statement"
                    return
                }
                $statement execute
                $statement close
            }

        } else {
            #Check if name changed
            if {[string compare $n [lindex $nameOrig $i]] != 0} {
                APSSetVarAndUpdate status "Updating category badge=$b name=$n"
                if {[catch {$mysqlhandle prepare "update orderUsers set name=\"$n\" where badge=$b"} statement]} {
                    APSSetVarAndUpdate status "Unable to write to mysql database: $statement"
                    return
                }
                $statement execute
                $statement close
                
                if {[catch {$mysqlhandle prepare {delete from orderUsersSearch where badge=$b}} statement]} {
                    APSSetVarAndUpdate status "Unable to write to mysql database: $statement"
                    return
                }
                $statement execute
                $statement close

                set n [string tolower $n]
                set len [string length $n]
                for {set j 0} {$j < $len} {incr j} {
                    lappend nn [string trim [string range $n 0 $j]]
                }
                lappend nn [string range [lindex $n 0] 0 0][string range [lindex $n end] 0 0]
                set n [lindex $n end]
                set len [string length $n]
                for {set j 0} {$j < $len} {incr j} {
                    lappend nn [string trim [string range $n 0 $j]]
                }
                set nn [lsort -unique $nn]
                foreach n $nn {
                    if {[catch {$mysqlhandle prepare "insert into orderUsersSearch (`badge`,`keys`) VALUES ($b, \"$n\")"} statement]} {
                        APSSetVarAndUpdate status "Unable to write to mysql database: $statement"
                        return
                    }
                    $statement execute
                    $statement close
                }
            }
            #Check if email changed
            if {[string compare $e [lindex $emailOrig $i]] != 0} {
                APSSetVarAndUpdate status "Updating category badge=$b email=$e"
                if {[catch {$mysqlhandle prepare "update orderUsers set email=\"$e\" where badge=$b"} statement]} {
                    APSSetVarAndUpdate status "Unable to write to mysql database: $statement"
                    return
                }
                $statement execute
                $statement close
            }
        }
    }
    $mysqlhandle close
    file copy -force $datafileUser $datafileUser.orig

    APSSetVarAndUpdate status "Done saving categories"
}

BuildGUI
