nix, shell, perl, php, mysql and mac os x tips and tricks

Wednesday, September 23, 2015

MySQL query to compare two COUNT results to get a percentage

SELECT
 keyed.keyed_codes AS "Entered Codes",
 totals.total_table AS "Total Codes",
 ROUND(((keyed.keyed_codes/totals.total_table)*100),2) AS "Percent Keyed"
FROM
(SELECT count(*) AS keyed_source FROM `table` WHERE code RLIKE "^[1-9]" AND timestamp BETWEEN '20150801000000' AND '20150831000000') AS keyed,
(SELECT count(*) AS total_table FROM `table` WHERE timestamp BETWEEN '20150801000000' AND '20150831000000') AS totals

Saturday, January 17, 2015

Stop iTunes v12 from Expiring Podcasts

If you're a heavy podcast listener you've probably seen this iTunes message:

 "iTunes has stopped updating this podcast because you have not listened to any episodes recently. Would you like to resume updating this podcast?"

 iTunes stops downloading podcasts if you haven't listened to any of the last 5 downloaded episodes. I perhaps because bandwidth gets expensive? But it's annoying because sometimes you're traveling and not able to sync, or you occasionally listen to the podcast on Stitcher or some other podcatcher right on your phone. Doesn't mean you want them to stop downloading.

 This script http://dougscripts.com/itunes/scripts/ss.php?sp=updateexpiredpodcasts used to work, but somewhere in iTunes version 11 they wised up to it and it stopped working. Now they are actually checking the downloaded tracks to see if any are played.

 So, I wrote a little script that gives 'em what they want. It plays the first unplayed episode in each subscribed podcast, and then back-tracks to the beginning of the podcast so that when you go to listen you don't know the difference. In order for a track to be identified as "played" it appears it needs to have been playing for at least 15 seconds. While the script is running it sets the volume to 0. But it will return the volume to what it was previously set at when it's done. So you can run this via launchd or cron once a day, in the middle of the night.

 Yes, it's kludgey. I can't even say for sure that it works, as I just wrote it. I borrowed some of Doug's code, so credits to him.

UPDATE: This actually appears to work.  I've been running it as a cron job late at night for a couple weeks and haven't had the "expired podcast" issue.  Only weirdness I'm seeing is that the iTunes "messages" (little note that pops up to tell you when a new track is playing) sticks on the last track processed.


--check if itunes is running.  if not, activate it
set itunesOK to my itunes_is_running()
if itunesOK is false then
 tell application "iTunes"
  activate
 end tell
end if

tell application "iTunes"
 with timeout of 300 seconds
  
  --see if there's a currently playing track so we can return to it when done
  set playTrack to false
  if player state is playing then
   set aTrack to current track
   stop
   set playTrack to true
   set myMessage to "Running the StopExpired script, will return to your track shortly"
   set myReturn to my growlMessage(myMessage)
  end if
  
  --see what the volume is currently set at
  set curVol to (get sound volume)
  
  --turn the volume all the way down so what we're about to do is not audible
  set sound volume to 0
  
  set podcast_playlist to some playlist whose special kind is Podcasts
  
  -- filter multiple names
  set each_podcast to {}
  try
   set each_podcast to my ASCII_Sort(my get_discrete_list_of(get album of every track of podcast_playlist))
  end try
  
  --display the list (debugging only)
  --set selectedVoice to {choose from list each_podcast}
  
  -- loop thru each podcast
  if each_podcast is not {} then
   repeat with this_podcast in each_podcast
    
    --create a list of unplayed episodes of within each podcast
    set each_unplayed to {}
    try
     set each_unplayed to (every track of podcast_playlist whose album is this_podcast and unplayed is true)
    end try
    
    if each_unplayed is not {} then
     set this_unplayed to (item 1 of each_unplayed)
     
     --for debugging
     set myMessage to this_podcast & ": " & name of this_unplayed as string
     set myReturn to my growlMessage(myMessage)
     
     --download the episode, if it's not already
     
     try
      set url_track to (get URL track of this_unplayed)
      set myMessage to "DOWNLOADING " & name of this_unplayed & " URL: " & URL track as string
      set myReturn to my growlMessage(myMessage)
      download url_track
     end try
     
     
     try
      play this_unplayed
      delay 20
      set player position to 0
      stop this_unplayed
      --back track this_unplayed
      
     end try
     
     --why is this necessary? I don't know.  Sometimes the last track just keeps playing.
     stop
     
    end if
    
   end repeat
  end if
  
  --update all the podcasts
  updateAllPodcasts
  
  --return the sound volume to previous level
  set sound volume to curVol
  
  set myMessage to "StopExpired script complete"
  set myReturn to my growlMessage(myMessage)
  
  --if there was a track already playing, return to it
  if playTrack is true then
   play aTrack
   set playTrack to true
  end if
 end timeout
end tell

--if itunes was originally closed, then close it again
if itunesOK is false then
 tell application "iTunes"
  quit
 end tell
end if

to get_discrete_list_of(list1)
 script a
  property list1ref : list1
 end script
 set list2 to {}
 script b
  property list2ref : list2
 end script
 repeat with i from 1 to length of list1
  set this_item to item i of a's list1ref
  considering case
   if this_item is not "" and this_item is not in b's list2ref then set end of list2 to this_item
  end considering
 end repeat
 return b's list2ref
end get_discrete_list_of

on ASCII_Sort(my_list)
 set the index_list to {}
 set the sorted_list to {}
 repeat (the number of items in my_list) times
  set the low_item to ""
  repeat with i from 1 to (number of items in my_list)
   if i is not in the index_list then
    set this_item to item i of my_list as text
    if the low_item is "" then
     set the low_item to this_item
     set the low_item_index to i
    else if this_item comes before the low_item then
     set the low_item to this_item
     set the low_item_index to i
    end if
   end if
  end repeat
  set the end of sorted_list to the low_item
  set the end of the index_list to the low_item_index
 end repeat
 return the sorted_list
end ASCII_Sort

--subroutine showing messages in growl (preferably)
--and if no growl, default dialog with timeout
to growlMessage(myMessage)
 --show our output message
 -- Check if Growl is running:
 set isRunning to my growl_is_running()
 
 --Only display growl notifications if Growl is running:
 if isRunning = true then
  
  tell application "GrowlHelperApp"
   -- Make a list of all notification types:
   set the allNotificationsList to ¬
    {"Notification 1", "Notification 2"}
   
   -- Make a list of the default enabled notifications:
   set the enabledNotificationsList to ¬
    {"Notification 1"}
   
   -- Register the script with Growl
   -- using either "icon of application"
   -- or "icon of file":
   register as application ¬
    "StopExpired" all notifications allNotificationsList ¬
    default notifications enabledNotificationsList ¬
    icon of application "Script Editor"
   
   -- Send a notification:
   notify with name "Notification 1" title "StopExpired output" description myMessage application name "StopExpired"
  end tell
 else
  display dialog myMessage giving up after 1
 end if
end growlMessage

--sub checks if growl is running
on growl_is_running()
 tell application "System Events" to return (exists process "GrowlHelperApp")
end growl_is_running

--subroutine checks if itunes is running
on itunes_is_running()
 tell application "System Events" to return (exists process "iTunes")
end itunes_is_running