My Learnings in SAP-CAP Using MongoDB

Recently I got an opportunity to explore SAP CAP on Mono DB.

I would like to share my learnings here.

1. Install SAP-CDS globally by executing below code

npm I -g @sap/cds-dk

2. Create a folder for our project, Go to that folder in command prompt and execute below code

cds init

Above code mainly creates sub folders app, db, srv and package.json file

3. Open our project folder in Visual Studio Code 

3.1 Create Schema file in the db Folder

namespace sap.mongo.db;

entity Orders{
  key orderNo    : String;
  key ItemNo     : String;
  Material       : String;
  Quantity       : Integer;
  QUOM           : String;
  Price          : Double;
  Currency       : String;
  OrderCreatedOn : String
};

entity Materials{
  key Material : String;
  ImageData: String;  
  ImageContentType: String @Core.IsMediaType;
  Description : String 
};

Model for Orders and Material is defined in the Schema

3.2 Update Node Modules

Execute below lines of code in Visual Studio Code terminal

npm install mongodb express @sap/cds @sap/cds-odata-v2-adapter-proxy

Above code will install mongodb, express, sap/cds, sap/cds-Odatav2 adapter npm modules

npm install

Above code will install all other dependent nom nodules and will update package.json file

3.3 Create Service Definition and Implementation files in srv Folder

Create “myapp.cds”  Service definition file

using { sap.mongo.db as my } from '../db/schema';

service MyOrders @(path: '/odata/mongodb/MyOrders')
{
   entity Orders as projection on my.Orders;  
}

service MyMaterials @(path: '/odata/mongodb/MyMaterials')
{
   entity Materials as projection on my.Materials; 
}

Service MyOrders and MyMaterials are defined with entities Orders and Materials respectively

Create “myapp.js” Service Implementation file

module.exports = cds.service.impl(function() {
    const { Orders, Materials } = this.entities;

    //For Orders
    this.on("READ", Orders, _getOrders);
    this.on("CREATE", Orders, _CreateOrders);

    //For Materials
    this.on("READ", Materials, _getMaterialInfo);
});

Function _getOrders is executed when a read operation is performed on entity Orders. This function call overrides automatic call to databased configured

Similarly _getMaterislInfo is called when read operation is performed on entity Materials

Sample code for Funcion _getOrders

async function _getOrders(req) {
    // Connect the client to the server
    await client.connect();

    // Establish and verify connection
    var db = await client.db(db_name);

    //Connect to Collection
    var collection_Orders = await db.collection("Orders");

    var filter, projection, results, date_high, date_low;

    console.log(req.query);

    //Make sure date_low and date_high are in format 2020-02-04T05:06:18.417Z
    //but not as 2020-2-4T5:6:18.417Z
    if (req.query.SELECT.where !== undefined) {
        date_low = req.query.SELECT.where[2].val;
        date_high = req.query.SELECT.where[6].val;
    }

    if (date_low !== undefined) {
        var MyCurLowDate = new Date(date_low);
        var MyCurHighDate = new Date(date_high);
        filter = { OrderCreatedOn: { $gte: MyCurLowDate, $lte: MyCurHighDate } };
    } else {
        filter = {};
    }

//Do not Select ID
//Select OrderCreatedOn in format yyyy-mm-ddThh:mm:ss:lll+z
//Mention all other fields which need to be selected
    projection = {
        _id: 0,
        OrderCreatedOn: {
            $dateToString: {
                format: "%Y-%m-%dT%H:%M:%S.%LZ",
                date: "$OrderCreatedOn",
            },
        },
        orderNo: 1,
        ItemNo: 1,
        Material: 1,
        Quantity: 1,
        QUOM: 1,
        Price: 1,
        Currency: 1,
    };

    var results = await collection_Orders
        .find(filter, { projection: projection })
        .toArray();

//return results as output
    return results;
}

Above function is self explantory to its purpose.

Below mentioned constants are declared prior using above function.

const cds = require("@sap/cds");
const MongoClient = require("mongodb").MongoClient;
const ObjectId = require("mongodb").ObjectID;
const uri = "mongodb://localhost:27017";
const db_name = "ERPData";
const client = new MongoClient(uri);
var response;

3.4 Create Server.js file

const express = require("express");
const cds = require("@sap/cds");
const odatav2proxy = require("@sap/cds-odata-v2-adapter-proxy");

const { PORT = 5007 } = process.env
const app = express()

//With below line all cds services can consumed as express serveices
cds.serve("all").in(app)

//convert Odata4 to Odata2 with below line
app.use(odatav2proxy({ path: "v2", port: PORT }))

//Generate a local server "http://localhost" running on port 5007
app.listen(PORT, () => console.info(`server listening on http://localhost:${PORT}`))

3.5 Few changes required to project .json file

Mention Node engine

"engines": {  "node": "^8.9"    },

Replace npx cds with node server.js

"scripts": {
        "start": "node server.js"
    }

Refer to below pacakge.json file for better understanding

4. Save files and execute below code in the terminal to start the server

Node server.js

5. We can either user Postman or directly consume in browser 

Odata V4 Service

http://localhost:5007/odata/mongodb/MyOrders/Orders

Odata V2 Service

http://localhost:5007/v2/odata/mongodb/MyOrders/Orders

Example for Filters

Odata V4 Service

http://localhost:5007/odata/mongodb/MyOrders/Orders?$filter=(OrderCreatedOn ge '2020-12-08T17:41:01.103Z' and OrderCreatedOn le '2020-12-09T17:41:01.103Z')

6. To Create SAPUI5 App for this

6.1 Expose Local Service to SAP Cloud using SAP Cloud Connector

6.2 Configure Destination in SAP Cloud Foundry

6.3 Develop SAPUI5 Application in Web IDE

Mention Destination path in neo-app.json file

    {
      "path": "/localmongodb",
      "target": {
        "type": "destination",
        "name": "localmongodb"
      },
      "description": "connect to local mongodb"
    }

Use URl as below to perform ajax/jquery calls

var url = "/localmongodb/v2/odata/mongodb/MyOrders/Orders?

Few Pointers from my project

1. My project extracts images from mongodb and exposes to SAPUI5

2. I have used Standard List in Master page to display master records

Here, I have used Data Range along with Search criteria.  When I user both, scroll bar comes to       entire page instead of only Standard Items. To get scroll bar to only Standard Items write below source in the Controller onIint

var oList = this.byId("idList");
oList.setSticky(["HeaderToolbar"]);

Considering that Search and Date Range are mentioned in the Header ToolBar.

Preview of my app:

Source Code is available at below Github Link

https://github.com/krish469/sap-cap-mongodb/tree/SAPUI5