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
Step 01: Download, Install, and Run XAMPP
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
2. View Transaction
