Программирование, т. 8

dfgrd · 16 декабря 2018

Just.Doit написал 16.12.2018 в 16:09:

ктонибудь может объяснить какого *уя
https://jsfiddle.net/s2ntc1zk/

в момент обработки события клика на чекбокс у него состояние перешло в следующее, непосредственно после e.preventDefault тоже, а вот после e.preventDefault асинхронно - оно остается в старом состоянии

Просто не делай так

Zellar · 16 декабря 2018

Rooster написал 16.12.2018 в 21:39:
До чего технологии дошли

Без хуя тебя ебли

uwotm8 · 17 декабря 2018

Just.Doit написал 16.12.2018 в 16:09:

ктонибудь может объяснить какого *уя
https://jsfiddle.net/s2ntc1zk/

в момент обработки события клика на чекбокс у него состояние перешло в следующее, непосредственно после e.preventDefault тоже, а вот после e.preventDefault асинхронно - оно остается в старом состоянии

Такое ощущение, будто дефолтное поведение не предотвращается, а откатывается

мб так и есть, с радио то же самое

Изменено 17 декабря 2018 пользователем uwotm8

Ramil · 17 декабря 2018

запрос на 210 строк возвращает Cause: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-811, SQLSTATE=21000, SQLERRMC=null, DRIVER=3.63.75

после перезапуска вебсферы - отрабатывает без ошибок

куда копать?

sonac · 17 декабря 2018

Судя по коду, у тебя либо подзапрос возвращает более одной строки, в случае когда должен возвращать одну, либо условие апдейта не строгое и джойнит больше нежели 1к1

Ramil · 17 декабря 2018

ну это как бы понятно, но какого хуя после перезапуска нормально отрабатывает?

choojoykin · 17 декабря 2018

Ramil написал 17.12.2018 в 05:37:
ну это как бы понятно, но какого хуя после перезапуска нормально отрабатывает?

тут мем про запустил код - ошибки, перезапустил - все работает

sonac · 17 декабря 2018

Ramil написал 17.12.2018 в 05:37:
ну это как бы понятно, но какого хуя после перезапуска нормально отрабатывает?

Я ебу че у тя там за запрос, может какие-то системные таблицы юзаются/сессии, которые чистятся после перезапуска.

Ramil · 17 декабря 2018

Показать содержимое

<select id="getDocument4AdditionalFinesFromMigrationClaim" parameterType="Map" resultMap="additionalFinesClaimListMap">
/* Выбор мгрированных требований по плательщику*/
WITH
<include refid="RO_ASV_RESPONSIBILITY"/>,

claim_migrated_by_insurer(claimId, claimNumber, claimDate, claimActual) AS (
SELECT
PENALTY_ARREARS_REPAYMENT_CLAIM_ID AS claimId,
MIGRATION_NUMBER AS claimNumber,
DATE(MIGRATION_DATE) AS claimDate,
CASE WHEN STATUS_ID = 29 THEN 0 ELSE 1 END AS claimActual
FROM ASV_PENALTY_ARREARS_REPAYMENT_CLAIM
WHERE 1 = 1
and INSURER_ID = #{insurerId}
AND (STATUS_ID IN (28, 30, 31)
<if test="currentClaimId != null">
OR STATUS_ID = 29
</if>)
AND PENALTY_PREREQUISITE_TYPE_ID = 8
)

, <include refid="getAllAdditionalFinesByInsurer"/>

/*Выбор всех актуальных сумм требований без учета компенсаций*/
, all_amounts_for_claims AS (
SELECT
ra.*,
claims.*,
rd.R_DOCUMENT_ID AS responsibilityDocumentId
FROM ASV_RESPONSIBILITY_AMOUNT ra
JOIN RO_ASV_RESPONSIBILITY r ON r.R_ID = ra.R_ID and r.RO_ID = ra.RO_ID
JOIN ASV_RESPONSIBILITY_DOCUMENT rd
ON rd.R_DOCUMENT_ID = r.R_DOCUMENT_ID AND rd.R_DOCUMENT_PREREQUISITE_TYPE_ID = 5
JOIN claim_migrated_by_insurer claims ON claims.claimId = rd.R_DOCUMENT_PREREQUISITE_ID
WHERE ra.OPERATION_TYPE IN (1, 2, 3, 4, 5, 9) AND RA.R_AMOUNT_ACTUAL_STATE = claims.claimActual
AND ra.R_AMOUNT_ALREADY_CHECKED != 1
and ra.R_AMOUNT_FEE_OVERDUE != 2
)
, all_paid_amount_by_chain_after_claim as (
select claims.* from all_amounts_for_claims claims
union all
select
ra.*
, claims.claimId
, claims.claimNumber
, claims.claimDate
, claims.claimActual
, claims.responsibilityDocumentId
from ASV_RESPONSIBILITY_AMOUNT ra
join all_amounts_for_claims claims on claims.R_AMOUNT_CHAIN_UNIQUE_ID = ra.R_AMOUNT_CHAIN_UNIQUE_ID and claims.RO_ID = ra.RO_ID
and claims.OPERATION_TYPE IN (1,2)
where ra.OPERATION_TYPE in (3, 4, 5) and ra.R_ID > claims.R_ID
AND ra.R_AMOUNT_ALREADY_CHECKED != 1
)
, amounts_for_claims_without_new_payments as (
select
ac.*
, coalesce((select max(mf
.PENALTY_MIGRATION_FINES_DATE) from ASV_PENALTY_MIGRATION_FINES mf
where exists ( select
KBK_ID as id
from ASV_KBK
where
KBK_MARKER_ID = ac.KBK_MARKER_ID and
KBK_GROUP_ID = 2 and
KBK_ID in (select KBK_ID from ASV_KBK_INSURER_CATEGORY where CATEGORY_ID = #{insurerCategoryId}) and
KBK_DISABLED = 0 and
KBK_ASV_ACTUAL = 1 and
KBK_START_DATE <= current date and
(KBK_END_DATE is null or KBK_END_DATE > current date)
and KBK_ID = mf.KBK_ID
)
and mf.INSURER_ID = #{insurerId}), claimDate) as initialStartCalcDate
from all_paid_amount_by_chain_after_claim ac
where (R_PAYMENT_CONSOLIDATED_DATE IS NULL OR (
R_PAYMENT_CONSOLIDATED_DATE <= coalesce((select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f
.R_AMOUNT_CHAIN_UNIQUE_ID = AC.R_AMOUNT_CHAIN_UNIQUE_ID), coalesce((select max(mf
.PENALTY_MIGRATION_FINES_DATE) from ASV_PENALTY_MIGRATION_FINES mf
where exists ( select
KBK_ID as id
from ASV_KBK
where
KBK_MARKER_ID = ac.KBK_MARKER_ID and
KBK_GROUP_ID = 2 and
KBK_ID in (select KBK_ID from ASV_KBK_INSURER_CATEGORY where CATEGORY_ID = #{insurerCategoryId}) and
KBK_DISABLED = 0 and
KBK_ASV_ACTUAL = 1 and
KBK_START_DATE <= current date and
(KBK_END_DATE is null or KBK_END_DATE > current date)
and KBK_ID = mf.KBK_ID
)
and mf.INSURER_ID = #{insurerId}), claimDate))
)

)
)

/* Выбор взносов, погашений, отмен погашений*/
, amounts_without_fines AS (
SELECT
ra.claimId,
ra.claimNumber,
ra.claimDate,
ra.claimActual,
ra.R_ID as responsibilityId,
ra.r_amount_id AS responsibilityAmountId,
ra.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID AS srcResponsibilityAmountId,
ra.R_AMOUNT_SRC_REPAYMENT_R_AMOUNT_ID AS srcRepaymentAmountId,
ra.R_AMOUNT_TOTAL_SUM AS totalSum,
ra.R_AMOUNT_PAID_SUM AS paidSum,
ra.OPERATION_TYPE AS operationType,
ra.R_AMOUNT_CHAIN_UNIQUE_ID AS chainUid,
ra.KBK_ID AS kbkId,
ra.KBK_GROUP_ID AS kbkGroupId,
ra.KBK_MARKER_ID AS kbkMarkerId,
ra.R_AMOUNT_FEE_DEADLINE_DATE AS feeDeadlineDate,
ra.responsibilityDocumentId AS responsibilityDocumentId,
ra.initialStartCalcDate AS initialStartCalcDate
FROM amounts_for_claims_without_new_payments ra
WHERE (ra.OPERATION_TYPE IN (1, 2) AND KBK_GROUP_ID = 1)
OR ra.OPERATION_TYPE IN (3, 4, 5)
)

/* Выбор всех непогашенных взносов*/
, non_payed_amounts (responsibilityAmountId, arrearsSum) AS (
SELECT responsibilityAmountId, arrearsSum
FROM (
SELECT
am.responsibilityAmountId AS responsibilityAmountId,
sum(coalesce(am2.totalSum, 0) - coalesce(am2.paidSum, 0)) AS arrearsSum
FROM amounts_without_fines am
LEFT JOIN amounts_without_fines am2 ON am.chainUid = am2.chainUid
WHERE am.operationType IN (1, 2)
GROUP BY am.responsibilityAmountId
)
WHERE arrearsSum > 0
)

, select_with_bankrupt_id as(
select
awf.*
, (select max(br.BANKRUPT_ID)from ASV_BANKRUPT_RTK_RESPONSIBILITY rtk
JOIN ASV_BANKRUPT_RTK br on br.BANKRUPT_RTK_ID = rtk.BANKRUPT_RTK_ID
JOIN ASV_BANKRUPT b on b.BANKRUPT_ID = br.BANKRUPT_ID and b.STATUS_ID not in (159, 162)
where rtk.R_AMOUNT_ID = awf.responsibilityAmountId) as ra_bankruptId
from amounts_without_fines awf
)
, select_with_all_bankrupt as(
select
awf.*
, case when ra_bankruptId is not null then
(select max(br.BANKRUPT_RTK_CREATED_DATE) from ASV_BANKRUPT_RTK br
where awf.ra_bankruptId = br.BANKRUPT_ID)
else null end as ra_bankruptRtkCalcDate
, case when awf.ra_bankruptId is null then 0 else 1 end as ra_isBankruptcyAmount
from select_with_bankrupt_id awf
)

select
-awf.responsibilityDocumentId AS id
, awf.claimId AS documentPrerequisiteId
, awf.responsibilityId AS responsibilityId
, awf.responsibilityDocumentId AS responsibilityDocumentId
, awf.claimActual AS actual
, 5 AS DOCUMENT_PREREQUISITE_TYPE_ID
, awf.claimNumber AS DOCUMENT_NUMBER
, awf.claimDate AS DOCUMENT_DATE
/*, awf.operationType AS operationType*/
, awf.responsibilityAmountId AS ra_rid
, awf.responsibilityAmountId AS ra_responsibilityAmountId
, awf.claimId AS ra_parentId
, awf.responsibilityId AS ra_responsibilityId
, awf.claimActual AS ra_parentActual
, awf.totalSum as ra_totalSum

, awf.kbkId AS ra_kbkId
, awf.kbkGroupId AS ra_kbkGroup
, awf.kbkMarkerId AS ra_kbkMarker
, kbk.KBK_CODE AS ra_kbkCode
, 4 AS ra_additionalFineType
, awf.feeDeadlineDate AS ra_feeDeadlineDate
, awf.initialStartCalcDate AS ra_initialStartCalcDate
, npa.arrearsSum AS ra_arrearsSum

, (select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId ) as ra_fineCalcdate

, (select totalFinesSum from get_total_previous_fine_sum t where awf.responsibilityAmountId = t
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID) AS ra_totalPreviousFinesSum
<choose>
<when test="currentClaimId != null">
,coalesce((select totalFinesSum from get_total_current_fine_sum t where awf.responsibilityAmountId = t
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID), 0) AS ra_fineSum
</when>
<otherwise>
,0 as ra_fineSum
</otherwise>
</choose>

, case when #{insurerUnregistered} = 1 then 1 else 0 end AS ra_isLiquidationInsurer
, CASE when exists(select 1 from asv_delay_schedule ads
JOIN asv_delay ad ON ad.DELAY_ID = ads.DELAY_ID
where ads.R_AMOUNT_ID = awf.responsibilityAmountId AND ad.DELAY_STATUS IN (6, 7)) then 1
ELSE 0
END AS ra_isDelayAmount
, awf.chainUid AS ra_chainUin
, awf.ra_bankruptId
, awf.ra_isBankruptcyAmount
, awf.ra_bankruptRtkCalcDate
, cast(#{liquidationDate, jdbcType=DATE} as DATE) as ra_liquidationDate
from select_with_all_bankrupt awf
join non_payed_amounts npa on awf.responsibilityAmountId = npa.responsibilityAmountId
LEFT JOIN ASV_KBK kbk ON kbk.KBK_ID = awf.kbkId

where not exists (
select * from ASV_PENALTY_ARREARS_REPAYMENT_CLAIM parc
join ASV_RESPONSIBILITY_DOCUMENT rd on rd.R_DOCUMENT_PREREQUISITE_ID = parc.PENALTY_ARREARS_REPAYMENT_CLAIM_ID and rd.R_DOCUMENT_PREREQUISITE_TYPE_ID = 5
join RO_ASV_RESPONSIBILITY r on r.R_DOCUMENT_ID = rd.R_DOCUMENT_ID
join ASV_RESPONSIBILITY_AMOUNT ra on ra.R_ID = r.R_ID and ra.RO_ID = r.RO_ID and ra.OPERATION_TYPE = 9
and ra.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId
where parc.PENALTY_PREREQUISITE_TYPE_ID = 4 and parc.STATUS_ID not in (28, 29, 30, 31)
<if test="currentClaimId != null">
and parc.PENALTY_ARREARS_REPAYMENT_CLAIM_ID != #{currentClaimId}
</if>
) and (awf.ra_bankruptRtkCalcDate >= (select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId ) or awf.ra_bankruptId is null)
or (select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId ) is null
<if test="liquidationDate != null">
or (#{liquidationDate} > (select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId ))
</if>
<if test="liquidationDate != null and currentClaimId == null">
and awf.feeDeadlineDate < #{liquidationDate}
</if>
</select>
 
<sql id="RO_ASV_RESPONSIBILITY">
RO_ASV_RESPONSIBILITY as (
select * from asv_responsibility where ro_id = (select ro_id from asv_insurer where insurer_id = #{insurerId})
)
</sql>

Rooster · 17 декабря 2018

Vot suka mudak blyat

TheDeadSkin · 17 декабря 2018

почему это всё без индентации (или это просто так вставилось?) и ключевые SQL слова через раз нижним или врехним регистром, это же нереал читать

hira88 · 17 декабря 2018

'Ramil' написал 17.12.2018 в 04:20:

запрос на 210 строк возвращает Cause: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-811, SQLSTATE=21000, SQLERRMC=null, DRIVER=3.63.75
после перезапуска вебсферы - отрабатывает без ошибок
куда копать?

**Kant** · 17 декабря 2018

да там почти в каждом запросе в селекте подселект

Любой из них может упасть

Ramil · 17 декабря 2018

TheDeadSkin написал 17.12.2018 в 08:29:
почему это всё без индентации (или это просто так вставилось?) и ключевые SQL слова через раз нижним или врехним регистром, это же нереал читать

так вставилось

регистр - вот такие у нас погромисты zatrolka_tupostu

hira88 · 17 декабря 2018

@@Kant
оцени убийцу артифакта https://github.com/danilw/card-game-GLSL

Закончил демку, карточная игра в одном шейдере(и логика и рендеринг)

для запуска
Виндовс юзеры могут скачать лаунчер
Линукс юзеры спокойно могут запускать из браузера на Шедертое (безопасно для Винды тоже, не зависнет если открыть)
или тут (не нажимайте если Виндовс, браузер зависнет)
Изменено 17 декабря 2018 пользователем hira88

Ramil · 17 декабря 2018

дай ключ

**Kant** · 17 декабря 2018

так так так, что тут у нас

нет ладдера

нет чатика

нельзя лутать бабосы бесплатно

"игра говно даже покупать не буду" (с)

Rooster · 17 декабря 2018

Что это за демон блять

SKYnv · 17 декабря 2018

Ramil написал 17.12.2018 в 07:20:

Показать содержимое

<select id="getDocument4AdditionalFinesFromMigrationClaim" parameterType="Map" resultMap="additionalFinesClaimListMap">
/* Выбор мгрированных требований по плательщику*/
WITH
<include refid="RO_ASV_RESPONSIBILITY"/>,

claim_migrated_by_insurer(claimId, claimNumber, claimDate, claimActual) AS (
SELECT
PENALTY_ARREARS_REPAYMENT_CLAIM_ID AS claimId,
MIGRATION_NUMBER AS claimNumber,
DATE(MIGRATION_DATE) AS claimDate,
CASE WHEN STATUS_ID = 29 THEN 0 ELSE 1 END AS claimActual
FROM ASV_PENALTY_ARREARS_REPAYMENT_CLAIM
WHERE 1 = 1
and INSURER_ID = #{insurerId}
AND (STATUS_ID IN (28, 30, 31)
<if test="currentClaimId != null">
OR STATUS_ID = 29
</if>)
AND PENALTY_PREREQUISITE_TYPE_ID = 8
)

, <include refid="getAllAdditionalFinesByInsurer"/>

/*Выбор всех актуальных сумм требований без учета компенсаций*/
, all_amounts_for_claims AS (
SELECT
ra.*,
claims.*,
rd.R_DOCUMENT_ID AS responsibilityDocumentId
FROM ASV_RESPONSIBILITY_AMOUNT ra
JOIN RO_ASV_RESPONSIBILITY r ON r.R_ID = ra.R_ID and r.RO_ID = ra.RO_ID
JOIN ASV_RESPONSIBILITY_DOCUMENT rd
ON rd.R_DOCUMENT_ID = r.R_DOCUMENT_ID AND rd.R_DOCUMENT_PREREQUISITE_TYPE_ID = 5
JOIN claim_migrated_by_insurer claims ON claims.claimId = rd.R_DOCUMENT_PREREQUISITE_ID
WHERE ra.OPERATION_TYPE IN (1, 2, 3, 4, 5, 9) AND RA.R_AMOUNT_ACTUAL_STATE = claims.claimActual
AND ra.R_AMOUNT_ALREADY_CHECKED != 1
and ra.R_AMOUNT_FEE_OVERDUE != 2
)
, all_paid_amount_by_chain_after_claim as (
select claims.* from all_amounts_for_claims claims
union all
select
ra.*
, claims.claimId
, claims.claimNumber
, claims.claimDate
, claims.claimActual
, claims.responsibilityDocumentId
from ASV_RESPONSIBILITY_AMOUNT ra
join all_amounts_for_claims claims on claims.R_AMOUNT_CHAIN_UNIQUE_ID = ra.R_AMOUNT_CHAIN_UNIQUE_ID and claims.RO_ID = ra.RO_ID
and claims.OPERATION_TYPE IN (1,2)
where ra.OPERATION_TYPE in (3, 4, 5) and ra.R_ID > claims.R_ID
AND ra.R_AMOUNT_ALREADY_CHECKED != 1
)
, amounts_for_claims_without_new_payments as (
select
ac.*
, coalesce((select max(mf
.PENALTY_MIGRATION_FINES_DATE) from ASV_PENALTY_MIGRATION_FINES mf
where exists ( select
KBK_ID as id
from ASV_KBK
where
KBK_MARKER_ID = ac.KBK_MARKER_ID and
KBK_GROUP_ID = 2 and
KBK_ID in (select KBK_ID from ASV_KBK_INSURER_CATEGORY where CATEGORY_ID = #{insurerCategoryId}) and
KBK_DISABLED = 0 and
KBK_ASV_ACTUAL = 1 and
KBK_START_DATE <= current date and
(KBK_END_DATE is null or KBK_END_DATE > current date)
and KBK_ID = mf.KBK_ID
)
and mf.INSURER_ID = #{insurerId}), claimDate) as initialStartCalcDate
from all_paid_amount_by_chain_after_claim ac
where (R_PAYMENT_CONSOLIDATED_DATE IS NULL OR (
R_PAYMENT_CONSOLIDATED_DATE <= coalesce((select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f
.R_AMOUNT_CHAIN_UNIQUE_ID = AC.R_AMOUNT_CHAIN_UNIQUE_ID), coalesce((select max(mf
.PENALTY_MIGRATION_FINES_DATE) from ASV_PENALTY_MIGRATION_FINES mf
where exists ( select
KBK_ID as id
from ASV_KBK
where
KBK_MARKER_ID = ac.KBK_MARKER_ID and
KBK_GROUP_ID = 2 and
KBK_ID in (select KBK_ID from ASV_KBK_INSURER_CATEGORY where CATEGORY_ID = #{insurerCategoryId}) and
KBK_DISABLED = 0 and
KBK_ASV_ACTUAL = 1 and
KBK_START_DATE <= current date and
(KBK_END_DATE is null or KBK_END_DATE > current date)
and KBK_ID = mf.KBK_ID
)
and mf.INSURER_ID = #{insurerId}), claimDate))
)

)
)

/* Выбор взносов, погашений, отмен погашений*/
, amounts_without_fines AS (
SELECT
ra.claimId,
ra.claimNumber,
ra.claimDate,
ra.claimActual,
ra.R_ID as responsibilityId,
ra.r_amount_id AS responsibilityAmountId,
ra.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID AS srcResponsibilityAmountId,
ra.R_AMOUNT_SRC_REPAYMENT_R_AMOUNT_ID AS srcRepaymentAmountId,
ra.R_AMOUNT_TOTAL_SUM AS totalSum,
ra.R_AMOUNT_PAID_SUM AS paidSum,
ra.OPERATION_TYPE AS operationType,
ra.R_AMOUNT_CHAIN_UNIQUE_ID AS chainUid,
ra.KBK_ID AS kbkId,
ra.KBK_GROUP_ID AS kbkGroupId,
ra.KBK_MARKER_ID AS kbkMarkerId,
ra.R_AMOUNT_FEE_DEADLINE_DATE AS feeDeadlineDate,
ra.responsibilityDocumentId AS responsibilityDocumentId,
ra.initialStartCalcDate AS initialStartCalcDate
FROM amounts_for_claims_without_new_payments ra
WHERE (ra.OPERATION_TYPE IN (1, 2) AND KBK_GROUP_ID = 1)
OR ra.OPERATION_TYPE IN (3, 4, 5)
)

/* Выбор всех непогашенных взносов*/
, non_payed_amounts (responsibilityAmountId, arrearsSum) AS (
SELECT responsibilityAmountId, arrearsSum
FROM (
SELECT
am.responsibilityAmountId AS responsibilityAmountId,
sum(coalesce(am2.totalSum, 0) - coalesce(am2.paidSum, 0)) AS arrearsSum
FROM amounts_without_fines am
LEFT JOIN amounts_without_fines am2 ON am.chainUid = am2.chainUid
WHERE am.operationType IN (1, 2)
GROUP BY am.responsibilityAmountId
)
WHERE arrearsSum > 0
)

, select_with_bankrupt_id as(
select
awf.*
, (select max(br.BANKRUPT_ID)from ASV_BANKRUPT_RTK_RESPONSIBILITY rtk
JOIN ASV_BANKRUPT_RTK br on br.BANKRUPT_RTK_ID = rtk.BANKRUPT_RTK_ID
JOIN ASV_BANKRUPT b on b.BANKRUPT_ID = br.BANKRUPT_ID and b.STATUS_ID not in (159, 162)
where rtk.R_AMOUNT_ID = awf.responsibilityAmountId) as ra_bankruptId
from amounts_without_fines awf
)
, select_with_all_bankrupt as(
select
awf.*
, case when ra_bankruptId is not null then
(select max(br.BANKRUPT_RTK_CREATED_DATE) from ASV_BANKRUPT_RTK br
where awf.ra_bankruptId = br.BANKRUPT_ID)
else null end as ra_bankruptRtkCalcDate
, case when awf.ra_bankruptId is null then 0 else 1 end as ra_isBankruptcyAmount
from select_with_bankrupt_id awf
)

select
-awf.responsibilityDocumentId AS id
, awf.claimId AS documentPrerequisiteId
, awf.responsibilityId AS responsibilityId
, awf.responsibilityDocumentId AS responsibilityDocumentId
, awf.claimActual AS actual
, 5 AS DOCUMENT_PREREQUISITE_TYPE_ID
, awf.claimNumber AS DOCUMENT_NUMBER
, awf.claimDate AS DOCUMENT_DATE
/*, awf.operationType AS operationType*/
, awf.responsibilityAmountId AS ra_rid
, awf.responsibilityAmountId AS ra_responsibilityAmountId
, awf.claimId AS ra_parentId
, awf.responsibilityId AS ra_responsibilityId
, awf.claimActual AS ra_parentActual
, awf.totalSum as ra_totalSum

, awf.kbkId AS ra_kbkId
, awf.kbkGroupId AS ra_kbkGroup
, awf.kbkMarkerId AS ra_kbkMarker
, kbk.KBK_CODE AS ra_kbkCode
, 4 AS ra_additionalFineType
, awf.feeDeadlineDate AS ra_feeDeadlineDate
, awf.initialStartCalcDate AS ra_initialStartCalcDate
, npa.arrearsSum AS ra_arrearsSum

, (select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId ) as ra_fineCalcdate

, (select totalFinesSum from get_total_previous_fine_sum t where awf.responsibilityAmountId = t
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID) AS ra_totalPreviousFinesSum
<choose>
<when test="currentClaimId != null">
,coalesce((select totalFinesSum from get_total_current_fine_sum t where awf.responsibilityAmountId = t
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID), 0) AS ra_fineSum
</when>
<otherwise>
,0 as ra_fineSum
</otherwise>
</choose>

, case when #{insurerUnregistered} = 1 then 1 else 0 end AS ra_isLiquidationInsurer
, CASE when exists(select 1 from asv_delay_schedule ads
JOIN asv_delay ad ON ad.DELAY_ID = ads.DELAY_ID
where ads.R_AMOUNT_ID = awf.responsibilityAmountId AND ad.DELAY_STATUS IN (6, 7)) then 1
ELSE 0
END AS ra_isDelayAmount
, awf.chainUid AS ra_chainUin
, awf.ra_bankruptId
, awf.ra_isBankruptcyAmount
, awf.ra_bankruptRtkCalcDate
, cast(#{liquidationDate, jdbcType=DATE} as DATE) as ra_liquidationDate
from select_with_all_bankrupt awf
join non_payed_amounts npa on awf.responsibilityAmountId = npa.responsibilityAmountId
LEFT JOIN ASV_KBK kbk ON kbk.KBK_ID = awf.kbkId

where not exists (
select * from ASV_PENALTY_ARREARS_REPAYMENT_CLAIM parc
join ASV_RESPONSIBILITY_DOCUMENT rd on rd.R_DOCUMENT_PREREQUISITE_ID = parc.PENALTY_ARREARS_REPAYMENT_CLAIM_ID and rd.R_DOCUMENT_PREREQUISITE_TYPE_ID = 5
join RO_ASV_RESPONSIBILITY r on r.R_DOCUMENT_ID = rd.R_DOCUMENT_ID
join ASV_RESPONSIBILITY_AMOUNT ra on ra.R_ID = r.R_ID and ra.RO_ID = r.RO_ID and ra.OPERATION_TYPE = 9
and ra.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId
where parc.PENALTY_PREREQUISITE_TYPE_ID = 4 and parc.STATUS_ID not in (28, 29, 30, 31)
<if test="currentClaimId != null">
and parc.PENALTY_ARREARS_REPAYMENT_CLAIM_ID != #{currentClaimId}
</if>
) and (awf.ra_bankruptRtkCalcDate >= (select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId ) or awf.ra_bankruptId is null)
or (select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId ) is null
<if test="liquidationDate != null">
or (#{liquidationDate} > (select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId ))
</if>
<if test="liquidationDate != null and currentClaimId == null">
and awf.feeDeadlineDate < #{liquidationDate}
</if>
</select>
 
<sql id="RO_ASV_RESPONSIBILITY">
RO_ASV_RESPONSIBILITY as (
select * from asv_responsibility where ro_id = (select ro_id from asv_insurer where insurer_id = #{insurerId})
)
</sql>

Не дай боже у нас CTO такое увидит.

sonac · 17 декабря 2018

Ramil написал 17.12.2018 в 07:20:

Показать содержимое

<select id="getDocument4AdditionalFinesFromMigrationClaim" parameterType="Map" resultMap="additionalFinesClaimListMap">
/* Выбор мгрированных требований по плательщику*/
WITH
<include refid="RO_ASV_RESPONSIBILITY"/>,

claim_migrated_by_insurer(claimId, claimNumber, claimDate, claimActual) AS (
SELECT
PENALTY_ARREARS_REPAYMENT_CLAIM_ID AS claimId,
MIGRATION_NUMBER AS claimNumber,
DATE(MIGRATION_DATE) AS claimDate,
CASE WHEN STATUS_ID = 29 THEN 0 ELSE 1 END AS claimActual
FROM ASV_PENALTY_ARREARS_REPAYMENT_CLAIM
WHERE 1 = 1
and INSURER_ID = #{insurerId}
AND (STATUS_ID IN (28, 30, 31)
<if test="currentClaimId != null">
OR STATUS_ID = 29
</if>)
AND PENALTY_PREREQUISITE_TYPE_ID = 8
)

, <include refid="getAllAdditionalFinesByInsurer"/>

/*Выбор всех актуальных сумм требований без учета компенсаций*/
, all_amounts_for_claims AS (
SELECT
ra.*,
claims.*,
rd.R_DOCUMENT_ID AS responsibilityDocumentId
FROM ASV_RESPONSIBILITY_AMOUNT ra
JOIN RO_ASV_RESPONSIBILITY r ON r.R_ID = ra.R_ID and r.RO_ID = ra.RO_ID
JOIN ASV_RESPONSIBILITY_DOCUMENT rd
ON rd.R_DOCUMENT_ID = r.R_DOCUMENT_ID AND rd.R_DOCUMENT_PREREQUISITE_TYPE_ID = 5
JOIN claim_migrated_by_insurer claims ON claims.claimId = rd.R_DOCUMENT_PREREQUISITE_ID
WHERE ra.OPERATION_TYPE IN (1, 2, 3, 4, 5, 9) AND RA.R_AMOUNT_ACTUAL_STATE = claims.claimActual
AND ra.R_AMOUNT_ALREADY_CHECKED != 1
and ra.R_AMOUNT_FEE_OVERDUE != 2
)
, all_paid_amount_by_chain_after_claim as (
select claims.* from all_amounts_for_claims claims
union all
select
ra.*
, claims.claimId
, claims.claimNumber
, claims.claimDate
, claims.claimActual
, claims.responsibilityDocumentId
from ASV_RESPONSIBILITY_AMOUNT ra
join all_amounts_for_claims claims on claims.R_AMOUNT_CHAIN_UNIQUE_ID = ra.R_AMOUNT_CHAIN_UNIQUE_ID and claims.RO_ID = ra.RO_ID
and claims.OPERATION_TYPE IN (1,2)
where ra.OPERATION_TYPE in (3, 4, 5) and ra.R_ID > claims.R_ID
AND ra.R_AMOUNT_ALREADY_CHECKED != 1
)
, amounts_for_claims_without_new_payments as (
select
ac.*
, coalesce((select max(mf
.PENALTY_MIGRATION_FINES_DATE) from ASV_PENALTY_MIGRATION_FINES mf
where exists ( select
KBK_ID as id
from ASV_KBK
where
KBK_MARKER_ID = ac.KBK_MARKER_ID and
KBK_GROUP_ID = 2 and
KBK_ID in (select KBK_ID from ASV_KBK_INSURER_CATEGORY where CATEGORY_ID = #{insurerCategoryId}) and
KBK_DISABLED = 0 and
KBK_ASV_ACTUAL = 1 and
KBK_START_DATE <= current date and
(KBK_END_DATE is null or KBK_END_DATE > current date)
and KBK_ID = mf.KBK_ID
)
and mf.INSURER_ID = #{insurerId}), claimDate) as initialStartCalcDate
from all_paid_amount_by_chain_after_claim ac
where (R_PAYMENT_CONSOLIDATED_DATE IS NULL OR (
R_PAYMENT_CONSOLIDATED_DATE <= coalesce((select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f
.R_AMOUNT_CHAIN_UNIQUE_ID = AC.R_AMOUNT_CHAIN_UNIQUE_ID), coalesce((select max(mf
.PENALTY_MIGRATION_FINES_DATE) from ASV_PENALTY_MIGRATION_FINES mf
where exists ( select
KBK_ID as id
from ASV_KBK
where
KBK_MARKER_ID = ac.KBK_MARKER_ID and
KBK_GROUP_ID = 2 and
KBK_ID in (select KBK_ID from ASV_KBK_INSURER_CATEGORY where CATEGORY_ID = #{insurerCategoryId}) and
KBK_DISABLED = 0 and
KBK_ASV_ACTUAL = 1 and
KBK_START_DATE <= current date and
(KBK_END_DATE is null or KBK_END_DATE > current date)
and KBK_ID = mf.KBK_ID
)
and mf.INSURER_ID = #{insurerId}), claimDate))
)

)
)

/* Выбор взносов, погашений, отмен погашений*/
, amounts_without_fines AS (
SELECT
ra.claimId,
ra.claimNumber,
ra.claimDate,
ra.claimActual,
ra.R_ID as responsibilityId,
ra.r_amount_id AS responsibilityAmountId,
ra.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID AS srcResponsibilityAmountId,
ra.R_AMOUNT_SRC_REPAYMENT_R_AMOUNT_ID AS srcRepaymentAmountId,
ra.R_AMOUNT_TOTAL_SUM AS totalSum,
ra.R_AMOUNT_PAID_SUM AS paidSum,
ra.OPERATION_TYPE AS operationType,
ra.R_AMOUNT_CHAIN_UNIQUE_ID AS chainUid,
ra.KBK_ID AS kbkId,
ra.KBK_GROUP_ID AS kbkGroupId,
ra.KBK_MARKER_ID AS kbkMarkerId,
ra.R_AMOUNT_FEE_DEADLINE_DATE AS feeDeadlineDate,
ra.responsibilityDocumentId AS responsibilityDocumentId,
ra.initialStartCalcDate AS initialStartCalcDate
FROM amounts_for_claims_without_new_payments ra
WHERE (ra.OPERATION_TYPE IN (1, 2) AND KBK_GROUP_ID = 1)
OR ra.OPERATION_TYPE IN (3, 4, 5)
)

/* Выбор всех непогашенных взносов*/
, non_payed_amounts (responsibilityAmountId, arrearsSum) AS (
SELECT responsibilityAmountId, arrearsSum
FROM (
SELECT
am.responsibilityAmountId AS responsibilityAmountId,
sum(coalesce(am2.totalSum, 0) - coalesce(am2.paidSum, 0)) AS arrearsSum
FROM amounts_without_fines am
LEFT JOIN amounts_without_fines am2 ON am.chainUid = am2.chainUid
WHERE am.operationType IN (1, 2)
GROUP BY am.responsibilityAmountId
)
WHERE arrearsSum > 0
)

, select_with_bankrupt_id as(
select
awf.*
, (select max(br.BANKRUPT_ID)from ASV_BANKRUPT_RTK_RESPONSIBILITY rtk
JOIN ASV_BANKRUPT_RTK br on br.BANKRUPT_RTK_ID = rtk.BANKRUPT_RTK_ID
JOIN ASV_BANKRUPT b on b.BANKRUPT_ID = br.BANKRUPT_ID and b.STATUS_ID not in (159, 162)
where rtk.R_AMOUNT_ID = awf.responsibilityAmountId) as ra_bankruptId
from amounts_without_fines awf
)
, select_with_all_bankrupt as(
select
awf.*
, case when ra_bankruptId is not null then
(select max(br.BANKRUPT_RTK_CREATED_DATE) from ASV_BANKRUPT_RTK br
where awf.ra_bankruptId = br.BANKRUPT_ID)
else null end as ra_bankruptRtkCalcDate
, case when awf.ra_bankruptId is null then 0 else 1 end as ra_isBankruptcyAmount
from select_with_bankrupt_id awf
)

select
-awf.responsibilityDocumentId AS id
, awf.claimId AS documentPrerequisiteId
, awf.responsibilityId AS responsibilityId
, awf.responsibilityDocumentId AS responsibilityDocumentId
, awf.claimActual AS actual
, 5 AS DOCUMENT_PREREQUISITE_TYPE_ID
, awf.claimNumber AS DOCUMENT_NUMBER
, awf.claimDate AS DOCUMENT_DATE
/*, awf.operationType AS operationType*/
, awf.responsibilityAmountId AS ra_rid
, awf.responsibilityAmountId AS ra_responsibilityAmountId
, awf.claimId AS ra_parentId
, awf.responsibilityId AS ra_responsibilityId
, awf.claimActual AS ra_parentActual
, awf.totalSum as ra_totalSum

, awf.kbkId AS ra_kbkId
, awf.kbkGroupId AS ra_kbkGroup
, awf.kbkMarkerId AS ra_kbkMarker
, kbk.KBK_CODE AS ra_kbkCode
, 4 AS ra_additionalFineType
, awf.feeDeadlineDate AS ra_feeDeadlineDate
, awf.initialStartCalcDate AS ra_initialStartCalcDate
, npa.arrearsSum AS ra_arrearsSum

, (select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId ) as ra_fineCalcdate

, (select totalFinesSum from get_total_previous_fine_sum t where awf.responsibilityAmountId = t
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID) AS ra_totalPreviousFinesSum
<choose>
<when test="currentClaimId != null">
,coalesce((select totalFinesSum from get_total_current_fine_sum t where awf.responsibilityAmountId = t
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID), 0) AS ra_fineSum
</when>
<otherwise>
,0 as ra_fineSum
</otherwise>
</choose>

, case when #{insurerUnregistered} = 1 then 1 else 0 end AS ra_isLiquidationInsurer
, CASE when exists(select 1 from asv_delay_schedule ads
JOIN asv_delay ad ON ad.DELAY_ID = ads.DELAY_ID
where ads.R_AMOUNT_ID = awf.responsibilityAmountId AND ad.DELAY_STATUS IN (6, 7)) then 1
ELSE 0
END AS ra_isDelayAmount
, awf.chainUid AS ra_chainUin
, awf.ra_bankruptId
, awf.ra_isBankruptcyAmount
, awf.ra_bankruptRtkCalcDate
, cast(#{liquidationDate, jdbcType=DATE} as DATE) as ra_liquidationDate
from select_with_all_bankrupt awf
join non_payed_amounts npa on awf.responsibilityAmountId = npa.responsibilityAmountId
LEFT JOIN ASV_KBK kbk ON kbk.KBK_ID = awf.kbkId

where not exists (
select * from ASV_PENALTY_ARREARS_REPAYMENT_CLAIM parc
join ASV_RESPONSIBILITY_DOCUMENT rd on rd.R_DOCUMENT_PREREQUISITE_ID = parc.PENALTY_ARREARS_REPAYMENT_CLAIM_ID and rd.R_DOCUMENT_PREREQUISITE_TYPE_ID = 5
join RO_ASV_RESPONSIBILITY r on r.R_DOCUMENT_ID = rd.R_DOCUMENT_ID
join ASV_RESPONSIBILITY_AMOUNT ra on ra.R_ID = r.R_ID and ra.RO_ID = r.RO_ID and ra.OPERATION_TYPE = 9
and ra.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId
where parc.PENALTY_PREREQUISITE_TYPE_ID = 4 and parc.STATUS_ID not in (28, 29, 30, 31)
<if test="currentClaimId != null">
and parc.PENALTY_ARREARS_REPAYMENT_CLAIM_ID != #{currentClaimId}
</if>
) and (awf.ra_bankruptRtkCalcDate >= (select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId ) or awf.ra_bankruptId is null)
or (select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId ) is null
<if test="liquidationDate != null">
or (#{liquidationDate} > (select f.R_AMOUNT_FINE_CALC_DATE from get_max_fines_date f where f
.R_AMOUNT_SRC_RESPONSIBILITY_R_AMOUNT_ID = awf.responsibilityAmountId ))
</if>
<if test="liquidationDate != null and currentClaimId == null">
and awf.feeDeadlineDate < #{liquidationDate}
</if>
</select>
 
<sql id="RO_ASV_RESPONSIBILITY">
RO_ASV_RESPONSIBILITY as (
select * from asv_responsibility where ro_id = (select ro_id from asv_insurer where insurer_id = #{insurerId})
)
</sql>

позапускай запросы в базу, посмотри на каком именно месте падает, так сложно сказать

Войти

Сейчас на странице Всего пользователей: 0 (0 пользователей, 0 гостей)

Программирование, т. 8

315 пользователей проголосовало

Рекомендованные сообщения

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение

Поделиться сообщением

Ссылка на сообщение