Скорая помощь. Полезные запросы — различия между версиями

select SC.ID, SC.pass_date as "Передано с ЦДС", SC.get_date as "Принято на ПС",  S.name as "Подстанция", MIS.Get_EMPFIO(SC.pass_emp_id) as "Принял сотр", sc.last_date_change as "Изм. записи в БД"
, TC.pass_date as "Передан бригаде", MIS.Get_EMPFIO(TC.pass_emp_id) as "Передал сотр", TC.moveto_date as "Выезд бригады", TC.last_date_change as "Изм. записи в БД"
from MIS.STATION_CALL SC, mis.station S, MIS.TEAM_CALL TC, mis.emergency_call EC
where SC.call_id=EC.id and SC.station_id=S.ID and TC.station_call_id=SC.id 
and trunc(EC.regist_date) = to_date('12.09.2012', 'DD.MM.YYYY') and EC.num='985'

ALTER TABLE MIS.EMERGENCY_CALL 
 ADD (
  INSUR_NUM1 VARCHAR2 (30),
  INSUR_COMPANY_ID1 VARCHAR2 (15),
  INSUR_COMPANY1 VARCHAR2 (100),
  COMPANY_CALL1 VARCHAR2 (100)
 )
/
COMMENT ON COLUMN MIS.EMERGENCY_CALL.INSUR_NUM1 IS '!Страховой полис Номер'
/
COMMENT ON COLUMN MIS.EMERGENCY_CALL.INSUR_COMPANY_ID1 IS '!Страховая компания ссылка. COMPANY_INSUR.ID'
/
COMMENT ON COLUMN MIS.EMERGENCY_CALL.INSUR_COMPANY1 IS '!Страховая компания не из справочника'
/
COMMENT ON COLUMN MIS.EMERGENCY_CALL.COMPANY_CALL1 IS '!Вызывающая компания'
/

-- переносим данные из старых колонок в новые
update MIS.EMERGENCY_CALL set
 INSUR_NUM1=INSUR_NUM,
 INSUR_COMPANY_ID1=INSUR_COMPANY_ID,
 INSUR_COMPANY1=INSUR_COMPANY,
 COMPANY_CALL1=COMPANY_CALL
 where INSUR_NUM is not null or INSUR_COMPANY_ID is not null or INSUR_COMPANY is not null or COMPANY_CALL is not null;
Commit;

-- удаляем старые
ALTER TABLE MIS.EMERGENCY_CALL
DROP COLUMN INSUR_NUM
/
ALTER TABLE MIS.EMERGENCY_CALL
DROP COLUMN INSUR_COMPANY_ID
/
ALTER TABLE MIS.EMERGENCY_CALL
DROP COLUMN INSUR_COMPANY
/
ALTER TABLE MIS.EMERGENCY_CALL
DROP COLUMN COMPANY_CALL
/

-- переименовываем новые колонки
ALTER TABLE MIS.EMERGENCY_CALL 
RENAME COLUMN INSUR_NUM1 TO INSUR_NUM
/
ALTER TABLE MIS.EMERGENCY_CALL 
RENAME COLUMN INSUR_COMPANY_ID1 TO INSUR_COMPANY_ID
/
ALTER TABLE MIS.EMERGENCY_CALL 
RENAME COLUMN INSUR_COMPANY1 TO INSUR_COMPANY
/
ALTER TABLE MIS.EMERGENCY_CALL 
RENAME COLUMN COMPANY_CALL1 TO COMPANY_CALL
/

SELECT decode(BT.CALL_ID,NULL,'', ''||EC.num||'  (Дата: '||to_char(EC.regist_date, 'DD.MM.YYYY HH24:MM')||')') AS "Вызов №"
, ST.name AS "Передача на ПС", ST.db_link AS "Синоним ПС" 
, BT.LAST_TRY_DATE AS "Последн попытка" -- передачи на ПС"
, BT.REGIST_DATE AS "Первая попытка"--"Дата добавления информации на ЦДС"
, BT.ACTION AS "Действие"
, BT.TABLE_NAME "Имя таблицы"
, BT.ERROR_TEXT AS "Текст ошибки", BT.STEP AS "Процедура"
, BT.ID AS "Buffer_transfer.ID"
, BT.KEY_FIELD_VALUE "Значение ID табл."
FROM(
SELECT b.*, bl.*
, CASE WHEN b.TABLE_NAME = 'STATION_CALL' THEN
                                (SELECT STATION_ID
                                   FROM MIS.STATION_CALL SC
                                  WHERE B.KEY_FIELD_VALUE = SC.ID)
       WHEN b.TABLE_NAME = 'EMERGENCY_CALL' THEN
                                MIS.PKG_REPLICATION.GET_CALL_STATION(B.KEY_FIELD_VALUE)
       WHEN b.TABLE_NAME = 'TEAM_CALL' THEN
                                 (SELECT STATION_ID
                                   FROM MIS.STATION_CALL SC, MIS.STATION S, MIS.TEAM_CALL TC
                                  WHERE SC.CALL_ID = TC.CALL_ID AND TC.ID = B.KEY_FIELD_VALUE
                                   AND SC.STATE_ID = MIS.CN_CALL_PASS_STATE_PASSED AND SC.STATION_ID=S.ID)
       WHEN b.TABLE_NAME = 'CALL_ADDR' THEN
                                 (SELECT STATION_ID
                                   FROM MIS.STATION_CALL SC, MIS.STATION S, MIS.CALL_ADDR CA
                                  WHERE CA.call_id=SC.CALL_ID AND CA.call_id=B.KEY_FIELD_VALUE
                                   AND SC.cancel_emp_id IS NULL AND RowNum=1)
  END STATION_ID 
, CASE WHEN b.TABLE_NAME = 'STATION_CALL' THEN
                                (SELECT SC.call_id
                                   FROM MIS.STATION_CALL SC
                                  WHERE B.KEY_FIELD_VALUE = SC.ID)
       WHEN b.TABLE_NAME = 'EMERGENCY_CALL' THEN
                                (B.KEY_FIELD_VALUE)
       WHEN b.TABLE_NAME = 'TEAM_CALL' THEN
                                 (SELECT SC.call_id
                                   FROM MIS.STATION_CALL SC, MIS.STATION S, MIS.TEAM_CALL TC
                                  WHERE SC.CALL_ID = TC.CALL_ID AND TC.ID = B.KEY_FIELD_VALUE
                                   AND SC.STATE_ID = MIS.CN_CALL_PASS_STATE_PASSED AND SC.STATION_ID=S.ID)
       WHEN b.TABLE_NAME = 'CALL_ADDR' THEN
                                 (B.KEY_FIELD_VALUE)
  END CALL_ID   
FROM mis.buffer_transfer b, mis.buffer_transfer_log bl 
WHERE bl.buffer_transfer_id=b.id AND trunc(b.regist_date) >= (trunc(sysdate)-1)
ORDER BY b.regist_date DESC 
) BT, MIS.station ST, MIS.emergency_call EC
WHERE BT.STATION_ID=ST.id(+) AND BT.CALL_ID=EC.id(+)

SELECT decode(a.sucess_date,NULL,'', trunc((a.sucess_date-a.insert_date)*24)||'ч : '
||trunc(((a.sucess_date-a.insert_date)*24 - trunc((a.sucess_date-a.insert_date)*24))*60)|| 'мин ') AS "Успех реплик."
, decode(a.sucess_date,NULL, trunc((nvl(a.sucess_date,sysdate)-a.insert_date)*24)||'ч : ' 
||trunc(((nvl(a.sucess_date,sysdate)-a.insert_date)*24 - trunc((nvl(a.sucess_date,sysdate)-a.insert_date)*24))*60)|| 'мин '
, NULL) AS "Неудача реплик."
, CASE WHEN a.TABLE_NAME = 'EMERGENCY_CALL' AND a.sucess_date <> NULL THEN
    (SELECT name FROM mis.station WHERE ID = MIS.PKG_REPLICATION.GET_CALL_STATION(a.KEY_FIELD_VALUE))
  END AS "Переданы на ПС"
, a.insert_date, a.repl_date, a.sucess_date,
       a.action, a.TABLE_NAME, a.key_field, a.key_field_value,
       a.error_text
  FROM mis.buffer_transfer_errlog a WHERE a.insert_date >= trunc(sysdate)
  ORDER BY nvl(a.sucess_date,sysdate)-a.insert_date DESC, a.insert_date DESC

DECLARE
    cursor cTrig IS SELECT  owner||'.'||object_name object_name 
                FROM   dba_objects 
                 WHERE object_type = 'TRIGGER'
                 AND (object_name LIKE 'PDOC$REPLICATION'
                      OR object_name LIKE 'PINFO$REPLICATION'
                      OR object_name LIKE 'PEOPLE_ADDR$REPLICATION'
                      OR object_name LIKE 'PEOPLE_ER$REPLICATION'
                      OR object_name LIKE 'PEOPLE$REPLICATION'); 
    nPeopleID NUMBER;
BEGIN
    nPeopleID:=4886802;
    FOR c IN (SELECT ec.people_age_y, ec.people_age_m, pe.people_id, ec.id, p.SEX
                FROM mis.emergency_call ec, mis.people_er pe, mis.people p
                WHERE ec.people_id = pe.id AND pe.people_id =nPeopleID
                AND pe.people_id=p.id
                and pe.id <> 10809100 and pe.id <> 6936300) -- Эти ID прописал для исключения их случайного удаления, т.к. на них апдэйтятся все удаляемые пиплы
    loop
        IF nvl(c.people_age_y,0) < 1 AND nvl(c.people_age_m,0) < 1 THEN -- Если в вызове не установлен возраст, то обновляем его параметрами пипла
            FOR c_year IN (SELECT ec.ID,  --ec.*, p.*, 
                         decode(trunc(ec.regist_date - nvl(p.birth,ec.regist_date)),ec.people_age_y
                        , trunc((ec.regist_date-nvl(p.birth,ec.regist_date))/365)) AS YEAR
                        , decode(trunc(ec.regist_date - nvl(p.birth,ec.regist_date)),ec.people_age_m
                        , trunc(((ec.regist_date-nvl(p.birth,ec.regist_date))/365 - trunc((ec.regist_date-nvl(p.birth,ec.regist_date))/365))*12)) AS Mouth
                        , p.SEX
 
                        FROM mis.emergency_call ec, mis.people_er pe, mis.people p 
                        WHERE ec.people_id = pe.id AND pe.people_id = c.people_id
                        AND pe.people_id=p.id AND ec.id=c.id)
            loop
                if c_year.SEX = 1 then
                    UPDATE mis.emergency_call SET people_age_y = c_year.YEAR, people_age_m = c_year.Mouth
                    , people_id = 10809100 -- это ID people_er (пустой) Мужской пол
                    WHERE  id = c_year.ID; 
                else
                    UPDATE mis.emergency_call SET people_age_y = c_year.YEAR, people_age_m = c_year.Mouth
                    , people_id = 6936300 -- это ID people_er (пустой) Женский пол
                    WHERE  id = c_year.ID;                   
                end if;
            END loop;
        ELSE -- Если в вызове указан возраст, то не обновляем его параметрами из пипла
                if c.SEX = 1 then
                    UPDATE mis.emergency_call SET people_id = 10809100 -- это ID people_er (пустой) Мужской по
                    WHERE  id = c.ID; 
                else
                    UPDATE mis.emergency_call SET people_id = 6936300 -- это ID people_er (пустой) Женский пол
                    WHERE  id = c.ID;                   
                end if;          
        END IF;
        Commit;
    END loop;
 
    FOR n IN cTrig -- Отключаем тригер репликации
    LOOP
        EXECUTE IMMEDIATE 'ALTER TRIGGER '||n.object_name||' DISABLE';
    END LOOP ;    
    -- Удаляем данные из таблиц ссылающихся на пипла
    DELETE FROM mis.pdoc WHERE pdoc.people_id=nPeopleID;
    DELETE FROM mis.pinfo WHERE pinfo.people_id=nPeopleID;
    DELETE FROM mis.people_addr pa WHERE pa.people_id=nPeopleID;
    DELETE FROM mis.people_er pe WHERE pe.people_id=nPeopleID;
    DELETE FROM mis.people WHERE id=nPeopleID; 
    Commit;
 
    FOR n IN cTrig -- Включаем тригер репликации
    LOOP
        EXECUTE IMMEDIATE 'ALTER TRIGGER '||n.object_name||' ENABLE';
    END LOOP ;  
END;

declare 
  i integer;
  cnt integer; --количество инкриментов
  ii integer;
begin
 cnt:=10000; 
 FOR i IN 1..cnt
  LOOP
    select mis.sq_team_schedule.nextval into ii from dual;
    --alter sequence mis.sq_team_schedule increment by  100;  
  end loop;  
end;