PHP & SQL Integration in SAP UI5

Introduction

We can use SAP UI5 and integrate it with any backend technologies available in the market. In this article, we will learn simple steps related to PHP & SQL Integration in SAP UI5.

How to Integrate PHP & SQL with SAP UI5

To Integrate PHP & SQL with SAP UI5, follow this simple flow:

Steps to follow for PHP

  • Install XAMPP
  • Run PHP My Admin (to see database): http://localhost:80/phpmyadmin/
  • Create a db with a few tables
  • Create a notepad file, save it as process.php
  • Add these configurations:
    • $servername = “localhost”;
    • $username = “root”;
    • $dbname = “<your db name>”;
  • Add these line to start connection:
    • $conn = new mysqli($servername, $username,””, $dbname);
    • if ($conn->connect_error) {
    • die(“Connection failed: ” . $conn->connect_error);
  • At the top of php file, add:
    • header(“Access-Control-Allow-Origin:*”);
    • echo $_POST[“method”]();

How to Connect a PHP File with SQL

Steps to Integrate PHP & SQL in SAP UI5

Step 01: Download, Install, and Run XAMPP

XAMPP for SAP UI5

Step 02: Once the server is up and running, open up php my admin using this URL: http://localhost:80/phpmyadmin/

Step 03: Create a database and few tables within it

Step 04: Create a PHP file, do the configuration of SQL connection as mentioned above and then save it within htdocs folder, preferably plan to save it within the UI5 folder

Step 05: Within the PHP file, write CRUD operation code within different functions. Learn PHP coding here.

Step 06: Configure PHP with UI5, as discuss in upcoming section

Step 07: Write code in UI5 to perform CRUD Operation

Step 08: Download the zip of UI5 folder and save with the htdocs folder. Now, you can run your code without the internet, just using XAMPP. And you data would be saved with the SQL table.

How to Connect UI5 with PHP file

To connect UI5 with PHP, you just need to provide the URL pointing to the PHP file. In our use case, we have stored the PHP file in the default location provided by the XAMPP server i.e., htdocs. Within that folder, we have our UI5 App i.e. Hisab; within that, we have a folder for PHP by the name php where we have saved our php file by the name process.php.
Hence the URL pointing the same will be: http://localhost:80/Hisab/php/process.php

And the code in UI5 will be:

this.http = "http://";
this.uri = this.http + "localhost:80/Hisab/php/process.php";

 

How to perform CRUD operation from UI5 using PHP in SQL Database

We will use AJAX call to call our code in the PHP file.

PHP file: process.php

<?php 
header("Access-Control-Allow-Origin:*");
// header('Content-Type: application/json');
echo $_POST["method"]();

function getClientTransaction(){
$servername = "localhost";
$username = "root";
$dbname = "hisabkitab";
// Create connection
$conn = new mysqli($servername, $username,"", $dbname);
// Check connection
    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }
// Get Filters
    $obj = json_decode($_POST["data"]);
    $Client = $obj->Client;
    $month = $obj->month;
    $year = $obj->year;
    $machineType = $obj->machineType;
    $officeType = $obj->officeType;
    $machineClause = "machineType = '".$machineType."'";
    $officeClause = "cc = '".$officeType."'";
    $clientClause = "client = '".$Client."'";
    if($machineType == "All")
    {
        $machineClause = "machineType != ''";
    }
    if($officeType == "All")
    {
        $officeClause = "cc != ''";
    }
    if($Client == "All")
    {
        $clientClause = "client != ''";
    }

    if ($result = $conn -> query("SELECT * FROM transaction WHERE ".$machineClause." AND ".$officeClause." AND ".$clientClause." AND month = '{$month}' AND year = '{$year}' ORDER by date DESC")) {
        if($result->num_rows > 0) {
            $i = 0;
            while($row = $result->fetch_assoc()){
                $dataArray[$i] = $row;
                $i = $i + 1;
            }
            echo json_encode($dataArray);
        }
    }
}

function onCreateATransaction(){
$servername = "localhost";
$username = "root";
$dbname = "hisabkitab";
// Create connection
$conn = new mysqli($servername, $username,"", $dbname);
// Check connection
    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }
    $timestamp = date('Y-m-d H:i:s');
    $obj = json_decode($_POST["data"]);
    $client = $obj->client;
    $labour = $obj->labour;
    $date = $obj->date;
    $time=strtotime($date);
    $month = $obj->month;
    $year=$obj->year;
    $cc = $obj->cc;
    $rate = $obj->rate;
    $quantity = $obj->quantity;
    $machineType = $obj->machineType;
    $total = $obj->total;
    $sql = "INSERT INTO transaction(date,client,labour,cc,rate,quantity,machineType,total, month, year) VALUES ('$date','$client','$labour','$cc','$rate','$quantity','$machineType','$total','$month','$year')";
    if(mysqli_query($conn,$sql)){
    $dataArray[0] = 'Insertion successful';
    echo json_encode($dataArray);
    }
    else{
    $dataArray[0] = 'Insertion failed';
    echo json_encode($dataArray);
    }
}
?>

View Code

1. Create Transaction

<mvc:View xmlns:core="sap.ui.core" xmlns:mvc="sap.ui.core.mvc" xmlns="sap.m" controllerName="Hisab.Hisab.controller.TransactionShaving"
    xmlns:html="http://www.w3.org/1999/xhtml">
    <Page title="Create a new Shaving Transaction" showNavButton="true" navButtonPress="onpressBack" titleAlignment="Center">
        <content>
            <HBox class="sapUiSmallMargin">
                <VBox class="sapUiSmallMarginEnd">
                    <Label text="Select Office" design="Bold" required="true"/>
                    <ComboBox id="idOffice" >
                        <core:Item key="Main Office" text="Main Office"/>
                        <core:Item key="Nauri Ram" text="Nauri Ram"/>
                        <core:Item key="Maidan" text="Maidan"/>
                    </ComboBox>
                </VBox>
                <VBox class="sapUiSmallMarginEnd">
                    <Label text="Enter the Labour Name" design="Bold" required="true"/>
                    <ComboBox id="idLabor" items="{ComboModel>/Labour_results}" change="onChangeCountry">
                        <items>
                            <core:Item key="{ComboModel>Labor}" text="{ComboModel>Labor}"/>
                        </items>
                    </ComboBox>
                </VBox>
            </HBox>
            <VBox id="idcTxBox" class="sapUiMediumMarginEnd">
                <!--Table to get User Inputs-->
                <Table id="idTable" headerText="Add Records" sticky="ColumnHeaders" growing="true" growingThreshold="10" class="sapUiSizeCompact"
                    >
                    <headerToolbar>
                        <OverflowToolbar design="Transparent">
                            <Title text="Add Records"/>
                            <ToolbarSpacer/>
                            <!-- <Button text="Add New" icon="sap-icon://add" tooltip="Add New" press="onAddRow"/> -->
                            <!-- <Button text="Submit" icon="sap-icon://refresh" tooltip="Refresh" press="onSaveTransaction"/> -->
                        </OverflowToolbar>
                    </headerToolbar>
                    <columns>
                        <Column  hAlign="Left">
                            <Label text="Date" required="true"/>
                        </Column>
                        <Column hAlign="Left">
                            <Label text="Client" required="true"/>
                        </Column>
                        <Column hAlign="Left">
                            <Label text="Quantity" required="true"/>
                        </Column>
                        <Column hAlign="Left">
                            <Label text="Rate" required="true"/>
                        </Column>
                        <Column  hAlign="Left">
                            <!-- <Label text="Rate" required="true"/> -->
                        </Column>
                    </columns>
                    <items>
                        <ColumnListItem>
                            <cells>
                                <DatePicker id="datePicker" displayFormat="dd-MM-yyyy" valueFormat="dd/MM/yyyy" value="{/Date}" />
                                <Input  value="{/Client}" placeholder="Enter Client Name" showSuggestion="true" suggestionItems="{ComboModel>/Clients_results}">
                                    <suggestionItems>
                                        <core:Item text="{ComboModel>client}"/>
                                    </suggestionItems>
                                </Input>
                                <Input  value="{/Quantity}" type="Number" submit="onSumbit"/>
                                <Input  value="{/Rate}" type="Number" submit="onSumbit"/>
                                <!-- <Input value="{Rate}" type="Number" submit="onSumbit"/> -->
                                <Button type="Accept" text="{i18n>Submit}" press="onSaveTransaction"/>
                            </cells>
                        </ColumnListItem>
                    </items>
                </Table>
            </VBox>
        </content>
    </Page>
</mvc:View>

 

2. View Transaction

<mvc:View xmlns:core="sap.ui.core" xmlns:mvc="sap.ui.core.mvc" xmlns="sap.m" controllerName="Hisab.Hisab.controller.ViewTransaction"
    xmlns:html="http://www.w3.org/1999/xhtml">
    <Page title="View Transactions" showNavButton="true" navButtonPress="onpressBack" titleAlignment="Center">
        <content>
            <HBox class="sapUiSmallMargin">
                <VBox class="sapUiSmallMarginEnd">
                    <Label text="Select Office" design="Bold" required="true"/>
                    <ComboBox id="idOffice" selectionChange="onChangeSelection" selectedKey="All">
                        <core:Item key="All" text="All"/>
                        <core:Item key="Main Office" text="Main Office"/>
                        <core:Item key="Nauri Ram" text="Nauri Ram"/>
                        <core:Item key="Maidan" text="Maidan"/>
                    </ComboBox>
                </VBox>
                <VBox class="sapUiSmallMarginEnd">
                    <Label text="Select Client" design="Bold" required="true"/>
                    <ComboBox id="idClient" items="{ path: 'ComboModel>/Clients_results', sorter: { path: 'cc', descending: false, group: true } }">
                        <core:Item key="{ComboModel>client}" text="{ComboModel>client}"/>
                    </ComboBox>
                </VBox>
                <VBox class="sapUiSmallMarginEnd">
                    <Label text="Operation" design="Bold" required="true"/>
                    <ComboBox id="idOperation" selectionChange="onChangeSelection" selectedKey="All">
                        <core:Item key="All" text="All"/>
                        <core:Item key="Shaving" text="Shaving"/>
                        <core:Item key="Buffing" text="Buffing"/>
                        <core:Item key="Softening" text="Softening"/>
                        <core:Item key="Milling" text="Milling"/>
                        <core:Item key="Tangan" text="Tangan"/>
                        <core:Item key="Thokai" text="Thokai"/>
                    </ComboBox>
                </VBox>
                <VBox class="sapUiSmallMarginEnd">
                    <Label text="Select Month" design="Bold" required="true"/>
                    <ComboBox id="idMonth" selectionChange="onChangeSelection" selectedKey="01">
                        <core:Item key="01" text="January"/>
                        <core:Item key="02" text="February"/>
                        <core:Item key="03" text="March"/>
                        <core:Item key="04" text="April"/>
                        <core:Item key="05" text="May"/>
                        <core:Item key="06" text="June"/>
                        <core:Item key="07" text="July"/>
                        <core:Item key="08" text="August"/>
                        <core:Item key="09" text="September"/>
                        <core:Item key="10" text="October"/>
                        <core:Item key="11" text="November"/>
                        <core:Item key="12" text="December"/>
                    </ComboBox>
                </VBox>
                <VBox class="sapUiSmallMarginEnd">
                    <Label text="Select Year" design="Bold" required="true"/>
                    <ComboBox id="idYear" selectionChange="onChangeSelection" selectedKey="2020">
                        <core:Item key="2020" text="2020"/>
                        <core:Item key="2021" text="2021"/>
                        <core:Item key="2022" text="2022"/>
                        <core:Item key="2022" text="2023"/>
                    </ComboBox>
                </VBox>
                <VBox>
                    <Label/>
                    <Button text="Get Transactions" press="onGetTransaction"/>
                </VBox>
            </HBox>
            <VBox id="idClientTx" visible="false">
                <VBox class="sapUiMediumMarginEnd">
                    <!-- Table to get User Inputs -->
                    <Table id="idTable" headerText="Client Transactions" sticky="ColumnHeaders" growing="true" mode="SingleSelectLeft" growingThreshold="20"
                        class="sapUiSizeCompact" items="{detailsModel>/results}">
                        <headerToolbar>
                            <OverflowToolbar>
                                <Title id="idTableHeader" text="All Transactions" level="H2"/>
                                <ToolbarSpacer/>
                                <Button text="Delete" icon="sap-icon://delete" press="onDelete"/>
                            </OverflowToolbar>
                        </headerToolbar>
                        <columns>
                            <Column hAlign="Left">
                                <Label text="Date"/>
                            </Column>
                            <Column hAlign="Left">
                                <Label text="Office"/>
                            </Column>
                            <Column hAlign="Left">
                                <Label text="Labour"/>
                            </Column>
                            <Column hAlign="Left">
                                <Label text="Operation"/>
                            </Column>
                            <Column hAlign="Left">
                                <Label text="Quantity/Hours"/>
                            </Column>
                            <Column hAlign="Left">
                                <Label text="Rate"/>
                            </Column>
                            <Column hAlign="Left">
                                <Label text="Amount"/>
                            </Column>
                        </columns>
                        <items>
                            <ColumnListItem type="Navigation" selected="onClickTransaction">
                                <cells>
                                    <Text text="{detailsModel>date}"/>
                                    <Text text="{detailsModel>cc}"/>
                                    <Text text="{detailsModel>labour}"/>
                                    <Text text="{detailsModel>machineType}"/>
                                    <Text text="{detailsModel>quantity}"/>
                                    <Text text="{detailsModel>rate}"/>
                                    <Text text="{detailsModel>total}"/>
                                </cells>
                            </ColumnListItem>
                        </items>
                    </Table>
                </VBox>
            </VBox>
        </content>
    </Page>
</mvc:View>

 

Controller Code

1. Create Transaction

sap.ui.define(
  [
    "sap/ui/core/mvc/Controller",
    "sap/ui/model/json/JSONModel",
    "sap/m/MessageBox",
  ],
  function (Controller, JSONModel, MessageBox) {
    "use strict";

    return Controller.extend("Hisab.Hisab.controller.TransactionShaving", {
      /**
       * Called when a controller is instantiated and its View controls (if available) are already created.
       * Can be used to modify the View before it is displayed, to bind event handlers and do other one-time initialization.
       * @memberOf Hisab.Hisab.view.TransactionShaving
       */
      onInit: function () {
        this.oRouter = sap.ui.core.UIComponent.getRouterFor(this);
        this.http = "http://";
        this.uri = this.http + "localhost:81/Hisab/php/process.php";
        this.oRouter
          .getRoute("TransactionShaving")
          .attachPatternMatched(this._handleRouteMatched, this);
      },

      _handleRouteMatched: function (oEvent) {
        this.updateValues();
        // get First Row
        this.getFirstRow();
      },

      onpressBack: function (oEvent) {
        this.oRouter.navTo("Main");
      },
      updateValues: function () {
        var that = this;
        // Get Labour's Data
        $.ajax({
          url: this.uri,
          type: "POST",
          data: {
            method: "getAllLabours",
            data: JSON.stringify({}),
          },
          dataType: "json",
          success: function (dataLabour) {
            // Get Labour's Data
            $.ajax({
              url: that.uri,
              type: "POST",
              data: {
                method: "getAllClients",
                data: JSON.stringify({}),
              },
              dataType: "json",
              success: function (dataClient) {
                that.ClientList = [];
                var ComboObj = {
                  Labour_results: dataLabour,
                  Clients_results: dataClient,
                };
                dataClient.forEach(function (Client) {
                  that.ClientList.push(Client.Client);
                });
                that.getView().setModel(new JSONModel(ComboObj), "ComboModel");
              },
              error: function (request, error) {},
            });
          },
          error: function (request, error) {},
        });
      },
      getFirstRow: function (oEvent) {
        // Empty Model
        var data = {
          Date: "",
          Client: "",
          Quantity: "",
          Rate: "",
        };
        // Add Data
        var oModel = new sap.ui.model.json.JSONModel(data);
        // Set Model
        this.byId("idTable").setModel(oModel);
      },

      onAddRow: function (oEvent) {
        var flag = true;
        var tableData = this.byId("idTable").getModel().getData();
        tableData.results.forEach(function (temp) {
          if (!temp.Date && !temp.Client && !temp.Quantity && !temp.Rate) {
            flag = false;
          }
        });
        if (flag === true) {
          var cuurentModel = this.byId("idTable").getModel().getData().results;
          cuurentModel.push({
            Date: "",
            Client: "",
            Quantity: "",
            Rate: "",
          });
          this.byId("idTable").getModel().refresh();
        } else {
          MessageBox.error("All fields are mandatory!");
        }
      },
      onRefresh: function () {
        this.byId("idTable").getModel().setData({
          Date: "",
          Client: "",
          Quantity: "",
          Rate: "",
        });
        this.byId("idTable").getModel().refresh();
      },
      onSaveTransaction: function () {
        var that = this;
        var sLaborName = this.byId("idLabor").getValue();
        var sOffice = this.byId("idOffice").getSelectedKey();
        if (sOffice && sLaborName) {
          var temp = this.byId("idTable").getModel().getData();
          if (!temp.Date || !temp.Client || !temp.Quantity || !temp.Rate) {
            alert("Fill all details");
          } else {
            var dataLabor = {
              id: sLaborName.toLowerCase().replace(/ /g, ""),
              Labor: sLaborName,
            };
            $.ajax({
              url: that.uri,
              type: "POST",
              data: {
                method: "onCreateLabor",
                data: JSON.stringify(dataLabor),
              },
              dataType: "json",
              success: function (dataClient) {
                console.log("success");
              },
              error: function (request, error) {
                console.log(error);
              },
            });
            var data = JSON.stringify({
              client: temp.Client,
              labour: sLaborName,
              date: temp.Date,
              month: parseInt(temp.Date.substring(3, 5), 0),
              cc: sOffice,
              rate: temp.Rate,
              quantity: temp.Quantity,
              machineType: "Shaving",
              total: temp.Rate * temp.Quantity,
              year: parseInt(temp.Date.substring(6), 0),
            });
            $.ajax({
              url: that.uri,
              type: "POST",
              data: {
                method: "onCreateATransaction",
                data: data,
              },
              crossDomain: true,
              dataType: "json",
              success: function (sData) {
                var messages = sData[0] + "\n";
                // that.onRefresh();
                that.getView().byId("datePicker").focus();
                that.updateValues();
                // that._handleRouteMatched();
                // MessageBox.success(messages);
              },
              error: function (request, error) {
                MessageBox.error(request.responseText);
                // if (i === results.length && messages.length > 0) {
                //   MessageBox.success(messages);
                //   that._handleRouteMatched();
                // }
              },
            });
            that.saveClient(temp.Client);
          }
        } else {
          alert("Labor ka naam nhi hai");
        }
      },
     

      onResetTransaction: function (oEvent) {
        this.getFirstRow();
        this._handleRouteMatched();
      },

      saveClient: function (client) {
        var that = this;
        var dataClient = {
          id: client.toLowerCase().replace(/ /g, ""),
          Client: client,
        };
        // Save Labour's Data
        $.ajax({
          url: that.uri,
          type: "POST",
          data: {
            method: "onCreateClient",
            data: JSON.stringify(dataClient),
          },
          dataType: "json",
          success: function (success) {},
          error: function (request, error) {},
        });
      },
    });
  }
);

 

2. View Transaction

sap.ui.define([
        "sap/ui/core/mvc/Controller",
        "sap/ui/model/json/JSONModel"
    ],
    function (Controller, JSONModel) {
        "use strict";

        return Controller.extend("Hisab.Hisab.controller.ViewTransaction", {
            /**
             * Called when a controller is instantiated and its View controls (if available) are already created.
             * Can be used to modify the View before it is displayed, to bind event handlers and do other one-time initialization.
             * @memberOf Hisab.Hisab.view.ClientPayment
             */
            onInit: function () {
                this.oRouter = sap.ui.core.UIComponent.getRouterFor(this);
                this.http = "http://";
                this.uri = this.http + "localhost:81/Hisab/php/process.php";
                this.oRouter.getRoute("ViewTransaction").attachPatternMatched(this._handleRouteMatched, this);
                this.getView().byId("idMonth").setSelectedKey(new Date().getMonth().toString());
            },

            _handleRouteMatched: function (oEvent) {
                var that = this;
                $.ajax({
                    url: that.uri,
                    type: "POST",
                    data: {
                        method: "getAllClients",
                        data: JSON.stringify({}),
                    },
                    dataType: "json",
                    success: function (dataClient) {
                        var data = [{
                            id: "All",
                            client: "All"
                        }];
                        data = data.concat(dataClient);
                        var ComboObj = {
                            Clients_results: data,
                        };
                        that.getView().setModel(new JSONModel(ComboObj), "ComboModel");
                    },
                    error: function (request, error) {
                        // console.log("fhfj");
                    },
                });
            },

            onpressBack: function (oEvent) {
                this.oRouter.navTo("Main");
            },
            onChangeSelection: function () {
                var that = this;
                var data = {};
                this.byId("idClient").setSelectedKey("");
                data.month = this.byId("idMonth").getSelectedKey();
                data.year = this.byId("idYear").getSelectedKey();
                data.machineType = this.byId("idOperation").getSelectedKey();
                data.officeType = this.byId("idOffice").getSelectedKey();
                $.ajax({
                    url: that.uri,
                    type: "POST",
                    data: {
                        method: "getAllClientsWithParam",
                        data: JSON.stringify(data),
                    },
                    dataType: "json",
                    success: function (dataClient) {
                        var data = [{
                            id: "All",
                            client: "All"
                        }];
                        data = data.concat(dataClient);
                        var ComboObj = {
                            Clients_results: data,
                        };
                        that.getView().setModel(new JSONModel(ComboObj), "ComboModel");
                    },
                    error: function (request, error) {
                        // console.log("fhfj");
                        var ComboObj = {
                            Clients_results: [],
                        };
                        that.getView().setModel(new JSONModel(ComboObj), "ComboModel");
                    },
                });
            },

            onGetTransaction: function (oEvent) {
                var that = this;
                this.byId("idClientTx").setVisible(true);
                var Client = this.byId("idClient").getSelectedKey();
                var month = this.byId("idMonth").getSelectedKey();
                var year = this.byId("idYear").getSelectedKey();
                var machineType = this.byId("idOperation").getSelectedKey();
                var officeType = this.byId("idOffice").getSelectedKey();

                var data = {
                    Client: Client,
                    month: month,
                    year: year,
                    machineType: machineType,
                    officeType: officeType,
                };
                $.ajax({
                    url: that.uri,
                    type: "POST",
                    data: {
                        method: "getClientTransaction",
                        data: JSON.stringify(data),
                    },
                    dataType: "json",
                    success: function (dataClient) {
                        var detailsModel = {
                            results: dataClient
                        };
                        var sum = 0;
                        var count = dataClient.length;
                        dataClient.forEach(function (amount) {
                            sum = sum + parseInt(amount.total, 0);
                        });
                        that
                            .byId("idTableHeader")
                            .setText("All Transactions (" + count + ")");
                        that
                            .getView()
                            .byId("idTable")
                            .setModel(new JSONModel(detailsModel), "detailsModel");
                    },
                    error: function (request, error) {
                        that
                            .getView()
                            .byId("idTable")
                            .setModel(new JSONModel({
                                results: []
                            }), "detailsModel");
                        that.byId("idTableHeader")
                            .setText("All Transactions (0)");
                    }
                });
            },

            onResetTransaction: function (oEvent) {
                this.byId("idClientTx").setVisible(false);
            }
        });
    });

Output

1. Create Transaction

Create a transaction using PHP in SAP UI5

2. View Transaction

View a transaction using PHP in SAP UI5

Author

  • Barry Allen

    A Full Stack Developer with 10+ years of experience in different domain including SAP, Blockchain, AI and Web Development.


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.