Ext JS Data-driven Application Design

By Kazuhiro Kotsutsumi
    Advance your knowledge in tech with a Packt subscription

  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies

About this book

Sencha Ext JS is an industry leader for business-standard web application development. Ext JS is a leading JavaScript framework that comes with a myriad of components, APIs, and extensive documentation that you can harness to build powerful and interactive applications. Using Ext JS, you can quickly develop rich desktop web applications that are compatible with all major browsers.

This book will enable you to build databases using information from an existing database with Ext JS. It covers the MVC application architecture that enables development teams to work independently on one project. Additionally, the book teaches advanced charting capability, enabling developers to create state-of-the-art charts just once. These charts are compatible with major browsers without the need to rely on plugins.

This hands-on, practical guide will take you through the mechanics of building an application. In this instance, we will use this application to manage existing data structures in the form of a database.

You will begin by making SQL and tables in MySQL and will then move on to developing the project environment and introducing Sencha Cmd. You will learn to create a form to input data and monitor the state of the input, while seeing how Ext Direct will validate the form on the server side.

Finally, you will have a working application that is ready for you to customize to suit your needs. You can also use it as a template for any future projects when you need a similar database.

Publication date:
December 2013
Publisher
Packt
Pages
162
ISBN
9781782165446

 

Chapter 1. Data Structure

This book will take you step-by-step through the process of building a clear and user-friendly sales management database in Ext JS using information from an existing database.

This book is intended for intermediate Ext JS developers with operational knowledge of MySQL and who want to improve their programming skills and create a higher-level application.

The finished application will give you a working sales management database. However, the true value of this book is the hands-on process of creating the application, and the opportunity to easily transfer and incorporate features introduced in this book in your own future applications.

The standout features we will look at while building this application are as follows:

  • History-supported back button functionality: We will customize the Ext JS function to create a lighter method to scroll forwards and backwards while staying on a single page.

  • More efficient screen management: We'll learn how simply registering a screen and naming conventions can help you cut down on the screen change processes; meaning you can focus more on the implementation behind each screen. Also, it will be easier to interact with the history just by conforming to this architecture.

  • Communication methods with Ext.Direct: Ext.Direct has a close affinity with Ext applications which makes for easier connection, easier maintenance, and removes the need for the client side to change the URL. Also, if you use Ext.Direct, you can reduce the stress on the server side as it combines multiple server requests into just one request.

  • Data display methods with charts: In Ext JS, by simply adjusting the store and the data structure set to display in a grid, we can display the data graphically in a chart.

This chapter will give you the basic building blocks of your database. In this chapter of the book, you will write the SQL code and and create tables in MySQL.

 

The structure of the application – User, Customer, Quotation, Quotations, Bill, and Bills


First, let's look at the structure of the application we're about to build. This is a sales management application built for the user to register customers, send quotations for orders, and finally to invoice the customer with a bill.

The user can input data in to the Customer table. The customer can be an individual or a company, either way, each customer receives a unique ID.

The Quotation table represents the final quotation sent to the customer. The Quotations table contains the individual items being ordered in the quotation.

A bill is the final invoice sent to the customer. As with the Quotations table, the Bills table refers to the individual items ordered by the customer.

 

The user


The user data is a simple structure that is used to log in to a system. It has an e-mail address, a password, and a name.

Do not delete the user data and physically manage it with a flag. It is connected to other data structures with joint ownership, recording the date and time when it was created along with the updated date and time.

When we design a table with a model of MySQL, it looks similar to the following table. After having carried out MD5, we perform SHA1. Then, we will have 40 characters and can store the password.

 

The customer


The customer data contains the name and address of the company or client. It lets the Quotation and Bill tables perform a relation of this data and use the data. Being the master data, adding to and deleting from the user interface is not available at this time. However, as you develop the application, you eventually should be able to edit this data.

The following screenshot shows the input fields for registering a customer. The sections under the Name column are the fields that need to be filled in for each customer. The Type column refers to the type of data to be entered, such as words, numbers, and dates. The Key column allows data to be referenced between different tables.

 

Quotation and Quotations


The Quotation and Quotations tables have a 1-N relationship.

In Quotation, you can save the basic information of the document, and in Quotations you can store each item being ordered.

Quotation

This following screenshot shows the fields necessary for Quotation. The table headings are the same as in the Customer table explained previously, so let's fill this out accordingly.

Quotations

This is the same as before, so let's go ahead and fill this out. The parent refers to the overall quotation that the Quotations (individual items) table belongs to.

 

Bill and Bills


The Bill table is almost the same as the Quotation table. However, the Bill table can sometimes contain the ID of an associated Quotation table.

Bill

The following screenshot shows the Bill table:

Bills

Similar to Quotations, in Bills you can store each item that is ordered:

 

Creating and dealing with the customer structure tables


We will be using MySQL, and the database character is set to utf8 and collation is set to utf8_bin. When SQL describes the details of what we defined previously, each of these components are as follows.

The User table

The User table we prepared earlier becomes operational when the following code is executed. It's important to remember to include AUTO_INCREMENT in the id column; otherwise, you have to input it manually:

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS 'users';
CREATE TABLE 'users' (
  'id' bigint(20) NOT NULL AUTO_INCREMENT,
  'status' tinyint(1) NOT NULL DEFAULT '1',
  'email' varchar(255) NOT NULL,
  'passwd' char(40) NOT NULL,
  'lastname' varchar(20) NOT NULL,
  'firstname' varchar(20) NOT NULL,
  'modified' datetime DEFAULT NULL,
  'created' datetime NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;

The Customer table

Once the following code is executed, the Customer table becomes operational:

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS 'customers';
CREATE TABLE 'customers' (
  'id' bigint(20) NOT NULL AUTO_INCREMENT,
  'status' tinyint(1) NOT NULL DEFAULT '1',
  'name' varchar(255) NOT NULL,
  'addr1' varchar(255) NOT NULL,
  'addr2' varchar(255) DEFAULT NULL,
  'city' varchar(50) NOT NULL,
  'state' varchar(50) NOT NULL,
  'zip' varchar(10) NOT NULL,
  'country' varchar(50) NOT NULL,
  'phone' varchar(50) NOT NULL,
  'fax' varchar(50) DEFAULT NULL,
  'modified' datetime DEFAULT NULL,
  'created' datetime NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;

This is the foundation of creating an initial set of tables that can later be populated with data.

The Quotation table

This is the corresponding code for the Quotation table. As with the Customer table, this code snippet will lay the foundation of our table.

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS 'quotation';
CREATE TABLE 'quotation' (
  'id' bigint(20) NOT NULL AUTO_INCREMENT,
  'status' tinyint(1) NOT NULL DEFAULT '1',
  'customer' bigint(20) NOT NULL,
  'note' text NOT NULL,
  'modified' datetime DEFAULT NULL,
  'created' datetime NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS 'quotations';
CREATE TABLE 'quotations' (
  'id' bigint(20) NOT NULL AUTO_INCREMENT,
  'status' tinyint(1) NOT NULL DEFAULT '1',
  'parent' bigint(20) NOT NULL,
  'description' varchar(255) NOT NULL,
  'qty' int(11) NOT NULL,
  'price' int(11) NOT NULL,
  'sum' int(11) NOT NULL,
  'modified' datetime DEFAULT NULL,
  'created' datetime NOT NULL,
  PRIMARY KEY ('id'),
  KEY 'parent' ('parent')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;

The Bill table

As with the previous two code snippets, the following code for the Bill table is very similar to the Quotation table, so this can be found in the source file under 04_bill_table.sql.

These are all the tables we need for this database. Now let's move on to testing after creating each operation.

 

Creating each operation and testing


Because we will use PHP in later stages, let's prepare each operation now. Here, we will insert some temporary data.

Remember to check that the acquisition and update operations are working properly.

User authentication

These are some SQL code you can use to develop your database.

You can look for a user by inputting an e-mail address and password. You can assume it was successful if the count is 1.

For increased password security, after having carried out MD5 encryption, you should store the password as a character string of 40 characters after being put through SHA1.

SELECT
    COUNT(id) as auth
FROM
    users
WHERE
    users.email = '[email protected]'
AND
    users.passwd = SHA1(MD5('password'))
AND
    users.status = 1;

Selecting the user list

This is used when you want to collect data for use in a grid. Make note of the fact that we are not performing the limit operation with PagingToolbar:

SELECT
    users.id,
    users.email,
    users.lastname,
    users.firstname
FROM
    users
WHERE
    users.status = 1;

Adding users

To add a user, put the current time in created and modified:

INSERT INTO users (
    email,
    passwd,
    lastname,
    firstname,
    modified,
    created
) VALUES (
    '[email protected]',
    SHA1(MD5('password')),
    'Kotsutsumi',
    'Kazuhiro',
    NOW(),
    NOW()
);

Updating the user information

Every time the modified file should be set to NOW() for it to be used as a time stamp. Other fields should be updated as needed.

UPDATE
    users
SET
    email='[email protected]',
    passwd=SHA1(MD5('password')),
    lastname='Kotsutsumi',
    firstname='Kazuhiro',
    modified=NOW()
WHERE
    id=1

Deleting users

Deletion from this system is not a hard purge where the user data is permanently deleted. Instead we will use a soft purge, where the user data is not displayed after deletion but remains in the system. Therefore, note that we will use UPDATE, not DELETE. In the following code, status=9 denotes that the user has been deleted but not displayed. (status=1 will denote that the user is active).

UPDATE
    users
SET
    status=9
WHERE
    id=1
 

The Customers table


Although Add, Update, and Delete are necessary operations, we'll come to these in the later chapter, so we can leave it out at this time.

The customer information list

Here we are preparing the SQL code to pull information about customers later on:

SELECT
    customers.id,
    customers.name,
    customers.addr1,
    customers.addr2,
    customers.city,
    customers.state,
    customers.zip,
    customers.country,
    customers.phone,
    customers.fax
FROM
    customers
WHERE
    customers.status = 1;

Selecting the quotation list

Next comes the code for selecting the Quotation lists. This is similar to what we saw for the customer information list. For the code, please refer to the source file under 11_s electing_quotation_list.sql.

Items

The code for items will select the quotation items from the database. This will pick up items where quotations.status is 1 and quotation.parent is 1:

SELECT quotations.description, 
  quotations.qty, 
  quotations.price, 
  quotations.sum
FROM
  quotations
WHERE
  quotations.'status' = 1
AND
  quotations.parent = 1

As this is similar to Customers, you can again leave out Add, Update, and Delete for now.

 

The Bill table


Again let's leave out Add, Update and Delete for now because the Bill table is similar to what preceded this.

It's straightforward to say that once a quotation has been accepted, a bill is produced. Therefore, in data structures such as ours, Quotation and Bill are related. The only difference is that Bill contains the extra Quotation ID to create the relationship between the two.

Also, remember the customer information list is almost the same as the quotation list.

 

Summary


In this chapter, we have defined the structure of the database we will use in this book.

You might have your own databases that you want to present in Ext JS. This is just a sample database that we can build on in the coming chapters.

In the next chapter we will begin the process of building the whole application. Don't worry, we'll explain each step.

About the Author

  • Kazuhiro Kotsutsumi

    Kazuhiro Kotsutsumi was born in Sapporo, Japan in 1979. He started using C/C++ at the age of 14 and proceeded to learn MASM, Delphi, ActionScript, PHP, C#, Perl, and so on.

    After working for a web systems development company while enrolled at college, he began programming built-in functions for cell phones.

    Having worked as a Project Manager and a freelance programmer for one year, he established his company Xenophy CO., LTD in 2006.

    Xenophy has always offered a variety of IT web solutions and has become synonymous with the promotion and expansion of Sencha in Japan.

    Currently, Xenophy is a Sencha Reseller and the official Sencha training partner in Japan. In September 2013, Xenophy announced the launch of Sencha Official Training in Japan, a fully localized course with Sencha's official training materials adapted for the Japanese market.

    He has already published two Sencha Ext JS guides in Japanese, including Sencha EXT JS 4 – A Practical Developing Guide and he recently co-authored a Sencha Touch guide.

    Browse publications by this author
Book Title
Unlock this book and the full library for only $5/m
Access now