#!/bin/bash

# Generates plain text files listing the addresses on a route, for either today's date or the specified date.

# 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
deldate="$(date '+%F')"
dateopt=0

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

# Where to unpack and modify the template file
xlsxdir="/tmp/rwxlsx"

# Road Warrior's template
rwtemplate="../Rw.Location.Upload.xlsx"

# what to generate
gentext=0
gentexta=0
genxlsx=0
genxlsxa=0

showhelp=0
while getopts ":o:q:d:ht:px" opt
  do
    case $opt in
      h)
        showhelp=1
        ;;
      p)
        gentext=1
        if (( quiet < 2 ))
          then
            echo "Generating plain text address lists."
          fi
        ;;
      x)
        genxlsx=1
        genxlsxa=1
        if (( quiet < 2 ))
          then
            echo "Generating RoadWarrior xlsx address lists."
          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 files for $deldate."
          fi
        ;;
      q)
        quiet="$OPTARG"
        if (( quiet < 2 ))
          then
            echo "Setting verbosity to $quiet."
          fi
        ;;
      \?)
        echo "Dunno what -$OPTARG is..."
        showhelp=1
        ;;
      :)
        echo "Option -$OPTARG requires an argument."
        showhelp=1
        ;;
    esac
  done
shift $((OPTIND-1))

if (( showhelp > 0 ))
  then
    echo "OnTrac address sheet generator version 0.1.  Copyright 2017 Randy Gardner."
    echo "Usage: $(basename "$0") [-q #] [-o destdir] [-d database] [-h] [-t deldate] [-x] [-p]"
    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 date to generate files for, in YYYY-MM-DD format."
    echo "  -x generate RoadWarrior XLSX files."
    echo "  -p generate plain text address lists."
    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 ))
  then
    if (( quiet < 2 ))
      then
        echo "Using today's date '$deldate'."
      fi
  fi
if (( gentext == 0 )) && (( genxlsx == 0 ))
  then
    if (( quiet < 2 ))
      then
        echo "Generating both text and xlsx by default."
      fi
    gentext=1
    gentexta=1
    genxlsx=1
    genxlsxa=1
  fi


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



# loop through for each file type to generate.  Less efficient, but easier than keeping track of two xlsx files at once.
types=""
if (( gentext > 0 ))
  then
    types="$types text"
  fi
if (( gentexta > 0 ))
  then
    types="$types texta"
  fi
if (( genxlsx > 0 ))
  then
    types="$types xlsx"
  fi
if (( genxlsxa > 0 ))
  then
    types="$types xlsxa"
  fi

for gentype in $types
  do
    #echo "generating file type: $gentype"


# read routes for specified date from database
routecount=0
stopcount=0
while IFS='' read -r route || [[ -n "$route" ]]
  do
    if (( quiet < 2 ))
      then
        echo "Generating address sheet for $route."
      fi

    if [[ "$gentype" == "text" ]]
      then
        textfile="$destdir/Addresses $route.txt"
        echo -n >"$textfile"
      fi

    if [[ "$gentype" == "texta" ]]
      then
        textfile="$destdir/Summary $route.txt"
        echo -n >"$textfile"
      fi

    if [[ "$gentype" == "xlsx" ]] || [[ "$gentype" == "xlsxa" ]]
      then
        # Generate xlsx files by a really ugly hack - stuffing text into them with no idea what an xlsx file looks like, in an incredibly fragile fashion.
        if [[ "$gentype" == "xlsxa" ]]
          then
            xlsxfile="$destdir/RoadWarrior Detailed $route.xlsx"
          else
            xlsxfile="$destdir/RoadWarrior $route.xlsx"
          fi
        # unpack the template file.  This code has no idea how to generate an xlsx from scratch, only how to modify roadwarrior's template.
        mkdir "$xlsxdir"
        unzip -q -o -d "$xlsxdir" "$rwtemplate"

        # shared strings file
        strings="$xlsxdir/xl/sharedStrings.xml"
        # get info about the shared strings file
        strcount="$(cat "$strings" | grep -i count | sed 's/.*count="\([0-9]\+\)".*/\1/')"
        struniquecount="$(cat "$strings" | grep -i count | sed 's/.*uniqueCount="\([0-9]\+\)".*/\1/')"
        if (( quiet < 0 ))
          then
            echo "Shared strings file starts with $strcount strings, of which $struniquecount are unique."
          fi
        mv "$strings" "$strings.old"
        # Keep the top part of the strings file
        cat "$strings.old" | tr -d '\r\n' | sed 's|</sst>.*||' >"$strings"

        # worksheet file
        sheet="$xlsxdir/xl/worksheets/sheet1.xml"
        mv "$sheet" "$sheet.old"
        # Keep the header portion of the sheet file
        cat "$sheet.old" | tr -d '\r\n' | sed 's|<row[^>]*r="2".*||' >"$sheet"

        # xlsx row number
        xrow=2
        # current "unique" (I don't bother actually uniquing them...) string
        s=$struniquecount
        # At this point, assume all deliveries are in the same country, and re-use the same string for them.
        countrystr=$s ; (( s++ ))
        echo "<si><t>US</t></si>" >>"$strings"
      fi

    # Get stops for route.  I <3 SQL!
    query="select addr.mapaddr, addr.house, addr.street, addr.city, addr.state, addr.zip,"
    query="$query sum( (select count(*) from pkg where pkg.route=stop.route and pkg.stopnum=stop.stopnum and pkg.deldate=stop.deldate"
    query="$query and pkg.sig=\"Bus/Sig Reqd\") ) as type, count(*),"
    query="$query sum( (select count(*) from pkg where pkg.route=stop.route and pkg.stopnum=stop.stopnum and pkg.deldate=stop.deldate) ) as pkgs,"
    query="$query sum( (select sum(weight) from pkg where pkg.route=stop.route and pkg.stopnum=stop.stopnum and pkg.deldate=stop.deldate) ) as weight"
    query="$query from addr,stop where addr.rawaddr=stop.addr and stop.route=\"$route\" and stop.deldate=\"$deldate\""
    query="$query group by addr.mapaddr order by addr.street, cast(addr.house as integer);"
    #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"
        mapaddr="${columns[0]}"
        house="${columns[1]}"
        street="${columns[2]}"
        city="${columns[3]}"
        state="${columns[4]}"
        zip="${columns[5]}"
        type="${columns[6]}"
        stops="${columns[7]}"
        pkgs="${columns[8]}"
        weight="${columns[9]}"
        if (( quiet < 1 ))
          then
            echo "Address '$mapaddr' - '$house' '$street', '$city' '$state' '$zip'.  '$type' with '$stops' stops and '$pkgs' pkgs, '$weight'lbs."
          fi

        if (( type > 0 ))
          then
            type="Business"
          else
            type="Residential"
          fi

        note="$type"
        if (( stops > 1 ))
          then
            note="$note, $stops stops"
          fi
        if (( pkgs > 1 ))
          then
            note="$note, $pkgs pieces"
          fi
        note="$note, ${weight}lbs"

        if [[ "$gentype" == "text" ]]
          then
            # The easy one!
            echo "$mapaddr" >>"$textfile"
          fi

        if [[ "$gentype" == "texta" ]]
          then
            echo "$mapaddr, $note" >>"$textfile"
          fi

        if [[ "$gentype" == "xlsx" ]] || [[ "$gentype" == "xlsxa" ]]
          then
            # And the ugly one.
            # Add a row to the sheet file.  Note this contains no actual data, just pointers to the string table, with the index incremented each time.
            # RoadWarrior's xlsx parser does not handle inline strings, so you have to use shared strings...
            echo "<row r=\"$xrow\" spans=\"1:12\" x14ac:dyDescent=\"0.45\">" >>"$sheet"
            echo "  <c r=\"A$xrow\" s=\"7\" t=\"s\"><v>$s</v></c>" >>"$sheet" ; (( s++ ))
            echo "  <c r=\"B$xrow\" s=\"7\" t=\"s\"><v>$s</v></c>" >>"$sheet" ; (( s++ ))
            echo "  <c r=\"C$xrow\" s=\"7\" t=\"s\"><v>$s</v></c>" >>"$sheet" ; (( s++ ))
            echo "  <c r=\"D$xrow\" s=\"7\"/>" >>"$sheet"
            echo "  <c r=\"E$xrow\" s=\"7\" t=\"s\"><v>$s</v></c>" >>"$sheet" ; (( s++ ))
            echo "  <c r=\"F$xrow\" s=\"7\" t=\"s\"><v>$countrystr</v></c>" >>"$sheet"
            #echo "  <c r=\"G$xrow\" s=\"7\"/>" >>"$sheet"
            echo "  <c r=\"G$xrow\" s=\"7\" t=\"s\"><v>$s</v></c>" >>"$sheet" ; (( s++ ))
            echo "  <c r=\"H$xrow\" s=\"7\"/>" >>"$sheet"
            #echo "  <c r=\"I$xrow\" s=\"7\"/>" >>"$sheet"
            echo "  <c r=\"I$xrow\" s=\"7\" t=\"s\"><v>$s</v></c>" >>"$sheet" ; (( s++ ))
            echo "  <c r=\"J$xrow\" s=\"7\"/>" >>"$sheet"
            echo "  <c r=\"K$xrow\" s=\"7\"/>" >>"$sheet"
            #echo "  <c r=\"L$xrow\" s=\"7\"/>" >>"$sheet"
            echo "  <c r=\"L$xrow\" s=\"7\" t=\"s\"><v>$s</v></c>" >>"$sheet" ; (( s++ ))
            echo "</row>" >>"$sheet"
            (( xrow++ ))

            if [[ "$type" =~ ^B ]]
              then
                color="0.2"
                (( time = stops * 6 ))
              else
                color="0.8"
                (( time = stops * 3 ))
              fi

            # Now put the actual data into the strings file.
            # Use <street> <housenum> for the stop name, so RW's sort puts them in the same order as the printed route sheets
            echo "<si><t>$street $house</t></si>" >>"$strings"
            echo "<si><t>$house $street</t></si>" >>"$strings"
            echo "<si><t>$city</t></si>" >>"$strings"
            echo "<si><t>$zip</t></si>" >>"$strings"
            echo "<si><t>$color</t></si>" >>"$strings"
            # the note field adds another line to the list screen, making it harder to use.
            if [[ "$gentype" == "xlsxa" ]]
              then
                echo "<si><t>$note</t></si>" >>"$strings"
              else
                echo "<si><t></t></si>" >>"$strings"
              fi
            echo "<si><t>$time</t></si>" >>"$strings"
            # State is not currently useful, so don't output it.

            (( stopcount++ ))
          fi
      done < <( sqlite3 "$database" "$query" )
#      done < <( sqlite3 "$database" "select addr.mapaddr,addr.house,addr.street,addr.city,addr.state,addr.zip,case when (select count(*) from pkg where pkg.route=stop.route and pkg.stopnum=stop.stopnum and pkg.deldate=stop.deldate and pkg.sig=\"Bus/Sig Reqd\") > 0 then \"Business\" else \"Residential\" end as type,count(*) from addr,stop where addr.rawaddr=stop.addr and stop.route=\"$route\" and stop.deldate=\"$deldate\" group by addr.mapaddr order by addr.street, cast(addr.house as integer);" )

    if [[ "$gentype" == "xlsx" ]] || [[ "$gentype" == "xlsxa" ]]
      then
        # Add the bottom portion of the original sheet file to the new one
        cat "$sheet.old" | tr -d '\r\n' | sed 's|.*\(</[Ss]heet[Dd]ata>\)|\1|' >>"$sheet"

        # Update the string file counts
        (( newstrs = s - struniquecount ))
        (( struniquecount += newstrs ))
        (( strcount += newstrs ))
        if (( quiet < 0 ))
          then
            echo "Shared strings file has $newstrs new strings, and now has $strcount strings, of which $struniquecount are (supposedly) unique."
          fi

        # And add the bottom of the original strings file to the new one
        cat "$strings.old" | tr -d '\r\n' | sed 's|.*\(</sst>\)|\1|' >>"$strings"
        # Update the strings file counts
        sed -i "s/count=\"[0-9]\+\"/count=\"$strcount\"/;s/uniqueCount=\"[0-9]\+\"/uniqueCount=\"$struniquecount\"/" "$strings"

        fullxlsxfile="$(pwd)/$xlsxfile"
        if (( quiet < 1 ))
          then
            echo "Zipping everything into $fullxlsxfile."
          fi
        # Is there a way to make zip compress something somewhere else without storing the full pathname?  cding is just ugly.  So is rming the zip first.
        opwd="$(pwd)"
        cd "$xlsxdir"
        if [[ -e "$fullxlsxfile" ]]
          then
            rm "$fullxlsxfile"
          fi
        zip -q -r "$fullxlsxfile" *
        if (( quiet < 1 ))
          then
            ls -l "$fullxlsxfile"
          fi
        cd "$opwd"
        rm -r "$xlsxdir"
      fi

    (( routecount++ ))
  done < <( sqlite3 "$database" "select distinct route from stop where deldate=\"$deldate\";" )

# done for the type loop
done


if (( quiet < 3 ))
  then
    echo "Generated sheets for $routecount routes with a total of $stopcount stops."
  fi




#example row
#<row r="2" spans="1:12" x14ac:dyDescent="0.45">
#<c r="A2" s="7" t="s"><v>30</v></c>   name
#<c r="B2" s="7" t="s"><v>32</v></c>   street
#<c r="C2" s="7" t="s"><v>31</v></c>   city
#<c r="D2" s="7"/>   state
#<c r="E2" s="7"><v>191023</v></c>   zip
#<c r="F6" s="7" t="s"><v>24</v></c>   country
#<c r="G6" s="10"><v>0.8</v></c>   color
#<c r="H6" s="9"/>   phone
#<c r="I6" s="9"/>   note
#<c r="J6" s="22"/>   lat
#<c r="K6" s="22"/>   long
#<c r="L6" s="10"><v>50</v></c>   time
#</row>