Здравствуйте, в коде программы есть метод на C#:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
public void ProcOrganisations()
{
using (var connection = m_dbSrc.GetConnection())
{
using (var transaction = connection.SafeBeginTransaction())
{
using (var command = dbLayer.GetCommand(@"""ProcOrganisations""", connection, transaction))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 3600;
try
{
command.SafeExecuteNonQuery();
transaction.SafeCommit();
}
catch (Exception exception)
{
Lib.HandleException(exception);
transaction.SafeRollback();
}
}
}
}
}
Тест процедуры ProcOrganisations:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
CREATE OR REPLACE FUNCTION "ProcOrganisations"()
RETURNS VOID AS $$
DECLARE
csmRow RECORD;
BEGIN
FOR csmRow IN (SELECT CSM.CSM_CODE
FROM CSM
INNER JOIN (SELECT CSM_CODE AS FICT_CSM_CODE
FROM CSM
WHERE DELETE_DATE IS NULL) FICT_CSM
ON FICT_CSM.FICT_CSM_CODE=CSM.CSM_CODE
ORDER BY PARENT_CODE DESC, CSM_NAME) LOOP
PERFORM "UpdateUsageReports"(csmRow.csm_code);<---вызов процедуры "UpdateUsageReports"
PERFORM "SetPastBills"(csmRow.csm_code);<-----вызов процедуры "SetPastBills"
END LOOP;
END;
$$ LANGUAGE plpgsql;
Тест процедуры "UpdateUsageReports":
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
CREATE OR REPLACE FUNCTION "UpdateUsageReports"(orgId INTEGER)
RETURNS VOID AS $$
DECLARE
csmRow RECORD;
BEGIN
FOR csmRow IN (select aaa.*, bill.bill_id
from ( select sum(MARK_QTY) as USED_QTY, max(REPORT_DATE) as REPORT_DATE, YEAR, MONTH
from CSM_USAGE_REPORT_ACTUAL
where CSM_CODE=orgId
group by YEAR, MONTH
) aaa
inner join bill on bill.csm_code=orgId and bill.year=aaa.year and bill.month=aaa.month
where bill.item_count != aaa.used_qty and bill.type_id=1 and node_id is null) LOOP
update BILL set ITEM_COUNT=csmRow.USED_QTY, ITEM_RATE=0, AMOUNT=0, OPER_DATE=csmRow.REPORT_DATE where BILL_ID=csmRow.bill_id;
END LOOP;
FOR csmRow IN (select sum(MARK_QTY) as USED_QTY, REPORT_DATE, YEAR, MONTH
from CSM_USAGE_REPORT_ACTUAL
where CSM_CODE=orgId and (MONTH not in (select CSM_USAGE_REPORT_ACTUAL.MONTH
from CSM_USAGE_REPORT_ACTUAL
inner join BILL on CSM_USAGE_REPORT_ACTUAL.YEAR = BILL.YEAR and CSM_USAGE_REPORT_ACTUAL.MONTH = BILL.MONTH and
CSM_USAGE_REPORT_ACTUAL.CSM_CODE = BILL.CSM_CODE
where CSM_USAGE_REPORT_ACTUAL.CSM_CODE=orgId) or
YEAR not in (select CSM_USAGE_REPORT_ACTUAL.YEAR
from CSM_USAGE_REPORT_ACTUAL
inner join BILL on CSM_USAGE_REPORT_ACTUAL.YEAR = BILL.YEAR and CSM_USAGE_REPORT_ACTUAL.MONTH = BILL.MONTH and
CSM_USAGE_REPORT_ACTUAL.CSM_CODE = BILL.CSM_CODE
where CSM_USAGE_REPORT_ACTUAL.CSM_CODE=orgId))
group by REPORT_DATE, YEAR, MONTH
order by REPORT_DATE) LOOP
insert into BILL
(TYPE_ID, ITEM_COUNT, ITEM_RATE, AMOUNT, ITEM_COUNT_REM, YEAR, MONTH, CSM_CODE, OPER_DATE)
values (1, csmRow.USED_QTY, 0, 0, 0, csmRow.year, csmRow.month, orgId, csmRow.report_date);
END LOOP;
delete from bill
where bill_id not in ( select distinct bill_id
from bill
inner join CSM_USAGE_REPORT_ACTUAL on bill.csm_code=CSM_USAGE_REPORT_ACTUAL.csm_code and
bill.year=CSM_USAGE_REPORT_ACTUAL.year and
bill.month=CSM_USAGE_REPORT_ACTUAL.month
) and type_id=1 and node_id is null;
END;
$$ LANGUAGE plpgsql;
Тест процедуры "SetPastBills":
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
CREATE OR REPLACE FUNCTION "SetPastBills"(orgId INTEGER)
RETURNS VOID AS $$
DECLARE
csmRow RECORD;
nMarkUsed bigint;
nItemCount bigint;
BEGIN
nMarkUsed=(select coalesce(SUM(ITEM_COUNT), 0)
from BILL
where CSM_CODE=orgId and TYPE_ID=1 and NODE_ID is null);
FOR csmRow IN (select aaa.*
from (select BILL_ID, OPER_DATE, coalesce(sum(END_NO - START_NO + 1), 0) as QTY,
case when OPER_DATE < current_date - 90 * interval '1 day' then 1 else 0 end as OVERDUE
from BILL
inner join MARK_DOC on BILL.SHIPMENT_ID=MARK_DOC.MDOC_ID
inner join CSM_INTERVAL on CSM_INTERVAL.MDOC_ID=BILL.SHIPMENT_ID
where NODE_ID is NULL and CSM_INTERVAL.CSM_CODE=orgId
group by bill_id, oper_date) aaa
order by OPER_DATE) LOOP
nItemCount = csmRow.QTY;
IF (nMarkUsed > nItemCount) THEN
nMarkUsed = nMarkUsed - nItemCount;
ELSE
nItemCount = nItemCount - nMarkUsed;
nMarkUsed = 0;
END IF;
IF (nMarkUsed <= 0 and nItemCount > 0 and csmRow.OVERDUE = 1) THEN
update BILL set TYPE_ID=2, ITEM_COUNT=nItemCount,AMOUNT=ITEM_RATE*nItemCount where BILL_ID=csmRow.bill_id;
ELSE
update BILL set TYPE_ID=NULL where BILL_ID=csmRow.bill_id;
END IF ;
END LOOP;
END;
$$ LANGUAGE plpgsql;
Выполнение на сервере процедуры "ProcOrganisations" осуществляется без ошибок, а в программе с ошибкой:
Backend sent unrecognized response type: P
А
иногда перед ней возникает следующее диагностическое сообщение (я запуская программу из среды разработки VS 2008) приведенное на рисунке.
Вопрос: как мне исправить данную ошибку?
Может попробовать оптимизировать процедуры?
