fontvielle
03918b1e58
Проверено по шагам мастера, что bj берёт из DEPO сам: подготовка базы, префикс счетов (шаг 3), реквизиты (шаг 4), готовность (шаг 10), ручной ввод, подбор клиента для портфеля ЛК. - Реквизиты (Det01): счёт НРД ссылается на исходную версию лица, а ИНН НРД бывает только в новых (в исходной — ИНН НДЦ) — лицо по любой версии (ID или SELF_ID; ИНН НРД или MNEM «НДЦ»); номер счёта — и «счёт/раздел», не только «счёт/LEI/код/раздел». Счёт НРД — тот, с которым записан выбранный раздел (пара «раздел + счёт» — по ней процедуры находят место хранения), а не самый частый. Для MSSQL — только шире: прежнее находится. - Поиск по ФИО, договору и счёту — без учёта регистра, как в MSSQL (Cyrillic_General_CI_AS): в базе Fancy с кодировкой NONE регистр кириллицы различается — Firebird-варианты сравнивают в WIN1251 (подбор клиента для портфеля ЛК — запросы вынесены в fsql/lkportfolio.go, поиск и список договоров). - На копии базы: шаг 4 — счёт, код, разделы, ИНН и наименования организации; поиск «бр-1147», «бубликов» находит; FBLivePrepare — 103 из 103. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01Q7eREaYwUt1i4fNLZcGnjn
136 lines
11 KiB
Go
136 lines
11 KiB
Go
package fsql
|
||
|
||
// FB — запросы к DEPO на Firebird (Fancy на Firebird 2.5, 27.10.2026): ключ — текст T-SQL, значение — текст
|
||
// Firebird с теми же @именами параметров. Здесь только то, что подключение к Firebird не переводит само
|
||
// (lkgateway/fancyfb.go переводит dbo., TOP, ISNULL, GETDATE(), N'…', exec): системные каталоги, LIKE с [_],
|
||
// оконные функции, OFFSET…FETCH, DATEADD с параметром. Параметр внутри функции — с CAST: Firebird иначе не
|
||
// знает его типа.
|
||
var FB = map[string]string{
|
||
Cat01: `SELECT CASE WHEN EXISTS (SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = UPPER(REPLACE(CAST(@p1 AS VARCHAR(63)), 'dbo.', ''))) THEN 1 ELSE 0 END FROM RDB$DATABASE`,
|
||
|
||
Cat02: `SELECT TRIM(RDB$FIELD_NAME) FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = UPPER(REPLACE(CAST(@p1 AS VARCHAR(63)), 'dbo.', ''))`,
|
||
|
||
// права: SYSDBA и владелец таблицы — всё; иначе — выданное логину или PUBLIC (RDB$PRIVILEGE: S, I, U, D)
|
||
Cat03: `SELECT CASE WHEN CURRENT_USER = 'SYSDBA'
|
||
OR EXISTS (SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = UPPER(REPLACE(CAST(@p1 AS VARCHAR(63)), 'dbo.', '')) AND RDB$OWNER_NAME = CURRENT_USER)
|
||
OR EXISTS (SELECT 1 FROM RDB$USER_PRIVILEGES WHERE RDB$USER IN (CURRENT_USER, 'PUBLIC')
|
||
AND RDB$RELATION_NAME = UPPER(REPLACE(CAST(@p1 AS VARCHAR(63)), 'dbo.', '')) AND RDB$PRIVILEGE = SUBSTRING(CAST(@p2 AS VARCHAR(10)) FROM 1 FOR 1))
|
||
THEN 1 ELSE 0 END FROM RDB$DATABASE`,
|
||
|
||
Cat04: `SELECT COUNT(*) FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = UPPER(CAST(@t AS VARCHAR(63))) AND RDB$FIELD_NAME = UPPER(CAST(@c AS VARCHAR(63)))`,
|
||
|
||
Cat05: `SELECT COUNT(*) FROM RDB$PROCEDURES p WHERE p.RDB$PROCEDURE_NAME = UPPER(REPLACE(CAST(@p AS VARCHAR(63)), 'dbo.', ''))
|
||
AND (CURRENT_USER = 'SYSDBA' OR p.RDB$OWNER_NAME = CURRENT_USER OR EXISTS (SELECT 1 FROM RDB$USER_PRIVILEGES u
|
||
WHERE u.RDB$USER IN (CURRENT_USER, 'PUBLIC') AND u.RDB$RELATION_NAME = p.RDB$PROCEDURE_NAME AND u.RDB$PRIVILEGE = 'X'))`,
|
||
|
||
Cat06: `SELECT CASE WHEN EXISTS (SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = UPPER(REPLACE(CAST(@n AS VARCHAR(63)), 'dbo.', '')))
|
||
OR EXISTS (SELECT 1 FROM RDB$PROCEDURES WHERE RDB$PROCEDURE_NAME = UPPER(REPLACE(CAST(@n AS VARCHAR(63)), 'dbo.', ''))) THEN 1 ELSE 0 END FROM RDB$DATABASE`,
|
||
|
||
// в Firebird нет EXECUTE AS: процедуры работают правами, выданными им самим, — пользователь не нужен
|
||
Cat07: `SELECT 1 FROM RDB$DATABASE`,
|
||
|
||
QEv09: `SELECT REFERENCE FROM FTV_M2M_LINES WHERE GUID=@g AND (CAST(@i AS VARCHAR(12)) = '' OR UPPER(ISIN) = CAST(@i AS VARCHAR(12))) ORDER BY ID`,
|
||
|
||
QEv11: `SELECT FIRST 1 EVENT_DATE, COALESCE(STATE,''), COALESCE(COMMENT,'') FROM FTV_M2M_EVENTS
|
||
WHERE EVENT_TYPE='HANDBOOK' AND (COMMENT IS NOT NULL OR TRIM(COALESCE(STATE,'')) <> '') ORDER BY EVENT_DATE DESC`,
|
||
|
||
Hlth01: `SELECT FIRST (@n) * FROM (
|
||
SELECT CAST('FTV_M2M' AS VARCHAR(16)) AS T, ID, CAST(GUID AS VARCHAR(36)) AS G, CAST('' AS VARCHAR(36)) AS R, CAST(COALESCE(DIRECTION,'') AS VARCHAR(64)) AS K, COALESCE(TimeEnd, TimeNew) AS A, COALESCE(COMMENT,'') AS C
|
||
FROM FTV_M2M WHERE STATE='ERROR' AND COALESCE(TimeEnd, TimeNew) > DATEADD(DAY, -CAST(@d AS INTEGER), CURRENT_TIMESTAMP)
|
||
UNION ALL
|
||
SELECT 'FTV_M2M_EVENTS', ID, CAST(GUID AS VARCHAR(36)), COALESCE(REFERENCE,''), EVENT_TYPE, COALESCE(TimeEnd, TimeNew), COALESCE(COMMENT,'')
|
||
FROM FTV_M2M_EVENTS WHERE STATE='ERROR' AND EVENT_TYPE STARTING WITH 'BJ_' AND EVENT_TYPE <> 'BJ_PING' AND COALESCE(TimeEnd, TimeNew) > DATEADD(DAY, -CAST(@d AS INTEGER), CURRENT_TIMESTAMP)
|
||
) x ORDER BY A DESC`,
|
||
|
||
// COUNT(*) OVER () и OFFSET…FETCH в Firebird 2.5 нет: число — подзапросом, первая строка — FIRST 1
|
||
Hlth02: `SELECT FIRST 1 (SELECT COUNT(*) FROM FTV_M2M_EVENTS e WHERE e.STATE='ERROR' AND e.EVENT_TYPE STARTING WITH 'BJ_' AND e.ID > @last AND COALESCE(e.TimeEnd, e.TimeNew) > DATEADD(DAY, -1, CURRENT_TIMESTAMP)),
|
||
ID, EVENT_TYPE, CAST(GUID AS VARCHAR(36)), COALESCE(COMMENT,'')
|
||
FROM FTV_M2M_EVENTS WHERE STATE='ERROR' AND EVENT_TYPE STARTING WITH 'BJ_' AND ID > @last AND COALESCE(TimeEnd, TimeNew) > DATEADD(DAY, -1, CURRENT_TIMESTAMP)
|
||
ORDER BY ID DESC`,
|
||
|
||
Hlth03: `SELECT COALESCE(MAX(ID),0) FROM FTV_M2M_EVENTS WHERE EVENT_TYPE STARTING WITH 'BJ_'`,
|
||
|
||
// сложение строк (+) — ||; UNION строк разной длины — CAST; параметр в DATEADD — CAST
|
||
Hist02: `SELECT FIRST %d * FROM (
|
||
SELECT d.R_DATE AS RD, d.PROC_DATE AS PD, COALESCE(d.NUM,'') AS NUM, d.STATE AS ST, CAST('зачисление' AS VARCHAR(32)) AS KIND,
|
||
z.DEPO AS ACC, COALESCE(v.ISIN,'') AS ISIN, COALESCE(v.NAME,'') AS SEC, CAST(COALESCE(z.AMOUNT,0) AS DOUBLE PRECISION) AS QTY,
|
||
CAST(TRIM(COALESCE(z.COMMENT,'') || ' ' || COALESCE(z.REJECT_REASON,'')) AS VARCHAR(600)) AS NOTE
|
||
FROM U_OP_INV_ZACH z JOIN OD_DOCS d ON d.ID = z.DOC LEFT JOIN OD_VALUES v ON v.ID = z.SHARE
|
||
WHERE z.DEPO IN (%s)
|
||
UNION ALL
|
||
SELECT d.R_DATE, d.PROC_DATE, COALESCE(d.NUM,''), d.STATE, 'списание',
|
||
p.DEPO, COALESCE(v.ISIN,''), COALESCE(v.NAME,''), -CAST(COALESCE(p.AMOUNT,0) AS DOUBLE PRECISION),
|
||
TRIM(COALESCE(p.COMMENT,'') || ' ' || COALESCE(p.REJECT_REASON,''))
|
||
FROM U_OP_INV_SPIS p JOIN OD_DOCS d ON d.ID = p.DOC LEFT JOIN OD_VALUES v ON v.ID = p.SHARE
|
||
WHERE p.DEPO IN (%s)
|
||
UNION ALL
|
||
SELECT d.R_DATE, d.PROC_DATE, COALESCE(d.NUM,''), d.STATE, 'перевод: списание',
|
||
t.D_DEPO, COALESCE(v.ISIN,''), COALESCE(v.NAME,''), -CAST(COALESCE(t.AMOUNT,0) AS DOUBLE PRECISION),
|
||
TRIM(COALESCE(t.COMMENT,'') || ' ' || COALESCE(t.REJECT_REASON,''))
|
||
FROM U_OP_INV_PEREVOD t JOIN OD_DOCS d ON d.ID = t.DOC LEFT JOIN OD_VALUES v ON v.ID = t.SHARE
|
||
WHERE t.D_DEPO IN (%s)
|
||
UNION ALL
|
||
SELECT d.R_DATE, d.PROC_DATE, COALESCE(d.NUM,''), d.STATE, 'перевод: зачисление',
|
||
t.K_DEPO, COALESCE(v.ISIN,''), COALESCE(v.NAME,''), CAST(COALESCE(t.AMOUNT,0) AS DOUBLE PRECISION),
|
||
TRIM(COALESCE(t.COMMENT,'') || ' ' || COALESCE(t.REJECT_REASON,''))
|
||
FROM U_OP_INV_PEREVOD t JOIN OD_DOCS d ON d.ID = t.DOC LEFT JOIN OD_VALUES v ON v.ID = t.SHARE
|
||
WHERE t.K_DEPO IN (%s)) x
|
||
WHERE x.RD >= @p1 AND x.RD < DATEADD(DAY, 1, CAST(@p2 AS TIMESTAMP))
|
||
ORDER BY x.RD DESC, x.NUM DESC`,
|
||
|
||
// сложение строк — ||; UPPER по кириллице — в WIN1251 (база Fancy — NONE, там UPPER меняет только латиницу)
|
||
Man01: `SELECT FIRST 20 COALESCE(NULLIF(f.SELF_ID,0),f.ID), COALESCE(f.NAME,''), f.DATE_BIRTH, COALESCE(f.DOC_TYPE,0), COALESCE(f.DOC_SER,''), COALESCE(f.DOC_NUM,''),
|
||
f.DOC_DAT, COALESCE(f.DOC_PLACE,''), COALESCE(f.DOC_CODE,''), COALESCE(f.PHONE,'')
|
||
FROM OD_FACES f
|
||
WHERE COALESCE(f.LAST_FLAG,1)=1 AND (UPPER(CAST(f.NAME AS VARCHAR(255) CHARACTER SET WIN1251)) LIKE UPPER(CAST(@q AS VARCHAR(255) CHARACTER SET WIN1251))
|
||
OR f.DOC_NUM=@d OR REPLACE(COALESCE(f.DOC_SER,''),' ','') || f.DOC_NUM=@d)
|
||
AND EXISTS (SELECT 1 FROM OD_CLIENTS c WHERE c.FACE=COALESCE(NULLIF(f.SELF_ID,0),f.ID) AND c.NUMBER LIKE '{ACC}%' AND c.STATE=0 AND c.D_CLOSE IS NULL)
|
||
ORDER BY f.NAME`,
|
||
|
||
// последние курсы валют: от 20 валют, а не от всех курсов — Firebird иначе перебирает миллионы строк
|
||
// с подзапросом на каждую (на MSSQL — доли секунды, на Firebird — больше 200 с)
|
||
Fx01: `SELECT UPPER(TRIM(v.SYSNAME)),
|
||
(SELECT FIRST 1 x.OFICDATE FROM OD_VALUES_RATES x WHERE x.VALUE_ID = v.ID AND x.MARKET = 0 AND x.RATE > 0 ORDER BY x.OFICDATE DESC),
|
||
(SELECT FIRST 1 CAST(x.RATE AS DOUBLE PRECISION) FROM OD_VALUES_RATES x WHERE x.VALUE_ID = v.ID AND x.MARKET = 0 AND x.RATE > 0 ORDER BY x.OFICDATE DESC)
|
||
FROM OD_VALUES v WHERE v.V_TYPE = 1`,
|
||
|
||
// Поиск по ФИО, договору и счёту без учёта регистра, как в MSSQL (Cyrillic_General_CI_AS): в базе Fancy с
|
||
// кодировкой NONE сравнение и UPPER различают регистр кириллицы — сравниваем в WIN1251.
|
||
Lkp01: `SELECT FIRST 50 COALESCE(p.PORT_NUM,''), COALESCE(f.NAME,''), c.NUMBER
|
||
FROM OD_CLIENTS c JOIN OD_FACES f ON f.ID=c.FACE LEFT JOIN OD_CLIENTS_PORTS p ON p.CLIENT=c.ID
|
||
WHERE c.NUMBER LIKE @pre AND c.STATE=0 AND c.D_CLOSE IS NULL
|
||
AND (UPPER(CAST(f.NAME AS VARCHAR(255) CHARACTER SET WIN1251)) LIKE UPPER(CAST(@q AS VARCHAR(260) CHARACTER SET WIN1251))
|
||
OR UPPER(CAST(p.PORT_NUM AS VARCHAR(255) CHARACTER SET WIN1251)) LIKE UPPER(CAST(@q AS VARCHAR(260) CHARACTER SET WIN1251))
|
||
OR UPPER(CAST(c.NUMBER AS VARCHAR(255) CHARACTER SET WIN1251)) LIKE UPPER(CAST(@q AS VARCHAR(260) CHARACTER SET WIN1251)))
|
||
ORDER BY f.NAME`,
|
||
QDb02: `SELECT FIRST (%d) DISTINCT PORT_NUM FROM OD_CLIENTS_PORTS
|
||
WHERE UPPER(CAST(PORT_NUM AS VARCHAR(255) CHARACTER SET WIN1251)) LIKE UPPER(CAST(@p1 AS VARCHAR(260) CHARACTER SET WIN1251)) ORDER BY PORT_NUM`,
|
||
Dp02: `SELECT FIRST (%d) p.PORT_NUM, COUNT(DISTINCT rst.VALUE_ID)
|
||
FROM OD_CLIENTS_PORTS p
|
||
JOIN OD_CLIENTS c ON c.ID = p.CLIENT
|
||
JOIN OD_RESTS rst ON rst.REG_1 = c.ID
|
||
JOIN OD_VALUES v ON v.ID = rst.VALUE_ID AND v.V_TYPE = 2
|
||
WHERE CAST(rst.K AS DOUBLE PRECISION) <> 0 AND (CAST(@p1 AS VARCHAR(255)) = ''
|
||
OR UPPER(CAST(p.PORT_NUM AS VARCHAR(255) CHARACTER SET WIN1251)) LIKE UPPER(CAST(@p2 AS VARCHAR(260) CHARACTER SET WIN1251)))
|
||
GROUP BY p.PORT_NUM ORDER BY p.PORT_NUM`,
|
||
|
||
// SELECT без FROM в Firebird нет
|
||
QEvPosted: `SELECT (SELECT COUNT(*) FROM FTV_M2M WHERE GUID=@g),
|
||
(SELECT COUNT(*) FROM FTV_M2M_EVENTS WHERE GUID=@g AND EVENT_TYPE='BJ_ORDER_ACCEPTED' AND STATE='SUCCESS'),
|
||
(SELECT COUNT(*) FROM FTV_M2M_EVENTS WHERE GUID=@g AND EVENT_TYPE='BJ_ORDER_ACCEPTED' AND STATE<>'SUCCESS'),
|
||
COALESCE((SELECT FIRST 1 STATE FROM FTV_M2M WHERE GUID=@g ORDER BY ID DESC),''),
|
||
COALESCE((SELECT FIRST 1 COMMENT FROM FTV_M2M WHERE GUID=@g AND STATE='ERROR' ORDER BY ID DESC),
|
||
(SELECT FIRST 1 COMMENT FROM FTV_M2M_EVENTS WHERE GUID=@g AND EVENT_TYPE='BJ_ORDER_ACCEPTED' AND STATE='ERROR' ORDER BY ID DESC), '')
|
||
FROM RDB$DATABASE`,
|
||
|
||
// PATINDEX в Firebird 2.5 нет: начало номера до первой цифры — по ближайшей из позиций цифр
|
||
Acc01: `SELECT FIRST 8 p, COUNT(*) FROM (SELECT LEFT(NUMBER, MINVALUE(POSITION('0' IN NUMBER || '0'),
|
||
COALESCE(NULLIF(POSITION('1' IN NUMBER), 0), 9999), COALESCE(NULLIF(POSITION('2' IN NUMBER), 0), 9999),
|
||
COALESCE(NULLIF(POSITION('3' IN NUMBER), 0), 9999), COALESCE(NULLIF(POSITION('4' IN NUMBER), 0), 9999),
|
||
COALESCE(NULLIF(POSITION('5' IN NUMBER), 0), 9999), COALESCE(NULLIF(POSITION('6' IN NUMBER), 0), 9999),
|
||
COALESCE(NULLIF(POSITION('7' IN NUMBER), 0), 9999), COALESCE(NULLIF(POSITION('8' IN NUMBER), 0), 9999),
|
||
COALESCE(NULLIF(POSITION('9' IN NUMBER), 0), 9999)) - 1) AS p
|
||
FROM OD_CLIENTS WHERE STATE=0 AND D_CLOSE IS NULL AND NUMBER IS NOT NULL) x
|
||
WHERE p <> '' GROUP BY p ORDER BY COUNT(*) DESC`,
|
||
}
|