Weekly Table Report Script

report_sample_weekly.sh

#!/bin/bash
filename='feature_weekly_report_4hourly'_From-$(date +%Y_%m_%d_%H_%M -d "0 day 4 hours ago")_To-$(date +%Y_%m_%d_%H_%M).csv
db_name=darts_game
host_ip=8.8.8.8

WEEK_RANGE_ARRAY=(
    [0]="20191115,20191121"
    [1]="20191122,20191128"
    [2]="20191129,20191205"
    [3]="20191206,20191212"
    [4]="20191213,20191219"
    [5]="20191220,20191226"
    [6]="20191227,20200102"
    [7]="20200103,20200109"
    [8]="20200110,20200116"
    [9]="20200117,20200123"
    [10]="20200124,20200130"
    [11]="20200131,20200206"
    [12]="20200207,20200213"
    [13]="20200214,20200220"
    [14]="20200221,20200227"
    [15]="20200228,20200305"
    [16]="20200306,20200312"
    [17]="20200313,20200319"
    [18]="20200320,20200326"
)

for i in "${WEEK_RANGE_ARRAY[@]}"; do # access each element of array
    IFS=','
    read -ra ADDR <<< "$i"

    d=`date +%Y%m%d`
    # Testing Block
    #d=`date --date="+8 day" +%Y%m%d`

    if [ $d -ge ${ADDR[0]} -a $d -le ${ADDR[1]} ]
    then
        TABLE_NAME=db_name".table_name_"${ADDR[0]}"_"${ADDR[1]}
        IFS=''
        break
    fi
done

#echo $TABLE_NAME

echo "User Id, MSISDN, Prize Id, Prize Name, Date/Time" > $filename

export PASSWORD='YourPassword'
QUERY="SELECT weekly.user_id,weekly.msisdn,0,'weekly',count(*) as total FROM "$TABLE_NAME" as weekly group by weekly.msisdn, weekly.user_id"
#echo $QUERY

/usr/bin/mysql -uroot -h$host_ip --password=$PASSWORD -s -N -e $QUERY | tr '\t' ',' >> $filename