Tdi

From PTAGISWiki

Jump to: navigation, search

Contents

TDI -- Tag Distribution and Inventory

The TDI subsystem of PTAGIS is undergoing design and development to replace the legacy Microsoft Windows application with a more streamlined web interface. See TDI Upgrade for details.

Click here to update your 2008 PIT Tag Forecast.

TDI is the PTAGIS system that provides for tracking PIT tag purchases, distributions and inventory. Over the years, the simple task of purchasing a couple hundred thousand tags and distributing them to half a dozen projects has ballooned into a task of purchasing a couple million tags and distributing them to sixty or seventy projects.

See the TDI_Workflow (obsolete) for details on the TDI legacy system operations.

See the TDI_Installation instructions to install TDI client on your personal computer.

Tag Distribution and Inventory (TDI) Schema

The purpose of TDI is to:

  1. Track the procurement of PIT tags. This means to be able to associate the source of funds used to the purchase order used to procure tags (see associations of tdi_mod, tdi_po_mod and tdi_po).
  2. Track the distribution of PIT tags by distribution request form (DRF) to a given project during a given fiscal year or performance period (see associations between tdi_project, tdi_period and tdi_distribution, tdi_tag_request and tdi_tag_shipment).
  3. Track "Return Material Authorizations" for warantee of defective tag shipments (see tdi_rma and tdi_rma_tag_bag)
  4. Track "Alternate Tag Distributions". This sub-schema is intended to identify a majority of the tags distributed by the U.S. Army Corps of Engineers Anadromous Fish Evaluation Program, and any other PIT tag marking program not affiliated with the Northwest Power and Conservation Council's Fish and Wildlife Program which is funded by Bonneville Power Administration (see tdi_alt_distribution).

Here is an entity relationship diagram for the TDI schema.

Queries

List DRF's by Project Number

select p1.project_number, d.drf_number
 from tdi_project p1 
    , tdi_distribution d
    , tdi_period p2
where project_number in ('1998-019-01','2001-025-00')
and   p1.project_id = p2.project_id
and   p2.period_id  = d.period_id

List DRF Number, Tag Types and Requested amounts by Project Number

select p1.project_number, d.drf_number, r.tag_type, r.amount_requested
 from tdi_project p1 
    , tdi_distribution d
    , tdi_period p2
    , tdi_tag_request r
where project_number in ('1998-019-01','2001-025-00')
and   p1.project_id = p2.project_id
and   p2.period_id  = d.period_id 
and   d.distribution_id = r.dist_request_id

List Number of Tags Shipped and Requested by Project Number and DRF

select p1.project_number
    , d.drf_number
    , r.tag_type
    , r.amount_requested
    , sum(s.amount_shipped) as "Amount Shipped"
from tdi_project p1 
    , tdi_distribution d
    , tdi_period p2
    , tdi_tag_request r
    , tdi_tag_shipment s
where project_number in ('1998-019-01','2001-025-00')
and   p1.project_id = p2.project_id
and   p2.period_id  = d.period_id 
and   d.distribution_id = r.dist_request_id
and   r.tag_request_id  = s.tag_request_id
group by p1.project_number
        , d.drf_number
        , r.tag_type
        , r.amount_requested


List Project Number, DRF Number, Tag_Type, Bag_ID and Tag Code for a Project

select p1.project_number
     , d.drf_number
     , r.tag_type
     , b.bag_id
     , t.tag_code
 from tdi_project p1 
    , tdi_distribution d
    , tdi_period p2
    , tdi_tag_request r
    , tdi_tag_shipment s
    , tdi_tag_bag b
    , tdi_tag t
where project_number in ('1998-019-01','2001-025-00')
and   p1.project_id = p2.project_id
and   p2.period_id  = d.period_id 
and   d.distribution_id = r.dist_request_id
and   r.tag_request_id  = s.tag_request_id
and   s.bag_id = b.bag_id
and   b.bag_id = t.bag_id

List Project Number, Description, DRF, Tags Requested, Shipping Date and Number of Tags Shipped

select p1.project_number
   , p1.description
   , d.drf_number
   , r.tag_type
   , r.amount_requested
   , s.date_shipped
   , sum(s.amount_shipped) as "Amount Shipped"
from tdi_project p1 
   , tdi_distribution d
   , tdi_period p2
   , tdi_tag_request r
   , tdi_tag_shipment s
where project_number not like 'PO%' 
and  p1.project_id = p2.project_id
and   p2.period_id  = d.period_id 
and   d.distribution_id = r.dist_request_id
and   r.tag_request_id  = s.tag_request_id
group by p1.project_number
       , p1.description
       , d.drf_number
       , r.tag_type
       , r.amount_requested
       , s.date_shipped
order by date_shipped desc
Retrieved from "http://php.ptagis.org/wiki/index.php/Tdi"
Personal tools