Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE PROCEDURE dev_NachislenieH(HsID k_House.ID%TYPE,
- Data_Start DATE,
- Prm NUMBER) AS
- finmonth DATE;
- data_end_nach DATE;
- data_beg_nach DATE;
- data_accountchange DATE;
- data_accountchange1 DATE;
- data_accountchange2 DATE;
- date_tmp DATE;
- TYPE KoefsInTimeRange IS RECORD(
- dayAnchor DATE, -- Указатель на день месяца, показывает промежуток с этого дня, и до следуйщего указателя
- caculatedKoeficient NUMBER(10, 5) -- Коефициент работающий в этот промежуток времени
- );
- TYPE tMonthChanges IS TABLE OF KoefsInTimeRange INDEX BY BINARY_INTEGER;
- monthchanges tmonthchanges;
- TYPE TAccountSpread IS RECORD(
- koeff NUMBER);
- TYPE tabAccountSpread IS TABLE OF TAccountSpread INDEX BY BINARY_INTEGER;
- accountSpreadTable tabAccountSpread;
- TYPE TAcc IS RECORD(
- acc VARCHAR2(12),
- ID VARCHAR2(12));
- TYPE TAccountsWithIndic IS TABLE OF TAcc INDEX BY BINARY_INTEGER;
- AccountsWithIndic TAccountsWithIndic;
- housevolume NUMBER(20);
- month_part NUMBER;
- summ_nach NUMBER;
- volume_nach NUMBER;
- tmp_numb NUMBER;
- AvgPeoplsCount NUMBER;
- curr_tarif NUMBER;
- Area_Izl NUMBER;
- commonKoef NUMBER := 0;
- spread_amount NUMBER;
- spread_tmp NUMBER;
- ActiveServiceDays NUMBER;
- all_norm NUMBER;
- avg_koeff NUMBER;
- avg_norm NUMBER;
- volume_social_nach NUMBER;
- normno BINARY_INTEGER;
- serviceno BINARY_INTEGER;
- dayinmonthnach INTEGER;
- -- Услуги.
- TYPE TService IS RECORD(
- Formula spr_Services.Formula%TYPE,
- Params n_AccountService.Params%TYPE,
- Norm_ID n_AccountService.Norm_ID%TYPE,
- State_Srv n_HouseService.State_Srv%TYPE);
- TYPE tservices IS TABLE OF tservice INDEX BY BINARY_INTEGER;
- Services TServices;
- -- Нормы.
- TYPE TNorm IS RECORD(
- norm spr_norms.norm%TYPE,
- norm_social spr_norms.norm_social%TYPE,
- Norm_Slave_Persons spr_Norms.Norm_Slave_Persons%TYPE,
- Period spr_Norms.Period%TYPE);
- TYPE TNorms IS TABLE OF TNorm INDEX BY BINARY_INTEGER;
- Norms TNorms;
- -- Тарифы общие и домовые.
- TYPE TTarif IS RECORD(
- Tarif spr_Tarifs.Tarif%TYPE,
- Dotacya spr_Tarifs.Dotacya%TYPE);
- TYPE TTarifs IS TABLE OF TTarif INDEX BY BINARY_INTEGER;
- tarifs ttarifs;
- TarifsH TTarifs;
- s_ VARCHAR2(10);
- FUNCTION Tarif(ServiceNo BINARY_INTEGER,
- SrvNorm_ID BINARY_INTEGER,
- OnlyHouseTarif BOOLEAN DEFAULT FALSE)
- RETURN spr_Tarifs.Tarif%TYPE -- Для услуги вернуть тариф (домовой или общий).
- AS
- Rslt NUMBER;
- BEGIN
- -- Взять тариф из домовых тарифов.
- BEGIN
- Rslt := TarifsH(ServiceNo * 1000000 + NVL(SrvNorm_ID, 0) * 100).Tarif;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- BEGIN
- IF NOT onlyhousetarif THEN
- Rslt := Tarifs(ServiceNo * 1000000 + NVL(SrvNorm_ID, 0) * 100 /*+ Category*/
- ).Tarif;
- ELSE
- Rslt := 0;
- END IF;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- -- Если тарифа для услуги среди общегородских нет.
- Rslt := 0;
- END;
- END;
- RETURN Rslt;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- RETURN 0;
- END;
- BEGIN
- s_ := ' ';
- housevolume := 0;
- dayinmonthnach := EXTRACT(DAY FROM LAST_DAY(data_beg_nach));
- SELECT FinMonth INTO FinMonth FROM System;
- data_end_nach := LAST_DAY(finmonth) + 1 - 1 / (24 * 60 * 60);
- data_beg_nach := TRUNC(data_start, 'MM');
- DBMS_OUTPUT.put_line(data_beg_nach || '-' || data_end_nach);
- FOR counter IN (SELECT DISTINCT k.*, s.vid
- FROM c_House c1,
- c_House c2,
- k_Counters k,
- spr_counters s
- WHERE c1.Status = 1
- AND c2.House_ID = c1.House_ID
- AND c2.Status = 1
- AND k.ID = c2.counter_id
- AND s.ID = k.counter_id
- AND k.Data BETWEEN Data_Beg_Nach AND Data_End_Nach
- AND c1.House_ID = LPAD(Hsid, 12, '0')) LOOP
- DBMS_OUTPUT.put_line('');
- DBMS_OUTPUT.put_line('домовой счётчик№ ' || counter.id || ' vid=' ||
- counter.vid);
- FOR idict IN (SELECT A.indication,
- a.indication_old,
- a.volume,
- a.data,
- a.counter_id
- FROM n_indications A
- LEFT JOIN n_indications b
- ON (A.counter_id = b.counter_id AND A.DATA < b.DATA)
- WHERE b.counter_id IS NULL
- AND A.counter_id = counter.ID
- AND A.Data BETWEEN Data_Beg_Nach AND Data_End_Nach) LOOP
- DBMS_OUTPUT.put_line(s_ || 'квартирные счётчики: ');
- houseVolume := 0;
- commonKoef := 0;
- FOR apartcounter IN (SELECT DISTINCT acc.id account_id,
- kc.apart_id,
- ni.indication,
- ni.data_ins,
- NVL(ni.indication_old, 0) indication_old,
- CASE
- WHEN ni.vid_old = 1 THEN
- (ni.data_old + 1)
- ELSE
- ni.data_old
- END data_old,
- CASE
- WHEN ni.vid = 1 THEN
- 0
- ELSE
- ni.indication -
- NVL(ni.indication_old, 0)
- END summa,
- ni.vid,
- ni.counter_id
- FROM k_counters kc
- JOIN K_Accounts acc
- ON Acc.Apart_Id = Kc.Apart_Id
- JOIN spr_counters s
- ON s.ID = kc.counter_id
- JOIN a_counters ac
- ON ac.house_counter_id = kc.house_counter_id
- JOIN n_indications ni
- ON ni.counter_id = kc.id
- WHERE ac.house_counter_id = counter.ID
- AND ni.vid = 2
- AND ni.data_ins BETWEEN data_beg_nach AND
- data_end_nach
- ORDER BY ni.vid, ni.counter_id, ni.data_ins) LOOP
- houseVolume := houseVolume + apartcounter.summa;
- -- если есть показания - запоминаем что бы не начислять по норме
- AccountsWithIndic(apartcounter.account_id).id := apartcounter.account_id;
- DBMS_OUTPUT.put_line(s_ || s_ || 'аккаунт№ ' ||
- apartcounter.account_id || ' счетчик№' ||
- apartcounter.counter_id || '[' ||
- apartcounter.data_old || '-' ||
- apartcounter.data_ins || '][' ||
- apartcounter.vid || ']' || ' показания ' ||
- apartcounter.indication_old || '-' ||
- apartcounter.indication || '=' ||
- (apartcounter.summa));
- END LOOP;
- DBMS_OUTPUT.put_line(s_ || 'сумма V по квартирным счетчикам=' ||
- housevolume);
- DBMS_OUTPUT.put_line(s_ || 'V по домовому счётчику =' ||
- idict.volume);
- DBMS_OUTPUT.put_line(s_ || 'Дельта для разброса =' ||
- (idict.volume - housevolume));
- DBMS_OUTPUT.put_line(s_ || 'Список акаунтов для разброса:');
- FOR clnt IN (SELECT DISTINCT ah.Account_ID Account_ID
- FROM c_AccountHouse ah
- WHERE ah.Counter_ID = counter.id
- AND ah.Data =
- (SELECT MAX(Data)
- FROM c_AccountHouse
- WHERE Account_ID = ah.Account_ID
- AND Data <= data_end_nach
- AND Counter_ID = ah.Counter_ID)
- AND ah.status = 1) LOOP
- IF NOT AccountsWithIndic.EXISTS(clnt.account_id) THEN
- -- мы не можем начислить по норме человеку, у которого етсь счетчик и показаниями
- DBMS_OUTPUT.put_line(s_ || 'Account_ID[' || clnt.account_id ||
- '] x ->');
- data_accountchange := data_end_nach;
- AvgPeoplsCount := 0;
- DBMS_OUTPUT.put_line(s_ || s_ || '|---' || 'История людей');
- FOR acounthist IN (SELECT DATA,
- NVL(count_inhabit, 0) count_inhabit,
- NVL(COUNT_WANTING, 0) COUNT_WANTING
- FROM a_accounts
- WHERE account_id = clnt.account_id
- AND DATA BETWEEN data_beg_nach AND
- data_end_nach
- ORDER BY DATA DESC) LOOP
- -- Считаем слюдей в среднемза месяц
- month_part := TRUNC(data_accountchange - CASE
- WHEN data_accountchange = data_end_nach THEN
- acounthist.DATA - 1
- ELSE
- acounthist.DATA
- END);
- DBMS_OUTPUT.put_line(s_ || s_ || '| ' || s_ ||
- '|[Л]Диапазон с ' || acounthist.DATA ||
- ' по ' || data_accountchange || '=' ||
- Month_Part || 'дней / ' ||
- EXTRACT(DAY FROM data_end_nach) || '*' ||
- (acounthist.count_inhabit -
- acounthist.COUNT_WANTING) ||
- '(человеков)' || '=' ||
- TO_CHAR((Month_Part /
- EXTRACT(DAY FROM data_end_nach)) *
- (acounthist.count_inhabit -
- acounthist.COUNT_WANTING),
- '99D99') || ' человек за период ');
- data_accountchange := CASE /*when EXTRACT(DAY from acounthist.DATA) = 1 then acounthist.DATA-1*/
- WHEN EXTRACT(DAY FROM acounthist.DATA) =
- EXTRACT(DAY FROM data_end_nach) THEN
- acounthist.DATA
- ELSE
- acounthist.DATA
- END;
- AvgPeoplsCount := AvgPeoplsCount +
- (Month_Part /
- EXTRACT(DAY FROM data_end_nach)) *
- (acounthist.count_inhabit -
- acounthist.COUNT_WANTING);
- END LOOP; -- Считаем слюдей в среднемза месяц
- FOR iservice IN (SELECT DISTINCT nacc.service_id,
- s.formula,
- DECODE(nacc.service_id,
- 28,
- 1,
- 5,
- 1,
- 6,
- 2,
- 35,
- 2,
- 27,
- 3,
- 44,
- 3,
- 25,
- 4,
- 10,
- 5) vid,
- nacc.state_srv
- -- ,nvl(acc.area_polezn, 0) area_polezn,
- -- nvl(acc.count_inhabit, 0) count_inhabit,
- -- nvl(acc.count_wanting, 0) count_wanting,
- -- acc.area_addsan
- -- ,acc.DATA
- FROM n_accountservice nacc
- JOIN spr_services s
- ON s.ID = nacc.service_id
- JOIN a_accounts acc
- ON acc.account_ID = nacc.account_id
- WHERE nacc.state_srv = 1
- AND DECODE(nacc.service_id,
- 28,
- 1,
- 5,
- 1,
- 6,
- 2,
- 35,
- 2,
- 27,
- 3,
- 44,
- 3,
- 25,
- 4,
- 10,
- 5) = counter.vid
- AND nacc.account_id = clnt.account_id) LOOP
- DBMS_OUTPUT.put_line(s_ || s_ || '|---' || 'История норм');
- tmp_numb := 0;
- -- IF Iservice.formula = 0 THEN -- Услуга не подходит под формулу.
- -- IF iservice.vid = 4 AND nvl(iservice.state_srv,1) = 1 THEN -- АГВ.
- -- normno := nvl(services(Iservice.service_id).norm_id,0);
- -- Volume_Nach := Iservice.Area_Polezn * Norms(NormNo).Norm * Month_Part;
- -- Volume_Social_Nach := Iservice.Area_Polezn * Norms(NormNo).Norm_Social * Month_Part;
- -- IF Norms(NormNo).Period = 1 THEN -- Норма в сутки, а не в месяц.
- -- Volume_Nach := Volume_Nach * DayInMonthNach;
- -- Volume_Social_Nach := Volume_Social_Nach * DayInMonthNach;
- -- END IF;
- -- summ_nach := volume_nach * curr_tarif;
- -- area_izl := iservice.area_polezn - (iservice.count_inhabit*norms(1).norm/*Норма площади на человека*/ + iservice.area_addsan + norms(2).norm/*Норма площади на семью*/);
- -- IF Area_Izl > 0 /*AND Surplus = 'Y'*/ THEN -- Есть излишки по площади и они учитываются.
- -- IF Iservice.Area_Polezn <> 0 THEN
- -- tmp_numb := Summ_Nach*(Area_Izl/Iservice.Area_Polezn);
- -- ELSE
- -- tmp_numb := 0;
- -- END IF;
- -- Summ_Nach := Summ_Nach - tmp_numb + tmp_numb * Norms(21).Norm;
- -- END IF;
- -- END IF;
- -- elsif Iservice.formula = 21 THEN -- Люди - проживающие минус отсутствующие.
- -- volume_nach := greatest(iservice.count_inhabit - iservice.count_wanting,1);
- avg_norm := 0;
- data_accountchange2 := data_end_nach;
- ActiveServiceDays := 0;
- FOR serviceState IN (SELECT n.data, N.State_Srv, N.Service_Id
- FROM n_accountservice n
- WHERE N.Account_Id = clnt.account_id
- AND DECODE(n.service_id,
- 28,
- 1,
- 5,
- 1,
- 6,
- 2,
- 35,
- 2,
- 27,
- 3,
- 44,
- 3,
- 25,
- 4,
- 10,
- 5) = counter.vid
- ORDER BY n.data DESC) LOOP
- date_tmp := serviceState.DATA;
- month_part := TRUNC(CASE
- WHEN EXTRACT(DAY FROM data_accountchange2)=30 THEN
- data_accountchange2+1
- ELSE data_accountchange2 END
- -
- CASE WHEN EXTRACT(DAY FROM date_tmp)=1 THEN
- date_tmp
- ELSE date_tmp END);
- DBMS_OUTPUT.put_line(s_ || s_ || '| ' || s_ ||
- CASE WHEN
- serviceState.State_Srv = 1 THEN
- '\[ON]' ELSE '/[OFF]' END || ' ' ||
- data_accountchange2 || '-' || date_tmp ||
- ' = ' ||' month_part='||month_part);
- IF serviceState.State_Srv = 1 OR 1=1 THEN
- ActiveServiceDays := ActiveServiceDays + month_part;
- --/////////////////////////////////////////////////////////////////////////////////////////
- data_accountchange1 := data_end_nach;
- BEGIN
- FOR normHist IN (SELECT norm, Data, sub
- FROM (SELECT norms.norm, Norms.Data,
- --extract(day from date_tmp) - extract(day from Norms.Data)
- -- + extract(day from data_accountchange2) - extract(day from data_accountchange1)
- CASE WHEN EXTRACT(DAY FROM Norms.Data) > EXTRACT(DAY FROM data_accountchange2) AND EXTRACT(DAY FROM Norms.Data) < EXTRACT(DAY FROM date_tmp) THEN
- EXTRACT(DAY FROM date_tmp)
- END
- sub
- FROM spr_norms norms
- WHERE norms.DATA BETWEEN date_tmp AND
- data_accountchange2
- AND norms.srvnorm_id =
- (SELECT DISTINCT s.norm_id
- FROM n_accountservice s
- WHERE s.account_id =
- clnt.account_id
- AND s.service_id =
- iservice.service_id)
- UNION ALL
- SELECT norms.norm,
- data_beg_nach data,
- 0 sub
- FROM spr_norms norms
- WHERE Norms.Srvnorm_Id =
- (SELECT DISTINCT s.norm_id
- FROM n_accountservice s
- WHERE s.account_id =
- clnt.account_id
- AND s.service_id =
- iservice.service_id)
- AND norms.DATA =
- (SELECT MAX(DATA)
- FROM spr_norms
- WHERE srvnorm_id =
- (SELECT DISTINCT s.norm_id
- FROM n_accountservice s
- WHERE s.account_id =
- clnt.account_id
- AND s.service_id =
- iservice.service_id)))
- ORDER BY Data DESC
- ) LOOP
- -- История норм
- DBMS_OUTPUT.put_line(s_ || s_ || '| ' || s_ ||' date_tmp='||EXTRACT(DAY FROM date_tmp)||' normHist.Data='||EXTRACT(DAY FROM normHist.Data)
- ||' data_accountchange2='|| EXTRACT(DAY FROM data_accountchange2)||' data_accountchange1='||EXTRACT(DAY FROM data_accountchange1)
- ||' sub='||(EXTRACT(DAY FROM date_tmp) - EXTRACT(DAY FROM normHist.Data)
- + EXTRACT(DAY FROM data_accountchange2) - EXTRACT(DAY FROM data_accountchange1)));
- tmp_numb := normHist.norm;
- month_part := TRUNC(data_accountchange1 - CASE
- WHEN data_accountchange1 = data_end_nach THEN
- normHist.DATA - 1
- ELSE
- normHist.DATA
- END);
- all_norm := (Month_Part /
- EXTRACT(DAY FROM data_end_nach)) *
- normHist.norm;
- avg_norm := avg_norm + all_norm;
- DBMS_OUTPUT.put_line(s_ || s_ || '| ' || s_ ||
- '|[Н]Диапазон с ' ||
- normHist.DATA || ' по ' ||
- data_accountchange1 || '=' ||
- Month_Part || 'дней / ' ||
- EXTRACT(DAY FROM data_end_nach) || '*' ||
- (normHist.norm) || '(норма)' || '=' ||
- TO_CHAR(all_norm, '99D99') ||
- ' норма за период ');
- data_accountchange1 := CASE
- WHEN EXTRACT(DAY FROM normHist.DATA) =
- EXTRACT(DAY FROM data_end_nach) THEN
- normHist.DATA
- ELSE
- normHist.DATA
- END;
- END LOOP; -- История норм
- -- END IF;
- END;
- --/////////////////////////////////////////////////////////////////////////////////////////
- --/////////////////////////////////////////////////////////////////////////////////////////
- END IF;
- data_accountchange2 := serviceState.DATA;
- END LOOP;
- avg_koeff := ActiveServiceDays /
- EXTRACT(DAY FROM data_end_nach) * tmp_numb;
- DBMS_OUTPUT.Put_Line(s_ || s_ ||
- '+---Средняя норма за месяц=Дней с включенной услугой(' ||
- ActiveServiceDays || ') / ' ||
- EXTRACT(DAY FROM data_end_nach) ||
- '*Коефициент(' ||
- TO_CHAR(avg_norm, '99D999') || ')=' ||
- TO_CHAR(avg_koeff, '99D99') ||
- ' Людей в среднем за месяц-' ||
- TO_CHAR(AvgPeoplsCount, '99D99') ||
- 'человек * ' ||
- TO_CHAR(avg_koeff, '99D99') || ' НОРМА=' ||
- TO_CHAR(AvgPeoplsCount * avg_koeff,
- '999D999'));
- accountSpreadTable(TO_NUMBER(clnt.account_id)).koeff := AvgPeoplsCount *
- avg_koeff;
- commonKoef := commonKoef + AvgPeoplsCount * avg_koeff;
- IF commonKoef = 0 THEN
- commonKoef := 1;
- SYS.DBMS_OUTPUT.Put_Line('[ERROR]: commonKoef = 0');
- END IF;
- END LOOP;
- END IF; -- мы не можем начислить по норме человеку, у которого етсь счетчик и показаниями
- END LOOP;
- DBMS_OUTPUT.put_line(s_ || s_ || s_ ||
- 'Коефициент = Объем разброса/сумму норм = ' ||
- (idict.volume - housevolume) || ' /' ||
- TO_CHAR(commonKoef, '999D999') || '=' ||
- TO_CHAR(((idict.volume - housevolume) /
- commonKoef),
- '99D999'));
- spread_amount := 0;
- FOR indx IN accountSpreadTable.FIRST .. accountSpreadTable.LAST LOOP
- IF accountSpreadTable.EXISTS(indx) THEN
- spread_tmp := accountSpreadTable(indx)
- .koeff * ((idict.volume - housevolume) / commonKoef);
- DBMS_OUTPUT.Put_Line(s_ || s_ || '|' || indx || '=' ||
- TO_CHAR(accountSpreadTable(indx).koeff,
- '99D999') || ' *' ||
- TO_CHAR(((idict.volume - housevolume) /
- commonKoef),
- '9D999') || ' =' ||
- TO_CHAR(spread_tmp, '99D999'));
- spread_amount := spread_amount + spread_tmp;
- END IF;
- IF indx = accountSpreadTable.LAST THEN
- DBMS_OUTPUT.Put_Line(s_ || s_ || '+-----------------------' ||
- TO_CHAR(spread_amount, '999D999'));
- END IF;
- END LOOP;
- accountSpreadTable.DELETE();
- END LOOP;
- END LOOP;
- END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement