СОРМ Яхонт, версия 2020 (архивная)

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

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

просмотр истории страницы
A.COMPANY || '";' ||

case when a.company=0 then

'"1";"";"";"";"' ||
A.NAME || '";"' ||
coalesce(av22.attribute_value,'') || '";"1";"";"";"";"' ||

iif(
(coalesce(av14.attribute_value,'') || coalesce(av13.attribute_value,'') || coalesce(av16.attribute_value,'') || coalesce(av17.attribute_value,'')) <> '', iif(coalesce(av14.attribute_value,'') <> '', av14.attribute_value,'') || iif(coalesce(av13.attribute_value,'') <> '', ' ' || av13.attribute_value,'') || iif(coalesce(av16.attribute_value,'') <> '', ' ' || av16.attribute_value,'') || iif(coalesce(av17.attribute_value,'') <> '', ' ' || av17.attribute_value,'')
, '-')

|| '";"1";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";'

else '"";"";"";"";"";"";"";"";"";"";"";"";"";"";"' end ||

case when a.company = 1 then

a.name || '";"' || coalesce(av4.attribute_value,'') || '";"' || coalesce(av32.attribute_value,'-') || '";"' || coalesce(av1.attribute_value,'') || '";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";'

else '"";"";"";"";"";"";' end

|| '"1";"";"";"";"";"";"";"";"";"";"' ||
|| '"1";"";"";"";"";"";"";"";"";"";"' ||
coalesce(av25.attribute_value,'') ||
case when a.company=0 then
IIF(a.home_id > 0, '"' || IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '') || IIF(coalesce(h.STREET,'') <> '', h.STREET || ' ', '') || coalesce(h.S_NUMBER,'') || IIF(coalesce(h.S_LITER,'') <> '', '/' || h.S_LITER, '') || IIF(coalesce(a.A_HOME_NUMBER, '') <> '', ' ' || a.A_HOME_NUMBER,'') || '', '""')

else
coalesce(av25.attribute_value,'') end

||
'";"1";"";"";"";"";"";"";"";"";"";' ||
IIF(a.home_id > 0, '"' || IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '') || IIF(coalesce(h.STREET,'') <> '', h.STREET || ' ', '') || coalesce(h.S_NUMBER,'') || IIF(coalesce(h.S_LITER,'') <> '', '/' || h.S_LITER, '') || IIF(coalesce(a.A_HOME_NUMBER, '') <> '', ' ' || a.A_HOME_NUMBER,'') || '"', '""')
and a.id is not null
and a.parent_id!=2;

{code}


*/app/asr_billing/cfg/sorm3/abonents-periodic.sql*
{code} set heading off; select distinct '"1";"' ||
select distinct '"1";"' ||
coalesce(U.LOGIN,'-') || '";"' ||
iif(coalesce(u.ip,'') <> '', UF_IP2STRING(U.IP), '') || '";"' ||
A.COMPANY || '";' ||

case when a.company=0 then

'"1";"";"";"";"' ||
A.NAME || '";"' ||
coalesce(av22.attribute_value,'') || '";"1";"";"";"";"' ||

iif(
(coalesce(av14.attribute_value,'') || coalesce(av13.attribute_value,'') || coalesce(av16.attribute_value,'') || coalesce(av17.attribute_value,'')) <> '', iif(coalesce(av14.attribute_value,'') <> '', av14.attribute_value,'') || iif(coalesce(av13.attribute_value,'') <> '', ' ' || av13.attribute_value,'') || iif(coalesce(av16.attribute_value,'') <> '', ' ' || av16.attribute_value,'') || iif(coalesce(av17.attribute_value,'') <> '', ' ' || av17.attribute_value,'')
, '-')

|| '";"1";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";'

else '"";"";"";"";"";"";"";"";"";"";"";"";"";"";"' end ||

case when a.company = 1 then

a.name || '";"' || coalesce(av4.attribute_value,'') || '";"' || coalesce(av32.attribute_value,'-') || '";"' || coalesce(av1.attribute_value,'') || '";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";'

else '"";"";"";"";"";"";' end

|| '"1";"";"";"";"";"";"";"";"";"";"' ||
|| '"1";"";"";"";"";"";"";"";"";"";"' ||
coalesce(av25.attribute_value,'') ||
case when a.company=0 then
IIF(a.home_id > 0, '"' || IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '') || IIF(coalesce(h.STREET,'') <> '', h.STREET || ' ', '') || coalesce(h.S_NUMBER,'') || IIF(coalesce(h.S_LITER,'') <> '', '/' || h.S_LITER, '') || IIF(coalesce(a.A_HOME_NUMBER, '') <> '', ' ' || a.A_HOME_NUMBER,'') || '', '""')

else
coalesce(av25.attribute_value,'') end

||
'";"1";"";"";"";"";"";"";"";"";"";' ||
IIF(a.home_id > 0, '"' || IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '') || IIF(coalesce(h.STREET,'') <> '', h.STREET || ' ', '') || coalesce(h.S_NUMBER,'') || IIF(coalesce(h.S_LITER,'') <> '', '/' || h.S_LITER, '') || IIF(coalesce(a.A_HOME_NUMBER, '') <> '', ' ' || a.A_HOME_NUMBER,'') || '"', '""')

from ABONENTS A
join
(select distinct abonent_id abon
from users_history
where time_changed between (current_timestamp - 1) and current_timestamp
or abonent_id in (select distinct abonents_id abon from abonents_history where time_changed between (current_timestamp - 1) and current_timestamp)
) user_periodic on user_periodic.abon=a.id
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
and a.deleted=0
and a.id is not null
and a.parent_id not in (2)
and a.id!=1373; a.parent_id!=2
and (u.login in (select uh.login from users_history uh where uh.time_changed between (current_timestamp - 1) and current_timestamp)
or a.id in (select ah.abonents_id from abonents_history ah where ah.time_changed between (current_timestamp - 1) and current_timestamp));
{code}