create or replace package         recBO AS

  FUNCTION prelievo(sbarcode IN VARCHAR2,   qtp IN NUMBER,   suser IN VARCHAR2 DEFAULT NULL,   sdate IN DATE DEFAULT NULL) RETURN NUMBER;

  PROCEDURE registermol(ppactive IN OUT chemdb_moltable.pactive%TYPE,   mole IN CLOB,   plabbbok IN chemdb_moltable.labbook%TYPE,   pprogram IN chemdb_moltable.PROGRAM %TYPE,
    pcode IN chemdb_moltable.code%TYPE,   psinonimo IN chemdb_moltable.sinonimo%TYPE,   pmnote IN chemdb_moltable.mnote%TYPE,   pmolname IN chemdb_moltable.molname%TYPE);

  procedure registerprod(pprodcode in out compound.PRODTYPE%TYPE, pcorpid in compound.CORPID%TYPE, ppactive in compound.PACTIVE%TYPE, psaltcode in compound.SALTCODE%TYPE, pcmpdname compound.CMPDNAME%TYPE,
    pmolweight in compound.MOLWEIGHT%TYPE, pmolformula in compound.MOLFORMULA%TYPE, pprodtype in compound.PRODTYPE%TYPE, ppatent in compound.PATENT%TYPE, pCCOMMENT in COMPOUND.CCOMMENT%TYPE, ppatexample in compound.PATEXAMPLE%TYPE,
    pcas in compound.CAS%TYPE, ponlinesearch in compound.ONLINESEARCH%TYPE, pbiblio in compound.BIBLIO%TYPE, pstereocomment in compound.STEREOCOMMENT%TYPE, pfontid in compound.FONTID%TYPE, pscode in compound.SCODE%TYPE);

  procedure registerbatch(pbatchcode in out batch.BATCHCODE%TYPE, pPRODCODE in batch.PRODCODE%TYPE, pBCODE in batch.BCODE%TYPE, pLABNOTEBOOK in batch.LABNOTEBOOK%TYPE, pAUTHOR batch.AUTHOR%TYPE, pBCOMMENT batch.BCOMMENT%TYPE,
    pSOK in batch.SOK%TYPE, pPERCENTYIELD in batch.PERCENTYIELD%TYPE, pPHAR in batch.PHAR%TYPE, pPLATE in batch.PLATE%TYPE, pWELL in batch.WELL%TYPE, pAMOUNT in batch.AMOUNT%TYPE);

  procedure registerinvio(pBARCODE in out invio.BARCODE%TYPE,
    pBATCHCODE in invio.BATCHCODE%TYPE, 
    pDATA in invio.DATA%TYPE,
    pRECEIVER in invio.RECEIVER%TYPE,
    pNOTES in invio.NOTES%TYPE,
    pQTINIZIALE in invio.QTINIZIALE%TYPE,
    pQTATTUALE in invio.QTATTUALE%TYPE,
    pTARA in invio.TARA%TYPE,
    pISEMPTY in invio.ISEMPTY%TYPE,
    pplate in invio.plate%TYPE := null,
    pwell in invio.well%TYPE := null
);

  procedure delete(pk in varchar, entity in integer);

  procedure registerstorage(pPRODUCT_ID in out storage.PRODUCT_ID%TYPE, pPACTIVE in storage.PACTIVE%TYPE, pAMOUNT in storage.AMOUNT%TYPE, pARMADIO in storage.ARMADIO%TYPE, pPOSIZIONE in storage.POSIZIONE%TYPE,
    pBOTTLE_NO in storage.BOTTLE_NO%TYPE, pBOX in storage.BOX%TYPE);

  PROCEDURE registerprop(ppactive IN calc_prop.pactive%TYPE,  ppropname IN calc_prop.propname%TYPE, ppropvalue IN calc_prop.propvalue%TYPE, isnew in integer);

  procedure registerlip(ppactive in LIPINSKY_DRUGABILITY.pactive%TYPE, pLIPINSKI_HBOND_ACCEPTOR in LIPINSKY_DRUGABILITY.LIPINSKI_HBOND_ACCEPTOR%TYPE,
    pLIPINSKI_HBOND_DONOR in LIPINSKY_DRUGABILITY.LIPINSKI_HBOND_DONOR%TYPE, pALOGP98 in LIPINSKY_DRUGABILITY.ALOGP98%TYPE,
    pADME_PSA_2D in LIPINSKY_DRUGABILITY.ADME_PSA_2D%TYPE, pMOLWEIGHT in LIPINSKY_DRUGABILITY.MOLWEIGHT%TYPE,
    pLIPINSKI_VIOLATIONS in LIPINSKY_DRUGABILITY.LIPINSKI_VIOLATIONS%TYPE, pROTLBONDS in LIPINSKY_DRUGABILITY.ROTLBONDS%TYPE,
    pADME_BBB_2D in LIPINSKY_DRUGABILITY.ADME_BBB_2D%TYPE, pADME_SOLUBILITY in LIPINSKY_DRUGABILITY.ADME_SOLUBILITY%TYPE, isnew in integer);
    
  function dupcheck(pmole in clob) return chemdb_moltable.pactive%type; 
  
  function registersalt(pscode in salts.scode%type, pSNAME in salts.SNAME%type, pmolweight in salts.molweight%type, pMOLFORMULA in salts.MOLFORMULA%type, pATTIVO in salts.ATTIVO%type) return integer;
    
end recBO ;
/

create or replace package body         recBO AS

 function prelievo(sbarcode in varchar2, qtp in number, suser in varchar2 default null, sdate in date default null) return number is
 muser varchar2(50);
 mdate date;
 res number;

 begin

   if suser is null then
     muser := user;
   else
     muser := suser;
   end if;

   if sdate is null then
     mdate := sysdate;
   else
     mdate := sdate;
   end if;

   insert into prelievi (barcode, qt, data, userid) values (sbarcode, qtp, mdate, muser);
   update invio set QTATTUALE = QTATTUALE - qtp where barcode = sbarcode returning QTATTUALE into res;

   return res;

 end;

 function dupcheck(pmole in clob) return chemdb_moltable.pactive%type as
  cursor curo is select pactive from chemdb_moltable where flexmatch(ctab, pmole, 'All')=1;
  retv chemdb_moltable.pactive%type;
 begin
  if pmole is null then -- no structure
    retv := 0;
  else
  open curo;
    fetch curo into retv;
    if curo%notfound then
      retv := 0;
    end if;
    close curo;
  end if;
  return retv;
 end;

 procedure registermol(ppactive IN OUT chemdb_moltable.pactive%TYPE,   mole IN CLOB,   plabbbok IN chemdb_moltable.labbook%TYPE,   pprogram IN chemdb_moltable.PROGRAM %TYPE,
    pcode IN chemdb_moltable.code%TYPE, psinonimo in chemdb_moltable.SINONIMO%type, pmnote in chemdb_moltable.MNOTE%type, pmolname in chemdb_moltable.MOLNAME%type) is
  begin
   if ppactive is null or ppactive < 1 then -- new record
     ppactive := getPA;
     insert into chemdb_mols (ctab, pactive, labbook, program, code, sinonimo, mnote, molname)
       values (mol(mole), ppactive, plabbbok, pprogram, pcode, psinonimo, pmnote, pmolname);
   else
     update chemdb_mols set ctab=mol(mole), labbook=plabbbok, program=pprogram, code=pcode, sinonimo=psinonimo, mnote=pmnote, molname=pmolname where pactive=ppactive ;
   end if;
 end;

 procedure registerprod(pprodcode in out compound.PRODTYPE%TYPE, pcorpid in compound.CORPID%TYPE, ppactive in compound.PACTIVE%TYPE, psaltcode in compound.SALTCODE%TYPE, pcmpdname compound.CMPDNAME%TYPE,
    pmolweight in compound.MOLWEIGHT%TYPE, pmolformula in compound.MOLFORMULA%TYPE, pprodtype in compound.PRODTYPE%TYPE, ppatent in compound.PATENT%TYPE, pCCOMMENT in COMPOUND.CCOMMENT%TYPE , ppatexample in compound.PATEXAMPLE%TYPE,
    pcas in compound.CAS%TYPE, ponlinesearch in compound.ONLINESEARCH%TYPE, pbiblio in compound.BIBLIO%TYPE, pstereocomment in compound.STEREOCOMMENT%TYPE, pfontid in compound.FONTID%TYPE, pscode in compound.SCODE%TYPE) is
  begin
   if pprodcode is null or pprodcode < 1 then -- new record
     pprodcode := GetRec;
     insert into compound (prodcode, corpid, pactive, saltcode, cmpdname, molweight, molformula, prodtype, patent, CCOMMENT, patexample, cas, onlinesearch, biblio, stereocomment, fontid, scode)
       values (pprodcode, pcorpid, ppactive, psaltcode, pcmpdname, pmolweight, pmolformula, pprodtype, ppatent, pCCOMMENT, ppatexample, pcas, ponlinesearch, pbiblio, pstereocomment, pfontid, pscode);
   else
     update compound set corpid=pcorpid, pactive=ppactive, saltcode=psaltcode, cmpdname=pcmpdname, molweight=pmolweight, molformula=pmolformula, prodtype=pprodtype,
       patent=ppatent, CCOMMENT=pCCOMMENT, patexample=ppatexample, cas=pcas, onlinesearch=ponlinesearch, biblio=pbiblio, stereocomment=pstereocomment, fontid=pfontid, scode=pscode where prodcode=pprodcode ;
   end if;
 end;

  procedure registerbatch(pbatchcode in out batch.BATCHCODE%TYPE, pPRODCODE in batch.PRODCODE%TYPE, pBCODE in batch.BCODE%TYPE, pLABNOTEBOOK in batch.LABNOTEBOOK%TYPE, pAUTHOR batch.AUTHOR%TYPE, pBCOMMENT batch.BCOMMENT%TYPE,
    pSOK in batch.SOK%TYPE, pPERCENTYIELD in batch.PERCENTYIELD%TYPE, pPHAR in batch.PHAR%TYPE, pPLATE in batch.PLATE%TYPE, pWELL in batch.WELL%TYPE, pAMOUNT in batch.AMOUNT%TYPE) is
  begin
   if pbatchcode is null or pbatchcode < 1 then -- new record
     insert into batch (PRODCODE, BCODE, LABNOTEBOOK, AUTHOR, BCOMMENT, SOK, PERCENTYIELD, PHAR, PLATE, WELL, AMOUNT)
       values (pPRODCODE, pBCODE, pLABNOTEBOOK, pAUTHOR, pBCOMMENT, pSOK, pPERCENTYIELD, pPHAR, pPLATE, pWELL, pAMOUNT) returning batchcode into pbatchcode;
   else
     update batch set PRODCODE=pPRODCODE, BCODE=pBCODE, LABNOTEBOOK=pLABNOTEBOOK, AUTHOR=pAUTHOR, BCOMMENT=pBCOMMENT, SOK=pSOK, PERCENTYIELD=pPERCENTYIELD, PHAR=pPHAR,
       PLATE=pPLATE, WELL=pWELL, AMOUNT=pAMOUNT where BATCHCODE=pBATCHCODE ;
   end if;
 end;

  procedure registerinvio(pBARCODE in out invio.BARCODE%TYPE,
    pBATCHCODE in invio.BATCHCODE%TYPE, 
    pDATA in invio.DATA%TYPE,
    pRECEIVER in invio.RECEIVER%TYPE,
    pNOTES in invio.NOTES%TYPE,
    pQTINIZIALE in invio.QTINIZIALE%TYPE,
    pQTATTUALE in invio.QTATTUALE%TYPE,
    pTARA in invio.TARA%TYPE,
    pISEMPTY in invio.ISEMPTY%TYPE,
    pplate in invio.plate%TYPE := null,
    pwell in invio.well%TYPE := null
) is
  tmpData date;
  begin
   if pBARCODE is null then -- new record
     if pdata is null then
       tmpData := sysdate;
     else
       tmpData := pDATA;
     end if;
     insert into invio (BATCHCODE, DATA, RECEIVER, NOTES, QTINIZIALE, QTATTUALE, TARA, ISEMPTY, plate, well)
       values (pBATCHCODE, tmpData, pRECEIVER, pNOTES, pQTINIZIALE, pQTATTUALE, pTARA, pISEMPTY, pplate, pwell)
       returning barcode into pBARCODE;
   else
     update invio set BATCHCODE=pBATCHCODE, /*DATA=pDATA, */RECEIVER=pRECEIVER, NOTES=pNOTES, QTINIZIALE=pQTINIZIALE, QTATTUALE=pQTATTUALE, TARA=pTARA,
       ISEMPTY=pISEMPTY, plate=pplate, well=pwell where BARCODE=pBARCODE ;
   end if;
 end;

 procedure delete(pk in varchar, entity in integer) is
  begin
   if entity = 1 then
     delete from chemdb_mols where pactive = to_number(pk);
   elsif entity = 2 then
     delete from compound where prodcode = to_number(pk);
   elsif entity = 3 then
     delete from batch where batchcode = to_number(pk);
   elsif entity = 4 then
     delete from invio where barcode = pk;
   elsif entity = 5 then
     delete from storage where product_id = to_number(pk);
   elsif entity = 7 then
     delete from LIPINSKY_DRUGABILITY where pactive = to_number(pk);
   elsif entity = 8 then
     delete from SALTS where scode = pk;
   elsif entity <0 then
     delete from calc_prop where pactive = -entity and propname=pk;
   end if;
 end;

 procedure registerstorage(pPRODUCT_ID in out storage.PRODUCT_ID%TYPE, pPACTIVE in storage.PACTIVE%TYPE, pAMOUNT in storage.AMOUNT%TYPE, pARMADIO in storage.ARMADIO%TYPE, pPOSIZIONE in storage.POSIZIONE%TYPE,
    pBOTTLE_NO in storage.BOTTLE_NO%TYPE, pBOX in storage.BOX%TYPE) is
  begin
   if pPRODUCT_ID is null or pPRODUCT_ID < 1 then -- new record
     insert into storage (PACTIVE, AMOUNT, ARMADIO, POSIZIONE, BOTTLE_NO, BOX)
       values (pPACTIVE, pAMOUNT, pARMADIO, pPOSIZIONE, pBOTTLE_NO, pBOX) returning PRODUCT_ID into pPRODUCT_ID;
   else
     update storage set PACTIVE=pPACTIVE, AMOUNT=pAMOUNT, ARMADIO=pARMADIO, POSIZIONE=pPOSIZIONE, BOTTLE_NO=pBOTTLE_NO, BOX=pBOX where PRODUCT_ID=pPRODUCT_ID ;
   end if;
  end;

  PROCEDURE registerprop(ppactive IN calc_prop.pactive%TYPE,  ppropname IN calc_prop.propname%TYPE, ppropvalue IN calc_prop.propvalue%TYPE, isnew in integer) is
  begin
    if isnew = 0 then --update
      update calc_prop set propvalue = ppropvalue where pactive = ppactive and propname = ppropname;
    else --insert
      insert into calc_prop (pactive, propname, propvalue) values (ppactive, ppropname, ppropvalue);
    end if;
  end;
  
  procedure registerlip(ppactive in LIPINSKY_DRUGABILITY.pactive%TYPE, pLIPINSKI_HBOND_ACCEPTOR in LIPINSKY_DRUGABILITY.LIPINSKI_HBOND_ACCEPTOR%TYPE,
    pLIPINSKI_HBOND_DONOR in LIPINSKY_DRUGABILITY.LIPINSKI_HBOND_DONOR%TYPE, pALOGP98 in LIPINSKY_DRUGABILITY.ALOGP98%TYPE,
    pADME_PSA_2D in LIPINSKY_DRUGABILITY.ADME_PSA_2D%TYPE, pMOLWEIGHT in LIPINSKY_DRUGABILITY.MOLWEIGHT%TYPE,
    pLIPINSKI_VIOLATIONS in LIPINSKY_DRUGABILITY.LIPINSKI_VIOLATIONS%TYPE, pROTLBONDS in LIPINSKY_DRUGABILITY.ROTLBONDS%TYPE,
    pADME_BBB_2D in LIPINSKY_DRUGABILITY.ADME_BBB_2D%TYPE, pADME_SOLUBILITY in LIPINSKY_DRUGABILITY.ADME_SOLUBILITY%TYPE, isnew in integer) is
  begin
    if isnew = 0 then --update
      update LIPINSKY_DRUGABILITY set LIPINSKI_HBOND_ACCEPTOR = pLIPINSKI_HBOND_ACCEPTOR, LIPINSKI_HBOND_DONOR = pLIPINSKI_HBOND_DONOR,
        ALOGP98 = pALOGP98, ADME_PSA_2D = pADME_PSA_2D,
        MOLWEIGHT = pMOLWEIGHT, LIPINSKI_VIOLATIONS = pLIPINSKI_VIOLATIONS,
        ROTLBONDS = pROTLBONDS, ADME_BBB_2D = pADME_BBB_2D,
        ADME_SOLUBILITY = pADME_SOLUBILITY  where pactive = ppactive;
    else --insert
      insert into LIPINSKY_DRUGABILITY (pactive, LIPINSKI_HBOND_ACCEPTOR, LIPINSKI_HBOND_DONOR, ALOGP98, ADME_PSA_2D, MOLWEIGHT, LIPINSKI_VIOLATIONS,
        ROTLBONDS, ADME_BBB_2D, ADME_SOLUBILITY) values (ppactive, pLIPINSKI_HBOND_ACCEPTOR, pLIPINSKI_HBOND_DONOR, pALOGP98, pADME_PSA_2D, pMOLWEIGHT,
        pLIPINSKI_VIOLATIONS, pROTLBONDS, pADME_BBB_2D, pADME_SOLUBILITY);
    end if;  
  end;

  function registersalt(pscode in salts.scode%type, pSNAME in salts.SNAME%type, pmolweight in salts.molweight%type, pMOLFORMULA in salts.MOLFORMULA%type, pATTIVO in salts.ATTIVO%type) return integer AS
  cursor appo is select scode from salts where pscode=scode for update;
  zecode salts.scode%type;
  reto integer := 0;
  BEGIN
    open appo;
    fetch appo into zecode;
    if appo%found then
      update salts set
        SNAME = psname,
        MOLWEIGHT = pmolweight,
        MOLFORMULA = pmolformula,
        ATTIVO = pattivo
      where current of appo;
    else
      insert into salts (
        SCODE,
        SNAME,
        MOLWEIGHT,
        MOLFORMULA,
        ATTIVO 
      ) values (
        pscode,
        psname,
        pmolweight,
        pmolformula,
        pattivo
      );
      reto := 1;
    end if;
    close appo;
    return reto;
  END registersalt;

end recBO ;
/