Request-Shipment application

Application can be accessed by using these URLs:

Batch data upload

In order to batch upload data XML or JSON files of the specific structure have to be prepared. Below are the template examples for various database objects.

Instructions

IMPORTANT: Request data has to be uploaded before proceeding with Shipments in case shipment ordered by request!

IMPORTANT: Shipment data have to be uploaded before proceeding with Shipment Items!

IMPORTANT: In case shipment ordered by request, Shipment relates to Request by Shimpent Item ROOT/DATA_SET/DATA/SHI_REQUEST_NUMBER field!

Creating and uploading Request data

Creating

  • for each request you will have to prepare a separate XML or JSON file (see template below)

  • ROOT/HEADER/RUN/RUN_NUMBER provide Request ID, it has to be unique thus you have to read it from database, i.e.
python /afs/cern.ch/user/v/valdo/public/rhapi.py -n --url=http://tracker-machine-a:8113 -f csv "select r.run_number from trker_int2r.trk_requests_next_id_v r" 

  • ROOT/HEADER/RUN/RUN_TYPE=Tracker Requests

  • ROOT/HEADER/RUN/RUN_BEGIN_TIMESTAMP run begin timestamp

  • ROOT/HEADER/TYPE/EXTENSION_TABLE_NAME=TRK_REQUESTS

  • ROOT/HEADER/TYPE/NAME=Tracker Requests

  • ROOT/DATA_SET/CREATED_BY_USER provide user name which prepares this file and uploads data i.e. your name

  • ROOT/DATA_SET/SUBVERSION=1 (for new requests)

  • ROOT/DATA_SET/DATA/REQ_KIND_OF_PART_ID which corresponds to component type ID and have to be looked up from database, i.e.
python /afs/cern.ch/user/v/valdo/public/rhapi.py --url=http://tracker-machine-a:8113 -f csv "select distinct r.id, r.name from trker_int2r.kinds_of_part r" 

  • ROOT/DATA_SET/DATA/REQ_LOCATION_ID provide location where components are to be delivered ID and have to be looked up from database, i.e.
python /afs/cern.ch/user/v/valdo/public/rhapi.py --url=http://tracker-machine-a:8113 -f csv "select a.location_id,a.location_name from trker_int2r.trk_locations_v a" 

  • ROOT/DATA_SET/DATA/REQ_NAME provide request title, any string, i.e. "Urgent request for construction"

  • ROOT/DATA_SET/DATA/REQ_QUANTITY is a number of Components requested, i.e. 12 (integer)

  • ROOT/DATA_SET/DATA/REQ_STATUS is a Request status, one of OPEN (not fulfilled), CLOSED (already fulfilled), CANCELED (canceled)

  • ROOT/DATA_SET/DATA/REQ_STATUS_COMMENT is any comment for Request

  • ROOT/DATA_SET/DATA/REQ_STATUS_DATE is the Date of the request, i.e. 2018-10-01

  • ROOT/DATA_SET/DATA/REQ_STATUS_PERSON is the responsible person name of the request, i.e. your name

Uploading through file system

  • zip request data files into the single bundle, i.e. my_requests.zip
zip my_requests.zip request1.xml request2.xml

  • copy zip file to db loader development database

scp my_requests.zip tracker-machine-a:/home/dbspool/spool/trker/int2r

  • copy zip file to db loader production database

scp my_requests.zip tracker-machine-a:/home/dbspool/spool/trker/cmsr

Uploading through service

Instant response: loaded - success, other message - no success

Handles file types: XML, JSON, ZIP

  • zip request data files into the single bundle, i.e. my_requests.zip
zip my_requests.zip request1.xml request2.xml

  • upload file to db loader production database
curl -F 'uploadFile=@my_requests.zip' http://tracker-machine-a/trker/cmsr/load/file

  • upload file to db loader development database
curl -F 'uploadFile=@my_requests.zip' http://tracker-machine-a/trker/int2r/load/file

Creating and uploading Shipment data

Creating

  • for each shipment you will have to prepare a separate XML or JSON file (see template below)

  • ROOT/HEADER/RUN/RUN_NUMBER provide Shipment ID, it has to be unique thus you have to read it from database, i.e.
python /afs/cern.ch/user/v/valdo/public/rhapi.py -n --url=http://tracker-machine-a:8113 -f csv "select r.run_number from trker_int2r.trk_shipments_next_id_v r" 

  • ROOT/HEADER/RUN/RUN_TYPE=Tracker Shipments

  • ROOT/HEADER/RUN/RUN_BEGIN_TIMESTAMP run begin timestamp

  • ROOT/HEADER/TYPE/EXTENSION_TABLE_NAME=TRK_SHIPMENTS

  • ROOT/HEADER/TYPE/NAME=Tracker Shipments

  • ROOT/DATA_SET/CREATED_BY_USER provide user name which prepares this file and uploads data i.e. your name

  • ROOT/DATA_SET/SUBVERSION=1 (for new shipment)

  • ROOT/DATA_SET/DATA/SHP_COMPANY_NAME shipment company name (e.g. Fedex)

  • ROOT/DATA_SET/DATA/SHP_FROM_LOCATION_ID location where shipment are to be ordered from ID and have to be looked up from database, i.e.
python /afs/cern.ch/user/v/valdo/public/rhapi.py --url=http://tracker-machine-a:8113 -f csv "select a.location_id,a.location_name from trker_int2r.trk_locations_v a" 

  • ROOT/DATA_SET/DATA/SHP_TO_LOCATION_ID location where shipment are to be delivered to ID and have to be looked up from database, i.e.
python /afs/cern.ch/user/v/valdo/public/rhapi.py --url=http://tracker-machine-a:8113 -f csv "select a.location_id,a.location_name from trker_int2r.trk_locations_v a" 

  • ROOT/DATA_SET/DATA/SHP_STATUS is a Shipment status, one of SHIPPED (not fulfilled), RECEIVED (already fulfilled), CANCELED (canceled)

  • ROOT/DATA_SET/DATA/SHP_STATUS_COMMENT any comment for shipment

  • ROOT/DATA_SET/DATA/SHP_STATUS_DATE is the Date of the shipment, i.e. 2018-10-01

  • ROOT/DATA_SET/DATA/SHP_STATUS_PERSON is the responsible person name of the shipment, i.e. your name

  • ROOT/DATA_SET/DATA/SHP_TRACKING_NUMBER is the shipment company tracking number

Uploading through file system

  • zip shipment data files into the single bundle, i.e. my_shipments.zip
zip my_shipments.zip shipment1.xml shipment2.xml

  • copy zip file to db loader development database

scp my_shipments.zip tracker-machine-a:/home/dbspool/spool/trker/int2r

  • copy zip file to db loader production database

scp my_shipments.zip tracker-machine-a:/home/dbspool/spool/trker/cmsr

Uploading through service

Instant response: loaded - success, other message - no success

Handles file types: XML, JSON, ZIP

  • zip request data files into the single bundle, i.e. my_requests.zip
zip my_requests.zip request1.xml request2.xml

  • upload file to db loader production database
curl -F 'uploadFile=@my_requests.zip' http://tracker-machine-a/trker/cmsr/load/file

  • upload file to db loader development database
curl -F 'uploadFile=@my_requests.zip' http://tracker-machine-a/trker/int2r/load/file

Creating and uploading Shipment Item data

Creating

  • for each shipment Item you will have to prepare a separate XML or JSON file (see template below)

  • ROOT/HEADER/RUN/RUN_TYPE=Tracker Shipment Items

  • ROOT/HEADER/RUN/RUN_BEGIN_TIMESTAMP run begin timestamp

  • ROOT/HEADER/TYPE/EXTENSION_TABLE_NAME=TRK_SHIPMENT_ITEMS

  • ROOT/HEADER/TYPE/NAME=Tracker Shipment Items

  • ROOT/DATA_SET/CREATED_BY_USER provide user name which prepares this file and uploads data i.e. your name

  • ROOT/DATA_SET/SUBVERSION=1 (for new shipment item)

  • ROOT/DATA_SET/DATA/SHI_COMMENT any comment for shipment item

  • ROOT/DATA_SET/DATA/SHI_PART_ID Part ID associated with shipment item. Firstly read list of KOP ID from database. Secondly read list of parts by KOP_ID.
python /afs/cern.ch/user/v/valdo/public/rhapi.py --url=http://tracker-machine-a:8113 -f csv "select distinct r.id as KOP_ID, r.name from trker_int2r.kinds_of_part r"

then select part ID's by KOP_ID:

python /afs/cern.ch/user/v/valdo/public/rhapi.py --url=http://tracker-machine-a:8113 -f csv "select distinct r.id, r.name_label from trker_int2r.parts r where r.kind_of_part_id = '<KOP_ID goes here>'"

  • ROOT/DATA_SET/DATA/SHI_REQUEST_NUMBER associate with concrete request ID. Request ID have to be read from database. Could be empty in case shipment proceed without request
python /afs/cern.ch/user/v/valdo/public/rhapi.py --url=http://tracker-machine-a:8113 -f csv "select r.run_number, r.req_name from trker_int2r.trk_requests_v r"

Uploading through file system

  • zip shipments items data files into the single bundle, i.e. my_shipments_items.zip
zip my_shipments_items.zip shipment_items1.xml shipment_items2.xml

  • copy zip file to db loader development database

scp my_shipments_items.zip tracker-machine-a:/home/dbspool/spool/trker/int2r

  • copy zip file to db loader production database

scp my_shipments_items.zip tracker-machine-a:/home/dbspool/spool/trker/cmsr

Uploading through service

Instant response: loaded - success, other message - no success

Handles file types: XML, JSON, ZIP

  • zip request data files into the single bundle, i.e. my_requests.zip
zip my_requests.zip request1.xml request2.xml

  • upload file to db loader production database
curl -F 'uploadFile=@my_requests.zip' http://tracker-machine-a/trker/cmsr/load/file

  • upload file to db loader development database
curl -F 'uploadFile=@my_requests.zip' http://tracker-machine-a/trker/int2r/load/file

Templates

Request

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <HEADER>
        <RUN>
            <RUN_NUMBER>10</RUN_NUMBER>
            <RUN_TYPE>Tracker Requests</RUN_TYPE>
            <RUN_BEGIN_TIMESTAMP>2015-11-03 19:00:00</RUN_BEGIN_TIMESTAMP>
        </RUN>
        <TYPE>
            <EXTENSION_TABLE_NAME>TRK_REQUESTS</EXTENSION_TABLE_NAME>
            <NAME>Tracker Requests</NAME>
        </TYPE>
    </HEADER>
    <DATA_SET>
        <CREATED_BY_USER>apoluden</CREATED_BY_USER>
        <SUBVERSION>1</SUBVERSION>
        <DATA>
            <REQ_KIND_OF_PART_ID>1000</REQ_KIND_OF_PART_ID>
            <REQ_LOCATION_ID>101010</REQ_LOCATION_ID>
            <REQ_NAME>apoluden0</REQ_NAME>
            <REQ_QUANTITY>100</REQ_QUANTITY>
            <REQ_STATUS>OPEN</REQ_STATUS>
            <REQ_STATUS_COMMENT>request comment</REQ_STATUS_COMMENT>
            <REQ_STATUS_DATE>2018-09-06</REQ_STATUS_DATE>
            <REQ_STATUS_PERSON>apoluden1</REQ_STATUS_PERSON>
        </DATA>
    </DATA_SET>
</ROOT>

{
  "Root": {
    "Header": {
      "Type": {
        "Name": "Tracker Requests",
        "ExtensionTableName": "TRK_REQUESTS"
      },
      "Run": {
        "RunNumber": "10",
        "RunType": "Tracker Requests", 
        "RunBeginTimestamp":"2015-11-03 19:00:00"
      }
    },
    "Datasets": [
      {
        "Dataset": {
          "CreatedByUser": "apoluden",
          "Subversion" : 1,
          "Data" : [
            {
              "req_name": "apoluden0",
              "req_location_id": "101010",
              "req_kind_of_part_id": "1000",
              "req_quantity": "100",
              "req_status" : "OPEN",
              "req_status_comment" : "request comment", 
              "req_status_date" : "2018-09-06",
              "req_status_person": "apoluden1"
            }
          ]
        }
      }
    ]
  }
}

Shipment

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <HEADER>
        <RUN>
            <RUN_NUMBER>200</RUN_NUMBER>
            <RUN_TYPE>Tracker Shipments</RUN_TYPE>
            <RUN_BEGIN_TIMESTAMP>2015-11-03 19:00:00</RUN_BEGIN_TIMESTAMP>
        </RUN>
        <TYPE>
            <EXTENSION_TABLE_NAME>TRK_SHIPMENTS</EXTENSION_TABLE_NAME>
            <NAME>Tracker Shipments</NAME>
        </TYPE>
    </HEADER>
    <DATA_SET>
        <CREATED_BY_USER>apoluden</CREATED_BY_USER>
        <SUBVERSION>3</SUBVERSION>
        <DATA>
            <SHP_COMPANY_NAME>company</SHP_COMPANY_NAME>
            <SHP_FROM_LOCATION_ID>100</SHP_FROM_LOCATION_ID>
            <SHP_STATUS>SHIPPED</SHP_STATUS>
            <SHP_STATUS_COMMENT>shippment component comment</SHP_STATUS_COMMENT>
            <SHP_STATUS_DATE>2018-09-06</SHP_STATUS_DATE>
            <SHP_STATUS_PERSON>apoluden</SHP_STATUS_PERSON>
            <SHP_TO_LOCATION_ID>200</SHP_TO_LOCATION_ID>
            <SHP_TRACKING_NUMBER>SEAPS5000495784V2</SHP_TRACKING_NUMBER>
        </DATA>
    </DATA_SET>
</ROOT>

{
  "Root": {
    "Header": {
      "Type": {
        "Name": "Tracker Shipments",
        "ExtensionTableName": "TRK_SHIPMENTS"
      },
      "Run": {
        "RunNumber": "200",
        "RunType": "Tracker Shipments",
        "RunBeginTimestamp":"2015-11-03 19:00:00"
      }
    },
    "Datasets": [
      {
        "Dataset": {
          "CreatedByUser": "apoluden",
          "Subversion" : 3,
          "Data" : [
            {
              "shp_company_name": "company",
              "shp_tracking_number": "SEAPS5000495784V2",
              "shp_from_location_id": "100",
              "shp_to_location_id": "200",
              "shp_status" : "SHIPPED",
              "shp_status_comment" : "shippment component comment", 
              "shp_status_date" : "2018-09-06", 
              "shp_status_person": "apoluden" 
            }
          ]
        }
      }
    ]
  }
}

Shipment Item

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <HEADER>
        <RUN>
            <RUN_NUMBER>200</RUN_NUMBER>
            <RUN_TYPE>Tracker Shipment Items</RUN_TYPE>
            <RUN_BEGIN_TIMESTAMP>2015-11-03 19:00:00</RUN_BEGIN_TIMESTAMP>
        </RUN>
        <TYPE>
            <EXTENSION_TABLE_NAME>TRK_SHIPMENT_ITEMS</EXTENSION_TABLE_NAME>
            <NAME>Tracker Shipment Items</NAME>
        </TYPE>
    </HEADER>
    <DATA_SET>
        <CREATED_BY_USER>apoluden</CREATED_BY_USER>
        <SUBVERSION>1</SUBVERSION>
        <DATA>
            <SHI_COMMENT>shippment request comment</SHI_COMMENT>
            <SHI_PART_ID>100</SHI_PART_ID>
            <SHI_REQUEST_NUMBER>10</SHI_REQUEST_NUMBER>
        </DATA>
    </DATA_SET>
</ROOT>

{
  "Root": {
    "Header": {
      "Type": {
        "Name": "Tracker Shipment Items",
        "ExtensionTableName": "TRK_SHIPMENT_ITEMS"
      },
      "Run": {
        "RunNumber": "200",
        "RunType": "Tracker Shipment Items",
        "RunBeginTimestamp":"2015-11-03 19:00:00"
      }
    },
    "Datasets": [
      {
        "Dataset": {
          "CreatedByUser": "apoluden",
          "Subversion" : 1,
          "Data" : [
            {
              "shi_request_number": "10",
              "shi_comment" : "shippment request comment",
              "shi_part_id" : 100
            }
          ]
        }
      }
    ]
  }
}

-- AlessandroDiMattia - 2018-09-25

Edit | Attach | Watch | Print version | History: r18 < r17 < r16 < r15 < r14 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r18 - 2019-12-13 - AivarasSilale
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    Sandbox All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback