Вы просматриваете старую версию данной страницы. Смотрите текущую версию.
Сравнить с текущим |
просмотр истории страницы
1) Структура отчёт ABONENT
select '' from abonents UNION select distinct '"' || A.ID || '";"' || A.OPERATOR_ID || '";"' || 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' || '";"' || A.CONTRACT_NUMBER || '";"' || 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)) || '";"' || 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)) || '";"' || 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(lpad(EXTRACT(DAY FROM a.activate_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.activate_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.activate_date) || '00:00:00"' else '"43";"";"";"";"";"";"";"";"";"";"";"";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";"' || A.NAME || '";"' || coalesce(av4.attribute_value,'') || '";"' || coalesce(av29.attribute_value,'') || '";"' || coalesce(av1.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(lpad(EXTRACT(DAY FROM a.activate_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.activate_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.activate_date) || '00:00:00"' 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 where A.IS_FOLDER=0 and a.parent_id not in (244) and a.deleted=0 and a.id is not null
2) Структура отчёта ABONENT_ADDRESS
select '' from abonents UNION select distinct '"' || A.ID || '";"' || case when a.company=1 then coalesce(UADR.ATTRIBUTE_VALUE,' ') when a.company=0 then coalesce(PPROPRISKA.ATTRIBUTE_VALUE,' ') end || '";"' || coalesce(POCHADR.ATTRIBUTE_VALUE,' ') || '";"' || case when a.company=1 then coalesce(PHADR.ATTRIBUTE_VALUE,' ') when a.company=0 then ' ' end || '";"' || coalesce( h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER,'') || '";"' || '0' || '";"' || coalesce(H.ZIP_CODE,'') || '";"' || coalesce(H.COUNTRY,'') || '";"' || coalesce(H.REGION,'') || '";"' || ' ' || '";"' || coalesce(H.city,'') || '";"' || coalesce(H.street,'') || '";"' || coalesce(H.s_number,'') || '";"' || coalesce(H.S_LITER,'') || '";"' || coalesce(A.A_HOME_NUMBER,'') || '"' 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 ATTRIBUTE_VALUES UADR on UADR.ABONENT_ID=A.ID and UADR.ATTRIBUTE_ID=25 left join ATTRIBUTE_VALUES PPROPRISKA on PPROPRISKA.ABONENT_ID=A.ID and PPROPRISKA.ATTRIBUTE_ID=15 left join ATTRIBUTE_VALUES POCHADR on POCHADR.ABONENT_ID=A.ID and POCHADR.ATTRIBUTE_ID=27 left join ATTRIBUTE_VALUES PHADR on PHADR.ABONENT_ID=A.ID and PHADR.ATTRIBUTE_ID=26
3) Структура отчёта ABONENT_IDENT
select '' from abonents UNION select distinct '"' || A.ID || '";"";"";"";"";"";"";"";"";"";"' || coalesce(u.mac,'') || '";"";"";"' || coalesce(u.login,'') || '";"' || coalesce(A.EMAIL,'') || '";"";"";"";"";"0";"' || coalesce(uf_ip2string(u.ip),'') || '";"' || ip.MASK || '";"' || uf_ip2string(ip.START_IP) || '";"' || uf_ip2string(ip.END_IP) ||'";"";"";"";"' || CAST(lpad(EXTRACT(DAY FROM a.activate_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.activate_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.activate_date) || ' 00:00:00"' || ';"' || IIF(ah.time_del is not null , CAST(lpad(EXTRACT(DAY FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_DEL) || ' 00:00:00' ,'') || '";"";"";"";"";""' 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 where A.IS_FOLDER=0 and a.parent_id not in (244) and a.id is not null
4) Структура отчёта ABONENT_SERVICE
select '' from abonents UNION select distinct '"' || A.ID || '";"' || A.OPERATOR_ID || '";"' || UU.USLUGA_ID || '";"' || 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(HOUR FROM UU.ENABLE_DATE),2,'0') AS varchar(2)) || ':' || CAST(lpad(EXTRACT(MINUTE FROM UU.ENABLE_DATE),2,'0') AS varchar(2)) || ':' || CAST(lpad(round((EXTRACT(SECOND FROM UU.ENABLE_DATE)),0),2,'0') AS varchar(2)) || '";"' || ' ' || '";"' || ' ' || '";"' || ' ' || '";"' || ' ' || '";"' from abonents a left join users_usluga UU on uu.abonent_id=a.id left join usluga u on u.id=uu.usluga_id where A.IS_FOLDER=0 and a.parent_id not in (244) and a.id is not null and u.deleted=0 and a.deleted=0 and uu.enabled=1 and uu.deleted=0