--------------- SQL ---------------

CREATE OR REPLACE FUNCTION "oms"."lpu_attach_detect_conflict" () RETURNS boolean AS
$body$
DECLARE
  rec RECORD;
  _new_cod_lpu CHAR(4);-- Код ЛПУ к которому будет прикреплен человек
  _new_attach_date DATE;
  _new_attach_number VARCHAR;
  _attach_date DATE;
  _new_uch_id INTEGER;
BEGIN
	-- Цикл по всем людям указанным в файлах
    FOR rec IN 
    	SELECT DISTINCT lai.person_id, p.area_lpu_d, p.attach_date, ld.cod_lpu
        FROM oms.lpu_attach_item lai 
        	INNER JOIN oms.person p ON lai.person_id = p.person_id
            LEFT JOIN foms.lpu_district ld ON ld.uch_id = p.area_lpu_d
        WHERE lai.person_id IS NOT NULL AND lai.uch_id IS NOT NULL AND 
              lai.lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1)
    LOOP
        IF rec.area_lpu_d IS NOT NULL THEN -- Если прикрепление есть
        	-- Ищем более новое прикрепление
        	_attach_date := rec.attach_date;
            IF _attach_date IS NULL THEN
            	SELECT CAST ('1970-01-01' as DATE) INTO _attach_date;
            END IF;
        
			SELECT lai.kodlpu, lai.d_attach, lai.n_attach, lai.uch_id INTO _new_cod_lpu, _new_attach_date, _new_attach_number, _new_uch_id
            FROM oms.lpu_attach_item lai
            WHERE lai.person_id = rec.person_id AND         
	              lai.lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1) AND
                  lai.d_attach > _attach_date
            ORDER BY lai.d_attach DESC LIMIT 1;
            
            IF _new_cod_lpu IS NOT NULL THEN -- Если новое прикрепление найдено
    	        -- Если ЛПУ различается
	            IF rec.cod_lpu <> _new_cod_lpu THEN 
        	    	-- Для новой ЛПУ устанавливем: Конфликт с положительным решением
                    UPDATE oms.lpu_attach_item
                    SET attach = 1
                    WHERE person_id = rec.person_id AND 
                    	  kodlpu = _new_cod_lpu AND
                          lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
                
    	            -- Для всех остальных: Конфликт с отрицательным решением
                    UPDATE oms.lpu_attach_item
                    SET attach = 2
                    WHERE person_id = rec.person_id AND 
                    	  kodlpu <> _new_cod_lpu AND
                          lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
                          
                    -- Проставляем новое прикрепление для человека
                    UPDATE oms.person
                    SET area_lpu_d = _new_uch_id, 
                    	attach_number = _new_attach_number, 
                        attach_date = _new_attach_date
                    WHERE person_id = rec.person_id;
                          
	            ELSE -- ЛПУ одинаковые
        	    	-- Для текущей ЛПУ ставим: Конфликт отсутсвует
                    UPDATE oms.lpu_attach_item
                    SET attach = 3
                    WHERE person_id = rec.person_id AND 
                    	  kodlpu = rec.cod_lpu AND
                          lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
                    
    	            -- Для всех остальных: Конфликт с отрицательным решением
                    UPDATE oms.lpu_attach_item
                    SET attach = 2
                    WHERE person_id = rec.person_id AND 
                    	  kodlpu <> rec.cod_lpu AND
                          lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
                    
                    -- Если участки разные, то проставляем новое прикрепление
                    IF rec.area_lpu_d <> _new_uch_id THEN
                	    UPDATE oms.person
            	        SET area_lpu_d = _new_uch_id, 
        	            	attach_number = _new_attach_number, 
    	                    attach_date = _new_attach_date
	                    WHERE person_id = rec.person_id;
                    END IF;      
	            END IF;
            ELSE -- Прикрепение не найдено
       	    	-- Для текущей ЛПУ ставим: Конфликт отсутсвует
                UPDATE oms.lpu_attach_item
                SET attach = 3
                WHERE person_id = rec.person_id AND 
                   	  kodlpu = rec.cod_lpu AND
                      lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
                    
   	            -- Для всех остальных: Конфликт с отрицательным решением
                UPDATE oms.lpu_attach_item
                SET attach = 2
                WHERE person_id = rec.person_id AND 
                  	  kodlpu <> rec.cod_lpu AND
                      lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
            END IF;
        ELSE -- Если текущего прикрепления нет
        	-- Ищем первое попашвееся прикрепление с максимальной датой
			SELECT lai.kodlpu, lai.d_attach, lai.n_attach, lai.uch_id INTO _new_cod_lpu, _new_attach_date, _new_attach_number, _new_uch_id
            FROM oms.lpu_attach_item lai
            WHERE lai.person_id = rec.person_id AND         
	              lai.lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1)
            ORDER BY lai.d_attach DESC LIMIT 1;
            
            IF _new_cod_lpu IS NOT NULL THEN -- Если прикрепление найдено
        	    -- Для новой ЛПУ устанавливем: Конфликт с положительным решением
                UPDATE oms.lpu_attach_item
                SET attach = 1
                WHERE person_id = rec.person_id AND 
                   	  kodlpu = _new_cod_lpu AND
                      lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
                
    	        -- Для всех остальных: Конфликт с отрицательным решением
                UPDATE oms.lpu_attach_item
                SET attach = 2
                WHERE person_id = rec.person_id AND 
                   	  kodlpu <> _new_cod_lpu AND
                      lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
                          
                -- Проставляем новое прикрепление для человека
                UPDATE oms.person
                SET area_lpu_d = _new_uch_id, 
                  	attach_number = _new_attach_number, 
                    attach_date = _new_attach_date
                WHERE person_id = rec.person_id;
            
            ELSE -- Оно должно быть всегда, найдено, но если вдруг нет, то
   	            -- Для всех остальных: Конфликт с отрицательным решением
                UPDATE oms.lpu_attach_item
                SET attach = 2
                WHERE person_id = rec.person_id AND 
                      lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
            END IF;
        	
        END IF;
    END LOOP;
    
    -- Устанавливает статусы для файлов прикреплений
    UPDATE oms.lpu_attach
    SET status = 3
    WHERE status = 1;
    
    RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;



















--------------- SQL ---------------

CREATE OR REPLACE FUNCTION "oms"."lpu_attach_detect_conflict" () RETURNS boolean AS
$body$
DECLARE
  rec RECORD;
  _new_cod_lpu CHAR(4);-- Код ЛПУ к которому будет прикреплен человек
  _new_attach_date DATE;
  _new_attach_number VARCHAR;
  _attach_date DATE;
  _new_uch_id INTEGER;
  _cnt INTEGER;  
BEGIN
	-- Цикл по всем людям указанным в файлах
    FOR rec IN 
    	SELECT DISTINCT lai.person_id, p.area_lpu_d, p.attach_date, ld.cod_lpu
        FROM oms.lpu_attach_item lai 
        	INNER JOIN oms.person p ON lai.person_id = p.person_id
            LEFT JOIN foms.lpu_district ld ON ld.uch_id = p.area_lpu_d
        WHERE lai.person_id IS NOT NULL AND lai.uch_id IS NOT NULL AND 
              lai.lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1)
    LOOP
        IF rec.area_lpu_d IS NOT NULL THEN -- Если прикрепление есть
        	-- Ищем более новое прикрепление
        	_attach_date := rec.attach_date;
            IF _attach_date IS NULL THEN
            	SELECT CAST ('1970-01-01' as DATE) INTO _attach_date;
            END IF;
        
			SELECT lai.kodlpu, lai.d_attach, lai.n_attach, lai.uch_id INTO _new_cod_lpu, _new_attach_date, _new_attach_number, _new_uch_id
            FROM oms.lpu_attach_item lai
            WHERE lai.person_id = rec.person_id AND         
	              lai.lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1) AND
                  lai.d_attach > _attach_date
            ORDER BY lai.d_attach DESC LIMIT 1;
            
            IF _new_cod_lpu IS NOT NULL THEN -- Если новое прикрепление найдено
    	        -- Если ЛПУ различается
	            IF rec.cod_lpu <> _new_cod_lpu THEN 
        	    	-- Для новой ЛПУ устанавливем: Конфликт с положительным решением
                    UPDATE oms.lpu_attach_item
                    SET attach = 1
                    WHERE person_id = rec.person_id AND 
                    	  kodlpu = _new_cod_lpu AND
                          lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
                
    	            -- Для всех остальных: Конфликт с отрицательным решением
                    UPDATE oms.lpu_attach_item
                    SET attach = 2
                    WHERE person_id = rec.person_id AND 
                    	  kodlpu <> _new_cod_lpu AND
                          lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
                          
                    -- Проставляем новое прикрепление для человека
                    UPDATE oms.person
                    SET area_lpu_d = _new_uch_id, 
                    	attach_number = _new_attach_number, 
                        attach_date = _new_attach_date
                    WHERE person_id = rec.person_id;
                          
	            ELSE -- ЛПУ одинаковые
        	    	-- Для текущей ЛПУ ставим: Конфликт отсутсвует
                    UPDATE oms.lpu_attach_item
                    SET attach = 3
                    WHERE person_id = rec.person_id AND 
                    	  kodlpu = rec.cod_lpu AND
                          lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
                    
    	            -- Для всех остальных: Конфликт с отрицательным решением
                    UPDATE oms.lpu_attach_item
                    SET attach = 2
                    WHERE person_id = rec.person_id AND 
                    	  kodlpu <> rec.cod_lpu AND
                          lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
                    
                    -- Если участки разные, то проставляем новое прикрепление
                    IF rec.area_lpu_d <> _new_uch_id THEN
                	    UPDATE oms.person
            	        SET area_lpu_d = _new_uch_id, 
        	            	attach_number = _new_attach_number, 
    	                    attach_date = _new_attach_date
	                    WHERE person_id = rec.person_id;
                    END IF;      
	            END IF;
            ELSE -- Прикрепение не найдено
       	    	-- Для текущей ЛПУ ставим: Конфликт отсутсвует
                UPDATE oms.lpu_attach_item
                SET attach = 3
                WHERE person_id = rec.person_id AND 
                   	  kodlpu = rec.cod_lpu AND
                      lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
                    
   	            -- Для всех остальных: Конфликт с отрицательным решением
                UPDATE oms.lpu_attach_item
                SET attach = 2
                WHERE person_id = rec.person_id AND 
                  	  kodlpu <> rec.cod_lpu AND
                      lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
            END IF;
        ELSE -- Если текущего прикрепления нет
        	-- Считаем сколько ЛПУ подали прикрепления на этого человека
            SELECT COUNT(*) INTO _cnt
            FROM (SELECT lai.kodlpu, lai.n_attach
	              FROM oms.lpu_attach_item lai
            	  WHERE lai.person_id = rec.person_id AND         
	              		lai.lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1)
            	  GROUP BY lai.kodlpu, lai.n_attach) as data;
            IF _cnt == 1 THEN -- Если только одно ЛПУ
	        	-- Ищем первое попашвееся прикрепление с максимальной датой
				SELECT lai.kodlpu, lai.d_attach, lai.n_attach, lai.uch_id INTO _new_cod_lpu, _new_attach_date, _new_attach_number, _new_uch_id
        	    FROM oms.lpu_attach_item lai
            	WHERE lai.person_id = rec.person_id AND         
	            	  lai.lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1)
	            ORDER BY lai.d_attach DESC LIMIT 1;
            ELSE -- Если ни одного или несколько, то считаем что прикрепление не найдено
            	_new_cod_lpu := NULL;
            END IF;
            
            IF _new_cod_lpu IS NOT NULL THEN -- Если прикрепление найдено
        	    -- Для новой ЛПУ устанавливем: Конфликт с положительным решением
                UPDATE oms.lpu_attach_item
                SET attach = 1
                WHERE person_id = rec.person_id AND 
                   	  kodlpu = _new_cod_lpu AND
                      lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
                
    	        -- Для всех остальных: Конфликт с отрицательным решением
                UPDATE oms.lpu_attach_item
                SET attach = 2
                WHERE person_id = rec.person_id AND 
                   	  kodlpu <> _new_cod_lpu AND
                      lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
                          
                -- Проставляем новое прикрепление для человека
                UPDATE oms.person
                SET area_lpu_d = _new_uch_id, 
                  	attach_number = _new_attach_number, 
                    attach_date = _new_attach_date
                WHERE person_id = rec.person_id;
            
            ELSE -- В противном случае для всех: Конфликт с отрицательным решением
                UPDATE oms.lpu_attach_item
                SET attach = 2
                WHERE person_id = rec.person_id AND 
                      lpu_attach_id IN (SELECT la.lpu_attach_id FROM oms.lpu_attach la WHERE la.status = 1);
            END IF;
        	
        END IF;
    END LOOP;
    
    -- Устанавливает статусы для файлов прикреплений
    UPDATE oms.lpu_attach
    SET status = 3
    WHERE status = 1;
    
    RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;














--------------- SQL ---------------

CREATE TABLE "lpu"."contract" (
  "contract_id" SERIAL NOT NULL, 
  "lpu_id" INTEGER NOT NULL, 
  "contract_number" VARCHAR(20) NOT NULL, 
  "contract_type" INTEGER NOT NULL, 
  "sign_date" DATE, 
  "start_date" DATE, 
  "finish_date" DATE, 
  "prolongation" VARCHAR(1000), 
  "description" VARCHAR(1000), 
  "1c_name" VARCHAR(500), 
  PRIMARY KEY("contract_id")
) WITH OIDS;

COMMENT ON TABLE "lpu"."contract"
IS 'Договора с ЛПУ по ДМС, ОМС и ДМС Транснефть';

COMMENT ON COLUMN "lpu"."contract"."contract_id"
IS 'Ид-р договора';

COMMENT ON COLUMN "lpu"."contract"."contract_number"
IS 'Номер договора';

COMMENT ON COLUMN "lpu"."contract"."contract_type"
IS 'Тип договора:
0 - ОМС
1 - ДМС Спасение
2 - ДМС Транснефть';

COMMENT ON COLUMN "lpu"."contract"."sign_date"
IS 'Дата подписания';

COMMENT ON COLUMN "lpu"."contract"."start_date"
IS 'Дата начала действия';

COMMENT ON COLUMN "lpu"."contract"."finish_date"
IS 'Дата завершения действия';

COMMENT ON COLUMN "lpu"."contract"."prolongation"
IS 'Информация о пролонгации договора';

COMMENT ON COLUMN "lpu"."contract"."description"
IS 'Комментарий и прочие заметки';

COMMENT ON COLUMN "lpu"."contract"."1c_name"
IS 'Наименовнаие для 1С';


--------------- SQL ---------------


--------------- SQL ---------------

ALTER TABLE "lpu"."contract"
  ADD CONSTRAINT "contract_fk_lpu" FOREIGN KEY ("lpu_id")
    REFERENCES "lpu"."lpu"("lpu_id")
    ON DELETE RESTRICT
    ON UPDATE NO ACTION
    NOT DEFERRABLE;


--------------- SQL ---------------

CREATE INDEX "contract_idx_lpu" ON "lpu"."contract"
  USING btree ("lpu_id");

--------------- SQL ---------------

CREATE UNIQUE INDEX "contract_idx_contract_number" ON "lpu"."contract"
  USING btree ("contract_number");


--------------- SQL ---------------

CREATE TABLE "lpu"."contract_program" (
  "contract_id" INTEGER NOT NULL, 
  "program_id" INTEGER NOT NULL
) WITH OIDS;

COMMENT ON TABLE "lpu"."contract_program"
IS 'Программы страхования доступные по данному договору. Актуально только для договоров ДМС';

COMMENT ON COLUMN "lpu"."contract_program"."contract_id"
IS 'Ид-р договора с ЛПУ по ДМС';

COMMENT ON COLUMN "lpu"."contract_program"."program_id"
IS 'Ид-р программы страхования';


--------------- SQL ---------------

ALTER TABLE "lpu"."contract_program"
  ADD CONSTRAINT "contract_program_pk" 
  PRIMARY KEY ("contract_id", "program_id");


--------------- SQL ---------------

ALTER TABLE "lpu"."contract_program"
  ADD CONSTRAINT "contract_program_fk_contract" FOREIGN KEY ("contract_id")
    REFERENCES "lpu"."contract"("contract_id")
    ON DELETE CASCADE
    ON UPDATE NO ACTION
    NOT DEFERRABLE;


--------------- SQL ---------------

ALTER TABLE "lpu"."contract_program"
  ADD CONSTRAINT "contract_program_fk_program" FOREIGN KEY ("program_id")
    REFERENCES "dms2"."program"("program_id")
    ON DELETE RESTRICT
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

--------------- SQL ---------------

ALTER TABLE "lpu"."contract"
  ADD COLUMN "pay_method" INTEGER;

ALTER TABLE "lpu"."contract"
  ALTER COLUMN "pay_method" SET DEFAULT 0;

ALTER TABLE "lpu"."contract"
  ALTER COLUMN "pay_method" SET NOT NULL;

COMMENT ON COLUMN "lpu"."contract"."pay_method"
IS 'Вид оплаты:
0 - по факту
1 - по 100% предоплате
2 - иное';

--------------- SQL ---------------

COMMENT ON COLUMN "lpu"."contract"."contract_type"
IS 'Тип договора:
0  - ДМС Спасение
1  - ДМС Транснефть
-1 - ОМС';


--------------- SQL ---------------

ALTER TABLE "lpu"."contract"
  RENAME COLUMN "1c_name" TO "name_1c";

--------------- SQL ---------------

ALTER TABLE "lpu"."price_section"
  ADD COLUMN "contract_id" INTEGER;

COMMENT ON COLUMN "lpu"."price_section"."contract_id"
IS 'Ид-р договора';


--------------- SQL ---------------

ALTER TABLE "lpu"."price_section"
  ADD CONSTRAINT "price_section_fk_contract" FOREIGN KEY ("contract_id")
    REFERENCES "lpu"."contract"("contract_id")
    ON DELETE CASCADE
    ON UPDATE NO ACTION
    NOT DEFERRABLE;


--------------- SQL ---------------

CREATE INDEX "price_section_idx_contract" ON "lpu"."price_section"
  USING btree ("contract_id");


--------------- SQL ---------------

ALTER TABLE "lpu"."act"
  ADD COLUMN "contract_id" INTEGER;

COMMENT ON COLUMN "lpu"."act"."contract_id"
IS 'Ид-р договора с ЛПУ';


--------------- SQL ---------------

ALTER TABLE "lpu"."act"
  ADD CONSTRAINT "act_fk_contract" FOREIGN KEY ("contract_id")
    REFERENCES "lpu"."contract"("contract_id")
    ON DELETE RESTRICT
    ON UPDATE NO ACTION
    NOT DEFERRABLE;


--------------- SQL ---------------

CREATE INDEX "act_idx_contract" ON "lpu"."act"
  USING btree ("contract_id");


--------------- SQL ---------------

ALTER TABLE "lpu"."bill"
  ADD COLUMN "contract_id" INTEGER;

COMMENT ON COLUMN "lpu"."bill"."contract_id"
IS 'Ид-р договора с ЛПУ';


--------------- SQL ---------------

ALTER TABLE "lpu"."bill"
  ADD CONSTRAINT "bill_fk_contract" FOREIGN KEY ("contract_id")
    REFERENCES "lpu"."contract"("contract_id")
    ON DELETE CASCADE
    ON UPDATE NO ACTION
    NOT DEFERRABLE;


--------------- SQL ---------------

CREATE INDEX "bill_idx_contract" ON "lpu"."bill"
  USING btree ("contract_id");



--------------- SQL ---------------

ALTER TABLE "lpu"."invoice"
  ADD COLUMN "contract_id" INTEGER;

COMMENT ON COLUMN "lpu"."invoice"."contract_id"
IS 'Ид-р договора с ЛПУ';


--------------- SQL ---------------

ALTER TABLE "lpu"."invoice"
  ADD CONSTRAINT "invoice_fk_contract" FOREIGN KEY ("contract_id")
    REFERENCES "lpu"."contract"("contract_id")
    ON DELETE CASCADE
    ON UPDATE NO ACTION
    NOT DEFERRABLE;


--------------- SQL ---------------

CREATE INDEX "invoice_idx_contract" ON "lpu"."invoice"
  USING btree ("contract_id");







-- Переносим информацию по договорам с ЛПУ
INSERT INTO lpu.contract (lpu_id, contract_number, contract_type, sign_date, start_date, finish_date, prolongation, description, name_1c, pay_method)
SELECT i.lpu_id, i.contract_number, i."type", i.sing_date, i.start_date, i.finish_date, i.status, i.description, NULL, i.pay_method
FROM lpu.lpu l INNER JOIN lpu.info i ON l.lpu_info_id = i.lpu_info_id;

-- Переносим информация по программам страхования
INSERT INTO lpu.contract_program(contract_id, program_id)
SELECT c.contract_id, lp.programm_id
FROM lpu.lpu_programm lp INNER JOIN lpu.contract c ON lp.lpu_id = c.lpu_id;

-- Переносим информацию по прейскурантам
ALTER TABLE "lpu"."price_section"
  DISABLE TRIGGER "price_section_tr";
ALTER TABLE "lpu"."price_section"
  DISABLE TRIGGER "price_section_tr_au";
ALTER TABLE "lpu"."price_section"
  DISABLE TRIGGER "price_section_tr_bru";
UPDATE lpu.price_section
SET contract_id = (SELECT c.contract_id FROM lpu.contract c WHERE c.lpu_id = lpu.price_section.lpu_id);
ALTER TABLE "lpu"."price_section"
  ENABLE TRIGGER "price_section_tr";
ALTER TABLE "lpu"."price_section"
  ENABLE TRIGGER "price_section_tr_au";
ALTER TABLE "lpu"."price_section"
  ENABLE TRIGGER "price_section_tr_bru";
  
-- Переносим информацию по актам
UPDATE lpu.act
SET contract_id = (SELECT c.contract_id FROM lpu.contract c WHERE c.lpu_id = lpu.act.lpu_id);

-- Переносим информацию по счетам
UPDATE lpu.bill
SET contract_id = (SELECT c.contract_id FROM lpu.contract c WHERE c.lpu_id = lpu.bill.lpu_id);

-- Переносим информацию по счетам-фактурам
UPDATE lpu.invoice
SET contract_id = (SELECT c.contract_id FROM lpu.contract c WHERE c.lpu_id = lpu.invoice.lpu_id);







--------------- SQL ---------------

ALTER TABLE "lpu"."act"
  ALTER COLUMN "contract_id" SET NOT NULL;


--------------- SQL ---------------

ALTER TABLE "lpu"."act"
  DROP COLUMN "lpu_id";


--------------- SQL ---------------

ALTER TABLE "lpu"."bill"
  ALTER COLUMN "contract_id" SET NOT NULL;

--------------- SQL ---------------

ALTER TABLE "lpu"."bill"
  DROP COLUMN "lpu_id";


--------------- SQL ---------------

ALTER TABLE "lpu"."invoice"
  ALTER COLUMN "contract_id" SET NOT NULL;


--------------- SQL ---------------

ALTER TABLE "lpu"."invoice"
  DROP COLUMN "lpu_id";



--------------- SQL ---------------

ALTER TABLE "lpu"."lpu"
  ADD COLUMN "dms_contract_id" INTEGER;

COMMENT ON COLUMN "lpu"."lpu"."dms_contract_id"
IS 'Ид-р договора с ЛПУ по ДМС Спасение';


--------------- SQL ---------------

ALTER TABLE "lpu"."lpu"
  ADD COLUMN "dms_contract_tn_id" INTEGER;

COMMENT ON COLUMN "lpu"."lpu"."dms_contract_tn_id"
IS 'Ид-р договора с ЛПУ по ДМС Транснефть';


--------------- SQL ---------------

ALTER TABLE "lpu"."lpu"
  ADD COLUMN "oms_contract_id" INTEGER;

COMMENT ON COLUMN "lpu"."lpu"."oms_contract_id"
IS 'Ид-р договора с ЛПУ по ОМС';




--------------- SQL ---------------

ALTER TABLE "lpu"."lpu"
  ADD CONSTRAINT "lpu_fk_dms_contract" FOREIGN KEY ("dms_contract_id")
    REFERENCES "lpu"."contract"("contract_id")
    ON DELETE SET NULL
    ON UPDATE NO ACTION
    NOT DEFERRABLE;


--------------- SQL ---------------

ALTER TABLE "lpu"."lpu"
  ADD CONSTRAINT "lpu_fk_dms_contract_tn" FOREIGN KEY ("dms_contract_tn_id")
    REFERENCES "lpu"."contract"("contract_id")
    ON DELETE SET NULL
    ON UPDATE NO ACTION
    NOT DEFERRABLE;


--------------- SQL ---------------

ALTER TABLE "lpu"."lpu"
  ADD CONSTRAINT "lpu_fk_oms_contract" FOREIGN KEY ("oms_contract_id")
    REFERENCES "lpu"."contract"("contract_id")
    ON DELETE SET NULL
    ON UPDATE NO ACTION
    NOT DEFERRABLE;








--------------- SQL ---------------

ALTER TABLE "lpu"."price_section"
  ALTER COLUMN "contract_id" SET NOT NULL;



--------------- SQL ---------------

CREATE FUNCTION "lpu"."convert_lpu" (_lpu_id integer) RETURNS integer AS
$body$
DECLARE
  _new_lpu_id INTEGER;
BEGIN
	SELECT nl.lpu_id INTO _new_lpu_id
	FROM 
	(
		SELECT MIN (i.lpu_info_id) as old_lpu_info_id, MAX(i.lpu_info_id) as new_lpu_info_id
		FROM lpu.info i
		GROUP BY i.short_name
		HAVING COUNT(*)>1
	) d
	INNER JOIN lpu.lpu ol ON ol.lpu_info_id = d.old_lpu_info_id
	INNER JOIN lpu.lpu nl ON nl.lpu_info_id = d.new_lpu_info_id
	WHERE ol.lpu_id = _lpu_id;
    
	RETURN _new_lpu_id;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;




-- Чистим список ЛПУ
-- a)Удаляем старую историческую информацию
DELETE FROM lpu.info
WHERE NOT EXISTS (SELECT * FROM lpu.lpu l WHERE l.lpu_info_id = lpu.info.lpu_info_id);




-- Правим ссылки на ЛПУ в договорах
UPDATE lpu.contract
SET lpu_id = lpu.convert_lpu(lpu_id)
WHERE lpu.convert_lpu(lpu_id) IS NOT NULL;

-- Правим ссылки на ЛПУ в обращениях
UPDATE ipr.petition
SET lpu_id = lpu.convert_lpu(lpu_id)
WHERE policy_type = 0 AND lpu.convert_lpu(lpu_id) IS NOT NULL;





-- Чистим список ЛПУ
-- б)Чистим дубликаты
DELETE FROM lpu.lpu
WHERE lpu.convert_lpu(lpu_id) IS NOT NULL;


-- Правим ссылки на ЛПУ в договорах
UPDATE lpu.contract
SET lpu_id = lpu.convert_lpu(lpu_id)
WHERE lpu.convert_lpu(lpu_id) IS NOT NULL;

-- Правим ссылки на ЛПУ в обращениях
UPDATE ipr.petition
SET lpu_id = lpu.convert_lpu(lpu_id)
WHERE policy_type = 0 AND lpu.convert_lpu(lpu_id) IS NOT NULL;



-- Чистим список ЛПУ
-- б)Чистим дубликаты
DELETE FROM lpu.lpu
WHERE lpu.convert_lpu(lpu_id) IS NOT NULL;



-- Проставляем ссылки на договора
UPDATE lpu.lpu
SET dms_contract_id = (SELECT c.contract_id FROM lpu.contract c WHERE c.contract_type=0 AND c.lpu_id = lpu.lpu.lpu_id AND c.start_date <= now() AND c.finish_date >= now()  LIMIT 1),
    dms_contract_tn_id = (SELECT c.contract_id FROM lpu.contract c WHERE c.contract_type=1 AND c.lpu_id = lpu.lpu.lpu_id  AND c.start_date <= now() AND c.finish_date >= now() LIMIT 1);


--------------- SQL ---------------

ALTER TABLE "lpu"."lpu"
  ADD COLUMN "lpu_type" INTEGER;

ALTER TABLE "lpu"."lpu"
  ALTER COLUMN "lpu_type" SET DEFAULT 0;

COMMENT ON COLUMN "lpu"."lpu"."lpu_type"
IS 'Тип ЛПУ:
0 - Городского типа
1 - Сельского типа';


UPDATE lpu.lpu
SET lpu_type = 0;


--------------- SQL ---------------

ALTER TABLE "lpu"."lpu"
  ALTER COLUMN "lpu_type" SET NOT NULL;


--------------- SQL ---------------

ALTER TABLE "lpu"."lpu"
  ADD COLUMN "lpu_code" CHAR(4);

COMMENT ON COLUMN "lpu"."lpu"."lpu_code"
IS 'Код ЛПУ';


--------------- SQL ---------------

ALTER TABLE "lpu"."lpu"
  ADD CONSTRAINT "lpu_fk_info" FOREIGN KEY ("lpu_info_id")
    REFERENCES "lpu"."info"("lpu_info_id")
    ON DELETE RESTRICT
    ON UPDATE NO ACTION
    NOT DEFERRABLE;


--------------- SQL ---------------

CREATE INDEX "lpu_idx_code" ON "lpu"."lpu"
  USING btree ("lpu_code");


--------------- SQL ---------------

ALTER TABLE "lpu"."info"
  ADD COLUMN "author_login" VARCHAR(20);

ALTER TABLE "lpu"."info"
  ALTER COLUMN "author_login" SET DEFAULT "session_user"();

COMMENT ON COLUMN "lpu"."info"."author_login"
IS 'Логин пользователя создавшего версию';


UPDATE lpu.info
SET author_login = session_user;


--------------- SQL ---------------

ALTER TABLE "lpu"."info"
  ALTER COLUMN "author_login" SET NOT NULL;

--------------- SQL ---------------

ALTER TABLE "lpu"."info"
  ALTER COLUMN "modify_time" SET NOT NULL;

--------------- SQL ---------------

ALTER TABLE "lpu"."info"
  ALTER COLUMN "contract_number" DROP NOT NULL;


--------------- SQL ---------------

ALTER TABLE "lpu"."lpu"
  ADD COLUMN "parent_lpu_id" INTEGER;

COMMENT ON COLUMN "lpu"."lpu"."parent_lpu_id"
IS 'Ид-р родительского ЛПУ';


--------------- SQL ---------------

ALTER TABLE "lpu"."lpu"
  ADD CONSTRAINT "lpu_fk_parent_lpu" FOREIGN KEY ("parent_lpu_id")
    REFERENCES "lpu"."lpu"("lpu_id")
    ON DELETE SET NULL
    ON UPDATE NO ACTION
    NOT DEFERRABLE;


--------------- SQL ---------------

CREATE INDEX "lpu_idx_parent_lpu" ON "lpu"."lpu"
  USING btree ("parent_lpu_id");


--------------- SQL ---------------

DROP FUNCTION "lpu"."get_lpu_by_id"(id integer, go integer, ilf integer, uin integer);









--------------- SQL ---------------

DROP VIEW "lpu"."lpu_view";



--------------- SQL ---------------

CREATE VIEW lpu.lpu_view ( 
  lpu_info_id,
  lpu_id,
  full_name,
  short_name,
  low_address,
  director_name,
  based_on,
  inn,
  kpp,
  ogrn,
  okato,
  bank,
  account,
  corr_account,
  bic,
  address,
  mail_address,
  phone,
  fax,
  email,
  agent_name,
  agent_phone,
  agent_mobile,
  agent_email,
  description,
  modify_time,
  author_login,
  status,
  author_name,
  director_name_r,
  director_pos,
  director_pos_r,
  lpu_type,
  parent_lpu_id,
  lpu_code,
  oms_contract_id,
  dms_contract_id,
  dms_contract_tn_id
) 
AS
SELECT 
  i.lpu_info_id,
  l.lpu_id,
  i.full_name,
  i.short_name,
  i.low_address,
  i.director_name,
  i.based_on,
  i.inn,
  i.kpp,
  i.ogrn,
  i.okato,
  i.bank,
  i.account,
  i.corr_account,
  i.bic,
  i.address,
  i.mail_address,
  i.phone,
  i.fax,
  i.email,
  i.agent_name,
  i.agent_phone,
  i.agent_mobile,
  i.agent_email,
  i.description,
  i.modify_time,
  i.author_login,
  i.status,
  u.name AS author_name,
  i.director_name_r,
  i.director_pos,
  i.director_pos_r,
  l.lpu_type,
  l.parent_lpu_id,
  l.lpu_code,
  l.oms_contract_id,
  l.dms_contract_id,
  l.dms_contract_tn_id
FROM
  lpu.lpu l
  INNER JOIN lpu.info i ON (i.lpu_info_id = l.lpu_info_id)
  LEFT OUTER JOIN qe_user u ON (u.login = i.author_login);






--------------- SQL ---------------

ALTER TABLE "lpu"."lpu"
  ADD COLUMN "place_id" INTEGER;

COMMENT ON COLUMN "lpu"."lpu"."place_id"
IS 'Ид-р населенного пункта в котором находтся ЛПУ';



--------------- SQL ---------------

ALTER TABLE "lpu"."lpu"
  ADD CONSTRAINT "lpu_fk_place" FOREIGN KEY ("place_id")
    REFERENCES "oms"."place"("place_id")
    ON DELETE SET NULL
    ON UPDATE NO ACTION
    NOT DEFERRABLE;


--------------- SQL ---------------

DROP VIEW "lpu"."lpu_view";

CREATE VIEW "lpu"."lpu_view" (
    lpu_info_id,
    lpu_id,
    full_name,
    short_name,
    low_address,
    director_name,
    based_on,
    inn,
    kpp,
    ogrn,
    okato,
    bank,
    account,
    corr_account,
    bic,
    address,
    mail_address,
    phone,
    fax,
    email,
    agent_name,
    agent_phone,
    agent_mobile,
    agent_email,
    description,
    modify_time,
    author_login,
    status,
    author_name,
    director_name_r,
    director_pos,
    director_pos_r,
    lpu_type,
    parent_lpu_id,
    lpu_code,
    oms_contract_id,
    dms_contract_id,
    dms_contract_tn_id,
    dms_contract_id_name,
    dms_contract_tn_id_name,
    oms_contract_id_name,
    parent_lpu_id_name)
AS
SELECT i.lpu_info_id, l.lpu_id, i.full_name, i.short_name, i.low_address,
    i.director_name, i.based_on, i.inn, i.kpp, i.ogrn, i.okato, i.bank,
    i.account, i.corr_account, i.bic, i.address, i.mail_address, i.phone,
    i.fax, i.email, i.agent_name, i.agent_phone, i.agent_mobile, i.agent_email,
    i.description, i.modify_time, i.author_login, i.status, u.name AS
    author_name, i.director_name_r, i.director_pos, i.director_pos_r,
    l.lpu_type, l.parent_lpu_id, l.lpu_code, l.oms_contract_id,
    l.dms_contract_id, l.dms_contract_tn_id,
    dmsc.contract_number || ' От ' || to_char(dmsc.sign_date, 'DD.MM.YYYY') as dms_contract_id_name,
    dmstnc.contract_number || ' От ' || to_char(dmstnc.sign_date, 'DD.MM.YYYY') as dms_contract_tn_id_name,
    omsc.contract_number || ' От ' || to_char(omsc.sign_date, 'DD.MM.YYYY') as oms_contract_id_name,
    pl.name as parent_lpu_id_name
FROM lpu.lpu l 
		JOIN lpu.info i ON i.lpu_info_id = l.lpu_info_id
        LEFT JOIN qe_user u ON (u.login)::text = (i.author_login)::text
        LEFT JOIN lpu.contract dmsc ON l.dms_contract_id = dmsc.contract_id
        LEFT JOIN lpu.contract omsc ON l.oms_contract_id = omsc.contract_id
        LEFT JOIN lpu.contract dmstnc ON l.dms_contract_tn_id = dmstnc.contract_id
        LEFT JOIN lpu.lpu pl ON l.parent_lpu_id = pl.lpu_id;




--------------- SQL ---------------

CREATE OR REPLACE VIEW "lpu"."lpu_view" (
    lpu_info_id,
    lpu_id,
    full_name,
    short_name,
    low_address,
    director_name,
    based_on,
    inn,
    kpp,
    ogrn,
    okato,
    bank,
    account,
    corr_account,
    bic,
    address,
    mail_address,
    phone,
    fax,
    email,
    agent_name,
    agent_phone,
    agent_mobile,
    agent_email,
    description,
    modify_time,
    author_login,
    status,
    author_name,
    director_name_r,
    director_pos,
    director_pos_r,
    lpu_type,
    parent_lpu_id,
    lpu_code,
    oms_contract_id,
    dms_contract_id,
    dms_contract_tn_id,
    dms_contract_id_name,
    dms_contract_tn_id_name,
    oms_contract_id_name,
    parent_lpu_id_name)
AS
SELECT i.lpu_info_id, l.lpu_id, i.full_name, i.short_name, i.low_address,
    i.director_name, i.based_on, i.inn, i.kpp, i.ogrn, i.okato, i.bank,
    i.account, i.corr_account, i.bic, i.address, i.mail_address, i.phone,
    i.fax, i.email, i.agent_name, i.agent_phone, i.agent_mobile, i.agent_email,
    i.description, i.modify_time, i.author_login, i.status, u.name AS
    author_name, i.director_name_r, i.director_pos, i.director_pos_r,
    l.lpu_type, l.parent_lpu_id, l.lpu_code, l.oms_contract_id,
    l.dms_contract_id, l.dms_contract_tn_id, (((dmsc.contract_number)::text ||
    ' от '::text) || to_char((dmsc.sign_date)::timestamp with time zone,
    'DD.MM.YYYY'::text)) AS dms_contract_id_name,
    (((dmstnc.contract_number)::text || ' от '::text) ||
    to_char((dmstnc.sign_date)::timestamp with time zone, 'DD.MM.YYYY'::text))
    AS dms_contract_tn_id_name, (((omsc.contract_number)::text || ' от '::text)
    || to_char((omsc.sign_date)::timestamp with time zone, 'DD.MM.YYYY'::text))
    AS oms_contract_id_name, pl.name AS parent_lpu_id_name
FROM ((((((lpu.lpu l JOIN lpu.info i ON ((i.lpu_info_id = l.lpu_info_id))) LEFT
    JOIN qe_user u ON (((u.login)::text = (i.author_login)::text))) LEFT JOIN
    lpu.contract dmsc ON ((l.dms_contract_id = dmsc.contract_id))) LEFT JOIN
    lpu.contract omsc ON ((l.oms_contract_id = omsc.contract_id))) LEFT JOIN
    lpu.contract dmstnc ON ((l.dms_contract_tn_id = dmstnc.contract_id))) LEFT
    JOIN lpu.lpu pl ON ((l.parent_lpu_id = pl.lpu_id)));








--------------- SQL ---------------

CREATE TABLE "lpu"."branch" (
  "branch_id" SERIAL NOT NULL, 
  "name" VARCHAR(500) NOT NULL, 
  "code" VARCHAR(20), 
  "auto_fill" INTEGER DEFAULT 0 NOT NULL, 
  "type" INTEGER DEFAULT 0 NOT NULL, 
  PRIMARY KEY("branch_id")
) WITH OIDS;

COMMENT ON TABLE "lpu"."branch"
IS 'Отделения ЛПУ';

COMMENT ON COLUMN "lpu"."branch"."branch_id"
IS 'Ид-р отделения ЛПУ';

COMMENT ON COLUMN "lpu"."branch"."name"
IS 'Наименование отделения';

COMMENT ON COLUMN "lpu"."branch"."code"
IS 'Код отделения';

COMMENT ON COLUMN "lpu"."branch"."auto_fill"
IS 'Признак автоматического заполнения
0 - заполнено вручную (оператором)
1 - заполнено автоматически (при обработке списков)';

COMMENT ON COLUMN "lpu"."branch"."type"
IS 'Тип:
0 - Городского типа
1 - Сельского типа';


--------------- SQL ---------------

ALTER TABLE "lpu"."branch"
  ADD COLUMN "lpu_id" INTEGER;

ALTER TABLE "lpu"."branch"
  ALTER COLUMN "lpu_id" SET NOT NULL;

COMMENT ON COLUMN "lpu"."branch"."lpu_id"
IS 'Ид-р ЛПУ';


--------------- SQL ---------------

ALTER TABLE "lpu"."branch"
  ADD CONSTRAINT "branch_fk_lpu" FOREIGN KEY ("lpu_id")
    REFERENCES "lpu"."lpu"("lpu_id")
    ON DELETE CASCADE
    ON UPDATE NO ACTION
    NOT DEFERRABLE;


--------------- SQL ---------------

CREATE INDEX "branch_idx_lpu" ON "lpu"."branch"
  USING btree ("lpu_id");







--------------- SQL ---------------

ALTER TABLE "lpu"."price_section"
  ADD COLUMN "section_count" INTEGER;

ALTER TABLE "lpu"."price_section"
  ALTER COLUMN "section_count" SET DEFAULT 0;

COMMENT ON COLUMN "lpu"."price_section"."section_count"
IS 'Кол-во дочерних разделов';



--------------- SQL ---------------

ALTER TABLE "lpu"."price_section"
  ADD COLUMN "service_count" INTEGER;

ALTER TABLE "lpu"."price_section"
  ALTER COLUMN "service_count" SET DEFAULT 0;

COMMENT ON COLUMN "lpu"."price_section"."service_count"
IS 'Кол-во услуг в разделе';

--------------- SQL ---------------

CREATE FUNCTION "lpu"."price_section_tr_ariud" () RETURNS trigger AS
$body$
BEGIN
	IF (TG_OP = 'DELETE') THEN    	
    	UPDATE lpu.price_section
        SET section_count = (SELECT COUNT(*) FROM lpu.price_section ss WHERE ss.parent_id = lpu.price_section.section_id)
        WHERE section_id = OLD.parent_id;
        
    	RETURN OLD;
   	ELSIF (TG_OP = 'UPDATE') THEN
    	UPDATE lpu.price_section
        SET section_count = (SELECT COUNT(*) FROM lpu.price_section ss WHERE ss.parent_id = lpu.price_section.section_id)
        WHERE section_id = OLD.parent_id;
        
    	UPDATE lpu.price_section
        SET section_count = (SELECT COUNT(*) FROM lpu.price_section ss WHERE ss.parent_id = lpu.price_section.section_id)
        WHERE section_id = NEW.parent_id;
        
		RETURN NEW;
    ELSE -- INSERT
    	UPDATE lpu.price_section
        SET section_count = (SELECT COUNT(*) FROM lpu.price_section ss WHERE ss.parent_id = lpu.price_section.section_id)
        WHERE section_id = NEW.parent_id;
        
    	RETURN NEW;
    END IF;
    
    RETURN NULL;

END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

CREATE TRIGGER "price_section_tr_ariud" AFTER INSERT OR UPDATE OR DELETE 
ON "lpu"."price_section" FOR EACH ROW 
EXECUTE PROCEDURE "lpu"."price_section_tr_ariud"();



--------------- SQL ---------------

CREATE OR REPLACE FUNCTION "lpu"."service_tr_ariu" () RETURNS trigger AS
$body$
BEGIN
	IF (TG_OP = 'INSERT') THEN
    	-- Добавляем в историю текущую цену
		INSERT INTO lpu.service_cost(service_id, cost, start_date)
			SELECT s.service_id, s.cost, COALESCE(s.start_date, NOW())
			FROM lpu.service s 
            WHERE s.service_id = NEW.service_id;
            
    	IF NEW.section_id <> OLD.section_id THEN
	    	UPDATE lpu.price_section
    	    SET service_count = (SELECT COUNT(*) FROM lpu.service s WHERE s.section_id = lpu.price_section.section_id)
	        WHERE section_id = OLD.section_id;

    		UPDATE lpu.price_section
	        SET service_count = (SELECT COUNT(*) FROM lpu.service s WHERE s.section_id = lpu.price_section.section_id)
	        WHERE section_id = NEW.section_id;
        END IF;
            
    ELSIF (TG_OP = 'UPDATE') THEN
    	-- Обновляем значение текущий цены в истории цен, если цена изменилась
        IF NEW.cost <> OLD.cost THEN
	    	UPDATE lpu.service_cost
	        SET cost = NEW.cost
	        WHERE service_id = NEW.service_id AND finish_date IS NULL;
        END IF;
        
    	UPDATE lpu.price_section
        SET service_count = (SELECT COUNT(*) FROM lpu.service s WHERE s.section_id = lpu.price_section.section_id)
        WHERE section_id = NEW.section_id;
        
    END IF;
    
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;


--------------- SQL ---------------

CREATE FUNCTION "lpu"."service_tr_ard" () RETURNS trigger AS
$body$
BEGIN
  	UPDATE lpu.price_section
    SET service_count = (SELECT COUNT(*) FROM lpu.service s WHERE s.section_id = lpu.price_section.section_id)
    WHERE section_id = OLD.section_id;

	RETURN OLD;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

CREATE TRIGGER "service_tr_ard" AFTER DELETE 
ON "lpu"."service" FOR EACH ROW 
EXECUTE PROCEDURE "lpu"."service_tr_ard"();




--------------- SQL ---------------

CREATE OR REPLACE FUNCTION "lpu"."price_section_tr_ariud" () RETURNS trigger AS
$body$
BEGIN
	IF (TG_OP = 'DELETE') THEN    	
    	UPDATE lpu.price_section
        SET section_count = (SELECT COUNT(*) FROM lpu.price_section ss WHERE ss.parent_id = lpu.price_section.section_id)
        WHERE section_id = OLD.parent_id;
        
    	RETURN OLD;
   	ELSIF (TG_OP = 'UPDATE') THEN
    	IF NEW.parent_id <> OLD.parent_id THEN
	    	UPDATE lpu.price_section
	        SET section_count = (SELECT COUNT(*) FROM lpu.price_section ss WHERE ss.parent_id = lpu.price_section.section_id)
	        WHERE section_id = OLD.parent_id;
        
	    	UPDATE lpu.price_section
	        SET section_count = (SELECT COUNT(*) FROM lpu.price_section ss WHERE ss.parent_id = lpu.price_section.section_id)
	        WHERE section_id = NEW.parent_id;
        END IF;
        
		RETURN NEW;
    ELSE -- INSERT
    	UPDATE lpu.price_section
        SET section_count = (SELECT COUNT(*) FROM lpu.price_section ss WHERE ss.parent_id = lpu.price_section.section_id)
        WHERE section_id = NEW.parent_id;
        
    	RETURN NEW;
    END IF;
    
    RETURN NULL;

END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;




--------------- SQL ---------------

CREATE OR REPLACE FUNCTION "lpu"."service_tr_ariu" () RETURNS trigger AS
$body$
BEGIN
	IF (TG_OP = 'INSERT') THEN
    	-- Добавляем в историю текущую цену
		INSERT INTO lpu.service_cost(service_id, cost, start_date)
			SELECT s.service_id, s.cost, COALESCE(s.start_date, NOW())
			FROM lpu.service s 
            WHERE s.service_id = NEW.service_id;
            
    	UPDATE lpu.price_section
        SET service_count = (SELECT COUNT(*) FROM lpu.service s WHERE s.section_id = lpu.price_section.section_id)
        WHERE section_id = NEW.section_id;
        
    ELSIF (TG_OP = 'UPDATE') THEN
    	-- Обновляем значение текущий цены в истории цен, если цена изменилась
        IF NEW.cost <> OLD.cost THEN
	    	UPDATE lpu.service_cost
	        SET cost = NEW.cost
	        WHERE service_id = NEW.service_id AND finish_date IS NULL;
        END IF;
        
    	IF NEW.section_id <> OLD.section_id THEN
	    	UPDATE lpu.price_section
    	    SET service_count = (SELECT COUNT(*) FROM lpu.service s WHERE s.section_id = lpu.price_section.section_id)
	        WHERE section_id = OLD.section_id;

    		UPDATE lpu.price_section
	        SET service_count = (SELECT COUNT(*) FROM lpu.service s WHERE s.section_id = lpu.price_section.section_id)
	        WHERE section_id = NEW.section_id;
        END IF;
            
    END IF;
    
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;



--------------- SQL ---------------

CREATE OR REPLACE FUNCTION "lpu"."price_section_tr_bru" () RETURNS trigger AS
$body$
DECLARE 
	pPath CHAR(32);
    pNode CHAR(2);
    pMaxNode CHAR(2);
    rec lpu.price_section%ROWTYPE;
BEGIN 
	-- Если родитель не менялся, что ничего не делаем
	IF NEW.parent_id = OLD.parent_id THEN
    	RETURN NEW;
    END IF;

    --Получаем Path родительского узла
    SELECT RTRIM("path") INTO pPath FROM lpu.price_section 
        WHERE (parent_id IS NULL AND NEW.parent_id IS NULL) OR section_id = NEW.parent_id;
            
    -- Вычисление нового NodeID
    SELECT MAX(nodeid) INTO pMaxNode FROM lpu.price_section 
        WHERE section_id <> NEW.section_id AND ((parent_id IS NULL AND NEW.parent_id IS NULL) OR parent_id = NEW.parent_id);
			
    -- Устанавливаем новые NodeID и Path
    pNode := emc.next_node_id(pMaxNode, 2);
    IF (pPath IS NOT NULL) THEN
        pPath := RTRIM(pPath||'.'||pNode);
    ELSE
        pPath := pNode;   
    END IF;
        
   	NEW.nodeid := pNode;
	NEW."path" := pPath;
    
    -- TODO: обновление детей не работает
	UPDATE lpu.price_section 
		SET "path" = pPath||substr("path", LENGTH(RTRIM(OLD."path")) + 1)
		WHERE "path" LIKE RTRIM(OLD."path")||'.%';
        
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;



--------------- SQL ---------------

CREATE OR REPLACE FUNCTION "lpu"."price_section_tr_bru" () RETURNS trigger AS
$body$
DECLARE 
	pPath CHAR(32);
    pNode CHAR(2);
    pMaxNode CHAR(2);
    rec lpu.price_section%ROWTYPE;
BEGIN 

    IF OLD.service_group_id <> NEW.service_group_id THEN
	    UPDATE lpu.service 
	    SET service_group_id = NEW.service_group_id 
	    WHERE section_id = NEW.section_id AND 
	          (service_group_id = OLD.service_group_id OR service_group_id IS NULL);
    END IF;
          
  	IF OLD.activity_id <> NEW.activity_id THEN
	    UPDATE lpu.service 
	    SET activity_id = NEW.activity_id
	    WHERE section_id = NEW.section_id AND 
	          (activity_id = OLD.activity_id OR activity_id IS NULL);
    END IF;

	-- Если родитель не менялся, что ничего более не делаем
	IF NEW.parent_id = OLD.parent_id THEN
    	RETURN NEW;
    END IF;

    --Получаем Path родительского узла
    SELECT RTRIM("path") INTO pPath FROM lpu.price_section 
        WHERE (parent_id IS NULL AND NEW.parent_id IS NULL) OR section_id = NEW.parent_id;
            
    -- Вычисление нового NodeID
    SELECT MAX(nodeid) INTO pMaxNode FROM lpu.price_section 
        WHERE section_id <> NEW.section_id AND ((parent_id IS NULL AND NEW.parent_id IS NULL) OR parent_id = NEW.parent_id);
			
    -- Устанавливаем новые NodeID и Path
    pNode := emc.next_node_id(pMaxNode, 2);
    IF (pPath IS NOT NULL) THEN
        pPath := RTRIM(pPath||'.'||pNode);
    ELSE
        pPath := pNode;   
    END IF;
        
   	NEW.nodeid := pNode;
	NEW."path" := pPath;
    
    -- TODO: обновление детей не работает
	UPDATE lpu.price_section 
		SET "path" = pPath||substr("path", LENGTH(RTRIM(OLD."path")) + 1)
		WHERE "path" LIKE RTRIM(OLD."path")||'.%';
        
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;


--------------- SQL ---------------

DROP TRIGGER "price_section_tr" ON "lpu"."price_section";




--------------- SQL ---------------

CREATE OR REPLACE FUNCTION "lpu"."price_section_tr_bru" () RETURNS trigger AS
$body$
DECLARE 
	pPath CHAR(32);
    pNode CHAR(2);
    pMaxNode CHAR(2);
    rec lpu.price_section%ROWTYPE;
BEGIN 

    IF OLD.service_group_id <> NEW.service_group_id THEN
	    UPDATE lpu.service 
	    SET service_group_id = NEW.service_group_id 
	    WHERE section_id = NEW.section_id AND 
	          (service_group_id = OLD.service_group_id OR service_group_id IS NULL);
    END IF;
          
	-- Если родитель не менялся, что ничего более не делаем
	IF NEW.parent_id = OLD.parent_id THEN
    	RETURN NEW;
    END IF;

    --Получаем Path родительского узла
    SELECT RTRIM("path") INTO pPath FROM lpu.price_section 
        WHERE (parent_id IS NULL AND NEW.parent_id IS NULL) OR section_id = NEW.parent_id;
            
    -- Вычисление нового NodeID
    SELECT MAX(nodeid) INTO pMaxNode FROM lpu.price_section 
        WHERE section_id <> NEW.section_id AND ((parent_id IS NULL AND NEW.parent_id IS NULL) OR parent_id = NEW.parent_id);
			
    -- Устанавливаем новые NodeID и Path
    pNode := emc.next_node_id(pMaxNode, 2);
    IF (pPath IS NOT NULL) THEN
        pPath := RTRIM(pPath||'.'||pNode);
    ELSE
        pPath := pNode;   
    END IF;
        
   	NEW.nodeid := pNode;
	NEW."path" := pPath;
    
    -- TODO: обновление детей не работает
	UPDATE lpu.price_section 
		SET "path" = pPath||substr("path", LENGTH(RTRIM(OLD."path")) + 1)
		WHERE "path" LIKE RTRIM(OLD."path")||'.%';
        
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;


--------------- SQL ---------------

CREATE OR REPLACE FUNCTION "lpu"."price_section_tr_bru" () RETURNS trigger AS
$body$
DECLARE 
	pPath CHAR(32);
    pNode CHAR(2);
    pMaxNode CHAR(2);
    rec lpu.price_section%ROWTYPE;
BEGIN 

    IF OLD.service_group_id <> NEW.service_group_id THEN
	    UPDATE lpu.service 
	    SET service_group_id = NEW.service_group_id 
	    WHERE section_id = NEW.section_id AND 
	          (service_group_id = OLD.service_group_id OR service_group_id IS NULL);
    END IF;
          
	-- Если родитель не менялся, что ничего более не делаем
	IF NEW.parent_id == OLD.parent_id THEN
    	RETURN NEW;
    END IF;

    --Получаем Path родительского узла
    SELECT RTRIM("path") INTO pPath FROM lpu.price_section 
        WHERE (parent_id IS NULL AND NEW.parent_id IS NULL) OR section_id = NEW.parent_id;
            
    -- Вычисление нового NodeID
    SELECT MAX(nodeid) INTO pMaxNode FROM lpu.price_section 
        WHERE section_id <> NEW.section_id AND ((parent_id IS NULL AND NEW.parent_id IS NULL) OR parent_id = NEW.parent_id);
			
    -- Устанавливаем новые NodeID и Path
    pNode := emc.next_node_id(pMaxNode, 2);
    IF (pPath IS NOT NULL) THEN
        pPath := RTRIM(pPath||'.'||pNode);
    ELSE
        pPath := pNode;   
    END IF;
        
   	NEW.nodeid := pNode;
	NEW."path" := pPath;
    
    -- TODO: обновление детей не работает
	UPDATE lpu.price_section 
		SET "path" = pPath||substr("path", LENGTH(RTRIM(OLD."path")) + 1)
		WHERE "path" LIKE RTRIM(OLD."path")||'.%';
        
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;


--------------- SQL ---------------

CREATE OR REPLACE FUNCTION "lpu"."price_section_tr_au" () RETURNS trigger AS
$body$
BEGIN
    -- Если изменился вид деятельности...
    IF OLD.activity_id <> NEW.activity_id AND OLD.activity_id IS NOT NULL THEN
    	-- Меняем его для всех дочерних разделов, где был установлен такой же
		UPDATE lpu.price_section 
        SET activity_id = NEW.activity_id
		WHERE "path" LIKE RTRIM(NEW."path")||'.%' AND activity_id = OLD.activity_id;
        
        -- Меняем его для всех услуг, где был установлен такой же
        UPDATE lpu.service
        SET activity_id = NEW.activity_id
        WHERE section_id = NEW.section_id AND activity_id = OLD.activity_id;
    END IF;
    
    -- Если установлен вид деятельности...
    IF OLD.activity_id IS NULL AND NEW.activity_id IS NOT NULL THEN    
    	-- Устанавливаем его для всех дочерних разделов
		UPDATE lpu.price_section 
        SET activity_id = NEW.activity_id
		WHERE "path" LIKE RTRIM(NEW."path")||'.%' AND activity_id IS NULL;
        
        -- Устанавливаем его для всех услуг, где еще ничего не установлено
        UPDATE lpu.service
        SET activity_id = NEW.activity_id
        WHERE section_id = NEW.section_id AND activity_id IS NULL;
    END IF;


    -- Если изменился код...
    IF OLD.code <> NEW.code AND OLD.code IS NOT NULL THEN
    	-- Меняем его для всех дочерних разделов, где был установлен такой же
		UPDATE lpu.price_section 
        SET code = NEW.code
		WHERE "path" LIKE RTRIM(NEW."path")||'.%' AND activity_id = OLD.activity_id;
        
    END IF;
    
    -- Если установлен код...
    IF OLD.code IS NULL AND NEW.code IS NOT NULL THEN    
    	-- Устанавливаем его для всех дочерних разделов
		UPDATE lpu.price_section 
        SET code = NEW.code
		WHERE "path" LIKE RTRIM(NEW."path")||'.%' AND activity_id IS NULL;
        
    END IF;

        
    RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;





UPDATE lpu.price_section
SET service_count = (SELECT COUNT(*) FROM lpu.service s WHERE s.section_id = lpu.price_section.section_id),
    section_count = (SELECT COUNT(*) FROM lpu.price_section ss WHERE ss.parent_id = lpu.price_section.section_id);




--------------- SQL ---------------

CREATE FUNCTION "lpu"."update_price_section_path" (_parent_id integer) RETURNS boolean AS
$body$
DECLARE
  r RECORD;
  _path CHAR(32);
  _cur_node CHAR(2);
  
BEGIN
	IF _parent_id IS NULL THEN
	    FOR r IN SELECT * 
    			 FROM lpu.contract c
		LOOP              
			PERFORM lpu.update_price_section_path(- r.contract_id); 
		END LOOP;        
    ELSEIF _parent_id < 0 THEN
	    _path := '';
        _cur_node := '';
	    FOR r IN SELECT * 
    			 FROM lpu.price_section ps
                 WHERE ps.contract_id = - _parent_id
		LOOP              
        	_cur_node := emc.next_node_id(_cur_node, 2);
			UPDATE lpu.price_section
            SET nodeid = _cur_node,
            	path = _cur_node 
            WHERE section_id = r.section_id;
            
            PERFORM lpu.update_price_section_path(- r.section_id); 
		END LOOP;        
    ELSE
	    SELECT ps.path INTO _path
        FROM lpu.price_section ps
        WHERE ps.section_id = _parent_id;
        _cur_node := '';
        
	    FOR r IN SELECT * 
    			 FROM lpu.price_section ps
                 WHERE ps.parent_id = _parent_id
		LOOP              
        	_cur_node := emc.next_node_id(_cur_node, 2);
			UPDATE lpu.price_section
            SET nodeid = _cur_node,
            	path = _path || '.' || _cur_node 
            WHERE section_id = r.section_id;
            
            PERFORM lpu.update_price_section_path(- r.section_id); 
		END LOOP;        
    END IF; 

	RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

COMMENT ON FUNCTION "lpu"."update_price_section_path"(_parent_id integer)
IS 'Пересчитываем пути для разделов прейскуранта';





--------------- SQL ---------------

CREATE OR REPLACE FUNCTION "lpu"."update_price_section_path" (_parent_id integer) RETURNS boolean AS
$body$
DECLARE
  r RECORD;
  _path CHAR(32);
  _cur_node CHAR(2);
  
BEGIN
	IF _parent_id IS NULL THEN
	    FOR r IN SELECT * 
    			 FROM lpu.contract c
		LOOP              
			PERFORM lpu.update_price_section_path(- r.contract_id); 
		END LOOP;        
    ELSEIF _parent_id < 0 THEN
	    _path := '';
        _cur_node := '';
	    FOR r IN SELECT * 
    			 FROM lpu.price_section ps
                 WHERE ps.contract_id = - _parent_id
		LOOP              
        	_cur_node := emc.next_node_id(_cur_node, 2);
			UPDATE lpu.price_section
            SET nodeid = _cur_node,
            	path = _cur_node 
            WHERE section_id = r.section_id;
            
            PERFORM lpu.update_price_section_path(r.section_id); 
		END LOOP;        
    ELSE
	    SELECT ps.path INTO _path
        FROM lpu.price_section ps
        WHERE ps.section_id = _parent_id;
        _cur_node := '';
        
	    FOR r IN SELECT * 
    			 FROM lpu.price_section ps
                 WHERE ps.parent_id = _parent_id
		LOOP              
        	_cur_node := emc.next_node_id(_cur_node, 2);
			UPDATE lpu.price_section
            SET nodeid = _cur_node,
            	path = _path || '.' || _cur_node 
            WHERE section_id = r.section_id;
            
            PERFORM lpu.update_price_section_path(r.section_id); 
		END LOOP;        
    END IF; 

	RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;




select lpu.update_price_section_path(null);




--------------- SQL ---------------

DROP FUNCTION "lpu"."get_service_by_id"(id integer, uid integer);


--------------- SQL ---------------

DROP VIEW "lpu"."service_info";

CREATE VIEW "lpu"."service_info" (
    lpu_id,
    service_id,
    activity_id,
    code,
    name,
    cost,
    service_group_id,
    start_date,
    finish_date,
    section_id,
    activity_id_name,
    service_group_id_name,
    license_id,
    license_id_name,
    section_id_name,
    licensedate,
    contract_id)
AS
SELECT p.lpu_id, s.service_id, s.activity_id, s.code, s.name, s.cost,
    s.service_group_id, s.start_date, s.finish_date, s.section_id, a.name AS
    activity_id_name, g.name AS service_group_id_name, l.license_id, l.name AS
    license_id_name, p.name AS section_id_name, l.finish_date AS licensedate, p.contract_id
FROM ((((lpu.service s JOIN lpu.price_section p ON ((p.section_id =
    s.section_id))) LEFT JOIN lpu.activity a ON ((a.activity_id =
    s.activity_id))) LEFT JOIN lpu.service_group g ON ((g.service_group_id =
    s.service_group_id))) LEFT JOIN (
    SELECT il.license_id, il.lpu_id, il.name, il.start_date, il.finish_date,
        la.activity_id
    FROM (lpu.license il JOIN lpu.license_activity la ON ((la.license_id =
        il.license_id)))
    ORDER BY il.finish_date
    ) l ON (((l.lpu_id = p.lpu_id) AND (l.activity_id = s.activity_id))));


--------------- SQL ---------------

DROP FUNCTION "lpu"."get_section_by_id"(id integer, uid integer);



--------------- SQL ---------------

CREATE TABLE "lpu"."area" (
  "area_id" SERIAL NOT NULL, 
  "num" INTEGER NOT NULL, 
  "office_num" INTEGER, 
  PRIMARY KEY("area_id")
) WITH OIDS;

COMMENT ON TABLE "lpu"."area"
IS 'Учстки ЛПУ';

COMMENT ON COLUMN "lpu"."area"."area_id"
IS 'Ид-р участка ЛПУ';

COMMENT ON COLUMN "lpu"."area"."num"
IS 'Номер участка';

COMMENT ON COLUMN "lpu"."area"."office_num"
IS 'Номер филиала';


--------------- SQL ---------------

ALTER TABLE "lpu"."area"
  ADD COLUMN "lpu_id" INTEGER;

ALTER TABLE "lpu"."area"
  ALTER COLUMN "lpu_id" SET NOT NULL;

COMMENT ON COLUMN "lpu"."area"."lpu_id"
IS 'Ид-р ЛПУ';


--------------- SQL ---------------

CREATE INDEX "area_idx_lpu" ON "lpu"."area"
  USING btree ("lpu_id");


--------------- SQL ---------------

CREATE INDEX "area_idx_lpu_num" ON "lpu"."area"
  USING btree ("lpu_id", "num");



--------------- SQL ---------------

ALTER TABLE "lpu"."area"
  ADD CONSTRAINT "area_fk_lpu" FOREIGN KEY ("lpu_id")
    REFERENCES "lpu"."lpu"("lpu_id")
    ON DELETE CASCADE
    ON UPDATE NO ACTION
    NOT DEFERRABLE;




--------------- SQL ---------------

ALTER TABLE "oms"."person"
  ADD CONSTRAINT "person_fk_area_lpu" FOREIGN KEY ("area_lpu_d")
    REFERENCES "foms"."lpu_district"("uch_id")
    ON DELETE SET NULL
    ON UPDATE NO ACTION
    NOT DEFERRABLE;




--------------- SQL ---------------

ALTER TABLE "oms"."lpu_attach_item"
  ADD CONSTRAINT "lpu_attach_item_fk_uch" FOREIGN KEY ("uch_id")
    REFERENCES "foms"."lpu_district"("uch_id")
    ON DELETE RESTRICT
    ON UPDATE NO ACTION
    NOT DEFERRABLE;