#!/bin/bash

# Generates information for use by the sort scanners

# 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

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

# date to generate files for.  Also handle complex descriptions.  Default to today, but allow ranges.  Sometimes packages that had issues of some form the day before
# do not show up on the current day's route sheets, so a range will increase the chances of finding them, at the expense of a proportionally larger output file.
deldatestring="today"
datestringopt=0
deldate=""
dateopt=0




showhelp=0
while getopts ":o:q:d:ht:l:" opt
  do
    case $opt in
      h)
        showhelp=1
        ;;
      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
        ;;
      q)
        quiet="$OPTARG"
        if (( quiet < 2 ))
          then
            echo "Setting verbosity to $quiet."
          fi
        ;;
      t)
        deldate="$OPTARG"
        dateopt=1
        if (( quiet < 2 ))
          then
            echo "Including all packages since $deldate."
          fi
        ;;
      l)
        deldatestring="$OPTARG"
        datestringopt=1
        if (( quiet < 2 ))
          then
            echo "Including all packages since $deldatestring."
          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 scanner data generator version 0.1.  Copyright 2017 Randy Gardner."
    echo "Usage: $(basename "$0") [-q #] [-o destdir] [-d database] -h [-t deldate] [-l datedescription]"
    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 "  -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




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


outfile="$destdir/sortscanner.csv"

# This one is easy!
query="select tracking, route, case when sig=\"Bus/Sig Reqd\" then \"Business\" else \"Residential\" end from pkg where deldate>=\"$deldate\" group by tracking;"
sqlite3 -separator ', ' "$database" "$query" >"$outfile"

if (( quiet < 3 ))
  then
    echo "Generated sort scanner file for $(wc -l "$outfile" | cut -d ' ' -f 1) tracking numbers."
  fi


# Note: Sometimes tracking numbers appear more than one on a route sheet.  This appears to be ontrac brokenness, like every other random headache.
# Grouping by the tracking number prevents duplicate output lines, but the route given will be randomly picked if it's listed on more than one route.