СОРМ Январь

Skip to end of metadata
Go to start of metadata
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/abonents.sql | sed '/^$/d'| sed 's/  */ /g' > /app/asr_billing/cfg/sorm3/data/ABONENTS_`date +%Y%m%d_%H%M`.csv

Для ABONENT_IDENT используем следующее, для перевода ip в HEX

chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/abonent_ident.sql | sed '/^$/d'| sed 's/  */ /g' | sed 's/-//g' | awk -F ';' -v f=1 -v t=43 '{for(i=f;i<=t;i++) { if(i==22) printf("%X;", $22); else { printf("%s",$i); if(i<43) printf("%s",";") } if(i==43) print("\r") } } ' > /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_`date +%Y%m%d_%H%M`.csv

ABONENTS

abonents.sql

Заголовок

ID;REGION_ID;CONTRACT_DATE;CONTRACT;ACCOUNT;ACTUAL_FROM;ACTUAL_TO;ABONENT_TYPE;NAME_INFO_TYPE;FAMILY_NAME;GIVEN_NAME;INITIAL_NAME;UNSTRUCT_NAME;BIRTH_DATE;IDENT_CARD_TYPE_ID;IDENT_CARD_TYPE;IDENT_CARD_SERIAL;IDENT_CARD_NUMBER;IDENT_CARD_DESCRIPTION;IDENT_CARD_UNSTRUCT;BANK;BANK_ACCOUNT;FULL_NAME;INN;CONTACT;PHONE_FAX;STATUS;ATTACH;DETACH;NETWORK_TYPE;INTERNAL_ID1;INTERNAL_ID2
set heading off;
select distinct '' ||
-- ID
A.ID || ';' ||
-- REGION_ID
A.OPERATOR_ID || ';' ||
-- CONTRACT_DATE Дата договора
CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date) || ' 00:00:00' ||  ';' ||
-- CONTRACT Номер договора
A.CONTRACT_NUMBER  || ';' ||
-- ACCOUNT Нужно добавить номер счёта
';' ||
-- ACTUAL_FROM
CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date) || ' ' || CAST(lpad(EXTRACT(HOUR FROM a.create_date),2,'0') AS varchar(2))  || ':' ||  CAST(lpad(EXTRACT(MINUTE FROM a.create_date),2,'0') AS varchar(2)) || ':' || CAST(lpad(round((EXTRACT(SECOND FROM a.create_date)),0),2,'0') AS varchar(2)) || ';' ||
-- ACTUAL_TO
--'31.12.2024 23:59:59' || ';' ||
iif(os.status=26, CAST(lpad(EXTRACT(DAY FROM os.APPLY_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM os.APPLY_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM os.APPLY_DATE) || ' ' || CAST(lpad(EXTRACT(HOUR FROM os.APPLY_DATE),2,'0') AS varchar(2))  || ':' ||  CAST(lpad(EXTRACT(MINUTE FROM os.APPLY_DATE),2,'0') AS varchar(2)) || ':' || CAST(lpad(round((EXTRACT(SECOND FROM os.APPLY_DATE)),0),2,'0') AS varchar(2)), '31.12.2024 23:59:59') || ';' ||
--01.06.2018 00:00:00

--CAST(lpad(EXTRACT(DAY FROM CURRENT_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM CURRENT_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM CURRENT_DATE) || ' ' || CAST(lpad(EXTRACT(HOUR FROM CURRENT_TIMESTAMP),2,'0') AS varchar(2))  || ':' ||  CAST(lpad(EXTRACT(MINUTE FROM CURRENT_TIMESTAMP),2,'0') AS varchar(2)) || ':' || CAST(lpad(round((EXTRACT(SECOND FROM CURRENT_TIMESTAMP)),0),2,'0') AS varchar(2)) || ';' || 

-- ABONENT_TYPE
case when a.company=0 then

-- Физ. лицо
'42;1;;;;' || A.NAME || ';' || coalesce(av22.attribute_value,'') || ';' || coalesce(av32.attribute_value,'') || ';0;' || coalesce(av14.attribute_value,'') || ';' || coalesce(av13.attribute_value,'') || ';' ||  coalesce(av16.attribute_value,'') || coalesce(av17.attribute_value,'') || ';' || coalesce(av7.attribute_value,'') || ';' || coalesce(av6.attribute_value,'') || ';;;;;' ||
case when a.id in (select distinct abonent_id from abonents_block where (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1)) then     '1' else '0' end || ';' ||


cast(coalesce(CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date),'1111111') as varchar(32))

 || '00:00:00' else

-- Юр. лицо
-- ABONENT_TYPE NAME_INFO_TYPE FAMILY_NAME GIVEN_NAME INITIAL_NAME UNSTRUCT_NAME BIRTH_DATE IDENT_CARD_TYPE_ID IDENT_CARD_TYPE IDENT_CARD_SERIAL IDENT_CARD_NUMBER IDENT_CARD_DESCRIPTION IDENT_CARD_UNSTRUCT
'43;;;;;;;;;;;;;' ||
-- BANK
coalesce(av7.attribute_value,'') || ';' ||
-- BANK_ACCOUNT
coalesce(av6.attribute_value,'') || ';' ||
-- FULL_NAME
A.NAME || ';' ||
-- INN
coalesce(av4.attribute_value,'') || ';' ||
-- CONTACT
coalesce(av29.attribute_value,'') || ';' ||
-- PHONE_FAX 
coalesce(av1.attribute_value,'') || ';' ||
-- STATUS 
iif(os.status=26, '1','0') || ';' ||
-- ATTACH
CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date) || ' 00:00:00' || ';' ||
--DETACH
iif(os.status=26, CAST(lpad(EXTRACT(DAY FROM os.APPLY_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM os.APPLY_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM os.APPLY_DATE) || ' ' || CAST(lpad(EXTRACT(HOUR FROM os.APPLY_DATE),2,'0') AS varchar(2))  || ':' ||  CAST(lpad(EXTRACT(MINUTE FROM os.APPLY_DATE),2,'0') AS varchar(2)) || ':' || CAST(lpad(round((EXTRACT(SECOND FROM os.APPLY_DATE)),0),2,'0') AS varchar(2)), '31.12.2024 23:59:59') || ';' ||
--NETWORK_TYPE INTERNAL_ID1 INTERNAL_ID2
'4;;'

end

from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ATTRIBUTE_VALUES as av1 on a.id=av1.ABONENT_ID and av1.ATTRIBUTE_ID=1
left join ATTRIBUTE_VALUES as av2 on a.id=av2.ABONENT_ID and av2.ATTRIBUTE_ID=2
left join ATTRIBUTE_VALUES as av4 on a.id=av4.ABONENT_ID and av4.ATTRIBUTE_ID=4
left join ATTRIBUTE_VALUES as av6 on a.id=av6.ABONENT_ID and av6.ATTRIBUTE_ID=6
left join ATTRIBUTE_VALUES as av7 on a.id=av7.ABONENT_ID and av7.ATTRIBUTE_ID=7
left join ATTRIBUTE_VALUES as av13 on a.id=av13.ABONENT_ID and av13.ATTRIBUTE_ID=13
left join ATTRIBUTE_VALUES as av14 on a.id=av14.ABONENT_ID and av14.ATTRIBUTE_ID=14
left join ATTRIBUTE_VALUES as av16 on a.id=av16.ABONENT_ID and av16.ATTRIBUTE_ID=16
left join ATTRIBUTE_VALUES as av17 on a.id=av17.ABONENT_ID and av17.ATTRIBUTE_ID=17
left join ATTRIBUTE_VALUES as av15 on a.id=av15.ABONENT_ID and av15.ATTRIBUTE_ID=15
left join ATTRIBUTE_VALUES as av22 on a.id=av22.ABONENT_ID and av22.ATTRIBUTE_ID=22
left join ATTRIBUTE_VALUES as av25 on a.id=av25.ABONENT_ID and av25.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES as av29 on a.id=av29.ABONENT_ID and av29.ATTRIBUTE_ID=29
left join ATTRIBUTE_VALUES as av32 on a.id=av32.ABONENT_ID and av32.ATTRIBUTE_ID=32
left join abonents_block ab on ab.abonent_id=a.id
left join OBJECTS_STATUS os on a.id=os.OBJECT_ID and OBJECT_NAME='Abonents'
where A.IS_FOLDER=0
and a.parent_id not in (2, 244, 1358)
and a.deleted=0
and a.id is not null;

ABONENT_ADDRESS

abonent_address.sql

Заголовок

ABONENT_ID;REGION_ID;ADDRESS_TYPE_ID;ADDRESS_TYPE;ZIP;COUNTRY;REGION;ZONE;CITY;STREET;BUILDING;BUILD_SECT;APARTMENT;UNSTRUCT_INFO;BEGIN_TIME;END_TIME;INTERNAL_ID1;INTERNAL_ID2
set heading off;
select distinct
-- ABONENT_ID
A.ID || ';' ||
-- REGION_ID
A.OPERATOR_ID || ';' ||
-- ADDRESS_TYPE_ID
'0'|| ';' ||
-- ADDRESS_TYPE
'0'|| ';' ||
-- ZIP
H.ZIP_CODE || ';' ||
-- COUNTRY
H.COUNTRY || ';' ||
-- REGION
H.REGION || ';' ||
-- ZONE
';' ||
-- CITY
H.city || ';' ||
-- STREET
H.street || ';' ||
-- BUILDING
H.s_number || ';' ||
-- BUILD_SECT
H.S_LITER || ';' ||
-- APARTMENT
A.A_HOME_NUMBER || ';' ||
-- UNSTRUCT_INFO
';' ||
-- BEGIN_TIME
CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date) || ' 00:00:00' || ';' ||
-- END_TIME
'31.12.2024 23:59:59' || ';' ||
-- INTERNAL_ID1
';' ||
-- INTERNAL_ID2
';'
from ABONENTS A
left join HOMES H on A.HOME_ID = H.ID
where A.IS_FOLDER=0
and a.parent_id not in (2, 244, 1358)
and a.deleted=0
and a.id is not null;

set heading off;
select distinct
-- ABONENT_ID
A.ID || ';' ||
-- REGION_ID
A.OPERATOR_ID || ';' ||
-- ADDRESS_TYPE_ID
'3'|| ';' ||
-- ADDRESS_TYPE
'1'|| ';' ||
-- ZIP
';' ||
-- COUNTRY
';' ||
-- REGION
';' ||
-- ZONE
';' ||
-- CITY
';' ||
-- STREET
';' ||
-- BUILDING
';' ||
-- BUILD_SECT
';' ||
-- APARTMENT
';' ||
-- UNSTRUCT_INFO
'394030, Воронежская область, город Воронеж, улица Кольцовская, дом 35' || ';' ||
-- BEGIN_TIME
CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date) || ' 00:00:00' || ';' ||
-- END_TIME
'31.12.2024 23:59:59' || ';' ||
-- INTERNAL_ID1
';' ||
-- INTERNAL_ID2
';'
from ABONENTS A
left join HOMES H on A.HOME_ID = H.ID
where A.IS_FOLDER=0
and a.parent_id not in (2, 244, 1358)
and a.deleted=0
and a.id is not null;

ABONENT_IDENT

abonent_ident.sql

Не верно выводится IP и маска. Нужно в формате HEX.

Заголовок

ABONENT_ID;REGION_ID;IDENT_TYPE;PHONE;INTERNAL_NUMBER;IMSI;IMEI;ICC;MIN;ESN;EQUIPMENT_TYPE;MAC;VPI;VCI;LOGIN;E_MAIL;PIN;USER_DOMAIN;RESERVED;ORIGINATOR_NAME;IP_TYPE;IPV4;IPV6;IP_MASK;IP_RANGE_START;IP_RANGE_END;INTERNAL_ID1;INTERNAL_ID2;BEGIN_TIME;END_TIME;LINE_OBJECT;LINE_CROSS;LINE_BLOCK;LINE_PAIR;LINE_RESERVED;LOC_TYPE;LOC_LAC;LOC_CELL;LOC_TA;LOC_CELL_WIRELESS;LOC_MAC;LOC_LATITUDE;LOC_LONGITUDE;LOC_PROJECTION_TYPE
A.ID || ';' ||
--- REGION_ID;IDENT_TYPE
'36;5;' ||
--- PHONE
';' ||
--- INTERNAL_NUMBER;IMSI;IMEI;ICC;MIN;ESN
';;;;;;'  || 
--- EQUIPMENT_TYPE
'0;' ||
--- MAC
coalesce(u.mac,'') || ';' || 
--- VPI;VCI;
';;' ||
--- LOGIN
coalesce(u.login,'') || ';' || 
--- E_MAIL
coalesce(A.EMAIL,'') || ';' || 
--- PIN;USER_DOMAIN;RESERVED;ORIGINATOR_NAME
';;;;' ||
--- IP_TYPE
'0;' ||
--- IPV4
iif(u.ip is not null, uf_ip2string(u.ip),'') ||  ';' ||  
--- IPV6
';' || 
--- MASK
'255.255.255.254' || ';' || 
--- IP_RANGE_START
iif(ip.START_IP is not null, uf_ip2string(ip.START_IP),'') || ';' || 
--- IP_RANGE_END
iif(ip.END_IP is not null, uf_ip2string(ip.END_IP),'') ||';' || 
--- ;INTERNAL_ID1;INTERNAL_ID2
';;' ||
--- BEGIN_TIME
CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date) || ' 00:00:00' || ';' || 
--- END_TIME
iif(os.status=26, CAST(lpad(EXTRACT(DAY FROM os.APPLY_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM os.APPLY_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM os.APPLY_DATE) || ' ' || CAST(lpad(EXTRACT(HOUR FROM os.APPLY_DATE),2,'0') AS varchar(2))  || ':' ||  CAST(lpad(EXTRACT(MINUTE FROM os.APPLY_DATE),2,'0') AS varchar(2)) || ':' || CAST(lpad(round((EXTRACT(SECOND FROM os.APPLY_DATE)),0),2,'0') AS varchar(2)), '31.12.2024 23:59:59') || ';' ||
--- LINE_OBJECT;LINE_CROSS;LINE_BLOCK;LINE_PAIR;LINE_RESERVED;LOC_TYPE;LOC_LAC;LOC_CELL;LOC_TA;LOC_CELL_WIRELESS;LOC_MAC;LOC_LATITUDE;LOC_LONGITUDE;LOC_PROJECTION_TYPE
';;;;;;;;;;;;;'
from abonents a
left join USERS U on A.ID = U.ABONENT_ID
left join abonents_history ah on ah.abonents_id=a.id
left join ip_pull ip on u.PULL_ID=ip.PULL_ID
left join OBJECTS_STATUS os on a.id=os.OBJECT_ID and OBJECT_NAME='Abonents'
where A.IS_FOLDER=0
and a.parent_id not in (244)
and a.id is not null;
Введите метки, чтобы добавить к этой странице:
Please wait 
Ищите метку? просто начните печатать.