Статистика по балансу

Пример отчетов

ВНИМАНИЕ! ДОКУМЕНТ В РАЗРАБОТКЕ

Описание

Назначение системы

Задача системы, запрашивать баланс по СИМ-картам операторов сотовой связи и анализировать их ответ. Полученную информацию сохранять в базе данных. На основании данных из базы строить отчеты о движении средств на счетах и организации уведомлений, в случае если баланс окажется ниже порогового значения.

Компоненты системы

Проверено на ubuntu server 10.04 LTS

• Asterisk (сервер голосовой связи)
• chan_dongle (модуль для asterisk обеспечения голосовых звонков через 3G модемы)
• mysql для хранения и обработки данных
• apache или любой другой web сервер (не обязательно, нужен для удобства просмотра отчетов)

Принцип работы

Скрипт назначенный на периодическое выполнение (раз в 30 минут) в cron, осуществляет USSD запросы с добавлением случайной составляющей времени от 0 до 65 секунд с момента запуска длякаждого модема. Это нужно для того, чтобы избежать одновременной отправки со всех устройств.

Asterisk должен быть предварительно настроен на то, чтобы обеспечивать отправку и получение USSD запросов через модемы.

Ответ операторов Asterisk помещает в тектовый файл, а скрипт пытается вытащить информацию о балансе из этих ответов, опираясь на строку разбора (парсинга), которая может быть написана для каждого оператора индивидуально. В текущем скрипте описано для МТС (2 варианта), Билайна и Мегафона для Хабаровского края. Возможно в других регионах придется чтото менять. Прошу свои варианты добавляйте в коментариях.

Каждая строка при внесении в базу контролируется на уникальность по md5 в качестве индекса. Таким образом не играет роли сколько раз запускается скрипт, в базу будут внесены только уникальные новые значения.

Подготовка к работе

Подготовка базы mysql

Создаем новую таблицу gsmbalans в базе asteriskcdrdb. Она обычно уже есть, если установлен FreePBX. Но можно создать и свою.

• Заходим в консоль mysql

mysql -p

• Выполняем запрос на создание новой таблицы gsmbalans в базеasteriskcdrdb

 USE asteriskcdrdb;
 CREATE TABLE 
  gsmbalans (
   md5 VARCHAR(64),
   port VARCHAR(10),
   balans DOUBLE(10,2),
   time DATETIME,
 UNIQUE (md5));

Подготовка плана набора Asterisk

Для успешной работы файл с ответами должен иметь определенный формат.

nano /etc/asterisk/extension_custom.conf

Добавим в диалплан работу с ussd запросами в контекст прописанный в dadacard.conf

[from-gsm]
exten => ussd,1,Verbose(Incoming USSD: ${BASE64_DECODE(${USSD_BASE64})})
exten => ussd,n,System(echo '${STRFTIME(${EPOCH},,%d.%m.%Y %H:%M:%S)} - USSD - ${DONGLENAME}: ${BASE64_DECODE(${USSD_BASE64})}' >> /var/www/msg)
exten => ussd,n,Hangup()

Пример файла с ответами операторов на запрос баланса

28.04.2012 04:30:21 - USSD - 000104: 216.4 p.
28.04.2012 04:30:29 - USSD - 000105: Баланс 393.08 р. iPhone в офисах Билайн от 16 590 р. Инф 068006
28.04.2012 04:30:34 - USSD - 000106: Balans:14,20r

Обработка баланса

Скрипт обработки баланса

nano /etc/balans

Скопируем и вставим

#!/bin/bash

# Имя файла ответов оператора
opermsg=/var/www/msg

# Число считываемых последних строк из файла (при первичном заполнении базы ставим 100500)
tn=1

# Имя базы данных
dbname=test

# Имя таблицы баланса
tbname=gsmbalans

# Имя пользователя базы
dbuser=root

# Пароль пользователя базы
dbpass=123

# Путь до папки с отчетами
repath=/var/www/bal
# Пытаться создать папку автоматически?
# mkdir -p $repath

# Создаем индексную страничку
echo "<b>Баланс модемов</b><br>" >$repath/index.htm
echo "<a href='balans_ob.txt'>Сводный баланс</a><br>" >>$repath/index.htm
echo "<b>Подробно</b><br>" >>$repath/index.htm


# Список модемов и соответствующих им типов ответов операторов о состоянии баланса

for opsos in \
"  000103    MTS2   +79140000001    МТС      *100#" \
"  000101    MTS    +79140000002    МТС      *100#" \
"  000106    MTS    +79140000003    МТС      *100#" \
"  000104    MGF    +79240000004    МЕГАФОН  *100#" \
"  000102    MGF    +79240000005    МЕГАФОН  *100#" \
"  000105    BEE    +79620000006    БИЛАЙН   *102#" \
"  000107    BEE    +79090000007    БИЛАЙН   *102#"

do
num=`echo $opsos | awk '{print $1}'`
oper=`echo $opsos | awk '{print $2}'`
numsim=`echo $opsos | awk '{print $3}'`
fovsim=`echo $opsos | awk '{print $4}'`
ussdzap=`echo $opsos | awk '{print $5}'`

# Раскоменчиваем, если надо делать запросы баланса
let D=$RANDOM/1000 && sleep $D && asterisk -rx "dongle ussd $num $ussdzap" &

# Создаем отчеты по снятию/Пополнению средств с симкарт
echo "Оператор связи $fovsim" > $repath/bal-$num.txt
echo "Федеральный номер СИМ карты $numsim" >> $repath/bal-$num.txt

mysql -u$dbuser -p$dbpass -D $dbname -e "\
 set @a=0.0;select port as 'Модем',time as 'Дата Время',balans as 'Баланс',if(delta>0 and balans<>delta,delta,'-') as 'Приход',if(delta<0,delta,'-') as 'Расход' \
 from(select * from (select port,time,balans,round(balans-pv_balans,2) as delta \
 from(select port,time,@a as pv_balans,@a:=balans,balans \
 from ${tbname} where port='${num}' order by time) as t1) as t2 where delta<>0) as t3;" | column -t >> $repath/bal-$num.txt

# Наполняем индексную страничку
echo "<a href='bal-$num.txt'>$num</a> $numsim $fovsim<br>" >> $repath/index.htm

# Варианты парсинга баланса для каждого оператора
balans=""
# Оператор МТС
  if [ "${oper}" = "MTS" ]
  then cat $opermsg | grep $num| grep USSD| grep Balans | sed -E 's/( - |: Balans:|r )/;/g' | sed 's/,/./g'  | awk -F ";" '{print $1";"$4";"$3}' | tail -n $tn >>tmpallbalans
  fi

# Оператор МТС вариант 2
  if [ "${oper}" = "MTS2" ]
  then cat $opermsg | grep $num| grep USSD| grep Баланс: | sed -E 's/( - |: Баланс:|р | р.)/;/g' | sed 's/,/./g' | awk -F ";" '{print $1";"$4";"$3}'|tail -n $tn  >>tmpallbalans
  fi

# Оператор БИЛАЙН
  if [ "${oper}" = "BEE" ]
  then cat $opermsg | grep $num| grep USSD| grep Баланс| grep р.| sed -E 's/( - |: Баланс | р.)/;/g' | sed 's/,/./g' | awk -F ";" '{print $1";"$4";"$3}' | tail -n $tn >>tmpallbalans
  fi

# Оператор МЕГАФОН
  if [ "${oper}" = "MGF" ]
  then cat $opermsg | grep $num| grep USSD| grep p.| sed -E 's/( - |: | p.)/;/g'| awk -F ";" '{print $1";"$4";"$3}' | tail -n $tn >>tmpallbalans
  fi

done

#Парсим построчно получившийся файл и считаем md5 для каждой строки
cat tmpallbalans| while read line; 

do                     
    mdha=`echo "$line"|openssl md5`
    baldate=`echo "$line" | awk -F ";" '{print $1}' | awk '{print $1"."$2}'|awk -F "." '{print $3"-"$2"-"$1" "$4}'`
    balance=`echo "$line" | awk -F ";" '{print $2}'`
    modnumr=`echo "$line" | awk -F ";" '{print $3}'`

# Заполняем базу значениями
mysql -u$dbuser -p$dbpass -D $dbname -e "INSERT INTO ${tbname} (md5,port,balans,time) values ('$mdha' , '$modnumr' , '$balance' , '$baldate');" >/dev/null

done 

# Создаем сводный отчет по текущим балансам
zapr="select port as 'Модем',balans as 'Баланс',time as 'Дата Время' \
 from (select * from (select max(time) as last_date,port as prt \
 from ${tbname} group by port) as tbl1 \
 JOIN ${tbname} where tbl1.last_date=${tbname}.time and tbl1.prt=${tbname}.port) as itog where port<>'' order by port;"

mysql -u$dbuser -p$dbpass -D $dbname -e "${zapr}" | column -t > $repath/balans_ob.txt

# Очистка временных файлов
echo "">tmpbalans
echo "">tmpallbalans

Делаем скрипт исполняемым

chmod +x /etc/balans

Добавляем его исполнение в крон на каждые полчаса

echo "*/30 * * * * root /etc/balans > /tmp/bal.tmp" >> /etc/crontab