REM Copyright (c) 2003 Konrad Buessow

REM This program is free software; you can redistribute and/or modify 
REM it under the terms of the GNU General Public Licence as published by
REM the Free Software Foundation; eiher version 2, or (at your option) any
REM later version.

REM This program is distributed in the hope that it will be useful, but 
REM WITHOUT ANY WARRANTY; without even the implied warranty of 
REM MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
REM GNU General Public Licence for more details. 

REM You should have received a copy of the GNU General Public Licence 
REM along with this program; if not, write to the Free Software 
REM Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA


-- 
-- VIEW: LIGATION_AND_PCRTEMPLATES 
--

CREATE OR REPLACE VIEW LIGATION_AND_PCRTEMPLATES
(LIGATIONID, DATE_, INSERTCLONEID, PCREXPID, FOUNDOKCLONE, INSERTCLONEWRONG, PREDICTEDPEPTIDESIZE, RE1VECTOR, RE2VECTOR, RE1INSERT, RE2INSERT, VECTORID, TRANSFORMANTSTESTED, TRANSFORMANTSINSERT, COMMENT_, INSERT_IS_RE_FRAGMENT, EXPERIMENTATORID) AS
select
l.ligationid, l.date_, l.insertcloneid, l.pcrexpid,
l.foundokclone, l.insertclonewrong, l.predictedpeptidesize,
l.re1vector, l.re2vector, l.re1insert, l.re2insert, l.vectorid,
l.transformantstested, l.transformantsinsert, l.comment_,
l.insert_is_re_fragment, l.experimentatorid
from ligation l where l.pcrexpid is null
union
select
l.ligationid, l.date_, p.templatecloneid as insertcloneid, l.pcrexpid,
l.foundokclone, l.insertclonewrong, l.predictedpeptidesize,
l.re1vector, l.re2vector, l.re1insert, l.re2insert, l.vectorid,
l.transformantstested, l.transformantsinsert, l.comment_,
l.insert_is_re_fragment, l.experimentatorid
from ligation l, pcrexperiment p
where l.pcrexpid = p.pcrexperimentid
;

-- 
-- VIEW: LIG_TRAFO 
--

CREATE OR REPLACE VIEW LIG_TRAFO
(LIGATIONID, DATE_, INSERTCLONEID, PCREXPID, FOUNDOKCLONE, INSERTCLONEWRONG, PREDICTEDPEPTIDESIZE, RE1VECTOR, RE2VECTOR, RE1INSERT, RE2INSERT, VECTORID, TRANSFORMANTSTESTED, TRANSFORMANTSINSERT, COMMENT_, INSERT_IS_RE_FRAGMENT, EXPERIMENTATORID, TRAFO_ID, TRAFO_DATE, METHOD_ID, TRANSFORMANTS_NO, LIGATION_ID, PLASMID_CLONEID_SOURCE, EXPERIMENT_ID, HELPERPLASMID_ID, STRAINID, LIGTRAFO_NO, EXPERIMENTATOR_ID) AS
select
  l.*, t.*
from
  ligation_and_pcrtemplates l, transformations t
where
  l.ligationid (+) = t.ligation_id
order by t.experiment_id asc, t.ligtrafo_no asc
;

-- 
-- VIEW: PAGELANES_CLONENAME 
--

CREATE OR REPLACE VIEW PAGELANES_CLONENAME
(ID, PAGE, LANENO, VOLUME, CLONEID, DESCRIPTION, INDEXCLONENAME) AS
SELECT PL.*, CI.INDEXCLONENAME
FROM PAGELANES PL, CLONEINDEX CI
where ci.cloneid (+) = pl.cloneid
;

-- 
-- VIEW: PLABO_CONC_ETC 
--

CREATE OR REPLACE VIEW PLABO_CONC_ETC
(PLATESBOXESID, PLATESBOXESADMINID, PLATE_NUMBER, ROW_, COLUMN_, VOLUME_UL, ITEM, OD260, CONCENTRATION, DNA_ACTIONID) AS
select 
p.platesboxesid, 
p.platesboxesadminid, 
p.plate_number, 
p.row_, 
p.column_,
p.volume_ul, 
plabo.plabo_itemname(p.platesboxesid) ITEM,
daq.od260, 
daq.concentration, 
daq.dna_actionid
from
platesboxes p,
(
select dq.*, da.plaboid, da.timestamp
from dnaquantification dq, dna_action da
where dq.dna_actionid = da.dnaactionid
) daq,
(
select max(da.timestamp) maxtime, da.plaboid
from dnaquantification dq, dna_action da
where dq.dna_actionid = da.dnaactionid
group by da.plaboid
) daqt
where daq.plaboid  (+) = p.platesboxesid
and daqt.plaboid  (+) = p.platesboxesid
and (daqt.maxtime = daq.timestamp or daqt.maxtime is null)
/*
this view delivers the last concentration measurements for the plates boxes entries.
*/ WITH READ ONLY
;

-- 
-- VIEW: QUANTIFY_ACTION 
--

CREATE OR REPLACE VIEW QUANTIFY_ACTION
(EXPERIMENTATORID, PLABOID, CONCENTRATION, OD260) AS
select DNA_ACTION.EXPERIMENTATORID, DNA_ACTION.
    PLABOID,  DNAQUANTIFICATION.CONCENTRATION, DNAQUANTIFICATION.OD260
    from DNA_ACTION, DNAQUANTIFICATION
    WHERE ( DNAQUANTIFICATION.DNA_ACTIONID =  DNA_ACTION.DNAACTIONID ) WITH READ ONLY
;

CREATE OR REPLACE VIEW CLONE_LIGID_V (
    LIGCLONEID,TRANSFORMANT,CLONEID,LIGCLONENAME,
    IDENTICALTOEXPECTED,COMMENT_,SILENT_MUTATION,
    POLYMORPH_TEMPLATE,TRAFO_ID,SEQ_HOW,SEQ_RESULT,
    INSERT_FLAG,INSERTSIZE_BP,EXPERIMENTATOR_ID,DATE_IN,
    LIGATION_ID) AS 
    select cloneligation.*,
get_lig_clonlig(cloneid, 1) ligation_id
from 
cloneligation  WITH READ ONLY
;

CREATE OR REPLACE VIEW CLONE_LIGID_VEC_V (
    LIGCLONEID,TRANSFORMANT,CLONEID,LIGCLONENAME,
    IDENTICALTOEXPECTED,COMMENT_,SILENT_MUTATION,
    POLYMORPH_TEMPLATE,TRAFO_ID,SEQ_HOW,SEQ_RESULT,
    INSERT_FLAG,INSERTSIZE_BP,EXPERIMENTATOR_ID,DATE_IN,
    LIGATION_ID) AS 
    select cloneligation.*,
get_lig_clonlig(cloneid, 0) ligation_id
from 
cloneligation  WITH READ ONLY
;


CREATE OR REPLACE VIEW CLONE_INSERTCLONE AS 
    SELECT DISTINCT
       cl.cloneid, 
       cl.ligclonename,
       cl.trafo_id,
       l.ligationid AS orig_ligid,
       l.insertcloneid as orig_insertcloneid,
       lv.vectorid as vectorid,
       lv.foundokclone,
       lv.insertclonewrong
  FROM clone_ligid_v cl, clone_ligid_vec_v clv,
       ligation_and_pcrtemplates l, 
       ligation_and_pcrtemplates lv
 WHERE l.ligationid = cl.ligation_id
   AND lv.ligationid = clv.ligation_id
   AND cl.cloneid = clv.cloneid WITH READ ONLY
;

CREATE OR REPLACE VIEW CLONELIG_VEC_V 
AS
SELECT CLONEID, TRAFO_ID, get_vec_clonelig(cloneid) vectorid from cloneligation
;

CREATE OR REPLACE VIEW CLONE_VEC_STRAIN (CLONEID,
    VECTORID,HELPID,STRAINID) AS 
SELECT
DISTINCT
  CLONEID, VECTORID, HELPID, STRAINID 
FROM (
SELECT
  CL.CLONEID,
  cl.vectorid,
  T.HELPERPLASMID_ID AS HELPID,
  T.STRAINID
FROM clonelig_vec_v CL, LIGATION LIG, TRANSFORMATIONS T
WHERE
  CL.TRAFO_ID = T.TRAFO_ID 
UNION
SELECT 
  CR.CLONEID,
  LIB.VECTOR_ID AS VECTORID,
  LIB.HELPER_VEC_ID AS HELPID,
  LIB.STRAIN_ID AS STRAINID
FROM CLONESRZPD CR, LIBRARIES LIB
WHERE
  CR.LIBNO = LIB.LIBNO AND
  LIB.VECTOR_ID IS NOT NULL 
UNION
SELECT
  CI.CLONEID,
  IL.VECTOR_ID AS VECTORID,
  DUMMY AS HELPID,
  DUMMY AS STRAINID
FROM
  CLONEIMAGE CI,
  IMAGE_LIB IL
WHERE
  CI.RZPD_IMAGE_LIB = IL.ID_LIB
UNION
SELECT
  CO.CLONEID,
  CO.VECTORID,
  CO.HELPERPLASMIDID AS HELPID,
  CO.STRAINID
FROM CLONEOTHER CO
)
;
/
CREATE OR REPLACE  FUNCTION CHECK_DELIVERY  (
    plaboid_in in platesboxes.platesboxesid%TYPE) return number
is 
  vecid vectors.vectorid%TYPE;
  cloneid# cloneindex.cloneid%TYPE;
begin
  select cloneid 
  into cloneid#
  from platesboxes
  where platesboxesid = plaboid_in;
  if cloneid# is null then
    return 0;
  end if; 
  select vectorid 
  into vecid  
  from clone_vec_strain 
  where clone_vec_strain.cloneid = cloneid#;
  return 0;
exception
  when no_data_found then
    return -1;
end;