CREATE OR REPLACE PROCEDURE START_PRODUCT(TYPE_PR IN NUMBER, NOTE IN VARCHAR2)
IS
BEGIN
insert into product(status,status_str,start_date,id_type,notes) values (0,'Запуск',sysdate,TYPE_PR,NOTE);
END START_PRODUCT;
/



CREATE OR REPLACE PROCEDURE GO_MAN(ID_PR IN NUMBER)
IS
        id_lp NUMBER;
        type_id NUMBER;
        op_name VARCHAR2(40);
        note VARCHAR2(200);
        stat NUMBER:=0;
BEGIN
select id_type into type_id from product where id_product=ID_PR;
select notes into note from product where id_product=ID_PR;
select status into stat from product where id_product=ID_PR;
if(stat=0) then
  select id_listop into id_lp  from tp where id_type=type_id and nom=1;
  select name into op_name from list_of_operation where id_listop=id_lp;
  insert into operations(id_listop, id_product, op_status, op_start_date, op_status_str) values (id_lp,ID_PR, 0, sysdate, 'Начата');
  update product set status=1, status_str=op_name where id_product=ID_PR;
end if;
END GO_MAN;
/


CREATE OR REPLACE PROCEDURE DELETE_PRODUCT(ID_PR IN NUMBER)
IS
  status NUMBER;
BEGIN
select status into status from product where id_product=ID_PR;
if(status>=0) then
  update operations set op_status=-2, op_status_str='Прервана', op_end_date=sysdate where id_product=ID_PR and id_listop=(select id_listop from tp join product on product.id_type=tp.id_type and product.status=tp.nom where product.id_product=ID_PR);
  update product set status=-2, status_str='Удалено',end_date=sysdate, notes='"Удалено на операции '||(select status_str||'";'||notes from product where id_product=ID_PR) where id_product=ID_PR;
end if;
END DELETE_PRODUCT;
/


CREATE OR REPLACE FUNCTION CHECK_OP(ID_OP IN NUMBER) RETURN NUMBER
IS
  status NUMBER;
  id_staff NUMBER;
BEGIN
select op_status into status from operations where id_operation=ID_OP;
if(status>=1) then
  select id_staff into id_staff from operations where id_operation=ID_OP;
  if(id_staff is NULL) then
    return 0;
  else
    return 1;
  end if;
else
  return 0;
end if;
END CHECK_OP;
/


CREATE OR REPLACE PROCEDURE NEXT_OP(ID_OP IN NUMBER)
IS
  status NUMBER;
  pr NUMBER;
  typ NUMBER;
  nom NUMBER;
  id_list NUMBER;
  name_op VARCHAR2(40);
  nomr NUMBER;
  nom_max NUMBER;
BEGIN
select op_status into status from operations where id_operation=ID_OP;
if(status=2) then
  update operations set op_end_date=sysdate, op_status=3,op_status_str='Завершена' where id_operation=ID_OP;
  select id_product into pr from operations where id_operation=ID_OP;
  select id_type into typ from product where id_product=pr;
  select status into nomr from product where id_product=pr;
  select max(nom) into nom_max from TP where id_type=typ; 
  if(nomr=nom_max) then
    update product set status=1000, status_str='Сделано',end_date=sysdate where id_product=pr;  
  else
	select id_listop into id_list from TP where nom=(select status from product where id_product=pr)+1 and id_type=typ;
    select name into name_op from list_of_operation where id_listop=id_list;
    update product set status=status+1, status_str=name_op where id_product=pr;
    insert into operations(id_listop, op_status, op_status_str, op_start_date,id_product) values (id_list, 0, 'Начата', sysdate,pr);
  end if;
end if;
END NEXT_OP;
/


CREATE OR REPLACE PROCEDURE DO_BRAK(ID_OP IN NUMBER)
IS
  st VARCHAR2(40);
BEGIN
  update operations set op_status=-1 where id_operation=ID_OP;
  select name into st from list_of_operation where id_listop in (select id_listop from operations where id_operation=ID_OP);
  update product set status=-1, notes='Брак на операции '||st where id_product=(select id_product from operations where id_operation=ID_OP);
END DO_BRAK;
/

CREATE OR REPLACE PROCEDURE find_frame_exmpl
       (id_frame IN INTEGER,
	   id_slot IN INTEGER,
	   slt_value VARCHAR2)
AS
       cur INTEGER;       
       ret INTEGER;       
       str VARCHAR2(250); 
	   id_exmpl INTEGER;
	   cur_delete INTEGER;       
       ret_delete INTEGER; 
	   str_delete VARCHAR2(250);
BEGIN
   str :=  'SELECT DISTINCT(ID_EXMPL) FROM TMP WHERE ID_EXMPL NOT IN (SELECT DISTINCT(ID_EXMPL) FROM TMP WHERE ( (ID_SLOTS = '||id_slot||') and (SLT_VALUE = '''||slt_value||''') and (id_frames='||id_frame|| ' )))';
    cur := dbms_sql.open_cursor;
    dbms_sql.parse(cur, str, dbms_sql.v7);
    dbms_sql.define_column(cur,1,id_exmpl);
    ret := dbms_sql.execute(cur);
 LOOP
    EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0;
    dbms_sql.column_value(cur,1,id_exmpl);
     cur_delete := dbms_sql.open_cursor;
   str_delete := 'DELETE FROM TMP WHERE ID_EXMPL = '||id_exmpl;
    dbms_sql.parse(cur_delete, str_delete, dbms_sql.v7);
   ret_delete := dbms_sql.execute(cur_delete);
    dbms_sql.close_cursor(cur_delete);

 END LOOP;
dbms_sql.close_cursor(cur);
END find_frame_exmpl;