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.
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.
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.
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.
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 data contains the name and address of the company or client. It lets the
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.
Quotations tables have a 1-N relationship.
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.
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;
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.
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;
These are all the tables we need for this database. Now let's move on to testing after creating each operation.
Remember to check that the acquisition and update operations are working properly.
You can look for a user by inputting an e-mail address and password. You can assume it was successful if the count is
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@example.com' AND users.passwd = SHA1(MD5('password')) AND users.status = 1;
SELECT users.id, users.email, users.lastname, users.firstname FROM users WHERE users.status = 1;
INSERT INTO users ( email, passwd, lastname, firstname, modified, created ) VALUES ( 'firstname.lastname@example.org', SHA1(MD5('password')), 'Kotsutsumi', 'Kazuhiro', NOW(), NOW() );
UPDATE users SET email@example.com', passwd=SHA1(MD5('password')), lastname='Kotsutsumi', firstname='Kazuhiro', modified=NOW() WHERE id=1
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
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
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;
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
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
Delete for now.
It's straightforward to say that once a quotation has been accepted, a bill is produced. Therefore, in data structures such as ours,
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.
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.