080e7ec105
- Добавлен сервис CrmMySQLService для прямого подключения к MySQL CRM - Обновлён метод get_draft() для получения cf_2624 напрямую из БД - Реализована блокировка полей (readonly) при contact_data_confirmed = true - Добавлен выбор банка для СБП выплат с динамической загрузкой из API - Обновлена документация по работе с cf_2624 и MySQL - Добавлен network_mode: host в docker-compose для доступа к MySQL - Обновлены компоненты формы для поддержки блокировки полей
128 lines
4.9 KiB
SQL
128 lines
4.9 KiB
SQL
-- ============================================================================
|
|
-- SQL запрос для получения документа по claim_document_id
|
|
-- ============================================================================
|
|
-- Параметры:
|
|
-- $1 :: uuid -- claim_id (ID жалобы)
|
|
-- $2 :: uuid -- claim_document_id (ID документа из таблицы clpr_claim_documents)
|
|
-- ============================================================================
|
|
|
|
WITH c AS (
|
|
SELECT id, id::text AS claim_id_text, payload
|
|
FROM clpr_claims
|
|
WHERE id = $1
|
|
)
|
|
|
|
SELECT
|
|
cd.id AS claim_document_id,
|
|
cd.claim_id::text AS claim_id,
|
|
cd.field_name,
|
|
cd.file_id,
|
|
cd.uploaded_at,
|
|
cd.file_name,
|
|
cd.original_file_name,
|
|
cd.file_hash, -- ✅ SHA-256 хеш файла (для дедупликации)
|
|
m.file_url,
|
|
m.file_name AS meta_file_name,
|
|
m.original_file_name AS meta_original_file_name,
|
|
-- ✅ Название документа: сначала из field_label в documents_meta, потом из documents_uploaded, потом из documents_required
|
|
COALESCE(
|
|
NULLIF(m.field_label, ''),
|
|
NULLIF(du_name.document_name_from_uploaded, ''),
|
|
NULLIF(dr_name.document_name_from_required, ''),
|
|
cd.field_name,
|
|
'Документ'
|
|
) AS document_name,
|
|
COALESCE(
|
|
NULLIF(m.original_file_name, ''),
|
|
NULLIF(m.file_name, ''),
|
|
NULLIF(cd.original_file_name, ''),
|
|
NULLIF(cd.file_name, ''),
|
|
NULLIF(regexp_replace(cd.file_id, '^.*/', ''), ''),
|
|
'document.pdf'
|
|
) AS filename_for_upload,
|
|
/* описание: сначала из массива edit_fields_parsed.uploads_descriptions[i],
|
|
потом — из payload.body['uploads_descriptions[i]'],
|
|
потом — из payload['uploads_descriptions[i]'] */
|
|
NULLIF(
|
|
COALESCE(ud_arr.upload_description, ud_body.upload_description, ud_root.upload_description),
|
|
''
|
|
) AS upload_description
|
|
|
|
FROM clpr_claim_documents cd
|
|
JOIN c ON c.claim_id_text = cd.claim_id::text
|
|
|
|
-- достаём i из uploads[i][j]
|
|
JOIN LATERAL (
|
|
SELECT NULLIF((regexp_match(cd.field_name, 'uploads\[(\d+)\]'))[1], '')::int AS i1
|
|
) idx ON TRUE
|
|
|
|
-- мета по файлу (валидный URL) + название документа (field_label)
|
|
LEFT JOIN LATERAL (
|
|
SELECT
|
|
x.file_url::text,
|
|
x.file_name::text,
|
|
x.original_file_name::text,
|
|
x.field_label::text
|
|
FROM jsonb_to_recordset(COALESCE(c.payload->'documents_meta','[]'::jsonb))
|
|
AS x(field_name text, file_id text, file_url text, file_name text, original_file_name text, field_label text)
|
|
WHERE x.field_name = cd.field_name
|
|
AND x.file_id = cd.file_id
|
|
AND x.file_url ~* '^https?://'
|
|
AND x.file_url <> ''
|
|
LIMIT 1
|
|
) m ON TRUE
|
|
|
|
-- название документа из documents_uploaded (fallback, если нет field_label в documents_meta)
|
|
LEFT JOIN LATERAL (
|
|
SELECT du.name::text AS document_name_from_uploaded
|
|
FROM jsonb_to_recordset(COALESCE(c.payload->'documents_uploaded','[]'::jsonb))
|
|
AS du(id text, name text, field_name text, file_id text, type text)
|
|
WHERE du.field_name = cd.field_name
|
|
AND (du.file_id = cd.file_id OR du.file_id IS NULL)
|
|
LIMIT 1
|
|
) du_name ON TRUE
|
|
|
|
-- название документа из documents_required (fallback через тип документа из documents_uploaded)
|
|
LEFT JOIN LATERAL (
|
|
SELECT dr.name::text AS document_name_from_required
|
|
FROM jsonb_to_recordset(COALESCE(c.payload->'documents_required','[]'::jsonb))
|
|
AS dr(id text, name text, required boolean, priority int)
|
|
-- Находим тип документа через documents_uploaded по field_name
|
|
WHERE EXISTS (
|
|
SELECT 1
|
|
FROM jsonb_to_recordset(COALESCE(c.payload->'documents_uploaded','[]'::jsonb))
|
|
AS du(id text, field_name text)
|
|
WHERE du.field_name = cd.field_name
|
|
AND du.id = dr.id
|
|
LIMIT 1
|
|
)
|
|
LIMIT 1
|
|
) dr_name ON TRUE
|
|
|
|
-- 1) массив: payload.edit_fields_parsed.uploads_descriptions[i]
|
|
LEFT JOIN LATERAL (
|
|
SELECT (c.payload->'edit_fields_parsed'->'uploads_descriptions')->>idx.i1 AS upload_description
|
|
) ud_arr ON TRUE
|
|
|
|
-- 2) плоские ключи в payload.body: 'uploads_descriptions[i]'
|
|
LEFT JOIN LATERAL (
|
|
SELECT b.v AS upload_description
|
|
FROM jsonb_each_text(COALESCE(c.payload->'body','{}'::jsonb)) AS b(k, v)
|
|
WHERE b.k = 'uploads_descriptions[' || idx.i1::text || ']'
|
|
LIMIT 1
|
|
) ud_body ON TRUE
|
|
|
|
-- 3) плоские ключи на корне payload: 'uploads_descriptions[i]'
|
|
LEFT JOIN LATERAL (
|
|
SELECT r.v AS upload_description
|
|
FROM jsonb_each_text(COALESCE(c.payload,'{}'::jsonb)) AS r(k, v)
|
|
WHERE r.k = 'uploads_descriptions[' || idx.i1::text || ']'
|
|
LIMIT 1
|
|
) ud_root ON TRUE
|
|
|
|
-- ✅ ФИЛЬТР: ищем конкретный документ по claim_document_id (после всех JOIN)
|
|
WHERE cd.id = $2
|
|
|
|
LIMIT 1; -- ✅ Возвращаем только один документ
|
|
|