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();
                        }
                    }
                }
            }
	    }

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;

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;

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;

CREATE OR REPLACE FUNCTION "ProcOrganisations"()
RETURNS VOID AS $$
DECLARE
    _array1 int[];
	csmRow RECORD;
    csmRow1 RECORD;
    nMarkUsed bigint;
    nItemCount bigint;
BEGIN
    _array1=ARRAY(SELECT CSM.CSM_CODE
				 FROM CSM
                 WHERE DELETE_DATE IS NULL);

    FOR csmRow IN (SELECT (unnest(_array1)) as csm_code) LOOP

        --PERFORM "UpdateUsageReports"(csmRow.csm_code);
        update BILL as b  set ITEM_COUNT=sq.USED_QTY, ITEM_RATE=0, AMOUNT=0, OPER_DATE=sq.REPORT_DATE 
        from (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=csmRow.csm_code
                     group by YEAR, MONTH
                    ) aaa
              inner join bill on bill.csm_code=csmRow.csm_code 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) as sq
         where b.BILL_ID=sq.bill_id;

        insert into BILL (TYPE_ID, ITEM_COUNT, ITEM_RATE, AMOUNT, ITEM_COUNT_REM, YEAR, MONTH, CSM_CODE, OPER_DATE)
		select 1, sum(MARK_QTY) as USED_QTY, 0, 0, 0, YEAR, MONTH, csmRow.csm_code, REPORT_DATE 
	    from CSM_USAGE_REPORT_ACTUAL
        where CSM_CODE=csmRow.csm_code 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=csmRow.csm_code) 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=csmRow.csm_code))
         group by REPORT_DATE, YEAR, MONTH
         order by REPORT_DATE;

        --PERFORM "SetPastBills"(csmRow.csm_code);
        nMarkUsed=(select coalesce(SUM(ITEM_COUNT), 0)
		            from BILL
		            where CSM_CODE=csmRow.csm_code and TYPE_ID=1 and NODE_ID is null);

        FOR csmRow1 IN (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=csmRow.csm_code
                          group by bill_id, oper_date order by OPER_DATE) LOOP

           nItemCount = csmRow1.QTY;

          IF (nMarkUsed > nItemCount) THEN
             nMarkUsed = nMarkUsed - nItemCount;
          ELSE
             nItemCount = nItemCount - nMarkUsed;
	           nMarkUsed = 0;
          END IF;

          IF (nMarkUsed <= 0 and nItemCount > 0 and csmRow1.OVERDUE = 1) THEN
               update BILL set TYPE_ID=2, ITEM_COUNT=nItemCount,AMOUNT=ITEM_RATE*nItemCount where BILL_ID=csmRow1.bill_id;
          ELSE
               update BILL set TYPE_ID=NULL where BILL_ID=csmRow1.bill_id;
          END IF ;
        END LOOP;
    END LOOP;
	
	
	delete from bill
    where bill_id not in ( select distinct bill_id
                            from bill as b
			                inner join CSM_USAGE_REPORT_ACTUAL on b.csm_code=CSM_USAGE_REPORT_ACTUAL.csm_code and
                                                                  b.year=CSM_USAGE_REPORT_ACTUAL.year and
                                                                  b.month=CSM_USAGE_REPORT_ACTUAL.month
     ) and type_id=1 and node_id is null;
END;

$$ LANGUAGE plpgsql;