МиСПрИС_Задание2_2_Петрова_Романова_Заболотников_9373

ON UPDATE NO ACTION

ON DELETE CASCADE

)

--------------------------------------------------НОВЫЕ ТАБЛИЦЫ-------

-----------------------------

CREATE TABLE SHD ( --Таблица Субъекта Хозяйственной Деятельности

CONSTRAINT CL_SHD FOREIGN KEY (CL_SHD)

REFERENCES classificator (id_class) MATCH SIMPLE

ON UPDATE NO ACTION

ON DELETE CASCADE

);

ALTER TABLE SHD ADD CONSTRAINT MAIN_SHD FOREIGN KEY (MAIN_SHD) REFERENCES SHD (id_SHD) MATCH SIMPLE

ON UPDATE NO ACTION

ON DELETE cascade;

CREATE TABLE GRC (--Таблица Группового Рабочего Центра

CONSTRAINT ID_CL FOREIGN KEY (id_cl)

REFERENCES classificator (id_class) MATCH SIMPLE

ON UPDATE NO ACTION

ON DELETE CASCADE

);

ALTER TABLE GRC ADD CONSTRAINT place FOREIGN KEY (place) REFERENCES SHD (id_SHD) MATCH SIMPLE

21

ON UPDATE NO ACTION

ON DELETE cascade;

CREATE TABLE enumeration --Таблица Перечислений

(

id_enum serial NOT NULL, -- ID перечисления name_enum text NOT NULL, --Имя перечисления

PRIMARY KEY (id_enum)

)

CREATE TABLE enum_value ( --Таблица значений Перечислений

short_name text, --Короткое наименование значения Перечисления name_ev text, --Наименование значения перечисления

CONSTRAINT id_enum FOREIGN KEY (id_enum)

REFERENCES enumeration (id_enum) MATCH SIMPLE

ON UPDATE NO ACTION

ON DELETE CASCADE

);

create unique index union_num_iden on enum_value(num, id_enum); --

Создадим индекс для уникальности пар (num, id_enum)

CREATE TABLE TM ( --Таблица технологического маршрута

ID_TO serial NOT NULL PRIMARY KEY, --идентификатор ТМ

FOR_PROD integer NOT NULL, --Ссылка на продукт для которого ТМ

NUM integer NOT NULL, --Номер ТМ для этого продукта

CL_TO integer NOT NULL, --Ссылка на класс Технической Операции

CL_PROF integer NOT NULL, --Ссылка на класс Профессионала

Qual integer NOT NULL,--Ссылка на необходимую квалификацию

GRC integer NOT NULL, --Ссылка на задействованный ГРЦ

T_PZ double precision NOT NULL, --Время на подготовку

T_ST double precision NOT null, --Время на ТО для штуки

CONSTRAINT CL_TO FOREIGN KEY (CL_TO)

REFERENCES classificator (id_class) MATCH SIMPLE

22

ON UPDATE NO ACTION

ON DELETE CASCADE

);

ALTER TABLE TM ADD CONSTRAINT CL_PROF FOREIGN KEY (CL_PROF) REFERENCES classificator (id_class) MATCH SIMPLE ON UPDATE NO ACTION

ON DELETE CASCADE;

ALTER TABLE TM ADD CONSTRAINT Qual FOREIGN KEY (Qual) REFERENCES ENUM_VAL (id_value) MATCH SIMPLE ON UPDATE NO ACTION

ON DELETE CASCADE;

ALTER TABLE TM ADD CONSTRAINT GRC FOREIGN KEY (GRC)

REFERENCES GRC (id_grc) MATCH SIMPLE

ON UPDATE NO ACTION

ON DELETE CASCADE;

ALTER TABLE TM ADD CONSTRAINT FOR_PROD FOREIGN KEY (FOR_PROD) REFERENCES product (id_product) MATCH SIMPLE

ON UPDATE NO ACTION

ON DELETE CASCADE;

CREATE TABLE IN_RES ( --Таблица входного ресурса

FOR_Q double precision NOT NULL); --Количество Выходного

ALTER TABLE IN_RES ADD CONSTRAINT OUT_TO FOREIGN KEY (OUT_TO) REFERENCES TM (ID_TO) MATCH SIMPLE

ON UPDATE NO ACTION

ON DELETE CASCADE;

ALTER TABLE IN_RES ADD CONSTRAINT IN_TO FOREIGN KEY (IN_TO) REFERENCES TM (ID_TO) MATCH SIMPLE

ON UPDATE NO ACTION

23

ON DELETE CASCADE;

-----------------------------------------ФУНКЦИИ----------------------

--------------------------

-----------------------------НАЙТИ ТЕХНОЛОГИЙСКИЙ МАРШРУТ-------------

--------------------------

/*функция: возвращает ТМ со списком входных ресурсов каждой операции позиции

вход: pIdEl - ид. изделия

выход: oNum – порядковый номер ТО,

IdTO – ид. ТО,

IdClTO integer,

ShNameTO – обозн. класса ТО,

NameTO – имя класса ТО,

IdGRC – ид. ГРЦ,

ShNameGRC – обозн. ГРЦ,

NameGRC – имя ГРЦ,

IdPlace – ид. места,

ShNamePlace – обозн. места,

NamePlace – имя места ,

IdProf – ид. профессии,

ShNameProf – обозн. проф.,

NameProf – имя проф.,

IdKval – ид. квалификации,

ShNameKval – обозн. квал.,

TPZ - Тпз,

TSt - Тшт,

IdInRes – ид. вх. ресурса,

ShNameInRes – обозн. вх. ресурса,

NameInRes – имя вх. ресурса,

IdClInRes – ид. класса вх. ресурса,

ShNameClInRes – обозн. класса вх. ресурса,

NameClInRes – имя класса вх ресурса,

Q – расход ресурса,

ForQ – для количества,

эффекты: 1) при отсутствии входных ресурсов все выходные равны

NULL,

2) при отсутствии изделия все выходные равны NULL,

24

требования: изделие с ид. pIdEl должно быть в PRODUCT*/

create or replace function FIND_TM ( PIDEL integer)

returns table( IdTO integer, NUM integer, IDClTO integer, NAMETO text, IDGRC integer, SHNAMEGRC text, NAMEGRC text,

IDPLACE integer,

SHNAMEPLACE text,

NAMEPLACE text,

IDPROF integer,

NAMEPROF text,

IDKVAL integer,

SHNAMEKVAL text,

TPZ double precision,

TST double precision,

IDINRES integer,

NAMEINRES text,

IDCLINRES integer,

NAMECLINRES text,

Q double precision,

FORQ double precision

)

as $$

declare VYESTM int; declare VIDINTO int; declare RES INT; declare f record; declare f2 record; begin

select count(*) from TM

where FOR_PROD = PIDEL

25

into Res;

if (Res > 0) then

/*Для каждой операции ТМ найти по ид. имена и обозначения*/

for f in select P1.ID_TO, P1.NUM, P1.CL_TO, P1.CL_PROF, P1.qual, P1.GRC, P1.T_PZ, P1.T_ST

from TM P1

where P1.FOR_PROD = PIDEL order by P1.NUM

loop

IDTO := F.id_TO;

NUM := F.NUM;

IDCLTO := F.CL_TO;

IDPROF := F.CL_PROF;

IDKVAL := F.QUAL;

IDGRC := F.GRC;

TPZ := f.T_PZ; TST := F.T_ST; select name_class

from classificator where ID_CLASS = F.CL_TO into NAMETO;

select name_class from classificator

where ID_CLASS = F.CL_PROF into NAMEPROF;

select SHORT_NAME from ENUM_VALUE

where ID_VALUE = F.QUAL into SHNAMEKVAL;

select SHORT_NAME, GRC_NAME, PLACE from GRC

where ID_GRC = F.GRC

into SHNAMEGRC, NAMEGRC, IDPLACE;

select SHORT_NAME, SHD_NAME

26

from SHD

where ID_SHD = IDPLACE

into SHNAMEPLACE, NAMEPLACE;

for f2 in select IN_TO, R.Q, R.FOR_Q from IN_RES as R

where OUT_TO = IDTO loop

VIDINTO := F2.IN_TO;

Q := F2.Q;

FORQ := F2.FOR_Q; select P2.FOR_PROD

from TM P2

where P2.ID_TO = VIDINTO into IDINRES;

select name_product, id_class from PRODUCT

where id_product = IDINRES into NAMEINRES, IDCLINRES;

select name_class from classificator

where ID_CLASS = IDCLINRES into NAMECLINRES;

return next; end loop;

end loop;

else

RES = 0; return next;

end if;

end $$ LANGUAGE plpgsql;

--------------------------------------НОРМЫ МАТЕРИАЛЬНЫХ РЕСУРСОВ-----

-------------------------

/* Функция: НОРМЫ МАТЕРИАЛЬНЫХ РЕСУРСОВ ДЛЯ ИД ИЗДЕЛИЯ Вход: pIdTO - ид. е операции,

27

pQ - для количества,

Выход: oInEl - ид. входного изделия, oQ - требуемое количество,

oRes - наличие ошибки 0, отсутствие ошибки > 0

Эффекты: 1) При отсутствии изделия с ид. pIdEl вых. параметры равны

0, oRes=0;

Требования: Наличие в PROD изделия с ид. pIdEl */

create or replace function MR_Q ( PIDTO integer, PQ double precision)

returns TABLE( OINEL integer, OQ double precision, oRes integer) as $$

declare VINEL integer; declare vIdEl integer; declare vYesTM integer; declare vYesRes integer; declare VQ double precision; declare VVQ double precision;

declare vForQ double precision; declare VVINEL integer; declare vInTO integer;

declare vIdTO integer;

declare f record; begin

/*Находим ид. изделия для pIdTO */ select FOR_PROD from TM

where ID_TO= pIdTO into vIdEl;

if(vIdEl > 0) then vYesTM=1;

end if;

select count(*) from IN_RES where OUT_TO = pIdTO into vYesRes;

if (vYesTM > 0) then oRes:=1;

if( vYesRes > 0) then

28

/*Для каждого входного ресурса pIdTO*/

for F IN select IN_TO,Q,FOR_Q from IN_RES where OUT_TO=pIdTO

loop

VINTO := F.IN_TO;

VQ := F.Q;

VFORQ := F.FOR_Q;

vInEl:=0;

vvQ:=0;

select p2.FOR_PROD from TM p2 where p2.ID_TO = vInTO into vInEl;

if (vInEl>0) then vvQ:=vQ*pQ/vForQ; oQ:=vvQ; oInEl:=vInEl; return next;

end if; end loop;

else oInEl:=0;

oQ:=0; return next;

end if; else

oInEl := 0; oQ := 0; return next;

end if; end

$$ LANGUAGE plpgsql;

----------------------------------СВОДНЫЕ НОРЫ МАТЕРИАЛЬНЫХ РЕСУРСОВ--

----------------------------

/*функция: СВОДНЫЕ НОРМЫ МАТ. РЕСУРСОВ ДЛЯ ИД ИЗДЕЛИЯ вход: pIdEl - ид. исходного изделия,

выход: oInEl - ид. входного ресурса

29

oQ - расход

oRes - 0 - ошибка, > 0- без ошибки

эффекты: 1) если изделия с ид. pIdEl нет, то oInEl=0, oQ=0, oRes=0;

2) если изделия с ид. pIdEl не имеет вх. ресурсов, то oInEl=0, oQ=0, oRes=1;

требования:1)изделие с ид. pIdEl должно существовать в TM */

create or replace function SUM_MR(pIdEl integer, pQ double precision) returns table (oInEl integer, oQ double precision, oRes integer)

as $$

declare vIdTO integer; declare vRes integer;

declare f record; declare F2 RECORD; declare F3 RECORD; begin

select count(*) from PRODUCT where id_product = pIdEl into oRes;

if(oRes>0) then

for F in select ID_TO from TM where FOR_PROD = pIdEl order by NUM desc

loop

VIDTO = F.ID_TO;

for F2 IN select FUNC.oInEl as A,SUM(FUNC.oQ) as SUMQ, FUNC.oRes as ORE from MR_Q(vIdTO,pQ) as FUNC

group by A,FUNC.oRes

loop oInEl:=F2.A; oQ := F2.SUMQ;

vRes := F2.ORE;

if(oInEl <> pIdEl and oInEl > 0) then return next;

for F3 in select FOO.oInEl as A, SUM(FOO.oQ) as SUMQ, FOO.oRes as ORE from SUM_MR(oInEl, oQ) as FOO

30