update from mysql

(*
*)

property themonths : {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}
property themonths_index : {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"}

property thedays : {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"}
property thedays_index : {"1", "2", "3", "4", "5", "6", "7"}

property themonthsfr : {"janvier", "fevrier", "mars", "avril", "mai", "juin", "juillet", "août", "septembre", "octobre", "novembre", "decembre"}
property thedaysfr : {"lundi", "mardi", "mercredi", "jeudi", "vendredi", "samedi", "dimanche"}

set badlyrics to {"NULL", "missing value"}
set audiobookgenres to {"Audiobook", "Languages"}

tell application "iTunes"
    get selection of front browser window
    if (selection of front browser window) is not {} then
        set sel to selection
        repeat with thisTrack in sel
            set updateOk to "yes"

            if comment of thisTrack is "" then set updateOk to "no"
            if (disc number of thisTrack as string) is "0" then set updateOk to "no"
            if (disc count of thisTrack as string) is "0" then set updateOk to "no"
            if (track number of thisTrack as string) is "0" then set updateOk to "no"
            if (track count of thisTrack as string) is "0" then set updateOk to "no"
            set newRating to 0
            if updateOk is equal to "yes" then
                try
                    set sqlResults to my findTrack(track count of thisTrack, album of thisTrack, track number of thisTrack, ¬
                        comment of thisTrack, disc number of thisTrack, disc count of thisTrack)
                    if sqlResults is not equal to "" then
                        set newName to item 1 of sqlResults
                        set newPlayedCount to item 2 of sqlResults
                        set newPlayedDate to item 3 of sqlResults
                        set newYear to item 4 of sqlResults
                        set newRating to item 5 of sqlResults
                        set newArtist to item 6 of sqlResults
                        set newComment to item 7 of sqlResults
                        set newGrouping to item 8 of sqlResults
                        set newComposer to item 9 of sqlResults
                        try
                            set discnumber to item 10 of sqlResults
                        end try
                        try
                            set disccount to item 11 of sqlResults
                        end try
                        try
                            set newbpm to item 12 of sqlResults
                        end try

                        try
                            set trackstart to item 13 of sqlResults
                        end try
                        try
                            set trackfinish to item 14 of sqlResults
                        end try
                        try
                            set dateadded to item 15 of sqlResults
                        end try
                        try
                            set tracklyrics to item 16 of sqlResults
                        end try

                        if genre of thisTrack is in audiobookgenres then
                            if trackstart is not in badlyrics then
                                set thisTrack's start to trackstart
                            end if
                            if (trackfinish is not in badlyrics) and (thisTrack's duration is greater than trackfinish) then
                                set thisTrack's finish to trackfinish
                            end if
                        end if


                        considering case
                            if newName is not equal to name of thisTrack then
                                set name of thisTrack to newName
                            end if
                            if newArtist is not equal to artist of thisTrack then
                                set artist of thisTrack to newArtist
                            end if
                            if (newComposer is not "") and (newComposer is not equal to composer of thisTrack) then
                                if (newComposer is not "NULL") then
                                    set composer of thisTrack to newComposer
                                end if
                            end if
                        end considering
                        if (newYear is not "") and (newYear is not equal to year of thisTrack) then
                            set year of thisTrack to newYear
                        end if


                        -- *********** Lyrics
                        set ChR to ASCII character 13
                        set newlyrics to my replace_chars(tracklyrics, "%return%", ChR)
                        try
                            if (thisTrack's lyrics is not equal to newlyrics) then
                                set thisTrack's lyrics to newlyrics
                            end if
                            if thisTrack's lyrics is in badlyrics then set thisTrack's lyrics to ""
                        end try

                        --                      if (newbpm is not "") and (newbpm is not equal to bpm of thisTrack) then
                        --                          try
                        --                              set bpm of thisTrack to newbpm
                        --                          end try
                        --                      end if
                        try
                            if newGrouping is not equal to "NULL" and grouping of thisTrack is not equal to newGrouping then
                                set grouping of thisTrack to newGrouping
                            end if
                        end try

                        --                      set comment of thisTrack to newComment

                        --                      if thisTrack's grouping is in badlyrics then set thisTrack's grouping to ""

                        try
                            if (newPlayedCount as integer) is greater than or equal to (played count of thisTrack) then
                                set played count of thisTrack to newPlayedCount
                                try
                                    set newPlayedDate to my convertdate(newPlayedDate)
                                    set played date of thisTrack to date newPlayedDate
                                end try
                            end if
                        end try
                        try
                            --                          set date added of thisTrack to date dateadded
                            --                          set date added of thisTrack to my convertdate(dateadded)
                        end try

                    end if
                end try
                if newRating as integer is greater than 0 then
                    set rating of thisTrack to newRating
                else
                    --my growl(rating of thisTrack as string)
                    if rating of thisTrack is equal to 0 then
                        set rating of thisTrack to 40
                    end if
                end if

            end if
        end repeat
    end if
    my growl("Updated tracks from MySQL")
end tell

on findTrack(trackCount, trackAlbum, track_number, trackComment, discnumber, disccount)
    set trackAlbum to my escapeString(trackAlbum)
    set trackComment to my escapeString(trackComment)

    if trackComment starts with "http" then
        set oldDelim to AppleScript's text item delimiters
        set AppleScript's text item delimiters to {" "}
        set thestring to text items of trackComment
        set AppleScript's text item delimiters to oldDelim
        set commentBegin to item 1 of thestring
    else
        if trackComment = "" then
            set commentBegin to "unknown"
        else
            set commentBegin to word 1 of trackComment
        end if
    end if

    set theQuery to "WHERE track_number ='" & (track_number as string) & "' && track_count='" & (trackCount as string) & "' "

    if the length of commentBegin is 10 then
        set theQuery to theQuery & "&& comment LIKE '%" & commentBegin & "%' "
        set cdEnd to true
    else
        set theQuery to theQuery & "&& comment LIKE '" & commentBegin & "%' "
        set cdEnd to true
    end if

    -- set newAlbum to getAlbumName(trackAlbum)
    --  set theQuery to theQuery & "&& album like '" & newAlbum & "%' "
    set theQuery to theQuery & "&& discnumber ='" & discnumber & "' "
    set theQuery to theQuery & "&& disccount ='" & disccount & "' "
    -- delimiters should be set back
    set text item delimiters to {"  "}
    return the text items of paragraph 2 of sqlSelect("name, played_count, played_date, year, rating, artist, comment, grouping, composer, discnumber, disccount, bpm, starttime, finishtime, date_added, lyrics", theQuery)
end findTrack

on sqlSelect(fieldsToGet, myString)
    set theQuery to "/usr/local/bin/mysql -u root -D 'itunes'  -e \"SELECT " & fieldsToGet & " FROM itunes " & myString & "\""
    --set the clipboard to myString
    return do shell script theQuery
end sqlSelect

on escapeString(newString)
    set newString to replace_chars(newString, "\"", "\\\"")
    set newString to replace_chars(newString, "'", "\\'")
    return newString
end escapeString


on getAlbumName(theAlbum)
    set AppleScript's text item delimiters to " "
    set the item_list to every text item of theAlbum
    if item -1 of item_list starts with "CD" then
        set newAlbum to items 1 thru -2 of item_list as string
    else
        set newAlbum to the item_list as string
    end if
    set AppleScript's text item delimiters to ""
    return newAlbum
end getAlbumName

on replace_chars(this_text, search_string, replacement_string)
    set AppleScript's text item delimiters to the search_string
    set the item_list to every text item of this_text
    set AppleScript's text item delimiters to the replacement_string
    set this_text to the item_list as string
    set AppleScript's text item delimiters to ""
    return this_text
end replace_chars

on convertdate(oldDate)
    -- french to english so far
    set tid to AppleScript's text item delimiters
    set AppleScript's text item delimiters to " "
    set testEnglish to text item 1 of oldDate as string
    if testEnglish is in thedaysfr then
        set AppleScript's text item delimiters to " "
        set datedayofWeek to text item 1 of oldDate
        set dateTemp to text item 2 of oldDate
        set dateMonth to text item 3 of oldDate
        set dateYear to text item 4 of oldDate
        set dateDay to text item 2 of oldDate


        repeat with x from 1 to 12
            if dateMonth = (item x of themonthsfr) then
                set dateMonth to x as string
                exit repeat
            end if
        end repeat


        repeat with x from 1 to 7
            if datedayofWeek = (item x of thedaysfr) then
                set datedayofWeek to x as integer
                exit repeat
            end if
        end repeat
        set newDate to (item datedayofWeek of thedays as string) & ", "
        set newDate to newDate & (item dateMonth of themonths as string) & ", " & dateDay

        set datedayofWeek to item datedayofWeek of thedaysfr
        set newDate to newDate & ", " & text item 4 of oldDate & " " & text item 5 of oldDate
    else
        set newDate to oldDate

    end if
    set AppleScript's text item delimiters to tid

    return newDate
end convertdate

on growl(myString)
    tell application "Growl"
        notify with name ¬
            "AppleScript" title "iTunes" description myString application name ¬
            "AppleScript" icon of file "file:///Applications/iTunes.app"
    end tell
    return true
end growl