OData API
=========

* The APIs are all readonly and follow the `OData standard <http://www.odata.org/>`_. See that link for more details.
* All OData calls also include links to return metadata about the return if that is needed. 
  An example can be found by issuing an authenticated 
  ``GET`` to ``https://trialdb.tpsdb.com/api/$metadata#People``.
* We return a max of 1000 records per request. 
  If more than that match, then the result will include the link to the next page (via the ``$skip`` parameter).
* Authentication to the OData API requires a user with both the ``Developer`` and ``APIOnly`` roles 
  (``APIOnly`` might not exist and will need to be created).

.. important::

    The examples below show using curl, a command line tool for transferring data using various URL protocols.
    This tool comes on a Mac and can be downloaded for Windows.

    OData uses a `$` (dollar sign) in the URL for filter operations. 
    Curl does not properly encode the $ sign.
    So, in the following curl commands, we show the dollar sign as escaped with a `\\` in front of it.
    The acutal URL that will be used does not have that backslash.

.. note::

    The examples below explicitly set the authorization header, but you can also set it like so::

        curl --user "apionly:MyApiPasswordToRuleThemAll" \
            "https://trialdb.tpsdb.com/api/People?\$top=5"

    To encode credentials as shown in the examples below, you can use a website
    such as `Base64 Conversion Utility <https://www.base64decode.com>`_.

People
-----------------

Get the top 5 people records. ::

    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
        "https://trialdb.tpsdb.com/api/People?\$top=5"

Get the people records that have been modified since ``2015-09-01``::
    
    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
        "https://trialdb.tpsdb.com/api/People?\$filter=ModifiedDate+ge+2015-09-01"

Contributions
----------------------------

Get the top 5 contribution records. ::

    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
        "https://trialdb.tpsdb.com/api/Contributions?\$top=5"

Get all contributions since ``2014-12-30``. ::

    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
        "https://trialdb.tpsdb.com/api/Contributions?\$filter=ContributionDate+ge+2014-12-30"

Same as above, but with greater time granularity. ::

    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
        "https://trialdb.tpsdb.com/api/Contributions?\$filter=ContributionDate+ge+2014-12-30T23:59:59.99Z"

Pledges
-------

Get the top 5 pledge records. ::

    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
        "https://trialdb.tpsdb.com/api/Pledges?\$top=5"

Funds
-----

Get contribution fund details. ::

    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" 
        "https://trialdb.tpsdb.com/api/Funds"

Lookups
-------

Lookups are good to find more details about records (i.e. such as campuses and/or family positions). They have defaults in our system, but allow for additional configurable lookups customized by the church.

Get all marital status lookups. ::

    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
        "https://trialdb.tpsdb.com/api/lookup/MaritalStatuses"

Get all campus lookups. ::

    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
        "https://trialdb.tpsdb.com/api/lookup/Campuses"

Get all family position lookups. ::

    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
        "https://trialdb.tpsdb.com/api/lookup/FamilyPositions"

Get all contribution type lookups. ::

    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
        "https://trialdb.tpsdb.com/api/lookup/ContributionTypes"

Get all gender lookups. ::

    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
        "https://trialdb.tpsdb.com/api/lookup/Genders"

Organizations
-------------

Get the organization member under org "2181006" with a people ID of "1". ::

    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
        "https://trialdb.tpsdb.com/api/OrganizationMembers?\$filter=OrganizationId+eq+2181006+and+PeopleId+eq+1"

Get all organizations. ::

    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
        "https://trialdb.tpsdb.com/api/Organizations"

Custom
------

You can Get the results of any SQL query using the URL 
``https://trialdb.tpsdb.com/CustomAPI/file`` where file is the name of the SQLScript in Special Content. 

* You can also pass parameters to the script using the QueryString. 
  These parameters will be available inside your SQL as @ variables.
  For example if you passed the querystring ?dt=5-5-2000&div=121 at the end of the URL,
  you would have @dt and @div available to your SQL to filter by. 
* The same Authentication Header applies this case (Basic Authentication, Https protocol, APIOnly role)

.. important::
    The SQLScript you use must begin with the comment line ``--API`` as in the example below.
    
If the following SQLScript called ``mytest`` is in your `Special Content > SQLScripts`

.. code-block:: sql

    --API
    SELECT TOP 5
        PeopleId, 
        FirstName, 
        PreferredName, 
        LastName, 
        PrimaryCity,
        Age
    FROM dbo.People
    WHERE PositionInFamilyId = 10 -- Primary
    AND GenderId = 1 -- Male
    AND MemberStatusId = 10 -- Member
    AND PrimaryCity = @city
    AND age > @olderthan

And you have an APIOnly role user with a username of apionly and trialdb is your church's database, 
then you can run the following command. This command actually works since trialdb is a real database with fake people.
Note that the \ at the end of the first line may need to be removed if you run this on windows::
    
    curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
    "https://trialdb.tpsdb.com/CustomAPI/mytest?olderthan=65&city=Bartlett"

And your results will be something like the following JSON::

    [
      {
        "PeopleId": 2100183,
        "FirstName": "Charles",
        "PreferredName": "Charles",
        "LastName": "Brown",
        "PrimaryCity": "Bartlett",
        "Age": 76
      },
      {
        "PeopleId": 2101567,
        "FirstName": "James",
        "PreferredName": "James",
        "LastName": "Collins",
        "PrimaryCity": "Bartlett",
        "Age": 95
      },
      {
        "PeopleId": 2106865,
        "FirstName": "Blake",
        "PreferredName": "Blake",
        "LastName": "Ministry",
        "PrimaryCity": "Bartlett",
        "Age": 70
      },
      {
        "PeopleId": 2106957,
        "FirstName": "Anthony",
        "PreferredName": "Anthony",
        "LastName": "Damron",
        "PrimaryCity": "Bartlett",
        "Age": 68
      },
      {
        "PeopleId": 2107964,
        "FirstName": "Paul",
        "PreferredName": "Paul",
        "LastName": "Elmore",
        "PrimaryCity": "Bartlett",
        "Age": 74
      }
    ]