Friday, June 29, 2018

Main Page (The Dashboard)


After entering username and password in the login page, users redirects to main.php page. This is the landing page of the system. We do not include anything here other than search a customer by NIC and a java script clock. But generally we can include many details in this page as well - such as a table of data of due payments, daily sales, or a graphs of sales, etc.

In this landing page (main.php), there is a java script clock that displays the local time (or hosted in a online web server, it displays server time). There is a form to enter the NIC of a customer and search customer by NIC number in the system. The action url of the form is search.php, when submit the form, system will go to search.php page and display search result there. As you can see, this page and all other pages uses common header.php and footer.php files.

<?php include("header.php"); ?>
<div style="float:right; width:250px; margin-right:5px;">
<form method="post" action="search.php">
N.I.C. <input type="text" name="nic" size="15" /> <input style="border:1px solid #FFCC00;" type="submit" name="submit" value="Search" />
</form>
</div>
<p style="text-align:center;"><img src="prezire.jpg" alt="Business System" width="970px"/></p>

<div align="center">
<font face="Arial, Helvetica, Sans Serif" size="3" color="#0000FF"><b>
<span id="clock">

<SCRIPT LANGUAGE="JavaScript">

var dayarray=new Array("Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
var montharray=new Array("January","February","March","April","May","June","July","August","September","October","November","December")
function getthedate(){
var mydate=new Date()
var year=mydate.getYear()
if (year < 1000)
year+=1900
var day=mydate.getDay()
var month=mydate.getMonth()
var daym=mydate.getDate()
if (daym<10)
daym="0"+daym
var hours=mydate.getHours()
var minutes=mydate.getMinutes()
var seconds=mydate.getSeconds()
var dn="AM"
if (hours>=12)
dn="PM"
if (hours>12){
hours=hours-12
}
{
 d = new Date();
 Time24H = new Date();
 Time24H.setTime(d.getTime() + (d.getTimezoneOffset()*60000) + 3600000);
 InternetTime = Math.round((Time24H.getHours()*60+Time24H.getMinutes()) / 1.44);
 if (InternetTime < 10) InternetTime = '00'+InternetTime;
 else if (InternetTime < 100) InternetTime = '0'+InternetTime;
}
if (hours==0)
hours=12
if (minutes<=9)
minutes="0"+minutes
if (seconds<=9)
seconds="0"+seconds
//change font size here
var cdate=dayarray[day]+", "+montharray[month]+" "+daym+" "+year+" | "+hours+":"+minutes+":"+seconds+" "+dn+""
if (document.all)
document.all.clock.innerHTML=cdate
else if (document.getElementById)
document.getElementById("clock").innerHTML=cdate
else
document.write(cdate)
}
if (!document.all&&!document.getElementById)
getthedate()
function goforit(){
if (document.all||document.getElementById)
setInterval("getthedate()",1000)
}
window.onload=goforit
//  End -->
</script>

</span>
</b>
</font>
</div>

<?php include("footer.php"); ?>

Style Sheet (CSS)


CSS (Cascade Style Sheet) is use to decorate a web page. It is the look and feel of a website or web application. In this application we use very simple set of styles defined in style.css file. This is mostly CSS 2.0 standard and the latest one is CSS 3.0 standard.

/* CSS Document */
body{
font-family:Verdana, Arial, Helvetica, sans-serif;
font-size:11px;
color:#666666;
}
div.main-wrapper{
width:980px;
padding:5px;
margin:0 auto;
background-color:#FFFFFF;
}
div.top-banner{
width:100%;
border-bottom:4px double #006699;
margin-bottom:20px;
}
div.top-banner p{
font-size:9px;
margin-top:-15px;
}
div.top-banner h2{
margin-top:-10px;
color:#006699;
}
div.top-banner-1{
float:left;
width:70%;
}
div.top-banner-2{
float:left;
width:29.8%;
text-align:right;
font-size:10px;
}
div.clear{
clear:both;
}
span.small-btn{
background-color:#FFCC00;
color:#FF3300;
border:1px solid #FF3300;
padding:2px 3px;
}
span.small-btn a:link{color:#FF3300; text-decoration:none}
span.small-btn a:visited{color:#FF3300; text-decoration:none}
span.small-btn a:hover{color:#FF3300; text-decoration:none}
span.small-btn a:active{color:#FF3300; text-decoration:none}
span.main-btn{
background-color:#0099CC;
color:#FFFFFF;
border:1px solid #000099;
padding:2px 3px;
font-size:12px;
}
span.main-btn a:link{color:#FFFFFF; text-decoration:none}
span.main-btn a:visited{color:#FFFFFF; text-decoration:none}
span.main-btn a:hover{color:#FFFFFF; text-decoration:none}
span.main-btn a:active{color:#FFFFFF; text-decoration:none}
div.links-set{
width:100%;
}
p.small-text{
font-size:9px;
text-align:center;
color:#666666;
}
form{
padding:5px;
background-color:#F9F7C6;
border:1px solid #F2E482;
}
input.btn{
width:auto;
}
a.wht:link{color:#FFFFFF; text-decoration:none}
a.wht:visited{color:#FFFFFF; text-decoration:none}
a.wht:hover{color:#FFFFFF; text-decoration:none}
a.wht:active{color:#FFFFFF; text-decoration:none}


h1{
text-align:center;
color:#006699;
text-decoration:underline;
}
h2{
text-align:left;
font-style:italic;
color:#009966;
}
h3{
text-align:left;
font-style:italic;
color:#009966;
border-bottom:1px dashed #009966;
}
p.red{
color:#FF0000;
}
p.green{
color:#009900;
}
span{
color:#FF0000;
font-size:10px;
}
table.ledger{
border-collapse:collapse;
}
tr.header{
font-weight:bold;
color:#FFFFFF;
background-color:#FF6600;
font-size:12px;
}
tr.wht{
background-color:#FFFFFF;
}
tr.lred{
background-color:#FFCBCA;
}
tr.lgreen{
background-color:#AFFCAB;
}
tr.lblack{
background-color:#DFDFDF;
}
div.error{
width:90%;
min-height:20px;
background-color:#FEC6BC;
border:1px solid #FF0000;
padding:5px;
margin:5px auto 5px auto;
color:#990000;
text-align:center;
}
div.noerror{
width:90%;
min-height:20px;
background-color:#B7F2BA;
border:1px solid #009933;
padding:5px;
margin:5px auto 5px auto;
color:#006600;
text-align:center;
}
div.header-1{
border-bottom:2px solid #666;
border-top:2px solid #666;
border-left:2px solid #666;
padding:2px 3px;
}
div.header-2{
border-bottom:2px solid #666;
border-left:1px solid #666;
border-top:2px solid #666;
padding:2px 5px;
}
div.header-3{
border-bottom:2px solid #666;
border-left:1px solid #666;
border-right:3px double #666;
border-top:2px solid #666;
padding:2px 5px;
}
div.header-4{
border-bottom:2px solid #666;
border-left:1px solid #666;
border-top:2px solid #666;
border-right:2px solid #666;
padding:2px 5px;
}

div.line-1{
border-left:2px solid #666;
padding:2px 3px;
}
div.line-2{
border-left:1px solid #666;
padding:2px 5px;
}
div.line-3{
border-left:1px solid #666;
border-right:3px double #666;
padding:2px 5px;
}
div.line-4{
border-left:1px solid #666;
border-right:2px solid #666;
padding:2px 5px;
}
div.line-total-1{
border-left:1px solid #666;
border-top:1px solid #666;
border-right:3px double #666;
border-bottom:3px double #666;
padding:2px 5px;
}
div.line-total-2{
border-left:1px solid #666;
border-top:1px solid #666;
border-right:2px solid #666;
border-bottom:3px double #666;
padding:2px 5px;
}
form.filter input{
border:1px solid #CCC;
font-size:10px;
}
a.small:link{
font-size:9px;
color:#0099FF;
text-decoration:none;
}
a.small:visited{
font-size:9px;
color:#0099FF;
text-decoration:none;
}
a.small:hover{
font-size:9px;
color:#0099FF;
text-decoration:none;
}
a.small:active{
font-size:9px;
color:#0099FF;
text-decoration:none;
}


Login Page


index.php file is the main file of a web base system or a website. It is called the index file because when we visit to the application, this is the first file loads in to the browser. In general practice, it says every site must have an index file. The name "index" or "default" is served as the index names in most web servers.

 In this system, index file (index.php) is considered as the login page to the system. To login to the system user need to enter a username and a password. Therefore we need to create a HTML form to enter these details. Following is the login form;

<form method="post" action="index.php">
<table border="0" align="center">
<tr><td>Username:</td><td><input type="text" size="30" name="user"></td></tr>
<tr><td>Password:</td><td><input type="password" size="30" name="pass"></td></tr>
<tr><td>&nbsp;</td><td><input type="submit" name="submit" value="Login"></td></tr>
  </table>
</form>

When user click on the login button, username and password submitted to the same page (index.php in the action tag). Then we need to handle the form submit in PHP script. We need to check in the users table for matching values.

<?php
if(isset($_POST['submit'])){
$user = $_POST['user'];
$pass = $_POST['pass'];
if($user != '' && $pass != ''){
$q = mysql_query("SELECT * FROM users WHERE User_Name = '$user' AND User_Password = '$pass'");
$r = mysql_fetch_assoc($q);
$db_username = $r['User_Name'];
$db_password = $r['User_Password'];
if($user === $db_username && $pass === $db_password){
// login
$_SESSION['login'] = date('YMd');
$_SESSION['user_id'] = $r['User_ID'];;
header("location: main.php");
ob_end_flush();
}else{
$error = "Incorrect Username and / or Password!";
}
}
}
?>

If matching values are found in the database, redirect the user to main.php file while setting login sessions. If user submitted data is not matching with database values, then displays an error message.

In the index.php file, we do not used, header.php and footer.php because of checking user login sessions. The complete source code of the index.php file as follows.

<?php session_start(); ?>
<?php ob_start(); ?>
<?php
if(isset($_SESSION['login']) && $_SESSION['login'] == date('YMd')){
header("Location: main.php");
ob_end_flush();
}
?>
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>EasyTrade&trade; | Login</title>
<link rel="stylesheet" type="text/css" href="style.css">
</head>

<body>
<?php include("db_connect.php"); ?>
<div class="main-wrapper">
<?php
if(isset($_POST['submit'])){
$user = $_POST['user'];
$pass = $_POST['pass'];
if($user != '' && $pass != ''){
$q = mysql_query("SELECT * FROM users WHERE User_Name = '$user' AND User_Password = '$pass'");
$r = mysql_fetch_assoc($q);
$db_username = $r['User_Name'];
$db_password = $r['User_Password'];
if($user === $db_username && $pass === $db_password){
// login
$_SESSION['login'] = date('YMd');
$_SESSION['user_id'] = $r['User_ID'];;
header("Location: main.php");
ob_end_flush();
}else{
$error = "Incorrect Username and / or Password!";
}
}
}
?>
<div style="width:30%; padding:5px; background-color:#FFFF00; border:3px solid #FFCC00; margin:150px auto;">
<?php
if(isset($error)){
echo "<div class='error'>{$error}</div>";
}
?>
<form method="post" action="index.php">
<table border="0" align="center">
<tr><td>Username:</td><td><input type="text" size="30" name="user"></td></tr>
<tr><td>Password:</td><td><input type="password" size="30" name="pass"></td></tr>
<tr><td>&nbsp;</td><td><input type="submit" name="submit" value="Login"></td></tr>
  </table>
</form>
</div>
</div>
</body>
</html>
<?php mysql_close($conn); ?>

We use inline css and well as css defined in the style.css file. In next chapter we discuss about the style sheets.

Footer File (footer.php)


Footer file contains only the closing HTML tags and database closing function.

</div>
</div>
</body>
</html>
<?php mysql_close($conn); ?>

we include this footer.php file in to other pages using php include() function. Following demonstrate hot to do this.

In every other page start with header and footer included at the top and bottom of the page.

<?php include('header.php'); ?>

<!-- Page contents goes here -->

<?php include('footer.php'); ?>

The final output of the page is a complete web page with opening and closing HTML tags and other scripts.

Page Header (header.php)


Header section contains starting HTML tags, CSS style sheets, Java Scripts, database connection, session control and navigation menu. It is a good practice to include java scripts in the footer. Since we are using very limited set of java scripts, using it in the header makes no changes to page loading. We use java scripts in the footer to reduce the page loading time, so if you are using lots of java scripts in your project, it is best to put all java scripts at the bottom of the page (footer).

At the most beginning of the page is session control.

<?php session_start(); ?>
<?php ob_start(); ?>

Then the database connection.

<?php include("db_connect.php"); ?>

db_connect.php file contains following connection data.

<?php
$conn = mysql_connect("localhost","root","") or die ("Can not connect to the host!");
mysql_select_db("ezpay",$conn) or die ("Connect to the database failed!");

?>

This is how we connect mysql database in to php application.

Then we check the user login status comparing session and session values.

<?php
$date = date('Y-m-d');
if(!isset($_SESSION['login']) || $_SESSION['login'] != date('YMd')){
header("Location: index.php");
ob_end_flush();
}
?>

Next the basic HTML elements - html title and headers.

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>EasyTrade &trade;</title>
<link rel="stylesheet" type="text/css" href="style.css" />
<link rel="stylesheet" href="cwcalendar.css" type="text/css" />
<script type="text/javascript" src="calendar.js"></script>
</head>

next HTML body and navigation menu.

<body>
<div class="main-wrapper">
<div class="top-banner">
<div class="top-banner-1">
<?php
$q = mysql_query("SELECT Name, Address FROM profile");
$r = mysql_fetch_assoc($q);
$pro_name = $r['Name'];
$pro_address = $r['Address'];
echo "<h2>{$pro_name}</h2>";
echo "<p>{$pro_address}</p>";
?>
</div>
<div class="top-banner-2">
<span class="small-btn"><a href="edit_profile.php">Edit Profile</a></span>
<span class="small-btn"><a href="logout.php">Logout</a></span>
</div>
<div class="clear"></div>
</div>
<div class="links-set">
<span class="main-btn"><a href="main.php">Home</a></span>
<span class="main-btn"><a href="customers.php">Customers</a></span>
<span class="main-btn"><a href="sales.php">Sales</a></span>
<span class="main-btn"><a href="payments.php">Payments</a></span>
<span class="main-btn"><a href="users.php">Users</a></span>
</div>
<div class="main-contents">

This is the header.php file and we will include this file in to pages using php include() function.

Page and Site Sturcture


We developing this application using PHP and MySQL. PHP is the scripting language and the MySQL is the database management system or database back-end. Since this is a web app all the pages are constructed using HTML because it is the web language. Additionally we need to use Java Scripts and CSS to complete page behaviors and look and feel.

We use CW Calendar plug for pop-up calendars to enter data where it needs.

Page is divides mainly in to three sections.


  1. Header
  2. Contents
  3. Footer
Header and footer is common contents for all the pages throughout the site. Header contains basic HTML elements, css, java scripts and database connection information. We also uses session control to check user login status and other privileges. In the footer contains closing HTML tags and database connection close statement.

Content are is unique to its page contents. Header and Footer are included using include() php function.

db_connection.php is the PHP database connection script file.
calendar.js and cwcalendar.css are CW Calendar plugin files.
style.css is the main style sheet for the site.
header.php is the header file
footer.php is the footer file.

In the next chapter lets explore more in these files.

Thursday, June 28, 2018

My SQL Database Structure


Following is the structure of the MySQL database. It has five tables.


  1. Customers
  2. Payments
  3. Sales
  4. Profile
  5. Users
Customers Table

Customer table uses to store customer details. Cus_ID is the primary key of this table. Sex uses Single bit char value to store one letter. M = Male, F = Female. Status also same with default value of A = Active, D = Deleted, B = Black Listed


Cus_ID          int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY
Cus_Date       date NOT NULL,
Cus_Name     varchar(120) NOT NULL,
Cus_NIC        varchar(10) NOT NULL,
Cus_Tel          varchar(10) NOT NULL,
Cus_Email     varchar(120) DEFAULT NULL,
Cus_Sex        char(1) DEFAULT NULL,
Cus_Add       text,
Cus_Status    char(1) NOT NULL DEFAULT 'A',

Payments Table

Payments table is to record customer payment details. P_Sale is a foreign key and is the primary key of Sales table. P_Type records the type of payment D = Down Payment, I = Installment

P_ID int(11) NOT NULL AUTO_INCREMENT,
P_Date date NOT NULL,
P_Sale int(11) NOT NULL,
P_Value decimal(10,2) NOT NULL,
P_Type char(1) NOT NULL,
P_Detail text,
PRIMARY KEY (P_ID)

Profile Table

This table uses to record company details.

Name varchar(40) NOT NULL,
Address text NOT NULL,
Telephone varchar(20) DEFAULT NULL,
PRIMARY KEY (Name)

Sales Table

This table records sales details.

  S_ID int(11) NOT NULL AUTO_INCREMENT,
  S_Date date NOT NULL,
  S_Customer int(11) NOT NULL,
  S_Item varchar(120) NOT NULL,
  S_Description text,
  S_Value decimal(10,2) NOT NULL,
  S_Installments smallint(6) NOT NULL,
  S_Installment_Value decimal(10,2) NOT NULL,
  S_Interest decimal(10,2) NOT NULL,
  S_Status char(1) NOT NULL DEFAULT 'A',
  PRIMARY KEY (S_ID)

Users Table

This table records system users details.

  User_ID smallint(6) NOT NULL AUTO_INCREMENT,
  User_Name varchar(20) NOT NULL,
  User_Password varchar(20) NOT NULL,
  User_Privileges text NOT NULL,
  PRIMARY KEY (User_ID)

Following the complete MySQL dump file to import database in to PHPMyAdmin.

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 05, 2011 at 10:31 AM
-- Server version: 5.1.36
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: prezire_030311
--

-- --------------------------------------------------------

--
-- Table structure for table customers
--

CREATE TABLE IF NOT EXISTS customers (
  Cus_ID int(11) NOT NULL AUTO_INCREMENT,
  Cus_Date date NOT NULL,
  Cus_Name varchar(120) NOT NULL,
  Cus_NIC varchar(10) NOT NULL,
  Cus_Tel varchar(10) NOT NULL,
  Cus_Email varchar(120) DEFAULT NULL,
  Cus_Sex char(1) DEFAULT NULL,
  Cus_Add text,
  Cus_Status char(1) NOT NULL DEFAULT 'A',
  PRIMARY KEY (Cus_ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table customers
--

INSERT INTO customers (Cus_ID, Cus_Date, Cus_Name, Cus_NIC, Cus_Tel, Cus_Email, Cus_Sex, Cus_Add, Cus_Status) VALUES
(1, '2011-03-04', 'Susantha Herath', '813030608V', '0712105424', 'susanthahm@gmail.com', 'F', '195/5, Mahabuthgamuwa,\r\nAngoda', 'B');

-- --------------------------------------------------------

--
-- Table structure for table payments
--

CREATE TABLE IF NOT EXISTS payments (
  P_ID int(11) NOT NULL AUTO_INCREMENT,
  P_Date date NOT NULL,
  P_Sale int(11) NOT NULL,
  P_Value decimal(10,2) NOT NULL,
  P_Type char(1) NOT NULL,
  P_Detail text,
  PRIMARY KEY (P_ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table payments
--

INSERT INTO payments (P_ID, P_Date, P_Sale, P_Value, P_Type, P_Detail) VALUES
(4, '2011-03-05', 1, '3570.00', 'I', ''),
(2, '2011-03-04', 1, '15000.00', 'D', 'CHQ 455568545 (COM)');

-- --------------------------------------------------------

--
-- Table structure for table profile
--

CREATE TABLE IF NOT EXISTS profile (
  Name varchar(40) NOT NULL,
  Address text NOT NULL,
  Telephone varchar(20) DEFAULT NULL,
  PRIMARY KEY (Name)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table profile
--

INSERT INTO profile (Name, Address, Telephone) VALUES
('Prezire Software Solutions', '195/5, Kotikawatte', '0115752806');

-- --------------------------------------------------------

--
-- Table structure for table sales
--

CREATE TABLE IF NOT EXISTS sales (
  S_ID int(11) NOT NULL AUTO_INCREMENT,
  S_Date date NOT NULL,
  S_Customer int(11) NOT NULL,
  S_Item varchar(120) NOT NULL,
  S_Description text,
  S_Value decimal(10,2) NOT NULL,
  S_Installments smallint(6) NOT NULL,
  S_Installment_Value decimal(10,2) NOT NULL,
  S_Interest decimal(10,2) NOT NULL,
  S_Status char(1) NOT NULL DEFAULT 'A',
  PRIMARY KEY (S_ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table sales
--

INSERT INTO sales (S_ID, S_Date, S_Customer, S_Item, S_Description, S_Value, S_Installments, S_Installment_Value, S_Interest, S_Status) VALUES
(1, '2011-03-04', 1, 'Singer LED TV 21', 'Model No. E458785\r\nYear: 2010\r\nColor: Black / Silver', '34000.00', 12, '3570.00', '5.00', 'C');

-- --------------------------------------------------------

--
-- Table structure for table users
--

CREATE TABLE IF NOT EXISTS users (
  User_ID smallint(6) NOT NULL AUTO_INCREMENT,
  User_Name varchar(20) NOT NULL,
  User_Password varchar(20) NOT NULL,
  User_Privileges text NOT NULL,
  PRIMARY KEY (User_ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table users
--

INSERT INTO users (User_ID, User_Name, User_Password, User_Privileges) VALUES
(1, 'admin', 'go2010', 'Manage Inventory, Billing, Manage Users, '),
(3, 'Susantha', '123', 'Manage Inventory, Billing, ');