123
123SELECT coalesce("sbis"."sum", 0) as "unaccepted_sbis_sum", odz_sum - coalesce("sbis"."sum", 0) as odz_sum_accepted, *
FROM (SELECT t.company_id,
t.name_short as company_name,
t.inn as company_inn,
t.manager_f3pro_id,
t.lastname as lastname,
t.firstname as firstname,
t.patronymic as patronymic,
max(payment_last_date) as payment_last_date,
round(avg(t.payment_day_diff)) as sum_payment_day_diff,
avg(payment_term) as avg_payment_term,
sum(invoice_sum) as all_invoice_sum,
sum(paidsum_by_invoice) as paidsum_by_invoice,
sum(odz_sum) as odz_sum,
avg(coalesce(summdt62, 0)) as summdt62,
sum(tech_dz_sum) as tech_dz_sum,
sum(coalesce("t"."pdz0_15", 0) + coalesce("t"."pdz16_30", 0) + coalesce("t"."pdz31_60", 0) +
coalesce("t"."pdz61_180", 0) + coalesce("t"."pzdc181", 0)) as pdz,
case when max(pay.payment_period) is null then 0 else max(pay.payment_period) end as payment_period
FROM (SELECT "dbz".*,
"company"."manager_f3pro_id",
(extract(epoch from (dbz.payment_date - dbz.invoice_date))) / 86400 as payment_day_diff,
"company"."name_short",
"company"."inn",
"f3pro_user"."lastname",
"f3pro_user"."firstname",
"f3pro_user"."patronymic"
FROM "dbz"."dbz" "dbz"
LEFT JOIN "nsi"."organizations" "organization" ON "dbz"."organization_id" = "organization"."id"
LEFT JOIN "nsi"."organizations" "company" ON "dbz"."company_id" = "company"."id"
LEFT JOIN "user" "f3pro_user" ON "company"."manager_f3pro_id" = "f3pro_user"."id"
WHERE "dbz"."organization_id" IN (SELECT "id"
FROM "nsi"."organizations"
WHERE ("parent_id" IN (SELECT "uo"."organization_id"
FROM "user_organization" "uo"
LEFT JOIN "nsi"."organizations" "no" ON no.id = uo.organization_id
WHERE (("user_id" = 140) OR ("user_id" IN
(SELECT DISTINCT "uo"."user_id"
FROM "user_organization" "uo"
LEFT JOIN "public"."user" "u" ON uo.user_id = u.id
WHERE "u"."supervisor" IN
(4509, 2014, 872, 4300, 140, 4514, 154, 1457, 197, 932, 178, 1714))))
AND ("type_org" = 20)))
AND ("type_org" = 30))) "t"
LEFT JOIN "dbz".payment_period_invoice "pay"
on "pay"."invoice_num" = "t"."invoice_num" and "pay"."invoice_date" = "t"."invoice_date"
GROUP BY "t"."company_id", "t"."manager_f3pro_id", "t"."name_short", "t"."inn", "t"."lastname", "t"."firstname",
"t"."patronymic") "t"
LEFT JOIN (SELECT "contractor_name", sum(sum)
FROM (SELECT *
FROM (SELECT case
when sbis_status_code <> '' then case
when sbis_status_code::integer in (3, 4, 7, 9, 10)
then true
else false end
else false end as check_pra,
case
when sbis_status_code <> ''
then case when sbis_status_code::integer in (7) then true else false end
else false end as check_contractor,
case when status_name is null then 'СБИС' else 'ДИАДОК' end as sad,
case when status_name is null then sbis_status_name else status_name end as status,
case when sbis_status_name = 'Отправлен' then true else false end as delivery,
*
FROM (SELECT "notes"."document_number",
"diadoc"."status_name",
"notes"."id" AS "document_id",
"notes"."document_date",
"notes"."recipient_name" AS "department_name",
"contractor"."pharmdata_id" AS "contractor_pharmdata_id",
"contractor"."name_short" AS "contractor_name",
"contractor"."inn" AS "contractor_inn",
"contractor"."manager_proapteka" AS "contractor_manager",
"notes"."sum",
"sbis"."document_sbis_id",
case
when (recipient_code ilike '777%') = true and length(recipient_code) > 7
then substr(recipient_code, 4, length(recipient_code))
else recipient_code
end as recipient_code,
"sbis_notes"."date_and_time" AS "sbis_status_date",
"sbis_notes"."amount"::float4 AS "sbis_sum",
case
when (EXTRACT(month FROM "notes"."document_date")) >= 4 and
(EXTRACT(month FROM "notes"."document_date") <= 10)
then notes.document_date +
CAST(affiliate_org.delivery_hours_summer || ' hours' AS Interval)
else notes.document_date +
CAST(affiliate_org.delivery_hours_winter || ' hours' AS Interval) end as date_season,
"sbis_notes"."status" ->> 'Код' AS "sbis_status_code",
case
when "sbis_notes"."commentary" = 'Акция, не подлежит оплате'
then "sbis_notes"."commentary"
else null end AS "commentary",
case sbis_sign_notes.status
when 2 then 'Накладной в СБИС не существует'
when 1 then 'Подписано'
when 0 then 'Ошибка при подписании'
else 'Нет данных' end as sign_status,
"sbis_notes"."status" ->> 'Название' AS "sbis_status_name"
FROM "delivery_notes"."notes"
LEFT JOIN "delivery_notes"."sbis_sign_notes"
ON notes.id = sbis_sign_notes.note_id
LEFT JOIN (select *
from (
with canceled as (
select document_number,
max(id) as id,
max(date_and_time) as date_and_time,
max(document_sbis_id) as document_sbis_id,
notes.date::date as document_date,
year_note
from (select date_part('year', date) as year_note, *
from delivery_notes.sbis_notes) notes
where status ->> 'Код' in ('20', '27', '22')
group by notes.document_number, notes.date, year_note),
correct as (
select document_number,
max(id) as id,
max(date_and_time) as date_and_time,
max(document_sbis_id) as document_sbis_id,
notes.date::date as document_date,
year_note
from (select date_part('year', date) as year_note, *
from delivery_notes.sbis_notes) notes
where status ->> 'Код' not in ('20', '27', '22')
group by notes.document_number, notes.date, year_note)
select case when correct.id is null then canceled.id else correct.id end as id,
case
when correct.document_number is null
then canceled.document_number
else correct.document_number end as document_number,
case
when correct.document_date is null
then canceled.document_date
else correct.document_date end as document_date,
case
when correct.id is null
then canceled.date_and_time
else correct.date_and_time end as date_and_time,
case
when correct.document_sbis_id is null
then canceled.document_sbis_id
else correct.document_sbis_id end as document_sbis_id
from correct
left join canceled on correct.document_number = canceled.document_number) t) sbis
on sbis.document_number = notes.document_number and
sbis.document_date = notes.document_date
LEFT JOIN "delivery_notes"."sbis_notes" ON sbis_notes.id = sbis.id and
notes.document_date::date = sbis.document_date
LEFT JOIN "nsi"."organizations" "org"
ON org.pharmdata_id = notes.recipient_code::integer
LEFT JOIN "nsi"."organizations" "contractor" ON contractor.id = org.parent_id
LEFT JOIN (select document_number, document_date, name as status_name
from delivery_notes.diadoc_notes
left join nsi.diadoc_status on diadoc_notes.status_id = diadoc_status.id
order by document_date desc) as diadoc
ON diadoc.document_number = notes.document_number and
diadoc.document_date = notes.document_date
LEFT JOIN "delivery_notes"."affiliate_org"
ON affiliate_org.affiliate_number::integer = org.protek_affiliate_id
LEFT JOIN "public"."user_organization" "user_org"
ON user_org.organization_id = org.id
WHERE ("user_org"."user_id" = 140)
AND ("notes"."document_date" > '2019-01-01')) "t") "t"
ORDER BY "document_date" DESC) "sbis"
WHERE ("check_contractor" = FALSE)
AND (NOT ("sbis_status_name" IS NULL))
GROUP BY "contractor_name") "sbis" ON "sbis"."contractor_name" = t."company_name"
LEFT JOIN (
select nu1.id as id_file, nu1.deadline, nu1.company_id as company_id_file, nu1.system_filename
from nsi.upload_files nu1
join (select max(id) as id, company_id
from nsi.upload_files nu
group by company_id) nu_join on nu_join.id = nu1.id) as nu_join2
on t.company_id = nu_join2.company_id_file
LEFT JOIN (
select dbz_history.id as history_dbz_id,
dbz_join.organization_id as dbz_history_organization_id,
dbz_history.total_overdue_days
from report.dbz_history dbz_history
join (select max(id) as id, organization_id
from report.dbz_history
group by organization_id) dbz_join
on dbz_join.id = dbz_history.id) as dbz_history_table on t.company_id = dbz_history_organization_id