Designer Wizard

Introduction

The effectiveness of any loan management system hinges on the quality of the reports it produces.

Reports, in this context, encompass the collection, aggregation, listing, and/or integration of relevant data into a format easily comprehensible for users, presenting valuable information.

Keeping this principle in mind, Mobiloan places a specific emphasis on not only securely creating and storing data in a robust database environment but also on empowering users with the freedom and flexibility to generate and tailor reports according to their unique business requirements.

Users have the option to utilise Mobiloan's standard report suite, request custom reports through a purchase, or even take the initiative to design their own reports using the user-friendly design wizard or by crafting their SQL scripts for personalised report generation.

Accessing the Mobiloan Scripting Functionality

Users wanting to make use of the custom mySQL script functionality can access the same via :

HOME SCREEN -> REPORTING -> CUSTOM RADIO BUTTON -> + NEW REPORT -> CUSTOM SQL ON BLANK TEMPLATE

Example of the reporting screen when accessing the custom sql on blank template functionality

SCRIPTING LANGUAGES

Not all SQL's are the same.

Mobiloan makes use of a mySQL database. The scripting syntax for mySQL is similar to that of other relational databases such as PostGres (sql) and Microsoft SQL (mssql) for example but not identical.

Users must be careful when using AI to generate scripts or even when researching syntax and ensure that they are referencing documentation related to mySQL.

Graphical interface for scripting in Mobiloan

When starting the report generation process, users will first be asked to enter a name for their report

New report name input screen

Mobiloan will generate a sequential report number. The next step is to start writing your script.

Example of script input screen

Data Model

Before you can start writing scripts to extract your data you need to have an understanding of the available models, fields and relationships in the data model in Mobiloan.

ACCESS TO DATA MODEL

A password protected file will be sent to you for reference purposes.

Users wishing to make use of the custom sql functionality must please email [e-mail] requesting a copy of the full data model.

Not that dissemination or distribution of the data model or extracts thereof will be in direct violation of your non disclosure agreement and will result in prosecution.

Below is an extract from the Mobiloan data model. For protection in intellectual property purposes we have only shown one model in the extract.

<?xml version="1.0" encoding="UTF-8"?>
<data-model>
    <!-- TRANSACTION -->
    <model name="transaction" label="Transaction">
        <field name="transaction_type" label="Transaction Type" type="single-choice">
            <option key="cash">Cash</option>
            <option key="debit_order">Debit Order</option>
            <option key="wallet">Wallet</option>
            <option key="eft">EFT</option>
            <option key="credit_card">Credit Card</option>
            <option key="pos">POS</option>
            <option key="payout">Payout Deduction</option>
            <option key="payroll">Payroll Deduction</option>
            <option key="journal">Journal</option>
            <option key="insurance">Insurance</option>
            <option key="other">Other</option>
        </field>
        <field name="transaction_group" label="Transaction Group" type="single-choice">
            <option key="loan_capital">Loan Capital</option>
            <!-- linked to client, loan -->
            <option key="loan_instalment">Loan Instalment</option>
            <!-- linked to client, loan, collection -->
            <option key="commission">Commission</option>
            <!-- linked to agent_id -->
            <option key="purchase_sale">Purchase and Sale</option>
            <!-- linked to any -->
        </field>
        <field name="date_created" label="Date Created" type="datetime" />
        <field name="category" label="Category" type="text" />
        <field name="transactor" label="Transactor" type="text" />
        <field name="description" label="Description" type="text:paragraph" />
        <!-- Loan and Instalment No -->
        <field name="amount" label="Amount" type="number" />
        <field name="balance_amount" label="Balance Amount" type="number" />
        <field name="transaction_number" label="Transaction Number" type="text" />
        <field name="image" label="Image" type="photo" />
        <field name="attachment" label="PDF" type="attachment" media="application/pdf" />
        <field name="user_transaction" label="User Transaction" type="boolean" />
        <!-- Relationship -->
        <belongs-to model="agent" />
        <belongs-to model="area" />
        <belongs-to model="branch" />
        <belongs-to model="client" />
        <belongs-to model="loan" name="payout_loan" />
        <belongs-to model="loan" name="repayment_loan" />
        <belongs-to model="operator" />
        <belongs-to model="operator" name="transactor_operator" />
        <belongs-to model="worker" name="user" />
        <belongs-to model="transaction" name="linked" />
        <!-- cash rounding journal or settlement journal -->
        <belongs-to model="transaction" name="contra" />
        <belongs-to model="transaction_supplier" />
        <belongs-to model="transaction_recon" />
        <belongs-to model="transaction_recon" name="contra_recon" />
        <belongs-to model="transaction_category" />
    </model>
</data-model>

With reference to the above model below is an explanation of the the various tags :

Tag Name	Tags	Description
data-model	<data-model></data-model>	This denotes the start and end of the mobiloan data model.
model	<model></model>	This indicates the start and end of a model. The name attribute will be the table reference you will use when querying or joining tables.
field	<field /> or <field></field>	This indicates a field for a model and will always be wrapped in a model tag. The name attribute will be the table reference you will use when querying fields or joining tables. The type attribute is also worth bearing in mind when deciding on how to filter your data using your WHERE clauses.
belongs-to	<belongs-to />	This is a relationship tag and represents how this object is related to other models. The relationship can be described as one to one which means an object can only be linked to another single object. For example if a transaction belongs to a client you can join the related client to the transaction using the client_id stored on the transaction table.

MODEL COMPLETENESS

For purposes of demonstration the above model does not include the index or display or has-many tags and related values as these are irrelevant to users when building your mySQL queries.

Simple Example

The mySQL custom functionality gives you the user, endless possibilities with regards to the types of reports you can generate.

Below is a simple example of a basic query with an explanation directly thereunder :

SELECT * 
FROM address a 
INNER JOIN 
client c ON a.client_id = c.id
WHERE a.status = 'Active'

Syntax item	Description
SELECT	This is the start of the statement and references the fields you want output in your result.
*	This is a 'wildcard' character that effectively means show all, so in this case you will see all fields on the address and client tables in the output.
FROM	This keyword references the main table being queried. All related tables must have a link in some way to this primary table.
address a	This tells mySQL that moving forward we will use the letter 'a' when referencing the 'address' table.
INNER JOIN	This is the start of the table joining functionality. There are different types of joins but the syntax is similar for each.
client c	This tells mySQL that moving forward we will use the letter 'c' when referencing the 'client' table.
ON a.client_id = c.id	This tells mySQL which two fields form the link between the two tables allowing for the JOIN. In this case the address table has a field called client_id because address belongs to a client and in order to join the two rows the client_id in the address table must match the client id field on the client table.
WHERE	This is a conditional statement, in this case we are saying we only want to see addresses with the client detail where the address status is still active.

The above is a very simple query and over time you can start creating more complex queries as you become more familiar with the mySQL syntax.

Learning Material

The team at Mobiloan recommends W3Schools for learning the basic and even advanced mySQL scripts. See below for access to this resource.

https://www.w3schools.com/mysql/default.asp