Toggle navigation

SUNNIES INTEGRATED DATABASE SYSTEM DOCUMENTATION

Version 1.0.0

Sunnies is an integrated database system developed for SUN Education Group. It is a web based application for managing all company’s relationships and interactions with their departments & staff.

It will definitely improve company’s productivity and profitability.

Your server need to meet the following requirements in order to run SUN Integrated Database System V.1

  • PHP 5.3.29
  • MYSQL 5.5.52
  • PHP Mcrypt Extention
  • PHP MBString Extension
  • PHP DOM Extension
  • PHP GD Extension
  • PHP MYSQL Extension
  • PHP PDO Extention
  • PHP CURL Extention
Migration Requirements
  • Main user database requires to have ALL PRIVILEGES (Super Privilege)
  • Main database requires to have user p3820r48 (create user)
  • Make sure database has Functions and Procedures. Else, import it using this file into the database.
Server Configuration
HTTPD

worker_processes: auto
worker_connections: 1024
client_max_body_size: 128m
send_timeout: 30
proxy_connect_timeout: 3600
proxy_send_timeout: 3600
proxy_read_timeout: 3600

NGINX

max_execution_time: 3600
max_input_time: 3600
memory_limit: 1G
error_reporting: E_ALL & ~E_DEPRECATED
display_errors: Off
post_max_size: 1G
upload_max_filesize: 1G

MYSQL

max_connections: 200
max_user_connections: 50
wait_timeout: 10
interactive_timeout: 200
max_allowed_packet: 128M

SAP =  Student application progress ( status application progress of student)
Apply = Student just applied
CLO = Conditional level offer (student is conditionally accepted by university)
FLO = Full level offer (student is completely accepted)
PAID =  Paid (student has paid the tuition fees)
VISA = Student’s visa in progress by embassy
Goal =  Visa approved & student leaving to the destination
Cancelled =  Student cancels using SUN’s service
Decline = Student is declined by some universities but still using SUN service.

SUN Integrated Database System is designed as a single product with separate modules, each one helping with specialized aspects of managing company’s relationships and sales. Departments and staff can use the appropriate modules for their tasks.

Dashboard

User profile

User’s information and role in the company.

sun_education_group_sunnies_1-6_-_20d16-11-29_15-02-09

Calendar/Agenda

A module to remind and document events.

sun_education_group_sunnies_1-6_-_2016-11-29_14-57-20
Branching

Unallocated client by Branch Manager. Master data is on Student > Branching

sun_education_group_sunnies_1-6_-_2016-12-05_13-48-54

Counselor

Unhandled client by Counselor. Master data is on Student > Allocate Counselor

sun_education_group_sunnies_1-6_-_2016-12-05_13-49-38

New Leads

A shortcut module to register new client/leads.

sun_education_group_sunnies_1-6_-_2016-12-05_13-50-08

Follow up Status

A summary of overall activities/follow up by Branch manager and Counselor as well to display client’s progress or status. May contain status of future years.

sun_education_group_sunnies_1-6_-_2016-12-05_13-50-23

SAP

Student Application Progress. A summary of overall client’s progress or status only in current year.

sun_education_group_sunnies_1-6_-_2016-12-05_14-35-46

Comments

Comments made by staff (branch manager & counselors) related with student’s application status.

sun_education_group_sunnies_1-6_-_2016-12-05_14-44-29

Today Birthday

A list of birth date of students/clients. Staff should congratulate people on this list via email.

sun_education_group_sunnies_1-6_-_2016-12-05_14-42-36
Last Activities

A module showing the last activity updated by staff.

sun_education_group_sunnies_1-6_-_2016-12-05_14-53-09

My Sales

A summary of overall annual sales performance

sun_education_group_sunnies_1-6_-_2016-12-05_16-32-41

sun_education_group_sunnies_1-6_-_2016-12-05_15-04-36

 

My KPI Performance

A summary of overall annual conversion performance

sun_education_group_sunnies_1-6_-_2016-12-05_14-57-56

sun_education_group_sunnies_1-6_-_2016-12-05_15-10-58

Notice

A reminder for staff to keep tracking and updating student’s application status.

sun_education_group_sunnies_1-6_-_2016-12-05_14-55-45

SUN of the day

A motivation message from Administrator

sun_education_group_sunnies_1-6_-_2016-12-05_15-18-50

All time file (download)

A generated excel file of the annual company’s performance

sun_education_group_sunnies_1-6_-_2016-12-05_15-20-50

General achievement

Overall achievement of the year

sun_education_group_sunnies_1-6_-_2016-12-05_15-23-18

Jakarta achievement

Overall achievement of the year. (Jakarta branches)

sun_education_group_sunnies_1-6_-_2016-12-05_15-24-24

Out station achievement

Overall achievement of the year. (other than Jakarta branches)

sun_education_group_sunnies_1-6_-_2016-12-05_15-25-21

Reporting

Event Registration List

A search module to get list of students who has registered on an event.

Follow Up List

A search module to get list of students based on their prospect status.

SAP (Student Application Process) List

A search module to get list of students based on their application status.

SAR (Student Application Report) List

A search module to get list of students who have their application approved. (status goal)

Student

Branching

A list of unallocated and return list of leads/student. Admin will assign unallocated or return leads to related branch manager based on their location.

Allocate Counselor

A list of unallocated leads/student. Admin will assign unallocated leads to related counselor based on their location.

Follow Up

A complete list of leads/student with their application status progress.

SAP

A list of registered leads/student with their application status progress.

SAR

A list of completed leads/student with their application status progress.

Event

Registration

A module to search or register a new event

Comments

A module to get list of made comments during the event

Membership

Admin Assistant
Administrator =  Super Administrator. All Privilege.
Admission
Admission Manager
BOD =  Super Administrator.
Branch Manager = Head Marketing Staff
Counselor = Marketing Staff
Deputy Director
Finance Officer
Product Manager
Deputy Director
Finance Officer
Product Manager
SUN English
Typist
Visa Officer
Sub Agent

Message

Compose

A module to write or broadcast a new message to staff, and leads/students.

Sent Item

History of sent messages

Trash

Where the unnecessary messages such will land

Master

Branch

A master list for branch office include their postcode data, and total sales performance

Country Destination

A master list for country destination

Credit Card Type

A master list of payment method/credit cards

Currency

A master list of currency

Document Archive

A master list of uploaded documents or archieves

Highest / Current Education

A master list of education degree

Marketing Source

A master list of source where clients have found SUN

Name of School Education

A master list of school institution

Program Reference

A master list of program reference

Target Counselor

A master list of staff or counselor

University

A master list of university

The basic organizational structures and facilities needed for the operation Sunnies Integrated Database System.

Class Section (/class)

class

Contains the Classes Used Within This Application

xmodules Section (/class/xmodules)

xmodules.class.php

This is the Modules base class. Sub-classes that extends xmodules are

  • comments
    This is the commenting section that handles/manages retrieval and display of all comments for different user
  • dashboard
    This is the section that handles/manages Dashboard information retrieval/display as well as track status of all operations if it went well or was at some point unhandled
event/event.class.php

This is the section that coordinates and reflect event that takes place by doing several things like updating the navbar, tracks the generation of excel/spreadsheets, as well as data backup event etc

login/login.class.php

This gracefully handles login actions and displays approporiate message while loading the application

masterpage

This handles master page information retrieval and display

mdata

This handles meta data/information regarding the site, such as aggregae information calculation/retrieval and display, including calendars, graphs

membership

This handles the retrieval and/or display list and details of all members on the system

message

This handles specifically the mailing/message features/functionality between users within as well as with those outside via their email address

registration

This handles actions such as student registration by providing the form and processing it

rptlst

This section handles all report list, including the followup report, registration report, SAR report, SAR report etc

rptrcp

This category/section handles Recarp of the Report for easy assimilation or overview

status

This category or section retrieves/displays the status of system audit by records

student

This handles student related information handling/processing/and spreadsheet or excel compiling or setup for export etc.

xauthenticate

Handles logged in users authentication

ximport

handles and processes importation of data from CSV/excel/spreadsheet files

xsql

handles and processes SQL query read from files stored in predefined location

xuser

This handles application user access, creates user role, etc.

xbase Section (/class/xbase)

xbase

This category  manages system audit records, handles mailing, excel/spreadsheet, dumping/exporting/backing up of database contents, writing to and reading from file, form

xbase/xaudit

Manages system audit record by deleting all, a range of or a single audit record

xbase/xcel

This contains PHPExcel Library, it handles Excel generation and management

xbase/xdump

This category handles dumping of database contents to sql file

xbase/xfile

This handles files such as resizing, ajax uploads, reading and writing to files

xbase/xform

This section dynamically handles form generation with form controls

xbase/xgenerator

This section handles generation of unique characters for different required formats depending on parameters

xbase/xlang

This category handles languages used as well as locale

xbase/xmail

This category handles the mailing aspects of this application)

xbase/xnavbar

This section or category dynamically handles the navbar links display of this application

xbase/xprovider

This section or category handles membership status, required access credentials such as email password, login attempts

xbase/xsession

This handles session storage/retrieval from the database

xbase/xstring

This handles common string manipulation functions e.g lower or uppercase etc.

xbase/xtemplate

This is a template engine, that reads a template and processes it by replacing contents where needed before presenting the final version.

xbase/xtime

This handles time/date related functionality

xbase/xxml

This is a group of xml parsing functions for reading contents from XML files

xdb Section (/class/xdb)

xdb

This handles major database functions or operations such as connection, transactions, rollback,commit, database selection and insert, update, execute etc. which could be any of the two listed below

xfpdf Section (/class/xfpdf)

xfpdf

This collection/library handles anything relating to PDF(Portable Document File) Definition and creation before it is exported or generated, below are the subcollections of this library

xmain Section (/class/xmain)

xmain

This Cordinates the operations between the template engine, the providers and the system audit collection/section to act as a single one unit entity

Configuration Hierarchy (/config)

config

This configuration actually sets up the entire application coordination by defining database

Files Hierarchy (/files)

files/commons

Contains the style sheets and javascript as well as the fonts

files/commons/media

contains site common images such as loading gif images etc)

files/commons/js

Contains javascripts files that executes various funtions such as file download, upload, chats, clipboards, etc.

files/media

This contains all uploads relating to the application for the attachment, dashboard, leads, member, etc.contains other attachment stored in folders  contains excel sheets that is downloadable via the dashboard , contains the image or passport of members, named by email, Contains PDF files for the sar grouped by folders

Global Section (/globals)

globals

This contains resources and template files that would be read and interpreted by the template engine

Layout section (/layout)

layout

This category contains XML files that defines different sections for layouts of different aspects of the application

A-Z Testing & Conclusion

This is a Customer Database & Relationship Management (CRM) system and so it contains a lot of sensitive data. So the testing mainly concentrates to ensure that the data is accurate and secure during storage and retrieval. This involves below steps:

1. Data Quality and Conversion

The first test cycle focuses on issues related to data quality and conversion. At every step of a migration or update you should be verifying that the CRM is working as expected both with and without data. Because if the data is not there then the CRM is nothing without the data. To ensure data activities there are certain features to test with data, they are:

  • Data-duplication
  • Data-mapping
  • Wrong data entry
  • Missing of data
  • Data sorting
  • Data security
  • Hidden datas
  • Missing of datas
  • Data updations
  • Editable options of datas
  • Data used for graphical representations
  • Data used for reporting etc..

Check that data which is not supposed to be editable is indeed not editable: Data like date and time of transaction should not be editable.

 

2. Functionality

The second cycle of testing focuses on testing the functional aspects of the system. Regression testing of data quality and conversion should also be done. Like below

  • Transaction processes
  • Connection lost
  • Proper exception handling
  • Avoid data mismatch
  • Pre-Authorisation
  • Post-Authorisation
    3. Integration

The third testing cycle focuses on integration, passing data to or from external systems, with a regression testing done for data quality and functionality. This will be a challenge in CRM applications where we will be migrating or integrating some other applications into the present CRM. We must see some of the features.

  • Accuracy and reliability
  • Sorting orders
  • Validations of datas
  • Reports and formats
  • Date format
  • Dependencies are integrated properly
  • Field and Labels matching each other

 

4. Regression and User Acceptance Testing

All of the end-to-end business processes are validated by the customer through user acceptance testing. User acceptance testing is process-level testing. All of the testing that has been done to this point is module-level testing. It includes below:

  • Stress
  • Scalability
  • Confidentiality
  • Reliability
  • Functional Correctness and Completeness
  • Usability

 

5. Not-so-final regression test

Once all of the critical defects have been fixed run another round of regression tests on all of the above.

Any time requirements change (a common occurrence in CRM implementations), new features are added or defects are fixed, you should run these regression tests again.

There will be regression testing always in an application like CRM where we need to test before migration and after migration. After migration whether the values are populated into the correct fields and validations are matched with the data. Other fields should not be disturbed because of this. This will measure:

  • Functional correctness and completeness
  • Reliability
  • Confidentiality
  • Scalability
  • Even performance

Any time requirements change (a common occurrence in CRM implementations), new features are added or defects are fixed, you should run these regression tests again.

 

6. General Testing (End to End)
  • Admission Calendar
    Front-End: Valid ; Database: (Not Displaying Invalid), Admin: Valid, Counselor: Valid, Branch Manager: Valid (implies other users: Valid)

Modification Authentication/Access Level: Admission Calendar Update Limited Only to the Administrator

  • User Session Data

Stored on the database (improved security. GOOD)

  • Speed
    The use of stored procedures, triggers and views helps make the application query the database faster which is good
  • Messages Module
    The Admin sending message to external or custom emails seems not to be working after testing my email out, the email is not configured or se tup properly.

Suggestions

  1. Email server development

    Email within server that is working would be setup to test this messaging aspect in more detail as communication and information flow within the organization is very crucial and critical.

  2. Web server upgrade & configuration

    Server upgrade to an powerful machine that allows big data process and traffic. A daily backup function is highly required to avoid data loss.

  3. PHP Version Upgrade

    Major upgrade PHP version from 5.3 to PHP 5.5 or 7.0. This action is very recommended, but requires to be executed very intensively since it will affect its current infrastructure. This action will be needed when we’re going to develop some high-end features.

  4. Data Encryption

    SSL installation. Since the application store many sensitive and important data, a data encryption is very critical.

  5. Database Optimization

    Double data or queries will affect the operational and will create bugs. It will also slow down server performance. Database optimization will clean up unnecessary data that makes the system running smoother.

General Definition:


TMAL = Unallocated
TMUH = Unhandled
OC = On Call
SC = Successful Called
IR = Interested
WC = Willing to Come
RS = Reschedule
NI = Not Interested

UC = Unsuccessful Called
BS = Busy / Call him back
RJ = Rejected
VM = Voice Message
NA = No. Answer
OT = Other
CF = Call Failed
NN = No Number / Blank Number

UR = Unreachable
NR = Not Registered
STC = Stop Calling
RU = Refollow Up
VO = Visted Office
VE = Visted Event

 

Telemarketing Leader

A. Login to System


1. Open Login Page
2. Entry for Username
3. Entry for Password
4. Click on Sing In Button

B. Allocating Leads to Telemarketing Staff

1. Find the Leads
(Go to page Student > Telemarketing)

2. Click Sub Menu Unallocated Status

3. Select one or multiple leads


4. Click button Set Telemarketing Staff then Choose Telemarketing Staff and click Yes to cofirm the dialog

C. Assingning Leads to Telemarketing Staff

1. Find the Leads
(Go to page Student > Follow Up)


2. Select one or multiple leads

3. Click button Set Telemarketing Staff and click Yes to cofirm the dialog

D. Reporting Activity Progress

1. Open Page : Reporting > Telemarketing Activity Log

2. Filter data by Date (Today, Yesterday, etc…) or Using Feature Advanced Search to Filter Telemarketing Staff

E. Reporting Performance

1. Open Page : Reporting > Telemarketing Performance

2. Filter data by Date (Today, Yesterday, etc…) or Using Feature Advanced Search to Filter Telemarketing Staff

Telemarketing Staff

A. Login to System


1. Open Login Page
2. Entry for Username
3. Entry for Password
4. Click on Sing In Button

B. Proccessing the Leads (On Call)

1. Open Page : Student > Telemarketing

2. Open Tab: Unhandled

3. Choose the Leads and click button “Detail”

4. Open Tab Info
And Start Making Call (by Phone, WhatsApp, Social media etc.)

5. Open Tab Telemarketing Status
– Choose Status (On call or Call failed)
– Select Secondary Status (Succesfull or Unsucceful)
– Select Third Status (Interested, Not Interested, Reschedule or Willing to Come)
– Choose Communication Tools
– Write a Note
– and Save

C. Proccessing the Leads (Willing to Come)

1. Open Page : Student > Telemarketing

2. Open Tab: Unhandled

3. Choose the Leads and click button “Detail”

4. Open Tab Info
And Start Making Call (by Phone, WhatsApp, Social media etc.)

5. Open Tab Telemarketing Status
– Choose Status (On call or Call failed)
– Select Secondary Status (Succesfull or Unsucceful)
– Select Third Status (Willing to Come)
– Pick date and time
– Choose Communication Tools
– Write a Note
– and Save

D. Reporting Activity Progress

1. Open Page : Reporting > Telemarketing Activity Log

2. Filter data by Date (Today, Yesterday, etc…)

E. Reporting Performance

a. Open Page : Reporting > Telemarketing Performance

b. Filter data by Date (Today, Yesterday, etc…)

Counselor

A. Login to System


1. Open Login Page
2. Entry for Username
3. Entry for Password
4. Click on Sing In Button

B. Share Leads to Telemarketing

1. Go To Page : Student > Follow Up

2. Click on any Sub Menu Statuses to find the leads (example: Unhandled, In Progress, Prospect, Future Prospect, Not Prospect)

3. Click on Select button (One or Multiple Lead)
On the right top of page, click button Share to Telemarketing

4. And then click Yes to confirm
*) This lead(s) will be shared to Telemarketing Leader under status Unallocated

C. Check Willing to Come

1. On Dashboard open tab Willing To Come or Go To Page : Student > Follow Up (Under sub menu status Telemarketing > Willing To Come)

2. Click Detail and Open Status Tab
# Check the date/time and make a call a few days earlier to confirm with the Lead

3. Choose Status
# If the lead unable to attend and want to reschedule, choose Reschedule, set another time schedule and save.
# If the lead has visited but still not willing to apply choose Refollow Up, mark as Visited office or event and save.
# If the lead has visited and willing to apply chose Apply , mark as Visited office or event and save.

*) This lead(s) will move to SAP to be further handled by the counselor

Created: 24 Nov 2016
Latest Update: 25 April 2018
By: Digital Niaga Solusindo (formerly Wibs Digital)
Email: info@dns.co.id