Интеграция с СОРМ3 VasExperts

Ключ
Эта строка удалена.
Это слово было удалено. Это слово было добавлено.
Эта строка добавлена.

Изменения (7)

просмотр истории страницы
...
/app/asr_billing/cfg/sorm3/subscribers_v2.sh
/app/asr_billing/cfg/sorm3/subscribers_v2.sql
/app/asr_billing/cfg/sorm3/switches.sh
/app/asr_billing/cfg/sorm3/switches.sql
/app/asr_billing/cfg/sorm3/upload_all.sh
{code}
...
chmod a+x /app/asr_billing/cfg/sorm3/services.sh
chmod a+x /app/asr_billing/cfg/sorm3/subscribers_v2.sh
chmod a+x /app/asr_billing/cfg/sorm3/switches.sh
chmod a+x /app/asr_billing/cfg/sorm3/upload_all.sh
bash /app/asr_billing/cfg/sorm3/upload_all.sh
...
fi
{code}
*/app/asr_billing/cfg/sorm3/switches.sh*
{code}
#!/bin/bash

source /app/asr_billing/cfg/sorm3/conf/ftp.conf

LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.log
file_date=$(date +%Y%m%d%H%M)
printf '\n\n' >> $LOGFILE 2>&1
date >> $LOGFILE 2>&1

#chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/switches.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/service_list_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/switches.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/switches_$file_date.txt

if [ -f /app/asr_billing/cfg/sorm3/data/switches_$file_date.txt ]
then
#sed -i '1d' /app/asr_billing/cfg/sorm3/data/switches_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/switches_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/switches_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/switches_$file_date.txt /app/asr_billing/cfg/sorm3/done/switches_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/switches_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/switches_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/upload_all.sh*
{code}
...
/app/asr_billing/cfg/sorm3/ip.sh
/app/asr_billing/cfg/sorm3/ip_numbering.sh
/app/asr_billing/cfg/sorm3/switches.sh
/app/asr_billing/cfg/sorm3/service_list.sh
/app/asr_billing/cfg/sorm3/fixednetid.sh
...
h1. Отчеты
h2. 1. Плоский формат выгрузки абонентских данных
*/app/asr_billing/cfg/sorm3/subscribers_v2.sql*
{code}
set heading off;select distinct '"0";"' || --стандарт связи абонента--
...
and a.id >0;
{code}
h2 2. Выгрузка номенклатуры услуг оператора
*/app/asr_billing/cfg/sorm3/service_list.sql *
{code}
set heading off; select
'"' || U.ID || '";"' || --id услуги --
iif (U.NAME='', 'Удаленная услуга', U.NAME) || '";"' || --имя услуги --
coalesce(U.COMMENTS,'') || '";"' || --описание услуги --
coalesce((
select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED)
from
usluga_history uh
where
uh.usluga_id = u.id
),'01.01.2018') || '";"' || --дата создания услуги (дата активации услуги)--
case
when U.DELETED=1
then coalesce ((select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED)
from
usluga_history uh
where
uh.usluga_id = u.id
and UH.deleted=1
),'01.01.2018')
else ''
end || '";"' || --дата удаления услуги (дата деактивации услуги)-- */
'Ethernet"' --тип сети
from
USLUGA U
where u.id>0;
{code}
h2 3. Выгрузка услуг по абонентам
*/app/asr_billing/cfg/sorm3/services.sql*
{code}
set heading off; select distinct
'"' || U.ID || '";"' || --id услуги --
A.ID || '";"' || --id абонента--
'";"";"' || '";"' || -- ID лицевого счёта, ID точки подключения, ID договора--
U.NAME || '";"' || --имя услуги --
coalesce(CAST(lpad(EXTRACT(DAY FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.ENABLE_DATE),CAST(lpad(EXTRACT(DAY FROM UU.CREATE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.CREATE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.CREATE_DATE)) || '";"' || --дата создания услуги--
case when UU.DELETED=1 then
coalesce((select first 1 CAST(lpad(EXTRACT(DAY FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UUH.TIME_CHANGED) from users_usluga_history uuh where uuh.USERS_USLUGA_ID = uu.id and UUH.deleted=1),CAST(lpad(EXTRACT(DAY FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.NEXT_DATE))
else ' ' end || '"' --дата удаления услуги--
from USERS_USLUGA UU
join abonents a on a.id=uu.abonent_id
join usluga u on uu.usluga_id = u.id
where a.is_folder=0
and a.id >0
and a.parent_id not in (2,244)
and u.id > 0;
{code}