Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select distinct
- CP.agent_name as "Партнёр",
- concat (rapart.inn , '/', rapart.kpp) as "ИНН/КПП Партнёра",
- ac.full_name as "АЦ наименование",
- concat(ac.inn, '/', ac.kpp) as "ИНН/КПП АЦ",
- cl_ac.full_name as "Абонент АЦ",
- concat(cl_ac.inn, '/', cl_ac.kpp) as "ИНН/КПП Абонента АЦ",
- rct1.initial_date as "Дата начала тарифа Абонента АЦ",
- rcert1.initial_time as "Дата начала последнего сертификата",
- rct1.registration_date as "Дата регистрации тарифа Абонента АЦ",
- concat(ac.phone, '/', cl_ac.phone) as "Контактный телефон АЦ / Контактный телефон абонента АЦ",
- concat(ac.email, '/', cl_ac.email) as "Почта АЦ / Почта Абонента АЦ"
- -----Партнёр
- from ro_product rp
- join ro_office ro on rp.office_id = ro.office_id
- join ro_agent ra on ro.agent_id = ra.agent_id
- join ro_agent CP on ra.parent_id = CP.agent_id
- join ro_abonent rapart on CP.abonent_id = rapart.abonent_id
- ----АЦ
- join ro_abonent ac ON rp.abonent_id = ac.abonent_id
- join ro_contract rc on rp.contract_id = rc.contract_id
- join ro_contract_tariff rct on rc.contract_id = rct.contract_id
- join ro_price_tariff rpt on rct.tariff_id = rpt.tariff_id
- join ro_product_person rpp on rp.product_id = rpp.product_id
- join ro_key rk on rpp.key_id = rk.key_id
- join ro_certificate rcert on rk.certificate_id = rcert.certificate_id
- ----Абонент АЦ
- join ro_product cl on cl.accr_represent_id = rp.abonent_id and cl.product_type_id = 1
- join ro_abonent cl_ac ON cl.abonent_id = cl_ac.abonent_id
- join ro_contract rc1 on cl.contract_id = rc1.contract_id
- join ro_contract_tariff rct1 on cl.contract_id = rct1.contract_id
- join ro_price_tariff rpt1 on rct1.tariff_id = rpt1.tariff_id
- join ro_product_person rpp1 on cl.product_id = rpp1.product_id
- join ro_key rk1 on rpp1.key_id = rk1.key_id
- join ro_certificate rcert1 on rk1.certificate_id = rcert1.certificate_id
- where rp.product_type_id = 1
- and rpt1.tariff_id in (216,248,300)
- and rp.accr_represent is not null
- and ra.agent_id in (3374, 172, 2422)
- and ((rct.initial_date < '2020-01-01' and rct.end_date >= '2020-01-01' and rct.step_id = 5) or (rcert.initial_time < '2020-01-01' and rcert.end_time >= '2020-01-01' and rcert.step_id = 5))
- and ((rct1.initial_date < '2020-01-01' and rct1.end_date >= '2020-01-01' and rct1.step_id = 5) or (rcert1.initial_time < '2020-01-01' and rcert1.end_time >= '2020-01-01' and rcert1.step_id = 5));
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement