#!/bin/bash

# Generates sort sheets for each city showing how to sort each street and address range

# Copyright 2017 Randy Gardner
# All rights reserved.  Commercial use or distribution prohibited.


# where to save the files
destdir="routes"
diropt=0

# sqlite3 database
database="ontrac"
addtodb=0

# date to generate files for.  Default to the last year.  This is probably a GNU-specific extension.  I'm too lazy to deal with date math!
deldatestring="last year"
datestringopt=0
deldate=""
dateopt=0
datenames=1

# Verbosity.  0 prints everything, 1 only prints addresses, 2 only prints summary, 3 is silent.
quiet=2

# Driver route numbers and names.  We could stick this in the db, but a text file is easier for non-db-types to maintain
driversfile="drivers.txt"


showhelp=0
while getopts ":o:q:d:ht:r:l:n" opt
  do
    case $opt in
      h)
        showhelp=1
        ;;
      r)
        driversfile="$OPTARG"
        if (( quiet < 2 ))
          then
            echo "Using '$driversfile' for driver names."
          fi
        ;;
      d)
        database="$OPTARG"
        addtodb=1
        if (( quiet < 2 ))
          then
            echo "Using database '$database'."
          fi
        ;;
      o)
        destdir="$OPTARG"
        diropt=1
        if (( quiet < 2 ))
          then
            echo "Saving output files to '$destdir'."
          fi
        ;;
      t)
        deldate="$OPTARG"
        dateopt=1
        if (( quiet < 2 ))
          then
            echo "Generating sheets for deliveries since $deldate."
          fi
        ;;
      l)
        deldatestring="$OPTARG"
        datestringopt=1
        if (( quiet < 2 ))
          then
            echo "Generating sheets for deliveries since $deldatestring."
          fi
        ;;
      q)
        quiet="$OPTARG"
        if (( quiet < 2 ))
          then
            echo "Setting verbosity to $quiet."
          fi
        ;;
      n)
        datenames=0
        if (( quiet < 2 ))
          then
            echo "Not putting dates in output filenames."
          fi
        ;;
      \?)
        echo "Dunno what -$OPTARG is..."
        showhelp=1
        ;;
      :)
        echo "Option -$OPTARG requires an argument."
        showhelp=1
        ;;
    esac
  done
shift $((OPTIND-1))


if (( dateopt == 1 )) && (( datestringopt == 1 ))
  then
    echo "Only one of -t and -l may be given."
    echo
    showhelp=1
  fi


if (( showhelp > 0 ))
  then
    echo "OnTrac sort sheet generator version 0.1.  Copyright 2017 Randy Gardner."
    echo "Usage: $(basename "$0") [-q #] [-o destdir] [-d database] [-h] [-t deldate] [-l datedescription] [-r driversfile] [-n]"
    echo "  -q quietness.  Less is more.  Current range is -3 to 3."
    echo "  -o directory to save files to."
    echo "  -d uses the specified sqlite3 database."
    echo "  -t earliest date to use to generate files, in YYYY-MM-DD format."
    echo "  -l earliest date to use to generate files, in any format that 'date' can figure out."
    echo "  -n don't stick the date in the output filenames."
    echo "  -r text file with list of route,driver name pairs."
    echo "  -h You're lookin' at it, pal!"
    exit
  fi


if (( addtodb == 0 ))
  then
    if (( quiet < 2 ))
      then
        echo "Using default database '$database'."
      fi
  fi
if (( diropt == 0 ))
  then
    if (( quiet < 2 ))
      then
        echo "Using default destination '$destdir/'."
      fi
  fi

if (( dateopt == 0 )) && (( datestringopt == 1 ))
  then
    deldate="$(date --date "$deldatestring" '+%F')"
    if (( quiet < 1 ))
      then
        echo "Using starting date '$deldatestring' - '$deldate'."
      fi
  fi
if (( dateopt == 0 )) && (( datestringopt == 0 ))
  then
    deldate="$(date --date "$deldatestring" '+%F')"
    datestringopt=1
    if (( quiet < 2 ))
      then
        echo "Using default starting date '$deldatestring' - '$deldate'."
      fi
  fi



declare -A drivers
while IFS='' read -r line || [[ -n "$line" ]]
  do
    #echo "$line"
    [[ "$line" =~ ([0-9]+),[[:space:]]*(.*) ]]
    drivers[${BASH_REMATCH[1]}]="${BASH_REMATCH[2]}"
  done < <(cat "$driversfile" | sed 's/#.*//' | grep -v '^$' )
#for r in "${!drivers[@]}"; do echo "$r - ${drivers[$r]}"; done



# create destination directory if needed
if ! [[ -d "$destdir" ]]
  then
    mkdir -v "$destdir"
  fi

citycount=0
# First, get a list of cities.  Arbritrarily only select cities with more than 5 stops, mostly to eliminate typoed city names.
while IFS='' read -r city || [[ -n "$city" ]]
  do
    # output files
    if (( datenames > 0 ))
      then
        if (( datestringopt > 0 ))
          then
            ss="$destdir/Sortsheet - $deldatestring - $city.txt"
            ss3="$destdir/3col Sortsheet - $deldatestring - $city.txt"
            ss3a="$destdir/3col89 Sortsheet - $deldatestring - $city.txt"
            ss3aps="$destdir/3col89 Sortsheet - $deldatestring - $city.ps"
            ss3apdf="$destdir/Sortsheet - $deldatestring - $city.pdf"
          else
            ss="$destdir/Sortsheet - since $deldate - $city.txt"
            ss3="$destdir/3col Sortsheet - since $deldate - $city.txt"
            ss3a="$destdir/3col89 Sortsheet - since $deldate - $city.txt"
            ss3aps="$destdir/3col89 Sortsheet - since $deldate - $city.ps"
            ss3apdf="$destdir/Sortsheet - since $deldate - $city.pdf"
          fi
      else
        ss="$destdir/Sortsheet - $city.txt"
        ss3="$destdir/3col Sortsheet - $city.txt"
        ss3a="$destdir/3col89 Sortsheet - $city.txt"
        ss3aps="$destdir/3col89 Sortsheet - $city.ps"
        ss3apdf="$destdir/Sortsheet - $city.pdf"
      fi
    echo -n >"$ss"

    if (( quiet < 2 ))
      then
        echo "Generating sort sheet for $city, '$ss'."
      fi

    # For each street and driver, get the minimum and maximum house numbers.  make sql do the work!
    # To remove some of the random crap, only return drivers who deliver more than a certain percentage of stops on a street
    # This may cause drivers who only deliver a couple stops on a small section of a major road to be ignored.  Set to 0% to turn off.
    # A better query would apply this only to specific address ranges, and other things way too complicated for a temporary solution.
    minpercent=".00"
    query="select street, min(cast(house as integer)) as minhouse, max(cast(house as integer)) as maxhouse, route, count(*) as c,"
    #query="$query (select count(*) from addr as a2, stop as s2 where a2.rawaddr=s2.addr and a2.street=a1.street and a2.city=a1.city) as tc"
    query="$query (select count(*) from addr as a2, stop as s2 where a2.rawaddr=s2.addr and a2.street=a1.street and a2.city=a1.city and s2.deldate>=\"$deldate\") as tc"
    query="$query from addr as a1,stop as s1 where a1.rawaddr=s1.addr and a1.city=\"$city\" and s1.deldate>=\"$deldate\" and a1.house != \"\" and a1.street != \"\""
    query="$query group by street, route having c >= tc * $minpercent"
    query="$query order by case when cast(street as integer) > 0 then cast(street as integer) else street end, minhouse, maxhouse, route;"
    #echo "$query"
    while IFS='' read -r row || [[ -n "$row" ]]
      do
        if (( quiet < 0 ))
          then
            echo "Stop row '$row'."
          fi
        # sqlite3 defaults to '|' as field separator, and I haven't spotted it in any addresses...
        IFS='|' read -a columns <<< "$row"
        street="${columns[0]}"
        minhouse="${columns[1]}"
        maxhouse="${columns[2]}"
        route="${columns[3]}"
        count="${columns[4]}"
        totalcount="${columns[5]}"
        if (( quiet < 1 ))
          then
            echo "$street $minhouse-$maxhouse($count/$totalcount) $route ${drivers[$route]}"
          fi

        (( percent = count * 100 / totalcount ))
        [[ "$route" =~ 285([0-9]+) ]]
        shortroute="${BASH_REMATCH[1]}"
        driver="${drivers[$route]}"
        if [[ "$driver" != "" ]]
          then
            showroute="$shortroute-$driver"
          else
            showroute="$route"
          fi
        echo "$street $minhouse-$maxhouse $percent% $showroute" >>"$ss"

#      done < <( sqlite3 "$database" "select street, min(cast(house as integer)) as minhouse, max(cast(house as integer)) as maxhouse, route, count(*) from addr,stop where addr.rawaddr=stop.addr and city=\"$city\" and deldate>=\"$deldate\" and house != \"\" and street != \"\" group by street, route order by case when cast(street as integer) > 0 then cast(street as integer) else street end, minhouse, maxhouse, route;" )
      done < <( sqlite3 "$database" "$query" )

    pr --columns 3 -T -l 10000 -w 130 -i1000 "$ss" >"$ss3"
    pr --columns 3 -T -l 89 -w 136 -i1000 "$ss" >"$ss3a"
    enscript --silent -B -h -f "Courier@7" "$ss3a" -o "$ss3aps"
    ps2pdf "$ss3aps" "$ss3apdf"

    (( citycount++ ))
  done < <( sqlite3 "$database" "select city from addr,stop where addr.rawaddr=stop.addr and city != \"\" and deldate >= \"$deldate\" group by city having count(distinct mapaddr) > 5;" )


if (( quiet < 3 ))
  then
    echo "Generated sort sheets for $citycount cities since $deldate."
  fi