#!/bin/bash
MY_DIR=`dirname $0`
DB='db'
DB_USER='user'
DB_HOST='127.0.0.1'
DB_PORT='3308'
DB_PASS='pass'
LOGPATH='/www/custlogs.cloud.net'
DATE=$(date -d "-1 day" +"%Y%m%d") # select logs for yesterday
LIST=$(ls $LOGPATH/domain.de*$DATE*.log.zip)
for i in $LIST; do
    echo "UNZIP LOG" $i
    cd $LOGPATH
    unzip -qq -o $i
    echo "LOAD DATA INTO TABLE"
    log=${i%.zip}
    mysql --local-infile -u$DB_USER -p$DB_PASS $DB -h$DB_HOST -P$DB_PORT -e "
        LOAD DATA LOCAL INFILE '${log}'
        INTO TABLE ad_picture_stats
        FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '\"'
        (@hostname, @logname, @username, @received, @timezone, @path, @result, @year, @month, @day, @hour)
        SET year    = SUBSTR(@received, -13, 4),
            month   = MONTH(STR_TO_DATE(SUBSTR(@received, -20, 11), '%d/%b/%Y')),
            day     = SUBSTR(@received, -20, 2),
            hour    = SUBSTR(@received, -8, 2),
            path    = TRIM(TRAILING '?' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(@path, ' ', 2), ' ', -1))"
    echo "REMOVE UNZIPED LOG"
    rm $log
done