Sumith Bandara

Introduction

Metropolitan Office (Pvt) Ltd is Metropolitan’s specialist imaging and printing company with a portfolio that includes copiers, printers, multifunction units and production graphic arts machines under the Canon and Oce brands of products. The company also provides a range of service products such as out sourced printing services and customized layout and designing services at its Print Hub center.

Metropolitan has been providing equipment for the imaging and printing sector since its beginnings in 1958. Being the sole authorized distributor for the Canon range of imaging and printing products for the last 3 decades, Metropolitan supports the increase in productivity and efficiency through these products and services that are cost effective, efficient and needless to mention user friendly.

Being the market leader in this sector, the company also offers value added services such as scanning, designing and printing, and compiling large volume reports and manuscripts. The service includes ‘exclusive color' printing at cost effective terms offered to customers who seek instant and short-run print jobs.

The Company is a leading workplace and engineering solutions provider for the public, private, NGO and SME sectors. The company also offers a range of cash management solutions, cheque scanning and network solutions from leading global brands such as Casio, Double A and Canon.

We understand that service support for any equipment purchased is a key to creating delighted customers and to this we are committed to providing customers unparalleled service support. But the sales or technical division may keep the track of records in manually and rest of things will prepare with help of the office application software. The managerial persons so will be analyzing the turnover and gross profit in branches. We have the system to develop the organizing of data in a storage area and get data back for analyzing when required.

Only important role is to enter all the selling and technical related data input to the data store. The system will be executed to manipulate the data processing section to generate the management information as per the requirement of managerial staff. The whole thing of back end part is presented here for implementing above tasks.

Goal of the project

The company basically two type of customers such as government and co-operate and the product range divided into few categories as mentioned below.

· Air Conditioner

· Business Multi-Function Devices

· Calculator

· Cameras

· Cash Management Solutions

· Communication Solution

· Consumables

· Desktop & Laptops

· Fax Multifunction

· Generators

· Inkjets Printers

· Large Format Printing

· Laser Printer

· Phones & Tabs

· Photocopy Machine

· Projectors

· Production & Graphic Art

· Scanners

Various product categories have the different models and its specification and unit prices and the data may include the item name, model, capacity, special features, unit price, other assecories, delivery schedule. The sales persons may sell the product to customers and should achieve the target according to company procedure. They can visit the customer’s location and identify the requirements and make the quotation or proposal available for the customers by selecting the products and their models.

When order from the customer received, it should record in order file and sales persons will consider the order as winning bid. Order details may include the quotation no, date of receive, item name, quantity, unit price, remark.

Sales persons should prepare the daily sales reports regularly when visited the customers and collect their requirements. If the sales person sent them the quotation, it also should record with details of quotation no/tender no, item name, unit price, term, delivery date, validity of bid, customer name. The government organization will invite to propose their quotation by calling tender procedure.

If it is a new customer, the sales person should collect the customer details such as name, address, contact, fax, status, Contact person, special contact no, designation. In sales division, different level of sales persons are available for the customers to contact and their rank (Senior Sales EXE, Junior Sales Exe, Junior Sales Rep, Senior Sales Rep), epf no, name, address may record in personal file.

We have to design a database to collect these large volumes of data which is happening in manual system for better decisions and analyze in future to be achieved the goal.

· To stock in the fast moving equipments
· To quote the best price by compare previous bid
· To understand latest strength of equipments
· To understand customer desired level on equipments
· To understand the competition
· To be solved the weakness of process

Implementation

Create Tables

CREATE TABLE 'child'

(

'name' varchar(80) NOT NULL,

'epfno' varchar(10) NOT NULL,

'relationship' varchar(170) NOT NULL,

PRIMARY KEY ('name')

)

CREATE TABLE 'customer'

(

'custid' varchar(10) NOT NULL,

'name' varchar(50) NOT NULL,

'address' varchar(150) NOT NULL,

'contact' varchar(10) NOT NULL,

'designation' varchar(65) NOT NULL,

PRIMARY KEY ('custid')

)

CREATE TABLE 'employee'

(

'name' varchar(80) NOT NULL,

'epfno' varchar(10) NOT NULL,

'address' varchar(170) NOT NULL,

'rank' varchar(50) NOT NULL,

PRIMARY KEY ('epfno')

)

CREATE TABLE 'orderdetails'

(

'orderno' varchar(10) NOT NULL,

'quotationno' varchar(10) NOT NULL,

'dateofreceive' datetime NOT NULL,

'itemno' varchar(10) NOT NULL,

'quantity' int(11) NOT NULL,

'price' int(11) NOT NULL,

'remark' varchar(200) NOT NULL,

PRIMARY KEY ('orderno')

)

ALTER TABLE 'quotation'

ADD CONSTRAINT ' orderdetails _ibfk_1' FOREIGN KEY (' quotationno ') REFERENCES 'quotation' (' quotationno ');

ALTER TABLE 'quotation'

ADD CONSTRAINT ' orderdetails _ibfk_2' FOREIGN KEY (' itemno ') REFERENCES 'products' (' itemno ');

CREATE TABLE 'products' (

'itemname' varchar(50) NOT NULL,

'model' varchar(50) NOT NULL,

'capacity' varchar(170) NOT NULL,

'brand' varchar(30) NOT NULL,

'features' varchar(170) NOT NULL,

'price' int(11) NOT NULL,

'accessories' varchar(2000) NOT NULL,

'deliveryschesule' varchar(70) NOT NULL,

PRIMARY KEY ('model')

)

CREATE TABLE 'quotation'

(

'quotationno' varchar(10) NOT NULL,

'model' varchar(50) NOT NULL,

'customerid' varchar(10) NOT NULL,

'epfno' varchar(10) NOT NULL,

'quantity' int(10) NOT NULL,

PRIMARY KEY ('quotationno')

)

ALTER TABLE 'quotation'

ADD CONSTRAINT 'quotation_ibfk_1' FOREIGN KEY ('customerid') REFERENCES 'customer' ('custid');

ALTER TABLE 'quotation'

ADD CONSTRAINT 'quotation_ibfk_2' FOREIGN KEY ('epfno') REFERENCES 'employee' ('epfno');

f

Data Entering

INSERT INTO 'child' ('name', 'epfno', 'relationship') VALUES

('aseni', '5042', 'daughter');

INSERT INTO 'child' ('name', 'epfno', 'relationship') VALUES

('iduwara', '5066', 'son');

INSERT INTO 'child' ('name', 'epfno', 'relationship') VALUES

('iduwari', '5066', 'daughter');

INSERT INTO 'customer' ('custid', 'name', 'address', 'contact', 'designation') VALUES

('pdhs1', 'provincial director of health service', 'apura', '025-223381', 'supply officer'),

INSERT INTO 'customer' ('custid', 'name', 'address', 'contact', 'designation') VALUES

('ped1', 'provincial education department', 'apura', '025-226678', 'supply clerk');

INSERT INTO 'customer' ('custid', 'name', 'address', 'contact', 'designation') VALUES

('rdhs1', 'regional director of health service', 'apura', '025-222333', 'accountant');

INSERT INTO 'customer' ('custid', 'name', 'address', 'contact', 'designation') VALUES

('uc1', 'urban council', 'apura', '025-222234', 'accountant');

INSERT INTO 'employee' ('name', 'epfno', 'address', 'rank') VALUES

('sumith', '5042', 'medawachchiya', 'executive');

INSERT INTO 'employee' ('name', 'epfno', 'address', 'rank') VALUES

('indika', '5066', 'medawachchiya', 'technical');

INSERT INTO 'employee' ('name', 'epfno', 'address', 'rank') VALUES

('samarasinghe', '5097', 'talawa', 'junior executive');

INSERT INTO 'employee' ('name', 'epfno', 'address', 'rank') VALUES

('nimesh', '6066', 'srawastipura', 'technical');

INSERT INTO 'orderdetails' ('orderno', 'quotationno', 'dateofreceive', 'itemno', 'quantity', 'price', 'remark') VALUES ('o1', 'q1', '2016-11-03 12:01:40', 'f788gs', 10, 2100, '');