| 1 | #!/bin/sh
|
|---|
| 2 |
|
|---|
| 3 | # nangu.tv report and cdr
|
|---|
| 4 |
|
|---|
| 5 | rsync="/usr/local/bin/rsync"
|
|---|
| 6 |
|
|---|
| 7 | # report servers
|
|---|
| 8 | report_servers="s05.olo01.iptv.riomedia.cz"
|
|---|
| 9 |
|
|---|
| 10 | # report list
|
|---|
| 11 | report_list="billing-offers billing-tariffs billing-tariffs-offers-prices billing-tariffs-setup-prices \
|
|---|
| 12 | provisioning-subscribers provisioning-subscribers-subscriptions provisioning-subscriptions-stb-accounts"
|
|---|
| 13 |
|
|---|
| 14 | # cdr servers
|
|---|
| 15 | cdr_servers="s01.olo01.iptv.riomedia.cz s02.olo01.iptv.riomedia.cz"
|
|---|
| 16 |
|
|---|
| 17 | # cdr list
|
|---|
| 18 | cdr_list="cdr-offer cdr-tvod"
|
|---|
| 19 |
|
|---|
| 20 | sql=`mktemp /tmp/fetch-cdr-reports.XXXXX`
|
|---|
| 21 |
|
|---|
| 22 | echo "begin;" > ${sql};
|
|---|
| 23 |
|
|---|
| 24 | # check for purge
|
|---|
| 25 | purge=0
|
|---|
| 26 |
|
|---|
| 27 | if [ "x$1" = "xpurge" ]; then
|
|---|
| 28 | purge=1
|
|---|
| 29 | fi
|
|---|
| 30 |
|
|---|
| 31 | # sync reports
|
|---|
| 32 | for server in $report_servers; do
|
|---|
| 33 |
|
|---|
| 34 | local=$HOME/data/reports
|
|---|
| 35 | mkdir -p ${local}
|
|---|
| 36 | ${rsync} -a portax@${server}:/mnt/data/reports/global ${local}
|
|---|
| 37 |
|
|---|
| 38 | for report in $report_list; do
|
|---|
| 39 |
|
|---|
| 40 | echo "process report ${report}"
|
|---|
| 41 |
|
|---|
| 42 | for file in `find ${local} -name "${report}_*.csv"`; do
|
|---|
| 43 |
|
|---|
| 44 | if [ -f ${file}.out ]; then
|
|---|
| 45 | echo "skip ${file}"
|
|---|
| 46 | continue
|
|---|
| 47 | fi
|
|---|
| 48 |
|
|---|
| 49 | echo ${file}
|
|---|
| 50 | touch ${file}.out
|
|---|
| 51 |
|
|---|
| 52 | echo "truncate table \"${report}\";" >> ${sql}
|
|---|
| 53 | echo " -- ${file}" >> ${sql}
|
|---|
| 54 | echo "\copy \"${report}\" from stdin with null as 'None' delimiter as ',' csv quote '\"'" >> ${sql}
|
|---|
| 55 | sed -e 's/\r\n/\r/g' -e 1d < ${file} >> ${sql}
|
|---|
| 56 | echo "\." >> ${sql}
|
|---|
| 57 |
|
|---|
| 58 | done
|
|---|
| 59 |
|
|---|
| 60 | done
|
|---|
| 61 |
|
|---|
| 62 | done
|
|---|
| 63 |
|
|---|
| 64 | # sync cdr data
|
|---|
| 65 | for cdr in $cdr_list; do
|
|---|
| 66 |
|
|---|
| 67 | echo "process cdr ${cdr}"
|
|---|
| 68 |
|
|---|
| 69 | local=$HOME/data/cdr
|
|---|
| 70 |
|
|---|
| 71 | if [ $purge = 1 ]; then
|
|---|
| 72 | echo "truncate table \"${cdr}\";" >> ${sql}
|
|---|
| 73 | rm -r ${local}
|
|---|
| 74 | purge=0
|
|---|
| 75 | fi
|
|---|
| 76 |
|
|---|
| 77 | for server in $cdr_servers; do
|
|---|
| 78 |
|
|---|
| 79 | mkdir -p ${local}
|
|---|
| 80 | ${rsync} -a portax@${server}:/mnt/data/srv/tve/cdr-export ${local}
|
|---|
| 81 |
|
|---|
| 82 | for file in `find ${local} -name "${cdr}*.csv"`; do
|
|---|
| 83 |
|
|---|
| 84 | if [ -f ${file}.out ]; then
|
|---|
| 85 | echo "skip ${file}"
|
|---|
| 86 | continue
|
|---|
| 87 | fi
|
|---|
| 88 |
|
|---|
| 89 | path=${file#${local}/cdr-export/}
|
|---|
| 90 | isp_id=${path%/*}
|
|---|
| 91 |
|
|---|
| 92 | echo ${file}
|
|---|
| 93 | touch ${file}.out
|
|---|
| 94 |
|
|---|
| 95 | echo " -- ${file}" >> ${sql}
|
|---|
| 96 | echo "\copy \"${cdr}\" from stdin with null as '' delimiter as ',' csv quote as '\"'" >> ${sql}
|
|---|
| 97 | sed -e "s/^/${isp_id},/g" -e 1d < ${file} >> ${sql}
|
|---|
| 98 | echo "\." >> ${sql}
|
|---|
| 99 |
|
|---|
| 100 | done
|
|---|
| 101 |
|
|---|
| 102 | done
|
|---|
| 103 |
|
|---|
| 104 | done
|
|---|
| 105 |
|
|---|
| 106 | echo "truncate table \"provisioning-subscribers-map\";" >> ${sql}
|
|---|
| 107 | echo "\\copy \"provisioning-subscribers-map\" from stdin with delimiter as ','" >> ${sql}
|
|---|
| 108 | (
|
|---|
| 109 | echo "\copy (select distinct '400-' || s.pn as subscriber_code,'400-' || s.pn || '-' || s.id::varchar as subscription_code,p.pn,p.profile_id from t_stb s join t_profile p on p.mac=s.relay_mac) to stdout with delimiter as ','"
|
|---|
| 110 | ) | /usr/local/bin/psql -h 172.25.7.12 -Uportax >> ${sql}
|
|---|
| 111 | echo "\\." >> ${sql}
|
|---|
| 112 |
|
|---|
| 113 | echo "commit;" >> ${sql}
|
|---|
| 114 |
|
|---|
| 115 | /usr/local/bin/psql -h 172.25.7.12 -Uvod < ${sql} && rm ${sql}
|
|---|
| 116 |
|
|---|
