Project page edits

[GoogleCodeExporter]

A consensus has emerged on the edited Project headings and descriptions 
(condensed from a long email thread):

Edits to the current headings and descriptions
Specimens used--> Contributions to Collections:
XX specimens or observations were contributed by this project to XX collections.

Specimens Contributed--> Contributions by other Projects:
XX other projects contributed XX specimens or observations that were used by 
this project.

Projects contributing specimens--> Usage by this Project:
XX specimens or observations from XX collections were used by this project.

Projects using contributed specimens--> Usage by other Projects:
XX other projects used XX specimens or observations contributed by this project.

Other issues in Projects to tweak:

--Media-- if greater than 5 returns, say, add a jump link or some other device 
so the page doesnt take forever to load..... another common complaint.

--Publications-- again only if greater than 5 linked pubs, add an expand link 
to the full list

-- to avoid the endless list of co-PI's or whoever on the participant list, a 
new category of agent like " Participant" or "Contributor" so we can still 
search on an agent who was a collector or otherwise doing something with this 
project but dont list them in the PI list only in an "Expand" or "More" link

Original issue reported on code.google.com by koomap...@gmail.com on 11 Apr 2012 at 8:44

[GoogleCodeExporter]

The mappings above don't make sense to me. Here's what's currently there, along 
with the actual SQL in case my explanations still don't make sense.

Publications - intersection of project_publication and publication

SELECT 
    publication.publication_id,
    full_citation,
    doi,
    pmid,
    count(citation.collection_object_id) numCit
FROM 
    project_publication,
    publication,
    citation
WHERE 
    project_publication.publication_id = publication.publication_id AND
    publication.publication_id=citation.publication_id (+) and
    project_publication.project_id = #project_id#
group by
    publication.publication_id,
    full_citation,
    doi,
    pmid
order by
    full_citation

Specimens Used - intersection of project_trans and loan

SELECT 
    collection.collection,
    collection.collection_id,
    count(distinct(cataloged_item.collection_object_id)) c
FROM 
    cataloged_item,
    collection,
    specimen_part,
    loan_item,
    project_trans
WHERE
    specimen_part.derived_from_cat_item = cataloged_item.collection_object_id AND
    cataloged_item.collection_id=collection.collection_id and
    specimen_part.collection_object_id = loan_item.collection_object_id AND
    loan_item.transaction_id = project_trans.transaction_id AND
    project_trans.project_id = #project_id#
group by
    collection.collection,
    collection.collection_id
UNION
SELECT 
    collection.collection,
    collection.collection_id,
    count(distinct(cataloged_item.collection_object_id)) c
FROM 
    cataloged_item,
    collection,
    loan_item,
    project_trans
WHERE
    cataloged_item.collection_id=collection.collection_id and
    cataloged_item.collection_object_id = loan_item.collection_object_id AND
    loan_item.transaction_id = project_trans.transaction_id AND
    project_trans.project_id = #project_id#
group by
    collection.collection,
    collection.collection_id

Specimens Contributed - intersection of project_trans and accn

SELECT 
    collection,
    collection.collection_id,
    count(*) c
FROM 
    project,
    project_trans,
    accn,
    cataloged_item,
    collection
WHERE 
    accn.transaction_id = cataloged_item.accn_id AND
    cataloged_item.collection_id=collection.collection_id and
    project_trans.transaction_id = accn.transaction_id AND 
    project.project_id = project_trans.project_id AND 
    project.project_id = #project_id#
group by
    collection,
    collection.collection_id            

Projects contributing specimens - find projects which have contributed 
specimens that are then used (loan) by the current project

SELECT 
    project.project_id,
    project_name
FROM 
    project
WHERE 
    project.project_id IN (
        SELECT 
            project_trans.project_id 
        FROM 
            project, 
            project_trans, 
            accn, 
            cataloged_item 
        where 
            project_trans.transaction_id = accn.transaction_id AND 
            accn.transaction_id = cataloged_item.accn_id AND 
            project_trans.project_id = project.project_id AND 
            cataloged_item.collection_object_id IN (
                SELECT 
                    cataloged_item.collection_object_id 
                FROM 
                    project,
                    project_trans,
                    loan_item,
                    specimen_part,
                    cataloged_item 
                WHERE 
                    loan_item.collection_object_id = specimen_part.collection_object_id AND
                    specimen_part.derived_from_cat_item = cataloged_item.collection_object_id AND
                    project_trans.transaction_id = loan_item.transaction_id AND 
                    project_trans.project_id = project.project_id AND 
                    project.project_id = #project_id#
            )
    )  
ORDER BY 
    project_name

Projects using contributed specimens - other projects that use specimens accn 
by this project     

SELECT 
    project.project_id,
    project_name 
FROM 
    project
WHERE 
    project.project_id IN (
        SELECT 
            project_trans.project_id 
        FROM 
            project, 
            project_trans, 
            loan_item,
            specimen_part,
            cataloged_item 
        where 
            project_trans.transaction_id = loan_item.transaction_id AND 
            loan_item.collection_object_id = specimen_part.collection_object_id AND
            specimen_part.derived_from_cat_item=cataloged_item.collection_object_id and
            project_trans.project_id = project.project_id AND 
            cataloged_item.collection_object_id IN (
                SELECT 
                    cataloged_item.collection_object_id 
                FROM 
                    project,
                    project_trans,
                    accn,
                    cataloged_item 
                WHERE 
                    accn.transaction_id = cataloged_item.accn_id AND 
                    project_trans.transaction_id = accn.transaction_id AND 
                    project_trans.project_id = project.project_id AND 
                    project.project_id = #project_id#
                )
            )
group by 
    project.project_id,
    project_name
order by project_name

Media - media related to the project via media_relations

 select distinct 
    media.media_id,
    media.media_uri,
    media.mime_type,
    media.media_type,
    media.preview_uri
 from
     media,
     media_relations,
     media_labels
 where
     media.media_id=media_relations.media_id and
     media.media_id=media_labels.media_id (+) and
     media_relations.media_relationship like '% project' and
     media_relations.related_primary_key = #project_id#

I'm not sure that hiding publications is a good idea. What is the use case for 
doing so?

The agents thing - why do you choose to add an agent to a project? I think you 
may be attempting to denormalize your data - seldom a good idea.  Either way, 
hiding data=bad - why add it if you don't want to see it?

Original comment by dust...@gmail.com on 2 May 2012 at 6:55

[GoogleCodeExporter]

need some advice

Original comment by dust...@gmail.com on 5 Jul 2012 at 3:43

• Changed state: AdvisoryCommittee